Re: Backup with Veritas 9.0

From: Paul Drake <drak0nian_at_yahoo.com>
Date: 30 Sep 2003 15:15:49 -0700
Message-ID: <1ac7c7b3.0309301415.3754a65b@posting.google.com>

allan.basso_at_proquestalison.com (Allan) wrote in message news:<f41fc10c.0309300709.2bce1304_at_posting.google.com>...
> I am using Oracle 9ias and I am trying to back the DB's up with
> Veritas Backup Exec 9.0. I am not able to attach to the DB's becuase
> I need to have SysDBA when I attempt to access it. I can of course
> attach to and see the DB with DBA but when I attempt to identify the
> table spaces that need to be backed up, I need to have sysdba in order
> to list them and therefore selcet them for backup. I have tried to
> contact Veritas with this but all I can get is a promise to call back.
> This has not happened as of yet. Has anyone else run into this
> problem?

um, who owns the databases?
Is there another dba/sysadmin that is responsible for these?

If all that Veritas Backup Exec 9.0 wants to do is to list the tablespaces and datafiles, the role "SELECT CATALOG ROLE" should suffice.

More than likely, provided that the database is in archivelog mode, the backup software oracle agent will attempt to do 2 things during a hot backup:

alter all of the tablespaces into backup mode; backup all of the datafiles; (to tape)
alter all of the tablespaces out of backup mode; create a backup controlfile; (to disk, copy to tape)

I haven't used the oracle agent since 8.0, as I find hot backups to disk more preferable, as then no restore from tape is required. simply compress the hot backup staging area to another mount point. in the event that the staging directory is lost due to media failure, fat fingers, etc, the compressed set is still available on disk. This should all be scripted so that all you have to do is check your email in the morning that:

the scheduled backup to disk ran successfully (a directory listing is good)
the scheduled tape backup ran

I do like the idea of having 8 times the size of the database available as storage, so that physical backups, exports, rman sets and archived redo logs can be restored without having to say "Lets go to the (video)tape". Still put it on tape for disaster recovery/business continuity.
Real datafiles go on pristine filesystems that do not have any churn, backup sets and archived logs go on filesystems that can be reformatted at any point.

this will likely look bad formatting-wise, but I this works for me:

column tablespace format a15
column file_name format a45
column backup_time format a18
column file_mb format a10
column s format a1
column datafile_name format a35
column last_hot_backup format a20
rem storage for tablespaces with datafiles (temp files excluded) rem removes 'ORACLE\ORADATA\' from the datafile name rem includes autoextend, backup status and backup time info select rpad(df_free.ts_name,15) Tablespace,

rpad(replace(df_free.df_name,'ORACLE\ORADATA\',' - '),35) Datafile_name,

df_free.mb_free,df_total.mb_total,100*round(df_free.mb_free/df_total.mb_total,2) pct_free,

decode(vb.status,'ACTIVE','A','NOT ACTIVE','N') "S", df_total.axt "Ext",

rpad(to_char(vb.time,'RRRRMMDD HH24:MI'),16) last_hot_backup from (select fs.tablespace_name ts_name,df.file_name df_name,

               trunc(sum(fs.bytes)/1048576) mb_free
          from dba_data_files df, dba_free_space fs
         where fs.tablespace_name=df.tablespace_name
           and fs.file_id = df.file_id
         group by fs.tablespace_name,df.file_name) df_free,
       (select df.tablespace_name ts_name,df.file_name df_name,
               df.file_id file_id,df.autoextensible axt,
trunc(df.bytes/1048576) mb_total
          from dba_data_files df) df_total,
       v$backup vb

where df_free.ts_name = df_total.ts_name and df_free.df_name = df_total.df_name and vb.file# = df_total.file_id
order by 1,2
/

TABLESPACE      DATAFILE_NAME                          MB_FREE

MB_TOTAL PCT_FREE S Ext LAST_HOT_BACKUP

--------------- ----------------------------------- ----------
---------- ---------- - --- --------------------
DRSYS           G:\ - DRSYS01.DBF                       22         32 
       69 N YES 20030922 19:30
INDEX_DATA      G:\ - INDEX_DATA01.DBF                 445        512 
       87 N YES 20030922 19:31
INDEX_DATA_LARG G:\ - INDEX_DATA_LARGE01.DBF           510        512 
      100 N YES 20030922 19:31
INDEX_WORK      G:\ - INDEX_WORK01.DBF                 127        128 
       99 N YES 20030922 19:31
ITD_BACKUP      G:\ - ITD_BACKUP01.DBF                 127        128 
       99 N YES 20030922 19:31
ITD_DATA        G:\ - ITD_DATA01.DBF                   127        128 
       99 N YES 20030922 19:31
PERFSTAT        G:\ - PERFSTAT01.DBF                   132        256 
       52 N YES 20030922 19:31
SYSTEM          G:\ - SYSTEM01.DBF                     155        512 
       30 N YES 20030922 19:30
TOOLS           G:\ - TOOLS01.DBF                       31         32 
       97 N YES 20030922 19:30
UNDOTBS1        G:\ - UNDOTBS01.DBF                   1008       1024 
       98 N NO  20030922 19:30
USER_DATA       G:\ - USER_DATA01.DBF                  138        512 
       27 N YES 20030922 19:30
USER_DATA_LARGE G:\ - USER_DATA_LARGE01.DBF            504        512 
       98 N YES 20030922 19:30
USER_WORK       G:\ - USER_WORK01.DBF                  245        256 
       96 N YES 20030922 19:31
XDB             G:\ - XDB01.DBF                          3         48 
        6 N YES 20030922 19:30

Received on Tue Sep 30 2003 - 17:15:49 CDT