Oracle World

Driving Site Hint is not working with Insert statement

2017-09-08T08:49:00.001-07:00

We had recently came across a situation that, Driving site Hint is not working with insert statement. Please see the test case below. Here you can see that select statement is working fine with hint and Driving site Hint is not working as expected when we add insert statement at the beginning.

Select statement with hint(Working fine as expected)

Execution Plan

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

Plan hash value: 1144483799

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

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

| 0 | SELECT STATEMENT REMOTE| | 160M| 30G| | 15M (1)| 00:09:57 |

| 1 | HASH GROUP BY | | 160M| 30G| 33G| 15M (1)| 00:09:57 |

|* 2 | HASH JOIN RIGHT OUTER| | 160M| 30G| 18M| 8120K (1)| 00:05:18 |

| 3 | TABLE ACCESS FULL | MASTER | 467K| 12M| | 9744 (1)| 00:00:01 |

|* 4 | HASH JOIN | | 160M| 26G| 121M| 6687K (1)| 00:04:22 |

| 5 | REMOTE | JOBS | 1874K| 100M| | 17789 (1)| 00:00:01 |

|* 6 | TABLE ACCESS FULL | DETAILS_MASTER | 304M| 33G| | 4777K (1)| 00:03:07 |

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

In the above Execution plan you can see that Table "JOBS" referred as REMOTE and this query complete in 5 Minutes.

Insert statement explain plan

Plan hash value: 1159224495

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

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

| 0 | INSERT STATEMENT | | 160M| 52G| | 19M (1)| 00:13:00 |

| 2 | HASH GROUP BY | | 160M| 52G| 55G| 19M (1)| 00:13:00 |

|* 3 | HASH JOIN RIGHT OUTER | | 160M| 52G| 57M| 8000K (1)| 00:05:13 |

| 4 | REMOTE | MASTER | 467K| 52M| | 6235 (1)| 00:00:01 |

|* 5 | HASH JOIN | | 160M| 34G| 76M| 6144K (1)| 00:04:01 |

| 6 | TABLE ACCESS FULL | JOBS | 1874K| 55M| | 27801 (1)| 00:00:02 |

| 7 | REMOTE | DETAILS_MASTER | 304M| 56G| | 3061K (1)| 00:02:00 |

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

In the above execution plan, you can see that Table "DETAILS_MASTER" referred as REMOTE table and entire insert is taking almost 1 hour to complete their execution.

Comparison between both execution plan, we identified that Driving site hint is not working as expected with insert statement.

I have decided to give a PL\SQL block to resolve this issue, Also added bulk collect + FOR ALL (Improve the Bulk Insert). Please see the below details.

DECLARE

TYPE ARRAY IS TABLE OF alltmrsmry%ROWTYPE;

CURSOR get_data

l_data ARRAY;

BEGIN

OPEN get_data;

LOOP

FETCH get_data

BULK COLLECT INTO l_data LIMIT 2000;

FORALL i IN 1 .. l_data.COUNT

INSERT INTO SUMMARY_TMP

VALUES l_data (i);

EXIT WHEN get_data%NOTFOUND;

END LOOP;

CLOSE get_data;

COMMIT;

END;

Select query with Driving hint is working fine with cursor and insert 2000 rows each. Entire PL\SQL block (SELECT+INSERT) is completing in 10 minutes now.

Customer is Happy and also our team.

2016-07-04T20:29:00.003-07:00

Are we getting different plan by using bind variable for an SQL_ID?

2016-07-04T20:29:00.002-07:00

I wonder why are we getting different plan for an SQL_ID while using bind variable. I have verified first plan is going for a full table scan and second plan is going for an index read instead of Full table scan.

Did Optimizer played a bad game again? No... Please do not blame optimizer every time J. There is a chance of skewed distribution of values in predicate column .

If Index histogram of the predicate column has an accurate information about the skewed distribution, we will get different plan sometime with respect to the predicate which we given.

How it will work ?

Adaptive Cursor is playing a major role for this scenario.

This feature will decide , if plan has to be recalculated when the bind variable values changes. Who is helping to make a good plan with respect to the bind variable value?

of course, it is Index Histogram , we should have accurate index histogram details otherwise they cannot make a decision for recalculation(Wrong Estimation of Cardinality) and again it will end up with bad performance

by using old cached execution plan.

Let me give a quick and simple test.

We have a table called JOB_MASTER and which is having two column named JOB_ID, JOB_CODE

SQL> DESC JOB_MASTER;

Name Null? Type

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

JOB_ID NUMBER(10)

JOB_CODE VARCHAR2(10)

And there is also a non-unique index in the JOB_CODE column.

Please see the query passing from application side against this table.

SQL_ID: cyt7c564y3nf6

SQL_TEXT :

select max(JOB_ID) from JOB_MASTER where JOB_CODE=:B1;

I checked AWR history of this query and found we are getting different plan for this particular SQL for most of the time.

PLAN_HASH_VALUE SQL_ID CHILD_NUMBER

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

4293876030 cyt7c564y3nf6 0

1405390124 cyt7c564y3nf6 1

Plan with Table Scan :

Execution Plan

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

Plan hash value: 1405390124

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

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

| 0 | SELECT STATEMENT | | 1 | 7 | 3923 (3)| 00:00:48

| 1 | SORT AGGREGATE | | 1 | 7 | |

|* 2 | TABLE ACCESS FULL| JOB_MASTER | 4196K| 28M| 3923 (3)| 00:00:48 =====> Full Table Scan

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

3 - access("JOB_CODE"='DBA')

Second Plan with Index Read:

Execution Plan

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

Plan hash value: 4293876030

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

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

| 0 | SELECT STATEMENT | | 1 | 7 | 16 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 7 | | |

| 2 | TABLE ACCESS BY INDEX ROWID| JOB_MASTER | 2311 | 16177 | 16 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | TEST_INDX | 2383 | | 7 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

3 - access("JOB_CODE"='DEVELOPER')

Why do we get this different plan, is this column having Skewed distribution of values?

Let me have a look,

SQL> select count(*),JOB_CODE from JOB_MASTER group by JOB_CODE;

COUNT(*) JOB_CODE

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

8388608 DBA

4608 DEVELOPER

Then we found its highly skewed values in the column JOB_CODE and we can see very less percentage of rows having "DEVELOPER" compared to "DBA" value in this column

There is a catch, It’s a thumb rule oracle following , Oracle will decide to go with full table scan when the bind variable (:B1) is "DBA" and will decide to go with index read

when bind variable (:B1) is "DEVELOPER"

It is really costly when oracle use full table scan while access predicate is "DEVELOPER" and on the other hand , when the access predicate is "DBA", index read is useless.

If the plan need to be recalculated by this way, cursor denoted as Bind Sensitive.

SQL> select plan_hash_value,sql_id,child_number,IS_BIND_SENSITIVE from gv$sql where sql_id='cyt7c564y3nf6';

PLAN_HASH_VALUE SQL_ID CHILD_NUMBER I

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

4293876030 cyt7c564y3nf6 0 Y

My request is , please do not scare, if we get multiple plan while using bind variable until we get a performance degradation over there. Please make sure to test SQL Profile couple of time to verify the performance improvement before getting to default category(Live).

Thanks,

Jyothish B

Fixing Archive Gap in Standby From Archive Log Backup

2016-03-20T12:01:00.000-07:00

Check the archive log gap in standby

SQL> select * from v$archive_gap;

Suppose you have archive gap from 2940 to 2954. We are going to restore Archive files from Backup in the primary.

RMAN> run
2> {
3> restore archivelog from logseq 2940 until logseq 2954;
4> }

This will restore the archive logs from Backup to Disk.

Note : If you are using SBT for taking Backups, Please add appropriate details in the script, Example is referring Disk Backups.

Once the archive restore completed, you can use below command in standby.

SQL>RECOVER AUTOMATIC STANDBY DATABASE ;

# This will automatically recover your standby using archive files which is created by the restore.

SQL PLAN Fixing Using SQLT

2016-03-20T11:50:00.001-07:00

Tune Bad SQL Using SQLT

We are getting a good plan and bad plan for a sql. In production we have bad plan and getting very bad result so we are planning to export good plan from non production to production.
SQL history of SQL ID : 7yq9pgtsundq0
col begin_time for a25
col end_time for a11
col inst for 99999
col snapid for 999999
set lines 200
set pages 20000
select snap_id snapid,
(select substr(BEGIN_INTERVAL_TIME,1,18)||' '||substr(BEGIN_INTERVAL_TIME,24,2) from dba_hist_snapshot
b where b.snap_id=a.snap_id and a.INSTANCE_NUMBER=b.INSTANCE_NUMBER) begin_time
,(select substr(end_INTERVAL_TIME,11,8)||' '||substr(end_INTERVAL_TIME,24,2) from dba_hist_snapshot b
where b.snap_id=a.snap_id and a.INSTANCE_NUMBER=b.INSTANCE_NUMBER) end_time
,INSTANCE_NUMBER inst , PLAN_HASH_VALUE,
EXECUTIONS_DELTA Executions,
ROWS_PROCESSED_DELTA rows1,
round( CPU_TIME_DELTA /1000000,0) cpu_time,round(IOWAIT_DELTA /1000000,0) io_wait,
round( ELAPSED_TIME_DELTA /1000000,0) elapsed
from sys.wrh$_sqlstat a where sql_id in('7yq9pgtsundq0')
order by snap_id, INSTANCE_NUMBER;

Above query will help you to get a good plan and bad plan from the history with respect to the SNAP_ID

Example

SNAPID BEGIN_TIME END_TIME INST PLAN_HASH_VALUE EXECUTIONS ROWS1 CPU_TIME IO_WAIT ELAPSED

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

65849 16-FEB-16 09.00.07 AM 10.00.27 AM 1 857637189 9513 9512 2501 2 2539 =====

65850 16-FEB-16 10.00.27 AM 11.00.48 AM 1 857637189 13378 13378 3469 0 3519

65851 16-FEB-16 11.00.48 AM 12.00.08 PM 1 857637189 2568 2569 649 0 660

65855 16-FEB-16 03.00.10 PM 04.00.31 PM 1 857637189 3986 3985 1010 0 1023

65856 16-FEB-16 04.00.31 PM 05.00.51 PM 1 857637189 6042 6043 1520 0 1539 BAD PLAN

65875 17-FEB-16 10.58.22 AM 11.15.53 AM 1 1673898079 11271 11271 928 4 947

65876 17-FEB-16 11.15.53 AM 11.30.58 AM 1 1673898079 10955 10955 883 0 896

65877 17-FEB-16 11.30.58 AM 11.45.03 AM 1 1673898079 10207 10207 824 0 836

65878 17-FEB-16 11.45.03 AM 12.00.08 PM 1 1673898079 11115 11115 882 0 896

65879 17-FEB-16 12.00.08 PM 12.06.49 PM 1 1673898079 4029 4030 318 0 321 ======

65880 17-FEB-16 12.06.49 PM 12.15.13 PM 1 1749449121 349619 349619 12 0 12 ======

65881 17-FEB-16 12.15.13 PM 12.30.18 PM 1 1749449121 997986 997986 34 0 34

65885 17-FEB-16 01.15.34 PM 01.30.39 PM 1 1749449121 690501 690501 23 0 25 Good Plan

65886 17-FEB-16 01.30.39 PM 01.45.48 PM 1 1749449121 657170 657170 22 0 22

65894 17-FEB-16 03.30.28 PM 05.00.58 PM 1 1749449121 1347708 1347708 44 0 46

65901 17-FEB-16 11.00.03 PM 12.00.26 AM 1 1749449121 1347765 1347765 44 0 44 ======

From the above SQL history we identified “1749449121” is a Good plan and “1673898079” is a bad plan.

Look at the CPU Time and Elapsed Time for bad plan, should be very high percentage CPU utilization hence very high elapsed time.

So we are planning to pin the plan using sql profile.

1) Download SQLT from below link.

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=189245731844662&id=215187.1&_afrWindowMode=0&_adf.ctrlstate=

1dezgs4aky_4

2) Install SQLT as per the document.

3) Once it is installed, please follow below step to create SQL Profile.

SQL>@coe_xfr_sql_profile.sql 68gtz2b5hby5m 4270379085

It will create sql profile script in sqlt/utl coe_xfr_sql_profile_7yq9pgtsundq0_1749449121.sql

4) If you are using coe_gen_sql_profile.sql instead of coe_xfr_sql_profile.sql , you will get all possible sql profile from history and it will

includes good and bad execution plan with this profiles.

5) Once the SQL Profile script created you can able to create sql profile using the above script and check whether we have good result or bad after applying SQL profile using query mentioned earlier.

Now you have two choice, you can export the profile from non production or you can directly create the sql profile using sql which is created by sqlt.

Exporting SQL Profile

1) Create Staging table which will hold our SQL Profile.

BEGIN

DBMS_SQLTUNE.create_stgtab_sqlprof(

table_name => 'SQL_PROF_29_02_2016',

schema_name => 'JYOTHISH' );

END;

2) Packing SQL Profile content to the staging Table.

BEGIN

DBMS_SQLTUNE.pack_stgtab_sqlprof(

profile_name => 'SQL_PROF_7yq9pgtsundq0',

staging_table_name => 'SQL_PROF_29_02_2016',

staging_schema_owner => 'JYOTHISH' );

END;

3) Export Staging Table using Data pump

4) Import dump file in production.

5) Unpack SQL Profile from Staging Table.

SQL>conn JYOTHISH/xxxxxx

BEGIN

DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(

replace => TRUE,

staging_table_name => 'SQL_PROF_29_02_2016');

END;

6) Validate newly created sql profile in production.

SQL> select name,status from dba_sql_profiles where profile_name like ‘SQL_PROF_7%’;

7) We need to flush shared pool or need to wait for the Database bounce for getting new plan from SQL Profile.

Check with Application team and select a convenient way for this step.

SQL> Alter system flush shared_pool;

If you would like to Extract Hints from a PLAN. Please see the below example.

Extracting Hint from Plan

Suppose if you don’t have licence to use the Database Tuning Pack license, Please use below query to extract hints and adding these hints in SQL

query will help you improve the SQL.

select

extractvalue(value(d), '/hint') as outline_hints

from

xmltable('/*/outline_data/hint'

passing (

select

xmltype(other_xml) as xmlval

from

dba_hist_sql_plan

where

sql_id = '7yq9pgtsundq0'

and plan_hash_value = 1749449121

and other_xml is not null

)

) d

Webpage Reference : https://carlos-sierra.net/2012/04/03/what-is-sqltxplain/

RMAN Backup Status Script

2016-03-20T11:25:00.000-07:00

We can see the RMAN Backup Status using below query.

col curtime for a11
set lines 200
select substr(systimestamp,11,8) curtime,
to_char(START_TIME, 'DD hh24:mi') START_TIME,
to_char(END_TIME, 'DD hh24:mi') END_TIME,
round(MBYTES_PROCESSED/1024,6) Processed,
round(INPUT_BYTES/1024/1024/1024,6) input_GB,
round(OUTPUT_BYTES/1024/1024/1024,6) output_GB
from v$rman_status where status='RUNNING';

Thanks,

Jyothish B

RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 57889 and starting SCN of 10236493076989

2014-02-12T00:14:00.000-08:00

RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 57889 and starting SCN of 10236493076989

Cause:- Controlfile does nt have the entry of archive log 57889 starting SCN 10236493076989

Action :-
1) Take the new controlfile backup from source
2) Shutdown Target Database. startup the Database in nomount state
3) Restore the new controlfile
Terminal>rman target /
RMAN>restore controlfile from 'restore controlfile from 'controlfile_Location';
Restore has been Completed .
4) Startup the database in mount state
RMAN>sql 'alter database mount';
5) Do a incomplete recovery or complete recovery what ever you want.
RMAN>Recover database;
Restore command may failure with below error.
RMAN-03002: failure of recover command at 08/28/2013 12:23:47
RMAN-06094: datafile 1 must be restored

6)Here we can use catalog option to resolve this issue.Below example "+ORA01_D1" is the disk group name for the entire Datafiles

RMAN>catalog start with '+ORA01_D1';

Rman will show all the Datafile need to be catalogged.
See the below example
List of Cataloged Files
=======================
File Name: +ORA01_D1/MYDB/AUTOBACKUP/2013_08_28/s_824645393.441.824645393
File Name: +ORA01_D1/MYDB/DATAFILE/SYSAUX.317.824646109
File Name: +ORA01_D1/MYDB/DATAFILE/SYSTEM.318.824646105
File Name: +ORA01_D1/MYDB/DATAFILE/UNDOTBS1.328.824646110
File Name: +ORA01_D1/MYDB/DATAFILE/D12.327.824646908
File Name: +ORA01_D1/MYDB/DATAFILE/D13.326.824641013
File Name: +ORA01_D1/MYDB/DATAFILE/D14.325.8246463105
File Name: +ORA01_D1/MYDB/DATAFILE/D15.324.82464632095
File Name: +ORA01_D1/MYDB/DATAFILE/D16.323.824646987
File Name: +ORA01_D1/MYDB/DATAFILE/USERS.322.82464131
File Name: +ORA01_D1/MYDB/DATAFILE/D17.321.824646989
Now it will Prompt for YES/NO . Put 'YES' if everything looks Good.

Now Oracle identified all the Datafiles which we restored from source.

7) Switches to image copy backup of database.

RMAN> switch database to copy;

8)Recover Database

Rman>Recover Database;

again if it is failed with unknown archive log entry to the controlfile. Please do a incomplete recovery.

RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 57889 and starting SCN of 10236493076989

9)Open Database with reset log sequence

RMAN>sql 'alter database open resetlogs';

10)Check the status

SQL>select status from v$instance;
OPEN

Thanks,
Jyothish

SQL History of an Oracle Session from ASH

2014-01-17T01:29:00.001-08:00

Query help to find out the SQL history of an oracle session from Active Session History(ASH) . Here we can see the historical Data available from AWR too.

SELECT SQL_ID,PLAN_HASH_VALUE,inst_id, sum(execs) "Execution", round(sum(tot_time)/1000000,2) "Elapsed_time",
(case when sum(execs) > 0 then sum(tot_time)/ sum(execs)/1000000 else 0 end) Average_exe_Second
FROM (SELECT C.SQL_ID ,c.PLAN_HASH_VALUE,b.inst_id,
c.executions execs ,c.elapsed_time tot_time
FROM gv$ACTIVE_SESSION_HISTORY A,gv$session b,
gv$SQLAREA C where
a.sql_id=c.sql_id and b.sid=a.session_id and b.sql_id=c.sql_id and b.sid=131
union all
SELECT C.SQL_ID ,c.PLAN_HASH_VALUE ,b.inst_id ,c.executions_delta Execs,c.elapsed_time_delta tot_time
from dba_hist_active_sess_history a, gv$session b, dba_hist_sqlstat c where
a.sql_id=c.sql_id and b.sid=a.session_id and b.sql_id=c.sql_id and b.sid=12 )
GROUP BY SQL_ID,PLAN_HASH_VALUE,inst_id;

Thanks,
Jyothish

PGA Allocation for an Oracle Process

2014-01-17T01:20:00.001-08:00

Query help to find out how much PGA allocated for an Oracle Process.

SELECT
s.sid,
p.spid,
DECODE(s.program, NULL, p.program, s.program) AS "Program",
pga_used_mem,
pga_alloc_mem,
pga_max_mem
FROM gv$process p, gv$session s
WHERE s.paddr = p.addr
ORDER BY s.sid;

Thanks,
Jyothish

PGA Allocated Memory for an Oracle Session

2014-01-17T01:18:00.001-08:00

Below Query help to find out PGA Allocated Memory for an Oracle Session.

select
to_char(ssn.sid, '9999') || ' – ' || nvl(ssn.username,
nvl(bgp.name, 'background')) || nvl(lower(ssn.machine), ins.host_name) "Session_Details",
to_char(prc.spid, '999999999') pid_thread,
to_char((se1.value / 1024) / 1024, '999g999g990d00') current_size_mb,
to_char((se2.value / 1024) / 1024, '999g999g990d00') maximum_size_mb
from
gv$statname stat1,
gv$statname stat2,
gv$session ssn,
gv$sesstat se1,
gv$sesstat se2,
gv$bgprocess bgp,
gv$process prc,
gv$instance ins
where stat1.name = 'session pga memory'
and stat2.name = 'session pga memory max'
and se1.sid = ssn.sid
and se2.sid = ssn.sid
and se2.statistic# = stat2.statistic#
and se1.statistic# = stat1.statistic#
and ssn.paddr = bgp.paddr(+)
and ssn.paddr = prc.addr(+) ;

Thanks,
Jyothish

Sort Area Usage/Temp Tablespace Usage of an Oracle Session

2014-01-17T01:08:00.000-08:00

Below query will picture of Temp Usage of an Oracle Session.

set lines 132
set feedback on
set pages 99

select s.osuser, s.sid, s.serial#,s.username, s.program,
s.client_info,(sum(u.blocks)*vp.value/1024/1024/1024) AS GB_USED, a.sql_text as sql_t
from sys.gv_$session s, sys.gv_$sort_usage u, sys.gv_$parameter vp, sys.gv_$sqlarea a, sys.gv_$process b
where
s.saddr = u.session_addr and
vp.name = 'db_block_size' and
a.address = s.sql_address and
b.addr = s.paddr
group by s.osuser, s.sid, s.serial#,s.username, s.program,s.client_info, vp.value, a.sql_text;

Thanks,
Jyothish

CPU usage of an Oracle session

2014-01-17T01:04:00.003-08:00

Below query help to find out the CPU usage(Second) for a oracle session session.

select
ss.sql_id "SQL_ID",ss.username,ss.status,ss.machine,b.sql_text,b.PLAN_HASH_VALUE,
VALUE/100 cpu_usage_seconds
from
gv$session ss,
gv$sesstat se,
gv$statname sn,
gv$sql b
where
se.STATISTIC# = sn.STATISTIC#
and
NAME like '%CPU used by this session%'
and
se.SID = ss.SID and ss.sql_id=b.sql_id
and
ss.status='ACTIVE'
and
ss.username is not null
order by VALUE desc;

Thanks,
Jyothish

ORA-01105: mount is incompatible with mounts by other instances ORA-19808: recovery destination parameter mismatch

2013-12-10T00:09:00.005-08:00

Please check both db_recovery_file_dest and db_recovery_file_dest_size parameters . In my case db_recovery_file_dest_size is different . See the below details

1)Instance 1

SQL> show parameter recovery

NAME TYPE VALUE
-------------- ---------------------- ----------- ------------------------------
db_recovery_file_dest string +ARCH01_R5
db_recovery_file_dest_size big integer 50G

2)Instance 2

I have Started the Instance 2 and changed the file_dest_size to 50G

Terminal> srvctl start instance -d DBname -i Instance_name

Terminal> sqlplus /nolog

SQL>conn / as sysdba

SQL> alter system set db_recovery_file_dest_size =50G scope=both;

Then again i tried to start first instance and it is successfully started.

Terminal>srvctl start instance -d DBname -i Instance_name

Thanks,

Jyothish Balakrishnan

Schema Details

2013-11-21T00:17:00.000-08:00

Sharing a simple query occupying some useful information about Oracle schema.

select a.username,a.created,a.profile,a.default_tablespace,a.account_status,
round(sum(b.bytes/1024/1024/1024),2) as "SIZE",c.ptime as "Last_password_change"
from dba_users a,
dba_segments b,
sys.user$ c
where a.username=b.owner
and a.username='JYOTHISH' and c.name=a.username and c.name=b.owner group by username,a.created,a.profile,a.default_tablespace,a.account_status,c.ptime;

Thanks,
Jyothish

ORA-00214: control file '+DATA02_R5/fdmn1i/controlfile/current.265.799827461' version 6909395 inconsistent with file '+ARCH02_R5/fdmn1i/controlfile/current.11120.826208033' version 6909384

2013-10-19T12:19:00.000-07:00

We already know about the reason but my overconfidence spoil around 1 hr for this error in RAC environment. Simple explanation is needed here '+DATA02_R5/fdmn1i/controlfile/current.265.799827461'
scn is 6909395 and other controlfile '+ARCH02_R5/fdmn1i/controlfile/current.11120.826208033' not able to write the changes after scn 6909384. So we need to need the recreate second
controfile as well. Please follow the below steps.

1)Add the Controlfile Entry Using spfile.

C:\>sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 19 16:43:00 2013

Copyright (c) 1982, 2010, Oracle. All rights reserved.

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.

Total System Global Area 3140026368 bytes
Fixed Size 2230600 bytes
Variable Size 1996490424 bytes
Database Buffers 1124073472 bytes
Redo Buffers 17231872 bytes
SQL> alter system set control_files ='+DATA02_R5/fdmn1i/controlfile/current.265.799827461' ,'+ARCH02_R5' scope=spfile;

System altered.

SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup nomount

SQL> show parameter control

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 14
control_files string +DATA02_R5/fdmn1i/controlfile/
current.265.799827461, +ARCH02
_R5
control_management_pack_access string DIAGNOSTIC+TUNING

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

2)Restore controlfile using RMAN
rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Sat Oct 19 08:47:29 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: FDMN1I (not mounted)

RMAN> restore controlfile from '+DATA02_R5/fdmn1i/controlfile/current.265.799827461';

Starting restore at 19-OCT-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1511 instance=FDMN1I1 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+DATA02_R5/fdmn1i/controlfile/current.265.799827461
output file name=+ARCH02_R5/fdmn1i/controlfile/current.10645.829212487
Finished restore at 19-OCT-13

RMAN> exit

Recovery Manager complete.
/ora001/g09104064 FDMN1I1]>sqlplus /nolog

SQL*Plus: Release 11.2.0.2.0 Production on Sat Oct 19 08:48:17 2013

Copyright (c) 1982, 2010, Oracle. All rights reserved.

SQL> conn / as sysdba
Connected.
SQL> alter system set control_files ='+DATA02_R5/fdmn1i/controlfile/current.265.799827461' ,'+ARCH02_R5/fdmn1i/controlfile/current.10645.829212487' scope=spfile;

System altered.

Please Dont use sqlplus here if you are already configured with Cluster Service

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Terminal>srvctl stop database -d FDMN1I -o immediate;
Terminal>srvctl start database -d FDMN1I
Terminal>srvctl status database -d FDMN1I
Instance FDMN1I1 is running on node svr00085
Instance FDMN1I2 is running on node svr00086

Thanks,
Jyothish

ORA-1555 Snapshot Too old while a running job or query .

2013-10-16T21:26:00.000-07:00

If we are getting this error frequently we need to think about the size of undo tablespace and undo retention time. Below query helped me to take optimum size of
undo tablespace and undo retention time.

ACTUAL UNDO SIZE
OPTIMAL UNDO RETENTION = ____________________________________
DB_BLOCK_SIZE*UNDO_BLOCK_PER_SEC

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
ROUND((d.undo_size / (to_number(f.value) *
g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size';

OPTIMAL UNDO SIZE = UNDO RETENTION*DB_BLOCK_SIZE*UNDO_BLOCK_PER_SEC

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",

SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",

(TO_NUMBER(e.value) * TO_NUMBER(f.value) *

g.undo_block_per_sec) / (1024*1024)

"NEEDED UNDO SIZE [MByte]"

FROM (

SELECT SUM(a.bytes) undo_size

FROM v$datafile a,

v$tablespace b,

dba_tablespaces c

WHERE c.contents = 'UNDO'

AND c.status = 'ONLINE'

AND b.name = c.tablespace_name

AND a.ts# = b.ts#

) d,

v$parameter e,

v$parameter f,

(

SELECT MAX(undoblks/((end_time-begin_time)*3600*24))

undo_block_per_sec

FROM v$undostat

) g

WHERE e.name = 'undo_retention'

AND f.name = 'db_block_size';

Note:This above estimation undo size and retention are depends upon the calculations from v$undostat during the previous run. Please impliment this estimation carefully
as per your requirment and resource avaialability.

Thanks,
Jyothish

Execution Time of an SQL using SQL_ID with respect to SNAP_ID

2013-08-27T21:03:00.001-07:00

Here we can calculate Elapsed of time of an sql statement using sql id. Below query will figure out the Elapsed time in seconds.

col begin_time for a25
col end_time for a11
col inst for 99999
col snapid for 999999
set lines 200
set pages 20000
select snap_id snapid,
(select substr(BEGIN_INTERVAL_TIME,1,18)||' '||substr(BEGIN_INTERVAL_TIME,24,2) from dba_hist_snapshot b where b.snap_id=a.snap_id and

a.INSTANCE_NUMBER=b.INSTANCE_NUMBER) begin_time
,(select substr(end_INTERVAL_TIME,11,8)||' '||substr(end_INTERVAL_TIME,24,2) from dba_hist_snapshot b where b.snap_id=a.snap_id and

a.INSTANCE_NUMBER=b.INSTANCE_NUMBER) end_time
,INSTANCE_NUMBER inst , PLAN_HASH_VALUE,
EXECUTIONS_DELTA Executions,
ROWS_PROCESSED_DELTA rows1,
round( CPU_TIME_DELTA /1000000,0) cpu_time,round(IOWAIT_DELTA /1000000,0) io_wait,
round( ELAPSED_TIME_DELTA /1000000,0) elapsed
from wrh$_sqlstat a where sql_id in('&SQL_ID')
order by snap_id, INSTANCE_NUMBER;

Thanks,

Jyothish Balakrishnan

Archive Generation Details

2013-08-20T14:23:00.000-07:00

Archive Generation per Hour

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY MM DD HH24:MI:SS';
select trunc(COMPLETION_TIME,'HH') Hour,round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB,count(*) Archives from v$archived_log group by trunc(COMPLETION_TIME,'HH') order by 1 ;

Archive Generation per Day

select trunc(COMPLETION_TIME,'DD') Day, round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB,count(*) Archives_Generated from v$archived_log
group by trunc(COMPLETION_TIME,'DD') order by 1;

How to find out ASM file locations for a Database

2013-08-20T14:14:00.001-07:00

column full_alias_path format a70
column file_type format a15

select concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path,
system_created, alias_directory, file_type
from ( select b.name gname, a.parent_index pindex, a.name aname,
a.reference_index rindex , a.system_created, a.alias_directory,
c.type file_type
from v$asm_alias a, v$asm_diskgroup b, v$asm_file c
where a.group_number = b.group_number
and a.group_number = c.group_number(+)
and a.file_number = c.file_number(+)
and a.file_incarnation = c.incarnation(+)
)
start with (mod(pindex, power(2, 24))) = 0
and rindex in
( select a.reference_index
from v$asm_alias a, v$asm_diskgroup b
where a.group_number = b.group_number
and (mod(a.parent_index, power(2, 24))) = 0
and a.name = '&DATABASENAME'
)
connect by prior rindex = pindex;

You can use this query with any Database running on the same server for a Database.

Drop Database Link in another schema .

2013-04-16T16:19:00.000-07:00

We can drop Database link in another schema by a simple procedure without knowing password of the user

SQL> conn / as sysdba
SQL> create or replace procedure schema.procedurename as
begin
execute immediate ‘drop database link db_link_name’;
end procedurename;

SQL>exec Procedurename;

Oracle Multimedia (ORDIM) status is "LOADING" in DBA_REGISTRY

2013-03-26T11:39:00.002-07:00

Oracle Version - 11g

Cause : Oracle Multimedia Registry Components are not properly configured .

sqlplus / as sysdab

SQL> COL COMP_NAME FOR A20
SQL> COL COMP_ID FOR A20
SQL> select comp_id,comp_name,status from dba_registry where comp_name='Oracle Multimedia';

SQL> select comp_id,comp_name,status from dba_registry where comp_name='Oracle Multimedia';

COMP_ID COMP_NAME STATUS
-------------------- -------------------- --------
ORDIM Oracle Multimedia LOADING

Action

1) Reload Registry component

SQL>conn / as sysdba

SQL> execute sys.dbms_registry.loaded('ORDIM');

2) Validate Registry Status .

SQL> execute sys.dbms_registry.valid('ORDIM');

Now we can see the status is "VALID"

SQL> select comp_id,comp_name,status from dba_registry where comp_name='Oracle Multimedia';

COMP_ID COMP_NAME STATUS
-------------------- -------------------- --------
ORDIM Oracle Multimedia VALID

Drop user failed with ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-00942: table or view does not exist

2013-03-21T12:07:00.003-07:00

Cause

Table system.aq$_internet_agent_privs is missing in "SYSTEM" Schema.

Action:

1. Check if system.aq$_internet_agent_privs exist.

SQL> conn / as sysdba

SQL> desc system.aq$_internet_agent_privs

2. Make sure that the default tablespace of SYSTEM user is properly set to SYSTEM tablespace.

SQL> select default_tablespace from dba_users where username=’SYSTEM’;

DEFAULT_TABLESPACE
——————————
SYSTEM

Run the below Script as sysdba user.

SQL> conn / as sysdba
SQL> @$ORACLE_HOME/rdbms/admin/catqueue.sql
SQL> exit

3. Then execute the DROP USER command again.

4. SQL> Drop user Username;

User Dropped

ORA-16053: DB_UNIQUE_NAME is not in the Data Guard Configuration

2013-01-24T10:13:00.002-08:00

Cause : DB_UNIQUE_NAME is not in the Data Guard Configuration. We can check current DB_UNIQUE_NAME in the configuration.

SQL> show parameter log_archive_config;

The list of valid DB_ UNIQUE_NAMEs can be seen with the V$DATAGUARD_CONFIG view. This problem can also occur when specifying a non-standby destination with an DB_ UNIQUE_NAME attribute that does not match the DB_UNIQUE_NAME initialization parameter for the current instance.

Action:

Amend the DB_UNIQUE_NAME in LOG_ARCHIVE_CONFIG parameter . Below example shows physical standby database as ORCL_STBY

1) ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL, ORCL_STBY)';

