How to migrate a small oracle database from one host to another

Note: This procedure works with 10g+ using Datapump. I am not sure, if the export/import utility supports Network Links. Also, this procedure can be used for a migration to a newer/older version (since we're importing the data and don't use a copy/duplicate)

Preparation:
Create Empty Database on new Host:
- dbca
- Custom
- Select components according to select comp_name from dba_registry;
- Select charset according to select * from nls_database_parameters where parameter='NLS_CHARACTERSET';
- Choose create script and deselect Create DB now
- Create oratab entry
- Goto admin/scritpts
- Modify init.ora according to 10g Template or 11g Template
- run ./SID.sh
- Do a quick check if all objects are valid: select count(*) from dba_objects where status='INVALID';
- After DB creation, create the same tablespaces as in the source DB using:

begin
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',true);
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
end;
/
set long 100000 longchunksize 10000 pages 0
set lines 200


select dbms_metadata.get_ddl('TABLESPACE',name) from v$tablespace where name not in ('SYSTEM','SYSAUX');

(Remove all existing tablespaces from the command list. Remove the datafile name (we're using OMF hell yeah!) put size to about 100M and turn autoextend on (that way, we will shrink unused space during the migration as well, how cool is that huh?))

- Create DB Link to source database: create database link Migration connect to system identified by "password" using 'SID';
- Test db link: select host_name from v$instance@MIGRATION; (result clearly should be the host of the source system)
- Create directory for datapump logfile. create directory migration_log as '/path/to/log';
- Put Database in Archivelog mode and activate Flashback. (startup force mount; alter database archivelog; alter database flashback on; alter database open;)
- Create Guaranteed Flashback Point (in case something goes wrong, we won't have to re-install the db) create restore point before_mig guarantee flashback database;
- Start the import: impdp full=y network_link=MIGRATION directory=migration_log logfile=migration.log (use / as sysdba)
- Check the logfile, for any errors other than ORA-31684 (Object already exists)
- drop restore point before_mig;
- Remove Source DB from Grid Control (or any other monitoring)
- Add new DB
- Configure Backup
- Inform everyone how hard you just worked!
- Get a Beer

OERR: ORA-31696 unable to export/import %s using client specified %s method

During a full import of a DB with Datapump, I got an ORA-31696 for a table.

Woraround for this error is simply to import it afterwards with tables=owner.table_name and table_exists_action=replace (since the table is created but no data is imported)

via Lazydba

Duplicate/Move/Import/Rename Schema

I was asked to copy two schemas from two databases into another.

Problem: The copies would have different names and use other Tablespaces

Solution: Export via Datapump (expdp schema=schema_name [...]) and import with remap_schema and remap_tablespace parameter:

impdp schemas=schema_name remap_schema=schema_name:new_schema_name remap_tablespace=tablespace_name:new_tablespace_name [...]