Oracle 9i Recovery Options
Download
Report
Transcript Oracle 9i Recovery Options
Oracle 9i Backup
ARCHIVELOG
vs. NOARCHIVELOG mode
User-managed backups: Logical vs. Physical
Export utility
Whole and Partial backups
Hot and cold backups
Noarchivelog Mode vs.
Archivelog Mode
No archive log
Archive log
Must backup entire database.
Can backup parts of database
(datafiles, tablespaces)
DB must be shut down to
perform a backup
hot backups possible
Only entire DB can be restored
Tablespaces can be restored
In case of a failure, all changes
since the last backup will be
lost
All committed transactions will
be restorable
(can recover the database at a
given point in time)
Source: http://www.adpgmbh.ch/ora/concepts/backup_recovery/archive_vs_noarchive_log.html
2
ARCHIVELOG mode
Oracle copies filled online redo logs to disk rather
than writing over them, creating archived redo logs.
SQL> select log_mode from v$database;
Ways to set or change this mode:
Specify the mode at database creation using dbca
Use the ALTER DATABASE ARCHIVELOG;
statement
To enable automatic archiving without shutting down the
current instance, use ALTER SYSTEM ARCHIVE LOG
START;
set the initialization parameter LOG_ARCHIVE_START to
TRUE
3
User-managed backups
Logical backup
use the Export utility to make backups of logical
objects (i.e., tables, views, and stored
procedures)
use the Import utility to restore these logical
objects
Physical backup
a backup of an Oracle database file or archived
redo log located on the operating system
Can use OS utilities or RMAN to create and
restore these files
4
Physical Backups
Backup Object
Backup Method
Example
Datafiles
Operating System
Utility
$ cp df3.f df3.bak
Archived Logs
Operating System
Utility
$ cp log_1_23.arc log_1_23.bak
Control Files
SQL Statement
SQL> ALTER DATABASE
BACKUP CONTROLFILE TO
cf1.bak
Init Parameter File
(init.ora / pfile)
SQL Statement
SQL> CREATE PFILE =
init.ora.bak FROM SPFILE;
Network and
Password files
Operating System
Utility
$ cp tnsnames.ora tnsnames.bak
$ cp orapw{SID} orapw{SID}.bak
(This information is from the following documentation: http://downloadwest.oracle.com/docs/cd/B10501_01/server.920/a96572/intro.htm#433631 )
5
Logical Backups
Backup Object
Backup Method
Example
Logical objects
(tables, indexes,
PL/SQL units such as
stored procedures)
Export utility
$ exp (to open interactively)
or
$ export SYSTEM/manager
TABLE=hr.emp FILE=emp.dmp
The Export utility: a simple way to transfer data objects between Oracle
databases, even if they reside on platforms with different hardware and
software configurations.
6
The Export Utility
Advantages:
An Export file: an Oracle binary dump file (typically located on disk or tape)
Files can be transferred using FTP or physically transported to a different site
Use the Import utility to transfer data between databases that are on systems not
connected through a network
Files can also be used as backups in addition to normal backup procedures
Can display contents of an export file without actually performing an import (use Import
SHOW parameter)
Disadvantages:
Export dump files can only be read by the Oracle Import utility
The version of the Import utility cannot be earlier than the version of the Export utility
used to create the dump file
Like snapshots of the database (therefore can’t apply redo to tables restored from export)
Has to have database fully open
More on using the Export and Import utilities: http://downloadwest.oracle.com/docs/cd/B10501_01/server.920/a96652/ch01.htm#1004671
7
Whole and Partial backups
Whole (full) database backup
A backup of the control file and all datafiles that belong to a
database
Is either consistent or inconsistent
(an inconsistent backup: some of the files in the backup contain
changes that were made after the files were checkpointed. An
inconsistent backup needs recovery before it can be made
consistent).
Can be done hot or cold
Partial database backup
any OS backup short of a full backup, taken while the database is
open or shut down.
Examples of partial database backups:
a backup of all datafiles for an individual tablespace
a backup of a single datafile
8
a backup of a control file
Hot (online) and cold (offline)
backups
Hot:
Database must be open
put the tablespaces in backup mode by issuing:
SQL> ALTER TABLESPACE BEGIN BACKUP
Oracle stops recording checkpoints to the tablespace's datafiles.
copy datafiles in that tablespace on OS level
SQL> ALTER TABLESPACE ... END BACKUP (or ALTER DATABASE END
BACKUP)
Cold: *especially important when the db schema changes
performed while the tablespace or datafile is offline
take tablespaces offline:
SQL> ALTER TABLESPACE tablespacename OFFLINE
9
Recommendations
Use archivelog mode
Perform regular full backups (not after unusual circumstances)
Consider time needed to perform backups and time to recover (the older
your last backup is, the more redo log files will have to be appliedtakes
longer to recover)
Perform scheduled cold backups (depends on database use)
For example: before an upgrade or a mass load, after schema
changes (new tablespace/datafile), after recovering, before using
“resetlogs”
Take more frequent hot backups if the amount of downtime for cold
backups must be limited
Backup archived log files on disks separate from the online redo logs
10
Oracle 9i Recovery
User-managed
recovery
Recovery Manager (RMAN)
Recommendations
Recovery Resources
User-managed Recovery
Files used in recovery
Datafiles
Control files
Archived redo logs
Server parameter files
12
Record Keeping
Record locations for:
datafiles: V$DATAFILE
control files: V$CONTROLFILE
online redo logs: V$LOGFILE
archived redo logs: V$PARAMATER
use LIKE log_archive_dest%
Record backup file locations:
Correlate backup files with the original files
name backup files using same relative filename as the
primary file
13
Datafile Recovery
Files to recover:
File location:
V$RECOVER_FILE
V$DATAFILE & V$TABLESPACE
Recovery:
Restore datafile from backup if available
If not available, can re-create when
all archived log files available
control file contains name of damaged file
14
Control File Recovery
Scenarios
Losing member of multiplexed control file
Restoring to default location
Restoring to non-default location
Copy one of the other control files
Alter the CONTROL_FILES initialization parameter
Losing all members of multiplexed control files
Copy one of the other control files
Restore from backup using RESETLOGS option
Losing all current & backup control files
Create new control file if all online redo log files are intact
15
Archived Redo Logs & Parameter Files
Archived redo logs will be needed during recovery
All logs generated between last backup and recovery time
Logs will need to be on disk during recovery
Determine needed logs: V$RECOVERY_LOG
Keep copies of parameter files
Restore from backup if available
Use client file if server file not available
16
RMAN & User-Managed Procedures Comparison
17
Recovery Manager
Recommended by Oracle for 8i or higher databases
Do not need to place online tablespaces in backup mode
Performs incremental backups
Omits never-used blocks from datafile backups
Uses the repository to report on crucial information
Stores RMAN scripts in the recovery catalog
Easily creates duplicate of production database for testing
purposes
Easily create a back up or standby database
Performs checks to determine whether backups on disk or in
the media catalog are still available
Performs automatic parallelization of backup and restore
operations
Tests whether files can be backed up or restored without
actually performing the backup or restore
Performs archived log failover automatically for corrupt or
missing logs
18
RMAN
RMAN Architecture
RMAN executable
Target database(s)
Recovery catalog database - optional
Media Management software
Running RMAN
From command line: rman
Oracle Enterprise Manager (OEM)
19
Recommendations
RMAN
Don’t store the RMAN database on the same disk with the
database being backed up
Don’t use the default account’s default password
Make sure to completely understand classical backup &
recovery procedures. RMAN’s methodology is not different.
Synchronize RMAN recovery catalog when the database file
layout changes
Monitor V$LONGOPS for long running backup sessions
Generic
Once you have established your backup plan & scripts, test
them and document your recovery.
20
Backup and Recovery
Resources
Oracle9i Backup and Recovery Concepts
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96519/toc.htm
Oracle9i User-Managed Backup and Recovery Guide
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96572/toc.htm
Oracle9i Recovery Manager User's Guide
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96566/toc.htm
Oracle9i DBA Handbook
Chapter 11: Backup and Recovery Options
Chapter 12: Using Recovery Manager (RMAN)
21