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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- UNDO表空間損壞導致資料庫無法OPEN資料庫
- 資料庫檔案壞塊損壞導致開啟時報錯的恢復方法資料庫
- 快取穿透導致資料庫效能不穩定快取穿透資料庫
- Oracle升級導致ocr檔案損壞一例Oracle
- Oracle資料庫塊的物理損壞與邏輯損壞Oracle資料庫
- 成功恢復某公司伺服器故障導致的資料庫損壞伺服器資料庫
- 快取與資料庫一致性快取資料庫
- raid5癱瘓導致資料庫損壞的恢復過程AI資料庫
- 隱含引數_DISABLE_LOGGING導致資料庫的損壞資料庫
- 磁碟損壞導致資料檔案丟失的恢復
- 分散式快取--快取與資料庫一致性方案分散式快取資料庫
- 聊聊資料庫和快取同步機制資料庫快取
- 總結導致oracle資料庫主機CPU sys%高的一些原因Oracle資料庫
- 快取與資料庫的一致性快取資料庫
- 快取淘汰、快取穿透、快取擊穿、快取雪崩、資料庫快取雙寫一致性快取穿透資料庫
- 資料庫損壞解決：資料庫已損壞，無法分配空間資料庫
- Oracle資料庫UNDO損壞後的恢復Oracle資料庫
- Redis快取穿透、擊穿、雪崩，資料庫與快取一致性Redis快取穿透資料庫
- 快取與資料庫不一致，咋辦？快取資料庫
- 資料庫與快取雙寫一致性資料庫快取
- 【北亞資料恢復】誤操作分割槽損壞導致SqlServer資料庫資料丟失的資料恢復資料恢復SQLServer資料庫
- Oracle資料庫恢復：歸檔日誌損壞案例一則Oracle資料庫
- oracle exadata資料庫一體機初識Oracle資料庫
- 資料庫與快取資料一致性解決方案資料庫快取
- 分散式快取--快取與資料庫強一致場景下的方案分散式快取資料庫
- archivelog滿了導致資料庫怠機的solutionHive資料庫
- 快取與資料庫的雙寫一致性快取資料庫
- ORACLE資料庫壞塊的處理 (一次壞快處理過程)Oracle資料庫
- OEL 11.2.0.2 RAC 資料庫停電導致has程式無法啟動OLR檔案損壞資料庫
- 一條sql語句導致的資料庫當機問題及分析SQL資料庫
- 一條sql語句“導致”的資料庫當機問題及分析SQL資料庫
- 【LINUX】Oracle資料庫 linux磁碟頭資料損壞修復LinuxOracle資料庫
- [ORACLE] 系統故障資料庫恢復--資料檔案無損壞Oracle資料庫
- Oracle 資料庫不一致導致異常的恢復Oracle資料庫
- flashback_area 區域溢位導致資料庫當機資料庫
- 快取與資料庫雙寫一致性 深度分析快取資料庫
- 快取與資料庫一致性問題深度剖析快取資料庫
- 資料庫和快取的一致性如何保證資料庫快取