1.8 Performing Backup and Recovery


1. Overview


Purpose

In this tutorial you configure your database for backup and recovery operations. You use Recovery Manager (RMAN) to perform backups and recover the database. You also use Oracle Flashback Table to rewind changes to a table and Oracle Flashback Drop to recover a dropped table.

Time to Complete

Approximately 1 hour

Introduction

This tutorial shows you how to perform Oracle Database backup and recovery operations by using Oracle Recovery Manager (RMAN). After completing this tutorial, you should be familiar with the basic concepts of Oracle Database backup and recovery operations, know how to implement a disk-based backup strategy, and use Oracle Flashback features.

Software Requirements

The following is a list of hardware and software requirements:
  • Oracle Database 12c
Prerequisites

Before starting this tutorial, you should:
  • Have Installed the sample schemas
  • Have completed the other tutorials in this series

2. Connecting to the Target Database


To perform backup and recovery operations, or to configure backup and recovery settings, you must invoke RMAN and connect to the target database.

  • Unlocking the SYSBACKUP User Account
In this section you connect to the target database as the SYSBACKUP user. If your SYSBACKUP user account is locked, perform the following steps to unlock it:

1. Launch Enterprise Manager Database Express 12c. Login in as the SYSTEM user.

Performing Backup and Recovery

2. Select Users in the Security menu.

Performing Backup and Recovery

3. Select the SYSBACKUP user. In the Actions menu, select "Alter Account."

Performing Backup and Recovery

4. Enter a password for the SYSBACKUP user in the Password and Confirm Password fields. Deselect "Account Locked." Click OK.

Performing Backup and Recovery

5. Click OK on the Confirmation page.

Performing Backup and Recovery

6. The Users page shows that the SYSBACKUP user account is now unlocked.

Performing Backup and Recovery

  • Invoking RMAN
1. Open a terminal window. Execute the oraenv utility to set the environment variables for the orcl database.

Performing Backup and Recovery

2. Log in to RMAN as the SYSBACKUP user by entering the rman target sysbackup command. Enter the password for the SYSBACKUP user when prompted.

Performing Backup and Recovery

3. Configuring Recovery Settings


In this section, you configure the Fast Recovery Area, set the database in ARCHIVELOG mode, and enable Flashback Database.
  • Configuring the Fast Recovery Area
1. Open a terminal window. Execute the oraenv utility to set the environment variables for the orcl database.

Performing Backup and Recovery

2. Invoke SQL*Plus and log in as the SYSTEM user.

Performing Backup and Recovery

3. Use the show parameter recovery SQL*Plus command to view the settings for all initialization parameters containing "recovery" in the name. Note that the size of your Fast Recovery Area may differ from what is shown in this example.

Performing Backup and Recovery

The location of the Fast Recovery Area is set by the DB_RECOVERY_FILE_DEST initialization parameter. Oracle recommends that the Fast Recovery Area be located on a separate storage device from the database files.

4. Set the Fast Recovery Area size to 6 GB by executing the alter system set db_recovery_file_dest_size=6G command. You can also execute this command in RMAN.

Performing Backup and Recovery

  • Enabling Archiving of Redo Log Files
1. Open a terminal window. Invoke SQL*Plus and connect as the SYSDBA user.

Performing Backup and Recovery

2. Execute the ARCHIVE LOG LIST command to determine whether your database is in ARCHIVELOG mode. Note that the log sequence numbers in your database will likely vary from what is shown in this tutorial.

Performing Backup and Recovery

3. Open another terminal window and set the environment variables for your orcl database. Invoke RMAN and connect as the SYSBACKUP user.

Performing Backup and Recovery

4. Execute the shutdown immediate command to shut down the database instance.

Performing Backup and Recovery

5. Execute the startup mount command to start the instance and mount the database.

Performing Backup and Recovery

6. Execute the BACKUP DATABASE command to create a backup before you change the ARCHIVELOG mode.

Performing Backup and Recovery

7. Execute the alter database archivelog command to put the database in ARCHIVELOG mode.

Performing Backup and Recovery

8. Execute the alter database open command.

Performing Backup and Recovery

9. Return to your SQL*Plus session (You might need to re-connect to sqlplus as sysdba). Execute the archive log list command again and note that the database is now in ARCHIVELOG mode. The archived redo log files will be written to the Fast Recovery Area as specified by the USE_DB_RECOVERY_FILE_DEST destination. 

Performing Backup and Recovery

10. Exit SQL*Plus.

Performing Backup and Recovery

11. Return to your RMAN session. Back up the database by executing the BACKUP DATABASE PLUS ARCHIVELOG command.

Performing Backup and Recovery


  • Enabling Flashback Database
Ensure that you have configured the Fast Recovery Area before enabling Flashback Database.

1. Invoke RMAN and connect to the target database as the SYSBACKUP user.

Performing Backup and Recovery

2. Optionally, specify the length of the desired flashback window (in minutes) by setting the DB_FLASHBACK_RETENTION_TARGET initialization parameter. The default value for this parameter is 1440 minutes, which is one day. This example sets the flashback window to 3 days: ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320;.

Performing Backup and Recovery

