The Complete Oracle Blog For Dba: How to use STANDBYS Clause to Disable recovery for Specific PDBs

Starting with Oracle Database 12.1.0.2 we have a new clause called "STANDBYS" in the sentence "CREATE PLUGGABLE DATABASE". This new clause is useful to specify if a new PDB will be recovered in the Standby Databases. This way you can have a Primary Container Database that have some PDBs where redo is not applied in the Standby Container Database.

This doesn't mean that the PDB will not exist in the Standby Container Database. The PDB exists, but only the general structure of that PDB is created in the Standby CDB, All its datafiles are not created in Standby CDB. Its datafiles are marked as UNNAMED and that PDB will have Recovery disabled and of course that PDB will not receive redo to get recovered. The PDB also cannot be open but will not impact the others PDBs in the Standby Container.

The STANDBYS clause have two values:

STANDBYS=ALL - Includes the New PDB on All Standby CDBs
STANDBYS=NONE - Excludes the New PDB from All Standby CDBs

If you create new Pluggable Databases with STNADBYS=NONE in a Database and later you create a Standby Database of this one you will see that Oracle will ignore this clause and all the Pluggable Databases will be created normally in Standby Side and all the PDBs will have recovery enabled and its datafiles will exist in Standby Side. To disable the recovery on a Specific Pluggable Database you will have to do it manually.

If you have already a Data Guard Configuration with a Primary Container Database and a Standby Container Database and then you create a new Pluggable Database in Primary Side with Clause STANDBYS=NONE then that clause will work. That PDB will not be created completely in Standby Container Database, only its structure will be created.

If you have a Pluggable Database that was created with STANDBYS=NONE in Standby Container Database and you want to enable recovery on it you can do it manually by restoring its datafiles in Standby Side and then enable recovery with sentence "ALTER PLUGGABLE DATABASE ENABLE RECOVERY".

If you have a Pluggable Database that was created with STANDBYS=ALL in Standby Container Database and you want to disable recovery in that PDB you can do it manually by executing "ALTER PLUGGABLE DATABASE DISABLE RECOVERY".

You can use the column "recovery_status" in the view "v$pdbs" to know if a PDB has recovery enabled. The view "v$recover_file" can be also used to know if the datafiles of a PDB are getting recovered.

In this article we will cover the following topics:

Create 2 Pluggable Databases in Primary CDB.
1. One PDB with STANDBYS=NONE
2. One PDB with STANDBYS=ALL
Create a Standby Database with EM12c
Conclusion Post Standby Creation
Create another PDB with STANDBYS=NONE with standby already created
Disabling Recovery manually in a PDB
Enabling Recovery manually in a PDB
1. On a PDB that has its datafiles already created
2. On a PDB that its datafiles are not created.

Create 2 Pluggable Databases in Primary CDB.

Checking which Pluggable Database we have already Created:

SQL> select name, cdb from v$database;

NAME CDB
--------- ---
CDB1 YES

SQL> select con_id, name , open_mode from v$pdbs;

CON_ID NAME OPEN_MODE
---------- ------------------------------ ----------
2 PDB$SEED READ ONLY

Create a new PDB with STANDBYS=NONE

SQL> CREATE PLUGGABLE DATABASE NoStandby ADMIN USER NoStandbyAdmin IDENTIFIED BY NoStandbyAdmin ROLES=(DBA) STANDBYS=NONE;

Pluggable database created.

SQL>

SQL> alter pluggable database NoStandby open;

Pluggable database altered.

Create a new PDB with STANDBYS=ALL:

SQL> CREATE PLUGGABLE DATABASE YesStandby ADMIN USER YesStandbyAdmin IDENTIFIED BY YesStandbyAdmin ROLES=(DBA) STANDBYS=ALL;

Pluggable database created.

SQL> alter pluggable database NoStandby open;

Pluggable database altered.

Checking the Status of the new PDBs:

