While Recovery Manager (RMAN) is highly recommended for database backup and recovery operations your organization may require you to perform backups using a custom process or you might have an interest in knowing what is required to perform a user managed backup.
This document will detail the steps to perform a user managed consistent database backup commonly referred to as a cold backup.
To perform a user managed consistent back you need to copy all of the data files including undo files, the control files, the SPFILE and any archive logs. You do not need to copy the online redo logs or the temporary files.
Finding the names and locations of the files can be found querying the database. The steps performed in this document were done on an Oracle Enterprise Linux system and the same on Windows or other UNIX variants.
Determine if the database is in archive log mode and the archive log location
While logged into the database with SYSDBA privileges issue archive log list.
SQL> archive log list; Database log mode No Archive Mode Automatic archival Disabled Archive destination /u01/app/oracle/oradata/odlin11g/arch Oldest online log sequence 72 Current log sequence 74 SQL>
The archive log mode along with the destination in which archive logs are written is displayed. In this case the database is not in archive log mode so there will be no archive logs to back up.
Determine the location of the SPFILE
Find the location of the SPFILE by querying V$PARAMETER.
SQL> select value from v$parameter where name = 'spfile'; VALUE -------------------------------------------------------------------------------- /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileodlin11g.ora SQL>
Determine the location of the control files
Find the names and locations of the control files by querying V$CONTROLFILE.
SQL> select name from v$controlfile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/odlin11g/control01.ctl /u01/app/oracle/oradata/odlin11g/control02.ctl SQL>
Determine the location of the data files
Find the names and locations of the data files by querying V$DATAFILE.
SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/odlin11g/system01.dbf /u01/app/oracle/oradata/odlin11g/sysaux01.dbf /u01/app/oracle/oradata/odlin11g/undotbs01.dbf /u01/app/oracle/oradata/odlin11g/users01.dbf SQL>
Build a script to copy the files
Now that we have the name and locations all we need to do is build a script to copy the files after we shutdown the database. Using SQL we can generate the OS copy commands to copy the files to the backup location.
SQL> select 'cp '||name||' /u01/app/oracle/oradata/odlin11g/backup' 2 from v$datafile 3 union 4 select 'cp '||name||' /u01/app/oracle/oradata/odlin11g/backup' 5 from v$controlfile 6 union 7 select 'cp '||value||' /u01/app/oracle/oradata/odlin11g/backup' 8 from v$parameter 9 where name = 'spfile'; 'CP'||NAME||'/U01/APP/ORACLE/ORADATA/ODLIN11G/BACKUP' ------------------------------------------------------------------------------------------------------------------------ cp /u01/app/oracle/oradata/odlin11g/control01.ctl /u01/app/oracle/oradata/odlin11g/backup cp /u01/app/oracle/oradata/odlin11g/control02.ctl /u01/app/oracle/oradata/odlin11g/backup cp /u01/app/oracle/oradata/odlin11g/sysaux01.dbf /u01/app/oracle/oradata/odlin11g/backup cp /u01/app/oracle/oradata/odlin11g/system01.dbf /u01/app/oracle/oradata/odlin11g/backup cp /u01/app/oracle/oradata/odlin11g/undotbs01.dbf /u01/app/oracle/oradata/odlin11g/backup cp /u01/app/oracle/oradata/odlin11g/users01.dbf /u01/app/oracle/oradata/odlin11g/backup cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileodlin11g.ora /u01/app/oracle/oradata/odlin11g/backup 7 rows selected. SQL>
You should consider spooling the results to a file. Now that we have a script to copy the files all that is left is to shutdown the database.
Shutdown the database and backup up the database
The database needs to be cleanly shutdown (IMMEDIATE, NORMAL or TRANSACTIONAL.)
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> host [oracle@odlinux ~]$
With the database down we can copy the files to the backup location.
[oracle@odlinux ~]$ cp /u01/app/oracle/oradata/odlin11g/control01.ctl /u01/app/oracle/oradata/odlin11g/backup [oracle@odlinux ~]$ cp /u01/app/oracle/oradata/odlin11g/control02.ctl /u01/app/oracle/oradata/odlin11g/backup [oracle@odlinux ~]$ cp /u01/app/oracle/oradata/odlin11g/sysaux01.dbf /u01/app/oracle/oradata/odlin11g/backup [oracle@odlinux ~]$ cp /u01/app/oracle/oradata/odlin11g/system01.dbf /u01/app/oracle/oradata/odlin11g/backup [oracle@odlinux ~]$ cp /u01/app/oracle/oradata/odlin11g/undotbs01.dbf /u01/app/oracle/oradata/odlin11g/backup [oracle@odlinux ~]$ cp /u01/app/oracle/oradata/odlin11g/users01.dbf /u01/app/oracle/oradata/odlin11g/backup [oracle@odlinux ~]$ cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileodlin11g.ora /u01/app/oracle/oradata/odlin11g/backup [oracle@odlinux ~]$
Perform an ls of the backup location to verify the copy.
[oracle@odlinux ~]$ ls /u01/app/oracle/oradata/odlin11g/backup/ control01.ctl control02.ctl spfileodlin11g.ora sysaux01.dbf system01.dbf undotbs01.dbf users01.dbf [oracle@odlinux ~]$
All that is left is to start the database back up.
SQL> startup ORACLE instance started. Total System Global Area 1653518336 bytes Fixed Size 2213896 bytes Variable Size 989857784 bytes Database Buffers 654311424 bytes Redo Buffers 7135232 bytes Database mounted. Database opened. SQL>
You have just performed the steps to create user managed consistent backup of the database. But there is more few more steps to do iron out the rough spots. Building a script to automate the backup process as well a scheduling the backup are just two things to consider. Keep in mind if the structure of the database changes such as new data files, control files, archive log mode, location change.. etc, the script will need to be updated to reflect these changes.