[20181031]truncate IDL_UB1$恢復.txt

[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下應用日誌沒有問題.