环境:
主库:2 节点RAC
从库:单节点
db：18c
1.主库上新增了一个pdb
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 READ WRITE NO
SQL> create pluggable database pdb3 from pdb1;
2.从库发现文件名没有转换
SQL> set linesize 5000; SQL> column CON_ID format 99; SQL> column guid format a32; SQL> column name format a8; SQL> column file_id format 99; SQL> column file_name format a128; SQL> select a.CON_ID, null as guid, 'CDB' as name, file_id, file_name 2 from cdb_data_files a 3 where a.CON_ID = 1 4 union all 5 select a.CON_ID, a.guid, a.name, b.FILE#, b.NAME 6 from v$pdbs a, v$datafile b 7 where a.CON_ID = b.CON_ID 8 order by name; CON_ID GUID NAME FILE_ID FILE_NAME ------ -------------------------------- -------- ------- -------------------------------------------------------------------------------------------------------------------------------- 1 CDB 7 /u01/app/oracle/oradata/ora18c/users01.dbf 1 CDB 4 /u01/app/oracle/oradata/ora18c/undotbs1_01.dbf 1 CDB 3 /u01/app/oracle/oradata/ora18c/sysaux01.dbf 1 CDB 1 /u01/app/oracle/oradata/ora18c/system01.dbf 1 CDB 9 /u01/app/oracle/oradata/ora18c/undotbs2_01.dbf 1 CDB 22 /u01/app/oracle/oradata/ora18c/tps_cdb_hxl.398.1073357031 2 C21CA002E8FB24FDE0536F38A8C0D830 PDB$SEED 5 /u01/app/oracle/oradata/ora18c/pdbseed/system01.dbf 2 C21CA002E8FB24FDE0536F38A8C0D830 PDB$SEED 6 /u01/app/oracle/oradata/ora18c/pdbseed/sysaux01.dbf 2 C21CA002E8FB24FDE0536F38A8C0D830 PDB$SEED 8 /u01/app/oracle/oradata/ora18c/pdbseed/undotbs01.dbf 3 C21CCC2F26B343B4E0536F38A8C01387 PDB1 13 /u01/app/oracle/oradata/ora18c/pdb1/undo_2.dbf 3 C21CCC2F26B343B4E0536F38A8C01387 PDB1 12 /u01/app/oracle/oradata/ora18c/pdb1/undotbs1.dbf CON_ID GUID NAME FILE_ID FILE_NAME ------ -------------------------------- -------- ------- -------------------------------------------------------------------------------------------------------------------------------- 3 C21CCC2F26B343B4E0536F38A8C01387 PDB1 11 /u01/app/oracle/oradata/ora18c/pdb1/sysaux01.dbf 3 C21CCC2F26B343B4E0536F38A8C01387 PDB1 10 /u01/app/oracle/oradata/ora18c/pdb1/system01.dbf 3 C21CCC2F26B343B4E0536F38A8C01387 PDB1 14 /u01/app/oracle/oradata/ora18c/pdb1/users01.dbf 3 C21CCC2F26B343B4E0536F38A8C01387 PDB1 21 /u01/app/oracle/oradata/ora18c/pdb1/tps_hxl.331.1072888977 3 C21CCC2F26B343B4E0536F38A8C01387 PDB1 20 /u01/app/oracle/oradata/ora18c/pdb1/tps_hxl.dbf 4 C21CD091742C46B3E0536F38A8C09100 PDB2 15 /u01/app/oracle/oradata/ora18c/pdb2/system01.dbf 4 C21CD091742C46B3E0536F38A8C09100 PDB2 16 /u01/app/oracle/oradata/ora18c/pdb2/sysaux01.dbf 4 C21CD091742C46B3E0536F38A8C09100 PDB2 19 /u01/app/oracle/oradata/ora18c/pdb2/users01.dbf 4 C21CD091742C46B3E0536F38A8C09100 PDB2 18 /u01/app/oracle/oradata/ora18c/pdb2/undo_2.dbf 4 C21CD091742C46B3E0536F38A8C09100 PDB2 17 /u01/app/oracle/oradata/ora18c/pdb2/undotbs1.dbf 5 C30F698489920BA1E0536F38A8C0B746 PDB3 26 /u01/app/oracle/product/18.0.0.0/db_1/dbs/UNNAMED00026 CON_ID GUID NAME FILE_ID FILE_NAME ------ -------------------------------- -------- ------- -------------------------------------------------------------------------------------------------------------------------------- 5 C30F698489920BA1E0536F38A8C0B746 PDB3 25 /u01/app/oracle/product/18.0.0.0/db_1/dbs/UNNAMED00025 5 C30F698489920BA1E0536F38A8C0B746 PDB3 24 /u01/app/oracle/product/18.0.0.0/db_1/dbs/UNNAMED00024 5 C30F698489920BA1E0536F38A8C0B746 PDB3 29 /u01/app/oracle/product/18.0.0.0/db_1/dbs/UNNAMED00029 5 C30F698489920BA1E0536F38A8C0B746 PDB3 28 /u01/app/oracle/product/18.0.0.0/db_1/dbs/UNNAMED00028 5 C30F698489920BA1E0536F38A8C0B746 PDB3 27 /u01/app/oracle/product/18.0.0.0/db_1/dbs/UNNAMED00027 5 C30F698489920BA1E0536F38A8C0B746 PDB3 23 /u01/app/oracle/product/18.0.0.0/db_1/dbs/UNNAMED00023 28 rows selected.
3.主库上查看文件id与文件名的关系
SQL> set linesize 5000; SQL> column CON_ID format 99; SQL> column guid format a32; SQL> column name format a8; SQL> column file_id format 99; SQL> column file_name format a128; SQL> select a.CON_ID, null as guid, 'CDB' as name, file_id, file_name 2 from cdb_data_files a 3 where a.CON_ID = 1 4 union all 5 select a.CON_ID, a.guid, a.name, b.FILE#, b.NAME 6 from v$pdbs a, v$datafile b 7 where a.CON_ID = b.CON_ID 8 order by name; CON_ID GUID NAME FILE_ID FILE_NAME ------ -------------------------------- -------- ------- -------------------------------------------------------------------------------------------------------------------------------- 1 CDB 1 +DATA/SLNNGK/DATAFILE/system.260.1072316819 1 CDB 22 +DATA/SLNNGK/DATAFILE/tps_cdb_hxl.398.1073357031 1 CDB 9 +DATA/SLNNGK/DATAFILE/undotbs2.279.1072317491 1 CDB 7 +DATA/SLNNGK/DATAFILE/users.257.1072316921 1 CDB 4 +DATA/SLNNGK/DATAFILE/undotbs1.258.1072316919 1 CDB 3 +DATA/SLNNGK/DATAFILE/sysaux.259.1072316885 2 C21CA002E8FB24FDE0536F38A8C0D830 PDB$SEED 5 +DATA/SLNNGK/64A52F53A7693286E053CDA9E80AED76/DATAFILE/system.276.1072317193 2 C21CA002E8FB24FDE0536F38A8C0D830 PDB$SEED 6 +DATA/SLNNGK/64A52F53A7693286E053CDA9E80AED76/DATAFILE/sysaux.275.1072317193 2 C21CA002E8FB24FDE0536F38A8C0D830 PDB$SEED 8 +DATA/SLNNGK/64A52F53A7693286E053CDA9E80AED76/DATAFILE/undotbs1.277.1072317193 3 C21CCC2F26B343B4E0536F38A8C01387 PDB1 10 +DATA/SLNNGK/C21CCC2F26B343B4E0536F38A8C01387/DATAFILE/system.289.1072317933 3 C21CCC2F26B343B4E0536F38A8C01387 PDB1 21 +DATA/SLNNGK/C21CCC2F26B343B4E0536F38A8C01387/DATAFILE/tps_hxl.331.1072888977 CON_ID GUID NAME FILE_ID FILE_NAME ------ -------------------------------- -------- ------- -------------------------------------------------------------------------------------------------------------------------------- 3 C21CCC2F26B343B4E0536F38A8C01387 PDB1 20 +DATA/SLNNGK/C21CCC2F26B343B4E0536F38A8C01387/DATAFILE/tps_hxl.317.1072505347 3 C21CCC2F26B343B4E0536F38A8C01387 PDB1 11 +DATA/SLNNGK/C21CCC2F26B343B4E0536F38A8C01387/DATAFILE/sysaux.290.1072317933 3 C21CCC2F26B343B4E0536F38A8C01387 PDB1 12 +DATA/SLNNGK/C21CCC2F26B343B4E0536F38A8C01387/DATAFILE/undotbs1.288.1072317933 3 C21CCC2F26B343B4E0536F38A8C01387 PDB1 13 +DATA/SLNNGK/C21CCC2F26B343B4E0536F38A8C01387/DATAFILE/undo_2.292.1072317981 3 C21CCC2F26B343B4E0536F38A8C01387 PDB1 14 +DATA/SLNNGK/C21CCC2F26B343B4E0536F38A8C01387/DATAFILE/users.293.1072317987 4 C21CD091742C46B3E0536F38A8C09100 PDB2 15 +DATA/SLNNGK/C21CD091742C46B3E0536F38A8C09100/DATAFILE/system.295.1072318011 4 C21CD091742C46B3E0536F38A8C09100 PDB2 19 +DATA/SLNNGK/C21CD091742C46B3E0536F38A8C09100/DATAFILE/users.299.1072318065 4 C21CD091742C46B3E0536F38A8C09100 PDB2 18 +DATA/SLNNGK/C21CD091742C46B3E0536F38A8C09100/DATAFILE/undo_2.298.1072318061 4 C21CD091742C46B3E0536F38A8C09100 PDB2 17 +DATA/SLNNGK/C21CD091742C46B3E0536F38A8C09100/DATAFILE/undotbs1.294.1072318011 4 C21CD091742C46B3E0536F38A8C09100 PDB2 16 +DATA/SLNNGK/C21CD091742C46B3E0536F38A8C09100/DATAFILE/sysaux.296.1072318011 5 C30F698489920BA1E0536F38A8C0B746 PDB3 28 +DATA/SLNNGK/C30F698489920BA1E0536F38A8C0B746/DATAFILE/tps_hxl.401.1073359953 CON_ID GUID NAME FILE_ID FILE_NAME ------ -------------------------------- -------- ------- -------------------------------------------------------------------------------------------------------------------------------- 5 C30F698489920BA1E0536F38A8C0B746 PDB3 27 +DATA/SLNNGK/C30F698489920BA1E0536F38A8C0B746/DATAFILE/users.400.1073359953 5 C30F698489920BA1E0536F38A8C0B746 PDB3 26 +DATA/SLNNGK/C30F698489920BA1E0536F38A8C0B746/DATAFILE/undo_2.403.1073359953 5 C30F698489920BA1E0536F38A8C0B746 PDB3 25 +DATA/SLNNGK/C30F698489920BA1E0536F38A8C0B746/DATAFILE/undotbs1.402.1073359953 5 C30F698489920BA1E0536F38A8C0B746 PDB3 24 +DATA/SLNNGK/C30F698489920BA1E0536F38A8C0B746/DATAFILE/sysaux.406.1073359953 5 C30F698489920BA1E0536F38A8C0B746 PDB3 29 +DATA/SLNNGK/C30F698489920BA1E0536F38A8C0B746/DATAFILE/tps_hxl.404.1073359953 5 C30F698489920BA1E0536F38A8C0B746 PDB3 23 +DATA/SLNNGK/C30F698489920BA1E0536F38A8C0B746/DATAFILE/system.405.1073359953 28 rows selected.
4.从库上创建新的pdb目录
[root@18c_single ~]# su - oracle
[oracle@18c_single ~]$ mkdir -p /u01/app/oracle/oradata/ora18c/pdb3
5.文件转换
SQL> connect / as sysdba
alter system set standby_file_management=manual;
alter session set container=pdb3;
alter database create datafile '/u01/app/oracle/product/18.0.0.0/db_1/dbs/UNNAMED00026' as '/u01/app/oracle/oradata/ora18c/pdb3/undo_2.403.dbf';
alter database create datafile '/u01/app/oracle/product/18.0.0.0/db_1/dbs/UNNAMED00025' as '/u01/app/oracle/oradata/ora18c/pdb3/undotbs1.402.dbf';
alter database create datafile '/u01/app/oracle/product/18.0.0.0/db_1/dbs/UNNAMED00024' as '/u01/app/oracle/oradata/ora18c/pdb3/sysaux.406.dbf';
alter database create datafile '/u01/app/oracle/product/18.0.0.0/db_1/dbs/UNNAMED00029' as '/u01/app/oracle/oradata/ora18c/pdb3/tps_hxl.404.dbf';
alter database create datafile '/u01/app/oracle/product/18.0.0.0/db_1/dbs/UNNAMED00028' as '/u01/app/oracle/oradata/ora18c/pdb3/tps_hxl.401.dbf';
alter database create datafile '/u01/app/oracle/product/18.0.0.0/db_1/dbs/UNNAMED00027' as '/u01/app/oracle/oradata/ora18c/pdb3/users.400.dbf';
alter database create datafile '/u01/app/oracle/product/18.0.0.0/db_1/dbs/UNNAMED00023' as '/u01/app/oracle/oradata/ora18c/pdb3/system.405.dbf';
alter session set container=cdb$root;
alter system set standby_file_management=auto;
若是已经停掉应用日志的,需要重新应用
alter database recover managed standby database using current logfile disconnect from session;
6.尝试打开pdb3
SQL> alter pluggable database pdb3 open;
alter pluggable database pdb3 open
*
ERROR at line 1:
ORA-01147: SYSTEM tablespace file 23 is offline
alter database datafile 23 online;
7.需要恢复
SQL> alter session set container=cdb$root;
SQL>alter system set standby_file_management=auto;
SQL>alter database recover managed standby database cancel;
SQL> alter session set container=pdb3;
SQL>alter pluggable database disable recovery;
##tnsslnngk1 是连接到主库的tns
RMAN> run{
2> restore pluggable database pdb3 from service tnsslnngk1 ;
3> }
Starting restore at 24-MAY-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=82 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service tnsslnngk1
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00023 to /u01/app/oracle/oradata/ora18c/pdb3/system.405.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service tnsslnngk1
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00024 to /u01/app/oracle/oradata/ora18c/pdb3/sysaux.406.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service tnsslnngk1
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00025 to /u01/app/oracle/oradata/ora18c/pdb3/undotbs1.402.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service tnsslnngk1
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00026 to /u01/app/oracle/oradata/ora18c/pdb3/undo_2.403.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service tnsslnngk1
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00027 to /u01/app/oracle/oradata/ora18c/pdb3/users.400.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service tnsslnngk1
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00028 to /u01/app/oracle/oradata/ora18c/pdb3/tps_hxl.401.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service tnsslnngk1
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00029 to /u01/app/oracle/oradata/ora18c/pdb3/tps_hxl.404.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at 24-MAY-21
SQL> alter session set container=cdb$root;
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter session set container=pdb3;
SQL> alter pluggable database enable recovery;
SQL> alter session set container=cdb$root;
SQL> alter database recover managed standby database disconnect from session;
8.打开pdb
alter database recover managed standby database cancel;
alter database open;
alter database recover managed standby database disconnect from session;
select open_mode from v$database;
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
4 PDB2 MOUNTED
5 PDB3 MOUNTED
SQL> alter pluggable database all open;
Pluggable database altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ ONLY NO
4 PDB2 READ ONLY NO
5 PDB3 READ ONLY NO
经过分析发现,dataguard 环境下,只要 source pdb 是 READ ONLY,那么在primary 上执行创建pdb时,在 standby 上就能顺利创建.
重点关注下文档 ID 1916648.1