Module 2

**Part 1: Backup/Recovery**  
第一部分：备份/恢复
```

```

**Section 1: Configuring the Recovery Catalog**  
第 1 节：配置恢复目录
```
EMCC

host02

./emctl status oms 
./emctl start oms

sysman/Oracle123

SETUP
 Add target
    Add Target Manually
     install agent on host
     add
        host==host01.example.com
        Platform==LINUx x86-64
        
        install dir   /u01/app/oracle/produce/agent13c
        name credential 添加
        向下添加

```

1. **Create an RMAN recovery catalog on the PDBRCAT1 pluggable database on the RCATCDB. Owner must be rc_admin and the password must be RC_ADMIN.**  
    1. 在 RCATCDB 上的 PDBRCAT1 可插拔数据库中创建一个 RMAN 恢复目录。拥有者必须是 rc_admin，密码必须是 RC_ADMIN。
```
create tablespace cattbs datafile '/u01/app/oracle/oradata/RCATCDB/PDBRCAT1/cattbs.dbf' size 200M autoextend on maxsize 2G;
create user rc_admin identified by RC_ADMIN DEFAULT tabelespace cattbs unlimitd on cattbs
grant sysdba,connect,resource,recovery_catalog_owner,sysoper to rc_admin;

rman catalog rc_admin/RC_ADMIN@PDBRCAT1
create catalog tablespace cattbs;
```

2. **Register the PRODCDB and DEVCDB databases with the recovery catalog.**  
   2. 将 PRODCDB 和 DEVCDB 数据库注册到恢复目录中。
```
cdb\dev 开启归档
host01
sqlplus  target sys/oracle catalog rc_admin/RC_ADMIN@PDBRCAT1
upgrade catalog;
register database;

sqlplus sys/oracle@devcdb catalog rc_admin/RC_ADMIN@PDBRCAT1
register database;
```
**Section 2: Recovery and Duplication**  
第 2 节：恢复和复制
1. **Users reported that the PROD5 database is unavailable. Identify the cause and take appropriate recovery actions so that you can connect with the net service name PROD5.**  
   1. 用户报告 PROD5 数据库不可用。识别原因并采取适当的恢复措施，以便您可以通过网络服务名称 PROD5 进行连接。
```
p5
rman target /
--恢复控制文件
list failure;
advise failure;
repair failure;

--恢复system
list failure;
advise failure;
repair failure;

```
2. **Create a PDB named PDBPROD2_DEV on the DEVCDB container database using the RMAN duplication feature. Use the PDBPROD2 pluggable database which is in active mode on the PRODCDB container database as the source. Do not create a separate auxiliary instance. Perform this task on the DEVCDB directly.**  
   2. 使用 RMAN 复制功能在 DEVCDB 容器数据库上创建一个名为 PDBPROD2_DEV 的 PDB。使用在 PRODCDB 容器数据库中处于活动模式的 PDBPROD2 可插拔数据库作为源。不创建单独的辅助实例。直接在 DEVCDB 上执行此任务。
```
mkdir /u01/app/oracle/oradata/remote
dev
show parameter remot
alter system set remote_recovery_file_dest='/app/oracle/oradata/remote';

rman target sys/oracle@prodcdb auxiliary sys/oracle@devcdb

duplicate PLUGGABLE DATABASE PDBPROD2 AS PDBPROD2_DEV TO devcdb  db_file_name_convert=('PRODCDB/PDBPORD2','DEVCDB/PDBPROD2_DEV') FROM active DATABASE SECTION SIZE 400M;
```
**Section 3: Backups**  
第 3 节：备份

1. **Make sure the following requirements are fulfilled:**  
   1. 确保满足以下要求：

- **Configure RMAN, so that backup taken for the database PRODCDB should be retained for 14 days. Files having no changes since the last backup are not backed up. Backup sets written to disk using one backup command should use 4 channels. Archived logs should be eligible for deletion after backed up 2 times to disk.**  
     - 配置 RMAN，使得 PRODCDB 数据库的备份应保留 14 天。自上次备份以来没有更改的文件不进行备份。使用一个备份命令写入磁盘的备份集应使用 4 个通道。归档日志在备份到磁盘 2 次后应具备删除资格。
```
cbd
rman target sys/oracle@prodcdb
show all

--无更改不备份
configure backup optimization on;
--14天
ONFIGURE RMAN OUTPUT TO KEEP FOR 14 days;
--两次
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 2;
--4个通道
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;

```
2. **Create a backup as a backup set, with TAG PRODCDB_BKP, use these criteria:**  
   2. 创建一个备份集，标签为 PRODCDB_BKP，使用以下标准：
   a. **It should include a backup of the PDBPROD1 database. It must be compressed.**  
      a. 应包括 PDBPROD1 数据库的备份。必须进行压缩。
   b. **It contains all files needed to perform complete recovery if any media failure occurs.**  
      b. 包含在发生任何介质故障时执行完全恢复所需的所有文件。
   c. **It must form the baseline for an incremental backup strategy.**  
      c. 必须构成增量备份策略的基线。
```
mkdir /u01/app/oracle/backup
rman sys/oracle@pdbprod1
backup tag 'PRODCDB_BKP' as  compressed backupset incremental level 0 format '/u01/app/oracle/backup/full_pdbprod1_%U.bak'   database plus archivelog;
```
3. **The backup of EXAMPLE tablespace in the PDBPROD1 database takes a long time. To speed up the backup process, you decide to backup the tablespace in parallel. Set the maximum file size to 30M, and ensure that the backup information is written in the catalog.**  
   3. PDBPROD1 数据库中 EXAMPLE 表空间的备份耗时较长。为了加快备份过程，您决定并行备份该表空间。将最大文件大小设置为 30M，并确保备份信息写入目录。
```
 backup as compressed backupset section size  30M format '/u01/app/oracle/backup/bk_example_%U.bak' tablespace pdbprod1:example plus archivelog;
```
4. **Reduce the overheads of incremental backups to a minimum on the PRODCDB database.**  
   4. 将 PRODCDB 数据库上的增量备份开销降至最低。
```
alter database enable block change tracking using file '/u01/app/oracle/backup/track.f';
```
5. **Multiplex the redo logs for all groups in the PRODCDB database to the location /u01/app/oracle/oradata/MREDO.**  
   5. 将 PRODCDB 数据库中所有组的重做日志多路复用到位置 /u01/app/oracle/oradata/MREDO。
```
select member ,group# from v$logfile;
alter database add logfile member '/u01/app/oracle/oradata/MREDO/redo01a.log' to group 1;
alter database add logfile member '/u01/app/oracle/oradata/MREDO/redo02a.log' to group 2;
alter database add logfile member '/u01/app/oracle/oradata/MREDO/redo03a.log' to group 3;
```
6. **Configure the database to reuse a reusable record in the control file after 14 days in the PRODCDB database.**  
   6. 在 PRODCDB 数据库中配置数据库，以便在 14 天后重用控制文件中的可重用记录。
```
show parameter control
alter system set control_file_record_keep_time=14;
```
**Section 4: Configuring Flashback**  
第 4 节：配置闪回

1. **In the PRODCDB container database, enable Flashback Database. Make sure the following requirements are fulfilled:**  
   1. 在 PRODCDB 容器数据库中启用闪回数据库。确保满足以下要求：
   a. **Enable Flashback Database on PRODCDB container database.**  
      a. 在 PRODCDB 容器数据库上启用闪回数据库。
   b. **Configure a fast recovery area of 20 GB. The fast recovery area should be in the /u01/app/oracle/flash directory on the Database Server.**  
      b. 配置 20 GB 的快速恢复区域。快速恢复区域应位于数据库服务器的 /u01/app/oracle/flash 目录中。
   c. **Configure Flashback Database for a timeframe of 48 hours.**  
      c. 将闪回数据库配置为 48 小时的时间框架。
```
show parameter recover
alter system set  db_recovery_file_dest='/u01/app/oracle/flash';
alter system set db_recovery_file_dest_size='20G';
select FLASHBACK_ON from v$database;
alter database flashback on;
show parameter flash
alter system set db_flashback_retention_target=2880;
```
2. **Create the restore point DBRSP1 in the PRODCDB database. The restore point does not age out on the control file.**  
   2. 在 PRODCDB 数据库中创建恢复点 DBRSP1。该恢复点在控制文件中不会过期。
```
create restore point  DBRSP1  guarantee flashback database;
```

**Part 2: Data Management**  
第二部分：数据管理

**Section 1: Creating an Additional Buffer Cache**  
第 1 节：创建额外的缓冲区缓存
1. **In the PRODCDB database, create an additional buffer cache to use with 16 KB blocks. Ensure that this new buffer cache is always available.**  
   1. 在 PRODCDB 数据库中创建一个额外的缓冲区缓存，以使用 16 KB 的块。确保这个新的缓冲区缓存始终可用。
