[20181031]truncate IDL_UB1$恢复.txt

[20181031]truncate IDL_UB1$恢复.txt

--//参考链接: http://www.ludatou.com/?p=2949 =>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下应用日志没有问题.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2218296/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-2218296/