OCP 1Z0-053 Answers

1.About deploying a new application module,The plan baselines can be evolved over time to produce better performance. At the same time ,

 The new SQL statements will initially run with the plans that are known to produce good performance

under standard test configuration.

2.About   increasing the size of the moving window , the moving window size should be equal to or less than the Automatic Workload Repository (AWR) retention period.

3.You discover that the online redo log file of the current redo group 1 is corrupted.To solve this problem,you can ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1;

4.A program、a job、a schedule is  three components that the Scheduler uses for managing tasks within the Oracle environment.

5.Concerning About Data Block Integrity Check ,it was performed manuallyand performed to check the disk image block corruptions.

6.You use RMAN to import metadata from  RCAT10 (10g) into the recovery catalog owned by RCO11 (11g),when the RCO11 catalog has scripts with the same name as that of the scripts in RCAT10 catalog,RMAN will renames the global scripts that are imported from the RCAT10 catalog.

7.An access control list (ACL)  is  a list of users and network privileges stored in the XML file according to which a group of users can connect to one or more hosts.

8.About online redefinition for the migration of BasicFile LOBs to SecureFile LOBs,It can be done at the table level or partition level.

9. When using DBMS_SPACE.SPACE_USAGE procedure for LOB space usage,It returns space usage of only SecureFile LOB chunks.

10.When backup optimization is enabled in RMAN,The RMAN makes the backup because no backup of the tablespace exists within the seven day window 仅当符合保留策略的备份不存在时，备份优化才会让RMAN 备份只读表空间

11.CHANGE BACKUPSET 231 NOKEEP  means that the backup becomes eligible for deletion according to the existing retention policy instead of deleting the backup.

12.Why is theSignificance Level threshold type not available in the threshold setting。 Because Current Open Cursors Count is not a basic metric.  

13.Regarding hot patching ,It does not require database instance shutdown and can detect conflicts between two online patches. 

15.The outcome of SQL "EXEC catowner.dbms_rcvcat.create_virtual_catalog;" is to create a virtual catalog for pre-Oracle 11g clients.

16. TSPITR is Tablespace Point in Time Recovery. You queried the TS_PITR_OBJECTS_TO_BE_DROPPED view and realized that there are a large number of objects thatwould be dropped when you start the recovery by using  TSPITR . To preserve these objects, you should Perform Export before TSPITR and Import after TSPITR.

17.It produces an error because the partitions specified for merging are not adjacent.

20.

B. A user belonging to DSS_QUERIES can log in to a new session but the session will be queued

C. The CPU_WAIT_TIME column indicates the total time that sessions in the consumer group waited for

the CPU due to resource management

21.Prerequisites Specific to Active Database Duplication:

A、The source database must be mounted or open. If the source database is open, then archiving must be enabled. If the source database is not open, then it must have been shut down consistently.

B、When you connect RMAN to the auxiliary instance, you must provide a net service name. This requirement applies even if the auxiliary instance is on the local host.

22. To check the impact on the performance of the queries if the index is not available, You can make the index invisible.

24. The information gathered by SQL Test Case Builder includes the query being executed, table and index definitions (but not the actual data), PL/SQL functions, procedures, and packages, optimizer statistics, and initialization parameter settings.

25.置于Flashback data archive中的table的一些限制

  追踪表(Tracked table)，也就是指定将历史数据保存到某个flashback data archive中的table，不能执行DDL操作(add column除外)。

26.IMPORT CATALOG rcat10/oracle@inst1 NO UNREGISTER will import all metadata from the RCAT10 catalog and  register all databases registered in the RCAT10 catalog.

27.you use to make more space available in the flash recovery like thie :   Use OS command to move files from the flash recovery area to some other location ||  Use the RMAN CROSSCHECK command to reclaim the archived log space.

28. EXECUTE DBMS_STATS.GATHER_TABLE_STATS('SH','SALES',GRANULARITY=>'GLOBAL'); 

You need statistics to be collected more quickly, you can set the INCREMENTAL value to TRUE for the partition table.

默认情况下INCREMENTAL 为false，如果将其设置为true，那么在搜集信息时，oracle只扫描改变的分区，这样搜集信息就会变得更快

29.Regarding the Automatic Diagnostic Repository (ADR) , 

In an Oracle Real Application Clusters environment with Oracle ASM, each database instance, Oracle ASM instance, and listener has an ADR home.

If environment variable ORACLE_BASE is set, DIAGNOSTIC_DEST is set to the directory designated by ORACLE_BASE.

If environment variable ORACLE_BASE is not set, DIAGNOSTIC_DEST is set to ORACLE_HOME/log

32.Before restoring the SPFILE and the control file by using the control file autobackup ,You shouldstarting up the database instance in the NOMOUNT state

须启动到nomount状态来restore spfile和control file

33.ALTER DISKGROUP data CHECK;

34.Use  the command 

"BACKUP DATABASE

TAG TESTDB

KEEP UNTIL 'SYSDATE+1'

RESTORE POINT TESTDB06; " 

 Archived redo logs are backed up only if the database is open during the backup.

 The backup becomes obsolete( instead of deleting ) after one day, regardless of the default retention policy settings.

35.You want the Automatic SQL Tuning process to stop accepting and implementing the recommended

SQL profiles automatically。You can use the DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETERS procedure to set ACCEPT_SQL_PROFILES to FALSE.

36.About the functionality of the remap ,

It repairs blocks that have read disk I/O errors. 

It reads the blocks from a good copy of an ASM mirror and rewrites them to an alternate location on disk if the blocks on the original location cannot be read properly.

38.SQL Tuning Advisor invokes Automatic Tuning Optimizer to generate recommendations. Recommendations to accept SQL profiles occur in a finding.

39. Run the Access Advisor for the SQL statement.

40.To tune a single SQL statement, the QUICK_TUNE procedure accepts as its input a task_name and a SQL statement. The procedure creates a task and workload and executes this task.

41.That you configure the sqlnet.ora file in the client machine to set a nonzero value for the OCI_RESULT_CACHE_MAX_SIZE parameter is to avoid round trips to the server byenabling caching of query results in client memory

42. the SQL text 、the execution context and the execution frequency is captured by  SQL Performance Analyzer to analyze the SQL workload.

43.It is possible with the NOCASCADE_FORCE option to back out transaction T2

44.SQL访问指导：评估 SQL 的全部工作量, 并建议索引, 分区和实体化视图, 它们将改善 SQL 工作量的总体性能。

45.Setting the ASM-preferred mirror read for the Stretch cluster configuration canimproves performance by reading from a copy of an extent closest to the node.

46.SQL Tuning Advisor provides options to manage the scope and duration of a tuning task. You can set the scope of a tuning task either of the following:

• Limited

  In this case, SQL Tuning Advisor produces recommendations based onstatistical checks, access path analysis, andSQL structure analysis.SQL profile recommendations are not generated.

• Comprehensive

  In this case, SQL Tuning Advisor carries out all the analysis it performs under limited scope plus SQL Profiling. With the comprehensive option you can alsospecify a time limit for the tuning task, which by default is 30 minutes.

47."REPORT NEED BACKUP DAYS 5" means that it will apply the current retention policy to determine the files that need to be backed up.

REPORT NEED BACKUP DAYS n

Displays files that require more than n days' worth of archived redo log files for recovery；

展示由于恢复而有需要使用超过n天归档重做日志文件 的那些文件。

48.There are no data files on raw devicesduring the I/O calibration process . The reason for this error is the FILESYSTEMIO_OPTIONS parameter is set to NONE.

49.If the lost redo log group is CURRENT, you must restore, perform cancel-based incomplete recovery,and open the database using the RESETLOGS option.

50.When some disks in a disk group are offline,you can ALTER DISKGROUP data MOUNT FORCE; 

   MOUNT FORCE = SHUTDOWN ABORT +  STARTUP MOUNT

51.The significance of "ALTER SYSTEM SET SEC_PROTOCOL_ERROR_FURTHER_ACTION = Drop,10;" is to terminate the client connection after 10 bad packets but the client can still reconnect, and attempt the same operation again.

在发现有10个坏包后终止客户端连接，但客户端仍然可以重新连接，并再次尝试同样的操作。

52.Set the DB_ULTRA_SAFE parameter to DATA_ONLY.

53.About the compression algorithm of RMANThe BZIP2 compression algorithm consumes more CPU resources than the ZLIB compression

algorithm.

54. The cached result becomes invalid when any structural change is done to the EMPLOYEES table.

