oracle實驗記錄 （恢復-完全恢復）
恢復的基礎操作
先說完全恢復:從備份時候的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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle實驗記錄 （恢復-不完全恢復）Oracle
- oracle實驗記錄 （恢復-rman恢復）Oracle
- oracle實驗記錄 （恢復-redo）Oracle
- oracle實驗記錄（恢復-checkpoint cnt）Oracle
- oracle實驗記錄 （可恢復session）OracleSession
- oracle實驗記錄 （恢復-使用resetlogs open前備份恢復）Oracle
- oracle實驗記錄 （恢復-rman基於控制檔案的恢復）Oracle
- oracle實驗記錄 （恢復-rman catalog）Oracle
- oracle實驗記錄 （恢復-rman保留策略）Oracle
- oracle實驗記錄 （恢復-恢復未備份的資料檔案）Oracle
- oracle實驗記錄 （恢復-rman維護(1)）Oracle
- oracle實驗記錄 （恢復-rman維護(2)）Oracle
- oracle實驗記錄 (恢復-rman增量備份)Oracle
- oracle實驗記錄 （恢復-表空間基於時間點恢復(rman)）Oracle
- oracle實驗記錄 （恢復-表空間基於時間點恢復(手動)）Oracle
- oracle實驗記錄 (恢復-rman reset incatnation(1))Oracle
- oracle實驗記錄 （恢復-關於熱備份）Oracle
- oracle實驗記錄 （恢復read only tablespace(1)）Oracle
- oracle實驗記錄 （恢復read only tablespace(2)）Oracle
- oracle實驗記錄 （恢復-rman操作delete(all) input ）Oracledelete
- 記錄一次ORACLE的不完全恢復Oracle
- Oracle恢復實驗（一）Oracle
- Oracle恢復實驗（二）Oracle
- Oracle恢復實驗（三）Oracle
- Oracle恢復實驗（四）Oracle
- 【Mysql】完全恢復與不完全恢復MySql
- Oracle 不完全恢復Oracle
- Oracle手工完全恢復案例Oracle
- oracle實驗記錄 （恢復-關於控制檔案(1)）Oracle
- oracle實驗記錄 （恢復-關於控制檔案(2)）Oracle
- oracle實驗記錄 （恢復-關於控制檔案(3)）Oracle
- oracle實驗記錄 （恢復-關於控制檔案(4)）Oracle
- oracle實驗記錄 （恢復-rman操作(設定&備份)）Oracle
- RMAN全庫【完全恢復/不完全恢復brief version】
- oracle實驗記錄 (oracle 10G dataguard(9)rman恢復與dg)Oracle
- 【Oracle 恢復表空間】 實驗Oracle
- 【備份與恢復】控制檔案的恢復（不完全恢復）
- oracle實驗記錄 (恢復,備份-含壞塊資料檔案)Oracle