callicon +1 210-504-8191
whatsAppIcon +1 210-504-8191
emailIcon info@koenig-solutions.com
koenig-logo
logo-30-years
Login
koenig-logo
+1 210-504-8191
+1 210-504-8191

Oracle Database 12c : Administration Workshop Quiz Questions and Answers

Answer :
  • 2, 3, 4, 1

Explanation :

Step 1 (2). Seed column usage - Oracle must observe a representative workload, in order to determine the appropriate column groups. Using the new procedure DBMS_STATS.SEED_COL_USAGE, you tell Oracle how long it should observe the workload. Step 2: (3) You don't need to execute all of the queries in your work during this window. You can simply run explain plan for some of your longer running queries to ensure column group information is recorded for these queries. Step 3. (1) Create the column groups At this point you can get Oracle to automatically create the column groups for each of the tables based on the usage information captured during the monitoring window. You simply have to call the DBMS_STATS.CREATE_EXTENDED_STATS function for each table.This function requires just two arguments, the schema name and the table name. From then on, statistics will be maintained for each column group whenever statistics are gathered on the table. Note: * DBMS_STATS.REPORT_COL_USAGE reports column usage information and records all the SQL operations the database has processed for a given object. * The Oracle SQL optimizer has always been ignorant of the implied relationships between data columns within the same table. While the optimizer has traditionally analyzed the distribution of values within a column, he does not collect value-based relationships between columns. * Creating extended statisticsHere are the steps to create extended statistics for related table columns withdbms_stats.created_extended_stats: 1 - The first step is to create column histograms for the related columns.2 Next, we run dbms_stats.create_extended_stats to relate the columns together. Unlike a traditional procedure that is invoked via an execute ( "exec ") statement, Oracle extended statistics are created via a select statement.
Answer :
  • Multiple pluggable databases (PDBs) share certain multitenant container database (CDB) resources.
  • Patches are always applied at the CDB level.

Explanation :

Using 12c Resource manager you will be able control CPU, Exadata I/O, sessions and parallel servers. A new 12c CDB Resource Manager Plan will use so- called "Shares " (resource allocations) to specify how CPU is distributed between PDBs. A CDB Resource Manager Plan also can use "utilization limits " to limit the CPU usage for a PDB. With a default directive, you do not need to modify the resource plan for each PDB plug and unplug. E: New paradigms for rapid patching and upgrades. The investment of time and effort to patch one multitenant container database results in patching all of its many pluggable databases. To patch a single pluggable database, you simply unplug/plug to a multitenant container database at a different Oracle Database software version. Incorrect: * The Oracle RAC documentation describes special considerations for a CDB in an Oracle RAC environment. * Oracle Multitenant is a new option for Oracle Database 12c Enterprise Edition that helps customers reduce IT costs by simplifying consolidation, provisioning, upgrades, and more. It is supported by a new architecture that allows a container database to hold many pluggable databases. And it fully complements other options, including Oracle Real Application Clusters and Oracle Active Data Guard. An existing database can be simply adopted, with no change, as a pluggable database; and no changes are needed in the other tiers of the application. You can unplug a PDB from one CDB and plug it into a different CDB without altering your schemas or applications. A PDB can be plugged into only one CDB at a time. * UNDO tablespace can NOT be local and stays on the CDB level. * Redo and undo go hand in hand, and so the CDB as a whole has a single undo tablespace per RAC instance.
Answer :
  • It automatically performs verification or evolves non-accepted plans, in COMPREHENSIVE mode when they perform better than existing accepted plans.
  • The non-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 nightly maintenance window and a

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 plan baseline, 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 the evolve process is now an AUTOTASK, DBAs can also schedule their own evolve job at end time. Note: * 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.
Answer :
  • Use the CREATE PLUGGABLE DATABASE statement to create a PDB using the files from the SEED.
  • Use the DBMS_PDB package to clone an existing PDB
  • Use the DBMS_PDB package to plug an Oracle 12c non-CDB database into an existing CDB.

Explanation :

Use the CREATE PLUGGABLE DATABASE statement to create a pluggable database (PDB). This statement enables you to perform the following tasks: * (A) Create a PDB by using the seed as a template Use the create_pdb_from_seed clause to create a PDB by using the seed in the multitenant container database (CDB) as a template. The files associated with the seed are copied to a new location and the copied files are then associated with the new PDB. * (C) Create a PDB by cloning an existing PDB Use the create_pdb_clone clause to create a PDB by copying an existing PDB (the source PDB) and then plugging the copy into the CDB. The files associated with the source PDB are copied to a new location and the copied files are associated with the new PDB. This operation is called cloning a PDB. The source PDB can be plugged in or unplugged. If plugged in, then the source PDB can be in the same CDB or in a remote CDB. If the source PDB is in a remote CDB, then a database link is used to connect to the remote CDB and copy the files. * Create a PDB by plugging an unplugged PDB or a non-CDB into a CDB Use the create_pdb_from_xml clause to plug an unplugged PDB or a non-CDB into a CDB, using an XML metadata file.
Answer :
  • It detects any configuration change that caused a performance difference in both time periods.
  • It identifies any workload change that caused a performance difference in both time periods.
  • It shows the resource usage for CPU, memory, and I/O in both time periods.

Explanation :

Full ADDM analysis across two AWR snapshot periods Detects causes, measure effects, then correlates them Causes: workload changes, configuration changes Effects: regressed SQL, reach resource limits (CPU, I/O, memory, interconnect) Makes actionable recommendations along with quantified impact * Identify what changed / Configuration changes, workload changes * Performance degradation of the database occurs when your database was performing optimally in the past, such as 6 months ago, but has gradually degraded to a point where it becomes noticeable to the users. The Automatic Workload Repository (AWR) Compare Periods report enables you to compare database performance between two periods of time. While an AWR report shows AWR data between two snapshots (or two points in time), the AWR Compare Periods report shows the difference (ABE) between two periods (or two AWR reports with a total of four snapshots). Using the AWR Compare Periods report helps you to identify detailed performance attributes and configuration settings that differ between two time periods. References:
Answer :
  • It fails because an increase in DB_8K_CACHE_SIZE cannot be accommodated within SGA_MAX_SIZE.

Explanation :

* The SGA_TARGET parameter can be dynamically increased up to the value specified for the SGA_MAX_SIZE parameter, and it can also be reduced. * Example: For example, suppose you have an environment with the following configuration: SGA_MAX_SIZE = 1024M - SGA_TARGET = 512M - DB_8K_CACHE_SIZE = 128M - In this example, the value of SGA_TARGET can be resized up to 1024M and can also be reduced until one or more of the automatically sized components reaches its minimum size. The exact value depends on environmental factors such as the number of CPUs on the system. However, the value of DB_8K_CACHE_SIZE remains fixed at all times at 128M * DB_8K_CACHE_SIZE Size of cache for 8K buffers - * For example, consider this configuration: SGA_TARGET = 512M - DB_8K_CACHE_SIZE = 128M - In this example, increasing DB_8K_CACHE_SIZE by 16 M to 144M means that the 16M is taken away from the automatically sized components. Likewise, reducing DB_8K_CACHE_SIZE by 16M to 112M means that the 16M is given to the automatically sized components.
Answer :
  • Create an STS that contains the queries cached in the library cache and run the SQL Access Advisor on the workload captured in the STS.

Explanation :

* SQL Access Advisor is primarily responsible for making schema modification recommendations, such as adding or dropping indexes and materialized views. SQL Tuning Advisor makes 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. Note: * 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. References:
Answer :
  • Online Table Redefinition.

Explanation :

Oracle Database provides a mechanism to make table structure modifications without significantly affecting the availability of the table. The mechanism is called online table redefinition. Redefining tables online provides a substantial increase in availability compared to traditional methods of redefining tables. * To redefine a table online: Choose the redefinition method: by key or by rowid * By keySelect a primary key or pseudo-primary key to use for the redefinition. Pseudo-primary keys are unique keys with all component columns having NOT NULL constraints. For this method, the versions of the tables before and after redefinition should have the same primary key columns. This is the preferred and default method of redefinition. * By rowidUse this method if no key is available. In this method, a hidden column named M_ROW$$ is added to the post-redefined version of the table. It is recommended that this column be dropped or marked as unused after the redefinition is complete. I
Answer :
  • Using Automatic Segment Space Management (ASSM)

Explanation :

Automatic segment space management (ASSM) is a simpler and more efficient way of managing space within a segment. It completely eliminates any need to specify and tune the pctused,freelists, and freelist groups storage parameters for schema objects created in the tablespace. If any of these attributes are specified, they are ignored. * Oracle introduced Automatic Segment Storage Management (ASSM) as a replacement for traditional freelists management which used one-way linked-lists to manage free blocks with tables and indexes. ASSM is commonly called "bitmap freelists " because that is how Oracle implement the internal data structures for free block management. Note: * Buffer busy waits are most commonly associated with segment header contention onside the data buffer pool (db_cache_size, etc.). * The most common remedies for high buffer busy waits include database writer (DBWR) contention tuning, adding freelists (or ASSM), and adding missing indexes.
Answer :
  • All users except SCOTT

Explanation :

If you run multiple AUDIT statements on the same unified audit policy but specify different EXCEPT users, then Oracle Database uses the last exception user list, not any of the users from the preceding lists. This means the effect of the earlier AUDIT POLICY ... EXCEPT statements are overridden by the latest AUDIT POLICY ... EXCEPT statement. Note: * The ORA_DATABASE_PARAMETER policy audits commonly used Oracle Database parameter settings. By default, this policy is not enabled. * You can use the keyword ALL to audit all actions. The following example shows how to audit all actions on the HR.EMPLOYEES table, except actions by user pmulligan. Example Auditing All Actions on a Table CREATE AUDIT POLICY all_actions_on_hr_emp_pol ACTIONS ALL ON HR.EMPLOYEES; AUDIT POLICY all_actions_on_hr_emp_pol EXCEPT pmulligan; References: