I have passed now (May 22, 2000) the third exam of the Oracle8 Certification: "Oracle8: Backup and Recovery Workshop". You need to get 42 out of 60 questions correct, I had 57 correct. I prepared by - reading the manual: Oracle8 Backup and Recovery (available online as part of the Oracle installation, e.g. use "acroread $ORACLE_HOME/doc/index.pdf"). - Taking the Oracle assessment test, which can be downloaded for free. - Buying 300 example questions from selftestsoftware.com for $99 + shipping. It is quite boring to go over them again and again, but it helps. The real questions are of course a bit different from the example questions, but in part they are only small variations, or at least the example questions will show you which areas you have to repeat. Some of the example questions were quite unfair, where some part of the official Oracle training material was taken completely out of context, and some explanations were not helpful at all, others contained typing errors. But although it is not as good as it could be, it was a very important part of my preparation. - Reading the chapters in the book Jason S. Couchman: Oracle 8i Certified Professional: DBA Certification Exam Guide With CDROM. Osborne / ORACLE Press, August 1999, ISBN 0072120878, 1200 pages, $99. [Best Book Buys Price Comparison] However, this book only helps together with other sources or when you already know the subject. I couldn't really understand the recovery manager from it before I had read the manual. It is basically not a textbook, but a kind of refresher. - There is a book about Backup&Recovery in the Oracle Press Series. I didn't use that one, but it might have been a good idea. - I did teach the foundation backup and recovery the first time when I taught IS 2710 (in the chapter about "Protecting your Data"), but it was not at all Oracle-specific, and hard to understand. Maybe when I improve the slides for IS 2711, I will put a chapter about Backup and Recovery there. - I did set up a test database and tried a few recovery situations. I also created a recovery catalog for the test database in my main database and tried a few recovery manager commands. Some Impressions: - When I prepared for the exam, I felt something like "Wow! This is what being a good DBA really means: That you know what to do in an emergency, that you develop a backup strategy which makes sure that no data is lost even in situations which are worse than a single disk failure (however, any backup strategy has its limits), and that you get the database running again quickly." Since ther nerves of all people will be stressed in such a situation, it really needs good training to know what you have to do. A former collegue of mine, who works for a bank, says, that the severety of a situation can be assessed by the level of the managers standing behind the DBAs trying to get the system running again. - So I think there is a good reason for this test, although part of the material was already tested in the previous "Database Administration" exam. - You have to learn the recovery manager tool. The program rman.exe or rman80.exe comes with your personal oracle CD. It has by itself no graphical interface, so you can only run it from the MS-DOS command prompt. However, the Backup Manager, which is part of the Oracle Enterprise Manager (on the Oracle8 Clients CD) is a graphical interface to the recovery manager. But you need to know the command-line interface, and only very little about the graphical interface. 19 out of the 60 questions were related to the recovery manager, so there is no way to skip this part of the preparation. I don't like the recovery manager, I think that the commands are unnecessarily difficult. But on the other hand, after you became accustomed with it, it is not that difficult. You need to knoe the various recovery manager commands for the test, as well as when to use a recovery catalog, and the command line parameters for logging into the target database and the catalog (or which parameter to use when you have no catalog). - You also should try out the server manager, especially the syntax for the RECOVER and STARTUP command. - There are questions of the type, in which state (CLOSED, NOMOUNT, MOUNT, OPEN) should the database be when you want to do this or that. MOUNT means that only the control file is open, and often this is the right answer (at least the recovery manager usually wants to have access to the control file). OPEN means that all datafiles are open. NOMOUNT means that only the processes are running. - There are questions on the export and import utilities (EXP, IMP) (as in the previous exam). You also should understand the difference between logical backups (EXP/IMP) and physical backups (datafile copies and archivelogs). - You should know the difference between types of failures: user error, media failure, instance failure, statement failure, user process failure. E.g. you should know from which types of failures Oracle recovers automaically. - You should understand the difference between ARCHIVELOG and NOARCHIVELOG mode. This is especially important since the default NOARCHIVELOG does not protect against disk failures. If I rememeber correctly, there were about 4-5 questions on this distinction, and it is actually pretty easy. - You should know the syntax of the ALTER DATABASE command (at least all clauses related to recovery). If I remember correctly, there were about 7 questions related to this command. - You should know the contents of the control file. E.g. after which changes should you backup the control file. There were several questions related to the control file, and it is very important for recovery. - You should know (again) which Oracle process performs which task. - You should know what happens at a checkpoint. - You should know the alert.log file and its contents. - You should know when you have to open the database with the RESETLOGS option and what does this mean. - You should know that you can take a datafile offline (when the database is mounted) (if it is not part of the system tablespace or contains a rollback segment) and open the database, so that at least the tables which are not stored in that file are available. It is obviously important what you can do to make the database available again as soon as possible. You can also take a tablespace offline, but only when the database is still open (so it did not crash from the error). - You should know about "hot" backups (taken when the database is open). You should know that this cannot be done in NOARCHIVELOG mode, and that you have to put the tablespace into backup mode if you are not using RMAN. You should know what to do when the database crashes while a tablespace is in backup mode. - You should know about checksums in database and log file blocks, how to activate them, and that RMAN checks more than an operating system copy command. - You should know that when a block in a datafile is somehow destroyed, you might still be able to use a query which can be answered entirely out of an index (index scan) to get at least the information stored in the index (e.g. the key values of the rows stored in the faulty block). - Readonly tablespaces need also special considerations. - Oracle allows incomplete recovery, e.g. when you want to restore the database state before a table was dropped (which is immediately commited, so it cannot be simply rolled back). You should know which commands to use for that, and that you need a complete backup from a time before the faulty transaction was run. In the example questions, the differences between cancel-based, time-based and change-based incomplete recovery were emphasized (which is only minor in my view, it is only a different way to determine the last log entry which should be applied). Oracle8 also allows tablespace-point-in-time recovery, which is when it gets really complicated (fortunately, in my exam there was no question about that, but that was probably only by chance.) - You should know Dynamic Performance Views related to Backup&Recovery, e.g.: - V$DATAFILE - V$CONTROLFILE - V$DATAFILE_HEADER - V$LOG - V$LOGFILE - V$LOG_HISTORY - V$ARCHIVED_LOG - V$ARCHIVE - V$ARCHIVE_DEST - V$RECOVER_FILE - V$RECOVERY_STATUS - V$RECOVERY_LOG - V$RECOVERY_PROGRESS - V$SESSION_LONGOPS - V$SESSION_WAIT - V$BACKUP - V$BACKUP_CORRUPTION - V$BACKUP_DATAFILE - V$BACKUP_REDOLOG - V$BACKUP_DEVICE - V$BACKUP_SET - V$BACKUP_PIECE - V$COPY_CORRUPTION Three questions were of the type "Which dynamic performance view would you use to look up that kind of information?" It is not very nice to learn the names and contents by heart, but when you play around with them, you will get some knowledge, which gives you at least a good guess. Not all views mentioned in the exam actually exist. For instance, I checked V$BACKUP_PROGRESS, but there is no such view (I mixed it up with V$RECOVERY_PROGRESS). - You should know Initialization Parameters related to Backup&Recovery, e.g.: - LOG_BLOCK_CHECKSUM - DB_BLOCK_CHECKSUM - LOG_ARCHIVE_DEST - LOG_ARCHIVE_FORMAT - LOG_ARCHIVE_START - LOG_CHECKPOINT_TIMEOUT - LOG_ARCHIVE_DUPLEX_DEST - LOG_ARCHIVE_MIN_SUCCEED_DEST - LARGE_POOL_SIZE - BACKUP_DISK_IO_SLAVES - BACKUP_TAPE_IO_SLAVES - BACKGROUND_DUMP_DEST - CONTROL_FILE_RECORD_KEEP_TIME It might be necessary to know the exact spelling, e.g. that ARCHIVE_LOG_DUPLEX_DEST is wrong. I think that this is a bit unfair, I would anyway have looked it up when I need it, and you don't need it every day. However, the test is completely "closed book", and this was actually where I lost one of the points (if I remember correctly, they don't tell you afterwards what was wrong). Three questions were related to initialization parameters. - There was one question, where I felt that I exactly knew the answer, but none of the proposed answers really matched what I would have said. I think that I picked the wrong one. For another question, the obvious and most important answer was also not provided, but you always have to pick the best answer among the ones you can choose from. So these questions were not good, but since they let you make 18 mistakes before you fail the exam, it is still fair. - I also had two questions which tested exactly the same piece of knowledge. Fortunately I knew that. Probably the questions are randomly selected from all questions about a subtopic, so that such things can happen. Here is the official list of topics: 1. Backup and Recovery Considerations (2 questions) 2. Oracle Recovery Structures and Processes (3 questions) 3. Oracle Backup and Recovery Configuration (5 questions) 4. Oracle Recovery Manager Overview (7 questions) 5. Oracle Recovery Catalog Maintenance (6 questions) 6. Physical Backups without Recovery Manager (6 questions) 7. Physical Backups using Recovery Manager (5 questions) 8. Types of Failures and Troubleshooting (5 questions) 9. Oracle Recovery without Archiving (3 questions) 10. Complete Oracle Recovery with Archiving (6 questions) 11. Incomplete Oracle Recovery with Archiving (4 questions) 12. Oracle Export and Import Utilities (3 questions) 13. Additional recovery Issues (5 questions)