当前位置: 首页 > BACKUP & RESTORE, BASIC > 正文

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

2DROP用户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_sequencesDDL语句

平时查询序列的时候都是查询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.

测试这里就不写了

 

整个实现结束

本文固定链接: http://www.htz.pw/2014/08/21/sys%e7%94%a8%e6%88%b7%e6%89%80%e6%9c%89%e5%ba%8f%e5%88%97%e5%88%a0%e9%99%a4%e7%9a%84%e6%81%a2%e5%a4%8d.html | 认真就输

该日志由 huangtingzhong 于2014年08月21日发表在 BACKUP & RESTORE, BASIC 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: SYS用户所有序列删除的恢复 | 认真就输
关键字: ,