Transport Database

Steps on Source Platform

Step-1: check compatibility

declare
b boolean;
begin
b:= dbms_tdb.check_db('Linux IA (32-bit)');
if b then dbms_output.put_line('YES your database can be transported on LINUX Platform');
else dbms_output.put_line('NO your database can not be transported on LINUX Platform');
end if;
end;

SQL> /

PL/SQL procedure successfully completed.


Step-2: shut immediate

Step-1: startup mount

Step-1: alter database open read only;

Step-1: create folder to hold converted data

Step-1: start RMAN

Step-1:

RMAN>

RMAN>

RMAN>

RMAN> CONVERT DATABASE transport script 'crdb.sql' new database 'newdb' TO PLATFORM 'Linux IA (32-bit)' FORMAT='C:\u01\app\oracle\product\10.2.0\db_transport/%U';


Starting convert at 23-JAN-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=153 devtype=DISK

External table SH.SALES_TRANSACTIONS_EXT found in the database
External table SYS.EXT_TABLE_CSV found in the database

Directory SYS.TRANS_RMAN_DIR found in the database
Directory SYS.TRANS_DIR found in the database
Directory SYS.DIR1 found in the database
Directory SYS.EXPDR found in the database
Directory SYS.SCOTT_DIR found in the database
Directory SYS.SYS_DIR found in the database
Directory SYS.DIR found in the database
Directory SYS.SUBDIR found in the database
Directory SYS.XMLDIR found in the database
Directory SYS.MEDIA_DIR found in the database
Directory SYS.LOG_FILE_DIR found in the database
Directory SYS.DATA_FILE_DIR found in the database
Directory SYS.WORK_DIR found in the database
Directory SYS.ADMIN_DIR found in the database
Directory SYS.DATA_PUMP_DIR found in the database

BFILE PM.PRINT_MEDIA found in the database

User SYS with SYSDBA and SYSOPER privilege found in password file
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00001 name=C:\U01\APP\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYST
EM01.DBF
converted datafile=C:\U01\APP\ORACLE\PRODUCT\10.2.0\DB_TRANSPORT\DATA_D-ORCL_I-1
268521292_TS-SYSTEM_FNO-1_0BM2RLF6
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00003 name=C:\U01\APP\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSA
UX01.DBF
converted datafile=C:\U01\APP\ORACLE\PRODUCT\10.2.0\DB_TRANSPORT\DATA_D-ORCL_I-1
268521292_TS-SYSAUX_FNO-3_0CM2RLG0
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00005 name=C:\U01\APP\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAM
PLE01.DBF
converted datafile=C:\U01\APP\ORACLE\PRODUCT\10.2.0\DB_TRANSPORT\DATA_D-ORCL_I-1
268521292_TS-EXAMPLE_FNO-5_0DM2RLGF
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00002 name=C:\U01\APP\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDO
TBS01.DBF
converted datafile=C:\U01\APP\ORACLE\PRODUCT\10.2.0\DB_TRANSPORT\DATA_D-ORCL_I-1
268521292_TS-UNDOTBS1_FNO-2_0EM2RLGN
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00004 name=C:\U01\APP\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USER
S01.DBF
converted datafile=C:\U01\APP\ORACLE\PRODUCT\10.2.0\DB_TRANSPORT\DATA_D-ORCL_I-1
268521292_TS-USERS_FNO-4_0FM2RLGU
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00006 name=C:\U01\APP\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TSP.
DBF
converted datafile=C:\U01\APP\ORACLE\PRODUCT\10.2.0\DB_TRANSPORT\DATA_D-ORCL_I-1
268521292_TS-TSP_FNO-6_0GM2RLH2
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00007 name=C:\U01\APP\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TRAN
S_TSP.DBF
converted datafile=C:\U01\APP\ORACLE\PRODUCT\10.2.0\DB_TRANSPORT\DATA_D-ORCL_I-1
268521292_TS-TRANS_TSP_FNO-7_0HM2RLH3
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Run SQL script C:\U01\APP\ORACLE\PRODUCT\10.2.0\DB_TRANSPORT\CRDB.SQL on the tar
get platform to create database
Edit init.ora file C:\U01\APP\ORACLE\PRODUCT\10.2.0\DB_TRANSPORT\INIT_00M2RLF6_1
_0.ORA. This PFILE will be used to create the database on the target platform
To recompile all PL/SQL modules, run utlirp.sql and utlrp.sql on the target plat
form
To change the internal database identifier, use DBNEWID Utility
Finished backup at 23-JAN-11

