AutoArchival?

I found this in my alert_ora1.log

Tue May 1 13:09:19 2001
ORACLE Instance ora1 - Can not allocate log, archival required
Tue May 1 13:09:19 2001
ARCH: Connecting to console port...
Thread 1 cannot allocate new log, sequence 584
All online logs needed archiving
Current log# 1 seq# 583 mem# 0: /data1/oradata/ora1/redo01.log

My guess is that I need to archive my redo log files. How do I go about this? Should I turn my AutoArchival to true? If so, how do I do this?

Edit the initSID.ora to
"log_archive_start = true", if its not already there.

This change will be in effect after restarting the instance.

To avoid restart, do the following,

Alter system archive log start ; --will be persistent until restarted

1) force a new archive logfile
ALTER SYSTEM SWITCH LOGFILE;

2) connect internal , to see all desciption for archive

archive log list;

I found these three commented lines in my initSID.ora file and uncommented them

##### For archiving if enabled #####
log_archive_start = true
log_archive_dest = "location=/data1/oradata/angus/arch"
log_archive_format = arch_%t_%s.arc

After I uncommented them, I issued the command SHUTDOWN IMMEDIATE in sever manager. When I tried to STARTUP I received the error

ORA-16032: parameter LOG_ARCHIVE_DEST destination string cannot be translated
ORA-07286: sksagdi: cannot obtain device information.
SVR4 Error: 2: No such file or directory

So I removed the location=/data1/... so that it just has the directory path in quotes. Now when I issued the STARTUP command I get the error message

ORA-03113: end-of-file on communication channel

I even recommented the archive lines in the ora file but still get this error. I tried to find a trace file to look at but there isn't one. Here is what I found in the alert log for today but it seems to be normal to me . . . Any ideas?


Wed May 2 08:55:01 2001
Restarting dead background process EMN0
EMN0 started with pid=15
Wed May 2 08:55:07 2001
Shutting down instance (immediate)
License high water mark = 8
Wed May 2 08:55:12 2001
ALTER DATABASE CLOSE NORMAL
Wed May 2 08:55:12 2001
SMON: disabling tx recovery
SMON: disabling cache recovery
Wed May 2 08:55:12 2001
Thread 1 closed at log sequence 584
Wed May 2 08:55:12 2001
Completed: ALTER DATABASE CLOSE NORMAL
Wed May 2 08:55:12 2001
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT
archiving is disabled
Wed May 2 08:59:24 2001
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Wed May 2 09:00:37 2001
Starting ORACLE instance (normal)
Wed May 2 09:06:36 2001
Starting ORACLE instance (normal)
Wed May 2 09:08:14 2001
Starting ORACLE instance (normal)

Hi, 2nd May 2001 21:14 hrs chennai

Did you do this in the mount state

alter database archivelog; This does the mode permanent to DB.

Then check up by typing as follows

svrmgr>log archive list

See whether it shows as follows

DATABASE LOG MODE as Acrhive mode and
AUTOMATIC archival as Enabled

Shutdown the DB in normal or immediate

Take a full closed DB bck

Startup and open the DB.

All other 3 parameters is what you have done is ok in init.ora.

I understand the path should be wrong in your case.Dont recomment do the steps as described above i am sure you will get across.


Cheers

Padmam



after this

Yvonne, The cause of the error is at

##### For archiving if enabled #####
log_archive_start = true
log_archive_dest = /data1/oradata/angus/arch

Then, start the instance issue,



SQL>alter system archive log start;

SQL> select * from v$instance;

Here you should see the archiving had been set to yes. Then shutdown the instance and restart the instance and issue

archive log list and see that you get a list. and also do the above select query and confirm that it is in archive mode.


Good luck,
Sam

Thanks very much for your help, I believe I understand now.

I've changed my initSID.ora file to reflect your suggestions. However, I can't seem to get passed the ORA-03113: end-of-file on communication channel error.

Any ideas?

First check whether the path you had specified is a valid one. Then try including the path within double quotes.

BTW, what is your OS and Oracle version.

Sam

The path does exist and it's empty. The oracle owner has write access to the directory. I am using Solaris 8 and Oracle 8i. I changed the ora file to include double quotes around the Archive destination directory. But when I try to issue a command in SVRMGR I get the error:

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

I haven't changed my listener.ora nor my tnsnames.ora. I started and stop the listeners without problem.

Lets do one by one.

1. I need to know your database version.

2. Comment out the archiving option and start your instance. Does it come up without any problem.

3. Now add the following

# Uncommenting the lines below will cause automatic archiving if archiving has
# been enabled using ALTER DATABASE ARCHIVELOG.
log_archive_start = true
log_archive_dest = oracle_base_path/admin/SID/arch
log_archive_format = arch_%t_%s.arc

start the instance,

If it comes up o.k, then issue

SVRMGR> alter system archive log start;


then issue

SVRMGR> archive log list


IF you have any problem, pl. let me know when it occoured while doing what?

Sam

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?

Yes the LD_LIBRARY_PATH is set

LD_LIBRARY_PATH=/usr/openwin/lib:/usr/dt/lib:/usr/java/lib:/app/oracle/product/8.1.7/lib:/app/oracle/product/8.1.7/jdbc/lib

That is correct. Do a ps -ef|grep your_instance2 and confirm that it had not been started. If it was running, then set your ORACLE_SID to it and connect through the server manager and issue a shutdown immediate. Then try to start the other instance. If that doesn't work,then, folloing the steps mention on A1, can you afford to bounce your server, to startup with?


Before bouncing the server, check /var/opt/oracle/oratab and make sure that the auto start option on the file for all the instance had been set to "N". Then bounce your server.

Then make sure that the

ORACLE_BASE=
ORACLE_HOME=$ORACLE_BASE/product/8.1.7
LD_LIBRARY_PATH=
PATH=$ORACLE_HOME/bin
ORACLE_SID=


Now log into server manager and try to issue the startup mount command. IF it were to come through, then proceed with the others. If not A1.3 or A1.4 and relink the oracle and try starting it in the nomount stage.

Please let me know of the results before doing the relinking. I'm in constant look out for your postings.

Sam



[Edited by sambavan on 05-02-2001 at 05:44 PM]

can you verify the envoronment variables ORACLE_SID, ORACLE_HOME, LD_LIBRARY_PATH are all set and are having the value you expected.

If not set them. The easiest way to do that is to invoke the oracle_env.sh if one already exists. (usually under "oracle" user's home directory)

now,

% svrmgrl

svrmgrl> connect / as sysdba ;
(or connect internal)

svrmgrl > startup ;

Let us know of any problem u have.

yaaay!!

Rebooting the server helped with starting the instance. In all of our conversations I'm not sure what to start next. Should I try to uncomment the log lines in the ora file, shutdown the instance and then restart or try what Sam suggested:
- shutdown instance
- startup nomount
- alter database mount
- alter database open

Yvonne

uncomment the needed lines

svrmgr> shutdown immediate ;

##u can alter those parameter without even shutting down the instance. Use the "ALter System dada.."

svrmgr> startup ;

O.K that was a good news. Now as gsprince suggested shutdown your instance as immediate. Forget about the other linking and all for the time being.

Uncomment the archive log options in your initSID.ora, then try to start the instance on the mount stage.


startup nomount
alter database mount;
alter database open;
alter system archive log start;
shutdown immediate;
startup
archive log list;
select * from v$instance;

This should put and confirm your instance to be in the archive log mode.

Let us know if you have any problem.

Good luck,
Sam

[Edited by sambavan on 05-02-2001 at 08:19 PM]

Well I uncommented the log lines in the initSID.ora file and stopped and restarted the instance. It came up great!

When will I expect it to begin the archiving. Is there somewhere where a time is set to begin archiving?

Thank you so much for your help Sam and gsprince. I really appreciate it.

Time for a beer!

you can see the logs that are being archive by

sqlplus sys

SQL> archive log list;

This would show you the log sequence number that is being archived.

You can also go to the archive log destination directory and do an ls and check it.


Have fun :D
Sam