Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
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.