Oracle DB offline redologs aka archive mode

[Ralph Grothe]

Hello,

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?

e.g.

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';

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE

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.


Regards
Ralph

[T G Manikandan · Accepted solution]

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

thanks

[Thierry Poels_1]

Hi,

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!

regards,
Thierry.
Oracle DBA who is also a skilled HP-UX Admin ;-))

[Bill Thorsteinson]

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 oraback.sh provided on the cd.

The patch to oraback.sh is attaced.