```
show parameter 16k
alter system set db_16k_cache_size=15m;
```
**Section 2: Creating a Plug-in Tablespace and External Table**  
第 2 节：创建可插拔表空间和外部表
1. **Plug in the transportable tablespace, TRPDATA, into the PDBPROD1 pluggable database, and bring it online. The data TRPDATA6, which is being transported, was created on a big-endian platform, Solaris 10 OE 64-bit. The TRPDATA6 data file and the trans2_2.dmp dump file are located in /home/oracle/scripts. The exported user is SST. The plug-in tablespace objects should belong to the SST user.**  
   1. 将可传输表空间 TRPDATA 插入 PDBPROD1 可插拔数据库，并使其上线。被传输的数据 TRPDATA6 是在大端平台（Solaris 10 OE 64-bit）上创建的。TRPDATA6 数据文件和 trans2_2.dmp 转储文件位于 /home/oracle/scripts。导出的用户是 SST。插入的表空间对象应属于 SST 用户。
```
create user stt identified by oracle ;
grant connect,resource,unlimited,tablespace to stt;
create dirctory dir as '/home/oracle/scripts';
grant read,wirite,excute on dirctory dir to public;
select PLATFORM_NAME from v$database;
select PLATFORM_NAME from v$transportable_platform;

--恢复表空间
rman target  / catalog rc_admin/RC_ADMIN@pdbrcat1
covert datafile '/home/oracle/scripts/trpdata_6.dbf' from platform='Solaris[tm] OE (64-bit)' to 'Linux x86 64-bit' db_file_name_convert=('/home/oracle/scripts','/u01/app/oracle/oradata/PRODCDB/PDBPROD1');

--导入数据
impdp system/oracle@pdbprod1 dirctoyy=dir dumpfile=trans2_2.dmp log=trans2_2.log transport_datafiles='/u01/app/oracle/oradata/PRODCDB/PDBPROD1/trpdata_6.dbf'

alter tablespace trans_tbs read write;

alter trans_user default tablespace trans_tbs;

图形化
create user stt identified by oracle account unlock;
grant connect,resource,unlimited tablespace to stt;

schame
传输表空间

```
2. **In the scripts directory, you will find sales_2018.dat.gz, sales_2019.dat.gz, and sales_master.ctl. Using the information found in the files, create an Oracle Loader external table in the schema in the PDBPROD1 database that must run a preprocess to manipulate data. This external table must be partitioned and meet the following requirements:**  
   2. 在脚本目录中，您将找到 sales_2018.dat.gz、sales_2019.dat.gz 和 sales_master.ctl。使用文件中的信息，在 PDBPROD1 数据库的模式中创建一个 Oracle Loader 外部表，该表必须运行预处理以操作数据。此外部表必须是分区的，符合以下要求：
   a. **Partitioning key is the TIME_ID column.**  
      a. 分区键是 TIME_ID 列。
   b. **Use the following partition information:**  
      b. 使用以下分区信息：
      - **year2018 VALUES LESS THAN (TO_DATE('31-12-2018', 'DD-MM-YYYY'))**  
      - **year2019 VALUES LESS THAN (TO_DATE('31-12-2019', 'DD-MM-YYYY'))**  
   c. **Partition "year2018" should correspond to sales_2018.dat.gz and partition "year2019" should correspond to sales_2019.dat.gz.**  
      c. 分区 "year2018" 应对应 sales_2018.dat.gz，分区 "year2019" 应对应 sales_2019.dat.gz
```
根据控制文件建表
create table  sales_master(sname varchar2(10), 
cname varchar2(10), 
sno varchar2(10), 
cno varchar2(10), 
cname1 varchar2(10) , 
cname2 varchar2(10), 
time_id date,
corder varchar2(10),
sorder varchar2(10))
partition by range(TIME_ID) 
interval (numtoyminterval(1,'year'))
(
partition year2018  VALUES LESS THAN (TO_DATE('31-12-2018', 'DD-MM-YYYY')),
partition year2019  VALUES LESS THAN (TO_DATE('31-12-2019', 'DD-MM-YYYY'))
);

sqlldr sh/sh@pdbprod1 control=sales_master.ctl log=external.log external_table=generate_only;

-- 以 sysdba 身份连接数据库
CONN sys/oracle@pdbprod1 AS SYSDBA;
-- 创建目录 SYS_SQLLDR_XT_TMPDIR_00002
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00002 AS '/home/oracle/scripts';
-- 创建目录 SYS_SQLLDR_XT_TMPDIR_00000
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle/scripts/';
-- 授予 public 用户对目录的读、写、执行权限
GRANT READ, WRITE, EXECUTE ON DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 TO PUBLIC;
GRANT READ, WRITE, EXECUTE ON DIRECTORY SYS_SQLLDR_XT_TMPDIR_00002 TO PUBLIC;

-- 切换到 sh 用户
CONN sh/sh@pdbprod1;
-- 删除已存在的表（如果存在）
DROP TABLE sales_master;
-- 创建外部表 sales_master
CREATE TABLE sales_master (
    "SNAME"    VARCHAR2(200),
    "CNAME"    VARCHAR2(200),
    "SNO"      VARCHAR2(200),
    "CNO"      VARCHAR2(200),
    "CNAME1"   VARCHAR2(200),
    "CNAME2"   VARCHAR2(200),
    "TIME_ID"  DATE,
    "CORDER"   VARCHAR2(200),
    "SORDER"   VARCHAR2(200)
)
ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000  -- 默认目录
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE              -- 记录以换行符分隔
        CHARACTERSET US7ASCII                    -- 字符集
        BADFILE 'SYS_SQLLDR_XT_TMPDIR_00002':'sales_2018.dat.bad'  -- 坏文件路径
        LOGFILE 'SYS_SQLLDR_XT_TMPDIR_00002':'external.log'        -- 日志文件路径
        PREPROCESSOR 'SYS_SQLLDR_XT_TMPDIR_00002':'uncompress.sh'  -- 预处理脚本
        READSIZE 1048576                         -- 读取大小
        FIELDS TERMINATED BY ","                 -- 字段以逗号分隔
        OPTIONALLY ENCLOSED BY '"' AND '"'       -- 字段可选引号
        MISSING FIELD VALUES ARE NULL            -- 缺失字段值为 NULL
        REJECT ROWS WITH ALL NULL FIELDS         -- 拒绝全空字段的行
        (
            "SNAME"    CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
            "CNAME"    CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
            "SNO"      CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
            "CNO"      CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
            "CNAME1"   CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
            "CNAME2"   CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
            "TIME_ID"  DATE "YYYY-MM-DD",        -- 日期格式
            "CORDER"   CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
            "SORDER"   CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
        )
    )
    LOCATION ('sales_2018.dat.gz')  -- 数据文件路径
)
PARTITION BY RANGE ("TIME_ID") (  -- 按 TIME_ID 分区
    PARTITION year2018 VALUES LESS THAN (TO_DATE('31-12-2018', 'DD-MM-YYYY'))
        LOCATION ('sales_2018.dat.gz'),  -- 2018 年数据文件
    PARTITION year2019 VALUES LESS THAN (TO_DATE('31-12-2019', 'DD-MM-YYYY'))
        LOCATION ('sales_2019.dat.gz')   -- 2019 年数据文件
);

@external.log

查询外部表是否有数
```

**Section 3: Optimizing Space Usage for the LOB Data and Encrypted Tablespace**  
第 3 节：优化 LOB 数据和加密表空间的空间使用

1. **In the PDBPROD1 database, create a table to optimize space usage for multiple rows that can have the same LOB data and compress the LOB data.**  
   1. 在 PDBPROD1 数据库中创建一个表，以优化可以具有相同 LOB 数据的多行的空间使用并压缩 LOB 数据。
   
   a. **Create the OE.CUSTOMER_PROFILES table in the SF_DATA tablespace. Enable compression for the PROFILE_INFO column to use the least amount of space for data and also save space by avoiding multiple copies of the same LOB data. The column specifications are as follows:**  
      a. 在 SF_DATA 表空间中创建 OE.CUSTOMER_PROFILES 表。为 PROFILE_INFO 列启用压缩，以使用最少的空间存储数据，并通过避免相同 LOB 数据的多个副本来节省空间。列的规格如下：
      - **CUST_ID NUMBER**  
      - **FIRST_NAME VARCHAR2(20)**  
      - **LAST_NAME VARCHAR2(30)**  
      - **PROFILE_INFO BLOB**  
   b. **Create a directory object, CUST_FILES, which points to /home/oracle/scripts.**  
      b. 创建一个目录对象 CUST_FILES，指向 /home/oracle/scripts。
   c. **Load data into the table by using the procedure in the /home/oracle/scripts/2_3_1.sql script.**  
      c. 使用 /home/oracle/scripts/2_3_1.sql 脚本中的过程将数据加载到表中。
```
cdb
sqlplus sys/oracle@pdbprod1
show parameter secure
alter system set db_securefile=permitted;

create dirctory  CUST_FILES as /home/oracle/scripts;

grant write,read execute on CUST_FILES to public;

create tablespace SF_DATA datafile '/u01/app/oracle/oradata/PRODCDB/PDBPROD1/sf_data.dbf' size 200M autoextend on maxsize 2G extent managent local uniform size 64M;

-- 创建表 customer_profiles
CREATE TABLE customer_profiles (
    cust_id      NUMBER,
    first_name   VARCHAR2(20), 
    last_name    VARCHAR2(30), 
    profile_info BLOB）
)
TABLESPACE SF_DATA -- 指定表空间为 SF_DATA
LOB (profile_info) STORE AS SECUREFILE (-- 将 BLOB 列存储为 SecureFile
    DEDUPLICATE -- 启用去重功能
    COMPRESS HIGH  -- 启用高压缩率
);

@/home/oracle/scripts/2_3_1.sql

exec sp_imp_blob

select count(1) from customer_profiles;
```

