Oracle12c OCP Exam Went through..

I am glad i went through Oracle12c OCP exam yesterday and passed!

I went through Oracle training and read study material helped me to get through this exam.

This exam has two section.

Section 1 is New features of Oracle database 12c. Passing score is 64%.  I scored 86%.
Section 2 is Key DBA skills. Passing score is 65%. I scored 90%.

Here are some sample OCP questions.

Question 1

A database is stored in Automatic storage management(ASM) disk group, disk group, DGROUP1
with SQL :

SQL > CREATE DISKGROUP dgroup1 NORMAL REDUNDANCY
             FAILGROUP controller1 DISK '/devices/diska1','/devices/diska2'
             FAILGROUP controller2 DISK '/devices/diskb1','/devices/diskb2';

There is enough free space in the disk gorup for mirroring to be done.

What happens if the CONTROLLER1 failure group becomes unavailable due to maintenance error?

A) Transactions and queries accessing database objects contained in any tablespace stored in DGROUP1 will fail

B) 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.

C) The data in the CONTROLLER1 failure group is copied to the CONTROLLER2 failure group and re balancing is initiated.

D) ASM does not mirror any data until the controller failure group is brought back online, and newly allocated primary allocation unit (PA) are stored in the controller2 failure group without mirroring.

F) Transactions accessing database objects  contained in any tablespace stored in DGROUP1 will fail

but queries will succeed.

The answer D

Question 2

In your Multitenant container database(CDB) containing pluggable database(CDB), users complain about performance degradation.

How does real - time automatic database diagnostic monitor check performance degradation  and provide solutions?

a) It collects data from SGA and compares it with a preserved snapshot.
b) It collects data from SGA,  analyze it and provides a report.
c) It collects data from SGA and compares it with a latest snapshot.
d) It collects data from both SGA and PGA, analyze it and provides a report.

The answer B

Question 3

Your Multitenant container database(CDB) has three pluggable database(PDB's) : PDB1,PDB2,PDB3.

Which two RMAN commands may be, used to backup only the PDB1 pluggable database?

a) BACKUP PLUGGABLE DATABASE PDB1 while connected to the root container.
b) BACKUP PLUGGABLE DATABASE PDB1 while connected to the PDB1 container.
c) BACKUP DATABASE while connected to the PDB1 container.
d) BACKUP DATABASE while connected to the boot  container.
e) BACKUP PLUGGABLE database PDB1 while connected to PDB2.

The answer A and C.
Question 4
To enable the Database Smart Flash Cache, you configure the following parameters:
DB_FLASH_CACHE_FILE='/dev/flash_device_1','/dev/flash_device_2'
DB_FLASH_CACHE_SIZE=64G
What is the result when you start up the database instance?
a)It result in an error because these parameter settings are invalid
b)One 64G flash cache file will be used
c)Two 64G flash cache file will be used
d)Two 32G flash cache file will be used
The answer A

Question 5
Your database is running an ARCHIVELOG mode,

The following parameter are set in your database instance.
LOG_ARCHIVE_FORMAT=arch+%t_%r.arc
LOG_ARCHIVE_DEST_1='LOCATION=/disk1/archive'
DB_RECOVERY_FILE_DEST_SIZE=50G
DB_RECOVERY_FILE='/u01/oradata'

Which statement is true about the archived log redo files?
a)They are created only in the location specified by the LOG_ARCHIVE_DEST_1  parameter
b) They are created only in the Fast Recovery Area
c) They are created in the location specified by the LOG_ARCHIVE_DEST_1 parameter and in the default location $ORACLE_HOME/dbs/arch
d) They are created in the location specified by the DB_RECOVERY_FILE_DEST_1 parameter and location specified by DB_RECOVERY_FILE_DEST parameter.
The answer A

Question 6

Your multitenant container(CDB) contains two pluggable database(PDB), HR_PDB and ACCOUINTS_PDB, both of which use the CDB tablespace. The temp file is
called temp01.tmp.

A user issues a query on a table on one of the PDBs and receives the following error.
ERROR at line 1:
ORA-01565: error in identifying file '/u01/app/oracle/oradata/CDB1/temp01.tmp'
ORA-27037: unable to obtain file status

Identify two ways to rectify the error.

a) Add a new temp file to the temporary tablespace and drop the temp file that produced the error
b) Shut down the database instance, restore the temp01.tmp file from the backup, and then restart the database
c) Take the temporary tablespace offline, recover the missing temp file by applying redo logs, and then bring the temporary tablespace  online
d) shutdown the database instance, restore and recover the temp file from the backup, and then open the database with RESETLOGS
e) shutdwon the database instance and then restart CDB and PDBs
The answer A and E.

Question 7

The persistent configuration settings for RMAN have defualt for all parameters.

Identify four RMAN commands that produce a multi-section backup.

a) BACKUP TABLESPACE SYSTEM SECTION SIZE 100M
b) BACKUP AS COPY TABLESPACE SYSTEM SECTION SIZE 100M
c) BACKUP ARCHIVELOG ALL SECTION SIZE 25M
d) BACKUP TABLESPACE "TEMP" SECTION SIZE 10M
e) BACKUP TABLESPACE "UNDO" INCLUDE CURRENT CONTROLFILE SECTION SIZE 100M
f) BACKUP SPFILE SECTION SIZE 1M
g) BACKUP INCREMENTAL LEVEL 0 TABLESPACE SYSAUX SECTION SIZE 100M

The answer A , B, E  and G

Question 8

Which two are prerequisite for performing a flashback transaction?

a) Flashback Database must be enabled
b) Undo retention guarantee for the database must be configure
c) EXECUTE privilege on the DBMS_FLASHBACK package must be granted to the user flashing back transaction
d) Supplemental logging must be enabled
e) Recycle bin must be enabled for the database
f) Block change tracking must be enabled for the database

Answer C and D

Question 9

Which three statements are true about Automatic Workload Repository(AWR)?

a) All AWR tables belong to the SYSTEM schema
b) The AWR data is stored in memory and in the database
c) The snapshots collected by AWR are used by the self tuning components in the database
d) AWR computes time model statistics based on time usage for activities, which are displayed in the v$SYS time model and V$SESS_TIME_MODEL views
e) AWR contains system wide tracing and logging information
Answer B, C and D


Question 10

Which three statements are true about using flashback database in a multitenant container database(CBD)?

a) The root container can be flashed back without flashing back the pluggable database(PDBs)
b) To enable flashback database, the CDB must be mounted.
c) Individual PDB's can be flashed back without flashing back the entire CDB
d) The DB_FLASHBACK RETENTION_TARGET parameter must be set to enable flashback of the CDB
e) A CDB can be flashed back specifying the desired target point in time or an SCN, but not a restore point

Answer B,D and E

Question 11
Which two statements are true when row archival management is enabled?
a) The ORA_ARCHIVE_STATE column visibility is controlled by the ROW ARCHIVAL VISIBILITY session parameter
b)The ORA_ARCHIVE_STATE column is updated manually or by a program that could reference activity tracking columns, to indicate that a row is no longer considered active.
c)The ROW ARCHIVAL VISIBILITY session parameter defaults to active rows only
d)The ORA_ARCHIVE_STATE column is visible if referenced in the select list of a query
e)The ORA_ARCHIVE_STATE column is updated automatically by the Oracle Server based on activity tracking columns, to indicate that a row is no longer considered active.
Answer C and D.

Question 12

Which Oracle Database component is audited by default if the unified Auditing Option is enabled?
a) Oracle Data Pump
b) Oracle Recovery Manager(RMAN)
c) Oracle Label Security
d) Oracle Database Vault
e) Oracle Real Application Security
Answer B
Question 13

Oracle Grid Infrastructure for a stand-alone server is installed on your production host before installing the Oracle Database server. The database and listener are configured by using oracle Restart.


Examine the following command and its output:


$ crsctl config has
CRS-4622: Oracle High Availability Services auto start is enabled.


What does this imply?


a) When you start an instance on a high with SQL *Plus dependent listeners and ASM disk groups are automatically started.
b) When a database instance is started by using the SRVCTL utility and listener startup fails, the instance is still started.
c) When database is created by using SQL * PLUS, it is automatically added to the Oracle Restart configuration.
d) When you create a database service by modifying the SERVICE_NAMES initialization parameter, it is automatically added to the Oracle Restart configuration.
Answer : B
Question 14


Which three statements are true regarding the use of the Database Migration Assistant for Unicode(DMU)?
a) A DBA can check specific tables with the DMU
b) The database to be migrated must be opened read-only
c)The release of the database to be converted can be any release since 9.2.0.8
d)The DMU can report columns that are too long in the converted characterset
e)The DMU can report columns that are not represented in the converted characterset.
Answer : A,D,E
Question 15
Your multitenant container database (CDB) contains a pluggable database, HR_PDB. The default
permanent tablespace in HR_PDB is USERDATA. The container database (CDB) is open and you
connect RMAN.
You want to issue the following RMAN command:


RMAN > BACKUP TABLESPACE hr_pdb:userdata;
Which task should you perform before issuing the command?
a)Place the root container in ARHCHIVELOG mode.
b)Take the user data tablespace offline.
c) Place the root container in the nomount stage.
d) Ensure that HR_PDB is open.

Answer : A
Question 16
Which three features work together, to allow a SQL statement to have different cursors for the same statement based on different selectivity ranges?
a) Bind Variable Peeking
b)SQL Plan Baselines
c) Adaptive Cursor Sharing
d) Bind variable used in a SQL statement
e) Literals in a SQL statement

Answer : A,C,E
Question 17
Which three statements are true about Oracle Data Pump export and import operations?
a) You can detach from a data pump export job and reattach later
b)Data pump uses parallel execution server processes to implement parallel import
c)Data pump import requires the import file to be a directory owned by the oracle owner
d)The Master table is the last object to be exported by the data pump
e) You can detach from a data pump import job and reattach later.
Answer : A,D,E

Question 18
Which three statements are true about adaptive SQL plan management?
a)It automatically performs verification or evolves non-accepted plans, in COMPREHENSIVE mode when they perform better than existing accepted plans.
b)The optimizer always uses the fixed plan, if the fixed plan exists in the plan baseline.
c)It adds new, better plans automatically as fixed plans to the baseline
d)The non-accepted plans are automatically accepted and become usable by the optimizer if they perform better than the existing accepted plans
e)The non-accepted plans in a SQL plan baseline are automatically evolved, in COMPREHENSIVE mode, during the nightly maintenance window and a persistent verification report is generated.
Answer : A,D,E
Question 19
On your Oracle 12c database, you invoked SQL * Loader to load data into the EMPLOYEES table in the HR schema by issuing the following command.
$> sqlldr hr/hr@pdb table=employees
which two statements are true regarding the command?
a)It succeeds with default settings if the EMPLOYEES table belonging to HR is already defined in the database
b) It fails because no SQL * Loader data file location is specified
c)It fails if the HR user does not have the CREATE ANY DIRECTORY privilege
d)It fails because no SQL * Loader control file location is specified.
Answers : A,C

Question 20
You plan to Migrate your database from a file system to ASM on same platform.
Which two methods or commands would you use to accomplish this task?
a)RMAN convert command
b)Data Pump Export and import
c)Conventional Export and Import
d)The BACKUP AS COPY DATABASE... command of  RMAN
e)DBMS_FILE_TRANSFER with transportable tablespace
Question 21
Which two statements are true about the RMAN validate database command?
a)It checks the database for intrablock corruptions
b)It can detect corrupt pfiles
c)It can detect corrupt spfiles
d)It checks the database for interblock corruptions
e)It can detect corrupt block change tracking files

Question 22
Your multitenant container database,  CDB1, is running in ARCHIVELOG mode and has two pluggable databases, HR_PDB and ACCOUNTS_PDB. An RMAN backup exists for the database.

You issue the command to open ACCOUNTS_PDB and find that the USERDATA.DBF data file for the default permanent tablespace USERDATA belonging to ACCOUNTS_PDB is corrupted.

What should you do before executing the commands to retore and recover the data in ACCOUNTS_PDB?
a)Place CDB1 in the mount stage and then the USERDATA tablespace offline in ACCOUNTS_PDB
b)Place CDB1 in the mount stage and issue the
ALTER PLUGGABLE DATABASE accounts_pdb CLOSE IMMEDIATE command.
c)Issue the ALTER PLUGGABLE DATABASE  accounts_pdb RESTRICTED command
d)Take the USERDATA tablespace offline in ACCOUNTS_PDB

Question 23
You use a recovery catalog for maintaining your database backups.

You execute the following command:
$rman TARGET/CATALOG rman/cat@catdb
RMAN> BACKUP VALIDATE DATABASE ARCHIVELOG ALL;
Which two statements are true?
a)Corrupted blocks, if any are repaired
b)Checks are performed for physical corruptions
c)Checks are performed for logical corruptions
d)Checks are performed to confirm whether all database files exists in correct locations
e)backup sets containing both data files and archive logs are created.

Question 24
You notice a performance change in your production oracle database and you want to know which change has made this performance difference.

You generate the compare period Automatic Database Diagnostic Monitor(ADDM) report to further investigation.

Which three findings would you get from the report?

a)It detects any configuration change that caused a performance difference in both time period.
b)It identifies any workload change that caused a performance difference in both time period.
c)It detects the top wait events causing performance degradation.
d)It shows the resource usage for CPU, memory, and I/O in both time periods.
e)It shows the difference in the size of memory pools in  both time period,
f)It gives information about statistics collection in  both time periods.

Question 25
Which three statements are true about SQL plan directives?
a)They are tied to specific statement or SQL ID.
b)They instruct the maintenance job to collect missing statistics or perform dynamic sampling to generate a more optimal plan.
c)They used to gather only missing statistics.
d)They are created for a query expression where statistics are missing or the cardinality estimates by the optimizer are incorrect.
e)They instruct the optimizer to create only column group statistics.
f)Improve plan accuracy by persisting both compilation and execution statistics in the SYSAUX tablespace.

Question 26
Which four actions are possible during an online Data file Move operations?
a)Creating and dropping tables in the data file being moved.
b)Performing file shrink of the data file being moved.
c)Querying tables in the data file being moved.
d)Performing Block Media Recovery for a data block in the data file being moved.
e)Flashing back the database
f)Executing DML statements on objects stored in the data file being moved.

Question 27

a)Fine Grained Auditing(FGA) is enabled for the PRICE column in the PRODUCT tables for SELECT statements only when a row with PRICE > 10000 is accessed.

b)FGA is enabled for the PRODUCTS.PRICE column and an audit record is written whenever a row with PRICE>10000 is accessed.

c)FGA is enabled for all DML operations by JIM on the PRODUCTS.PRICE column.

d)FGA is enabled for the PRICE column of the PRODUCTS table and the SQL statements are captured in the FGA audit trail.

Question 28

You are administering a database stored in ASM. You use RMAN to backup the database and the MD_BACKUP command to backup the ASM metadata regularly. You lost an ASM disk group DG1 due to hardware failure.

In which three ways can you re-create the lost disk group and restore the data?
a)Use the MD_RESTORE command to restore metadata for an existing disk group by passing the existing disk group name as an input parameter and use RMAN to restore the data.
b)Use the MKDG command to restore the disk group with the same configuration as the backed -up disk group and data on the disk group.
c)Use the MD_RESTORE command to restore the disk group with the changed disk group specification, failure group specification name, and other attributes and use RMAN to restore the data.
d)Use the MKDG command to restore the disk group with the same configuration as the backed-up disk group name and same set of disks and failure group configuration, and use RMAN to restore the data.
e)Use the MD_RESTORE command to restore both the metadata and data for the failed disk group.
f)Use the MKDG command to add a new disk group DG1 with the same or different specifications for failure group and other attributes and use RMAN to restore the data.

Question 29

Examine the parameters for your database instance.

Which three statements are true about the process of automatic optimization by using cardinality feedback?

a)The optimizer automatically changes a plan during subsequent execution of a SQL statement if there is a huge difference in optimizer estimates and execution statistics.
b)The optimizer can re optimize a query only once using cardinality feedback.
c)The optimizer enables monitoring for cardinaltiy feedback after the first execution of a query.
d)The optimizer does not monitor cardinality feedback if dynamic sampling and multicolumn statistics are enabled.
e)After the optimizer identifies a query as a re-optimization candidate, statistics collected by the collectors are submitted to the optimizer.

Question 30

You have altered a non-unique index to be invisible to determine if queries execute within an acceptable response time without using this index,

Which two are possible if table updates are performed which affect the invisible index columns?
a)The index remains invisible
b)The index is not updated by the DML statements on the indexed table
c)The index automatically becomes visible in order to have it updated by DML on the table.
d)The index becomes unusable but the table is updated by DML
e)The index is updated by the DML on the table.

Question 31
Which three are direct benefits of the multiprocess, multithreaded architecture of Oracle Database 12c when it is enabled?
a)Reduced Logical I/O
b)Reduced virtual memory utilization
c)Improved Parallel Execution performance
d)Improved Serial Execution Performance
e)Reduced Physical I/O
f)Reduced CPU utilization

Question 32
You are using SQL Plus to the root container of a multitenant container database(CDB) with SYSDBA privilege.

The CDB has several pluggable databases(PDB's) open in the read/write mode.
There are ongoing transactions in both the CDB and PDBs
What happens after issuing the SHUTDOWN TRANSACTIONAL statement?
a) The shutdown proceeds immediately.  The shutdown proceeds as soon as all transactions in the PDB's are either committed or rolled back.
b)The shutdown proceeds as soon as all transactionals in the CDB are either committed or rolledback.
c)The shutdown proceeds as soon as all transactions in both the CDB and PDBs are either committed or rolled back.
d)The statement results in an error because there are open PDBs

Question 33
Which two statements are true about the use of the procedures listed in the v$sysaux_occupants.move_procedure column?
a)The procedure may be used for some components to relocate component data to the SYSAUX tablespace from its current tablespace.
b)The procedure may be used for some components to relocate component data from the SYSAUX tablespace to another tablespace.

c)All the components may be moved into SYSAUX tablespace

d)All the components may be mvoed from the SYSAUX tablespace.

Question 34

User SCOTT has been granted the CREATE SESSION privilege and the MGR role.
Which two statements are true when a session logged in as SCOTT queries the SAL column in the view and the table?
a) Data is redacted for the EMP.SAL column only if the SCOTT session does not have the MGR role set.
b)Data is redacted for EMP.SAL column only if the SCOTT session has the MGR role set.
c)Data is never redacted for the EMP_V.SAL column
d)Data is redacted for the EMP_V.SAL column only if the SCOTT session has the MGR role set.
e)Data is redacted for  the EMP_V.SAL column only if the SCOTT session does not have the MGR role set.

Question 35
Examine the parameter for your database instance.

You generated the execution plan for the following query in the plan table and noticed that the nested loop join was done. After actual execution of the query, you notice that the hash join was done in the execution plan:

a)The optimizer used a dynamic plan for the query
b)The optimizer chose different plans because automatic dynamic sampling was enabled.
c)The optimizer used re-optimization cardinality feedback for the query
d)The optimizer chose different plan because extended statistics were created for the column used.

Question 36
You are the DBA supporting an Oracle 11g Release 2 database and wish to move a table containing several DATE, CHAR, VARCHAR2 and NUMBER data types, and the table's indexes, to another tablespace.

The table does not have a primary key and is used by an OLTP application

Which technique will move the table and indexes while maintaining the highest level of availability to the application?
a)Oracle Data Pump
b)An ALTER TABLE MOVE to move the table and ALTER INDEX REBUILD to move the indexes
c)An ALTER TABLE MOVE to move the table and ALTER INDEX REBUILD ONLINE to move the indexes

d)Online Table Redefinition

e)Edition-Based Table Redefinition

Question 37

Which two partitioned table maintenance operations support asynchronous Global index Maintenance in Oracle database 12c?

a)ALTER TABLE SPLIT PARTITION

b)ALTER TABLE MERGE PARTITION

c)ALTER TABLE TRUNCATE PARTITION

d)ALTER TABLE ADD PARTITION

e)ALTER TABLE DROP PARTITION

f)ALTER TABLE MOVE PARTITION

Question 38
Examine the details of the Top 5 Timed Events in the following AWR report?

What are three possible causes for the latch-related wait events?
a)The size of the shared pool is too small
b)Cursors are not being shared
c)A large number of COMMITS are being performed
d)There are frequent logons and logoffs
e)The buffers are being read into the buffer cache. but some other session is changing the buffers.

Question 39
Which statement is true about EM express in oracle 12c?
a)By default, EM express is available for a database  after database creation
b)You can use EM express to manage multiple databases running on the same server
c)You can perform basic administrative tasks for pluggable databases by using the EM express interface
d)You can not startup or shut down a database instance by using EM express
e)You can create and configure pluggable databases by using EM express.

Question 40
Examine the following command.

ALTER SYSTEM SET enable_ddl_logging=FALSE;

Which statement is true?
a)None of the DDL statemets are logged in the trace file
b)Only DDL commands that resulted in errors are logged in the alert log file
c)A new log xml file that contains the DDL statements is created, and the DDL command details are removed from the alert log file.
d)Only DDL commands that resulted in the creation of new database files are logged.

Question 41
What is the result of executing a TRUNCATE TABLE command on a table that has Flashback Archiving enabled?
a)It fails with the ORA-665610 Invalid DDL statement on history-tracked message
b)The rows in the table are truncated without being archived
c)The rows in the table are archived, and then truncated
d)The rows in both the table and the archive are truncated.

Question 42
A warehouse fact table in your Oracle12c Database is range-partitioned by month and accessed
frequently with queries that span multiple partitions.

The table has a local prefixed, range partitioned index.

Some of these queries access very few rows in some partitions and all the rows in other partitions, but these queries still perform a full scan for all accessed partitions.

This commonly occurs when the range of dates begins at the end of a month or ends close to the start of month.

You want an execution plan to be generated that uses indexes access when only a few rows are accessed form a segment, while still allowing full scans for segments where many rows are returned.

Which three methods could transparently help to achieve this result?

a)Using a partial local index on the warehouse fact table month column with indexing disabled to the table partition that return most of their rows to the queries.
b)Using the partial local index on the warehouse fact table month column with indexing disabled for the table partition that return a few rows to the queries.
c)Using a partitioned view that does a UNION ALL query on the partitions of the warehouse fact table, which retains the existing local partitioned column.
d)Converting the partitioned table to a partitioned view that does a UNION ALL query on the monthly tables, which retains the existing local partitioned column.
e)Using the partial global index on the warehouse fact table month column with indexing disabling for the table partitions that return most of their rows to the queries.

f)Using a partial global index on the warehouse fact table month column with indexing for disabled for the table partition that return a few rows to the queries.

Question 43
Your upgraded your database from pre-12c to a multitenant container database(CDB)
containing pluggable database(PDBs).

Examine the query and its output.

Which two tasks must you perform to add users with SYSBAKCUP,SYSDG and SYSKM privilege to the password file?

a)Assign appropriate OS groups to SYSBAKCUP,SYSDG and SYSKM.
b)Grant SYSBAKCUP,SYSDG and SYSKM privilege to the intended users.
c)Re-create the password file with SYSBAKCUP,SYSDG and SYSKM privilege and the FORCE argument set to No.
d)Re-create the password file with SYSBAKCUP,SYSDG and SYSKM privilege, and FORCE arguments set to Yes.
e) Re-create the password file in the Oracle Database 12c format

Question 44

You Create a table with the PERIOD FOR clause to enable the use of the Temporal Validity features of Oracle Database 12c.

Examine the table definition.

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

a) The Valid time columns employee_time_start and employee_time_end are automatically created.
b) The same statement may filter on both transaction time and valid temporal time by using the AS OF TIMESTAMP and PERIOD FOR clause.
c)The Valid time columns are not populated by the oracle server automatically
d)The valid time columns are visible by default when the table is described.
e)Setting the session valid time using DBMS_FLASHBACK_ARCHIVE_ENABLE_AT_VALID_TIME sets the visibility for DML , DDL and queries performed by the session.

Question 45
Which two statements are true concerning the Resource Manager plans for individual pluggable databases(PDB plans) in a multitenant container database(CDB)?

a)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
b)In a PDB plan, subplans may be used with up to eight consumer groups
c)If a PDB plan is enabeld for a pluggable database, then resources are allocated to consumer groups across all PDBs in the CDB.
d)If no PDB plan is enabled for a pluggable database, then the PDB share in the CDB plan is dynamically calculated.
e)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.

Question 46
Your Database supports a DSS workload that involves the execution of complex 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?

a)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.
b)Run the Automatic workload Repository Monitor(ADDM)
c)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.
d)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

Question 47

Examine the contents of the SQL Loader control file :

Which three statements are true regarding the SQL * Loader operation performed using the control file?

a)An EMP table is created if a table does not exist.  Otherwise, if the EMP table is appended with the loaded data.

b)The SQL * Loader data file myfile1.data has the column names for the EMP table.

c)The SQL * Loader operation fails because no record terminators are specified

d) Field names should be the first line in the both the SQL * Loader data files

Question 48

