Oracle-真實(shí)環(huán)境的丟失current redo log file的故障恢復(fù)

背景：客戶找到我們，反饋有一套10.2.0.4版本的Oracle數(shù)據(jù)庫，運(yùn)行在Solaris Sparc 10的HA架構(gòu)上, 因?yàn)楣蚕泶鎯?chǔ)被寫滿與不恰當(dāng)?shù)牟僮鳎ㄟ@是后來的Sun工程師確認(rèn)），

導(dǎo)致數(shù)據(jù)庫異常。查看環(huán)境時(shí)，共享存儲(chǔ)不能被集群軟件掛載，同時(shí)，數(shù)據(jù)庫告警日志中除了歸檔日志寫滿的告警之外，未發(fā)現(xiàn)有其他錯(cuò)誤。同時(shí)，存儲(chǔ)工程師確認(rèn)存儲(chǔ)正常。

后續(xù)Sun主機(jī)工程師修復(fù)了掛載故障后發(fā)現(xiàn)，數(shù)據(jù)庫的當(dāng)前重做日志文件損壞，無法讀取。于是，我們只有做了一次強(qiáng)制開庫。

     共享存儲(chǔ)使用的是ZFS文件系統(tǒng)。

首先說明一下SMON的作用。初次恢復(fù)時(shí)，因?yàn)镾MON的原因，數(shù)據(jù)庫OPEN之后，還是會(huì)實(shí)例崩潰。后續(xù)增加了10061事件參數(shù)，_smon_internal_errlimit參數(shù)，導(dǎo)致

實(shí)例崩潰的錯(cuò)誤就少了不少

實(shí)施local instance recovery

實(shí)施OPS/RAC instance recovery

服務(wù)于排序段sort segment申請(qǐng)

實(shí)施transaction recovery(rollback)

清理不再使用的臨時(shí)段temporary segments

清理已經(jīng)被aged out的游標(biāo)所使用的臨時(shí)表temporary tables

清理dead instance的臨時(shí)表temporary tables

 刪除OBJ$基表上不再存在的對(duì)象記錄

 若index online rebuild失敗，則負(fù)責(zé)清理ind$和indpart$

合并extents

在適當(dāng)?shù)臅r(shí)機(jī)收縮 rollback segment

在適當(dāng)?shù)膶?shí)際offline rollback segment

恢復(fù)crash/instance recovery因datafile不可用(eg. offline)而跳過的dead transaction

恢復(fù)前臺(tái)進(jìn)程因?yàn)閏rash而造成的dead transaction

  SMON的控制事件event列表：

event='10061 trace name context forever, level 10'禁用SMON清理臨時(shí)段(disable SMON from cleaning temp segments)

event='10269 trace name context forever, level 10'來禁用SMON合并空閑區(qū)間(Don't do coalesces of free space in SMON)

event='10052 trace name context forever'來禁止SMON清理obj$基表

設(shè)置隱藏參數(shù)_column_tracking_level(column usage tracking)，該參數(shù)默認(rèn)為1即啟用column使用情況跟蹤。設(shè)置該參數(shù)為0，將禁用column tracking

events '10513 trace name context forever, level 2';設(shè)置10513事件來臨時(shí)禁止SMON恢復(fù)死事務(wù)，這在我們做某些異?；謴?fù)的時(shí)候顯得異常有效，當(dāng)然不建議在一個(gè)正常的生產(chǎn)環(huán)境中設(shè)置這個(gè)事件

event='8105 trace name context forever'來禁止SMON清理IND$(Oracle event to turn off smon cleanup for online index build)

events '12500 trace name context forever, level 10';可以在設(shè)置了12500事件后手動(dòng)刪除SMON_SCN_TIME上的記錄，重啟實(shí)例后SMON會(huì)繼續(xù)正常更新SMON_SCN_TIME。

event='10511 trace name context forever, level 1'來禁用SMON OFFLINE UNDO SEGS; 但是10511事件不會(huì)跳過”Fast Ramp Up”，而僅會(huì)限制SMON對(duì)UNDO SEGS產(chǎn)生的工作負(fù)載。 一旦設(shè)置了10511 event， 則所有已生成的 UNDO SEGS會(huì)始終保持ONLINE狀態(tài)。

 event='10512 trace name context forever,level 1' 禁用SMON shrink rollback segment

event='10510 trace name context forever,level 1' 禁用檢測以便offline rollback

參考：https://www.cnblogs.com/macleanoracle/archive/2013/03/19/2968335.html

使用的參數(shù)文件：

*._allow_resetlogs_corruption=TRUE

*.audit_file_dest='/opt/oracle/app/admin/orcl/adump'

*.background_dump_dest='/opt/oracle/app/admin/orcl/bdump'

*.compatible='10.2.0.3.0'

*.control_files='/dataora/orcl/control01.ctl','/dataora/orcl/control02.ctl','/dataora/orcl/control03.ctl'

*.core_dump_dest='/opt/oracle/app/admin/orcl/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='orcl'

*.db_recovery_file_dest='/orapool/dataora/flash_recovery_area'

*.db_recovery_file_dest_size=2147483648

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.job_queue_processes=0

*.log_archive_dest_1='location=/orapool/dataora/arch'

*.open_cursors=30000

*.pga_aggregate_target=3424649216

*.processes=1500

*.remote_login_passwordfile='EXCLUSIVE'

*.sessions=1655

*.sga_target=1610612736

*.sort_area_size=5242880

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.fast_start_parallel_rollback=FALSE

*.user_dump_dest='/opt/oracle/app/admin/orcl/udump'

event='10061 trace name context forever, level 10'

_smon_internal_errlimit=1000000

1. 恢復(fù)數(shù)據(jù)庫

recover database until cancel;

alter database open resetlogs;

2. 導(dǎo)出后遇到錯(cuò)誤

ORACLE Instance orcl (pid = 11) - Error 600 encountered while recovering transaction (3, 20) on object 658092.

Sat Jan 25 11:09:23 2020

Errors in file /opt/oracle/app/admin/orcl/bdump/orcl_smon_15656.trc:

ORA-00600: internal error code, arguments: [6006], [1], [], [], [], [], [], []

重建了索引：

Database mounted.

Database opened.

SQL> select owner, object_name, object_type from dba_objects where object_id = 658092;

OWNER

------------------------------

OBJECT_NAME

--------------------------------------------------------------------------------

OBJECT_TYPE

-------------------

SCOTT

IND_TEST

INDEX

SQL> 

SQL> alter index scott.IND_TEST rebuild;        

Index altered.

參考：https://www.eygle.com/archives/2011/07/ora-600_6006_recovery.html

3.導(dǎo)出數(shù)據(jù)，重建數(shù)據(jù)庫

4.導(dǎo)出數(shù)據(jù)

nohup exp \'/ as sysdba\' file=/new2-orapool/orcl_20200125_test.dmp owner=test &

5. 重建數(shù)據(jù)庫，校驗(yàn)數(shù)據(jù)，業(yè)務(wù)恢復(fù)