断电引起的oracle数据库异常恢复

发表于 2023 年 3 月 19 日 由 惜分飞

服务器断电，数据库mount失败

SQL> startup mount pfile='d:/pfile.txt'
ORACLE 例程已经启动。

Total System Global Area 1185853440 bytes
Fixed Size                  2175168 bytes
Variable Size             335548224 bytes
Database Buffers          838860800 bytes
Redo Buffers                9269248 bytes
ORA-00205: ?????????, ??????, ???????

alert日志报错信息

Sun Mar 19 20:18:29 2023
ALTER DATABASE   MOUNT
Errors in file d:\app\xifenfei\diag\rdbms\orcl\orcl\trace\orcl_ckpt_15064.trc  (incident=3697):
ORA-00227: ????????????: (? 1, # ? 1)
ORA-00202: ????: ''D:\BAIDUNETDISKDOWNLOAD\ORCL\CONTROL01.CTL''
Incident details in: d:\app\xifenfei\diag\rdbms\orcl\orcl\incident\incdir_3697\orcl_ckpt_15064_i3697.trc
Sun Mar 19 20:18:30 2023
Errors in file d:\app\xifenfei\diag\rdbms\orcl\orcl\trace\orcl_m000_18084.trc  (incident=3761):
ORA-00227: ????????????: (? 1, # ? 1)
ORA-00202: ????: ''D:\BAIDUNETDISKDOWNLOAD\ORCL\CONTROL01.CTL''
Incident details in: d:\app\xifenfei\diag\rdbms\orcl\orcl\incident\incdir_3761\orcl_m000_18084_i3761.trc
Sun Mar 19 20:18:29 2023
MMNL started with pid=16, OS id=9404 
ORA-00227: ????????????: (? 1, # ? 1)
ORA-00202: ????: ''D:\BAIDUNETDISKDOWNLOAD\ORCL\CONTROL01.CTL''
Checker run found 1 new persistent data failures
Trace dumping is performing id=[cdmp_20230319201831]
ORA-205 signalled during: ALTER DATABASE   MOUNT...

错误比较明显由于控制文件的block损坏导致数据库在mount的时候提示ORA-00205,重试重建ctl

SQL> CREATE CONTROLFILE REUSE DATABASE "orcl" NORESETLOGS  NOARCHIVELOG
  2      MAXLOGFILES 50
  3      MAXLOGMEMBERS 5
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 226
  7  LOGFILE
  8    GROUP 1 'D:\BaiduNetdiskDownload\orcl/redo01.log'  SIZE 50M,
  9    GROUP 2 'D:\BaiduNetdiskDownload\orcl/redo02.log'  SIZE 50M,
 10    GROUP 3 'D:\BaiduNetdiskDownload\orcl/redo03.log'  SIZE 50M
 11  DATAFILE
 12  'D:\BaiduNetdiskDownload\orcl\EXAMPLE01.DBF',
 13  'D:\BaiduNetdiskDownload\orcl\GHZN.DBF',
 14  'D:\BaiduNetdiskDownload\orcl\GHZN2.DBF',
 15  'D:\BaiduNetdiskDownload\orcl\SYSAUX01.DBF',
 16  'D:\BaiduNetdiskDownload\orcl\SYSTEM01.DBF',
 17  'D:\BaiduNetdiskDownload\orcl\UNDOTBS01.DBF',
 18  'D:\BaiduNetdiskDownload\orcl\USERS01.DBF'
 19  CHARACTER SET ZHS16GBK
 20  ;
CREATE CONTROLFILE REUSE DATABASE "orcl" NORESETLOGS  NOARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01565: error in identifying file
'D:\BaiduNetdiskDownload\orcl\UNDOTBS01.DBF'
ORA-27041: unable to open file
OSD-04001: 逻辑块大小无效 (OS 2613931212)

由于undo文件异常(大小不是block size的整数倍）,因此报OSD-04001: 逻辑块大小无效错误.对undo文件及其其他文件进行检查发现数据库文件有不少坏块,而且undo文件的文件头损坏
20230319202417

通过抛弃undo文件并进行一些处理,重建ctl成功,并且recover 数据库成功,顺利open数据库

SQL> CREATE CONTROLFILE REUSE DATABASE "orcl" NORESETLOGS  NOARCHIVELOG
  2      MAXLOGFILES 50
  3      MAXLOGMEMBERS 5
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 226
  7  LOGFILE
  8    GROUP 1 'D:\BaiduNetdiskDownload\orcl/redo01.log'  SIZE 50M,
  9    GROUP 2 'D:\BaiduNetdiskDownload\orcl/redo02.log'  SIZE 50M,
 10    GROUP 3 'D:\BaiduNetdiskDownload\orcl/redo03.log'  SIZE 50M
 11  DATAFILE
 12  'D:\BaiduNetdiskDownload\orcl\EXAMPLE01.DBF',
 13  'D:\BaiduNetdiskDownload\orcl\GHZN.DBF',
 14  'D:\BaiduNetdiskDownload\orcl\GHZN2.DBF',
 15  'D:\BaiduNetdiskDownload\orcl\SYSAUX01.DBF',
 16  'D:\BaiduNetdiskDownload\orcl\SYSTEM01.DBF',
 17  'D:\BaiduNetdiskDownload\orcl\USERS01.DBF'
 18  CHARACTER SET ZHS16GBK
 19  ;

Control file created.

SQL> recover database;
Media recovery complete.
SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount pfile='d:/pfile.txt'
ORACLE instance started.

Total System Global Area 1185853440 bytes
Fixed Size                  2175168 bytes
Variable Size             335548224 bytes
Database Buffers          838860800 bytes
Redo Buffers                9269248 bytes
Database mounted.
SQL> alter database open;

Database altered.

然后使用逻辑方式导出数据,运气不错业务文件没有任何坏块,system坏块在aud$上,无任何业务数据丢失.

发表在 Oracle备份恢复 | 标签为 , , | 评论关闭

等保修改oracle SYS用户名要求的请注意—ORA-00600 kokasgi1

发表于 2023 年 3 月 19 日 由 惜分飞

在2019年处理过第一起ORA-00600 kokasgi1 故障到现在,已经过去了近4年,今天又有客户依旧因为修改sys重启库遇到该问题
ora-600-kokasgi1

通过分析确认客户那边在等保的时候要求修改oracle的SYS用户
20230319001510
然后重启数据库，数据库就开始报ORA-600 kokasgi1错误.
再次呼吁:
1. oracle的sys用户名不能修改,这个东西是写在oracle代码里面的，启动的时候会去读取
2.如果已经修改了sys用户名的,请在数据库重启之前一定修改回来

发表在 Oracle备份恢复 | 标签为 , , , | 评论关闭

ORA-07445 opiaba—绑定变量超过65535导致实例crash

发表于 2023 年 3 月 15 日 由 惜分飞

数据库异常报ORA-07445 opiaba，ORA-00600 17147错,导致实例crash

Wed Mar 15 09:48:06 2023
Exception [type: SIGSEGV, SI_KERNEL(general_protection)] [ADDR:0x0] [PC:0x187B953, opiaba()+639] [flags: 0x0, count: 1]
Errors in file /u01/app/oracle/diag/rdbms/xifenfei/xifenfei3/trace/xifenfei3_ora_169909.trc  (incident=446531):
ORA-07445: 出现异常错误: 核心转储 [opiaba()+639] [SIGSEGV] [ADDR:0x0] [PC:0x187B953] [SI_KERNEL(general_protection)] []
Incident details in: /u01/app/oracle/diag/rdbms/xifenfei/xifenfei3/incident/incdir_446531/xifenfei3_ora_169909_i446531.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Wed Mar 15 09:48:07 2023
Dumping diagnostic data in directory=[cdmp_20230315094807], requested by (instance=3, osid=169909), summary=[incident=446531].
Wed Mar 15 09:48:08 2023
Sweep [inc][446531]: completed
Sweep [inc2][446531]: completed
Wed Mar 15 09:48:33 2023
Errors in file /u01/app/oracle/diag/rdbms/xifenfei/xifenfei3/trace/xifenfei3_pmon_161557.trc  (incident=440035):
ORA-00600: internal error code, arguments: [17147], [0x4AFC25D0C8], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/xifenfei/xifenfei3/incident/incdir_440035/xifenfei3_pmon_161557_i440035.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Wed Mar 15 09:48:33 2023
Dumping diagnostic data in directory=[cdmp_20230315094833], requested by (instance=3, osid=161557 (PMON)), summary=[incident=440035].
Errors in file /u01/app/oracle/diag/rdbms/xifenfei/xifenfei3/trace/xifenfei3_pmon_161557.trc:
ORA-00600: internal error code, arguments: [17147], [0x4AFC25D0C8], [], [], [], [], [], [], [], [], [], []
PMON (ospid: 161557): terminating the instance due to error 472
Wed Mar 15 09:48:34 2023
opiodr aborting process unknown ospid (170089) as a result of ORA-1092

对应的trace文件信息

*** 2023-03-15 09:09:34.862
*** SESSION ID:(1858.63187) 2023-03-15 09:09:34.862
*** CLIENT ID:() 2023-03-15 09:09:34.862
*** SERVICE NAME:(xifenfei) 2023-03-15 09:09:34.862
*** MODULE NAME:(JDBC Thin Client) 2023-03-15 09:09:34.862
*** ACTION NAME:() 2023-03-15 09:09:34.862
 
Dump continued from file: /u01/app/oracle/diag/rdbms/xifenfei/xifenfei3/trace/xifenfei3_ora_116886.trc
ORA-07445: 出现异常错误: 核心转储 [opiaba()+639] [SIGSEGV] [ADDR:0x0] [PC:0x187B953] [SI_KERNEL(general_protection)] []

========= Dump for incident 326049 (ORA 7445 [opiaba()+639]) ========
----- Beginning of Customized Incident Dump(s) -----
Exception [type: SIGSEGV, SI_KERNEL(general_protection)] [ADDR:0x0] [PC:0x187B953, opiaba()+639] [flags: 0x0, count: 1]
Registers:
%rax: 0x00000046fa212588 %rbx: 0x00000046762ee798 %rcx: 0x0000000000003200
%rdx: 0x0000000000000000 %rdi: 0x0000000000007340 %rsi: 0x0000000000007340
%rsp: 0x00007fffffff41d0 %rbp: 0x00007fffffff4240  %r8: 0x00000044a8599f00
 %r9: 0x0000000000000099 %r10: 0x0000000000000b34 %r11: 0x00000046fa211720
%r12: 0x00000046fa5c7488 %r13: 0x0000000000000000 %r14: 0x0200000046fa2125
%r15: 0x0000000000000005 %rip: 0x000000000187b953 %efl: 0x0000000000010202
  opiaba()+625 (0x187b945) jmp 0x187b982
  opiaba()+627 (0x187b947) movzbl 0x2c(%rbx),%r15d
  opiaba()+632 (0x187b94c) mov %r14,-0x60(%rbp)
  opiaba()+636 (0x187b950) mov %rax,%r14
> opiaba()+639 (0x187b953) movswq 0xa(%r14),%rcx
  opiaba()+644 (0x187b958) cmp %ecx,%r15d
  opiaba()+647 (0x187b95b) jne 0x187b976
  opiaba()+649 (0x187b95d) mov 0x18(%rbx),%rdi
  opiaba()+653 (0x187b961) lea 0xc(%r14),%rsi

*** 2023-03-15 09:09:34.863
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x3, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=5cvg8vqsbr8j6) -----
BEGIN  
            UPDATE
             t_xifenfei 
             SET valid_param=:1 , comm_addr1 = :2 ,
                password=:3 , customer_type = :4 ,
                comm_no=:5 , date_grade_no = :6 ,
                date_grade_flag=:7 , curve_config_no = :8 ,
                curve_config_flag=:9 , baudrate = :10 ,
                meas_order=:11 , meter_no = :12 ,
                kind_no=:13 , tariff_count = :14 ,
                meter_digits = :15 , import_user=:16 ,
                readmeter_flag = :17 ,status_code=:18  
            WHERE meter_id = :19 
         ; 
…………
         ; 
            UPDATE
             t_xifenfei 
             SET valid_param=:94982 , comm_addr1 = :94983 ,
                password=:94984 , customer_type = :94985 ,
                comm_no=:94986 , date_grade_no = :94987 ,
                date_grade_flag=:94988 , curve_config_no = :94989 ,
                curve_config_flag=:94990 , baudrate = :94991 ,
                meas_order=:94992 , meter_no = :94993 ,
                kind_no=:94994 , tariff_count = :94995 ,
                meter_digits = :94996 , import_user=:94997 ,
                readmeter_flag = :94998 ,status_code=:94999  
            WHERE meter_id = :95000 
         ;END;

写了一个begin end,里面对于同一个update语句进行多次绑定变量实现批量提交功能,绑定变量的数量达到95000个,远超oracle官方限制的65535的极限,触发类似Bug 12578873 ORA-7445 [opiaba] when using more than 65535 bind variables
20230315132517

处理方法减少绑定变量数量,不能超过65535个

发表在 ORA-xxxxx | 标签为 , | 评论关闭