Feb 06

Large .patch_storage

I received an alert from Enterprise Manager that one of my production databases was getting low on disk space. I tracked it down to $GRID_HOME/.patch_storage which was consuming 30GB of my 90GB drive. Yikes!

The first thing I did was to run the opatch cleanup routine as I documented here back in 2013: http://www.peasland.net/2013/03/21/patch_storage/

Unfortunately, it didn’t clean up anything.

This time, I had to resort to a manual cleanup. Here are the steps I did.

The files in .patch_storage start with the patch molecule number and a timestamp. For example: 19582630_Nov_14_2014_21_43_23

I need to ask opatch if that patch is still in the inventory.

$ORACLE_HOME/OPatch/opatch lsinventory|grep 19582630
20075154, 20641027, 22271856, 20548410, 19016964, 19582630

lsinventory shows the patch is in the inventory. I move on to the next patch.

When my lsinventory command returns nothing, the patch is not in the inventory.  MOS Note 550522.1 says you can remove that directory as its  no longer needed.  The ever-cautious DBA personality in me wants to ensure I can recover from a simple “rm -rf dir_name” command. So I tar and gzip the directory first, then remove the directory.

tar cvf 25869825_Jul_3_2017_23_11_58.tar 25869825_Jul_3_2017_23_11_58

gzip 25869825_Jul_3_2017_23_11_58.tar

rm -rf 25869825_Jul_3_2017_23_11_58

Its painstaking work doing this for each and every patch. I’m sure someone who is better than me with sed and awk and shell scripting could automate this process.

By following these steps, my .patch_storage directory dropped from 30GB down to 11GB.

Next quarter when I apply my CPU again, should opatch cry foul and demand these be placed back, I can quickly unzip and extract the tarball and opatch should be happy.

I did this operation on $GRID_HOME but it will work on $RDBMS_HOME as well. Also, since this is Oracle RAC, I may want to do this on all nodes in the cluster.

Nov 08

Where are my Patches?

I thought I had posted something like this on my blog previously but I couldn’t find it when I was looking for someone earlier today. So here is the information anew. If my old blog post is not more than just a figment of my imagination, it probably needs updating anyway. 🙂

How do I find the latest/greatest patches for my database? Well the answer is often to apply the most recent quarterly Cumulative Patch Update (CPU). All too often on the MOSC and OTN forums, I see people who ask where the most recent CPU is and someone provides the patch number for them. That’s great but in 3 months, the info will be out of date. Here is how you can find your most recent patches for currently supported versions.

First, point your browser to OTN (http://otn.oracle.com) then in the Essential Links section, click on Critical Patch Updates.

The next page shows you each quarter’s CPU patches. Near the top of the page is a section showing you the CPUs. One thing I like about this page is that it shows you the next four release dates. In my screen shot below, the next CPU will be released on 18 Jan 2018. This is great for planning. As I write this on 8 Nov 2017, I know that I’m still more than 2 months away. But if I were looking for the most recent CPU and today were 17 Jan 2018, I’d probably just wait one more day to download the next newest CPU and be even more current.

The table that follows contains a link to each CPU. You can always find the most recent one at the top of the table. In the screenshot above, the Oct 2017 CPU can be seen as the most recent. Almost every time, you will be downloading the most recent CPU. But on rare occasions, you may be interested in one from the past. Click on the link under the Critical Patch Update page of the quarter you need.

This will bring you to a page with a big table showing links to every possible CPU for every possible Oracle product. To get to the CPU, you will click on the link under the Patch Availability column. If you click on the link under the first column, as I have done more times than I’d like to admit, you will not end up in the correct place.

I typically only deal with the Database. So I scroll down the list of products until I find the Oracle Database Server line and then click on the Database link on the right.

That’s it, you have now arrived at the MOS Note for that quarter’s CPU for your product.

Happy patching!

Oct 25

RU or RUR?

Oracle 12.2 has changed the patches. It used to be so easy back in the day. Just download the Cumulative Patch Update (CPU) and apply the latest/greatest security patches. Then Oracle decided security patches were not enough so they gave us the Patch Set Update (PSU) which contained the regression fixes on top of plugging security holes. The CPU was renamed to be the Security Patch Update (SPU) which in my opinion contributed to some monkey business.

Now I’ve always been of the opinion to introduce as little change as possible to a stable production environment. I do need to patch security holes but if I’m not experiencing any other issues, why apply extra patches on top? With the PSU/SPU choice, I always chose the SPU. Oracle’s recommendation was to apply the PSU and beginning with 12.1, the SPU was no longer available.

At some point, Oracle also introduced the Bundle Patch (BP) which contains all of the changes in the PSU plus even more changes for optimizer fixes and functional fixes. Given a choice between SPU, PSU and BP, I’d still choose the SPU if that option were available to me. Oracle now recommends the BP.

Well if that were not confusing enough (remember when it was simple with just the CPU?), Oracle now has the Release Update (RU) and Release Update Revision (RUR). The PSU is gone. I haven’t heard yet, but I suspect the BP is on its way out since the RU covers it.

So what is the RU and RUR? Rather than try to describe it and totally botch the description, I’ll refer you to this blog post by Oracle’s Mike Dietrich. Please give it a read. It does a very nice job detailing the history of what is in the PSU, BP, RU, and RUR. I had to read this post a few times before I got it all sorted out in my head.

That being said, Oracle seems to be making this even more complicated. I know have to understand that the RUR is released the quarter after the RU it modifies. and if I’m reading the last diagram correctly, it means in one quarter, RU1 is released. In the second quarter, RU2 and RUR1 for RU1 is released. And in the third quarter, RU3 is released along with RUR2 for RU1 and RUR1 for RU2. Seems confusing to me. It should be simpler than this. I’m sure Oracle will tell me to keep it simple by applying the RU’s and never worry about the RUR. But again…that introduces more change to a stable production system that I may not be comfortable with.



Oct 09

Filtering Alert Logs in EM13c

I’ve been receiving ORA-1555 (snapshot too old) alerts from my databases via Enterprise Manager 13c. For production environments, these are good alerts to receive and can be an indicator that I have an issue to resolve. For my development databases, ORA-1555 errors are not a concern of mine. It is common for developers to write queries that run a long time and then tune them later on. I do not want to see alerts on ORA-1555 errors in my Inbox from dev databases. Yet EM13c does not have an obvious way to stop these alerts from coming. I recently learned that one can filter out rows of the Alert Log from EM13c’s notification functionality, thus supressing any ORA-1555 alerts from being generated.
To do this, log in to EM13c and navigate to the database in question (or update a template). Then click on Oracle Database –> Monitoring –> Metric and Collection Settings.  In the DB Alert Log section is Generic Alert Log Error. Press the pencils icon on the right for this line to edit the settings.

Scroll down to the very bottom of the next page. In the section titled Metric Collection Properties, there is one box labelled Alert Log Filter Expression. This box is a regular expression. Any lines in the Alert Log that match the regex will be filtered out from consideration. As you can see below, I added the “01555” error code.


Not only can you filter out any ORA-1555 errors, but you can see others that can be filtered out as well.



Jul 20

The Performance Tuning Maze

One day, you wake up and find that you are an Oracle database administrator. The gods have finally seen the light to your true potential and allowed you to work in the best job in the world! You begin your DBA career so bright-eyed and bushy-tailed. You’re creating new databases, granting privileges, writing PL/SQL code. Life is great. You cannot wait to get up in the morning, pour that first cup of coffee, and point your browser to your favorite Oracle forums, eager to soak up a lifetime’s worth of knowledge all before lunch! If those gods are still smiling on you, you may even answer a few questions and get rewarded with awesome points. Life is good. Life is sweet.

While you are still basking in the glow of your new-found career, someone comes to  you with a problem. A performance problem. You freeze. There’s a small lump in your throat as you come to the self-realization you have no idea how to solve database performance problems. What you did not realize on that day is that every DBA before you has been in exactly this same situation early in their careers. Yet, it doesn’t stop the other person from looking at you wondering why you are not solving the performance issue immediately. After all, you are the DBA and you should know how to do this stuff. This magical stuff they call (cue the angels singing) Performance Tuning. For it has been written, if you are going to be a DBA and survive, and do this job well, you will have to tune performance. Others will never know what goes on behind the curtain as you make potions and cast spells. All they care about is that you solved their performance issue and they can get on with their daily work.

At this point early in their career, every DBA decides they need to learn more about this thing they call Performance Tuning. What is it? How do I do it? How can I become the most important person in my IT department because I discovered the secret sauce to turn that 5 hour report into a 1 minute miracle?

Oh, we were so young back then…so naive. We thought it was easy. Push a few buttons, fire up a few tools and presto. The performance tuning solution is found and we are wonderful! Life seemed so easy as we ventured down that yellow brick road. But on this road, there are no scare crows. No lions. No tin men. Not even any cute little dogs named Toto. No…this road….this Oracle Performance Tuning road is filled with things much greater. On this road, we meet utilities and tools. We meet Explain Plan. We meet tkprof and SQLT. We find wonderful views like V$SGA_TARGET_ADVICE and V$SESSION_WAIT and its twin V$SESSION_EVENT (not identical twins mind you, but one look and you know they’re related).

So there you are. Your shiny DBA title still sitting under your name in every email signature you send. And you now have all of these wonderful tools at your disposal. You’ve picked up ASH and AWR because thankfully your company has gifted you the Diagnostics Pack. Your bookshelf is armed with great tomes like this one. (Shameless plug I know). Some great guy on the forums, like me, clued you in to Lighty. You have an entire toolbox at your disposal. No! Not toolbox….warchest! Small countries in other places in the world do not have the arsenal you have at your disposal. Why…I could touch that super-secret button in the SQL Tuning Advisor and blow away one of those countries, *and* make SQL ID 98byz76pkyql run faster while my coffee still has steam rising from it…I’m so good.

Remember that day you received your first performance issue and you had that lump in your throat? There’s another day like that in your DBA career. Its the day you reach the Performance Tuning Maze (cue the thunder and lightning). But this isn’t any maze. This is different. Most mazes have one entrance and one exit, with many turns and decisions along the way. This maze, why, this maze is obviously different. This maze has many, many entrances. And this maze has many, many exits. Each entrance is a different performance tuning tool. And each exit is a solution, but not all solution’s really solve the performance problem. And this is the conundrum Oracle performance tuning specialists face. I have a performance problem. I know on the other side of this maze is my solution. But which entrance do I choose? One entrance has Explain Plan written above it. Another entrance has V$DB_CACHE_ADVICE written on it. Why there are all these entrances, one for each tool at my disposal. This is a tale of my youth and hopefully, as Bilbo wrote to Frodo, this story can help you in your adventures as well.

So I pick an entrance.

I enter the maze.

Did I make a good choice?

Well let’s see where this goes. Up ahead, the road makes a left turn. But its my only choice so I go with it. Next, I come to an intersection. I can go right or left. I make a right turn. Oops…dead end. So I backtrack and take that left instead. Another dead end. Drats. I entered the maze incorrectly. Sometimes, the tools do not lead you to any solution. So I go back to the entrances and make another choice, chose a different tool.

I’ve now entered the maze a second time. But things are looking much better. I keep going. Just a few more turns. I can see light so I know I’m getting close to the end. Yes…there it is, the exit. I finally come out the other side of the maze. I have my performance tuning solution in hand but after I implement the solution, I quickly realize this did not solve my performance problem at all. Sometimes, tools can lead you to solutions that have no bearing on your specific problem. So its time for my third entrance into the maze.

Now, being an astute performance tuning specialist, I realized all they entrances I chose so far are related to overall database performance but what I’m really looking for is performance related to a specific SQL statement. But I do not know which SQL statement needs tuning. How can I figure out which one? Well three doors down is an entrance to the maze marked SQL Trace. Right next to it is a door marked EM Search Sessions. I flip a coin and choose SQL Trace. Shortly after I enter the maze, I come to a T-intersection. If I go left, it takes me back to the EM Search Sessions door. If I go right, its a straight shot to the exit. Naturally I go right. But it is at this moment that I know that sometimes, two different tools will lead you to the same answer. As I exit the maze I am given a free pass to tkprof because after all, don’t all SQL Trace roads lead straight to tkprof? I now have the offending SQL statement. But my problem isn’t solved yet. What to do?

I head back to the maze entrance. Sometimes, we get an answer from our tuning tools and we have to perform another run through the maze to drill down into the final answer. This time, I enter the SQLT door. A few twists and turns, but this maze path is pretty easy, or so it seems. I get to the end, and I not only have one answer, I have many answers. Oh…glorious day! I have found the mother of all tools.

I heard other DBAs speak of these miracle tools like SQLT and AWR Reports. How wonderful they are. These tools are so great, some DBAs only see the SQLT and AWR Report entrances.  I always thought this was stuff of legends, but here at last, I too have found the one tool to rule them all…ok…one for each hand. I have all of these answers at my disposal. Now which answer is directly related to my performance issue. Here I have my SQLT report and I have all these answers contained therein. Which answer is mine. Which one?!?!? Sometimes, tools will give you too much information. For me, who is new this this performance tuning thing, the output of SQLT might as well be written in Klingon. But lucky me, I know a fellow DBA that sits two cubes down from me that speaks Klingon. I hand him my SQLT output. He thumbs through it and within 30 seconds, he points out one tiny section of the report and says those magical words. “See…right there…that’s your problem.” With a quizzical look on my face, he waves his hand over the report and as if by magic, Google Translate has changed a few words on the page and I can now clearly see I have a table with very bad stats. Sometimes, tools with all those answers are great time savers for those that know how to use them. This Klingon speaking DBA pushes up his glasses and reveals another section of the SQLT report. “See here he says…those bad stats are forcing a FTS” as if I’m supposed to know what an FTS is at this point in my career. But I don’t want to seem like a total n00b so I smile and nod in agreement.

Ok…I’m getting closer to solving my problem. I know I have bad stats. I’m heading back to my desk eager to get to work to finally solve my problem. As I pass by the water cooler and go around the ever-present throng of my coworkers with nothing better to do all day but chat, the sun shines off one door to the maze and catches the corner of my eye…just one door. Above that door is a sign that says DBA_TABLES. Well like any good DBA, I say to myself that its not a bad idea to double check these things. Begin drawn to it, I enter the DBA_TABLES door and am once again in the maze. I make a quick turn and something jumps out at me as if to startle me. But I’m becoming good at this. I don’t care that some little maze dweller insists on telling me this table resides in tablespace USERS. I am quick to know that this makes no difference to my issue at hand. I push on and ignore all of these little imps with their spurious information. I press on. And there I have it…confirmation at the maze exit that there are no stats on this table. A quick lesson was learned here, sometimes, the tools will give you information that isn’t relevant to you on this day.

I may be new at this DBA game, but I do know this. I need to see how things are performing now, make a change, and measure the performance improvement if any. So I head back to the maze. This time, I enter the door marked SQL Developer Autotrace and I execute the offending SQL statement. I not only get the runtime of the SQL statement but I can see the number of reads and the execution plan. I quickly update the stats on the table my Klingon-speaking friend pointed out to me. (quick aside…I used to think he was a jerk but now he’s not so bad. I can learn from this guy. Maybe one day I too can speak Klingon). Then I enter the SQL Developer Autotrace door again. Not only did my query executing go from 2 minutes of execution down to 2 seconds, but reads dropped significantly and the Explain Plan improved. Ok, that last part is a bit of a stretch. I’m still too green to know the Explain Plan was better but looking back on it later in my career I know it was. I quickly learn that sometimes, the performance tuning tools are my disposal are not only there to help find the root cause of the problem, but are also there to confirm the solution actually fixed the problem. And sometimes, the tools to confirm the results are not the tools I used to find the root cause. 

I quickly informed my end user that the issue is resolved. The user grumbles something I couldn’t quite make out and checks to see if his life is actually better. And that’s when I receive it. The greatest gift a DBA could ever receive. That’s right… I received user adoration. Today, I am a miracle worker or so the user thinks.  As I am standing in this user’s cubical he shouts out “HE FIXED IT” and on cue, the entire department’s head pop up over the cubical walls like gophers out of the ground. Hurray..they cheer! I am loving life basking in the glow. Why the boss even offers to take us out to the pub after work..first round is on her.

I walk back to my desk, eager to take on the next challenge. This job could not be any sweeter.

I remember my first encounters with this Performance Tuning Maze as if it were yesterday. When we were joking over pints at the pub that night, I dared not speak of some of the things I saw in that maze. My coworkers wouldn’t understand anyway. I never tell anyone of my fights with the MOS dragons. I’ve been burned too many times. I never tell anyone how boring it is to run a query, wait an hour for a result, try again, wait for an hour, try again, wait for an hour..oops..I dozed off there. The trials and tribulations of my youth are better saved for another time. Maybe I’ll write another book.

But I learned a lot back in those days. Over time, I became better and choosing the best entrance to the maze for the problem at hand. After all, it is only with experience that one can get better with these magical performance tuning elixirs. I’ve also learned that sometimes, one tool seems to be the correct one for the job only to discover part way through the tuning effort that another tool is better suited.

I’ve also learned that it is only working with the tools and learning what they are good at and conversely what they are not good at, that I can best choose the appropriate tool for the job. Back in the day, If often felt like I was trying to pound a screw in with a hammer. Now I see a screw and know the best tool is a screwdriver.

Over time, I’ve grown the number of entrances to my performance tuning maze. I still go through the tried and true doors like with one with just a number above it, 10046. In the past, I’ve been told about magical doors that led to rainbows and unicorns only to discover yet one more grumpy old troll under a bridge. I was skeptical about Lighty being such a magical tool in the beginning, but I was wrong about that one.

Oh the tales I could tell you, but this story is really about that Performance Tuning Maze. It always comes down to that maze. Choose the best door possible, but only experience can tell you which one is best. That will let you arrive at your solution the quickest. Make a wrong turn and start over. Don’t be afraid to enter the maze multiple times. When you think you have the solution, go through the maze to verify. This magical Performance Tuning Maze with all of those wonder Oracle performance tuning utilities and tools has now become one of my favorite places to hang out. I like to add more entrances all the time, hoping that each new tool will lead me to the end of the maze much faster. Sometimes they do and sometimes they don’t.

I still remember the days when I used to hang out in the old “ratio-based tuning” maze, but I’ve moved on to greener pastures. I still chuckle when I see some new DBA standing in front of that old maze, covered in spider webs and they just can’t take the hint. And then I get cranky when I yell at them to forget about that maze and come over here where everyone else is hanging out, only to be spurned by someone who thinks they know better. Well if we ever see them again, we can say “I told you so” and have a good laugh.

I often work with people who see me using some of these shiny tools. They watch me enter the maze and come out the other side with the answer. So their obvious next question is “can I go in that door too?” I chuckle. “Sure…go right ahead”, I tell them. Armed with this cool tuning tool, but zero knowledge on how to tune Oracle, they make a pretty good, but feeble attempt. They call me over to the maze and ask me to help them solve the problem. So we fire up the tool and look at it. I instantly recognize the root cause of the issue, but the tool’s shiny bells and whistles are confusing the neophyte. At this point, I’m now speaking Klingon. Within seconds I say “See…right there…that’s your problem.” and I get back that same quizzical look I provided to my DBA mentor so many years ago. These novices always want access to the tools and think they can wield them like a master. They have no clue what is in the maze nor any clue how to navigate it. Too many people think the tools are the secret sauce when its really the person wielding the tool. Sadly, some people with access to the tools just want a quick and easy answer. They don’t want to put the time in like so many of us.

Time, time to follow the masters. We all have our version of Mt Rushmore. Carved in stone. People like Millsap, Lewis, and Shallahammer to name a few. Your Mt Rushmore may have other names or even similar ones. Others who view our Mt Rushmore, all set in stone, do not realize these fine people were our guides in the maze. They showed us how to navigate the maze. They showed us how to use the tools and which tools to use when. Those of us who learned from the masters try our level best to pay it forward and teach others, although we may never achieve such lofty heights, and that’s ok.

The moral of the story is to learn these tools, learn what they do and what they don’t do. Learn which problems they help tackle. Leverage the tools, but realize that you need to learn as much as you can so that you to can walk the maze with confidence. Sadly, I have to end my story here. Someone just came into my office with another performance tuning problem. Time to enter the maze again. Now which door do I take?


Jul 14

Yet Another Reason To Upgrade

As I usually do, I fire up lots of questions on the MOSC and OTN forums and pick and choose which ones I want to answer. This morning, I came across a post from a user who was asking questions about Oracle 9i. As expected, this user got the anticipated responses from the forum faithful about how old/ancient their Oracle version is and they should upgrade to a much more recent version.

Like others, I have written articles about reasons why you should upgrade. Here is one such article I wrote for SearchOracle.com:  Top Reasons To Do An Oracle Upgrade

The article above is not ground breaking and you can certainly find alternative examples from other authors as well. But in reading this forum post, it struck me that there is yet another reason to upgrade your Oracle database that I have yet to see anyone write about. That reason is:

I cannot remember the details about your very old version any more!!!

This morning’s forum discussion was referencing 9i. I haven’t used Oracle 9i since I upgraded everything to 10.2. Oracle 10.2 was released in July 2005. I do not have accurate documentation going back that far but I’m pretty confident I was running 10.2 across the board by July 2006, more than 10 years ago!

Technology changes and the pace of change is fast. I cannot be expected to remember all the intimate details of Oracle 9i (any of its versions), a version I have not used in 10 years or more. Now if you are asking me a question that is still relevant in today’s supported versions, then I can certainly provide an answer. But if the details of your answer are not applicable to today’s 11.2 or 12c versions, then I’m sorry but I cannot adequately answer your question.

In addition, I am not going to find the 9i documentation to cite in any response to your question. If I go to http://docs.oracle.com to look up something I may need to help answer your question, it gives me versions 11.2 and higher. I can choose Earler Versions, but my feeling is if you are too lazy to upgrade your database, then I’m going to be too lazy to go through that extra step of finding your version’s documentation. Another step I am unwilling to take is to download and install the old version and create a database so that I can attempt to reproduce your problem. If I cannot reproduce your issue in one of my current versions, then the answer is obvious…upgrade to resolve your issue.

The bottom line is to upgrade to a more recent version so that you can enjoy obtaining help on whichever forums you post your questions to.I’m not the only one that answers questions on MOSC/OTN but I’m also not the only one that feels this way.

Stay current with your versions so that you can interact with your peers no matter how you discuss Oracle.

Jun 29

ORA-16789: standby redo logs configured incorrectly

The DG Broker is reporting some warnings for one of my databases in the Broker configuration.


DGMGRL> show database orcl

Database - orcl

 Intended State: TRANSPORT-ON

Database Warning(s):
 ORA-16789: standby redo logs configured incorrectly
 ORA-16789: standby redo logs configured incorrectly
 ORA-16789: standby redo logs configured incorrectly
 ORA-16789: standby redo logs configured incorrectly
 ORA-16789: standby redo logs configured incorrectly
 ORA-16789: standby redo logs configured incorrectly
 ORA-16789: standby redo logs configured incorrectly

Database Status:


If these warnings were seen in the standby database, the most common reason is that the Standby Redo Logs are smaller than the primary’s Online Redo Logs. But note in the above output, this is from the primary database.


The reason for these warnings is quite simple…my primary does not have any SRL groups. Once I added the SRL groups, the warnings went away.

Jun 29

ORA-01264: Unable to create logfile file name

I am in the process of replacing production hardware for an Oracle RAC database. To do this with zero downtime, I am adding the new nodes and then extending the RAC database to run on both old and new nodes. Then I will remove the old nodes once they no longer have any database connections.

When adding new nodes, I needed to add additional threads of redo to support the new instances. This means I needed to add new Online Redo Log groups for those threads. In my haste to get this project completed, I forgot to take into account my physical standby database. Once the standby received the redo records to create the ORLs, it promptly threw the following errors:

Thu Jun 29 14:17:44 2017
Media Recovery Log /u01/app/oracle/admin/orcls/arch/7_63989_677462342.dbf
No OMF destination specified, unable to create logs 
Errors with log /u01/app/oracle/admin/orcls/arch/7_63989_677462342.dbf
MRP0: Background Media Recovery terminated with error 1264
Thu Jun 29 14:17:44 2017
Errors in file /u01/app/oracle/diag/rdbms/orcls/orcls1/trace/orcls1_pr00_22818.trc:
ORA-01264: Unable to create logfile file name
Recovery interrupted!
Recovered data files to a consistent state at change 259717189001
Thu Jun 29 14:17:45 2017
Errors in file /u01/app/oracle/diag/rdbms/orcls/orcls1/trace/orcls1_pr00_22818.trc:
ORA-01264: Unable to create logfile file name
Thu Jun 29 14:17:45 2017
MRP0: Background Media Recovery process shutdown (orcls1)


Silly me. Thankfully this is an easy fix. On the standby, issue:

SQL> alter system set standby_file_management=MANUAL scope=memory;

System altered.


Then watch the media recovery take place. We will see a warning in the alert log that the ORL will not be added, which is okay because the standby already has its ORLs.

Media Recovery Log /u01/app/oracle/admin/orcls/arch/7_63989_677462342.dbf
WARNING: standby_file_management is set to MANUAL
Online logs not added for newly enabled thread 1 

Once sufficient recovery has been applied, I set the parameter back to its original value.

SQL> alter system set standby_file_management=AUTO scope=memory;

System altered.

I could get away with doing this because there were no other file management tasks completed during the time the ORL’s were added to the primary.

May 18

Increase VOTE diskgroup in ASM for GI 12.2 Upgrade

Oracle 12.2 requires more disk space in your Voting Disk/OCR area than previous versions. In the past, I had a pretty small diskgroup for Vote and OCR. Here are the instructions I followed to setup Oracle RAC on my laptop for 12.1. Before I can upgrade to Grid Infrastruture, I need to add more space to my VOTE diskgroup. This is pretty easy since I am running on Oracle VirtualBox.



To add more space, I will add another disk file shared on the nodes and then add that disk device to my ASM diskgroup. First, I shutdown my hosts. Next, in Virtual Box Manager, I select host01 and then click on the Settings button. I then click on the Storage button.

I can see my VM’s internal disk (host01.vdi) and the three disk files I have for my shared storage as outlined in my original setup document I linked at the beginning of this post. I will click on the second plus sign to add a new hard disk. On the next screen, I click on the Create New Disk button.

In entered the file name and made sure the location is correct. I also made sure this file was big enough for my new requirements. I pressed the Create button to create the hard disk on this node. Before I go farther, I need to make this file shareable over the nodes. In File –> Virtual Media Manager I can see the file I just created. I select it and click on the Modify button. I choose the option to make this file shareable and press Ok. I then press Close.

Next, I add this harddisk to host02. I select host02 and press the Settings button. In the Storage section, I press the same icon to add a new harddisk. This time, I choose Choose Existing disk. I navigate to my file and press OK to add it to the node. I now startup both nodes.

Once everything is back up and running. I verify the new disk device on both nodes. On both nodes, /dev/sde is now present and this is my new hard disk.

[root@host01 ~]# ls -l /dev/sd*
brw-rw---- 1 root disk 8,  0 May 10 09:22 /dev/sda
brw-rw---- 1 root disk 8,  1 May 10 14:22 /dev/sda1
brw-rw---- 1 root disk 8,  2 May 10 09:22 /dev/sda2
brw-rw---- 1 root disk 8, 16 May 10 14:22 /dev/sdb
brw-rw---- 1 root disk 8, 17 May 10 14:22 /dev/sdb1
brw-rw---- 1 root disk 8, 32 May 10 14:22 /dev/sdc
brw-rw---- 1 root disk 8, 33 May 10 14:22 /dev/sdc1
brw-rw---- 1 root disk 8, 48 May 10 14:22 /dev/sdd
brw-rw---- 1 root disk 8, 49 May 10 14:22 /dev/sdd1
brw-rw---- 1 root disk 8, 64 May 10 14:22 /dev/sde

First I need to format the disk. I use “fdisk /dev/sde” and answer “n”, “p”,”1″, default, default, “w” when prompted. I now add this disk in ASM.

[root@host01 ~]# oracleasm createdisk VOTE2 /dev/sde1
Writing disk header: done
Instantiating disk: done

I use ‘oracleasm listdisks’ on both nodes to verify the VOTE2 disk has been added to both nodes. If the VOTE2 disk is not on host02, issue “oracleasm scandisks” to have it find it. I rarely use ASM, so for this I used the asmca utility to add the disk to the diskgroup. In the Disk Groups tab, I right clicked on the +VOTE disk group and chose Add Disks. I could see my new disk device so I selected it and pressed OK. I can now see the +VOTE disk group has plenty of space.

That’s all there is to it. My VOTE diskgroup is now much larger and I can proceed with my GI12.2 upgrade.


May 15

Surviving an Oracle Audit

I recently had the experience of being audited by Oracle Corporation. Nothing quite prepares you for what to expect until you have actually gone through the process. The best advice I can give anyone is to start working today as if you are undergoing an audit. If you are not thinking about being in compliance with your license agreement, by the time an audit is sprung on you, it may be too late.

To be prepared, the DBA should be well-informed of Oracle licensing policies. Reading the Oracle License Guide is a must, but it is only a start. There are many things that are not published. For example, you may not be aware that if you are running Oracle on VMWare ESX 6 or higher, that you need to license all ESX clusters across your enterprise. Oracle is also changing terms such as licensing on non-Oracle cloud services. It is incumbent on today’s Oracle DBA, especially with the cloud changes, to keep on top of Oracle’s licensing stance. Licensing can even change between versions so the DBA needs to keep track of the differences. If a support contract has lapsed, the company cannot legally upgrade their database to a version released after the contract has expired.

Once the DBA has a good feel for the licensing terms and policies, they should then determine the company’s current entitlements. Is the current license agreement “per processor” or Named User Plus (NUP)? How many cores are licensed? Even NUP licensing has to take into account the number of cores. Are optional features being used that have not been paid for? The DBA_FEATURE_USAGE_STATISTICS view can help understand which features have been used in the database and compare it to what is licensed for that database. The DBA should perform their own internal audit and make sure the environments they are in charge of are compliant with their Oracle contract. If their Oracle environment is not in conformance with the contract, the DBA needs to take steps to address the issue.

If the company wants to make sure they have everything covered in advance of an audit, Oracle has a division called License Management Services (LMS) that will help, for a fee. Oracle LMS will help understand what the company is licensed for and how to true-up or remediate to be in compliance with the contract. There are third party vendors that will also provide similar services.

When an audit starts, you will be asked by Oracle LMS for two things. One, they will ask you to fill out a detailed spreadsheet showing exactly where you are running Oracle, what options are in use, and information about the environment. Two, they will ask you to run a script to harvest license details from your systems. The script will need to be run on each and every Oracle machine in the enterprise.

This stage is where the nervous part really begins. Oracle LMS will be combing through what the company has paid for and trying to determine if the usage is in compliance. There is always fear and trepidation wondering what was missed on our end and what they might find.

It is well understood by many in the Oracle community that audits are used to drive sales. In the past, this meant a company could make bad audit findings magically disappear if they agreed to buy some new products. In today’s cloud-enabled world, many companies are finding that Oracle audits are being used to drive sales of cloud services. It is up to each company to decide if they want to fight the findings in court or to work to reach some other agreement with Oracle. For many, a court battle is even more costly.

Oracle audits can be a scary time in the DBA’s life. Be prepared by doing your homework upfront. Work to make sure your environment is as compliant as it can be. Time spent today will go a long way towards making the audit go more smoothly in the future.