Wednesday, March 28, 2012

How to move controlfile to different diskgroup in ASM?

Question if quite interesting and though of sharing it with you. Here what i did, my old diskgroup is +DATA and wanted to move to +FRA.

I did something like
SQL>show parameter control_files
control_files                        string      +DATA/ORCL/controlfile/current.110.293846193
 
SQL>alter system set CONTROL_FILES = '+FRA' scope=spfile
 
SQL>shutdown
rman target / 
RMAN>startup nomount
RMAN>restore controlfile  from  '/+DATA/ORCL/controlfile/current.110.293846193';
RMAN>alter database mount;
RMAN> alter database open;

Friday, March 23, 2012

Set autotrace (SP2-0612: Error generating AUTOTRACE EXPLAIN report)

Wanted to give permission of Auto Trace of other User for tuning purposes?

SQL> create role plustrace;

Role created.

SQL> grant select on v_$sesstat to plustrace;

Grant succeeded.

SQL> grant select on v_$statname to plustrace;

Grant succeeded.

SQL> grant select on v_$mystat to plustrace;

Grant succeeded.

SQL> grant  plustrace to test with admin option;

Grant succeeded.

SQL> conn test/test
Connected.
SQL> set autotrace traceonly explain
SQL> select * from T1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  4000 |   968K|    44   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T1   |  4000 |   968K|    44   (0)| 00:00:01 |
--------------------------------------------------------------------------

There is a sql script in "D:\app\rnagi\product\11.2.0\dbhome_1\sqlplus\admin\plustrce.sql" which we can execute to create this role and grant role to any user who need trace.
Enjoy!

How many rows i had in my table?? History of row count

This question often comes to developer/DBA's mind that how many rows a table had in past. These type of information is quite handy when explain plan change happens. As we know change in data volume can also change the explain plan.
So keeping the history and knowing table growth trend one can know when a particular plan can change.

Whenever stats are gathered on table, its old stats like rowcount,numbers of blocks, average row len etc are flushed to SYS.WRI$_OPTSTAT_TAB_HISTORY table.
So its bit easy to get the history of rowcount for any table.

