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:
- PDB Containers Clause
- PDB Standbys Clause
- 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 standbyWe 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:
[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