Once again, the Unix> line is the syntax required to run this SQL*Plus script. Lines 5 through 9 turn off all SQL*Plus output formatting features. Line 10 opens the file that will contain the operating system command that will append the filenames to the output file created by Listing 23.4. Notice the UNIX-borne shell file suffix. Lines 11 through 14 select the external directory structure stored in the table SYS.DBA_DIRECTORIES, adding and appending the appropriate syntax for the UNIX operating system. Line 15 closes the newly created UNIX shell script. Line 16 then executes this command file, selecting all of the filenames from the appropriate directory structures and appending them to the filenames created by Listing 23.4. Line 17 cleans up the shell script after its use.

Listing 23.5. Oracle8 only—adding externally stored objects.

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

 2:

 3: rem File bfile_list.sql

 4: rem

 5: rem               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 bfile.sh

13: SELECT ‘ls ‘ || directory_path || ‘/’ || directory_name || ‘/*.* >>

cold_back.lst ‘

14: FROM sys.dba_directories

15: /

16: spool off

17: host bfile.sh

18: host rm bfile.sh

19: exit

Listing 23.6 automates the cold backup procedure by utilizing techniques of extracting information from Oracle’s data dictionary and not depending on an error-prone manually maintained list.

This code will need to be modified for the syntax of the particular backup medium and computing environment. Listing 23.6 uses standard UNIX commands to copy the previously prepared list of files required for a cold backup to a tape device.

Listing 23.6. Processing file list for cold backup.

1: Unix> filesystem_backup.sh <<EOF

2: #!/bin/sh

3:

4: tar -cvf <tape device> < cold_back.lst

5: EOF

6: Unix>

7: Unix>

---

NOTE:  
Replace the tar command for the syntax of the storage media being utilized.

---

This code can be used for UNIX raw partitions, putting one raw partition on a single tape.

Unix> rawpartition_backup.sh <<EOF

#!/bin/sh



tar for j in \`cat cold_back.lst\`

do

    echo “Put tape in tape device 0”

    pause “Hit Return to continue...”

    dd if=$j if=/dev/rmt0

done



EOF

Unix>

The following syntax (Listing 23.7) can be used to shut down and start up instances of Oracle. Cold backups must be done with the Oracle database in a “shutdown” status.

Make sure the environment variable ORACLE_SID is set to the instance of Oracle you want to shut down or start up.

Listing 23.7. Oracle shutdown/startup scripts.

 1: Unix> svrmgrl <<EOF

 2: connect internal

 3: shutdown

 4: exit

 5: EOF

 6:

 7: Unix> svrmgrl <<EOF

 8: connect internal

 9: startup

10: exit

11: EOF

The steps required to do a cold backup from an Oracle RDBMS point of view are the following:

1.  Determine the amount of backup media required by running Listing 23.3.

2.  Create a list of Oracle datafiles to be backed up, utilizing Listing 23.4.

3.  If this is an Oracle8 environment with externally stored binary files, utilize Listing 23.5 to supplement the list of data files requiring backup.

4.  Add to that list all support files that make up the Oracle instance, including init[instance].ora files and control files. It wouldn’t hurt to also include all the SQL*Net files. The following command could do all that:

ls    $ORACLE_HOME/dbs/init*.ora        \

    $ORACLE_BASE/admin/*/pfile/*.ora    \

       $ORACLE_HOME/network/admin/*.ora    \

    >> cold_back.lst

You will want to modify this command for your environment.

5.  Shut down the instance of Oracle to be backed up.

6.  Run the shell script which processes the file listing appropriately (Listing 23.6).

7.  Start up the instance of Oracle.

Automating Hot Backups

Hot backups differ from cold backups in that only one tablespace is backed up at a time, while the database is still online and available for use. IS departments that require true 24×7 availability of data will utilize this method of backup. VLDBs also utilize this method when a cold backup is not possible or feasible.

The ability to do hot backups relies on the Log Archive being turned on.

Recovery is accomplished by restoring the damaged files and initiating a recovery for that particular tablespace. During recovery, Oracle RDBMS will prompt the operator for the required archive log files.

Run the script in Listing 23.8 to create a list of files for each of the tablespaces to be backed up. For Oracle8 with externally stored binary objects, run Listing 23.9. After that, run the scripts in Listing 23.10 to create a list of the control files, initiate a log switch, and then create a backup list of all archive log files and SQL*Net files. Listings 23.11 and 23.12 show how to process this hot backup list of files, and Listing 23.13 ends the hot backup mode for the tablespace.