Wednesday, August 29, 2007

SQL Tuning - Evaluation of a RBO View in Oracle 10g

PURPOSE
=======
In the production database (8.1.7.4), there is a view called CILI_VW, which is created with a hint /*+ RULE */. I call it RBO view, the purpose of this experiment is to compare the effects between with and without this hints


RESULTS
=======


-----------------------------------------------------
Metric View Simple sql Complex query
-----------------------------------------------------
Elaps Time CBO 00:00:25.49 00:00:00.08
RBO 00:01:30.49 00:00:50.77
-----------------------------------------------------
Consistent CBO 100084 50
Get RBO 6555101 4456996
----------------------------------------------------


 


CONCLUSIONS
===========

1. RBO view performs far better than CBO view in 10g
2. Sql based on RBO maybe work well in 8i, however, when used in 10g, care must be taken
3. The complex query executed in less time than the simple query in this experiment indicates that for a query consisting of views, when it gets executed, Oracle does not generate these views first; instead, these views are replaced by sql statments. Oracle optimizes the expanded query as a whole


METHODS
=======

1. Created a new view called CILI_VW_CBO, which has same definition as the CILI_VW except without /*+ RULE */. This two views are referred to as RBO view and CBO view, respectively.


(a) RBO View
CILI_VW
SELECT /*+ RULE */
li.INVOICE_LINE_ITEM_ID,
li.INVOICE_LINE_ITEM_TYPE,
li.ACCOUNT_ID,
li.ITEM_ID,
li.FINANCE_TERM_ID,
li.INVOICE_ID,
g.GL_ACCOU
...
(b) CBO View

CILI_VW_CBO
SELECT
li.INVOICE_LINE_ITEM_ID,
li.INVOICE_LINE_ITEM_TYPE,
li.ACCOUNT_ID,
li.ITEM_ID,
li.FINANCE_TERM_ID,
li.INVOICE_ID,
g.GL_ACCOUNT_ID,
...


2. Comparing a simple select statement using AUTOTRACE

(a) For CBO view

select count(*) from cili_vw_cbo

Elapsed: 00:00:25.49



Execution Plan
----------------------------------------------------------
Plan hash value: 1691688664

-----------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes TempSpc Cost (%CPU) Time
-----------------------------------------------------------------------------------------------
0 SELECT STATEMENT 1 31 36417 (5) 00:07:18
1 SORT AGGREGATE 1 31
* 2 FILTER
* 3 HASH JOIN RIGHT OUTER 1571K 46M 52M 36417 (5) 00:07:18
4 TABLE ACCESS FULL CF_GL_ACCOUNT 3035K 17M 7805 (6) 00:01:34
* 5 FILTER
* 6 HASH JOIN OUTER 1514K 36M 43M 23200 (5) 00:04:39
* 7 FILTER
* 8 HASH JOIN OUTER 1459K 26M 12M 10481 (4) 00:02:06
9 INDEX FAST FULL SCAN XPK_CILI 746K 3644K 376 (6) 00:00:05
* 10 TABLE ACCESS FULL CF_GL_ACCOUNT 1459K 19M 7617 (4) 00:01:32
11 TABLE ACCESS FULL CF_GL_ACCOUNT 3035K 17M 7805 (6) 00:01:34
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter(NVL("CR"."GL_ACCOUNT_TYPE_CODE",'CHR')='CHR')
3 - access("G"."GL_ACCOUNT_ID"="CR"."PARENT_GL_ACCOUNT_ID"(+))
5 - filter(NVL("CO"."GL_ACCOUNT_TYPE_CODE",'CHO')='CHO')
6 - access("G"."GL_ACCOUNT_ID"="CO"."PARENT_GL_ACCOUNT_ID"(+))
7 - filter(NVL("G"."GL_ACCOUNT_TYPE_CODE",'NRV')='NRV' AND NVL("G"."BEGINNING_BALANCE_AMT",1)>0)
8 - access("LI"."INVOICE_LINE_ITEM_ID"="G"."INVOICE_LINE_ITEM_ID"(+))
10 - filter("G"."INVOICE_LINE_ITEM_ID"(+) IS NOT NULL)


Statistics
----------------------------------------------------------
28 recursive calls
0 db block gets
100084 consistent gets
3007 physical reads
0 redo size
517 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed




(b) For RBO view

select count(*) from cili_vw

Elapsed: 00:01:30.49


Execution Plan
----------------------------------------------------------
Plan hash value: 3993977882