3. Enable Flashback Database by executing the ALTER DATABASE FLASHBACK ON command.

Performing Backup and Recovery

4. Configuring Backup Settings

You can configure several backup-related settings and policies in RMAN.

  • Viewing Backup Settings

1. Invoke RMAN and connect to the target database as the SYSBACKUP user.

Performing Backup and Recovery

2. Use the RMAN SHOW ALL command to view the RMAN configuration settings, including backup settings.

Performing Backup and Recovery

  • Configuring Backup Device Settings
1. If the default device for backups is not set to disk (the default), use the CONFIGURE DEFAULT DEVICE TYPE TO DISK command to set it.

Performing Backup and Recovery

2. Verify the setting by using the SHOW DEFAULT DEVICE TYPE command.

Performing Backup and Recovery

  • Configuring Backup Policy Settings
1. Configure backup optimization to save space in the Fast Recovery Area by executing CONFIGURE BACKUP OPTIMIZATION ON.

Performing Backup and Recovery

2. Verify the setting by executing SHOW BACKUP OPTIMIZATION.

Performing Backup and Recovery

3. Configure the retention policy to specify that the backups and archived logs must be retained for 31 days. Use the following command: CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 31 DAYS

Performing Backup and Recovery

4. Use the SHOW RETENTION POLICY command to verify the setting.

Performing Backup and Recovery

  • Configuring Control File and Server Parameter File Automatic Backups
1. Configure automatic backups of the control file and server parameter file by executing CONFIGURE CONTROLFILE AUTOBACKUP ON.

Performing Backup and Recovery

2. Use the SHOW CONTROLFILE AUTOBACKUP command to verify the setting.

Performing Backup and Recovery

  • Enabling Block Change Tracking
1. Invoke SQL*Plus and connect as the SYSTEM user.

Performing Backup and Recovery

2. Specify the location of the block change tracking file by setting DB_CREATE_FILE_DEST if it is not already set. DB_CREATE_FILE_DEST specifies the default location for Oracle-managed datafiles. In this tutorial DB_CREATE_FILE_DEST is set to /u01/app/oracle/oradata/orcl, the storage location for data files and control files.

Performing Backup and Recovery

3. Enable block change tracking by executing the ALTER DATABASE ENABLE BLOCK CHANGE TRACKING command.

Performing Backup and Recovery

4. Verify that block change tracking is enabled and view the name of the block change tracking file by querying V$BLOCK_CHANGE_TRACKING. Note that the file name is a system-generated name.

Performing Backup and Recovery

5. Performing and Scheduling Backups


You can use RMAN to perform different types of backups as dictated by your backup and recovery strategy.

  • Performing a Whole Database Backup
You can use RMAN to perform a whole backup of your database. A whole backup includes the complete contents of all data files of the database, plus the control file, archived redo log files, and server parameter file. You can perform a complete recovery with the files from a whole backup.

1. Invoke RMAN and connect to the target database as the SYSBACKUP user.

Performing Backup and Recovery

2. Execute the BACKUP DATABASE PLUS ARCHIVELOG command to back up the database files, including the archive redo log files.

Performing Backup and Recovery

3. After backing up the archived logs, RMAN backs up the datafiles.

Performing Backup and Recovery

4. RMAN also backs up the current log files, then the control file and server parameter file (SPFILE).

Performing Backup and Recovery

  • Displaying Backup Information Stored in the RMAN Repository
1. Enter the LIST BACKUP SUMMARY command to display backup information stored in the RMAN repository. Note that the number of backups taken in your database may vary from what is shown in this tutorial.

Performing Backup and Recovery

2. Execute the LIST BACKUP OF DATAFILE command to view detailed information stored in the RMAN repository about the backup of a specific datafile.

Performing Backup and Recovery

  • Validating Backups
1. Execute the VALIDATE DATAFILE command to validate the backup for a specific datafile. The VALIDATE command determines whether the backup exists.

Performing Backup and Recovery

2. Execute the RESTORE TABLESPACE VALIDATE command to validate that the datafiles for the specified tablespace can be restored.

Performing Backup and Recovery


6. Managing Backups


You should manage your backups by deleting obsolete backups and performing periodic checks to ensure that backups are available and usable.

  • Displaying Backup Information
1. Switch to the sqlplus terminal window. Query the V$RMAN_BACKUP_JOB_DETAILS view to display backup job history.

Performing Backup and Recovery

  • Crosschecking Backups
1. Execute the LIST BACKUP SUMMARY command to view a list of all backup sets. Note that the number of backups taken in your database may vary from what is shown in this tutorial.

Performing Backup and Recovery

2. Execute the CROSSCHECK BACKUP command to crosscheck all backup sets.

Performing Backup and Recovery

3. Enter the CROSSCHECK BACKUPSET command to crosscheck the specified backupset.

Performing Backup and Recovery

  • Deleting Expired Backups
1. Enter the DELETE EXPIRED BACKUP command to delete expired backups from the RMAN repository.

Performing Backup and Recovery

  • Monitoring Fast Recovery Area Space Usage
