Performing Oracle Online Backups


In this tutorial we will be performing an online database backup. As mentioned in the text, your database will need to be in ARCHIVELOG mode to successfully execute this backup.
1. We assume your database is already running in ARCHIVELOG mode. If it’s not, put your database in ARCHIVELOG mode.
2. As with the previous offline/cold backup, you need to know what datafiles need to be backed up.
SQL> Select file_name from dba_data_files;
FILE_NAME
——————————————–
C:\ORACLE\ORADATA\ORCL\USERS01.DBF
C:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF
C:\ORACLE\ORADATA\ORCL\SYSAUX01.DBF
C:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF
C:\ORACLE\ORADATA\ORCL\REVEAL_DATA_01.DBF
C:\ORACLE\ORADATA\ORCL\REVEAL_INDEX_01.DBF
C:\ORACLE\ORADATA\ORCL\USERS02.DBF
7 rows selected.


3. Having determined which datafiles need to be backed up, you need to know where the archived redo logs are being copied to.
SQL> show parameter log_archive_dest_1
NAME TYPE VALUE
————————– ———– ——————————
log_archive_dest_1 string location=c:\oracle\arch\orcl

 

4. You should note the current online redo log sequence number at this point. You will need this, plus all log sequences generated during the backup, in order to be able to perform your recovery. You can get this number from the v$log view:
SQL> select group#, sequence#, status from v$log;
GROUP# SEQUENCE# STATUS
———- ———- —————-
1 13 INACTIVE
2 14 CURRENT
3 12 INACTIVE
In this case, you see that you will need all log files from sequence number 14 on in order to restore the backup you are preparing to use.
5. You now need to put the database in hot backup mode. Oracle Database 11g provides the command alter database begin backup for this purpose. You can also back up specific tablespaces with the alter tablespace begin backup command.
SQL> alter database begin backup;
Database altered.
— ALTERNATE – Run this for each tablespace to be backed up.
— alter tablespace users begin backup;
6. The database datafiles are now ready to be backed up. You will copy the files to a directory that you will create called c:\backup\orcl\backup2.
SQL> host mkdir c:\backup\orcl\backup2
7. Now copy all the database datafiles to this directory. In this case, all the files are inthe directory c:\oracle\oradata\orcl, and the filenames all end with an extension of .DBF, so the command to copy them is pretty easy. Once you have started the datafile copy, go get something to eat. It might take a while.
SQL> host copy c:\oracle\oradata\orcl\*.dbf c:\backup\orcl\backup2
c:\oracle\oradata\orcl\REVEAL_DATA_01.DBF
c:\oracle\oradata\orcl\REVEAL_INDEX_01.DBF
c:\oracle\oradata\orcl\SYSAUX01.DBF
c:\oracle\oradata\orcl\SYSTEM01.DBF
c:\oracle\oradata\orcl\TEMP01.DBF
c:\oracle\oradata\orcl\UNDOTBS01.DBF
c:\oracle\oradata\orcl\USERS01.DBF
c:\oracle\oradata\orcl\USERS02.DBF
8 file(s) copied.

8. Having patiently waited for the backup to complete, you now need to take the database out of hot backup mode. Oracle Database 11g provides the command alter database end backup for this purpose. You can also back up specific tablespaces with the alter tablespace end backup command.
SQL> alter database end backup;
Database altered.
— ALTERNATE – Run this for each tablespace to be backed up.
— alter tablespace users end backup;
9. Next you need to determine the current log file sequence number. You will need the earlier log file that you identified and all log files generated during the backup up to the current log file in order to be able to restore this backup. The query is the same against V$LOG that you saw earlier:
SQL> select group#, sequence#, status from v$log;
GROUP# SEQUENCE# STATUS
———- ———- —————-
1 13 INACTIVE
2 14 ACTIVE
3 15 CURRENT
In this example, you can see that during the backup you had a log file switch, from sequence number 14 to sequence number 15. You see that log sequence 15 is the current sequence number. You know now that you will need to back up the logs with sequence numbers 14 and 15 in order to be able to restore this backup.
10. You now need to force a log switch so the log with sequence number 15 (the current online redo log sequence number) will be archived. To do this, you issue the alter system switch logfile command. This will cause Oracle to switch to the next log file (sequence 16), and the current archive log (Sequence 15) will be copied to thearchive-log directory by the ARCn processes.
SQL> Alter system switch logfile;
System altered.
11. Having switched log files, you need to wait for ARCH to complete copying the last log file to the archive-log directory. You can check for this completion by looking at the V$ARCHIVED_LOG view.
SQL> Select sequence#, archived, status from v$archived_log
2 Where sequence# between 14 and 15;
SEQUENCE# ARC S
———- — –

14 YES A
15 YES A
Here you see that the logs with sequence numbers 14 and 15 (already identified as critical to restoring this backup) have been archived successfully. The ARCHIVED column indicates this with the use of the YES value.
12. Now back up all archived redo logs, ensuring that all logs with numbers between sequence x and sequence y are backed up. You will simply copy all archived redo logs from the directory identified in step 3 (c:\oracle\arch\orcl) to your backup directory.
SQL> Host copy c:\oracle\arch\orcl\*.* c:\backup\orcl\backup2
c:\oracle\arch\orcl\ORCL_658485967_1_10.ARC
c:\oracle\arch\orcl\ORCL_658485967_1_11.ARC
c:\oracle\arch\orcl\ORCL_658485967_1_12.ARC
c:\oracle\arch\orcl\ORCL_658485967_1_13.ARC
c:\oracle\arch\orcl\ORCL_658485967_1_14.ARC ← Log sequence 14
c:\oracle\arch\orcl\ORCL_658485967_1_15.ARC ← Log sequence 15
c:\oracle\arch\orcl\ORCL_658485967_1_2.ARC
c:\oracle\arch\orcl\ORCL_658485967_1_3.ARC
c:\oracle\arch\orcl\ORCL_658485967_1_4.ARC
c:\oracle\arch\orcl\ORCL_658485967_1_5.ARC
c:\oracle\arch\orcl\ORCL_658485967_1_6.ARC
c:\oracle\arch\orcl\ORCL_658485967_1_7.ARC
c:\oracle\arch\orcl\ORCL_658485967_1_8.ARC
c:\oracle\arch\orcl\ORCL_658485967_1_9.ARC
14 file(s) copied.
You can tell that the logs with sequence numbers 14 and 15 were backed up since you know that the log sequence number is part of the filename (it’s the number right before the extension). We also marked them for you in the output just because we are nice guys. After copying the archived redo logs to the backup location, you can delete the source location if you want to save space. Once the backup of the archived redo logs is complete, your database backup is done.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s