10GR2 Physical Standby easy Creation that never fails

My goal is to create a Physical Standby DB, quickly and without errors.

Primary DB : PRIMA / Standby DB : STDBY

Assuming that the DB is located on pathnames ‘/data’ and ‘/redo’.

0] Decisions

Standby Creation : RMAN Disk Backup ( constraint = same pathname to RMAN dumps on both Servers ) then DUPLICATE FOR STANDBY

Data Protection Mode : Maximum Performance ( Default )

Standby Redolog files : NO

Pathnames conversion : YES [ XX_FILE_NAME_CONVERT parameter ]

Flashback Database : NO

Automatic Archivelogs Generation : 15 minutes [ archive_lag_target param ]

Broker installed in another post.

1]  Pre-requisite

1.1 Standby Server

1.1.1 Arborescence

Should be the same as Primary DB :

use this on PRIMA, to find out what's the DB structure

sqlplus -s '/ as sysdba' << ! | sort | sed 's/[^\/]*$//' | uniq | sed 's/^\///'

set head off feed off

select name from v\$datafile ;

select member from v\$logfile ;

select name from v\$controlfile ;

!

1.1.2 Declare DNS Host Alias & add /etc/oratab Entry

1.1.3 Create necessary listener.ora STDBY connectivity

add in LISTENER SID_LIST

    (SID_DESC =
      (ORACLE_HOME = <ORACLE_HOME>)
      (SID_NAME = <STDBY>)
    )

1.1.4 Oracle Password File creation

In Standby Instance environment

$ cd $ORACLE_HOME/dbs

$ orapwd file=orapw${ORACLE_SID} \ password=<SAME_AS_SYS_ON_PRIMARY>

1.2 Primary DB

1.2.1 Archivelog mode

1.2.2 Activate Force Logging

See, first, which objects will be impacted ( that are in Nologging Mode )

select owner, table_name from dba_tables where logging='NO' and owner not in ('SYS','SYSTEM','DBSNMP','MDSYS','WMSYS','FLOWS_030000','MPL5') order by 1,2 ;

select owner, index_name from dba_indexes where logging='NO' order by 1,2 ;

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

SQL> select FORCE_LOGGING from v$database ;

FORCE_LOG

---------

YES

1.2.3 Know your archivelogs consumption for the last 30 days

select trunc(completion_time) "date", min(sequence#) "num_log_begin_day", count(1) "NB_ARC", ceil(sum(blocks*block_size)/1024/1024/1024) Arclogs_GB

from v$archived_log where

completion_time > SYSDATE-30 group by trunc(completion_time) order by 1 desc ;

1.2.4 Verify that Password File exists, if not create it

SQL> select * from v$pwfile_users ;

USERNAME                       SYSDBA          SYSOPER

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

SYS                            TRUE            TRUE

1.2.5 Init parameters

1.2.5.1 Just check those

DB_NAME=prima

DB_UNIQUE_NAME=prima

LOG_ARCHIVE_DEST_STATE_1=ENABLE

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

archive_lag_target = 900

Note : ‘archive_lag_target’ unnecessary if not in Max Perf Sync mode ( see below ).

1.2.5.2 To be modified online with "alter system ... scope = both"

LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIMA,STDBY)'

LOG_ARCHIVE_DEST_STATE_2=DEFER

LOG_ARCHIVE_DEST_1='LOCATION=/<Archivelogs_Primary_location> VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIMA'

LOG_ARCHIVE_DEST_2='SERVICE=STDBY ARCH SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STDBY'

LOG_ARCHIVE_MAX_PROCESSES=8

dg_broker_config_file1 = '/<Primary_Server_first_location>/dr1PRIMA.dat'

dg_broker_config_file2 = '/<Prima_Server_alternate_location>/dr2PRIMA.dat'

Whenever Primary DB becomes a Standby :

STANDBY_FILE_MANAGEMENT=AUTO

FAL_SERVER=STDBY

FAL_CLIENT=PRIMA

STANDBY_ARCHIVE_DEST='/<Archivelogs_Primary_location>'

1.2.5.3 To be modified online with "alter system ... scope = both"

Only needed when Primary DB becomes a Standby :

# Specify the location of the standby DB datafiles followed by the primary location

DB_FILE_NAME_CONVERT='/data/stdby','/data/prima'

# Specify the location of the standby DB online redo log files followed by the primary location

LOG_FILE_NAME_CONVERT='/redo/stdby','/redo/prima'

1.2.5.4 Copy Primary Init parameter File to Standby Server

1.3 Back to Standby Server

1.3.1 Adap Primary Init parameter file as follow :

- change Path Names & Memory allocations ...

- change those specific Standby values

DB_UNIQUE_NAME=stdby

instance_name=STDBY

log_archive_dest_1='LOCATION=/<Archivelogs_Standby_location> VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STDBY'

LOG_ARCHIVE_DEST_2='SERVICE=PRIMA ARCH SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIMA'

log_archive_dest_state_2='ENABLE'

FAL_SERVER=PRIMA

FAL_CLIENT=STDBY

# Specify the location of the standby DB datafiles followed by the primary location

DB_FILE_NAME_CONVERT='/data/prima','/data/stdby'

# Specify the location of the standby DB online redo log files followed by the primary location

LOG_FILE_NAME_CONVERT='/redo/prima','/redo/stdby'

STANDBY_ARCHIVE_DEST='<Archivelogs_Primary_location>'

dg_broker_config_file1='/<Standby_Server_first_location>/dr1STDBY.dat'

dg_broker_config_file2='/<Stand_Server_alternate_location>/dr2STDBY.dat'

1.3.2 Create an SPFILE and start NOMOUNT Standby Instance

1.4 On both Servers

1.4.1 Add TNSNAMES or LDAP entries for PRIMA & STDBY

1.4.2 Check SQLNET access

This is very important that cross-instance communication works as below :

$ sqlplus SYS/<SYS_PSW_ON_PRIMARY>@PRIMA AS SYSDBA

$ sqlplus SYS/<SYS_PSW_ON_PRIMARY>@STDBY AS SYSDBA

2] Standby DB Creation

Thanks to this wonderful RMAN utility, we will backup Primary DB

 - on Disk , COMPRESSED mode

 - Online including Standby Controlfile

2.1 Primary DB

2.1.1 Prepare RMAN script

#+-+-+-++-+-+-++-+-+-++-+-+-+-+-+-+-+

# RMAN backup script to Standby

# Standard Database 10g

# Media : DISK

# NoCatalog

#+-+-+-++-+-+-++-+-+-++-+-+-+-+-+-+-+

#   9i cmd 'backup database + archivelogs + controlfile'

#   when channel type 'sbt_tape', default is "plus archivelog"

#   cinematic :

#    sql 'alter system archive log current' ;

#    backup archivelog all

#    backup database

#    sql 'alter system archive log current' ;

#    backup archivelog -> archivelogs generated during backup

#       delete input

#    autobackup of controlfile & spfile

connect target /

# autobackup of controlfile & spfile after a successful backup

# in a well-known format that can be retrieved without rcat !!

# default = OFF

configure controlfile autobackup on ;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/<Primary_Server_alternate_location>/backup/%F';

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/<Primary_Server_alternate_location>/backup/snapcf_<PRIMA>.f';

# avoid backup of archivelogs if ALREADY BACKUPED ONCE :

configure backup optimization on ;

# Media : DISK

CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET ;

run {

#   rcat/ctl & disk arclogs sync

  sql 'alter system archive log current' ;

#  resync catalog ;

  Change Archivelog All Crosscheck ;

  backup as compressed backupset database format '/<RMAN_DMP_location>/db_%d_s%s_U%U'

        plus archivelog format '/<RMAN_DMP_location>/ar_%d_s%s_U%U' ;

BACKUP CURRENT CONTROLFILE FOR STANDBY format '/<RMAN_DMP_location>/cs_%d_s%s_U%U';

  SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';  # so backup is consistent and recoverable

  BACKUP ARCHIVELOG ALL format '/<RMAN_DMP_location>/ar_%d_s%s_U%U' NOT BACKED UP 1 TIMES;

}

2.1.2 Execution

$ rman @<rman_backup_script>.scr | tee <rman_backup_script>.log

2.1.3 Copy RMAN dumps on Standby Server SAME PATH

2.1.4 Activate Primary to Standby Instance Communication

SQL> ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' ;

System altered.

The communication will be fully functionnal as soon as Standby Instance reachs the MOUNT state in step 2.2.1.

At that time, with FAL ( Fecth Archivelog ) capability, needed archivelogs will be sent from Primary to Standby server.

[ The SCN to which you recover must be greater than or equal to the standby control file SCN. ]

2.2 Standby DB

2.2.1 Instance should be in NOMOUNT.

2.2.2 Execute Duplicate to Standby script

#+-+-+-++-+-+-++-+-+-++-+-+-+-+-+-+-+

#

# RMAN duplicate for Standby script

#

# Target:               Primary DB

# AUXILIARY:            Standby DB

# Recovery catalog:     N

# Action:               duplicate for Standby

# Restore element:      database

# Instance state:       open

# controlfile state:    current

# Oracle version:       10g

# Media:                Disk

#

# Cinematic:

#       connect target aux

#       duplicate for standby

#

#+-+-+-++-+-+-++-+-+-++-+-+-+-+-+-+-+

connect target sys@PRIMA

connect AUXILIARY /

# Media : DISK

CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET ;

run {

  DUPLICATE TARGET DATABASE FOR STANDBY DORECOVER ;

}

$ rman @<rman_duplicate_script>.scr | tee <rman_duplicate_script>.log

…

Finished restore at 20/10/2011

contents of Memory Script:

{

   switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy recid=2 stamp=765034903 filename=/data/pelus/systPELUN.dbf

datafile 2 switched to datafile copy

input datafile copy recid=3 stamp=765034903 filename=/data/pelus/undo.dbf

datafile 3 switched to datafile copy

input datafile copy recid=4 stamp=765034903 filename=/data/pelus/users.dbf

datafile 4 switched to datafile copy

input datafile copy recid=5 stamp=765034903 filename=/data/pelus/xdb01.dbf

datafile 5 switched to datafile copy

input datafile copy recid=6 stamp=765034903 filename=/data/pelus/boPELUN.dbf

datafile 6 switched to datafile copy

input datafile copy recid=7 stamp=765034903 filename=/data/pelus/its_dat.dbf

datafile 7 switched to datafile copy

input datafile copy recid=8 stamp=765034903 filename=/data/pelus/sysaux01.dbf

datafile 8 switched to datafile copy

input datafile copy recid=9 stamp=765034903 filename=/data/pelus/gdm_dat.dbf

datafile 9 switched to datafile copy

input datafile copy recid=10 stamp=765034903 filename=/data/pelus/adb_dat.dbf

datafile 10 switched to datafile copy

input datafile copy recid=11 stamp=765034903 filename=/data/pelus/pel_datPELUN.dbf

datafile 11 switched to datafile copy

input datafile copy recid=12 stamp=765034903 filename=/data/pelus/pel_dev_6i_dat.dbf

datafile 12 switched to datafile copy

input datafile copy recid=13 stamp=765034903 filename=/data/pelus/ioe_hst_9i_dat.dbf

datafile 13 switched to datafile copy

input datafile copy recid=14 stamp=765034903 filename=/data/pelus/ioe_9i_idx.dbf

datafile 16 switched to datafile copy

input datafile copy recid=15 stamp=765034903 filename=/data/pelus/tools.dbf

contents of Memory Script:

{

   set until scn  9555833956;

   recover

   standby

   clone database

    delete archivelog

   ;

}

executing Memory Script

executing command: SET until clause

Starting recover at 20/10/2011

using channel ORA_AUX_DISK_1

using channel ORA_AUX_DISK_2

starting media recovery

channel ORA_AUX_DISK_2: starting archive log restore to default destination

channel ORA_AUX_DISK_1: starting archive log restore to default destination

channel ORA_AUX_DISK_2: restoring archive log

archive log thread=1 sequence=47033

channel ORA_AUX_DISK_2: reading from backup piece /work/oracle/pelun/rman/backup/ar_PELUN_s138_U4ampimr2_1_1

channel ORA_AUX_DISK_1: restoring archive log

archive log thread=1 sequence=47032

channel ORA_AUX_DISK_1: reading from backup piece /work/oracle/pelun/rman/backup/ar_PELUN_s135_U47mpimqn_1_1

channel ORA_AUX_DISK_1: restored backup piece 1

piece handle=/work/oracle/pelun/rman/backup/ar_PELUN_s135_U47mpimqn_1_1 tag=TAG20111020T105831

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

archive log filename=/work/pelus/arch/PELUS1_47032_602341547.arc thread=1 sequence=47032

channel clone_default: deleting archive log(s)

archive log filename=/work/pelus/arch/PELUS1_47032_602341547.arc recid=2 stamp=765034906

channel ORA_AUX_DISK_2: restored backup piece 1

piece handle=/work/oracle/pelun/rman/backup/ar_PELUN_s138_U4ampimr2_1_1 tag=TAG20111020T105841

channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:00:02

archive log filename=/work/pelus/arch/PELUS1_47033_602341547.arc thread=1 sequence=47033

channel clone_default: deleting archive log(s)

archive log filename=/work/pelus/arch/PELUS1_47033_602341547.arc recid=1 stamp=765034906

media recovery complete, elapsed time: 00:00:02

Finished recover at 20/10/2011

Finished Duplicate Db at 20/10/2011

Recovery Manager complete.

RMAN connects to the target instance and therefore knows where the dumps are located.

In case of problem [ Recover can’t find the archivelog backup ], it’s possible to put an UNTIL LOGSEQ command that tells RMAN not to go beyond what was backuped on Source Server. It is because, without any information, DUPLICATE command tries to be as close as possible as Target instance, timewise speaking / Though SQLNET communication should have brought the newly created archivelogs to Standby Server, as soon as Standby instance is Mounted.

Again : [ The SCN to which you recover must be greater than or equal to the standby control file SCN. ]

Find the Archivelog Sequence # with RMAN LIST on Primary DB or, reading the Alert.log file accordingly : the archivelog that includes

ALTER SYSTEM ARCHIVE LOG

Thu Oct 20 10:58:30 DFT 2011

LGWR: Standby redo logfile selected to archive thread 1 sequence 47033

LGWR: Standby redo logfile selected for thread 1 sequence 47033 for destination LOG_ARCHIVE_DEST_2

Thu Oct 20 10:58:30 DFT 2011

Thread 1 advanced to log sequence 47033 (LGWR switch)

  Current log# 3 seq# 47033 mem# 0: /rdo1/pelun/log_03_1.rdo

  Current log# 3 seq# 47033 mem# 1: /rdo2/pelun/log_03_2.rdo

Thu Oct 20 10:58:34 DFT 2011

Clearing standby activation ID 732565199 (0x2baa0ecf)

The primary database controlfile was created using the

'MAXLOGFILES 21' clause.

There is space for up to 17 standby redo logfiles

Use the following SQL commands on the standby database to create

standby redo logfiles that match the primary database:

ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 33554432;

ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 33554432;

ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 33554432;

ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 33554432;

ALTER DATABASE ADD STANDBY LOGFILE 'srl5.f' SIZE 33554432;

Starting control autobackup

Control autobackup written to DISK device

        handle '/work/oracle/pelun/rman/backup/c-589503915-20111020-00'

Thu Oct 20 10:58:38 DFT 2011

ALTER SYSTEM ARCHIVE LOG

Thu Oct 20 10:58:38 DFT 2011

LGWR: Standby redo logfile selected to archive thread 1 sequence 47034

LGWR: Standby redo logfile selected for thread 1 sequence 47034 for destination LOG_ARCHIVE_DEST_2

=> sequence 47034 -1 = sequence 47033

3] Post Installation

3.1 Test archivelog transport between instances

SQL> alter system switch logfile ;

System altered.

Check archivelog creation on Standby server.

3.2 Create redolog files

Remember, RMAN never takes care of redologs ...

SQL> select open_mode from v$database ;

OPEN_MODE

----------

MOUNTED

SQL> alter database clear logfile group 1 ;

Database altered.

SQL> alter database clear logfile group 2 ;

Database altered.

SQL> alter database clear logfile group 3 ;

Database altered.

SQL> alter database clear logfile group 4 ;

Database altered.

SQL> col MEMBER for a30

SQL> select * from v$logfile

    GROUP# STATUS  TYPE    MEMBER                         IS_

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

         1         ONLINE  /redo/stdby/log_01_1.rdo  NO

         2         ONLINE  /redo/stdby/log_02_1.rdo  NO

         3         ONLINE  /redo/stdby/log_03_1.rdo  NO

         4         ONLINE  /redo/stdby/log_04_1.rdo  NO

4 rows selected.

3.3 Start RECOVERY process

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE disconnect from session ;

Database altered.

Tue Jun  8 15:56:41 2010

MRP0: Background Managed Standby Recovery process started (STDBY)

Managed Standby Recovery not using Real Time Apply

See another post for Standby Monitoring.

3.4 Archivelogs deletion

cron this :

#+-+-+-++-+-+-++-+-+-++-+-+-+-+-+-+-+

#

# RMAN Standby delete received arcs

#

# Target:               Standby DB

# Recovery catalog:     N

# Action:               DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-1'

# Instance state:       mount

# controlfile state:    standby

# Oracle version:       10g

# Media:                Disk

#

# Cinematic:

#       connect target

#       DELETE ARCHIVELOG

#

#+-+-+-++-+-+-++-+-+-++-+-+-+-+-+-+-+

connect target /

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/<Stand_Server_alternate_location>/backup/%F';

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/<Stand_Server_alternate_location>/backup/snapcf_<STDBY>.f';

run {

  DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-1' ;

}

And that’s it !!

Broker configuration will be discussed in a following post.