SQL> select con_id, name , open_mode from v$pdbs;

CON_ID NAME OPEN_MODE
---------- ------------------------------ ----------
2 PDB$SEED READ ONLY
3 NOSTANDBY READ WRITE
4 YESSTANDBY READ WRITE

Createing a Standby Container Database

Select "Create a new physical standby database":

"Select Online Backup"

Specify Backup Options:

Specify the Instance name, host name and Oracle Home of Standby Database:

Specify where the datafiles will be created:

Specify the unique name of Standby Database:

When the standby is created you will see the job created by EM12c with the status Succeeded:

Conclusion Post Standby Creation

Let's take a look into the new Standby CDB:

SQL> select name, cdb, database_role from v$database;

NAME CDB DATABASE_ROLE
--------- --- ----------------
CDB1 YES PHYSICAL STANDBY

SQL> select con_id, name , open_mode from v$pdbs;

CON_ID NAME OPEN_MODE
---------- ------------------------------ ----------
2 PDB$SEED MOUNTED
3 NOSTANDBY MOUNTED
4 YESSTANDBY MOUNTED

Confirming if the PDB have recovery enabled:

SQL> select name, recovery_status from v$pdbs

NAME RECOVERY
--------------- --------
PDB$SEED ENABLED
NOSTANDBY ENABLED
YESSTANDBY ENABLED

What?! I created a PDB in Primary CDB with STANDBYS=NONE, but it seems that all the PDBs were created with STANDBYS=ALL since they have recovery enabled.

Let's take a look into its datafiles maybe I am lucky and I find some datafiles as UNNAMED:

SQL> select p.name pdb_name, d.name dbf_name, d.status from v$datafile d, v$pdbs p where p.con_id=d.con_id order by p.name;

PDB_NAME DBF_NAME STATUS
---------- ------------------------------------------------------- -------
NOSTANDBY /data/CDB1STBY/datafile/o1_mf_usertbs_m8qspt99_.dbf ONLINE
NOSTANDBY /data/CDB1STBY/datafile/o1_mf_sysaux_luqspt0r_.dbf ONLINE
NOSTANDBY /data/CDB1STBY/datafile/o1_mf_deftbs_m3qspt5n_.dbf ONLINE
NOSTANDBY /data/CDB1STBY/datafile/o1_mf_catalog_mcqspta3_.dbf ONLINE
NOSTANDBY /data/CDB1STBY/datafile/o1_mf_system_m5qspt87_.dbf SYSTEM
PDB$SEED /data/CDB1STBY/datafile/o1_mf_sysaux_m0qspt2n_.dbf ONLINE
PDB$SEED /data/CDB1STBY/datafile/o1_mf_usertbs_m7qspt8p_.dbf ONLINE
PDB$SEED /data/CDB1STBY/datafile/o1_mf_system_maqspt9p_.dbf SYSTEM
PDB$SEED /data/CDB1STBY/datafile/o1_mf_deftbs_m2qspt4k_.dbf ONLINE
YESSTANDBY /data/CDB1STBY/datafile/o1_mf_system_m6qspt8m_.dbf SYSTEM
YESSTANDBY /data/CDB1STBY/datafile/o1_mf_sysaux_lvqspt28_.dbf ONLINE
YESSTANDBY /data/CDB1STBY/datafile/o1_mf_catalog_mdqspta4_.dbf ONLINE
YESSTANDBY /data/CDB1STBY/datafile/o1_mf_deftbs_m4qspt66_.dbf ONLINE
YESSTANDBY /data/CDB1STBY/datafile/o1_mf_usertbs_m9qspt9a_.dbf ONLINE

14 rows selected.

No, There is not any datafile as UNNAMED. So with this we have confirmed the following:

"Any new standby CDBs that are instantiated after the PDB has been created must disable the PDB for recovery explicitly to exclude it from the standby CDB."

Creating another PDB with STANDBYS=NONE with standby already created

In Primary Container Database let's create a new PDB with STANDBYS=NONE, but this time it will be different because we have already created one Standby Database. The PDB will be called "I Said No Standby" because that's what I told Oracle :)

SQL> CREATE PLUGGABLE DATABASE ISaidNoStandby ADMIN USER ISaidNoStandby IDENTIFIED BY ISaidNoStandby ROLES=(DBA) STANDBYS=NONE;

Pluggable database created.

SQL> alter pluggable database ISaidNoStandby open;

Pluggable database altered.

Let's confirm if the new PDB has recovery enabled:

SQL> select name, recovery_status from v$pdbs;

NAME RECOVERY
--------------- --------
PDB$SEED ENABLED
NOSTANDBY ENABLED
YESSTANDBY ENABLED
ISAIDNOSTANDBY DISABLED

So far all good. The standby "I Said No Standby" has not recovery enabled. What about its datafiles? are they getting recovered?

SQL> select * from v$recover_file

FILE# ONLINE ONLINE_ ERROR CHANGE# TIME CON_ID
---------- ------- ------- --------------- ---------- --------- ----------
20 OFFLINE OFFLINE FILE MISSING 0 5
21 OFFLINE OFFLINE FILE MISSING 0 5
22 OFFLINE OFFLINE FILE MISSING 0 5
23 OFFLINE OFFLINE FILE MISSING 0 5
24 OFFLINE OFFLINE FILE MISSING 0 5

But, there is only one pending thing to confirm. The datafiles should be UNNAMED. Let's confirm that:

SQL> select p.name pdb_name, d.name dbf_name, d.status from v$datafile d, v$pdbs p where p.con_id=d.con_id order by p.name;

PDB_NAME DBF_NAME STATUS
---------------- ------------------------------------------------------------ -------
ISAIDNOSTANDBY   /u01/app/oracle/product/12.1.0/dbhome_1/dbs/UNNAMED00024   RECOVER
ISAIDNOSTANDBY   /u01/app/oracle/product/12.1.0/dbhome_1/dbs/UNNAMED00023   RECOVER
ISAIDNOSTANDBY   /u01/app/oracle/product/12.1.0/dbhome_1/dbs/UNNAMED00021   RECOVER
ISAIDNOSTANDBY   /u01/app/oracle/product/12.1.0/dbhome_1/dbs/UNNAMED00020   SYSOFF
ISAIDNOSTANDBY   /u01/app/oracle/product/12.1.0/dbhome_1/dbs/UNNAMED00022   RECOVER
NOSTANDBY /data/CDB1STBY/datafile/o1_mf_usertbs_m8qspt99_.dbf ONLINE
NOSTANDBY /data/CDB1STBY/datafile/o1_mf_catalog_mcqspta3_.dbf ONLINE
NOSTANDBY /data/CDB1STBY/datafile/o1_mf_deftbs_m3qspt5n_.dbf ONLINE
NOSTANDBY /data/CDB1STBY/datafile/o1_mf_system_m5qspt87_.dbf SYSTEM
NOSTANDBY /data/CDB1STBY/datafile/o1_mf_sysaux_luqspt0r_.dbf ONLINE
PDB$SEED /data/CDB1STBY/datafile/o1_mf_system_maqspt9p_.dbf SYSTEM
PDB$SEED /data/CDB1STBY/datafile/o1_mf_deftbs_m2qspt4k_.dbf ONLINE
PDB$SEED /data/CDB1STBY/datafile/o1_mf_sysaux_m0qspt2n_.dbf ONLINE
PDB$SEED /data/CDB1STBY/datafile/o1_mf_usertbs_m7qspt8p_.dbf ONLINE
YESSTANDBY /data/CDB1STBY/datafile/o1_mf_usertbs_m9qspt9a_.dbf ONLINE
YESSTANDBY /data/CDB1STBY/datafile/o1_mf_deftbs_m4qspt66_.dbf ONLINE
YESSTANDBY /data/CDB1STBY/datafile/o1_mf_sysaux_lvqspt28_.dbf ONLINE
YESSTANDBY /data/CDB1STBY/datafile/o1_mf_system_m6qspt8m_.dbf SYSTEM
YESSTANDBY /data/CDB1STBY/datafile/o1_mf_catalog_mdqspta4_.dbf ONLINE

19 rows selected.

Disabling Recovery manually in a PDB

In case you have already some PDBs alrealdy created in your Primary Container Database and you are about to create a new Standby Container Database then you know now that the PDBs will be created normally in the Standby Side and if you want some PDB with recovery disabled you have to do that after Standby creating, and you have to do it manually. You can use the following Steps to accomplish it.

Cancel Recovery at CDB Level:

SQL> conn / as sysdba
Connected.
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> alter database recover managed standby database cancel ;

Database altered.

Disable recovery on the PDB that you want: For this example it will be "NoStandby" PDB.

SQL> alter session set container=NoStandby;

Session altered.

SQL> alter pluggable database disable recovery;

Pluggable database altered.

Enable Recovery at CDB Level:

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> alter database recover managed standby database disconnect from session;

Database altered.

Confirm that the PDB has recovery disabled:

SQL> select name, recovery_status from v$pdbs;

NAME RECOVERY
--------------- --------
PDB$SEED ENABLED
NOSTANDBY DISABLED
YESSTANDBY ENABLED
ISAIDNOSTANDBY DISABLED

SQL> select * from v$recover_file;

FILE# ONLINE ONLINE_ ERROR CHANGE# TIME CON_ID
---------- ------- ------- -------------- ---------- --------- ----------
10 OFFLINE OFFLINE 2915561 01-FEB-16 3
11 OFFLINE OFFLINE 2915561 01-FEB-16 3
12 OFFLINE OFFLINE 2915561 01-FEB-16 3
13 OFFLINE OFFLINE 2915561 01-FEB-16 3
14 OFFLINE OFFLINE 2915561 01-FEB-16 3
20 OFFLINE OFFLINE FILE MISSING 0 5
21 OFFLINE OFFLINE FILE MISSING 0 5
22 OFFLINE OFFLINE FILE MISSING 0 5
23 OFFLINE OFFLINE FILE MISSING 0 5
24 OFFLINE OFFLINE FILE MISSING 0 5

10 rows selected.

Enabling Recovery manually in a PDB

If you have a Standby Container Database where you have some PDB that have recovery disabled and you want to enable recovery on those PDBs you have to do it manually. The first question that you have to do is: does the PDB has at least one datafile missed (UNNAMED)? With this question you will know if you have to restore the whole PDB (all its datafiles), one or some datafiles or none of them.

On a PDB that has its datafiles already created

In Primary CDB, let's confirm which datafiles the PDB have:

SQL> select p.name pdb_name, d.name dbf_name, d.status from v$datafile d, v$pdbs p where p.con_id=d.con_id and p.name='NOSTANDBY' order by p.name;

PDB_NAME DBF_NAME STATUS
---------- ----------------------------------------------- -------
NOSTANDBY /data/CDB1/datafile/o1_mf_catalog_cbxrcmqm_.dbf ONLINE
NOSTANDBY /data/CDB1/datafile/o1_mf_deftbs_cbxqjr9s_.dbf ONLINE
NOSTANDBY /data/CDB1/datafile/o1_mf_usertbs_cbxqjr9x_.dbf ONLINE
NOSTANDBY /data/CDB1/datafile/o1_mf_system_cbxqjr9c_.dbf SYSTEM
NOSTANDBY /data/CDB1/datafile/o1_mf_sysaux_cbxqjr9p_.dbf ONLINE

In Standby CDB: Let's confirm if all the datafiles that we saw in Primary CDB are already created in Standby CDB (There is not any UNNAMED datafile).

SQL> select p.name pdb_name, d.name dbf_name, d.status from v$datafile d, v$pdbs p where p.con_id=d.con_id and p.name='NOSTANDBY' order by p.name;

PDB_NAME DBF_NAME STATUS
---------------- -------------------------------------------------- -------
NOSTANDBY /data/CDB1STBY/datafile/o1_mf_sysaux_luqspt0r_.dbf RECOVER
NOSTANDBY /data/CDB1STBY/datafile/o1_mf_system_m5qspt87_.dbf SYSOFF
NOSTANDBY /data/CDB1STBY/datafile/o1_mf_deftbs_m3qspt5n_.dbf RECOVER
NOSTANDBY /data/CDB1STBY/datafile/o1_mf_catalog_mcqspta3_.dbf RECOVER
NOSTANDBY /data/CDB1STBY/datafile/o1_mf_usertbs_m8qspt99_.dbf RECOVER

If there is not any datafile as UNNAMED we can go with the following approach:

Disable recovery at CDB Level:

SQL> conn / as sysdba
Connected.
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> alter database recover managed standby database cancel ;

Database altered.

Enable recovery on the specific PDB:

SQL> alter session set container=NoStandby;

Session altered.

SQL> alter pluggable database enable recovery;

Pluggable database altered.

Enable recovery at CDB Level:

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> show con_name

CON_NAME
-----------
CDB$ROOT
SQL> alter database recover managed standby database disconnect from session;

Database altered.

Confirm that the PDB has recovery enabled:

SQL> select name, recovery_status from v$pdbs;

NAME RECOVERY
--------------- --------
PDB$SEED ENABLED
NOSTANDBY ENABLED
YESSTANDBY ENABLED
ISAIDNOSTANDBY DISABLED

On a PDB that its datafiles are not created.

If al the datafiles of the PDB are UNNAMED in the Standby Container Database then we can go with the following approach:

SQL> select p.name pdb_name, d.name dbf_name, d.status from v$datafile d, v$pdbs p where p.con_id=d.con_id and p.name='ISAIDNOSTANDBY' order by p.name;

PDB_NAME DBF_NAME STATUS
---------------- ------------------------------------------------------------ -------
ISAIDNOSTANDBY /u01/app/oracle/product/12.1.0/dbhome_1/dbs/UNNAMED00020 SYSOFF
ISAIDNOSTANDBY /u01/app/oracle/product/12.1.0/dbhome_1/dbs/UNNAMED00021 RECOVER
ISAIDNOSTANDBY /u01/app/oracle/product/12.1.0/dbhome_1/dbs/UNNAMED00023 RECOVER
ISAIDNOSTANDBY /u01/app/oracle/product/12.1.0/dbhome_1/dbs/UNNAMED00022 RECOVER
ISAIDNOSTANDBY /u01/app/oracle/product/12.1.0/dbhome_1/dbs/UNNAMED00024 RECOVER

Restoring all the datafiles of the PDB from Primary CDB to the Standby CDB. In the Standby CDB Server execute the following:

[oracle@db12102s ~]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Mon Feb 1 06:28:00 2016

connected to target database: CDB1 (DBID=874182551, not open)

RMAN> run{
set newname for pluggable database ISaidNoStandby to new;
restore pluggable database ISaidNoStandby from service CDB1_Primary;
switch datafile all;
}
2> 3> 4> 5>
executing command: SET NEWNAME

Starting restore at 01-FEB-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=47 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=21 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service CDB1_Primary
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00020 to /data/CDB1STBY/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 CDB1_Primary
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00021 to /data/CDB1STBY/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service CDB1_Primary
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00022 to /data/CDB1STBY/datafile/o1_mf_deftbs_%u_.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 CDB1_Primary
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00023 to /data/CDB1STBY/datafile/o1_mf_usertbs_%u_.dbf
channel ORA_DISK_2: restore complete, elapsed time: 00:00:11
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: using network backup set from service CDB1_Primary
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00024 to /data/CDB1STBY/datafile/o1_mf_catalog_%u_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_2: restore complete, elapsed time: 00:00:01
Finished restore at 01-FEB-16

datafile 20 switched to datafile copy
input datafile copy RECID=21 STAMP=902644090 file name=/data/CDB1STBY/datafile/o1_mf_system_cbyjb6r6_.dbf
datafile 21 switched to datafile copy
input datafile copy RECID=23 STAMP=902644097 file name=/data/CDB1STBY/datafile/o1_mf_sysaux_cbyjb6z6_.dbf
datafile 22 switched to datafile copy
input datafile copy RECID=22 STAMP=902644096 file name=/data/CDB1STBY/datafile/o1_mf_deftbs_cbyjbgcc_.dbf
datafile 23 switched to datafile copy
input datafile copy RECID=24 STAMP=902644098 file name=/data/CDB1STBY/datafile/o1_mf_usertbs_cbyjbkkm_.dbf
datafile 24 switched to datafile copy
input datafile copy RECID=25 STAMP=902644098 file name=/data/CDB1STBY/datafile/o1_mf_catalog_cbyjbkr7_.dbf

NOTE: For this example we are restoring the whole PDB but if only some datafile are missed, you can restore those datafiles that are UNNAMED instead of recover the whole PDB.

Confirming if there is any datafile as UNNAMED:

SQL> select p.name pdb_name, d.name dbf_name, d.status from v$datafile d, v$pdbs p where p.con_id=d.con_id and p.name='ISAIDNOSTANDBY' order by p.name

PDB_NAME DBF_NAME STATUS
---------------- ---------------------------------------------------- -------
ISAIDNOSTANDBY /data/CDB1STBY/datafile/o1_mf_system_cbyjb6r6_.dbf SYSOFF
ISAIDNOSTANDBY /data/CDB1STBY/datafile/o1_mf_sysaux_cbyjb6z6_.dbf RECOVER
ISAIDNOSTANDBY /data/CDB1STBY/datafile/o1_mf_usertbs_cbyjbkkm_.dbf RECOVER
ISAIDNOSTANDBY /data/CDB1STBY/datafile/o1_mf_deftbs_cbyjbgcc_.dbf RECOVER
ISAIDNOSTANDBY /data/CDB1STBY/datafile/o1_mf_catalog_cbyjbkr7_.dbf RECOVER

Once there is not any datafile as UNNAMED then we can enable recovery on that PDB.

Disable recovery at CDB level:

SQL> conn / as sysdba
Connected.
SQL> alter database recover managed standby database cancel ;

Database altered.

Enable Recovery on the PDB that you want:

SQL> alter session set container=ISaidNoStandby;

Session altered.

SQL> alter pluggable database enable recovery;

Pluggable database altered.

Enable recovery at CDB Level:

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> alter database recover managed standby database disconnect from session;

Database altered.

Confirming that the PDB has recovery enabled:

SQL> select name, recovery_status from v$pdbs;

NAME RECOVERY
------------- --------
PDB$SEED ENABLED
NOSTANDBY ENABLED
YESSTANDBY ENABLED
ISAIDNOSTANDBY ENABLED

With these examples, we have confirmed the following:

"It is possible to enable a PDB on a standby CDB after it was excluded on that standby CDB."

The Complete Oracle Blog For Dba

Saturday 3 June 2017

How to use STANDBYS Clause to Disable recovery for Specific PDBs

Create 2 Pluggable Databases in Primary CDB.

Createing a Standby Container Database

Conclusion Post Standby Creation

Creating another PDB with STANDBYS=NONE with standby already created

Disabling Recovery manually in a PDB

Enabling Recovery manually in a PDB

No comments:

Post a Comment