Showing results for 
Search instead for 
Did you mean: 

Oracle DB offline redologs aka archive mode

Go to solution
Ralph Grothe
Honored Contributor

Oracle DB offline redologs aka archive mode


although this is an HP-UX forum I know that among you are quite a few sysadmins who are also skilled Oracle DBAs.
Unfortunately I've never had any Oracle training nor have been exposed to any considerable Oracle db administration.
(I think I will have to install an Oracle db on my Linux box, to have something to play with)
Despite this severe lack of DBA knowledge I have to maintain backup scripts for tablespaces, control files and archived redo logs.
First of all can you tell me the name of a table or view of the data dictionary that I could query to find out, wether a db is running in archive mode?
I know I could simply grep for the archiver Oracle processes like

# ps -fu oracle|grep ora_arch|grep -v grep
oracle 3370 1 0 Jul 1 ? 19:12 ora_arch_B00
oracle 17614 1 0 Jul 13 ? 0:06 ora_arch_B13

And I also could look up the settings in the instances' initialization files, but there must exist a table/view.

Then I would like to know if it is possible (as for instance like with fetchmail) to set the initialization parameter LOG_ARCHIVE_DEST to a script that initiates the backing up to our ADSM server instead of to a filesystem destination?


LOG_ARCHIVE_DEST = "| /path/to/my/backup_script"

Is there a table/view in the dictionary to query the settings of the archive related parameters?

With regard to the online backup of the tablespaces, I would like to know if a query like

SQL> select tablespace_name,status from dba_tablespaces where tablespace_name = 'SYSTEM';

------------------------------ ---------

would indicate if the tablespace were in backup mode.
I want to test this because I read somewhere that it is vital to assure that the tablespace is ALTERed back to "END BACKUP" mode, for otherwise the db could suffer corruption.
I would like to catch this in a signal handler.

Madness, thy name is system administration
T G Manikandan
Honored Contributor

Re: Oracle DB offline redologs aka archive mode

you can query

svrmgrl>select log_mode from v$database
to find the archive or noarchivelog mode.

svrmgrl>archive log list

should show you the destination and the other parameters

svrmgrl>select * from v$backup

to find the status of the backup

Thierry Poels_1
Honored Contributor

Re: Oracle DB offline redologs aka archive mode


1. Select log_mode from v$database;
will show you if the database is in archive log mode or not.

2. I pass on this one. Archive logs are important : I leave them on the system until they are backed up at least twice.

3. Select * from v$parameter where num between 174 and 193;
should show all parameters related to archiving.

4. Select * from v$backup;
will show you which datafiles are in backup mode (link this with dba_data_files).
The status in dba_tablespace will remain "ONLINE" whilst tablespace is in backup mode!

Oracle DBA who is also a skilled HP-UX Admin ;-))
All unix flavours are exactly the same . . . . . . . . . . for end users anyway.
Bill Thorsteinson
Honored Contributor

Re: Oracle DB offline redologs aka archive mode

ou can use 'ps' to check whether archiving is running by check
for processes named arch${ORACLE_SID}. If they aren't running,
archiving is disabled.

Your destination needs to be a directory or devices where Oracle
can write the files. It can not be a script.

You can write a script to compress or delete archived redo logs
after a period of time. I keep a weeks worth of redo online,
which may be excessive as I also have the latest backup online as well.
My backups are compressed.

I use a 'find .. -exec rm {} \;' command in a crontab to clean the '
old archive logs. It would be better to use xargs rather than exec.

I would recommend you look at Unix Backup and Recovery from O'Rielly,
which has a good chapter on Oracle Backup and recovery. I use a
patched version of provided on the cd.

The patch to is attaced.