Category Archives: Oracle

In this we are handling Oracle Database Administration and development task. If provide solution of ORA Errors and configuration Steps for setup in Oracle.

Create & grant permission to directory in Oracle

Create, grant permission, modify, drop database directory in Oracle

Create directory in Oracle Database

Create directory dir_name as 'D:\scripts';

Grant read write permission to Directory:

--grant read permission
GRANT read on DIRECTORY dir_name to user_name;
--grant write permission
GRANT write on DIRECTORY dir_name to user_name;
-- grant both
GRANT READ,WRITE ON DIRECTORY dir_Name TO user_Name;

Revoke permission from directory

-- revoke read permission
REVOKE read on DIRECTORY dir_name FROM User_Name;
-- revoke write permission
REVOKE write on DIRECTORY dir_name FROM User_Name;
-- revoke both
REVOKE read,write on Directory dir_name from user_name;

Modify path of directory

SQL> create or replace directory dbscript as 'D:\';
Directory created.
SQL> create or replace directory dbscript as 'D:\scripts';
Directory created.

Drop the directory

DROP Directory dbscript;

Advertisements

Script for monitoring windows drive with oracle tablespace space

Script for monitoring windows drive with oracle tablespace space

Created the script for monitoring the tablespace and windows drive with UTL_MAIL functionality.
Script is divided into two parts:
1. Using windows command (bat file) to get info into CSV file of windows drive information.
2. Use procedure for fetching tablespace information and use external function for using CSV data as a table and then convert both information into html format and send the Mail to user.

Output:

Utilizationreport

Steps to Configure

1. Create the RUNDISKINFO.bat script which scheduled in Task Scheduler.
Note: I am using D:\scripts location of the script change according to your need

Save it as rundiskinfo.bat
D:\scripts\diskinfo.bat localhost > D:\scripts\diskinfo.csv

2. Create the diskinfo.bat file.

@ECHO OFF
REM IF "%~1"=="" goto help

@SETLOCAL ENABLEEXTENSIONS
@SETLOCAL ENABLEDELAYEDEXPANSION

@FOR /F "skip=1 tokens=1" %%x IN ('"WMIC /node:"%1" LOGICALDISK GET Name " ') DO (
REM @ECHO %%x

@FOR /F "tokens=1-3" %%n IN ('"WMIC /node:"%1" LOGICALDISK GET Name,Size,FreeSpace | find /i "%%x""') DO ( @SET FreeBytes=%%n & @SET TotalBytes=%%p

SET TotalGB=0
SET FreeGB=0

REM Parameter value used to convert in GB
set num1=1074

@echo %%x,!TotalBytes!,!FreeBytes!
)
)
goto end
:error
echo.
echo *** Invalid server or drive specified ***
echo.
goto help

:help
echo.
echo diskfree.cmd
echo.
echo Queries remote server for free disk space.
echo Specify a MACHINENAME and a drive letter to be queried
echo.
echo Example: diskfree.cmd MACHINENAME c:
echo.
goto end
:end

3. Run the RUNDISKINFO.BAT will generate a file of D:\scripts\diskinfo.csv as output.

4. Now create an directory in database which is used by external table to read this diskinfo.csv file.

--create directory as location of CSV file.
Creete directory dbscript as 'D:\scripts';
--I am using sys user so need not to give permission.
GRANT READ, WRITE ON DIRECTORY MY_DIR TO USER;

5. Create external table in the Oracle

----drop table ext_diskinfo;
CREATE TABLE ext_diskinfo
(DISK_ID VARCHAR2(30),
TOTAL_SPACE VARCHAR2(30),
FREE_SPACE VARCHAR2(30)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY dbscript
ACCESS PARAMETERS
(
records delimited by newline
badfile dbscript:'ext_diskinfo%a_%p.bad'
logfile dbscript:'ext_diskinfo%a_%p.log'
fields terminated by ','
missing field values are null
( DISK_ID , TOTAL_SPACE, FREE_SPACE
)
)
LOCATION ('diskinfo.csv')
)
PARALLEL
REJECT LIMIT UNLIMITED;

6. Test the External table is giving output of Drive information

select DISK_ID,TOTAL_SPACE/1024/1024/1024,FREE_SPACE/1024/1024/1024 from ext_diskinfo where TOTAL_SPACE is not null;

7. Create procedure for fetching tablespace & disk info. Send them through mail.

-- exec Proc_emailalert_tablespace_70
set serveroutput on

create or replace procedure Proc_emailalert_tablespace_70
as
var_name VARCHAR2(35);
var_hostname VARCHAR2(25);
var_mode VARCHAR2(10);
var_result NUMBER;
var VARCHAR2(5000);
var_Date varchar2(15);

-- Cursor defining query which fetch all data
CURSOR cn IS
SELECT a.tablespace_name,
a.bytes_alloc/(1024*1024*1024) "TOTAL_ALLOC_GB",
a.physical_bytes/(1024*1024*1024) "TOTAL_PHYS_ALLOC_GB",
nvl(b.tot_used,0)/(1024*1024*1024) "USED_GB",
(a.bytes_alloc-b.tot_used)/(1024*1024*1024) "FREE_GB",
(nvl(b.tot_used,0)/a.bytes_alloc)*100 "PERC_USED"
FROM
(SELECT tablespace_name,
sum(bytes) physical_bytes,
sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
FROM dba_data_files
GROUP BY tablespace_name ) a,
(SELECT
tablespace_name,
sum(bytes) tot_used
FROM dba_segments
GROUP BY tablespace_name ) b
WHERE a.tablespace_name = b.tablespace_name (+)
and a.tablespace_name not in
(SELECT distinct tablespace_name FROM dba_temp_files)
--and a.tablespace_name not like 'UNDO%'
--and ((nvl(b.tot_used,0)/a.bytes_alloc)*100) > 70
ORDER BY 1;

--Cursor for fetching data from CSV file external table
Cursor diskinfo is
select DISK_ID,TOTAL_SPACE/1024/1024/1024 "TOTAL_GB",FREE_SPACE/1024/1024/1024 "FREE_GB",(TOTAL_SPACE-FREE_SPACE)/1024/1024/1024 "USED_GB",
(nvl((TOTAL_SPACE-FREE_SPACE),0)/TOTAL_SPACE)*100 "PERC_USED" from ext_diskinfo
where TOTAL_SPACE is not null;

-- Begin of coding
BEGIN

var_result := 0;
var := '';

-- Get database Name and Status
SELECT name,open_mode into var_name,var_mode FROM v$database;

--Get host name of machine
SELECT host_name into var_hostname FROM v$instance;

--Get today date
SELECT to_char(sysdate,'DD-MON-YYYY') into var_Date FROM dual;

--Define header of the table in html format
var := var||'<table border="1"><tbody>
<tr>
<th colspan="6" >Report for Tablespace Utilization dated: '||var_date||'</t>
</tr>
<tr>
<td colspan="6">Hostname: '||var_hostname||'</td>
</tr>
<tr>
<td colspan="6">DB Name: '||var_name||'</td>
</tr>
<tr>
<th>Tablespace Name</th>
<th>Alloc Space(GB)</th>
<th>Physical Alloc(GB)</th>
<th>Used(GB)</th>
<th>Free(GB)</th>
<th>Percent Used</th>
</tr>';

-- Loop for calculating multiple tablespace output
for data_cur in cn
loop
var_result := 1;

var := var ||'<tr>
<td>'||data_cur.tablespace_name||'</td>
<td>'||to_char(DATA_CUR.TOTAL_ALLOC_GB,9999999.99)||'</td>
<td>'||to_char(data_cur.TOTAL_PHYS_ALLOC_GB,9999999.99)||'</td>
<td>'||to_char(data_cur.USED_GB,9999999.99)||'</td>
<td>'||to_char(data_cur.FREE_GB,9999999.99)||'</td>
<td>'||to_char(data_cur.perc_used,999.99)||'</td>
</tr>';
end loop;

