In this thread, i am going to discuss about possible recovery scenario in RMAN. Before that, we need to setup the RMAN in oracle instance.. How do we set up RMAN in oracle instance? Here is the link to see how to setup RMAN in oracle.
Now we will discuss about different type of DB recovery scenarios.... The database name is ORCL. The catalog database name CATDB.... RMAN has setup and backup is running every day. The database is running in windows environment.
Scenario1
Kevin is DBA, he is working in Mission critical workshop. On Friday night, while taking RMAN backup, the machine is crashed and database is down. Kevin restarted the database and database is mounted successfully. But it is not opened. He realized that, some of the tablespaces are corrupted. Fortunately, redo log files and control files are intact. Kevin wanted to bring the DB as fast as possible.
SQL> startup
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 188746628 bytes
Database Buffers 415236096 bytes
Redo Buffers 7135232 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF'
SQL>
Here is the solution.... Since most of the tablespace datafiles are corrupted, he decided to do complete database recovery. First he mounted the database. He can not open the database until he completely recover the database.
Kevin connected the RMAN and recoverd the whole database as below.
C:\>rman catalog=rman/rman@catdb target=sys/password@orcl
Recovery Manager: Release 10.2.0.1.0 - Production on Thu May 21 21:32:28 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1215124933, not open)
connected to recovery catalog database
RMAN> run{
2> restore database;
3> recover database;
4> alter database open;
5> }
Starting restore at 21-MAY-09
starting full resync of recovery catalog
full resync complete
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
creating datafile fno=5 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS02.DBF
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
restoring datafile 00002 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
restoring datafile 00003 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
restoring datafile 00004 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: reading from backup piece C:\RMANBACKUP\BACKUPORCL_DB_0CKFJAS2_1_1_%S_%P
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\RMANBACKUP\BACKUPORCL_DB_0CKFJAS2_1_1_%S_%P tag=TAG20090521T143649
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 21-MAY-09
Starting recover at 21-MAY-09
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:14
Finished recover at 21-MAY-09
database opened
RMAN>
Adita is DBA in banking firm. On Monday afternoon, due to media failure, one of the data file is corrupted. But all other data files are working fine. Anita restarted the database and one data file is complaining and DB is not restarted. Anita made the corrupted data file offine and opened the database. So users can use the DB while recoving the one data file.
SQL> startup
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 188746628 bytes
Database Buffers 415236096 bytes
Redo Buffers 7135232 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS02.DBF'
SQL> alter database datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS02.DBF'
offline;
Database altered.
SQL> alter database open;
Database altered.
SQL> select file_id from dba_data_files where file_name
2 ='C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS02.DBF';
FILE_ID
----------
5
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Here is the solution to recover the database..........
C:\>rman catalog=rman/rman@catdb target=sys/password@orcl
Recovery Manager: Release 10.2.0.1.0 - Production on Thu May 21 22:30:06 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1215124933)
connected to recovery catalog database
RMAN> run
2> {
3> restore datafile 5;
4> recover datafile 5;
5> }
Starting restore at 21-MAY-09
starting full resync of recovery catalog
full resync complete
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=135 devtype=DISK
creating datafile fno=5 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS02.DBF
restore not done; all files readonly, offline, or already restored
Finished restore at 21-MAY-09
Starting recover at 21-MAY-09
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 21-MAY-09
RMAN> exit
Recovery Manager complete.
C:\>sqlplus sys/password as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 21 22:31:10 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> alter database datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS02.DBF'
ONLINE;
Database altered.
SQL> select distinct status from dba_data_files;
STATUS
---------
AVAILABLE
SQL>
Scenario3
Scott is a DBA in trading firm. During the hard disk replacement in unix box, one of the application tablespace data files are corrupted. When Scott restart the database, it complained that, data file is corrupted and tablespace is not usable. But fortunately, other tablespaces datafiles are intact. Scott decided to make this tablespace offline and open the database. So that others can use the database while recovering the tablespace.
SQL> startup
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 188746628 bytes
Database Buffers 415236096 bytes
Redo Buffers 7135232 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF'
SQL> alter database datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF'
OFFLINE;
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS02.DBF'
SQL> alter database datafile 'C:\ORACLE\PRODUCT\10.2.\ORADATA\ORCL\USERS02.DBF'
OFFLINE;
Database altered.
SQL> alter database open;
Database altered.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options
Scott made data files offline and opened the database. While others are using the database, he connected in RMAN and recovered only corrupted tablespace
C:\>rman catalog=rman/rman@catdb target=sys/password@orcl
Recovery Manager: Release 10.2.0.1.0 - Production on Fri May 22 09:36:52 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1215124933)
connected to recovery catalog database
RMAN> run{
2> restore tablespace users;
3> recover tablespace users;
4> }
Starting restore at 22-MAY-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=128 devtype=DISK
creating datafile fno=5 name=C:\ORACLE\PRODUCT\10.2.\ORADATA\ORCL\USERS02.DBF
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: reading from backup piece C:\RMANBACKUP\BACKUPORCL_DB_0CKFJA S2_1_1_%S_%P
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\RMANBACKUP\BACKUPORCL_DB_0CKFJAS2_1_1_%S_%P tag=TAG20090521T143649
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
Finished restore at 22-MAY-09
Starting recover at 22-MAY-09
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 12 is already on disk as file C:\ORACLE\PRODUCT\10
.2.0\DB_1\DATABASE\ARCHIVE\LOG_12_1_687344200.ARC
archive log thread 1 sequence 17 is already on disk as file C:\ORACLE\PRODUCT\10
.2.0\DB_1\DATABASE\ARCHIVE\LOG_17_1_687344200.ARC
archive log thread 1 sequence 18 is already on disk as file C:\ORACLE\PRODUCT\10
.2.0\DB_1\DATABASE\ARCHIVE\LOG_18_1_687344200.ARC
archive log thread 1 sequence 19 is already on disk as file C:\ORACLE\PRODUCT\10
.2.0\DB_1\DATABASE\ARCHIVE\LOG_19_1_687344200.ARC
media recovery complete, elapsed time: 00:01:23
Finished recover at 22-MAY-09
SQL> connect sys/password as sysdba
Connected.
SQL> alter database datafile 'c:\oracle\product\10.2.0\oradata\orcl\users01.dbf'
2 online;
Database altered.
SQL> alter database datafile 'c:\oracle\product\10.2.0\oradata\orcl\users02.dbf'
2 online;
Database altered.
SQL> connect scott/tiger@orcl
Connected.
SQL> select count(*) from emp;
COUNT(*)
----------
14
SQL>
Scenario4
Jane is a DBA in telecommunication company. Unix admin had done some maintenance work on the server. Once they completed their work, Jane restarted the database. When Jane restart the DB, Jane realized that, SYSTEM tablespace is corrupted.. But others are intact. He decided to mount the database and recover this tablespace. This leads downtime for the database, since jane can not open the database without recovering the system tablespace.
SQL> startup
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 188746628 bytes
Database Buffers 415236096 bytes
Redo Buffers 7135232 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF'
SQL> alter database datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
' OFFLINE;
Database altered.
SQL> ALTER DATABASE OPEN;
ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-01147: SYSTEM tablespace file 1 is offline
ORA-01110: data file 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF'
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options
C:\>rman catalog=rman/rman@catdb target=sys/password@orcl
Recovery Manager: Release 10.2.0.1.0 - Production on Fri May 22 10:21:04 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1215124933, not open)
connected to recovery catalog database
RMAN> run
2> {
3> restore tablespace system;
4> recover tablespace system;
5> }
Starting restore at 22-MAY-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
channel ORA_DISK_1: reading from backup piece C:\RMANBACKUP\BACKUPORCL_DB_0JKFLE
9Q_1_1_%S_%P
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\RMANBACKUP\BACKUPORCL_DB_0JKFLE9Q_1_1_%S_%P tag=TAG20090522T094738
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 22-MAY-09
Starting recover at 22-MAY-09
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:04
Finished recover at 22-MAY-09
RMAN> exit
Recovery Manager complete.
C:\>sqlplus sys/password as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri May 22 10:22:20 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01147: SYSTEM tablespace file 1 is offline
ORA-01110: data file 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF'
SQL> alter database datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF' online;
Database altered.
SQL> ALTER DATABASE OPEN;
Database altered.
SQL> select distinct status from dba_tablespaces;
STATUS
---------
ONLINE
SQL> select distinct status from dba_data_files;
STATUS
---------
AVAILABLE
SQL>
Scenario5
David is DBA for Energy company. David ended up restarting the database due to hardware maintenance. When David restarts the database, realized that all the redo log files are corrupted... RMAN backup runs every day at 11.30PM. David found this issue on 12.20PM. Now David needs to do incomplete recovery. Here is the steps David taken... Mount the database and run the RMAN backup. Just to make sure all the archived redo log files are backed up before start the recovery. Once RMAN backup is completed, David run the incomplete recovery and recovered until the last archived redo log.
SQL> startup
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 188746628 bytes
Database Buffers 415236096 bytes
Redo Buffers 7135232 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1:
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG'
SQL>exit
C:\>rman catalog=rman/rman@catdb target=sys/password@orcl
Recovery Manager: Release 10.2.0.1.0 - Production on Sat May 23 20:36:24 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1215124933, not open)
connected to recovery catalog database
RMAN> backup archivelog all;
Starting backup at 23-MAY-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=152 devtype=DISK
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=2 recid=31 stamp=687541158
input archive log thread=1 sequence=3 recid=32 stamp=687542042
input archive log thread=1 sequence=4 recid=33 stamp=687542062
40 comment=NONE
Starting Control File and SPFILE Autobackup at 23-MAY-09
piece handle=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\C-1215124933-20090523-0A comment=NONE
Finished Control File and SPFILE Autobackup at 23-MAY-09
RMAN> list backup of archivelog from time='sysdate-7';
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
10609 8.10M DISK 00:00:02 23-MAY-09
BP Key: 10611 Status: AVAILABLE Compressed: NO Tag: TAG20090523T2235
40
Piece Name: C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\01KFPFLS_1_1
List of Archived Logs in backup set 10609
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 2 544522 23-MAY-09 550869 23-MAY-09
1 3 550869 23-MAY-09 550898 23-MAY-09
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
10625 103.00K DISK 00:00:02 23-MAY-09
BP Key: 10630 Status: AVAILABLE Compressed: NO Tag: TAG20090523T2236
29
Piece Name: C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\03KFPFND_1_1
List of Archived Logs in backup set 10625
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 4 550898 23-MAY-09 550988 23-MAY-09
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
10672 8.29M DISK 00:00:02 23-MAY-09
BP Key: 10676 Status: AVAILABLE Compressed: NO Tag: TAG20090523T2239
52
Piece Name: C:\RMANBACKUP\BACKUPORCL_DB_05KFPFTO_5_1
List of Archived Logs in backup set 10672
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 2 544522 23-MAY-09 550869 23-MAY-09
1 3 550869 23-MAY-09 550898 23-MAY-09
1 4 550898 23-MAY-09 550988 23-MAY-09
1 5 550988 23-MAY-09 551206 23-MAY-09
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
10692 37.00K DISK 00:00:01 23-MAY-09
BP Key: 10697 Status: AVAILABLE Compressed: NO Tag: TAG20090523T2240
42
Piece Name: C:\RMANBACKUP\BACKUPORCL_DB_07KFPFVA_7_1
List of Archived Logs in backup set 10692
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 6 551206 23-MAY-09 551263 23-MAY-09
RMAN> restore database until sequence=6 thread=1 force;
Starting restore at 23-MAY-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
restoring datafile 00002 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
restoring datafile 00003 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
restoring datafile 00004 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: reading from backup piece C:\ORACLE\PRODUCT\10.2.0\DB_1\DATA
BASE\02KFPFLV_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\02KFPFLV_1_1 tag=TAG20090523
T223542
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 23-MAY-09
RMAN> recover database until sequence=6 thread=1 ;
Starting recover at 23-MAY-09
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 4 is already on disk as file C:\ORACLE\PRODUCT\10.
2.0\DB_1\DATABASE\ARCHIVE\LOG_4_1_687649381.ARC
archive log thread 1 sequence 5 is already on disk as file C:\ORACLE\PRODUCT\10.
2.0\DB_1\DATABASE\ARCHIVE\LOG_5_1_687649381.ARC
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_4_1_6876
49381.ARC thread=1 sequence=4
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_5_1_6876
49381.ARC thread=1 sequence=5
media recovery complete, elapsed time: 00:00:03
Finished recover at 23-MAY-09
RMAN> alter database open resetlogs;
database opened
new incarnation of database registered in recovery catalog
RMAN>
The database is recovered successfully. But this is incomplete recovery. Prior to oracle10g, oracle strongly recommend to take the full database backup whenever there is incomplete recovery. But in oracle10g, it is optional. But still it is good to take the full database backup for safer side. Please read this link to know more about this...
Scenario6
Kathy is DBA for one of the finance company. On Monday, due to hardware issue, the server was crashed. Unfortunately, Kathy lost all data files, control files, redo log files. But luckily the current archived redo log files were intact. Here is the steps Kathy has taken to handle this crash recovery.
Kathy was not able to mount the database since she lost her control file. So she first recovered the control file from RMAN backup. Once control file is recovered, she mounted the database. After the database is mounted, she ran the RMAN backup to make sure, all the current archive log files are backed up. she recovered the data base until last archived log file. Finally she opened the the DB RESETLOGS option.
SQL> startup
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 188746628 bytes
Database Buffers 415236096 bytes
Redo Buffers 7135232 bytes
ORA-00205: error in identifying control file, check alert log for more info
SQL>
C:\>set oracle_sid=orcl
C:\>rman catalog=rman/rman@catdb target=sys/password
Recovery Manager: Release 10.2.0.1.0 - Production on Sun May 24 15:26:51 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: orcl (not mounted)
connected to recovery catalog database
RMAN> restore controlfile;
Starting restore at 24-MAY-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: reading from backup piece C:\ORACLE\PRODUCT\10.2.0\DB_1\DATA
BASE\C-1215492928-20090524-00
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\C-1215492928-20090524-00 tag
=TAG20090524T152409
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01.CTL
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL02.CTL
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL03.CTL
Finished restore at 24-MAY-09
RMAN>
Once the control file is recovered, the database is mounted.....
SQL> alter database mount;
Database altered.
SQL> select archivelog_change#-1 from v$database;
ARCHIVELOG_CHANGE#-1
--------------------
547010
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options
C:\>rman catalog=rman/rman@catdb target=sys/password@orcl
Recovery Manager: Release 10.2.0.1.0 - Production on Sun May 24 15:29:33 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1215492928, not open)
connected to recovery catalog database
RMAN> run{
2> set until scn 547010;
3> restore database;
4> recover database;
5> alter database open resetlogs;
6> }
executing command: SET until clause
Starting restore at 24-MAY-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
restoring datafile 00002 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
restoring datafile 00003 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
restoring datafile 00004 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: reading from backup piece C:\RMANBACKUP\BACKUPORCL_DB_02KFRA
N7_1_1_%S_%P
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\RMANBACKUP\BACKUPORCL_DB_02KFRAN7_1_1_%S_%P tag=TAG20090524T1523
19
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 24-MAY-09
Starting recover at 24-MAY-09
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 3 is already on disk as file C:\ORACLE\PRODUCT\10.
2.0\DB_1\DATABASE\ARCHIVE\LOG_3_1_687712197.ARC
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_3_1_6877
12197.ARC thread=1 sequence=3
media recovery complete, elapsed time: 00:00:04
Finished recover at 24-MAY-09
database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
RMAN>
Scenario7
Scott is the DBA for database. One of the DB progammer truncated the critical table around 3.45PM and he called Scott and explained the situation. Scott has to recover the truncated table. Here is the steps Scott followed....
SQL> select count(*) from employee;
COUNT(*)
----------
14
SQL> select to_char(sysdate,'DD-MM-YYYY:HH24:MI:SS')
2 from dual
3 /
TO_CHAR(SYSDATE,'DD
-------------------
24-05-2009:15:45:42
SQL> truncate table employee;
Table truncated.
SQL> select count(*) from employee;
COUNT(*)
----------
0
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 188746628 bytes
Database Buffers 415236096 bytes
Redo Buffers 7135232 bytes
Database mounted.
SQL>
C:\>rman catalog=rman/rman@catdb target=sys/password@orcl
Recovery Manager: Release 10.2.0.1.0 - Production on Sun May 24 15:58:56 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1215492928, not open)
connected to recovery catalog database
RMAN> run
2> {
3> set until time "to_date('24-05-2009:15:45:42','DD-MM-YYYY HH24:MI:SS')";
4> restore database;
5> recover database;
6> }
executing command: SET until clause
Starting restore at 24-MAY-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
restoring datafile 00002 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
restoring datafile 00003 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
restoring datafile 00004 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: reading from backup piece C:\RMANBACKUP\BACKUPORCL_DB_09KFRB
BR_1_1_%S_%P
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\RMANBACKUP\BACKUPORCL_DB_09KFRBBR_1_1_%S_%P tag=TAG20090524T1534
19
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 24-MAY-09
Starting recover at 24-MAY-09
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 2 is already on disk as file C:\ORACLE\PRODUCT\10.
2.0\DB_1\DATABASE\ARCHIVE\LOG_2_1_687713476.ARC
archive log thread 1 sequence 3 is already on disk as file C:\ORACLE\PRODUCT\10.
2.0\DB_1\DATABASE\ARCHIVE\LOG_3_1_687713476.ARC
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_2_1_6877
13476.ARC thread=1 sequence=2
media recovery complete, elapsed time: 00:00:05
Finished recover at 24-MAY-09
RMAN> exit
Recovery Manager complete.
C:\>sqlplus sys/password as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sun May 24 16:01:31 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> alter database open resetlogs;
Database altered.
SQL> connect scott/tiger@orcl
Connected.
SQL> select count(*) from employee;
COUNT(*)
----------
14
SQL>
I have discussed some of the other topics which are related to this thread. Please go through below links in case if you are interested....
How do we enable archivelog in oracle? Click here
What are the new features in RMAN since Oracle9i? Click here
No comments:
Post a Comment