AutoArchival?

Oracle 8i

I've commented out the archiving option
##### For archiving if enabled #####
#log_archive_start = true
#log_archive_dest = "/data1/oradata/angus/arch"
#log_archive_format = arch_%t_%s.arc

and have tried to start the instance with no luck. I tried both startup mount and startup. The error messages I get are below.

> svrmgrl

Oracle Server Manager Release 3.1.7.0.0 - Production

Copyright (c) 1997, 1999, Oracle Corporation. All Rights Reserved.

SVRMGR> startup mount
LCC-00161: Message 161 not found; product=SVRMGR; facility=MGR

ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect descriptor
SVRMGR> startup
LCC-00161: Message 161 not found; product=SVRMGR; facility=MGR

ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect descriptor
SVRMGR>

Check to make sure that your oracle_home and the other oracle environment had been set correctly. Is it a test or production instance?

were you able to bring up the instance without the archive log parameters, (i.e) in noarchive log mode? I am asking this because to figure out from where the MGR & TNS are poping out. If you are successfull, then try to connect to the instance through sqlplus and make sure that your tns configuration is fine. if not try restarting the listener


lsnrctl reload

Check to make sure that you have the ORACLE_SID being set to the instance sid, in your environment, before you do a startup

That is it should not be the case that you are trying to start an 8.1.6 instance using 8.1.7 server manager.

Please let me know.


Sam

12514, 00000, "TNS:listener could not resolve SERVICE_NAME given in connect descriptor"
// *Cause: The SERVICE_NAME in the CONNECT_DATA was not found in the listener's tables.
// *Action: Check to make sure that the SERVICE_NAME specified is correct.
// *Comment: This error will be returned if the database instance has not
// registered with the listener; the instance may need to be started.
$

OK, I can now log into svrmgr fine, something must have been wonky with my enviro variables. I logged out and then back in and now I can log into svrmgr.

This database is a test database that will become production once I get it working. The database was always in ArciveLog mode but the autoarchive was false (does this sound right?). I have had the instance up for a few weeks now (restarting it sometimes over the last week) so I haven't have any trouble bringing it up before.

When I log into svrmgr, I'm back to the same problem as before when I try to startup the database:
SVRMGR> startup
ORA-03113: end-of-file on communication channel

I've tried accessing it through sqlplus and I get the error

ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
SVR4 Error: 2: No such file or directory

I've tried reloading the lsnrctl as you suggested but I'm not having the lisner problem since I relogged in. I can't log into sqlplus because my database is down.

I can ping my instance fine using TNSPING. I've also tried reverting back to my original ora file before I started messing with it but that didn't help either.

Quote:

---

Originally posted by Yvonne
When I log into svrmgr, I'm back to the same problem as before when I try to startup the database:
SVRMGR> startup
ORA-03113: end-of-file on communication channel

---

When this came, have you had the log_* in the initSID.ora file commentted out. If not, could you, please just comment those archive log options on the init.ora file and try starting it.


Before starting it, make sure that you have set your ORACLE_SID= in the environment variable. Then first issue

shutdown immediate This is to make sure that the database had been shutdown.

now try to bring your system to the nomount

startup nomount

If it comes successfully, issue

alter database mount;

If it comes successful, issue

alter database open;

If this comes out successful, now your database would be in no archive log mode.


Could your try these first and let me know how did this go?

Sam

My SID variable are set properly -
ORACLE_BASE=/app/oracle
ORACLE_HOME=/app/oracle/product/8.1.7
ORACLE_SID=angus

The log_* in the init ora file are all commented out (with # right?)

# log_archive_start = true
# log_archive_dest_1 = "location=/data1/oradata/angus/arch"
# log_archive_format = arch_%t_%s.arc

Should I comment out other log_* lines like log_checkpoint_interval, log_checkpoint_timeout or log_buffer?

When I issue the shutdown immediate command in svrmgr it tells me that that it isn't connected to Oracle. If I then try the startup nomount I get the end-of-file on communication channel error.

Your environment setups are all seems to be o.k and you have commented out the right things.

Now, did you create any other test instances appart from this one? If so I would suggest that you shut them down first and then try strating this instance. There are a number of scrnatios that could result in the end-of-file-communication. So it is a matter of exploring them one by one.

IF you have patience, I'm willing...

Sam

Here is a list that you could try:


ORA-3113 when attempting to STARTUP Oracle


There are several phases involved in starting up a database. If ORA-3113
occurs during startup then abort the instance and start up using the
sequence below. If an error occurs at any step then see the related notes
below.

a. Start any required services. On error see A1
Eg: On NT start the OracleServiceSID

b. Connect as a SYSDBA user. On error see A1
Eg: svrmgrl
SVRMGR> connect / as sysdba (or connect internal)

c. Startup nomount. On error see A1
Eg:
SVRMGR> startup nomount

d. Mount the database. On error see A1 and A2
Eg:
SVRMGR> alter database mount;

e. Recover the database On error see A3
Eg:
SVRMGR> recover database

f. Open the database On error see A4
Eg:
SVRMGR> alter database open;

g. Wait 3 minutes then issue a select. On error see A4
Eg:
SVRMGR> select count(*) from DBA_OBJECTS;



A1) Errors connecting as SYSDBA / Internal OR on startup nomount

There is something fundamental wrong with the software / environment
if you cannot connect to Server Manager as a DBA user.
The steps here cover errors such as ORA-3113, ORA-12547: TNS:lost contact
or similar errors connecting to Oracle or starting the instance NOMOUNT.
Check the following items:

A1.1) If possible reboot the server disabling any automatic
startup of Oracle before you do so. This may seem drastic
but helps make sure you are working from a consistent
starting point.

A1.2) Check your environment points at the expected ORACLE_HOME
and ORACLE_SID and that TWO_TASK is not set (Unix) or
LOCAL is not set (NT registry).
Check the USER_DUMP_DEST and BACKGROUND_DUMP_DEST and default
trace directories under this environment for any user trace
files or alert log entries generated. These may help indicate
the cause of the problem.
Eg: ORA-600[SKGMINVALID] may indicate a problem with the
shared memory Unix parameters on Unix systems.
Try to show that any trace file / alert log entry you
find is truely related to the "CONNECT" command by re-issuing
the "connect" and checking for a new trace file / alert entry
at the time of the error.

A1.3) Unix only:
Some Unix platforms need LD_LIBRARY_PATH to be set
correctly to resolve any dynamically linked libraries.
As the user with the problem:

% script /tmp/ldd.out
% id
% cd $ORACLE_HOME/bin
% ldd oracle
% exit

If the 'ldd' command does not exist go to the next step below.
Check that all lines listed show a full library file. If there
are any 'not found' lines reported contact Oracle support
with the output of /tmp/ldd.out .


A1.4) Unix only:
Your 'oracle' executable may be corrupt. Relink it thus:

Log in as the 'oracle' user.
% script /tmp/relink.out
% cd $ORACLE_HOME/rdbms/lib
% mv $ORACLE_HOME/bin/oracle $ORACLE_HOME/bin/oracle.dd.mon.yy
% rm -f ./oracle
% make -f ins_rdbms.mk ioracle
% exit

Prior to Oracle7.3 the relink command was:
make -f oracle.mk ioracle

If this reports any errors Oracle support will need to see
the contents of the file /tmp/relink.out .


A1.5) Have you installed the Parallel Server Option ?
ORA-3113 can occur if you have installed the Parallel
Server Option but do NOT have a Distributed Lock Manager
installed or running correctly.

Unix:
If the Parallel Server Option was installed by accident
then it can be de-installed by relinking.
Eg:
Shut down any Oracle instances
% script /tmp/relink.out
% cd $ORACLE_HOME/rdbms/lib
# 'oracle' should not exist so delete it if it present
% rm -f oracle
% make -f ins_rdbms.mk no_parropt ioracle
% exit

NB: Do NOT deinstall the Parallel Server Option if the
database is using Parallel Server unless both nodes
are shut down otherwise database corruption could occur.

A1.6) If the error is on STARTUP NOMOUNT:

Check the init.ora file used to start the database.
This provides the configuration details used
configure the instance. To help isolate the problem
it may be useful use a very basic init.ora file
when starting the instance. If this works then
parameters can be increased / introduced one at a
time to see if there is a problem with a particular
setting.

A1.7) Check for server side trace files which may give more
indication what the underlying problem is.
See section C for details on how to check
for server trace files.


A1.8) Ensure there is free disk space in:

a. Your USER_DUMP_DEST and BACKGROUND_DUMP_DEST locations

b. Your AUDIT destination (Unix)
The default is $ORACLE_HOME/rdbms/audit

c. Your Oracle Trace directory if Oracle Trace is enabled
See


A2) Errors Mounting the database

Check all the items in A1 first.

If an error occurs when mounting the database there may be problems
with the control-files or data files, or with resources required to
open these files.

A2.1) The location of the control files are specified in the
init.ora file. Try mounting using each controlfile in
turn.
eg: "Shutdown abort",
edit the init.ora to refer to ONE of the controlfiles only,
"startup nomount",
"alter database mount"
Repeat for each controlfile to see if any controlfile works.

A2.2) It is possible to re-create the controlfiles if you know the
location of all datafiles and online logs, or to restore an old
backup controlfile. Always back up the current controlfiles before
restoring any backup copies or issuing a CREATE CONTROLFILE
command.
The steps for this are not documented here.

A2.3) Unix:
Some unix platforms have a 'truss' command (or 'tusc').
If available this can be used to help trace how far Oracle
gets before the error occurs.
Eg:
% truss -o /tmp/truss.out -f svrmgrl

Keep the file /tmp/truss.out safe - Oracle Support MAY need to see it.


A3) Errors on RECOVER DATABASE

ORA-3113 during recover database is often related to a corruption on the
database or redo stream which causes the shadow process to die. There should
be a server side trace file produced for this sort of problem.
See Section C for details on how to locate any trace files
from both USER_DUMP_DEST and BACKGROUND_DUMP_DEST.

A3.1) If the "recover database" fails fairly quickly then it
may help to collect the redo up to the point of failure as this
may help identify where the problem is.

Use the following commands just prior to the RECOVER DATABASE
command:

SVRMGR> alter session set max_dump_file_size=unlimited;
SVRMGR> alter session set events
2> '10228 trace name context forever, level 10';
SVRMGR> RECOVER DATABASE

This causes redo information to be written to the user trace
file. The last items of redo may help determine which file
has problems.

A3.2) If you do not have many datafiles in the database it may be
as quick to recover each file in turn to see if this narrows
down the problem.
Eg:
SVRMGR> select name from v$datafile;

and then for each file:

SVRMGR> RECOVER DATAFILE 'full_file_name'

If this gets to a problem file then back up the file and
use standard recovery options as if the file was lost.


A4) Errors on ALTER DATABASE OPEN

Database open performs very many operations and so it is necessary
to collect any trace information before determining the next steps.
However, the following may help isolate the problem more quickly:

A4.1) Move files out of your USER_DUMP_DEST and BACKGROUND_DUMP_DEST
directory as these steps will generate a lot of trace.

A4.2) Edit the init.ora file and add the lines:

event="10046 trace name context forever, level 12"
event="10015 trace name context forever, level 1"
event="10228 trace name context forever, level 1"

If you already have "EVENT=" lines in the init.ora file
this MUST directly below the other "Event=" lines.
These lines will trace:

SQL and BIND activity during startup
REDO applied
Information about transactional rollback required

A4.3) Startup the instance as described at the top of this section.

As soon as the error occurs REMOVE the above events from the init.ora
file and shutdown. Collect together the trace files and alert logs
as described in Section C



Sam

Btw, have you set your LD_LIBRARY_PATH in your env ?

Sam

If you're willing, I have the patience and the gratitude!!

One other instance was created during setup (angus2) but I've never started it. I have a listner for it and an entry in the tnsnames.ora file. I can't tnsping it so I know it isn't running. If I understand your note correctly, you don't want me to try to start it, correct?