Oracle 11g Logo

Oracle 11g Manual Online Hot Backup

In this article steps by step approach to perform online hot backup with database in open mode.

Note: It is assumed that your database already in ARCHIVELOG mode, If not than follow my one of the article to convert your database in archivelog mode.

For manual online hot backup, follow the steps mentioned below. I have simulated mentioned scenario on my test database, i.e. RTS.

Step 1>>

Verify your database is in ARCHIVELOG mode OR not.

 SQL> archive log list;
 Database log mode Archive Mode
 Automatic archival Enabled
 Archive destination USE_DB_RECOVERY_FILE_DEST
 Oldest online log sequence 88
 Next log sequence to archive 90
 Current log sequence 90
 Database is in ARCHIVELOG mode.

Step 2>>

List down the all oracle data files which you have to backup, with the help of following SQL.

 SQL> select file_id,file_name from dba_data_files;
 FILE_ID    FILE_NAME
 ---------  ----------------------------------------------
 4          /u01/app/oracle/oradata/RTS/users01.dbf
 3          /u01/app/oracle/oradata/RTS/undotbs01.dbf
 2          /u01/app/oracle/oradata/RTS/sysaux01.dbf
 1          /u01/app/oracle/oradata/RTS/system01.dbf

Step 3>>

We need to know current online log sequence number at this point, Plus all log sequence generated during backup. Because we need these archive logs in order to restore database.

 SQL> select group#, sequence#, status from v$log;
 GROUP#     SEQUENCE#  STATUS
 ---------- ---------- ----------------
 1          88         INACTIVE
 2          89         INACTIVE
 3          90         CURRENT

In my case, CURRENT log sequence number : 90, GROUP# : 3

Step 4>>

To start with hot backup, all the online datafile status should be in NOT ACTIVE mode in V$BACKUP dictionary view, that displays the backup status of all online datafiles.

select * from v$backup;
     FILE# STATUS             CHANGE#    TIME
---------- ------------------ ---------- ---------
         1 NOT ACTIVE         0
         2 NOT ACTIVE         0
         3 NOT ACTIVE         0
         4 NOT ACTIVE         0

Put your database in hot backup mode with the help of following SQL:

 SQL> alter database begin backup;
 Database altered.

Verify status of all online datafiles after begin backup mode:

SQL> Select * from v$backup;
     FILE# STATUS             CHANGE#    TIME
---------- ------------------ ---------- ---------
         1 ACTIVE             1114633    12-JUL-17
         2 ACTIVE             1114633    12-JUL-17
         3 ACTIVE             1114633    12-JUL-17
         4 ACTIVE             1114633    12-JUL-17

Step 5>>

Create backup directory in order to copy backup files to backup directory.

 [oracle@PR ~]$ mkdir -p /u01/bkup/manual_online_hot
 [oracle@PR ~]$ cd /u01/bkup/manual_online_hot
 [oracle@PR manual_online_hot]$ pwd
 /u01/bkup/manual_online_hot

It is recommended to backup your pfile or spfile by coping it to backup location.

[oracle@PR ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/dbs/
[oracle@PR dbs]$ cp initRTS.ora /u01/bkup/manual_online_hot/
[oracle@PR dbs]$ cp spfileRTS.ora /u01/bkup/manual_online_hot/

Step 6>>

Copy all the files (i.e. .DBF) from the database directory “/u01/app/oracle/oradata/RTS” to backup directory “/u01/bkup/manual_online_hot”.

 [oracle@PR ~]$ cd /u01/app/oracle/oradata/RTS
 [oracle@PR RTS]$ cp *.dbf /u01/bkup/manual_online_hot

Step 7>>

Take your database out from hot backup mode:

 SQL> alter database end backup;
 Database altered.

Step 8>>

Verify current log sequence number with the help of same SQL mentioned in step-3.

 SQL> select group#, sequence#, status from v$log;
 GROUP#     SEQUENCE#  STATUS
 ---------- ---------- ----------------
 1          88         INACTIVE
 2          89         INACTIVE
 3          90         CURRENT

We need the earlier log file that we identified in step-3 & all log files generated during the database backup upto the current log file.

Note: In our case, As you know this is an test environment not production database. current log file before and after backup remain same, i.e. 90. But in case of production system, it may vary and generate more log files during backup.

Step 9>>

Forcefully we need to switch the logfile in order to archive current log sequence number. i.e. 90.

 SQL> alter system switch logfile;
 System altered.
 SQL> select group#, sequence#, status from v$log;
 GROUP#     SEQUENCE#  STATUS
 ---------- ---------- ----------------
 1          91         CURRENT
 2          89         INACTIVE
 3          90         ACTIVE

Now, Current log sequence number is 91, and log sequence number will be archive to archive log location. In my case it is FRA: Flash Recovery Area.

Step 10>>

Verify log sequence number have been archived to archive log location with the help of following SQL:

SQL> select SEQUENCE#,ARCHIVED,STATUS from v$archived_log where SEQUENCE#=90;
 SEQUENCE#  ARC S
 ---------- --- -
 90         YES A

Archive log sequence 90 has archived. Some time we need to wait for ARCH background process to complete copy the last online redo log file to the archive log directory.

Step 11>>

Now, Copy all archived logs (i.e. log sequence number noted in step-3 and all archived logs generated during backup) from archived log location (i.e. FRA) to the backup location.
In our case it was only log sequence 90.

[oracle@PR ~]$ cd /home/oracle/archdir
 [oracle@PR archdir]$ cp 1_90_910140016.dbf /u01/bkup/manual_online_hot
 OR
 [oracle@PR archdir]$ cp * /u01/bkup/manual_online_hot

Step 12>>

Backup the database control file:

SQL> alter database backup controlfile to '/u01/bkup/manual_online_hot/hot_bkup_control.ctl';
Database altered.

Cross verify all backup files are in place(i.e. datafiles, controlfile and archive log files @backup location in order restore database.

Congratulations!! Manual Online hot backup successfully completed.

To restore and recover above online hot backup, kindly visit my next article on Restore Oracle hot backup

Stay Tune. 🙂