Prerequisites:
Need to install Oracle Database 11.2.0.1
I have used following softwares for this activity:-
OS: Oracle Linux 5.8
DB: 11.2.0.1
Primary Database
Database Name: dgt01
Database Unique Name: dgt01
Host: leopard
Oracle Home: /u01/app/oracle/product/11.2.0.1/db_home
Standby Database
Database Name: dgt01
Database Unique Name: dgt01_stdby
Host: tiger
Oracle Home: /u01/app/oracle/product/11.2.0.1/db_home
1) Ensure forced logging in enabled on Primary Database
SQL> select force_logging from v$database;
FOR
---
NO
SQL> alter database force logging;
Database altered.
SQL> select force_logging from v$database;
FOR
---
YES
2) Ensure archivelog is enabled on Primary Database
SQL> select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 535662592 bytes
Fixed Size 1337720 bytes
Variable Size 180356744 bytes
Database Buffers 348127232 bytes
Redo Buffers 5840896 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/admin/dgt01/arch
Oldest online log sequence 17
Next log sequence to archive 19
Current log sequence 19
3) Configure Standby Redo Logs on Primary Database (NOTE: the size should match redo logs and the number should be 1 more than the total redo logs)
SQL> alter database add standby logfile '/d01/oradata/dgt01/srl01.log' size 50m;
Database altered.
SQL> alter database add standby logfile '/d01/oradata/dgt01/srl02.log' size 50m;
Database altered.
SQL> alter database add standby logfile '/d01/oradata/dgt01/srl03.log' size 50m;
Database altered.
SQL> alter database add standby logfile '/d01/oradata/dgt01/srl04.log' size 50m;
Database altered.
4) Set LOG_ARCHIVE_CONFIG on Primary Database
SQL> alter system set log_archive_config='dg_config=(dgt01,dgt01_stdby)';
System altered.
5) Set LOG_ARCHIVE_DEST_2 on Primary Database
SQL> alter system set log_archive_dest_2='service=dgt01_stdby async valid_for=(online_logfile,primary_role) db_unique_name=dgt01_stdby';
6) Update tnsnames.ora on Primary Database & Physical Standby Database
********************************************************************************************
DGT01 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = leopard)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dgt01)
)
)
DGT01_STDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = tiger)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dgt01_stdby)
)
)
*********************************************************************
7) Update listener.ora on Primary Database & Physical Standby Database
- Note: If using below script to update listener.ora on Physical Standby Database, insert correct HOST(i.e. tiger as per this example)
*****************************************************************************************
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = dgt01)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0.1/db_home)
(SID_NAME = dgt01)
)
(SID_DESC =
(GLOBAL_DBNAME = dgt01_stdby)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0.1/db_home)
(SID_NAME = dgt01_stdby)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = leopard)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/oracle
*********************************************************************
Reload listener.ora on Primary Database & Physical Standby Database
[oracle@leopard ~]$ lsnrctl reload
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 28-MAY-2014 05:43:58
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=leopard)(PORT=1521)))
The command completed successfully
NOTE: To ensure listener.ora and tnsnames.ora are updated successfully, perform tnsping on both the hosts
Location SD:
8) Create Password File on Standby Database
- Navigate to $ORACLE_HOME/dbs
$ cd /u01/app/oracle/product/11.2.0.1/db_home/dbs
$ orapwd file=orapwdgt01_stdby password=oracle entries=5
9) Create Initialization Parameter File on Standby Database
- Navigate to $ORACLE_HOME/dbs
$ cd /u01/app/oracle/product/11.2.0.1/db_home/dbs
$ echo DB_NAME=dgt01_stdby > initdgt01_stdby.ora
10) Create Directory Structures on Standby Database
- Navigate to $ORACLE_BASE/admin
$ cd /u01/app/oracle/admin
$ mkdir dgt01_stdby
$ cd dgt01_stdby
$ mkdir adump arch pfile
- Navigate to datafile location
$ cd /d01/oradata
$ mkdir dgt01_stdby
11) Set Environment Variable on Standby Database (Note: I usually use a go profile. Have shared it at the end of this article)
$ export ORACLE_SID=dgt01_stdby
$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0.1/db_home
12) Startup Standby Database in No Mount mode using initialization file.
$ sqlplus / as sysdba
SQL> startup nomount pfile=$ORACLE_HOME/dbs/initdgt01_stdby.ora
SQL> exit
13) On Primary Database host set environment variable
$ export ORACLE_SID=dgt01
$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0.1/db_home
14) Connect to RMAN on Primary Database
$ rman target / auxiliary sys/oracle@dgt01_stdby
RMAN>
15) Execute following run block
RMAN> run {
2> allocate channel prmy1 type disk;
3> allocate channel prmy2 type disk;
4> allocate auxiliary channel stby type disk;
5> duplicate target database for standby from active database
6> spfile parameter_value_convert 'dgt01','dgt01_stdby'
7> set db_unique_name='dgt01_stdby'
8> set db_file_name_convert='dgt01','dgt01_stdby'
9> set log_file_name_convert='dgt01','dgt01_stdby'
10> set control_files='/d01/oradata/dgt01_stdby/control01.ctl','/d01/oradata/dgt01_stdby/control02.ctl'
11> set log_archive_max_processes='5'
12> set fal_client='dgt01_stdby'
13> set fal_server='dgt01'
14> set standby_file_management='AUTO'
15> set log_archive_config='dg_config=(dgt01,dgt01_stdby)'
16> set log_archive_dest_1='location=/u01/app/oracle/admin/dgt01_stdby/arch'
17> set log_archive_dest_2='service=dgt01 async valid_for=(online_logfile,primary_role) db_unique_name=dgt01';
18> }
RMAN> exit
16) Verify Standby Database status
SQL> set lines 150
SQL> select name, db_unique_name, open_mode, database_role, protection_mode from v$database;
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE
--------- --------------- -------------------- ---------------- --------------------
DGT01 dgt01_stdby MOUNTED PHYSICAL STANDBY MAXIMUM PERFORMANCE
16) Enable MRP on Standby Database
SQL> alter database recover managed standby database using current logfile disconnect;
Database altered.
17) On Primary Database perform log switch
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/admin/dgt01/arch
Oldest online log sequence 17
Next log sequence to archive 19
Current log sequence 19
18) On Standby Database verify the redo logs are received, archived and applied
SQL> select sequence#, first_time, applied from v$archived_log;
SEQUENCE# FIRST_TIM APPLIED
---------- --------- ---------
15 28-MAY-14 YES
14 28-MAY-14 YES
16 28-MAY-14 YES
17 28-MAY-14 YES
18 28-MAY-14 YES
Now, we have a functional Standby Database on Maximum Performance Mode.
Need to Configure Active Dataguard - CLICK HERE
=============================================================================
Common Errors while Standby Database Creation:
RMAN-03002: failure of Duplicate Db command .....
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on prmy1 channel at .....
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-01017: invalid username/password; logon denied
ORA-17629: Cannot connect to the remote database server
Please ensure you mention username and password for target while connecting to RMAN
So, instead of;
$ rman target / auxiliary sys/oracle@dgt01_stdby
Connect to RMAN as (NOTE: Ensure orapwdgt01 file is updated with same password which you are using for target);
$ rman target sys/oracle auxiliary sys/oracle@dgt01_stdby
****************
**go_profile**
****************
#Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
ORACLE_HOSTNAME=leopard; export ORACLE_HOSTNAME
ORACLE_UNQNAME=dgt01_stdby; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0.1/db_home; export ORACLE_HOME
ORACLE_SID=dgt01_stdby; export ORACLE_SID
TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
echo
echo Oracle Home is set to `echo $ORACLE_HOME`
echo Oracle SID is set to `echo $ORACLE_SID`
echo
Need to install Oracle Database 11.2.0.1
I have used following softwares for this activity:-
OS: Oracle Linux 5.8
DB: 11.2.0.1
Primary Database
Database Name: dgt01
Database Unique Name: dgt01
Host: leopard
Oracle Home: /u01/app/oracle/product/11.2.0.1/db_home
Standby Database
Database Name: dgt01
Database Unique Name: dgt01_stdby
Host: tiger
Oracle Home: /u01/app/oracle/product/11.2.0.1/db_home
1) Ensure forced logging in enabled on Primary Database
SQL> select force_logging from v$database;
FOR
---
NO
SQL> alter database force logging;
Database altered.
SQL> select force_logging from v$database;
FOR
---
YES
2) Ensure archivelog is enabled on Primary Database
SQL> select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 535662592 bytes
Fixed Size 1337720 bytes
Variable Size 180356744 bytes
Database Buffers 348127232 bytes
Redo Buffers 5840896 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/admin/dgt01/arch
Oldest online log sequence 17
Next log sequence to archive 19
Current log sequence 19
3) Configure Standby Redo Logs on Primary Database (NOTE: the size should match redo logs and the number should be 1 more than the total redo logs)
SQL> alter database add standby logfile '/d01/oradata/dgt01/srl01.log' size 50m;
Database altered.
SQL> alter database add standby logfile '/d01/oradata/dgt01/srl02.log' size 50m;
Database altered.
SQL> alter database add standby logfile '/d01/oradata/dgt01/srl03.log' size 50m;
Database altered.
SQL> alter database add standby logfile '/d01/oradata/dgt01/srl04.log' size 50m;
Database altered.
4) Set LOG_ARCHIVE_CONFIG on Primary Database
SQL> alter system set log_archive_config='dg_config=(dgt01,dgt01_stdby)';
System altered.
5) Set LOG_ARCHIVE_DEST_2 on Primary Database
SQL> alter system set log_archive_dest_2='service=dgt01_stdby async valid_for=(online_logfile,primary_role) db_unique_name=dgt01_stdby';
6) Update tnsnames.ora on Primary Database & Physical Standby Database
********************************************************************************************
DGT01 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = leopard)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dgt01)
)
)
DGT01_STDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = tiger)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dgt01_stdby)
)
)
*********************************************************************
7) Update listener.ora on Primary Database & Physical Standby Database
- Note: If using below script to update listener.ora on Physical Standby Database, insert correct HOST(i.e. tiger as per this example)
*****************************************************************************************
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = dgt01)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0.1/db_home)
(SID_NAME = dgt01)
)
(SID_DESC =
(GLOBAL_DBNAME = dgt01_stdby)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0.1/db_home)
(SID_NAME = dgt01_stdby)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = leopard)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/oracle
*********************************************************************
Reload listener.ora on Primary Database & Physical Standby Database
[oracle@leopard ~]$ lsnrctl reload
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 28-MAY-2014 05:43:58
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=leopard)(PORT=1521)))
The command completed successfully
NOTE: To ensure listener.ora and tnsnames.ora are updated successfully, perform tnsping on both the hosts
Location SD:
8) Create Password File on Standby Database
- Navigate to $ORACLE_HOME/dbs
$ cd /u01/app/oracle/product/11.2.0.1/db_home/dbs
$ orapwd file=orapwdgt01_stdby password=oracle entries=5
9) Create Initialization Parameter File on Standby Database
- Navigate to $ORACLE_HOME/dbs
$ cd /u01/app/oracle/product/11.2.0.1/db_home/dbs
$ echo DB_NAME=dgt01_stdby > initdgt01_stdby.ora
10) Create Directory Structures on Standby Database
- Navigate to $ORACLE_BASE/admin
$ cd /u01/app/oracle/admin
$ mkdir dgt01_stdby
$ cd dgt01_stdby
$ mkdir adump arch pfile
- Navigate to datafile location
$ cd /d01/oradata
$ mkdir dgt01_stdby
11) Set Environment Variable on Standby Database (Note: I usually use a go profile. Have shared it at the end of this article)
$ export ORACLE_SID=dgt01_stdby
$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0.1/db_home
12) Startup Standby Database in No Mount mode using initialization file.
$ sqlplus / as sysdba
SQL> startup nomount pfile=$ORACLE_HOME/dbs/initdgt01_stdby.ora
SQL> exit
13) On Primary Database host set environment variable
$ export ORACLE_SID=dgt01
$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0.1/db_home
14) Connect to RMAN on Primary Database
$ rman target / auxiliary sys/oracle@dgt01_stdby
RMAN>
15) Execute following run block
RMAN> run {
2> allocate channel prmy1 type disk;
3> allocate channel prmy2 type disk;
4> allocate auxiliary channel stby type disk;
5> duplicate target database for standby from active database
6> spfile parameter_value_convert 'dgt01','dgt01_stdby'
7> set db_unique_name='dgt01_stdby'
8> set db_file_name_convert='dgt01','dgt01_stdby'
9> set log_file_name_convert='dgt01','dgt01_stdby'
10> set control_files='/d01/oradata/dgt01_stdby/control01.ctl','/d01/oradata/dgt01_stdby/control02.ctl'
11> set log_archive_max_processes='5'
12> set fal_client='dgt01_stdby'
13> set fal_server='dgt01'
14> set standby_file_management='AUTO'
15> set log_archive_config='dg_config=(dgt01,dgt01_stdby)'
16> set log_archive_dest_1='location=/u01/app/oracle/admin/dgt01_stdby/arch'
17> set log_archive_dest_2='service=dgt01 async valid_for=(online_logfile,primary_role) db_unique_name=dgt01';
18> }
RMAN> exit
16) Verify Standby Database status
SQL> set lines 150
SQL> select name, db_unique_name, open_mode, database_role, protection_mode from v$database;
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE
--------- --------------- -------------------- ---------------- --------------------
DGT01 dgt01_stdby MOUNTED PHYSICAL STANDBY MAXIMUM PERFORMANCE
16) Enable MRP on Standby Database
SQL> alter database recover managed standby database using current logfile disconnect;
Database altered.
17) On Primary Database perform log switch
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/admin/dgt01/arch
Oldest online log sequence 17
Next log sequence to archive 19
Current log sequence 19
18) On Standby Database verify the redo logs are received, archived and applied
SQL> select sequence#, first_time, applied from v$archived_log;
SEQUENCE# FIRST_TIM APPLIED
---------- --------- ---------
15 28-MAY-14 YES
14 28-MAY-14 YES
16 28-MAY-14 YES
17 28-MAY-14 YES
18 28-MAY-14 YES
Now, we have a functional Standby Database on Maximum Performance Mode.
Need to Configure Active Dataguard - CLICK HERE
=============================================================================
Common Errors while Standby Database Creation:
RMAN-03002: failure of Duplicate Db command .....
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on prmy1 channel at .....
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-01017: invalid username/password; logon denied
ORA-17629: Cannot connect to the remote database server
Please ensure you mention username and password for target while connecting to RMAN
So, instead of;
$ rman target / auxiliary sys/oracle@dgt01_stdby
Connect to RMAN as (NOTE: Ensure orapwdgt01 file is updated with same password which you are using for target);
$ rman target sys/oracle auxiliary sys/oracle@dgt01_stdby
****************
**go_profile**
****************
#Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
ORACLE_HOSTNAME=leopard; export ORACLE_HOSTNAME
ORACLE_UNQNAME=dgt01_stdby; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0.1/db_home; export ORACLE_HOME
ORACLE_SID=dgt01_stdby; export ORACLE_SID
TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
echo
echo Oracle Home is set to `echo $ORACLE_HOME`
echo Oracle SID is set to `echo $ORACLE_SID`
echo
No comments:
Post a Comment