Guenadi N Jilevski's Oracle BLOG

Oracle RAC, DG, EBS, DR and HA DBA BLOG

Oracle CRSCTL easy reference

Oracle CRSCTL easy reference

You can find below various commands which can be used to administer Oracle Clusterware using crsctl. This is for purpose of easy reference.

Start Oracle Clusterware

#crsctl start crs

Stop Oracle Clusterware

#crsctl stop crs

Enable Oracle Clusterware

#crsctl enable crs

It enables automatic startup of Clusterware daemons

Disable Oracle Clusterware

#crsctl disable crs

It disables automatic startup of Clusterware daemons. This is useful when you are performing some operations like OS patching and does not want clusterware to start the daemons automatically.

Checking Voting disk Location

$crsctl query css votedisk

0. 0 /dev/sda3
1. 0 /dev/sda5
2. 0 /dev/sda6
Located 3 voting disk(s).

Note: -Any command which just needs to query information can be run using oracle user. But anything which alters Oracle Clusterware requires root privileges.

Add Voting disk

#crsctl add css votedisk path

Remove Voting disk

#crsctl delete css votedisk path

Check CRS Status

$crsctl check crs

Cluster Synchronization Services appears healthy

Cluster Ready Services appears healthy

Event Manager appears healthy

You can also see particular daemon status

$crsctl check cssd

Cluster Synchronization Services appears healthy

$crsctl check crsd

Cluster Ready Services appears healthy

$crsctl check evmd

Event Manager appears healthy

You can also check Clusterware status on both the nodes using

$crsctl check cluster

raclinux1 ONLINE

raclinux2 ONLINE

Checking Oracle Clusterware Version

To determine software version (binary version of the software on a particular cluster node) use

$crsctl query crs softwareversion

Oracle Clusterware version on node [raclinux1] is [11.1.0.6.0]

For checking active version on cluster, use

$ crsctl query crs activeversion

Oracle Clusterware active version on the cluster is [11.1.0.6.0]

As per documentation, multiple versions are used while upgrading.

There are other options for CRSCTL too which can be seen using

$crsctl

Or

$crsctl help

June 24, 2008 Posted by | oracle | Leave a comment

Troubleshoot ORA-12154 TNS-12154 TNS-03505: Failed to resolve

Troubleshoot ORA-12154 TNS-12154 TNS-03505: Failed to resolve name

In this document I will discuss step by step solutions for ORA-12154, TNS-12154 and TNS-03505.


What Oracle Says about the Problem

Oracle Error: TNS-12154

TNS: could not resolve the connect identifier specified

Cause: A connection to a database or other service was requested using a connect identifier, and the connect identifier specified could not be resolved into a connect descriptor using one of the naming methods configured. For example, if the type of connect identifier used was a net service name then the net service name could not be found in a naming method repository, or the repository could not be located or reached.

Action:

– If you are using local naming (TNSNAMES.ORA file):

– Make sure that “TNSNAMES” is listed as one of the values of the NAMES.DIRECTORY_PATH parameter in the Oracle Net profile (SQLNET.ORA)

– Verify that a TNSNAMES.ORA file exists and is in the proper directory and is accessible.

– Check that the net service name used as the connect identifier exists in the TNSNAMES.ORA file.

– Make sure there are no syntax errors anywhere in the TNSNAMES.ORA file. Look for unmatched parentheses or stray characters. Errors in a TNSNAMES.ORA file may make it unusable.

– If you are using directory naming:

– Verify that “LDAP” is listed as one of the values of the NAMES.DIRETORY_PATH parameter in the Oracle Net profile (SQLNET.ORA).

– Verify that the LDAP directory server is up and that it is accessible.

– Verify that the net service name or database name used as the connect identifier is configured in the directory.

– Verify that the default context being used is correct by specifying a fully qualified net service name or a full LDAP DN as the connect identifier

– If you are using easy connect naming:

– Verify that “EZCONNECT” is listed as one of the values of the NAMES.DIRETORY_PATH parameter in the Oracle Net profile (SQLNET.ORA).

– Make sure the host, port and service name specified are correct.

– Try enclosing the connect identifier in quote marks. See the Oracle Net Services Administrators Guide or the Oracle operating system specific guide for more information on naming.


For case TNS-03505:

TNS-03505: Failed to resolve name

Cause: The service name you provided could not be found in TNSNAMES.ORA, an Oracle Names server, or a native naming service.

Action: Verify that you entered the service name correctly. You may need to ensure that the name was entered correctly into the network configuration.

which indicates for both ORA-12154, TNS-12154 and TNS-03505 we will have a generic solution.



Step 01: Overview of the Problem


Both ORA-12154, TNS-12154 and TNS-03505 indicates that SQL*Net could not find the alias specified for a connection in the TNSNAMES.ORA file or other naming adapter.

As the problem is regarding TNSNAMES.ORA and SQLNET.ORA so in order to dig into solution it is important to print out or a view of both the TNSNAMES.ORA and the SQLNET.ORA files. Looking at these files at the same time is helpful since references will be made to both.

Step 02: Identify the location of TNSNAMES.ORA and SQLNET.ORA


It is necessary to locate the location of TNSNAMES.ORA and SQLNET.ORA. If you simply use tnsping dummy it will say the location of SQLNET.ORA and there TNSNAMES.ORA should be in the same location.

E:\Documents and Settings\app>tnsping dummy

TNS Ping Utility for 32-bit Windows: Version 11.1.0.6.0 – Production on 08-JUN-2008 11:00:46

Copyright (c) 1997, 2007, Oracle. All rights reserved.

Used parameter files:

D:\app\app\product\11.1.0\db_1\network\admin\sqlnet.ora

TNS-03505: Failed to resolve name

From the above output we see both of these files are located in the path D:\app\app\product\11.1.0\db_1\network\admin\.

Through above ways we can determine current path of these two files. However it is important to know the default path of these files. Based on the operating system default path are mentioned below.

i)Windows 3.x client
ORAWIN\NETWORK\ADMIN directory

ii)Windows 95/98 client
SQL*Net 2.x – ORAWIN95\NETWORK\ADMIN
Net8 – ORAWIN95\NET80\admin
Net8i – ORACLE\ORA81\NETWORK\ADMIN

iii)Windows NT client
SQL*Net 2.x – ORANT\NETWORK\ADMIN
Net8 – ORANT\NET80\ADMIN
Net8i – ORACLE\ORA81\NETWORK\ADMIN

iv)UNIX Client
$ORACLE_HOME/NETWORK/ADMIN
or /etc
or /var/opt/oracle

Step 03: Print out or View both TNSNAMES.ORA and SQLNET.ORA.
Following is the correct TNSNAMES.ORA from my system

# tnsnames.ora Network Configuration File: D:\app\app\product\11.1.0\db_1\network\admin\tnsnames.ora

# Generated by Oracle configuration tools.

A =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = APP)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = a)

)

)

Following is the correct SQLNET.ORA from my system.

# sqlnet.ora Network Configuration File: D:\app\app\product\11.1.0\db_1\network\admin\sqlnet.ora

# Generated by Oracle configuration tools.

# This file is actually generated by netca. But if customers choose to

# install “Software Only”, this file will not exist and without the native

# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)



Step 04: Check several factors one by one.
To Troubleshoot problem let’s start with some complex TNSNAMES.ORA and SQLNET.ORA.
TNSNAMES.ORA Experimental Sample

DEV.WORLD =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS =

(PROTOCOL = TCP)

(Host = 10.1.1.1)

(Port = 1521)

)

)

(CONNECT_DATA = (SID = ORCL)

)

)

SQLNET.ORA Experimental Sample

TRACE_LEVEL_CLIENT = OFF

SQLNET.AUTHENTICATION_SERVICES = (NONE)

NAMES.DIRECTORY_PATH = (TNSNAMES)

AUTOMATIC_IPC = OFF

i) Determine which tns entry you used to connect. For example if you used dummy then issue a tnsping dummy and ensure that the entry DUMMY must exist in the TNSNAMES.ORA.

For example, my “tnsping a” worked because in my TNSNAMES.ORA I have an entry regarding alias A which I have pasted my TNSNAMES.ORA above.

E:\Documents and Settings\app>tnsping a

TNS Ping Utility for 32-bit Windows: Version 11.1.0.6.0 – Production on 08-JUN-2008 11:00:34

Copyright (c) 1997, 2007, Oracle. All rights reserved.

Used parameter files:

D:\app\app\product\11.1.0\db_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = APP)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = a)))

OK (330 msec)


tnsping dummy will fail because there is no entry in my TNSNAMES.ORA with name dummy and also connection with dummy will fail too.
Make sure by using IP Address entry instead of hostname inside TNSNAMES.ORA. So use HOST=192.168.1.1 something like that instead of HOST=APP.

ii) Check both file again and ensure that there is no CTRL-A (^A) or CTRL-C (^C) characters inserted at the ends of any lines.

iii) If both are fine check the TNSNAMES.ORA alias. Here in our experimental sample it is DEV.WORLD which means .WORLD is our domain and it should be specified it our SQLNET.ORA. So add the parameter NAMES.DEFAULT_DOMAIN = world anywhere in the SQLNET.ORA file. Save the file, and try the connection again.

iv) If within the TNSNAMES.ORA there is no default domain in the alias*(for example it is simply DEV but not DEV.WORLD) then remove NAMES.DEFAULT_DOMAIN = world from the SQLNET.ORA file if there existed. After removing the parameter, save the SQLNET.ORA file, and try connecting again.

v) If the parameter NAMES.DIRECTORY_PATH exists in the SQLNET.ORA file, make sure the value in parenthesis lists TNSNAMES. For example following two examples are valid entry.

NAMES.DIRECTORY_PATH=(TNSNAMES)

NAMES.DIRECTORY_PATH=(TNSNAMES, HOSTNAME)

Though NAMES.DIRECTORY_PATH parameter is not needed, but if it exists then it should look ok. After removing it you can try connecting again.

vi) At this phase the configuration files are most likely technically accurate.
Now you have to ensure that your TNSNAMES.ORA is in the correct location and you have used correct TNS entry while connecting. The TNS location can be set by TNS_ASMIN environmental variable.

On your Unix environment,
% echo $TNS_ADMIN

If nothing is returned, try setting the TNS_ADMIN environment variable to explicitly point to the location of the TNSNAMES.ORA file.

In C Shell, the syntax is:
% setenv TNS_ADMIN full_path_to_tnsnames.ora_file

In K Shell, the syntax is:
% TNS_ADMIN=full_path_to_tnsnames.ora_file; export TNS_ADMIN

After setting try the connection/tnsping again.

In case of Windows 3.x,
– TNS_ADMIN is set in the ORACLE.INI.

– Look in the C:\WINDOWS directory for the ORACLE.INI file. Look for the parameter TNS_ADMIN. TNS_ADMIN is a parameter that can be set to have SQL*Net point to an alternate location for the configuration files.
– Search for duplicate TNSNAMES.ORA and SQLNET.ORA files.
– Make sure none exist.
– Once you are sure try the connection/tnsping again.

In case of Windows 95/98,
– Open the file ORACLE_HOME\bin\oracle.key.
– Search for Registry under the location specified by the .key file, such as HKEY_LOCAL_MACHINE\Software\Oracle\Home1 for a parameter called TNS_ADMIN.
– TNS_ADMIN is a parameter that can be set to have SQL*Net point to an alternate location for the configuration files.
– Search for duplicate TNSNAMES.ORA and SQLNET.ORA files.
– Make sure none exist.
– Once you are sure try the connection/tnsping again.

In case of Windows XP,
– Make sure TNS_ADMIN environemental points to correct location. Ensure that there must not exist both user variable or system variable environmental variable. If nothing exist you can add one user variable. To set user/system variable,
— Right click on My computer
— Click Properties.
— Click Advanced Tab.
— Click Environemental Variables.
— You can have both User and System Variables there.
You can add, edit or delete variables. You can simply add TNS_ADMIN and its location in the user variable section. Note that Variable Name would be “TNS_ADMIN” and variable would be “The location of the TNSNAMES.ORA in your client environement.”

– After you set try connection again.

June 14, 2008 Posted by | oracle | Leave a comment

ORA-12154: TNS: could not resolve the connect identifier specified

ORA-12154: TNS: could not resolve the connect identifier specified

Problem Description:
——————————————–

