All about Database Administration, Tips & Tricks

All information is offered in good faith and in the hope that it may be of use for educational purpose and for Database community purpose, but is not guaranteed to be correct, up to date or suitable for any particular purpose. db.geeksinsight.com accepts no liability in respect of this information or its use. This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content and if notify any such I am happy to remove. Product and company names mentioned in this website may be the trademarks of their respective owners and published here for informational purpose only. This is my personal blog. The views expressed on these pages are mine and learnt from other blogs and bloggers and to enhance and support the DBA community and this web blog does not represent the thoughts, intentions, plans or strategies of my current employer nor the Oracle and its affiliates or any other companies. And this website does not offer or take profit for providing these content and this is purely non-profit and for educational purpose only. If you see any issues with Content and copy write issues, I am happy to remove if you notify me. Contact Geek DBA Team, via geeksinsights@gmail.com

PROC-26: Error while accessing the physical storage, PROT-602: ,OCR/Voting Disk fail to mount, OCR corruption

Want to know new features in 12c, 18c, 19c, 20c ??? Follow this

More than 100+ features listed here : http://db.geeksinsight.com/category/12c-database/

More than 50+ features listed here: http://db.geeksinsight.com/category/18c-database/

More than 40 features listed here: http://db.geeksinsight.com/category/19c-database/

Upcoming 20c features : http://db.geeksinsight.com/category/20c-database/

Issue: CRSD not coming up, the other resource until CRS, (need CRS startup sequence, see here) CRSD is failing with error, Possible OCR corruption.

in $GI_HOME/log/<nodename>crsd.log

PROC-26: Error while accessing the physical storage,

Environment:-

GI Version: 11.2.0.2, RDBMS Version: 11.2.0.2 , Two Node RAC on Linux

We are not using ASMLIB, using block devices directly i.e /dev/mapper in asm_diskstring.

First lets check the environment,

Environment Checks:-

Verify OCR Diskgroup & its location

[root@racp001 bin]# ./ocrcheck
Errors in file :
ORA-27091: unable to queue I/O
ORA-15081: failed to submit an I/O operation to a disk
ORA-06512: at line 4
Continue reading PROC-26: Error while accessing the physical storage, PROT-602: ,OCR/Voting Disk fail to mount, OCR corruption

Quiz Post #2: Why you need to increase the cache size for sys.audsess$ sequence in RAC

Why you need to increase the cache size of sys.audsess$ sequence in RAC?

Answer:-

Background:- In oracle database every session must have an AUDSID (v$session.audsid) when the session spawns/created in database, this number is derived from a sequence called sys.audsess$ where the cache option set to 20 and noorder.

When you have an database that can provide logon storms , for example many number of users logged in first time during business start may create lot of logon storms to the database directly and these sequence value in the dictionary cache will be exhausted and it has to parse again and again another set 20 sequences cache values, which need an enqueue (SQ Lock) Enq: SQ for a long time or most of the time depend on the logon storms received.

In RAC this will become worse since the library cache/dictionary cache are global, both will have wait for this enqueue and cause dead lock or hang situation.

In order to mitigate the issue, we have to give cache clause for sequence audsess$ the bigger number especially for RAC instances (with noorder clause), say about 1000.

-Hope this helps

11gR2 RAC: Important Trace Directories & Log Locations

All,

Please find the structured 11gR2 GI Clusterware log locations and its relevancy.

Directory structured (Source: Oracle Documentation)

Log Locations

Continue reading 11gR2 RAC: Important Trace Directories & Log Locations

Replication: does Streams works after role transition i.e switchover or failover

Hello,

Today I have got a call containing couple of questions on streams replication in a standby environment especially when the streams is on downstream capture database.

Here are the questions & my answers to it.

Environment:-

Primary or Source: -10GR2 RAC with 2 node

Standby: Physical Standby

Streams: Downstreams capture database in different server

Behavior of streams at the time of shifting from Primary to Standby and vice versa :

a ) In case if Primary is shifted to Secondary(stand by) DB, the streams DB is to be replicated fully (not from point where it stopped)

This is called switchover/Failover or the role transitions. In your streams environment as the changes captured in downstream database (different database) with minimal changes or the pre-requisite this transitions can be performed seamlessly and the streams continue to work.

Procedure:-

Pre-requisities:- Continue reading Replication: does Streams works after role transition i.e switchover or failover

11g Grid Infrastructure (RAC): CRS Startup Sequence

This is about to understand the startup sequence of Grid Infrastructure daemons and its resources in 11gR2 RAC.

In 11g RAC aka Grid Infrastructure we all know there are additional background daemons and agents, and the Oracle documentation is not so clear nor the other blog.

For example:- I have found below diagram from metalink which is very confusing.

Continue reading 11g Grid Infrastructure (RAC): CRS Startup Sequence

11g Feature: Audit Trail Purging, No more custom scripts

Dear All,

Prior to 11g, in order to purge the audit trails we have to write a custom script and run in a cron or control M scheduler job or a DBMS_SCHEDULER, where in

From 11g 11.1.0.7 onwards Oracle provides a package DBMS_AUDIT_MGMT to manage the audit trails.

Additionally, This package can also be deployed in 10.2.0.3 databases via patch as mentioned in note 731908.1.

Example:-

Automated Audit trail Purging

Continue reading 11g Feature: Audit Trail Purging, No more custom scripts

Quiz Post #1 : SQL Magic

Q: is it my possible to make my query to change from "select 'what is my name' from dual" to "select 'you'r Geek DBA' from dual" during execution.

A: this feature is called "query equivalence".

query equivalence is declared using the dbms_advanced_rewrite.declare_rewrite_equivalence procedure, and uses the syntax:

dbms_advanced_rewrite.declare_rewrite_equivalence(declaration_name,source_statement,target_statement);

Scripts: Find out a expensive sql statements from AWR Top Events with just three small scripts

A typical day starts like this.

1) Application team complained about slowness in their job

2) DBA looking into this

No clues or details from App team saying this particular sql is that we are running but the response time of the database server is bit slow, so obvious that app team reported slowness.

This time I have chosen event based troubleshooting approach from AWR whether to see am I able to drill down the issue.

What I started first is, lets take a closer look at top classes in the database that is at the moment.

set lines 1000
set pages 1000
select wait_class_id, wait_class, count(*) cnt
from dba_hist_active_sess_history
where snap_id between 5205 and 5208
group by wait_class_id, wait_class
order by 3;

WAIT_CLASS_ID WAIT_CLASS CNT
------------- ------------------ ----------
Continue reading Scripts: Find out a expensive sql statements from AWR Top Events with just three small scripts

ORA-15036: disk ‘string’ is truncated, ASM disks group DATA is not mounting

Hello,

Another issue came up today!!!

Issue:- ORA-15036: disk 'string' is truncated The size of the disk, as reported by the operating system, was smaller than the size of the disk as recorded in the disk header block on the disk.

Apparently the concerned disk group is not mounting, unfortunately this contains our OCR Disks (okay, bigger problem)

Sequence of actions or the issues encountered:-

1) DBA found that disk partitions are not visible, passed to unix to fix the issue

2) Unix tried many alternatives and involved Novell (Suse Linux) as well , and Novell wisely rewritten the disk headers and they are visible now

3) Passed to DBA, now the DBA turn to mount the diskgroups

4) Got hit with ORA-15036, and CRSD failing to come up , parallely its resources as well.

5) Raised SR, Oracle suggested to recreate and restore the diskgroups data

6) Novell, said they can't do anything.

Strucked ....... and Corner end..... what else?

Continue reading ORA-15036: disk ‘string’ is truncated, ASM disks group DATA is not mounting

Basics: What is RAID?

Hello,

A Simple readable format about "What is RAID?"

RAID (Redundant Array of Independent Disks). A collection of disk drives that offers increased performance and fault tolerance. There are a number of different RAID levels. The three most commonly used are 0, 1, and 5:

Level 0: striping without parity (spreading out blocks of each file across multiple disks).
Level 1: disk mirroring or duplexing.
Level 2: bit-level striping with parity
Level 3: byte-level striping with dedicated parity. Same as Level 0, but also reserves one dedicated disk for error correction data. It provides good performance and some level of fault tolerance.
Level 4: block-level striping with dedicated parity
Level 5: block-level striping with distributed parity
Level 6: block-level striping with two sets of distributed parity for extra fault tolerance
Level 7: Asynchronous, cached striping with dedicated parity

Hope this helps!!!!

All about Database Administration, Tips & Tricks

New Features for DBA’s

Subscribe to Posts by Email

Subscriber Count

Disclaimer

Recent Posts

Categories

Archives

Pages

PROC-26: Error while accessing the physical storage, PROT-602: ,OCR/Voting Disk fail to mount, OCR corruption

Quiz Post #2: Why you need to increase the cache size for sys.audsess$ sequence in RAC

11gR2 RAC: Important Trace Directories & Log Locations

Replication: does Streams works after role transition i.e switchover or failover

11g Grid Infrastructure (RAC): CRS Startup Sequence

11g Feature: Audit Trail Purging, No more custom scripts

Quiz Post #1 : SQL Magic

Scripts: Find out a expensive sql statements from AWR Top Events with just three small scripts

ORA-15036: disk ‘string’ is truncated, ASM disks group DATA is not mounting

Basics: What is RAID?

New Features for DBA’s

Follow Me!!!

Subscribe to Posts by Email

Subscriber Count

Disclaimer

Recent Posts

Categories

Archives

Tags

Pages