Oracle – Temporarily disabling the log shipping to standby database

Temporarily disabling the log shipping to standby database

$ sqlplus

SQL*Plus: Release 10.2.0.3.0 – Production on Wed Jul 25 21:07:57 2012

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Enter user-name: /as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> select name,open_mode from v$database;

NAME OPEN_MODE
——— ———-
AIRMAN READ WRITE

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

MAX(SEQUENCE#)
————–
8941

SQL> alter system switch logfile;

System altered.

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

MAX(SEQUENCE#)
————–
8942

SQL> select status, DEST_NAME, DESTINATION from v$archive_dest where status = ‘VALID’;

STATUS
———
DEST_NAME
——————————————————————————–
DESTINATION
——————————————————————————–
VALID
LOG_ARCHIVE_DEST_1
/u01/oradata/airman/archive

VALID
LOG_ARCHIVE_DEST_2
airman_sj

STATUS
———
DEST_NAME
——————————————————————————–
DESTINATION
——————————————————————————–

SQL> show parameter LOG_ARCHIVE_DEST_2

NAME TYPE VALUE
———————————— ———– ——————————
log_archive_dest_2 string SERVICE=airman_sj LGWR ASYNC V
ALID_FOR=(ONLINE_LOGFILE, PRIM
ARY_ROLE) db_unique_name=airma
n_sj

SQL> show parameter log_archive

NAME TYPE VALUE
———————————— ———– ——————————
log_archive_config string DG_CONFIG=(airman_kj,airman_sj
)
log_archive_dest string
log_archive_dest_1 string LOCATION=/u01/oradata/airman/a
rchive VALID_FOR=(ALL_LOGFILES
, ALL_ROLES) db_unique_name=ai
rman_kj
log_archive_dest_10 string
log_archive_dest_2 string SERVICE=airman_sj LGWR ASYNC V
ALID_FOR=(ONLINE_LOGFILE, PRIM
ARY_ROLE) db_unique_name=airma

NAME TYPE VALUE
———————————— ———– ——————————
n_sj
log_archive_dest_3 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
log_archive_dest_9 string
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_2 string ENABLE
log_archive_dest_state_3 string enable
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
log_archive_duplex_dest string
log_archive_format string %t_%s_%r.arc
log_archive_local_first boolean TRUE
log_archive_max_processes integer 2
log_archive_min_succeed_dest integer 1
log_archive_start boolean FALSE
log_archive_trace integer 0

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

System altered.

SQL> show parameter log_archive_dest_state_2

NAME TYPE VALUE
———————————— ———– ——————————
log_archive_dest_state_2 string DEFER

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

MAX(SEQUENCE#)
————–
8942

SQL> alter system switch logfile;

System altered.

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

MAX(SEQUENCE#)
————–
8943

SQL> alter system switch logfile;

System altered.

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

MAX(SEQUENCE#)
————–
8944

SQL> alter system switch logfile;

System altered.

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

MAX(SEQUENCE#)
————–
8945

Advertisements

About shanojkumar

* Has 12 years of experience in Planning, Implementing, Managing and Monitoring the Oracle Databases as an (Oracle 10g & 11g OCP) Certified Oracle Database Administrator.
This entry was posted in Oracle and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s