Here is
the part 2 question and answer
26)
Examine
the following query output:
You
issue the following command to import tables into the hr schema:
$ > impdp hr/hr directory = dumpdir dumpfile = hr_new.dmp schemas=hr
TRANSFORM=DISABLE_ARCHIVE_LOGGING: Y
Which statement is true?
A.
All database operations performed by the impdp command are logged.
B.
Only CREATE INDEX and CREATE TABLE statements generated by the import are
logged.
C.
Only CREATE TABLE and ALTER TABLE statements generated by the import are
logged.
D.
None of the operations against the master table used by Oracle Data Pump to
coordinate its activities are logged.
Correct
Answer:- C
Notes:-
Datapump
Import impdp in 12c includes a new parameter to disable logging during
data import. This option could improve performance of import tremendously
during large data
loads.
The TRANSFORM=DISABLE_ARCHIVE_LOGGING is used to disable logging. The value can
be
Y or N. Y to disable logging and N to enable logging.
However, if the database is running with FORCE LOGGING enabled, data pump
ignores disable
logging request.
when you
database is configured with FORCE LOGGING=N , Even if you specified this
parameter during import there is still redo logging for other operations
of Oracle Data Pump. This includes all CREATE and ALTER statements, except
CREATE INDEX, and all operations against the master table used by Oracle Data
Pump during the import.”
learn
from here-->
http://db12c.blogspot.de/2013/06/using-new-oracle-database-12c-feature.html
--------------------------------------------------------------------------------------------------------------------------------------------------
27)
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, bettor 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.
Correct
Answer:- A,D and E
--------------------------------------------------------------------------------------------------------------------------------------------------
28)
You create a new pluggable database, HR_PDB, from the seed database. Which
three
tablespaces are created by default in HR_PDB?
A.
SYSTEM
B.
SYSAUX
C.
EXAMPLE
D.
UNDO
E.
TEMP
F.
USERS
Correct
Answer:- A,B & E
Notes:-
select
CON_ID, NAME from v$containers where con_id=2;
select
tablespace_name from cdb_tablespaces where con_id=2;
--------------------------------------------------------------------------------------------------------------------------------------------------
29)
Which two statements are true about variable extent size support for large ASM
files?
A.
The metadata used to track extents in SGA is reduced.
B.
Rebalance operations are completed faster than with a fixed extent size
C.
An ASM Instance automatically allocates an appropriate extent size.
D.
Resync operations are completed faster when a disk comes online after being
taken offline.
E.
Performance improves in a stretch cluster configuration by reading from a local
copy of an
extent.
Correct
Answer:- A & C
Notes:-
Extents
The
contents of Oracle ASM files are stored in a disk group as a set, or
collection, of extents that are stored on individual disks within disk groups.
Each extent resides on an individual disk. Extents consist of one or more
allocation units (AU). To accommodate increasingly larger files, Oracle ASM
uses variable size extents.
Variable
size extents enable support for larger Oracle ASM data files, reduce SGA memory
requirements for very large databases, and improve performance for file create
and open operations. The initial extent size equals the disk group allocation
unit size and it increases by a factor of 4 or 16 at predefined thresholds.
This feature is automatic for newly created and resized data files when
specific disk group compatibility attributes are set to 11.1 or higher. For
information about compatibility attributes, see “Disk Group Compatibility”.
The
extent size of a file varies as follows:
Extent
size always equals the disk group AU size for the first 20000 extent sets (0 –
19999).
Extent
size equals 4*AU size for the next 20000 extent sets (20000 – 39999).
Extent
size equals 16*AU size for the next 20000 and higher extent sets (40000+).
--------------------------------------------------------------------------------------------------------------------------------------------------
30)
You
created a new database using the “create database” statement without specifying
the
“ENABLE PLUGGABLE” clause.
What are two effects of not using the “ENABLE PLUGGABLE database” clause?
A.
The database is created as a non-CDB and can never contain a PDB.
B.
The database is treated as a PDB and must be plugged into an existing
multitenant container
database (CDB).
C.
The database is created as a non-CDB and can never be plugged into a CDB.
D.
The database is created as a non-CDB but can be plugged into an existing CDB.
E.
The database is created as a non-CDB but will become a CDB whenever the first
PDB is
plugged in.
Correct
Answer:- A & D
--------------------------------------------------------------------------------------------------------------------------------------------------
31)
What is
the effect of specifying the “ENABLE PLUGGABLE DATABASE” clause in a “CREATE
DATABASE” statement?
A.
It will create a multitenant container database (CDB) 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.
Correct
Answer:- B
--------------------------------------------------------------------------------------------------------------------------------------------------
32) You
have installed two 64G flash devices to support the Database Smart Flash Cache
feature on
your database server that is running on Oracle Linux.
You have set the DB_SMART_FLASH_FILE parameter:
DB_FLASH_CACHE_FILE= ‘/dev/flash_device_1 ‘,’ /dev/flash_device_2’
How should the DB_FLASH_CACHE_SIZE be configured to use both devices?
A.
Set DB_FLASH_CACHE_ZISE = 64G.
B.
Set DB_FLASH_CACHE_ZISE = 64G, 64G
C.
Set DB_FLASH_CACHE_ZISE = 128G.
D.
DB_FLASH_CACHE_SIZE is automatically configured by the instance at startup.
Correct
Answer:- B
Notes:-
The
Smart Flash Cache feature is available on Solaris and Oracle Linux platforms.
Oracle Database 11g Release 2 Enterprise Edition allows you to use flash
devices to increase the effective size of the Oracle Database buffer cache
(Level 2 cache) without adding more main memory. This capability is referred to
as Database Smart Flash Cache.When the database requests data I/O, the system
first looks in the buffer pool. If the data is not found, the system then looks
in the Database Smart Flash Cache buffer. If it does not find the data there,
only then does it look in disk storage. The database Smart Flash Cache was
originally released as an Exadata feature. From 11.2.0.2 it was available for use
with any flash disks, not just Exadata storage.
Note that Exadata Smart Flash Cache functions quite
differently – it is a write-through cache that brings data into flash cache on
read-write disk operations, whereas Database Smart Flash Cache is a read-only
cache that brings data into flash cache on buffer cache replacement.
Implementation:-
The
Database Smart Flash Cache feature introduces two new init.ora parameters:
DB_FLASH_CACHE_FILE, which identifies the flash device
DB_FLASH_CACHE_SIZE,
which specifies the amount of flash storage available to the Database Smart
Flash Cache.
You can
specify up to 16 file sizes, for each of the flash memory devices specified
with DB_FLASH_CACHE_FILE. For example, if there are three flash raw devices,
you can specify the sizes of each device as follows:
db_flash_cache_file
= /dev/raw/sda, /dev/raw/sdb, /dev/raw/sdc
db_flash_cache_size = 32G, 32G, 64G
Information
about Smart Flash Cache usage is displayed using the V$FLASHFILESTAT view.
SELECT * FROM
v$flashfilestat;
The
default action is for blocks to be aged out of the buffer cache and into the
Smart Flash Cache. The FLASH_CACHE clause of the ALTER TABLE command allows additional control.
-- Prevent EMP
from using the Smart Flash Cache.
ALTER TABLE
scott.emp STORAGE (FLASH_CACHE NONE);
--------------------------------------------------------------------------------------------------------------------------------------------------
33)
Examine the following parameters for a database instance:
MEMORY_MAX_TARGET=0
MEMORY_TARGET=0
SGA_TARGET=0
PGA_AGGREGATE_TARGET=500m
Which three initialization parameters are not controlled by Automatic Shared
Memory
Management (ASMM)?
A.
LOG_BUFFER
B.
SORT_AREA_SIZE
C.
JAVA_POOL_SIZE
D.
STREAMS_POOL_SIZE
E.
DB_16K_CACHE_SZIE
F.
DB_KEEP_CACHE_SIZE
Correct
Answer:- A,E & F
--------------------------------------------------------------------------------------------------------------------------------------------------
34)
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.
Correct
Answer:-B
Notes:-
SQL>
revoke create table from c##test;
revoke create table from c##test
*
ERROR at line 1:
ORA-65092: system privilege granted with a different scope to ‘C##TEST’
SQL>
revoke create table from c##test container=all;
Revoke succeeded.
It’s
depend how the privilege was granted.
If you
give the privilege with:
grant create table to c##a_admin container=all;
You only
take back with:
revoke create table from c##a_admin container=all;
However if
you give the privilege at container and at each of pdbs without container=all
clause, you can remove the privilege only in cdb and A will be correct.
In the
case of this question, I would mark A, because of this sentence:
“you granted
the CREATE TABLE privilege to the common user C # # A_ADMIN in root and all
PDBs”
--------------------------------------------------------------------------------------------------------------------------------------------------
35) 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 exist in correct
locations
E.
Backup sets containing both data files and archive logs are created.
Correct
Answer:-B & D
--------------------------------------------------------------------------------------------------------------------------------------------------
36) Which
three statements are true concerning the multitenant architecture?
A.
Each pluggable database (PDB) has its own set of background processes.
B.
A PDB 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 CDB level.
Correct
Answer:- B, D & E
--------------------------------------------------------------------------------------------------------------------------------------------------
37)
You run a script that completes successfully using SQL*Plus that performs these
actions:
1. Creates a multitenant container database (CDB)
2. Plugs in three pluggable databases (PDBs)
3. Shuts down the CDB instance
4. Starts up the CDB instance using STARTUP OPEN READ WRITE
Which two statements are true about the outcome after running the script?
A.
The seed will be in mount state.
B.
The seed will be opened read-only.
C.
The seed will be opened read/write.
D.
The other PDBs will be in mount state.
E.
The other PDBs will be opened read-only.
F.
The PDBs will be opened read/write.
Correct
Answer:- B & D
Notes:-
SQL>
STARTUP OPEN READ WRITE;
ORACLE instance started.
Total
System Global Area 1610612736 bytes
Fixed Size 2924928 bytes
Variable Size 520097408 bytes
Database Buffers 1073741824 bytes
Redo Buffers 13848576 bytes
Database mounted.
Database opened.
SQL>
select NAME,OPEN_MODE from v$pdbs;
NAME
OPEN_MODE
—————————— ———-
PDB$SEED READ ONLY
PDB1 MOUNTED
PDB2 MOUNTED
PDB3 MOUNTED
NEW_PDB MOUNTED
--------------------------------------------------------------------------------------------------------------------------------------------------
39)
Which three statements are true about using flashback database in a multitenant
container
database (CDB)?
A.
The root container can be flashed back without flashing back the pluggable
databases (PDBs).
B.
To enable flashback database, the CDB must be mounted.
C.
Individual PDBs 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.
Correct Answer:- ?? is there any correct answer given ?
Notes:-
Flashback can be done only at CDB level and this will apply to
all containers, the root and the PDBs -- so it doing a flashback at CDB level
will affect all PDB is'nt it ?
Flashback can be enabled in 12c in OPEN and mount mode … so the
MUST not be in mount mode.
Flash-backing a pluggable database is not supported .
The Oracle doc says that the DB_FLASHBACK_RETENTION_TARGET can
optionnaly be set since it has a default value.
Flash-backing to a restore points are possible.
--------------------------------------------------------------------------------------------------------------------------------------------------
40)
You support Oracle Database 12c Oracle Database 11g, and Oracle Database log on
the same
server.
All databases of all versions use Automatic Storage Management (ASM).
Which three statements are true about the ASM disk group compatibility
attributes that are set for
a 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 databases 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
Oracle 10 g database.
Correct Answer:- D , C/D ? ? ,?
--------------------------------------------------------------------------------------------------------------------------------------------------
41)
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 results in an error because these parameter settings are invalid.
B.
One 64G flash cache file will be used.
C.
Two 64G flash cache files will be used.
D.
Two 32G flash cache files will be used.
Correct Answer:-A
Notes:-
An error
is raised if the number of specified sizes does not match the number of
specified files.
For
example, if there are three flash raw devices, you can specify the sizes of
each device as follows:
db_flash_cache_file = /dev/raw/sda, /dev/raw/sdb, /dev/raw/sdc
db_flash_cache_size = 32G, 32G, 64G
--------------------------------------------------------------------------------------------------------------------------------------------------
42)
You administer an online transaction processing (OLTP) system whose database is
stored in
Automatic Storage Management (ASM) and whose disk group use normal redundancy.
One of the ASM disks 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.
E.
Increase the number of DBWR_IO_SLAVES in the ASM instance.
Correct Answer:-A
Notes:-
when we add / drop disk from diskgroup , this will invoke the
rebalancing operation - RBAL will cordinate the rebalancing works and ARBn
process do the actual task .The number of ARBn processes started is directly
determined by the ASM_POWER_LIMIT parameter setting .For example if we
increase the ASM_POWER_LIMIT to 1 to 5 , you can see 5 ARBn process got
started - asm_arb0, asm_arb1, asm_arb2,asm_arb3 & asm_arb4 -- At normal
situation we cant see arbn process .
--------------------------------------------------------------------------------------------------------------------------------------------------
42)
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
Correct Answer:- C & E
Notes:-
Asynchronous
Global Index Maintenance for DROP and TRUNCATE PARTITION
This feature enables global index maintenance to be delayed and decoupled from
a DROP and
TRUNCATE partition without making a global index unusable. Enhancements include
faster
DROP and TRUNCATE partition operations and the ability to delay index maintenance
to off-peak
time.
--------------------------------------------------------------------------------------------------------------------------------------------------
43) You
configure your database Instance to support shared server connections.
Which two memory areas that are part of PGA are stored in SGA instead, for
shared server
connection?
A.
User session data
B.
Stack space
C.
Private SQL area
D.
Location of the runtime area for DML and DDL Statements
E.
Location of a part of the runtime area for SELECT statements
Correct Answer:- A & C
--------------------------------------------------------------------------------------------------------------------------------------------------
44) Which
four actions are possible during an Online Data file Move operation?
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
Correct Answer:- A,C,D & F
Notes:-
An Online Move data file operation is not compatible when:
• The data file is an OFFLINE data file
• A concurrent flashback database operation is executing
• A media recovery is completing
• A file shrink operation or tablespace offline/drop operation involving the
same file is
performing
But it is compatible with:
• Block media recovery
--------------------------------------------------------------------------------------------------------------------------------------------------
45)
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.
Correct Answer:- A
Notes:-
Pluggable Database cannot be backed up in NOARCHIVELOG mode.
When connect to CDB using RMAN. You can backup pdb even pdb in
mount stat.
--------------------------------------------------------------------------------------------------------------------------------------------------
46)
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.
Correct Answer:- A
Notes:-
Block corruptions can be divided into interblock corruption and intrablock
corruption. In intrablock corruption, the corruption occurs within the block
itself and can be either physical or logical corruption. In interblock
corruption, the corruption occurs between blocks and can only be logical
corruption. The VALIDATE command checks for intrablock corruptions only.
Validates
the server parameter file currently used by the database. RMAN cannot validates
other copies of the server parameter file, and cannot validate the server
parameter file when the instance was started with an initialization parameter
file.
--------------------------------------------------------------------------------------------------------------------------------------------------
47) In
your multitenant container database (CDB) with two pluggable database (PDBs).
You want to
create a new PDB by using SQL Developer.
Which statement is true?
A.
The CDB must be open.
B.
The CDB must be in the mount stage.
C.
The CDB must be in the nomount stage.
D.
Alt existing PDBs must be closed.
Correct
answer:- A
Notes:-
SQL
Developer will not open id DB mount or nomount stage.
--------------------------------------------------------------------------------------------------------------------------------------------------
48) Which
three operations can be performed as multipartition operations in Oracle?
A.
Merge partitions of a list partitioned table
B.
Drop partitions of a list partitioned table
C.
Coalesce partitions of a hash-partitioned global index.
D.
Move partitions of a range-partitioned table
E.
Rename partitions of a range partitioned table
F.
Merge partitions of a reference partitioned index
Correct
answer:- A ,B and C
Notes:-
In
Oracle 12c some maintenance operations can now be performed on multiple
partitions in a single ALTER
TABLE statement.
Option D & E are for single partition .
--------------------------------------------------------------------------------------------------------------------------------------------------
49)
You are connected using SQL* Plus to a multitenant container database (CDB)
with SYSDBA privileges and execute the following sequence statements:
What is
the result of the last SET CONTAINER statement and why is it 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.
D.
If fails because the SET CONTAINER statement cannot be used with PDB$SEED as
the target
pluggable database (PDB).
Correct
answer:- C
Notes:-
--------------------------------------------------------------------------------------------------------------------------------------------------
50)
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 a 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.
Correct
answer:- B
Notes:-
it’s B: Oracle Restart also manages the weak dependency between database
instances and the Oracle Net listener (the listener): When a database instance
is started, Oracle Restart attempts to start the listener. If the listener
startup fails, then the database is still started. If the listener later fails,
Oracle Restart does not shut down and restart any database instances.
--------------------------------------------------------------------------------------------------------------------------------------------------
