Get the App
SLTechnology News&Howtos  ›  Database  › 

Recovery of physically deleted oracle data files

Shulou Source: shulou.com Published: 2022-06-01 21:10:06 10月02日 Update

Test in archive mode:

(normal file system): the data file is physically deleted in open, and the data file is restored if it is not closed:

SQL > select file_name from dba_data_files

FILE_NAME

/ u01/oracle/oradata/CPP/example01.dbf

/ u01/oracle/oradata/CPP/users01.dbf

/ u01/oracle/oradata/CPP/undotbs01.dbf

/ u01/oracle/oradata/CPP/sysaux01.dbf

/ u01/oracle/oradata/CPP/system01.dbf

SQL > create tablespace test datafile'/ u01qqoracleUniple oradata size CPP Universe test01.dbf' oradata 10m

Tablespace created.SQL > create user test identified by test default tablespace test

User created.

SQL > grant connect, resource to test

Grant succeeded.

SQL > conn test/test

Connected.

SQL > grant dba to test

Grant succeeded.

SQL > conn test/test

Connected.

SQL > create table T1 as select * from dba_objects where rownum select table_name,tablespace_name from user_tables

TABLE_NAME TABLESPACE_NAME

T1 TEST

SQL > conn / as sysdba

Connected.

SQL > alter system checkpoint

System altered.

SQL > select file_name from dba_data_files

FILE_NAME

/ u01/oracle/oradata/CPP/example01.dbf

/ u01/oracle/oradata/CPP/users01.dbf

/ u01/oracle/oradata/CPP/undotbs01.dbf

/ u01/oracle/oradata/CPP/sysaux01.dbf

/ u01/oracle/oradata/CPP/system01.dbf

/ u01/oracle/oradata/CPP/test01.dbf

6 rows selected.

[root@orcl1 CPP] # ls

Control01.ctl redo01.log redo03.log system01.dbf test01.dbf users01.dbf

Example01.dbf redo02.log sysaux01.dbf temp01.dbf undotbs01.dbf

[root@orcl1 CPP] # rm-rf test01.dbf

SQL > create table T2 as select * from T1

Create table T2 as select * from T1 *

ERROR at line 1:

ORA-01116: error in opening database file 6

ORA-01110: data file 6:'/ u01Universe oradataUniverse CPP Universe test01.dbf'

ORA-27041: unable to open file

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

Additional information: 3

[oracle@orcl1 ~] $ps-ef | grep dbw0

Oracle 2898 1 0 09:24? 00:00:00 ora_dbw0_CPP

Oracle 5423 5382 3 09:50 pts/2 00:00:00 grep dbw0

[oracle@orcl1 ~] $su-root

Password:

[root@orcl1 ~] # cd / proc/2898/

[root@orcl1 2898] # ls

Attr clear_refs cwd fdinfo maps mountstats oom_score root smaps status

Autogroup cmdline environ io mem net oom_score_adj sched stack syscall

Auxv coredump_filter exe limits mountinfo numa_maps pagemap schedstat stat task

Cgroup cpuset fd loginuid mounts oom_adj personality sessionid statm wchan

[root@orcl1 2898] # cd fd

[root@orcl1 fd] # ls

0 1 10 11 2 256 257 258 259 260 261 262 263 264 3 4 56 7 8 9

[root@orcl1 fd] # ll

Total 0

Lr-x- 1 oracle oinstall 64 Jan 7 09:51 0-> / dev/null

Lmurwx-1 oracle oinstall 64 Jan 7 09:51 1-> / dev/null

Lrwx- 1 oracle oinstall 64 Jan 7 09:51 10-> / u01/oracle/product/11.2.0/db_1/dbs/lkCPP

Lr-x- 1 oracle oinstall 64 Jan 7 09:51 11-> / u01/oracle/product/11.2.0/db_1/rdbms/mesg/oraus.msb

Lmurwx-1 oracle oinstall 64 Jan 7 09:51 2-> / dev/null

Lrwx- 1 oracle oinstall 64 Jan 7 09:51 256-> / u01/oracle/oradata/CPP/control01.ctl

Lrwx- 1 oracle oinstall 64 Jan 7 09:51 257-> / u01/oracle/fast_recovery_area/CPP/control02.ctl

Lrwx- 1 oracle oinstall 64 Jan 7 09:51 258-> / u01/oracle/oradata/CPP/system01.dbf

Lrwx- 1 oracle oinstall 64 Jan 7 09:51 259-> / u01/oracle/oradata/CPP/sysaux01.dbf

Lrwx- 1 oracle oinstall 64 Jan 7 09:51 260-> / u01/oracle/oradata/CPP/undotbs01.dbf

