Active data guard in 11g - How to activate "Active Data guard" feature in 11g.

How to activate Active Data guard feature in 11g.

Here I am converting an existing physical standby database to Read only standby database with real time redo apply.

Given you have a UP and running physical standby if not please follow the below Note:
Step by Step Guide on Creating Physical Standby Using RMAN DUPLICATE...FROM ACTIVE DATABASE [ID 1075908.1]

Standby Redo Logs - what are they and when to use them ??
--------------------------------------------------------------------------

Starting Oracle 9i you have to opportunity to add Standby Rodo Log Groups to
your Online Redo Log Groups. These Standby Redo Logs then store the information
received from the Primary Database. In case of a Failover situation, you will
have less data loss than without Standby Redo Logs.

Standby Redo Logs are only supported for the Physical Standby Database in
Oracle 9i and as well for Logical Standby Databases in 10g. Standby Redo Logs
are only used if you have the LGWR activated for archival to the Remote Standby
Database.

The great Advantage of Standby Redo Logs is that every Entry written into
the Online RedoLogs of the Primary Database is transfered to the Standby
Site and written into the Standby Redo Logs at the same time; threfore, you
reduce the probability of Data Loss on the Standby Database.

Starting with 10g it is possible to start Real-Time Apply with Physical and
Logical Standby Databases. With Real-Time Apply, the Redo is applied to the
Strandby Database from the Standby RedoLog instead of waiting until an Archive
Log is created. So Standby Redo Logs are required for Real-Time Apply.


On Primary:

SQL> show parameter unique

NAME                  PRODX

It is important you have the standby logfile size at a minimum of the primary redo logfile size.

SQL> SELECT distinct(to_char((bytes*0.000001),'9990.999')) size_mb
FROM v$log; 

SIZE_MB
---------
  262.144



SQL> select group#,thread#,members from v$log;

    GROUP#    THREAD#    MEMBERS
---------- ---------- ----------
         1          1          2
         3          1          2
         2          1          2


On Standby:


QL> show parameter unique

NAME                  STANDX

Cancel the recovery:

SQL> alter database recover managed standby database cancel;

Database altered.

Add the standby redo logfile groups: ( 4-6 here )

SQL> alter database add standby logfile group 4 ('/u01/u0090/oradata/STANDX/stndby_redo_1a.log') size 265m;

Database altered.

SQL> alter database add standby logfile group 5 ('/u01/u0091/oradata/STANDX/stndby_redo_2a.log') size 265m;

Database altered.

SQL> alter database add standby logfile group 6('/u01/u0092/oradata/STANDX/stndby_redo_3a.log') size 265m;

Database altered.

Now add the second member to the above groups if would like or add these while creating the groups itself:


SQL> alter database add standby logfile member '/u01/u0092/oradata/STANDX/stndby_redo_1b.log' reuse to group 4;

Database altered.

SQL>  alter database add standby logfile member '/u01/u0091/oradata/STANDX/stndby_redo_3b.log' reuse to group 6;

Database altered.

SQL> alter database add standby logfile member '/u01/u0090/oradata/STANDX/stndby_redo_2b.log' reuse to group 5;

Database altered.

Now open the standby database in Read only mode:

SQL> shut immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area 2990735360 bytes
Fixed Size                  2106176 bytes
Variable Size             872422592 bytes
Database Buffers         2097152000 bytes
Redo Buffers               19054592 bytes
Database mounted.
Database opened.
SQL>


Now start the media recovery on the standby database:

SQL>  recover managed standby database using current logfile disconnect;
Media recovery complete.


Example to make sure we can use the standby database:
Here the standby is waiting for the logseq 5865#

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

PROCESS   STATUS     THREAD#   SEQUENCE#

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

ARCH     CONNECTED      0          0

ARCH     CONNECTED      0          0

ARCH     CONNECTED      0          0

ARCH     CONNECTED      0          0

MRP0     WAIT_FOR_GAP   1       5865

RFS      IDLE           0          0

6 rows selected.




On Primary current sequence is 5865#

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /crm01/u0085/oradata/HRCRMPRD
Oldest online log sequence     5863
Next log sequence to archive   5865
Current log sequence           5865

Create a sample user on primary, create some test tables and then switch the logfiles to reflect the changes at the standby.

SQL> create user myuser identified by standbytest ;

User created.

SQL> grant dba to myuser;

Grant succeeded.

SQL> GRANT CONNECT TO myuser;

Grant succeeded.

SQL> exit


Connect as the new user on Primary:

SQL>sqlplus myuser/standbytest

SQL*Plus: Release 11.1.0.7.0 - Production on Sun Sep 16 02:19:40 2012


SQL> create table readonly_standby as select  * from dba_tables;

Table created.

SQL> select count(*) from readonly_standby;

  COUNT(*)
----------
      8511

SQL> create table index_dba as select * from dba_indexes;

Table created.

SQL> select count(*) from index_dba;

  COUNT(*)
----------
     12423

SQL> commit;

Commit complete.

Now switch the log sequence:

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

On Standby the 5865 sequence is being applied now:

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

PROCESS   STATUS      THREAD# SEQUENCE#

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

ARCH     CLOSING        1       5868

ARCH     CONNECTED      0          0

ARCH     CLOSING        1       5866

ARCH     CLOSING        1       5867

MRP0     APPLYING_LOG   1       5865

RFS      IDLE           0          0

RFS      IDLE           1       5865

RFS      IDLE           0          0

8 rows selected.

PROCESS   STATUS      THREAD# SEQUENCE#

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

ARCH     CLOSING        1       5868

ARCH     CONNECTED      0          0

ARCH     CLOSING        1       5865

ARCH     CLOSING        1       5867

MRP0     WAIT_FOR_GAP   1       5869

RFS      IDLE           0          0

RFS      IDLE           0          0

RFS      IDLE           0          0

8 rows selected.

Verify if the got created or not:


SQL> select username,account_status,created from dba_users where username='myuser';

USERNAME ACCOUNT_STATUS CREATED

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

Myuser   OPEN           08-JUL-14

       

SQL>Select owner,table_name from dba_tables where OWNER='myuser';

OWNER    TABLE_NAME

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

Myuser   READONLY_STANDBY

Myuser   INDEX_DBA