Oracle Database HOT Backup STEPS

Note:  Proofread any scripts before using. Always try scripts on a test instance first. This Blog is not responsible for any damage.

User managed backup means you take backup without any oracle feature. Suppose if you take backup by Operating system then it is called user managed backup. And the term hot backup means taking your backup whenever your database is at open state.

Overview of what internal activities is performed by oracle while database/tablespaces into backup mode

1. Oracle checkpoints the tablespace, flushing all changes from shared memory to disk.
2. The SCN markes for each datafile in that tablespace are “frozen” at their current values. Even though further updates will be sent to the datafiles, the SCN markers will not be updated until the tablespace is taken out of backup mode.
3. Oracle switches to logging full images of changed database blocks to the redologs. Instead of recording how it changed a particular block the change vector), it will log the entire image of the block after he change. This is why the redologs grow at a much faster rate while hot backups are going on.

After this, your backup program works happily through this datafile, backing it up block by block. Since the file is being updated as you are reading it, it may read blocks just before they’re changed, after they’re changed, or even while they’re changing. Suppose that your filesystem block size is 4 KB, and Oracle’s block size is 8 KB. Your backup program will be reading in increments of 4 KB. It could back up the first 4 KB of an 8-KB Oracle data block before a change is made to that block, then back up the last 4 KB of that file after a change has been made. This results in what Oracle calls a “split block”. However, when your backup program reaches the point of the datafile that contains the SCN, it will back up that block the way it looked when the backup began, since that block is frozen. Once you take the tablespace out of backup mode, the SCN marker is advanced to the current value, and Oracle switches back to logging change vectors instead of full images of changed blocks.

It is a good practice but not a mandatory one do a manual switch before begin and then end back up and that is because archives generated only during the two manual switches can only be backed up, so that you don’t have to back up all the archives right from the start.

To take full database backup follow the following steps.

One can issue BEGIN BACKUP .. SCP serially (Take one tablespace in begin backup mode and then copy the associated datafiles and make the tablespace out of backup mode) or can do it parallelly(Take all tablespaces in begin backup mode and then copy the associated datafiles of all tabelspaces and then make the tablespace out of backup mode).
BRIEF STEPS - How to take a Hot backup of Oracle database

    01: Put the db in archive log mode
    02: Set the db_sid to correct one
    03: Login to sqlplus
    04: Verify the name of the db that you are connected to
         sql> select name from v$database;
    05: Check if the db is in archive log mode
         sql> select log_mode from v$database;
       (or)
         sql> archive log list;
    06: Find where on disk oracle writes archive log when it is in archive log mode
         sql> show parameter log_archive_dest_1;
    07: Tablespace must be in hot backup mode
        a) check the status
         sql> SELECT t.name, d.file# as, b.status FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b WHERE d.TS#=t.TS# AND b.FILE#=d.FILE#;
        b) if status found as not active, then change to hot backup mode. We cannot put the db into hot backup mode, unless it is in archivelog mode
         sql> alter database begin backup;
        c) check the status
         sql> SELECT t.name, d.file# as, b.status FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b WHERE d.TS#=t.TS# AND b.FILE#=d.FILE#;
    08: Now we can only COPY DBF FILES
         $ cp -p *dbf (or) copy *.DBF
    09: Need to take the db out to hot backup mode
         sql> alter database end backup;
    10: Need to make another archive log switch
         sql> alter system archive log current;
    11: Need to copy control files now, need to do a binary bckup
         sql> alter database backup controlfile to '/ora/<database_name>/backup/controlbackup';
    12: Make another archive log switch 
         sql> alter system archive log current;
    13: Backup all the archive logs to a new location

DETAILED EXPLANATION -

1) Before proceed remember you can take online/hot backup whenever your database is in Archivelog mode. If your database run on noarchivelog mode then you must take consistent backup that is after cleanly shutdown. In order to determine the archival mode, issue the query,

     sql> SELECT LOG_MODE FROM V$DATABASE;
      
           LOG_MODE
           ---------------------
           ARCHIVELOG

If you see ARCHIVELOG then you can proceed further. In order to take backup while you are in noarhivelog mode follow the steps:
a. Shutdown the database
b. Copy all required files either using scpor cp command to backup location
c. startup database

(or) Change the database to archivelog mode and proceed with HOT Backup. To change the database to archivelog mode follow below steps:
      
      sql> archive log list;

Find where on disk oracle writes archive log when it is in archive log mode
      sql> show parameter log_archive_dest_1;

If the value is found to be 0, that means no values will be recorded, so we need to change it
      sql> alter system set log_archive_dest_1='LOCATION=/ora/<database_name/arch/oradata/<database_name>/' scope=spfile;

Shutdown database
      sql> shutdown immediate; < archive mode can only be changed when the database is in mount state >

Startup the db in mount mode
      sql> startup mount;
       ( 3 startup types : nomount - just starts the instance,
                             mount - locates the control files and open up according to the values,
                              open - finds the datafiles from the control files and opens up the db )

Put the db in archive log mode
       sql> alter database archivelog;

Open the database
       sql> alter database open;

Check the status of the db
       sql> select log_mode from v$database;
(or) sql> archive log list;

Create a directory for archived log, check if its empty, if empty we need to switch
       sql> alter system archive log current;

2) Determine the files that you need to take backup.

Whenever you decide to take your database backup then take backup of data files , online redo log files ,control files, spfile.

In order to decide which files you need to backup issue the following query.

SELECT NAME "File Need Backup" FROM V$DATAFILE
UNION ALL
SELECT MEMBER FROM V$LOGFILE
UNION ALL
SELECT NAME FROM V$CONTROLFILE
UNION ALL
SELECT VALUE FROM V$PARAMETER WHERE NAME='spfile';

So after running the above query it shows the number of files required to be backed up.

3) Take the tablespace in backup mode rather than offline and read-only tablespace. In case of offline and read only tablespace you do not have to place the tablespace in backup mode because the database is not permitting changes to the datafiles.

You can check the status, tablespace_name and it’s associated data file name with the following query,

SET LINESIZE 132 PAGESIZE 60
COLUMN FILE_NAME FORMAT A60

SELECT t.STATUS,t.TABLESPACE_NAME "Tablespace", f.FILE_NAME "Datafile"
FROM DBA_TABLESPACES t, DBA_DATA_FILES f
WHERE t.TABLESPACE_NAME = f.TABLESPACE_NAME;
ORDER BY t.NAME;

You can easily make a script of taking the online tablespace in backup mode by following query.
 
SELECT 'ALTER TABLESPACE ' ||TABLESPACE_NAME ||' BEGIN BACKUP;' "Script" FROM DBA_TABLESPACES WHERE STATUS NOT IN ('READ ONLY','OFFLINE');

Alternatively, you can issue
 
ALTER DATABASE BEGIN BACKUP;

4) You can check whether backup mode now active or not by issuing following query,

SELECT t.name AS "TB_NAME", d.file# as "DF#", d.name AS "DF_NAME", b.status
FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b
WHERE d.TS#=t.TS#
AND b.FILE#=d.FILE#
AND b.STATUS='ACTIVE';

5)Copy the datafile to backup location.

After marking a tablespace to backup mode take backup/copy of the associated datafiles. Here you can also make a script in order to copy datafiles to another location.

SELECT 'host scp '|| FILE_NAME || ' &backup_location ' "Backup Command" FROM DBA_DATA_FILES;

Also you can backup network files. Do a recursive search for *.ora starting in your Oracle home directory and under it.

In order to make script for to copy data files for those tablespace which are only in backup mode then issue,

SELECT 'host scp '|| d.name ||' &backup_location' FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b
WHERE d.TS#=t.TS#
AND b.FILE#=d.FILE#
AND b.STATUS='ACTIVE';

Run the script that you genereted.

On windows or other operating system you can use graphical browser to copy or other associated copy command.

6)Once the copy is finished make the tablespace out of backup mode. here is a script like,

SELECT 'ALTER TABLESPACE ' ||t.name ||' END BACKUP;' "End Backup Script"
FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b
WHERE d.TS#=t.TS#
AND b.FILE#=d.FILE#
AND b.STATUS='ACTIVE';

If taken Database in backup mode then issue

ALTER DATABASE END BACKUP;