Moving SYSAUX from one diskgroup to another

Introduction

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.

Pre-Work

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:

Clean-up

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:

Pete

After 22 years of working at Oracle in just about every role except Marketing and Support, I am now working as a Senior Managed Services Consultant with Red Stack Tech, specializing in Oracle Database technology, High Availability and Disaster Recovery solutions.

I am also a member of the OakTable Network, and have presented at RMOUG Training Days, Hotsos Symposia, Oracle OpenWorld conferences, and other user group events. I have co-authored the Expert Oracle Enterprise Manager 12c and Practical Oracle Database Appliance books published by Apress, and am one of the authors of the Building Database Clouds in Oracle Database 12c book published by Addison Wesley.

Leave a Reply

Your email address will not be published. Required fields are marked *