Oracle DB refresh steps from RMAN backups
----check path----
select member from v$logfile;
F:\ORACLE\ORADATA\AE02PRD\REDO03A.LOG
D:\ORACLE\ORADATA\AE02PRD\REDO03B.LOG
F:\ORACLE\ORADATA\AE02PRD\REDO02A.LOG
D:\ORACLE\ORADATA\AE02PRD\REDO02B.LOG
F:\ORACLE\ORADATA\AE02PRD\REDO01A.LOG
D:\ORACLE\ORADATA\AE02PRD\REDO01B.LOG
select file_id,file_name from dba_Data_files;
1 D:\ORACLE\ORADATA\AE02PRD\SYSTEM01.DBF
2 D:\ORACLE\ORADATA\AE02PRD\SYSAUX01.DBF
3 D:\ORACLE\ORADATA\AE02PRD\UNDOTBS01.DBF
4 D:\ORACLE\ORADATA\AE02PRD\USERS01.DBF
6 F:\ORACLE\ORADATA\AE02PRD\CWMLITE01.DBF
5 F:\ORACLE\ORADATA\AE02PRD\TEMP01.DBF
9 F:\ORACLE\ORADATA\AE02PRD\PSD_TMP01.DBF
8 F:\ORACLE\ORADATA\AE02PRD\DRSYS01.DBF
7 F:\ORACLE\ORADATA\AE02PRD\RED04.DBF
11 F:\ORACLE\ORADATA\AE02PRD\RED05.DBF
10 F:\ORACLE\ORADATA\AE02PRD\EXAMPLE01.DBF
13 F:\ORACLE\ORADATA\AE02PRD\INDX01.DBF
12 F:\ORACLE\ORADATA\AE02PRD\RED06.DBF
14 F:\ORACLE\ORADATA\AE02PRD\ODM01.DBF
15 F:\ORACLE\ORADATA\AE02PRD\AUE_AUDIT01.DBF
21 F:\ORACLE\ORADATA\AE02PRD\PSD_DATA06.DBF
20 F:\ORACLE\ORADATA\AE02PRD\PSD_DATA05.DBF
19 F:\ORACLE\ORADATA\AE02PRD\PSD_DATA04.DBF
18 F:\ORACLE\ORADATA\AE02PRD\PSD_DATA03.DBF
17 F:\ORACLE\ORADATA\AE02PRD\PSD_DATA02.DBF
16 F:\ORACLE\ORADATA\AE02PRD\PSD_DATA01.DBF
29 F:\ORACLE\ORADATA\AE02PRD\PSD_INDX08.DBF
28 F:\ORACLE\ORADATA\AE02PRD\PSD_INDX07.DBF
27 F:\ORACLE\ORADATA\AE02PRD\PSD_INDX06.DBF
26 F:\ORACLE\ORADATA\AE02PRD\PSD_INDX05.DBF
25 F:\ORACLE\ORADATA\AE02PRD\PSD_INDX04.DBF
24 F:\ORACLE\ORADATA\AE02PRD\PSD_INDX03.DBF
23 F:\ORACLE\ORADATA\AE02PRD\PSD_INDX02.DBF
22 F:\ORACLE\ORADATA\AE02PRD\PSD_INDX01.DBF
30 F:\ORACLE\ORADATA\AE02PRD\TOOLS01.DBF
31 F:\ORACLE\ORADATA\AE02PRD\XDB01.DBF
32 F:\ORACLE\ORADATA\AE02PRD\AUDIT_01.DBF
select name from v$controlfile;
F:\ORACLE\ORADATA\AE02PRD\CONTROL01.CTL
F:\ORACLE\ORADATA\AE02PRD\CONTROL02.CTL
F:\ORACLE\ORADATA\AE02PRD\CONTROL03.CTL
-------start to refresh
C:\Windows\system32>set NLS_LANG=AMERICAN_AMERICA.AR8MSWIN1256
set ORACLE_SID=AE02PRD
RMAN TARGET /
shutdown immediate
startup nomount
RMAN>restore controlfile from 'F:\RMAN_Backup\AE02PRD\AE02PRD_FULL_JJSGR3QO_1_1.BAK';
Starting restore at 16-OCT-17
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=F:\ORACLE\ORADATA\AE02PRD\CONTROL01.CTL
output file name=F:\ORACLE\ORADATA\AE02PRD\CONTROL02.CTL
output file name=F:\ORACLE\ORADATA\AE02PRD\CONTROL03.CTL
Finished restore at 16-OCT-17
RMAN> alter database mount;
Statement processed
For record type ARCHIVED LOG RECIDS from 7934 to 27122 are re-used before resync
For record type BACKUP PIECE RECIDS from 562 to 2220 are re-used before resync
For record type BACKUP SET RECIDS from 556 to 2790 are re-used before resync
For record type DELETED OBJECT RECIDS from 7862 to 27223 are re-used before resy
nc
released channel: ORA_DISK_1
RMAN> catalog start with 'F:\RMAN_Backup\AE02PRD\';
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of catalog command at 10/16/2017 01:02:59
RMAN-06189: current DBID 2265703831 does not match target mounted database (2269432018)
RMAN>
shutdown abort;
startup nomount;
set dbid=2269432018
alter database mount;
startup mount;
catalog start with 'F:\RMAN_Backup\AE02PRD\';
C:\Windows\system32>set NLS_LANG=AMERICAN_AMERICA.AR8MSWIN1256
C:\Windows\system32>rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Mon Oct 16 00:34:32 2017
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: AE02PRD (DBID=2265703831, not open)
RMAN> catalog start with 'F:\RMAN_Backup\AE02PRD\';
using target database control file instead of recovery catalog
searching for all files that match the pattern F:\RMAN_Backup\AE02PRD\
List of Files Unknown to the Database
=====================================
File Name: F:\RMAN_BACKUP\AE02PRD\AE02PRD_FULL_JHSGR27J_1_1.BAK
File Name: F:\RMAN_BACKUP\AE02PRD\AE02PRD_FULL_JISGR27R_1_1.BAK
File Name: F:\RMAN_BACKUP\AE02PRD\AE02PRD_FULL_JJSGR3QO_1_1.BAK
File Name: F:\RMAN_BACKUP\AE02PRD\AE02PRD_FULL_JKSGR3QU_1_1.BAK
Do you really want to catalog the above files (enter YES or NO)? y
cataloging files...
no files cataloged
List of Files Which Were Not Cataloged
=======================================
File Name: F:\RMAN_BACKUP\AE02PRD\AE02PRD_FULL_JHSGR27J_1_1.BAK
RMAN-07518: Reason: Foreign database file DBID: 2269432018 Database Name: AE0
2PRD
File Name: F:\RMAN_BACKUP\AE02PRD\AE02PRD_FULL_JISGR27R_1_1.BAK
RMAN-07518: Reason: Foreign database file DBID: 2269432018 Database Name: AE0
2PRD
File Name: F:\RMAN_BACKUP\AE02PRD\AE02PRD_FULL_JJSGR3QO_1_1.BAK
RMAN-07518: Reason: Foreign database file DBID: 2269432018 Database Name: AE0
2PRD
File Name: F:\RMAN_BACKUP\AE02PRD\AE02PRD_FULL_JKSGR3QU_1_1.BAK
RMAN-07518: Reason: Foreign database file DBID: 2269432018 Database Name: AE0
2PRD
RMAN>
----------------------------------------------------------------------------------
set ORACLE_SID=AE02PRD
RMAN TARGET /
shutdown abort
startup nomount
RMAN> restore controlfile from 'F:\RMAN_Backup\AE02PRD\AE02PRD_FULL_JJSGR3QO_1_1
.BAK';
Starting restore at 16-OCT-17
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=F:\ORACLE\ORADATA\AE02PRD\CONTROL01.CTL
output file name=F:\ORACLE\ORADATA\AE02PRD\CONTROL02.CTL
output file name=F:\ORACLE\ORADATA\AE02PRD\CONTROL03.CTL
Finished restore at 16-OCT-17
RMAN> alter database mount;
Statement processed
For record type ARCHIVED LOG RECIDS from 7934 to 27122 are re-used before resync
For record type BACKUP PIECE RECIDS from 562 to 2220 are re-used before resync
For record type BACKUP SET RECIDS from 556 to 2790 are re-used before resync
For record type DELETED OBJECT RECIDS from 7862 to 27223 are re-used before resync
released channel: ORA_DISK_1
RMAN> catalog start with 'F:\RMAN_Backup\AE02PRD\';
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of catalog command at 10/16/2017 01:02:59
RMAN-06189: current DBID 2265703831 does not match target mounted database (2269
432018)
RMAN> shutdown abort
Oracle instance shut down
RMAN> startup nomount
connected to target database (not started)
Oracle instance started
Total System Global Area 1300234240 bytes
Fixed Size 3056376 bytes
Variable Size 1119882504 bytes
Database Buffers 171966464 bytes
Redo Buffers 5328896 bytes
RMAN>
RMAN> set dbid=2269432018
executing command: SET DBID
RMAN> alter database mount;
Statement processed
RMAN> catalog start with 'F:\RMAN_Backup\AE02PRD\';
searching for all files that match the pattern F:\RMAN_Backup\AE02PRD\
List of Files Unknown to the Database
=====================================
File Name: F:\RMAN_BACKUP\AE02PRD\AE02PRD_FULL_JHSGR27J_1_1.BAK
File Name: F:\RMAN_BACKUP\AE02PRD\AE02PRD_FULL_JISGR27R_1_1.BAK
File Name: F:\RMAN_BACKUP\AE02PRD\AE02PRD_FULL_JJSGR3QO_1_1.BAK
File Name: F:\RMAN_BACKUP\AE02PRD\AE02PRD_FULL_JKSGR3QU_1_1.BAK
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: F:\RMAN_BACKUP\AE02PRD\AE02PRD_FULL_JHSGR27J_1_1.BAK
File Name: F:\RMAN_BACKUP\AE02PRD\AE02PRD_FULL_JISGR27R_1_1.BAK
File Name: F:\RMAN_BACKUP\AE02PRD\AE02PRD_FULL_JJSGR3QO_1_1.BAK
File Name: F:\RMAN_BACKUP\AE02PRD\AE02PRD_FULL_JKSGR3QU_1_1.BAK
RMAN>
run{
restore database;
switch datafile all;
}
Starting restore at 16-OCT-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1422 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 D:\ORACLE\ORADATA\AE02PRD\SYSTEM
01.DBF
channel ORA_DISK_1: restoring datafile 00002 to D:\ORACLE\ORADATA\AE02PRD\SYSAUX
01.DBF
channel ORA_DISK_1: restoring datafile 00003 to D:\ORACLE\ORADATA\AE02PRD\UNDOTB
S01.DBF
channel ORA_DISK_1: restoring datafile 00004 to D:\ORACLE\ORADATA\AE02PRD\USERS0
1.DBF
channel ORA_DISK_1: restoring datafile 00005 to F:\ORACLE\ORADATA\AE02PRD\TEMP01
.DBF
channel ORA_DISK_1: restoring datafile 00006 to F:\ORACLE\ORADATA\AE02PRD\CWMLIT
E01.DBF
channel ORA_DISK_1: restoring datafile 00007 to F:\ORACLE\ORADATA\AE02PRD\RED04.
DBF
channel ORA_DISK_1: restoring datafile 00008 to F:\ORACLE\ORADATA\AE02PRD\DRSYS0
1.DBF
channel ORA_DISK_1: restoring datafile 00009 to F:\ORACLE\ORADATA\AE02PRD\PSD_TM
P01.DBF
channel ORA_DISK_1: restoring datafile 00010 to F:\ORACLE\ORADATA\AE02PRD\EXAMPL
E01.DBF
channel ORA_DISK_1: restoring datafile 00011 to F:\ORACLE\ORADATA\AE02PRD\RED05.
DBF
channel ORA_DISK_1: restoring datafile 00012 to F:\ORACLE\ORADATA\AE02PRD\RED06.
DBF
channel ORA_DISK_1: restoring datafile 00013 to F:\ORACLE\ORADATA\AE02PRD\INDX01
.DBF
channel ORA_DISK_1: restoring datafile 00014 to F:\ORACLE\ORADATA\AE02PRD\ODM01.
DBF
channel ORA_DISK_1: restoring datafile 00015 to F:\ORACLE\ORADATA\AE02PRD\AUE_AU
DIT01.DBF
channel ORA_DISK_1: restoring datafile 00016 to F:\ORACLE\ORADATA\AE02PRD\PSD_DA
TA01.DBF
channel ORA_DISK_1: restoring datafile 00017 to F:\ORACLE\ORADATA\AE02PRD\PSD_DA
TA02.DBF
channel ORA_DISK_1: restoring datafile 00018 to F:\ORACLE\ORADATA\AE02PRD\PSD_DA
TA03.DBF
channel ORA_DISK_1: restoring datafile 00019 to F:\ORACLE\ORADATA\AE02PRD\PSD_DA
TA04.DBF
channel ORA_DISK_1: restoring datafile 00020 to F:\ORACLE\ORADATA\AE02PRD\PSD_DA
TA05.DBF
channel ORA_DISK_1: restoring datafile 00021 to F:\ORACLE\ORADATA\AE02PRD\PSD_DA
TA06.DBF
channel ORA_DISK_1: restoring datafile 00022 to F:\ORACLE\ORADATA\AE02PRD\PSD_IN
DX01.DBF
channel ORA_DISK_1: restoring datafile 00023 to F:\ORACLE\ORADATA\AE02PRD\PSD_IN
DX02.DBF
channel ORA_DISK_1: restoring datafile 00024 to F:\ORACLE\ORADATA\AE02PRD\PSD_IN
DX03.DBF
channel ORA_DISK_1: restoring datafile 00025 to F:\ORACLE\ORADATA\AE02PRD\PSD_IN
DX04.DBF
channel ORA_DISK_1: restoring datafile 00026 to F:\ORACLE\ORADATA\AE02PRD\PSD_IN
DX05.DBF
channel ORA_DISK_1: restoring datafile 00027 to F:\ORACLE\ORADATA\AE02PRD\PSD_IN
DX06.DBF
channel ORA_DISK_1: restoring datafile 00028 to F:\ORACLE\ORADATA\AE02PRD\PSD_IN
DX07.DBF
channel ORA_DISK_1: restoring datafile 00029 to F:\ORACLE\ORADATA\AE02PRD\PSD_IN
DX08.DBF
channel ORA_DISK_1: restoring datafile 00030 to F:\ORACLE\ORADATA\AE02PRD\TOOLS0
1.DBF
channel ORA_DISK_1: restoring datafile 00031 to F:\ORACLE\ORADATA\AE02PRD\XDB01.
DBF
channel ORA_DISK_1: restoring datafile 00032 to F:\ORACLE\ORADATA\AE02PRD\AUDIT_
01.DBF
channel ORA_DISK_1: reading from backup piece F:\RMAN_BKP\AE02PRD\AE02PRD_FULL_J
ISGR27R_1_1.BAK
channel ORA_DISK_1: errors found reading piece handle=F:\RMAN_BKP\AE02PRD\AE02PR
D_FULL_JISGR27R_1_1.BAK
channel ORA_DISK_1: failover to piece handle=F:\RMAN_BACKUP\AE02PRD\AE02PRD_FULL
_JISGR27R_1_1.BAK tag=TAG20171012T133907
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:31:18
Finished restore at 16-OCT-17
RMAN>recover database;
Starting recover at 16-OCT-17
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/16/2017 01:44:33
ORA-19698: D:\ORACLE\ORADATA\AE02PRD\REDO01B.LOG is from different database: id=
2265703831, db_name=AE02PRD
RMAN>RMAN>
dropped relative log files, then
RMAN> recover database;
Starting recover at 16-OCT-17
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=45835
channel ORA_DISK_1: reading from backup piece F:\RMAN_BACKUP\AE02PRD\AE02PRD_FUL
L_JKSGR3QU_1_1.BAK
channel ORA_DISK_1: piece handle=F:\RMAN_BACKUP\AE02PRD\AE02PRD_FULL_JKSGR3QU_1_
1.BAK tag=TAG20171012T140622
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
archived log file name=F:\ARCHIVE\AE02PRD\AE02PRD_1_45835_938831065.ARC thread=1
sequence=45835
unable to find archived log
archived log thread=1 sequence=45836
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/16/2017 01:54:09
RMAN-06054: media recovery requesting unknown archived log for thread 1 with seq
uence 45836 and starting SCN of 8181561702
RMAN>recover database until scn 8181561702;
RMAN> recover database until scn 8181561702;
Starting recover at 16-OCT-17
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 16-OCT-17
RMAN>select open_mode from v$database;
RMAN> alter database open resetlogs;
Statement processed
RMAN> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
RMAN> shutdown immediate
database closed
database dismounted
Oracle instance shut down
RMAN> startup
connected to target database (not started)
Oracle instance started
database mounted
database opened
Total System Global Area 1300234240 bytes
Fixed Size 3056376 bytes
Variable Size 1124076808 bytes
Database Buffers 167772160 bytes
Redo Buffers 5328896 bytes
RMAN>
C:\Windows\system32>sqlplus psd/UAEPRD823
SQL*Plus: Release 12.1.0.2.0 Production on Mon Oct 16 02:09:15 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Thu Oct 12 2017 03:02:35 -07:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt
ions
SQL> CONN / AS SYSDBA
Connected.
SQL> show parameter case
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon boolean TRUE
SQL> alter system set sec_case_sensitive_logon=FALSE;
System altered.
SQL> alter system set sec_case_sensitive_logon=FALSE;
System altered.
SQL> QUIT
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64
bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt
ions
C:\Windows\system32>sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Oct 16 02:11:17 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt
ions
SQL> show parameter case
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon boolean FALSE
SQL>
All for u
