Oracle Associate 2.0
Which set of statements is true about data dictionary views? 1. They are stored in the SYSTEM tablespace. 2. They are based on virtual tables. 3. They are owned by the SYS user. 4. They can be queried by a normal user only if the 07_DICTIONARY_ACCESSIBILITY parameter is set to TRUE. 5. The V$FIXED_TABLE view can be queried to list the names of these views. 6. They are owned by the SYSTEM user.
1 and 3
Your database instance has Automatic Memory Management enabled and supports shared server connections. Examine the following: 1. Parallel execution messages and control structures 2. Local variables for a process 3. Security and resource usage information 4. Runtime memory values, such as rows retrieved for a SQL statement using a serial execution plan 5. SQL execution work areas Which option indicates what is allocated from the large pool in this instance?
1,2,4
On your Oracle 12c database, you Issue the following commands to create indexes SQL > CREATE INDEX oe.ord_customer_ix1 ON oe.orders (customers_id, sales_rep_id) INVISIBLE; SQL> CREATE BITMAP INDEX oe.ord_customer_ix2 ON oe.orders (customers_id, sales_rep_id); Which two statements are correct?
A. Both theindexes are created; however, only the ORD_COSTOMER index is visible. E. Both the indexes are updated when a new row is inserted, updated, or deleted In the orders table
Which two statements correctly describe the relationship between data files and logical database structures? A. A segment cannot span data files. B. A data file can belong to only one tablespace. C. An extent cannot span data files. D. The size of an Oracle data blockin a data file should be the same as the size of an OS block.
B. A data file can belong to only one tablespace C. An extent cannot span data files
Which two would you recommend to an application developer for reducing locking conflicts on tables in an Oracle database? (Choose two.)
B. Avoid coding long-running transactions C. Allow the database to handle locks ln default locking mode
A user establishes a connection to a database instance by using an Oracle Net. You want to ensure that: 1. The user account must be locked after five consecutive unsuccessful login attempts. 2. Data read per session must be limited for the user. 3. The user cannot have more than three simultaneous sessions. 4. The user must have a maximum of 10 minutes session idle time before being logged off automatically. Which two would you do to implement this?
B. by using appropriatePASSWORDparameters set in the profile assigned to the user E. by using appropriateKERNELparameters set in the profile assigned to the user
Which three statements are true about Enterprise Manager Database Express? (Choose three.)
B. it can use the HTTP protocol D. it can use the HTTPS protocol E. it is available only when the database is open
The performance of your database degrades between 11:00 AM and 3:00 PM. Automatic Workload Repository (AWR) snapshots are collected on an hourly basis. What is the most efficient way of diagnosing this problem?
Create a custom ADDM task for the period defined by the snapshots taken between 11:00 AM and 3:00 PM.
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?
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
Which statement is true regarding the startup of a database instance?
Instance recovery is not required if the database instance was shut down by using SHUTDOWN IMMEDIATE
You have successfully taken a database backup by using the command: RMAN> BACKUP AS BACKUPSET DATABASE; Now you execute this command: RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE; What is the outcome?
It takes an incremental level 0 backup of the database.
Which Oracle Database component is audited by default if the unified Auditing option is enabled?
Oracle Recovery Manager (RMAN)
What can be automatically implemented after the SQL Tuning Advisor is run as part of the Automated Maintenance Task?
SQL profile Recommendations
Which statement is true about unified auditing?
The unified audit trail, by default, resides in a read-only table in theAUDSYSschema in theSYSAUXtablespace
Which statement is true about the loss of a data file belonging to the default undo tablespace?
the database instance aborts
In which situations does the Database Writer process (DBWn) write to data files?
when a tablespace is made read-only or taken offline
A database instance is started by using an SPFILE. The database is configured in ARCHIVELOG mode and the control file autobackup is configured. Daily full database backups are performed by using RMAN. You lost all control files due to media failure. Given the steps to recover from the error in random order: 1. Shut down the instance, if it is not already down. 2. Restore the control file from autobackup to a new location. 3. Start the database instance to NOMOUNT state. 4. Recover the database to the point of failure of the control file. 5. Open the database with the RESETLOGS option. 6. Mount the database. 7. Update the SPFILE with the new location of the control file by using the ALTER SYSTEM command. Identify the correct sequence of the required steps.
1,3,7,2,6,4,5
The following parameter are set for your Oracle 12c database instance: OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=FALSE OPTIMIZER_USE_SQL_PLAN_BASELINES=TRUE You want to manage the SQL plan evolution task manually. Examine the following steps: 1. Set the evolve task parameters. 2. Create the evolve task by using the DBMS_SPM.CREATE_EVOLVE_TASK function. 3. Implement the recommendations in the task by using the DBMS_SPM.IMPLEMENT_EVOLVE_TASK function. 4. Execute the evolve task by using the DBMS_SPM.EXECUTE_EVOLVE_TASK function. 5. Report the task outcome by using the DBMS_SPM.REPORT_EVOLVE_TASK function. Identify the correct sequence of steps:
2, 1, 4, 3, 5
You have a production Oracle 12c database running on a host. You want to install and create databases across multiple new machines that do not have any Oracle database software installed. You also want the new databases to have the same directory structure and components as your existing 12c database. The steps in random order: 1. Create directory structures similar to the production database on all new machines. 2. Create a response file for Oracle Universal Installer (OUI) with the same configurations as the production database. 3. Create a database clone template for the database. 4. Run the Database Configuration Assistant (DBCA) to create the database. 5. Run OUI in graphical mode on each machine. 6. Run OUI in silent mode using the OUI response file. Identify the required steps in the correct sequence to achieve the requirement with minimal human intervention.
2,3,1,6
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 CDB. 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 SHUTDOWN ABORT. Which option identifies the correct sequence to recover the SYSAUX tablespace?
6,5,4,7
You want to create a database with a block size other than the default 8 kilobytes (KB) by using the Database Configuration Assistant (DBCA). Which option should you use?
A custom database template
Which two statements are true about resumable space allocation? (Choose two.)
A. A database-levelLOGONtrigger can be used to automatically configure resumable statement settings forindividual sessions B. SELECTstatements that run out of temporary space for sort areas are candidates for resumable execution.
Which three statements are true about Database Resource Manager?
A. A resource plan change can be automated by using the Oracle Scheduler. C. It can be used tocontrol the usage of the undo tablespace by consumer groups. D. A resource plan can have multiple resource plan directives, each of which controls resource allocation for a different consumer group
Your multitenant container (CDB) contains two pluggable databases (PDB), HR_PDB and ACCOUNTS_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 that produced the error. E. Shut down the database instance and then restart the CDB and PDBs.
As part of a manual upgrade of your database to Oracle Database 12c, you plan to issue the command: SQL> STARTUP UPGRADE Which three statements are true about the upgrade process? (Choose three.)
A. All system triggersare disabled during the upgrade process. B. Only queries on fixed views execute without errors until you run thecatctl.plscript E. Only connectionsAS SYSDBAare allowed during the upgrade process
Examine the following commands for redefining a table with Virtual Private Database (VPD) policies: <picture> Begin DBMS_RLS.ADD_POLICY ( ......... ); END; begin DBMS_REDEFINITION.START_REDEF_TABLE ( ..... ); END which two statements are true about redefining the table?
A. All the triggers for the table are disabled without changing any of the column names or column types in the table. C. VPD policies are copied from the original table to the new table during online redefinition.
The HR user receives the following error while inserting data into the sales table: Error at line 1: unable to extend table HR.SALES by 128 in tablespace USERS On investigation, you find that the users tablespace uses Automatic Segment Space Management (ASSM). It is the default tablespace for the HR user with an unlimited quota on it. Which two methods would you use to resolve this error? A. Altering the data file associated with the USERS tablespace to ex automatically B. Adding a data file to the USERS tablespace C. Changing segment space management for the USERS tablespace to manual D. Creating a new tablespace with autoextend enabled and changing the default tablespace of the HRuser to the new tablespace E. Enabling resumable space allocation by setting the RESUMABLE_TIMEOUT parameter to a nonzero value
A. Altering the data file associated with the USERS tablespace to ex automatically B. adding a data file to the USERS tablespace
Which three statements are true about Automatic Workload Repository (AWR)? (Choose three.)
A. An AWR snapshot shows the SQL statements that are producing the highest load on the system, based on criteria such as elapsed time and CPU time B. AWR data is stored in memory and in a database. D. The manageability monitor (MMON) process gathers statistics and creates an AWR snapshot that is used by the self-tuning components in a database.
Which two statements are true about a server parameter file (SPFILE)? (Choose two.)
A. An SPFILE can be created from a PFILE or from memory. B. A PFILE can be used to start up a databaseinstance even if an SPFILE exists
Your multitenant container database has three pluggable databases (PDBs): PDB1, PDB2, and PDB3. Which two RMAN commands may be; used to back up only the PDB1 pluggable database?
A. BACKUP PLUGGABLE DATABASE PDB1 while connected to the root container C. BACKUP DATABASE while connected to the PDB1 container
You want execution of large database operations to suspend, and then resume, in the event of space allocation failures. You set the value of the initialization parameter RESUMABLE_TIMEOUT to 3600. Which two statements are true?
A. Before a statement executes in resumable mode, theALTER SESSION ENABLE RESUMABLEstatement must be issued in its session. D. A suspended statement will report an error if no corrective action has taken place during a timeout period.
Which two options can be configured for an existing database by using the Database Configuration Assistant (DBCA)?
A. Configure Label Security B. Database Vault in ORACLE_HOME
Which two statements are true about standard database auditing? (Choose two.)
A. DDL statements can be audited B. statements that refer to standalone procedure can be audited
Which three statements are true about Oracle Restart?
A. It can be configured toautomatically attempt to restart various components after a hardware or software failure. B. While starting any components, it automatically attempts to start all dependencies first and in proper order. E. It runs periodic check operations to monitor the health of Oracle components.
In your database, the STATISTICS_LEVEL parameter is set to TYPICAL and an Automatic Workload Repository (AWR) snapshot is taken every 30 minutes. Which two statements are true about the Automatic Database Diagnostic Monitor (ADDM)? (Choose two.)
A. It measures database performance by analyzing the wait time and CPU time of all non-idle user sessions C. It runs after each AWR snapshot is created and it requires at least two snapshots for analysis
You use multiple temporary tables frequently in your database. Which two are benefits of configuring temporary undo?
A. Performance improves because less redo is written to the redo log. B. Temporary undo reduces the amount of undo stored in undo tablespaces.
Identify three situations in which messages are written to the alert log file. (Choose three.)
A. Rebuilding an index using ALTER INDEX . . . REBUILD fails with an ORA-01578: ORACLE data block corrupted (file # 14, block # 50)" D. Updating a record in a table returns "ORA-00060: deadlock detected while waiting for resource" F. Running a query on a table returns "ORA-01578: ORACLE data block corrupted (file # 4, block # 131)"
you execute this command: SQL> create tablespace lmtbsb DATAFILE ' ' size 50m extent management local; which two statements are true about segment space management for segments in this tablespace?
A. Space utilization inside segments is mapped by bitmaps D. the PCTUSED storage parameter has no effect on segments created in this tablespace
Which two statements are true about availability audit features after migration to unified auditing?
A. The ability of users to audit their own schema objects is not available in the post-migrated database. D. Mandatory auditing of audit administrative actions is available in the post-migrated database
Examine the following impdp command to import a database over the network from a pre-12c Oracle database (source): $> imdp <user name> full=y netowkr_link=hrdb_test transportable=always transport_datafiles =........ Which three are prerequisites for successful execution of the command
A. The import operation must be performed by a user on the target database by a user with theDATAPUMP_IMP_FULL_DATABASE role, and the database link must connect to a user with the DATAPUMP_EXP_FULL_DATABASE role on the source database. B. All the user-defined tablespaces must be in read-only mode on the source database. D. The source and target database must be running on the same operating system (OS) with the same endianness
In order to exploit some new storage tiers that have been provisioned by a storage administrator, the partitions of a large heap table must be moved to other tablespaces in your Oracle 12c 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 it at night and during weekends. Minimal disrupt ion to availability is required. Which three statements are true about this requirement?
A. The partitions can be moved online to new tablespaces C. The partitions can be compressed in the same tablespaces. D. The partitions can be compressed in the new tablespaces.
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 theSYSAUX tablespace to another tablespace.
Examine the parameter settings in your local ORCL database: DB_DOMAIN=us.example.com DB_NAME=orcl REMOTE_OS_AUTHENT=true GLOBAL_NAMES=TRUE examine the parameter settings for the remote sales database: .......... As the SYS user, you issue the following command on ORCL: CREATE PUBLIC DATABASE LINK sales.us.example.com USING 'sales1'; The sales1 tnsnames entry points to the sales database instance Which two are requirements to ensure that you can successfully connect by using this database link?
A. TheGLOBAL_NAMESinitialization parameter should be set toFALSEin theORCLdatabase. C. Local database users who use this database link should have accounts and the required privileges in the remote database.
You set the following parameters in the parameter file and restart the database instance: memory_target=500m pga_aggregate_target=90M sga_target=270M which two statements are true?
A. TheMEMORY_MAX_TARGETparameter is automatically set to 500 MB. D. The lower limits of thePGA_AGGREGATE_TARGETandSGA_TARGETparameters are set to 90 MB and 270 MB respectively.
Identify three valid options for adding a pluggable database (PDB) to an existing multitenant container database (CDB).
A. Use the CREATE PLUGGABLE DATABASE statement to create a PDBusing the files from the SEED D. Use the DBMS_PDB package to plug an Oracle 12c non-CDB database into an existing CDB.
You are administering a database stored in Automatic Storage Management (ASM). You use RMAN to back up the database and the MD_BACKUP command to back up 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. C. Use the MD_RESTORE command to restore the disk group with the changed disk group specification, failure group specification, name, andother attributes and use RMAN to restore the data. 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.
Which two statements are true about Oracle Data Pump export and import operations?
A. You cannot specify how partitioned tablesshould be handled during an import operation. E. Metadata that is exported and imported can be filtered based on objects and object types
Which three statements are true about server-generated alerts? (Choose three.)
A. a server-generated alerts notify administrators of problems that cannot be resolved automatically B. alters are not issued for locally managed read-only tablespaces E. when an alert is cleared, it is moved to the DBA_ALERT_HISTORY view
Your database is configured in ARCHIVELOG mode, and a daily full database backup is taken by using RMAN. Control file autobackup is configured. Loss of which three database files can lead to an incomplete recovery? (Choose three.)
A. inactive online redo file group C. a data file belonging to the SYSAUX tablespace E. active online redo logle group
Which four operations performed after the Oracle Restart installation are automatically added to the Oracle Restart configuration?
A. listener configured by using NETCA B. database service created by using SRVCTL D. database created by using DBCA E. ASM instance created by using ASMCA
You want to upgrade an Oracle Database running Oracle Database 11g to Oracle Database 12c. Which three tasks should be performed before a manual upgrade?
A. runningpreupgrad.sqlin Oracle Database 11g to generate fix-up scripts and a log file C. copying the initialization parameter file to the new Oracle home D. copying the password file to the new Oracle home
For which three requirements would you use the Database Resource Manager?
A. specifying an idle time limit that applies to sessions that are idle andblocking other sessions B. limited the degress of parallelism of operations performed by user sessions in a consumer group C. Specifying the maximum number of concurrent sessions allowed for a user
Which two statements are true about SQL *Loader Express Mode in an Oracle 12c database?
A. the DEGREE_OF_PARALLELISM parameter is set to AUTO C. If no data file is specified, it assumes the data file to be<table-name>.datin the current directory and uses it.
Which three factors influence the optimizer's choice of an execution plan?
A. the optimizer_mode initialization parameter D. object statistics in the data dictionary E. fixed baselines
Which three factors influence the optimizer's choice of an execution plan? A. theoptimizer_mode initialization parameter B. operating system (OS) statistics C. cardinality estimates D. object statistics in the data dictionary E. fixed baselines
A. the optimizer_mode initialization parameter D. object statistics in the data dictionary E. fixed baselines
You install "Oracle Grid Infrastructure for a standalone server" on a host on which the ORCL1 and ORCL2 databases both have their instances running. Which two statements are true?
A.All databases subsequently created by using the Database Configuration Assistant (DBCA) are automatically added to the Oracle Restart configuration. B. Thesrvctl add databasecommand must be used to addORCL1andORCL2to the ORACLE Restart configuration.
A database is stored in an Automatic Storage Management (ASM) disk group, disk group, DGROUP1 with SQL: SQL> create diskgroup statement...... There is enough free space in the disk group for mirroring to be done. What happens if the CONTROLLER1 failure group becomes unavailable due to error of for maintenance?
ASM does not mirror any data until the controller failure group is brought back online, and newly allocated primary allocation units (AU) are stored in the controller2 failure group, without mirroring
Examine the following command; ALTER SYSTEM SET enable_ddl_logging = TRUE; Which statement is true?
All DDL commands are logged in XML format in the alert directory under the Automatic Diagnostic Repository(ADR) home.
Which statement is true about redo log files during instance recovery?
All redo log entries in the current and active logs are applied to data files to reconstruct changes made after the most recent checkpoint.
Which statement is true redo log files during instance recovery?
All redo log entries in the current and active logs are applied to data files to reconstruct changes made after the most recent checkpoint.
What is the effect of setting the STATISTICS_LEVEL initialization parameter to BASIC?
Automatic Workload Repository (AWR) snapshots are not generated automatically.
You plan to install the Oracle Database 12c software. You want to use Oracle Automatic Storage Management (ASM) for storage and Oracle Restart for managing components. Oracle Grid Infrastructure for a Standalone Server is already installed on the server. Which three statements must be true for successful installation of the Oracle Database 12c software?
B. An operating systemOSDBAgroup must be created for userswith theSYSDBAsystem privilege. C. The Oracle Database 12c software owner and the Oracle Grid Infrastructure owner must belong to the same Oracle Inventory Group. E. An operating systemOSOPERgroup be created for users with theSYSOPERsystem privilege.
Your database instance has started using an SPFILE. Examine the RMAN configuration settings: Configure retention policy to redundancy 1l # default ......... configure controlfile autobackup format for device type disk to '%F'; default you execute the command: RMAN> backups as copy tablespace test; which three types of files are backed up by using this command?
B. Control file C. SPFILE E. data file(s)
Which two statements are true about extents?
B. Data blocks in an extent are logically contiguous but can be non-contiguous on disk. C. The blocks of a newly allocated extent, although free, may have been used before.
Identify three benefits of Unified Auditing
B. It improves overall auditing performance. D. The audit trail cannot be easily modified because it is read-only. E. Itautomatically audits Recovery Manager (RMAN) events
Which two statements are true about Automatic Storage Management (ASM)? (Choose two.)
B. It spreads files proportionally across all disks in a diskgroup, aiming to ensure that all the disks in a diskgroup have the same I/O load E. It mirrors data at the allocation unit (AU) level across failure groups within anormal or high redundancy diskgroup
Which three tools or tasks are run by default as part automated maintenance tasks? (Choose three.)
B. Optimizer Statistics gathering D. Segment Advisor E. Automatic SQL tuning Advisor
You install Oracle Grid Infrastructure for a standalone server. Which two components are automatically included in the Oracle Restart configuration? (Choose two.)
B. Oracle Notification services E. Oracle CSSD service
Which two statements are true about the Automatic Database Diagnostic Monitor (ADDM)? (Choose two.)
B. The ADDM runs after each AWR snapshot is collected automatically by MMON C. The results of the ADDM analysis are stored in the Automatic Workload Repository (AWR)
Which three statements are true about the Pre-Upgrade Information Tool?
B. Thepreupgrade_fixups.sqlscript is created to list and describe issues in the source database. C. A log file,preupgrade.log, is created that contains the output of the Pre-Upgrade Information tool. F. Theportupgrade_fixups.sqlscript is created to address issues that can befixed after a database has been upgraded.
Which two statements are true?
B. a role can be granted to other roles C. a role can contain both system and object privileges
The DEFERRED_SEGMENT_CREATION parameter is set to TRUE in your database instance. You execute the following command to create a table: SQL> create table acct1 (ac_no number, ac_desc varchar2(25), amount number (10,2)); which two statements are true?
B. a segment is allocated when the first row is inserted in the table E. a segment is allocated for the table if the ALTER TABLE .... ALLOCATE EXTENT command is issued
Which three are activities performed by SMON?
B. applying online redo during instance recovery C. cleaning up temporary segments that are no longer needed F. recovering failed transactions that were skipped during instance recoverybecause of file-read or tablespace offline errors
You plan to install the Oracle Database 12c software on a new server. The database will use Automatic Storage Management (ASM) and Oracle Restart. Oracle Grid Infrastructure for a standalone server is already installed on the server. You want to configure job role separation. You create the following operating system users and groups: - The user oracle as the owner of the Oracle database installation - The user grid as the owner of Oracle Grid Infrastructure - The group oinstall as an Oracle Inventory group - The group dba as the OSDBA group for Oracle database The group asmdba as the OSDBA group for Oracle ASM - The group asmadmin as the administration privileges group for Oracle ASM - The group asmoper as the group for Oracle ASM Which two additional tasks should you perform with regard to the OS-level owners and groups?
B. assigningoinstallas the primary group for theoracleuser D. creating OS groups associated with theOSBACKUPDBA,OSDGDBA, andOSKMDBAsystem privileges
Which three functions can be performed by the SQL Tuning Advisor? (Choose three.)
B. recommending restructuring of SQL statements that have suboptimal plans C. checking schema objects for missing and state statistics E. generating SQL profiles
Identify three uses of the CROSSCHECK command (Choose three.)
B. to synchronize logical backup records with physical files in backup storage Do update information about backups that are deleted, corrupted, or inaccessible in a recovery catalog or control file E. to update the recovery catalog or control file if archived log files are deleted withoperating system commands
Your database instance has the following parameter setting: OS_AUTHENT_PREFIX = OPS$ You execute the following command: SQL> create user ops$guest_user identified externally default tablespace users; And then grant OPS$GUEST_USER the CREATE SESSION privilege. Which two statements are true?
C. A localGUEST_USEROS account should exist beforeGUEST_USERcan log on to the database. D. GUEST_USERcan log on to the database without specifying a username and password
Examine this command: SQL > exec DBMS_STATS.SET_TABLE_PREFS ('SH', 'CUSTOMERS', 'PUBLISH', 'false'); Which three statements are true about the effect of this command?
C. Any existing statistics for the CUSTOMERS table are still available to the optimizer at parse time. D. Statistics gathered on the CUSTOMERS table when schema stats are gathered are stored as pending statistics. E. Statistics gathered on the CUSTOMERS table when database stats are gathered are stored as pending statistics.
Which three statements are true about space usage alerts?
C. Database alerts can provide warnings about low space availability at both tablespace and segment levels D. Alerts are not issued for locally managed tablespaces that are offline or in read-only mode E. A newly created locally managed tablespace is automatically assigned the default threshold values defined for a database.
Which two are prerequisites for performing a flashback transaction?
C. EXECUTE privilege on the DBMS_FLASHBACK package must be granted to the user flashing back transaction. D. Supplemental logging must be enabled
Which two actions does an incremental checkpoint perform?
C. It advances the checkpoint position in the checkpoint queue. D. It writes the checkpoint position to the control file.
Which two statements are true about the (PMON) background process in Oracle Database 12c
C. It kills sessions that exceed idle time E. it frees resources held by abnormally terminated processes
Which three factors influence the optimizer's choice of an execution plan? SQL> select name, active_sessions, que_length, consumed_cpu ..... <chart> which two statements are true?
C. The CPU_WAIT_TIME column indicates the total time that sessions in the consumer group waited for the CPU due to resource management. E. A user belonging to the DSS__QUERIES resource consumer group can create a new session but the session will be queued.
Which two statements are true about Automatic Database Diagnostic Monitor (ADDM)?
C. it results are stored in AWR D. It runs automatically after each SWR snapshot is created and requires at ;east two snapshots for analysis
Your single-instance Oracle 12c database home currently supports conventional auditing and uses Automatic Storage Management (ASM). You want to enable unified auditing by executing the command: $ make -f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=$ORACLE_HOME Which two steps should you perform before executing this command?
C. stop the listener D. shut down the database instance
You are using RMAN to back up your database. All the data files are in read/write mode examine the RMAN configuration parameters <configure parameters> which two statements are true about a whole consistent backup of a database running in ARCHIVELOG mode?
C. the database must be in MOUNT state to take the backup E. the system change number (SCN) is the same for all the data files in the backup
Which two files must you copy from the Oracle home of the database that is being upgraded to the new Oracle home for Oracle Database 12c?
C. the initialization parameter file D. the password file
You want to create a file watcher and an event-based job for detecting the arrival of files on the local server from various locations. To achieve this, you enable the raising of file arrival events from remote systems. Which two conditions must be satisfied to receive file arrival events from a remote system?
C. the local database must be set up to run remote external jobs D. The remote system's scheduler agent must be registered with your database
As a user of the ORCL database, you establish a database link to the remote HQ database such that all users in the ORCL database may access tables only from the SCOTT schema in the HQ database. SCOTT's password is TIGER. The service mane "HQ" is used to connect to the remote HQ database. Which command would you execute to create the database link? A. CREATE DATABASE LINK HQ USING 'HQ'; B. CREATE DATABASE LINK HQ CONNECT TO CURRENT_USER USING 'HQ'; C. CREATE PUBLIC DATABASE LINK HQ CONNECT TO scott IDENTIFIED BY tiger USING 'HQ'; D. CREATE DATABASE LINKHQ CONNECT TO scott IDENTIFIED BY tiger USING 'HQ';
CREATE PUBLIC DATABASE LINK HQ CONNECT TO scott IDENTIFIED BY tiger USING 'HQ';
You want to schedule a job to rebuild a table's indexes after a bulk insert, which must be scheduled as soon as a file containing data arrives on the system. What would you do to accomplish this?
Create a file watcher and an event-based job for bulk insert and then create another job to rebuild indexes on the table.
Which two tasks can be performed on an external table? A. partitioningthe table B. creating an invisible index C. updating the table by using an update statement D. creating a public synonym E. creating a view
D. Create a public Synonym E. creating a view
Which two statements are true about the logical storage structure of an Oracle database? A. An extent contains data blocks that are always physically contiguous on disk. B. An extent can span multiple segments. C. Each data block always corresponds to one operating system block. D. It is possible to have tablespaces of different block sizes. E. A data block is the smallest unit of I/O in data files
D. It is possible to have tablespaces of different block sizes E. A data block is the smallest unit of I/O in data files
In your Oracle 12c database, you invoke SQL *Loader Express Mode command to load data: $> sqlldr hr/hr table=employees Which two statements are true about this command? (Choose two.)
D. It succeeds with default settings if the EMPLOYEES table belonging to the HR schema is already defined in the database E. It succeeds even if the HR user does not have the CREATE DIRECTORY privilege
Examine the parameters: <chart> Users complain that their sessions for certain transactions hang. You investigate and discover that some users fail to complete their transactions, causing other transactions to wait on row-level locks. Which two actions would you take to prevent this problem? (Choose two.)
D. Use Database Resource Manager to automatically kill the sessions that are idle and are blocking other sessions E. Decrease the IDLE_TIME resource limit in the profiles assigned to blocking users
The HR user updates the salary of one of the employees in the non-partitioned EMPLOYEES table, but does not commit the transaction. Which two types of lock exist in this situation?
D. row level lock on the row being updated E. shared lock on the EMPLOYEES table
A database uses Automatic Storage Management (ASM) as database storage, which has a diskgroup, DATA1, which is created as follows: SQL> create diskgroup data1 normal redundancy failgroup failgrp1 disk ' ' ...... what happens when the FAILGRP1 failure group is corrupted
Data in theFAILGRP1failure group is moved to theFAILGRP2failure group and rebalancing is started
You upgraded from a previous Oracle database version to Oracle Database version to Oracle Database 12c. Your database supports a mixed workload. During the day, lots of insert, update, and 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?
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.
The HR user owns the BONUSES table. HR grants privileges to the user TOM by using the command: SQL> GRANT SELECT ON bonuses TO tom WITH GRANT OPTION; TOM then executes this command to grant privileges to the user JIM: SQL> GRANT SELET ON hr.bonuses TO jim; Which statement is true?
HRcan revoke theSELECT ON HR.BONUSESprivilege fromTOM, which will automatically revoke theSELECT ON HR.BONUSESprivilege fromJIM.
Examine the following steps: A DBA grants the CREATE TABLE system privilege with ADMIN OPTION to the user SIDNEY. SIDNEY grants the CREATE TABLE system privilege to the HR user. Which statement is true?
HRstill retains the CREATE TABLE system privilege if the DBA revokes theCREATETABLEprivilegefromSIDNEY.
You want to prevent a group of users in your database from performing long-running transactions that consume huge amounts of space in the undo tablespace. If the quota for these users is exceeded during execution of a data manipulation language (DML) statement, the operation should abort and return an error. However, queries should still be allowed, even if users have exceeded the undo space limitation. How would you achieve this?
Implement a Database Resource Manager plan
In your database instance, the UNDO_RETENTION parameter is set to 1000 and undo retention is not guaranteed for the fixed size undo tablespace. Which statement is true about undo retention?
Inactive undo is retained for at least 1000 seconds if free undo space is available
Which statement is true about the Log Writer process A. It writes when it receives a signal from the checkpoint process (CKPT). B. It writes concurrently to all members of multiplexed redo log groups. C. It writes after the Database Writer process writes dirty buffers to disk. D. It writes when a user commits a transaction
It Writes when a user commits a transaction
Your database is configured in ARCHIVELOG mode. Examine the RMAN configuration parameters <configuration parameters> Examine the command: RMAN> BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT what is the outcome?
It executes successfully and creates a backup set of the database along with archived log files and then deletes the original archived logfiles
Your database is running in NOARCHIVING mode. Examine the following parameters: <chart> you execute the following command after performing a STARTUP MOUNT SQL> ALTER DATABASE ARCHIVELOG; which statement is true about the execution of the command
It executes successfully and sets the Fast Recovery Area as the local archive destination.
You executed the following command to create a password file in the database server: $ orapwd file = orapworcl entries = 5 ignorecase=N Which statement describes the purpose of the above password file?
It is used by Oracle to authenticate users for remote database administrator
You plan to implement the distributed database system in your company. You invoke Database Configuration Assistant (DBCA) to create a database on the server. During the installation, DBCA prompts you to specify the Global Database Name. What must this name be made up of?
It must be made up of a database name and a domain name.
What is the benefit of running the catctl.pl script during an upgrade of a pre-12c database to an Oracle 12c database?
It provides parallel upgrade options to finish the upgrade process with a reduced down time.
Automatic Shared Memory Management (ASMM) is enabled for your database instance. You execute the following command: SQL> ALTER SYSTEM SET DB_CACHE_SIZE = 100M; Which statement is true?
It succeedsand the minimum size for the DEFAULT buffer pool is set to 100M.
You determine that database performance is sub-optimal due to hard parsing statements. Automatic Shared Memory Management (ASMM) is disabled for your database instance. Which tool would you use to get advice on how to improve performance?
Memory Advisor for the shared pool
Examine the memory-related parameters set in the SPFILE of an Oracle database: memory_max_target=6g memory_target=5g ....... sga_target=0 which statement is true?
Memory is dynamically re-allocated between the SGA and PGA as needed
You have just completed a manual upgrade of an Oracle 11g Database to Oracle Database 12c. The Post-Upgrade Status Tool reports an INVALID status for some of the components after the upgrade. What must you do first in this situation to attempt to fix this problem?
Runutlrp.sqlto recompile stored PL/SQL and Java code and check theDBA_REGISTRYview.
You create an Oracle 12c database and then import schemas that are required by an application which has not yet been developed. You want to get advice on creation of or modifications to indexes, materialized views and partitioning in these schemas. What must you run to achieve this?
SQL Access Advisor with a hypothetical worklaod
Your database supports a Decision Support System (DSS) workload that involves the execution of complex queries. Currently, the database is running with peak workload. You want to analyze some of the most resource-intensive statements cached in the library cache. What must you run to receive recommendations on the efficient use of indexes and materialized views to improve query performance?
SQL access Advisor
You want to load data from a large file into your database without causing an overhead on the SGA. Which tool would you use
SQL* LOADER with a direct data path
Which statement is true about Oracle Net Listener?
Service registration with thelistener is performed by the process monitor (LREG) process of each database instance.
You want to reduce fragmentation and reclaim unused space for the SALES table but not its dependent objects. During this operation, you want to ensure the following: - Long-running queries are not affected. - No Extra Space is used - Data manipulation language (DML) operations on the table succeed at all times throughout the process. - Unused space is reclaimed both above and below the high water mark. Which ALTER TABLE option would you recommend?
Shrink space cascade
Examine the following command: SQL> DBMS_STATS. SET_TABLE_PREFS ('SH', 'CUSTOMERS', 'PUBLISH', 'false'); What is the effect of executing this command?
Statistics subsequently gathered on theCUSTOMERStable are stored as pending statistics
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 restore and recover the data file in ACCOUNTS_PDB?
Take the USERDATA tablespace offline in ACCOUNTS_PDB
You are administering a database that supports an OLTP workload. The default UNDO tablespace is a fixed size tablespace with the RETENTION NOGUARATNEE clause and undo retention set to 12 minutes. The user SCOTT queries a large table during peak activity. The query runs for more than 15 minutes and then SCOTT receives the following error: ORA-01555: snapshot too old Which is possible reason for this?
The Oracleserver is unable to generate a read-consistent image for a block containing updates.committed after the query began.
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?
The STATISTICS_LEVEL parameter is set to all whenever this PDB is re-opened.
The user SCOTT owns the CUST table that is placed in the SALES tablespace. The user SCOTT opens a session and executes commands as follows: SQL> INSERT INTO cust VALUES(101, 'JACK'); 1 row created. SQL> INSERT INTO cust VALUES(102, 'SMITH'); 1 row created. As a DBA, you execute the following command from another session: ALTER TABLESPACE sales READ ONLY; Which statement is true regarding the effect of this command on the transaction in Scott's session?
The command hangs until all transactions on theobjects in the tablespace commit or rollback, and then the tablespace is placed in readonly mode.
Your database is configured in ARCHIVELOG mode, and daily full database backups are taken. RMAN is configured to perform control file autobackups. which statement is true about the loss of a duplexed control file?
The database instance aborts and a control filerestore operation does not require media recovery
What happens if a maintenance window closes before a job that collects optimizer statistics completes A. The job is terminated and the gathered statistics are not saved. B. The job is terminated but the gathered statistics are not published. C. The job continues to run until all statistics are gathered. D. The job is terminated and statistics for the remaining objects are collected the next time the maintenance window opens.
The job continues to run until all statistics are gathered.
Examine the query and its output: SQL> select reason, metric_value from dba_outstanding_alerts <chart> after 30 minutes, you execute the same query ........ what might have caused three of the alerts to disappear?
The threshold alerts werecleared and transferred to DBA_ALERT_HISTORY.
Which action takes place when a file checkpoint occurs?
TheDatabase Writer process (DBWn) writes all dirty buffers in the buffer cache to data files.
You plan to create a database by using the Database Configuration Assistant (DBCA), with the 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 the execution of complex BI queries scheduled at night Which DBCA option must you choose to create the database A. a General Purpose database template with defaultmemory allocation B. a Data Warehouse database template, with the dedicated server mode option and AMM enabled C. a General Purpose database template, with the shared server mode option and Automatic Memory Management (AMM) enabled D. a default database configuration
a General Purpose database template, with the shared server mode option and Automatic Memory Management (AMM) enabled
You want to create a role that: - is protected from unauthorized usage - does not use a password embedded in the application source code or stored in a table - is enabled for a user based on security policies defined in a PL/SQL package How would you create this role?
as a secure application role
Which statement is true about Enterprise Manager (EM) express in Oracle Database 12c?
by default, EM express is available for a database after database creation
Your production database uses file system storage. You want to move storage to Oracle Automatic Storage Management (ASM). How would you achieve this?
by using RMAN
An application repeatedly accesses small lookup tables, causing a lot of physical I/O operations. What do you recommend to minimize this?
configure the KEEP buffer cache and alter the tables to use the KEEP cache
What action must you take to ensure complete database recovery till the point of failure?
configure the database to run in ARCHIVELOG mode
Which task is performed by a background process in a database instance?
copying online redo log files to offline storage
Your database has been running with a peak load for the past hour. You want to preserve the performance statistics collected during this period for comparison when you analyze the performance of the database later. What must you do to achieve this?
create a baseline on a pair of snapshots that span the peak load period
What should you do to ensure that a job stored minimal job metadata and runtime data on disk, and uses only existing PL/SQL programs?
create a light weight job
What is the outcome of the SHUTDOWN ABORT command?
dirty buffers in the buffer cache and unwritten redo are not written to the data files and redo log files respectively
Your production database PROD uses file system storage. You want to migrate storage including the Fast Recovery Area for the PROD database to Oracle Automatic Storage Management (ASM) by using RMAN. You back up the entire database. What should the next step be in this migration process?
disabling oracle flashback database if enabled
Which activity is audited by default and recorded in the operating system audit trail irrespective of whether or not database auditing is enabled?
execution of SQL statements by users connected with the SYSDBA privilege
Which statement is true about using the Export/Import method for migrating data when upgrading to Oracle Database 12c?
it allows migration of a database directly over network links
Which statement is true about using the Database Upgrade Assistant (DBUA) to upgrade your database from Oracle Database 11g to Oracle Database 12c?
it automatically makes necessary changes to oracle environment variables
Which statement is true about the Oracle central inventory directory (oraInventory)?
lf an OFA-compliant path is not created and theORACLE_BASEenvironment variable is not set during an Oracle Database installation, the Oracle Inventory directory is placed in the home directory of the user that is performing the installation.
Examine the structure of the sales table, which is stored in a locally managed tablespace with Automatic Segment Space Management (ASSM) enabled. <chart> You want to perform online segment shrink to reclaim fragmented free space below the high water mark. what should you ensure before the start of the operation?
row movement is enabled
Automatic Shared Memory Management is enabled for your database instance. You notice that there are SQL statements performing poorly because of repeated parsing activity. Which action generates recommendations to overcome the performance issues?
running the memory advisor for the SGA
Which task would you recommend before using the Database Upgrade Assistant (DBUA) to upgrade a single-instance Oracle 11g R2 database to Oracle Database
running the pre-upgrade information tool
Your database is open and the listener LISTNENER is up. You issue the command: LSNRCTL> RELOAD What is the effect of reload on sessions that were originally established by listener?
the sessions are not affected and continue to function normally
Your database is open in read/write mode and multiple users are connected to the database instance. You execute the following command: SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION; What would be the effect on current sessions?
they are not affected
You want to distribute a set of structured data to your customers who can integrate this data into their existing databases irrespective of the platform. Which method provides the fastest way of achieving this?
using RMAN transportable tablespace operation
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 find that buffer busy waits is caused by contention on data blocks. Which option would you consider first to decrease the wait event immediately?
using automatic segment space management (ASSM)
When is the UNDO_RETENTION parameter value ignored by a transaction?
when the undo tablespace is of a fixed size and retention guarantee is not enabled
