CREATED BY: Chad Dimatulac

DESCRIPTION: This is a step-by-step guideline for building Oracle 10g/11g Data Guard Physical Standby.

    The procedures are based on the traditional use of Rman backup/clone which is the only method available for

    versions 10g and below but will also work on 11g. If you are already on 11g, there is a better way

    to build a standby without the use of rman backup which will be discussed on a separate article.

    Intended for lab use only.

I. PREPARE THE ENVIRONMENT

0. Optionally, setup a shared file system (NFS on UNIX or, Shared Drive in Windows) to store the RMan backup files

   in the following exercise. Otherwise, it need to be manually copied from primary server to standby server

   maintaining the same directory paths. In our case, the NFS mount point is /disk/share

1. Install Oracle Database Software in Standby server matching the version of the primary database.

   Install software only including patches without the seed database.

   opatch lsinventory

   

   NOTE: setup oraenv, ORACLE_SID are the same on primary and standby

   

2. Create database directories in the standby server to be the same as the primary database server.

   This covers,

   ORACLE_BASE/admin - adump, bdump, cdump, udump, etc.

   Database dir - redo, rbs, temp, dbf, utl_dir, data pump dir, flashback dir, iDIR, etc.

   

@Primary server:   

  copy and paste the resulting valid mkdir paths and execute in Standby server.

  

SQL> column file_name format a30

SQL> select unique 'mkdir -p '||a.DIRPATH MAKEDIR

from ( select substr(FILE_NAME,0,instr(FILE_NAME,'/',-1)-1) DIRPATH

from dba_data_files

union

select substr(FILE_NAME,0,instr(FILE_NAME,'/',-1)-1) DIRPATH

from dba_temp_files

union

select substr(MEMBER,0,instr(MEMBER,'/',-1)-1) DIRPATH

from v$logfile

union

select substr(NAME,0,instr(NAME,'/',-1)-1) DIRPATH

from v$controlfile

) a;

  

SQL> column directory_path format a70

SQL> select 'mkdir -p '||directory_path MAKEDIR from dba_directories;

!!! WARNING: you might need to verify mkdir syntax on some of the query results below depending on how the output was formatted before executing in a script.

SQL> column value format a70

SQL> set lines 120

SQL> select 'mkdir -p '||value MAKEDIR

from v$parameter

where (regexp_like(name, '^log_archive_(dest|dest\_([1-9]))$', 'i') or name like '%dump_dest' or name like '%file_dest' or name like 'diag%dest' or name ='utl_file_dir') and value is not null;

   

3. Optional: Configure Dataguard IP on both the primary and standby servers.

   This will be used as dataguard specific listeners tuned to send/receive bulk data transmissions.

       

4. Setup TNS layer for dataguard connections on both primary and standby servers.

   For listener.ora, set GLOBAL_DBNAME as DB_UNIQUE_NAME.DB_DOMAIN.

   For tnsnames.ora, if using SERVICE_NAMES, this will be DB_UNIQUE_NAME.DB_DOMAIN by default.

   Query the v$parameter for registered values.

   

sqlnet.ora (10g only)

DEFAULT_SDU_SIZE=32767

PRIMARY DATABASE tnsnames.ora

ORA10DG1 =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = pacific)(PORT = 1530))

    )

    (CONNECT_DATA =

      (SID = ORA10DG1)

    )

  )

  

ORA10DG1_LISTENER =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = pacific)(PORT = 1530))

    )

  )

 

ORA10DG1SB =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = atlantic)(PORT = 1530))

    )

    (CONNECT_DATA =

      (SID = ORA10DG1)

    )

  )  

STANDBY DATABASE tnsnames.ora

ORA10DG1SB =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = atlantic)(PORT = 1530))

    )

    (CONNECT_DATA =

      (SID = ORA10DG1)

    )

  )  

  

  

ORA10DG1SB_LISTENER =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = atlantic)(PORT = 1530))

    )

  )

 

ORA10DG1 =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = pacific)(PORT = 1530))

    )

    (CONNECT_DATA =

      (SID = ORA10DG1)

    )

  )

PRIMARY DATABASE listener.ora

  

ORA10DG1 =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = pacific)(PORT = 1530))

    )

  )

SID_LIST_ORA10DG1 =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = /dsk0/oradb/ORA10DG1/sys/app/oracle/product/10.2.0/db)

      (PROGRAM = extproc)

    )

    (SID_DESC =

      (GLOBAL_DBNAME = ORA10DG1.earth.com)

      (ORACLE_HOME = /dsk0/oradb/ORA10DG1/sys/app/oracle/product/10.2.0/db)

      (SID_NAME = ORA10DG1)

    )

  )

  

CONNECT_TIMEOUT_ORA10DG1 = 0

STARTUP_WAIT_TIME_ORA10DG1=0

#LOGGING_ORA10DG1=OFF

#TRACE_LEVEL_ORA10DG1=OFF   

STANDBY DATABASE listener.ora

ORA10DG1SB =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = atlantic)(PORT = 1530))

    )

  )

SID_LIST_ORA10DG1SB =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = /dsk0/oradb/ORA10DG1/sys/app/oracle/product/10.2.0/db)

      (PROGRAM = extproc)

    )

    (SID_DESC =

      (GLOBAL_DBNAME = ORA10DG1SB.earth.com)

      (ORACLE_HOME = /dsk0/oradb/ORA10DG1/sys/app/oracle/product/10.2.0/db)

      (SID_NAME = ORA10DG1)

    )

  )

  

CONNECT_TIMEOUT_ORA10DG1SB = 0

STARTUP_WAIT_TIME_ORA10DG1SB=0

#LOGGING_ORA10DG1SB=OFF

#TRACE_LEVEL_ORA10DG1SB=OFF   

  

Now, start the dataguard listeners on both Primary and Standby.

@Primary server

lsnrctl start ORA10DG1

@Standby server

lsnrctl start ORA10DG1SB

Now, ping the listeners to test if reachable remotely.

@Primary server

tnsping ORA10DG1SB

@Standby server

tnsping ORA10DG1

5. Enable remote sysdba login from standby server to primary server.

   Create orapw file in the primary server and test sqlplus connection to primary database from standby server

   as sys.

   $ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs/orapwORA10DG1 password=change_on_install force=y

   

   @standby server, test connect to primary as sysdba:

   

   SQL> connect sys/change_on_install@ORA10DG1 as sysdba

   

   

6. Optionally, create Standby Redo log files in the Primary database. This is a recommended "Best Practice"

   to enable the primary database take the Standby role during a switch or failover to DR.

   

SQL> select type, group#, member from v$logfile;

TYPE                      GROUP# MEMBER

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

ONLINE                         1 /dsk0/oradb/ORA10DG1/oradata/redo1/redo1g1ORA10DG1.log

ONLINE                         1 /dsk0/oradb/ORA10DG1/oradata/redo2/redo2g1ORA10DG1.log

ONLINE                         2 /dsk0/oradb/ORA10DG1/oradata/redo1/redo1g2ORA10DG1.log

ONLINE                         2 /dsk0/oradb/ORA10DG1/oradata/redo2/redo2g2ORA10DG1.log

ONLINE                         3 /dsk0/oradb/ORA10DG1/oradata/redo1/redo1g3ORA10DG1.log

ONLINE                         3 /dsk0/oradb/ORA10DG1/oradata/redo2/redo2g3ORA10DG1.log

ONLINE                         4 /dsk0/oradb/ORA10DG1/oradata/redo1/redo1g4ORA10DG1.log

ONLINE                         4 /dsk0/oradb/ORA10DG1/oradata/redo2/redo2g4ORA10DG1.log

8 rows selected.

SQL>

SQL> select group#, bytes from v$log;

    GROUP#      BYTES

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

         1   10485760

         2   10485760

         3   10485760

         4   10485760

SQL>

   

SQL> alter database add standby logfile

  group 5 ('/dsk0/oradb/ORA10DG1/oradata/redo1/sbredo1g1ORA10DG1.log', '/dsk0/oradb/ORA10DG1/oradata/redo2/sbredo2g1ORA10DG1.log') size 10485760,

  group 6 ('/dsk0/oradb/ORA10DG1/oradata/redo1/sbredo1g2ORA10DG1.log', '/dsk0/oradb/ORA10DG1/oradata/redo2/sbredo2g2ORA10DG1.log') size 10485760,

  group 7 ('/dsk0/oradb/ORA10DG1/oradata/redo1/sbredo1g3ORA10DG1.log', '/dsk0/oradb/ORA10DG1/oradata/redo2/sbredo2g3ORA10DG1.log') size 10485760,  

  group 8 ('/dsk0/oradb/ORA10DG1/oradata/redo1/sbredo1g4ORA10DG1.log', '/dsk0/oradb/ORA10DG1/oradata/redo2/sbredo2g4ORA10DG1.log') size 10485760;

   

SQL> select type, group#, member from v$logfile;

TYPE                      GROUP# MEMBER

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

ONLINE                         1 /dsk0/oradb/ORA10DG1/oradata/redo1/redo1g1ORA10DG1.log

ONLINE                         1 /dsk0/oradb/ORA10DG1/oradata/redo2/redo2g1ORA10DG1.log

ONLINE                         2 /dsk0/oradb/ORA10DG1/oradata/redo1/redo1g2ORA10DG1.log

ONLINE                         2 /dsk0/oradb/ORA10DG1/oradata/redo2/redo2g2ORA10DG1.log

ONLINE                         3 /dsk0/oradb/ORA10DG1/oradata/redo1/redo1g3ORA10DG1.log

ONLINE                         3 /dsk0/oradb/ORA10DG1/oradata/redo2/redo2g3ORA10DG1.log

ONLINE                         4 /dsk0/oradb/ORA10DG1/oradata/redo1/redo1g4ORA10DG1.log

ONLINE                         4 /dsk0/oradb/ORA10DG1/oradata/redo2/redo2g4ORA10DG1.log

STANDBY                        5 /dsk0/oradb/ORA10DG1/oradata/redo1/sbredo1g1ORA10DG1.log

STANDBY                        5 /dsk0/oradb/ORA10DG1/oradata/redo2/sbredo2g1ORA10DG1.log

STANDBY                        6 /dsk0/oradb/ORA10DG1/oradata/redo1/sbredo1g2ORA10DG1.log

STANDBY                        6 /dsk0/oradb/ORA10DG1/oradata/redo2/sbredo2g2ORA10DG1.log

STANDBY                        7 /dsk0/oradb/ORA10DG1/oradata/redo1/sbredo1g3ORA10DG1.log

STANDBY                        7 /dsk0/oradb/ORA10DG1/oradata/redo2/sbredo2g3ORA10DG1.log

STANDBY                        8 /dsk0/oradb/ORA10DG1/oradata/redo1/sbredo1g4ORA10DG1.log

STANDBY                        8 /dsk0/oradb/ORA10DG1/oradata/redo2/sbredo2g4ORA10DG1.log

16 rows selected.

SQL>

  

7. Optional: Manually create a standby control file if it will not be included in RMan backup.

             Do this in the assumption that no new datafile are added during the lengthy RMan backup.

             Otherwise, you must create standby control file within RMan script after the line of Backup...Database.

             In our case, we are using rman to create the standby control file.

              

   NOTE: You cannot use a single control file for both the primary and standby databases.

   

   @Primary Database,

   

   SQL>startup mount;

   SQL>alter database create standby controlfile as '/dsk0/oradb/ORA10DG1/oradata/ctl1/ctl1ORA10DG1SB.ctl';

   Copy standby controlfile to backup directories

   UNIX> cd /dsk0/oradb/ORA10DG1/oradata/ctl1

   UNIX> cp ctl1ORA10DG1SB.ctl ../ctl2/ctl2ORA10DG1SB.ctl

   UNIX> cp ctl1ORA10DG1SB.ctl ../ctl3/ctl3ORA10DG1SB.ctl

   Then later, these new files need to be declared as the control files in the Standby init.ora

   

   

8. Build the init.ora config files to accommodate Data Guard parameters.

8.1. Offline init.ora Method

This method requires a restart of the primary database to enable the changes done through the init.ora file. If your primary is configured with spfile, you may dynamically update the required parameters which avoids the instance restart, but you’ll do this at the later part during the “Enable Dataguard” section.

Note that in my init parameter examples I have shown file path conversion to demonstrate situations where the standby may have a different directory structures. The Rman standby duplicate recovery will use this conversion to place the data files and log files onto the new path.

   

   SQL> create pfile='/disk/share/backup/pfileORA10DG1SB.ora' from spfile;

   SQL> create pfile='/disk/share/backup/pfileORA10DG1.ora' from spfile;

   

   Note:

   

   DB_NAME     is the same in both primary and standby.

   DB_UNIQUE_NAME    For standby init.ora, is named as ORA10DG1SB. default is DB_NAME, so no need to change in primary.

         

   

   Check if you are using Flash Reco Area for archiving:

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     164

