Oracle 10g New Features -Ch 5 Automated Storage Management

Chapter 5 - Automated Storage Management
OCP: Oracle 10g New Features for Administrators Study Guide (Exam 1Z0-040)


1. Which data dictionary view provides the recommended action, as a SQL statement, from the Segment Advisor?
A. DBA_ADVISOR_FINDINGS
B. DBA_ADVISOR_RECOMMENDATIONS
C. DBA_ADVISOR_ACTIONS
D. DBA_ADVISOR_RATIONALE

----
Ans: C.
The data dictionary view DBA_ADVISOR_ACTIONS contains the SQL statement(s) that the Segment Advisor supplies to implement its recommendation for segment maintenance. DBA_ADVISOR_FINDINGS contains the results of the analysis, but no SQL. DBA_ADVISOR_RECOMMENDATIONS presents one or more findings and the benefits for performing the recommendation. DBA_ADVISOR_RATIONALE provides a more detailed set of reasons why the recommendation should be implemented, along with the impact of not performing the recommendation.

2. Which of the following is not true about sorted hash clusters?
A. The new access path is used regardless of the type of predicate in the WHERE clause.
B. You are allowed to create indexes on sorted hash clusters.
C. The cost-based optimizer must be used to take advantage of the new access path.
D. Additional sorts are not necessary if you access the cluster by one of the lists of hash key columns.
E. More than one table can be stored in a sorted hash cluster.

----
Ans: A.
The new access path in a sorted hash cluster is used only if an equality predicate is used.

3. Consider the following scenario: The user SCOTT runs a query at 8:25 a.m. that receives an "ORA-01555: Snapshot too old" error after running for 15 minutes. An alert is sent to the DBA that the undo tablespace is incorrectly sized. At 10:15 a.m. the DBA checks the initialization parameter UNDO_RETENTION, and its value is 3600; the parameter is sized correctly. The DBA doubles the size of the undo tablespace by adding a second datafile. At 1:15 p.m. the user SCOTT runs the same query and once again receives an "ORA-01555: Snapshot too old" error. What happens next? (Choose the best answer.)

A. The DBA receives another alert indicating that the undo tablespace is still undersized.
B. The user SCOTT calls the DBA to report that the query is still failing.
C. The second datafile autoextends so that future queries will have enough undo to complete when there is concurrent DML activity.
D. Resumable Space Allocation suspends the query until the DBA adds another datafile to the undo tablespace and then the query runs to completion.

---
Ans: B.
Even if the size of the undo tablespace is adjusted after an undo space problem, only one alert is sent for each 24-hour period. Therefore, the only way that the problem will be resolved promptly is for SCOTT to call the DBA, as the DBA will not receive another alert until the next day when another query fails.


4. The background process __________checks for tablespace threshold violation or clearance every ______minutes.
A. MMON, 10
B. SMON, 10
C. TMON, 30
D. PMON, 15
E. MMON, 30

----
Ans: A.
The new background process MMON checks for threshold violations every 10 minutes. An alert is triggered when the threshold is reached or is cleared.


5. Which of the following initialization parameters influences the recommended redo logfile size provided by the Redo Logfile Size Advisor?
A. LOG_CHECKPOINT_INTERVAL
B. OPTIMAL_LOGFILE_SIZE
C. FAST_START_IO_TARGET
D. FAST_START_MTTR_TARGET
E. None of the above

---
Ans: D.
FAST_START_MTTR_TARGET specifies the desired time, in seconds, for instance recovery after a crash or an instance failure. Therefore, the Redo Logfile Size Advisor uses this value to determine the optimal logfile size. OPTIMAL_LOGFILE_SIZE is not an initialization parameter but a column in the view V$INSTANCE_RECOVERY. The initialization parameters FAST_START_IO_TARGET specifies recovery at the I/O level, and LOG_CHECKPOINT_INTERVAL specifies the frequency of checkpoints in terms of redo logfile blocks used.

6. Which of the following is not a benefit of segment shrink?
A. Full table scans will take less time.
B. Better index access because of a smaller B*Tree.
C. Space is freed up for other database objects.
D. All chained rows are fixed.
E. Space below the HWM is released and the HWM moved down.

-----
Ans: D.
While some chained rows may be fixed with segment shrink functionality, it is not guaranteed that all chained rows will be fixed since not all blocks may be read in a segment shrink operation.


7. Which of the following ASM file templates are not striped as Fine?
A. FLASHBACK
B. ARCHIVELOG
C. CONTROLFILE
D. ONLINELOG

----
Ans: B.
Files such as ARCHIVELOG files use coarse-grained striping. Fine striping stripes the files every 128KB while coarse striping stripes the files every 1MB. All file types with the exception of FLASHBACK, CONTROLFILE, and ONLINELOG are striped coarse.
C.

8. You want to migrate your database to ASM, so you've done a clean shutdown, made a closed backup of the entire database, noted the location of your control files and online redo log files, and changed your SPFILE to use OMF. The last step is running an RMAN script to do the conversion. Using the following steps, which is the correct order or the RMAN commands?
1 STARTUP NOMOUNT
2 ALTER DATABASE OPEN RESETLOGS
3 SQL "ALTER DATABASE RENAME 'logfile1 path ' TO '+dgrp4 ' " # plus all
other log files
4 SWITCH DATABASE TO COPY
5 BACKUP AS COPY DATABASE FORMAT '+dgrp4 '
6 ALTER DATABASE MOUNT
7 RESTORE CONTROLFILE FROM 'controlfile_location '

A. 2, 5, 3, 1, 7, 6, 4
B. 1, 7, 6, 5, 4, 3, 2
C. 5, 1, 2, 7, 4, 6, 3
D. 7, 3, 1, 5, 6, 2, 4

----
Ans: B.
After the RMAN script is run, and the database is up and running successfully, you may delete the old database files.

9. The EM Database Control Segment Resource Estimation feature uses all the following characteristics of the proposed table except for which one?
A. Column datatypes
B. PCTUSED
C. PCTFREE
D. Column sizes
E. Estimated number of rows

---
Ans: B.
Only PCTFREE is used in the calculation, as it is the amount of space to leave free in the block for updates to existing rows. PCTUSED is not needed unless the segment space management is not AUTO. In addition, extent sizes calculated by this feature helps assess the impact on the tablespace where this segment will be stored.
B.

10. To reference existing ASM files, you need to use a fully qualified ASM filename. Your development database has a disk group named DG2A, the database name is DEV19, and the ASM file you want to reference is a datafile for the USERS02 tablespace. Which of the following is a valid ASM filename for this ASM file?

A. dev19/+DG2A/datafile/users02.701.2
B. +DG2A/dev19/datafile/users02.701.2
C. +DG2A/dev19/users02/datafile.701.2
D. +DG2A.701.2
E. +DG2A/datafile/dev19.users.02.701.2

---
Ans: B.
A fully qualified existing ASM filename has the format +group/dbname/filetype/tag.file.incarnation. In this case, filetype is datafile, and tag is the tablespace name to which it belongs, or users02.


11. Which of the following is not a benefit of sorted hash clusters? (Choose the best answer.)
A. Rows within a given cluster key value are sorted by the sort key(s).
B. The ORDER BY clause is not required to retrieve rows in ascending or descending order of the sort key(s).
C. Cluster key values are hashed.
D. Rows selected by a cluster key value using an equality operator are returned in ascending or descending order.

---
Ans: C.
While cluster key values in a sorted hash cluster are hashed, this is also true of regular hash clusters and therefore is not a benefit unique to sorted hash clusters.

12. On the development database rac0 there are six raw devices /dev/raw/raw1 through /dev/raw/raw6. /dev/raw/raw1 and /dev/raw/raw2 are 8GB each, and the rest are 6GB each. An existing disk group +DATA1, of NORMAL REDUNDANCY, uses /dev/raw/raw1 and /dev/raw/ raw2. Which series of the following commands will drop one of the failgroups for +DATA1, create a new disk group +DATA2 using two of the remaining four raw devices, and then cancel the drop operation from +DATA1?


A

ALTER DISKGROUP DATA1 DROP DISK DATA1_0001;
CREATE DISKGROUP DATA2 NORMAL REDUNDANCY
FAILGROUP DATA1A DISK '/dev/raw/raw3'
FAILGROUP DATA1B DISK '/dev/raw/raw4';
ALTER DISKGROUP DATA1 UNDROP DISKS;

B

ALTER DISKGROUP DATA1 DROP DISK DATA1_0001;
CREATE DISKGROUP DATA2 HIGH REDUNDANCY
FAILGROUP DATA1A DISK '/dev/raw/raw3'
FAILGROUP DATA1B DISK '/dev/raw/raw4';
ALTER DISKGROUP DATA1 UNDROP DISKS;

C

ALTER DISKGROUP DATA1 DROP DISK DATA1_0001;
CREATE DISKGROUP DATA2 NORMAL REDUNDANCY
FAILGROUP DATA1A DISK '/dev/raw/raw3'
FAILGROUP DATA1B DISK '/dev/raw/raw4';
ALTER DISKGROUP DATA1 UNDROP DATA1_0001;

D

ALTER DISKGROUP DATA1 DROP DISK DATA1_0001
ADD DISKGROUP DATA2 NORMAL REDUNDANCY
FAILGROUP DATA1A DISK '/dev/raw/raw3'
FAILGROUP DATA1B DISK '/dev/raw/raw4';
ALTER DISKGROUP DATA1 UNDROP DISKS;

---
Ans: A.
Note that the UNDROP operation will cancel a drop operation in progress but cannot reverse a drop operation that has already completed. For HIGH REDUNDANCY, at least three failure groups must be specified. While you can combine a drop and add operation into one command, the command can reference only one disk group.


13. In the following scenario, the DBA wants to reclaim a lot of wasted space in the HR.EMPLOYEES table by using the segment shrink functionality. Which of the following is the correct order of the steps?
1 ALTER TABLE HR.EMPLOYEES SHRINK SPACE;
2 ALTER TABLE HR.EMPLOYEES DISABLE ROW MOVEMENT;
3 ALTER TABLE HR.EMPLOYEES ENABLE ROW MOVEMENT;
4 ALTER TABLE HR.EMPLOYEES SHRINK SPACE COMPACT;
5 ALTER TABLE HR.EMPLOYEES SHRINK SPACE CASCADE;
A. 3, 4, 1, 5, 2
B. 4, 1, 3, 2, 5
C. 5, 2, 1, 3, 4
D. 4, 1, 2, 3, 5

---.
Ans: A.
While the segment shrink operation could combine steps A and D, the impact to the users may be lessened by performing two smaller operations instead of one large one.


14. Which of the following calls to DBMS_SERVER_ALERT.SET_THRESHOLD will set the thresholds for the UNDOTBS1 tablespace to 60 percent and 90 percent? (Choose the best answer.)
A. dbms_server_alert.set_threshold(
dbms_server_alert.tablespace_pct_full,
dbms_server_alert.operator_ge, 60,
dbms_server_alert.operator_ge, 90,
1, 1, null,
dbms_server_alert.object_type_tablespace,
null);

B dbms_server_alert.set_threshold(
dbms_server_alert.tablespace_pct_full,
dbms_server_alert.operator_le, 60,
dbms_server_alert.operator_le, 90,
1, 1, null,
dbms_server_alert.object_type_datafile,
'UNDOTBS1');

C dbms_server_alert.set_threshold(
dbms_server_alert.tablespace_full,
dbms_server_alert.operator_ge, 60,
dbms_server_alert.operator_ge, 90,
1, 1, null,
dbms_server_alert.object_type_tablespace,
'UNDOTBS1');

D. dbms_server_alert.set_threshold(
dbms_server_alert.tablespace_pct_full,
dbms_server_alert.operator_ge, 60,
dbms_server_alert.operator_ge, 90,
1, 1, null,
dbms_server_alert.object_type_tablespace,
'UNDOTBS1');
---
Ans: D.
The call to DBMS_SERVER_ALERT.SET_THRESHOLD must specify the metric TABLESPACE_PCT_FULL, the two thresholds, an object type of tablespace, and the tablespace name itself. specifying NULL for the tablespace name will set the threshold for all tablespaces, not just the UNDOTBS1 tablespace.

15. Which of the following statements is not true about segment shrink operations? (Choose the best answer.)
A. Tables with ROWID-based materialized views are maintained.
B. Segment shrink is only allowed on segments whose space is automatically managed.
C. Heap-organized and index-organized tables can be shrunk.
D. ROW MOVEMENT must be enabled for heap-organized segments.
E. Chained rows may be repaired during a segment shrink operation.
F. Triggers are not fired during a segment shrink operation.

---
Ans: A.
Because the ROWIDs are changed with a segment shrink operation, tables with ROWID-based materialized views cannot be shrunk unless the materialized views are dropped and re-created after the segment shrink operation.
C


16. Which of the following is not a feature of the Segment Advisor within the EM Database Control?
A. Growth trend analysis
B. Segment resource estimation
C. Finding candidates for segment shrink
D. Finding table segments with chained rows

---
Ans: D.
The Segment Advisor is not used to find tables with chained rows, but instead is used for finding segments that are good candidates for segment shrink or may be growing too fast.

17. Choose the set of the following initialization parameters that is valid and recommended for an ASM instance.
A. INSTANCE_TYPE=RDBMS
ASM_POWER_LIMIT=2
LARGE_POOL_SIZE=8MB
DB_UNIQUE_NAME=+ASM
ASM_DISKGROUPS=DATA1,DATA2

B INSTANCE_TYPE=ASM
ASM_POWER_LIMIT=2
LARGE_POOL_SIZE=8MB
DB_UNIQUE_NAME=+ASM
ASM_DISKGROUPS=DATA1,DATA2

C INSTANCE_TYPE=ASM
ASM_POWER_LIMIT=15
LARGE_POOL_SIZE=8MB
DB_UNIQUE_NAME=+ASM
ASM_DISKGROUPS=DATA1,DATA2

D INSTANCE_TYPE=ASM
ASM_POWER_LIMIT=2
LARGE_POOL_SIZE=4MB
DB_UNIQUE_NAME=+ASM
ASM_DISKGROUPS=DATA1,DATA2

---
Ans: B.
The INSTANCE_TYPE for an ASM instance is ASM; otherwise, it is RDBMS whether it uses ASM or not. The ASM_POWER_LIMIT command controls the speed of a disk group rebalance, but its maximum value is 11. For an ASM instance, the minimum recommended value for LARGE_POOL_SIZE is 8MB.

18. Which of the following scenarios concerning ASM instance shutdown is correct?
A. When an ASM instance is shut down with NORMAL, IMMEDIATE or TRANSACTIONAL, the same shutdown command is passed to the dependent instances and the ASM instance waits for all dependent instances to shut down before it shuts down.
B. When an ASM instance shuts down with NORMAL, an alert is sent to all dependent instances, notifying the DBA to shut down the dependent instances manually before the ASM instance shuts down.
C. When an ASM instance shuts down with the TRANSACTIONAL option, all dependent instances shut down with either NORMAL, IMMEDIATE or TRANSACTIONAL, depending on the dependent database's default.
D. When an ASM instance is shut down with NORMAL, IMMEDIATE or TRANSACTIONAL, the same shutdown command is passed to the dependent instances and the ASM instance does not wait for all dependent instances to shut down before it shuts down.
E. When an ASM instance shuts down with the IMMEDIATE option, the ASM instance shuts down immediately and all dependent instances shut down with ABORT.

---
Ans: A.
When an ASM instance shuts down with NORMAL, IMMEDIATE or TRANSACTIONAL, the same shutdown option is passed to all dependent instances and the ASM instance waits for the dependent instances to shut down before shutting itself down. If an ASM instance shuts down with ABORT, it immediately shuts down, the dependent instances lose their connection to the ASM instance and as a result shut down with ABORT either before or after the ASM instance shuts down completely.


19. Which of the following conditions will trigger an additional sort on a sorted hash cluster? (Choose two.)
A. The ORDER BY clause specifies nonsort columns that are not indexed.
B. An ORDER BY clause is used in the query although the sort may still fit in memory.
C. The cost-based optimizer is in effect.
D. The ORDER BY clause is omitted, and the WHERE clause does not reference the cluster key.
E. The ORDER BY clause specifies trailing sort columns.

---
Ans: A, E.

If a query on a sorted hash cluster retrieves rows and an ORDER BY clause specifies either nonsort columns or a suffix of the sort columns, additional sorting is required, assuming that indexes are not defined on the columns in the ORDER BY clause.

20. Which of the following is not true about segment shrink operations in tablespaces with automatic segment space management?
A. Clustered tables cannot be shrunk.
B. LOB segments can be shrunk.
C. IOT mapping tables and overflow segments cannot be shrunk.
D. Tables with function-based indexes cannot be shrunk.
E. ROW MOVEMENT must be enabled for heap-based segments.

----
Ans: B.

For segments in tablespaces with automatic segment space management, LOB segments cannot be shrunk. In addition, tables with LONG columns, on-commit materialized views, and ROWID-based materialized view cannot be shrunk. In all cases, shrink operations cannot be performed on segments managed by freelists.