Ch7 Handling Block Corruption
Review Questions
-----------------
1. What activity is responsible for causing most block corruption?
A. Human errors that introduce bugs caused by hardware, software, or firmware
B. Memory errors written to disk
C. Random I/ O errors written to disk
D. A bad transaction that updates many tables
----
Ans: A.
Most block corruption is caused by human error introducing bugs with new
hardware, software, or firmware changes.
2. What is the most common way of detecting block corruption in the database?
A. Monitoring the operating system log files
B. Monitoring the application log files
C. Monitoring the ALERT.LOG database log
D. Monitoring the ALERT.LOG and associated trace files
----
Ans: D.
Monitoring the ALERT.LOG and associated trace files is the best method for
detecting block corruption in the database.
3. What is the correct command to analyze the EMPLOYEE table in the schema
owned by user TEST?
A. ANALYZE INDEX EMPLOYEE VALIDATE STRUCTURE
B. ANALYZE TABLE TEST.EMPLOYEE VALIDATE STRUCTURE
C. ANALYZE TABLE EMPLOYEE VALIDATE STRUCTURE
D. ANALYZE INDEX TEST.EMPLOYEE VALIDATE STRUCTURE
----
Ans: B.
If the ANALYZE command is being run by a DBA account, you need to prefix the
table name with the schema owner.
4. Which of the following is the most correct statement about what the ANALYZE
command does?
A. The ANALYZE command identifies the object and blocks where corruption exists.
B. The ANALYZE command identifies the objects where corruption exists.
C. The ANALYZE command identifies the table where corruption exists.
D. The ANALYZE command identifies the index and the block where corruption exists.
----
Ans: B.
The ANALYZE command determines which object has corrupted indexes or tables,
because the command returns an error if the statement does not process completely.
The ANALYZE command does not identify which block is corrupt.
5. The DBVERIFY utility reports output about corruption in which manner?
A. The DBVERIFY utility identifies the amount of corrupt objects, not the amount of blocks in a table.
B. The DBVERIFY utility identifies the amount of corrupt blocks, not the amount of objects in a datafile.
C. The DBVERIFY utility identifies the amount of corrupt pages, not the amount of blocks in a datafile.
D. The DBVERIFY utility identifies the amount of corrupt pages, not the amount of blocks in the database.
----
Ans: C.
The DBVERIFY utility uses the term pages instead of blocks. The DBVERIFY utility determines the amount of corrupt pages in a datafile.
6. Which of the following is a correct statement about the DBVERIFY utility?
A. The DBVERIFY utility can be executed only on online datafiles.
B. The DBVERIFY utility can be executed on online datafiles and offline datafiles.
C. The DBVERIFY utility can be executed only on offline datafiles.
D. The DBVERIFY utility can be executed only on online datafiles and offline tablespaces.
---
Ans: B.
The DBVERIFY utility can be used on online and offline datafiles.
7. What is the correct syntax for using the DBVERIFY utility to write to a file
and to verify the DATA tablespace with a 4k database block size?
A. dbv blocksize=4096 file=data01.dbf logfile=c:\temp\data01.log
B. dbv blocksize=4096 file=user01.dbf logfile=c:\temp\data01.log
C. dbv file=data01.dbf blocksize=4k logfile=c:\temp\ data01.log
D. dbverify blocksize=4096 file=data01.dbf logfile=c:\temp\data01.log
-----
Ans: A.
The correct syntax for the DBVERIFY utility to write the output to a log and
specify a 4k block size is as follows: dbv blocksize=4096 file=data01.dbf
logfile=c:\temp\data01.log.
8. The DB_BLOCK_CHECKING initialization parameter needs be enabled to verify what objects?
A. Objects stored in the default tablespace of the SYS user
B. Objects stored in the default tablespace of any application owner
C. Objects stored in the default tablespace of the SCOTT user
D. Objects stored in the default tablespace of the HR user
----
Ans: A. DB_BLOCK_CHECKING is enabled for the SYSTEM tablespace by default. The SYS
user default tablespace is SYSTEM.
Notes: Seems B is correct
9. What statement best describes DB_BLOCK_CHECKING?
A. DB_BLOCK_CHECKING is a utility that performs checksums on blocks every time the block is modified.
B. DB_BLOCK_CHECKING is a database parameter that causes the Oracle to perform checksums on blocks
every time the block is modified.
C. DB_BLOCK_CHECKING is a command that performs checksums on blocks every time the checkpoint process occurs.
D. DB_BLOCK_CHECKING is a database parameter that performs checksums on blocks
every time the database checkpoint occurs.
----
Ans: B.
DB_BLOCK_CHECKING is a database parameter that causes Oracle to perform
checksums on blocks every time the block is modified.
10. How should you use the DBMS_REPAIR package to build a REPAIR_TABLE for
the DATA tablespace that can help the diagnosis of corrupt rows in a table in
that tablespace?
A. declare
begin
dbms_repair.admin_table
(table_name => 'REPAIR_TABLE',
table_type => dbms_repair.repair_table,
action => dbms_repair.create_action,
tablespace => 'DATA01');
end;
/
B. declare
begin
dbms_repair.admin_tables
(table_name => 'REPAIR_TABLE',
table_type => dbms_repair.repair_table,
action => dbms_repair.create_action,
tablespace => 'DATA');
end;
/
C. declare
begin
dbms_repair.admin_table
(table_name => 'REPAIR_TABLE',
table_type => dbms_repair.repair_tables,
action => dbms_repair.create_action,
tablespace => 'DATA');
end;
/
D. declare
begin
dbms_repair.admin_tables
(table_name => 'REPAIR_TABLE',
table_type => dbms_repair.repair_table,
action => dbms_repair.create_action,
tablespace => 'DATA');
end;
/
---------
Ans: B.
The correct use of the DBMS_REPAIR package on the DATA tablespace is to use the
ADMIN_ TABLES procedure with the following parameters:
declare
begin
dbms_repair.admin_tables
(table_name => 'REPAIR_TABLE',
table_type => dbms_repair.repair_table,
action => dbms_repair.create_action,
tablespace => 'DATA');
end;
/
11. How should you use the DBMS_REPAIR package to verify REPAIR BLOCK
COUNT and load REPAIR_TABLE for the EMPLOYEE table in the SCOTT schema
with information of the object and block that is corrupt in the tablespace?
A. set serveroutput on size 100000;
declare
rpr_count int;
begin
rpr_count := 0;
dbms_repair.check_objects(
schema_name => 'SCOTT',
object_name => 'EMPLOYEE',
repair_table_name => 'REPAIR_TABLE',
corrupt_count => rpr_count);
dbms_output.put_line('repair block count: '
||to_char(rpr_count));
end;
B. set serverout on size 100000;
declare
rpr_count int;
begin
rpr_count := 0;
dbms_repair.check_objects(
schema_name => 'SCOTT',
object_name => 'EMPLOYEE',
repair_table_name => 'REPAIR_TABLESPACE',
corrupt_count => rpr_count);
dbms_output.put_line('repair block count: '
||to_char(rpr_count));
end;
C. set serveroutput on size 100000;
declare
rpr_count int;
begin
rpr_count := 0;
dbms_repair.check_object(
schema_name => 'SCOTT',
object_name => 'EMPLOYEE',
repair_table_name => 'REPAIR_TABLE',
corrupt_count => rpr_count);
dbms_output.put_line('repair block count: '
||to_char(rpr_count));
end;
D. set serverout on size 100000;
declare
rpr_count int;
begin
rpr_count := 0;
dbms_repair.check_object(
schema_name => 'SCOTT',
object_name => 'EMPLOYEE',
repair_table_name => 'REPAIR_TABLE',
corrupt_count => rpr_count);
dbms_output.put_line('repair block count: '
||to_char(rpr_count));
end;
---
Ans: C.
The DBMS_REPAIR package must be used with the CHECK_OBJECT procedure. If
you want to verify REPAIR BLOCK COUNT, you must have SET SERVEROUTPUT ON
SIZE. The following is the correct PL/ SQL syntax for the DBMS_REPAIR package:
Notes: C and D seem to me same
set serveroutput on size 100000;
declare
rpr_count int;
begin
rpr_count := 0;
dbms_repair.check_object(
schema_name => 'SCOTT',
object_name => 'EMPLOYEE',
repair_table_name => 'REPAIR_TABLE',
corrupt_count => rpr_count);
dbms_output.put_line('repair block count: '
||to_char(rpr_count));
end;
12. What could be impacted by the DBMS_REPAIR package being used on a table?
A. A table related by a foreign key and primary key to the table repaired
by the DBMS_REPAIR package could become unusable.
B. A trigger on the table repaired by the DBMS_REPAIR package could
introduce logical corruption.
C. An index related to the table repaired by the DBMS_REPAIR package
could become out of sync with the repaired table.
D. All of the above.
---
Ans: D.
Referential integrity constraints on related tables can be broken, indexes can
become out of sync with the table data, and triggers on a table can cause logical
corruption if they are not well understood when using the DBMS_REPAIR package on a
table.
13. How should you use the DBMS_REPAIR package to identify problems with a
primary key index on a corrupt table in the SCOTT schema with the
EMPLOYEE_PK index? (Choose all that apply.)
A. declare
orph_count int;
begin
orph_count:= 0;
dbms_repair.dump_child_keys (
schema_name => 'SCOTT',
object_name => 'EMPLOYEE_PK',
object_type => dbms_repair.index_object,
repair_table_name => 'REPAIR_TABLE',
orphan_table_name => 'ORPHAN_KEY_TABLE',
key_count => orph_count);
dbms_output.put_line('orphan-index entries: ' ||
to_char(orph_count));
end;
/
B. declare
orph_count int;
begin
orph_count:= 0;
dbms_repair.dump_orphan_key (
schema_name => 'SCOTT',
object_name => 'EMPLOYEE_PK',
object_type => dbms_repair.index_object,
repair_table_name => 'REPAIR_TABLE',
orphan_table_name => 'ORPHAN_KEY_TABLE',
key_count => orph_count);
dbms_output.put_line('orphan-index entries: ' ||
to_char(orph_count));
end;
/
C. declare
orph_count int;
begin
orph_count:= 0;
dbms_repair.dump_orphan_keys (
schema_name => 'SCOTT',
object_name => 'EMPLOYEE_PK',
object_type => dbms_repair.index_object,
repair_table_name => 'REPAIR_TABLE',
orphan_table_name => 'ORPHAN_KEY_TABLE',
key_count => orph_count);
dbms_output.put_line('orphan-index entries: ' ||
to_char(orph_count));
end;
/
D. declare
orph_count int;
begin
orph_count:= 0;
dbms_repair.dump_orphan_key (
schema_name => 'SCOTT',
object_name => 'EMPLOYEE_PK',
object_type => dbms_repair.index_object,
repair_table_name => 'REPAIR_TABLE',
orphan_table_name => 'ORPHAN_KEY_VIEW',
key_count => orph_count);
dbms_output.put_line('orphan-index entries: ' ||
to_char(orph_count));
end;
/
----
Ans: C.
The DBMS_REPAIR package can be used to identify problems with a primary index
by identifying orphaned keys in the index as they relate to the table being repaired for
corruption. The correct usage of the DBMS_REPAIR.DUMP_ORPHANED_KEYS package
procedure is as follows:
declare
orph_count int;
begin
orph_count:= 0;
dbms_repair.dump_orphan_keys (
schema_name => 'SCOTT',
object_name => 'EMPLOYEE_PK',
object_type => dbms_repair.index_object,
repair_table_name => 'REPAIR_TABLE',
orphan_table_name => 'ORPHAN_KEY_TABLE',
key_count => orph_count);
dbms_output.put_line('orphan-index entries: ' ||
to_char(orph_count));
end;
/
14. What table can you query to identify the block that is corrupt?
A. DBA_CORRUPT_BLOCKS
B. REPAIR_TABLE
C. DBA_REPAIR_TABLE
D. CORRUPT_BLOCKS_TABLE
-----
Ans: B.
REPAIR_TABLE will show OBJECT_NAME, BLOCK_ID, and if the block is MARKED_CORRUPT.
15. When determining how to resolve block corruption, what should you keep in mind?
A. The tables where the corruption is located
B. The block or blocks in the corrupt table
C. The extent of the corruption in the table or tables
D. The tables and indexes where the corruption is located
----
Ans: C.
When determining how to resolve block corruption, you should determine the
extent of the corruption first. Is the corruption limited to a few blocks or is it
widespread?
16. How should you use the DBMS_REPAIR package to rebuild freelists on a corrupt
table?
A. declare
begin
dbms_repair.rebuild_freelist (
schema_name => 'SCOTT',
object_name => 'EMPLOYEE',
object_type => dbms_repair.table_object);
end;
/
B. declare
begin
dbms_repair.rebuild_freelists (
schema_name => 'SCOTT',
object_name => 'EMPLOYEE',
object_type => dbms_repair.object_table);
end;
/
C. declare
begin
dbms_repair.repair_freelists (
schema_name => 'SCOTT',
object_name => 'EMPLOYEE',
object_type => dbms_repair.table_object);
end;
/
D. declare
begin
dbms_repair.rebuild_freelists (
schema_name => 'SCOTT',
object_name => 'EMPLOYEE',
object_type => dbms_repair.table_object);
end;
/
------------
Ans: D.
The DBMS_REPAIR package can be used to rebuild freelists on a corrupt table with
the following syntax:
declare
begin
dbms_repair.rebuild_freelists (
schema_name => 'SCOTT',
object_name => 'EMPLOYEE',
object_type => dbms_repair.table_object);
end;
/
17. Which of the following statements best describes using the DBMS_REPAIR
package to resolve block corruption?
A. Resolving block corruption is a complex process, and you should be careful.
B. Resolving block corruption is a complex process, and you most often use
the DBMS_REPAIR package even if the corruption is widespread.
C. Resolving block corruption is a complex process, and you should contact Oracle Support if possible.
D. Resolving block corruption is a complex process, and use of the DBMS_REPAIR package
cannot introduce other problems in the database.
---
Ans: C.
Resolving block corruption can result in the loss of data in the blocks that are
corrupt. You should contact Oracle Support, if possible.
18. Before attempting to resolve block corruption with the DBMS_REPAIR package,
what should you consider?
A. Examine other methods of resolving the corrupt objects by rebuilding the object if the data is available.
B. Attempt to exclude the corruption by excluding the row from select statements.
C. If possible, perform an incomplete recovery.
D. All of the above.
---
Ans: D.
Before using the DBMS_REPAIR package, consider other alternatives to resolve the
block corruption. Make sure you understand how else to resolve block corruption, as
a backup plan if necessary.
19. Which of the following is required to perform a block media recovery with RMAN? (Choose all that apply.)
A. The datafile number and block number of the corrupted block or blocks in the UDUMP trace file
B. The tablespace number and block number of the corrupted block or blocks
C. The datafile number and block number of the corrupted block or blocks
D. The ALERT.LOG information from the ORA-01578 error
-----
Ans: C, D.
The ORA-01578 error in the ALERT.LOG specifies the datafile number and the
block number.
20. Which of the following statements are true regarding BMR? (Choose all that
apply.)
A. BMR must be performed with RMAN.
B. BMR may be performed with RMAN and SQL.
C. Incremental backups can be used to perform BMR.
D. Redo logs are not required to perform BMR if the redo log records don't affect the corrupt block.
---
Ans: A, D.
BMR must be performed with RMAN only. Incremental backups cannot be
used to perform BMR, because incremental backups consist of changed blocks only.
All redo logs are not required to perform BMR if the damaged block does not require
those redo logs
