重建smon_scn_time表和索引

来源：互联网发布：高斯滤波算法matlab 编辑：程序博客网时间：2024/06/05 01:17

参考文章：http://www.cndba.cn/dave/article/1975；http://www.cndba.cn/dave/article/1285

smon_scan_time表介绍：scn和时间的对应关系表，由smon进程维护
scn：系统改变数字，保证数据一致性

一、创建SMON_SCN_TIME表

先对SMON_SCN_TIME进行查询，和表中的记录内同

SQL> desc smon_scn_timeName                       Null?    Type----------------------------------------- -------- ----------------------------THREAD                         NUMBERTIME_MP                        NUMBERTIME_DP                        DATESCN_WRP                        NUMBERSCN_BAS                        NUMBERNUM_MAPPINGS                        NUMBERTIM_SCN_MAP                        RAW(1200)SCN                            NUMBERORIG_THREAD                        NUMBERSQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';Session altered.SQL> select time_dp,scn from smon_scn_time where rownum<5;TIME_DP            SCN------------------- ----------2017-09-23 02:12:55    9899292017-09-23 02:17:44    9903222017-09-23 02:23:02    9904322017-09-23 02:27:44    990533

删除SMON_SCN_TIME表
此时查询表已经不存在

SQL> drop table smon_scn_time;Table dropped.SQL> select time_dp,scn from smon_scn_time where rownum<5;select time_dp,scn from smon_scn_time where rownum<5;                                      *ERROR at line 1:ORA-00942: table or view does not exist

查询SMON_SCN_TIME表创建语句（这里使用的是11g 11.2.04的环境）
SMON_SCN_TIME表重建语句在 $ORACLE_HOME/rdbms/admin/dtxnspc.bsq

remrem create the scn<->time tracking table that smon will maintainrem as a circular queue - notice that we populate the entirerem table with at least 144000 entries (enough for 5 days).remrem -"thread" is for backward compatibility and is always 0rem -"orig_thread" is for upgrade/downgraderem - scn_wrp, scn_bas, and time_dp are for backward compatibilityrem   and not queried by the ktf layer.rem Also create the props$ entry to indicate that the mapping is in GMT.remRem Add to props$ the flashback timestamp time zone information.Rem Both flashback timestamp and the timestamp in smon_scn_time table areRem now recorded in GMT.insert into props$ (name, value$, comment$)  values('Flashback Timestamp TimeZone', 'GMT',         'Flashback timestamp created in GMT');commit;/create cluster smon_scn_to_time_aux (  thread number                         /* thread, compatibility */) tablespace SYSAUX/create index smon_scn_to_time_aux_idx on cluster smon_scn_to_time_aux/-----create table smon_scn_time (  thread number,                         /* thread, compatibility */  time_mp number,                        /* time this recent scn represents */  time_dp date,                          /* time as date, compatibility */  scn_wrp number,                        /* scn.wrp, compatibility */  scn_bas number,                        /* scn.bas, compatibility */  num_mappings number,  tim_scn_map raw(1200),  scn number default 0,                  /* scn */  orig_thread number default 0           /* for downgrade */) cluster smon_scn_to_time_aux (thread)/create unique index smon_scn_time_tim_idx on smon_scn_time(time_mp)  tablespace SYSAUX/create unique index smon_scn_time_scn_idx on smon_scn_time(scn)  tablespace SYSAUX/

执行创建语句

create table smon_scn_time (  thread number,                         /* thread, compatibility */  time_mp number,                        /* time this recent scn represents */  time_dp date,                          /* time as date, compatibility */  scn_wrp number,                        /* scn.wrp, compatibility */  scn_bas number,                        /* scn.bas, compatibility */  num_mappings number,  tim_scn_map raw(1200),  scn number default 0,                  /* scn */  orig_thread number default 0           /* for downgrade */) cluster smon_scn_to_time_aux (thread)/create unique index smon_scn_time_tim_idx on smon_scn_time(time_mp)  tablespace SYSAUX/create unique index smon_scn_time_scn_idx on smon_scn_time(scn)  tablespace SYSAUX/

查询SMON_SCN_TIME状态

SQL> analyze table SMON_SCN_TIME validate structure cascade online;Table analyzed.SQL> select time_dp,scn from smon_scn_time where rownum<5;TIME_DP            SCN------------------- ----------2017-10-11 02:30:15    1125028      #(这里的时间和上面不一致是因为我的是虚拟机之前做实验恢复过，导致时间不连续)2017-10-11 02:35:30    1125305

二、创建SMON_SCN_TIME表索引

确认表上smon_scn_time是否有损坏

SQL> analyze table SMON_SCN_TIME validate structure;Table analyzed.

查询表上索引

SQL> set lines 120SQL> col index_name for a40SQL> col index_type for a20SQL> col owner for a20   SQL> select index_name,index_type,owner from dba_indexes where table_name='SMON_SCN_TIME';INDEX_NAME                 INDEX_TYPE          OWNER---------------------------------------- -------------------- --------------------SMON_SCN_TIME_SCN_IDX             NORMAL           SYSSMON_SCN_TIME_TIM_IDX             NORMAL           SYS

获取索引的DDL语句

SQL> select dbms_metadata.get_ddl('INDEX','SMON_SCN_TIME_TIM_IDX','SYS') FROM DUAL;DBMS_METADATA.GET_DDL('INDEX','SMON_SCN_TIME_TIM_IDX','SYS')--------------------------------------------------------------------------------  CREATE UNIQUE INDEX "SYS"."SMON_SCN_TIME_TIM_IDX" ON "SYS"."SMON_SCN_TIME" ("TIME_MP")  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)  TABLESPACE "SYSAUX"SQL> SELECT DBMS_METADATA.GET_DDL('INDEX','SMON_SCN_TIME_SCN_IDX','SYS') FROM DUAL;DBMS_METADATA.GET_DDL('INDEX','SMON_SCN_TIME_SCN_IDX','SYS')--------------------------------------------------------------------------------  CREATE UNIQUE INDEX "SYS"."SMON_SCN_TIME_SCN_IDX" ON "SYS"."SMON_SCN_TIME" ("SCN")  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)  TABLESPACE "SYSAUX"

删除索引

SQL> drop index sys.smon_scn_time_tim_idx;Index dropped.SQL> drop index sys.smon_scn_time_scn_idx;Index dropped.

验证SMON_SCN_TIME表的有效性

SQL> analyze table smon_scn_time validate structure cascade online;Table analyzed.

创建索引

SQL>   CREATE UNIQUE INDEX "SYS"."SMON_SCN_TIME_TIM_IDX" ON "SYS"."SMON_SCN_TIME" ("TIME_MP")  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)  TABLESPACE "SYSAUX" ;Index created.SQL>   CREATE UNIQUE INDEX "SYS"."SMON_SCN_TIME_SCN_IDX" ON "SYS"."SMON_SCN_TIME" ("SCN")  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)  TABLESPACE "SYSAUX" ;Index created.

验证SMON_SCN_TIME表状态

SQL> analyze table smon_scn_time validate structure cascade online;Table analyzed.

创建完成

三、设置12500事件停止SMON进程对SMON_SCN_TIME的写入和关闭12500事件

正常SMON_TIME_SCN的记录

SQL> select sysdate from dual;SYSDATE-------------------2017-10-11 11:05:02SQL> select count(1) from smon_scn_time;  COUNT(1)----------     4SQL> ^Cse  SQL> select count(1) from smon_scn_time;  COUNT(1)----------     8SQL> select sysdate from dual;SYSDATE-------------------2017-10-11 11:14:01

设置12500事件后

SQL> alter system set events '12500 trace name context forever,level 10';System altered.SQL> select count(1) from smon_scn_time;  COUNT(1)----------     8SQL> select sysdate from dual;SYSDATE-------------------2017-10-11 11:15:49SQL> select count(1) from smon_scn_time;  COUNT(1)----------     8SQL> select sysdate from dual;SYSDATE-------------------2017-10-11 11:28:37

关闭12500事件

SQL> alter system set events '12500 trace name context off';System altered.

阅读全文

0 0