Shutdown Standby Database for maintenance work

Shutdown Standby Database:

-- step 1: Disable standby archive writing:

-- In primary database

SQL> show parameter log_archive_dest_3;

NAME TYPE VALUE

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

log_archive_dest_3 string service="ipxtest", LGWR ASYNC

NOAFFIRM delay=0 optional comp

ression=disable max_failure=0

max_connections=1 reopen=300 d

b_unique_name="ipxtest", valid

_for=(all_logfiles,primary_rol

SQL> show parameter log_archive_dest_state_3;

NAME TYPE VALUE

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

log_archive_dest_state_3 string ENABLE

SQL> alter system set log_archive_dest_state_3=defer scope = both;

System altered.

SQL> show parameter log_archive_dest_state_3;

NAME TYPE VALUE

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

log_archive_dest_state_3 string DEFER

-- step 2: Disable auto recovery at standby site:

-- In Standby database

SQL> select process, status, thread#, sequence#, block#,blocks from v$managed_standby;

PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS

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

ARCH CLOSING 1 3288 3635200 300

ARCH CLOSING 1 3287 3682304 1745

ARCH CONNECTED 0 0 0 0

ARCH CLOSING 1 3289 28672 1614

RFS IDLE 0 0 0 0

RFS IDLE 1 3290 13690 1

MRP0 APPLYING_LOG 1 3290 13689 4194304

8 rows selected.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> select process, status, thread#, sequence#, block#,blocks from v$managed_standby;

PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS

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

ARCH CLOSING 1 3288 3635200 300

ARCH CLOSING 1 3287 3682304 1745

ARCH CONNECTED 0 0 0 0

ARCH CLOSING 1 3289 28672 1614

RFS IDLE 0 0 0 0

RFS IDLE 1 3290 15030 2

7 rows selected.

SQL> shutdown immediate

-- STARTUP STANDBY DATABASE

-- Step 1: Coping all archive from primary to DR generated during shutdown.

in this step we will copy all archive those were generated after standby database was taken down.

$scp /arhive_log_location/*.arc oracle@DR-Site-IP:/ arhive_log_location

-- Step 2: Startup standby database and configure automatic recovery:

SQL> startup nomount

SQL> alter database mount standby database;

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

Database altered.

SQL> select process, status, thread#, sequence#, block#,blocks from v$managed_standby;

PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS

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

ARCH CLOSING 1 3288 3635200 300

ARCH CLOSING 1 3287 3682304 1745

ARCH CONNECTED 0 0 0 0

ARCH CLOSING 1 3290 16384 565

RFS IDLE 0 0 0 0

MRP0 WAIT_FOR_LOG 1 3291 0 0

7 rows selected.

-- step 3: Enable standby archive writing:

In primary database

SQL> alter system set log_archive_dest_state_3=enable scope=both;

System altered.

SQL> show parameter log_archive_dest_state_3;

NAME TYPE VALUE

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

log_archive_dest_state_3 string ENABLE