Link to home
Start Free TrialLog in
Avatar of afytech1
afytech1

asked on

Oracle DB down -----Oracle 7.3.4

Hi,

For a few weeks I've been having a problem with the DB going down.  I've always been able to get it going by going into server manager and issuing the "shutdown abort" command followed by a "start" command.  At that point I would get a message that a particular datafile needed to be recovered.  I then issued the "recover datafile......" command, followed by the "start" command, and the DB would come up normally.  

Now, however, after the first "shutdown abort", when I issue the "start" command, I am getting error ORA-01034..."oracle not available".

What do I need to do?

Thanks
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

hi,
  please try to restart the svrmgr73, and if that does not help, check if the process smon is still there:

  ps -ef | grep smon

  if it is not, then main oracle process is not started. check the Oracle Startup configuration files (not sure where they are located exactly under solaris)

to attract more oracle gurus, you should post a question pointer in the databases/oracle topic area
Avatar of afytech1
afytech1

ASKER

Here is the output from the ps -ef command:

 ps -ef | grep smon
    root  2765  2727  0 09:03:25 pts/0    0:00 grep smon
  oracle  1189     1  0 08:34:38 ?        0:00 ora_smon_S734
  oracle  1314     1  0 08:34:50 ?        0:00 ora_smon_DSS
  oracle  1334     1  0 08:34:57 ?        0:00 ora_smon_AVYX

We have three instances:

DSS
AVYX
COLOR

It appears there is no process for COLOR...how do I start it.

Thanks
the output looks like you DO have 3 instances running, possibly there is a different process name vs instance name.
what is the configuration in the relevant initCOLOR.ora resp. initS734.ora files?
where would those files be located?   (I'm new to Oracle administration:-)
in the ORACLE_HOME folder in the database subfolder
There was a very old problem with Oracle sometimes leaving the memory segments around even though the database was down.  I do not remember the symptom, but it may have been this.

If you run this command "ipcs -bm"  you should see three segments owned by oracle.  One for each instance.  If you see four, then you have this problem.  Hopefully, the last column which is the size will help you determine which one is the extra.  If you can determine which is the extra one, use "ipcrm -m <id>" to remove it.  <id> should be replaced with the number in the first column of the output of the ipcs command.  After removing the segment, you should be able to start up the database.
I do see four processes...two are identical in size.

IPC status from <running system> as of Tue Feb 14 09:19:37 2006
T         ID      KEY        MODE        OWNER    GROUP      SEGSZ
Shared Memory:
m          0   0x50000a38 --rw-r--r--     root     root         68
m          1   0x0d2b3f3c --rw-r-----   oracle      dba    4517888
m          2   0x0c69dcbe --rw-r-----   oracle      dba   13123584
m          3   0x0b1adf5f --rw-r-----   oracle      dba   36962304
m          4   0x0d2eecb7 --rw-r-----   oracle      dba   13123584

How can I tell which one is not needed?  and how do I tell which process is tied to which instance?

Thanks
One way is to shutdown all the existing instances of Oracle and see which of the shared RAM segments remains.
There appear to be init files for 4 instances....

configAVYX.ora   init.ora         initDSS_0.ora    initcolor_0.ora  lkS734           sgadefcolor.dbf
configDSS.ora    initAVYX.ora     initS734.ora     lkAVYX           sgadefAVYX.dbf   sql.bsq
configS734.ora   initAVYX_0.ora   initS734_0.ora   lkCOLOR          sgadefDSS.dbf
configcolor.ora  initDSS.ora      initcolor.ora    lkDSS            sgadefS734.dbf

How do I start the process for the COLOR instance?

Thanks
"One way is to shutdown all the existing instances of Oracle and see which of the shared RAM segments remains."


Would you elaborate on the steps to accomplish this?

Thanks
I am not sure if this exists in Oracle7, but is does in Oracle8.  Be sure that your ORACLE_SID is set to the database that is down and run "$ORACLE_HOME/bin/sysresv".  That should tell you the ID for the memory segment for that instance.

Otherwise, you can log into each database that is up and check the size of the SGA.  If you can eliminate the 3 from the list, the fourth one is the one to remove.  You can determine the size of the SGA from logging into server manager and doing "show sga".
Here is my last attempt to bring the DB up:

SunOS 5.6

login: oracle
Password:
Last login: Tue Feb 14 10:02:47 from 10.10.1.168
Sun Microsystems Inc.   SunOS 5.6       Generic August 1997
You have new mail.


  TERM = xterm
  HOST = a1afya10
  BASE = /d001/oracle
  COMMON = /d002/oracle/common/

  ORACLE SIDs
  Available on a1afya10:

    DSS
    COLOR
    AVYX

  Select any of the above by entering the
    ORACLE_SID as a command or run .dbaprof.
    Default SID of DSS has been selected.

[DSS:/d001/oracle] color
[color:/d001/oracle] show sga
ksh: show:  not found
[color:/d001/oracle] svrmgrl

Oracle Server Manager Release 2.3.4.0.0 - Production

Copyright (c) Oracle Corporation 1994, 1995. All rights reserved.

Oracle7 Server Release 7.3.4.3.0 - Production
With the distributed, replication and parallel query options
PL/SQL Release 2.3.4.3.0 - Production

SVRMGR> show sga
ORA-01012: not logged on
SVRMGR> connect internal
Connected.
SVRMGR> show sga
Total System Global Area      13113280 bytes
Fixed Size                       39816 bytes
Variable Size                  6487096 bytes
Database Buffers               6553600 bytes
Redo Buffers                     32768 bytes
SVRMGR> exit
Server Manager complete.
[color:/d001/oracle] DSS
[DSS:/d001/oracle] svrmgrl

Oracle Server Manager Release 2.3.4.0.0 - Production

Copyright (c) Oracle Corporation 1994, 1995. All rights reserved.

Oracle7 Server Release 7.3.4.3.0 - Production
With the distributed, replication and parallel query options
PL/SQL Release 2.3.4.3.0 - Production

SVRMGR> connect internal
Connected.
SVRMGR> show sga
Total System Global Area      36952428 bytes
Fixed Size                       39816 bytes
Variable Size                 10534372 bytes
Database Buffers              26214400 bytes
Redo Buffers                    163840 bytes
SVRMGR> exit
Server Manager complete.
[DSS:/d001/oracle] avyx
[AVYX:/d001/oracle] svrmgrl

Oracle Server Manager Release 2.3.4.0.0 - Production

Copyright (c) Oracle Corporation 1994, 1995. All rights reserved.

Oracle7 Server Release 7.3.4.3.0 - Production
With the distributed, replication and parallel query options
PL/SQL Release 2.3.4.3.0 - Production

SVRMGR> connect internal
Connected.
SVRMGR> show sga
Total System Global Area      13113280 bytes
Fixed Size                       39816 bytes
Variable Size                  6487096 bytes
Database Buffers               6553600 bytes
Redo Buffers                     32768 bytes
SVRMGR> exit
Server Manager complete.
[AVYX:/d001/oracle] color
[color:/d001/oracle] svrmgrl

Oracle Server Manager Release 2.3.4.0.0 - Production

Copyright (c) Oracle Corporation 1994, 1995. All rights reserved.

Oracle7 Server Release 7.3.4.3.0 - Production
With the distributed, replication and parallel query options
PL/SQL Release 2.3.4.3.0 - Production

SVRMGR> connect internal
Connected.
SVRMGR> shutdown abort
ORACLE instance shut down.
SVRMGR> start
     2> ;
start
*
ORA-01034: ORACLE not available
SVRMGR> exit

"Normally" when I issue the "start" command I get a message telling be that a datafile needs to be recovered and I do that with the recover database command.  Now, I just get the ORA-01034 error.  I have tried ending the other two instances, but nothing seems to work.  When I end them and try to start them back, I get the same error message.

Any ideas?

Thanks
Based on the sizes in the show sga commands, the "extra" memory segment is segment 1.

Run this:

ipcrm -m 1

Then try to start up the COLOR instance.
I performed another boot of the system.  I found the dbstart.log file and have posted it below.

Normally, when I issue the "start" command, I see this

Database mounted.
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/d103/oracle/color/color.data.1.dbf'

and then I issue the "recover datafile...." command and that fixes it.

But today, I'm getting the ORA-01034 error.  


Thanks


********************************************************
LSNRCTL for Solaris: Version 2.3.4.0.0 - Production on 14-FEB-06 11:45:40

Copyright (c) Oracle Corporation 1994.  All rights reserved.

Starting /d001/oracle/7.3.4/bin/tnslsnr: please wait...

TNSLSNR for Solaris: Version 2.3.4.0.0 - Production
System parameter file is /var/opt/oracle/listener.ora
Log messages written to /d001/oracle/7.3.4/network/log/listener.log
Listening on: (ADDRESS=(PROTOCOL=ipc)(DEV=10)(KEY=S734))
Listening on: (ADDRESS=(PROTOCOL=ipc)(DEV=13)(KEY=PNPKEY))
Listening on: (ADDRESS=(PROTOCOL=tcp)(DEV=15)(HOST=10.10.1.30)(PORT=1521))

Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=S734))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Solaris: Version 2.3.4.0.0 - Production
Start Date                14-FEB-06 11:45:44
Uptime                    0 days 0 hr. 0 min. 1 sec
Trace Level               off
Security                  OFF
SNMP                      OFF
Listener Parameter File   /var/opt/oracle/listener.ora
Listener Log File         /d001/oracle/7.3.4/network/log/listener.log
Services Summary...
  AVYX          has 1 service handler(s)
  DSS           has 1 service handler(s)
  S734          has 1 service handler(s)
  color         has 1 service handler(s)
The command completed successfully

Oracle Server Manager Release 2.3.4.0.0 - Production

Copyright (c) Oracle Corporation 1994, 1995. All rights reserved.

Oracle7 Server Release 7.3.4.3.0 - Production
With the distributed, replication and parallel query options
PL/SQL Release 2.3.4.3.0 - Production

SVRMGR> Connected to an idle instance.
SVRMGR> ORACLE instance started.
Total System Global Area       4509584 bytes
Fixed Size                       39816 bytes
Variable Size                  4051976 bytes
Database Buffers                409600 bytes
Redo Buffers                      8192 bytes
Database mounted.
Database opened.
SVRMGR>
Server Manager complete.

Database "S734" warm started.

Oracle Server Manager Release 2.3.4.0.0 - Production

Copyright (c) Oracle Corporation 1994, 1995. All rights reserved.

Oracle7 Server Release 7.3.4.3.0 - Production
With the distributed, replication and parallel query options
PL/SQL Release 2.3.4.3.0 - Production

SVRMGR> Connected to an idle instance.
SVRMGR> ORACLE instance started.
Total System Global Area      13113280 bytes
Fixed Size                       39816 bytes
Variable Size                  6487096 bytes
Database Buffers               6553600 bytes
Redo Buffers                     32768 bytes
Database mounted.
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/d103/oracle/color/color.data.1.dbf'
SVRMGR>
Server Manager complete.

Database "color" warm started.

Oracle Server Manager Release 2.3.4.0.0 - Production

Copyright (c) Oracle Corporation 1994, 1995. All rights reserved.

Oracle7 Server Release 7.3.4.3.0 - Production
With the distributed, replication and parallel query options
PL/SQL Release 2.3.4.3.0 - Production

SVRMGR> Connected to an idle instance.
SVRMGR> ORACLE instance started.
Total System Global Area      36952428 bytes
Fixed Size                       39816 bytes
Variable Size                 10534372 bytes
Database Buffers              26214400 bytes
Redo Buffers                    163840 bytes
Database mounted.
ORA-01545: rollback segment 'R01' specified not available
SVRMGR>
Server Manager complete.

Database "DSS" warm started.

Oracle Server Manager Release 2.3.4.0.0 - Production

Copyright (c) Oracle Corporation 1994, 1995. All rights reserved.

Oracle7 Server Release 7.3.4.3.0 - Production
With the distributed, replication and parallel query options
PL/SQL Release 2.3.4.3.0 - Production

SVRMGR> Connected to an idle instance.
SVRMGR> ORACLE instance started.
Total System Global Area      13113280 bytes
Fixed Size                       39816 bytes
Variable Size                  6487096 bytes
Database Buffers               6553600 bytes
Redo Buffers                     32768 bytes
Database mounted.
ORA-01113: file 7 needs media recovery
ORA-01110: data file 7: '/d105/oracle/AVYX/avyx.data.1.dbf'
SVRMGR>
Server Manager complete.

Database "AVYX" warm started.
It doesn't appear to me that there is an "extra" process running.

Ideas?


# ps -ef | grep smon
  oracle  1156     1  0 11:45:56 ?        0:00 ora_smon_color
  oracle  1073     1  0 11:45:48 ?        0:00 ora_smon_S734
  oracle  1178     1  0 11:46:00 ?        0:00 ora_smon_DSS
  oracle  1198     1  0 11:46:07 ?        0:00 ora_smon_AVYX
    root  1341  1244  0 12:11:29 pts/2    0:00 grep smon
# ipcs -bm
IPC status from <running system> as of Tue Feb 14 12:12:14 2006
T         ID      KEY        MODE        OWNER    GROUP      SEGSZ
Shared Memory:
m          0   0x50000a38 --rw-r--r--     root     root         68
m          1   0x0d2b3f3c --rw-r-----   oracle      dba    4517888
m          2   0x0c69dcbe --rw-r-----   oracle      dba   13123584
m          3   0x0b1adf5f --rw-r-----   oracle      dba   36962304
m          4   0x0d2eecb7 --rw-r-----   oracle      dba   13123584
Also....the only instance that we even need or care about is "color".  DSS and AVYX are no longer used.  If someone can tell me how to "safely get rid of" unneeded instances that would be great.

