ORACLE一體機pcie 快取卡損壞導致資料庫dang機
資料庫版本：
19.11.0.0
非歸檔模式
報錯
ERROR at line 1:
ORA-01113: file 11 needs media recovery
ORA-01110: data file 11: '+DATAC1/JSPARAMS/datafile2/tbs_upc0001.dbf'
增加引數：
alter system set "_allow_resetlogs_corruption"=true scope=spfile;
alter system set "_allow_error_simulation"=true scope=spfile;
alter system set events '10513 trace name context forever,level 2';
alter database backup controlfile to trace as '/tmp/ctl.txt';
嘗試重建控制檔案：
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "JSPARAMS" RESETLOGS FORCE LOGGING NOARCHIVELOG
MAXLOGFILES 1024
MAXLOGMEMBERS 5
MAXDATAFILES 32767
MAXINSTANCES 48
MAXLOGHISTORY 33012
LOGFILE
GROUP 1 '+DATAC1/JSPARAMS/ONLINELOG/group_101' SIZE 1024M BLOCKSIZE 512,
GROUP 2 '+DATAC1/JSPARAMS/ONLINELOG/group_102' SIZE 1024M BLOCKSIZE 512,
GROUP 13 '+DATAC1/JSPARAMS/ONLINELOG/group_103' SIZE 1024M BLOCKSIZE 512,
GROUP 14 '+DATAC1/JSPARAMS/ONLINELOG/group_104' SIZE 1024M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'+DATAC1/JSPARAMS/datafile2/system.dbf',
'+DATAC1/JSPARAMS/datafile2/sysaux.dbf',
'+DATAC1/JSPARAMS/datafile2/undotbs1.dbf',
'+DATAC1/JSPARAMS/datafile2/undotbs2.dbf',
'+DATAC1/JSPARAMS/datafile2/undotbs3.dbf',
'+DATAC1/JSPARAMS/datafile2/undotbs4.dbf',
'+DATAC1/JSPARAMS/datafile2/undotbs5.dbf',
'+DATAC1/JSPARAMS/datafile2/undotbs6.dbf',
'+DATAC1/JSPARAMS/datafile2/users.dbf',
'+DATAC1/JSPARAMS/datafile2/tbs_params0001.dbf',
'+DATAC1/JSPARAMS/datafile2/tbs_upc0001.dbf',
'+DATAC1/JSPARAMS/datafile2/tbs_upc0002.dbf',
'+DATAC1/JSPARAMS/datafile2/idx_upc0001.dbf',
'+DATAC1/JSPARAMS/datafile2/idx_params0001.dbf',
'+DATAC1/JSPARAMS/datafile2/gj_01.dbf',
'+DATAC1/JSPARAMS/datafile2/gj_02.dbf',
'+DATAC1/JSPARAMS/datafile2/tbs_res0001.dbf',
'+DATAC1/JSPARAMS/datafile2/idx_res0001.dbf',
'+DATAC1/JSPARAMS/datafile2/tbs_data01.dbf',
'+DATAC1/JSPARAMS/datafile2/tbs_pd01.dbf',
'+DATAC1/JSPARAMS/datafile2/tbs_idxpd01.dbf',
'+DATAC1/JSPARAMS/datafile2/tbs_sd01.dbf',
'+DATAC1/JSPARAMS/datafile2/tbs_idxsd01.dbf',
'+DATAC1/JSPARAMS/datafile2/tbs_tsvbinfo10m01.dbf',
'+DATAC1/JSPARAMS/datafile2/tbs_tsvbinfoidx1m.dbf',
'+DATAC1/JSPARAMS/datafile2/tbs_bd01.dbf',
'+DATAC1/JSPARAMS/datafile2/tbs_idxbd01.dbf'
CHARACTER SET ZHS16GBK
;
啟動報錯：
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [kcbzib_kcrsds_1], [], [], [], [],
[], [], [], [], [], [], []
Process ID: 382237
Session ID: 3574 Serial number: 41890
推進 SCN
vi /tmp/pfile
event="21307096 trace name context forever, level 3"
使用 +DATAC1/JSPARAMS/ONLINELOG/group_2.637.1094028699 等 redo 進行例項恢復
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 15950365669073 generated at 02/07/2022 19:13:53 needed for
thread 1
ORA-00289: suggestion :
/oracle/app/oracle/product/19.3.0/db/dbs/arch1_1_1096053222.dbf
ORA-00280: change 15950365669073 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
+DATAC1/JSPARAMS/ONLINELOG/group_2.637.1094028699
ORA-00342: archived log does not have expected resetlogs SCN 15950365669070
ORA-00334: archived log: '+DATAC1/JSPARAMS/ONLINELOG/group_2.637.1094028699'
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '+DATAC1/JSPARAMS/datafile2/system.dbf'
再次嘗試開啟資料庫
SQL> alter database open resetlogs upgrade;
alter database open resetlogs upgrade
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [4193], [4105], [4108], [], [], [],
[], [], [], [], [], []
Process ID: 5195
Session ID: 5075 Serial number: 25071
錯誤編碼變了，說明scn推進成功了，這是undo的一個問題，透過mos查詢解決方法
SOLUTION
Best practice to create a new undo tablespace.
This method includes segment check.
1. Create pfile from spfile to edit
SQL> Create pfile='/tmp/initsid.ora' from spfile;
2. Shutdown the instance
3. set the following parameters in the pfile /tmp/initsid.ora
undo_management = manual
event = '10513 trace name context forever, level 2'
4. SQL>>startup restrict pfile='/tmp/initsid.ora'
5. SQL>select tablespace_name, status, segment_name from dba_rollback_segs where status != 'OFFLINE';
This is critical - we are looking for all undo segments to be offline - System will always be online.
If any are 'PARTLY AVAILABLE' or 'NEEDS RECOVERY' - Please open an issue with Oracle Support or update the current SR. There are many options from this moment and Oracle Support Analyst can offer different solutions for the bad undo segments.
If all offline then continue to the next step
6. Create new undo tablespace - example
SQL>create undo tablespace <new undo tablespace> datafile <datafile> size 2000M;
7. Drop old undo tablespace
SQL>drop tablespace <old undo tablespace> including contents and datafiles;
8. SQL>shutdown immediate;
9 SQL>startup nomount; --> Using your Original spfile
10. Modify the spfile with the new undo tablespace name
SQL> Alter system set undo_tablespace = '<new tablespace created in step 6>' scope=spfile;
11. SQL>shutdown immediate;
12. SQL>startup; --> Using spfile
解決問題過程：
SQL> startup restrict pfile='/tmp/inittmp.ora'
ORACLE instance started.
Total System Global Area 1.0737E+10 bytes
Fixed Size 12446792 bytes
Variable Size 3791650816 bytes
Database Buffers 6845104128 bytes
Redo Buffers 88215552 bytes
Database mounted.
Database opened.
SQL> select tablespace_name, status, segment_name from dba_rollback_segs where status != 'OFFLINE';
TABLESPACE_NAME STATUS SEGMENT_NAME
------------------------------ ---------------- ------------------------------
SYSTEM ONLINE SYSTEM
UNDOTBS1 PARTLY AVAILABLE _SYSSMU10_3568133130$
建立一個新的 undotbs 7
SQL> create undo tablespace undotbs7 datafile '+DATAC1/JSPARAMS/datafile2/undotbs7.dbf' size 2000M;
Tablespace created.
SQL> drop tablespace UNDOTBS1 including contents and datafiles;
drop tablespace UNDOTBS1 including contents and datafiles
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU10_3568133130$' found, terminate
dropping tablespace
發現無法刪除undotbs 1 ，遮蔽掉
Vi /tmp/inittmp.ora
*._corrupted_rollback_segments=(_SYSSMU10_3568133130$)
刪除 UNDOTBS1
SQL> drop tablespace UNDOTBS1 including contents and datafiles;
Tablespace dropped.
修改 pfile
jsparams2.__data_transfer_cache_size=0
jsparams1.__data_transfer_cache_size=0
jsparams2.__db_cache_size=6643777536
jsparams1.__db_cache_size=6710886400
jsparams2.__inmemory_ext_roarea=0
jsparams1.__inmemory_ext_roarea=0
jsparams2.__inmemory_ext_rwarea=0
jsparams1.__inmemory_ext_rwarea=0
jsparams2.__java_pool_size=134217728
jsparams1.__java_pool_size=167772160
jsparams2.__large_pool_size=167772160
jsparams1.__large_pool_size=167772160
jsparams1.__oracle_base='/oracle/app/oracle'#ORACLE_BASE set from environment
jsparams2.__oracle_base='/oracle/app/oracle'#ORACLE_BASE set from environment
jsparams2.__pga_aggregate_target=10737418240
jsparams1.__pga_aggregate_target=10737418240
jsparams2.__sga_target=10737418240
jsparams1.__sga_target=10737418240
jsparams2.__shared_io_pool_size=134217728
jsparams1.__shared_io_pool_size=134217728
jsparams2.__shared_pool_size=3489660928
jsparams1.__shared_pool_size=3388997632
jsparams2.__streams_pool_size=67108864
jsparams1.__streams_pool_size=67108864
jsparams2.__unified_pga_pool_size=0
jsparams1.__unified_pga_pool_size=0
*._allow_error_simulation=TRUE
*._allow_resetlogs_corruption=TRUE
*._assm_segment_repair_bg=FALSE
*._parallel_adaptive_max_users=2
*.audit_sys_operations=TRUE
*.audit_trail='db'
*.undo_management = manual
*.cluster_database=FALSE
*.compatible='19.0.0'
*.control_files='+DATAC1/JSPARAMS/CONTROLFILE/current.621.1093981287'
*.db_block_checking='false'
*.db_block_checksum='full'
*.db_block_size=8192
*.db_create_file_dest='+DATAC1'
*.db_create_online_log_dest_1='+DATAC1'
*.db_files=2000
*.db_lost_write_protect='typical'
*.db_name='jsparams'
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=jsparamsXDB)'
*.event='21307096 trace name context forever, level 3'
*.event = '10513 trace name context forever, level 2'
*._corrupted_rollback_segments=(_SYSSMU10_3568133130$)
*.fast_start_mttr_target=300
*.filesystemio_options='setall'
*.global_names=FALSE
family:dw_helper.instance_mode='read-only'
jsparams2.instance_number=2
jsparams1.instance_number=1
*.local_listener='-oraagent-dummy-'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=1000
*.os_authent_prefix=''
*.parallel_execution_message_size=16384
*.parallel_threads_per_cpu=1
*.pga_aggregate_target=10g
*.processes=5000
*.recyclebin='on'
*.remote_login_passwordfile='exclusive'
*.sga_target=10g
jsparams2.thread=2
jsparams1.thread=1
*.undo_tablespace='UNDOTBS1'
jsparams2.undo_tablespace='UNDOTBS2'
jsparams1.undo_tablespace='UNDOTBS1'
~
為：
jsparams2.__data_transfer_cache_size=0
jsparams1.__data_transfer_cache_size=0
jsparams2.__db_cache_size=6643777536
jsparams1.__db_cache_size=6710886400
jsparams2.__inmemory_ext_roarea=0
jsparams1.__inmemory_ext_roarea=0
jsparams2.__inmemory_ext_rwarea=0
jsparams1.__inmemory_ext_rwarea=0
jsparams2.__java_pool_size=134217728
jsparams1.__java_pool_size=167772160
jsparams2.__large_pool_size=167772160
jsparams1.__large_pool_size=167772160
jsparams1.__oracle_base='/oracle/app/oracle'#ORACLE_BASE set from environment
jsparams2.__oracle_base='/oracle/app/oracle'#ORACLE_BASE set from environment
jsparams2.__pga_aggregate_target=10737418240
jsparams1.__pga_aggregate_target=10737418240
jsparams2.__sga_target=10737418240
jsparams1.__sga_target=10737418240
jsparams2.__shared_io_pool_size=134217728
jsparams1.__shared_io_pool_size=134217728
jsparams2.__shared_pool_size=3489660928
jsparams1.__shared_pool_size=3388997632
jsparams2.__streams_pool_size=67108864
jsparams1.__streams_pool_size=67108864
jsparams2.__unified_pga_pool_size=0
jsparams1.__unified_pga_pool_size=0
*._allow_error_simulation=TRUE
*._allow_resetlogs_corruption=TRUE
*._assm_segment_repair_bg=FALSE
*._parallel_adaptive_max_users=2
*.audit_sys_operations=TRUE
*.audit_trail='db'
*.undo_management = manual
*.cluster_database=FALSE
*.compatible='19.0.0'
*.control_files='+DATAC1/JSPARAMS/CONTROLFILE/current.621.1093981287'
*.db_block_checking='false'
*.db_block_checksum='full'
*.db_block_size=8192
*.db_create_file_dest='+DATAC1'
*.db_create_online_log_dest_1='+DATAC1'
*.db_files=2000
*.db_lost_write_protect='typical'
*.db_name='jsparams'
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=jsparamsXDB)'
*.event='21307096 trace name context forever, level 3'
*.event = '10513 trace name context forever, level 2'
*._corrupted_rollback_segments=(_SYSSMU10_3568133130$)
*.fast_start_mttr_target=300
*.filesystemio_options='setall'
*.global_names=FALSE
family:dw_helper.instance_mode='read-only'
jsparams2.instance_number=2
jsparams1.instance_number=1
*.local_listener='-oraagent-dummy-'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=1000
*.os_authent_prefix=''
*.parallel_execution_message_size=16384
*.parallel_threads_per_cpu=1
*.pga_aggregate_target=10g
*.processes=5000
*.recyclebin='on'
*.remote_login_passwordfile='exclusive'
*.sga_target=10g
jsparams2.thread=2
jsparams1.thread=1
*.undo_tablespace='UNDOTBS7'
#jsparams2.undo_tablespace='UNDOTBS2'
#jsparams1.undo_tablespace='UNDOTBS1'
~
startup pfile='/tmp/inittmp.ora' mount;
Alter database open readonly
讓客戶dmp匯出即可
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23825935/viewspace-2922973/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- raid5癱瘓導致資料庫損壞的恢復過程AI資料庫
- 成功恢復某公司伺服器故障導致的資料庫損壞伺服器資料庫
- 伺服器Oracle資料庫損壞修復伺服器Oracle資料庫
- 電腦進水導致硬碟損壞資料恢復硬碟資料恢復
- 總結導致oracle資料庫主機CPU sys%高的一些原因Oracle資料庫
- 快取與資料庫一致性快取資料庫
- 【LINUX】Oracle資料庫 linux磁碟頭資料損壞修復LinuxOracle資料庫
- 分散式快取--快取與資料庫一致性方案分散式快取資料庫
- 快取與資料庫的一致性快取資料庫
- 快取淘汰、快取穿透、快取擊穿、快取雪崩、資料庫快取雙寫一致性快取穿透資料庫
- Redis快取穿透、擊穿、雪崩，資料庫與快取一致性Redis快取穿透資料庫
- 快取與資料庫不一致，咋辦？快取資料庫
- 資料庫與快取雙寫一致性資料庫快取
- 【北亞資料恢復】誤操作分割槽損壞導致SqlServer資料庫資料丟失的資料恢復資料恢復SQLServer資料庫
- Oracle資料庫不同損壞級別的恢復詳解Oracle資料庫
- 資料庫與快取資料一致性解決方案資料庫快取
- 分散式快取--快取與資料庫強一致場景下的方案分散式快取資料庫
- 快取與資料庫的雙寫一致性快取資料庫
- ORACLE DSG資料同步軟體程式導致資料庫無法正常關閉Oracle資料庫
- 如何保證快取和資料庫的一致性？快取資料庫
- 快取與資料庫一致性問題深度剖析快取資料庫
- 快取與資料庫雙寫一致性 深度分析快取資料庫
- 資料庫和快取的一致性如何保證資料庫快取
- [Redis] 02-快取和資料庫資料一致性問題Redis快取資料庫
- 【vSAN資料恢復案例】異常斷電導致vSAN底層資料損壞的資料恢復資料恢復
- PostgreSQL資料庫toast表損壞解決SQL資料庫AST
- SQLite資料庫損壞及其修復探究SQLite資料庫
- oracle資料庫卡頓Oracle資料庫
- 【北亞資料恢復】異常斷電導致linux伺服器無法啟動，資料庫損壞的資料恢復資料恢復Linux伺服器資料庫
- 從實戰出發，聊聊快取資料庫一致性快取資料庫
- 趣說 ｜ 資料庫和快取如何保證一致性？資料庫快取
- 一次ORACLE資料庫undo壞塊處理Oracle資料庫
- Web快取知多少(快取機制和資料儲存)Web快取
- 記一次Oracle RAC for aix 儲存雙控鎖盤導致ASM控制檔案損壞恢復OracleAIASM
- 快取與資料庫雙寫一致性幾種策略分析快取資料庫
- 資料庫和快取雙寫一致性方案總結分析資料庫快取
- 如何解決資料庫與快取的一致性問題資料庫快取
- 如何保證快取與資料庫的雙寫一致性？快取資料庫