Lrwx- 1 oracle oinstall 64 Jan 7 09:51 261-> / u01/oracle/oradata/CPP/users01.dbf

Lrwx- 1 oracle oinstall 64 Jan 7 09:51 262-> / u01/oracle/oradata/CPP/example01.dbf

Lrwx- 1 oracle oinstall 64 Jan 7 09:51 263-> / u01/oracle/oradata/CPP/temp01.dbf

Lrwx- 1 oracle oinstall 64 Jan 7 09:51 26464-> / u01/oracle/oradata/CPP/test01.dbf (deleted)

Lr-x- 1 oracle oinstall 64 Jan 7 09:51 3-> / dev/null

Lr-x- 1 oracle oinstall 64 Jan 7 09:51 4-> / dev/null

Lr-x- 1 oracle oinstall 64 Jan 7 09:51 5-> / dev/null

Lr-x- 1 oracle oinstall 64 Jan 7 09:51 6-> / u01/oracle/product/11.2.0/db_1/rdbms/mesg/oraus.msb

Lr-x- 1 oracle oinstall 64 Jan 7 09:51 7-> / proc/2898/fd

Lr-x- 1 oracle oinstall 64 Jan 7 09:51 8-> / dev/zero

Lrwx- 1 oracle oinstall 64 Jan 7 09:51 9-> / u01/oracle/product/11.2.0/db_1/dbs/hc_CPP.dat

[oracle@orcl1 fd] # cp 264 / u01/oracle/oradata/CPP/test01.dbf

SQL > select name,status from v$datafile

NAME STATUS

/ u01/oracle/oradata/CPP/system01.dbf SYSTEM

/ u01/oracle/oradata/CPP/sysaux01.dbf ONLINE

/ u01/oracle/oradata/CPP/undotbs01.dbf ONLINE

/ u01/oracle/oradata/CPP/users01.dbf ONLINE

/ u01/oracle/oradata/CPP/example01.dbf ONLINE

/ u01/oracle/oradata/CPP/test01.dbf ONLINE

6 rows selected.

SQL > alter database datafile'/ u01qqoracleUniple oradata offline CPP Universe test01.dbf'r

Database altered.

SQL > select name,status from v$datafile

NAME STATUS

/ u01/oracle/oradata/CPP/system01.dbf SYSTEM

/ u01/oracle/oradata/CPP/sysaux01.dbf ONLINE

/ u01/oracle/oradata/CPP/undotbs01.dbf ONLINE

/ u01/oracle/oradata/CPP/users01.dbf ONLINE

/ u01/oracle/oradata/CPP/example01.dbf ONLINE

/ u01/oracle/oradata/CPP/test01.dbf RECOVER

6 rows selected.

SQL > recover datafile'/ u01qqoracle.oradataUniplicCPP Universe test01.dbf'

Media recovery complete.

SQL > alter database datafile'/ u01qqoracleUniple oradata online CPP Universe test01.dbf'r

Database altered.

SQL > select name,status from v$datafile

NAME STATUS

/ u01/oracle/oradata/CPP/system01.dbf SYSTEM

/ u01/oracle/oradata/CPP/sysaux01.dbf ONLINE

/ u01/oracle/oradata/CPP/undotbs01.dbf ONLINE

/ u01/oracle/oradata/CPP/users01.dbf ONLINE

/ u01/oracle/oradata/CPP/example01.dbf ONLINE

/ u01/oracle/oradata/CPP/test01.dbf ONLINE

6 rows selected.

SQL > conn test/test

Connected.

SQL > create table T2 as select * from T1

Table created.

(ASM file system) data files are physically deleted in open, and the shutdown condition is restored:

SQL > create tablespace test datafile'+ DATA/mecbs/datafile/test01.dbf' size 10m

Tablespace created.

SQL > conn / as sysdba

Connected.

SQL > create user test identified by test default tablespace test

User created.

SQL > grant connect,resource to test

Grant succeeded.

SQL > conn test/test

Connected.

SQL > conn / as sysdba

Connected.

SQL > grant dba to test

Grant succeeded.

SQL > conn test/test

Connected.

SQL > create table T1 as select * from dba_objects where rownum select table_name,tablespace_name from user_tables

TABLE_NAME TABLESPACE_NAME

T1 TEST

SQL > select file_name from dba_data_files

FILE_NAME

+ DATA/mecbs/datafile/users.259.862339391

+ DATA/mecbs/datafile/undotbs1.258.862339391

+ DATA/mecbs/datafile/sysaux.257.862339391

+ DATA/mecbs/datafile/system.256.862339387

+ DATA/mecbs/datafile/example.264.862339751

+ DATA/mecbs/datafile/undotbs2.265.862341013

+ DATA/mecbs/datafile/system01.dbf

+ DATA/mecbs/datafile/crm01.dbf

+ DATA/mecbs/datafile/test01.dbf

+ DATA/mecbs/datafile/cross.dbf

+ DATA/mecbs/datafile/aix_trans.dbf

11 rows selected.

ASMCMD [+ data/mecbs/DATAFILE] > ls

AIX_TRANS.281.868377837

CRM.276.863565267

CROSSTBS.279.868372675

EXAMPLE.264.862339751

SYSAUX.257.862339391

SYSTEM.256.862339387

SYSTEM.275.863564943

TEST.278.868380831

UNDOTBS1.258.862339391

UNDOTBS2.265.862341013

USERS.259.862339391

Aix_trans.dbf

Crm01.dbf

Cross.dbf

System01.dbf

Test01.dbf

SQL > alter tablespace test offline

Tablespace altered.

ASMCMD [+ data/mecbs/DATAFILE] > rm-rf test01.dbf

ASMCMD [+ data/mecbs/DATAFILE] >

SQL > select name,status from v$datafile

NAME STATUS

+ DATA/mecbs/datafile/system.256.862339387 SYSTEM

+ DATA/mecbs/datafile/sysaux.257.862339391 ONLINE

+ DATA/mecbs/datafile/undotbs1.258.862339391 ONLINE

+ DATA/mecbs/datafile/users.259.862339391 ONLINE

+ DATA/mecbs/datafile/example.264.862339751 ONLINE

+ DATA/mecbs/datafile/undotbs2.265.862341013 ONLINE

+ DATA/mecbs/datafile/system01.dbf SYSTEM

+ DATA/mecbs/datafile/crm01.dbf ONLINE

+ DATA/mecbs/datafile/test01.dbf OFFLINE

+ DATA/mecbs/datafile/cross.dbf ONLINE

+ DATA/mecbs/datafile/aix_trans.dbf ONLINE

SQL > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL > startup mount

ORACLE instance started.

Total System Global Area 484356096 bytes

Fixed Size 2254464 bytes

Variable Size 264243584 bytes

Database Buffers 209715200 bytes

Redo Buffers 8142848 bytes

Database mounted.

SQL > alter database create datafile'+ DATA/mecbs/datafile/test01.dbf'

Database altered.

SQL > recover datafile'+ DATA/mecbs/datafile/test01.dbf'

Media recovery complete.

SQL > alter database open

Database altered.

SQL > select name,status from v$datafile

NAME STATUS

+ DATA/mecbs/datafile/system.256.862339387 SYSTEM

+ DATA/mecbs/datafile/sysaux.257.862339391 ONLINE

+ DATA/mecbs/datafile/undotbs1.258.862339391 ONLINE

+ DATA/mecbs/datafile/users.259.862339391 ONLINE

+ DATA/mecbs/datafile/example.264.862339751 ONLINE

+ DATA/mecbs/datafile/undotbs2.265.862341013 ONLINE

+ DATA/mecbs/datafile/system01.dbf SYSTEM

+ DATA/mecbs/datafile/crm01.dbf ONLINE

+ DATA/mecbs/datafile/test01.dbf OFFLINE

+ DATA/mecbs/datafile/cross.dbf ONLINE

+ DATA/mecbs/datafile/aix_trans.dbf ONLINE

11 rows selected.

SQL > alter tablespace test online

Tablespace altered.

SQL > select name,status from v$datafile

NAME STATUS

+ DATA/mecbs/datafile/system.256.862339387 SYSTEM

+ DATA/mecbs/datafile/sysaux.257.862339391 ONLINE

+ DATA/mecbs/datafile/undotbs1.258.862339391 ONLINE

+ DATA/mecbs/datafile/users.259.862339391 ONLINE

+ DATA/mecbs/datafile/example.264.862339751 ONLINE

+ DATA/mecbs/datafile/undotbs2.265.862341013 ONLINE

+ DATA/mecbs/datafile/system01.dbf SYSTEM

+ DATA/mecbs/datafile/crm01.dbf ONLINE

+ DATA/mecbs/datafile/test01.dbf ONLINE

+ DATA/mecbs/datafile/cross.dbf ONLINE

+ DATA/mecbs/datafile/aix_trans.dbf ONLINE

11 rows selected.

SQL > conn test/test

Connected.

SQL > select count (*) from T1

COUNT (*)

-

one thousand

Tags: File data physics situation state system normal mode test Apple Docker Huawei Linux macOS MariaDB Microsoft MySQL NVidia OPPO Reno Redmi macOS OPPO Reno Xiaomi Apple

Related Articles