Thursday, September 8, 2016

EBS -- RAC/ASM aware auclondb.sql

auclondb.sql is something we use in EBS migrations.  I have used it in many EBS on Exadata implementation, during the migration phase.
It is basically the to create script (aucrdb.sql) that creates a database with tablespaces and file structures similar to the database against which the script is run.
However it is not ASM aware. That is  the auclondb.sql creates the database creation script perfectly, but it names the datafiles without considering ASM.
As you recall, when we create a datafile in ASM environments (like RAC /ASM and Exadata), we just name them as the name of the diskgroup (for ex: +DATA) and leave the Oracle to store them in the related ASM directories with the related ASM filenames.
The auclondb.sql however, creates the tablespace creation scripting using a format like Diskgroup/File_name.dbf. So , altough this is not a big problem, it is not a good practice in my opinion.
This is because, when we use the script (aucrdb.sql) created by the auclondb.sql to create our target database on ASM, the aucrdb.sql instructs oracle to create a file with a filename. So ASM does not like this, it is not designed for this. So, what ASM does for making aucrdb.sql happy, is to create the ASM aliases for the datafile names provided by aucrdb.sql and but still uses its own file naming in the backend.
This ends up with having lots of ASM aliases on ASM filesytem and it just looks dirty (consider there are lots of DBs created in this way). Also, getting rid of these aliases is another problem, as they are recorded in the controlfiles, so controlfiles must be recreated after deleting these aliases.

So, what I recommend is to modify the auclondb.sql (which is not supported, but I don't see any harm in this, as long as it is done appropriately) to give the datafile names according to the ASM.
I actually won't share you the modified auclondb.sql, but I did this and it works.
With just a little modification, auclondb.sql can be changed in a way to create the database creating script aligned with the ASM. That is , it can be modified to create the datafiles using only the diskgroup name(For ex: +DATAC1), thus giving the file naming and directory pathing to ASM . 

This is just a hint. Keep that in mind :)

No comments :

Post a Comment