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.
