NEW QUESTION 1

You upgraded Oracle Database 10g to Oracle Database 11g. How would this affect the existing users' passwords?

• A. All passwords automatically become case-sensitive.
• B. All passwords remain non-case-sensitive till they are changed.
• C. All passwords remain non-case-sensitive and cannot be changed.
• D. All passwords remain non-case-sensitive until their password attribute in the profile is altered.

Answer: B

NEW QUESTION 2

What is the danger associated with stopping a running job by using the STOP_JOB procedure?

• A. The job will need to be reenabled before it will execute again.
• B. The job may hold locks on objects referenced within it.
• C. All jobs within the job group will also be stopped.
• D. The job may leave data in an inconsistent state.
• E. There is no danger in using the STOP_JOB procedure.

Answer: D

NEW QUESTION 3

You are managing the APPPROD database as a DBA. You plan to duplicate this database in the same system with the name DUPDB. You issued the following RMAN commands to create a duplicate database:

Which three are the prerequisites for the successful execution of the above command? (Choose three.)

• A. The source database should be open.
• B. The target database should be in ARCHIVELOG mode if it is open.
• C. RMAN should be connected to both the instances as SYSDBA.
• D. The target database backups should be copied to the source database backup directories.
• E. The password file must exist for the source database and have the same SYS user password as the target.

Answer: BCE

NEW QUESTION 4

Within a resource-plan definition, what differentiates a top-level plan from a subplan?

• A. A subplan has the PLAN_SUB parameter value set to SUB.
• B. A top-level plan has the GROUP_OR_PLAN parameter set to the name of the subplan in the resource-plan definition.
• C. There is no difference in the resource-plan definition.
• D. A subplan always has the CPU_MTH parameter value set to RATIO.
• E. The string TOP_LEVEL is appended to the name of top-level resource plans.

Answer: C

NEW QUESTION 5

You plan to collect the Automatic Workload Repository (AWR) data every Monday morning for a month. You want Oracle Database to automatically create a baseline every Monday and remove the old baseline. What is the correct action to achieve this?

• A. Create and populate a SQL Tuning Set from the AWR on every Monday.
• B. Change the RETENTION setting for the AWR snapshots to 7 days on Monday.
• C. Create a repeating baseline template.
• D. Insert a finding directive for future ADDM tasks.

Answer: C

NEW QUESTION 6

You can back up the RMAN recovery catalog with RMAN.

• A. True
• B. False

Answer: A

Explanation:
When backing up the recovery catalog database, you can use RMAN to make the backups. Refer to here.

NEW QUESTION 7

You backed up the database at 8 a.m. today using an online backup. Accounting made a large change to the underlying data between 10 a.m. and noon.
Which of the following actions would ensure that the changes could be recovered using the 8 a.m. backup?

• A. Create a manual incremental online database backup.
• B. Back up all the archived redo logs generated since the 8 a.
• C. backup.
• D. Create a brand-new backup after all the changes have been applied.
• E. There is no way to make the changes recoverable based on the 8 a.
• F. backup.
• G. Perform an online backup of the tablespace(s) that contained changed data.

Answer: B

NEW QUESTION 8

Evaluate the following code:

What is the outcome of this block of code?

• A. It creates a task and workload, and executes the task.
• B. It creates a task and workload but does not execute the task.
• C. It produces an error because a template has not been created.
• D. It produces an error because the SQL Tuning Set has not been created.

Answer: A

NEW QUESTION 9

A shoot-out has erupted between your MS development teams using .NET and your Linux development teams using Java. Knowing that your database is in danger, which command would you use to back up your NOARCHIVELOG mode database using RMAN with compression?

• A. backup database all
• B. backup compressed database
• C. backup as compressed backupset database;
• D. backup as compressed backup database plus archivelog all;
• E. backup as compressed backupset database plus compress archivelog all;

Answer: A

Explanation:

C:\Users\albo\Desktop\1-1.jpg
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 02/23/2014 11:46:01
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode RMAN>

NEW QUESTION 10

Consider the following scenario for your database:
✑ Backup optimization is enabled in RMAN.
✑ The recovery window is set to 7 days in RMAN.
✑ The most recent backup to disk for the TOOLS tablespace was taken on November 3, 2007.
✑ The TOOLS tablespace is read-only since November 4, 2007.
On November 23, 2007, you issue the RMAN command to back up the database to disk. Which statement is true regarding the backup of the TOOLS tablespace?

• A. The RMAN backup fails because the TOOLS tablespace is read-only
• B. The RMAN skips the backup of the tablespace because backup optimization is enabled
• C. The RMAN makes backup because optimization can be enabled only for backups to disk
• D. The RMAN makes the backup because no backup of the tablespace exists within the seven day window

Answer: D

NEW QUESTION 11

You execute the following FLASHBACK TABLE command:
SQL> FLASHBACK TABLE emp TO TIMESTAMP TO_TIMESTAMP('2008-01-04 11:00:00','YYYY-MMDD HH24:MI:SS');
Which two statements are correct? (Choose two.)

• A. The emp table that was dropped by mistake earlier is restored.
• B. The FLASHBACK TABLE statement is executed as a single transaction.
• C. The FLASHBACK TABLE statement does not maintain existing indexes on the emp table.
• D. The changes made to the emp table since the specified time are undone if no constraintis violated during flashback.

Answer: BD

Explanation:
FLASHBACK TABLE (Link)
Purpose
Use the FLASHBACK TABLE statement to restore an earlier state of a table in the event of human or application error. The time in the past to which the table can be flashed back is dependent on the amount of undo data in the system. Also, Oracle Database cannot restore a table to an earlier state across any DDL operations that change the structure of the table.
Semantics
During an Oracle Flashback Table operation, Oracle Database acquires exclusive DML locks on all the tables specified in the Flashback list. These locks prevent any operations on the tables while they are reverting to their earlier state.
The Flashback Table operation is executed in a single transaction, regardless of the number of tables specified in the Flashback list. Either all of the tables revert to the earlier state or none of them do. If the Flashback Table operation fails on any table, then the entire statement fails.
At the completion of the Flashback Table operation, the data in table is consistent with table at the earlier time.
However, FLASHBACK TABLE TO SCN or TIMESTAMP does not preserve rowids, and FLASHBACK TABLE
TO BEFORE DROP does not recover referential constraints.
Oracle Database does not revert statistics associated with table to their earlier form. Indexes on table that exist currently are reverted and reflect the state of the table at the Flashback point. If the index exists now but did not yet exist at the Flashback point, then the database updates the index to reflect the state of the table at the Flashback point. However, indexes that were dropped during the interval between the Flashback point and the current time are not restored.
Logical Flashback Features Useful in Backup and Recovery (Link)
The remaining flashback features operate at the logical level. The logical features documented in this chapter are as follows:
✑ Flashback Table
You can recover a table or set of tables to a specified point in time in the past without taking any part of the database offline. In many cases, Flashback Table eliminates the need to perform more complicated point-in-time recovery operations. Flashback Table restores tables while automatically maintaining associated attributes such as current indexes, triggers and constraints, and not requiring you to find and restore application-
specific properties.
"Rewinding a Table with Flashback Table" explains how to use this feature.
✑ Flashback Drop
You can reverse the effects of a DROP TABLE statement.
"Rewinding a DROP TABLE Operation with Flashback Drop" explains how to use this feature.
Note:
Because the logical flashback features have uses not specific to backup and recovery, some documentation for them is located elsewhere in the documentation set.
All logical flashback features except Flashback Drop rely on undo data. Used primarily for providing read consistency for SQL queries and rolling back transactions, undo records contain the information required to reconstruct data as it existed at a past time and examine the record of changes since that past time.

NEW QUESTION 12

Identify the scenarios in which the RMAN CROSSCHECR command is useful. (Choose all that apply.)

• A. To check that the obsolete backups are deleted from the repository records and from the disk.
• B. To update the RMAN repository if you delete archived redo logs with operating system commands.
• C. To update outdated information about backups that disappeared from disk or tape or became corrupted and inaccessible.
• D. To synchronize the actual files on disk or in the media management catalog with the RMAN repository for which the backup was not taken using RMAN.

