ORACLE - Active Data Guard Enable/Disable

======================================================================
Open Read only mode - Enable Active data guard :  Using SQL Plus or Broker (11gR1) But for
11gR2 : alter database open read only; is replace folloing 3 steps.
======================================================================
1) Stop the redo apply:
DGMGRL> edit database 'ATEST_STBY' set state='APPLY-OFF';
DGMGRL> show database verbose 'ATEST_STBY'
Database - ATEST_STBY
  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-OFF
  Transport Lag:   0 seconds
  Apply Lag:       9 seconds
  Real Time Query: OFF
  Instance(s):    ATEST

2. Open the database as read-only
sql> alter database open read only;
SELECT database_role, open_mode FROM v$database;
DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY WITH APPLY

3.Restart the redo apply:
DGMGRL> edit database ATEST_STBY set state='APPLY-ON';
show database verbose 'ATEST_STBY'
Database - ATEST_STBY
  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: ON
  Instance(s):     ATEST

Note: 11gR2 : alter database open read only; is replaced avove 3 steps.
======================================================================
Using SQL PLUS : Enable Active data guard
======================================================================
1. Stop the redo and open the database as read-only :
recover managed standby database cancel;
alter database open read only;

2.Restart the redo apply:
recover managed standby database disconnect using current logfile;

==================================================================
Disable Active Data Guard
==================================================================
SQL> SELECT database_role, open_mode FROM v$database;
DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY WITH APPLY

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.
Total System Global Area 1043886080 bytes
Fixed Size                  2234960 bytes
Variable Size             729810352 bytes
Database Buffers          306184192 bytes
Redo Buffers                5656576 bytes
Database mounted.

SQL> alter database recover managed standby database using current logfile disconnect;
alter database recover managed standby database using current logfile disconnect
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active

DGMGRL> show database verbose 'ATEST_STBY'
Database - ATEST_STBY
  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: OFF
  Instance(s):    ATEST

================================================================
If DGMGRL setup: Shutdown and STARTUP will open with read only REDO APPLY
===============================================================
SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1043886080 bytes
Fixed Size                  2234960 bytes
Variable Size             729810352 bytes
Database Buffers          306184192 bytes
Redo Buffers                5656576 bytes
Database mounted.
Database opened.

SQL> SELECT OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, DATABASE_ROLE ROLE FROM V$DATABASE;
OPEN_MODE            PROTECTION_MODE      PROTECTION_LEVEL     ROLE
-------------------- -------------------- -------------------- ----------------
READ ONLY WITH APPLY MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  PHYSICAL STANDBY

SQL>select * from jtable;
1000

Using DGMGRL:
DGMGRL> show database verbose 'ATEST_STBY';
Database - ATEST_STBY
  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: ON
  Instance(s):   ATEST

=========================================================
If DGMGRL setup: Shutdown and STARTUP MOUNT
=========================================================
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1043886080 bytes
Fixed Size                  2234960 bytes
Variable Size             729810352 bytes
Database Buffers          306184192 bytes
Redo Buffers                5656576 bytes
Database mounted.
SQL> SELECT OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, DATABASE_ROLE ROLE FROM V$DATABASE;
OPEN_MODE            PROTECTION_MODE      PROTECTION_LEVEL     ROLE
-------------------- -------------------- -------------------- ----------------
MOUNTED              MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  PHYSICAL STANDBY
SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY  ;
PROCESS   STATUS
--------- ------------
ARCH      CONNECTED
ARCH      CONNECTED
ARCH      CONNECTED
ARCH      CONNECTED
RFS       IDLE
RFS       IDLE
6 rows selected.

SQL> select * from jtable2;
select * from jtable
              *
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only
Using DGMGRL:
DGMGRL> show database verbose 'ATEST_STBY';
Database - ATEST_STBY
  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: OFF
  Instance(s):    ATEST

=====================================================================
11gR2 : alter database open read only will make DG READONLY with APPLY
=====================================================================
SQL>  alter database open read only;
Database altered.
SQL> SELECT OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, DATABASE_ROLE ROLE FROM V$DATABASE;
OPEN_MODE            PROTECTION_MODE      PROTECTION_LEVEL     ROLE
-------------------- -------------------- -------------------- ----------------
READ ONLY            MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  PHYSICAL STANDBY
SQL> SELECT OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, DATABASE_ROLE ROLE FROM V$DATABASE;
OPEN_MODE            PROTECTION_MODE      PROTECTION_LEVEL     ROLE
-------------------- -------------------- -------------------- ----------------
READ ONLY WITH APPLY MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  PHYSICAL STANDBY
SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY  ;
PROCESS   STATUS
--------- ------------
ARCH      CONNECTED
ARCH      CONNECTED
ARCH      CONNECTED
ARCH      CLOSING
RFS       IDLE
RFS       IDLE
MRP0      APPLYING_LOG
RFS       IDLE
8 rows selected.
Using DGMGRL:
show database verbose 'ATEST_STBY';
Database - ATEST_STBY
  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: ON
  Instance(s):   ATEST

=================================================================