Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Dynamically generate sql for hot backup

Re: Dynamically generate sql for hot backup

From: Howard J. Rogers <howardjr_at_www.com>
Date: Tue, 23 Jan 2001 17:16:22 +1100
Message-ID: <3a6d218f@news.iprimus.com.au>

My first thought is, why bother? Your script will do perfectly well as written. I can't think for a moment that your RBS tablespace will suffer by having half copied at one time and half copied at another -provided all the redo is available, Oracle will sort it out.

My second thought is: one of my students came up with the following on the backup and recovery course last week. Shell scripting not being my thing, I have no idea whether this will do what you want or not. My suspicion is not, but I'd appreciate your comments on it anyway:

Remember to backup the files in the backup directory and

# Archive log directories to tape before running this script
#
# This script will generate errors when attempting to put
# read only tablespaces into backup mode but will still
# back them up

sqlplus /nolog <<eof > bckup.lis
  connect / as sysdba

  drop table temptbl;
  commit;
  create table temptbl (no number(4),text varchar2(500));

  declare cursor c1 is select name from v\$tablespace;

          counter number(4) := 1;
  begin
    insert into temptbl values(counter,null);     for c1rec in c1 loop

      counter := counter + 1;
      insert into temptbl
        select counter,'alter tablespace '||c1rec.name||' begin backup;'
        from dual;
      counter := counter + 1;
      insert into temptbl
        select counter,'!cp '||df.name||' $HOME/BCKUP/'
        from v\$datafile df, v\$tablespace ts
        where ts.TS# = df.TS#
        and ts.name = c1rec.name;
      counter := counter + 1;
      insert into temptbl
        select counter,'alter tablespace '||c1rec.name||' end backup;'
        from dual;
      insert into temptbl values(counter,null);
   end loop;
  end;
/
eof

sqlplus -s /nolog <<eof >> bckup.lis
  connect / as sysdba
  set head off termout off verify off feedback off echo off pagesize 0   spool bckup.sql
  select 'alter database backup controlfile to ''$HOME/BCKUP/control.ctl'';'   from dual;
  select text from temptbl order by no;
  spool off
eof
sqlplus /nolog <<eof >> bckup.lis
  connect / as sysdba
  @bckup
eof
rm bckup.sql

Regards
HJR "Dmitry Vays" <dimaweiss_at_home.com> wrote in message news:_39b6.164587$w35.29679886_at_news1.rdc1.nj.home.com...
> Hi everybody,
> I have this script
> select 'alter tablespace ' || tablespace_name || ' begin backup; ' ,
> '! cp ' || file_name || ' $ORA_HOT ', 'alter tablespace ' ||
> tablespace_name || ' end backup
> from dba_data_files

>
>
>

> IT WILL PRODUCE THE FOLLOWING :
>

> alter tablespace SYSTEM begin backup;
> ! cp /data1/oradata/eccp/system01.dbf $ORA_HOT
> alter tablespace SYSTEM end backup;
>

> alter tablespace RBS begin backup;
> ! cp /log1/eccp/rbs01.dbf $ORA_HOT
> alter tablespace RBS end backup;
>

> alter tablespace RBS begin backup;
> ! cp /log2/eccp/rbs02.dbf $ORA_HOT
> alter tablespace RBS end backup;
>

> alter tablespace USERS begin backup;
> ! cp /data1/oradata/eccp/users01.dbf $ORA_HOT
> alter tablespace USERS end backup;
>

> If you notice I have 2 datafiles for RBS tablespace.
>

> Can anobody help my to achieve the following result:
>

> alter tablespace RBS begin backup;
> ! cp /log1/eccp/rbs01.dbf $ORA_HOT
> ! cp /log2/eccp/rbs02.dbf $ORA_HOT
> alter tablespace RBS end backup;
>

> Dima

>
> Received on Tue Jan 23 2001 - 00:16:22 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US