/*
|| Oracle 10gR2 Transportable Tablespace Enhancements Listing 1
||
|| Demonstrates new Oracle 10gR2 transportable tablespace features, including:
|| - How to transport tablespaces between different platforms
|| - How to transport an entire database between different platforms
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| This script is provided to demonstrate various features of Oracle 10gR2
|| Transportable Tablespaces and should be carefully proofread before it is
|| executed against any existing Oracle database to insure that no potential
|| damage can occur.
||
*/

/* 
|| Listing 1.1: Determining a platform's ENDIAN-ness and its potential impact
||              on the database's tablespace "transportability"
*/

-----
-- What are the available Transportable Tablespace Platform possibilities?
-----

TTITLE 'Current Transportable Tablespace Platform Attributes'
COL platform_name   FORMAT A40      HEADING 'Platform Name'
COL endian_format   FORMAT A12      HEADING 'ENDIAN|Format'
SELECT 
    platform_name
   ,endian_format
  FROM v$transportable_platform
 ORDER BY platform_name
;
TTITLE OFF

>>> Results:

Thu Apr 13                                  page    1

 Current Transportable Tablespace Platform Attributes

                                         ENDIAN
Platform Name                            Format
---------------------------------------- ------------
AIX-Based Systems (64-bit)               Big
Apple Mac OS                             Big
HP Open VMS                              Little
HP Tru64 UNIX                            Little
HP-UX (64-bit)                           Big
HP-UX IA (64-bit)                        Big
IBM Power Based Linux                    Big
IBM zSeries Based Linux                  Big
Linux 64-bit for AMD                     Little
Linux IA (32-bit)                        Little
Linux IA (64-bit)                        Little
Microsoft Windows 64-bit for AMD         Little
Microsoft Windows IA (32-bit)            Little
Microsoft Windows IA (64-bit)            Little
Solaris Operating System (x86)           Little
Solaris[tm] OE (32-bit)                  Big
Solaris[tm] OE (64-bit)                  Big

17 rows selected.

-----
-- What's the current ENDIAN-ness of my database and platform?
-----

TTITLE 'Current Database Platform ENDIAN-Ness'
COL name            FORMAT A16      HEADING 'Database Name'
COL endian_format   FORMAT A12      HEADING 'ENDIAN|Format'
SELECT 
     D.name
    ,TP.endian_format
  FROM
      v$transportable_platform TP
     ,v$database D
 WHERE TP.platform_name = D.platform_name
;
TTITLE OFF

>>> Results:

Thu Apr 13                     page 1
Current Database Platform ENDIAN-Ness

                 ENDIAN
Database Name    Format
---------------- ------------
ORCL102          Little

/* 
|| Listing 1.2: Preparations for Transportable Tablespace demonstrations
*/

-----
-- Create new tablespaces LMT_XACT (for transactional tables) and 
-- LMT_REF (for reference tables)
-----

DROP TABLESPACE lmt_xact INCLUDING CONTENTS AND DATAFILES;
CREATE TABLESPACE lmt_xact
    DATAFILE 'H:\oradata\orcl102\lmt_xact01.dbf'
    SIZE 10M REUSE
    AUTOEXTEND ON
    MAXSIZE 50M
    SEGMENT SPACE MANAGEMENT AUTO;
    
DROP TABLESPACE lmt_ref INCLUDING CONTENTS AND DATAFILES;
CREATE TABLESPACE lmt_ref
    DATAFILE 'H:\oradata\orcl102\lmt_ref01.dbf'
    SIZE 2M REUSE
    AUTOEXTEND ON
    MAXSIZE 5M
    SEGMENT SPACE MANAGEMENT AUTO;

-----
-- Create reference tables
-----
DROP TABLE sh.sales_aggr_types PURGE;
CREATE TABLE sh.sales_aggr_types (
     item_type      VARCHAR2(3)     NOT NULL    PRIMARY KEY
    ,description    VARCHAR2(40)    NOT NULL
)
    TABLESPACE lmt_ref
    PCTFREE 10
    STORAGE (INITIAL 128K);

DROP TABLE sh.customer_types PURGE;
CREATE TABLE sh.customer_types (
     cust_type      VARCHAR2(3)     NOT NULL    PRIMARY KEY
    ,description    VARCHAR2(40)    NOT NULL
)
    TABLESPACE lmt_ref
    PCTFREE 10
    STORAGE (INITIAL 128K);

-----
-- Load reference tables
-----
INSERT INTO sh.sales_aggr_types VALUES('CPY', 'Company Rollup');
INSERT INTO sh.sales_aggr_types VALUES('RGN', 'Regional Rollup');
INSERT INTO sh.sales_aggr_types VALUES('DST', 'District Rollup');
INSERT INTO sh.sales_aggr_types VALUES('TER', 'Territory Rollup');
INSERT INTO sh.customer_types VALUES('RTL', 'Retail Account');
INSERT INTO sh.customer_types VALUES('WHL', 'Wholesale Account');
INSERT INTO sh.customer_types VALUES('USG', 'U.S. Government Account');
INSERT INTO sh.customer_types VALUES('SLG', 'State/Local Government Account');
INSERT INTO sh.customer_types VALUES('ITL', 'International Account');
COMMIT;

-----
-- Create transaction table
-----
DROP TABLE sh.aggr_sales PURGE;
CREATE TABLE sh.aggr_sales (
     sales_aggr_type    VARCHAR2(3) NOT NULL
    ,geo_area           VARCHAR2(4) NOT NULL
    ,cust_type          VARCHAR2(3) NOT NULL
    ,units              NUMBER(15)
    ,revenue            NUMBER(15,2)
)
    TABLESPACE lmt_xact
    PCTFREE 5
    STORAGE (INITIAL 1M);

-----
-- Load transactions table
-----
INSERT INTO SH.AGGR_SALES VALUES('CPY','CMPY','USG',148514,1608050.19);
INSERT INTO SH.AGGR_SALES VALUES('RGN','EAST','USG',58830,636987.71);
INSERT INTO SH.AGGR_SALES VALUES('RGN','WEST','USG',89684,971062.48);
INSERT INTO SH.AGGR_SALES VALUES('DST','EA00','USG',15270,165337.45);
INSERT INTO SH.AGGR_SALES VALUES('DST','SE00','USG',43560,471650.26);
INSERT INTO SH.AGGR_SALES VALUES('DST','MW00','USG',40876,442588.98);
INSERT INTO SH.AGGR_SALES VALUES('DST','WE00','USG',48808,528473.50);
INSERT INTO SH.AGGR_SALES VALUES('TER','EA01','USG',12380,134045.69);
INSERT INTO SH.AGGR_SALES VALUES('TER','EA02','USG',2890,31291.76);
INSERT INTO SH.AGGR_SALES VALUES('TER','SE01','USG',10576,114512.70);
INSERT INTO SH.AGGR_SALES VALUES('TER','SE02','USG',32984,357137.56);
INSERT INTO SH.AGGR_SALES VALUES('TER','MW01','USG',31003,335688.08);
INSERT INTO SH.AGGR_SALES VALUES('TER','MW02','USG',9873,106900.89);
INSERT INTO SH.AGGR_SALES VALUES('TER','WE01','USG',27891,301992.59);
INSERT INTO SH.AGGR_SALES VALUES('TER','WE02','USG',20917,226480.90);
INSERT INTO SH.AGGR_SALES VALUES('CPY','CMPY','RTL',335399,3631566.21);
INSERT INTO SH.AGGR_SALES VALUES('RGN','EAST','RTL',174783,1892480.41);
INSERT INTO SH.AGGR_SALES VALUES('RGN','WEST','RTL',160616,1739085.80);
INSERT INTO SH.AGGR_SALES VALUES('DST','EA00','RTL',80878,875714.63);
INSERT INTO SH.AGGR_SALES VALUES('DST','SE00','RTL',93905,1016765.78);
INSERT INTO SH.AGGR_SALES VALUES('DST','MW00','RTL',69652,754164.00);
INSERT INTO SH.AGGR_SALES VALUES('DST','WE00','RTL',90964,984921.81);
INSERT INTO SH.AGGR_SALES VALUES('TER','EA01','RTL',29875,323474.55);
INSERT INTO SH.AGGR_SALES VALUES('TER','EA02','RTL',51003,552240.08);
INSERT INTO SH.AGGR_SALES VALUES('TER','SE01','RTL',51875,561681.75);
INSERT INTO SH.AGGR_SALES VALUES('TER','SE02','RTL',42030,455084.03);
INSERT INTO SH.AGGR_SALES VALUES('TER','MW01','RTL',30651,331876.77);
INSERT INTO SH.AGGR_SALES VALUES('TER','MW02','RTL',39001,422287.23);
INSERT INTO SH.AGGR_SALES VALUES('TER','WE01','RTL',49871,539983.24);
INSERT INTO SH.AGGR_SALES VALUES('TER','WE02','RTL',41093,444938.57);
INSERT INTO SH.AGGR_SALES VALUES('CPY','CMPY','WHL',1657787,17949854.52);
INSERT INTO SH.AGGR_SALES VALUES('RGN','EAST','WHL',601285,6510473.47);
INSERT INTO SH.AGGR_SALES VALUES('RGN','WEST','WHL',1056502,11439381.06);
INSERT INTO SH.AGGR_SALES VALUES('DST','EA00','WHL',364557,3947277.37);
INSERT INTO SH.AGGR_SALES VALUES('DST','SE00','WHL',236728,2563196.09);
INSERT INTO SH.AGGR_SALES VALUES('DST','MW00','WHL',414780,4491071.93);
INSERT INTO SH.AGGR_SALES VALUES('DST','WE00','WHL',641722,6948309.13);
INSERT INTO SH.AGGR_SALES VALUES('TER','EA01','WHL',175094,1895847.79);
INSERT INTO SH.AGGR_SALES VALUES('TER','EA02','WHL',189463,2051429.58);
INSERT INTO SH.AGGR_SALES VALUES('TER','SE01','WHL',109127,1181583.51);
INSERT INTO SH.AGGR_SALES VALUES('TER','SE02','WHL',127601,1381612.59);
INSERT INTO SH.AGGR_SALES VALUES('TER','MW01','WHL',201981,2186969.48);
INSERT INTO SH.AGGR_SALES VALUES('TER','MW02','WHL',212799,2304102.45);
INSERT INTO SH.AGGR_SALES VALUES('TER','WE01','WHL',305791,3310982.63);
INSERT INTO SH.AGGR_SALES VALUES('TER','WE02','WHL',335931,3637326.50);
INSERT INTO SH.AGGR_SALES VALUES('CPY','CMPY','SLG',98728,1068987.29);
INSERT INTO SH.AGGR_SALES VALUES('RGN','EAST','SLG',40139,434609.04);
INSERT INTO SH.AGGR_SALES VALUES('RGN','WEST','SLG',58589,634378.26);
INSERT INTO SH.AGGR_SALES VALUES('DST','EA00','SLG',19963,216151.38);
INSERT INTO SH.AGGR_SALES VALUES('DST','SE00','SLG',20176,218457.66);
INSERT INTO SH.AGGR_SALES VALUES('DST','MW00','SLG',26920,291478.99);
INSERT INTO SH.AGGR_SALES VALUES('DST','WE00','SLG',31669,342899.26);
INSERT INTO SH.AGGR_SALES VALUES('TER','EA01','SLG',9872,106890.07);
INSERT INTO SH.AGGR_SALES VALUES('TER','EA02','SLG',10091,109261.31);
INSERT INTO SH.AGGR_SALES VALUES('TER','SE01','SLG',11091,120088.91);
INSERT INTO SH.AGGR_SALES VALUES('TER','SE02','SLG',9085,98368.75);
INSERT INTO SH.AGGR_SALES VALUES('TER','MW01','SLG',12000,129931.2);
INSERT INTO SH.AGGR_SALES VALUES('TER','MW02','SLG',14920,161547.79);
INSERT INTO SH.AGGR_SALES VALUES('TER','WE01','SLG',2908,31486.66);
INSERT INTO SH.AGGR_SALES VALUES('TER','WE02','SLG',28761,311412.60);
INSERT INTO SH.AGGR_SALES VALUES('CPY','CMPY','ITL',5694,61652.35);
INSERT INTO SH.AGGR_SALES VALUES('RGN','EAST','ITL',3299,35720.25);
INSERT INTO SH.AGGR_SALES VALUES('RGN','WEST','ITL',2395,25932.10);
INSERT INTO SH.AGGR_SALES VALUES('DST','EA00','ITL',2037,22055.82);
INSERT INTO SH.AGGR_SALES VALUES('DST','SE00','ITL',1262,13664.43);
INSERT INTO SH.AGGR_SALES VALUES('DST','MW00','ITL',1992,21568.58);
INSERT INTO SH.AGGR_SALES VALUES('DST','WE00','ITL',403,4363.52);
INSERT INTO SH.AGGR_SALES VALUES('TER','EA01','ITL',1400,15158.64);
INSERT INTO SH.AGGR_SALES VALUES('TER','EA02','ITL',637,6897.18);
INSERT INTO SH.AGGR_SALES VALUES('TER','SE01','ITL',291,3150.83);
INSERT INTO SH.AGGR_SALES VALUES('TER','SE02','ITL',971,10513.60);
INSERT INTO SH.AGGR_SALES VALUES('TER','MW01','ITL',1005,10881.74);
INSERT INTO SH.AGGR_SALES VALUES('TER','MW02','ITL',987,10686.84);
INSERT INTO SH.AGGR_SALES VALUES('TER','WE01','ITL',129,1396.76);
INSERT INTO SH.AGGR_SALES VALUES('TER','WE02','ITL',274,2966.76);

COMMIT;

-----
-- Create indexes and constraints
-----
-- Create indexes and constraints
CREATE UNIQUE INDEX sh.aggr_sales_pk_idx
    ON sh.aggr_sales (sales_aggr_type, geo_area, cust_type)
    TABLESPACE lmt_ref
    PCTFREE 5
    STORAGE (INITIAL 128K);

ALTER TABLE sh.aggr_sales
    ADD CONSTRAINT aggr_sales_pk 
    PRIMARY KEY (sales_aggr_type, geo_area, cust_type);

ALTER TABLE sh.aggr_sales
    ADD CONSTRAINT aggr_sales_fk_aggr_type
    FOREIGN KEY (sales_aggr_type)
    REFERENCES sh.sales_aggr_types (item_type);

ALTER TABLE sh.aggr_sales
    ADD CONSTRAINT aggr_sales_fk_cust_type
    FOREIGN KEY (cust_type)
    REFERENCES sh.customer_types (cust_type);

/* 
|| Listing 1.3: Transporting a tablespace:
|| 1.) Create DIRECTORY object for storage of source transportable
||     tablespaces and metadata
|| 2.) Make the source tablespace read-only to permit the transport
|| 3.) Export the tablespace's metadata via DataPump Export
|| 4.) Copy the tablespace's datafile and metadata to target server
|| 5.) Import tablespace into target database
|| 6.) Enable tablespace for READ WRITE mode on target database
|| 7.) Re-enable tablespace for READ WRITE mode on source database
*/

/*
|| Transporting Tablespaces: Source Server Processing
*/

-- Create the DIRECTORY object on the source database server
C:> mkdir c:\oracle\ttxports
SQL> DROP DIRECTORY ttxports;
SQL> CREATE DIRECTORY ttxports AS 'c:\oracle\ttxports';
SQL> GRANT READ, WRITE ON DIRECTORY ttxports TO PUBLIC;

-- Make the source tablespace read-only
SQL> ALTER TABLESPACE lmt_xact READ ONLY;

-- Contents of DataPump Export parameter file (tts_export_1.dpectl):
JOB_NAME = TTS_EXPORT_1
DIRECTORY = TTXPORTS
DUMPFILE = tts_export_1.dmp 
LOGFILE = tts_export_1.log
TRANSPORT_TABLESPACES = lmt_xact
TRANSPORT_FULL_CHECK = TRUE

-- Start a DataPump Export operation for the tablespace transport
EXPDP system/oracle PARFILE=c:\oracle\ttxports\tts_export_1.dpectl

-----
-- Results of failed transportable tablespace metadata export. Note that
-- Oracle will not let a tablespace's metadata be created if tables within 
-- the tablespace reference objects in other tablespaces not included in
-- the list of tablespaces to be transported; also, all tablespaces in the
-- transportable tablespace set must be in READ ONLY mode
-----

Export: Release 10.2.0.1.0 - Production on Sunday, 16 April, 2006 12:38:36
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."TTS_EXPORT_1":  system/******** PARFILE=c:\oracle\ttxports\tts_export_1.dpectl 
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29341: The transportable set is not self-contained
Job "SYSTEM"."TTS_EXPORT_1" stopped due to fatal error at 12:39:19

-----
-- Results of successful transportable tablespace metadata export (i.e. 
-- after changing list of transportable tablespaces to include both LMT_XACT
-- and LMT_REF)
-----

Export: Release 10.2.0.1.0 - Production on Sunday, 16 April, 2006 12:45:51
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."TTS_EXPORT_1":  system/******** PARFILE=c:\oracle\ttxports\tts_export_1.dpectl 
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."TTS_EXPORT_1" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.TTS_EXPORT_1 is:
  C:\ORACLE\TTXPORTS\TTS_EXPORT_1.DMP
Job "SYSTEM"."TTS_EXPORT_1" successfully completed at 12:47:43

/*
|| Transporting Tablespaces: Target Server Processing
*/

-----
-- Create a DIRECTORY object for storage of target transportable
-- tablespaces and metadata
-----

$> mkdir ttxports
SQL> DROP DIRECTORY ttxports;
SQL> CREATE DIRECTORY ttxports AS '/u02/ttxports';
SQL> GRANT READ, WRITE ON DIRECTORY ttxports TO PUBLIC;

-----
-- Contents of DataPump Import parameter file (tts_import_1.dpictl):
-----
JOB_NAME = TTS_IMPORT_1
DIRECTORY = TTXPORTS
DUMPFILE = TTS_EXPORT_1.DMP 
LOGFILE = tts_import_1.log
TRANSPORT_DATAFILES = /u02/ttxports/LMT_XACT01.DBF, /u02/ttxports/LMT_REF.DBF

-----
-- DataPump Import command:
-----
IMPDP system/oracle PARFILE=tts_import_1.dpictl

>>> Results of successful transportable tablespace import on target

Import: Release 10.2.0.1.0 - Production on Sunday, 16 April, 2006 12:52:38
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Master table "SYSTEM"."TTS_IMPORT_1" successfully loaded/unloaded
Starting "SYSTEM"."TTS_IMPORT_1":  system/******** parfile = /u02/ttxports/tts_import_1.dpictl 
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."TTS_IMPORT_1" successfully completed at 12:53:07

-- After copying the tablespaces' datafiles to the destination server's 
-- directory, bring the tablespaces back into read-write mode

SQL> ALTER TABLESPACE lmt_xact READ WRITE;
SQL> ALTER TABLESPACE lmt_ref READ WRITE;

/* 
|| Listing 1.4: Transporting a tablespace between platforms of different
||              "endian-ness"
*/

-----
-- Recovery Manager Script to convert database on the target
-- server from Windows 32-bit format to Linux 64-bit format
-----

#####
# RMAN command to convert a tablespace's datafiles from
# the current endian-ness to a different endian-ness
#####
CONVERT TABLESPACE lmt_xact
  TO PLATFORM='Solaris[tm] OE (32-bit)'
  FORMAT='/u03/oracle/oradata/rptrepos';

/* 
|| Listing 1.5: Preparing to transport an entire database between platforms
*/

-----
-- Using DBMS_TDB to validate if a database is ready for transport
-----
SET SERVEROUTPUT ON
DECLARE
    db_check BOOLEAN;
BEGIN

    -----
    -- Can this database be transported to the specified target platform?
    -----
    db_check := 
        DBMS_TDB.CHECK_DB(
             target_platform_name => 'Linux IA (32-bit)'
            ,skip_option => DBMS_TDB.SKIP_OFFLINE
        );
    IF db_check
        THEN DBMS_OUTPUT.PUT_LINE('Database can be transferred to target platform.');
        ELSE DBMS_OUTPUT.PUT_LINE('Warning!!! Database CANNOT be transported to target platform.');
    END IF;

    -----
    -- Are there any directories or external objects that need to be 
    -- transferred separately after these tablespace(s) have been 
    -- transported to the target platform? 
    -----
    db_check := DBMS_TDB.CHECK_EXTERNAL;
    IF db_check
        THEN DBMS_OUTPUT.PUT_LINE('Database can be transferred to target platform.');
        ELSE DBMS_OUTPUT.PUT_LINE('Warning!!! Database CANNOT be transported to target platform.');
    END IF;

END;
/

/* 
|| Listing 1.6: Preparing a database for transport when conversion will
||              occur on the target platform
*/

-----
-- RMAN session that prepares database for transport and conversion
-----

C:\WINDOWS\system32>rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Apr 19 18:39:28 2006
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
connected to target database: ORCL102 (DBID=3040314982)

RMAN> RUN {
2>     #####
3>     # Converting a database on target platform
4>     #####
5>     CONVERT DATABASE
6>     ON TARGET PLATFORM
7>     CONVERT SCRIPT 'c:\oracle\rptrepos\rptrepos.cnv'
8>     TRANSPORT SCRIPT 'c:\oracle\rptrepos\rptrepos.txp'
9>     NEW DATABASE 'rptrepos'
11>    DB_FILE_NAME_CONVERT =
            'c:\oracle\oradata\orcl102' 
            'c:\oracle\rptrepos';
12> }

Starting convert at 19-APR-06
using channel ORA_DISK_1

External table HR.XT_EMPLOYEE_PAYCHECKS found in the database
External table HR.XT_EMPLOYEE_SECURED found in the database
External table SH.XT_MIDWEST_SALES found in the database
External table SH.SALES_TRANSACTIONS_EXT found in the database

Directory SYS.TTXPORTS found in the database
Directory SYS.FTP_SECURED found in the database
Directory SYS.EXTERNAL_DIR found in the database
Directory SYS.DATA_PUMP_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

BFILE PM.PRINT_MEDIA found in the database

User SYS with SYSDBA and SYSOPER privilege found in password file
channel ORA_DISK_1: starting to check datafiles
input datafile fno=00001 name=C:\ORACLE\ORADATA\ORCL102\SYSTEM01.DBF
channel ORA_DISK_1: datafile checking complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting to check datafiles
input datafile fno=00003 name=C:\ORACLE\ORADATA\ORCL102\SYSAUX01.DBF
channel ORA_DISK_1: datafile checking complete, elapsed time: 00:00:00
channel ORA_DISK_1: starting to check datafiles
input datafile fno=00002 name=C:\ORACLE\ORADATA\ORCL102\UNDOTBS01.DBF
channel ORA_DISK_1: datafile checking complete, elapsed time: 00:00:00
channel ORA_DISK_1: starting to check datafiles
input datafile fno=00005 name=C:\ORACLE\ORADATA\ORCL102\EXAMPLE01.DBF
channel ORA_DISK_1: datafile checking complete, elapsed time: 00:00:00
channel ORA_DISK_1: starting to check datafiles
input datafile fno=00015 name=C:\ORACLE\ORADATA\ORCL102\LMT_XACT01.DBF
channel ORA_DISK_1: datafile checking complete, elapsed time: 00:00:00
channel ORA_DISK_1: starting to check datafiles
input datafile fno=00004 name=C:\ORACLE\ORADATA\ORCL102\USERS01.DBF
channel ORA_DISK_1: datafile checking complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting to check datafiles
input datafile fno=00016 name=C:\ORACLE\ORADATA\ORCL102\LMT_REF01.DBF
channel ORA_DISK_1: datafile checking complete, elapsed time: 00:00:00
Run SQL script C:\ORACLE\RPTREPOS\RPTREPOS.TXP on the target platform to create database
Edit init.ora file C:\ORACLE\INIT_RPTREPOS.ORA. This PFILE will be used to create the database on the target p
latform
Run RMAN script C:\ORACLE\RPTREPOS\RPTREPOS.CNV on target platform to convert datafiles
To recompile all PL/SQL modules, run utlirp.sql and utlrp.sql on the target platform
To change the internal database identifier, use DBNEWID Utility
Finished backup at 19-APR-06
RMAN>

-----
-- Oracle-generated script (RPTREPOS.TXP) containing commands needed
-- to recreate database on target platform before datafile conversion. 
-- This file can then be edited on the target to place datafiles in 
-- appropriate folders
-----

-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.

-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT PFILE='C:\ORACLE\INIT_RPTREPOS.ORA'
CREATE CONTROLFILE REUSE SET DATABASE "RPTREPOS" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 'C:\ORACLE\RPTREPOS'  SIZE 50M,
  GROUP 2 'C:\ORACLE\RPTREPOS'  SIZE 50M,
  GROUP 3 'C:\ORACLE\RPTREPOS'  SIZE 50M
DATAFILE
  'C:\ORACLE\RPTREPOS\SYSTEM01.DBF',
  'C:\ORACLE\RPTREPOS\UNDOTBS01.DBF',
  'C:\ORACLE\RPTREPOS\SYSAUX01.DBF',
  'C:\ORACLE\RPTREPOS\USERS01.DBF',
  'C:\ORACLE\RPTREPOS\EXAMPLE01.DBF',
  'C:\ORACLE\RPTREPOS\LMT_XACT01.DBF',
  'C:\ORACLE\RPTREPOS\LMT_REF01.DBF'
CHARACTER SET AL32UTF8
;

-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;

-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\ORACLE\RPTREPOS'
     SIZE 202375168  AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
-- End of tempfile additions.
--

set echo off
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt * Your database has been created successfully!
prompt * There are many things to think about for the new database. Here
prompt * is a checklist to help you stay on track:
prompt * 1. You may want to redefine the location of the directory objects.
prompt * 2. You may want to change the internal database identifier (DBID) 
prompt *    or the global database name for this database. Use the 
prompt *    NEWDBID Utility (nid).
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SHUTDOWN IMMEDIATE 
STARTUP UPGRADE PFILE='C:\ORACLE\INIT_RPTREPOS.ORA'
@@ ?/rdbms/admin/utlirp.sql 
SHUTDOWN IMMEDIATE 
STARTUP PFILE='C:\ORACLE\INIT_RPTREPOS.ORA'
-- The following step will recompile all PL/SQL modules.
-- It may take serveral hours to complete.
@@ ?/rdbms/admin/utlrp.sql 
set feedback 6;

-----
-- Oracle-generated script (RPTREPOS.CNV) containing commands needed to
-- convert all datafiles on the target platform. This file can be edited
-- to place datafiles in appropriate folders on the target
-----

RUN {
  CONVERT DATAFILE 'C:\ORACLE\ORADATA\ORCL102\SYSTEM01.DBF' 
    FROM PLATFORM 'Microsoft Windows IA (32-bit)' 
    FORMAT 'C:\ORACLE\RPTREPOS\SYSTEM01.DBF';
  CONVERT DATAFILE 'C:\ORACLE\ORADATA\ORCL102\SYSAUX01.DBF' 
    FROM PLATFORM 'Microsoft Windows IA (32-bit)' 
    FORMAT 'C:\ORACLE\RPTREPOS\SYSAUX01.DBF';
  CONVERT DATAFILE 'C:\ORACLE\ORADATA\ORCL102\UNDOTBS01.DBF' 
    FROM PLATFORM 'Microsoft Windows IA (32-bit)' 
    FORMAT 'C:\ORACLE\RPTREPOS\UNDOTBS01.DBF';
  CONVERT DATAFILE 'C:\ORACLE\ORADATA\ORCL102\EXAMPLE01.DBF' 
    FROM PLATFORM 'Microsoft Windows IA (32-bit)' 
    FORMAT 'C:\ORACLE\RPTREPOS\EXAMPLE01.DBF';
  CONVERT DATAFILE 'C:\ORACLE\ORADATA\ORCL102\LMT_XACT01.DBF' 
    FROM PLATFORM 'Microsoft Windows IA (32-bit)' 
    FORMAT 'C:\ORACLE\RPTREPOS\LMT_XACT01.DBF';
  CONVERT DATAFILE 'C:\ORACLE\ORADATA\ORCL102\USERS01.DBF' 
    FROM PLATFORM 'Microsoft Windows IA (32-bit)' 
    FORMAT 'C:\ORACLE\RPTREPOS\USERS01.DBF';
  CONVERT DATAFILE 'C:\ORACLE\ORADATA\ORCL102\LMT_REF01.DBF' 
    FROM PLATFORM 'Microsoft Windows IA (32-bit)' 
    FORMAT 'C:\ORACLE\RPTREPOS\LMT_REF01.DBF';
}

-----
-- Oracle-generated initialization parameter file (INIT_RPTREPOS.ORA) 
-- for use during database creation and conversion on target platform
-----
# Please change the values of the following parameters:
  control_files            = "C:\ORACLE\RPTREPOS"
  db_recovery_file_dest    = "C:\ORACLE\flash_recovery_area"
  db_recovery_file_dest_size= 2147483648
  audit_file_dest          = "C:\ORACLE\ADUMP"
  background_dump_dest     = "C:\ORACLE\BDUMP"
  user_dump_dest           = "C:\ORACLE\UDUMP"
  core_dump_dest           = "C:\ORACLE\CDUMP"
  db_name                  = "RPTREPOS"
# Please review the values of the following parameters:
  __shared_pool_size       = 54525952
  __large_pool_size        = 4194304
  __java_pool_size         = 4194304
  __streams_pool_size      = 4194304
  __db_cache_size          = 46137344
  remote_login_passwordfile= "EXCLUSIVE"
  db_domain                = ""
  dispatchers              = "(PROTOCOL=TCP) (SERVICE=orcl102XDB)"
