Document Scope
This document describes some important scenarios for recovering oracle9i database for a specific failure using complete and incomplete recovery
Document contents
==================
Scenario 1
Recovery in NOARCHIVELOG mode with out redo log files backups.
Scenario 2
Loosing users tablespace (because of media failure) and need to recover it in new location.( The database is open)
Scenario 3
Loosing all control files
Scenario 4
Loosing undo tablespace
Scenario 5
Loosing System tablespace
Scenario 6
Recover from user error. The table xyz was dropped at 11:00 am.
Scenario 7
Loss of inactive online redo log
Scenario 8
Loss of current online redo log
Scenario 9
Recover a lost datafile with no backup but you have all the archives from datafile creation.
Tips
====
Before the Recover command verify whether any offile datafiles exist and place then online, because any offline files may be unrecoverable after recovery.
SQL> select * from V$recover_file;
Ensure the log_archive_format to much your archives if not during recovery use the command
Sql>alter system log_archive_format=your_format scope=memory i.e in police enviroment is arch_%s.arc for
Example arch_88909.arc.
To change the archive destination for new location
Sql>alter system archive log start to ‘/backup/archives/day0’
To get a picture of the archives and current redo logs
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 130 **ARCHIVED
Next log sequence to archive 131 **WAIT TO BE ARCHIVED
Current log sequence 131
SQL> select status,archived,sequence# from v$log;
STATUS ARC SEQUENCE#
---------------- --- ----------
CURRENT NO 131
INACTIVE YES 130
Sql>select sequence#,to_char(first_time,'DD-MON HH24:MI') from v$log_history
Or
Sql> select archived,sequence# from v$archived_log order by sequence# desc
Scenario 1
Recovery in NOARCHIVELOG mode with out redo log files backups.
Sql > shutdown immediate;
Restore all datafiles and control files from your backup
Sql> startup mount;
Sql>Recover database until cancel using backup controlfile;
Sql>Alter database open resetlogs;
Scenario 2
Loosing users tablespace (because of media failure) and need to recover it in new location.( The database is open)
SQL> alter tablespace users offline;
Restore from backup into new location
SQL> alter tablespace users
rename datafile 'C:\ORACLE\ORADATA\TARGET01\USERS01.
DBF' to 'C:\ORACLE\ORADATA\TARGET01\disk2\USERS01.DBF';
SQL> recover tablespace users
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
SQL> alter tablespace users online;
Tablespace altered.
Scenario 3
Loosing all control files
If and only if current redo is available
1. create the controlfile from your saved scripts
2. sql>recover database
3. sql>alter system archive log all
4. sql>alter database open ;
If and only if current redo is not available
1. create the controlfile from your saved scripts
2. sql>recover database using backup control file;
3. sql>alter database open resetlogs
Scenario 4
Loosing undo tablespace
1. Restore undotbs1 from backup
2. Mount the database
3. sql>Recover database
4. Apply archives and redo logs
5. sql>Alter database open;
Scenario 5
Loosing System tablespace
*******Use the instruction on step 4
Scenario 6
Recover from user error. The table xyz was dropped at 11:00 am.
1. Sql>shutdown immediate;
2. Sql>Restore datafiles and controlfiles
3. Sql>recover database until time ‘2006-12-30 10:59:00’ using backup controlfile
4. apply archives
5. Sql>alter database open resetlogs
6. backup database
Scenario 7
Loss of inactive online redo log
1. Drop inactive online redo log and create a new one.
2. Sql>alter database drop logfile group 3
3. Sql>alter database add logfile group 3 ( ‘/u01/../redo03a.dbf’,’/u02/../redo3b.dbf’) size 5M
Scenario 8
Loss of current online redo log
1. Restore datafiles
2. Startup mount
3. sql>Recover database until cancel
4. Apply archived
5. sql>Alter database open resetlogs
6. Backup database
Scenario 9
Recover a lost datafile with no backup but you have all the archives from datafile creation.
Mount database
Drop datafile
Sql>alter database create datafile ‘/u01/../user02.dbf’
Sql>recover database
Apply all archives
Sql>alter database open;
