Dataguard Adventures...!!!!! - Part I

So, I started working on Phase-I of DR implementation....!!!!

Creation of Physical Standby for the running production database.

Assumptions

1) Oracle binaries with proper patchset are already installed on server.

2) SCP/FTP is working perfectly between the primary and standby server.

3) We have about 30GB local disk space available on both primary and standby server to hold disk based RMAN backup of database.

4) The file system layout on primary and standby server is exactly same.

5) The primary and standby server are pingable from each other using IP and hostname i.e. /etc/hosts on both hosts contain entries of primary as well as standby server.

Downtime

1) Downtime available for production is just 15 mins.

Below are the brief configuration details.

Parameter	Primary	Standby
Hostname/IP	dguard1 (192.168.10.80)	dguard2 (192.168.10.83)
Database Name	wukprd	wukprd
INSTANCE_NAME	wukprd	wukprd
DB_UNIQUE_NAME	P_WUKPRD	S_WUKPRD
SERVICE_NAME	P_WUKPRD	S_WUKPRD
Archivelog location	/u01/arch	/u01/s_arch
ORACLE_HOME	/u01/app/oracle/product/10.2.0	/u01/app/oracle/product/10.2.0

I’ll be using the following parameter for Log Transport Services

Protection Mode used - Maximum Performance

This is the mode in which the ARCH/LGWR process transfers the archivelogs from primary to standby and there is no disruption of operation on primary during network disconnects. This is the mode that provides the highest level of data protection possible without affecting the performance of database. The transaction on primary is comitted as soon as the redo required to recover is written to local online redo log.

Log Shipping - LGWR ASYNC NOAFFIRM

For Maximum Performance Mode configuration, we can use ARCH or LGWR process to transfer the redo from primary to standby. I am going to use LGWR for my configuration with standby redo logs in ASYNC mode. Although standby redo logs are not required for this mode, but still it is recommended to have them. Moreover in phase-II, I’ll be putting this setup in REAL TIME APPLY mode. The LNS process on primary is used to transfer the redo from primary to standby redo logs on standby via RFS. (on standby) There would be ASYNCHRONOUS mode of data transfer with NOAFFIRM.

ARCHIVE_LAG_TARGET - 1200 (20 Minutes)

When there is no activity on database, the log switch doesn’t occur, so there may be a possibility that log switch doesn’t occur for hours due to which changes (although negligible) won’t get applied to standby. To ensure that log switch occurs after a fixed duration (irrespective of activity on database), we add a parameter called archive_lag_target. The value for the parameter is specified in seconds.

Another thing I want to ,mention here is that we can change the SID of standby database on standby server but I am not going to do it coz I don't want it.

Steps for creation of physical standby

1) Put the primary database into force logging, so that all the changes on primary moves to standby database.

SQL> SELECT force_logging FROM v$database;

FOR

---

NO

SQL> alter database force logging;

Database altered.

SQL> SELECT force_logging FROM v$database;

FOR

---

YES

2) Create a backup directory for storing the backup of primary database. I do have tape library attached to the servers but I would prefer to take a disk based backup on a local disk as my database is only 30 Gigs in size. And I am going to use RMAN for creating a standby database.

Note: Make sure that same directory (/dump/bkp) exists on standby server too for storing the backup and restoration..

$ mkdir /dump/bkp

$ chown oracle:dba /dump/bkp

3) Take RMAN backup of primary database for standby creation. I don’t have catalog right now, so taking backup in nocatalog mode.

The initial part of script takes LEVEL 0 backup of database and creates a controlfile for standby database (to be used for mounting the standby database). The next part of script is backing up the archivelogs.

[oracle@dguard1 ~]$ echo $ORACLE_SID

wukprd

[oracle@dguard1 ~]$ rman

Recovery Manager: Release 10.2.0.1.0 - Production on Sat Sep 5 10:41:29 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

RMAN> connect target /

connected to target database: WUKPRD (DBID=451079523)

RMAN> run

2> {

3> change archivelog all crosscheck;

4> allocate channel ch1 type disk;

5> backup incremental level 0 database format '/dump/bKP/bk_inc0_%s_%p' setsize=8250000

6> include current controlfile for standby ;

7> sql "alter system archive log current";

8> backup archivelog all format '/dump/bKP/al_%s_%p';

9> release channel ch1;

10> }

So, this is what I have in my local backup directory.

[[oracle@dguard1 bkp]$ ls -rlt /dump/bkp

total 1803188

-rw-r----- 1 oracle dba 519004160 Sep 3 16:12 bk_inc0_4_1

-rw-r----- 1 oracle dba 7110656 Sep 3 16:12 bk_inc0_5_1

-rw-r----- 1 oracle dba 33662464 Sep 3 16:12 al_6_1

[oracle@dguard1 bkp]$

4) Create PFILE for primary database. This is required to add the dataguard parameters manually.

SQL> show parameter spfile;

NAME TYPE VALUE

---------------------------------- ------------------------------

spfile string /u01/app/oracle/product/10.2.0/dbs/spfilewukprd.ora

SQL> create pfile from spfile;

The pfile created would be $ORACLE_HOME/dbs/initwukprd.ora

5) Create a copy of primary pfile (initwukprd.ora) under backup directory (to transfer it to standby server later )

[oracle@dguard1 bkp] cp -p $ORACLE_HOME/dbs/initwukprd.ora /dump/bkp

6) Add/Modify the following dataguard parameters in pfile (initwukprd.ora) on primary (dguard1)

# PRIMARY DATABASE PRIMARY ROLE PRIMARY ROLE INIT PARAMETERS

*.db_unique_name='P_WUKPRD'

*.LOG_ARCHIVE_CONFIG = 'DG_CONFIG=(P_WUKPRD,S_WUKPRD)'

*.log_archive_dest_1='LOCATION=/u01/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=P_WUKPRD'

*.log_archive_dest_2='SERVICE=S_WUKPRD LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=S_WUKPRD'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

*.archive_lag_target=1200

# PRIMARY DATABASE STANDBY ROLE INIT PARAMETERS

*.fal_client='P_WUKPRD'

*.fal_server='S_WUKPRD'

*.standby_archive_dest='/u01/arch'

*.standby_file_management='AUTO'

7) SCP/FTP the backup of primary database to standby server.

Usually scp/ftp of files involve compressing/zipping and tarring them before transferring over a low bandwidth network.

This a single command which zips, tars, transfer the files to destination machine and unzip, untar the files there. It can be run from the standby server with present working directory as the directory where you want to transfer the files. A single command to do lot of tasks…!!!!

[oracle@dguard2 bkp]cd /dump/bkp

[oracle@dguard2 bkp]ssh oracle@dguard1 "(cd /dump/bkp ; tar -cf - *|gzip )"|gunzip|tar -xf –

[oracle@dguard2 bkp]ls -rlt

-rw-r----- 1 oracle dba 519004160 Sep 3 18:12 bk_inc0_4_1

-rw-r----- 1 oracle dba 7110656 Sep 3 18:12 bk_inc0_5_1

-rw-r----- 1 oracle dba 33662464 Sep 3 18:12 al_6_1

-rw-r----- 1 oracle dba 33662464 Sep 3 18:12 initwukprd.ora

8) Copy the initwukprd.ora (received from primary) to $ORACLE_HOME/dbs (on standby)

[oracle@dguard2 bkp]cp –p /dump/bkp/initwukprd.ora $ORACLE_HOME/dbs

[oracle@dguard2 bkp]ls -rlt $ORACLE_HOME/dbs/initwukprd.ora

-rw-r----- 1 oracle dba 33662464 Sep 3 18:12 initwukprd.ora

[oracle@dguard2 bkp]

9) Modify the initwukprd.ora (on standby) to accommodate/change the following parameters

# STANDBY DATABASE STANDBY ROLE INIT PARAMETERS

*.db_unique_name='S_WUKPRD'

*.LOG_ARCHIVE_CONFIG = 'DG_CONFIG=(P_WUKPRD,S_WUKPRD)'

*.log_archive_dest_1='LOCATION=/u01/s_archVALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=S_WUKPRD'

*.log_archive_dest_2='SERVICE=P_WUKPRD LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=P_WUKPRD'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

*.archive_lag_target=1200

# STANDBY DATABASE PRIMARY ROLE INIT PARAMETERS

*.fal_client='S_WUKPRD'

*.fal_server='P_WUKPRD'

*.standby_archive_dest='/u01/s_arch'

*.standby_file_management='AUTO'

10) Changing network files (listener.ora and tnsnames.ora) on primary server.

Add standby service entry (S_WUKPRD) in tnsnames.ora on primary server. So, my listener.ora looks like this.

#listener.ora Network Configuration File: /u01/app/oracle/product/10.2.0/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = /u01/app/oracle/product/10.2.0)

(PROGRAM = extproc)

)

(SID_DESC =

(GLOBAL_DBNAME = wukprd)

(ORACLE_HOME = /u01/app/oracle/product/10.2.0)

(SID_NAME = wukprd)

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = dguard1)(PORT = 1521))

)

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

)

And this is what I have in my tnsnames.ora…

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

EXTPROC_CONNECTION_DATA =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

(CONNECT_DATA =

(SID = PLSExtProc)

(PRESENTATION = RO)

)

)

P_WUKPRD =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = dguard1)(PORT = 1521)))

(CONNECT_DATA =

(SERVER = DEDICATED) (SERVICE_NAME = wukprd)))

S_WUKPRD =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = dguard2)(PORT = 1521)))

(CONNECT_DATA =

(SERVER = DEDICATED) (SERVICE_NAME = wukprd))

)

11) Reload the listener on primary.

[oracle@dguard1 bkp]$lsnrctl reload

[oracle@dguard1 admin]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 07-SEP-2009 18:45:05

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dguard1)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production

Start Date 07-SEP-2009 18:40:32

Uptime 0 days 0 hr. 4 min. 33 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/oracle/product/10.2.0/network/admin/listener.ora

Listener Log File /u01/app/oracle/product/10.2.0/network/log/listener.log

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dguard1)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))

Services Summary...

Service "PLSExtProc" has 1 instance(s).

Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

Service "P_WUKPRD" has 1 instance(s).

Instance "wukprd", status READY, has 1 handler(s) for this service...

Service "P_WUKPRD_XPT" has 1 instance(s).

Instance "wukprd", status READY, has 1 handler(s) for this service...

Service "wukprd" has 1 instance(s).

Instance "wukprd", status UNKNOWN, has 1 handler(s) for this service...

Service "wukprdXDB" has 1 instance(s).

Instance "wukprd", status READY, has 1 handler(s) for this service...

The command completed successfully

[oracle@dguard1 admin]$

DOWNTIME OF PRODUCTION STARTS HERE……!!!!

12) Shutdown the primary database and startup using the modified pfile. Check if all the changed parameters got reflected using show parameter command. If everything looks good, create spfile from pfile and startup the database.

DOWNTIME OF PRODUCTION ENDS HERE……!!!!

13) Changing network files (listener.ora and tnsnames.ora) on standby server.

Add the primary and standby service name entries in network file on standby server.

listener.ora on standby server

# listener.ora Network Configuration File: /u01/app/oracle/product/10.2.0/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = /u01/app/oracle/product/10.2.0)

(PROGRAM = extproc)

)

(SID_DESC =

(ORACLE_HOME = /u01/app/oracle/product/10.2.0)

(SID_NAME = wukprd)

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = dguard2)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

)

tnsnames.ora on standby server

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

EXTPROC_CONNECTION_DATA =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

(CONNECT_DATA =

(SID = PLSExtProc)

(PRESENTATION = RO)

)

)

P_WUKPRD =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = dguard1)(PORT = 1521)))

(CONNECT_DATA =

(SERVER = DEDICATED) (SERVICE_NAME = wukprd)))

S_WUKPRD =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = dguard2)(PORT = 1521)))

(CONNECT_DATA =

(SERVER = DEDICATED) (SERVICE_NAME = wukprd))

)

14) Check listener status on standby.

[oracle@dguard2 admin]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 07-SEP-2009 18:49:11

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dguard2)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production

Start Date 07-SEP-2009 18:44:51

Uptime 0 days 0 hr. 4 min. 19 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/oracle/product/10.2.0/network/admin/listener.ora

Listener Log File /u01/app/oracle/product/10.2.0/network/log/listener.log

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dguard2)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))

Services Summary...

Service "PLSExtProc" has 1 instance(s).

Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

Service "S_WUKPRD" has 1 instance(s).

Instance "wukprd", status READY, has 1 handler(s) for this service...

Service "S_WUKPRD_XPT" has 1 instance(s).

Instance "wukprd", status READY, has 1 handler(s) for this service...

Service "wukprd" has 1 instance(s).

Instance "wukprd", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

15) Create password file for standby instanace.

[oracle@dguard2 ~]$ orpwd file=$ORACLE_HOME/dbs/orapwwukprd password=sys123 entries=10

Note:- The password for sys entered above for standby instance should be the same as sys password for primary instance.

16) Startup the standby instance in nomount state.

[oracle@dguard2 ~]$ echo $ORACLE_SID

wukprd

[oracle@dguard2 ~]$ sqlplus /nolog

SQL> connect / as sysdba

SQL> startup nomount;

17) Restore/Recover the primary backup to create standby database.

Execute the following commands from primary database server (dguard1) to restore and recover the primary backup for standby creation.

Target – Primary instance (wukprd)

Auxiliary – Standby instance (wukprd)

[oracle@dguard1 ~]$ echo $ORACLE_SID

wukprd

[oracle@dguard1 ~]$ rman

Recovery Manager: Release 10.2.0.1.0 - Production on Sat Sep 5 10:41:29 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

RMAN> connect target /

connected to target database: WUKPRD (DBID=451079523)

RMAN> connect auxiliary sys/*****@S_WUKPRD

RMAN> run {

2> allocate auxiliary channel ch1 type disk;

3> duplicate target database for standby dorecover nofilenamecheck;

4> release channel ch1;

5> }

The above script will create the standby database on standby server using backup present on standby server (under backup directory /dump/bkp).

NOFILENAMECHECK clause means the path of datafiles is same on primary and standby

database and hence no check is required.

If the datafiles are required to be restored on a different location than primary database, then use the SET NEWNAME clause to rename the datafiles.

The above scripts will create the standby database and put it in mount state. It will not put the standby database in managed recovery mode. We need to initiate the managed recovery of standby database manually.

18) Add standby redolog groups to both the primary and standby database using the following statements.

SQL>!hostname

dguard1

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 (‘/u01/oradata/wukprd/stby_redo04.log’)

size 50M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 (‘/u01/oradata/wukprd/stby_redo05.log’)

size 50M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 (‘/u01/oradata/wukprd/stby_redo06.log’)

size 50M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 (‘/u01/oradata/wukprd/stby_redo07.log’)

size 50M;

SQL>!hostname

dguard2

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 (‘/u01/oradata/wukprd/stby_redo04.log’)

size 50M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 (‘/u01/oradata/wukprd/stby_redo05.log’)

size 50M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 (‘/u01/oradata/wukprd/stby_redo06.log’)

size 50M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 (‘/u01/oradata/wukprd/stby_redo07.log’)

size 50M;

The number of standby redolog groups required in a dataguard setup is n+1, n being the number of online redolog groups on primary. Since my primary has 3 online redolog groups, so I’ll add (3+1=4) online redologs to my standby.

Although I don’t require standby redologs for MAXIMUM PERFORMANCE operation but would later put the dataguard setup in REAL TIME APPLY, so adding them right away. The reason I am adding them on primary too is that they would be required on primary after switchover. (role reversal)

19) Put the physical standby database in managed recovery mode.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

SQL> SELECT PROCESS, STATUS,SEQUENCE#,BLOCK#,BLOCKS, DELAY_MINS FROM V$MANAGED_STANDBY;

PROCESS STATUS SEQUENCE# BLOCK# BLOCKS DELAY_MINS

--------- ------------ ---------- ---------- ---------- ----------

ARCH CLOSING 32818 1 256 0

ARCH CLOSING 32806 6145 1080 0

RFS IDLE 32819 1872 1 0

MRP0 APPLYING_LOG 32819 1869 524288 0

RFS IDLE 0 0 0 0

The above statement will put the standby database in managed recovery mode and archivelogs fetched by RFS process on standby would be applied to standby database by MRP process.

The standby database can be put into READ ONLY mode or RECOVERY mode depending upon the requirements. The database can be put into READ ONLY mode only after cancelling the managed recovery.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

The above statement will cancel the managed recovery on standby database. Although the RFS process would keep on running and fetching the archivelogs from primary but MRP will not to be there to apply those archivelogs to standby database until the database is put back into managed recovery mode.

SQL> ALTER DATABASE OPEN READ ONLY;

The above statement can be used to put the database into READ ONLY mode (for reporting) when required. The standby database can further be put into MANAGED RECOVERY mode to start recovery by MRP.

Note: Please note that for the duration when standby is in READ ONLY mode, the archivelogs are not being applied on standby and hence standby is lagging from primary.

20) Test LOG SHIPPING and LOG APPLY

On primary, run the following queries to find out the latest archivelog sequence archived by primary.

SQL>!hostname

dguard1

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/arch

Oldest online log sequence 2125

Next log sequence to archive 2127

Current log sequence 2127

SQL> select max(SEQUENCE#) from v$archived_log AL, V$DATABASE DB where AL.RESETLOGS_CHANGE#=DB.RESETLOGS_CHANGE# AND AL.RESETLOGS_TIME = DB.RESETLOGS_TIME and AL.ARCHIVED='YES' and AL.APPLIED='YES' AND DEST_ID=2;

MAX(SEQUENCE#)

--------------

2126

This is the latest sequence number generated by primary and has been applied on standby. Let’s confirm this on standby.

SQL>!hostname

dguard2

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/s_arch

Oldest online log sequence 2125

Next log sequence to archive 0

Current log sequence 2127

SQL> select max(SEQUENCE#) from v$archived_log AL, V$DATABASE DB where AL.RESETLOGS_CHANGE#=DB.RESETLOGS_CHANGE# AND AL.RESETLOGS_TIME = DB.RESETLOGS_TIME and AL.ARCHIVED='YES' and AL.APPLIED='YES';

MAX(SEQUENCE#)

--------------

2126

Yup…!!!! Its working ….:-)

Time to try a log switch now…!!!!!

Let’s try a log switch on primary and see if the changes are being applied to standby.

SQL> !hostname

dguard1

SQL> alter system switch logfile;

System altered.

Now, check on standby whether the sequence got applied on standby.

SQL> !hostname

dguard2

SQL> select max(SEQUENCE#) from v$archived_log AL, V$DATABASE DB where AL.RESETLOGS_CHANGE#=DB.RESETLOGS_CHANGE# AND AL.RESETLOGS_TIME = DB.RESETLOGS_TIME and AL.ARCHIVED='YES' and AL.APPLIED='YES';

MAX(SEQUENCE#)

--------------

2127

Let’s do it again …!!!!

SQL> !hostname

dguard1

SQL> alter system switch logfile;

System altered.

SQL> !hostname

dguard2

SQL> select max(SEQUENCE#) from v$archived_log AL, V$DATABASE DB where AL.RESETLOGS_CHANGE#=DB.RESETLOGS_CHANGE# AND AL.RESETLOGS_TIME = DB.RESETLOGS_TIME and AL.ARCHIVED='YES' and AL.APPLIED='YES';

MAX(SEQUENCE#)

--------------

2128

So its working perfect now…!!!!!

-----------------------------------------------------------------------------

Some more queries to check the things….!!!!!

Check DB Roles

Primary

SQL> select NAME,DB_UNIQUE_NAME,OPEN_MODE,DATABASE_ROLE, PROTECTION_MODE,

from v$database;

NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE

--------- ------------------------------ ---------- ---------------- --------WUKPRD P_WUKPRD READ WRITE PRIMARY MAXIMUM PERFORMANCE

It says P_WUKPRD is primary and is in Read/Write mode. The protection mode is MAXIMUM PERFORMANCE.

Standby

SQL> select NAME,DB_UNIQUE_NAME,OPEN_MODE,DATABASE_ROLE, PROTECTION_MODE,

from v$database;

NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE

--------- ------------------------------ ---------- ---------------- --------WUKPRD S_WUKPRD MOUNTED PHYSICAL STANDBY MAXIMUM PERFORMANCE

It says S_WUKPRD is physical standby and is in MOUNTED state. The protection mode is MAXIMUM PERFORMANCE.

Check if PRIMARY and STANDBY are in sync

Primary

SQL> select max(SEQUENCE#) from v$archived_log AL, V$DATABASE DB where AL.RESETLOGS_CHANGE#=DB.RESETLOGS_CHANGE# AND AL.RESETLOGS_TIME = DB.RESETLOGS_TIME and AL.ARCHIVED='YES' and AL.APPLIED='YES' AND DEST_ID=2;

MAX(SEQUENCE#)

--------------

32818

Standby

SQL> select max(SEQUENCE#) from v$archived_log AL, V$DATABASE DB where AL.RESETLOGS_CHANGE#=DB.RESETLOGS_CHANGE# AND AL.RESETLOGS_TIME = DB.RESETLOGS_TIME and AL.ARCHIVED='YES' and AL.APPLIED='YES';

MAX(SEQUENCE#)

--------------

32818

The above values should match. If they don't match, standby is lagging behind primary.

Determining Archive gap

In case of network disconnect between primary and standby, standby database starts lagging behind primary until the network issue is resolved. This will create an archivelog gap between the latest archivelog generated on primary and the last archivelog applied on standby. This gap can be determined using the following statement.

SQL> select * from v$archive_gap;

no rows selected

Check status of Log transport and log apply processes on Standby

SQL> SELECT PROCESS, STATUS,SEQUENCE#,BLOCK#,BLOCKS, DELAY_MINS FROM V$MANAGED_STANDBY;

PROCESS STATUS SEQUENCE# BLOCK# BLOCKS DELAY_MINS

--------- ------------ ---------- ---------- ---------- ----------

ARCH CLOSING 32818 1 256 0

ARCH CLOSING 32806 6145 1080 0

RFS IDLE 32819 1872 1 0

MRP0 APPLYING_LOG 32819 1869 524288 0

RFS IDLE 0 0 0 0

The above output shows the RFS process is used to fetch archivelogs from primary and MRP process is used to do managed recovery on standby.If MRP is not present in the above output, it means managed recovery is not active and we need to put the standby in managed recovery.

So, we are done with the setup of physical standby...Will discuss configuration DG Broker for this setup in my next post....!!!!