Introduction. It is simple to generate hot backup scripts for non-raw unix Oracle database systems. The power of unix scripting makes life easy, allows DBA's to generate cp or even compress -k statements for all files in the database and more importantly automatically thus allowing such scripts to continue with minimal or no manual intervention. For the OS/390 platform this task at first seems daunting, do we create some REXX to automatically generate the alter database statements and then the DUMP statements? Well it would be taking a 'Hammer to crack a nut'. I found it can be done just as easily as the unix scripts but using JCL, SQLPLUS and some PL/SQL code. My solution for undertaking hot backups on the OS/390 platform uses JCL to invoke SQLPLUS, this then calls PL/SQL to generate the necessary alter database statements and DFDSS dump statements for each tablespace and the corresponding datasets. It's simple and as it looks at Oracle to generate the tablespaces and dump statements for each file requires minimal manual intervention. Please feel free to copy, but you will have to customise for your environment and TEST. I cannot be held responsible though for this papers contents being applied, or any errors found or any ommissions. You must take it as seen and use your own judgement if you wish to use it. However enjoy life and this paper. Contents (1). Sample JCL used to build online backup JCL statements. (2). Sample PL/sql code used to generate alter database begin, Dump, End statements JCL dd cards (1). Sample JCL to build online backup JCL statements. //* -------------------------------------------------------- //* BUILD ONLINE BACKUP JCL //* --------------------------------------------------------- //STEP1 EXEC PGM=IEFBR14 //A1 DD DISP=(MOD,DELETE),DSN=WILL01.BKUPJCL.DATA //* -------------------------------------------------------- //GENJCL EXEC ORASQLV7,PARM='++/DD/LOGON' //STEPLIB DD DISP=SHR,DSN=ORACLE.TEST.CMDLOAD //LOGON DD DISP=SHR,DSN=WILL01.DBA.LIB(LOGON) //SPOOLED DD DISP=(,CATLG),DSN=WILL01.BKUPJCL.FDATA, // DCB=(RECFM=FB,LRECL=80) //SYSPRINT DD SYSOUT=* //SQLLOGIN DD DUMMY //ORA@DAVE DD DUMMY //SYSIN DD DSN=WILL01.TEST.PARMLIB(HOTBKUP1),DISP=SHR //* -------------------------------------------------------- Notes : The above is fairly standard and should not be too difficult to follow. We are running SQLPLUS in batch, the DSN which will hold the generated hotbackup JCL statements has a DD name of SPOOLED and (for this example) the database name is called DAVE (after my son - David). The High Level Qualifier WILL01 is just my login name. Some datasets are required to set up the SQLPLUS environment correctly : The dataset WILL01.TEST.PARMLIB(HOTBKUP1) holds the following:- START /DSN/ORACLE.DEVL.PARMLIB(HOTBKP2B) and the dataset /DSN/ORACLE.DEVL.PARMLIB(HOTBKP2B) holds :- START /DSN/ORACLE.DEVL.PARMLIB(HOTBKP2C) SPOOL /DD/SPOOLED START /DSN/ORACLE.SQL.HOTBAKUP.FDATA Dataset /DSN/ORACLE.DEVL.PARMLIB(HOTBKP2C) ensures the sqlplus spooled output is correct. SET ECHO OFF SET VERIFY OFF SET TERMOUT OFF SET FEEDBACK OFF SET HEADING OFF SET PAGESIZE 0 SET LINESIZE 80 SET SERVEROUTPUT ON FORMAT WRAPPED Note the WRAPPED parameter is needed so that dbms.output does not truncate white spaces from the beginning of the output line. A handy trick ;-) (2).. PL/SQL code to build hot backup JCL DECLARE -->>> CURSOR'S CURSOR GTS IS SELECT TABLESPACE_NAME FROM SYS.DBA_TABLESPACES WHERE TABLESPACE_NAME ^= 'SYSTEM' ORDER BY TABLESPACE_NAME; CURSOR GDF_A(ATS SYS.DBA_TABLESPACES.TABLESPACE_NAME%TYPE) IS SELECT SUBSTR(FILE_NAME,6) FROM SYS.DBA_DATA_FILES WHERE TABLESPACE_NAME = ATS; CURSOR GDF_B IS SELECT SUBSTR(FILE_NAME,6) FROM SYS.DBA_DATA_FILES WHERE TABLESPACE_NAME = 'SYSTEM'; -->>> VARIABLES AFILE_REC SYS.DBA_DATA_FILES.FILE_NAME%TYPE; ADATE CHAR(13); X NUMBER :=1; -->>> EXECUTE CODE BEGIN /* INITIALIZE VARIABLES */ SELECT 'D'||TO_CHAR(SYSDATE,'YYMMDD')||'.T'||TO_CHAR(SYSDATE,'HH24MI') INTO ADATE FROM DUAL; /* JOB CARD */ DBMS_OUTPUT.ENABLE(80000); DBMS_OUTPUT.PUT_LINE('//WILL01AA JOB A773,'DAVE BACKUP',CLASS=8,MSGCLASS=R'); DBMS_OUTPUT.PUT_LINE('/*JOBPARM L=9999,K=0'); DBMS_OUTPUT.PUT_LINE('//PROCLIB1 JCLLIB ORDER=ORACLE.TEST.PROCLIB'); DBMS_OUTPUT.PUT_LINE('//COPYINIT EXEC PGM=IEBCOPY'); DBMS_OUTPUT.PUT_LINE('//SYSPRINT DD SYSOUT=*'); DBMS_OUTPUT.PUT_LINE('//COPYOUT DD DSN=WILL01.DUMP.'||ADATE||'.LBL'||X||'.TAPE,'); DBMS_OUTPUT.PUT_LINE('// DISP=(NEW,CATLG),LABEL=('||X||',SL),'); DBMS_OUTPUT.PUT_LINE('// VOL=(,RETAIN)'); DBMS_OUTPUT.PUT_LINE('//COPYIN DD DSN=ORACLE.TEST.PARMLIB,DISP=SHR'); DBMS_OUTPUT.PUT_LINE('//SYSIN DD * '); DBMS_OUTPUT.PUT_LINE(' copy indd=copyin,outdd=copyout '); DBMS_OUTPUT.PUT_LINE(' select member=(initdave) '); /* BUILD EXEC STEPS */ FOR GTS_CUR IN GTS LOOP /* BUILD ALTER TABLESPACE BEGIN BACKUP EXEC STEP */ DBMS_OUTPUT.PUT_LINE('//* '); DBMS_OUTPUT.PUT_LINE('//STRTBKUP EXEC ORADBAV7,COND=(4,LT)'); DBMS_OUTPUT.PUT_LINE('//STEPLIB DD DISP=SHR,DSN=ORACLE.TEST.CMDLOAD'); DBMS_OUTPUT.PUT_LINE('//SYSPRINT DD SYSOUT=* '); DBMS_OUTPUT.PUT_LINE('//ORA@DAVE DD DUMMY '); DBMS_OUTPUT.PUT_LINE('//SQLLOGIN DD DUMMY '); DBMS_OUTPUT.PUT_LINE('//SYSIN DD * '); DBMS_OUTPUT.PUT_LINE(' CONNECT INTERNAL '); DBMS_OUTPUT.PUT_LINE(' ALTER TABLESPACE '||GTS_CUR.TABLESPACE_NAME||' '); DBMS_OUTPUT.PUT_LINE(' BEGIN BACKUP; '); DBMS_OUTPUT.PUT_LINE('/* '); /* FOR THIS TABLESPACE GET THE DATAFILES */ OPEN GDF_A(GTS_CUR.TABLESPACE_NAME); LOOP FETCH GDF_A INTO AFILE_REC; IF (GDF_A%FOUND) THEN /* IF ANY - BUILD THE DFDSS COPY EXEC STEP */ X:=X+1; DBMS_OUTPUT.PUT_LINE('//* '); DBMS_OUTPUT.PUT_LINE('//COPYFLE EXEC PGM=ADRDSSU,COND=(4,LT) '); DBMS_OUTPUT.PUT_LINE('//SYSPRINT DD SYSOUT=*'); DBMS_OUTPUT.PUT_LINE('//DUMPOUT DD DSN=WILL01.DUMP.'||ADATE||'.LBL'||X||'.TAPE,'); DBMS_OUTPUT.PUT_LINE('// DISP=(NEW,CATLG),LABEL=('||X||',SL),'); DBMS_OUTPUT.PUT_LINE('// VOL=(,RETAIN,REF=*.COPYINIT.COPYOUT)'); DBMS_OUTPUT.PUT_LINE('//SYSIN DD * '); DBMS_OUTPUT.PUT_LINE(' DUMP DATASET(INCLUDE('||AFILE_REC||')) - '); DBMS_OUTPUT.PUT_LINE(' OUTDD(DUMPOUT) TOL(ENQF) WAIT(0,0) '); DBMS_OUTPUT.PUT_LINE('/* '); DBMS_OUTPUT.PUT_LINE('//* ----------------------------------------------- '); DBMS_OUTPUT.PUT_LINE('//*DUMPIN DD DSN=WILL01'||'.DUMP.'||ADATE||'.LBL'||X||'.TAPE,'); DBMS_OUTPUT.PUT_LINE('//* DISP=OLD'); DBMS_OUTPUT.PUT_LINE('//*SYSIN DD * '); DBMS_OUTPUT.PUT_LINE('//* RESTORE DATASET(INCLUDE('||AFILE_REC||')) -*/ '); DBMS_OUTPUT.PUT_LINE('//* INDD(DUMPIN) */'); DBMS_OUTPUT.PUT_LINE('//* ----------------------------------------------- '); END IF; EXIT WHEN (GDF_A%NOTFOUND); END LOOP; CLOSE GDF_A; /* BUILD END BACKUP EXEC */ DBMS_OUTPUT.PUT_LINE('//* '); DBMS_OUTPUT.PUT_LINE('//ENDBKUP EXEC ORADBAV7'); DBMS_OUTPUT.PUT_LINE('//STEPLIB DD DISP=SHR,DSN=ORACLE.TEST.CMDLOAD'); DBMS_OUTPUT.PUT_LINE('//SYSPRINT DD SYSOUT=* '); DBMS_OUTPUT.PUT_LINE('//ORA@DAVE DD DUMMY '); DBMS_OUTPUT.PUT_LINE('//SQLLOGIN DD DUMMY '); DBMS_OUTPUT.PUT_LINE('//SYSIN DD * '); DBMS_OUTPUT.PUT_LINE(' CONNECT INTERNAL '); DBMS_OUTPUT.PUT_LINE(' ALTER TABLESPACE '||GTS_CUR.TABLESPACE_NAME||' '); DBMS_OUTPUT.PUT_LINE(' END BACKUP; '); DBMS_OUTPUT.PUT_LINE('/* '); END LOOP; /* ----------------------------- */ /* DO THE SYSTEM TABLESPACE LAST */ /* ----------------------------- */ DBMS_OUTPUT.PUT_LINE('//* '); DBMS_OUTPUT.PUT_LINE('//STRTBKUP EXEC ORADBAV7'); DBMS_OUTPUT.PUT_LINE('//STEPLIB DD DISP=SHR,DSN=ORACLE.TEST.CMDLOAD'); DBMS_OUTPUT.PUT_LINE('//SYSPRINT DD SYSOUT=* '); DBMS_OUTPUT.PUT_LINE('//ORA@DAVE DD DUMMY '); DBMS_OUTPUT.PUT_LINE('//SQLLOGIN DD DUMMY '); DBMS_OUTPUT.PUT_LINE('//SYSIN DD * '); DBMS_OUTPUT.PUT_LINE(' CONNECT INTERNAL '); DBMS_OUTPUT.PUT_LINE(' ALTER TABLESPACE SYSTEM '); DBMS_OUTPUT.PUT_LINE(' BEGIN BACKUP; '); DBMS_OUTPUT.PUT_LINE('/* '); /* FOR THE SYSTEM TABLESPACE GET THE DATAFILES */ OPEN GDF_B; LOOP FETCH GDF_B INTO AFILE_REC; IF (GDF_B%FOUND) THEN X:=X+1; DBMS_OUTPUT.PUT_LINE('//* '); DBMS_OUTPUT.PUT_LINE('//COPYFLE EXEC PGM=ADRDSSU,COND=(4,LT) '); DBMS_OUTPUT.PUT_LINE('//SYSPRINT DD SYSOUT=* '); DBMS_OUTPUT.PUT_LINE('//DUMPOUT DD DSN=WILL01.DUMP.'||ADATE||'.LBL'||X||'.TAPE,'); DBMS_OUTPUT.PUT_LINE('// DISP=(NEW,CATLG),LABEL=('||X||',SL),'); DBMS_OUTPUT.PUT_LINE('// VOL=(,RETAIN,REF=*.COPYINIT.COPYOUT)'); DBMS_OUTPUT.PUT_LINE('//SYSIN DD * '); DBMS_OUTPUT.PUT_LINE(' DUMP DATASET(INCLUDE('||AFILE_REC||')) -'); DBMS_OUTPUT.PUT_LINE(' OUTDD(DUMPOUT) TOL(ENQF) WAIT(0,0) '); DBMS_OUTPUT.PUT_LINE('/* '); DBMS_OUTPUT.PUT_LINE('//* ----------------------------------------------- '); DBMS_OUTPUT.PUT_LINE('//*DUMPIN DD DSN=WILL01.DUMP.'||ADATE||'.LBL'||X||'.TAPE,'); DBMS_OUTPUT.PUT_LINE('//* DISP=OLD'); DBMS_OUTPUT.PUT_LINE('//*SYSIN DD * '); DBMS_OUTPUT.PUT_LINE('//* RESTORE DATASET(INCLUDE('||AFILE_REC||')) -*/ '); DBMS_OUTPUT.PUT_LINE('//* INDD(DUMPIN) */'); DBMS_OUTPUT.PUT_LINE('//* ----------------------------------------------- '); END IF; EXIT WHEN (GDF_B%NOTFOUND); END LOOP; CLOSE GDF_B; DBMS_OUTPUT.PUT_LINE('//ENDBKUP EXEC ORADBAV7'); DBMS_OUTPUT.PUT_LINE('//STEPLIB DD DISP=SHR,DSN=ORACLE.TEST.CMDLOAD'); DBMS_OUTPUT.PUT_LINE('//SYSPRINT DD SYSOUT=* '); DBMS_OUTPUT.PUT_LINE('//ORA@DAVE DD DUMMY '); DBMS_OUTPUT.PUT_LINE('//SQLLOGIN DD DUMMY '); DBMS_OUTPUT.PUT_LINE('//SYSIN DD * '); DBMS_OUTPUT.PUT_LINE(' CONNECT INTERNAL '); DBMS_OUTPUT.PUT_LINE(' ALTER TABLESPACE SYSTEM '); DBMS_OUTPUT.PUT_LINE(' END BACKUP; '); DBMS_OUTPUT.PUT_LINE('/*'); DBMS_OUTPUT.PUT_LINE('//'); END; / Note... There's scope to make this a production PL/sql code. For example you could pass parameter's down to quickly change the SID, jobcard, etc. It's a nice touch also to have the restore cards commented out with the dump cards. If required I think you will find this most useful. Most of it should be fairly standard stuff: Alter begin statements, dump statements then finally the alter end statements. I dump to cartridge and use a refer back is used to the first step, attempting to keep files on the same tape;-) In testing I dumped 12G to tape in 30mins, this with waiting for cartridge mounts. It's OK as I don't have to worry about the batch slot yet. I have done a simulated recovery using similar JCL as above. It seemed to work... Regards Leon Will (Aug 1998) email leon.will@tesco.net Useful comments appreciated, cannot say I will reply quickly as I am a DBA contractor out on site most of the time.....