Oracle LogMiner Tips And Tricks

LOGMINER :-

  • Logminer is a utility provided with the Oracle database server which mines the redologs or Archivelogs and data dictionary information to build the SQL statements and keeps the  contents of the redolog file in the fixed view called ” V$logmnr_contents “.
  • LogMiner tool can help the DBA to the find changed records in redo log  files by using a set of  PL/SQL  procedures  and  functions .
  • Internally Oracle uses the Log Miner technology for several other features,such as Flashback Transaction Backout,Streams, and Logical Standby Databases .Most often  LogMiner is used for recovery purposes when the data consists of just a few tables or a single code  change .

Steps for Configuring Logminer :-

 In this Scenario,we are checking that Username who Dropped the Table using LogMiner utility.

Setting  parameter UTL_FILE_DIR :

Normally we set the UTL_FILE_DIR parameter where you need to create dictionary file.From 12.2,we need to create directory object.

[oracle@orcl:~ orcldemo] mkdir -p /oradb/logminer

SQL> alter system set utl_file_dir='/u01/logminer' scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size 8621232 bytes
Variable Size 1006633808 bytes
Database Buffers 587202560 bytes
Redo Buffers 8155136 bytes
Database mounted.
Database opened.

SQL> show parameter UTL_FILE_DIR;

NAME          TYPE        VALUE
------------- ----------- -------------
utl_file_dir  string      /u01/logminer

Create and grant acccess to directory :

SQL> CREATE OR REPLACE DIRECTORY LOG_DIR AS '/oradb/logminer';

Directory created.

SQL> GRANT READ, WRITE ON DIRECTORY LOG_DIR TO hari;

Grant succeeded.

Enable Supplemental logging:

SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEME
--------
NO

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; 

Database altered.

Build Logminer :

Creation of Dictionary file :

The dictionary file is used to translate this data into a more meaningful format. Specify a filename for the dictionary and a directory path name for the file. This procedure creates the dictionary file.

The dictionary file is created using the BUILD procedure in the DBMS_LOGMNR_D package.

SQL> BEGIN
sys.DBMS_LOGMNR_D.build (
dictionary_filename => 'lgmnrdict.ora',
dictionary_location => 'LOG_DIR');
END;
/

PL/SQL procedure successfully completed.
SQL> select member from v$logfile;

MEMBER
------------------------------------------------------------------
/oradb/app/oracle/oradata/ORCLDEMO/onlinelog/o1_mf_3_fm2fw8lr_.log
/oradb/app/oracle/oradata/ORCLDEMO/onlinelog/o1_mf_2_fm2fvl50_.log
/oradb/app/oracle/oradata/ORCLDEMO/onlinelog/o1_mf_1_fm2fvl47_.log

3 rows selected.

Adding logfiles to get analyzed

Adding logfiles using NEW procedure first and add all logfiles using ADD_LOGFILE procedure.

SQL> BEGIN
DBMS_LOGMNR.add_logfile (
options => DBMS_LOGMNR.new,
logfilename => '/oradb/app/oracle/oradata/ORCLDEMO/onlinelog/o1_mf_3_fm2fw8lr_.log');

DBMS_LOGMNR.add_logfile (
options => DBMS_LOGMNR.addfile,
logfilename => '/oradb/app/oracle/oradata/ORCLDEMO/onlinelog/o1_mf_2_fm2fvl50_.log');

DBMS_LOGMNR.add_logfile (
options => DBMS_LOGMNR.addfile,
logfilename => '/oradb/app/oracle/oradata/ORCLDEMO/onlinelog/o1_mf_1_fm2fvl47_.log');

END;
/ 

PL/SQL procedure successfully completed.

Starting logminer process :-

SQL> BEGIN
-- Start using all logs
DBMS_LOGMNR.start_logmnr (
dictfilename => '/oradb/logminer/lgmnrdict.ora');
END;
/ 

PL/SQL procedure successfully completed.

Querying  v$logmnr_contents :-

When the LogMiner session ends then v$logmnr_contents is no more accessible .  Its always better to copy contents of v$logmnr_contents to a user table and then perform  the analysis as it is quite expensive to query v$logmnr_contents .

Now we can catch that user who dropped the table, the user is ‘HARI’ and also we can check who created the table.

SQL> select username,table_name,sql_redo from v$logmnr_contents where seg_name='SAN';

USERNAME   TABLE_NAME  SQL_REDO
---------- ----------- -------------------------------------
HARI       SAN     create table san(num number,name varchar2(10));

SYS        SAN    ALTER TABLE "HARI"."SAN" RENAME TO                                     "BIN$dsifZpmIOhHgU4NWqMCSjA==$0" ;                                                                                                      

HARI       SAN    drop table hari.san AS  
                  "BIN$dsifZpmIOhHgU4NWqMCSjA==$0" ;  

3 rows selected.                 

 

Filtering Data by SCN:

To filter data by SCN (system change number), use the STARTSCN and ENDSCN parameters,

Demo :-

Check the current_scn before perform DML transaction

SQL> select to_char(current_scn) from v$database;

TO_CHAR(CURRENT_SCN)
--------------------
2619410

Execute DML statements

SQL> conn hari/hari;
Connected.
SQL> insert into emp values(1,100);

1 row created.

SQL> insert into emp values(2,200);

1 row created.

SQL> insert into emp values(3,300);

1 row created.

SQL> commit;

Commit complete.

Check the current_scn before perform DML transaction

SQL> select to_char(current_scn) from v$database;

TO_CHAR(CURRENT_SCN)
--------------------

 2619497

Add list of logfiles to get analyzed

SQL> BEGIN
DBMS_LOGMNR.add_logfile (
options => DBMS_LOGMNR.new,
logfilename => '/oradb/app/oracle/oradata/ORCLDEMO/onlinelog/o1_mf_3_fm2fw8lr_.log');

DBMS_LOGMNR.add_logfile (
options => DBMS_LOGMNR.addfile,
logfilename => '/oradb/app/oracle/oradata/ORCLDEMO/onlinelog/o1_mf_2_fm2fvl50_.log');

DBMS_LOGMNR.add_logfile (
options => DBMS_LOGMNR.addfile,
logfilename => '/oradb/app/oracle/oradata/ORCLDEMO/onlinelog/o1_mf_1_fm2fvl47_.log');

END;
/

PL/SQL procedure successfully completed.

Specify SCN range to start logminer process

SQL> begin
DBMS_LOGMNR.start_logmnr (
dictfilename => '/oradb/logminer/lgmnrdict.ora',
startscn => 2619410,
endscn => 2619497);
END;
/ 

PL/SQL procedure successfully completed.

Check the log information v$logmnr_contents

SQL> SELECT OPERATION, STATUS, SQL_REDO FROM V$LOGMNR_CONTENTS WHERE SEG_OWNER = 'HARI' AND TABLE_NAME = 'EMP';

OPERATION   STATUS   SQL_REDO
----------  ------   -----------------------------------------------
INSERT      0        insert into "HARI"."EMP"("EMPNO","SAL") values                         ('1','100');

INSERT      0        insert into "HARI"."EMP"("EMPNO","SAL") values                                               
                     ('2','200');
INSERT      0        insert into "HARI"."EMP"("EMPNO","SAL") values
                     ('3','300');
Filtering Data By Time  : 

 

To filter data by time, set the STARTTIME and ENDTIME parameters in the DBMS_LOGMNR.START_LOGMNR procedure.

Demo :-

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> select sysdate from dual;

SYSDATE
--------------------
27-SEP-2018 03:13:13

Perform DML operations

SQL> conn hari/hari;
Connected.
SQL> insert into emp values(5,500);

1 row created.

SQL> delete emp where empno=1;

1 row deleted.

SQL> commit;

Commit complete.
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> select sysdate from dual;

SYSDATE
--------------------
27-SEP-2018 03:15:00

Add list of logfiles to get analyzed

SQL> BEGIN
DBMS_LOGMNR.add_logfile (
options => DBMS_LOGMNR.new,
logfilename => '/oradb/app/oracle/oradata/ORCLDEMO/onlinelog/o1_mf_3_fm2fw8lr_.log');

