Module 1

Module 1
PART 1: Manage Container Database
第一部分：管理容器数据库

Section 1: Creating Pluggable Databases:
第 1 节：创建可插拔数据库：

Create a pluggable database PDBPROD3 in PRODCDB. Use the following specifications.
在 PRODCDB 中创建可插拔数据库 PDBPROD3。使用以下规格。
a. The PDBPROD3 must be a copy of the PDBPROD1 database and must be opened for updates.
a. PDBPROD3 必须是 PDBPROD1 数据库的副本，并且必须打开以进行更新。
```sql
--关闭数据库
alter pluggable database PDBPROD1 close;

--开启到只读状态
alter pluggable database PDBPROD1 open read only;

--复制数据库
create pluggable database PDBPROD3 from PDBPROD1 where file_name_convert('PDBPROD1','PDBPROD3');

alter pluggable database PROPROD1 CLOSE;
alter pluggable database all open;

--验证 应全部为read write 
show pdbs
```
b. Ensure that PDBPROD3 is opened read/write automatically whenever PRODCDB opened read/write, without using triggers.
b. 确保每当 PRODCDB 以读写模式打开时，PDBPROD3 自动以读写模式打开，而不使用触发器。
```sql
--保持状态
alter pluggable database PDBPROD3 save state;

--验证
desc cdb_pdb_saved_state;
select CON_NAME,instance_name ,state from cdb_pdb_saved_states;
```
Create pluggable databases PDBPROD4 and PDBPROD5 in PRODCDB. Use the following specifications:
2. 在 PRODCDB 中创建可插拔数据库 PDBPROD4 和 PDBPROD5。使用以下规格：
a. Import application schemas HR, OE and BI from the DB12C non-CDB database into the PDBPROD4 pluggable database in the PRODCDB container database.
a. 从 DB12C 非 CDB 数据库中导入应用程序模式 HR、OE 和 BI 到 PRODCDB 容器数据库中的 PDBPROD4 可插拔数据库。
```
--确认12c使用表空间及大小
12
select TABLESPACE_NAME,sum(BYTES)/124/1024/1024 from dba_segments where OWNER in ('HR','OE','BI') group by TABLESPACE_NAME;

--创建PRODPROD4
create pluggable database PDBPROD4 admin user pdbuser identified by oracle file_name_convert=('pdbseed','PDBPROD4') nologging;

--开启pdbprod4
alter  pluggable database pdbprod4 open;
/*
配置sqlnet.ora注释原本,保留 TNSNAMES,EZCONNECT
配置PDBPRD4\5  的tnsname.ora

sqllus sys/oracle@pdbprod4 as sysdba
*/

--创建表空间
select name from v$datafile;
create tablespace users datafile '/home/oracle/files/users.dbf' size 2G autoextend on;
create tablespace example datafile '/home/oracle/files/example.dbf' size 2G autoextend on;

--创建目录
create directory dir as '/home/oracle/files';
grant write,read on directory dir to system;

--创建到12的dblink
create public database link lk_db12c conncet to system identified by oracle useing 'db12c'

--导入
impdp  system/oracle@pdbprod4 directory=dir network_link=lk_db12c schemas=hr,oe,bi

--验证
sqlplus sys/oracle@pdbprod4 as sysdba
SELECT TABLESPACE_NAME,sum(bytes)/1024/1024/1024 from dba_segments where owner IN ('HR','OE','BI') group by tablespace_name;

```
b. Clone the Oracle 19c non-CDB database PROD4 into the PRODCDB container database as the pluggable database PDBPROD5.
b. 将 Oracle 19c 非 CDB 数据库 PROD4 克隆到 PRODCDB 容器数据库中，作为可插拔数据库 PDBPROD5。
```sql
p4
--授权创建可插拔数据库权限
grant create pluggable database to system
alter user system identified by oracle;
--只读开启
shutdown immediate
startup open read only ;

dbs
--创建dblink
create public database link lk_p4 connect to system identified by oracle using prod4;

create  pluggable database PDBPROD5 from NON$CDB@lk_p4  file_name_convert=('PROD4','PDBPROD5') nologging;

conn sys/oracle@PDBPROD5
@?/rdbms/admin/noncdb_to_pdb.sql
--执行完
alter  pluggable database all open;

show pdbs
```
c. Make sure the non-CDB databases DB12C and PROD4, and the pluggable databases PDBPROD4 and PDBPROD5 are open for updates.
c. 确保非 CDB 数据库 DB12C 和 PROD4，以及可插拔数据库 PDBPROD4 和 PDBPROD5 是开放以进行更新的。
```
db12
select open_mode from v$database

p4
select open_mode from v$database

cdb
show pdbs

```
Create the following users:
3. 创建以下用户：

a. USER1, with the following specifications:
a. 用户 USER1，具有以下规格：
i. USER1 must be defined in PDBPROD1, PDBPROD2, PDBPROD3, PDBPROD4, and PDBPROD5.
i. USER1 必须在 PDBPROD1、PDBPROD2、PDBPROD3、PDBPROD4 和 PDBPROD5 中定义。
ii. USER1 must also be defined in any future PDB in PRODCDB.
ii. USER1 还必须在 PRODCDB 中的任何未来 PDB 中定义。
iii. Adjust the name of the user, USER1, if required.
iii. 如有需要，调整用户 USER1 的名称。
```
show parameter comm

--设置用户前缀为空
alter  system set common_user_prefix='' scope=spfile;
--重启
shutdown imemdiate
startup
show pdbs
alter pluggable database all open;

create user user1 identified oracle;

--验证
desc cdb_users;
select  USERNAME,CON_ID,COMMON from cdb_users where USERNAME='USER1'
```

b. USER2, which is defined only in the PDBPROD1 database.
b. 用户 USER2，仅在 PDBPROD1 数据库中定义。
```
sqlplus sys/oracle@PDBPROD1 as sysdba
create user user2 identified by oracle; 
select  USERNAME,CON_ID,COMMON from cdb_users where USERNAME='USER2'
```
**4. Create the following roles**  
4. 创建以下角色
   a. **ROLE1, with the following specifications.**  
      a. ROLE1，具有以下规格。
   i. **It is defined in PDBPROD1, PDBPROD2, PDBPROD3, PDBPROD4, and PDBPROD5.**  
      i. 它在 PDBPROD1、PDBPROD2、PDBPROD3、PDBPROD4 和 PDBPROD5 中定义。
   ii. **ROLE1 must also be defined in any future PDB in PRODCDB.**  
       ii. ROLE1 还必须在 PRODCDB 中的任何未来 PDB 中定义。
   iii. **Adjust the name of the role, ROLE1, if required.**  
        iii. 如有需要，调整角色名称 ROLE1。
   iv. **Make ROLE1 the default role of USER1.**  
        iv. 将 ROLE1 设置为 USER1 的默认角色。
```
cdb模式下

create role role1
grant role1 to use1 container= all
alter user user1 default role role1 container=all;

conn user1/oracle@prodcdb
select * from user_role_privs;
```

b. **ROLE2, which is defined only in database PDBPROD1. Users must be authorized by password before this role is enabled.**  
      b. ROLE2，仅在 PDBPROD1 数据库中定义。用户在启用此角色之前必须通过密码授权。
```
sqlplus sys/oracle@pdbprod1 as sysdba
create role role2;
alter role role2 identified by oracle;

```

**5. Grant privileges and roles to users and roles. Use the following specifications**  
5. 向用户和角色授予权限和角色。使用以下规格：

a. **Allow USER1 the privilege to log in to PDBPROD1, PDBPROD2, PDBPROD3, PDBPROD4, and PDBPROD5, and any other pluggable databases that may be created in the future. And USER1 will have create any table privilege limited to PDBPROD2.**  
      a. 允许 USER1 有权登录 PDBPROD1、PDBPROD2、PDBPROD3、PDBPROD4 和 PDBPROD5，以及将来可能创建的任何其他可插拔数据库。同时，USER1 将在 PDBPROD2 中拥有创建任何表的权限。
```
cdb
grant create session to user1 container=all;

conn sys/oracle@pdbprod2
grant create table to user1;

```
   b. **Allow USER2 the privilege to login to PDBPROD1.**  
      b. 允许 USER2 有权登录 PDBPROD1。
```
cdb
alter session set container=pdbprod1;
grant create session to user2;
```
   c. **Modify ROLE1 role such that it has the privilege to create a stored procedure in all the current as well as future pluggable databases of the PRODCDB container database granted via the CR_PRO_ROLE role. Adjust the name of the role CR_PRO_ROLE, if required.**  
      c. 修改 ROLE1 角色，使其具有在 PRODCDB 容器数据库的所有当前及未来可插拔数据库中创建存储过程的权限，通过角色 CR_PRO_ROLE 授予。如果需要，调整角色名称 CR_PRO_ROLE。
```
create role CR_PRO_ROLE  container=all;
grant create produce to CR_PRO_ROLE  container=all;
grant  CR_PRO_ROLE to role1  container=all;

select * from role_sys_privs
```
   d. **Allow USER2 to create a trigger via role CR_TRG_ROLE in PDBPROD1 and allow USER2 to grant CR_TRG_ROLE to other users.**  
      d. 允许 USER2 在 PDBPROD1 中通过角色 CR_TRG_ROLE 创建触发器，并允许 USER2 将 CR_TRG_ROLE 授予其他用户。
```
create role CR_TRG_ROLE container=all;
grant create trigger to CR_TRG_ROLE container=all;
--  with admin  option允许收取按给其他用户
grant CR_TRG_ROLE to USER2 with admin  option  container=all;
```
---

**Section 2: Creating and configuring Tablespaces**  
第 2 节：创建和配置表空间

1. **In the PDBPROD1 database, create a temporary tablespace group named TEMP_GRP to support batch processing, the creation of large indexes, and analysis of tables. The group should contain the temporary tablespaces TEMP1 and TEMP2. Make TEMP_GRP the default temporary tablespace for all new users and all existing users assigned to the current default temporary tablespace.**  
   1. 在 PDBPROD1 数据库中创建一个名为 TEMP_GRP 的临时表空间组，以支持批处理、大型索引的创建和表的分析。该组应包含临时表空间 TEMP1 和 TEMP2。将 TEMP_GRP 设置为所有新用户和所有当前分配到默认临时表空间的现有用户的默认临时表空间。
```
create temporary tablespace temp1 tempfiel '/dir/temp1.dbf' size 50M AUTOEXTEND ON TABLESPACE GROUP  TEMP_GRP;
create temporary tablespace temp2 tempfiel '/dir/temp2.dbf' size 50M AUTOEXTEND ON TABLESPACE GROUP  TEMP_GRP;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_GRP；
```
   2. **In the PDBPROD2 database, create a permanent tablespace to store sample test data. Use the following specifications:**  
   2. 在 PDBPROD2 数据库中创建一个永久表空间以存储示例测试数据。使用以下规格：
   a. **Tablespace name: OLD_DATA_TBS**  
      a. 表空间名称：OLD_DATA_TBS
   b. **Tablespace will only have one datafile for storing sample test data.**  
      b. 表空间将仅有一个数据文件用于存储示例测试数据。
   c. **Initial data file size: 500 MB**  
      c. 初始数据文件大小：500 MB
   d. **The file can be expected to grow up to 1 TB.**  
      d. 文件预计可以增长到 1 TB。
```
create bigfile tablespace OLD_DATA_TBS datafile '/dir/OLD_DATA_TBS.dbf' size 500m  autoextend on maxsize 1T;
```
3. **In the PDBPROD1 database, create a permanent tablespace to store customer data. Use the following specifications:**  
   3. 在 PDBPROD1 数据库中创建一个永久表空间以存储客户数据。使用以下规格：
   a. **Tablespace name: CUST_DATA**  
      a. 表空间名称：CUST_DATA
   b. **File size: 40 MB**  
      b. 文件大小：40 MB
   c. **The tablespace will use uniform extent size with 5 MB.**  
      c. 表空间将使用统一的区大小为 5 MB。
```
create tablespace CUST_DATA datafile '/u01/app/oracle/oradata/PRODCDB/PDBPROD2/CUST_DATA.dbf' size 40m extent  management local uniform  size 5m; 
```

**Section 3: UNDO Set Up and Management**  
第 3 节：UNDO 设置和管理

1. **Configure automatic undo management in the PRODCDB database to support the following requirements. Make sure that queries up to 90 minutes long can run and get read consistent versions of rows without any errors.**  
   1. 在 PRODCDB 数据库中配置自动 UNDO 管理，以支持以下要求。确保查询时间最长可达 90 分钟，能够运行并获得行的读取一致版本而不出现任何错误。
```
show parameter undo 
alter system set undo_retention=5400;
```
2. **Change the configuration of the DEVCDB database so that there is an UNDO tablespace in each PDB.**  
   2. 更改 DEVCDB 数据库的配置，使每个 PDB 中都有一个 UNDO 表空间。
```
dev
--查看是否本地管理undo
desc database_properties
select property_name,property_value from database_properties where property_name ='LOCAL_UNDO_ENABLED';
shutdown immediate
--开启升级模式
startup upgrade 
alter database local undo on;
shutdown immedoate 
startup
alter pluggable database all open;
select property_name,property_value from database_properties where property_name ='LOCAL_UNDO_ENABLED';
```

**Section 4: Creating and Managing Application Containers**  
第 4 节：创建和管理应用程序容器
1. **Create an application container on the DEVCDB container database with the following specifications:**  
   1. 在 DEVCDB 容器数据库上创建一个应用程序容器，具有以下规格：
   a. **The application root is named DEVAPP_ROOT.**  
      a. 应用程序根名为 DEVAPP_ROOT。
   b. **Create the application PDBs that belong to the application root as DEVAPP_PDB1.**  
      b. 将属于应用程序根的应用程序 PDB 创建为 DEVAPP_PDB1。
   c. **Open the application container that you created with read/write mode.**  
      c. 以读写模式打开您创建的应用程序容器。
```
--创建目录
mkdir /u01/app/oracle/oradata/DEVCDB/DEVAPP_ROOT
chmod 755 /u01/app/oracle/oradata/DEVCDB/DEVAPP_ROOT
chown oracle:oinstall /u01/app/oracle/oradata/DEVCDB/DEVAPP_ROOT

create pluggable database DEVAPP_ROOT  as application container admin user DEVAPP_ROOT_USER identified by oracle create_file_dest='/u01/app/oracle/oradata/DEVCDB/DEVAPP_ROOT';

alter pluggable database DEVAPP_ROOT open;

--添加tnsname.ora

conn sys/oracle@devapp_root as sysdba
!mkdir /u01/app/oracle/oradata/DEVCDB/DEVAPP_ROOT/DEVAPP_PDB1
create pluggable database DEVAPP_PDB1  admin user pdb_admin identified by oracle create_file_dest='/u01/app/oracle/oradata/DEVCDB/DEVAPP_ROOT/DEVAPP_PDB1';
alter pluggable database devapp_pdb1 open;
```
2. **Install the application in the application root that you created with the following specifications:**
   2. 在您创建的应用程序根中安装应用程序，具有以下规格：
   a. **Application Name: DEV_APP**  
      a. 应用程序名称：DEV_APP
   b. **Application Version: 1.0**  
      b. 应用程序版本：1.0
   c. **The application you are installing should run "dev_app4-2.sql."**  
      c. 您要安装的应用程序应运行 "dev_app4-2.sql"。
```
alter pluggable database application devapp begin install '1.0';
@/home/oracle/script/dev_app4-2.sql
alter pluggable database application devapp end install '1.0';
desc devapp
```
3. **Synchronize the application installed in the application root to the application PDB.**  
   3. 将在应用程序根中安装的应用程序与应用程序 PDB 同步。
```
conn sys/oracle@devapp_pdb1 as sysdba
desc devapp

alter pluggable database application devapp sync;

desc devapp 
```
4. **The DEVCDB container database has the application root container named HR_ROOT. The following applications are installed:**  
   4. DEVCDB 容器数据库具有名为 HR_ROOT 的应用程序根容器。安装了以下应用程序：
   a. **Common user of the application container: the hr_admin (password is oracle_4U).**  
      a. 应用程序容器的公共用户：hr_admin（密码为 oracle_4U）。
   b. **Common objects of the application container: the EMP table and the DEPT table, which are METADATA-LINK objects, in the hr_admin schema.**  
      b. 应用程序容器的公共对象：在 hr_admin 模式中的 EMP 表和 DEPT 表，它们是元数据链接对象。
   c. **Application name: hr_app (version 1.0).**  
      c. 应用程序名称：hr_app（版本 1.0）。
   **Configure the application container to meet the following requirements:**  
   配置应用程序容器以满足以下要求：
   a. **Open all the application PDBs (SALES, ACCOUNTING, RESEARCH) associated with HR_ROOT application root container. They should be in read/write mode.**  
      a. 打开与 HR_ROOT 应用程序根容器关联的所有应用程序 PDB（SALES、ACCOUNTING、RESEARCH）。它们应处于读写模式。
   b. **Connect as the hr_admin user to the HR_ROOT application root container and run the "dev_app4-3-2.sql" script to store data in the EMP and DEPT tables.**  
    b. 以 hr_admin 用户身份连接到 HR_ROOT 应用程序根容器并运行 "dev_app4-3-2.sql" 脚本，以将数据存储到 EMP 和 DEPT 表中。

c. **The EMP and DEPT tables store data separately for each application PDB. Create a container map so that it is properly routed to the relevant application PDB when a query is performed. Use the map table name MAPTABLE and the DEPTNO column for the partitioning key.**  
    c. EMP 和 DEPT 表为每个应用程序 PDB 单独存储数据。创建一个容器映射，以便在执行查询时能够正确路由到相关的应用程序 
    PDB。使用映射表名称 MAPTABLE 和 DEPTNO 列作为分区键。
   d. **Configure to enable the container map you created to work together with the queried table. Also, configure the HR_ROOT application root container so that the table can be queried without the CONTAINERS clause.**  
      d. 配置使您创建的容器映射能够与查询的表一起工作。同时，配置 HR_ROOT 应用程序根容器，使该表可以在不使用 CONTAINERS 子句的情况下进行查询。
```
-- 在 CDB 级别创建公共用户（需以 SYSDBA 连接 CDB）
CONNECT sys/oracle@DEVCDB AS SYSDBA
CREATE USER C##hr_admin IDENTIFIED BY oracle_4U CONTAINER=ALL;
GRANT CREATE SESSION, CONNECT, RESOURCE, UNLIMITED TABLESPACE TO C##hr_admin CONTAINER=ALL;

-- 创建 HR_ROOT 应用程序根容器
CREATE PLUGGABLE DATABASE HR_ROOT 
AS APPLICATION CONTAINER 
ADMIN USER hr_admin IDENTIFIED BY oracle_4U
FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/DEVCDB/pdbseed/', 
                    '/u01/app/oracle/oradata/DEVCDB/HR_ROOT/');

ALTER PLUGGABLE DATABASE HR_ROOT OPEN;

-- 连接到 HR_ROOT 应用程序容器
CONNECT sys/oracle@HR_ROOT AS SYSDBA

-- 安装应用程序
ALTER PLUGGABLE DATABASE APPLICATION hr_app BEGIN INSTALL '1.0';
@/home/oracle/scripts/dev_app4-3-2.sql
ALTER PLUGGABLE DATABASE APPLICATION hr_app END INSTALL '1.0';

-- 创建容器映射
CONNECT hr_admin/oracle_4U@HR_ROOT

-- 创建映射表
CREATE TABLE MAPTABLE (
  DEPTNO NUMBER PRIMARY KEY,
  PDB_NAME VARCHAR2(30)
);

-- 插入映射关系
INSERT INTO MAPTABLE VALUES (10, 'SALES');
INSERT INTO MAPTABLE VALUES (20, 'ACCOUNTING');
INSERT INTO MAPTABLE VALUES (30, 'RESEARCH');
COMMIT;

-- 配置容器映射
BEGIN
  DBMS_CONTAINER_MAP.CREATE_CONTAINER_MAP(
    map_name          => 'DEPT_MAP',
    table_name        => 'MAPTABLE',
    key_column        => 'DEPTNO',
    container_column  => 'PDB_NAME'
  );
END;
/

-- 启用自动路由
ALTER PLUGGABLE DATABASE APPLICATION hr_app SET CONTAINER_MAP = 'DEPT_MAP';

-- 验证配置
SELECT * FROM CONTAINER(EMP) WHERE DEPTNO = 10; -- 自动路由到 SALES PDB
```

**Section 5: Server-Side and Client-Side Network Configuration**  
第 5 节：服务器端和客户端网络配置

1. **Perform the following client-side network configuration tasks on the 19c database server. Provide connect descriptors for your databases by using the local naming method. Use fully qualified hostnames.**  
   1. 在 19c 数据库服务器上执行以下客户端网络配置任务。使用本地命名方法为您的数据库提供连接描述符。使用完全合格的主机名。
   a. **The PDBPROD3 alias should connect to PDBPROD3 by using the default listener LISTENER with TCP/IP port 1521, and should always use a dedicated server connection.**  
      a. PDBPROD3 别名应通过默认监听器 LISTENER 连接到 PDBPROD3，使用 TCP/IP 端口 1521，并且应始终使用专用服务器连接。
   b. **The PDBPROD4 alias should connect to PDBPROD4 by using the default listener LISTENER with TCP/IP port 1521, and should always use a dedicated server connection.**  
      b. PDBPROD4 别名应通过默认监听器 LISTENER 连接到 PDBPROD4，使用 TCP/IP 端口 1521，并且应始终使用专用服务器连接。
   c. **The PDBPROD5 alias should connect to PDBPROD5 by using the default listener LISTENER with TCP/IP port 1521, and should always use a dedicated server connection.**  
      c. PDBPROD5 别名应通过默认监听器 LISTENER 连接到 PDBPROD5，使用 TCP/IP 端口 1521，并且应始终使用专用服务器连接。
```
listner.ora添加
SID_LIST_LISTENER=
  (SID_LIST=
      (SID_DESC=
         (GLOBAL_DBNAME=PRODCDB.example.com)
         (SID_NAME=PRODCDB)
         (ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1)
        )
       (SID_DESC=
         (GLOBAL_DBNAME=DEVCDB.example.com)
         (SID_NAME=DEVCDB)
         (ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1)
        )
   )

lsnrctl reload

tnsname.ora
添加
PROD1HOTCLINE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = host01.example.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PROD1HOTCLINE.example.com)
    )
  )

```
2. **On the 19c Database Server, create an additional listener LSNR2 by using port 1525. Create PROD_S alias for shared server connections to the database PDBPROD1 using listener LSNR2.**  
   2. 在 19c 数据库服务器上，通过使用 1525 端口创建一个额外的监听器 LSNR2。使用监听器 LSNR2 为数据库 PDBPROD1 创建共享服务器连接的 PROD_S 别名。
```
listner.ora添加
复制修改默认监听
LSNR2 =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = host01.example.com)(PORT = 1525))
    )
lsnrctl start lsnr2

cdb
show parameter local
alter system set local_listener=LSNR2;
alter system register;
lsnrctl status lsnr2
```

**Section 6: Configuring the Container Database for Shared Server**  
第 6 节：配置容器数据库以支持共享服务器

1. **Configure the PRODCDB database to support up to 500 sessions reserving 100 for dedicated connections.**  
   1. 配置 PRODCDB 数据库以支持最多 500 个会话，保留 100 个用于专用连接。
```
show parameter session
alter system set sessions=500 scope=spfile;
alter system set shared_server_sessions=100;
```
2. **Configure the PRODCDB database to support a minimum of 3 TCP dispatchers and a maximum of 10.**  
   2. 配置 PRODCDB 数据库以支持最少 3 个 TCP 派发器和最多 10 个。
```
show parameter dis
alter system set dispatchers='(PROTOCOL=TCP) (DIS=3)';
alter system set max_dispatchers=10;
```
3. **Configure the PRODCDB database to support a minimum of 10 shared server processes and a maximum of 50 shared server processes.**  
   3. 配置 PRODCDB 数据库以支持最少 10 个共享服务器进程和最多 50 个共享服务器进程。
```
show parameter share
alter system set shared_servers=10;
alter system set max_shared_servers=50;
```
**Section 7: Configuring the Control File**  
第 7 节：配置控制文件

1. **Create a third copy of the control file, control03.ctl, for the PRODCDB container database in \$ORACLE_HOME/dbs/. The original control files must not be renamed or moved.**  
   1. 在 $ORACLE_HOME/dbs/ 下为 PRODCDB 容器数据库创建控制文件的第三个副本 control03.ctl。原始控制文件不得重命名或移动。
```
show parameter control

alter system set control_files = '/u01/app/oracle/oradata/PRODCDB/control01.ctl','/u01/app/oracle/fast_recovery_area/PRODCDB/control02.ctl','/u01/app/oracle/product/19.3.0/dbhome_1/dbs/control03.ctl' scope = spfile;

alter pluggable database all close immediate;

shutdown immediate

!cp /u01/app/oracle/oradata/PRODCDB/control01.ctl /u01/app/oracle/product/19.3.0/dbhome_1/dbs/control03.ctl

startup

alter pluggable database all open;
```

**Section 8: Managing Pluggable Databases**  
第 8 节：管理可插拔数据库

1. **Change the following initialization parameter settings on the PRODCDB container database.**  
   1. 更改 PRODCDB 容器数据库上的以下初始化参数设置。
   a. **Set the maximum number of cursors that can be opened simultaneously in a session to 500.**  
      a. 将可以同时在会话中打开的最大光标数量设置为 500。
   b. **The maximum number of cursors must be set to PDBPROD1 only.**  
      b. 最大光标数量必须仅设置为 PDBPROD1。
   c. **Set the settings to persist across database restarts.**  
      c. 设置以确保在数据库重启后持续有效。
```
cdb
conn sys/oracle@PDBPROD1 as sysdba
show parameter cursor
alter system set open_cursors=500 container=current;
```
2. **Enable the lockdown profile to limit operations at the PDB level. Create a lockdown profile with the following rules:**  
   2. 启用锁定配置文件以限制在 PDB 级别的操作。创建一个具有以下规则的锁定配置文件：
   a. **The lockdown profile name is PROD_PROF1.**  
      a. 锁定配置文件名称为 PROD_PROF1。
   b. **Do not allow ALTER SYSTEM statements. ALLOW ALTER SYSTEM SET statements.**  
      b. 不允许 ALTER SYSTEM 语句。允许 ALTER SYSTEM SET 语句。
   c. **PROD_PROF1 should affect only PDBPROD2.**  
      c. PROD_PROF1 仅应影响 PDBPROD2。
