In this post I'll create a ASM Physical Database on a different host for a NON-ASM Primary database.
Here:-
Database_Type Database_Name Host
Primary db1 prim.oracle.com
Standby sdb1 stby.oracle.com
I'll follow the below step to configure the same.
1. Take the full backup of Production database including archives as well.
RMAN> backup database format '/u01/bkp/db_%U.bkp';
Starting backup at 10-FEB-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
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/db1/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/db1/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/db1/test01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/db1/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/db1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 10-FEB-14
channel ORA_DISK_1: finished piece 1 at 10-FEB-14
piece handle=/u01/bkp/db_0op09ghd_1_1.bkp tag=TAG20140210T153053 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:36
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 10-FEB-14
channel ORA_DISK_1: finished piece 1 at 10-FEB-14
piece handle=/u01/bkp/db_0pp09gkd_1_1.bkp tag=TAG20140210T153053 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 10-FEB-14
RMAN> backup archivelog all format '/u01/bkp/arc_%U.bkp';
Starting backup at 10-FEB-14
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=9 RECID=7 STAMP=838829575
input archived log thread=1 sequence=10 RECID=8 STAMP=838884652
input archived log thread=1 sequence=11 RECID=9 STAMP=838901252
input archived log thread=1 sequence=12 RECID=10 STAMP=838901331
input archived log thread=1 sequence=13 RECID=11 STAMP=838905416
input archived log thread=1 sequence=14 RECID=12 STAMP=838939737
input archived log thread=1 sequence=15 RECID=13 STAMP=838978232
input archived log thread=1 sequence=16 RECID=14 STAMP=839014248
input archived log thread=1 sequence=17 RECID=15 STAMP=839052021
input archived log thread=1 sequence=18 RECID=16 STAMP=839095566
input archived log thread=1 sequence=19 RECID=17 STAMP=839140243
input archived log thread=1 sequence=20 RECID=18 STAMP=839161908
input archived log thread=1 sequence=21 RECID=19 STAMP=839162150
input archived log thread=1 sequence=22 RECID=20 STAMP=839172779
channel ORA_DISK_1: starting piece 1 at 10-FEB-14
channel ORA_DISK_1: finished piece 1 at 10-FEB-14
piece handle=/u01/bkp/arc_0qp09glc_1_1.bkp tag=TAG20140210T153300 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 10-FEB-14
RMAN> backup current controlfile for standby format '/u01/bkp/control_%U.ctl';
Starting backup at 10-FEB-14
using channel ORA_DISK_1
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 10-FEB-14
channel ORA_DISK_1: finished piece 1 at 10-FEB-14
piece handle=/u01/bkp/control_0rp09goe_1_1.ctl tag=TAG20140210T153438 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 10-FEB-14
Here:-
Database_Type Database_Name Host
Primary db1 prim.oracle.com
Standby sdb1 stby.oracle.com
I'll follow the below step to configure the same.
1. Take the full backup of Production database including archives as well.
RMAN> backup database format '/u01/bkp/db_%U.bkp';
Starting backup at 10-FEB-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
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/db1/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/db1/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/db1/test01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/db1/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/db1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 10-FEB-14
channel ORA_DISK_1: finished piece 1 at 10-FEB-14
piece handle=/u01/bkp/db_0op09ghd_1_1.bkp tag=TAG20140210T153053 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:36
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 10-FEB-14
channel ORA_DISK_1: finished piece 1 at 10-FEB-14
piece handle=/u01/bkp/db_0pp09gkd_1_1.bkp tag=TAG20140210T153053 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 10-FEB-14
RMAN> backup archivelog all format '/u01/bkp/arc_%U.bkp';
Starting backup at 10-FEB-14
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=9 RECID=7 STAMP=838829575
input archived log thread=1 sequence=10 RECID=8 STAMP=838884652
input archived log thread=1 sequence=11 RECID=9 STAMP=838901252
input archived log thread=1 sequence=12 RECID=10 STAMP=838901331
input archived log thread=1 sequence=13 RECID=11 STAMP=838905416
input archived log thread=1 sequence=14 RECID=12 STAMP=838939737
input archived log thread=1 sequence=15 RECID=13 STAMP=838978232
input archived log thread=1 sequence=16 RECID=14 STAMP=839014248
input archived log thread=1 sequence=17 RECID=15 STAMP=839052021
input archived log thread=1 sequence=18 RECID=16 STAMP=839095566
input archived log thread=1 sequence=19 RECID=17 STAMP=839140243
input archived log thread=1 sequence=20 RECID=18 STAMP=839161908
input archived log thread=1 sequence=21 RECID=19 STAMP=839162150
input archived log thread=1 sequence=22 RECID=20 STAMP=839172779
channel ORA_DISK_1: starting piece 1 at 10-FEB-14
channel ORA_DISK_1: finished piece 1 at 10-FEB-14
piece handle=/u01/bkp/arc_0qp09glc_1_1.bkp tag=TAG20140210T153300 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 10-FEB-14
RMAN> backup current controlfile for standby format '/u01/bkp/control_%U.ctl';
Starting backup at 10-FEB-14
using channel ORA_DISK_1
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 10-FEB-14
channel ORA_DISK_1: finished piece 1 at 10-FEB-14
piece handle=/u01/bkp/control_0rp09goe_1_1.ctl tag=TAG20140210T153438 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 10-FEB-14
Step 2.
Copy the backups to the standby server.
[oracle@prim bkp]$ scp -r * oracle@stby:/u01/bkp/
oracle@stby's password:
Permission denied, please try again.
oracle@stby's password:
arc_0qp09glc_1_1.bkp 100% 269MB 17.9MB/s 00:15
control_0rp09goe_1_1.ctl 100% 9568KB 9.3MB/s 00:01
db_0op09ghd_1_1.bkp 100% 1072MB 13.6MB/s 01:19
db_0pp09gkd_1_1.bkp 100% 9600KB 9.4MB/s 00:00
Step 3.
Make an entry for both the database servers in the tnsnames.ora file and the file should be similar on both the host.
[oracle@prim dbs]$ more /u01/app/oracle/product/11.2.0/db_2/network/admin/tnsnames.ora
DB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.xx.xx.xx )(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db1)
)
)
SDB1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.xx.xx.xx)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db1)
)
)
listener file of db1.
[oracle@prim dbs]$ more /u01/app/oracle/product/11.2.0/db_2/network/admin/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = db1)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_2)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.xx.xx.xx)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
Listener file of sdb1
[oracle@stby dbs]$ more /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.xx.xx.xx )(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = db1)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
Step 4.
Create the ora password file on the primary database and copy the same to the standby database server $ORACLE_HOME/dbs.
[oracle@prim dbs]$ orapwd file=orapwdb1 password=oracle force=y
[oracle@prim dbs]$ scp orapwdb1 oracle@stby:/u01/app/oracle/product/11.2.0/db_1/dbs
oracle@stby's password:
orapwdb1 100% 1536 1.5KB/s 00:00
Step 5.
Copy the parameter file from Primary to Standby database host and change the parameter file accordingly to standby .
db1.__db_cache_size=687865856
db1.__java_pool_size=16777216
db1.__large_pool_size=16777216
db1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
db1.__pga_aggregate_target=687865856
db1.__sga_target=1006632960
db1.__shared_io_pool_size=0
db1.__shared_pool_size=268435456
db1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/db1/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='+DATA','+DATA'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain='oracle.com'
*.db_name='db1'
*.db_unique_name='sdb1'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=db1XDB)'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=2147483648
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=db1XDB)'
*.fal_client='DB1'
*.fal_server='SDB1'
*.log_archive_config='DG_CONFIG=(db1,sdb1)'
*.log_archive_dest_1='LOCATION=+DATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=sdb1'
*.log_archive_dest_2='SERVICE=db1 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) COMPRESSION=ENABLE DB_UNIQUE_NAME=db1'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
Parameter file of Primary database.
[oracle@prim dbs]$ more initdb1.ora
db1.__db_cache_size=687865856
db1.__java_pool_size=16777216
db1.__large_pool_size=16777216
db1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
db1.__pga_aggregate_target=687865856
db1.__sga_target=1006632960
db1.__shared_io_pool_size=0
db1.__shared_pool_size=268435456
db1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/db1/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/db1/control01.ctl','/u01/app/oracle/fast_recovery_area/db1/control02.ctl'
*.db_block_size=8192
*.db_domain='oracle.com'
*.db_name='db1'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4322230272
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=db1XDB)'
#*.local_listener='LISTENER'
*.memory_target=1694498816
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.fal_client='DB1'
*.fal_server='SDB1'
*.log_archive_config='DG_CONFIG=(db1,sdb1)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/fast_recovery_area/DB1/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=db1'
*.log_archive_dest_2='SERVICE=sdb1 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) COMPRESSION=ENABLE DB_UNIQUE_NAME=sdb1'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
Step 6.
Start the Standby database in nomount mode using the new parameter file.
SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initsdb1.ora';
ORACLE instance started.
Total System Global Area 171581440 bytes
Fixed Size 1343668 bytes
Variable Size 117444428 bytes
Database Buffers 50331648 bytes
Redo Buffers 2461696 bytes
Database mounted.
Step 7.
Restore the control file using the standby control file backup.
RMAN> restore standby controlfile from '/u01/bkp/control_0rp09goe_1_1.ctl';
Starting restore at 10-FEB-14
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=+DATA/sdb1/controlfile/current.262.839184505
output file name=+DATA/sdb1/controlfile/current.263.839184509
Finished restore at 10-FEB-14
Step 8. Bounce the database and update the parameter file with the control file information.
*.control_files='+DATA/sdb1/controlfile/current.262.839184505','+DATA/sdb1/controlfile/current.263.839184509'
Step 9.
Start the standby database in mount mode and perform the restoration process of the database.
SQL> startup mount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initsdb1.ora';
ORACLE instance started.
Total System Global Area 171581440 bytes
Fixed Size 1343668 bytes
Variable Size 117444428 bytes
Database Buffers 50331648 bytes
Redo Buffers 2461696 bytes
Database mounted.
SQL> host rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Mon Feb 10 18:51:22 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: DB1 (DBID=1432532444, not open)
RMAN> run
{
set newname for datafile 1 to '+DATA';
set newname for datafile 2 to '+DATA';
set newname for datafile 3 to '+DATA';
set newname for datafile 4 to '+DATA';
set newname for datafile 5 to '+DATA';
set newname for tempfile 1 to '+DATA';
restore database;
switch datafile all;
switch tempfile all;
}
2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12>
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 10-FEB-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=33 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATA
channel ORA_DISK_1: restoring datafile 00002 to +DATA
channel ORA_DISK_1: restoring datafile 00003 to +DATA
channel ORA_DISK_1: restoring datafile 00004 to +DATA
channel ORA_DISK_1: restoring datafile 00005 to +DATA
channel ORA_DISK_1: reading from backup piece /u01/bkp/db_0op09ghd_1_1.bkp
channel ORA_DISK_1: piece handle=/u01/bkp/db_0op09ghd_1_1.bkp tag=TAG20140210T153053
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:56
Finished restore at 10-FEB-14
datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=839184818 file name=+DATA/sdb1/datafile/system.259.839184701
datafile 2 switched to datafile copy
input datafile copy RECID=7 STAMP=839184818 file name=+DATA/sdb1/datafile/sysaux.257.839184703
datafile 3 switched to datafile copy
input datafile copy RECID=8 STAMP=839184818 file name=+DATA/sdb1/datafile/undotbs1.260.839184705
datafile 4 switched to datafile copy
input datafile copy RECID=9 STAMP=839184818 file name=+DATA/sdb1/datafile/users.261.839184705
datafile 5 switched to datafile copy
input datafile copy RECID=10 STAMP=839184819 file name=+DATA/sdb1/datafile/test.258.839184703
renamed tempfile 1 to +DATA in control file
RMAN> list backup of archivelog all;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
1 137.97M DISK 00:00:07 06-FEB-14
BP Key: 1 Status: EXPIRED Compressed: NO Tag: TAG20140206T161117
Piece Name: /u01/bkp/01ovv1d5_1_1.bkp
List of Archived Logs in backup set 1
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 3 791516 04-FEB-14 816435 05-FEB-14
1 4 816435 05-FEB-14 858512 05-FEB-14
1 5 858512 05-FEB-14 859840 05-FEB-14
1 6 859840 05-FEB-14 859843 05-FEB-14
1 7 859843 05-FEB-14 869252 05-FEB-14
1 8 869252 05-FEB-14 898900 06-FEB-14
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
4 6.50K DISK 00:00:00 06-FEB-14
BP Key: 4 Status: EXPIRED Compressed: NO Tag: TAG20140206T161256
Piece Name: /u01/bkp/04ovv1g8_1_1.bkp
List of Archived Logs in backup set 4
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 9 898900 06-FEB-14 898952 06-FEB-14
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
7 177.34M DISK 00:00:07 07-FEB-14
BP Key: 7 Status: EXPIRED Compressed: NO Tag: TAG20140207T120732
Piece Name: /u01/bkp/07p017g5_1_1.bkp
List of Archived Logs in backup set 7
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 3 791516 04-FEB-14 816435 05-FEB-14
1 4 816435 05-FEB-14 858512 05-FEB-14
1 5 858512 05-FEB-14 859840 05-FEB-14
1 6 859840 05-FEB-14 859843 05-FEB-14
1 7 859843 05-FEB-14 869252 05-FEB-14
1 8 869252 05-FEB-14 898900 06-FEB-14
1 9 898900 06-FEB-14 898952 06-FEB-14
1 10 898952 06-FEB-14 926119 07-FEB-14
1 11 926119 07-FEB-14 933143 07-FEB-14
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
10 3.00K DISK 00:00:00 07-FEB-14
BP Key: 10 Status: EXPIRED Compressed: NO Tag: TAG20140207T120851
Piece Name: /u01/bkp/0ap017ij_1_1.bkp
List of Archived Logs in backup set 10
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 12 933143 07-FEB-14 933178 07-FEB-14
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
14 178.69M DISK 00:00:09 07-FEB-14
BP Key: 14 Status: EXPIRED Compressed: NO Tag: TAG20140207T131656
Piece Name: /u01/bkp/archivelog_0fp01bi8_1_1.bkp
List of Archived Logs in backup set 14
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 3 791516 04-FEB-14 816435 05-FEB-14
1 4 816435 05-FEB-14 858512 05-FEB-14
1 5 858512 05-FEB-14 859840 05-FEB-14
1 6 859840 05-FEB-14 859843 05-FEB-14
1 7 859843 05-FEB-14 869252 05-FEB-14
1 8 869252 05-FEB-14 898900 06-FEB-14
1 9 898900 06-FEB-14 898952 06-FEB-14
1 10 898952 06-FEB-14 926119 07-FEB-14
1 11 926119 07-FEB-14 933143 07-FEB-14
1 12 933143 07-FEB-14 933178 07-FEB-14
1 13 933178 07-FEB-14 934988 07-FEB-14
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
21 263.13M DISK 00:00:17 10-FEB-14
BP Key: 21 Status: EXPIRED Compressed: NO Tag: TAG20140210T123550
Piece Name: /u01/bkp/arc_0mp09696_1_1.bkp
List of Archived Logs in backup set 21
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 9 898900 06-FEB-14 898952 06-FEB-14
1 10 898952 06-FEB-14 926119 07-FEB-14
1 11 926119 07-FEB-14 933143 07-FEB-14
1 12 933143 07-FEB-14 933178 07-FEB-14
1 13 933178 07-FEB-14 934988 07-FEB-14
1 14 934988 07-FEB-14 953914 07-FEB-14
1 15 953914 07-FEB-14 974151 08-FEB-14
1 16 974151 08-FEB-14 993762 08-FEB-14
1 17 993762 08-FEB-14 1016061 09-FEB-14
1 18 1016061 09-FEB-14 1039552 09-FEB-14
1 19 1039552 09-FEB-14 1062286 10-FEB-14
1 20 1062286 10-FEB-14 1090918 10-FEB-14
1 21 1090918 10-FEB-14 1091253 10-FEB-14
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
25 268.79M DISK 00:00:18 10-FEB-14
BP Key: 25 Status: AVAILABLE Compressed: NO Tag: TAG20140210T153300
Piece Name: /u01/bkp/arc_0qp09glc_1_1.bkp
List of Archived Logs in backup set 25
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 9 898900 06-FEB-14 898952 06-FEB-14
1 10 898952 06-FEB-14 926119 07-FEB-14
1 11 926119 07-FEB-14 933143 07-FEB-14
1 12 933143 07-FEB-14 933178 07-FEB-14
1 13 933178 07-FEB-14 934988 07-FEB-14
1 14 934988 07-FEB-14 953914 07-FEB-14
1 15 953914 07-FEB-14 974151 08-FEB-14
1 16 974151 08-FEB-14 993762 08-FEB-14
1 17 993762 08-FEB-14 1016061 09-FEB-14
1 18 1016061 09-FEB-14 1039552 09-FEB-14
1 19 1039552 09-FEB-14 1062286 10-FEB-14
1 20 1062286 10-FEB-14 1090918 10-FEB-14
1 21 1090918 10-FEB-14 1091253 10-FEB-14
1 22 1091253 10-FEB-14 1096401 10-FEB-14
Step 10.
Perform the recover of database.
RMAN> run
2> {
3> set until sequence 22 thread 1;
4> recover database;
5> }
executing command: SET until clause
Starting recover at 11-FEB-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=42 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/11/2014 12:36:18
RMAN-06556: datafile 1 must be restored from backup older than SCN 1091253
Then I looked into the Oracle community and found the similar case and the solution given by user 491476 worked here.
https://community.oracle.com/thread/646983?start=0&tstart=0
SQL> select group#, first_change#, status, archived from v$log;
GROUP# FIRST_CHANGE# STATUS ARC
---------- ------------- ---------------- ---
1 1161769 CLEARING YES
3 1090918 INACTIVE YES
2 1175084 CURRENT YES
RMAN> run
2> {
3> set until sequence 1175084 thread 1;
4> recover database;
5> }
executing command: SET until clause
Starting recover at 11-FEB-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=43 device type=DISK
starting media recovery
archived log for thread 1 with sequence 23 is already on disk as file +DATA/sdb1/archivelog/2014_02_11/thread_1_seq_23.264.839248363
archived log for thread 1 with sequence 24 is already on disk as file +DATA/sdb1/archivelog/2014_02_11/thread_1_seq_24.276.839248363
archived log for thread 1 with sequence 25 is already on disk as file +DATA/sdb1/archivelog/2014_02_11/thread_1_seq_25.265.839248361
archived log for thread 1 with sequence 26 is already on disk as file +DATA/sdb1/archivelog/2014_02_11/thread_1_seq_26.275.839248367
archived log for thread 1 with sequence 27 is already on disk as file +DATA/sdb1/archivelog/2014_02_11/thread_1_seq_27.274.839248369
archived log for thread 1 with sequence 28 is already on disk as file +DATA/sdb1/archivelog/2014_02_11/thread_1_seq_28.273.839248369
archived log for thread 1 with sequence 29 is already on disk as file +DATA/sdb1/archivelog/2014_02_11/thread_1_seq_29.272.839248371
archived log for thread 1 with sequence 30 is already on disk as file +DATA/sdb1/archivelog/2014_02_11/thread_1_seq_30.283.839248371
archived log for thread 1 with sequence 31 is already on disk as file +DATA/sdb1/archivelog/2014_02_11/thread_1_seq_31.281.839248371
archived log for thread 1 with sequence 32 is already on disk as file +DATA/sdb1/archivelog/2014_02_11/thread_1_seq_32.289.839248373
archived log for thread 1 with sequence 33 is already on disk as file +DATA/sdb1/archivelog/2014_02_11/thread_1_seq_33.288.839248373
archived log for thread 1 with sequence 34 is already on disk as file +DATA/sdb1/archivelog/2014_02_11/thread_1_seq_34.287.839248375
archived log for thread 1 with sequence 35 is already on disk as file +DATA/sdb1/archivelog/2014_02_11/thread_1_seq_35.286.839248375
archived log for thread 1 with sequence 36 is already on disk as file +DATA/sdb1/archivelog/2014_02_11/thread_1_seq_36.284.839248375
archived log for thread 1 with sequence 37 is already on disk as file +DATA/sdb1/archivelog/2014_02_11/thread_1_seq_37.282.839248379
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=22
channel ORA_DISK_1: reading from backup piece /u01/bkp/arc_0qp09glc_1_1.bkp
channel ORA_DISK_1: piece handle=/u01/bkp/arc_0qp09glc_1_1.bkp tag=TAG20140210T153300
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
archived log file name=+DATA/sdb1/archivelog/2014_02_11/thread_1_seq_22.280.839249075 thread=1 sequence=22
archived log file name=+DATA/sdb1/archivelog/2014_02_11/thread_1_seq_23.264.839248363 thread=1 sequence=23
archived log file name=+DATA/sdb1/archivelog/2014_02_11/thread_1_seq_24.276.839248363 thread=1 sequence=24
archived log file name=+DATA/sdb1/archivelog/2014_02_11/thread_1_seq_25.265.839248361 thread=1 sequence=25
archived log file name=+DATA/sdb1/archivelog/2014_02_11/thread_1_seq_26.275.839248367 thread=1 sequence=26
archived log file name=+DATA/sdb1/archivelog/2014_02_11/thread_1_seq_27.274.839248369 thread=1 sequence=27
archived log file name=+DATA/sdb1/archivelog/2014_02_11/thread_1_seq_28.273.839248369 thread=1 sequence=28
archived log file name=+DATA/sdb1/archivelog/2014_02_11/thread_1_seq_29.272.839248371 thread=1 sequence=29
archived log file name=+DATA/sdb1/archivelog/2014_02_11/thread_1_seq_30.283.839248371 thread=1 sequence=30
archived log file name=+DATA/sdb1/archivelog/2014_02_11/thread_1_seq_31.281.839248371 thread=1 sequence=31
archived log file name=+DATA/sdb1/archivelog/2014_02_11/thread_1_seq_32.289.839248373 thread=1 sequence=32
archived log file name=+DATA/sdb1/archivelog/2014_02_11/thread_1_seq_33.288.839248373 thread=1 sequence=33
archived log file name=+DATA/sdb1/archivelog/2014_02_11/thread_1_seq_34.287.839248375 thread=1 sequence=34
archived log file name=+DATA/sdb1/archivelog/2014_02_11/thread_1_seq_35.286.839248375 thread=1 sequence=35
archived log file name=+DATA/sdb1/archivelog/2014_02_11/thread_1_seq_36.284.839248375 thread=1 sequence=36
archived log file name=+DATA/sdb1/archivelog/2014_02_11/thread_1_seq_37.282.839248379 thread=1 sequence=37
unable to find archived log
archived log thread=1 sequence=38
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/11/2014 12:45:24
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 38 and starting SCN of 1175084
RMAN> alter database open resetlogs;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 02/11/2014 12:46:12
ORA-01666: control file is for a standby database
SQL> alter database open;
Database altered.
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
DB1 READ ONLY PHYSICAL STANDBY
SQL> select process from v$managed_standby;
PROCESS
---------
ARCH
ARCH
ARCH
ARCH
RFS
RFS
RFS
RFS
8 rows selected.
SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.
SQL> select process from v$managed_standby;
PROCESS
---------
ARCH
ARCH
ARCH
ARCH
RFS
RFS
RFS
RFS
MRP0
9 rows selected.
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
DB1 READ ONLY WITH APPLY PHYSICAL STANDBY
No comments:
Post a Comment