You should monitor the Fast Recovery Area to ensure that it is large enough to contain backups and other recovery-related files. Note that space usage in your database may vary from what is shown in this tutorial.

1. Query the V$RECOVERY_FILE_DEST view to obtain information about the Fast Recovery Area.

Performing Backup and Recovery

2. Query the V$RECOVERY_AREA_USAGE view to obtain additional information about the Fast Recovery Area.

Performing Backup and Recovery

7. Performing Oracle Advised Recovery



The Oracle advised recovery feature uses Data Recovery Advisor, which is an Oracle Database feature that automatically diagnoses data failures, determines and presents appropriate repair options, and performs repairs if requested by the user.

1. Query the V$DATAFILE view to determine the file name of the file that belongs to the APPTS tablespace. You created the APPTS tablespace in the Managing Database Storage Structure tutorial.

Performing Backup and Recovery

2. Enter the SQL*Plus host command to obtain an operating system prompt.

Performing Backup and Recovery

3. Use the Linux mv command to move the datafile belonging to the APPTS tablespace to $HOME/appts.bkup: mv /u01/app/oracle/product/12.1.0/dbhome_1/dbs/appts.dbf $HOME/appts.bkup

Performing Backup and Recovery

4. Return to SQL*Plus by entering the exit command.

Performing Backup and Recovery

5. Query the APPUSER.PURCHASE_ORDERS table by executing SELECT * FROM appuser.purchase_orders.

Performing Backup and Recovery

6. Exit from SQL*Plus.

Performing Backup and Recovery

7. Invoke RMAN and connect as the SYSBACKUP user.

Performing Backup and Recovery

8. List all the failures known to the Data Recovery Advisor by executing the LIST FAILURE command.

Performing Backup and Recovery

9. Determine repair options, both automatic and manual, by executing the ADVISE FAILURE command.

Performing Backup and Recovery

10. Correct the problem by executing the REPAIR FAILURE command.

Performing Backup and Recovery

11. Respond with "yes" to execute the repair script.

Performing Backup and Recovery

12. Exit from RMAN.

Performing Backup and Recovery

13. Log in to SQL*Plus as the SYSTEM user.

Performing Backup and Recovery

14. Once again, query the APPUSER.PURCHASE_ORDERS table by executing SELECT * FROM appuser.purchase_orders.

Performing Backup and Recovery

15. Exit from SQL*Plus.

Performing Backup and Recovery

8. Rewinding a Table by Using Oracle Flashback Table


You can use Oracle Flashback Table to rewind the contents of one or more tables back to a previous point in time without affecting other database objects. This feature enables you to recover from logical data corruption such as the accidental addition or deletion of rows.

  • Enabling Row Movement on a Table
In this section you enable row movement on the HR.REGIONS table. Row movement must be enabled on a table before you can use Flashback Table.

1. Invoke SQL*Plus and connect as the SYSTEM user.

Performing Backup and Recovery

2. Enable row movement on the HR.REGIONS table.

Performing Backup and Recovery

  • Simulating User Error
In this section you simulate user error by changing data in the HR.REGIONS table.

1. Query the HR.REGIONS tables by executing the SELECT * FROM hr.regions command.

Performing Backup and Recovery

2. Simulate user error by executing the following SQL commands to change the value in the REGION_NAME column in all rows: UPDATE hr.regions SET region_name = 'ORACLE'; COMMIT;

Performing Backup and Recovery

3. Once again, query the HR.REGIONS tables by executing the SELECT * FROM hr.regions command.

Performing Backup and Recovery

  • Performing Flashback Table
In this section you rewind the HR.REGIONS table to a point prior to the update you performed to simulate user error.

1. Use the FLASHBACK TABLE command to flash back table to a time before you performed the update to the HR.REGIONS table.

Performing Backup and Recovery

2. Query the HR.REGIONS table to verify that the values in the REGION_NAME column have been restored.

Performing Backup and Recovery

3. Exit from SQL*Plus.

Performing Backup and Recovery

9. Recovering a Dropped Table by Using Oracle Flashback Drop

  • Creating a New Table

In this section you create a new table.
1. Invoke SQL*Plus and connect as the SYSTEM user.

Performing Backup and Recovery

2. Create a new table named HR.REGIONS_HIST. Use the CREATE TABLE hr.regions_hist AS SELECT * FROM hr.regions statement to create a table that has the same structure and content as the HR.REGIONS table.

Performing Backup and Recovery

3. Query the new HR.REGIONS_HIST table.

Performing Backup and Recovery

  • Dropping a Table
In this section you drop your new HR.REGIONS_HIST table.

1. Execute the DROP TABLE hr.regions_hist command to drop the table.

Performing Backup and Recovery

2. Query the HR.REGIONS_HIST table.

Performing Backup and Recovery

  • Recovering the Dropped Table
In this section you recover the HR.REGIONS_HIST table from the recycle bin.

1. Execute the FLASHBACK TABLE hr.regions_hist TO BEFORE DROP command to recover the dropped table

Performing Backup and Recovery

2. Once again query the HR.REGIONS_HIST table to verify the data has been restored.

Performing Backup and Recovery

3. Exit from SQL*Plus.

Performing Backup and Recovery