Pro Oracle Database 11g Administration (71 page)
You can display the directory associated with the FRA like this:
SQL> show parameter db_recovery_file_dest
When you first implement an FRA, there are no subdirectories beneath the base FRA directory (specified with DB_RECOVERY_FILE_DEST). The first time Oracle needs to write a file to the FRA, it creates any required directories beneath the base directory. For example, after you implement an FRA, if archiving for your database is enabled, then the first time a online-redo log switch occurs, Oracle creates the following directories beneath the base FRA directory:
For this database example, the archive logs are written to the following directory:
/ora02/fra/O11R2/archivelog/2010_08_25
Each day that archive-redo logs are generated results in a new directory being created in the FRA using the directory name format of YYYY_MM_DD.
If you want archive-redo logs written to both an FRA and a non-FRA location, you can enable that as follows:
SQL> alter system set log_archive_dest_1='location=/ora02/oraarch/O11R2'; SQL> alter system set log_archive_dest_2='location=USE_DB_RECOVERY_FILE_DEST'; Thinking Unoraclethodox FRA Thoughts
Oracle recommends that you use an FRA for archive-redo logs and RMAN backups. However, I usually don’t implement an FRA in production environments—not for the archive-redo logs, not for the RMAN
backup files, not for any types of files. Why is that?
When you enable an FRA, if you don’t set the initialization parameter LOG_ARCHIVE_DEST_N, then by default, the archive-redo logs are written to the FRA. It’s the same with RMAN backups: if you don’t specifically configure an RMAN channel disk location, then by default, the RMAN backup files are written to the FRA.
When you use an FRA (as described earlier), the disk space consumed by the archive-redo logs and RMAN backups must fit in the disk space assigned to the FRA. What happens if you have an unexpected spike in the amount of redo generated for a database, or if an unforeseen issue arises with the RMAN
backups that results in unanticipated amounts of disk space being consumed?
With regard to the RMAN backups, if the FRA fills up, the RMAN backups abort. An RMAN backup failure isn’t catastrophic, because usually you can quickly resolve space issues and manually run another backup. In most situations, this doesn’t compromise your database availability; in the event of a backup failure, you still have a previous RMAN backup that you can use to restore and recover your database.
However, in the event that the archive-redo log destination fills up, and the archiver can’t write to the file system, your database will hang. In many 24x7 mission-critical environments, this type of downtime is unacceptable and will jeopardize your ability to keep your job.
I find it easier to control the space allocated to the archive-redo logs by using a dedicated mount point for these critical files. If possible, don’t share the disk space allocated to the archive-redo logs with the RMAN backups. A problem with the RMAN backups can cause disk-space issues for the archive-redo logs, and you want the space consumed by the archive-redo logs to be as stable and predictable as possible.
425
CHAPTER 16 ■ USER-MANAGED BACKUP AND RECOVERY
Disabling Archivelog Mode
Usually, you don’t disable archivelog mode for a production database. However, you may be doing a big data load and want to reduce any overhead associated with the archiving process, so you want to turn off archivelog mode before the load begins and then re-enable it after the load. If you do this, be sure you make a backup as soon as possible after you re-enable archiving.
To disable archiving, do the following as SYS (or a user with the SYSDBA privilege): $ sqlplus / as sysdba
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database noarchivelog;
SQL> alter database open;
You can confirm archivelog mode with the following:
SQL> archive log list;
You can also confirm the log mode as follows:
SQL> select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG
Reacting to a Lack of Disk Space in Your Archive Log Destination
The archiver background process writes archive-redo logs to a location that you specify. If for any reason the archiver process can’t write to the archive location, your database hangs. Any users attempting to connect receive this error:
ORA-00257: archiver error. Connect internal only, until freed.
As a production-support DBA, you never want to let your database get into that state. Sometimes unpredictable events happen, and you have to deal with issues that weren’t ever supposed to happen.
■
Note
DBAs who support production databases have a completely different mindset that architect DBAs who get new ideas from flashy presentations or regurgitated documentation.
In this situation, your database is as good as down and completely unavailable. To fix the issue, you have to do something quickly:
• Move files to a different location.
• Compress old files in the archive-redo log location.
• Permanently remove old files.
• Switch the archive-redo log destination to a different location.
427
CHAPTER 16 ■ USER-MANAGED BACKUP AND RECOVERY
Moving files is usually the quickest and safest way to resolve the archiver error. You can use an OS
utility such as mv to move old archive-redo logs to a different location. If they’re needed for a subsequent restore and recovery, you can let the recovery process know about the new location. Be careful not to move an archive-redo log that is currently being written to. If an archived redo log file appears in V$ARCHIVED_LOG, that means it has been completely archived.
You can use an OS utility such as gzip to compress archive-redo log files in the current archive destination. If you do this, you have to remember to uncompress any files that may be later needed for a restore and recovery. Be careful not to compress an archive-redo log that is currently being written to.
Another option is to use an OS utility such as rm to permanently remove archive-redo logs from disk.
This approach is dangerous because you may need those archive-redo logs for a subsequent recovery. If you do remove archive-redo log files, and you don’t have a backup of them, you should make a full backup of your database as soon as possible.
If another location on your server has plenty of space, you can consider changing the location to which the archive-redo logs are being written. For example:
SQL> alter system set log_archive_dest_1='location=/oraarch02';
After you’ve resolve the issue with the primary location, you can switch back the original location.
You can perform this operation while the database is up and running.
When the archive-redo log-file destination is full, you have to scramble to resolve it. This is why a good deal of thought should precede enabling archiving for production 24x7 databases.
For most databases, writing the archive-redo logs to one location is sufficient. However, if you have any type of disaster-recovery or high-availability requirement, then you should write to multiple locations. Sometimes DBAs set up a job to back up the archive-redo logs every hour and copy them to an alternate location or even an alternate server.
Backing Up Archive-Redo Log Files
Depending on your business requirements, you may need a strategy for backing up archive-redo log files. Minimally, you should back up any archive-redo logs generated during a backup of a database in archivelog mode. Additional strategies may include
• Periodically copying archive-redo logs to an alternate location and then removing them from the primary destination
• Copying the archive-redo logs to tape and then deleting them from disk
• Using two archive-redo log locations
• Using Data Guard for a robust disaster-recovery solution
Keep in mind that you need all archive-redo logs generated since the begin time of the last good backup to ensure that you can completely recover your database. Only after you’re sure you have a good backup of your database should you consider removing archive-redo logs that were generated prior to the backup.
Making a Cold Backup of an Archivelog-Mode Database
You can use a backup of a database in archivelog mode to restore and recover up to the last committed transaction prior to a failure. Therefore, unlike a backup of a noarchivelog-mode database, this type of backup is never intended to be used to reset the database back to a point in time in the past from which no recovery can be applied. The purpose of a backup of an archivelog-mode database is to restore the database and roll forward and apply transactions to fully recover the database.
428
CHAPTER 16 ■ USER-MANAGED BACKUP AND RECOVERY
The derailleur was fine. He was just one of those guys who knows only to push the button, without any understanding of what goes on underneath that action.”
Efforts you put in to understanding how B&R is implemented pay off in the long run. You actually have less to remember—because your understanding of the underlying implementation enables you to think through problems and solve them in ways that checklists don’t.
Implementing a Hot-Backup Strategy
As mentioned earlier in this chapter, RMAN should be your tool of choice for any type of Oracle database backup (either online or offline). RMAN is more efficient than user-managed backups and automates almost all of the tasks. Having said that, one of the best ways to gain an understanding of Oracle B&R
internals is to make a hot backup and then use that backup to restore and recover your database.
Manually issuing the commands involved in a hot backup, and then a restore and recovery, helps you understand the role of each type of file (control files, datafiles, archive-redo logs, and online-redo logs) in a restore-and-recovery scenario.
This section begins by showing you how to implement a hot backup. It also provides basic scripts that you can use to automate the hot-backup process. Later sections explain some of the internal mechanics of a hot backup and clarify why you must put tablespaces in backup mode before the hot backup takes place.
Making a Hot Backup
Here are the steps required for a hot backup:
1. Ensure that the database is in archivelog mode.
2. Determine where to copy the backup files.
3. Determine which files need to be backed up.
4. Note the maximum sequence number of the online-redo logs.
5. Alter the database/tablespace into backup mode.
6. Copy the datafiles with an OS utility.
7. Alter the database/tablespace out of backup mode.
8. Archive the current online-redo log, and note the maximum sequence number of the online-redo logs.
9. Back up the control file.
10. Back up any archive-redo logs generated during the backup.
These steps are covered in detail in the following sections.
430
CHAPTER 16 ■ USER-MANAGED BACKUP AND RECOVERY
Step 1: Ensure That the Database Is in Archivelog Mode
Run the following command to check the archivelog-mode status of your database: SQL> archive log list;
The output shows that this database is in archivelog mode:
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /ora02/oraarch/O11R2
If you’re not sure how to enable archiving, see the earlier section in this chapter on Enabling Archivelog Mode that details how to implement archiving.
Step 2: Determine Where to Copy the Backup Files
Now, determine the backup location. For this example, the backup location is the directory
/oradump/hbackup/O11R2. To get a rough idea of how much space you need, you can run this query: SQL> select sum(bytes) from dba_data_files;
Ideally, the backup location should be on a separate set of disks from your live datafiles. But in practice, many times you’re given a slice of space on a storage area network (SAN) and have no idea about the underlying disk layout. In these situations, you rely on redundancy being built into the SAN