Oracle Data Guard: 02 physical standby database configuration

Tweet

Click here to read the previous post

After the installation of the Oracle sw on the primary database, the first step is to create a password file on every database in the Data Guard configuration using the same identical sys user and password, so the log transport services can correctly work.
First look into the $ORACLE_HOME/dbs directory and search for an existing password file. In my virtual linux machine the orapwDGUARD file already exists and also the parameter remote_login_passwordfile is correctly set to EXCLUSIVE mode (see the picture below).

SQL> show parameter remote_login_

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string EXCLUSIVE



If you don't have a password file, execute these step:

$cd $ORACLE_HOME/dbs
$orapwd file=orapwDGUARD password=your_password

The orapw file must have the following name: orapwSID (and SID is equal to DGUARD in my case).
Then you must set the remote_login_passwordfile parameter in the spfile while the database is in the nomount state:

shutdown immediate;
startup nomount;
alter system set remote_login_passwordfile=exclusive scope=spfile;
alter database open;

When you will install the Oracle software on the standby machine you simply will be able to copy the orapwDGUARD password file to the standby machine to use the same identical sys user and password and let the log transport services work correctly.

The next step is to enable force logging. This step is not mandatory but you must kwow that any nologging operations performed on the primary won't be logged into the redo stream. So if you place the primary database into force logging mode, nologging operations are still permitted, but the changes are also written into the redo stream and the disaster recovery solution is maintained.
You can place the primary database in forced logging mode, issuing the following command with sysdba privilege (see the picture below):

alter database force logging;



Even if it's not mandatory (in very truth it's mandatory in maximum protection and maximum availability modes, but
I'm configuring maximum performance mode and in this case is optional), to make role transitions easier and faster,
it's a good practice to create the standby redo logs on both the databases (primary and standby).

First of all I see how many kilobytes are my redo log groups formed by:
SQL> SELECT GROUP#, THREAD#, BYTES/1024 FROM V$LOG;

GROUP# THREAD# BYTES/1024
---------- ---------- ----------
1 1 51200
2 1 51200

Oracle suggests to have at least one more standby redo log file group than
the number of online redo log file groups on the primary database.
I have two online redo log file groups, so I've to create three standby redo log file groups (see the picture below), typing:

ALTER DATABASE ADD STANDBY LOGFILE GROUP 3 ('/u01/app/oracle/oradata/DGUARD/sredolog03.log') SIZE 51200K;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/u01/app/oracle/oradata/DGUARD/sredolog04.log') SIZE 51200K;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/u01/app/oracle/oradata/DGUARD/sredolog05.log') SIZE 51200K;



In the following picture you can see the list of the $ORACLE_BASE/oradata/DGUARD/ directory:


The next step is to configure the initialization parameters of the primary database.
It's important to configure the parameters to control log transport services and log apply services useful during future role transitions.
The standby parameters are not read, while I use the primary control file; they are put into effect only when a role transition happens.

I have to obtain at least the following parameters:
## Primary Role Parameters ##
## DB_UNIQUE_NAME is the parameter the identify your role
## DB_NAME equal to DGUARD both on the primary and on the standby databases, is used only to preserve the same location of
## the oracle files, under the directory $ORACLE_BASE/oradata/DGUARD/
DB_UNIQUE_NAME=PRIMARY
SERVICE_NAMES=PRIMARY
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIMARY,STANDBY)'
LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIMARY'
LOG_ARCHIVE_DEST_2='SERVICE=STANDBY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
#Initially I decide to defer this paraemter, unless I complete the standby configuration.
LOG_ARCHIVE_DEST_STATE_2=DEFER
## Standby Role Parameters ##
## In this example I don't need to use the following parameters
## DB_FILE_NAME_CONVERT=('PRIMARY','STANDBY')
## LOG_FILE_NAME_CONVERT=('PRIMARY','STANDBY')
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=STANDBY_macbook2
FAL_CLIENT=PRIMARY_macbook1

Here is MY initDGUARD.ora file presents at the time of my configuration, before creating the spfile and before enable the log_archive_dest_state_2 parameter:
DGUARD.__db_cache_size=79691776
DGUARD.__java_pool_size=4194304
DGUARD.__large_pool_size=4194304
DGUARD.__shared_pool_size=75497472
DGUARD.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/DGUARD/adump'
*.background_dump_dest='/u01/app/oracle/admin/DGUARD/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/app/oracle/oradata/DGUARD/control01.ctl','/u01/app/oracle/oradata/DGUARD/control02.ctl','/u01/app/oracle/oradata/DGUARD/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/DGUARD/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='DGUARD'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=1073741824
*.db_unique_name='PRIMARY'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DGUARDXDB)'
*.fal_client='PRIMARY_MACBOOK1'
*.fal_server='STANDBY_MACBOOK2'
*.job_queue_processes=10
*.log_archive_config='DG_CONFIG=(PRIMARY,STANDBY)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIMARY'
*.log_archive_dest_2='SERVICE=STANDBY VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='DEFER'
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.service_names='PRIMARY'
*.sga_target=167772160
*.standby_file_management='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/DGUARD/udump'

I can use Enterprise Manager or show parameter to obtain the current value and eventually modify it.
I typed (see the picture below):
alter system set service_names=PRIMARY;
alter system set db_unique_name=PRIMARY scope=spfile;
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIMARY,STANDBY)' scope=spfile;
alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIMARY' scope=spfile;
alter system set LOG_ARCHIVE_DEST_2='SERVICE=STANDBY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY' scope=spfile;
alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=spfile;
alter system set LOG_ARCHIVE_DEST_STATE_2=DEFER scope=spfile;
alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=spfile;
alter system set FAL_SERVER=STANDBY_macbook2 scope=spfile;
alter system set FAL_CLIENT=PRIMARY_macbook1 scope=spfile;


In the picture below you can see the configuration of the FAL_SERVER and the FAL_CLIENT parameters:


Redo logs are important so the primary database must be in archivelog mode.
During the database installation I have choose to set the database in noarchivelog mode, in fact if I type:

SQL> select name, log_mode, open_mode from V$DATABASE;

NAME LOG_MODE OPEN_MODE
--------- ------------ ---------
DGUARD NOARCHIVELOG MOUNTED

So I must now set the database in archivelog mode in this way, using sysdba privilege (see the picture below):

shutdown immediate;
startup mount;
alter database archivelog;
alter database open;



Now I have to create the environment for the standby database.
So I start the second virtual machine (Standby) that I configured just like this step,
download the Oracle 10g Database from Oracle website and extract the zip file.
Launch the runInstaller as usual, select Advanced Installation from the Select Installation Method screen and click
NEXT untill the Select Configuration Option screen appears. From this screen select Install database Software only and click NEXT, untill the setup process doesn't finish successfully.

At this point I can create the necessary directories for the standby database.
As oracle user type (see the picture below):
cd $ORACLE_BASE
mkdir -p oradata/DGUARD
mkdir -p flash_recovery_area/DGUARD/onlinelog
mkdir -p admin/DGUARD/adump
mkdir -p admin/DGUARD/bdump
mkdir -p admin/DGUARD/cdump
mkdir -p admin/DGUARD/dpdump
mkdir -p admin/DGUARD/pfile
mkdir -p admin/DGUARD/udump



The next step is to get the necessary files of the primary database that will be used
later by the physical standby database.
I have to make a backup of the primary database, and I will not use RMAN but rather I will use
scp to copy all the necessary files from the primary database to the remote standby database.
Remember to type the oracle password when asked, using the scp command.
These are the commands I typed while the database was closed, I mean... use "shutdown immediate" (see the picture below):

$ cd $ORACLE_BASE/oradata/DGUARD/
$ ls
control01.ctl control03.ctl redo01.log sredolog03.log sredolog05.log system01.dbf undotbs01.dbf
control02.ctl example01.dbf redo02.log sredolog04.log sysaux01.dbf temp01.dbf users01.dbf
$ ping -c 1 macbook2
PING macbook2 (192.168.0.11) 56(84) bytes of data.
64 bytes from macbook2 (192.168.0.11): icmp_seq=0 ttl=64 time=1.31 ms

--- macbook2 ping statistics ---
1 packets transmitted, 1 received, 0% packet loss, time 6ms
rtt min/avg/max/mdev = 1.311/1.311/1.311/0.000 ms, pipe 2
$ scp example01.dbf oracle@macbook2:/u01/app/oracle/oradata/DGUARD/example01.dbf
oracle@macbook2's password:
example01.dbf 100% 100MB 3.2MB/s 00:31
$ scp redo01.log oracle@macbook2:/u01/app/oracle/oradata/DGUARD/redo01.log
oracle@macbook2's password:
redo01.log 100% 50MB 3.1MB/s 00:16
$ scp redo02.log oracle@macbook2:/u01/app/oracle/oradata/DGUARD/redo02.log
oracle@macbook2's password:
redo02.log 100% 50MB 2.6MB/s 00:19
$ scp sredolog03.log oracle@macbook2:/u01/app/oracle/oradata/DGUARD/sredolog03.log
oracle@macbook2's password:
sredolog03.log 100% 50MB 2.8MB/s 00:18
$ scp sredolog04.log oracle@macbook2:/u01/app/oracle/oradata/DGUARD/sredolog04.log
oracle@macbook2's password:
sredolog04.log 100% 50MB 2.6MB/s 00:19
$ scp sredolog05.log oracle@macbook2:/u01/app/oracle/oradata/DGUARD/sredolog05.log
oracle@macbook2's password:
sredolog05.log 100% 50MB 3.9MB/s 00:13
$ scp sysaux01.dbf oracle@macbook2:/u01/app/oracle/oradata/DGUARD/sysaux01.dbf
oracle@macbook2's password:
sysaux01.dbf 100% 230MB 4.7MB/s 00:49
$ scp system01.dbf oracle@macbook2:/u01/app/oracle/oradata/DGUARD/system01.dbf
oracle@macbook2's password:
system01.dbf 100% 480MB 5.4MB/s 01:29
$ scp temp01.dbf oracle@macbook2:/u01/app/oracle/oradata/DGUARD/temp01.dbf
oracle@macbook2's password:
temp01.dbf 100% 21MB 2.3MB/s 00:09
$ scp undotbs01.dbf oracle@macbook2:/u01/app/oracle/oradata/DGUARD/undotbs01.dbf
oracle@macbook2's password:
undotbs01.dbf 100% 30MB 3.3MB/s 00:09
$ scp users01.dbf oracle@macbook2:/u01/app/oracle/oradata/DGUARD/users01.dbf
oracle@macbook2's password:
users01.dbf 100% 5128KB 5.0MB/s 00:01



In the picture below you can see the scp command on temp01.dbf, undotbs01.dbf and users01.dbf datafiles:


I have to obtain the init parameter from the spfile of the primary database (see the picture below):
create pfile from spfile;
it will be created into the $ORACLE_HOME/dbs location.

I have to create also the standby control file:
ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/u01/app/oracle/oradata/PRIMARY/standby_control01.ctl';



Copy also these files and the orapwDGUARD file from primary database machine (named Primary) to the standby database machine
(named Standby)
As oracle user type:
cd $ORACLE_HOME/dbs
scp initDGUARD.ora oracle@macbook2:/u01/app/oracle/product/10.2.0/db_1/dbs/initDGUARD.ora
(type the oracle password when asked)
cd $ORACLE_BASE/oradata/PRIMARY/
scp standby_control01.ctl oracle@guard.dataguard.com:/u01/app/oracle/oradata/STANDBY/standby_control01.ctl
(type the oracle password when asked)
Repeat this step for every file existing in the $ORACLE_BASE/oradata/DGUARD/ directory.

$ scp standby_control01.ctl oracle@macbook2:/u01/app/oracle/oradata/DGUARD/standby_control01.ctl
oracle@macbook2's password:
standby_control01.ctl 100% 6896KB 3.4MB/s 00:02
$ scp /u01/app/oracle/product/10.2.0/db_1/dbs/initDGUARD.ora oracle@macbook2:/u01/app/oracle/product/10.2.0/db_1/dbs/initDGUARD.ora
oracle@macbook2's password:
initDGUARD.ora 100% 1510 1.5KB/s 00:00
$ scp orapwDGUARD oracle@macbook2:/u01/app/oracle/product/10.2.0/db_1/dbs/orapwDGUARD
oracle@macbook2's password:
orapwDGUARD 100% 1536 1.5KB/s 00:00


On the standby machine, edit the initDGUARD.ora initialization parameter file that you have copied from primary database into the directory $ORACLE_HOME/dbs/ as in the following example:

DB_UNIQUE_NAME=STANDBY
SERVICE_NAMES=STANDBY
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIMARY,STANDBY)'
LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STANDBY'
LOG_ARCHIVE_DEST_2='SERVICE=PRIMARY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIMARY'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
## Standby Role Parameters ##
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=PRIMARY_macbook1
FAL_CLIENT=STANDBY_macbook2

Remember to edit also the following line:
*.control_files='/u01/app/oracle/oradata/DGUARD/standby_control01.ctl','/u01/app/oracle/oradata/DGUARD/standby_control02.ctl','/u01/app/oracle/oradata/DGUARD/standby_control03.ctl'

Then go to the directory $ORACLE_BASE/oradata/DGUARD/ and type the following commands to multiplex your standby control files:
$ cp standby_control01.ctl standby_control02.ctl
$ cp standby_control01.ctl standby_control03.ctl

DON'T change the value of the parameter DB_NAME='DGUARD' into DB_NAME='STANDBY' (you are using the control file provided by primary database and in that file there's written that the database name is DGUARD and not STANDBY.)
Just add DB_UNIQUE_NAME=STANDBY in the standby pfile.

Then, from the standby database machine, type as oracle user (see the picture below):
sqlplus "/ as sysdba"
create spfile from pfile='initDGUARD.ora'
startup mount;



Now it's time to configure the listeners and the net services names... that is ... create the file listener.ora and
tnsnames.ora on both the databases into the directory $ORACLE_HOME/network/admin/
Before, because I experienced problems make sure the loopback entry in /etc/hosts is not missing when you start the listener:
127.0.0.1 localhost

and make sure you have the libaio-devel package installed on both machine.
On hostname macbook1 type as root user (see the picture below):
rpm -q libaio-devel
and eventually install the package mounting the ISO 3
rpm -Uvh libaio-devel-0.3.105-2.i386.rpm



On macbook2 type as root user (see the picture below):
rpm -q libaio-devel
and eventually install the package mounting the ISO 3
rpm -Uvh libaio-devel-0.3.105-2.i386.rpm



Create the following listener.ora file into the directory $ORACLE_HOME/network/admin/ of the primary machine (see the picture below):
# listener.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = macbook1)(PORT = 1521))
)
)

Create the following tnsnames.ora file into the directory $ORACLE_HOME/network/admin/ of the primary machine (macbook1 has 192.168.0.10 as static IP, macbook2 has 192.168.0.11 as static IP):
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

STANDBY_MACBOOK2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.11)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = STANDBY)
)
)



Create the following listener.ora file into the directory $ORACLE_HOME/network/admin/ of the standby machine (see the picture below):
# listener.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = macbook2)(PORT = 1521))
)
)

Create the following tnsnames.ora file into the directory $ORACLE_HOME/network/admin/ of the standby machine (macbook1 has 192.168.0.10 as static IP, macbook2 has 192.168.0.11 as static IP):
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

PRIMARY_MACBOOK1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.10)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = STANDBY)
)
)




Click here to read the next post