Author Archives: SandeepSingh DBA

About SandeepSingh DBA

Hi, I am working in IT industry with having more than 10 year of experience, worked as an Oracle DBA with a Company and handling different databases like Oracle, SQL Server , DB2 etc Worked as a Development and Database Administrator.

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.

Advertisements

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

Check and set the database and session time zone in Oracle

Setting database and session time zone in Oracle

Oracle choose default time zone as operating system during installation of Oracle. If you want to set the database time zone at create time then use SET TIME_ZONE with CREATE DATABASE statement.

Set the time zone at database level

-- Check the time zone
SELECT dbtimezone FROM DUAL;
DBTIME
------
+00:00

--Change the time zone of database
ALTER DATABASE SET TIME_ZONE='Europe/London';
OR
ALTER DATABASE SET TIME_ZONE='-05:00';

--restart the database
Shutdown immediate;
startup

-- Check the time zone
SELECT dbtimezone FROM DUAL;
DBTIME
------
-05:00

Set the time zone at session level

-- use alter session commands
ALTER SESSION SET TIME_ZONE=local;
ALTER SESSION SET TIME_ZONE=dbtimezone;
ALTER SESSION SET TIME_ZONE='Asia/Hong_Kong';
ALTER SESSION SET TIME_ZONE='+10:00';

SQL> SELECT sessiontimezone FROM DUAL;
SESSIONTIMEZONE
---------------
+05:30

SQL> alter session set time_zone='+10:00';
Session altered.

SQL> SELECT sessiontimezone FROM DUAL;
SESSIONTIMEZONE
---------------
+10:00

Convert timezone from one to another

SELECT FROM_TZ(CAST(TO_DATE('2018-11-02 03:00:00','YYYY-MM-DD HH:MI:SS') AS TIMESTAMP), '+5:30') AT TIME ZONE '-6:00' "West Coast Time" FROM DUAL;

Understand and Set Time Zone datatime datatype in Oracle

Understand and Set Time Zone datatime datatype in Oracle

DATETIME DATATYPE
This has different data type which will support different format.

DATE:
It store date and time as value.Default date format can be defined from NLS_DATE_FORMAT and NLS_DATE_LANGUAGE parameters.

EXAMPLE
DATE 'YYYY-MM-DD'

--Set the date format at session level
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';

TIMESTAMP:
It store additional fraction seconds plus date datatype format as value. By default it use NLS_TIMESTAMP_FORMAT and NLS_DATE_LANGUAGE parameters.
Note: If you insert data with timezone in this TIMESTAMP datatype then it will skip timezone.

Example
TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.FF'

--Set the timestamp format
ALTER SESSION SET NLS_TIMESTAMP_FORMAT='DD-MON-YY HH:MI:SSXFF';

TIMESTAMP WITH TIME ZONE:
It includes a time zone region name or time zone offset in its value (it is difference in hours & mins between local or universal time(GMT). Default NLS_TIMESTAMP_TZ_FORMAT parameter.

Example:
TIMESTAMP '1997-01-31 09:26:56.66 +02:00'
TIMESTAMP '1999-01-15 8:00:00 America/Los_Angeles'
--For day light saving PDT used
TIMESTAMP '1999-10-29 01:30:00 America/Los_Angeles PDT'

--set the timestamp format.
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH:MI:SSXFF AM TZR';
ALTER SESSION SET TIME_ZONE='-7:00';

TIMESTAMP WITH LOCAL TIME ZONE
It stored in the database is normalized to the database time zone, and the time zone offset is not stored as part of the column data.
When users retrieve the data, Oracle Database returns it in the users’ local session time zone. By default NLS_TIMESTAMP_FORMAT parameter.

Example of using Local Time Zone:
It will change the time according to your local time while you inserting into the database:
--set the timestamp format.
ALTER SESSION SET TIME_ZONE='-7:00';
CREATE TABLE table_tsltz (c_id NUMBER, c_tsltz TIMESTAMP WITH LOCAL TIME ZONE);
INSERT INTO table_tsltz VALUES(1, '01-JAN-2003 2:00:00');
INSERT INTO table_tsltz VALUES(3, TIMESTAMP '2003-01-01 2:00:00 -08:00');

SELECT * FROM table_tsltz;

C_ID C_TSLTZ
---------- ------------------------------------
1 01-JAN-03 02.00.00.000000 AM
2 01-JAN-03 02.00.00.000000 AM
3 01-JAN-03 03.00.00.000000 AM

Check all timezone present in Oracle

SELECT TZNAME, TZABBREV FROM V$TIMEZONE_NAMES ORDER BY TZNAME, TZABBREV;

Check Database time zone
Returns the value of the database time zone.

SQL> select dbtimezone from dual
DBTIME
------
+00:00

Check the current_Date
Returns the current date in the session time zone as date datatype.

Select current_Date from dual;
CURRENT_D
---------
02-NOV-18

Check current_timestamp
Returns the current date and time in the session time zone as a TIMESTAMP WITH TIME ZONE datatype.

select current_timestamp from dual;
CURRENT_TIMESTAMP
-------------------------------------
02-NOV-18 10.56.04.000000 AM +05:30

Check the localtimestamp
Returns the current date and time in the session time zone as timestamp datatype

select localtimestamp from dual;
LOCALTIMESTAMP
----------------------
02-NOV-18 10.56.04.000000 AM