HOME > > Creating Standby Database through RMAN

Creating Standby Database through RMAN

Anup - Friday, June 5, 2009

You can use the Recovery Manager DUPLICATE TARGET DATABASE FOR STANDBY command to create a standby database.

RMAN automates the following steps of the creation procedure:

  • Restores the standby control file.
  • Restores the primary datafile backups and copies.
  • Optionally, RMAN recovers the standby database (after the control file has been mounted) up to the specified time or to the latest archived redo log generated.
  • RMAN leaves the database mounted so that the user can activate it, place it in manual or managed recovery mode, or open it in read-only mode.

After the standby database is created, RMAN can back up the standby database and archived redo logs as part of your backup strategy. These standby backups are fully interchangeable with primary backups. In other words, you can restore a backup of a standby datafile to the primary database, and you can restore a backup of a primary datafile to the standby database.

Step-by-Step Stand by Database Configuration:

Step1: Configure Listener in Production Server and Standby Server.

  • TIPS: You should try to Create Listener (Standby) by using Net Configuration Assistant on Standby Server.
  • TIPS: assume Listener already configure with PROD name on Primary Node. If Listener not configured on Primery Node , You Should Create Listener by using Net Configuration Assistant on Primary Server.

Step2: Configure TNSNAMES.ORA in Production Server and Standby Server. following TNSNAMES.ORA entry on Production Database and Standby Database

# Connection string for Primary Instance.

PROD =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = Production IP)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = PROD)

)

)

# Connecting string for Standby Instance

STANDBY =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = STANDBY IP)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = PROD)

)

)

Step3: Put your production database in Archive Log mode if your database not running in Archive log mode add following entries in init.ora file in Production Server.

LOG_ARCHIVE_START=TRUE

LOG_ARCHIVE_DEST_1='LOCATION=C:\oracle\database\archive MANDATORY REOPEN=30'

LOG_ARCHIVE_DEST_2='SERVICE=STANDBY REOPEN=300'

LOG_ARCHIVE_DEST_STATE_1=enable

LOG_ARCHIVE_DEST_STATE_2=enable

LOG_ARCHIVE_FORMAT=ARC%S.arc

REMOTE_ARCHIVE_ENABLE=true

STANDBY_FILE_MANAGEMENT=AUTO

STANDBY_ARCHIVE_DEST = 'C:\standby\archive '

# db_file_name_convert: do not need; same directory structure

# log_file_name_convert: do not need; same directory structure

Step4 : Configure RMAN in Production Instance if not configured earlier.

Example:

1. CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/oraprod/rmanbkp/%F';

2. CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT '/home/oraprod/rmanbkp/%U.bak' MAX PIECESIZE 4 G;

3. CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT '/home/oraprod/rmanbkp/%U.bak' MAX PIECESIZE 4 G;

Step5 : Take a full valied backup of Production instance

RMAN> backup database plus archivelog;

Step6 : go to Standby machine and Create Service for standby instance

Step7 : create a standby controlfile on production Machine

RMAN> backup current controlfile for standby format='c:\rman_backup\stby_cfile.%U';

Step8 : Record last log sequance

SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)

--------------
100

Step8 : Backup new archive logs

RMAN>backup archivelog all;

Step9 : Make the RMAN Backups Available to Standby Server

Step10 : On the both system, the same directory structure was set-up
Step11 : Create init.ora file for standby database.
Copy init.ora file from Production Server to Stand by Server in Database folder in oracle home directory and add following entries:

LOG_ARCHIVE_START = TRUE

LOG_ARCHIVE_DEST_1 = 'LOCATION=c:\oracle\database\archive MANDATORY'

LOG_ARCHIVE_FORMAT = arch%s.arc

REMOTE_ARCHIVE_ENABLE = true

STANDBY_FILE_MANAGEMENT = AUTO

LOG_ARCHIVE_MIN_SUCCEED_DEST=1

STANDBY_ARCHIVE_DEST = 'C:\standby\archive '

fal_server = FAL

fal_client = STANDBY

# db_file_name_convert: do not need; same directory structure

# log_file_name_convert: do not need; same directory structure

Note: Although most of the initialization parameter settings in the text initialization parameter file that you copied from the primary system are also appropriate for the physical standby database, some modifications need to be made.
Step: 12 Start Physical standby database
Start up the stand by database using following commands
C:\>set oracle_sid=PROD
C:\>sqlplus /nolog
SQL> conn sys/prod as sysdba

Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Step13 : Go to the Standby server and connect RMAN

Run the following:

CMD> rman target sys/change_on_install@prod_conn_string

RMAN > connect auxiliary sys/change_on_install

Step14 : The following RUN block can be used to fully duplicate the target database from the latest full backup. This will create the standby database:

run {
# Set the last log sequence number
set until sequence = 100 thread = 1;
# Allocate the channel for the duplicate work
allocate auxiliary channel ch1 type disk;
# Duplicate the database to ORA920
duplicate target database for standby dorecover nofilenamecheck ;
}

RMAN> exit

Step15 : Put the Standby in Managed recovery Mode

On the standby database, run the following:

SQL> sqlplus "/ as sysdba"
SQL> recover standby database;
SQL> alter database recover managed standby database disconnect;
Database altered.

Error and Solution:

Error 1: RMAN-06059: expected archived log not found, lost of archived log compromises recoverability

Solution: RMAN> change archivelog all crosscheck;

Shahim said...

Excellent article,
Will definately try it out...

Unknown said...

Gr8 Article Worked for me

Unknown said...

Superb Article worked for me too...Thanks for such a nice article..

Contact me

Get in Touch

Need to get touch with me? Please fill out the form with your enquiry.

Name
Anup Srivastav
Address
Lucknow - Utter Pradesh
Email
myindiandba@gmail.com
Message me