Whenever you try to connect your database it returns error ORA-12154.


SQL> conn prod/prod@prod


ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Cause of The Problem:
————————————

The cause of the problem is depends on the which naming method you are using. Suppose if you use tnsnames.ora for naming method then there might be problem in it. I look for oerr command on my unix machine and got,

$ oerr ora 12154
12154, 00000, “TNS:could not resolve the connect identifier specified”
// *Cause: A connection to a database or other service was requested using
// a connect identifier, and the connect identifier specified could not
// be resolved into a connect descriptor using one of the naming methods
// configured. For example, if the type of connect identifier used was a
// net service name then the net service name could not be found in a
// naming method repository, or the repository could not be
// located or reached.
// *Action:
// – If you are using local naming (TNSNAMES.ORA file):
// – Make sure that “TNSNAMES” is listed as one of the values of the
// NAMES.DIRECTORY_PATH parameter in the Oracle Net profile
// (SQLNET.ORA)
// – Verify that a TNSNAMES.ORA file exists and is in the proper
// directory and is accessible.
// – Check that the net service name used as the connect identifier
// exists in the TNSNAMES.ORA file.
// – Make sure there are no syntax errors anywhere in the TNSNAMES.ORA
// file. Look for unmatched parentheses or stray characters. Errors
// in a TNSNAMES.ORA file may make it unusable.
// – If you are using directory naming:
// – Verify that “LDAP” is listed as one of the values of the
// NAMES.DIRETORY_PATH parameter in the Oracle Net profile
// (SQLNET.ORA).
// – Verify that the LDAP directory server is up and that it is
// accessible.
// – Verify that the net service name or database name used as the
// connect identifier is configured in the directory.
// – Verify that the default context being used is correct by
// specifying a fully qualified net service name or a full LDAP DN
// as the connect identifier
// – If you are using easy connect naming:
// – Verify that “EZCONNECT” is listed as one of the values of the
// NAMES.DIRETORY_PATH parameter in the Oracle Net profile
// (SQLNET.ORA).
// – Make sure the host, port and service name specified
// are correct.
// – Try enclosing the connect identifier in quote marks.
//
// See the Oracle Net Services Administrators Guide or the Oracle
// operating system specific guide for more information on naming.

Solution of The Problem:
———————————————–

Actually above solution already depicted what to do if you get the problem. My suggestion after getting ORA-12154 follow the steps mentioned below.

Step 1: Look for tnsnames.ora and sqlnet.ora.
———————————————————————–

Look for your tnsnames.ora and sqlnet.ora file that you are using. On UNIX the default location is $ORACLE_HOME/network/admin. You better avoid using tnsnames.ora. By using easy naming service you can easily avoid this error.


Step 2: Check both file
—————————————————————————

•After locating both file open it with any viewer like on windows with notepad and on unix with less or more or cat.

•Check within the files whether any illegal character or any unnecessary space exists. If have then remove that.

•Check whether the exact entry exist on the tnsnames.ora that you used in connection identifer. Like if you use conn a/a@prod then within tnsnames.ora search for only prod alias.

•You can check your whether there is error or not in the tnsnames alias inside tnsnames.ora by using tnsping. In order to check alias prod we issue,

$ tnsping prod

TNS Ping Utility for Solaris: Version 10.2.0.1.0 – Production on 12-Feb-2008 03:25:58

Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files:
/oracle/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = ((ADDRESS = (PROTOCOL = TCP)(HOST = zeus)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = GJ)))


TNS-12533: TNS:illegal ADDRESS parameters
So there is illegal ADDRESS parameter which we see an extra ( before ADDRESS parameter.

If our tnsalias was good, then it would result below with how many miliseconds.

$ tnsping prod

TNS Ping Utility for Solaris: Version 10.2.0.1.0 – Production on 12-OCT-2008 03:28:26

Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files:
/oracle/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = zeus)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = GJ)))
OK (10 msec)

•Note that with name alias checking tnsnames also tell us which location parameter files it used. Here the location is /oracle/app/oracle/product/10.2.0/db_1/network/admin. So /oracle/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora location is used in order to resolve name. However if your tnsnames.ora is in other location then you have to set TNS_ADMIN variable.

•If you see tnsnames.ora is most likely accurate, echo the TNS_ADMIN environment variable.

% echo $TNS_ADMIN

•If nothing is returned, try to set the TNS_ADMIN environment variable to explicitly point to the location of the TNSNAMES.ORA file.

In C Shell, the syntax is:
% setenv TNS_ADMIN full_path_of_tnsnames.ora_file

In K Shell or bash, the syntax is:
% export TNS_ADMIN=full_path_of_tnsnames.ora_file

In windows the syntax is:
set TNS_ADMIN=full_path_of_tnsnames.ora_file

•Now try and see whether error remains. If still you get error then in the SQLNET.ORA file, add the parameter AUTOMATIC_IPC = OFF. If AUTOMATIC_IPC is already set to ON, then change the value to OFF. And try to connect. If still you get error then check the permission of tnsnames.ora and sqlnet.ora. For workaround you can set chmod 777 of these files and try to connect. At this point it is expected you have solved your problem.

June 9, 2008 Posted by | oracle | Leave a comment

Scheduling Oracle job through UNIX cron

Scheduling Oracle job through UNIX cron

This was a problem which I faced yesterday. I had a mview refresh which was lagging on one of the server. After checking master site, I found that there were many pending requests coming from the second materialized view site. Checking second site revealed that there were multiple sessions running in database which were waiting on ‘enq :JI Contention’ wait event.

These mviews were refreshed by job scheduled through a cron. Doing a grep for the refresh script gave following result

$ps –ef |grep scott | grep -v grep

oracle 11725 11714 0 13:00:00 ? 0:00 /usr/bin/sh /home/oracle/cron/Refresh_scott.sh

oracle 19981 19970 0 Feb 7 ? 0:00 /usr/bin/sh /home/oracle/cron/Refresh_scott.sh

oracle 24794 24781 0 06:30:00 ? 0:00 /usr/bin/sh /home/oracle/cron/Refresh_scott.sh

oracle 10538 10527 0 Feb 6 ? 0:00 /usr/bin/sh /home/oracle/cron/Refresh_scott.sh

oracle 13972 13935 0 Feb 6 ? 0:00 /usr/bin/sh /home/oracle/cron/Refresh_scott.sh

oracle 2601 2592 0 19:00:00 ? 0:00 /usr/bin/sh /home/oracle/cron/Refresh_scott.sh

oracle 17274 17246 0 Feb 6 ? 0:00 /usr/bin/sh /home/oracle/cron/Refresh_scott.sh

oracle 8308 8294 0 13:30:00 ? 0:00 /usr/bin/sh /home/oracle/cron/Refresh_scott.sh

This was definitely wrong. I killed the database sessions and also killed the OS processes

$ ps –ef | grep Refresh_scott.sh | grep -v grep|awk ‘{print $2}’| xargs kill -9

This issue had occurred as another site was added for mview refresh and due to locking issues (while deleting records from Mlog$), job could not complete before next refresh schedule. While scheduling scripts through cron, care should be taken to put a check if script is already running or not. This is required as Cron will spawn the new job (as per job schedule) despite that whether earlier execution has completed or not. I used following logic to implement the same check

cnt=`ps –ef |grep Refresh_scott.sh |grep -v grep |wc -l`

if [ $cnt -eq 1 ]

then

echo “Running Fast Refresh now ” > $DIR/Check_refresh_scott.log

sqlplus /nolog < $DIR/Refresh_scott.log

conn scott/tiger

exec dbms_mview.refresh …


exit

EOF

else

echo “Fast refresh already running ” > $DIR/Check_refresh_scott.log

date >> $DIR/Check_refresh_scott.log

exit

fi

There could be other approach as well which can be used to implement the same. That is, another could be

filename=$DIR/Check_refresh_scott.log

if [ -e $filename ]

then

echo “Fast Refresh already running ” > refresh_runnning.log

exit

else

sqlplus /nolog < $DIR/Refresh_scott.log

conn scott/tiger

exec dbms_mview.refresh …


exit

EOF

rm $filename

fi

Another option could be to use DBMS_JOB and DBMS_SCHEDULER to schedule the database job.

June 6, 2008 Posted by | oracle | Leave a comment