oracle11g OCP 认证 1Z0-052考试笔记2
发布时间：2020-12-12 15:43:38 所属栏目：百科 来源：网络整理
导读：Q1 View the Exhibit and identify the component marked with a question mark. A.Checkpoint (CKPT) Q2. Sunday An incremental level 0 backup backs up all blocks that have ever been in use in this database. 0级增量备份会备份所有曾经在此数据库中
|
Q1 View the Exhibit and identify the component marked with a question mark. A.Checkpoint (CKPT) Q2. Sunday An incremental level 0 backup backs up all blocks that have ever been in use in this database. 0级增量备份会备份所有曾经在此数据库中使用的块 Monday - Saturday A cumulative incremental level 1 backup copies all blocks changed since the most recent level 0 backup. Because the most recent level 0 backup was created on Sunday,the level 1 backup on each day Monday through Saturday backs up all blocks changed since the Sunday backup（level 0 backup）. 增量累积1级备份，备份自最近0级备份所有变化的块。 因为最近0级备份在周日， 从星期一到星期六的1级备份，是备份自周日即0级备份所有变化的块。 You are managing an Oracle Database 11g database with the following backup strategy: 1) On Sunday,an incremental level 0 tape backup is performed. 2) Monday through Saturday,a cumulative incremental level 1 tape backup is performed. Which two statements are true regarding the backups performed? (Choose two.) A.The backup performed in step 1 backs up all blocks that have ever been in use in the database. B.The backup performed in step 2 copies all the blocks changed since the most recent level 0 Q3. 5. The DBA revokes the CREATE TABLE system privilege from SKD. Which statement is true after step 5 is performed? C.The table created by HR remains and HR still has the CREATE TABLE system privilege. Q4. 断电 事务未提交，下次打开数据库回滚。 C.The uncommitted transaction is rolled back automatically at the next opening of the database. Q5. 5: Identify the memory component from which memory may be allocated for: 1. Session memory for the shared server 2. Buffers for I/O slaves 3. Oracle Database Recovery Manager (RMAN) backup and restore operations A.Large Pool 大池用来做备份 Q6. SQL> AUDIT DROPANY TABLE BY scott BY SESSIONWHENEVER SUCCESSFUL; What is the effect of this command? C.One audit record is created for the whole session if user SCOTT successfully drops one or more tables in his session. Q7. 使用外部表存储flat file 7: You have a large amount of historical data in a flat file. Some analysts in your organization need to query this data in the file. The file is too large to load the data in it into your current database. Which is the most effective method to access this data in your database? D.Create an external table and leave the data in the flat file. Q8. 8: You want to check the details of few errors that users have reported. You search for the alert log file and execute few commands to find the location of the alert log file. View the Exhibit and check the commands executed. What is the location of the alert_orcl.log file? 报警日志文件的位置 F.ORACLE_BASE/diag/rdbms/orcl/orcl/trace $ORACLE_BASE/diag/rdbms/<db_name>/<SID>/alert 目录中有一个XML 版本的预警日志。 要通过SQL*Plus 确定预警日志的位置，请执行以下操作： ? 使用SQL*Plus（或其它查询工具，如 SQL Developer）连接到数据库。 ? 查询V$DIAG_INFO 视图。 要查看不带XML 标记的纯文本预警日志，请执行以下操作： ? 在V$DIAG_INFO 查询结果中，记下对应于Diag Trace 条目的路径。将目录更改至该路径。 ? 使用文本编辑器打开alert_SID.log 文件。 要查看XML 格式的预警日志，请执行以下操作： ? 在V$DIAG_INFO 查询结果中，记下对应于Diag Alert 条目的路径。将目录更改至该路径。 ? 使用文本编辑器打开log.xml文件。 sys@TEST0924> select * from v$diag_info; INST_ID NAME VALUE 1 Diag Trace /u01/app/oracle/diag/rdbms/test0924/test0924/trace Q9. 9: Which statements are true regarding the creation of an incident package file （事件包文件）by using the EM Workbench Support?(EM 工作台支持) (Choose all that apply.) 选项 A.You can add SQL test cases to the incident package. B.You can add or remove the trace files to the package. C.You cannot create an incremental incident package when the physical files are purged from the ADR. D.You can create the incremental incident package ZIP file for new or modified diagnostic information for the incident package already created. Correct Answers: A B D （you can ） Q10. 10: You are managing an Oracle Database 11g database. You want to ensure the recovery of the database to the point of failure.Which configuration will you do to accomplish the task? 保证恢复数据库到失败的点 C.Configure the database instance for ARCHIVELOG mode. Q11: 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 AutomaticWorkload Repository (AWR). ADDM 在记录每个AWR 快照之后会自动运行(B)。每次记录快照后，ADDM 会分析与最后两个快照对应的时段。ADDM 会预先监视实例，以便在大多数瓶颈问题成为严重问题之前检测到这些问题。多数情况下，ADDM 会为检测到的问题提供建议解决方案，甚至可以量化这些建议的优势。 每次ADDM 分析的结果都存储在AWR (C)中，并可通过Enterprise Manager 进行访问 Q12. Examine the values for the following initialization parameters: FAST_START_MTTR_TARGET = 0 LOG_CHECKPOINT_INTERVAL = 0 B.The MTTR advisor will be disabled. C.Automatic checkpoint tuning will be disabled. Q13. 13: View the Exhibit and examine the user information. The user has been granted CONNECT and RESOURCE roles and no individual system privileges. The SL_REP user executes this command to create a table: SQL> CREATE TABLE orders(oid number(6),odate date,ccode number(4),oamt number(10,2)) TABLESPACE purchase_space; B.The command executes successfully and creates the table in the PURCHASE_SPACE tablespace. 参考：http://blog.csdn.net/rlhua/article/details/12276409 因为RESOURCE角色有CREATE TABLE的权限，故此命令可以创建成功。 Q14. 14: Note the following functionalities of various background processes: 4. 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? C.Process Monitor Process (PMON): 4,5,6 Q15. SQL> CREATE TABLESPACE sales_data DATAFILE SIZE 100M SEGMENT SPACE MANAGEMENTAUTO; Which two statements are true about the SALES_DATA tablespace? (Choose two.) A.The database automatically determines the extent-sizing policy for the tablespace. 数据库自动确定区大小策略 D.The space utilization（空间利用） description of the data blocks in segments is recorded in bitmap blocks. 在位图中记录数据库的空间利用描述 段空间管理: 本地管理的表空间中的段空间管理方式可指定为: 自动:Oracle DB 使用位图管理段中的空闲空间.位图描述了段中每个数据块的状态,该状态与可插入行的块中的空间量有关.当数据块中可用空间增多或减少时,位图中会反映数据块的新状态.通过使用位图,Oracle DB 可以提高管理空闲空间的自动化程度.因此,这种空间管理方式称为"自动段空间管理(ASSM)". 手动:此方法指定要使用空闲列表来管理段中的空闲空间.空闲列表是由一些数据块组成的列表,这些数据块中有可插入行的空间.由于这种管理段空间的方式需要为在表空间中创建的方案对象指定并优化PCTUSED、FREELISTS和FREELIST GROUPS存储参数,因此这种方式称为"手动段空间管理".支持使用此方法是为了向后兼容,建议使用ASSM. 可通过dba_tablespaces查看分区和段空间管理方式: SYS@ENMOEDU> select tablespace_name,extent_management,segment_space_management from dba_tablespaces; Q16. 16: View the Exhibit to examine the output for the CROSSCHECK BACKUP command. Which statement is true about the output of the command? B.The backup piece is expired because the operating-system file was not found. Q17. A constraint in a table is defined with the INITIALLY IMMEDIATE clause. You executed the ALTER TABLE command with the ENABLE VALIDATE option to enable the constraint that was disabled. What are the two effects of this command? (Choose two.) 表上的约束设置初始化立即执行， 更改为enable validate-要求新旧数据必须同时满足约束规则 A) It fails if any existing row violates（违反） the constraint. 若已经存在的行违反约束会失败 D) It prevents insert,update,and delete operations on the table while the constraint is in the process of being enabled. 约束进行过程中，阻止DML操作 Answer: A,D 约束状态--约束一共有四种状态 a、enable validate-要求新旧数据必须同时满足约束规则-在规则正在进行中时是不容许在表上进行任何 DML 操作的 b、enable novalidate-已存在数据可以不满足，但是新数据必须满足 c、disable validate-不容许在表上进行任何 DML 操作，对主键和唯一约束来说，会删除相应的唯一索引，但 约束规则仍然有效 d、disable novalidate-数据不满足约束规则，对主键和唯一约束来说，会删除相应的唯一索引 INITIALLY IMMEDIATE 初始化立即执行--在每条语句执行结束时检验约束 INITALLY DEFERRED 初始化延迟执行,一直等到事务完成后（或者调用 set constraint immediate 语句时）才检验约束 SQL> create table t( x int constraint check_x check ( x > 0 ) deferrable initially immediate,y int constraint check_y check ( y > 0 ) deferrable initially deferred ) SQL> insert into t values ( -1,1); insert into t values ( -1,1);0ERROR at line 1: ORA-02290: checkconstraint (OPS$TKYTE.CHECK_X) violated 由于 CHECK_X 是可延迟但初始化为立即执行的约束，所以这一行立刻被拒绝了。而 CHECK_Y 则不同，它不仅是可延迟的，而且初 始化为延迟执行，这就意味着直到我用 COMMIT 命令提交事务或将约束状态设置为立即执行时才检验约束。 SQL> insert into t values ( 1,-1); 现在它是成功的（总之到目前为止是成功的）。我将约束检验延迟到了执行 COMMIT 的时候： SQL> commit; 0ERROR at line 1: ORA-02091: transaction rolled back ORA-02290: check constraint (OPS$TKYTE.CHECK_Y) violated 此时数据库将事务回滚，因为违反约束导致了COMMIT语句的失败。 这些语句说明了初始化立即执行与初始化延迟执行约束之间的区别。 initially（初始化）部分指定Oracle什么时候会进行默认的约束检验--是在语句结束时［immediate（立即执行）］， 还是在事务结束时［deferred（延迟执行）］。 还要说明deferred（可延迟）子句有什么用。 我可以发出命令，让所有可延迟的约束变为延迟执行的。 注意，你也可以对一个约束使用该命令,你不必让所有可延迟的约束都变为延迟执行的： SQL>set constraints all deferred; 或者 SQL> set constraints allimmediate; 延迟约束有哪些实际用处呢？ 有很多。 它主要用于物化视图（快照）。这些视图会使用延迟约束来进行视图刷新。 在刷新物化视图的过程中，可能会破坏完整性，而且将不能逐句检验约束。 但到执行 COMMIT 时，数据完整性就没问题了，而且能满足约束。 没有延迟约束，物化视图的约束可能会使刷新过程不能成功进行。 使用延迟约束的另一个普遍原因是，当预测是否需要更新父/子关系中的主键时，它有助于级联更新。 如果你将外键设为可延迟、但初始化为立即执行，那么你就可以将所有约束设置为可延迟。 将父键更新为一个新值--至此子关系的完整性约束不会被验证。将子外键更新为这个新值。 COMMIT--只要所有受更新影响的子记录都指向现有的父记录，这条命令就能成功执行。 Q18. 18:Which statements listed below describe the data dictionary views? 1) These are stored in the SYSTEM tablespace. 2) These are the based on the virtual tables. 3) These are owned by the SYS user. 4) These can be queried by a normal user only if O7_DICTIONARY_ACCESSIBILTY parameter is set to TRUE. 5) The V$FIXED_TABLE view can be queried to list the names of these views. A.1 and 3 Q19. Check complete. The overall result of this check is: Failed <<<< Problem: The system does not have the required swap space. What happens to the installation in this situation? A.It can be continued. Q20. 20: Which two statements correctly describe the relation between a data file and the logical database structures? (Choose two.) A.An extent cannot spread across data files. 区不能跨越数据文件 C.A data file can belong to only one tablespace. 数据文件只能属于一个表空间 Q21. 21: Your database is configured in shared server mode(共享服务模式). However,your senior （高级）DBA asks you to modify the value of the PRIVATE_SGA limit in the profile of the users. What could be the reason for this? A.to limit the User Global Area (UGA) memory allocated to a session from the SGA Limiting Memory Use for Each User Session by Setting PRIVATE_SGA You can set the PRIVATE_SGA resource limit to restrict the memory used by each client session from the SGA. PRIVATE_SGA defines the number of bytes of memory used from the SGA by a session. However,this parameter is used rarely,because most DBAs do not limit SGA consumption on a user-by-user basis. The UGA must be available to a database session for the life of the session. For this reason,the UGA cannot be stored in the PGA when using a shared server connection because the PGA is specific to a single process. Therefore,the UGA is stored in the SGA when using shared server connections,enabling any shared server process access to it. When using a dedicated server connection,the UGA is stored in the PGA. PRIVATE_SGA Use this clause to apply a weight to the PRIVATE_SGA resource. This limit applies only if you are using shared server architecture and allocating private space in the SGA for your session. Q22. 22:Which two operations can be performed on an external table? (Choose two.) A.Create a view on the table. C.Create a synonym on the table. Q23. 23: You want to import schema objects of the HR user using Oracle Data Pump from the development database,DEVDB,to the production database,PRODDB. A database link devdb.us.oracle.com is created between PRODDB and DEVDB. 导入用户HR模式对象，通过dblink 导入 ORA-31640: unable to open dump file "/home/oracle/schema/schemas.dat" for read ORA-27037: unable to obtain file status What would you do to overcome the error? E.Replace the dumpfile option with the network_link = devdb.us.oracle.com Correct Answers: E Q24. 24: You execute this command to drop the ITEM table,which has the primary key referred in the ORDERS table: SQL> DROP TABLE scott.item CASCADE CONSTRAINTS PURGE; Which two statements are true about the effect of the command? (Choose two.) A.No flashback is possible to bring back the ITEM table. 不可闪回 D.The dependent依赖的 referential integrity constraints引用完整性约束 in the ORDERS table are removed. 移除 Q25. You executed this command to create a temporary table: SQL> CREATE GLOBAL TEMPORARY TABLE report_work_area (startdate DATE,enddate DATE,class CHAR(20)) ON COMMIT PRESERVE ROWS; Which statement is true about the rows inserted into the REPORT_WORK_AREA table during a transaction? A.The rows stay in the table only until session termination. 行保存到会话终止 B.The rows stay in the table only until the next transaction starts on the table. C.The rows are visible to all current sessions after the transaction is committed. D.The rows stay available for subsequent sessions after the transaction is committed. Answer: A 答案解析： 参考：http://blog.csdn.net/rlhua/article/details/13015519 下列子句控制着行的生存期： ? ON COMMIT DELETE ROWS ：指定插入行的生存期仅为事务处理的持续时间 ? ON COMMIT PRESERVE(维持，持续) ROWS ：指定插入行的生存期为会话的持续时间 Q26. 26: Which naming method uses the tnsnames.ora file to store the connect descriptor used by the client while connecting to the database instance from a remote machine? B.local naming method 使用本地命名，用户需要为Oracle Net service提供一个别名，相比简单连接只需记住一个简短的别名，Oracle Net将别名与已知服务名的本地列表进行对比，服务名的本地列表存储在客户端的文本文件/network/admin/tnsnames.ora中，可以使用TNS_ADMIN环境变量修改这个默认位置，经常用于应用程序与数据库的连接。 本地命名 要求客户端名称解析文件tnsnames.ora 支持所有Oracle Net协议 Q27. 27:Which two statements are true about Shared SQLArea and Private SQLArea? (Choose two.) A.Shared SQLArea will be allocated in the shared pool. F.The number of Private SQLArea allocations is dependent on 取决于 the OPEN_CURSORS parameter. Shared SQL Area存储着SQL的执行计划等信息，可以被多个session共享，该component存储在SGA中 Private SQL Area 包括绑定变量等信息，每个session的这类信息都不一样，为session所独有（PGA）。且在Private SQL Area 存储一个指向shared SQL area ( SGA )中共享SQL的指针,与OPEN_CURSORS参数相关 shared SQL area:An area in the shared pool that contains the parse tree and execution plan for a SQL statement. Only one shared SQL area exists for a unique statement. A cursor is a name or handle 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 variable values,query execution state information,and query execution work areas. The client process is responsible for managing private 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 parameter OPEN_CURSORS. Q28. 28: You configured the Flash Recovery Area for your database. The database instance has been started in ARCHIVELOG mode 归档模式 and the LOG_ARCHIVE_DEST_1 归档位置未设置parameter is not set. What will be the implications on the archiving and the location of archive redo log files? C.Archiving will be enabled and the destination for the archived redo log file will be set to the Flash Recovery Area implicitly. 归档正常运行备份到FRA Q29. 29:Which three operations require undo data? (Choose three.) B.flashing back a transaction 闪回一个事务 C.recovering a failed transaction 恢复一个失败的事务 D.running a read-consistent query 读一致性查询 Q30. 30:Which two statements are true about alerts? (Choose two.) A.Clearing an alert sends the alert to the alert history. D.Server-generated alerts notify the problems that cannot be resolved automatically and require administrators to be notified. Q31. Which two activities are NOT supported by the Data Recovery Advisor (DRA)? (Choose two.) A.Recover from failures in the RAC environment. 不支持rac D.Diagnose and repair failures on a standby database. 分析和修复备库 Data Recovery Advisor 支持的数据库配置： – 单实例 – 非RAC – 支持故障转移到备用数据库，但不支持分析和修复备用数据库 Q32. RMAN>REPAIR FAILURE; D.The command produces an error because the ADVISE FAILURE command was not executed before the REPAIR FAILURE command. Q33. 更改为归档模式 1. Shut down the instance. 2. Execute the ALTER DATABASE ARCHIVELOG command. 3. Start up the instance and mount the database. 4. Set the DB_RECOVERY_FILE_DEST initialization parameter to $ORACLE_HOME/dest_1. 5. Open the database. C.1,3,2,5; 4 not required Q34. Which two statements are true regarding the SGA_TARGET initialization parameter? (Choose two.) A.It can be increased up to the value of the SGA_MAX_SIZE parameter. B.Increasing the value of the SGA_TARGET parameter distributes the increased memory among all the autotuned components. 解析：SGA_TARGET最大能增加到SGA_MAX_SIZE规定的大小，当增大SGA_TARGET的大小，同时会自动增加其他组件的大小. Q35:Which statement is true about the ManageabilityMonitor (MMON) background process? A.It transfers statistics统计信息 from memory to disk at regular intervals每隔一定时间. 数据库（默认情况下）每60 分钟从SGA 中自动捕获一次统计信息，然后将其以快照形式存储在AWR 中。这些快照通过一个名为易管理性监视器(MMON) Manageability Monitor 的后台进程存储在磁盘上。默认情况下，快照会保留八天。可以修改快照时间间隔和保留间隔 Q36. 36:Which two statements are true regarding Oracle Data Pump? (Choose two.) 选项 A.EXPDP and IMPDP are the client components of Oracle Data Pump. B.DBMS_DATAPUMP PL/SQL packages can be used independently of the Data Pump clients. Q37. 37: Automatic Shared Memory Management (ASMM) has been enabled for your database instance. The initialization parameters for the components that are managed by ASMM are not set. After observing the effects of ASMM,you executed the following command: SQL> ALTER SYSTEM SET DB_CACHE_SIZE = 100M; Which statement is true in this scenario? A.The minimum memory size for the database buffer cache is set to 100 MB. 启用ASMM 之前，应该从 SPFILE 中删除单个内存区参数，因为设置这些参数会使 ASMM 受到限制。看到 ASMM 分配的效果之后，如果决定要调整特定组件的分配值，可 以为这些组件指定值。如果指定的值比目前的值小，这些值将被视为各个组件的最小内存 大小。如果指定的值比目前的值大，那么只要有空闲内存，就可以将内存组件的大小重新 上调至您提供的值。执行此操作会限制可用于自动调整的内存量，但在环境需要对大小进 行特殊调整、而ASMM 又无法满足此需要时，可使用此功能。 Q38. 38: Your test database is configured to run in NOARCHIVELOG mode. One of the data files in the USERS tablespace is lost due to a media failure. You notice that all the online redo logs have been overwritten since the last backup. 非归档模式，数据文件丢失，上次备份后在线redo log 已经被重写。 的恢复方式 C.Shut down the instance,restore all the database files from the last consistent backup,and restart the database instance. 关闭实例，从上一个一致性备份中恢复数据文件，重启数据库实例 Q39. 39: Which two files in the database can be configured for automatic backups by using the autobackup feature in Recovery Manager (RMAN)? (Choose two.) 使用rman的自动备份特性自动备份的文件， B.control files E.server parameter file Q40: You notice that the performance of the database has degraded because of frequent checkpoints. Which two actions resolve the issue? (Choose two.) 反复检查点导致性能下降 B.Check the size of the redo log file size and increase the size if it is small. 检查redolog的文件大小，如果小则增加大小 C.Set the FAST_START_MTTR_TARGET parameter as per the advice given by the MTTR Advisor. 参数FAST_START_MTTR_TARGET参数是一个加快实例恢复的参数，我们可以根据服务界别来定义一个合理的、可接受的值。该值得单位为秒。比如设定为60S，假定该值处于合理的情况之下，则一旦实例崩溃，在60S以内实例应当能够被恢复。合理即该值不能太大，也不能太小。太大则实例恢复所需的时间较长，太小则导致大量数据的及时写入，增加了系统的I/O。 影响实例恢复时间长短的主要因素是从最近检查点位置到联机重做日志尾部之间的距离。距离越长则所需要的cache recovery和undo、redo的时间越长。所以如何有效的缩短最近检查点位置与联机重做日志尾部之间的距离，正是FAST_START_MTTR_TARGET的目的。 FAST_START_MTTR_TARGET的值实际上是通过触发检查点来实现它的目的的。当内存中产生的dirty buffer所需的恢复时间（estimated_mttr）到达FAST_START_MTTR_TARGET所指定的时间，则检查点进程被触发。检查点进程一旦被触发，将通过DBWn进程按检查点队列顺序将脏数据写入到数据文件，从而缩短了最后检查点位置与联机重做日志间的距离，减少了实例恢复所需的时间。 Q41. View the Exhibit1,which shows the options that are selected to create the user SL_REP. 用户创建密码认证 View the Exhibit2 and examine the contents of DATABASE_PROPERTIES. 配置文件的内容 Which two statements are true regarding this user? (Choose two.) B.The objects created by the user are placed in the USERS tablespace. C.The user does not require quota(配额) on the TEMP tablespace to perform sort operations. Q42. Which two statements are true regarding transactions in an Oracle database? (Choose two.) 选项 A.Multiple transactions can use the same undo segment. B.A transaction is assigned an undo segment when it is started. Q43. 43: Examine the commands executed in a DBA session: SQL> CREATE BIGFILE TABLESPACE MRKT 2 DATAFILE '/u01/app/oracle/oradata/orcl/mrkt.dbf' size 10M LOGGING 3 EXTENTMANAGEMENT LOCAL SEGMENT SPACE MANAGEMENTAUTO; Tablespace created. SQL> ALTER DATABASE DEFAULT TABLESPACE MRKT; Database altered. Which two statements are true regarding the MRKT tablespace? (Choose two.) A.No more data files can be added to the tablespace. C.A user created without being assigned a default tablespace uses this tablespace. 答案解析： 1、BIGFILE只能有一个数据文件。A对 2、ALTER DATABASE DEFAULT TABLESPACE MRKT;设置默认表空间，以后用户创建的数据都会放在这个表空间里。C对。 3、创建BIGFILE时，oracle自动创建本地管理表空间和自动段空间管理（ASSM）。B错 Oracle Database automatically creates a locally managed tablespace with automatic segment space management. 4、正在使用的默认表空间不能被drop。 D错。 Q44. Note the following points describing various utilities in Oracle Database 11g: 1. It enables the 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 tables of an Oracle database. 4. It provides a tape backup management for the Oracle ecosystem. Which point describes the Oracle Data Pump utility? Answer: D 4 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 负责整个备份解决方案，因此该解决方案可提供更好的客户支持服务 Q45. 3. The user may not have more than three simultaneous sessions. 4. The user session must automatically be logged off if more than 10 minutes elapsed time used. How would you accomplish the above? A.by assigning profiles for the users 创建配置文件 Q46. Which two statements are true about setting the FAST_START_MTTR_TARGET initialization parameter to a nonzero value? (Choose two.) B.Automatic checkpoint tuning will be enabled. C.The value for the LOG_CHECKPOINT_INTERVAL initialization parameter will be override the value for FAST_START_MTTR_TARGET. A。 MTTR advisor禁用，错误。 To enable MTTR Advisor,set the two initialization parameters STATISTICS_LEVEL and FAST_START_MTTR_TARGET. STATISTICS_LEVEL governs whether all advisors are enabled and is not specific to MTTR Advisor. Ensure that it is set to TYPICAL or ALL. Then,whenFAST_START_MTTR_TARGET is set to a nonzero value,the MTTR Advisor is enabled. 故A错误。 MTTR 指导可将FAST_START_MTTR_TARGET值转换为多个参数，以便在所需时间内（或者在尽量接近此时间的范围内）启用实例恢复。请注意，将禁用 FAST_START_MTTR_TARGET参数显式设置为0会禁用MTTR 指导。 故D错误。是在所需时间内或在尽量接近此事件的范围内，并不是指定的时间点。 FAST_START_MTTR_TARGET参数的设置值必须支持系统的服务级协议。如果MTTR 目标的值较小，则会因增加了数据文件写入次数而增加I/O 开销（这会影响性能）。但是，如果MTTR 目标设置得过大，则实例在崩溃后需要花费较长的时间才会恢复。 而快速启动检查点功能是通过指定所需的非零恢复平均时间（MTTR）值来启用的，该值将用于设置FAST_START_MTTR_TARGET初始化参数。此参数控制数据库对单个实例执行崩溃恢复所花费的时间量。启用快速启动检查点功能时，oracle将自动保持检查点的速度，已便达到请求的MTTR.如果将该值设置为0，将禁用此功能。 故B正确。 FAST_START_MTTR_TARGET enables you to specify the number of seconds the database takes to perform crash recovery of a single instance. When specified,FAST_START_MTTR_TARGET is overridden by LOG_CHECKPOINT_INTERVAL. FAST_START_MTTR_TARGET 会被 LOG_CHECKPOINT_INTERVAL所覆盖，故C正确 Q47. Automatic Shared Memory Management is disabled for your database instance. You realize that there are cases of SQL statements performing poorly because of repeated parsing(重复的语法分析) activity,resulting in degradation of performance.What would be your next step to improve performance? D.Run the Memory Advisor for the shared pool. Q48. Which task would you perform before you run Oracle Universal Installer (OUI) in silent or suppressed mode for an installation? A.Run the root.sh script. B.Create the oraInst.loc file. C.Create the tnsnames.ora file. D.Run the oraintRoot.sh script. Answer: B 答案解析： ORACLE 静默安装 Creating the oraInst.loc File If you plan to install Oracle products using Oracle Universal Installer in silent or response file mode,then you must manually create the oraInst.loc file if it does not exist. This file specifies the location of the Oracle Inventory directory where Oracle Universal Installer creates the inventory of Oracle products installed on the system. Note: If Oracle software was installed previously on the system,the oraInst.loc file might exist. If the file does exist,you do not have to create a file. To create the oraInst.loc file,follow these steps: Switch the user to root: $ su - root Create the /var/opt/oracle directory if it does not exist: Change the directory: # cd /var/opt/oracle Use a text editor to create the oraInst.loc file,containing the following lines: inventory_loc=u01/app/oraInventory inst_group=oinstall In this example,inventory_loc is the location of the Oracle inventory; and the inst_group parameter shows the name of the Oracle Inventory group (in this example,oinstall). Enter the following commands to set the appropriate owner,group,and permissions on the oraInst.loc file: # chown oracle:oinstall oraInst.loc # chmod 664 oraInst.loc Q49. You are using flat files as the data source for one of your data warehousing applications. To optimize the application performance,you plan to move the data from the flat files to clustered tables in an Oracle database. While migrating the data,you want to have minimal impact on the database performance and optimize the data load operation. Which method would you use to load data into the Oracle database 迁移flat file 到数据库表中，性能影响最小 C.Use the conventional path data load of the SQL*Loader utility. 使用sqlloader工具 Q50. 50: Given below is a list of scenarios: 1) A user terminates his session abnormally. 2) The connection between the server and the client application terminates because of a network failure. 3) A schema is accidentally dropped. 模式意外掉线 4) The tablespace is accidentally dropped from the database. 表空间从数据库意外掉线 5) The hard disk gets corrupted and the data files in the disk are lost. 磁盘损坏 6) The database instance abnormally shuts down because of power failure.数据库自动读取redolog恢复 Which scenarios require DBA intervention to perform recovery? 那种场景需要DBA介入完成恢复 C.3,4,and 5 Q51. 174. Which two statements are true regarding B-tree index? (Choose two.) A.The leaf blocks in the index are doubly linked. B.The leaf node stores a bitmap for each key value. C.The rows with NULL value in key columns also have entries in the index. D.The deletion of a row from the table causes a logical deletion in index leaf block and the space becomes available for the new leaf entry. Answer: AD 答案解析： 参考：http://blog.csdn.net/rlhua/article/details/12780775 A，在索引的叶块是双向链表。正确。叶块在两个方向都是相互关联的，这便于按键值升序或降序扫描索引。 B，叶节点为每个键值存储的是一个位图。错误。是ROWID C，键列中的NULL值的行在索引中也有条目。错误。当某行包含的所有键列为NULL 时，该行没有对应的索引条目。因此，当WHERE子句指定了NULL 时，始终会导致全表扫描。 D，从表中的行的删除操作导致逻辑删除索引的叶块中的空间变为可用的新的叶条目，正确。删除一行只会导致对索引条目进行逻辑删除。在删除块中的所有条目之前，已删除行占用的空间不可用于新条目。在删除后，便可用于新条目 52: For which database operation would you need the database to be in the MOUNT state? 更改归档状态 数据库需要mount状态 E.configuring the database instance to operate in the ARCHIVELOG or NOARCHIVELOG modes Q53. 48. The job to gather 收集optimizer优化 statistics统计信息 for objects runs as part of the automatic maintenance window自动维护窗口 in your database instance. At a certain point of time,the maintenance window closes before the statistics are gathered for all objects. 在收集完成之前窗口关闭 Which statement is true in this scenario? D.The job is terminated and the statistics for the remaining objects are collected the next time that the maintenance window is opened. Answer: D 答案解析： 题问：一个作业在你的数据库实例的自动维护窗口中为一些对象正在搜集优化统计信息，在一个特定的时间点，所有对象的统计信息还没有搜集完，维护窗口就关闭了，此时： 这个作业被终止，剩下的统计信息的搜集将在下次维护窗口打开的时候继续搜集 Q54. 12. Which is the correct description of a pinned buffer in the database buffer cache? A.The buffer is currently being accessed. Every buffer has an access mode: pinnedor free(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. Q55. 6. In your production database生产数据库,data manipulation language (DML) operations are executed on the SALES table在表SALES上执行DML操作. You have noticed注意到 some dubious values不可靠的值 in the SALES table during the last few days过去的几天. You are able to track users,跟踪用户 actions taken动作执行,and the time of the action for this particular period特定阶段的动作 but the changes in data are not tracked.但是数据没有跟踪 You decide to keep track of both the old data and new data in the table along with the user information.你决定 老数据和新数据 连同用户信息 一起跟踪 What action would you take to achieve this task? B.Implement value-based auditing. 进行数据库审计时会记录审计对象中发生的插入、更新和删除操作，但是不会捕获更改的实际值。 要扩展数据库审计，可使用基于值的审计，利用数据库触发器（事件驱动的PL/SQL 构造）来捕获更改的值。 用户在连接了相应触发器的表中插入、更新或删除数据时，触发器在后台将审计信息复制到包含审计信息的表中。 因为审计触发器代码在每次插入、更新或删除操作发生时都必须执行，所以与标准数据库审计相比，使用基于值的审计时，性能下降幅度比较大。 性能下降幅度取决于触发器代码的效率。只在标准数据库审计捕获的信息不足的情况下，才使用基于值的审计。 基于值的审计由用户或第三方代码实施。Oracle DB 提供了可用来构建基于值的审计系统的PL/SQL 构造。 基于值的审计的关键部分是审计触发器，这是一个单纯为了捕获审计信息而构造的PL/SQL 触发器。 Q56: You are managing an Oracle Database 11g database. The database is open,and you plan to perform Recovery Manager (RMAN) backups. Which three statements are true about these backups? (Choose three.) B.The backups would be inconsistent. 备份非一致性的 C.The backups would be possible only if the database is running in ARCHIVELOG mode. 必须为归档模式 E.The backups need to be restored and the database has to be recovered in case of a media failure 假设介质故障，备份需要还原和数据回复 Q57. SQL> ALTER TABLESPACE undotbs1 RETENTION GUARANTEE; B.The extents containing committed undo in the undo tablespace are not overwritten for at least 15 minutes. GUARANTEE ： 保证undo_retention参数所设定的时间有效，这个是10g的新功能。 Q58: View the Exhibit and examine the output of the query. What do you infer from this? C.The server parameter file (SPFILE) was used to start the instance. spfile这个方法，如果value有值，则说明使用spfile启动。如果没有值，则又pfile启动。 s@TEST0924> select name,value,isspecified from v$spparameter where name like '%sga%'; NAME VALUE ISSPEC -------------------- -------------------- ------ sga_max_size FALSE pre_page_sga FALSE lock_sga FALSE sga_target 0 TRUE sys@TEST0924> show parameter spfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string /u01/app/oracle/product/11.2.0 /dbhome_1/dbs/spfiletest0924.o ra 使用spfile启动时，sga_target 的isspecified为true，如果为false，则是使用pfile启动； 也可以通过 show parameter spfile这个方法，如果value有值，则说明使用spfile启动。如果没有值，则又pfile启动。 下面用pfile启动，然后再来查询这 Q59. ：View the Exhibit and examine the output. Which two statements are true regarding the LISTENER2 listener? (Choose two.) B.The ORCL instance is registered statically in the listener.ora file. D.The total number of client connections handled so far by the service handler is two. 通过UNKNOWN可以知道这是静态注册，参考问题19，因此选B。通过established为2可以知道，目前建立了两个连接，因此选D。 Q60. 60: 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? 通过DBCA创建数据库 Global DATABASE name 由什么组成 A.It must be made up of a database name and a domain name.数据库名和域名 Q61. User SCOTT wants to perform a bulk insert operation 大量插入操作in the EMP_DEP table. SCOTT receives the following error after the INSERT statement is issued and few rows are inserted: INSERT INTO EMP_DEP (emp_id,name,salary,dep_name,mgr_id) * ERROR at line 1: ORA-01653: unable to extend table SCOTT.EMP_DEP by 128 in tablespace USERS 不能扩展表 Identify two actions either of which will help you resolve this problem. (Choose two.) B.Add data files to the USERS tablespace. 增加数据文件 E.Increase the size of the data file associated with the USERS tablespace 增加与用户表空间相关联的数据文件大小 Q62. 62: The HR user creates a stand-alone procedure as follows and grants the EXECUTE privilege on the procedure to many database users B.Create the procedure with invoker's right. 创建有调用权限的过程 用户有执行procedure 的权限以至于能够把记录插入到DEPARTMENTS，即使这些用户没有插入DEPARTMENTS表的权限。 你只想只有能够插入DEPARTMENTS表的数据的那些用户执行procedure 成功。 有什么建议来实现？ PL/SQL 还提供的数据保护功能。调用方不必知道要读取或处理的数据结构便可进行调用。 另外，调用方不必具有访问这些对象的权限，只需要具有执行PL/SQL 程序的权限就足够了。 可以选择使用另一种模式的权限来调用PL/SQL，这种情况下，调用方必须有权执行调用程序运行期间执行的每个语句。 63: Some non-DBA users in your database have been granted ANY TABLE system privileges and they are able to access data dictionary base tables. You decide to restrict限制 their access to data dictionary objects.Which method would you adopt to achieve this objective? 非DBA用户有ANY TABLE权限，能够访问数据字典表，现在觉定限制他们访问数据字典 采取的措施 E.Set the value of the O7_DICTIONARY_ACCESSIBILITY parameter to FALSE. O7_DICTIONARY_ACCESSIBILITY参数控制对数据字典的访问.设置为true,如果用户被授予了如select any table等any table权限,用户即使不是dba或sysdba用户也可以访问数据字典.在9i及以上版本默认为false,8i及以前版本默认为true.如果设置为true就可能会带来安全上的一些问题.这也就为什么O7_DICTIONARY_ACCESSIBILITY 设置为false时,sys不能以normal方式登录的原因. 64: Examine the following output: SQL> SELECT index_name,status FROM dba_indexes WHERE status='UNUSABLE'; INDEX_NAME STATUS ------------------------------ ----------- EIND UNUSABLE Which two statements about the above index are true? (Choose two.) A.It is ignored by the query optimizer. B.It is not used while the index is being rebuilt. 65: You have recently collected statistics on certain objects of a schema in your database. But you observe suboptimal execution plans for the queries on these objects after two days of statistics collection. The optimizer statistics retention period is set to its default value. Which action would help to use the previous set of statistics on the objects? 取得两天前的统计信息。 A.Restore statistics from statistics history. A,正确，可以从统计信息的历史信息恢复统计信息。如下面这个储存过程。 RESTORE_TABLE_STATS Procedure This procedure restores statistics of a table as of a specified timestamp (as_of_timestamp). The procedure will restore statistics of associated indexes and columns as well. If the table statistics were locked at the specified timestamp the procedure will lock the statistics. The procedure will not restore user defined statistics. Syntax DBMS_STATS.RESTORE_TABLE_STATS ( ownname VARCHAR2,tabname VARCHAR2,as_of_timestamp TIMESTAMP WITH TIME ZONE,restore_cluster_index BOOLEAN DEFAULT FALSE,force BOOLEAN DEFAULT FALSE,no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (GET_PARAM('NO_INVALIDATE'))); 66: You have issued a SHUTDOWN ABORT command to bring down your database instance. Consider the 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,6,4 67: The database instance is currently using SPFILE. View the Exhibit and examine the error that you received 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 of the 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,6 68: Identify two situations in which the block header grows in a data block. (Choose two.) 选项 A.when row directories need more row entries D.when more transaction slots are required than are initially configured 69: The session of user SCOTT receives the following error after executing an UPDATE command on the EMP table: ERROR at line 1: ORA-00060: deadlock detected while waiting for resource On investigation,you find that a session opened by user JIM has a transaction that caused the deadlock. Which two statements are true regarding the session of SCOTT in this scenario? (Choose two.) scott资源死锁命令执行失败， JIM也同时打开了一个会话。 处理操作 B.SCOTT should perform a COMMIT or ROLLBACK to allow JIM to continue with his transaction. SCOTT 应该执行提交或者回滚，允许JIM继续它的事务 D.SCOTT has to reexecute the last command in the transaction after he commits the transaction scott必须重新执行最后的命令 ，在提交事务后。 70: Your database instance is running with full workload after database creation. You have decided to use a fixed-size undo tablespace. You want to use the Undo Advisor to estimate the capacity of the undo tablespace. Which two factors must you consider before using the Undo Advisor to estimate the capacity of the undo tablespace? (Choose two.) 数据库建立之后，数据库实例满负载运行，你决定调整UNDO 表空间的大小，使用UNDO顾问评估undo表空间的容量下面哪两个你必须考虑，在使用undo顾问评估容量之前？ A.the retention period to support flashback 支持闪回的保存期 B.the expected length of the longest-running query 查询最长运行的预期长度 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |