Add to Favorites　|　Login　|　Register
Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

About us Contact us

Recovery of physically deleted oracle data files

2025-03-25 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

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

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

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.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Tag Search: File data physics situation state system normal mode test vpn macOS Linux MariaDB MySQL Docker Shulou Information Shulou Technology Shulou Tech Info OPPO Reno Redmi Xiaomi NVidia Microsoft Huawei Apple OPPO Reno NVidia macOS Redmi vpn Microsoft Shulou Technology Docker Xiaomi Huawei Shulou Tech Info MySQL Linux MariaDB Apple Shulou Information OPPO Reno NVidia macOS Redmi vpn Microsoft Shulou Technology Docker Xiaomi Huawei Shulou Tech Info MySQL Linux MariaDB Apple Shulou Information

Share To

Related

Database

More Database >

Latest Network Security More Network Security >

Latest Internet Technology More Internet Technology >

Latest Development More Development >

Latest Database More Database >

Latest Servers More Servers >

Latest Mobile Phone More Mobile Phone >

Latest Android Software More Android Software >

Latest Apple Software More Apple Software >

Latest Computer Software News More Computer Software News >

Latest IT Information More IT Information >

Wechat

About us Contact us Product review car news thenatureplanet

© 2024 shulou.com SLNews company. All rights reserved.

12
Report