2. **In the PDBPROD2 database, create an encrypted tablespace using the following specifications:**  
   2. 在 PDBPROD2 数据库中创建一个加密表空间，使用以下规格：
   a. **Tablespace name: ENCT_DATA**  
      a. 表空间名称：ENCT_DATA
   b. **File size: 50 MB**  
      b. 文件大小：50 MB
   c. **Enable data file auto extend, set the minimum size of incremental to 10 MB and the maximum to 1 GB.**  
      c. 启用数据文件自动扩展，设置增量的最小大小为 10 MB，最大大小为 1 GB。
   d. **Use 'welcome' as the password for the keystore.**  
      d. 使用 'welcome' 作为密钥库的密码。
```

-- 1. 查询加密钱包状态
SELECT * FROM v$encryption_wallet;
-- 2. 创建加密钱包目录
!mkdir -p /u01/app/oracle/admin/PRODCDB/wallet;
-- 3. 创建加密钱包
ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/app/oracle/admin/PRODCDB/wallet' IDENTIFIED BY "welcome";
-- 作用：在指定目录中创建一个加密钱包，并设置钱包密码为 `welcome`。
-- 4. 打开加密钱包
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "welcome";
-- 作用：使用密码 `welcome` 打开加密钱包。
-- 5. 设置加密密钥
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "welcome" WITH BACKUP;
-- 作用：生成一个新的加密密钥，并备份钱包。
-- 6. 创建自动登录钱包
ADMINISTER KEY MANAGEMENT CREATE LOCAL AUTO_LOGIN KEYSTORE FROM KEYSTORE '/u01/app/oracle/admin/PRODCDB/wallet' IDENTIFIED BY "welcome";
-- 作用：创建自动登录钱包，使数据库在启动时自动打开钱包，无需手动输入密码。
-- 7. 再次查询加密钱包状态
SELECT * FROM v$encryption_wallet;
-- 作用：确认加密钱包是否已成功打开，并检查钱包状态。
-- 8. 打开 PDB 的加密钱包
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "welcome";
-- 作用：在 PDB（Pluggable Database）中打开加密钱包。
-- 9. 设置 PDB 的加密密钥
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "welcome" WITH BACKUP;
-- 作用：在 PDB 中生成一个新的加密密钥，并备份钱包。
-- 10. 创建加密表空间
CREATE TABLESPACE ENCT_DATA
DATAFILE '/u01/app/oracle/oradata/PRODCDB/PDBPROD2/ENCT_DATA01.dbf'
SIZE 50M
AUTOEXTEND ON
MAXSIZE 1G
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 10M
ENCRYPTION DEFAULT STORAGE (ENCRYPT);

--   - `EXTENT MANAGEMENT LOCAL`：使用本地管理表空间。
--   - `UNIFORM SIZE 10M`：每个区大小为 10MB。
--   - `ENCRYPTION DEFAULT STORAGE (ENCRYPT)`：默认启用加密。

```
3. **Ensure that the data in SH.CUSTOMERS and SH.PROMOTION is encrypted and stored in the ENCT_DATA tablespace.**  
   3. 确保 SH.CUSTOMERS 和 SH.PROMOTION 中的数据被加密并存储在 ENCT_DATA 表空间中。
```
ALTER TABLE SH.CUSTOMERS MOVE TABLESPACE ENCT DATA;
ALTER TABLE SH.PROMOTION MOVE TABLESPACE ENCT DATA;
```
**Section 4: Administering Partitioning**  
第 4 节：管理分区

1. **In PDBPROD1, create a partitioned table SH.NEW_ORDER_ITEMS. Use the SH.NEW_ORDERS table as the basis for partitioning the NEW_ORDER_ITEMS table. Use PART_TBS1 and PART_TBS2 tablespaces for the partitions.**  
   1. 在 PDBPROD1 中创建一个分区表 SH.NEW_ORDER_ITEMS。使用 SH.NEW_ORDERS 表作为分区 NEW_ORDER_ITEMS 表的基础。使用 PART_TBS1 和 PART_TBS2 表空间作为分区。

a. **The SH.NEW_ORDER_ITEMS table must only contain the following columns:**  
      a. SH.NEW_ORDER_ITEMS 表必须仅包含以下列：
      - **ORDER_ID NUMBER(12) NOT NULL**  
      - **PRODUCT_ID NUMBER NOT NULL**  
      - **QUANTITY NUMBER NOT NULL**  
      - **SALES_AMOUNT NUMBER NOT NULL**

b. **Populate the SH.NEW_ORDERS and SH.NEW_ORDER_ITEMS tables by executing the 241.sql script located in the /home/oracle/scripts directory.**  
      b. 通过执行位于 /home/oracle/scripts 目录中的 2_4_1.sql 脚本来填充 SH.NEW_ORDERS 和 SH.NEW_ORDER_ITEMS 表。
```
CREATE TABLE new_order_items (
    order_id      NUMBER(12) NOT NULL,
    product_id    NUMBER NOT NULL,
    quantity      NUMBER NOT NULL,
    sales_amount  NUMBER NOT NULL,
    CONSTRAINT order_items_fk
        FOREIGN KEY (order_id) REFERENCES new_orders (order_id)
)
PARTITION BY reference (order_items_fk);

@/home/oracle/scripts/2_4_1.sql
```
2. **In PDBPROD1, create a partitioned table SH.SALES_HISTORY_2014 with these columns:**  
   2. 在 PDBPROD1 中创建一个分区表 SH.SALES_HISTORY_2014，包含以下列：

- **PROD_ID NUMBER NOT NULL**  
   - **CUST_ID NUMBER NOT NULL**  
   - **TIME_ID DATE NOT NULL**  
   - **CHANNEL_ID NUMBER NOT NULL**  
   - **PROMO_ID NUMBER NOT NULL**  
   - **QUANTITY_SOLD NUMBER(10,2) NOT NULL**  
   - **AMOUNT_SOLD NUMBER(10,2) NOT NULL**

a. **The table must have the TIME_ID column as the partitioning key, and data for the years 2011, 2012, 2013, and 2014 must be stored in separate partitions named SAL1, SAL2, SAL3, and SAL4, and be stored in sales_tbs1, sales_tbs2, sales_tbs3, and sales_tbs4 tablespaces respectively. Data after 2014 must be stored in a separate partition for each month. These partitions will be non-existent when the table is first created but will be created as data is inserted into the table.**  
      a. 表必须以 TIME_ID 列作为分区键，并且 2011 年、2012 年、2013 年和 2014 年的数据必须存储在分别名为 SAL1、SAL2、SAL3 和 SAL4 的分区中，并存储在 sales_tbs1、sales_tbs2、sales_tbs3 和 sales_tbs4 表空间中。2014 年之后的数据必须为每个月存储在一个单独的分区中。这些分区在表首次创建时不存在，但在向表中插入数据时会创建。

c. **Use the 2_4_2.sql script in /home/oracle/scripts to populate the table.**  
```
CREATE TABLE sales_history_2014 (
    prod_id        NUMBER NOT NULL,
    cust_id        NUMBER NOT NULL,
    time_id        DATE NOT NULL,
    channel_id     NUMBER NOT NULL,
    promo_id       NUMBER NOT NULL,
    quantity_sold  NUMBER(10,2) NOT NULL,
    amount_sold    NUMBER(10,2) NOT NULL
)
PARTITION BY RANGE (time_id)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
STORE IN (sales_tbs1, sales_tbs2, sales_tbs3, sales_tbs4)
(
    PARTITION SAL1 VALUES LESS THAN (TO_DATE('2012-1-1', 'YYYY-MM-DD')) TABLESPACE sales_tbs1,
    PARTITION SAL2 VALUES LESS THAN (TO_DATE('2013-1-1', 'YYYY-MM-DD')) TABLESPACE sales_tbs2,
    PARTITION SAL3 VALUES LESS THAN (TO_DATE('2014-1-1', 'YYYY-MM-DD')) TABLESPACE sales_tbs3,
    PARTITION SAL4 VALUES LESS THAN (TO_DATE('2015-1-1', 'YYYY-MM-DD')) TABLESPACE sales_tbs4
);

@/home/oracle/scripts/2_4_2.sql
```
**Section 5: Configuring the Database to Retrieve Previous Versions of the Table Rows**  
第 5 节：配置数据库以检索表行的先前版本

