Changing the archivelog destination online.

Couple of days back received a high priority ticket developers saying not able to connect to database

hard working people they work on saturdays and sundays and make us also do the hard work :(

they are getting an error ORA-00257: archiver error. Connect internal only, until freed.

Found the following error in the alertlog file:

Sat Apr 11 05:20:00 2009
ARC0: Archiving not possible: No primary destinations
ARC0: Failed to archive thread 1 sequence 2029 (4)

When I checked for details from the database I can see the following:

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- --------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 devdb2
sun-boxdb1
10.2.0.2.0 10-APR-09 OPEN NO 1 FAILED ARCHIVE LOG
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO


SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /mounts/devdb2_arch/devdb2
Oldest online log sequence 2029
Next log sequence to archive 2029
Current log sequence 2031

Thought may be the archive destination mount is full but shocked coz I cannot see the mount point.
Then I realized the mistake that I not 'I' "they" made the earlier day when we moved the database to the new Sun box.

Our Storage guys forgot to mount the archive Mount point.
I actually prefer seperate mount points for Data,Arch and Temp :)

Dont ask me why I did not checked when I was making the database up..its a friday evening guys...
But did not know how the database was up without the archive mount point destination specified.

So for immediate resolution had to change the archive destination "the day is SATURDAY and storage guys available on saturday??? no way!!! don't look for them"

Did the following:

SQL> alter system set log_archive_dest_1='LOCATION=/mounts/devdb2_temp/devdb2/arch/';

System altered.

SQL> alter system archive log all;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /mounts/devdb2_temp/devdb2/arch/
Oldest online log sequence 2031
Next log sequence to archive 2033
Current log sequence 2033
SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- --------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 devdb2
sun-boxdb1
10.2.0.2.0 10-APR-09 OPEN NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO


SQL>

And you can see that all the errors are cleared in the alertlog file:

Sat Apr 11 05:20:03 2009
ARC1: Archiving not possible: No primary destinations
ARC1: Failed to archive thread 1 sequence 2029 (4)
Sat Apr 11 05:20:06 2009
ALTER SYSTEM SET log_archive_dest_1='LOCATION=/mounts/devdb2_temp/devdb2/arch/' SCOPE=BOTH;
Sat Apr 11 05:20:07 2009
Archiver process freed from errors. No longer stopped
Sat Apr 11 05:20:09 2009
Thread 1 advanced to log sequence 2032
Current log# 3 seq# 2032 mem# 0: /mounts/devdb2_data/devdb2/redo03.log
Sat Apr 11 05:20:29 2009
Thread 1 advanced to log sequence 2033
Current log# 1 seq# 2033 mem# 0: /mounts/devdb2_data/devdb2/redo01.log
~


Happies endingss