SAINORA

HomeBlogsDataguard

Dataguard

Configure dataguard in oracle 10G

Primary database : sss
server : 192.168.1.100

standby database : std
server : 192.168.1.101

Prerequisite :

1. Configure listener and tnsnames on both Primary and standby .
On Primary database - sss :

[oracle@sssedu admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = sss)
(ORACLE_HOME = /u01/product/10.2.0/db_1)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = sssedu.in)(PORT = 1521))
)
)

[oracle@sssedu admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

SSS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = sss)
)
)

STD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = std)
)
)

On standby database - std :
[oracle@dg2 admin]$ cat listener.ora
# listener.ora Network Configuration File: /home/oracle/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = sss)
(ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg2.sss.com)(PORT = 1521))
)
)

[oracle@dg2 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /home/oracle/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

SSS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = sss)
)
)

STD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = std)
)
)

On primary database we have the below scripts.

[oracle@sssedu scripts]$ cat create.sh
rm /tmp/std.ctl
export ORACLE_SID=sss
sqlplus "/ as sysdba" <<EOF
startup nomount
create database sss
logfile group 1 ('/u02/sss/oradata/redo1.log') size 50M,
group 2 ('/u02/sss/oradata/redo2.log') size 50M
datafile '/u02/sss/oradata/system.dbf' size 200M autoextend on maxsize unlimited extent management local
sysaux datafile '/u02/sss/oradata/sysaux.dbf' size 100M autoextend on maxsize unlimited
undo tablespace undotbs datafile '/u02/sss/oradata/undotbs1.dbf' size 100M
default temporary tablespace temp tempfile '/u02/sss/oradata/temp01.dbf' size 100M;
alter database datafile 1 autoextend on;
alter database datafile 2 autoextend on;
alter database datafile 3 autoextend on;
alter database datafile 4 autoextend on;
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
shutdown immediate
startup mount
alter database archivelog;
alter database open;
alter database force logging;
alter database add standby logfile '/u02/sss/oradata/stdlog1.dbf' size 50m;
alter database add standby logfile '/u02/sss/oradata/stdlog2.dbf' size 50m;
alter database create standby controlfile as '/tmp/std.ctl';
shutdown immediate
EOF
[oracle@sssedu scripts]$