DBMS_LOGMNR.add_logfile (
options => DBMS_LOGMNR.addfile,
logfilename => '/oradb/app/oracle/oradata/ORCLDEMO/onlinelog/o1_mf_2_fm2fvl50_.log');

DBMS_LOGMNR.add_logfile (
options => DBMS_LOGMNR.addfile,
logfilename => '/oradb/app/oracle/oradata/ORCLDEMO/onlinelog/o1_mf_1_fm2fvl47_.log');

END;
/ 

PL/SQL procedure successfully completed.

Specify time range to start the logminer process

begin
dbms_logmnr.start_logmnr (
dictfilename => '/oradb/logminer/lgmnrdict.ora',
starttime => TO_DATE('27-SEP-2018 03:13:13', 'DD-MON-YYYY HH:MI:SS'),
endtime => TO_DATE('27-SEP-2018 03:15:00', 'DD-MON-YYYY HH:MI:SS'));
end;
/

PL/SQL procedure successfully completed.

Check  v$logmnr_contents 

SQL> SELECT OPERATION, STATUS, SQL_REDO FROM V$LOGMNR_CONTENTS WHERE SEG_OWNER = 'HARI' AND TABLE_NAME = 'EMP';

OPERATION  STATUS  SQL_REDO
---------- ------  --------------------------------------------------
INSERT     0       insert into "HARI"."EMP"("EMPNO","SAL") values   
                   ('5','500');                   

DELETE     0       delete from "HARI"."EMP" where "EMPNO" = '1' and
                   "SAL" = '100' and ROWID = 'AAASWiAACAAAAqYAAA';

Stopping logminer process:

Once the analysis is complete, logminer should be stopped using the END_LOGMNR procedure.

SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR();

PL/SQL procedure successfully completed.
Accessing LogMiner Information : 
LogMiner information is contained in the following views. We can use SQL to query them as we would any other view.
V$LOGMNR_CONTENTS  :  Shows changes made to user and table information.
V$LOGMNR_DICTIONARY : Shows information about the LogMiner dictionary file, provided the dictionary was created using the STORE_IN_FLAT_FILE option. The information shown includes the database name and status information.
V$LOGMNR_LOGS  :  Shows information about specified redo logs. There is one row for each redo log.
V$LOGMNR_PARAMETERS : Shows information about optional LogMiner parameters, including starting and ending system change numbers (SCNs) and starting and ending times.

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

Step-by-Step One Node Rac Applying Psu Patch on 12c Grid and DB Home

Description:-

As we already seen how to configure Oracle One node RAC in 12cR1 and the relocation of the instance from one node to another node. In this article, let us apply the July’18 PSU patch to the same environment.

For Oracle One Node RAC configuration, please click here. Below is the configuration of the environment.

High Level steps for applying the Patch:-

  • Current OPatch Version
  • Upgrade Opatch utility
  • Prepare for Patching
  • Applying Patch
  • Patch Verification

Current OPatch Version:-

Step 1:- Current version of Opatch Tool in our environment

$ export PATH=$ORACLE_HOME/OPatch:$PATH
$ $ORACLE_HOME/OPatch/opatch version
OPatch Version: 12.1.0.1.3

OPatch succeeded.

From the above output,the opatch version is 12.1.0.1.3. But as per our README document, the minimum OPatch utility version shoul be 12.2.0.1.12 or later to apply this patch. Oracle recommends that you use the latest released OPatch version for 12.2, which is available for download from My Oracle Support patch 6880880 by selecting the 12.2.0.1.0 release.

Upgrade Opatch utility:-

Step 2:- Backup the existing Opatch folder

Backup the OPatch directory as root user for GRID_HOME and oracle user for ORACLE_HOME(Database) in both the nodes of the cluster. Otherwise, if we try to backup as oracle user in GRID_HOME, we will receive permission issues.

GRID_HOME:
$ su - root
$ cd /oradb/app/12.1.0.2/grid/
$ mv OPatch/ OPatch_bkp
$ unzip <PATH_TO_PATCH>/p6880880_122010_Linux-x86-64.zip -d .
$ chown -R oracle:oinstall OPatch
$ chmod -R 755 OPatch

ORACLE_HOME:
$ su - oracle
$ cd /oradb/app/oracle/product/12.1.0.2/db_1
$ mv OPatch/ OPatch_bkp
$ unzip <PATH_TO_PATCH>/p6880880_122010_Linux-x86-64.zip -d .
$ chmod -R 755 OPatch

Now, as oracle user verify the OPatch utility version.

GRID_HOME:-(Both Nodes)

$ export ORACLE_HOME=/oradb/app/12.1.0.2/grid
$ export PATH=$ORACLE_HOME/OPatch:$PATH
$ opatch version
OPatch Version: 12.2.0.1.14

OPatch succeeded.

ORACLE_HOME:-(Both Nodes)

$ export ORACLE_HOME=/oradb/app/oracle/product/12.1.0.2/db_1
$ export PATH=$ORACLE_HOME/OPatch:$PATH
$ opatch version
OPatch Version: 12.2.0.1.14

OPatch succeeded.

Prepare for Patching:-

Step 3:- Preparing Node 1 to apply the PSU Patch

Now, login as root user and set the environmental variables

Applying Patch:-

Step 4:- Navigate to the patch location and follow the below steps to apply the patch.

$ cd <PATH_TO_PATCH>
$ unzip p27967747_121020_Linux-x86-64.zip
$ cd 27967747
$ $ORACLE_HOME/OPatch/opatchauto apply ./

OPatchauto session is initiated at Wed Sep 26 02:39:52 2018

System initialization log file is /oradb/app/12.1.0.2/grid/cfgtoollogs/opatchautodb/systemconfig2018-09-26_02-40-10AM.log.

Session log file is /oradb/app/12.1.0.2/grid/cfgtoollogs/opatchauto/opatchauto2018-09-26_02-41-29AM.log
The id for this session is WYWB

Executing OPatch prereq operations to verify patch applicability on home /oradb/app/12.1.0.2/grid

Executing OPatch prereq operations to verify patch applicability on home /oradb/app/oracle/product/12.1.0.2/db_1
Patch applicability verified successfully on home /oradb/app/oracle/product/12.1.0.2/db_1

Patch applicability verified successfully on home /oradb/app/12.1.0.2/grid

Verifying SQL patch applicability on home /oradb/app/oracle/product/12.1.0.2/db_1
SQL patch applicability verified successfully on home /oradb/app/oracle/product/12.1.0.2/db_1

Preparing to bring down database service on home /oradb/app/oracle/product/12.1.0.2/db_1

WARNING: The service ORCL.oracledbwr.com configured on orcl will not be switched as it is not configured to run on any other node(s).
Successfully prepared home /oradb/app/oracle/product/12.1.0.2/db_1 to bring down database service

Relocating RACOne home before patching on home /oradb/app/oracle/product/12.1.0.2/db_1
Relocated RACOne home before patching on home /oradb/app/oracle/product/12.1.0.2/db_1

Bringing down CRS service on home /oradb/app/12.1.0.2/grid
Prepatch operation log file location: /oradb/app/12.1.0.2/grid/cfgtoollogs/crsconfig/crspatch_prodrac101_2018-09-26_02-49-04AM.log
CRS service brought down successfully on home /oradb/app/12.1.0.2/grid

Performing prepatch operation on home /oradb/app/oracle/product/12.1.0.2/db_1
Perpatch operation completed successfully on home /oradb/app/oracle/product/12.1.0.2/db_1

Start applying binary patch on home /oradb/app/oracle/product/12.1.0.2/db_1
Binary patch applied successfully on home /oradb/app/oracle/product/12.1.0.2/db_1

Performing postpatch operation on home /oradb/app/oracle/product/12.1.0.2/db_1
Postpatch operation completed successfully on home /oradb/app/oracle/product/12.1.0.2/db_1


Start applying binary patch on home /oradb/app/12.1.0.2/grid
Binary patch applied successfully on home /oradb/app/12.1.0.2/grid

