Search Articles

ORA-3136: Inbound Connection Timed Out

Few days back we get an issue Inbound connection timed out (ORA-3136) in alert log. To troubleshoot this we need to dig into sqlnet.ora file and the alert log. 

ora-3136: inbound connection timed out


The following four points are very important to troubleshoot this issue: 
  • Check the alert log file and check from where the connection comes.
  • Check the listener is up & running.
  • Ping the server,make sure tnsping is working.
  • Increase inbound_connect_timeout 

Regarding this we need to checked the value of "inbound_connect_timeout".

LSNRCTL> show inbound_connect_timeout
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
LISTENER parameter "inbound_connect_timeout" set to 120
The command completed successfully

Here we can see that time is set to 120 which is ok.

The following would be the most likely reasons for this error :

1.Server gets a connection request from a malicious client which is not supposed to connect to the database.  In this case the error thrown would be the expected and desirable behaviour. You can get the client address for which the error was thrown in the sqlnet.log file that is local to the database.

2.The server receives a valid client connection request but the client takes a long time to authenticate more than the defined timeout.

3.The DB server is heavily loaded due to which it cannot finish the client logon within the timeout specified.


By default, the SQLNET.INBOUND_CONNECT_TIMEOUT is set to 60 seconds.
You can change the setting by adding the parameters SQLNET.INBOUND_CONNECT_TIMEOUT and INBOUND_CONNECT_TIMEOUT_<listener name>  to the $ORACLE_HOME/network/admin/sqlnet.ora file on the database server.

Setting the above parameters to a value of 0 implies an infinite time out.
Alternatively, you can use the lsnrctl command and issue the following:

LSNRCTL> set inbound_connect_timeout=<value>
Before opting for the above parameter changes, confirm that any firewall activity or Network Address Transalation (NAT) that may be occuring beween the client and and the database are not the cause of latency which is exceeding the timeout threshold.

 To identify the listener name and ORACLE_HOME and sqlnet.ora file we can use the below command.

[oracle@racdbq1.dcb.ichotels.com] ps -eaf|grep tns

Read more ...

ORA-01950: no privileges on tablespace users in Oracle

Few days back, an error was reported in the alert log which was due to no privileges on tablespace for auto execution of job as shown below.


ORA-01950: no privileges on tablespace


ORA-12012: error on auto execute of job "ORAHOW"."DB_GROWTH_JOB"
ORA-01950: no privileges on tablespace 'ORA_DATA'
ORA-06512: at "ORAHOW.PROC_DB_GROWTH_INFO", line 4


After looking into it we found that user doesn't have privileges to allocate an extent in the specified tablespace. So using the below query we get the object type and the status of the object.

SQL> select owner,object_name,object_type,status from dba_objects where  object_name='DB_GROWTH_JOB';

OWNER          OBJECT_NAM      OBJECT_TYPE       STATUS
-------- ---------- ------------------------------------
ORAHOW      DB_GROWTH_ JOB      JOB            VALID

      

Now we will check the user default tablespace on which we need to give privileges.
SQL> select USERNAME, DEFAULT_TABLESPACE from dba_users where USERNAME='ORAHOW';

USERNAME                 DEFAULT_TABLESPACE
-----------------------------------------------
ORAHOW                     ORA_DATA


As you can see that user ORAHOW is using default tablespace ORA_DATA, but we also need to check the amount of space allocated for that user on that tablespace using the below query.
SQL> select TABLESPACE_NAME,USERNAME,MAX_BYTES,BYTES from dba_ts_quotas where USERNAME='ORAHOW';

no rows selected

Using the above query we can see that this user doesn't have access to this tablespace, so we will assign unlimited quota on this tablespace for this user.
SQL> alter user ORAHOW DEFAULT TABLESPACE ORA_DATA quota unlimited on ORA_DATA;

User altered.
If you want you can assign limited quota to that user.
ALTER USER <username> QUOTA 100M ON <tablespace name>
GRANT UNLIMITED TABLESPACE TO <username> 


