1Z0-050 - Oracle Database 11g: New Features for Administrators

Go back to Oracle

Example Questions

In your database, the RESULT_CACHE_MODE parameter has been set to MANUAL in the initialization parameter file. You issued the following command: SQL>SELECT /*+ RESULT_CACHE */ sale_category, sum(sale_amt) FROM sales GROUP BY sale_category; Where would the result of this query be stored? You are working on a CATDB database that contains an Oracle Database version 11.1 catalog schema owned by the user RCO11. The INST1 database contains an Oracle Database version 10.1 catalog schema owned by the user RCAT10. You want the RMAN to import metadata for database IDs 1423241 and 1423242, registered in RCAT10, into the recovery catalog owned by RCO11. You executed the following RMAN commands: RMAN> CONNECT CATALOG rco11/[email protected] RMAN> IMPORT CATALOG rcat10/[email protected]; What happens when you execute the above commands? (Choose all that apply.) Which three activities are supported by the Data Recovery Advisor? You are managing an Oracle Database 11g ASM instance with a disk group dg01 having three disks. One of the disks in the disk group becomes unavailable because of power failure. You issued the following command to change the DISK_REPAIR_TIME attribute from 3.6 hours to 5 hours: ALTER DISKGROUP dg01 SET ATTRIBUTE 'disk_repair_time' = '5h'; To which disks in the disk group will the new value be applicable? You executed the following PL/SQL block successfully: VARIABLE tname VARCHAR2(20) BEGIN dbms_addm.insert_finding_directive (NULL, DIR_NAME=>'Detail CPU Usage', FINDING_NAME=>'CPU Usage', MIN_ACTIVE_SESSIONS=>0, MIN_PERC_IMPACT=>90); :tname := 'database ADDM task4'; dbms_addm.analyze_db(:tname, 150, 162); END; / Then you executed the following command: SQL> SELECT dbms_addm.get_report(:tname) FROM DUAL; The above command produces Automatic Database Diagnostic Monitor (ADDM) analysis ____. You configure your database Instance to support shared server connections. Which two memory areas that are part of PGA are stored in SGA instead, for shared server connection? You plan to control the sessions performing a huge number of I/O operations. Your requirement is to kill the session when it exceeds a specified number of I/Os. Which statement describes a solution to the above? The following command is executed to create the baseline template: SQL> EXECUTE DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE(START_TIME=> TO_TIMESTAMP(SYSDATE+2), END_TIME=>TO_TIMESTAMP(SYSDATE+10), BASELINE_NAME=>'Mybase4', TEMPLATE_NAME=>'Mytemp4', EXPIRATION=>NULL); Which statement is true about the effect of the above command? Which three features work together, to allow a SQL statement to have different cursors for the same statement based on different selectivity ranges? Your database has the SRV1 service configured for an application that runs on middle-tier application server. The application has multiple modules. You enable tracing at the service level by executing the following command: SQL > exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE (`SRV1); The possible outcome and actions to aggregate the trace files are as follows: 1. The command fails because a module name is not specified. 2. A trace file is created for each session that is running the SRV1 service. 3. An aggregated trace file is created for all the sessions that are running the SRV1 service. 4. The trace files may be aggregated by using the trcess utility. 5. The trace files be aggregated by using the tkprof utility. Identify the correct outcome and the step to aggregate by using tkprof utility? Which two statements are true about the Oracle Direct Network File system (DNFS)? Your database initialization parameter file has the following entry: SEC_MAX_FAILED_LOGIN_ATTEMPTS=3 Which statement is true regarding this setting? Observe the following warning in an RMAN session of your database instance: WARNING: new failures were found since last LIST FAILURE command Which statement describes the scenario that must have produced this warning? While performing the backup of the flash recovery area, you notice that one of the archived redo log files is missing. You have multiple destinations for archived redo log files. What implications does it have on the backup of the flash recovery area? View the following SQL statements: Transaction T1 INSERT INTO hr.regions VALUES (5,'Pole'); COMMIT; Transaction T2 UPDATE hr.regions SET region_name='Poles' WHERE region_id = 5; COMMIT; Transaction T3 UPDATE hr.regions SET region_name='North and South Poles' WHERE region_id = 5; You want to back out transaction T2. Which option would you use? A senior DBA asked you to execute the following command to improve performance: SQL> ALTER TABLE subscribe log STORAGE (BUFFER_POOL recycle); You checked the data in the SUBSCRIBE_LOG table and found that it is a large table containing one million rows. What could be a reason for this recommendation? You are working on a CATDB database that contains an Oracle Database version 11.1 catalog schema owned by the user RCO11. The INST1 database contains an Oracle Database version 10.1 catalog schema owned by the user RCAT10. You want the RMAN to import metadata for database IDs 1423241 and 1423242, registered in RCAT10, into the recovery catalog owned by RCO11. You also want to deregister them from the catalog after import. You executed the following commands to achieve this: RMAN> CONNECT CATALOG rco11/[email protected] RMAN> IMPORT CATALOG rcat10/[email protected] DBID=1423241,1423242; What happens if the RCO11 catalog has scripts with the same name as that of the scripts in RCAT10 catalog? USER_DATA is a nonencrypted tablespace that contains a set of tables with data. You want to convert all existing data in the USER_DATA tablespace and the new data into the encrypted format. Which methods would you use to achieve this? (Choose all that apply.) Examine the following command; ALTER SYSTEM SET enable_ddl_logging = TRUE; Which statement is true? Your organization decided to upgrade the existing Oracle 10g database to Oracle 11g database in a multiprocessor environment. At the end of the upgrade, you observe that the DBA executes the following script: SQL> @utlrp.sql What is the significance of executing this script? You enabled an audit policy by issuing the following statements: SQL> AUDIT POLICY ORA_DATABASE_PARAMETER BY SCOTT; SQL> AUDIT POLICY ORA_DATABASE_PARAMETER BY SYS, SYSTEM; For which database users and for which executions is the audit policy now active? Select two. In your Database, the TBS PERCENT USED parameter is set to 60 and the TBS PERCENT FREE parameter is set to 20. Which two storage-tiering actions might be automated when using information Lifecycle Management (ILM) to automate data movement? Your are the DBA supporting an Oracle 11g Release 2 database and wish to move a table containing several DATE, CHAR, VARCHAR2, and NUMBER data types, and the table's indexes, to another tablespace. The table does not have a primary key and is used by an OLTP application. Which technique will move the table and indexes while maintaining the highest level of availability to the application? You issued the following RMAN command to back up the database: RMAN> RUN{ ALLOCATE CHANNEL c1 DEVICE TYPE sbt BACKUP DATABASE TAG quarterly KEEP FOREVER RESTORE POINT FY06Q4;} Which two statements are true regarding the backup performed? (Choose two.) Which of the following information will be gathered by the SQL Test Case Builder for the problems pertaining to SQL-related problems? (Choose all that apply.) Examine the following PL/SQL block: SET SERVEROUTPUT ON SET LONG 10000 ECLARE report clob; BEGIN report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(); DBMS_OUTPUT.PUT_LINE(report); END; Which statement describes the effect of the execution of the above PL/SQL block? You are managing Oracle Database 11g with an ASM storage with high redundancy. The following command was issued to drop the disks from the dga disk group after five hours: ALTER DISKGROUP dga OFFLINE DISKS IN FAILGROUP f2 DROP AFTER 5H; Which statement is true in this scenario? Which three statements are true about using flashback database in a multitenant container database (CDB)? Which statement describes the significance of the CHANGE FAILURE command in RMAN? (Choose all that apply.) Examine the commands executed to monitor database operations: $> conn sys oracle/[email protected] as sysdba SQL > VAR eid NUMBER SQL > EXEC: eid := DBMS_SQL_MONITOR.BEGIN_OPERATION (`batch_job' , FORCED_TRACKING => `Y'); Which two statements are true? To generate recommendations to improve the performance of a set of SQL queries in an application, you execute the following blocks of code: BEGIN dbms_advisor.create_task(dbms_advisor.sqlaccess_advisor,'TASK1'); END; / BEGIN dbms_advisor.set_task_parameter('TASK1','ANALYSIS_SCOPE','ALL'); dbms_advisor.set_task_parameter('TASK1','MODE','COMPREHENSIVE'); END; / BEGIN dbms_advisor.execute_task('TASK1'); dbms_output.put_line(dbms_advisor.get_task_script('TASK1')); END; / The blocks of code execute successfully; however, you do not get the required outcome. What could be the reason? Observe the following PL/SQL block: BEGIN dbms_spm.configure('SPACE_BUDGET_PERCENT', 30); END; Which statement is correct regarding the above PL/SQL block? Which are the prerequisites for performing flashback transactions on your database? (Choose all that apply.) The INV_HISTORY table is created using the command: SQL>CREATE TABLE INV_HISTORY (inv_no NUMBER(3), inv_date DATE, inv_amt NUMBER(10,2)) partition by range (inv_date) interval (numtoyminterval(1,'month')) (partition p0 values less than (to_date('01-01-2005','dd-mm-yyyy')), partition p1 values less than (to_date('01-01-2006','dd-mm-yyyy'))); The following data has been inserted into the INV_HISTORY table : INV_NO INV_DATE INV_AMT 1 30-dec-2004 1000 2 30-dec-2005 2000 3 1-feb-2006 3000 4 1-mar-2006 4000 5 1-apr-2006 5000 You would like to store the data belonging to the year 2006 in a single partition and issue the command: SQL> ALTER TABLE inv_history MERGE PARTITIONS FOR(TO_DATE('15-feb-2006','dd-mon-yyyy')), FOR(TO_DATE('15-apr-2006')) INTO PARTITION sys_py; What would be the outcome of this command? Which statements are true regarding the concept of problems and incidents in the fault diagnosability infrastructure for Oracle Database 11g? (Choose all that apply.) Which two statements are true regarding the functionality of the remap command in ASMCMD? (Choose two.) The Database Resource Manager is automatically enabled in the maintenance window that runs the Automated Maintenance Task. What is the reason for this? Which two statements are true regarding Health Monitor checks in Oracle Database 11g? (Choose two.) Evaluate the following command: SQL>ALTER SYSTEM SET db_securefile = 'IGNORE'; What is the impact of this setting on the usage of SecureFiles? Which tasks are run automatically as part of the Automated Maintenance Task by default? (Choose all that apply.) In which two aspects does hot patching differ from conventional patching? (Choose two.) In your database, you want to ensure that idle sessions that are blocking active are automatically terminated after a specified period of time. How would you accomplish this? You have applications that have frequently executed queries, and produce small and static result sets. You configure the sqlnet.ora file in the client machine to set a nonzero value for the OCI_RESULT_CACHE_MAX_SIZE parameter. What is the purpose of this configuration? Which two statements about the SQL Management Base (SMB) are true? (Choose two.) You plan to migrate your database from a File system to Automata Storage Management (ASM) on same platform. Which two methods or commands would you use to accomplish this task? The Oracle Database 11g database is running in the ARCHIVELOG mode. The archived redo log files are stored on three locations. The Flash Recovery Area is one of the locations. The details are given below: LOG_ARCHIVE_DEST_1 = 'LOCATION = /disk1/archive' LOG_ARCHIVE_DEST_2 = 'SERVICE = stdb1' DB_RECOVERY_FILE_DEST = '/u01/oradata' Examine the following RMAN command issued to set the deletion policy for archived log files: RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO sbt; Which statement is true regarding what this command accomplishes? You have a range-partitioned table in your database. Each partition in the table contains the sales data for a quarter. The partition related to the current quarter is modified frequently and other partitions undergo fewer data manipulations. The preferences for the table are set to their default values. You collect statistics for the table using the following command in regular intervals: SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS('SH','SALES',GRANULARITY=>'GLOBAL'); You need statistics to be collected more quickly. What can you do to achieve this? Your system has been upgraded from Oracle Database 10g to Oracle Database 11g. You imported SQL Tuning Sets (STS) from the previous version. After changing the OPTIMIZER_FEATURE_ENABLE parameter to and running the SQL Performance Analyzer, you observed performance regression for a few SQL statements. What would you do with these SQL statements? Which statement is true regarding virtual private catalogs? In your database, the LDAP_DIRECTORY_SYSAUTH initialization parameter has been set to YES and the users who need to access the database as DBAs have been granted SYSDBA enterprise role in Oracle Internet Directory (OID). SSL and the password file have been configured. A user SCOTT with the SYSDBA privilege tries to connect to the database instance from a remote machine using the command: $ SQLPLUS scott/[email protected] AS SYSDBA where DB01 is the net service name. Which authentication method would be used first?

Study Guides