Core DBA OCP Questions

Core DBA OCP Questions

1. You are working on an instance started using the SPFILE. You want to move the Flash Recovery Area of your database to a new location. You want the Flashback log files to be stored in the new location. Given below are the steps to accomplish the task in random order:

  1. Shut down the instance.
  2. Change the value of the DB_RECOVERY_FILE_DEST initialization parameter to a new value.
  3. Execute the ALTER DATABASE FLASHBACK OFF command.
  4. Start up the instance and mount the database.
  5. Execute the ALTER DATABASE FLASHBACK ON command.
  6. Open the database.

Select the correct order in which these tasks need to be performed.

  • A. 2, 1, 4, 3, 5, 6
  • B. 1, 4, 3, 2, 6, 5
  • C. 1, 4, 2, 6, 3, 5
  • D. 3, 2, 1, 4, 5, 6

Ans: A


2.You perform differential incremental level 1 backups of your database on each working day and level 0 backup on Sundays, to tape. Which two statements are true about differential incremental backups? (Choose two.)

  • A.) The backup performed on Sundays contains all the blocks that have ever been used in the database.
  • B.) The backup performed on Sundays contains all the blocks that have changed since the last level 1 backup.
  • C.) The backup performed on each working day contains all the blocks that have changed since the last level 0 backup.
  • D.) The backup performed on Monday contains all the blocks that have changed since the level 0 backup, and every other working day contains all the blocks that have changed since the level 1 backup.

Ans: A, D


3.Identify two situations in which you can use Data Recovery Advisor for recovery. (Choose two.)

  • A) The database files are corrupted when the database is open.
  • B) The user has dropped an important table that needs to be recovered.
  • C) The archived redo log files are missing for which the backup is not available.
  • D)The database is not opening because the required database files are missing.

Ans: A, D


4.Your database is open and the LISTENER listener is running. The new DBA of the system stops the listener by using the command: LSNRCTL> STOP 

What happens to the sessions that are presently connected to the database instance?

  • A)The sessions are able to perform only queries.
  • B) The sessions are not affected and continue to function normally.
  • C)The sessions are terminated and the active transactions are rolled back.
  • D)The sessions are not allowed to perform any operations till the listener is started.

Ans: B


5.You executed this command to create a temporary table:

SQL> CREATE GLOBAL TEMPORARY TABLE report_work_area (startdate DATE, enddate DATE, class CHAR(20)) ON COMMIT PRESERVE ROWS; Which statement is true about the rows inserted into the REPORT_WORK_AREA table during a transaction?

  • A) The rows stay in the table only until session termination.
  • B) The rows stay in the table only until the next transaction starts on the table.
  • C) The rows are visible to all current sessions after the transaction is committed.
  • D)The rows stay available for subsequent sessions after the transaction is committed.

Ans: A


6.In which of the scenarios will the DBA perform recovery? (Choose all that apply.)

  • A) The alert log file is corrupted.
  • B) A tablespace is accidentally dropped.
  • C) One of the redo log members is corrupted.
  • D) A database user terminates the session abnormally.
  • E) The hard disk on which the data file is stored is corrupted.

Ans: B, E


7. Note the following structures in your database server:

  • 1.Extents
  • 2.OS Blocks
  • 3.Tablespace
  • 4.Segments
  • 5.Oracle Data Block

Which option has the correct arrangement of these structures from the smallest to the largest?

  • A) 2, 5, 1, 4, 3
  • B) 1, 2, 3, 4, 5
  • C) 5, 2, 1, 3, 4
  • D) 2, 1, 5, 4, 3

Ans: A


8.A user, who is authenticated externally, logs in to a remote machine and connects to the database instance. What action would you take to ensure that a user cannot connect to the database instance by merely logging in to a remote machine?

  • A) Set REMOTE_OS_ROLES to FALSE.
  • B) Set the OS_ROLES parameter to FALSE.
  • C) Set the REMOTE_OS_AUTHENT parameter to FALSE.
  • D) Set the REMOTE_LOGIN_PASSWORD_FILE parameter to NONE.

Ans: C


9.Your database instance is started using the server parameter file (SPFILE). You executed a command to change the value of the LOG_BUFFER initialization parameter: ALTER SYSTEM SET LOG_BUFFER=32M SCOPE=BOTH; What would be the outcome of this command?

  • A) The command succeeds only if Automatic Memory Management is not enabled.
  • B) The command succeeds, but you need to restart the database for changes to take effect.
  • C) The command returns an error because the size of the redo log buffer cannot be changed dynamically.
  • D) The parameter value is changed and it comes into effect as soon as space becomes available in the System Global Area (SGA).

Ans: C


10.User A executes the following command to drop a large table in your database:

SQL> DROP TABLE trans; While the drop table operation is in progress, user B executes the following command on the same table: SQL> DELETE FROM trans WHERE tr_type=’SL'; Which statement is true regarding the DELETE command?

  • A) It fails to delete the records because the records are locked in SHARE mode.
  • B) It deletes the rows successfully because the table is locked in SHARE mode.
  • C) It fails to delete the records because the table is locked in EXCLUSIVE mode.
  • D) It deletes the rows successfully because the table is locked in SHARE ROW EXCLUSIVE mode.

Ans: C


11.You work as a database administrator. You have been asked to use a centralized administrative tool to administer your database servers and application servers. In order to achieve the objective, which component would you configure on each database server?

  • A) Database Control
  • B) Management Server
  • C) Management Repository
  • D) Application Server Control
  • E) Oracle Management Agent

Ans: E


12.One the evening of April 22, you are working on a database created using Oracle Database 10g. This database operates in the ARCHIVELOG mode. You discover that you need crucial data that was dropped from the database at 8:00 a.m. No full backup has been taken after April 15. What would you do?

  • A) recover the database until April 10
  • B) recover the database until April 15
  • C) recover the database until 22 7:59 a.m.
  • D) recovery is not possible; manually re-create the object

Ans: C


13.You want to enforce a company’s business policy on several objects by using a single policy function. Which two types of policies can be assigned to the policy_type argument in the  dbms_rls.add_policy procedure to achieve the above objective? (Choose two.)

  • A) DBMS_RLS.STATIC
  • B) DBMS_RLS.DYNAMIC
  • C) DBMS_RLS.SHARED_STATIC
  • D) DBMS_RLS.CONTEXT_SENSITIVE
  • E) DBMS_RLS.SHARED_CONTEXT_SENSITIVE

Ans: C, E


14.The Automatic Database Diagnostic Monitor (ADDM) analysis runs every 60 minutes on your database. Your database if facing a series of interrelated problems over a period of two hours. You need to ensure that the ADDM analysis is run over a time span of two hours in future.What would you do?

  • A) Create two custom ADDM tasks.
  • B) Modify the AWR snapshot time interval to two hours.
  • C) Create a new scheduler window for a time period of two hours.
  • D) Modify the time interval by using the DBMS_JOB.INTERVAL procedure.
  • E) Modify the Automatic Workload Repository (AWR) snapshot retention period to two hours.

Ans: B


15.You are working on a test database where instance recovery takes a considerable amount of time.How can reduce the recovery time? Choose two.

A) By multiplexing the control files
B) By multiplexing the redo log files
C) By decreasing the size of redo log files
D) By configuring mean time to recover (MTTR) to a lower value
E) By setting the UNDO_RETENTION parameter to a higher value

Ans: C, D


16.Which background process does Automatic Shared Memory Management use to coordinate the sizing of memory components?

• A) PMON
• B) SMON
• C) MMNL
• D) MMAN
• E) MMON

Ans: D


17.You are not sure if Flashback Database is enabled. What database column and view can you query to see if the flashback logs are being created in the flash recovery area?

  • A) Query the initialization parameter FLASHBACK_DATABASE in VJINSTANCE.
  • B) Query the FLASHBACK_ENABLED column in VJDATABASE.
  • C) Query the FLASHBACK column in VJDATABASE..
  • D) Query the FLASHBACK_ON column in VJDATABASE.

Ans: D


18.Which of the following methods can you use to run a job chain? (Choose all that apply)

  • A) Create and schedule a job with CREATEJOB of type CHAIN.
  • B) Create and schedule a chain using the CREATE_CHAIN procedure.
  • C) Create and schedule a chain using the CREATE_JOB_CHAIN procedure.
  • D) Use the START_CHAIN procedure.
  • E) Use the RUN_CHAIN procedure.

Ans: A,E


19.You need to recover the database after a disk is lost but you are missing two archived redo log files. You need to run the RMAN RECOVER DATABASE UNTIL command. In what database state can you perform this recovery?

  • A) OPEN
  • B) MOUNT
  • C) RESTRICTED
  • D) NOMOUNT

Ans: B


20.You are restoring and recovering a database to a new host. What data dictionary view will you use to identify the names of the datafiles on the new database?

  • A) DBA_DATAFILE
  • B) VJTABLESPACE
  • C) VJDATAFILE
  • D) V$DATAFILE_COPY
  • E) VJBACKUP DATAFILE

Ans: C


21.Your flash recovery area becomes full when you are running an RMAN backup (and has not yet reached the value specified by DB_RECOVERY_FILE_DEST). It is the destination for archived redo log files and backups. What will happen to the database? (Choose the best answer.)

  • A) If you have the flash recovery area set toautoextend, the backup will continue with no problem.
  • B) The database will hang until you add more space to the flash recovery area.
  • C) RMAN will delete obsolete backups from the flash recovery area to accommodate new backups.
  • D) The RMAN backup will hang until you add more space to the flash recovery area.

Ans: C


22.You want to ensure the recoverability of the recovery catalog itself. Which of the following steps should you implement to maximize recoverability? (Choose all that apply.)

  • A) Configure the database for ARCHIVELOG mode.
  • B) Use BACKUP DATABASE PLUS ARCHIVELOG when you back up the recovery catalog.
  • C) Put the recovery catalog in the target database, and include a backup of the recovery catalog with every backup         .
  • D) Use BACKUP DATABASE PLUS ARCHIVELOG when you back up the recovery catalog.
  • E) Back up the recovery catalog to disk and tape after each target database backup (in other words, two separate media types).

Ans: A, B, D, E


23.For which of the following scenarios would you use user-managed incomplete recovery? (Choose all that apply.)

  • A) You have lost a data file and one or more of the archived redo log files are missing.
  • B) Several widespread logical errors have occurred in the database in the last day.
  • C) You want to create a test database as of a point in time in the past.
  • D) You have lost a data file and you have all archived redo log files available.
  • E)You have lost one of the copies of the control file.

Ans: A, B, C


24.What dynamic performance view can you query to list the supported platforms for transportable tablespaces?

  • A) V$TRANSPORTABLE_PLATFORM
  • B) V$TRANSPORTABLE_TABLESPACE
  • C) V$SUPPORTED_PLATFORMS
  • D) V$DATABASE PLATFORM

Ans: A


25.You issue the following command to enable block change tracking: SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING; However, you receive an error message, and the tracking file is not created. What is a possible reason for the error message?

  • A) The parameter DB_RECOVERY_FILE_DEST is not set.
  • B) You must use ALTER SYSTEM instead. )
  • C) Your database must be in the MOUNT state to create the block change tracking file.
  • D) The parameter DB_CREATE_FILE_DEST is not set.

Ans: D


26.Which of the following tablespaces can be recovered while the database is online? (Choose all that apply.)

  • A) USERS
  • B) UNDO
  • C) SYSAUX
  • D) SYSTEM 3
  • E) EXAMPLE

Ans: A,C,E


27.Which of the following commands successfully allocates a channel? (Choose all that apply.)

  • A) ALLOCATE CHANNEL T2 TYPE TAPE;
  • B) ALLOCATE CHANNEL T2 TYPE SBT;
  • C) ALLOCATE CHANNEL DB2 TYPE DISK PARMS=’SBT_LIBRARY=mmv.so';
  • D) ALLOCATE CHANNEL T1 TYPE DISK;

Ans: B,D


28.To enable remote connections to the database using the password file, what values should REMOTE_LOGIN_PASSWORDFILE have? (Choose all that apply.)

  • A) NONE
  • B) SHARED
  • C) EXCLUSIVE
  • D) REMOTE

Ans: B,C


29.You want to use Flashback Transaction Query to identify the SQL needed to reverse a transaction in the HR. EMPLOYEES table. Where can you find out the value of the transaction ID column XID to use when you query FLASHBACK_TRANSACTION_QUERY? (Choose the best answer.)

  • A) From the VERSIONS_XID column of HR. EMPLOYEES
  • B) From LogMiner
  • C) From the XID column of HR.EMPLOYEES
  • D) From the VERSIONS XID column of FLASHBACK VERSION QUERY

Ans: A


30.You configure AUTOBACKUP to ON in an RMAN session. When will RMAN back up the control file? (Choose all that apply)

  • A) When you run an RMAN BACKUP command
  • B) When you run an RMAN ALLOCATE command
  • C) When you add or drop a table in the SYSTEMtablespace
  • D) When you change the physical structure of the database, such as dropping atablespace
  • E) When you specify the INCLUDING CONTROLFILE clause

Ans: A


31.Which of the following methods can you use to enable duplexed RMAN backups? (Choose two answers.)

  • A) Use the initialization parameter RMAN_BACKUP_COPIES.
  • B) Use the BACKUP COPIES option in the RMAN CONFIGURE command.
  • C) Use the BACKUP COPIES option in the ALLOCATE CHANNEL command.
  • D) Use the COPIES option in the RMAN BACKUP command.

Ans: B,D


32.Setting which of the following initialization parameters enables Automatic Memory Management?

  • A) MEMORYJARGET
  • B) MEMORY_MAX_TARGET
  • C) SGATARGET
  • D) PGA AGGREGATE TARGET

Ans: A


33.Which of the following two parameters must you specify to use a flash recovery area for all of your backups? (Choose two answers.)

  • A) DB_RECOVERY_FILE_DEST_SIZE
  • B) DB_RECYCLE_CACHE_SIZE
  • C) DB_RECOVERY_FILE_DEST
  • D) DB RECOVERY FILE SIZE

Ans: A,C


34.You want to leverage resource consumer groups when using the Scheduler. Which Scheduler object uses resource plans directly?

  • A) A lightweight job
  • B) Ajob class
  • C) Ajob chain
  • D) A program

Ans: B


35.Your database is not using an RMAN recovery catalog. Which of the following commands are not available to you in this scenario? (Choose all that apply.)

  • A) CONFIGURE RETENTION . . .
  • B) BACKUP . . .KEEP FOREVER
  • C) DELETE . . . EXPIRED
  • D) REPORT SCHEMA … AT

Ans: B,D


36.Which of the following dynamic performance views can you use to show the total CPU time for a consumer group? (Choose the best answer.)

  • A) V$RSRC_CONSUMER_GROUP
  • B) V$SESSTAT
  • C) V$SYSSTAT
  • D) V$RSRC CONSUMER GRP

Ans: A


37.You want to designate one of the Linux system administrators as the recovery catalog owner. Which role must you grant to her Oracle user account?

  • A) RECOVERY_CATALOG
  • B) CATALOGjDWNER
  • C) RECOVERY_CATALOG_OWNER
  • D) SYSDBA

Ans: C


38.Fill in the blanks to make the following statement true: You use the________ command so that the view_______ contains the specified name associated with the session in the_________column.

  • A) ALTER SESSION ENABLE RESUMABLE TIMEOUT . . . NAME ‘statement description’, DBA_RESUMABLE_STATEMENT, NAME
  • B) ALTER SESSION ENABLE RESUMABLE TIMEOUT . . . NAME ‘statement description’,DBA_RESUMABLE, NAME
  • C) ALTER SESSION ENABLE RESUMABLE TIMEOUT . . . NAME ‘statement description’, DBA_RESUMABLE, SQL_NAME
  • D) ALTER USER ENABLE RESUMABLE TIMEOUT . . . NAME ‘statement description’, DBA_RESUMABLE, NAME

Ans: B


39.You execute this command: CONFIGURE RETENTION POLICY CLEAR; What is the retention policy set to after running the command?

  • A) Retention is set to the default of REDUNDANCY 1.
  • B) No retention policy will be defined.
  • C) Retention will be set according to the value of the initialization parameter CONTROL_FILE_RECORD_KEEP_TIME.
  • D) Retention is set to RECOVERY WINDOW OF 4 DAYS.
  • E) Retention is set to the default of REDUNDANCY 2.

Ans: A


40.When you perform a whole database backup with the command BACKUP DATABASE PLUS ARCHIVELOG, which database objects are included in the backup? (Choose the best answer.)

  • A) Alldatafiles and archived log files. The control file is included if you add the CONTROLFILE keyword, and the SPFILE is included if       your configuration includes automatic backup of the SPFILE.
  • B) Alldatafiles and archived log files. The SPFILE is included if you add the SPFILE keyword, and the control file is included if your           configuration includes automatic backup of the control file.
  • C) Alldatafiles and archived log files; after the operation successfully completes, the archived log files are automatically deleted.
  • D) Alldatafiles and archived log files only, regardless of the current RMAN settings.

Ans: B


41.You lose an entire redo log group that is in the CURRENT state. What happens to the database instance and how can you recover from the lost redo log group?

  • A) The instance will hang until you clear the redo log group.
  • B) The instance will shut down or crash and you can perform complete recovery using the archived redo log files.
  • C) The instance will skip to the next redo log group.
  • D) The instance will shut down or crash and you will have to perform cancel-based recovery.

Ans: D


42.Identify the correct relationships between resource consumer groups, resource plan directives, and resource plans. (Choose all that apply.)

  • A) Only one resource plan can be active at a time.
  • B) A user can be a member of only one resource consumer group.
  • C) One resource plan can have multiple plan directives.
  • D) A user can be assigned to more than one resource plan.

Ans: A,C


43.You are using the RMAN Data Recovery Advisor functionality. When an error occurs, which of the following commands can you use to monitor and maintain failures? (Choose all that apply.)

  • A) IDENTIFY FAILURE
  • B) REPAIR FAILURE
  • C) CHANGE FAILURE
  • D) ADVISE FAILURE
  • E) RESET FAILURE

Ans: B,C,D


44.You are using TISPITR to recover one or more tablespaces to a previous time in point or back to a specific SCN. What included in the recovery set? (Choose all that apply.)

  • A) A copy of the SYSTEMtablespace
  • B) A copy of the SYSAUXtablespace
  • C) A copy of the UNDOtablespace
  • D) A temporarytablespace for exported database objects from the auxiliary instance.
  • E) Thedatafiles from the tablespaces to be recovered

Ans: E


45.You are using a recovery catalog and you lose all copies of your control file. Which of the following sets commands will you use to restore the control file before you recover the database?

  • A) SET DBID 188390190; RESTORE CONTROLFILE;
  • B) SET DBID 188390190; RESTORE CONTROLFILE FROM AUTOBACKUP;
  • C) RESTORE CONTROLFILE FROM AUTOBACKUP;
  • D) RESTORE CONTROLFILE;

Ans: D


46.Identify the advantage of recovery an image copy of a datafile during RMAN backup. (Choose all that apply.)

  • A) During media recovery, fewer incremental updates need to be applied to the last image copy.
  • B) The overhead for the block change tracking file is minimal.
  • C) You do not need to perform additional image copy backups.
  • D) You no longer need to duplex your backups.
  • E) The time required to perform the backup and incremental updates to the image copy is reduced.

Ans: A,C


47.After you unregister a database from a recovery catalog, what happens to the backup metadata in the recovery catalog?

  • A) You must migrate the backup metadata from the recovery catalog to the control file before unregistering the database from the         recovery catalog.
  • B) The backup metadata is maintained in the control file for a number of days specified by the initialization parameter CONTROL_FILE_RECORD_KEEP_TIME and immediately removed from the recovery catalog.
  • C) The backup metadata is retained in the control file, and the database structure metadata remains in the recovery catalog.
  • D) The backup metadata is maintained in the control file and in the recovery catalog for a number of days specified by the initialization parameter CONTROL FILE RECORD KEEP TIME.

Ans: B


48.You switch from Automatic Memory Management (AMM) to Automatic Shared Memory Management (ASMM) by executing this       statement: alter system set memory_target = 0; Next, you restart the instance and set these values: alter system set                         sga_max_size = 10gb; alter system set sga_target = 8gb; alter system set shared_pool_size = 1 gb; How do the values of               SGA_TARGET, SGA_MAX_SIZE, and SHARED_POOL_SIZE vary throughout the day? (Choose the best answer.)

  • A) SGA_TARGET varies up to SGA_MAX_SIZE, which depends on the number of users running DML
  • B) SGA_TARGET can fall below 8GB if the PGA is low on memory.
  • C) SGA_TARGET stays at 8GB, and SHARED_POOL_SIZE never is above 1GB.
  • D) SGA_TARGET stays at 8GB, and SHARED_POOL_SIZE never falls below 1GB.

Ans: D


49.Which two dynamic performance views can you use to identify files that need to be backed up, regardless of whether you are in ARCHIVELOG mode or NOARCHIVELOG mode?

  • A) V$DATAFILE and VJLOGFILE
  • B) V$DATAFILE and VJTEMPFILE
  • C) V$LOG and VJLOGFILE
  • D) V$DATAFILE and VJCONTROLFILE

Ans: D


50.You want to create an archival backup for historical purposes. Where can you store the archival backup?

  • A) To a disk location outside of the recovery area or a tape group
  • B) To a disk device or the flash recovery area
  • C) In the flash recovery area only
  • D) To a tape device or the flash recovery area
  • E) On any combination of disk and tape devices

Ans: A


51.You lose all of the datafiles in your TEMP tablespace after the database shuts down. When the database starts again, what happens?

  • A) The database instance will not start and you must re-create the TEMPtablespace in MOUNT mode.
  • B) The database instance will not start and you will have to drop the old TEMPtablespace in NOMOUNT mode.
  • C) Thetempfile is re-created at the original location, a message is written to the alert log, and the database starts normally.
  • D) The database starts normally, a message is written to the alert log, and the DBA must manually re-create the TEMPtablespace.

Ans: C


52.Which of the following segment types can you shrink using Oracle’s segment shrink functionality? (Choose all that apply.)

  • A) Undo segments
  • B) Index segments
  • C) Temporary segments
  • D) Data segments

Ans: B,D


53.You are creating a duplicate (auxiliary) database for testing purposes. Which of the following initialization parameter files can you use on the auxiliary database to specify the filenames on the new database? (Choose all that apply.)

  • A) CONTROL_FILE_NAME_CONVERT
  • B) LOG_FILE_NAME_CONVERT
  • C) DB_FILE_NAME_CONVERT
  • D) DB_CREATE_FILE_DEST
  • E)CONTROL_FILES

Ans: B,C,E


54.Which of the following DML operations use a significant portion of the PGA? (Choose all that apply.)

  • A) Hash joins
  • B) Buffers for bulk load operations
  • C) Full table scans
  • D) Bitmap merges
  • E) Bitmap create

Ans: A,B,D,E


55.You are using RMAN to perform a fast switch to image copies for recovery purposes. You need to perform a SET NEWNAME command as part of the fast switch. What is the primary restriction when using SET NEWNAME? (Choose the best answer.)

  • A) The SET NEWNAME is available only within a RUN block.
  • B) The SET NEWNAME is available only as a standalone RMAN command.
  • C) You can use SET NEWNAME only for non-SYSTEMdatafiles.
  • D) The SET NEWNAME can change only the filename itself and not the directory path.

Ans: A


56.Identify the statement that is true about Flashback Data Archive. (Choose the best answer.)

  • A) You can use multipletablespaces for an archive, and each archive can have its own retention time.
  • B) You can have one archive, and for eachtablespace that is part of the archive you can specify a different retention period.
  • C) You can use multipletablespaces for an archive, and you can have more than one default archive per retention period.
  • D) If you specify a default archive, it must exist in only onetablespace.

Ans: B


57.Which of the following statements is true regarding lightweight jobs? (Choose all that apply.

  • A) You can use EM to create a lightweight job.
  • B) A lightweight job generates minimum redo when it runs.
  • C) A lightweight job is stored in the same table as other jobs.
  • D) You use the CREATE_LIGHTWEIGHT_JOB procedure to create a lightweight job.

Ans: B


58.As the DBA, you run this command on the recovery catalog database: SQL> GRANT RECOVERY_CATALOG_OWNER TO KMSHELT; what privileges do the user KMSHELT have?

  • A) KMSHELT can create users that own virtual private catalogs.
  • B) KMSHELT can immediately use an existing virtual catalog on the recovery catalog database.
  • C) KMSHELT can register a new database in her Virtual Private Catalog (VPC).
  • D) KMSHELT can potentially own a VPC but will not be able to unless the DBA grants other privileges first.

Ans: D


59.You run the following query against the dynamic performance view V$ASM_DISKGROUP: SQL> select group_number, name, type, total_mb, free_mb 2 from v$asm diskgroup; GROUP_NUMBER NAME TYPE TOTAL MB FREE MB

1 DATA NORMAL 24568 20798

2 RECOV NORMAL 24568 24090

3 DATA2 HIGH 16376 16221

What can you infer about the disk group DATA2?

  • A) Disk group DATA2 is mirrored at the operating system level or with a RAID array.
  • B) The TYPE column specifies disk group performance, not the redundancy level.
  • C) Disk group DATA2 is mirrored threefold or more.
  • D) Disk group DATA2 is mirrored with two disks in the failure group.

