Pro Oracle Database 11g Administration (70 page)

/ as sysdba

set head off pages0 lines 132 verify off feed off trimsp on

define cbdir=/oradump/cbackup/O11R2

define dbname=$ORACLE_SID

spo coldrest.sql

select 'shutdown abort;' from dual;

select '!cp ' || '&&cbdir/' || substr(name, instr(name,'/',-1,1)+1) ||

' ' || name from v\$datafile;

select '!cp ' || '&&cbdir/' || substr(name, instr(name,'/',-1,1)+1) ||

' ' || name from v\$tempfile;

select '!cp ' || '&&cbdir/' || substr(member, instr(member,'/',-1,1)+1) ||

' ' || member from v\$logfile;

select '!cp ' || '&&cbdir/' || substr(name, instr(name,'/',-1,1)+1) ||

' ' || name from v\$controlfile;

select 'startup;' from dual;

spo off;

EOF

exit 0

This script creates a script named coldrest.sql that generates the copy commands to restore your datafiles, tempfiles, logfiles, and control files back to their original locations. After you run this shell script, here’s a partial snippet of the code in the coldrest.sql file: shutdown abort;

!cp /oradump/cbackup/O11R2/system01.dbf /ora01/dbfile/O11R2/system01.dbf

!cp /oradump/cbackup/O11R2/sysaux01.dbf /ora01/dbfile/O11R2/sysaux01.dbf

!cp /oradump/cbackup/O11R2/undotbs01.dbf /ora02/dbfile/O11R2/undotbs01.dbf

!cp /oradump/cbackup/O11R2/users01.dbf /ora02/dbfile/O11R2/users01.dbf

...

!cp /oradump/cbackup/O11R2/redo02a.rdo /ora01/oraredo/O11R2/redo02a.rdo

!cp /oradump/cbackup/O11R2/redo02b.rdo /ora02/oraredo/O11R2/redo02b.rdo

!cp /oradump/cbackup/O11R2/control01.ctl /ora01/dbfile/O11R2/control01.ctl startup;

If you need to restore from a cold backup using this script, log on to SQL*Plus as SYS and execute the script:

$ sqlplus / as sysdba

SQL> @coldrest.sql

Implementing Archivelog Mode

Recall from the discussion in Chapter 5 that archive redo logs are created only if your database is in archivelog mode. In this mode, as the online-redo logs fill up, a log switch is initiated. When a log switch occurs, the log-writer process stops writing to the most recently filled online-redo logs and starts writing to a new online-redo log group. The online-redo log groups are written to in a round-robin fashion.

Archivelog mode is a prerequisite for the following technologies: user-managed hot backups, the Flashback Database feature, and RMAN online backups. If you don’t have archiving enabled, and you 420

CHAPTER 16 ■ USER-MANAGED BACKUP AND RECOVERY

attempt to make a user-managed hot backup, you receive this error when altering a database or tablespace into backup mode:

ORA-01123: cannot start online backup; media recovery not enabled

At this point, you need to place your database in archivelog mode.

Making Architectural Decisions

When you implement archivelog mode, you also need a strategy for managing these files. The archive-redo logs consume disk space. If left unattended, these files will eventually consume all the space allocated for them. If this happens, the archiver can’t write a new archive redo log file to disk, and your database will stop processing transactions. At this point you have a hung database. You need to manually intervene by creating space for the archiver to resume work. For these reasons, there are several architectural decisions you must carefully consider before you turn on archiving:

• Where to place the archive-redo logs, and whether to use the fast-recovery area (formerly known as the flash-recovery area) to store the archive-redo logs

• How to name the archive-redo logs

• How much space should be allocated to the archive-redo log location

• How often to back up the archive-redo logs

• When it’s okay to permanently remove archive-redo logs from disk

• Whether multiple archive-redo log locations should be enabled

• If this is a production database, when to schedule the small amount of downtime that’s required

Minimally, you should have enough space in your primary archive-redo location to hold at least a day’s worth of archive-redo logs. This lets you back them up on a daily basis and then remove them from disk after they’ve been backed up.

If you decide to use a fast-recovery area (FRA) for your archive-redo log location, you must ensure that it contains sufficient space to hold the number of archive-redo logs generated between backups.

Keep in mind that the FRA typically contains other types of files, such as RMAN backup files, flashback logs, and so on. If you use an FRA, be aware that the generation of other types of files can potentially impact the space required by the archive-redo log files.

You need a strategy to automate the backup and removal of archive-redo log files. For user-managed backups, this can be implemented with a shell script that periodically copies the archive-redo logs to a backup location and then removes them from the primary location. As you see in later chapters, RMAN automates the backup and removal of archive-redo log files.

If your business requirements are such that you must have a certain degree of high availability and redundancy, then you should consider writing your archive-redo logs to more than one location. Some shops set up jobs to copy the archive-redo logs periodically to a different location on disk or even copy them to a different server.

Setting the Archive-Redo File Location

Before you set your database mode to archiving, you should specifically instruct Oracle where you want the archive-redo logs to be placed. You can set the archive-redo log-file destination with the following techniques:

421

CHAPTER 16 ■ USER-MANAGED BACKUP AND RECOVERY

• Set the LOG_ARCHIVE_DEST_N database initialization parameter.

• Implement an FRA.

These two approaches are discussed in detail in the following sections.

