Steps to configure Manual Data Guard – Oracle 11g Manual Data Guard Part-I

This article is about step by step approach to configure Manual Physical Standby Data Guard in Oracle 11g. In my case, Ingredients to simulate Manual Physical Standby Manual data guard environment are as below:

2 VM’s, PR and DR with enough CPU and RAM in order to run oracle database.

Primary server configuration:

CentOS 6.5
Server name: PR
IP: 192.168.239.130
Oracle 11g software plus oracle instance.
Oracle SID/Global_name: RTS
Oracle db_unique_name: RTS

Standby server configuration:

CentOS 6.5
Server name: DR
IP: 192.168.239.131
Oracle 11g software only.
Oracle SID/Global name: RTS
Oracle db_unique_name: RTSDR

Note:

Oracle version on Primary and Standby should be identical. i.e. In my case it’s 11.2.0.1.0
db_unique_name on standby and primary database should be different.
Primary and Standby server should ping each other by IP as well as its server name. In order to ping with its server name, edit /etc/hosts file accordingly.

PR /etc/hosts file:

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4

::1 localhost localhost.localdomain localhost6 localhost6.localdomain6

192.168.239.130 PR.localdomain PR

192.168.239.131 DR

DR /etc/hosts file:

[root@dr ~]# cat /etc/hosts

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4

::1 localhost localhost.localdomain localhost6 localhost6.localdomain6

192.168.239.131 DR.localdomain DR

192.168.239.130 PR

Lets start the configuration:

Step -I

Verify your database is in Archive Log Mode or NO archive log mode, if not than my one of the post will help you to configure your Oracle 11g database is in archive log mode. Primary database need to be in Archive log mode.

Step -II

Enable Force Logging on Primary database(Hostname:PR)

Following query will help you find out your database is in force logging or not:

SQL> column force_logging format a15

SQL> select force_logging from v$database;

FORCE_LOGGING

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

Following query will help you to enable force logging:

SQL> alter database force logging;

Database altered.

SQL> select force_logging from v$database;

FORCE_LOGGING

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

YES

Step – III

Backup primary database with the help of RMAN:

[oracle@PR ~]$ rman target /

connected to target database: RTS (DBID=1470135364)

RMAN> run {

allocate channel c1 device type disk format '/home/oracle/prdg/bkup/%U';

backup as compressed backupset full database plus archivelog;

release channel c1;

}

Copy RMAN backup to standby database(Hostname: DR)

[oracle@PR ~]$ cd /home/oracle/prdg/bkup

[oracle@PR bkup]$ ll

total 266332

-rw-r-----. 1 oracle oinstall 12165120 Apr 18 03:55 09r3ah13_1_1

-rw-r-----. 1 oracle oinstall 259457024 Apr 18 03:56 0ar3ah17_1_1

-rw-r-----. 1 oracle oinstall 1097728 Apr 18 03:56 0br3ah3s_1_1

-rw-r-----. 1 oracle oinstall 4096 Apr 18 03:56 0cr3ah3v_1_1

[oracle@PR bkup]$ scp * oracle@DR:/home/oracle/drdg/bkup/

Step – IV

Create Standby Controlfile on Primary database(Hostname:PR)

SQL> alter database create standby controlfile as '/home/oracle/prdg/stndby_control.ctl';

Database altered.

Copy Standby Controlfile to standby database(Hostname: DR)

[oracle@PR ~]$ scp /home/oracle/prdg/stndby_control.ctl oracle@DR:/u01/app/oracle/oradata/RTS/control01.ctl

[oracle@PR ~]$ scp /home/oracle/prdg/stndby_control.ctl oracle@DR:/u01/app/oracle/oradata/RTS/control02.ctl

Step – V

Create parameter file(i.e. initRTS.ora) on Primary database(Hostname:PR). If you are not using spfile than skip this step.

SQL> create pfile='/home/oracle/prdg/initRTS.ora' from spfile;

Copy parameter file(i.e. initRTS.ora) to standby database(Hostname: DR)

[oracle@PR ~]$ scp /home/oracle/prdg/initRTS.ora oracle@DR:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/

Step – VI

Copy password file to standby database(Hostname: DR). (Note: create password file if not created for your database.)

[oracle@PR ~]$ cd $ORACLE_HOME/dbs

[oracle@PR dbs]$ ll orapwRTS

-rw-r-----. 1 oracle oinstall 2048 Apr 18 03:23 orapwRTS

[oracle@PR dbs]$ scp orapwRTS oracle@DR:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/

Step – VII

Create Required Directory Structure on standby database server(Hostname: DR). In my case I am going to replicate same directory structure as mentioned in parameter file.

=== initRTS.ora ===

*.audit_file_dest='/u01/app/oracle/admin/RTS/adump'

*.control_files='/u01/app/oracle/oradata/RTS/control01.ctl','/u01/app/oracle/oradata/RTS/control02.ctl'

*.diagnostic_dest='/u01/app/oracle'

*.log_archive_dest_1='LOCATION=/home/oracle/archdir'

=== End ===

Directory structure on standby database server.(Hostname: DR)

[oracle@DR ~]$ mkdir -p /u01/app/oracle/admin/RTS/adump

[oracle@DR ~]$ mkdir -p /u01/app/oracle/oradata/RTS/

[oracle@DR ~]$ mkdir -p /u01/app/oracle

[oracle@DR ~]$ mkdir -p /home/oracle/archdir

Step – VIII

Configure tnsname.ora and listener.ora and Start listener on standby database(Hostname: DR)

=== tnsnames.ora ===

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = DR.localdomain)(PORT = 1521))

)

ADR_BASE_LISTENER = /u01/app/oracle

=== End ===

===listener.ora ===

RTS =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = RTS)

)

RTSDR =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = RTSDR)

)

=== End ===

Step – IX

Add following parameters to initRTS.ora file on Standby database. (Hostname: DR)

*.db_unique_name='RTSDR'

In case of your datafile and logfile location on standby is different from primary than add following parameter to initRTS.ora file.

*.db_file_name_convert=('location on primary','location on Stanndby')

*.log_file_name_convert=('location on primary','location on Stanndby')

Step – X

Connect Standby Database

$ export ORACLE_SID=RTS

sqlplus / as sysdba

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 960372736 bytes

Fixed Size 2219152 bytes

Variable Size 562037616 bytes

Database Buffers 390070272 bytes

Redo Buffers 6045696 bytes

SQL>

Step – XI

Create spfile from existing parameter file (i.e.initRTS.ora) and bounce the database:

SQL> create spfile from pfile;

SQL> Shut immediate;

SQL> startup mount;

Step – XI

Restore and recover database with the help of RMAN backed up files from primary database. (Step – III)

$ rman target /

On standby database, Practically there is no backup taken from RMAN, but if you issue following command to list out existing backups, than RMAN will show you full database backup information of primary database.(Step – III) Because RMAN repository is stored in control file and we have copy pasted standby control file from primary to standby database.

RMAN> list backup of database summary;

using target database control file instead of recovery catalog

List of Backups

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

Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag

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

10 B F A DISK 18-APR-16 1 1 YES TAG20160418T035519

Just cross check and delete invalid entry from control file with the help of following RMAN commands:

RMAN> crosscheck backup;

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=25 device type=DISK

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/home/oracle/prdg/bkup/01r3vadq_1_1 RECID=1 STAMP=910141882

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/home/oracle/prdg/bkup/02r3vadu_1_1 RECID=2 STAMP=910141886

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/home/oracle/prdg/bkup/03r3vaga_1_1 RECID=3 STAMP=910141963

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/home/oracle/prdg/bkup/04r3vagc_1_1 RECID=4 STAMP=910141964

Crosschecked 4 objects

RMAN> delete noprompt force expired backup;

Step – XII

Catalog all RMAN backup file entry to control file with following:

RMAN> catalog start with '/home/oracle/drdg/bkup/';

using target database control file instead of recovery catalog

searching for all files that match the pattern /home/oracle/drdg/bkup/

List of Files Unknown to the Database

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

File Name: /home/oracle/drdg/bkup/0ar3ah17_1_1

File Name: /home/oracle/drdg/bkup/09r3ah13_1_1

File Name: /home/oracle/drdg/bkup/0cr3ah3v_1_1

File Name: /home/oracle/drdg/bkup/0br3ah3s_1_1

Do you really want to catalog the above files (enter YES or NO)? yes

cataloging files...

cataloging done

List of Cataloged Files

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

File Name: /home/oracle/drdg/bkup/0ar3ah17_1_1

File Name: /home/oracle/drdg/bkup/09r3ah13_1_1

File Name: /home/oracle/drdg/bkup/0cr3ah3v_1_1

File Name: /home/oracle/drdg/bkup/0br3ah3s_1_1

Step – XIII

Restore and recover database with the help of following RMAN commands:

run

{

restore database;

recover database;

}

Above recovery session will be end up with RMAN error message like mentioned below. Recovery successful.

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 04/26/2016 02:18:57

RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 4 and starting SCN of 954414

Step – XIV

Shutdown database and Startup Standby Database(Mount mode):

sqlplus / as sysdba

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 960372736 bytes

Fixed Size 2219152 bytes

Variable Size 562037616 bytes

Database Buffers 390070272 bytes

Redo Buffers 6045696 bytes

SQL> alter database mount standby database;

Database altered.

Step – XV

Cheers!! Our Manual Data Guard configuration have configured successfully.

Verify Manual Data Guard configuration on Standby database with the help of following SQL query:

SQL> select name,open_mode,database_role from v$database;

NAME OPEN_MODE DATABASE_ROLE

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

RTS MOUNTED PHYSICAL STANDBY

Issue same above SQL query on primary database:

SQL> select name,open_mode,database_role from v$database;

NAME OPEN_MODE DATABASE_ROLE

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

RTS READ WRITE PRIMARY

Step – XVI

Verify your Manual Data Guard configuration is working properly or not with the help of generating and applying archive logs on standby database:

Step – A

Generate multiple archive logs on primary database, issue following SQL query multiple times:

SQL> alter system switch logfile;

System altered.

SQL> SELECT MAX(SEQUENCE#) FROM V$LOG_HISTORY;

MAX(SEQUENCE#)

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

Step – B

Transfer above generated archive logs to standby database server @ path: /home/oracle/archdir/ (As per parameter: *.log_archive_dest_1 set for standby database)

scp * oracle@DR:/home/oracle/archdir/

//Above SCP linux command will transfer all the archive logs from mentioned directory. It will overwrite old files. (Note: I used this shortcut because I am demonstrating you on my test environment, In case of production, please copy only newly generated files)

Step – C

Issue following SQL to query for maximum of sequence number of applied archived log on standby database.(Hostname: DR)

SQL> SELECT MAX(SEQUENCE#) FROM V$LOG_HISTORY;

MAX(SEQUENCE#)

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

Apply copy pasted archive logs on standby database as below,

SQL> recover standby database until cancel;

ORA-00279: change 1008623 generated at 04/18/2016 03:56:47 needed for thread 1

ORA-00289: suggestion : /home/oracle/archdir/1_18_908844551.dbf

ORA-00280: change 1008623 for thread 1 is in sequence #18

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

AUTO

ORA-00279: change 957138 generated at 04/26/2016 02:20:59 needed for thread 1

ORA-00289: suggestion : /home/oracle/archdir/1_5_910140016.dbf

ORA-00280: change 957138 for thread 1 is in sequence #5

ORA-00278: log file '/home/oracle/archdir/1_4_910140016.dbf' no longer needed for this recovery

ORA-00279: change 957141 generated at 04/26/2016 02:21:00 needed for thread 1

ORA-00289: suggestion : /home/oracle/archdir/1_6_910140016.dbf

ORA-00280: change 957141 for thread 1 is in sequence #6

ORA-00278: log file '/home/oracle/archdir/1_5_910140016.dbf' no longer needed for this recovery

ORA-00279: change 957144 generated at 04/26/2016 02:21:01 needed for thread 1

ORA-00289: suggestion : /home/oracle/archdir/1_7_910140016.dbf

ORA-00280: change 957144 for thread 1 is in sequence #7

ORA-00278: log file '/home/oracle/archdir/1_6_910140016.dbf' no longer needed for this recovery

ORA-00279: change 957147 generated at 04/26/2016 02:21:01 needed for thread 1

ORA-00289: suggestion : /home/oracle/archdir/1_8_910140016.dbf

ORA-00280: change 957147 for thread 1 is in sequence #8

ORA-00278: log file '/home/oracle/archdir/1_7_910140016.dbf' no longer needed for this recovery

ORA-00308: cannot open archived log '/home/oracle/archdir/1_8_910140016.dbf'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

All transfered archive logs have applied on standby database successfully, now check maximum of sequence number of applied archived log.

SQL> SELECT MAX(SEQUENCE#) FROM V$LOG_HISTORY;

MAX(SEQUENCE#)

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

Maximum of sequence number of applied archived log on primary and standby is same, our configuration successful. Cheers!!!

Your comment and suggestion are highly appreciated.

Switch over and switch back – Oracle 11g Manual Data Guard Part-II

In my Previous article we have covered How to configure oracle 11g Manual Data Guard, Now we will look into switch-over and switch-back activity.

Switch-over Pre-requisites:

One:

Check listener status on primary and standby database.

Primary Database Listener:

[oracle@PR ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 11-MAY-2016 22:49:31

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=PR.localdomain)(PORT=1521)))

STATUS of the LISTENER

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

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production

Start Date 11-MAY-2016 22:44:06

Uptime 0 days 0 hr. 5 min. 25 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

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

Listener Log File /u01/app/oracle/diag/tnslsnr/PR/listener/alert/log.xml

Listening Endpoints Summary...

 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=PR.localdomain)(PORT=1521)))

Services Summary...

Service "RTS" has 1 instance(s).

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

Service "RTSXDB" has 1 instance(s).

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

The command completed successfully

Standby Database Listener:

[oracle@DR ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 11-MAY-2016 22:51:55

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DR.localdomain)(PORT=1521)))

