#眉標=Oracle、LogMiner、Recyclebin
#副標=Oracle資料庫優化實務經驗談(3)
#大標=實作資料庫災難復原(下)
#作者=文／圖 賞金獵人


===<反灰>=============
select * from jack.aaa as of timestamp to_timestamp('20100725 05:00:00', 'YYYYMMDD HH24:MI:SS')
==<end>==============




===<反灰>=============
select * from jack.aaa as of SCN 2827502
==<end>==============



===<反灰>=============
select versions_startscn,versions_starttime,versions_endscn,versions_endtime,versions_xid,versions_operation,aa,bb from jack.aaa versions between timestamp minvalue and maxvalue order by aa, versions_starttime;
==<end>==============




===<反灰>=============
select versions_startscn,versions_starttime,versions_endscn,versions_endtime,versions_xid,versions_operation,aa,bb from jack.aaa versions between SCN minvalue and maxvalue order by aa, versions_starttime;
==<end>==============




===<反灰>=============
select start_scn, start_timestamp, commit_scn, commit_timestamp, logon_user,operation, Table_name,undo_sql from flashback_transaction_query where logon_user='JACK'
==<end>==============





===<反灰>=============
alter table jack.aaa enable row movement;
FLASHBACK TABLE jack.aaa TO TIMESTAMP TO_TIMESTAMP('2010-07-25 17:50:25','YYYY-MM-DD HH24:MI:SS')
==<end>==============




===<反灰>=============
Oradebug setmypid
Oradebug unlimit
Oradebug dump controlf n ( n為1時所能查看的資訊最少，數字越大所查看的資訊越多)
==<end>==============



===<反灰>=============
STARTUP NOMOUNT
--如果Online redo log也損毀，用以下寫法
CREATE CONTROLFILE REUSE DATABASE "PRODUCT" RESETLOGS NOARCHIVELOG
--如果Online redo log未損毀，用以下寫法
--CREATE CONTROLFILE REUSE DATABASE "PRODUCT" NORESETLOGS NOARCHIVELOG
    MAXLOGFILES 5
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 226
LOGFILE
  GROUP 1 'C:\app\Administrator\oradata\product\REDO01.LOG'  SIZE 50M,
  GROUP 2 'C:\app\Administrator\oradata\product\REDO02.LOG'  SIZE 50M,
  GROUP 3 'C:\app\Administrator\oradata\product\REDO03.LOG'  SIZE 50M
DATAFILE
  'C:\app\Administrator\oradata\product\SYSTEM01.DBF',
  'C:\app\Administrator\oradata\product\SYSAUX01.DBF',
  'C:\app\Administrator\oradata\product\UNDOTBS01.DBF',  
  'C:\app\Administrator\oradata\product\USERS01.DBF'
CHARACTER SET AL32UTF8;
--如果使用Flashback功能，需執行以下指令
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
--如果Online redo log也損毀，用以下寫法
ALTER DATABASE OPEN RESETLOGS;
--如果Online redo log未損毀，用以下寫法
--ALTER DATABASE OPEN;
==<end>==============




===<反灰>=============
---建立一個過渡的Temp tablespace
create temporary tablespace TEMP2 TEMPFILE 'C:\APP\ADMINISTRATOR\ORADATA\PRODUCT\TEMP02.DBF' SIZE 512M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
alter database default temporary tablespace temp2;

---刪除在系統中原有的後再重建
drop tablespace temp including contents and datafiles;
create temporary tablespace TEMP TEMPFILE 'C:\APP\ADMINISTRATOR\ORADATA\PRODUCT\TEMP02.DBF' SIZE 512M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
alter database default temporary tablespace temp;

---刪除過渡的Temp tablespace
drop tablespace temp2 including contents and datafiles;
==<end>==============





===<反灰>=============
---處於Non-archived log mode、或Archive log mode，且已archived
alter database clear logfile group 1;

---處於archived log mode，且該檔尚未archived
alter database clear unarchived logfile group 1;
alter database open;

---另一種處理方式：Drop掉該log file再重建，如圖9所示
==<end>==============



===<反灰>=============
---先進行上述的處理動作，如果不行再進行以下步驟
Shutdown immediate

---修改pfile參數檔，加入_allow_resetlogs_corruption=TRUE，並將spfile改名。如此啟動時就會主動抓pfile
Startup; ---此時會報錯誤
Recover database until cancel; 
alter database open resetlogs;

---將資料庫完整的export
Shutdown immediate

--移除pfile中的_allow_resetlogs_corruption，並將spfile改回原來的名稱
startup
==<end>==============



Bountyhunter.z@hotmail.com