Thanks again!
I found this:

ORA-01034:      ORACLE not available
Cause:      Oracle was not started. Possible causes include
the following:

    * The SGA requires more space than was allocated for it.
    * The operating-system variable pointing to the instance is improperly defined.

Action:      Refer to accompanying messages for possible
causes and correct the problem mentioned in the other messages. If Oracle has been initialized, then on some operating systems, verify that Oracle was linked correctly. See the platform-specific Oracle documentation.

Could space be and issue?  How can I tell how much space SGA requires and what space does SGA use...ie where on the disk is it looking for space?

Thanks
Now there is no extra memory segment, as you rebooted the server.  Did you try the ipcrm???

After the startup, it appears the instance started.  You should issue a "shutdown" and then start it back up and it should give you the recovery message.
I didn't run the "ipcrm" command, I just rebooted the server.

I did what I normally have done to recover the datafile, but am still getting the ORA-01034 error.  Any ideas to get around this?

I really appreciate your efforts on this!!

********************************************************

[color:/d001/oracle] svrmgrl

Oracle Server Manager Release 2.3.4.0.0 - Production

Copyright (c) Oracle Corporation 1994, 1995. All rights reserved.

Oracle7 Server Release 7.3.4.3.0 - Production
With the distributed, replication and parallel query options
PL/SQL Release 2.3.4.3.0 - Production

SVRMGR> connect internal
Connected to an idle instance.
SVRMGR> shutdown abort
ORACLE instance shut down.
SVRMGR> start
     2> ;
start
*
ORA-01034: ORACLE not available
Here is the "alert_color.log" file.  Notice the error near the bottom.  Does that help any?

Thanks

*******************************************************************

 Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
LICENSE_MAX_USERS = 0
Starting up ORACLE RDBMS Version: 7.3.4.3.0.
System parameters with non-default values:
  processes                = 100
  shared_pool_size         = 5000000
  control_files            = /d101/oracle/color/color.control1.ctl, /d102/oracle/color/color.control2.ctl, /d103/oracle/color/color.control3.ctl
  compatible               = 7.3.4.3
  log_archive_start        = TRUE
  log_archive_dest         = /d801/oracle/color/archive/arch_
  log_archive_format       = %s_color.log
  log_buffer               = 32768
  log_checkpoint_interval  = 10000
  rollback_segments        = rollback1, rollback2, rollback3, rollback4
  sequence_cache_hash_buckets= 10
  remote_login_passwordfile= NONE
  mts_service              = color
  mts_servers              = 0
  mts_max_servers          = 0
  mts_max_dispatchers      = 0
  audit_trail              = NONE
  sort_area_retained_size  = 65536
  sort_direct_writes       = AUTO
  db_name                  = color
  ifile                    = /d001/oracle/7.3.4/dbs/configcolor.ora
  background_dump_dest     = /d002/oracle/color/dumpb
  user_dump_dest           = /d002/oracle/color/dumpu
  core_dump_dest           = /d002/oracle/color/dumpc
PMON started
DBWR started
ARCH started
LGWR started
RECO started
Tue Feb 14 12:26:21 2006
alter database  mount exclusive
Tue Feb 14 12:26:21 2006
Successful mount of redo thread 1.
Tue Feb 14 12:26:21 2006
Completed: alter database  mount exclusive
Tue Feb 14 12:26:21 2006
alter database open
ORA-1113 signalled during: alter database open...
Tue Feb 14 12:48:20 2006
Shutting down instance (abort)
License high water mark = 1
ASKER CERTIFIED SOLUTION
Avatar of johnsone
johnsone
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
That's the problem, when I enter the "start" command, I get the "ORA-01034 - Oracle not available" message.  Same thing if I enter the "recover database" command.

Any way you could give me a call?  If so, I'll post the phone number.
You need to remove the memory segment.  Run this command:

ipcs -bm

You will see a line where the last number is 4517888

Take the first number from that line (the id) and put it in this command:

ipcrm -m <id>

Then try the startup.
I found the problem, sorry to say it was me.  I had been using "start" instead of "startup".  I had this in my documentation and thought I had remembered using it.

Is START a valid command for anything (related to Oracle and this OS)?

Thanks, and sorry for all the hassle.
Yes.  Start is used to run SQL scripts.  It is not used often, as @ is a lot shorter to type.