isitdevops.com/databases

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