Oracle Database 12c: Installation and Administration
Question No: 71
Your multitenant container database (CDB) contains some pluggable databases (PDBs), you execute the followingcommand in the root container:
Which two statements are true?
Schema objects owned by the C# # A_ADMIN common user can be shared across all PDBs.
The C # # A_ADMIN user will be able to use the TEMP_TS temporary tablespace only in root.
The command will, create a common user whose description is contained in the root and each PDB.
The schema for the common user C # # A_ADMIN can be different in each container.
The command will create a user in the root container only because the containerclause is not used.
Question No: 72
Which two statements are true concerning the Resource Manager plans for individual pluggable databases (PDB plans) in a multitenantcontainer database (CDB)?
If no PDB plan is enabled for a pluggable database, then all sessions for that PDB are treated to an equal degree of the resource share of that PDB.
In a PDB plan, subplans may be used with up to eight consumer groups.
If a PDB plan is enabled for a pluggable database, then resources are allocated to consumer groups across all PDBs in the CDB.
If no PDB plan is enabled for a pluggable database, then the PDB share in the CDB plan is dynamically calculated.
If a PDB plan is enabled for a pluggable database, then resources are allocated to consumer groups based on the shares provided to the PDB in the CDB plan and the shares provided to the consumer groups in the PDB plan.
Answer: A,E Explanation:
A: Setting a PDB resource plan is optional. If not specified, all sessions within the PDB are treated equally.
In a non-CDB database, workloads within a database are managed with resource plans. In a PDB, workloads are also managed with resource plans, also called PDB resource plans.
The functionality is similar except for the following differences:
/ Non-CDB Database Multi-level resource plans
Up to 32 consumer groups Subplans
/ PDB Database
Single-level resource plans only Up to 8 consumer groups
(not B) No subplans
Question No: 73
The DEFERRED_SEGMENT_CREATION parameter is set to TRUE in your database instance.
You execute the following command to create a table:
Which two statements are true?
The table is created without a segment because the storage clause is missing.
A segment is allocated when the first row is inserted in the table.
A segment is allocated when an index is created for any column in the table.
The table is created and extents are immediately allocated as per the default storage defined for its tablespace.
A segment is allocated for the table if the ALTER TABLE… ALLOCATE EXTENT command is issued.
Question No: 74
Your database supports a DSS workload that involves the execution ofcomplex queries: Currently, the library cache contains the ideal workload for analysis. You want to analyze some of the queries for an application that are cached in the library cache.
What must you do to receive recommendations about the efficient use of indexes and materialized views to improve query performance?
Create a SQL Tuning Set (STS) that contains the queries cached in the library cache and run the SQL Tuning Advisor (STA) on the workload captured in the STS.
Run the Automatic Workload Repository Monitor (ADDM).
Create an STS that contains the queries cached in the library cache and run the SQL Performance Analyzer (SPA) on the workload captured in the STS.
Create an STS that contains the queries cached in the library cache and runthe SQL Access Advisor on the workload captured in the STS.
Answer: D Explanation:
SQL Access Advisor is primarily responsible for making schema modification recommendations, such as adding or dropping indexes and materialized views. SQL Tuning Advisormakes other types of recommendations, such as creating SQL profiles and restructuring SQL statements.
The query optimizer can also help you tune SQL statements. By using SQL Tuning Advisor and SQL Access Advisor, you can invoke the query optimizer in advisory mode to examine a SQL statement or set of statements and determine how to improve their efficiency. SQL Tuning Advisor and SQL Access Advisor can make various recommendations, such as creating SQL profiles, restructuring SQL statements, creating additional indexes or materialized views, and refreshing optimizer statistics.
Decision support system (DSS) workload
The library cache is a shared pool memory structure that stores executable SQL and PL/SQL code. This cache contains the shared SQL and PL/SQL areas and control structures such as locks and library cache handles.
Question No: 75
Your multitenant container (CDB) containing three pluggable databases (PDBs) is running in ARCHIVELOG mode. You find that the SYSAUX tablespace is corrupted in the root container.
The steps torecover the tablespace are as follows:
Mount the CDB.
Close all the PDBs.
Open the database.
Apply the archive redo logs.
Restore the data file.
Take the SYSAUX tablespace offline.
Place the SYSAUX tablespace online.
Open all the PDBs with RESETLOGS.
Open the database with RESETLOGS.
Execute the command SHUTDOWN ABORT.
Which option identifies the correct sequence to recover the SYSAUX tablespace?
A. 6, 5, 4, 7
B. 10, 1, 2, 5, 8
C. 10, 1, 2, 5, 4, 9, 8
D. 10, 1, 5, 8, 10
Answer: A Explanation:
RMANgt;ALTER TABLESPACE sysaux OFFLINE IMMEDIATE; RMANgt; RESTORE TABLESPACE sysaux;
RMANgt; RECOVER TABLESPACE sysaux; RMANgt; ALTER TABLESPACE sysaux ONLINE;
While evaluating the 12c beta3 I was not able to do the recover while testing “all pdb fileslost”.
Cannot close the pdb as the system datafile was missing… So only option to recover was:
Shutdown cdb (10)
startup mount; (1)
restore pluggable database recover pluggable database alter database open;
alter pluggable database name open;
Oracle support says: You should be able to close the pdb and restore/recover the system tablespace of PDB.
* Inconsistent backups are usually created by taking online database backups. You can also make an inconsistent backup by backing up data files while a databaseis closed, either:
/ Immediately after the crash of an Oracle instance (or, in an Oracle RAC configuration, all instances)
/ After shutting down the database using SHUTDOWN ABORT
Inconsistent backups are only useful if the database is in ARCHIVELOG modeand all archived redo logs created since the backup are available.
* Open the database with the RESETLOGS option after finishing recovery: SQLgt; ALTER DATABASE OPEN RESETLOGS;
Question No: 76
Examine the parameter settings in your local ORCL database:
Examine the parameter settings for the remote SALES database:
As the SYS user, you issue the following command on ORCL:
CREATE PUBLIC DATABASE LINK sales.us.example.com USING ‘sales1’; The sales1 tnsnames entry points to the salesdatabase instance.
Which two are requirements to ensure that you can successfully connect by using this database link?
The GLOBAL_NAMES initialization parameter should be set to FALSE in the ORCL database.
The SALES1 net service name should beknown to both the local and remote databases.
Local database users who use this database link should have accounts and the required privileges in the remote database.
The REMOTE_OS_AUTHENT initialization parameter should be set to FALSE in the remotedatabase.
Only the user creating the database link must have an account and the required privileges in the remote database.
Question No: 77
Which three statements are true about adaptive SQL plan management?
It automatically performs verification or evolves non-accepted plans, inCOMPREHENSIVE mode when they perform better than existing accepted plans.
The optimizer always uses the fixed plan, if the fixed plan exists in the plan baseline.
It adds new, bettor plans automatically as fixed plans to the baseline.
Thenon-accepted plans are automatically accepted and become usable by the optimizer if they perform better than the existing accepted plans.
The non-accepted plans in a SQL plan baseline are automatically evolved, in COMPREHENSIVE mode, during the nightlymaintenance window and a persistent verification report is generated.
Answer: A,D,E Explanation:
With adaptive SQL plan management, DBAs no longer have to manually run the verification or evolve process for non-accepted plans. When automatic SQL tuning is in COMPREHENSIVE mode, it runs a verification or evolve process for all SQL statements that have non-accepted plans during the nightly maintenance window. If the non-accepted plan performs better than the existing accepted plan (or plans) in the SQL planbaseline, then the plan is automatically accepted and becomes usable by the optimizer. After the verification is complete, a persistent report is generated detailing how the non-accepted plan performs compared to the accepted plan performance. Because theevolve process is now an AUTOTASK, DBAs can also schedule their own evolve job at end time.
The optimizer is able to adapt plans on the fly by predetermining multiple subplans for portions of the plan.
Adaptive plans, introduced in Oracle Database 12c, enable the optimizer to defer the final plan decision for a statement until execution time. The optimizer instruments its chosen plan (the default plan) with statistics collectors so that it can detect at runtime, if its cardinality estimates differ greatly from the actual number of rows seen by the operations in the plan. If there is a significant difference, then the plan or a portion of it will be automatically adapted to avoid suboptimal performance on the first execution of a SQL statement.
Question No: 78
Examine the query and its output executed In an RDBMS Instance:
Which three statements are true about the users (other than sys) in the output?
The C # # B_ADMIN user can perform all backup and recovery operations using RMAN only.
The C # #C_ADMIN user can perform the data guard operation with Data Guard Broker.
The C # # A_ADMIN user can perform wallet operations.
The C # # D_ADMIN user can perform backup and recovery operations for Automatic Storage Management (ASM).
The C # # B_ADMIN user can perform all backup and recovery operations using RMAN or SQL* Plus.
Answer: B,D,E Explanation:
B: SYSDG administrative privilege has ability to perform Data Guard operations (including startup and shutdown) using Data Guard Broker ordgmgrl.
The new (introduced in 11g) SYSASM role to manage the ASM instance, variable extent sizes to reduce shared pool usage, and the ability of an instance to read from a specific disk of a diskgroup
E (Not A): SYSDBA is like a role in the sense that it is granted, but SYSDBA is a special built-in privilege to allow the DBA full control over the database
Not C: SYSKM. SYSKM administrative privilege has ability to perform transparent data encryption wallet operations.
Use the V$PWFILE_USERS view to see the users who have been granted administrative privileges.
Question No: 79
Which two statements are true about the RMAN validate database command?
It checks the database for intrablock corruptions.
It can detect corrupt pfiles.
It can detect corrupt spfiles.
It checks the database for interblock corruptions.
It can detect corrupt block change tracking files.
Answer: A,C Explanation:
Block corruptions can be divided IntoInterblock corruption and intrablock corruption. In intrablock corruption. th芦 corruption occurs within the block itself and can be either physical or logical corruption. In interblock corruption, the corruption occurs between blocks and can only be logical corruption.
(key word) * The VALIDATE command checks for intrablock corruptions only. Only DBVERIFY and the ANALYZE statement detect Interblock corruption.
VALIDATE Command Output 鈥⑩€?gt; List of Control File and SPFILE. File TYPE gt;禄禄 SPFILE or Control File.
Status gt;禄禄 OK if no corruption, or FAILED If block corruption is found.
Blocks Failing 禄禄禄 The number of blocks that fail the corruption check. These blocks are newly corrupt.
Blocks Examined 禄禄禄 Total number of blocks in the file. Oracle#39; Database Backup and Recovery User#39;s Guide
12c Release 1 (12.1) – 16 Validating Database Files and Backups
Question No: 80
Which three statements are true about a job chain?
It can contain a nested chain of jobs.
It can be used to implement dependency-based scheduling.
It cannot invoke the same program or nested chain in multiple steps in the chain.
It cannot have more than one dependency.
It can be executed using event-based or time-based schedules.
Answer: A,B,E Explanation: References:
EnsurePass ExamCollection Testking Lowest Price Guarantee Yes No No Up-to-Dated Yes No No Real Questions Yes No No Explanation Yes No No PDF VCE Yes No No Free VCE Simulator Yes No No Instant Download Yes No No