Black Friday / Cyber Monday Limited Time Flat 70% Discount offer - Ends in 0d 00h 00m 00s - Coupon code: 70spcl

Oracle 1z0-083 Oracle Database Administration II Exam Practice Test

Page: 1 / 28
Total 282 questions

Oracle Database Administration II Questions and Answers

Question 1

Choose two. Your SALES_ROOT application container has two application PDBs. The SALES_APP application has a common table, FIN.REVENUE, in the two PDBs. Examine this query and its output: SELECT containers_default, container_map, table_name FROM dba_tables WHERE owner='FIN' ; CONTAINERS_DEFAULT CONTAINER_MAP CONTAINER_MAP_OBJECT TABLE_NAME -------------------------------------------------------------------------------------------------------------------------------NO YES NO REVENUE NO NO YES MAPTABLE Which two are true?

Options:

A.

The REVENUE table must be a list-partitioned table.

B.

The CONTAINERS clause cannot be used in queries on the REVENUE table.

C.

The REVENUE table partitions are not pruned across the PDBs automatically.

D.

The REVENUE table must be a composite-partitioned table.

E.

The MAPTABLE table defines a logical partition key on a commonly used column for the REVENUE table.

Question 2

choose three Identify three reasons for using a recovery catalog with Recovery Manager (RMAN).

Options:

A.

to allow database duplication without a connection to the target instance.

B.

to store backup metadata that would cause the controlfile to become too large.

C.

to store backups that would cause the controlfile to become too large.

D.

to report on the database schema at an earlier point in time.

E.

to allow database duplication without a connection to the auxiliary instance.

Question 3

Which two statements are true about RMAN duplexed backups?

Options:

A.

It is not supported for image copies.

B.

It is only supported for backups to tape via the media management interface.

C.

Duplex backups can be performed on either disk or media, but cannot be performed on media and disk simultaneously.

D.

Duplex backups can contain only data files and control files.

E.

For performing duplexed backups, the parallelism for the device must be set equal to the number of copies.

Question 4

HR_ROOT is an application container with the HR_APP application installed. No application PDBs and no application seed have yet been created in HR_ROOT. An application PDB, PDB1,must be created so that the HR_APP application's common objects are accessible to it. Which two methods can be used?

Options:

A.

Create the PDB1 application PDB and synchronize it with poB$SEED.

B.

Create the PDB1 application PDB and synchronize it with HR_ROOT.

C.

Create the PDB1 application PDB and install HR_APP in it.

D.

Create an application seed, and install HR_APP in it.

E.

Create an application seed, synchronize it with HR_ROOT, and then create the PDB1.application PDB.

Question 5

You notice that the performance of your production 24/7 Oracle 12c database has significantly degraded. Sometimes you are not able to connect to the instance because it hangs. You do not want to restart the database instance. How can you detect the cause of the degraded performance?

Options:

A.

Enable Memory Access Mode, which reads performance data from SGA.

B.

Use emergency monitoring to fetch data directly from SGA for analysis.

C.

Run Automatic Database Diagnostic Monitor (ADDM) to fetch information from the latest Automatic Workload Repository (AWR) snapshots.

D.

Use Active Session History (ASH) data and hang analysis in regular performance monitoring,

E.

Run ADDM in diagnostic mode.

Question 6

Choose three. Which three are true about actions that can or cannot be performed by users with the SYSBACKUP privilege?

Options:

A.

They can view data from any data dictionary view or dynamic performance

B.

They can drop any tablespace.

C.

They can view data from any user-defined tables.

D.

They can create any table.

E.

They cannot create restore points.

F.

They cannot drop tablespaces.

Question 7

Which two are true about creating RMAN backups for an Oracle container database? (Choose two.)

Options:

A.

Tablespaces from different PDBs with identical names must be backed up by connecting RMAN separately to each PDB to back up the tablespaces.

B.

The BACKUP DATABASE command will create a pluggable database (PDB) backup when RMAN is connected to a PDB.

C.

SPFILE backups can be created while connected to an application root PDB.

D.

The BACKUP DATABASE PLUS ARCHIVE LOG command will back up archive logs when RMAN is connected to a PDB.

E.

The BACKUP PLUGGABLE DATABASE command can be used to back up CDB$ROOT.

Question 8

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

Options:

A.

The ORA_ARCHIVE_STATE column may only be queried by users with the DBA role set.

B.

The ORA_ARCHIVE_STATE column is only displayed if it is referenced in the column select list of a query

C.

The ROW ARCHIVAL VISIBILITY session parameter defaults to active rows.

D.

The ORA_ARCHIVE_STATE column may only be updated by users with the DBA role set.

E.

The ORA_ARCHIVE_STATE column is updated in an overnight maintenance job based on activity tracking.

Question 9

Which two are true about flashback features in Oracle Database 19c and later releases? (Choose two.)

Options:

A.

Flashback logs are automatically purged when DB_FLASHBACK_RETENTION_TARGET is set lower than the time they have already been retained.

B.

Flashback logs are monitored and proactively deleted when beyond the retention period defined in DB_FLASHBACK_RETENTION_TARGET only after there is space pressure.

C.

Flashback logs are monitored and proactively deleted when beyond the retention period defined in DB_FLASHBACK_RETENTION_TARGET before there is space pressure.

D.

Flashback logs are monitored for being older than the retention period defined in DB_FLASHBACK_RETENTION_TARGET and can be deleted by an administrator written event trigger.

E.

Flashback logs are automatically purged whenever the value of DB_FLASHBACK_RETENTION_TARGET is changed.

Question 10

Which two are true about automatic block repair? (Choose two.)

Options:

A.

Automatic block repair can repair blocks with no standby database if DB_BLOCK_CHECKING = TRUE.

B.

Real-Time Query must be enabled on a physical standby database for automatic block repair to be done on that physical standby database.

C.

Real-Time Query must be enabled on a primary database for automatic block repair to be done on any of its physical standby databases.

D.

It is not possible for media corrupt blocks.

E.

Real-Time Query must be enabled on a physical standby database for automatic block repair to be done on its primary database.

Question 11

Choose two. Which two are true about reclaiming space used by Flashback logs in Oracle Database 19c and later releases?

Options:

A.

Space might be reclaimed proactively before space pressure occurs.

B.

Space is always reclaimed proactively before space pressure occurs.

C.

Space might be reclaimed automatically when the retention period for Flashback logs is lowered.

D.

Space is always reclaimed automatically when the retention period for Flashback logs is lowered.

E.

Space is only reclaimed when there is space pressure in the Fast Recovery Area (FRA).

Question 12

Choose three. Which three are true about the Oracle Optimizer?

Options:

A.

It considers the filters in WHERE clauses when generating execution plans for SQL statements.

B.

It obeys all hints.

C.

It considers object statistics when generating execution plans for SQL statements.

D.

It updates stale object statistics in the Data Dictionary.

E.

It can only use SQL Plan Directives tied to the SQL statement for which a plan is being generated.

F.

It can re-optimize execution plans after previous executions detect suboptimal plans.

Question 13

Choose three. Which three are true about patchsets?

Options:

A.

They are installed via OPatch or OPatchAuto.

B.

A base release is not needed to install patchsets.

C.

They can be applied in a rolling fashion for Clusterware and the databases.

D.

Installing a patchset is considered an "upgrade."

E.

They can introduce new features.

F.

They are only released quarterly.

Question 14

Which are three of the steps taken by Database Configuration Assistant (DBCA) to clone a remote pluggable database (PDB) starting from Oracle 19c?

Options:

A.

creating a database link from CDB$ROOT in the local database to CDB$ROOT in the remote system that is to be cloned

B.

creating a database link from CDB$ROOT in the local database to the PDB in the remote system that is to be cloned

C.

backing up the cloned PDB

D.

opening the cloned PDB

E.

leaving the cloned PDB in mount state

Question 15

Choose three. PDB1 and PDB2 are pluggable databases in CDB1. Examine these commands: $ export ORACLE_SID=CDB1 $ sqlplus / as sysdba SQL> STARTUP ....... Database opened. SQL> SHOW PDBS CON_ID CON_NAME --------------------------------------------------------------------OPEN MODE READ ONLY NO MOUNTED MOUNTED SQL> ALTER PLUGGABLE Pluggable database altered. SQL> ALTER PLUGGABLE DATABASE pdb1 SAVE STATE; Pluggable database altered. SQL> ALTER PLUGGABLE DATABASE pdb1 CLOSE; Pluggable database altered. SQL> ALTER PLUGGABLE A DATABASE pdb2 OPEN; Pluggable database altered. CDB1 is then restarted. Which three are true?

Options:

A.

PDB2 will be opened READ WRITE.

B.

PDB1 will be opened READ WRITE.

C.

PDB1 will be MOUNTED.

D.

PDB$SEED will be opened READ ONLY.

E.

PDB$SEED will be MOUNTED

F.

PDB2 will be MOUNTED.

Question 16

In a recent Automatic Workload Repository (AWR) report for your database, you notice a high number of buffer busy waits. The database consists of locally managed tablespaces with free list managed segments. On further investigation, you f ind that buffer busy waits is caused by contention on data blocks. Which option would you consider first to decrease the wait event immediately?

Options:

A.

Decreasing PCTUSED

B.

Decreasing PCTFREE

C.

Increasing the number of DBWN process

D.

Using Automatic Segment Space Management (ASSM)

E.

Increasing db_buffer_cache based on t he V$DB_CACHE_ADVICE recommendation

Question 17

Which three are true about upgrading Oracle Grid Infrastructure? (Choose three.)

Options:

A.

A direct upgrade can be performed only from the immediately preceding Oracle Grid Infrastructure version.

B.

The newer version is installed in a separate Oracle Grid Infrastructure home on the same server as the existing version.

C.

An existing Oracle base can be used.

D.

The upgrade process will automatically install all mandatory patches for the current version of Oracle Grid Infrastructure.

E.

Existing Oracle Database instances must be shut down before starting the upgrade.

F.

Only the grid user can perform the upgrade.

Question 18

Choose three. Which three are true about interpreting Recovery Manager (RMAN) error stacks returned to standard output?

Options:

A.

If an RMAN command fails, the output will only identify the command that failed.

B.

Some messages in the error stack are not errors.

C.

If an RMAN command fails, the output will identify the channel ID where the failure occurred. D)Media Management errors appear as a line with "Additional information" and a number.

D.

Media Management errors appear as a line with "sbtio" and a number.

E.

Media Management errors appear as a line with "sbtio".

Question 19

Choose two. Which two are true about the Optimizer Statistics Advisor?

Options:

A.

It always analyzes all schemas in the database.

B.

It can recommend changes to improve the statistics gathering process.

C.

It can be run only manually.

D.

It runs automatically every night by default. E) It can be run only automatically.

Question 20

Choose the best answer. Examine this configuration: 1. CDB1 is a container database. 2. APP_ROOT is an application root in CDB1. 3. APP_PDB1 is an application PDB in APP_ROOT. 4. FLASHBACK DATABASE is ON. You execute these commands: $ sqlplus sys/orac1e_4U@localhost:1521/cdb1 SQL> SELECT current_scn FROM v$database; CURRENT_SCN -----------------------2074756 SQL> CREATE TABLE cdb1_tab(c1 NUMBER) ; Table created. SQL> ALTER SESSION SET CONTAINER=app_root; Session altered. SQL> CREATE TABLE app_root_tab (c1 NUMBER) ; Table created. SQL> ALTER SESSION SET CONTAINER=app_pdb1; Session altered. SQL> CREATE TABLE app_pdb1_tab (c1 NUMBER) ; Table created. SQL> conn / as sysdba Connected. SQL> ALTER PLUGGABLE DATABASE app_root CLOSE; Pluggable database altered. SQL> FLASHBACK PLUGGABLE DATABASE app_root TO SCN 2074756; Flashback complete. Which table or set of tables will exist after the Flashback operation has completed?

Options:

A.

CDB1_TAB, APP_ROOT_TAB, and APP_PDB1_TAB

B.

CDB1_TAB and APP_PDB1_TAB

C.

CDB1_TAB and APP_ROOT_TAB

D.

CDB1_TAB only

E.

none of the tables, because all three tables will be dropped

Question 21

A container database called CDB1 is OMF-enabled. PDB_FILE_NAME_CONVERT is not configured in CDB1. PDB1 was unplugged from CDB1 earlier in the week. Examine this command, which will be executed in CDB1: CREATE PLUGGABLE DATABASE pdb1 USING ‘/u01/app/oracle/oradata/pdb1.xml’ SOURCE_FILE_NAME_CONVERT = (‘/u01/app/oracle/oradata/’, ‘/u02/app/oracle/oradata/’);

Which two are true? (Choose two.)

Options:

A.

PDB1 data files already exist in the correct location.

B.

DBMS_PDB.CHECK_PLUG_COMPATIBILITY must be run in CDB1 before executing the command.

C.

PDB_FILE_NAME_CONVERT must be set before executing the command.

D.

/u01/app/oracle/oradata/pdb1.xml does not contain the current locations of data files for PDB1.

E.

PDB1 must be dropped from CDB1.

Question 22

Choose three. Which three are true about the tools for diagnosing Oracle Database failure situations?

Options:

A.

RMAN can always repair corrupt blocks.

B.

Flashback commands help with repairing physical errors.

C.

The ADR can store metadata in an Oracle Database repository.

D.

The Automatic Diagnostic Repository (ADR) has a separate home directory for each instance of each Oracle product that is installed and uses it.

E.

The ADR command-line utility (ADRCI) can package incident information to send to Oracle Support.

F.

The Data Recovery Advisor uses the ADR.

Question 23

You plan to create a database by using the Database Configuration Assistant (DBCA), with t he following specifications: Applications will connect to the database via a middle tier. The number of concurrent user connections will be high. The database will have mixed workload, with t he execution of complex BI queries scheduled at night. Which DBCA option must you choose to create the database?

Options:

A.

a General Purpose database template with default memory allocation

B.

a Data Warehouse database template, with t he dedicated server mode option and AMM enabled

C.

a General Purpose database template, with t he shared server mode option and Automatic Memory Management (AMM) enabled

D.

a default database configuration

Question 24

Choose two. Which two are true about gathering optimizer statistics?

Options:

A.

Executing DBMS_STATS.GATHER_DATABASE_STATS while connected to CDB$ROOT gathers object statistics only in PDB$SEED.

B.

Executing DBMS_STATS.GATHER_DATABASE_STATS while connected to CDB$ROOT gathers object statistics only in CDB$ROOT.

C.

Executing DBMS_STATS.GATHER_DATABASE_STATS while connected to CDB$ROOT gathers object statistics in all open pluggable databases (PDBs).

D.

System statistics can be gathered only while the CDB is in MOUNT state.

E.

Executing DBMS_STATS.GATHER_DATABASE_STATS while connected to a PDB opened in read/ write mode gathers object statistics for that PDB.

Question 25

Choose two. In which two situations can you use Database Upgrade Assistant?

Options:

A.

when a character set conversion is required during the upgrade

B.

when multiple pluggable databases in a container database have to be upgraded in a specific sequence

C.

when the target and source database are on the same platform

D.

when the hardware platform needs to be changed as part of the upgrade

E.

when the operating system (OS) needs to be changed as part of the upgrade

Question 26

Examine this configuration: While CDB1 is open, ‘/u02/app/oracle/fast_recover_area/cdb1/CDB1/ controlfile02.ctl’is accidentally deleted. To recover from this critical failure, you execute these commands:

What will be the outcome?

Options:

A.

It will create ‘$ORACLE_HOME/dbs/cdb1/CDB1/controlfile02.ctl’

B.

It will create ‘/u01/app/oralce/oradata/CDB1/controlfile/controlfile02.ctl’.

C.

It will re-create ‘/u02/app/oracle/fast_recover_area/cdb1/CDB1/controlfile02.ctl’

D.

It will create ‘/u01/app/oracle/product/12.2.0.1/db_1/dbs/snapcf_cdb1control02.ctl’.

E.

It will fail because there is no autobackup of the controlfiles.

Question 27

Which two statements are true about the Database Configuration Assistant (DBCA)?

Options:

A.

It can configure Automatic Storage Management (ASM) diskgroups.

B.

It can be used to copy an existing Oracle database to a new host and apply any patches necessary in the new host.

C.

It can be used to add a new tablespace.

D.

It can be used to create a database template from an existing database.

E.

It can generate SQL database creation scripts.

Question 28

Which three methods can be used for heap table data migration after upgrading a database? (Choose three.)

Options:

A.

using Database Replay

B.

using SQL Developer

C.

using Oracle Data Pump

D.

using operating system file copy utilities

E.

using Database Upgrade Assistant

F.

using the CREATE TABLE AS SELECT SQL statement

Question 29

choose two Which two statements are true about encrypted backups performed by using RMAN?

Options:

A.

Transparent encryption of backups uses an encryption wallet.

B.

Image copy backups can be created by using password encryption.

C.

A password encryption of backups uses the password while creating and restoring backups.

D.

Encrypted backups can only be written to disk.

E.

A database uses the same encryption key for every encrypted backup.

Question 30

Which four are true about duplicating a database using Recovery Manager (RMAN)? (Choose four.)

Options:

A.

Duplication can be done by having the auxiliary database instance pull backup sets from the target database instance.

B.

A connection to an auxiliary instance is always required.

C.

A subset of the target database can be duplicated.

D.

A new DBID is always created for the duplicated database.

E.

A connection to the recovery catalog instance is always required.

F.

A backup of the target database is always required.

G.

Duplication can be done by having the target database instance push copies to the auxiliary database instance.

Question 31

RMAN is connected to a target database instance and an auxilary instance. You execute 3625the command: RMAN> DUPLICATE TARGET DATABASE TO dupdb FROM ACTIVE DATABASE SECTION SIZE 1G; What is the outcome?

Options:

A.

RMAN uses the pull method to restore files using image copies

B.

RMAN uses the push method to transfer image copies

C.

RMAN uses the push method to transfer backup sets

D.

RMAN uses the pull method to restore files using backup sets

Question 32

Which statement describes the significance of the CHANGE FAILURE command in RMAN? (Choose all that apply.)

Options:

A.

It is used to change failure priority only for HIGH or LOW priorities.

B.

It is used to execute the advised repair script.

C.

It is used to change failure priority only for the CRITICAL priority.

D.

It is used to explicitly close the open failures.

E.

It is used to inform the database about the repair after the repair script executes.

Question 33

Choose three. Which three are true about backing up the control file?

Options:

A.

It cannot be backed up manually as a binary copy.

B.

It gets backed up automatically by RMAN as a trace file containing SQL statements for control file re¬creation when a database backup occurs.

C.

It can be backed up manually as a binary copy.

D.

It can get backed up automatically as a binary copy.

E.

It gets backed up automatically by RMAN when the BACKUP SPFILE command is used.

F.

It can be backed up manually as a trace file containing SQL statements for control file recreation.

Question 34

Choose three. Which three are true about Rapid Home Provisioning of Oracle software?

Options:

A.

It can be used to patch existing Oracle software installations.

B.

It can be used only on nodes with Oracle Grid Infrastructure installed.

C.

It can be used to create a single-instance Oracle Database in an already-installed Oracle home. D)It can be used to deploy new homes without disrupting active databases.

D.

It can be used to create templates of Oracle homes as gold images of only Oracle databases.

E.

It can be used to upgrade only single-instance databases running on Oracle Restart.

Question 35

Which two are true about poorly performing SQL statements and their tuning in Oracle database instances?

Options:

A.

SQL statements performing many physical I/Os can always have their performance improved by creating indexes.

B.

The solution that best optimizes one SQL statement can degrade the performance of others.

C.

SQL statements doing joins always do more physical I/O than single table queries.

D.

Poorly performing SQL statements can be tuned automatically by the Oracle server.

E.

SQL statements doing only logical reads never require tuning because they do no physical I/O.

Question 36

Which two statements are true about recovering logically corrupted tables or table partitions from an RMAN backup?

Options:

A.

Tables or table partitions can be recovered by using an auxiliary instance only.

B.

Tables or table partitions with a foreign key cannot be recovered.

C.

Tables or table partitions can be recovered only when the database is in mount state.

D.

Tables or table partitions from the system and sysaux tablespaces cannot be recovered.

E.

Tables with not null constraints cannot be recovered.

Question 37

choose three Which three are true about unified auditing in full mode?

Options:

A.

The unified audit trail is written to a read-only table.

B.

The unified audit trail is written to a read-only external file.

C.

Audit records are created for COMMIT, ROLLBACK, and SAVEPOINT statements.

D.

Audit records are created for SYS.

E.

Audit records are created for non-sys users.

Question 38

Choose three. In which three situations can you use threshold server-generated alerts to help diagnose and fix problems?

Options:

A.

when free space in a dictionary managed tablespace falls below a specified percentage

B.

when free space in a locally managed tablespace falls below a specified percentage

C.

when the total number of locked user account exceeds a specific value

D.

when the number of logons per second exceeds a specific value

E.

when a resumable statement is suspended

F.

when the total number of logons exceeds a specific value

Question 39

choose four Which four are true about performance-related Oracle tools?

Options:

A.

The Automatic Database Diagnostic Monitor (ADDM) Compare Period report can compare two consecutive periods.

B.

SQL Performance Analyzer can suggest schema changes.

C.

SQL Performance Analyzer can identify regressed SQL statements.

D.

Database Replay always uses one replay client for all the original database sessions.

E.

Database Replay always uses one replay client for each original database session.

F.

The Automatic Database Diagnostic Monitor (ADDM) Compare Period report can compare two non¬consecutive periods.

G.

The Active Session History (ASH) report is based on wait event samples.

Question 40

Examine the statements that use flashback technologies:

FLASHBACK TABLE customers TO TIMESTAMP TO_TIMESTAMP('2013-02-04 09:30:00', 'YYYYMM¬DD HH:MI:SS');

SELECT * FROM customers AS OF SCN 123456;

FLASHBACK TABLE customers TO BEFORE DROP;

FLASHBACK DATABASE TO TIMESTAMP TO_TIMESTAMP('2013-02-04 09:30:00', 'YYYY-MM-DD HH:MI:SS');

SELECT * FROM customers VERSIONS BETWEEN SCN 123456 AND 123999;

ALTER TABLE customers FLASHBACK ARCHIVE; Which set ofstatements depends on the availability of relevant undo data in the undo tablespace?

Options:

A.

1,3,6

B.

1,2,5

C.

3,4,5

D.

2,3,5,6

Question 41

Choose two. Which two are true about an Oracle gold image-based installation in Oracle 18c and later releases?

Options:

A.

It does not require the setup wizard.

B.

It can be used to install and upgrade Oracle Database for single-instance and cluster configurations.

C.

It can be used for both Oracle Database and Oracle Grid Infrastructure installation.

D.

It uses a single RPM that automatically extracts and installs the Oracle Database software.

E.

It can only install and configure Oracle Database software. The database has to be created separately.

Question 42

Choose three. Which three are true about block media recovery?

Options:

A.

To use it, Flashback Database must be enabled.

B.

A block being recovered is not accessible.

C.

It cannot repair logical corruption.

D.

It can be performed on noncorrupt blocks.

E.

The target database for which one or more blocks are to be recovered must be in the OPEN state.

F.

The data file containing the block being recovered remains online.

Page: 1 / 28
Total 282 questions