Bullet proof your Oracle database

Bulletproofing Oracle database:
Yes, you really can bulletproof your database so that it is always up and available and you never lose any data from almost any disaster scenario - for a price, whether in dollars, storage, servers, and/or bandwidth. You can do things such as running parallel servers in separate locations with standby databases for quick switchovers using Oracle's Data Guard package, along with using other options that Oracle and others provide. For the rest of us with or without large budgets and staff, there are things that we, too, can do to lessen the possibility of data loss, minimize downtime and make our databases more bulletproof, such as those described below:

1. Enable archivelog mode (to reapply the changes during recovery; which is required by most of the disaster recovery scenarios detailed below).
2. Separate the archive logs from the redo logs (allocating them to separate drives; likewise for the following items).
3. Separate the redo logs and archive logs from the datafiles.
4. Multiplex (mirror) the redo log groups and members.
5. Multiplex (mirror) the control file.
6. Multiplex (mirror) the archive log files.

Oracle Backup and Recovery 7-9i

Backups
The DBA's primary job is to make sure that the data is available and accessable by the users during those times that they need it, which means that a complete and well-tested backup and recovery procedure is in place and functioning. This section covers what files need to be included in that backup, the types of backups (cold backups and hot backups), and other processing that you could perform during the backup for proactive maintenance.

What To Back Up
The following types of files making up the database and providing recovery capabilities should be backed up during one backup cycle, such as all of them during a cold backup, or all or subsets of them during a hot backup cycle, assuming the entire set is eventually backed up during that cycle.

• Datafiles (for all tablespaces)
• Control Files (binary and textual versions)
• Redo Log Files (cold backups only, not hot backups)
• Archive Log Files (archived redo logs, if archivelog mode is enabled)
• Parameter Files (init.ora; like $ORACLE_HOME/dbs/initPROD.ora)
• Password Files (like $ORACLE_HOME/dbs/orapwdPROD, if used)

The most basic way to get the names of the datafiles, control files, and redo log files through SQL is shown below, which can be used in both the startup mount state and the database open state:

SQL> select name from v$datafile;
SQL> select name from v$tempfile;
SQL> select name from v$controlfile;
SQL> select member from v$logfile;

You can also get the tablespace name with the datafile and temp file names using:
SQL> select tablespace_name,file_name from dba_data_files order by tablespace_name;
SQL> select tablespace_name,file_name from dba_temp_files;

The list of archive log files which are currently on-line cannot be found through SQL. However, you can get the directory containing those archive logs, the format of the file names, and whether or not archiving was turned on in the init.ora parameter file using:
SQL> select name,value from v$parameter where name in ('log_archive_dest',
'log_archive_format','log_archive_start');

These archiving parameters can also be found in an sqldba session by issuing either of the following commands (but the first one might truncate the dest directory name):
SQL> show parameter archive
SQL> archive log list

To get the list of archive log files for the last 5 days (the date is when Oracle started writing to that redo log, not when it was copied to the archivelogs directory), which may or may not still be on-line, you can query the v$archived_log table in Oracle 8.x and above:
SQL> select name from v$archived_log
where trunc(completion_time) >= trunc(sysdate)-5;

Or, you can query the v$log_history table in Oracle 7.x:
SQL> select archive_name from v$log_history
where trunc(to_date(substr(time,1,8),'MM/DD/YY')) >=
trunc(sysdate)-5;

Note:- please send your commments to improve the articles.