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
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:
What this program does:
This program needs following inputs from user side:
At the end of execution, it provides following details about each tablespace in database.
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:
Hope u will find this post very useful.
Stay tuned for script to monitor FRA usage details in an Oracle DB
Cheers
Regards,
Adityanath
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:
Hope u will find this post very useful.
Cheers
Regards,
Adityanath
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:
Hope u will find this post very useful.
Cheers
Regards,
Adityanath
Today while applying “Patch 30501910: GI RELEASE UPDATE 19.6.0.0.0” on recently upgrade GRID home from 12.2 to 19.6, I received error related ACFS ==> CLSRSC-196: ACFS driver install actions failed.
Command used for patching ==> opatchauto apply -oh /u01/app/19.6.0.0/grid /u05/ADI/patch/30501910
Detailed error is as given below:
Execution of [SIHAStartupAction] patch action failed, check log for more details. Failures: Patch Target : test-oem01->/u01/app/19.6.0.0/grid Type[siha] Details: [ ---------------------------Patching Failed--------------------------------- Command execution failed during patching in home: /u01/app/19.6.0.0/grid, host: test-oem01. Command failed: /u01/app/19.6.0.0/grid/perl/bin/perl -I/u01/app/19.6.0.0/grid/perl/lib -I/u01/app/19.6.0.0/grid/opatchautocfg/db/dbtmp/bootstrap_test-oem01/patchwork/crs/install -I/u01/app/19.6.0.0/grid/opatchautocfg/db/dbtmp/bootstrap_test-oem01/patchwork/xag /u01/app/19.6.0.0/grid/opatchautocfg/db/dbtmp/bootstrap_test-oem01/patchwork/crs/install/roothas.pl -postpatch -norestart Command failure output: Using configuration parameter file: /u01/app/19.6.0.0/grid/opatchautocfg/db/dbtmp/bootstrap_test-oem01/patchwork/crs/install/crsconfig_params The log of current session can be found at: /u01/app/crsdata/test-oem01/crsconfig/hapatch_2020-03-17_01-46-48PM.log 2020/03/17 13:47:16 CLSRSC-329: Replacing Clusterware entries in file '/etc/inittab' 2020/03/17 13:47:42 CLSRSC-196: ACFS driver install actions failed After fixing the cause of failure Run opatchauto resume ] OPATCHAUTO-68061: The orchestration engine failed. OPATCHAUTO-68061: The orchestration engine failed with return code 1 OPATCHAUTO-68061: Check the log for more details. OPatchAuto failed. OPatchauto session completed at Tue Mar 17 13:47:43 2020 Time taken to complete the session 16 minutes, 44 seconds opatchauto failed with error code 42
After investigating through patch log, I did see below additional details about the error:
> exec(): 0509-036 Cannot load program /usr/lib/methods/udefacfsctl.bin because of the following errors: > 0509-150 Dependent module libhasgen12.so could not be loaded. > 0509-022 Cannot load module libhasgen12.so. > 0509-026 System error: A file or directory in the path name does not exist. > ACFS-9361: Removing device 'acfsctl' failed with error code '255'. > ACFS-9294: updating file /etc/oracledrivers.conf > ACFS-9178: Return code = USM_FAIL > ACFS-9177: Return from 'uninstall' > ACFS-9305: ADVM/ACFS installation cannot proceed: > ACFS-9306: Failed to uninstall previous installation.
So issue was pointing failed uninstall of ACFS from previous installation.
After checking on MOS, I found a note which given some idea about this behavior: AIX: ROOT.SH FAILS WITH CLSRSC-196: ACFS DRIVER INSTALL ACTIONS FAILED (Doc ID 1929899.1)
Most likely this was due to existence of older version of ACFS lib/bin files in /usr/lib/methods which prevents the new version installation. This was verified using strings command as given below:
test-oem01:/u01/app/19.6.0.0/grid/lib # strings /usr/lib/methods/udefacfsctl.bin @(#)23 1.6 src/bos/usr/ccs/lib/libpthreads/init.c, libpth, bos610 6/21/07 15:28:59 @(#)61 1.16 src/bos/usr/ccs/lib/libc/__threads_init.c, libcthrd, bos610 8/2/07 13:09:21 脺矛每每每每 12.2.0.1.0 USM_VERSION-12.2.0.1.0.0
So we got the culprit which caused this issue. Files in /usr/lib/methods are still in older version.
Now big question was to how to fix this.
We had to deinstall older version of ACFS so to fix this:
Step 1: Copy files from new GRID home to /usr/lib/methods/
# cd /<19C GRID HOME>/usm/install/cmds/bin
# cp cfgacfsctl.bin cfgadvmctl.bin cfgadvmvol.bin defacfsctl.bin defadvmctl.bin ucfgacfsctl.bin ucfgadvmctl.bin ucfgadvmvol.bin udefacfsctl.bin udefadvmctl.bin /usr/lib/methods/
Step 2: Deinstall older ACFS
# /usr/lib/methods/ucfgacfsctl -l ofsctl
# /usr/lib/methods/ucfgadvmctl -l advmctl
# /usr/lib/methods/udefacfsctl -l ofsctl
# /usr/lib/methods/udefadvmctl -l advmctl
# /usr/sbin/rmauth -h oracle
# rmrole oracle_devmgmt
# setkst
# rm /usr/lib/drivers/oracle*
# rm /usr/lib/methods/*advm* /usr/lib/methods/*acfs*
# rm -rf /sbin/helpers/acfs
# rm /usr/sbin/acfsutil* /usr/sbin/advmutil*
# rm /sbin/acfsutil* /sbin/advmutil*
Step 3: Disable HAS
#/<19C GRID HOME>/bin/crsctl disable has
Step 4: Reboot server
Step 5: Enable HAS
#/<19C GRID HOME>/bin/crsctl eanble has
Once the above task is done, I attempted to resume my previous failed patching session using below command:
opatchauto resume -log <logfile of previous failed patching session>
This time patching did complete successfully without any errors 馃檪
Hope u will find this post very useful.
Cheers
Regards,
Adityanath
Dear Readers,
I have published two more articles on KT EXPERTS. This articles focuses on new DB features that have been introduced in Oracle 18C along with its significance.
Oracle 18C new feature: Cancel SQL instead of killing session
Oracle 18C new feature: Schema Only Account
Hope u will find this post very useful.聽馃檪
Stay tuned for More articles on Oracle 18c.
Cheers
Regards,
Adityanath
