Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2034892
  • 博文数量: 200
  • 博客积分: 2413
  • 博客等级: 大尉
  • 技术积分: 3067
  • 用 户 组: 普通用户
  • 注册时间: 2011-04-01 22:07
  • 认证徽章:
文章分类

全部博文(200)

文章存档

2018年(2)

2017年(8)

2016年(35)

2015年(14)

2014年(20)

2013年(24)

2012年(53)

2011年(44)

分类: Oracle

2012-08-20 16:55:47

Applies to: Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 11.1.0.7 - Release: 10.1 to 11.1
Information in this document applies to any platform.
Goal

During the database creation, the CATPROC.SQL script will be run, which in turn invokes the following scripts to build/install the LogMiner metadata schema:

 

dbmslm.sql
prvtlm.plb
dbmslmd.sql
prvtlmd.plb
prvtlmc.plb (or prvtlmcs.plb + prvtlmcb.plb beginning with 11gR1)

However if any of the LogMiner tables or views becomes corrupted rerunning these scripts does not properly rebuild the metadata schema for LogMiner unless specific conditions are met.
Solution

The creation of the LogMiner tables depends on the existence of the SYSTEM.LOGMNR_SESSION$ table. If this table exists, rerunning the above scrips will only recreate the packages of LogMiner, but will not recreate any metadata tables or views. By removing this table and rerunning the aforementioned scripts the LogMiner metadata schema will be rebuilt correctly, like in:

 

NOTE:  Modifications to these steps and tested these steps internally on 10g, 11.1.x, and 11.2.x (Oct 2011)

On 10g database

SQL> connect / as sysdba
SQL> drop table SYSTEM.LOGMNR_SESSION$ purge;

NOTE: you will need to recreate the LOGMNR_SESSION$ view manually

SQL> CREATE TABLE SYSTEM.LOGMNR_SESSION$ (
session# number,
client# number,
session_name varchar2(128) not null,
db_id number,
resetlogs_change# number,
session_attr number,
session_attr_verbose varchar2(400),
start_scn number,
end_scn number,
spill_scn number,
spill_time date,
oldest_scn number,
resume_scn number,
global_db_name varchar2(128) default null,
reset_timestamp number,
branch_scn number,
version varchar2(64),
spare1 number,
spare2 number,
spare3 number,
spare4 number,
spare5 number,
spare6 date,
spare7 varchar2(1000),
spare8 varchar2(1000),
constraint logmnr_session_pk primary key (session#)
using index tablespace SYSTEM logging,
constraint logmnr_session_uk1 unique (session_name)
using index tablespace SYSTEM logging enable validate)
tablespace SYSTEM logging
/

SQL> @?/rdbms/admin/dbmslm.sql
SQL> @?/rdbms/admin/prvtlm.plb
SQL> @?/rdbms/admin/dbmslmd.sql
SQL> @?/rdbms/admin/prvtlmd.plb
SQL> @?/rdbms/admin/prvtlmc.plb
SQL> @?/rdbms/admin/utlrp.sql


If you continue to see invalid objects after these steps, you may have to rerun the utlrp.sql step a number of times.

On 11g (Release 1 and Release 2)

SQL> connect / as sysdba
SQL> drop table SYSTEM.LOGMNR_SESSION$ purge;

NOTE: you will need to recreate the LOGMNR_SESSION$ view manually

CREATE TABLE SYSTEM.LOGMNR_SESSION$ (
session# number,
client# number,
session_name varchar2(128) not null,
db_id number,
resetlogs_change# number,
session_attr number,
session_attr_verbose varchar2(400),
start_scn number,
end_scn number,
spill_scn number,
spill_time date,
oldest_scn number,
resume_scn number,
global_db_name varchar2(128) default null,
reset_timestamp number,
branch_scn number,
version varchar2(64),
redo_compat varchar2(20),
spare1 number,
spare2 number,
spare3 number,
spare4 number,
spare5 number,
spare6 date,
spare7 varchar2(1000),
spare8 varchar2(1000),
constraint logmnr_session_pk primary key (session#)
using index tablespace SYSTEM logging,
constraint logmnr_session_uk1 unique (session_name)
using index tablespace SYSTEM logging enable validate)
tablespace SYSTEM logging
/

SQL> @?/rdbms/admin/dbmslm.sql
SQL> @?/rdbms/admin/prvtlm.plb
SQL> @?/rdbms/admin/dbmslmd.sql
SQL> @?/rdbms/admin/prvtlmd.plb
SQL> @?/rdbms/admin/prvtlmcs.plb
SQL> @?/rdbms/admin/prvtlmcb.plb
SQL> @?/rdbms/admin/utlrp.sql

If you continue to see invalid objects after these steps, you may have to rerun the utlrp.sql step a number of times.

阅读(2011) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册