Re: Backup Script

"Howard J. Rogers" wrote:
>
> I have here a hot backup shell script which a student of one of my backup
> and recovery courses knocked up during the brief interludes when I wasn't
> waffling on about one thing or another.
>
> I can follow the logic, and I *think* it seems OK.
>
> Being a mere humble Microsoft boffin, however, I wouldn't know one end of a
> truss from a top or a nice, so I can't be sure (and I can't currently test
> it on any Solaris box to which I normally have access).
>
> So I throw it out here to see whether there are Unix bods that can rip it to
> shreds and make it better. Or Unix bods who can say, "Yup, it's not bad".
> Comments, please, in any case. My apologies for the length (I'm sure a
> Windows batch file would have been smaller!):

Aside from the problems others have mentioned, I don't see this script skipping offline tablespaces (though I might have missed that part). Looks like the coder put in a comment (# SYSTEM, ONLINE, OFFLINE) along those lines, as if he/she intended to come back and work on that part, but it's hard for me to tell. The relevent section is below:  

> # Statment used to extract tablespaces in database #
>
> EXTRACTDATAFILESSQL="set head off
> set feedback off
> SELECT d.file_id || ' ' || d.file_name || ' ' || d.tablespace_name
> || ' ' || t.contents || ' ' || v.status || ' ' || t.status
> FROM sys.dba_data_files d, v\$datafile v, dba_tablespaces t
> WHERE d.file_id = v.file#
> AND d.tablespace_name = t.tablespace_name;
> exit"

> while [ -n "$DATAHOLD" -o -n "$LINE" ] # We have data to analyse
> do
> set -- $LINE # Break up the line into variables
> if [ -n "$LINE" ]
> then
> eval DATAFILENO$COUNT=$1 # Hold datafile number
> eval DATAFILE$COUNT=$2 # Hold datafile name
> eval TABLESPACE$COUNT=$3 # Hold tablespace name
> eval TABLECONTENTS$COUNT=$4 # Hold tablespace content type
> eval DATAFILESTATUS$COUNT=$5 # Hold datafile status
> # SYSTEM, ONLINE, OFFLINE
> if [ "$6" = "READ" -a "$7" = "ONLY" ] # READ ONLY tablespace
> then
> eval TABLEREADONLY$COUNT=true

Basically you want to skip trying to put tablespaces into hotbackup mode if they are offline or read-only. Looks like he/she got the read-only part, but not the offline part. For what it's worth, our main backup script just queries tablespace names from a join of v$datafile and v$tablespace where v$datafile.enabled not in ('READ ONLY', 'DISABLED'). In other words, try to put all tablespaces into hotbackup mode except those containing datafiles that are read only or disabled (disabled is not the same as offline in terms of datafiles). Try to put all others in hotbackup mode since we want to know about failures. For instance, if a tablespace is online but has one offline datafile, its datafiles will show 'READ WRITE' in the v$datafile.enabled column. This is good: we will therefore try to put that tablespace into hotbackup mode, the command will fail, and we'll get paged on the error. But if the tablespace itself is offline, v$datafile.enabled will show 'DISABLED' for all of the datafiles in the tablespace, meaning no writes are occurring on those files. Backups of such files are OK - they may or may not need recovery depending on whether the tablespace was taken offline normal, temporary, or immediate, but that's OK (we're in archivelog mode after all). So if the tablespace is offline or read-only, go ahead and backup its datafiles, but don't try to put it into hotbackup mode because it'll error out even though you don't want it to.

Regards,
Sean Received on Tue Jul 16 2002 - 17:49:22 CDT