Checklist to determine if an Oracle schema is self-contained

2 Replies

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.

Continue reading →

Patch 22652097 in 12.1 makes optimizer_adaptive_features parameter obsolete

3 Replies

After having several issues with adaptive features on 12.1 databases, in some cases, the quickest way to solve problems was to simply set ‘optimizer_adaptive_features’ parameter to FALSE.
In 12.2, this parameter became obsolete and was replaced with two different parameters : ‘optimizer_adaptive_plans’ and ‘optimizer_adaptive_statistics’. Therefore, it is recommended to apply Patch 22652097 (PROVIDE SEPARATE CONTROLS FOR ADAPTIVE PLANS AND ADAPTIVE STATISTICS FEATURES) in order to have the same behaviour in 12.1.
I read thoroughly the README file of Patch 22652097 and applied it on my 12.1 homes. But when I tried to start the databases afterwards, some of them returned the following errors :

Continue reading →

Impdp tip : exclude content of a table and include metadata at once

Leave a reply

I am currently in the process of migrating a 150 Gb Oracle 10g database to 12c. Due to some limitations I won’t discuss here, I have to do it with Datapump.
As I encountered different problems during the import, I had to launch it several times. It took 2 hours to import a 120 Gb table. I knew I had to rerun the import anyway to fix other issues, so I decided to get rid of the data of this big table temporarily.
I scratched my head for a few minutes, because I can be lazy and I did not want to run impdp twice just to exclude the content of the big table and include its metadata.

Continue reading →

ODC Appreciation Day : EM CLI

3 Replies

When I first started as a DBA two years ago, the colleague in charge of my training thought that I would rather use several GUIs to complete most of the tasks. He said it was because of my age … But he quickly found out that I was more comfortable with CLIs.

For this very first blog post, I would like to participate in the ODC Appreciation Day and talk about a tool I really recommend : EM CLI.

Continue reading →