Now will verify the above changes using the following query.
SQL>  select TABLESPACE_NAME,USERNAME,MAX_BYTES,BYTES from dba_ts_quotas where USERNAME='ORAHOW';

TABLESPACE_NAME        USERNAME         BYTES         MAX_BYTES
---------------------------------------------------------------
ORA_DATA                 ORAHOW          786432           -1
  


Finally, after assigning privileges on tablespace to the user we will execute the job.
SQL> exec DBREPORT.PROC_DB_GROWTH_INFO

PL/SQL procedure successfully completed. 
Read more ...

Linux Crontab: Cron and Crontab usage and Examples


Crontab-Examples


Linux Crontab Format - Cron Job Sceduling, usage with Examples


The content of the file can be divided in to 6 fields(first 5 for specifying time and last one for executing scripts/app etc). The first five fields are separated by either a space or a tab and represent the following units, respectively:
* minutes (0-59)

* hour (0-23)

* day of the month (1-31)

* month of the year (1-12)

* day of the week (0-6, 0=Sunday)

An example of crontab format with commented fields is as follows:

# Minute   Hour    Day of Month      Month           Day of Week        Command   
# (0-59)  (0-23)     (1-31)      (1-12 or Jan-Dec)  (0-6 or Sun-Sat) 
           

Allowed special character for job scheduling(*, -, /, ?, #)

Asterik(*) – Match all values in the field or any possible value
Hyphen(-) – To define range.
Slash (/) – 1st field /10 meaning every ten minute or increment of range.
Comma (,) – To separate items.
Hash  (#) - To comment job, or to make a job inactive.



1. To View/List Crontab Entries
To List or view crontab job entries you can use crontab command with -l option, here you will see all the scheduled jobs.
# crontab -l

00 10 * * * /bin/ls >/ls.txt

2. To Edit Crontab Entries
To edit crontab entry, use -e option with crontab as shown below. After proper entry press escap :wq which saves the file and will make necessary changes after editing.
# crontab -e

3.To Remove Crontab Entries
Caution: Crontab with -r parameter will remove complete scheduled jobs without your confirmation from crontab. You can use -i option which confirm's you before removing entries.
# crontab -r

4. Ask Confirmation Before Deleting Crontab
crontab with -i option will prompt you confirmation from user before deleting user’s crontab.
# crontab -i -r

crontab: really delete root's crontab?

5. To Create Crontab for a User
As a root user you can create crontab entries for a particular user using the following command.
#crontab -e -u username

-e is for editing and -u for specifying user name when you are scheduling crontabs for other users. If users want to schedule their own he can just give (crontab -e). After entering the values save and exit the file. A file will be created in crontab file location as /var/spool/cron/orahow with the above content.

6. To View User’s Crontabs entries
To view crontab entries of other Linux users, login as root and use -u {username} -l as shown below.
[root@orahow]# crontab -u santosh -l
@monthly /home/santosh/monthly-backup
00 09-18 * * * /home/santosh/check-db-status 
7. To Check Cron Process
To find out what are the cron jobs which are running at any instant on a Solaris server, you can use ps command which list all the cron jobs which are running on the server. To Check if the 'cron' daemon is running.
ps -ef | grep cron

There should be a process: /usr/sbin/cron that started at the same time as the system booted (check with who -b).

You can 'bounce' the cron daemon, if you want, with:
/etc/init.d/cron stop
/etc/init.d/cron start

 Most frequently used Crontab Examples in Linux/Unix.


1. Schedule a cron to execute at 5 am daily. 
To schedule a job at 5 am daily, you can edit entries as shown below.
0 5 * * * /var/test/script.sh > /temp/script.out 

2. Schedule a cron to execute on every Sunday at 6 PM.
This type of cron are useful for doing weekly tasks, like backup, log rotation etc.

0 18 * * sun  /var/test/script.sh > /temp/script.out

3. Schedule a cron to execute on every 15 minutes.
If you want to run your script on 15 minutes interval, can configure entries like below. These type of crons are usefull for monitoring.

*/15 * * * * /var/test/script.sh > /temp/script.out

*/15: means to on each 15 minutes.

 4. Schedule a cron to execute on every six hours.
If you want to run script on 6 hours interval. It can be configure like below.

0 */6 * * * /var/test/script.sh > /temp/script.out 

5. To Schedule Job on every weekday(Mon to Fri) during the working hours 10 a.m – 6 p.m.

00 10-18 * * 1-5 /var/test/script.sh > /temp/script.out 

00 – 0th Minute
10-18 : At 10 am,11 am, 12 am, 1 pm, 2 pm, 3 pm, 4 pm, 5 pm, 6 pm
* : Every day
* : Every month
1-5 : Mon, Tue, Wed, Thu and Fri

6. To execute script on every 10th of each month at 11:45pm
Script located in /var/test/script.sh executed at given time and save output to the /temp/script.out.
45 23 10 * * /var/test/script.sh > /temp/script.out 

here * in month field and week field indicates any month and any week execute this script

7. To schedule job on every minute of every hour of every day of every month.
This line executes the "ping" command every minute of every hour of every day of every month. The standard output is redirected to dev null so we will get no e-mail but will allow the standard error to be sent as a e-mail. If you want no e-mail ever change the command line to "/sbin/ping -c 1 192.168.0.1 > /dev/null 2>&1".

*       *       *       *       *       /sbin/ping -c 1 192.168.0.1 > /dev/null 

You can also use some of the Special strings for job scheduling:

string         meaning
------         -------

@reboot        Run once, at startup.
----------------------------------------------
@yearly        Run once a year, "0 0 1 1 *".
----------------------------------------------
@annually      (same as @yearly)
----------------------------------------------
@monthly       Run once a month, "0 0 1 * *".
----------------------------------------------
@weekly        Run once a week, "0 0 * * 0".
----------------------------------------------
@daily         Run once a day, "0 0 * * *".
----------------------------------------------
@midnight      (same as @daily)
-----------------------------------------------
@hourly        Run once an hour, "0 * * * *".

 Schedule a job to execute on yearly basis.
@yearly timestamp is similar to “0 0 1 1 *”. This will execute job at 00:00 on Jan 1st for every year.
@yearly /var/test/script.sh 

Schedule a tasks to execute on monthly
@monthly timestamp is similar to “0 0 1 * *”. This will execute job at 00:00 on 1st of every month.
@monthly /var/test/script.sh 

 Schedule a tasks to execute on Weekly.
@weekly timestamp is similar to “0 0 1 * *”. It will execute task on first minute of month. It may usefull to do weekly tasks like cleanup of system etc.
@weekly /var/test/script.sh 

 Schedule a tasks to execute on daily ( @daily ).
@daily timestamp is similar to “0 0 * * *”. It will execute job at 00:00 on every day..
@daily /scripts/script.sh 

 Schedule a tasks to execute on hourly ( @hourly ).
@hourly timestamp is similar to “0 * * * *”. It will execute task on first minute of every hour.
@hourly /var/test/script.sh 

Schedule a tasks to execute on system reboot
@reboot is usefull for those tasks which you want to run on your system startup. It will be same as system startup scripts. It is usefull for starting tasks in background automatically.

@reboot /var/test/script.sh 
System Wide Cron Schedule
you can also use predefine cron directory for job scheduling as shown below.
/etc/cron.d
/etc/cron.daily
/etc/cron.hourly
/etc/cron.monthly
/etc/cron.weekly
So this is all about Linux crontab for cron job scheduling. Here we have explained basics and limited usage examples, if you are planning to deploy any script under crontab we recommend you to cross-verify again from experts or from similar sources.
Read more ...

Install Wine in RHEL/CentOS/Ubuntu 7.0/6.x/5.x and Fedora 21-12

Wine is used To Run Windows Software on Linux. Linux users generally search for some techniques using which they could install Windows .exe files like VLC, on Linux machine. In this article you will show you a step by step process on "how to install latest version of Wine on Red Hat - RHEL and Debian based operating systems like CentOS, Fedora, Ubuntu, Linux Mint and other supported distributions.


Install Wine on RedHat, Ubantu, CentOS, Fedora


Some of the Extra Features Added In this New Release:

  • New version of the Mono engine.
  • A few more functions implemented in MSHTML.
  • Improved support for restoring display mode.
  • Font metrics improvements in DirectWrite.
  • Various types of bug fixes in this new release.

How to install Wine on redhat Linux based operating Systems

Before wine installation we need to install some of the "Development tools" which is required to install the package smoothly. These tools are mostly required to compile and install them using YUM command. If previously installed no need to install again.

#yum groupinstall 'Development Tools'
# yum install libX11-devel freetype-devel zlib-devel libxcb-devel


 Download Wine Packages for Installation from Source using Wget Command

Download the latest version of source file using Wget command under /tmp directory as a normal User, you can also find the latest release from the wine website.

https://www.winehq.org/

$ cd /tmp

$ wget http://citylan.dl.sourceforge.net/project/wine/Source/wine-1.7.32.tar.bz2


Now, extract the downloaded tar file using the following command

$ tar -xvf wine-1.7.32.tar.bz2

To install extracted Wine Packages

Now, it’s time to compile and build Wine installer using the following commands as normal user, if asked for root password provide the same. Note: The installation process might take up-to 10-15 minutes depending upon your internet speed.


On 64-Bit Systems

$ cd wine-1.7.32/

$ ./configure --enable-win64

$ make

# make install


On 32-Bit Systems

# cd wine-1.7.32/

#./configure

# make

# make install

To Install Wine On Ubuntu, Debian and Linux Mint

Under Ubuntu based systems, you can easily install the latest development build of Wine using the official PPA. Open a terminal and run the following commands with sudo privileges.
$ sudo add-apt-repository ppa:ubuntu-wine/ppa

$ sudo apt-get update

$ sudo apt-get install -y wine1.7 winetricks

How to Install and run EXE files in Linux, RHEL, CentOS, Fedora, Ubantu

Navigate to the directory location of your windows application setup file and double click on it. It will automatically start the installation of the application (.exe) file. If it does not work then you can start the installation process from the terminal by typing the following command:

wine your_application_setup_file_name.exe

example:

wine VLC.exe

Read more ...

How to Find Out the size of your Oracle Database

Free-and-Used-Space-within-Oracle-Database
As an Oracle DBA this is the most important interview question and at any time you may face an issue where you need to find out the size of the Oracle Database. In detail, here you will get to know the actual size of the database that comprises of only data files when no redo and temp are generated. In second case, you may asked to find out the overall database size that contains all the data files, temp files and the redo logs (free+used space). Third, is the size occupied by data in this database or you can say  Database usage details which you can get using dba_segments. 


To Find the Actual size of the Database in GB

SELECT SUM (bytes) / 1024 / 1024 / 1024 AS GB FROM dba_data_files;

To Find the Size Occupied by Data in the Database 

Gives the size occupied by data in this database or Database usage details.
SELECT SUM (bytes)/1024/1024/1024 AS GB FROM dba_segments;

To Find the Overall/Total Database Size

Overall database size is the sum of used space plus free space i.e. the size of the data files, temp files, log files and the control files. You can find out the total database size using simple query. This sql gives the total size in GB.  
select
( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"
from
dual
Read more ...

Schema and Non-Schema Objects in Oracle Database

Oracle database contains schema objects like views, tables, triggers etc., and several other types of objects which are also stored in the database but are not contained in a schema.

A schema is a collection of logical structures of data, or schema objects which is owned by a database user and has the same name as that of the user. Schema objects can be created and manipulated with SQL and include the following types of objects:




Types of Schema Objects

Schema objects are logical data storage structures which do not have a one-to-one correspondence to physical files on disk that store their information. However, Oracle Database stores a schema object logically within a tablespace of the database. The data of each object is physically contained in one or more of the tablespace's datafiles.
  • Tables and index-organized tables
  • Constraints
  • Views
  • Database links
  • Database triggers
  • Dimensions
  • External procedure librarie
  • Indexes and indextypes
  • Java classes, Java resources, and Java sources
  • Materialized views and materialized view logs
  • Object tables, object types, and object views
  • Operators
  • Sequences
  • Stored functions, procedures, and packages
  • Synonym
  • Tables and index-organized tables
  • Clusters 

Types of NON-SCHEMA Objects

There are several other types of objects which are also stored in the database but are not contained in a schema are:
  • Contexts
  • Directories
  • Parameter files (PFILEs) and server parameter files (SPFILEs)
  • Profile
  • Roles
  • Rollback segments
  • Tablespaces
  • User
 For some objects, such as tables, indexes, and clusters, you can specify how much disk space Oracle Database allocates for the object within the tablespace's datafiles.

Read more ...

How To Find DBID in NOMOUNT State

Oracle Database identifier in short DBID is an internal, unique identifier for an Oracle database. Database administrator must note down the DBID in safe place, so that any miss-happening to the database could be easily identified and recovered. In case it is required to recover SPFILE or control file from autobackup, such as disaster recovery, you will need to set DBID. So lets see how to get DBID in NOMOUNT State.



Why DBID is important? 

  • It is an unique identifier for a database.
  • In case of backup and recovery RMAN distinguishes databases by DBID.
  • When DBID of a database is changed, all previous backups and archived logs of the database become unusable.
  • After you change the DBID, you must open the database with the RESETLOGS option, which re-creates the online redo logs and resets their log sequence to 1 
  • You should make a backup of the whole database immediately after changing the DBID.


Let's take an example of getting it in nomount state:

First shut down the database using shut immediate command

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
Now startup database in nomount state
SQL> startup nomount
ORACLE instance started.
Total System Global Area  606806016 bytes
Fixed Size                  1376268 bytes
Variable Size             402657268 bytes
Database Buffers          197132288 bytes
Redo Buffers                5640192 bytes
You can also set tracefile identifier for easily identification of tracefile.

SQL> alter session set tracefile_identifier=orahow;
Session altered.
Now, dump first ten block of datafile, because each block header contains dbid information.

SQL> alter system dump datafile 'D:\app\SantoshTiwari\oradata\TEST11\USERS01.DBF'
  2  block min 1 block max 10;
System altered.
Now find the location of Trace file.

SQL> show parameter user_dump_dest
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest                       string      d:\app\santoshtiwari\diag\rdbm
                                                 s\test11\test11\trace

Now search for Db ID inside the trace file. In Linux you can use cat command with grep to find it:

cat filename | grep Db id

Here you can see the dump here:

Start dump data block from file D:\APP\SANTOSHTIWARI\ORADATA\TEST11\USERS01.DBF minblk 1 maxblk 10
 V10 STYLE FILE HEADER:
Compatibility Vsn = 186646528=0xb200000
Db ID=3561501508=0xd4483344, Db Name='TEST11'
Activation ID=0=0x0
Control Seq=3522=0xdc2, File size=640=0x280
File Number=4, Blksiz=8192, File Type=3 DATA


In simple you can also get it using v$database:

SQL> select name, dbid from v$database;
NAME            DBID
--------- ----------
TEST11    3561501508


 DBID is also displayed by the RMAN client when it starts up and connects to your database. Typical output follows:


SQL> host rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Thu Nov 6 19:59:06 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: TEST11 (DBID=3561501508)

Read more ...

How To Check Alert Log File in Oracle 11g

Each database has an alert log file, which contains a chronological(sequential order in which they occurred) log of database messages and errors. Oracle will automatically create a new alert log file whenever the old one is deleted.

Alert Log Location in Oracle 11g

The alert log includes the following errors and log messages:


  • All internal errors (ORA-600), block corruption errors (ORA-1578), and deadlock errors (ORA-60)
  • Administrative operations such as DDL statements and the SQL*Plus commands STARTUP, SHUTDOWN, ARCHIVE LOG, and RECOVER
  • Several messages and errors relating to the functions of shared server and dispatcher processes
  • Errors during the automatic refresh of a materialized view

How to Find the Location of alert log file in Oracle 11g


SQL> select name from v$database;

NAME
---------
TEST11


alert.log file is written to the directory specified by the background_dump_dest parameter. To view alert log, first you need to find it's location using show parameter command.


SQL> show parameter background_dump_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest                 string      /u01/orahow/diag/rdbms/test11/TEST11/trace


You can also get the same result using this sql:


SQL> show parameter background

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_core_dump                 string      partial
background_dump_dest                 string      /u01/orahow/diag/rdbms/test11/TEST11/trace

 Copy the above value to find the exact name and location of alert log using ls command:
                                              
[oracle@orahow ~]$ ls -ltr /u01/orahow/diag/rdbms/test11/TEST11/trace/al*
-rw-r----- 1 oracle dba 1901154 Nov 24 04:00 //u01/orahow/diag/rdbms/test11/TEST11/trace/alert_TEST11.log

To view alert log you can use tail command with options and filename.

[oracle@orahow ~]$ tail -20f /u01/orahow/diag/rdbms/test11/TEST11/trace/ alert_TEST11.log
In the following output you will see the last 20 lines of the alert log, but if you want to see more number of lines (ex: 100 line) you can use 100f like that.
Read more ...

How to Make a File Write Protected in Linux

In Linux there is an additional file attribute which prevent files and folders from being deleted accidentally. Flag which make a file write/delete protected even from root is called immutable flag. Linux administrator must set this flag for various configuration files on the production servers so that no one could delete/temper with these files.


Prevent file from being deleted


When you want to delete file/folder having immutable flag set, you will encounter with an error:
rm: cannot remove 'file-name': Operation not permitted.

Making file write protected in Linux

To set this flag we use plus (+) sign with chattr command and to unset this flag we use minus (-) sign.


Syntax:

To set this flag on files you can use the following command:
chattr +i filename

To unset or remove the attribute you can use the following command:
 chattr -i filename 


To set this flag on directories only you need to specify the directory name:

 chattr +i directory name 

chattr -i directory name 


How to search files having immutable flag set?
To accomplish this we use lsattr command pipe with the grep command.

lsattr -R | grep +i

Example:

Creating file having name san.txt and setting flag on it.
[oracle@orahow ~]$ vi san.txt

Initially check the file permission:
[oracle@orahow ~]$ ls -l san.txt
-rw-r--r-- 1 oracle dba 19 Nov 18 05:56 san.txt

 [oracle@orahow ~]$ chattr +i san.txt

chattr: Operation not permitted while setting flags on san.txt


To set this flag you need to login as a root user:

[root@orahow oracle]# chattr +i san.txt

[root@orahow oracle]# lsattr san.txt
----i-------- san.txt 


Now try to delete the file having immutable flag set:
[root@orahow oracle]# rm san.txt

rm: remove write-protected regular file `san.txt'? y

rm: cannot remove `san.txt': Operation not permitted

To remove this file you need to unset this flag:

[root@orahow oracle]# chattr -i san.txt

[root@orahow oracle]# lsattr san.txt

------------- san.txt

[root@orahow oracle]# rm san.txt
rm: remove regular file `san.txt'? y
[root@orahow oracle]# cat san.txt
cat: san.txt: No such file or directory

Making a directory write protected in Linux:

[root@orahow oracle]# mkdir san
[root@orahow oracle]# ls -ldr san

drwxr-xr-x 2 root root 4096 Nov 18 07:36 san

[root@orahow oracle]# chattr +i san
Now if you try to remove the directory it will throw a message: operation not permitted. Before deleting this folder you need to unset this flag.

[root@orahow oracle]# rmdir san
rmdir: san: Operation not permitted

[root@orahow oracle]# chattr -i san

[root@orahow oracle]# rmdir san

[root@orahow oracle]# ls -ldr san

ls: san: No such file or directory




Read more ...

CONTACT

Name

Email *

Message *