Jan 04

Monitoring Online ASM Datafile Moves

I’m moving a ~7TB database online as the development group that uses it doesn’t want to take any down time. Thankfully the database isn’t horribly active right now so moving the data files online isn’t causing a huge problem. The only other times I have moved data files online was in an incredibly busy database and it could take half a day per data file.

I don’t want them to continually bother me so I gave them a view that they could monitor the process through. Here are the caveat, I know when the move started, that is hard coded, also my volume names are easy to tell if they are on flash storage or spinning media as they are two different asm pools, so extracting the second character of the file name yields a ‘D’ or an ‘F’ where D is spinning media and F is flash media.

Then they can check on the stats for themselves

It’s not pretty but it gets the job done, leaving me with less contact with people, which is great 🙂

Oct 17

Reporting Database Sizes

I frequently need to pull size information about a large number of databases. OEM has an interesting capacity planning feature, but some times I just want the raw data. This is the base query I start with and add on additional information or slice it in different ways to fit what I need. I run this in the OEM repository.

This gives you the size, by database as of the previous day.

Jan 23

ORA 600 [kcbAdoptBuffers_pw]

We hit this bug the other day. The oracle note references creating an index as part of this bug. As far as I can tell, we weren’t doing that when we hit this. It is a vendor application though, and I know they do some DDL such as creating tables on the fly. I was never able to reproduce the bug at will, but I was getting flooded with these alerts.

Oracle support recommended we change the _db_cache_pre_warm parameter to false

We changed it to false, and did a rolling restart of the database

after restarting

The errors have stopped. This appears to be bug 21864012

Dec 07

Setting up SSL ACL in 12cR2

Before you can connect to a website you need to setup the oracle wallet. Start by going to the website you are going to be connecting to. In this case I am just using my own site. I am also using Safari on OSX, the steps differ slightly depending on operating system and browser.

After clicking the padlock icon, and show certificate, we represented with the certificate chain. The root certificate is trusted by the browser, which then is used to validate the intermediary certificate which belongs to the encryption authority, in this case it’s Let’s Encrypt. Lets Encrypt in turn validates the certificate for my site. When oracle tries to connect to balddba.com it will need the intermediary and the root certificate to validate the server we are connecting to.

When I download the certificate we need to convert it to base-64 encoding so that we can handle it like a text file.

Convert the certificates to base-64

On the database server, create a folder to hold the wallet. Since I am doing this on the oracle cloud, and I only have one database SID, and the database is not RAC, I am not worrying about putting the wallet into a dynamic location. If you want to have a wallet per sid, you can use the $QNAME variable in the sqlnet.ora

Modify the sqlnet.ora to point to the new wallet directory

Create a new auto login wallet using orapki

Add the root and intermediate certificates to the wallet

Then validate the certificates are in the wallet

Now that the wallet is setup with our certificates,

I am starting off by creating myself a new user, so that I am starting from scratch and not using the dba privileges.

Give the user access to connect and to use the url_http package

We create a new ACL for the host, and for the wallet

Now we can test the connection

There is something I think should be pointed out, there is a change from 12.1 to 12.2. utl_http.request takes in a new argument called https_host.

I was getting the following error

There is now another parameter that needs to be set, the  https_host has to match the common name in the certificate.

Here is my certificate

So after adding the arguemnt I am able to get to the page




Nov 30

13C OMS and TLSv1.2

A while ago I deployed OEM 13c to manage and monitor our databases. We have a lot of different systems on different architectures and operating systems. For the most part the move from OEM12c to OEM 13c was pretty smooth. As part of this process we were instructed to lock everything down to TLSv1.2, which is a huge pain inside of OEM. All the internal connections from the different components and nothing was listed in the documentation together. It took a lot of trial and error, but once it was setup, the deployment of the agents went just fine.

At least until I got to our AIX hosts. This is one of the longest open tickets I have ever had with oracle. Bug:23708579. After 9 months Oracle finally got me a patch that resolved the problem this week.


From the log files

There was a lot of confusion over this issue and originally they told me it was a bug in AIX that we would need to get IBM to fix. It turns out the patch that was needed was an agent patch

After applying patch: 25237184 the agent can now be locked to TLSv1.2 by adding the following to emd.properties

and then by re-securing the agent with the “-protocol tlsv1.2” flag

After this the agent was able to start up and start communicating with the OMS.


Oct 12

Loading Execution Plan for AWR

Create a new tuning set

Load the execution plan into the tuning set

Apply the plan to the statement



Aug 31

ORA-31617: unable to open dump file “/export/data.dmp” for write

This morning a user came to me because they were getting what appeared to be a Permission error

I checked the directory listed, /nfs/unix/export. It is mounted on both nodes of the cluster.

Checked file permissions, using touch I created a file as the oracle user, and deleted it, no permissions issues there.

It turns out it was the directory object itself! I looked at the job definition, and it was using the directory /export

Export is a link to the /nfs/unix/export directory. The second node had link, but not the first! What threw me off was the directory object itself was only pointing to /export, but the error message was pointing to the full NFS path. Dropping and re-creating the directory object in the database pointing to /nfs/unix/export/ resolve the issue.

Aug 31

RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process

On a non-production database I was getting the following error when trying to delete archive logs with RMAN.

This database does not have any golden gate processes associated with it, so why doesn’t Oracle want to delete the  archive log?

The database was rebuilt with a copy of a production database that did have golden gate running on it.

We can see the name of the extract that is still registered with the database.

Since the database was a copy of our production database that did have golden gate running, it till thinks there is a valid extract. Connecting to the database and unregistered the extract allows the archive log to be deleted.

UPDATE 11/13/2017:

One additional helpful command if you don’t have goldengate installed on the host where you are getting this error, you can remove the extract directly from the database

Update 08/10/2018

I usually just generate the code I need to run like this since I usually want to drop all the captures


Aug 31

ORA-02396: exceeded maximum idle time, please connect again

I was doing a schema import last night, over a database link. Lack of sleep had set in and I was getting the following error

Now if you aren’t tired this should be a pretty obvious fix, the idle_time value on the remote server was set to 60 and was disconnecting me during the index build section of the import. I altered the profile to give my user/profile unlimited idle_time. Problem solve, import complete.

Aug 07

RMAN-20036: invalid record order

Trying to register a database with my rman catalog I was getting the following error

Poking around metalink I found note 113325.1

Basicly it says there is a mismatch in the number of standby redo logs, but this database does not have a standby. However it was cloned from a database that had a standby, and I apparently forgot to remove the standby redo logs after I cloned it.

So cleanup the standby logfiles

I dropped and re-created the catalog


Older posts «

» Newer posts