Current log sequence           167

SQL>      

SQL> show parameter DB_RECOVERY_FILE_DEST

NAME                                 TYPE             VALUE

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

db_recovery_file_dest                string           /dsk0/oradb/ORA10DG1/flashReco

db_recovery_file_dest_size           big integer      16G

SQL>

   

The above indicates the use of flash recovery (USE_DB_RECOVERY_FILE_DEST) with a location and size defined by the init param

db_recovery_file_dest and db_recovery_file_dest_size

Otherwise, a local path is defined for Archive destination.

 

***** PRIMARY DATABASE INIT PARAMS

pfileORA10DG1.ora

#------------------------------------------#

# DataGuard Required Params

#------------------------------------------#

#-- ROLE INDEPENDENT PARAMETERS --#

db_name=ORA10DG1

db_unique_name=ORA10DG1

instance_name=ORA10DG1

service_names=ORA10DG1.earth.com  # use this as your global_dbname of listener.ora

log_archive_max_processes=30  # default is 2, must set minimum 4 or greater

remote_login_passwordfile=EXCLUSIVE

# NOTE: When log_archive_dest_# is declared, it invalidates the entry in log_archive_dest parameter.

log_archive_dest_1='LOCATION=/dsk0/oradb/ORA10DG1/oradata/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORA10DG1'

#Uncomment below if using Flash Recovery Area

#log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORA10DG1'

log_archive_dest_state_1=ENABLE

#-- PRIMARY ROLE PARAMETERS --#

log_archive_config='DG_CONFIG=(ORA10DG1,ORA10DG1SB)'  # the DB_UNIQUE_NAME of each dataguard member

log_archive_dest_2='SERVICE=ORA10DG1SB ASYNC REOPEN=60 NET_TIMEOUT=60 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORA10DG1SB'

log_archive_dest_state_2=ENABLE

#-- STANDBY ROLE PARAMETERS IN PRIMARY DB --#

fal_server=ORA10DG1SB          # net service name primary Fetch Archive Log for gap

fal_client=ORA10DG1            # net service name gap requesting database (standby)

standby_file_management=AUTO   # enable replication of OS file addition and deletion to standby db

                               # only works on physical standby config, default MANUAL

#db_file_name_convert = '/dsk0/oradb/ORA10DG1SB', '/dsk0/oradb/ORA10DG1'    # file path conversion 'primary path','standby path'

#log_file_name_convert = '/dsk0/oradb/ORA10DG1SB', '/dsk0/oradb/ORA10DG1'

#------------------------------------------#

# Network Registration

#------------------------------------------#

local_listener=ORA10DG1_LISTENER

***** STANDBY DATABASE INIT PARAMS

pfileORA10DG1SB.ora

  

#------------------------------------------#

# DataGuard Required Params

#------------------------------------------#

#-- ROLE INDEPENDENT PARAMETERS --#

db_name=ORA10DG1

db_unique_name=ORA10DG1SB

instance_name=ORA10DG1

service_names=ORA10DG1SB.earth.com  # use this as your global_dbname of listener.ora

control_files=("/dsk0/oradb/ORA10DG1/oradata/ctl1/ctl1ORA10DG1SB.ctl",

               "/dsk0/oradb/ORA10DG1/oradata/ctl2/ctl2ORA10DG1SB.ctl",

               "/dsk0/oradb/ORA10DG1/oradata/ctl3/ctl3ORA10DG1SB.ctl")

               

log_archive_max_processes=30  # default is 2, must set minimum 4 or greater

remote_login_passwordfile=EXCLUSIVE

# NOTE: When log_archive_dest_# is declared, it invalidates the entry in log_archive_dest parameter.

log_archive_dest_1='LOCATION=/dsk0/oradb/ORA10DG1/oradata/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORA10DG1SB'

#Uncomment below if using Flash Recovery Area

#log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORA10DG1SB'

log_archive_dest_state_1=ENABLE

#-- PRIMARY ROLE PARAMETERS --#

log_archive_config='DG_CONFIG=(ORA10DG1SB,ORA10DG1)'  # the DB_UNIQUE_NAME of each dataguard member

log_archive_dest_2='SERVICE=ORA10DG1 ASYNC REOPEN=60 NET_TIMEOUT=60 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORA10DG1'

log_archive_dest_state_2=ENABLE

#-- STANDBY ROLE PARAMETERS IN STANDBY DB --#

fal_server=ORA10DG1              # net service name primary Fetch Archive Log for gap

fal_client=ORA10DG1SB            # net service name gap requesting database (standby)

standby_file_management=AUTO   # enable replication of OS file addition and deletion to standby db

                               # only works on physical standby config, default MANUAL

#db_file_name_convert = '/dsk0/oradb/ORA10DG1', '/dsk0/oradb/ORA10DG1SB'    # file path conversion 'primary path','standby path'

#log_file_name_convert = '/dsk0/oradb/ORA10DG1', '/dsk0/oradb/ORA10DG1SB'

#------------------------------------------#

# Network Registration

#------------------------------------------#

local_listener=ORA10DG1SB_LISTENER

NOTE: The datafile file path conversion is required if your standby database has a different directory structure compared to the primary.

                               

9. Copy required files to standby server

   

   @NFS drive:

   

   pfileORA10DG1SB.ora

   orapwORA10DG1

   

   @standby:

   cp /disk/share/backup/pfileORA10DG1SB.ora $ORACLE_HOME/dbs/initORA10DG1.ora

   

   cp /disk/share/backup/orapwORA10DG1 $ORACLE_HOME/dbs/orapwORA10DG1

        

         

   

II. BACKUP PRIMARY DATABASE

This is in the assumption that archivelog is enabled on the primary database.

If not, do:

SQL> shutdown immediate;

SQL> startup mount;

SQL> alter database archivelog;

SQL> alter database open;

In most cases, tables are created with the default logging mode. But there are times that such is not the case and data loads were done nologging mode (direct load) for improved performance. In both cases, archive logging is manually being bypassed and is detrimental to the consistency of the standby database. It is being suggested to enforce logging at the database level by doing:

SQL> alter database force logging;

Keep in mind that such enforcement impacts the performance of your direct loading processes.

Also, ensure that the directory to store the backup exists. In my case, I am using an NFS shared directory which is mounted on both the primary and standby servers. If you don’t have an NFS setup, make sure the same directory structure for the backup exists on both servers. Once the backup is done in the primary db, you may copy the backup files onto the same directory paths.

NFS Mount point:

/disk/share/backup

FULL BACKUP OPTION 1: COMMAND LINE

$ rman

RMAN> connect target sys/change_on_install

RMAN> spool log to /disk/share/backup/bak_opt0_1.log

RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 2;

RMAN> SET ENCRYPTION ON IDENTIFIED BY 'secure_backup_ora10dg1' ONLY;

RMAN> run {

    allocate channel c1 type disk;

    allocate channel c2 type disk;

    allocate channel c3 type disk;

    allocate channel c4 type disk;

    backup tag full_db_backup format '/disk/share/backup/DB_ORA10DG1_t%t_s%s_p%p' database plus archivelog;

    backup tag full_db_ctlfile format '/disk/share/backup/STANDBY_CTL_ORA10DG1_t%t_s%s_p%p' current controlfile for standby;

    release channel c1;

    release channel c2;

    release channel c3;

    release channel c4;

}

FULL BACKUP OPTION 2: RMAN SCRIPT

This is my preferred method especially on larger database systems.

#!/bin/ksh

LOG_DATE=`date +%Y%m%d%H%M`

ORACLE_BASE=/dsk0/oradb/ORA10DG1/sys/app/oracle ; export ORACLE_BASE

ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db ; export ORACLE_HOME

ORACLE_SID=ORA10DG1 ; export ORACLE_SID

DISKBACKUP_PATH=/disk/share/backup ; export DISKBACKUP_PATH

if [ ! -f $DISKBACKUP_PATH ];

then

  mkdir -p $DISKBACKUP_PATH

fi

CMD_STR="

SET ENCRYPTION ON IDENTIFIED BY 'secure_backup_ora10dg1' ONLY;

run {

    allocate channel c1 type disk;

    allocate channel c2 type disk;

    allocate channel c3 type disk;

    allocate channel c4 type disk;

    backup tag full_db_backup format '${DISKBACKUP_PATH}/DB_${ORACLE_SID}_t%t_s%s_p%p' database plus archivelog;

    backup tag full_db_ctlfile format '${DISKBACKUP_PATH}/STANDBY_CTL_${ORACLE_SID}_t%t_s%s_p%p' current controlfile for standby;

    release channel c1;

    release channel c2;

    release channel c3;

    release channel c4;

}

"

export CMD_STR

$ORACLE_HOME/bin/rman target sys/change_on_install nocatalog log ${DISKBACKUP_PATH}/BakForDg_${ORACLE_SID}_${LOG_DATE}.log << EOF

$CMD_STR

EOF

If not using NFS, then copy the backup files to standby servers into the same directory paths.

For duplex archive log path, the "delete input" of the archivelog backup will cause only to drop the files defined in the first archive log destination path.

III. ENABLE DATAGUARD

OPTION 1: Enable Data Guard via init.ora

This approach is only necessary if the required Data Guard parameter changes where all done via the init.ora file. This requires a restart of the primary database.

If your primary is already configured with spfile, use Option 2 for dynamic parameter change.

1. Shutdown Primary server.

   If there's a separate db listener for application, stop it now. Leave the DG listener running.

   

   lsrnctl stop <listener name>

   Now, shutdown the db.

   

   SQL> shutdown immediate

   

2. Create init.ora of Primary server based on pfile we modified before.

   If init.ora, spfile exist, backup first.

     cd $ORACLE_HOME/dbs

     mv initORA10DG1.ora initORA10DG1.noDG

     mv spfileORA10DG1.ora spfileORA10DG1.noDG

     cp /disk/share/backup/pfileORA10DG1.ora $ORACLE_HOME/dbs/initORA10DG1.ora

3. Re-create spfile of Primary and startup

   SQL> startup mount pfile=/disk/share/backup/pfileORA10DG1.ora

   

   SQL> create spfile from pfile='/disk/share/backup/pfileORA10DG1.ora';

   SQL> shutdown immediate

   

   SQL> startup

OPTION 2: Dynamically Enable Data Guard Parameters

@Primary database with a configured spfile, do the following:

#---- Role Independent Parameters

SQL> column type format a12

SQL> set line 110

SQL> show parameter archive_dest_1

NAME                                 TYPE         VALUE

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

log_archive_dest_1                   string       LOCATION=/dsk0/oradb/ORA10DG1/

                                                  oradata/arch

log_archive_dest_10                  string

SQL> alter system

  set log_archive_dest_1='LOCATION=/dsk0/oradb/ORA10DG1/oradata/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORA10DG1'

  comment='added VALID_FOR and DB_UNIQUE_NAME parameters for DataGuard'

  scope=both;

SQL> alter system set log_archive_dest_state_1=ENABLE scope=both;

SQL> show parameter archive_dest_1

NAME                                 TYPE         VALUE

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

log_archive_dest_1                   string       LOCATION=/dsk0/oradb/ORA10DG1/

                                                  oradata/arch VALID_FOR=(ALL_LO

                                                  GFILES,ALL_ROLES) DB_UNIQUE_NA

                                                  ME=ORA10DG1

log_archive_dest_10                  string

#---- Primary role parameters

SQL> show parameter archive_dest_2

NAME                                 TYPE         VALUE

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

log_archive_dest_2                   string

SQL> alter system

  set log_archive_dest_2='SERVICE=ORA10DG1SB ASYNC REOPEN=60 NET_TIMEOUT=60 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORA10DG1SB'

  comment='the standby archive log destination'

  scope=both;

SQL> alter system set log_archive_dest_state_2=ENABLE scope=both;

SQL> show parameter archive_dest_2

NAME                                 TYPE         VALUE

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

log_archive_dest_2                   string       SERVICE=ORA10DG1SB ASYNC REOPE

                                                  N=60 NET_TIMEOUT=60 VALID_FOR=

                                                  (ONLINE_LOGFILES,PRIMARY_ROLE)

                                                   DB_UNIQUE_NAME=ORA10DG1SB

SQL> alter system

set log_archive_config='DG_CONFIG=(rasuapro,rasuasdb)'

comment='the DB_UNIQUE_NAME of each dataguard member'

scope=both;

#---- Standby role parameters

SQL> show parameter fal

NAME                                 TYPE         VALUE

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

fal_client                           string

fal_server                           string

SQL> alter system

set fal_server=ORA10DG1SB

comment='net service name primary Fetch Archive Log for gap'

scope=both;

SQL> alter system

set fal_client=ORA10DG1

comment='net service name gap requesting database (standby)'

scope=both;

SQL> show parameter fal

NAME                                 TYPE         VALUE

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

fal_client                           string       ORA10DG1

fal_server                           string       ORA10DG1SB

SQL>

SQL> show parameter standby_file_management

NAME                                 TYPE         VALUE

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

standby_file_management              string       MANUAL

SQL> alter system

set standby_file_management=AUTO

comment='enable replication of OS file addition and deletion to standby db'

scope=both;

SQL> show parameter standby_file_management

NAME                                 TYPE         VALUE

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

standby_file_management              string       AUTO

#---- Network Registration

SQL> show parameter local_listener

NAME                                 TYPE         VALUE

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

local_listener                       string       

SQL>

SQL> alter system

set local_listener=ORA10DG1_LISTENER

comment='register the database to the listener'

scope=both;

SQL> show parameter local_listener

NAME                                 TYPE         VALUE

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

local_listener                       string       ORA10DG1_LISTENER

SQL>

Register the database to the listener,

SQL> alter system register;

#---- File Conversion

The following is optional only if the file paths were different between the primary and standby; a conversion is needed.

NOTE: The parameters *_file_name_convert cannot be dynamically modified to both memory and spfile (scope=BOTH).

The change can only be done at the spfile and would require an instance restart to take effect.

You will see an error similar to the following if you attempt to use scope=BOTH.

set db_file_name_convert='/dsk0/oradb/ORA10DG1SB', '/dsk0/oradb/ORA10DG1'

    *

ERROR at line 2:

ORA-02096: specified initialization parameter is not modifiable with this option

SQL> show parameter file_name_convert

NAME                                 TYPE         VALUE

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

db_file_name_convert                 string

log_file_name_convert                string

SQL>

SQL> alter system

set db_file_name_convert='/dsk0/oradb/ORA10DG1SB', '/dsk0/oradb/ORA10DG1'

comment='file path conversion <primary path>,<standby path>'

scope=spfile;

SQL> alter system

set log_file_name_convert='/dsk0/oradb/ORA10DG1SB', '/dsk0/oradb/ORA10DG1'

comment='file path conversion <primary path>,<standby path>'

scope=spfile;

At this point, the changes will not take effect unless an instance restart occurs.

VERIFY DATA GUARD CONFIGURATION

1. Check Primary server spfile entries if you got the correct log destination entries meant for primary.

Make sure that the secondary standby archive destination (ex. log_archive_dest_2) meant for the standby database has entries and is enabled (log_archive_dest_state_2).

   SQL> show parameter log_archive_dest

         

2. Check if Standby server can connect thru Primary Data Guard listener.

     

     @Standby server,

     

     sqlplus /nolog

     SQL> connect sys/change_on_install@ORA10DG1 as sysdba

     SQL> exit

     

3. Optionally, startup Primary server application listener to resume user transactions.

It is best to hold-off the restart of application listener up to the maximum allowed maintenance downtime while doing the restore for standby in next step. This is to reduce the sync time of Data Guard on high volume transaction database.  

IV. BACKUP ACCUMULATED ARCHIVE LOGS

Time may have passed since the last backup and there may be new updates that went in.

Let’s backup accumulated archive logs.

At primary,

SQL> alter system archive log current;

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

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

           171

SQL>

Note: The above sequence is the latest archive log which will be used later as our point of recovery for the Standby database.

Do final archive log backup using the script,

#!/bin/ksh

LOG_DATE=`date +%Y%m%d%H%M`

ORACLE_BASE=/dsk0/oradb/ORA10DG1/sys/app/oracle ; export ORACLE_BASE

ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db ; export ORACLE_HOME

ORACLE_SID=ORA10DG1 ; export ORACLE_SID

DISKBACKUP_PATH=/disk/share/backup ; export DISKBACKUP_PATH

if [ ! -f $DISKBACKUP_PATH ];

then

  mkdir -p $DISKBACKUP_PATH

fi

#-- NOTE: You may optionally delete the archive logs upon backup

#--       using the (archivelog all delete input) clause for the CMD_STR

CMD_STR="

SET ENCRYPTION ON IDENTIFIED BY 'secure_backup_ora10dg1' ONLY;

run {

    allocate channel c1 type disk;

    allocate channel c2 type disk;

    backup

      tag ${ORACLE_SID}_afterdbbak_arc_logs

      filesperset 20

      format '${DISKBACKUP_PATH}/arclog_${ORACLE_SID}_%s_%p_%t'

      (archivelog all);

    release channel c1;

    release channel c2;

}

"  

export CMD_STR

$ORACLE_HOME/bin/rman target sys/change_on_install nocatalog log ${DISKBACKUP_PATH}/ArcBakForDg_${ORACLE_SID}_${LOG_DATE}.log << EOF

$CMD_STR

EOF

         

      

V. BUILD STANDBY DATABASE

1. Start Standby database in NOMOUNT.

export ORACLE_SID=ORA10DG1

sqlplus /nolog

SQL> connect sys/change_on_install as sysdba;

SQL> startup nomount pfile='/disk/share/backup/pfileORA10DG1SB.ora';

SQL> create spfile from pfile='/disk/share/backup/pfileORA10DG1SB.ora';

SQL> show parameter log_archive_dest

SQL> shutdown immediate;

SQL> startup nomount;

2. Restore as duplicate database for standby.

It is best for the primary database to be in quiet mode (no transactions) when doing the duplicate recovery for standby to ensure that both primary db and standby db log sequence can be in-sync faster.

The quiet mode can be achieved either by open the database in restricted mode or restart the primary database with only the dataguard listener open, or startup the database in mount mode.

You can leave the primary database in an active state where the Standby would then be initially built up to a certain sequence number and let the Data Guard do the catch up. But you have to weigh-in the amount of time for a Standby duplicate to be completed which is the same amount of time to be behind of accumulated logs of an active primary db and this becomes the basis of the standby to catch-up.

In my case, I prefer the mount mode as this ensures that everything really is "quiet" since it allows the standby to be in-sync initially before Data Guard becomes active on shipping logs. This approach is the fastest way to be in-sync, but this requires to have a downtime.

My example below is based on building a standby with an active primary database which requires the Standby database to catch-up.

@Standby server,

OPTION 1: Command line restore

This is an example of a database restore for standby with auto recovery up to the latest log sequence of the primary.

RMAN> connect target sys/change_on_install@ORA10DG1

connected to target database: ORA10DG1 (DBID=2508653868)

RMAN> connect auxiliary sys/change_on_install

connected to auxiliary database: ORA10DG1 (not mounted)

RMAN> spool log to /export/home/oracle/Res_Duplicate_1.log

RMAN> list backup;  # take note of the latest Arc log Seq number, the next sequence is the starting log to be shipped by primary

RMAN> SET DECRYPTION IDENTIFIED BY 'secure_backup_ora10dg1';

RMAN> run {

allocate auxiliary channel d1 type disk;

allocate auxiliary channel d2 type disk;

allocate auxiliary channel d3 type disk;

allocate auxiliary channel d4 type disk;

duplicate target database for standby nofilenamecheck dorecover;

release channel d1;

release channel d2;

release channel d3;

release channel d4;

}

OPTION 2: Script restore

Script 1: Standby duplicate with auto recovery.

This will restore the database and execute the recovery up to the latest archive log sequence of the primary. Use this if you have plenty of storage space in the primary for the archive logs.

Keep in mind that the archive logs cannot be deleted by a regular log backup until the primary database was able to send the logs to the standby which is not available yet.

dupstandby_ORA10DG1SB_autoreco.sh

#!/bin/ksh

LOG_DATE=`date +%Y%m%d%H%M`

ORACLE_BASE=/dsk0/orabin/10gR2 ; export ORACLE_BASE

ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db ; export ORACLE_HOME

ORACLE_SID=ORA10DG1SB ; export ORACLE_SID

DISKBACKUP_PATH=/disk/share/backup ; export DISKBACKUP_PATH

CMD_STR="

list backup;  # take note of the latest Arc log Seq number, the next sequence is the starting log to be shipped by primary

SET DECRYPTION IDENTIFIED BY 'secure_backup_ora10dg1';

run {

allocate auxiliary channel d1 type disk;

allocate auxiliary channel d2 type disk;

allocate auxiliary channel d3 type disk;

allocate auxiliary channel d4 type disk;

duplicate target database for standby nofilenamecheck dorecover;

release channel d1;

release channel d2;

release channel d3;

release channel d4;

}

"

export CMD_STR

$ORACLE_HOME/bin/rman target sys/change_on_install@ORA10DG1 auxiliary sys/change_on_install log ${HOME}/StandbyDup_${ORACLE_SID}_${LOG_DATE}.log  << EOF

$CMD_STR

EOF

Once the standby restore has completed successfully, check if both databases are in-sync or, how far they are out-sync.

@primary,

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

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

           172

SQL>

@standby,

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

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

           171

SQL>

Script 2: Standby duplicate with manual recovery.

This script is ideal for situations where the primary database had limited archive log space to fill-in while waiting for the standby build. You can determine if this is your situation by comparing the estimated time of Standby duplicate restore if it is longer than the time it takes for the archive log destination of the primary to be full.

RMAN> connect target sys/change_on_install@ORA10DG1

RMAN> list backup;

...

...

  List of Archived Logs in backup set 16

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

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

  1    168     387085     13-OCT-12 387104     13-OCT-12

  1    169     387104     13-OCT-12 392588     13-OCT-12

  1    170     392588     13-OCT-12 394055     13-OCT-12

  1    171     394055     13-OCT-12 405138     14-OCT-12

RMAN>

Take note of the latest Archivelog sequence number, this will be your recovery point.

dupstandby_ORA10DG1SB_manreco.sh

#!/bin/ksh

LOG_DATE=`date +%Y%m%d%H%M`

ORACLE_BASE=/dsk0/orabin/10gR2 ; export ORACLE_BASE

ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db ; export ORACLE_HOME

ORACLE_SID=ORA10DG1SB ; export ORACLE_SID

DISKBACKUP_PATH=/disk/share/backup ; export DISKBACKUP_PATH

CMD_STR="

list backup;

SET DECRYPTION IDENTIFIED BY 'secure_backup_ora10dg1';

run {

allocate auxiliary channel d1 type disk;

allocate auxiliary channel d2 type disk;

allocate auxiliary channel d3 type disk;

allocate auxiliary channel d4 type disk;

set until sequence = 171;     # the value comes from previous query of v$log_history, or Rman list backup

duplicate target database for standby nofilenamecheck;

release channel d1;

release channel d2;

release channel d3;

release channel d4;

}

"

export CMD_STR

$ORACLE_HOME/bin/rman target sys/change_on_install@ORA10DG1 auxiliary sys/change_on_install log ${HOME}/StandbyDup_${ORACLE_SID}_${LOG_DATE}.log  << EOF

$CMD_STR

EOF

While the script is running, you may optionally move the latest archive logs onto another temporary disk but leaving some archive logs needed for the manual recovery. You may put back the archive logs that was set aside and execute the manual recovery again until you are not too far behind. As for the archive logs already applied, you may delete them if your previous archive log backup had captured them.

SQL> alter database recover managed standby database through sequence 244;

The above statement will recover the database from 172 up to 244.

To check the progress of recovery,

 

On a separate SQL*Plus session,do:

SQL> select sequence#, applied from v$archived_log;

Once recovery has completed, you may reload onto the archive log directory the next set of logs that was set aside and do again the database recover through the next latest sequence. You may repeat the entire process again until there's nothing left on the logs set aside.

Then, finally stop the recovery:

SQL> alter database recover managed standby database cancel;

At this point, you may proceed to the next instructions to start the redo apply.

VI. START REDO APPLY AT STANDBY DATABASE

           

@Standby server,

Verify that the database is at mount state, we need to set the recovery mode to be managed (if IDLE, then it's not doing any recovery) to keep the log sequence current in your log history.

Also, add the SRL files BEFORE enabling the managed recovery in case you haven’t created in the primary.

 

SQL> select name,open_mode,log_mode from v$database;

NAME                        OPEN_MODE                      LOG_MODE

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

ORA10DG1                    MOUNTED                        ARCHIVELOG

SQL> select recovery_mode from v$archive_dest_status;

     

RECOVERY_MODE

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

IDLE

IDLE

IDLE

IDLE

IDLE

IDLE

IDLE

IDLE

IDLE

IDLE

IDLE

11 rows selected.

SQL>      

If you have not created the standby redo in the primary, the standby duplicate restore will not have one.

In this case, you need to build a standby redo for the standby database such as the following steps.

Otherwise, no need to build it as the query on the v$logfile should reflect it, and you may proceed by activating the redo apply.

Example to build a standby redo:

                       

SQL> select type, group#, member from v$logfile;

TYPE                      GROUP# MEMBER

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

ONLINE                         1 /dsk0/oradb/ORA10DG1/oradata/redo1/redo1g1ORA10DG1.log

ONLINE                         1 /dsk0/oradb/ORA10DG1/oradata/redo2/redo2g1ORA10DG1.log

ONLINE                         2 /dsk0/oradb/ORA10DG1/oradata/redo1/redo1g2ORA10DG1.log

ONLINE                         2 /dsk0/oradb/ORA10DG1/oradata/redo2/redo2g2ORA10DG1.log

ONLINE                         3 /dsk0/oradb/ORA10DG1/oradata/redo1/redo1g3ORA10DG1.log

ONLINE                         3 /dsk0/oradb/ORA10DG1/oradata/redo2/redo2g3ORA10DG1.log

ONLINE                         4 /dsk0/oradb/ORA10DG1/oradata/redo1/redo1g4ORA10DG1.log

ONLINE                         4 /dsk0/oradb/ORA10DG1/oradata/redo2/redo2g4ORA10DG1.log

8 rows selected.

SQL>

SQL> select group#, bytes from v$log;

    GROUP#      BYTES

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

         1   10485760

         4   10485760

         3   10485760

         2   10485760

SQL>

SQL> alter database add standby logfile

  group 5 ('/dsk0/oradb/ORA10DG1/oradata/redo1/sbredo1g1ORA10DG1.log', '/dsk0/oradb/ORA10DG1/oradata/redo2/sbredo2g1ORA10DG1.log') size 10485760,

  group 6 ('/dsk0/oradb/ORA10DG1/oradata/redo1/sbredo1g2ORA10DG1.log', '/dsk0/oradb/ORA10DG1/oradata/redo2/sbredo2g2ORA10DG1.log') size 10485760,

  group 7 ('/dsk0/oradb/ORA10DG1/oradata/redo1/sbredo1g3ORA10DG1.log', '/dsk0/oradb/ORA10DG1/oradata/redo2/sbredo2g3ORA10DG1.log') size 10485760,  

  group 8 ('/dsk0/oradb/ORA10DG1/oradata/redo1/sbredo1g4ORA10DG1.log', '/dsk0/oradb/ORA10DG1/oradata/redo2/sbredo2g4ORA10DG1.log') size 10485760;

  

SQL> column member format a70

SQL> set linesize 120

SQL> select type, group#, member from v$logfile;

TYPE                      GROUP# MEMBER

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

ONLINE                         1 /dsk0/oradb/ORA10DG1/oradata/redo1/redo1g1ORA10DG1.log

ONLINE                         1 /dsk0/oradb/ORA10DG1/oradata/redo2/redo2g1ORA10DG1.log

ONLINE                         2 /dsk0/oradb/ORA10DG1/oradata/redo1/redo1g2ORA10DG1.log

ONLINE                         2 /dsk0/oradb/ORA10DG1/oradata/redo2/redo2g2ORA10DG1.log

ONLINE                         3 /dsk0/oradb/ORA10DG1/oradata/redo1/redo1g3ORA10DG1.log

ONLINE                         3 /dsk0/oradb/ORA10DG1/oradata/redo2/redo2g3ORA10DG1.log

ONLINE                         4 /dsk0/oradb/ORA10DG1/oradata/redo1/redo1g4ORA10DG1.log

ONLINE                         4 /dsk0/oradb/ORA10DG1/oradata/redo2/redo2g4ORA10DG1.log

STANDBY                        5 /dsk0/oradb/ORA10DG1/oradata/redo1/sbredo1g1ORA10DG1.log

STANDBY                        5 /dsk0/oradb/ORA10DG1/oradata/redo2/sbredo2g1ORA10DG1.log

STANDBY                        6 /dsk0/oradb/ORA10DG1/oradata/redo1/sbredo1g2ORA10DG1.log

TYPE                      GROUP# MEMBER

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

STANDBY                        6 /dsk0/oradb/ORA10DG1/oradata/redo2/sbredo2g2ORA10DG1.log

STANDBY                        7 /dsk0/oradb/ORA10DG1/oradata/redo1/sbredo1g3ORA10DG1.log

STANDBY                        7 /dsk0/oradb/ORA10DG1/oradata/redo2/sbredo2g3ORA10DG1.log

STANDBY                        8 /dsk0/oradb/ORA10DG1/oradata/redo1/sbredo1g4ORA10DG1.log

STANDBY                        8 /dsk0/oradb/ORA10DG1/oradata/redo2/sbredo2g4ORA10DG1.log

16 rows selected.

SQL>

ACTIVATE THE REDO APPLY OF STANDBY DATABASE

           

SQL> alter database recover managed standby database using current logfile disconnect from session;

     

Database altered.

SQL> select recovery_mode from v$archive_dest_status;

RECOVERY_MODE

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

MANAGED REAL TIME APPLY

MANAGED REAL TIME APPLY

MANAGED REAL TIME APPLY

MANAGED REAL TIME APPLY

MANAGED REAL TIME APPLY

MANAGED REAL TIME APPLY

MANAGED REAL TIME APPLY

MANAGED REAL TIME APPLY

MANAGED REAL TIME APPLY

MANAGED REAL TIME APPLY

MANAGED REAL TIME APPLY

11 rows selected.

SQL>

Make the primary db available to applications and start shipping logs.

@primary, start the application listener (if you have any) and switch the redo which should send the new archive logs onto the standby server.

$ lsnrctl start <your_app_listener>

SQL> connect sys as sysdba

SQL> alter system archive log current;

Let's check the sync-up

@Primary,

SQL> select DATABASE_ROLE from V$DATABASE;

DATABASE_ROLE

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

PRIMARY

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

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

           173

           

@Standby,

SQL> select DATABASE_ROLE from V$DATABASE;

DATABASE_ROLE

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

PHYSICAL STANDBY

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

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

           173          

This proves that the log shipping is working and both databases are in-sync with their current log sequence at this point.

You may check how far behind the redo apply by looking at the applied column:

SQL> select sequence#, applied from v$archived_log;          

CONGRATULATIONS! Data Guard is now working!

          

**********************************

       T H E   E N D                    

**********************************

         

TO START/STOP PHYSICAL STANDBY DATABASE

START STANDBY DATABASE

   SQL> startup mount

Option 1: Start Redo Apply

   

    To start Redo Apply, issue the following statement:

    SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE

      2> DISCONNECT FROM SESSION;

Option 2: Start Real-time Redo Apply

   SQL> alter database recover managed standby database using current logfile disconnect from session;

   NOTE: "using current logfile" causes redo to be applied as soon it is received

         "disconnect from session" makes the redo apply process run in the background.

   

TO STOP REDO APPLY

   SQL> alter database recover managed standby database cancel;  

             

   

   

SWITCHING STANDBY TO PRIMARY ROLE

1. Determine which is primary, the roles of the databases

select DATABASE_ROLE from V$DATABASE;

where DATABASE_ROLE values are:

         PRIMARY

         PHYSICAL STANDBY

         LOGICAL STANDBY

         SNAPSHOT STANDBY

         

 

2. Verify the primary database instance is open and the standby database instance is mounted.

select OPEN_MODE from V$DATABASE;

where OPEN_MODE values are:

         MOUNTED

         READ WRITE

         READ ONLY

         READ ONLY WITH APPLY (A physical standby database is open in real-time query mode)

         

3. Check that there are no active users connected to the primary database.

4. Force a redo switch and check that the last redo data transmitted from the Primary database was applied on the standby database.        

@Primary,

                 

SQL> alter system switch logfile;

System altered.

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

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

           185

           

@Standby,

SQL> select max(sequence#) from v$log_history;

MAX(SEQUENCE#)

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

           185           

           

           

           

5. Demote the PRIMARY database

SQL> connect SYS@ORA10DG1 as sysdba

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

6. Convert STANDBY database to PRIMARY (Do this only when switchover of primary db was successful)

Optional pre-steps: shutdown the application listener if there is one.

At Standby Server,

SQL>connect SYS as sysdba

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

At Primary server,

SQL>SHUTDOWN IMMEDIATE;

SQL>STARTUP MOUNT;

SQL> alter database recover managed standby database using current logfile disconnect from session;

7. Open the database.

- If you are using Oracle Database 10g release 1, you will have to Shut down and restart the new primary database ORA10DG1SB.

SQL>SHUTDOWN IMMEDIATE;

SQL>STARTUP;

- If you are using Oracle Database 10g release 2, you can open the new Primary database ORA10DG1SB:

SQL>ALTER DATABASE OPEN;

ORA11DG1SB is now transitioned to the primary database role.

8. On the new primary database ORA10DG1SB, perform a SWITCH LOGFILE to start sending redo data to the standby database ORA10DG1.

SQL>ALTER SYSTEM SWITCH LOGFILE;

TO OPEN A STANDBY DATABASE FOR READ-ONLY ACCESS

    Cancel Redo Apply:

    SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

    Optional: If audit_trail parameter was enabled (TRUE), you need to disable it:

              SQL> ALTER SYSTEM SET audit_trail='NONE' SCOPE=SPFILE;

              

              You need to restart the database for changes to take effect.

              SQL>SHUTDOWN IMMEDIATE;

              SQL>STARTUP;

    Open the database for read-only access:

    SQL> ALTER DATABASE OPEN;

NOTE: No need to shut down the instance to open it for read-only access.

By default, the ALTER DATABASE OPEN statement opens physical standby databases in read-only mode.

The Oracle database determines if this is a physical standby database based on information in the

control file (which currently is using a standby control file created from the primary).

If the standby database is currently shutdown, you may open it in READ-ONLY by just starting it up.

SQL> STARTUP;

TO RESUME REDO-APPLY FROM STANDBY READ-ONLY

1.  Terminate all active user sessions on the standby database.

2.  Optional: If audit_trail is enabled at the Primary database, you need to enabled it also in standby:

    SQL> ALTER SYSTEM SET audit_trail='TRUE' SCOPE=SPFILE;

 

    Start the database in mount mode

    SQL>SHUTDOWN IMMEDIATE;

    SQL>STARTUP MOUNT;

3.  Restart Redo Apply.

    To start Redo Apply, issue the following statement:

    SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE

      2> DISCONNECT FROM SESSION;

    To enable real-time apply, include the USING CURRENT LOGFILE clause:

    SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE

      2> USING CURRENT LOGFILE;

NOTE: If there’s no audit_trail required, No need to shut down the instance to start either of these apply modes.

MEASURING THE APPLY RATE (Physical Standby only)

select to_char(START_TIME, 'YYYY-MON-DD HH24:MI:SS') apply_start_time, ITEM, SOFAR

from V$RECOVERY_PROGRESS

where item in ('Active Apply Rate', 'Redo Applied');

NOTE:

Active Apply Rate = (kb/s) average rate of redo applied in 3 minutes time.

                    To get the MB/s, ActiveApplyRate/1024.

Redo Applied = (MB) total size of redo applied.

 

TO CHECK LAG TIME OF TRANSPORT AND APPLY

column NAME format a15

column VALUE format a20

column UNIT format a30

select NAME, VALUE, UNIT, TIME_COMPUTED

from V$DATAGUARD_STATS

where name in ('transport lag','apply lag');

TO CHECK HOW MANY ARCHIVE LOGS BEHIND IS THE REDO APPLY

At Standby,

select max(SEQUENCE#) as "STANDBY ARC LOG SEQ#", APPLIED

from V$ARCHIVED_LOG

group by APPLIED;

ABOUT ORACLE ACTIVE DATA GUARD LICENSE

From Oracle Documentation:

A physical standby database can be opened for read-only access and used to offload queries from a primary database.

If a license for the Oracle Active Data Guard option has been purchased, a physical standby database can be open while redo apply is active. This capability is known as real-time query. See Section 9.2.1 for more details.

If a license for the Oracle Active Data Guard option has not been purchased, a physical standby database cannot be open while redo apply is active, so the following rules must be observed when opening a physical standby database instance or starting redo apply:

    a) Redo apply must be stopped before any physical standby database instance is opened.

    b) If one or more physical standby instances are open, those instances must be closed before starting redo apply.

Note:

The SET TRANSACTION READ ONLY SQL statement must be executed before performing a distributed query on a physical standby database.