OraFAQ Forum: Server Administration » Idle Instance issue

Home » RDBMS Server » Server Administration » Idle Instance issue (Oracle MySQL, 11g2R, Linux)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Idle Instance issue [message #595170]

Sun, 08 September 2013 21:20

robdba
Messages: 11
Registered: September 2013

Junior Member

[oracle@localhost /]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Sun Sep 8 19:10:30 2013

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

Connected to an idle instance.
--------------------------------------

SQL> startup
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '/home/oracle/app/oracle/product/11.2.0/dbhome_2/dbs/'
ORA-27037: unable to obtain file status
Additional information: 5

What seems to be a problem here? I've been trying to fix this for few weeks. Please help.

Report message to a moderator

Re: Idle Instance issue [message #595171 is a reply to message #595170]

Sun, 08 September 2013 21:28

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

what additional clues exist in alert_SID.log file

post excerpt from alert_SID.log file which shows the error & surrounding lines.

Report message to a moderator

Re: Idle Instance issue [message #595174 is a reply to message #595171]

Mon, 09 September 2013 00:17

robdba
Messages: 11
Registered: September 2013

Junior Member

SQL> desc v$diag_info
ERROR:
ORA-01012: not logged on
------------------------------------
SQL> show parameter background
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0

This is what I get...

Report message to a moderator

Re: Idle Instance issue [message #595176 is a reply to message #595174]

Mon, 09 September 2013 00:19

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

What don't you understand in the following sentence:

Quote:

post excerpt from alert_SID.log file which shows the error & surrounding lines.

Regards
Michel

Report message to a moderator

Re: Idle Instance issue [message #595179 is a reply to message #595174]

Mon, 09 September 2013 00:34

robdba
Messages: 11
Registered: September 2013

Junior Member

These are the last few from Alert Log File. I hope this will help you to figure out.

<msg time='2013-06-17T20:14:10.506-07:00' org_id='oracle' comp_id='rdbms'
msg_id='ksbrdp:4183:3697353022' type='NOTIFICATION' group='process start'
level='16' host_id='localhost.localdomain' host_addr='127.0.0.1'
pid='2618'>
<txt>CJQ0 started with pid=38, OS id=2618
</txt>
</msg>

<msg time='2013-06-17T20:14:12.256-07:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='localhost.localdomain' host_addr='127.0.0.1' module='MMON_SLAVE'
pid='2604'>
<txt>db_recovery_file_dest_size of 3852 MB is 0.24% used. This is a
</txt>
</msg>

<msg time='2013-06-17T20:14:12.256-07:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='localhost.localdomain' host_addr='127.0.0.1' module='MMON_SLAVE'
pid='2604'>
<txt>user-specified limit on the amount of space that will be used by this
</txt>
</msg>

<msg time='2013-06-17T20:14:12.257-07:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='localhost.localdomain' host_addr='127.0.0.1' module='MMON_SLAVE'
pid='2604'>
<txt>database for recovery-related files, and does not reflect the amount of
</txt>
</msg>

<msg time='2013-06-17T20:14:12.257-07:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='localhost.localdomain' host_addr='127.0.0.1' module='MMON_SLAVE'
pid='2604'>
<txt>space available in the underlying filesystem or ASM diskgroup.
</txt>
</msg>

<msg time='2013-06-17T20:19:02.457-07:00' org_id='oracle' comp_id='rdbms'
msg_id='ksbs1p_real:2401:2371767696' client_id='' type='NOTIFICATION'
group='process start' level='16' host_id='localhost.localdomain'
host_addr='127.0.0.1' module='' pid='2435'>
<txt>Starting background process SMCO
</txt>
</msg>

<msg time='2013-06-17T20:19:02.489-07:00' org_id='oracle' comp_id='rdbms'
msg_id='ksbrdp:4183:3697353022' type='NOTIFICATION' group='process start'
level='16' host_id='localhost.localdomain' host_addr='127.0.0.1'
pid='3226'>
<txt>SMCO started with pid=28, OS id=3226
</txt>
</msg>

<msg time='2013-06-17T20:27:52.710-07:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='localhost.localdomain' host_addr='127.0.0.1' module='sqlplus@localhost.localdomain (TNS V1-V3)'
pid='3214'>
<txt>ALTER SYSTEM SET sga_target='1400M' SCOPE=SPFILE;
</txt>
</msg>

<msg time='2013-06-17T20:39:40.823-07:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='localhost.localdomain' host_addr='127.0.0.1' module=''
pid='2431'>
<txt>Thread 1 advanced to log sequence 626 (LGWR switch)
</txt>
</msg>

<msg time='2013-06-17T20:39:40.823-07:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='localhost.localdomain' host_addr='127.0.0.1' module=''
pid='2431'>
<txt> Current log# 2 seq# 626 mem# 0: /home/oracle/app/oracle/oradata/orcl/redo02.log
</txt>
</msg>

<msg time='2013-06-17T20:41:30.150-07:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='localhost.localdomain' host_addr='127.0.0.1' module=''
pid='2431'>
<txt>Thread 1 advanced to log sequence 627 (LGWR switch)
</txt>
</msg>

<msg time='2013-06-17T20:41:30.150-07:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='localhost.localdomain' host_addr='127.0.0.1' module=''
pid='2431'>
<txt> Current log# 3 seq# 627 mem# 0: /home/oracle/app/oracle/oradata/orcl/redo03.log
</txt>
</msg>

<msg time='2013-06-17T20:55:35.445-07:00' org_id='oracle' comp_id='rdbms'
msg_id='ksbs1p_real:2401:2371767696' client_id='' type='NOTIFICATION'
group='process start' level='16' host_id='localhost.localdomain'
host_addr='127.0.0.1' module='sqlplus@localhost.localdomain (TNS V1-V3)' pid='3559'>
<txt>Starting background process EMNC
</txt>
</msg>

<msg time='2013-06-17T20:55:35.472-07:00' org_id='oracle' comp_id='rdbms'
msg_id='ksbrdp:4183:3697353022' type='NOTIFICATION' group='process start'
level='16' host_id='localhost.localdomain' host_addr='127.0.0.1'
pid='3561'>
<txt>EMNC started with pid=41, OS id=3561
</txt>
</msg>

Report message to a moderator

Re: Idle Instance issue [message #595185 is a reply to message #595179]

Mon, 09 September 2013 01:47

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Please post text file NOT the xml one, it is unreadable.
Or post the last 50 txt fields and only this field and converted to text (that is convert all &<something> and so on).

Regards
Michel

Report message to a moderator

Re: Idle Instance issue [message #595192 is a reply to message #595185]

Mon, 09 September 2013 02:36

robdba
Messages: 11
Registered: September 2013

Junior Member

I am not sure if this is the txt file... or please tell me the steps how to get the txt file if you have time. Thank you Michel.

Mon Jun 17 20:13:47 2013
MMON started with pid=15, OS id=2439
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Mon Jun 17 20:13:47 2013
MMNL started with pid=16, OS id=2441
starting up 10 shared server(s) ...
ORACLE_BASE not set in environment. It is recommended
that ORACLE_BASE be set in the environment
Reusing ORACLE_BASE from an earlier startup = /home/oracle/app/oracle
Mon Jun 17 20:13:47 2013
ALTER DATABASE MOUNT
Successful mount of redo thread 1, with mount id 1346164091
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
Mon Jun 17 20:13:52 2013
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
Started redo scan
Completed redo scan
read 149 KB redo, 72 data blocks need recovery
Started redo application at
Thread 1: logseq 624, block 17916
Recovery of Online Redo Log: Thread 1 Group 3 Seq 624 Reading mem 0
Mem# 0: /home/oracle/app/oracle/oradata/orcl/redo03.log
Completed redo application of 0.05MB
Completed crash recovery at
Thread 1: logseq 624, block 18214, scn 14310523
72 data blocks read, 72 data blocks written, 149 redo k-bytes read
Thread 1 advanced to log sequence 625 (thread open)
Thread 1 opened at log sequence 625
Current log# 1 seq# 625 mem# 0: /home/oracle/app/oracle/oradata/orcl/redo01.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
SMON: enabling cache recovery
[2488] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:4294108870 end:4294109520 diff:650 (6 seconds)
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is AL32UTF8
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Mon Jun 17 20:14:00 2013
QMNC started with pid=29, OS id=2490
Mon Jun 17 20:14:04 2013
Completed: ALTER DATABASE OPEN
Mon Jun 17 20:14:10 2013
Starting background process CJQ0
Mon Jun 17 20:14:10 2013
CJQ0 started with pid=38, OS id=2618
Mon Jun 17 20:14:12 2013
db_recovery_file_dest_size of 3852 MB is 0.24% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Mon Jun 17 20:19:02 2013
Starting background process SMCO
Mon Jun 17 20:19:02 2013
SMCO started with pid=28, OS id=3226
Mon Jun 17 20:27:52 2013
ALTER SYSTEM SET sga_target='1400M' SCOPE=SPFILE;
Mon Jun 17 20:39:40 2013
Thread 1 advanced to log sequence 626 (LGWR switch)
Current log# 2 seq# 626 mem# 0: /home/oracle/app/oracle/oradata/orcl/redo02.log
Mon Jun 17 20:41:30 2013
Thread 1 advanced to log sequence 627 (LGWR switch)
Current log# 3 seq# 627 mem# 0: /home/oracle/app/oracle/oradata/orcl/redo03.log
Mon Jun 17 20:55:35 2013
Starting background process EMNC
Mon Jun 17 20:55:35 2013
EMNC started with pid=41, OS id=3561

Report message to a moderator

Re: Idle Instance issue [message #595197 is a reply to message #595192]

Mon, 09 September 2013 03:18

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

This is the correct file but the error is before.
Search for the last "Starting up" and post from this line.

Regards
Michel

Report message to a moderator

Re: Idle Instance issue [message #595223 is a reply to message #595197]

Mon, 09 September 2013 08:24

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Starting background process EMNC
Mon Jun 17 20:55:35 2013
EMNC started with pid=41, OS id=3561

what has this DB been doing for the last 2.5 months?
Why do you think these lines are relevant to today's issue?

Report message to a moderator

Re: Idle Instance issue [message #595251 is a reply to message #595170]

Mon, 09 September 2013 12:05

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

robdba wrote on Sun, 08 September 2013 21:20

[oracle@localhost /]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Sun Sep 8 19:10:30 2013

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

Connected to an idle instance.
--------------------------------------

SQL> startup
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '/home/oracle/app/oracle/product/11.2.0/dbhome_2/dbs/'
ORA-27037: unable to obtain file status
Additional information: 5

What seems to be a problem here? I've been trying to fix this for few weeks. Please help.

The ORA-01078 says it couldn't process the system (intialilzation) parameters. Without that, it can't even set up memory structures, nor know where to write alert log messages.

The ORA-01565 says it can't identify a file (the init_sid.ora or spffilesid.ora) but only lists a directory.

What is in /home/oracle/app/oracle/product/11.2.0/dbhome_2/dbs/? Show us an 'ls -l /home/oracle/app/oracle/product/11.2.0/dbhome_2/dbs'

Report message to a moderator

Re: Idle Instance issue [message #595277 is a reply to message #595251]

Mon, 09 September 2013 19:49

robdba
Messages: 11
Registered: September 2013

Junior Member

@EdStevens,
This is what's in /dbhome_2/dbs. Thank you.
[oracle@localhost dbs]$ ls -l
total 9576
-rw-rw---- 1 oracle oracle 1544 Oct 2 2010 hc_DBUA0.dat
-rw-rw---- 1 oracle oracle 1544 Sep 8 20:22 hc_orcl.dat
-rw-r--r-- 1 oracle oracle 2859 Jun 20 12:33 init.ora
-rw-r--r-- 1 oracle oracle 2851 Jun 20 12:02 init.ora~
-rw-r----- 1 oracle oracle 64 Sep 8 18:47 initorcl.ora
-rw-r----- 1 oracle oracle 1142 Jun 20 12:31 initorcl.ora~
-rw-r----- 1 oracle oracle 1142 Jun 20 12:33 initorcl.ora.2013-09-01_14-59-16
-rw-r----- 1 oracle oracle 24 Oct 2 2010 lkORCL
-rw-r----- 1 oracle oracle 1536 Oct 2 2010 orapworcl
-rw-rw---- 1 oracle oracle 9748480 May 20 11:13 snapcf_orcl.f
-rw-rw---- 1 oracle oracle 1536 Sep 8 18:47 spfileorcl.ora

@BlackSwan-
I have no idea that's why it's an issue. If I would know, I'd not post it here. I thought this is the right place to get help and so I am expeting to get some help. Sorry if it's really a small problem for you, but at the moment, I've been scratching my head trying to fix this. It's been coulple of months that my SQL has not been working. Thank you.

@Michel Cadot
Here we go Michel. Please let me know what additional step I need to take to fix this issue. Thank you.
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
Using parameter settings in server-side spfile /home/oracle/app/oracle/product/11.2.0/dbhome_2/dbs/spfileorcl.ora
System parameters with non-default values:
processes = 150
event = ""
memory_target = 436M
control_files = "/home/oracle/app/oracle/oradata/orcl/control01.ctl"
control_files = "/home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl"
db_block_size = 8192
compatible = "11.2.0.0.0"
db_recovery_file_dest = "/home/oracle/app/oracle/flash_recovery_area"
db_recovery_file_dest_size= 3852M
undo_tablespace = "UNDOTBS1"
remote_login_passwordfile= "EXCLUSIVE"
db_domain = ""
dispatchers = "(PROTOCOL=TCP) (SERVICE=orclXDB)"
shared_servers = 10
max_shared_servers = 5
local_listener = "LISTENER_ORCL"
audit_file_dest = "/home/oracle/app/oracle/admin/orcl/adump"
audit_trail = "DB"
db_name = "orcl"
open_cursors = 300
client_result_cache_size = 64M
client_result_cache_lag = 3000
diagnostic_dest = "/home/oracle/app/oracle"
Mon Jun 17 20:13:46 2013
PSP0 started with pid=3, OS id=2413
Mon Jun 17 20:13:46 2013
PMON started with pid=2, OS id=2411
Mon Jun 17 20:13:47 2013
VKTM started with pid=4, OS id=2415 at elevated priority
Mon Jun 17 20:13:47 2013
DIAG started with pid=6, OS id=2421
Mon Jun 17 20:13:47 2013
DBRM started with pid=7, OS id=2423
VKTM running at (1)millisec precision with DBRM quantum (100)ms
Mon Jun 17 20:13:47 2013
DIA0 started with pid=8, OS id=2425
Mon Jun 17 20:13:47 2013
MMAN started with pid=9, OS id=2427
Mon Jun 17 20:13:47 2013
GEN0 started with pid=5, OS id=2419
Mon Jun 17 20:13:47 2013
DBW0 started with pid=10, OS id=2429
Mon Jun 17 20:13:47 2013
LGWR started with pid=11, OS id=2431
Mon Jun 17 20:13:47 2013
CKPT started with pid=12, OS id=2433
Mon Jun 17 20:13:47 2013
SMON started with pid=13, OS id=2435
Mon Jun 17 20:13:47 2013
RECO started with pid=14, OS id=2437
Mon Jun 17 20:13:47 2013
MMON started with pid=15, OS id=2439
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Mon Jun 17 20:13:47 2013
MMNL started with pid=16, OS id=2441
starting up 10 shared server(s) ...
ORACLE_BASE not set in environment. It is recommended
that ORACLE_BASE be set in the environment
Reusing ORACLE_BASE from an earlier startup = /home/oracle/app/oracle
Mon Jun 17 20:13:47 2013
ALTER DATABASE MOUNT
Successful mount of redo thread 1, with mount id 1346164091
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
Mon Jun 17 20:13:52 2013
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
Started redo scan
Completed redo scan
read 149 KB redo, 72 data blocks need recovery
Started redo application at
Thread 1: logseq 624, block 17916
Recovery of Online Redo Log: Thread 1 Group 3 Seq 624 Reading mem 0
Mem# 0: /home/oracle/app/oracle/oradata/orcl/redo03.log
Completed redo application of 0.05MB
Completed crash recovery at
Thread 1: logseq 624, block 18214, scn 14310523
72 data blocks read, 72 data blocks written, 149 redo k-bytes read
Thread 1 advanced to log sequence 625 (thread open)
Thread 1 opened at log sequence 625
Current log# 1 seq# 625 mem# 0: /home/oracle/app/oracle/oradata/orcl/redo01.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
SMON: enabling cache recovery
[2488] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:4294108870 end:4294109520 diff:650 (6 seconds)
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is AL32UTF8
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Mon Jun 17 20:14:00 2013
QMNC started with pid=29, OS id=2490
Mon Jun 17 20:14:04 2013
Completed: ALTER DATABASE OPEN
Mon Jun 17 20:14:10 2013
Starting background process CJQ0
Mon Jun 17 20:14:10 2013
CJQ0 started with pid=38, OS id=2618
Mon Jun 17 20:14:12 2013
db_recovery_file_dest_size of 3852 MB is 0.24% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Mon Jun 17 20:19:02 2013
Starting background process SMCO
Mon Jun 17 20:19:02 2013
SMCO started with pid=28, OS id=3226
Mon Jun 17 20:27:52 2013
ALTER SYSTEM SET sga_target='1400M' SCOPE=SPFILE;
Mon Jun 17 20:39:40 2013
Thread 1 advanced to log sequence 626 (LGWR switch)
Current log# 2 seq# 626 mem# 0: /home/oracle/app/oracle/oradata/orcl/redo02.log
Mon Jun 17 20:41:30 2013
Thread 1 advanced to log sequence 627 (LGWR switch)
Current log# 3 seq# 627 mem# 0: /home/oracle/app/oracle/oradata/orcl/redo03.log
Mon Jun 17 20:55:35 2013
Starting background process EMNC
Mon Jun 17 20:55:35 2013
EMNC started with pid=41, OS id=3561

Report message to a moderator

Re: Idle Instance issue [message #595278 is a reply to message #595277]

Mon, 09 September 2013 20:00

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Mon Jun 17 20:55:35 2013

it is now almost 3 MONTHS since the excerpt above was created!

>[oracle@localhost /]$ sqlplus / as sysdba
>SQL*Plus: Release 11.2.0.2.0 Production on Sun Sep 8 19:10:30 2013

somewhere & someplace is a log file which recorded the failed startup from within the last 48 HOURS!

we can not know what is wrong now looking at irrelevant entries from JUNE!

Report message to a moderator

Re: Idle Instance issue [message #595282 is a reply to message #595278]

Mon, 09 September 2013 21:02

robdba
Messages: 11
Registered: September 2013

Junior Member

I've had this problem since June. Since then I've been trying to fix it. I'm a rookie in this field. I've been learning/using Linux mostly but I try to mess with MySQL according to my online classes. And in June, I had this problem and I completely stopped working with MySQL. Just few days back, I started to reseach online help, I came across this page and found out outstanding source to learn, get help and meet up experts. If this can't be fixed, please tell me what can I do to re-set MySQL and bring it to day 1. Thank you.

Report message to a moderator

Re: Idle Instance issue [message #595283 is a reply to message #595282]

Mon, 09 September 2013 21:09

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

> If this can't be fixed, please tell me what can I do to re-set MySQL and bring it to day 1.
One of us is VERY confused.
MYSQL & Oracle RDBMS are completely different products & I am not sure what product you have installed.
I suggest that you stop wasting your time with both of them & first learn how to use MS ACCESS; which is more user-friendly application.

Report message to a moderator

Re: Idle Instance issue [message #595284 is a reply to message #595283]

Mon, 09 September 2013 21:25

robdba
Messages: 11
Registered: September 2013

Junior Member

Actually I meant to say SQL*PLUS from Oracle RDBMS. sorry.

Report message to a moderator

Re: Idle Instance issue [message #595285 is a reply to message #595284]

Mon, 09 September 2013 21:43

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

post results from following OS command below

find / -name alert_orcl.log -ls 2>/dev/null

Report message to a moderator

Re: Idle Instance issue [message #595286 is a reply to message #595285]

Mon, 09 September 2013 21:53

robdba
Messages: 11
Registered: September 2013

Junior Member

[oracle@localhost ~]$ find / -name alert_orcl.log -ls 2>/dev/null
722840 792 -rw-rw---- 1 oracle oracle 804126 Sep 9 00:34 /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log
1806225 4 -rw-rw---- 1 oracle oracle 224 Sep 1 19:45 /home/oracle/app/oracle/product/11.2.0/dbhome_2/log/diag/rdbms/unknown/orcl/trace/alert_orcl.log
1805361 8 -rw-r----- 1 oracle oracle 7827 Sep 8 20:22 /home/oracle/app/oracle/product/11.2.0/dbhome_2/log/diag/rdbms/dummy/orcl/trace/alert_orcl.log
2981909 4 -rw-r----- 1 oracle oracle 2609 Sep 1 14:59 /home/oracle/app/oracle/product/11.2.0/dbhome_1/log/diag/rdbms/dummy/orcl/trace/alert_orcl.log

Report message to a moderator

Re: Idle Instance issue [message #595287 is a reply to message #595286]

Mon, 09 September 2013 21:58

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

please guess which file contains clues about the most recent STARTUP which we need to review?
please post excerpt back here.

Report message to a moderator

Re: Idle Instance issue [message #595288 is a reply to message #595287]

Mon, 09 September 2013 23:26

robdba
Messages: 11
Registered: September 2013

Junior Member

I am guessing this is the file:
7827 Sep 8 20:22 /home/oracle/app/oracle/product/11.2.0/dbhome_2/log/diag/rdbms/dummy/orcl/trace/alert_orcl.log

Sun Sep 01 14:59:18 2013
Starting ORACLE instance (restrict)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_1 parameter default value as /home/oracle/app/oracle/product/11.2.0/dbhome_2/dbs/arch
Autotune of undo retention is turned on.
IMODE=BR
ILAT =18
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
Using parameter settings in client-side pfile /tmp/ora_tfil5qSlnT on machine localhost.localdomain
System parameters with non-default values:
sga_target = 152M
compatible = "11.2.0.2.0"
_dummy_instance = TRUE
remote_login_passwordfile= "EXCLUSIVE"
db_name = "DUMMY"
Sun Sep 01 14:59:20 2013
PMON started with pid=2, OS id=5500
Sun Sep 01 14:59:20 2013
PSP0 started with pid=3, OS id=5502
Sun Sep 01 14:59:21 2013
VKTM started with pid=4, OS id=5504 at elevated priority
Sun Sep 01 14:59:21 2013
GEN0 started with pid=5, OS id=5508
Sun Sep 01 14:59:21 2013
DIAG started with pid=6, OS id=5510
VKTM running at (1)millisec precision with DBRM quantum (100)ms
Sun Sep 01 14:59:21 2013
DBRM started with pid=7, OS id=5512
Sun Sep 01 14:59:21 2013
DIA0 started with pid=8, OS id=5514
Sun Sep 01 14:59:21 2013
MMAN started with pid=9, OS id=5516
Sun Sep 01 14:59:21 2013
DBW0 started with pid=10, OS id=5518
Sun Sep 01 14:59:21 2013
LGWR started with pid=11, OS id=5520
Sun Sep 01 14:59:21 2013
CKPT started with pid=12, OS id=5522
Sun Sep 01 14:59:21 2013
SMON started with pid=13, OS id=5524
Sun Sep 01 14:59:21 2013
RECO started with pid=14, OS id=5526
Sun Sep 01 14:59:21 2013
MMON started with pid=15, OS id=5528
Sun Sep 01 14:59:21 2013
MMNL started with pid=16, OS id=5530
ORACLE_BASE not set in environment. It is recommended
that ORACLE_BASE be set in the environment
Sun Sep 01 14:59:27 2013
Shutting down instance (immediate)
Shutting down instance: further logons disabled
Stopping background process MMNL
Stopping background process MMON
License high water mark = 3
alter database close
ORA-1507 signalled during: alter database close...
alter database dismount
ORA-1507 signalled during: alter database dismount...
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Stopping background process VKTM
Sun Sep 01 14:59:32 2013
Instance shutdown complete
Sun Sep 08 20:22:17 2013
Starting ORACLE instance (restrict)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_1 parameter default value as /home/oracle/app/oracle/product/11.2.0/dbhome_2/dbs/arch
Autotune of undo retention is turned on.
IMODE=BR
ILAT =18
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
Using parameter settings in client-side pfile /tmp/ora_tfilVpCSzQ on machine localhost.localdomain
System parameters with non-default values:
sga_target = 152M
compatible = "11.2.0.2.0"
_dummy_instance = TRUE
remote_login_passwordfile= "EXCLUSIVE"
db_name = "DUMMY"
Sun Sep 08 20:22:18 2013
PMON started with pid=2, OS id=7228
Sun Sep 08 20:22:18 2013
PSP0 started with pid=3, OS id=7230
Sun Sep 08 20:22:20 2013
VKTM started with pid=4, OS id=7233 at elevated priority
Sun Sep 08 20:22:20 2013
GEN0 started with pid=5, OS id=7237
Sun Sep 08 20:22:20 2013
DIAG started with pid=6, OS id=7239
VKTM running at (1)millisec precision with DBRM quantum (100)ms
Sun Sep 08 20:22:20 2013
DBRM started with pid=7, OS id=7241
Sun Sep 08 20:22:20 2013
DIA0 started with pid=8, OS id=7243
Sun Sep 08 20:22:20 2013
MMAN started with pid=9, OS id=7245
Sun Sep 08 20:22:20 2013
DBW0 started with pid=10, OS id=7247
Sun Sep 08 20:22:20 2013
LGWR started with pid=11, OS id=7249
Sun Sep 08 20:22:20 2013
CKPT started with pid=12, OS id=7251
Sun Sep 08 20:22:20 2013
SMON started with pid=13, OS id=7253
Sun Sep 08 20:22:20 2013
RECO started with pid=14, OS id=7255
Sun Sep 08 20:22:20 2013
MMON started with pid=15, OS id=7257
Sun Sep 08 20:22:20 2013
MMNL started with pid=16, OS id=7259
ORACLE_BASE not set in environment. It is recommended
that ORACLE_BASE be set in the environment
Sun Sep 08 20:22:22 2013
Shutting down instance (immediate)
Shutting down instance: further logons disabled
Stopping background process MMNL
Stopping background process MMON
License high water mark = 3
alter database close
ORA-1507 signalled during: alter database close...
alter database dismount
ORA-1507 signalled during: alter database dismount...
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Stopping background process VKTM
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Sun Sep 08 20:22:25 2013
Instance shutdown complete
Sun Sep 08 20:22:27 2013
Starting ORACLE instance (restrict)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_1 parameter default value as /home/oracle/app/oracle/product/11.2.0/dbhome_2/dbs/arch
Autotune of undo retention is turned on.
IMODE=BR
ILAT =18
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
Using parameter settings in client-side pfile /tmp/ora_tfileXiDjN on machine localhost.localdomain
System parameters with non-default values:
sga_target = 152M
compatible = "11.2.0.2.0"
_dummy_instance = TRUE
remote_login_passwordfile= "EXCLUSIVE"
db_name = "DUMMY"
Sun Sep 08 20:22:27 2013
PMON started with pid=2, OS id=7291
Sun Sep 08 20:22:27 2013
PSP0 started with pid=3, OS id=7293
Sun Sep 08 20:22:27 2013
VKTM started with pid=4, OS id=7295 at elevated priority
Sun Sep 08 20:22:27 2013
GEN0 started with pid=5, OS id=7299
VKTM running at (1)millisec precision with DBRM quantum (100)ms
Sun Sep 08 20:22:27 2013
DIAG started with pid=6, OS id=7301
Sun Sep 08 20:22:27 2013
DBRM started with pid=7, OS id=7303
Sun Sep 08 20:22:27 2013
DIA0 started with pid=8, OS id=7305
Sun Sep 08 20:22:27 2013
MMAN started with pid=9, OS id=7307
Sun Sep 08 20:22:27 2013
DBW0 started with pid=10, OS id=7309
Sun Sep 08 20:22:27 2013
LGWR started with pid=11, OS id=7311
Sun Sep 08 20:22:27 2013
CKPT started with pid=12, OS id=7313
Sun Sep 08 20:22:27 2013
SMON started with pid=13, OS id=7315
Sun Sep 08 20:22:27 2013
RECO started with pid=14, OS id=7317
Sun Sep 08 20:22:27 2013
MMON started with pid=15, OS id=7319
Sun Sep 08 20:22:27 2013
MMNL started with pid=16, OS id=7321
ORACLE_BASE not set in environment. It is recommended
that ORACLE_BASE be set in the environment
Sun Sep 08 20:22:29 2013
Shutting down instance (immediate)
Shutting down instance: further logons disabled
Stopping background process MMNL
Stopping background process MMON
License high water mark = 3
alter database close
ORA-1507 signalled during: alter database close...
alter database dismount
ORA-1507 signalled during: alter database dismount...
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Stopping background process VKTM
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Sun Sep 08 20:22:33 2013
Instance shutdown complete

Report message to a moderator

Re: Idle Instance issue [message #595290 is a reply to message #595288]

Mon, 09 September 2013 23:40

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Stopping background process VKTM
>Sun Sep 01 14:59:32 2013
>Instance shutdown complete
>Sun Sep 08 20:22:17 2013
>Starting ORACLE instance (restrict)

How to reconcile above with below?

>[oracle@localhost /]$ sqlplus / as sysdba
>SQL*Plus: Release 11.2.0.2.0 Production on Sun Sep 8 19:10:30 2013

the STARTUP should have been recorded within time frame at the top.
I can not reconcile the disparate "facts" in this thread.
I will cease trying to solve this riddle, since I can nor discern fact from fantasy.

Report message to a moderator

Re: Idle Instance issue [message #595291 is a reply to message #595290]

Mon, 09 September 2013 23:53

robdba
Messages: 11
Registered: September 2013

Junior Member

Thanks for your time. That's what I've been going through and I have no idea when did I go wrong or what did I do wrong? I still can't figure it out. Just a question, is there any way or something I can do to bring sql*plus to day 1? or are you saying that my systems sql*plus will never run?

Report message to a moderator

Re: Idle Instance issue [message #595293 is a reply to message #595291]

Mon, 09 September 2013 23:59

robdba
Messages: 11
Registered: September 2013

Junior Member

This is the day when my sql*plus went down. I don't if it helps but this is the alert_orcl.log from June.
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
Using parameter settings in server-side spfile /home/oracle/app/oracle/product/11.2.0/dbhome_2/dbs/spfileorcl.ora
System parameters with non-default values:
processes = 150
event = ""
memory_target = 436M
control_files = "/home/oracle/app/oracle/oradata/orcl/control01.ctl"
control_files = "/home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl"
db_block_size = 8192
compatible = "11.2.0.0.0"
db_recovery_file_dest = "/home/oracle/app/oracle/flash_recovery_area"
db_recovery_file_dest_size= 3852M
undo_tablespace = "UNDOTBS1"
remote_login_passwordfile= "EXCLUSIVE"
db_domain = ""
dispatchers = "(PROTOCOL=TCP) (SERVICE=orclXDB)"
shared_servers = 10
max_shared_servers = 5
local_listener = "LISTENER_ORCL"
audit_file_dest = "/home/oracle/app/oracle/admin/orcl/adump"
audit_trail = "DB"
db_name = "orcl"
open_cursors = 300
client_result_cache_size = 64M
client_result_cache_lag = 3000
diagnostic_dest = "/home/oracle/app/oracle"
Mon Jun 17 20:13:46 2013
PSP0 started with pid=3, OS id=2413
Mon Jun 17 20:13:46 2013
PMON started with pid=2, OS id=2411
Mon Jun 17 20:13:47 2013
VKTM started with pid=4, OS id=2415 at elevated priority
Mon Jun 17 20:13:47 2013
DIAG started with pid=6, OS id=2421
Mon Jun 17 20:13:47 2013
DBRM started with pid=7, OS id=2423
VKTM running at (1)millisec precision with DBRM quantum (100)ms
Mon Jun 17 20:13:47 2013
DIA0 started with pid=8, OS id=2425
Mon Jun 17 20:13:47 2013
MMAN started with pid=9, OS id=2427
Mon Jun 17 20:13:47 2013
GEN0 started with pid=5, OS id=2419
Mon Jun 17 20:13:47 2013
DBW0 started with pid=10, OS id=2429
Mon Jun 17 20:13:47 2013
LGWR started with pid=11, OS id=2431
Mon Jun 17 20:13:47 2013
CKPT started with pid=12, OS id=2433
Mon Jun 17 20:13:47 2013
SMON started with pid=13, OS id=2435
Mon Jun 17 20:13:47 2013
RECO started with pid=14, OS id=2437
Mon Jun 17 20:13:47 2013
MMON started with pid=15, OS id=2439
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Mon Jun 17 20:13:47 2013
MMNL started with pid=16, OS id=2441
starting up 10 shared server(s) ...
ORACLE_BASE not set in environment. It is recommended
that ORACLE_BASE be set in the environment
Reusing ORACLE_BASE from an earlier startup = /home/oracle/app/oracle
Mon Jun 17 20:13:47 2013
ALTER DATABASE MOUNT
Successful mount of redo thread 1, with mount id 1346164091
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
Mon Jun 17 20:13:52 2013
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
Started redo scan
Completed redo scan
read 149 KB redo, 72 data blocks need recovery
Started redo application at
Thread 1: logseq 624, block 17916
Recovery of Online Redo Log: Thread 1 Group 3 Seq 624 Reading mem 0
Mem# 0: /home/oracle/app/oracle/oradata/orcl/redo03.log
Completed redo application of 0.05MB
Completed crash recovery at
Thread 1: logseq 624, block 18214, scn 14310523
72 data blocks read, 72 data blocks written, 149 redo k-bytes read
Thread 1 advanced to log sequence 625 (thread open)
Thread 1 opened at log sequence 625
Current log# 1 seq# 625 mem# 0: /home/oracle/app/oracle/oradata/orcl/redo01.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
SMON: enabling cache recovery
[2488] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:4294108870 end:4294109520 diff:650 (6 seconds)
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is AL32UTF8
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Mon Jun 17 20:14:00 2013
QMNC started with pid=29, OS id=2490
Mon Jun 17 20:14:04 2013
Completed: ALTER DATABASE OPEN
Mon Jun 17 20:14:10 2013
Starting background process CJQ0
Mon Jun 17 20:14:10 2013
CJQ0 started with pid=38, OS id=2618
Mon Jun 17 20:14:12 2013
db_recovery_file_dest_size of 3852 MB is 0.24% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Mon Jun 17 20:19:02 2013
Starting background process SMCO
Mon Jun 17 20:19:02 2013
SMCO started with pid=28, OS id=3226
Mon Jun 17 20:27:52 2013
ALTER SYSTEM SET sga_target='1400M' SCOPE=SPFILE;
Mon Jun 17 20:39:40 2013
Thread 1 advanced to log sequence 626 (LGWR switch)
Current log# 2 seq# 626 mem# 0: /home/oracle/app/oracle/oradata/orcl/redo02.log
Mon Jun 17 20:41:30 2013
Thread 1 advanced to log sequence 627 (LGWR switch)
Current log# 3 seq# 627 mem# 0: /home/oracle/app/oracle/oradata/orcl/redo03.log
Mon Jun 17 20:55:35 2013
Starting background process EMNC
Mon Jun 17 20:55:35 2013
EMNC started with pid=41, OS id=3561

Report message to a moderator

Re: Idle Instance issue [message #595327 is a reply to message #595293]

Tue, 10 September 2013 03:16

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

The trouble is you're posting confusing logs - some are ancient and dont contain what they should given the error you are reporting.

It *looks* like you're missing a parameter file, but it may be more than that, really the right alert log is needed to assist properly.

Report message to a moderator

Re: Idle Instance issue [message #595347 is a reply to message #595277]

Tue, 10 September 2013 07:41

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

robdba wrote on Mon, 09 September 2013 19:49

@EdStevens,
This is what's in /dbhome_2/dbs. Thank you.
[oracle@localhost dbs]$ ls -l
total 9576
-rw-rw---- 1 oracle oracle 1544 Oct 2 2010 hc_DBUA0.dat
-rw-rw---- 1 oracle oracle 1544 Sep 8 20:22 hc_orcl.dat
-rw-r--r-- 1 oracle oracle 2859 Jun 20 12:33 init.ora
-rw-r--r-- 1 oracle oracle 2851 Jun 20 12:02 init.ora~
-rw-r----- 1 oracle oracle 64 Sep 8 18:47 initorcl.ora
-rw-r----- 1 oracle oracle 1142 Jun 20 12:31 initorcl.ora~
-rw-r----- 1 oracle oracle 1142 Jun 20 12:33 initorcl.ora.2013-09-01_14-59-16
-rw-r----- 1 oracle oracle 24 Oct 2 2010 lkORCL
-rw-r----- 1 oracle oracle 1536 Oct 2 2010 orapworcl
-rw-rw---- 1 oracle oracle 9748480 May 20 11:13 snapcf_orcl.f
-rw-rw---- 1 oracle oracle 1536 Sep 8 18:47 spfileorcl.ora
<snip>

show us the contents of init.ora and initorcl.ora
looks like initorcl.ora was modified a couple of days ago .... is this a detail you have not disclosed to us?

Report message to a moderator

Re: Idle Instance issue [message #595348 is a reply to message #595288]

Tue, 10 September 2013 07:44

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

robdba wrote on Mon, 09 September 2013 23:26

I am guessing this is the file:
7827 Sep 8 20:22 /home/oracle/app/oracle/product/11.2.0/dbhome_2/log/diag/rdbms/dummy/orcl/trace/alert_orcl.log
<snip>

Why would you guess a file from a directory named 'dummy'?

Why not the one from "/home/oracle/app/oracle/product/11.2.0/dbhome_2/log/diag/rdbms/orcl/orcl/trace/alert_orcl.log"

Report message to a moderator

Previous Topic:	gathering database statistics
Next Topic:	ORA-04030

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 13:49:22 CDT 2024