1. 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 41523620186 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\11.2.0\ORADATA\ORCL\SYSTEM01.DBF'
SQL>
soulution :
Since most of the tablespace datafiles are corrupted, he decided to do complete database recovery. First he mounted the database. He cannot open the database until he completely recovers 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 11.2.0.1.0 - Production on Thu May 21 21:32:28 2013
Copyright (c) 1982, 2013, 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-2018
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\11.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\11.2.0\ORADATA\ORCL\SYSTEM01.DBF
restoring datafile 00002 to C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\UNDOTBS01.DBF
restoring datafile 00003 to C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\SYSAUX01.DBF
restoring datafile 00004 to C:\ORACLE\PRODUCT\11.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=TAG20130521T143649
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 21-MAY-2018
Starting recover at 21-MAY-2018
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:14
Finished recover at 21-MAY-2018
database opened
2. 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 41523620186 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\11.2.0\ORADATA\ORCL\USERS02.DBF'
SQL> alter database datafile 'C:\ORACLE\PRODUCT\11.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\11.2.0\ORADATA\ORCL\USERS02.DBF';
FILE_ID
----------
5
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
solution :
C:\>rman catalog=rman/rman@catdb target=sys/password@orcl
Recovery Manager: Release 11.2.0.1.0 - Production on Thu May 21 22:30:06 2013
Copyright (c) 1982, 2013, 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-2018
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\11.2.0\ORADATA\ORCL\USERS02.DBF
restore not done; all files readonly, offline, or already restored
Finished restore at 21-MAY-2018
Starting recover at 21-MAY-2018
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 21-MAY-2018
RMAN> exit
Recovery Manager complete.
C:\>sqlplus sys/password as sysdba
SQL*Plus: Release 11.2.0.1.0 - Production on Thu May 21 22:31:10 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> alter database datafile 'C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\USERS02.DBF'
ONLINE;
Database altered.
SQL> select distinct status from dba_data_files;
STATUS
---------
AVAILABLE
SQL>
3. 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 41523620186 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\11.2.0\ORADATA\ORCL\USERS01.DBF'
SQL> alter database datafile 'C:\ORACLE\PRODUCT\11.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\11.2.0\ORADATA\ORCL\USERS02.DBF'
SQL> alter database datafile 'C:\ORACLE\PRODUCT\11.2.\ORADATA\ORCL\USERS02.DBF'
OFFLINE;
Database altered.
SQL> alter database open;
Database altered.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.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 11.2.0.1.0 - Production on Fri May 22 2018:36:52 2013
Copyright (c) 1982, 2013, 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-2018
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=128 devtype=DISK
creating datafile fno=5 name=C:\ORACLE\PRODUCT\11.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\11.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=TAG20130521T143649
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
Finished restore at 22-MAY-2018
Starting recover at 22-MAY-2018
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-2018
SQL> connect sys/password as sysdba
Connected.
SQL> alter database datafile 'c:\oracle\product\11.2.0\oradata\orcl\users01.dbf'
2 online;
Database altered.
SQL> alter database datafile 'c:\oracle\product\11.2.0\oradata\orcl\users02.dbf'
2 online;
Database altered.
SQL> connect scott/tiger@orcl
Connected.
SQL> select count(*) from emp;
COUNT(*)
----------
14
4. 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 41523620186 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\11.2.0\ORADATA\ORCL\SYSTEM01.DBF'
SQL> alter database datafile 'C:\ORACLE\PRODUCT\11.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\11.2.0\ORADATA\ORCL\SYSTEM01.DBF'
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.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 11.2.0.1.0 - Production on Fri May 22 10:21:04 2013
Copyright (c) 1982, 2013, 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-2018
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\11.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=TAG20130522T20184738
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 22-MAY-2018
Starting recover at 22-MAY-2018
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:04
Finished recover at 22-MAY-2018
RMAN> exit
Recovery Manager complete.
C:\>sqlplus sys/password as sysdba
SQL*Plus: Release 11.2.0.1.0 - Production on Fri May 22 10:22:20 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.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\11.2.0\ORADATA\ORCL\SYSTEM01.DBF'
SQL> alter database datafile 'C:\ORACLE\PRODUCT\11.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>
5. 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 41523620186 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\11.2.0\ORADATA\ORCL\REDO01.LOG'
SQL>exit
C:\>rman catalog=rman/rman@catdb target=sys/password@orcl
Recovery Manager: Release 11.2.0.1.0 - Production on Sat May 23 20:36:24 2013
Copyright (c) 1982, 2013, 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-2018
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-2018
piece handle=C:\ORACLE\PRODUCT\11.2.0\DB_1\DATABASE\C-1215124933-20130523-0A comment=NONE
Finished Control File and SPFILE Autobackup at 23-MAY-2018
RMAN> list backup of archivelog from time='sysdate-7';
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
1062018 8.10M DISK 00:00:02 23-MAY-2018
BP Key: 10611 Status: AVAILABLE Compressed: NO Tag: TAG20130523T2235
40
Piece Name: C:\ORACLE\PRODUCT\11.2.0\DB_1\DATABASE\01KFPFLS_1_1
List of Archived Logs in backup set 1062018
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 2 544522 23-MAY-2018 550869 23-MAY-2018
1 3 550869 23-MAY-2018 550898 23-MAY-2018
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
10625 103.00K DISK 00:00:02 23-MAY-2018
BP Key: 10630 Status: AVAILABLE Compressed: NO Tag: TAG20130523T2236
29
Piece Name: C:\ORACLE\PRODUCT\11.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-2018 55201888 23-MAY-2018
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
10672 8.29M DISK 00:00:02 23-MAY-2018
BP Key: 10676 Status: AVAILABLE Compressed: NO Tag: TAG20130523T2239
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-2018 550869 23-MAY-2018
1 3 550869 23-MAY-2018 550898 23-MAY-2018
1 4 550898 23-MAY-2018 55201888 23-MAY-2018
1 5 55201888 23-MAY-2018 551206 23-MAY-2018
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
10692 37.00K DISK 00:00:01 23-MAY-2018
BP Key: 10697 Status: AVAILABLE Compressed: NO Tag: TAG20130523T2240
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-2018 551263 23-MAY-2018
RMAN> restore database until sequence=6 thread=1 force;
Starting restore at 23-MAY-2018
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\11.2.0\ORADATA\ORCL\SYSTEM01.DBF
restoring datafile 00002 to C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\UNDOTBS01.DBF
restoring datafile 00003 to C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\SYSAUX01.DBF
restoring datafile 00004 to C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: reading from backup piece C:\ORACLE\PRODUCT\11.2.0\DB_1\DATA
BASE\02KFPFLV_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\ORACLE\PRODUCT\11.2.0\DB_1\DATABASE\02KFPFLV_1_1 tag=TAG20130523
T223542
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 23-MAY-2018
RMAN> recover database until sequence=6 thread=1 ;
Starting recover at 23-MAY-2018
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\11.2.0\DB_1\DATABASE\ARCHIVE\LOG_4_1_6876
49381.ARC thread=1 sequence=4
archive log filename=C:\ORACLE\PRODUCT\11.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-2018
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 oracle11g, oracle strongly recommend to take the full database backup whenever there is incomplete recovery. But in oracle11g, it is optional. But still it is good to take the full database backup for safer side.
6. 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 41523620186 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 11.2.0.1.0 - Production on Sun May 24 15:26:51 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
connected to target database: orcl (not mounted)
connected to recovery catalog database
RMAN> restore controlfile;
Starting restore at 24-MAY-2018
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\11.2.0\DB_1\DATA
BASE\C-1215492928-20130524-00
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\ORACLE\PRODUCT\11.2.0\DB_1\DATABASE\C-1215492928-20130524-00 tag
=TAG20130524T15242018
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
output filename=C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\CONTROL01.CTL
output filename=C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\CONTROL02.CTL
output filename=C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\CONTROL03.CTL
Finished restore at 24-MAY-2018
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 11g Enterprise Edition Release 11.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 11.2.0.1.0 - Production on Sun May 24 15:29:33 2013
Copyright (c) 1982, 2013, 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-2018
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\11.2.0\ORADATA\ORCL\SYSTEM01.DBF
restoring datafile 00002 to C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\UNDOTBS01.DBF
restoring datafile 00003 to C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\SYSAUX01.DBF
restoring datafile 00004 to C:\ORACLE\PRODUCT\11.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=TAG20130524T1523
19
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 24-MAY-2018
Starting recover at 24-MAY-2018
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\11.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-2018
database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
RMAN>
7. 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-2013: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 41523620186 bytes
Redo Buffers 7135232 bytes
Database mounted.
SQL>
C:\>rman catalog=rman/rman@catdb target=sys/password@orcl
Recovery Manager: Release 11.2.0.1.0 - Production on Sun May 24 15:58:56 2013
Copyright (c) 1982, 2013, 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-2013: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-2018
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\11.2.0\ORADATA\ORCL\SYSTEM01.DBF
restoring datafile 00002 to C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\UNDOTBS01.DBF
restoring datafile 00003 to C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\SYSAUX01.DBF
restoring datafile 00004 to C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: reading from backup piece C:\RMANBACKUP\BACKUPORCL_DB_2018KFRB
BR_1_1_%S_%P
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\RMANBACKUP\BACKUPORCL_DB_2018KFRBBR_1_1_%S_%P tag=TAG20130524T1534
19
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 24-MAY-2018
Starting recover at 24-MAY-2018
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\11.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-2018
RMAN> exit
Recovery Manager complete.
C:\>sqlplus sys/password as sysdba
SQL*Plus: Release 11.2.0.1.0 - Production on Sun May 24 16:01:31 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.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>
8. Complete Recovery when SYSTEM tablespace is missing
Problem Generation
Delete system tablespace datafile from operating system when database is up and running.
[oracle@ocm ocmdb]$ rm -i /u01/app/oracle/oradata/disk3/ocmdb/system01.dbf
Solution
i.[oracle@ocm ocmdb]$ sqlplus / as sysdba
ii.SQL> SHUTDOWN abort;
iii.SQL> STARTUP mount;
iv.SQL> SELECT file#, name FROM v$datafile;
v.Using rman connect to target database and catalog database or directly to target database.
[oracle@oem scripts]$ rman target sys/oracle@ocmdb catalog rman/rman@oemdb
vi. RMAN> run
{
restore datafile 1;
recover datafile 1;
sql ‘ alter database open ‘;
}
Complete Recovery when NON-SYSTEM tablespace is missing and database is open and restoring datafile to different location.
Problem Generation
i.Delete USERS tablespace datafile from operating system
[oracle@ocm ocmdb]$ rm -i users01.dbf
ii.Connect as user sh/sh and create a table in users tablespace
SQL> CREATE TABLE sales_test TABLESPACE users AS SELECT *
FROM sales WHERE ROWNUM < 10;
ERROR at line 3:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: ‘/u01/app/oracle/oradata/disk3/ocmdb/users01.dbf’
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
Solution
i.As DBA user make datafile users01.dbf off line
SQL> SELECT file#, name FROM v$datafile;
SQL> ALTER DATABASE DATAFILE 4 OFFLINE;
ii.Restoring and recovering datafile 4 at new location
RMAN> run
{
set newname for datafile 4 to ‘/u01/app/oracle/oradata/disk5/ocmdb/users01.dbf’;
restore datafile 4;
switch datafile 4;
recover datafile 4;
sql ‘ alter database datafile 4 online’;
}
iii.Connect as user sh/sh and create a table in users tablespace
SQL> CREATE TABLE sales_test
TABLESPACE users AS SELECT * FROM sales WHERE ROWNUM < 10;
Table created
9. Complete Recovery when NON-SYSTEM tablespace is missing and database is closed and restoring datafile to different location.
Problem Generation
i.Shutdown the database.
SQL> SHUTDOWN immediate;
ii.Delete USERS tablespace datafile from operating system
[oracle@ocm ocmdb]$ rm -i users01.dbf
iii.Start the database
SQL> STARTUP;
ORACLE instance started.
Total System Global Area 53687201812 bytes
Fixed Size 1220460 bytes
Variable Size 21392018652 bytes
Database Buffers 318767104 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 – see DBWR trace file
ORA-01110: data file 4: ‘/u01/app/oracle/oradata/disk5/ocmdb/users01.dbf’
Solution
i.Make datafile 4 offline
SQL> ALTER DATABASE DATAFILE 4 OFFLINE;
ii.Open the database with datafile 4 oflfine
SQL> ALTER DATABASE OPEN;
iii.Using RMAN again connect to target database
[oracle@oem scripts]$ rman target sys/oracle@ocmdb catalog rman/rman@oemdb
iv.Restoring and recovering datafile 4 at new location
RMAN> run
{
set newname for datafile 4 to ‘/u01/app/oracle/oradata/disk3/ocmdb/users01.dbf’;
restore datafile 4;
switch datafile 4;
recover datafile 4;
sql ‘ alter database datafile 4 online’;
}
10. Recovery of datafile which has no backups
Problem Generation
i.Create Oracle Managed File tablespace
SQL> CREATE TABLESPACE reco_test;
ii.Create table sh_sales on tablespace reco_test.
SQL> CREATE TABLE sh_sales
TABLESPACE reco_test AS
SELECT *
FROM sh.sales WHERE ROWNUM < 10;
iii.Delete RECO_TEST tablespace datafile from operating system.
[oracle@ocm ~]$ rm –i /u01/app/oracle/oradata/disk5/OCMDB/datafile/o1_mf_reco_tes_6n8dnc7z_.dbf
iv.Select from table sh_sales.
SQL> SELECT COUNT (*) FROM sh_sales;
ERROR at line 1:
ORA-01116: error in opening database file 5
ORA-01110: data file 5:
‘/u01/app/oracle/oradata/disk5/OCMDB/datafile/o1_mf_reco_tes_6n8dnc7z_.dbf’
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
Solution
i.Recover Datafile 5.
RMAN> run {
sql ‘ alter database datafile 5 offline ‘;
restore datafile 5;
recover datafile 5;
sql ‘ alter database datafile 5 online ‘;
}
ii.Select from table sh_sales.
SQL> SELECT COUNT (*) FROM sh_sales;
10. Recover of missing controlfile ( Copy other controlfile and mount database).
Problem Generation
i.Shutdown the Database
SQL> SHUTDOWN immediate;
ii.Delete Control file control01.ctl from operating system
[oracle@ocm ocmdb]$ rm -i /u01/app/oracle/oradata/disk1/ocmdb/control01.ctl
iii.Startup Database.
SQL> STARTUP;
ORACLE instance started.
Total System Global Area 53687201812 bytes
Fixed Size 1220460 bytes
Variable Size 218103956 bytes
Database Buffers 314572800 bytes
Redo Buffers 2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info
Solution
i.Check the control file location
SQL> SHOW PARAMETER control
NAME TYPE VALUE
———————————— ———– ——————————
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/oradata/disk1/ocmdb/control01.ctl, /u01/app/
oracle/admin/ocmdb/control/control02.ctl
ii.Copy control01.ctl from control02.ctl
[oracle@ocm ocmdb]$ cp /u01/app/oracle/admin/ocmdb/control/control02.ctl /u01/app/oracle/oradata/disk1/ocmdb/control01.ctl
iii.Mount the Database and Open it.
SQL> ALTER DATABASE MOUNT;
SQL> ALTER DATABASE OPEN;
11. Incomplete recovery is required when archived log, redo log is missing, then recovery can only be made until the previous sequence, or when an important object was dropped and recovery needs to be made until before the object was dropped. Use until sequence or until time or until cancel for incomplete recovery.
Problem Generation
i.Shutdown Database.
SQL> SHUTDOWN immediate;
ii.Delete redo log files from operating system
[oracle@ocm ocmdb]$ rm -i /u01/app/oracle/oradata/disk4/ocmdb/redo01b.log[oracle@ocm ocmdb]$ rm -i /u01/app/oracle/oradata/disk2/ocmdb/redo01a.log
If redo log file is lost when database is up , always switch logfile few times until sql statement hangs to archive the available redo logfiles, which are still not archived.
iii.Startup the Database.
SQL> STARTUP;
ORACLE instance started.
Total System Global Area 53687201812 bytes
Fixed Size 1220460 bytes
Variable Size 218103956 bytes
Database Buffers 314572800 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1:
‘/u01/app/oracle/oradata/disk2/ocmdb/redo01a.log’
ORA-00312: online log 1 thread 1:
‘/u01/app/oracle/oradata/disk4/ocmdb/redo01b.log’
Solution
i.Complete database restore is required ,execute sql below to get latest sequence# archived ,add 1 to last sequence and then use in RMAN to recover database.
SQL> SELECT thread#, resetlogs_change#,archived,sequence#,
TO_CHAR (completion_time, ‘YYYY-MM-DD:HH24:MI:SS’) completion_time
FROM v$archived_log
WHERE archived = ‘YES’
AND completion_time = (SELECT MAX (completion_time) FROM v$archived_log WHERE archived = ‘YES’);
THREAD# RESETLOGS_CHANGE# ARC SEQUENCE# COMPLETION_TIME
———- ————— –— ———- ——————-
11 YES 58 2011-01-29:21:02:46
ii.Connect to target database using RMAN catalog
[oracle@oem scripts]$ rman target sys/oracle@ocmdb catalog rman/rman@oemdb
iii.RMAN command to recover database.
RMAN> run
{
restore database;
recover database until sequence 59 thread 1;
sql ‘ alter database open resetlogs ‘;
}
iv.After database is open switch logfile few times
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER SYSTEM SWITCH LOGFILE;
Scenario 12
Recovery if all controlfiles are missing
Problem Generation
i.Shutdown Database.
SQL> SHUTDOWN immediate;
ii.Delete all Control files from operating system
[oracle@ocm ocmdb]$ rm -i /u01/app/oracle/oradata/disk1/ocmdb/control01.ctl
[oracle@ocm ocmdb]$ rm -i /u01/app/oracle/admin/ocmdb/control/control02.ctl
iii.Connect as sysdba and startup Database.
[oracle@ocm ocmdb]$ sqlplus / as sysdba
SQL> STARTUP;
ORACLE instance started.
Total System Global Area 53687201812 bytes
Fixed Size 1220460 bytes
Variable Size 222298260 bytes
Database Buffers 310378496 bytes
Redo Buffers 2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info
Solution
i.Using RMAN restore contolfile from Autobackup and Recover Database
RMAN> run
{
restore controlfile from autobackup;
sql ‘ alter database mount’;
recover database;
sql ‘ alter database open resetlogs’;
}
ii.List Database Incarnation
RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
——- ——- ——– —————- — ———- ———-
1 2 OCMDB4083722939 PARENT 1 15-MAY-18
1 1415 OCMDB 4083722939 PARENT 503961 29-MAY-18
1 1867 OCMDB 4083722939 CURRENT 519078 29-MAY-18
iii.After database is open switch logfile few times
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER SYSTEM SWITCH LOGFILE;
v.Always backup controlfile after open resetlogs even if CONTROLFILE AUTOBACKUP ON. Let us NOT BACKUP controlfile and see what happens in next scenario.
RMAN> backup current controlfile; — For now do not execute this command
13.Recover if all controlfiles,datafiles,spfile,redologs are lost
Problem Generation
i.Shutdown Database.
SQL> SHUTDOWN immediate;
ii.Delete all the datafiles ,controlfiles ,redo logfiles ,spfile ,initocmdb.ora files from Operating System
iii.Startup Database
SQL> STARTUP;
ORA-01078: failure in processing system parameters
LRM-0012018: could not open parameter file ‘/u01/app/oracle/product/11.2.0/db_1/dbs/initocmdb.ora’
Make sure you have lost all the files not just spfile, if only spfile is lost , then too you will get above error you need to create only spfile.
Solution
i.Using RMAN connect to target database using catalog
[oracle@oem scripts]$ rman target sys/oracle@ocmdb catalog rman/rman@oemdb
Recovery Manager: Release 11.2.0.1.0 – Production on Sun Jan 30 06:02:44 2011
Copyright (c) 1982, 2013, Oracle. All rights reserved.
connected to target database (not started)
connected to recovery catalog database
ii.Set Database DBID
RMAN> set dbid <dbid> — dbid 4083722939
iii.Startup Database in Nomount State
RMAN> startup nomount;
iv.Restore spfile from Autobackup
RMAN> restore spfile from autobackup;
v.Again start the Database in Nomount State
RMAN> startup force nomount;
vi.Restore controlfile from Autobackup
RMAN> restore controlfile from autobackup;
vii.Mount Database
RMAN> alter database mount;
viii.Open a terminal in OCMDB machine and check archive log completion time
[oracle@ocm ~]$ sqlplus / as sysdba
SQL> SELECT thread#, resetlogs_change#,archived,sequence#,
TO_CHAR (completion_time, ‘YYYY-MM-DD:HH24:MI:SS’) completion_time
FROM v$archived_log
WHERE archived = ‘YES’
AND completion_time = (SELECT MAX (completion_time)
FROM v$archived_log
WHERE archived = ‘YES’);
THREAD# RESETLOGS_CHANGE# ARC SEQUENCE# COMPLETION_TIME
———-—————– — ——— ——————-
1519078 YES 1 2011-01-29:23:18:17
ix.By executing the command below we may not be able to recover database because control file is from older incarnation. This is because we have not taken backup of controlfile after reset logs and immediately crashed the database after recovery in previous scenario.
RMAN> run
{
restore database;
recover database until sequence 1 thread 1;
sql ‘ alter database open resetlogs’;
}
x.Now, we have to reset database to older incarnation and recover the database.
xi.List Database Incarnation.
RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
——- ——- ——– —————- — ———- ———-
1 2 OCMDB 4083722939 PARENT 1 15-MAY-18
1 1415 OCMDB 4083722939 PARENT 503961 29-MAY-18
11867 OCMDB 4083722939 CURRENT 519078 29-MAY-18
xii.Open a terminal in OCMDB machine and check archive log completion time.
SQL> SELECT thread#,resetlogs_change#,archived,sequence#,
TO_CHAR (completion_time, ‘YYYY-MM-DD:HH24:MI:SS’) completion_time
FROM v$archived_log
WHERE archived = ‘YES’
ORDER BY completion_time;
THREAD# RESETLOGS_CHANGE# ARC SEQUENCE# COMPLETION_TIME
———- —————– — ——— ——————-
1 503961 YES 4 2011-01-29:22:18:18
Add 1 to the latest sequence of last incarnation i.e. 4+1 =5
xiii.Reset the Database to old incarnation and recover.
RMAN> reset DATABASE TO incarnation 1415;
RMAN> run
{
restore database;
recover database until sequence 5 thread 1;
sql ‘ alter database open resetlogs’;
}
14. Recovery if Temporary tablespace is lost
Recreate the temporary tablespace.
For other backup recovery scenarios, please refer to Oracle Documentation
No comments:
Post a Comment