Monday, July 29, 2013

ORA-01506: missing or illegal database name

Sometimes when we try to start a database in windows or other OS platform, we get the error ORA-01506: missing or illegal database name

Environment Details:
OS: Windows Server 2008
Database Version: 11gR2
User: oracle
Oracle Home: E:\oracle\product\11.2.0\dbhome_1
Oracle SID: orcldb

Debugging: 
TNS Ping is working fine
E:\>tnsping orcldb

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 29-JUL-2013 07:56:32

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

Used parameter files:
E:\oracle\product\11.2.0\dbhome_1\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=winhost1.com)(PORT=1521)) (CONNECT_DATA=(SID=orcldb)))
OK (60 msec)

Listener is also up and running
E:\>lsnrctl status

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 29-JUL-2013 12:37:21

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=winhost1.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Start Date                29-JUL-2013 07:42:58
Uptime                    0 days 4 hr. 54 min. 23 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   E:\oracle\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File         E:\oracle\diag\tnslsnr\winhost1\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=winhost1.com)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcldb.us.oracle.com" has 1 instance(s).
  Instance "orcldb", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

but if we login & start the database we get error 

E:\>set ORACLE_HOME=E:\oracle\product\11.2.0\dbhome_1
E:\>set PATH=%ORACLE_HOME%\bin;%PATH%
E:\>set ORACLE_SID=orcldb
E:\>sqlplus sys as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Jul 29 12:50:28 2013
Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter password:
Connected to an idle instance.

SQL> startup
ORA-01506: missing or illegal database name
SQL> exit
Disconnected


Solution:
Verify the content of parameter file used to start the database especially the db_name parameter. Provide correct name of the database and then start the database
Location of parameter file %ORACLE_HOME%\database
parameter file name: initorcldb.ora

E:\>sqlplus sys as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Jul 29 12:55:28 2013
Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter password:
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  518922240 bytes
Fixed Size                  1375676 bytes
Variable Size             159384132 bytes
Database Buffers          352321536 bytes
Redo Buffers                5840896 bytes
Database mounted.
Database opened.

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------

ORCLDB    READ WRITE


1 comment:

  1. what do you mean by the first paragraph of your solution

    ReplyDelete