Recovery of Oracle data files being deleted by physical error under Linux

Rapid recovery of Oracle in #加深对Linux句柄的理解/emergency situations

Unlike dropping data files from Oracle, in some cases it may be possible to encounter a database at runtime when the data file is deleted at the operating system level, while the Oracle instance does not crash and remains open. At this point, it is required to perform the recovery as efficiently as possible under minimal impact. The recovery process is now sorted out in case of a rainy future.

<< Process Simulation >>

<step 1> Simulation Delete

[email protected] >select name from V$datafile;

NAME

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

/ora_data/icsdb/system01.dbf

/ora_data/icsdb/sysaux01.dbf

/ora_data/icsdb/undotbs01.dbf

/ora_data/icsdb/users01.dbf

/ora_data/icsdb/icsdb01.bdf

/ora_data/icsdb/hr01.dbf

6 rows have been selected.

[Email protected] icsdb]# ls-ld ICSDB01.BDF

-rw-r-----. 1 Oracle oinstall 1073750016 May 16:24 ICSDB01.BDF

Check the data to see the current table and the number of data bars, there are 3 tables

[EMAIL protected] >select table_name from User_tables;

table_name

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

Sc

COURSE

STUDENT

As an example of the student table, there are 39 data in the table

[Email protected] >select count (*) from student;

COUNT (*)

----------

39

INSERT into student values (200216303, ' Wang Wei ', ' Male ', ' is ');

To delete a test data file

[Email protected] icsdb]# RM-RF/ORACLE_DATA/ICSDB/ICSDB01.DBF

Sql> Ho rm/oracle_data/icsdb/icsdb01.dbf--delete user data files;

The view data file is no longer available

[Email protected] icsdb]# ls-ld ICSDB01.BDF

LS: Unable to access ICSDB01.BDF: No file or directory

Don't restart the database here, or you'll lose it.

<step 2> recover data file by handle--first find the PID corresponding to the DB writer (3742)

[Email protected] icsdb]# Ps-ef|grep dbw0|grep-v grep

Oracle 3742 1 0 11:13? 00:00:00 Ora_dbw0_icsdb

--then find the handle to the file deleted by the process (3742), there may be multiple processes, there is only one

[Email protected] icsdb]# Ls-l/proc/3742/fd | grep deleted

LRWX------. 1 Oracle Oinstall 64 May 16:36 263-/ORA_DATA/ICSDB/ICSDB01.BDF (Deleted)

--Copy the handle of the deleted data file back to the data file and back up to TMP

[Email protected] icsdb]# CP/PROC/3742/FD/263/TMP/ICSDB01.DBF

Restart database test recovery, close database shutdown will be error

[Email protected] >shutdown immediate;

ORA-01116: Error opening database file 5 o'clock

ORA-01110: Data file 5: '/ORA_DATA/ICSDB/ICSDB01.BDF '

ORA-27041: Unable to open file

linux-x86_64 error:2: No such file or directory

Additional Information:3

Forcing the database to close

[Email protected] >shutdown Abort

The ORACLE routine has been closed.

Start the database test, boot can only boot to mount state, open not

[Email protected] >startup

The ORACLE routine has been started.

Total System Global area 471830528 bytes

Fixed Size 2254344 bytes

Variable Size 360712696 bytes

Database buffers 104857600 bytes

Redo buffers 4005888 bytes

The database is loaded.

ORA-01157: Unable to identify/lock data file 5-see DBWR trace file ORA-01110:

Data file 5: '/ORA_DATA/ICSDB/ICSDB01.BDF '

[Email protected] >select instance_name,status from V$instance;

instance_name STATUS

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

Icsdb Mounted

Copy the data file to the original directory and modify the group as Oracle

[Email protected] tmp]# MV icsdb01.dbf/ora_data/icsdb/

[Email protected] icsdb]# chown oracle:oinstall icsdb01.dbf

Alter tablespace icsdb rename datafile '/ora_data/icsdb/icsdb01.bdf ' to '/ora_data/icsdb/icsdb01.dbf ';

<step 3> recovering transactions through logs

The next step is a transaction recovery operation in two cases:

1) for the archive mode, simply offline out the data file, recover the data file and then online, such as:

sql> ALTER DATABASE datafile 4 offline;

Database altered.

sql> recover datafile 4;

Media recovery complete.

sql> ALTER DATABASE datafile 4 online;

Database altered.

2) If you are not archiving, you will encounter a ORA-01145 error when offline datafile, but you can copy the file handle after the

Try offline tablespace, and then online tablespace, this time will require the recovery of files mistakenly deleted before, if the log group has not switched to full coverage, then recover can be successful.

Sql> alter tablespace users offline;

Tablespace altered.

sql> recover datafile 4;

Media recovery complete.

sql> alter tablespace users online;

Tablespace altered.

Restore to completion!

<< Recovery Principles >>

In the Linux operating system, if the file is RM dropped from the operating system level, the process that opened the file still holds the corresponding file handle, and the file pointed to is still readable and writable.

and the file descriptor of the file can be obtained from the/proc directory. Note, however, that if you close the database at this point, the handle disappears, and there is no other way than to scan the disk for file recovery.

Therefore, in the event of a database problem, if you do not confirm the complexity of the situation, do not arbitrarily shut down the database. Restarting a database is often meaningless and even deadly.

Another: RM operation is always the most dangerous area on the system, you need to drive carefully!

This article is from "Learning to live like a tree" blog, please be sure to keep this source http://laobaiv1.blog.51cto.com/2893832/1948152

Recovery of Oracle data files being deleted by physical error under Linux