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 恢復表空間】 實驗Oracle
- oracle 增量備份恢復驗證Oracle
- NBU恢復oracleOracle
- oracle使用小記、刪除恢復Oracle
- oracle冷備恢復Oracle
- oracle 異機恢復Oracle
- 微信聊天記錄的恢復
- 伺服器資料恢復案例：FreeNAS資料恢復過程記錄伺服器資料恢復
- Oracle案例12——NBU Oracle恢復Oracle
- 表空間TSPITR恢復-實驗
- Oracle 不完全恢復遇到的ORA-600錯誤Oracle
- 如何進行Oracle資料庫不完全恢復RBOracle資料庫
- Oracle RMAN恢復測試Oracle
- 透過搭建恢復目錄實現RMAN異地備份和恢復
- Oracle 目錄許可權丟失故障恢復Oracle
- [20191213]不完全恢復疑問.txt
- [20190130]刪除tab$記錄的恢復.txt
- Oracle RushQL勒索病毒恢復方法Oracle
- Oracle 備份 與 恢復 概述Oracle
- Oracle 備份恢復之 FlashbackOracle
- Oracle恢復誤刪資料Oracle
- ORACLE備份&恢復案例(轉)Oracle
- oracle基於SCN增量恢復Oracle
- Oracle Redo丟失恢復方案Oracle
- Oracle RMAN 表空間恢復Oracle
- RMAN恢復實踐
- Oracle Database 12c RAC損壞ocr和votedisk恢復實驗OracleDatabase
- ORACLE資料庫基於時間點的不完全恢復Oracle資料庫
- 記錄一次Oracle 11.2.0.4 RAC異地恢復到單例項Oracle單例
- 【11g 庫異地恢復】實驗
- 【12c 庫異機恢復】實驗
- 恢復聊天記錄手機軟體如何使用
- 備份與恢復oracle_homeOracle
- Oracle 備份和恢復介紹Oracle
- ORACLE備份&恢復案例三(轉)Oracle
- Oracle資料庫恢復之resetlogsOracle資料庫
- ORACLE備份&恢復案例二(轉)Oracle
- Networker恢復oracle rac到單機Oracle