Question 5) Oracle Database 10g Administration

Posted on
Like what you see? Share it.Share on Google+Share on LinkedInShare on FacebookShare on RedditTweet about this on TwitterEmail this to someone

Objective : Flashback Database
SubObjective : Configure and Use Flashback Database

 

Single Answer Multiple Choice

 

You have joined a new organization as a Database Administrator. The Security Administrator in your organization has kept human errors, such as erroneously dropping users, tables, and important data, to a minimum by carefully granting only the necessary privileges to each user. As a result of several ongoing transactions within your database, you discover that the flashback logs are rapidly increasing in the flash recovery area. To avoid out-of-space errors, you decide to delete the flashback logs from the flash recovery area.

 

How can you delete the flashback logs from the database if your database is up and running?

 

A. Shut down the database using the SHUTDOWN command. Mount the database using the STARTUP MOUNT EXCLUSIVE command and disable Flashback Database using the ALTER DATABASE FLASHBACK OFF statement.
B. Change the parameter setting in the SPFILE for the DB_RECOVERY_FILE_DEST parameter using the ALTER SYSTEM SET DB_RECOVERY_FILE_DEST=” SCOPE=BOTH statement while the database is up and running.
C. Shut down the database using the SHUTDOWN command. Mount the database using the STARTUP MOUNT EXCLUSIVE command. Change the parameter setting in the SPFILE for the DB_RECOVERY_FILE_DEST parameter using the ALTER SYSTEM SET DB_RECOVERY_FILE_DEST=” SCOPE=BOTH statement. Disable Flashback Database using the ALTER DATABASE FLASHBACK OFF statement.
D. Change the parameter setting in the SPFILE for the DB_RECOVERY_FILE_DEST parameter using the ALTER SYSTEM SET DB_RECOVERY_FILE_DEST=” SCOPE=BOTH statement. Shut down the database using the SHUTDOWN command. Mount the database using the STARTUP MOUNT EXCLUSIVE command and disable Flashback Database using the ALTER DATABASE FLASHBACK OFF statement.

 

Answer:
A. Shut down the database using the SHUTDOWN command. Mount the database using the STARTUP MOUNT EXCLUSIVE command and disable Flashback Database using the ALTER DATABASE FLASHBACK OFF statement.

 

Tutorial:
You can use the following set of commands to delete the flashback logs from the flash recovery area if your database is up and running:

 

SHUTDOWN
STARTUP MOUNT EXCLUSIVE
ALTER DATABASE FLASHBACK OFF;

 

When you disable Flashback Database using the ALTER DATABASE FLASHBACK OFF statement, the flashback logs will be automatically deleted from the flash recovery area.

 

The option stating that only the parameter setting for the DB_RECOVERY_FILE_DEST initialization parameter needs to be changed to disable Flashback Database is an incorrect option because changing this parameter setting will not disable Flashback Database. This operation will continue to result in the ORA-02097 and ORA-38775 errors. To disable the flashback database feature you must issue the SHUTDOWN, STARTUP, and ALTER DATABASE statements mentioned above.

 

Executing the ALTER SYSTEM SET DB_RECOVERY_FILE_DEST=” SCOPE=BOTH statement while the database is up and running, and then executing the ALTER DATABASE FLASHBACK OFF statement when the database is in the MOUNT mode, will result in the ORA-02097 and ORA-38775 errors. The DB_RECOVERY_FILE_DEST initialization parameter specifies the location of the flash recovery area within the database. The setup of the flash recovery area is a prerequisite for using the Flashback Database feature. You must have a setting for this parameter in your initialization parameter file before you enable Flashback Database. You must also disable Flashback Database before disabling the flash recovery area. Attempting to disable the flash recovery area before disabling Flashback Database using the ALTER SYSTEM SET DB_RECOVERY_FILE_DEST=” SCOPE=BOTH statement, will result in the following errors:

 

ORA-02097: parameter cannot modified because specified value is
invalid
ORA-38775: cannot disable flash recovery area flashback
database is enabled

 

You will still receive the ORA-02097 and ORA-38775 errors if you try to change the parameter setting using the ALTER SYSTEM SET DB_RECOVERY_FILE_DEST=” SCOPE=BOTH statement in the MOUNT mode, before executing the ALTER DATABASE FLASHBACK OFF statement. The DB_RECOVERY_FILE_DEST initialization parameter specifies the location of the flash recovery area within the database. The setup of the flash recovery area is a prerequisite for using the Flashback Database feature. You must have a setting for this parameter in your initialization parameter file before you enable Flashback Database. You must disable Flashback Database before disabling the flash recovery area. Attempting to disable the flash recovery area before disabling Flashback Database using the ALTER SYSTEM SET DB_RECOVERY_FILE_DEST=” SCOPE=BOTH statement, will result in an error.

 

Reference:
Oracle Database 10g: Administration Workshop II
Chapter 9: Flashback Database

 

Oracle Database Backup and Recovery Advanced User’s Guide
10g Release 1 (10.1)
Part Number B10734-01
Chapter 9: Flashback Technology: Recovering from Logical Corruptions
Topic: Enabling Flashback Database
http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10734/rcmflash.htm#1020720

 

These questions are derived from the Self Test Software Practice Test for Oracle exam #IZ0-043: Oracle Database 10g: Administration II.

Like what you see? Share it.Share on Google+Share on LinkedInShare on FacebookShare on RedditTweet about this on TwitterEmail this to someone
cmadmin

ABOUT THE AUTHOR

Posted in Archive|

Comment: