Upgrade to Oracle Database 12c
Question No: 1
Examine the following parameters for a database instance:
MEMORY_MAX_TARGET=0 MEMORY_TARGET=0 SGA_TARGET=0 PGA_AGGREGATE_TARGET=500m
Which three initialization parameters are not controlled by Automatic Shared Memory Management (ASMM)?
Explanation: Manually Sized SGA Components that Use SGA_TARGET Space SGA Component, Initialization Parameter
/ The log buffer LOG_BUFFER
/ The keep and recycle buffer caches DB_KEEP_CACHE_SIZE DB_RECYCLE_CACHE_SIZE
/ Nonstandard block size buffer caches DB_nK_CACHE_SIZE
In addition to setting SGA_TARGET to a nonzero value, you must set to zero all initialization parameters listed in the table below to enable full automatic tuning of the automatically sized SGA components.
Table, Automatically Sized SGA Components and Corresponding Parameters
Question No: 2
Which three are direct benefits of the multiprocess, multithreaded architecture of Oracle Database 12c when it is enabled?
Reduced logical I/O
Reduced virtual memory utilization
Improved parallel Execution performance
Improved Serial Execution performance
Reduced physical I/O
Reduced CPU utilization
Explanation: * Multiprocess and Multithreaded Oracle Database Systems
Multiprocess Oracle Database (also called multiuser Oracle Database) uses several processes to run different parts of the Oracle Database code and additional Oracle processes for the users-either one process for each connected user or one or more processes shared by multiple users. Most databases are multiuser because a primary advantage of a database is managing data needed by multiple users simultaneously.
Each process in a database instance performs a specific job. By dividing the work of the database and applications into several processes, multiple users and applications can connect to an instance simultaneously while the system gives good performance.
In previous releases, Oracle processes did not run as threads on UNIX and Linux systems. Starting in Oracle Database 12c, the multithreaded Oracle Database model enables Oracle processes to execute as operating system threads in separate address spaces.
Question No: 3
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 accessing database 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.
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: 4
You performed an incremental level 0 backup of a database: RMAN gt; BACKUP INCREMENTAL LEVEL 0 DATABASE;
To enable block change tracking after the incremental level 0 backup, you issued this
SQL gt; ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE
To perform an incremental level 1 cumulative backup, you issued this command: RMANgt; BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;
Which three statements are true?
Backup change tracking will sometimes reduce I/O performed during cumulative incremental backups.
The change tracking file must always be backed up when you perform a full database backup.
Block change tracking will always reduce I/O performed during cumulative incremental backups.
More than one database block may be read by an incremental backup for a change made to a single block.
The incremental level 1 backup that immediately follows the enabling of block change tracking will not read the change tracking file to discover changed blocks.
Explanation: A: In a cumulative level 1 backup, RMAN backs up all the blocks used since the most recent level 0 incremental backup.
E: Oracle Block Change Tracking
Once enabled; this new 10g feature records the modified since last backup and stores the log of it in a block change tracking file using the CTW (Change Tracking Writer) process. During backups RMAN uses the log file to identify the specific blocks that must be backed up. This improves RMAN#39;s performance as it does not have to scan whole datafiles to detect changed blocks. Logging of changed blocks is performed by the CTRW process which is also responsible for writing data to the block change tracking file.
* An incremental level 0 backup backs up all blocks that have ever been in use in this database.
Question No: 5
On your Oracle Database, you issue the following commands to create indexes:
SQL gt; CREATE INDEX oe.ord_customer_ix1 ON oe.orders (customer_id, sales_rep_id) INVISIBLE;
SQLgt; CREATE BITMAP INDEX oe.ord_customer_ix2 ON oe.orders (customer_id, sales_rep_id);
Which two statements are true?
Only the ORD_CUSTOMER_IX1 index created.
Both the indexes are updated when a row is inserted, updated, or deleted in the ORDERS table.
Both the indexes are created: however, only ORD_CUSTOMERS_IX1 is used by the optimizer for queries on the ORDERS table.
The ORD_CUSTOMER_IX1 index is not used by the optimizer even when the OPTIMIZER_USE_INVISIBLE_INDEXES parameters is set to true.
Both the indexes are created and used by the optimizer for queries on the ORDERS table.
Both the indexes are created: however, only ORD_CUSTOMERS_IX2 is used by the optimizer for queries on the ORDERS table.
Explanation: Not A: Both indexes are created fine.
B: The invisible index ORD_CUSTOMERS_IX1 and the bitmap index are both updated by DML operations on the Orders table.
F: Since ORD_CUSTOMERS_IX1 is invisible only ORD_CUSTOMERS_IX2 is used by the query optimizer.
Not C,Not D,Not E:
ord_customer_ix1 is an invisible index and is therefore not used by the optimizer.
VISIBLE | INVISIBLE Use this clause to specify whether the index is visible or invisible to the optimizer. An invisible index is maintained by DML operations, but it is not be used by the optimizer during queries unless you explicitly set the parameter OPTIMIZER_USE_INVISIBLE_INDEXES to TRUE at the session or system level.
Note: Specify BITMAP to indicate that index is to be created with a bitmap for each distinct key, rather than indexing each row separately. Bitmap indexes store the rowids associated
with a key value as a bitmap. Each bit in the bitmap corresponds to a possible rowid. If the bit is set, then it means that the row with the corresponding rowid contains the key value. The internal representation of bitmaps is best suited for applications with low levels of concurrent transactions, such as data warehousing.
Question No: 6
Which three statements are true about the working of system privileges in a multitenant control database (CDB) that has pluggable databases (PDBs)?
System privileges apply only to the PDB in which they are used.
Local users cannot use local system privileges on the schema of a common user.
The granter of system privileges must possess the set container privilege.
Common users connected to a PDB can exercise privileges across other PDBs.
System privileges with the with grant option container all clause must be granted to a common user before the common user can grant privileges to other users.
Explanation: A, Not D: In a CDB, PUBLIC is a common role. In a PDB, privileges granted locally to PUBLIC enable all local and common users to exercise these privileges in this PDB only.
C: A user can only perform common operations on a common role, for example, granting privileges commonly to the role, when the following criteria are met:
The user is a common user whose current container is root.
The user has the SET CONTAINER privilege granted commonly, which means that the privilege applies in all containers.
The user has privilege controlling the ability to perform the specified operation, and this privilege has been granted commonly
* Every privilege and role granted to Oracle-supplied users and roles is granted commonly except for system privileges granted to PUBLIC, which are granted locally.
Question No: 7
Your multitenant container database (CDB) contains three pluggable database (PDBs). You find that the control file is damaged. You plan to use RMAN to recover the control file. There are no startup triggers associated with the PDBs.
Which three steps should you perform to recover the control file and make the database fully operational?
Mount the container database (CDB) and restore the control file from the control file auto backup.
Recover and open the CDB in NORMAL mode.
Mount the CDB and then recover and open the database, with the RESETLOGS option.
Open all the pluggable databases.
Recover each pluggable database.
Start the database instance in the nomount stage and restore the control file from control file auto backup.
Answer: C,D,F Explanation: Step 1: F Step 2: D
Step 3: C: If all copies of the current control file are lost or damaged, then you must restore and mount a backup control file. You must then run the RECOVERcommand, even if no data files have been restored, and open the database with the RESETLOGS option.
* RMAN and Oracle Enterprise Manager Cloud Control (Cloud Control) provide full support for backup and recovery in a multitenant environment. You can back up and recover a whole multitenant container database (CDB), root only, or one or more pluggable databases (PDBs).
Question No: 8
What are two benefits of installing Grid Infrastructure software for a stand-alone server before installing and creating an Oracle database?
Effectively implements role separation
Enables you to take advantage of Oracle Managed Files.
Automatically registers the database with Oracle Restart.
Helps you to easily upgrade the database from a prior release.
Enables the Installation of Grid Infrastructure files on block or raw devices.
Explanation: “Oracle Grid Infrastructure for a standalone server includes Oracle Restart and Oracle Automatic Storage Management. Oracle combined the two infrastructure products into a single set of binaries that is installed into an Oracle Restart home.”
Question No: 9
You configure your database Instance to support shared server connections.
Which two memory areas that are part of PGA are stored in SGA instead, for shared server connection?
User session data
Private SQL area
Location of the runtime area for DML and DDL Statements
Location of a part of the runtime area for SELECT statements
Explanation: A: PGA itself is subdivided. The UGA (User Global Area) contains session state information, including stuff like package-level variables, cursor state, etc. Note that, with shared server, the UGA is in the SGA. It has to be, because shared server means that the session state needs to be accessible to all server processes, as any one of them could be assigned a particular session. However, with dedicated server (which likely what you#39;re using), the UGA is allocated in the PGA.
C: The Location of a private SQL area depends on the type of connection established for a session. If a session is connected through a dedicated server, private SQL areas are located in the server process’ PGA. However, if a session is connected through a shared
server, part of the private SQL area is kept in the SGA.
System global area (SGA)
The SGA is a group of shared memory structures, known as SGA components, that contain data and control information for one Oracle Database instance. The SGA is shared by all server and background processes. Examples of data stored in the SGA include cached data blocks and shared SQL areas.
Program global area (PGA)
A PGA is a memory region that contains data and control information for a server process. It is nonshared memory created by Oracle Database when a server process is started.
Access to the PGA is exclusive to the server process. There is one PGA for each server process. Background processes also allocate their own PGAs. The total memory used by all individual PGAs is known as the total instance PGA memory, and the collection of individual PGAs is referred to as the total instance PGA, or just instance PGA. You use database initialization parameters to set the size of the instance PGA, not individual PGAs.
Reference: Oracle Database Concepts 12c
Question No: 10
Examine the following command:
CREATE TABLE (prod_id number(4), Prod_name varchar2 (20), Category_id number(30),
Quantity_on_hand number (3) INVISIBLE);
Which three statements are true about using an invisible column in the PRODUCTS table?
The %ROWTYPE attribute declarations in PL/SQL to access a row will not display the invisible column in the output.
The DESCRIBE commands in SQL *Plus will not display the invisible column in the
Referential integrity constraint cannot be set on the invisible column.
The invisible column cannot be made visible and can only be marked as unused.
A primary key constraint can be added on the invisible column.
Explanation: AB: You can make individual table columns invisible. Any generic access of a table does not show the invisible columns in the table. For example, the following operations do not display invisible columns in the output:
SELECT * FROM statements in SQL
DESCRIBE commands in SQL*Plus
%ROWTYPE attribute declarations in PL/SQL
Describes in Oracle Call Interface (OCI)
Not D: You can make invisible columns visible.
You can make a column invisible during table creation or when you add a column to a table, and you can later alter the table to make the same column visible.
Reference: Understand Invisible Columns
|Lowest Price Guarantee||Yes||No||No|
|Free VCE Simulator||Yes||No||No|