Cold Backup Oracle Application Server Tips by Burleson Consulting

A cold or closed backup is taken when the database is closed using a shutdown normal or a shutdown immediate.  A shutdown abort is an inconsistent shutdown and required recovery.  Once the database is down normal (or immediate) all necessary files are up-to-date.  All open transactions were closed (committed or rolled back) when the database shuts down and the datafiles SCN match the SCN in the control files.  A cold backup is the easiest and simplest method of backing up the database.  Close the database, copy the files to the backup medium and restart the database.  You do not need to backup the undo tablespaces as there are no open transactions.  The drawback of a cold backup is that the database is not available and in the case of the application server, that requires that the application server also be unavailable.  If the application server requires constant availability you must use a hot backup.

Hot Backup

A hot or open backup is taken while the database is open and servicing user request.  Because data is changing while the backup is in progress, steps must be taken to insure that the backup is valid.  Because transactions are taking place while the backup is in operation the database MUST be in ARCHIVELOG mode.  The backup will not be consistent, meaning that each data file may be backup up at a different transactional point in time. This is ok because as long as the database has access to the archive log files, the Oracle recovery mechanism will be able to recover the data files to a consistent point.

Executing a hot backup is more complicated and stressful to the database.  It is not recommended that you create hot backups during high transaction periods.  The basis of a hot backup is Oracle?s ability to place a tablespace in backup mode.  If you remember, changes to data are logged in the transaction logs and are written to disk in the background by the DBW process.  When you place a tablespace in backup mode, DBW will not update the data file until it is taken out of backup mode.  So the steps to executing a ht backup of the metadata repository are:

1. Get a list of data files that support each tablespace.

2. Place a tablepace in backup mode.

3. Copy the datafiles to another location or to a backup medium

4. Take the tablespace out of backup mode.

5. Repeat Steps 2-4 for all remaining tablespaces.

6. Backup all the archive log files since the last backup.

7. Backup the control file

8. Backup necessary support files.

To get a list of datafiles and tablespaces use the command below:

SQL> set pages 999 line 90
SQL> column c1 heading 'File Name' format a52;
SQL> column c2 heading Tablespace|Name format a16;
SQL> select file_name c1, tablespace_name c2 from dba_data_files
  2  order by c2;

                                                     Tablespace
File Name                                            Name
---------------------------------------------------- ----------------
/u01/oracle/infra904/oradata/asdb/dcm.dbf            DCM
/u01/oracle/infra904/oradata/asdb/discopltc1.dbf     DISCO_PTM5_CACHE
/u01/oracle/infra904/oradata/asdb/discopltm1.dbf     DISCO_PTM5_META
/u01/oracle/infra904/oradata/asdb/drsys01.dbf        DRSYS
/u01/oracle/infra904/oradata/asdb/oss_sys01.dbf      DSGATEWAY_TAB
/u01/oracle/infra904/oradata/asdb/ias_meta01.dbf     IAS_META
/u01/oracle/infra904/oradata/asdb/ip_dt.dbf          IP_DT
/u01/oracle/infra904/oradata/asdb/ip_idx.dbf         IP_IDX
/u01/oracle/infra904/oradata/asdb/ip_lob.dbf         IP_LOB
/u01/oracle/infra904/oradata/asdb/ip_rt.dbf          IP_RT
/u01/oracle/infra904/oradata/asdb/oca.dbf            OCATS
/u01/oracle/infra904/oradata/asdb/attrs1_oid.dbf     OLTS_ATTRSTORE
/u01/oracle/infra904/oradata/asdb/battrs1_oid.dbf    OLTS_BATTRSTORE
/u01/oracle/infra904/oradata/asdb/gcats1_oid.dbf     OLTS_CT_STORE
/u01/oracle/infra904/oradata/asdb/gdefault1_oid.dbf  OLTS_DEFAULT
/u01/oracle/infra904/oradata/asdb/svrmg1_oid.dbf     OLTS_SVRMGSTORE
/u01/oracle/infra904/oradata/asdb/portal.dbf         PORTAL
/u01/oracle/infra904/oradata/asdb/ptldoc.dbf         PORTAL_DOC
/u01/oracle/infra904/oradata/asdb/ptlidx.dbf         PORTAL_IDX
/u01/oracle/infra904/oradata/asdb/ptllog.dbf         PORTAL_LOG
/u01/oracle/infra904/oradata/asdb/system01.dbf       SYSTEM
/u01/oracle/infra904/oradata/asdb/uddisys01.dbf      UDDISYS_TS
/u01/oracle/infra904/oradata/asdb/undotbs01.dbf      UNDOTBS
/u01/oracle/infra904/oradata/asdb/wcrsys01.dbf       WCRSYS_TS
 

 To place a tablespace in and out of backup mode use the command:

ALTER TABLESPACE tablespace_name BEGIN BACKUP;
ALTER TABLESPACE tablespace_name END BACKUP;

To backup the database control file you use the following command:

ALTER DATABASE BACKUP CONTROL FILE TO ?location? REUSE;

Where location is a directory where you are placing the backup files.  The final step is to backup other necessary file such as the init.ora/SPFILE, tnsname.ora etc.  These files do not normally change so they may not be backed up every time.  To execute a hot backup you create a script that executes each step.  The script below will create a hot backup for the Metadata Repository database as configured during installation.  Remember, the database must be in ARCHIVELOG mode.

- Hot Backup Script

- Run as SYSDBA

ALTER TABLESPACE DCM BEGIN BACKUP;
HOST cp /u01/oracle/infra904/oradata/asdb/dcm.dbf /u03/oracle/backup/.           
ALTER TABLESPACE DCM END BACKUP;

ALTER TABLESPACE DISCO_PTM5_CACHE BEGIN BACKUP;
HOST cp /u01/oracle/infra904/oradata/asdb/discopltc1.dbf
        /u03/oracle/backup/.    
ALTER TABLESPACE DISCO_PTM5_CACHE END BACKUP;

ALTER TABLESPACE DISCO_PTM5_META BEGIN BACKUP;
HOST cp /u01/oracle/infra904/oradata/asdb/discopltm1.dbf
        /u03/oracle/backup/.    
ALTER TABLESPACE DISCO_PTM5_META END BACKUP;

ALTER TABLESPACE DRSYS BEGIN BACKUP;
HOST cp /u01/oracle/infra904/oradata/asdb/drsys01.dbf /u03/oracle/backup/.       
ALTER TABLESPACE DRSYS END BACKUP;

ALTER TABLESPACE DSGATEWAY_TAB BEGIN BACKUP;
HOST cp /u01/oracle/infra904/oradata/asdb/oss_sys01.dbf /u03/oracle/backup/.    
ALTER TABLESPACE DSGATEWAY_TAB END BACKUP;

ALTER TABLESPACE IAS_META BEGIN BACKUP;
HOST cp /u01/oracle/infra904/oradata/asdb/ias_meta01.dbf
        /u03/oracle/backup/.    
ALTER TABLESPACE IAS_META END BACKUP;

ALTER TABLESPACE IP_DT BEGIN BACKUP;
HOST cp /u01/oracle/infra904/oradata/asdb/ip_dt.dbf /u03/oracle/backup/.         
ALTER TABLESPACE IP_DT END BACKUP;

ALTER TABLESPACE IP_IDX BEGIN BACKUP;
HOST cp /u01/oracle/infra904/oradata/asdb/ip_idx.dbf /u03/oracle/backup/.        
ALTER TABLESPACE IP_IDX END BACKUP;

ALTER TABLESPACE IP_LOB BEGIN BACKUP;
HOST cp /u01/oracle/infra904/oradata/asdb/ip_lob.dbf /u03/oracle/backup/.        
ALTER TABLESPACE IP_LOB END BACKUP;

ALTER TABLESPACE IP_RT BEGIN BACKUP;
HOST cp /u01/oracle/infra904/oradata/asdb/ip_rt.dbf /u03/oracle/backup/.         
ALTER TABLESPACE IP_RT END BACKUP;

ALTER TABLESPACE OCATS BEGIN BACKUP;
HOST cp /u01/oracle/infra904/oradata/asdb/oca.dbf /u03/oracle/backup/.          
ALTER TABLESPACE OCATS END BACKUP;

ALTER TABLESPACE OLTS_ATTRSTORE BEGIN BACKUP;
HOST cp /u01/oracle/infra904/oradata/asdb/attrs1_oid.dbf
        /u03/oracle/backup/.    
ALTER TABLESPACE OLTS_ATTRSTORE END BACKUP;

ALTER TABLESPACE OLTS_BATTRSTORE BEGIN BACKUP;
HOST cp /u01/oracle/infra904/oradata/asdb/battrs1_oid.dbf
        /u03/oracle/backup/.   
ALTER TABLESPACE OLTS_BATTRSTORE END BACKUP;

ALTER TABLESPACE OLTS_CT_STORE BEGIN BACKUP;
HOST cp /u01/oracle/infra904/oradata/asdb/gcats1_oid.dbf
        /u03/oracle/backup/.    
ALTER TABLESPACE OLTS_CT_STORE END BACKUP;

ALTER TABLESPACE OLTS_DEFAULT BEGIN BACKUP;
HOST cp /u01/oracle/infra904/oradata/asdb/gdefault1_oid.dbf
        /u03/oracle/backup/. 
ALTER TABLESPACE OLTS_DEFAULT END BACKUP;

ALTER TABLESPACE OLTS_SVRMGSTORE BEGIN BACKUP;
HOST cp /u01/oracle/infra904/oradata/asdb/svrmg1_oid.dbf
        /u03/oracle/backup/.    
ALTER TABLESPACE OLTS_SVRMGSTORE END BACKUP;

ALTER TABLESPACE PORTAL BEGIN BACKUP;
HOST cp /u01/oracle/infra904/oradata/asdb/portal.dbf /u03/oracle/backup/.     
ALTER TABLESPACE PORTAL END BACKUP;

ALTER TABLESPACE PORTAL_DOC BEGIN BACKUP;
HOST cp /u01/oracle/infra904/oradata/asdb/ptldoc.dbf /u03/oracle/backup/.     
ALTER TABLESPACE PORTAL_DOC END BACKUP;

ALTER TABLESPACE PORTAL_IDX BEGIN BACKUP;
HOST cp /u01/oracle/infra904/oradata/asdb/ptlidx.dbf  /u03/oracle/backup/.     
ALTER TABLESPACE PORTAL_IDX END BACKUP;

ALTER TABLESPACE PORTAL_LOG BEGIN BACKUP;
HOST cp /u01/oracle/infra904/oradata/asdb/ptllog.dbf  /u03/oracle/backup/.     
ALTER TABLESPACE PORTAL_LOG END BACKUP;

ALTER TABLESPACE SYSTEM BEGIN BACKUP;
HOST cp /u01/oracle/infra904/oradata/asdb/system01.dbf  /u03/oracle/backup/.     
ALTER TABLESPACE SYSTEM END BACKUP;

ALTER TABLESPACE UDDISYS_TS BEGIN BACKUP;
HOST cp /u01/oracle/infra904/oradata/asdb/uddisys01.dbf /u03/oracle/backup/.     
ALTER TABLESPACE UDDISYS_TS END BACKUP;

ALTER TABLESPACE UNDOTBS BEGIN BACKUP;
HOST cp /u01/oracle/infra904/oradata/asdb/undotbs01.dbf /u03/oracle/backup/.     
ALTER TABLESPACE UNDOTBS END BACKUP;

ALTER TABLESPACE WCRSYS_TS BEGIN BACKUP;
HOST cp /u01/oracle/infra904/oradata/asdb/wcrsys01.dbf /u03/oracle/backup/.      
ALTER TABLESPACE WCRSYS_TS END BACKUP;

- Backup the Control File

ALTER DATABASE BACKUP CONTROLFILE TO '/u03/oracle/backup/' REUSE;

- Create a text version of the Control File

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

- Backup the Archive logs  First Switch log files

ALTER SYSTEM SWITCH LOGFILE

- Stop Logging

ALTER SYSTEM ARCHIVE LOG STOP;
HOST cp /u01/oracle/infra904/dbs/arch/*.dbf /u03/oracle/backup/.
ALTER SYSTEM ARCHIVE LOG START;

- Backup init.ora and SPFILE

HOST cp /u01/oracle/infra904/dbs/initasdb.ora /u03/oracle/backup/.
HOST cp /u01/oracle/infra904/dbs/spfileasdb.ora /u03/oracle/backup/.

This file is very basic and is used to illustrate the method of creating a hot backup.  There is no error checking of logging.  Notice that if I add an additional data file to a tablespace I will have to manually add it to my backup script.  A production hot backup script should obtains current information on data files and tablespaces from the database at execution time.  Likewise, it should create a log file of the actions performed so that the administrator can insure that he backup was error free. 

While creating a hot backup is more complicated that a cold backup, recovery is pretty much the same.  Recover the missing or corrupted data file and start the database.  The database will determine what archive logs need to be applied and will roll the database forward until the data file is recovered?provided the archive logs are available. 

This is an excerpt from "Oracle 10g Application Server Administration Handbook" by Don Burleson and John Garmany.