Starting CRS service on home /oradb/app/12.1.0.2/grid
Postpatch operation log file location: /oradb/app/12.1.0.2/grid/cfgtoollogs/crsconfig/crspatch_prodrac101_2018-09-26_03-42-46AM.log
CRS service started successfully on home /oradb/app/12.1.0.2/grid

Relocating back RACOne to home /oradb/app/oracle/product/12.1.0.2/db_1
Relocated back RACOne home successfully to home /oradb/app/oracle/product/12.1.0.2/db_1


Preparing home /oradb/app/oracle/product/12.1.0.2/db_1 after database service restarted
No step execution required.........


Trying to apply SQL patch on home /oradb/app/oracle/product/12.1.0.2/db_1
SQL patch applied successfully on home /oradb/app/oracle/product/12.1.0.2/db_1

OPatchAuto successful.

--------------------------------Summary--------------------------------

Patching is completed successfully. Please find the summary as follows:

Host:prodrac101
RAC Home:/oradb/app/oracle/product/12.1.0.2/db_1
Version:12.1.0.2.0
Summary:

==Following patches were SKIPPED:

Patch: /mnt/hgfs/shared/soft/12102/July18_PSU/27967747/26983807
Reason: This patch is not applicable to this specified target type - "rac_database"

Patch: /mnt/hgfs/shared/soft/12102/July18_PSU/27967747/27762277
Reason: This patch is not applicable to this specified target type - "rac_database"


==Following patches were SUCCESSFULLY applied:

Patch: /mnt/hgfs/shared/soft/12102/July18_PSU/27967747/27547329
Log: /oradb/app/oracle/product/12.1.0.2/db_1/cfgtoollogs/opatchauto/core/opatch/opatch2018-09-26_02-55-50AM_1.log

Patch: /mnt/hgfs/shared/soft/12102/July18_PSU/27967747/27762253
Log: /oradb/app/oracle/product/12.1.0.2/db_1/cfgtoollogs/opatchauto/core/opatch/opatch2018-09-26_02-55-50AM_1.log


Host:prodrac101
CRS Home:/oradb/app/12.1.0.2/grid
Version:12.1.0.2.0
Summary:

==Following patches were SUCCESSFULLY applied:

Patch: /mnt/hgfs/shared/soft/12102/July18_PSU/27967747/26983807
Log: /oradb/app/12.1.0.2/grid/cfgtoollogs/opatchauto/core/opatch/opatch2018-09-26_03-08-36AM_1.log

Patch: /mnt/hgfs/shared/soft/12102/July18_PSU/27967747/27547329
Log: /oradb/app/12.1.0.2/grid/cfgtoollogs/opatchauto/core/opatch/opatch2018-09-26_03-08-36AM_1.log

Patch: /mnt/hgfs/shared/soft/12102/July18_PSU/27967747/27762253
Log: /oradb/app/12.1.0.2/grid/cfgtoollogs/opatchauto/core/opatch/opatch2018-09-26_03-08-36AM_1.log

Patch: /mnt/hgfs/shared/soft/12102/July18_PSU/27967747/27762277
Log: /oradb/app/12.1.0.2/grid/cfgtoollogs/opatchauto/core/opatch/opatch2018-09-26_03-08-36AM_1.log

OPatchauto session completed at Wed Sep 26 04:03:09 2018
Time taken to complete the session 83 minutes, 17 seconds

Patch Verification:-

Step 5:- Once the patch has been applied successfully, verify it in the database like below.

$ sqlplus / as sysdba
SQL> set serveroutput on
SQL> exec dbms_qopatch.get_sqlpatch_status;

Patch Id : 27547329
Action : APPLY
Action Time : 26-SEP-2018 04:03:06
Description : DATABASE PATCH SET UPDATE 12.1.0.2.180717
Logfile :
/oradb/app/oracle/cfgtoollogs/sqlpatch/27547329/22280349/27547329_apply_ORCL_201
8Sep26_04_00_51.log
Status : SUCCESS

PL/SQL procedure successfully completed.

Similarly, follow the same steps to apply the patch in Node 2.

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba