One of the customers I’ve been working closely with over the past few months is running Standard Edition (SE) 18.104.22.168 (yes, I know they should upgrade but that’s coming further down the road). Recently, we moved their database to another, much larger ASM diskgroup (that’s the subject of another post that I haven’t written yet), so we now have the opportunity to add some more space to their existing tablespaces, which were mostly running close to full (as was the original diskgroup, for that matter). Now my experience of SE has not been particularly long, and I knew they were running a manual standby configuration like people used to use before Data Guard came around. Data Guard, of course, is an Enterprise Edition feature, so that’s not an option for this particular customer. I wondered what would happen to the standby when I added new datafiles. Would it resolve it automatically, or would I have to do some jiggerypokery on the standby to get it to recognize these new datafiles? Well, that’s what I’m going to cover in this post.
Obviously, this is a customer environment so I’m not going to be sharing customer names or anything like that. So with a bit of obfuscation, here’s the fairly simple customer environment:
On the left, we have the primary database, and we have a script that manually copies archive logs to the standby on the right every 3 minutes. As far as the standby configuration is concerned, STANDBY_FILE_MANAGEMENT is set to “AUTO”:
Adding the Datafile
In this particular case, the SYSAUX tablespace is currently composed of 7 datafiles, but the tablespace is 97% full due to a known bug with unified auditing:
On the standby site, we can see there are also 7 datafiles for the SYSAUX tablespace:
Now let’s add an 8th datafile on the primary. At the end of that operation, the primary now shows 8 datafiles:
After a short while, we can see an extra datafile appears on the standby. But look at the name and location! I can’t even restrict the query to look for SYSAUX any more!
So we now have an unnamed datafile, which isn’t following our naming conventions any more. Great! Do I have to rename it or something? Well, I went off searching for the syntax on how to do that, which took me a couple of minutes to find. I came back to the session and reran the command, just to be sure and look what row 39 shows me now!
The end result is what I wanted to happen actually did! We added a datafile to a tablespace on the primary database running Standard Edition, and it magically appeared after a few minutes on the standby with the correct name format. Luckily, I double checked as when it first appeared on the standby, it was not correctly named but shortly thereafter the standby database did rename it for me.