| how to find out if a table has dirty blocks in buffers [message #136567] |
Thu, 08 September 2005 15:22  |
nile
Messages: 13
Registered: September 2005
|
Junior Member |
|
|
I was wondering if there is a way to find out if a table has modified blocks in the buffer cache that have not been flushed to disk. I was able to find out if there are modified buffers but could not come up with a way to be sure what table they were from. This is what I was trying:
SQL> create table try (name char(10)) tablespace users;
Table created.
SQL> alter system checkpoint;
System altered.
SQL> select ACTUAL_REDO_BLKS from v$instance_recovery;
ACTUAL_REDO_BLKS
----------------
0
SQL> insert into try values ('bbb');
1 row created.
SQL> commit;
Commit complete.
SQL> select ACTUAL_REDO_BLKS from v$instance_recovery;
ACTUAL_REDO_BLKS
----------------
1
What query can I use and what system tables to confirm that this modified block is from table 'try'?
TIA.
|
|
|
|
| Re: how to find out if a table has dirty blocks in buffers [message #136642 is a reply to message #136567] |
Fri, 09 September 2005 04:35  |
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
|
Senior Member |
|
|
Hi the following article may help:
Oracle provides a number of methods to detect and repair corruption within datafiles:
* DBVerify
* ANALYZE .. VALIDATE STRUCTURE
* DB_BLOCK_CHECKING.
* DBMS_REPAIR.
* Other Repair Methods.
DBVerify
DBVerify is an external utility that allows validation of offline datafiles. In addition to offline datafiles it can be used to check the validity of backup datafiles:
C:>dbv file=C:\Oracle\oradata\TSH1\system01.dbf feedback=100 blocksize=4096
ANALYZE .. VALIDATE STRUCTURE
The ANALYZE command can be used to verify each data block in the analyzed object. If any corruption is detected rows are added to the INVALID_ROWS table:
-- Create the INVALID_ROWS table
SQL> @C:\Oracle\901\rdbms\admin\UTLVALID.SQL
-- Validate the table structure.
SQL> ANALYZE TABLE scott.emp VALIDATE STRUCTURE;
-- Validate the table structure along with all it's indexes.
SQL> ANALYZE TABLE scott.emp VALIDATE STRUCTURE CASCADE;
-- Validate the index structure.
SQL> ANALYZE INDEX scott.pk_emp VALIDATE STRUCTURE;
DB_BLOCK_CHECKING
When the DB_BLOCK_CHECKING parameter is set to TRUE Oracle performs a walk through of the data in the block to check it is self-consistent. Unfortunately block checking can add between 1 and 10% overhead to the server. Oracle recommend setting this parameter to TRUE if the overhead is acceptable.
DBMS_REPAIR
Unlike the previous methods dicussed, the DBMS_REPAIR package allows you to detect and repair corruption. The process requires two administration tables to hold a list of corrupt blocks and index keys pointing to those blocks. These are created as follows:
BEGIN
DBMS_REPAIR.admin_tables (
table_name => 'REPAIR_TABLE',
table_type => DBMS_REPAIR.repair_table,
action => DBMS_REPAIR.create_action,
tablespace => 'USERS');
DBMS_REPAIR.admin_tables (
table_name => 'ORPHAN_KEY_TABLE',
table_type => DBMS_REPAIR.orphan_table,
action => DBMS_REPAIR.create_action,
tablespace => 'USERS');
END;
/
With the administration tables built we are able to check the table of interest using the CHECK_OBJECT procedure:
SET SERVEROUTPUT ON
DECLARE
v_num_corrupt INT;
BEGIN
v_num_corrupt := 0;
DBMS_REPAIR.check_object (
schema_name => 'SCOTT',
object_name => 'DEPT',
repair_table_name => 'REPAIR_TABLE',
corrupt_count => v_num_corrupt);
DBMS_OUTPUT.put_line('number corrupt: ' || TO_CHAR (v_num_corrupt));
END;
/
Assuming the number of corrupt blocks is greater than 0 the CORRUPTION_DESCRIPTION and the REPAIR_DESCRIPTION columns of the REPAIR_TABLE can be used to get more information about the corruption.
At this point the currupt blocks have been detected, but are not marked as corrupt. The FIX_CORRUPT_BLOCKS procedure can be used to mark the blocks as corrupt, allowing them to be skipped by DML once the table is in the correct mode:
SET SERVEROUTPUT ON
DECLARE
v_num_fix INT;
BEGIN
v_num_fix := 0;
DBMS_REPAIR.fix_corrupt_blocks (
schema_name => 'SCOTT',
object_name => 'DEPT',
object_type => Dbms_Repair.table_object,
repair_table_name => 'REPAIR_TABLE',
fix_count => v_num_fix);
DBMS_OUTPUT.put_line('num fix: ' || TO_CHAR(v_num_fix));
END;
/
Once the corrupt table blocks have been located and marked all indexes must be checked to see if any of their key entries point to a corrupt block. This is done using the DUMP_ORPHAN_KEYS procedure:
SET SERVEROUTPUT ON
DECLARE
v_num_orphans INT;
BEGIN
v_num_orphans := 0;
DBMS_REPAIR.dump_orphan_keys (
schema_name => 'SCOTT',
object_name => 'PK_DEPT',
object_type => DBMS_REPAIR.index_object,
repair_table_name => 'REPAIR_TABLE',
orphan_table_name => 'ORPHAN_KEY_TABLE',
key_count => v_num_orphans);
DBMS_OUTPUT.put_line('orphan key count: ' || TO_CHAR(v_num_orphans));
END;
/
If the orphan key count is greater than 0 the index should be rebuilt.
The process of marking the table block as corrupt automatically removes it from the freelists. This can prevent freelist access to all blocks following the corrupt block. To correct this the freelists must be rebuilt using the REBUILD_FREELISTS procedure:
BEGIN
DBMS_REPAIR.rebuild_freelists (
schema_name => 'SCOTT',
object_name => 'DEPT',
object_type => DBMS_REPAIR.table_object);
END;
/
The final step in the process is to make sure all DML statements ignore the data blocks marked as corrupt. This is done using the SKIP_CORRUPT_BLOCKS procedure:
BEGIN
DBMS_REPAIR.skip_corrupt_blocks (
schema_name => 'SCOTT',
object_name => 'DEPT',
object_type => DBMS_REPAIR.table_object,
flags => DBMS_REPAIR.skip_flag);
END;
/
The SKIP_CORRUPT column in the DBA_TABLES view indicates if this action has been successful.
At this point the table can be used again but you will have to take steps to correct any data loss associated with the missing blocks.
Other Repair Methods
Other methods to repair corruption include:
* Full database recovery.
* Individual datafile recovery.
* Block media recovery (BMR), available in Oracle9i when using RMAN.
* Recreate the table using the CREATE TABLE .. AS SELECT command, taking care to avoid the corrupt blocks by retricting the where clause of the query.
* Drop the table and restore it from a previous export. This may require some manual effort to replace missing data.
Regds
Girish
|
|
|
|
|
|
|
|
|
|
| Re: how to find out if a table has dirty blocks in buffers [message #136671 is a reply to message #136567] |
Fri, 09 September 2005 08:18 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
My first thought was to look in the v$sysstat or v$sesstat using the stats described in v$statname. But these I think are not at the individual object level but more counts of activity.
I don't know anything about v$bh, but at the top of the reference page for it it says
"This is a Real Application Clusters view. This view gives the status and number of pings for every buffer in the SGA"
Yet mine is populated with data, even though I'm not running RAC. Is this a contradiction? What am I missing?
|
|
|
|
|
|
|
|
|
|
| Re: how to find out if a table has dirty blocks in buffers [message #136680 is a reply to message #136567] |
Fri, 09 September 2005 08:49 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Thanks Nile for the question and Mahesh for the information. I just ran a script and saw it in action. I also saw when I compared a regular insert of 10,000 rows to a direct path insert the number of dirty blocks in first case showed at 192 while in the second case at only 6, which makes sense because the direct path would bypass buffer cache.
|
|
|
|
|
|
|
|
| Re: how to find out if a table has dirty blocks in buffers [message #136710 is a reply to message #136567] |
Fri, 09 September 2005 11:16 |
nile
Messages: 13
Registered: September 2005
|
Junior Member |
|
|
Thanks a bunch for the information. I really appreciate it. I was trying out the queries from v$bh and things work correctly with data_object_id indicating if there are dirty blocks present. I am still confused about one thing though. Here is a test I tried:
SQL> create table try (name char(10)) tablespace users;
Table created.
SQL> select object_id, data_object_id from user_objects where object_name='TRY';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
1192310 1192310
SQL> insert into TRY values ('aaa');
1 row created.
SQL> commit;
Commit complete.
SQL> alter system checkpoint;
System altered.
SQL> select count(*) from v$bh where DIRTY='Y' and objd=(select object_id from user_objects where object_name='TRY');
COUNT(*)
----------
0
SQL> select count(*) from v$bh where DIRTY='Y' and objd=(select data_object_id from user_objects where object_name='TRY');
COUNT(*)
----------
0
----> verified: on disk 1 row present - 'aaa'
SQL> insert into TRY values ('bbb');
1 row created.
SQL> commit;
Commit complete.
SQL> select object_id, data_object_id from user_objects where object_name='TRY';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
1192310 1192310
SQL> select count(*) from v$bh where DIRTY='Y' and objd=(select object_id from user_objects where object_name='TRY');
COUNT(*)
----------
1
SQL> select count(*) from v$bh where DIRTY='Y' and objd=(select data_object_id from user_objects where object_name='TRY');
COUNT(*)
----------
1
----> indicates dirty blocks present i.e data on disk is not accurate
----> verification: on disk 1 row present - 'aaa'
SQL> alter system checkpoint;
System altered.
SQL> select count(*) from v$bh where DIRTY='Y' and objd=(select object_id from user_objects where object_name='TRY');
COUNT(*)
----------
0
SQL> select count(*) from v$bh where DIRTY='Y' and objd=(select data_object_id from user_objects where object_name='TRY');
COUNT(*)
----------
0
----> indicates NO dirty blocks i.e data on disk is accurate
----> verification: on disk 2 rows - 'aaa', 'bbb'
SQL> truncate table TRY;
Table truncated.
SQL> select object_id, data_object_id from user_objects where object_name='TRY';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
1192310 1192311
SQL> select count(*) from v$bh where DIRTY='Y' and objd=(select object_id from user_objects where object_name='TRY');
COUNT(*)
----------
0
SQL> select count(*) from v$bh where DIRTY='Y' and objd=(select data_object_id from user_objects where object_name='TRY');
COUNT(*)
----------
1 (seems to indicate dirty blocks present)
----> indicates dirty blocks present i.e data on disk is not accurate
----> verification: on disk 0 rows
As I verified that data on disk contains no rows, data_object_id seems to indicate otherwise... am I missing something??
TIA
|
|
|
|
|
|
| Re: how to find out if a table has dirty blocks in buffers [message #136714 is a reply to message #136567] |
Fri, 09 September 2005 11:37 |
nile
Messages: 13
Registered: September 2005
|
Junior Member |
|
|
same thing when I try 'alter table TRY move'. data_object_id indicates that there are dirty blocks present but data on disk seems ok...
----> verification: on disk 1 row - '111'
SQL> select object_id, data_object_id from user_objects where object_name='TRY';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
1192310 1192312
SQL> alter table TRY move;
Table altered.
SQL> select object_id, data_object_id from user_objects where object_name='TRY';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
1192310 1192313
SQL> select count(*) from v$bh where DIRTY='Y' and objd=(select object_id from user_objects where object_name='TRY');
COUNT(*)
----------
0
SQL> select count(*) from v$bh where DIRTY='Y' and objd=(select data_object_id from user_objects where object_name='TRY');
COUNT(*)
----------
1
----> verification: on disk 1 row - '111' (data_block_id indicates data on disk is not accurate????)
|
|
|
|
|
|
|
|
| Re: how to find out if a table has dirty blocks in buffers [message #136744 is a reply to message #136567] |
Fri, 09 September 2005 15:38 |
nile
Messages: 13
Registered: September 2005
|
Junior Member |
|
|
I could not relate the contents of the 'status' to figure it out. Adding the check for class# (=1) seems to work but I could not find enough documentation (even on oracle site for class number). So for the examples I tried, it seems to work but I am not sure if it covers all cases. Queries I tried follow. Let me know if you think this solution does not cover all cases.
SQL> alter system checkpoint;
System altered.
SQL> alter table TRY move;
Table altered.
SQL> select object_id, data_object_id from user_objects where object_name='TRY';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
1192325 1192326
SQL> select 'MODIFIED', status from v$bh where objd=(select data_object_id from user_objects where object_name='TRY' and class# = 1 and dirty = 'Y');
no rows selected
(conclusion: dirty blocks do not exist)
SQL> alter system checkpoint;
System altered.
SQL> select object_id, data_object_id from user_objects where object_name='TRY';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
1192325 1192326
SQL> select 'MODIFIED', status from v$bh where objd=(select data_object_id from user_objects where object_name='TRY' and class# = 1 and dirty = 'Y');
no rows selected
(conclusion: dirty blocks do not exist)
SQL> insert into TRY values ('aaa');
1 row created.
SQL> commit;
Commit complete.
SQL> select object_id, data_object_id from user_objects where object_name='TRY';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
1192325 1192326
SQL> select 'MODIFIED', status from v$bh where objd=(select data_object_id from user_objects where object_name='TRY' and class# = 1 and dirty = 'Y');
'MODIFIE STATU
-------- -----
MODIFIED xcur
(conclusion: dirty blocks exist)
SQL> alter system checkpoint;
System altered.
SQL> select 'MODIFIED', status from v$bh where objd=(select data_object_id from user_objects where object_name='TRY' and class# = 1 and dirty = 'Y');
no rows selected
(conclusion: dirty blocks do not exist)
SQL> insert into TRY values ('222');
1 row created.
SQL> commit;
Commit complete.
SQL> select 'MODIFIED', status from v$bh where objd=(select data_object_id from user_objects where object_name='TRY' and class# = 1 and dirty = 'Y');
'MODIFIE STATU
-------- -----
MODIFIED xcur
(conclusion: dirty blocks exist)
|
|
|
|
| Re: how to find out if a table has dirty blocks in buffers [message #136951 is a reply to message #136567] |
Mon, 12 September 2005 07:57 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Looks good...I saw a list for the class# values on asktom, and 1 was the only data or index block. Rest was undo blocks or system blocks or segment headers.
So, now that we at least have a pretty good answer, one that will provide at the least an estimate, what is your goal in all this?
|
|
|
|
| Re: how to find out if a table has dirty blocks in buffers [message #136985 is a reply to message #136744] |
Mon, 12 September 2005 10:25 |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
>>select 'MODIFIED', status from v$bh where objd=(select data_object_id from user_objects where object_name='TRY' and class# = 1 and dirty = 'Y');
I dont understand what you are trying to do here.
There are no columns CLASS OR DIrty in user_objects.
So the above sql is WRONG.
By Theory,
Truncate will reset HWM. THis is known and proved
Truncate will write all dirty blocks to disk.
But there is something over here, when we truncate the table.
I "beleive" , Dirty blocks are NOT written immediately.
They are placed in a checkpoint queue system.
SO only when a checkpoint occurs, the dirty buffers are actually written to Disk.
I still have no proof ( convincing proof) for my above theory and i am working on that.
What i found is for a truncate , there is always a 'local write wait' event.
Please seee the session.
scott@9i > create table try (name char(10)) tablespace users;
Table created.
scott@9i > get get_status
1 SELECT o.data_object_id,
2 bh.dirty,
3 bh.STATUs,
4 count(*)
5 FROM (select * from user_objects where object_name = 'TRY') o,
6 v$bh bh
7 WHERE o.data_object_id = bh.objd
8* group by O.data_object_id,bh.dirty,bh.STATUS
scott@9i > insert into try ( select dbms_random.string('A',10) from dba_extents where rownum < 10000);
1515 rows created.
scott@9i > @get_status;
DATA_OBJECT_ID D STATU COUNT(*)
-------------- - ----- ----------
6369 N xcur 1
6369 Y xcur 7
scott@9i > commit;
Commit complete.
scott@9i > @get_status
DATA_OBJECT_ID D STATU COUNT(*)
-------------- - ----- ----------
6369 N xcur 1
6369 Y xcur 7
scott@9i > alter system checkpoint;
System altered.
scott@9i > @get_status;
DATA_OBJECT_ID D STATU COUNT(*)
-------------- - ----- ----------
6369 N xcur 8
scott@9i > insert into try ( select dbms_random.string('A',10) from dba_extents where rownum < 10000);
1515 rows created.
scott@9i > commit;
Commit complete.
scott@9i > @get_status;
DATA_OBJECT_ID D STATU COUNT(*)
-------------- - ----- ----------
6369 N xcur 3
6369 Y xcur 13
scott@9i > truncate table try;
Table truncated.
scott@9i > @get_status;
DATA_OBJECT_ID D STATU COUNT(*)
-------------- - ----- ----------
6370 N cr 1
6370 Y xcur 1
scott@9i > alter table try move tablespace users;
Table altered.
scott@9i > @get_status
DATA_OBJECT_ID D STATU COUNT(*)
-------------- - ----- ----------
6371 Y xcur 3
scott@9i > alter system checkpoint;
System altered.
scott@9i > @get_status;
DATA_OBJECT_ID D STATU COUNT(*)
-------------- - ----- ----------
6371 N xcur 3
|
|
|
|
| Re: how to find out if a table has dirty blocks in buffers [message #137026 is a reply to message #136567] |
Mon, 12 September 2005 18:05 |
nile
Messages: 13
Registered: September 2005
|
Junior Member |
|
|
>> I dont understand what you are trying to do here.
>>There are no columns CLASS OR DIrty in user_objects.
Sorry, the SQL should have looked like this:
SQL> select 1 from v$bh where class# = 1 and dirty = 'Y' and objd=(select data_object_id from user_objects where object_name = 'TRY1' and data_object_id
is not null);
(tried it on a bunch of cases and it seems to work but I can't be 100% sure as I would need to read more about 'class#' and 'status' about which there is not too much info even in ora docs.
>> Truncate will reset HWM. THis is known and proved
>> Truncate will write all dirty blocks to disk.
After reading your comment, I read about HWMs and things are clearer now. Thanks. Since the HWM is reset, there would be no data to read whether checkpoint is done or not.
|
|
|
|
| Re: how to find out if a table has dirty blocks in buffers [message #137358 is a reply to message #136567] |
Wed, 14 September 2005 16:37 |
nile
Messages: 13
Registered: September 2005
|
Junior Member |
|
|
partitioned tables have partitions dealt with separately so the query is:
SQL> select 1 from v$bh where class# = 1 and dirty = 'Y' and objd IN (select data_object_id from user_objects where object_name = 'TRY1' and data_object_id
is not null);
Also here are the class# values:
1 Data/Index GC_DB_LOCKS
2 Sort blocks No PCM lock held for these blocks
3 Save UNDO Blocks GC_SAVE_ROLLBACK_LOCKS
4 Segment Headers GC_SEGMENTS
5 Save UNDO Headers GC_ROLLBACK_LOCKS
6 Free List Groups GC_FREELIST_GROUPS
7 System UNDO segment hdr None
8 System UNDO blocks GC_ROLLBACK_LOCKS
7+(2n) User UNDO segment hdr n GC_ROLLBACK_SEGMENTS
7+(2n+1)User UNDO segment GC_ROLLBACK_LOCKS
|
|
|
|
| Re: how to find out if a table has dirty blocks in buffers [message #137488 is a reply to message #136567] |
Thu, 15 September 2005 06:36 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
What is your source on those class# values? I had seen something similar.
Also, what is are the bits after you list what the class# stands for? For example, GC_SEGMENTS? Is that just a global constant variable declared somewhere? Do you know where?
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
