This is one in the list of my Example Series in this blog. Check this blog for more possible examples
This is an example Physical Standby Database Creation. This is mostly from the logs I created. You can use it as a guide or hint. It does not show all the step-by-step, but it should help. Because it is logs, some processes may be duplicated. I will try to clean it up as much as I can.
This is based on Oracle 10.2.0.2 on Windows XP on my test database. Both Primary and Standby is on the Same Machine. As you know, this is a test database and show not be configured to be on same database for Production. This does not show how the Primary Database was created.
To complete this, I followed Oracle® Data Guard Concepts and Administration
Here is the Primary Database Initialisation Paranmeter
testdb.__db_cache_size=37748736
testdb.__java_pool_size=4194304
testdb.__large_pool_size=4194304
testdb.__shared_pool_size=79691776
testdb.__streams_pool_size=0
*._awr_flush_threshold_metrics=FALSE
*._system_trig_enabled=FALSE
*.audit_file_dest='C:\oracle\admin\testdb\adump'
*.audit_trail='DB'
*.background_dump_dest='C:\oracle\admin\testdb\bdump'
*.compatible='10.2.0.2.0'
*.control_files='C:\oracle\oradata\testdb\control01.ctl','C:\oracle\oradata\testdb\control02.ctl','C:\oracle\oradata\testdb\control03.ctl'
*.core_dump_dest='C:\oracle\admin\testdb\cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.DB_FILE_NAME_CONVERT='standby','testdb'
*.db_name='testdb'
*.db_recovery_file_dest='C:\oracle\flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.db_unique_name='TESTDB'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testdbXDB)'
*.fal_client='TESTDB'
*.fal_server='STANDBY'
*.job_queue_processes=4
*.log_archive_config='DG_CONFIG=(testdb,standby)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST LGWR MANDATORY REOPEN=5 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)'
*.log_archive_dest_2='SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.log_archive_format='ARC%S_%R.%T'
*.LOG_FILE_NAME_CONVERT='C:\oracle\oradata\standby','C:\oracle\oradata\testdb'
*.nls_language='ENGLISH'
*.nls_territory='UNITED KINGDOM'
*.open_cursors=300
*.pga_aggregate_target=12777216
*.processes=100
*.remote_login_passwordfile='EXCLUSIVE'
*.service_names='testdb'
*.sga_target=127772160
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='C:\oracle\admin\testdb\udump'
Create Standby Database Intialisation Parameter from Primary
SQL> create pfile='C:\oracle\ora10g\database\INITSTANDBYDB.ora' from spfile;File created.
Here is the amended Standby Initialisation Paranmeter
standby.__db_cache_size=29360128
standby.__java_pool_size=8388608
standby.__large_pool_size=4194304
standby.__shared_pool_size=83886080
standby.__streams_pool_size=0
*._awr_flush_threshold_metrics=FALSE
*._system_trig_enabled=FALSE
*.audit_file_dest='C:\oracle\admin\standby\adump'
*.audit_trail='DB'
*.background_dump_dest='C:\oracle\admin\standby\bdump'
*.compatible='10.2.0.2.0'
*.control_files='C:\oracle\oradata\standby\control01.ctl','C:\oracle\oradata\standby\control02.ctl','C:\oracle\oradata\standby\control03.ctl'
*.core_dump_dest='C:\oracle\admin\standby\cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.DB_FILE_NAME_CONVERT='testdb','standby'
*.db_name='testdb'
*.db_recovery_file_dest='C:\oracle\flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.db_unique_name='standby'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=standbyXDB)'
*.fal_client='STANDBY'
*.fal_server='TESTDB'
*.job_queue_processes=4
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(testdb,standby)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST LGWR MANDATORY REOPEN=5 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)'
*.log_archive_dest_2='SERVICE=testdb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testdb'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.log_archive_format='ARC%S_%R.%T'
*.LOG_FILE_NAME_CONVERT='C:\oracle\oradata\testdb','C:\oracle\oradata\standby'
*.nls_language='ENGLISH'
*.nls_territory='UNITED KINGDOM'
*.open_cursors=300
*.pga_aggregate_target=12777216
*.processes=100
*.remote_login_passwordfile='EXCLUSIVE'
*.service_names='standby'
*.sga_target=127772160
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='C:\oracle\admin\standby\udump'
Create Password File for Standby
C:\>orapwd file=C:\oracle\ora10g\database\PWDstandbydb.ora password=oracl3
Swtich Logfile
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
Get Current Log Sequence
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
117
SQL> create pfile='C:\oracle\ora10g\database\INITSTANDBYDB.ora' from spfile;
File created.
Connect to RMAN (I am not using Catalog)
C:\Documents\dbmsdirect\Testing\StandBy>rman target /
Recovery Manager: Release 10.2.0.2.0 - Production on Wed Nov 28 11:04:10 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: TESTDB (DBID=2403050596)
Startup MOUNT the Primary Database
RMAN> STARTUP MOUNT
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 130023424 bytes
Fixed Size 1288148 bytes
Variable Size 96471084 bytes
Database Buffers 29360128 bytes
Redo Buffers 2904064 bytes
Backup current controlfile for Standby
RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY;
Starting backup at 28-NOV-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=100 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including standby control file in backupset
channel ORA_DISK_1: starting piece 1 at 28-NOV-07
channel ORA_DISK_1: finished piece 1 at 28-NOV-07
piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\BACKUPSET\2007_11_28\O1_MF_NCNNF_TAG20071128T110608_3NTLX352_.BKP tag=TAG20071128T110608 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:05
Finished backup at 28-NOV-07
Open Primary Database
RMAN> ALTER DATABASE OPEN;
database opened
Archive the current log so to ensure that backup is consistent and recoverable
RMAN> SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';
sql statement: ALTER SYSTEM ARCHIVE LOG CURRENT
Backup Database and all archivelogs
RMAN> BACKUP ARCHIVELOG ALL NOT BACKED UP 1 TIMES;
Starting backup at 28-NOV-07
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=99 recid=72 stamp=637507805
input archive log thread=1 sequence=100 recid=73 stamp=637508008
input archive log thread=1 sequence=101 recid=74 stamp=637508021
input archive log thread=1 sequence=102 recid=75 stamp=637509664
input archive log thread=1 sequence=103 recid=76 stamp=637509671
input archive log thread=1 sequence=104 recid=77 stamp=637509676
input archive log thread=1 sequence=105 recid=78 stamp=638121625
input archive log thread=1 sequence=106 recid=79 stamp=639147639
input archive log thread=1 sequence=107 recid=80 stamp=639661045
input archive log thread=1 sequence=108 recid=81 stamp=639661317
input archive log thread=1 sequence=109 recid=82 stamp=639661904
input archive log thread=1 sequence=110 recid=83 stamp=639662160
input archive log thread=1 sequence=111 recid=84 stamp=639668842
input archive log thread=1 sequence=112 recid=85 stamp=639832029
input archive log thread=1 sequence=113 recid=86 stamp=639832036
channel ORA_DISK_1: starting piece 1 at 28-NOV-07
channel ORA_DISK_1: finished piece 1 at 28-NOV-07
piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\BACKUPSET\2007_11_28\O1_MF_ANNNN_TAG20071128T110717_3NTLZ9RT_.BKP tag=TAG20071128T110717 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:06
Finished backup at 28-NOV-07
RMAN> backup database plus archivelog delete input;
Starting backup at 28-NOV-07
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=99 recid=72 stamp=637507805
input archive log thread=1 sequence=100 recid=73 stamp=637508008
input archive log thread=1 sequence=101 recid=74 stamp=637508021
input archive log thread=1 sequence=102 recid=75 stamp=637509664
input archive log thread=1 sequence=103 recid=76 stamp=637509671
input archive log thread=1 sequence=104 recid=77 stamp=637509676
input archive log thread=1 sequence=105 recid=78 stamp=638121625
input archive log thread=1 sequence=106 recid=79 stamp=639147639
input archive log thread=1 sequence=107 recid=80 stamp=639661045
input archive log thread=1 sequence=108 recid=81 stamp=639661317
input archive log thread=1 sequence=109 recid=82 stamp=639661904
input archive log thread=1 sequence=110 recid=83 stamp=639662160
input archive log thread=1 sequence=111 recid=84 stamp=639668842
input archive log thread=1 sequence=112 recid=85 stamp=639832029
input archive log thread=1 sequence=113 recid=86 stamp=639832036
input archive log thread=1 sequence=114 recid=87 stamp=639833067
channel ORA_DISK_1: starting piece 1 at 28-NOV-07
channel ORA_DISK_1: finished piece 1 at 28-NOV-07
piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\BACKUPSET\2007_11_28\O1_MF_ANNNN_TAG20071128T112428_3NTMZH70_.BKP tag=TAG20071128T112428 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:57
channel ORA_DISK_1: deleting archive log(s)
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_99_3LMO6WJF_.ARC recid=72 stamp=637507805
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_100_3LMOF7HQ_.ARC recid=73 stamp=637508008
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_101_3LMOFN8Q_.ARC recid=74 stamp=637508021
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_102_3LMQ0ZGL_.ARC recid=75 stamp=637509664
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_103_3LMQ1756_.ARC recid=76 stamp=637509671
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_104_3LMQ1D0D_.ARC recid=77 stamp=637509676
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_08\O1_MF_1_105_3M6DNLSO_.ARC recid=78 stamp=638121625
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_20\O1_MF_1_106_3N5PMM5P_.ARC recid=79 stamp=639147639
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_26\O1_MF_1_107_3NOCZDMX_.ARC recid=80 stamp=639661045
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_26\O1_MF_1_108_3NOD7V4F_.ARC recid=81 stamp=639661317
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_26\O1_MF_1_109_3NODT9KL_.ARC recid=82 stamp=639661904
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_26\O1_MF_1_110_3NOF23DK_.ARC recid=83 stamp=639662160
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_26\O1_MF_1_111_3NOMM5PR_.ARC recid=84 stamp=639668842
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_28\O1_MF_1_112_3NTLYVL7_.ARC recid=85 stamp=639832029
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_28\O1_MF_1_113_3NTLZ4L0_.ARC recid=86 stamp=639832036
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_28\O1_MF_1_114_3NTMZCGZ_.ARC recid=87 stamp=639833067
Finished backup at 28-NOV-07
Starting backup at 28-NOV-07
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=C:\ORACLE\ORADATA\TESTDB\SYSTEM01.DBF
input datafile fno=00002 name=C:\ORACLE\ORADATA\TESTDB\UNDOTBS01.DBF
input datafile fno=00003 name=C:\ORACLE\ORADATA\TESTDB\SYSAUX01.DBF
input datafile fno=00004 name=C:\ORACLE\ORADATA\TESTDB\USERS01.DBF
input datafile fno=00006 name=C:\ORACLE\ORADATA\TESTDB\DISC_EUL1.ORA
input datafile fno=00005 name=C:\ORACLE\ORADATA\TESTDB\TBSALERT01.ORA
channel ORA_DISK_1: starting piece 1 at 28-NOV-07
channel ORA_DISK_1: finished piece 1 at 28-NOV-07
piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\BACKUPSET\2007_11_28\O1_MF_NNNDF_TAG20071128T112530_3NTN1CGF_.BKP tag=TAG20071128T112530 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:05
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 28-NOV-07
channel ORA_DISK_1: finished piece 1 at 28-NOV-07
piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\BACKUPSET\2007_11_28\O1_MF_NCSNF_TAG20071128T112530_3NTN5C4T_.BKP tag=TAG20071128T112530 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:06
Finished backup at 28-NOV-07
Starting backup at 28-NOV-07
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=115 recid=88 stamp=639833263
channel ORA_DISK_1: starting piece 1 at 28-NOV-07
channel ORA_DISK_1: finished piece 1 at 28-NOV-07
piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\BACKUPSET\2007_11_28\O1_MF_ANNNN_TAG20071128T112743_3NTN5JLT_.BKP tag=TAG20071128T112743 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archive log(s)
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_28\O1_MF_1_115_3NTN5GRZ_.ARC recid=88 stamp=639833263
Finished backup at 28-NOV-07
RMAN> backup current controlfile for standby format='C:\Documents\dbmsdirect\Testing\StandBy\stby_cfile.%U';
Starting backup at 28-NOV-07
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including standby control file in backupset
channel ORA_DISK_1: starting piece 1 at 28-NOV-07
channel ORA_DISK_1: finished piece 1 at 28-NOV-07
piece handle=C:\DOCUMENTS\dbmsdirect\TESTING\STANDBY\STBY_CFILE.09J265GJ_1_1 tag=TAG20071128T113338 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 28-NOV-07
RMAN> backup archivelog all delete input;
Starting backup at 28-NOV-07
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=116 recid=89 stamp=639833685
input archive log thread=1 sequence=117 recid=90 stamp=639833686
input archive log thread=1 sequence=118 recid=91 stamp=639833741
channel ORA_DISK_1: starting piece 1 at 28-NOV-07
channel ORA_DISK_1: finished piece 1 at 28-NOV-07
piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\BACKUPSET\2007_11_28\O1_MF_ANNNN_TAG20071128T113541_3NTNNHGQ_.BKP tag=TAG20071128T113541 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archive log(s)
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_28\O1_MF_1_116_3NTNLNWB_.ARC recid=89 stamp=639833685
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_28\O1_MF_1_117_3NTNLOXB_.ARC recid=90 stamp=639833686
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_28\O1_MF_1_118_3NTNNF9M_.ARC recid=91 stamp=639833741
Finished backup at 28-NOV-07
RMAN> exit
Connect to RMAN and Auxilliary
C:\Documents\dbmsdirect\Testing\StandBy>rman target / auxiliary sys/xxxx@standby
Recovery Manager: Release 10.2.0.2.0 - Production on Wed Nov 28 13:38:52 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: TESTDB (DBID=2403050596)
connected to auxiliary database: TESTDB (not mounted)
RMAN>
Use Diplicate Database to Create the Standby from Primary
RMAN> run {
2> set until sequence = 117 thread = 1;
3> allocate auxiliary channel ch1 type disk;
4> duplicate target database for standby dorecover nofilenamecheck ;
5> }
executing command: SET until clause
using target database control file instead of recovery catalog
allocated channel: ch1
channel ch1: sid=101 devtype=DISK
Starting Duplicate Db at 28-NOV-07
contents of Memory Script:
{
set until scn 2216367;
restore clone standby controlfile;
sql clone 'alter database mount standby database';
}
executing Memory Script
executing command: SET until clause
Starting restore at 28-NOV-07
channel ch1: starting datafile backupset restore
channel ch1: restoring control file
channel ch1: reading from backup piece C:\DOCUMENTS\dbmsdirect\TESTING\STANDBY\STBY_CFILE.09J265GJ_1_1
channel ch1: restored backup piece 1
piece handle=C:\DOCUMENTS\dbmsdirect\TESTING\STANDBY\STBY_CFILE.09J265GJ_1_1 tag=TAG20071128T113338
channel ch1: restore complete, elapsed time: 00:00:07
output filename=C:\ORACLE\ORADATA\STANDBY\CONTROL01.CTL
output filename=C:\ORACLE\ORADATA\STANDBY\CONTROL02.CTL
output filename=C:\ORACLE\ORADATA\STANDBY\CONTROL03.CTL
Finished restore at 28-NOV-07
sql statement: alter database mount standby database
contents of Memory Script:
{
set until scn 2216367;
set newname for tempfile 1 to
"C:\ORACLE\ORADATA\STANDBY\TEMP01.DBF";
switch clone tempfile all;
set newname for datafile 1 to
"C:\ORACLE\ORADATA\STANDBY\SYSTEM01.DBF";
set newname for datafile 2 to
"C:\ORACLE\ORADATA\STANDBY\UNDOTBS01.DBF";
set newname for datafile 3 to
"C:\ORACLE\ORADATA\STANDBY\SYSAUX01.DBF";
set newname for datafile 4 to
"C:\ORACLE\ORADATA\STANDBY\USERS01.DBF";
set newname for datafile 5 to
"C:\ORACLE\ORADATA\STANDBY\TBSALERT01.ORA";
set newname for datafile 6 to
"C:\ORACLE\ORADATA\STANDBY\DISC_EUL1.ORA";
restore
check readonly
clone database
;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
renamed temporary file 1 to C:\ORACLE\ORADATA\STANDBY\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
executing command: SET NEWNAME
Starting restore at 28-NOV-07
channel ch1: starting datafile backupset restore
channel ch1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\ORACLE\ORADATA\STANDBY\SYSTEM01.DBF
restoring datafile 00002 to C:\ORACLE\ORADATA\STANDBY\UNDOTBS01.DBF
restoring datafile 00003 to C:\ORACLE\ORADATA\STANDBY\SYSAUX01.DBF
restoring datafile 00004 to C:\ORACLE\ORADATA\STANDBY\USERS01.DBF
restoring datafile 00005 to C:\ORACLE\ORADATA\STANDBY\TBSALERT01.ORA
restoring datafile 00006 to C:\ORACLE\ORADATA\STANDBY\DISC_EUL1.ORA
channel ch1: reading from backup piece C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\BACKUPSET\2007_11_28\O1_MF_NNNDF_TAG20071128T112530_3NTN1CGF_.BKP
channel ch1: restored backup piece 1
piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\BACKUPSET\2007_11_28\O1_MF_NNNDF_TAG20071128T112530_3NTN1CGF_.BKP tag=TAG20071128T112530
channel ch1: restore complete, elapsed time: 00:02:25
Finished restore at 28-NOV-07
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy recid=7 stamp=639841305 filename=C:\ORACLE\ORADATA\STANDBY\SYSTEM01.DBF
datafile 2 switched to datafile copy
input datafile copy recid=8 stamp=639841305 filename=C:\ORACLE\ORADATA\STANDBY\UNDOTBS01.DBF
datafile 3 switched to datafile copy
input datafile copy recid=9 stamp=639841306 filename=C:\ORACLE\ORADATA\STANDBY\SYSAUX01.DBF
datafile 4 switched to datafile copy
input datafile copy recid=10 stamp=639841306 filename=C:\ORACLE\ORADATA\STANDBY\USERS01.DBF
datafile 5 switched to datafile copy
input datafile copy recid=11 stamp=639841306 filename=C:\ORACLE\ORADATA\STANDBY\TBSALERT01.ORA
datafile 6 switched to datafile copy
input datafile copy recid=12 stamp=639841306 filename=C:\ORACLE\ORADATA\STANDBY\DISC_EUL1.ORA
contents of Memory Script:
{
set until scn 2216367;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 28-NOV-07
starting media recovery
channel ch1: starting archive log restore to default destination
channel ch1: restoring archive log
archive log thread=1 sequence=115
channel ch1: reading from backup piece C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\BACKUPSET\2007_11_28\O1_MF_ANNNN_TAG20071128T112743_3NTN5JLT_.BKP
channel ch1: restored backup piece 1
piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\BACKUPSET\2007_11_28\O1_MF_ANNNN_TAG20071128T112743_3NTN5JLT_.BKP tag=TAG20071128T112743
channel ch1: restore complete, elapsed time: 00:00:04
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\STANDBY\ARCHIVELOG\2007_11_28\O1_MF_1_115_3NTW147J_.ARC thread=1 sequence=115
channel clone_default: deleting archive log(s)
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\STANDBY\ARCHIVELOG\2007_11_28\O1_MF_1_115_3NTW147J_.ARC recid=1 stamp=639841316
channel ch1: starting archive log restore to default destination
channel ch1: restoring archive log
archive log thread=1 sequence=116
channel ch1: reading from backup piece C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\BACKUPSET\2007_11_28\O1_MF_ANNNN_TAG20071128T113541_3NTNNHGQ_.BKP
channel ch1: restored backup piece 1
piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\BACKUPSET\2007_11_28\O1_MF_ANNNN_TAG20071128T113541_3NTNNHGQ_.BKP tag=TAG20071128T113541
channel ch1: restore complete, elapsed time: 00:00:02
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\STANDBY\ARCHIVELOG\2007_11_28\O1_MF_1_116_3NTW1BHF_.ARC thread=1 sequence=116
channel clone_default: deleting archive log(s)
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\STANDBY\ARCHIVELOG\2007_11_28\O1_MF_1_116_3NTW1BHF_.ARC recid=2 stamp=639841322
media recovery complete, elapsed time: 00:00:04
Finished recover at 28-NOV-07
Finished Duplicate Db at 28-NOV-07
released channel: ch1
RMAN>
To open a standby database for read-only access when it is currently shut down:
Start, mount, and open the database for read-only access using the following statement:
SQL> STARTUP;
To open a standby database for read-only access when it is currently performing Redo Apply:
Cancel Redo Apply:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Open the database for read-only access:
SQL> ALTER DATABASE OPEN;
To change the standby database from being open for read-only access to performing Redo Apply:
Terminate all active user sessions on the standby database.
Restart Redo Apply. To start Redo Apply, issue the following statement:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Switchovers Involving a Physical Standby Database
Step 1 Verify it is possible to perform a switchover.
SELECT SWITCHOVER_STATUS FROM V$DATABASE;
Step 2 Initiate the switchover on the primary database.
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
Step 3 Shut down and restart the former primary instance.
Shut down the former primary instance, and restart and mount the database:
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
Step 4 Verify the switchover status in the V$DATABASE view.
SELECT SWITCHOVER_STATUS FROM V$DATABASE;
Step 5 Switch the target physical standby database role to the primary role.
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
Step 6 Finish the transition of the standby database to the primary role.
ALTER DATABASE OPEN;
Step 7 If necessary, restart log apply services on the standby databases.
Step 8 Begin sending redo data to the standby databases.
ALTER SYSTEM SWITCH LOGFILE;
Useful Queries
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#, APPLIED_SEQ# FROM V$ARCHIVE_DEST_STATUS;
SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2 ;
select * from V$ARCHIVE_GAP;
select max(sequence#) from v$archived_log where applied='YES';
Select process,status from v$managed_standby;
select * from v$dataguard_status;
select * from v$dataguard_stats;
Friday, 25 April 2008
Friday, 4 April 2008
RMAN Backup and Recovery Example
This is one in the list of my Example Series in this blog. Check this blog for more possible examples
I have been using this to backup and recover database. I thought as usual, I should share it.
This is a simple script and should be used with care. This assumes a full backup and recover. It does not take other scenarios into consideration.
RMAN Backup Script
I assume you are running Unix.
This script will help you. Save it as shell script (e.g rman_backup.sh) and make required changes.
# Changeand to your own (e.g /u01/oracle/backup)
#!/usr/bin/sh
export ORACLE_SID=TEST
export ORACLE_HOME=$ORACLE_HOME
# Add date to be used in logfile
export TDAY=`date +%a`
export backup_dir = /u01/oracle/backup
export LOGFILE=$backup_dir/$SID_clone.log
echo "Backup Started at `date` \n" >$LOGFILE
$ORACLE_HOME/bin/rman <<'!' 1>> $LOGFILE 2>&1
# Connect to the database. Change this to Sys logon if not using /
connect target /
# Allocate Disk channels. Allocate more if you have enough max process to use
run {
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
allocate channel t4 type disk;
#backup the whole source database.
# Use tags for easy separation from other backups during restore
backup
tag whole_database_open
format '$backup_dir/df_%u'
database;
# switch out of the current logfile
sql 'alter system archive log current';
#backup the archived logs
backup
archivelog all
format '$backup_dir/al_%u';
# backup a copy of the controlfile that contains records for the backups just made
backup
current controlfile
tag = cf1
format '$backup_dir/cf_%u';
}
exit
echo "Backup Finished at `date` \n" >>$LOGFILE
RMAN Recovery Script
This one of the routines I use to clone my database from one Server to another. so it may help you, but you have to use it with caution .
hope you are a DBA and can make all the required changes to the scripts. Whereever you see $, it means run from OS. RMAN> means run from RMAN
--I assume the RMAN Backup has been restored to the DISK as well
--I assume your system is Unix
--I assume you will run the commands manually (e.g. copy and paste). If you can script them, thats ok.
Esnure you Rebuild all your configuration as before
Set all your ORACLE_HOME etc.
Prepare all your init ora file as before (restore a previous copy is possible)
Create all the starting mount point as it was for your datafiles.
Logon to RMAN
$ORACLE_HOME/bin/rman
Run the following command. This part can also be scripted if required.
RMAN> connect target /
Startup the Instance with nomount
RMAN> startup nomount;
# add the init parameter file to the above if not on default location
#If you have or know DBID, set the DBID
RMAN> set dbid
Identify and Restore the control file.
RMAN> RESTORE CONTROLFILE FROM 'mount_point//';
Create a password file
$create password file orapwd file=$ORACLE_HOME/dbs/orapw password=
Modify the script below to use to restore the database
export ORACLE_SID=
export ORACLE_HOME==$ORACLE_HOME
$ORACLE_HOME/bin/rman
connect target /
# Mount the database
alter database mount;
# Allocate Disk channels.
RMAN> run {
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
allocate channel t4 type disk;
restore database;
# If your rman used a tag,add "from tag" after database above
}
# Check the LOGFILE for errors
# Recover the Database
RMAN> run {
SET UNTIL logseq =thread = 1;
RECOVER DATABASE;
}
# Add Temp files because your backup will not have them
# Example
ALTER TABLESPACE "TEMP"
ADD TEMPFILE '//<_tempfile_name>' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 4000M;
# Run reset logs
RMAN> alter database open resetlogs;
I have been using this to backup and recover database. I thought as usual, I should share it.
This is a simple script and should be used with care. This assumes a full backup and recover. It does not take other scenarios into consideration.
RMAN Backup Script
I assume you are running Unix.
This script will help you. Save it as shell script (e.g rman_backup.sh) and make required changes.
# Change
#!/usr/bin/sh
export ORACLE_SID=TEST
export ORACLE_HOME=$ORACLE_HOME
# Add date to be used in logfile
export TDAY=`date +%a`
export backup_dir = /u01/oracle/backup
export LOGFILE=$backup_dir/$SID_clone.log
echo "Backup Started at `date` \n" >$LOGFILE
$ORACLE_HOME/bin/rman <<'!' 1>> $LOGFILE 2>&1
# Connect to the database. Change this to Sys logon if not using /
connect target /
# Allocate Disk channels. Allocate more if you have enough max process to use
run {
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
allocate channel t4 type disk;
#backup the whole source database.
# Use tags for easy separation from other backups during restore
backup
tag whole_database_open
format '$backup_dir/df_%u'
database;
# switch out of the current logfile
sql 'alter system archive log current';
#backup the archived logs
backup
archivelog all
format '$backup_dir/al_%u';
# backup a copy of the controlfile that contains records for the backups just made
backup
current controlfile
tag = cf1
format '$backup_dir/cf_%u';
}
exit
echo "Backup Finished at `date` \n" >>$LOGFILE
RMAN Recovery Script
This one of the routines I use to clone my database from one Server to another. so it may help you, but you have to use it with caution .
hope you are a DBA and can make all the required changes to the scripts. Whereever you see $, it means run from OS. RMAN> means run from RMAN
--I assume the RMAN Backup has been restored to the DISK as well
--I assume your system is Unix
--I assume you will run the commands manually (e.g. copy and paste). If you can script them, thats ok.
Esnure you Rebuild all your configuration as before
Set all your ORACLE_HOME etc.
Prepare all your init ora file as before (restore a previous copy is possible)
Create all the starting mount point as it was for your datafiles.
Logon to RMAN
$ORACLE_HOME/bin/rman
Run the following command. This part can also be scripted if required.
RMAN> connect target /
Startup the Instance with nomount
RMAN> startup nomount;
# add the init parameter file to the above if not on default location
#If you have or know DBID, set the DBID
RMAN> set dbid
Identify and Restore the control file.
RMAN> RESTORE CONTROLFILE FROM 'mount_point/
Create a password file
$create password file orapwd file=$ORACLE_HOME/dbs/orapw
Modify the script below to use to restore the database
export ORACLE_SID=
export ORACLE_HOME==$ORACLE_HOME
$ORACLE_HOME/bin/rman
connect target /
# Mount the database
alter database mount;
# Allocate Disk channels.
RMAN> run {
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
allocate channel t4 type disk;
restore database;
# If your rman used a tag,add "from tag
}
# Check the LOGFILE for errors
# Recover the Database
RMAN> run {
SET UNTIL logseq =
RECOVER DATABASE;
}
# Add Temp files because your backup will not have them
# Example
ALTER TABLESPACE "TEMP"
ADD TEMPFILE '
# Run reset logs
RMAN> alter database open resetlogs;
Subscribe to:
Posts (Atom)