AQ-DATA SYSTEMS ADMINISTRATION - D330 (All Questions from Chapter 8-10, 12-15, and 17)

SGA_TARGET

Which parameter can an administrator enable without restarting a database?

V$SPPARAMETER

Which view displays database parameters and values modified using the SCOPE=SPFILE clause?

SHUTDOWN IMMEDIATE

Which command performs a clean shutdown without waiting for clients to disconnect?

Java Pool

Which area is a member of the System Global Area (SGA)?

Tablespace creation

Which types of events are recorded in an alert log?

DIAGNOSTIC_DEST

Which initialization parameter sets the location of the alert log?

$ORACLE_HOME/NETWORK/ADMIN

Where is the listener.ora file located by default?

Client with database server

Which type of connection uses the Oracle Listener to communicate?

A committed server process

What does the SERVER=DEDICATED element in a tnsnames.ora file associate with each client connection?

Host

Which net service naming method requires the client to use a fixed port number?

scott.employee@wgu2021

A database link named wgu2021 has been created to link to a remote object in the test database. The object is named employee and is owned by Scott.

Which reference resolves to the remote object?

User name

Which information from a remote database is included in the configuration of a database link?

table data

Which object can be added to an existing bigfile tablespace?

To facilitate the rollback of transactions

What is the purpose of the undo tablespace?

DBA_ADVISOR_OBJECTS

Which dictionary view is available in the database to view segment advisor results?

the SHRINK option

A database administrator needs to recover unused space from a tablespace while preventing data loss.

What should the administrator use?

DBMS_SPACE_ADMIN

Which package should an administrator use to configure local extent management for tablespaces?

segment advisor

What should an administrator use to identify tables that are fragmented?

UNDO_RETENTION

Which parameter determines how long information should be kept before it is overwritten in an undo tablespace?

Segments are assigned dynamically each time a change is made.

How is undo tablespace storage allocated?

A deadlock occurs, and the database cancels Session2.

Given the following transactions and sessions named Session1 and Session2:

At 8:00, Session1 issues the statement: UPDATE POLICY SET LOB= '16' WHERE status='bound' AND LOB_CAT='cancel';

At 8:05, Session1 commits the update transaction.

At 8:05, Session2 issues the statement: UPDATE POLICY SET LOB= '16' WHERE status='submitted' And LOB_CAT='cancel';

At 8:15, Session1 issues the statement: UPDATE POLICY SET LOB= '81' WHERE status='bound' AND LOB_CAT='cancel';

At 8:30, Session2 issues the statement: UPDATE POLICY SET LOB= '16' WHERE status='bound' AND LOB_CAT='cancel';

What happens after Session2 issues the statement at 8:30?

Deadlocks

Which condition is automatically resolved by Oracle without human intervention?

Resumable space allocation

Which feature allows a failed transaction to be suspended until the problem that caused the failure is resolved?

DBA_DATA_FILES

Which view has space information?

Enabling the retention guarantee

An administrator attempts to roll back a transaction from the previous day. The rollback attempt fails.

Which action will prevent the failure from recurring?

Data for committed transactions will be overwritten if an active transaction needs the space.

What is the impact of setting the value of the undo retention initialization parameter to 900 in an undo tablespace that uses a fixed size?

DIRECT

Which parameter disables conventional path loading when using SQL*Loader?

SQL*Loader

What allows users to capture data from non-Oracle sources into an Oracle database?

SELECT

Which SQL statement is allowed with external tables?

Control

An administrator plans to use SQL*Loader to import a data file using a fixed-width format.

Which file must be configured before importing the file?

Automatic Database Diagnostic Monitor

Which tool identifies potential performance bottlenecks?

DBMS_STATS

Which tool contains procedures to collect performance information on database objects?

SQL Tuning Advisor

Which tool is started by the system and automatically improves the performance of queries?

When a job is running slow in the database

When does a DBA run an Automatic Workload Repository (AWR) report to find the root cause of an issue?

Execution plan

Which additional information can administrators collect by configuring the Automatic Workload Repository (AWR) to use the ALL statistics collection level?

The insert fails with an error for missing privileges on the DATA tablespace.

An administrator runs the following SQL statements:

CREATE USER HR_User IDENTIFIED BY 'Password' DEFAULT TABLESPACE DATA;

GRANT CREATE SESSION, CREATE TABLE to HR_User;

HR_User creates a new table in their own schema.

What happens when HR_User inserts a row into the new table?

EXTERNALLY

Which parameter is used when creating a user account that will be authenticated by the operating system?

USER$

Which dictionary table stores the credentials for a password-authenticated account?

Tables and the data are deleted from the database.

