Server Administration :: Fractured Block During Buffer Read?
Nov 17, 2011
I am in charge of several instances located on a Linux server CentOS, virtualized on a ESX 3.5 environment.
From time to time (every 4 to 5 days), I have some errors in the alert.log. Last occurence was last night :
Corrupt block relative dba: 0x01004e12 (file 4, block 19986)
Fractured block found during buffer read
Data in bad block:
type: 6 format: 2 rdba: 0x01004e12
last change scn: 0x0000.131aaa5b seq: 0x2 flg: 0x04
[Code] ........
We are doing user manual backup (with BEGIN/END BACKUP) every night at 8PM, ending at 9PM approx. Then, fractured blocks never occur during backups. At 1AM, the maintenance window is opening, thus explaining the GATHER_STATS_JOB job.
When I check corruption on early morning, I am always unable to reproduce the problem. DBV is OK without issues. We never had a problem with the data itself, whatever it is a table or an index in the reported failed block.
I would like to know what could cause these logical corruption, and how to stop them ?
View 7 Replies
ADVERTISEMENT
Apr 17, 2010
When i was starting my database .there was an error
ora-00333:redo log read error block 203 count 8192.
View 1 Replies
View Related
May 27, 2011
I learnt that logWriter writes in the redo log files when redo log buffer is 1/3 full, it means that 66 % of redo log buffer are always empty and never used,
if no, isn't a waste of memory (66 % always empty !)
View 5 Replies
View Related
Jul 24, 2010
I have a multi record control block (basically a text item displaying 6 records) where user enters values and I want to process the values using pre-insert trigger.
I want to read value in each record and then do some tasks using a pre-insert trigger before I commit the values. To navigate between the records I was using first_record, next_record, clear_record built-ins but it gives errors like "40737-illegalrestricted procedure next_record in pre-insert trigger".
View 3 Replies
View Related
Aug 19, 2010
1)If i issue a DELETE statement to delete a row, will this statement drag any data from the datafile to database buffer? How is the change made by a DELETE statement recorded in buffer cache? How is this change then applied to the data in datafiles after commit?
View 7 Replies
View Related
Mar 28, 2011
How can i know which objects used keep buffer cache?
View 5 Replies
View Related
Jan 26, 2011
Say Database Buffer Cache configured as 2M and my updates may use 4m size,will it throw an error message or update will happen perfectly without any issues?
View 2 Replies
View Related
Jan 4, 2012
How to read the treedump contents of a index IDX_TB_TEST_N1?
SQL> select object_id,object_name from dba_objects where owner='HXL';
OBJECT_ID OBJECT_NAME
---------- ---------------------------------------------
51786 IDX_TB_TEST_N1
alter session set events 'immediate trace name treedump level 51786'
/u01/app/oracle/admin/oracl/udump/oracl_ora_2679.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
[code]...
View 6 Replies
View Related
Feb 5, 2010
I am getting an error through by application when i am trying to access a screen.
Error:No data to read from socket.
What could be the reasons for this error.
View 1 Replies
View Related
Jun 13, 2010
I need to display the parameter and status of DB for listener and Read Only.
I know those value could be get from command line , but could we get the values of Listener and Read only by SQL/PlSQL? So I can get it through the query of DB.
View 8 Replies
View Related
Apr 5, 2010
I am facing a strange issue on 11gR2 (OEL 5.4) standby readonly with apply database.It's throwing 16000: database open for read-only access during SELECT's .
Here is snapshot of errors.
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access
SQL> l
1* SELECT t0.airportID, t0.archived, t0.assetTag, t0.bluetoothID, t0.cmBundle, t0.createdDate,
t0.currentProductTaskID, t0.ethernetID, t0.failOrReworkCount, t0.highestCompletedTaskTypeID, t0.lastModDate, t0.lastStationID,
t0.modCount, t0.modelID, t0.oemSerialNumber, t0.orgSerialNumber, t0.pdmVersion, t0.preburnComplete,
t0.productID, t0.reworked, t0.secondaryEthernetID,
t0.serialNumber, t0.shipped, t0.specialBuildTypeID,
[code]....
View 7 Replies
View Related
Nov 24, 2012
I have created an user named "Raja" with a default tablespace as "Raja_TBS" along with a datafile "rajadata.dbf". I have taken the tablespace offline
SQL> alter tablespace raja_tbs offline;
Tablespace Altered. when I take a tablespace offline, which means I cannot read or write and the tablespace is currently unavailable for users. I am still able to create a table on the "Raja_TBS" while it is offline.
View 39 Replies
View Related
Jan 6, 2013
i have tried to create the index in diff tbs also but same error is there
SQL> create index inx_tbl_voicechat_unsub_ani on tbl_voicechat_unsub (ani) tablespace ideadb_index;
create index inx_tbl_voicechat_unsub_ani on tbl_voicechat_unsub (ani) tablespace ideadb_index
*
ERROR at line 1:
ORA-01115: IO error reading block from file 201 (block # 144265)
ORA-27070: async read/write failed
OSD-04016: Error queuing an asynchronous I/O request.
O/S-Error: (OS 23) Data error (cyclic redundancy check).
ORA-01115: IO error reading block from file 201 (block # 144265)
ORA-27070: async read/write failed
OSD-04016: Error queuing an asynchronous I/O request.
O/S-Error: (OS 23) Data error (cyclic redundancy check).
View 10 Replies
View Related
Feb 7, 2011
) How to find out, whether my Query return the output from the block of BUFFER CACHE or from DATAFILES?
2) How to calculate the no of data blocks were used to return a single output.
View 2 Replies
View Related
Aug 30, 2012
we are generating Entity beans at java appication. where application is connecting to DB and copy Table structure in Java class file. when this process start, oracle process takes 100% CPU. while there is no wait event at DB level. only queries are fired on oracle sys tables.(user_tables,user_constraints,etc..)
On trial and error based, we have changed this parameter and that java process works fine. how this parameter effect the on oracle system table.
View 16 Replies
View Related
Aug 15, 2011
I am using oracle 10g with sga_max_size =4GB and db block size 16k. Now i am creating a tablespace with block size 32 kb , whats value i select for the parameter db_32k_cache_size.
Is there any standard way to calculate the value of this parameter.
View 4 Replies
View Related
Mar 31, 2012
①SQL> SELECT OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_NAME='T2012';
OBJECT_ID
---------
57082
②SQL> SELECT HEADER_BLOCK,BLOCKS FROM DBA_SEGMENTS WHERE SEGMENT_NAME = 'T2012';
HEADER_BLOCK BLOCKS
------------- --------
683 8
③SQL> SELECT DBMS_ROWID.rowid_block_number(ROWID)USED_BLOCK_NUMBER FROM SCOTT.T2012;
USED_BLOCK_NUMBER
----------------
684
④SQL> SHUTDOWN IMMEDIATE;
⑤SQL> STARTUP;
⑥SQL> SELECT BLOCK#,CLASS# FROM V$BH WHERE OBJD = '57082';
no data found
⑦SQL> SELECT * FROM SCOTT.T2012;
ID
-----
1
⑧SQL> SELECT BLOCK#,CLASS# FROM V$BH WHERE OBJD='57082';
BLOCK# CLASS#
------- ----------
686 1
684 1
687 1
685 1
688 1
683 4
⑨SQL> SELECT EMPTY_BLOCKS FROM DBA_TABLES WHERE TABLE_NAME='T2012';
EMPTY_BLOCKS
------------
3
QUESTIONS ONE:
in the ⑧ step,why block#685,block#686,block#687,block#688 in the buffer cache after i query data from scott.T2012?
QUESTIONS TWO:
in the ⑨ step,what's the block number of the empty block?just like DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID).
View 2 Replies
View Related
Nov 17, 2011
Is there any way I can calculate percentage of space used in a block.Eg if a table size is 100 blocks,How Can I check the percentage of used space in block.
View 6 Replies
View Related
Feb 26, 2013
for some reason,i want to know which data block contains free space,or which table/index contains free space.
View 6 Replies
View Related
May 12, 2011
1) What is PHYSICAL/LOGICAL Corruption.
2) How it occurs.
3) Will RMAN works on both the types of corruption or only Physical (My senior told it works on both).
View 3 Replies
View Related
Apr 26, 2011
I am trying to create standby with oracle 10g R2 from Solaris 5.10 (sun ultrasparc X86-64 bit) to Solaris 5.10(Intel Xeon 64 bit). All the mount points are the same as primary server. All the data files are at location as specified in control file. I created standby control file from primary. All the control files are at location specified in pfile.
When I am trying to mount database I am getting following error.
ORA-00202: control file: '/oracle/BP1/ctl1.dbf'
ORA-27047: unable to read the header block of file
ORA-205 signalled during: alter database mount...
I try to open database with trace file also but got following error..
ORA-01503: CREATE CONTROLFILE failed
ORA-01565: error in identifying file
'/oracle/BP1/sapdata1/system_1/system.data1'
ORA-27047: unable to read the header block of file
View 1 Replies
View Related
Jan 4, 2012
One of our developers team member had created a Anonymous block program to do something in the Database, and he forgotten and terminated the session without confirming the program's status, whether it was fully ran or not.
Is there any way to check out the status of this, which happened yesterday?
View 3 Replies
View Related
Jan 6, 2011
We are creating non standard blocking size for 16k and so accordingly have to set the parameter db_16k_cache_size.
if have any thumb rule for setting the value for this parameter.
View 5 Replies
View Related
May 16, 2012
I have a database where view v$database_block_corruption shows a corrupted block
SQL> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
4 756652 1 5.5157E+12 CORRUPT
I am not able to get the relevant segment from the above information
SQL> select segment_name, segment_type, owner
2 from dba_extents
3 where file_id = 4
4 and 756652 between block_id
5 and block_id + blocks -1;
no rows selected
DBVERIFY Summary
DBVERIFY - Verification complete
Total Pages Examined : 3932160
Total Pages Processed (Data) : 3119107
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 755048
[code]....
I have uploaded the complete logfile.
Below is a part of logfile
DBVERIFY - Verification starting : FILE = /prd/dvp/ora/oradata/LHF/disk06/gds_t01_01.dbf
Block Checking: DBA = 21728172, Block Type = KTB-managed data block
**** kdxcoavs = -84 < 0, avail = 3129
---- end index block validation
Page 756652 failed with check code 6401
##not here that 756652 is the same block# mentioned in v$database_block_corruption
here i tried finding the OBJECT ID as below
SELECT dbms_utility.data_block_address_block(21728172) "BLOCK", dbms_utility.data_block_address_file(21728172) "FILE" FROM dual;
BLOCK FILE
---------- ----------
756652 5
Now for the same BLOCK it is giving different File, again the segment_name or segment_id could not be found from the above information.
View 4 Replies
View Related
Jan 9, 2012
The stand alone stored procedure has 2 parameter, an IN and OUT...
CREATE OR REPLACE PROCEDURE someprocedure( businessdate IN NUMBER,
tablename OUT VARCHAR2)
This procedure has multiple inner blocks. Intention is to control execution of each of the inner blocks basing on the value of flag field obtained from a "processlogtable". This processlog table has structure as below.
jobname varchar2(100)
controldate (date)
controlflag varchar2(1)
I have the below code snippet at the beginning of each inner block that checks value of the flag and then proceeds with execution of that block. The intent is to avoid redundant call of a block that has successfully executed first time. That is, once a specific inner block fails for some reason, the re-execution of the stored procedure should AVOID re-executing the PRIOR successed steps.
I've the code set up as below, but the prior successed block(s) code gets re-executed again once the procedure is re-executed after a failure.
CREATE OR REPLACE PROCEDURE someprocedure( businessdate IN NUMBER,tablename OUT VARCHAR2)
CURSOR c_missingtablename
IS
SELECT datatablename
FROM (
SELECT UPPER(datatablename ) TABLE
FROM
WHERE datatableName IN ('Aaa','BbB','CcC');
[code]....
View 5 Replies
View Related
Jul 10, 2012
The block size of my db(10gr2) is 8k, and the db is on the raw device file system on a aix machine.
OS block size is 512k.
How much block will be read from disk when a single oracle io call occurs? Which one is currect, 8K or 512K?
If the block size for a single io call depends on the os block size (512k), I think os block size need to be tuned for oracle block size (8k). If we use raw device file system, os level block size has no meaning?
View 1 Replies
View Related
Oct 24, 2011
SQL> select block_size from v$controlfile;
BLOCK_SIZE
----------
16384
SQL> show parameter db_block_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
the 2 can have difference block size?
View 3 Replies
View Related
Sep 4, 2012
I am having 3 oracle database instances running on 3 seperate Linux Node(RHEL Node).
Instance -1 named - DS
Instance -2 named - MIS
Instance -3 named - OAS
Among of these 3 nodes, we are facing Block Corruption issues with sysaux tablespace.Error in Instance name DS is
Errors in file /u01/home/dba/oracle/diag/rdbms/ds4db/DS/trace/DS_j000_655388.trc (incident=300847):
ORA-01578: ORACLE data block corrupted (file # 2, block # 38428)
ORA-01110: data file 2: '/DSdb1/dssysaux.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
GATHER_STATS_JOB encountered errors. Check the trace file.
[code]....
for this I googled and found some solution as oracle doc [430230.1] related to sysaux couruption.After this again we are facing the same issue in sysaux tablespace.
View 19 Replies
View Related
Nov 26, 2011
i written this code i m facing ORA-04030: out of process memory when trying to allocate 16408 bytes error
/* Formatted on 2011/11/26 11:52 (Formatter Plus v4.8. */
DECLARE
row_id varchar2(50);
v_batch_id temp.batch_id%TYPE;
v_slab_id temp.slab_id%TYPE;
flag NUMBER (2);
num varchar2(50) := &row_id;
[code].....
View 1 Replies
View Related
Aug 31, 2010
My understanding of DB_FILE_MULTIBLOCK_READ_COUNT parameter is that it affects only Full Table Scans and Fast Full Index Scans - all other disk retrieval is single block.If so, then maybe I'm reading this trace incorrectly:
select /*+ first_rows */ pk
from test_join_tgt
where pk >= 0
and rownum > 1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 21.48 27.77 22368 22134 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 21.49 27.78 22368 22134 0 0
[code]...
What the heck is going on with the Multi-block reads in the Range Scan?
View 3 Replies
View Related
