oracle11G-OCP-052答案解析.docx

Oracle OCP 11G 052答案解析1. View the Exhibit and examine the attributes of an undo tablespace.In an OLTP system, the user SCOTT has started a query on a large table in the peak transactional hourthat performs bulk inserts. The query runs for more than 15 minutes and then SCOTT receives thefollowing error:ORA-01555: snapshot too oldWhat could be the reason for this error?A.The query is unable to get a read-consistent image.B.There is not enough space in Flash Recovery Area.C.There is not enough free space in the flashback archive.D.The query is unable to place data blocks in undo tablespaceAnswer: A答案解析： “快照太旧”错误是因为没有足够的还原数据来实现读取一致性。所以选A。ORA-01555: snapshot too old: rollback segment number string with name string too smallCause: rollback records needed bya reader for consistentread are overwritten by other writersAction: If in Automatic Undo Management mode, increase undo_retention setting. Otherwise, use larger rollback segments2. Which two statements are true about the Automatic Workload Repository (AWR)? (Choose two.)A.All AWR tables belong to the SYSTEM schema.B.The AWR contains systemwide tracing and logging information.C.The snapshots collected by the AWR are accessible through data dictionary views.D.The snapshots collected by the AWR are used by self-tuning components in the database.Answer: CD答案解析：AWR 基础结构由两个主要部分组成： 一个内存中统计信息收集工具，Oracle Database 11g组件使用它来收集统计信息。出于性能方面的考虑，这些统计信息存储在内存中。可以通过动态性能(V$) 视图访问存储在内存中的统计信息。 代表该工具持久部分的AWR 快照。AWR 快照可以通过数据字典视图和EnterpriseManager Database Control 来访问。它为内部Oracle 服务器组件提供服务来收集、处理、维护和使用性能统计信息，以便进行问题检测和自优化。AWR 中包含数百个表，所有这些表均属于SYSMAN方案且存储在SYSAUX表空间中。3. While observing the index statistics, you find that an index is highly fragmented, thereby resulting inpoor database performance. Which option would you use to reduce fragmentation without affecting theusers who are currently using the index?A.Validate the index structure using the ANALYZE.INDEX command.B.Rebuild the index using the ALTER INDEX.REBUILD ONLINE command.C.Change the block space utilization parameters using the ALTER INDEX command.D.Deallocate the unused space in the index using the ALTER INDEX.DEALLOCATE UNUSED command.Answer: B答案解析：通过重建不可用索引来重算指针，可使不可用索引变为有效。重建不可用索引会在新位置重新创建索引，然后会删除不可用索引。如使用一下语句来重建索引ALTER INDEX HR.emp_empid_pkREBUILD ONLINE;使用REBUILD ONLINE子句，用户可以在重建时继续更新索引表。4. Your test database is running in NOARCHIVELOG mode. Which statement is true regarding the type ofbackups that can be performed on the database?A.You can perform online whole database backups.B.You can performofflinewhole database backups.C.You cannot perform schema-level export/import operations.D.You can perform the backup of only the SYSTEM tablespace when the database is open.Answer: B答案解析：在非归档模式下，只能在数据库未打开的情况下做一致性的冷备份，即脱机备份。所以选B.是因为进行备份时，数据文件头中的系统更改号(SCN) 与控制文件中的SCN 相匹配。5. The database instance is currently using SPFILE. View the Exhibit and examine the error that youreceived while running the DB Structure Integrity check.Given below are the steps to recover from the error in random order:1: Shut down the instance, if not already done.2: Copy one of the remaining control files to a new location.3: Change the value of the CONTROL_FILES initialization parameter to correspond to the new location ofthe control files.4: Start up the database instance to the NOMOUNT stage.5: Recover the database to the point of failure of the control file.6: Open the database.Identify the correct sequence of steps?A.1, 2, 4, 3, 5, 6B.2, 4, 3, 5, 6; 1 not requiredC.4, 5, 6, 2, 3; 1 not requiredD.5, 2, 3, 4; 1 and 6 not requiredAnswer: A答案解析：6. Note the following points describing various utilities in Oracle Database 11g:1: It enables the high-speed transfer of data from one database to another.2: It provides a complete solution for the backup, restoration, and recovery needs of the entire database.3: It enables the loading of data from an external file into an Oracle database.4: It provides a tape backup management for the Oracle ecosystem.Which point describes Oracle Secure Backup?A.1B.2C.3D.4E.1, 2, and 4F.1, 2, 3, and 4Answer: D答案解析：Oracle Secure Backup 增加了备份到磁带功能和操作系统文件备份。 Oracle Secure Backup 和RMAN 一起为Oracle 环境提供端到端备份解决方案： 对文件系统数据和Oracle DB 使用集中式磁带备份管理 对RMAN 备份提供最佳集成介质管理层 可备份网络上任意位置的任意数据 由一个技术支持资源来支持整个备份解决方案，可加快解决问题的速度。 确保以较低的成本和复杂性提供可靠的数据保护。Oracle Secure Backup 通过以下方式对现有功能提供补充： 完整的备份解决方案：Oracle Secure Backup 为数据库和非数据库的数据提供数据保护，可保护整个Oracle 环境。 介质管理：Oracle Secure Backup 为RMAN 数据库磁带备份提供了介质管理层。在Oracle Secure Backup 推出以前，客户需要购买价格昂贵、集成有RMAN 磁带备份功能的第三方介质管理产品。 备份网络上任何位置的数据：Oracle Secure Backup 可备份从多台连接到网络的计算机系统至网络上的三级存储资源中的所有数据。Oracle Secure Backup 支持对服务器、客户机、网络连接存储(NAS) 服务器和三级存储设备的各种配置，并保护网络存储环境。RMAN 和Oracle Secure Backup 的组合提供了一个纯粹以Oracle 产品堆栈实现的端到端备份解决方案。因为由Oracle Corporation 负责整个备份解决方案，因此该解决方案可提供更好的客户支持服务。7. You have issued a SHUTDOWN ABORT command to bring down your database instance. Considerthe steps that will be performed later when you open the database:1: SGA is allocated.2: Control file is read.3: Redo log files are read.4: Instance recovery is started.5: Background processes are started.6: Data files are checked for consistency.7: Server parameter file or the initialization parameter file is read.Which option has the correct order in which these steps occur?A.7, 1, 5, 2, 3, 6, 4B.1, 2, 3,7, 5, 6, 4C.7, 1, 4, 5, 2, 3, 6D.1, 7, 5, 4, 2, 3, 6Answer: A答案解析：shutdown abort后，再startup，需要用到smon进行实例恢复。在nomount阶段，读取初始化参数，分配SGA，后台进程启动在mount阶段，读取控制文件和联机日志文件，查看需要打开哪些数据文件在open阶段，根据控制文件内容找到数据文件，并打开，因为是abort关闭的数据库，则在open时需要检查数据库，数据库文件以及数据文件头部的scn号是否一致，如果不一致，SMON进程进行实例恢复。8. Observe the information in the columns:1: The SGA a. Text and parsed forms of all SQL statements2: The cursor state b. Run-time memory values for the SQL statement, such as rows retrieved3: User-session data c. Security and resource usage information4: The stack space d. Local variables for the processWhich option has the correct match between the memory areas and their contents?A.1-c, 2-b, 3-d, 4-aB.1-c, 2-b, 3-d, 4-aC.1-a, 2-b, 3-c, 4-dD.1-a, 2-b, 3-d, 4-cAnswer: C答案解析：这道题，概念性问题，理解了就好，姑且就记答案ABCD吧。9. View the Exhibit and examine the privileges granted to the SL_REP user.The EMP table is owned by the SCOTT user. The SL_REP user executes the following command:SQL GRANT SELECT ON scott.emp TO hr;Which statement describes the outcome of the command?A.The command executes successfully.B.The command produces an error because the EMP table is owned by SCOTT.C.The command produces an error because SL_REP has the GRANT ANY OBJECT PRIVILEGE withoutADMIN_OPTION.D.The command produces an error because SL_REP does not have the SELECT privilege withGRANT_OPTION on the EMP table.Answer: A答案解析：环境测试一下：1、创建一个用户，并解锁sysTEST0924 create user SL_REP identified by 123 account unlock;User created.2、将connect、resource角色赋予sysTEST0924 grant connect,resource to SL_REP;Grant succeeded.3、将grant any object privilege赋予sysTEST0924 grant grant any object privilege to SL_REP;Grant succeeded.4、SL_REP连接数据库，开始授予hr访问scott的emp表，赋予成功。所以选择A。sysTEST0924 conn SL_REP/123Connected.sl_repTEST0924 grant select on scott.emp to hr;Grant succeeded.10. Examine the following statement that is used to modify the constraint on the SALES table:SQL ALTER TABLE SALES MODIFY CONSTRAINT pk DISABLE VALIDATE;Which three statements are true regarding the above command? (Choose three.)A.The constraint remains valid.B.The index on the constraint is dropped.C.It allows the loading of data into the table using SQL *Loader.D.New data conforms to the constraint, but existing data is not checkedE.It allows the data manipulation on the table using INSERT/UPDATE/DELETE SQL statements.Answer: ABC答案解析：这道题，可用排除法来做，D:属于ENABLENOVALIDATE，E，不能对已存在的数据做update所以，只能选择ABC简单来讲，DISABLE 和ENABLE 是针对新增的数据是否符合约束，NOVALIDATE 和VALIDATE是针对已经存在的数据是否符合约束。题中是DISABLE VALIDATE即是针对新增的数据可以不符合约束，但是已经存在的数据要满足约束。可以使用排除法，D是属于.ENABLENOVALIDATEE,不能修改已经存在数据，错误。C正确，是因为对新增的数据不限制，没有约束。A正确，是因为DISABLE VALIDATE对现有的数据还有约束。B呢？约束条件状态为了更好地处理数据可能暂时违反约束条件的情况，可将约束条件指定为不同的状态。可以启用(ENABLE) 或禁用(DISABLE) 完整性约束条件。如果启用约束条件，在数据库中输入或更新数据时就会检查数据。此时，禁止输入不符合约束条件规则的数据。如果禁用约束条件，则可以在数据库中输入不符合规则的数据。完整性约束条件可处于以下一种状态： DISABLE NOVALIDATE DISABLE VALIDATE ENABLE NOVALIDATE ENABLE VALIDATEDISABLE NOVALIDATE：不检查新数据和现有数据，因此这些数据可能不符合约束条件。当数据来自验证过的源，而且表是只读表时，通常会使用此状态。因此，不会将新数据输入表中。在已清理了数据的数据仓库环境中使用NOVALIDATE。此时不需要进行验证，因而可以节省很多时间。DISABLE VALIDATE：如果约束条件处于此状态，则不允许对有约束条件的列进行任何修改。因为如果在验证现有数据后，又允许将未经检查的数据输入表中，就会出现不一致的情况。通常，在必须验证现有数据但不进行修改时，以及不需要索引来提高性能时，可使用此状态。ENABLE NOVALIDATE：新数据符合约束条件，但现有数据处于未知状态。通常在确信表中只存在纯净数据和符合规则的数据的情况下使用该状态，此时不需要进行验证。但是，不允许将违反规则的新数据输入到系统中。ENABLE VALIDATE：新数据与现有数据均符合约束条件。这是约束条件的典型状态和默认状态。You can specify that a constraint is enabled (ENABLE) or disabled (DISABLE). If a constraint is enabled, data is checked as it is entered or updated in the database, and data that does not conform to the constraint is prevented from being entered. If a constraint is disabled, then data that does not conform can be allowed to enter the database.Additionally, you can specify that existing data in the table must conform to the constraint (VALIDATE). Conversely, if you specifyNOVALIDATE, you are not ensured that existing data conforms.An integrity constraint defined on a table can be in one of the following states: ENABLE,VALIDATE ENABLE,NOVALIDATE DISABLE,VALIDATE DISABLE,NOVALIDATE11. Identify the memory component from which memory may be allocated for:1: Session memory for the shared server2: Buffers for I/O slaves3: Oracle Database Recovery Manager (RMAN) backup and restore operationsA.Large PoolB.Redo Log BufferC.Database Buffer CacheD.Program Global Area (PGA)Answer: A答案解析：大型池数据库管理员可以配置称为“大型池”的可选内存区，以便为以下对象提供大型内存分配：共享服务器的会话内存和Oracle XA 接口（在事务处理与多个数据库交互时使用）I/O 服务器进程Oracle DB 备份和还原操作通过从大型池中为共享服务器、Oracle XA 或并行查询缓冲区分配会话内存，Oracle DB 可以主要使用共享池来缓存共享SQL，并避免由于收缩共享SQL 高速缓存而导致的性能开销。此外，用于Oracle DB 备份和还原操作、I/O 服务器进程以及并行缓冲区的内存以数百KB的缓冲区进行分配。与共享池相比，大型池可以更好地满足此类大型内存请求。大型池没有LRU 列表。它与共享池中的保留空间不同，后者与从共享池中分配的其它内存使用相同的LRU 列表。12. Which is the correct description of a pinned buffer in the database buffer cache?A.The buffer is currently being accessed.B.The buffer is empty and has not been used.C.The contents of the buffer have changed and must be flushed to the disk by the DBWn process.D.The buffer is a candidate for immediate aging out and its contents are synchronized with the blockcontents on the disk.Answer: A答案解析：Every buffer has an access mode:pinnedorfree(unpinned). A buffer is pinned in the cache so that it does not age out of memory while a user session accesses it. Multiple sessions cannot modify a pinned buffer at the same time.13. You have executed this command to change the size of the database buffer cache:SQL ALTER SYSTEM SET DB_CACHE_SIZE=2516582;System altered.To verify the change in size, you executed this command:SQL SHOW PARAMETER DB_CACHE_SIZENAME TYPE VALUE- - -db_cache_size big integer 41943044MWhy is the value set to 4194304 and not to 2516582?A.because 4194304 is the granule sizeB.because 4194304 is the standard block sizeC.because 4194304 is the largest nonstandard block size defined in the databaseD.because 4194304 is the total size of data already available in the database buffer cacheAnswer: A答案解析：sysTEST0924 select 2516582/1024/1024 from dual;2516582/1024/1024-2.39999962sysTEST0924 select 4194304/1024/1024 from dual;4194304/1024/1024-4All SGA components allocate and deallocate space in units ofgranules. Oracle Database tracks SGA memory use in internal numbers of granules for each SGA component.The memory for dynamic components in the SGA is allocated in the unit of granules. The granule size is determined by the amount of SGA memory requested when the instance starts. Specifically, the granule size is based on the value of theSGA_MAX_SIZEinitialization parameter. Table 6-1shows the granule size for different amounts of SGA memory.Table 6-1 Granule SizeSGA Memory AmountGranule SizeLess than or equal to 1 GB4 MBGreater than 1 GB and less than or equal to 8 GB16 MBGreater than 8 GB and less than or equal to 16 GB32 MBGreater than 16 GB and less than or equal to 32 GB64 MBGreater than 32 GB and less than or equal to 64 GB128 MBGreater than 64 GB and less than or equal to 128 GB256 MBGreater than 128 GB512 MBYou can query theV$SGAINFOview to see the granule size that is being used by an instance. The same granule size is used for all components in the SGA.If you specify a size for a component that is not a multiple of granule size, Oracle Database rounds the specified size up to the nearest multiple. For example, if the granule size is 4 MB and you specifyDB_CACHE_SIZEas 10 MB, the database actually allocates 12 MB.如果指定的size不是granule size的整数倍，则rounds到整数倍，例如，如果granule size为4M，指定DB_CACHE_SIZEas 10 MB，不是4的整数倍，则实际上DB_CACHE_SIZEas 为12M。一下是我的环境，因为我的sga大于1G，故granule size为16M。sysTEST0924 desc V$SGAINFOName Null? Type- - -NAME VARCHAR2(32)BYTES NUMBERRESIZEABLE VARCHAR2(3)sysTEST0924 select * from V$SGAINFO ;NAME BYTES RES- - -Fixed SGA Size 2230952 NoRedo Buffers 20078592 NoBuffer Cache Size 1862270976 YesShared Pool Size 570425344 YesLarge Pool Size 16777216 YesJava Pool Size 16777216 YesStreams Pool Size 16777216 YesShared IO Pool Size 0 YesGranule Size 16777216 NoMaximum SGA Size 2505338880 NoStartup overhead in Shared Pool 117947968 NoFree SGA Memory Available 012 rows selected.14. These are points that describe the contents of different memory components:1: Descriptive information or metadata about schema objects that are queried by using SQL statements2: The run-time area for data manipulation language (DML) or data definition language (DDL) statements3: Results of SQL queries and PL/SQL functions4: Executable forms of SQL cursors, PL/SQL programs, and Java classes5: The information necessary to reconstruct changes made to the database by a transactionWhich of these will be stored in theShared Poolif the necessary configurations are done?A.1 and 2B.2 and 5C.1, 3, and 4D.3, 4, and 5E.1, 2, 3, and 4Answer: C答案解析：Shared PoolTheshared poolcaches various types of program data. For example, the shared pool stores parsed SQL, PL/SQL code, system parameters, anddata dictionaryinformation. The shared pool is involved in almost every operation that occurs in the database. For example, if a user executes a SQL statement, then Oracle Database accesses the shared pool.从下图可知：Thelibrary cacheis a shared pool memory structure that stores executable SQL and PL/SQL code.The data dictionary is accessed so often by Oracle Database that the following special memory locations are designated to hold dictionary data: Data dictionary cacheThis cache holds information about database objects. The cache is also known as therow cachebecause it holds data as rows instead of buffers. Library cache All server processes share these caches for access to data dictionary information.he server result cache contains theSQL query result cacheandPL/SQL function result cache, which share the same infrastructure.15. Which two statements are true about Shared SQL Area and Private SQL Area? (Choose two.)A.Shared SQL Area will be allocated in the shared pool.B.Shared SQL Area will be allocated when a session starts.C.Shared SQL Area will be allocated in the large pool always.D.Private SQL Area will be allocated in the Program Global Area (PGA) always.E.Shared SQL Area and Private SQL Area will be allocated in the PGA or large pool.F.The number of Private SQL Area allocations is dependent on the OPEN_CURSORS parameter.Answer: AF答案解析：shared SQL area:An area in the shared poolthat contains the parse tree and execution planfor a SQL statement. Only one shared SQL area exists for a unique statement.Acursoris a name orhandle to a specific private SQL area.cursors are closely associated with private SQL areas。private SQL area：An area in memory that holds a parsed statement and other information for processing. The private SQL area contains data such as bind variablevalues,queryexecution state information, and query execution work areas.The client process is responsible for managingprivate SQL areas. The allocation and deallocation of private SQL areas depends largely on the application, although the number of private SQL areas that a client process can allocate is limited by the initialization parameterOPEN_CURSORS.16. Note the following functionalities of various background processes:1: Record the checkpoint information in data file headers. CKPT2: Perform recovery at instance startup. SMON3: Cleanup unused temporary segments. SMON4: Free the resources used by a user process when it fails.5: Dynamically register database services with listeners.6: Monitor sessions for idle session timeout.Which option has the correct functionalities listed for a background process?A.Archiver Process (ARCn): 1, 2, 5B.System Monitor Process (SMON): 1, 4, 5C.Process Monitor Process (PMON): 4, 5, 6D.Database Writer Process (DBWn): 1, 3, 4Answer: C答案解析：进程监视器进程(PMON) 在用户进程失败时执行进程恢复。PMON 负责清除数据库缓冲区高速缓存以及释放该用户进程使用的资源。例如，它重置活动事务处理表的状态，释放锁，并且从活动进程列表中删除该进程ID。PMON 定期检查分派程序和服务器进程的状态，并重新启动任何已停止运行（但并非Oracle DB