Ans: C


60.You drop the table ORDERJTEM, and because you have the RECYCLEBIN initialization parameter set to ON, the table and its indexes are moved to the recycle bin. Which of the following are valid names for these two objects in the recycle bin?

  • A) BINJORDERJTEMJO BIN$ORDERJTEM_PK! I; 0
  • B) BIN$QR9ZZ=49X2Z$0BIN$QR8ZZ=W9X2Z$0
  • C) BIN$ORDER_ITEM$0BIN$ORDERJTEM$1
  • D) BIN$QR9ZZ=49X2Z$0BIN!i;C!R9ZZ=49X2Z!i;i

Ans: B


61.You create and execute a stored local script using the following commands: create script userbackup{backup as copy tablespace users; delete noprompt obsolete;}execute script user_backup; What happens when you run these commands?

  • A) The script does not run because it must be executed within a RUN block.
  • B) An image copy of the USERS tablespace occurs and all previous backups and archived redo logs outside of the retention period or retention policy are deleted.
  • C) The script creation step fails because you must explicitly allocate one or more channels with a stored script.
  • D) The script does not run because you must specify a target database when you use a local script.

Ans: A


62.Which of the following can be a performance bottleneck in RMAN? (Choose all that apply)

  • A) Reading data
  • B) Writing data
  • C) Allocating channels
  • D) Validating blocks
  • E) Copying blocks
  • F) Multiplexing backups

Answer: A,B,D,E


63.Which of the following cannot be used as input to the SQL Tuning Advisor? (Choose all that apply.)

  • A) A single SQL statement provided by a user
  • B) An existing SQL Tuning Set (STS)
  • C) A preprocessed Database Replay workload
  • D) A schema name
  • E) SQL statement identified in EM as using excessive resources

Ans: C,D


64.How many copies of the alert log are stored in the directory specified by the initialization parameter DIAGNOSTIC_DEST?

  • A) One per database
  • B) One per instance
  • C) Two per database
  • D) Two per instance
  • E) One per incident directory

Ans: D


65.You lose all control file copies and your datafiles are intact, but your online log files are unavailable. Mow do you recover the database in this situation?

  • A) Re-create the control file and open the database with RESETLOGS.
  • B) Restore a backup copy of the control file, perform incomplete recovery, and manually specify the names of the online redo log files.       Also, open the database with RESETLOGS.
  • C) Restore a backup copy of the control file, perform complete recovery, and open the database with RESETLOGS.
  • D) Restore a backup copy of the control file, perform incomplete recovery, and open the database with RESETLOGS.

Ans: A


66.You need to determine the proper target time for TSPITR. What methods can you use to determine the correct target time? (Choose all that apply.)

  • A) Flashback Database
  • B) Flashback Version Query
  • C) Flashback Transaction Query
  • D) Flashback Query
  • E) Flashback Drop
  • F) Flashback Data Archive

Ans: B,C,D


67.Being a  database administrator.You decided to manage client and server connections using Local Naming method. When you try to connect to the database you get the following error:

ERROR:ORA-12154:TNS:couldnot resolve service name

Which network configuration files would you look into to resolve this error? Choose two.

  • A) snmp.ora
  • B) names.ora
  • C) spfile.ora
  • D) sqlnet.ora
  • E) listener.ora
  • F) tnsnames.ora

Ans: D, F


68.You find that users with  DBA role are using more CPU resources than what is allocated in their profiles. Which action would you take to ensure that resources limits are imposed on these users?

  • A) Assign the DEFAULT profile to the users
  • B) Set the RESOURCE_LIMIT parameter to TRUE in the parameter file
  • C) Create a new profile with CPU restrictions and assign it to the users
  • D) Specify the users as members of the DEFAULT_CONSUMER_GROUP
  • E) Revoke the DBA role and grant CONNECT and RESOURCE role to the users

Ans: B


69.You work as a database administrator. Your database is configured for automatic undo management. UNDO_RETENTION is set to 3 hours. You want to flash back a table that was created last year.How far back can the flashback query go?

  • A) 3 hours
  • B) 6 months
  • C) until last year
  • D) until last commit
  • E) until the point when the undo tablespace was refreshed
  • F) until the database is shut down and the memory erased

Ans: A 


70.Assume that you are a DBA. On a Monday morning,  you find the database instance aborted. After inspecting the alert log file, you  execute the STARTUP command in SQL*Plus to bring the instance up. What statement is true?

  • A) PMON coordinates media recovery.
  • B) SMON coordinates instance recovery.
  • C) PMON coordinates instance recovery.
  • D) Undo Advisor would roll back all uncommitted transactions.
  • E) SQL*PLUS reports an error with the message asking you to perform instance recovery.

Ans: B


71.Which statement regarding the dept and emp tables are true?

  • A) When you delete a row from the emp table, you would receive a constraint violation error.
  • B) When you delete a row from the dept table, you would receive a constraint violation error.
  • C) When you delete a row from the emp table, automatically the corresponding rows are deleted from the dept table.
  • D) When you delete a row from the dept table, automatically the corresponding rows are deleted from the emp table.
  • E) When you delete a row from the dept table, automatically the corresponding rows are updated with null values in the emp table.
  • F) When you delete a row from the emp table, automatically the corresponding rows are updated with null values in the dept table.

Ans: D


72.If  you have to clarify Oracle 10g. Which two steps are performed the first time any UPDATE statement is issued after the instance is started? Choose two.

  • A) Creating the parse tree of the statement
  • B) Writing the modified data blocks to the data files
  • C) Writing the modified data to the archived redo log files
  • D) Updating the control file to indicate the most recent checkpoint
  • E) Updating the data file header to indicate the most recent checkpoint
  • F) Reading the blocks to database buffer cache if they are not already there

Ans: C, D


73.You work as a database administrator.You started the instance using the init.orafile. You have two control files and three redo log groups in your database. You decided to protect the database against failures by adding one more control file. Which file is the appropriate way to perform this task?

  • A) Shut down the instance, copy the control file to the third location and open the database.
  • B) Abort the instance, copy the control file to the third location, modify the CONTROL_FILES parameter in the init.orafile and open the database.
  • C) Copy the control file to the third location, modify the CONTROL_FILES parameter in the init.orafile, restart the instance and open the database.
  • D) Shut down the instance, copy the control file to the third location, modify the CONTROL_FILES parameter in the init.orafile and open the database.
  • E) Shut down the instance, startup in the mount state, copy the control file to the third location, modify the CONTROL_FILES parameter in init.ora and open the database.

Ans: D


74.You executed the following command to back up the control file: 

ALTER DATABASE BACKUP CONTROLFILE TO TRACE; 

What do you find in the trace file?

  • A) Image of the control file
  • B) Location of the control file
  • C) Contents of the control file in text format
  • D) SQL command to re-create the database
  • E) SQL command to re-create the control file
  • F) Contents of the control file in binary format

Ans: E


75.You work as a database administrator. You find that the database performance degrades while you backup the database using Recovery Manager (RMAN). The database is running in shared server mode. The database instance is currently using 60% of total operating system memory. You suspect the shared pool fragmentation to be the reason.

Which action would you consider to overcome the performance degradation?

  • A) Configure Java Pool to cache the java objects.
  • B) Configure Streams Pool to enable parallel processing.
  • C) Increase Shared Pool size to cache more PL/SQL objects.
  • D) Increase Database Buffer Cache size to increase cache hits.
  • E) Configure Large Pool to be used by RMAN and shared server.
  • F) Increase the total System Global Area (SGA) size to increase memory hits.

Ans: E


76.In your production  database, the size of Database Buffer Cache needs to be increased immediately for the current as well as future instances of the database. The Oracle instance has been configured to accommodate any changes in the size of the memory structures. At this is production database, you want to accomplish this task with no impact on the user’s connections.

Which activity must you have completed before accomplishing this task?

  • A) You must have started the database instance in restricted mode.
  • B) You must have started the database instance in NORMAL mode.
  • C) You must have started the database instance with server parameter file.
  • D) You must have started the database instance but must not have mounted.
  • E) You must have started the database instance but must not have opened.

Ans: C


77.You are a database administrator. In your Oracle database 10g installation you have set ORACLE_BASEto/u01/app/oracle. 

Which objective will be achieved by this setting?

  • A) The Oracle kernel will be placed in this location.
  • B) The Oracle software will be placed in this location.
  • C) The server parameter file (SPFILE) will be placed in this location.
  • D) The database files will be placed in this location, if not specified explicitly.
  • E) The location will be considered for the base of Oracle Managed Files (OMF).
  • F) The location will be considered for the base of Optimal Flexible Architecture (OFA).

Ans: F


78.The operating system filecratabin the Linux platform gets updated whenever you create a new database on the same host machine. What kind of information is stored is stored in this file?

  • A) OracleSIDsonly
  • B) Oracle homes only
  • C) Oracle install timestamp
  • D) Oracle inventory pointer files
  • E) Oracle database creation timestamp
  • F) OracleSIDsand Oracle homes only
  • G) OracleSIDs, Oracle homes and flag for auto startus

Ans: G


79.If you want to clarify Oracle 10g. What statement about the Shared Server configuration is valid?

  • A) Program Global Area (PGA) is stored in Shared pool.
  • B) User session data and Cursor state are stored in Large pool and Stack space is stored Shared pool.
  • C) User session data is stored in Shared pool and Stack space and Cursor state are stored in Large pool.
  • D) User session data and Cursor state are stored in Large pool and Stack space is stored outside the System Global (SGA).
  • E) User session data and Cursor state are stored outside the System Global Area (SGA) and Stack space is stored inside the SGA.

Ans: D


80.Assume  that you a  DBA. While loading data into the STAFF table using Oracle Enterprise Manager 10g Database Control,you find the status of the job as failed. On further investigation, you find the following error message in the output log: ORA-01653 unable to extend table HR. STAFF by 8 in tablespace USERS Which task would you perform to load the data successfully without affecting the users who are accessing the table?

  • A) Restart the database instance and run the job
  • B) Truncate the STAFF table and run the job
  • C) Delete all rows from the STAFF table and run the job
  • D) Increase the size of the USERS tablespace the and run the job
  • E) Increase the size of the database default permanent tablespace and run the job

Ans: D


81.In your production database, you find that the database users are able to create and read files with unstructured data, available in any location on the host machine from an application. You want to restrict the database users to access files in a specific location on the host machine. What could be done to achieve this?

  • A) Modify the value for the UTL_FILE_DIR parameter in the parameter file
  • B) Grant read and write privilege on the operating system path to the database users
  • C) Modify the value for the LDAP_DIRECTORY_ACCESS parameter in the parameter file
  • D) Modify the value for the PLSQL_NATIVE_LIBRARY_DIR parameter in the parameter file
  • E) Create a directory object referring to the operating system path, and grant read and write privilege on the directory object to the database users.

Ans: A


82.In the database server the parameter PLSQL_CODE_TYPE has been set to NATIVE. Which object would be achieved by the setting?

  • A) The source PL/SQL code will be stored in native machine code.
  • B) The source PL/SQL code will be stored in interpreted byte code.
  • C) The compiled PL/SQL code will be stored in native machine code.
  • D) The compiled PL/SQL code will be stored in interpreted byte code.

Ans: C


83.The UNDO_RETENTION  parameter in your database is set to 1000 and undo retention is not guaranteed. Which statement regarding retention of undo data is correct?

  • A) Undo data becomes obsolete after 1,000 seconds.
  • B) Undo data gets refreshed after 1,000 seconds.
  • C) Undo data will be stored permanently after 1,000 seconds.
  • D) Committed undo data would be retained for 1,000 seconds if free undo space is available.
  • E) Undo data will be retained in the UNDO tablespace for 1,000 seconds, then it gets moved to the TEMPORARY tablespace to provide read consistency.

Ans: D


84.You work as a database administrator. You have been asked to use a centralized administrative tool to administer your database servers and application servers. In order to achieve the objective, which component would you configure on each database server?

  • A) Database Control
  • B) Management Server
  • C) Management Repository
  • D) Application Server Control
  • E) Oracle Management Agent

Ans: E


85.Users in your Company’s PROD database complain about the slow response of transactions. While investigating the reason you find that the transactions are waiting for the undo  segments to be available, and undo retention has been set to zero.What would you do to overcome this problem?

  • A) Increase the undo return
  • B) Create more undo segments
  • C) Create another undo tablespace
  • D) Increase the size of the undo tablespace

Ans: D


86.You are working on a test database where instance recovery takes a considerable amount of time.  How to reduce the recovery time? Choose two.

  • A) By multiplexing the control files
  • B) By multiplexing the redo log files
  • C) By decreasing the size of redo log files
  • D) By configuring mean time to recover (MTTR) to a lower value
  • E) By setting the UNDO_RETENTION parameter to a higher value

Ans: C, D


87.You have set the retention  period for Automatic Repository (AWR) statistics to four days and collection interval to 15 minutes. You want to view the statistics collected and stored in AWR snapshot. Which two methods would you use to view the AWR statistics? Choose two.

  • A) use enterprise manager
  • B) use DBMS_SQL package
  • C) use DBMS_AWR package
  • D) use PRVT_WORKLOAD package
  • E) query the AWR snapshot repository objects
  • F) use DBMS_WORKLOAD_REPOSITORY package

Ans: A, F


88.You find that reports are  running for a long time in your data warehousing applications. You suspect a lack  of indexes is the reason for the performance degradation. Which advisory component would you refer to, in order to determine the appropriate indexes? 

  • A) Memory Advisors
  • B) Segment Advisors
  • C) SQL Tuning and Access Advisors
  • D) Automated Workload Repository (AWR)
  • E) Automatic Database Diagnostic Monitor (ADDM)

Ans: C


89.You are working as a DBA. In the middle of a transaction, a user session was abnormally terminated but the instance is still up and the database is open.Which two statements are true in this scenario? Choose two.

  • A) Event Viewer gives more details on the failure.
  • B) The alert log file gives detailed information about the failure.
  • C) PMON rolls back the transaction and releases the locks.
  • D) SMON rolls back the transaction and releases the locks.
  • E) The transaction is rolled back by the next session that refers to any of the blocks updates by the failed transaction.
  • F) Data modified by the transaction up to the last commit before the abnormal termination is retained in the database.

Ans: C, F


90.Users in the PROD database complain about the slow response when accessing the database. In the Active Session Waiting: Configuration page you find that the majority of the wait time is for the redo log buffer. Further investigation using the Run Queue graph and operating system tools reveals that the redo log files are on the same disks, and that the disk I/O for the redo log files is the reason for the performance degradation. Which two actions would you take to gain performance? Choose two.

  • A)  Increase the size of redo log files
  • B) Increase the size of redo log buffer
  • C) Increase the number of redo log files
  • D) Place the redo log files on a separate disk
  • E) Decrease the number of redo log groups to two
  • F) Increate the number Log Writer (LGWR) process

Ans: B, D


91.Database is in ARCHIVELOG mode. The data file that belongs to the SYSTEM tablespace has become corrupted. Up to what point can you recover the database?

  • A) Until last commit
  • B) Until the time you perform recovery
  • C) Until the time the date file got corrupted
  • D) Until the point where the last transaction begun
  • E) You cannot recover the SYSTEM tablespace and must re-create the database.

Ans: A


92.The UNDO_RETENTION  parameter in your database is set to 1000 and undo retention is not guaranteed.  Which statement regarding retention of undo data is correct?

  • A) Undo data becomes obsolete after 1,000 seconds.
  • B) Undo data gets refreshed after 1,000 seconds.
  • C) Undo data will be stored permanently after 1,000 seconds.
  • D) Committed undo data would be retained for 1,000 seconds if free undo space is available.
  • E) Undo data will be retained in the UNDO tablespace for 1,000 seconds, then it gets moved to the TEMPORARY tablespace to provide read consistency.

Ans: D


93.When  the database is open, a media failure has occurred, resulting in loss of all the control files in your database. Which statement regarding the database instance is true in this scenario?

  1. The instance would hang.
  2. The instance needs to be shut down.
  3. The instance would be in the open state.
  4. The instance would abort in such cases.
  5. The instance would be in the open and invalid state.
  6. The instance would in the open state, but all the background processes will be restarted.

Ans: D


94.In a production environment, users complain about the slow response time when accessing the database. You have not optimized the memory usage of the Oracle instance and you suspect the problem to be with the memory.To which type of object would you refer to determine the cause of the slow response?

  • A) The trace file
  • B) The fixed views
  • C) The data dictionary views
  • D) The operating system log fields
  • E) The dynamic performance views.

Ans: E


95.As a result of  performance analysis, you created an index on the prod_name column of the  prod table, which contains about ten thousand rows. Later, you updated a product name in the table.How does this change affect the index?

  1. A) A leaf will be marked as invalid.
  2. B) An update in a leaf row takes place.
  3. C) The index will be updated automatically at commit.
  4. D) A leaf row in the index will be deleted and inserted.
  5. E) The index becomes invalid when you make any updates

Ans: D


96.In your database server, the parameter PLSQL_OPTIMIZE_LEVEL has been set to 2. What would this setting achieve?

  • A) It degrades the run time and compiler performance.
  • B) It provides better run time and compiler performance.
  • C) It provides better optimization of the statement during parse time.
  • D) It forces the rule based optimizer to be used for statement optimization.
  • E) It provides better run time performance but slightly degraded compiler performance.
  • F) It provides better run time performance but slightly degraded run time performance.

Ans: E


97.Assume that you are a DBA  Redo log files are not multiplexed in your database. Redo log blocks are corrupted in group 2, and archiving has stopped. All the redo logs are filled and database activity is halted. Database writer has written everything to disk. Which command would you execute to proceed further?

  • A) RECOVER LOFIEL BLOCK GROUP 2;
  • B) ALTER DATABASE DROP LOGFILE GROUP 2;
  • C) ALTER DATABASE CLEAR LOGFILE GROUP 2;
  • D) ALTER DATABASE RECOVER LOGFILE GROUP 2;
  • E) ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;

Ans: E


98.If  you have to clarify Oracle 10g. Which condition in your database forces you to perform only consistent backups?

  • A) The database is in mount state.
  • B) One of the tablespaces is offline.
  • C) One of the tablespaces is read only.
  • D) The database operates in ARCHIEVELOG mode.
  • E) The database operates in NOARCHIEVELOG mode.

Ans: E


99.If  your database is in NOARCHIVELOG mode. The database has been configured with three redo logs groups, and there have been five log switches. You lost one non-system-critical data file from your database. Which method would you use to recover the data file?

  • A) Restore all the data files and open the database
  • B) Create the data file and perform tablespace recovery
  • C) No need to restore any file, just recover the database
  • D) Restore only the system data file and recover the database
  • E) Restore only lost date file and perform tablespace recovery

Ans: A


100.Your database is opened in NOARCHIVELOG mode. You decide to place the database in ARCHIVELOG mode. How would you do this? Select two.

  • A) Execute the ALTER DATABASE ARCHIVELOG statement
  • B) Select ARCHIVELOG option in the database control and restart the database to apply the changes.
  • C) Shut down and start the instance, and in the NOMOUNT state execute the ALTER
  • D) DATABASE ARCHIVELOG statement.
  • E) Shut down and start the instance, mount the database, then execute the ALTER
  • F) DATABASE ARCHIVELOG statement.

Ans: B, D


101.You work as a database administrator. You have been asked to use a centralized administrative tool to administer your database servers and application servers. In order to achieve the objective, which component would you configure on each database server?

  • A) Database Control
  • B) Management Server
  • C) Management Repository
  • D) Application Server Control
  • E) Oracle Management Agent

Ans: E


102.The UNDO_RETENTION  parameter in your database is set to 1000 and undo retention is not guaranteed.  Which statement regarding retention of undo data is correct?

  • A) Undo data becomes obsolete after 1,000 seconds.
  • B) Undo data gets refreshed after 1,000 seconds.
  • C) Undo data will be stored permanently after 1,000 seconds.
  • D) Committed undo data would be retained for 1,000 seconds if free undo space is available.
  • E) Undo data will be retained in the UNDO tablespace for 1,000 seconds, then it gets moved to the TEMPORARY tablespace to provide read consistency.

Ans: D


103.You are working on a test database where instance recovery takes a considerable amount of time.  How to reduce the recovery time? Choose two.

  • A) By multiplexing the control files
  • B) By multiplexing the redo log files
  • C) By decreasing the size of redo log files
  • D) By configuring mean time to recover (MTTR) to a lower value
  • E) By setting the UNDO_RETENTION parameter to a higher value

Ans: C, D


104.You have set the retention  period for Automatic Repository (AWR) statistics to four days and collection interval to 15 minutes. You want to view the statistics collected and stored in AWR snapshot. Which two methods would you use to view the AWR statistics? Choose two.

  • A) use enterprise manager
  • B) use DBMS_SQL package
  • C) use DBMS_AWR package
  • D) use PRVT_WORKLOAD package
  • E) query the AWR snapshot repository objects
  • F) use DBMS_WORKLOAD_REPOSITORY package

Ans: A, F


105.Users in the PROD database complain about the slow response when accessing the database. In the Active Session Waiting: Configuration page you find that the majority of the wait time is for the redo log buffer. Further investigation using the Run Queue graph and operating system tools reveals that the redo log files are on the same disks, and that the disk I/O for the redo log files is the reason for the performance degradation.Which two actions would you take to gain performance? Choose two

  • A) Increase the size of redo log files
  • B) Increase the size of redo log buffer
  • C) Increase the number of redo log files
  • D) Place the redo log files on a separate disk
  • E) Decrease the number of redo log groups to two
  • F) Increase the number Log Writer (LGWR) process

Ans: B, D


106.If  your database is in  NOARCHIVELOG mode. The database has been configured with three redo logs groups, and there have been five log switches. You lost one non-system-critical data file from your database.Which method would you use to recover the data file?

  • A) Restore all the data files and open the database
  • B) Create the data file and perform tablespace recovery
  • C) No need to restore any file, just recover the database
  • D) Restore only the system data file and recover the database
  • E) Restore only lost date file and perform tablespace recovery

Ans: A


107.You are using three database, DB01, DB02, and DB03, on different host machines in your development environment. The database server configuration, such as IP address and listener port number, change frequently due to development requirements, and you have the task of notifying the developers of the changes. Which connection method would you use to overcome this overhead?

  • A) Host naming
  • B) Local naming
  • C) Easy Connect
  • D) External naming
  • E) directory naming

Ans: E


108.Exhibit:-
While creating a new user or changing the password for existing users, the password must adhere to the restrictions specified in the exhibit.Which option would you use to achieve this objective?

  • A) Use a trigger to validate the password
  • B) Use a user defined PL/SQL block to validate the password
  • C) Use a profile to include the password verify function to validate the password
  • D) Use Oracle’s default password verification procedure to validate the password
  • E) Use a procedure name in the CREATE USER command to validate the password

Ans: C


109.Database is in ARCHIVELOG mode. The data file that belongs to the SYSTEM tablespace has become corrupted. Up to what point can you recover the database?

  • A) Until last commit
  • B) Until the time you perform recovery
  • C) Until the time the date file got corrupted
  • D) Until the point where the last transaction begun
  • E) You cannot recover the SYSTEM tablespace and must re-create the database.

Ans: A


110.Users in your Company’s PROD database complain about the slow response of transactions. While investigating the reason you find that the transactions are waiting for the undo  segments to be available, and undo retention has been set to zero.What would you do to overcome this problem?

  • A) Increase the undo return
  • B) Create more undo segments
  • C) Create another undo tablespace
  • D) Increase the size of the undo tablespace

Ans: D


111.You find that reports are  running for a long time in your data warehousing applications. You suspect a lack  of indexes is the reason for the performance degradation.Which advisory component would you refer to, in order to determine the appropriate indexes?

  • A) Memory Advisors
  • B) Segment Advisors
  • C) SQL Tuning and Access Advisors
  • D) Automated Workload Repository (AWR)
  • E) Automatic Database Diagnostic Monitor (ADDM)

Ans: C


112.You  work as a DBA. In the middle of a transaction, a user session was abnormally terminated but the instance is still up  and the database is open.Which two statements are true in this scenario? Choose two

  • A) Event Viewer gives more details on the failure.
  • B) The alert log file gives detailed information about the failure.
  • C) PMON rolls back the transaction and releases the locks.
  • D) SMON rolls back the transaction and releases the locks.
  • E) The transaction is rolled back by the next session that refers to any of the blocks updates by the failed transaction.
  • F) Data modified by the transaction up to the last commit before the abnormal termination is retained in the database.

Ans: C, F


113.As a result of  performance analysis, you created an index on the prod_name column of the prod table, which contains about ten thousand rows. Later, you updated a product name in the table. How does this change affect the index?

  • A) A leaf will be marked as invalid.
  • B) An update in a leaf row takes place.
  • C) The index will be updated automatically at commit.
  • D) A leaf row in the index will be deleted and inserted.
  • E) The index becomes invalid when you make any updates

Ans: D


114.Assume that you are a DBA.  Redo log files are not multiplexed in your database. Redo log blocks are corrupted in group 2, and archiving has stopped. All the redo logs are filled and database activity is halted. Database writer has written everything to disk. Which command would you execute to proceed further?

  • A) RECOVER LOFIEL BLOCK GROUP 2;
  • B) ALTER DATABASE DROP LOGFILE GROUP 2;
  • C) ALTER DATABASE CLEAR LOGFILE GROUP 2;
  • D) ALTER DATABASE RECOVER LOGFILE GROUP 2;
  • E) ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;

Ans: E


115.Your database is opened in NOARCHIVEKLOG mode. You decide to place the database in ARCHIVELOG mode. How would you do this? Select two.

  • A) Execute the ALTER DATABASE ARCHIVELOG statement
  • B) Select ARCHIVELOG option in the database control and restart the database to apply the changes.
  • C) Shut down and start the instance, and in the NOMOUNT state execute the ALTER DATABASE ARCHIVELOG statement.
  • D) Shut down and start the instance, mount the database, then execute the ALTER DATABASE ARCHIVELOG statement.

Ans: B, D


116.If  you have to clarify Oracle 10g. Which condition in your database forces you to perform only consistent backups?

  • A) The database is in mount state.
  • B) One of the tablespaces is offline.
  • C) One of the tablespaces is read only.
  • D) The database operates in ARCHIEVELOG mode.
  • E) The database operates in NOARCHIEVELOG mode.

Ans: E


117.In your database server, the parameter PLSQL_OPTIMIZE_LEVEL has been set to 2. What would this setting achieve?

  • A) It degrades the run time and compiler performance.
  • B) It provides better run time and compiler performance.
  • C) It provides better optimization of the statement during parse time.
  • D) It forces the rule based optimizer to be used for statement optimization.
  • E) It provides better run time performance but slightly degraded compiler performance.
  • F) It provides better run time performance but slightly degraded run time performance.

Ans: E


118.In a production environment, users complain about the slow response time when accessing the database. You have not optimized the memory usage of the Oracle instance and you suspect the problem to be with the memory.To which type of object would you refer to determine the cause of the slow response?

  • A) The trace file
  • B) The fixed views
  • C) The data dictionary views
  • D) The operating system log fields
  • E) The dynamic performance views.

Ans: E


119.When  the database is open, a media failure has occurred, resulting in loss of all the control files in your database. Which statement regarding the database instance is true in this scenario?

  • A) The instance would hang.
  • B) The instance needs to be shut down.
  • C) The instance would be in the open state.
  • D) The instance would abort in such cases.
  • E) The instance would be in the open and invalid state.
  • F) The instance would in the open state, but all the background processes will be restarted.

Ans: D


120.To clarify Oracle 10g. Which statement regarding the contents of the V$PARAMETER view is true?

  • A) Displays only the list of default values
  • B) Displays only the list of all basic parameters
  • C) Displays the currently in effect parameter values
  • D) Displays only the list of all advanced parameters
  • E) Displays the list of all the parameter files of a database
  • F) Displays the current contents of the server parameter file.

Ans: C


121.In your development environment, the developers are responsible for modifying the table structure according to the application requirements. However, you want to keep track of the ALTER TABLE commands being executed by developers, so you enable auditing to achieve this objective.Which two views would you refer to find out the audit information? Choose two.

  • A) DBA_AUDIT_TRAIL
  • B) DBA_AUDIT_SESSION
  • C) DBA_FGA_AUDIT_TRAIL
  • D) DBA_COMMON_AUDIT_TRAIL

Ans: A, C


122.To clarify Oracle 10g. Which two statements regarding the LOGGING clause of the CREATE TABLESPACE… statement are correct? Choose two.

  • A) This clause is not valid for a temporary or undo tablespace.
  • B) If the tablespace is in the NOLOGGING mode, no operation on the tablespace will
  • generate redo.
  • C) The tablespace will be In the NOLOGGING mode by default, if not specified while
  • creating a tablespace.
  • D) The tablespace-level logging attribute can be overridden by logging specification at the
  • table, index, materialized view, materialized view log, and partition levels.

Ans: A, D


123.You are a DBA. Users complain that their query is taking a long time to complete. You suspect the database buffer cache size to be the reason, and you define an alert to be generated when the buffer cache hit metric goes below 85%. When you observe the dynamic performance views, you find that the buffer cache hit ratio is 81% but no alerts are being generated.What could be the reason for this problem?

  • A)  The advisory memory has been turned off.
  • B) The DB_CACHE_ADVICE parameter has been set to OFF in the parameter file.
  • C) The STATISTICS_LEVEL parameter has been set to BASIC in the parameter file.
  • D) The TIMED_STATISTICS parameter has been set to FALSE in the parameter file.
  • E) The STATISTICS_LEVEL parameter has been set to TYPICAL in the parameter file.

Ans: C


124.Assume that you are a database administrator. In your transaction application, you have scheduled a job to update the optimizer statistics at 05:00 pm every Friday. The job has successfully completed. Which three pieces of information would you check to confirm that the statistics have been collected? Choose three.

  1. Average row size
  2. Last analyzed date
  3. Size of table in bytes
  4. Size of table in database blocks
  5. Number of free blocks in the free list
  6. Number of extents present in the table.

Ans: A, B, D


125.You want to be notified when the number of disk I/O increases by 10% of what it was yesterday. You create a baseline metric for yesterday’s disk I/O performance. How do you use the baseline metrics to achieve your objective?

  • A) Save the baseline in the database
  • B) Save the baseline in the Database Control Home Page
  • C) Create a procedure to read and apply the baseline to disk I/O metrics
  • D) Apply the baseline to the disk I/O metrics in the Mange Metrics page
  • E) Alter the disk I/O metrics to include baseline by using the ALTER METRICS command

Ans: D


126.In the PRODUCTION database, you have granted RESUMABLE system privilege to the CONNECT role. Resumable space operation has been enabled for all user session. You want users NOT to be aware of any kind of space-related problems while performing transactions. Instead, you want the problem to be resolved by a database trigger automatically. Which combination of triggering time and event would you use to achieve this objective?

  • A) AFTER CREATE
  • B) AFTER SUSPEND
  • C) BEFORE CREATE
  • D) AFTER TRUNCATE
  • E) BEFORE SUSPEND
  • F) AFTER INSERT OR DELETE OR DELETE
  • G) BEFORE INSERT OR DELETE OR DELETE

Ans: B


127.Assume that you are DBA. You enabled tracing while creating a listener to your database. What additional information is available to you when you enable tracing?

  • A) Information about each Oracle Net connection.
  • B) Information about each entry in the Linstener.orafile
  • C) Information about the changed done in the database
  • D) Information about the different listeners in your system
  • E) Information about all the non database files required to operate the database

Ans: A


128.Your database is in NOARCHIVELOG mode and you want to perform a backup. Which type of backup would be appropriate in this case?

  • A) Hot backup
  • B) Online backup
  • C) consistent backup
  • D) incremental backup
  • E) inconsistent backup

Ans: C


129.You are a DBA. Because of space constraints, you decided to manually shrink the table. You executed the ALTERTABLE ….SHRINK SPACE statement to shrink the space and you receive an error as follows:

ERROR at Line 1:

ORA-10635: Invalid segment or tablespace type What could be the reason?

  • A) The table is partitioned
  • B) The table name is wrong.
  • C) It is an index-Organized table (IOT)
  • D) The table is stored in locally managed tablespace.
  • E) The table is stored in a tablespace where segment space is managed manually.

Ans: E


130.If you want to create your  database with the automatic undo management feature enable. Which two tasks must you perform to achieve this? Choose two.

  • A) Set the UNDO_RETENTION parameter to AUTO.
  • B) Create an initial undo segment in undo tablespace.
  • C) Set the UNDO_RETENTION parameter to 900 or more
  • D) Set the initialization parameter UNDO_MANAGEMENT to AUTO
  • E) Set the UNDO_MANAGEMENT parameter to the name of an undo tablespace

Ans: C, E


131.You are a DBA. In your organization, you have four databases, DB01, DB02, DB03, and DB04, which are located in Chicago, Frankfurt, Sydney, and Paris respectively, and which were created using Database Configuration Assistant (DBCA).  You want to perform administrative tasks on these databases that include startup and shutdown, taking regular backups, and so on, from Frankfurt using Oracle tools. Which Oracle file would you enable to perform this task?

  • A) Control file
  • B) Password file
  • C) Parameter file
  • D) Online redo log file
  • E) Listener controller file
  • F) Server parameter file

Ans: B


132.Your are a DBA. When you tried to connect to the database, you received the following error:

ERROR:

ORA-12541:TNS:no_listener

You found that the listener is not running on the server, so you issued the following command in the Listener Control utility to start the listener. lsnrctl>START What statement is true?

  • A) It would attempt to start the default listener
  • B) It would start the most recently created listener.
  • C) It would display an error with a message asking for the listener name to be specified.
  • D) It would show you a list of listeners and prompts for the listener name that you want to start.

Ans: A


133.You enabled tracing while creating a listener to your database. What additional information is available to you when you enable tracing?

  • A) Information about each Oracle Net connection.
  • B) Information about all the non database files required to operate the database
  • C) Information about the changed done in the database
  • D) Information about the different listeners in your system

Ans: A


134.The data file belonging to the SYSTEM tablespace is corrupted and no backup of the file is available. How do you recover the data file?

  • A) The data file cannot be recovered.
  • B) The date file can be restored from the SYSTEM auto backup.
  • C) Take the tablespace offline,drop and re-create it.
  • D) Recovery Manager (RMAN) is used to recover the data file.
  • E) The ALTER DATABASE CREATE DATAFILEcommand is used to recover the datafile.

Ans: A


135.You are working on Oracle Database 10g. To forward client connections to an instance, the listener must know information such as the name of the instance and where the instance’s ORACLE_HOME is located. How does the listener get this information? Choose two.

  • A) It gets it from the parameter file.
  • B) The listener prompts for details on startup.
  • C) The listener gathers information from the control file.
  • D) The instance automatically registers with the default listener.
  • E) The listener gets this information through the listener.orafile.

Ans: D, E


136.Your database is not configured for session failover. Your tnsnames.orafile contains the details shown in the exhibit.Which feature is enabled in this case?

  • A) Load balancing
  • B) Instance failover
  • C) Database failover
  • D) Connect-time failover
  • E) Transparent Application Failover (TAF)

Ans: D


137.You work as a DBA You suspect that in one of your applications in the customer table is being accessed by some unauthorized users. Which option would you use to monitor the queries being executed on the customer table?

  • A) Monitor the alert.logfile
  • B) Enable server-side SQL tracing for user sessions
  • C) Enable fine-grained auditing for the customer table
  • D) Enable Fine-Grained Access Control (FGAC) for the customer table
  • E) Write a database trigger on the customer table on the SELECT event

Ans: D


138.Your database is in the MOUNT state and you execute the following command to open it

ALTER DATABASE OPEN; What two actions are performed as a result of this command? Choose two.

  • A) All control files are opened
  • B) All redo files are opened
  • C) The password file is opened
  • D) The parameter file is opened
  • E) All online data files are opened

Ans: B, E


139.Client connection requests are failing because the listener is not responding. You want to forward client connect requests to another listener if one listener is not responding. How do you achieve this?

  • A) Enable instance failover
  • B) Configure shared server
  • C) Enable database failover
  • D) Enable connect-time failover
  • E) Enable Transparent Application Failover (TAF)

Ans: D


140.You added a tablespace to your database. Because of the changes to the physical structure of your database, you performed a backup of the control file to trace. After this, you lost only the control files and the instance aborted due to a hardware failure. How would you proceed to recover the database?

  • A) Start the instance and open the database, instance recovery automatically recovers control files
  • B) Create a file based on the contents of the trace file and save it to the control file locations and start the database
  • C) Re-create the database (The recovery of the control file is not possible because you have lost all the control files.)
  • D) Start the instance, in the NOMOUNT state, re-create the control file using the CREATECONTROLFILE command stored in the trace file.Start the instance and MOUNT the database, then re-create the control file using the
  • E) CREATE CONTROLFILE command stored in the trace file.

Ans: D


141.You want to enforce a company’s business policy on several objects by using a single policy function.Which two types of policies can be assigned to the policy_type argument in the dbms_rls.add_policy procedure to achieve the above objective? (Choose two.)

  • A) DBMS_RLS.STATIC
  • B) DBMS_RLS.DYNAMIC
  • C) DBMS_RLS.SHARED_STATIC
  • D) DBMS_RLS.CONTEXT_SENSITIVE
  • E) DBMS_RLS.SHARED_CONTEXT_SENSITIVE

Ans: C, E


142.The Automatic Database Diagnostic Monitor (ADDM) analysis runs every 60 minutes on your database. Your database if facing a series of interrelated problems over a period of two hours. You need to ensure that the ADDM analysis is run over a time span of two hours in future.What would you do?

  • A) Create two custom ADDM tasks.
  • B) Modify the AWR snapshot time interval to two hours.
  • C) Create a new scheduler window for a time period of two hours.
  • D) Modify the time interval by using the DBMS_JOB.INTERVAL procedure.
  • E) Modify the Automatic Workload Repository (AWR) snapshot retention period to two hours.

Ans: B 


143.You are connecting to an Oracle database server from a client by using the following connect string:

SQL> CONNECT hr/hr@pdserver.us.oracle.com:1521/proddb Which naming method is being used in this case?

  • A) Local Naming
  • B) Easy Connect
  • C) External Naming
  • D) Directory Naming

Ans: B


143.While setting up the database for your production environment, you want to create a user with following requirements.

1.The objects created by the user must be stored in a tablespace, TBS1, if the TABLESPACE option is not defined during the object creation.

2.The user should be able to use 10MB of space in the tablespace TBS1.

3.The user must change the password immediately after logging in for the first time.

Which three options of the CREATE USER command would you use to achieve this objective? (Choose three.)

  • A) profile
  • B) account lock
  • C) account unlock
  • D) password expire
  • E) quota 10MB on TBS1
  • F) password never expire
  • G) default tablespace TBS1

Ans: D,E,G


144.You received the following error while working on your database:

ORA-01555: snapshot too old. In which situation would you receive such an error?

  • A) You performed rollback after commit.
  • B) If a log switch occurs in your database.
  • C) The archiver fails to generate an archived log file.
  • D) Instance recovery fails to find one of the redo log files.
  • E) A long-running query is unable to get read-consistent image.
  • F) If you perform manual undo management operations in auto mode.

Ans: E


145.You want to administer your database servers, which are running on different host machines, using the Web-enabled grid control interface. You have configured the agent on the host machines. You have started the Oracle Database 10g Grid Control utility on your machine.Which two additional components would you configure to achieve this objective? Choose two

  • A) Application Server Control
  • B) Oracle Management Service
  • C) Oracle Enterprise Manager Repository
  • D) Oracle Enterprise Manager Database Control

Ans: C, D


146.You are a DBA. You are using Recovery  Manager (RMAN) to perform backups in your databases. Which four backup operations can be performed using RMAN? Choose four.

  • A) A backup of the target database when it is mounted
  • B) A backup of online redo log files when the database is open
  • C) A backup of only used data blocks in the file being backup up
  • D) A backup of online, read/write data files when the database is open
  • E) A backup of only those data blocks that have changed since a previous backup
  • F) A backup of online, read/write data files when the database is in the NOMOUNT state.

Ans: A, C, D, E


147.You observe that in your PROD database, customer information is being modifie by some unauthorized users. You want to keep track of all of the transactions happening on the table using PL/SQL. Which type of PL/SQL subprogram or construct would you use to accomplish this task?

  • A) functions
  • B) packages
  • C) procedures
  • D) database triggers
  • E) anonymous PL/SQL block

Ans: D


148.Your database is in the NOARCHIVELOG mode. Since last night’s offline backup, the logs in group 1 have been written to twice. This morning, the SYSTEM data file has become corrupted. Up to what point can it be recovered?

  • A) until the last commit
  • B) cannot be recovered
  • C) until the last offline backup
  • D) until the beginning of the last transaction

Ans: C


149.Where do you find information about a missing redo log file?

  • A) trace file
  • B) audit trail
  • C) control file
  • D) alert log file
  • E) event viewer

Ans: D


150.Your database is running in NOARCHIVELOG mode. You want to put the database in ARCHIVELOG mode. Which two statements regarding archive log destinations are true? (Choose two.)

  • A) The destination must be global only.
  • B) A maximum of five destinations can be assigned.
  • C) A maximum of ten different destinations can be assigned.
  • D) All the destinations are determined by the server automatically.
  • E) The destination may be local or remote for a Standby Database.
  • F) The destination can be changed by setting the LOG_ARCHIVE_FORMAT initialization parameter.

Ans: C,E


 

Contact Us

DBA Technologies
Aditya Enclave, 303A, Nilgiri Block
Ameerpet, Hyderabad - India
Telephone: 040 6555 5689
Mobile: +91 944 11 72718
Email: training@dbatechnologies.net

Address 2:
3rd Floor, TMC Building
Dilsukhnagar, Hyderabad - India
Telephone: 040-24052718

Important Links

© 2014 DBA Technologies. All Rights Reserved. | Website Design by BWT