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:
Create a Recovery Manager Backup Configuration
Use Recovery Manager to Recover a database, tablespace, and datafile
Use Recovery Manager Advanced Features
Prerequisites
Before starting this module, you should have complete the following:
Preinstallation Tasks
Install the Oracle9i Database
Postinstallation Tasks
Review the Sample Schema
Enabling Archiving
Downloaded the rman.zip module files and unzipped them into your working directory.
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.
1. Backup Configurations to customize and save commonly used configurations for repeated use
2. Backup and Recovery wizards to walk the user through the steps of creating a backup script and submitting it as a scheduled job
3. Backup Job Library to save commonly used backup jobs that can be retrieved and applied to multiple targets
4. 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:
1. Configuration and Backup
2. Database Recovery
3. 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.
1 Configuration and Backup
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:
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;
No comments:
Post a Comment