本文共 9846 字，大约阅读时间需要 32 分钟。
20181031]truncate IDL_UB1$恢复.txt
--//参考链接: =>How to recreate idl_ub1$,idl_char$,idl_ub2$,idl_sb4$.好像对方恢复环境是 --//windows系统. --//自己在测试环境重复演示看看,千万不要在生产系统做这个测试!! --//idl_ub1$表是用来存储PL/SQL的代码单元的，包括DIANA等，IDL在这里代表Interface Definition Language. 1.环境: SCOTT@book> @ ver1 PORT_STRING VERSION BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production SYS@book> select object_id,data_object_id ,object_name from dba_objects where owner='SYS' and object_name='IDL_UB1$'; OBJECT_ID DATA_OBJECT_ID OBJECT_NAME ---------- -------------- -------------------- 225 225 IDL_UB1$ SYS@book> truncate table idl_ub1$; truncate table idl_ub1$ * ERROR at line 1: ORA-03113: end-of-file on communication channel Process ID: 29521 Session ID: 274 Serial number: 251 --//建立一些事务,主要模拟接近真实环境.实际上我的测试根本不行. SCOTT@book> create table empy as select * from emp; create table empy as select * from emp * ERROR at line 1: ORA-03113: end-of-file on communication channel Process ID: 29532 Session ID: 274 Serial number: 253 ---//选择abort 关闭数据库.增加恢复难度. SYS@book> shutdown abort ; ORACLE instance shut down. 2.通过10046跟踪可以发现如下: SYS@book> startup mount ; ORACLE instance started. Total System Global Area 634732544 bytes Fixed Size 2255792 bytes Variable Size 197133392 bytes Database Buffers 427819008 bytes Redo Buffers 7524352 bytes Database mounted. @ 10046on 12 alter database open ; --//我的测试就停止在那里,无法进行,根本没有提示.也无法判断问题在那里. @ 10046on off --//打开另外会话: SYS@book> @ &r/wait P1RAW P2RAW P3RAW P1 P2 P3 SID SERIAL# SEQ# EVENT STATUS STATE WAIT_TIME_MICRO SECONDS_IN_WAIT WAIT_CLASS ---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ----------------------- -------- ------------------- --------------- --------------- -------------------- 0000000000000001 00000000000005FB 0000000000000001 1 1531 1 274 3 1037 db file sequential read ACTIVE WAITED SHORT TIME 5 95 User I/O --//也就是停在dba=1,1531的位置.找另外1台机器检查发现: SYS@dbcn1> @ &r/which_obj 1 1531 OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO ------ ------------ -------------- ------------ --------------- --------- ---------- ---------- ---------- ---------- ------------ SYS IDL_UB2$ TABLE SYSTEM 0 1 1528 65536 8 1 --//并没有停在IDL_UB1$. 3.开始尝试恢复: --//以upgrade方式打开数据库，并截断相关表 SYS@book> startup mount; ORACLE instance started. Total System Global Area 634732544 bytes Fixed Size 2255792 bytes Variable Size 197133392 bytes Database Buffers 427819008 bytes Redo Buffers 7524352 bytes Database mounted. SYS@book> alter database open upgrade; Database altered. SYS@book> @ &r/which_obj 1 1531 OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO ------ -------------------- -------------- ------------ --------------- --------- ---------- ---------- ---------- ---------- ------------ SYS IDL_UB2$ TABLE SYSTEM 0 1 1528 65536 8 1 --//截断相关表 --//truncate table idl_ub1$; --//truncate table idl_char$; --//truncate table idl_ub2$; --//truncate table idl_sb4$; SYS@book> truncate table idl_ub1$; Table truncated. SYS@book> truncate table idl_char$; Table truncated. SYS@book> truncate table idl_ub2$; Table truncated. SYS@book> truncate table idl_sb4$; Table truncated. --//执行utlirp和rmjvm两个脚本，并关闭数据库 SYS@book> spool xxx.out SYS@book> @ $ORACLE_HOME/rdbms/admin/utlirp.sql .... SYS@book> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SYS@book> startup upgrade ORACLE instance started. Total System Global Area 634732544 bytes Fixed Size 2255792 bytes Variable Size 197133392 bytes Database Buffers 427819008 bytes Redo Buffers 7524352 bytes Database mounted. Database opened. SYS@book> spool yyy.out SYS@book> @ $ORACLE_HOME/javavm/install/rmjvm.sql SYS@book> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. --//再次启动数据库后，设置监听为临时不可用状态并依据数据库组件状态运行相关脚本 SYS@book> startup upgrade ORACLE instance started. Total System Global Area 634732544 bytes Fixed Size 2255792 bytes Variable Size 197133392 bytes Database Buffers 427819008 bytes Redo Buffers 7524352 bytes Database mounted. Database opened. SYS@book> alter system set "_system_trig_enabled"=false scope=memory; System altered. SYS@book> @ $ORACLE_HOME/javavm/install/initjvm.sql --//我的测试在执行过程中出现: ### ### Aborting because available java pool, 4194304, is less than 12000000 . ### . BEGIN initjvmaux.check_sizes_for_cjs; END; * ERROR at line 1: ORA-29554: unhandled Java out of memory condition ORA-06512: at "SYS.INITJVMAUX", line 208 ORA-06512: at line 1 SYS@book> show parameter java_pool_size NAME TYPE VALUE -------------- ----------- ----- java_pool_size big integer 4M --//估计太小. SYS@book> alter system set java_pool_size=12M scope=spfile; System altered. --//再次重复: startup upgrade alter system set "_system_trig_enabled"=false scope=memory; @ $ORACLE_HOME/javavm/install/initjvm.sql --// 如果xml已经安装执行此脚本,确定安装了那些组件执行: SYS@book> SELECT comp_id,schema,status,version,comp_name FROM dba_registry ORDER BY 1; COMP_ID SCHEMA STATUS VERSION COMP_NAME -------- ----------- ------- ------------ ---------------------------------- AMD OLAPSYS VALID 11.2.0.4.0 OLAP Catalog APEX APEX_030200 VALID 3.2.1.00.12 Oracle Application Express APS SYS VALID 11.2.0.4.0 OLAP Analytic Workspace CATALOG SYS VALID 11.2.0.4.0 Oracle Database Catalog Views CATJAVA SYS VALID 11.2.0.4.0 Oracle Database Java Packages CATPROC SYS VALID 11.2.0.4.0 Oracle Database Packages and Types CONTEXT CTXSYS VALID 11.2.0.4.0 Oracle Text EM SYSMAN VALID 11.2.0.4.0 Oracle Enterprise Manager EXF EXFSYS VALID 11.2.0.4.0 Oracle Expression Filter JAVAVM SYS VALID 11.2.0.4.0 JServer JAVA Virtual Machine ORDIM ORDSYS VALID 11.2.0.4.0 Oracle Multimedia OWB OWBSYS VALID 11.2.0.4.0 OWB OWM WMSYS VALID 11.2.0.4.0 Oracle Workspace Manager RUL EXFSYS VALID 11.2.0.4.0 Oracle Rules Manager SDO MDSYS VALID 11.2.0.4.0 Spatial XDB XDB VALID 11.2.0.4.0 Oracle XML Database XML SYS VALID 11.2.0.4.0 Oracle XDK XOQ SYS VALID 11.2.0.4.0 Oracle OLAP API 18 rows selected. --//我看到的ok的,这样下面的脚本应该不用执行. --//@ $ORACLE_HOME/xdk/admin/initxml.sql --//@ $ORACLE_HOME/xdk/admin/xmlja.sql --//@ $ORACLE_HOME/rdbms/admin/catjava.sql SYS@book> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. --//启动数据库并执行ultrp修复失效对象，运行编译修复后，修复完成。 startup upgrade @ $ORACLE_HOME/rdbms/admin/utlrp SYS@book> shutdown immediate ; Database closed. Database dismounted. ORACLE instance shut down. SYS@book> startup ORACLE instance started. Total System Global Area 643084288 bytes Fixed Size 2255872 bytes Variable Size 205521920 bytes Database Buffers 427819008 bytes Redo Buffers 7487488 bytes Database mounted. Database opened. --//我下面的步骤没有执行: --//SQLPLUS / AS SYSDBA --//shutdown immediate --//startup --//execute utl_recomp.recomp_serial(); --//exit --// SYS@book> select object_id,data_object_id ,object_name from dba_objects where owner='SYS' and object_name in ('IDL_UB1$','IDL_CHAR$','IDL_UB2$','IDL_SB4$'); OBJECT_ID DATA_OBJECT_ID OBJECT_NAME ---------- -------------- -------------------- 226 90861 IDL_CHAR$ 228 90865 IDL_SB4$ 225 90857 IDL_UB1$ 227 90863 IDL_UB2$ --//不过我的dg破坏了,仅仅停在truncate时的状态. Started Parallel Media Recovery *** 2018-10-31 17:02:29.239 4329 krsh.c Managed Standby Recovery starting Real Time Apply *** 2018-10-31 17:02:29.347 Dumping database incarnation table: Resetlogs 0 scn and time: 0x0000.000e2006 11/24/2015 09:11:12 Recovery target incarnation = 2, activation ID = 1337448558 Influx buffer limit = 24990 min(50% x 49980, 100000) Start recovery at thread 1 ckpt scn 14982773558 logseq 811 block 2 Initial buffer sizes: read 1024K, overflow 832K, change 805K *** 2018-10-31 17:02:29.575 Media Recovery add redo thread 1 *** 2018-10-31 17:02:29.678 Media Recovery Log /u01/app/oracle/archivelog/book/1_811_896605872.dbf *** 2018-10-31 17:02:29.692 4329 krsh.c MRP0: Background Media Recovery terminated with error 10485 ORA-10485: Real-Time Query cannot be enabled while applying migration redo. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ *** 2018-10-31 17:02:29.693 4329 krsh.c Managed Standby Recovery not using Real Time Apply ----- Redo read statistics for thread 1 ----- Read rate (ASYNC): 1023Kb in 0.18s => 5.55 Mb/sec Total redo bytes: 1023Kb Longest LWN: 0Kb, reads: 1 ---------------------------------------------- SYS@bookdg> select object_id,data_object_id ,object_name from dba_objects where owner='SYS' and object_name in ('IDL_UB1$','IDL_CHAR$','IDL_UB2$','IDL_SB4$'); OBJECT_ID DATA_OBJECT_ID OBJECT_NAME ---------- -------------- -------------------- 226 226 IDL_CHAR$ 228 228 IDL_SB4$ 225 90857 IDL_UB1$ 227 227 IDL_UB2$ --//日志已经无法应用,只能在mount状态下应用. SYS@bookdg> shutdown immediate ; Database closed. Database dismounted. ORACLE instance shut down. SYS@bookdg> startup mount; ORACLE instance started. Total System Global Area 634732544 bytes Fixed Size 2255792 bytes Variable Size 197133392 bytes Database Buffers 427819008 bytes Redo Buffers 7524352 bytes Database mounted. SYS@bookdg> alter database recover managed standby database using current logfile disconnect ; Database altered. SYS@bookdg> @ &r/dg/dg PROCESS PID STATUS CLIENT_P GROUP# THREAD# SEQUENCE# BLOCK# BLOCKS DELAY_MINS --------- ------- ------------ -------- ------ ------- ---------- ---------- ---------- ---------- ARCH 26776 CONNECTED ARCH N/A 0 0 0 0 0 ARCH 26778 CONNECTED ARCH N/A 0 0 0 0 0 ARCH 26780 CONNECTED ARCH N/A 0 0 0 0 0 RFS 26801 IDLE ARCH N/A 0 0 0 0 0 RFS 26787 IDLE LGWR 2 1 852 36 1 0 ARCH 26782 CLOSING ARCH 4 1 851 1 324 0 MRP0 26789 APPLYING_LOG N/A N/A 1 812 45046 81243 0 7 rows selected. --//在mount下应用日志没有问题.转载地址：http://fssof.baihongyu.com/