1. **Configure the PDBPROD1 pluggable database so that all previous versions of rows for the past year in the SH.PROMOTIONS table are retrievable. Create and use the following tablespace and non-DBA user:**  
   1. 配置 PDBPROD1 可插拔数据库，以便可以检索 SH.PROMOTIONS 表中过去一年所有行的先前版本。创建并使用以下表空间和非 DBA 用户：
   a. **The tablespace named FLASH_ARC with a data file of 50 MB.**  
      a. 名为 FLASH_ARC 的表空间，数据文件大小为 50 MB。
   b. **The user named FARC_OWNER.**  
      b. 用户名为 FARC_OWNER。
   c. **Previous versions of rows in the SH.PROMOTIONS table should be retrievable from an archive named flasharc1. The archive should not use more than 30 MB of the FLASH_ARC tablespace.**  
      c. SH.PROMOTIONS 表中行的先前版本应可从名为 flasharc1 的归档中检索。该归档在 FLASH_ARC 表空间中不得超过 30 MB。
```
-- 创建表空间
CREATE TABLESPACE FLASH_ARC
DATAFILE '/u01/app/oracle/oradata/PRODCDB/PDBPROD1/flash_arc.dbf'
SIZE 50M
AUTOEXTEND ON NEXT 1M MAXSIZE 2G;
-- 创建用户
CREATE USER FARC_OWNER
IDENTIFIED BY oracle
ACCOUNT UNLOCK;
-- 授予权限
GRANT CONNECT, RESOURCE, UNLIMITED TABLESPACE, FLASHBACK ARCHIVE ADMINISTER TO FARC_OWNER;
-- 创建闪回归档
CREATE FLASHBACK ARCHIVE flasharcl TABLESPACE FLASH_ARC QUOTA 30M RETENTION 1 YEAR;
-- 授予闪回归档权限
GRANT FLASHBACK ARCHIVE ON flasharcl TO SH;
-- 启用表的闪回归档
ALTER TABLE promotions FLASHBACK ARCHIVE flasharcl;

```
**Section 6: Optimize Star Queries**  
第 6 节：优化星型查询

1. **Configure the database instance so that the query in /home/oracle/scripts/2_6_1.sql persistently uses the star transformation optimizations in PDBPROD1. Ensure that the changes do not affect any other PDBs in PRODCDB.**  
   1. 配置数据库实例，以便位于 /home/oracle/scripts/2_6_1.sql 中的查询在 PDBPROD1 中持久性地使用星型转换优化。确保这些更改不会影响 PRODCDB 中的任何其他 PDB。
```
-- 启用星型转换
ALTER SYSTEM SET star_transformation_enabled = TRUE;
-- 查看参数值
SHOW PARAMETER star_transformation_enabled;
-- 创建位图索引
CREATE BITMAP INDEX sales_time_idx ON sales(time_id) LOCAL;
CREATE BITMAP INDEX sales_customer_idx ON sales(cust_id) LOCAL;
CREATE BITMAP INDEX sales_channel_idx ON sales(channel_id) LOCAL;

set autotrace trace exp

@/home/oracle/scripts/2_6_1.sql
```

**Section 7: Materialized Views**  
第 7 节：物化视图

1. **Configure the PDBPROD1 pluggable database such that query rewrites are always done against all materialized views in all cases where the materialized views are consistent with the underlying data, even if constraints on the tables are not verified. Ensure that the changes do not affect any other PDBs in PRODCDB.**  
   1. 配置 PDBPROD1 可插拔数据库，以便在物化视图与基础数据一致的所有情况下始终对所有物化视图进行查询重写，即使对表的约束未被验证。确保这些更改不会影响 PRODCDB 中的任何其他 PDB。
```
-- 启用查询重写
ALTER SYSTEM SET query_rewrite_enabled = TRUE;
-- 设置查询重写完整性
ALTER SYSTEM SET query_rewrite_integrity = TRUSTED;
-- 查看参数值
SHOW PARAMETER query;

```

**Section 8: Auditing**  
第 8 节：审计

We plan to implement an auditing feature because unauthorized access to the PDBPROD2 pluggable database on the PRODCDB database is suspected.  
我们计划实施审计功能，因为怀疑对 PRODCDB 数据库中的 PDBPROD2 可插拔数据库存在未经授权的访问。

1. **Create users who can maintain the audit configuration and audit trail. This user is a non-DBA user and configures auditing only on the PDBPROD2 database. Username should be SECUSER.**  
   1. 创建可以维护审计配置和审计跟踪的用户。此用户是非 DBA 用户，仅在 PDBPROD2 数据库上配置审计。用户名应为 SECUSER。
```
-- 创建用户
sqlplus sys/oracle@pdbprod2 as sysdba
CREATE USER SECUSER IDENTIFIED BY oracle;
-- 授予权限
GRANT CONNECT, RESOURCE, AUDIT SYSTEM, AUDIT ANY, AUDIT ADMIN, AUDIT VIEWER TO SECUSER;
```

