Fresh off the heels of my earlier composite partitioning post, I just ran into this confusing issue:SQL> alter table p_objects 2 add partition p201410 3 values less than (to_date('2014/11/01','yyyy/mm/dd')) 4 ( 5 subpartition p201410_spdts values ('DTS') 6 (more...)
As part of a project for work I wanted to create a script that would create a new range partition but also pre-create all the list subpartitions. By default the subpartitions would be created based on the subpartition template. However for various reasons which I won't get into we don't update or use the subpartition template. I wanted to define the subpartition list as part of the ALTER TABLE ... ADD PARTITION statement. I assumed it (more...)
A few weeks ago we upgraded one of our production server, adding another CPU tray. This brought the number of CPU cores from 80 to 160, and took us from 2Tb of RAM to 4Tb (just in time for Oracle to announce the in-memory database in 220.127.116.11!).
However when I went to start things up, ASM wasn't starting up, giving me these errors:ORA-04031: unable to allocate 32 bytes of shared (more...)
When the latest quarterly Patch Set Update (PSU) came out from Oracle, we planned to get it in place as soon as reasonable due to our need to stay current with security patches, and my need to apply what I had assumed were well-tested bug fixes for our 18.104.22.168 installations. However we were in for an unpleasant surprise.
We first applied the April 2014 PSU on our staging & development database hosts (more...)
TL;DR: The archive_lag_target parameter will force log archiving in Standard Edition.
Just a quick note here that I wanted to share since I didn't see anything directly confirming this when I was searching around.
I have an Oracle 11gR2 Standard Edition (SE) database that I'm also maintaining a manual standby for, since Oracle Data Guard is not available in SE. I created a metric extension in EM12c to alert me if the standby is more (more...)
A few weeks ago, our customer support team asked us to automate part of their checklist that looks at the number of active sessions in our production database. In EM12c, this seemed like a no-brainer with the Average Active Sessions metric. So I added this to my production incident ruleset and went back to another project. Over the next few days we'd get pinged by EM12c but the support folks would say it shouldn't. After (more...)
A few weeks back, we began making changes to prepare for using Oracle Golden Gate. One of the first steps required is to enable "minimal supplemental logging" at the database level. We did this during an evening maintenance window. However by the time the morning workload picked up, we started seeing a lot of sessions blocking, and the root blocker was one of the DB Writer (DBWn) processes.
Looking at the blocked sessions, a query (more...)
This week I migrated our EM12c repository database to a new server as part of it's promotion to production status. Just to make it a little more exciting, the migration also involved an in-flight upgrade from 22.214.171.124 to 126.96.36.199. Much of this post is directly inspired by Martin Bach's post on the same subject
. I ran into a few other snags that weren't mentioned so I thought it would (more...)
Last Friday I kicked off a database backup to an NFS destination, using the standard "backup as compressed backupset database" syntax. Loyal readers of this blog may recall that I'm the proud custodian of a 25 Tb database, so this backup normally takes a few days, with an expected completion on Monday morning. However it was still running on Wednesday, and reviewing the logs I saw that there was just 1 channel (of the original (more...)
I recently had to change the db_unique_name of a database to make it jive with our typical database/DataGuard naming policy of appending the datacenter location. For the sake of this post let's say it was changed from ORCL to ORCL_NYC, since this database is in our fictional New York City datacenter.
I did a quick set of tests and thought I'd share the findings to save anyone any unpleasant surprises. Here are the things to (more...)
Readers of an earlier post on this blog
will know about my latest forays into the world of Direct NFS. Part of that means stumbling over configuration hiccups or slamming into brick walls when you find new bugs.
To quickly re-set the table, my organization purchased the Oracle ZFS Storage Appliance (ZFSSA) 7420
. Oracle sold us on the Infiniband connectivity as a way to make a possible future transition to Exadata easier. However the pre-sales (more...)
When I sat down at my desk yesterday morning I was greeted with some disturbing email alerts notifying me that one of the NFS mounts on my standby database host was full. This was the NFS mount that held an image copy of my database that is updated daily from an incremental backup. The concept and an example can be found in the documentation
. With a 25Tb database, waiting to restore from backups is not (more...)
When adding ZFSSA targets to #EM12c
, be sure to uncheck "kiosk user" for the oracle_agent user in ZFSSA. Doc ID 1609846.1https://twitter.com/dtseiler/status/428210917155692544
We recently upgraded our EM12c 188.8.131.52 installation to 184.108.40.206. This service was configured to authenticate logins against our Active Directory server via LDAP. However, after upgrading we found that authentication stopped working, even after recreating the Active Directory Authenticator (as required). I could see the list of users from AD, but could not authenticate them in EM!
In this post I'm going to share what we did to diagnose (more...)
When I started out as an Oracle DBA on Oracle 7.3, nearly everyone would tell you to avoid doing a "shutdown abort" if at all possible since it leaves the database open to possible corruptions. In the years and versions since, that thinking has softened greatly, to the point (more...)
Yesterday I was creating a new Oracle 220.127.116.11 database from a copy of datafiles and archivelogs taken from our standby. I was sure to include archivelogs from just prior to well after the span of the datafile backup time. I had created a new controlfile, gotten everything (more...)