Home »

Restore and Recovery Tactics

Start here


Control Files: what if we lost all control files.
-> Create control files from scrash.
The good practise for this case is everytime we change DB structure -> Backup control file to trace

alter database backup controlfile to trace as ‘c:\oracle\product\control_bk.sql’;
–In this file, we can find sql to create controlfile.
With this case, database will startup no mount
-> alter database open;

Redo Logs: recovery
Case : Steps need to process when system corrupts.
1.Get the lastest backup to restore
2.Apply archive log (use RMAN to find)
3.Apply the most current redo log, which hasn’t updated to archive long

select a.group#, member, a.status, bytes
from v$log a, v$logfile b
where a.group# = b.group#
order by 1;

How to recover when thing goes wrong with redo/archive log

–We can alter size redo logfile to make checkpoint less offen
2 Options
2.1. Drop and recreate for inactive
alter database drop logfile group1;
alter database add logfile group1 (‘C:\oracle\….\redo1.log’) size 100M;

For current log,
alter system switch logfile;
alter database drop logfile group1;
alter database add logfile group1 (‘C:\oracle\….\redo1.log’) size 100M;

2.2. Alter logfile extend

–For case Undo tablespce crash
To simulate situation, we can shutdown DB, then rename Undo tbs datafile
-> Start DB -> ORA-01157
-> Check alert log
show parameter undo;

>alter system set undo_management = manual scope = spfile;
>aler database datafile ”  drop;

> shutdown immediate;
> startup;

> create undo tablespace undo datafile ‘..\UNDO01.DBF’ size 10M;
> alter system set undo_management =auto scope=spfile;
> shutdown immediate;
> start up;
select tablespace_name, contents
from dba_tablespaces;

> show parameter undo;
–if the old undo info is there, then
alter system drop ….;
alter system set undo_tablespace = ”;

Recovering non-critical datafiles

Recovering critical datafiles

Tuning : Monitoring Recovery

Startup:    NoMount                           ==>  Mount         ==> Open
(spfiles.ora or spfile.ora or initSID.ora)   (Control Files)    (Datafiles, Redo longs, Everthing in Sync)


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: