Incomplete recoveries

3 types of recoveries: timed based recovery, log-sequence recovery,

Recover database until time ‘2008-10-23:13:00:00’
Recover database until sequence 34;
Recover database until change 226250;

Opening database after incomplete recovery:

alter database open resetlogs;

Recovering from the Loss of a Tempfile:

ALTER TABLESPACE TEMP ADD TEMPFILE ‘<FILE_LOCATION>/temp01.dbf’SIZE 200m  REUSE AUTOEXTEND ON;

Recovering from the Loss of an Online Redo Log Group

1. Dealing with the Loss of an Inactive Online Redo Log Group Member
Just recreate redo log group member:

alter database add logfile ‘D:\ORACLE\ORADATA\ORCL\REDO02.LOG’ reuse to
group 2;

2. Dealing with the Loss of an Inactive Online Redo Log Group

• During database startup - Drop the log file group then recreate online redo log group 

alter database drop logfile group 2;

alter database add logfile group 2 ‘c:\oracle\oradata\orcl\redo02.log’ size 50m;
alter database add logfile group 2 ‘c:\oracle\oradata\orcl\redo02.log’ size 50m;

• When database is running - force the checkpoint and then clear the log group

alter system checkpoint;
alter database clear logfile group 1;

          If you receive the the following error:

          ERROR at line 1:

          ORA-00350: log 1 of instance orcl (thread 1) needs to be archived

          ORA-00312: online log 1 thread 1: ‘/oracle01/oradata/orcl/redo01.log’ 

          Then you need to clear unarchived log file:

alter database clear unarchived logfile ‘/oracle01/oradata/orcl/redo01.log’;

          then again you issue:

alter database clear logfile group 1; 

3. Dealing with the Loss of an Active but Not Current Online Redo Log Group

alter database clear unarchived logfile ‘/oracle01/oradata/orcl/redo01.log’;

 4. Dealing with the Loss of the Current Online Redo Log Group

alter system checkpoint;
shutdown;
startup mount;
alter database clear unarchived logfile ‘/oracle01/oradata/orcl/redo01.log’;
alter database open

Recovering Lost Control Files with a Backup Control File
recovery from the loss of all control files using backup control file.

1. Restore backup control file to the location where control files should reside(check CONTROL_FILES parameter).
2. mount the database

startup mount;

3. Recover the database with:

recover database using backup controlfile 

4. At the prompt type AUTO to apply all archived logs.
5. If you get an error please run again:

recover database using backup controlfile 

6. At the prompt choose one of the online redo logs .
7. Issue:

alter database open resetlogs;

Recovering Lost Control Files Using the create control file Command 

1. Assuming that you created control file creation script:

alter database backup controlfile to trace;

2.  Manually issue the create control file command but first modify the RESETLOGS or NORESETLOGS parameter.

Recovering from the Loss of the Password File

If you have backup, please restore it to $ORACLE_HOME/dbs

If no backup then:

cd $ORACLE_HOME/dbs
orapwd file=orapw<SID> entries=20 password=acid

where password is the password for SYS user