var := var || '<tr><th colspan="6">Disk information</th></tr>';

var := var||'<tr>
<th colspan=2>Disk Drive</th>
<th>Total(GB)</th>
<th>Used(GB)</th>
<th>Free(GB)</th>
<th>Percent Used</th>
</tr>';

--- procedure for output the diskspace ----
for data_cur1 in diskinfo
loop
var_result := 1;

var := var ||'<tr>
<td colspan=2>'||data_cur1.disk_id||'</td>
<td>'||to_char(DATA_CUR1.Total_GB,9999999.99)||'</td>
<td>'||to_char(data_cur1.USED_GB,9999999.99)||'</td>
<td>'||to_char(data_cur1.FREE_GB,9999999.99)||'</td>
<td>'||to_char(data_cur1.perc_used,999.99)||'</td>
</tr>';
end loop;

var := var||'</tbody></table><br></br>';

-- Sending Mail Utility in HTML format.

if var_Result > 0 then
UTL_MAIL.send(sender => 'sqlalerts@gmail.com',
recipients => 'sqlalerts@gmail.com',subject => 'Tablespace utilized info',message => var,mime_type => 'text/html;charset=us-ascii' );

end if;

end;
/

8. Execute the procedure

exec Proc_emailalert_tablespace_70

9. If you getting error then please check SMTP parameter and utl mail is configured on DB Server.

Start and Stop window Services from command prompt using NET commands

Start and Stop window Services from command prompt using NET commands

NET START: Name of all services present in the windows services which is running

D:\ImageCenter\Database\6dbcheck>net start
These Windows services are started:

Adobe Acrobat Update Service
Application Information
Application Management
COM+ Event System
OracleServiceXE
OracleXETNSListener

SC QUERY command give you only service name which you specify

D:\ImageCenter\Database\6dbcheck>sc query OracleServiceXE

SERVICE_NAME: OracleServiceXE
TYPE : 10 WIN32_OWN_PROCESS
STATE : 4 RUNNING
(STOPPABLE, PAUSABLE, ACCEPTS_SHUTDOWN)
WIN32_EXIT_CODE : 0 (0x0)
SERVICE_EXIT_CODE : 0 (0x0)
CHECKPOINT : 0x0
WAIT_HINT : 0x0

SC QUERY command give you all services details as follows

D:\ImageCenter\Database\6dbcheck>sc query
SERVICE_NAME: OracleServiceXE
DISPLAY_NAME: OracleServiceXE
TYPE : 10 WIN32_OWN_PROCESS
STATE : 4 RUNNING
(STOPPABLE, PAUSABLE, ACCEPTS_SHUTDOWN)
WIN32_EXIT_CODE : 0 (0x0)
SERVICE_EXIT_CODE : 0 (0x0)
CHECKPOINT : 0x0
WAIT_HINT : 0x0

SERVICE_NAME: OracleXETNSListener
DISPLAY_NAME: OracleXETNSListener
TYPE : 10 WIN32_OWN_PROCESS
STATE : 4 RUNNING
(STOPPABLE, NOT_PAUSABLE, IGNORES_SHUTDOWN)
WIN32_EXIT_CODE : 0 (0x0)
SERVICE_EXIT_CODE : 0 (0x0)
CHECKPOINT : 0x0
WAIT_HINT : 0x0

NET STOP : Stop the service from windows command prompt

net stop OracleXETNSListener
C:\Windows\system32>net stop OracleXETNSListener
The OracleXETNSListener service is stopping.
The OracleXETNSListener service was stopped successfully.

net start OracleServiceXE
C:\Windows\system32>net stop OracleServiceXE
The OracleServiceXE service is stopping...................
The OracleServiceXE service was stopped successfully.

NET START : Start the service from windows command prompt

