Oracle 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 convert your database in archivelog mode.
For manual online hot backup, follow the steps mentioned below.
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/XYZ/users01.dbf
3 /u01/app/oracle/oradata/XYZ/undotbs01.dbf
2 /u01/app/oracle/oradata/XYZ/sysaux01.dbf
1 /u01/app/oracle/oradata/XYZ/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/bkp/online_bkp
[oracle@PR ~]$ cd /u01/bkp/online_bkp
[oracle@PR manual_online_hot]$ pwd
/u01/bkp/online_bkp
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 initXYZ.ora /u01/bkp/online_bkp/
[oracle@PR dbs]$ cp spfileXYZ.ora /u01/bkp/online_bkp/
Step 6>>
Copy all the files (i.e. .DBF) from the database directory “/u01/app/oracle/oradata/XYZ” to backup directory “/u01/bkp/online_bkp”.
[oracle@PR ~]$ cd /u01/app/oracle/oradata/XYZ
[oracle@PR XYZ]$ cp *.dbf /u01/bkp/online_bkp
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/bkp/online_bkp
OR
[oracle@PR archdir]$ cp * /u01/bkp/online_bkp
Step 12>>
Backup the database control file:
SQL> alter database backup controlfile to '/u01/bkp/online_bkp/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.
Restore Oracle hot backup
Consider any worst database scenario of your life, let say we got HDD corruption and some online datafile got corrupted and not accessible due to any reason. Now we have to restore database from hot backup, as follows:
Step 1>>
Copy back all the online datafile from backup location to the actual datafile location:
cp /u01/bkup/online_bkp/users01.dbf /u01/app/oracle/oradata/XYZ/users01.dbf
cp /u01/bkup/online_bkp/users01.dbf /u01/app/oracle/oradata/XYZ/undotbs01.dbf
cp /u01/bkup/online_bkp/users01.dbf /u01/app/oracle/oradata/XYZ/sysaux01.dbf
cp /u01/bkup/online_bkp/users01.dbf /u01/app/oracle/oradata/XYZ/system01.dbf
Step 2>>
Copy back control file to all the controlfile locations, refer controlfile locations from parameter file, i.e. initXYZ.ora file.
cp /u01/bkup/online_bkp/hot_bkup_control.ctl /u01/app/oracle/oradata/XYZ/control01.ctl
cp /u01/bkup/online_bkp/hot_bkup_control.ctl /u01/app/oracle/oradata/XYZ/control02.ctl
Step 3>>
Copy back pfile or spfile file to its location:
cp /u01/bkup/online_bkp/initXYZ.ora /u01/app/oracle/product/11.2.0/dbhome_1/dbs/
cp /u01/bkup/online_bkp/spfileXYZ.ora /u01/app/oracle/product/11.2.0/dbhome_1/dbs/
Step 4>>
Startup database in mount mode:
SQL> startup mount;
ORACLE instance started.
Total System Global Area 755769344 bytes
Fixed Size 2217184 bytes
Variable Size 478153504 bytes
Database Buffers 272629760 bytes
Redo Buffers 2768896 bytes
Database mounted.
Step 5>>
Now, recover database with the help of following command and specify log: AUTO
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 1117103 generated at 07/12/2017 04:39:47 needed for thread 1
ORA-00289: suggestion : /home/oracle/archdir/1_90_910140016.dbf
ORA-00280: change 1117103 for thread 1 is in sequence #90
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
Step 6>>
Open database with resetlogs:
SQL> alter database open resetlogs;
Database altered.
SQL>Select name,open_mode from v$database;
Name Open_mode
------- ---------------
XYZ READ WRITE
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 convert your database in archivelog mode.
For manual online hot backup, follow the steps mentioned below.
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/XYZ/users01.dbf
3 /u01/app/oracle/oradata/XYZ/undotbs01.dbf
2 /u01/app/oracle/oradata/XYZ/sysaux01.dbf
1 /u01/app/oracle/oradata/XYZ/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/bkp/online_bkp
[oracle@PR ~]$ cd /u01/bkp/online_bkp
[oracle@PR manual_online_hot]$ pwd
/u01/bkp/online_bkp
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 initXYZ.ora /u01/bkp/online_bkp/
[oracle@PR dbs]$ cp spfileXYZ.ora /u01/bkp/online_bkp/
Step 6>>
Copy all the files (i.e. .DBF) from the database directory “/u01/app/oracle/oradata/XYZ” to backup directory “/u01/bkp/online_bkp”.
[oracle@PR ~]$ cd /u01/app/oracle/oradata/XYZ
[oracle@PR XYZ]$ cp *.dbf /u01/bkp/online_bkp
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/bkp/online_bkp
OR
[oracle@PR archdir]$ cp * /u01/bkp/online_bkp
Step 12>>
Backup the database control file:
SQL> alter database backup controlfile to '/u01/bkp/online_bkp/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.
Restore Oracle hot backup
Consider any worst database scenario of your life, let say we got HDD corruption and some online datafile got corrupted and not accessible due to any reason. Now we have to restore database from hot backup, as follows:
Step 1>>
Copy back all the online datafile from backup location to the actual datafile location:
cp /u01/bkup/online_bkp/users01.dbf /u01/app/oracle/oradata/XYZ/users01.dbf
cp /u01/bkup/online_bkp/users01.dbf /u01/app/oracle/oradata/XYZ/undotbs01.dbf
cp /u01/bkup/online_bkp/users01.dbf /u01/app/oracle/oradata/XYZ/sysaux01.dbf
cp /u01/bkup/online_bkp/users01.dbf /u01/app/oracle/oradata/XYZ/system01.dbf
Step 2>>
Copy back control file to all the controlfile locations, refer controlfile locations from parameter file, i.e. initXYZ.ora file.
cp /u01/bkup/online_bkp/hot_bkup_control.ctl /u01/app/oracle/oradata/XYZ/control01.ctl
cp /u01/bkup/online_bkp/hot_bkup_control.ctl /u01/app/oracle/oradata/XYZ/control02.ctl
Step 3>>
Copy back pfile or spfile file to its location:
cp /u01/bkup/online_bkp/initXYZ.ora /u01/app/oracle/product/11.2.0/dbhome_1/dbs/
cp /u01/bkup/online_bkp/spfileXYZ.ora /u01/app/oracle/product/11.2.0/dbhome_1/dbs/
Step 4>>
Startup database in mount mode:
SQL> startup mount;
ORACLE instance started.
Total System Global Area 755769344 bytes
Fixed Size 2217184 bytes
Variable Size 478153504 bytes
Database Buffers 272629760 bytes
Redo Buffers 2768896 bytes
Database mounted.
Step 5>>
Now, recover database with the help of following command and specify log: AUTO
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 1117103 generated at 07/12/2017 04:39:47 needed for thread 1
ORA-00289: suggestion : /home/oracle/archdir/1_90_910140016.dbf
ORA-00280: change 1117103 for thread 1 is in sequence #90
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
Step 6>>
Open database with resetlogs:
SQL> alter database open resetlogs;
Database altered.
SQL>Select name,open_mode from v$database;
Name Open_mode
------- ---------------
XYZ READ WRITE
No comments:
Post a Comment