|
"What
befalls the earth befalls all the sons of the earth. This we
know: the earth does not belong to man, man belongs to the
earth. All things are connected like the blood that unites us
all. Man does not weave this web of life. He is merely a
strand of it. Whatever he does to the web, he does to
himself."
-Chief
Seattle (1786-1866)
|
COMMON
Oracle Database Recoveries...
Summary
Steps
COMMON
Oracle Database Recoveries:
1.
Loss of data file(s) of SYSTEM
tablespace
Solution:
SQL>
SHUTDOWN IMMEDIATE;
SQL>
host cp backupfile file
(Restore your data backup files from backup to related loss system’s
datafiles)
SQL>
CONNECT / AS SYSDBA
SQL>
STARTUP MOUNT
SQL>
RECOVER DATABASE;
SQL>
ALTER DATABASE OPEN;
2.
Loss of data file(s) of Non-System
tablespace.
Solution:
>Don’t
need to shutdown database.
SQL>
ALTER TABLESPACE … OFFLINE;
SQL>
host cp backupfile file
(Restore your data backup files from backup to related loss system’s
datafiles)
SQL>
RECOVER TABLESPACE your_tablespace_name;
SQL>
ALTER TABLESPACE your_tablespace_name ONLINE;
Or
recover by using datafile(s).
SQL>
ALTER DATAFILE ‘/u01/…’ OFFLINE;
SQL>
host cp backupfile file
(Restore your data backup files from backup to related loss system’s
datafiles)
SQL>
RECOVER DATAFILE ‘/u01…’;
SQL>
ALTER DATAFIL ‘/u01/…’ ONLINE;
Or
shutdown database.
SQL>
SHUTDOWN IMMDIATE;
SQL>
(Restore your data backup files from backup to related loss system’s
datafiles)
SQL>
STARTUP MOUNT;
SQL>
RECOVER DATABASE;
SQL>
ALTER DATABASE OPEN;
3.
Loss of data file(s) of rollback OR
undo segments tablespace.
A
datafile in a rollback segment tablespace has been lost or damaged to
a point that
Oracle
cannot recognize it anymore.
If
you try to startup the database will result in ORA-1157, ORA-1110, and
possibly
an
operating system level error such as ORA-7360. Trying to shut down the
database
in
normal or immediate mode will result in ORA-1116, ORA-1110, and
possibly
an
operating system level error such as ORA-7368.
PLEASE
NOTE that before using _corrupted_rollback_segments
parameter, you should
explore
all other options. This will require extra caution. Call Oracle
Customer Support
if
you have any questions or need any assistance.
The
Database is down:
Assuming
that your database was cleanly shutdown (check your alert.log)
SQL>
SHUTDOWN IMMEDIATE/NORMAL
SQL>
STARTUP RESTRICT MOUNT
SQL>
ALTER DATABASE DATAFILE '<full_path_file_name>' OFFLINE DROP;
SQL>
ALTER DATABASE OPEN
SQL>
DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS;
SQL>
-- Recreate the rollback tablespace with all its rollback segments.
Remember to bring the rollbacks online after you create them.
Assuming
that you cann't open your database and
you get the following error
messages:
ORA-604, ORA-376, and ORA-1110.
--
Comment out the ROLLBACK_SEGMENTS parameter and add the following
line:
_corrupted_rollback_segments
= ( <rollback1>,...., <rollbackN> )
with
exact the same rollbacks.
WARNING:
Use this parameter ONLY IN THIS SPECIFIC SCENARIO or as
instructed
by Oracle Customer Support.
SQL>
STARTUP RESTRICT
SQL>
DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS;
SQL>
Recreate the rollback tablespace with all its rollback segments.
SQL>
ALTER SYSTEM DISABLE RESTRICTED SESSION;
--
put back your ROLLBACK_SEGMENTS parameter in the init.ora file.
--
remove _CORRUPTED_ROLLBACK_SEGMENTS PARAMETER
SQL>
-- Rebuild the database.
Assuming
that database was not cleanly shutdown:
--
Restore the lost file from a backup.
SQL>
STARTUP MOUNT
SQL>
SELECT FILE#, NAME, STATUS FROM V$DATAFILE;
SQL>
ALTER DATABASE DATAFILE '<full_path_file_name>' ONLINE;
(if datafile is offline)
SQL>
SELECT V1.GROUP#, MEMBER, SEQUENCE#, FIRST_CHANGE#
FROM V$LOG V1, V$LOGFILE V2
WHERE V1.GROUP# = V2.GROUP# ;
SQL>
SELECT FILE#, CHANGE# FROM V$RECOVER_FILE;
If
NOARCHIVELOG Mode and the CHANGE# is GREATER than the minimum
FIRST_CHANGE#
of
your logs, the datafile can be recovered.
SQL>
RECOVER DATAFILE '<full_path_file_name>';
SQL>
ALTER DATABASE OPEN;
Assuming
that the database is up and it is in ARCHIVELOG mode:
SQL>
ALTER DATABASE DATAFILE '<full_path_file_name>' OFFLINE;
SQL>
-- Restore it from your backup.
SQL>
RECOVER DATAFILE '<full_path_file_name>';
SQL>
ALTER DATABASE DATAFILE '<full_path_file_name>' ONLINE;
-OR-
SQL>
ALTER SYSTEM KILL SESSION ' , ' -- The transaction in RBS involved.
SQL>
ALTER ROLLBACK SEGMENT <rollback_segment> OFFLINE;
SQL>
DROP ROLLBACK SEGMENT <rollback_segment>;
SQL>
DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS;
SQL>
CREATE TABLESPACE rbs ...;
SQL>
CREATE ROLLBACK SEGMENT rbs01 ...;
SQL>
ALTER ROLLBACK SEGMENT ... ONLINE;
In
this case to access to data you will get a message like this:
ORA-00376:
file 20 connot be read at this time
File
2 happens to be one of the data files that belongs to the rollback
segment tablespace.
Solution:
SQL>
select segment_name, status from dba_rollback_segs;
(If status are in “NEED RECOVERY”)
SQL>
SHOW PARAMETER undo
SQL>
CREATE UNDO TABLESPACE myundotbs DATAFILE ‘/u01/…’ SIZE
50M;
SQL>
ALTER SYSTEM SET undo_tablespace=’myundotbs’;
SQL>
DROP TABLESPACE undotbs;
(Remove all related OS datafiles)
4.
Loss of Rollback segments while there were
transaction activities;
For
example:
SQL>
CREATE TABLE mytest (col1 varchar2(20));
SQL>
BEGIN
FOR this IN 1..100 LOOP
INSERT INTO mytest VALUSE (this);
END LOOP;
END;
/
SQL>
/* Notice that we didn’t commit any of above transaction */
SQL>
/* shutdown abort or any crash on rollback datafiles will cause
The
following error messages if we start the database. */
ORA-01157:
cannot identify/lock data file 20 – see DBWR trace file
ORA-01110:
data file 20: ‘/u01/…’
Solution:
If
you shutdown the server then you will get a message like this:
ORA-01157:
cannot identify/lock data file 8 - see DBWR trace file
ORA-01110:
data file 8: 'C:\MYUNDOTBS_01.DBF'
SQL>
STARTUP MOUNT
SQL>
ALTER DATABASE DATAFILE 'C:\MYUNDOTBS_01.DBF' OFFLINE;
SQL>
ALTER DATABASE OPEN;
SQL>
/* remember you can not access to that table with transaction */
SQL>
SELECT segment_name, status FROM dba_rollback_segs;
(You will see that the segments are NEEDS RECOVERY)
SQL>
/* replace your backup file and recover the tablespace */
SQL>
RECOVER TABLESPACE your_tablespace_name;
SQL>
ALTER TABLESPACE your_tablespace_name ONLINE;
SQL>
ALTER ROLLBACK SEGMENT … ONLINE;
(Repeat this for all of your rollback segments)
If
you are not shutdown:
SQL>
ALTER DATABASE DATAFILE 'C:\MYUNDOTBS_01.DBF' OFFLINE;
SQL>
/* remember you can not access to that table with transaction */
SQL>
SELECT tablespace_name, segment_name, status FROM dba_rollback_segs;
(You will see that the segments are NEEDS RECOVERY)
SQL>
/* replace your backup file and recover the tablespace */
SQL>
RECOVER TABLESPACE your_tablespace_name;
SQL>
ALTER TABLESPACE your_tablespace_name ONLINE;
SQL>
ALTER ROLLBACK SEGMENT … ONLINE;
(Repeat this for all of your rollback segments)
5.
Loss of an UN-archived Online log
file.
Solution:
SQL>
/* Your database is done. */
SQL>
/* Replace all your datafiles and online redo logfile, but not
controlfiles. */
SQL>
host cp … …
SQL>
STARTUP MOUNT
SQL>
RECOVER DATABASE UNTIL CANCEL; -- UNTIL TIME …
SQL>
ALTER DATABASE OPEN RESETLOGS;
/* Since you did incomplete recovery, you must use ‘RESETLOGS’
option.
SQL>
SHUTDOWN IMMEDIATE
/* Do your cold backup immediately */
Loss
of your Online Redo-log files.
Solution:
SQL>
/* If you loss your online redo-log files, your instance will hung or
crash.
If not, try to shutdown abort. Restore all files from your cold backup
with exception of your control files. */
SQL>
STARTUP MOUNT
SQL>
/* Now do an incomplete recovery recover using CANCEL or TIME option
*/
SQL>
RECOVER DATABASE UNTIL CANCEL;
/* you should apply up to your last archive and then cancel the
recovery process */
SQL>
ALTER DATABASE OPEN RESETLOGS;
/* Since you did incomplete recovery, you must use ‘RESETLOGS’
option.
SQL>
SHUTDOWN IMMEDIATE
/* Do your cold backup immediately */
6.
Loss of your control file.
Solution:
SQL>
/* Assuming you have already backup controlfile to trace */
SQL>
ALTER DATABASE BACKUP CONTROLFILE TO trace;
SQL>
/* modify and run your trace file and your control file is up to date
*/
SQL>
/* if not, then copy a old control file */
SQL>
STARTUP MOUNT;
SQL>
RECOVER DATABASE USING BACKUP CONTROLFILE;
SQL>
ALTER DATABASE OPEN;
|