Oracle dataguard Standby setup step by step

dataguard :
-----------

  --> datagurad mainly used for the diaster recover.
 
  --> the primary must be

             force_logging=yes

    sql> select force_logging from v$database;
    sql> alter database force logging;

  --> in oracle 11g it will be opened in read only mode for reporting purpose.

  --> it includes
  
          -- primary database
      
          -- standby database
  
  --> stand by types

      -- physical standby
        -- recover will be done using logfiles.

      -- logical standby
        -- uisng log miner it generates sql statements and execute it.
        -- we can use it for particular schema also
        -- we can use anohter database as logical standby
        -- we can open it as read write mode

      -- snapshot standby
        -- we can open read write mode and do falshback operations also.

  --> standby modes

      -- maximum performance
        -- in this first commit the transaction in primary  later on standby.
        -- so in this there may be a data loss but the performance is good.

      -- maximum protection
        -- in this commit the transaction in both primary and standby so there is no data loss
        -- but the performance is not good
        -- if any network problem araise then it shutdown  the primary also.

      -- maximum availability
        -- in this commit the transaction in both
        -- if any network problem it cant shutdown the  primary
        -- performace issue.


--> creating a standby database using rman


    primary database : testdb
     standby database : standdb


step1 : create pfile for stand using the spfile of testdb

    sql> create pfile='initstanddb.ora' from spfile;

    sql> ALTER SYSTEM SET log_archive_config = 'dg_config=(testdb,standdb)';
    sql> alter system set STANDBY_FILE_MANAGEMENT='auto';

step2 : alter the pfiles of testdb and stand as follows


step3 : create the password file for stand and passowrd should     be same as testdb nad spfile for stand

step4 : start the testdb and stand in nomount

        testdd -- sql> startup
        stand  -- sql> strtup nomount

step5 : connect to the rman

[oracle@server1 dbs]$ rman target sys/proddb@testdb auxiliary sys/proddb@standdb

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Jun 27 13:57:19 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TESTDB (DBID=2567442324)
connected to auxiliary database: TESTDB (not mounted)

RMAN> duplicate target database for standby from active database nofilenamecheck;

Starting Duplicate Db at 27-JUN-12
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=17 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u01/app/product/11.2.0/dbhome_1/dbs/orapwtestdb' auxiliary format
 '/u01/app/product/11.2.0/dbhome_1/dbs/orapwstanddb'   ;
}
executing Memory Script

Starting backup at 27-JUN-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=79 device type=DISK
Finished backup at 27-JUN-12

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '/u01/app/oradata/stand/controlfile1.ctl';
}
executing Memory Script

Starting backup at 27-JUN-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/product/11.2.0/dbhome_1/dbs/snapcf_testdb.f tag=TAG20120627T140031 RECID=15 STAMP=787068034
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 27-JUN-12

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database
Using previous duplicated file /u01/app/oradata/stand/system01.dbf for datafile 1 with checkpoint SCN of 700576
Using previous duplicated file /u01/app/oradata/stand/sysaux01.dbf for datafile 2 with checkpoint SCN of 700642
Using previous duplicated file /u01/app/oradata/stand/undotbs01.dbf for datafile 3 with checkpoint SCN of 700761
Using previous duplicated file /u01/app/oradata/stand/users01.dbf for datafile 4 with checkpoint SCN of 700473
Using previous duplicated file /u01/app/oradata/stand/rman01.dbf for datafile 5 with checkpoint SCN of 700802

contents of Memory Script:
{
   set newname for tempfile  1 to
 "/u01/app/oradata/stand/temp01.dbf";
   switch clone tempfile all;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oradata/stand/temp01.dbf in control file

sql statement: alter system archive log current

contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "/u01/app/testdb/arch/1_52_784114205.arc" auxiliary format
 "/u01/app/product/11.2.0/dbhome_1/dbs/arch1_52_784114205.arc"   ;
   catalog clone archivelog  "/u01/app/product/11.2.0/dbhome_1/dbs/arch1_52_784114205.arc";
   catalog clone datafilecopy  "/u01/app/oradata/stand/system01.dbf",
 "/u01/app/oradata/stand/sysaux01.dbf",
 "/u01/app/oradata/stand/undotbs01.dbf",
 "/u01/app/oradata/stand/users01.dbf",
 "/u01/app/oradata/stand/rman01.dbf";
   switch clone datafile  1 to datafilecopy
 "/u01/app/oradata/stand/system01.dbf";
   switch clone datafile  2 to datafilecopy
 "/u01/app/oradata/stand/sysaux01.dbf";
   switch clone datafile  3 to datafilecopy
 "/u01/app/oradata/stand/undotbs01.dbf";
   switch clone datafile  4 to datafilecopy
 "/u01/app/oradata/stand/users01.dbf";
   switch clone datafile  5 to datafilecopy
 "/u01/app/oradata/stand/rman01.dbf";
}
executing Memory Script

Starting backup at 27-JUN-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=52 RECID=71 STAMP=787068071
output file name=/u01/app/product/11.2.0/dbhome_1/dbs/arch1_52_784114205.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:25
Finished backup at 27-JUN-12

cataloged archived log
archived log file name=/u01/app/product/11.2.0/dbhome_1/dbs/arch1_52_784114205.arc RECID=1 STAMP=787068107

cataloged datafile copy
datafile copy file name=/u01/app/oradata/stand/system01.dbf RECID=15 STAMP=787068107
cataloged datafile copy
datafile copy file name=/u01/app/oradata/stand/sysaux01.dbf RECID=16 STAMP=787068107
cataloged datafile copy
datafile copy file name=/u01/app/oradata/stand/undotbs01.dbf RECID=17 STAMP=787068107
cataloged datafile copy
datafile copy file name=/u01/app/oradata/stand/users01.dbf RECID=18 STAMP=787068107
cataloged datafile copy
datafile copy file name=/u01/app/oradata/stand/rman01.dbf RECID=19 STAMP=787068107

datafile 1 switched to datafile copy
input datafile copy RECID=15 STAMP=787068107 file name=/u01/app/oradata/stand/system01.dbf

datafile 2 switched to datafile copy
input datafile copy RECID=16 STAMP=787068107 file name=/u01/app/oradata/stand/sysaux01.dbf

datafile 3 switched to datafile copy
input datafile copy RECID=17 STAMP=787068107 file name=/u01/app/oradata/stand/undotbs01.dbf

datafile 4 switched to datafile copy
input datafile copy RECID=18 STAMP=787068107 file name=/u01/app/oradata/stand/users01.dbf

datafile 5 switched to datafile copy
input datafile copy RECID=19 STAMP=787068107 file name=/u01/app/oradata/stand/rman01.dbf
Finished Duplicate Db at 27-JUN-12


step6 : finally standby created and in mount

sql> select open_mode,switchover_status,database_role from v$database;

        OPEN_MODE  SWITCHOVER_STATUS    DATABASE_ROLE
        ---------- -------------------- ----------------
        MOUNTED    TO PRIMARY           PHYSICAL STANDBY
     
step7 : Configure a Standby Redo Log

-- add the standby logifles for testdb and standdb

-- Ensure log file sizes are identical on the primary and standby database

on standdb

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


    GROUP#    THREAD# BYTES/1024/1024    MEMBERS
---------- ---------- --------------- ----------
         1          1             100          1
         3          1             100          1
         2          1             100          1

sql> alter database add standby logfile '/u01/app/oradata/stand/redo04.log' size 100m;


sql> alter database add standby logfile '/u01/app/oradata/stand/redo05.log' size 100m;

sql> alter database add standby logfile '/u01/app/oradata/stand/redo06.log' size 100m;

-- Verify the standby redo log file groups were created

SQL> select group#,thread#,sequence#,archived,status from v$standby_log;

    GROUP#    THREAD#  SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------
         4          0          0 YES UNASSIGNED
         5          0          0 YES UNASSIGNED
         6          0          0 YES UNASSIGNED

on testddb

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


    GROUP#    THREAD# BYTES/1024/1024    MEMBERS
---------- ---------- --------------- ----------
         1          1             100          1
         3          1             100          1
         2          1             100          1

sql> alter database add standby logfile '/u01/app/oradata/testdb/redo04.log' size 100m;


sql> alter database add standby logfile '/u01/app/oradata/testdb/redo05.log' size 100m;

sql> alter database add standby logfile '/u01/app/oradata/testdb/redo06.log' size 100m;

-- Verify the standby redo log file groups were created

SQL> select group#,thread#,sequence#,archived,status from v$standby_log;

    GROUP#    THREAD#  SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------
         4          0          0 YES UNASSIGNED
         5          0          0 YES UNASSIGNED
         6          0          0 YES UNASSIGNED



--> enable the recover(standdb)

       sql> alter database recover managed standby database disconnect from session using current logfile;

--> for switch primary to standby

       sql> alter database commit to switchover to physical standby;

--> for switch standby to primary

       sql> alter database commit to switchover to primary;

--> checking archives in dest 2(standby)

    sql> select * from v$archive_dest where dest_id=2;


--> for checking dataguard status

    sql> select * from v$dataguard_status;

vb script password=inputbox("enter Password ") x="report1.bat " &password msgbox(x) dim shell set shell=createobject("wscript.shell") shell.run x set shell=nothing

chandu's oracle blog link

http://chandu208.blogspot.in/2011/04/rman-question-answers.html

Kamran Agayev's Oracle blog link

http://kamranagayev.com/

SQL/ PL-SQL OTN link

https://forums.oracle.com/forums/forum.jspa?forumID=75

pavan dba blog

http://pavandba.com/

oracle 11g documentation link

http://www.oracle.com/pls/db111/homepage