ANJANI APPS DBA: Oracle Apps DBA Recovery Procedures . srini

Recovery Procedures steps .. from racinfotech
1. LOSS OF NON-ESSENTIAL DATAFILE WHEN DATABASE IS DOWN (DATABASE CAN BE IN ARCHIVE LOG MODE OR NO ARCHIVE LOG MODE)
SCENARIO
Database startup fails with errors :-
ora 1157 can not identify datafile
ora 1110 give the name of datafile which is missing.
REQUIREMENT
The script which will recreate the objects in the datafile like script which will create indexex.
TIME TAKEN IN RECOVERY
DATABASE WILL BE READY FOR USE IN (5 MIN+ TIME TAKEN TO CREATE INDEXES)
NON-ESSENTIAL DATAFILES
DATAFILE OF INDEX TABLESPACE, TEMPORARY TABLESPACE.
SOLUTION
Shutdown the database. (shutdown immediate).
Take complete backup of current database.
Startup mount
Query the v$recover_file view along with v$datafile with a join on file# and note down the name of file
say it is /prodebs/test/ind.dbf.
Alter database datafile ‘/prodebs/test/ind.dbf’ offline;
(if database is in noarchivwlog mode command will be
Alter database datafile ‘/prodebs/test/ind.dbf’ offline drop; )
Alter database open;
Drop tablespace user_index including contents;
Create tablespace user_index
datafile ‘/prodebs/test/ind.dbf’ size 1M;
Run the script which will built indexes.
Shutdown the database and take backup if necessary.
Startup.
NB : For temporary tablespace skip this step.

2. MISSING MIRRORED ONLINE REDO LOG FILES (DATABASE IS UP/DOWN)
SCENARIO
Database opens neatly but in alert log two error messages are logged with errors :-
(error from lgwr. Error is also written in lgwr trace file)
ora 313 open failed for members ..
ora 312 name of redo log memeber missing
ora 7360 OS error
ora 321 Can not update logfile header
TIME TAKEN IN RECOVERY
DATABASE WILL BE READY FOR USE IN 5 MIN.
SOLUTION
Shutdown the database.(shutdown).
Startup mount
Query v$logfile view and find which member has become invalid.
Query v$log view and find which group is current and size of group members (say it is b).
If the member of current log group (say it is 1) is corrupted issue the following commands :
Alter system switch logfile
If you can add one more member to corrupted log group ie maximum log member is not reached add one more
member to that group
Alter database add logfile member ‘filespec’ to group 1;
Shutdown the database
Startup the database
If you can not add one more member to corrupted log group
create one more log group with equal members and size of non corrupted log group.
Alter database group 3 (‘filespec’,'filespec’) size b;
Drop corrupted log group.
alter database drop logfile group 1;
Manually remove other members of this corrupted log group (ie rm in unix)
Shut down the database
Startup the database

3. RECOVER A LOST DATAFILE WITH NO BACKUP AND ALL ARCHIVED LOG FILES

SCENARIO

Database startup fails with errors :
ora 1157 can not identify datafile
ora 1110 give the name of datafile which is missing.

REQUIREMENT

For full recovery, database should be in archivelog mode.

TIME TAKEN IN RECOVERY
DATABASE WILL BE READY FOR USE IN MIN 10 mins.

SOLUTION

Shutdown the database.(shutdown).
Startup mount;
Query v$recover_file
Query v$datafile and find the name of datafile which is missing.(say it is ‘/prodebs/test/user_odc.dbf’)
Now issue the following commands in the given order :
alter database datafile ‘/prodebs/test/user_odc.dbf’ offline;
alter database create datafile ‘/prodebs/test/user_odc.dbf’ as ‘/prodebs/test/user_odc1.dbf’;
(removed file) (new file)
alter database datafile ‘/prodebs/test/user_odc1.dbf’ online;
alter database recover datafile ‘/prodebs/test/user_odc1.dbf’; or recover database
It will generally recover the database if you have all the archived file with you
alter database open;
Shutdown the database and take necessary backup if required.
Start the database.

4. RECOVER A LOST DATAFILE WITH BACKUP AND ALL ARCHIVED LOG FILES

SCENARIO

Database startup fails with errors :
ora 1157 can not identify datafile
ora 1110 give the name of datafile which is missing.

REQUIREMENT

For full recovery, database should be in archivelog mode.

TIME TAKEN IN RECOVERY
DATABASE WILL BE READY FOR USE IN 5 mins.

SOLUTION

Shutdown the database.(shutdown).
Startup mount;
Query v$recover_file
Query v$datafile and find the name of datafile which is missing.(say it is ‘/prodebs/test/user_odc.dbf’)
Copy the archived datafile (old one that is in backup) and give following commands :
recover database;
alter database open;
Shutdown the database and take necessary backup if required.
Start the database.

5. RECOVER A LOST DATAFILE WITH BACKUP AND MISSING ARCHIVED LOG FILES

SCENARIO

Database startup fails with errors :
ora 1157 can not identify datafile
ora 1110 give the name of datafile which is missing.

REQUIREMENT

For recovery, database should be in archivelog mode.

CONDITION
Recovery will be incomplete.

TIME TAKEN IN RECOVERY
DATABASE WILL BE READY FOR USE IN MIN 10 mins.

SOLUTION

Shutdown the database.(shutdown).
Copy all your datafiles from backup except control file.
Startup mount;
Copy the archived datafile (old one that is in backup) and give following commands :
recover database until cancel
alter database open resetlogs;
Shutdown the database and take necessary backup if required.
Start the database.

6. LOSS OF DATAFILE WHEN THE DATABASE IS IN NOARCHIVELOG MODE WITH NO LOGICAL BACKUP AND RECOVERY.

SCENARIO

Database startup fails with errors : (a) on monday morning (b) on thursday
ora 1157 can not identify datafile
ora 1110 give the name of datafile which is missing and found to be user data file.
There is no export and import backup (Logical backup).
Cold backup is taken once in every week . Here backup is taken on every sunday and this is the last activity
on sunday.
Problem has occured on (a) on monday morning (b) on thursday.
Here data file (s) associated with user tablespace is (are) lost.

TIME TAKEN IN RECOVERY

DATABASE WILL BE READY FOR USE IN MIN 30 mins.

SOLUTION
when Problem has occured on (a) on monday morning

Shutdown the database.(shutdown).
Take complete backup of current database.
Delete all the database files, redo log files and control files.
Copy the all the database files , redo log files and control files from the backup.
Start the database. There is no data loss.

SOLUTION
when Problem has occured on (a) on thursday morning. Here data loss will occur.

7. LOSS OF DATAFILE WHEN THE DATABASE IS IN NOARCHIVELOG MODE WITH LOGICAL BACKUP AND RECOVERY.

SCENARIO

Database startup fails with errors on Thursdays morning.
ora 1157 can not identify datafile
ora 1110 give the name of datafile which is missing and found to be user data file.
There is export and import backup. (Logical backup).
Strategy : So along with coldback up a complete database backup is also taken.
(i) After this on everyday, an incremental backup is also taken.
(ii) After this on everyday, complete database backup is also taken.
Cold backup is taken once in every week . Here backup is taken on every sunday and this is the last activity
on sunday.
Here data file (s) associated with user tablespace is (are) lost.

TIME TAKEN IN RECOVERY

DATABASE WILL BE READY FOR USE IN MIN 30 mins.

SOLUTION - 1
when Problem has occured on thursday morning and incremental backup is taken.

SOLUTION - 2
when Problem has occured on thursday morning and daily complete database logical
backup is taken.

8. LOSS OF SYSTEM DATAFILE WHEN THE DATABASE IS IN ARCHIVE LOG MODE AND RECOVERY.

SCENARIO

Database startup fails with errors on thursday morning.
ora 1157 can not identify datafile
ora 1110 give the name of datafile which is missing and found to be user data file.
Cold backup is taken once in every week . Here backup is taken on every sunday and this is the last activity
on sunday.
Here data file (s) associated with system tablespace is (are) lost.

TIME TAKEN IN RECOVERY

DATABASE WILL BE READY FOR USE IN MIN 30 mins.

SOLUTION

Shutdown the database.(shutdown).
Take complete backup of current database.
Copy the missing system database file(s).
Startup mount exclusive;
recover database;
alter database open;
Database is ready for use.

9. LOSS OF NON SYSTEM DATAFILE WITHOUT ROLLBACK SEGMENTS WHEN THE DATABASE IS IN ARCHIVE LOG MODE AND RECOVERY.

SCENARIO

TIME TAKEN IN RECOVERY
DATABASE WILL BE READY FOR USE IN MIN 30 mins.

SOLUTION-1 (DATABASE RECOVERY)

Shutdown the database.(shutdown).
Take complete backup of current database.
Copy the missing database file(s).
Startup mount exclusive;
recover database;
alter database open;
Database is ready for use.

SOLUTION-2 (DATAFILE RECOVERY)

Shutdown the database.(shutdown).
Take complete backup of current database.
Copy the missing database file(s).
Startup mount exclusive;
Alter database datafile ” offline;
Alter database open;
recover datafile ”
Alter database datafile ” online;
Database is ready for use.

* If multiple datafiles are lost use parallel recovery method (from muliple terminal use the same method for different files).

SOLUTION-3 (TABLESPACE RECOVERY)

Shutdown the database.(shutdown).
Take complete backup of current database.
Copy the missing database file(s).
Startup mount exclusive;
Alter database datafile ” offline;
Alter database open;
Alter tablespace offline temporary;
recover tablespace ;
Alter tablespace online ;
Database is ready for use.

10. LOSS OF NON SYSTEM DATAFILE WITH ROLLBACK SEGMENTS WHEN THE DATABASE IS IN ARCHIVE LOG MODE AND RECOVERY.

SCENARIO

Database startup fails with errors on thursday morning.
ora 1157 can not identify datafile
ora 1110 give the name of datafile which is missing and found to be user data file.
Cold backup is taken once in every week . Here backup is taken on every sunday and this is the last activity
on sunday.
Datafile(s) associated with rollback segment tablespace is (are) lost.

TIME TAKEN IN RECOVERY
DATABASE WILL BE READY FOR USE IN MIN 30 mins.

SOLUTION–

Shutdown the database.(shutdown).
Take complete backup of current database.
Comment the ROLLBACK_SEGMENT parameter or assign ROLLBACK_SEGMENT=(SYSTEM)
in init.ora file before startup.
Copy the missing rollback segment database file(s).
Startup mount exclusive;
alter database datafile ” offline;
alter database open;
Alter tablespace offline temporary;
recover tablespace ;
Alter tablespace online;
Query dba_rollback_segs (column name segment_name, status) and note down the name of segment
name having status recovery (say they are r01, r02,r03).
alter rollback segment r01 online;
alter rollback segment r02 online;
alter rollback segment r03 online;
shutdown the database.
Remove comment from rollback_segment parameter or remove system value and give the name of rollback
segments which you want to be online when database starts.
Start the database.
Database is ready for use.

11. LOSS OF UNARCHIVED ONLINE LOG FILES WHEN THEY ARE NOT MIRRORED WHEN THE DATABASE IS IN ARCHIVE LOG MODE AND RECOVERY.

SCENARIO

Database startup fails with errors on thursday morning.
ora 1157 can not identify datafile
ora 1110 give the name of datafile which is missing and found to be user data file.
Cold backup is taken once in every week . Here backup is taken on every sunday and this is the last activity
on sunday.
All the online redo log files are lost.
All the data files and current control files are intact.

TIME TAKEN IN RECOVERY
DATABASE WILL BE READY FOR USE IN MIN 30 mins.

SOLUTION

Shutdown the database.(shutdown).
Take complete backup of current database.
Copy the all the database files from latest offline or online backup.
Startup mount exclusive;
recover database until cancel;
alter database open resetlogs;
shutdown the database.
Take cold backup. It is strongely advised.
Start the database.
Database is ready for use.

12. DATABASE CRASH DURING HOT BACKUP WHEN THE DATABASE IS IN
ARCHIVE LOG MODE AND RECOVERY.

SCENARIO

While taking hot backup, database crashes.
(a) When Oracle Version is 7.2 or more.
(b) When Oracle Version is 7.1.

TIME TAKEN IN RECOVERY
[a] DATABASE WILL BE READY FOR USE IN MIN 2 mins.
[b] DATABASE WILL BE READY FOR USE IN MIN 30 mins.

SOLUTION– (Oracle Version is 7.2 or more)

Shutdown the database.(shutdown).
Take complete backup of current database.
Startup mount exclusive;
Query the view v$backup and get file # having status active. Now from v$datafile get the name of
file which is active in v$backup
alter database datafile ” end backup;
alter database open ;
shutdown the database.
Start the database.
Database is ready for use.

SOLUTION– (Oracle Version is 7.1 )

Shutdown the database.(shutdown).
Take complete backup of current database.
Startup mount exclusive;
recover database ;
( This may take significant amount of time if a large number of archived logs are to be applied)
alter database open ;
shutdown the database.
Start the database.
Database is ready for use.

13. LOSS OF CONTROL FILE AND WHEN THE DATABASE IS IN ARCHIVE LOG MODE AND RECOVERY.

SCENARIO

[a] Loss of control file when it is mirrored.

[b] Loss of control file when there is a backup and it is not mirrored but not before last reset log option.

[c] Loss of control file when there is no backup and it is not mirrored (total loss).

NB : Loss of control file when there is a backup and it is not mirrored but it is before last reset log option. (suppose database is open on day x with alter database startup resetlogs.So your control file should be before xth day )

startup/recover database using backup controlfile

Oracle error no is ora 1190 :control file or data file 1 is from before the last RESETLOGS
ora 1110 :name of system datafile.

[b] Loss of control file when there is a backup and it is not mirrored but not before last reset log option. (suppose database is open on day x with alter database startup resetlogs.So your control file should be after xth day )

ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: ‘/prodebs/test/sys_odc.dbf’
ORA-01207: file is more recent than control file – old control file

TIME TAKEN IN RECOVERY

[a] DATABASE WILL BE READY FOR USE IN MIN 2 mins.
[b] DATABASE WILL BE READY FOR USE IN MIN 30 mins.
[c] DATABASE WILL BE READY FOR USE IN MIN 30 mins.

SOLUTION– (Loss of control file when it is mirrored)
Shutdown the database.(shutdown).
Copy the second control file to this disk.
Rename this control file to the lost one.
Start the database.
Database is ready for use.

SOLUTION– (Loss of all the control file(s) )
Shutdown the database.(shutdown).
run backup of controlfile which you might have taken using the following command :
alter database backup controlfile to trace;
It creates a script. After editing that script it looks like this :
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE “” NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 100
LOGFILE
GROUP 1 (
‘/prodebs/test/redo_odc11.dbf’,
‘/prodebs/test/redo_odc12.dbf’
) SIZE 50K,
GROUP 2 (
‘/prodebs/test/redo_odc21.dbf’,
‘/prodebs/test/redo_odc22.dbf’
) SIZE 50K
DATAFILE
‘/prodebs/test/sys_odc.dbf’,
‘/prodebs/test/sys_odc1.dbf’,
‘/prodebs/test/user_odc.dbf’,
‘/prodebs/test/temp_odc.dbf’,
‘/prodebs/test/rbs_odc.dbf’,
‘/prodebs/test/ind.dbf’
;
RECOVER DATABASE
ALTER SYSTEM ARCHIVE LOG ALL;
ALTER DATABASE OPEN;
shutdown the database.
Start the database.
Database is ready for use.

SOLUTION– (Loss of control file when there is a backup and it is not mirrored)

Shutdown the database.(shutdown).
Copy the old control file to this disk.
startup mount exclusive;
If you have any tablespace which is read only, take all the datafile offiline related to this tablespace.
recover database using backup controlfile;
Offline datafile should bring to online status. (alter database datafile ‘< name of datafile>’ online;)
(for read only tablespace)
alter database open resetlogs;
Shutdown the database.
Take cold backup. It is strongely advised.
Start the database.
Database is ready for use.

14. DATABASE SPACE MANAGEMENT WHEN THE DATABASE IS IN ARCHIVE LOG MODE AND RECOVERY (RESIZING DATAFILE).

SCENARIO

Space management when :
(a) Oracle Version is 7.2 or more.
(b) Oracle Version is 7.1.

Oracle Error is : ora 00376 file # can not be read at this time.
ora 01110 name of datafile.

TIME TAKEN IN RECOVERY
[a] DATABASE WILL BE READY FOR USE IN MIN 5 mins.
[b] DATABASE WILL BE READY FOR USE IN MIN 30 mins.

SOLUTION– (Oracle Version is 7.2 or more)

Shutdown the database.(shutdown).
Take complete backup of current database.
Startup open;
Query the view v$datafile get the name of file which you want to resize.
alter database datafile ” resize [m/k];
shutdown the database.
Take backup if necessary.
Start the database.
Database is ready for use.

SOLUTION– (Oracle Version is 7.1 )

[a] Restore the datafile and apply recovery. Resizing is not possible.

Shutdown the database.(shutdown).
Take complete backup of current database.
Copy the deleted datafile
Startup mount exclusive;
recover database ;
( This may take significant amount of time if a large number of archived logs are to be applied)
alter database open ;
shutdown the database.
Start the database.
Database is ready for use.

[b] If deleted datafile is not available in backup .

Shutdown the database.(shutdown).
Take complete backup of current database.
Startup mount exclusive;
alter database add datafile ” as ”;
recover database ;
( This may take significant amount of time if a large number of archived logs are to be applied)
alter database open ;
shutdown the database.
Start the database.
Database is ready for use.

[c] Rebuild tablespace

REQUIREMENT

Logical backup is there.

Shutdown the database.(shutdown).
Take complete backup of current database.
Startup mount exclusive;
alter database datafile ” offline;
alter database open;
alter tablespace offline;
drop tablespace ;
create tablespace datafile ” size [m/k];
alter tablespace online;
use export / import method to recover the loss data.
shutdown the database.
Take backup if necessary.
Start the database.
Database is ready for use.

15. RECOVERY THROUGH RESETLOGS .

CONDITIONS :

[A] When online redo logs files are deleted.
[B] Loss of all control files.
[C] When recovery is done through old control files.

Events:-

[1]
a- Cold backup is taken.
b- Loss of redo log file.and media recovery. At this moment, a backup is taken.
c- Loss of data file

[2]
a- Cold backup is taken.
b- Loss of redo log file.and media recovery. At this moment, a backup is not taken.
c- Loss of data file .

SOLUTION - 1

Shutdown the database.(shutdown).
Take complete backup of current database.
Copy the most recent cold backup of datafiles.
Startup mount exclusive;
Recover database;
alter database open;
shutdown the database.
Take a cold backup.
Start the database.
Database is ready for use.

Advantage : All the data will be recovered.

SOLUTION - 2.1

Shutdown the database.(shutdown).
Take complete backup of current database.
Startup mount exclusive;
alter database datafile ” offline;
alter database open;
Export all the data from the tablespace (all objects in missing file will be inaccessible),
Drop and recreate the tablespace.
Import all the data taken from tablespace.
recover datafile ” ;
alter database open;
shutdown the database.
Take a cold backup.
Start the database.
Database is ready for use.

Disadvantage : All the data will be lost that was entered in datafile 5;

SOLUTION - 2.2

Shutdown the database.(shutdown).
Take complete backup of current database.
Copy the most recent cold backup of datafile and control file only. Do not copy redo log file.
Startup mount exclusive;
Recover database;
alter database open resetlogs;
shutdown the database.
Take a cold backup.
Start the database.
Database is ready for use.

Disadvantage : All the data will be lost that after event b.

16. LOSS OF DATA FILE WHEN THE DATABASE IS IN ARCHIVE LOG MODE AND RECOVERY.

SCENARIO

Database startup fails with errors on thursday morning.
ora 1157 can not identify datafile
ora 1110 give the name of datafile which is missing and found to be user data file.
Cold backup is taken once in every week . Here backup is taken on every sunday and this is the last activity
on sunday.
Datafile(s) associated with user tablespace is (are) permanently lost.

TIME TAKEN IN RECOVERY
DATABASE WILL BE READY FOR USE IN MIN 30 mins.

SOLUTION

Shutdown the database.(shutdown).
Take complete backup of current database.
Startup mount exclusive;
alter database create datafile ” ;
recover datafile ” ;
alter database open;
shutdown the database.
Start the database.
Database is ready for use.

17. SYSTEM CLOCK CHANGE AND POINT-IN-TIME RECOVERY.

SCENARIO

Database startup fails with errors on thursday morning.
ora 1157 can not identify datafile
ora 1110 give the name of datafile which is missing and found to be user data file.
Cold backup is taken once in every week . Here backup is taken on every sunday and this is the last activity
on sunday.
Datafile(s) associated with user tablespace is (are) permanently lost.

TIME TAKEN IN RECOVERY
DATABASE WILL BE READY FOR USE IN MIN 30 mins.

SOLUTION

Shutdown the database.(shutdown).
Take complete backup of current database.
Startup mount exclusive;
recover database until time ” ;
alter database open resetlogs;
shutdown the database.
Take a cold backup of database;
Start the database.
Database is ready for use.

18. OFFLINE TABLESPACES AND RECOVERY.

SCENARIO

Database startup fails with errors on thursday morning.
ora 1157 can not identify datafile
ora 1110 give the name of datafile which is missing and found to be user data file.
Cold backup is taken once in every week . Here backup is taken on every sunday and this is the last activity
on sunday.
Datafile(s) associated with user tablespace is (are) permanently lost.

TIME TAKEN IN RECOVERY
DATABASE WILL BE READY FOR USE IN MIN 30 mins.

SOLUTION - 1

Shutdown the database.(shutdown).
Take complete backup of current database.
Startup mount exclusive;
recover database ;
alter database open ;
recover tablespace ;
alter tablespace online;
Shutdown the database.
Start the database.
Database is ready for use.

SOLUTION - 2

Shutdown the database.(shutdown).
Take complete backup of current database.
Startup mount exclusive;
Query the view v$datafile and note which datafile is offline say it is .
alter database datafile <’a'> online;
recover database;
alter database open ;
shutdown the database
Start the database.
Database is ready for use.

Regards

Srini

ANJANI APPS DBA

Oracle Apps DBA

Oracle Apps DBA

Monday, 5 October 2015

Oracle Apps DBA Recovery Procedures . srini

No comments:

Post a Comment

Popular Posts