认真就输

下面的测试来至于一位朋友，不小心将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由那几张底层表构成。

这里看到由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.

测试这里就不写了

整个实现结束