backup and recover

1、net configuration files
ldap.ora
listener.ora
tnsnames.ora
names.ora
sqlnet.ora

2、server configurations
dedicated server process
shared server process

3、backup and recovery
objects:
Protect the database from numberous type of failures
MTBF-mean-time-between-failure
MTTR-mean-time-to-recovery
Minimize data loss

catagories of Failures:
Statement failure
User process failure
User error
Network failure
Instance failure
Media failure

Causes of Statement Failures:
Logic error in an application
Attempt to enter invalid data into the table
Attempt an operation with insufficient privileges
Attempt to create a table but exceed allotted quota limits
Attempt an INSERT or UPDATE to a table, causing an extent to be allocated, but with insufficient free

space available in the tablespace
Resolutions for Statement Failures:
Correct the logical flow of the program.
Modify and reissue the SQL statement.
Provide the necessary database privileges.
Change the user’s quota limit by using the ALTER USER command.
Add file space to the tablespace.
Enable resumable space allocation.

Causes of User Process Failures:
The user performed an abnormal disconnect in the session.
The user’s session was abnormally terminated.
The user’s program raised an address exception,which terminated the session.
Resolution of User Process Failures:
The PMON process detects an abnormally terminated user process.
PMON rolls back the transaction and releases any resources and locks being held by it.

Possible User Errors:
SQL> DROP TABLE employees;
SQL> TRUNCATE TABLE employees;
SQL> UPDATE employees
SET salary = salary * 1.5;
SQL> COMMIT;
SQL> DELETE FROM employees;
SQL> COMMIT;
Resolution of User Errors:
Train the database users.
Recover from a valid backup.
Import the table from an export file.
Use LogMiner to determine the time of error.
Recover with a point-in-time recovery.
Use LogMiner to perform object-level recovery.
Use FlashBack to view and repair historical data.

Instance failure:
Recovery from Instance Failure:
No special recovery action is needed from DBA.
Start the instance.
Wait for the “database opened” notification.
Notify users.
Check the alert log to determine the reason for the failure.

Causes of Media Failures:
Head crash on a disk drive
Physical problem in reading from or writing to database files
File was accidentally erased
Resolutions for Media Failures:
The recovery strategy depends on which backup method was chosen and which files are affected.
If available, apply archived redo log files to recover data committed since the last backup.

Defining a Backup and Recovery Strategy
Business requirements
Operational requirements
Technical considerations
Management concurrence

Business Requirements
Mean time to recover
Mean time between failure
Evolutionary process

Operational Requirements
24-hour operations
Testing and validating backups
Database volatility

Technical Considerations
Resources: hardware, software, manpower,and time
Physical image copies of the operating system files
Logical copies of the objects in the database
Database configuration
Transaction volume which affects desired frequency of backups

Disaster Recovery Issues
How will your business be affected in the event of a
major disaster
– Earthquake, flood, or fire
– Complete loss of machine
– Malfunction of storage hardware or software
– Loss of key personnel, such as the database administrator
Do you have a plan for testing your strategy periodically

4、Instance and Media Recovery Structures
objectives:
Describe the Oracle processes, memory structures,and files relating to recovery
Identify the importance of checkpoints, redo log files, and archived log files
Describe ways to tune instance recovery

Large Pool
Can be configured as a separate memory area in the SGA to be used for:
– Oracle backup and restore operations
– I/O server processes
– Session memory for the shared servers
Is sized by the LARGE_POOL_SIZE parameter

Database Buffer Cache, DBWn,and Datafiles
Redo Log Buffer, LGWR, Redo Log Files(Multiplexed Redo Log Files),ARCn,and Archived log files

Checkpointing
Checkpoints are used to determine where recovery should start.
Checkpoint position – where recovery starts
Checkpoint queue – link list of dirty blocks
Types of Checkpoints
Full checkpoint
– All dirty buffers are written
– SHUTDOWN NORMAL, IMMEDIATE, or TRANSACTIONAL
– ALTER SYSTEM CHECKPOINT
Incremental checkpoint (Fast-Start checkpoint)
– Periodic writes
– Only write the oldest blocks
Partial checkpoint
– Dirty buffers belonging to the tablespace
– ALTER TABLESPACE BEGIN BACKUP
– ALTER TABLESPACE tablespace OFFLINE NORMAL
CKPT Process:
datafiles and control file(Multiplexed Control Files)

Database Synchronization
All datafiles (except offline and read-only) must be synchronized for the database to open.
Synchronization is based on the current checkpoint number.
Applying changes recorded in the redo log files synchronizes datafiles.
Redo log files are automatically requested by the Oracle server.

Phases for Instance Recovery:
1. Datafiles out-of-synch
2. Roll forward (redo)
3. Committed and noncommitted data in files
4. Roll back (undo)
5. Committed data in files

Tuning Crash and Instance Recovery Performance
Tuning the duration of instance and crash recovery
Tuning the phases of instance recovery
Tuning the Duration of Instance and Crash Recovery
Methods to keep the duration of instance and crash recovery within user-specified bounds:
Set initialization parameters to influence the number of
redo log records and data blocks involved in recovery.
Size the redo log file to influence checkpointing frequency.
Issue SQL statements to initiate checkpoints.
Parallelize instance recovery operations.
Initialization Parameters Influencing Checkpoints
Parameter:
FAST_START_MTTR_TARGET
LOG_CHECKPOINT_TIMEOUT
LOG_CHECKPOINT_INTERVAL

V$INSTANCE_RECOVERY
Used to monitor the mechanisms available to limit recovery I/O
Statistics from this view to calculate which parameter has the greatest influence on checkpointing.

Tuning the Phases of Crash and Instance Recovery
Tuning the roll forward phase
Tuning the rollback phase
Tuning the Rolling Forward Phase
Parallel block recovery
RECOVERY_PARALLELISM specifies the number of concurrent recovery processes
Tuning the Rolling Back Phase
Fast-start on-demand rollback
Fast-start parallel rollback
FAST_START_PARALLEL_ROLLBACK parameter

Monitoring Parallel Rollback
V$FAST_START_SERVERS
V$FAST_START_TRANSACTIONS

5、archivelog and no archivelog
Changing the Archiving Mode:
shutdown normal/immediate/transactional
startup mount
alter database archivelog
alter database open
full database backup

Automatic and Manual Archiving
Automatic archiving: LOG_ARCHIVE_START=TRUE
Manual archiving: LOG_ARCHIVE_START=FALSE

Specifying Multiple ARCn Processes
The dynamic parameter LOG_ARCHIVE_MAX_PROCESSES controls the number
of archive processes started at instance startup.
A maximum of ten ARCn processes can be specified.
The number of ARCn processes can be changed with ALTER SYSTEM.

Stop or Start Additional Archive Processes
eg. ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES = 3;

Enabling Automatic Archivingat Instance Startup
LOG_ARCHIVE_START=TRUE
LOG_ARCHIVE_MAX_PROCESSES=n

Enabling Automatic Archiving After Instance Startup
ALTER SYSTEM ARCHIVE LOG START;

Manually Archiving Online Redo Log Files
ALTER SYSTEM ARCHIVE LOG CURRENT;

Specifying the Archive Log Destination
Use LOG_ARCHIVE_DEST_n to specify up to ten archival destinations.
Use LOG_ARCHIVE_FORMAT to include the log sequence number and thread number as part of the
filename.

Specifying Multiple Archive Log Destinations
Use LOG_ARCHIVE_DEST_n to specify up to ten archival destinations, which can be on a:
Local disk
Remote standby database
log_archive_dest_1 = "LOCATION=/archive1"
log_archive_dest_2 = "SERVICE=standby_db1"

LOG_ARCHIVE_DEST_n Options:
Set archive location as MANDATORY or OPTIONAL.
Define time before retry in case of failures.
log_archive_dest_1="LOCATION=/archive/MANDATORY REOPEN"
log_archive_dest_2="SERVICE=standby_db1MANDATORY REOPEN=600"
log_archive_dest_3="LOCATION=/archive2/OPTIONAL"

Specifying a Minimum Number of Local Destinations
LOG_ARCHIVE_MIN_SUCCEED_DEST parameter
LOG_ARCHIVE_MIN_SUCCEED_DEST = 2

Obtaining Archive Log Information
Dynamic Views
V$ARCHIVE_DEST
V$ARCHIVED_LOG
V$LOG_HISTORY
V$DATABASE
V$ARCHIVE_PROCESSES
Command Line
ARCHIVE LOG LIST

6、RMAN
objectives:
Identify the features and components of RMAN
Describe the RMAN repository and control file usage
Describe channel allocation
Describe the Media Management Library interface
Connect to RMAN without the recovery catalog
Configure the RMAN environment

Recovery Manager Features
RMAN provides a flexible way to:
Back up the database, tablespaces, datafiles,control files, and archive logs
Store frequently executed backup and recovery operations
Perform incremental block-level backup
Skip unused blocks
Specify limits for backups
Detect corrupted blocks during backup
Increase performance through:
– Automatic parallelization
– Generation of less redo
– Restricting I/O for backups
– Tape streaming
Manage backup and recovery tasks

Recovery Manager Components
Target database
Server session(channel)
Server session(polling)
Server session(default)
Server session(rcvcat)
Recovery Manager(RMAN)
Recovery catalog DB

RMAN Repository: Using the Control File
RMAN repository is metadata about target database and backup and recovery operations.
RMAN repository is always stored in the control file of the target database.
CONTROL_FILE_RECORD_KEEP_TIME determines the minimum age in days of a record before it can
be overwritten.
The control file can grow in size.

Automatic Channel Allocation
Change the default device type:
RMAN> CONFIGURE DEFAULT DEVICE TYPE TO sbt;
Configure parallelism for automatic channels:
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 3;
Configure automatic channel options:
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK
FORMAT = ‘/BACKUP/RMAN/%U';
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK
MAXPIECESIZE 2G;

Manual Channel Allocation
BACKUP, COPY, RESTORE, and RECOVER commands
require at least one channel.
Allocating a channel starts a server process on the target database.
Channels affect the degree of parallelism.
Channels write to different media types.
Channels can be used to impose limits.
RMAN> RUN {
2> ALLOCATE CHANNEL c1 TYPE disk
3> FORMAT = '/db01/BACKUP/usr0520.bak';
4> BACKUP DATAFILE '/db01/ORADATA/users01.dbf';}

