asm的别名机制带来了一些便利的同时有些时候也引入了一些麻烦,比如某些工具无法很好的识别别名,闲着写了sql直接获取别名和实际数据文件对应关系
直接查询数据文件
通过asmcmd中的ls命令查看
ASMCMD> ls -l +MGMT/ora18c/test01.dbf Type Redund Striped Time Sys Name DATAFILE UNPROT COARSE SEP 06 00:00:00 N test01.dbf => +MGMT/ORA18C/DATAFILE/TEST16K.274.1016183943
但是如果太多,这样一个个替换效率太低,通过sql语句实现
获取实际数据文件
通过sql语句快速实现把数据文件路径中的别名转换为实际存储路径(omf方式存储)
在oracle dataguard环境中,当主库修改密码之后,备库不会立即生效,需要flush shared pool之后才会生效
主库创建用户并尝试登录
[oracle@primary ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Tue Sep 3 17:24:12 2019 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> select open_mode,database_role from v$database; OPEN_MODE DATABASE_ROLE -------------------- ---------------- READ WRITE PRIMARY SQL> create user xff identified by oracle; User created. SQL> grant dba to xff; Grant succeeded. SQL> conn xff/oracle Connected.
备库登录信息
[oracle@standby ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Tue Sep 3 17:17:47 2019 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select database_role,open_mode from v$database; DATABASE_ROLE OPEN_MODE ---------------- -------------------- PHYSICAL STANDBY READ ONLY WITH APPLY [oracle@standby ~]$ ss SQL*Plus: Release 11.2.0.4.0 Production on Tue Sep 3 17:25:58 2019 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> conn xff/oracle Connected.
主库修改密码
[oracle@primary ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Tue Sep 3 17:17:47 2019 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> alter user xff identified by xff; User altered. SQL> conn xff/xff Connected.
备库尝试登录
SQL> conn xff/xff; ERROR: ORA-01017: invalid username/password; logon denied Warning: You are no longer connected to ORACLE. SQL> conn xff/oracle Connected.
备库原密码可以登录修改之后的密码无法登录
备库刷新shared pool,新密码登录成功
SQL> conn / as sysdba Connected. SQL> alter system flush shared_pool; System altered. SQL> conn xff/oracle ERROR: ORA-01017: invalid username/password; logon denied Warning: You are no longer connected to ORACLE. SQL> conn xff/xff Connected.
有一个客户恢复请求,6个节点11.2.0.3 RAC,非归档模式,数据量近200T
由于存储掉电导致数据库6个节点全部宕机,恢复硬件之后,数据库无法正常启动,报错如下:
SQL> recover database; ORA-00279: change 318472018583 generated at 05/04/2019 17:58:05 needed for thread 4 ORA-00289: suggestion : /u01/app/oracle/product/11.2.0/db_1/dbs/arch4_322810_870181839.dbf ORA-00280: change 318472018583 for thread 4 is in sequence #322810 Wed Aug 28 11:19:55 2019 ALTER DATABASE RECOVER DATABAE Media Recovery Start Serial Media Recovery started Recovery of Online Redo Log: Thread 1 Group 14 Seq 552 Reading mem 0 Mem# 0: +REDO/xff/log2.ora Recovery of Online Redo Log: Thread 2 Group 15 Seq 126 Reading mem 0 Mem# 0: +REDO/xff/log3.ora Recovery of Online Redo Log: Thread 3 Group 18 Seq 122 Reading mem 0 Mem# 0: +REDO/xff/log6.ora ORA-279 signalled during: ALTER DATABASE RECOVER database ... Wed Aug 28 11:21:31 2019 ALTER DATABASE RECOVER CANCEL Media Recovery Canceled Completed: ALTER DATABASE RECOVER CANCEL
数据库恢复需要thread 4 sequence #322810,查询redo信息
redo已经被覆盖,数据库无法通过正常途径恢复实现数据库open,尝试屏蔽一致性强制拉库操作后
Wed Aug 28 12:40:15 2019 SMON: enabling tx recovery Database Characterset is ZHS16GBK Errors in file /u01/app/oracle/diag/rdbms/xff/xff1/trace/xff1_smon_51338.trc (incident=244209): ORA-00600: internal error code, arguments: [4137], [44.47.613406], [0], [0], [], [], [], [], [], [], [], [] Incident details in: /u01/app/oracle/diag/rdbms/xff/xff1/incident/incdir_244209/xff1_smon_51338_i244209.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. No Resource Manager plan active replication_dependency_tracking turned off (no async multimaster replication found) Wed Aug 28 12:40:16 2019 ORACLE Instance xff1 (pid = 26) - Error 600 encountered while recovering transaction (44, 47). Errors in file /u01/app/oracle/diag/rdbms/xff/xff1/trace/xff1_smon_51338.trc: ORA-00600: internal error code, arguments: [4137], [44.47.613406], [0], [0], [], [], [], [], [], [], [], [] Wed Aug 28 12:40:20 2019 Exception[type: SIGSEGV,Address not mapped to object][ADDR:0x5122000000C8][PC:0xE1B4D3,ktugru()+87][flags:0x0,count:1] Errors in file /u01/app/oracle/diag/rdbms/xff/xff1/trace/xff1_p086_54066.trc (incident=245017): ORA-07445:exception encountered:core dump [ktugru()+87][SIGSEGV][ADDR:0x5122000000C8][Address not mapped to object] Incident details in: /u01/app/oracle/diag/rdbms/xff/xff1/incident/incdir_245017/xff1_p086_54066_i245017.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Wed Aug 28 12:40:20 2019 Errors in file /u01/app/oracle/diag/rdbms/xff/xff1/trace/xff1_p000_53873.trc (incident=244305): ORA-00600: internal error code, arguments: [4198], [], [], [], [], [], [], [], [], [], [], [] Incident details in: /u01/app/oracle/diag/rdbms/xff/xff1/incident/incdir_244305/xff1_p000_53873_i244305.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details.
提示undo异常,屏蔽回滚段之后,数据库正常打开没有任何报错信息
Wed Aug 28 12:57:15 2019 SMON: enabling cache recovery Instance recovery: looking for dead threads Instance recovery: lock domain invalid but no dead threads [57676] Successfully onlined Undo Tablespace 22. Undo initialization finished serial:0 start:2386111306 end:2386112316 diff:1010 (10 seconds) Verifying file header compatibility for 11g tablespace encryption.. Verifying 11g file header compatibility for tablespace encryption completed SMON: enabling tx recovery Database Characterset is ZHS16GBK Wed Aug 28 12:57:17 2019 minact-scn: Inst 1 is now the master inc#:2 mmon proc-id:57624 status:0x7 minact-scn status: grec-scn:0x0000.00000000 gmin-scn:0x0000.00000000 gcalc-scn:0x0000.00000000 No Resource Manager plan active Starting background process GTX0 Wed Aug 28 12:57:18 2019 GTX0 started with pid=45, OS id=57777 Starting background process RCBG Wed Aug 28 12:57:18 2019 RCBG started with pid=46, OS id=57779 replication_dependency_tracking turned off (no async multimaster replication found) Starting background process QMNC Wed Aug 28 12:57:19 2019 QMNC started with pid=47, OS id=57788 Completed: ALTER DATABASE OPEN
后续涉及创建新undo,删除老undo并处理一些类似,基本上恢复正常
在一个12.1.0.2的库的awr中发现大量47r1y8yn34jmj语句的解析
对应的完整sql为:select default$ from col$ where rowid=:1,按道理说正常的库不应该出现大量该类sql的解析,查询mos发现相关Bug 20907061 : HIGH # OF EXECUTIONS FOR RECURSIVE CALL ON COL$
数据库版本
SQL> select * from v$version; BANNER CON_ID -------------------------------------------------------------------------------- ---------- Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0 PL/SQL Release 12.2.0.1.0 - Production 0 CORE 12.2.0.1.0 Production 0 TNS for Linux: Version 12.2.0.1.0 - Production 0 NLSRTL Version 12.2.0.1.0 - Production 0
alert 日志报错
2019-08-14T11:30:15.112151+08:00 WARNING: too many parse errors, count=546 SQL hash=0x750004bb PARSE ERROR: ospid=11550, error=933 for statement: 2019-08-14T11:30:15.112224+08:00 DELETE FROM wri$_adv_sqlt_rtn_planWHERE task_id = :tid AND exec_name = :execution_name Additional information: hd=0x16a3e1db8 phd=0x1699bf628 flg=0x28 cisid=0 sid=0 ciuid=0 uid=0 2019-08-14T11:30:15.114628+08:00 ----- PL/SQL Call Stack ----- object line object handle number name 0xd0ba9890 259 type body SYS.WRI$_ADV_SQLTUNE.SUB_DELETE_EXECUTION 0x870ac548 2134 package body SYS.PRVT_ADVISOR.COMMON_DELETE_TASK 0x870ac548 7342 package body SYS.PRVT_ADVISOR.DELETE_EXPIRED_TASKS 0xc91e5518 1 anonymous block WARNING: too many parse errors, count=646 SQL hash=0x750004bb PARSE ERROR: ospid=11550, error=933 for statement: 2019-08-14T11:30:15.298603+08:00 DELETE FROM wri$_adv_sqlt_rtn_planWHERE task_id = :tid AND exec_name = :execution_name Additional information: hd=0x16a3e1db8 phd=0x1699bf628 flg=0x28 cisid=0 sid=0 ciuid=0 uid=0 2019-08-14T11:30:15.298698+08:00 ----- PL/SQL Call Stack ----- object line object handle number name 0xd0ba9890 259 type body SYS.WRI$_ADV_SQLTUNE.SUB_DELETE_EXECUTION 0x870ac548 2134 package body SYS.PRVT_ADVISOR.COMMON_DELETE_TASK 0x870ac548 7342 package body SYS.PRVT_ADVISOR.DELETE_EXPIRED_TASKS 0xc91e5518 1 anonymous block
这里比较明显由于DELETE FROM wri$_adv_sqlt_rtn_planWHERE这条sql语法不对，导致无法解析因此报了ORA-00933错误,通过人工执行
SQL> exec SYS.PRVT_ADVISOR.DELETE_EXPIRED_TASKS(); PL/SQL procedure successfully completed.
后台alert日志重现该错误，证明该程序本身有问题，属于oracle bug范畴，查询mos发现相关Bug 26764561 : ORA-00933 IN SYS.WRI$_ADV_SQLTUNE.SUB_DELETE_EXECUTION
可以打上对应Patch 26764561: ORA-00933 IN SYS.WRI$_ADV_SQLTUNE.SUB_DELETE_EXECUTION
|
Patchsets |
|
|
12.1.0.2 (12.1.0.2.0 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
11.2.0.4 (11.2.0.4.0 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
11.2.0.3 (11.2.0.3.0 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
11.2.0.2 (11.2.0.2.0 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
11.1.0.7 (11.1.0.7.0 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
10.2.0.5 (10.2.0.5 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
A10.2.0.4 (10.2.0.4.0 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
B10.2.0.3 (10.2.0.3 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
10.2.0.2 (10.2.0.2 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
10.1.0.5 (10.1.0.5 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
10.1.0.4 (10.1.0.4 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
10.1.0.3 (10.1.0.3 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
9.2.0.8 (9.2.0.8 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
9.2.0.7 (9.2.0.7 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
9.2.0.6 (9.2.0.6 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
9.2.0.5 (ORACLE 9I DATABASE SERVER RELEASE 2 – PATCH SET 4 VERSION 9.2.0.5.0) |
|
|
9.2.0.4 (9.2.0.4 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
9.2.0.3 (9.2.0.3 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
9.2.0.2 (9.2.0.2 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
9.0.1.5 (9.0.1.5 PATCHSET) |
|
|
9.0.1.4 (9.0.1.4 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
9.0.1.3 (9.0.1.3. PATCH SET FOR ORACLE DATA SERVER) |
|
|
8.1.7.4 (8.1.7.4 PATCH SET FOR ORACLE DATA SERVER) |
|
|
8.1.7.3 (8.1.7.3 PATCH SET FOR ORACLE DATA SERVER) |
|
|
8.1.7.2 (8.1.7.2.1 PATCH SET FOR ORACLE DATA SERVER) |
|
|
19.0.0.0 |
|||
| Release | GI | DB | |
| JUL2019 | 29708769 (19.4.0.0.0) | 29834717 (19.4.0.0.0) | |
|
18.0.0.0 |
||||
| Release | DB Revision 1 | DB Revision 2 | GI Revision 1 | GI Revision 2 |
| JUL2019 | 29708235 (18.6.1.0.0) | 29708437(18.5.2.0.0) | 29708736 (18.6.1.0.0) | 29708805 (18.5.2.0.0) |
| APR2019 | 29230887 (18.5.1.0.0) | 29230809(18.4.2.0.0) | 29231062 (18.5.1.0.0) | 29230841 (18.4.2.0.0) |
| JAN2019 | 28822587 (18.4.1.0.0) | 28790643(18.3.2.0.0) | 28833172 (18.4.1.0.0) | 28833251 (18.3.1.0.0) |
| OCT2018 | 28507480 (18.3.1.0.0) | 28601267(18.2.2.0.0) | 28660077 (18.3.1.0.0) | 28702032 (18.2.2.0.0) |
| JUL2018 | 28346593 (18.2.1.0.0) | 28276290 (18.2.1.0.0) | ||
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
12.1.0.2 |
||||
| Description | PSU | GI PSU | Proactive Bundle Patch | Bundle Patch(Windows 32bit & 64bit) |
| JUL2019 (12.1.0.2.190716) | 29494060 | 29698592 | 29698629 | 29831650 |
| APR2019 (12.1.0.2.190416) | 29141015 | 29176115 | 29176139 | 29413116 |
| JAN2019 (12.1.0.2.190115) | 28729169 | 28813884 | 28833531 | 28810679 |
| NOV2018 (12.1.0.2.181130) | NA | NA | NA | 28810544 (64bit) |
| OCT2018 (12.1.0.2.181016) | 28259833 | 28349311 | 28349951 | 28563501 |
| JUL2018 (12.1.0.2.180717) | 27547329 | 27967747 | 27968010 | 27937907 |
| APR2018 (12.1.0.2.180417) | 27338041 | 27468957 | 27486326 | 27440294 |
| JAN2018 (12.1.0.2.180116) | 26925311 | 27010872 | 27010930 | 27162953 |
| OCT2017 (12.1.0.2.171017) | 26713565 | 26635815 | 26635880 | 26720785 |
| AUG2017(12.1.0.2.170814) | 26609783 | 26610308 | 26610322 | 26161726 |
| JUL2017 (12.1.0.2.170718) | 25755742 | 25901062 | 26022196 | 26161724 |
| APR2017 (12.1.0.2.170418) | 25171037 | 25434003 | 25433352 | 25632533 |
| JAN2017 (12.1.0.2.170117) | 24732082 | 24917825 | 24968615 | 25115951 |
| OCT2016 (12.1.0.2.161018) | 24006101 | 24412235 | 24448103 | 24591642 |
| JUL2016 (12.1.0.2.160719) | 23054246 | 23273629 | 23273686 | 23530387 |
| APR2016 (12.1.0.2.160419) | 22291127 | 22646084 | 22899531 | 22809813 |
| JAN2016 (12.1.0.2.160119) | 21948354 | 22191349 | 22243551 | 22310559 |
| OCT2015 | 21359755(12.1.0.2.5) | 21523234(12.1.0.2.5) | 21744410(12.1.0.2.13) | 21821214(12.1.0.2.10) |
| JUL2015 | 20831110(12.1.0.2.4) | 20996835(12.1.0.2.4) | 21188742(12.1.0.2.10) | 21126814(12.1.0.2.7) |
| APR2015 | 20299023(12.1.0.2.3) | 20485724(12.1.0.2.3) | 20698050(12.1.0.2.7) | 20684004(12.1.0.2.4) |
| JAN2015 | 19769480(12.1.0.2.2) | 19954978(12.1.0.2.2) | 20141343(12.1.0.2.4) | 19720843(12.1.0.2.1) |
| OCT2014 | 19303936(12.1.0.2.1) | 19392646(12.1.0.2.1) | 19404326(12.1.0.2.1) | N/A |
|
12.1.0.1 |
|||||
|
Description |
PSU |
GI PSU |
Bundle Patch |
||
|
A |
B |
Windows 64 bit |
Windows 32 bit |
||
|
JUL2016 (12.1.0.1.160719) |
23273935 | ||||
|
APR2016 (12.1.0.1.160419) |
|||||
|
JAN2016 (12.1.0.1.160119) |
|||||
|
OCT2015 |
21352619(12.1.0.1.9) |
21551666(12.1.0.1.9) |
21551685(12.1.0.1.9) |
21744907 (12.1.0.1.21) |
|
|
JUL2015 |
20831107(12.1.0.1.8) |
20996901(12.1.0.1.8) |
20996911(12.1.0.1.8) |
21076681 (12.1.0.1.20) |
|
|
APR2015 |
20299016(12.1.0.1.7) |
20485762(12.1.0.1.7) |
19971331(12.1.0.1.7) |
20558101 (12.1.0.1.18) |
|
|
JAN2015 |
19769486(12.1.0.1.6) |
19971324(12.1.0.1.6) |
19971331(12.1.0.1.6) |
20160748 (12.1.0.1.16) |
|
|
OCT2014 |
19121550(12.1.0.1.5) |
19392372(12.1.0.1.5) |
19392451(12.1.0.1.5) |
19542943 (12.1.0.1.14) |
|
|
JUL2014 |
18522516(12.1.0.1.4) |
18705901(12.1.0.1.4) |
18705972(12.1.0.1.4) |
19062327 (12.1.0.1.11) |
|
|
APR2014 |
18031528(12.1.0.1.3) |
18139660(12.1.0.1.3) |
18413105(12.1.0.1.3) |
18448604 (12.1.0.1.7) |
|
|
JAN2014 |
17552800(12.1.0.1.2) |
17735306 (12.1.0.1.2) |
17977915 (12.1.0.1.3) |
||
|
OCT2013 |
17027533(12.1.0.1.1) |
17272829 (12.1.0.1.1) |
17363796(12.1.0.1.1) |
17363795(12.1.0.1.1) |
|
|
11.2.0.4 |
||||
| Description | PSU | SPU(CPU) | GI PSU | Bundle Patch (Windows 32bit & 64bit) |
| JUL2019 (11.2.0.4.190716) | 29497421 | N/A | 29698727 | 29596609 |
| APR2019 (11.2.0.4.190416) | 29141056 | 28790634 | 29255947 | 29218820 |
| JAN2019 (11.2.0.4.190115) | 28729262 | 28790634 | 28813878 | 28761877 |
| OCT2018 (11.2.0.4.181016) | 28204707 | 28364007 | 28429134 | 28265827 |
| JUL2018 (11.2.0.4.180717) | 27734982 | 27870645 | 27967757 | 27695940 |
| APR2018 (11.2.0.4.180417) | 27338049 | 26474853 | 27475913 | 27381640 |
| JAN2018 (11.2.0.4.180116) | 26925576 | N/A | 27107360 | 27162965 |
| OCT2017 (11.2.0.4.171017) | 26392168 | 26474853 | 26635745 | 26581376 |
| AUG2017 (11.2.0.4.170814) | 26609445 | N/A | 26610246 | 26194138 |
| JUL2017 (11.2.0.4.170718) | 25869727 | 25879656 | 26030799 | 26194136 |
| APR2017 (11.2.0.4.170418) | 24732075 | 25369547 | 25476126 | 25632525 |
| JAN2017 | N/A | N/A | N/A | N/A |
| OCT2016 (11.2.0.4.161018) | 24006111 | 24433711 | 24436338 | 24591646 |
| JUL2016 (11.2.0.4.160719) | 23054359 | 23177648 | 23274134 | 23530402 |
| APR2016 (11.2.0.4.160419) | 22502456 | 22502493 | 22646198 | 22839608 |
| JAN2016 (11.2.0.4.160119) | 21948347 | 21972320 | 22191577 | 22310544 |
| OCT2015 | 21352635(11.2.0.4.8) | 21352646 | 21523375(11.2.0.4.8) | 21821802 (11.2.0.4.20) |
| JUL2015 | 20760982(11.2.0.4.7) | 20803583 | 20996923(11.2.0.4.7) | 21469106 (11.2.0.4.18) |
| APR2015 | 20299013(11.2.0.4.6) | 20299015 | 20485808(11.2.0.4.6) | 20544696 (11.2.0.4.15) |
| JAN2015 | 19769489(11.2.0.4.5) | 19854503 | 19955028(11.2.0.4.5) | 20127071 (11.2.0.4.12) |
| OCT2014 | 19121551(11.2.0.4.4) | 19271443 | 19380115(11.2.0.4.4) | 19651773 (11.2.0.4.10) |
| JUL2014 | 18522509(11.2.0.4.3) | 18681862 | 18706472(11.2.0.4.3) | 18842982(11.2.0.4.7) |
| APR2014 | 18031668(11.2.0.4.2) | 18139690 | 18139609(11.2.0.4.2) | 18296644(11.2.0.4.4) |
| JAN2014 | 17478514(11.2.0.4.1) | 17551709 | N/A | 17987366(11.2.0.4.1) |
|
11.2.0.3 |
|||||
|
Description |
PSU |
SPU(CPU) |
GI PSU |
Bundle Patch (Windows 64bit) |
Bundle Patch (Windows 32bit) |
|
JUL2015 |
20760997 (11.2.0.3.15) |
20996944 (11.2.0.3.15) |
|||
|
APR2015 |
20299017 (11.2.0.3.14) |
20485830 (11.2.0.3.14) |
|||
|
JAN2015 |
19769496 (11.2.0.3.13) |
19971343 (11.2.0.3.13) |
|||
|
OCT2014 |
19121548 (11.2.0.3.12) |
19440385 (11.2.0.3.12) |
|||
|
JUL2014 |
18522512 (11.2.0.3.11) |
18706488 (11.2.0.3.11) |
|||
|
APR2014 |
18031683 (11.2.0.3.10) |
18139678 (11.2.0.3.10) |
|||
|
JAN2014 |
17540582 (11.2.0.3.9) |
17735354 (11.2.0.3.9) |
|||
|
OCT2013 |
16902043 (11.2.0.3.8) |
17272731 (11.2.0.3.8) |
|||
|
JUL2013 |
16619892 (11.2.0.3.7) |
16742216 (11.2.0.3.7) |
|||
|
APR2013 |
16056266 (11.2.0.3.6) |
16083653 (11.2.0.3.6) |
|||
|
JAN2013 |
14727310 (11.2.0.3.5) |
14727347 (11.2.0.3.5) |
|||
|
OCT2012 |
14275605 (11.2.0.3.4) |
14275572 (11.2.0.3.4) |
|||
|
JUL2012 |
13923374 (11.2.0.3.3) |
13919095 (11.2.0.3.3) |
|||
|
APR2012 |
13696216 (11.2.0.3.2) |
13696251 (11.2.0.3.2) |
|||
|
JAN2012 |
13343438 (11.2.0.3.1) |
13348650 (11.2.0.3.1) |
|||
|
11.2.0.2 |
|||||
|
Description |
PSU |
SPU(CPU) |
GI PSU |
Bundle Patch (Windows 64bit) |
Bundle Patch (Windows 32bit) |
|
OCT2013 |
17082367 (11.2.0.2.12) |
17272753 (11.2.0.2.12) |
|||
|
JUL2013 |
16619893 (11.2.0.2.11) |
16742320 (11.2.0.2.11) |
|||
|
APR2013 |
16056267 (11.2.0.2.10) |
16166868 (11.2.0.2.10) |
|||
|
JAN2013 |
14727315 (11.2.0.2.9) |
14841385 (11.2.0.2.9) |
|||
|
OCT2012 |
14275621 (11.2.0.2.8) |
14390437 (11.2.0.2.8) |
|||
|
JUL2012 |
13923804 (11.2.0.2.7) |
14192201 (11.2.0.2.7) |
|||
|
APR2012 |
13696224 (11.2.0.2.6) |
13696242 (11.2.0.2.6) |
|||
|
JAN2012 |
13343424 (11.2.0.2.5) |
13653086 (11.2.0.2.5) |
|||
|
OCT2011 |
12827726 (11.2.0.2.4) |
12827731 (11.2.0.2.4) |
|||
|
JUL2011 |
12419331 (11.2.0.2.3) |
12419353 (11.2.0.2.3) |
|||
|
APR2011 |
11724916 (11.2.0.2.2) |
12311357 (11.2.0.2.2) |
|||
|
JAN2011 |
10248523 (11.2.0.2.1) |
N/A |
N/A |
||
|
11.2.0.1 |
||||
|
Description |
PSU |
CPU |
Bundle Patch (Windows 64bit) |
Bundle Patch (Windows 32bit) |
|
JUL2011 |
12419378 (11.2.0.1.6) |
|||
|
APR2011 |
11724930 (11.2.0.1.5) |
|||
|
JAN2011 |
10248516 (11.2.0.1.4) |
|||
|
OCT2010 |
9952216 (11.2.0.1.3) |
|||
|
JUL2010 |
9654983 (11.2.0.1.2) |
|||
|
APR2010 |
9352237 (11.2.0.1.1) |
N/A |
N/A |
|
|
10.2.0.5 |
|||||
|
Description |
PSU |
SPU(CPU) |
Bundle Patch (Windows 64bit) |
Bundle Patch (Windows 32bit) |
Bundle Patch (Windows Itanium) |
|
JUL2015 |
20299014(10.2.0.5.19) |
N/A |
|||
|
APR2015 |
N/A |
N/A |
N/A |
N/A |
N/A |
|
JAN2015 |
19769505(10.2.0.5.18) |
N/A |
|||
|
OCT2014 |
19274523(10.2.0.5.17) |
N/A |
|||
|
JUL2014 |
18522511(10.2.0.5.16) |
N/A |
|||
|
APR2014 |
18031728(10.2.0.5.15) |
N/A |
|||
|
JAN2014 |
17465584(10.2.0.5.14) |
N/A |
|||
|
OCT2013 |
17082365(10.2.0.5.13) |
N/A |
N/A |
||
|
JUL2013 |
16619894(10.2.0.5.12) |
||||
|
APR2013 |
16056270(10.2.0.5.11) |
||||
|
JAN2013 |
14727319(10.2.0.5.10) |
||||
|
OCT2012 |
14275629(10.2.0.5.9) |
||||
|
JUL2012 |
13923855(10.2.0.5.8) |
||||
|
APR2012 |
13632743(10.2.0.5.7) |
||||
|
JAN2012 |
13343471(10.2.0.5.6) |
N/A |
|||
|
OCT2011 |
12827745(10.2.0.5.5) |
N/A |
N/A |
||
|
JUL2011 |
12419392(10.2.0.5.4) |
N/A |
|||
|
APR2011 |
11724962(10.2.0.5.3) |
N/A |
|||
|
JAN2011 |
10248542(10.2.0.5.2) |
N/A |
|||
|
OCT2010 |
9952230(10.2.0.5.1) |
N/A |
|||
|
10.2.0.4 |
|||||
|
Description |
PSU |
SPU(CPU) |
Bundle Patch (Windows 32bit) |
Bundle Patch (Windows 64bit) |
Bundle Patch (Windows Itanium) |
|
JUL2013 |
16619897 (10.2.0.4.17) |
N/A |
N/A |
N/A |
|
|
APR2013 |
16056269 (10.2.0.4.16) |
N/A |
N/A |
N/A |
|
|
JAN2013 |
14736542 (10.2.0.4.15) |
N/A |
N/A |
N/A |
|
|
OCT2012 |
14275630 (10.2.0.4.14) |
N/A |
N/A |
N/A |
|
|
JUL2012 |
13923851 (10.2.0.4.13) |
N/A |
N/A |
N/A |
|
|
APR2012 |
12879933 (10.2.0.4.12) |
N/A |
|||
|
JAN2012 |
12879929 (10.2.0.4.11) |
N/A |
N/A |
||
|
OCT2011 |
12827778 (10.2.0.4.10) |
||||
|
JUL2011 |
12419397 (10.2.0.4.9) |
||||
|
APR2011 |
11724977 (10.2.0.4.8) |
||||
|
JAN2011 |
10248636 (10.2.0.4.7) |
||||
|
OCT2010 |
9952234 (10.2.0.4.6) |
||||
|
JUL2010 |
9654991 (10.2.0.4.5) |
||||
|
APR2010 |
9352164 (10.2.0.4.4) |
||||
|
JAN2010 |
9119284 (10.2.0.4.3) |
||||
|
OCT2009 |
8833280 (10.2.0.4.2) |
||||
|
JUL2009 |
8576156 (10.2.0.4.1) |
||||
|
APR2009 |
N/A |
||||
|
JAN2009 |
N/A |
N/A |
|||
|
OCT2008 |
N/A |
N/A |
|||
|
JUL2008 |
N/A |
N/A |
|||
|
10.2.0.3 |
||||
|
Description |
CPU (Unix/Linux) |
Bundle Patch (Windows 32bit) |
Bundle Patch (Windows Itanium) |
Bundle Patch (Windows 64bit) |
|
JAN2009 |
||||
|
OCT2008 |
||||
|
JUL2008 |
||||
|
APR2008 |
||||
|
JAN2008 |
||||
|
OCT2007 |
||||
|
JUL2007 |
||||
|
APR2007 |
||||
|
JAN2007 |
||||
|
10.2.0.2 |
||||
|
Description |
CPU (Unix/Linux) |
Bundle Patch (Windows 32bit) |
Bundle Patch (Windows 64bit) |
Bundle Patch (Windows Itanium) |
|
JAN2009 |
N/A |
N/A |
N/A |
|
|
OCT2008 |
N/A |
N/A |
N/A |
|
|
JUL2008 |
N/A |
N/A |
N/A |
|
|
APR2008 |
N/A |
N/A |
N/A |
|
|
JAN2008 |
N/A |
N/A |
N/A |
|
|
OCT2007 |
||||
|
JUL2007 |
||||
|
APR2007 |
||||
|
JAN2007 |
||||
|
OCT2006 |
||||
|
JUL2006 |
||||
|
APR2006 |
||||
|
10.2.0.1 |
||||
|
Description |
CPU (Unix/Linux) |
Bundle Patch (Windows 32bit) |
Bundle Patch (Windows 64bit) |
Bundle Patch (Windows Itanium) |
|
APR2007 |
N/A |
N/A |
N/A |
|
|
JAN2007 |
||||
|
OCT2006 |
||||
|
JUL2006 |
||||
|
APR2006 |
||||
|
JAN2006 |
||||
|
10.1.0.5 |
|||
|
Description |
CPU (Unix/Linux) |
Bundle Patch (Windows 32bit) |
Bundle Patch (Windows Itanium) |
|
JAN2012 |
|||
|
OCT2011 |
|||
|
JUL2011 |
|||
|
APR2011 |
|||
|
JAN2011 |
N/A |
N/A |
N/A |
|
OCT2010 |
|||
|
JUL2010 |
|||
|
APR2010 |
|||
|
JAN2010 |
|||
|
OCT2009 |
|||
|
JUL2009 |
|||
|
APR2009 |
|||
|
JAN2009 |
|||
|
OCT2008 |
|||
|
JUL2008 |
|||
|
APR2008 |
|||
|
JAN2008 |
|||
|
OCT2007 |
|||
|
JUL2007 |
|||
|
APR2007 |
|||
|
JAN2007 |
|||
|
OCT2006 |
|||
|
JUL2006 |
|||
|
APR2006 |
|||
|
JAN2006 |
|||
|
10.1.0.4 |
|||
|
Description |
CPU (Unix/Linux) |
Bundle Patch (Windows 32bit) |
Bundle Patch (Windows Itanium) |
|
APR2007 |
|||
|
JAN2007 |
|||
|
OCT2006 |
|||
|
JUL2006 |
|||
|
APR2006 |
|||
|
JAN2006 |
|||
|
OCT2005 |
|||
|
JUL2005 |
|||
|
APR2005 |
|||
|
10.1.0.3 |
|||
|
Description |
CPU (Unix/Linux) |
Bundle Patch (Windows 32bit) |
Bundle Patch (Windows Itanium) |
|
JAN2007 |
N/A |
N/A |
|
|
OCT2006 |
N/A |
N/A |
|
|
JUL2006 |
N/A |
N/A |
|
|
APR2006 |
N/A |
N/A |
|
|
JAN2006 |
|||
|
OCT2005 |
|||
|
JUL2005 |
|||
|
APR2005 |
|||
|
JAN2005 |
|||
|
10.1.0.2 |
|||
|
Description |
CPU (Unix/Linux) |
Bundle Patch (Windows 32bit) |
Bundle Patch (Windows Itanium) |
|
APR2005 |
|||
|
JUL2005 |
|||
|
JAN2005 |
|||
|
9.2.0.8 |
|||
|
Description |
CPU (Unix/Linux) |
Bundle Patch (Windows 32bit) |
Bundle Patch (Windows Itanium) |
|
JUL2010 |
|||
|
APR2010 |
N/A |
||
|
JAN2010 |
N/A |
||
|
OCT2009 |
|||
|
JUL2009 |
|||
|
APR2009 |
|||
|
JAN2009 |
|||
|
OCT2008 |
|||
|
JUL2008 |
|||
|
APR2008 |
|||
|
JAN2008 |
|||
|
OCT2007 |
|||
|
JUL2007 |
|||
|
APR2007 |
|||
|
JAN2007 |
N/A |
N/A |
N/A |
|
OCT2006 |
|||
|
9.2.0.7 |
|||
|
Description |
CPU (Unix/Linux) |
Bundle Patch (Windows 32bit) |
Bundle Patch (Windows Itanium) |
|
JUL2007 |
|||
|
APR2007 |
|||
|
JAN2007 |
|||
|
OCT2006 |
|||
|
JUL2006 |
|||
|
APR2006 |
|||
|
JAN2006 |
|||
|
OCT2005 |
|||
|
JUL2005 |
N/A |
N/A |
|
|
9.2.0.6 |
|||
|
Description |
CPU (Unix/Linux) |
Bundle Patch (Windows 32bit) |
Bundle Patch (Windows Itanium) |
|
OCT2006 |
|||
|
JUL2006 |
|||
|
APR2006 |
|||
|
JAN2006 |
|||
|
OCT2005 |
|||
|
JUL2005 |
|||
|
APR2005 |
|||
|
9.2.0.5 |
|||
|
Description |
CPU (Unix/Linux) |
Bundle Patch (Windows 32bit) |
Bundle Patch (Windows Itanium) |
|
OCT2006 |
N/A |
N/A |
|
|
JUL2006 |
N/A |
N/A |
|
|
APR2006 |
N/A |
N/A |
|
|
OCT2005 |
N/A |
N/A |
|
|
JUL2005 |
|||
|
APR2005 |
|||
|
JAN2005 |
|||
|
9.2.0.4 |
|||
|
Description |
CPU (Unix/Linux) |
Bundle Patch (Windows 32bit) |
Bundle Patch (Windows Itanium) |
|
JAN2005 |
|||
|
8.1.7.4 |
||
|
Description |
CPU (Unix/Linux) |
Bundle Patch (Windows 32bit) |
|
JAN2007 |
||
|
OCT2006 |
||
|
JUL2006 |
||
|
APR2006 |
||
|
JAN2006 |
||
|
OCT2005 |
||
|
JUL2005 |
||
|
APR2005 |
||
|
JAN2005 |
||
参考：Assistant: Download Reference for Oracle Database/GI Update, Revision, PSU, SPU(CPU), Bundle Patches, Patchsets and Base Releases (文档 ID 2118136.2)
又一起win rac的asm disk被格式化为ntfs,导致数据库异常恢复的请求,客户描述有一个500G的data磁盘组(只有一个磁盘,被误操作进行了格式化).格式化asm disk之后
asm的alert日志报错
Tue Jul 09 17:09:45 2019 NOTE: ASM client orcl1:ORCL disconnected unexpectedly. NOTE: check client alert log. NOTE: Trace records dumped in trace file D:\APP\ADMINISTRATOR\diag\asm\+asm\+asm1\trace\+asm1_ora_5376.trc Tue Jul 09 17:10:19 2019 Errors in file D:\APP\ADMINISTRATOR\diag\asm\+asm\+asm1\trace\+asm1_lgwr_1448.trc: ORA-27070: async read/write failed OSD-04008: WriteFile() 失败, 无法写入文件 O/S-Error: (OS 21) 设备未就绪。 WARNING: Write Failed. group:1 disk:0 AU:15 offset:876544 size:4096 NOTE: unable to write any mirror side for diskgroup DATA NOTE: cache initiating offline of disk 0 group DATA NOTE: process _lgwr_+asm1 (3764:1448) initiating offline of disk 0.4042281525 (DATA_0000) with mask 0x7e in group 1 Tue Jul 09 17:10:19 2019 WARNING: Disk 0 (DATA_0000) in group 1 mode 0x7f is now being offlined WARNING: Disk 0 (DATA_0000) in group 1 in mode 0x7f is now being taken offline on ASM inst 1 NOTE: initiating PST update: grp = 1, dsk = 0/0xf0f05235, mask = 0x6a, op = clear GMON updating disk modes for group 1 at 10 for pid 15, osid 1448 ERROR: Disk 0 cannot be offlined, since diskgroup has external redundancy. ERROR: too many offline disks in PST (grp 1) WARNING: Disk 0 (DATA_0000) in group 1 mode 0x7f offline is being aborted WARNING: Offline of disk 0 (DATA_0000) in group 1 and mode 0x7f failed on ASM inst 1 Tue Jul 09 17:10:20 2019 NOTE: halting all I/Os to diskgroup 1 (DATA) NOTE: unable to offline disks after getting write error for diskgroup DATA Tue Jul 09 17:10:20 2019 NOTE: cache dismounting (not clean) group 1/0xBDB0A2C0 (DATA) NOTE: disk 0 had IO error NOTE: messaging CKPT to quiesce pins Windows thread id: 520528, image: ORACLE.EXE (B000) Tue Jul 09 17:10:20 2019 NOTE: Deferred communication with ASM instance Errors in file D:\APP\ADMINISTRATOR\diag\asm\+asm\+asm1\trace\+asm1_ora_6140.trc: ORA-15130: diskgroup "DATA" is being dismounted NOTE: deferred map free for map id 4 NOTE: LGWR doing non-clean dismount of group 1 (DATA) NOTE: LGWR sync ABA=38.3028 last written ABA 38.3029
数据库的alert日志报错
Tue Jul 09 17:09:12 2019 Errors in file d:\app\administrator\diag\rdbms\orcl\orcl1\trace\orcl1_lgwr_5396.trc: ORA-27072: 文件 I/O 错误 WARNING: IO Failed. group:1 disk(number.incarnation):0.0xf0f05235 disk_path:\\.\ORCLDISKDATA0 AU:1305 disk_offset(bytes):1368456704 io_size:512 operation:Write type:asynchronous result:I/O error process_id:5396 Errors in file d:\app\administrator\diag\rdbms\orcl\orcl1\trace\orcl1_lgwr_5396.trc: ORA-15080: 与磁盘的同步 I/O 操作失败 WARNING: failed to write mirror side 1 of virtual extent 23 logical extent 0 of file 261 in group 1 on disk 0 allocation unit 1305 Errors in file d:\app\administrator\diag\rdbms\orcl\orcl1\trace\orcl1_lgwr_5396.trc: ORA-00345: 重做日志写入块 47231 计数 1 出错 ORA-00312: 联机日志 1 线程 1: '+DATA/orcl/onlinelog/group_1.261.909498607' ORA-15081: 无法将 I/O 操作提交到磁盘 ORA-15081: 无法将 I/O 操作提交到磁盘 Errors in file d:\app\administrator\diag\rdbms\orcl\orcl1\trace\orcl1_lgwr_5396.trc: ORA-27070: 异步读取/写入失败 OSD-04006: ReadFile() 失败, 无法读取文件 O/S-Error: (OS 21) 设备未就绪。
机器上查看磁盘信息
这个客户比较幸运,asm disk被格式化之后,没有进行任何写操作,理解对现场进行了保护,没有任何的二次破坏.因为处理过多起类似故障(oracle asm disk格式化恢复—格式化为ntfs文件系统,oracle asm disk格式化恢复—格式化为ext4文件系统又一例asm格式化文件系统恢复),有一定的经验,一般三种方案恢复数据文件：
1)运气好直接通过kfed进行修复asm disk,然后mount起来,然后把数据文件拷贝到文件系统中
2)通过相关工具把asm disk中相关的数据文件拷贝到文件系统中
3)如果损坏的严重,通过底层碎片,把相关数据文件恢复到文件系统中
拷贝完成数据文件之后,然后根据文件的情况有几种可能性:
1)直接open数据库,处理可能的其他坏块
2)通过一些方法强制拉库,然后对其进行导出导入新库
3)通过工具直接恢复表数据,甚至恢复部分核心数据
这次的恢复运气不错,对asm disk进行一系列修复之后,asm 磁盘组mount成功
C:\Users\Administrator>asmtool -list
NTFS \Device\Harddisk0\Partition1 80000M
NTFS \Device\Harddisk0\Partition2 491133M
ORCLDISKOCR0 \Device\Harddisk1\Partition1 34132M
ORCLDISKOCR1 \Device\Harddisk1\Partition2 34132M
ORCLDISKOCR2 \Device\Harddisk1\Partition3 34133M
ORCLDISKDATA0 \Device\Harddisk2\Partition1 511997M
ORCLDISKFRA0 \Device\Harddisk3\Partition2 1225000M
NTFS \Device\Harddisk3\Partition3 1225800M
\Device\Harddisk3\Partition4 1314861M
C:\Users\Administrator>sqlplus / as sysasm
SQL*Plus: Release 11.2.0.1.0 Production on 星期六 7月 13 11:20:02 2019
Copyright (c) 1982, 2010, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options
SQL> alter diskgroup data mount;
Diskgroup altered.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64
bit Production
With the Real Application Clusters and Automatic Storage Management options
mount数据库拷贝文件
C:\Users\Administrator>sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on 星期六 7月 13 16:02:08 2019 Copyright (c) 1982, 2010, Oracle. All rights reserved. 已连接到空闲例程。 SQL> startup mount; ORACLE 例程已经启动。 Total System Global Area 3.4206E+10 bytes Fixed Size 2192864 bytes Variable Size 7516195360 bytes Database Buffers 2.6642E+10 bytes Redo Buffers 45727744 bytes 数据库已装载 RMAN> backup as copy database format 'I:\rmanback\df_%U.dbf'; 启动 backup 于 13-7月 -19 使用通道 ORA_DISK_1 使用通道 ORA_DISK_2 使用通道 ORA_DISK_3 使用通道 ORA_DISK_4 通道 ORA_DISK_1: 启动数据文件副本 输入数据文件: 文件号=00006 名称=+DATA/orcl/datafile/XFF5.dmp 通道 ORA_DISK_2: 启动数据文件副本 输入数据文件: 文件号=00016 名称=+DATA/orcl/datafile/XFF53 通道 ORA_DISK_3: 启动数据文件副本 输入数据文件: 文件号=00017 名称=+DATA/orcl/datafile/XFF54 通道 ORA_DISK_4: 启动数据文件副本 输入数据文件: 文件号=00018 名称=+DATA/orcl/datafile/XFF55 输出文件名=I:\RMANBACK\DF_DATA_D-ORCL_I-1437279340_TS-XFF5_FNO-16_0VU6I1FO.DBF 标记=TAG20190713T120927 RECID=2 STAMP=1013517954 通道 ORA_DISK_2: 数据文件复制完毕, 经过时间: 00:36:29 通道 ORA_DISK_2: 启动数据文件副本 输入数据文件: 文件号=00019 名称=+DATA/orcl/datafile/XFF5.281.988042407 输出文件名=I:\RMANBACK\DF_DATA_D-ORCL_I-1437279340_TS-XFF5_FNO-17_10U6I1FO.DBF 标记=TAG20190713T120927 RECID=1 STAMP=1013517951 通道 ORA_DISK_3: 数据文件复制完毕, 经过时间: 00:36:29 通道 ORA_DISK_3: 启动数据文件副本 输入数据文件: 文件号=00020 名称=+DATA/orcl/datafile/XFF5.282.988042943 输出文件名=I:\RMANBACK\DF_DATA_D-ORCL_I-1437279340_TS-XFF5_FNO-18_11U6I1FO.DBF 标记=TAG20190713T120927 RECID=3 STAMP=1013517962 通道 ORA_DISK_4: 数据文件复制完毕, 经过时间: 00:36:37 通道 ORA_DISK_4: 启动数据文件副本 输入数据文件: 文件号=00021 名称=+DATA/orcl/datafile/XFF5.283.988043279 输出文件名=I:\RMANBACK\DF_DATA_D-ORCL_I-1437279340_TS-XFF5_FNO-6_0UU6I1FO.DBF 标记=TAG20190713T120927 RECID=4 STAMP=1013518052 通道 ORA_DISK_1: 数据文件复制完毕, 经过时间: 00:38:12 通道 ORA_DISK_1: 启动数据文件副本 输入数据文件: 文件号=00022 名称=+DATA/orcl/datafile/XFF5.284.988043721 输出文件名=I:\RMANBACK\DF_DATA_D-ORCL_I-1437279340_TS-XFF5_FNO-20_13U6I3K5.DBF 标记=TAG20190713T120927 RECID=5 STAMP=1013519584 通道 ORA_DISK_3: 数据文件复制完毕, 经过时间: 00:27:10 通道 ORA_DISK_3: 启动数据文件副本 输入数据文件: 文件号=00009 名称=+DATA/orcl/datafile/XFF51.dmp 输出文件名=I:\RMANBACK\DF_DATA_D-ORCL_I-1437279340_TS-XFF5_FNO-21_14U6I3KD.DBF 标记=TAG20190713T120927 RECID=6 STAMP=1013519586 通道 ORA_DISK_4: 数据文件复制完毕, 经过时间: 00:27:03 通道 ORA_DISK_4: 启动数据文件副本 输入数据文件: 文件号=00010 名称=+DATA/orcl/datafile/XFF52.dmp 输出文件名=I:\RMANBACK\DF_DATA_D-ORCL_I-1437279340_TS-XFF5_FNO-19_12U6I3K5.DBF 标记=TAG20190713T120927 RECID=7 STAMP=1013519599 通道 ORA_DISK_2: 数据文件复制完毕, 经过时间: 00:27:26 通道 ORA_DISK_2: 启动数据文件副本 输入数据文件: 文件号=00003 名称=+DATA/orcl/datafile/undotbs1.258.909498475 输出文件名=I:\RMANBACK\DF_DATA_D-ORCL_I-1437279340_TS-XFF5_FNO-22_15U6I3NC.DBF 标记=TAG20190713T120927 RECID=8 STAMP=1013519695 通道 ORA_DISK_1: 数据文件复制完毕, 经过时间: 00:27:19 通道 ORA_DISK_1: 启动数据文件副本 输入数据文件: 文件号=00005 名称=+DATA/orcl/datafile/undotbs2.264.909498739 输出文件名=I:\RMANBACK\DF_DATA_D-ORCL_I-1437279340_TS-UNDOTBS1_FNO-3_18U6I57K.DB F 标记=TAG20190713T120927 RECID=9 STAMP=1013520920 通道 ORA_DISK_2: 数据文件复制完毕, 经过时间: 00:22:02 通道 ORA_DISK_2: 启动数据文件副本 输入数据文件: 文件号=00012 名称=+DATA/orcl/datafile/XFF5.275.954036129 输出文件名=I:\RMANBACK\DF_DATA_D-ORCL_I-1437279340_TS-UNDOTBS2_FNO-5_19U6I5AJ.DB F 标记=TAG20190713T120927 RECID=10 STAMP=1013521015 通道 ORA_DISK_1: 数据文件复制完毕, 经过时间: 00:22:03 通道 ORA_DISK_1: 启动数据文件副本 输入数据文件: 文件号=00013 名称=+DATA/orcl/datafile/XFF5.276.954036233 输出文件名=I:\RMANBACK\DF_DATA_D-ORCL_I-1437279340_TS-XFF5_FNO-10_17U6I574.DBF 标记=TAG20190713T120927 RECID=11 STAMP=1013521231 通道 ORA_DISK_4: 数据文件复制完毕, 经过时间: 00:27:30 通道 ORA_DISK_4: 启动数据文件副本 输入数据文件: 文件号=00002 名称=+DATA/orcl/datafile/sysaux.257.909498475 输出文件名=I:\RMANBACK\DF_DATA_D-ORCL_I-1437279340_TS-XFF5_FNO-9_16U6I574.DBF 标记=TAG20190713T120927 RECID=12 STAMP=1013521244 通道 ORA_DISK_3: 数据文件复制完毕, 经过时间: 00:27:37 通道 ORA_DISK_3: 启动数据文件副本 输入数据文件: 文件号=00023 名称=+DATA/orcl/datafile/XFF60.dmp 输出文件名=I:\RMANBACK\DF_DATA_D-ORCL_I-1437279340_TS-XFF5_FNO-12_1AU6I6GU.DBF 标记=TAG20190713T120927 RECID=13 STAMP=1013522267 通道 ORA_DISK_2: 数据文件复制完毕, 经过时间: 00:22:25 通道 ORA_DISK_2: 启动数据文件副本 输入数据文件: 文件号=00024 名称=+DATA/orcl/datafile/XFF61.dmp 输出文件名=I:\RMANBACK\DF_DATA_D-ORCL_I-1437279340_TS-XFF5_FNO-13_1BU6I6JU.DBF 标记=TAG20190713T120927 RECID=15 STAMP=1013522367 通道 ORA_DISK_1: 数据文件复制完毕, 经过时间: 00:22:26 通道 ORA_DISK_1: 启动数据文件副本 输入数据文件: 文件号=00011 名称=+DATA/orcl/datafile/system.dmp 输出文件名=I:\RMANBACK\DF_DATA_D-ORCL_I-1437279340_TS-SYSAUX_FNO-2_1CU6I6QM.DBF 标记=TAG20190713T120927 RECID=14 STAMP=1013522361 通道 ORA_DISK_4: 数据文件复制完毕, 经过时间: 00:18:50 通道 ORA_DISK_4: 启动数据文件副本 输入数据文件: 文件号=00001 名称=+DATA/orcl/datafile/system.256.909498475 输出文件名=I:\RMANBACK\DF_DATA_D-ORCL_I-1437279340_TS-XFF5_FNO-23_1DU6I6QT.DBF 标记=TAG20190713T120927 RECID=16 STAMP=1013522400 通道 ORA_DISK_3: 数据文件复制完毕, 经过时间: 00:19:19 通道 ORA_DISK_3: 启动数据文件副本 输入数据文件: 文件号=00025 名称=+DATA/orcl/datafile/XFF62.dmp 输出文件名=I:\RMANBACK\DF_DATA_D-ORCL_I-1437279340_TS-XFF5_FNO-25_1HU6I7V4.DBF 标记=TAG20190713T120927 RECID=17 STAMP=1013522490 通道 ORA_DISK_3: 数据文件复制完毕, 经过时间: 00:01:35 通道 ORA_DISK_3: 启动数据文件副本 输入数据文件: 文件号=00026 名称=+DATA/orcl/datafile/XFF63.dmp 输出文件名=I:\RMANBACK\DF_DATA_D-ORCL_I-1437279340_TS-SYSTEM_FNO-1_1GU6I7U0.DBF 标记=TAG20190713T120927 RECID=18 STAMP=1013522526 通道 ORA_DISK_4: 数据文件复制完毕, 经过时间: 00:02:47 通道 ORA_DISK_4: 启动数据文件副本 输入数据文件: 文件号=00007 名称=+DATA/orcl/datafile/precise.dbf 输出文件名=I:\RMANBACK\DF_DATA_D-ORCL_I-1437279340_TS-SYSTEM_FNO-11_1FU6I7U0.DBF 标记=TAG20190713T120927 RECID=20 STAMP=1013522576 通道 ORA_DISK_1: 数据文件复制完毕, 经过时间: 00:03:33 通道 ORA_DISK_1: 启动数据文件副本 输入数据文件: 文件号=00004 名称=+DATA/orcl/datafile/users.259.909498477 RMAN-03009: backup 命令 (ORA_DISK_3 通道上, 在 07/13/2019 14:03:01 上) 失败 ORA-19566: 超出损坏块限制 0 (文件 +DATA/orcl/datafile/XFF63.dmp) 继续执行其他作业步骤, 将不重新运行失败的作业 通道 ORA_DISK_3: 启动数据文件副本 输入数据文件: 文件号=00014 名称=+DATA/orcl/datafile/test.280.972807149 输出文件名=I:\RMANBACK\DF_DATA_D-ORCL_I-1437279340_TS-PRECISE_FNO-7_1JU6I837.DBF 标记=TAG20190713T120927 RECID=19 STAMP=1013522572 通道 ORA_DISK_4: 数据文件复制完毕, 经过时间: 00:00:46 输出文件名=I:\RMANBACK\DF_DATA_D-ORCL_I-1437279340_TS-USERS_FNO-4_1KU6I84L.DBF 标记=TAG20190713T120927 RECID=21 STAMP=1013522591 通道 ORA_DISK_1: 数据文件复制完毕, 经过时间: 00:00:15 输出文件名=I:\RMANBACK\DF_DATA_D-ORCL_I-1437279340_TS-TEST_FNO-14_1LU6I84L.DBF 标记=TAG20190713T120927 RECID=22 STAMP=1013522591 通道 ORA_DISK_3: 数据文件复制完毕, 经过时间: 00:00:15 输出文件名=I:\RMANBACK\DF_DATA_D-ORCL_I-1437279340_TS-XFF5_FNO-24_1EU6I7R0.DBF 标记=TAG20190713T120927 RECID=23 STAMP=1013522974 通道 ORA_DISK_2: 数据文件复制完毕, 经过时间: 00:11:44 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03009: backup 命令 (ORA_DISK_3 通道上, 在 07/13/2019 14:03:01 上) 失败 ORA-19566: 超出损坏块限制 0 (文件 +DATA/orcl/datafile/XFF63.dmp)
运气不错,除+DATA/orcl/datafile/XFF63.dmp文件上面有坏块之外其他文件没有发现坏块,对该文件进行特殊方式拷贝处理
dbv检查该文件
C:\Users\Administrator>dbv file=i:/rmanback/26.dbf DBVERIFY: Release 11.2.0.1.0 - Production on 星期六 7月 13 14:09:32 2019 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. DBVERIFY - 开始验证: FILE = I:\RMANBACK\26.DBF 页 132735 流入 - 很可能是介质损坏 Corrupt block relative dba: 0x0682067f (file 26, block 132735) Fractured block found during dbv: Data in bad block: type: 40 format: 2 rdba: 0x0682067f last change scn: 0x0000.333eaa21 seq: 0x2 flg: 0x04 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0xaa550000 check value in block header: 0x4242 computed block checksum: 0xb24f DBVERIFY - 验证完成 检查的页总数: 153600 处理的页总数 (数据): 9283 失败的页总数 (数据): 0 处理的页总数 (索引): 4789 失败的页总数 (索引): 0 处理的页总数 (其他): 139463 处理的总页数 (段) : 0 失败的总页数 (段) : 0 空的页总数: 64 标记为损坏的总页数: 1 流入的页总数: 1 加密的总页数 : 0 最高块 SCN : 1029666541 (0.1029666541)
这次运气非常好,该文件也只有一个坏块,整体来说,把正在运行的asm disk磁盘格式化为ntfs格式化,整个数据库文件只发现一个坏块.拷贝数据文件,redo,ctl等之后
通过重建控制文件尝试open数据库
SQL> @ctl.sql 控制文件已创建。 SQL> RECOVER DATABASE; 完成介质恢复。 SQL> ALTER DATABASE OPEN; 数据库已更改。
通过dba_extents 定位坏块对象,然后根据实际情况处理(index直接rebuild,表跳过,lob置空等方法),确定数据没有问题,重建磁盘组,数据回迁,恢复完美完成
最近遇到客户Oracle文件被加密,后缀名为:.id-BE19A09A.[savemydata@qq.com].harma
对应的txt文件为:
通过分析,确定该加密是对数据文件进行分段式进行处理加密破坏,通过分析出来oracle字典存储信息,和对应的数据存储关系,open数据库,跳过被分段加密部分,实现数据库较为完美恢复
对于sql server数据库,如果不幸被该中类型病毒加密,在数据库层面也可以实现较为完美恢复,尽可能减少损失,不助长黑客的猖狂行为(就是不给他们交比特币)
有客户晚上对数据库进行正常的重启维护,结果去遭遇数据库无法正常启动故障,报ORA-600 kkdlcob-objn-exist 40错误.
alert日志信息
Sat Jul 06 12:05:50 2019 alter database open Sat Jul 06 12:05:51 2019 LGWR: STARTING ARCH PROCESSES Sat Jul 06 12:05:51 2019 ARC0 started with pid=19, OS id=7056 ARC0: Archival started LGWR: STARTING ARCH PROCESSES COMPLETE ARC0: STARTING ARCH PROCESSES Thread 1 opened at log sequence 12 Current log# 3 seq# 12 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO03.LOG Successful open of redo thread 1 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Sat Jul 06 12:05:52 2019 SMON: enabling cache recovery Sat Jul 06 12:05:52 2019 ARC1 started with pid=21, OS id=7948 Sat Jul 06 12:05:52 2019 ARC2 started with pid=22, OS id=11552 Sat Jul 06 12:05:52 2019 ARC3 started with pid=23, OS id=5504 ARC1: Archival started ARC2: Archival started ARC1: Becoming the 'no FAL' ARCH ARC1: Becoming the 'no SRL' ARCH ARC2: Becoming the heartbeat ARCH ARC3: Archival started ARC0: STARTING ARCH PROCESSES COMPLETE Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_3092.trc (incident=20562): ORA-00600: internal error code, arguments: [kkdlcob-objn-exists], [40], [], [], [], [], [], [], [], [], [], [] Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\incident\incdir_20562\orcl_ora_3092_i20562.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_3092.trc: ORA-00704: bootstrap process failure ORA-00600: internal error code, arguments: [kkdlcob-objn-exists], [40], [], [], [], [], [], [], [], [], [], [] Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_3092.trc: ORA-00704: bootstrap process failure ORA-00600: internal error code, arguments: [kkdlcob-objn-exists], [40], [], [], [], [], [], [], [], [], [], [] Error 704 happened during db open, shutting down database USER (ospid: 3092): terminating the instance due to error 704 Instance terminated by USER, pid = 3092 ORA-1092 signalled during: alter database open... opiodr aborting process unknown ospid (3092) as a result of ORA-1092 Sat Jul 06 12:05:56 2019 ORA-1092 : opitsk aborting process
ORA-600 kkdlcob-objn-exists官方解释，一般是由于ddl操作时dataobj#不合适引起,最大的可能是_NEXT_OBJECT值不对
对数据库启动过程进行跟踪
CREATE UNIQUE INDEX I_OBJ5 ON OBJ$(SPARE3,NAME,NAMESPACE,TYPE#,OWNER#,REMOTEOWNER,LINKNAME,SUBNAME,OBJ#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 40 EXTENTS (FILE 1 BLOCK 368)) END OF STMT PARSE #299964504:c=0,e=368,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=440321312,tim=8823997737 ORA-00600: internal error code, arguments: [kkdlcob-objn-exists], [40], [], [], [], [], [], [], [], [], [], [] EXEC #299964504:c=875000,e=1213391,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=440321312,tim=8825211183 ERROR #299964504:err=600 tim=8825211203 ORA-00704: bootstrap process failure ORA-00600: internal error code, arguments: [kkdlcob-objn-exists], [40], [], [], [], [], [], [], [], [], [], [] ORA-00704: bootstrap process failure ORA-00600: internal error code, arguments: [kkdlcob-objn-exists], [40], [], [], [], [], [], [], [], [], [], []
发现是I_OBJ5无法正常创建,导致数据库无法正常启动,由于这个是obj$对象的一个index(而且是11.2之后版本才引进),不是数据库启动必须的对象,通过bbed修改相关基表,让数据库启动不在跳过该index,数据库启动成功.因为进行了非常规的方法恢复,建议客户导出数据,导入到新库,数据库恢复工作完成.
客户数据库版本10.2.0.4，启动成功之后立马crash，让我们协助解决
Thu Jul 4 13:03:10 2019 Completed: ALTER DATABASE OPEN Thu Jul 4 13:03:10 2019 db_recovery_file_dest_size of 2048 MB is 0.00% used. This is a user-specified limit on the amount of space that will be used by this database for recovery-related files, and does not reflect the amount of space available in the underlying filesystem or ASM diskgroup. Thu Jul 4 13:04:01 2019 Errors in file /oracle/app/oracle/admin/orcl/bdump/orcl_reco_22268.trc: ORA-00600: internal error code, arguments: [2252], [3987], [3375047096], [], [], [], [], [] Thu Jul 4 13:04:01 2019 Errors in file /oracle/app/oracle/admin/orcl/bdump/orcl_reco_22268.trc: ORA-00600: internal error code, arguments: [2252], [3987], [3375047096], [], [], [], [], [] Thu Jul 4 13:04:02 2019 Errors in file /oracle/app/oracle/admin/orcl/bdump/orcl_reco_22268.trc: ORA-00600: internal error code, arguments: [2252], [3987], [3375047096], [], [], [], [], [] Thu Jul 4 13:04:02 2019 RECO: terminating instance due to error 476 Instance terminated by RECO, pid = 22268
根据以往经验记录一次ORA-00600[2252]故障解决,很可能是scn过大引起.通过Oracle数据库异常恢复检查脚本(Oracle Database Recovery Check)检查scn相关信息
从ORA-600 2252错误信息看,由于scn可能超过该数据库的天花板理论上而导致该问题,而reco进程主要是由于分布式事务引起,通过和客户确认,该库有通过dblink去访问11204版本oracle,而从2019年6月23日之后scn的算法发生了一些改变(SCN Compatibility问题汇总-2019年6月23日),导致数据库可以支持更大的scn,从而当低版本需要进行分布式事务操作之时,可能导致数据库异常.
处理方案：通过临时屏蔽分布式事务,让数据库临时正常工作;长期解决方案需要把数据库版本升级,避免scn引起相关问题