12c/19c unplug/plug迁移PDB

习狐若

2023-12-01

参考链接：
https://www.cndba.cn/dave/article/223
https://blog.csdn.net/ogdkevin/article/details/83106794
源端：

OS：Red Hat Enterprise Linux Server release 7.9 (Maipo)
SOFTWARE：grid+db（19.3）
IP：192.168.168.51/52

目标端

OS：Red Hat Enterprise Linux Server release 7.9 (Maipo)
SOFTWARE：db（19.3）
IP：192.168.168.59

说明
在Oracle 12c的CDB架构中，可以对PDB进行unplug和plug。就是把一个库从CDB的架构拔出，然后在附加到CDB中。那么可以利用这个特性，在2个不同的数据库之间进行数据迁移。
源库：

SQL> show pdbs;

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 		PDB$SEED		  READ ONLY  	NO
	 3		PDB1 			  READ WRITE 	NO
	 4 		PLUGDB			  READ WRITE 	NO

目标库：

SQL> show pdbs;

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 		PDB$SEED		  READ ONLY  	NO
	 3 		PDB1 			  READ WRITE 	NO

目的：就是把PLUGDB这个PDB从源库unplug下来，然后plug到目标库上。
在源库进行unplug操作
先查看PLUGDB这个PDB数据文件的信息：

SQL> alter session set container=plugdb;

Session altered.

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
+DATA/DB19C/DF5AEB91EE8B6DCDE05333A8A8C07469/DATAFILE/system.279.1105118585
+DATA/DB19C/DF5AEB91EE8B6DCDE05333A8A8C07469/DATAFILE/sysaux.281.1105118587
+DATA/DB19C/DF5AEB91EE8B6DCDE05333A8A8C07469/DATAFILE/undotbs1.280.1105118585
+DATA/DB19C/DF5AEB91EE8B6DCDE05333A8A8C07469/DATAFILE/undo_2.283.1105118717

关闭PDB，并unplug：

SQL> alter pluggable database plugdb close immediate instances=all;

Pluggable database altered.

SQL> alter pluggable database plugdb unplug into '/u01/unplug/plugdb.xml';

Pluggable database altered.

SQL> drop pluggable database plugdb keep datafiles;

Pluggable database dropped.

SQL> show pdbs;

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDB1 			  READ WRITE NO

在目标库进行plug操作
目标库与源库可以有相同的目录结构，当然也可以不同。
把源库上PLUGDB对应的所有数据文件，和含有元数据的XML文件全部scp过去：
源库：

chown -R grid:oinstall /u01/unplug
ASMCMD> pwd 
+data/db19c/DF5AEB91EE8B6DCDE05333A8A8C07469/datafile
ASMCMD> cp SYSAUX.281.1105118587 SYSTEM.279.1105118585 UNDOTBS1.280.1105118585 UNDO_2.283.1105118717 /u01/unplug
copying +data/db19c/DF5AEB91EE8B6DCDE05333A8A8C07469/datafile/SYSAUX.281.1105118587 -> /u01/unplug/SYSAUX.281.1105118587
copying +data/db19c/DF5AEB91EE8B6DCDE05333A8A8C07469/datafile/SYSTEM.279.1105118585 -> /u01/unplug//SYSTEM.279.1105118585
copying +data/db19c/DF5AEB91EE8B6DCDE05333A8A8C07469/datafile/UNDOTBS1.280.1105118585 -> /u01/unplug//UNDOTBS1.280.1105118585
copying +data/db19c/DF5AEB91EE8B6DCDE05333A8A8C07469/datafile/UNDO_2.283.1105118717 -> /u01/unplug//UNDO_2.283.1105118717
chown -R oracle:oinstall /u01/unplug
su - oracle
scp /u01/unplug/plugdb.xml 192.168.168.59:/u01/plug
plugdb.xml                                                                                                                                        100% 7958     1.1MB/s   00:00 
[oracle@19crac1:/u01/unplug]$ pwd
/u01/unplug
[oracle@19crac1:/u01/unplug]$ ls -lrht
total 821M
-rw-r--r-- 1 oracle oinstall 7.8K May 20 09:52 plugdb.xml
-rw-r----- 1 oracle oinstall 341M May 20 09:59 SYSAUX.281.1105118587
-rw-r----- 1 oracle oinstall 281M May 20 09:59 SYSTEM.279.1105118585
-rw-r----- 1 oracle oinstall 101M May 20 09:59 UNDOTBS1.280.1105118585
-rw-r----- 1 oracle oinstall 101M May 20 09:59 UNDO_2.283.1105118717
scp *.2* 192.168.168.59:/u01/app/oracle/oradata/DB19C/plugdb
SYSAUX.281.1105118587                                                                                                                             100%  340MB  22.0MB/s   00:15    
SYSTEM.279.1105118585                                                                                                                             100%  280MB  21.5MB/s   00:13    
UNDO_2.283.1105118717                                                                                                                             100%  100MB  20.0MB/s   00:05    
UNDOTBS1.280.1105118585                                                                                                                           100%  100MB  17.0MB/s   00:05

在目标库验证待插入PDB的兼容性

SET SERVEROUTPUT ON
DECLARE
  l_result BOOLEAN;
BEGIN
  l_result := DBMS_PDB.check_plug_compatibility(
                pdb_descr_file => '/u01/plug/plugdb.xml',
                pdb_name       => 'plugdb');
  IF l_result THEN
    DBMS_OUTPUT.PUT_LINE('compatible');
  ELSE
    DBMS_OUTPUT.PUT_LINE('incompatible');
  END IF;
