This is another of the posts on how I did something to remind me how to do it again, when next the need arises. 🙂
Situation here was a customer who was running out of space in their Production database due to the unified audit trail bug in early versions of 12.1. Their SYSAUX tablespace had blown out to 186 GB, and their ASM diskgroup had run into negative space territory (i.e. if a disk failed, it would be unrecoverable):
That’s not a comfortable place for anyone to be! So we decided to create another diskgroup and move the SYSAUX tablespace to it. The reason for creating another diskgroup is that the performance characteristics of the two sets of disks were sufficiently different that we didn’t want to have them all in one diskgroup. In this example, we’ll call the old diskgroup +DATA and the new diskgroup +DATA2.
The other variable we had in this particular case was the customer who ran into this situation was running Oracle Database 12c Standard Edition. That meant that none of the online move functionality of 12c was available to us, as that all needed Enterprise Edition.
Firstly, we needed to create a new diskgroup. The sysadmins at the site provisioned 4 devices for us as /dev/mapper/asm01, /dev/mapper/asm02, /dev/mapper/asm03 and /dev/mapper/asm04. Lets have a look at what the existing disks are called so we can follow the same naming convention:
OK, let’s create four more disks in ASM:
And just to double-check:
Now let’s swap over to the oracle account, as that’s who owns ASM for this site:
OK, now let’s create the diskgroup and recheck the ASM disk statuses:
So now we are almost ready to go, but of course an operation like this really should include a backup before the changes are made:
Copying SYSAUX to the New Diskgroup
Our next step is to use the “backup as copy tablespace” command to start moving the tablespace to the new diskgroup. In this example, the SYSAUX tablespace is composed of multiple datafiles, which will be copied as part of this one command:
Next, I need to switch the datafiles to this copy. However, SYSAUX is considered a “system” tablespace so this can only be done with the database mounted but not open, so I first need to shutdown the database then do a startup mount command:
Now I can issue the switch tablespace to copy command successfully. Notice this switches all the datafiles in one command:
Next, we need to recover the database:
And now we can restart the database:
And just to check that the files have all been moved:
We can see the SYSAUX datafiles are now all on +DATA2. While the active datafiles are now located on +DATA2, because we did this as a copy operation the original files are still located on +DATA and need to be removed. Depending on your requirements, this can either be done by dropping the diskgroup entirely, or dropping the individual files manually.
Notice the value of “Usable_file_MB” has not changed. Looking at the old +DATA diskgroup, we can still see files belonging to SYSAUX. Once we have removed those, you can see the diskgroup free space changes: