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 (c) 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 (c) 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>