Types of Connections with RMAN
Target database
Recovery catalog database
Auxiliary database
– Standby database
– Duplicate database
– TSPITR instance

Connecting Without a Recovery Catalog
Starting RMAN locally
UNIX: $ ORACLE_SID=DB01; export ORACLE_SID
$ rman target / as sysdba
Windows NT: C:> set ORACLE_SID=DB01
C:> rman target / as sysdba
Starting RMAN remotely
rman target sys/target_pwd@DB01

Recovery Manager Modes
Interactive mode
– Use it when doing analysis
– Minimize regular usage
– Avoid using with log option
Batch mode
– Meant for automated jobs
– Minimize operator errors
– Set the log file to obtain information

RMAN Commands
RMAN commands are of the following types:
Stand-alone
– Executed only at the RMAN prompt
– Executed individually
– Cannot appear as subcommands within RUN
Job
– Must be within the brackets of RUN
– Executed as a group
Stand-alone or job

Job Command Example
RUN command:
RMAN> RUN {
backup
incremental level 0
format ‘/u01/db01/backup/%d_%s_%p’
fileperset 5
(database include current controlfile);
sql ‘alter database archive log current’;
}

RMAN Configuration Settings
RMAN is preset with default configuration settings
Use the CONFIGURE command to:
– Configure automatic channels
– Specify the backup retention policy
– Specify the number of backup copies to be created
– Limit the size of backup sets
– Exempt a tablespace from backup
– Enable and disable backup optimization

The CONFIGURE Command
Configure automatic channels:
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/db01/BACKUP/%U';

Implement retention policy by specifying a recovery window:
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY
2> WINDOW OF 7 days;

Implement retention policy by specifying redundancy:
RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

Configure duplexed backup sets:
RMAN> CONFIGURE DATAFILE BACKUP COPIES FOR
2> DEVICE TYPE disk TO 2;

Configure backup optimization:
RMAN> CONFIGURE BACKUP OPTIMIZATION ON;

Use the CLEAR option to return to the default value:
RMAN> CONFIGURE RETENTION POLICY CLEAR;
RMAN> CONFIGURE CHANNEL DEVICE TYPE sbt CLEAR;

RMAN> SHOW ALL;
Displays persistent configuration settings
Use the SHOW command to display:
– Automatic channel configuration settings
– Backup retention policy settings
– Number of backup copies to be created
– Backup set size limit
– Tablespace excluded from backups
– Backup optimization status
Use SHOW ALL to display all settings:

RMAN> LIST
Lists backup sets and copies of datafiles
Lists backup sets and copies of any datafile for a specified tablespace
Lists backup sets and copies containing archive logs for a specified range

List backups of all files in the database:
RMAN> LIST BACKUP OF DATABASE;

List all backup sets containing the users01.dbf datafile:
RMAN> LIST BACKUP OF DATAFILE
“/db01/ORADATA/u03/users01.dbf”;

List all copies of datafiles in the SYSTEM tablespace:
RMAN> LIST COPY OF TABLESPACE “SYSTEM”;

REPORT
Produces a detailed analysis of the repository
Produces reports to answer:
– Which files need a backup
– Which backups can be deleted
– Which files are unrecoverable

Provides three options:
– Incremental REPORT NEED BACKUP incremental 3;
– Days REPORT NEED BACKUP days 3;
– Redundancy REPORT NEED BACKUP redundancy 3;

RMAN Usage Considerations
Resources: Shared memory, more processes
Privileges given to users
– Database: SYSDBA
– Operating System: Access to devices
Remote operations
– Set up the password file
– Ensure that the password file is backed up
Globalization environment variables
Format used for the time parameters in RMAN commands

7、User-Managed Backups
Objectives:
After completing this lesson, you should be able to do the following:
Describe user-managed backup and recovery operations
Discuss backup issues associated with read-only tablespaces
Perform closed database backups
Perform open database backups
Back up the control file
Perform cleanup after a failed online backup
Use the DBVERIFY utility to detect corruption

Terminology
Whole database backup
– Target database may be open or closed
– Backup of all datafiles and the control file
Partial database backups
– Tablespace
– Datafile
– Control file
Consistent backups
Inconsistent backups

User-Managed Backup and Recovery
Files are backed up with operating system commands
Backups are restored with operating system commands
Recovery is accomplished using SQL and SQL*Plus commands

Querying Views to Obtain Database File Information
V$DATAFILE
V$CONTROLFILE
V$LOGFILE
DBA_DATA_FILES

Backup Methods(physical backup)
NOARCHIVELOG Mode: Closed database
ARCHIVELOG MODE : Closed or Open database

Consistent Whole Database Backup(Closed Database Backup)---use online or offline storage
Datafiles
Controlfiles
Redo Log files
Parameter files
Password files

Advantages of Making Consistent Whole Database Backups
Conceptually simple
Easy to perform
Require little operator interaction

