Transcript Performing User-Managed Incomplete Recovery

Performing User-Managed Backup and
Recovery
Copyright © 2007, Oracle. All rights reserved.
Objectives
After completing this lesson, you should be able to:
• Describe the difference between user-managed and servermanaged backup and recovery
• Manually back up a data file, tablespace, and database
• Back up and recover a control file
• Perform user-managed complete database recovery
• Perform user-managed incomplete database recovery
• Recover from a lost TEMP file
• Recover from a lost redo log group
6-2
Copyright © 2007, Oracle. All rights reserved.
Restoring and Recovering
Restore
Redo log
Recover
6-3
Copyright © 2007, Oracle. All rights reserved.
Causes of File Loss
File loss can be caused by:
• User error
• Application error
• Media failure
6-4
Copyright © 2007, Oracle. All rights reserved.
Critical Versus Noncritical
A noncritical file loss is one where the database can continue to
function.
You fix the problem by taking one of these actions:
• Create a new file.
• Rebuild the file.
• Recover the lost or damaged file.
6-5
Copyright © 2007, Oracle. All rights reserved.
Losing a TEMPFILE
SQL statements that require TEMP space to execute fail if one
of the tempfiles is missing.
SQL> select * from big_table order by
1,2,3,4,5,6,7,8,9,10,11,12,13;
select * from big_table order by
1,2,3,4,5,6,7,8,9,10,11,12,13
*
ERROR at line 1:
ORA-01565: error in identifying file
'/u01/app/oracle/oradata/orcl/temp01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
6-6
Copyright © 2007, Oracle. All rights reserved.
Recovering from a TEMPFILE Loss
SYSTEM
USERS
Redo log Redo log
file 1A
file 1B
SYSAUX
INDEX
Redo log Redo log
file 2A
file 2B
UNDO
TEMP
Control
files
Parameter
file
Password
file
6-7
Copyright © 2007, Oracle. All rights reserved.
Log Group Status: Review
A redo log group has a status of one of the
following values at any given time:
• CURRENT: The LGWR process is currently
writing redo data to it.
• ACTIVE: It is no longer being written to,
but it is still required for instance recovery.
• INACTIVE: It is no longer being written to,
and it is no longer required for instance
recovery.
6-8
Copyright © 2007, Oracle. All rights reserved.
Recovering from the Loss of a
Redo Log Group
Start
Yes
Done
Fix
media?
Inactive
Group
status
Active
No
Perform
checkpoint
Clear log file
Yes
CKPT
successful?
No
Restore and
recover
until cancel
6-9
Copyright © 2007, Oracle. All rights reserved.
Current
Clearing a Log File
Start
Yes
ALTER DATABASE CLEAR LOGFILE ...
Log file
archived?
No
Needed for
data file?
Yes
No
ALTER DATABASE CLEAR LOGFILE UNARCHIVED ...
ALTER DATABASE CLEAR LOGFILE UNARCHIVED ... UNRECOVERABLE DATAFILE
6 - 10
Copyright © 2007, Oracle. All rights reserved.
Re-Creating Indexes
Use options to reduce the time it takes to create the index:
• PARALLEL
• NOLOGGING
SQL> CREATE INDEX rname_idx
2 ON hr.regions (region_name)
3 PARALLEL 4;
6 - 11
Copyright © 2007, Oracle. All rights reserved.
Recovering from a Lost Index Tablespace
• A tablespace that contains only indexes may be recovered
without performing a RECOVER task.
• If a data file that belongs to an index-only tablespace is lost,
it may be simpler to re-create the tablespace and re-create
the indexes.
6 - 13
Copyright © 2007, Oracle. All rights reserved.
Authentication Methods
for Database Administrators
Remote database
administration
Do you
have a secure
connection?
Local database
administration
Yes
No
6 - 14
Do you want
to use OS
authentication?
Yes
No
Copyright © 2007, Oracle. All rights reserved.
Use OS
authentication.
Use a
password file.
Re-Creating a Password
Authentication File
SQL> grant sysdba to admin2;
grant sysdba to admin2
*
ERROR at line 1:
ORA-01994: GRANT failed: password file missing or disabled
To recover from the loss of a password file:
1. Re-create the password file by using orapwd.
$ orapwd file=$ORACLE_HOME/dbs/orapworcl password=ora entries=5
2. Add users to the password file and assign appropriate
privileges to each user.
6 - 15
Copyright © 2007, Oracle. All rights reserved.
Comparing Complete and Incomplete Recovery
Recovery can have two kinds of scope:
• Complete recovery: Brings the database up to the present,
including all committed data changes made to the point in
time when the recovery was requested
• Incomplete recovery: Brings the database up to a specified
point in time in the past, before the recovery operation was
requested
Time of
crash
Complete
recovery
Incomplete
recovery
Restore from
this backup
6 - 17
Missing transactions
after incomplete recovery
Recovery
task started
at this time
Copyright © 2007, Oracle. All rights reserved.
Complete Recovery Process
Archived
log Archived
log
Online
Redo log
Changes applied
2
1
Restored
data files
6 - 18
Database
opened
Undo applied
4
5
3
Data files containing
committed and uncommitted
transactions
Copyright © 2007, Oracle. All rights reserved.
6
Recovered
data files
Incomplete Recovery Process
Archived
log Archived
log
Online
Redo log
X
X
Changes applied to point in time (PIT) Database
opened
2
1
Restored data
files from as far
back as necessary
6 - 19
4
3
Data files containing
committed and
uncommitted
transactions up to PIT
Copyright © 2007, Oracle. All rights reserved.
Undo applied
5
6
PIT-recovered
data files
Types of Backup and Recovery Practices
Types of database backup and recovery are:
• User-managed: Does not use RMAN.
– Uses OS commands to move files around
– Keeps some records of backup activity manually
• Server-managed: Uses RMAN
6 - 21
Copyright © 2007, Oracle. All rights reserved.
Performing a User-Managed
Backup of the Database
No
Yes
ARCHIVELOG
mode?
Shutdown
Put tablespaces into backup mode
Copy files
6 - 22
Copyright © 2007, Oracle. All rights reserved.
The Need for Backup Mode
A DML statement updates
a database block:
Different parts of the block are
written to at different times:
Copy
data file
while
online
Database block
t1
t2
If the block is copied at time t2, then the block is fractured.
6 - 23
Copyright © 2007, Oracle. All rights reserved.
t3
Identifying Files to Manually Backup
SQL> select name from v$datafile;
NAME
-----------------------------------------------------------------------/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_36mky81f_.dbf
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_36mky81p_.dbf
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_36mky857_.dbf
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_36mky876_.dbf
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_36ml2cmh_.dbf
/u01/app/oracle/oradata/ORCL/datafile/survey01.dbf
SQL> select name from v$controlfile;
NAME
-----------------------------------------------------------------------/u01/app/oracle/oradata/ORCL/controlfile/o1_mf_36ml1f8x_.ctl
/u01/app/oracle/flash_recovery_area/ORCL/controlfile/o1_mf_36ml1fkk_.ctl
6 - 24
Copyright © 2007, Oracle. All rights reserved.
Manually Backing Up a NOARCHIVELOG Database
• Shut down the database:
SQL> SHUTDOWN IMMEDIATE
• Copy the data files to the backup location:
$ cp $ORACLE_BASE/ORCL/datafile/*.dbf \
> /u02/backup/datafile
• Start up the database:
SQL> STARTUP
6 - 25
Copyright © 2007, Oracle. All rights reserved.
Manually Backing Up an ARCHIVELOG Database
SQL> select file_name, tablespace_name from dba_data_files;
• Identify tablespaces
FILE_NAME
TABLESPACE_NAME
----------------------------------------------------------and their datafiles:
/u01/app/oracle/oradata/orcl/users01.dbf
USERS
/u01/app/oracle/oradata/orcl/users02.dbf
USERS
/u01/app/oracle/oradata/orcl/undotbs1.dbf
UNDOTBS1
/u01/app/oracle/oradata/orcl/sysaux01.dbf
SYSAUX
/u01/app/oracle/oradata/orcl/system01.dbf
SYSTEM
/u01/app/oracle/oradata/orcl/example01.dbf
EXAMPLE
For each tablespace:
• Put the tablespace into backup mode:
SQL> ALTER TABLESPACE users BEGIN BACKUP;
• Copy the data files for that tablespace to the backup location:
$ cp $ORACLE_HOME/oradata/orcl/users*.dbf /u02/backup/datafile
• Bring the tablespace out of backup mode:
SQL> ALTER TABLESPACE users END BACKUP;
6 - 26
Copyright © 2007, Oracle. All rights reserved.
Backing Up the Control File
Back up the control file:
• As an image copy, to a specifically named file:
SQL> ALTER DATABASE BACKUP CONTROLFILE TO
2> '/u01/backup/controlfile.bak';
Database altered.
• By generating a script that re-creates it, in a trace file:
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
Database altered.
6 - 27
Copyright © 2007, Oracle. All rights reserved.
Performing User-Managed
Complete Database Recovery: Overview
User-managed complete database recovery:
• Recovers the database to the most recent SCN
• Can be done with the entire database at once, or a data file
or tablespace at a time
• Requires a current control file
• Requires backups of all files to be recovered
• Requires all archive logs up to the present
6 - 28
Copyright © 2007, Oracle. All rights reserved.
Performing Complete Closed Database
Recovery: Overview
Yes
Database
open?
Query for files to recover.
No
Query for needed archive logs.
Shutdown.
Repair hardware.
Restore damaged files and archive logs.
Mount database.
Recover data files.
Bring data files online.
Open database.
6 - 29
Copyright © 2007, Oracle. All rights reserved.
Identifying Recovery-Related Files
• Identify data files that need to be recovered:
SQL> SELECT file#, error FROM v$recover_file;
• Identify archive log files that are required to complete
recovery:
SQL> SELECT archive_name FROM v$recovery_log;
6 - 30
Copyright © 2007, Oracle. All rights reserved.
Restoring Recovery-Related Files
Data files
1
Archive logs
2
/disk1/datafile.dbf
3
6 - 31
/disk2/datafile.dbf
ONLINE
Copyright © 2007, Oracle. All rights reserved.
Applying Redo Data
1. Apply redo data using the RECOVER command:
SQL> RECOVER AUTOMATIC FROM '/u01/arch_temp' DATABASE;
Apply each redo log
without prompting.
Alternate location for
restored archive log files
Could be DATABASE,
TABLESPACE, or DATAFILE
2. Open the database:
SQL> ALTER DATABASE OPEN;
6 - 33
Copyright © 2007, Oracle. All rights reserved.
Performing Complete Open Database Recovery
Query for files to recover.
Take damaged data files offline.
Query for needed archive logs.
Done
Diagnose media problem.
Yes
Can bring
data files
online?
No
Restore damaged files and archive logs.
Recover data files.
Bring tablespaces online.
6 - 34
Copyright © 2007, Oracle. All rights reserved.
Performing User-Managed
Incomplete Recovery: Overview
Recover the database to a past point in time in the following
situations:
• You want the database to be in the state that existed before
a user error or an administrative error occurred.
• The database contains corrupt blocks.
• You are unable to perform complete database recovery
because some of the redo log files are missing.
• You want to create a test database that is in the state at
some time in the past.
• One or more unarchived redo log files and a data file are
lost.
6 - 36
Copyright © 2007, Oracle. All rights reserved.
Choosing an Incomplete Recovery Method
Indicate when to stop applying redo data by:
• Specifying a time at which to stop
• Specifying an SCN at which to stop
• Issuing a CANCEL command while the recovery is executing
Time = August 23, 2007 01:00 AM
CANCEL
SCN = 1200
Applying redo data
6 - 37
Copyright © 2007, Oracle. All rights reserved.
Now
Performing User-Managed
Incomplete Recovery
• Recover a database until time:
SQL> RECOVER DATABASE UNTIL
2 TIME '2005-12-14:12:10:03';
• Recover a database until cancel:
SQL> RECOVER DATABASE UNTIL CANCEL;
6 - 38
Copyright © 2007, Oracle. All rights reserved.
Performing User-Managed
Incomplete Recovery: Steps
To perform user-managed incomplete recovery, follow these
steps:
1. Shut down the database.
2. Restore data files.
3. Mount the database.
4. Recover the database.
5. Open the database with the RESETLOGS option.
6 - 40
Copyright © 2007, Oracle. All rights reserved.
User-Managed Time-Based Recovery: Example
This is the scenario:
• A job ran in error, and its effects have to be undone.
• This happened 15 minutes ago, and there has been little
database activity since then.
• You decide to perform incomplete recovery to restore the
database back to its state as of 15 minutes ago.
SQL>
$ cp
SQL>
SQL>
SQL>
6 - 41
SHUTDOWN IMMEDIATE
/BACKUP/* /u01/db01/ORADATA
STARTUP MOUNT
RECOVER DATABASE UNTIL TIME '2005-11-28:11:44:00';
ALTER DATABASE OPEN RESETLOGS;
Copyright © 2007, Oracle. All rights reserved.
User-Managed Cancel-Based Recovery: Example
The scenario is the same as the one for the time-based
example, except for these 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 AM.
• Twenty-six minutes of data are lost.
• Users can reenter their data manually.
6 - 43
Copyright © 2007, Oracle. All rights reserved.
User-Managed Cancel-Based Recovery: Example
Recover the database as follows:
• Shut down the database.
• Restore all data files from the most recent backup.
• You already have a valid backup, so mount the database.
• Execute RECOVER DATABASE UNTIL CANCEL.
• Execute ALTER DATABASE OPEN RESETLOGS to open the
database.
6 - 44
Copyright © 2007, Oracle. All rights reserved.
Recovering a Read-Only Tablespace
Special user-managed backup and recovery considerations for
a read-only tablespace:
• You do not have to put it in backup mode in order to make a
copy of its data files.
• You do not have to take the tablespace or data file offline
before making a copy of it.
6 - 45
Copyright © 2007, Oracle. All rights reserved.
Recovering NOLOGGING Database Objects
Redo log
SQL> CREATE TABLE sales_copy NOLOGGING;
SQL> INSERT /*+ APPEND */ INTO sales_copy
2 SELECT * FROM sales_history;
6 - 46
Copyright © 2007, Oracle. All rights reserved.
Recovering from the Loss of
All Control File Copies: Overview
Current
Backup
Available
Restore backup control file,
Restore backup control file,
perform complete recovery, OPEN perform complete recovery,
RESETLOGS
OPEN RESETLOGS
Unavailable
Re-create control file, OPEN
RESETLOGS
Restore backup control file,
perform incomplete recovery,
OPEN RESETLOGS
Online log status
6 - 47
Data file status
Copyright © 2007, Oracle. All rights reserved.
Recovering the Control File
to the Default Location
Database
open?
Yes
No
Repair hardware.
Restore control file.
SHUTDOWN ABORT
STARTUP MOUNT
Open database
using
RESETLOGS.
Start database recovery.
Archivelog
missing?
Yes
6 - 48
Copyright © 2007, Oracle. All rights reserved.
No
Specify online log.
Summary
In this lesson, you should have learned how to:
• Describe the difference between user-managed and servermanaged backup and recovery
• Manually back up a data file, tablespace, and database
• Back up and recover a control file
• Perform user-managed complete database recovery
• Perform user-managed incomplete database recovery
• Recover from a lost TEMP file
• Recover from a lost redo log group
6 - 49
Copyright © 2007, Oracle. All rights reserved.
Practice 6 Overview:
Performing User-Managed Recovery
This practice covers the following topics:
• Backing up a NOARCHIVELOG database
• Recovering from lost control files
• Recovering from the loss of a redo log group
6 - 50
Copyright © 2007, Oracle. All rights reserved.