RMAN>





Steps on Target Platform


C:\>
C:\>
C:\>oradim -new -sid newdb
Instance created.

C:\>
C:\>
C:\>
C:\>
C:\>set oracle_sid=newdb
C:\>
C:\>
C:\>
C:\>sqlplus

SQL*Plus: Release 10.2.0.3.0 - Production on Sun Jan 23 19:39:38 2011

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Enter user-name: /as sysdba
Connected to an idle instance.

SQL>
SQL>
SQL>
SQL>
SQL> create spfile from pfile;

File created.

SQL>
SQL>
SQL>
SQL>
SQL>
SQL> startup nomount
ORACLE instance started.

Total System Global Area 734003200 bytes
Fixed Size 1292780 bytes
Variable Size 285214228 bytes
Database Buffers 440401920 bytes
Redo Buffers 7094272 bytes
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> CREATE CONTROLFILE REUSE SET DATABASE "NEWDB" RESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 'C:\u01\app\oracle\product\10.2.0\oradata\newdb\log01.log' SIZE
50M,
9 GROUP 2 'C:\u01\app\oracle\product\10.2.0\oradata\newdb\log02.log' SIZE
50M,
10 GROUP 3 'C:\u01\app\oracle\product\10.2.0\oradata\newdb\log03.log' SIZE
50M
11 DATAFILE
12 'C:\u01\app\oracle\product\10.2.0\oradata\newdb\SYSTEM.dbf',
13 'C:\u01\app\oracle\product\10.2.0\oradata\newdb\UNDOTBS1.dbf',
14 'C:\u01\app\oracle\product\10.2.0\oradata\newdb\SYSAUX.dbf',
15 'C:\u01\app\oracle\product\10.2.0\oradata\newdb\USERS.dbf',
16 'C:\u01\app\oracle\product\10.2.0\oradata\newdb\EXAMPLE.dbf',
17 'C:\u01\app\oracle\product\10.2.0\oradata\newdb\TSP.dbf',
18 'C:\u01\app\oracle\product\10.2.0\oradata\newdb\TRANS_TSP.dbf'
19 CHARACTER SET WE8MSWIN1252
20 ;

Control file created.

SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> STARTUP UPGRADE
ORACLE instance started.

Total System Global Area 734003200 bytes
Fixed Size 1292780 bytes
Variable Size 297797140 bytes
Database Buffers 427819008 bytes
Redo Buffers 7094272 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL>
SQL> @C:\u01\app\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlirp.sql
SQL>
SQL> WHENEVER SQLERROR EXIT;
SQL>
SQL> DOC
DOC>#######################################################################
DOC>#######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
2 package STANDARD AUTHID CURRENT_USER is -- careful on this line; SED edit occurs!
3
4 /********** Types and subtypes, do not reorder **********/
5 type BOOLEAN is (FALSE, TRUE);
6
7 type DATE is DATE_BASE;
8
9 type NUMBER is NUMBER_BASE;
SQL> DOC
DOC>#######################################################################
DOC>#######################################################################
DOC> utlirp.sql completed successfully. All PL/SQL objects in the
DOC> database have been invalidated.
DOC>
DOC> Shut down and restart the database in normal mode and run utlrp.sql to
DOC> recompile invalid objects.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area 734003200 bytes
Fixed Size 1292780 bytes
Variable Size 301991444 bytes
Database Buffers 423624704 bytes
Redo Buffers 7094272 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL>
SQL>
SQL> @C:\u01\app\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlrp.sql
SQL> Rem
SQL> Rem $Header: utlrp.sql 24-jul-2003.10:06:51 gviswana Exp $
SQL> Rem
SQL> Rem utlrp.sql
SQL>
SQL> Rem ===========================================================================
SQL> Rem END utlrp.sql
SQL> Rem ===========================================================================
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> select name from v$database;

NAME
---------
NEWDB

SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
C:\U01\APP\ORACLE\PRODUCT\10.2.0\ORADATA\NEWDB\CONTROL01.CTL
C:\U01\APP\ORACLE\PRODUCT\10.2.0\ORADATA\NEWDB\CONTROL02.CTL
C:\U01\APP\ORACLE\PRODUCT\10.2.0\ORADATA\NEWDB\CONTROL03.CTL

SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>