恢复的基础操作
先说完全恢复:从备份时候的SCN 应用到CURRENT REDOLOG的最后一个SCN
recover database(不能OPEN恢复)
recover tablespace
recover datafile
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 84
Next log sequence to archive 87
Current log sequence 87
SQL> select tablespace_name ,file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
------------------------------ ------------------------------
EXAMPLE E:\ORACLE\PRODUCT\10.1.0\ORADA
TA\ORCL\EXAMPLE01.DBF
USERS E:\ORACLE\PRODUCT\10.1.0\ORADA
TA\ORCL\USERS01.DBF
SYSAUX E:\ORACLE\PRODUCT\10.1.0\ORADA
TA\ORCL\SYSAUX01.DBF
UNDOTBS1 E:\ORACLE\PRODUCT\10.1.0\ORADA
TA\ORCL\UNDOTBS01.DBF
TABLESPACE_NAME FILE_NAME
------------------------------ ------------------------------
SYSTEM E:\ORACLE\PRODUCT\10.1.0\ORADA
TA\ORCL\SYSTEM01.DBF
TEST D:\TEST.DBF
6 rows selected.
SQL> alter tablespace test begin backup;
Tablespace altered.
SQL> host copy d:\test.dbf e:\test.dbf
已复制 1 个文件。
SQL> alter tablespace test end backup;
Tablespace altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> host del d:\test.dbf
SQL> startup
ORACLE instance started.
Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 10 - see DBWR trace file
ORA-01110: data file 10: 'D:\TEST.DBF'~~~~~~~~~~~~~~~
SQL> col error format a30
SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE#
---------- ------- ------- ------------------------------ ----------
TIME
--------------
10 ONLINE ONLINE FILE NOT FOUND 0
SQL> select file#,status,recover from v$datafile_header;
FILE# STATUS REC
---------- ------- ---
1 ONLINE NO
2 ONLINE NO
3 ONLINE NO
4 ONLINE NO
5 ONLINE NO
10 ONLINE File needs media recovery (YES | NO)
SQL> host copy e:\test.dbf d:\test.dbf;
已复制 1 个文件。
SQL> select file#,status,recover from v$datafile_header;
FILE# STATUS REC
---------- ------- ---
1 ONLINE NO
2 ONLINE NO
3 ONLINE NO
4 ONLINE NO
5 ONLINE NO
10 ONLINE YES~~~~~~~~~~~~~~~~~~~~~~~~~要恢复
6 rows selected.
SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE#
---------- ------- ------- ------------------------------ ----------
TIME
--------------
10 ONLINE ONLINE 7615801~~~~从这开始应用redo
19-8月 -09
SQL> recover tablespace test;*************************
Media recovery complete.
SQL> select file#,status,recover from v$datafile_header;~~~~~~~~~~~~~~~
FILE# STATUS REC
---------- ------- ---
1 ONLINE NO
2 ONLINE NO
3 ONLINE NO
4 ONLINE NO
5 ONLINE NO
10 ONLINE NO
6 rows selected.
SQL> select * from v$recover_file;~~~~~~~~~~~~~~~~~~~~~
no rows selected
SQL> alter database open;~~~~~~~~~~~~~~~~~~~
Database altered.
SQL> select tablespace_name ,file_name,status from dba_data_files;
TABLESPACE_NAME FILE_NAME STATUS
------------------------------ ------------------------------ ---------
EXAMPLE E:\ORACLE\PRODUCT\10.1.0\ORADA AVAILABLE
TA\ORCL\EXAMPLE01.DBF
USERS E:\ORACLE\PRODUCT\10.1.0\ORADA AVAILABLE
TA\ORCL\USERS01.DBF
SYSAUX E:\ORACLE\PRODUCT\10.1.0\ORADA AVAILABLE
TA\ORCL\SYSAUX01.DBF
UNDOTBS1 E:\ORACLE\PRODUCT\10.1.0\ORADA AVAILABLE
TA\ORCL\UNDOTBS01.DBF
TABLESPACE_NAME FILE_NAME STATUS
------------------------------ ------------------------------ ---------
SYSTEM E:\ORACLE\PRODUCT\10.1.0\ORADA AVAILABLE
TA\ORCL\SYSTEM01.DBF
TEST D:\TEST.DBF AVAILABLE
6 rows selected.
~~~~~~~~~~~~~~~~~~~~~~~~~~非常简单的恢复
重新指定新位置
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> host del d:\test.dbf
SQL> startup
ORACLE instance started.
Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 10 - see DBWR trace file
ORA-01110: data file 10: 'D:\TEST.DBF'
SQL> alter database datafile 10 offline;~~~~~~~~~~~offline后就不检查了 就可以OPEN了
Database altered.
SQL> alter database open;
Database altered.
SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE#
---------- ------- ------- ------------------------------ ----------
TIME
--------------
10 OFFLINE OFFLINE FILE NOT FOUND 0
SQL> alter database rename file 'd:\test.dbf' to 'e:\test.dbf';~~~~若原来位置用不了了 放入
新位置 先改controlfile(正好备份在E:\TEST.DBF)
Database altered.
SQL> reocver tablespace test;
~~~~~~~~~~~~~~~~~~~~
SQL> col file_name format a30
SQL> select tablespace_name ,file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
------------------------------ ------------------------------
EXAMPLE E:\ORACLE\PRODUCT\10.1.0\ORADA
TA\ORCL\EXAMPLE01.DBF
USERS E:\ORACLE\PRODUCT\10.1.0\ORADA
TA\ORCL\USERS01.DBF
SYSAUX E:\ORACLE\PRODUCT\10.1.0\ORADA
TA\ORCL\SYSAUX01.DBF
UNDOTBS1 E:\ORACLE\PRODUCT\10.1.0\ORADA
TA\ORCL\UNDOTBS01.DBF
TABLESPACE_NAME FILE_NAME
------------------------------ ------------------------------
SYSTEM E:\ORACLE\PRODUCT\10.1.0\ORADA
TA\ORCL\SYSTEM01.DBF
TEST E:\TEST.DBF
6 rows selected. 恢复database(MOUNT下) 与恢复datafile与此类似
使用rman来恢复
RMAN> backup tablespace "TEST"
2> ;
Starting backup at 20-8月 -09
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00010 name=E:\TEST.DBF
channel ORA_DISK_1: starting piece 1 at 20-8月 -09
channel ORA_DISK_1: finished piece 1 at 20-8月 -09
piece handle=E:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\XHTEST\BACKUPSET\2009_
08_20\O1_MF_NNNDF_TAG20090820T163233_58T2K1OH_.BKP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 20-8月 -09
RMAN> list backup of tablespace 'TEST';
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
32 Full 304K DISK 00:00:03 20-8月 -09
BP Key: 34 Status: AVAILABLE Compressed: NO Tag: TAG20090820T163233
Piece Name: E:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\XHTEST\BACKUPSE
T\2009_08_20\O1_MF_NNNDF_TAG20090820T163233_58T2K1OH_.BKP
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> host del e:\test.dbf;
SQL> startup
ORACLE instance started.
RMAN> run{restore tablespace 'TEST';~~~~~~~~~~~~~~~~~~~~
2> recover tablespace 'TEST';};~~~~~~~~~~~~
Starting restore at 20-8月 -09
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=142 devtype=DISK
channel ORA_DISK_1: restoring datafile 00010
input datafilecopy recid=4 stamp=695388316 filename=D:\TEST.DBF
destination for restore of datafile 00010: E:\TEST.DBF
channel ORA_DISK_1: copied datafilecopy of datafile 00010
output filename=E:\TEST.DBF recid=5 stamp=695407018
Finished restore at 20-8月 -09
Starting recover at 20-8月 -09
using channel ORA_DISK_1
starting media recovery
media recovery complete~~~~~~~~~~~~~~~~~~~~~~~~~
SQL> select tablespace_name ,file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
------------------------------ ------------------------------
EXAMPLE E:\ORACLE\PRODUCT\10.1.0\ORADA
TA\ORCL\EXAMPLE01.DBF
USERS E:\ORACLE\PRODUCT\10.1.0\ORADA
TA\ORCL\USERS01.DBF
SYSAUX E:\ORACLE\PRODUCT\10.1.0\ORADA
TA\ORCL\SYSAUX01.DBF
UNDOTBS1 E:\ORACLE\PRODUCT\10.1.0\ORADA
TA\ORCL\UNDOTBS01.DBF
TABLESPACE_NAME FILE_NAME
------------------------------ ------------------------------
SYSTEM E:\ORACLE\PRODUCT\10.1.0\ORADA
TA\ORCL\SYSTEM01.DBF
TEST E:\TEST.DBF
6 rows selected.
如果原先位置不能用的话
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> host del e:\test.dbf;
SQL> startup
ORACLE instance started.
Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 10 - see DBWR trace file
ORA-01110: data file 10: 'E:\TEST.DBF'
RMAN> run{set newname for datafile 'E:\TEST.DBF' to 'D:\TEST.DBF';~~~~该restore到的位置
2> restore tablespace 'TEST';~~~~~~~~restore
3> switch datafile all;~~~~~~~~~~改controlfile
4> recover tablespace 'TEST';~~~~~~~~~~~~~~~~~~~~~恢复
5> sql 'alter database datafile 10 online';};
Finished recover at 20-8月 -09
SQL> alter database open;
Database altered.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12020513/viewspace-612770/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/12020513/viewspace-612770/