net start OracleXETNSListener
C:\Windows\system32>net start OracleXETNSListener
The OracleXETNSListener service is starting.
The OracleXETNSListener service was started successfully.

net start OracleServiceXE
C:\Windows\system32>net start OracleServiceXE
The OracleServiceXE service is starting...............
The OracleServiceXE service was started successfully.

Batch script for getting drives information in GB & percentage

Batch script for getting drives information in GB & percentage

Following script provide you the drive information in GB & percentage used in bat file script. Simple copy & paste it in bat file and execute it.

Run instruction as following:
1. Save this file as name diskinfo.bat
2. Open the command prompt window
3. Go to the location of script
4. Run this as:
diskinfo localhost
5. Output as shown in snapshot:

diskinfo

Script:

@ECHO OFF
IF "%~1"=="" goto help

@SETLOCAL ENABLEEXTENSIONS
@SETLOCAL ENABLEDELAYEDEXPANSION

@FOR /F "skip=1 tokens=1" %%x IN ('"WMIC /node:"%1" LOGICALDISK GET Name " ') DO (
REM @ECHO %%x

@FOR /F "tokens=1-3" %%n IN ('"WMIC /node:"%1" LOGICALDISK GET Name,Size,FreeSpace | find /i "%%x""') DO ( @SET FreeBytes=%%n & @SET TotalBytes=%%p

SET TotalGB=0
SET FreeGB=0

REM Parameter value used to convert in GB
set num1=1074

REM Parameter value used to convert in MB or KB
REM set num1 = 1049

REM @ECHO Total space: !TotalBytes!

SET /a TotalSpace=!TotalBytes:~0,-6! / !NUM1!
SET /a FreeSpace=!FreeBytes:~0,-7! / !NUM1!

SET TotalGB=!TotalSpace!
SET FreeGB=!FreeSpace!

SET PERNUM=100

SET /A TotalUsed=!TotalSpace! - !FreeSpace!
SET /A MULTIUSED=!TotalUsed!*!PERNUM!
SET /A PERCENTUSED=!MULTIUSED!/!TotalGB!

REM IF !TotalSpace! LSS 0 goto error

@echo.
@echo.
@echo Drive: %%x
@ECHO ===========================
@ECHO Total space: !TotalGB! GB
@ECHO Free space : !FreeGB! GB
@ECHO PERCENTUSED : !PERCENTUSED!
REM @SET TotalSpace=
REM @SET FreeSpace=
REM @SET TotalUsed=
REM goto end
)
)
goto end
:error
echo.
echo *** Invalid server or drive specified ***
echo.
goto help

:help
echo.
echo diskfree.cmd
echo.
echo Queries remote server for free disk space.
echo Specify a MACHINENAME and a drive letter to be queried
echo.
echo Example: diskfree.cmd MACHINENAME c:
echo.
goto end

:end

ORA-28040: No matching authentication protocol error

ORA-28040: No matching authentication protocol error

SQLNET parameters ALLOWED_LOGON_VERSION_CLIENT & ALLOWED_LOGON_VERSION_SERVER issue in Oracle 12c.
Set the value to the minimum authentication protocol allowed while making connection to Oracle Database instances.

Note: Version refer to the authentication protocol its not mean that oracle release version.

Error:

Caused by: java.sql.SQLException: ORA-28040: No matching authentication protocol”
ORA-28040: No matching authentication protocol error
ORA-03134: Connections to this server version are no longer supported error.

Solution:
1. Set the following value in SQLNET.ora file in Oracle database Server:

SQLNET.ALLOWED_LOGON_VERSION_SERVER=8
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8

Note: value permit all most values for authentication protocol.

2. You can also check DBA_USERS table.

select username,password_versions from DBA_USERS
--Password Version has value 10G, 11G, and 12C

Example:
Suppose application is using client version 9i then for connectivity need to set following parameters as follows:

