How to Configure Oracle Dataguard and How to Perform Switchover and Failover
Answer:
In this article i will explained about how to configure oracle dataguard without using Rman utility and also explained what type of errors you will be faced at the time of configuring oracle dataguard and also i gave some real time interview questions and answer related to oracle dataguard
Before Dataguard configure you need to perform some postrequest
step 6: primary
============================================================================
HOW TO PERFORM SWITCHOVER IN ORACLE DATAGUARD:
Mostly switchover performed every 6 month one time or 3 month one time for increasing the performance of dataguard.
Note : Make sure that redo records are received and applied in standnby database.
Real time Dataguard interview questions and answers for Experience people
In this article i will explained about how to configure oracle dataguard without using Rman utility and also explained what type of errors you will be faced at the time of configuring oracle dataguard and also i gave some real time interview questions and answer related to oracle dataguard
Before Dataguard configure you need to perform some postrequest
- oracle version and os version should be same.
- Ping the both server from primary to standby and standby to primary( ping 192.168.1.100)
- check fairewal;l status if enaled or not using service iptables status
- check the port number whether available or not using netstat -plunts |grep port_number
Primary database : nijam standby database : benzserver : 192.168.1.100 server : 192.168.1.101
1. Configure listener and tnsnames on both Primary and standby .
On Primary database - nijam :
[oracle@nijamedu 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 = nijam)
(ORACLE_HOME = /u01/product/10.2.0/db_1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = nijamedu.in)(PORT = 1521))
)
)
[oracle@nijamedu 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.
NIJAM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = nijam)
)
)
BENZ =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = benz)
)
)
On standby database - benz :
[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 = nijam)
(ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg2.nijam.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.
NIJAM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = nijam)
)
)
BENZ =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = benz)
)
)
On primary database we have the below scripts.
[oracle@nijamedu scripts]$ cat create.sh
rm /tmp/benz.ctl
export ORACLE_SID=nijam
sqlplus "/ as sysdba" <<EOF
startup nomount
create database nijam
logfile group 1 ('/u02/nijam/oradata/redo1.log') size 50M,
group 2 ('/u02/nijam/oradata/redo2.log') size 50M
datafile '/u02/nijam/oradata/system.dbf' size 200M autoextend on maxsize unlimited extent management local
sysaux datafile '/u02/nijam/oradata/sysaux.dbf' size 100M autoextend on maxsize unlimited
undo tablespace undotbs datafile '/u02/nijam/oradata/undotbs1.dbf' size 100M
default temporary tablespace temp tempfile '/u02/nijam/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/nijam/oradata/benzlog1.dbf' size 50m;
alter database add standby logfile '/u02/nijam/oradata/benzlog2.dbf' size 50m;
alter database create standby controlfile as '/tmp/benz.ctl';
shutdown immediate
EOF
[oracle@nijamedu scripts]$
[oracle@nijamedu scripts]$ cat portfiles.sh
scp /tmp/benz.ctl 192.168.1.101:/u02/benz/oradata/
scp /u02/nijam/oradata/* 192.168.1.101:/u02/benz/oradata/
[oracle@nijamedu scripts]$ cat execlog.sh
export ORACLE_SID=nijam
sqlplus "/ as sysdba" <<EOF
exec DBMS_LOGBENZBY.BUILD;
EOF
[oracle@nijamedu scripts]$ cat test.sh
export ORACLE_SID=nijam
/u01/product/10.2.0/db_1/bin/sqlplus "/ as sysdba" <<EOF
create tablespace users2 datafile '/u02/nijam/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=benz
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=benz
export ORACLE_SID
sqlplus "/ as sysdba" <<EOF
startup mount
alter database recover to logical standby nijam;
shutdown immediate;
startup mount;
alter database open resetlogs;
alter database start logical standby apply immediate;
EOF
Parameter file of primary database (nijam)
[oracle@nijamedu dbs]$ cat initnijam.ora
db_name=nijam
sga_target=500m
undo_management=auto
undo_tablespace=undotbs
compatible=10.1.0.0.0
control_files=/u02/nijam/oradata/nijam.ctl
# for data guard
db_unique_name=nijam
log_archive_config='DG_CONFIG=(nijam,benz)'
log_archive_dest_1='LOCATION=/u02/nijam/arch/ valid_for=(all_logfiles,all_roles) db_unique_name=nijam'
log_archive_dest_2='SERVICE=benz LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=benz'
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 (benz)
[oracle@dg2 dbs]$ cat initbenz.ora db_name=nijam sga_target=500m undo_management=auto undo_tablespace=undotbs compatible=10.1.0.0.0 control_files=/u02/benz/oradata/benz.ctl # for data guard db_file_name_convert='/u02/nijam/','/u02/benz/' log_file_name_convert='/u02/nijam/','/u02/benz/' db_unique_name=benz log_archive_config='DG_CONFIG=(nijam,benz)' log_archive_dest_1='LOCATION=/u02/benz/arch/ valid_for=(all_logfiles,all_roles) db_unique_name=benz' log_archive_dest_2='SERVICE=nijam LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=nijam' 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=nijam fal_client=benz
Create the below directories for datafile and archives
in primary:
mkdir -p /u02/nijam/oradata
mkdir -p /u02/nijam/arch
in standby:
mkdir -p /u02/nijam/oradata
mkdir -p /u02/benz/oradata
mkdir -p /u02/nijam/arch
Create password file on both the servers.
in primary :
cd $ORACLE_HOME/dbs
export ORACLE_SID=nijam
orapwd file=orapwnijam password=sys
in standby :
cd $ORACLE_HOME/dbs
export ORACLE_SID=benz
orapwd file=orapwbenz password=sys
Note : Make sure that listener is running on both primary and standby.
you need to run following order from above mentioned script :
step 1: - primary
./create.sh
- creates database named "nijam"
- 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=benz
orapwd file=orapwbenz 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.
Postrequest:
1.just check the database role and unique name using following command
More Details About Dataguard GAP Go to this ARTICLEPostrequest:
1.just check the database role and unique name using following command
SQL> select name,db_unique_name,database_role,SWITCHOVER_STATUS from v$database;
2. monitor the log sequence and applied sequence if you get any sequnece gap means just take the incremental backup import that on standby database SQL> archive log list; SQL> select APPLIED,CREATOR,SEQUENCE# from v$archived_log; ---For Detecting Archive GAP SQL> select * from v$archive_gap; THREAD# LOW_SEQUENCE# HIGH_SEQUENCE# -------- -------------- -------------- 1 24 28
============================================================================
HOW TO PERFORM SWITCHOVER IN ORACLE DATAGUARD:
Mostly switchover performed every 6 month one time or 3 month one time for increasing the performance of dataguard.
Note : Make sure that redo records are received and applied in standnby database.
In Primary :
From following example you can perform switchover
From following example you can perform switchover
alter database commit to switchover to physical standby with session shutdown;
(or)
alter database commit to switchover to physical standby with session shutdown nowait;
In Standby :
alter database commit to switchover to primary with session shutdown;
(or)
alter database commit to switchover to primary with session shutdown nowait;
In Primary :
shutdown immediate;
startup mount;
alter database mount standby database;
alter database open;
recover managed standby database disconnect from session;
In standby :
shutdown immediate;
startup;
How to Perform Failover in oracle dataguard :
Here i made failover with myself for learning purpose you will get failover After disabled log_archive_dest_2 at standby.
standby database.
Here i made failover with myself for learning purpose you will get failover After disabled log_archive_dest_2 at standby.
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
Real time Dataguard interview questions and answers for Experience people
1.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 = nijam_benzby 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.
2.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.
3.How will you calculate bandwidth for data guard?
Required bandwidth = ((Redo generate bytes per sec. / 0.7) * 8) / 1,000,000 = bandwidth in Mbps
4.what are the issue or errors faced in Dataguard :
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;
5.how will you resolve the following error in oracle dataguard ?
ORA-00444: background process "QMNC" failed while starting
ORA-00020: maximum number of processes () exceeded
Solution :
increase the processes parameter in spfile/pfile.
6.How will you give the solution if you get following errors ?
Errors in file /u01/product/10.2.0/db_1/rdbms/log/nijam_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/nijam_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=(nijam_primary,nijam_stndby)'
7. fixing dataguard log file errors
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/log1nijam.dbf'
solution :
log_file_name_convert='hello','hello'
Note : instead of hello u can give anything.
8.dataguard errors resolving
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.
9.dataguard errors solving
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.
Comments
Post a Comment