首页 » ORACLE 9i-23c » 如何恢复Truncate sys.IDL_UB1$？

如何恢复Truncate sys.IDL_UB1$？

  • 2023/01/06
  • ORACLE 9i-23c
  • 237 views
  • 如何恢复Truncate sys.IDL_UB1$？已关闭评论

近日一客户咨询truncate table sys.IDL_UB1$如何恢复,  为什么会有这样的需求？后来我在测试统计了一下system表空间的TOP 10的大对象，它确实在TOP 1。猜想应该是看到system表空间使用率高， 也未评估IDL_UB1$表的重要性，直接truncate释放了空间。 然后数据crash无法再启动。我找了个19c PDB测试库测试一下如何恢复？不要在生产库测试，也不要轻易删sys Schema下的对象。

测试Truncate sys.IDL_UB1$

SQL> conn / as sysdba
SELECT *
  FROM (SELECT   BYTES, segment_name, segment_type, owner
            FROM dba_segments
           WHERE tablespace_name = 'SYSTEM'
        ORDER BY BYTES DESC)
 WHERE ROWNUM < 10
  7  /

     BYTES SEGMENT_NAME              SEGMENT_TYPE       OWNER
---------- ------------------------- ------------------ ----------
  75497472 IDL_UB1$                  TABLE              SYS
  47185920 IDX_G_DEPTNO              INDEX              SYS
  41943040 TESTREUSE_BASICFILE       TABLE              SYS
  39845888 C_TOID_VERSION#           CLUSTER            SYS
  20971520 Q1_SALES_BY_REGION        TABLE PARTITION    SYS
  15728640 IDL_UB2$                  TABLE              SYS
  15728640 IDX_L_DEPTNO              INDEX PARTITION    SYS
  14680064 C_OBJ#                    CLUSTER            SYS
  12582912 C_OBJ#_INTCOL#            CLUSTER            SYS

SQL> @cc pdbanbob
ALTER SESSION SET container = pdbanbob;

--创建个测试PLSQL对象后期验证是否存在
SQL>create procedure P_TEST is
begin
null;
end;
/


SQL> truncate table IDL_UB1$;

SQL> shu immediate;
Pluggable Database closed.

SQL> startup
ORA-03113: end-of-file on communication channel
Process ID: 6350
Session ID: 72 Serial number: 22933

SQL跟踪

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDBANBOB                       MOUNTED
		 
SQL> @46on 12

SQL> alter pluggable database pdbanbob open;
ERROR:
ORA-03113: end-of-file on communication channel
Process ID: 6561
Session ID: 75 Serial number: 30227

trace文件内容

EXEC #140174721207048:c=2119,e=2919,p=0,cr=17,cu=0,mis=1,r=0,dep=2,og=4,plh=2317816222,tim=4781429546
WAIT #140174721207048: nam='db file sequential read' ela= 565 file#=196 block#=2849 blocks=1 obj#=382 tim=4781430173 
WAIT #140174721207048: nam='db file sequential read' ela= 378 file#=196 block#=2850 blocks=1 obj#=382 tim=4781430638
WAIT #140174721207048: nam='db file sequential read' ela= 772 file#=196 block#=2842 blocks=1 obj#=371 tim=4781431453
WAIT #140174721207048: nam='db file sequential read' ela= 569 file#=196 block#=2841 blocks=1 obj#=371 tim=4781432146  
FETCH #140174721207048:c=1474,e=2647,p=4,cr=5,cu=0,mis=0,r=1,dep=2,og=4,plh=2317816222,tim=4781432230
FETCH #140174721207048:c=16,e=16,p=0,cr=3,cu=0,mis=0,r=1,dep=2,og=4,plh=2317816222,tim=4781432419
WAIT #140174721207048: nam='db file sequential read' ela= 420 file#=196 block#=2843 blocks=1 obj#=371 tim=4781432897   
FETCH #140174721207048:c=497,e=497,p=1,cr=3,cu=0,mis=0,r=1,dep=2,og=4,plh=2317816222,tim=4781432960
FETCH #140174721207048:c=4,e=4,p=0,cr=1,cu=0,mis=0,r=0,dep=2,og=4,plh=2317816222,tim=4781433004
STAT #140174721207048 id=1 cnt=3 pid=0 pos=1 obj=371 op='TABLE ACCESS BY INDEX ROWID IDL_UB2$ (cr=8 pr=4 pw=0 str=1 time=1970 us cost=3 size=42 card=2)'   
STAT #140174721207048 id=2 cnt=3 pid=1 pos=1 obj=382 op='INDEX RANGE SCAN I_IDL_UB21 (cr=5 pr=2 pw=0 str=1 time=1102 us cost=2 size=0 card=2)'
CLOSE #140174721207048:c=7,e=8,dep=2,type=0,tim=4781433119

*** 2023-01-06T16:46:46.059159+08:00 (PDBANBOB(4))
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x18] [PC:0x5208A2D, hshget1()+173] [flags: 0x0, count: 1]
2023-01-06T16:46:46.163751+08:00
Incident 823613 created, dump file: /u01/app/oracle/diag/rdbms/anbob19c/anbob19c/incident/incdir_823613/anbob19c_ora_6561_i823613.trc
ORA-07445: exception encountered: core dump [hshget1()+173] [SIGSEGV] [ADDR:0x18] [PC:0x5208A2D] [Address not mapped to object] []


