数据库版本：
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
https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=109987664404089&parent=EXTERNAL_SEARCH&sourceId=PROBLEM&id=1428786.1&_afrWindowMode=0&_adf.ctrl-state=13ps2528tu_279
解决问题过程：
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导出即可
