ORACLE BACKUP AND RECOVERY
1 INTRODUCTION TO USER MANAGED BACKUP AND RECOVERY 1 2 MAKING USER MANAGED BACKUPS 9 3 PERFORMING USER MANAGED RESTORE OPERATIONS 39 4 PERFORMING USER MANAGED MEDIA RECOVERY 55 5 TROUBLESHOOTING USER MANAGED MEDIA RECOVERY 91 6 USER MANAGED MEDIA RECOVERY SCENARIOS 101 7 PERFORMING USER MANAGED TSPITR 117 CONTENTS 1 INTRODUCTION TO USER MANAGED BACKUP AND RECOVERY THIS CHAPTER INTRODUCES DATABASE CONCEPTS THAT ARE FUNDAMENTAL TO USER MANAGED BACKUP AND RECOVERY THIS CHAPTER INCLUDES THE FOLLOWING TOPICS I ABOUT USER MANAGED BACKUP AND RECOVERY I WHY USE USER MANAGED BACKUP AND RECOVERY METHODS I OVERVIEW OF USER MANAGED BACKUP AND RECOVERY SEE ALSO ORACLE9I BACKUP AND RECOVERY CONCEPTS FOR A CONCEPTUAL OVERVIEW OF ESSENTIAL BACKUP AND RECOVERY CONCEPTS 1 ABOUT USER MANAGED BACKUP AND RECOVERY USER MANAGED BACKUP AND RECOVERY IS ANY STRATEGY IN WHICH RECOVERY MANAGER RMAN IS NOT USED AS THE PRINCIPAL BACKUP AND RECOVERY TOOL THE BASIC USER MANAGED BACKUP STRATEGY IS TO MAKE PERIODIC BACKUPS OF DATA LES AND ARCHIVED LOGS WITH OPERATING SYSTEM COMMANDS THE BASIC USER MANAGED MEDIA RECOVERY PROCEDURE IS AS FOLLOWS 1 IN THE EVENT OF A MEDIA FAILURE RESTORE DATABASE LE BACKUPS WITH OPERATING SYSTEM COMMANDS 2 RECOVER RESTORED DATA LES WITH THE SQL PLUS RECOVER STATEMENT 3 IF THE DATABASE IS CLOSED THEN OPEN IT FOR NORMAL USE IF IT IS OPEN THEN BRING THE RECOVERED TABLESPACES BACK ONLINE WHY USE USER MANAGED BACKUP AND RECOVERY METHODS ORACLE CORPORATION RECOMMENDS USING RMAN AS THE FOUNDATION OF AN ENTERPRISE BACKUP AND RECOVERY STRATEGY BUT USER MANAGED METHODS THAT IS METHODS THAT DO NOT INVOLVE RMAN ARE ALSO JUST AS EFFECTIVE NOTE THAT SOME FEATURES SUCH AS BLOCK MEDIA RECOVERY CAN ONLY BE PERFORMED WITH RMAN THE FOLLOWING ARE POSSIBLE CIRCUMSTANCES IN WHICH YOU MAY CHOOSE TO EMPLOY USER MANAGED METHODS RATHER THAN USE RMAN I YOU ARE MIGRATING FROM AN OLDER VERSION OF THE DATABASE TO THE CURRENT VERSION AND DO NOT IMMEDIATELY WANT TO UPDATE YOUR LEGACY BACKUP SCRIPTS I YOU MAINTAIN A NETWORK CONTAINING ORACLE7 AND LATER DATABASES AND WANT A SINGLE BACKUP AND RECOVERY METHOD TO HANDLE ALL DATABASES IN THE SAME WAY RMAN ONLY SUPPORTS ORACLE DATABASES OF RELEASE 8 0 OR GREATER I ALL YOUR RMAN BACKUP SETS ARE DESTROYED AND YOU ARE FORCED TO RESTORE USER MANAGED BACKUPS AND PERFORM RECOVERY WITH THE SQL PLUS RECOVER COMMAND ORACLE CORPORATION SUPPORTS USER MANAGED BACKUP AND RECOVERY AS A VIABLE ALTERNATIVE TO USING RMAN OVERVIEW OF USER MANAGED BACKUP AND RECOVERY THIS SECTION CONTAINS THESE TOPICS I ABOUT USER MANAGED BACKUPS ORACLE9I USER MANAGED BACKUP AND RECOVERY GUIDE 2 I ABOUT USER MANAGED RESTORE AND RECOVERY ABOUT USER MANAGED BACKUPS USER MANAGED BACKUPS CAN BE EITHER LOGICAL OR PHYSICAL YOU CAN USE THE EXPORT UTILITY TO MAKE BACKUPS OF LOGICAL OBJECTS SUCH AS TABLES VIEWS AND STORED PROCEDURES AND USE THE IMPORT UTILITY TO RESTORE THESE OBJECTS IF YOU DO NOT USE RMAN THEN YOU CAN USE OPERATING SYSTEM UTILITIES TO MAKE PHYSICAL BACKUPS A PHYSICAL BACKUP IS A BACKUP OF AN ORACLE DATABASE LE OR ARCHIVED REDO LOG LOCATED ON THE OPERATING SYSTEM NOTE THAT THESE LES CAN EITHER BE MANUALLY MANAGED DATABASE LES OR ORACLE MANAGED LES IF YOU USE THE ORACLE MANAGED FILES FEATURE THEN ORACLE NAMES THE LES FOR YOU AND ALSO DELETES THEM FOR YOU WHEN YOU DROP A TABLESPACE FROM THE POINT OF VIEW OF BACKUP AND RECOVERY ORACLE MANAGED LES ARE NO DIFFERENT FROM USER MANAGED LES THE FOLLOWING TABLE ILLUSTRATES THE MAIN TYPES OF PHYSICAL BACKUPS AND THE NON RMAN METHODS FOR PERFORMING THESE BACKUPS BACKUP OBJECT BACKUP METHOD EXAMPLE DATA LES OPERATING SYSTEM UTILITY CP DF3 F DF3 BAK ARCHIVED LOGS OPERATING SYSTEM UTILITY CP LOG 1 23 ARC LOG 1 23 BAK CONTROL LES SQL STATEMENT SQL ALTER DATABASE BACKUP CONTROLFILE TO CF1 BAK INITIALIZATION PARAMETER LE SQL STATEMENT SQL CREATE PFILE INIT ORA BAK FROM SPFILE NETWORK AND PASSWORD LES OPERATING SYSTEM UTILITY CP TNSNAMES ORA TNSNAMES BAK C COPY TNSNAMES ORA TNSNAMES BAK LOGICAL OBJECTS TABLES INDEXES PL SQL UNITS EXPORT UTILITY EXPORT SYSTEM MANAGER TABLE HR EMP FILE EMP DMP SEE ALSO I ORACLE9I DATABASE UTILITIES TO LEARN HOW TO USE THE EXPORT AND IMPORT UTILITIES I ORACLE9I DATABASE ADMINISTRATOR S GUIDE TO LEARN ABOUT ORACLE MANAGED FILES ORACLE9I USER MANAGED BACKUP AND RECOVERY GUIDE 3 BASIC BACKUP METHODOLOGY THE BASIC METHOD FOR TAKING USER MANAGED BACKUPS OF THE WHOLE DATABASE IS AS FOLLOWS 1 IDENTIFY THE DATA LES CONTROL LES AND ARCHIVED REDO LOGS TO BE BACKED UP BY QUERYING DYNAMIC PERFORMANCE VIEWS OR DATA DICTIONARY TABLES REFER TO QUERYING V VIEWS TO OBTAIN BACKUP INFORMATION ON PAGE 2 2 FOR PROCEDURES 2 USE AN OPERATING SYSTEM COMMAND SUCH AS THE UNIX CP COMMAND TO BACK UP DATA LES AND ARCHIVED REDO LOGS REFER TO MAKING USER MANAGED BACKUPS OF THE WHOLE DATABASE ON PAGE 2 4 FOR PROCEDURES 3 USE A SQL STATEMENT TO BACK UP THE CONTROL LE REFER TO MAKING USER MANAGED BACKUPS OF THE CONTROL FILE ON PAGE 2 19 FOR PROCEDURES 4 USE AN OPERATING SYSTEM COMMAND SUCH AS THE UNIX CP COMMAND TO BACK UP CON GURATION LES REFER TO MAKING USER MANAGED BACKUPS OF MISCELLANEOUS ORACLE FILES ON PAGE 2 29 FOR PROCEDURES CONSISTENT AND INCONSISTENT USER MANAGED BACKUPS YOU CAN USE RMAN OR OPERATING SYSTEM COMMANDS TO MAKE AN INCONSISTENT BACKUP OR A CONSISTENT BACKUP AN INCONSISTENT BACKUP IS A BACKUP OF ONE OR MORE DATABASE LES MADE WHILE THE DATABASE IS OPEN OR AFTER THE DATABASE HAS NOT BEEN SHUT DOWN NORMALLY A CONSISTENT BACKUP IS A BACKUP OF ONE OR MORE DATABASE LES THAT YOU MAKE AFTER THE DATABASE HAS BEEN SHUT DOWN NORMALLY UNLIKE AN INCONSISTENT BACKUP A CONSISTENT BACKUP DOES NOT REQUIRE RECOVERY AFTER IT IS RESTORED A CONSISTENT WHOLE DATABASE BACKUP IS THE ONLY VALID BACKUP OPTION FOR DATABASES RUNNING IN NOARCHIVELOG MODE BECAUSE OTHERWISE REDO NEEDED FOR RECOVERY IS NOT AVAILABLE IN NOARCHIVELOG MODE ORACLE OVERWRITES REDO RECORDS WITHOUT ARCHIVING THEM RST IF YOU RUN THE DATABASE IN ARCHIVELOG MODE THEN YOU CAN BACK UP DATABASE LES WHILE THE DATABASE IS OPEN THESE BACKUPS ARE INCONSISTENT BUT AS LONG AS YOU HAVE THE NECESSARY ARCHIVED REDO LOGS YOU CAN RECOVER THESE BACKUPS YOU CAN EITHER TAKE A TABLESPACE OF INE AND BACK UP ITS DATA LES OR PERFORM AN ONLINE BACKUP AN CAUTION DO NOT BACK UP ONLINE REDO LOGS IF YOU RESET THE ONLINE LOGS AFTER MEDIA RECOVERY AND THEN ACCIDENTALLY APPLY THE BACKED UP LOGS TO THE DATABASE THEN YOU CAN CORRUPT THE DATABASE ORACLE9I USER MANAGED BACKUP AND RECOVERY GUIDE 4