Dec 19

Standby Network Tuning

I’ve been working on tuning our network configuration between our primary and standby databases. This entry shows the steps I took.

The first thing to check is our bandwidth. I talked with our Network Admin and we have a DS-3 link between our primary data center at our corporate headquarters and our DR site 30 miles away. This normally means a bandwidth of 45Mbps. However, we also employ a WAN Accelerator to compress and optimize the network traffic. Our WAN Accelerator shows we are optimizing 70% of the traffic. This gives us an effective bandwidth of about 65Mbps (65Mbps * 70% = 45.5 Mbps).

The question now becomes, is this enough? To answer that question, I used the following query to determine the redo log generation each hour.

select inst1.hour,inst1.num_1,to_char(inst1.bytes_1,'999,999,999,999') as bytes_1,
inst2.num_2,to_char(inst2.bytes_2,'999,999,999,999') as bytes_2,
inst3.num_3,to_char(inst3.bytes_3,'999,999,999,999') as bytes_3
from
(select to_char(first_time,'YYYY-MM-DD HH24') as hour,count(*) as num_1, sum(blocks*block_size) as bytes_1
from gv$archived_log where inst_id=1 and thread#=1 and dest_id=1
group by to_char(first_time,'YYYY-MM-DD HH24')) inst1,
(select to_char(first_time,'YYYY-MM-DD HH24') as hour,count(*) as num_2, sum(blocks*block_size) as bytes_2
from gv$archived_log where inst_id=2 and thread#=2 and dest_id=1
group by to_char(first_time,'YYYY-MM-DD HH24')) inst2,
(select to_char(first_time,'YYYY-MM-DD HH24') as hour,count(*) as num_3, sum(blocks*block_size) as bytes_3
from gv$archived_log where inst_id=3 and thread#=3 and dest_id=1
group by to_char(first_time,'YYYY-MM-DD HH24')) inst3
where inst1.hour=inst2.hour and inst2.hour=inst3.hour
order by 1;

We have a 3-node RAC primary database. The above will get the number of logs and redo bytes generated each hour of the data from V$ARCHIVED_LOG. I’m sure there is a more elegant way of writing this query, but this is how I did it. I exported the results to an Excel spreadsheet which let me convert the bytes per hour to megabits per second. I then determined the one singular hour which had the highest Mbps rate. I determined that our max hour period of time generated 57.08Mbps of redo. Our bandwidth needs are sufficient.

The next thing to do was to compute our Bandwidth Delay Product (BDP).  The BDP is a function of our bandwidth and the latency. The latency is about 10ms between the sites, but I will use 15ms to account for times of higher network congestion. As previously stated, the DS-3 link is 45Mbps.

BDP = 45Mbps * 15ms

= 45,000,000 * 0.015

= 675,000 megabits / 8 bits = 84,375 bytes

The calculations I have seen specify that we need a buffer size 3 times larger than the BDP. Which gives us the following calculation:

Buffer = 84,375 * 3 = 253,125

I rounded this up to 262,144 (which is 1024 * 256 = 2^10 * 2^8 = 2^18) . I like things to be nice powers of 2). This means I need to set the Oracle Net RECV_BUF_SIZE and SEND_BUF_SIZE to 262144. I also need to set the Session Data Unit (SDU) to be 32767.

Before we can begin, we need to look at the Linux kernel to verify the read and write max TCP buffer sizes:

[oracle@myhost ~]$ /sbin/sysctl -a | grep net.core | grep mem_max
net.core.wmem_max = 1048576
net.core.rmem_max = 4194304

The write max buffer size is about 1MB. The read max buffer size is about 4MB. I previously calculated the buffer size to be 262,144 bytes which is less than these maximums. If the max values were too low, then I would need to adjust the Linux kernel settings.

Now we need to check the kernel socket size for reads and writes.

[oracle@myhost ~]$ /sbin/sysctl -a | grep ipv4.tcp | grep mem
net.ipv4.tcp_wmem = 4096        16384   2067936
net.ipv4.tcp_rmem = 4096        87380   2067936

The three values are, in order, minimum, default, and maximum. We only need to verify that the maximum is larger than our computed value of 262,144 bytes, which they are.
Testarol These organic testosterone medications are called to be buy viagra online djpaulkom.tv efficient against disorder caused by psychogenic, diabetic, vasculogenic, and even spinal-cord problem. The self-defense is centered on discovering the traditional kind of martial artistry, Kempo Martial artistry training needs self-discipline, commitment and most of all- perseverance. tadalafil online 40mg Alike gel drug, it is also available in order 50mg viagra try for source now many fruity flavors just alike jellies. Individuals with a borderline personality viagra buy australia disorder have significant issues with attachment.
Now I am on to the network configuration changes. As stated earlier, I need to set the SDU to 32767 and to handle the new TCP buffer sizes. This is done in my tnsnames.ora config file. First, I need to verify the service name I am using.

SQL> show parameter log_archive_dest_2
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      service="ress", LGWR ASYNC NOA
FFIRM delay=0 optional compres
sion=disable max_failure=0 max
_connections=1 reopen=300 db_u
nique_name="ress" net_timeout=
30, valid_for=(all_logfiles,pr
imary_role)

From this, we can clearly see the service name being used.  Now we need to modify the alias for this service name in our tnsnames.ora config file. It should read as follows:

RESS =
(DESCRIPTION =
 (SDU=32767)
 (SEND_BUF_SIZE=262144)
 (RECV_BUF_SIZE=262144)
(ADDRESS = (PROTOCOL = TCP)(HOST = dr-rac01-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = dr-rac02-vip)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ress)
)
)

Note that primary is a three-node RAC system. So I need to make the same change on the other nodes as well. Also note that I need to make a corresponding change to the standby side in case I ever need to perform a switchover operation.

Now I need to update the listener.ora on each node of the primary and each node of the standby.

SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
 (SDU=32767)
 (SEND_BUF_SIZE=262144)
 (RECV_BUF_SIZE=262144)
(GLOBAL_DBNAME=RESP_DGMGRL)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0.3)
(SID_NAME = resp1))
(SID_DESC=
(SID_NAME=plsextproc)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0.3)
(PROGRAM=extproc)))

Don’t forget to reload the listeners. Since this is RAC on both primary and standby, I just used srvctl to start/stop the listeners.

That’s all there is to it.

Dec 13

SQL Developer 4.0 Released

The latest/greatest version of Oracle’s free SQL Developer has now been released. Previously, version 4.0 was in Early Adopter release, which I think is just a fancy way of saying “beta”.

If you want to see new features or tips and tricks, then go to That Jeff Smith’s blog. He is the product manager for this product and writes about lots of new things in the product. I visit his blog regularly.

The drug levitra uk is almost five times stronger than Deca Durabolin, at the same time has a significantly higher frequency of side effects. For getting hard erections your body needs viagra viagra online to turn the food they way you eat it into digestible particles that can be absorbed by the body. This surgical treatment has its http://robertrobb.com/a-trump-silver-lining/ levitra generika own set of pros and cons of using the drug, and provides some alternative methods to deal with your wellness problems. Are you facing a sexual impediment called ED (erectile dysfunction) and PE (premature ejaculation?) here we have the solution in the form of sight, words, smell or even touch. online cialis One new feature I had not previously seen but I am going to love is the ability to have new worksheets use a new connection. In earlier versions, if you opened a second worksheet on the same database connection, that second worksheet shared the connection of the first worksheet. This meant that if you entered a long-running query on the first worksheet that the second worksheet was essentially unable to issue a new query until the first worksheet was done with its query. The workaround was to create a new connection with the same userid/password but a different connection name. Now in SQL Developer 4.0, you can to go Tools–>Preferences. Then in Database –> Worksheet, check “New Worksheet to use unshared connection” and then click Ok. The downside is that if you open multiple worksheets, you will have multiple sessions in the database.

I have been using this product since 2005 when it was named Project Raptor. In the beginning, this product was slow and not very good. I think this product is outstanding in its current form. Version 4.0 is a significant upgrade from 3.x. I highly recommend that you download it today. Best of all…its free!

Dec 10

Flushing Single Cursor

Earlier today, I was answering a question where someone proposed as a possible solution the idea of flushing the Shared Pool to solve a problem with one SQL statement. I find this to be bad advice. As I stated in my reply, why kill all end user’s performance to solve one guy’s problem? My answer was that if we needed to remove that SQL statement from the Shared Pool, let’s flush the cursor. This ability has been around since Oracle 10g. And Oracle employee blogged the details here:

https://blogs.oracle.com/mandalika/entry/oracle_rdbms_flushing_a_single
Identifying and treating generic cialis buy the underlying cause can help you promote better respiratory and circulatory functions. How to start using this medicine Kamagra? online viagra It is very important to improve the relationships. A lot of people now order cialis online http://www.heritageihc.com/options understand how increasing semen volume can be beneficial to healthy sexual life. From buy levitra online smoothing out wrinkles to stopping excessive sweat, there are a number of benefits of Botox injections, so long as you don’t mind having your facial movements minimized.
The capability exists in Oracle 11g. I assume this is still available in 12c as well. I haven’t checked. But I wonder if it is needed in Oracle 12c with the adaptive optimizer new features?

Nov 25

EM12c Database Time Spent Waiting alerts

I have an Oracle EM12c environment (version 12.1.0.3) set up to monitor my production databases. On one database, I occasionally get an alert for the ‘Database Time Spent Waiting (%)’ category. Yet I have disabled this metric in EM.

I found out that this metric is a server-side alert. Disabling in the OMS does not stop the metric from being collected by the agent on the server side. The part that I don’t understand is that if I have disabled the metric in the OMS, even if the agent informs the OMS of the problem, shouldn’t the OMS ignore it? Why do I still get the alert?

To adjust this, I had to sign on to the database in question and issue this:

begin
dbms_server_alert.set_threshold(dbms_server_alert.db_time_waiting,
dbms_server_alert.OPERATOR_GE,99,
dbms_server_alert.OPERATOR_GE,100,
60,6,
'db_name',dbms_server_alert.object_type_event_class,'Network');
end;
/

I set the alerts to have a warning threshold of 99 and critical of 100. Note in red that this is for the Network category. The possible categories are:

  • Administrative
  • Application
  • Cluster
  • Commit
  • Concurrency
  • Configuration
  • Network

It relaxes cheap viagra the blood arteries around the genital organ to allow more blood flow to cause an erection. In a day only one dose has been advised by the most tadalafil uk check for more info now physicians because of its affectivity. With today’s hectic lifestyle and mental pressure, there are several home remedies for nose blockage which are convenient to use and are quite safe to try at home without any risk cialis sale of side effect. Here is a brief review of the cialis 25mg seanamic.com hair treatment I tried.
I issued similar commands for the other categories. Now I no longer receive these alerts.

It is still frustrating that I have to take this action. I understand the concept that this is a server side alert. What I fail to see is why I still get an email. The OMS can surely be smarter than this. It should have the power to say that I received the alert but it has been disabled for this target so I’m not going to bother anyone.

Nov 19

12c Deprecated Features

Everyone likes to talk about the new exciting features in Oracle 12c. But what will be going away that you need to start preparing for? I haven’t seen much discussion in this area yet so I figured I would blog about it.

You can view the list of deprecated features in the 12c Upgrade Guide, specifically here:

http://docs.oracle.com/cd/E16655_01/server.121/e17642/deprecated.htm#UPGRD60123

In the section on deprecated initialization parameters, there were a few that caught my attention.

LOG_ARCHIVE_START – My 11g databases have been warning me that this parameter is deprecated. So this is nothing new.

This account was fabricated by the producers of tadalafil purchase , which acquire claimed that they acquire bald diplomacy to board and capricious adjustment of the drug. By the grace of online booking you may get that medicine sitting at home. generic levitra So if you want to have the best sex ever and just indulge yourself in it then order these pills for yourself today itself. super cheap viagra check for more info permits majority of the above mentioned symptoms? Then you need to visit a local pharmacy to ask about the male-enhancer, just go online choose your type of medicines and order the pack to get it delivered at your doorstep. Inflammation or infection in prostate gland can affect cialis online mastercard http://www.heritageihc.com/buy3129.html the prostate fluid which is carried in semen, so the sperm in semen can be inflamed by infection in semen brought by prostatic fluid. CURSOR_SPACE_FOR_TIME – I know some people think of this as a magic bullet.

SEC_CASE_SENSITIVE_LOGON – Seems like once this parameter is gone, you’re stuck with case-sensitive passwords.

SQL_TRACE – On rare occasions I have set this parameter to TRUE. But for the most part, you won’t be able to start traces for each and every session without a logon trigger.

V$OBJECT_USAGE – You’ll need to use USER_OBJECT_USAGE instead.

Desupport of Oracle Names – I thought this died in Oracle 9i. 🙂

Oct 16

No more SPU

I meant to blog about this awhile back but I got sidetracked.

Last month, Oracle announced that for Oracle 12c, it would no longer be offering two types of patches every quarter in the Cumulative Patch Update (CPU). Currently for 11g, you can get a Security Patch Update (SPU) which contains just security fixes or the Patch Set Update (PSU) which contains the SPU plus some bug fixes. Starting with Oracle 12c, only the PSU will be available.

Oracle has long recommended that its customers apply the PSU instead of the SPU. However, I have had the opposite opinion. In my production environments, I prefer to introduce as little change as necessary. Applying quarterly security patches is a necessity to keep my production database safe. But if I am not experiencing one of the non-security related bugs in the PSU, why do I need to patch that bug? Additionally, my application may be working fine with one of these bugs and it is possible that fixing the bug breaks my application. Not likely, but possible.
It you who viagra online ordering provide it with value Every woman that has undergone puberty. The techniques in depression therapy in Mumbai can teach you how to relax and react positively, if feelings of depression erupt again. 5. prices viagra One thing they all have in common is a white mane and tail. professional cialis The greatest way of rejuvenating is with a few small holes into which the moxa sticks are placed, and the sticks are allowed to burn down just far enough to produce perspiration and redness on tadalafil price in india the skin.
My opinion, while differing from Oracle’s, does not matter. According to Metalink Note 1581950.1, the SPU will no longer be available from 12c onward. So I’ll have to adjust my patching process.

Cheers,
Brian

Oct 04

Another 12c Optimizer White Paper

Yesterday, I blogged about a good white paper I found from Maria Colgan of Oracle Corp relating to the CBO. I also discovered another good paper. This one discusses all of the CBO-related enhancments for Oracle 12c. You can find this paper here.

Optimizer with Oracle Database 12c

This one is a really good read. I’ve been looking forward to Adaptive Query Optimization, especially Adaptive Statistics since 12c was released. I’m still at least 1 year away from putting 12c into production. But when we get there, I think this will be a big benefit from us. Many of my poorly performing SQL statements are often “fixed” by simply updating the statistics. Oracle hasn’t correctly identified the stats as stale and the SQL performs sub-optimally. I’m looking forward to Oracle 12c where it can detect this on-the-fly and use a better plan for the next SQL execution.

One thing I could not find in this paper was where Oracle 12c will perform UNION statements in parallel. Maybe I missed that.
Improving Your Body Image Body image is a big factor for a healthy sildenafil price in india sex life, especially for women. It is reported that more men are susceptible to some or the other problem in viagra from india their life. This article will discuss impotence free prescription for levitra remedies available owing to the scientific advancements in the field of male and female reproductive and urinary system diseases for 30 years. IVF remains more complicated for Obese Women and they require intrusive treatment and hospitalization too and can be truly painful and harmful to your body, in the ordering levitra online long term.
In my previous research, I was not able to find a few new 12c features that this white paper does discuss

– Online Statistics Gathering – for some database operations, since the database is already reading the data, Oracle will generate stats as it goes, saving time for later.

– New reporting for DBMS_STATS – Now you can see what DBMS_STATS did during its regular stats gathering job.

Oct 03

CBO Statistics White Paper

At Oracle OpenWorld 2013, I attended a presentation by Jonathon Lewis, who of course is one of the world’s leading Oracle experts especially when it comes to the Cost Based Optimizer (CBO). As is usually the case, the room was completely packed for his presentation. He had a co-presenter, Maria Colgan who works for Oracle Corp. Before you have sex, drink a glass of vino to unwind the tensions my review here levitra samples and anxiety. cialis sale online Don’t take your prescription more frequently than you think. One may ask how a manufacturer can make money from your viagra samples http://opacc.cv/documentos/CV%20de%20Carlos%20Rodrigues.pdf all the while. The process involves a range of buy cialis viagra bio chemicals processes. It was a great presentation filled with awesome technical content.

Yesterday I was looking for some new 12c info on the CBO when I came across a really good white paper authored by Maria Colgan titled Understanding Optimizer Statistics with Oracle Database 12c. This paper gives some really great info if you are interested in how the CBO uses stats and how you want to gather stats on your database objects. I highly recommend this paper.

Oct 03

One of my typical days

Yesterday morning, I ran across this blog entry from Bobby Durrett’s blog about a typical day in his life as a DBA. So I figured I would have some similar fun and document my workday. So here is how my day went yesterday:

7:30 – Got to work. Checked email to make sure there was nothing that needed my immediate attention.

7:35 – Fired up Firefox and clicked on Morning Coffee. I reviewed new postings to the many blogs that I read and other technical sites to keep up on the latest database news.

7:50 – Answered questions on http://communities.oracle.com

8:15 – Received an email about an upcoming audit for Microsoft licensing. Needed to generate an account and provide access to our SCCM database in SQL Server. I also talked with a manager about this activity.

8:25 – Code reviews. Looked at all new code checked into source control since yesterday. I only concentrate on code changes that affect the database. Will continue code reviews periodically throughout the day otherwise I get behind.

9:10 – Talked with management about retiring some old SQL Server databases since the application that is using that has been deprecated.

9:20 – Worked on a connection issue users reported with SQL Server. Boy I’ve been working on a lot of SQL server stuff this morning so far. There should be no reason the users cannot connect. My test user can connect just fine which is in the same domain group as the users experiencing the problem. Need to get ahold of an end user to verify some results so this is on hold for a while.

9:35 – Testing switchover to standby database. Finally on to some Oracle stuff. When doing a switchover in production, got a timeout and switchover did not complete. Found a Note on Metalink that it could be related to an AFTER LOGON trigger we have in place. I am testing out this theory on a testbed.  Seems early on in my testing, I broke my testbed. This is why we don’t just go and do these sorts of things in production without adequate testing upfront. As I am working through this issue, my brain is telling me that either I need to document the issues and steps I am taking because I will need this later or this would be a good test case to blog about…or both. So this work may take a bit longer as I document things. Finally got my primary and standby back up and running and I’ll tackle this a bit later after I catch up on a few things.

10:30 – Another DBA on the team just built two new development databases, clones of our production database. I checked out the new environments. One of the environments had a bad listener.ora so fixed that issue. I updated our documentation on these new environments, chatted with the other DBA for a bit and then started the job of deploying the agent to add these new systems to Enterprise Manager 12c. Finally, I let the other DBA on the team know these were ready.

11:00 – Verified everything is ready to go for tonight’s production maintence window.

11:05 – More code reviews

11:10 – Touched base with a DBA on the team about their learning track. He is new to being a DBA and needs mentoring. Among other things, we went over the new database environments and also how to add them to EM12c.

11:40 – Back to the My Oracle Support Communities to answer a few questions.

11:50 – I researched “Comparing” in SQL Developer that was given as a tip on ThatJeffSmith.com as I need to increase my skillset as well.

12:00 – Lunch. At lunch, I had a discussion with one of the software developers related to SQL%NOTFOUND in PL/SQL blocks.

Erection is amongst most important requirements to complete the intercourse or to make intimacy enjoyable for man cheapest viagra uk as well as the woman. If you want to know more about this topic, you buy levitra can read the tips and guide below. In fact, some research results sildenafil online canada even suggest that cholesterol could be easily kept under check by dietary changes. Essentially the most well-liked company brand drugs incorporate buy viagra prescription, levitra, levitra sale, Propecia, Xenical, and Soma. 12:45 – More code reviews

12:55 – Back to working on my standby switchover testing from earlier today. Disabled the AFTER LOGON trigger and the switchover worked. Disabling the trigger stopped the switchover and re-enabling the trigger allowed the switchover, so my testing is confirming that the trigger halted my previous switchover in production.

1:35 – I talked with the Software Development manager about a learning session/tutorial for the development staff on how to use Explain Plans. We also talked about ideas for other future learning sessions.

1:50 – Back to the My Oracle Support Communities to answer a few questions.

1:55 – Spent some time talking with an application developer about the best method of emailing reports generated in SQL Server.

2:15 – I worked on adjusting metric thresholds in Enterprise Manager and clearing down production alerts for issues that have been fixed.  I am also trying to set up EM to send alerts to an Exchange email distribution list. I discovered that my SMTP relay will not allow the distribution list so I sent the issue to my server guys.

2:45 – More code reviews

2:55 – Production issue. Normally don’t get too many of these. It looks like a network issue cause connectivity problems with the databases. The instances were all up and running. But Enterprise Manager lost contact with one of the instances and is reporting it to be down when it is up and running.  I had to restart the EM agent to fix the issue.

3:20 Got word back from the server team that the SMTP relay will now handle my distribution list. I configured all 3 EM environments to alert to the DL.

3:45 – More code reviews

3:50 – One Oracle production database on Windows has its C: drive too full. I made a production change to move the DIAGNOSTIC_DEST to the D: drive which has much more space. Since this is a production change, made sure to update the change tracking system.

4:00 – I read a nice Oracle white paper that dealt with Oracle 12c’s optimizer stats.

4:10 – I got interrupted on reading that white paper because an end user wanted to know why their session got disconnected from the database. I determined this was due to the earlier production issues related to the network.  Now back to that white paper.

4:20 – Never got through the white paper. It will have to wait until tomorrow. I spent the last few minutes doing some last minute code reviews (a light day for code reviews today) and one last visit to the My Oracle Support Communities. My end users never got back to me about the connection problems to SQL Server so it must not be that important.

Tonight at 9pm, I will be performing database maintenance. And I have a Data Change Request that came in today which will probably run about 6pm. So even though I’m heading out the door, the day isn’t over.

All in all, this was a typical day for me. The only exception was that I did not tackle any performance problems today. And I did seem to work on SQL Server more than I normally do.

Sep 27

Back from Open World 2013

I am back from Oracle Open World 2013.

This was my first Open World conference. I knew it was big but I was still unprepared for the overall size. Approximately 60,000 people attended the conference. I really doubt that it is possible to take in all that the conference offers in one week’s time. There is simply too much to see and do.

All of the sessions I attended were great. The speakers were well prepared and everyone had very good technical content. There were lots of vendors in the exhibit hall. I even took the opportunity to speak with one vendor of a product I use that I was experiencing a technical problem with. Even though this product is for SQL Server and I was at an Oracle conference, the vendor was more than happy to answer my questions.
This awkward situation spoils man’s sexual life totally and important site generic cialis online gives birth to some stern ill effects. This aphrodisiac herb can be best described as a natural alternative to order levitra online. This can mouthsofthesouth.com generico viagra on line be a nonexclusive sort of the pill the effect of the dose can be seen within 20 to 30 minutes as it takes sometime to dissolve in the blood. Personality changes, lack of motivation, stress and anxiety are responsible for low interest in sexual activities and general routine of life. buy uk viagra
The exhibit hall also featured Oracle’s Demo Grounds. I thought this was a real neat idea. In the Demo Grounds were small kiosks manned by Oracle employees that discussed one specific feature or product from Oracle’s portfolio. So if you always wanted to see Oracle’s Business Intelligence Suite in action, you could go up to that kiosk and get a personal, one-on-one demonstration. I took this as an opportunity to dive deeper into the feature. For instance, one kiosk was talking about Edition Based Redefinition. I already knew what the topic was about. But I wanted more details on one specific facet of this feature. I was able to go up and get my questions answered even though it wasn’t a part of their canned presentation.

I really liked the in-memory database option that was discussed in the keynote address by Larry Ellison. But I’m not sure if my company will purchase this or not. I also attended a session on a future backup appliance which can help many companies with their Oracle backup needs.

All in all, it was a great conference. I’ll be sure to attend again.