55.你不需要配置多实施Oracle直接NFS客户端。按下列顺序直接搜索条目NFS挂载点：

$ORACLE_HOME/dbs/oranfstab

/etc/oranfstab

/etc/mtab

56.You discover that your Recycle Bin contains two tables with the same name. When you execute "FLASHBACK TABLE my_table TO BEFORE DROP RENAME TO my_table2;" . The outcome is that One of the tables is recovered from the Recycle Bin using a Last In First Out (LIFO) approach.

57.ASMB

58.AWR baselines can be created on the basis of two time values.

59. You do not want to scan the entire datafile every time an incremental backup is performed.
To enable the block change tracking feature ， you should ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE <path>;

60.You can use functionality provided by the Resource Manager in these two situation : 

   a.setting idle timeout limits on resource plans                                      在资源计划上设定空闲超时限制

   b.assigning priorities to jobs to manage access to system resources  给作业分配优先级对系统资源的访问进行管理

61.You can use the BACKUP AS COPY. command of RMANto convert your existing non-ASM files to ASM files for the database PROD

62.SQL Tuning Advisor  will analyze a single SQL statement and make recommendations for performance improvement .

63.You can use FLASHBACK_TRANSACTION_QUERY  to undo the changes made by a particular transaction without affecting the changes made by other transactions

使用闪回查询功能，可以执行截止到特定时间的查询。使用SELECT语句的AS OF子句，可以指定要查看其对应数据的时间戳。这在分析数据差异时非常有用。

64.You performed an incomplete recovery and opened the database with the RESETLOGS option.Regarding the archived redo log files, created in an earlier incarnation of the database , the archived redo log files are still maintained because the file names are unique. 

 (The LOG_ARCHIVE_FORMAT parameter is set to 'ora_%t_%s_%r.log')

66.set SGA_TARGET to a non-zero value and set DB_CACHE_SIZE to a non-zero value will enable Automatic Shared Memory Management(ASMM).

67. The VERSIONS clause of the Flashback Versions Query can use heap tablesAND Index-Organized Tables (IOTs) .

68.When You use "CREATE TEMPORARY TABLESPACE TEMP1 TEMPFILE '/u1/data/temp1.dbf' SIZE 10M TABLESPACE

GROUP '';The Command will not add the tablespace TEMP1 to any group. (Because the command miss G1 or G2 or G3 after Group.)

69. You want to execute the following query:

SELECT ename, sal FROM emp AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '6' MINUTE) WHERE ename = 'ALLEN';

you should confirm thatThe UNDO_MANAGEMENT parameter must be set to AUTO and The UNDO_RETENTION parameter must be set appropriately.

闪回查询是基于undo数据的，所以只要保证闪回查询的那个时间点有undo数据就能够查询成功

70.When use "ALTER TABLE employees SHRINK SPACE CASCADE;" , The shrink behavior will be cascaded to all dependent segments of the table that support a shrink operation.收缩行为将会级联到所有支持收缩的表的从属段。

71.You need to ensure that the ADDM analysis is run over a time span of two hours in future. You could Modify the AWR snapshot time interval to two hours.

72.You realize that the USERS tablespace is runninglow on space. You can use Segment Advisor to identify the segments that you should shrink .

73. You perform a compressed backup in these situation :

C. You are using disk-based backups and disk space inyour Flash Recovery Area, or other disk-based

backup destinations are limited.(When backup destinations are limited)

D. You are performing your backups to some device overa network where reduced network bandwidth is

more important than CPU usage.(When Reducing network bandwidth is moreimportant than CPU usage)

E. You are using some archival backup media, such asCD or DVD, where reducing backup sizes saves

media costs and archival storage.(When saving media costs and archivalstorage)

74.To maintain aconsistent performance of the database while adding or removing disks,youwould Define the POWER optionwhile adding or removing the disks ，  Increasethe number of ARBprocesses by setting up a higher value for ASM_POWER_LIMIT.

75.The snapshot that gathered statistics into AWR is stored on the disk through a daemon that is called manageability monitor(MMON).

76.Three key features of ASM :  file striping 、allocation unit mirroring、automatic disk rebalancing.

77.When  other database instances are connected to it ,the command "shutdown immediate" results in an error .

78.BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE，RMAN reads the block change tracking file only when it performs the incremental level 1 backup.

79. Image copies of the '/u01/app/oracle/oradata/orcl/users01.dbf' data file are updated with all changes up to incremental backup SCN.

映像副本会使用截至增量备份SCN 的所有更改来进行更新

80.A SQL profile is built by Automatic Tuning OptimizerAND can be used by the query optimizer automatically.

81.ALLOCATED_SPACE indicates both the space currently allocated and used, and the space that is available for reuse.

     FREE_SPACE indicates both the space that is currently allocated and available for reuse, and the space that is currently unallocated.

82. Here are the three predefined server-generated alerts : Tablespace Space Usage、Resumable Session Suspended、 Recovery Area Low On Free Space. (预警是当数据库处于不理想状态且需要引起注意时发出的通知)

83.To configure the Flashback Database ,  we should set Flash Recovery Area 、enable Flashback logging、start the database in the ARCHIVELOG mode.

84.

主要功能组件：

• DBMS_DATAPUMP：包括高速导出与导入实用程序的API，可用于成批地移动数据和元数据。

• 直接路径API (DPAPI)：Oracle Database 11g支持直接路径API 接口，可在卸载和加载时将数据转换与语法分析工作量降至最低。

• DBMS_METADATA：Worker 进程使用该组件卸载或加载所有元数据。数据库对象定义是使用XML 存储的，而不是SQL。

• 外部表API：使用ORACLE_DATAPUMP和ORACLE_LOADER访问驱动程序，可将数据存储在外部表中（即与平台无关的文件中）。使用SELECT语句可读取外部表，就像外部表存储在Oracle DB 中一样。

• SQL*Loader：与外部表集成在一起，因此可为外部表访问参数提供自动移植加载程序控制文件的功能。

• expdp和impdp：瘦客户机层，可通过调用DBMS_DATAPUMP程序包启动和监视数据泵操作。

• 其它客户机：得益于此基础结构的应用程序（如Database Control、复制应用程序、可传输表空间应用程序和用户应用程序）。SQL*Plus 也可用作DBMS_DATAPUMP的一个客户机，但只提供实时操作的简单状态查询。

85.Drop User:  When you drop a user, Oracle Database also purgesall of that user's schema objects from the recycle bin. 

    Cascade:All the objectsowned by scott are permanently dropped from the database.

86.When fails to open  the database ,It displays the message that the data file belonging to the users tablespace are corrupted.To recover the damaged data files are follows : Mount the database ->  Make the data file offline  ->  Restore the data file  

                                                        Recover the data file->  Make the data file online  ->  Open the database

87.When perform an RMAN backup of database as a copy, you will consider the two factors :

     a.The backup as copy can only be taken to disk.

     b.Backup will constitute all used and unused blocks in the database

88.Only one channel is allocated and the backup is created in the flash recovery area

 rman备份的路径应该是这样的优先级

a.备份语句中指定的format  > rman 中显现的configure channel device type disk format '/oracle/orclarch/%U_%d'的路径 > 闪回恢复区>$ORACLE_HOME/dbs 

b.手动分配通道会覆盖CONFIGURE配置的自动分配通道，并且rman的备份信息会默认放在fra中

89.灾难恢复包括在丢失了整个目标数据库、所有当前控制文件、所有联机重做日志文件、所有参数文件和恢复目录数据库（如果有）后还原和恢复目标数据库。

基本过程：

• 还原服务器参数文件的自动备份。

• 启动目标数据库实例。

• 从自动备份还原控制文件。

• 装载数据库。

• 还原数据文件。

• 恢复数据文件。

• 使用RESETLOGS选项打开数据库。（Open the database with the RESETLOGS option）

90. The FRA disk group has an asynchronous I/O bottleneck 

91. Server process：Database processes that handle a client request on behalf of a database. 用来处理连接到实例的用户进程(User Process)提交的请求（如查询语句：select empno from emp ;） 

92.Resumable set for session with sid 18 when occuring the situation as follow：(sid=18的会话被暂停，等待错误被清除，清除后，会话恢复)

93.Control filesand Archived log files required to perform recovery  require a recovery with the RESETLOGS option .

灾难恢复包括在丢失了整个目标数据库、所有当前控制文件、所有联机重做日志文件、所有参数文件和恢复目录数据库（如果有）后还原和恢复目标数据库。

94.