Performing Backups and Recovering your Database using Oracle9i Recovery
Manager
Module Objectives
Purpose
In this module, you will learn how to use Oracle9i
Recovery Manager to backup and restore a database.
Objectives
After completing this module, you should be able to:
Prerequisites
Before starting this module, you should have complete the following:
Reference Material
The following is a list of useful reference material if you want additional
information about the topics in this module:
 |
Documentation: Oracle9i
Recovery Manager User's Guide
|
Recovery Manager Overview
Recovery Manager is Oracle’s utility to manage the backup, and more
importantly the recovery, of the database. It eliminates operational complexity
while providing superior performance and availability of the database.
Recovery Manager debuted with Oracle8 to provide DBAs an integrated backup
and recovery solution.
Recovery Manager determines the most efficient method of executing the
requested backup, restore, or recovery operation and then executes these
operations in concert with the Oracle database server. Recovery Manager
and the server automatically identify modifications to the structure of
the database and dynamically adjust the required operation to adapt to
the changes.
Enhanced Enterprise Manager Support
A Recovery Manager Graphical User Interface is built into Oracle's Enterprise
Manager. Enterprise Manager is the central management framework providing
a robust console, a rich set of tools, and the extensibility to detect,
solve, and simplify the problems of any managed environment. Enterprise
Manager
is included as part of the Oracle Database Server.
Scheduling of backup jobs and RMAN-specific tasks via the Enterprise Manager
Job System enhances the Backup and Recovery facility and provides the
flexibility in scheduling the backup task at specified intervals, on specified
day of the week, or on a specified day of the month. Oracle Enterprise
Manager (EM) supports Backup and Recovery features required by users.
- Backup Configurations to customize and save commonly used configurations
for repeated use
- Backup and Recovery wizards to walk the user through the steps of
creating a backup script and submitting it as a scheduled job
- Backup Job Library to save commonly used backup jobs that can be retrieved
and applied to multiple targets
- Backup Job Task to submit any RMAN job using a user-defined RMAN script.
Integrated with 3rd Party Media Management Vendors
Organizations rely on Oracle to provide solutions for very large critical
systems. In addition to needing databases capable of handling large amounts
of data and complex queries, these organizations also need robust backup
and recovery technology. Recovery of data quickly and reliably is paramount
should some aspect of the system fail. To address these needs, Oracle
has created the Backup Solutions Program (BSP), a cooperative program
designed to facilitate tighter integration between Oracle's backup products
and those of third-party media management vendors.
Why learn two tools to backup your enterprise systems? Use your preferred
third-party backup software to backup all of your system files and the
Oracle database. Use a product that is integrated with Recovery Manger
to ensure your database is precisely backed up; "One tool - One interface".
Together, Oracle and media management vendors provide robust easy-to-use
database backup and recovery solutions to customers with high-end requirements.
To view the current members of the Backup Solutions Program, go to http://otn.oracle.com/deploy/availability.
Module Layout
The RMAN lessons are broken into three parts:
- Configuration and Backup
- Database Recovery
- Advanced Features
Lesson 1 Configuration and Backup
must be completed before starting Database
Recovery or Advanced Features.
After completing the Configuration and Backup lesson you can move to Database
Recovery or Advanced Features.
The Recovery Manager GUI interface is built into Oracle's Enterprise
Manager. It provides the capability to backup, recover, and perform catalog
maintenance on your Oracle database.
The first step in backing up your database is to create a backup configuration.
The backup configuration is used at backup time to determine where the
backup will be made.
| 1. |
Open three windows, one for SQL*Plus, two command line windows
of which one is for RMAN and the other is for scripts.
|
| 2. |
Verify the ORCL instance is started. Click on the SQL*Plus
icon and connect as SYS. If the instance is idle start it.
connect sys/oracle@orcl.world as sysdba
If SQL*Plus returns: 'Connect to an idle instance' then type
startup
Verify that the database is in archive log mode.
archive log list;
|
Starting RMAN
RMAN is a client executable that is installed with the Oracle database
server. You can connect it to a target database, and then use server sessions
on the target database to back up, restore, and recover files.
The following options can be used to start RMAN:
Specify the database connection information at the command line:
rman TARGET SYS/target_pwd@target_str # connects in NOCATALOG mode
rman TARGET / CATALOG rman/rman@rcat
rman TARGET / CATALOG rman/rman@rcat AUXILIARY sys/aux_pwd@aux_str
Omit the database connection at the command line, and use the CONNECT
command in your
RMAN scripts:
rman
In both cases, RMAN displays an RMAN> prompt at which you can enter
commands or run a command file.
RMAN can connect to the following types of databases:
Target database
The database that you will use RMAN to back up and restore: RMAN connects
to the target database as SYSDBA. If you do not have this privilege, then
the connection fails. You can connect with the SYSDBA privilege by using
a password file or using operating system authentication.
Recovery catalog database
This database is optional: you can use RMAN with the default NOCATALOG
option, in which case RMAN uses the control file as the exclusive repository
of metadata. A recovery catalog is a set of tables containing RMAN metadata.
RMAN obtains the metadata from the target database control file. If you
choose to use a recovery catalog, refer to Oracle9i Recovery Manager User's
Guide for complete instructions.
Auxiliary database
The auxiliary instance is only used for duplicate and standby database
created with the DUPLICATE command and for tablespace point-in-time-recovery
| 3. |
Connect to RMAN. From RMAN, type the following command to start
the
RMAN client and create a server session on the target database ORCL.
rman target sys/oracle@orcl.world
|
Configuring the RMAN Environment
You can configure persistent settings in the RMAN environment. The configuration
setting is done once, and used by RMAN to perform all subsequent operations.
Display the preconfigured settings by typing the command SHOW ALL.
There are various parameters that can be used to configure RMAN operations
to suit your needs. Some of the things that you can configure are:
- Required number of backups of each datafile
- Number of server processes that will do backup/restore operations
in parallel
- Directory where on-disk backups will be stored
You can return any CONFIGURE command to its default setting by running
the command with the CLEAR option, as in:
CONFIGURE CHANNEL DEVICE TYPE sbt CLEAR;
CONFIGURE RETENTION POLICY CLEAR;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;
You will configure the following parameters:
- 3 backups of each datafile
- Backups to be stored on disk in the D:\ORACLE\ORCLBACKUP directory
- 2 server processes to do backup/restore operations in parallel
- Use the new controlfile autobackup feature
- Set backup optimization on
| 4. |
If D:\ORACL\ORCLBACKUP
does not exist please create is now. From the command prompt, in
a windows where you are not connected to RMAN:
D:
cd\ORALCE
mkdir ORCLBACKUP
cd\wkdir
|
| 5. |
Configure the RMAN environment by typing the following commands
from RMAN.
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT
'd:\oracle\orclbackup\ora_df%t_s%s_s%p';
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO
'd:\oracle\orclbackup\ora_cf%F';
CONFIGURE BACKUP OPTIMIZATION ON;
|
| 6. |
To view the current configuration settings, type the following
command.
SHOW ALL;
|
Backup the Database
Use the BACKUP command to back up files to the configured default device
by using the configured channels. For example, use this command to back
up the database and all archived logs:
BACKUP DATABASE PLUS ARCHIVELOG;
RMAN stores its backups in backup sets. A backup set is a logical structure
that contains one or more backup pieces, which are the physical files
containing the data. A backup set usually contains only one backup piece.
Only RMAN can create and restore backup sets. You can also back up individual
tablespaces, database files, and backup sets.
Incremental Backups
In an incremental backup strategy, you first create a level 0 backup,
which is a whole backup of the database. For example:
BACKUP INCREMENTAL LEVEL 0 DATABASE;
Later, you can create backups at a higher "level." In a cumulative
incremental backup, RMAN only backs up those blocks that are different
between the specified level n and the most recent level n-1.
Restarting Backups
If a backup fails for any reason, backing up some but not all specified
files, then you can use the NOT BACKED UP SINCE option on the BACKUP command
to continue from the point of failure. This example backs up all files
not backed up within the last day:
BACKUP DATABASE NOT BACKED UP SINCE TIME 'SYSDATE-1';
Testing Backups
You can run a test RMAN backup that does not generate any output. The
test checks datafiles for physical and logical corruption and that all
database files exist and are in the correct locations. For example:
BACKUP VALIDATE DATABASE ARCHIVELOG ALL;
| 7. |
Generate archive logs to backup. From the command prompt
execute the batch file switch5.bat,
location in D:\wkdir,
by typing the following:
switch5 orcl.world
|
| 8. |
Backup database files and archive logs. While the backup is going
on, we can also monitor the progress.
In RMAN type:
BACKUP DATABASE PLUS ARCHIVELOG;
In SQL*Plus where you have already connected to the ORCL database,
type the following command to view the progress of the RMAN job.
At first you may receive 'no row returned.'
@monitorjob
The columns from the resulting SQL statement which is run in MONITORJOB,
refer to RMAN jobs in this manner:
SOFAR – number of blocks read/or put into the buffer by
the RMAN process
TOTALWORK – total number of blocks to be read and/or put into
the buffer
%COMPLETE – percentage of SOFAR/TOTALWORK * 100
|
| 9. |
Backup individual datafiles. First, let’s take a look at the
schema via RMAN. From RMAN, type the following commands. What does
the database schema look like?
REPORT SCHEMA;
|
| 10. |
RMAN can backup at the database, tablespace, and datafile level.
First you will backup a datafile. Backup the database file which
corresponds to the
Example tablespace. In the example below the Example
tablespace is datafile 5.
BACKUP DATAFILE 5;
|
| 11. |
Backup at the Tablespace Level. From RMAN, type the following
commands.
BACKUP TABLESPACE EXAMPLE;
|
| 12. |
Incremental backups can be made by RMAN. From RMAN, type the following
command to make an incremental level 0 backup of the database.
BACKUP INCREMENTAL LEVEL 0 DATABASE;
In SQL*Plus, you can run the following SQL statement to monitor
the work being processed by RMAN.
@monitorjob
|
| 13. |
Not only can you perform an incremental backup at the database
level, but you can perform incremental backups on datafiles and
tablespaces as well. Let’s perform an incremental backup on
a datafile.
Use the same datafile as used previously, the datafile for the
Example
tablespace.
BACKUP INCREMENTAL LEVEL 1 DATAFILE 5;
|
| 14. |
RMAN can backup the archive logs and database at the same time
or separately. Let’s backup just the archive logs. First, generate
some logs. From the command prompt type the following:
switch5 orcl.world
Now, backup the archive logs. From RMAN type:
BACKUP ARCHIVELOG ALL;
|
| 15. |
At one time or another, a backup may terminate due to an error
or be killed due to system resources. RMAN offers the capability
to restart a backup. With the RMAN command NOT BACKED UP SINCE,
files that have not been backed up since a specified time will then
be backed up. In the following command, we tell RMAN to backup the
database files that have not been backed up since yesterday.
From RMAN, type the following command:
BACKUP DATABASE NOT BACKED UP SINCE TIME 'SYSDATE-1';
|
Administrative Commands
The following commands can be used in RMAN to find out if files require
a backup, delete backups that are no longer needed, and view backups of
files. We refer to this set of commands as RMAN maintenance. From RMAN,
type the following commands.
What files need to be backed up based on the configured retention policy?
REPORT NEED BACKUP;
The following 2 commands allow you to view what backups are obsolete
and delete them.
REPORT OBSOLETE;
DELETE OBSOLETE;
The validate command can be used to check for corruption or verify that
database files are in the correct location.
BACKUP VALIDATE DATABASE ARCHIVELOG ALL;
If you run any batch jobs with the nologging option, the following command
will report any files that have not been backed up since the last nolog
operation.
REPORT UNRECOVERABLE;
To fee up disk space of archive logs that you know have already been
backed up, run this command to delete the archive logs that are older
then yesterday.
DELETE ARCHIVELOG UNTIL TIME 'SYSDATE-1';
The crosscheck command is used to verify that backups of the database
are in the location known by the repository and to verify that archive
logs are still on disk and have not been deleted outside of RMAN.
CROSSCHECK BACKUP OF DATABASE;
The LIST commands allow you to view the backup information of the database,
datafile, tablespaces, and controlfiles. Lets view the backups for datafile
4 and the control file.
LIST BACKUP OF DATAFILE 4;
LIST BACKUP OF CONTROLFILE;
| 16. |
Enter the following commands in RMAN:
REPORT NEED BACKUP;
REPORT OBSOLETE;
DELETE OBSOLETE;
BACKUP VALIDATE DATABASE ARCHIVELOG ALL;
REPORT UNRECOVERABLE;
DELETE ARCHIVELOG UNTIL TIME 'SYSDATE-1';
CROSSCHECK BACKUP OF DATABASE;
LIST BACKUP OF DATAFILE 5;
LIST BACKUP OF CONTROLFILE;
|
| 17. |
Clean up.
From RMAN, type the following command to exit RMAN.
Exit
In SQL*Plus, type the following command to exit from SQL*Plus
Exit
|
2 Database Recovery
The RESTORE and RECOVER commands manage restore and recovery of the
database from RMAN.
Complete Recovery
When you perform complete recovery, you recover the backups to the current
SCN. You can either recover the whole database at once or recover individual
tablespaces or datafiles. Because you do not have to open the database
with the RESETLOGS option after complete recovery as you do after incomplete
recovery, you have the option of recovering some datafiles at one time
and the remaining datafiles later.
Note: Lesson 1 Configuration and Backup, must be completed prior
to starting this lesson.
Recovering Datafiles
Take the datafile that needs recovery offline, restore the datafile,
recover the datafile, and bring the datafile online.
| 1. |
In SQL*Plus connect as HR and select from the EMPLOYEES table to
verify that a table exist.
connect hr/hr@orcl.world
set pagesize 50000
SELECT first_name
FROM employees;
|
| 2. |
Simulate the loss of employee01.dbf. Run the following script from
the command line window.
rmdf orcl.world orcl
|
| 3. |
In SQL*Plus as HR, verify that the file has been deleted.
connect hr/hr@orcl.world;
SELECT *
FROM employees;
|
| 4. |
Recover example01.dbf. In RMAN, type the following commands.
RMAN TARGET SYS/ORACLE@orcl.world
RESTORE DATAFILE 5;
RECOVER DATAFILE 5;
SQL 'alter tablespace example online';
|
| 5. |
In SQL*Plus as HR, verify the data is there.
SELECT first_name
FROM employees;
|
Recovering the Database
The database must be in MOUNT mode to restore and recover with RMAN.
| 6. |
As HR, select from the EMPLOYEES table to verify that the database
is up and running. In SQL*Plus, type the following:
SELECT first_name
FROM employees;
|
| 7. |
Simulate the loss of all datafiles. (This does not include the
control files or online logs) From the command prompt, type the
following
rmdb orcl.world orcl
|
| 8. |
Recover DATABASE. In RMAN, type the following commands.
RMAN TARGET SYS/ORACLE@orcl.world
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN;
|
| 9. |
In SQL*Plus as HR, type the following command to show that the
datafile has been successfully restored and recovered.
CONNECT hr/hr@orcl.world
SELECT first_name
FROM employees;
|
Recovering Tablespaces
Take the tablespace that needs recovery offline, restore the tablespace,
recover the tablespace, and bring the recovered tablespace online.
| 10. |
Select from the EMPLOYEES table to verify that a table exist. In
SQL*Plus as HR, type the following command .
CONNECT hr/hr@orcl.world
SELECT first_name
FROM employees;
|
| 11. |
Simulate the loss of the EXAMPLE tablespace. Run the following
command from the command prompt.
rmts orcl.world orcl
|
| 12. |
Verify that the file has been deleted.
Connect hr/hr@orcl.world
SELECT first_name
FROM EMPLOYEES;
|
| 13. |
Recover tablespace EXAMPLE. In RMAN, run the following commands
RMAN TARGET SYS/ORACLE@ORCL.WORLD
RESTORE TABLESPACE EXAMPLE;
RECOVER TABLESPACE EXAMPLE;
SQL 'ALTER TABLESPACE EXAMPLE ONLINE';
|
| 14. |
In SQL*Plus, type the following commands.
SELECT first_name
FROM employees;
|
Recovering the Database
The database must be in MOUNT mode to restore and recover with RMAN.
| 15. |
In SQL*Plus as HR, select from the EMPLOYEES table to verify that
the database is up and running.
SELECT first_name
FROM employees;
|
| 16. |
Simulate the loss of all datafiles (this does not include the control
files or online logs). From the command prompt, type the following
command.
rmdb orcl.world orcl
|
| 17. |
Verify that the database files have been deleted.
Connect hr/hr@orcl.world
SELECT *
FROM employees;
|
| 18. |
Recover DATABASE. In RMAN, type the following commands. Connect
to RMAN.
RMAN TARGET SYS/ORACLE@ORCL.WORLD
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN;
|
| 19. |
From SQL*Plus are HR, type the following commands to verify that
the database has been recovered.
Connect hr/hr@orcl.world
SELECT first_name
FROM employees;
|
Incomplete Recovery
Incomplete recovery uses a backup to produce a noncurrent version of
the database. In other words, you
do not apply all of the redo records generated after the most recent backup.
You usually perform
incomplete recovery of the whole database in the following situations:
- Media failure destroys some or all of the online redo logs.
- A user error causes data loss, for example, a user inadvertently drops
a table.
- You cannot perform complete recovery because an archived redo log
is missing.
- You lose your current control file and must use a backup control file
to open the database.
To perform incomplete media recovery, you must restore all datafiles
from backups created prior to the time to which you want to recover and
then open the database with the RESETLOGS option when recovery completes.
The RESETLOGS operation creates a new incarnation of the database—in other
words, a database with a new stream of log sequence numbers starting with
log sequence 1.
Incomplete Recovery of the Database
| 20. |
In SQL*Plus as HR, select from the EMPLOYEES table to verify that
the database is up and running.
Connect hr/hr@orcl
SELECT *
FROM employees;
|
| 21. |
Connect as SYS and select the database id from V$DATABASE. Write
down the value returned.
Connect SYS/ORACLE@orcl.world as sysdba
SELECT dbid
FROM v$database;
|
| 22. |
Simulate the loss of all datafiles, control files, online logs
and archive logs. Run the following script from the command line.
rmall orcl.world orcl
|
| 23. |
Verify that the database files have been deleted.
Connect hr/hr@orcl.world
SELECT first_name
FROM employees;
|
| 24. |
Recover DATABASE. In RMAN, type the following commands. Connect
to RMAN without any connect strings
RMAN
|
| 25. |
The following commands are issued within RMAN, using the database
id(DBID) returned from the query. You will receive an error message
at the end of recover due to the loss of the online redo logs.
SET DBID 1003121815;
CONNECT TARGET SYS/ORACLE@ORCL.WORLD;
STARTUP NOMOUNT;
SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO
'D:\ORACLE\ORCLBACKUP\ora_cf%F';
RESTORE CONTROLFILE FROM AUTOBACKUP;
STARTUP MOUNT;
RESTORE DATABASE;
RECOVER DATABASE;
|
| 26. |
Since the online logs were lost, complete recovery is not possible.
Open the database with resetlogs to continue.
ALTER DATABASE OPEN RESETLOGS;
 |
| 27. |
To view the successful recovery of the database, in SQL*Plus type
the following commands.
Connect hr/hr@orcl.world
SELECT first_name
FROM employees;
|
| 28. |
Make a backup of new database incarnation version!! Let's generate
some archive logs. From the command prompt, type the following command.
swtich5 orcl.world
|
| 29. |
In RMAN, type the following commands to make a backup and remove
obsolete backups and archive logs. What files require a backup now?
RMAN TARGET SYS/ORACLE@ORCL.WORLD
REPORT NEED BACKUP;
|
| 30. |
Make a backup of the database and archive logs.
BACKUP DATABASE PLUS ARCHIVELOG;
|
| 31. |
Remove obsolete backups and archive logs.
DELETE OBSOLETE;
|
| 32. |
Crosscheck Backups.
CROSSCHECK BACKUP OF DATABASE;
|
| 33. |
Delete obsolete again.
DELETE OBSOLETE;
|
3 RMAN Advanced Features
Duplicate Database
You can use the RMAN DUPLICATE command create a duplicate database from
target database backups while still retaining the original target database.
A duplicate database is a copy of the target database that you can run
independently for a variety of purposes. For example, you can use it to:
- Test backup and recovery procedures
- Export data such as a table that was inadvertently dropped from the
production database, and then importing it back into the production
database.
A duplicate database is distinct from a standby database, although both
types of databases are created with the DUPLICATE command. A standby database
is a copy of the primary database that you can update continually or periodically
by using archived logs from the primary database. If the primary database
is damaged or destroyed, then you can perform failover to the standby
database and effectively transform it into the new primary database. A
duplicate database, on the other hand, cannot be used in this way: it
is not intended for failover scenarios and does not support the various
standby recovery and failover options.
Note: Lesson 1 Configuration and Backup, must be completed prior
to starting this lesson.
| 01. |
From the command prompt create a password file for the AUX database.
orapwd file=d:\oracle\ora92\database\PWDaux.ora password=oracle entries=5
|
| 2. |
In SQL*Plus as SYS create a parameter file from the spfile for
the AUX database.
connect sys/oracle@orcl.world as sysdba
create pfile='d:\oracle\ora92\database\INITaux.ORA' from spfile;
|
| 3. |
Edit the file INITaux.ORA,
found in D:\ORALCE\ORA92\DATABASE;
change all occurrences of
\orcl\ to \auxorcl\.
Next change db_name and instance_name to aux. Then add the following
two lines to the parameter file:
db_file_name_convert=('D:\ORACLE\ORADATA\ORCL','D:\ORACLE\ORADATA\AUXORCL')
log_file_name_convert=('D:\ORACLE\ORADATA\ORCL','D:\ORACLE\ORADATA\AUXORCL')
|
| 4. |
Run script cre_dir from the command prompt to create the subdirectories
for the duplicate database.
cre_dir
|
| 5. |
Add an entry in the tnsnames.ora and listener.ora for the AUX database.
Use Net Manager.
Start > Programs > Oracle - OraHome92 > Configuration
and Migration Tools > Net Manager
TNSNAMES.ORA
Select Oracle Net Configuration > Local > Service Name.
Then from the menu select Edit > Create. Use the following
values to create the entry.
Net Service Name: aux.world
Protocol: TCP/IP (Internet Protocol)
Host: <your computer's name>
Port: 1521
Service Name: aux.world
Since the database has not been created you can not successfully
test the configuration.
LISTENER.ORA
Select Oracle Net Configuration > Local > Listeners >
LISTENER. From the drop down menu on the right and side change
Listener Location to Database Services. Click Add Database at the
bottom. Use the following values:
Global Database Name: aux.net
Oracle Home: D:\oracle\ora92
SID: aux
Select from the top menu: File > Save Network Configuration
followed by File > Exit.
|
| 6. |
Shutdown and startup the listener.ora. From the command prompt
type the follow:
lsnrctl stop
lsnrctl start
|
| 7. |
Create a service for the database. From the command prompt type:
oradim -new -sid AUX -startmode m -pfile d:\oracle\ora92\database\INITaux.ORA
|
| 8. |
From RMAN, type the following commands.
RMAN
CONNECT AUXILIARY SYS/ORACLE@AUX.WORLD
CONNECT TARGET SYS/ORACLE@ORCL.WORLD
STARTUP CLONE NOMOUNT FORCE;
DUPLICATE TARGET DATABASE TO AUX;
|
| 9. |
Connect to the new database
connect hr/hr@aux.world
SELECT first_name
FROM employees;
|
Module Summary
In this module, you should have learned how to:
Copyright © 2002 Oracle Corporation. All Rights Reserved.
Close Window
|
Bookmark