STATUS of the LISTENER

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

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production

Start Date 11-MAY-2016 22:45:18

Uptime 0 days 0 hr. 6 min. 37 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

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

Listener Log File /u01/app/oracle/diag/tnslsnr/DR/listener/alert/log.xml

Listening Endpoints Summary...

 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DR.localdomain)(PORT=1521)))

Services Summary...

Service "RTSDR" has 1 instance(s).

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

The command completed successfully

Two:

Check database mode on primary and standby database.

Primary Database Mode:

SQL> select name,open_mode from v$database;

NAME OPEN_MODE

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

RTS READ WRITE

Standby Database Mode:

SQL> select name,open_mode from v$database;

NAME OPEN_MODE

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

RTS MOUNTED

Three:

Check database role on primary and standby database.

Primary Database:

SQL> select name,database_role from v$database;

NAME DATABASE_ROLE

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

RTS PRIMARY

Standby Database:

SQL> select name,database_role from v$database;

NAME DATABASE_ROLE

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

RTS PHYSICAL STANDBY

Four:

No datafiles should be in recovery mode on Primary and Standby database.

SQL> SELECT FILE# FILESTAT FROM V$DATAFILE WHERE STATUS in ('RECOVER');

no rows selected

Five:

No datafiles should be in offline mode on Primary and Standby database.

SQL> SELECT FILE# FILESTAT FROM V$DATAFILE WHERE STATUS in ('OFFLINE');

no rows selected

Six:

No datafiles should be in backup mode on Primary and Standby database.

SQL> select file# from v$backup where status='ACTIVE';

no rows selected

Switch-over steps:

Step-A:

Shutdown primary database:

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

Step-B:

Copy control file to temporary location on primary database.

[oracle@PR ~]$ cp /u01/app/oracle/oradata/RTS/control01.ctl /home/oracle/prtemp/

[oracle@PR ~]$ cp /u01/app/oracle/oradata/RTS/control02.ctl /home/oracle/prtemp/

Step-C:

Copy SPFile to temporary location on primary database.

[oracle@PR ~]$ cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileRTS.ora /home/oracle/prtemp/

Step-D:

Copy Redo log files to temporary location on primary database.

[oracle@PR ~]$ cd /u01/app/oracle/oradata/RTS/

[oracle@PR RTS]$ ll *.log

-rw-r-----. 1 oracle oinstall 52429312 May 11 22:46 redo01.log

-rw-r-----. 1 oracle oinstall 52429312 May 11 23:08 redo02.log

-rw-r-----. 1 oracle oinstall 52429312 May 11 22:46 redo03.log

[oracle@PR RTS]$ cp *.log /home/oracle/prtemp/

Step-E:

Copy needed archive log to DR site.

[oracle@PR RTS]$ cd /home/oracle/archdir/

[oracle@PR archdir]$ scp * oracle@DR:/home/oracle/archdir/

Step-F:

Get primary database to READ ONLY mode.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 755769344 bytes

Fixed Size 2217184 bytes

Variable Size 473959200 bytes

Database Buffers 276824064 bytes

Redo Buffers 2768896 bytes

Database mounted.

SQL> alter database open read only;

Database altered.

Step-G:

Apply all needed archive logs to standby database.

SQL> recover standby database until cancel;

ORA-00279: change 957836 generated at 04/26/2016 02:49:33 needed for thread 1

ORA-00289: suggestion : /home/oracle/archdir/1_15_910140016.dbf

ORA-00280: change 957836 for thread 1 is in sequence #15

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

AUTO

ORA-00279: change 967269 generated at 04/26/2016 23:34:52 needed for thread 1

ORA-00289: suggestion : /home/oracle/archdir/1_16_910140016.dbf

ORA-00280: change 967269 for thread 1 is in sequence #16

ORA-00278: log file '/home/oracle/archdir/1_15_910140016.dbf' no longer needed for this recovery

ORA-00279: change 967272 generated at 04/26/2016 23:34:53 needed for thread 1

ORA-00289: suggestion : /home/oracle/archdir/1_17_910140016.dbf

ORA-00280: change 967272 for thread 1 is in sequence #17

ORA-00278: log file '/home/oracle/archdir/1_16_910140016.dbf' no longer needed for this recovery

..

...

ORA-00279: change 969653 generated at 05/09/2016 23:48:39 needed for thread 1

ORA-00289: suggestion : /home/oracle/archdir/1_29_910140016.dbf

ORA-00280: change 969653 for thread 1 is in sequence #29

ORA-00278: log file '/home/oracle/archdir/1_28_910140016.dbf' no longer needed for this recovery

ORA-00308: cannot open archived log '/home/oracle/archdir/1_29_910140016.dbf'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Step-H:

Shutdown standby database.

SQL> shut immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Step-I:

Copy control file to temporary location on standby database.

[oracle@DR ~]$ cp /u01/app/oracle/oradata/RTS/control01.ctl /home/oracle/drtemp/

[oracle@DR ~]$ cp /u01/app/oracle/oradata/RTS/control02.ctl /home/oracle/drtemp/

Step-J:

Copy SPFile to temporary location on standby database.

[oracle@DR dbs]$ cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileRTS.ora /home/oracle/drtemp/

Step-K:

On Standby database, copy all files(from /home/oracle/drtemp/ directory) to primary database server(to /home/oracle/from_dr directory).

[oracle@DR ~]$ cd /home/oracle/drtemp/

[oracle@DR drtemp]$ ll

total 19044

-rw-r-----. 1 oracle oinstall 9748480 May 11 23:24 control01.ctl

-rw-r-----. 1 oracle oinstall 9748480 May 11 23:24 control02.ctl

-rw-r-----. 1 oracle oinstall 2560 May 11 23:27 spfileRTS.ora

[oracle@DR drtemp]$ scp * oracle@PR:/home/oracle/from_dr

Step-L:

On Primary database, copy all files(from /home/oracle/prtemp/ directory) to standby database server(to /home/oracle/from_pr directory).

[oracle@PR ~]$ cd /home/oracle/prtemp/

[oracle@PR prtemp]$ ll

total 172656

-rw-r-----. 1 oracle oinstall 9748480 May 11 23:10 control01.ctl

-rw-r-----. 1 oracle oinstall 9748480 May 11 23:10 control02.ctl

-rw-r-----. 1 oracle oinstall 52429312 May 11 23:12 redo01.log

-rw-r-----. 1 oracle oinstall 52429312 May 11 23:12 redo02.log

-rw-r-----. 1 oracle oinstall 52429312 May 11 23:12 redo03.log

-rw-r-----. 1 oracle oinstall 2560 May 11 23:10 spfileRTS.ora

[oracle@PR prtemp]$ scp * oracle@DR:/home/oracle/from_pr

Step-M:

On Standby database, replace copied files(source directory: /home/oracle/prtemp/) to its appropriate locations:

[oracle@DR from_pr]$ cp control01.ctl /u01/app/oracle/oradata/RTS/control01.ctl

[oracle@DR from_pr]$ cp control02.ctl /u01/app/oracle/oradata/RTS/control02.ctl

[oracle@DR from_pr]$ cp spfileRTS.ora $ORACLE_HOME/dbs/spfileRTS.ora

