----------------------------------------------------------------------------------------------------------------------------
Primary database name: prim on rac6 machine
Standby database name: stand on rac6 machine
----------------------------------------------------------------------------------------------------------------------------
The Enviroment :
2 Linux servers, Oracle Distribution 2.6.9-55 EL i686 i386 GNU/Linux
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
ssh is configured for user oracle on both nodes
Oracle Home is on identical path on both nodes
Implementation notes:
Once you have your primary database up and running these are the steps to follow:
1. Enable Forced Logging
2. Create a Password File
3. Configure a Standby Redo Log
4. Enable Archiving
5. Set Primary Database Initialization Parameters
Having followed these steps to implement the Physical Standby you need to follow these steps:
1. Create a Control File for the Standby Database
2. Backup the Primary Database and transfer a copy to the Standby node.
3. Prepare an Initialization Parameter File for the Standby Database
4. Configure the listener and tnsnames to support the database on both nodes
5. Set Up the Environment to Support the Standby Database on the standby node.
6. Start the Physical Standby Database
7. Verify the Physical Standby Database Is Performing Properly
----------------------------------------------------------------------------------------------------------------------------
Primary Database Steps
----------------------------------------------------------------------------------------------------------------------------
SQL> archive log list; Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 0
Current log sequence 1
SQL> select name from v$database;
NAME
---------
PRIM
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/oracle/10gdg/oradata/prim/system01.dbf
/oracle/10gdg/oradata/prim/undotbs01.dbf
/oracle/10gdg/oradata/prim/sysaux01.dbf
/oracle/10gdg/oradata/prim/users01.dbf
SQL> show parameters unique NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string prim
----------------------------------------------------------------------------------------------------------------------------
Enable Forced Logging : In order to implement Standby Database we enable 'Forced Logging'. This option ensures that even in the event that a 'nologging' operation is done, force logging takes precedence and all operations are logged into the redo logs.
----------------------------------------------------------------------------------------------------------------------------
SQL> ALTER DATABASE FORCE LOGGING; Database altered.
----------------------------------------------------------------------------------------------------------------------------
Create a Password File : A password file must be created on the Primary and copied over to the Standby site. The sys password must be identical on both sites. This is a key pre requisite in order to be able to ship and apply archived logs from Primary to Standby.
----------------------------------------------------------------------------------------------------------------------------
[oracle@rac6 ~]$ cd $ORACLE_HOME/dbs
[oracle@rac6 dbs]$ orapwd file=orapwprim password=oracle force=y
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE
----------------------------------------------------------------------------------------------------------------------------
Configure a Standby Redo Log : A Standby Redo log is added to enable Data Guard Maximum Availability and Maximum Protection modes. It is important to configure the Standby Redo Logs (SRL) with the same size as the online redo logs. If you are not using OMF's you then must pass the full qualified name.
----------------------------------------------------------------------------------------------------------------------------
SQL> select group#,type,member from v$logfile;
GROUP# TYPE MEMBER
---------- ------- ----------------------------------------
3 ONLINE /oracle/10gdg/oradata/prim/redo03.log
2 ONLINE /oracle/10gdg/oradata/prim/redo02.log
1 ONLINE /oracle/10gdg/oradata/prim/redo01.log
SQL> select bytes from v$log;
BYTES
----------
52428800
52428800
52428800
SQL> ALTER DATABASE ADD STANDBY LOGFILE
GROUP 4 'oracle/10gdg/oradata/prim/redo04.log' size 50m;
Database altered.
SQL> SELECT GROUP#,TYPE,MEMBER FROM V$LOGFILE;
GROUP# TYPE MEMBER
---------- ------- ----------------------------------------
3 ONLINE /oracle/10gdg/oradata/prim/redo03.log
2 ONLINE /oracle/10gdg/oradata/prim/redo02.log
1 ONLINE /oracle/10gdg/oradata/prim/redo01.log
4 STANDBY /oracle/10gdg/oradata/prim/redo04.log
Note: you have to create this standby redolog in standby database also.
----------------------------------------------------------------------------------------------------------------------------
Set Primary Database Initialization Parameters : Data Guard must use spfile, in order to configure it we create and configure the standby parameters on a regular pfile, and once it is ready we convert it to an spfile. Several init.ora parameters control the behavior of a Data Guard environment. In this example the Primary database init.ora is configured so that it can hold both roles, as Primary or Standby.
----------------------------------------------------------------------------------------------------------------------------
SQL> CREATE PFILE FROM SPFILE;
----------------------------------------------------------------------------------------------------------------------------
Edit the pfile to add the standby parameters, here shown highlighted:
----------------------------------------------------------------------------------------------------------------------------
prim.__db_cache_size=100663296
prim.__java_pool_size=4194304
prim.__large_pool_size=4194304
prim.__shared_pool_size=54525952
prim.__streams_pool_size=0
*.audit_file_dest='/oracle/10gdg/admin/prim/adump'
*.background_dump_dest='/oracle/10gdg/admin/prim/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/oracle/10gdg/oradata/prim/control01.ctl','/oracle/10gdg/oradata/prim/control02.ctl','/oracle/10gdg/oradata/prim/control03.ctl'
*.core_dump_dest='/oracle/10gdg/admin/prim/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='prim'
*.db_recovery_file_dest='/oracle/10gdg/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.job_queue_processes=10
*.dispatchers='(PROTOCOL=TCP) (SERVICE=primXDB)'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/oracle/10gdg/admin/prim/udump'
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.sga_target=167772160
*.DB_UNIQUE_NAME='prim'
*.INSTANCE_NAME='prim'
*.DB_FILE_NAME_CONVERT='stand','prim'*.INSTANCE_NAME='prim'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(prim,stand)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/oracle/10gdg/admin/prim/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prim'
*.LOG_ARCHIVE_DEST_2='SERVICE=stand VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stand'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.LOG_ARCHIVE_FORMAT='log%t_%s_%r.arc'
*.LOG_FILE_NAME_CONVERT='stand','prim'
*.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
*.SERVICE_NAMES='prim'
*.STANDBY_FILE_MANAGEMENT='AUTO'*.FAL_CLIENT='prim'
*.FAL_SERVER='stand'
*.FAL_CLIENT='prim'
----------------------------------------------------------------------------------------------------------------------------
Once the new parameter file is ready we create from it the spfile:
----------------------------------------------------------------------------------------------------------------------------
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.SQL> startup nomount pfile='$ORACLE_HOME/dbs/initprim.ora'
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 96470608 bytes
Database Buffers 184549376 bytes
Redo Buffers 2973696 bytes
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> create spfile from pfile;
File created.
----------------------------------------------------------------------------------------------------------------------------
Enable Archiving: On 10g you can enable archive log mode by mounting the database and executing the archivelog command:
----------------------------------------------------------------------------------------------------------------------------
SQL> startup mount
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 96470608 bytes
Database Buffers 184549376 bytes
Redo Buffers 2973696 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 /oracle/10gdg/admin/prim/arch
Oldest online log sequence 14
Next log sequence to archive 16
Current log sequence 16
----------------------------------------------------------------------------------------------------------------------------
Standby Database Steps:
----------------------------------------------------------------------------------------------------------------------------
create standby database using backup of the primary database datafiles,redologs, controlfile by RMAN compare with user managed backup, rman is comfortable and flexible method.
[oracle@rac6 ~]$ . oraenv
ORACLE_SID = [oracle] ? prim
[oracle@rac6 ~]$ rman target/
Recovery Manager: Release 10.2.0.1.0 - Production on Mon Nov 22 19:41:18 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: PRIM (DBID=4027893585)
----------------------------------------------------------------------------------------------------------------------------
Take the backup of the primary database :
----------------------------------------------------------------------------------------------------------------------------
RMAN> backup full database plus archivelog
----------------------------------------------------------------------------------------------------------------------------
Take the backup of controlfile for standby :
----------------------------------------------------------------------------------------------------------------------------
RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY;
RMAN> BACKUP ARCHIVELOG ALL;
----------------------------------------------------------------------------------------------------------------------------
SCP the backup pieces to the same location as that of the primary :
----------------------------------------------------------------------------------------------------------------------------
[oracle@rac6 ~]$ cd /oracle/10gdg/flash_recovery_area/PRIM/backupset/2010_11_21/
[oracle@rac6 2010_11_21]$ ls -ltr
total 662848
-rw-r----- 1 oracle oinstall 123472896 Nov 21 14:23 o1_mf_annnn_TAG20101121T142316_6gkqhxj3_.bkp
-rw-r----- 1 oracle oinstall 540319744 Nov 21 14:24 o1_mf_nnndf_TAG20101121T142327_6gkqj7f8_.bkp
-rw-r----- 1 oracle oinstall 7143424 Nov 21 14:24 o1_mf_ncsnf_TAG20101121T142327_6gkqkoxx_.bkp
-rw-r----- 1 oracle oinstall 22528 Nov 21 14:24 o1_mf_annnn_TAG20101121T142416_6gkqksmc_.bkp
-rw-r----- 1 oracle oinstall 7110656 Nov 21 14:25 o1_mf_ncnnf_TAG20101121T142459_6gkqm403_.bkp
[oracle@rac6 2010_11_21] scp * oracle@rac1:/oracle/10gdg/flash_recovery_area/PRIM/backupset/2010_11_21/
File created.
----------------------------------------------------------------------------------------------------------------------------
On the standby node create the required directories to get the datafiles :
----------------------------------------------------------------------------------------------------------------------------
mkdir -p /oracle/10gdg/flash_recovery_area/PRIM/backupset/2010_11_21/
mkdir -p /oracle/10gdg/flash_recovery_area/PRIM/onlinelog
mkdir -p /oracle/10gdg/admin/prim/adump
mkdir -p /oracle/10gdg/admin/prim/arch
mkdir -p /oracle/10gdg/oradata/prim/
mkdir -p /oracle/10gdg/admin/prim/bdump
mkdir -p /oracle/10gdg/admin/prim/cdump
mkdir -p /oracle/10gdg/admin/prim/udump
----------------------------------------------------------------------------------------------------------------------------
Prepare an Initialization Parameter File for the Standby Database:
----------------------------------------------------------------------------------------------------------------------------
stand.__db_cache_size=100663296
stand.__java_pool_size=4194304
stand.__large_pool_size=4194304
stand.__shared_pool_size=54525952
stand.__streams_pool_size=0
*.audit_file_dest='/oracle/10gdg/admin/stand/adump'
*.background_dump_dest='/oracle/10gdg/admin/stand/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/oracle/10gdg/oradata/stand/control01.ctl','/oracle/10gdg/oradata/stand/control02.ctl','/oracle/10gdg/oradata/stand/control03.ctl'
*.core_dump_dest='/oracle/10gdg/admin/stand/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_recovery_file_dest='/oracle/10gdg/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=standXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/oracle/10gdg/admin/stand/udump'
*.sga_target=167772160
*.DB_FILE_NAME_CONVERT='prim','stand'
*.DB_NAME='prim'
*.DB_UNIQUE_NAME='stand'
*.INSTANCE_NAME='stand'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(prim,stand)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/oracle/10gdg/admin/stand/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stand'
*.LOG_ARCHIVE_DEST_2='SERVICE=prim VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prim'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.LOG_ARCHIVE_FORMAT='log%t_%s_%r.arc'
*.LOG_FILE_NAME_CONVERT='prim','stand'
*.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
*.SERVICE_NAMES='stand'
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.FAL_CLIENT='stand'
*.FAL_SERVER='prim'
#on rac6 machine : primary database
# Listener in primary
PRIM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac6)(PORT = 1521))
)
SID_LIST_PRIM =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = prim_DGMGRL)
(ORACLE_HOME = /oracle/10gdg)
(SID_NAME = prim)
)
)
#tnsnames in primary
STAND =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac7)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = stand)
)
)
PRIM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac6)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prim)
)
)
#on rac7 machine : standby database
# Listener in Standby
STAND =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac7)(PORT = 1521))
)
SID_LIST_STAND =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = stand_DGMGRL)
(ORACLE_HOME = /oracle/10gdg)
(SID_NAME = stand)
)
)
#tnsnames in primary
STAND =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac7)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = stand)
)
)
PRIM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac6)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prim)
)
)
----------------------------------------------------------------------------------------------------------------------------
Start the listener and check tnsping on both nodes to both services:
----------------------------------------------------------------------------------------------------------------------------
#on machine rac6 : primary database
[oracle@rac6 ~]$ lsnrctl stop prim
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 22-NOV-2010 20:17:12
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac6)(PORT=1521)))
The command completed successfully
[oracle@rac6 ~]$ lsnrctl start prim
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 22-NOV-2010 20:17:22
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Starting /oracle/10gdg/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /oracle/10gdg/network/admin/listener.ora
Log messages written to /oracle/10gdg/network/log/prim.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac6)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac6)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias prim
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 22-NOV-2010 20:17:22
Uptime 0 days 0 hr. 0 min. 1 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/10gdg/network/admin/listener.ora
Listener Log File /oracle/10gdg/network/log/prim.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac6)(PORT=1521)))
Services Summary...
Service "prim_DGMGRL" has 1 instance(s).
Instance "prim", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
#on machine rac7 : standby database
[oracle@rac7 ~]$ lsnrctl stop stand
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 22-NOV-2010 20:18:40
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac7)(PORT=1521)))
The command completed successfully
[oracle@rac7 ~]$ lsnrctl start stand
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 22-NOV-2010 20:19:18
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Starting /oracle/10gdg/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /oracle/10gdg/network/admin/listener.ora
Log messages written to /oracle/10gdg/network/log/stand.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac7)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac7)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias stand
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 22-NOV-2010 20:19:18
Uptime 0 days 0 hr. 0 min. 1 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/10gdg/network/admin/listener.ora
Listener Log File /oracle/10gdg/network/log/stand.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac7)(PORT=1521)))
Services Summary...
Service "stand_DGMGRL" has 1 instance(s).
Instance "stand", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
----------------------------------------------------------------------------------------------------------------------------
Create a passwordfile for the standby:
----------------------------------------------------------------------------------------------------------------------------
[oracle@rac1 ~]$ orapwd file=$ORACLE_HOME/dbs/orapwblack password=oracle
note: sys password must be identical for both primary and standby database
----------------------------------------------------------------------------------------------------------------------------Startup nomount the Standby database : Nomount the standby instance in preparation for the duplicate operation: Startup nomount the Standby database and generate an spfile.
----------------------------------------------------------------------------------------------------------------------------
[oracle@rac7 ~]$ . oraenv
ORACLE_SID = [whiteowl] ? stand
[oracle@rac7 ~]$ sqlplus '/as sysdba'
SQL> startup nomount pfile='$ORACLE_HOME/dbs/initstand.ora'
ORACLE instance started.
SQL> create spfile from pfile;
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
----------------------------------------------------------------------------------------------------------------------------Create the standby database using rman:
----------------------------------------------------------------------------------------------------------------------------
[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [oracle] ? black
[oracle@rac1 ~]$ rman target=sys/oracle@white auxiliary=/
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Jan 21 00:43:11 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: PRIM (DBID=3603807872)
connected to auxiliary database: PRIM (not mounted)
RMAN> DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK;
----------------------------------------------------------------------------------------------------------------------------Start the redo apply:
----------------------------------------------------------------------------------------------------------------------------
SQL> alter database recover managed standby database disconnect from session;
----------------------------------------------------------------------------------------------------------------------------Test the configuration by generating archive logs from the primary and then querying the standby to see if the logs are being successfully applied.
----------------------------------------------------------------------------------------------------------------------------
On the Primary:
#on machine rac6 : primary database
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle/10gdg/admin/prim/arch
Oldest online log sequence 14
Next log sequence to archive 16
Current log sequence 16
#on machine rac7 : standby database
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle/10gdg/admin/stand/arch
Oldest online log sequence 14
Next log sequence to archive 0
Current log sequence 16
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG
ORDER BY SEQUENCE#;
----------------------------------------------------------------------------------------------------------------------------Verify the sync between primary and standby:
----------------------------------------------------------------------------------------------------------------------------
From primary
sql > select thread#,max(sequence#) from v$archived_log where archived='YES' group by thread#;
From StandbySQL > select thread#,max(sequence#) from v$archived_log where applied ='YES' group by thread#;
----------------------------------------------------------------------------------------------------------------------------Stop the managed recovery process on the standby:
----------------------------------------------------------------------------------------------------------------------------
SQL> alter database recover managed standby database cancel;
Primary database name: prim on rac6 machine
Standby database name: stand on rac6 machine
----------------------------------------------------------------------------------------------------------------------------
The Enviroment :
2 Linux servers, Oracle Distribution 2.6.9-55 EL i686 i386 GNU/Linux
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
ssh is configured for user oracle on both nodes
Oracle Home is on identical path on both nodes
Implementation notes:
Once you have your primary database up and running these are the steps to follow:
1. Enable Forced Logging
2. Create a Password File
3. Configure a Standby Redo Log
4. Enable Archiving
5. Set Primary Database Initialization Parameters
Having followed these steps to implement the Physical Standby you need to follow these steps:
1. Create a Control File for the Standby Database
2. Backup the Primary Database and transfer a copy to the Standby node.
3. Prepare an Initialization Parameter File for the Standby Database
4. Configure the listener and tnsnames to support the database on both nodes
5. Set Up the Environment to Support the Standby Database on the standby node.
6. Start the Physical Standby Database
7. Verify the Physical Standby Database Is Performing Properly
----------------------------------------------------------------------------------------------------------------------------
Primary Database Steps
----------------------------------------------------------------------------------------------------------------------------
SQL> archive log list; Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 0
Current log sequence 1
SQL> select name from v$database;
NAME
---------
PRIM
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/oracle/10gdg/oradata/prim/system01.dbf
/oracle/10gdg/oradata/prim/undotbs01.dbf
/oracle/10gdg/oradata/prim/sysaux01.dbf
/oracle/10gdg/oradata/prim/users01.dbf
SQL> show parameters unique NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string prim
----------------------------------------------------------------------------------------------------------------------------
Enable Forced Logging : In order to implement Standby Database we enable 'Forced Logging'. This option ensures that even in the event that a 'nologging' operation is done, force logging takes precedence and all operations are logged into the redo logs.
----------------------------------------------------------------------------------------------------------------------------
SQL> ALTER DATABASE FORCE LOGGING; Database altered.
----------------------------------------------------------------------------------------------------------------------------
Create a Password File : A password file must be created on the Primary and copied over to the Standby site. The sys password must be identical on both sites. This is a key pre requisite in order to be able to ship and apply archived logs from Primary to Standby.
----------------------------------------------------------------------------------------------------------------------------
[oracle@rac6 ~]$ cd $ORACLE_HOME/dbs
[oracle@rac6 dbs]$ orapwd file=orapwprim password=oracle force=y
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE
----------------------------------------------------------------------------------------------------------------------------
Configure a Standby Redo Log : A Standby Redo log is added to enable Data Guard Maximum Availability and Maximum Protection modes. It is important to configure the Standby Redo Logs (SRL) with the same size as the online redo logs. If you are not using OMF's you then must pass the full qualified name.
----------------------------------------------------------------------------------------------------------------------------
SQL> select group#,type,member from v$logfile;
GROUP# TYPE MEMBER
---------- ------- ----------------------------------------
3 ONLINE /oracle/10gdg/oradata/prim/redo03.log
2 ONLINE /oracle/10gdg/oradata/prim/redo02.log
1 ONLINE /oracle/10gdg/oradata/prim/redo01.log
SQL> select bytes from v$log;
BYTES
----------
52428800
52428800
52428800
SQL> ALTER DATABASE ADD STANDBY LOGFILE
GROUP 4 'oracle/10gdg/oradata/prim/redo04.log' size 50m;
Database altered.
SQL> SELECT GROUP#,TYPE,MEMBER FROM V$LOGFILE;
GROUP# TYPE MEMBER
---------- ------- ----------------------------------------
3 ONLINE /oracle/10gdg/oradata/prim/redo03.log
2 ONLINE /oracle/10gdg/oradata/prim/redo02.log
1 ONLINE /oracle/10gdg/oradata/prim/redo01.log
4 STANDBY /oracle/10gdg/oradata/prim/redo04.log
Note: you have to create this standby redolog in standby database also.
----------------------------------------------------------------------------------------------------------------------------
Set Primary Database Initialization Parameters : Data Guard must use spfile, in order to configure it we create and configure the standby parameters on a regular pfile, and once it is ready we convert it to an spfile. Several init.ora parameters control the behavior of a Data Guard environment. In this example the Primary database init.ora is configured so that it can hold both roles, as Primary or Standby.
----------------------------------------------------------------------------------------------------------------------------
SQL> CREATE PFILE FROM SPFILE;
----------------------------------------------------------------------------------------------------------------------------
Edit the pfile to add the standby parameters, here shown highlighted:
----------------------------------------------------------------------------------------------------------------------------
prim.__db_cache_size=100663296
prim.__java_pool_size=4194304
prim.__large_pool_size=4194304
prim.__shared_pool_size=54525952
prim.__streams_pool_size=0
*.audit_file_dest='/oracle/10gdg/admin/prim/adump'
*.background_dump_dest='/oracle/10gdg/admin/prim/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/oracle/10gdg/oradata/prim/control01.ctl','/oracle/10gdg/oradata/prim/control02.ctl','/oracle/10gdg/oradata/prim/control03.ctl'
*.core_dump_dest='/oracle/10gdg/admin/prim/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='prim'
*.db_recovery_file_dest='/oracle/10gdg/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.job_queue_processes=10
*.dispatchers='(PROTOCOL=TCP) (SERVICE=primXDB)'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/oracle/10gdg/admin/prim/udump'
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.sga_target=167772160
*.DB_UNIQUE_NAME='prim'
*.INSTANCE_NAME='prim'
*.DB_FILE_NAME_CONVERT='stand','prim'*.INSTANCE_NAME='prim'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(prim,stand)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/oracle/10gdg/admin/prim/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prim'
*.LOG_ARCHIVE_DEST_2='SERVICE=stand VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stand'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.LOG_ARCHIVE_FORMAT='log%t_%s_%r.arc'
*.LOG_FILE_NAME_CONVERT='stand','prim'
*.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
*.SERVICE_NAMES='prim'
*.STANDBY_FILE_MANAGEMENT='AUTO'*.FAL_CLIENT='prim'
*.FAL_SERVER='stand'
*.FAL_CLIENT='prim'
----------------------------------------------------------------------------------------------------------------------------
Once the new parameter file is ready we create from it the spfile:
----------------------------------------------------------------------------------------------------------------------------
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.SQL> startup nomount pfile='$ORACLE_HOME/dbs/initprim.ora'
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 96470608 bytes
Database Buffers 184549376 bytes
Redo Buffers 2973696 bytes
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> create spfile from pfile;
File created.
----------------------------------------------------------------------------------------------------------------------------
Enable Archiving: On 10g you can enable archive log mode by mounting the database and executing the archivelog command:
----------------------------------------------------------------------------------------------------------------------------
SQL> startup mount
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 96470608 bytes
Database Buffers 184549376 bytes
Redo Buffers 2973696 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 /oracle/10gdg/admin/prim/arch
Oldest online log sequence 14
Next log sequence to archive 16
Current log sequence 16
----------------------------------------------------------------------------------------------------------------------------
Standby Database Steps:
----------------------------------------------------------------------------------------------------------------------------
create standby database using backup of the primary database datafiles,redologs, controlfile by RMAN compare with user managed backup, rman is comfortable and flexible method.
[oracle@rac6 ~]$ . oraenv
ORACLE_SID = [oracle] ? prim
[oracle@rac6 ~]$ rman target/
Recovery Manager: Release 10.2.0.1.0 - Production on Mon Nov 22 19:41:18 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: PRIM (DBID=4027893585)
----------------------------------------------------------------------------------------------------------------------------
Take the backup of the primary database :
----------------------------------------------------------------------------------------------------------------------------
RMAN> backup full database plus archivelog
----------------------------------------------------------------------------------------------------------------------------
Take the backup of controlfile for standby :
----------------------------------------------------------------------------------------------------------------------------
RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY;
RMAN> BACKUP ARCHIVELOG ALL;
----------------------------------------------------------------------------------------------------------------------------
SCP the backup pieces to the same location as that of the primary :
----------------------------------------------------------------------------------------------------------------------------
[oracle@rac6 ~]$ cd /oracle/10gdg/flash_recovery_area/PRIM/backupset/2010_11_21/
[oracle@rac6 2010_11_21]$ ls -ltr
total 662848
-rw-r----- 1 oracle oinstall 123472896 Nov 21 14:23 o1_mf_annnn_TAG20101121T142316_6gkqhxj3_.bkp
-rw-r----- 1 oracle oinstall 540319744 Nov 21 14:24 o1_mf_nnndf_TAG20101121T142327_6gkqj7f8_.bkp
-rw-r----- 1 oracle oinstall 7143424 Nov 21 14:24 o1_mf_ncsnf_TAG20101121T142327_6gkqkoxx_.bkp
-rw-r----- 1 oracle oinstall 22528 Nov 21 14:24 o1_mf_annnn_TAG20101121T142416_6gkqksmc_.bkp
-rw-r----- 1 oracle oinstall 7110656 Nov 21 14:25 o1_mf_ncnnf_TAG20101121T142459_6gkqm403_.bkp
[oracle@rac6 2010_11_21] scp * oracle@rac1:/oracle/10gdg/flash_recovery_area/PRIM/backupset/2010_11_21/
File created.
NOTE:The primary and standby database location for backup folder must be same.
for eg: /u01/app/oracle/backup folder----------------------------------------------------------------------------------------------------------------------------
On the standby node create the required directories to get the datafiles :
----------------------------------------------------------------------------------------------------------------------------
mkdir -p /oracle/10gdg/flash_recovery_area/PRIM/backupset/2010_11_21/
mkdir -p /oracle/10gdg/flash_recovery_area/PRIM/onlinelog
mkdir -p /oracle/10gdg/admin/prim/adump
mkdir -p /oracle/10gdg/admin/prim/arch
mkdir -p /oracle/10gdg/oradata/prim/
mkdir -p /oracle/10gdg/admin/prim/bdump
mkdir -p /oracle/10gdg/admin/prim/cdump
mkdir -p /oracle/10gdg/admin/prim/udump
----------------------------------------------------------------------------------------------------------------------------
Prepare an Initialization Parameter File for the Standby Database:
----------------------------------------------------------------------------------------------------------------------------
stand.__db_cache_size=100663296
stand.__java_pool_size=4194304
stand.__large_pool_size=4194304
stand.__shared_pool_size=54525952
stand.__streams_pool_size=0
*.audit_file_dest='/oracle/10gdg/admin/stand/adump'
*.background_dump_dest='/oracle/10gdg/admin/stand/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/oracle/10gdg/oradata/stand/control01.ctl','/oracle/10gdg/oradata/stand/control02.ctl','/oracle/10gdg/oradata/stand/control03.ctl'
*.core_dump_dest='/oracle/10gdg/admin/stand/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_recovery_file_dest='/oracle/10gdg/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=standXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/oracle/10gdg/admin/stand/udump'
*.sga_target=167772160
*.DB_FILE_NAME_CONVERT='prim','stand'
*.DB_NAME='prim'
*.DB_UNIQUE_NAME='stand'
*.INSTANCE_NAME='stand'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(prim,stand)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/oracle/10gdg/admin/stand/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stand'
*.LOG_ARCHIVE_DEST_2='SERVICE=prim VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prim'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.LOG_ARCHIVE_FORMAT='log%t_%s_%r.arc'
*.LOG_FILE_NAME_CONVERT='prim','stand'
*.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
*.SERVICE_NAMES='stand'
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.FAL_CLIENT='stand'
*.FAL_SERVER='prim'
----------------------------------------------------------------------------------------------------------------------------Configure the listener and tnsnames to support the database on both nodes
----------------------------------------------------------------------------------------------------------------------------#on rac6 machine : primary database
# Listener in primary
PRIM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac6)(PORT = 1521))
)
SID_LIST_PRIM =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = prim_DGMGRL)
(ORACLE_HOME = /oracle/10gdg)
(SID_NAME = prim)
)
)
#tnsnames in primary
STAND =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac7)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = stand)
)
)
PRIM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac6)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prim)
)
)
#on rac7 machine : standby database
# Listener in Standby
STAND =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac7)(PORT = 1521))
)
SID_LIST_STAND =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = stand_DGMGRL)
(ORACLE_HOME = /oracle/10gdg)
(SID_NAME = stand)
)
)
#tnsnames in primary
STAND =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac7)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = stand)
)
)
PRIM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac6)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prim)
)
)
----------------------------------------------------------------------------------------------------------------------------
Start the listener and check tnsping on both nodes to both services:
----------------------------------------------------------------------------------------------------------------------------
#on machine rac6 : primary database
[oracle@rac6 ~]$ lsnrctl stop prim
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 22-NOV-2010 20:17:12
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac6)(PORT=1521)))
The command completed successfully
[oracle@rac6 ~]$ lsnrctl start prim
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 22-NOV-2010 20:17:22
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Starting /oracle/10gdg/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /oracle/10gdg/network/admin/listener.ora
Log messages written to /oracle/10gdg/network/log/prim.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac6)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac6)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias prim
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 22-NOV-2010 20:17:22
Uptime 0 days 0 hr. 0 min. 1 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/10gdg/network/admin/listener.ora
Listener Log File /oracle/10gdg/network/log/prim.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac6)(PORT=1521)))
Services Summary...
Service "prim_DGMGRL" has 1 instance(s).
Instance "prim", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
#on machine rac7 : standby database
[oracle@rac7 ~]$ lsnrctl stop stand
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 22-NOV-2010 20:18:40
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac7)(PORT=1521)))
The command completed successfully
[oracle@rac7 ~]$ lsnrctl start stand
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 22-NOV-2010 20:19:18
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Starting /oracle/10gdg/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /oracle/10gdg/network/admin/listener.ora
Log messages written to /oracle/10gdg/network/log/stand.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac7)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac7)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias stand
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 22-NOV-2010 20:19:18
Uptime 0 days 0 hr. 0 min. 1 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/10gdg/network/admin/listener.ora
Listener Log File /oracle/10gdg/network/log/stand.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac7)(PORT=1521)))
Services Summary...
Service "stand_DGMGRL" has 1 instance(s).
Instance "stand", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
----------------------------------------------------------------------------------------------------------------------------
Create a passwordfile for the standby:
----------------------------------------------------------------------------------------------------------------------------
[oracle@rac1 ~]$ orapwd file=$ORACLE_HOME/dbs/orapwblack password=oracle
note: sys password must be identical for both primary and standby database
----------------------------------------------------------------------------------------------------------------------------Startup nomount the Standby database : Nomount the standby instance in preparation for the duplicate operation: Startup nomount the Standby database and generate an spfile.
----------------------------------------------------------------------------------------------------------------------------
[oracle@rac7 ~]$ . oraenv
ORACLE_SID = [whiteowl] ? stand
[oracle@rac7 ~]$ sqlplus '/as sysdba'
SQL> startup nomount pfile='$ORACLE_HOME/dbs/initstand.ora'
ORACLE instance started.
SQL> create spfile from pfile;
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
----------------------------------------------------------------------------------------------------------------------------Create the standby database using rman:
----------------------------------------------------------------------------------------------------------------------------
[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [oracle] ? black
[oracle@rac1 ~]$ rman target=sys/oracle@white auxiliary=/
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Jan 21 00:43:11 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: PRIM (DBID=3603807872)
connected to auxiliary database: PRIM (not mounted)
RMAN> DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK;
----------------------------------------------------------------------------------------------------------------------------Start the redo apply:
----------------------------------------------------------------------------------------------------------------------------
SQL> alter database recover managed standby database disconnect from session;
----------------------------------------------------------------------------------------------------------------------------Test the configuration by generating archive logs from the primary and then querying the standby to see if the logs are being successfully applied.
----------------------------------------------------------------------------------------------------------------------------
On the Primary:
SQL> alter system switch logfile;
SQL> alter system archive log current;#on machine rac6 : primary database
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle/10gdg/admin/prim/arch
Oldest online log sequence 14
Next log sequence to archive 16
Current log sequence 16
#on machine rac7 : standby database
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle/10gdg/admin/stand/arch
Oldest online log sequence 14
Next log sequence to archive 0
Current log sequence 16
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG
ORDER BY SEQUENCE#;
----------------------------------------------------------------------------------------------------------------------------Verify the sync between primary and standby:
----------------------------------------------------------------------------------------------------------------------------
From primary
sql > select thread#,max(sequence#) from v$archived_log where archived='YES' group by thread#;
From StandbySQL > select thread#,max(sequence#) from v$archived_log where applied ='YES' group by thread#;
----------------------------------------------------------------------------------------------------------------------------Stop the managed recovery process on the standby:
----------------------------------------------------------------------------------------------------------------------------
SQL> alter database recover managed standby database cancel;
Regards,
Sukhwinder Singh
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.