Rman
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Rman as PDF for free.
More details
- Words: 4,275
- Pages: 45
rman
Page 1 of 45
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: 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
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 2 of 45
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,
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 3 of 45
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/[email protected] 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;
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 4 of 45
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.
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 5 of 45
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-timerecovery 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/[email protected]
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: z z z
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
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 6 of 45
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: z z z z z
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;
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 7 of 45
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;
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 8 of 45
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
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 9 of 45
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
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 10 of 45
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;
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 11 of 45
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;
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 12 of 45
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
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 13 of 45
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
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 14 of 45
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';
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 15 of 45
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.
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 16 of 45
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;
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 17 of 45
DELETE OBSOLETE;
BACKUP VALIDATE DATABASE ARCHIVELOG ALL;
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 18 of 45
REPORT UNRECOVERABLE;
DELETE ARCHIVELOG UNTIL TIME 'SYSDATE-1';
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 19 of 45
CROSSCHECK BACKUP OF DATABASE;
LIST BACKUP OF DATAFILE 5;
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 20 of 45
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
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 21 of 45
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/[email protected] 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
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 22 of 45
3. In SQL*Plus as HR, verify that the file has been deleted. connect hr/[email protected]; SELECT * FROM employees;
4. Recover example01.dbf. In RMAN, type the following commands. RMAN TARGET SYS/[email protected] RESTORE DATAFILE 5; RECOVER DATAFILE 5; SQL 'alter tablespace example online';
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 23 of 45
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:
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 24 of 45
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/[email protected] RESTORE DATABASE;
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 25 of 45
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/[email protected] SELECT first_name FROM employees;
Recovering Tablespaces
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 26 of 45
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/[email protected] 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
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 27 of 45
12. Verify that the file has been deleted. Connect hr/[email protected] SELECT first_name FROM EMPLOYEES;
13. Recover tablespace EXAMPLE. In RMAN, run the following commands RMAN TARGET SYS/[email protected] RESTORE TABLESPACE EXAMPLE; RECOVER TABLESPACE EXAMPLE; SQL 'ALTER TABLESPACE EXAMPLE ONLINE';
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 28 of 45
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;
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 29 of 45
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/[email protected] SELECT * FROM employees;
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 30 of 45
18. Recover DATABASE. In RMAN, type the following commands. Connect to RMAN. RMAN TARGET SYS/[email protected] 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/[email protected] SELECT first_name
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 31 of 45
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: z z z z
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;
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 32 of 45
21. Connect as SYS and select the database id from V$DATABASE. Write down the value returned. Connect SYS/[email protected] 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
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 33 of 45
23. Verify that the database files have been deleted. Connect hr/[email protected] SELECT first_name FROM employees;
24. Recover DATABASE. In RMAN, type the following commands. Connect to RMAN without any connect strings RMAN
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 34 of 45
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/[email protected]; 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
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 35 of 45
to continue. ALTER DATABASE OPEN RESETLOGS;
27. To view the successful recovery of the database, in SQL*Plus type the following commands. Connect hr/[email protected] 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
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 36 of 45
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/[email protected] REPORT NEED BACKUP;
30. Make a backup of the database and archive logs. BACKUP DATABASE PLUS ARCHIVELOG;
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 37 of 45
31. Remove obsolete backups and archive logs. DELETE OBSOLETE;
32. Crosscheck Backups. CROSSCHECK BACKUP OF DATABASE;
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 38 of 45
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:
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 39 of 45
z z
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/[email protected] as sysdba create pfile='d:\oracle\ora92\database\INITaux.ORA' from spfile;
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 40 of 45
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
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 41 of 45
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
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 42 of 45
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:
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 43 of 45
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
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 44 of 45
8. From RMAN, type the following commands. RMAN CONNECT AUXILIARY SYS/[email protected] CONNECT TARGET SYS/[email protected] STARTUP CLONE NOMOUNT FORCE; DUPLICATE TARGET DATABASE TO AUX;
9. Connect to the new database connect hr/[email protected] SELECT first_name FROM employees;
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 45 of 45
Module Summary In this module, you should have learned how to: Create a Recovery Manager Backup Configuration Use Recovery Manager to Recover a database, tablespace, and datafile Use Recovery Manager Advanced Features
Copyright © 2002 Oracle Corporation. All Rights Reserved. Close Window
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
Page 1 of 45
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: 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
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 2 of 45
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,
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 3 of 45
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/[email protected] 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;
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 4 of 45
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.
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 5 of 45
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-timerecovery 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/[email protected]
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: z z z
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
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 6 of 45
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: z z z z z
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;
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 7 of 45
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;
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 8 of 45
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
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 9 of 45
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
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 10 of 45
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;
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 11 of 45
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;
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 12 of 45
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
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 13 of 45
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
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 14 of 45
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';
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 15 of 45
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.
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 16 of 45
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;
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 17 of 45
DELETE OBSOLETE;
BACKUP VALIDATE DATABASE ARCHIVELOG ALL;
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 18 of 45
REPORT UNRECOVERABLE;
DELETE ARCHIVELOG UNTIL TIME 'SYSDATE-1';
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 19 of 45
CROSSCHECK BACKUP OF DATABASE;
LIST BACKUP OF DATAFILE 5;
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 20 of 45
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
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 21 of 45
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/[email protected] 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
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 22 of 45
3. In SQL*Plus as HR, verify that the file has been deleted. connect hr/[email protected]; SELECT * FROM employees;
4. Recover example01.dbf. In RMAN, type the following commands. RMAN TARGET SYS/[email protected] RESTORE DATAFILE 5; RECOVER DATAFILE 5; SQL 'alter tablespace example online';
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 23 of 45
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:
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 24 of 45
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/[email protected] RESTORE DATABASE;
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 25 of 45
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/[email protected] SELECT first_name FROM employees;
Recovering Tablespaces
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 26 of 45
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/[email protected] 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
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 27 of 45
12. Verify that the file has been deleted. Connect hr/[email protected] SELECT first_name FROM EMPLOYEES;
13. Recover tablespace EXAMPLE. In RMAN, run the following commands RMAN TARGET SYS/[email protected] RESTORE TABLESPACE EXAMPLE; RECOVER TABLESPACE EXAMPLE; SQL 'ALTER TABLESPACE EXAMPLE ONLINE';
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 28 of 45
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;
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 29 of 45
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/[email protected] SELECT * FROM employees;
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 30 of 45
18. Recover DATABASE. In RMAN, type the following commands. Connect to RMAN. RMAN TARGET SYS/[email protected] 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/[email protected] SELECT first_name
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 31 of 45
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: z z z z
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;
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 32 of 45
21. Connect as SYS and select the database id from V$DATABASE. Write down the value returned. Connect SYS/[email protected] 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
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 33 of 45
23. Verify that the database files have been deleted. Connect hr/[email protected] SELECT first_name FROM employees;
24. Recover DATABASE. In RMAN, type the following commands. Connect to RMAN without any connect strings RMAN
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 34 of 45
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/[email protected]; 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
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 35 of 45
to continue. ALTER DATABASE OPEN RESETLOGS;
27. To view the successful recovery of the database, in SQL*Plus type the following commands. Connect hr/[email protected] 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
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 36 of 45
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/[email protected] REPORT NEED BACKUP;
30. Make a backup of the database and archive logs. BACKUP DATABASE PLUS ARCHIVELOG;
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 37 of 45
31. Remove obsolete backups and archive logs. DELETE OBSOLETE;
32. Crosscheck Backups. CROSSCHECK BACKUP OF DATABASE;
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 38 of 45
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:
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 39 of 45
z z
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/[email protected] as sysdba create pfile='d:\oracle\ora92\database\INITaux.ORA' from spfile;
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 40 of 45
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
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 41 of 45
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
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 42 of 45
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:
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 43 of 45
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
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 44 of 45
8. From RMAN, type the following commands. RMAN CONNECT AUXILIARY SYS/[email protected] CONNECT TARGET SYS/[email protected] STARTUP CLONE NOMOUNT FORCE; DUPLICATE TARGET DATABASE TO AUX;
9. Connect to the new database connect hr/[email protected] SELECT first_name FROM employees;
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
rman
Page 45 of 45
Module Summary In this module, you should have learned how to: Create a Recovery Manager Backup Configuration Use Recovery Manager to Recover a database, tablespace, and datafile Use Recovery Manager Advanced Features
Copyright © 2002 Oracle Corporation. All Rights Reserved. Close Window
http://www.oracle.com/technology/obe/obe9ir2/obe-ha/rman/rman.htm?_template=/ocom... 22-06-2009
Related Documents
Rman
November 2019 17
Rman
May 2020 9
Rman
June 2020 9
Rman - Doc
November 2019 17
Rman Bkup.sh
April 2020 8