Like
SQL> select n.object_name as T,o.ROWCNT,o.BLKCNT,to_char(o.SAVTIME,'hh/dd/mm HH:
MI') as time from dba_objects n,WRI$_OPTSTAT_TAB_HISTORY o where o.obj#=n.object
_id and n.object_name='T1';

Table    ROWCNT     BLKCNT TIME
---------- ---------- --------------
T1
              4000        155 12/23/03 12:13

T1
              4000        155 12/23/03 12:15

T1
                                     10/29/02 10:01

Needless to say this view can have zero rows if stats where on gathered at all on table.

Enjoy!

Want to know when Statistics on table where gathered Last time and its history?

DBA_TAB_STATS_HISTORY is view which will tell you the time when gather stats job updated the satistics on table. This keep all history for this activity and is very usefull piece of information.

SQL> select to_char(STATS_UPDATE_TIME,'yyyy/mm/dd HH:MI') from DBA_TAB_STATS_HIS
TORY where table_name='T1';
TO_CHAR(STATS_UP
---------------------------------------------------------------------------
2012/02/29 10:01

SQL> exec dbms_stats.gather_table_stats('TEST','T1');

SQL> select to_char(STATS_UPDATE_TIME,'yyyy/mm/dd HH:MI') from DBA_TAB_STATS_HIS
TORY where table_name='T1';
TO_CHAR(STATS_UP
----------------
2012/03/23 12:13
2012/02/29 10:01

SQL> exec dbms_stats.gather_table_stats('TEST','T1');

PL/SQL procedure successfully completed.

SQL> select to_char(STATS_UPDATE_TIME,'yyyy/mm/dd HH:MI') from DBA_TAB_STATS_HIS
TORY where table_name='T1';

TO_CHAR(STATS_UP
----------------
2012/03/23 12:13
2012/03/23 12:15
2012/02/29 10:01


Thursday, March 22, 2012

WARNING: failed to online diskgroup resource ora.DATA.dg (unable to communicate with CRSD/OHASD)

Hi,

Starting from 11gR2 if we look at asm alert.log we sometimes notice that "WARNING: failed to online diskgroup resource ora.DATA.dg (unable to communicate with CRSD/OHASD)" appear quite on-and-off.
Now what does this mean?


I did some googling and found that in 11R2 that " 11gR2 ASM does not depend on CRSD anymore; rather if OCR is located on ASM, ASM will start before CRSD while clusterware is coming up, and stops after CRSD while clusterware is going down. Due to the new startup sequence, ASM can not update diskgroup resource status in CRSD either during clusterware startup or shutdown, thus above message is logged"

How interesting?
  
So simply we can ignore these warning message if we are on 11gR2

Will difference in size of LUNs asm disks in DiskGroup will affect the Performance?

Hi,

This question was appeared in forums.oracle.com one day and i tried the OP to explain how the performance can affect or become visible when we add LUN to already existing diskgroup with different in size. However i didn't produce any practical example to it but it was all logical and theoretical.

Here is the like - https://forums.oracle.com/forums/thread.jspa?threadID=2363635&tstart=0

srvctl modify service - TAF

What if in RAC database i want to implement TAF (Transparent Application Failover) to already existing setup?
Normally before setup people setup the TAF by configuring the tnsnames.ora in client machine to take the benefit of TAF. This was old method and not very convenient.
 However we can configure without modifying tnsnames.ora of client machine by modifying our SERVICE

srvctl modify service -d orcl -s orcldb -r node1,node2 -P basic -e select -m basic -z 10 -w 2


Thats it!


-

d = database name

-s = service name we want to modify

-r = preferd node list where this service is running

-a = available nodes - this we haven't given

-P = is type which can be BASIC | NONE | PRECONNECT

-e = is type i.e SELECT or SESSION

-z = Failover restries

-w = Failover delay

-m = defines the method of TAF.

Reference MOS note -

11gR2(11.2) RAC TAF Configuration for Admin and Policy Managed Databases [ID 1312749.1]















How to Grant on Data Dictionary v$ views

There can be requirement when we as a DBA wanted to give access to some v$session or v$process or any other dictionary view to other users for any reason.

We can do it in following way

SQL> grant select on v_$SESSION to TEST;

Grant succeeded.

If we do

SQL> grant select on v$session to TEST;
grant select on v$session to TEST
                *
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

Now why second command failed?

Because v$ is a synonym and v$_ is a view. So we cannt give grants to synonym.

See below
SQL> select object_name,object_type from dba_objects where object_name='V$SESSIO
N';

OBJECT_NAME
--------------------------------------------------------------------------------

OBJECT_TYPE
-------------------
V$SESSION
SYNONYM


SQL> select object_name,object_type from dba_objects where object_name='V_$SESSI
ON';

OBJECT_NAME
--------------------------------------------------------------------------------

OBJECT_TYPE
-------------------
V_$SESSION
VIEW

So in general we can give select grants to view i.e. v_$ not to synonym v$

Tuesday, March 20, 2012

Minact-Scn Master-Status: Grec-Scn Messages In Trace File

Hi,

Version 11.2.0.2

I came across a situation when MMON process can generate Hugh traces in backgroud dump directory. Reason was unknown why mmon process is creating lots of traces..
Following metalink note explains reason for it.
ID 1361567.1

Tracefile contents are:

*** 2012-03-19 15:58:43.884
minact-scn master-status: grec-scn:0x0000.019ffe75 gmin-scn:0x0000.019ffe75
gcalc-scn:0x0000.019ffe75
minact-scn master-status: grec-scn:0x0000.01a00095 gmin-scn:0x0000.01a00095
gcalc-scn:0x0000.01a00095
 
*** 2012-03-19 16:08:47.655
minact-scn master-status: grec-scn:0x0000.01a0020c gmin-scn:0x0000.01a0020c
gcalc-scn:0x0000.01a0020c
 
Coming back to situation:
In 11g a new feature was introduced, that is called "active scn optimization feature"
.This feature was introduced to improve the "Delayed blocks cleaout operation".
 
Now what is block clean out operation and why it had issues prior to 11g?
Well following blog explain what is Block cleanout operation and how it happen actually.
"http://hemantoracledba.blogspot.in/2008/10/delayed-block-cleanout.html"
 
Solution:
Solution is to disable this feature manually by setting 
alter system set "_enable_minscn_cr"=false scope=spfile;

So if you are not having high batch job operation, disabling this wont have any effects to your database.
However if you do have high batch job and there are blocks requirements for next operation
then second operation maybe delayed a bit in doing Block cleanouts.

Sunday, March 11, 2012

srvctl modify instace

Hi there

During y regular posting in forums.oracle.com, i came across as interesting question rather i never thought that this could happen and can be done.
The question was something like this:
I have two node RAC on oracle 11gR2 Linux. RAC database name is RACDB and node names are Linux1 and Linux2.
I want to run RACDB1 on node Linux2 and RACDb2 on node Linux1, how can we do?

well my answer was pretty simple and i gave something like this

An example of this command to relocate a database instance is:
srvctl modify instance -d crm -i crm1 -n my_new_node 
 
The following example of this command establishes a dependency between an ASM instance and a database instance:
srvctl modify instance -d crm -i crm1 -s asm1

Hope this help
 

Saturday, March 10, 2012

ORA-0600: internal error code, arguments: [kksfbc-new-child-thresh-exceeded], [], [], [], [], [], [], [], [], [], [], []

Yesterday my client got frequent ORA-0600: internal error code, arguments: [kksfbc-new-child-thresh-exceeded], [], [], [], [], [], [], [], [], [], [], [] error in their alert.log. 
Database version - 11.1.0.7
PSU - 11.1.0.7.2
OS - Linux  5.2
Cluster - 3 node 11.1.0.7

This database was recently migrated from 10.2.0.4 database version to 11.1.0.7. And since after that they started seeing ORA-0600 error in alert.log in all 3 instances. However this error went unnoticed for few days.

Yesterday client came up with issue that one of their node is not responding, so when we check alert.log we found many ORA-4031: unable to allocate 32 bytes of shared memory ("shared pool", in alert.log. We were not able to login into database and neither users. So due to SLA cleint reboot this node and everything became fine. Then we checked in AWR reports for any kind of contention in shared pool or library cache. We did find contention in library cache latch and there were about 5 to 6 SQL statements which were having huge parse calls somewhere around 5000 per executions. But all these were background queries.
We could have increased the shared pool size after seeing ORA-4031 but we did a little more research.

We cheked alert.log in all 3 instance, and we found that ORA-0600 occurred few hours before ORA-4031.
So we first concentrated on ORA-0600 and lokking for it cause. When we checked metalink the very first document which we come across was "ORA-600 [kksfbc-new-child-thresh-exceeded] [ID 285704.1]"

This doc shows that 11.1.0.7 is exposed to two bugs i.e. 8865718 and 7626014
These bugs are related to MView refresh. So whenever MView is refreshed these bugs are likely to hit. Bug explains that while doing MView refresh it create huge number to recursive child cursor  while are not shareable. 
We dont know the reason for this but what actually what we conclude that ORA-0600 and ORA-4031 were related to each other. AS bug explains that they create huge number of recursive child cursor which were non shareable, library cache latch or library cache contention were likely to come and which caused very less memory in library cache for other cursors.

We might not have succeeded in increasing the shared pool size unless and untill we fix these bugs. So we are no planning to apply PSU 11.1.0.7.10 to this database and looking for its stability.

Thanks


Saturday, March 3, 2012

Oracle RDBMS and Grid 11.2.0.3.1 Download location

Hi,

 I regularly discuss about oracle databases at OTN discussion form. During discussion,i observed that most of oracle ppl dont know from 11gR2 each patchset is now full release. For e.g if you want your database to be 11.2.0.3 then you dont need to first install 11.2.0.1 and then apply 11.2.0.3 patchset on top of it.

But 11.2.0.1 is directly downloadable from eDelivery site and from OTN site and 11.2.0.3 is not. So below is the Metalink note from where you can download 11.2.0.3 database. Remember you can directly download and install 11.2.0.3 to your server if you dont have 11.2.0.1 or 11.2.0.2.

However if you already have 11.2.0.1 or 11.2.0.2 and want to upgrade to 11.2.0.3, then install 11.2.0.3 into new oracle home. This is called out-of-place upgrade. From 11gR2 every patchset is out-of-place upgrade. If you install 11.2.0.3 in existing oracle home, then installer will give you warning about this.
Some customer still want to upgrade to 11.2.0.3 and want to install this in existing 11gR2 home. This is still possible(using-detachHome while running runInstaller).The reason for this case might be customer want to safe space on server etc.

As of now 11.2.0.3.1 i.e. PSU1 is current PSU available on 11.2.0.3.

11.2.0.3 download location from MOS - 11.2.0.3 Patchset Location For Grid Infrastructure And RDBMS. [ID 1362393.1]

Hope this help