Oracle Server - Enterprise Edition - Version: 10.2.0.2 and later Information in this document applies to any platform.
Note:-
Performing Cross platform database migration using Rman
Convert Database feature in 10g Release 2 on the Destination
Host
The below example demonstrates migration from Windows 32-bit to Linux 32-bit:
Note the following restrictions exist with Cross-Platform Transportable Database:
1) The principal restriction on cross-platform transportable database is that the source and destination platform must share the same endian format.
2) Redo log files and control files from the source database are not transported. New control files and redo log files are created for the new database during the transport process, and an Open Resetogs is performed once the new database is created. Similarly, tempfiles belonging to locally managed temporary tablespaces are not transported. The temporary tablespace will be re-created on the target platform when the transport script is run.
3) BFiles, External tables and directories, Password files are not transported.
4) The Source and the target database version must be equal / greater than 10.2.0. version
The below example demonstrates migration from Windows 32-bit to Linux 32-bit:
Note the following restrictions exist with Cross-Platform Transportable Database:
1) The principal restriction on cross-platform transportable database is that the source and destination platform must share the same endian format.
2) Redo log files and control files from the source database are not transported. New control files and redo log files are created for the new database during the transport process, and an Open Resetogs is performed once the new database is created. Similarly, tempfiles belonging to locally managed temporary tablespaces are not transported. The temporary tablespace will be re-created on the target platform when the transport script is run.
3) BFiles, External tables and directories, Password files are not transported.
4) The Source and the target database version must be equal / greater than 10.2.0. version
SOLUTION
1) Check that the source and destination platform belong to same ENDIAN format in the view V$TRANSPORTABLE_PLATFORM. We will try to transport a database from Windows (32-bit) to Linux (32-bit). As seen below both belong to Little endian format:
SQL> select PLATFORM_NAME, ENDIAN_FORMAT from
V$TRANSPORTABLE_PLATFORM;PLATFORM_NAME ENDIAN_FORMAT---------------------------------------- --------------Solaris[tm] OE (32-bit) BigSolaris[tm] OE (64-bit) BigMicrosoft Windows IA (32-bit) LittleLinux IA (32-bit) LittleAIX-Based Systems (64-bit) BigHP-UX (64-bit) BigHP Tru64 UNIX LittleHP-UX IA (64-bit) BigLinux IA (64-bit) LittleHP Open VMS LittleMicrosoft Windows IA (64-bit) LittleIBM zSeries Based Linux BigLinux 64-bit for AMD LittleApple Mac OS BigMicrosoft Windows 64-bit for AMD LittleSolaris Operating System (x86) LittleIBM Power Based Linux Big17 rows selected.
2) Open the
database in read only mode
SQL> alter database open read only;Database altered.3) Use DBMS_TDB.CHECK_DB to check whether the database can be transported to a desired destination platform, and whether the current state of the database (such as incorrect compatibility settings, in-doubt or active transactions) permits transport. Make sure your database is open in READ ONLY mode before you call DBMS_TDB.CHECK_DB. Since we need to transport to Linux we will call the procedure with following arguments:
SQL> set serveroutput onSQL> declare2 db_ready boolean;3 begin4 db_ready := dbms_tdb.check_db('Linux IA (32-bit)');5 end;6 /PL/SQL procedure successfully completed.
If you call DBMS_TDB.CHECK_DB
and no messages are displayed indicating conditions preventing transport BEFORE
the "PL/SQL procedure successfully completed message", then your
database is ready for transport.
4) Use DBMS_TDB.CHECK_EXTERNAL to identify any external tables, directories or BFILEs. RMAN cannot automate the transport of such files as mentioned above.
4) Use DBMS_TDB.CHECK_EXTERNAL to identify any external tables, directories or BFILEs. RMAN cannot automate the transport of such files as mentioned above.
SQL> set serveroutput onSQL> declare2 external boolean;3 begin4 external := dbms_tdb.check_external;5 end;6 /The following directories exist in the database:SYS.DATA_PUMP_DIRPL/SQL procedure successfully completed.
If there are no external
objects, then this procedure completes with no output. If there are external
objects, however, the output will be somewhat similar to above.
5) Now once the database is ready for transport , make sure as to where you are going to convert the datafiles.You may choose to convert the datafiles of the database being transported on the destination platform instead of the source platform. Reasons for doing this include:
5) Now once the database is ready for transport , make sure as to where you are going to convert the datafiles.You may choose to convert the datafiles of the database being transported on the destination platform instead of the source platform. Reasons for doing this include:
+
+
Avoiding any performance overhead on the source host
due to the conversion process.+
Distributing a database from one source system to multiple recipients on
several different platforms.6) Run the RMAN CONVERT DATABASE command on the source platform specifying the ON TARGET PLATFORM argument. When used in this manner, the command syntax is as follows:
RMAN> CONVERT DATABASE ON TARGET PLATFORM2> CONVERT SCRIPT 'C:\convertscript.rman'3> TRANSPORT SCRIPT 'C:\transportscript.sql'4> new database 'win10g'5> FORMAT 'C:\%U';Starting convert at 12-FEB-07using channel ORA_DISK_1Directory SYS.DATA_PUMP_DIR found in the databaseUser SYS with SYSDBA and SYSOPER privilege found in password filechannel ORA_DISK_1: starting to check datafilesinput datafile fno=00001 name=D:\ORACLE\ORADATA\ORA10G\DATA\SYSTEM.DBFchannel ORA_DISK_1: datafile checking complete, elapsed time: 00:00:00channel ORA_DISK_1: starting to check datafilesinput datafile fno=00002 name=D:\ORACLE\ORADATA\ORA10G\DATA\UNDOTBS1.DBFchannel ORA_DISK_1: datafile checking complete, elapsed time: 00:00:00channel ORA_DISK_1: starting to check datafilesinput datafile fno=00003 name=D:\ORACLE\ORADATA\ORA10G\DATA\SYSAUX.DBFchannel ORA_DISK_1: datafile checking complete, elapsed time: 00:00:00channel ORA_DISK_1: starting to check datafilesinput datafile fno=00004 name=D:\ORACLE\ORADATA\ORA10G\DATA\USERS.DBFchannel ORA_DISK_1: datafile checking complete, elapsed time: 00:00:00Run SQL script C:\TRANSPORTSCRIPT.SQL on the target platform to create
databaseEdit init.ora file C:\INIT_00I9SC77_1_0.ORA. This PFILE will be used to
create the database on the target platformRun RMAN script C:\CONVERTSCRIPT.RMAN on target platform to convert
datafilesTo recompile all PL/SQL modules, run utlirp.sql and utlrp.sql on the
target platformTo change the internal database identifier, use DBNEWID UtilityFinished backup at 12-FEB-07
The CONVERT DATABASE ON
TARGET PLATFORM produces a transport script containing SQL*Plus commands to
create a new database on the destination platform, and a PFILE for the new
database containing the same settings as the source database.The pfile will be
generated in location specified by the Format command.
CONVERT DATABASE ON TARGET PLATFORM also generates a convert script containing RMAN CONVERT DATAFILE commands for each of the datafiles of the database being transported. The source datafiles must be copied unconverted to some temporary location at the destination, and then the convert script must be run at the destination to actually convert the datafiles to a format usable by the destination host.
Note that CONVERT DATABASE ON TARGET PLATFORM does not produce converted datafile copies.
If the filesystem containing the datafiles of the source database is accessible from the destination system using the same path names, then you can use the convert script on the destination host without any changes. The CONVERT DATAFILE commands in the script produce datafile copies in the locations specified by FORMAT, converted for use with the new database. (Once the convert script has been run, the source database can be opened for read-write access again.)
Otherwise, while the datafiles are still read-only, copy them to a temporary location. (As soon as copies of the datafiles are made, the source database can be opened read-write again.) If necessary, move the copies of the datafiles to a temporary location on the destination host, and then update each CONVERT DATAFILE command in the convert script to use the temporary location of each datafile as input and the FORMAT parameter of each CONVERT command to specify the desired final location of the datafiles of the transported database.
7) Now copy the following files to the destination machine manually :
CONVERT DATABASE ON TARGET PLATFORM also generates a convert script containing RMAN CONVERT DATAFILE commands for each of the datafiles of the database being transported. The source datafiles must be copied unconverted to some temporary location at the destination, and then the convert script must be run at the destination to actually convert the datafiles to a format usable by the destination host.
Note that CONVERT DATABASE ON TARGET PLATFORM does not produce converted datafile copies.
If the filesystem containing the datafiles of the source database is accessible from the destination system using the same path names, then you can use the convert script on the destination host without any changes. The CONVERT DATAFILE commands in the script produce datafile copies in the locations specified by FORMAT, converted for use with the new database. (Once the convert script has been run, the source database can be opened for read-write access again.)
Otherwise, while the datafiles are still read-only, copy them to a temporary location. (As soon as copies of the datafiles are made, the source database can be opened read-write again.) If necessary, move the copies of the datafiles to a temporary location on the destination host, and then update each CONVERT DATAFILE command in the convert script to use the temporary location of each datafile as input and the FORMAT parameter of each CONVERT command to specify the desired final location of the datafiles of the transported database.
7) Now copy the following files to the destination machine manually :
+ Datafiles+ Transport.sql+ Convertscript.rman+ Pfile generated by the convert database command.
8) Edit the
PFILE moved on the destination host to change the environment specific
parameters.
9) Create a dummy Controlfile
9) Create a dummy Controlfile
sqlplus "/ as sysdba"SQL*Plus: Release 10.2.0.1.0 - Production on Mon Feb 12 20:10:52 2007Copyright (c) 1982, 2005, Oracle. All rights reserved.Connected to an idle instance.SQL> startup nomount;ORACLE instance started.Total System Global Area 201326592 bytesFixed Size 1218484 bytesVariable Size 67110988 bytesDatabase Buffers 125829120 bytesRedo Buffers 7168000 bytesSQL>SQL> CREATE CONTROLFILE REUSE SET DATABASE "WIN10G"
RESETLOGS ARCHIVELOGMAXLOGFILES 16MAXLOGMEMBERS 3MAXDATAFILES 100MAXINSTANCES 8MAXLOGHISTORY 292LOGFILEGROUP 4 ' /u01/app/oracle/oradata/win10g/redo01.log' SIZE 25M,GROUP 5 ' /u01/app/oracle/oradata/win10g/redo02.log' SIZE 25M,GROUP 6 ' /u01/app/oracle/oradata/win10g/redo03.log' SIZE 25MDATAFILE'/u01/app/oracle/oradata/win10g/SYSTEM01.DBF','/u01/app/oracle/oradata/win10g/SYSAUX01.DBF','/u01/app/oracle/oradata/win10g/UNDOTBS01.DBF','/u01/app/oracle/oradata/win10g/USERS01.DBF'CHARACTER SET WE8MSWIN1252;Control file created.
10) Now edit
the file Convertscript.rman and make necessary changes with respect to the
filesystem and the file names.Now once the changes are done run the script from
rman prompt
[oracle@test-br win10g]$ rman target / nocatalog
'@/u01/CONVERTSCRIPT.RMAN'Recovery Manager: Release 10.2.0.1.0 - Production on Mon Feb 12 20:24:18
2007Copyright (c) 1982, 2005, Oracle. All rights reserved.connected to target database: WIN10G (DBID=3926158280, not open)using target database control file instead of recovery catalogRMAN> RUN {CONVERT DATAFILE '/u01/app/oracle/oradata/win10g/SYSTEM01.DBF'FROM PLATFORM 'Microsoft Windows IA (32-bit)'FORMAT '/u01/app/oracle/oradata/win10g/SYSTEM01.DBF ';CONVERT DATAFILE '/u01/app/oracle/oradata/win10g/SYSAUX01.DBF'FROM PLATFORM 'Microsoft Windows IA (32-bit)'FORMAT '/u01/app/oracle/oradata/win10g/SYSAUX01.DBF';CONVERT DATAFILE '/u01/app/oracle/oradata/win10g/UNDOTBS01.DBF'FROM PLATFORM 'Microsoft Windows IA (32-bit)'FORMAT '/u01/app/oracle/oradata/win10g/UNDOTBS01.DBF';CONVERT DATAFILE '/u01/app/oracle/oradata/win10g/USERS01.DBF'FROM PLATFORM 'Microsoft Windows IA (32-bit)'FORMAT '/u01/app/oracle/oradata/win10g/USERS01.DBF';}Starting backup at 12-FEB-07allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=97 devtype=DISKchannel ORA_DISK_1: starting datafile conversioninput filename=/u01/oracle/oradata/win10g/SYSTEM.DBFconverted datafile=/u01/oracle/oradata/win10g/data/SYSTEM.DBFchannel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:16Finished backup at 12-FEB-07Starting backup at 12-FEB-07using channel ORA_DISK_1channel ORA_DISK_1: starting datafile conversioninput filename=/u01/oracle/oradata/win10g/SYSAUX.DBFconverted datafile=/u01/oracle/oradata/win10g/data/SYSAUX.DBFchannel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:09Finished backup at 12-FEB-07Starting backup at 12-FEB-07using channel ORA_DISK_1channel ORA_DISK_1: starting datafile conversioninput filename=/u01/oracle/oradata/win10g/UNDOTBS1.DBFconverted datafile=/u01/oracle/oradata/win10g/data/UNDOTBS1.DBFchannel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:08Finished backup at 12-FEB-07Starting backup at 12-FEB-07using channel ORA_DISK_1channel ORA_DISK_1: starting datafile conversioninput filename=/u01/oracle/oradata/win10g/USERS.DBFconverted datafile=/u01/oracle/oradata/win10g/data/USERS.DBFchannel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:04Finished backup at 12-FEB-07Recovery Manager complete.
10) Now
shutdown the database and delete the dummy controlfile.
11) Now edit the TRANSPORT sql script to reflect the new path for datafiles and redolog files in the CREATE CONTROLFILE section of the script. Also change all references to the INIT.ORA in the script to the new path and name of the INIT.ORA modified above.
12) Once the PFILE and TRANSPORT sql scripts are suitably modified invoke SQLPLUS on the destination host after setting the Oracle environment parameters and then run TRANSPORT.sql as:
11) Now edit the TRANSPORT sql script to reflect the new path for datafiles and redolog files in the CREATE CONTROLFILE section of the script. Also change all references to the INIT.ORA in the script to the new path and name of the INIT.ORA modified above.
12) Once the PFILE and TRANSPORT sql scripts are suitably modified invoke SQLPLUS on the destination host after setting the Oracle environment parameters and then run TRANSPORT.sql as:
[oracle@test-br ora10g]$ export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1[oracle@test-br ora10g]$ export ORACLE_SID=win10g[oracle@test-br ora10g]$ export PATH=$ORACLE_HOME/bin:$PATH[oracle@test-br LinDB10g]$ sqlplus "/ as sysdba"SQL*Plus: Release 10.2.0.1.0 - Production on Sat Feb 3 01:55:46 2007Copyright (c) 1982, 2005, Oracle. All rights reserved.Connected to an idle instance.SQL> @’/u01/TRANSPORTSCRIPT.SQL’ORACLE instance started.Total System Global Area 201326592 bytesFixed Size 1218484 bytesVariable Size 67110988 bytesDatabase Buffers 125829120 bytesRedo Buffers 7168000 bytesControl file created.Database altered.~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~* Your database has been created successfully!* There are many things to think about for the new database. Here* is a checklist to help you stay on track:* 1. You may want to redefine the location of the directory objects.* 2. You may want to change the internal database identifier (DBID)* or the global database name for this database. Use the* NEWDBID Utility (nid).~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Database closed.Database dismounted.ORACLE instance shut down.ORACLE instance started.Total System Global Area 201326592 bytesFixed Size 1218484 bytesVariable Size 67110988 bytesDatabase Buffers 125829120 bytesRedo Buffers 7168000 bytesDatabase mounted.Database opened.......Note:- Try everything at your own risk, if any thing happens wrong with your database so blogger will not be responsible.
No comments:
Post a Comment