最新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解析：題目的意思是，在星期天執(zhí)行0級備份，在其他時候執(zhí)行1級備份，備份路徑為磁帶上級別0增量備份包含指定數(shù)據(jù)文件中的所有塊，但從未使用過的塊除外，等同于一個數(shù)據(jù)文件完全備份，完全備份策略不能與增量備份策略共用，它是獨立，級別1有兩種類型，一種是微分備份，它備份上一個級別0或1之后發(fā)生了變化的塊。一

4、種是累積備份，它備份上一個級別0備份以來所有發(fā)生了變化的塊因此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：動態(tài)查詢各個內(nèi)存組件的大小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):數(shù)據(jù)恢復顧問2.rman中可以使用如下命令進入DRAa.list failure b.advise failure c.repair failure d.change failure3.修正錯誤的時候數(shù)據(jù)庫處于哪種狀態(tài)取決于錯誤的類型，如果控制文件要恢復，數(shù)據(jù)庫處于nomount狀態(tài),

9、 如果是數(shù)據(jù)文件，則應該處于加載狀態(tài)或打開狀態(tài)4.一般數(shù)據(jù)庫崩潰時，首先使用list failure，然后使用advice failure，最后執(zhí)行repair failure5.list failure list failure：可以列出檢測出的錯誤以及錯誤的優(yōu)先級別（關鍵、高、低），狀態(tài)（打開、關閉） list failure detail:列出錯誤的詳細信息 list failure exclude failure n:可以從報告輸出中排除特定的錯誤號 具體參數(shù) ALL 列出所有錯誤 CRITICAL 只列出優(yōu)先級為關鍵的錯誤 HIGH 只列出優(yōu)先級為高的錯誤 LOW 只列出優(yōu)先級為低的

10、錯誤 CLOSED 只列出已經(jīng)關閉的錯誤 EXCLUDE FAILURE 從列表中，去除列出錯誤號的錯誤，該選項后跟隨一個由逗號隔開的希望去除的錯誤ID列表 DETAIL 提供列出的錯誤的其他細節(jié) # 實際列出的錯誤號 list failure closed 254; list failure closed exclude failure 242,248 -list failure 只能在單一的實例中運行，也不能在備用數(shù)據(jù)庫上使用 -如果出現(xiàn)在列表中的一個錯誤處于打開狀態(tài)，則意味著對當前的問題需要著手處理6.advise failure可以用于提供可使用的改正該錯誤的建議方案-也是只能在單一實

11、例中運行，也不能在備用數(shù)據(jù)庫上使用7.repair failure-運行該命令必須啟動目標數(shù)據(jù)庫實例，同樣只能在單一。repair failure preview ：查閱修復錯誤的操作，不會執(zhí)行修復8.change failurechange failure 187 priority low;-將187的錯誤更改為low-不能將closed的錯誤切換到open狀態(tài) 9.數(shù)據(jù)恢復顧問數(shù)據(jù)字典視圖以V$IR為前綴V$IR 提供關于錯誤的信息，注意該視圖中的記錄可能在視圖中存在父記錄V$IR_FAILURE_SET 該表提供了各種與錯誤關聯(lián)的指導記錄的列表，允許使用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解析：這道題是說，兩張表存在外鍵約束，現(xiàn)在想要先插入子表在插入父表，怎么處理這里主要是考察initially immediate和initially deferred第一種為馬上檢查，第二種為延遲檢查如果給表中添加行，那么只有在commit的時候才會對該約束進行檢查，延遲約束必須在創(chuàng)建的時候指出，不能創(chuàng)建好后再修改為延遲約束我們測試一下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解析：監(jiān)聽停止后，已經(jīng)連接的會話是不受影響的-首先在一個crt窗口連接上數(shù)據(jù)庫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窗口執(zhí)行下面命令 oraclewahaha3 admin$ lsnrctl stop -停止監(jiān)聽oraclewahaha3 admin$ lsnrctl status -查看狀態(tài)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; -不受影響，執(zhí)行dml也不受影響 DEPTNO DNAME LOC- - - 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS

22、 30 SALES CHICAGO 40 OPERATIONS BOSTONSQL exit -退出oraclewahaha3 $ sqlplus scott/tigerwahaha3 -重新連接，發(fā)現(xiàn)無法連接了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.查看狀態(tài) alter system switch logfile; -切換幾次日志 SQL select group#,status,type,member from v$logfile; -查看狀態(tài) 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; -查看組狀態(tài) GROUP# STATUS ARC - - - 1 CURRENT NO 2 INACTIVE YES 3 INACTIVE YES SQL !rm /u01/oracle/oradata/wahaha3/redo05.log -開始破壞一個日志文件 -日志中會出現(xiàn)如下狀態(tài)，測試的時候沒有發(fā)現(xiàn)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刪除，然后重建就可以了，丟失的屬于第二組，查看狀態(tài)，并且如果恢復需要使用 -那么將它寫入歸檔 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; -查看狀態(tài) 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、次，再次查看狀態(tài) -這里需要注意，如果文件沒有丟失，只是損壞了，可以使用如下語句進行修復 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解析：最小的應該是操作系統(tǒng)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總結(jié)：上面四個都可以改變，說明只要修改路徑后，在啟動一次閃回就修改了，但是這里存在另外一個問題，那就是之前的閃回是否可用，因為BCD都是在數(shù)據(jù)庫open狀態(tài)下進行關閉、或打開閃回功能的，

48、這是不是可能會造成歷史的閃回數(shù)據(jù)不可以使用了這里不知道應該怎樣驗證這里由一個事例是按照A執(zhí)行的，那就選擇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