SQLNET.ORA FILE : SQLNET.ALLOWED_LOGON_VERSION_SERVER value to 8.
DBA_USERS table have column PASSWORD VERSION its value should be 8.

SQLNET.ALLOWED_LOGON_VERSION_CLIENT
minimum authentication protocol allowed for clients and used when a server is acting as a client.

Example:
If ORacle database 12c has database link to Oracle 10g database for some information then need to set the paremeter in sqlnet.ora of oracle 12c because it act as client of oracle 10g database.
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=10

Note: Version refer to the authentication protocol its not mean that oracle release version.

Value of Parameter

12a for Oracle Database 12c 12.1.0.2 or later
12 for the critical patch updates CPUOct2012 and later Oracle Database 11g authentication protocols
11 for Oracle Database 11g authentication protocols ----- (default)
10 for Oracle Database 10g authentication protocols
8 for Oracle8i authentication protocol

Create or attach the central inventory in Oracle Database

Create or attach the central inventory in Oracle Database

1. Find the existing location of Central inventory.

Windows
1. By default Central Inventory location in windows is at “C:\Program Files\Oracle\Inventory”.
2. If you don’t find then you will check the “inst_loc” key in Window registry:
Go to Regedit in research --> Open Registry editor --> HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE --> Find "inst_loc".
3. Find in 64-bit command prompt as follows:
reg query HKLM\SOFTWARE\ORACLE /v inst_loc
4. Find in 32-bit with command prompt:
reg query HKLM\SOFTWARE\WOW6432Node\ORACLE /v inst_loc

Unix or Linux
1. Default Central inventory location present in the file /etc/oraInst.loc (AIX and Linux) or /var/opt/oracle/oraInst.loc (other platform)
2. If you don’t find then you can create the file with oraInst.loc please take care of capital or small letters Linux is case sensitivity.
Example:
inventory_loc=/u01/oracle/oraInventory
inst_group=oinstall

2. If already exists then rename the old inventory and if not exists then create the default location as mentioned in above step.

3. Go to the OUI directory in your command prompt or command shell as follows:

-- Windows:
cd %ORACLE_HOME%\oui\bin

--Linux or Unix:
cd $ORACLE_HOME/oui/bin

4. Attach the home or create the home with runInstaller script as follows:

-- Windows:
setup.exe -silent -ignoreSysPrereqs -attachHome ORACLE_HOME="" ORACLE_HOME_NAME=""

--linux:
./runInstaller -silent -ignoreSysPrereqs -attachHome ORACLE_HOME="" ORACLE_HOME_NAME=""

5. For Example you need to set Oracle_home and Oracle_home_name as value to attached the Oracle Home with new inventory or existing inventory:

./runInstaller -silent -ignoreSysPrereqs -attachHome ORACLE_HOME="/u01/oracle/product/12.1.0" ORACLE_HOME_NAME="OraDb12c_home1"

Note: If you have more than one home then execute last step by changing oracle_home and oracle_home_name parameter

Check the central inventory in Oracle Database at Windows or Unix platform

Check the central inventory in Oracle Database at Windows or Unix platform

Windows

1. By default Central Inventory location in windows is at “C:\Program Files\Oracle\Inventory”.
2. If you don’t find then you will check the “inst_loc” key in Window registry:
Go to Regedit in research --> Open Registry editor --> HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE --> Find "inst_loc".
3. Find in 64-bit command prompt as follows:
reg query HKLM\SOFTWARE\ORACLE /v inst_loc
4. Find in 32-bit with command prompt:
reg query HKLM\SOFTWARE\WOW6432Node\ORACLE /v inst_loc

Unix or Linux

1. Default Central inventory location present in the file /etc/oraInst.loc (AIX and Linux) or /var/opt/oracle/oraInst.loc (other platform)
2. If you don’t find then you can create the file with oraInst.loc please take care of capital or small letters Linux is case sensitivity.

Example:
inventory_loc=/u01/oracle/oraInventory
inst_group=oinstall