重庆思庄oracle技术分享-oracle 19C 通过克隆方式把NON-CDB拷贝到CDB去

环境：
ORACLE 19.12
LINUX 8.4

目的：
把一个NON-CDB 数据库 EMREP通过克隆方式，拷贝到CDB去，取名 pdb_emrep

步骤：

1.把non-cdb emrep 启动数据库到只读

SQL> startup open read only;
ORACLE instance started.

Total System Global Area 2147481648 bytes
Fixed Size 8898608 bytes
Variable Size 956301312 bytes
Database Buffers 1174405120 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL> select name,open_mode from v$database;

NAME OPEN_MODE

---

EMREP READ ONLY

2.建立到EMREP的连接串
在CDB所在的机器上执行

[oracle@szdb admin]$ cat tnsnames.ora |grep -i ‘^emrep’ -A 10
emrep =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = szdb)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = emrep)
)
)

测试一下联通性
[oracle@szdb admin]$ tnsping emrep

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 29-OCT-2021 15:08:42

Copyright © 1997, 2021, Oracle. All rights reserved.

Used parameter files:
/u01/app/oracle/product/19.12.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = szdb)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = emrep)))
OK (0 msec)

3.建立到NOCDB的DBLINK
在CDB中建立

3.1 进入CDB1

[oracle@szdb admin]$ . oraenv
ORACLE_SID = [cdb1] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@szdb admin]$
[oracle@szdb admin]$ sqlplus /nolog

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Oct 29 15:20:12 2021
Version 19.12.0.0.0

Copyright © 1982, 2021, Oracle. All rights reserved.

SQL> conn / as sysdba
Connected.
SQL> select name,cdb,open_mode from V$database;

NAME CDB OPEN_MODE

---

CDB1 YES READ WRITE

SQL>

3.2 建立到EMREP的DBLINK
CREATE database link to_emrep
connect to system identified by oracle_4U
using ‘emrep’;

SQL> CREATE database link to_emrep
2 connect to system identified by oracle_4U
3 using ‘emrep’;

Database link created.

4.克隆no-cdb
create pluggable database pdb_emrep
from non$cdb@to_emrep
create_file_dest=’/home/oracle/oradata/’ ;

SQL> create pluggable database pdb_emrep
2 from non$cdb@to_emrep
3 create_file_dest=’/home/oracle/oradata/’
4 ;
create pluggable database pdb_emrep
*
ERROR at line 1:
ORA-17628: Oracle error 1031 returned by remote Oracle server
ORA-01031: insufficient privileges

---

在NON-CDB EMREP中去执行：
重新打开数据库为正常读写：
SQL> STARTUP FORCE;

SQL> GRANT CREATE PLUGGABLE DATABASE to system;

SQL> select privilege from dba_sys_privs where grantee=‘SYSTEM’;

PRIVILEGE

CREATE PLUGGABLE DATABASE
GLOBAL QUERY REWRITE
CREATE TABLE
DEQUEUE ANY QUEUE
ENQUEUE ANY QUEUE
SELECT ANY TABLE
MANAGE ANY QUEUE
UNLIMITED TABLESPACE
CREATE MATERIALIZED VIEW

9 rows selected.

SQL>STARTUP FORCE OPEN READ ONLY;

再去执行：
SQL> create pluggable database pdb_emrep
2 from non$cdb@to_emrep
3 create_file_dest=’/home/oracle/oradata/’
4 ;

Pluggable database created.

OK 成功

5.远程 noncdb_to_pdb.sql 脚本
SQL> show pdbs

CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---

     2 PDB$SEED                       READ ONLY  NO
     3 PDB2                           MOUNTED
     4 PDB1                           MOUNTED
     5 PDB_EMREP                      MOUNTED

SQL>
SQL>
SQL> alter session set container=pdb_emrep;

Session altered.

sql >@?/rdbms/admin/noncdb_to_pdb.sql

6.打开新的PDB
sql> alter pluggable database pdb_emrep open;

SQL> show pdbs

CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---

     5 PDB_EMREP                      MOUNTED

SQL> alter database open;

Database altered.

SQL> show pdbs

CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---

     5 PDB_EMREP                      READ WRITE YES

SQL>