Answer: BC

Explanation:
Crosscheck are useful because they can do the following: (Link)
✑ Update outdated information about backups that disappeared from disk or tape or became corrupted
✑ Update the repository if you delete archived redo logs or other files with operating system commands
Use the CROSSCHECK command to synchronize the physical reality of backups and copies with their logical records in the RMAN repository.
The CROSSCHECK command checks only objects marked AVAILABLE or EXPIRED in the repository by examining the files on disk for DISK channels or by querying the media manager for sbt channels.
Meaning of Crosscheck Status: EXPIRED
Object is not found either in file system (for DISK) or in the media manager (for sbt). A backup set is EXPIRED if any backup piece in the set is EXPIRED.
The CROSSCHECK command does not delete the repository records of the files that it does not find, but updates their repository records to EXPIRED. You can run DELETE EXPIRED to remove the repository records for expired files and any existing physical files whose status is EXPIRED.
If backups are EXPIRED, then you can re-execute the crosscheck later and determine whether expired backups are available. This precaution is especially useful when you use RMAN with a media manager. For example, if some backup pieces or copies were erroneously marked as EXPIRED because the PARMS channel settings were incorrect, then after ensuring that the files really do exist in the media manager, run the CROSSCHECK BACKUP command again to restore those files to AVAILABLE status. AVAILABLE
Object is available for use by RMAN. For a backup set to be AVAILABLE, all backup pieces in the set must have the status AVAILABLE.

NEW QUESTION 13

Which DBMS_RESOURCE_MANAGER procedure prioritizes consumer-group mappings?

• A. CREATE_MAPPING_PRIORITY
• B. SET_MAPPING_PRIORITY
• C. SET_MAPPING_ORDER
• D. PRIORITIZE_MAPPING_ORDER
• E. This functionality is not available through the DBMS_RESOURCE_MANAGER package.

Answer: B

NEW QUESTION 14

What can you determine about the following linguistic sorts based only on their names?
1. GERMAN
2. FRENCH_M

• A. 1 is a monolingual sort.
• B. 2 is a monolingual sort.
• C. 1 is case insensitive.
• D. Both 1 and 2 are case insensitive.
• E. Case sensitivity is unknown.

Answer: A

NEW QUESTION 15

The OPTIMIZER_USE_PLAN_BASELINES parameter is set to TRUE. The optimizer generates a plan for a SQL statement but does not find a matching plan in the SQL plan baseline.
Which two operations are performed by the optimizer in this scenario? (Choose two.)

• A. The optimizer adds the new plan to the plan history.
• B. The optimizer selects the new plan for the execution of the SQL statement.
• C. The optimizer adds the new plan to the SQL plan baseline as an accepted plan.
• D. The optimizer adds the new plan to the SQL plan baseline but not in the ENABLED state.
• E. The optimizer costs each of the accepted plans in the SQL plan baseline and picks the one with the lowest cost.

Answer: AE

Explanation:
15.2.2 Selecting SQL Plan Baselines
During the SQL plan baseline selection phase, Oracle Database detects plan changes based on the stored plan history, and selects plans to avoid potential performance regressions for a set of SQL statements.
Each time the database compiles a SQL statement, the optimizer does the following:
1. Uses a cost-based search method to build a best-cost plan
2. Tries to find a matching plan in the SQL plan baseline
3. Does either of the following depending on whether a match is found:
✑ If found, then the optimizer proceeds using the matched plan
✑ If not found, then the optimizer evaluates the cost of each accepted plan in the SQL plan baseline and selects the plan with the lowest cost
The best-cost plan found by the optimizer that does not match any plans in the plan history for the SQL statement represents a new plan. The database adds this plan as a nonaccepted plan to the plan history. The database does not use the new plan until it is verified to not cause a performance regression. However, if a change in the system (such as a dropped index) causes all accepted plans to become non-reproducible, then the optimizer selects the best-cost plan. Thus, the presence of a SQL plan baseline causes the optimizer to use conservative plan selection strategy for the SQL statement.
To enable the use of SQL plan baselines, set the OPTIMIZER_USE_SQL_PLAN_BASELINES initialization parameter to TRUE (default). A SQL plan baseline contains one or more accepted plans.
The plan history is the set of plans, both accepted and not accepted, that the optimizer generates for a SQL statement over time, the plans in the baseline form a subset of the plan history. For example, after the optimizer generates the first acceptable plan for a SQL plan baseline, subsequent plans are part of the plan history but not part of the plan baseline.

C:\Users\albo\Desktop\1-1.jpg

NEW QUESTION 16

Which of the following does the recover command not do?

• A. Restore archived redo logs.
• B. Apply archived redo logs.
• C. Restore incremental backups.
• D. Apply incremental backups.
• E. Restore datafile images.

Answer: A

NEW QUESTION 17

View the Exhibit for some of the current parameter settings. A user logs in to the HR schema and issues the following commands:
SQL> CREATE TABLE emp (empno NUMBER(3), ename VARCHAR2(20), sal NUMBER(8,2));
SQL> INSERT INTO emp(empno,ename) VALUES(1,'JAMES');
At this moment, a second user also logs in to the HR schema and issues the following command:
SQL> ALTER TABLE emp MODIFY sal NUMBER(10,2);
What happens in the above scenario? Exhibit:

• A. The second user's session immediately produces the resource busy error.
• B. The second user's command executes successfully.
• C. The second user's session waits for a time period before producing the resource busy error.
• D. A deadlock is created.

Answer: C

NEW QUESTION 18

Your database has experienced a loss of datafile users_01.dbf, which is associated with a tablespace called USERS. The database is still running.
Which answer properly describes the order of the steps that you would use to recover from this error?
1. Shut down the database.
2. Take the users_01.dbf datafile offline with the alter database command.
3. Restore the users_01.dbf datafile from backup media with the required archived redo logs.
4. Restore all users tablespace-related datafiles from backup media.
5. Issue the recover tablespace users command.
6. Issue the recover datafile users_01.dbf command.
7. Start up the database.
8. Bring the users_01.dbf datafile online with the alter database command.

• A. 1, 3, 6, 7
• B. 2, 3, 6, 8
• C. 1, 2,3,6,7
• D. 1, 2, 3, 6, 7, 8
• E. 2, 3,6,5,7

Answer: B

NEW QUESTION 19

Given the following steps, which would be the correct order to create a backup of an Oracle database in NOARCHIVELOG mode?
7. shutdown immediate from RMAN
8. Log into RMAN
9. startup mount from RMAN 10.backup database
11. alter database open
12. backup database plus archivelog delete input

• A. 2,3,1,4,5
• B. 2,1,3,6,5
• C. 1,3,5,4
• D. 2,1,3,5,6
• E. 2,1,3,4,5

Answer: E

Explanation:
Backing Up a Database in NOARCHIVELOG Mode
If a database runs in NOARCHIVELOG mode, then the only valid database backup is a consistent backup. For the backup to be consistent, the database must be mounted after a consistent shutdown. No recovery is required after restoring the backup.
To make a consistent database backup:
1. Start RMAN and connect to a target database.
2. Shut down the database consistently and then mount it.
For example, enter the following commands to guarantee that the database is in a consistent state for a backup:
RMAN> SHUTDOWN IMMEDIATE; RMAN> STARTUP FORCE DBA; RMAN> SHUTDOWN IMMEDIATE; RMAN> STARTUP MOUNT;
3. Run the BACKUP DATABASE command.
For example, enter the following command at the RMAN prompt to back up the database to the default backup device:
RMAN> BACKUP DATABASE;
The following variation of the command creates image copy backups of all data files in the database:
RMAN> BACKUP AS COPY DATABASE;
4. Open the database and resume normal operations. The following command opens the database:
RMAN> ALTER DATABASE OPEN;

NEW QUESTION 20

Identify the activities performed as part of the Automatic SQL Tuning process in the maintenance window? (Choose all that apply.)

• A. generating the SQL profile
• B. testing and accepting the SQL profile
• C. generating a list of candidate SQLs for tuning
• D. adding tuned SQL plans into the SQL plan baseline
• E. tuning each SQL statement in the order of importance
• F. generating baselines that include candidate SQLs for tuning

Answer: ABCE

Explanation:
17.2.1 How Automatic SQL Tuning Works (link)
Oracle Database automatically runs SQL Tuning Advisor on selected high-load SQL statements from the Automatic Workload Repository (AWR) that qualify as tuning candidates. This task, called Automatic SQL Tuning, runs in the default maintenance windows on a nightly basis. By default, automatic SQL tuning runs for at most one hour. You can customize attributes of the maintenance windows, including start and end time, frequency, and days of the week.
After automatic SQL tuning begins, the database performs the following steps:
1. Identifies SQL candidates in the AWR for tuning
Oracle Database analyzes statistics in AWR and generates a list of potential SQL statements that are eligible for tuning. These statements include repeating high-load statements that have a significant impact on the database.
The database tunes only SQL statements that have an execution plan with a high potential for improvement. The database ignores recursive SQL and statements that have been tuned recently (in the last month), parallel queries, DML, DDL, and SQL statements with performance problems caused by concurrency issues.The database orders the SQL statements that are selected as candidates based on their performance impact. The database calculates the impact by summing the CPU time and the I/O times in AWR for the selected statement in the past week.
2. Tunes each SQL statement individually by calling SQL Tuning Advisor
During the tuning process, the database considers and reports all recommendation types, but it can implement only SQL profiles automatically.
3. Tests SQL profiles by executing the SQL statement
If a SQL profile is recommended, the database tests the new profile by executing the SQL statement both with and without the profile. If the performance improvement improves at least threefold, then the database accepts the SQL profile, but only if the ACCEPT_SQL_PROFILES task parameter is set to TRUE. Otherwise, the automatic SQL tuning reports merely report the recommendation to create a SQL profile.
4. Optionally, implements the SQL profiles provided they meet the criteria of threefold
performance improvement The database considers other factors when deciding whether to implement the SQL profile. For example, the database does not implement a profile when the objects referenced in the statement have stale optimizer statistics. SQL profiles that have been implemented automatically show type is AUTO in the DBA_SQL_PROFILES view.
If the database uses SQL plan management, and if a SQL plan baseline exists for the SQL statement, then the database adds a new plan baseline when creating the SQL profile. As a result, the optimizer uses the new plan immediately after profile creation. See Chapter 15, "Using SQL Plan Management". At any time during or after the automatic SQL tuning process, you can view the results using the automatic SQL tuning report. This report describes in detail all the SQL statements that were analyzed, the recommendations generated, and the SQL profiles that were automatically implemented.

C:\Users\albo\Desktop\1-1.jpg

NEW QUESTION 21

Which two statements are true about a duplicate database that is created by using the DUPLICATE command in RMAN? (Choose two.)

• A. It is a copy or a subset of the target database.
• B. It is opened in RESTRICT mode after a duplicating operation.
• C. It is created by using backups and archived redo log files from the target database.
• D. It is created with the same database identifier (DBID) as that of target database.

Answer: AC

Explanation:
Duplicating a Database
Overview of RMAN Database Duplication
Database duplication is the use of the DUPLICATE command to copy all or a subset of the data in a source database. The duplicate database (the copied database) functions entirely independently from the source database (the database being copied).
Purpose of Database Duplication
If you copy a database with operating system utilities instead of the DUPLICATE command, then the DBID of the copied database remains the same as the original database. To register the copy database in the same recovery catalog with the original, you must change the DBID with the DBNEWID utility (see Oracle Database Utilities). In contrast, the DUPLICATE command automatically assigns the duplicate database a different DBID so that it can be registered in the same recovery catalog as the source database.
Backup-Based Duplication
In backup-based duplication, RMAN creates the duplicate database by using pre-existing RMAN backups and copies. This technique of duplication uses one of the following mutually exclusive subtechniques: Duplication without a target database connection, RMAN obtains metadata about backups from a recovery catalog.
Duplication without a target database connection and without a recovery catalog. RMAN obtains metadata about where backups and copies reside from BACKUP LOCATION. Duplication with a target database connection. RMAN obtains metadata about backups
from the target database control file or from the recovery catalog. How RMAN Duplicates a Database
For backup-based duplication, the principal work of the duplication is performed by the auxiliary channels. These channels correspond to a server session on the auxiliary instance on the destination host. For active database duplication the primary work is performed by target channels. RMAN must perform database point-in-time recovery, even when no explicit point in time is provided for duplication. Point-in-time recovery is required because the online redo log files in the source database are not backed up and cannot be applied to the duplicate database. The farthest point of recovery of the duplicate database is the most recent redo log file archived by the source database.
As part of the duplicating operation, RMAN automates the following steps:
1. Creates a default server parameter file for the auxiliary instance if the following conditions are true:
✑ Duplication does not involve a standby database.
✑ Server parameter files are not being duplicated.
✑ The auxiliary instance was not started with a server parameter file.
2. Restores from backup or copies from active database the latest control file that satisfies the UNTIL clause requirements.
3. Mounts the restored or copied backup control file from the active database.
4. Uses the RMAN repository to select the backups for restoring the data files to the auxiliary instance. This step applies to backup-based duplication.
5. Restores and copies the duplicate data files and recovers them with incremental backups and archived redo log files to a noncurrent point in time.
6. Shuts down and restarts the database instance in NOMOUNT mode.
7. Creates a new control file, which then creates and stores the new DBID in the data files.
8. Opens the duplicate database with the RESETLOGS option and creates the online redo log for the new database.

NEW QUESTION 22

In your production database, users report that they are unable to generate reports on an important table because it does not contain any data. While investigating the reason, you realize that another user executed the TRUNCATE TABLE command, which accidentally
caused the data to be lost. Now you want to recover the lost data of the table without affecting objects in other schemas.
Which method must you use to recover the lost data?

• A. Complete Recovery with online redo log
• B. Complete Recovery with archived redo log
• C. Tablespace Point-in-Time Recovery (TSPITR)
• D. Incomplete Recovery with system change number (SCN)

Answer: C

NEW QUESTION 23

Evaluate the following statements:
CREATE TABLE purchase_orders ( po_id NUMBER(4),
po_date TIMESTAMP, supplier_id NUMBER(6), po_total NUMBER(8,2),
CONSTRAINT order_pk PRIMARY KEY(po_id)) PARTITION BY RANGE(po_date) (
PARTITION Q1 VALUES LESS THAN (TO_DATE('1-apr-2007','d-mon-yyyy'), PARTITION Q2 VALUES LESS THAN (TO_DATE('1-jul-2007','d-mon-yyyy'), PARTITION Q3 VALUES LESS THAN (TO_DATE('1-oct-2007','d-mon-yyyy'), PARTITION Q4 VALUES LESS THAN (TO_DATE('1-jan-2008','d-mon-yyyy'));
CREATE TABLE purchase_order_items ( po_id NUMBER(4) NOT NULL,
product_id NUMBER(6) NOT NULL, unit_price NUMBER(8,2),
quantity NUMBER(8),
CONSTRAINT po_items_fk FOREIGN KEY (po_id) REFERENCES purchase_orders(po_id)) PARTITION BY REFERENCE(po_items_fk);
What are the two consequences of the above statements? (Choose two.)

• A. Partitions of PURCHASE_ORDER_ITEMS have system-generated names.
• B. Both PURCHASE_ORDERS and PURCHASE_ORDER_ITEMS tables are created with four partitions each.
• C. Partitions of the PURCHASE_ORDER_ITEMS table exist in the same tablespaces as the partitions of the PURCHASE_ORDERS table.
• D. The PURCHASE_ORDER_ITEMS table inherits the partitioning key from the parent table by automatically duplicating the key columns.
• E. Partition maintenance operations performed on the PURCHASE_ORDER_ITEMS table are automatically reflected in the PURCHASE_ORDERS table.

Answer: BC

NEW QUESTION 24

When you create a backup control file, where is the resulting file written to?

• A. The database user dump destination directory
• B. The database diagnostic destination directory
• C. To $ORACLE_HOME/rdbms
• D. To $ORACLE_HOME/admin
• E. To the directory and filename you specify in the command

Answer: E

NEW QUESTION 25
......