Back to Basics – USER MANAGED BACKUPs part 1

In the past few weeks I have received many questions regarding user managed backups, and due to this, I decided to write a little regarding this topic.

User managed backups are basically all backups you can make without the use of RMAN and no automatic metadata record of the backup is generated anywhere in the database, therefore you must keep records of what you backed up and where all the time, also this method allows a DBA to make consistent backups of a whole database (cold backup), partial backups of a database making one or more tablespaces offline (inconsistent backup) or even online backups (inconsistent backup) can be performed as  well if your database is running on ARCHIVELOG mode. The last option, allows you to perform user managed backups without affecting the availability of your database to the business, very useful when your database needs to be available 24x7x365. RMAN was introduced in version 8.0 and it’s not compatible with prior releases of the database, and it will make most of your work when executing backups or any recovery process, consequently making your life a lot more easy and your database safer.

Note: A database is only in a consistent state after being SHUTDOWN in a consistent way (using SHUTDOWN [NORMAL/IMMEDIATE/TRANSACTIONAL).

Understanding the basics involving a manual backup and recovery will help you to easily understand what is going on in the background of your database when using RMAN and it will also help you to compare and easily understand all benefits of using RMAN against any other backup method when working with Oracle.

Before you start a user managed backup, you will need to know first:

1. Where all your datafiles are located, and their names;
2. Where your archivelogs are located;
3. And finally where your controlfile are located.

Tip: When doing user managed backups, never backup online redo log files, because these files contain the end of backup marker and would cause corruption if used in a recovery process.

You can easily collect all information necessary with the following commands:

· To select the datafiles information you can use the following SQL*Plus command:

SQL> SELECT name FROM v$datafile;

Or if you prefer to see the tablespaces and all associated datafiles, you can use the following SQL*Plus command:

SQL> SELECT a.name tablespace, b.name datafile

2 FROM v$tablespace a, v$datafile b

3 WHERE a.ts# = b.ts#

4 ORDER BY a.name;

· To check where your archive logs are being generated, you should use the following command:

SQL> SELECT destination

2 FROM v$archive_dest;

· Use the following command to see the name and location of your current control files:

SQL> SELECT name

2 FROM v$controlfile;

Tip: Always remember that the control files plays a crucial role in the database restore and recovery process, and you only need to make a backup of only one copy of a multiplexed control file.

Also, do not forget to include a copy of your PFILE or SPFILE (these files are generally found in $ORACLE_HOME/dbs) when making a user managed backup.

Now that you have all the information you need to perform a user managed backup, let’s take a close look in some of the most common user managed backup and recovery options available.

Cold Backup

As I mentioned before, cold backups are the only way possible to a DBA to perform a consistent backup of a database independent of the mode your database is running (ARCHIVELOG or NOARCHIVELOG).

Note: If your database is running on NOARCHIVELOG mode, all our backlups should be made using this method to ensure that your database is always in a consistent mode. If your database is on ARCHIVELOG mode, than you will be able to perform an additional recovery process to a more current point in time applying all ARCHIVELOG files generated after your consistent backup was made.

You can easily perform a manual backup of your database with the database down just following these simple steps:

1. If your database is OPEN, than SHUTDOWN your database completely in a consistent mode (use SHUTDOWN [NORMAL/IMMEDIATE/TRANSACTIONAL] only), this will ensure that all database files headers are consistent to the same SCN;
2. Backup all database datafiles, control files and the PFILE or SPFILE (copying them to a stage area at operating system level);

$ cp $ORACLE_BASE/oradata/cdb1/*.dbf /stage/backup

$ cp $ORACLE_BASE/oradata/cdb1/control01.ctl /stage/backup

$ cp $ORACLE_HOME/dbs/spfilecdb1.ora /stage/backup

3.    Restart the database.

4.    Archive all unarchived redo logs so any redo required to recover the tablespace is archived and executes a backup of all archivelog files.

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

$ cp $ORACLE_BASE/fast_recovery_area/*.arc /stage/backup

Tip: Always backup your archive log files generated between cold backups, this will ensure that you will not lose data when a recovery of your database is required.

Offline Backup

If what you need to do is to perform an offline backup of one or more tablespace, you should first notice that you cannot take offline the SYSTEM tablespace or a tablespace with any active UNDO segments, also when using this method always take in consideration first if the tablespace is completely self contained before perform this type of backup, in other words, you should first check if any logical or physical dependencies between objects exists, as per example, if any index related to any table in the tablespace that will become offline is stored in a different tablespace, in such case both tablespaces (DATA and INDEX) should be taken offline and backed up together.

Tip: Never perform Offline Backups of your database if it’s running on NOARCHIVELOG mode.

You can easily check if a tablespace is self contained using the very useful DBMS_TTS.TRANSPORT_SET_CHECK procedure (that is part of the DBMS_TTS package), and the view TRANSPORT_SET_VIOLATIONS as per the example bellow:

SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK(‘example’, TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM transport_set_violations;

no rows selected

SQL>

The example above checked if the tablespace EXAMPLE is self contained, you can also use the same statement to check more than one tablespace at the same time just adding all tablesapces separated by “comma”.

SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK(‘example,example2’, TRUE);

If the query to the view TRANSPORT_SET_VIOLATIONS returns no rows, than your tablespace is self contained.

Note: If not connected as SYS to the database, you must have been granted the EXECUTE_CATALOG_ROLE role to be able to execute this procedure.

Now that you know all guidelines when backing up offline tablespaces, let’s see how it can be done:

1. Identify the tablespace’s datafiles by quering the DBA_DATA_FILES view;

SQL> SELECT tablespace_name, file_name

2 FROM sys.dba_data_files

3 WHERE tablespace_name = ‘EXAMPLE’;

2.   Take the tablespace offline using the NORMAL priority if possible (This guarantees that no recovery will be necessary when bringing the tablespace online later);

SQL> ALTER TABLESPACE example OFFLINE NORMAL;

3.   Backup all datafiles related to the now offline tablespace via OS;

$ cp $ORACLE_BASE/oradata/cdb1/pdb1/example_01.dbf /stage/backup

4.   Bring the tableaspace online;

SQL> ALTER TABLESPACE example ONLINE;

5.   Archive all unarchived redo logs so any redo required to recover the tablespace is archived and executes a backup of all archivelog files.

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

$ cp $ORACLE_BASE/fast_recovery_area/*.arc /stage/backup

Note: Taking a tablespace offline using the TEMPORARY or IMMEDIATE priority, will always require a tablespace recovery at the moment to bring the tablespace online.

In the next part, I will talk about Hot Backups (inconsistent) and much more.

Cheers,

Francisco Munoz Alvarez