END;
/
compatible

PL/SQL procedure successfully completed.

在目标库上plug

SQL> create pluggable database plugdb using '/u01/plug/plugdb.xml' source_file_directory='/u01/app/oracle/oradata/DB19C/plugdb' nocopy tempfile reuse;

Pluggable database created.

SQL> show pdbs;

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDB1 			  READ WRITE NO
	 4 PLUGDB			  MOUNTED

打开PDB并检查插入的PDB是否有问题，查看PDB_PLUG_IN_VIOLATIONS表

SQL> alter pluggable database plugdb open instances=all;

Pluggable database altered.

SQL> show pdbs;

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDB1 			  READ WRITE NO
	 4 PLUGDB			  READ WRITE NO

要处理一下stauts是PENDING的

set lin 200
col name for a10 
col cause for a20 
col message for a100 
select name,cause,type,message,status from pdb_plug_in_violations order by name;
NAME	   CAUSE		TYPE	  MESSAGE											       STATUS
---------- -------------------- --------- ---------------------------------------------------------------------------------------------------- ---------
PDB$SEED   SQL Patch		ERROR	  '19.3.0.0.0 Release_Update 1904101227' is installed in the CDB but no release updates are installed  RESOLVED
					  in the PDB

PLUGDB	   OPTION		WARNING   Database option RAC mismatch: PDB installed version 19.0.0.0.0. CDB installed version NULL.	       PENDING
PLUGDB	   Parameter		WARNING   CDB parameter _ipddb_enable mismatch: Previous TRUE Current FALSE				       PENDING
PLUGDB	   Parameter		WARNING   CDB parameter cluster_database mismatch: Previous TRUE Current FALSE				       PENDING
PLUGDB	   Parameter		WARNING   CDB parameter pga_aggregate_target mismatch: Previous 374M Current 410M			       PENDING

切换到PLUGDB，关掉RAC选项

SQL> alter session set container=plugdb;

Session altered.

SQL> exec dbms_registry.OPTION_OFF('RAC');

PL/SQL procedure successfully completed.

SQL> alter pluggable database plugdb close immediate;

Pluggable database altered.

SQL> alter pluggable database plugdb open;

Pluggable database altered.

再看已经没有需要处理的警告了

set lin 200
col name for a10 
col cause for a20 
col message for a100 
SQL> select name,cause,type,message,status from pdb_plug_in_violations order by name;

NAME	   CAUSE		TYPE	  MESSAGE											       STATUS
---------- -------------------- --------- ---------------------------------------------------------------------------------------------------- ---------
PDB$SEED   SQL Patch		ERROR	  '19.3.0.0.0 Release_Update 1904101227' is installed in the CDB but no release updates are installed  RESOLVED
					  in the PDB

PLUGDB	   OPTION		WARNING   Database option RAC mismatch: PDB installed version 19.0.0.0.0. CDB installed version NULL.	       RESOLVED
PLUGDB	   Parameter		WARNING   CDB parameter _ipddb_enable mismatch: Previous TRUE Current FALSE				       RESOLVED
PLUGDB	   Parameter		WARNING   CDB parameter cluster_database mismatch: Previous TRUE Current FALSE				       RESOLVED
PLUGDB	   Parameter		WARNING   CDB parameter pga_aggregate_target mismatch: Previous 374M Current 410M			       RESOLVED

刚刚在源库执行了drop pluggable database plugdb keep datafiles;数据文件还是在的，XML文件也没有删除，现在在源库恢复PLUGDB
源库：

ASMCMD> pwd
+data/db19c/DF5AEB91EE8B6DCDE05333A8A8C07469/datafile
ASMCMD> ls -l
Type      Redund  Striped  Time             Sys  Name
DATAFILE  UNPROT  COARSE   MAY 20 09:00:00  Y    SYSAUX.281.1105118587
DATAFILE  UNPROT  COARSE   MAY 20 09:00:00  Y    SYSTEM.279.1105118585
DATAFILE  UNPROT  COARSE   MAY 20 09:00:00  Y    UNDOTBS1.280.1105118585
DATAFILE  UNPROT  COARSE   MAY 20 09:00:00  Y    UNDO_2.283.1105118717

[oracle@19crac1:/home/oracle]$ ls -lrth /u01/unplug/
total 821M
-rw-r--r-- 1 oracle oinstall 7.8K May 20 09:52 plugdb.xml

SQL>show pdbs;

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDB1 			  READ WRITE NO

在源库plug：

SQL> create pluggable database plugdb using '/u01/unplug/plugdb.xml';     

Pluggable database created.

SQL> show pdbs;

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDB1 			  READ WRITE NO
	 4 PLUGDB			  MOUNTED
SQL> alter pluggable database plugdb open instances=all;

Pluggable database altered.

SQL> set lin 200
col name for a10 
col cause for a20 
col message for a100 SQL> SQL> SQL> 
SQL> select name,cause,type,message,status from pdb_plug_in_violations order by name;

NAME	   CAUSE		TYPE	  MESSAGE											       STATUS
---------- -------------------- --------- ---------------------------------------------------------------------------------------------------- ---------
PDB$SEED   SQL Patch		ERROR	  '19.3.0.0.0 Release_Update 1904101227' is installed in the CDB but no release updates are installed  RESOLVED
					  in the PDB

12c/19c unplug/plug迁移PDB

相关阅读

相关文章

相关问答

相关文档