# The values of the following parameters are from source database:
  processes                = 150
  sga_max_size             = 134217728
  sga_target               = 117440512
  db_block_size            = 8192
  compatible               = "10.2.0.1.0"
  db_file_multiblock_read_count= 16
  undo_management          = "AUTO"
  undo_tablespace          = "UNDOTBS1"
  shared_servers           = 2
  max_shared_servers       = 5
  job_queue_processes      = 10
  open_cursors             = 300
  pga_aggregate_target     = 33554432

/* 
|| Listing 1.7: Preparing a database for transport when conversion will
||              occur on the source platform
*/

-----
-- RMAN session that converts database at source and prepares for transport
-- to target platform
-----

C:\WINDOWS\system32>rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Sun Apr 16 14:16:38 2006
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
connected to target database: ORCL102 (DBID=3040314982)

RMAN> RUN {
2>     CONVERT DATABASE
3>     NEW DATABASE 'rptrepos'
4>     TRANSPORT SCRIPT 'c:\oracle\rptrepos\rptrepos.sql'
5>     TO PLATFORM 'Linux IA (32-bit)'
6>     db_file_name_convert 'c:\oracle\oradata\orcl102' 'c:\oracle\rptrepos';
7> }

Starting convert at 16-APR-06
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=153 devtype=DISK

External table HR.XT_EMPLOYEE_PAYCHECKS found in the database
External table HR.XT_EMPLOYEE_SECURED found in the database
External table SH.XT_MIDWEST_SALES found in the database
External table SH.SALES_TRANSACTIONS_EXT found in the database

Directory SYS.TTXPORTS found in the database
Directory SYS.FTP_SECURED found in the database
Directory SYS.EXTERNAL_DIR found in the database
Directory SYS.DATA_PUMP_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

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:\ORACLE\ORADATA\ORCL102\SYSTEM01.DBF
converted datafile=C:\ORACLE\RPTREPOS\SYSTEM01.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:01:26
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00003 name=C:\ORACLE\ORADATA\ORCL102\SYSAUX01.DBF
converted datafile=C:\ORACLE\RPTREPOS\SYSAUX01.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00002 name=C:\ORACLE\ORADATA\ORCL102\UNDOTBS01.DBF
converted datafile=C:\ORACLE\RPTREPOS\UNDOTBS01.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00005 name=C:\ORACLE\ORADATA\ORCL102\EXAMPLE01.DBF
converted datafile=C:\ORACLE\RPTREPOS\EXAMPLE01.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00015 name=C:\ORACLE\ORADATA\ORCL102\LMT_XACT01.DBF
converted datafile=C:\ORACLE\RPTREPOS\LMT_XACT01.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00004 name=C:\ORACLE\ORADATA\ORCL102\USERS01.DBF
converted datafile=C:\ORACLE\RPTREPOS\USERS01.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00016 name=C:\ORACLE\ORADATA\ORCL102\LMT_REF01.DBF
converted datafile=C:\ORACLE\RPTREPOS\LMT_REF01.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:02
Run SQL script C:\ORACLE\RPTREPOS\RPTREPOS.SQL on the target platform to create database
Edit init.ora file C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INIT_00HGLPN4_1_0.ORA. This PFILE will be used to cr
eate the database on the target platform
To recompile all PL/SQL modules, run utlirp.sql and utlrp.sql on the target platform
To change the internal database identifier, use DBNEWID Utility
Finished backup at 16-APR-06

-----
-- Oracle-generated script (RPTREPOS.SQL) containing commands needed
-- to recreate database on target platform without any conversion. This 
-- file can then be edited on the target to place datafiles in appropriate
-- folders
-----

-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.

-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT PFILE='C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INIT_00HGLPN4_1_0.ORA'
CREATE CONTROLFILE REUSE SET DATABASE "RPTREPOS" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCH_D-RPTREPOS_ID-3040314982_S-38_T-1_A-578445806_00HGLPN4'  SIZE 50M,
  GROUP 2 'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCH_D-RPTREPOS_ID-3040314982_S-39_T-1_A-578445806_00HGLPN4'  SIZE 50M,
  GROUP 3 'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCH_D-RPTREPOS_ID-3040314982_S-40_T-1_A-578445806_00HGLPN4'  SIZE 50M
DATAFILE
  'C:\ORACLE\RPTREPOS\SYSTEM01.DBF',
  'C:\ORACLE\RPTREPOS\UNDOTBS01.DBF',
  'C:\ORACLE\RPTREPOS\SYSAUX01.DBF',
  'C:\ORACLE\RPTREPOS\USERS01.DBF',
  'C:\ORACLE\RPTREPOS\EXAMPLE01.DBF',
  'C:\ORACLE\RPTREPOS\LMT_XACT01.DBF',
  'C:\ORACLE\RPTREPOS\LMT_REF01.DBF'
CHARACTER SET AL32UTF8
;

-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;

-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP 
    ADD TEMPFILE 'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\DATA_D-RPTREPOS_I-3040314982_TS-TEMP_FNO-1_00HGLPN4'
     SIZE 202375168  
     AUTOEXTEND ON NEXT 655360  
     MAXSIZE 32767M;
-- End of tempfile additions.
--

set echo off
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt * Your database has been created successfully!
prompt * There are many things to think about for the new database. Here
prompt * is a checklist to help you stay on track:
prompt * 1. You may want to redefine the location of the directory objects.
prompt * 2. You may want to change the internal database identifier (DBID) 
prompt *    or the global database name for this database. Use the 
prompt *    NEWDBID Utility (nid).
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SHUTDOWN IMMEDIATE 
STARTUP UPGRADE PFILE='C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INIT_00HGLPN4_1_0.ORA'
@@ ?/rdbms/admin/utlirp.sql 
SHUTDOWN IMMEDIATE 
STARTUP PFILE='C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INIT_00HGLPN4_1_0.ORA'
-- The following step will recompile all PL/SQL modules.
-- It may take several hours to complete.
@@ ?/rdbms/admin/utlrp.sql 
set feedback 6;

