Monday, June 9, 2008

Transporting Database

1. Check if the database can be transported to destination platform. Put the DB in read only mode and use DBMS_TDB.CHECK_DB for this. It can be called without arguments to see if any condition at the source database prevents transport. It can also be called with one or both of the following arguments
target_platform_name : The name of the destination platform, as it appears in V$DB_TRANSPORTABLE_PLATFORM.

skip_option : Specifies which, if any, parts of the database to skip when checking whether the database can be transported.
* SKIP_NONE (or 0), which checks all tablespaces
* SKIP_OFFLINE (or 2), which skips checking datafiles in offline tablespaces
* SKIP_READONLY (or 3), which skips checking datafiles in read-only tablespaces

If SERVEROUTPUT is ON, and DBMS_TDB.CHECK_DB returns FALSE, then the output includes the reason why the database cannot be transported.
If you call DBMS_TDB.CHECK_DB and no messages are displayed indicating conditions preventing transport before the PL/SQL procedure successfully completed message, then your database is ready for transport.

set serveroutput on
db_ready boolean;
db_ready := dbms_tdb.check_db('Solaris Operating System (AMD64)',dbms_tdb.skip_readonly);

2. Use DBMS_TDB.CHECK_EXTERNAL to identify any external tables, directories or BFILEs. RMAN cannot automate the transport of such files. BMS_TDB.CHECK_EXTERNAL takes no parameters. With SERVEROUTPUT set to ON, the output of DBMS_TDB.CHECK_EXTERNAL lists the external tables, directories and BFILEs of your database.

set serveroutput on
external boolean;
external := dbms_tdb.check_external;

3. To convert the database at the source
CONVERT DATABASE NEW DATABASE 'newdb' transport script '/home/oracle/dump/tranport.sql' to platform 'Solaris Operating System (AMD64)' db_file_name_convert '/orace/oradata/test','/home/oracle/dump';

This will generate datafiles at /home/oracle/dump ready for transport, a PFile at $ORACLE_HOME/dbs and a transport.sql used to create the DB at the target.

4. Copy the datafiles, PFile and transport sql file to the target host.

5. At the target edit the pfile to reflect the new directory structure. control_files, *dump_dest, and recovery area are some of the things to consider. If the pfile is renamed from the original system generated name edit the transport sql to reflect the changes.

6. Once all the changes are done set the oracle sid to new DB and run the transport sql

7. At the end of the script DB is created and opened. Towards the end of the script utlirp.sql and utlrp.sql are run. IF utlrp.sql gives out errors then manually open the DB in upgrade mode and run utlirp and utlrp to compile the packages.

8. Create a password file for the database.

9. To generate a new SID for the database use the DBNEWID utility.
nid target=sys/newdb

10. After the new id is generated database is shutdown. open it with resetlogs.
alter database open resetlogs;

11. Create new directory objects in the target
select directory_path from dba_directories;
update dba_directories set directory_path='new path' where directory_path='old path';