The Enterprise Manager AWR Warehouse is designed to hold performance data from multiple databases for long-term analysis. It promoses that it will save storage and improve performance on your production systems. In that it is indeed correct. However the warehouse itself does not seem to be performant when taking in multiple sources and retaining them long-term – 400 days in our case. Why 400 days is an obvious question that might be asked. Primarily because we are a Retail organisation and Easter is variable each year.
I am currently interested in managing storage on our Exadata platform which is primarily used for running a data warehouse.
The ongoing challenge is that most of the large tablespaces use a bigfile datafile and we insert data using append. That causes data gaps within the datafile and it is constantly growing. A recent example showed a 7Tb datafile containing only 2Tb of data and yet the ASM diskgroups were reporting we were running out of space.
Today an application team had a problem with a production system and they wanted the database restored to a point a couple of days ago. It is a configuration management system and they figured they could lose any changes . Their call.
Back in Nov 2015 I commented on a Oracle-L discussion about What happened to SQL*Developer SQL Formatter asking why people liked to see a comma at the beginning of the line in sql code and there was a fair bit of input into how it was easier to use and amend – think of it like bullet points was one comment.
Fair enough I though and I have used that technique when I remember ever since.
I saw this from an Oracle account manager who I deal with.
Oracle’s regional price lists are based on a constant Dollar based price list, and these are usually recalculated at the beginning of each financial quarter. Given the recent sharp decline in the £ against the $, I’m expecting an increase of roughly 15% for Oracle’s next financial quarter, beginning September.
For illustration, a single Oracle DB EE license has a current GBP list price of £31,597. Based on the constant Dollar price list and today’s exchange rates, this price should be £36,862.
I have recently taken over our Systems Management team and we have several vacancies for people used to working with the HP toolset. I know my blog is read almost entirely by DBAs but I am sure some of you work with Systems management teams and hopefully you can pass a link on to anyone you think might be interested.
This isn’t directly Oracle related but it did come to light within an Oracle context
We had a problem with a password file in a RAC cluster and whilst they appeared to be the same on both nodes of a cluster there were issues. I knew we needed to check the size of the files and ensure they were the same but I could not remember the (l)Unix command.
I knew it was to do with checking the bytes in a file and for some reason I was thinking it was to do with md5. Our sysadmin told me of the apropos command.
After upgrading to SQL Developer 184.108.40.206 a number of users noticed that some connections in the left hand pane were greyed out. I had the same experience for myself. There did not seem to be any pattern as to why specific connections were chosen and not others. Once greyed out they remained greyed out permanently – the selection did not change
I have not been able to find out an answer as to why it is happening, other than it is a bug/feature of that release.
My colleague John Evans sits next to me and after hearing another grumble encountered while using the 12c OEM AWR Repository I asked him to write-up some of the issues he had come across and their fixes / workarounds. It does appear to me that the idea is fantastic but it is not been given sufficient exposure to sites that would be wanting to put a lot of data into it from various sources and therefore a lot of the necessary functionality is not robust enough as yet. This is John’s summary
I have looked at the ORAchk tool before and thought that I must do something with it and I have now had a more detailed review of what it offers.
Firstly the history, it started off as RACchk and then morphed into ORAchk with a sibling called EXAchk. The recent release version is 220.127.116.11.5 which is available for MoS Doc_Id 1268927.2
It can perform an ever-growing list of checks against database, OS, specific applications (EBS and Peoplesoft being two)and various permutations of all that. It is very customisable, the usage command output runs to 281 lines !!
I like the memory advisors that are available from the OEM GUI or the direct database tables themselves. If I want a quick overview of whether a memory area (shared_pool, cache or the overall memory target itself) needs reviewing look there first. Note that I did not include PGA here specifically, I am well aware that PGA is included with the MAX_MEMORY_TARGET value – I am not considering that view in my discussion here because it is so subjective to the application and what the users are doing and the code being run.
This blog has the aim of answering quetions about how a sql statement has performed and which plan it has used. I have asked the type of questions that we as DBAs normally have to answer and shown how an answer may be arrived at.
We know the SQL_ID and we want to see what has been happening
I posted a routine on how to take a cold backup locally to disk and then restore it back in 2010. Last week I was asked in a comment ‘why did you have to open the database using resetlogs?’ A very good question I thought so I proceeded to backup and recover just as the blog showed and I now know why.
Because Oracle will not let you do otherwise
Let me run through the example again and I will add a bit of commentary.
As part of ongoing security reviews I wanted to determine if all database links on production systems were in use. That is not very easy to do and this article is a listing of some of the options I have considered to get that information and how it is now possible from 11GR2 onwards.
I think most large sites who have multiple support teams are aware of how the phrase “Segregation of Duties” is impacting the DBA world. The basic principle, that one user should not be able to, for instance, add a user, grant it privileges, let the user run scripts and then drop the user and remove all log files is a sound one and cannot be argued with.
With the release of 12c Oracle e added three new users to perform administrative tasks. Each user as a corresponding privilege with the same name as the user, which is a bit confusing.
We have a number of updates to partitioned tables that are run from within pl/sql blocks which have either an execute immediate ‘alter session enable parallel dml’ or execute immediate ‘alter session force parallel dml’ in the same pl/sql block. It appears that the alter session is not having any effect as we are ending up with non-parallel plans. When the same queries are run outside pl/sql either in sqlplus or sqldeveloper sessions the updates are given a parallel plan.
When you create a database in 12C it now creates a resource in HAS/CRS , which isn’t a problem
However, when you come to recreate a standby database, probably because it has got such a big lag that it is quicker to recreate than recover the log files, then you will see the following error message :-
The following technical detail was put together by a colleague John Evans and have taken it , with his permission, and wrapped some more detail around it as it seemed to be of real value to anybody who might have upgraded an agent to 18.104.22.168
Following an upgrade of the EM agent from 22.214.171.124 (or 126.96.36.199) to 188.8.131.52 after about 90 days of usage we saw a number of agents failing with out of memory errors.
This morning I will pass the 1 million mark for hits on this blog. My first post was written in 2008 and I remember being quite pleased with myself when I reached 5000 hits, I never dreamt of getting 1 million.
There are 5 parameters that are all prefixed with ‘sec’ in an 11g and 12c database. Actually that is a lie because one is now deprecated in 12c. They are all, as you might guess related to security. This blog is about changes in the default values and some thoughts about whether or not the default value is appropriate or not.
The focus on this post started off in one direction and ended up in another. Originally I had been running a drop user script which had hung and even when I killed the process I could not drop the users as it gave a “ORA-01940: cannot drop a user that is currently connected” – despite the users having left the company months ago and there being no chance of them actually having connected sessions.
Two posts from me on the same day. The other one about Datapatch is about a brand new utility in 12c and is probably new to most people. This post caused mixed reactions when I mentioned it at work last week. Some people laughed at my naivety in not knowing about it, others took the same view as me and were interested to hear about it as it may prove useful one day.
There have been a few changes in the way patches are managed and monitored in 12c and whilst looking at this I found a potential problem that might occur when you clone or copy databases around, or even build them from a template file.
Firstly when you apply a PSU and run an opatch lsinventory command you now see a description of the patch rather than just a patch number – here showing that PSU 1 has been applied. This came in at 184.108.40.206 and in my opinion is really helpful.
I noticed the error message when running lsinventory against a 220.127.116.11 Oracle_Home. As the command worked I didn’t think anymore of it until on the same server against an 18.104.22.168 home I got the same error message.
tr: extra operand `y'
Try `tr --help' for more information.
/app/oracle/product/22.214.171.124/dbhome_1/OPatch/opatch: line 384: [: =: unary operator expected
There is a Mos note which provides a solution – 551584.1
I recently found out that it is possible to change a database link password without dropping and recreating a database link in its entirety.
To be honest I thought this might have existed forever and I had just never come across it but it actually come out in 11GR2
The ALTER DATABASE LINK statement can be used and you do not need to specify the target service either – all you need is to run the following command from the user that owns a pre-existing database link
ALTER DATABASE LINK JOHN connect to USER identified by PASSWORD;