oracle 19c 数据库 非PDB数据库迁移至PDB 详细步骤说明

一、修改system用户密码、创建dblink

SQL> alter user system identified by oracle;


SQL> create public database link to_prod4 connect to system identified by oracle using 'PROD4';

Database link created.

SQL> select status from v$instance@to_prod4;

STATUS
------------
OPEN

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD4/datafile/o1_mf_system_kkxp16yn_.dbf
/u01/app/oracle/oradata/PROD4/datafile/o1_mf_sysaux_kkxp2b7o_.dbf
/u01/app/oracle/oradata/PROD4/datafile/o1_mf_undotbs1_kkxp33fj_.dbf
/u01/app/oracle/oradata/PROD4/datafile/o1_mf_users_kkxp34lm_.dbf

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PRODCDB/system01.dbf
/u01/app/oracle/oradata/PRODCDB/sysaux01.dbf
/u01/app/oracle/oradata/PRODCDB/undotbs01.dbf
/u01/app/oracle/oradata/PRODCDB/pdbseed/system01.dbf
/u01/app/oracle/oradata/PRODCDB/pdbseed/sysaux01.dbf
/u01/app/oracle/oradata/PRODCDB/users01.dbf
/u01/app/oracle/oradata/PRODCDB/pdbseed/undotbs01.dbf
/u01/app/oracle/oradata/PRODCDB/PDBPROD1/system01.dbf
/u01/app/oracle/oradata/PRODCDB/PDBPROD1/sysaux01.dbf
/u01/app/oracle/oradata/PRODCDB/PDBPROD1/undotbs01.dbf
/u01/app/oracle/oradata/PRODCDB/PDBPROD1/users01.dbf

二、创建pdb

SQL> create pluggable database pdbprod4 from non$cdb@to_prod4 file_name_convert=('/u01/app/oracle/oradata/PROD4/datafile/','/u01/app/oracle/oradata/PRODCDB/PDBPROD4/') nologging;
create pluggable database pdbprod4 from non$cdb@to_prod4 file_name_convert=('/u01/app/oracle/oradata/PROD4/datafile/','/u01/app/oracle/oradata/PRODCDB/PDBPROD4/') nologging
*
ERROR at line 1:
ORA-17628: Oracle error 1031 returned by remote Oracle server
ORA-01031: insufficient privileges

看来system用户权限不够源库授予权限

SQL> grant create pluggable database to system;

Grant succeeded.

SQL> create pluggable database pdbprod4 from non$cdb@to_prod4 file_name_convert=('/u01/app/oracle/oradata/PROD4/datafile/','/u01/app/oracle/oradata/PRODCDB/PDBPROD4/') nologging;
create pluggable database pdbprod4 from non$cdb@to_prod4 file_name_convert=('/u01/app/oracle/oradata/PROD4/datafile/','/u01/app/oracle/oradata/PRODCDB/PDBPROD4/') nologging

ERROR at line 1:
ORA-01276: Cannot add file
/u01/app/oracle/oradata/PRODCDB/PDBPROD4/o1_mf_system_kkxp16yn_.dbf.  File has 
an Oracle Managed Files file name.

启用了OMF，还是不行

三、测试重命名数据文件

SQL> alter database move datafile '/u01/app/oracle/oradata/PROD4/datafile/o1_mf_system_kkxp16yn_.dbf' to '/u01/app/oracle/oradata/PROD4/datafile/system01.dbf';


Database altered.

SQL> SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD4/datafile/system01.dbf
/u01/app/oracle/oradata/PROD4/datafile/o1_mf_sysaux_kkxp2b7o_.dbf
/u01/app/oracle/oradata/PROD4/datafile/o1_mf_undotbs1_kkxp33fj_.dbf
/u01/app/oracle/oradata/PROD4/datafile/o1_mf_users_kkxp34lm_.dbf

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD4/datafile/system01.dbf
/u01/app/oracle/oradata/PROD4/datafile/sysaux.dbf
/u01/app/oracle/oradata/PROD4/datafile/undotbs1.dbf
/u01/app/oracle/oradata/PROD4/datafile/user01.dbf

select x.ksppinm name, y.kspftctxvl value, y.kspftctxdf isdefault, decode(bitand(y.kspftctxvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismod,
decode(bitand(y.kspftctxvf,2),2,'TRUE','FALSE') isadj from sys.x$ksppi x, sys.x$ksppcv2 y where x.inst_id = userenv('Instance')
and y.inst_id = userenv('Instance') and x.indx+1 = y.kspftctxpn and x.ksppinm like '%omf%' ;

NAME                      VALUE      ISDEFA ISMOD      ISADJ
------------------------- ---------- ------ ---------- -----
_omf                      enabled    TRUE   FALSE      FALSE


create pluggable database pdbprod4 from non$cdb@to_prod4 file_name_convert=('/u01/app/oracle/oradata/PROD4/datafile/','/u01/app/oracle/oradata/PRODCDB/PDBPROD4/') nologging;

grant create pluggable database to system;

ERROR at line 1:
ORA-01276: Cannot add file
/u01/app/oracle/oradata/PRODCDB/PDBPROD4/o1_mf_system_kkxp16yn_.dbf.  File has 
an Oracle Managed Files file name.
看来是目标库的问题

四、修改目标库参数

SQL> alter system set db_create_file_dest='/u01/app/oracle/oradata/PRODCDB/PDBPROD4';

System altered.

SQL> create pluggable database pdbprod4 from non$cdb@to_prod4;

Pluggable database created.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBPROD1                       MOUNTED
         5 PDBPROD4                       MOUNTED
SQL> alter pluggable database pdbprod4 open;

Warning: PDB altered with errors.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBPROD1                       MOUNTED
         5 PDBPROD4                       READ WRITE YES
修改完参数就不用file_name_convert

五、执行脚本

SQL> alter session set container=pdbprod4;

Session altered.

SQL> @?/rdbms/admin/noncdb_to_pdb.sql
SQL> SET FEEDBACK 1
SQL> SET NUMWIDTH 10
SQL> SET LINESIZE 80
SQL> SET TRIMSPOOL ON
SQL> SET TAB OFF
SQL> SET PAGESIZE 100
SQL> SET VERIFY OFF
SQL>
SQL> -- save settings
SQL> STORE SET ncdb2pdb.settings.sql REPLACE
Wrote file ncdb2pdb.settings.sql
SQL>
SQL> SET TIME ON
16:13:31 SQL> SET TIMING ON
16:13:31 SQL>
16:13:31 SQL> WHENEVER SQLERROR EXIT;
16:13:31 SQL>
16:13:31 SQL> DOC
16:13:31 DOC>#######################################################################
16:13:31 DOC>#######################################################################
16:13:31 DOC>   The following statement will cause an "ORA-01403: no data found"
16:13:31 DOC>   error if we're not in a PDB.
16:13:31 DOC>   This script is intended to be run right after plugin of a PDB,
16:13:31 DOC>   while inside the PDB.
16:13:31 DOC>########################################################


SQL> alter session set container=cdb$root;

Session altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBPROD1                       MOUNTED
         5 PDBPROD4                       READ WRITE YES

SQL> alter pluggable database pdbprod4 close;

Pluggable database altered.

SQL> alter pluggable database pdbprod4 open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBPROD1                       MOUNTED
         5 PDBPROD4                       READ WRITE NO