An Administrator Account is granted the CREATE SESSION and SET CONTAINER system privileges.

A multitenant container database (CDB) instant has the following parameter set :

THREADED_EXECUTION = FALSE

Which four statements are true about this administrator establishing connections to root in a CDB that has been opened in read only mode?

a) You can connect as a common user by using the connect statement

b) You can connect as a local user by using the connect statement

c) You can connect by using easy connect

d) You can connect by using OS authentication

e) You can connect by using a Net Service name

f) You can connect as a local user by using  the SET CONTAINER statement.

Question 49

Which three tasks can be automatically performed by the automatic Data optimization feature  of information life cycle management (ILM)?

a) Tracking the most recent read time for a table segment in a user tablespace

b) Tracking the most recent write time for a table segment in a user tablespace

c) Tracking insert time by row for table rows

d) Tracking the most recent write time for table block.

e) Tracking the most recent read time for a table segment in a SYSAUX tablespace

f) Tracking the most recent write time for a table segment in a SYSAUX tablespace

Question 50

Which three are true about the large pool for an oracle database instance that supports shared server connections?

a) Allocate memory for RMAN backup and restore operations

b) Allocate memory for shared and private SQL areas

c) Contains a cursor area for storing runtime information about cursors

d) Contains stack space

e) Contains a hash area performing hash joins of tables

Question 51

You want to flash back a test database by five hours.

You issue this command:

SQL > FLASHBACK DATABASE TO TIMESTAMP (SYSDATE-5/24);

Which two statements are true about this flashback scenario?

a) The database must have multiplexed redo logs for the flashback to succeed.

b) The database must be MOUNTED for the flashback to succeed

c) The database must use block change tracking for the flashback to succeed

d) The database must be opened in restricted mode for the flashback to succeed.

e) The database must be opened with the RESETLOGS option after the flashback is complete

f) The database must be opened in read-only mode to check if the database has been flashed back to the  correct SCN

Question 52

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?

A)The C ## B_ADMIN user can perform all backup and recovery operations using RMAN only.

B)The C ## B_ADMIN user can perform the data guard operation with data guard broker

C)The C ## B_ADMIN user can perform wallet operations

D)The C ## B_ADMIN user can perform backup and recovery operations for ASM

E)The C ## B_ADMIN user can perform all backup and recovery operations using RMAN or SQL * Plus

Question 53

You are connected to a pluggable database(PDB) as a common user with DBA privileges.

The STATISTICS_LEVEL parameter is PDB_MODIFIABLE. You execute the following :

SQL> ALTER SYSTEM SET STATISTICS_LEVEL = ALL SID = ‘*’ SCOPE = SPFILE;

Which is true about the result of this command?

a)The  STATISTICS_LEVEL  parameter is set to ALL whenever this PDB is re-opened.

b)The  STATISTICS_LEVEL  parameter is set to ALL whenever any PDB is reopened.

c)The  STATISTICS_LEVEL  parameter is set to ALL whenever the multitenant container database(CDB) is restarted.

d)Nothing happens, because there is no SPFILE for each PDB, the statement is ignored.

Question 54

You upgraded from a previous Oracle database version to Oracle Database 12c. Your database supports a mixed workload. During the day, lots of insert, update, delete operations are performed. At night, Extract, Transform, Load(ETL) and batch reporting jobs are run.  The ETL jobs perform certain database operations using two or more concurrent sessions.

After the upgrade, you notice that the performance of ETL jobs has degraded. To ascertain the cause of performance degradation, you want to collect basic statistics such as  the level of parallelism, total database time, and the number of I/O requests for the ETL jobs.

How do you accomplish this?

a)Examine the ASH reports for the time period of the ETL or batch reporting runs

b)Enable SQL tracing for the queries in the ETL and batch reporting queries and gather diagnostic data from the trace file

c)Enable real-time SQL monitoring for ETL jobs and gather diagnostic data from the V$SQL_MONITOR view

d)Enable real-time database operation monitoring using the DBMS_SQL_MONITOR.BEGIN_OPERATION function, and then use the DBMS_SQL_MONITOR.REPORT_SQL_MONITOR function to view the required information.

Question 55

Your database is open and the LISTENER listener running. You stopped the wrong listener LISTENER by issuing the following command.

lsnrctl> STOP

What happens to the session that are presently connected to the database instance?

a)They are able to perform only queries

b)They are not affected and continue to function normally

c) They are terminated and the active transactions are rolled back

d)They are not allowed to perform any operations until the listener LISTENER is started

Question 56

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 PRODCTS table?

a)The %ROWTYPE attribute declarations in PL/SQL to access a row will not display the invisible column in the output.

b)The DESCRIBE commands in SQL * Plus will not display the invisible column in the output.

c)Referential integrity constraint cannot be set on the invisible column

d)The invisible column cannot be made visible and can only be marked as unused

e)A primary key constraint can be added on the invisible column.

Question 57

A senior DBA asked you to execute the following command to improve the performance.

SQL> ALTER TABLE subscribe log STORAGE(BUFFER_POOL recycle);

You checked the data in the SUBSRIBE_LOG  table and found that it is a large table containing one million rows.

What could be a reason for this recommendation?

a)The keep pool is not configured.

b)Automatic workarea management is not configured

c)Automatic shared memory management is not enabled

d)The data blocks in the SUBSCRIBE_LOG table are rarely accessed

e)All the queries on the SUBSCRIBE_LOG table are rewritten to materialized view

Question 58

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 to recover the tablespace are as follows.

1.Mount the CBD

2.Close all the PDBs

3.Open the database

4.Apply the archive redo logs

5.Restore the data file

6.Take the SYSAUX tablespace offline

7.Place the SYSAUX tablespace online

8.Open all the PDBs with RESETLOGS

9.Open the database with RESETLOGS

10.Execute the command SHUTODWN 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

Question 59

You are administering a database stored in ASM.  The files are stored in the DATA disk group. You execute the following command :

SQL> ALTER DISKGROUP data ADD  ALIAS ‘+data/prod/myfile.dbf’ FOR ‘+data/prod/myfile.dbf’

What is the result?

a)The file ‘+data.231.54769’ is physically relocated to ‘+data/prod’ and renamed as  ‘myfile.dbf’

b)The file ‘+data.231.54769’ is renamed as ‘myfile.dbf’, and copied to ‘+data/prod’

c)The file ‘+data.231.54769’ remains in the same location and a synonym ‘myfile.dbf’ is created.

e)The file ‘+data.231.54769’ is created in ‘+data/prod’ and the reference to ‘+data.231.54769’ in the data dictionary removed.

Question 60

Identify there scenarios in which you would recommend the use of SQL performance Analyzer to analyze impact on the performance of SQL statements.

a)Change in the Oracle database version

b)change in your network infrastructure

c)Change in the hardware configuration of the database server

d)Migration of database storage from non-ASM to ASM storage

e)Database and operating system upgrade

Question 61
You created an encrypted tablespace.

You then closed the encryption wallet because you were advised that this is secure.
Later in the day, you attempt to create the EMPLOYEES table in the SECURESPACE tablespace with the SALT option on the EMPLOYEE column.

Which is true about the result?

a)It creates the table successfully but does not encrypt any inserted data in the EMPNAME column because the wallet must be opened to encrypt columns with SALT.
b) It generates an error when creating the table because the wallet is closed
c) It creates the table successfully, and encrypts any inserted data in the EMPNAME column because the wallet needs to be open only for tablespace creation.
e)It generates error when creating the table, because the salt option cannot be used with encrypted tablespaces.

Question 62

What are three purpose of the RMAN "FROM" clause?

a)To support PUSH-based active database duplication

b)To support synchronization of a standby database with the primary database in Data Guard Environment

c)To support PULL-based active database duplication

d)To support file restores over the network in a Data Guard environment

e)To support file recovery over the network in a Data Guard environment.

Question 63

You performed an incremental level 0 backup of a database.

RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE;

To enable block change tracking after the incremental level 0 backup, you issued this command:

SQL>ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE ‘/mydir/rman_change_track.f’;

To perform an incremental level 1 cumulative backup, you issued this command:

RMAN>BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

Which three statements are true?

a)Backup change tracking will sometimes reduce I/0 performed during cumulative incremental backups

b)The change tracking file must always be backed up when you perform a full database backup

c)Block change tracking will always reduce I/0 performed during cumulative incremental backups

d)More than one database block may be read by an incremental backup for a change made to single block.

e)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.

Question 64

Your multitenant container database (CBD) contains pluggable database(PDBs), you are connected to the HR_PDB. You execute the following command :

SQL> CREATE UNDO TABLESPACE undotb01

DATAFILE ‘/u01/oracle/rddb1/undotbs01.dbf’  size 60m AUTOEXTEND ON;

What is the result?

a)It executes successfully and creates an UNDO tablespace in HR_PDB

b)It fails  and reports and error because there can be only one undo tablespace in CDB

c)It fails  and reports and error because the CONTAINER=ALL clause is not specified in the command.

d) It fails  and reports and error because the CONTAINER=CURRENT  clause is not specified in the command.

e) It executes successfully but neither tablespace not the data file is created.

Question 65

In your multitenant container database(CDB) containing pluggable database(PDBs), you granted the CREATE TABLE privilege to the common user  C##A_ADMIN in root and all PDBs. 

You execute the following command from the root container.

SQL>REVOKE create table FROM C##A_ADMIN;

What is the result?

a)It executes successfully and the CREATE TABLE privilege is revoked from C##A_ADMIN in root only.

b)It fails  and reports an error because the CONTAINER=ALL clause is not used.

c)It excludes successfully and the CREATE TABLE privilege is revoked from  C##A_ADMIN in root and all PDBs.

d)It fails and reports an error because the CONTAINER=CURRENT clause is not used.

e)It executes successfully and the CREATE TABLE privilege is revoked from C##A_ADMIN in all PDBs.

Question 66

Which three statements are true about Flashback Database?

a)Flashback logs are written sequentially, and are archived.

b) Flashback Database uses a restored control file to recover the database.

c)The oracle database automatically creates, deletes, and resides flashback logs in the fast recovery area.

d) Flashback database can recover a database to the state that it was in before a reset logs operation.

e) Flashback Database can recover a data file that was dropped during the span of time of the flashback.

f)Flashback logs are used to restore to the blocks before image, and then the redo data may be used to roll forward to the desired flashback time

Question 67

In your database, you want to ensure that idle sessions that are blocking active are automatically terminated after a specified period of time.

How would you accomplish this?

a)Setting a metric threshold

b)Implementing Database resource manager

c)Enabling resumable timeout for user sessions

d)Decreasing the value of the IDLE_TIME resource limit in the default profile.

Question 68

Examine the following parameters for a database instance:

MEMORY_MAX_TARGET=0

MEMROY_TARGET=0

SGA_TARGET=0

PGA_AGGREGATE_TARGET=500m

Which three initialization parameters are not controlled by ASMM?

a)LOG_BUFFER

b)SORT_AREA_SIZE

c)JAVA_POOL_SIZE

d)STREAMS_POOL_SIZE

e)DB_16K_CACHE_SIZE

f)DB_KEEP_CACHE_SIZE

Question 69

Your multitenant container database(CDB) is running in ARCHIVELOG mode. You connect to the CBD RMAN.

Examine the following command and its output.

You execute the following command.

RMAN>BACKUP DATABASE PLUS ARCHIVELOG;

Which data files will be backed up?

a)Data files that belong to only the root container

b)Data files that belong to the root container and all the pluggable database(PDBs)

c)Data files that belong to only the root container and PDB$SEED

d)Data files that belong to the root container and all the PDBs excluding PDP$SEED

Question 70

You executed a DROP USER CASCADE on an Oracle11g release 1 database and immediately realized that you forgot to copy the OCA.EXAM_RESULT table to the OCP schema.

The RECYCLE_BIN enabled before the DROP USER was executed and the OCP user has been granted the FLASHBACK ANY TABLE system privilege.

What is the quickest way to recover the contents of the OCA.EXAM_RESULTS table to the OCP schema?

a)Execute FLASHBACK TABLE OCA.EXAM_RESULTS TO BEFORE DROP RENAME TO OC.EXAM_RESULTS; connect as SYSTEM.

b)Recover the table using traditional Tablespace point in time recovery.

c)Recover the table using Automated tablespace point in time recovery

d)Recover the table using Database Point in Time Recovery

e)Execute FLASHBACK TABLE OCA.EXAM_RESULTS TO BEFORE DROP RENAME TO EXAM_RESULTS; connected as a OCP user

Question 71

In your multitenant container database(CDB) containing pluggable database(PDBs), the HR user executes the following commands to create and grant privileges on a procedure:

CREATE OR REPLACE PROCEDURE create_test_v(v_emp_id NUMBER, v_ename VARCHAR2, v_salary number, v_dept_id number)

BEGIN

INSERT INTO hr.test values(v_emp_id,v_ename,v_salary, v_dept_id);

END;

/

GRANT EXECUTE ON CREATE_TEST TO john, jim, smith, king;

How can you prevent users having the EXECUTE privilege on the CREATE_TEST procedure from inserting values into tables on which

they do not have any privileges?

a)Create the CREATE_TEST procedure with definer’s rights

b)Grant EXECUTE privilege to users with GRANT OPTION on the CREATE_TEST procedure

c)Create the CREATE_TEST procedure with invoker’s rights

d)Create the CREATE_TEST procedure as part of a package and grant users the EXECUTE privilege the package

Question 72

You notice a performance change in your production Oracle12c database. You want to know which change caused this performance difference.

Which method or feature should you use?

a)Compare Period ADDM report

b)AWR Compare period report

c)Active Session History(ASH) report

d)Taking a new snapshot and comparing it with preserved snapshot

Question 73

The tnsnames.ora file has an entry for the service alias ORCL as follows.

The TNS ping command executes successfully when tested with ORCL; however, from the same OS user session, you are not able to connect to the database instance with the following command.

SQL> CONNECT scott/tiger@orcl

What could be the reason for this?

a)The listener is not running on the database node

b)The TNS_ADMIN environment variable is set to the wrong value

c)The orcl.oracle.com database service is not registered with the listener.

d)The DEFAULT_DOMAIN parameter is set to the wrong value in the sqlnet.ora file

e)The listener is running on a different port

Question 74

You administer an OLTP system whose database is stored in ASM and whose disk group use normal redundancy.  

One of the ASM disk goes offline, and is then dropped because it was not brought online before DISK_REPAIR_TIME elapsed.

When the disk is replaced and added back to the disk group, the ensuing rebalance operation is too slow.

Which two recommendations should you make to speed up the rebalance operation if this type of failure happens again?

a)Increase the value of the ASM_POWER_LIMIT parameter

b)Set the DISK_REPAIR_TIME disk attribute to a lower value

c)Specify the statement that adds the disk back to the disk group

d)Increase the number of ASMB processes

f)Increase the number of DBWR_IO_SLAVES in the ASM instance.

Question 75

You are required to migrate your 11.2.0.3 databases as a pluggable database(PDB) to a multitenant container database(CBD).

The following are the possible steps to accomplish this task

1. Place all the user-defined tablespace in read-only mode on the source database
2. Upgrade the source database to a 12c version
3. Create a new PDB in the target container database
4. Perform a full transportable export on the source database with the VERSION parameter set to 12 using the expdp utility.
5. Copy the associated data files and export the dump file to the desired location in the target database.
6. Invoke the Data Pump import utility on the new PDB database as a user with DATAPUMP_IMP_FULL_DATABASE role and specify the full transportable import options
7. Synchronize the PDB on the target container database by using the DBMS_PDS.SYNC_ODB function

Identify the correct order of the required steps.

a)2, 1, 3, 4, 5, 6

b)1, 3, 4, 5, 6, 7

c)1, 4, 3, 5, 6, 7

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

e)1, 5, 6, 4, 3, 2

Question 76

You execute the following commands to audit database activities:

SQL> ALTER SYSTEM SET AUDIT_TRIAL=DB, EXTENDED SCOPE=SPFILE;

SQL>AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE BY JOHN by SESSION WHENEVER SUCCESSFUL;

Which statement is true about the audit record that generated when auditing after instance restarts?

a)One audit record is created for every successful execution of a SELECT, INSERT OR DELETE command on a table, and contains the SQL text for the SQL statement.

b) One audit record is created for every successful execution of a SELECT, INSERT OR DELETE command, and contains the execution plan for the SQL statements.

c) One audit record is created for the whole session if john successfully executes a SELECT, INSERT or DELETE  command, and contains the execution plan for the SQL statements.

d) One audit record is created for the whole session if JOHN successfully executes a select command, and contains the SQL text and bind variables used.

e)One audit record is created for the whole session if john successfully executes a SELECT, INSERT or DELETE commands  on a table, and contains the execution plan, SQL text, and bind variable used.

Question 77

Which three statements are true concerning the multitenant architecture?

a)Each pluggable database(PDB) has its own set of background processes

b)A PDB’s can have a private temp tablespace

c)PDBs can share the sysaux tablespace

d)Log switches occur only at the multitenant container database(CDB) level

e)Different PDBs can have different default block sizes

f)PDBs share a common system tablespace

g)Instance recovery is always performed at the CBD level

Question 78

Which three operations can be performed as multi partition operations in Oracle?

a)Merge partitions of a list partitioned tables

b)Drop partitions of a list partitioned tables

c)coalesce partition of a hash-partitioned global index

d)Move partitions of a range partitioned tables

e) Rename partitions of a range partitioned tables

f)Merge partitions of a reference partitioned index

Question 79

In your multitenant container database(CDB) containing same pluggable databases(PDBs), you execute the following commands in the Root container.

Which two statements are true?

a)The C##ROLE1 role is created in the root database and all the PDBs.

b)The C##ROLE1 role is created only in the root database because the container clause is not used.

c)Privilege are granted to the C##A_ADMIN user only in the root database

d)Privilege are granted to the C##A_ADMIN user in the root database and all PDBs

e)The statement for granting a role to user fails because the CONTAINER clause is not used

Question 80

Examine the following steps of privilege analysis for checking and revoking excessive, unused privileges granted to users:

1)Create a policy to capture the privilege used by a user for privilege analysis

2)Generate a report with the data captured for a specified privilege capture

3)Start analyzing the data captured by the policy

4)Revoke the unused privileges

5)Compare the used and unused privilege’s lists

6)Stop analyzing the data

Identify the correct sequence of steps:

a)1, 3, 5, 6, 2, 4

b)1, 3, 6, 2, 5, 4

c)1, 3, 2, 5, 6, 4

e)1, 3, 5, 2, 6, 4

Question 81

Which two statements are true about the Oracle Direct Network file system(DNFS)?

a)It utilize the OS file system cache

b)A Traditional NFS mount is not required when using Direct NFS

c)Oracle Disk Manger can manage NFS on its own, without using the operating kernel NFS driver

d)Direct NFS is available only in UNIX platforms

e)Direct NFS can load-balance I/O traffic across multiple network adapters.

Question 82

You find this query being used in your Oracle 12c database

Which method a used by the optimizer to limit the rows being returned?

a)A filter is added to the table query dynamically using ROWNUM to limit the rows to 20 percent of the total rows

b)All the rows are returned to the client or middle tier but only the first 20 percent are returned to the screen or the application

c) A view is created during execution and a filter on the view limits the rows to 20 percent of the total rows

d)A TOP-N query is created to limit the rows to 20 percent of the total rows

Question 83

You are connected using SQL*plus to a multitenant container database(CBD) with SYSDBA privilege and execute the following sequence statements :

What is the result of the last SET CONTAINER statement and why is to so?

a)It succeeds because the PDB_ADMIN user has the required privileges

b)It fails because common users are unable to use the SET CONTAINER statement

c)It fails because local users are unable to use the SET CONTAINER statement

Question 84

You are administering a database and you receive a requirement to apply the following restrictions:

1)A connection must be terminated after four unsuccessful login attempts by user

2)A user should not be able to create more than four simultaneous sessions

3)User session must be terminated after 15 minutes of inactivity

4)Users must be prompted to change their passwords every 15 days

How would you accomplish these requirements?

a)By granting a secure application role to the users

b)By creating and assigning a profile to the users and setting the REMOTE_OS_AUTHENT parameter to FALSE

c)By creating and assigning a profile to the users and setting the SEC_MAX_FAILED_LOGIN_ATTEMPTS parameter to 4

d)By Implementing Fine-Grained Auditing(FGA) and setting the REMOTE_LOGIN_PASSWORD_FILE parameter to NONE

e)By Implementing the database resource manager plan and setting the SEC_MAX_FAILED_LOGIN_ATTEMPTS parameter to 4

Question 85

You support Oracle Database 12c Oracle Database 11g, and  Oracle database log on the same server.

All database of all versions use ASM.

Which three statements are true about the ASM disk group compatibility attributes that are set for disk group?

a)The ASM compatibility attribute controls the format of the disk group metadata

b)RDBMS compatibility together with the database version determines whether a database instance can mount the ASM disk group

c)The RDBMS compatibility setting allows only database set to the same version as the compatibility value, to mount the ASM disk group

d)The ASM compatibility attribute determines some of the ASM features that may be used by the Oracle disk group

e)The ADVM compatibility attribute determines the ACFS features that may be used by the oracle10g database

Question 86

In a recent AWR report for your database, you notice high number of buffer busy waits. The database consists of locally managed tablespace with free list managed segments.

On further investigation, you find that buffer busy waits is caused by contention on data blocks.

What option would you consider first to decrease the wait event immediately?

a)Decreasing PCTUSED

b)Decreasing PCTFREE

c)Increasing the number of DBWR process

d)Using Automatic segment space management(ASSM)

e)Increasing db_buffer_cache based on the V$DB_CACHE_ADVICE recommendation

Question 87

In order to exploit some new storage tiers that have been provisioned by a storage administrator, the partitions of large heap table must be moved to other tablespaces in your Oracle12c database?

Both local and global partitioned B-tree indexes are defined on the table

A high volume of transactions access the table during the day and a medium volume of transactions access at night and during weekends.

Minimal distruption availability is required.

Which three statements are true about this requirement?

a)The partitions can be moved online to new tablespace

b)Global indexes must be rebuilt manually after moving the partitions

c)The partitions can be compressed in the same tablespaces

d)The partitions can be compressed in the new tablespaces

e)Local indexes must be rebuilt manually after moving the partitions

Question 88

To implement Automatic Memory Management (AMM), you set the following parameters.

When you try to start the database instance with these parameter settings, you receive the following error message:

SQL> startup

ORA-00824: cannot set SGA_TARGET or MEMROY_TARGET due to existing internal settings, see alert log for more information.

Identify the reason the instance failed to start.

a)The PGA_AGGREGATE_TARGET parameter is set to zero

b)The STATISTICS_LEVEL parameter is set to BASIC

c)Both the SGA_TARGET and MEMORY_TARGET parameters are set

d)The SGA_MAX_SIZE and SGA_TARGET parameter values are not equal

Question 89

The redaction policy was added to the SAL column of the SCOTT.EMP table

All users have their default set of system privileges.

For which three situations will data not be redacted?

a)SYS sessions, regardless of the roles that are set in the session

b) SYSTEM sessions, regardless of the roles that are set in the session

c)SCOTT sessions, only if the MGR role is set in the session

d)SCOTT sessions, only if the MGR role is granted to SCOTT

e)SCOTT sessions, because he is the owner of the table

f)SYSTEM session, only if the MGR role is set in the session

Question 90

Examine the following impdp command to import a database over the network from a pre-12c oracle database(source):

Which three are prerequisites for successful execution of the command?

a)The import operation must be performed by a user on the target database with the DATAPUMP_IMP_FULL_DATABASE role, and the database link must connect to a user on the source database with the DATAPUMP_EXP_FULL_DATABASE role.

b)All the user-defined tablespaces must be in read-only mode on the source database

c)The export dump file must be created before starting the import on the target database

d)The source and target database must be running on the same platform with same endianness

e)The path of data files on the target database must be  the same as that on the source database

f)The impdp operation must be performed by the same user that performed the expdp operation

Question 91

You install a non-RAC oracle database, during installation, the oracle universal installer(OUI) prompts you to enter the path of the inventory directory and also to specify an operating system group name.

Which statement is true?

a)The ORACLE_BASE base parameter is not set

b)The installation is being performed by the root user

c)The operating system group that is specified should have the root user as its member

d)The operating system group that is specified must have permission to write to the inventory directory.

Question 92

Identify two situations in which the alert log file is updated?

a)Running a query on a table returns ORA-600: Internal Error.

b)Inserting a value into a table returns ORA-01722: Invalid number

c)Creating a table returns ORA-00955: name is already in used by an existing objects

d)Inserting a value into table returns ORA-00001: unique constraint(SYS.OK_TECHP) violated

e)Rebuilding an index using ALTER INDEX…REBUILD fails with an ORA-01578: ORACLE data block corrupted(ifle#14, block#50) error

Question 93

Which statement is true concerning dropping a pluggable database(PDB)?

a)The PDB must be open in read only mode

b)The PDB must be in mount state

c)The PDB must be unplugged

d)The PDB data files are always removed from disk

e)A dropped PDB can never be plugged back into a multitenant container database(CDB)

Question 94

Your multitenant container database(CDB) contains three pluggable database(PDB). 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?

a)Mount the container database(CBD) and restore the control file from the control file auto backup

b)Recover and open the CDB in NORMAL mode

c)Mount the CDB and then recover and open the database, with the RESETLOGS option

d)Open all the pluggable database

e)Recover each pluggable database

f)Start the database instance in the nomount stage and restore the control file from control file auto backup.

Question 95

In which two scenarios do you use SQL* Loader to load data?

a)Transform the data while it is being loaded into the database

b)Use Transparent parallel processing without having to split the external data first

c)Load data into multiple tables during the same load statement

d)Generate unique sequential key values in specified columns

Question 96

You use the segment advisor to help determine objects for which space may be reclaimed.

Which three statements are true about the advisor given by the segment advisor?

a)It may advise the use of online table redefinition for tables in dictionary managed tablespace

b)It may advise the use of segment shrink for tables in dictionary managed tablespace it the no chained rows

c)It may advise the use of online table redefinition for tables in locally managed tablespace

d)It will detect and advise about chained rows

e)It may advise the use of segment shrink for free list manage tables

Question 97

You enabled an audit policy by issuing the following statements:

SQL> AUDIT POLICY ORA_DATABASE_PARAMETER BY SCOTT;

SQL> AUDIT POLICY ORA_DATABASE_PARAMETER BY SYS,SYSTEM;

For which database users and  for which executions is the audit policy now active? Select Two.

a)SYS,SYSTEM

B)SCOTT

c)Only for successful exections

d)Only for failed executions

e)Both successful and failed executions

Question 98

What is the effect of specifying the “ENABLE PLUGGABLE DATABASE” clause in a “CREATE DATABASE” statement?

a)It will create a multitenant container database(CBD) with only the root opened

b)It will create a CDB with root opened and seed read only

c)It will create a CDB with root and seed opened and one PDB mounted

d)It will create a CDB that must be plugged into an existing CDB

e)It will create a CDB with root opened and seed mounted.

Question 99

You execute the following command to create password file in the database server.

$orapwd file = ‘+DATA/PROD/orapwprod entries = 5 ignorecase = N format = 12’

Which two statements are true about the password file?

a)It records the usernames and passwords of users when granted the DBA role.

b)It records the username and passwords of users for whom auditing is enabled

c)Is used by Oracle to authenticate users for remote database administration

d)It records the usernames and passwords of all users when they are added to the OSDBA or OSOPER operating system groups

e) It supports the SYSBACKUP, SYSDG and SYSKM system privileges

Question 100

Which three statements are true when the listener handles connection request to an Oracle 12c database instance with multithreaded architecture enabled in UNIX?

a)Thread creation must be routed through a dispatcher process

b)The local listener may spawn a new process and have that new process creates a thread

c)Each Oracle process runs an SCMN thread

d)Each multithreaded oracle process has an SCMN thread

e)The local listener may pass the request to an existing process which in turn will create a thread

How to Bounce Oracle11g Active Data Guard Standby Database?

This post is tested in Oracle 11.2.0.4 Active Data Guard instance.  This step works on 11.1.0.7, 11.2.0.2, 11.2.0.4 as well.  Let us assume, we have primary and standby database and we need to shutdown the standby database for maintenance schedule.


Step1
Tail the alert log on  both primary and standby for monitoring the database.
tail -f  alert_$ORACLE_SID.log

Defer  the log shipping in primary database.
Login to primary database and run the below command to defer the log shipping. Assuming, log_archive_dest_state_2 is standby location.

alter system set log_archive_dest_state_2='DEFER' scope=both;


Step2
Login to standby database and shutdown the database.

alter database recover managed standby database cancel;
shutdown immediate

After the maintenance schedule is over, then bring the standby instance.

Step3
Login to primary instance and enable the log shipping.

alter system set log_archive_dest_state_2='ENABLE' scope=both;

Step4
Login to standby instance, run the below command.

startup nomount;
alter database mount standby database;
alter database recover managed standby database disconnect from session;

Leave the standby instance for few min to catch up the logs and run the rest of the commands.

alter database recover managed standby database cancel;
alter database open;
alter database recover managed standby database using current logfile disconnect;

Check the alert logs on the both primary and standby and make sure no errors.

Please remember, It will be different steps to bounce the Oracle10g  standby database. Since Oracle10g is not an active Data Guard.  Please click here to view the steps to bounce the Oracle10g data guard standby database.

Memory Parameters in oracle9i

Oracle9i new features on Memory Management :

1. Oracle9i introduced new parameter PGA_AGGREGATE_TARGET to tune portion of the memory in PGA. This new parameter dynamically adjust sort_area_size, hash_area_size, create_bitmap_area_size and bitmap_merge_area_size. The WORK_AREA_SIZE_POLICY should be set to AUTO to enable this feature.

2. Prior to oracle9i, shared pool size was static parameter. DBA has to restart the instance if shared pool size parameter needs to be changed. But in oracle9i introduced a new capability to change the shared_pool_size parameter dynamically without restarting the instance. But this parameter size should be less than or equal to SGA_MAX_SIZE.

3. Oracle9i introduced new approach to determine how the buffer cache is being using and how much memory has to be added or released from the buffer cache for optimal performance. Here are the steps to enable to feature.

a) Set the DB_CACHE_ADVICE parameter to ON. It gather statistics on the buffer cache by setting the value of this parameter. This is dynamic paramter and can be changed by using ALTER SYSTEM command.

b) Display the statistics gathered by querying the new dynamic performance view, V$DB_CACHE_ADVICE. This view contains information about the physical reads for the different cache sizes.

select id, name, block_size,buffers_for_estimate, estd_physical_reads, estd_physical_read_factor from v$db_cache_advice

c) We can change the buffer cache size based on the observation in step b.

The source of this article is click

Here are other related link to this topic.

Oracle10g Memory feature
Oracle11g Memory feature
Oracle Memory components

Memory Parameter in Oracle10g

Oracle10g new feature on Memory Management :

Oracle10g introduced memory management feature to make DBA's life much easy. Automatic shared memory management(ASMM) is another self management enhancement in oracle10g. In previous release of oracle, we had to manually configure the shared pool size, java pool size, large pool size and data base buffer cache. It was often challenge to optimally configure these components because sizing them too small could cause memory errors and sizing them too large could lead to waste of memory.

In oracle10g, you need to specify only the SGA_TARGET parameter, which specifies the total size of the SGA. Individual components of the SGA are automatically allocated by the database based on the workload and history information. so during the normal online operations,the buffer cache and java pool may be bigger. During the batch window, the database can automatically increase the large pool and reduce the buffer cache.

When ASMM is enabled, then the following memory pools are automatically sized:

1 .Buffer cache (DB_CACHE_SIZE)
2. Shared pool (SHARED_POOL_SIZE)
3. Large pool (LARGE_POOL_SIZE)
4. Java pool (JAVA_POOL_SIZE)

If above automatically tuned memory pools are set to non-zero values, then those values are used as minimum levels by Automatic Shared Memory Management. You would set minimum values if an application component needs a minimum amount of memory to function properly.

The following pools are manually sized components and are not affected by ASMM.

1. DB_KEEP_CACHE_SIZE
2. DB_RECYCLE_CACHE_SIZE
3. DB_nK_CACHE_SIZE (non-default block size)
3. STREAMS_POOL_SIZE
4. LOG_BUFFER

For instance, SGA_TARGET is 400MB, LOG_BUFFER is 1M, DB_KEEP_CACHE_SIZE is 50M, then the memory available for automatically configured components is 349MB.

Questions and Answers :

1. How does ASMM work? ASMM automatically allocate the memory based on the work load and type of DB activities. We do not need to size the SGA parameters. The sga_target parameter will not dynamically manage all the sub component of the SGA memory. Some of the memory areas need to be sized by DBA as i explained above.

2. How do we switch the DB from non-ASMM to ASMM? Switching to ASSM can be done by changing SGA_TARGET parameter to non-zero value. STATISTICS_LEVEL should be TYPICAL or ALL. We need to allocate proper memory size for SGA_TARGET parameter. But this should not be greater then SGA_MAX_SIZE. Again, we need to reset the automatically tunable memory parameter to zero or minimum values. If we set minimum values for automatically tuned parameters, then oracle always maintain the minimum values all the time.

Here my DB is running in Non-ASMM. Let us switch this to ASMM.

SQL> select name,value from v$parameter where
2 name in('shared_pool_size','large_pool_size','java_pool_size','db_cache_size','sga_target');

NAME VALUE
------------------------------ ------------------------------
shared_pool_size 163577856
large_pool_size 4194304
java_pool_size 4194304
db_cache_size 432013312
sga_target 0

SQL> alter system set sga_target=1000M scope=both;

System altered.

SQL> alter system set shared_pool_size=500M scope=both;

System altered.

SQL> show sga

Total System Global Area 1048576000 bytes
Fixed Size 1252976 bytes
Variable Size 553648528 bytes
Database Buffers 486539264 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.

SQL> select target_size from v$sga_resize_ops
2 where component='shared pool';

TARGET_SIZE
-----------
528482304

SQL>

Now SGA parameters will be automatically tuned based on the workload and DB activities. But it always retain the minimum values, since we set shared_pool_size, large_pool_size, java_pool_size, db_cache_size set to non-zero values.

3. How do we disable ASMM? Disabling ASSM can be done by chaning SGA_TARGET parameter to zero value. But we need to set the below parameter with proper sizing when we disable ASMM

1. shared_pool_size
2. large_pool_size
3. java_pool_size
4. db_cache_size

SQL> alter system set sga_target=0 scope=both;

System altered.

4. When do we think that, ASMM should be disabled in oracle10g? We can disable ASMM if there is significant amount of memory resizing activity that cause the performance issue in database. This frequent memory resizing might happen when the environment is mixed of DSS and OLTP environment.

5. What is the best environment to enable the ASMM feature? Based on my knowledge, i would recommend to go for ASMM feature when DB is running on OLTP mode during the day time and DB is running on DSS more during the night time. In this scenario, ASMM feature will resize the memory based on the DB activities. DBA does not need to resize the memory for day time and night time differently.

Here are the relevant link to this topic.

Oracle9i Memory feature
Oracle11g Memory feature
Oracle Memory components

Memory Parameter in Oracle11g

Oracle is enhancing the memory management in each version starting from oracle9i. Oracle9i introduced pga_aggregate_target to dynamically manage the memory for PGA. Oracle went one step further in 10g and introduced sga_target parameter to dynamically allocate the SGA memory. Oracle introduced memory_target parameter in oracle11g and dynamically allocate both SGA and PGA memory. This is one of exciting features that takes care of SGA as well as PGA in one parameter. We let Oracle to adjust the memory as the workload needs it.

I would like to write how this memory parameter works in oracle11g. How the memory is configured on each sub component when memory_target parameter is set to non zero?

Oracle11g introduced two memory parameters(memory_target, memory_max_target). oracle has become smart as in exchanging memory between SGA and PGA. This feature helps DBA to allocate chunk of memory to a particular instance without worrying about the subcateogary allocations of different components.

Question and Answers :

1. What is the advantage of memory_target parameter? I do not see any reason to not to use this memory management parameter. If we set this parameter, then we do not need to size all other sub component of this parameter. Sub component parameters are like SGA_TARGET, PGA_AGGREGATE_TARGET etc. These memory area can be managed dynamically.

2. What is the best place to leverage this feature? If your database running in mixed load, Oracle will adjust all your memory setting accordingly to DB load. Let us say, your database is running on OLTP mode in the day time, and running batch job during the night time. DBA can not adjust the different component of the memory according to Database activity. This memory_target parameter balance the memory between shared_pool_size, redolog_buffer, large_pool_size, java_pool_size etc. This is ideal scenario to use this feature.

3. How do we enable AMM feature ? This feature can be enabled by setting memory_target and max_memory_target to non-zero values. LOCK_SGA parameter should be set to FALSE to enable this feature.

4. How do we disable AMM feature ? This feature can be disabled by setting zero for memory_target parameter. Please remember, sga_target, pga_aggregate_target parameter should be sized according to DB activities.

5. Do we need to check the Shared memory in server before implementing AMM? Yes. We need to check the current size of your shared memory file system before we set the AMM feature. On linux, we need to use the below command to check the shared memory.

[oracle@rate9812~]$ df -k /dev/shm

Filesystem 1k-blocks Used Available Use% Mounted on tmpfs

2023256 1065000 958256 53% /dev/shm

[oracle@rate9812~]$

Here the shared memory is around 2GB. So the memory target can be more than 2GB. If it is more than 2GB, the we get the below error.

ORA-00845: MEMORY_TARGET not supported on this system

6. Do we have any new data dictionary views to monitor AMM? Yes. we do have new views. Here are some. These views helps to monitor the Memory utilization.

V$MEMORY_CURRENT_RESIZE_OPS
V$MEMORY_DYNAMIC_COMPONENTS
V$MEMORY_RESIZE_OPS
V$MEMORY_TARGET_ADVICE

6. How does this feature differ from Oracle10g memory feature? This parameter covers both SGA and PGA. In Oracle10g, we use individual parameter for SGA and PGA. Now it is not required to define or size the memory for SGA and PGA seperately.

7. Do we need to take care of any memory parameters after AMM feature is enabled? Log buffer and the non-default block size caches not part of dynamic memory allocation. These memory should be taken care manually.

The new data dictionary V$MEMORY_DYNAMIC_COMPONENTS will give the list of components which is taken care by database when we enable AMM feature.

Here are relevant topic to this article.

Oracle9i Memory feature
Oracle10g Memory feature
Oracle Memory components

Oracle Memory Management

It has been a few months since i added a new post in my blog. I moved to Austin, TX last December 2009 and since that time, i have been busy with my new job, settling down with new place, buying new home etc. I am going to discuss about Oracle Memory components and their function of each component. I am writing this article based on my experience, reading oracle websites, books and other blogs.

First let me explain about each memory component in oracle. Oracle Memory is divided into two portion. One is System Global Area(SGA) and another one is Program Global Area(PGA). Let us now discuss about SGA now. Then we will talk about PGA in the later portion of this article.

System Global Area consists of following Memory component.

1. Fixed size
2. Variable size
3. DB Buffer cache
4. Redo log buffer

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning option

SQL> show sga

Total System Global Area 1654013952 bytes
Fixed Size 2103048 bytes
Variable Size 1342179576 bytes
Database Buffers 301989888 bytes
Redo Buffers 7741440 bytes
SQL>

SGA = Fixed size + Variable size + DB buffers + Redo buffers

What is Fixed size?

The size of the fixed portion is constant for a release and a platform of Oracle, that is, it cannot be changed through any means such as altering the initialization parameters. This memory varies in size from platform to platform and release to release. This is something over which we have no control and it is generally very small. Think of this area as a bootstrap section of the SGA, something Oracle uses internally to find the other bits and pieces of the SGA.

What is Variable size?

Variable size is sum of shared pool, java pool, large pool and streams pool. The streams pool is added in oracle10g.

Shared pool = library cache + dictionary cache

Variable size = Shared pool + Java pool + large pool + streams pool

Let us briefly talk about each component in variable size.

Shared pool : It consists of Library cache and Dictonary cache.

The library cache stores shared SQL, caching the parse tree and the execution plan for every unique SQL statement. If multiple applications issue the same SQL statement, each application can access the shared SQL area. This reduces the amount of memory needed and reduces the processing-time used for parsing and execution planning.

The dictionary cache stores dictionary information in order to parse the SQL statement. It also contains user information, integrity constraints defined for tables etc.

Java pool : It is used for caching parsed java programs.

Large pool : is used in shared/MTS server systems for session memory, by parallel execution for message buffers, and by RMAN backup processes for disk I/O buffers.

Stream pool : is used for streams.

What is DB buffer cache? It stores the recently executed database blocks. When other user fires the same query, oracle reads from buffer cache instead of accessing physical files.

What is Redo log buffer? It stores all the changes made in the database. It will be stored in archive log files for recovery purpose.

Please click to know more info about SGA

Program Global Area(PGA) A PGA is allocated by Oracle when a user connects to an Oracle database and a session is created. PGA contains session information and Private SQL area.

Content of PGA :

1. Session information. For instance, Logon info, session related info.

2. Private SQL area. Please remember, this is part of PGA only if the DB is dedicated server. This memory will be part of SGA if the DB is shared server. Private SQL area contains informatoin about the cursor, query execution status info, sql work area(sort area). Please click here to know more about PGA memory.

Oracle keep introducing new features in the memory management side to make DBA's life easy.

Let us talk about new oracle memory management features in each new version since from Oracle9i.

1. Oracle9i Feature

2. Oracle10g Feature

3. Oracle11g Feature