Exporting the AUDIT tables/AUD$ Using Oracle Data Pump EXPDP

Dear Readers,

I have published one more article on KT EXPERTS. This article explains how to export AUD$ & other AUDIT tables using DATAPUMP.

Exporting the AUDIT tables/AUD$ Using Oracle Data Pump EXPDP

Hope u will find this post very useful. 🙂

Cheers

Regards,

Adityanath

Oracle Tablespace monitoring using Python scripting

Dear Readers,

I am planning to convert most of the scripts I currently use for Oracle DB monitoring, into python in near future. But why am I fascinated with Python?? Its very easy to understand. Its open source & most important its platform independent.

So here comes python code to check tablespace usage details in Oracle DB.

Note: This code is ready to use. But you can definitely refer it & modify wherever as per your need.

Prerequisites:

  • python binaries with version 3.
  • cx_Oracle module

What this program does:

This program needs following inputs from user side:

  • SYSTEM password
  • TNS entry to connect to DB.

At the end of execution, it provides following details about each tablespace in database.

  • Tablespace Name
  • Free Space in MB
  • Free%

Note: This code considers autoexensible clause & maxbytes, while calculating tablespace usage. Any tablespace having space less than 20% will be highlighted in RED color, while others will be in GREEN color.

Actual code:


# Script Name: tbs.py
# Description: Tablespace usage details in an Oracle DB
# Usage: python tbs.py
# Author: Adityanath Dewoolkar
# Version: 1

# importing required modules
import cx_Oracle
import getpass
import os

# For conditional coloring
os.system("")
class style():
RED = '\033[31m'
GREEN = '\033[32m'
RESET = '\033[0m'

# Prompt the user for a password without echoing
password = getpass.getpass(prompt = 'Enter SYSTEM password: ', stream=None)
# Enter TNS string to connect
db_name = input("Enter DB name: ")

# Code for getting Tablespace usage details in an Oracle DB
try:
con = cx_Oracle.connect("SYSTEM",password,db_name)
cursor=con.cursor()
head = ("TbsName","FreeSpaceMB","Free%")
print(head)
for result in cursor.execute("WITH new_tbs AS (SELECT /*+parallel(4)*/file_id, tablespace_name, file_name, DECODE (autoextensible,'YES', GREATEST (BYTES, maxbytes),BYTES) mysize,DECODE (autoextensible,'YES', CASE WHEN (maxbytes > BYTES) THEN (maxbytes - BYTES) ELSE 0 END, 0) growth FROM dba_data_files) select /*+parallel(4)*/ * from (SELECT new_tbs.tablespace_name tablespace ,ROUND ((SUM (NVL (freebytes, 0)) + SUM (growth)) / (1024 * 1024)) free_mb,ROUND ( (SUM (NVL (freebytes, 0)) + SUM (growth))/ SUM (new_tbs.mysize)* 100) pct_free FROM new_tbs, (SELECT file_id, SUM (BYTES) freebytes FROM dba_free_space GROUP BY file_id ) dfs WHERE new_tbs.file_id = dfs.file_id(+) AND upper(new_tbs.tablespace_name) NOT LIKE '%UNDO%' GROUP BY new_tbs.tablespace_name) my_tbs ORDER BY 3 "):
result=str(result)
pieces = result.split()
pieces = pieces[2]
free_percent = float(pieces[:-1])
if free_percent < 20:
print(style.RED + result.format(head) + style.RESET )
else:
print(style.GREEN + result.format(head) + style.RESET)


except cx_Oracle.DatabaseError as err:
print("Script execution failed: ", err)

How to execute:

 

Here comes the result:


I am re-running code after fixing SYSTEM tablespace. Here comes the new result:

ALL GREEN!!! HAPPY!!!!

You can download sample files from below location:

tbs.py

Hope u will find this post very useful.

Stay tuned for script to monitor FRA usage details in an Oracle DB

Cheers

Regards,
Adityanath

Generating multiple AWR reports using single command: Part 2

Dear Readers,

Yesterday one of my friend pinged with the requirement of generating muliple AWR reports for every 1 hour using single command. This particular database has SNAP_INTERVAL set to every 15 minutes.

He had already referred one of my old post given below, which helps us generating muliple AWR reports but it does generate awr report for every subsequent snaps.

