Dataguard – Creating a Physical Standby Instructions
=================================
The DB_UNIQUE_NAME for the primary database on NODE1 is UNIQUEDB1 and for the physical standby database on NODE2 is UNIQUEDB2.
The database sids are DB1 and DB2.
The domain is .dom.local


Primary Database Environment pre-requisites

============================
Update /etc/hosts file on any Z1ND01 node to include I.P. addresses and hostnames of standby nodes.
Copy this version of the hosts file to /etc/hosts on all nodes in the Z1ND02 cluster.
Update $ORACLE_HOME/network/admin/tnsnames.ora to include entry for the physical standby database :-

DB2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = NODE2) (PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED
(SERVICE_NAME = DB2.dom.local)
)
)

Update $ORACLE_HOME/network/admin/listener.ora with required DGMGRL (Data Guard Broker) entries:-

SID_LIST_LISTENER_NODE1 =
(SID_LIST =
(SID_DESC =
(SID_NAME = DB1)
(GLOBAL_DBNAME= db1_DGMGRL.dom.local)
(ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
)
)

Update $ORACLE_HOME/network/admin/sqlnet.ora with DEFAULT_SDU_SIZE=32767

Create standby redo logs
Start SQLPLUS :-
$ sqlplus / as sysdba
At the SQLPLUS prompt issue the following commands :-
SQL> alter database add standby logfile thread 1 ‘+FRA’ size 100M;
SQL> alter database add standby logfile thread 1 ‘+FRA’ size 100M;
SQL> alter database add standby logfile thread 1 ‘+FRA’ size 100M;
SQL> alter database add standby logfile thread 1 ‘+FRA’ size 100M;
SQL> alter database add standby logfile thread 2 ‘+FRA’ size 100M;
SQL> alter database add standby logfile thread 2 ‘+FRA’ size 100M;
SQL> alter database add standby logfile thread 2 ‘+FRA’ size 100M;
SQL> alter database add standby logfile thread 2 ‘+FRA’ size 100M;
SQL> alter database add standby logfile thread 3 ‘+FRA’ size 100M;
SQL> alter database add standby logfile thread 3 ‘+FRA’ size 100M;
SQL> alter database add standby logfile thread 3 ‘+FRA’ size 100M;
SQL> alter database add standby logfile thread 3 ‘+FRA’ size 100M;

Set force logging on primary database
Start SQLPLUS :-
$ sqlplus / as sysdba
SQL> alter database force logging;


Physical Standby environment pre-requisites (NODE2)

=================================
Edit /etc/oratab  file and add the db entry
+ASM1:/u01/app/oracle/product/11.1.0/asm_1:N
db2:/u01/app/oracle/product/11.1.0/db_1:N

Create admin directories (on all cluster2 nodes Z1ND02CLSV104/2/3)
Login as Oracle user
$ mkdir –p  /u01/app/oracle/admin/db2
$ cd /u01/app/oracle/admin/db2
$ mkdir  adump dpdump hdump pfile scripts

Pull oracle password file from NODE1 to NODE2
$ cd /u01/app/oracle/product/11.1.0/db_1/dbs
$ scp NODE1:/u01/app/oracle/product/11.1.0/db_1/dbs/orapwdb1 orapwdb2

Pull tnsnames file (tnsnames.ora) from NODE1 to NODE2
Pull sqlnet file (sqlnet.ora) from NODE1 to NODE2
$ cd /u01/app/oracle/product/11.1.0/asm_1/network/admin
$ scp NODE1:/u01/app/oracle/product/11.1.0/asm_1/network/admin/listener.ora .
$ scp NODE1:/u01/app/oracle/product/11.1.0/asm_1/network/admin/sqlnet.ora .
$ scp NODE1:/u01/app/oracle/product/11.1.0/asm_1/network/admin/tnsnames.ora .

Edit listener.ora file
$ vi /u01/app/oracle/product/11.1.0/asm_1/network/admin/listener.ora
add the following lines:
SID_LIST_LISTENER_NODE2 =
(SID_LIST =
(SID_DESC =
(SID_NAME = DB2)
(GLOBAL_DBNAME= db2_DGMGRL.dom.local)
(ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
)
)

Reload the listener

Create ‘stub’ Initialization parameter file ‘pfile’
$ cd /u01/app/oracle/product/11.1.0/db_1/dbs
$ scp NODE1:/u01/app/oracle/product/11.1.0/db_1/dbs/initDB1.ora initDB2.ora

Edit file & replace ‘DB1’ with ‘DB2’
THEN
$ cp initDB2.ora initDB2.ora_full

Create spfile from saved pfile and startup db2
Login as Oracle user
$ Sqlplus / as sysdba
SQL> startup nomount pfile=’/u01/app/oracle/product/11.1.0/db_1/dbs/initDB2.ora’
SQL> create spfile=’+DATA/uniqueDB2/spfileDB2.ora’ from pfile=’/u01/app/oracle/product/11.1.0/db_1/dbs/initDB2.ora’
SQL> shutdown immediate 
SQL> exit

EDIT initDB2.ora and replace the full contents with the following single line
spfile=’+DATA/uniqueDB2/spfileDB2.ora’
THEN
$ Sqlplus / as sysdba
SQL> startup nomount
SQL> exit


Back-up Primary Database using RMAN (NODE1)

=============================
Start RMAN and backup database
$ cd $HOME
$ rman target /
RMAN> backup database format ‘/home/oracle/db1_%U’;
RMAN> backup archivelog all format ‘/home/oracle/arc_db1_%U’;
RMAN> backup current controlfile for standby format ‘/home/oracle/ctl_db1_%U’;
RMAN> exit;


Instantiate Physical Standby database (NODE2)

=============================
Logon to NODE2 as the oracle user

Copy database backups created in step 4
$ cd $HOME
$ scp NODE1:*db1* .

Start RMAN and create standby database
$ . oraenv
DB2
$ rman target sys/password@DB1 auxiliary  /
RMAN> duplicate target database for standby
RMAN>exit


Start DataGuard Broker and create configuration

==============================

Start the Data Guard Broker process
Logon to node1 as oracle
Start SQLPLUS :-
$ sqlplus / as sysdba
SQL> alter system set dg_broker_start=TRUE scope=spfile sid=’*’;

Logon to node2 as oracle user and repeat the process
Start SQLPLUS :-
$ sqlplus / as sysdba
SQL> alter system set dg_broker_start=TRUE scope=spfile sid=’*’;

Create the Dataguard Broker configuration
Logon to node1
$ . oraenv
DB1
$ dgmgrl sys/password
DGMGRL> create configuration DB1DB2 as primary database is UNIQUEDB1 connect identifier is UNIQUEDB1.dev.local;
DGMGRL> add database UNIQUEDB2 as connect identifier is UNIQUEDB2.dev.local;
DGMGRL> edit database DB2 set property PreferredApplyInstance=1;
DGMGRL> enable configuration
DGMGRL> enable database DB2
DGMGRL> exit

$ dgmgrl sys/password
DGMGRL> Show configuration;
DGMGRL> Show database verbose z1osba;
DGMGRL> Show database verbose z1osbb;


Check  the status of the Data Guard Broker configuration

Check Redo Log apply and Managed Recovery Process (MRP0) is working
=============================================
Logon to NODE2 as oracle
$ . oraenv
DB2
$ sqlplus / as sysdba
SQL> select * from gv$managed_standby where process=’MRP0’;

INST_ID PROCESS  PID   STATUS       CLIENT_P CLIENT_PID CLIENT_DBID GROUP#
1       MRP0     16956 APPLYING_LOG N/A      N/A        N/A         N/A

RESETLOG_ID THREAD# SEQUENCE# BLOCK# BLOCKS DELAY_MINS KNOWN_AGENTS ACTIVE_AGENTS
693650262   3       676       5171   102400 0          0            0

Execute the same query a number of times and you should notice that the value for the BLOCK# column changes.
This indicates that redo log apply is applying blocks for the current log (indicated by the values for the THREAD# and SEQUENCE# columns)  and everything is ok.

 

 

 

After you create the configuration with DGMGRL, you can set database properties at any time.
For example, the following statements set the LogArchiveFormat and StandbyArchiveLocation configurable database properties for the UNIQUEDB2 standby database:
DGMGRL> EDIT DATABASE ‘UNIQUEDB2’ SET PROPERTY ‘LogArchiveFormat’=’log_%t_%s_%r_%d.arc’;
Property “LogArchiveFormat” updated.

SET THE CONFIGURATION PROTECTION MODE
=========================
Set the LogXptMode configurable database property appropriately.
Use the EDIT DATABASE (property) command on the standby database to set the redo transport service that corresponds to the protection mode you plan to set.
If the protection mode to be set is MAXAVAILABILITY, it is required that the redo transport service of at least one standby database is set to SYNC.
For example:
DGMGRL> EDIT DATABASE ‘UNIQUEDB2’ SET PROPERTY ‘LogXptMode’=’SYNC’;
Property “LogXptMode” updated

 

Change the overall protection mode for the configuration.
Use the EDIT CONFIGURATION command to upgrade the broker configuration to the MAXAVAILABILITY protection mode:
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
Succeeded

 

 

Verify the protection mode was changed.
Use the SHOW CONFIGURATION command to display the current protection mode for the configuration:
DGMGRL> SHOW CONFIGURATION;
Configuration
Name:                        DRSolution
Enabled:                     YES
Protection Mode:             MaxAvailability
Databases:
UNIQUEDB1 – Primary database
UNIQUEDB2 – Physical standby database
Fast-Start Failover:         DISABLED
Current status for “DB1DB2”:
SUCCESS

ENABLING FAST START FAILOVER AND STARTING THE OBSERVER
=====================================

DGMGRL> EDIT DATABASE ‘UNIQUEDB1’ SET PROPERTY ‘LogXptMode’=’SYNC’;
DGMGRL> EDIT DATABASE ‘UNIQUEDB2’ SET PROPERTY ‘LogXptMode’=’SYNC’;
DGMGRL> EDIT DATABASE ‘UNIQUEDB1′ SET PROPERTY FastStartFailoverTarget=’UNIQUEDB2’;

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;

On primary and standby ensure the db is in archivelog mode and flashback is enabled.
ALTER SYSTEM SET UNDO_RETENTION=3600 SCOPE=SPFILE;
ALTER SYSTEM SET UNDO_MANAGEMENT=’AUTO’ SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
SHOW PARAMETER UNDO;
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320 SCOPE=BOTH;
ALTER DATABASE ARCHIVELOG;
ALTER SYSTEM SET db_recovery_file_dest_size=;
ALTER SYSTEM SET db_recovery_file_dest=;
ALTER DATABASE FLASHBACK ON;
ALTER DATABASE OPEN;

CONFIGURABLE PROPERTIES

 

DGMGRL> CONNECT sys@UNIQUEDB1.dev.local
DGMGRL> START OBSERVER;
DGMGRL> ENABLE FAST_START FAILOVER;
DGMGRL> SHOW FAST_START FAILOVER;
Fast-Start Failover: ENABLED
Threshold:           30 seconds
Target:              UNIQUEDB2
Observer:            observer.dev.local
Lag Limit:           30 seconds (not in use)
Shutdown Primary:    TRUE
Auto-reinstate:      TRUE
Configurable Failover Conditions
Health Conditions:
Corrupted Controlfile YES
Corrupted Dictionary YES
Inaccessible Logfile NO
Stuck Archiver NO
Datafile Offline YES
Oracle Error Conditions:
(none)

OTHER DGMGRL COMMANDS
================

DGMGRL> EDIT DATABASE ‘UNIQUEDB1’ SET PROPERTY ‘LogArchiveTrace’=’127’;
DGMGRL> EDIT DATABASE ‘UNIQUEDB2′ SET STATE=’APPLY-OFF’;
DGMGRL> EDIT DATABASE  UNIQUEDB1 SET STATE=TRANSPORT-OFF;
DGMGRL> EDIT DATABASE  UNIQUEDB1 SET STATE=TRANSPORT-ON;

 

DGMGRL> SHOW DATABASE ‘UNIQUEDB1’ ‘LogXptStatus’;                 # PRIMARY DB
DGMGRL> SHOW DATABASE ‘UNIQUEDB1’ ‘InconsistentProperties’;
DGMGRL> SHOW DATABASE ‘UNIQUEDB1’ ‘InconsistentLogXptProps’; # PRIMARY DB

DGMGRL> SHOW DATABASE ‘UNIQUEDB2’ ‘LsbyFailedTxInfo’;            # STANDBY DB
DGMGRL> SHOW DATABASE ‘UNIQUEDB2’ ‘LsbyParameters’;             # STANDBY DB
DGMGRL> SHOW DATABASE ‘UNIQUEDB2’ ‘LsbySkipTable’;               # STANDBY DB
DGMGRL> SHOW DATABASE ‘UNIQUEDB2’ ‘LsbySkipTxnTable’;          # STANDBY DB
DGMGRL> SHOW DATABASE ‘UNIQUEDB2’ ‘RecvQEntries’;                 # STANDBY DB
DGMGRL> SHOW DATABASE ‘UNIQUEDB1’ ‘SendQEntries’;                # PRIMARY DB
DGMGRL> SHOW DATABASE ‘UNIQUEDB1’ ‘StatusReport’;
DGMGRL> SHOW DATABASE ‘UNIQUEDB1’ ‘TopWaitEvents’;

DGMGRL> DISABLE CONFIGURATION;
DGMGRL> DISABLE DATABASE ‘UNIQUEDB2’;

DGMGRL> REMOVE DATABASE ‘UNIQUEDB2’;
DGMGRL> REMOVE CONFIGURATION;

DGMGRL> switchover to ‘UNIQUEDB2’;

DGMGRL> SHOW DATABASE ‘UNIQUEDB2’ StatusReport;

 

DGMGRL> REINSTATE DATABASE ‘UNIQUEDB1’;  # reinstate the database after a failover

CONVERTING A PHYSICAL STANDBY DATABASE TO A SNAPSHOT DATABASE
============================================

DGMGRL> CONVERT DATABASE ‘UNIQUEDB2’ to SNAPSHOT STANDBY;
DGMGRL> CONVERT DATABASE ‘UNIQUEDB2’ to PHYSICAL STANDBY;

==================

Configurable Property Name

Scope

Pertains To

AlternateLocation

Instance

Redo transport services

ApplyInstanceTimeout

Database

Redo Apply and SQL Apply

ApplyParallel

Database

Redo Apply

ArchiveLagTarget

Database

Redo transport services

Binding

Database

Redo transport services

BystandersFollowRoleChange

Configuration

Fast-start failover

CommunicationTimeout

Configuration

Redo transport services

DbFileNameConvert

Database

Redo transport services

DelayMins

Database

Redo Apply and SQL Apply

DGConnectIdentifier

Database

Broker communication, Redo transport services

FastStartFailoverAutoReinstate

Configuration

Fast-start failover

FastStartFailoverLagLimit

Configuration

Fast-start failover

FastStartFailoverPmyShutdown

Configuration

Fast-start failover

FastStartFailoverTarget

Database

Fast-start failover

FastStartFailoverThreshold

Configuration

Fast-start failover

HostName

Instance

Instance identification

LogArchiveFormat

Instance

Redo transport services

LogArchiveMaxProcesses

Database

Redo transport services

LogArchiveMinSucceedDest

Database

Redo transport services

LogArchiveTrace

Instance

Diagnosis

LogFileNameConvert

Database

Redo transport services

LogShipping

Database

Redo transport services

LogXptMode

Database

Redo transport services

LsbyASkipCfgPr

Database

SQL Apply

LsbyASkipErrorCfgPr

Database

SQL Apply

LsbyASkipTxnCfgPr

Database

SQL Apply

LsbyDSkipCfgPr

Database

SQL Apply

LsbyDSkipErrorCfgPr

Database

SQL Apply

LsbyDSkipTxnCfgPr

Database

SQL Apply

LsbyMaxEventsRecorded

Database

SQL Apply

LsbyMaxSga

Instance

SQL Apply

LsbyMaxServers

Instance

SQL Apply

LsbyPreserveCommitOrder

Database

SQL Apply

LsbyRecordAppliedDdl

Database

SQL Apply

LsbyRecordSkipDdl

Database

SQL Apply

LsbyRecordSkipErrors

Database

SQL Apply

MaxConnections

Database

Redo transport services

MaxFailure

Database

Redo transport services

NetTimeout

Database

Redo transport services

ObserverConnectIdentifier

Database

Fast-start failover

PreferredApplyInstance

Database

Redo Apply and SQL Apply

RedoCompression

Database

Redo transport services

ReopenSecs

Database

Redo transport services

SidName Instance

Instance

identification

StandbyArchiveLocation

Instance

Redo transport services

StandbyFileManagement

Database

Redo Apply and SQL Apply