Study Brief: Oracle8 Backup and Recovery

by Michael Ritacco, Oraclenotes.com

Structures and Processes (See DBA for Review)

Memory

- Buffer Cache
- Log Buffer
- Shared Pool
- Large Pool

Background Processes
If any of these processes terminate the instance will shut down.

- DBWR
- LGWR
- SMON
- PMON
- CKPT

User Process
Process created (on server or client) when the user starts a tool that communicates with Oracle. SQL*Plus, OEM, Server Manager, etc.

Server Process
Process is created when connecting to the instance in a non-MTS confiquration.

Physical Files

- Control Files
- Parameter File
- Password File
- Data Files
- Redo Logs
- Archive Logs

Configuring Oracle for Backup & Recovery

Noarchivelog Mode (default status)

• Redo log files are immediately reused after a checkpoint. No redo log history is kept for recovery.
• Once a redo log has been overwritten recovery is only possible to the last full backup. Therefore you will lose all data since the last full backup!
• Backups can only be performed when the database is shutdown.
• All files must be backuped. Redo log files, datafiles, control files.
• No online backups are available.
• The Import/Export utility may be used for database backup but this method still results in a incomplete recovery.

Archivelog Mode

• Redo log files cannot be reused until a checkpoint has occured and the log file has been archived by the ARCH process.
• The archived log files are used to restore backup copys of lost or damaged files bringing them up-to-date with the rest of the database. This can be while the database is online or offline.
• The database is protected from media failure and recovery of the databse is possible up to the point-of-failure.

Archive Parameters

LOG_ARCHIVE_DEST
LOG_ARCHIVE_FORMAT
LOG_ARCHIVE_ DUPLEX_DEST

To enable Archivelog Mode

• SHUTDOWN IMMEDIATE
• START MOUNT
• ALTER DATABASE ARCHIVELOG;
• ALTER DATABASE OPEN;
• SHUTDOWN IMMEDIATE
• Perform full database backup

The database is now in archivelog mode with manual archiving.

To enable the automatic archive process (recommended)

• In parameter file set LOG_ARCHIVE_START=TRUE
• In an open instance issue the command: ALTER SYSTEM ARCHIVE LOG START

Archive Log Information

• V$ARCHIVED_LOG: Archived Log information from the control file.
• V$ARCHIVED_DEST: Describes all archive log destinations, the current value, mode and status for the current instance.
• V$LOG_HISTORY: Log file information form the control file
• V$DATABASE: Current state of archiving
• SVRMGR> ARCHIVE LOG LIST
  DATABASE LOG MODE
  AUTOMATIC ARCHIVAL
  ARCHIVE DESTINATION
  OLDEST ONLINE LOG SEQUENCE
  NEXT LOG SEQUENCE TO ARCHIVE
  CURRENT LOG SEQUENCE
  
  

Physical Backups (Manual)

Types of Failures

There are five types of failure that can happen in Oracle:

• statement failure: is when Oracle cannot process a statement due to syntax error or a database restriction
• user process failure: is when a user process is abnormally terminated PMON will automatically clean up after the abnormally terminated session
• user error: User error is when a user does something to an object in the database that was not desired.
• instance failure: Instance failure is when the instance crashes due to a failed core process (review first section), bad RAM, CPU, controller card - any hardware or OS failure on the server except those defined as media failure.
• media failure: This is the most serious type of failure and is very DBA intensive to fix. Happens when a hard drive head crashes or a Oracle data file is deleted.
  
  DBVERIFY is the separate tool used to verify the integrity of datafiles.

LOG_BLOCK_CHECKSUM=TRUE is set to check the integrity of redo logs. This prevents corrupted redo logs being used in the backup or recovery process and potentially corrupting the entire database.

• If a bad checksum is found on one log member Oracle will attempt to use another member of the group.
• If all members of a redo log group have bad checksums the database will hang beacause Oracle cannot reuse the redo log file.
  
  Use this command if the active redo log group is corrupt and you only have two groups of redo log files:ALTER DATABASE CLEAR UNARCHIVED LOGFILE
  
  
• BACKGROUND_DUMP_DEST specifies the loaction of the alert.log and all background process trace files.
• The alert.log records information such as when the instance starts and stops, checkpoint information, and system errors.

Complete Recovery

Complete recovery recovers the database to the point of failure. A database must be running in ARCHIVELOG mode with all archived redo logs available to perform this recovery. Complete recovery will restore only committed transactions up until the point of failure, uncommitted transactions must still be reentered.

Incomplete Recovery

• Time-based recovery recovers the database to a given time in the past. (Good for user errors)
• Change-based recovery recovers the database to a specified SCN (system change number). Change-based recovery is used in in a distributed environment only.
• Cancel-based recovery recovers the database up through a given archived redo log. Once the final redo log is applied, CANCEL is entered to stop recovery. You will only be able to recover up to the last good redo log prior to the missing or damaged redo log. All redo logs after the missing or damaged redo log will be useless. Perform a cancel-based recovery when a redo log group is damaged or missing.

Export and Import

A logical backup is created using the EXPORT utility. No physical files are backed up.

Full
User
Tables

Exports are useful for: moving tables between schema, moving schema from one instance to another, or quickly creating new databases.

Exports cannot be used with archived redo logs to do a complete recovery.

DIRECT=Y used to increase the speed of export. This is known as a direct-path export. When performing a direct-path export the BUFFER switch is ignored and the tablespace should be backed becasue little redo log information is kept.

CONSISTENT=Y will ensure read consistency for users on the system.

NLS_LANG

Recovery Manager (RMAN)

RMAN is tool provided with Oracle that performs backup & recovery operations.

Backup manager is the GUI interface, a part of OEM, and is used for maintaining and accessing RMAN information. Backup Manager is independent from the operating system.

When to use a recovery catalog:

• Stored scripts
  
• tablespace point in time recovery
• long term backup information is required
• Incremental backups are needed
• You have enough resources for a second database to store the recovery catalog.

Create a Recovery Catalog:

• Choose a separate database to store the recovery catalog
• Create a tablespace for the catalog
• Create a RMAN user
• grant recovery_catalog_owner and the necessary DBA privileges to the rman user.
• Run the catrman.sql script to create the tables required for the recovery catalog.
• A recovery catalog should be resynchronized at least once per day.

CONTROL_FILE_RECORD_KEEP_TIME: sepecifies the legnth of time RMAN information is stored in the control file. Default is 7 days.

A snapshot control file is created used by RMAN for read consistant image of the control file. The location can be changed by issuing this command:
set snapshot controlfile name to '/disk5/backup/ctl247.snp'

Connecting with Recovery Catalog

• Locally connecting:
  $ rman rcvcat rman/rman@RCVCAT
  RMAN> connect target
• With SYSDBA:
  $ rman target bubba/beer \
  $ rcvcat rman/man@RCVCAT
• Remotely:
  $ rman target bubba/beer@DBA08 \
  $ rcvcat rman/rman@RCVCAT

Connecting without Recovery Catalog

• Locally connecting:
  $ rman nocatalog
  RMAN> connect target
  
  
• With SYSDBA:
  $ rman target bubba/beer \
  $ nocatalog
  
  
• Remotely:
  $ rman target bubba/beer@DBA08 \
  $ nocatalog

Recovery Catalog

COMMANDS

• REGISTER DATABASE;
  Mount the target database then issue this command to register the database in the recovery catalog. *Do not register cloned databases in the same catalog.
• RESET DATABASE;
  Use this command if a database experiences imcomplete recovery and must be opened with the "restlogs".
• RESET DATABASE TO INCARNATION <identifier>;
  This command can undo a "restlogs" on a database. You can locate the necessary <identifer> by issuing the command:
  LIST INCARNATION OF DATABASE;
• RESYNC CATALOG
  Updates the recovery catalog with the changed/missing information from the control file. Resync under the following conditions:
  • Adding or dropping a rollback segment
  • Adding or dropping a tablespace
  • A new datafile has been added to an exisiting tablespace
• change datafilecopy '/disk1/oracle/system1.dbf' uncatalog;
  command removes system files that nolonger exist.
• catalog datafilecopy '/disk1/oracle/system1.dbf' tag='SYS01';
  command is used to store OS file information in recovery catalog.
• change archivelog '/disk1/oracle/arch_1.rdo' unavailable;
  specifies file available or unavailable for restore or recovery
  
• change archivelog '/disk1/oracle/arch_1.rdo' uncatalog;
  removes the references from the control file and recovery catalog if the file no longer exists.
  
• change archivelog all validate;
  removes files that have been deleted from the recovery catalog.
  
  
• catalog datafilecopy '/disk1/oracle/user1.dbf';
• catalog archivelog '/disk1/oracle/arch3.rdo';
  the catalog command is used to add copies of files to the recovery catalog. these copies once stored in the recovery catalog can be used by Recovery Manager for recovery.

Ways to recreate the Recovery Catalog:

• Rebuild the catalog database then use the CATALOG command to recatalog archived log files, backup control files, and datafiles.
• RESYNC CATALOG FROM BACKUP CONTROLFILE <filename>;
• Import the catalog owner schema from a previous Export then resync catalog.

Using RMAN for Physical Backups

Recovery manager will backup:

• Every datafile (entire database)
• Control File
• All or selected Archive log files
  
  *Online redo log files are not backed up by RMAN

Closed Backup: Target database must be MOUNTED.
Open Backup: Do not put tablespaces in hot backup mode!

RMAN Backups:

-Whole Backup
-Full Backup
-Incremental Backup

Types:

• Image Copies: is a physical copy of a datafile, archived log, or control file that is checked for corruption and registered in the control file. Similar to an Operating System copy, all blocks are copied. V$COPYCORRUPTION is used for image copies.
• Backup Sets: consists of one or more physical files stored in Oracle format. The Datafile type can contain datafiles and control files. The Archived Log type can only contain archived log files. V$BACKUP_CORRUPTION is used to get information on backup set corruption.

Allocating Channels:

A channel is the method of communication between the Oracle Server and the Operating System. At least one channel is required for every backup, restore, or recover command to function.

Troubleshooting RMAN:

• V$SESSION_LONGOPS: is used to check the progress of a backup.
  If the %COMPLETE column is not steadily increasing you may have a problem.
• V$SESSION_WAIT: can be used to see what events are being waited for.

Values available for the FORMAT option with the RMAN BACKUP command:
%d - target database name
%n - padded target database name
%p - backup piece number
%s - backup set number
%t - backup set stamp
%u - representation of the backup set number and time backup set was created.

Restore and Recovery with RMAN

• set newname for datafile name to name;
• Switch command
• Noarchivelog Mode databases must be MOUNT or NOMOUNT mode.
• Archivelog Mode databases must be in OPEN or MOUNT mode.

Other Recovery Issues

Damaged datafiles must be brought offline to perform recovery
ALTER DATABASE DATAFILE file_name OFFLINE;

To recover a damaged tablespace it also must be taken offline first:
ALTER TABLESPACE tablespace_name OFFLINE;

In certain situations you must use the IMMEDIATE clause with the ALTER TABLESPACE command to force the tablespace offline.

-PARALLEL RECOVERY

• To increase the speed of database recovery use the PARALLEL clause with the RECOVER command. RECOVER DATABASE PARALLEL (DEGREE n );
• PARALLEL_MAX_SERVERS: recovery processes cannot exceed the value of this parameter.
• RECOVERY_PARALLELISM: sets the default number of recovery processes per sesssion. The value specified in the PARALLEL clause of the RECOVER command will override this value.
  

-LOSS OF ALL CONTROL FILES (you should never let this happen)

• CREATE CONTROLFILE: this command is used to recreate a lost control file or to change the DBNAME, MAXDATAFILES, MAXLOGMEMBERS, etc parameters.
  

- READ-ONLY TABLESPACE RECOVERY

• ALTER DATABASE BACKUP CONTROLFILE TO TRACE; this command lists the special procedures necessary for read-only tablespace recovery.

-DATAFILE CORRUPTIONS
There are three ways to recover from datafile corruptions:

• Index Scan: if an index is available on the table with corruptions the index can be used to salvage the data.
• Select the rows above and below the corrupted block and insert into a new table.
• Restore and recovery on the datafile that is corrupted.