Making a Consistent Whole Database Backup
a、archivelog or noarchivelog
b、SHUTDOWN IMMEDIATE
c、HOST cp <files> /backup/ (up the five type files)
d、STARTUP OPEN

Open Database Backup
datafiles
control files
password files
parameter files
Archived redo log files
(not need online redo log files)

Advantages of Making Open Database Backups
Maintains high database availability
Can be done at a tablespace or datafile level
Supports nonstop business operations

Open Database Backup Requirements
Archivelog mode
Archived redo log files

Open Database Backup Options
Tablespace backup
Datafile backup
Backup of an Online Tablespace:
SQL> ALTER TABLESPACE users BEGIN BACKUP;
SQL> !cp /…/users01.dbf /BACKUP/users01.dbf
SQL> ALTER TABLESPACE users END BACKUP;

Backup Status Information Dynamic views
V$BACKUP
V$DATAFILE_HEADER

Failure During Online Tablespace Backup
Need Ending the Online Backup
Query V$BACKUP to check backup status
Issue the ALTER DATABASE command to change the status and unfreeze the header:
ALTER DATABASE DATAFILE ‘<filename>’ END BACKUP;
Or use this command in Oracle9i:
ALTER DATABASE END BACKUP;

Read-Only Tablespace Backup
ALTER TABLESPACE query_data READ ONLY;
Read-Only Tablespace Backup Issues
Only one backup is needed after altering the tablespace to read-only.
Resume a normal backup schedule for that tablespace after making it read-write.
The control file must correctly identify the tablespace in read-only mode; otherwise you must
recover it.

Backup Issues with Logging and Nologging Options
Logging Nologging
------------------------------------------------------------------
All changes recorded to redo Minimal redo recorded
Fully recoverable from last backup Not recoverable from last backup
No additional backup May require additional backup

Manual Control File Backups
Creating a binary image
ALTER DATABASE BACKUP CONTROLFILE TO‘control1.bkp`;
Creating a text trace file
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Backing Up the Initialization Parameter File
CREATE PFILE FROM SPFILE;
CREATE PFILE = ‘/backup/init.ora’FROM SPFILE;

Verifying Backups Using the DBVERIFY Utility
%dbv file=/ORADATA/u03/users01.dbf logfile=dbv.log

8、RMAN BACKUP
RMAN Backup Concepts:
Recovery Manager backup is a server-managed backup
Recovery Manager uses Oracle server sessions for backup operations
Can back up entire database, all datafiles in a tablespace, selected datafiles, control files,

archived redo log files
Closed database backup
– Target database must be mounted (not open)
– Includes datafiles, control files, archived redo log files
Open database backup
– Tablespaces should not be put in backup mode
– Includes datafiles, control files, archived redo log files

Recovery Manager Backups
Image copy
Backup set

Characteristics of Backup Sets
The BACKUP command creates backup sets.
Backup sets usually contain more than one file.
Backup sets can be written to a disk or tape.
A restore operation is required to extract files from a backup set.
Datafile backup sets can be incremental or full.
Backup sets do not include never-used blocks.

Backup Piece
A backup piece is a file in a backup set.
A backup piece can contain blocks from more than one datafile.
Backup Piece Size
Backup piece size can be limited as follows:
RMAN> RUN {
2> ALLOCATE CHANNEL t1 TYPE 'SBT'
3> MAXPIECESIZE = 4G;
4> BACKUP
5> FORMAT 'df_%t_%s_%p' FILESPERSET 3
6> (tablespace users); }

BACKUP Command
RMAN> BACKUP
2> FORMAT '/BACKUP/df_%d_%s_%p.bus'
3> DATABASE filesperset = 2;

Multiplexed Backup Sets
Multiplex two or more datafiles into a backup set for tape streaming.

Parallelization of Backup Sets
Allocate multiple channels, optionally specify filesperset, and include many files.

Duplexed Backup Sets
Backups of Backup Sets

Archived Redo Log File Backups
Online redo log file switch is automatic.
Archived log failover is performed.
Archived Redo Log Backup Sets
Include only archived redo log files
Are always full backups
RMAN> BACKUP
2> FORMAT '/disk1/backup/ar_%t_%s_%p'
3> ARCHIVELOG ALL DELETE ALL INPUT;

Backup Constraints
The database must be mounted or open.
Online redo log backups are not supported.
Only “clean” backups are usable in NOARCHIVELOG mode.
Only “current” datafile backups are usable in ARCHIVELOG mode.

Characteristics of an Image Copy
Can be written only to a disk
Can be used for recovery immediately; does not need to be restored
Is a physical copy of a single datafile, archived log,or control file
Is most like an operating system backup (contains all blocks)
Can be part of an incremental strategy

Image Copy Example
RMAN> COPY
2> DATAFILE '/ORADATA/users_01_db01.dbf' TO
3> '/BACKUP/users01.dbf' tag=DF3,
4> ARCHIVELOG 'arch_1060.arc' TO
5> 'arch_1060.bak';

RMAN> COPY
2> DATAFILE 3 TO '/BACKUP/file3.dbf',
3> DATAFILE 1 TO '/BACKUP/file1.dbf';

Image Copy Parallelization
RMAN> CONFIGURE DEVICE TYPE disk parallelism 4;
COPY # 3 files copied in parallel
datafile 1 TO '/BACKUP/df1.dbf',
datafile 2 TO '/BACKUP/df2.dbf',
datafile 3 TO '/BACKUP/df3.dbf';
RMAN> COPY # Second copy command
datafile 4 TO '/BACKUP/df4.dbf';

Copying the Whole Database
Mount the database for a whole consistent backup.
Use the REPORT SCHEMA command to list the files.
Use the COPY command or make an image copy of each datafile.
Use the LIST COPY command to verify the copies.

Making Incremental Backups
Full backups contain all datafile blocks.
Differential incremental backups contain only modified blocks from level n or lower.
Cumulative incremental backups contain only modified blocks from level n-1 or lower.

Backup in NOARCHIVELOG Mode
1. Ensure sufficient space for the backup.
2. Shut down using the NORMAL or IMMEDIATE clause.
3. Mount the database.
4. Allocate multiple channels if not using automatic.
5. Run the BACKUP command.
6. Verify that the backup is finished and cataloged.
7. Open the database for normal use.
RMAN> BACKUP DATABASE FILESPERSET 3;

RMAN Control File Autobackups
Use the CONFIGURE CONTROLFILE AUTOBACKUP command to enable
When enabled, RMAN automatically performs a control file autobackup after BACKUP or COPY
commands
Backup is given a default name

RMAN Dynamic Views
V$ARCHIVED_LOG
V$BACKUP_CORRUPTION
V$COPY_CORRUPTION
V$BACKUP_DATAFILE
V$BACKUP_REDOLOG
V$BACKUP_SET
V$BACKUP_PIECE

Monitoring RMAN Backups
Correlate server sessions with channels with the SET COMMAND ID command.
Query V$PROCESS and V$SESSION to determine which sessions correspond to which RMAN channels.
Query V$SESSION_LONGOPS to monitor the progress of backups and copies.
Use an operating system utility to monitor the process or threads.

Miscellaneous RMAN Issues
Abnormal termination of a Recovery Manager job
Detecting physical and logical block corruption
Detecting a fractured block during open backups

9、User-Managed Complete Recovery
Objectives
After completing this lesson, you should be able to do the following:
Describe media recovery
Perform recovery in NOARCHIVELOG mode
Perform complete recovery in ARCHIVELOG mode
Restore datafiles to different locations
Relocate and recover a tablespace by using archived redo log files
Describe read-only tablespace recovery

Media Recovery
Used to recover a lost or damaged current datafile or control file
Requires explicit invocation
Operates as follows:
– Files are restored from backups
– Redo data is applied to the restored files from archived redo log files and online redo logs

Recovery Steps
a、restored datafiles
b、redo applied
c、Database containing committed and uncommitted transactions
d、undo applied
e、recoveryd database

Restoration and Datafile Media Recovery
with User-Managed Procedures
Restore files using operating system commands
Recover files using the SQL*Plus RECOVER command

Recovery in NOARCHIVELOG Mode
In NOARCHIVELOG mode, you must restore the following database files:
– All datafiles
– Control files
You can also restore the following files:
– Redo log files
– Password file
– Parameter file

Recovery in NOARCHIVELOG Mode
Advantages
– Easy to perform, with low risk of error
– Recovery time is the time it takes to restore all files
Disadvantages
– Data is lost and must be reapplied manually
– The entire database is restored to the point of the last whole closed backup

Recovery in NOARCHIVELOG Mode Without Redo Log File Backups
1. Shut down the instance.
2. Restore the datafiles and the control file from the most recent whole database backup.
3. Perform cancel-based recovery.
4. Open the database with the RESETLOGS option.

Recovery in ARCHIVELOG Mode
Complete Recovery
– Uses redo data or incremental backups
– Updates the database to the most current point in time
– Applies all redo changes
Incomplete Recovery
– Uses backup and redo logs to produce a noncurrent version of the database

Complete Recovery
Make sure that datafiles for restore are offline.
Restore only lost or damaged datafiles.
Do not restore the control files, redo log files,password files, or parameter files.
Recover the datafiles.

Complete Recovery in ARCHIVELOG Mode
Advantages
– Only need to restore lost files
– Recovers all data to the time of failure
– Recovery time is the time it takes to restore lost files and apply all archived log files
Disadvantages
– Must have all archived log files since the backup from which you are restoring

Determining Which Files Need Recovery
View V$RECOVER_FILE to determine which datafiles need recovery.
View V$ARCHIVED_LOG for a list of all archived redo log files for the database.
View V$RECOVERY_LOG for a list of all archived redo log files required for recovery.

User-Managed Recovery Procedures:
RECOVER Command
Recover a mounted database:
SQL> RECOVER DATABASE
or
SQL> RECOVER DATAFILE
‘/ORADATA/u03/users01.dbf’

Recover an open database:
SQL> RECOVER TABLESPACE users
or
SQL> RECOVER DATAFILE
‘/ORADATA/u03/users01.dbf’

Using Archived Redo Log Files During Recovery
To change archive location, use the
ALTER SYSTEM ARCHIVE LOG. . . command.
To apply redo log files automatically:
– Issue the SET AUTORECOVERY ON command before starting media recovery.
– Enter auto when prompted for an archived log file.
– Use the RECOVER AUTOMATIC. . . command.

Restoring Datafiles to a New Location
with User-Managed Procedures
Use operating system commands to restore the datafile to the new location.
Use the ALTER DATABASE RENAME FILE command to record the change in the control file.

Complete Recovery Methods
Closed database recovery for:
– System datafiles
– Undo segment datafiles
– Whole database
Open database recovery, with database initially opened (for file loss)
Open database recovery with database initially closed (for hardware failure)
Datafile recovery with no datafile backup

Complete Recovery of a Closed Database
Closed database recovery is used for:
System tablespace datafiles
Rollback segment datafiles
Whole database

Closed Database Recovery Example
1 Shut down the instance
2 Restore datafile 1(Log Sequence 144)
3 apply Archive log file to recovery
4 Open the database

Open Database Recovery When
the Database Is Initially Open Use this method when:
The database is currently open
The database will remain open during the recovery
The media failure does not affect the SYSTEM tablespace

Open Database Recovery Example
1 Take datafile 2 offline
2 Restore datafile 2 (Log Sequence 144)
3 apply Archive log file to recovery
4 Bring datafile 2 online

Open Database Recovery When
the Database Is Initially Closed Use this method when:
The database is currently closed
The database will be opened during recovery
The media failure does not affect the SYSTEM tablespace

Open Database Recovery Example
1 Mount the database
2 Take datafile 2 offline
3 Open the database
4 Restore datafile 2
5 apply Archive log file to recovery
6 Bring datafile 2 online

Recovery of a Datafile Without a Backup
Datafile is lost that was never backed up
Cannot be used when it is a file from the SYSTEM tablespace
Cannot be used if the control file has been recreated

Re-creating Lost Datafiles Without Backup
Used when missing datafile cannot be restored because it had never been backed up.
Description of missing datafile is still in data dictionary and control file.
Re-create the datafile:
SQL> ALTER DATABASE CREATE DATAFILE ‘filename’;
Re-create the datafile with a different filename:
SQL> ALTER DATABASE CREATE DATAFILE ‘filename’
AS ‘new file name’;

Recovery without a Backup Example
1 Take the datafile or tablespace offline
2 Re-create the datafile
3 apply all archive log files
4 Bring the datafile or tablespace online

Read-Only Tablespace Recovery Issues
Special considerations must be taken for read-only tablespaces when:
Re-creating a control file
Renaming datafiles
Using a backup control file

Loss of Control Files
You may need to create control files if:
All control files are lost because of a failure
The name of a database needs to be changed
The current settings in the control file need to be changed

Recovering Control Files
Methods to recover from loss of control file:
Use the current control file
Create a new control file
Use a backup control file

10、RMAN complete recovery
Objectives:
Describe the use of RMAN for restoration and recovery
Perform complete recovery in ARCHIVELOG mode
Restore datafiles to different locations
Relocate and recover a tablespace by using archived redo log files

using the RMAN RESTORE command and RECOVER command to Restoration and Datafile Media Recovery

Using RMAN to Recover a Database in ARCHIVELOG Mode
rman target /
RMAN> STARTUP MOUNT
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN;

Using RMAN to Restore Datafiles to a New Location
Use the SET NEWNAME command to restore the datafile to the new location.
SET NEWNAME FOR DATAFILE 1 to ‘/<newdir>/system01.dbf’;
Use the SWITCH command to record the change in the control file.
SWITCH DATAFILE ALL;

Recover a Tablespace
RESTORE TABLESPACE
RECOVER TABLESPACE
RMAN>run{
sql “alter tablespace users offline immediate”;
restore tablespace users;
recover tablespace users;
sql “alter tablespace users online”;
}

Relocate a Tablespace
Use the SET NEWNAME command to restore the files.
Use the SWITCH command to record the new names in the control file.
Use the RECOVER TABLESPACE command to recover the datafiles of the tablespace.

11、User-Managed Incomplete Recovery
Objectives:
Describe the steps of incomplete recovery
Perform an incomplete database recovery
Identify the loss of current online redo log files

Situations Requiring Incomplete Recovery
Complete recovery fails because an archived log is lost.
All unarchived redo log files and a datafile are lost.
User error
– An important table was dropped.
– Invalid data was committed in a table.
Current control file is lost and a backup control file must be used to open the database.

Types of Incomplete Recovery
There are three types of incomplete recovery:
– Time-based recovery
– Cancel-based recovery
– Change-based recovery
You may need to recover using a restored control file when:
– Control files are lost
– Performing incomplete recovery to a point when the database structure is different than the

current

Incomplete Recovery Guidelines
Follow all steps carefully.
Take whole database backups before and after recovery.
Always verify that the recovery was successful.
Back up and remove archived logs.

Incomplete Recovery and the Alert Log
Check the alert log before and after recovery
Contains error information, hints, and SCNs

User-Managed Procedures for Incomplete Recovery
1. Shut down and back up the database.
2. Restore all datafiles. Do not restore the control file,redo logs, password file, or parameter

file.
3. Mount the database.
4. Recover the datafiles to a point before the time of failure.
5. Open the database with RESETLOGS.
6. Perform a closed database backup.

RECOVER Command Overview
Recover a database until cancel:
RECOVER DATABASE until cancel
Recover a database until time:
RECOVER DATABASE until time ‘2001-03-04:14:22:03’
Recover using backup control file:
RECOVER DATABASE
until time ‘2001-03-04:14:22:03’
using backup controlfile

Time-Based Recovery Example
Scenario:
The current time is 12:00 p.m. on March 9, 2001.
The EMPLOYEES table has been dropped.
The table was dropped at approximately 11:45 a.m.
Database activity is minimal because most staff are currently in a meeting.
The table must be recovered.
UNTIL TIME Recovery
1 shut down and back up
2 restore all datafiles
3 mount the database
4 use archive log file to recovery until time
5 open with Resetlogs
6 back up the database

Cancel-Based Recovery Example
Scenario:
The current time is 12:00 p.m. on March 9,2001.
The EMPLOYEES table was dropped while someone was trying to fix bad blocks.
Log files exist on the same disk.
The table was dropped at approximately 11:45 a.m.
Staff are currently in a meeting.
Cancel-Based Recovery Example
Findings:
Redo logs are not multiplexed.
One of the online redo logs is missing.
The missing redo log is not archived.
The redo log contained information from 11:34 a.m.
Twenty-six minutes of data will be lost.
Users can recover their data.

Using a Backup Control File
During Recovery
Scenario:
The current time is 12:00 p.m. on March 9, 2001.
The tablespace containing the EMPLOYEES table has been dropped.
The error occurred around 11:45 a.m.
Many employee records were updated this morning, but not since 11:00 a.m.
Backups are taken every night.
Using a Backup Control File
During Recovery
Findings:
The backup from last night contains datafiles and control files required for recovery.
The EMP_TS tablespace has one datafile.
The current log sequence number is 61.
You confirm that the tablespace was dropped at 11:44:54 a.m. on March 9, 2001.
Datafile number 4 is offline.

Loss of Current Redo Log Files
If the database is closed:
Attempt to open the database.
Find the current log sequence number.
Recover the database until cancel.
Drop and re-create log files if necessary.
Open the database using RESETLOGS.
Perform a whole-database backup.

12、RMAN Incomplete recovery
Objectives:
Perform an incomplete database recovery using UNTIL TIME
Perform an incomplete database recovery using UNTIL SEQUENCE

Incomplete Recovery of a Database
Using RMAN
1. Mount the database.
2. Allocate multiple channels for parallelization.
3. Restore all datafiles.
4. Recover the database by using UNTIL TIME, UNTIL SEQUENCE, or UNTIL SCN.
5. Open the database by using RESETLOGS.
6. Perform a whole database backup.

RMAN Incomplete Recovery UNTIL TIME Example
RMAN> run {
2> allocate channel c1 type DISK;
3> allocate channel c2 type DISK;
4> set until time = ‘2000-12-09:11:44:00';
5> restore database;
6> recover database;
7> alter database open resetlogs; }

RMAN Incomplete Recovery UNTIL SEQUENCE Example
RMAN> RUN {
2> SET UNTIL SEQUENCE 120 THREAD 1;
3> ALTER DATABASE MOUNT;
4> RESTORE DATABASE;
5> RECOVER DATABASE; # recovers through log 119
6> ALTER DATABASE OPEN RESESTLOGS;
7> }

10、RMAN Maintain
objectives:
Perform cross-checking of backups and copies
Update the repository when backups have been deleted
Change the availability status of backups and copies
Make a backup or copy exempt from the retention policy
Catalog backups made with operating system commands

Cross Checking Backups and Copies
Use the CROSSCHECK command to:
Ensure repository information is synchronized with actual files
Check the status of a backup or copy
Update the repository when files have been deleted with operating system commands

The CROSSCHECK Command
Cross-check all backups in the database:
CROSSCHECK BACKUPSET OF DATABASE;
Cross-check all copies in the database:
CROSSCHECK COPY;

Deleting Backups and Copies
Use the DELETE command to:
Delete physical backups and image copies
Update repository status to DELETED
Remove records from the recovery catalog
The DELETE Command:
Delete a specific backup set:
DELETE BACKUPSET 102;
Delete an expired backup without the confirmation:
DELETE NOPROMPT EXPIRED BACKUP OF TABLESPACE users;
Delete all backups, copies, and archived redo log files based on the configured retention policy:
DELETE OBSOLETE;

Deleting Backups and Copies
Use the BACKUP … DELETE INPUT command to:
Delete input files upon successful creation of the backup set
Delete archived redo log files, datafile copies, and backup sets

Changing the Availability of RMAN Backups and Copies
Change the status of a backup or copy to Unavailable with the CHANGE… UNAVAILABLE command.
Return the status to Available with the CHANGE… AVAILABLE command.
Changing the Availability Status
Change the status of a specific datafile:
CHANGE DATAFILECOPY '/DB01/BACKUP/users01.dbf' UNAVAILABLE;
Change the status of a control file backup:
CHANGE BACKUP OF CONTROLFILE UNAVAILABLE;
CHANGE BACKUP OF CONTROLFILE AVAILABLE;
Change the status of archived redo log files:
CHANGE COPY OF ARCHIVELOG SEQUENCE BETWEEN 230 AND 240 UNAVAILABLE;

Exempting a Backup or Copy from the Retention Policy
Use the CHANGE… KEEP command to exempt a backup or copy from the retention policy.
Use the CHANGE… NOKEEP command to cancel the exemption.
Create a long-term backup:
CHANGE BACKUPSET 123 KEEP FOREVER NOLOGS;
Make a datafile exempt from the retention policy for 60 days:
CHANGE DATAFILECOPY '/DB01/BACKUP/users01.dbf' KEEP UNTIL 'SYSDATE+60';

Cataloging Archived Redo Log Files and User-Managed Backups
You can use the CATALOG command to add information to the repository about:
An operating system datafile copy
An archived redo log copy
A control file copy
The CATALOG Command
Catalog a backup taken with an operating system command:
CATALOG DATAFILECOPY '/DB01/BACKUP/users01.dbf';
Catalog archived redo log files:
CATALOG ARCHIVELOG
'/ORADATA/ARCHIVE1/arch_12.arc',
'/ORADATA/ARCHIVE1/arch_13.arc';

Uncataloging RMAN Records
Use the CHANGE… UNCATALOG command to:
Update the record in the repository to DELETED status
Delete a specific backup or copy record from the recovery catalog

Remove records for deleted archived redo log files:
CHANGE ARCHIVELOG … UNCATALOG;
Remove records for a deleted datafile:
CHANGE DATAFILECOPY '/DB01/BACKUP/users01.dbf' UNCATALOG;

13、Recovery Catalog Creation and Maintenance
Objectives:
Describe the contents of the recovery catalog
List the RMAN features which require the recovery catalog
Create the recovery catalog
Maintain the recovery catalog by using RMAN commands
Use RMAN to register, resynchronize, and reset a database
Query the recovery catalog to generate reports and lists
Create, store, and run scripts
Describe methods for backing up and recovering the recovery catalog

Recovery Catalog Contents
The recovery catalog is an optional repository containing information on:
Datafile and archived redo log file backup sets and backup pieces
Datafile copies
Archived redo log files
The physical structure of the target database
Persistent RMAN configuration settings
Stored job scripts

Benefits of Using a Recovery Catalog
The following features are available only when you use a recovery catalog:
Metadata about multiple target databases in one catalog
Metadata about multiple incarnations of a single target database
Historical metadata
Reporting on the target database at a noncurrent time

Create Recovery Catalog
1. Create tablespace
2. Create catalog owner
3. Grant privileges
4. Create catalog
5. Connect to target database
6. Register target database

Connecting Using a Recovery Catalog
Example on UNIX
$ ORACLE_SID=db01; export ORACLE_SID
$ rman target /
RMAN> connect catalog rman_db01/rman_db01@catdb

Example on Windows NT
C:> set ORACLE_SID=db01
C:> rman target /
RMAN> connect catalog rman_db01/rman_db01@catdb

Resynchronization of the Recovery Catalog
Resynchronization of the recovery catalog happens:
Automatically with BACKUP and COPY commands
Manually with RESYNC CATALOG command

Using RESYNC CATALOG for Resynchronization
Issue the RESYNC CATALOG command when you:
Add or drop a tablespace
Add or drop a datafile
Relocate a database file
$ rman target / catalog rman/rman@catdb
RMAN> RESYNC CATALOG;

Resetting a Database Incarnation
Use the RESET DATABASE command:
– To direct RMAN to create a new database incarnation record
– To distinguish between opening with RESETLOGS and an accidental restore operation of an old

control file
Open with RESETLOGS after RESET DATABASE

Viewing the Recovery Catalog
Data dictionary views:
RC_DATABASE
RC_DATAFILE
RC_STORED_SCRIPT
RC_STORED_SCRIPT_LINE
RC_TABLESPACE

Script Examples
Use CREATE SCRIPT to store a script.
RMAN> create script Level0Backup {
backup
incremental level 0
format ‘/u01/db01/backup/%d_%s_%p’
fileperset 5
(database include current controlfile);
sql ‘alter database archive log current’;
}
Use EXECUTE SCRIPT to run a script.
RMAN > run {execute script Level0Backup;}

Managing Scripts
Use REPLACE SCRIPT to rewrite a script
RMAN> REPLACE SCRIPT Level0Backup {
…
fileperset 3
…
}
Use DELETE SCRIPT to remove a script
RMAN> DELETE SCRIPT Level0Backup;
Use PRINT SCRIPT to display a script
RMAN> PRINT SCRIPT Level0Backup;

Backup of Recovery Catalog
Whole database backup of the database containing the recovery catalog
Tablespace backup of the tablespace containing the recovery catalog
Export:
– If catalog database is not very large, you can export the database at regular intervals.
– If catalog database is large, export the schema containing the recovery catalog.

Recovering the Recovery Catalog
Create a database from a previous backup of the recovery catalog database.
Relocate the catalog into another database and import the data.
Import the entire database from an export.