最新ocp考试题库Oracle.1Z0-052解析

1、Exam A QUESTION 1 You perform differential incremental level 1 backups of your database on each working day and level 0 backup on Sundays, to tape.Which two statements are true about differential incremental backups? (Choose two.)A.The backup performed on Sundays contains all the blocks that have ev

2、er been used in the database.B.The backup performed on Sundays contains all the blocks that have changed since the last level 1 backup.C.The backup performed on each working day contains all the blocks that have changed since the last level 0 backup.D.The backup performed on Monday contains all the

3、blocks that have changed since the level 0 backup, and every other working day contains all the blocks that have changed since the level 1 backup.答案：AD解析：题目的意思是，在星期天执行0级备份，在其他时候执行1级备份，备份路径为磁带上级别0增量备份包含指定数据文件中的所有块，但从未使用过的块除外，等同于一个数据文件完全备份，完全备份策略不能与增量备份策略共用，它是独立，级别1有两种类型，一种是微分备份，它备份上一个级别0或1之后发生了变化的块。一

4、种是累积备份，它备份上一个级别0备份以来所有发生了变化的块因此A正确，B错误，C错误这个时累计备份（Cumulative Incremental ），D正确，这个就是differential incrementalQUESTION 2 View the Exhibit to examine the output produced by the following query at three different times sincethe database instance started and has experienced workloads of different capaciti

5、es:SQL SELECT substr(component, 0, 10) COMP, current_size CS,user_specified_size USFROM v$memory_dynamic_componentsWHERE current_size!=0;What do you infer from this?A.The database instance is running with manual PGA management.B.The database instance is running with manual shared memory management.C

6、.The database instance has the MEMORY_TARGET value set to a nonzero value.D.All sessions are connected to the database instance in dedicated mode, and no RMAN or parallel query operations have been performed.MM is set to 100 MB.答案：C解析：从图中可以看出sga_target和pga_target三次都不一样，说明启用了amm因此选择C， V$MEMORY_DYNAMI

7、C_COMPONENTS：动态查询各个内存组件的大小QUESTION 3 Identify two situations in which you can use Data Recovery Advisor for recovery. (Choose two.) A.The database files are corrupted when the database is open.B.The user has dropped an important table that needs to be recovered.C.The archived redo log files are miss

8、ing for which the backup is not available.D.The database is not opening because the required database files are missing.答案：AD解析：1.Data Recovery Advisor(DRA):数据恢复顾问2.rman中可以使用如下命令进入DRAa.list failure b.advise failure c.repair failure d.change failure3.修正错误的时候数据库处于哪种状态取决于错误的类型，如果控制文件要恢复，数据库处于nomount状态,

9、 如果是数据文件，则应该处于加载状态或打开状态4.一般数据库崩溃时，首先使用list failure，然后使用advice failure，最后执行repair failure5.list failure list failure：可以列出检测出的错误以及错误的优先级别（关键、高、低），状态（打开、关闭） list failure detail:列出错误的详细信息 list failure exclude failure n:可以从报告输出中排除特定的错误号 具体参数 ALL 列出所有错误 CRITICAL 只列出优先级为关键的错误 HIGH 只列出优先级为高的错误 LOW 只列出优先级为低的

10、错误 CLOSED 只列出已经关闭的错误 EXCLUDE FAILURE 从列表中，去除列出错误号的错误，该选项后跟随一个由逗号隔开的希望去除的错误ID列表 DETAIL 提供列出的错误的其他细节 # 实际列出的错误号 list failure closed 254; list failure closed exclude failure 242,248 -list failure 只能在单一的实例中运行，也不能在备用数据库上使用 -如果出现在列表中的一个错误处于打开状态，则意味着对当前的问题需要着手处理6.advise failure可以用于提供可使用的改正该错误的建议方案-也是只能在单一实

11、例中运行，也不能在备用数据库上使用7.repair failure-运行该命令必须启动目标数据库实例，同样只能在单一。repair failure preview ：查阅修复错误的操作，不会执行修复8.change failurechange failure 187 priority low;-将187的错误更改为low-不能将closed的错误切换到open状态 9.数据恢复顾问数据字典视图以V$IR为前缀V$IR 提供关于错误的信息，注意该视图中的记录可能在视图中存在父记录V$IR_FAILURE_SET 该表提供了各种与错误关联的指导记录的列表，允许使用v$IR_FAILURE视图与V$

12、IR_MANUAL_CHECKLIST视图连接V$IR_MANUAL_CHECKLIST 该视图提供了与错误有关的详细的信息消息，这些消息提供了如何手动修复错误问题的信息V$IR_REPAIR 当视图与v$IR_FAILURE和V$FAILURE_SET连接时，提供了由oracle建立的，用于改正检测到的错误，包含修复步骤的物理文件的指针 QUESTION 4 You have two tables with referential integrity enforced between them. You need to insert data to thechild table first

13、because it is going to be a long transaction and data for the parent table will be available ina later stage, which can be inserted as part of the same transaction.View the Exhibit to examine the commands used to create tables.Which action would you take to delay the referential integrity checking u

14、ntil the end of the transaction?A.Set the constraint to deferred before starting the transaction.B.Alter the constraint to NOVALIDATE state before starting the transaction.C.Enable the resumable mode for the session before starting the transaction.D.Set the COMMIT_WAIT parameter to FORCE_WAIT for th

15、e session before starting the transaction.答案：A解析：这道题是说，两张表存在外键约束，现在想要先插入子表在插入父表，怎么处理这里主要是考察initially immediate和initially deferred第一种为马上检查，第二种为延迟检查如果给表中添加行，那么只有在commit的时候才会对该约束进行检查，延迟约束必须在创建的时候指出，不能创建好后再修改为延迟约束我们测试一下SQL create table items(item_code number(4) constraint pk primary key deferrable initi

16、ally immediate);Table created.SQL create table orders(item_code number(4),constraint fk_item foreign key(item_code) references items(item_code) on delete cascade deferrable initially immediate);Table created.-直接插入子表失败SQL insert into orders values(10);insert into orders values(10)*ERROR at line 1:ORA

17、-02291: integrity constraint (HR.FK_ITEM) violated - parent key not found-临时修改为延迟约束SQL set constraints fk_item deferred;Constraint set.SQL insert into orders values(10);1 row created.SQL insert into items values(10);1 row created.SQL commit;Commit complete.SQLQUESTION 5 Your database is open and the

18、 LISTENER listener is running. The new DBA of the system stops thelistener by using the command:LSNRCTL STOPWhat happens to the sessions that are presently connected to the database instance?A.The sessions are able to perform only queries.B.The sessions are not affected and continue to function norm

19、ally.C.The sessions are terminated and the active transactions are rolled back.D.The sessions are not allowed to perform any operations till the listener is started.答案：B解析：监听停止后，已经连接的会话是不受影响的-首先在一个crt窗口连接上数据库oraclewahaha3 $ sqlplus scott/tigerwahaha3SQL select * from dept; DEPTNO DNAME LOC- - - 10 A

20、CCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON在另外一个crt窗口执行下面命令 oraclewahaha3 admin$ lsnrctl stop -停止监听oraclewahaha3 admin$ lsnrctl status -查看状态LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 27-JUL-2016 22:15:50Copyright (c) 1991, 2011, Oracle. All rights reserved.

21、Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)TNS-12541: TNS:no listenerTNS-12560: TNS:protocol adapter error TNS-00511: No listener Linux Error: 111: Connection refused 在第一个crt窗口验证是否中断会话SQL select * from dept; -不受影响，执行dml也不受影响 DEPTNO DNAME LOC- - - 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS

22、 30 SALES CHICAGO 40 OPERATIONS BOSTONSQL exit -退出oraclewahaha3 $ sqlplus scott/tigerwahaha3 -重新连接，发现无法连接了SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 27 22:17:12 2016Copyright (c) 1982, 2011, Oracle. All rights reserved.ERROR:ORA-12541: TNS:no listenerQUESTION 6 You executed this command to c

23、reate 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 terminat

24、ion.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.答案：A解析：这里考察的是临时表on commit preserve|delete rows指明

25、临时表中行的有效期，delete 说明这些行在事务的末尾要被删除，preserve 说明这些行在用户会话的末尾被删除如果没有指定on commit 那么默认为 delete Explanation/Reference:QUESTION 7 .In which of the scenarios will the DBA perform recovery? (Choose all that apply.)A.The alert log file is corrupted.B.A tablespace is accidentally dropped.C.One of the redo log mem

26、bers is corrupted.D.A database user terminates the session abnormally.E.The hard disk on which the data file is stored is corrupted.答案：BE解析：A:A说是告警文件损坏了，这个是不需要恢复的oraclewahaha3 trace$ ls -l | grep alert-rw-r-. 1 oracle oinstall 933597 Jul 20 20:13 alert_wahaha3.logoraclewahaha3 trace$ rm alert_wahaha

27、3.log -这里我们把它删除oraclewahaha3 trace$ pwd -看一下路径/u01/oracle/diag/rdbms/wahaha3/wahaha3/traceoraclewahaha3 trace$ sqlplus / as sysdba -登陆一下oraclewahaha3 trace$ ls -l | grep alert_ -查看是否有新的日志生成-rw-r-. 1 oracle oinstall 121 Jul 20 20:18 alert_wahaha3.logoraclewahaha3 trace$ date -看下当前时间Wed Jul 20 20:19:2

28、1 CST 2016B:表空间被删除了，这个需要进行恢复C:一个redo成员被删除，这个也不需要进行恢复1.查看当前是否做了多路 SQL select group#,status,type,member from v$logfile; GROUP# STATUS TYPE MEMBER - - - - 3 ONLINE /u01/oracle/oradata/wahaha3/redo03.log 2 ONLINE /u01/oracle/oradata/wahaha3/redo02.log 1 ONLINE /u01/oracle/oradata/wahaha3/redo01.log 2.配置

29、为多路 SQL alter database add logfile member /u01/oracle/oradata/wahaha3/redo04.log to group 1,/u01/oracle/oradata/wahaha3/redo05.log to group 2,/u01/oracle/oradata/wahaha3/redo06.log to group 3; 3.查看状态 alter system switch logfile; -切换几次日志 SQL select group#,status,type,member from v$logfile; -查看状态 GROU

30、P# STATUS TYPE MEMBER - - - - 3 ONLINE /u01/oracle/oradata/wahaha3/redo03.log 2 ONLINE /u01/oracle/oradata/wahaha3/redo02.log 1 ONLINE /u01/oracle/oradata/wahaha3/redo01.log 1 ONLINE /u01/oracle/oradata/wahaha3/redo04.log 2 ONLINE /u01/oracle/oradata/wahaha3/redo05.log 3 ONLINE /u01/oracle/oradata/w

31、ahaha3/redo06.log SQL select group#,status,archived from v$log; -查看组状态 GROUP# STATUS ARC - - - 1 CURRENT NO 2 INACTIVE YES 3 INACTIVE YES SQL !rm /u01/oracle/oradata/wahaha3/redo05.log -开始破坏一个日志文件 -日志中会出现如下状态，测试的时候没有发现v$logfile Errors in file /u01/oracle/diag/rdbms/wahaha3/wahaha3/trace/wahaha3_arc3

32、_2217.trc: ORA-00313: open failed for members of log group 2 of thread 1 ORA-00312: online log 2 thread 1: /u01/oracle/oradata/wahaha3/redo05.log ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory -这个时候只需要将那个redo删除，然后重建就可以了，丢失的属于第二组，查看状态，并且如果恢复需要使用 -那么将它写入归档 SQL

33、 alter database drop logfile member /u01/oracle/oradata/wahaha3/redo05.log;-删除掉 SQL alter database add logfile member /u01/oracle/oradata/wahaha3/redo05a.log to group 2;-添加一个新的 SQL select group#,status,type,member from v$logfile; -查看状态 GROUP# STATUS TYPE MEMBER - - - - 3 ONLINE /u01/oracle/oradata/w

34、ahaha3/redo03.log 2 ONLINE /u01/oracle/oradata/wahaha3/redo02.log 1 ONLINE /u01/oracle/oradata/wahaha3/redo01.log 1 ONLINE /u01/oracle/oradata/wahaha3/redo04.log 2 INVALID ONLINE /u01/oracle/oradata/wahaha3/redo05a.log 3 ONLINE /u01/oracle/oradata/wahaha3/redo06.log alter system switch logfile; -切换几

35、次，再次查看状态 -这里需要注意，如果文件没有丢失，只是损坏了，可以使用如下语句进行修复 alter database add logfile member /u01/oracle/oradata/wahaha3/redo05.log reuse to group 2;D：用户的会话被kill，这个也不需要恢复的E：这个需要恢复QUESTION 8 Note the following structures in your database server:1: Extents2: OS Blocks3: Tablespace4: Segments5: Oracle Data BlockWhic

36、h option has the correct arrangement of these structures from the smallest to the largest?A.2, 5, 1, 4, 3B.1, 2, 3, 4, 5C.5, 2, 1, 3, 4D.2, 1, 5, 4, 3答案：A解析：最小的应该是操作系统bolck，然后是oracle的block然后是extents，然后是segments，然后是tablespaceSQL l 1 select segment_name,tablespace_name,extents,blocks 2* from dba_segme

37、nts where tablespace_name=USERS and owner=SCOTT and segment_type=TABLESQL /SEGMENT_NA TABLESPACE EXTENTS BLOCKS- - - -DEPT USERS 1 8EMP USERS 2 16SALGRADE USERS 1 8从这里我们可以看出users表空间包括多个segment，segment为dept的包括多个extent，dept包括1个extent并且包括8个blocks，因此从这里可以看出来blocksextentssegment select flashback_on from

38、v$database;FLASHBACK_ON-YES-当前闪回开着SQL show parameter db_re NAME TYPE VALUE- - -db_recovery_file_dest string /home/oracle/db_recoverdb_recovery_file_dest_size big integer 4122Mdb_recycle_cache_size big integer 0SQL ! ls -l /home/oracle/db_recover/WAHAHA3/flashbacktotal 102420-rw-r-. 1 oracle oinstall

39、 52436992 Jul 21 15:40 o1_mf_cs0y16z7_.flb-rw-r-. 1 oracle oinstall 52436992 Jul 21 15:22 o1_mf_cs0y1mv6_.flb按照D的步骤来进行配置 D:3,2,1,4,5,6SQL ALTER DATABASE FLASHBACK OFF;Database altered.SQL alter system set DB_RECOVERY_FILE_DEST=/u01/oracle/fast_recovery_area scope=both;System altered.SQL shutdown imm

40、ediateSQL startup mount;SQL ALTER DATABASE FLASHBACK ON;SQL alter database open;-验证oraclewahaha3 flashback$ ls -ltotal 0oraclewahaha3 flashback$ pwd/home/oracle/db_recover/WAHAHA3/flashback-这里是原来的路径，文件没有了oraclewahaha3 flashback$ pwd/u01/oracle/fast_recovery_area/WAHAHA3/flashbackoraclewahaha3 flashb

41、ack$ ls -ltotal 102420-rw-r-. 1 oracle oinstall 52436992 Jul 21 15:52 o1_mf_cs0zrf5o_.flb-rw-r-. 1 oracle oinstall 52436992 Jul 21 15:51 o1_mf_cs0zrky5_.flb-这里是新改的路径，说明D也是对的，但是这里文件名是改变了按照C的步骤来进行配置 D:1, 4, 2, 6, 3, 5SQL shutdown immediateSQL startup mountSQL alter system set DB_RECOVERY_FILE_DEST=/ho

