Useful Tips for Oracle DBAs

Hi All,

Here are some Oracle DBA Tips which I want to share with you....

Logminer in Oracle 9i -

What is LogMiner?
LogMiner is an Oracle utility. Using LogMiner one can query the contents of online redo log files and archived log files. It can be used as a powerful data audit tool, as well as a tool for sophisticated data analysis.

Log Miner Objects:
The three basic objects in a LogMiner configuration:
Source Database
LogMiner Directory
Redo log files

Database must be OPEN and in ARCHIVELOG mode:
To extract a LogMiner dictionary to the redo log files, the database must be open and in ARCHIVELOG mode and archiving must be enabled. While the dictionary is being extracted to the redo log stream, no DDL statements can be executed.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /ora/app/oracle/admin/testd/arch/testd_
Oldest online log sequence 20860
Next log sequence to archive 20862
Current log sequence 20862

Get the list of ARCHIVES:
Make sure you get the list of archives generated for the day using the below command. From the below output identify the archivelogs you are going to mine using logminer..
SQL> Alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> Select thread#,sequence#,completion_time from v$archived_log
order by completion_time desc;
2
THREAD# SEQUENCE# COMPLETION_TIME
---------- ---------- -------------------
1 20858 2008-12-17 14:38:17
1 20857 2008-12-17 11:24:40

Set the initialization parameter:
Set the initialization parameter, UTL_FILE_DIR, in the initialization parameter file. For example, to set UTL_FILE_DIR to use /ora/app/oracle/admin/testd/utl as the directory where the dictionary file is placed, enter the following in the initialization parameter file:
UTL_FILE_DIR = /ora/app/oracle/admin/testd/utl
SQL> show parameter utl_file
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir string /ora/app/oracle/admin/testd/utl

Create the Directory file:
Execute the PL/SQL procedure DBMS_LOGMNR_D.BUILD. Specify a filename for
the dictionary and a directory path name for the file. This procedure creates the dictionary file. For example, enter the following to create the file dictionary.ora in /ora/app/oracle/admin/testd/utl:
execute dbms_logmnr_d.build ('dictionary.ora','/ora/app/oracle/admin/testd/utl');
PL/SQL procedure successfully completed.

Copy & Register all Archivelogs:
Copy all the archivelog to a path and register all the logs as show below:
SQL> execute dbms_logmnr.add_logfile ('/ora/app/oracle/admin/testd/arch/testd_0000020858.arc',dbms_logmnr.addfile);
PL/SQL procedure successfully completed.

Registered logs availability:
Now from the below view, make sure you have all the registered logs available for mining.
Set lines 120
Set pages 120
SQL> col filename for a52
SQL> select log_id,filename from v$logmnr_logs;
LOG_ID FILENAME
---------- ----------------------------------------------------
20858 /ora/app/oracle/admin/testd/arch/testd_0000020858.arc

Find the SCNs:
Using the below view's find the first SCN and high SCN to mine from the registered logs.
SQL> Alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> select low_time,high_time,low_scn,next_scn from v$logmnr_logs;
LOW_TIME HIGH_TIME LOW_SCN NEXT_SCN
------------------- ------------------- ---------- ----------
2008-12-17 11:24:40 2008-12-17 14:38:17 3.4951E+10 3.4951E+10

LogMiner Session:
From the above out gather the details and add it to the below LogMiner session:
Starting Log Miner
SQL> execute dbms_logmnr.start_logmnr(dictfilename =>'/ora/app/oracle/admin/testd/utl/dictionary.ora', -
starttime =>to_date('2008-12-17 11:24:40','yyyy-mm-dd hh24:mi:ss'), endtime =>to_date ('2008-12-17 14:38:17','yyyy-mm-dd hh24:mi:ss'));>
PL/SQL procedure successfully completed.

See The Content:
As v$logmnr_contents is a temporary view, once you disconnect your session , you won't
be able to see the content, so make sure you create a table of all the contents of the view.
SQL> create table logmnr_table_01 as select * from v$logmnr_contents;
Table created.
Checking operations on database objects
Now query the table to see the content.
SQL> set pages 120
SQL> SELECT count(*) occurrences ,
2 seg_owner ,
3 seg_name ,
4 operation
5 FROM v$logmnr_contents
6 group by seg_owner,seg_name,operation order by 1;
SQL> /
OCCURRENCES SEG_OWNER SEG_NAME OPERATION
----------- -------------------------------- ------------------------------ ------------------
1 ROLLBACK
1 SYS OBJ$ INSERT
1 SYS TEST DDL
1 SYS OBJ$ UPDATE
1 SYS OBJ$,I_OBJ2 INTERNAL
1 SYS USER$,I_USER1 INTERNAL
1 SYS OBJ$,I_OBJ1 INTERNAL
1 SYS TEST DELETE
2 SYS COL$ UNSUPPORTED
2 SYS COL$,I_COL1 INTERNAL
2 KELKAM DDL
2 SYS COL$,I_COL3 INTERNAL
2 SYS COL$,I_COL2 INTERNAL
2 SYS SYSAUTH$ UNSUPPORTED
2 SYS TAB$ UNSUPPORTED
2 SYS UET$ UNSUPPORTED
2 SYS SYSAUTH$ DELETE
2 SYS SEQ$ UPDATE
3 SYS TEST INSERT
3 SYS DEFROLE$,I_DEFROLE1 INTERNAL
3 SYS DEFROLE$ INSERT
4 DDL
4 SYS SYSAUTH$,I_SYSAUTH1 INTERNAL
4 UNSUPPORTED
4 SYS FET$ UNSUPPORTED
5 SYS SEG$ UNSUPPORTED
6 SYS SYSAUTH$ INSERT
7 SYS USER$ UNSUPPORTED
26 SYS SMON_SCN_TIME UNSUPPORTED
27 SYS COL_USAGE$ UPDATE
28 SYS COL_USAGE$ INSERT
28 SYS COL_USAGE$,I_COL_USAGE$ INTERNAL
67 COMMIT
68 START
73 INTERNAL
35 rows selected.
3 SYS TEST INSERT
This is the operation where I have inserted some rows into TEST table owned by SYS.

Checking Specific Actions
Now query any segment for checking the action/transaction done on it.
SQL> select sql_redo FROM v$logmnr_contents
where seg_owner='SYS' and seg_name='SYSAUTH$'
and operation='INSERT' and rownum <>
SQL_REDO
----------------------------------------------------------------------------------------------
insert into "SYS"."SYSAUTH$"("GRANTEE#","PRIVILEGE#","SEQUENCE#") values ('47','-15','1043');
insert into "SYS"."SYSAUTH$"("GRANTEE#","PRIVILEGE#","SEQUENCE#") values ('47','2','1044');
insert into "SYS"."SYSAUTH$"("GRANTEE#","PRIVILEGE#","SEQUENCE#") values ('47','-15','1045');

Ending Log Miner Session :
End the LogMiner session if you are done with all the auditing of the data by mining the archive logs.
SQL> BEGIN
2 DBMS_LOGMNR.END_LOGMNR;
3 END;
4 /
PL/SQL procedure successfully completed.

Atishay Sunder.....

Kahi diwasapurvi mi ha natyaprayog Gaqdkarila pahila....atishay sunder katha,subak sambhashan aani netka abhinayachi jod natyala apratim darja deun jaate. Punha punha pahawse waatnare aase natak. Aani lagnachya wayat aalyala tarunani baghawe aase adwitiy aase natak.

dhanyawaad !

email: amol.tambolkar@tcs.com