Section 16.3. Physical Backups Without rman

16.3. Physical Backups Without rman

Many Oracle environments prefer the supported nature of rman. They also enjoy the way that you can completely integrate your commercial backup software with rman. It's also the only way to get true incremental backups of your datafiles. In addition, you can also use rman to back up to disk without purchasing an agent for your commercial backup system.

However, some DBAs prefer what Oracle calls user-managed backups. They put their database into backup mode prior to backing up and take it out of backup mode after backup. Sometimes this is due to a long history and familiarity with user-managed backups; sometimes it is the cost of either the disk you would need to use rman without a media manager or the cost of the media manager. For whatever reason, approximately half of Oracle customers perform user-managed backups, but this percentage goes down every day.

This section discusses methods that can be used to safely back up Oracle without using rman. You can back up to disk and then back that disk up using your normal backup procedures, or you can back up directly to tape.

Like most RDBMSs, Oracle databases can reside on cooked filesystem files or raw disk devices (raw disks are available only in Unix). Even if raw devices are available, many Oracle DBAs put their databases on cooked files. One of the reasons for this is that if all of the database files are accessible via the filesystem, backing up is very simple. Any standard copy utility (e.g., cp, copy) or any backup utility (e.g., dump, cpio, or commercial utility) can copy the data. If you are running Oracle on Unix and decide to put your Oracle database on raw partitions, you need to use a tool that can back up raw partitions (e.g., dd).

Backups can be done offline (a cold backup) or online (a hot backup). If you're going to perform user-managed backups, you must back up all of the following:

• Datafiles

• Control files

• Online redo logs (if performing a cold backup)

• The parameter file

• Archived redo logs

• Password file if used

• The ORACLE_HOME directory

16.3.1. Cold Backup

A cold backup of an Oracle database that is based on filesystem files is the easiest of all database backups because most companies already have some system that backs up their server's filesystems. It could be a homegrown program that runs dump or ntbackup, or it could be a commercial backup product. To perform a cold backup of an Oracle database, simply perform an orderly shutdown of Oracle (not shutdown abort) before running the normal backup. An orderly shutdown performs a checkpoint, flushing any changed data stored in memory to disk, and then stops all processes that allow access to the database. This procedure puts all Oracle files into a clean, consistent, quiescent state.

This procedure assumes, of course, the use of filesystem files. An Oracle database running on a Unix server also could be sitting on raw devices. A cold backup of such a database requires a little more effort because you need to understand the structure of the database. The procedure starts out the same, by shutting down the database. A filesystem backup at this point, though, gets only the executables and any database objects that reside in the filesystem, such as the control file. The database itself requires extra effort. The first thing to figure out is where all the database files are. A script can "ask" Oracle this question by querying v$datafile, but that script would have to be written. Once the locations of all files are known, dd can back them up to a file somewhere in the filesystem or send them directly to a backup volume. If they are backed up to a file in the filesystem, it must be done before the normally scheduled filesystem backup. That way, the files are automatically backed up to a backup volume.

Therefore, backing up an Oracle database that uses raw partitions is harder than backing one up that is based on filesystem files. This is one reason why Oracle DBAs have historically used the filesystem to store their database files, even though raw partitions yield slightly better performance.

16.3.2. Hot Backup

If an Oracle database is providing the data for the customer service web page or any other application that requires 24-hour uptime, a cold backup is not acceptable because it requires that the database be shut down on a regular basis. Even if this is done late at night, customers accessing the web page may do so at any time. A company has a much better online image if it is able to leave the web page up all the time. What is needed, then, is a hot, or online backup.

The database must be running in archivelog mode in order to run hot backups.

A hot backup requires quite a bit more work than a cold backup, and this is even more true when the cold backup is of a database using raw devices. The following steps must be taken every time a hot backup is performed:

1. Find out the names and locations of all tablespaces and the datafiles they reside on:

2. 1. If running Oracle 10gR2 or later, you must ask Oracle for a list of all datafiles, using the SQL command shown here:

   2. SQL> select file_name from sys.dba_data_files; /oracle/product/10.2.0/oradata/orcl/users01.dbf /oracle/product/10.2.0/oradata/orcl/sysaux01.dbf /oracle/product/10.2.0/oradata/orcl/undotbs01.dbf /oracle/product/10.2.0/oradata/orcl/system01.dbf /oracle/product/10.2.0/oradata/orcl/example01.dbf

      
      

   3. If running a version prior to Oracle 10gR2, you must ask Oracle for a list of all datafiles and tablespaces, using the SQL command shown here:

   4. SQL> select tablespace_name, file_name from sys.dba_data_files; USERS  /oracle/product/10.2.0/oradata/orcl/users01.dbf SYSAUX  /oracle/product/10.2.0/oradata/orcl/sysaux01.dbf UNDOTBS1 /oracle/product/10.2.0/oradata/orcl/undotbs01.dbf SYSTEM  /oracle/product/10.2.0/oradata/orcl/system01.dbf EXAMPLE /oracle/product/10.2.0/oradata/orcl/example01.dbf

      
      

3. Ask Oracle for the location of the archived redo logs using a SQL command like the one shown here. Since you can now specify multiple locations, change the query to show those parameters that are like 'log_archive_dest_%'.

4. SQL> select name,value from v$parameter where name  SQL> like 'log_archive_dest_%'; log_archive_dest_1  location=/backups/archive

   
   

5. Put the database into backup mode:

6. 1. If running Oracle 10gR2 or later, you can put the entire database into backup mode using the SQL command alter database begin backup.

   2. If running a version prior to 10gR2, put each tablespace into backup mode using the SQL command alter tablespace tablespace_name begin backup.

Because more redo is generated when tablespaces are in backup mode, there are performance ramifications to placing the entire database in backup mode. If placing the entire database into backup mode at once creates too much of a load on your server, place a few tablespaces into backup mode at a time and back up just their datafiles. This is obviously a lot more complicated than the method proposed here.

1. Copy each tablespace's datafiles to an alternate location such as disk or tape, or run your commercial backup program to back up all filesystems (and possibly raw devices) to disk or tape.

2. Take the database out of backup mode:

3. 1. If running Oracle 10gR2 or later, you can take the entire database out of backup mode by running the SQL command alter database end backup.

   2. If running a version prior to 10gR2, take each tablespace out of backup mode using the SQL command alter tablespace tablespace_name end backup.

4. Switch the redo logfile and make sure it is archived. The best method to do both is to run the SQL command alter system archive log current. This switches the logfile but does not return the prompt until the previous redo log has been archived. You can run alter system switch logfile, but then you won't be sure that the latest redo log has been archived before you move on to the next step.

5. Back up the control file using the SQL commands alter database backup controlfile to filename and alter database backup controlfile to trace.

Make sure you use both methods to back up the control file; either one may come in handy at different times.

1. Ensure that all archived redo logs that span the time of the backup are backed up.

This is obviously a lot of work. A good knowledge of scripting is required, as well as a good knowledge of the commands necessary to accomplish these tasks. To explain the whole process, let's break it down by section:

Determine structure

Steps 1 and 2 have to do with figuring out where everything is. Make sure you do these steps every single time you do a backup, not just once or only when you change the structure of the database. This ill-advised method results in a static script that backs up only the tablespaces that were discovered the last time these steps were done. This is too open to human error and not recommended. It is much better to automate these steps and do them each time an instance is backed up. Doing them each time ensures that the configuration data is always currentand that the backups never miss a thing.

Put files into backup mode, and then copy them

Copying the database files while the database is running can be done only by placing one or more tablespaces (or the whole database) in backup mode. The datafiles that are in those tablespaces or that database then can be copied or backed up at will. Since the files are still being written to as you are backing them up, Oracle refers to this as an inconsistent backup. Some also refer to this type of backup as a "fuzzy" backup; they are the same thing. The point is that the file is still changing while you are backing it up. Don't worry; Oracle will be able to resolve any inconsistencies in the file during recovery.

Please see the following section for a refutation of the common misconception that datafiles are not being written to when they are in hot-backup mode.

Back up related files

The datafiles are just one of the many sections of Oracle that need to be backed up. Also back up the control file, the archived redo logs, the configuration files, the password file if you're using it, the ORACLE_HOME directory, and any directories. (If it is a cold backup, you should also copy the online redo logs because they create a complete copy of the database at that point in time.)

16.3.3. Debunking Hot-Backup Myths

What happens during a hot backup is widely misunderstood. I debunk two main myths here:

• Datafiles don't change during hot backup.

• Oracle logs a full copy of every block every time it's changed during hot backup.

Many people believe that while a tablespace is in backup mode, the datafiles within that tablespace are not written to. They believe that all changes to these files are kept in the redo logs until the tablespace is taken out of backup mode, at which point all changes are applied to the datafiles just as they are during a media recovery. (The concept of media recovery is described in the section "Recovering Oracle" later in this chapter.) Although this explanation is easier to understand (and swallow) than how things really work, it is absolutely not how hot backups work in Oracle.

Many people believe that when a tablespace is in backup mode, Oracle switches from logging the redo vector to logging full blocks. Neither is correct. It continues to log redo vectors, but it also logs the full image of any block that is changedbut only the first time it changes that block.