[oracle@DR from_pr]$ cp *.log /u01/app/oracle/oradata/RTS/

Step-N:

Perform role switching on old standby database: (old standby database to new Primary database)

one:

Startup mount old standby database.

[oracle@DR ~]$ sqlplus / as sysdba

...

Connected to an idle instance.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 755769344 bytes

Fixed Size 2217184 bytes

Variable Size 473959200 bytes

Database Buffers 276824064 bytes

Redo Buffers 2768896 bytes

Database mounted.

Two:

Recover database.

SQL> recover database;

Media recovery complete.

Three:

Drop tempfiles(From temporary tablespace) from old standby database. (Note: If this is your first switch over then you may skip this step)

Four:

Open database:

SQL> alter database open;

Database altered.

Five:

Create new tempfile:

SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/RTS/temp02.dbf' size 100m autoextend on maxsize unlimited;

Tablespace altered.

On Primary database, replace copied files(source directory: /home/oracle/drtemp/) to its appropriate locations:

[oracle@PR from_dr]$ cp control01.ctl /u01/app/oracle/oradata/RTS/control01.ctl

[oracle@PR from_dr]$ cp control02.ctl /u01/app/oracle/oradata/RTS/control02.ctl

[oracle@PR from_dr]$ cp spfileRTS.ora $ORACLE_HOME/dbs/spfileRTS.ora

Shutdown old primary database.

SQL> shut immediate;

Step-O:

Perform role switching on old primary database: (old primary database to new standby database)

One:

Start database with nomount state:

SQL> startup nomount;

Two:

Alter database to mount state with standby database:

SQL> alter database mount standby database;

Database altered.

Cheers!! Switch over activity successfully carried out, now lets perform post checks on both the database servers:

Post checks:

Check database mode and role on new primary database, it shall be READ WRITE and Primary.

SQL> select name,open_mode,database_role from v$database;

NAME OPEN_MODE DATABASE_ROLE

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

RTS READ WRITE PRIMARY

Check database mode and role on new standby database, it shall be MOUNTED and PHYSICAL STANDBY.

SQL> select name,open_mode,database_role from v$database;

NAME OPEN_MODE DATABASE_ROLE

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

RTS MOUNTED PHYSICAL STANDBY

We are done with switch over activity.

Switch-back activity steps:

There is no difference in switch-over and switch-back steps. Use above steps to do it.
Cheers!!

Your suggestions and comments are highly appreciated, if any.

Stay tuned with my next article on How to open Standby Database in Read ONLY mode.

How to open Manual Physical Standby Database in READ ONLY mode – Oracle 11g Manual Data Guard Part-III

You can use Manual Physical Standby database for query access for query offloading and reporting purposes by making a standby database to read only mode, resulting we can use the primary database exclusively for core purpose.(i.e. OLTP)

Note:

Simply you can’t apply archive logs to standby database server when database is in READ ONLY mode, but you can continue to transfer your archive logs to standby database server, Once your standby database back to MOUNT mode than you can apply your remaining archive logs.

Previously, We covered Manual data guard switch over and switch back activity step by step approach.

Steps are as follows:

Step-I

Verify database name, its mode and role by following command:

SQL> select name,open_mode,database_role from v$database;

The database is in mount state and its role is Physical Standby, as expected, good to go.

Step-II

Transfer all remaining archive logs from the primary database server to standby database server and apply it (If there is an archive log gap), before opening a standby database to READ ONLY mode. For more information on it, you can refer Step – XVI from my one of post on “Configuring Manual Data Guard”.

Step-III

Delete existing tempfile[s]:

select tf.name from v$tablespace ts,v$tempfile tf where ts.TS#=tf.TS# and ts.name=’TEMP’;
alter database tempfile ‘Above_temp_file[s]’ drop;

Step-IV

Open your Standby database in READ ONLY mode from MOUNTED mode:

SQL> alter database open read only;

Step-V

Create new tempfile[s]:

SQL> alter tablespace TEMP add tempfile '/path/temp02.dbf' size 200m autoextend on maxsize unlimited;

Step-VI

Repeat Step-I to verify standby database mode and its role.

Your suggestions and comments are highly appreciated, if any.

Stay tuned with my next article on Incremental backup from SCN.

Incremental backup from SCN – Oracle 11g Manual Data Guard Part-IV

Previously we covered How to open Manual Physical Standby Database in READ ONLY mode.

Let us discuss one scenario and then you will realize the use of Incremental backup from SCN.

Scenario:

Consider the production database configured with Manual data guard, and unfortunately any archive log file gets corrupted or lost. Here we solely depend on that corrupted archive log to continue database replication, in this case we can fix the problem with RMAN incremental backup from SCN. Input SCN from standby database current state and backup incremental from that SCN from Primary database, and recover standby database. By this method, we no more need that corrupted archive log file.

OR

There is huge archive log sequence gap between primary and standby database, and we need to recover it in one go.

Before taking Incremental backup from SCN, we need to perform some pre-requisites on primary and standby database server, are as follows:

First:

Check listener status, it should be running and listening to databases.

[oracle@PR ~]$ lsnrctl status

Second:

Check database mode and role, On primary site, database should be in READ WRITE mode and role should be PRIMARY.

On standby site, database should be in MOUNTED mode and role should be PHYSICAL STANDBY.

Steps to take incremental backup from SCN:

Step-I

On primary database, take standby controlfile backup:

SQL> alter database create standby controlfile as '/home/oracle/backup/standby_control.ctl';

Database altered.

Step-II

Get current SCN from Standby database:

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

Step-III

On primary database, take RMAN incremental backup from SCN, take SCN as an input from Step-II:

[oracle@PR ~]$ rman target /

RMAN> backup device type disk incremental from scn 957835 database format '/home/oracle/backup/Inc_backup_%U';

Starting backup at 13-MAY-16

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=34 device type=DISK

backup will be obsolete on date 20-MAY-16

archived logs will not be kept or backed up

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/RTS/system01.dbf

input datafile file number=00002 name=/u01/app/oracle/oradata/RTS/sysaux01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/RTS/undotbs01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/RTS/users01.dbf

channel ORA_DISK_1: starting piece 1 at 13-MAY-16

channel ORA_DISK_1: finished piece 1 at 13-MAY-16

piece handle=/home/oracle/backup/Inc_backup_05r5f6en_1_1 tag=TAG20160513T045757 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:46

using channel ORA_DISK_1

backup will be obsolete on date 20-MAY-16

archived logs will not be kept or backed up

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

channel ORA_DISK_1: starting piece 1 at 13-MAY-16

channel ORA_DISK_1: finished piece 1 at 13-MAY-16

piece handle=/home/oracle/backup/Inc_backup_06r5f6g7_1_1 tag=TAG20160513T045757 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 13-MAY-16

Step-IV

Transfer backup to standby database server:

[oracle@DR backup]$ pwd

/home/oracle/backup

[oracle@DR backup]$ ll

total 59728

-rw-r--r--. 1 oracle oinstall 41615360 May 13 04:58 Inc_backup_05r5f6en_1_1

-rw-r--r--. 1 oracle oinstall 9797632 May 13 04:58 Inc_backup_06r5f6g7_1_1

-rw-r--r--. 1 oracle oinstall 9748480 May 13 04:36 standby_control.ctl

Step-V

On Standby database, Restore Incremental backup:

one:

Shutdown standby database and startup in nomount mode:

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 755769344 bytes

Fixed Size 2217184 bytes

Variable Size 448793376 bytes

Database Buffers 301989888 bytes

Redo Buffers 2768896 bytes

Two:

Restore standby controlfile:

[oracle@DR ~]$ rman target /

RMAN> restore controlfile from '/home/oracle/backup/standby_control.ctl';

Starting restore at 13-MAY-16

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: copied control file copy

output file name=/u01/app/oracle/oradata/RTS/control01.ctl

output file name=/u01/app/oracle/oradata/RTS/control02.ctl

Finished restore at 13-MAY-16

Three:

Alter database to MOUNT mode:

SQL> alter database mount;

Database altered.

Four:

Catalog RMAN incremental backup files to RMAN repository(In my case, i.e. controlfile):

[oracle@DR ~]$ rman target /

RMAN> catalog start with '/home/oracle/backup/';

using target database control file instead of recovery catalog

searching for all files that match the pattern /home/oracle/backup/

List of Files Unknown to the Database

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

File Name: /home/oracle/backup/Inc_backup_06r5f6g7_1_1

File Name: /home/oracle/backup/standby_control.ctl

File Name: /home/oracle/backup/Inc_backup_05r5f6en_1_1

Do you really want to catalog the above files (enter YES or NO)? yes

cataloging files...

cataloging done

List of Cataloged Files

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

File Name: /home/oracle/backup/Inc_backup_06r5f6g7_1_1

File Name: /home/oracle/backup/standby_control.ctl

File Name: /home/oracle/backup/Inc_backup_05r5f6en_1_1

Five:

Recover standby database:

RMAN> recover database;

Starting recover at 13-MAY-16

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=24 device type=DISK

channel ORA_DISK_1: starting incremental datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

destination for restore of datafile 00001: /u01/app/oracle/oradata/RTS/system01.dbf

destination for restore of datafile 00002: /u01/app/oracle/oradata/RTS/sysaux01.dbf

destination for restore of datafile 00003: /u01/app/oracle/oradata/RTS/undotbs01.dbf

destination for restore of datafile 00004: /u01/app/oracle/oradata/RTS/users01.dbf

channel ORA_DISK_1: reading from backup piece /home/oracle/backup/Inc_backup_05r5f6en_1_1

channel ORA_DISK_1: piece handle=/home/oracle/backup/Inc_backup_05r5f6en_1_1 tag=TAG20160513T045757

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:15

starting media recovery

unable to find archived log

archived log thread=1 sequence=0

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 05/13/2016 05:13:28

RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 0 and starting SCN of 972205

Above recovery successful and expecting next archive log and starting SCN 972205, which is greater than old current SCN of standby database.( Ref: Step-II )
Ensure archive log gap sequence between primary and standby database, it should be zero or one or two, because logfiles might have switched due to heavy load on your production primary database.

Cheers!! Standby database successfully recovered from incremental backup. Now onward you can continue replication with new archive log files.

Your suggestions and comments are highly appreciated, if any.

Stay tuned with my next article on failover(Activate) of standby database.

Manual Physical Standby Failover – Activate Standby Database – Oracle 11g Manual Data Guard Part-V

In case of disaster with Primary Database site, or not available for production for any reason then we can activated standby database as a primary production database.

Note: If you are trying to activate standby database for production use, please get permission from senior authority to do so.

On Standby database, steps to perform failover OR Activate standby database:

One:

Check database listener, it should be running:

[oracle@DR ~]$ lsnrctl status

Two:

Check database mode and role, It should be MOUNTED and PHYSICAL STANDBY:

SQL> select name,open_mode,database_role from v$database;

NAME OPEN_MODE DATABASE_ROLE

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

RTS MOUNTED PHYSICAL STANDBY

Three:

Copy archive logs from primary database, those yet to apply on standby database before activate it.

SQL> recover standby database until cancel;

AUTO

Four:

Activate standby database as primary database:

[oracle@DR ~]$ sqlplus / as sysdba

SQL> alter database activate standby database;

Database altered.

Five:

Shutdown standby database and startup:

SQL> shut immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 755769344 bytes

Fixed Size 2217184 bytes

Variable Size 448793376 bytes

Database Buffers 301989888 bytes

Redo Buffers 2768896 bytes

Database mounted.

Database opened.

Cheers!! Standby database successfully activated as Primary database. Failover successful.

Post activity check, ensure database mode and role, it should be READ WRITE and PRIMARY.

SQL> select name,open_mode,database_role from v$database;

NAME OPEN_MODE DATABASE_ROLE

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

RTS READ WRITE PRIMARY

Note: Please perform full database backup after failover.
Note: Once old primary database is available for use then you can reconfigure standby database.

Your suggestions and comments are highly appreciated, if any.

Core And Application DBA Blog

Friday, September 28, 2018

Steps to configure Manual Data Guard – Oracle 11g Manual Data Guard Part-I

Switch over and switch back – Oracle 11g Manual Data Guard Part-II

Switch-over Pre-requisites:

Switch-over steps:

Switch-back activity steps:

How to open Manual Physical Standby Database in READ ONLY mode – Oracle 11g Manual Data Guard Part-III

Incremental backup from SCN – Oracle 11g Manual Data Guard Part-IV

Manual Physical Standby Failover – Activate Standby Database – Oracle 11g Manual Data Guard Part-V

[ERROR]: The value of s_patch_service_name is not set correctly in atleast one of the context files.