Given the following SQL statement:

DROP USER User1 CASCADE;

What happens to tables that User1 owns when an administrator runs the SQL statement?

REFERENCES

Which privilege is required to add a foreign key constraint to a table owned by another user?

Drop user

Which privilege provides grantees permission to remove accounts from a database?

All the user's objects are removed from the database.

What is the result of the CASCADE option when used with the DROP USER command?

UPDATE

Which privilege must be granted to allow a user to modify existing rows in a table?

Modify

A user creates a role granting select and update access to table1, and assigns the role to a coworker. The next day the user revokes select access from the role.

Which actions will the coworker still be able to perform on table1?

SELECT_CATALOG_ROLE

A user needs to view the table privileges of other users.

Which role should be granted to the user?

PUBLIC

After an administrator granted some privileges to a user, all database users automatically had those same privileges.

Which user was assigned the privileges?

Profile

An administrator wants to limit CPU time for accounts.

Which object should the administrator alter?

COMPOSITE_LIMIT

In response to users' complaints that the system is slow, a database administrator detects that a user is consuming too many resources.

Which parameter controls the use of service units?

PASSWORD EXPIRE

An administrator creates a user profile that forces a change to the user's password at the first login.

Which clause did the administrator include in the create user statement?

Inconsistent

Which type of database backup can be performed while a database is online?

Control

Which file must be present to start an instance of a database?

Compressed

Which format minimizes the space required for a full database backup?

Instance parameters

What can be queried from a database while it is in the NOMOUNT state?

Data

An administrator starts a database and initiates instance recovery.

Which type of files can be recovered?

LOG_CHECKPOINT_INTERVAL

Which parameter affects the mean time to recovery target for a database instance?

Database Writer (DBWn)

Which process duplicates modified blocks from a buffer cache to files on disk?

host_name.olr

Which file does the Database Upgrade Assistant (DBUA) obtain its list of databases from?

Using Oracle Golden Gate

Which action updates a database from an earlier version to a newer version while the database remains online?

CONTENT

Which data pump parameter can an administrator use to perform a metadata-only export?

Export and Import

Which method allows any Oracle release to be migrated to Oracle 12c?

Convert by using Recovery Manager

A database will be migrated to a platform that has a different endianness.

Which action must be performed on the data files before the migration?

Database Upgrade Assistant

Which method is used to perform an in-place upgrade?

Action

Which type of auditing rule records table insert operations?

By configuring the ORA_ACCOUNT_MGMT predefined policy

How should an administrator enable mixed-mode auditing for a database?

adrci

Which interactive tool presents a view of an alert log?

Information about the current version of database components

Which information is included in the output of the utlu121s.sql post-upgrade status script?

Rolling

A company plans to use Data Guard SQL Apply to migrate a database to Oracle 12c.

Which type of upgrade does this tool perform?

Database Upgrade Assistant in silent mode

Which upgrade option automates the upgrade process without user intervention?

compatible

Which parameter must be set after an upgrade to enable the new version's features?

utlu121s.sql

Which tool provides status upgrade result information after an upgrade?

Shared pool

Buffer cache

Database buffer cache, shared pool, and log buffer are required; they are configured automatically in every instance. It is better to use Automatic Memory Management

or Automatic Shared Memory Management, so that the DBA does not need to tune individual components.

Choose two SGA structures that are required in every Oracle instance.

A database must have at least one control file.

The control file is the most key file in an Oracle database. Due to its importance, it is a good practice to have two more copies of the file. A database must have at least one control file to start the database.

Which statement is true?

Redo log buffer

An Oracle database allows you to manage all memory components dynamically, except the redo log buffer. Redo log buffer is set at instance startup and is not dynamically alterable without restarting the instance.

Which component is configured at database startup and cannot be dynamically managed?

Control file

Control file, data file, and redo log files are part of the Oracle database. The Oracle instance constitutes the memory structures and background processes.

Which component is not part of an Oracle instance?

LGWR

The log writer (LGWR) process writes the redo log buffer information to the online redo log files. A commit operation is completed only after the redo buffer is written to online redo log files.

Which background process guarantees that committed data is saved even when the changes have not been recorded in data files?

Does nothing.

When a user issues a commit, the LGWR process makes sure the redo log buffer is written to the online redo log files. Database writer takes no action against the commit event.

User John has updated several rows in a table and issued a commit. What does the DBWn (database writer) process do at this time in response to the commit event?

One database, multiple instances

With Real Application Clusters, multiple instances (known as nodes) can mount one database. One instance can be associated with only one database.

Which of the following best describes a RAC configuration?

Library cache