2) Re – enable Standby Archive Location Parameter;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE= ORCL_STBY REOPEN=5 NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME= ORCL_STBY;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE;

3) Check that status of the standby destination.

SQL> select dest_name,status,error from v$archive_dest_status where dest_id=2;

Cheers !!!

ORA-27100: shared memory realm already exists

2012-12-28T05:51:00.000-08:00

You may get this error after restarting the database with improper memory management of SGA in SPFILE or from a Improper shutdown .

Scenario
*******
startup nomount

ORA-27100: shared memory realm already exists

SQL> shutdown immediate

ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

Following Steps will help to overcome this issue .


           1)Rename existing SPFILE in the Database folder of Oracle Home.
           2)Restart the Service .
           3) Startup database using pfile with reverting changes .
           4)Create SPFILE from PFILE .
           5)Shutdown and Startup the Database using newly created SPFILE.

ORA-39776 & ORA-00600 in IMPDP on 11.1.0.6.0

2012-12-24T06:40:00.002-08:00

We may have to get ORA-39776 & ORA-00600 errors while importing dump file using IMPDP on 11.1.0.6.0

ORA-02354: error in exporting/importing data
ORA-39776: fatal Direct Path API error loading table

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

Cause : Oracle bug on version 11.1.0.6.0

Action: Export Dump file without COMPRESSION option or Apply Patchset 11.1.0.7 .