臨時表空間恢複
1.建立臨時表空間
SQL> create temporary tablespace temp123 tempfile '/u01/app/oracle/oradata/orcl/temp123.dbf' size 20M;
2.更改為預設
SQL> alter database default temporary tablespace temp123;
3.SQL> drop tablespace temp;(删除)
==========================================================
日志檔案,控制檔案恢複
1.删除2個日志檔案
[oracle@sq orcl]$ rm -rf redo01.log redo03.log
删除所有控制檔案
[oracle@sq orcl]$ rm -rf *.ctl
2.
SQL> startup force(報錯)
3.
CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log' SIZE 50M,
GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log' SIZE 50M,
GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/orcl/system01.dbf',
'/u01/app/oracle/oradata/orcl/undotbs01.dbf',
'/u01/app/oracle/oradata/orcl/sysaux01.dbf',
'/u01/app/oracle/oradata/orcl/users01.dbf',
'/u01/app/oracle/oradata/orcl/aaa.dbf'
CHARACTER SET WE8ISO8859P1
;
4.SQL> select open_mode from v$database;
5.SQL> alter database open;(報錯)
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;(報錯)
SQL> recover database; (報錯)
檢視恢複目錄
SQL> show parameter DB_RECOVERY_FILE_DEST
SQL> recover database using BACKUP CONTROLFILE;
(using backup controlfile. 恢複就不會受“目前controlfile所紀錄的SCN”的限制) 成功
提示中輸入: /u01/app/oracle/oradata/orcl/redo02.log (目前就一個日志檔案)
SQL> alter database open resetlogs;
==========================================
部分資料庫恢複
1.SQL> alter tablespace users begin backup;
[oracle@sq orcl]$ cp users01.dbf /home/oracle/db_bak/
2.SQL> alter tablespace users end backup;
3.SQL> create table tt as select * from dba_objects;
SQL> select count(*) from tt;
SQL> commit;
4.[oracle@sq orcl]$ rm -rf users01.dbf (模拟損壞)
5.SQL> alter system flush buffer_cache;
SQL> conn sys/123456 as sysdba
6.SQL> startup force;(報錯 缺少4号檔案)
7.SQL> alter database datafile 4 offline;
[oracle@sq orcl]$ cp /home/oracle/db_bak/users01.dbf .
8.SQL> recover datafile 4;
SQL> alter database datafile 4 online;
9.SQL> startup force;
============================================
完全資料庫恢複
步驟跟上面一樣
recover database
歸檔模式資料檔案恢複
1.SQL> create tablespace tbs_recover datafile '/oracle/app/oradata/TEST/tbs_recover.dbf' size 10M;
SQL> create table tab_recover tablespace tbs_recover as select * from dba_objects;
SQL> commit;
3.SQL> alter system switch logfile;
SQL> /
4.[oracle@sq123 TEST]$ rm -rf tbs_recover.dbf
6.SQL> conn sys/123456 as sysdba
SQL> select * from tab_recover;(報錯)
7.SQL> alter database datafile 5 offline;
8.SQL> alter database create datafile '/oracle/app/oradata/TEST/tbs_recover.dbf';
(建立出表空間)
9.SQL> alter database datafile 5 online;(報錯)
SQL> recover datafile 5;
AUTO
10.SQL> alter database datafile 5 online;(成功)
SQL> select * from tab_recover;(資料回來了)
=============================================
不完全資料庫恢複
日志挖掘技術确定誤操作時間與scn
檢視目前時間
alter database begin backup;
cp *.dbf /home/oracle/db_bak
alter database end backup;
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; 2012-04-24 07:20:34
SQL> drop table tab_recover;
1.日志挖掘技術,确定誤删除表的時間
[oracle@sq123 ~]$ mkdir logminer
存儲目錄
SQL> alter system set utl_file_dir='/home/oracle/logminer' scope=spfile;
SQL> shutdown immediate
SQL> startup
2.建立存儲資料字典檔案的檔案
SQL> execute dbms_logmnr_d.build('shwdict.ora','/home/oracle/logminer');
cd /home/oracle/logminer/
SQL> execute dbms_logmnr.add_logfile('/oracel/app/oradtaa/TEST/',dbms_logmnr.new);
4.挖掘資料
SQL> exec dbms_logmnr.start_logmnr (dictfilename=>'/home/oracle/logminer/shwdict.ora');
5.
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
SQL> select SCN,TIMESTAMP,SQL_REDO from v$LOGMNR_CONTENTS where SQL_REDO like '%drop%';
結果:
716922 2012-03-12 06:12:38
drop table tab_recover AS "BIN$uv7sdpUIi1TgQAB/AQByfw==$0" ;
恢複資料
6.
SQL> shutdown abort;
SQL> startup mount
[oracle@sq123 db_bak]$ cp *.dbf /oracle/app/oradata/TEST/
7.
SQL> recover database until time '2012-04-24 07:20:34';
8.
SQL> select count(*) from tab_recover; (資料恢複出來了)
日志檔案恢複
1.[oracle@sq orcl]$ rm *.log
SQL> startup force(報錯)
2.SQL> alter database open resetlogs;(報錯)
3.SQL> recover database until cancel;
(執行RECOVER指令,在提示輸入歸檔日志時,應用這些歸檔日志提示輸入歸檔日志序列号時,不輸入這個檔案的名稱或者不接受建議的名稱,而是輸入CANCEL)
(恢複不能成功,因為我們沒有歸檔日志了)
4.添加隐藏參數
SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;
(強制啟動資料庫,設定此參數之後,在資料庫Open過程中,Oracle會跳過某些一緻性檢查,進而使資料庫可能跳過不一緻狀态,Open打開)
5.SQL> startup force
SQL> alter database open resetlogs;
(可以啟動,但是剛才沒有同步到硬碟的資料丢失了)
--後續操作
SQL> create pfile from spfile
然後vi pfile檔案
删除 *._allow_resetlogs_corruption
SQL> create spfile from pfile;
SQL> startup force
===============================================
本文轉自陳繼松 51CTO部落格,原文連結:http://blog.51cto.com/chenjisong/1737391,如需轉載請自行聯系原作者