Archive

Posts Tagged ‘Oracle recovery’

Recovery of SYSTEM datafile.

Assumptions:

  • the database is in archive log mode
  • a solid cold backup is available
  • if the database is close then clean shutdown was performed

– shutdown the database

SHUTDOWN IMMEDIATE;

– restore the system01.dbf from the latest backup to the datafile location
– mount the database

STARTUP MOUNT;

– identify the datafile which needs recovery

select * from v$recover_file;

– recover the datafile

RECOVER DATAFILE <file_number>;

– open the database

ALTER DATABASE OPEN;

Hope, this helps.

Advertisements

Recovery of UNDO tablespace in the database with NOARCHIVELOG mode.

Every transaction stores the post modification data in the UNDO tablespace for a rollback or read consistency or flash back query or for recovery in case of abort. All system transactions will use the rollback segments created in the system tablespace. It cannot be used by other schema operations, they should use the undo tablespace. So an undo tablespace is a must for any transaction to occur. If the database is in ARCHIVELOG mode, the recovery of the UNDO tablespace is the same as that of any other non-system tablespace (with backup/without backup), but how to recover if the database is in NOARCHIVELOG mode?

1. Database is open.
– create the new UNDO tablespace

CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE 'd:\oracle\oradata\ORACLE_SID\undotbs02.dbf' SIZE 128M;

– set the initialization parameter UNDO_TABLESPACE to the proper value, if you use PFILE then edit the init.ora file, otherwise use the following command

ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2 SCOPE=SPFILE;

– restart the database

SHUTDOWN IMMEDIATE;
STARTUP;

– drop the old UNDO tablespace as it is no longer used

DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;

2. Database is close.
This will work only if the database was shutdown properly (shutdown immediate). If the database was aborted, oracle would do the ‘instance recovery’ whilst next startup and would terminate if the undo tablespace isn’t present.
– mount the database

STARTUP MOUNT;

– identify the datafile which needs recovery

select * from v$recover_file;

– connect datafile with tablespace

select t.name, f.name
from v$tablespace t, v$datafile f
where t.ts# = f.ts#
and f.file# = <file_number>;

– offline the missing datafile with ‘FOR DROP’ option (a datafile once marked ‘FOR DROP’ can never be brought online)

ALTER DATABASE DATAFILE <file_number> OFFLINE FOR DROP;

– open the database

ALTER DATABASE OPEN;

– create the new UNDO tablespace

CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE 'd:\oracle\oradata\ORACLE_SID\undotbs02.dbf' SIZE 128M;

– set the initialization parameter UNDO_TABLESPACE to the proper value, if you use PFILE then edit the init.ora file, otherwise use the following command

ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2 SCOPE=SPFILE;

– restart the database

SHUTDOWN IMMEDIATE;
STARTUP;

– drop the old UNDO tablespace as it is no longer used

DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;

Hope, this helps.

Recovery of non system datafile without backup.

Assumptions:

  • the database is in archive log mode

A non-system datafile can be recovered even if no backups are available for that datafile, provided all the archive logs since the creation of the datafile are available and the datafile is added after the control file is created. Datafiles created before the creation of the controlfile cannot be recoverd by this method.

1. Database is open.
– identify the datafile which needs recovery

select * from v$recover_file;

– connect datafile with tablespace

select t.name, f.name
from v$tablespace t, v$datafile f
where t.ts# = f.ts#
and f.file# = <file_number>;

– offline the tablespace with immediate option

ALTER TABLESPACE <tablespace_name> OFFLINE IMMEDIATE;

– create the missing datafile

ALTER DATABASE CREATE DATAFILE '<datafile_name>';

– recover the tablespace

RECOVER TABLESPACE <tablespace_name>;

– online the tablespace

ALTER TABLESPACE <tablespace_name> ONLINE;

2. Database is close.
– mount the database

STARTUP MOUNT;

– identify the datafile which needs recovery

select * from v$recover_file;

– connect datafile with tablespace

select t.name, f.name
from v$tablespace t, v$datafile f
where t.ts# = f.ts#
and f.file# = <file_number>;

– offline the missing datafile

ALTER DATABASE DATAFILE <file_number> OFFLINE;

– open the database

ALTER DATABASE OPEN;

– create the missing datafile

ALTER DATABASE CREATE DATAFILE '<datafile_name>';

– recover the tablespace

RECOVER TABLESPACE <tablespace_name>;

– online the tablespace

ALTER TABLESPACE <tablespace_name> ONLINE;

Hope, this helps.

Recovery of non system datafile.

Assumptions:

  • the database is in archive log mode
  • a solid cold backup is available

1. Database is open.
Users which use tablespace data from missing datafile get the follownig ORA-01116, ORA-01110 and/or ORA-27041 erros:

ORA-01116: error in opening database file 4
ORA-01110: data file 4: 'D:\oracle\oradata\ORACLE_SID\users01.dbf'
ORA-27041: unable to open file

Since the missing datafile is a non-system, the recovery can be done online. Users that does not use this tablespace data will continue to work as normal.

– identify the datafile which needs recovery

select * from v$recover_file;

– connect datafile with tablespace

select t.name, f.name
from v$tablespace t, v$datafile f
where t.ts# = f.ts#
and f.file# = <file_number>;

– offline the tablespace with immediate option

ALTER TABLESPACE <tablespace_name> OFFLINE IMMEDIATE;

– restore the missing datafile from the latest backup
– recover the datafile

RECOVER DATAFILE <file_number>;

– online the tablespace

ALTER TABLESPACE <tablespace_name> ONLINE;

2. Database is close.
We will be greeted with the ORA-01157 and ORA-01110 messages by Oracle, if any of the datafiles are missing:

ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: 'D:\oracle\oradata\ORACLE_SID\users01.dbf'

– mount the database

STARTUP MOUNT;

– identify the datafile which needs recovery

select * from v$recover_file;

– offline the missing datafile

ALTER DATABASE DATAFILE <file_number> OFFLINE;

– open the database

ALTER DATABASE OPEN;

– restore the missing file from the backup
– recover the datafile

RECOVER DATAFILE <file_number>;

– online the datafile

ALTER DATABASE DATAFILE <file_number> ONLINE;

Hope, this helps.

Recovery from loss of redo log file.

2012-03-29 1 comment

LGWR – logwriter process writes the redo data from the log buffer cache to the redo log files when,

  • a transaction is commited
  • every 3 seconds
  • when redo log buffer is 1/3 full

The LGWR process writes to the redo log file in a circluar fashion. After log group 1 is written, the LGWR will move to log group 2 and meanwhile ARC(archiver) will archive log group 1. The LGWR will complain if one or all of the members of a log group are missing. The database will be unable to serve any requests if all members of group are missing.

If all the members of a redo log group goes missing before the ARC process could archive it – Oracle complains with errors ORA-00313, ORA-00312 and/or ORA-27037. The database halts unable to switch the log file.

1. Recover from loss of active redo log file.
– shutdown the database

SHUTDOWN IMMEDIATE;

– restore the datafiles and proceed for an incomplete recovery until the missing log sequence
– mount the database

STARTUP MOUNT;

– check the status of the missing log file group from v$log view

select group#, sequence#, bytes, first_change#, to_char(first_time,’DD-MM-YY hh24:mi:ss’) tim, status from v$log;

– if STATUS = ACTIVE and ARCHIVED = NO

RECOVER DATABASE UNTIL TIME ‘yyyy-mm-dd:hh24:mi:ss’;

‘ss’ in the seconds should be 1 second less than the first_time of the missing redo log file.

– open the database with resetlogs option

ALTER DATABASE OPEN RESETLOGS;

– take a solid backup

2. Recover from loss of inactive redo log file.
– shutdown the database

SHUTDOWN IMMEDIATE;

– mount the database

STARTUP MOUNT;

– check the status of the missing log file group from v$log view

select * from v$log;

– if STATUS = INACTIVE and ARCHIVED = YES

ALTER DATABASE CLEAR LOGFILE GROUP ;

– if STATUS = INACTIVE and ARCHIVED = NO

ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP ;

The CLEAR command will create the missing log files at the OS level. Since the unarchived log file is cleared, there will NOT be an archive log of that sequence. Means, if log of seq#10 is cleared. Then archive log seq#10 will be not be created. It will be like seq#8,seq#9,seq#11,seq#12. So, rollforward using archive logs will not be possible. Also, all uncommitted transactions will be lost.

– open the database

ALTER DATABASE OPEN;

– take a good backup

Hope, this helps.