oraback Script

Unix Backup & Recovery had a Unix-only script that supported hot and cold backups for Oracle databases using the begin backup and end backup commands. The script still exists and is available on www.backupcentral.com but is not covered in detail in this chapter for two reasons. The first reason is that the script is in a constant state of change, and I didn't want the book to be out of date with the script. (We're currently looking for volunteers to help create a Perl version that will also support Windows. Feel free to join the team.) The second reason is space. This edition of the book got really large, and we had to cut where we could.

A common reaction to these statements is a very loud "What?" followed by crossed arms and a really stern look. (I reacted the same way the first time I heard it.) "How could I safely back up these files if they are changing as I'm backing them up? What do you mean it logs the full image only the first time it changes the block?" Don't worry; Oracle has it all under control. Remember that every Oracle datafile has an SCN that is changed every time an update is made to the file. Also remember that every time Oracle makes a change to a datafile, it records the vector of that change in the redo log. When a tablespace is put into backup mode, the following three things happen:

1. Oracle checkpoints the tablespace, flushing all changes from shared memory to disk.

2. The SCN markers for each datafile in that tablespace are "frozen" at their current values. Even though further updates will be sent to the datafiles, the SCN markers will not be updated until the tablespace is taken out of backup mode.

3. In addition to recording how it changed a particular block (referred to as the redo vector), it logs the entire image of each block the first time it changes it on disk.

After this happens, your backup program works happily through this datafile, backing it up block by block. Since the file is being updated as you are reading it, it may read blocks just before they're changed, after they're changed, or even while they're changing! Suppose that your filesystem block size is 4 KB, and Oracle's block size is 8 KB. Your backup program will be reading in increments of 4 KB. It could back up the first 4 KB of an 8 KB Oracle data block before a change is made to that block, then back up the last 4 KB of that file after a change has been made. This results in what Oracle calls a split block. However, when your backup program reaches the point of the datafile that contains the SCN, it backs up the SCN the way it looked when the backup began because the SCN is frozen. Once you take the tablespace out of backup mode, the SCN marker is advanced to the current value, and Oracle switches back to logging change vectors and doesn't worry about full images of changed blocks.

How does Oracle straighten this out during media recovery? It's actually very simple. You use your backup program to restore the datafile. When you attempt to start the instance, Oracle looks at the datafile and sees an old SCN value. Actually, it sees the value that the SCN marker had before the hot backup began. When you enter recover datafile, it begins to apply redo against this datafile. Since the redo logs contain one complete image of every block that changed during your backup, it can rebuild this file to a consistent state, regardless of when you backed up a particular block of data. The first thing it does is overwrite any blocks for which it contains full images (that is, those blocks that changed during backup). It then applies regular redo against the file to apply any changes that occurred during or after the backup. This is why it needs only the first image of any block that was changed during the backup. It just needs one image to ensure that the block is put into a known state (as opposed to a split block). It can then apply redo against that block to redo any other changes.

If you're like me, you won't believe this the first time that you read it. So I'll prove it to you. Let's create a table called tapes in the tablespace test, insert the value "DLT" into it, and force a checkpoint:

SQL> create table tapes (name varchar2(32)) tablespace test; Table created. SQL> insert into tapes values ('DLT'); 1 row created SQL> commit; Commit complete. SQL> alter system checkpoint; System altered.

Now we ask Oracle what block number contains the new value:

SQL> select dbms_rowid.rowid_block_number(rowid) blk, name from tapes;   BLK NAME ------- ----------------    3 DLT

The value "DLT" is recorded in the third data block. Allowing nine blocks for the datafile headers, we can read the third block of data with dd and run strings on it to actually see that the value is there:

$ dd if=/db/Oracle/a/oradata/crash/test01.dbf ibs=8192 skip=11 count=1|strings 1+0 records in 16+0 records out DLT

Place the tablespace in hot-backup mode:

SQL> alter tablespace test begin backup ; Tablespace altered.

Now update the table, commit the update, and force a global checkpoint on the database:

SQL> update tapes set name = 'AIT'; 1 row updated SQL> commit; Commit complete. SQL> alter system checkpoint; System altered.

Extract the same block of data to show that the new value was actually written to disk:

$ dd if=/db/Oracle/a/oradata/crash/test01.dbf ibs=8192 skip=11 count=1|strings 1+0 records in 16+0 records out DLT, AIT

Now we can take the tablespace out of backup mode:

SQL> alter tablespace test end backup;

This test proves that datafiles are indeed being written to during hot backups!