Skip to content

Instantly share code, notes, and snippets.

@NISH1001

NISH1001/oracle.md

Last active July 9, 2021 05:21
Show Gist options
  • Save NISH1001/a2bef7e820b48b715860e88b42b06177 to your computer and use it in GitHub Desktop.
Save NISH1001/a2bef7e820b48b715860e88b42b06177 to your computer and use it in GitHub Desktop.
Download ZIP
oracle
Raw
oracle.md

Check If archivelog mode is enabled

select log_mode from v$database;

Enable Archive mode

alter database archivelog;

Invoke Archiving

alter system switch logfile;

This will start log miner...

Get Redo Log Filenames

SELECT distinct member LOGFILENAME FROM V$LOGFILE

Docker Shit

run with autogenerated password

docker run --name oracle-test -p 1521:1521 -p 5500:5500 -e ORACLE_SID=ORCLCDB -e ORACLE_PDB=ORCLPDB1 -e ORACLE_CHARACTERSET=AL32UTF8 -v /opt/oracle/oradata oracle/database:12.2.0.1-se2

run with previous password

docker run --name oracle-test -p 1521:1521 -p 5500:5500 -e ORACLE_SID=ORCLCDB -e ORACLE_PDB=ORCLPDB1 -e ORACLE_PWD=66175d775cM=1 -e ORACLE_CHARACTERSET=AL32UTF8 -v /opt/oracle/oradata oracle/database:12.2.0.1-se2

run with local storage

docker run --name oracle-test -p 1521:1521 -p 5500:5500 -e ORACLE_SID=ORCLCDB -e ORACLE_PDB=ORCLPDB1 -e ORACLE_PWD=66175d775cM=1 -e ORACLE_CHARACTERSET=AL32UTF8 -v /opt/oracle/oradata/:/opt/oracle/oradata oracle/database:12.2.0.1-se2

run as sys

docker exec -ti oracle-test sqlplus sys/66175d775cM=1@ORCLCDB as sysdba

References:

SHIT

setup

enable archive log

select log_mode from v$database;

shutdown immediate

startup mount

alter database archivelog;

alter database open;

Enable Supplement Logging

select supplemental_log_data_min, supplemental_log_data_pk from v$database;

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

alter system switch logfile;

Create Logminer role

alter session set "_ORACLE_SCRIPT"=true;

create role logminer_priv;

grant create session to logminer_priv; grant execute_catalog_role to logminer_priv; grant select any transaction to logminer_priv; grant select any dictionary to logminer_priv; grant select on SYSTEM.LOGMNR_COL$ to logminer_priv; grant select on SYSTEM.LOGMNR_OBJ$ to logminer_priv; grant select on SYSTEM.LOGMNR_USER$ to logminer_priv; grant select on SYSTEM.LOGMNR_UID$ to logminer_priv;

Create New user for logminer

create user miner identified by miner; grant logminer_priv to miner; alter user miner quota unlimited on users;

GRANT CONNECT TO miner; GRANT CONNECT, RESOURCE, DBA TO miner; GRANT CREATE SESSION GRANT ANY PRIVILEGE TO miner; GRANT UNLIMITED TABLESPACE TO miner;

grant LOGMINING to logminer_priv; grant LOGMINING to miner;

create user c##miner identified by miner container=all; grant logminer_priv to c##miner container=all; alter user c##miner set container_data = (cdb$root, ORCLPDB1) container=current;

Create Quiscence table

CREATE TABLE QUIESCEMARKER (source varchar2(100), status varchar2(100), sequence NUMBER(10), inittime timestamp, updatetime timestamp default sysdate, approvedtime timestamp, reason varchar2(100), CONSTRAINT quiesce_marker_pk PRIMARY KEY (source, sequence));

Get Log Files

SELECT distinct member LOGFILENAME FROM V$LOGFILE;

Add Log Files

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( - LOGFILENAME => '/opt/oracle/oradata/ORCLCDB/redo01.log', - OPTIONS => DBMS_LOGMNR.NEW);

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( - LOGFILENAME => '/opt/oracle/oradata/ORCLCDB/redo01.log', - OPTIONS => DBMS_LOGMNR.NEW);

execute DBMS_LOGMNR.ADD_LOGFILE ('/opt/oracle/oradata/ORCLCDB/redo03.log') execute DBMS_LOGMNR.ADD_LOGFILE ('/opt/oracle/oradata/ORCLCDB/redo02.log') execute DBMS_LOGMNR.ADD_LOGFILE ('/opt/oracle/oradata/ORCLCDB/redo01.log')

Scripts

begin sys.DBMS_LOGMNR.ADD_LOGFILE ('/opt/oracle/oradata/ORCLCDB/redo03.log'); end; begin sys.DBMS_LOGMNR.ADD_LOGFILE ('/opt/oracle/oradata/ORCLCDB/redo02.log'); end; begin sys.DBMS_LOGMNR.ADD_LOGFILE ('/opt/oracle/oradata/ORCLCDB/redo01.log'); end;

Start Log Miner

execute DBMS_LOGMNR.START_LOGMNR (options => dbms_logmnr.dict_from_online_catalog);

execute DBMS_LOGMNR.START_LOGMNR (options => dbms_logmnr.committed_data_only);

EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY);

EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY);

begin sys.dbms_logmnr.start_logmnr( options => dbms_logmnr.dict_from_online_catalog); end;

begin dbms_logmnr.start_logmnr( options => dbms_logmnr.dict_from_online_catalog + dbms_logmnr.committed_data_only); end;

Continuous Mine

alter session set NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

execute DBMS_LOGMNR.START_LOGMNR(STARTTIME => '19-FEB-2019 01:01:01', ENDTIME => SYSDATE, OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE );

execute DBMS_LOGMNR.START_LOGMNR(STARTTIME => SYSDATE - interval '1' hour, ENDTIME => SYSDATE, OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE );

begin execute DBMS_LOGMNR.START_LOGMNR( STARTTIME => '14-FEB-2019 09:00:00', ENDTIME => SYSDATE, OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE + dbms_logmnr.committed_data_only); end;

Stop/End Log Miner

execute DBMS_LOGMNR.END_LOGMNR();

begin sys.DBMS_LOGMNR.END_LOGMNR(); end;

Some SQL Queries to Logs

select table_space, operation, table_name from v$logmnr_contents where start_timestamp>=sysdate-1 and username='UNKNOWN';

select operation, table_name from v$logmnr_contents where username='striim';

SELECT sql_redo FROM v$logmnr_contents WHERE table_name = 'info' AND seg_owner = 'striim' AND operation IN ('INSERT', 'UPDATE', 'DELETE');

SELECT username AS USR, SQL_REDO,SQL_UNDO FROM V$LOGMNR_CONTENTS WHERE SEG_OWNER IN ('striim');

select username, SQL_REDO, session_info from v$logmnr_contents where rownum < 50; select username, SQL_REDO, session_info from v$logmnr_contents where username = 'STRIIM'; select username, SQL_REDO, session_info from v$logmnr_contents where username not in ('SYS', 'SYSTEM') and TIMESTAMP > '10-feb-19'; select table_name, username, SQL_REDO, SQL_UNDO from v$logmnr_contents where rownum < 4 and operation = 'INSERT'; select table_name from v$logmnr_contents where operation = 'INSERT';

select commit_timestamp, table_name from v$logmnr_contents where table_name = 'INFO'; select scn, table_name, sql_redo from v$logmnr_contents where table_name = 'INFO'; select scn, table_name, operation, username, timestamp, sql_redo from v$logmnr_contents where table_name = 'INFO'; select scn, table_name, operation, username, timestamp, sql_redo from v$logmnr_contents where table_name = 'CUSTOMERS'; select scn, table_name, operation, username, timestamp, sql_redo from v$logmnr_contents where table_name in ('INFO'); select scn, table_name, operation, username, timestamp, sql_redo from v$logmnr_contents where table_name = 'INFO' and scn > 1787096;

select cscn, table_name, operation, username, timestamp, sql_redo from v$logmnr_contents where table_name = 'INFO';

References:

docker exec -ti oracle-test sqlplus miner/miner//192.168.40.106:1521/ORCLCDB

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment