Oracle Oracle Partner Network 1Z0-497 Questions & Answers

• Question 21:

  Examine the output:

  SQL> SELECT index_name, status FROM dba_indexes WHEREstatus='UNUSABLE'; INDEX_NAME STATUS

  EIND UNUSABLE

  Which two statements about this index are true?

  A. It is ignored by the query optimizer.

  B. It is not used while the index is being rebuilt.

  C. The index cannot be rebuilt, and has to be re-created.

  D. The index is automatically rebuilt when used the next time.

  Correct Answer: AB

  *

  Oracle indexes can go into a UNUSABLE state after maintenance operation on the table or if the index is

  marked as 'unusable' with an ALTER INDEX command. A direct path load against a table or partition will

  also leave its indexes unusable. Queries and other operations agains a table with unuable indexes will

  generate errors:

  ORA-01502: index `string.string' or partition of such index is in unusable state

  *

  The following SQL will print out a list of alter commands that can be executed to fix unusable indexes:

  Indexes:

  SELECT 'alter index '||index_name||' rebuild tablespace '||tablespace_name ||';' FROM dba_indexes

  WHERE status = 'UNUSABLE';

• Question 22:

  Which three events signal a database writer (DWR) to write buffers to disk?

  A. The database advances a checkpoint.

  B. A client process commits a transaction.

  C. The redo log buffer becomes full.

  D. A server process cannot find non-dirty blocks after scanning a threshold number of buffers.

  E. A tablespace is changed to read-only status.

  F. The database is shut down in immediate mode.

  Correct Answer: ADE

  AE: When tablespace is changed to read only status it will issue a Partial checkpoint. During db shutdown a checkpoint is also issued. DWR writes when there is a checkpoint.

• Question 23:

  Which three background processes are mandatory in a database Instance started with a minimally configured Initialization parameter file?

  A. Process Monitor Process (PMON)

  B. Flashback Data Archive process (FBDA)

  C. Space Management Coordinator process (SMCO)

  D. Recoverer process (RECO)

  E. Listener Registration process (LREG)

  Correct Answer: ADE

  The mandatory background processes are present in all typical database configurations. These processes run by default in a database instance started with a minimally configured initialization parameter file.

  The following are the mandatory background processes:

  (A)

  Process Monitor Process (PMON)

  (D)

  Recoverer Process (RECO)

  (E)

  Listener Registration Process (LREG)

  System Monitor Process (SMON)

  Database Writer Process (DBW)

  Log Writer Process (LGWR)

  Checkpoint Process (CKPT)

  Manageability Monitor Processes (MMON and MMNL)

  Note: The mandatory processes are not the same release 11.

  Reference: Oracle Database Concepts 12c, Mandatory Background Processes http://docs.oracle.com/ database/121/CNCPT/process.htm#CNCPT008

• Question 24:

  Examine the error:

  GSM-45054: GSM error or NET-40006: unable to start GSH What is the cause of this error, and what

  troubleshooting steps should you perform?

  A. The GDS catalog database of its listener may be down. Verify that the GDS catalog database and its local listener are running.

  B. The GDS pool database may be down. Make sure that the GDS pool databases are up and the service is enabled and started.

  C. The GDS pool database's local listener may be down. Make sure that it is running.

  D. The global service may be down. Verify that the pool databases are up and the service is started.

  E. The global service may be disabled. Make sure that the pool databases are up and the service is enabled and started.

  Correct Answer: E

• Question 25:

  Which two options can you use to list Oracle Database 12c patches by using Queryable Patch Inventory?

  A. select patch from vSoption where opatch_data=' 333333' ;

  B. select xmltransform(dbms_qopatch.get_opatch_data(3333333), dbms_qopatch.GET_OPATCH_XSLT ()) from dual

  C. select opatch from vSpatchsets where opatch_number = '333333';

  D. opatchlsinventory -xml

  Correct Answer: CD

  Reference:http://www.oracle.com/technetwork/pt/articles/sql/queryable-patch-inventory-12c- 2098576ptb.html

• Question 26:

  What new role in Oracle Database 12c encompasses the administrative privileges required for backup and recovery?

  A. SYSBKP

  B. SYSBKPRMAN

  C. SYSRMAN

  D. SYSBACKUP

  Correct Answer: D

  Reference: http://docs.oracle.com/cd/E16655_01/backup.121/e17631/release_changes.htm#RCMRF90521 (sys backup privilege)

• Question 27:

  Which statement is true about Enhanced Real-Time ADDM in Oracle Database 12c?

  A. The analyzed result is lost when the database instance is restarted.

  B. It runs every 1 hour automatically.

  C. It is shipped only with Oracle Enterprise Manager 12c.

  D. It analyzes high CPU trend and identifies problems before they can threaten application performance.

  Correct Answer: D

• Question 28:

  Which three statements are true about System Global Area (SGA)?

  A. SGA contains shared memory structures for all Oracle processes running on a single server.

  B. Each server process has its own SGA.

  C. SGA is allocated automatically when a database is started.

  D. SGA is used to speed up queries by caching data blocks, program data, information about database objects, and query results

  E. SGA is allocated automatically when a database is mounted.

  F. SGA is read and written by server and background processes.

  Correct Answer: CDF

• Question 29:

  Why does performance degrade when many UPDATE, INSERT, or DELETEstatements are issued on a table that has an associated bitmap index?

  A. The DML operations re-create the bitmap index blocks.

  B. The bitmap index is rebuilt automatically after a DML operation.

  C. The smallest amount of a bitmap that can be locked is a bitmap segment.

  D. Additional time is taken to remove NULL values from the bitmap index alter a DML operation.

  Correct Answer: C

  Bitmap indexes in currently "supported" versions of Oracle are maintained in a much more efficient manner than they were previously, to the point where the need for frequently rebuilds has been much reduced, even in tables in which such indexes are not dropped during heavy loads.

  That said, Bitmap indexes are still unsuitable in OLTP type environments (even in 11g or later) due to the locking implications associated with them.

  Note: bitmap indexes are only suitable for static tables and materialized views which are updated at nigh

  and rebuilt after batch row loading.

  Not B: Like b-tree indexes, bitmap indexes should be rebuilt (ALTER INDEX . . . REBUILD) if there is a lot

  of DML (UPDATE, INSERT, DELETE) activity.

  Not A: Since 10g, Oracle is significantly more efficient and where possible will simply adjust the current

  rowid range of the Bitmap index entry and modify the bitmap string accordingly to accommodate a new row

  value (resulting in an overall increase of just 2 bytes overall in this example) rather than create a totally

  new index entry (which required an additional 21 bytes in the 9i example).

• Question 30:

  Which type of information is included in dynamic performance views?

  1 - System and session parameters 2 - Memory usage and allocation 3 - File states (including RMAN backup files) 4 - Progress of jobs and tasks 5 - SQL execution 6 - Statistics and metrics

  A. 3, 4, and 5

  B. 1, 2, 5, and 6

  C. 1, 2, 3, and 4

  D. 2, 3, 5, and 6

  E. 2, 3, 4, 5, and 6

  F. 1, 2, 3, 4, 5, and 6

  Correct Answer: F

  Reference: http://docs.oracle.com/cd/E16655_01/server.121/e17633/datadict.htm#CNCPT1213 (overview of dynamic performance views)