What's Happening - if flash cache file corrupt

This is something, I wish to know. if flash cache file corrupt.
Question: What's happening - if flash cache file has the problem(corrupt)?

Flash Cache parameters.

SQL> show parameter db_flash_cache_file

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flash_cache_file string /dev/sdc

SQL> show parameter db_flash_cache_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flash_cache_size big integer 20G

Number of database blocks in flash cache script.

SELECT
SUM (CASE WHEN b.status LIKE 'flash%' THEN 1 else 0 END) flash_blocks, SUM
(CASE WHEN b.status LIKE 'flash%' THEN 0 else 1 END) cache_blocks,
count(*) total_blocks
FROM v$bh b
/

- Check on flash cache file.

$ dd if=/dev/sdc count=1024 | strings
Oracle RDBMS Flash Cache File
orcl
1024+0 records in
1024+0 records out

- Check database blocks in flash cache.

FLASH_BLOCKS CACHE_BLOCKS TOTAL_BLOCKS
------------ ------------ ------------
1352 3417 4769

A. Test To Destroy Flash Cache File.

$ dd if=/dev/zero of=/dev/sdc bs=4k count=10000
10000+0 records in
10000+0 records out
40960000 bytes (41 MB) copied, 0.044147 seconds, 928 MB/s

$ dd if=/dev/sdc count=1024 | strings
1024+0 records in
1024+0 records out

Flash Cache File corrupt.

- Check Alert Log File.

Sun Feb 28 22:33:59 2010
Encounter problem verifying flash cache /dev/sdc. Disable flash cache and issue an ORA-700 for diagnostics
Errors in file /u01/app/diag/rdbms/orcl/orcl/trace/orcl_gen0_9755.trc (incident=71223):
ORA-00700: soft internal error, arguments: [kcbl2vfyfh_action], [db_flash_cache_file integrity check failed], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/diag/rdbms/orcl/orcl/incident/incdir_71223/orcl_gen0_9755_i71223.trc
Sun Feb 28 22:33:59 2010
L2 cache file closed by dbwr 0
L2 cache disabled for dbwr 0
Sun Feb 28 22:33:59 2010
Sweep [inc][71223]: completed
Sweep [inc2][71223]: completed

- Check database blocks in flash cache.

FLASH_BLOCKS CACHE_BLOCKS TOTAL_BLOCKS
------------ ------------ ------------
0 3412 3412

B. Test DML on table.

SQL> update tb_test01 set object_id=101;

- Check Alert Log File.

Nothing Error

- Check flash cache file.

$ dd if=/dev/sdc count=1024 | strings
1024+0 records in
1024+0 records out

- Check database blocks in flash cache.

FLASH_BLOCKS CACHE_BLOCKS TOTAL_BLOCKS
------------ ------------ ------------
0 3412 3412

The flash cache is disabled, So database could not use flash cache.

C. Solve this issue.

SQL> show parameter db_flash_cache_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flash_cache_size big integer 20G

SQL> alter system set db_flash_cache_size=20G;

System altered.

SQL> show parameter db_flash_cache_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flash_cache_size big integer 20G

- Check Alert Log File.

Sun Feb 28 22:36:47 2010
Dynamically re-enabling flash cache db_flash_cache_file (/dev/sdc) to size 21474836480
ALTER SYSTEM SET db_flash_cache_size='20G' SCOPE=BOTH;

- Check flash cache file

$ dd if=/dev/sdc count=1024 | strings
Oracle RDBMS Flash Cache File
orcl
1024+0 records in
1024+0 records out

- Check database blocks in flash cache

FLASH_BLOCKS CACHE_BLOCKS TOTAL_BLOCKS
------------ ------------ ------------
1877 3437 5314

Answer: The Flash Cache is disabled, If Flash Cache File has the problem.

How To Clear DB Blocks In Flash Cache

For each database block moved from the buffer cache to the flash cache. We check...

SELECT
SUM (CASE WHEN b.status LIKE 'flash%' THEN 1 else 0 END) flash_blocks, SUM
(CASE WHEN b.status LIKE 'flash%' THEN 0 else 1 END) cache_blocks,
count(*) total_blocks
FROM v$bh b
/

We'll see number of database blocks in flash cache.

FLASH_BLOCKS CACHE_BLOCKS TOTAL_BLOCKS
------------ ------------ ------------
3132 3427 6559

We see 3132 blocks in flash cache.
How to clear database blocks in flash cache?

- Disable Flash Cache

SQL> show parameter db_flash_cache_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flash_cache_size big integer 20G

SQL> alter system set db_flash_cache_size=0;

System altered.

SQL> show parameter db_flash_cache_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flash_cache_size big integer 0

Check

FLASH_BLOCKS CACHE_BLOCKS TOTAL_BLOCKS
------------ ------------ ------------
0 3427 3427

- Flush Buffer Cache

The FLUSH BUFFER_CACHE clause lets you clear all data from the buffer cache in the system global area (SGA), including the KEEP, RECYCLE, and DEFAULT buffer pools.

I'm curious with it, so test & check.

FLASH_BLOCKS CACHE_BLOCKS TOTAL_BLOCKS
------------ ------------ ------------
2404 3382 5786

Alter system...

SQL> alter system flush buffer_cache;

System altered.

Check

FLASH_BLOCKS CACHE_BLOCKS TOTAL_BLOCKS
------------ ------------ ------------
0 3382 3382

After, I flushed buffer_cache, I don't see database block in flash cache. However I think we should use Disable Flash Cache.
This is just fun for test system, don't flush buffer cache on production system.

Oracle 11gR2 Database Flash Cache

Posted to setup the Database Flash Cache, then start to Test.

SQL> show parameter db_flash_cache_file

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flash_cache_file string /dev/sdc

SQL> show parameter db_flash_cache_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flash_cache_size big integer 10G

Good idea to use the flash cache, when "db file sequential read" is a top wait event.

The FLASH_CACHE clause lets you override the automatic buffer cache policy and specify how specific schema objects are cached in flash memory. To use this clause, Database Smart Flash Cache (flash cache) must be configured on your system. The flash cache is an extension of the database buffer cache that is stored on a flash disk, a storage device that uses flash memory. Because flash memory is faster than magnetic disks, the database can improve performance by caching buffers in the flash cache instead of reading from magnetic disk.

Example:

SQL> create table tb_test01 as select * from all_objects where rownum <=10;
Table created.

SQL> select table_name, flash_cache from user_tables where table_name='TB_TEST01';

TABLE_NAME FLASH_C
------------------------------ -------
TB_TEST01 DEFAULT

KEEP Specify KEEP if you want the schema object buffers to remain cached in the flash cache as long as the flash cache is large enough.

NONE Specify NONE to ensure that the schema object buffers are never cached in the flash cache. This allows you to reserve the flash cache space for more frequently accessed objects.

DEFAULT Specify DEFAULT if you want the schema object buffers to be written to the flash cache when they are aged out of main memory, and then be aged out of the flash cache with the standard buffer cache replacement algorithm. This is the default if flash cache is configured and you do not specify KEEP or NONE.

SQL> create table TB_TEST01 storage( flash_cache keep) as select * from all_objects;

Table created.

SQL> select table_name,flash_cache from user_tables;

TABLE_NAME FLASH_C
------------------------------ -------
TB_TEST01 KEEP

Check V$SYSSTAT

SQL> select * from v$sysstat where name like 'flash cache%';

STATISTIC# NAME CLASS VALUE STAT_ID
---------- ---------------------------------------- ---------- ---------- ----------
87 flash cache inserts 8 27933 2941771786
91 flash cache eviction: invalidated 8 22256 3647825193
92 flash cache eviction: buffer pinned 8 0 3134415242
93 flash cache eviction: aged out 8 0 3307935113
94 flash cache insert skip: not current 8 12 1693383402
95 flash cache insert skip: DBWR overloaded 8 0 3504558414
96 flash cache insert skip: exists 8 8150 3291155557
97 flash cache insert skip: not useful 8 3738 3620030478
98 flash cache insert skip: modification 8 195 4128442906
99 flash cache insert skip: corrupt 8 0 2560222967

Check number of blocks(segment) in the flash cache.

SQL> SELECT owner || '.' || object_name object,
SUM (CASE WHEN b.status LIKE 'flash%' THEN 1 END) flash_blocks,
SUM (CASE WHEN b.status LIKE 'flash%' THEN 0 else 1 END) cache_blocks,
count(*) total_blocks
FROM v$bh b
JOIN
dba_objects
ON (objd = object_id)
where owner='SURACHART' and object_name='TB_TEST01'
group by owner,object_name
/

OBJECT FLASH_BLOCKS CACHE_BLOCKS TOTAL_BLOCKS
------------------------------ ------------ ------------ ------------
SURACHART.TB_TEST01 67 1607 1674

Or number of blocks(all segments) in the flash cache

SQL> SELECT
SUM (CASE WHEN b.status LIKE 'flash%' THEN 1 END) flash_blocks, SUM (CASE WHEN b.status LIKE 'flash%' THEN 0 else 1 END) cache_blocks,
count(*) total_blocks
FROM v$bh b
/

FLASH_BLOCKS CACHE_BLOCKS TOTAL_BLOCKS
------------ ------------ ------------
5535 14281 19816

However, We can disable the flash cache by set db_flash_cache_size to zero.

SQL> show parameter db_flash_cache_size;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flash_cache_size big integer 10G

SQL> alter system set db_flash_cache_size=0;

System altered.

SQL> show parameter db_flash_cache_size;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flash_cache_size big integer 0

Reference:
Oracle Document 11gR2
Using the Oracle 11GR2 database flash cache
Pardon Me, Where Is That Flash Cache? Part I.
Pardon Me, Where Is That Flash Cache? Part II.

11gR2(Test) Flash Cache on Redhat

I'd like to test Flash Array , then I installed Oracle 11gR2 ( + patched 8974084) on RHEL 5.3 and want to test Flash Cache Feature.

# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5.3 (Tikanga)

I don't check much oracle document, just read about configuration.
Choose disk:

# fdisk -l /dev/sdc

Disk /dev/sdc: 24.5 GB, 24575868928 bytes
255 heads, 63 sectors/track, 2987 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Disk /dev/sdc doesn't contain a valid partition table
# chmod 777 /dev/sdc

set Oracle initialization parameters:

$ sqlplus / as sysdba

SQL> alter system set db_flash_cache_file='/dev/sdc' scope=spfile;

System altered.

SQL> alter system set db_flash_cache_size=10G scope=spfile;

System altered.

Stop/Start database

SQL> shutdown immediate

SQL> startup
ORA-00439: feature not enabled: Server Flash Cache
ORA-01078: failure in processing system parameters

I found above error, then asked ... and find out. I found (oracle docs):

Your database is running on the Solaris or Oracle Enterprise Linux operating systems. The flash cache is supported on these operating systems only.

So, I had plan to install OEL. But... I'd like to figure out on Redhat as well.

Thank You Kevin Closson for figure me out. He is helpful me. I can use flash cache feature on Redhat.

SQL> startup;
ORACLE instance started.

Total System Global Area 7549435904 bytes
Fixed Size 2214456 bytes
Variable Size 4026533320 bytes
Database Buffers 3489660928 bytes
Redo Buffers 31027200 bytes
Database mounted.
Database opened.

SQL> show parameter db_flash_cache_file

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flash_cache_file string /dev/sdc

SQL> show parameter db_flash_cache_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flash_cache_size big integer 10G

-)

Remark:
Using db_flash_cache_file =/dev/sdc, Check -> A device name is Flash Cache File, isn't it?

$ dd if=/dev/sdc count=1024 | strings
Oracle RDBMS Flash Cache File
orcl
1024+0 records in
1024+0 records out
524288 bytes (524 kB) copied, 0.008165 seconds, 64.2 MB/s

Just Idea.

Oracle ACE - Time To Start More My Oracle Community

The Oracle ACE Program is designed to recognize and reward members of the Oracle Technology and Applications communities for their contributions to those communities. These individuals are technically proficient (when applicable) and willingly share their knowledge and experiences.

I'm an Oracle Ace. It's time to start more oracle community.

I hope this award will make me fun -)

Expert Oracle Practices: Oracle Database Administration from the Oak Table

The OakTable network is a network for the Oracle scientist, who believes in better ways of administering and developing Oracle based systems. The network is an informal organisation, which will get together at various Oracle events, for instance the Database Forum run by Miracle every year in Denmark, OracleWorld events and EOUG forums.

Expert Oracle Practices: Oracle Database Administration from the Oak Table book, Written by By Melanie Caffrey, Pete Finnigan, Randolf Geist, Alex Gorbachev, Tim Gorman, Connie Green, Charles Hooper, Jonathan Lewis, Niall Litchfield, Karen Morton, Robyn Sands, Jože Senegačnik, Uri Shaft, Riyaj Shamsudeen, Jeremiah Wilton, Graham Wood