Generating multiple AWR reports using single command: Part 1

So he asked me whether we can modify above procedure to generate AWR report for every 4 snaps.

Below procedure can be used for this requirement:

Code:


CREATE OR REPLACE PROCEDURE SYS.create_multiple_awr (
begin_snap NUMBER,
end_snap NUMBER,
directory VARCHAR2)
AS
v_Instance_number v$instance.instance_number%TYPE;
v_Instance_name v$instance.instance_name%TYPE;
v_dbid V$database.dbid%TYPE;
v_file UTL_FILE.file_type;
i NUMBER (5);
start_step NUMBER (5);
end_step NUMBER (5);
BEGIN
/* Collecting instance information: Instance_number, Instance_name and Dbid */
SELECT instance_number, instance_name
INTO v_Instance_number, v_Instance_name
FROM v$instance
ORDER BY 1;
SELECT dbid INTO v_dbid FROM v$database;
/* Creating a database directory which will point to the actual wanted report directory in the OS */
EXECUTE IMMEDIATE('CREATE OR REPLACE DIRECTORY TEMP_DIR AS ''' || directory || '''');
/* Looping on all the snapshots from the begin_snap input parameter to the end_snap input parameter
On each snapshot pair we will create a file in the given directory which will contain the AWR report.
We use DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML build in procedure to create the HTML report.
*/
i := 2;
start_step := 0;
end_step := 2;
BEGIN
LOOP
v_file :=
UTL_FILE.fopen ('TEMP_DIR','awr_'|| v_Instance_name|| '_'|| v_Instance_number|| '_'|| (( begin_snap ) + ( i * start_step ))
|| '_'
|| (( begin_snap )+ ( i * end_step ))
|| '.html',
'w',
32767);
FOR c_AWRReport IN (SELECT output
FROM TABLE (
DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML (
v_dbid,
v_Instance_number,
(( begin_snap ) +( i * start_step )),
(( begin_snap ) +( i * end_step )))))
LOOP
UTL_FILE.PUT_LINE (v_file, c_AWRReport.output);
END LOOP;
EXIT WHEN ( ( begin_snap )+( i * end_step )) >= end_snap;
start_step := start_step + 2;
end_step := end_step + 2;
--Closing the file:
UTL_FILE.fclose (v_file);
END LOOP;
END;
/* Dropping the database directory which we've created earlier. */
EXECUTE IMMEDIATE ('DROP DIRECTORY TEMP_DIR');
END;
/

You have to create this procedure under SYS schema.


Usage:

exec create_multiple_awr (begin_snap,end_snap,'<path>’);

Example:

exec create_multiple_awr (15700,15720,’/home/oracle’);


You can also download code from below link:

create_multiple_awr


Hope u will find this post very useful.

Cheers

Regards,
Adityanath

RMAN backup report through Python

Dear Readers,

I recently completed Python programming course which was authorized by “University of Michigan” offered through Coursera. It was really good course & worth the time I invested in.

So here comes my first python program, which can be useful for Oracle DBA’s like me to get consolidated report on RMAN backup.

Of course we can always get this information from v$rman_backup_job_details but what if database is not available or you are looking for backup information older than your retention period. You need to go to backup location & read through all backup out/log files & capture the details you need

So I thought of writing a python program & see if we can get desired results in one shot.

Note: You cant just copy/paste this code & use in your environment. But you can definitely refer it & modify wherever needed.

Prerequisites:

1. You will create a file named bkp.txt. This file will contain all names of logfiles you need to read. I am using below file for my demo.

2. python binaries with version 3.

3. Program file, in this example bkp.py & bkp.txt should be copied to location which contains all logfiles.

Actual program:

How to execute:

Here comes the result:

Please find below the course link through which I was able to write my first program.

Coursera – Python for Everybody

You can download sample files from below location:

Sample log files

bkp.py + bkp.txt

Hope u will find this post very useful.

Cheers

Regards,
Adityanath

SQL*Plus command line history on UNIX platform

One of the best feature I came across recently. Oracle SQL*Plus is now support command line history similar to UNIX “history” command. This feature is available from Oracle 12.2.

Sample example is given below:

You can refer below link for more details:

SQL*Plus HISTORY

Hope u will find this post very useful. 🙂

Cheers

Regards,
Adityanath