topic how to set a database in archive mode in Operating System - HP-UX

how to set a database in archive mode

HPADM — Thu, 19 Dec 2002 16:39:36 GMT

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?

Re: how to set a database in archive mode

Volker Borowski — Thu, 19 Dec 2002 17:41:30 GMT

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

Re: how to set a database in archive mode

Steven E. Protter — Thu, 19 Dec 2002 17:53:14 GMT

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

Re: how to set a database in archive mode

Printaporn_1 — Fri, 20 Dec 2002 02:41:32 GMT

Hi,

Please make sure that the directory for your archive destination exist.

Re: how to set a database in archive mode

T G Manikandan — Fri, 20 Dec 2002 03:22:52 GMT

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

Re: how to set a database in archive mode

T G Manikandan — Fri, 20 Dec 2002 03:28:16 GMT

check this link for the same.

http://www.vi.unizh.ch/oracle8i_816/onlinedocu/doc/server.816/a76956/archredo.htm#4553

Re: how to set a database in archive mode

Yogeeraj_1 — Fri, 20 Dec 2002 04:45:43 GMT

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

Re: how to set a database in archive mode

Ravi_8 — Fri, 20 Dec 2002 05:52:48 GMT

Hi,

dbshut
startup nomount;
alter database archivelog;
alter database open;
sql> select log_mode from v$database;
should return "archivelog".

Re: how to set a database in archive mode

Wodisch — Fri, 20 Dec 2002 09:53:21 GMT

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

Re: how to set a database in archive mode

Yogeeraj_1 — Fri, 20 Dec 2002 10:50:18 GMT

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

Re: how to set a database in archive mode

Steven E. Protter — Fri, 20 Dec 2002 14:04:12 GMT

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

Re: how to set a database in archive mode

Steven E. Protter — Fri, 20 Dec 2002 15:31:28 GMT

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.