Oracle RAC 11.2.0.3 PSU4 Installation Notes

June 20, 2013 Leave a comment

Last month we had installed Oracle RAC 11.2.0.3 PSU4 to Red Hat 6.4 (Santiago).

You could find below documentation in PDF format for each part of installation:

There you can find cases, may be specific to our environment.  In order not to re-install several times, clean OS side etc., I had preferred, first ensuring our OS and network environment is ready for installation by using Oracle cluster verification utility.

This environment is in production and working for more than 2 weeks.

Hope this helps to anyone installing Oracle RAC :)

How to use Turkcell VINN modem in Ubuntu?

March 30, 2013 Leave a comment

Folowing link summarizes how to use VINN Modem, Turkcell brand, 3G USB modem in Linux. It is in Turkish but, screenshot helps a lot…

In summary, you must click Network Connections icon in System Tray and then choose New Mobile Broadband (GSM) connection. Follow the self explanatory steps and do not forget to use APNs like internet, or mgb as APN entry.

http://forum.shiftdelete.net/linux/116520-%5Bresimli-anlatim%5D-ubuntuda-turkcell-3g-vinn-usb-modem-ile-internete-baglanmak.html

Categories: links Tags: , , ,

SP2-0575: Use of Oracle SQL feature not in SQL92 Entry Level error

March 28, 2013 Leave a comment

I had encountered “SP2-0575: Use of Oracle SQL feature not in SQL92 Entry Level” error message.

When:

After my session is killed. And issued “set autotrace traceonly” as below:

ERROR at line 8:
ORA-00028: your session has been killed
ORA-00028: your session has been killed
17:38:53 TUNED>set autotrace traceonly
SP2-0575: Use of Oracle SQL feature not in SQL92 Entry Level.
17:39:07 TUNED>set autotrace trace only
SP2-0575: Use of Oracle SQL feature not in SQL92 Entry Level.
17:39:10 TUNED>
17:39:11 TUNED>
17:39:11 TUNED>
17:39:11 TUNED>conn Admin1@mydb1
Enter password:
Connected.
Elapsed: 00:00:00.01
17:39:29 ADMIN1@MYDB:mydb1> set autotrace traceonly
17:39:32 ADMIN1@MYDB:mydb1>

 

expdp with sys without knowing password

February 19, 2013 4 comments

Simple but very handy way of taking exports without knowing database password.

expdp \’/ as sysdba\’ DIRECTORY=DATA_PUMP_DIR DUMPFILE=example.dmp LOGFILE=example.log SCHEMAS=MYSCHEMA

Categories: tips Tags: , ,

Why are we using root cause analysis?

February 19, 2013 Leave a comment

RCA stands for Root-Cause Analysis. Why are we using RCA or specifically, why we are using RCA in operation.

It is clear that documenting, archiving, publishing RCA, helps to solve problems faster in re-occurences of problems.

But the there seems to be a deeper philosophy behind…

While looking at Project Management Quality Management topics in Rita Mulcahy(RIP), I see a good principle which may help us in future problems.

 

The 80 percent of problems are due to 20 percent of the root causes. [Pareto Principle, 80/20 principle by Joseph Juran]

If this is real then solving 20 percent of root causes will solve our 80 percent of our problems. So why not find and deal with those big fishes… Or preventing them before occuring.

PLS-00302 error worths me two cups of Turkish tea!

February 13, 2013 Leave a comment

Problem:

A db user came to me that, something strange is happening, he could not manage to execute a PL/SQL block.

He is getting PLS-00302 error while executing an anonymous PLSQL block.

In PLSQL block, there is a function QUERYFUNC which is already created in the same schema.

Thus giving right with grant option is nonsense.

The weird thing does not happened when I executed in SYSTEM schema.

This is what happens:

DUMMY@MYDB:node1> select DUMMY.QUERYFUNC(‘345354′) from dual;

DUMMY.QUERYFUNC(‘345354′)
—————————————————————————-
X

1 row selected.

DUMMY@MYDB:node1> declare
2
3  RetVal VARCHAR2(200);
4  MYVAR VARCHAR2(200);
5
6  BEGIN
7  MYVAR := ‘12345’;
8
9  RetVal := DUMMY.QUERYFUNC ( MYVAR );
10
11  –DBMS_OUTPUT.Put_Line(‘RetVal = ‘ || DUMMY.QUERYFUNC ( MYVAR ));
12
13  END;
14
15
16
17  /
RetVal := DUMMY.QUERYFUNC ( MYVAR );
*
ERROR at line 9:
ORA-06550: line 9, column 15:
PLS-00302: component ‘QUERYFUNC’ must be declared
ORA-06550: line 9, column 1:
PL/SQL: Statement ignored

Cause:

Below code worked, in which DUMMY schema is not specified.
DUMMY@MYDB:node1> declare
2
3  RetVal VARCHAR2(200);
4  MYVAR VARCHAR2(200);
5
6  BEGIN
7  MYVAR := ‘12345’;
8
9  RetVal := QUERYFUNC ( MYVAR );
10
11  –DBMS_OUTPUT.Put_Line(‘RetVal = ‘ || DUMMY.QUERYFUNC ( MYVAR ));
12
13  END;
14
15  /

PL/SQL procedure successfully completed.

First of all I guessed it was because we have same function somewhere else, or there is synonym object which overrides the QUERYFUNC function.

But after querying dba_objects I could not find any.

At last, the goal comes from other side…

Solution

There was a table named DUMMY in the schema named DUMMY.

Which assumes DUMMY.QUERYFUNC is a table column or something I guess.

Thank you for the solution, Mustafa Saburlu. “Afiyet olsun” for Turkish tea :)

Using Index Unusable For Gaining Disk Space In Partitioned Tables

February 9, 2013 Leave a comment

Why dedicate disk space for indexes that you are not using for data access in older partitions.

–index partition usable

select * from ADMIN1.RNC_CELLSTATS_DERYA_CFO where datetime=to_date(‘31.10.2011 23:45:00′,’dd.mm.yyyy hh24:mi:ss’)

Plan
SELECT STATEMENT ALL_ROWS Cost: 43.447 Bytes: 667.772.937 Cardinality: 79.677 CPU Cost: 1.219.929.850 IO Cost: 43.396 Time: 2
3 PARTITION RANGE SINGLE Cost: 43.447 Bytes: 667.772.937 Cardinality: 79.677 CPU Cost: 1.219.929.850 IO Cost: 43.396 Time: 2 Partition #: 1 Partitions accessed #1
2 TABLE ACCESS BY LOCAL INDEX ROWID TABLE ADMIN1.RNC_CELLSTATS_DERYA_CFO Object Instance: 1 Cost: 43.447 Bytes: 667.772.937 Cardinality: 79.677 CPU Cost: 1.219.929.850 IO Cost: 43.396 Time: 2 Partition #: 2 Partitions accessed #1
1 INDEX RANGE SCAN INDEX (UNIQUE) ADMIN1.RNC_CELLSTATS_DERYA_CFO_PK Search Columns: 1 Access Predicates: “DATETIME”=TO_DATE(‘ 2011-10-31 23:45:00′, ‘syyyy-mm-dd hh24:mi:ss’) Cost: 159 Cardinality: 79.677 CPU Cost: 17.926.550 IO Cost: 158 Time: 1 Partition #: 3 Partitions accessed #1

–index partition unusable, after

ALTER INDEX ADMIN1.RNC_CELLSTATS_DERYA_CFO_PK

MODIFY PARTITION P20111031

UNUSABLE;

select * from ADMIN1.RNC_CELLSTATS_DERYA_CFO where datetime=to_date(‘31.10.2011 23:45:00′,’dd.mm.yyyy hh24:mi:ss’)

Plan
SELECT STATEMENT ALL_ROWS Cost: 257.057 Bytes: 667.772.937 Cardinality: 79.677 CPU Cost: 2.924.946.617.725 IO Cost: 135.767 Time: 9
2 PARTITION RANGE SINGLE Cost: 257.057 Bytes: 667.772.937 Cardinality: 79.677 CPU Cost: 2.924.946.617.725 IO Cost: 135.767 Time: 9 Partition #: 1 Partitions accessed #1
1 TABLE ACCESS FULL TABLE ADMIN1.RNC_CELLSTATS_DERYA_CFO Object Instance: 1 Filter Predicates: “DATETIME”=TO_DATE(‘ 2011-10-31 23:45:00′, ‘syyyy-mm-dd hh24:mi:ss’) Cost: 257.057 Bytes: 667.772.937 Cardinality: 79.677 CPU Cost: 2.924.946.617.725 IO Cost: 135.767 Time: 9 Partition #: 2 Partitions accessed #1

–other partitions goes over indexes (usable index partitions) Beware that, if you are using bind variables, you may go with FTS over indexed partitions. You may use hints for a solution for access path, like INDEX(CELLHANDOVERS CELLHANDOVERS_PK).

 

select * from ADMIN1.RNC_CELLSTATS_DERYA_CFO where datetime=to_date(‘01.11.2011 23:45:00′,’dd.mm.yyyy hh24:mi:ss’);

Plan
SELECT STATEMENT ALL_ROWS Cost: 1.810 Bytes: 24.472.520 Cardinality: 2.920 CPU Cost: 50.591.850 IO Cost: 1.808 Time: 1
3 PARTITION RANGE SINGLE Cost: 1.810 Bytes: 24.472.520 Cardinality: 2.920 CPU Cost: 50.591.850 IO Cost: 1.808 Time: 1 Partition #: 1 Partitions accessed #2
2 TABLE ACCESS BY LOCAL INDEX ROWID TABLE ADMIN1.RNC_CELLSTATS_DERYA_CFO Object Instance: 1 Cost: 1.810 Bytes: 24.472.520 Cardinality: 2.920 CPU Cost: 50.591.850 IO Cost: 1.808 Time: 1 Partition #: 2 Partitions accessed #2
1 INDEX RANGE SCAN INDEX (UNIQUE) ADMIN1.RNC_CELLSTATS_DERYA_CFO_PK Search Columns: 1 Access Predicates: “DATETIME”=TO_DATE(‘ 2011-11-01 23:45:00′, ‘syyyy-mm-dd hh24:mi:ss’) Cost: 7 Cardinality: 2.920 CPU Cost: 749.500 IO Cost: 7 Time: 1 Partition #: 3 Partitions accessed #2

Unique constraint also worked for usable index partition.       

Inserting same row in the range usable index triggered below error:

ORA-00001: unique constraint (ADMIN1.RNC_CELLSTATS_DERYA_CFO_PK) violated

Only thing to remember is: DML activity on unusable partitions is impossible, without rebuilding the index!

Inserting existing row, which must trigger unique constraint, gives unusable state error:

insert into ADMIN1.RNC_CELLSTATS_DERYA_CFO(datetime,rnc,cell) values (to_date(‘31.10.2011 23:45:00′,’dd.mm.yyyy hh24:mi:ss’),’KRP1R01′,’YUNUP32′);

ORA-01502: index ‘ADMIN1.RNC_CELLSTATS_DERYA_CFO_PK’ or partition of such index is in unusable state

Inserting non-existent row, which must not trigger unique constraint, also gives  unusable state error:

insert into ADMIN1.RNC_CELLSTATS_DERYA_CFO(datetime,rnc,cell) values (to_date(‘31.10.2011 23:44:00′,’dd.mm.yyyy hh24:mi:ss’),’KRP1R01′,’YUNUP32′);

ORA-01502: index ‘ADMIN1.RNC_CELLSTATS_DERYA_CFO_PK’ or partition of such index is in unusable state

In a 1TB sized table RNC_CELLSTATS, making unusable frees 15 GB. Thus, you may not gain big spaces from big tables. While selecting targets  focus on index segments having huge sizes.

SEGMENT_NAME GB # partitions
RNC_CELLSTATS

980

91

RNC_CELLSTATS_PK

15

91

Below, focusing on index segments sizes over 100G, you may earn 6 TB disk space. Data is extracted by using DBA_SEGMENTS dictionary view.

SEGMENT_NAME Size GB # partitions Earned GB
CELLHANDOVERS_PK

2.666

1.100

2132

CELLHANDOVERS_IND1

859

1.100

686

CELLHANDOVERS_IND3

819

1.100

654

CELLHANDOVERS_IND2

811

1.100

648

SGSN_CDR_NDX03

725

65

362

RNC_CELLHO_PK

311

91

155

MGW_E1TTP_PK

271

91

216

RNC_GSMRELATION_PK

245

91

122

CELLIDENTITY_NDX

228

65

114

RNC_CELLHO_PK2

208

91

103

RECORDOPENINGDATE_NDX

193

65

96

INTERNAL_SOHO_DS_MISSING_NE_IX

188

31

93

ENUM_FULL_IND01

161

180

128

MGW_E1TTP_D_PK

146

91

72

NEIGHBOUR_CELL_PK

128

100

63

CELLHANDOVERS_CL_PK

123

1.100

98

CCNPERFCOUNTER_PK

122

31

60

MGW_TDMTERMGRP_PK

109

91

54

CELLHANDOVERS_CL_IND2

102

1.100

81

6,280

Follow

Get every new post delivered to your Inbox.

Join 36 other followers