42、me/oracle/db_recover scope=both;SQL alter database open;SQL ALTER DATABASE FLASHBACK OFF;SQL ALTER DATABASE FLASHBACK ON;oraclewahaha3 flashback$ pwd/home/oracle/db_recover/WAHAHA3/flashbackoraclewahaha3 flashback$ ls -ltotal 102420-rw-r-. 1 oracle oinstall 52436992 Jul 21 16:03 o1_mf_cs10dhdd_.flb-

43、rw-r-. 1 oracle oinstall 52436992 Jul 21 16:02 o1_mf_cs10dw8o_.flb-新的路径下有了oraclewahaha3 flashback$ pwd/u01/oracle/fast_recovery_area/WAHAHA3/flashbackoraclewahaha3 flashback$ ls -ltotal 0-旧的没有了按照B的步骤进行配置B.1, 4, 3, 2, 6, 5SQL shutdown immediateSQL startup mount;SQL ALTER DATABASE FLASHBACK OFF;SQL al

44、ter system set DB_RECOVERY_FILE_DEST=/u01/oracle/fast_recovery_area scope=both;SQL alter database open;SQL ALTER DATABASE FLASHBACK ON;-验证oraclewahaha3 flashback$ ls -ltotal 102420-rw-r-. 1 oracle oinstall 52436992 Jul 22 00:57 o1_mf_cs1zqmq0_.flb-rw-r-. 1 oracle oinstall 52436992 Jul 22 00:57 o1_mf

45、_cs1zqq4m_.flboraclewahaha3 flashback$ pwd/u01/oracle/fast_recovery_area/WAHAHA3/flashback-新的路径下有了oraclewahaha3 flashback$ ls -ltotal 0oraclewahaha3 flashback$ pwd/home/oracle/db_recover/WAHAHA3/flashback-旧的没有了按照A的步骤进行配置A.2, 1, 4, 3, 5, 6SQL alter system set DB_RECOVERY_FILE_DEST=/home/oracle/db_rec

46、over scope=both;SQL shutdown immediate;SQL startup mount;SQL ALTER DATABASE FLASHBACK OFF;SQL ALTER DATABASE FLASHBACK ON;SQL alter database open;-验证oraclewahaha3 flashback$ ls -ltotal 102420-rw-r-. 1 oracle oinstall 52436992 Jul 22 01:01 o1_mf_cs1zz52l_.flb-rw-r-. 1 oracle oinstall 52436992 Jul 22

47、01:01 o1_mf_cs1zz7fy_.flboraclewahaha3 flashback$ pwd/home/oracle/db_recover/WAHAHA3/flashbackoraclewahaha3 flashback$ ls -ltotal 0oraclewahaha3 flashback$ pwd/u01/oracle/fast_recovery_area/WAHAHA3/flashback总结：上面四个都可以改变，说明只要修改路径后，在启动一次闪回就修改了，但是这里存在另外一个问题，那就是之前的闪回是否可用，因为BCD都是在数据库open状态下进行关闭、或打开闪回功能的，

48、这是不是可能会造成历史的闪回数据不可以使用了这里不知道应该怎样验证这里由一个事例是按照A执行的，那就选择A吧QUESTION 10 A user, who is authenticated externally, logs in to a remote machine and connects to the database instance. What action would you take to ensure that a user cannot connect to the database instance by merely logging in to a remote mach

49、ine?A.Set REMOTE_OS_ROLES to FALSE.B.Set the OS_ROLES parameter to FALSE.C.Set the REMOTE_OS_AUTHENT parameter to FALSE.D.Set the REMOTE_LOGIN_PASSWORD_FILE parameter to NONE.答案：C解析：REMOTE_OS_AUTHENT Value Consequences1.TRUE for the remote database An externally-authenticated user can connect to the remote database using a connected u