-----
-- Oracle-generated initialization parameter file (INIT_00HGLPN4_1_0.ORA) 
-- for use during database creation on target platform without any conversion
-----
# Please change the values of the following parameters:
  control_files            = "C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\CF_D-RPTREPOS_ID-3040314982_00HGLPN4"
  db_recovery_file_dest    = "C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\flash_recovery_area"
  db_recovery_file_dest_size= 2147483648
  audit_file_dest          = "C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ADUMP"
  background_dump_dest     = "C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\BDUMP"
  user_dump_dest           = "C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UDUMP"
  core_dump_dest           = "C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\CDUMP"
  db_name                  = "RPTREPOS"

# Please review the values of the following parameters:
  __shared_pool_size       = 58720256
  __large_pool_size        = 4194304
  __java_pool_size         = 4194304
  __streams_pool_size      = 4194304
  __db_cache_size          = 41943040
  remote_login_passwordfile= "EXCLUSIVE"
  db_domain                = ""
  dispatchers              = "(PROTOCOL=TCP) (SERVICE=orcl102XDB)"

# The values of the following parameters are from source database:
  processes                = 150
  sga_max_size             = 134217728
  sga_target               = 117440512
  db_block_size            = 8192
  compatible               = "10.2.0.1.0"
  db_file_multiblock_read_count= 16
  undo_management          = "AUTO"
  undo_tablespace          = "UNDOTBS1"
  shared_servers           = 2
  max_shared_servers       = 5
  job_queue_processes      = 10
  open_cursors             = 300
  pga_aggregate_target     = 33554432

/* 
|| Listing 1.8: Completing the transport of the database on the target
||              platform. Note that this example converts the datafiles
||              on the target, and that the scripts generated on the 
||              source platform were edited to reflect the appropriate 
||              directories and file names on the target platform
*/

-----
-- Completion of transfer to target database:
-- 1.) Copy converted datafiles to target platform
-- 2.) Edit conversion scripts to reflect file locations on target server
-- 3.) Run script to create control files on target server
-- 3.) Run conversion scripts to convert all datafiles on target server
-- 4.) Open database in RESETLOGS mode
-- 5.) Bring all datafiles into READ WRITE mode on target server
-----

#####
# Edited RMAN script to complete datafile conversion
#####
RUN {
  CONVERT DATAFILE '/u01/app/oracle/oradata/RPTREPOS/SYSTEM01.DBF' 
    FROM PLATFORM 'Microsoft Windows IA (32-bit)' 
    FORMAT '/u01/app/oracle/oradata/RPTREPOS/system01.dbf';
  CONVERT DATAFILE '/u01/app/oracle/oradata/RPTREPOS/SYSAUX01.DBF' 
    FROM PLATFORM 'Microsoft Windows IA (32-bit)' 
    FORMAT '/u01/app/oracle/oradata/RPTREPOS/sysaux01.dbf';
  CONVERT DATAFILE '/u01/app/oracle/oradata/RPTREPOS/UNDOTBS01.DBF' 
    FROM PLATFORM 'Microsoft Windows IA (32-bit)' 
    FORMAT '/u01/app/oracle/oradata/RPTREPOS/sysaux01.dbf';
  CONVERT DATAFILE '/u01/app/oracle/oradata/RPTREPOS/EXAMPLE01.DBF' 
    FROM PLATFORM 'Microsoft Windows IA (32-bit)' 
    FORMAT '/u01/app/oracle/oradata/RPTREPOS/example01.dbf';
  CONVERT DATAFILE '/u01/app/oracle/oradata/RPTREPOS/LMT_XACT01.DBF' 
    FROM PLATFORM 'Microsoft Windows IA (32-bit)' 
    FORMAT '/u01/app/oracle/oradata/RPTREPOS/lmt_xact01.dbf';
  CONVERT DATAFILE '/u01/app/oracle/oradata/RPTREPOS/USERS01.DBF' 
    FROM PLATFORM 'Microsoft Windows IA (32-bit)' 
    FORMAT '/u01/app/oracle/oradata/RPTREPOS/users01.dbf';
  CONVERT DATAFILE '/u01/app/oracle/oradata/RPTREPOS/LMT_REF01.DBF' 
    FROM PLATFORM 'Microsoft Windows IA (32-bit)' 
    FORMAT '/u01/app/oracle/oradata/RPTREPOS/lmt_ref01.dbf';
}

#####
# Edited PFILE for use during creation of new database
#####

# Please change the values of the following parameters:
  control_files            = "/u01/app/oracle/oradata/RPTREPOS/control01.ctl"
  db_recovery_file_dest    = "/u01/app/oracle/flash_recovery_area"
  db_recovery_file_dest_size= 2147483648
  audit_file_dest          = "/u01/app/oracle/admin/RPTREPOS/adump"
  background_dump_dest     = "/u01/app/oracle/admin/RPTREPOS/bdump"
  user_dump_dest           = "/u01/app/oracle/admin/RPTREPOS/udump"
  core_dump_dest           = "/u01/app/oracle/admin/RPTREPOS/cdump"
  db_name                  = "RPTREPOS"
# Please review the values of the following parameters:
  __shared_pool_size       = 54525952
  __large_pool_size        = 4194304
  __java_pool_size         = 4194304
  __streams_pool_size      = 4194304
  __db_cache_size          = 46137344
  remote_login_passwordfile= "EXCLUSIVE"
  db_domain                = ""
# The values of the following parameters are from source database:
  processes                = 150
  sga_max_size             = 134217728
  sga_target               = 117440512
  db_block_size            = 8192
  compatible               = "10.2.0.1.0"
  db_file_multiblock_read_count= 16
  undo_management          = "AUTO"
  undo_tablespace          = "UNDOTBS1"
  shared_servers           = 2
  max_shared_servers       = 5
  job_queue_processes      = 10
  open_cursors             = 300
  pga_aggregate_target     = 33554432

/*
|| Script: RPTREPOS.TXP
|| Edited control file creation script to create database on 
|| target platform
*/

-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.

-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT PFILE='/u01/app/oracle/oradata/RPTREPOS/INIT_RPTREPOS.ORA'
CREATE CONTROLFILE REUSE SET DATABASE "RPTREPOS" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/RPTREPOS/redo01.log'  SIZE 50M,
  GROUP 2 '/u01/app/oracle/oradata/RPTREPOS/redo02.log'  SIZE 50M,
  GROUP 3 '/u01/app/oracle/oradata/RPTREPOS/redo03.log'  SIZE 50M
DATAFILE
  '/u01/app/oracle/oradata/RPTREPOS/SYSTEM01.DBF',
  '/u01/app/oracle/oradata/RPTREPOS/UNDOTBS01.DBF',
  '/u01/app/oracle/oradata/RPTREPOS/SYSAUX01.DBF',
  '/u01/app/oracle/oradata/RPTREPOS/USERS01.DBF',
  '/u01/app/oracle/oradata/RPTREPOS/EXAMPLE01.DBF',
  '/u01/app/oracle/oradata/RPTREPOS/LMT_XACT01.DBF',
  '/u01/app/oracle/oradata/RPTREPOS/LMT_REF01.DBF'
CHARACTER SET AL32UTF8
;

-----
-- Now perform conversion of datafiles via RMAN:
-----
$> rman target / 

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Apr 19 19:49:26 2006
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
connected to target database: RPTREPOS (DBID=3040314982, not open)

RUN {
  CONVERT DATAFILE '/u01/app/oracle/oradata/RPTREPOS/SYSTEM01.DBF'
    FROM PLATFORM 'Microsoft Windows IA (32-bit)'
    FORMAT '/u01/app/oracle/oradata/RPTREPOS/system01.dbf';
  CONVERT DATAFILE '/u01/app/oracle/oradata/RPTREPOS/SYSAUX01.DBF'
    FROM PLATFORM 'Microsoft Windows IA (32-bit)'
    FORMAT '/u01/app/oracle/oradata/RPTREPOS/sysaux01.dbf';
  CONVERT DATAFILE '/u01/app/oracle/oradata/RPTREPOS/UNDOTBS01.DBF'
    FROM PLATFORM 'Microsoft Windows IA (32-bit)'
    FORMAT '/u01/app/oracle/oradata/RPTREPOS/sysaux01.dbf';
  CONVERT DATAFILE '/u01/app/oracle/oradata/RPTREPOS/EXAMPLE01.DBF'
    FROM PLATFORM 'Microsoft Windows IA (32-bit)'
    FORMAT '/u01/app/oracle/oradata/RPTREPOS/example01.dbf';
  CONVERT DATAFILE '/u01/app/oracle/oradata/RPTREPOS/LMT_XACT01.DBF'
    FROM PLATFORM 'Microsoft Windows IA (32-bit)'
    FORMAT '/u01/app/oracle/oradata/RPTREPOS/lmt_xact01.dbf';
  CONVERT DATAFILE '/u01/app/oracle/oradata/RPTREPOS/USERS01.DBF'
    FROM PLATFORM 'Microsoft Windows IA (32-bit)'
    FORMAT '/u01/app/oracle/oradata/RPTREPOS/users01.dbf';
  CONVERT DATAFILE '/u01/app/oracle/oradata/RPTREPOS/LMT_REF01.DBF'
    FROM PLATFORM 'Microsoft Windows IA (32-bit)'
    FORMAT '/u01/app/oracle/oradata/RPTREPOS/lmt_ref01.dbf';
}
2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21> 22> 23> 24> 25> 26>
Starting backup at 19-APR-06
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input filename=/u01/app/oracle/oradata/RPTREPOS/SYSTEM01.DBF
converted datafile=/u01/app/oracle/oradata/RPTREPOS/system01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:04:19
Finished backup at 19-APR-06
 
Starting backup at 19-APR-06
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=/u01/app/oracle/oradata/RPTREPOS/SYSAUX01.DBF
converted datafile=/u01/app/oracle/oradata/RPTREPOS/sysaux01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:03:07
Finished backup at 19-APR-06
 
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=/u01/app/oracle/oradata/RPTREPOS/UNDOTBS01.DBF
converted datafile=/u01/app/oracle/oradata/RPTREPOS/undotbs01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:59
Finished backup at 19-APR-06
 
Starting backup at 19-APR-06
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=/u01/app/oracle/oradata/RPTREPOS/EXAMPLE01.DBF
converted datafile=/u01/app/oracle/oradata/RPTREPOS/example01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:55
Finished backup at 19-APR-06
 
Starting backup at 19-APR-06
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=/u01/app/oracle/oradata/RPTREPOS/LMT_XACT01.DBF
converted datafile=/u01/app/oracle/oradata/RPTREPOS/lmt_xact01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:08
Finished backup at 19-APR-06
 
Starting backup at 19-APR-06
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=/u01/app/oracle/oradata/RPTREPOS/USERS01.DBF
converted datafile=/u01/app/oracle/oradata/RPTREPOS/users01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:08
Finished backup at 19-APR-06
 
Starting backup at 19-APR-06
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=/u01/app/oracle/oradata/RPTREPOS/LMT_REF01.DBF
converted datafile=/u01/app/oracle/oradata/RPTREPOS/lmt_ref01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
Finished backup at 19-APR-06
 
RMAN>

/* 
|| Listing 1.9: Opening the database and recompiling all PL/SQL under the new Completing the conversion of the database
*/

SQL> alter database open resetlogs;
 
Database altered.

SQL> ALTER TABLESPACE TEMP
    ADD TEMPFILE '/u01/app/oracle/oradata/RPTREPOS/temp01.tmp'
    SIZE 202375168
    AUTOEXTEND ON
    NEXT 655360
    MAXSIZE 32767M;  2    3    4    5    6
 
Tablespace altered.
 
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP UPGRADE PFILE='C:\ORACLE\INIT_RPTREPOS.ORA'
SQL> @@ ?/rdbms/admin/utlirp.sql 

<<< Results edited for brevity >>>

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>#######################################################################

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP PFILE='/u01/app/oracle/oradata/RPTREPOS/INIT_RPTREPOS.ORA'
ORACLE instance started.
 
Total System Global Area  134217728 bytes
Fixed Size                  1218148 bytes
Variable Size              83888540 bytes
Database Buffers           46137344 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL> @@ ?/rdbms/admin/utlrp.sql
 
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN  2006-04-19 20:53:24

<<< Results edited for brevity >>>

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END  2006-04-19 21:18:24