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, 27 April 2013

Creating Physical Standby DB using (RMAN DUPLICATE TARGET)

Here in below example we will see step by step procedure how to create standby database by using RMAN DUPLICATE TARGET DATABASE command. While creating standby database, RMAN automate the following steps of the creation procedure.
  1. Restores the standby control file.
  2. Restores the primary datafile backups and copies.
  3. Recovers the standby database (optional) after the control file has been mounted up to the specified time or to the latest archived redo log generated.
  4. Finally RMAN leave the standby DB mounted, So we can start MRP manually (if needed)
Note: According to oracle documentation, RMAN cannot full automate for create standby database, there are some manual process require.
Steps:
Primary server side Preparation:
  1. Create Standby redologs
  2. Enable database force logging.
  3. Create Pfile or use ALTER SYSTEM to add standby parameter for primary database
  4. Enable Archiving (if not)
  5. Create Pfile for Standby database (optional)
  6. Configure Listener.ora and tnsname.ora for primary (if not)
  7. Copy Pfile and Password file to standby server.
  8. Take a full backup of Primary system and copy it to standby server.
  9. Create standby control file for primary database system and copy to standby server.
Standby server side Preparation:
  1. Create password file for standby (if not copied)
  2. Create directory structure similar to primary server.
  3. Modify the Pfile accordingly for standby.
  4. Finally verify connectivity between two server.
Consider the following point in mind before starting the actual process:
Point to Remember
Primary Server
Standby Server
Host Name
dbserver
dbserver1
DB_Name
Sadhan
Sadhan
DB_Unique_Name
Sadhan
Sadstby
Net_service_Name
Sadhan
Sadstby
Oracle database version: 10.2.0.1.0 and Platform: Windows 2003 Server
Create Standby Redo logs
SQL> select GROUP#,THREAD#,MEMBERS,BYTES FROM V$LOG;
GROUP#    THREAD#    MEMBERS   BYTES
-------   --------   --------  ---------
     1          1          1   52428800
     2          1          1   52428800
     3          1          1   52428800
SQL> select group#,member from v$logfile;
GROUP# MEMBER ORDER BY group#;
     1 D:\oracle\oradata\sadhan\redo01.log
     2 D:\oracle\oradata\sadhan\redo02.log
     3 D:\oracle\oradata\sadhan\redo03.log
SQL> SELECT GROUP#, BYTES FROM V$STANDBY_LOG;
no rows selected
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('D:\oracle\oradata\sadhan\stby_redo01.log') SIZE 52428800;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('D:\oracle\oradata\sadhan\stby_redo02.log') SIZE 52428800;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('D:\oracle\oradata\sadhan\stby_redo03.log') SIZE 52428800;
Database altered.
SQL> SELECT GROUP#, BYTES FROM V$STANDBY_LOG;
GROUP#   BYTES
------   ---------
     4   52428800
     5   52428800
     6   52428800
Force Logging to the primary DB and put your DB in Archive mode (if not)
In primary database we need to enable force logging and define some initialization parameters, which controls redo transport services while the database is in primary role.
SQL> Select FORCE_LOGGING FROM V$DATABASE;
SQL> Alter database FORCE_LOGGING;
SQL> Select name, open_mode, log_mode from V$database;
NAME      OPEN_MODE  LOG_MODE
--------- ---------- ------------
SADHAN    READ WRITE ARCHIVELOG
SQL> SELECT INSTANCE_NAME, HOST_NAME FROM V$INSTANCE;
INSTANCE_NAME    HOST_NAME
---------------- --------------
Sadhan           dbserver
SQL> SELECT name, value  FROM V$PARAMETER
     where name = 'service_names';
NAME              VALUE
----------        -----------
service_names     SADHAN.WORLD
Note: Create password file for primary database (if not exist) using orapwd.
Create Pfile or use ALTER SYSTEM to add the following parameters
SQL> CREATE PFILE = ‘D:\SADHAN.SQL’ FROM SPFILE;
service_names=SADHAN
db_unique_name=SADHAN
log_archive_config='dg_config=(sadhan, sadstby)'
log_archive_dest_1='LOCATION=E:\ORACLE\SADHAN\ARCHIVE valid_for=(ALL_LOGFILES, ALL_ROLE) db_unique_name=SADSTBY'
log_archive_dest_2='service=SADSTBY ASYNC valid_for=(ONLINE_LOGFILES, PRIMARY_ROLE) db_unique_name=SADSTBY'
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER='SADSTBY'
FAL_CLIENT='SADHAN'
db_file_name_convert=‘D:\ORACLE\ORADATA\SADHAN’,’D:\ORACLE\ORADATA\SADSTBY’
log_file_name_convert=’D:\ORACLE\ORADATA\SADHAN’,’D:\ORACLE\ORADATA\SADSTBY’
standby_file_management=auto
-or- you can use later system to dynamically modify the below parameters
SQL> alter system set log_archive_config = 'dg_config = (sadhan,sadstby)' scope=spfile;
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_1= 'LOCATION=E:\oracle\Sadhan\Archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=SADHAN';
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_2= 'service=sadstby LGWR ASYN VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=SADSTBY';
SQL> alter system set log_archive_dest_state_1=enable;
System altered.
SQL> alter system set log_archive_dest_state_2=enable;
System altered.
SQL> alter system set remote_login_passwordfile = exclusive scope=spfile;
System altered.
SQL>alter system set log_archive_max_processes=30 scope=spfile;
System altered.
#DATAGAURD PARAMTERS#
SQL> alter system set fal_server = sadstby;
System altered.
SQL> alter system set fal_client = sadhan;
System altered.
# db_file_name_convert: do not need for same directory structure
SQL> alter system set db_file_name_convert =
'D:\oracle\oradata\sadhan', 'D:\oracle\oradata\sadstby' scope=spfile;
System altered.
# log_file_name_convert: do not need for same directory structure
SQL> alter system set log_file_name_convert =
‘D:\oracle\oradata\sadhan', ‘D:\oracle\oradata\sadstby' scope=spfile;
System altered.
SQL> alter system set standby_file_management = auto scope=spfile;
System altered.
Note: Create password file in your primary database (if already exist no need) using orapwd.
Copy the Pfile and Password file to Standby server and modify this Pfile for standby
compatible='10.2.0.1.0'
control_files='d:\oracle\oradata\sadstby\control01.ctl’,’D:\oracle\oradata\sadstby\control02.ctl’
db_name=’sadhan’
service_names=SADSTBY
db_unique_name=sadstby
log_archive_config='dg_config=(sadhan, sadstby)'
log_archive_dest_1='LOCATION=E:\ORACLE\SADSTBY\ARCHIVE valid_for=(ALL_LOGFILES, ALL_ROLE) db_unique_name=SADSTBY'
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
log_archive_dest_2='service=sadhan ASYNC valid_for=(ONLINE_LOGFILES, PRIMARY_ROLE) db_unique_name=SADHAN'
FAL_SERVER='SADHAN'
FAL_CLIENT='SADSTBY'
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
db_file_name_convert=‘D:\ORACLE\ORADATA\SADHAN’,’D:\ORACLE\ORADATA\SADSTBY’
log_file_name_convert=’D:\ORACLE\ORADATA\SADHAN’,’D:\ORACLE\ORADATA\SADSTBY’
standby_file_management=auto
Create service for Standby DB and Configure Listener for both Primary and Standby
oradim -NEW -SID sadstby -INTPWD password -STARTMODE manual
TIPS: Assume Listener already configured on primary node. You should try to Create Listener (Standby) by using Net Configuration Assistant on Standby Server.
#Connection string for Primary Instance.
SADHAN =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.14.17)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = SADHAN)
)
)
#Connecting string for Standby Instance
STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.14.222)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = SADSTBY)
)
)
Add the below line in SQLNET.ORA (Standby DB) to broken connection detection on primary file system
SQLNET.EXPIRE_TIME=2
Finally check connection between two databases:
C:\>tnsping Sadhan
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
 (HOST = 192.168.14.17) (PORT = 1521))) (CONNECT_DATA = (SID = sadhan)))
OK (70 msec)
C:\>tnsping sadstby
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
 (HOST = 192.168.14.222) (PORT = 1521))) (CONNECT_DATA = (SID = sadstby)))
OK (60 msec)
Make a note of log Sequence Number for RMAN until recovery
SQL> Alter system switch logfile;
SQL> Select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
450
Take a full backup of Primary database and copy the all backup to standby host
RMAN>run
{
allocate channel ch1 type disk;
allocate channel ch2 type disk;
backup incremental level 0 format 'H:\oraback\Weekly_%T_FULL_%d-%s_%p.db' database;
backup format 'H:\oraback\Weekly_%T_FULL_%d-%s_%p.db' archivelog all;
backup current controlfile for standby format'H:\oraback\Weekly_%T_FULL_%d-%s_%p.db' ; 
}
Note: Manage the same directory Structure on both machines. Copy the backup to standby on the same location as you are taking backup on primary site.
Create Standby Control file on Primary database and Copy to Standby site.
C:\>RMAN TARGET /
connected to target database: SADHAN (DBID=63198018)
RMAN> backup current controlfile for standby format=’H:\oraback\sadstby_CFile.%U’;
Starting backup at 25-FEB-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=158 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including standby control file in backupset
channel ORA_DISK_1: starting piece 1 at 25-FEB-13
channel ORA_DISK_1: finished piece 1 at 25-FEB-13
piece handle=H:\oraback\sadstbycontrol.ctl tag=TAG20130225T020241 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 25-FEB-13
Restart both the instance with modified Pfile and create Spfile from Pfile
SQL> conn sys/****@sadhan as sysdba
SQL> shutdown immediate;
SQL> startup PFILE=’D:\SADHAN.SQL’ FROM SPFILE
SQL> CREATE SPFILE FROM PFILE =‘D:\SADHAN_NEW.SQL’;
SQL> shutdown immediate:
SQL> startup;
On standby server:
SQL> conn sys/****@sadstby as sysdba
Connected to an idle instance.
SQL> startup nomount pfile='D:\sadstby.sql';
ORACLE instance started.
SQL> create spfile from pfile='D:\sadstby.sql';
File created.
Now bounce back the database to activate with spfile
SQL> show parameter log_archive_dest_1
NAME                       TYPE        VALUE
---------------------      ---------   -----------------------
log_archive_dest_1         string   service=Sadhan valid_for=(online logfiles, primary_role)
                                 db_unique name = sadhan                               
log_archive_dest_10        string  
SQL> shut immediate;
SQL> startup nomount;
Now Connect RMAN and configure parameter for Standby server
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘H:\oraback\%F’;
CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT ‘H:\oraback\%U.bak’ MAX PIECESIZE 4 G;
CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT ‘H:\oraback\%U.bak’ MAX PIECESIZE 4 G;
CMD> rman target sys/****@sadhan
RMAN > connect auxiliary sys/****@sadstby
Recovery Manager: Release 10.2.0.1.0 - Production on Fri Feb 25 02:30:20 2013
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
connected to target database: SADHAN (DBID=63198018)
auxiliary database Password:
connected to auxiliary database: SADSTBY (not mounted)
Use RMAN DUPLICATE TARGET DATABASE to create standby database
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
Now you have successfully created standby database using RMAN. Try to check both the database is synchronizing.
C:\sqlplus “/as sysdba”  --connect with primary
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            E:\oracle\Archivelog
Oldest online log sequence     56
Next log sequence to archive   58
Current log sequence           58
SQL> exit
C:\>sqlplus sys/***@sadstby as sysdba
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            E:\oracle\Archivelog
Oldest online log sequence     56
Next log sequence to archive   58
Current log sequence           58
SQL> exit
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.
Note: This statement include the DISCONNECT FROM SESSION option so that Redo Apply runs in a background session. If you find the Error: RMAN-06059: expected archived log not found, lost of archived log compromises recoverability then try to user RMAN> change archivelog all crosscheck;
SQL> select recovery_mode from v$archive_dest_status;
RECOVERY_MODE
-----------------------
MANAGED
MANAGED
MANAGED
MANAGED
MANAGED
MANAGED
5 rows selected.
SQL> select max(sequence#) from v$log_history;

MAX(SEQUENCE#)
--------------
            60
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.

8 comments:

  1. Hey very nice blog!!
    Hi there,I enjoy reading through your article post, I wanted to write a little comment to support you and wish you a good continuationAll the best for all your blogging efforts.
    Appreciate the recommendation! Let me try it out.
    Keep working ,great job!
    http://www.sqlservermasters.com/

    ReplyDelete
  2. Fantastic article ! You havemade some very astute statements and I appreciate the the effort you have put into your writing. Its clear that you know what you are writing about. I am excited to read more of your sites content.
    http://www.mindqonline.com/

    ReplyDelete
  3. canal libÚrÚ : ch1
    canal libÚrÚ : ch2
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: Úchec de la commande Duplicate Db Ó 06/30/2013 19:57:52
    RMAN-03015: une erreur s'est produite dans le script stockÚ Memory Script
    RMAN-06026: certaines cibles sont introuvables - abandon de la restauration
    RMAN-06024: aucune sauvegarde ou copie du fichier de contr¶le Ó restaurer

    ReplyDelete
  4. plz can you help me ,when i run the script of dupplicating the database this error appear

    ReplyDelete
    Replies
    1. Dear,

      The error indicating it is not finding the backup. there can be many reson
      Try to list backup controlfile what it telling. check the user permission having the write permission for backup location and send me the exact command what you are using for duplicate the database.
      RMAN> LIST BACKUP OF CONTROLFILE;

      Delete
  5. RMAN> run {
    2> # Set the last log sequence number
    3> set until sequence = 4 thread = 1;
    4> allocate auxiliary channel ch1 type disk;
    5> allocate auxiliary channel ch2 type disk;
    6> duplicate target database for standby dorecover nofilenamecheck;
    7> }

    exÚcution de la commande : SET until clause

    canal libÚrÚ : ORA_DISK_1
    canal affectÚ : ch1
    canal ch1 : sid=157 typedev=DISK

    canal affectÚ : ch2
    canal ch2 : sid=158 typedev=DISK

    DÚmarrage de Duplicate Db dans 05/07/13

    contenu de script mÚmoire:
    {
    set until scn 583951;
    restore clone standby controlfile;
    sql clone 'alter database mount standby database';
    }
    exÚcution de script mÚmoire

    exÚcution de la commande : SET until clause

    DÚmarrage de restore dans 05/07/13

    canal libÚrÚ : ch1
    canal libÚrÚ : ch2
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: Úchec de la commande Duplicate Db Ó 07/05/2013 02:32:06
    RMAN-03015: une erreur s'est produite dans le script stockÚ Memory Script
    RMAN-06026: certaines cibles sont introuvables - abandon de la restauration
    RMAN-06024: aucune sauvegarde ou copie du fichier de contr¶le Ó restaurer

    ______________ LIST BACKUP OF CONTROLFILE;--------------


    RMAN> LIST BACKUP OF CONTROLFILE;


    Liste des ensembles de sauvegarde
    ===================

    ClÚ BS Type Taille LV Type d'unitÚ Temps ÚcoulÚ Heure d'exÚcution
    ------- ---- -- ---------- ----------- ------------ -----------------
    3 Incr 0 6.77M DISK 00:00:04 04/07/13
    ClÚ BP : 3 Statut : AVAILABLE CompressÚ : NO Balise : TAG20130704T174730
    Nom d'ÚlÚment : G:\WEEKLY_20130704_FULL_ORCL-3_1.DB
    Fichier de contr¶le inclus : SCN Ckp : 583915 Heure Ckp : 04/07/13

    ClÚ BS Type Taille LV Type d'unitÚ Temps ÚcoulÚ Heure d'exÚcution
    ------- ---- -- ---------- ----------- ------------ -----------------
    7 Full 6.77M DISK 00:00:03 04/07/13
    ClÚ BP : 7 Statut : AVAILABLE CompressÚ : NO Balise : TAG20130704T174932
    Nom d'ÚlÚment : G:\WEEKLY_20130704_FULL_ORCL-7_1.DB
    Fichier de contr¶le de secours inclus : SCN Ckp : 583966 Heure Ckp : 04/07/13

    ClÚ BS Type Taille LV Type d'unitÚ Temps ÚcoulÚ Heure d'exÚcution
    ------- ---- -- ---------- ----------- ------------ -----------------
    8 Full 6.77M DISK 00:00:03 04/07/13
    ClÚ BP : 8 Statut : AVAILABLE CompressÚ : NO Balise : TAG20130704T183520
    Nom d'ÚlÚment : G:\ORCL2_CFILE.08ODTRNB_1_1
    Fichier de contr¶le de secours inclus : SCN Ckp : 586470 Heure Ckp : 04/07/13

    ClÚ BS Type Taille LV Type d'unitÚ Temps ÚcoulÚ Heure d'exÚcution
    ------- ---- -- ---------- ----------- ------------ -----------------
    9 Full 6.77M DISK 00:00:02 05/07/13
    ClÚ BP : 9 Statut : AVAILABLE CompressÚ : NO Balise : TAG20130705T023022
    Nom d'ÚlÚment : G:\ORCL2_CFILE.09ODUNHU_1_1
    Fichier de contr¶le de secours inclus : SCN Ckp : 610846 Heure Ckp : 05/07/13

    ClÚ BS Type Taille LV Type d'unitÚ Temps ÚcoulÚ Heure d'exÚcution
    ------- ---- -- ---------- ----------- ------------ -----------------
    10 Full 6.80M DISK 00:00:02 05/07/13
    ClÚ BP : 10 Statut : AVAILABLE CompressÚ : NO Balise : TAG20130705T023027
    Nom d'ÚlÚment : G:\C-1347637339-20130705-00
    Fichier de contr¶le inclus : SCN Ckp : 610851 Heure Ckp : 05/07/13

    RMAN>

    ReplyDelete
  6. i wanna know if i can dupplicate the database from the primary server or from the stby server is the same thing?

    ReplyDelete
    Replies
    1. Dear,

      Try your command without "dorecover"
      Regarding duplicate database, you can create duplicate database thruogh the backup of standby database.
      In fact i did not try this concept. I don't think so there is any differnce you succesfully duplicate.

      Delete