I currently work on a 4-node cluster with 220.127.116.11 Grid Infrastructure installed, hosting several 11g and 12c databases. As it is a cold failover cluster, databases are not RAC databases and are active on only one node at a time.
Thoses databases share the same action script. The “start” section of this action script is quite complex and also manages the relocation of the database target on Enterprise Manager 18.104.22.168. But this specific task is time-consuming (approximately 30 seconds for 1 target) and is not always successful. Which means I often have to relocate some targets in Enterprise Manager by hand.
Recently, 2 nodes of our cluster did not survive a major storage outage. After their reboot, the databases restarted (quite slowly) according to the action script. But target relocation did not work as expected for all targets and I had to manually fix the mess.
For a few months, I have been working on massive migrations of Oracle 10g/11g databases on Solaris to 12c on Linux. Most of these databases are highly consolidated with several schemas (no PDBs of course), and these schemas are used by different applications. These applications have supposedly nothing do to with each other. Of course, those applications being different, they follow different roadmaps. Knowing this, it was quite impossible to migrate the whole database at once, and schema-by-schema migration was relevant and comfortable for everybody.
I realized that the main challenge to address was not technical complexity, it was more about understanding the muddle between schemas that should be distinct.
Everytime I had to migrate another schema, I wondered : Is this particular schema “self-contained” ? Can I move it with Datapump from one database to another “as is”, with minimum work and error handling ?
I knew DBMS_TTS.TRANSPORT_SET_CHECK procedure could be helpful at tablespace level, but I needed information at schema level.
I still have no magic wand to separate dependant schemas, but at least I have a checklist to identify wether a schema is self-contained or not.