OCA Flashcards
Which three are direct benefits of the multiprocess, multithreaded architecture of Oracle Database 12c when it is enabled?
- Improved parallel Execution performance
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
Which Oracle Database component is audited by default if the unified Auditing option is enabled?
- Oracle Recovery Manager (RMAN)
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 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?
- 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.
- Use the MD_RESTORE command to restore the disk group with the changed disk group specification, failure group specification, name, and other attributes and use RMAN to restore the data.
- 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 statement is true about Oracle Net Listener?
- Service registration with the listener is performed by the process monitor (PMON) process of each database instance.
Which two statements are true about the use of the procedures listed in the v$sysaux_occupants.move_procedure column?
- The procedure may be used for some components to relocate component data to the SYSAUX tablespace from its current tablespace.
- The procedure may be used for some components to relocate component data from the SYSAUX tablespace to another tablespace.
Examine the following commands for redefining a table with Virtual Private Database (VPD) policies:
Which two statements are true about redefining the table?
- The primary key constraint on the EMPLOYEES table is disabled during redefinition.
- VPD policies are copied from the original table to the new table during online redefinition.
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.
- Add a new temp file to the temporary tablespace and drop the temp file that that produced the error.
- Shut down the database instance and then restart the CDB and PDBs.
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.
Identify three benefits of Unified Auditing.
- Decreased use of storage to store audit trail rows in the database.
- It improves overall auditing performance.
- It automatically audits Recovery Manager (RMAN) events.
You create a table with the PERIOD FOR clause to enable the use of the Temporal Validity feature of Oracle Database 12c.
Examine the table definition:
Which three statements are true concerning the use of the Valid Time Temporal feature for the EMPLOYEES table?
- The valid time columns employee_time_start and employee_time_end are automatically created.
- The same statement may filter on both transaction time and valid temporal time by using the AS OF TIMESTAMP and PERIOD FOR clauses.
- Setting the session valid time using DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME sets the visibility for data manipulation language (DML), data definition language (DDL), and queries performed by the session.
Examine the following ALTER command:
SQL> ALTER DISKGROUP dgroup1 UNDROP DISKS;
What is the purpose of the command?
- It cancels all pending disk drops within the disk group.
Tape streaming is not happening while performing RMAN tape backup. On investigation, you find that it is not because of the incremental backup or the empty file backup and that RMAN is sending data blocks to the tape drive fast enough.
What could be a solution to make tape streaming happen during the backup?
- Configure the channel to increase the capacity with the RATE parameter
View the Exhibit and examine the disk groups created at the time of migrating the database storage to Automatic Storage Management (ASM).
Why does the FRA disk group initially have more free space even though both DATA and FRA disk groups are provided with the same size?
- Because the FRA disk group is not configured to support mirroring
You find this query being used in your Oracle 12c database:
Which method a used by the optimizer to limit the rows being returned?
- A view is created during execution and a filter on the view limits the rows to 20 percent of the total rows.
Which three resources might be prioritized between competing pluggable databases when creating a multitenant container database plan (CDB plan) using Oracle Database Resource Manager?
- Maximum Undo per consumer group
- Parallel server limit
- CPU
You created an encrypted tablespace:
You then closed the encryption wallet because you were advised that this is secure.
Later in the day, you attempt to create the EMPLOYEES table in the SECURESPACE tablespace with the SALT option on the EMPLOYEE column.
Which is true about the result?
- It creates the table successfully, and encrypts any inserted data in the EMPNAME column because the wallet needs to be open only for tablespace creation.
On your Oracle Database, you issue the following commands to create indexes:
SQL > CREATE INDEX oe.ord_customer_ix1 ON oe.orders (customer_id, sales_rep_id) INVISIBLE;
SQL> CREATE BITMAP INDEX oe.ord_customer_ix2 ON oe.orders (customer_id, sales_rep_id);
Which two statements are true?
- Both the indexes are updated when a row is inserted, updated, or deleted in the ORDERS table.
- Both the indexes are created: however, only ORD_CUSTOMERS_IX2 is used by the optimizer for queries on the ORDERS table.
Which two statements are true when row archival management is enabled?
- The ORA_ARCHIVE_STATE column visibility is controlled by the ROW ARCHIVAL VISIBILITY session parameter.
- The ORA_ARCHIVE_STATE column is updated manually or by a program that could reference activity tracking columns, to indicate that a row is no longer considered active.
A warehouse fact table in your Oracle 12c Database is range-partitioned by month and accessed frequently with queries that span multiple partitions
The table has a local prefixed, range partitioned index.
Some of these queries access very few rows in some partitions and all the rows in other partitions, but these queries still perform a full scan for all accessed partitions.
This commonly occurs when the range of dates begins at the end of a month or ends close to the start of a month.
You want an execution plan to be generated that uses indexed access when only a few rows are accessed from a segment, while still allowing full scans for segments where many rows are returned.
Which three methods could transparently help to achieve this result?
- B. Using a partial local Index on the warehouse fact table month column with indexing disabled for the table partitions that return a few rows to the queries.
- Using a partitioned view that does a UNION ALL query on the partitions of the warehouse fact table, which retains the existing local partitioned column.
- Using a partial global index on the warehouse fact table month column with indexing disabling for the table partitions that return most of their rows to the queries.
You use the segment advisor to help determine objects for which space may be reclaimed.
Which three statements are true about the advisor given by the segment advisor?
- It may advise the use of segment shrink for tables in dictionary managed tablespaces it the no chained rows.
- It may advise the use of online table redefinition for tables in locally managed tablespaces
- It will detect and advise about chained rows.
You have altered a non-unique index to be invisible to determine if queries execute within an acceptable response time without using this index.
Which two are possible if table updates are performed which affect the invisible index columns?
- The index remains invisible.
* The index is updated by the DML on the table.
In your multitenant container database (CDB) containing same pluggable databases (PDBs), you execute the following commands in the root container:
Which two statements are true?
- The C # # ROLE1 role is created in the root database and all the PDBs.
- Privileges are granted to the C##A_ADMIN user only in the root database.
