Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

how to set a database in archive mode

SOLVED
Go to solution
HPADM
Advisor

how to set a database in archive mode

Hi
I have a database in no archivelog mode.
How can I set it in achevelog mode?

I changed the parameter file and I tried to start the instance without opening the datadase.

I got this error:

SVRMGR> startup mount
ORA-16032: parameter LOG_ARCHIVE_DEST_1 destination string cannot be translated
ORA-07286: sksagdi: cannot obtain device information.
HP-UX Error: 2: No such file or directory


What is going wrong?
11 REPLIES
Volker Borowski
Honored Contributor

Re: how to set a database in archive mode

Goes like this in oracle:

shutdown immediate;
startup mount;
alter database archive log;
alter database open;

Archive mode has nothing to do with init.ora parameters. You should set them back to the previous values.
Make sure, that your archive-destination is valid and the oracle user has write permissions there.

Hope this helps
Volker
Steven E. Protter
Exalted Contributor

Re: how to set a database in archive mode

You need to turn on archive log mode in sqlplus, if thats what you need, reply and I'll look up the syntax.

What you might be trying to do, I'm not sure, is dual logging? Have the archive logs go to a secondary location, a different disk?

If that it the case, I can provide you an init.ora that does that correctly. Let me know, though because I have to get them from my dba.

Steve
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Printaporn_1
Esteemed Contributor

Re: how to set a database in archive mode

Hi,

Please make sure that the directory for your archive destination exist.
enjoy any little thing in my life
T G Manikandan
Honored Contributor
Solution

Re: how to set a database in archive mode

Yes,to change the noarchivelog database to archivelog which should be helpful in recovery purposes
you should

1.First shutdown the database cleanly by using

shutdown immediate;
2.Take a backup of the current setup of the database
//this is always recommended//
3.startup and mount the database.
startup mount

//do not open the database

4.change the database to archivelog mode

alter database archivelog;

5.open the database

alter database open;

YOu have various archivelog parameters.

you parameter should be like

LOG_ARCHIVE_DEST_1= 'LOCATION='

Make sure that you create the directories and provide permissions to Oracle user and dba group.

Thanks
T G Manikandan
Honored Contributor

Re: how to set a database in archive mode

Yogeeraj_1
Honored Contributor

Re: how to set a database in archive mode

Hi,

the explanation give by my predecessors are perfectly correct and you should not have any problems enabling ARCHIVE LOG mode.

The missing part is that you should do a few tests to ensure that everything is working fine now.

a. check if your configuration is OK
archive log list;

b. Make sure that the size of your redologs have been sized correctly - not too big not too small
select * from v$log
check the column BYTES

c. Check if the log switches are working fine. (do it three times and after each time run the SQL statement below)
alter system switch logfile;
Check the STATUS: ACTIVE/CURRENT

d. Make sure that no logs are STALE
select * from V$log;
Check the STATUS: should in no case be STALE!
If STALE, you should switch the logs a few times...


Below an example from my site:
=======================================================================
$ sqlplus internal

SQL*Plus: Release 8.1.7.0.0 - Production on Fri Dec 20 08:37:59 2002

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


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/admin/pfs/arch
Oldest online log sequence 125267
Next log sequence to archive 125269
Current log sequence 125269
SQL>
SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- ----------
4 1 125268 26214400 1 YES INACTIVE
69071558 20/12/2002

5 1 125269 26214400 1 NO CURRENT
69090371 20/12/2002

6 1 125267 26214400 1 YES INACTIVE
69035472 19/12/2002


SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- ----------
4 1 125268 26214400 1 YES INACTIVE
69071558 20/12/2002

5 1 125269 26214400 1 YES ACTIVE
69090371 20/12/2002

6 1 125270 26214400 1 NO CURRENT
69094772 20/12/2002


SQL> !ll -rtl /u01/app/oracle/admin/pfs/arch |tail -1
-rw-r----- 1 ora817 oinstall 13686784 Dec 20 08:40 arch_1_125269.arc

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/admin/pfs/arch
Oldest online log sequence 125268
Next log sequence to archive 125270
Current log sequence 125270
SQL>

=======================================================================

Hope this helps!

Best Regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Ravi_8
Honored Contributor

Re: how to set a database in archive mode

Hi,

dbshut
startup nomount;
alter database archivelog;
alter database open;
sql> select log_mode from v$database;
should return "archivelog".
never give up
Wodisch
Honored Contributor

Re: how to set a database in archive mode

Hi,

in addition to changing the DB to "archivelog" mode (as shown by the others), I *do* recommend checking your "init*ora", as you'll need some parameters in there set to the correct values:
- you'll need to start the "archiver"
- you'll need to configure the archive *destination*, i.e. the directory
- you'll need to configure the the log-format, i.e. the archivelogfile's names

And now for the VERY important steps: BEFORE you change to archivelog mode, create a crontab entry to
- save the *old* archivelogfiles continuously
- delete the very old archivelogfiles (say, older than 3 days)

Without that your instance will FREEZE sooner or later (and hence your application)!

FWIW,
Wodisch
Yogeeraj_1
Honored Contributor

Re: how to set a database in archive mode

Hi again,

A more structured way of backing up your archived redo logs would be to use Oracle RMAN with a catalog.

This is how i do it (backup to disk and cleanup of archived redologs):
=====================================================================
#!/bin/sh
#Script Name: /prod/global/admin/bckup/rman_al.sh
#By: YD-20/12/2002

messagelog="/backup/rman/logfiles/rman_al-`date +%d%m%y-%H%M`"
subject="RMAN-Backup Log (Archive Redologs) on `date` completed"
emailadd1="youremail@servername.mu"
catlg="rman_dbslx1/rman_dbslx1@dev"


rman target internal rcvcat $catlg msglog
$messagelog </dev/null
run {
allocate channel fs1 type disk format='/backup/rman/al/al1_t%t_s%s_p%p';
set limit channel fs1 kbytes=100000;
backup filesperset 10 archivelog all delete input;
release channel fs1;
}
exit;
EOF
#
/usr/bin/mailx -s "$subject" $emailadd1 < $messagelog
#
===================================================================

Then schedule it using cron (i use root for all my crons, can also be otherwise):
Also, note that the frequency of backup will depend on the number of archived redologs generated within a determined time interval.
===================================================================
#*******************************************************************************
# min|hour |day |month|day |script
# | |of mo| |of wk|
#----|-----|-----|-----|-----|--------------------------------------------------
#*******************************************************************************
00 22 * * * echo "/prod/global/admin/bckup/rman_al.sh" | su - ora817 1>/backup/rman/logfiles/output-rman_al.crn 2>/backup/rman/logfiles/error-rman_al.crn

===================================================================

Also, schedule periodic cleanup of the rman disk backups and of course you daily backup should be copying them to storage media that can be stored off-site.

On top of all this, don't forget that archived logs work in conjunction with full backups. hence, you also need to do full backups of your database. For this also, it is recommended to user Oracle RMAN. Please read the excellent "Oracle8i Backup and Recovery Guide" to understand the possibilities concerning Backup and Recovery

Hope this helps!

Best Regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Steven E. Protter
Exalted Contributor

Re: how to set a database in archive mode

You need exclusive access to the database, even if its in cluster mode to turn on archive log mode.

here is how to do it from either svrmgrl or sqlplus

svrmgrl
connect internal
startup mount cc1;
alter database archivelog;
archive log start;
alter database open;

Its the same in sqlplus except your command line is sqlplus internal

Steve
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Steven E. Protter
Exalted Contributor

Re: how to set a database in archive mode

Have you heard of tnsping?

Its an oracle utility that lets you test your tnsnames.ora sqlnet.ora and listener.ora configuration and validate you have proper connectivity and that the daemons(listener, etc) are configured properly.

Steve.
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com