BEQ connection Error ->>> ORA-12162: TNS:net service name is incorrectly specified

BEQ connection Error ->>> ORA-12162: TNS:net service name is incorrectly specified

Prognosis: Only occurs for oracle user

[SID@cdrserver ~]$ oerr ORA 12162
12162, 00000, "TNS:net service name is incorrectly specified"
// *Cause: The connect descriptor corresponding to the net service name in
// TNSNAMES.ORA or in the directory server (Oracle Internet Directory) is
// incorrectly specified.
// *Action: If using local naming make sure there are no syntax errors in
// the corresponding connect descriptor in the TNSNAMES.ORA file. If using
// directory naming check the information provided through the administration
// used for directory naming.
[SID@cdrserver ~]$

problem only accurs when connecting to oracle directly; i.e.

sqlplus username/password [results in ORA-12162 ERROR]

[oracle@cdrserver trace]$ sqlplus username/*********

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Mar 4 09:50:06 2010

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

ERROR:
ORA-12162: TNS:net service name is incorrectly specified

Enter user-name:

sqlplus username/password@servicename [successful]

[oracle@cdrserver trace]$ sqlplus username/*********@SID

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Mar 4 09:56:22 2010

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>


So I went into MOS and I must say, I feel sheepish!!

Solution [MOS Note: 312348.1]:

The user environment variable $ORACLE_SID is not set while running a bequeath (local) connection.
When this env var is omitted, then using SQL*Plus without the @SID connect string, may result in an ORA-12162 error.

Make sure to add the $ORACLE_SID environment variable to the .profile for the Oracle user.
This will give the BEQ adapter a route to connect to the instance.

ORACLE_SID environment variable

[oracle@cdrserver trace]$ echo $ORACLE_SID

[oracle@cdrserver trace]$

Oops! MOS was right after all.

Environment variable

[oracle@cdrserver ~]$ cat .bashrc
# .bashrc

# Source global definitions
if [ -f /etc/bashrc ]; then
. /etc/bashrc
fi

# User specific aliases and functions
export PATH=$PATH:/oradata01/app/oracle/product/11.1.0/db_1/bin:/usr/x11R6/bin
export ORACLE_SID= SID <-- Take note of space
export ORAENV_ASK=NO
. oraenv

[oracle@cdrserver ~]$

You will not believe how long it took me to notice that the [export ORACLE_SID= SID] statement had a space between the variable name and the value

[oracle@cdrserver ~]$ sqlplus username/**********

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Mar 4 10:01:24 2010

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

OEM java.lang.Exception: No such metric

I tried the fix specified on the link below, but that did not sort out my problem.

http://forums.oracle.com/forums/message.jspa?messageID=1131271

Turns out though that the xml file ->>> $ORACLE_HOME/hostname_SID/sysman/emd/targets.xml had a wrong entry for my machine name.

Just confirm that you have the correct entries in the file.

If the problem persists, just recreate the EM repository

emca -config dbcontrol db -repos recreate

Oracle Database 10g - Shutdown Hanging

Phweeee!!! I must say, today was a rather interesting day... like always, I learnt something new. Okay, so one of the applications administrator tells me there is an audit on the way and he needs me to check for him if the DB is being backed up.

same old story... I am tired of hearing it too; DB is production.... running in NOARCHIVELOG mode and guess what, they have never performed a backup on the poor thing. So like a good dog (as always), I start narrating to him what repacations all this might have on the DB......

Fact File

OS ->>> MS Windows Server 2003 R2 Enterprise Edition; Service Pack 2
RAM ->>> 3.25GM
CPU ->>> Intel Xeron; 2.00GHz
DB ->>> Oracle Database 10g R2 Enterprise Edition
DB Role ->>> Production
Application: OMNIX (A Telecommunication Asset Management System)


Problem: DB needs to be mounted in older for DB to be configured in ARCHIVELOG mod. Unfortunately, Shutdown Hangs (see alertlog entries below).


Thu Feb 25 11:02:18 2010
Starting background process EMN0
EMN0 started with pid=27, OS id=7552
Thu Feb 25 11:02:18 2010
Shutting down instance: further logons disabled
Thu Feb 25 11:02:19 2010
Stopping background process QMNC
Thu Feb 25 11:02:20 2010
Stopping background process CJQ0
Thu Feb 25 11:02:21 2010
Stopping background process MMNL
Thu Feb 25 11:02:22 2010
Stopping background process MMON
Thu Feb 25 11:02:23 2010
Shutting down instance (immediate)
License high water mark = 30
Thu Feb 25 11:02:23 2010
Stopping Job queue slave processes
Thu Feb 25 11:02:23 2010
Job queue slave processes stopped
Thu Feb 25 11:07:22 2010
Active call for process 7360 user 'SYSTEM' program 'ORACLE.EXE (SHAD)'
SHUTDOWN: waiting for active calls to complete.
Thu Feb 25 11:21:48 2010
MMNL absent for 1204 secs; Foregrounds taking over



Solution:

Step 1 ->>> Log in as SYSDBA and issue shutdown abort

D:\>sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Feb 25 11:45:53 2010

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

SQL> connect sys as sysdba
Enter password:
Connected to an idle instance.
SQL> shutdown abort
ORA-03113: end-of-file on communication channel
SQL>


Step 2 ->>> Startup DB in restricted mode



C:\>sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Feb 25 11:56:39 2010

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

SQL> connect sys as sysdba
Enter password:
ERROR:
ORA-12560: TNS:protocol adapter error


SQL>


Oh boy; Vindows :( Turns out the windows Oracle service is not started.... net start command should sort this out

C:\>net start OracleServiceOMXZAMPRD
The OracleServiceOMXZAMPRD service is starting.......
The OracleServiceOMXZAMPRD service was started successfully.


C:\>sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Feb 25 12:01:12 2010

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

SQL> connect sys as sysdba
Enter password:
Connected.
SQL> startup restrict;
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup restrict;
ORACLE instance started.

Total System Global Area 1098907648 bytes
Fixed Size 1291700 bytes
Variable Size 654314060 bytes
Database Buffers 436207616 bytes
Redo Buffers 7094272 bytes
Database mounted.
Database opened.
SQL>
SQL>


Step 3: Now shutdown Normally

SQL>
SQL> shutdown normal;
Database closed.
Database dismounted.
ORACLE instance shut down.



Step 4: Startup Mount


SQL> startup mount;
ORACLE instance started.

Total System Global Area 1098907648 bytes
Fixed Size 1291700 bytes
Variable Size 654314060 bytes
Database Buffers 436207616 bytes
Redo Buffers 7094272 bytes
Database mounted.
SQL>



Step 5: Change to Archivelog mode

SQL> alter database archivelog;

Database altered.

SQL>



Step 6: Open the Database

SQL> alter database open;

Database altered.

SQL> SELECT dbid, name, created, log_mode FROM v$database;

DBID NAME CREATED LOG_MODE
---------- --------- --------- ------------
4175576194 OMXZAMPR 01-JUN-07 ARCHIVELOG

SQL>



References:

http://asktom.oracle.com/
http://dbataj.blogspot.com/

1Z0-042 - Oracle Database 10g: Administration I

I wrote and passed the 1z0-042 exam today; all I need to do now is write companion exam and then I shall be officially an Oracle Database 10g Certified Associate. I must say, it's been a rough six+ months of preparation. A couple of tips if you intend to write this exam.

Certification Path
Just so you didn't know, you only become an Oracle Certified Associate upon completion of the 1z0-042 + [1z0-007|1z0-047|1z0-051]. I intend to sit for 1z0-051 (Oracle Database 11g: SQL Fundamentals I) towards the end of August 2009; it's the simplest option and I really don't have to study because it's what I do on a daily basis.

In any case, I intend to become an Oracle Database: SQL Certified Expert by end of October 2009 and so naturally, I just can't run away from the 1z0-047 exam.

Recommended Study Option
Install Oracle 10g Express Edition. Download a copy here.
Download the course outline from here
Study a chapter per week (well if you work from 8AM to 7PM like me :( ).

Study Materials

Oracle University Official Study guide: Oracle 10g Administration I
Oracle Reference Manuals
OCA: Oracle 10g Administration I Study Guide (1Z0-042). Details are here

Well, if you intend to write the exam, best of luck.

My Projects - Career Prospects Zambia

I started this project (http://www.careerprospectszambia.com/) in 2008, but my client keeps coming up with new requirements; I am however thinking of changing the whole website.

Some of the issues I hope include to the site are:

• The skin of the website is going to completely change; my client says they want their company colours to be used.
  
• Ajax (there's' already some functionality making use of this, but i intend to spread it all across the website)
  
• Web 2.0
• Application Module (most of the steps are most likely going to be merged and some removed as it seems the average user takes an awful long time to finish the application process)
• JavaScript (the site currently does not make use of a lot of JavaScripting; I intend to make full use of the language)

I hope to finish off everything by August, 2009.