Pavan DBA's Blog

The DBA Knowledge Store

Configuration of Oracle 10g Data guard

Posted by Pavan DBA on December 28, 2009


Primary Database Requirements for Data Guard:

FORCE LOGGING must be enabled:

SQL> select force_logging from v$database;
SQL> alter database force logging;
ARCHIVELOG mode and automatic archiving must be enabled:
SQL> archive log list
MAXLOGFILES >= (2 * Current Redo Log Groups) + 1:
SQL> select  records_used “Current Groups”,records_total “Max Groups” from v$controlfile_record_section  where type = ‘REDO LOG’;
listener.ora Additions:

Define the standby database SID on the standby site:
(SID_DESC=
 (SID_NAME=PROD2)
 (ORACLE_HOME=/pgms/oracle/product/v9204)
)
(in $ORACLE_HOME/network/admin/listener.ora)
tnsnames.ora Additions:

Define the standby database connect string on the primary site:
myserver_prod2 =
   (DESCRIPTION =
   (ADDRESS_LIST =
       (ADDRESS =
         (PROTOCOL = TCP)
         (Host = 123.45.67.89) — whatever host IP has PROD2
         (Port = 1521)
       )
   )
   (CONNECT_DATA = (SID = PROD2)
   )
 )
(define myserver_prod and myserver_prod2 on both
primary and standby sites for quick switchovers)

sqlnet.ora and /etc/oratab Additions:

Enable dead connection detection on the primary and standby sites:
sqlnet.expire_time=2
 (in $ORACLE_HOME/network/admin/sqlnet.ora)
Add the standby database’s entry to /etc/oratab on the standby site:
PROD2:/pgms/oracle/product/v9204:N
Standby Database Parameter File:

Create the initPROD2.ora parameter file to be used for the standby database (done from primary database):
If your primary is using an spfile:
 sqlplus “/ as sysdba”
SQL> create pfile=’$ORACLE_HOME/dbs/initPROD2.ora’from spfile;
Else, if your primary is using a pfile:
cp -p $ORACLE_HOME/dbs/initPROD.ora 
$ORACLE_HOME/dbs/initPROD2.ora
Note: We will be modifying both the primary and standby parameter files to handle being in either the primary or the standby mode for quick switchovers.
Standby Database Parameters (changes in copy of primary’s values):

Change pathnames, such as control_files, background_dump_dest,
core_dump_dest, user_dump_dest, and audit_file_dest, and add:
# log_archive_dest = /orcl/oradata/PROD2/archivelogs
log_archive_dest_1 = ‘LOCATION=/orcl/oradata/PROD2/archivelogs MANDATORY’  # for switchover
log_archive_dest_state_1 = ENABLE  # for switchover
log_archive_dest_2 = ‘SERVICE=myserver_prod LGWR SYNC’  # for switchover
log_archive_dest_state_2 = ENABLE  # for switchover
standby_archive_dest = /orcl/oradata/PROD2/archivelogs
standby_file_management = AUTO  # or MANUAL for raw devices
remote_archive_enable = TRUE  # TRUE or RECEIVE, change RECEIVE to SEND on switchover
instance_name = PROD2
lock_name_space = PROD2  # use when primary and standby on same system; same as instance_name
fal_server = myserver_prod  # “fal” is Fetch Archive Log, for log gap resolution
fal_client = myserver_prod2
db_file_name_convert = (‘/PROD/’,’/PROD2/’)
log_file_name_convert = (‘/PROD/’,’/PROD2/’)

Primary Database Parameters (changes in primary’s values):

#log_archive_dest = /orcl/oradata/PROD/archivelogs
log_archive_dest_1 = ‘LOCATION=/orcl/oradata/PROD/archivelogs MANDATORY’
log_archive_dest_state_1 = ENABLE
log_archive_dest_2 = ‘SERVICE=myserver_prod2 LGWR SYNC’
log_archive_dest_state_2 = ENABLE
standby_archive_dest = /orcl/oradata/PROD/archivelogs  # for switchover
standby_file_management = AUTO  # for switchover; or MANUAL for raw devices
remote_archive_enable = TRUE  # TRUE or SEND, change SEND to RECEIVE on switchover
instance_name = PROD
lock_name_space = PROD  # use when primary and standby on same system; same as instance_name
fal_server = myserver_prod2  # for switchover
fal_client = myserver_prod  # for switchover
db_file_name_convert = (‘/PROD2/’,’/PROD/’)  # for switchover
log_file_name_convert = (‘/PROD2/’,’/PROD/’)  # for switchover
(If primary uses spfile, wait until after the standby database
files are copied/created to make these parameter changes.)
Standby Database Datafiles, etc.

Create the standby control file from the primary database:
SQL> alter database create standby controlfile as ‘/orcl/oradata/PROD2/ctrl_PROD_01.ctl’;
Shut down the primary database and copy or FTP its datafiles, redo log files, and the just-created standby parameter file and standby control file, to the standby site.
Copy the standby control file on the standby site to the other file names listed in the control_files init.ora parameter.
Create the standby’s password file, if needed, on the standby site:
$ orapwd file=$ORACLE_HOME/dbs/orapwPROD2  password=<sys password> entries=5
Reload the listener on the primary and standby sites:
$ lsnrctl reload
Standby Database Startup:

Start the standby database in nomount mode, create the spfile if wanted, mount the standby database, and change to managed recovery:
$ . oraenv   PROD2
$ sqlplus “/ as sysdba”
SQL> create spfile from pfile;
SQL> startup nomount
SQL> alter database mount standby database;
SQL> alter database recover managed standby database disconnect from session;
SQL> exit
Primary Database Startup:

If your primary is using an spfile, set the primary database parameters in the spfile as listed earlier. 
Sample “alter system” commands are shown below:

SQL> startup nomount
SQL> alter system reset log_archive_dest scope=spfile sid=’*’;
SQL> alter system set log_archive_dest_1 = ‘LOCATION=/orcl/oradata/PROD/archivelogs MANDATORY’ scope=spfile;… etc
SQL> shutdown
Start up the primary database with the new parameters:
SQL> startup
Start archiving to the standby database by issuing a log switch:
SQL> alter system switch logfile;
Congratulations!!!  You now have a working standby database for your primary database
Add Standby Redo Log Groups to Standby Database

Create standby redo log groups on standby database (start with next group number; create one more group than current number of groups) after switching out of managed recovery mode:
SQL> sqlplus “/ as sysdba”
SQL> alter database recover managed standby database cancel;
SQL> alter database open read only;
SQL> select max(group#) maxgroup from v$logfile;
SQL> select max(bytes) / 1024 “size (K)” from v$log;
SQL> alter database add standby logfile group 4 (‘/orcl/oradata/PROD2/stby_log_PROD_4A.rdo’,’/orcl/oradata/PROD2/stby_log_PROD_4B.rdo’) size 4096K; … etc …
SQL> column member format a55
SQL> select vs.group#,vs.bytes,vl.member from v$standby_log vs,v$logfile vl where vs.group# = vl.group# order by vs.group#,vl.member;

Add Tempfile To Standby
Add a tempfile to the standby database for switchover or read-only access, then, switch back to managed recovery:
SQL> alter tablespace temp add tempfile ‘/data/oradata/PROD2/temp_PROD_01.dbf’ size 400064K reuse;
SQL> alter database recover managed standby database disconnect from session;
SQL> select * from v$tempfile;
SQL> exit
Add Standby Redo Log Groups to Primary Database
Create standby logfile groups on the primary database for switchovers (start with next group number; create one more group than current number of groups):
$ sqlplus “/ as sysdba”
SQL> select max(group#) maxgroup from v$logfile;
SQL> select max(bytes) / 1024 “size (K)” from v$log;
SQL> alter database add standby logfile group 4 (‘/orcl/oradata/PROD/stby_log_PROD_4A.rdo’, ‘/orcl/oradata/PROD/stby_log_PROD_4B.rdo’) size 4096K; … etc …
SQL> column member format a55
SQL> select vs.group#,vs.bytes,vl.member from v$standby_log vs, v$logfile vl where vs.group# = vl.group# order by vs.group#,vl.member;

Switch To Maximum Availability Protection Mode
Switch to the desired “maximum availability” protection mode on the primary database (from the default “maximum performance”):
SQL> select value from v$parameter where name = ‘log_archive_dest_2’;  — must show LGWR SYNC
SQL> shutdown normal
SQL> startup mount
SQL> alter database set standby database to maximize availability;
SQL> alter database open;
SQL> select protection_mode from v$database;
Test Updates Propagating to Standby
Try some edits on the primary and check to see that the changes made it to the standby:
On the primary:
SQL> update spriden set spriden_first_name = ‘James’ where spriden_pidm = 1234 and spriden_change_ind is null;
SQL> commit;
SQL> alter system switch logfile;
On the standby (wait a few seconds first):
SQL> alter database recover managed standby database cancel;
SQL> alter database open read only;
SQL> select * from spriden where spriden_pidm = 1234 and spriden_change_ind is null;
SQL> alter database recover managed standby database disconnect from session;

Running Reports with a Standby
Set standby to Read Only to run reports:
SQL> alter database recover managed standby database cancel;
SQL> alter database open read only;
SQL> @myreport.sql
SQL> alter database recover managed standby database disconnect from session;

Shutdown and Startup for Standby Database
To shut down a standby database:
If in read-only access, switch back to managed recovery (after terminating any other active sessions):
SQL> alter database recover managed standby database disconnect from session;
Cancel managed recovery and shutdown:
SQL> alter database recover managed standby database cancel;
SQL> shutdown immediate

To start up a standby database:
SQL> startup nomount
SQL> alter database mount standby database;
SQL> alter database recover managed standby database disconnect from session;
Switchover – Swapping Primary and Standby
End all activities on the primary and standby database.
On the primary (switchover status should show “TO STANDBY”):
SQL> select database_role,switchover_status from v$database;
SQL> alter database commit to switchover to physical standby;
SQL> shutdown immediate
SQL> startup nomount
SQL> alter database mount standby database;
On the standby (switchover status should show “SWITCHOVER PENDING”):
SQL> select database_role,switchover_status from v$database;
SQL> alter database commit to switchover to primary;
SQL> shutdown normal
SQL> startup
On the primary:SQL> alter database recover managed standby database disconnect from session;
On the standby:SQL> alter system archive log current;
Change tnsnames.ora entry on all servers to swap the connect strings (myserver_prod and myserver_prod2).
Failover – Standby Becomes Primary

End all activities on the standby database.
May need to resolve redo log gaps (not shown here).
On the standby: SQL> alter database recover managed standby database finish;
SQL> alter database commit to switchover to primary;
SQL> shutdown immediate
SQL> startup
Change tnsnames.ora entry on all servers to point the primary connect string to the standby database.
New standby needs to be created.  Old primary is no longer functional.
Monitoring Standby Database
select count(*) from v$archive_gap;  
This query detects gaps in the logs that have been received.  If any rows are returned by
this query then there is a gap in the sequence numbers of the logs that have been received.
This gap must be resolved before logs can be applied.

SELECT decode(count(*),0,0,1) FROM v$managed_standby WHERE (PROCESS=’ARCH’ AND STATUS NOT
IN (‘CONNECTED’)) OR (PROCESS=’MRP0′ AND STATUS NOT IN (‘WAIT_FOR_LOG’,’APPLYING_LOG’))
OR (PROCESS=’RFS’ AND STATUS NOT IN (‘IDLE’,’RECEIVING’));
This query detects bad statuses.  When a bad status is present this query will return a “1”.
The ‘ARCH’ process should always be ‘CONNECTED’.  The ‘MRP0’ process should always be waiting for a log or applying a log, and when this is not true it will report the error in the status. The ‘RFS’ process exists when the Primary is connected to the Standby and should always be ‘IDLE’ or ‘RECEIVING’.

SELECT DECODE(COUNT(DISTINCT PROCESS),3,0,1)
FROM v$managed_standby;
This query detects missing processes.  If we do not have exactly 3 distinct processes then there is a problem, and this query will return a “1”. 
The most likely process to be missing is the ‘RFS’ which is the connection to the Primary database. You must resolve the problem preventing the Primary from connecting to the Standby before this process will start running again.
Verify all STANDBY PROCESSES are running normally on the STANDBY database.

SELECT PROCESS,STATUS,RESETLOG_ID,SEQUENCE#,ACTIVE_AGENTS
FROM V$MANAGED_STANDBY ;
A query with good results follows proving all processes are connected
with normal statuses.

SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED
FROM V$ARCHIVED_LOG
WHERE FIRST_TIME > TRUNC(SYSDATE)
ORDER BY SEQUENCE#;

Data guard Related View
V$DATABASE
PROTECTION_LEVEL: current protection mode setting.
FS_FAILOVER_STATUS: synchronization status
DBA_LOGSTDBY_UNSUPPORTED: unsupported tables.
DBA_LOGSTDBY_EVENTS: monitor transaction activity.
V$LOG: Redo log changed.
V$MANAGED_STANDBY : Recovery progress.

9 Responses to “Configuration of Oracle 10g Data guard”

  1. rajaneesh said

    Hi pavan sir,

    This is Rajaneesh, i want to know about datagaurd setup in standard edition.gone through in my sites i couldn’t fine the exact steps.

    Thanks in advance,

    • Pavan DBA said

      Hi Rajaneesh, dataguard implementation is not possible in standard edition. It is only possible in licensed enterprise edition.

      • rajaneesh said

        somebody says it is configured manually.Is it true? If yes how to configure..

      • Pavan DBA said

        manually means, u need write shell scripts and achieve that task. for example, u need to write 2 shell scripts. one for transferring archives from primary to standby (this should run on primary). other script is to apply archives on standby (this script should run on standby).

        its a bit complicated task and also we cannot assure data availability in case primary crashes. rather u can suggest client to get enterprise license

  2. Munna said

    Hi Pavan,

    I wanna know the procedure(Roll Forward Mechanism) for applying the patch on logical standby configuration with zero or less down time.I am having 3 node RAC envi with 10.2.0.4.
    Need your help.

    –Munna

    • Munna said

      Any updates pavan……..

      • hi munna, got busy with so many projects. i will find sometime to dig into it. i don’t have any personal exp on this…

      • Munna said

        The below stuff may help….

        Rolling Patch – OPatch Support for RAC
        ————————————–

        1 – RAC Patching methods

        OPatch supports 3 different patch methods on a RAC environment:

        . Patching RAC as a single instance (All-Node Patch)

        In this mode, OPatch applies the patch to the local node first, then
        propagates the patch to all other nodes, and finally updates the inventory.
        All instances will be down during the whole patching process.

        · Patching RAC using a minimum down-time strategy (Min. Downtime Patch)

        In this mode, OPatch patches the local node, asks users for a sub-set of
        nodes, which will be the first nodes to be patched. After the initial
        subset of nodes are patched, Opatch propagates the patch to the other nodes
        and finally updates the inventory. The downtime would happen between the
        shutdown of the the second subset of nodes and the startp of the initial
        subset of nodes patched.

        · Patching RAC using a rolling strategy – No down time (Rolling Patch)

        With this method, there is no downtime. Each node would be patched and
        brought up while all the other nodes are up and running, resulting in
        no disruption of the system.

        2 – Flow diagrams

        . All-Node Patch

        . Shutdown all Oracle instances on all nodes
        . Apply the patch to all nodes
        . Bring all nodes up

        . Minimum downtime

        . Shutdown the Oracle instance on node 1
        . Apply the patch to the Oracle instance on node 1
        . Shutdown the Oracle instance on node 2
        . Apply the patch to the Oracle instance on node 2
        . Shutdown the Oracle instance on node 3
        . At this point, instances on nodes 1 and 2 can be brought up
        . Apply the patch to the Oracle instance on node 3
        . Startup the Oracle instance on node 3

        . Rolling patch (no downtime)

        . Shutdown the Oracle instance on node 1
        . Apply the patch to the Oracle instance on node 1
        . Start the Oracle instance on node 1
        . Shutdown the Oracle instance on node 2
        . Apply the patch to the Oracle instance on node 2
        . Start the Oracle instance on node 2
        . Shutdown the Oracle instance on node 3
        . Apply the patch to the Oracle instance on node 3
        . Start the Oracle instance on node 3

      • thank you munna for sharing this info

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

 
%d bloggers like this: