Oracle File Corruption and recovery process

I. Basic Information

OS: RHEL 3

CPU: 4 Intel (R) Xeon (TM) mp cpu 2.70 GHz

Mem: 8 GB

Swap: 16 GB

Disk: 120 GB

Oracle Database 10g Enterprise Edition Release 10.1.0.3.0

Ii. Backup Mode

The database runs in archive mode and supports multi-level Incremental backup of RMAN. The policy is as follows:

Sets automatic backup of control files.

A full database backup (including all the databases and read-only tablespaces) is performed every three months, and archiving logs are backed up.

Perform a zero-level backup every month (excluding the read-only tablespace) and back up and archive logs.

Perform a level-1 backup every week and back up archived logs.

Perform a secondary backup every day and back up archived logs.

 

Iii. recovery case

Prerequisites for all recovery: You have performed full database backup (including archiving logs), automatic backup of control files and spfiles.

1. corrupt a data file

(1) fault simulation

Delete data files: rm/u02/oradata/dbnms/users01.dbf

Shut down the database: shutdown immediate;

ORA-01116: error in opening database file 4

ORA-01110: data file 4: '/u02/oradata/dbnms/users01.dbf'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

Forced shutdown: sutdown abort;

Start Database: startup;

ORA-01157: cannot identify/lock data file 4-see DBWR trace file

ORA-01110: data file 4: '/u02/oradata/dbnms/users01.dbf'

(2) recovery steps

Rman target sys @ dbnms catalog rmanuser @ cata

Run {

Allocate channel c1 type disk;

Restore datafile 4;

Recover datafile 4;

SQL 'alter database datafile 4 online ';

SQL 'alter database open ';

Release channel c1;

}

Sqlplus sys as sysdba

Select instance_name, status from v $ instance;

INSTANCE_NAME STATUS

----------------------------

Dbnms OPEN

Recovery successful

If the data space is not backed up, the database must be forcibly started.

SQL> shutdown immediate;

SQL> startup mount;

SQL> alter database datafile '/oradata/test. dbf'' offline drop;

SQL> alter database open;

2. Damage to all data files

(1) fault simulation

Delete data files: rm/u02/oradata/dbnms/*. dbf

Forced shutdown: sutdown abort;

Start Database: startup;

ORA-01157: cannot identify/lock data file 1-see DBWR trace file

ORA-01110: data file 1: '/u02/oradata/dbnms/system01.dbf'

(2) recovery steps

Rman target sys @ dbnms catalog rmanuser @ cata

Run {

Allocate channel c1 type disk;

Restore database;

Recover database;

SQL 'alter database open ';

Release channel c1;

}

Sqlplus sys as sysdba

Select instance_name, status from v $ instance;

INSTANCE_NAME STATUS

----------------------------

Dbnms OPEN

Recover temporary files:

Alter database tempfile '/u02/oradata/dbnms/temp01.dbf' drop;

Alter tablespace temp add tempfile '/u02/oradata/dbnms/temp01.dbf' size 50 M autoextend on next 5 M maxsize unlimited;

Recovery successful

3. Damage to non-current online log members

(1) fault simulation

Delete a log file: rm/u02/oradata/dbnms/redo01.log

Shut down the database: shutdown immediate;

Start Database: startup;

Select * from v $ logfile; -- you can consider searching from the v $ log view.

GROUP # status type member is _

---------------------------------------------------------

3 ONLINE/u02/oradata/dbnms/redo03.log NO

2 stale online/u02/oradata/dbnms/redo02.log NO

1 invalid online/u02/oradata/dbnms/redo01.log NO

1 stale online/u02/oradata/dbnms/redo11.log NO

1 stale online/u02/oradata/dbnms/redo21.log NO

2 stale online/u02/oradata/dbnms/redo12.log NO

3 ONLINE/u02/oradata/dbnms/redo13.log NO

2 stale online/u02/oradata/dbnms/redo22.log NO

3 ONLINE/u02/oradata/dbnms/redo23.log NO

4 ONLINE/u02/oradata/dbnms/redo31.log NO

4 ONLINE/u02/oradata/dbnms/redo32.log NO

GROUP # status type member is _

---------------------------------------------------------

4 ONLINE/u02/oradata/dbnms/redo33.log NO

(2) recovery steps

Alter database drop logfile member '/u02/oradata/dbnms/redo01.log ';

Alter database add logfile member '/u02/oradata/dbnms/redo01.log' to group 1;

Recovery successful

1 2 3 Next Page