SQL> select ctime, mtime, stime ,name from obj$ where obj#=371;
CTIME               MTIME               STIME               NAME
------------------- ------------------- ------------------- --------------------------- 
2019-04-17 00:56:27 2019-04-17 00:56:27 2019-04-17 00:56:27 IDL_UB1$

SQL> select ctime, mtime, stime ,name from obj$ where obj#=382;

CTIME               MTIME               STIME               NAME
------------------- ------------------- ------------------- --------------------------- 
2019-04-17 00:56:27 2019-04-17 00:56:27 2019-04-17 00:56:27 I_IDL_UB11

1 row selected.

恢复

SQL> @cc pdbanbob
ALTER SESSION SET container = pdbanbob;

Session altered.


USERNAME             INST_NAME            HOST_NAME                  I# SID   SERIAL#  VERSION    STARTED  SPID       OPID  CPID            SADDR            PADDR
-------------------- -------------------- ------------------------- --- ----- -------- ---------- -------- ---------- ----- --------------- ---------------- ----------------
SYS                  PDBANBOB-anbob19c    oel7db1                     1 76    6580     19.0.0.0.0 20230106 19287      53    6349            000000007713C120 0000000077D12AC8


SQL> alter database open upgrade;
Database altered.

SQL>  truncate table idl_ub1$;
Table truncated.

SQL>  truncate table idl_char$;
Table truncated.

SQL> truncate table idl_ub2$;
Table truncated.

SQL> truncate table idl_sb4$;
Table truncated.

SQL> spool xxx.log
SQL> @$ORACLE_HOME/rdbms/admin/utlirp.sql

SQL> DOC
DOC>#######################################################################
DOC>#######################################################################
DOC>   utlirp.sql completed successfully. All PL/SQL objects in the
DOC>   database have been invalidated.
DOC>
DOC>   Shut down and restart the database in normal mode and run utlrp.sql to
DOC>   recompile invalid objects.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SQL> select status,count(*) from dba_objects group by status;

STATUS    COUNT(*)
------- ----------
VALID        62773
INVALID       9584

SQL> shutdown immediate
Pluggable Database closed.
SQL>  alter database open upgrade;
Database altered.


SYS@book> spool yyy.out
SYS@book> @$ORACLE_HOME/javavm/install/rmjvm.sql

SQL> select status,count(*) from dba_objects group by status;

STATUS    COUNT(*)
------- ----------
VALID        62858
INVALID       9286


SQL> shutdown immediate     ---- ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE
Pluggable Database closed.

SQL> shutdown immediate
Pluggable Database closed.
SQL> alter pluggable database pdbanbob open;
Pluggable database altered.

SQL> shutdown immediate
Pluggable Database closed.


SQL> alter database open upgrade;
Database altered.

SQL> alter system set "_system_trig_enabled"=false scope=memory;
System altered.

SQL> show parameter java_p
PARAMETER_NAME                                               TYPE        VALUE
------------------------------------------------------------ ----------- -------------------------
java_pool_size                                               big integer 100M

SQL> @$ORACLE_HOME/javavm/install/initjvm.sql

-- if you have xml installed
SQL> @$ORACLE_HOME/xdk/admin/initxml.sql
SQL> @$ORACLE_HOME/xdk/admin/xmlja.sql
SQL> @$ORACLE_HOME/rdbms/admin/catjava.sql

SQL> shutdown immediate
Pluggable Database closed.

SQL> alter database open upgrade;

Database altered.

SQL>@$ORACLE_HOME/rdbms/admin/utlrp

---
/*
SQL> DECLARE
  2     threads pls_integer := &&1;
  3  BEGIN
  4     utl_recomp.recomp_parallel(threads);
  5  END;
  6  /
  
SQL>
SQL> Rem =====================================================================
SQL> Rem Run component validation procedure
SQL> Rem =====================================================================
SQL>
SQL> SET serveroutput on
SQL> EXECUTE dbms_registry_sys.validate_components;

PL/SQL procedure successfully completed.

SQL> SET serveroutput off
SQL>
SQL>
SQL> Rem ===========================================================================
SQL> Rem END utlrp.sql
SQL> Rem ===========================================================================
SQL>

*/

SQL> select status,count(*) from dba_objects group by status;

STATUS    COUNT(*)
------- ----------
VALID        72358

1 row selected.

SQL> shutdown immediate ;
Pluggable Database closed.

SQL>  alter pluggable database pdbanbob open;
Pluggable database altered.

验证

SQL> 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
---------- -------------- ------------------------------
       370          73010 IDL_CHAR$
       372          73014 IDL_SB4$
       369          73005 IDL_UB1$
       371          73012 IDL_UB2$

SQL> select count(*) from IDL_UB1$ ;
  COUNT(*)
----------
      6905

1 row selected.

SQL> @o p_test

owner                     object_name                    subname                        object_type          status           OID      D_OID CREATED             LAST_DDL_TIME
------------------------- ------------------------------ ------------------------------ -------------------- --------- ---------- ---------- ------------------- -------------------
SYS                       P_TEST                                                        PROCEDURE            VALID          73003            2023-01-06 16:43:27 2023-01-06 21:24:39

1 row selected.

打赏

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：