Tuesday, January 5, 2021

Oracle19c LogMiner

ORA-01325 archive log must be enabled


archive log - copy of redo log

delete of data

suspend archive on datapump



CL SCR

automatic log file slecction by specifying time frame timestamps

sqlplus -s 

SET LINESIZE 200;

alter session sset nlf_date_format = 'DD-MON-YYYY HH24:MI:SS';

ALTER DATATBASE ADD  SUPPLEMENTAL LOG DATA;

select supppliemental)log_data)min from V$DATABASE;


EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -

    LOGFILENAME => 'PATH/fast_recovery_area/LGDB/archivelog/DATA/*.arc'. -

    options =>   DBMS_LOGMNR.NEW);>>


EXECUTE DBMS_LOGMNR.ADD_LOGFILE( 'path/redo1.log');


EXECUTE DBMS_LOGMNR.START_LOGMINER( -

        OPTIONS=>DBMS_LOGMNR.DICT_FROM_ONLLINE_CATALOG);

    STARTTIME => '01-Jan-2021 00:00:00', 

        ENDTIME =>


CONTINUOUS MINE IS GONE IN 19C

USE



DESC V$LOGMNR_CONTENETS

COLUMN X FORMAT A##;



SELECT OPERATION,USERNAME,OS_USENAME, SQL_REDO, TIMESTAMP FROM VS LOGMNR_CONTENTS;


DATA DICTIONARY USES UTL_FILE_DIR




localhost:1158/em

alter dataabae add suplimental log data(pirmary key) columns

alter system switch logfile; -- to force checkpoint



1. create init param on oracle db pointing to path \

init param can be pfile INIT.ora or spfile (server binary)

SHOW PARAMETERS UTL_FILE_DIR;

SHOW PARAMETERS 

Issue this command to display the parameter values that are currently in effect.

V$PARAMETER view

Query this view to display the parameter values that are currently in effect.

V$PARAMETER2 view

Query this view to display the parameter values that are currently in effect. The output from this view is the same, but more readable, than the output from the V$PARAMETER view.

V$SPPARAMETER view

Query this view to display the current contents of the server parameter file. The view returns null values if a server parameter file is not being used by the instance.

alter system set UTL_FILE_DIR = path


then us the DBMS_LOGMNR_D.build in plsql 



to build dd

EXECUTE DBMS_LOGMNR_D.BUILD('datadict.ora', -

    'utl_file_dir_loc', -

    DBMS_LOGMNR_D.store_in-flat_file;>>


EXECUTE DBMS_LOGMNR.START_LOGMNR( -

    DICTFILENAME => 'PATH/datadict.ora');>



https://grepora.com/2020/05/13/19c-could-not-execute-dbms_logmnr-start_logmnr-ora-44609-continous_mine-is-desupported-for-use-with-dbms_logmnr-start_logmnr/



LogMiner dictionary provide table/column names