ORA-12528: TNS:listener: all appropriate instances are blocking new connections
I am using Oracle 11g on Windows Xp Service Pack 2. After restarting my computer and then loading up the database control I get the following error under the Agent connection to Instance:
Status Failed
Details ORA-12528: TNS:listener: all appropriate instances are blocking new connections (DBD ERROR: OCIServerAttach)
I have tried to bounce the listener but it never worked using:
LSNRCTL> stop
LSNRCTL> start
Then restart your instance: sqlplus with sysdba privileges
SQL> startup
Also I have tried to do a shutdown immediate through RMAN and received the following:
Recovery Manager: Release 11.1.0.6.0 - Production on Wed Jan 7 08:42:05 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
RMAN> SHUTDOWN IMMEDIATE
using target database control file instead of recovery catalog
RMAN-00571: ==========================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ==========================
RMAN-03002: failure of shutdown command at 01/07/2009 08:45:15
RMAN-06171: not connected to target database
And then when I try to connect using the SYS account I get the following:
RMAN> connect target SYS/PASSWORD@orcl
RMAN-00571: ==========================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ==========================
ORA-12528: TNS:listener: all appropriate instances are blocking new connections
***I have tried restarting the computer many times without any luck, Contents of alert log below:
Starting up ORACLE RDBMS Version: 11.1.0.6.0.
Using parameter settings in server-side spfile C:\APP\TEST\PRODUCT\11.1.0
System parameters with non-default values:
processes = 200
memory_target = 392M
control_files = "C:\APP\TEST\ORADATA\ORCL\
control_files = "C:\APP\TEST\ORADATA\ORCL\
control_files = "C:\APP\TEST\ORADATA\ORCL\
db_block_size = 8192
compatible = "11.1.0.0.0"
log_archive_dest_1 = "LOCATION=C:\Flash_Recover
log_archive_dest_10 = "LOCATION=USE_DB_RECOVERY_
db_recovery_file_dest = "C:\Flash_Recovery_Area"
db_recovery_file_dest_size
undo_tablespace = "UNDOTBS1"
remote_login_passwordfile=
db_domain = ""
dispatchers = "(PROTOCOL=TCP) (SERVICE=orclXDB)"
audit_file_dest = "C:\APP\TEST\ADMIN\ORCL\AD
audit_trail = "DB"
db_name = "orcl"
open_cursors = 300
diagnostic_dest = "C:\APP\TEST"
Wed Jan 07 09:01:13 2009
PMON started with pid=2, OS id=6512
Wed Jan 07 09:01:13 2009
VKTM started with pid=3, OS id=5200 at elevated priority
VKTM running at (20)ms precision
Wed Jan 07 09:01:13 2009
DIAG started with pid=4, OS id=7852
Wed Jan 07 09:01:13 2009
DBRM started with pid=5, OS id=7076
Wed Jan 07 09:01:13 2009
PSP0 started with pid=6, OS id=6932
Wed Jan 07 09:01:13 2009
DSKM started with pid=7, OS id=7728
Wed Jan 07 09:01:13 2009
DIA0 started with pid=8, OS id=6352
Wed Jan 07 09:01:13 2009
MMAN started with pid=7, OS id=6796
Wed Jan 07 09:01:13 2009
DBW0 started with pid=9, OS id=3192
Wed Jan 07 09:01:13 2009
LGWR started with pid=10, OS id=5824
Wed Jan 07 09:01:13 2009
CKPT started with pid=11, OS id=3792
Wed Jan 07 09:01:13 2009
SMON started with pid=12, OS id=6460
Wed Jan 07 09:01:13 2009
RECO started with pid=13, OS id=7588
Wed Jan 07 09:01:13 2009
MMON started with pid=14, OS id=3512
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PR
Wed Jan 07 09:01:13 2009
MMNL started with pid=15, OS id=7148
starting up 1 shared server(s) ...
Oracle Data Guard is not available in this edition of Oracle.
ORACLE_BASE from environment = C:\app\TEST
Wed Jan 07 09:01:13 2009
ALTER DATABASE MOUNT
Setting recovery target incarnation to 2
Errors in file c:\appTEST\diag\rdbms\orcl
ORA-01507: database not mounted
ORA-1507 signalled during: ALTER DATABASE MOUNT...
Contents of orcl_ora_5764.trc:see below
Trace file c:\app\TEST\diag\rdbms\orc
Oracle Database 11g Release 11.1.0.6.0 - Production
Windows XP Version V5.1 Service Pack 2
CPU : 2 - type 586
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:1123M/2004M, Ph+PgF:2229M/3314M, VA:1437M/2047M
Instance name: orcl
Redo thread mounted by this instance: 0 <none>
Oracle process number: 18
Windows thread id: 5764, image: ORACLE.EXE (SHAD)
*** 2009-01-07 09:01:19.611
*** SESSION ID:(225.3) 2009-01-07 09:01:19.611
*** CLIENT ID:() 2009-01-07 09:01:19.611
*** SERVICE NAME:() 2009-01-07 09:01:19.611
*** MODULE NAME:(sqlplus.exe) 2009-01-07 09:01:19.611
*** ACTION NAME:() 2009-01-07 09:01:19.611
ORA-01507: database not mounted
Status Failed
Details ORA-12528: TNS:listener: all appropriate instances are blocking new connections (DBD ERROR: OCIServerAttach)
I have tried to bounce the listener but it never worked using:
LSNRCTL> stop
LSNRCTL> start
Then restart your instance: sqlplus with sysdba privileges
SQL> startup
Also I have tried to do a shutdown immediate through RMAN and received the following:
Recovery Manager: Release 11.1.0.6.0 - Production on Wed Jan 7 08:42:05 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
RMAN> SHUTDOWN IMMEDIATE
using target database control file instead of recovery catalog
RMAN-00571: ==========================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ==========================
RMAN-03002: failure of shutdown command at 01/07/2009 08:45:15
RMAN-06171: not connected to target database
And then when I try to connect using the SYS account I get the following:
RMAN> connect target SYS/PASSWORD@orcl
RMAN-00571: ==========================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ==========================
ORA-12528: TNS:listener: all appropriate instances are blocking new connections
***I have tried restarting the computer many times without any luck, Contents of alert log below:
Starting up ORACLE RDBMS Version: 11.1.0.6.0.
Using parameter settings in server-side spfile C:\APP\TEST\PRODUCT\11.1.0
System parameters with non-default values:
processes = 200
memory_target = 392M
control_files = "C:\APP\TEST\ORADATA\ORCL\
control_files = "C:\APP\TEST\ORADATA\ORCL\
control_files = "C:\APP\TEST\ORADATA\ORCL\
db_block_size = 8192
compatible = "11.1.0.0.0"
log_archive_dest_1 = "LOCATION=C:\Flash_Recover
log_archive_dest_10 = "LOCATION=USE_DB_RECOVERY_
db_recovery_file_dest = "C:\Flash_Recovery_Area"
db_recovery_file_dest_size
undo_tablespace = "UNDOTBS1"
remote_login_passwordfile=
db_domain = ""
dispatchers = "(PROTOCOL=TCP) (SERVICE=orclXDB)"
audit_file_dest = "C:\APP\TEST\ADMIN\ORCL\AD
audit_trail = "DB"
db_name = "orcl"
open_cursors = 300
diagnostic_dest = "C:\APP\TEST"
Wed Jan 07 09:01:13 2009
PMON started with pid=2, OS id=6512
Wed Jan 07 09:01:13 2009
VKTM started with pid=3, OS id=5200 at elevated priority
VKTM running at (20)ms precision
Wed Jan 07 09:01:13 2009
DIAG started with pid=4, OS id=7852
Wed Jan 07 09:01:13 2009
DBRM started with pid=5, OS id=7076
Wed Jan 07 09:01:13 2009
PSP0 started with pid=6, OS id=6932
Wed Jan 07 09:01:13 2009
DSKM started with pid=7, OS id=7728
Wed Jan 07 09:01:13 2009
DIA0 started with pid=8, OS id=6352
Wed Jan 07 09:01:13 2009
MMAN started with pid=7, OS id=6796
Wed Jan 07 09:01:13 2009
DBW0 started with pid=9, OS id=3192
Wed Jan 07 09:01:13 2009
LGWR started with pid=10, OS id=5824
Wed Jan 07 09:01:13 2009
CKPT started with pid=11, OS id=3792
Wed Jan 07 09:01:13 2009
SMON started with pid=12, OS id=6460
Wed Jan 07 09:01:13 2009
RECO started with pid=13, OS id=7588
Wed Jan 07 09:01:13 2009
MMON started with pid=14, OS id=3512
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PR
Wed Jan 07 09:01:13 2009
MMNL started with pid=15, OS id=7148
starting up 1 shared server(s) ...
Oracle Data Guard is not available in this edition of Oracle.
ORACLE_BASE from environment = C:\app\TEST
Wed Jan 07 09:01:13 2009
ALTER DATABASE MOUNT
Setting recovery target incarnation to 2
Errors in file c:\appTEST\diag\rdbms\orcl
ORA-01507: database not mounted
ORA-1507 signalled during: ALTER DATABASE MOUNT...
Contents of orcl_ora_5764.trc:see below
Trace file c:\app\TEST\diag\rdbms\orc
Oracle Database 11g Release 11.1.0.6.0 - Production
Windows XP Version V5.1 Service Pack 2
CPU : 2 - type 586
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:1123M/2004M, Ph+PgF:2229M/3314M, VA:1437M/2047M
Instance name: orcl
Redo thread mounted by this instance: 0 <none>
Oracle process number: 18
Windows thread id: 5764, image: ORACLE.EXE (SHAD)
*** 2009-01-07 09:01:19.611
*** SESSION ID:(225.3) 2009-01-07 09:01:19.611
*** CLIENT ID:() 2009-01-07 09:01:19.611
*** SERVICE NAME:() 2009-01-07 09:01:19.611
*** MODULE NAME:(sqlplus.exe) 2009-01-07 09:01:19.611
*** ACTION NAME:() 2009-01-07 09:01:19.611
ORA-01507: database not mounted
This is known situation in the reason lies in bad dialog between the DB and the Listener.
The Listener has not registered the instance and so on ..
So the first step is to look for running firewalls on XP and shut them off.
Forever!
After that check the registration by the Listener:
Backup LISTENER.ORA file!!!!!!!
In ...\network\admin open listener.ora
see
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\Ora10g)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = test)
(ORACLE_HOME = D:\Ora10g)
)
)
Add the entry
(SID_DESC =
(SID_NAME = test)
(ORACLE_HOME = D:\Ora10g)
)
with regard to the particular Oracle Home and SID Name.
In Oracle 11g you have to create from scratch in the listener.ora file the section
SID_LIST_LISTENER =
&..
mentioned above.
c:>lsnrctl stop
c:>lsnctl start
After that give a try and report anomalies if any.
The Listener has not registered the instance and so on ..
So the first step is to look for running firewalls on XP and shut them off.
Forever!
After that check the registration by the Listener:
Backup LISTENER.ORA file!!!!!!!
In ...\network\admin open listener.ora
see
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\Ora10g)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = test)
(ORACLE_HOME = D:\Ora10g)
)
)
Add the entry
(SID_DESC =
(SID_NAME = test)
(ORACLE_HOME = D:\Ora10g)
)
with regard to the particular Oracle Home and SID Name.
In Oracle 11g you have to create from scratch in the listener.ora file the section
SID_LIST_LISTENER =
&..
mentioned above.
c:>lsnrctl stop
c:>lsnctl start
After that give a try and report anomalies if any.
ASKER
I appreciate the quick response and I have tried the WIN service stop/start of the listener but that doesn't seem to correct the problem. No error is given and the listener service stops and starts without a problem. I also tried to showdown and startup the database using sys as sysdba through sqlplus see below for the full script and errors:
SQL*Plus: Release 11.1.0.6.0 - Production on Wed Jan 7 10:45:39 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Enter user-name: sys as sysdba
Enter password:
Connected to:
Oracle Database 11g Release 11.1.0.6.0 - Production
SQL> shutdown
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 410112000 bytes
Fixed Size 1333536 bytes
Variable Size 301991648 bytes
Database Buffers 100663296 bytes
Redo Buffers 6123520 bytes
ORA-01507: database not mounted
SQL>
DVZ here is a link I found with a possible solution I think but I'm not sure how to perform what he is talking about: (How do you set the service name parameter to include the name of the instance?)
http://blogs.jadba.com/2008/08/resolving-ora-12528-errors.html
SQL*Plus: Release 11.1.0.6.0 - Production on Wed Jan 7 10:45:39 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Enter user-name: sys as sysdba
Enter password:
Connected to:
Oracle Database 11g Release 11.1.0.6.0 - Production
SQL> shutdown
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 410112000 bytes
Fixed Size 1333536 bytes
Variable Size 301991648 bytes
Database Buffers 100663296 bytes
Redo Buffers 6123520 bytes
ORA-01507: database not mounted
SQL>
DVZ here is a link I found with a possible solution I think but I'm not sure how to perform what he is talking about: (How do you set the service name parameter to include the name of the instance?)
http://blogs.jadba.com/2008/08/resolving-ora-12528-errors.html
I spoke about registration of the instance by the listener.
You have to check if the instance is registered so:
lsnrct services
If you do not see your instance here then you have to follow
the steps i mentioned above.
You have to check if the instance is registered so:
lsnrct services
If you do not see your instance here then you have to follow
the steps i mentioned above.
Please post the last hundred or so lines of the instance's alert.log. I'm trying to understand the link instructions as well....
ASKER
Schwertner here is the contents of my listener.ora file: just added the SID_LIST_LISTENER section and ran the commands below it all output is given below:
# listener.ora Network Configuration File: C:\app\test\product\11.1.0
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = isnet2.baycoat.com)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = C:\app\test\product\11.1.0
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = C:\app\test\product\11.1.0
)
)
Then I ran the following and here's what I got:
C:\Documents and Settings\test.BAYCOAT>lsnr
LSNRCTL for 32-bit Windows: Version 11.1.0.6.0 - Production on 07-JAN-2009 11:54
:05
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PRO
The command completed successfully
C:\Documents and Settings\test.BAYCOAT>lsnr
LSNRCTL for 32-bit Windows: Version 11.1.0.6.0 - Production on 07-JAN-2009 11:54
:23
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Starting tnslsnr: please wait...
TNSLSNR for 32-bit Windows: Version 11.1.0.6.0 - Production
System parameter file is C:\app\test\product\11.1.0
er.ora
Log messages written to c:\app\test\diag\tnslsnr\i
l
Listening on: (DESCRIPTION=(ADDRESS=(PRO
ipc)))
Listening on: (DESCRIPTION=(ADDRESS=(PRO
=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PRO
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 11.1.0.6.0 - Produ
ction
Start Date 07-JAN-2009 11:54:24
Uptime 0 days 0 hr. 0 min. 2 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\app\test\product\11.1.0
ner.ora
Listener Log File c:\app\test\diag\tnslsnr\i
xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PRO
(DESCRIPTION=(ADDRESS=(PRO
Services Summary...
Service "orcl" has 1 instance(s).
Instance "orcl", status UNKNOWN, has 2 handler(s) for this service...
The command completed successfully
C:\Documents and Settings\test.BAYCOAT>lsnr
LSNRCTL for 32-bit Windows: Version 11.1.0.6.0 - Production on 07-JAN-2009 11:55
:46
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PRO
Services Summary...
Service "orcl" has 2 instance(s).
Instance "orcl", status UNKNOWN, has 2 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
"DEDICATED" established:43 refused:0
LOCAL SERVER
Instance "orcl", status BLOCKED, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "orcl_XPT" has 1 instance(s).
Instance "orcl", status BLOCKED, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully
# listener.ora Network Configuration File: C:\app\test\product\11.1.0
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = isnet2.baycoat.com)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = C:\app\test\product\11.1.0
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = C:\app\test\product\11.1.0
)
)
Then I ran the following and here's what I got:
C:\Documents and Settings\test.BAYCOAT>lsnr
LSNRCTL for 32-bit Windows: Version 11.1.0.6.0 - Production on 07-JAN-2009 11:54
:05
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PRO
The command completed successfully
C:\Documents and Settings\test.BAYCOAT>lsnr
LSNRCTL for 32-bit Windows: Version 11.1.0.6.0 - Production on 07-JAN-2009 11:54
:23
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Starting tnslsnr: please wait...
TNSLSNR for 32-bit Windows: Version 11.1.0.6.0 - Production
System parameter file is C:\app\test\product\11.1.0
er.ora
Log messages written to c:\app\test\diag\tnslsnr\i
l
Listening on: (DESCRIPTION=(ADDRESS=(PRO
ipc)))
Listening on: (DESCRIPTION=(ADDRESS=(PRO
=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PRO
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 11.1.0.6.0 - Produ
ction
Start Date 07-JAN-2009 11:54:24
Uptime 0 days 0 hr. 0 min. 2 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\app\test\product\11.1.0
ner.ora
Listener Log File c:\app\test\diag\tnslsnr\i
xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PRO
(DESCRIPTION=(ADDRESS=(PRO
Services Summary...
Service "orcl" has 1 instance(s).
Instance "orcl", status UNKNOWN, has 2 handler(s) for this service...
The command completed successfully
C:\Documents and Settings\test.BAYCOAT>lsnr
LSNRCTL for 32-bit Windows: Version 11.1.0.6.0 - Production on 07-JAN-2009 11:55
:46
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PRO
Services Summary...
Service "orcl" has 2 instance(s).
Instance "orcl", status UNKNOWN, has 2 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
"DEDICATED" established:43 refused:0
LOCAL SERVER
Instance "orcl", status BLOCKED, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "orcl_XPT" has 1 instance(s).
Instance "orcl", status BLOCKED, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully
ASKER
Hi DVZ here are the last 100 lines or so of the alert_orcl.log
Starting up ORACLE RDBMS Version: 11.1.0.6.0.
Using parameter settings in server-side spfile C:\APP\TEST\PRODUCT\11.1.0
System parameters with non-default values:
processes = 200
memory_target = 392M
control_files = "C:\APP\TEST\ORADATA\ORCL\
control_files = "C:\APP\TEST\ORADATA\ORCL\
control_files = "C:\APP\TEST\ORADATA\ORCL\
db_block_size = 8192
compatible = "11.1.0.0.0"
log_archive_dest_1 = "LOCATION=C:\Flash_Recover
log_archive_dest_10 = "LOCATION=USE_DB_RECOVERY_
db_recovery_file_dest = "C:\Flash_Recovery_Area"
db_recovery_file_dest_size
undo_tablespace = "UNDOTBS1"
remote_login_passwordfile=
db_domain = ""
dispatchers = "(PROTOCOL=TCP) (SERVICE=orclXDB)"
audit_file_dest = "C:\APP\TEST\ADMIN\ORCL\AD
audit_trail = "DB"
db_name = "orcl"
open_cursors = 300
diagnostic_dest = "C:\APP\TEST"
Wed Jan 07 12:09:04 2009
PMON started with pid=2, OS id=1556
Wed Jan 07 12:09:04 2009
VKTM started with pid=3, OS id=1532 at elevated priority
VKTM running at (20)ms precision
Wed Jan 07 12:09:04 2009
DIAG started with pid=4, OS id=1096
Wed Jan 07 12:09:04 2009
DBRM started with pid=5, OS id=2120
Wed Jan 07 12:09:04 2009
PSP0 started with pid=6, OS id=2128
Wed Jan 07 12:09:04 2009
DSKM started with pid=7, OS id=1940
Wed Jan 07 12:09:04 2009
DIA0 started with pid=8, OS id=1640
Wed Jan 07 12:09:04 2009
MMAN started with pid=7, OS id=2060
Wed Jan 07 12:09:04 2009
DBW0 started with pid=9, OS id=2116
Wed Jan 07 12:09:04 2009
LGWR started with pid=10, OS id=2124
Wed Jan 07 12:09:04 2009
CKPT started with pid=11, OS id=2112
Wed Jan 07 12:09:04 2009
SMON started with pid=12, OS id=1136
Wed Jan 07 12:09:04 2009
RECO started with pid=13, OS id=1604
Wed Jan 07 12:09:04 2009
MMON started with pid=14, OS id=2224
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PR
Wed Jan 07 12:09:04 2009
MMNL started with pid=15, OS id=1368
starting up 1 shared server(s) ...
Oracle Data Guard is not available in this edition of Oracle.
ORACLE_BASE from environment = C:\app\TEST
Wed Jan 07 12:09:04 2009
alter database mount exclusive
Setting recovery target incarnation to 2
Errors in file c:\app\TEST\diag\rdbms\orc
ORA-01507: database not mounted
ORA-1507 signalled during: alter database mount exclusive...
Dump file c:\app\TEST\diag\rdbms\orc
Wed Jan 07 12:10:50 2009
ORACLE V11.1.0.6.0 - Production vsnsta=0
vsnsql=15 vsnxtr=3
Windows XP Version V5.1 Service Pack 2
CPU : 2 - type 586
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:1254M/2004M, Ph+PgF:2386M/3314M, VA:1431M/2047M
Wed Jan 07 12:10:50 2009
Errors in file
ORA-07445: caught exception [ACCESS_VIOLATION] at [{empty}] [0x7C911E58]
***Also when I open Database Control Console now under
Agent Connection to Instance
Status Failed
Details ORA-01033: ORACLE initialization or shutdown in progress (DBD ERROR: OCISessionBegin)
Starting up ORACLE RDBMS Version: 11.1.0.6.0.
Using parameter settings in server-side spfile C:\APP\TEST\PRODUCT\11.1.0
System parameters with non-default values:
processes = 200
memory_target = 392M
control_files = "C:\APP\TEST\ORADATA\ORCL\
control_files = "C:\APP\TEST\ORADATA\ORCL\
control_files = "C:\APP\TEST\ORADATA\ORCL\
db_block_size = 8192
compatible = "11.1.0.0.0"
log_archive_dest_1 = "LOCATION=C:\Flash_Recover
log_archive_dest_10 = "LOCATION=USE_DB_RECOVERY_
db_recovery_file_dest = "C:\Flash_Recovery_Area"
db_recovery_file_dest_size
undo_tablespace = "UNDOTBS1"
remote_login_passwordfile=
db_domain = ""
dispatchers = "(PROTOCOL=TCP) (SERVICE=orclXDB)"
audit_file_dest = "C:\APP\TEST\ADMIN\ORCL\AD
audit_trail = "DB"
db_name = "orcl"
open_cursors = 300
diagnostic_dest = "C:\APP\TEST"
Wed Jan 07 12:09:04 2009
PMON started with pid=2, OS id=1556
Wed Jan 07 12:09:04 2009
VKTM started with pid=3, OS id=1532 at elevated priority
VKTM running at (20)ms precision
Wed Jan 07 12:09:04 2009
DIAG started with pid=4, OS id=1096
Wed Jan 07 12:09:04 2009
DBRM started with pid=5, OS id=2120
Wed Jan 07 12:09:04 2009
PSP0 started with pid=6, OS id=2128
Wed Jan 07 12:09:04 2009
DSKM started with pid=7, OS id=1940
Wed Jan 07 12:09:04 2009
DIA0 started with pid=8, OS id=1640
Wed Jan 07 12:09:04 2009
MMAN started with pid=7, OS id=2060
Wed Jan 07 12:09:04 2009
DBW0 started with pid=9, OS id=2116
Wed Jan 07 12:09:04 2009
LGWR started with pid=10, OS id=2124
Wed Jan 07 12:09:04 2009
CKPT started with pid=11, OS id=2112
Wed Jan 07 12:09:04 2009
SMON started with pid=12, OS id=1136
Wed Jan 07 12:09:04 2009
RECO started with pid=13, OS id=1604
Wed Jan 07 12:09:04 2009
MMON started with pid=14, OS id=2224
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PR
Wed Jan 07 12:09:04 2009
MMNL started with pid=15, OS id=1368
starting up 1 shared server(s) ...
Oracle Data Guard is not available in this edition of Oracle.
ORACLE_BASE from environment = C:\app\TEST
Wed Jan 07 12:09:04 2009
alter database mount exclusive
Setting recovery target incarnation to 2
Errors in file c:\app\TEST\diag\rdbms\orc
ORA-01507: database not mounted
ORA-1507 signalled during: alter database mount exclusive...
Dump file c:\app\TEST\diag\rdbms\orc
Wed Jan 07 12:10:50 2009
ORACLE V11.1.0.6.0 - Production vsnsta=0
vsnsql=15 vsnxtr=3
Windows XP Version V5.1 Service Pack 2
CPU : 2 - type 586
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:1254M/2004M, Ph+PgF:2386M/3314M, VA:1431M/2047M
Wed Jan 07 12:10:50 2009
Errors in file
ORA-07445: caught exception [ACCESS_VIOLATION] at [{empty}] [0x7C911E58]
***Also when I open Database Control Console now under
Agent Connection to Instance
Status Failed
Details ORA-01033: ORACLE initialization or shutdown in progress (DBD ERROR: OCISessionBegin)
ASKER
Database Control now stating:
Agent Connection to Instance
Status Failed
Details ORA-01033: ORACLE initialization or shutdown in progress (DBD ERROR: OCISessionBegin)
also the following command produces the results:
C:\Documents and Settings\test.BAYCOAT>lsnr
LSNRCTL for 32-bit Windows: Version 11.1.0.6.0 - Production on 07-JAN-2009 13:09
:10
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PRO
Services Summary...
Service "orcl" has 2 instance(s).
Instance "orcl", status UNKNOWN, has 2 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
"DEDICATED" established:497 refused:0
LOCAL SERVER
Instance "orcl", status BLOCKED, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "orcl_XPT" has 1 instance(s).
Instance "orcl", status BLOCKED, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully
***Should I change my listener.ora file back to:
# listener.ora Network Configuration File: C:\app\test\product\11.1.0
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = isnet2.baycoat.com)(PORT = 1521))
)
)
Agent Connection to Instance
Status Failed
Details ORA-01033: ORACLE initialization or shutdown in progress (DBD ERROR: OCISessionBegin)
also the following command produces the results:
C:\Documents and Settings\test.BAYCOAT>lsnr
LSNRCTL for 32-bit Windows: Version 11.1.0.6.0 - Production on 07-JAN-2009 13:09
:10
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PRO
Services Summary...
Service "orcl" has 2 instance(s).
Instance "orcl", status UNKNOWN, has 2 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
"DEDICATED" established:497 refused:0
LOCAL SERVER
Instance "orcl", status BLOCKED, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "orcl_XPT" has 1 instance(s).
Instance "orcl", status BLOCKED, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully
***Should I change my listener.ora file back to:
# listener.ora Network Configuration File: C:\app\test\product\11.1.0
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = isnet2.baycoat.com)(PORT = 1521))
)
)
Listener.ora is now OK!
But something strange happens with the instance.
I thing you run into a bug.
If you have Metalink support open a SR to Oracle.
Is this production or development?
What about new installation.
Also check that well known default firewall of Windows and turn it off
if it runs.
But something strange happens with the instance.
I thing you run into a bug.
If you have Metalink support open a SR to Oracle.
Is this production or development?
What about new installation.
Also check that well known default firewall of Windows and turn it off
if it runs.
ASKER
Just to verify this listener.ora file below looks good to you schwertner?
# listener.ora Network Configuration File: C:\app\test\product\11.1.0
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = isnet2.baycoat.com)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = C:\app\test\product\11.1.0
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = C:\app\test\product\11.1.0
)
)
Yes Windows Firewall is turned off always has been.
Yes this is a development server and I will eventually open a SR to Oracle, I just wanted to see if I could solve it here first at E.E.
So the error I am receiving now through Database Control is:
Agent Connection to Instance
Status Failed
Details ORA-01033: ORACLE initialization or shutdown in progress (DBD ERROR: OCISessionBegin)
I appreciate your on going support, do you know now what might be causing the above error?
# listener.ora Network Configuration File: C:\app\test\product\11.1.0
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = isnet2.baycoat.com)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = C:\app\test\product\11.1.0
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = C:\app\test\product\11.1.0
)
)
Yes Windows Firewall is turned off always has been.
Yes this is a development server and I will eventually open a SR to Oracle, I just wanted to see if I could solve it here first at E.E.
So the error I am receiving now through Database Control is:
Agent Connection to Instance
Status Failed
Details ORA-01033: ORACLE initialization or shutdown in progress (DBD ERROR: OCISessionBegin)
I appreciate your on going support, do you know now what might be causing the above error?
From your command out put
SQL> startup
ORACLE instance started.
Total System Global Area 410112000 bytes
Fixed Size 1333536 bytes
Variable Size 301991648 bytes
Database Buffers 100663296 bytes
Redo Buffers 6123520 bytes
ORA-01507: database not mounted
From you alert log file
alter database mount exclusive
Setting recovery target incarnation to 2
Errors in file c:\app\TEST\diag\rdbms\orc
ORA-01507: database not mounted
ORA-1507 signalled during: alter database mount exclusive...
Your database is not started. You need to check c:\app\TEST\diag\rdbms\orc
HTH
Vishal
SQL> startup
ORACLE instance started.
Total System Global Area 410112000 bytes
Fixed Size 1333536 bytes
Variable Size 301991648 bytes
Database Buffers 100663296 bytes
Redo Buffers 6123520 bytes
ORA-01507: database not mounted
From you alert log file
alter database mount exclusive
Setting recovery target incarnation to 2
Errors in file c:\app\TEST\diag\rdbms\orc
ORA-01507: database not mounted
ORA-1507 signalled during: alter database mount exclusive...
Your database is not started. You need to check c:\app\TEST\diag\rdbms\orc
HTH
Vishal
On metalink there are some docs (addressed to 10g, not to 11g)
that says this what you experience is a bug.
I have about 8 smootly running 11g instances, but on Linux.
So I am very surprised by this bug, because 11g was very carefully
cleaned from bugs before initial release.
Follow vishal68 advice. Possibly you will find some additional details there.
that says this what you experience is a bug.
I have about 8 smootly running 11g instances, but on Linux.
So I am very surprised by this bug, because 11g was very carefully
cleaned from bugs before initial release.
Follow vishal68 advice. Possibly you will find some additional details there.
ASKER
Vishal68 - Below is the details of the requested files
schwertner - Nothing I have tried has worked to resolve the issue i do think this is a bug
Last few lines of alert_orcl.log
Thu Jan 08 07:57:42 2009
alter database mount exclusive
Setting recovery target incarnation to 2
Errors in file c:\app\test\diag\rdbms\orc
ORA-01507: database not mounted
ORA-1507 signalled during: alter database mount exclusive...
Dump file c:\app\test\diag\rdbms\orc
Thu Jan 08 08:00:41 2009
ORACLE V11.1.0.6.0 - Production vsnsta=0
vsnsql=15 vsnxtr=3
Windows XP Version V5.1 Service Pack 2
CPU : 2 - type 586
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:1219M/2004M, Ph+PgF:2347M/3314M, VA:1428M/2047M
Thu Jan 08 08:00:41 2009
Errors in file
ORA-07445: caught exception [ACCESS_VIOLATION] at [{empty}] [0x7C9111E0]
contents of orcl_ora_1812.trc
Trace file c:\app\test\diag\rdbms\orc
Oracle Database 11g Release 11.1.0.6.0 - Production
Windows XP Version V5.1 Service Pack 2
CPU : 2 - type 586
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:1408M/2004M, Ph+PgF:2603M/3314M, VA:1435M/2047M
Instance name: orcl
Redo thread mounted by this instance: 0 <none>
Oracle process number: 18
Windows thread id: 1812, image: ORACLE.EXE (SHAD)
*** 2009-01-08 07:57:46.687
*** SESSION ID:(225.3) 2009-01-08 07:57:46.687
*** CLIENT ID:() 2009-01-08 07:57:46.687
*** SERVICE NAME:() 2009-01-08 07:57:46.687
*** MODULE NAME:(oradim.exe) 2009-01-08 07:57:46.687
*** ACTION NAME:() 2009-01-08 07:57:46.687
ORA-01507: database not mounted
schwertner - Nothing I have tried has worked to resolve the issue i do think this is a bug
Last few lines of alert_orcl.log
Thu Jan 08 07:57:42 2009
alter database mount exclusive
Setting recovery target incarnation to 2
Errors in file c:\app\test\diag\rdbms\orc
ORA-01507: database not mounted
ORA-1507 signalled during: alter database mount exclusive...
Dump file c:\app\test\diag\rdbms\orc
Thu Jan 08 08:00:41 2009
ORACLE V11.1.0.6.0 - Production vsnsta=0
vsnsql=15 vsnxtr=3
Windows XP Version V5.1 Service Pack 2
CPU : 2 - type 586
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:1219M/2004M, Ph+PgF:2347M/3314M, VA:1428M/2047M
Thu Jan 08 08:00:41 2009
Errors in file
ORA-07445: caught exception [ACCESS_VIOLATION] at [{empty}] [0x7C9111E0]
contents of orcl_ora_1812.trc
Trace file c:\app\test\diag\rdbms\orc
Oracle Database 11g Release 11.1.0.6.0 - Production
Windows XP Version V5.1 Service Pack 2
CPU : 2 - type 586
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:1408M/2004M, Ph+PgF:2603M/3314M, VA:1435M/2047M
Instance name: orcl
Redo thread mounted by this instance: 0 <none>
Oracle process number: 18
Windows thread id: 1812, image: ORACLE.EXE (SHAD)
*** 2009-01-08 07:57:46.687
*** SESSION ID:(225.3) 2009-01-08 07:57:46.687
*** CLIENT ID:() 2009-01-08 07:57:46.687
*** SERVICE NAME:() 2009-01-08 07:57:46.687
*** MODULE NAME:(oradim.exe) 2009-01-08 07:57:46.687
*** ACTION NAME:() 2009-01-08 07:57:46.687
ORA-01507: database not mounted
Would you like instruction on how to use Metalink for querying their bug database?
Please also check thoroughly the Installation Guide on Windows 2003
and check if all prerequisites are fulfilled.
Whath about the PATH and LD_LIBRARY_PATH environment variables?
How many Oracle Home Homes do you have?
and check if all prerequisites are fulfilled.
Whath about the PATH and LD_LIBRARY_PATH environment variables?
How many Oracle Home Homes do you have?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
vishal68 you were correct that was the last thing I did before restarting the database. I set the Quota Size for my archive log destination because it was exceeding the maximum. The exact steps I followed were:
1. log into SQLPLuS as sysdba and ran:
2. SQL> create pfile from spfile;
3. set log_archive_dest_1=' QUOTA_SIZE=0' in the INITorcl.ORA and SPFILEORCL.ORA back to zero.
4. then I had to restart the oracle instance through the services panel before running:
5. SQL> create spfile from pfile
6. Restarted the server and Oracle fired up!
***So I'm pretty sure that the best way to clean up obsolete backups is by using the database control
Availability > Manage Current Backups
Thanks again for all your help.
1. log into SQLPLuS as sysdba and ran:
2. SQL> create pfile from spfile;
3. set log_archive_dest_1=' QUOTA_SIZE=0' in the INITorcl.ORA and SPFILEORCL.ORA back to zero.
4. then I had to restart the oracle instance through the services panel before running:
5. SQL> create spfile from pfile
6. Restarted the server and Oracle fired up!
***So I'm pretty sure that the best way to clean up obsolete backups is by using the database control
Availability > Manage Current Backups
Thanks again for all your help.
ASKER
Thanks again!
Side question: ensure that you do not have multiple listener processes. Your code shows you've handled the default. I'd also try a WIN service stop/start of the listener.