Prior to release 12.1.0.2, when adding a pluggable database in the primary of a Data Guard configuration, all files associated with the new PDB needed to be in place, accessible, in the correct state (the same state as the unplug left them) and in the expected location on the physical standby for redo apply to continue operations normally. If any of the requirements were missed, redo apply would stop and would not be allowed to continue until the issues were resolved and the files successfully added to the database controlfile. But when you do remote clone of a PDB, It is not possible to pre-copy the files to the physical standby database and ensure they will be in the correct state when the CREATE PLUGGABLE DATABASE statement redo is applied to the standby.
In 12.1.0.2, the CREATE PLUGGABLE DATABASE statement has a new clause, STANDBYS=NONE, that allows for deferral of file instantiation on the standby allowing the physical standby database to continue to protect existing pluggable databases. The clause allows the general structure of the PDB to be created on all physical standbys but all files belonging to the PDB are marked as OFFLINE/RECOVER at the standby. The affected PDB cannot be opened with the files in this state, although any other PDBs not in this state can be opened Read/Only with Active Data Guard as usual. You can check the recovery status of PDBs by querying V$PDBS and looking at the value of RECOVERY_STATUS which will show either DISABLED or ENABLED. You can check the status of the datafiles in V$RECOVER_FILE.ONLINE where CON_ID is equal to the container id of your PDB.
The problem is, that Oracle has to manage two different redo streams: the redo, that is generated during the hot clone of the PDB, and the normal redo transport to the standby database.
18c new feature -
New pluggable databases (PDBs) created from the seed, or as a clone of an existing read-only PDB in the local root container are handled automatically by Data Guard. New PDBs created by a remote clone or a plugin operation are not managed automatically. The
STANDBY_PDB_SOURCE_FILE_DBLINK and STANDBY_PDB_SOURCE_FILE_DIRECTORY initialisation parameters make remote clones and plugin operations much simpler in Oracle 18c.This feature is available from 12.1.0.2 when patched appropriately, as described in MOS Doc ID 2274735.1, but 18c is the first time it has been documented in the official documentation.In this Post we will take a look at the usage of standbys=none in a multi-tenant environment.
My environment details-
SRORCL is Primary CDB database.
TRORCL is Standby CDB database.
At Primary-
SQL> select name ,db_unique_name,open_mode,database_role from v$database;
NAME DB_UNIQUE_NA OPEN_MODE DATABASE_ROLE
---------- ------------ -------------------- ----------------
SRORCL SRORCL READ WRITE PRIMARY
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 SRPDB READ WRITE NO
SQL>
At standby-
SQL> select name ,db_unique_name,open_mode,database_role from v$database;
NAME DB_UNIQUE_NA OPEN_MODE DATABASE_ROLE
---------- ------------ -------------------- ----------------
SRORCL TRORCL READ ONLY WITH APPLY PHYSICAL STANDBY
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 SRPDB READ ONLY NO
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
DGRD ALLOCATED 0 0 0 0
ARCH CLOSING 1 95 2048 150
DGRD ALLOCATED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CLOSING 1 96 2048 1770
RFS IDLE 0 0 0 0
RFS IDLE 1 97 1278 1
RFS IDLE 0 0 0 0
MRP0 APPLYING_LOG 1 97 1278 409600
10 rows selected.
Let's clone a new PDB using standbys=none option.
Please note, we are keeping our source PDB in read write mode.
SQL> create pluggable database SRPDB_DUP from SRPDB standbys=none;
Pluggable database created.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 SRPDB READ ONLY NO
4 SRPDB_DUP MOUNTED
SQL> alter pluggable database SRPDB_DUP open;
Pluggable database altered.
SQL> alter pluggable database SRPDB close immediate ;
Pluggable database altered.
SQL> alter pluggable database SRPDB open;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 SRPDB READ WRITE NO
4 SRPDB_DUP READ WRITE NO
SQL>
Below is the entry made on standby alertlog file-
Recovery created pluggable database SRPDB_DUP
File #28 added to control file as 'UNNAMED00028'. Originally created as:
'F:\ORADATA\SRORCL\E664B939B2D54120B226E71BF79BF257\DATAFILE\O1_MF_SYSTEM_HF6NRRPQ_.DBF'
because the pluggable database was created with nostandby
or the tablespace belonging to the pluggable database is
offline.
2020-05-31 12:18:47.530000 +05:30
File #29 added to control file as 'UNNAMED00029'. Originally created as:
'F:\ORADATA\SRORCL\E664B939B2D54120B226E71BF79BF257\DATAFILE\O1_MF_SYSAUX_HF6NRRY4_.DBF'
because the pluggable database was created with nostandby
or the tablespace belonging to the pluggable database is
offline.
2020-05-31 12:18:49.388000 +05:30
File #30 added to control file as 'UNNAMED00030'. Originally created as:
'F:\ORADATA\SRORCL\E664B939B2D54120B226E71BF79BF257\DATAFILE\O1_MF_UNDOTBS1_HF6NRS01_.DBF'
because the pluggable database was created with nostandby
or the tablespace belonging to the pluggable database is
offline.
2020-05-31 12:18:51.597000 +05:30
File #31 added to control file as 'UNNAMED00031'. Originally created as:
'F:\ORADATA\SRORCL\E664B939B2D54120B226E71BF79BF257\DATAFILE\O1_MF_USERS_HF6NRS2R_.DBF'
because the pluggable database was created with nostandby
or the tablespace belonging to the pluggable database is
offline.
Check the standby-
SQL> select name ,db_unique_name,open_mode,database_role from v$database;
NAME DB_UNIQUE_NA OPEN_MODE DATABASE_ROLE
---------- ------------ -------------------- ----------------
SRORCL TRORCL READ ONLY WITH APPLY PHYSICAL STANDBY
SQL> SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”,
2 (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference” FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG
3 WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
4 (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)
5 FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 96 96 0
SQL>
Do some log switch and verify standby is catching up with the primary.
SQL> select name ,db_unique_name,open_mode,database_role from v$database;
NAME DB_UNIQUE_NA OPEN_MODE DATABASE_ROLE
---------- ------------ -------------------- ----------------
SRORCL SRORCL READ WRITE PRIMARY
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL>
Now at standby check the sync status-
SQL> select name ,db_unique_name,open_mode,database_role from v$database;
NAME DB_UNIQUE_NA OPEN_MODE DATABASE_ROLE
---------- ------------ -------------------- ----------------
SRORCL TRORCL READ ONLY WITH APPLY PHYSICAL STANDBY
SQL> SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”,
2 (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference” FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG
3 WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
4 (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)
5 FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 98 98 0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 SRPDB READ ONLY NO
4 SRPDB_DUP MOUNTED
SQL>
Standby is in sync, it's not affected by the PDB creation on the primary.
SQL> select name, recovery_status from v$pdbs;
NAME RECOVERY
---------------------- --------
PDB$SEED ENABLED
SRPDB ENABLED
SRPDB_DUP DISABLED
SQL> alter session set container=SRPDB_DUP;
Session altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 SRPDB_DUP MOUNTED
SQL> select name, status from v$datafile;
NAME STATUS
---------------------------------------------------------------------------------------- -------
F:\APP\MAHESH\PRODUCT\12.2.0\DBHOME_1\DATABASE\UNNAMED00028 SYSOFF
F:\APP\MAHESH\PRODUCT\12.2.0\DBHOME_1\DATABASE\UNNAMED00029 RECOVER
F:\APP\MAHESH\PRODUCT\12.2.0\DBHOME_1\DATABASE\UNNAMED00030 RECOVER
F:\APP\MAHESH\PRODUCT\12.2.0\DBHOME_1\DATABASE\UNNAMED00031 RECOVER
SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME CON_ID
---------- ------- ------- -------------------- ---------- --------- ----------
28 OFFLINE OFFLINE FILE MISSING 0 4
29 OFFLINE OFFLINE FILE MISSING 0 4
30 OFFLINE OFFLINE FILE MISSING 0 4
31 OFFLINE OFFLINE FILE MISSING 0 4
SQL>
As per MOS note 1916648.1 , at this stage we can even perform a switch over operation.
To get the datafiles of the newly created PDB at standby side we can make use of RMAN.
To prepare for the RMAN copy of the files, create a small run block like the following while connected to the standby database using RMAN. This method is preferred as RMAN will rename all of the
datafiles in the standby controlfile to match those created during the restore process. If RMAN was not used, you would have to manually rename each datafile prior to being able to ENABLE RECOVERY on the PDB.In the following code, specify the PDB name that does not have recovery enabled. The FROM SERVICE clause should identify a TNS alias that will connect to the primary database.
run
{
set newname for pluggable database SRPDB_DUP to new;
restore pluggable database SRPDB_DUP from service SRORCL;
switch datafile all;
}
Execute the command block create above to restore the files from the primary to the standby. Connect only to your standby database as target and run the block.
C:\Users\Mahesh>set ORACLE_SID=TRORCL
C:\Users\Mahesh>rman target /
Recovery Manager: Release 12.2.0.1.0 - Production on Sun May 31 12:51:35 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: SRORCL (DBID=3570490003)
RMAN> run
2> {
3> set newname for pluggable database SRPDB_DUP to new;
4> restore pluggable database SRPDB_DUP from service SRORCL;
5> switch datafile all;
6> }
executing command: SET NEWNAME
Starting restore at 31-MAY-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=394 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=133 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=269 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=396 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service SRORCL
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00028 to F:\ORADATA\TRORCL\E664B939B2D54120B226E71BF79BF257\DATAFILE\O1_MF_SYSTEM_%U_.DBF
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: using network backup set from service SRORCL
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00029 to F:\ORADATA\TRORCL\E664B939B2D54120B226E71BF79BF257\DATAFILE\O1_MF_SYSAUX_%U_.DBF
channel ORA_DISK_3: starting datafile backup set restore
channel ORA_DISK_3: using network backup set from service SRORCL
channel ORA_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_DISK_3: restoring datafile 00030 to F:\ORADATA\TRORCL\E664B939B2D54120B226E71BF79BF257\DATAFILE\O1_MF_UNDOTBS1_%U_.DBF
channel ORA_DISK_4: starting datafile backup set restore
channel ORA_DISK_4: using network backup set from service SRORCL
channel ORA_DISK_4: specifying datafile(s) to restore from backup set
channel ORA_DISK_4: restoring datafile 00031 to F:\ORADATA\TRORCL\E664B939B2D54120B226E71BF79BF257\DATAFILE\O1_MF_USERS_%U_.DBF
channel ORA_DISK_3: restore complete, elapsed time: 00:00:41
channel ORA_DISK_4: restore complete, elapsed time: 00:00:39
channel ORA_DISK_1: restore complete, elapsed time: 00:00:56
channel ORA_DISK_2: restore complete, elapsed time: 00:00:54
Finished restore at 31-MAY-20
datafile 28 switched to datafile copy
input datafile copy RECID=25 STAMP=1041857552 file name=F:\ORADATA\TRORCL\E664B939B2D54120B226E71BF79BF257\DATAFILE\O1_MF_SYSTEM_HF6PRF7X_.DBF
datafile 29 switched to datafile copy
input datafile copy RECID=26 STAMP=1041857559 file name=F:\ORADATA\TRORCL\E664B939B2D54120B226E71BF79BF257\DATAFILE\O1_MF_SYSAUX_HF6PRJ40_.DBF
datafile 30 switched to datafile copy
input datafile copy RECID=24 STAMP=1041857547 file name=F:\ORADATA\TRORCL\E664B939B2D54120B226E71BF79BF257\DATAFILE\O1_MF_UNDOTBS1_HF6PRWCH_.DBF
datafile 31 switched to datafile copy
input datafile copy RECID=23 STAMP=1041857546 file name=F:\ORADATA\TRORCL\E664B939B2D54120B226E71BF79BF257\DATAFILE\O1_MF_USERS_HF6PS8VQ_.DBF
RMAN>
C:\Users\Mahesh>set ORACLE_SID=TRORCL
C:\Users\Mahesh>sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sun May 31 12:59:33 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> select * from v$recover_file;
no rows selected
SQL> select name, recovery_status from v$pdbs;
NAME RECOVERY
----------------- --------
PDB$SEED ENABLED
SRPDB ENABLED
SRPDB_DUP DISABLED
SQL> alter session set container=SRPDB_DUP;
Session altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 SRPDB_DUP MOUNTED
SQL> select name, status from v$datafile;
NAME STATUS
------------------------------------------------------------------------------------------ -------
F:\ORADATA\TRORCL\E664B939B2D54120B226E71BF79BF257\DATAFILE\O1_MF_SYSTEM_HF6PRF7X_.DBF SYSTEM
F:\ORADATA\TRORCL\E664B939B2D54120B226E71BF79BF257\DATAFILE\O1_MF_SYSAUX_HF6PRJ40_.DBF RECOVER
F:\ORADATA\TRORCL\E664B939B2D54120B226E71BF79BF257\DATAFILE\O1_MF_UNDOTBS1_HF6PRWCH_.DBF RECOVER
F:\ORADATA\TRORCL\E664B939B2D54120B226E71BF79BF257\DATAFILE\O1_MF_USERS_HF6PS8VQ_.DBF RECOVER
SQL> alter pluggable database enable recovery;
alter pluggable database enable recovery
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
DGRD ALLOCATED 0 0 0 0
ARCH CLOSING 1 95 2048 150
DGRD ALLOCATED 0 0 0 0
ARCH CLOSING 1 98 1 6
ARCH CONNECTED 0 0 0 0
ARCH CLOSING 1 97 8192 1122
RFS IDLE 0 0 0 0
RFS IDLE 1 99 14147 1
RFS IDLE 0 0 0 0
MRP0 APPLYING_LOG 1 99 14147 409600
10 rows selected.
SQL> recover managed standby database cancel;
ORA-65040: operation not allowed from within a pluggable database
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
C:\Users\Mahesh>set ORACLE_SID=TRORCL
C:\Users\Mahesh>sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sun May 31 13:03:02 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> recover managed standby database cancel;
Media recovery complete.
SQL>
SQL> alter session set container=SRPDB_DUP;
Session altered.
SQL> alter pluggable database enable recovery;
Pluggable database altered.
SQL> select name, recovery_status from v$pdbs;
NAME RECOVERY
--------------- --------
SRPDB_DUP ENABLED
SQL>
Now enable recovery at CDB level-
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 SRPDB READ ONLY NO
4 SRPDB_DUP MOUNTED
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
DGRD ALLOCATED 0 0 0 0
ARCH CLOSING 1 95 2048 150
DGRD ALLOCATED 0 0 0 0
ARCH CLOSING 1 98 1 6
ARCH CONNECTED 0 0 0 0
ARCH CLOSING 1 97 8192 1122
RFS IDLE 0 0 0 0
RFS IDLE 1 99 17048 1
RFS IDLE 0 0 0 0
MRP0 APPLYING_LOG 1 99 14334 409600
10 rows selected.
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter pluggable database SRPDB_DUP open;
Pluggable database altered.
SQL>
SQL> SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”,
2 (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference” FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG
3 WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
4 (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)
5 FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 104 98 6
SQL> recover managed standby database disconnect from session;
Media recovery complete.
SQL> SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”,
2 (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference” FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG
3 WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
4 (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)
5 FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 104 104 0
SQL>
Ref: https://oracle-base.com/articles/18c/multitenant-copying-a-pdb-in-a-data-guard-environment-18c