-------------------------------------------------------------------
Id Operation Name
-------------------------------------------------------------------
0 SELECT STATEMENT
1 SORT AGGREGATE
* 2 FILTER
3 NESTED LOOPS OUTER
* 4 FILTER
5 NESTED LOOPS OUTER
* 6 FILTER
7 NESTED LOOPS OUTER
8 TABLE ACCESS FULL CILI
9 TABLE ACCESS BY INDEX ROWID CF_GL_ACCOUNT
* 10 INDEX RANGE SCAN XIF504_CF_GL_ACCOUNT
11 TABLE ACCESS BY INDEX ROWID CF_GL_ACCOUNT
* 12 INDEX RANGE SCAN XIF967_CF_GL_ACCOUNT
13 TABLE ACCESS BY INDEX ROWID CF_GL_ACCOUNT
* 14 INDEX RANGE SCAN XIF967_CF_GL_ACCOUNT
-------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter(NVL("CO"."GL_ACCOUNT_TYPE_CODE",'CHO')='CHO')
4 - filter(NVL("CR"."GL_ACCOUNT_TYPE_CODE",'CHR')='CHR')
6 - filter(NVL("G"."BEGINNING_BALANCE_AMT",1)>0 AND
NVL("G"."GL_ACCOUNT_TYPE_CODE",'NRV')='NRV')
10 - access("LI"."INVOICE_LINE_ITEM_ID"="G"."INVOICE_LINE_ITEM_ID"(+))
12 - access("G"."GL_ACCOUNT_ID"="CR"."PARENT_GL_ACCOUNT_ID"(+))
14 - access("G"."GL_ACCOUNT_ID"="CO"."PARENT_GL_ACCOUNT_ID"(+))

Note
-----
- rule based optimizer used (consider using cbo)


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6555101 consistent gets
0 physical reads
0 redo size
517 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

  


3. A Complex query

SELECT DISTINCT
it.item_id "itemId",
it.model_number "itemModelNumber",
it.serial_number "itemSerialNumber",
decode(it.item_status_code,null,'',
CodeTable.getDescription(it.item_status_code,'Item Status')) "itemStatus",
it.description "itemDescription",
inv.invoice_id "invoiceId",
inv.invoice_number "invoiceNumber",
ac.account_id "invFinAccountId",
ac.name "invFinAccountName",
lp.lender_product_id "invFinLoanId",
lp.name "invFinLoanName",
bs.entity_id "businessId",
bs.name "businessName",
it.vin_number "chassisNumber",
it.stock_number "stockNumber",
ilit.invoice_line_item_id "invLineItemId"
FROM
cf_item it,
cili_vw_cbo ilit, -- or: cili_vw
cf_invoice inv,
cf_account ac,
cf_lender_product_vw lp,
cf_business_vw bs
WHERE upper(lp.lender_product_type_code) = 'DLC'
AND upper(inv.invoice_status_code) = 'PFD'
AND UPPER(ilit.invoice_line_item_status_code)<>'REM'
AND UPPER(it.serial_number) = UPPER('W08124')
AND it.item_id = ilit.item_id
AND ilit.invoice_id = inv.invoice_id
AND inv.account_id = ac.account_id
AND ac.lender_product_id = lp.lender_product_id
AND lp.entity_id = bs.entity_id
ORDER BY
bs.name ASC
/



SQL> @sql_cbo.sql

Elapsed: 00:00:00.08

....


Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
50 consistent gets
10 physical reads
0 redo size
1738 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed


SQL> @sql_rbo.sql

Elapsed: 00:00:50.77

Execution Plan

....

Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
4456996 consistent gets
0 physical reads
0 redo size
1738 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed

Tempfile create, add and drop

(1) Make two files - this way ensures that the actual space is allocated in OS.


mkfile 6G /ora01/oraredo/TESTDB/temp01_02.dbf

mkfile 6G /ora02/oraredo/TESTDB/temp01_03.dbf


(2) Add the files to the temp tablespace temp01


alter tablespace temp01 add tempfile '/ora01/oraredo/TESTDB/temp01_02.dbf' reuse;


alter tablespace temp02 add tempfile '/ora02/oraredo/TESTDB/temp01_03.dbf' reuse;


(3) Drop the tempfile temp01_01.dbf


alter database tempfile '/ora03/oradata/TESTDB/temp01_01.dbf' drop;

Monday, August 27, 2007

