Oracle 9i. User-Managed Backup and Recovery Guide

Ashdown L.

This manual is intended for database administrators who perform the following tasks:■ Perform backups and restore database files with operating system utilities, and perform recovery with the SQL*Plus RECOVER command.■ Use Recovery Manager as a backup and recovery solution but choose to back up some files with user-managed methods.To use this document, you need to be familiar with relational database concepts and basic database administration as described in these manuals:■ Oracle9i Database Concepts■ Oracle9i Backup and Recovery Concepts■ Oracle9i Database Administrator’s GuideYou should also be familiar with the operating system environment under which you are running Oracle.

---

Table of contents :
Send Us Your Comments……Page 9
Preface……Page 11
What’s New in User-Managed Backup and Recovery?……Page 17
1 Introduction to User-Managed Backup and Recovery……Page 21
Overview of User-Managed Backup and Recovery……Page 22
About User-Managed Backups……Page 23
About User-Managed Restore and Recovery……Page 26
2 Making User-Managed Backups……Page 29
Listing Database Files Before a Backup……Page 30
Determining Datafile Status for Online Tablespace Backups……Page 31
Making Consistent Whole Database Backups……Page 32
Making User-Managed Backups of Offline Tablespaces and Datafiles……Page 34
Making User-Managed Backups of Online Tablespaces and Datafiles……Page 35
Making User-Managed Backups of Online Read/Write Tablespaces……Page 36
Making Multiple User-Managed Backups of Online Read/Write Tablespaces……Page 37
Ending a Backup After an Instance Failure or SHUTDOWN ABORT……Page 39
Making User-Managed Backups of Read-Only Tablespaces……Page 42
Making User-Managed Backups of Undo Tablespaces……Page 43
About the Suspend/Resume Feature……Page 44
Making Backups in a Suspended Database……Page 45
Backing Up the Control File to a Trace File……Page 47
Backing Up to Raw Devices on UNIX……Page 50
Backing Up to Raw Devices on NT……Page 53
Using the DBVERIFY Utility……Page 55
Using Export……Page 56
Making User-Managed Backups of Miscellaneous Oracle Files……Page 57
3 Performing User-Managed Restore Operations……Page 59
About User-Managed Restore Operations……Page 60
Recording the Locations of Datafiles, Control Files, and Online Redo Logs……Page 61
Recording the Locations of Backup Files……Page 62
Determining Which Datafiles Require Recovery……Page 63
Restoring Datafiles……Page 64
Re-Creating Datafiles When Backups Are Unavailable……Page 65
Restoring and Re-Creating Control Files……Page 66
Losing a Member of a Multiplexed Control File……Page 67
Losing All Members of a Multiplexed Control File When a Backup Is Available……Page 68
Losing All Current and Backup Control Files……Page 71
Restoring Archived Redo Logs……Page 73
4 Performing User-Managed Media Recovery……Page 75
Applying Logs Automatically with the RECOVER Command……Page 76
Recovering When Archived Logs Are in the Default Location……Page 79
Recovering When Archived Logs Are in a Nondefault Location……Page 80
Overriding the Archived Log Destination……Page 81
Responding to Unsuccessful Application of Redo Logs……Page 82
Performing Closed Database Recovery……Page 83
Performing Datafile Recovery in an Open Database……Page 86
Restoring Datafiles Before Performing Incomplete Recovery……Page 90
Performing Cancel-Based Incomplete Recovery……Page 92
Performing Time-Based Incomplete Recovery……Page 94
Performing Change-Based Incomplete Recovery……Page 95
Recovering a Database in NOARCHIVELOG Mode……Page 96
Restoring the Database to its Default Location……Page 97
Restoring the Database to a New Location……Page 98
Performing Media Recovery in Parallel……Page 99
Opening the Database After User-Managed Media Recovery……Page 100
About RESETLOGS Operations……Page 101
Determining Whether to Reset the Online Redo Logs……Page 102
Following Up After a RESETLOGS Operation……Page 104
Recovering a Backup Created Before a RESETLOGS……Page 105
Interrupting User-Managed Media Recovery……Page 107
User-Managed Recovery of Unrecoverable Tables and Indexes……Page 108
User-Managed Recovery of Read-Only Tablespaces with a Noncurrent Control File……Page 109
5 Troubleshooting User-Managed Media Recovery……Page 111
About User-Managed Media Recovery Problems……Page 112
Trying to Fix the Recovery Problem Without Corrupting Blocks: Phase 2……Page 114
Deciding Whether to Allow Recovery to Corrupt Blocks: Phase 3……Page 116
Allowing Recovery to Corrupt Blocks: Phase 4……Page 117
How Trial Recovery Works……Page 118
Initiating Trial Recovery……Page 119
6 User-Managed Media Recovery Scenarios……Page 121
Losing Datafiles in ARCHIVELOG Mode……Page 122
Recovering Through an Added Datafile: Scenario……Page 123
Recovering Transportable Tablespaces: Scenario……Page 124
Recovering After the Loss of Online Redo Log Files: Scenarios……Page 125
Recovering After Losing a Member of a Multiplexed Online Redo Log Group……Page 126
Recovering After the Loss of All Members of an Online Redo Log Group……Page 127
Recovering After the Loss of Archived Redo Log Files: Scenario……Page 132
Performing Media Recovery in a Distributed Environment: Scenario……Page 133
Coordinating Time-Based and Change-Based Distributed Database Recovery……Page 134
7 Performing User-Managed TSPITR……Page 137
TSPITR Terminology……Page 138
TSPITR Methods……Page 139
Preparing for Tablespace Point-in-Time Recovery: Basic Steps……Page 140
Step 2: Identify All of the Files in the Recovery and Auxiliary Set Tablespaces……Page 141
Step 3: Determine Whether Objects Will Be Lost……Page 142
Step 6: Create the Initialization Parameter File for the Auxiliary Instance……Page 143
Restoring and Recovering the Auxiliary Database: Basic Steps……Page 145
Restoring and Recovering the Auxiliary Database on the Same Host……Page 146
Restoring and Recovering the Auxiliary Database on a Different Host with the Same Path Names……Page 148
Step 1: Unplugging the Tablespaces from the Auxiliary Database……Page 150
Step 2: Transporting the Tablespaces into the Primary Database……Page 151
Performing Partial TSPITR of Partitioned Tables……Page 152
Step 5: Create Tables at Auxiliary Database……Page 153
Step 10: Back Up the Recovered Tablespaces in the Primary Database……Page 154
Step 3: Delete Records From the Partitioned Table……Page 155
Step 9: Insert Standalone Tables into Partitioned Tables……Page 156
Step 1: Drop the Lower of the Two Partitions at the Primary Database……Page 157
Steps 2: Follow Same Procedure as for Partial TSPITR of Partitioned Tablespaces……Page 158
Index……Page 159