[oracle@sssedu scripts]$ cat portfiles.sh
scp /tmp/std.ctl 192.168.1.101:/u02/std/oradata/
scp /u02/sss/oradata/* 192.168.1.101:/u02/std/oradata/

[oracle@sssedu scripts]$ cat execlog.sh
export ORACLE_SID=sss
sqlplus "/ as sysdba" <<EOF
exec DBMS_LOGSTDBY.BUILD;
EOF

[oracle@sssedu scripts]$ cat test.sh
export ORACLE_SID=sss
/u01/product/10.2.0/db_1/bin/sqlplus "/ as sysdba" <<EOF
create tablespace users2 datafile '/u02/sss/oradata/users2.dbf' size 10m;
create user gopi1 identified by gopi1 default tablespace users2 temporary tablespace temp;
grant dba to gopi1;
conn gopi1/gopi1
create table a(a number);
insert into a select 1 from all_objects;
commit;
conn / as sysdba
alter system switch logfile;
EOF

On standby database we have the below scripts.

[oracle@dg2 scripts]$ cat start.sh
ORACLE_SID=std
export ORACLE_SID
sqlplus "/ as sysdba" <<EOF
startup nomount
alter database mount standby database;
alter database recover managed standby database disconnect from session;
alter system switch logfile;
alter database recover managed standby database cancel;
EOF

[oracle@dg2 scripts]$ cat convert.sh
ORACLE_SID=std
export ORACLE_SID
sqlplus "/ as sysdba" <<EOF
startup mount
alter database recover to logical standby sss;
shutdown immediate;
startup mount;
alter database open resetlogs;
alter database start logical standby apply immediate;
EOF

Parameter file of primary database (sss)

[oracle@sssedu dbs]$ cat initsss.ora
db_name=sss
sga_target=500m
undo_management=auto
undo_tablespace=undotbs
compatible=10.1.0.0.0
control_files=/u02/sss/oradata/sss.ctl

# for data guard
db_unique_name=sss
log_archive_config='DG_CONFIG=(sss,std)'
log_archive_dest_1='LOCATION=/u02/sss/arch/ valid_for=(all_logfiles,all_roles) db_unique_name=sss'
log_archive_dest_2='SERVICE=std LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=std'
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
remote_login_passwordfile=exclusive
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=4

Parameter file of standby database (std)

[oracle@dg2 dbs]$ cat initstd.ora
db_name=sss
sga_target=500m
undo_management=auto
undo_tablespace=undotbs
compatible=10.1.0.0.0
control_files=/u02/std/oradata/std.ctl

# for data guard
db_file_name_convert='/u02/sss/','/u02/std/'
log_file_name_convert='/u02/sss/','/u02/std/'
db_unique_name=std
log_archive_config='DG_CONFIG=(sss,std)'
log_archive_dest_1='LOCATION=/u02/std/arch/ valid_for=(all_logfiles,all_roles) db_unique_name=std'
log_archive_dest_2='SERVICE=sss LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=sss'
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
remote_login_passwordfile=exclusive
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=4
standby_file_management=auto
fal_server=sss
fal_client=std

Create the below directories 
in primary:

mkdir -p /u02/sss/oradata
mkdir -p /u02/sss/arch

in standby:

mkdir -p /u02/sss/oradata
mkdir -p /u02/std/oradata
mkdir -p /u02/sss/arch

 

Create password file on both the servers.
in primary :

cd $ORACLE_HOME/dbs
export ORACLE_SID=sss
orapwd file=orapwsss password=sys

in standby :

cd $ORACLE_HOME/dbs
export ORACLE_SID=std
orapwd file=orapwstd password=sys

Note : Make sure that listener is running on both primary and standby.

Order of execution
step 1: - primary
./create.sh
- creates database named "sss"
- enables archive log mode
-  enables force logging
- creates standby controlfile

step 2: - primary
./portfiles.sh
- copies standby control file from primary(192.168.1.100)  to standby server(192.168.1.101)

   - copy database files from primary to standby database.

step 3 : standby
./start.sh
-  starts the database in physical standby mode.

step 4:  primary database
./execlog.sh
- create the logbuilder

step 5: standby
./convert.sh
note : again recreate the password file in standby database.
cd $ORACLE_HOME/dbs
export ORACLE_SID=std
orapwd file=orapwstd password=sys

step 6: primary
./test.sh
- creates user gopi1 and tablespace users.
Note : make sure that the changes on the primary is reflected in secondary within 15 mins.

SWITCHOVER FROM PRIMARY TO SECONDARY and VICEVERSA.

Note :Make sure that redo records are received and applied in standnby database.

In Primary :
alter database commit to switchover to physical standby with session shutdown;

In Standby :
alter database commit to switchover to primary;

In Primary :
shutdown immediate;
startup mount;

In standby :
shutdown immediate;
startup;

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

Failover database :  -- standby database.
make sure that log_archive_dest_2 is disabled.
alter database recover managed standby database finish;
alter database commit to switchover to primary;

shutdown immediate
startup

What is NET_TIMEOUT parameter?

  The LGWR process  sends the redo data  to standby database and wait for acknowledgement. The waiting time is specified in seconds using NET_TIMEOUT. This parameter is specified in LOG_ARCHIVE_DEST parameter.

LOG_ARCHIVE_DEST_3='service = sss_stdby NETTIME_OUT= 30'
The default values in Oracle 10G is 180 sec   and Oracle 11g is 30 secs.

Note: LGWR sends the redo using LNS(Log network service) from primary to the RFS (Remote  file server) in standby database.

What is AFFIRM and NOAFFIRM ?

AFFIRM : When the redo data from primary is received by RFS the data will be written to standby redo log file  Once it is written the acknowledgement will be sent to the LNS through RFS.

NO AFFIRM : When the redo data from primary is received by RFS the data will be written to standby redo log file(SRLF)  Acknowledgement(ACK) will be sent to the LNS through RFS before writing to standby redo log file(SRLF).

Difference : AFFIRM will get the ACK after the redo data is written to SRLF. NOAFFIRM will get the ACK before writing to SLRF.

How will you calculate bandwidth for data guard?
http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/6006ac18-7900-2e10-a793-ee2d6b39063f?QuickLink=index&overridelayout=true&49873160777130

 

Required bandwidth = ((Redo generate bytes per sec. / 0.7) * 8) / 1,000,000 = bandwidth in Mbps

Dataguard Related Errors :
SQL> recover managed standby database disconnect from session;
ORA-01153: an incompatible media recovery is active
Solution :
SQL> alter database recover managed standby database cancel;

Database altered.
SQL> alter database recover managed standby database using current logfile disconnect;

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

ORA-00444: background process "QMNC" failed while starting
ORA-00020: maximum number of processes () exceeded
Solution :

increase the processes parameter in spfile/pfile.

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

Errors in file /u01/product/10.2.0/db_1/rdbms/log/sss_primary_lns1_15564.trc:
ORA-16057: DGID from server not in Data Guard configuration
LNS: Failed to archive log 2 thread 1 sequence 84 (16057)
Sun Apr  8 17:44:25 2012
Errors in file /u01/product/10.2.0/db_1/rdbms/log/sss_primary_arc0_15504.trc:
ORA-16057: DGID from server not in Data Guard configuration
Sun Apr  8 17:44:25 2012

Solution :
set the below parameter in pfile/spfile
log_archive_config='DG_CONFIG=(sss_primary,sss_stndby)'
---------------------------------------------------------------

 

Errors in file /u01/product/10.2.0/db_1/rdbms/log/zod_mrp0_12655.trc:
ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 1 thread 1: '/u01/product/10.2.0/db_1/dbs/log1sss.dbf'

 

solution :
log_file_name_convert='hello','hello'

 

Note : instead of hello u can give anything.

 

------------------------------------------------------------------
SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-01154: database busy. Open, close, mount, and dismount not allowed now

 

Solution :

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open read only;

Database altered.

 

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

SQL> alter database recover managed standby database disconnect from session;
alter database recover managed standby database disconnect from session
*
ERROR at line 1:
ORA-00444: background process "MRP0" failed while starting
ORA-00020: maximum number of processes () exceeded

solution  :
SQL> alter system set  log_archive_max_processes=1;

System altered.

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

           

Published on 24th March 2012

Published by Gopinathan D