故障处理：SYS用户所有序列删除的恢复

我们的文章会在微信公众号IT民工的龙马人生和博客网站( www.htz.pw )同步更新，欢迎关注收藏，也欢迎大家转载，但是请在文章开始地方标注文章出处，谢谢！
由于博客中有大量代码，通过页面浏览效果更佳。

SYS用户所有序列删除的恢复

下面的案例来至于一位朋友，不小心将sys用户下面的所有的sequence全部删除了。下面测试仅限测试环境，如在生产环境，请提前备份。整个实验利用数据库的闪回查询功能使用，其它如果undo中数据不存在，那么我们也可以利用软件来抽取表已经delete的行记录，前提是块中空间没有被覆盖。

1，数据库版本与OS版本

www.htz.pw > !lsb_release -a

LSB Version:    :core-3.0-amd64:core-3.0-ia32:core-3.0-noarch:graphics-3.0-amd64:graphics-3.0-ia32:graphics-3.0-noarch

Distributor ID: RedHatEnterpriseAS

Description:    Red Hat Enterprise Linux AS release 4 (Nahant Update 8)

Release:        4

Codename:       NahantUpdate8

www.htz.pw > select * from v$version where rownum<2;

BANNER

——————————————————————————–

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

2，DROP用户SYS序列

select ‘drop sequence ‘||sequence_owner||’.’||sequence_name||’;’ from dba_sequences where sequence_owner=’SYS’;


drop sequence SYS.APPLY$_DEST_OBJ_ID;

drop sequence SYS.APPLY$_ERROR_HANDLER_SEQUENCE;

drop sequence SYS.APPLY$_SOURCE_OBJ_ID;

drop sequence SYS.AQ$_ALERT_QT_N;

drop sequence SYS.AQ$_AQ$_MEM_MC_N;

drop sequence SYS.AQ$_AQ_PROP_TABLE_N;

drop sequence SYS.AQ$_CHAINSEQ;

drop sequence SYS.AQ$_IOTENQTXID;

drop sequence SYS.AQ$_KUPC$DATAPUMP_QUETAB_1_N;

drop sequence SYS.AQ$_KUPC$DATAPUMP_QUETAB_N;

drop sequence SYS.AQ$_NONDURSUB_SEQUENCE;

drop sequence SYS.AQ$_PROPAGATION_SEQUENCE;

drop sequence SYS.AQ$_PUBLISHER_SEQUENCE;

drop sequence SYS.AQ$_RULE_SEQUENCE;

drop sequence SYS.AQ$_RULE_SET_SEQUENCE;

drop sequence SYS.AQ$_SCHEDULER$_EVENT_QTAB_N;

drop sequence SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_N;

drop sequence SYS.AQ$_SCHEDULER_FILEWATCHER_QT_N;

drop sequence SYS.AQ$_SYS$SERVICE_METRICS_TAB_N;

drop sequence SYS.AQ$_TRANS_SEQUENCE;

drop sequence SYS.AUDSES$;

drop sequence SYS.AWCREATE10G_S$;

drop sequence SYS.AWCREATE_S$;

drop sequence SYS.AWLOGSEQ$;

drop sequence SYS.AWMD_S$;

drop sequence SYS.AWREPORT_S$;

drop sequence SYS.AWSEQ$;

drop sequence SYS.AWXML_S$;

drop sequence SYS.CACHE_STATS_SEQ_0;

drop sequence SYS.CACHE_STATS_SEQ_1;

drop sequence SYS.CDC_RSID_SEQ$;

drop sequence SYS.CDC_SUBSCRIBE_SEQ$;

drop sequence SYS.CHNF$_CLAUSEID_SEQ;

drop sequence SYS.CHNF$_QUERYID_SEQ;

drop sequence SYS.COMPARISON_SCAN_SEQ$;

drop sequence SYS.COMPARISON_SEQ$;

drop sequence SYS.CONFLICT_HANDLER_ID_SEQ$;

drop sequence SYS.DAM_CLEANUP_SEQ$;

drop sequence SYS.DBFS_HS$_ARCHIVEREFIDSEQ;

drop sequence SYS.DBFS_HS$_BACKUPFILEIDSEQ;

drop sequence SYS.DBFS_HS$_POLICYIDSEQ;

drop sequence SYS.DBFS_HS$_RSEQ;

drop sequence SYS.DBFS_HS$_STOREIDSEQ;

drop sequence SYS.DBFS_HS$_TARBALLSEQ;

drop sequence SYS.DBFS_SFS$_FSSEQ;

drop sequence SYS.DBMS_CUBE_ADVICE_SEQ$;

drop sequence SYS.DBMS_LOCK_ID;

drop sequence SYS.DBMS_PARALLEL_EXECUTE_SEQ$;

drop sequence SYS.DEPTREE_SEQ;

drop sequence SYS.DM$EXPIMP_ID_SEQ;

drop sequence SYS.EXPRESS_S$;

drop sequence SYS.FGR$_NAMES_S;

drop sequence SYS.GENERATOR$_S;

drop sequence SYS.GROUP_NUM_SEQ;

drop sequence SYS.HS$_BASE_DD_S;

drop sequence SYS.HS$_CLASS_CAPS_S;

drop sequence SYS.HS$_CLASS_DD_S;

drop sequence SYS.HS$_CLASS_INIT_S;

drop sequence SYS.HS$_FDS_CLASS_S;

drop sequence SYS.HS$_FDS_INST_S;

drop sequence SYS.HS$_INST_CAPS_S;

drop sequence SYS.HS$_INST_DD_S;

drop sequence SYS.HS$_INST_INIT_S;

drop sequence SYS.HS_BULK_SEQ;

drop sequence SYS.IDGEN1$;

drop sequence SYS.IDX_RB$JOBSEQ;

drop sequence SYS.INVALIDATION_REG_ID$;

drop sequence SYS.JAVA$POLICY$SEQUENCE$;

drop sequence SYS.JAVA$PREFS$SEQ$;

drop sequence SYS.JOBSEQ;

drop sequence SYS.JOBSEQLSBY;

drop sequence SYS.LOG$SEQUENCE;

drop sequence SYS.MV_RF$JOBSEQ;

drop sequence SYS.OBJECT_GRANT;

drop sequence SYS.OLAP_ASSIGNMENTS_SEQ;

drop sequence SYS.OLAP_ATTRIBUTES_SEQ;

drop sequence SYS.OLAP_CALCULATED_MEMBERS_SEQ;

drop sequence SYS.OLAP_DIMENSIONALITY_SEQ;

drop sequence SYS.OLAP_DIM_LEVELS_SEQ;

drop sequence SYS.OLAP_HIERARCHIES_SEQ;

drop sequence SYS.OLAP_HIER_LEVELS_SEQ;

drop sequence SYS.OLAP_MAPPINGS_SEQ;

drop sequence SYS.OLAP_MEASURES_SEQ;

drop sequence SYS.OLAP_MODELS_SEQ;

drop sequence SYS.ORA_PLAN_ID_SEQ$;

drop sequence SYS.ORA_TQ_BASE$;

drop sequence SYS.PARTITION_NAME$;

drop sequence SYS.PROFNUM$;

drop sequence SYS.PSINDEX_SEQ$;

drop sequence SYS.REDEF_SEQ$;

drop sequence SYS.RGROUPSEQ;

drop sequence SYS.SCHEDULER$_EVTSEQ;

drop sequence SYS.SCHEDULER$_INSTANCE_S;

drop sequence SYS.SCHEDULER$_JOBSUFFIX_S;

drop sequence SYS.SCHEDULER$_LWJOB_OID_SEQ;

drop sequence SYS.SCHEDULER$_RDB_SEQ;

drop sequence SYS.SNAPSHOT_ID$;

drop sequence SYS.SNAPSITE_ID$;

drop sequence SYS.SQLLOG$_SEQ;

drop sequence SYS.SQL_TK_CHK_ID;

drop sequence SYS.SSCR_CAP_SEQ$;

drop sequence SYS.STREAMS$_APPLY_SPILL_TXNKEY_S;

drop sequence SYS.STREAMS$_CAPTURE_INST;

drop sequence SYS.STREAMS$_CAP_SUB_INST;

drop sequence SYS.STREAMS$_PROPAGATION_SEQNUM;

drop sequence SYS.STREAMS$_RULE_NAME_S;

drop sequence SYS.STREAMS$_SM_ID;

drop sequence SYS.STREAMS$_STMT_HANDLER_SEQ;

drop sequence SYS.SYNOPSIS_NUM_SEQ;

drop sequence SYS.SYSTEM_GRANT;

drop sequence SYS.TSM_MIG_SEQ$;

drop sequence SYS.UGROUP_SEQUENCE;

drop sequence SYS.UTL_RECOMP_SEQ;

drop sequence SYS.WRI$_ADV_SEQ_DIR;

drop sequence SYS.WRI$_ADV_SEQ_DIR_INST;

drop sequence SYS.WRI$_ADV_SEQ_EXEC;

drop sequence SYS.WRI$_ADV_SEQ_JOURNAL;

drop sequence SYS.WRI$_ADV_SEQ_MSGGROUP;

drop sequence SYS.WRI$_ADV_SEQ_SQLW_QUERY;

drop sequence SYS.WRI$_ADV_SEQ_TASK;

drop sequence SYS.WRI$_ADV_SQLT_PLAN_SEQ;

drop sequence SYS.WRI$_ALERT_SEQUENCE;

drop sequence SYS.WRI$_ALERT_THRSLOG_SEQUENCE;

drop sequence SYS.WRI$_REPT_COMP_ID_SEQ;

drop sequence SYS.WRI$_REPT_FILE_ID_SEQ;

drop sequence SYS.WRI$_REPT_FORMAT_ID_SEQ;

drop sequence SYS.WRI$_REPT_REPT_ID_SEQ;

drop sequence SYS.WRI$_SQLSET_ID_SEQ;

drop sequence SYS.WRI$_SQLSET_REF_ID_SEQ;

drop sequence SYS.WRI$_SQLSET_STMT_ID_SEQ;

drop sequence SYS.WRI$_SQLSET_WORKSPACE_PLAN_SEQ;

drop sequence SYS.WRM$_DEEP_PURGE_EXTENT;

drop sequence SYS.WRM$_DEEP_PURGE_INTERVAL;

drop sequence SYS.WRR$_CAPTURE_ID;

drop sequence SYS.WRR$_REPLAY_ID;

drop sequence SYS.XSPARAM_REG_SEQUENCE$;

3，恢复序列

3.1 生成dba_sequences的DDL语句

平时查询序列的时候都是查询dba_sequences这个视图，下面来看看dba_sequences由那几张底层表构成。

  CREATE OR REPLACE FORCE VIEW "SYS"."DBA_SEQUENCES" ("SEQUENCE_OWNER", "SEQUENCE_NAME", "MIN_VALUE", "MAX_VALUE", "INCREMENT_BY", "CYCLE_FLAG", "ORDER_FLAG", "CACHE_SIZE", "LAST_NUMBER") AS

  select u.name, o.name,

      s.minvalue, s.maxvalue, s.increment$,

      decode (s.cycle#, 0, ‘N’, 1, ‘Y’),

      decode (s.order$, 0, ‘N’, 1, ‘Y’),

      s.cache, s.highwater

from sys.seq$ s, sys.obj$ o, sys.user$ u

where u.user# = o.owner#

  and o.obj# = s.obj#

这里看到由seq$,obj$,user$这几张表构成。

3.2 闪回查询delete数据

下面利用闪回查询来查找已经delete的数据

www.htz.pw > create table scott.seq as select * from seq$  as of timestamp to_timestamp(‘2014-08-21 11:31:57′,’YYYY-MM-DD HH24:MI:SS’);


Table created.

www.htz.pw > create table scott.obj as select * from obj$  as of timestamp to_timestamp(‘2014-08-21 11:31:57′,’YYYY-MM-DD HH24:MI:SS’);

Table created.

users这张表是可心不需要的

www.htz.pw > create table scott.users as select * from user$  as of timestamp to_timestamp(‘2014-08-21 11:31:57′,’YYYY-MM-DD HH24:MI:SS’);


Table created.

3.3 插入已经删除的值

下面的两条SQL写得性能不高，如果大量数据，可以使用merge来改写，性能高一些。

www.htz.pw > insert into sys.seq$

  2    select *

  3      from scott.seq b

  4     where b.obj# in (select s.obj#

                      from scott.seq s, scott.obj o, scott.users u

  6                       where u.user# = o.owner#

  7                         and o.obj# = s.obj#

  8                         and u.name = ‘SYS’)

  9  ;

 

136 rows created.

 

insert into sys.obj$

  2    select *

  3      from scott.obj b

  4     where b.obj# in (select s.obj#

                      from scott.seq s, scott.obj o, scott.users u

  6                       where u.user# = o.owner#

  7                         and o.obj# = s.obj#

  8                         and u.name = ‘SYS’)

  9  ;


136 rows created.

www.htz.pw > select count(*) from seq$;

3.4 重新运行建库脚本

@?/rdbms/admin/catalog.sql

运行过程中会报很多错误，原因是由于sequence不存在，可心不用管

@?/rdbms/admin/catproc.sql

@?/sqlplus/admin/pupbld.sql


www.htz.pw > @?/rdbms/admin/utlrp.sql

www.htz.pw > Rem

无效对象

www.htz.pw > select status,count(*) from dba_objects group by status;


STATUS    COUNT(*)

——- ———-

VALID        74600

3.5 重启数据库，功能测试

www.htz.pw > startup force;

ORACLE instance started.


Total System Global Area  839282688 bytes

Fixed Size                  2233000 bytes

Variable Size             641731928 bytes

Database Buffers          192937984 bytes

Redo Buffers                2379776 bytes

Database mounted.

Database opened.

------------------作者介绍-----------------------
姓名：黄廷忠
现就职：Oracle中国高级服务团队
曾就职：OceanBase、云和恩墨、东方龙马等
电话、微信、QQ：18081072613
个人博客: (http://www.htz.pw)
CSDN地址: (https://blog.csdn.net/wwwhtzpw)
博客园地址: (https://www.cnblogs.com/www-htz-pw)

posted @ 2025-08-08 21:19 认真就输阅读(16) 评论(0) 收藏举报

刷新页面返回顶部

故障处理：SYS用户所有序列删除的恢复

SYS用户所有序列删除的恢复

公告