1z0-060 Upgrade to Oracle Database 12c Questions and Answers

1. Set user tablespaces in the source database to READ ONLY.

2. From the Oracle Database 11g Release 2 {11.2.0.3) environment, export the metadata and any data residing in administrative tablespaces from the source database using the FULL=Y and TRANSPORTABLE=ALWAYS parameters. Note that the VER$ION=12 parameter is required only when exporting from an Oracle Database llg Release 2 database:

3. Copy the tablespace data files from the source system to the destination system. Note that the log file from the export operation will list the data files required to be moved.

4. Create a COB on the destination system, including a PDB into which you will import the source database.

5. In the Oracle Database 12c environment, connect to the pre-created PDB and import the dump file. The act of importing the dump file will plug the tablespace data files into the destination PDB

Oracle White Paper - Upgrading to Oracle Database 12c -August 2013

Unlike unusable indexes, an invisible index is maintained during DML statements.

Note:

* Oracle 11g allows indexes to be marked as invisible. Invisible indexes are maintained like any other index, but they are ignored by the optimizer unless the OPTIMIZER_USE_INVISIBLE_INDEXES parameter is set to TRUE at the instance or session level. Indexes can be created as invisible by using the INVISIBLE keyword, and their visibility can be toggled using the ALTER INDEX command.

The large pool can provide large memory allocations for the following:

/ (B)UGA (User Global Area) for the shared server and the Oracle XA interface (used where transactions interact with multiple databases)

/Message buffers used in the parallel execution of statements

/ (A) Buffers for Recovery Manager (RMAN) I/O slaves

Note:

* large pool

Optional area in the SGA that provides large memory allocations for backup and restore operations, I/O server processes, and session memory for the shared server and Oracle XA.

* Oracle XA

An external interface that allows global transactions to be coordinated by a transaction manager other than Oracle Database.

* UGA

User global area. Session memory that stores session variables, such as logon information, and can also contain the OLAP pool.

* Configuring the Large Pool

Unlike the shared pool, the large pool does not have an LRU list (not D). Oracle Database does not attempt to age objects out of the large pool. Consider configuring a large pool if the database instance uses any of the following Oracle Database features:

* Shared server

In a shared server architecture, the session memory for each client process is included in the shared pool.

* Parallel query

Parallel query uses shared pool memory to cache parallel execution message buffers.

* Recovery Manager

Recovery Manager (RMAN) uses the shared pool to cache I/O buffers during backup and restore operations. For I/O server processes, backup, and restore operations, Oracle Database allocates buffers that are a few hundred kilobytes in size.

* The CREATE DATABASE ... ENABLE PLUGGABLE DATABASE SQL statement creates a new CDB. If you do not specify the ENABLE PLUGGABLE DATABASE clause, then the newly created database is a non-CDB and can never contain PDBs.

Along with the root (CDB$ROOT), Oracle Database automatically creates a seed PDB (PDB$SEED). The following graphic shows a newly created CDB:

* Creating a PDB

Rather than constructing the data dictionary tables that define an empty PDB from scratch,

and then populating its Obj$ and Dependency$ tables, the empty PDB is created when the CDB

is created. (Here, we use empty to mean containing no customer-created artifacts.) It is referred

to as the seed PDB and has the name PDB$Seed. Every CDB non-negotiably contains a

seed PDB; it is non-negotiably always open in read-only mode. This has no conceptual

significance; rather, it is just an optimization device. The create PDB operation is implemented

as a special case of the clone PDB operation.

D: DB_FILE_MULTIBLOCK_READ_COUNT is one of the parameters you can use to minimize I/O during table scans. It specifies the maximum number of blocks read in one I/O operation during a sequential scan. The total number of I/Os needed to perform a full table scan depends on such factors as the size of the table, the multiblock read count, and whether parallel execution is being utilized for the operation.

Block corruptions can be divided Into Interblock 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 ••> List of Control File and SPFILE.

File TYPE >»» SPFILE or Control File.

Status >»» 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' Database Backup and Recovery User's Guide

12c Release 1 (12.1) - 16 Validating Database Files and Backups

A: The APPEND keyword tells SQL*Loader to preserve any preexisting data in the table. Other options allow you to delete preexisting data, or to fail with an error if the table is not empty to begin with.

B (not D):

Note:

* SQL*Loader-00210: first data file is empty, cannot process the FIELD NAMES record

Cause: The data file listed in the next message was empty. Therefore, the FIELD NAMES FIRST FILE directive could not be processed.

Action: Check the listed data file and fix it. Then retry the operation

E:

* A comma-separated values (CSV) (also sometimes called character-separated values, because the separator character does not have to be a comma) file stores tabular data (numbers and text) in plain-text form. Plain text means that the file is a sequence of characters, with no data that has to be interpreted instead, as binary numbers. A CSV file consists of any number of records, separated by line breaks of some kind; each record consists of fields, separated by some other character or string, most commonly a literal comma or tab. Usually, all records have an identical sequence of fields.

* Fields with embedded commas must be quoted.

Example:

1997,Ford,E350,"Super, luxurious truck"

Note:

* SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database.

References: https://docs.oracle.com/database/121/DVADM/priv_analysis.htm#DVADM591

You must specify the names and locations of the seed's files in one of the following ways:

