The method of recovering Oracle data files by mis-deletion under Linux platform

1 Problem description

Previously wrote a blog to delete the Oracle home directory, refer to:

Linux platform mistakenly delete home Oracle root directory solution

http://blog.csdn.net/tianlesoftware/article/details/43794273

This is the depth of this side, it should have been organized years ago, dragged into the year after.

Simulation Status:

Database in normal operation, Misoperation, direct RM dropped the data file.

Test environment:

[Email protected] trace]$ cat/etc/redhat-release

Red Hat Enterprise Linux Server release 6.1 (Santiago)

Oracle 11.2.0.3 Single instance.

[Email protected] ~]$ Sqlplus/as SYSDBA

Sql*plus:release 11.2.0.3.0 Production onwed 27 18:36:32 2014

Copyright (c) 1982, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise editionrelease 11.2.0.3.0-64bit Production

With the partitioning, OLAP, Data miningand Real Application Testing Options

Sql>

This problem also has 2 kinds of cases, one is the archive mode, a non-archiving mode, archiving mode processing is much easier. But now a lot of developers managed libraries are non-archival, and also lack of professional operational skills, the probability of mis-operation will increase a lot.

2 Creating test data

sql> Create tablespace dropspacedatafile '/u01/dropspace01.dbf ' size 100m;

Tablespace created.

sql> Create user ahzhixinidentified by ahzhixin default tablespace dropspace;

User created.

Sql> grantconnect,resource,dba to Ahzhixin;

Grant succeeded.

Sql> Conn Ahzhixin/ahzhixin

Connected.

Sql> CREATE TABLE Test1 as SELECT * fromall_users;

Table created.

Sql> CREATE TABLE Test2 as SELECT * fromall_users;

Table created.

Sql> CREATE TABLE Test3 as SELECT * fromall_users;

Table created.

3 Archive Mode processing

3.1 Analog failure

sql> archive log list;

Database Log Mode Archive mode

Automatic Archival Enabled

Archive Destination/u01/archivelog

Oldest online log sequence 83

Next log sequence to archive 85

Current log Sequence 85

Sql>

Delete data files directly on the operating system:

[Email protected] u01]$ RM-RF/U01/DROPSPACE01.DBF

Now that the database is still working, let's look at some of the tables we created earlier:

Sql> Select COUNT (1) from Test1;

COUNT (1)

----------

31

Sql> Select COUNT (1) from Test2;

COUNT (1)

----------

31

Sql> Select COUNT (1) from Test3;

COUNT (1)

----------

31

--insert also has no problem:

sql> INSERT INTO test1 select * fromall_users;

Rows created.

Sql> commit;

Commit complete.

Sql> Select COUNT (1) from Test1;

COUNT (1)

----------

62

For the moment at least, everything is normal. This is also normal because our operating system is Linux, when the data file from the operating system level is RM off, but the process of opening the file still holds the corresponding file handle, so the point of the file can still read and write, and the file descriptor can be obtained from the/proc directory, You can also use this handle to recover files.

If you restart the database or the operating system at this time, the handle disappears and you can only scan the disk for file recovery.

3.2 Recovery

The DBWR process opens a handle to all data files, which can be found in the proc directory, where the directory name is the process PID,FD representing the file descriptor.

Check the process PID of the DBWR:

[Email protected] trace]$ Ps-ef|grep dbw0|grep-v grep

Oracle 9964 1 0 00:49? 00:00:03 Ora_dbw0_dave

[Email protected] trace]$ CD/PROC/9964/FD

[Email protected] fd]$ ls-l

The 259 here is the data file we deleted.

// Direct CP The handle file name back to the original location:

[email protected] fd]$ CP 259/u01/dropspace01.dbf

[Email protected] fd]$

Because the database has been open, then the SCN will continue to change, we have CP out of the data files and database current information is inconsistent, so we need to recover:

sql> ALTER DATABASE datafile '/U01/DROPSPACE01.DBF ' offline;

Database altered.

sql> recover datafile '/u01/dropspace01.dbf ';

Media recovery complete.

sql> ALTER DATABASE datafile '/U01/DROPSPACE01.DBF ' online;

Database altered.

Sql>

Back to normal.

// File exists:

[Email protected] u01]$ LS-LA/U01/DROPSPACE01.DBF

-RW-R-----1 Oracle oinstall 104865792 Aug27 21:41/u01/dropspace01.dbf

// To Restart the database:

sql> shutdown Immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

Sql> Startup

ORACLE instance started.

Total System Global area 814227456 bytes

Fixed Size 2232760 bytes

Variable Size 490737224 bytes

Database buffers 318767104 bytes

Redo buffers 2490368 bytes

Database mounted.

Database opened.

Sql>

also normal. Here are 2 things to note: The database is in archive mode, the database or the operating system is not restarted. These 2 points are critical. It's also official, so it's easier to operate.

It's a lot more complicated if it's a non-archive mode.

In the non-archive mode, if the data file is deleted and the ckpt is triggered, then CKPT will break the entire instance directly, that is, if it is a busy database, if the data file is deleted by mistake, the instance can be interrupted, and once the instance is interrupted, there is no possibility of using the previously spoken handle to recover.

Of course, there is another possibility, that is, after the deletion of the data file, you can first recover through the handle, and then use EXPDP to export data, as much as possible to save some of the data. This action is the process of running against time.

In short, the production environment, the operation must be careful, but also to open the archive, unless the data is allowed to be lost.

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

All rights reserved, the article prohibits reprint, otherwise investigates the legal liability!

Aboutdave:

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

qq:251097186

Email: [email protected]

Blog:http://blog.csdn.net/tianlesoftware

Weibo:http://weibo.com/tianlesoftware

Twitter:http://twitter.com/tianlesoftware

Facebook:http://www.facebook.com/tianlesoftware

Linkedin:http://cn.linkedin.com/in/tianlesoftware

Dave's QQ Group:

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

Note: Add group must indicate tablespace and data file relationship | Do not repeat the addition group

cndba_1:62697850 (empty) cndba_2:62697716 (full) cndba_3:283816689

cndba_4:391125754 cndba_5:104207940 cndba_6:62697977 cndba_7:142216823 (full)

The method of recovering Oracle data files by mis-deletion under Linux platform