Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Saturday, 4 May 2013

Creating Physical Standby DB using RMAN (without shutting down the prod DB)

Here we are going to create standby database from RMAN backup without shutting down the production database.
Steps:
(1)   Take RMAN FULL backup of Primary database
(2)   Create standby controlfile on primary site.
(3)   Move the Backup and Standby control file to standby site
(4)   Create or copy PFILE to standby DB and modify it accordingly
(5)   Configure tns and listerner for both primary and standby database.
(6)   Start the standby database in NOMOUNT then perform the next step
Take RMAN backup on the production system disk
Rman target sys/****@sadhan catalog/catalog@rman
RMAN> BACKUP database plus archivelog;
Create standby controlfile on Primary database
RMAN> backup current controlfile for standby format ‘H:\oraback\sadstby_cfile.ctl’;
RMAN> SQL ‘ALTER SYSTEM ARCHIVE LOG CURRENT’;
RMAN> BACKUP ARCHIVELOG ALL NOT BACKED UP 1 TIMES;
Move the Backup to the Standby Server
You can move the backup from primary site to standby site by using any of the below method:
         You can move the backup exactly the same location on standby as they were created on primary database.
         You can use CATALOG BACKUPIECE command (on 10g onwards) if you are moving the backup to a different location.
         If the backup are on NFS then mount the NFS on standby server with the same name as you mounted on primary database.
         Use FTP or SCP to move the backup.
         If you taken the backup on tape then you must do proper changes on standby server to restore the backup using tape.
Perform log switches on primary and record the last log sequence number.
SQL> alter system switch logfile;
SQL> alter system switch logfile;
Note: Do not forget to make same folder structures in the destination server.
Create Pfile in primary server after making necessary change transfer to standby site:
SQL> connect target /
SQL> create pfile=’D:\ORABACK\sadstby.sql’ from spfile;
background_dump_dest='D:\oracle\admin\sadstby\bdump'
control_files='D:\oracle\oradata\sadstby\control01.ctl','D:\oracle\oradata\sadstby\control01.ctl'
core_dump_dest='D:\oracle\sadstby\cdump'
DB_UNIQUE_NAME= SADSTBY
db_name='SADHAN'
instance_name='sadstby'
log_archive_format='%t_%s_%r.dbf'
log_archive_dest_2='SERVICE=SADHAN VALID_FOR=(ONLINE_LOGFILES, ALL_ROLES) DB_UNIQUE_NAME=SADHAN'
log_archive_dest_state_2='DEFER'
log_archive_dest_1='LOCATION=E:\ORACLE\SADSTBY\ARCHIVE'
remote_archive_enable='true'
fal_server='SADHAN'
fal_client='SADSTBY'
log_file_name_convert=('D:\oracle\oradata\sadhan', 'D:\oracle\oradata\sadstby')
db_file_name_convert=('D:\oracle\oradata\sadhan', 'D:\oracle\oradata\sadstby')
Configure Network File for standby
#Assume Primary database is already configure. You have to do necessary addition for standby database.
#Update Listener.ora in standby server:
(SID_DESC =
(GLOBAL_DBNAME = SADSTBY)
(ORACLE_HOME = d:\oracle)
(SID_NAME = SADSTBY)
)
#Update tnsnames.ora on primary node:
SADSTBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.14.222)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = SADSTBY)
)
)
Create the password file for standby database.
orapwd file=orapwSADSTBY password=oracle entries=10
Startup the standby database in nomount phase
sqlplus “/ as sysdba”
SQL>
startup nomount pfile=’D:\ORABACK\sadstby.sql’;
Connected to an idle instance.
SQL> Create spfile from pfile=’D:\ORABACK\sadstby.sql’;
SQL> shutdown immediate;
SQL> startup nomount;
Duplicate the database by connecting to target, auxiliary and catalog.
connect target /
connect catalog catalog/catalog@rman
connect auxiliary sys/****@sadstby
connected to target database: SADHAN (DBID=63198018)
connected to recovery catalog database
connected to auxiliary database: SADHAN (not mounted)
RMAN> run { 
# Set the last log sequence number
set until sequence = 450 thread = 1;
allocate auxiliary channel ch1 type disk; 
allocate auxiliary channel ch2 type disk;
duplicate target database for standby dorecover nofilenamecheck;
}
RMAN> exit
Once 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 vice versa.
Take the Standby in Managed recovery Mode and check sync the logs from primary are shipped over to standby archive destination.
SQL> Select max(sequence#) from v$archive_log;
MAX(SEQUENCE#)
--------------
            58
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
            60
SQL> select name,open_mode,log_mode from v$database;
NAME      OPEN_MODE  LOG_MODE
--------- ---------- ------------
SADHAN    MOUNTED    ARCHIVELOG
SQL> select recovery_mode from v$archive_dest_status;

SQL> select max(sequence#) from v$log_history;
MAX(SEQUENCE#)
--------------
            58
SQL> alter database recover managed standby database disconnect;
Database altered.

1 comments:

  1. BlueHost is definitely one of the best web-hosting provider for any hosting services you might require.

    ReplyDelete