标签云
asm 恢复 asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 ORACLE恢复 Oracle 恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (100)
- 数据库 (1,595)
- DB2 (22)
- MySQL (70)
- Oracle (1,462)
- Data Guard (49)
- EXADATA (7)
- GoldenGate (21)
- ORA-xxxxx (158)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (13)
- ORACLE 21C (3)
- Oracle ASM (65)
- Oracle Bug (7)
- Oracle RAC (47)
- Oracle 安全 (6)
- Oracle 开发 (27)
- Oracle 监听 (27)
- Oracle备份恢复 (530)
- Oracle安装升级 (83)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (75)
- PostgreSQL (16)
- PostgreSQL恢复 (4)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (36)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (19)
-
最近发表
- PostgreSQL部分主要字典信息
- PostgreSQL恢复系列:pg_filedump恢复字典构造
- PostgreSQL 16 源码安装
- ORA-00742 ORA-00312 恢复
- 数据库open成功后报ORA-00353 ORA-00354错误引起的一系列问题(本质ntfs文件系统异常)
- ORA-600 ktsiseginfo1故障
- ORA-00600: internal error code, arguments: [16703], [1403], [4] 原因
- 最近遇到几起ORA-600 16703故障(tab$被清空),请引起重视
- ORA-600 2662快速恢复之Patch scn工具
- TNS-12518: TNS:listener could not hand off client connection
- ora.storage无法启动报ORA-12514故障处理
- 断电引起文件scn异常数据库恢复
- ORA-16188: LOG_ARCHIVE_CONFIG settings inconsistent with previously started instance
- .[hudsonL@cock.li].mkp勒索加密数据库完美恢复
- 模拟带库实现rman远程备份
- 又一例:ORA-600 kclchkblk_4和2662故障
- Oracle误删除数据文件恢复
- Oracle 19C 备库DML重定向—DML Redirection
- ORA-01595/ORA-600 4194处理
- 从ORA-00283 ORA-16433报错开始恢复
月归档:一月 2014
关于dul有效期描述
dul是oracle内部工具,用于在数据库不open启动下挖取数据文件内容,从而最小程度减少数据损失.主要可以恢复如下情况:
1.用于异常断电,强制关闭数据库等故障导致数据库使用隐含参数,bbed等各种非常规数据库恢复方法无法正常open的数据库恢复
2.用于恢复无truncate,drop表恢复
3.用于丢失system表空间文件数据库恢复
4.用于大量坏块数据库恢复
5.用于丢失部分非system数据文件恢复
关于dul的相关使用文档请参考:ORACLE DUL汇总,因为dul是oracle内部工具,不需要数据库open就可以获得数据,为了数据安全,dul软件增加了有效期,本文就是对于dul的有效期进行了描述
关于dul的有效期
熟悉dul的人都清楚,dul从10开始就有有效期之说,一般的有效期是1到2个月,极少数情况会到3个月的有效期.通过试验验证
--使用上一期dul,在未修改系统时间的情况下提示过期 e:\dul10>dul Data UnLoader: 10.2.0.5.25 - Internal Only - on Sat Jan 25 16:17:41 2014 with 64-bit io functions Copyright (c) 1994 2013 Bernard van Duijnen All rights reserved. Strictly Oracle Internal Use Only Cannot start now You need a more recent DUL version for this os --修改系统时间,提示加载成功 e:\dul10>dul Data UnLoader: 10.2.0.5.25 - Internal Only - on Sat Dec 21 16:21:16 2013 with 64-bit io functions Copyright (c) 1994 2013 Bernard van Duijnen All rights reserved. Strictly Oracle Internal Use Only --配置dul挖取数据文件,dul又提示和以前一样错误 e:\dul10>dul Data UnLoader: 10.2.0.5.25 - Internal Only - on Sat Dec 21 16:19:49 2013 with 64-bit io functions Copyright (c) 1994 2013 Bernard van Duijnen All rights reserved. Strictly Oracle Internal Use Only Cannot open file now You need a more recent DUL version for this os --使用最新版dul,在不修改系统时间和配置挖取文件的情况下均正常使用 e:\dul10>dul Data UnLoader: 10.2.0.5.26 - Internal Only - on Sat Jan 25 16:22:15 2014 with 64-bit io functions Copyright (c) 1994 2014 Bernard van Duijnen All rights reserved. Strictly Oracle Internal Use Only e:\dul10>dul Data UnLoader: 10.2.0.5.26 - Internal Only - on Sat Jan 25 16:22:39 2014 with 64-bit io functions Copyright (c) 1994 2014 Bernard van Duijnen All rights reserved. Strictly Oracle Internal Use Only Found db_id = 4156511432 Found db_name = ORA10G
这里可以知道dul是通过系统时间和数据文件头时间两重保证该软件安全,不能通过修改系统时间来破解
使用DUL挖数据文件恢复非数据外对象方法
在dul进行数据库挖掘恢复的时候,我们可以通过unload table/user等方式来恢复表数据,但是对于一些view,index,trigger,source,seq,Dblink等不能直接通过unload来实现,但是可以通过挖基表来实现相关操作,这里提供了一些处理思路,在实际操作中根据需求,分析数据字典灵活应用
一.view
导出对象
USER$ OBJ$ COL$ VIEW$
执行sql语句
Set pages 10000 Set long 1000 Spool d:\create_view.sql select 'CREATE OR REPLACE VIEW '||O.NAME||' ('|| replace(c.cols,',',','||chr(10))||')'||CHR(10)|| 'as'||chr(10), v.text from user$ u, obj$ o, view$ v, ( SELECT COL.OBJ#, COL.COLS FROM (SELECT OBJ#, COL#, substr(SYS_CONNECT_BY_PATH(NAME,','),2) COLS FROM COL$ WHERE COL# > 0 START WITH COL# = 1 CONNECT BY PRIOR OBJ# = OBJ# AND PRIOR COL# = COL# - 1 ) COL, (SELECT OBJ#, COUNT(*) COLCNT FROM COL$ WHERE COL# > 0 GROUP BY OBJ#) CN WHERE COL.OBJ# = CN.OBJ# AND COL.COL# = CN.COLCNT ) C where u.user#=o.owner# and o.obj# = c.obj# and v.obj# = o.obj# and u.name=upper('&username');
说明
1) 分布执行,不能放置一个脚本文件中执行
2) 每条as后面的select语句可能需要重新格式化
3) Create view 语句最后需要增加”;”
二.source
导出对象
USER$ SOURCE$ OBJ$
执行sql语句
Set pages 10000 Set long 1000 set linse 1000 Spool d:\create_source.sql SELECT DECODE(S.LINE,1,'CREATE OR REPLACE ','')||SOURCE SOURCE FROM USER$ U, OBJ$ O, SOURCE$ S WHERE U.USER# = O.OWNER# AND O.OBJ# = S.OBJ# AND U.NAME = UPPER('&username') -- AND O.NAME = UPPER('&SOURCE_NAME') ORDER BY S.OBJ#, S.LINE;
说明
1) 注意SOURCE中的用户名,如果导入不是相同用户,需要修改该脚本用户名
2) 修改完用户名后,直接执行生成脚本即可
三.Index
导出对象
USER$ OBJ$ COL$ IND$ ICOL$
执行sql
Set pages 10000 Set long 1000 set linse 1000 Spool d:\create_index.sql SELECT 'CREATE '||decode(bitand(IDX.property, 1), 1, 'UNIQUE', '')|| ' INDEX '||I.NAME||' ON '||T.NAME||'('||IDX.PATH||');' INDEX_DDL FROM USER$ U, OBJ$ T, OBJ$ I, ( select I.PROPERTY, I.BO#, I.OBJ#, C.POS#, SUBSTR(sys_connect_by_path(CN.NAME,','),2) path from IND$ I, ICOL$ C, COL$ CN WHERE I.OBJ# = C.OBJ# AND I.BO# = C.BO# AND I.BO# = CN.OBJ# AND C.COL# = CN.INTCOL# start with C.POS#=1 connect by PRIOR I.OBJ# = I.OBJ# AND prior C.POS# = C.POS# - 1 ) IDX, (SELECT I.BO#, I.OBJ#, COUNT(*) COLCNT FROM ICOL$ I GROUP BY I.BO#, I.OBJ#) IDXC WHERE U.USER# = T.OWNER# AND IDX.BO# = T.OBJ# AND IDX.OBJ# = I.OBJ# AND IDX.BO# = IDXC.BO# AND IDX.OBJ# = IDXC.OBJ# AND IDX.POS# = IDXC.COLCNT AND U.NAME = upper('&username') ORDER BY T.NAME, I.NAME;
说明
1) 因为SYS_CONNECT_BY_PATH所以需要10g及其以上版本
2) SQL中没有分区唯一性索引
3) 注意检查sql是否因为行长度不够导致异常
四.Sequence
导出对象
USER$ OBJ$ SEQ$
执行sql语句
Set pages 10000 Set long 1000 set linse 1000 Spool d:\create_sequence.sql SELECT 'CREATE SEQUENCE '|| SEQ_NAME || ' MINVALUE '||minval || ' MAXVALUE '||MAXVAL || ' START WITH '||LASTVAL || ' ' || CYC || ' ' || ORD || DECODE(SIGN(CACHE), 1,' CACHE '|| CACHE, 'NOCACHE') || ';' SEQ_DDL from (select u.name OWNER, o.name SEQ_NAME, s.minvalue MINVAL, s.maxvalue MAXVAL, s.increment$ INC, decode (s.cycle#, 0, 'NOCYCLE', 1, 'CYCLE ') CYC, decode (s.order$, 0, 'NOORDER', 1, 'ORDER') ORD, s.cache, s.highwater LASTVAL from seq$ s, obj$ o, user$ u where u.user# = o.owner# and o.obj# = s.obj# and u.name=upper('&username'));
五.TRIGGER
导出对象
OBJ$ USER$ TRIGGER$
执行sql语句
Set pages 10000 Set long 1000 set linse 1000 Spool d:\create_trigger.sql select 'CREATE OR REPLACE TRIGGER '|| trigger_name || chr(10)|| decode( substr( trigger_type, 1, 1 ), 'A', 'AFTER ', 'B', 'BEFORE ', 'I', 'INSTEAD OF ' ) || triggering_event || ' ON ' || table_owner || '.' || table_name || chr(10) || REF_CLAUSE || chr(10) || decode( instr( trigger_type, 'EACH ROW' ), 0, null, 'FOR EACH ROW' ), trigger_body from ( select trigusr.name owner, trigobj.name trigger_name, decode(t.type#, 0, 'BEFORE STATEMENT', 1, 'BEFORE EACH ROW', 2, 'AFTER STATEMENT', 3, 'AFTER EACH ROW', 4, 'INSTEAD OF', 'UNDEFINED') trigger_type, decode(t.insert$*100 + t.update$*10 + t.delete$, 100, 'INSERT', 010, 'UPDATE', 001, 'DELETE', 110, 'INSERT OR UPDATE', 101, 'INSERT OR DELETE', 011, 'UPDATE OR DELETE', 111, 'INSERT OR UPDATE OR DELETE', 'ERROR') triggering_event, tabusr.name table_owner, tabobj.name table_name, 'REFERENCING NEW AS '||t.refnewname||' OLD AS '||t.refoldname REF_CLAUSE, t.whenclause,decode(t.enabled, 0, 'DISABLED', 1, 'ENABLED', 'ERROR') STATUS, t.definition , t.action# trigger_body from obj$ trigobj, obj$ tabobj, trigger$ t, user$ tabusr, user$ trigusr where (trigobj.obj# = t.obj# and tabobj.obj# = t.baseobject and tabobj.owner# = tabusr.user# and trigobj.owner# = trigusr.user# and bitand(t.property, 63) < 8 )) where table_owner=upper('&username') order by owner, trigger_name;
六. Dblink
导出对象
Sys.link$ sys.user$
执行查询sql
SELECT 'CREATE '||DECODE(U.NAME,'PUBLIC','public ')||'DATABASE LINK '||CHR(10) ||DECODE(U.NAME,'PUBLIC',Null, 'SYS','',U.NAME||'.')|| L.NAME||chr(10) ||'CONNECT TO ' || L.USERID || ' IDENTIFIED BY "'||L.PASSWORD||'" USING '''||L.HOST||'''' ||chr(10)||';' TEXT FROM SYS.LINK$ L, SYS.USER$ U WHERE L.OWNER# = U.USER#;
11.2.0.3 adg库因 bug 16427872 导致smon占用大量cpu
检查数据库发现客户有一套核心的ADG库smon进程负载异常,单进程一直持有cpu 100%
[oracle@q9adg01 trace]$ top -c top - 14:00:14 up 83 days, 21:39, 4 users, load average: 10.34, 11.55, 11.25 Tasks: 1162 total, 3 running, 1157 sleeping, 0 stopped, 2 zombie Cpu(s): 1.7%us, 1.2%sy, 0.0%ni, 86.2%id, 10.7%wa, 0.0%hi, 0.1%si, 0.0%st Mem: 264253752k total, 200445076k used, 63808676k free, 757684k buffers Swap: 33554424k total, 0k used, 33554424k free, 6529220k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 5707 oracle 25 0 150g 20m 16m R 99.9 0.0 14273:00 ora_smon_q9db1 5285 oracle 16 0 13564 1952 820 R 31.5 0.0 0:02.49 top -c 5713 oracle 18 0 150g 20m 17m S 5.3 0.0 410:01.33 ora_asmb_q9db1 5821 oracle 15 0 150g 23m 17m S 5.3 0.0 4883:29 ora_lck0_q9db1 7596 oracle 15 0 150g 69m 37m S 5.3 0.0 5368:28 ora_pr00_q9db1 [oracle@q9adg02 ~]$ top -c top - 14:00:03 up 84 days, 19:36, 3 users, load average: 6.46, 6.96, 6.76 Tasks: 1045 total, 5 running, 1040 sleeping, 0 stopped, 0 zombie Cpu(s): 1.8%us, 1.0%sy, 0.0%ni, 93.4%id, 3.7%wa, 0.0%hi, 0.1%si, 0.0%st Mem: 264253752k total, 196879216k used, 67374536k free, 425320k buffers Swap: 33554424k total, 0k used, 33554424k free, 4727836k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 11615 oracle 25 0 150g 16m 14m R 100.0 0.0 14272:55 ora_smon_q9db2 18173 oracle 16 0 150g 73m 37m D 18.6 0.0 24:33.91 oracleq9db2 (LOCAL=NO) 6561 oracle 15 0 150g 31m 25m R 12.2 0.0 0:48.50 oracleq9db2 (LOCAL=NO)
数据库版本和patch信息
14:18:05 sys@Q9DB>select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production TNS for Linux: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production SQL> SELECT INST_ID,DATABASE_ROLE,OPEN_MODE FROM GV$DATABASE; INST_ID DATABASE_ROLE OPEN_MODE ---------- ---------------- -------------------- 2 PHYSICAL STANDBY READ ONLY WITH APPLY 1 PHYSICAL STANDBY READ ONLY WITH APPLY SQL >select inst_id,STARTUP_TIME from gv$instance; INST_ID STARTUP_T ---------- --------- 2 01-NOV-13 1 01-NOV-13 [oracle@q9adg01 trace]$ /u01/app/oracle/product/11.2.0/db_1/OPatch/opatch lspatches 16056266;Database Patch Set Update : 11.2.0.3.6 (16056266) 16315641;Grid Infrastructure Patch Set Update : 11.2.0.3.6 (16083653)
SYSAUX表空间增加数据文件
SQL> select ts# from v$tablespace where name='SYSAUX'; TS# ---------- 2 SQL> select file#,name,creation_time from v$datafile where ts#=2; FILE# NAME CREATION_ ---------- -------------------------------------------------- --------- 3 +DATA/q9db/datafile/sysaux.1412.818566605 12-MAR-08 151 +DATA/q9db/datafile/sysaux.1431.818566885 26-MAR-12 221 +DATA/q9db/datafile/sysaux.828.818547945 16-APR-12 1744 +DATA/q9db_adg/datafile/sysaux.2050.835459505 29-DEC-13
核对数据库确实在2013年12月29日对SYSAUX表空间增加了数据文件而且未重启数据库,触发Bug 16427872 Standby SMON spins on CPU after add/drop SYSAUX datafile on primary
Bug 16427872 Standby SMON spins on CPU after add/drop SYSAUX datafile on primary
在12.1.0.1中修复,在未修复前增加/删除sysaux的数据文件后,通过重启实例来解决该问题