2. **Create an audit policy named PDBPROD2_POL. This audit policy audits for:**  
   2. 创建一个名为 PDBPROD2_POL 的审计策略。此审计策略审计以下内容：
   a. **CREATE TABLE**  
      a. 创建表
   b. **Using Datapump import**  
      b. 使用 Data Pump 导入
   c. **Selecting and deleting HR Users from the EMPLOYEES Table**  
      c. 从 EMPLOYEES 表中选择和删除 HR 用户
   d. **Do not collect indirect user activity (monitor direct user activity only).**  
      d. 不收集间接用户活动（仅监视直接用户活动）。
```
-- 切换到用户
conn SECUSER/oracle@pdbprod2
-- 创建审计策略
CREATE AUDIT POLICY PDBPROD2_POL
ACTIONS CREATE TABLE, SELECT ON HR.EMPLOYEES, DELETE ON HR.EMPLOYEES
ACTIONS COMPONENT=DATAPUMP IMPORT ONLY TOPLEVEL;

```
3. **Enable the PDBPROD2_POL policy to record only when these actions are successful.**  
   3. 启用 PDBPROD2_POL 策略，仅在这些操作成功时记录。
```
-- 启用审计策略
AUDIT POLICY PDBPROD2_POL WHENEVER SUCCESSFUL;
-- 查询审计记录
SELECT DBUSERNAME, ACTION_NAME, SYSTEM_PRIVILEGE_USED, RETURNCODE, OBJECT_SCHEMA, OBJECT_NAME, SQL_TEXT
FROM UNIFIED_AUDIT_TRAIL
WHERE UNIFIED_AUDIT_POLICIES = 'PDBPROD2_POL';
```
**Section 9: Configuring Additional Options**  
第 9 节：配置附加选项

1. **In PDBPROD1, create a table HR.EMPLOYEES2 that is a copy of the HR.EMPLOYEES table.**  
   1. 在 PDBPROD1 中创建一个表 HR.EMPLOYEES2，该表是 HR.EMPLOYEES 表的副本。

a. **Add a column EMPNO to HR.EMPLOYEES2 so that inserts of new rows cause a value to be automatically generated for that column.**  
      a. 向 HR.EMPLOYEES2 添加一个列 EMPNO，以便新行的插入能自动生成该列的值。

b. **Explicit insertion of data into the column is not permitted.**  
      b. 不允许显式向该列插入数据。
```
-- 连接到数据库
conn hr/hr@pdbprod1
-- 创建表并复制数据
CREATE TABLE EMPLOYEES2 AS SELECT * FROM EMPLOYEES;
-- 修改表结构
ALTER TABLE EMPLOYEES2
ADD (empNO NUMBER   generated always AS  identity (START WITH 1  increment BY 1));
```

2. **In PDBPROD1, reclaim unused space from the sparsely populated table HR.EMP without moving the entire segment. Any changes you need to make to the table properties should be permanent changes.**  
   2. 在 PDBPROD1 中，从稀疏填充的表 HR.EMP 中回收未使用的空间，而不移动整个段。对表属性所需进行的任何更改应为永久性更改。
```
alter table emp enable row movement.

emcc
方案-数据库对象-方案=HR 收缩段
```
3. **In PDBPROD1, create a table HR.T2 with the following columns:**  
   3. 在 PDBPROD1 中创建一个表 HR.T2，包含以下列：

- **C1 NUMBER(4)**  
   - **C2 VARCHAR2(10)**

**The segment should not be created at the timing of creating the table. Do not insert the rows in the table.**  
   表段在创建表时不应被创建。不要向表中插入行。
```
CREATE TABLE hr.t2 (c1 NUMBER(4), c2 VARCHAR2(10)) SEGMENT CREATION DEFERRED;

```
4. **Ensure that the index INVENTORY_IX on the table OE.INVENTORIES in PDBPROD1 is maintained for any DML operation and is not used by the optimizer unless explicitly specified as a hint.**  
   4. 确保 PDBPROD1 中表 OE.INVENTORIES 上的索引 INVENTORY_IX 在任何 DML 操作中都被维护，并且除非明确指定为提示，否则优化器不使用该索引。
```
-- 修改索引为不可见
ALTER INDEX oe.INVENTORY_IX INVISIBLE;
-- 查询时强制使用不可见索引
SELECT /*+ USE_INVISIBLE_INDEXES INDEX(a INVENTORY_IX) */ COUNT(1) FROM oe.INVENTORIES a;
```

DBLOG

导航1234

最近发表

友情链接