DBA

1. Loss of datafile(s) System Tablespace :-

Action...

sql>shutdown immediate

Oracle Instance shutdown.

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 Non-system(s) Tablespace :-

Action...

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 possiblyan 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 '' OFFLINE DROP;

SQL> ALTER DATABASE OPEN

SQL> DROP TABLESPACE 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 = ( ,...., )
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 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 '' 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 '';

SQL> ALTER DATABASE OPEN;

----------------------------------------------------------------------------------------------------------------------------------------
Assuming that the database is up and it is in ARCHIVELOG mode:

SQL> ALTER DATABASE DATAFILE '' OFFLINE;

SQL> -- Restore it from your backup.

SQL> RECOVER DATAFILE '';

SQL> ALTER DATABASE DATAFILE '' ONLINE;


-OR-

SQL> ALTER SYSTEM KILL SESSION ' , ' -- The transaction in RBS involved.

SQL> ALTER ROLLBACK SEGMENT OFFLINE;
\
SQL> DROP ROLLBACK SEGMENT ;

SQL> DROP TABLESPACE 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)
----------------------------------------------------------------------------------------------------------------------------------------
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)

----------------------------------------------------------------------------------------------------------------------------------------
. 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 */
--------------------------------------------------------------------------------------------------------------------------------------- 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;

Database Opened.