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;
Subscribe to:
Post Comments (Atom)
1 comment:
NICE POST.
Post a Comment