Oracle Clear data block (logical) corrupt from dbverify

** Myself

dbverify report  (dbv) detect corrupt blocks but no physical corrupt.
for 10g

Verify after dbv
check  v$database_block_corruption or v$backup_corruption (from rman verfity)
run {
allocate channel c1 type disk;
allocate channel c2 type disk;
backup check logical validate database;
release channel c1;
release channel c2;
}


Fix
1) run analyze object  for check  integrity and run dbv again
analyze <xxx> validate structure [ONLINE]
2) or flush db buffer cache and run dbv again
   alter system flush buffer_cache

Problems DBVERIFY Oracle 10.2.0.5/11.2.0.X (bad num_itls:,csc(aaaa.bbbb) higher than,itl[] has higher commit scn

** Myself & SAP Note 1566406

Symptom
dbv report errors related to transaction layer.
Following messages can be reported by dbveryfy:

"bad num_itls: <itlCount>"


"csc(aaaa.bbbb) higher than block scn(xxxx.yyyy)


"itl[<itl_id>] has higher commit scn(aaaa.bbbb) than block scn (xxxx.yyyy)"


"Control <csc> greater than block <block scn>"


"Slot[<slot>] greater than block <block scn> "
Page <pageno> failed with check code <CheckCode>

CheckCode can be:
6051 - bad block scn
6052 - bad objd
6053 - bad segment id
6054 - bad csc
6055 - bad free list link
6056 - bad itl scn
6057 - bad # itls



Reason and PrerequisitesStarting with Oracle 10.2.0.5, DBVerify has been enhanced to identify more
logical block corruptions in the transaction layer like SCN logical

Oracle DBMS UTILITY.MAKE DATA BLOCK ADDRESS

A data block address is the internal structure used to identify a block in the database. This function is useful when accessing certain fixed tables that contain data block addresses.

Can be used for investigate corruption of oracle internal structure for find actual data block address (DBA)

Check oracle segment for actual data block address

SQL> select segment_name,segment_type,header_file,header_block from dba_segments where segment_name like 'USR02' ;

SEGMENT_NAME                   SEGMEN HEADER_FILE HEADER_BLOCK

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

USR02                           TABLE            5       195075

SQL> select dbms_utility.make_data_block_address (5,195075) from dual;

dbms_utility.make_data_block_address (5,195075)

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

                                                                         21166595

## convert 21166595(DEC)=0x0142fa03(HEX)

## rdba: 0x0142fa03 (file 5, block 195075)

Problem oracle Instance crash Bug 7662491 ORA-600 OERI[kghstack_free1] [kddummy_blkchk]

** Myself,team,Note
Cause
ORA-00600: internal error code, arguments: [kghstack_free1], [context for tx CR rollback], [], [], [], [], [], []
Result if Don’t apply
#Instance crash database down and write to alert log and oracle shadow process
#DB writer trace
#Oracle shadow process trace
Result After apply Patch 7662491
#Instance No crash but oracle server process die
#Pointer in memory is overwritten in error by a software problem
#No DB writer trace
#Oracle shadow process log=/oracle/PRD/saptrace/usertrace/PRD_ora_27810.trc

Step apply Interim patch
1. Stop database and listener (by ora<sid> user)
SQL>shutdown immediate;
lsnrctl stop
2. Install interim patch 7662491 on (by ora<sid> user)
#$ORACLE_HOME/OPatch/opatch apply /<Patch_Base_Path>/7662491
3. Check interim inventory report
#$ORACLE_HOME/OPatch/opatch lsinventory
4. Startup database (by ora<sid>user)
SQL>startup;
5. Startup listener (by ora<sid>user)
lsnrctl start
6. Check oracle process and listener
ps -ef|grep smon
ps -ef|grep tns
7. Check listener
tnsping <SID>

Problem oracle query access slow from install oracle interim patch 9558995

** Myself,team,Note
Cause
For inserts in LOB segments, long wait events "enq: HW contents" occur if the relevant table and the LOB segments are created in an ASSM tablespace (TBS) Table TST03,VBDATA.
Change the storage option of the LOB from RETENTION to PCTVERSION.
SQL> alter table <OWNER>.<TABLE NAME> modify lob (<LOB NAME>) (pctversion 10);
For
example, for table TST03:
SQL> alter table SAPSR3.TST03 modify lob (DCONTENT) (pctversion 10);
Result if Don’t apply
# long wait events and slow write table TST03,VBDATA

Result After rollback Patch and reduce pctversion storage option
# Normal operation

Step remove Interim patch
1. Stop database and listener (by ora<sid> user)
SQL>shutdown immediate;
lsnrctl stop
2. Deinstall interim patch 9558995 on (by ora<sid> user)
#cd /<Patch_Base_Path>/SAP_102044_201005/9558995
#$ORACLE_HOME/OPatch/opatch rollback -id 9558995

Oracle DB Suspended Rman Backup jobs "suspended on Agent Unreachable"

** Myself


Verify status of EM agent
emctl status agent

-> start for service down
emctl start agent

Oralce DB Restoration command

** Myself

Noarchive log mode
Recovery with Current Control Files Complete Recovery (applied online redo)     NoResetLogs    (online redo logs available)	Sql> shutdown abort Restore n  All datafiles n  Controlfiles n  Redolog files n  Password files and Parameter files Sql> startup
Recovery with Backup control Files Complete Recovery (applied online redo)     NoResetLogs    (online redo logs available)     Incomplete Recovery (applied online redo)     ResetLogs    (online redo logs available     or NOT available)	#Can’t used Backup control files but use to generate new control files Sql> shutdown immediate Restore n  All datafiles n  Old Controlfiles n  Redolog files (for Noresetlog) n  Password files and Parameter files Sql> startup mount Sql> database backup controlfile to trace; Sql> shutdown Sql> @<admin>/udumn/<*.trl> Sql> alter database open [resetlogs]
Recovery with New control Files Complete Recovery (applied online redo)     NoResetlogs    (online redo logs available)     Incomplete Recovery (applied online redo)     ResetLogs    (online redo logs available     or NOT available)	Sql> shutdown immediate Restore n  All datafiles n  Old Controlfiles n  Redolog files (for Noresetlog) n  Password files and Parameter files Sql> startup mount Sql> database backup controlfile to trace; Sql> shutdown Sql> @<admin>/udumn/<*.trl> Sql> alter database open [resetlogs]

Oracle Snapshot too old" (ORA-01555) occur/Undo space insufficient

** Myself & Oracle Note & SAP Notes
How often and when does "Snapshot too old" (ORA-01555) occur?
set linesize 140
select to_char(begin_time,'YYYY-MM-DD HH24:MI:SS') "Begin",
to_char(end_time,'YYYY-MM-DD HH24:MI:SS') "End ",
undoblks "UndoBlocks",undoblks*8/1024 "UndoBlocks(MB)”,
SSOLDERRCNT "ORA-1555",MAXQUERYLEN "Query Time(sec)",MAXQUERYLEN/60/60 "Query Time(hr)"
from v$undostat
where SSOLDERRCNT > 0;

 
Calculation Undo
Undospace         =UR*UPS*8k
(UR=Undo retention,UPS=Max undo blocks)

Oracle DB Using Thai Support Features in Oracle Database [Thai Version]

** Web Narisa

ตัวแปร NLS_LANG ประกอบด้วย 3 ส่วนด้วยกัน คือ Language, Territory และ Character Set ซึ่งอยู่ในรูปแบบ

NLS_LANG = language_territory.charset

กติกาง่ายๆ คือ กำหนดส่วนของ Character Set ในตัวแปร NLS_LANG ให้ตรงกับ Database Character Set ปัจจุบัน เพื่อป้องกันปัญหาการ Encode ข้อมูลผิด อย่างเช่น แสดงข้อมูลออกมาเป็น '??????' หรือ 'ฟฟฟฟฟ'

ตัวอย่างเช่น ถ้า Database ที่เราสร้างขึ้นมามี Character Set เป็น TH8TISASCII ดังนั้นการกำหนดค่า NLS_LANG ของ Client ก็อาจจะกำหนดได้ดังตัวอย่างข้างล่างนี้ ซึ่งผลที่ได้จะทำให้ไม่มีปัญหาเกี่ยวกับการ Encode ข้อมูลผิดเป็น  '??????' หรือ 'ฟฟฟฟฟ'

NLS_LANG=THAI_THAILAND.TH8TISASCII or NLS_LANG=AMERICAN_AMERICA.TH8TISASCII or NLS_LANG=THAI_AMERICA.TH8TISASCII

SQL> select * from  NLS_DATABASE_PARAMETERS;

PARAMETER                      VALUE

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

NLS_LANGUAGE                   AMERICAN

NLS_TERRITORY                  AMERICA

NLS_CURRENCY                   $

NLS_ISO_CURRENCY               AMERICA

NLS_NUMERIC_CHARACTERS         .,

NLS_CHARACTERSET               TH8TISASCII

Unix session parameter

export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1

 

Window session parameter

set NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1

Oracle Database query for check invalid object and fix it

** Myself
--Check invalid object
SQL>spool invalid_objects
SQL> select unique object_name, object_type, owner from dba_objects where status = 'INVALID';
--Fix it
SQL>@?/rdbms/admin/utlrp.sql
SQL> select unique object_name, object_type, owner from dba_objects where status = 'INVALID';
SQL>spool off

Oracle Rac How to Clean Up After a Failed CRS(cluster ready service) Install

** Credit Web

10g RAC: How to Clean Up After a Failed CRS Install 

  Doc ID:  Note:239998.1 Type:  BULLETIN 

  Last Revision Date:  25-OCT-2005 Status:  PUBLISHED 

PURPOSE

-------

 The purpose of this document is to help DBA's and support analysts understand how

to clean up a failed CRS (Cluster Ready Services) install for 10g RAC.

 SCOPE & APPLICATION

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

 DBA's and Support Analysts

10g RAC: How to Clean Up After a Failed CRS Install

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

FAQ:Netbackup Configuration with Oracle [Thai]

** Myself
Question:
ผมมี Oracle 8i และ 9i บน Sun solaris 8 และ 9 ตามลำดับ
ต้องการ Backup ครับ ด้วย Netbackup 6.0 MP5
Medea และ Master Server เป็นตัวเดียวกัน Windows Server 2003
คือมีของเดิมอยู่ครับ คนเก่าเค้าทำไว้ มีทั้ง Offline และ Online backup เท่าที่รู้ Offline นี่เค้าปิด Database และ copy files ออกมาคับ
ถึงตอนนี้ ระบบเปลี่ยนครับ มีการเพิ่มและลด database เลยจะทำ backup job เพิ่มและลบของเดิมออกด้วย ครับ นั่งอ่าน oracle มาสามสัปดาห์แล้วคับ
ถ้าฝั้ง windows ล่ะง่ายเลย(พวก MS SQL) นะคับ ปกติไม่ได้จับ Oracle คับ
Ans:
ลำบากนิดนึงน๊ะครับสำหรับคนไม่เคยจับ oracle และแนะนำให้ไปดู ใน web veritas สำหรับ netbackup กับ oracle เพิ่ม

Oracle Query check How often and when does "Snapshot too old" (ORA-01555) occur

** Myself
##How often and when does "Snapshot too old" (ORA-01555) occur?
set linesize 140
select to_char(begin_time,'YYYY-MM-DD HH24:MI:SS') "Begin",
to_char(end_time,'YYYY-MM-DD HH24:MI:SS') "End ",
undoblks "UndoBlocks",undoblks*8/1024 "UndoBlocks(MB)",
SSOLDERRCNT "ORA-1555",MAXQUERYLEN "Query Time(sec)",MAXQUERYLEN/60/60 "Query Time(hr)"
from v$undostat
where SSOLDERRCNT > 0;

##Depending on the result: Increase the undo retention