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

Do system management is like this, it is inevitable to delete files, one day if an Oracle data file is deleted, then how to restore it? (here the database is OPEN and is not closed.)

Establish a test tablespace

Create a test user

Insert test data

Delete data file

Restore database files

Establish a test tablespace

SQL > select name from v$datafile NAME----/opt/oracle/oradata/member/system01.dbf/opt/oracle/oradata/member/sysaux01.dbf/opt/oracle/oradata/member/undotbs01.dbf/opt/ Oracle/oradata/member/users01.dbfSQL > create tablespace test datafile'/ opt/oracle/oradata/member/test01.dbf' size 10m Tablespace created.SQL > select name from v$datafile NAME----/opt/oracle/oradata/member/system01.dbf/opt/oracle/oradata/member/sysaux01.dbf/opt/oracle/oradata/member/undotbs01.dbf/opt/ Oracle/oradata/member/users01.dbf/opt/oracle/oradata/member/test01.dbf

two。 Create a test account

SQL > create user test identified by test default tablespace test;SQL > grant connect,resource to test

3. Insert test data

SQL > conn test/testSQL > create table T1 (id int); SQL > insert into T1 values (1); SQL > select * from T1; ID- 1

4. Delete data file

[oracle@db2] $rm-f / opt/oracle/oradata/member/test01.dbf [oracle@db2 ~] $sqlplus test/testSQL > 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 5ORA-01110: data file 5:'/ opt/oracle/oradata/member/test01.dbf'ORA-27041: unable to open fileLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3SQL > select table_name,tablespace_name from user_tables TABLE_NAME TABLESPACE_NAME---T1 TEST

What should I do if the data file is deleted? Don't restart the database at this time, or the data will be lost.

5. Data file recovery

[oracle@db2 ~] $ps-ef | grep dbw0oracle 3309 10 12:07? 00:00:00 ora_dbw0_memberoracle 6217 5105 0 15:29 pts/0 00:00:00 grep dbw0# finds the process number of ora_dbw0_SID 3309 [oracle@db2 ~] $cd / proc/3309/fd# above 3309 is the process number, then execute ls-al to view the link to the file

You can see that file 27 is the deleted file.

[oracle@db2 fd] $cp 27 / opt/oracle/oradata/member/test01.dbf

View test tablespace status

SQL > select name,status from v$datafile NAME STATUS---/ opt/oracle/oradata/member/system01.dbf SYSTEM/opt/oracle/oradata/member/sysaux01.dbf ONLINE/opt/oracle/oradata/member/undotbs01.dbf ONLINE/opt/oracle/oradata/member/users01.dbf ONLINE/opt/oracle/oradata/member/test01.dbf ONLINE

Offline test01 table file

SQL > alter database datafile'/ opt/oracle/oradata/member/test01.dbf' offline;SQL > recover datafile'/ opt/oracle/oradata/member/test01.dbf';Media recovery complete.SQL > alter database datafile'/ opt/oracle/oradata/member/test01.dbf' online;Database altered.

# if the above is successfully restored, if it appears

SQL > recover datafile'/ opt/oracle/oradata/member/test01.dbf';ORA-00283: recovery session canceled due to errorsORA-01110: datafile 5:'/ opt/oracle/oradata/member/test01.dbf'ORA-01157: cannot identify/lock datafile 5-see DBWR trace fileORA-01110: datafile 5:'/ opt/oracle/oradata/member/test01.dbf'

It may be caused by the permission problem of the / opt/oracle/oradata/member/test01.dbf file, under the root user

[root@db2] # chown-R oracle.oinstall / opt/oracle/oradata/member/test01.dbf then recover datafile'/ opt/oracle/oradata/member/test01.dbf'

After the experiment is completed, delete the test user and test tablespace

SQL > drop user test cascade;SQL > drop tablespace test INCLUDING CONTENTS AND DATAFILES

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.