Moving DB using expdp/impd

Need to move data from one db to another, but have to use export/import (expdp/impdp), and selected schemas too, question is that what is the best possible way to make sure that I don't skip or missed anything at the source db.

Source: 11203 - AIX 6.1
Target:  11204 - AIX 6.1

Please assist/guide ... thanks in advance.
Mushfique KhanDirector OperationsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

If you're worried about missing something

Start with a FULL=Y  and then remove what you don't want later in the target.
This will of course be slower and take up more space but is lowest risk.

If you're simply trying to move schemas, then just specify the schemas.  That's what those options are for and they'll grab everything in them.  If something goes wrong you should see an error in the logs.
Mushfique KhanDirector OperationsAuthor Commented:
Can not do FULL=Y due to some configuration issues, moving from HSM to Oracle Wallet and just can't export/import SYS schema, means have to do it on schema basis.

Do advise, what the best options to avoid any misses, we do have FGAC (fine grained access control) implemented too + TDE (encryption), also what should be best possible way to copy PUBLIC synonyms too?
HuaMin ChenProblem resolverCommented:
Other than import/export to move the DB schema, you also need to re-create DB user account/privileges.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Mushfique KhanDirector OperationsAuthor Commented:
Plan is to create a brand new db (11204) and then export/import into this new one, but not Oracle stuff, only application data/schemas.

Questions is how to make sure that ... we don't miss anything?
there is no automated method to make sure or really even a thorough manual method.
To use an analogy "you're packing to go on a vacation, how do you make sure you don't forget anything?"

There's no system to answer that 100%, you simply have to think it through, make your list carefully and then check off things from your list as you do them.

To get all of the db, either replicate it with RMAN, or use FULL.  You've said you want to use expdp so that rules out rman, and you said you don't want to use FULL, so that means you're going to have to manually go through your database picking out the things you want.

You can look through source code and dba_dependencies to try to help you not forget anything; but if you have an external script or program or macro that references some object there's no way to know that by looking inside the database.  You have to know what those external entities are and how they relate to the db.  Nobody here can answer that for you.

As for other oracle-related options, again, that's a list only you and your dba can come up with.
You mentioned oracle wallets, so obviously you'll need to grab the wallets.
As for other stuff - again, only you know.  Oracle has tons of products and add ons.
Are you using a oracle web server?  Do you need to move it or install a second copy somewhere?
Are you using gateways. If so, which ones? Do you have licenses to replicate them?
Do you use APEX? Do you have the workspaces and applications exported?
What about the oracle rest data services?

I can't list every possible app, add on, option, plugin,etc that oracle has.

Assuming you have proper legal licenses that are all up to date, contact your oracle representative to send you a list of all the products you have purchased and make sure you aren't forgetting any.  Also, make sure you have sufficient licenses to build a second copy on your new server.

When you think you have your list and your have migrated everything on your list, the way to make sure you have everything is to test extensively.  If something breaks, go back and pick up the piece you left out.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark GeerlingsDatabase AdministratorCommented:
I agree with the other recommendations: do schema level exports for each schema that you know you need.  You will have to use a different technique to get  the public synonyms since they are only included in a FULL export, but you said you can't use that option.

Here is a way to generate the commands you need for the public synonyms:

column command format a120;
select 'create public synonym '||synonym_name||' for '||table_owner||'.'||table_name||';' "Command"
from all_synonyms
where owner = 'PUBLIC';

Just run the resulting commands in the new database to create the public synonyms there.

Before you run import there, you will also need to create tablespaces and users there to match the tablespaces and user schemas that you export.  And, if you notice any other DB objects that are missing after the import, you will have to use queries something like the one I gave you here for public synonyms but do those selects from all_objects (or dba_objects) instead, and include the object_type column in those queries.
Mushfique KhanDirector OperationsAuthor Commented:
yes tablespaces will be identical as source, will import schemas one by one, complete, without stats, what I did on test db, while importing, monitoring the log file and if it raises any error, go and fix and and the import again, using TABLE_EXISTS_ACTION=REPLACE and this works.

Had encounter only issue and that was of PUBLIC synonym, at least 3/4 that was missed, rest was all good, but this was test, just would like to be more cautious for PROD, not to skip anything.

for the FGAC, will be using, while exporting:

this is the plan, do share, if you have any other/better idea.

And thanks a lot for all your assistance & guidance.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.