Here in this article I am going to cover steps by step approach to configure Oracle 11g Data Guard Physical Standby.
In my case, Ingredients to simulate Physical Standby data guard environment are as below:
In my case, Ingredients to simulate Physical Standby data guard environment are as below:
2 VM’s, Primary and DR with enough CPU and RAM in order to run oracle database.
Primary server configuration
CentOS 6.5
Server name: pr
IP: 192.168.17.131
Oracle 11g software plus oracle instance.
Oracle SID/Global_name: RTS
Oracle db_unique_name: RTS
Secondary server configuration
CentOS 6.5
Server name: dr
IP: 192.168.17.132
Oracle 11g software only.
Oracle SID/Global name: RTS
Oracle db_unique_name: RTSDR
Note:
Oracle version on Primary and secondary should be identical. i.e. In my case it’s 11.2.0.1.0
db_unique_name on standby and primary database should be different in order to used in DG_CONFIG setting of the LOG_ARCHIVE_CONFIG parameter.
Primary and DR server should ping each other by IP as well as its server name. In order to ping with its server name, edit /etc/hosts file accordingly.
Primary Server Configurations:
<Step – 1 >
Enable Archive log:
Primary database is in No Archive Mode, Enable it:
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Current log sequence 3
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 3
Current log sequence 3
<Step – 2 >
Enable force logging with the help of following SQL command:
SQL> ALTER DATABASE FORCE LOGGING;
<Step – 3 >
Verify initialization parameters db_name and db_unique_name on primary, In my case those are set to: RTS
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string RTS
SQL> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string RTS
<Step – 4 >
Issue following command to set LOG_ARCHIVE_CONFIG parameter for data guard config.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(RTS,RTSDR)';
Ensure your above changes as below:
SQL> show parameter LOG_ARCHIVE_CONFIG
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string DG_CONFIG=(RTS,RTSDR)
<Step – 5 >
Issue following to set LOG_ARCHIVE_DEST_2, In my case i have used flash recovery area for remote archive log destination.
Note: Service and DB_UNIQUE_NAME reference the remote standby location.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=RTSDR NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=RTSDR';
Ensure your above changes as below:
SQL> show parameter LOG_ARCHIVE_DEST_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string SERVICE=rtsdr NOAFFIRM ASYNC V
ALID_FOR=(ONLINE_LOGFILES,PRIM
ARY_ROLE) DB_UNIQUE_NAME=rtsdr
Enable LOG_ARCHIVE_DEST_2:
SQL> alter system set log_archive_dest_state_2=enable;
SQL> show parameter log_archive_dest_state_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2 string ENABLE
<Step – 6 >
Set log_archive_format parameter as below:
SQL> alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;
SQL> show parameter log_archive_format
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_format string %t_%s_%r.arc
Set log_archive_max_processes parameter to 30:
SQL> alter system set log_archive_max_processes=30;
SQL> show parameter log_archive_max_processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_max_processes integer 30
Set remote_login_passwordfile parameter to exclusive:
SQL> alter system set remote_login_passwordfile=exclusive scope=spfile;
SQL> show parameter remote_login_passwordfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string EXCLUSIVE
<Step – 7 >
Set fal_server and fal_client parameter for primary database:
SQL> alter system set fal_server=RTSDR;
SQL> show parameter fal_server
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fal_server string RTSDR
SQL> alter system set fal_client='RTS';
SQL> show parameter fal_client
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fal_client string RTS
Set standby_file_management parameter to auto:
SQL> alter system set standby_file_management=auto;
SQL> show parameter standby_file_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string AUTO
<Step – 8 >
Configure listener file and copy it to DR @ location: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin
Listener file:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = RTS)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = RTS)
) )
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = pr)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/oracle
<Step – 9 >
Entries for the primary and standby databases are needed in the “$ORACLE_HOME/network/admin/tnsnames.ora” files on both the servers.
RTS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = pr)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = RTS)
(GLOBAL_NAME = RTS)
(UR=A)
) )
RTSDR =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dr)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = RTS)
(GLOBAL_NAME = RTS)
(UR=A)
) )
Ensure your above configuration by TNSPing utility on Primary and standby:
[oracle@pr admin]$ tnsping RTS
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 03-OCT-2014 06:04:09
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = pr)(PORT = 1521))) (CONNECT_ DATA = (SERVICE_NAME = rts) (GLOBAL_NAME = RTS)))
OK (10 msec)
[oracle@pr admin]$ tnsping RTSDR
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 03-OCT-2014 06:04:15
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dr)(PORT = 1521))) (CONNECT_ DATA = (SERVICE_NAME = RTS) (GLOBAL_NAME = RTS)))
OK (20 msec)
<Step – 10 >
Backup primary database via RMAN backup utility:
[oracle@pr admin]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Oct 4 02:02:11 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: RTS (DBID=1421312347)
RMAN> backup database plus archivelog;
Note: Backup location: Flash Recovery Area.
<Step – 11>
Create Standby control file.
SQL> alter database create standby controlfile as '/u01/bkup/stndbyctrl.ctl';
<Step – 12>
Create pfile from spfile:
SQL> create pfile='/u01/bkup/initRTS.ora' from spfile;
<Step – 13>
After creating parameter file as above, edit following changes in newly created pfile:
*.db_unique_name='RTSDR'
*.fal_server='RTS';
*.log_archive_dest_2='SERVICE=RTS ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=RTS'
<Step – 14>
Copy parameter file to DR @ location: /u01/app/oracle/product/11.2.0/dbhome_1/dbs/
[oracle@pr bkup]$ scp initRTS.ora oracle@192.168.17.132:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/
Copy standby control file to DR @ location: /u01/app/oracle/oradata/RTS/control01.ctl & /u01/app/oracle/flash_recovery_area/RTS/control02.ctl
[oracle@pr bkup]$ scp stndbyctrl.ctl oracle@192.168.17.132:/u01/app/oracle/oradata/RTS/control01.ctl
[oracle@pr bkup]$ scp stndbyctrl.ctl oracle@192.168.17.132:/u01/app/oracle/flash_recovery_area/RTS/control02.ctl
Copy password file to DR @ location: /u01/app/oracle/product/11.2.0/dbhome_1/dbs/
[oracle@pr ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/dbs/
[oracle@pr dbs]$ scp orapwRTS oracle@192.168.17.132:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/
Copy Listener file to DR @ location: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin
[oracle@pr bkup]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin
[oracle@pr admin]$ scp listener.ora oracle@192.168.17.132:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin
Edit DR database listener file with host name. ( i.e. (HOST = dr))
And finally copy RMAN backup to DR @ flash recovery area.
[oracle@pr ~]$ cd /u01/app/oracle/flash_recovery_area/
[oracle@pr flash_recovery_area]$ scp -r RTS oracle@192.168.17.132:/u01/app/oracle/flash_recovery_area/
Standby/DR Server Configurations:
<Step – 15>
Startup standby database in mount state:
Set following environment variable as oracle user OR edit those in /home/oracle/.bash_profile in order to set it for every time while oracle user logged in:
export ORACLE_SID=RTS
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export PATH=$PATH:/u01/app/oracle/product/11.2.0/dbhome_1/bin
[oracle@DR ~]$ mkdir -p /u01/app/oracle/admin/RTS/adump
SQL> sqlplus / as sysdba
SQL> startup mount
ORACLE instance started.
Total System Global Area 972898304 bytes
Fixed Size 2219272 bytes
Variable Size 566231800 bytes
Database Buffers 398458880 bytes
Redo Buffers 5988352 bytes
Database mounted.
<Step – 16>
Create spfile from pfile:
SQL> create spfile from pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initRTS.ora';
<Step – 17>
Start listener on standby:
[oracle@dr ~]$ lsnrctl start
<Step – 18>
Create follwoing directories on DR server, in case those are not available:
/u01/app/oracle/admin/RTS/adump
/u01/app/oracle/flash_recovery_area
<Step – 19>
Restore and recover database @ DR with RMAN backup utility.
RMAN> list backup of database summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
6 B F A DISK 05-OCT-14 1 1 NO TAG20141005T065604
RMAN> restore database;
RMAN> recover database;
Note: Recovery of database would be failed with RMAN-06054 error, We can ignore it because RMAN will ask for unknown archive log ( i.e. next archive log sequence, i.e. 10 ) who is not also available on Primary database.
Error log: RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 10 and starting SCN of 1009554
<Step – 20>
Create standby redolog file to Primary and DR for the user of switch over, It should be match the configuration of the primary server.
Note: Create one additional standby redolog file on both.
SQL> sqlplus / as sysdba
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='MANUAL' scope=spfile;
SQL> ALTER DATABASE ADD STANDBY LOGFILE('/u01/app/oracle/oradata/RTS/stndby1.log') size 51M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE('/u01/app/oracle/oradata/RTS/stndby2.log') size 51M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE('/u01/app/oracle/oradata/RTS/stndby3.log') size 51M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE('/u01/app/oracle/oradata/RTS/stndby4.log') size 51M;
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO' scope=spfile;
<Step – 21>
Start apply process @ DR.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE nodelay DISCONNECT FROM SESSION;
In case of you want to cancel apply process, issue following command:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
<Step – 22>
After graceful completion of above apply process, verify archive logs on Primary as well as DR.
On Primary:
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 8
Next log sequence to archive 10
Current log sequence 10
On Standby:
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 8
Next log sequence to archive 0
Current log sequence 10
By above result, archive logs on both the databases are in sync now.
To test your data guard configuration, generate archive logs on primary site and verify it on DR site:
SQL> alter system switch logfile; //Give this command multiple times for testing.
On Primary:
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 19
Next log sequence to archive 21
Current log sequence 21
OR
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
20
On Standby:
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 19
Next log sequence to archive 0
Current log sequence 21
OR
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
20
Cheers!! Our Data Guard configuration has been configured successfully.
Verify database roles by below mentioned SQL query:
On Primary:
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
RTS READ WRITE PRIMARY
On DR:
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
RTS MOUNTED PHYSICAL STANDBY
You can also verify total number of log sequence generated and applied on DR site, by below SQL query:
SQL> ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';
SQL> SELECT sequence#, first_time, next_time, applied
FROM v$archived_log
ORDER BY sequence#;
SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
---------- --------- --------- ---------
3 04-OCT-14 04-OCT-14 YES
4 04-OCT-14 04-OCT-14 YES
5 04-OCT-14 04-OCT-14 YES
6 04-OCT-14 05-OCT-14 YES
7 05-OCT-14 05-OCT-14 YES
8 05-OCT-14 05-OCT-14 YES
9 05-OCT-14 05-OCT-14 YES
10 05-OCT-14 05-OCT-14 YES
11 05-OCT-14 05-OCT-14 YES
12 05-OCT-14 05-OCT-14 YES
13 05-OCT-14 05-OCT-14 YES
14 05-OCT-14 05-OCT-14 YES
15 05-OCT-14 05-OCT-14 YES
16 05-OCT-14 05-OCT-14 YES
17 05-OCT-14 05-OCT-14 YES
18 05-OCT-14 05-OCT-14 YES
19 05-OCT-14 05-OCT-14 YES
20 05-OCT-14 05-OCT-14 YES
In case of you are facing any kind of error than following SQL query will help you to diagnose it.
SQL> select dest_name,status,error from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_2';
DEST_NAME
--------------------------------------------------------------------------------
STATUS ERROR
--------- -----------------------------------------------------------------
LOG_ARCHIVE_DEST_2
VALID
Note: LOG_ARCHIVE_DEST_2 should be VALID in order to continue Data Guard sync.
OR
SQL> select message from v$dataguard_status;
Note: This command will give you appropriate message about the dataguard current status.
By default, for a newly created standby database, the primary database is in maximum performance mode.
Protection Mode:
Default protection mode of newly configured standby database would be maximum performance mode.
There are 3 protection modes: Maximum Availability, Maximum Performance and Maximum Protection. for more information, click me.
By above mentioned steps you can configure Data Guard in your environment.
No comments:
Post a Comment