Oracle Database 12c: Installation and Administration
Question No: 81
Which two statements are true about availability audit features after migration to unified auditing?
The ability of users to audit their own schema objects is not available in the post- migrated database.
Operating systemaudit trail is available in the post-migrated database.
Network auditing is available in the post-migrated database.
Mandatory auditing of audit administrative actions is available in the post-migrated database.
Answer: A,D Explanation:
Question No: 82
A database is stored in an Automatic Storage Management (ASM)disk group, disk group, DGROUP1 with SQL:
There is enough free space in the disk group for mirroring to be done.
What happens if the CONTROLLER1 failure group becomes unavailable due to error of for maintenance?
Transactions and queries accessingdatabase objects contained in any tablespace stored in DGROUP1 will fall.
Mirroring of allocation units will be done to ASM disks in the CONTROLLER2 failure group until the CONTROLLER1 for failure group is brought back online.
The data in the CONTROLLER1 failure group is copied to the controller2 failure group and rebalancing is initiated.
ASM does not mirror any data until the controller failure group is brought back online, and newly allocated primary allocation units (AU) are stored in the controller2 failure group, without mirroring.
Transactions accessing database objects contained in any tablespace stored in DGROUP1 will fail but queries will succeed.
Answer: B Explanation:
CREATE DISKGROUP NORMAL REDUNDANCY
For Oracle ASM to mirror files, specify the redundancy level as NORMAL REDUNDANCY (2-way mirroring by default for most file types) or HIGH REDUNDANCY (3-way mirroring for all files).
Question No: 83
Examine this command:
SQL gt; exec DBMS_STATS.SET_TABLE_PREFS (‘SH’, ‘CUSTOMERS’, ‘PUBLISH’,
Which three statements are true about the effect of this command?
Statistics collection is not done for the CUSTOMERS table when schema stats are gathered.
Statistics collection is not done for the CUSTOMERS table when database stats are gathered.
Any existing statistics for the CUSTOMERS table are still available to the optimizer at parse time.
Statistics gathered on the CUSTOMERS table when schema stats are gathered are stored as pending statistics.
Statistics gathered on the CUSTOMERS table when database stats are gathered are stored as pending statistics.
Answer: C,D,E Explanation:
This procedure is used to set the statistics preferences of the specified table in the specified schema.
Using Pending Statistics
Assume many modifications have been made to the employees table since the last time statistics were gathered. To ensure that the cost-based optimizer is still picking the best plan, statistics should be gathered once again; however, the user is concerned that new statistics will cause the optimizer to choose bad plans when the current ones are acceptable. The user can do the following:
EXEC DBMS_STATS.SET_TABLE_PREFS(#39;hr#39;, #39;employees#39;, #39;PUBLISH#39;, #39;false#39;);
By setting the employees tables publish preference to FALSE, any statistics gather from now on will not be automatically published. The newly gathered statistics will be marked as pending.
Question No: 84
Examine the parameter for your database instance:
You generated the execution plan for the following query in the plan table and noticed that the nested loop join was done. After actual execution of the query, you notice that the hash join was done in the execution plan:
Identify the reason why the optimizer chose different execution plans.
The optimizer used a dynamic plan for the query.
The optimizer chose different plans because automatic dynamic sampling was enabled.
The optimizer used re-optimization cardinality feedback for the query.
The optimizer chose different plan because extended statistics were created for the columns used.
OPTIMIZER_DYNAMIC_SAMPLING controls both when the database gathers dynamic statistics, and the size ofthe sample that the optimizer uses to gather the statistics.
Range of values0 to 11
Question No: 85
Your multitenant container database, CDB1, is running in ARCHIVELOG mode and has two pluggable databases, HR_PDB and ACCOUNTS_PDB. An RMAN backupexists for the database.
You issue the command to open ACCOUNTS_PDB and find that the USERDATA.DBF data file for the default permanent tablespace USERDATA belonging to ACCOUNTS_PDB is corrupted.
What should you do before executing the commands to restoreand recover the data file in ACCOUNTS_PDB?
Place CDB1 in the mount stage and then the USERDATA tablespace offline in ACCOUNTS_PDB.
Place CDB1 in the mount stage and issue the ALTER PLUGGABLE DATABASE accounts_pdb CLOSE IMMEDIATE command.
Issuethe ALTER PLUGGABLE DATABASE accounts_pdb RESTRICTED command.
Take the USERDATA tablespace offline in ACCOUNTS_PDB.
Answer: D Explanation:
You can take an online tablespace offline so that it is temporarily unavailable for general use. The rest of the database remains open and available for users to access data.
Conversely, you can bring an offline tablespace online to make the schema objects within the tablespace available to database users. The database must be open to alter the availability of a tablespace.
Question No: 86
The HR user executes the following query on the EMPLOYEES table but does not issue COMMIT, ROLLBACK, or any data definition language (DDL) command after that:
HR then opens a second session.
Which two operations wait when executed in HR’s second session?
LOCK TABLE employees IN EXCLUSIVE MODE;
INSERT INTO employees(empno,ename) VALUES (1289, ‘Dick’);
SELECT job FROM employees WHERE job=’CLERK’ FOR UPDATE OF empno;
SELECT empno,ename FROM employees WHERE job=’CLERK’;
INSERT INTO employees(empno,ename,job) VALUES (2001,’Harry’,’CLERK);
Question No: 87
You execute the commands:
SQLgt;CREATE USER sidney IDENTIFIED BY out_standing1 DEFAULT TABLESPACE users
QUOTA 10M ON users TEMPORARY TABLESPACE temp
SQLgt;GRANT CREATE SESSION TO Sidney;
Which two statements are true?
The create user command fails if any role with the name Sidney exists in the database.
The user sidney can connect to the database instance but cannot perform sort operations becauseno space quota is specified for the temp tablespace.
The user sidney is created but cannot connect to the database instance because no profile is default.
The user sidney can connect to the database instance but requires relevant privileges to createobjects in the users tablespace.
The user sidney is created and authenticated by the operating system.
Answer: A,D Explanation: References:
Question No: 88
You want to flash back a test database by five hours. You issue this command:
SQL gt; FLASHBACK DATABASE TO TIMESTAMP (SYSDATE – 5/24);
Which two statements are true about this flashback scenario?
The database must have multiplexed redo logsfor the flashback to succeed.
The database must be MOUNTED for the flashback to succeed.
The database must use block change tracking for the flashback to succeed.
The database must be opened in restricted mode for the flashback to succeed.
Thedatabase must be opened with the RESETLOGS option after the flashback is complete.
The database must be opened in read-only mode to check if the database has been flashed back to the correct SCN.
Question No: 89
You notice a performance change inyour production Oracle 12c database. You want to know which change caused this performance difference.
Which method or feature should you use?
Compare Period ADDM report
AWR Compare Period report
Active Session History (ASH) report
Taking anew snapshot and comparing it with a preserved snapshot
Answer: B Explanation:
The awrddrpt.sql report is the Automated Workload Repository Compare Period Report. The awrddrpt.sql script is located in the $ORACLE_HOME/rdbms/admin directory.
Not A: Compare Period ADDM
Use this report to perform a high-level comparison of one workload replay to its capture or to another replay of the same capture. Only workload replays that contain at least 5 minutes of database time can be compared using thisreport.
Question No: 90
Which two are true concerning a multitenant container database with three pluggable database?
All administration tasks must be done to a specific pluggable database.
The pluggable databases increase patching time.
The pluggable databases reduce administration effort.
The pluggable databases are patched together.
Pluggable databases are only used for database consolidation.
|Lowest Price Guarantee||Yes||No||No|
|Free VCE Simulator||Yes||No||No|