REM $Id$
SET SERVEROUTPUT ON
store set set_settings.sql
set feedback off pagesize 0 heading off verify off
set linesize 100 trimspool on
DEFINE backup_dir = 's:\orabackup\webmip\files\'
REM Determine the oldest archive log to be backed up
archive log list;
alter system switch logfile;
REM
REM produce a SQL script that:
REM   places each tablespace into 'backup' mode,
REM   copies the contents of the tablespace to the 
REM     backup directory,
REM   returns each tablespace from 'backup' mode
REM
PROMPT *** SPOOLING
spool do_backup.sql
WITH tsp_df AS (
SELECT tsp.tablespace_name
      ,df.file_name
      ,rownum AS current_row
      ,first_value(rownum) over(PARTITION BY tsp.tablespace_name) AS first_row
      ,last_value(rownum) over(PARTITION BY tsp.tablespace_name) AS last_row
  FROM dba_tablespaces tsp
      ,dba_data_files  df
 WHERE tsp.contents <> 'TEMPORARY'
   AND tsp.tablespace_name = df.tablespace_name
)
SELECT cmd
  FROM (SELECT 'alter tablespace ' || tablespace_name || ' begin backup;' AS cmd
              ,tablespace_name
              ,1 AS seq
          FROM tsp_df
         WHERE current_row = first_row
        UNION
        SELECT 'host ocopy ' || file_name || ' &&backup_dir' AS cmd
              ,tablespace_name
              ,2 AS seq
          FROM tsp_df
         WHERE (current_row = first_row)
            OR (current_row <> first_row AND current_row <> last_row)
            OR (current_row = last_row AND last_row <> first_row)
        UNION
        SELECT 'alter tablespace ' || tablespace_name || ' end backup;' AS cmd
              ,tablespace_name
              ,3 AS seq
          FROM tsp_df
         WHERE current_row = last_row)
 ORDER BY tablespace_name
         ,seq
/
spool off;
PROMPT *** SPOOLING COMPLETE
@set_settings
@@do_backup
REM
REM Create SQL script to add temporary files to temporary tablespaces
REM
set feedback off pagesize 0 heading off verify off
set linesize 100 trimspool on
spool create_tempfile$.sql
select 'alter tablespace '||ts.name||' add tempfile '''||df.name||''' size '|| df.bytes||' reuse;' from v$tempfile df, v$tablespace ts
where ts.ts# = df.ts#
/
spool off
host move create_tempfile$.sql &&backupdir\create_tempfiles.sql
@set_settings
REM
REM create control file for the standby database
REM create backup control file for the primary database
REM
alter database create standby controlfile as 's:\orabackup\webmip\files\standby.ctl';
alter database backup controlfile to 's:\orabackup\webmip\files\backup.ctl';
REM
REM create a database initialization script for
REM the standby database
REM
create pfile='s:\orabackup\webmip\files\initstandby.ora' from spfile;
REM
REM Determine the current archive log to be backed up
archive log list;
alter system switch logfile;