The shared SQL area is stored in the library cache in a shared pool and is shared between users. If a query is executed again before it is aged out of the library cache, Oracle will use the parsed code and execution plan from the library cache. The database buffer cache has the data blocks cached. The dictionary cache caches data dictionary information. There is no SGA component called the parse cache.

Which component of the SGA contains the parsed SQL code?

Performs recovery at instance startup

Coalesces contiguous free space in dictionary-managed tablespaces

Which tasks are accomplished by the SMON process? (Choose all that apply.)

A nonpartitioned table can have only one segment.

A table or index has a segment. A segment consists of one or more extents. A segment can belong to only one tablespace, but it can span across multiple data files.

Choose the best statement from the options related to segments.

ARCn

MMAN

Memory operations are configured. From the following list, choose two processes that are optional in an Oracle Database 12c database.

Large pool

The large pool is configured so that RMAN does not use the shared pool; therefore, the shared pool is totally dedicated to application space

Which SGA component will you increase or configure so that RMAN tape backups do not use memory from the shared pool?

PMON is responsible for cleaning up failed user processes. It reclaims all the resources held by the user and releases all locks on tables and rows held by the user. No other process is involved in the session cleanup.

When a user session is terminated, which processes are responsible for cleaning up and releasing locks?

The shared pool and database buffer cache portions of the SGA.

The LRU algorithm is used to manage what part of the Oracle architecture?

An instance consists of memory structures and processes, whereas a database is composed of physical files.

Two structures make up an Oracle server: an instance and a database. Which of the following best describes the difference between an Oracle instance and a database?

Operating-system block, database block, extent, segment

Which of the following is the proper order of Oracle's storage hierarchy, from smallest to largest?

The instance crashes and needs to be restarted.

The DBA unknowingly terminated the process ID belonging to the PMON process of Oracle Database 12c database using the kill -9 command on Unix. Choose the best answer:

Control files

When an incremental checkpoint happens in a database, which file(s) are updated with the checkpoint position?

1. Oracle reads the blocks from data file to buffer cache and updates the blocks.

2. The server process writes the change vectors to the redo log buffer.

3. The user commits the change.

4. LGWR flushes the redo log buffer to redo log files.

5. A checkpoint occurs.

6. Changed blocks from the buffer cache are written to data files.

7. LGWR writes the changed blocks to the redo log buffer. (Not Relevant)

User Isabella updates a table and commits the change after a few seconds. Which of the following actions are happening in the database? Order them in the correct sequence and ignore the actions that are not relevant.

Group 1 and 4

Redo log groups with status CURRENT and ACTIVE are required during instance crash recovery.

Querying the V$LOG file shows the following information. Which redo group files are required for instance crash recovery?

SQL> select GROUP#, ARCHIVED, STATUS from V$LOG;

GROUP# ARC STATUS

When using ALTER SYSTEM to change parameter values, the change is made to the server parameter file (spfile) too, because the default for the SCOPE clause is BOTH. Option D would have been correct, if the pfile were used to start up the database. When a database is created using DBCA, the parameter file created is always spfile.

You noticed that the current value of the UNDO_RETENTION parameter is 900 and is too low for some of your transactions. The database was created using DBCA. You issue the following statement:

ALTER SYSTEM SET UNDO_RETENTION=4800;

Which option is true?

BACKGROUND_DUMP_DEST.

You need to find the directory where the Oracle alert log is being written. Which initialization parameter can be best used to list the full directory path of the alert log location?

V$PARAMETER

Which data dictionary view is used to view the current values of parameters?

STARTUP NOMOUNT

Which startup options must be used to start the instance when you create a new database?

/u01/app/oracle/diag/rdbms/xyz/xyz/trace

The DIAGNOSTIC_DEST parameter is not set up in the initialization parameter file. The value of the ORACLE_HOME environment variable is /u01/app/oracle/product/12.1.0, and the value of ORACLE_BASE is /u01/app/oracle. The database and instance name is xyz.

What is the location of the text-alert log file for the xyz database?

You must choose the Custom template in the DBCA.

You want to create a database using the DBCA with DB_BLOCK_SIZE as 32KB. Which statement is true?

Change Database Initialization Parameters

All of the following are database-management options within the Database Configuration Assistant except which one?

runInstaller

The Oracle Universal Installer is started by executing which program?

Determine which directory will be used for $ORACLE_HOME.

You've been asked to install Oracle Database 12c on a new Linux server. You're likely to ask the Unix system administrator to do all but which one of the following for you in order to get the new server ready for Oracle?

spfile.ora

Your database name is OCA12C. The options show the files that are available in the $ORACLE_HOME/dbs directory. Which file is used to start up the database instance when you issue the STARTUP command?