The data guard environment : -
-
Two machine with oracle Linux 6.8 installed (Names: Primary and Standby)
-
Oracle 11g database release 2 installed on each computer.
-
On the primary database Oracle software is installed with a database with the name primary , on the standby database only the oracle software has been installed the database will be created from active primary database.
-
Oracle home's path is identical on both databases.
-
Control files are also on identical path
The data guard process will be implemented in two processes :-
-
Primary database preparation.
-
Standby database preparation
Primary
database Preparation : -
-
enable force logging on the primary databases
SYS @ primary > alter
database force logging;
SYS @ primary > select
force_logging from v$database;
-
Add standby log files on the primary databases for storing redo received from primary database before being propagated to the standby database during swicthover or failover, they must be equal or more to the redo on the primary database.
SYS @ primary > select *
from v$log;
SYS @ primary > alter
database add standby logfile size 50M;
SYS
@ primary > alter
database add standby logfile size 50M;
SYS
@ primary > alter
database add standby logfile size 50M;
SYS
@ primary > alter
database add standby logfile size 50M;
SYS
@ primary > select * from v$loglogfile;
-
set the log_archive_config and log_archive_config_2 parameters which disable or enable sending the sending of redo streams to standby.
SYS
@ primary > show parameter db_name
NAME
TYPE VALUE
------------------------------------
----------- ------------------------------
db_name
string primary
SYS
@ primary > show parameter db_unique_name
NAME
TYPE VALUE
------------------------------------
----------- ------------------------------
db_unique_name
string primary
SYS
@ primary > alter system set
log_archive_config='dg_config=(primary,standby)';
where
primary is the primary database and standby is the standby database.
SYS
@ primary > alter system set log_archive_dest_2='service=primary
async valid_for=(online_logfile,primary_role)
db_unique_name=standby';
where
primary is the primary service name and standby is the db unique name
of the standby database.
-
Set the standby file management to auto so that when oracle files are dropped or added to the primary database the changes are made on the standby automatically.
SYS
@ primary > alter system set STANDBY_FILE_MANAGEMENT=AUTO;
-
Set the Primary database to run in archive log mode
SYS
@ primary > archive log list;
Database
log mode Archive Mode
Automatic
archival Enabled
Archive
destination USE_DB_RECOVERY_FILE_DEST
Oldest
online log sequence 31
Next
log sequence to archive 33
Current
log sequence 33
SYS
@ primary > shutdown immediate;
SYS
@ primary > startup mount;
SYS
@ primary > alter database archivelog;
SYS
@ primary > alter database open;
6.
Use net manager to ensure oracle net services aliases for both
primary and standby are added to the tnsnames.ora.
[oracle@linux
admin]$ cat tnsnames.ora
#
tnsnames.ora Network Configuration File:
/u01/app/oracle/product/11.2.0/home/network/admin/tnsnames.ora
#
Generated by Oracle configuration tools.
PRIMARY
=
(DESCRIPTION
=
(ADDRESS_LIST
=
(ADDRESS
= (PROTOCOL = TCP)(HOST = linux)(PORT = 1521))
)
(CONNECT_DATA
=
(SERVICE_NAME
= primary)
)
)
STANDBY
=
(DESCRIPTION
=
(ADDRESS_LIST
=
(ADDRESS
= (PROTOCOL = TCP)(HOST = host1)(PORT = 1521))
)
(CONNECT_DATA
=
(SERVICE_NAME
= standby)
)
)
then
copy the tnsnames.ora to the standby database using secure copy
[oracle@linux admin]$ scp
/u01/app/oracle/product/11.2.0/home/network/admin/tnsnames.ora
oracle@host1:/u01/app/oracle/product/11.2.0/home/network/admin/tnsnames.ora;
7. Copy the password file to the standby server
[oracle@host1 dbs]$ scp
/u01/app/oracle/product/11.2.0/home/dbs/orapwprimary
oracle@host1:/u01/app/oracle/product/11.2.0/home/dbs/orapwstandby
Standby
database Preparation : -
- The SID will be Standby
-
Configure a listener on the standby with a static service entry of the standby databases
[oracle@host1 admin]$ more listener.ora
# listener.ora Network Configuration File:
/u01/app/oracle/product/11.2.0/home/n
etwork/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = standby)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/home)
(SID_NAME = standby)
)
(SID_DESC =
(GLOBAL_DBNAME = standby_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/home)
(SID_NAME = standby)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = host1)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/oracle
2. Create audit, fast recovery and oradata directories in the standby
with the following path
[oracle@host1 adump]$ mkdir -p /u01/app/oracle/admin/standby/adump
[oracle@host1 standby]$ mkdir /u01/app/oracle/FRA/standby
[oracle@host1 oradata]$ mkdir /u01/app/oracle/oradata
3. Create a parameter file in the dbs directory with the db_name as
the only value
[oracle@host1 oradata]$ cd $ORACLE_HOME/dbs
[oracle@host1 dbs]$ vi initstandby.ora
DB_NAME=standby
~
~
~
~
4. start the standby database in nomount mode
SYS @ standby > startup nomount
- Go back to the primary database and connect to rman in order to
start the duplication by connecting to both primary and auxiliary
databases
[oracle@linux admin]$ rman target / auxiliary sys@standby
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Feb 25
21:06:59 2017
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights
reserved.
connected to target database: PRIMARY (DBID=1774373217)
auxiliary database Password:
connected to auxiliary database: PRIMARY (DBID=1774373217)
RMAN>
Run the below Rman script to duplicate the databases
run {
allocate channel p1 type disk;
allocate channel p2 type disk;
allocate channel p3 type disk;
allocate channel p4 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert 'primary','standby'
set db_unique_name = 'standby'
set db_file_name_convert='/primary/','/standby/'
set log_file_name_convert='/primary/','/standby/'
set log_archive_max_processes='5'
set fal_server = 'primary'
set standby_file_management='AUTO'
set log_archive_config ='dg_config=(primary,standby)'
set log_archive_dest_2='service=primary async
valid_for=(online_logfile,primary_role) db_unique_name=primary';
}
- After the script has completed successful run the following command
on the standby databases
SYS @ standby > alter database recover managed standby database
using current logfile disconnect;
The command enable redo apply to be started where current logfile
allows redo to be applied as soon as it's received and disconnect
option allow the redo apply to run in the background.
Now the data guard configuration is completed and can be tested by
switching logfile on the primary database and checking the sequence
number in the standby database.
On standby
SYS @ standby > select sequence#,first_time from
v$archived_log;
SEQUENCE# FIRST_TIM
---------- ---------
25 25-FEB-17
25 25-FEB-17
26 25-FEB-17
26 25-FEB-17
27 25-FEB-17
27 25-FEB-17
28 25-FEB-17
30 25-FEB-17
29 25-FEB-17
31 25-FEB-17
32 25-FEB-17
33 rows selected
On Primary database
SYS @ primary > alter system switch logfile;
System altered.
SYS @ primary >
On standby database
SYS @ standby > select sequence#,first_time from v$archived_log;
SEQUENCE# FIRST_TIM
---------- ---------
25 25-FEB-17
25 25-FEB-17
26 25-FEB-17
26 25-FEB-17
27 25-FEB-17
27 25-FEB-17
28 25-FEB-17
30 25-FEB-17
29 25-FEB-17
31 25-FEB-17
32 25-FEB-17
SEQUENCE# FIRST_TIM
---------- ---------
33 25-FEB-17
34 rows selected.
In the next article I will discuss Data broker configuration and
basic tasks like switching over roles from primary to standby
database.
