Deciding Which Scheme Is Best

Which backup scheme or plan is best is dictated by business needs. Backup plans should be tested to make sure that they are free of errors, are able to recover from various media or data loss scenarios, and meet the system availability time frames.

A backup set is the required files for a particular backup plan. Some backup sets build on other backup sets. For example, the archive log files would supplement the previous hot or cold backup sets. These archive log files become a backup set in themselves.

Backups should definitely be grandfathered; that is, several generations of backups need to be kept in existence for off-site storage and as a recovery mechanism in case the backup medium itself proves unusable during the recovery process. Depending on the nature of the information, some shops make copies of backup sets, keeping one on-site for a quick response to a recovery need and storing the second copy in a vault off-site.

The more critical the information, the more redundant the backup process should be.

---

TIP:  
It is not a bad idea to make duplicate copies of the archive log file backups and keep them for an extended period of time.

---

Automating Backup Schemes

I use SQL*Plus to do most of the work because it is an easy and flexible environment that can extract information stored in the database and format it properly for these scripts purposes. SQL*Plus scripts are easily understood and maintained.

Automating Cold Backups

See Listing 23.3 for a SQL*Plus script to determine the physical files and space required for backup. This information is useful for determining the amount of removable media to have available, particularly if the media needs to be formatted before it can be utilized. This script is executed through the SQL*Plus interface and utilizes information stored in the data dictionary table SYS.DBA_DATA_FILES. This is a simple SQL*Plus report that will identify the Oracle database files and their total size in bytes.

The Unix> line in Listing 23.3 is the syntax required to run the SQL*Plus script. Lines 5 through 8 turn off the SQL*Plus display output. Lines 9 through 14 apply simple SQL*Plus headings and column formatting. Line 15 opens an operating system file to store the results of the query. Lines 16 through 19 are the SQL query that retrieves the Tablespace information. Line 20 closes the operating system file.

Listing 23.3. Determining tablespace sizes script.

 1: Unix>sqlplus system/<password>  @tablespace_size.sql

 2:

 3:

 4: rem File:  “tablespace_size.sql”

 5: rem

 6: rem Tablespace file and size report by Dan Hotka 2/15/1998

 7: rem

 8: set feedback off

 9: set verify off

10: set termout off

11: set echo off

12: ttitle ‘Oracle Tablespace Physical Files’

13: column tablespace_name format a20 heading ‘Tablespace’

14: column file_name format a40 heading ‘File Name’

15: column bytes format 999,999,999 heading ‘File Size’

16: compute sum of bytes on tablespace_name

17: compute sum of bytes on report

18: break on tablespace_name skip 2

19: break on report skip 2

20: spool tablespace.out

21: SELECT tablespace_name, file_name, bytes

22: FROM sys.dba_data_files

23: ORDER BY tablespace_name

24: /

25: spool off

26: exit

If the cold backup requires 12GB to be backed up to 8mm tape at 5GB per tape, three tapes are required to be formatted. With compression (hardware or software) the last tape might not be needed, but it’s better to format extra than have to prepare more tapes with the database down.

Listing 23.4 shows how to create a list of files that the backup medium can act on. The Unix> line shows the syntax required to run the SQL*Plus script. Lines 5 through 9 turn off all SQL*Plus output formatting features. The output from this script will simply be a list of the Oracle database files currently being utilized by the Oracle RDBMS environment. Line 10 creates a file called cold_back.lst. Lines 11 through 20 is the SQL that retrieves the various data, control, and log filenames. Line 20 closes the cold_back.lst file.

Listing 23.4. Gathering of data files.

 1: Unix>sqlplus system/<password>  @file_list.sql

 2:

 3: rem File “file_list.sql”

 4: rem

 5: rem Tablespace file listing script by Dan Hotka 2/15/1998

 6: rem

 7: set feedback off

 8: set verify off

 9: set termout off

10: set echo off

11: set head off

12: spool cold_back.lst

13: SELECT name

14: FROM v$datafile

15: UNION

16: SELECT name

17: FROM v$controlfile

18: UNION

19: SELECT member

20: FROM v$logfile

21: /

22: spool off

23: exit

The next script in Listing 23.5 is only necessary in Oracle8 environments that store binary objects (LOBS) outside the database environment.

Listing 23.5 demonstrates how versatile SQL*Plus can be in building and executing an operating system command file. This script formats the output necessary to locate and append the externally stored binary files to the file, cold_back.lst, created by Listing 23.4.