Target database : orcl (10.0.0.160) (primary database)
Auxiliary database: orclstby (10.0.0.161) (standby database)
Check that the primary database is in archivelog mode.
Sql > select log_mode from v$database;
If it is noarchivelog mode, switch it to archivelog mode.
Sql > SHUTDOWN IMMEDIATE
Sql > STARTUP MOUNT;
Sql > ALTER DATABASE ARCHIVELOG;
Sql > ALTER DATABASE OPEN;
Sql> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1=’LOCATION=/u01/orcl_archives’ SCOPE=SPFILE;
Enabled forced logging by issuing the following command
Sql > ALTER DATABASE FORCE LOGGING;
Add standby logfiles on primary database (it is recommended to create standby redo logs on primary database to make things easier during switchover and failover)
Sql > ALTER DATABASE ADD STANDBY LOGFILE GROUP 4
‘/u01/app/oracle/oradata/orcl/redo04.log’ SIZE 15M;
Sql > ALTER DATABASE ADD STANDBY LOGFILE GROUP 5
‘/u01/app/oracle/oradata/orcl/redo05.log’ SIZE 15M;
Sql > ALTER DATABASE ADD STANDBY LOGFILE GROUP 6
‘/u01/app/oracle/oradata/orcl/redo06.log’ SIZE 15M;
The DB_NAME of the standby database will be the same as that of the primary, but it must have a different DB_UNIQUE_NAME value. The DB_UNIQUE_NAME values of the primary and standby database should be used in the DG_CONFIG setting of the LOG_ARCHIVE_CONFIG parameter. For this example, the standby database will have the value "DB11G_STBY".
Sql > ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orclstby)';
Set suitable remote archive log destinations.
Sql > ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=orclstby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orclstby';
Sql > ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
The LOG_ARCHIVE_FORMAT and LOG_ARCHIVE_MAX_PROCESSES parameters must be set to appropriate values and the REMOTE_LOGIN_PASSWORDFILE must be set to exclusive.
Sql > ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;
Sql > ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30;
Sql > ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
In addition to the previous setting, it is recommended to make sure the primary is ready to switch roles to become a standby. For that to work properly we need to set the following parameters. Adjust the *_CONVERT parameters to account for your filename and patch differences between the servers.
Sql > ALTER SYSTEM SET FAL_SERVER=orclstby;
Sql > ALTER SYSTEM SET DB_FILE_NAME_CONVERT='orclstby','orcl' SCOPE=SPFILE;
Sql > ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='orclstby','orcl' SCOPE=SPFILE;
Sql > ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
Remember, some of the parameters are not modifiable, so the database will need to be restarted before they take effect.
PREPARE STANDBY DATABASE
· Install oracle 11g R2 binaries on standby machine i.e on 10.0.0.161
· Create directories on standby at the desired locations (refer to fig 1.2)
· Copy password file to standby database(password file of my primary database is orapworcl ,so I copied it to standby database and changed its name to orapworclstby)
· Configure tnsnames.ora and listener.ora on both primary (orcl) and standby (orclstby) database
Listener.ora of orcl (primary database)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = orcl)
) )
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.160)(PORT = 1521))
))
ADR_BASE_LISTENER = /u01/app/oracle
tnsnames.ora of orcl (primary database)
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.160)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
(INSTANCE_NAME = orcl)
))
ORCLSTBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.161)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orclstby)
(INSTANCE_NAME = orclstby)
) )
Listener.ora of orclstby (standby database)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orclstby)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = orclstby)
))
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.161)(PORT = 1521))
))
ADR_BASE_LISTENER = /u01/app/oracle
tnsnames.ora of orclstby (standby database)
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.160)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
(INSTANCE_NAME = orcl)
))
ORCLSTBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.161)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orclstby)
(INSTANCE_NAME = orclstby)
))
· Create pfile for standby database
On primary database, execute
Sql > CREATE PFILE='/u01/initorclstby.ora' FROM SPFILE;
Alter the above pfile and transfer it to standby database
So , pfile (initorclstby.ora) of my standby database (orclstby) is :
orclstby.__db_cache_size=109051904
orclstby.__java_pool_size=4194304
orclstby.__large_pool_size=4194304
orclstby.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orclstby.__pga_aggregate_target=184549376
orclstby.__sga_target=251658240
orclstby.__shared_io_pool_size=0
orclstby.__shared_pool_size=125829120
orclstby.__streams_pool_size=0
audit_file_dest='/u01/app/oracle/admin/orclstby/adump'
audit_trail='db'
compatible='11.2.0.0.0'
control_files='/u01/app/oracle/oradata/orclstby/control01.ctl','/u01/app/oracle/flash_recovery_area/orclstby/control02.ctl'
db_block_size=8192
db_domain='localdomain'
db_unique_name='orclstby'
db_file_name_convert='orcl','orclstby'
db_name='orcl'
db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
db_recovery_file_dest_size=4039114752
diagnostic_dest='/u01/app/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
fal_server='orcl'
log_archive_config='DG_CONFIG=(orcl,orclstby)'
log_archive_dest_1='location=/u01/orcl_archives'
log_archive_dest_2='SERVICE=orcl ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'
log_archive_dest_state_2='ENABLE'
log_archive_format='%t_%s_%r.arc'
log_archive_max_processes=30
log_archive_start=TRUE
log_file_name_convert='orcl','orclstby'
memory_target=433061888
open_cursors=300
processes=150
remote_login_passwordfile='EXCLUSIVE'
standby_file_management='AUTO'
undo_tablespace='UNDOTBS1'
TAKING BACKUP OF PRIMARY DATABASE USING RMAN
-bash-3.2$ rman
Recovery Manager: Release 11.2.0.1.0 - Production on Thu Nov 24 14:41:20 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
RMAN > connect target sys@orcl
target database Password:
connected to target database: ORCL (DBID=1295591331)
RMAN > backup database include current controlfile for standby plus archivelog;
Starting backup at 24-NOV-11
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=101 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=3 RECID=1 STAMP=767885366
input archived log thread=1 sequence=4 RECID=2 STAMP=767885391
input archived log thread=1 sequence=5 RECID=3 STAMP=767886484
input archived log thread=1 sequence=6 RECID=4 STAMP=767886616
input archived log thread=1 sequence=7 RECID=6 STAMP=767899462
input archived log thread=1 sequence=8 RECID=5 STAMP=767899353
input archived log thread=1 sequence=9 RECID=7 STAMP=767906298
input archived log thread=1 sequence=10 RECID=8 STAMP=767907740
input archived log thread=1 sequence=11 RECID=9 STAMP=767908826
input archived log thread=1 sequence=12 RECID=10 STAMP=767908911
input archived log thread=1 sequence=13 RECID=11 STAMP=767916311
input archived log thread=1 sequence=14 RECID=12 STAMP=767917336
input archived log thread=1 sequence=15 RECID=13 STAMP=767950266
input archived log thread=1 sequence=16 RECID=14 STAMP=767986293
input archived log thread=1 sequence=17 RECID=15 STAMP=768003827
input archived log thread=1 sequence=18 RECID=16 STAMP=768036665
input archived log thread=1 sequence=19 RECID=17 STAMP=768059623
input archived log thread=1 sequence=20 RECID=18 STAMP=768060626
input archived log thread=1 sequence=21 RECID=19 STAMP=768062538
channel ORA_DISK_1: starting piece 1 at 24-NOV-11
channel ORA_DISK_1: finished piece 1 at 24-NOV-11
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_24/o1_mf_annnn_TAG20111124T144222_7dw2my5x_.bkp tag=TAG20111124T144222 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:38
Finished backup at 24-NOV-11
Starting backup at 24-NOV-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 24-NOV-11
channel ORA_DISK_1: finished piece 1 at 24-NOV-11
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_24/o1_mf_nnndf_TAG20111124T144408_7dw2q1qv_.bkp tag=TAG20111124T144408 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:03:04
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including standby control file in backup set
channel ORA_DISK_1: starting piece 1 at 24-NOV-11
channel ORA_DISK_1: finished piece 1 at 24-NOV-11
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_24/o1_mf_ncnnf_TAG20111124T144408_7dw2ww2x_.bkp tag=TAG20111124T144408 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 24-NOV-11
Starting backup at 24-NOV-11
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=22 RECID=20 STAMP=768062838
channel ORA_DISK_1: starting piece 1 at 24-NOV-11
channel ORA_DISK_1: finished piece 1 at 24-NOV-11
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_24/o1_mf_annnn_TAG20111124T144718_7dw2x0bk_.bkp tag=TAG20111124T144718 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 24-NOV-11
Starting Control File Autobackup at 24-NOV-11
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2011_11_24/o1_mf_n_768062841_7dw2x23z_.bkp comment=NONE
Finished Control File Autobackup at 24-NOV-11
RMAN>
COPY ALL BACKUP AND ARCHIVES FROM PRIMARY DATABASE TO STANDBY DATABASE
Location of archives of orcl (primary database) on 10.0.0.160 is /u01/ and these archives are copied on standby 10.0.0.161 at exactly the same location (/u01/).
· Location of backupset of orcl (primary) on 10.0.0.160 is /u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_22/ , /u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_24/
and
/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2011_11_22/ ,
/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2011_11_24/
And so, they are copied on standby 10.0.0.161 at exactly the same location i.e
Location of backupset of orclstby (standby) on 10.0.0.161 is /u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_22/ , /u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_24/
and
/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2011_11_22/ ,
/u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_24/
So, directories location on primary i.e orcl (10.0.0.160) are:
Fig 1.1
And directories location on standby i.e orclstby (10.0.0.161) are:
Fig.1.2
Notice: in above figures the path i.e /u01/app/oracle/flash_recovery_area/ORCL/autobackup/ on primary (orcl 10.0.0.160) and /u01/app/oracle/flash_recovery_area/ORCL/backupset/ on standby (orclstby 10.0.0.161) are exactly same.
CREATE STANDBY DATABASE (orclstby) USING DUPLICATE COMMAND
On standby database , orclstby (i.e at 10.0.0.161)
-bash-3.2$ export ORACLE_SID=orclstby
-bash-3.2$ sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 24 13:47:15 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Enter user-name: sys@orclstby as sysdba
Enter password:
Connected to an idle instance.
SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initorclstby.ora';
ORACLE instance started.
Total System Global Area 435224576 bytes
Fixed Size 1337044 bytes
Variable Size 318769452 bytes
Database Buffers 109051904 bytes
Redo Buffers 6066176 bytes
-bash-3.2$ rman
RMAN> connect target
RMAN> duplicate target database for standby nofilenamecheck dorecover;
Starting Duplicate Db at 24-NOV-11
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK
contents of Memory Script:
{
set until scn 1041555;
restore clone standby controlfile;
}
executing Memory Script
executing command: SET until clause
Starting restore at 24-NOV-11
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/autobackup/2011_11_24/o1_mf_n_768062841_7dw2x23z_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2011_11_24/o1_mf_n_768062841_7dw2x23z_.bkp tag=TAG20111124T144721
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/u01/app/oracle/oradata/orclstby/control01.ctl
output file name=/u01/app/oracle/flash_recovery_area/orclstby/control02.ctl
Finished restore at 24-NOV-11
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
set until scn 1041555;
set newname for tempfile 1 to
"/u01/app/oracle/oradata/orclstby/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/orclstby/system01.dbf";
set newname for datafile 2 to
"/u01/app/oracle/oradata/orclstby/sysaux01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/orclstby/undotbs01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/orclstby/users01.dbf";
set newname for datafile 5 to
"/u01/app/oracle/oradata/orclstby/example01.dbf";
restore
clone database
;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/orclstby/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 24-NOV-11
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/orclstby/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/orclstby/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/orclstby/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/orclstby/users01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/orclstby/example01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_24/o1_mf_nnndf_TAG20111124T144408_7dw2q1qv_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_24/o1_mf_nnndf_TAG20111124T144408_7dw2q1qv_.bkp tag=TAG20111124T144408
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:07
Finished restore at 24-NOV-11
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=3 STAMP=768070406 file name=/u01/app/oracle/oradata/orclstby/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=4 STAMP=768070406 file name=/u01/app/oracle/oradata/orclstby/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=768070406 file name=/u01/app/oracle/oradata/orclstby/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=768070406 file name=/u01/app/oracle/oradata/orclstby/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=7 STAMP=768070406 file name=/u01/app/oracle/oradata/orclstby/example01.dbf
contents of Memory Script:
{
set until scn 1041555;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 24-NOV-11
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 22 is already on disk as file /u01/orcl_archives1_22_767810598.arc
archived log for thread 1 with sequence 23 is already on disk as file /u01/orcl_archives1_23_767810598.arc
archived log for thread 1 with sequence 24 is already on disk as file /u01/orcl_archives1_24_767810598.arc
archived log file name=/u01/orcl_archives1_22_767810598.arc thread=1 sequence=22
archived log file name=/u01/orcl_archives1_23_767810598.arc thread=1 sequence=23
archived log file name=/u01/orcl_archives1_24_767810598.arc thread=1 sequence=24
media recovery complete, elapsed time: 00:00:08
Finished recover at 24-NOV-11
Finished Duplicate Db at 24-NOV-11
RMAN>
Test Log Transport by executing command ALTER SYSTEM SWITCH LOGFILE on primary database and then checking SEQUENCE# at standby database
So , on standby database ::
SQL> select sequence#,first_time,next_time from v$archived_log order by sequence#;
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
25 24-NOV-11 24-NOV-11
26 24-NOV-11 24-NOV-11
27 24-NOV-11 24-NOV-11
SQL> select protection_mode from v$database;
PROTECTION_MODE
--------------------
MAXIMUM PERFORMANCE
SQL> select open_mode,database_role from v$database;
OPEN_MODE DATABASE_ROLE
-------------------- ----------------
MOUNTED PHYSICAL STANDBY
SQL> shu immediate
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 435224576 bytes
Fixed Size 1337044 bytes
Variable Size 318769452 bytes
Database Buffers 109051904 bytes
Redo Buffers 6066176 bytes
Database mounted.
Database opened.
SQL> select open_mode,database_role from v$database;
OPEN_MODE DATABASE_ROLE
-------------------- ----------------
READ ONLY PHYSICAL STANDBY
Start the apply process on standby server
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select open_mode,database_role from v$database;
OPEN_MODE DATABASE_ROLE
-------------------- ----------------
READ ONLY WITH APPLY PHYSICAL STANDBY
