Can not allocate log, archival required

Printable View

03-12-2003, 12:51 AM
hrishy

Can not allocate log, archival required

Hi

One of my database runs in no archive log mode..but in the alert log i am seeing teh following message

ARCH: Connecting to console port...
Thread 1 cannot allocate new log, sequence 8
All online logs needed archiving

can anybody explain why this thing is goin no ??

regards
Hrishy
03-12-2003, 01:09 AM
hrishy

Hi

I noticed some starnge things too..

when i say

SVRMGR> archive log list
Database log mode Archive Mode
Automatic archival Disabled
Archive destination /apps/home/ora817/dbs/arch
Oldest online log sequence 5
Next log sequence to archive 8
Current log sequence 8

but in the parameter file it is somethin like this

# Uncommenting the lines below will cause automatic archiving if archiving has
# been enabled using ALTER DATABASE ARCHIVELOG.
# log_archive_start = true
# log_archive_dest_1 = "location=/apps/home/admin/tixh/arch"
# log_archive_format = arch_%t_%s.arc

regards
Hrishy
03-12-2003, 01:14 AM
SANJAY_G

Hrishy,
Are you sure this database is in NOARCHIVELOG mode? I believe it is in ARCHIVELOG.
03-12-2003, 01:15 AM
hrishy

Hi Sanjay

Please see my above paraneter file and teh archive log list they surely tell me different stories..whats goin on ?

regards
Hrishy
03-12-2003, 01:19 AM
SANJAY_G

It is running in ARCHIVELOG mode. You will have to set it manually to NOARCHIVELOG.
03-12-2003, 01:32 AM
hrishy

Hi Sanjay

Thanks..well i must confess..just todau i learnt something new ..i was under the impression that modes of operation of the database was mentioned in the init.ora file..whereas switching modes has nouthing to do with init.ora...

i can switch modes like this

alter databse archivelog

alter database noarchivelog..

and teh archive log files are being generated at $ORACLE_HOME/dbs directory..even though this directory is not mentioned in the init.ora file

regards
Hrishy
03-12-2003, 01:33 AM
ggnanaraj

Quote:

Originally posted by hrishy
Hi
SVRMGR> archive log list
Database log mode Archive Mode
Automatic archival Disabled
Archive destination /apps/home/ora817/dbs/arch
Oldest online log sequence 5
Next log sequence to archive 8
Current log sequence 8

As automatic archiving is disabled you have to manually archive to archive log destination.

You must also check the parameter file that it is reading when starting the instance. You could also specify the parameter file to be used, to avoid doubts on which is being used.

HTH.
03-12-2003, 01:46 AM
hrishy

Hi

Is there any view through which i can check the location and the name of parameter file did my database use when it started up ?soemthing like show parameter paranmeter file

regards
Hrishy
03-12-2003, 01:48 AM
SANJAY_G

Quote:

Originally posted by hrishy
and teh archive log files are being generated at $ORACLE_HOME/dbs directory..even though this directory is not mentioned in the init.ora file

regards
Hrishy

I guess that is the default archive directory for *nix
03-12-2003, 02:57 AM
alapps

$ORACLE_HOME/dbs is the location that Oracle will use to retrieve the pfile during startup unless otherwise explicitly specified
(i.e. startup pfile=/path1/path..n/mypfile.ora)

As for the current parameters in effect for this startup of the database, refer to the v$parameter view.
03-12-2003, 03:00 AM
ggnanaraj

Quote:

Originally posted by hrishy
Is there any view through which i can check the location and the name of parameter file did my database use when it started up ?soemthing like show parameter paranmeter file

If under OFA, then the default directory for the parameter file is $ORACLE_HOME/admin/$ORACLE_SID/pfile.

If you have created the service manually (not through OUI) then the location depends on the 'oradim' command you used during manual database creation.

On windows, you can get the location used from the registry. (ORA_(SID)_PFILE).

HTH.
03-12-2003, 03:07 AM
alapps

Check the alert.log. It will contain a listing of non-default System parameters that were in effect when the database was started.
03-12-2003, 03:48 AM
jmodic

Quote:

Originally posted by hrishy
Is there any view through which i can check the location and the name of parameter file did my database use when it started up ?

No, no such thing untill 9i (with spfile). Once started, there is no way to tell which initialistion parameter file was used during database startup.
03-12-2003, 04:24 AM
hrishy

Hi Jurij

Thanks for clarifing teh doubt..

regards
Hrishy
03-13-2003, 02:06 AM
ggnanaraj

Quote:

Originally posted by hrishy
Is there any view through which i can check the location and the name of parameter file did my database use when it started up ?soemthing like show parameter paranmeter file

In 9i, we can use the following, but not in 8i or below:

Quote:

TST: SYS> show parameter ifile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
ifile file D:\oracle\admin\tst\pfile\init
.ora
TST: SYS>

HTH.
03-13-2003, 02:37 AM
shailendra

SVRMGR> archive log list
Database log mode Archive Mode
Automatic archival Disabled
Archive destination /apps/home/ora817/dbs/arch
Oldest online log sequence 5
Next log sequence to archive 8
Current log sequence 8
but in the parameter file it is somethin like this
# Uncommenting the lines below will cause automatic archiving if archiving has
# been enabled using ALTER DATABASE ARCHIVELOG.
# log_archive_start = true
# log_archive_dest_1 = "location=/apps/home/admin/tixh/arch"
# log_archive_format = arch_%t_%s.arc

YOU HAVE STARTED THE DATABASE IN ARCHIVE LOG MOD BUT YOU FORGOT TO CHANGE THE PARAMETER IN THE INIT FILE SO IF YOU WANT TO RUN YOUR DATABASE IN ARCHIVE LOG MODE THE UNCOMMENT THE ABOVE PARAMETER OR IF YOU WANT TO RUN IN NOARCHIVELOGMODE THEN GO TO MOUNT STAGE AND EXECUTE "ALTER DATABASE NOARCHIVELOG" AND IF YOU WANT TO DO IT MANULLAY THEN DEFINE THE LOCATION IN THE PARAMETER FILE OTHERWISE IT WILL STORE IN THE LOCAL DESTINATION AS WELL AS YOU WILL HAVE TO EXECUTE THE COMMAND MANUALLY.

SHAILENDRA
03-13-2003, 02:49 AM
SANJAY_G

Quote:

Originally posted by ggnanaraj
In 9i, we can use the following, but not in 8i or below:
HTH.

No. ifile is pointer to additional parameter file, mention in parameter file. It is available before 9i as well BUT it doesn't tell you the path of actual parameter file. As jmodic said there is no way we can see the parameter file used for startup, at least till 9.2
03-13-2003, 02:52 AM
SANJAY_G

Quote:

Originally posted by shailendra
YOU HAVE STARTED THE DATABASE IN ARCHIVE LOG MOD BUT YOU FORGOT TO CHANGE THE PARAMETER IN THE INIT FILE SO IF YOU WANT TO RUN YOUR DATABASE IN ARCHIVE LOG MODE THE UNCOMMENT THE ABOVE PARAMETER OR IF YOU WANT TO RUN IN NOARCHIVELOGMODE THEN GO TO MOUNT STAGE AND EXECUTE "ALTER DATABASE NOARCHIVELOG" AND IF YOU WANT TO DO IT MANULLAY THEN DEFINE THE LOCATION IN THE PARAMETER FILE OTHERWISE IT WILL STORE IN THE LOCAL DESTINATION AS WELL AS YOU WILL HAVE TO EXECUTE THE COMMAND MANUALLY.

SHAILENDRA

Shailendra,
I think Hrishy already mentioned what he missed.
BTW Why all CAPS ??
03-13-2003, 03:06 AM
pando

ifile and pfile are quite different you know ggnanaraj :D