sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

【案例】Oracle sys用户下面所有的sequence全部删除的恢复笔记

时间:2016-10-29 20:23   来源:Oracle研究中心   作者:HTZ   点击:

天萃荷净 Oracle研究中心案例分析:运维DBA反映在测试Oracle数据库时不小心删除了sys用户下面的所有的sequence造成数据库异常,通过Oracle数据库闪回查询功能恢复。
将sys用户下面的所有的sequence全部删除了。下面测试仅限测试环境,如在生产环境,请提前备份。整个实验利用数据库的闪回查询功能使用,其它如果undo中数据不存在,那么我们也可以利用软件来抽取表已经delete的行记录,前提是块中空间没有被覆盖。

1,数据库版本与OS版本

oracleplus.net> !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

oracleplus.net> 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;
dropOracleoracleplus.net 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的数据
oracleplus.net> 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.

oracleplus.net> 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这张表是可心不需要的
oracleplus.net> 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来改写,性能高一些。
oracleplus.net> 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.

oracleplus.net> select count(*) from seq$;

3.4 重新运行建库脚本
@ /rdbms/admin/catalog.sql
运行过程中会报很多错误,原因是由于sequence不存在,可心不用管

@ /rdbms/admin/catproc.sql
@ /sqlplus/admin/pupbld.sql

oracleplus.net> @ /rdbms/admin/utlrp.sql
oracleplus.net> Rem
无效对象

oracleplus.net> select status,count(*) from dba_objects group by status;

STATUS COUNT(*)
——- ———-
VALID 74600

3.5 重启数据库,功能测试
oracleplus.net> 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 | 认真就输

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle sys用户下面所有的sequence全部删除的恢复笔记

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1105.html

Oracle研究中心

关键词:

Oracle闪回查询功能测试笔记

Oracle数据库sequence删除恢复笔记

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