Archive

Archive for the ‘tips’ Category

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 ūüôā

expdp with sys without knowing password

February 19, 2013 6 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: , ,

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

How to kill, get rid of sticky RMAN processes

February 9, 2013 1 comment

Killing RMAN processes with alter system kill session… will not have an effect on them.They will marked as killed but never killed.

You have no time to wait, but those silly RMAN processes have much of that time. So, how to deal with these guys?

Finding the SPID of the process and killing from OS side is a solution, which most people recommend.

I have a practical, handy solution for this case, don’t wait them:) Use disconnect session clause with immediate keyword with alter system disconnect session. For example, you can disconnect a session having SID 1122 and serial# 222 like below:

alter system disconnect session ‘1122,222’ immediate;

Below is a script which could be used to generate disconnect session sqls for all of the RMAN sessions.

SELECT 'alter system disconnect session '''||sid||','||serial#||''' immediate;' 
FROM V$SESSION s 
 WHERE 
(
UPPER( s.PROGRAM )LIKE 'RMAN%'
)

 

Remember that there are differences between disconnect and kill session clauses.
Most remarkable is ability to give inst_id or not.

In case of disconnect session, You cannot issue disconnect session to sessions spread on cluster instances from one session. Thus, you must directly connect to sessions’ instance first, and then issue the alter system disconnect session statement.

In case of kill session, you have a chance to give third parameter “inst_id” in kill session clause. This allows you to kill sessons spread on cluster instances from one session.

It woths referring to SQL Language Reference for details of disconnect session and kill session clauses.

Hope this helps!

Oracle RACSIG subscription

December 25, 2012 Leave a comment

While looking for something in Oracle discussion forum, I bumped into webinar link, which lead me to subscribe Oracle RAC SIG.

It will enable me to get enthusiast with RAC live webinars, documents from now on.

You may subcribe from http://www.oracleracsig.org/pls/apex/f?p=105:51:0::NO:51

Enjoy it !racsig

Categories: tips Tags: , , , ,

Which rights must be granted for displaying TOAD session browser

November 22, 2012 1 comment

Following rights must be given to specific user, here MYUSER, in order to display sessions in TOAD session browser.

GRANT SELECT ON GV_$SESSION TO MYUSER;
GRANT SELECT ON GV_$PROCESS TO MYUSER;
GRANT SELECT ON GV_$SESS_IO TO MYUSER;
GRANT SELECT ON GV_$SESSION_WAIT TO MYUSER;
GRANT SELECT ON GV_$SESSION_EVENT TO MYUSER;
GRANT SELECT ON GV_$ACCESS TO MYUSER;
GRANT SELECT ON GV_$SESSTAT TO MYUSER;
GRANT SELECT ON GV_$SQL_PLAN TO MYUSER;
GRANT SELECT ON GV_$SQLTEXT_WITH_NEWLINES TO MYUSER;

Categories: tips Tags: , , , ,

Infrastructure Consolidation Projects

November 10, 2012 1 comment

What is consolidation? At first, it seems a process which enables making things with smaller number of resources.

Below picture that depicts the economy of using one resource, one jar, for many fish, which forms a real motivation to managers, and initial understanding of the concept.

I had a chance to coordinate and enroll in operational standardization, consolidation projects.

At first glance,¬†¬†you may¬†wonder why do we need consolidation or normalization projects? We may¬†find the answers with more questions like following…¬†Why systems fall in a state that are not economically operable? Why whole service responsibility is given¬†on one or two¬†super-persons who never gets ill, or goes to holiday,¬† having roles like database administration, application server administration, hardware&software procurement, application administration etc? Why¬†not divide and conquer responsibility of service management? Why do we try to use one resource for all problems?

Why people tends to put one fish in one jar, instead of putting more fish in one jar. This maybe because of less knowledge/specialization/expertise in operation, fast, unplanned installations, badly managed projects and vendor driven configurations/installations for applications/services.

Let’s continue on¬†consolidation projects… They¬†took place among different divisions, teams, outsource companies, and tens of people, having goal of responsibility transition to several technology specialized groups such as database, application server, disk, test, security, resource management, procurement ¬†from single responsible group.

After consolidating/normalizing our service/application infrastructure we found a pot of gold, here comes, what is in this pot:

– Opportunity to technology improvement like filesystem to ASM, legacy Sun Cluster to Oracle RAC. It is an opportunity, because you are not renovating, it is a process that designing the underlying infrastructure from scratch.

– Decreasing the number of hardware & software, licenses, operation personnel which lead great savings, which means decreasing OPEX (operational expenditure).

– Combining above two issues, recall that technology inevitably occurs in infrastructure every 3-5 years, due to organic growth of business, and maintenance cost increase, and manager’s will to prefer CAPEX¬†with compared to OPEX.

– Have a chance to harden the underlying infrastructure, because we deal with fewer system. Hardening matrix example

– Adopt “service” concept to infrastructure, for example an application will go to database infrastructure over application server with its database service connection. This lead flexibility in locating services to database instances.

РApplying operational standards (backup, maintenance, patch, etc.), database security standards, change management, not using production for everything, change through development to test and to production environments.

РCompliance with security standards. In our case, compliance with ISO and SOX security requirements. Enabling segregation of duties or simply separation of duties. Centrify, guardium like technologies makes things easy.

– Specialization in operations: Special tasks must be operated by specialized people, in order to make it faster with high quality.

– At last, which is simpler? Defending tens of castles or only one. Such as monitoring a large database, application server, is always a lot more easier than managing tens of them. For example, you will only get one RMAN backup, instead of ten; this is not only database task, but also backup, and first line monitoring task. You could increase the examples for application server administration, Oracle Gateway administration, Oracle EM grid agent administration etc.

A picture which may lead understanding the compliance to security/operational standards, shows the real outcome value of consolidation projects. This is like putting the fish in a bag, and changing the water again and again because it is impossible to use air pump due to its cost or technology. Why not put these fish to modern aquarium with air pump, light, plants, thick glass.

I want to add more words for specialization… In Turkish, there is a saying, “If you own only a hammer, everything seems nail to you.” In real world, do you really have only hammer in your hand, and¬† nails to be nailed… or specialized tools like screw driver, pliers, saw for screwing a screw, gripping/bending a copper cable, cutting woods etc? You must utilize from specialized tools, people, in your systems… You must divide your service into specialized responsibility areas…

Don Charisma

because anything is possible with Charisma

Carol no Mundo!

Aventuras de uma intercambista a trabalho pelo mundo!

Blog do PHP

Oracle DBA for rookies!

nimaidba

Welcome to the world of Oracle with me....

Tech

News and reviews from the world of gadgets, gear, apps and the web

WordPress.com

WordPress.com is the best place for your personal blog or business site.

Gurcan Orhan's ODI and DWH Blog

Some words about Oracle Data Integrator and Data Warehousing.