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
                                                e)
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                                          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                                          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
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

Comments