1z0-053: Oracle Database 11g: Administration II - Free download from Exam Files

The INV_HISTORY table is created using the command:

The following data has been inserted into the INV_HISTORY table:

You would like to store the data belonging to the year 2006 in a single partition and issue the command:

SQL> ALTER TABLE inv_history

MERGE PARTITIONS

FOR(TO_DATE('15-feb-2006','dd-mon-yyyy')),

FOR(TO_DATE('15-apr-2006'))

INTO PARTITION sys_py;

What would be the outcome of this command?

It executes successfully, and the transition point is set to '1-apr-2006'.
It executes successfully, and the transition point is set to '15-apr-2006'.
It produces an error because the partitions specified for merging are not adjacent.
It produces an error because the date values specified in the merge do not match the date values stored in the table.

Correct answer: C

You want to perform the following operations for the DATA ASM disk group:

Verify the consistency of the disk.

Cross-check all the file extent maps and allocation tables for consistency. Check whether the alias metadata directory and file directory are linked correctly. Check that ASM metadata directories do not have unreachable allocated blocks.

Which command accomplishes these tasks?

ALTER DISKGROUP data CHECK;
ALTER DISKGROUP data CHECK DISK;
ALTER DISKGROUP data CHECK FILE;
ALTER DISKGROUP data CHECK DISK IN FAILURE GROUP 1;

Correct answer: A

Explanation:

Syntax: ALTER DISKGROUP <disk_group_id> CHECK [REPAIR | NOREPAIR];The check_diskgroup_clause lets you verify the internal consistency of Oracle ASM disk group metadata. The disk group must be mounted. Oracle ASM displays summary errors and writes the details of the detected errors in the alert log. The CHECK keyword performs the following operations:Checks the consistency of the disk. Cross checks all the file extent maps and allocation tables for consistently. Checks that the alias metadata directory and file directory are linked correctly. Checks that the alias directory tree is linked correctly. Checks that Oracle ASM metadata directories do not have unreachable allocated blocks.

Syntax: ALTER DISKGROUP <disk_group_id> CHECK [REPAIR | NOREPAIR];

The check_diskgroup_clause lets you verify the internal consistency of Oracle ASM disk group metadata. The disk group must be mounted. Oracle ASM displays summary errors and writes the details of the detected errors in the alert log.

The CHECK keyword performs the following operations:

Checks the consistency of the disk.

Cross checks all the file extent maps and allocation tables for consistently. Checks that the alias metadata directory and file directory are linked correctly. Checks that the alias directory tree is linked correctly. Checks that Oracle ASM metadata directories do not have unreachable allocated blocks.

Which two statements are true regarding the functionality of the remap command in ASMCMD? (Choose two.)

It repairs blocks that have read disk I/O errors.
It checks whether the alias metadata directory and the file directory are linked correctly.
It repairs blocks by always reading them from the mirror copy and writing them to the original location.
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.

Correct answer: AD

Explanation:

Reference from the Oracle document release v11.1 at here:Repairs a range of physical blocks on a disk. The remap command only repairs blocks that have read disk I/O errors. It does not repair blocks that contain corrupted contents, whether or not those blocks can be read. The command assumes a physical block size of 512 bytes and supports all allocation unit sizes (1 to 64 MB). Reference from the Oracle document release v11.2 at here:The remap command marks a range of blocks as unusable on the disk and relocates any data allocated in that range.

Reference from the Oracle document release v11.1 at here:

Repairs a range of physical blocks on a disk. The remap command only repairs blocks that have read disk I/O errors. It does not repair blocks that contain corrupted contents, whether or not those blocks can be read. The command assumes a physical block size of 512 bytes and supports all allocation unit sizes (1 to 64 MB).

Reference from the Oracle document release v11.2 at here:

The remap command marks a range of blocks as unusable on the disk and relocates any data allocated in that range.

What is the advantage of setting the ASM-preferred mirror read for the stretch cluster configuration?

It improves resync operations.
This feature enables much faster file opens.
It improves performance as fewer extent pointers are needed in the shared pool.
It improves performance by reading from a copy of an extent closest to the node.

Correct answer: D

Explanation:

Preferred Read Failure Groups When you configure Oracle ASM failure groups, it might be more efficient for a node to read from an extent that is closest to the node, even if that extent is a secondary extent. In other words, you can configure Oracle ASM to read from a secondary extent if that extent is closer to the node instead of Oracle ASM reading from the primary copy which might be farther from the node. Using the preferred read failure groups feature is most useful in extended clusters.

Preferred Read Failure Groups

When you configure Oracle ASM failure groups, it might be more efficient for a node to read from an extent that is closest to the node, even if that extent is a secondary extent. In other words, you can configure Oracle ASM to read from a secondary extent if that extent is closer to the node instead of Oracle ASM reading from the primary copy which might be farther from the node. Using the preferred read failure groups feature is most useful in extended clusters.

Examine the following command:

ALTER DISKGROUP data MOUNT FORCE;

In which scenario can you use the above command to mount the disk group?

when ASM disk goes offline
when one or more ASM files are dropped
when some disks in a disk group are offline
when some disks in a failure group for a disk group are rebalancing

Correct answer: C

Explanation:

In the FORCE mode, Oracle ASM attempts to mount the disk group even if it cannot discover all of the devices that belong to the disk group. This setting is useful if some of the disks in a normal or high redundancy disk group became unavailable while the disk group was dismounted. When MOUNT FORCE succeeds, Oracle ASM takes the missing disks offline. If Oracle ASM discovers all of the disks in the disk group, then MOUNT FORCE fails. Therefore, use the MOUNT FORCE setting only if some disks are unavailable. Otherwise, use NOFORCE. In normal- and high-redundancy disk groups, disks from one failure group can be unavailable and MOUNT FORCE will succeed. Also in high-redundancy disk groups, two disks in two different failure groups can be unavailable and MOUNT FORCE will succeed. Any other combination of unavailable disks causes the operation to fail, because Oracle ASM cannot guarantee that a valid copy of all user data or metadata exists on the available disks.

In the FORCE mode, Oracle ASM attempts to mount the disk group even if it cannot discover all of the devices that belong to the disk group. This setting is useful if some of the disks in a normal or high redundancy disk group became unavailable while the disk group was dismounted. When MOUNT FORCE succeeds, Oracle ASM takes the missing disks offline.

If Oracle ASM discovers all of the disks in the disk group, then MOUNT FORCE fails. Therefore, use the MOUNT FORCE setting only if some disks are unavailable. Otherwise, use NOFORCE.

In normal- and high-redundancy disk groups, disks from one failure group can be unavailable and MOUNT FORCE will succeed. Also in high-redundancy disk groups, two disks in two different failure groups can be unavailable and MOUNT FORCE will succeed. Any other combination of unavailable disks causes the operation to fail, because Oracle ASM cannot guarantee that a valid copy of all user data or metadata exists on the available disks.

Which background process of a database instance, using Automatic Storage Management (ASM), connects as a foreground process into the ASM instance?

ASMB
PMON
RBAL
SMON

Correct answer: A

Explanation:

ASMB (ASM Background Process): Communicates with the ASM instance, managing storage and providing statistics, runs in ASM instances when the ASMCMD cp command runs or when the database instance first starts if the server parameter file is stored in ASM. ASMB also runs with Oracle Cluster Registry on ASM.RBAL (ASM Rebalance Master Process): In an ASM instance, it coordinates rebalance activity for disk groups. In a database instances, it manages ASM disk groups.PMON (Process Monitor): Monitors the other background processes and performs process recovery when a server or dispatcher process terminates abnormally.SMON (System Monitor Process): Performs critical tasks such as instance recovery and dead transaction recovery, and maintenance tasks such as temporary space reclamation, data dictionary cleanup, and undo tablespace management

ASMB (ASM Background Process): Communicates with the ASM instance, managing storage and providing statistics, runs in ASM instances when the ASMCMD cp command runs or when the database instance first starts if the server parameter file is stored in ASM. ASMB also runs with Oracle Cluster Registry on ASM.

RBAL (ASM Rebalance Master Process): In an ASM instance, it coordinates rebalance activity for disk groups. In a database instances, it manages ASM disk groups.

PMON (Process Monitor): Monitors the other background processes and performs process recovery when a server or dispatcher process terminates abnormally.

SMON (System Monitor Process): Performs critical tasks such as instance recovery and dead transaction recovery, and maintenance tasks such as temporary space reclamation, data dictionary cleanup, and undo tablespace management

Immediately after adding a new disk to or removing an existing disk from an ASM instance, you find that the performance of the database goes down initially until the time the addition or removal process is completed, and then gradually becomes normal.

Which two activities would you perform to maintain a consistent performance of the database while adding or removing disks? (Choose two.)

Define the POWER option while adding or removing the disks.
Increase the number of ARB processes by setting up a higher value for ASM_POWER_LIMIT.
Increase the number of DBWR processes by setting up a higher value for DB_WRITER_PROCESSES.
Increase the number of slave database writer processes by setting up a higher value for DBWR_IO_SLAVES.

Correct answer: AB

Explanation:

ARBn (ASM Rebalance Process): Rebalances data extents within an ASM disk group, possible processes are ARB0-ARB9 and ARBA.ALTER DISKGROUP..POWER clause, specify a value from 0 to 11, where 0 stops the rebalance operation and 11 permits Oracle ASM to execute the rebalance as fast as possible. The value you specify in the POWER clause defaults to the value of the ASM_POWER_LIMIT initialization parameter. If you omit the POWER clause, then Oracle ASM executes both automatic and specified rebalance operations at the power determined by the value of the ASM_POWER_LIMIT initialization parameter. Note:Beginning with Oracle Database 11g Release 2 (11.2.0.2), if the COMPATIBLE.ASM disk group attribute is set to 11.2.0.2 or higher, then you can specify a value from 0 to 1024 in the POWER clause.

ARBn (ASM Rebalance Process): Rebalances data extents within an ASM disk group, possible processes are ARB0-ARB9 and ARBA.

ALTER DISKGROUP..POWER clause, specify a value from 0 to 11, where 0 stops the rebalance operation and 11 permits Oracle ASM to execute the rebalance as fast as possible. The value you specify in the POWER clause defaults to the value of the ASM_POWER_LIMIT initialization parameter. If you omit the POWER clause, then Oracle ASM executes both automatic and specified rebalance operations at the power determined by the value of the ASM_POWER_LIMIT initialization parameter.

Note:

Beginning with Oracle Database 11g Release 2 (11.2.0.2), if the COMPATIBLE.ASM disk group attribute is set to 11.2.0.2 or higher, then you can specify a value from 0 to 1024 in the POWER clause.

Identify three key features of ASM. (Choose three.)

file striping
allocation unit mirroring
automatic disk rebalancing
automatic file size increment
automatic undo management

Correct answer: ABC

You have three production databases, HRDB, FINDB, and ORGDB, that use the same ASM instance. At the end of the day, while all three production database instances are running, you execute the following command on the ASM instance:

SQL> shutdown immediate;

What is the result of executing this command?

The ASM instance is shut down, but the other instances are still running.
It results in an error because other database instances are connected to it.
All the instances, including the ASM instance, are shut down in the IMMEDIATE mode.
HRDB, FINDB, and ORGDB instances are shut down in the ABORT mode and the ASM instance is shut down in the IMMEDIATE mode.

Correct answer: B

You are managing an ASM instance. You previously issued the following statements:

ALTER DISKGROUP dg1 DROP DISK disk2;

ALTER DISKGROUP dg1 DROP DISK disk3;

ALTER DISKGROUP dg1 DROP DISK disk5;

You want to cancel the disk drops that are pending for the DG1 disk group.

Which statement should you issue?

ALTER DISKGROUP dg1 UNDROP disk2, disk3, disk5;
ALTER DISKGROUP dg1 UNDROP;
ALTER DISKGROUP dg1 UNDROP DISKS;
You cannot cancel the pending disk drops.

Correct answer: C

Explanation:

Use this clause to cancel the drop of disks from the disk group. You can cancel the pending drop of all the disks in one or more disk groups (by specifying diskgroup_name) or of all the disks in all disk groups (by specifying ALL). This clause is not relevant for disks that have already been completely dropped from the disk group or for disk groups that have been completely dropped. This clause results in a long-running operation. You can see the status of the operation by querying the V$ASM_OPERATION dynamic performance view.

Use this clause to cancel the drop of disks from the disk group. You can cancel the pending drop of all the disks in one or more disk groups (by specifying diskgroup_name) or of all the disks in all disk groups (by specifying ALL).

This clause is not relevant for disks that have already been completely dropped from the disk group or for disk groups that have been completely dropped. This clause results in a long-running operation. You can see the status of the operation by querying the V$ASM_OPERATION dynamic performance view.