Modify a colume of a replicated table from NOT NULL to NULL - is quiesce the replication group necessary?

This morning I was pinged by the manager to answer this question. Because a production change has been made last weekend and now we want to change a column from NOT NULL to NULL. I, as well as other two more experienced DBAs all believe we need to quiesce the group.

Later, I did the following test. It seems we can just apply the alter table statement directly at each site without shutdown the database.

(1) Replicated table t1 has a column B that is not null

repadmin@TSTQA> desc myschema.t1;

Name Null? Type
----------------------------------------------------- -------- ----------
A NOT NULL NUMBER
B NOT NULL NUMBER
C NUMBER


repadmin@TSTQB> desc myschema.t1

Name Null? Type
----------------------------------------------------- -------- -----------
A NOT NULL NUMBER
B NOT NULL NUMBER
C NUMBER


(2) Test replication

repadmin@TSTQA> insert into myschema.t1 values(10,11,12);
1 row created.

repadmin@TSTQA> commit;
Commit complete.

repadmin@TSTQA> select * from myschema.t1;
A B C
---------- ---------- ----------
1 2 3
2 4 6
4 5 6
7 8 9
8 9
9 11 12
10 11 12

7 rows selected.

repadmin@TSTQA> select * from myschema.t1@tstqb;
A B C
---------- ---------- ----------
1 2 3
2 4 6
4 5 6
7 8 9
8 9
9 11 12
10 11 12

7 rows selected.


(3) Change from not null to null

repadmin@TSTQB> alter table myschema.t1 modify (b null);

Table altered.

repadmin@TSTQB> desc myschema.t1

Name Null? Type
----------------------------------------------------- -------- ----------
A NOT NULL NUMBER
B NUMBER
C NUMBER



repadmin@TSTQA> alter table myschema.t1 modify (b null);

Table altered.

repadmin@TSTQA> desc myschema.t1

Name Null? Type
----------------------------------------------------- -------- ----------
A NOT NULL NUMBER
B NUMBER
C NUMBER


(4) Test replication with null values for B

repadmin@TSTQB> insert into myschema.t1 values(12, null, null);

1 row created.
repadmin@TSTQB> commit;
Commit complete.

repadmin@TSTQB> select * from myschema.t1;

A B C
---------- ---------- ----------
1 2 3
2 4 6
4 5 6
7 8 9
8 9
9 11 12
10 11 12
12

8 rows selected.

repadmin@TSTQB> select * from myschema.t1@tstqa;

A B C
---------- ---------- ----------
1 2 3
2 4 6
4 5 6
7 8 9
8 9
9 11 12
10 11 12
12

8 rows selected.

Friday, August 17, 2007

OCP 10g Admin II - Ch7 Handling Block Corruption

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

 

Wednesday, August 15, 2007

Performing a Flashback Table with SCN

1. Enable ROW MOVEMENT on table ACCOUNTS:

scott@TOY10G> alter table accounts enable row movement;
Table altered.

2. Retrieve the current SCN before you modify the table:

scott@TOY10G> select current_scn from v$database;
CURRENT_SCN
-----------
4280975

3. Update a value in the table so you can verify the change was eliminated after you
performed the Flashback Table operation to the SCN prior to the update:

scott@TOY10G> update accounts set balance=50000 where account_no = 10;
1 row updated.

scott@TOY10G> commit;
commit complete.

scott@TOY10G> select * from accounts where account_no=10;

ACCOUNT_NO BALANCE ACCOUNT_NAME
---------- ---------- --------------------
10 50000 6XNA6OSAT4OOR8I8


4. Perform the FLASHBACK TABLE command to recover the table to an SCN

scott@TOY10G> flashback table accounts to scn 4280975;
Flashback complete.

5. Query the table to verify the change was eliminated due to the Flashback Table to an

SCN prior to the existence of the change:

scott@TOY10G> select * from accounts where account_no=10;

ACCOUNT_NO BALANCE ACCOUNT_NAME
---------- ---------- --------------------
10 21627.9199 6XNA6OSAT4OOR8I8

Tuesday, August 14, 2007

Using Flashback Versions Query

Using Flashback Versions Query

===============================

 

(1) Execute update statments to generate changes

 

scott@TOY10G> update accounts set balance=2000 where account_no=1;

 

1 row updated.

 

scott@TOY10G> commit;

 

Commit complete.

 

scott@TOY10G> update accounts set balance=4000 where account_no=1;

 

1 row updated.

 

scott@TOY10G> commit;

 

Commit complete.

 

scott@TOY10G> update accounts set balance=9000 where account_no=1;

 

1 row updated.

 

scott@TOY10G> commit;

 

Commit complete.

 

scott@TOY10G>

 

 

(2) Query using versions between

 

scott@TOY10G> select balance from accounts

  2  versions between

  3  scn minvalue and maxvalue

  4  where account_no=1;

 

   BALANCE

----------

      6000

      4000

      2000

77195.4526

 

 

(3) Using timestamp 

 

select balance from accounts

versions between timestamp

to_timestamp('14-AUG-2007 19:15:01','DD-MON-YYYY HH24:MI:SS') and

to_timestamp('14-AUG-2007 21:29:01','DD-MON-YYYY HH24:MI:SS')

where account_no=1;

 

 

(4) pseudocolumns

 

scott@TOY10G> select to_char(versions_starttime,'DD-MON-YYYY HH24:MI') "START DATE",

to_char (versions_endtime, 'DD-MON-YYYY HH24:MI') "END DATE",

versions_xid,

versions_operation,

balance

from accounts

versions between scn

minvalue and maxvalue

where account_no=1;  2    3    4    5    6    7    8    9 

 

START DATE        END DATE          VERSIONS_XID     V    BALANCE

----------------- ----------------- ---------------- - ----------

14-AUG-2007 21:27                   000500110000039F U       9000

14-AUG-2007 21:27 14-AUG-2007 21:27 0007001B0000116F U       4000

14-AUG-2007 21:27 14-AUG-2007 21:27 0001002100000396 U       2000

14-AUG-2007 20:56 14-AUG-2007 21:27 0007001E0000116C U       6000

14-AUG-2007 20:56 14-AUG-2007 20:56 0002001C00000390 U       4000

14-AUG-2007 20:56 14-AUG-2007 20:56 0007000E0000116C U       2000

                  14-AUG-2007 20:56                    77195.4526

 

Note:

 

There are several new pseudocolumns that help you work with the Flashback Versions Query:

_ VERSIONS_STARTTIME

_ VERSIONS_STARTSCN

_ VERSIONS_ENDTIME

_ VERSIONS_ENDSCN

_ VERSIONS_XID

_ VERSIONS_OPERATION

 

Recycle Bin and Flashback Drop

This test shows the commands to flashback drop a table

Env: Oracle 10gR2

(1) Table structure

scott@TOY10G> desc t1

Name Null? Type

----------------------------------------------------- -------- ------------------------------------

OWNER NOT NULL VARCHAR2(30)

OBJECT_NAME NOT NULL VARCHAR2(30)

SUBOBJECT_NAME VARCHAR2(30)

OBJECT_ID NOT NULL NUMBER

DATA_OBJECT_ID NUMBER

OBJECT_TYPE VARCHAR2(19)

CREATED NOT NULL DATE

LAST_DDL_TIME NOT NULL DATE

TIMESTAMP VARCHAR2(19)

STATUS VARCHAR2(7)

TEMPORARY VARCHAR2(1)

GENERATED VARCHAR2(1)

SECONDARY VARCHAR2(1)

(2) Drop the table

scott@TOY10G> drop table t1;

Table dropped.

(3) Object in the recyclebin

scott@TOY10G> show recyclebin;

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

---------------- ------------------------------ ------------ -------------------

T1 BIN$N69b2mR+VDHgRAADupTRjg==$0 TABLE 2007-08-14:15:05:11

(4) We can query recycle bin name

scott@TOY10G> ;

1 select object_name from "BIN$N69b2mR+VDHgRAADupTRjg==$0"

2* where rownum < 10

scott@TOY10G> /

OBJECT_NAME

------------------------------

CON$

I_COL2

I_USER#

C_TS#

I_OBJ#

I_CON2

IND$

BOOTSTRAP$

UET$

9 rows selected.

(5) Flash back drop and rename the table

scott@TOY10G> flashback table "BIN$N69b2mR+VDHgRAADupTRjg==$0" to before drop rename to t3;

Flashback complete.

(6) New table contents

scott@TOY10G> select object_name from t3

where rownum < 10 2

3 /

OBJECT_NAME

------------------------------

CON$

I_COL2

I_USER#

C_TS#

I_OBJ#

I_CON2

IND$

BOOTSTRAP$

UET$

9 rows selected.

(7) Recyclebin is clean now

scott@TOY10G> show recyclebin;

scott@TOY10G>