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:
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:
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:
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:
Standby database as below:
In following example using Standbys clause in create pluggable database statement.
On primary database:
Alert log of primary database as below:
Alert log of standby database as below:
On standby database:
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.
Checking status of data files in new primary database :
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.
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.
Copy files to standby side, PDB must be closed or must be opened as read only.
Enable recovery on standby database:
Data guard running well:
Regards
Mahir M. Quluzade
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 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
Comments
Post a Comment