Shulou(Shulou.com)06/01 Report--

This article mainly introduces "how to restore data files and archive logs mistakenly deleted in Linux". In daily operation, I believe that many people have doubts about how to restore data files and archive logs mistakenly deleted in Linux. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts of "how to restore data files and archive logs in Linux". Next, please follow the editor to study!

Before erroneous deletion, there are 2 archived logs and 4480064 user data.

The code is as follows:

[oracle@station90 datafile] $sqlplus / nolog

SQL > conn hr/hr

Connected.

SQL > host ls-l / u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_01_17/*.arc

-rw-r- 1 oracle oinstall 50048000 Jan 17 16:56 / u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_01_17/o1_mf_1_5_8hhh6hhp_.arc

-rw-r- 1 oracle oinstall 50049536 Jan 17 16:56 / u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_01_17/o1_mf_1_6_8hhh6x0t_.arc

SQL > select count (*) from test

COUNT (*)

-

4480064

2. Users continue to insert 200w pieces of data, and the final number of archived logs is 3.

The code is as follows:

SQL > insert into test select * from test where rownum commit

Commit complete.

SQL > host ls-l / u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_01_17/*.arc

-rw-r- 1 oracle oinstall 50048000 Jan 17 16:56 / u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_01_17/o1_mf_1_5_8hhh6hhp_.arc

-rw-r- 1 oracle oinstall 50049536 Jan 17 16:56 / u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_01_17/o1_mf_1_6_8hhh6x0t_.arc

-rw-r- 1 oracle oinstall 50049024 Jan 17 17:08 / u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_01_17/o1_mf_1_7_8hhhx0h7_.arc

3. Delete all archive logs, one of which is the archive resulting from the insertion of 200w records

The code is as follows:

SQL > host rm / u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_01_17/*.arc

SQL > host ls-l / u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_01_17/*.arc

Ls: / u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_01_17/*.arc: No such file or directory

4. The final data volume of test is 6480064.

The code is as follows:

SQL > select count (*) from test

COUNT (*)

-

6480064

5. Delete all data files under the users tablespace

The code is as follows:

SQL > conn / as sysdba

Connected.

SQL > select file_name from dba_data_files where tablespace_name='USERS'

FILE_NAME

/ u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_5qd5zcsh_.dbf

/ u01/app/oracle/oradata/ORCL/datafile/users01.dbf

SQL > host rm / u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_5qd5zcsh_.dbf

SQL > host rm / u01/app/oracle/oradata/ORCL/datafile/users01.dbf

5. Create a table in the Users tablespace and report an error

The code is as follows:

SQL > conn hr/hr

Connected.

SQL > create table test33 (a number)

Create table test33 (a number)

*

ERROR at line 1:

ORA-01116: error in opening database file 4

ORA-01110: data file 4:

'/ u01appranumoracleandoradata _ orCLamp _ datafileplico _ 1qd5zcshroom.dbf'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

SQL > quit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-64bit Production

With the Partitioning, OLAP and Data Mining options

5. View the dbwr process PID

The code is as follows:

[oracle@station90 ~] $ps-ef | grep ora_dbw | grep-v grep

Oracle 9878 1 0 16:20? 00:00:02 ora_dbw0_orcl

6. The dbwr process opens the handle to all data files. It can be found in the / proc directory that the directory name is the process PID,fd represents the file descriptor

The code is as follows:

[oracle@station90 ~] $cd / proc/9878/fd

[oracle@station90 fd] $ls-l

Total 0

Lr-x- 1 oracle oinstall 64 Jan 17 17:11 0-> / dev/null

Lr-x- 1 oracle oinstall 64 Jan 17 17:11 1-> / dev/null

Lrwx- 1 oracle oinstall 64 Jan 17 17:11 10-> / u01/app/oracle/admin/orcl/adump/ora_9870.aud

Lr-x- 1 oracle oinstall 64 Jan 17 17:11 11-> / dev/zero

Lr-x- 1 oracle oinstall 64 Jan 17 17:11 12-> / dev/zero

Lr-x- 1 oracle oinstall 64 Jan 17 17:11 13-> / u01/app/oracle/product/10.2.0/db_1/rdbms/mesg/oraus.msb

Lrwx- 1 oracle oinstall 64 Jan 17 17:11 14-> / u01/app/oracle/product/10.2.0/db_1/dbs/hc_orcl.dat

Lrwx- 1 oracle oinstall 64 Jan 17 17:11 15-> / u01/app/oracle/product/10.2.0/db_1/dbs/lkORCL

Lrwx- 1 oracle oinstall 64 Jan 17 17:11 16-> / u01/app/oracle/oradata/ORCL/controlfile/o1_mf_5qd60qj5_.ctl

Lrwx- 1 oracle oinstall 64 Jan 17 17:11 17-> / u01/app/oracle/flash_recovery_area/ORCL/controlfile/o1_mf_5qd60qq6_.ctl

Lrwx- 1 oracle oinstall 64 Jan 17 17:11 18-> / u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_5qd5zcop_.dbf

Lrwx- 1 oracle oinstall 64 Jan 17 17:11 19-> / u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_5qd5zcrn_.dbf

Lr-x- 1 oracle oinstall 64 Jan 17 17:11 2-> / dev/null

Lrwx- 1 oracle oinstall 64 Jan 17 17:11 20-> / u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_5qd5zcot_.dbf

Lrwx- 1 oracle oinstall 64 Jan 17 17:11 21-> socket: [138219]

Lrwx- 1 oracle oinstall 64 Jan 17 17:11 22-> / u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_5qd616wf_.dbf

Lrwx- 1 oracle oinstall 64 Jan 17 17:11 23-> / u01/app/oracle/oradata/ORCL/datafile/o1_mf_temp_5qd614y5_.tmp

Lr-x- 1 oracle oinstall 64 Jan 17 17:11 24-> / u01/app/oracle/product/10.2.0/db_1/rdbms/mesg/oraus.msb

Lrwx- 1 oracle oinstall 64 Jan 17 17:11 25-> / u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_5qd5zcsh_.dbf (deleted)

Lrwx- 1 oracle oinstall 64 Jan 17 17:11 26-> / u01/app/oracle/oradata/ORCL/datafile/users01.dbf (deleted)

Lr-x- 1 oracle oinstall 64 Jan 17 17:11 3-> / dev/null

Lr-x- 1 oracle oinstall 64 Jan 17 17:11 4-> / dev/null

Lmurwx-1 oracle oinstall 64 Jan 17 17:11 5-> / u01/app/oracle/admin/orcl/udump/orcl_ora_9870.trc

Lmurwx-1 oracle oinstall 64 Jan 17 17:11 6-> / u01/app/oracle/admin/orcl/bdump/alert_orcl.log

Lrwx- 1 oracle oinstall 64 Jan 17 17:11 7-> / u01/app/oracle/product/10.2.0/db_1/dbs/lkinstorcl (deleted)

Lmurwx-1 oracle oinstall 64 Jan 17 17:11 8-> / u01/app/oracle/admin/orcl/bdump/alert_orcl.log

Lrwx- 1 oracle oinstall 64 Jan 17 17:11 9-> / u01/app/oracle/product/10.2.0/db_1/dbs/hc_orcl.dat

[oracle@station90 fd] $ls-l | grep users

Lrwx- 1 oracle oinstall 64 Jan 17 17:11 25-> / u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_5qd5zcsh_.dbf (deleted)

Lrwx- 1 oracle oinstall 64 Jan 17 17:11 26-> / u01/app/oracle/oradata/ORCL/datafile/users01.dbf (deleted)

Note that the word "/ u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_5qd5zcsh_.dbf (deleted)" indicates that the file has been deleted

7. Directly cp the handle file name back to its original location

The code is as follows:

[oracle@station90fd] $cp25 / u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_5qd5zcsh_.dbf

[oracle@station90 fd] $cp 26 / u01/app/oracle/oradata/ORCL/datafile/users01.dbf

8. Restore data files

The code is as follows:

[oracle@station90 fd] $sqlplus / nolog

SQL*Plus: Release 10.2.0.1.0-Production on Thu Jan 17 17:12:27 2013

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

SQL > conn / as sysdba

Connected.

SQL > select file_id,file_name from dba_data_files where tablespace_name='USERS'

FILE_ID

-

FILE_NAME

four

/ u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_5qd5zcsh_.dbf

six

/ u01/app/oracle/oradata/ORCL/datafile/users01.dbf

SQL > alter database datafile 4 offline

Database altered.

SQL > recover datafile 4

Media recovery complete.

SQL > alter database datafile 4 online

Database altered.

SQL > alter database datafile 6 offline

Database altered.

SQL > recover datafile 6

Media recovery complete.

SQL > alter database datafile 6 online

Database altered.

9. Complete data recovery

The code is as follows:

SQL > conn hr/hr

Connected.

SQL > select count (*) from test

COUNT (*)

-

6480064

The principle of recovery is that in the Linux operating system, if a file is rm from the operating system level, the process that opened the file before still holds the corresponding file handle, the file it points to can still read and write, and the file descriptor of the file can be obtained from the / proc directory. However, it should be noted that if you close the database at this time, this handle will disappear, so there is no other way but to scan the disk for file recovery, so when there is a problem with the database, if you are not sure of the complexity of the situation, do not shut down the database casually. Restarting a database is often pointless, even fatal.

In addition, if the control file is rm, it cannot be recovered in this way

At the same time, the data files and even the archived logs are deleted through the rm of linux. As long as the database does not crash and the dbw0 process is still there, the data can be completely restored, which has nothing to do with whether there are archived logs, but it is different from whether archiving is enabled.

At this point, the study on "mistakenly deleted data files and archived logs in Linux" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.