/*
|| 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