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

Register Database with RMAN catalog

rman target / catalog username/password@MYDB

RMAN> register database;

Might come in handy :P

via dbmotive.com

Incomplete Restore

First time I actually did this:

A dev DB was faulty and the Developers asked me to restore it to one week before (05.08.2009 - 20:00)

Action plan:

- startup force mount
- Delete datafiles via asmcmd
- rman target /
- run {
set until time "to_date('2009-08-05:20:00:00','YYYY-MM-DD:hh24:mi:ss')";
restore database;
recover database;
}

Easy =)

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

TNSping resolves but SQLplus gives ORA-12514

After creating a new DB on 11g, configuring it in listener.ora and entering it into LDAP, tnsping worked but I wasn't able to make a connection.

The problem was, that the DB didn't register with the listener automatically. Setting the local_listener parameter solved the problem

alter system set local_listener='host_listenername';

ORA-01578 ORACLE data block corrupted

In case this happens, Metalink provides a super-duper statement, that will show you the name and type of the corrupt segment:

SELECT segment_name , segment_type , owner , tablespace_name
FROM sys.dba_extents
WHERE file_id = &bad_file_id
AND &bad_block_id BETWEEN block_id and block_id + blocks -1

To fix it, I used toad to recreate the tables/indexes that were corrupt.

Some import/export notes

• Always use direct=y when using exp/imp (much faster since it basically copies blocks from the db to the dumpfile)
• Disable archive log mode before an import of a large database and do a backup afterwards. It's not really state of the art to delete archive logs when you get an ORA-19809: limit exceeded for recovery files (altough you could just back them up <.<)
  
• When moving data from a <=9i to 10g+ database, only do a schema import. Those old standard users could cause the import to just abort.

Just a few things I remember from some frustrating few hours of exp/importing a 40 gig database -.-

Btw: Just in case you don't know how to disable archive log mode:

shutdown immediate;
startup mount;
alter database noarchivelog;
alter database open;
archive log list;