This book explains Ideas/Practices 15 Chapters about Oracle Database. (Preview)

Book Contents

I DBA Fundamentals
1 Battle Against Any Guess – Alex Gorbachev
2 A Partly-cloudy Future – Jeremiah Wilton
3 Developing A Performance Optimization Method – Connie Green, Graham Wood, Uri Shaft
4 The DBA as Designer – Melanie Caffrey

II Network and Operating Systems
5 Running Oracle on Windows – Niall Litchfield

III SQL and PL/SQL
6 Managing SQL Performance – Karen Morton
7 PL/SQL and the CBO – Joze Senegacnik

IV Performance Optimization Methods
8 Understanding Performance Optimization Methods – Charles Hooper & Randolf Geist
9 Choosing a Performance Optimization Method – Randolf Geist & Charles Hooper

IV Operational Concerns
10 Managing the Very Large Database – Tim Gorman
11 Statistics Collection and Creation – Jonathan Lewis

V Troubleshooting
12 Troubleshooting Latch Contention – Riyaj Shamsudeen
13 Measuring for Robust Performance – Robyn Sands

VI Security
14 Securing Users – Pete Finnigan
15 Securing Data – Pete Finnigan

I believe this book is helpful for DBA to learn about ideas and practices.

Reference

ALTER SYSTEM SET.. SID='FUN'

Just fun today, I altered to change some parameter on Database by using SID='sid'
Example:

SYS@db1> show parameter pga_aggregate_target;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 200M

SYS@db1> select instance_name from v$instance;

INSTANCE_NAME
----------------
db1

1 row selected.

Used upper characters:(Be able to alter, but parameter not change... because wrong SID)

SYS@db1> alter system set pga_aggregate_target=100M sid='DB1';

System altered.

SYS@db1> show parameter pga_aggregate_target;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 200M

Used sid='FUN': (Be able to alter, but parameter not change... because wrong SID)

SYS@db1> alter system set pga_aggregate_target=100M sid='FUN';

System altered.

SYS@db1> show parameter pga_aggregate_target;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 200M

Used sid='select instance_name from v$instance': (Be able to alter and parameter changed)

SYS@db1> alter system set pga_aggregate_target=100M sid='db1';

System altered.

SYS@db1> show parameter pga_aggregate_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 100M

So, Checked on v$spparameter and in spfile file -)

SYS@db1> select SID, NAME, VALUE from v$spparameter where name like '%pga%';

SID NAME VALUE
----- ------------------------------ ------------------------------
db1 pga_aggregate_target 104857600
FUN pga_aggregate_target 104857600
* pga_aggregate_target 209715200
DB1 pga_aggregate_target 104857600

SYS@db1> create pfile='/tmp/pfile' from spfile;

File created.

$ grep pga_aggregate_target /tmp/pfile

*.pga_aggregate_target=209715200
DB1.pga_aggregate_target=104857600
FUN.pga_aggregate_target=104857600
db1.pga_aggregate_target=104857600

Reset Wrong SID:

SYS@db1> alter system reset pga_aggregate_target sid='FUN';

System altered.

SYS@db1> alter system reset pga_aggregate_target sid='DB1';

System altered.

This taught me check SID before... before ALTER SYSTEM.. SID='sid'. read more ALTER SYSTEM
The SID clause is relevant only in a Real Application Clusters environment. This clause lets you specify the SID of the instance where the value will take effect.
- Specify SID = '*' if you want Oracle Database to change the value of the parameter for all instances.
- Specify SID = 'sid' if you want Oracle Database to change the value of the parameter only for the instance sid. This setting takes precedence over previous and subsequent ALTER SYSTEM SET statements that specify SID = '*'.

Just fun -)

ORA-00600: internal error code, arguments: [17069]

Found this error, after changed meta data on TABLES and recompiled many stored procedures.

Errors in file /oracle/admin/db/udump/db1_ora_11420.trc:
ORA-00600: internal error code, arguments: [17069], [0x08071B8D0], [], [], [], [], [], []

In trace file.

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
ORACLE_HOME = /oracle/10g
.
.
.
Unix process pid: 11420
.
.
.
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [17069], [0x08071B8D0], [], [], [], [], [], []
Current SQL statement for this session:
BEGIN :1 := PKG_TMP01.PRC_TMP01(
:2 ,
:3 )
; END;
----- PL/SQL Call Stack -----

Check PKG_TMP01 package on Database.

SQL> select object_name,object_type, status from dba_objects where object_name='PKG_TMP01';

OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ ------------------- -------
PKG_TMP01 PACKAGE VALID
PKG_TMP01 PACKAGE BODY INVALID

SQL> alter package PKG_TMP01 compile body;

Package body altered.

But still Invalid...

SQL> select object_name,object_type, status from dba_objects where object_name='PKG_TMP01';

OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ ------------------- -------
PKG_TMP01 PACKAGE VALID
PKG_TMP01 PACKAGE BODY INVALID

Check Oracle Support report Bug 4587556 (10.2.0.3), Fixed in 10.2.0.4 (Server Patch Set),11.1.0.6 (Base Release)

However, I can not patch yet, so checked Workaround: Compile packages which have a self referencing synonym explicitly from SQL before compiling any other dependant packages.
But I didn't find synonym invalid status and made script to compile all:

set lines 130
set pages 0
spool recompile.sql
select 'alter '|| object_type||' '||owner||'.'||object_name ||' compile;' from dba_objects where status='INVALID' and object_type != 'PACKAGE BODY';
select 'alter package '|| owner||'.'||object_name ||' compile body;' from dba_objects where status='INVALID' and object_type = 'PACKAGE BODY';
select 'alter '|| object_type||' "'||owner||'"."'||object_name ||'" compile;' from dba_objects where status='INVALID' and object_type != 'PACKAGE BODY' and owner='PUBLIC';
spool off;
@recompile.sql

Checked again:

SQL> select object_name,object_type, status from dba_objects where object_name='PKG_TMP01';

OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ ------------------- -------
PKG_TMP01 PACKAGE VALID
PKG_TMP01 PACKAGE BODY INVALID

Package body status was invalid. Then got idea about this bug report.

ERROR:
ORA-600 [17069] [a]

DESCRIPTION:
Failed to pin a library cache object after 50 attempts.

ARGUMENTS:
Arg [a] Library Cache Object Handle.

This bug is about library cache, So Flushed shared pool and recompiled again.

SQL> alter system flush shared_pool;

SQL> alter package PKG_TMP01 compile body;

Package body altered.

SQL> select object_name,object_type, status from dba_objects where object_name='PKG_TMP01';

OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ ------------------- -------
PKG_TMP01 PACKAGE VALID
PKG_TMP01 PACKAGE BODY VALID

That's a good thing to solve this issue. Perhaps I recompiled synonyms refer this package before. And flush shared pool helped database be able to recompile.

My Oracle Support on Firefox

I often use Oracle Support (Flash) to find out knowledges and oracle bugs. I use Firefox version 3.6

and see blank or... on right frame.

I have to click "Right" and then choose "Open Item in New Window"

Result...

Or Click some...Not Sure what wrong???
However... click "Right" and then choose "Open Item in New Window" help me -)

Oracle 11g Streams Implementer's Guide Book

Oracle Streams enables the propagation and management of data, transactions and events in a data stream either within a database, or from one database to another. We can learn Oracle Streams Concepts & Administration from Oracle Docs.

I mention a book titled Oracle 11g Streams Implementer's Guide written by Ann L. R. McKinnell, Eric Yen. They have combined their experience of working. They wrote 9 chapters, Example: Plot Your Course: Design Considerations, Prepare the Rafts and Secure Your Gear: The pre-work before configuring Oracle 11g Streams and ... (Table of Contents)

This book is a excellent tutorial for the Design and the Implementation Oracle Streams. We can find out Oracle Streams Concepts from Oracle Docs, But this book gives us know what before start building.
If Someone will be implementing Oracle Streams, who should read this book before. I believe this book is helpful.

What we will learn from this book:
- Clear, concise explanations of Oracle Streams components and concepts
- Up-front design considerations and techniques – know what you need BEFORE you start building
- Configure both Source and Target databases to support Streams capture, propagation, and apply processes
- Set up a Single-Source Configuration using the Enterprise manager DB Console to divert flow in one direction and apply its concepts to a multi-master, or N-Way Replication environment configuration
- Solidify your Streams concepts through basic implementation examples that can then be applied to more complicated systems
- Learn about popular advanced features of Oracle Streams including Subsetting, Tags, Rules, and Rule based transformations
- Discover effective and efficient troubleshooting techniques and tools