oracle user-maneged recovery（二）

将数据文件还原到新位置的恢复过程：

1.使用操作系统命令COPY文件到新位置

2.启动并装载数据库

3.使用alter database命令更新控制文件 例如：alter database rename file 'xxxxxxxx' to 'xxxxxxxx';

SQL> create tablespace tbs_test datafile '/database/oradata/skyread/tbs_test.dbf' size 10M; --创建测试表空间

Tablespace created.

SQL> select file_name from dba_data_files where tablespace_name='TBS_TEST';

FILE_NAME

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

/database/oradata/skyread/tbs_test.dbf

1 rows selected.

SQL> alter tablespace tbs_test begin backup; --热备

Tablespace altered.

SQL> !

oracle@readerlogdb-> cp /database/oradata/skyread/tbs_test.dbf /home/oracle/tbs_test.dbf

oracle@readerlogdb-> exit

exit

SQL> alter tablespace tbs_test end backup;

Tablespace altered.

SQL> alter system archive log current;

System altered.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> startup mount;

ORACLE instance started.

Total System Global Area 5049942016 bytes

Fixed Size 2090880 bytes

Variable Size 1375733888 bytes

Database Buffers 3657433088 bytes

Redo Buffers 14684160 bytes

Database mounted.

SQL> alter database rename file '/database/oradata/skyread/tbs_test.dbf' to '/home/oracle/tbs_test.dbf'; --用新位置的数据文件还原

Database altered.

SQL> alter database open; --打开数据库

alter database open

*

ERROR at line 1:

ORA-01113: file 2 needs media recovery

ORA-01110: data file 2: '/home/oracle/tbs_test.dbf'

SQL> recover automatic datafile 2; --需要介质恢复，恢复一下

Media recovery complete.

SQL> alter database open;

SQL> select file_name，tablespace_name from dba_data_files where tablespace_name='TBS_TEST'; --可见已经用到新位置的数据文件

FILE_NAME TABLESPACE_NAME

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

/home/oracle/tbs_test.dbf TBS_TEST

完全恢复：

1.恢复关闭的数据库

恢复的文件是系统表空间或者UNDO表空间

需要恢复整个数据库或者大多数数据文件

数据库非7*24小时运行

SQL> alter tablespace SYSTEM begin backup;

Tablespace altered.

SQL> !cp /database/oradata/skyread/system01.dbf /home/oracle/system01.dbf --热备system表空间

SQL> alter tablespace system end backup;

Tablespace altered.

SQL> create table t2 (a int);

SQL> ! mv /database/oradata/skyread/system01.dbf /database/oradata/skyread/system01.dbf.bak --损坏system表空间

SQL> shutdown abort;

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area 5049942016 bytes

Fixed Size 2090880 bytes

Variable Size 1375733888 bytes

Database Buffers 3657433088 bytes

Redo Buffers 14684160 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

ORA-01110: data file 1: '/database/oradata/skyread/system01.dbf'

SQL> ! cp /home/oracle/system01.dbf /database/oradata/skyread/system01.dbf --从备份中copy文件

SQL> recover database; --介质恢复，这里也可以用recover datafile 1;应用了所有归档和redo

Media recovery complete.

SQL> alter database open; --打开数据库

Database altered.

SQL> desc t2 --看到恢复到了宕机前的状态

Name Null? Type

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

A NUMBER(38)

2.恢复打开的数据库（数据库没有因为故障宕机）：

损坏的文件不是系统表空间和回滚表空间

7*24小时运行

未导致数据库关闭

SQL> alter tablespace TBS_SF begin backup;

SQL> !cp /database/oradata/skyread/sf01.dbf /home/oracle/sf01.dbf --进行热备

SQL> alter tablespace tbs_sf end backup;

rm -rf /database/oradata/skyread/sf01.dbf --模拟故障系统误删数据文件

SQL> drop table t1; --数据文件不可用

drop table t1

*

ERROR at line 1:

ORA-01116: error in opening database file 26

ORA-01110: data file 26: '/database/oradata/skyread/sf01.dbf'

ORA-27041: unable to open file

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

Additional information: 3

SQL> col NAME for a50

SQL> SELECT d.file# f#, d.name, d.status, h.status

2 FROM v$datafile d, v$datafile_header h

3 WHERE d.file# = h.file#;

F# NAME STATUS STATUS

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

1 /database/oradata/skyread/system01.dbf SYSTEM ONLINE

3 /database/oradata/skyread/sysaux01.dbf ONLINE ONLINE

4 /database/oradata/skyread/users01.dbf ONLINE ONLINE

18 /database2/oradata/skyread/TBS_MRPMUSIC01.dbf ONLINE ONLINE

26 /database/oradata/skyread/sf01.dbf ONLINE ONLINE

31 /database2/oradata/skyread/undotbs02 ONLINE ONLINE

SQL> ! cp /home/oracle/sf01.dbf /database/oradata/skyread/sf01.dbf --copy备份的数据文件

SQL> recover datafile 26; --进行恢复出错

ORA-00283: recovery session canceled due to errors

ORA-01124: cannot recover data file 26 - file is in use or recovery

ORA-01110: data file 26: '/database/oradata/skyread/sf01.dbf'

SQL> alter database datafile '/database/oradata/skyread/sf01.dbf' offline drop; --手工offline

Database altered.

SQL> SELECT d.file# f#, d.name, d.status, h.status

2 FROM v$datafile d, v$datafile_header h

3 WHERE d.file# = h.file#;

F# NAME STATUS STATUS

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

1 /database/oradata/skyread/system01.dbf SYSTEM ONLINE

3 /database/oradata/skyread/sysaux01.dbf ONLINE ONLINE

4 /database/oradata/skyread/users01.dbf ONLINE ONLINE

18 /database2/oradata/skyread/TBS_MRPMUSIC01.dbf ONLINE ONLINE

26 /database/oradata/skyread/sf01.dbf RECOVER OFFLINE

31 /database2/oradata/skyread/undotbs02 ONLINE ONLINE

6 rows selected.

SQL> recover datafile 26; --再次进行介质恢复

Media recovery complete.

SQL> alter database datafile '/database/oradata/skyread/sf01.dbf' online; --数据文件online

Database altered.

SQL> drop table t1; --恢复正常

Table dropped

这里需要注意的是，在误删了数据文件后，数据并没有自动offline数据文件，有时候可能会自动offline文件，如果没有自动offline

需要手工去offline该文件，还有就是如果一个表空间含有多个数据文件，如果只有某一个数据文件损坏，不影响表空间使用，只是损坏

的数据文件里的数据无法使用。