The MOS docs on the matter (2273304.1) creating aliases when ASM is used for storage. Another MOS note (2274735.1) shows using the above parameter when cloning PDBs.
This post shows the steps for plugging in a non-CDB in data gaurd configuration and resuing the standby data files of the non-CDB with the help of STANDBY_PDB_SOURCE_FILE_DIRECTORY parameter. The database reference documentation states
the STANDBY_PDB_SOURCE_FILE_DIRECTORY specifies a directory location on the standby where source datafiles for instantiating the PDB may be found. If the datafiles are not found there, an attempt will be made to locate them in the OMF location on the standby. This parameter can also be used to try to copy files in cases where the source PDB's files are not present on the standby.Some information on the setup, both non-CDB and CDB are running same version (19.10 in this case). Two cases are shown in this post are one using file system with OMF and another where ASM is used for storage. TDE is not used in this configuration nor on the CDB where non-CDB is plugged in.
The non-CDB data guard configuration is as follows.
DGMGRL> show configuration;
Configuration - noncdb_dg
Protection Mode: MaxAvailability
Members:
noncdb - Primary database
noncdb2 - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 50 seconds ago)If upgrading from a lower version this could be thought of as a data guard configuration that is already upgraded (with dbms_rolling, transient logical standby or traditional method with down time) to 19c.The steps for plugging in the non-CDB as a PDB on primary is same as without data guard (however, autoupgrade was not used for this and non-CDB was plugged in manually). But there are few key things to make a note of. When the primary non-CDB is open in read only mode to create the descriptor file make sure that checkpoint_change# on primary and standby datafiles match.
On primary non-CDB
shutdown imemdiate;
startup mount;
alter database open read only;
select CHECKPOINT_CHANGE#,name from v$datafile;
CHECKPOINT_CHANGE# NAME
------------------ -------------------------------------------------------
858807 /opt/data/NONCDB/datafile/o1_mf_system_j2tgk6do_.dbf
858807 /opt/data/NONCDB/datafile/o1_mf_sysaux_j2tgkgg8_.dbf
858807 /opt/data/NONCDB/datafile/o1_mf_undotbs1_j2tgklqr_.dbf
858807 /opt/data/NONCDB/datafile/o1_mf_users_j2tgl0j3_.dbfCheck the same on standby non-CDBselect CHECKPOINT_CHANGE#,name from v$datafile;
CHECKPOINT_CHANGE# NAME
------------------ --------------------------------------------------------
858807 /opt/data/NONCDB2/datafile/o1_mf_system_09vne4sm_.dbf
858807 /opt/data/NONCDB2/datafile/o1_mf_sysaux_0avne4t5_.dbf
858807 /opt/data/NONCDB2/datafile/o1_mf_undotbs1_0bvne4tk_.dbf
858807 /opt/data/NONCDB2/datafile/o1_mf_users_0cvne4tr_.dbfIn this case a simple clean shutdown was enough to make both sets of data files to have same checkpoint change number (the fact protection mode being max availability mode may have something to do with this). If the checkpoint change numbers are different then follow 2273304.1 to flush redo and recover the standby non-CDB data files to the same checkpoint change number as the primary non-CDB data files.Create the non-CDB descriptor file and shutdown both primary and standby non-CDB databases.
On primary
exec dbms_pdb.describe(pdb_descr_file=>'/home/oracle/noncdb.xml'); PL/SQL procedure successfully completed. SQL> shutdown immediate;On standby
SQL> shutdown immediate;
The CDB data guard configuration details are as follows
DGMGRL> show configuration
Configuration - dg_19c_fs
Protection Mode: MaxAvailability
Members:
testfs - Primary database
testfs2 - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 59 seconds ago)Run the compatability check on the primary CDB.set serveroutput on
declare
compa boolean;
begin
compa := dbms_pdb.CHECK_PLUG_COMPATIBILITY(pdb_descr_file=>'/home/oracle/noncdb.xml');
if compa = true then
dbms_output.put_line('compatible');
else
dbms_output.put_line('not compatible');
end if;
end;
/
compatibleBefore creating the PDB set the standby_pdb_source_file_directory on the standby CDB to the location where data files of the non-CDB standby resides. If the data files of the non-CDB are on the file system (with OMF) then set the standby_pdb_source_file_directory similar to SQL> alter system set standby_pdb_source_file_directory='/opt/data/NONCDB2/datafile' scope=both; System altered.If standby non-CDB was using ASM then set similar to below.
SQL> alter system set standby_pdb_source_file_directory='+DATA/NONCDB2/DATAFILE' scope=both; System altered.Once the standby_pdb_source_file_directory are set then plug in the non-CDB as a PDB on the primary.
CREATE PLUGGABLE DATABASE noncdbaspdb USING '/home/oracle/noncdb.xml' copy;While plug in is happening the standby alert log will show files being copied from the standby_pdb_source_file_directory location to the OMF locations. Following is from standby alert log when file system is used.
2021-02-17T17:23:25.952694+00:00 Recovery created pluggable database NONCDBASPDB Recovery attempting to copy datafiles for pdb-NONCDBASPDB from source dir-/opt/data/NONCDB2/datafile 2021-02-17T17:23:30.951094+00:00 Recovery copied files for tablespace SYSTEM Recovery successfully copied file /opt/data/TESTFS2/BB6378EF1A471937E053F00C1FAC6AAD/datafile/o1_mf_system_j2tn81ol_.dbf from /opt/data/NONCDB2/datafile/o1_mf_system_09vne4sm_.dbf NONCDBASPDB(4):Datafile 98 added to flashback set NONCDBASPDB(4):Successfully added datafile 98 to media recovery NONCDBASPDB(4):Datafile #98: '/opt/data/TESTFS2/BB6378EF1A471937E053F00C1FAC6AAD/datafile/o1_mf_system_j2tn81ol_.dbf' 2021-02-17T17:23:35.458745+00:00 Recovery copied files for tablespace SYSAUX Recovery successfully copied file /opt/data/TESTFS2/BB8B1CFF497166F8E053F00C1FACF64E/datafile/o1_mf_sysaux_j2tn81ox_.dbf from /opt/data/NONCDB2/datafile/o1_mf_sysaux_0avne4t5_.dbf NONCDBASPDB(4):Datafile 99 added to flashback set NONCDBASPDB(4):Successfully added datafile 99 to media recovery NONCDBASPDB(4):Datafile #99: '/opt/data/TESTFS2/BB8B1CFF497166F8E053F00C1FACF64E/datafile/o1_mf_sysaux_j2tn81ox_.dbf' 2021-02-17T17:23:37.428931+00:00 Recovery copied files for tablespace UNDOTBS1 Recovery successfully copied file /opt/data/TESTFS2/BB8B1CFF497166F8E053F00C1FACF64E/datafile/o1_mf_undotbs1_j2tn81oz_.dbf from /opt/data/NONCDB2/datafile/o1_mf_undotbs1_0bvne4tk_.dbf NONCDBASPDB(4):Datafile 100 added to flashback set NONCDBASPDB(4):Successfully added datafile 100 to media recovery NONCDBASPDB(4):Datafile #100: '/opt/data/TESTFS2/BB8B1CFF497166F8E053F00C1FACF64E/datafile/o1_mf_undotbs1_j2tn81oz_.dbf' Recovery copied files for tablespace USERS Recovery successfully copied file /opt/data/TESTFS2/BB8B1CFF497166F8E053F00C1FACF64E/datafile/o1_mf_users_j2tn81p0_.dbf from /opt/data/NONCDB2/datafile/o1_mf_users_0cvne4tr_.dbf NONCDBASPDB(4):Datafile 101 added to flashback set NONCDBASPDB(4):Successfully added datafile 101 to media recovery NONCDBASPDB(4):Datafile #101: '/opt/data/TESTFS2/BB8B1CFF497166F8E053F00C1FACF64E/datafile/o1_mf_users_j2tn81p0_.dbf'
On the otherhand if ASM is used expect something similar to below on the alert log.
2021-02-18T13:06:46.114728+00:00 Recovery created pluggable database NONCDBASPDB Recovery attempting to copy datafiles for pdb-NONCDBASPDB from source dir-+DATA/NONCDB2/DATAFILE 2021-02-18T13:06:46.240880+00:00 NOTE: dependency between database testfs2 and diskgroup resource ora.DATA.dg is established 2021-02-18T13:06:55.953110+00:00 Recovery copied files for tablespace SYSTEM Recovery successfully copied file +DATA/TESTFS2/BB6378EF1A471937E053F00C1FAC6AAD/DATAFILE/system.262.1064840807 from +DATA/NONCDB2/DATAFILE/system.256.1064839675 NONCDBASPDB(5):Datafile 111 added to flashback set NONCDBASPDB(5):Successfully added datafile 111 to media recovery NONCDBASPDB(5):Datafile #111: '+DATA/TESTFS2/BB6378EF1A471937E053F00C1FAC6AAD/DATAFILE/system.262.1064840807' 2021-02-18T13:07:04.357805+00:00 Recovery copied files for tablespace SYSAUX Recovery successfully copied file +DATA/TESTFS2/BB8B1CFF497166F8E053F00C1FACF64E/DATAFILE/sysaux.265.1064840817 from +DATA/NONCDB2/DATAFILE/sysaux.257.1064839687 NONCDBASPDB(5):Datafile 112 added to flashback set NONCDBASPDB(5):Successfully added datafile 112 to media recovery NONCDBASPDB(5):Datafile #112: '+DATA/TESTFS2/BB8B1CFF497166F8E053F00C1FACF64E/DATAFILE/sysaux.265.1064840817' 2021-02-18T13:07:08.042371+00:00 Recovery copied files for tablespace UNDOTBS1 Recovery successfully copied file +DATA/TESTFS2/BB8B1CFF497166F8E053F00C1FACF64E/DATAFILE/undotbs1.263.1064840825 from +DATA/NONCDB2/DATAFILE/undotbs1.258.1064839695 NONCDBASPDB(5):Datafile 113 added to flashback set NONCDBASPDB(5):Successfully added datafile 113 to media recovery NONCDBASPDB(5):Datafile #113: '+DATA/TESTFS2/BB8B1CFF497166F8E053F00C1FACF64E/DATAFILE/undotbs1.263.1064840825' Recovery copied files for tablespace USERS Recovery successfully copied file +DATA/TESTFS2/BB8B1CFF497166F8E053F00C1FACF64E/DATAFILE/users.275.1064840829 from +DATA/NONCDB2/DATAFILE/users.259.1064839697 NONCDBASPDB(5):Datafile 114 added to flashback set NONCDBASPDB(5):Successfully added datafile 114 to media recovery NONCDBASPDB(5):Datafile #114: '+DATA/TESTFS2/BB8B1CFF497166F8E053F00C1FACF64E/DATAFILE/users.275.1064840829' Recovery copied files for tablespace TEST Recovery successfully copied file +DATA/TESTFS2/BB8B1CFF497166F8E053F00C1FACF64E/DATAFILE/test.276.1064840829 from +DATA/NONCDB2/DATAFILE/test.260.1064839883 NONCDBASPDB(5):Datafile 115 added to flashback set NONCDBASPDB(5):Successfully added datafile 115 to media recovery NONCDBASPDB(5):Datafile #115: '+DATA/TESTFS2/BB8B1CFF497166F8E053F00C1FACF64E/DATAFILE/test.276.1064840829'
Complete the non-CDB plug in by running noncdb_to_pdb script.
SQL> alter session set container=NONCDBASPDB;
Session altered.
SQL> @?/rdbms/admin/noncdb_to_pdb.sql
on primary
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 NONCDBASPDB MOUNTED
SQL> alter pluggable database open;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 NONCDBASPDB READ WRITE NOOn the standby the new PDB will be in mount state.SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED MOUNTED
3 TESTPDB MOUNTED
4 NONCDBASPDB MOUNTEDTo test if all is working fine, create a test tablespace on the PDB. First identify the GUID of the newly created PDB.SQL> select guid,name from v$pdbs; GUID NAME -------------------------------- ------------- BB8B1CFF497166F8E053F00C1FACF64E NONCDBASPDBCreate a tablespace on the primary.
SQL> create tablespace test datafile size 10m; Tablespace created.Check the standby alert log for the corresponding datafile being created inside the same GUID.
2021-02-17T17:29:28.946404+00:00 (4):Resize operation completed for file# 100, old size 424960K, new size 430080K 2021-02-17T17:32:20.962105+00:00 (4):Datafile 102 added to flashback set (4):Successfully added datafile 102 to media recovery (4):Datafile #102: '/opt/data/TESTFS2/BB8B1CFF497166F8E053F00C1FACF64E/datafile/o1_mf_test_j2tns4w1_.dbf'If possible do a switchover and open the newly created PDB in read-write mode.
DGMGRL> switchover to testfs2;
Performing switchover NOW, please wait...
Operation requires a connection to database "testfs2"
Connecting ...
Connected to "testfs2"
Connected as SYSDBA.
New primary database "testfs2" is opening...
Oracle Clusterware is restarting database "testfs" ...
Connected to "testfs"
Connected to "testfs"
Switchover succeeded, new primary is "testfs2"
DGMGRL> show configuration
Configuration - dg_19c_fs
Protection Mode: MaxAvailability
Members:
testfs2 - Primary database
testfs - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 60 seconds ago)PDB status on old standby (new primary)SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TESTPDB READ WRITE NO
4 NONCDBASPDB READ WRITE NOThis post has shown how standby_pdb_source_file_directory could be used to specify the location of the data files in standby and reuse them. It also showed when ASM is used no need to create aliases anymore.
Related Posts
Plugging non-CDB as a PDB - Manual vs Autoupgrade
Plugging non-CDB as a PDB into a CDB in the Same Host
Creating a PDB in a Data Guard Configuration
Useful metalink notes
Reusing the Source Standby Database Files When Plugging a non-CDB as a PDB into the Primary Database of a Data Guard Configuration [ID 2273304.1]
Using standby_pdb_source_file_dblink and standby_pdb_source_file_directory to Maintain Standby Databases when Performing PDB Remote Clones or Plugins [ID 2274735.1]