* (A) Oracle Managed Files

* (B) The SEED FILE_NAME_CONVERT Clause

* (C) The PDB_FILE_NAME_CONVERT Initialization Parameter

SQL> create user c##admin identified by stipsi container=all;

User created.

SQL> grant create table to c##admin container=all;

Grant succeeded.

SQL> revoke create table from c##admin;

revoke create table from c##admin

*

ERROR at line 1:

ORA-65092: system privilege granted with a different scope to 'C##ADMIN'

To configure your database for the Flashback Transaction feature, you or your database administrator must:

With the database mounted but not open, enable ARCHIVELOG:

ALTER DATABASE ARCHIVELOG;

Open at least one archive log:

ALTER SYSTEM ARCHIVE LOG CURRENT;

If not done, enable minimal and primary key supplemental logging:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

If you want to track foreign key dependencies, enable foreign key supplemental logging:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;

References: http://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#ADFNS611

Multipartition maintenance enables adding, dropping, truncate, merge, split operations on multiple partitions.

A: Merge Multiple Partitions:

The new “ALTER TABLE … MERGE PARTITIONS ” help merge multiple partitions or subpartitions with a single statement. When merging multiple partitions, local and global index operations and semantics for inheritance of unspecified physical attributes are the same for merging two partitions.

B: Drop Multiple Partitions:

The new “ALTER TABLE … DROP PARTITIONS ” help drop multiple partitions or subpartitions with a single statement.

Example:

view plaincopy to clipboardprint?

SQL> ALTER TABLE Tab_tst1 DROP PARTITIONS

Tab_tst1_PART5, Tab_tst1_PART6, Tab_tst1_PART7;

Table altered

SQL>

Restrictions:

References:

The log files are created only in $ADR_HOME/log

in alert log only alter system is catched.Sun Mar 30 23:30:04 2014ALTER SYSTEM SET enable_ddl_logging=TRUE SCOPE=BOTH;Sun Mar 30 23:32:41 2014ALTER SYSTEM SET enable_ddl_logging=TRUE SCOPE=BOTH;Sun Mar 30 23:39:35 2014ALTER SYSTEM SET enable_ddl_logging=FALSE SCOPE=BOTH;[oracle@ovs trace]$

only in diag/rdbms/…/../log/ two files are capturing this. The xml file is capturing full details and text file having only the commands.

[oracle@ovs log]$ pwd/oraclebase/diag/rdbms/ora12c1/ora12c1/log[oracle@ovs log]$ cat ddl/*

create table testing2(a number)

create table testing3( aa number)

[oracle@ovs log]$ cat ddl_ora12c1.logSun Mar 30 23:33:26 2014diag_adl:create table testing2(a number)diag_adl:create table testing3( aa number)

References:

* SET_TABLE_PREFS Procedure

This procedure is used to set the statistics preferences of the specified table in the specified schema.

* Example:

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('hr', 'employees', 'PUBLISH', 'false');

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.

C: Setting the CONTROL_MANAGEMENT_PACK_ACCESS initialization parameter to DIAGNOSTIC+TUNING (default) enables monitoring of database operations. Real-Time SQL Monitoring is a feature of the Oracle Database Tuning Pack.

Note:

* The DBMS_SQL_MONITOR package provides information about Real-time SQL Monitoring and Real-time Database Operation Monitoring.

*(not B) BEGIN_OPERATION Function

starts a composite database operation in the current session.

/ (E) FORCE_TRACKING - forces the composite database operation to be tracked when the operation starts. You can also use the string variable 'Y'.

/ (not A) NO_FORCE_TRACKING - the operation will be tracked only when it has consumed at least 5 seconds of CPU or I/O time. You can also use the string variable 'N'.

A (not B, not E): The CREATE DATABASE ... ENABLE PLUGGABLE DATABASE SQL statement creates a new CDB. If you do not specify the ENABLE PLUGGABLE DATABASE clause, then the newly created database is a non-CDB and can never contain PDBs.

D: You can create a PDB by plugging in a Non-CDB as a PDB.

The following graphic depicts the options for creating a PDB:

A: Variable size extents enable support for larger ASM datafiles, reduce SGA memory requirements for very large databases (A), and improve performance for file create and open operations.

C: You don't have to worry about the sizes; the ASM instance automatically allocates the appropriate extent size.

Note:

* The contents of ASM files are stored in a disk group as a set, or collection, of data extents that are stored on individual disks within disk groups. Each extent resides on an individual disk. Extents consist of one or more allocation units (AU). To accommodate increasingly larger files, ASM uses variable size extents.

* The size of the extent map that defines a file can be smaller by a factor of 8 and 64 depending on the file size. The initial extent size is equal to the allocation unit size and it increases by a factor of 8 and 64 at predefined thresholds. This feature is automatic for newly created and resized datafiles when the disk group compatibility attributes are set to Oracle Release 11 or higher.

* You can include the CONTAINER clause in several SQL statements, such as the CREATE USER, ALTER USER, CREATE ROLE, GRANT, REVOKE, and ALTER SYSTEM statements.

* * CREATE ROLE with CONTAINER (optional) clause

/ CONTAINER = ALL

Creates a common role.

/ CONTAINER = CURRENT

Creates a local role in the current PDB.

* 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.