Oracle Multitenant - PDB New Clauses - Standbys (12.1.0.2) - Part II

As you know, Oracle Database 12c Release 1 (12.1.0.2.0) already released at July 22, 2014.
You can download new release from here.

Oracle Multitenant option also supports some news in Oracle Database 12c Release 1 (12.1.0.2.0).
With new release coming new PDB Clauses.

This article series covers following new clauses:
  1. PDB Containers Clause
  2. PDB Standbys Clause
  3. PDB Logging Clause  (as soon)
Part II: PDB Standbys Clause

PDB Inclusion in Standby CDBs feature is coming with Oracle Database 12c Release 1 (12.1.0.2).

The STANDBYS clause of the CREATE PLUGGABLE DATABASE statement specifies whether the new PDB is included in standby CDBs. You can specify one of the following values for the STANDBYS clause:

  • ALL includes the new PDB in all of the standby CDBs.
  • NONE excludes the new PDB from all of the standby CDBs.

When a PDB is not included in any of the standby CDBs, the PDB's data files are offline and marked as unnamed on all of the standby CDBs. 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.

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

Follow my examples:

Broker-managed Data Guard configuration shown as below:

[oracle@oel62-ora12c2 ~]$ dgmgrl / 
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected as SYSDG.
DGMGRL> show configuration

Configuration - dg

  Protection Mode: MaxPerformance
  Members:
  prmcdb - Primary database
    stbcdb - Physical standby database 

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 10 seconds ago)

Primary database is Multitenant Container Database - CDB (prmcdb).



We can create or drop pluggable databases on primary database. Data Guard will create or drop this databases on primary database automatically like as below:

[oracle@oel62-ora12c2 ~]$ export ORACLE_SID=prmcdb
[oracle@oel62-ora12c2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jul 25 15:52:32 2014

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select banner from  v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

SQL> select name, cdb from v$database;

NAME   CDB
--------- ---
PRMCDB   YES

SQL> select name, open_mode from v$pdbs;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PRMPDB01         READ WRITE

SQL> create pluggable database prmpdb03 admin user pdb03admin identified by pdb03admin file_name_convert=('pdbseed','prmpdb03');

Pluggable database created.

SQL> select name, open_mode from v$pdbs;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PRMPDB01         READ WRITE
PRMPDB03         READ WRITE

Standby database as below:

[oracle@oel62-ora12c2 ~]$ export ORACLE_SID=stbcdb
[oracle@oel62-ora12c2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jul 25 15:52:10 2014

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

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

DB_UNIQUE_NAME         DATABASE_ROLE          CDB
------------------------------ ---------------- --------
stbcdb          PHYSICAL STANDBY           YES

SQL> select name, open_mode from v$pdbs;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         MOUNTED
PRMPDB01         MOUNTED
PRMPDB03         MOUNTED

SQL> select name, status , enabled from  v$datafile;

NAME                                                         STATUS   ENABLED
--------------------------------------------------------- ------- ----------
/u01/app/oracle/oradata/stbcdb/system01.dbf                    SYSTEM READ WRITE
/u01/app/oracle/oradata/stbcdb/sysaux01.dbf                    ONLINE READ WRITE
/u01/app/oracle/oradata/stbcdb/undotbs01.dbf                   ONLINE READ WRITE
/u01/app/oracle/oradata/stbcdb/pdbseed/system01.dbf            SYSTEM READ ONLY
/u01/app/oracle/oradata/stbcdb/users01.dbf                     ONLINE READ WRITE
/u01/app/oracle/oradata/stbcdb/pdbseed/sysaux01.dbf            ONLINE READ ONLY
/u01/app/oracle/oradata/stbcdb/prmpdb01/system01.dbf           SYSTEM READ WRITE
/u01/app/oracle/oradata/stbcdb/prmpdb01/sysaux01.dbf           ONLINE UNKNOWN
/u01/app/oracle/oradata/stbcdb/prmpdb01/prmpdb01_users01.dbf   ONLINE UNKNOWN
/u01/app/oracle/oradata/stbcdb/prmpdb03/system01.dbf           SYSTEM READ WRITE
/u01/app/oracle/oradata/stbcdb/prmpdb03/sysaux01.dbf           ONLINE UNKNOWN

11 rows selected.


In following example using Standbys clause in create pluggable database statement.

On primary database:

SQL> create pluggable database prmpdb02 admin user pdb02admin identified by pdb02admin file_name_convert=('pdbseed','prmpdb02') standbys=NONE;

Pluggable database created.

SQL> alter pluggable database prmpdb02 open;

Pluggable database altered.

Alert log of primary database as below:

Thu Jul 31 13:59:29 2014
create pluggable database prmpdb02 admin user pdb02admin identified by * file_name_convert=('pdbseed','prmpdb02') standbys=NONE
Thu Jul 31 13:59:34 2014
Opatch XML is skipped for PDB PDB$SEED (conid=2)
 APEX_040200.APEX$_WS_NOTES (CONTENT) - CLOB populated
Thu Jul 31 14:00:00 2014
Closing scheduler window
Closing Resource Manager plan via scheduler window
Clearing Resource Manager plan at pdb PRMPDB01 (3) via parameter
Thu Jul 31 14:00:07 2014
****************************************************************
Pluggable Database PRMPDB02 with pdb id - 4 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
****************************************************************
Database Characterset for PRMPDB02 is AL32UTF8
Thu Jul 31 14:00:27 2014
Deleting old file#5 from file$ 
Deleting old file#7 from file$ 
Adding new file#45 to file$(old file#5) 
Adding new file#46 to file$(old file#7) 
Successfully created internal service prmpdb02 at open
Thu Jul 31 14:00:32 2014
TABLE SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY: ADDED INTERVAL PARTITION SYS_P266 (41850) VALUES LESS THAN (TO_DATE(' 2014-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
ALTER SYSTEM: Flushing buffer cache inst=0 container=4 local
Thu Jul 31 14:00:46 2014
****************************************************************
Post plug operations are now complete.
Pluggable database PRMPDB02 with pdb id - 4 is now marked as NEW.
****************************************************************
Completed: create pluggable database prmpdb02 admin user pdb02admin identified by * file_name_convert=('pdbseed','prmpdb02') standbys=NONE

Alert log of standby database as below:

Thu Jul 31 13:59:59 2014
Recovery created pluggable database PRMPDB02
File #45 added to control file as 'UNNAMED00045'. Originally created as:
'/u01/app/oracle/oradata/prmcdb/prmpdb02/system01.dbf'
because the pluggable database was created with nostandby
or the tablespace belonging to the pluggable database is
offline.
File #46 added to control file as 'UNNAMED00046'. Originally created as:
'/u01/app/oracle/oradata/prmcdb/prmpdb02/sysaux01.dbf'
because the pluggable database was created with nostandby
or the tablespace belonging to the pluggable database is
offline.

On standby database:

[oracle@oel62-ora12c2 stbcdb]$ export ORACLE_SID=stbcdb
[oracle@oel62-ora12c2 stbcdb]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 31 14:03:15 2014

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select database_role from  v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

SQL> select name, open_mode from  v$pdbs;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         MOUNTED
PRMPDB01         MOUNTED
PRMPDB02         MOUNTED
PRMPDB03         MOUNTED

SQL> alter session set container=prmpdb02;

Session altered.

SQL> select file#, name, status from v$datafile;

   FILE#   NAME       STATUS
------- --------- -------------------------------------------- ---------------
 47     /u01/app/oracle/product/12.1.0.2/dbhome/dbs/UNNAMED00047    SYSOFF
 48     /u01/app/oracle/product/12.1.0.2/dbhome/dbs/UNNAMED00048    RECOVER


Performing switchover to CDB, which is contain excluded PDB.  

Before enable a PDB on a standby CDB after it was excluded on that standby CDB, we can perform switchover on our Data Guard configuration.

[oracle@oel62-ora12c2 stbcdb]$ dgmgrl sys/********@stbcdb 
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected as SYSDBA.
DGMGRL> validate database stbcdb

  Database Role:     Physical standby database
  Primary Database:  prmcdb

  Ready for Switchover:  Yes 
  Ready for Failover:    Yes (Primary Running)

  Temporary Tablespace File Information:
    prmcdb TEMP Files:  5
    stbcdb TEMP Files:  4

  Flashback Database Status:
    prmcdb:  Off
    stbcdb:  Off

 ...

DGMGRL> switchover to stbcdb;
Performing switchover NOW, please wait...
New primary database "stbcdb" is opening...
Operation requires start up of instance "prmcdb" on database "prmcdb"
Starting instance "prmcdb"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "stbcdb"
DGMGRL> 
DGMGRL> show configuration

Configuration - dg

  Protection Mode: MaxPerformance
  Members:
  stbcdb - Primary database
    prmcdb - Physical standby database 

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 132 seconds ago)

DGMGRL> exit

Checking status of data files in new primary database :

[oracle@oel62-ora12c2 ~]$ export ORACLE_SID=stbcdb
[oracle@oel62-ora12c2 ~]$ sqlplus / as  sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Aug 4 14:07:35 2014

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options

SQL> select open_mode, database_role, db_unique_name from v$database;

OPEN_MODE      DATABASE_ROLE    DB_UNIQUE_NAME
-------------------- ---------------- ------------------------------
READ WRITE      PRIMARY       stbcdb

SQL> select name, open_mode from  v$pdbs;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         MOUNTED
PRMPDB01         MOUNTED
PRMPDB02         MOUNTED
PRMPDB03         MOUNTED

SQL> select name, status from  v$datafile;

NAME                                                        STATUS
--------------------------------------------------------------------------
/u01/app/oracle/oradata/stbcdb/system01.dbf                  SYSTEM
/u01/app/oracle/oradata/stbcdb/sysaux01.dbf                  ONLINE
/u01/app/oracle/oradata/stbcdb/undotbs01.dbf                 ONLINE
/u01/app/oracle/oradata/stbcdb/pdbseed/system01.dbf          SYSTEM
/u01/app/oracle/oradata/stbcdb/users01.dbf                   ONLINE
/u01/app/oracle/oradata/stbcdb/pdbseed/sysaux01.dbf          ONLINE
/u01/app/oracle/oradata/stbcdb/prmpdb01/system01.dbf         SYSTEM
/u01/app/oracle/oradata/stbcdb/prmpdb01/sysaux01.dbf         ONLINE
/u01/app/oracle/oradata/stbcdb/prmpdb01/prmpdb01_users01.dbf ONLINE
/u01/app/oracle/oradata/stbcdb/prmpdb03/system01.dbf         SYSTEM
/u01/app/oracle/oradata/stbcdb/prmpdb03/sysaux01.dbf         ONLINE
/u01/app/oracle/product/12.1.0.2/dbhome/dbs/UNNAMED00052     SYSOFF
/u01/app/oracle/product/12.1.0.2/dbhome/dbs/UNNAMED00053     RECOVER

13 rows selected.


We performed switchover on CDB, because excluded PDB's (prmpdb02) data files are offline and locked. It means we cannot make changes in PDB, in other words data files  will not change.

We must switchover again, before enable PDB.

[oracle@oel62-ora12c2 stbcdb]$ dgmgrl sys/Mq12345678@prmcdb
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected as SYSDBA.

DGMGRL> show configuration

Configuration - dg

  Protection Mode: MaxPerformance
  Members:
  stbcdb - Primary database
    prmcdb - Physical standby database 

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 42 seconds ago)

DGMGRL> switchover to prmcdb;
Performing switchover NOW, please wait...
New primary database "prmcdb" is opening...
Operation requires start up of instance "stbcdb" on database "stbcdb"
Starting instance "stbcdb"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "prmcdb"
DGMGRL> show configuration

Configuration - dg

  Protection Mode: MaxPerformance
  Members:
  prmcdb - Primary database
    stbcdb - Physical standby database 
      Error: ORA-16843: errors discovered in diagnostic repository

Fast-Start Failover: DISABLED

Configuration Status:
ERROR   (status updated 0 seconds ago)

DGMGRL> show database verbose stbcdb;

Database - stbcdb

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          0 seconds (computed 0 seconds ago)
  Average Apply Rate: 19.00 KByte/s
  Active Apply Rate:  0 Byte/s
  Maximum Apply Rate: 0 Byte/s
  Real Time Query:    OFF
  Instance(s):
    stbcdb

  Database Error(s):
    ORA-16838: one or more system data files are offline
    ORA-16841: one or more user data files are offline

  Properties:
    DGConnectIdentifier             = 'stbcdb'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    RedoRoutes                      = ''
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyLagThreshold               = '0'
    TransportLagThreshold           = '0'
    TransportDisconnectedThreshold  = '30'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'MANUAL'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = 'prmcdb, stbcdb'
    LogFileNameConvert              = 'prmcdb, stbcdb'
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oel62-ora12c2.localdomain)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=stbcdb_DGMGRL)(INSTANCE_NAME=stbcdb)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
ERROR

DGMGRL> 

As you seen, switchover performed succesfully. But some data files (excluded PDB's data files) are offline.

ORA-16838: one or more system data files are offline
 ORA-16841: one or more user data files are offline

Don't worry, you can continue enable PDB.

Enabling excluded PDB on standby

We can enable a PDB on a standby CDB after it was excluded on that standby CDB by copying the data files to the correct location, bringing the PDB online, and marking it as enabled for recovery.

[oracle@oel62-ora12c2 ~]$ export ORACLE_SID=prmcdb
[oracle@oel62-ora12c2 ~]$ dgmgrl / 
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected as SYSDG.
DGMGRL> show configuration

Configuration - dg

  Protection Mode: MaxPerformance
  Members:
  prmcdb - Primary database
    stbcdb - Physical standby database 

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 44 seconds ago)
DGMGRL> edit database stbcdb set state = apply-off;
Succeeded.
DGMGRL> edit database stbcdb set  property StandbyFileManagement =Manual;
Property "standbyfilemanagement" updated
DGMGRL> 

Copy files to standby side, PDB  must be closed or must be opened as read only.


[oracle@oel62-ora12c2 ~]$ cd /u01/app/oracle/oradata/prmcdb/prmpdb02
[oracle@oel62-ora12c2 prmpdb02]$ ls -l
total 819280
-rw-r----- 1 oracle oinstall  20979712 Jul 31 14:53 prmpdb02_temp012014-07-30_11-02-52-AM.dbf
-rw-r----- 1 oracle oinstall 576724992 Jul 31 14:53 sysaux01.dbf
-rw-r----- 1 oracle oinstall 262152192 Jul 31 14:53 system01.dbf
[oracle@oel62-ora12c2 prmpdb02]$ 
[oracle@oel62-ora12c2 prmpdb02]$ cp * /u01/app/oracle/oradata/stbcdb/prmpdb02
[oracle@oel62-ora12c2 prmpdb02]$ cp * /u01/app/oracle/oradata/stbcdb/prmpdb02
[oracle@oel62-ora12c2 prmpdb02]$ 

Enable recovery on standby database:

[oracle@oel62-ora12c2 ~]$ export ORACLE_SID=stbcdb
[oracle@oel62-ora12c2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 31 15:03:13 2014

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> alter session set container=prmpdb02;

Session altered.

SQL> alter database create datafile 47 as '/u01/app/oracle/oradata/stbcdb/prmpdb02/system01.dbf';

Database altered.

SQL> alter database create datafile 48 as '/u01/app/oracle/oradata/stbcdb/prmpdb02/sysaux01.dbf';

Database altered.

SQL> alter pluggable database enable recovery;

Pluggable database altered.

SQL> select file#, name, status, enabled from  v$datafile;

    FILE#                                     NAME       STATUS       ENABLED
------- --------------------------------------- --------------  ------------
 47  /u01/app/oracle/oradata/stbcdb/prmpdb02/system01.dbf SYSTEM     READ ONLY
 48  /u01/app/oracle/oradata/stbcdb/prmpdb02/sysaux01.dbf RECOVER     UNKNOWN


Data guard running well:

DGMGRL> show configuration

Configuration - dg

  Protection Mode: MaxPerformance
  Members:
  prmcdb - Primary database
    stbcdb - Physical standby database 

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 58 seconds ago)



Regards
Mahir M. Quluzade

Popular posts from this blog

Which device is mapped Oracle ASM disk?

Oracle Data Guard 11g - How to safely remove a Data Guard Broker configuration?

Oracle Data Guard 11g - Redo Transport User