■
Tip
If you don’t specifically set the archive-redo log location via an initialization parameter or by enabling the FRA, then the archive-redo logs are written to a default location. This location depends on the database version and operating system. For active production database systems, the default archive-redo log location is rarely appropriate.

Setting the Archive Location to a User-Defined Disk Location (non-FRA) If you’re using an init.ora file, modify the file with an OS utility (such as vi) and place in it the location where you want the archive-redo logs written. In this example, the archive-redo log location is set to

/ora02/oraarch/O11R2:

log_archive_dest_1='location=/ora02/oraarch/O11R2'

log_archive_format='%t_%s_%r.arc'

If you’re using an spfile, use ALTER SYSTEM to modify the appropriate initialization variables: SQL> alter system set log_archive_dest_1='location=/ora02/oraarch/O11R2' scope=both; SQL> alter system set log_archive_format='%t_%s_%r.arc' scope=spfile; You can dynamically change the LOG_ARCHIVE_DEST_n parameters while your database is open.

However, you have to stop and start your database for the LOG_ARCHIVE_FORMAT parameter to take effect.

Take care not to set the LOG_ARCHIVE_FORMAT to an invalid value, because then you can’t start your database:

SQL> startup nomount;

ORA-19905: log_archive_format must contain %s, %t and %r

In this situation, if you’re using an spfile, you can’t start your instance. You have to rename the spfile, create a pfile from the spfile (SQL> create pfile from spfile;), modify the parameter to a valid value, start the database with the pfile, and re-create the spfile from the pfile.

When you specify LOG_ARCHIVE_FORMAT, you must include %t (or %T), %s (or %S), and d% in the format string. Table 16–1 lists the valid variables you can use with the LOG_ARCHIVE_FORMAT initialization parameter.

422

CHAPTER 16 ■ USER-MANAGED BACKUP AND RECOVERY

Table 16–1.
Valid Variables for the Log Archive Format String
Format String

Meaning

%s

Log-sequence number

%S

Log-sequence number padded to the left with zeros

%t

Thread number

%T

Thread number padded to the left with zeros

%a

Activation ID

%d

Database ID

%r

Resetlogs ID required to ensure uniqueness across multiple incarnations of the database

If you don’t specify a value for LOG_ARCHIVE_FORMAT, Oracle uses a default such as %t_%s_%r.dbf. One aspect of the default format that I don’t like is that it ends with the extension .dbf, which is widely used for datafiles. This can cause confusion about whether a particular file can be safely removed because it’s an old archive-redo log file or whether the file shouldn’t be touched because it’s a live datafile. Most DBAs are reluctant to issue commands like this (for fear of accidentally removing a critical datafile): $ rm *.dbf

You can view the value of the LOG_ARCHIVE_DEST_N parameter by running the following: SQL> show parameter log_archive_dest

Here’s a partial listing of the output:

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest string

log_archive_dest_1 string location=/ora02/oraarch/O11R2

log_archive_dest_10 string

log_archive_dest_11 string

For Oracle Database 11
g
, you can enable up to 31 different locations for the archive-redo log file destination. For most production systems, you can enable just one archive-redo log destination location.

If you need a higher degree of protection, you can enable multiple destinations. Keep in mind that when you use multiple destinations, the archiver must be able to write to at least one location successfully. If you enable multiple mandatory locations and set LOG_ARCHIVE_MIN_SUCCEED_DEST to be higher than 1, then your database may hang if the archiver can’t write to all mandatory locations.

423

CHAPTER 16 ■ USER-MANAGED BACKUP AND RECOVERY

You can check the details regarding the status of archive-redo log locations via this query: select

dest_name

,destination

,status

,binding

from v$archive_dest;

Here’s a small sample of the output:

DEST_NAME DESTINATION STATUS BINDING

-------------------- ------------------------------ --------- ---------

LOG_ARCHIVE_DEST_1 /ora02/oraarch/O11R2 VALID OPTIONAL

LOG_ARCHIVE_DEST_2 INACTIVE OPTIONAL

Using the FRA for Archive Log Files

The FRA is an area on disk—specified via database-initialization parameters—that instructs Oracle to place specific files like archive-redo logs, RMAN backup files, flashback logs, and multiplexed control files and online-redo logs. To enable the use of an FRA, you must set two initialization parameters:

• DB_RECOVERY_FILE_DEST_SIZE specifies the maximum space to be used for all files that are stored the FRA.

• DB_RECOVERY_FILE_DEST specifies the base directory for the FRA.

When you create an FRA, you’re not really creating anything—you’re telling Oracle which directory to use when storing files that go in the FRA. For example, say 200GB of space are reserved on a mount point, and you want the base directory for the FRA to be /ora02/fra. To enable the FRA, first set DB_RECOVERY_FILE_DEST_SIZE:

SQL> alter system set db_recovery_file_dest_size=200g;

Next, set the DB_RECOVERY_FILE_DEST parameter:

SQL> alter system set db_recovery_file_dest='/ora02/fra';

If you’re using an init.ora file, be sure you modify it with an OS utility (such as vi) so the changes persist across database restarts.

After you enable an FRA, by default, Oracle writes archive-redo logs to subdirectories in the FRA.

■
Note
If you’ve set the LOG_ARCHIVE_DEST_N parameter to be a location on disk, archive-redo logs aren’t written to the FRA.

You can verify that the archive location is using an FRA:

SQL> archive log list;

424

CHAPTER 16 ■ USER-MANAGED BACKUP AND RECOVERY

If archive files are being written to the FRA, you should see output like this: Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST