Q1. Examine the parameters for your database instance:
NAMETYPEVALUE
optimizer_adaptive_reporting_onlybooleanFALSE
optimizer_capture_sql_plan_baselinesbooleanFALSE
optimizer_dynamic_samplinginteger2
optimizer_features_enablestring12.1.0.1
Which three statements are true about the process of automatic optimization by using statistics feedback?
A. The optimizer automatically changes a plan during subsequent execution of a SQL statement if there is a huge difference in optimizer estimates and execution statistics.
B. The optimizer can re optimize a query only once using cardinality feedback.
C. The optimizer enables monitoring for cardinality feedback after the first execution of a query.
D. The optimizer doesnot monitor cardinality feedback if dynamic sampling and multicolumn statistics are enabled.
E. After the optimizer identifies a query as a re-optimization candidate, statistics collected by the collectors are submitted to the optimizer.
Answer: A,C,D
Explanation: C: During the first execution of a SQL statement, an execution plan is generated as usual.
D: if multi-column statistics are not present for the relevant combination of columns, the
optimizer can fall back on cardinality feedback.
(not B)* Cardinality feedback. This feature, enabled by default in 11.2, is intended to
improve plans for repeated executions.
optimizer_dynamic_sampling optimizer_features_enable
Dynamic sampling or multi-column statistics allow the optimizer to more accurately estimate selectivity of conjunctive predicates.
Note:
* OPTIMIZER_DYNAMIC_SAMPLING controls the level of dynamic sampling performed by the optimizer. Range of values. 0 to 10
Cardinality feedback was introduced in Oracle Database 11gR2. The purpose of this feature is toautomatically improve plans for queries that are executed repeatedly, for which the optimizer does not estimate cardinalities in the plan properly. The optimizer may misestimate cardinalities for a variety of reasons, such as missing or inaccurate statistics, or complex predicates. Whatever the reason for the misestimate, cardinality feedback may be able to help.
Q2. Which three conditions must be met before you create a Virtual Private Catalog (VPC)?
A. A base recovery catalog should exist.
B. The owner of VPC cannot own recovery catalog.
C. At least one target database should beregistered in the recovery catalog.
D. The register database privilege should be granted to the virtual catalog owner.
E. The recovery_catalog_owner role should be granted to the virtual catalog owner.
Answer: C,D,E
Reference:http://docs.oracle.com/cd/B28359_01/backup.111/b28273/rcmsynta013.htm
Q3. Which three conditions must be true for unused block compression to be used automatically while performing backups by using RMAN?
A. The compatible initialization parameter is set to 10.2 or higher.
B. There are no guaranteed restore points defined for the database.
C. The default device for the backup must be set to disk.
D. The tablespaces are locally managed.
E. The fast recovery area is less than 50 percentfree.
Answer: A,B,D
Reference:http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmcncpt.htm#BRAD V89481(See unused block compression)
https://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmcncpt.htm#BRADV89481
Q4. You must unload data from the orders, order_items, and products database tables to four filesusing the External Tables.
CREATE TABLE orders_ext
(order_id, order_date, product_id, product_name,quantity)
ORGANIZATION EXTERNAL ( TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY ext.dir LOCATION (‘ordersl.dmp','orders2.dmp’,’orders3.dmp’,’lorders4.dmp') ) PARALLEL AS SELECT o.order_id,o.order_date,p.product_id,p.product_name,i.quantity FROM orders o,productsp,order_itemsi WHERE o.orderjd = i.order_id and i.product_id = p.product_id;
You execute the command shown in the Exhibit, but only two files are created. Which parameter must be changed so that four files are created?
A. TYPE
B. LOCATION
C. PARALLEL
D. DEFAULT DIRECTORY
E. ORGANIZATION EXTERNAL
Answer: C
Q5. Which three statements are true about the database instance startup after an instance failure?
A. The RECO process recovers the uncommitted transactions at the next instance startup.
B. Online redo log files and archived redo log files are required to complete the rollback stage of instance recovery.
C. Uncommitted changes are rolled back to ensure transactional consistency.
D. The SMON process automatically performs the database recovery.
E. Media recovery is required to complete the database recovery.
F. Changes committed before the failure, which were not written to the data files, are re-applied.
Answer: A,C,D
Q6. You are administering a database that supports a data warehousing workload and is running in noarchivelog mode. You use RMAN to perform a level 0 backup on Sundays and level 1 incremental backups on allthe other days of the week.
One of the data files is corrupted and the current online redo log file is lost because of a media failure.
Which action must you take for recovery?
A. Restore the data file, recover it by using the recover datafilenoredo command, and use the resetlogs option to open the database.
B. Restore the control file and all the data files, recover them by using the recover database noredo command, and use the resetlogs option to open the database.
C. Restore all the data files, recoverthem by using the recover database command, and open the database.
D. Restore all the data files, recover them by using the recover database noredo command, and use the resetlogs option to open the database.
Answer: B
Q7. You use RMAN with a recovery catalog to back up your database. The backups and the archived redo log files are backed up to media daily. Because of a media failure, the entire database along with the recovery catalog database is lost.
Examine the steps required to recover the database:
1.Restore an autobackup of the server parameter file. 2.Restore the control file.
3. Start up the database instance in nomount state.
4.Mount the database.
5.Restore the data files.
6.Open the database with the resetlogs option.
7.Recover the data files.
8.Set D3ID for the database.
Identify the required steps in the correct order.
A. 1, 8, 3, 2, 4, 5, 7, 6
B. 8, 1, 3, 2, 4, 5, 7, 6
C. 1, 3, 2, 4, 8, 5, 6, 7
D. 8, 3, 2, 4, 5, 7, 6
E. 8, 1, 3, 2, 4, 5, 6
Answer: D
Q8. You want to consolidate backup information and centrally manage backup and recovery scripts for multiple databases running in your organization.
Which two backup solutions can be used?
A. RMAN recovery catalog
B. RMAN Media Management Library
C. Enterprise Manager Cloud Control
D. Enterprise Manager Database Express
E. Oracle Secure Backup
Answer: A,C
Q9. Because of logical corruption of data in a table, you want to recover the tablefrom an RMAN backup to a specified point in time.
Examine the steps to recover this table from an RMAN backup:
1.Determine which backup contains the table that needs to be recovered.
2.Issue the recover table RMAN command with an auxiliary destination defined and the point in time specified.
3.Import the Data Pump export dump file into the auxiliary instance.
4.Create a Data Pump export dump file that contains the recovered table on a target database.
Identify the required steps in the correct order.
A. 1, 4, 3
B. 1, 2
C. 1, 4, 3, 2
D. 1, 2, 4
Answer: B
Explanation: https://docs.oracle.com/database/121/BRADV/rcmresind.htm#BRADV689
Q10. You have set the value of the NLS_TIMESTAMP_TZ_FORMAT parameterto YYYY-MM-DD. The default format of which two data types would be affected by this setting?
A. DATE
B. TIMESTAMP
C. INTERVAL YEAR TO MONTH
D. INTERVAL DAY TO SECOND
E. TIMESTAMP WITH LOCAL TIME ZONE
Answer: B,E