环境:
主库:2节点RAC
从库:单节点
db：19C
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
Data Guard Impact on Oracle Multitenant Environments (Doc ID 2049127.1)
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
