In this post, I will demonstrate transporting a database on Linux 32 bit platform to Windows 32 bit.
Overview
TDB requires that data files be converted to the target platform format. The data file
conversion can occur on either the source system or the target system. When performing a
source system conversion, TDB creates a second copy of all data files on the source system
in the format of the target system. The converted data files must then be transferred to the
proper location on the target system.
Using TDB to migrate a database to a new platform of the same endian format consists of the following high-level steps:
1. Check prerequisites
2. Prepare for the platform migration
3. Start the database in READ ONLY mode
4. Verify the database is ready for migration
5. Run the RMAN CONVERT DATABASE command
6. Move necessary files to the target system
7. Complete the migration
Implementation:
– Check Prerequisites
– check the platform name of current database –
SQL>SELECT PLATFORM_NAME
FROM V$DATABASE;
PLATFORM_NAME
——————————
Linux IA (32-bit)
– Check the endian format of current platform
– Note that ENDIAN format is little
SQL>SELECT PLATFORM_ID, PLATFORM_NAME, ENDIAN_FORMAT
FROM V$TRANSPORTABLE_PLATFORM
WHERE UPPER(PLATFORM_NAME) LIKE ‘%LINUX IA%';
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
———– —————————— ————–
10 Linux IA (32-bit) Little
11 Linux IA (64-bit) Little
– Check that endian format of the target platform i.e. Windows IA(32-bit) is also little
– Hence database can be transported
SQL>SELECT PLATFORM_ID, PLATFORM_NAME, ENDIAN_FORMAT
FROM V$TRANSPORTABLE_PLATFORM
WHERE UPPER(PLATFORM_NAME) LIKE ‘%MICROSOFT WINDOWS%';
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
———– —————————— ————–
7 Microsoft Windows IA (32-bit) Little
8 Microsoft Windows IA (64-bit) Little
12 Microsoft Windows x86 64-bit Little
If the target platform does not appear in the output from V$TRANSPORTABLE_PLATFORM, then the database cannot be migrated using TDB.
— Start the database in READ ONLY mode
SQL> shutdown immediate;
startup mount;
alter database open read only;
— Verify the database is ready for migration
SQL>
SET SERVEROUTPUT ON;
DECLARE
db_ready BOOLEAN;
BEGIN
db_ready :=
DBMS_TDB.CHECK_DB(‘Microsoft Windows IA (32-bit)’,DBMS_TDB.SKIP_READONLY);
if db_ready
then
dbms_output.put_line(‘YES your database can be transported to Windows
platform’);
else
dbms_output.put_line(‘NO your database can not be transported to WIndows
Platform’);
end if;
end;
/
– Make a folder to hold converted datafiles
$mkdir /home/oracle/convertdb
— Run the RMAN CONVERT DATABASE Command
The CONVERT DATABASE command specified in the example creates
– a transport script named /home/oracle/convertdb/transport.sql which contains SQL statements used to create the new database on the destination platform,
– a PFILE initnewdb.ora in file /home/oracle/convertdb for use with the new database on the destination platform, containing settings used from the source database. Several entries at the top of the PFILE should be edited when the database is moved to the destination platform
– a copy of all data files in the =/home/oracle/convertdb directory in the format of the target platform ‘Microsoft Windows IA (32-bit)
RMAN>CONVERT DATABASE
NEW DATABASE ‘newdb’
TRANSPORT SCRIPT ‘/home/oracle/convertdb/transport.sql’
TO PLATFORM ‘Microsoft Windows IA (32-bit)’
FORMAT ‘/home/oracle/convertdb/newdb’
DB_FILE_NAME_CONVERT ‘/u01/app/oracle/oradata/linuxdb/’ ‘/home/oracle/convertdb/';
Starting conversion at source at 18-NOV-12
….
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00001 name=/u01/app/oracle/oradata/linuxdb/system01.dbf
converted datafile=/home/oracle/convertdb/system01.dbf
…
input datafile file number=00004 name=/u01/app/oracle/oradata/linuxdb/users01.dbf
converted datafile=/home/oracle/convertdb/users01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Edit init.ora file /home/oracle/convertdb/init_newdb.ora. This PFILE will be used to create
the database on the target platform
Run SQL script /home/oracle/convertdb/transport.sql on the target platform to create
database
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 conversion at source at 18-NOV-12
– Review and Edit the PFILE to change any settings for the destination database.
$vi /home/oracle/convertdb/init_newdb.ora
# Please change the values of the following parameters:
control_files = “c:\app\administrator\oradata\newdb\control01.ctl”
db_recovery_file_dest=”c:\app\administrator\flash_recovery_area”
audit_file_dest = “c:\app\administrator\admin\newdb\adump”
– Review and edit the transport script transport.sql
$vi /home/oracle/convertdb/transport.sql
– Modify the following :
STARTUP NOMOUNT PFILE=’C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DBS
\initnewdb.ora';
LOGFILE
GROUP 1 ‘C:\APP\ADMINISTRATOR\ORADATA\NEWDB\REDO01.LOG’ SIZE 50M BLOCKSIZE 512,
GROUP 2 ‘C:\APP\ADMINISTRATOR\ORADATA\NEWDB\REDO02.LOG’ SIZE 50M BLOCKSIZE 512,
GROUP 3 ‘C:\APP\ADMINISTRATOR\ORADATA\NEWDB\REDO03.LOG’ SIZE 50M BLOCKSIZE 512
DATAFILE
‘C:\APP\ADMINISTRATOR\ORADATA\NEWDB\system01.dbf’,
‘C:\APP\ADMINISTRATOR\ORADATA\NEWDB\sysaux01.dbf’,
‘C:\APP\ADMINISTRATOR\ORADATA\NEWDB\undotbs01.dbf’,
‘C:\APP\ADMINISTRATOR\ORADATA\NEWDB\users01.dbf’,
‘C:\APP\ADMINISTRATOR\ORADATA\NEWDB\example01.dbf’
ALTER TABLESPACE TEMP
ADD TEMPFILE
‘C:\APP\ADMINISTRATOR\ORADATA\NEWDB\TEMP01.DBF’
SIZE 20971520 AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
STARTUP UPGRADE PFILE=’C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DBS\initnewdb.ora';
@@ ?\rdbms\admin\utlirp.sql
SHUTDOWN IMMEDIATE
STARTUP PFILE=’C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DBS\initnewdb.ora';
– Make folders on target windows host
c:\>mkdir c:\app\administrator\oradata\newdb
mkdir c:\app\administrator\admin\newdb\adump”
– Copy all the files in /home/oracle/convertdb folder to c:\app\administrator\oradata\newdb
i.e. datafiles
Pfile
transport script
– Move PFILE to $ORACLE_HOME/database i.e. c:\app\administrator\product
\11.2.0\dbhome_1\dbs
C:\>copy c:\app\administrator\oradata\newdb\init_newdb.ora c:\app\administrator\product
\11.2.0\dbhome_1\dbs\initnewdb.ora
– create a service for newdb on windows
c:\>oradim -new -sid newdb
– Execute the transport script in SQL*Plus to create the new database on the destination host.
c:\>set oracle_sid=newdb
sqlplus / as sysdba
SQL>@c:\app\administrator\oradata\newdb\transport.sql
– check that database has been transported —
SQL>select * from hr.employees;
——————————————————————————-
Related links:
———————–
Hi Anju,
Thankx a lot. You made my day with this article……
I planned 4 hrs for this task but I started and ended successfully with your above post in just 20 mins….
Hi Sirish,
It’s nice to know that the post was useful. Thanks for your time.
Regards
Anju Garg
Hi Anju,
Your blog is extremely helpful .
It has covered new feature and new feature and in more practical way.
This is first blog I found very simple and very informative .
Thanks
Amit Bansode
Thanks Amit!
Your comments, queries and suggestions are always welcome.
Regards
Anju Garg
Thanks about your post, but what’s the /u01/xxx location? thanks.
Thanks for your time. /u01/app/oracle/oradata/linuxdb location refers to the location of datafiles on linux.
Regards
Anju Garg
Thank you very much.
hi anju,
great blog..please keep posting.
Thanks
Raghavendra
Hi Ragahavendra
Thanks for your time.
Your comments and suggestions are always welcome!
Regards
Anju Garg
Hi Anju,
Your blog is very helpful. One doubt regarding this post
– create a service for newdb on windows
c:\>oradim -new -sid newdb
what extractly oradim do in above command. we use have a message called
instance created.
Thanks
Shiv
Hi Shiv,
Pls refer to following link:
http://docs.oracle.com/cd/B19306_01/win.102/b14304/create.htm#i1006533
Hope it helps
Regards
Anju
can the source database still be used after migration?
Yes, the source database still be used after migration.
Regards
Anju
I am getting below errors
C:\Windows\system32>set oracle_sid=newdb
C:\Windows\system32>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Aug 8 16:11:05 2016
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to an idle instance.
SQL> @G:\app\maruf.hossain\oradata\newdb\transport.sql
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name ‘NEWDBLISTENER_TWOTEST’
Please help to resolve the issue.
Hi
Pls set LOCAL_LISTENER to ” in pfile and retry
Hope it helps
Regards
Anju
Hi,
Now getting below
C:\Windows\system32>set ORACLE_SID=newdb
C:\Windows\system32>sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Tue Aug 9 11:46:06 2016
Copyright (c) 1982, 2010, Oracle. All rights reserved.
SQL> @G:\app\maruf.hossain\oradata\newdb\transport.sql
ORA-01031: insufficient privileges
SP2-0640: Not connected
Thanks
Maruf
Hi
Pls connect using sqlplus / as sysdba first
Regards
Anju
Hi
Now getting below
C:\Windows\system32>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Aug 9 14:10:44 2016
Copyright (c) 1982, 2010, Oracle. All rights reserved.
ERROR:
ORA-01031: insufficient privileges
Enter user-name:
Thanks
Maruf
set oracle_sid=newdb
regards
Anju
D:\app\rehan\virtual\product\12.2.0\dbhome_1\bin>sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Fri May 25 14:54:41 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.
SQL> @d:\app\rehan\virtual\product\12.2.0\dbhome_1\oradata\newdb\transport.sql
LRM-00109: could not open parameter file ‘/home/oracle/convertdb/init_newdb.ora’
Hi Rehan,
it seems you have not modified modified transport script to reflect location of pfile on destination host.
regards
Anju Garg