Archive for the ‘Oracle Issues’ Category

Shutdown Hangup Scenario

If Shutdown normal or shutdown immediate hangs and you try to connect as sys, it would get you connected either as idle instance or simply connected which is in reality a fake connection. In a particular scenario, I changed ORACLE_HOME from EM and then issued simple “SHUTDOWN” command which took a long time to complete. I tried to issue “shutdown immediate” and “shutdown abort” commands and found following errors.


C:\Users\ACE>sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Sat Mar 5 19:52:01 2011

Enter user-name: sys as sysdba
Enter password:
Connected.
SQL>  shutdown immediate
ORA-24324: service handle not initialized
ORA-24323:  value not allowed
ORA-01090: shutdown in progress - connection is not  permitted


SQL> shutdown abort
ORA-01031: insufficient privileges


SQL> select instance from v$instance;
select instance from  v$instance
* ERROR at line 1: ORA-01012: not logged on
Process ID:  0
Session ID: 0 Serial number: 0

In this scenario it did “connect / as sysdba”, but in actually failed to connect. Then, when I
tried to shutdown abort, you got ORA-1031 cause you weren’t actually connected.
It’s interesting to note that if you connect as sysdba, and the connection is successful, you see something like:”Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit
Production With the Partitioning, OLAP and Data Mining options”If you fail to connect, cause process table is full, you’ll  get: “Connected.”
So, “Connected.” means you *failed* to connect!

ORA-01012: not logged on (check this link for another reason why Oracle shows “not logged on” error)

 

Although shutdow abort causes instance recovery but it becomes necessary if you want to accomplish a repetitive task at a given time in a specific time. You can combine it with restricted startup and then again shutdown with immediate option and better run it through a script.


#!/bin/ksh
# Source the db.env to set the Oracle environment

export ORACLE_SID
ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH

shutdown abort;
startup restrict;
shutdown immediate;

Debugging a hung database

Starting with Oracle 11g,  SQL*Plus has a new option called “prelim” . This option is very useful for running oradebug and other utilities that do not require a real connection to the database.


C:\ACE> sqlplus –prelim

SQL>

or

SQL> set _prelim on
SQL> connect / as sysdba

Run oradebug commands to diagnose a hung database issue using the

new hanganalyze utility:


SQL> oradebug hanganalyze

Wait for some time to identify process state changes.

SQL> oradebug hanganalyze

Open a separate SQL session and immediately generate a system state dump.

SQL> alter session set events 'immediate trace name SYSTEMSTATE level 10';

Further Reading:

Fix Oracle Hanging

Advertisements

Reconfiguring Oracle EM

Article taken from arjudba blogspot
An Stack of Problems while creating Repository using emca

In my database I tried to create repository and stopped several times while creating repository. I spend a significant amount of time to create repository and after analysis I got the following.
A)Manually Resolve
1)If you fail after invoking command
emca -config dbcontrol db –repos

create see alert log file for more details. Also refer to another log file if it shows. Directly go to step 3)
2)However I see many errors like,
CONFIG: ORA-00955: name is already used by an existing object
CONFIG: ORA-01921: role name ‘MGMT_USER’ conflicts with another user or role name
and many severals errors.
3)Execute the following commands in SQL*plus and then execute emca command. I wish no more error will come. After several experiment I get this.

drop user sysman cascade;
drop public synonym SETEMVIEWUSERCONTEXT;
drop role MGMT_USER;
drop PUBLIC SYNONYM MGMT_TARGET_BLACKOUTS;
drop user MGMT_VIEW;

And then,
$ emca -deconfig dbcontrol db
$ emca -config dbcontrol db -repos create
B)Using RepManager:
If I drop the Repository using RepManager like following error will not come while creating repository.
$ORACLE_HOME/sysman/admin/emdrep/bin/RepManager -action drop
This will not remove the configuration files, but only the repository related objects from the database.
But you need to remember when RepManager is ran the database will be put in Quiescing mode.

 

Another method from MacLochLainns Weblog:

These are the steps:

1. Change the %ORACLE_HOME%\network\admin\listener.ora file from an IP number to machine name.

2. Change the %ORACLE_HOME%\network\admin\tnsnames.ora file from an IP number to a machine name.

3. Change the C:\WINDOWS\System32\drivers\etc\hosts file by adding this line beneath the default localhost line:

127.0.0.1       localhost
172.16.113.128  mclaughlin11g mclaughlin11g.techtinker.com

4. Change the Windows hostname by navigating: Start > Control Panel > System (classic view) from the random assignment of VMWare Fusion.

5. Reboot the machine, and then drop the em configuration with the following commands:

C:\Data> set ORACLE_SID=orcl
C:\Data> emca -deconfig dbcontrol db -repos drop

6. You’ll receive the following prompts, enter the Oracle SID and Port number without double quotes but you must enter all passwords with double quotes (at least in Oracle Database 11g):

STARTED EMCA at Jul 13, 2008 8:26:42 AM
EM Configuration Assistant, Version 11.1.0.5.0 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.   Enter the following information:
Database SID: orcl
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:
Password for SYSMAN user:
Do you wish to continue? [yes(Y)/no(N)]: y

7. You should then drop the SYSMAN user manually because it doesn’t happen without your assistance (or, it didn’t happen when I did it). If you don’t drop the SYSMAN schema, you’ll raise an error when you try to recreate it:

CONFIG: ORA-20001: SYSMAN already EXISTS..
ORA-06512: at line 17
8. You can then create the em environment with the following syntax:
C:\Data> emca -config dbcontrol db -repos create

9. Again, you’ll receive the following prompts, enter the Oracle SID and Port number without double quotes but you must enter all passwords with double quotes (at least in Oracle Database 11g):

STARTED EMCA at Jul 13, 2008 8:28:48 AM
EM Configuration Assistant, Version 11.1.0.5.0 Production
Copyright (c) 2003, 2005, Oracle.  ALL rights reserved.   Enter the following information:
DATABASE SID: orcl
Listener port number: 1521
Password FOR SYS user:
Password FOR DBSNMP user:
Password FOR SYSMAN user:
Password FOR SYSMAN user: Email address FOR notifications (optional):
Outgoing Mail (SMTP) server FOR notifications (optional):
-----------------------------------------------------------------

Oracle Network Issues Link

Network DBA

This link contains common network issues for Oracle Connectivity along with brief description of network files.

NETWORK DBA 

1. LISTENER.ORA file

It is a server side file and contains information regarding network configuration parameters. It can be found in the ORACLE_HOME/network/admin directory on the server.

Sample File

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oracle\product\10.2.0\db)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL)
      (ORACLE_HOME = C:\oracle\product\10.2.0\db)
      (SID_NAME = ORCL)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
  )

 

If listener is modified then the listener should be restarted or reloaded to allow the new configuration to take place.  Basic commands of Listener control utility are as follows:

LSNRCTL>start   LSNRCTL>stop  LSNRCTL>reload LSNRCTL>status LSNRCTL>show

 

2.TNSNAMES.ORA file

It is a Client side file which is created on both sites SERVER/CLIENT. It contains client side network configuration parameters.It can be found in the ORACLE_HOME/network/admin. It can be found on the server if client style connections are used on the server side.

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

3. SQLNET.ORA file

It is a Client side file which is created on both sites SERVER/CLIENT. It is use for user AUTHENTICATION purpose.

SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

 

Another good link for commonly known TNS errors from

Emre Baransel – Oracle Blog