Monday, October 28, 2013

Oracle Database 12c - What's new with Oracle Dataguard for Physical Standbys? (New Features)

We know Dataguard by far is the industry's utmost reliable DR solution to protect the enterprise data for Oracle Databases. Let me share some of the new features I have learned so far,

Following points to be noted
=>SYSDG Privilege
A special privilege "SYSDG" has been introduced to simplify Dataguard operations.

=>Far Sync Instance
A New Standby destination type is allowed in Active Dataguard environment that ensures zero data loss failover and zero performance impact on Primary Database, is called "Far Sync Instance". This destination contains parameterfile, controlfile, and standby redo logs (SRLs), it receives the redo from Primary Database, and archives it to local destination through Standby Redo Logs. There are no Datafiles here. Technically, A Standby Database Instance without Datafiles is called Far Sync Instance. A far sync instance consumes very little disk and processing resources, yet provides the ability to failover to a terminal destination with zero data loss, as well as offload the primary database of other types of overhead (for example, redo transport).
To setup Far Sync Instance: 
(Same as configuring Dataguard except backup/restore operations of the Primary Database)
1)Create pfile from spfile of the Primary, and copy to to Far Sync Server, and start the Instance
2)Add the Far Sync Destination, EX: "CDBFS"(Net Service Name/DB_UNIQUE_NAME), to Dataguard configuration (under LOG_ARCHVE_CONFIG=DG_CONFIG)
3)Set a new destination parameter to enable Log Transport Services, EX: LOG_ARCHIVE_DEST_3 & LOG_ARCHIVE_DEST_STATE_3
4)Create the Far Sync Instance Control file, copy it to FSI Server, and mount
SYS@CDB AS SYSDBA 28-OCT-13> ALTER DATABASE CREATE FAR SYNC INSTANCE CONTROLFILE AS '/fra/control01.ctl';
Database altered.
5)FSI can now receive the redo by enabling its destination on primary

=>Maximum Availability mode now allows SYNC mode with "NOAFFIRM" transport, i.e., A redo reception only acknowledgment is delivered by Standby to Primary in order to receive the next synchronous redo, but no longer requires an acknowledgement for redo commit on Standby thus reduces the performance overhead on Primary Database but maybe at the cost of some data loss.
=>There is no difference in configuring Dataguard and creating a Physical Standby for a Multitenant hosted CDB Database, which is same as Non CDB Database. We require to login to the root container and backup the whole Database that includes ROOT, SEED, and all PLUGGABLE Databases.

=>All DDL SQL's for recovery, role transitions etc.. can only be executed on ROOT Container.
For Example:
SYS@CDBDR AS SYSDBA 28-OCT-13> ALTER SESSION SET CONTAINER=PDB;
Session altered.
SYS@CDBDR AS SYSDBA 28-OCT-13> SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL;
SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
PDB
SYS@CDBDR AS SYSDBA 28-OCT-13> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database

=>A Role transition(Switchover/Failover/Snapshots Standby etc..) is applicable to the entire CDB.

=>A PDB can also be opened in READ ONLY mode on Standby irrespective of its state on Primary.

For Example:
On Primary (CDB):
SYS@CDB AS SYSDBA 28-OCT-13>  select name,open_mode from v$pdbs;
NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDB             MOUNTED
SYS@CDB AS SYSDBA 28-OCT-13> alter pluggable database pdb open;
Pluggable database altered.
SYS@CDB AS SYSDBA 28-OCT-13> select name,open_mode from v$pdbs;
NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDB       READ WRITE

On Standby (CDBDR):
SYS@CDBDR AS SYSDBA 28-OCT-13>  select name,open_mode from v$pdbs;
NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDB       READ ONLY
SYS@CDBDR AS SYSDBA 28-OCT-13> alter pluggable database pdb close;
Pluggable database altered.
SYS@CDBDR AS SYSDBA 28-OCT-13> select name,open_mode from v$pdbs;
NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDB       MOUNTED

=>Now switchover possibility can be verified by means of ALTER command.
For Example:
SYS@CDB AS SYSDBA 28-OCT-13> ALTER DATABASE SWITCHOVER TO CDBDR VERIFY;
Database altered.
In Alert log:
SWITCHOVER VERIFY: Send VERIFY request to switchover target CDBDR
SWITCHOVER VERIFY COMPLETE
If it returns "Database altered" message, it means switchover target is available to take over the desired role.
Let's cancel the redo apply on Standby and verify on Primary what it returns
On Standby:
SYS@CDBDR AS SYSDBA 28-OCT-13> alter database recover managed standby database cancel;
Database altered.
On Primary:
SYS@CDB AS SYSDBA 28-OCT-13>  ALTER DATABASE SWITCHOVER TO CDBDR VERIFY;
 ALTER DATABASE SWITCHOVER TO CDBDR VERIFY
*
ERROR at line 1:
ORA-16470: Redo Apply is not running on switchover target
It means Switchover target is not ready to takeover

=>Now Switchover operation is more simplified.
To Switchover
1)Verify the Switchover possibility as explained above
2)Switchover
On Primary (Make it Physical Standby)
SYS@CDB AS SYSDBA 28-OCT-13> ALTER DATABASE SWITCHOVER TO CDBDR;
Database altered.
On Standby
SYS@CDBDR AS SYSDBA 28-OCT-13> ALTER DATABASE OPEN;
Database altered.
On Primary
SYS@CDB AS SYSDBA 28-OCT-13> STARTUP
ORACLE instance started.
Total System Global Area  417546240 bytes
Fixed Size     2289064 bytes
Variable Size   255853144 bytes
Database Buffers   155189248 bytes
Redo Buffers     4214784 bytes
Database mounted.
Database opened.
SYS@CDB AS SYSDBA 28-OCT-13>  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Database altered.
On Standby
SYS@CDBDR AS SYSDBA 28-OCT-13> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
System altered.

=>DML operations on Temporary tables now permitted on Standby Database in Active Dataguard mode and also use of sequences.

I will keep this updated as I keep learning myself, please feel free to leave the feedback or ask any questions.