1z0-067: Upgrade Oracle9i/10g/11g OCA OR OCP to Oracle Database 12c OCP

You create a table with the period for clause to enable the use of the Temporal Validity feature of Oracle Database 12c.

Examine the table definition:

create table employees

(empno number, salary number,

deptid number, name varchar2(100),

period for employee_time);

Which three statements are true concerning the use of the Valid Time Temporal feature for the EMPLOYEES table?

The valid time columns employee_time_start and employee_time_end are automatically created.
The same statement may filter on both transaction time and valid temporal time by using the AS OF TIMESTAMP and PERIOD FOR clauses.
The valid time columns are not populated by the Oracle Server automatically.
The valid time columns are visible by default when the table is described.
Setting the session valid time using DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME sets the visibility for data manipulation language (DML), data definition language (DDL), and queries performed by the session.

Correct answer: ABC

Explanation:

Reference: https://docs.oracle.com/database/121/ARPLS/d_flashb_archive.htm#ARPLS74552

Which two statements are true when row-archival management is enabled?

Visibility of the ORA_ARCHIVE_STATE column is controlled by the row archival visibility session parameter.
The ORA_ARCHIVE_STATE column is updated manually or by a program that can reference activity tracking columns, to indicate that a row is no longer considered active.
The row archival visibility session parameter defaults to all rows.
The ORA_ARCHIVE_STATE column is visible if it is referenced in the select list of a query.
The ORA_ARCHIVE_STATE column is updated automatically by the database based on activity tracking columns, to indicate that a row is no longer considered active.

Correct answer: BD

Which two resources might be prioritized between competing pluggable databases (PDBs) when creating a multitenant container database (COB) plan using Oracle Database Resource Manager?

maximum undo per consumer group
maximum idle time for a session in a PDB
parallel server limit
CPU
maximum number of sessions for a PDB

Correct answer: CD

Which three types of failures are detected by the Data Recovery Advisor (DRA)?

loss of a non-critical data file
loss of a control file
physical data block corruption
logical data block corruption
loss of an archived redo log file

Correct answer: BDE

Explanation:

Data Recovery Advisor can diagnose failures such as the following: Components such as datafiles and control files that are not accessible because they do not exist, do not have the correct access permissions, have been taken offline, and so on Physical corruptions such as block checksum failures and invalid block header field values

You want to capture column group usage and gather extended statistics for better cardinality estimates for the customers table in the SH schema.

Examine the following steps:

Issue the SELECTDBMS_STATS. CREATE_EXTENDED_STATS(‘SH’, 'CUSTOMERS')from dual statement.
Execute the dbms_stats.seed_col_usage (null,‘SH’,500) procedure.
Execute the required queries on the customers table.
Issue the select dbms_stats.reportwcol_usage(‘SH’, 'customers') from dual statement.

Identify the correct sequence of steps.

3, 2, 1, 4
2, 3, 4, 1
4, 1, 3, 2
3, 2, 4, 1

Correct answer: B

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 statistics Here 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.

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 statistics

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

Examine the initialization parameter that is set in the PFILE:

DB_CREATE_FILE_DEST ='/u01/app/oracle/oradata/'

You execute the following command to create theCDB1. container database (CDB):

SQL>CREATE DATABASE CDB1

DEFAULT TABLESPACE users

DEFAULT TEMPORARY TABLESPACE temp

UNDO TABLESPACE undotbsl

ENABLE PLUGGABLE DATABASE

SEED

SYSTEM DATAFILES SIZE125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED

SYS AUX DATAFILES SIZE 100M;

Which three statements are true?

It creates a multitenant container database with a root and a seed pluggable database (PDB) that are opened in read-write and read-only modes, respectively.
The files created for both the root and seed databases use Oracle Managed Files (OMF).
It creates a multitenant container database with the root and seed databases opened and one PDB mounted.
It sets the users tablespace as the default for both the root and seed databases.
undotbs1 is used as the undo tablespace for both the root and seed databases.
It creates a multitenant container database with the root database opened and the seed database mounted.

Correct answer: ABE

Explanation:

The seed actually doesn’t use anything since it’s read-only, but any PDB created from the seed will use the undotbs1 undo tablespace. Each PDB can have its own default tablespace, but cannot change the undo tablespace.

Your database is running in archivelog mode. Examine the parameters for your database instance:

LOG_ARCHIVE_DEST_l ='LOCATION=/disk1/arch MANDATORY'

LOG_ARCHIVE_DEST_2 ='LOCATION=/disk2/arch'

LOG_ARCHIVE_DEST_3 ='LOCATIO=/disk3/arch'

LOG_ARCHIVE_DEST _4 ='LOCATIONs/disk4/arch'

LOG_ARCHIVE _MIN_SUCCEED_DEST = 2

While the database is open, you notice that the destination set by the log_archive_dest_1 parameter is not available. All redo log groups have been used.

What happens at the next log switch?

The database instance hangs and the redo log files are not overwritten.
The archived redo log files are written to the fast recovery area until the mandatory destination is made available.
The database instance is shutdown immediately.
The destination set by the log_archive_dest parameter is ignored and the archived redo log files are created in the next two available locations to guarantee archive log success.

Correct answer: A

Explanation:

Made more logfile switch until all redos groups are full. first log switch succeds, second also succeds then next will hang database.

Your database is running in archivelog mode and a nightly backup of the database, along with an autobackup of the control file, is taken by using RMAN. Because of a media failure, the SPFILE and the control files are lost.

Examine the steps to restore the SPFILE and the control file to mount the database:

Set DBID of the target database in RMAN.
Start the database instance by using the STARTUP FORCE NOMOUNT command in RMAN.
Restore the control files from the backup.
Mount the database.
Restore the SPFILE from the autobackup.
Create a PFILE from the recovered SPFILE.
Restart the instance in NOMOUNT state.

Identify the required steps in the correct order.

1, 2, 5, 3, 6, 4
1, 2, 3, 5, 6, 4
2, 1, 5, 7, 3, 4
2, 1, 5, 6, 7, 4, 3

Correct answer: C

After implementing full Oracle Data Redaction, you change the default value for the number data type as follows:

SQL> SELECT NUMBER_VALUE FROM REDACTION_VALUES_FOR_TYPE_FULL; NUMBER_VALUE

-------------------------

0

SQL> EXEC DBMS_REDACT.UPDATE_FULL_REDACTI0N_VALUES(-1)

PL/SQL procedure successfully completed.

SQL> select number_value from redaction_values_for_type_full;

NUMBER VALUE

------------------------

-1

After changing the value, you notice that FULL redaction continues to redact numeric data with a zero.

What must you do to activate the new default value for numeric full redaction?

Re-enable redaction policies that use FULL data redaction.
Re-create redaction policies that use FULL data redaction.
Re-connect the sessions that access objects with redaction policies defined on them.
Flush the shared pool.
Restart the database instance.

Correct answer: E

Explanation:

About Altering the Default Full Data Redaction Value You can alter the default displayed values for full Data Redaction polices. By default, 0 is the redacted value when Oracle Database performs full redaction (DBMS_REDACT.FULL) on a column of the NUMBER data type. If you want to change it to another value (for example, 7), then you can run the DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES procedure to modify this value. The modification applies to all of the Data Redaction policies in the current database instance. After you modify a value, you must restart the database for it to take effect.

About Altering the Default Full Data Redaction Value

You can alter the default displayed values for full Data Redaction polices. By default, 0 is the redacted value when Oracle Database performs full redaction (DBMS_REDACT.FULL) on a column of the NUMBER data type. If you want to change it to another value (for example, 7), then you can run the DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES procedure to modify this value. The modification applies to all of the Data Redaction policies in the current database instance. After you modify a value, you must restart the database for it to take effect.

You want to create a guaranteed restore point for your database by executing the command:

SQL> CREATE RESTORE POINT dbrsp1 GUARANTEE FLASHBACK DATABASE;

Identify two prerequisites for the successful execution of this command.

The database must be running in archivelog mode.
Flashback Database must be enabled.
Fast Recovery Area must be enabled.
The recyclebin must be enabled for the database.
Undo retention guarantee must be enabled.
A database backup must be taken.

Correct answer: AC

Explanation:

SQL> select flashback_on from v$database; FLASHBACK_ON —————— NO SQL> CREATE RESTORE POINT dbrsp1 GUARANTEE FLASHBACK DATABASE; Restore point created. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 113 Next log sequence to archive 115 Current log sequence 115 SQL> http://docs.oracle.com/cd/B19306_01/backup.102/b14192/rpfbdb002.htm

SQL> select flashback_on from v$database;

FLASHBACK_ON

——————

NO

SQL> CREATE RESTORE POINT dbrsp1 GUARANTEE FLASHBACK DATABASE;

Restore point created.

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 113

Next log sequence to archive 115

Current log sequence 115

SQL>

http://docs.oracle.com/cd/B19306_01/backup.102/b14192/rpfbdb002.htm

Vendor:	Oracle
Exam Code:	1z0-067
Exam Name:	Upgrade Oracle9i/10g/11g OCA OR OCP to Oracle Database 12c OCP
Date:	Oct 01, 2018
File Size:	325 KB

Download Upgrade Oracle9i-10g-11g OCA OR OCP to Oracle Database 12c OCP.1z0-067.TestInside.2018-10-01.125q.vcex

How to open VCEX files?

Demo Questions

Question 1

Question 2

Question 3

Question 4

Question 5

Question 6

Question 7

Question 8

Question 9

Question 10

ProfExam at a 20% markdown