Oracle OCP 9i 11g The Big Hard Ones

Card Set Information

Author:
Tralala
ID:
275898
Filename:
Oracle OCP 9i 11g The Big Hard Ones
Updated:
2014-06-05 02:55:40
Tags:
Oracle OCP 9i 11g Big Hard Ones
Folders:

Description:
123
Show Answers:

Home > Flashcards > Print Preview

The flashcards below were created by user Tralala on FreezingBlue Flashcards. What would you like to do?


  1. What new component is installed by default and optionally in 11g?
    • Default:
    • Application Express
    • Oracle Real Application Testing
    • Oracle SQL Developer
    • Oracle Warehouse Builder
    • Optionally:
    • Oracle Configuration Manager
    • Oracle Database Vault
  2. What is the default location of the Flash Recovery Area?
    $ORACLE_BASE/diag/flash_recovery_area
  3. What are the advantages of online patching?
    • No downtime is required
    • They persist across shutdowns
    • They allow rolling patches in RAC
    • They have a fast installation
  4. In Oracle 11g, which five things are checked with the ALTER DISKGROUP...CHECK statement?
    • Disk consistency
    • Consistency of file extent maps and allocation
    • Linking of the metadata directory and file directory
    • Linking of the alias directory tree
    • The existence of unreachable allocated blocks in ASM metadata directories
  5. When creating an ASM diskgroup, what is optionally specified?
    • Disks belonging to failure groups
    • The type of failure group
    • Disk group attributes
  6. What are ASM Disks?
    • A storage device assigned to Oracle ASM disk group
    • Physical Disk or Partition
    • Logical Unit Number from storage array
    • Logical volume
    • Network attached file
  7. What happens if you attempt to rename a readonly tablespace?
    • Datafile headers are not updates with the new name
    • A message is written to the alertlog
  8. Which views does lsdsk in ASM commandline use?
    V$ASM_DISK_STAT and V$ASM_DISK
  9. What are the advantages of ASM?
    • Simplifies operations such as Creating databases and managing diskspace
    • Distributes data across physical disks for uniform performance
    • Rebalances data automatically
  10. How does one disable disk rebalancing?
    Set ASM_POWER_LIMITS to 0
  11. What command allows disks to be added to a disk group?
    ALTER DISKGROUP data ADD DISK '/devices/disk6' NAME disk6;
  12. Which initialization parameter must be set to YES to implement strong authentication for administrators?
    The LDAP_DIRECTORY_SYSAUTH initialization parameter
  13. Which three methods can you use in Oracle 11g to implement strong authentication for administrators?
    By granting the SYSDBA or SYSOPER role in Oracle Internet Directory (OID), using certificates over SSL, or using Kerberos tickets
  14. In and ACL is when is RESOLVE automatically granted?
    When CONNECT is granted
  15. What does Oracle TDE stand for?
    Transparent Data Encryption
  16. Where is the Oracle Wallet location defined?
    ENCRYPTION_WALLET_LOCATION in sqlnet.ora
  17. How is a tablespace decrypted?
    It can't be.
  18. What are the options for SEC_PROTOCOL_ERROR_FURTHER_ACTION?
    • CONTINUE (Default)
    • DROP
    • DELAY, n (secs)
  19. Which Packages control the ACLs?
    DBMS_NETWORK_ACL_ADMIN and DBMS_NETWORK_ACL_UTILITY
  20. What does VALIDATE DATABASE command do?
    • Validates all datafiles and controlfiles
    • Checks for corrupt blocks
    • Checks for missing files
    • Checks SPFILE is being used
  21. What are the reported sections ADVISE FAILURE?
    • Mandatory Manual Actions
    • Optional Manual Actions
    • Automated Repair Actions
  22. Which view returns Active Problem Count and Active Incident Count?
    V$DIAG_INFO
  23. What are the two modes for running heath checks?
    • DB-online
    • DB-offline
  24. Which view contains a list of available health checks?
    V$HM_CHECK
  25. What are the possible values for STATUS in an incident?
    • COLLECTING
    • READY
    • TRACKING
  26. Which view list alls the ADR locations for current Oracle instance?
    V$DIAG_INFO
  27. What does ADRCI allow?
    • Viewing of diagnostic data in ADR
    • Viewing of Health Monitor reports
    • Packaging incident and problem information for Oracle Support.
  28. In support Workbench, what is a "Problem"?
    • A critical error
    • ORA-00600 ORA-07445 ORA-04031
  29. Which Health Monitor check is used to check for disk image block corruptions, such as checksum failures and intrablock corruption?
    Data Block Integrity Check
  30. When using Custom Packaging to package incidents, what additional information can you include in your incident package?
    Additional problems or incidents, trace files, external files, and other diagnostic information, such as SQL test cases
  31. What is checked if you run a DB Structure Integrity Check using Health Monitor and the database is in NOMOUNT mode?
    Only the control file
  32. When viewing a Health Monitor report, which output column indicates whether the health check was runmanually or in reaction to a critical error?
    The run_mode column
  33. What must be true for tablespaces used by a flashback data archive?
    They must use automatic segment space management.
  34. Which four types of database objects can Flashback Versions Query NOT be used to query?
    • Fixed tables
    • External tables
    • Temporary tables
    • Views
  35. Which two prerequisites must be met to use Flashback Data Archive?
    • Database must use automatic undo management.
    • Tablespaces used by flashback data archives must use automatic segment space management.
  36. In which view can the oldest SCN/Timestamp that can be flashed back to be found?
    V$FLASHBACK_DATABASE_LOG
  37. What is required to allow Flashback Transaction to function?
    • Database must be in ARCHIVELOG mode
    • Supplemental logging must be enabled
  38. Which view can be used to monitor the flashback space for the last 24 hours?
    • V$FLASHBACK_DATABASE_STAT
    • One row for each hour
  39. How is UNDO_SQL generated?
    • SELECT VERSIONS_XID, *
    • FROM table
    • VERSIONS BETWEEN SCN scn1 AND scn2
    • [WHERE condition];

    • SELECT OPERATION, UNDO_SQL, TABLE_NAME
    • FROM flashback_transaction_queryWHERE xid='n';
  40. Which RMAN command should you issue to indicate that all archived logs that have been backed up more than five times should be eligible for deletion?
    CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 5 TIMES TO devicetype;
  41. Where are warning about space in the Flash Recovery area written?
    DBA_OUTSTANDING_ALERTS
  42. How is VIRTUAL PRIVATE CATALOG granted?
    • SQL> GRANT recovery_catalog_owner TO vpc1
    • RMAN> CONNECT CATALOG rman/rman@rmandb
    • RMAN> GRANT CATALOG FOR DATABASE prod1 TO vpc1
    • RMAN> CONNECT CATALOG vpc1/password@rmandb
    • RMAN> CREATE VIRTUAL CATALOG
  43. In which views is IS_RECOVERY_DEST_FILES found?
    • V$CONTROLFILE
    • V$LOGFILE
    • V$ARCHIVED_LOG
    • V$DATAFILE_COPY
    • V$DATAFILE
    • V$BACKUP_PIECE
    • RMAN Tables
  44. What clauses in create external table can speed up reading?
    • PARALLEL
    • PROECT COLUMN REFERENCED
  45. What cannot be transported in transportable tablespaces?
    • SYSTEM tablespace or objects owned by the user SYS
    • Materialized Views
    • A table partition, the entire table must be transported
  46. What are the only ALTER TABLE allowed against external tables?
    • REJECT LIMIT
    • DEFAULT DIRECTORY
    • PROJECT COLUMN ALL
    • PROJECT COLUMN REFERENCED
  47. hat role do you require to attach and control Data Pump jobs of other users?
    EXP_FULL_DATABASE or IMP_FULL_DATABASE
  48. When cannot the direct method method be used to access tables in data pump?
    • Clustered tables
    • Presence of active triggers in the tables
    • Export of a single partition in a table with a global index
    • Presence of referential integrity constraints
    • Presence of domain indexes on LOB columns
    • Tables with fine-grained access control enabled in the insert mode
    • Tables with BFILE or opaque type columns
  49. Which utility can be used to check if a tablespace is ready for transportation?
    • DBMS_TTS.TRANSPORT_SET_CHECK
    • DBMS_TTS.TRANSPORT_SET_CHECK( TS_LIST=>'sales_1,sales_2', INCL_CONSTRAINTS =>TRUE,FULL_CHECK =>TRUE)
  50. How can the current progress of a data pump job be found?
    STATUS command in Data Pump
  51. What can you do with Export and Import Data Pump utilities?
    • export and import data faster than Old export/import utilities
    • estimate job times
    • perform fine-grained object selection
    • monitor jobs effectively
    • directly load one database from a remote instance
    • call the utilities from PL/SQL using Data Dump API
    • stop, resume and restart the utilities
    • attach a running job to monitor jobs, as well as to modify certain parameters interactively.
    • have fine-grained data import capability
    • remap objects of a specific schema to another schema
  52. hich view shows which platforms are supported for Cross Platform tablespace transport?
    V$TRANSPORTABLE_PLATFORM
  53. Which data pump parameter will suppress logfile generation?
    NOLOGFILE=Y
  54. What is not permitted for external tables that is for normal tables?
    • INDEXES
    • DML
    • ANALYZE
    • VIRTUAL COLUMNS
  55. To use DBMS_TTS, what role must you have been granted?
    EXECUTE_CATALOG_ROLE
  56. Which view shows what issues should be addressed before transporting a tablespace?
    TRANSPORT_SET_VIOLATIONS
  57. What segment types are NOT eligible for Online Segment Shrink?
    • IOT Mapped tables
    • Tables with rowid based materialised views
    • Tables with function-based indexes
    • SECUREFILE LOBs
    • Compressed tables
  58. What objects does Automatic Segment Advisor analyze?
    • Tablespaces that have passed warning thresholds
    • Segments with the most activity
    • Segments with the highest growth rate
    • Tables > 10MB that have >= 3 indexes
  59. What is interval partitioning?
    • An extension of range partitioning
    • Database creates a new partition of a specified interval when inserted data exceed the range partition.
  60. What are the segment requirement for Online Segment Shrink?
    • Segment is in a Locally managed tablespace
    • Automatic Segment Space Management is enabled
  61. Which status value must a task have to successfully create a directive for it using the INSERT_FINDING_DIRECTIVE procedure?
    The INITIAL status
  62. For what purpose would you use the INSERT_SEGMENT_DIRECTIVE procedure of the DBMS_ADDM package?
    To suppress ADDM reporting for a specific schema, segment, subsegment, or object
  63. What is the result of calling the CREATE_EXTENDED_STATS function of the DBMS_STATS package to create a column group?
    A virtual hidden column is created in the table
  64. When Automatic Memory Management is activated, which two SGA memory parameters are static and cannot be modified without shutting down the instance?
    • LOG_BUFFER
    • SGA_MAX_SIZE
  65. Who is ADDM disabled?
    Set CONTROL_MANAGMENT_PACK_ACCESS=NONE
  66. What are the DBMS_ADDM options?
    • DBMS_ADDM.ANALYZE_DB
    • DBMS_ADDM.ANALYZE_INST
    • DBMS_ADDM.ANALYZE_PARTIAL
  67. Which initilization parameters take their memory from the SGA_TARGET and are automayically sized?
    • SHARED_POOL_SIZE
    • LARGE_POOL_SIZE
    • JAVA_POOL_SIZE
    • DB_CACHE_SIZE
    • STREAMS_POOL_SIZE
  68. Desc Gathering Pending stats for a table and then publishing the pending stats
    • DBMS_STATS.delete_table_stats('USER','TAB');
    • DBMS_STATS.set_table_prefs('USER','TAB', 'PUBLISH', 'false');
    • ANALYZE TABLE scott.emp COMPUTE STATISTICS;
    • DBMS_STATS.publish_pending_stats('USER','TAB');
  69. If INCREMENTAL is set to TRUE and GRANULARITY to AUTO for DBMS_STATS, what happens when collecting data in a partitioned table?
    If data loaded in a new partition, stats are INCREMENTALLY gathered for that partition and update the global statistics.
  70. What controls ADDM?
    init parameters CONTROL_MANAGMENT_PACK_ACCESS and STATISTICS LEVEL
  71. What are the four classes of findings for ADDM?
    • Problem - root cause of a database problem
    • Symptom - symptoms of a problem
    • Information - background information
    • Warning - issues that may affect accuracy of ADDM analysis
  72. How are Expression Statistics gathered?
    • Using DBMS_STATS.CREATE_EXTENDED_STATS
    • SELECT DBMS_STATS.CREATE_EXTENDED_STATS (null, 'cust' '(lower(state))') from dual;
  73. What are the possible blanks here: INSERT_??_DIRECTIVE?
    • FINDING
    • PARAMETER
    • SEGMENT
    • SQL
  74. What are the tablespace characheristics of the bigfile tablespace?
    • Locally managed
    • ASSM
  75. What is the command to drop a datafile in ASM?
    ALTER DISKGROUP data DROP FILE '+/data/ocpdb/ocp_undo_ts';
  76. Define a tuning set?
    A database object that includes one or more SQL statements with execution stats and execution context.
  77. If OPTIMIZER_USE_PLAN_BASELINES=TRUE what happens if the Optimizer generates a plan, but no matching plan in the SQL plan baseline?
    • The plan is added to the plan history
    • Costs each SQL plan that's been accepted
    • Selects the plan with the lowest cost
  78. How are SQL plan baselines manually added?
    DBMS_SPM
  79. How are SQL plan baselines automatically added?
    OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE
  80. What is captured in Automatic Plan Capture?
    • SQL Text
    • Outline
    • Bind variables
    • Compilation Environment
  81. Which view shows the current settings for SMB?
    DBA_SQL_MANAGEMENT_CONFIG
  82. How are SQL Plan baselines displayed?
    DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE function
  83. What can SQL Plan execution be affected by?
    • Optimizer Version
    • Stats
    • Application of SQL profiles
    • Dropping of an index
    • System changes
  84. What parameter enables Automatic Plan Capture?
    OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE
  85. Which Oracle product can predict the impact of any change when using Database Replay?
    SQL Performance Analyzer
  86. What are the steps for capturing data in Database Replay?
    • CREATE DIRECTORY db_replay_capture_dir AS '/u01/db_replay_capture';
    • CREATE USER db_replay_test IDENTIFIED BY db_replay_test;
    • GRANT CONNECT, CREATE TABLE TO db_replay_test;
    • SHUTDOWN IMMEDIATE
    • STARTUP
    • BEGIN DBMS_WORKLOAD_CAPTURE.start_capture
    • Do some work!!!
    • DBMS_WORKLOAD_CAPTURE.finish_capture
  87. Which view contains information about Database Replay Capture?
    DBA_WORKLOAD_CAPTURES
  88. What work is not captured as part of Database Replay?
    • Flashback Queries
    • Direct Path Loads
    • External Stuff
  89. Give examples of what Database Replay can assist in advising.
    • Debugging
    • Upgrading the OS
    • Converting from a single instance to RAC
  90. What segment types are NOT eligible for Online Segment Shrink?
    • IOT Mapped tables
    • Tables with rowid based materialised views
    • Tables with function-based indexes
    • SECUREFILE LOBs
    • Compressed tables
  91. What does the DBMS_SPACE.SPACE_USAGE procedure return for LOBs?
    Information about LOB space usage for both SecureFiles and BasicFiles
  92. For which type LOBs does the DBMS_SPACE.SPACE_USAGE procedure return space usage for LOB chunks?
    Only for SecureFile LOBs
  93. Which procedure allows SecureFile LOB features to be set?
    DBMS_LOB.SETOPTIONS
  94. Which procedure is used to check to space usage on LOBS?
    DBMS_SPACE.SPACE_USAGE
  95. What are the advantages of Online Redefinition?
    Tables and Partitions do not have to be take offline.Operation can be done in parallel.
  96. How can one obtain the current settings of a SecureFile LOB?
    DBMS_LOB.GETOPTIONS
  97. What happens is one users the CACHE with NOLOGGING or FILESYSTEM_LIKE_LOGGING?
    An error is generated, CACHE must have logging
  98. When creating, altering LOBs for which type of LOB is CACHING an option?
    Both SecureFile and BasicFile
  99. What has to be done after a partitioned table is redefined?
    Global indexes must be rebuilt.
  100. What are the CACHING options when creating LOBS? What is the default
    • CACHE
    • CACHE READS
    • NOCACHE (default)
  101. For what purpose would you use the INVALIDATE procedure of the DBMS_RESULT_CACHE package?
    To make cached results that depend on specific objects invalid
  102. What SQL statements can be audited using Fine-grained auditing (FGA) policy?
    SELECT, INSERT, UPDATE, MERGE, and DELETE statements
  103. What information is captured by Fine-Grained Auditing (FGA) policy when it is used to audit Data Manipulation Language (DML) statements?
    The Structured Query Language (SQL) operations being performed and the SQL statements used in the audited operations
  104. If you add a column to a table, what is the effect on dependent objects?
    They remain valid.
  105. Which clause should you use in a CREATE FUNCTION statement to enable result-caching for the function?
    The RESULT_CACHE clause
  106. In an ALTER TABLESPACE statement, what is the difference between using the SHRINK SPACE clause and the SHRINK TEMPFILE clause?
    SHRINK SPACE shrinks the entire tablespace and SHRINK TEMPFILE shrinks only the specified file.
  107. What is required to capture SQL in fine grained audit?
    DBMS_FGA.ADD_POLICY to DB+EXTENDED or XML+EXTENDED
  108. In Fine Grained Auditing, can the values before and after an update be tracked?
    No
  109. Which DBMS_RESULT_CACHE element stops/starts existing Result Cache results being used and new ones being added?
    DBMS_RESULT_CACHE.BYPASS
  110. Which parameter allow DBA to change the memory allocated to the Result Cache?
    RESULT_CACHE_MAX_SIZE
  111. How is a memory report of the memory cache produced?
    DBMS_RESULT_CACHE.MEMORY_REPORT
  112. Which parameter specifies the time for which a result that access remote database objects remains valid? What is the default?RESULT_CACHE_REMOTE_EXPIRATION
    Default 0 - The result is NOT cached
  113. Which view lists memory blocks and corresponding stats?
    V$RESULT_CACHE MEMORY
  114. What objects and functions will NOT allow SQL results to be cached?
    • Dictionary and Temporary Tables
    • Sequence CURRVAL and NEXTVALSQL functions current_date, current_timestamp, local_timestamp, sys_guid, sysdate and sys_timestamp
    • Non-deterministic PL/SQL
  115. What is required to create a fine-grained auditing policy?
    Execute privilege on DBMS_FGA package
  116. How is a tablespace removed from a tablespace group?
    Specify a group name as '' (ie empty string)ALTER TABLESPACE tbsname TABLESPACE GROUP '';
  117. What are the advantages of FGA over standard auditing?
    • Performs a Boolean check, record only created is a condition is met
    • Can capture the SQL that triggered the audit (with bind variables)
    • Can audit columns with sensitive data
    • Can create event handler to send an email alert
  118. What are the three elements of DRM?
    • Resource Consumer Groups
    • Resource Plan
    • Resource Plan Directive
  119. What are the four setting of Baseline Metric Thresholds?
    • Threshold Type
    • Critical
    • Warning
    • Occurrences
  120. Which view show details from DBA_SCHEDULER_JOB_LOG?
    DBA_SCHEDULER_JOB_RUN_DETAILS
  121. What must Lightweight jobs be based on?
    A program object, PLSQL or STORED_PROCEDURE
  122. What tasks are performed by ABP process?
    • Creating jobs for each maintenance task
    • Storing execution history in SYSAUX
    • Prioritizing maintenance tasks
  123. What are the possible actions that the resource manager can take when a Resource Plan Directive is exceeded?
    • SWITCH_GROUP
    • KILL_SESSION
    • CANCEL_SQL
  124. What are the two types of adaptive threshold?
    • Percentage of maximum
    • Significance level

What would you like to do?

Home > Flashcards > Print Preview