-- This view stores a query, that when run, creates an RMAN script.
-- The RMAN script creates one backup, stored into two locations on 
-- the file system.

CREATE OR REPLACE FORCE VIEW SYSTEM.CREATE_RMAN_SCRIPT
(TEXT)
AS 
SELECT
-- ©2005 Edward Stoever 
    '### connect string: rman target sys/***'
       || '***@test nocatalog'
       || CHR (10)
       || '### tnsnames.ora must have test listed '
       || 'as a dedicated connection' AS text
  FROM DUAL
UNION ALL
SELECT 'CONFIGURE CONTROLFILE AUTOBACKUP OFF;'
  FROM DUAL
UNION ALL
SELECT 'CONFIGURE MAXSETSIZE TO 4G;'
  FROM DUAL
UNION ALL
SELECT 'RUN' || CHR (10) || '{'
  FROM DUAL
UNION ALL
SELECT    '  ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT '
       || '''DKB100:[ORACLE.RMAN_BACKUPS]%U'';'
  FROM DUAL
UNION ALL
SELECT    '  ALLOCATE CHANNEL disk2 DEVICE TYPE DISK FORMAT '
       || '''DKB600:[ORACLE.RMAN_BACKUPS]%U'';'
  FROM DUAL
UNION ALL
SELECT '  BACKUP INCREMENTAL LEVEL = 0'
  FROM DUAL
UNION ALL
SELECT    DECODE ((SELECT MIN (file_id)
                     FROM DBA_DATA_FILES
                    WHERE DECODE (MOD (file_id, 2), 1, file_id) IS NOT NULL),
                  file_id, '     (  DATAFILE',
                  '                '
                 )
       || LPAD (file_id, 3)
       || DECODE ((SELECT MAX (file_id)
                     FROM DBA_DATA_FILES
                    WHERE DECODE (MOD (file_id, 2), 1, file_id) IS NOT NULL),
                  file_id, '    ### --> '
                   || tablespace_name
                   || CHR (10)
                   || '        CHANNEL disk1'
                   || CHR (10)
                   || '     )',
                  ',   ### --> ' || tablespace_name
                 )
  FROM DBA_DATA_FILES
 WHERE DECODE (MOD (file_id, 2), 1, file_id) IS NOT NULL
UNION ALL
SELECT    DECODE ((SELECT MIN (file_id)
                     FROM DBA_DATA_FILES
                    WHERE DECODE (MOD (file_id, 2), 0, file_id) IS NOT NULL),
                  file_id, '     (  DATAFILE',
                  '                '
                 )
       || LPAD (file_id, 3)
       || DECODE ((SELECT MAX (file_id)
                     FROM DBA_DATA_FILES
                    WHERE DECODE (MOD (file_id, 2), 0, file_id) IS NOT NULL),
                  file_id, '    ### --> '
                   || tablespace_name
                   || CHR (10)
                   || '        CHANNEL disk2'
                   || CHR (10)
                   || '     )',
                  ',   ### --> ' || tablespace_name
                 )
  FROM DBA_DATA_FILES
 WHERE DECODE (MOD (file_id, 2), 0, file_id) IS NOT NULL
UNION ALL
SELECT    '     (  ARCHIVELOG UNTIL TIME ''SYSDATE'' CHANNEL disk2  )'
       || CHR (10)
       || '     (  CURRENT CONTROLFILE             CHANNEL disk2  );'
       || CHR (10)
       || '}'
  FROM DUAL
WITH READ ONLY;
/

SET LINESIZE 86
SELECT * FROM CREATE_RMAN_SCRIPT;
TEXT                                                                                                                    
--------------------------------------------------------------------------------------
### connect string: rman target sys/******@test nocatalog
### tnsnames.ora must have test listed as a dedicated connection
CONFIGURE CONTROLFILE AUTOBACKUP OFF;                                                                                   
CONFIGURE MAXSETSIZE TO 4G;                                                                                             
RUN
{                                                                                                                     
  ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT 'DKB100:[ORACLE.RMAN_BACKUPS]%U';                                      
  ALLOCATE CHANNEL disk2 DEVICE TYPE DISK FORMAT 'DKB600:[ORACLE.RMAN_BACKUPS]%U';                                      
  BACKUP INCREMENTAL LEVEL = 0                                                                                          
     (  DATAFILE  1,   ### --> SYSTEM                                                                                   
                  3,   ### --> LOBS                                                                                     
                  5,   ### --> INDX                                                                                     
                  7,   ### --> USERS                                                                                    
                  9,   ### --> INDX                                                                                     
                 11,   ### --> BOSSCARS                                                                                 
                 13,   ### --> DEVELOPMENT                                                                              
                 15    ### --> DEVELOPMENT
        CHANNEL disk1
     )                                                   
     (  DATAFILE  2,   ### --> UNDOTBS1                                                                                 
                  6,   ### --> TOOLS                                                                                    
                 10,   ### --> FINARC                                                                                   
                 12,   ### --> DEVELOPMENT                                                                              
                 14,   ### --> DEVELOPMENT                                                                              
                 16    ### --> INDX32
        CHANNEL disk2
     )                                                        
     (  ARCHIVELOG UNTIL TIME 'SYSDATE' CHANNEL disk2  )
     (  CURRENT CONTROLFILE             CHANNEL disk2  );
}      
22 rows selected