Performing Oracle Offline Backups


In this tutorial you will be executing an offline backup of your database. Follow these steps to back up a database with an offline backup:
1. First you need to determine which files to back up. You will need to know the location of the datafiles, the control file, and the online redo logs. You use the FILE_NAME column of the DBA_DATA_FILES view to find the datafiles first.

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.

2. You use the MEMBER column in the V$LOGFILE view to find the location of all the online redo logs:
SQL> select member from v$logfile;
MEMBER
———————————–
C:\ORACLE\ORADATA\ORCL\REDO03A.LOG
C:\ORACLE\ORADATA\ORCL\REDO02.LOG
C:\ORACLE\ORADATA\ORCL\REDO01.LOG
C:\ORACLE\ORADATA\ORCL\REDO03B.LOG
3. You use the NAME column in V$CONTROLFILE to find the control files:
SQL> select name from v$controlfile;
NAME
————————————-
C:\ORACLE\ORADATA\ORCL\CONTROL01.CTL
C:\ORACLE\ORADATA\ORCL\CONTROL02.CTL
C:\ORACLE\ORADATA\ORCL\CONTROL03.CTL
C:\ORACLE\ORADATA\ORCL\CONTROL04.CTL
4. Having found all the files you will need for your backup, create a directory to back up all your files to. Of course, you might back your files up to tape or a thumb drive or some such thing. In this case, you will just copy the files to a directory that you will create called c:\backup\orcl\backup1.
SQL> host mkdir c:\backup\orcl\backup1
5. Having created your backup directory, you need to shut down the database with the shutdown immediate command before you start your backup.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
6. Now copy the files that you found in steps 1, 2, and 3 to the backup directory created in step 4. Notice that all the files in this example reside in one directory, c:\oracle\oradata\orcl, so the copy command is quite easy. Backups can take a while, so be patient. It’s probably a good time to go grab a cool refreshment from the vending machine!
C:\>copy c:\oracle\oradata\orcl\*.* c:\backup\orcl\backup1
c:\oracle\oradata\orcl\CONTROL01.CTL

c:\oracle\oradata\orcl\CONTROL02.CTL
c:\oracle\oradata\orcl\CONTROL03.CTL
c:\oracle\oradata\orcl\CONTROL04.CTL
c:\oracle\oradata\orcl\REDO01.LOG
c:\oracle\oradata\orcl\REDO02.LOG
c:\oracle\oradata\orcl\REDO03A.LOG
c:\oracle\oradata\orcl\REDO03B.LOG
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
16 file(s) copied.
7. Once the copy is complete, verify that the backup is where you expect it to be:
C:\>dir c:\backup\orcl\backup1
Volume in drive C has no label.
Volume Serial Number is 08DE-E1AB
Directory of c:\backup\orcl\backup1
08/02/2008 02:16 PM <DIR> .
08/02/2008 02:16 PM <DIR> ..
08/02/2008 02:02 PM 10,174,464 CONTROL01.CTL
08/02/2008 02:02 PM 10,174,464 CONTROL02.CTL
08/02/2008 02:02 PM 10,174,464 CONTROL03.CTL
08/02/2008 02:02 PM 10,174,464 CONTROL04.CTL
08/02/2008 02:02 PM 52,429,312 REDO01.LOG
08/02/2008 02:02 PM 52,429,312 REDO02.LOG
08/02/2008 02:02 PM 104,858,112 REDO03A.LOG
08/02/2008 02:02 PM 104,858,112 REDO03B.LOG
08/02/2008 02:02 PM 15,736,832 REVEAL_DATA_01.DBF
08/02/2008 02:02 PM 15,736,832 REVEAL_INDEX_01.DBF
08/02/2008 02:02 PM 851,386,368 SYSAUX01.DBF
08/02/2008 02:02 PM 754,982,912 SYSTEM01.DBF
08/02/2008 02:02 PM 50,339,840 TEMP01.DBF
08/02/2008 02:02 PM 519,053,312 UNDOTBS01.DBF

08/02/2008 02:02 PM 581,246,976 USERS01.DBF
08/02/2008 02:02 PM 10,493,952 USERS02.DBF
16 File(s) 3,154,249,728 bytes
2 Dir(s) 13,330,685,952 bytes free
8. Start the database. Your backup is complete!
SQL> startup
ORACLE instance started.
Total System Global Area 418484224 bytes
Fixed Size 1333592 bytes
Variable Size 348128936 bytes
Database Buffers 62914560 bytes
Redo Buffers 6107136 bytes
Database mounted.
Database opened.

 

 

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 )

Connecting to %s