首页 » ORACLE 9i-23ai » 如何恢复Truncate sys.IDL_UB1$？
如何恢复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.
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。