Oracle 11g Logo

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
-----------
 957835

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.

Thank you 🙂

Oracle 11g Logo

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.

Previously, we covered “Incremental backup from SCN – Oracle 11g Manual Data Guard Part-IV”.

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.

Stay tune 🙂

Oracle 11g Logo

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.

Thank you. 🙂

Oracle 11g Logo

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.

Thank you. 🙂

Oracle 11g Logo

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
---------------
NO

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#)
 --------------
 7

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#)
 --------------
 3

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#)
 --------------
 7

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.

Stay Tuned with my next article on “Switch over and switch back – Oracle 11g Manual Data Guard Part-II“.

Thank you. 🙂