```
dbs
create lockdown profile PROD_PROF12;
alter lockdown profile PROD_PROF1 disable statement=('alter system');
alter lockdown profile PROD_PROF1 enable statement=('alter system set');

conn sys/oracle@pdbprod2 as sysdba
alter system set pdb_lockdown=PROD_PROF1;

shwo parameter pdb_lockdown
```
3. **Create a clone of PDBPROD1 which is in PRODCDB. The clone which you create should be on DEVCDB. Use the remote cloning feature with hot mode. Perform with the following specifications:**  
   3. 创建一个在 PRODCDB 中的 PDBPROD1 的克隆。您创建的克隆应位于 DEVCDB 上。使用热模式的远程克隆功能。执行以下规格：
   a. **The name of the clone which you create is PROD1_HOTCLONE. Enable its data to be refreshed every 5 minutes from the source pluggable database.**  
      a. 您创建的克隆名称为 PROD1_HOTCLONE。使其数据能够每 5 分钟从源可插拔数据库刷新。
   b. **The state of the PDB which you created should be in an instance mode that is able to refresh.**  
      b. 您创建的 PDB 的状态应处于能够刷新的实例模式。
```
都创建用户授权
create user c##u1  identified by oracle;
grant create session ,resource,create any table ,unlimited tablespace to c##u1 container=all;
grant create pulggable database to c##u01 container=all;
grant sysoper to c##u01 container=all;

dev
create public database link lk_P1 connect c##u1 identified by oracle using 'PDBPROD1';

CREATE pluggable database PROD1_HOTCLONE  from PDBPROD1@lk_P1 refresh mode every 5 mintes file_name_dest=('PROCDB/PDBPROD1','DEVCDB/PROD1_HOTCLONE');

```
4. **Move a PDB in the PRODCDB to the DEVCDB using near-zero downtime PDB relocation. Confirm the following to relocate the PDB:**  
   4. 使用近零停机时间 PDB 迁移将 PRODCDB 中的 PDB 移动到 DEVCDB。确认以下内容以迁移 PDB：
   a. **Run the relocate8-4.sh script under /home/oracle/scripts. This script starts RELOCATE_PDB1 on PRODCDB and creates a table named BIGTAB on RELOCATE_PDB1. After that, this script inserts data to the BIGTAB table.**  
      a. 在 /home/oracle/scripts 下运行 relocate8-4.sh 脚本。此脚本在 PRODCDB 上启动 RELOCATE_PDB1，并在 RELOCATE_PDB1 上创建名为 BIGTAB 的表。之后，此脚本向 BIGTAB 表插入数据。
   **Note:** Although the terminal that you ran relocate8-4.sh does not return the prompt, you can proceed to the next task.  
   **注意：** 尽管运行 relocate8-4.sh 的终端未返回提示，您可以继续进行下一任务。
   b. **Move RELOCATE_PDB1 to the DEVCDB database using the near-zero downtime PDB relocation feature. Use AVAILABILITY NORMAL mode, and use the same name for the relocated PDB.**  
      b. 使用近零停机时间 PDB 迁移功能将 RELOCATE_PDB1 移动到 DEVCDB 数据库。使用可用性正常模式，并为迁移的 PDB 使用相同的名称。
   c. **Open the RELOCATE_PDB1 on the DEVCDB with read/write mode.**  
      c. 以读写模式打开 DEVCDB 上的 RELOCATE_PDB1。
```sql
cdb
@/home/oracle/scripts/relocate8-4.sh
(
create table bigdata(id number);
insert into bigdata values(88);
commit;
)

conn  sys/oracle@relocate_pdb1 as sysdba
(
create table bigdata(id number);
insert into bigdata values(88);
commit;
)

dev
create public database link lk_rel connect to c##u1 identified by "oracle" using 'PRODCDB';
create pluggable database RELOCATE_PDB1 from RELOCATE_PDB1@lk_rel relocate AVAILABILITY NORMAL file_name_convert=('PRODCDB/RELOCATE_PDB1','DEVCDB/RELOCATE_PDB1');
```

DBLOG

导航1234

最近发表

友情链接