The Unix> line in Listing 23.8 is the command line used to put the tablespace in backup mode. The data is still available but all updates are written to the log files. When the end backup command is given (see Listing 23.12), these updates are then applied back to the tablespace. Line 5 of the script takes the input variable from the command line Unix> and assigns it a variable name. This makes the scripts more readable. Once again, lines 6 through 10 turn off all of the SQL*Plus formatting commands. Line 11 opens the output file hot_back.lst, a file that will contain names of all files to be backed up. Lines 12 through 15 is the actual SQL query that will extract the database filenames assigned to the tablespace being backed up.

Listing 23.8. Creating list of tablespace files for hot backup.

 1: Unix> svrmgrl <<EOF

 2: connect internal

 3: alter tablespace <TABLESPACE NAME> begin backup;

 4: exit

 5: EOF

 6:

 7: Unix>sqlplus system/<password> <TABLESPACE NAME> <<EOF

 8: rem

 9: rem Tablespace file cold backup script by Dan Hotka 2/15/1998

10: rem

11: define tname=&1

12: set feedback off

13: set verify off

14: set termout off

15: set echo off

16: set headings off

17: spool hot_back.lst

18: select  file_name

19: from sys.dba_data_files

20: where tablespace_name = &tname

21: /

22: spool off

23: exit

24: EOF

Listing 23.9 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, hot_back.lst, created by Listing 23.8.

Listing 23.9. 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/98

 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 || ‘/*.* >>

hot_back.lst ‘

14: FROM sys.dba_directories

15: /

16: spool off

17: host bfile.sh

18: host rm bfile.sh

19: exit

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.8. Notice the UNIX-born 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.8. Line 17 cleans up the shell script after its use.

The first Unix> script in Listing 23.10 creates a copy of the active control file in the UNIX directory tmp. The second Unix> command switches the log file to a new log file. This will enable the backup copy to have all changed data. The last Unix> command copies a few other important configuration files to the backup list, as well as the just- created control file in the tmp directory.

Listing 23.10. Add other files to hot backup file list.

 1: Unix> svrmgrl <<EOF

 2: connect internal

 3: alter database backup controlfile to /tmp/<oracle instance>.ctl;

 4: exit

 5: EOF

 6:

 7: Unix> svrmgrl <<EOF

 8: connect internal

 9: alter system switch logfile;

10: exit

11: EOF

12:

13: Unix>ls  /tmp/*.ctl

14: $ORACLE_HOME/rdbms/init*.ora

15: $ORACLE_HOME/admin/network/*.ora

16: <log_archive_dest>*.* >> hot_back.lst

The code in Listing 23.11 will need to be modified for the syntax of your particular backup medium and computing environment. This example uses standard UNIX commands to copy the required backup files to a tape device.

Listing 23.11. Processing file list for hot backup.

1: Unix> filesystem_backup.sh <<EOF

2: #!/bin/sh

3:

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

5: EOF

6: Unix>

7: Unix>

The code in Listing 23.12 can be used for UNIX raw partitions, putting 1 raw partition on a single tape. Consult your Oracle System Administration Guide for specifics on UNIX multi-volumn tape backup.

Listing 23.12. Processing file list for hot backup with unix raw partitions.

 1: Unix> rawpartition_backup.sh <<EOF

 2: #!/bin/sh

 3:

 4: tar for j in \`cat hot_back.lst\`

 5: do

 6:     echo “Put tape in tape device 0”

 7:     pause “Hit Return to continue...”

 8:     dd if=$j if=/dev/rmt0

 9: done

10:

11: EOF

12: Unix>

Listing 23.13 will end the hot tablespace backup, and Oracle will then apply any changes for the tablespace from the log files.

Listing 23.13 Script to end the tablespace backup mode.

1: Unix> svrmgrl <<EOF

2: connect internal

3: alter tablespace <TABLESPACE NAME> end backup;

4: exit

5: EOF

Recovery Via Scripts

Cold backup recovery is accomplished by first taking the Oracle RDBMS completely down.

Unix> svrmgrl <<EOF

connect internal

shutdown abort

exit

EOF