Target:
In this post I will provides background and details on the usage of the STANDBYS=NONE and disabled recovery of pluggable databases (PDBs) in an Oracle Multitenant environment that includes a Data Guard physical standby database.
Overview and Solution :
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.
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 STANDBYS= clause of the CREATE PLUGGABLE DATABASE statement requires Oracle Database 12c 12.1.0.2 or later.
The process as defined here requires the fix for Patch 19335438.
At some point in the future, copy the files to the standby database and enable recovery of the PDB and thus begin Data Guard protection of the PDB.
Oracle provides tools to copy the files from the primary database to the standby database while the PDB is open and accessible.
Enabling recovery of the PDB requires a bounce of the standby database into MOUNT mode and a brief stoppage of redo apply.
The reasons for requiring deferral include but are not limited to:
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.
The PDB is considered to be a test or short-lived PDB that will be dropped relatively quickly and thus does not need to be protected by Data Guard.
Timing of the PDB creation does not allow for pre-instantiation of the files at the standby since that will prolong the application downtime associated with that PDB, but recovery will be required after the PDB has been created.
Storage for the PDB on the standby environment is not immediately available.
In some cases, the newly added PDB does not require higher level of data protection that comes with having a physical standby database and can be permanently disabled.
The MAA team does not recommend this “subset standby” architecture where some PDBs in the same CDB has different HA SLAs.
This document will provide step-by-step examples of:
1. Creating a PDB with the STANDBYS=NONE clause in a Data Guard configuration with 1 physical standby
2. Showing how the PDB will appear in certain tables and views on the physical standby
3. The impact of this PDB during switchover and failover operations
4. The zero downtime instantiation process using RMAN for copying the files from the primary to standby
5. The steps required for enabling recovery on the PDB after the files have been copied
Considerations :
The test environment consists of a single instance primary container database (CDB) <primary_db_unique_name> with a single instance physical standby CDB <standby_db_unique_name>.
The files are stored in ASM on both the primary and standby environments.
The Data Guard configuration is managed by Data Guard broker.
Initially there are 6 PDBs in the CDB including the SEED (PDB0001-PDB0005).
This test will perform a plugin of a PDB (PDB0006) by cloning a remote PDB.
The remote PDB contains the Swingbench SOE schema.
The following process has been tested in both Oracle Exadata Database Cloud Service (ExaCS) and Oracle Exadata Database Cloud at Customer (ExaCC).
PDB Operations performed outside of the Console via SQL will not reflect in the Console until a subsequent sync with the OCI control plane, they should be visible in the console and API-based tools within 45 minutes ofcompletion.
This is expected behavior.
For Data Guard configurations, PDBs that are in deferred recovery status will not appear in the Console, only PDBs with recovery enabled appear in the Console.
After enabling recovery of a PDB, the status change will appear in the Console on a subsequent sync of the OCI control plane.
Creating a PDB with the STANDBYS=NONE clause in a Data Guard configuration with 1 physical standby
This section shows a PDB being created by cloning a remote PDB, the cloned PDB will be created with the STANDBYS=NONE clause.
The different statuses of the Data Guard configuration and the PDBs will be shown.
Display the Data Guard configuration
DGMGRL> show configuration
Configuration - <dbname>
Protection Mode: MaxPerformance
Members:
<primary_db_unique_name> - Primary database
<standby_db_unique_name> - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 11 seconds ago)
DGMGRL> validate database <standby_db_unique_name>
Database Role: Physical standby database
Primary Database: <primary_db_unique_name>
Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)
Display the PDBs contained in the primary database
SQL> show parameter db_unique_name
NAME TYPE VALUE
---------- --------- ---------
db_unique_name string <primary_db_unique_name>
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PRIMARY
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ----- ------ -------
2 PDB$SEED READ ONLY NO
3 PDB0001 READ WRITE NO
4 PDB0002 READ WRITE NO
5 PDB0003 READ WRITE NO
6 PDB0004 READ WRITE NO
7 PDB0005 READ WRITE NO
Display the PDBs in the phyiscal standby database
SQL> show parameter db_unique_name
NAME TYPE VALUE
--------- ----------- -----------
db_unique_name string <standby_db_unique_name>
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------- ------- --------
2 PDB$SEED READ ONLY NO
3 PDB0001 READ ONLY NO
4 PDB0002 READ ONLY NO
5 PDB0003 READ ONLY NO
6 PDB0004 READ ONLY NO
7 PDB0005 READ ONLY NO
SQL> select name, recovery_status
from v$pdbs;
NAME RECOVERY
----------------------- --------
PDB$SEED ENABLED
PDB0001 ENABLED
PDB0002 ENABLED
PDB0003 ENABLED
PDB0004 ENABLED
PDB0005 ENABLED
6 rows selected.
Now I create the new PDB on the primary using the STANDBYS=NONE clause
SQL> create pluggable database pdb0006 from pdb1025@cdb200 tempfile reuse STANDBYS=NONE;
Pluggable database created.
Show the status of the new PDB on the primary
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------- --------- ----
2 PDB$SEED READ ONLY NO
3 PDB0001 READ WRITE NO
4 PDB0002 READ WRITE NO
5 PDB0003 READ WRITE NO
6 PDB0004 READ WRITE NO
7 PDB0005 READ WRITE NO
8 PDB0006 MOUNTED
Open the new PDB on the primary
SQL> alter pluggable database
pdb0006 open;
Pluggable database altered.
Showing how the cloned PDB will appear in certain tables and views on the physical standby
After the PDB creation has completed on the primary database, the redo for the creation will be applied on the standby database.
The standby database will show the PDB has been created but V$PDBS will show that it has a RECOVERY_STATUS of disabled.
If you look at the status of the files associated with the PDB on the standby database, they will be in RECOVER, however the SYSTEM tablespace will be SYSOFF to identify the file as a SYSTEM tablespace datafile and offline.
You will not be able to open the PDB on the standby database as it has no files to access.
The alert log for the creation of the PDB will note that the PDB was created with the STANDBYS=NONE clause (identified as "nostandby") and state the tablespace was created in a state of OFFLINE.
The PDB creation has finished on the primary and the redo has been applied to the standby.
Show all the PDBs on the standby
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTE
---------- ------- ------ -------
2 PDB$SEED READ ONLY NO
3 PDB0001 READ ONLY NO
4 PDB0002 READ ONLY NO
5 PDB0003 READ ONLY NO
6 PDB0004 READ ONLY NO
7 PDB0005 READ ONLY NO
8 PDB0006 MOUNTED📍
Display the RECOVERY_STATUS of all the PDBs in the standby
SQL> select name, recovery_status from v$pdbs;
NAME RECOVERY
------------- ------------
PDB$SEED ENABLED
PDB0001 ENABLED
PDB0002 ENABLED
PDB0003 ENABLED
PDB0004 ENABLED
PDB0005 ENABLED
PDB0006 DISABLED 📍
7 rows selected.
Show the status of the datafiles for the PDB
SQL> alter session set
container=pdb0006;
Session altered.
SQL> set lines 120
SQL> set pages 9999
SQL> col name format a65
SQL> select name, status
from v$datafile;
NAME STATUS
---------------------- ---------------
/<oracle_home>/dbs/UNNAMED00178 SYSOFF
/<oracle_home>/dbs/UNNAMED00179 RECOVER
/<oracle_home>/dbs/UNNAMED00180 RECOVER
Attempt to open the PDB
SQL> alter pluggable database
pdb0006 open;
alter pluggable database pdb0006 open
*
ERROR at line 1:
ORA-01147: SYSTEM tablespace file 178 is offline
Standby Alert log entry for the creation of the PDB
Xxx Xxx NN NN:NN:NN NNNN
Recovery created pluggable database PDB0006
Recovery scanning directory +<diskgroup>/<standby_db_unique_name>/<pdb>/DATAFILE for any matching files
File #178 added to control file as 'UNNAMED00178'.
Originally created as:
'+<diskgroup>/<db_unique_name>/<pdb_guid>/DATAFILE/<filename>'
because the pluggable database was created with nostandby
or the tablespace belonging to the pluggable database is
offline.
File #179 added to control file as 'UNNAMED00179'.
Originally created as:'+<diskgroup>DA1/<db_unique_name>/<pdb_guid>/DATAFILE/<filename>'
because the pluggable database was created with nostandby or the tablespace belonging to the pluggable database is offline.
File #180 added to control file as 'UNNAMED00180'.
Originally created as:
'+<diskgroup>/<db_unique_name>/<pdb_guid>/DATAFILE/<filename>'
because the pluggable database was created with nostandby
or the tablespace belonging to the pluggable database is offline.
📍Performing a Data Guard Role Transition
with a PDB in DISABLED RECOVERY
The PDB has now been created.
The following examples will show how the Data Guard broker displays the information.
After validating the standby database, a switchover to the standby will be performed and various statuses will be shown.
Finally, a failover back to the original configuration will be performed.
Show the Data Guard configuration in the broker
DGMGRL> show configuration
Configuration - <db_name>_dg
Protection Mode: MaxPerformance
Members:
<primary_db_unique_name> - Primary database
<standby_db_unique_name> - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS(status updated 15 seconds ago)
Run the broker VALIDATE DATABASE command against the standby.
📍Notice that the configuration is ready for both failover and switchover.
Since we cannot open the PDB on the standby to be able to add a tempfile to the newly created PDB, the broker shows the standby having one less temporary file than the primary.
DGMGRL> validate database <standby_db_unique_name>
Database Role: Physical standby database
Primary Database:<db_name>
Ready for Switchover: Yes
Ready for Failover:Yes(Primary Running)
Temporary Tablespace File Information:
<primary_db_unique_name>TEMPFiles:8
<standby_db_unique_name> TEMPFiles:7
Disaster Recovery Advisor (DRA) periodically runs deeper analysis of the databases in a Data Guard configuration.
This will also occur after an database instance bounce been performed.
When DRA has performed this analysis you will begin to see additional information as part of the Data Guard broker VALIDATE DATABASE command.
Note:
Starting in Oracle Database 19c, the Data Recovery Advisor (DRA) feature is deprecated.
The deprecation of DRA includes deprecation of the following Oracle Recovery Manager (RMAN) commands: LIST FAILURE, ADVISE FAILURE, REPAIR FAILURE, and CHANGE FAILURE. Database administrators will no longer have access to these commands. There is no replacement feature for DRA.
Data Recovery Advisor only supports single-instance databases.
Oracle Real Application Clusters (Oracle RAC) databases are not supported.
If a data failure occurs that brings down all Oracle RAC instances, then you can mount the database in single-instance mode and use Data Recovery Advisor to detect and repair control file, SYSTEM data file, and data dictionary failures.
You can also invoke data recovery checks proactively to test other database components for data failures.
This approach does not detect data failures that are local to other cluster instances, for example, an inaccessible data file.
DGMGRL> validate database <standby_db_unique_name>
Database Role: Physical standby database
Primary Database: <db_name>
Ready for Switchover: Yes
Ready for Failover:No (Primary Running)
Temporary Tablespace File Information:
<primary_db_unique_name> TEMP Files: 8
<standby_db_unique_name> TEMP Files: 7
Automatic Diagnostic Repository Errors:
Error <primary_db_unique_name> <standby_db_unique_name>
System data file offline NO YES
User data file offline NO YES
You can get more detailed information using RMAN against the database showing errors.
Connect via RMAN to the database listed above with a value of YES.
You will see something like the following if the database is in the standby role:
Disaster Recovery Advisor is deprecated as of Oracle Database 19c.
In 19c the list failure command will only succeed on single instance primary databases.
It should no longer be relied upon, the following examples are left for Oracle Database 18c and earlier.
RMAN> list failure;
using target database control file instead of recovery catalog
Database Role: PHYSICAL STANDBY
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- -------- -------
3347 CRITICAL OPEN 13-AUG-14 System datafile 181: '/<oracle_home>/dbs/UNNAMED00181' is offline
3368 HIGH OPEN 13-AUG-14 Name for datafile 183 is unknown in the control file
360 HIGH OPEN 13-AUG-14 One or more non-system datafiles are offline
3359 HIGH OPEN 13-AUG-14 Name for datafile 182 is unknown in the control file
3350 HIGH OPEN 13-AUG-14 Name for datafile 181 is unknown in the control file
3302 HIGH OPEN 13-AUG-14 Datafiles are mutually inconsistent.
If the database listed with errors is in the primary role, you will see something like the following:
RMAN> list failure;
Database Role: PRIMARY
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- -------- -------
3368 HIGH OPEN 13-AUG-14 Name for datafile 183 is unknown in the control file
3359 HIGH OPEN 13-AUG-14 Name for datafile 182 is unknown in the control file
3350 HIGH OPEN 13-AUG-14 Name for datafile 181 is unknown in the control file
Perform a switchover from primary to the standby
DGMGRL> switchover to <standby_db_unique_name>
Performing switchover NOW, please wait...
Operation requires a connection to instance "<standby_db_unique_name>" on database "<standby_db_unique_name>"
Connecting to instance "<standby_db_unique_name>"...
Connected as SYSDBA.
New primary database "<standby_db_unique_name>" is opening...
Oracle Clusterware is restarting database "<primary_db_unique_name>" ...
Switchover succeeded, new primary is "<standby_db_unique_name>"
The PDBs on the standby, the new primary, are all open READ WRITE except for PDB0006 which does not have datafiles
SQL> show parameter db_unique_name
NAME TYPE VALUE
---------- --------- ---------
db_unique_name string <standby_db_unique_name>
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
------ --------- -------- --------
2 PDB$SEED READ ONLY NO
3 PDB0001 READ WRITE NO
4 PDB0002 READ WRITE NO
5 PDB0003 READ WRITE NO
6 PDB0004 READ WRITE NO
7 PDB0005 READ WRITE NO
8 PDB0006 MOUNTED
<primary_db_unique_name> has all of the datafiles for all of the PDBs and therefore all of the PDBs can be opened in READ ONLY mode
SQL> show parameter db_unique_name
NAME TYPE VALUE
---------- -------- -------
db_unique_name string <primary_db_unique_name>
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
------- -------- ------- --------
2 PDB$SEED READ ONLY NO
3 PDB0001 MOUNTED
4 PDB0002 MOUNTED
5 PDB0003 MOUNTED
6 PDB0004 MOUNTED
7 PDB0005 MOUNTED
8 PDB0006 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 PDB0001 READ ONLY NO
4 PDB0002 READ ONLY NO
5 PDB0003 READ ONLY NO
6 PDB0004 READ ONLY NO
7 PDB0005 READ ONLY NO
8 PDB0006 READ ONLY NO
The Data Guard broker shows the configuration with <standby_db_unique_name> as the primary database and the <primary_db_unique_name> as the standby database.
DGMGRL> show configuration
Configuration - <db_name>
Protection Mode: MaxPerformance
Members:
<standby_db_unique_name>- Primary database
<primary_db_unique_name>- Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS(status updated 14 seconds ago)
Run the Data Guard broker VALIDATE DATABASE command against each database
DGMGRL> validate database <primary_db_unique_name>
Database Role: Primary database
Ready for Switchover: Yes
DGMGRL> validate database <standby_db_unique_name>
Database Role: Physical standby database
Primary Database: <primary_db_unique_name>
Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)
Temporary Tablespace File Information:
<standby_db_unique_name>TEMP Files: 7
<primary_db_unique_name>TEMP Files: 8
Prepare to failover from standby to primary by shutting down the standby with abort
It is possible to perform a switchover to the original configuration, however this next section documents that a failover is possible as well.
SQL> show parameter db_unique_name
NAME TYPE VALUE
----------- ----------- -------
db_unique_name string <standby_db_unique_name>
SQL> shutdown abort
ORACLE instance shut down.
Show the Data Guard configuration and perform the failover
DGMGRL> show configuration
Configuration - <db_name>
Protection Mode: MaxPerformance
Members:
<primary_db_unique_name> - Primary database
Error: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
<standby_db_unique_name>- Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
ERROR(status updated 0 seconds ago)
DGMGRL> failover to <standby_db_unique_name>
Performing failover NOW, please wait...
Failover succeeded, new primary is "<standby_db_unique_name>"
DGMGRL> show configuration
Configuration - <db_name>
Protection Mode: MaxPerformance
Members:
<primary_db_unique_name> - Primary database
<standby_db_unique_name> - Physical standby database (disabled)
ORA-16661: the standby database needs to be reinstated
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 14 seconds ago)
Flashback is enabled so use Data Guard broker to reinstate <standby_db_unique_name>
DGMGRL> reinstate database <standby_db_unique_name>
Reinstating database "<standby_db_unique_name>", please wait...
Reinstatement of database "<standby_db_unique_name>" succeeded
DGMGRL> validate database <standby_db_unique_name>
Database Role: Physical standby database
Primary Database: primary_db_unique_name>
Ready for Switchover: Yes
Ready for Failover:Yes (Primary Running)
Temporary Tablespace File Information:
<primary_db_unique_name>TEMP Files: 8
<standby_db_unique_name>TEMP Files: 7
The zero downtime instantiation process to enable recovery using RMAN for copying the files from the primary to standby
Dealing with Transparent Data Encryption (TDE) Keys
In environments such as Oracle Cloud, you will encounter Transparent Data Encryption (TDE).
Without going into too much detail, each PDB will have TDE key(s) associated with it, these encryption keys/keystores must be synchronized between the primary database and standby database to allow access to the PDB's files.
If an encryption key for a PDB is added or modified on the primary database, the key must be made available to the standby database, failure to do so will cause redo apply to fail as it does not have the key to decrypt the data to apply changes.
📍When a PDB has its recovery deferred, redo apply is not attempting to access the files at the standby so the keys aren't required until it is time to enable recovery.
The keystores can be synchronized at any time prior to enabling recovery of the PDB, the new keys will be accessed when redo apply is restarted by the enable recovery process.
The location of the keystore will be defined by different methods depending on your TDE configuration and the version of the database. For database versions prior to 18c, the keystore location is always defined in the sqlnet.ora by the ENCRYPTION_WALLET_LOCATION attribute. For database versions 18c and later, Oracle highly recommends you use the initialization parameter WALLET_ROOT to define the keystore location.
If WALLET_ROOT is not set, the database will use the SQLNET.ENCRYPTION_WALLET_LOCATION setting.
The following sample shows settings in the primary and standby of a fictional configuration using WALLET_ROOT.
Note that the keystore is in the
WALLET_ROOT/tde subdirectory.
SQL-PRIM> show parameter wallet_root
NAME TYPE Value
----------- ---------- ------------
wallet_root string
/var/opt/oracle/dbaas_acfs/<primary db name>/wallet_root
SQL-stby> show parameter wallet_root
NAME TYPE VALUE
--------------- ----------- ------------------
wallet_root string
/var/opt/oracle/dbaas_acfs/<standby db name>/wallet_root
📍To synchronize the keystores do the following:
On the standby environment create a backup of the existing keystore
[stby-host tde]$ mkdir keystore_backup_<date>
[stby-host tde]$ cp *.* keystore_backup_<date>/
Copy the entire contents of the keystore from the primary keystore location to the standby keystore location
[stby-host tde]$ scp prim-host/<WALLET_ROOT>/tde/*.* <WALLET_ROOT>/tde/
The keys for the PDB are now in place.
In steps below as part of enabling recovery you will restart redo apply, this will cause the keys for the PDB to be read into memory, allowing redo apply to maintain the copied files.
Steps for Preparing to enable recovery of the PDB
The databases are now back to their original roles when the PDB was created.
This section will demonstrate how to finish the PDB plug in at the standby by making the relevant data files available to the standby and enabling recovery so the new PDB can be protected by Data Guard.
This instantiation process can be performed while the PDB is open and accessible on the primary database.
If you have a strict archive log deletion policy like APPLIED ON ALL STANDBY, re-configure it at this time.
For the files to be instantiated without additional steps, the standby needs to have all redo available to it from the time the file copy process begins.
The policy can be put back in place after ENABLE RECOVERY of the PDB has been completed.
The policy should be disabled on both the primary and the standby.
📍To change the policy, connect to each database via RMAN and perform the following:
RMAN> show archivelog deletion policy;
RMAN configuration parameters for database with db_unique_name <primary_db_unique_name> are:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
RMAN> configure archivelog deletion policy to none;
old RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
new RMAN configuration parameters are successfully stored
Verify that the PDB is open for access on the primary database
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
-------- ------- -------- --------
2 PDB$SEED READ ONLY NO
3 PDB0001 READ WRITE NO
4 PDB0002 READ WRITE NO
5 PDB0003 READ WRITE NO
6 PDB0004 READ WRITE NO
7 PDB0005 READ WRITE NO
8 PDB0006 READ WRITE NO
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. If required, allocate disk channels to control parallelism and resource utilization during the restore process.
Specify the SECTION SIZE clause to perform multi-section backups and allow multiple channels to work on large files.
While restoring the files for the PDB from the primary to the standby, ensure no files are added to the PDB on the primary.
Files added after the restore begins will not be included in the restore set and require additional individual file restore commands.
run{
allocate channel disk1 device type disk;
allocate channel disk2 device type disk;
allocate channel disk3 device type disk;
allocate channel disk4 device type disk;
allocate channel disk5 device type disk;
set newname for pluggable database PDB0006 to new;
restore pluggable database PDB0006 from service <primary_db_tns_alias> section size 64G;
}
Execute the command block create above to restore the files from the primary to the standby.
Connect only to your standby database as target, there is no need to create a specific connection to your primary, that will be accomplished automatically through the FROM SERVICE clause.
RMAN> connect target sys@<standby_db_unique_name>
target database Password:
connected to target database: <primary_db_unique_name> (DBID=663359056, not open)
RMAN> @copy.cmd
RMAN> run{
allocate channel disk1 device type disk;
allocate channel disk2 device type disk;
allocate channel disk3 device type disk;
allocate channel disk4 device type disk;
allocate channel disk5 device type disk;
set newname for pluggable database pdb0006 to new;
restore pluggable database pdb0006 from service <primary_db_tns_alias> 64G;
}
using target database control file instead of recovery catalog
allocated channel: disk1
channel disk1: SID=277 instance=<standby_instance_name> device type=DISKallocated channel: disk2
channel disk2: SID=790 instance=<standby_instance_name> device type=DISK
allocated channel: disk3
channel disk3: SID=1048 instance=<standby_instance_name> device type=DISK
allocated channel: disk4
channel disk4: SID=1307 instance=<standby_instance_name> device type=DISK
allocated channel: disk5
channel disk5: SID=18 instance=<standby_instance_name> device type=DISK
executing command: SET NEWNAME
Starting restore at 09-DEC-21channel disk1: starting datafile backup set restore
channel disk1: using network backup set from service <primary_db_tns_alias>
channel disk1: specifying datafile(s) to restore from backup set
channel disk1: restoring datafile 00226 to +<diskgroup>/<standby_db_unique_name>/<pdb_guid>/DATAFILE/<filename>
channel disk1: restoring section 1 of 1
channel disk2: starting datafile backup set restore
channel disk2: using network backup set from service <primary_db_tns_alias>
channel disk2: specifying datafile(s) to restore from backup set
channel disk2: restoring datafile 00227 to +<diskgroup>/<standby_db_unique_name>/<pdb_guid>/DATAFILE/<filename>
channel disk2: restoring section 1 of 1
channel disk3: starting datafile backup set restore
channel disk3: using network backup set from service <primary_db_tns_alias>
channel disk3: specifying datafile(s) to restore from backup set
channel disk3: restoring datafile 00228 to +<diskgroup>/<standby_db_unique_name>/<pdb_guid>/DATAFILE/<filename>
channel disk3: restoring section 1 of 1
<snip>
Finished restore at 09-DEC-21
released channel: disk1
released channel: disk2
released channel: disk3
released channel: disk4
released channel: disk5
released channel: disk6
released channel: disk7
RMAN> **end-of-file**
📍Steps required for enabling recovery on the PDB after the files have been copied
Now that the files have been copied from the primary to the standby, you can now ENABLE RECOVERY on the PDB.
Enabling recovery will only impact the standby database on which the commands are being executed, the recovery status of the PDB on any other standby database remains unchanged.
The standby database is going to be restarted in mount mode, but it must not have redo apply running.
Connect to dgmgrl and disable redo apply for the standby database.
DGMGRL> edit database <standby_db_unique_name>
set state='apply-off';
Succeeded.
DGMGRL> show configuration Configuration - <db_name>
Protection Mode: MaxPerformance
Members:
<primary_db_unique_name> - Primary database
<standby_db_unique_name> - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS(status updated 30 seconds ago)
Connect to RMAN on the standby database and switch the pluggable database files to the ones just restored
RMAN> switch pluggable database PDB006 to copy;
using target database control file instead of recovery catalog
datafile 38 switched to datafile copy
input datafile copy RECID=38 STAMP=855397781 file name=+<diskgroup>/<standby_db_unique_name>/<pdb_guid>/DATAFILE/<filename>
datafile 39 switched to datafile copy
input datafile copy RECID=39 STAMP=855397794 file name=+<diskgroup>/<standby_db_unique_name>/<pdb_guid>/DATAFILE/<filename>
datafile 40 switched to datafile copy
input datafile copy RECID=40 STAMP=855397960 file name=+<diskgroup>/<standby_db_unique_name>/<pdb_guid>/DATAFILE/<filename>
📍For 18c and later versions we need to online the restored datafiles.
Connect to the standby database via SQLPlus and run the following query to build the SQL commands to perform the online.
This step can be skipped for 12c environments.
SQL> alter session set container=PDB006;
SQL> set lines 120
SQL> set pages 9999
SQL> spool online_files.sql
SQL> select 'alter database datafile '||''''||name||''''||' online;' from v$datafile;
'ALTERDATABASEDATAFILE'||''''||NAME||''''||'ONLINE;'
----------------------------
alter database datafile '+<diskgroup>/<standby_db_unique_name>/<pdb_guid>/DATAFILE/<filename>' online;
alter database datafile '+<diskgroup>/<standby_db_unique_name>/<pdb_guid>/DATAFILE/<filename>' online;
alter database datafile '+<diskgroup>/<standby_db_unique_name>/<pdb_guid>/DATAFILE/<filename>' online;
SQL> spool off
Edit the output to remove any extraneous heading, format and feedback lines.
Restart the physical standby database in mount mode if it is running in Active Data Guard mode.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total SGA 4831838208 bytes
Fixed Size 3720600 bytes
Variable Size 1308625512 bytes
Database Buffers 3506438144 bytes
Redo Buffers 13053952 bytes
Database mounted.
Change your container on the physical standby to the PDB to be enabled
SQL> alter session set container=pdb0006;
Session altered.
Issue the ENABLE RECOVERY command
SQL> show con_name
CON_NAME
------------------------------
PDB0006
SQL> alter pluggable database enable recovery;
Pluggable database altered.
If you receive ORA-1113 file NNN needs media recovery, this is a sign you have not restarted the standby database in mount mode
📍For 18c and later environments, connect to the standby database PDB via SQLPlus and run the script created above to online the datafiles for the PDB.
This step can be skipped for 12c environments.
SQL> alter session set container=PDB006;
SQL> @online_files
Database altered.Database altered.
Database altered.
Enable/start redo apply of the physical standby database in the Data Guard Broker
DGMGRL> edit database <standby_db_unique_name>set state='apply-on';
Succeeded.
Verify the physical standby database in the Data Guard Broker
DGMGRL> validate database <standby_db_unique_name>
Database Role: Physical standby database
Primary Database: <primary_db_unique_name>
Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)
Temporary Tablespace File Information:
<primary_db_unique_name>TEMP Files: 8
<standby_db_unique_name>TEMP Files: 7
Redo apply must run for a short period (5 minutes will suffice) to ensure the datafiles are recognized and processed successfully moving forward.
After this short period, you can enable Active Data Guard and open the PDB on the standby.
SQL> select open_mode from
v$database;
OPEN_MODE
--------------------
MOUNTED
SQL> alter database open;
Database altered.
📍NOTE it may take 30s for redo apply to restart
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
Connect into SQL*Plus on the standby and add a tempfile to the TEMP tablespace of the PDB.
📍# Open the PDB if necessary
SQL> alter pluggable database
pdb0006 open;
Pluggable database altered.
SQL> alter session set container=pdb0006;
Session altered.
SQL> alter tablespace temp add
tempfile size 20g;
Tablespace altered.
Check the status of the PDB datafiles on the standby
SQL> select name, status from
v$datafile;
NAME STATUS
------------------- -------
+<diskgroup>/<standby_db_unique_name>/<pdb_guid>/DATAFILE/<filename> SYSTEM
+<diskgroup>/<standby_db_unique_name>/<pdb_guid>/DATAFILE/<filename> ONLINE
+<diskgroup>/<standby_db_unique_name>/<pdb_guid>/DATAFILE/<filename> ONLINE
Check the RECOVERY_STATUS of the PDB in the standby
SQL> select name, recovery_status from v$pdbs where name = 'PDB0006';
NAME RECOVERY
------------------------- --------
PDB0006 ENABLED
Validate the standby database in Data Guard Broker
DGMGRL> validate database <standby_db_unique_name>
Database Role: Physical standby database
Primary Database: <primary_db_unique_name>
Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)
📍If you changed your RMAN ARCHIVELOG DELETION POLICY, reset it now.
The policy should be disabled on both the primary and the standby.
To change the policy, connect to each database via RMAN and perform the following:
RMAN> show archivelog deletion policy;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name <standby_db_unique_name> are:
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
RMAN> configure archivelog deletion policy to APPLIED ON ALL STANDBY;
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
new RMAN configuration parameters are successfully stored
📍Steps to DISABLE RECOVERY of a Pluggable Database
It is also possible to DISABLE RECOVERY of an existing pluggable database at a standby. Disabling recovery will only impact the standby database on which the commands are being executed, the recovery status of the PDB on any other standby database remains unchanged. Some potential reasons for disabling recovery of a PDB may include:
Debugging operations of a particular pluggable database where redo being applied at the standby causes redo apply to fail, thus leaving the entire container database unprotected.
Application activity against a single pluggable database cause redo apply on the standby to lag beyond SLA requirements.
The following steps document how to disable recovery of a PDB on a standby.
Show current status of the configuration
DGMGRL> show configuration
Configuration - <db_name>
Protection Mode: MaxPerformance
Members:
<primary_db_unique_name>- Primary database
<standby_db_unique_name> - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS(status updated 32 seconds ago)
DGMGRL> validate database <standby_db_unique_name>;
Database Role: Physical standby database
Primary Database: <primary_db_unique_name>
Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)
Show the current status of the PDBs on the standby
SQL> show parameter db_unique_name
NAME TYPE VALUE
--------- ---------- --------------
db_unique_name string <standby_db_unique_name>
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
-------- ---------- -------- -------
2 PDB$SEED READ ONLY NO
3 PDB0001 READ ONLY NO
4 PDB0002 READ ONLY NO
5 PDB0003 READ ONLY NO
6 PDB0004 READ ONLY NO
7 PDB0005 READ ONLY NO
8 PDB0006 READ ONLY NO
9 PDB0007 READ ONLY NO
10 PDB0008 READ ONLY NO
11 PDB0009 READ ONLY NO
SQL> select name, recovery_status from
v$pdbs;
NAME RECOVERY
----------------- --------------
PDB$SEED ENABLED
PDB0001 ENABLED
PDB0002 ENABLED
PDB0003 ENABLED
PDB0004 ENABLED
PDB0005 ENABLED
PDB0006 ENABLED
PDB0007 ENABLED
PDB0008 ENABLED
PDB0009 ENABLED
10 rows selected.
📍Turn off redo apply in Data Guard Broker
DGMGRL> edit database <standby_db_unique_name> set state='apply-off';
Succeeded.
Change your container to the PDB to have recovery disabled
The standby database does not need to be restarted in mount mode to disable recovery, it can be left running in its current state.
SQL> alter session set container=pdb0006;
altered.
Issue the DISABLE RECOVERY command
SQL> alter pluggable database disable recovery;
Pluggable database altered.
Enable/start redo apply of the physical standby database in the Data Guard Broker
DGMGRL> edit database <standby_db_unique_name> set state='apply-on';
Succeeded.
📍Enable Active Data Guard and open the PDBs on the standby.
SQL> show parameter db_unique_name
NAME TYPE VALUE
------------ ----------- -------------
db_unique_name string <standby_db_unique_name>
SQL> alter database open;
Database altered.
SQL> alter pluggable database all open;
alter pluggable database all open
*
ERROR at line 1:
ORA-01147: SYSTEM tablespace file 181 is offline
Check the status of the PDB datafiles on the standby
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- -------- ------- --------
2 PDB$SEED READ ONLY NO
3 PDB0001 READ ONLY NO
4 PDB0002 READ ONLY NO
5 PDB0003 READ ONLY NO
6 PDB0004 READ ONLY NO
7 PDB0005 READ ONLY NO
8 PDB0006 MOUNTED
9 PDB0007 READ ONLY NO
10 PDB0008 READ ONLY NO
11 PDB0009 READ ONLY NO
SQL> alter session set container=pdb0006;
Session altered.
SQL> col name format a85
SQL> select name, status from v$datafile;
NAME STATUS
-------------------------- -------
+<diskgroup>/<standby_db_unique_name>/<pdb_guid>/DATAFILE/<filename> SYSOFF
+<diskgroup>/<standby_db_unique_name>/<pdb_guid>/DATAFILE/<filename> RECOVER
+<diskgroup>/<standby_db_unique_name>/<pdb_guid>/DATAFILE/<filename> RECOVER
The datafile names, instead of being UNNAMED as is the case when creating a PDB with STANDBYS=NONE will retain the original file names.
Check the RECOVERY_STATUS of the PDB in the standby
SQL> select name, recovery_status from v$pdbs;
NAME RECOVERY
------------------ -------------
PDB$SEED ENABLED
PDB0001 ENABLED
PDB0002 ENABLED
PDB0003 ENABLED
PDB0004 ENABLED
PDB0005 ENABLED
PDB0006 DISABLED
PDB0007 ENABLED
PDB0008 ENABLED
PDB0009 ENABLED
10 rows selected.
Validate the standby database in Data Guard Broker
DGMGRL> show configuration
Configuration - <db_name>
Protection Mode: MaxPerformance
Members:
<primary_db_unique_name>- Primary database
<standby_db_unique_name> - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 46 seconds ago)
DGMGRL> validate database <standby_db_unique_name>;
Database Role: Physical standby database
Primary Database: <primary_db_unique_name>
Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)
If you bounce the standby instance and rerun VALIDATE DATABASE you will see:
DGMGRL> validate database <standby_db_unique_name>;
Database Role: Physical standby database
Primary Database: <primary_db_unique_name>
Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)
Automatic Diagnostic Repository Errors:
Error : <primary_db_unique_name> <standby_db_unique_name>
System data file offline NO YES
User data file offline NO YES
In RMAN, you will see something similar to the following:
📍Disaster Recovery Advisor is deprecated as of Oracle Database 19c.
In 19c the list failure command will only succeed on single instance primary databases.
It should no longer be relied upon, the following examples are left for Oracle Database 18c and earlier.
RMAN> list failure;
Database Role: PHYSICAL STANDBY
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------- -------
4114 CRITICAL OPEN 26-AUG-14 System datafile 181: '+<diskgroup>/<standby_db_unique_name>/<pdb>/DATAFILE/system.296.856610895' is offline
360 HIGH OPEN 26-AUG-14 One or more non system datafiles are offline
4102 HIGH OPEN 26-AUG-14 Datafiles are mutually inconsistent
Delete the datafiles from the standby storage
Conclusion:
As you can see from this example, it is possible to defer work on physical standby databases when creating a pluggable database by using the new Oracle Database 12c 12.1.0.2 clause STANDBYS=NONE.
The missing datafiles for this situation will have no impact to Data Guard role transitions.
When you are ready it is a simple task to propagate the PDB's files from the primary to the standby and ENABLE RECOVERY on the PDB to provide Data Guard protection to the new PDB.
It is also possible to DISABLE RECOVERY of an existing PDB already being recovered at a specific standby.
The steps are similar to those for the ENABLE RECOVERY process.
Sincerely,
Alireza Kamrani
Senior Database Consultant.
Please visit more posts in:
🔴Datbase Box🔴 LinkedIn Group.
No comments:
Post a Comment