Migration steps DUPLICATING DATABASES AND TRANSPORTING DATA
1) Oracle’s transportable tablespaces feature lets you move large amounts of data much faster than an export and import of data. This is because the transportable tablespace opera-tion requires that you copy only those datafiles pertaining to a tablespace and merely export and import the metadata concerning the tablespaces.
The transportable tablespace feature shighly useful when archiving historical data, performing tablespace point-in-time recovery,and exporting and importing large data warehouse table partitions. You can transport table-spaces across platforms,which means you can use this feature to easily migrate Oracle databases from one platform to another or move data from data warehouses to data martsrunning on smaller platforms.
2)big disadvantage in that method(export/import method) is that the transportable tablespaces must be put in a “read-only” mode, thus affect-ing database availability. It not only may be time-consuming to put the tablespaces into read-only mode, but users can’t write to those tablespaces for the duration of the tablespace transport. You don’t have any of these limitations when you use RMAN backups as the basis of your transportable tablespace operation.You can create transportable tablespace sets from RMAN backups. You must have a prior backup of all the datafiles that belong to the transportable tablespace set and the archived redo logs, so RMAN can use them to recover to the target point in time.You may use this capability of RMAN for creating transportable tablespace sets for report-ing purposes. Transportable tablespace sets are also highly useful during the instantiation of streams. The one big requirement for using RMAN backups to create transportable table-
spaces is that your RMAN backups must be recoverable to the SCN at which you want the transportable tablespaces.
RMAN creates an auxiliary database instance through which it creates the transportable tablespace sets. RMAN does quite a few things in order to prepare the transportable table set.
Here’s a summary of the actions set off when you execute the transport tablespace
command:
RMAN creates an auxiliary database instance through which it creates the transportable
tablespace sets. RMAN does quite a few things in order to prepare the transportable table set.
Here’s a summary of the actions set off when you execute the transport tablespace
command:
1. RMAN starts an auxiliary instance in nomount mode first. You don’t have to specify a
parameter file for this auxiliary instance, since RMAN automatically creates the file.
The name of the auxiliary instance is also made up by RMAN. In our example shown
in the previous section, the ORACLE_SID of the auxiliary instance is aux (Creating
automatic instance, with SID='aux').
2. RMAN restores a backup of the target database control file and uses it to mount the
auxiliary database.
3. Using the switch operation, RMAN restores all datafiles from the target database
for the auxiliary instance. These files are restored to the location specified by the
auxiliary destination clause in the transport tablespace command.
4. RMAN also stores the files pertaining to the tablespaces in the transportable table-
space set in the location specified by the tablespace destination parameter in the
transport tablespace command.
■ DUPLICATING DATABASES AND TRANSPORTING DATA
5. Once the datafiles from the target database are all restored to the auxiliary database
location, RMAN performs a point-in-time recovery of the auxiliary instance. In our
example, since we didn’t specify a target time, a complete database recovery is per-
formed. Note that all applicable archived redo logs are also automatically restored and
applied by RMAN during the recovery process. Once the recovery is finished, an open
resetlogs operation is performed on the auxiliary database by RMAN.
6. RMAN invokes the Data Pump Export utility (in the transportable tablespace mode)
to create the export dump file containing the tablespaces in the transportable table-
space set. By default, the export dump file is placed in the location specified by the
tablespace destination clause of the transport tablespace command.
7. RMAN also simultaneously generates a Data Pump Import script you can use to plug
in the transported tablespaces into the target database. The default script name is
impscrpt.sql, and this script is located in the directory specified by the tablespace
destination clause of the transport tablespace command.
8. RMAN shuts down the auxiliary instance and automatically deletes all the files created
and used during the transport tablespace process. The only files that remain are the
transportable set files, the Data Pump Export log, and the sample Data Pump Import
script.
In the example shown in the solution, the RMAN-created impscrpt.sql script was used to
import the tablespaces into the target database. The script utilizes a PL/SQL script to import
the tablespaces.
15-10. Resynchronizing a Duplicate Database
Problem
You want to synchronize a duplicate database with its parent database.
Solution
Once you create a duplicate database from a source database, you can periodically “update”
or synchronize the duplicate database by simply rerunning the duplicate command over
again, in essence re-creating the duplicate database (reduplicating the target database). In the
following example, we first perform a one-time setup of the new datafile names by using the
configure auxname clause, as shown here:
RMAN> connect target /
RMAN> connect catalog rman/cat@catdb
RMAN> connect auxiliary sys/sammyy1@dupdb
RMAN> run {
configure auxname for datafile 1 to '/oradata1/system01.dbf';
configure auxname for datafile 2 to '/oradata2/sysaux01.dbf';
configure auxname for datafile 3 to '/oradata3/undotbs01.dbf';
configure auxname for datafile 4 to '/oradata4/drsys01';
configure auxname for datafile 5 to '/oradata5/example01.dbf';
configure auxname for datafile 6 to '/oradata6/indx01.dbf';
configure auxname for datafile 7 to '/oradata7/users01.dbf';
}
Synchronize the duplicate database with the source database by periodically executing
the duplicate target database command to re-create the duplicate database. For example:
RMAN> connect target /
RMAN> connect catalog rman/cat@catdb
RMAN> connect auxiliary sys/sammyy1@dupdb
RMAN> duplicate target database to dupdb
logfile
group 1 ('/duplogs/redo01a.log',
'/duplogs/redo01b.log') size 200k reuse,
group 2 ('/duplogs/redo02a.log',
'/duplogs/redo02b.log') size 200k reuse;
You can schedule this script for running on a daily or a weekly basis, thus creating a new
and up-to-date duplicate database on a continuous basis.
■ DUPLICATING DATABASES AND TRANSPORTING DATA
How It Works
To synchronize a duplicate database with the parent database, you must in essence re-create
the duplicate database by transferring the latest copies of the source database files to the
duplicate database.
To set up a database for periodic synchronization, you must first use the configure
command to set persistent new names for the datafiles. Once you set the persistent datafile
names, the filenames will be recorded in the control file, and RMAN will use the same file-
names each time you synchronize the duplicate database by using the duplicate command.
Remember that you have to employ the configure auxname clause only once—the first
time you duplicate the database. RMAN will reuse the same persistent filenames anytime you
execute the duplicate command after configuring the auxiliary filenames to synchronize the
duplicate database.
Transporting Tablespaces on the Same OS Platform
Problem
You want to transport tablespaces using RMAN backups instead of performing the trans-
portable tablespaces operation on the “live” production database.
Solution
You create a transportable tablespace set by executing the RMAN command transport
tablespace. The following example shows how to transport tablespaces on identical operating
system platforms by utilizing RMAN backups. Here are the steps you must follow to transport
the tablespaces:
1. Make sure the tablespaces you plan to transport are self-contained. To be considered
self-contained, the tablespace set you want to transport mustn’t contain references
pointing outside those tablespaces, such as an index on a table that doesn’t belong to
one of the tablespaces you’re transporting. In the following example, the transportable
tablespace set consists of two tablespaces—test1 and test2. Use the transport_set_check
procedure of the DBMS_TTS package to verify whether the two tablespaces are self-
contained, as shown here:
SQL> execute sys.dbms_tts.transport_set_check('test1, test2',TRUE);
PL/SQL procedure successfully completed.
SQL>
To confirm that there are no errors because of the tablespaces, you can also query the
sys.transport_set_violations table after running the sys.dbms_tts.transport_set
procedure to verify the results and view error messages if there are any.
■ Note You don’t have to manually make the tablespaces you are transporting read-only. RMAN will do this as part of the transport tablespace command.
DUPLICATING DATABASES AND TRANSPORTING DATA
2. Generate the transportable tablespace set by issuing the transport tablespace com-
mand. During regular (without RMAN) tablespace transport, you use an operating
system utility such as the cp or copy command to copy the database files that belong to
the tablespaces in the transportable tablespace set. Here, however, since we’re using
RMAN to transport the tablespaces, we use the RMAN backups for this purpose:
RMAN> transport tablespace test1,test2
2> tablespace destination '/u05/app/oracle/transportdest'
3> auxiliary destination '/u05/app/oracle/auxdest';
Creating automatic instance, with SID='aux'
db_name=NINA
db_unique_name=tspitr_NINA_aux
...
starting up automatic instance NINA
Oracle instance started
contents of Memory Script:
{
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
...
}
executing command: SET until clause
Starting restore at 20-JUN-07
...
Finished restore at 20-JUN-07
sql statement: alter database mount clone database
alter clone database open resetlogs;
executing command: SET until clause
executing command: SET NEWNAME
...
Starting restore at 20-JUN-07
...
Finished restore at 20-JUN-07
...
Starting recover at 20-JUN-07
...
Finished recover at 20-JUN-07
database opened
# export the tablespaces in the recovery set
host 'expdp userid=\"/@\(DESCRIPTION=\(ADDRESS=\(PROTOCOL=beq\)
\(PROGRAM=/home/oracle/product/11.1.0/db_1/bin/oracle\)
\(ARGV0=oracleaux\)\(ARGS=^'\(DESCRIPTION=\(LOCAL=YES\)
\(ADDRESS=\(PROTOCOL=beq\)\)\)^'\)\(ENVS=^'ORACLE_SID=aux^'\)\)
\(CONNECT_DATA=\(SID=aux\)\)\) as sysdba\" transport_tablespaces=
TEST1,
473474
DUPLICATING DATABASES AND TRANSPORTING DATA
TEST2 dumpfile=
dmpfile.dmp directory=
STREAMS_DIROBJ_DPDIR logfile=
explog.log';
}
sql statement: alter tablespace TEST1 read only
sql statement: alter tablespace TEST2 read only
sql statement: create or replace directory STREAMS
DIROBJ_DPDIR as "/u05/app/oracle/transportdest"
...
Removing automatic instance
shutting down automatic instance
Oracle instance shut down
Automatic instance removed
...
RMAN>
Export: Release 11.1.0.1.0 - Beta on Tuesday, 20 June, 2007 2:27:03
Copyright © 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition
Release 11.1.0.1.0- Beta
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": userid="/********@
(DESCRIPTION=(ADDRESS=(PROTOCOL=beq)
(PROGRAM=/home/oracle/product/11.1.0/db_1/bin/oracle)
(ARGV0=oracleaux)(ARGS=\(DESCRIPTION=\(LOCAL=YES\)\
(ADDRESS=\(PROTOCOL=beq\)\)\))(ENVS=ORACLE_SID=aux))
(CONNECT_DATA=(SID=aux))) AS SYSDBA"
transport_tablespaces= TEST1, TEST2
dumpfile=dmpfile.dmp directory=STREAMS_DIROBJ_DPDIR logfile=explog.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully
loaded/unloaded******************************************************
Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
/u05/app/oracle/transportdest/dmpfile.dmp
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 02:27:54
RMAN>
3. You now have the export file with the necessary metadata to transport the two table-
spaces, test1 and test2. You also have the datafiles for the two tablespaces you’re going
to transport.
4. Import the tablespace set into the target database. You can perform the import by
using the Data Pump Import utility from the command line, but it’s a whole lot simpler
to use the import script prepared for you by RMAN, with the default name
impscrpt.sql.
SQL> @impscrpt.sql
Directory created.CHAPTER 15 ■ DUPLICATING DATABASES AND TRANSPORTING DATA
Directory created.
PL/SQL procedure successfully completed.
Directory dropped.
Directory dropped.
SQL>
5. Use the DBA_TABLESPACES view to check that the two tablespace test1 and test2 have
been currently imported to the newdb database.
How It Works
In this recipe, we showed you how to transport tablespaces to the same operating system
platform using RMAN backups. The next recipe shows how to do this on different platforms.
You can also transport tablespaces from a “live” database using an alternative transport
tablespace technique explained in the Oracle manual. However, the big disadvantage in that
method is that the transportable tablespaces must be put in a “read-only” mode, thus affect-
ing database availability. It not only may be time-consuming to put the tablespaces into
read-only mode, but users can’t write to those tablespaces for the duration of the tablespace
transport. You don’t have any of these limitations when you use RMAN backups as the basis of
your transportable tablespace operation.
You can create transportable tablespace sets from RMAN backups. You must have a prior
backup of all the datafiles that belong to the transportable tablespace set and the archived
redo logs, so RMAN can use them to recover to the target point in time.
You may use this capability of RMAN for creating transportable tablespace sets for report-
ing purposes. Transportable tablespace sets are also highly useful during the instantiation of
streams. The one big requirement for using RMAN backups to create transportable table-
spaces is that your RMAN backups must be recoverable to the SCN at which you want the
transportable tablespaces.
RMAN creates an auxiliary database instance through which it creates the transportable
tablespace sets. RMAN does quite a few things in order to prepare the transportable table set.
Here’s a summary of the actions set off when you execute the transport tablespace
command:
1. RMAN starts an auxiliary instance in nomount mode first. You don’t have to specify a
parameter file for this auxiliary instance, since RMAN automatically creates the file.
The name of the auxiliary instance is also made up by RMAN. In our example shown
in the previous section, the ORACLE_SID of the auxiliary instance is aux (Creating
automatic instance, with SID='aux').
2. RMAN restores a backup of the target database control file and uses it to mount the
auxiliary database.
3. Using the switch operation, RMAN restores all datafiles from the target database
for the auxiliary instance. These files are restored to the location specified by the
auxiliary destination clause in the transport tablespace command.
4. RMAN also stores the files pertaining to the tablespaces in the transportable table-
space set in the location specified by the tablespace destination parameter in the
transport tablespace command.
475476
■ DUPLICATING DATABASES AND TRANSPORTING DATA
5. Once the datafiles from the target database are all restored to the auxiliary database
location, RMAN performs a point-in-time recovery of the auxiliary instance. In our
example, since we didn’t specify a target time, a complete database recovery is per-
formed. Note that all applicable archived redo logs are also automatically restored and
applied by RMAN during the recovery process. Once the recovery is finished, an open
resetlogs operation is performed on the auxiliary database by RMAN.
6. RMAN invokes the Data Pump Export utility (in the transportable tablespace mode)
to create the export dump file containing the tablespaces in the transportable table-
space set. By default, the export dump file is placed in the location specified by the
tablespace destination clause of the transport tablespace command.
7. RMAN also simultaneously generates a Data Pump Import script you can use to plug
in the transported tablespaces into the target database. The default script name is
impscrpt.sql, and this script is located in the directory specified by the tablespace
destination clause of the transport tablespace command.
8. RMAN shuts down the auxiliary instance and automatically deletes all the files created
and used during the transport tablespace process. The only files that remain are the
transportable set files, the Data Pump Export log, and the sample Data Pump Import
script.
In the example shown in the solution, the RMAN-created impscrpt.sql script was used to
import the tablespaces into the target database. The script utilizes a PL/SQL script to import
the tablespaces.
Alternatively, you can use the following Data Pump import command to import the table-
spaces. For example:
$ impdp sys/sammyy1
directory=exp_data_dir
dumpfile= 'dmpfile.dmp'
transport_datafiles= /u05/app/oracle/transportdest/test1_01.dbf,
/u05/app/oracle/transportdest/test2_01.dbf
■ Note You can even use non-RMAN backups to create transportable tablespace sets, as long as you
record the datafile copies and archived redo logs in the RMAN repository using the catalog command.
You can also use the transport tablespace command to perform a tablespace transport
to a past point in time. Simply add the unitl scn clause to the transport tablespace com-
mand, as shown here:
RMAN>
2>
3>4>
transport tablespace test1,test2
tablespace destination '/u05/app/oracle/transportdest'
auxiliary destination '/u05/app/oracle/auxdest'
until SCN 259386;CHAPTER 15 ■ DUPLICATING DATABASES AND TRANSPORTING DATA
The preceding command will recover the transportable tablespaces only up to the specified
SCN. Instead of the SCN, you can also specify a target point in time or a restore point as well.
Transporting Tablespaces Across Different Operating System Platforms
Problem
You want to transport one or more tablespaces to another Oracle Database running on a dif-
ferent operating system platform using RMAN-made backups.
Solution
You can transport a tablespace to a host running on a different operating system platform
than the source database basically by using the same transport tablespace command, as
shown in recipe 15-11. The crucial thing to do here is to determine the endian formats of the
two platforms—the source and the target. If the two server platforms have the same endian
format, then the transport tablespace operation will be identical to the one described in
recipe 15-11. If the two operating systems’ endian formats differ, say one is big and the other is
little, then you have to convert the tablespaces either before or after moving the tablespace’s
datafiles to the target server platform.
To find out the endian formats of the two platforms, use the following query:
SQL> select platform_name, endian_format from
2* v$transportable_platform;
PLATFORM_NAME
---------------------------
Solaris[tm] OE (32-bit)
Solaris[tm] OE (64-bit)
Microsoft Windows IA (32-bit)
Linux IA (32-bit)
AIX-Based Systems (64-bit)
HP-UX (64-bit)
HP Tru64 UNIX
HP-UX IA (64-bit)
Linux IA (64-bit)
HP Open VMS
Microsoft Windows IA (64-bit)
IBM zSeries Based Linux
Linux 64-bit for AMD
Apple Mac OS
Microsoft Windows
64-bit for AMD
Solaris Operating Sy stem
(x86)
IBM Power Based Linux
17 rows selected.
SQL>
ENDIAN_FORMAT
----------------------------
Big
Big
Little
Little
Big
Big
Little
Big
Little
Little
Little
Big
Little
Big
Little
Little
Big
477478
■ DUPLICATING DATABASES AND TRANSPORTING DATA
If the endian formats of the two platforms (source and target) are different, you must con-
vert the endian format of the datafiles in the transportable tablespace set to match the endian
format of the target operating system platform. You must use the convert tablespace com-
mand if you want to convert on the source host or the convert datafile command if you want
to perform the conversion on the destination host.
In our example, we are transporting a tablespace from the HP-UX (64-bit) operating sys-
tem platform to a Linux (32-bit) platform. Since the HP-UX (64-bit)’s endian format is big and
the Linux platform’s is small, the endian formats aren’t identical, and we must convert the
datafiles belonging to the source platform before we can perform the transport tablespace
operation.
The following are the steps you must take to transport a tablespace across different plat-
forms when the endian formats of the two platforms are different:
1. Place the tablespaces you want to transport in read-only mode:
SQL> alter tablespace myspace read only;
Tablespace altered.
SQL>
2. Use the convert tablespace command to convert the source (HP-UX in this case)
datafiles in the transportable tablespace set to the target (Linux in this case) platform:
RMAN> convert tablespace myspace
2> to platform 'Linux IA (32-bit)'
3> format='/tmp/dba/%U';
Starting backup at 02-JAN-07
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00064 name=/pasu61/oradata/pasu/myspace01.dbf
converted datafile=/tmp/dba/data_D-PASU_I-877170026_TS-MYSPACE_FNO-64_02i6g1vs
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00066 name=/pasu61/oradata/pasu/myspace02.dbf
converted datafile=/tmp/dba/data_D-PASU_I-877170026_TS-MYSPACE_FNO-66_03i6g200
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
Finished backup at 02-JAN-07
RMAN>
3. Verify that you have the two datafiles that are part of the myspace tablespace in the
/tmp/dba directory:
$ ls –altr /tmp/dba
data_D-PASU_I-877170026_TS-MYSPACE_FNO-64_02i6g1vs
data_D-PASU_I-877170026_TS-MYSPACE_FNO-66_03i6g200
Note that the two files listed here are converted to the endian format of the Linux IA
(32-bit) platform.
4. Use the Data Pump Export utility to create the export dump file with the metadata for
the myspace tablespace:
DUPLICATING DATABASES AND TRANSPORTING DATA
$ expdp pasowner/orbiter1 directory=direct1 transport_tablespaces=myspace
Export: Release 11.1.0.1.0 - 64bit Production on Tuesday, 26 June,
2007 10:18
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.1.0 -
64bit Beta
Starting "PASOWNER"."SYS_EXPORT_TRANSPORTABLE_01": pasowner/********
directory=direct1 transport_tablespaces=myspace dumpfile=myspace.dmp
logfile=myspace.log
Processing object type TRANSPORTABLE_EXPORT/TABLE
Master table "PASOWNER"."SYS_EXPORT_TRANSPORTABLE_01" successfully
loaded/unloaded
Dump file set for PASOWNER.SYS_EXPORT_TRANSPORTABLE_01 is:
/u01/app/oracle/dba/myspace.dmp
Job "PASOWNER"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 10:19
$
5. Move both the converted datafiles from step 2 and the export dump file
(myspace.dmp) from step 3 to the target host, as shown here:
$ rcp data_D-PASU_I-877170026_TS-MYSPACE_FNO-64_02i6g1vs prod1:/tmp/dba
$ rcp data_D-PASU_I-877170026_TS-MYSPACE_FNO-66_03i6g200 prod1:/tmp/dba
$ rcp myspace.dmp prod1:/tmp/dba
6. Import the tablespace metadata for the transported tablespaces into the target data-
base, as shown here:
$ impdp system/sammyy1 directory=data_dump_dir2
dumpfile='myspace2.dmp'
transport_datafiles='/u05/app/oracle/data_D-PASU_I-877170026_TS-YSPACE_FNO- ➥
64_02i6g1v,
'/u05/app/oracle/ data_D-PASU_I-877170026_TS-MYSPACE_FNO-66_03i6g200'
Import: Release 11.1.0.1.0 - Beta on Tuesday, 26 June, 2007
5:10:17
Copyright © 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release
11.1.0.1.0 - Beta
With the Partitioning, OLAP and Data Mining options
ORA-31655: no data or metadata objects selected for job
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully ➥
loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/********
directory=data_dump_dir2 dumpfile=myspace2.dmp
transport_datafiles=/u05/app/oracle/data_D-PASU_I-877170026_TS-
MYSPACE_FNO-64_02i6g1v,/u05/app/oracle/ data_D-PASU_I-877170026_TS-
MYSPACE_FNO-66_03i6g200 Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01"
successfully completed at 05:10:29
$
479480
DUPLICATING DATABASES AND TRANSPORTING DATA
Once the import completes successfully as shown here, the tablespace transport process
is complete.
How It Works
Contrary to its name, the convert command doesn’t actually convert the source database
files—it merely prepares duplicate files in the correct format for use on the target platform.
The source files remain untouched by this conversion operation.
In the example shown in this recipe, we converted the source datafiles on the source plat-
form by using the convert tablespace command. However, you can convert the datafiles that
belong to the tablespaces in the transportable tablespace set on the target system as well by
using the convert datafile command, as shown here:
RMAN> connect target /
RMAN> convert datafile=
'/u01/transport_solaris/sales/sales01.dbf',
'/u01/transport_solaris/sales/sales02.dbf'
from platform 'Solaris[tm] OE (32-bit)'
db_file_name_convert
'/u01/transport_solaris/sales','/u05/newdb/sales';
You’ll end up with the following pair of files on the target server after the datafile conversion:
/u05/newdb/sales01.dbf
/u05/newdb/sales02.dbf
You follow the same general procedure for transporting tablespaces. After the conversion,
use the Data Pump Import utility to plug the converted tablespaces into the target database.
15-13. Transporting an Entire Database to a Different Platform
Problem
You want to transport an entire Oracle database to another host that is using a different oper-
ating system platform.
Solution
Use the convert database command to move an Oracle database from one platform to
another. The only requirement is that the two platforms share an identical endian format. You
can perform the convert database operation on the source platform or the destination plat-
form. In our example here, we perform the datafile conversion process on the source database
platform. The next recipe shows you how to perform the datafile conversion on the target
database platform.
The following are the steps to transport an Oracle database from a Windows XP platform
to the Linux platform:
1. Make sure the source database is eligible for transporting to the destination operating
system platform by executing the dbms_tdb.check_db procedure, as shown here. The
source database is running on a Windows XP platform, and the target database is run-
ning on a Linux 32-bit platform.
SQL> connect sys/sammyy1 as sysdba
Connected.
SQL> set serveroutput on
SQL> declare
2> db_ready boolean;
3> begin
4> db_ready := dbms_tdb.check_db('Linux IA(32-bit)',
dbms_tdb.skip_readonly);
5* end;
SQL> /
PL/SQL procedure successfully completed.
SQL>
If you see the message “PL/SQL procedure successfully completed,” as is the case in
this example, it means you can migrate from the specified operating system platform
to the destination platform.
2. Since external tables aren’t automatically transported to the target platform, you must
invoke the dbms_tdb.check_db procedure again to get a list of the external tables,
which you can then use to re-create those tables on the destination platform after the
database transportation.
SQL> declare
2> external boolean;
3> begin
4> external :=dbms_tdb.check_external;
5* end;
SQL> /
PL/SQL procedure successfully completed.
SQL>
Again, the output of the execution of the dbms_tdb package shows you don’t have any
external tables in the parent database that you’ll have to worry about.
3. Put the target database in the read-only mode after shutting it down first and restarting
it in mount state:
SQL> connect sys/sammyy1 as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount;
ORACLE instance started.
Total System Global Area 612368384
Fixed Size
1250452
Variable Size
180357996
Database Buffers
427819008
bytes
bytes
bytes
bytes
481482
Redo Buffers
2940928 bytes
Database mounted.
SQL> alter database open read only;
Database altered.
SQL>
4. Execute the convert database command, which creates a transport script you’ll subse-
quently use in the transportation process:
RMAN> convert database new database 'mydb'
2> transport script 'c:\temp\mydb_script'
3> to platform 'Linux IA (32-bit)'
4> db_file_name_convert 'c:\oracle\product\11.1.0\oradata\nick\'
'c:\temp\';
Starting convert at 15-FEB-07
using channel ORA_DISK_1
External table SH.SALES_TRANSACTIONS_EXT found in the database
Directory SYS.SUBDIR found in the database
Directory SYS.XMLDIR found in the database
Directory SYS.MEDIA_DIR found in the database
Directory SYS.LOG_FILE_DIR found in the database
Directory SYS.DATA_FILE_DIR found in the database
Directory SYS.WORK_DIR found in the database
Directory SYS.ADMIN_DIR found in the database
Directory SYS.DATA_PUMP_DIR found in the database
BFILE PM.PRINT_MEDIA found in the database
User SYS with SYSDBA and SYSOPER privilege found in password file
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00001
name=C:\ORACLE\PRODUCT\11.1.0\ORADATA\NICK\SYSTEM01.DBF
converted datafile=C:\TEMP\SYSTEM01.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time:
00:00:35
...
Edit init.ora file
C:\ORACLE\PRODUCT\11.1.0\DB_1\DATABASE\INIT_00IA4IO1_1_0.ORA.
This PFILE will be used to create the database on the target platform
To recompile all PL/SQL modules, run utlirp.sql and utlrp.sql on the target ➥
platform
To change the internal database identifier, use DBNEWID Utility
Finished backup at 15-FEB-07
RMAN>
Once the source database files are converted, you must move them to the target plat-
form and place them in the location specified for the datafiles in the pfile of the target
database. You must also copy the mydb_script file and the pfile for the new database to
the target platform.CHAPTER 15 ■ DUPLICATING DATABASES AND TRANSPORTING DATA
5. On the target platform, first set the ORACLE_SID environment variable pointing to the
new database:
$ export ORACLE_SID=newdb
The ORACLE_HOME environment variable must point to the same version of Oracle
software as on the source platform.
Execute the mydb_script file after logging in to SQL*Plus as the user sys:
$ sqlplus /nolog
SQL> connect sys/sammyy1 as sysdgba
Connected to an idle instance.
SQL> @mydb
ORACLE instance started.
Total System Global Area 243269632
Fixed Size
1218748
Variable Size
79693636
Database Buffers
159383552
Redo Buffers
2973696
...
Control file created.
Database altered.
Tablespace altered.
bytes
bytes
bytes
bytes
bytes
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 243269632 bytes
Fixed Size
1218748 bytes
Variable Size
79693636 bytes
Database Buffers
159383552 bytes
Redo Buffers
2973696 bytes
Database mounted.
Database opened.
SQL>
SQL> Rem invalidate all pl/sql modules and recompile standard and ➥
dbms_standard
SQL> @/u01/app/oracle/product/11.1.0/db_3/rdbms/admin/utlrp.sql
DOC>
utlirp.sql completed successfully. All PL/SQL objects in the
DOC>
database have been invalidated.
DOC>
Shut down and restart the database in normal mode and run utlrp.sql to
DOC>
recompile invalid objects.
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP
ORACLE instance started.
...
Database mounted.
Database opened.
SQL> -- The following step will recompile all PL/SQL modules.
SQL> -- It may take several hours to complete.
SQL> @/home/oracle/product/11.1.0/db_3/rdbms/admin/utlrp.sql
When the utlrp.sql script completes, your database transport is complete. You now
have a functioning copy of the target database on the new platform.
6. Check to make sure there are no invalidated objects left in the database:
SQL> select count(*) from dba_objects where
status='INVALID';
no rows selected
SQL>
Sometimes, several invalid objects are in the database after the utlrp.sql script finishes
executing. To recompile the leftover invalid objects, you can run the utlrp.sql script multiple
times.
How It Works
It’s important to understand that although you can use the RMAN backups from the source
database as the basis for the target database, you must still create the new instance first. As
part of the database transport, you have to move all the datafiles belonging to the permanent
tablespaces, as well as the initialization parameter file (pfile), from the source platform to the
destination platform.
If you’re using a pfile for your source database, the conversion process will migrate the
pfile to the new database. If you’re using an spfile instead, Oracle will first create a pfile from
this spfile and then use the pfile as the basis for creating a new spfile for the transported data-
base (destination database).
The transportation process won’t transport the following items:
• Redo logs and control files.
• Bfiles.
• Temp files. You must create a new temporary tablespace on the target platform after the
transport is done.
• External tables and directories. You must redefine these on the target platform.
• Password files. Using the information provided in the output of the convert database
command, you must create a new password file on the destination database.
Running utlrp.sql as part of the transportation script (mydb_script in our example)
recompiles all the invalidated objects in the database. Even if you don’t recompile the objects
with this script, it is OK, since Oracle dynamically recompiles any invalidated object when you
try to access it. The utlrp.sql script is invoked really to reduce latencies caused by on-the-fly
recompilation of objects. Once the new database is created, it’ll automatically be registered by
the currently running Oracle listener process.
Transporting a Database by Converting Datafiles on the Target Platform
Problem
You want to perform a database transport but perform the datafile conversion on the target
database platform instead of the source database platform.
Solution
Performing a database transport by performing the file conversion on the target platform is
similar in many ways to the database transport shown in recipe 15-14, where we performed
the datafile conversion on the target platform. Here are the steps:
1. Make sure the source database is eligible for transporting to the destination operating
system platform by executing the dbms_tdb.check_db procedure, as shown here. The
source database is running on a Windows XP platform, and the target database is run-
ning on a Linux 32-bit platform.
SQL> connect sys/sammyy1 as sysdba
Connected.
SQL> set serveroutput on
SQL> declare
2> db_ready boolean;
3> begin
4> db_ready := dbms_tdb.check_db('Linux IA(32-bit)',
5> dbms_tdb.skip_readonly);
6* end;
SQL> /
PL/SQL procedure successfully completed.
SQL>
If you see the message “PL/SQL procedure successfully completed,” as is the case in
this example, it means you can migrate from the specified operating system platform
to the destination platform.
2. Since external tables aren’t automatically transported to the target platform, you must
invoke the dbms_tdb.check_external procedure again to get a list of the external tables
that you can then use to re-create those tables on the destination platform after the
database transportation.
SQL> declare
2> external boolean;
3> begin
4> external :=dbms_tdb.check_external;
5> end;
6> /
PL/SQL procedure successfully completed.
SQL>
Again, the output of the execution of the dbms_tdb package shows that you don’t have
any external tables in the parent database you’ll have to worry about.
3. Put the target database in read-only mode after shutting it down first and restarting it
in the mount state:
SQL> connect sys/sammyy1 as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount;
ORACLE instance started.
Total System Global Area 612368384
Fixed Size
1250452
Variable Size
180357996
Database Buffers
427819008
Redo Buffers
2940928
Database mounted.
SQL> alter database open read only;
Database altered.
SQL>
bytes
bytes
bytes
bytes
bytes
4. Execute the convert database command on the source database platform with the on
target platform clause, as shown here:
RMAN>
2>
3>
4>
5>
6>
convert database on target platform
convert script 'c:\temp\convert\convertscript.rman'
transport script 'c:\temp\convert\transportscript.sql'
new database 'youdb'
format 'c:\temp\convert\%U'
;
Starting convert at 21-FEB-07
using channel ORA_DISK_1
External table SH.SALES_TRANSACTIONS_EXT found in the database
Directory SYS.SUBDIR found in the database
Directory SYS.XMLDIR found in the database
Directory SYS.MEDIA_DIR found in the database
Directory SYS.LOG_FILE_DIR found in the database
Directory SYS.DATA_FILE_DIR found in the database
Directory SYS.WORK_DIR found in the database
Directory SYS.ADMIN_DIR found in the database
Directory SYS.DATA_PUMP_DIR found in the database
BFILE PM.PRINT_MEDIA found in the database
User SYS with SYSDBA and SYSOPER privilege found in password file
channel ORA_DISK_1: starting to check datafiles input datafile fno=00001
name=C:\ORACLE\PRODUCT\11.1.0\ORADATA\NICK\SYSTEM01.DBF
channel ORA_DISK_1: datafile checking complete, elapsed time: 00:00:00
...
Run SQL script C:\TEMP\CONVERT\TRANSPORTSCRIPT.SQL on the target
platform to create database
Edit init.ora file C:\TEMP\CONVERT\INIT_00IAKAV1_1_0.ORA. This PFILE
will be used to create the database on the target platform
Run RMAN script C:\TEMP\CONVERT\CONVERTSCRIPT.RMAN 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 21-FEB-07
RMAN>
The convert database command shown here creates three files to help in the database
transport:
• A pfile to create the new database on the target platform, with identical parameters
as the source database.
• A SQL script named transportscript.sql with SQL*Plus commands to create the
new database. This script will first create the new instance using the pfile created
here.
• A datafile conversion script named convertscript.rman to help convert the source
datafiles on the target platform so they can be used by the new database you’re
going to create on that platform.
It’s important to understand that although you use a convert database command here,
no actual conversion of the datafiles takes place. That step will come after you trans-
port the original datafiles to the target platform.
5. Copy the datafiles of the source database while that database is still in the read-only
mode and move them to the target database platform.
6. Run the convertscript.rman script on the target database after first transporting all the
source database files to the target platform. Once you copy the necessary files, you can
put the source database in a read-write mode again.
7. You use a separate convert datafile command for each datafile you need to convert
from the source to the target platform. The format parameter of the convert datafile
command specifies the location of each file on the target platform.
$ rman target / cmdfile=CONVERTSCRIPT.RMAN
Recovery Manager: Release 11.1.0.1.0 - Beta on Thu Feb 22 11:13:09 2007
Copyright © 1982, 2005, Oracle. All rights reserved.
connected to target database: MYDB (DBID=2561840016)
RMAN> run
2>
{
3>
convert datafile '/u01/app/oracle/youdb/temp/SYSTEM01.DBF'
4>
from platform 'Microsoft Windows IA (32-bit)'
5>
format '/u01/app/oracle/youdb/SYSTEM01.DBF';
6>
7>
convert datafile '/u01/app/oracle/youdb/temp/SYSAUX01.DBF'
8>
from platform 'Microsoft Windows IA (32-bit)'
9>
format '/u01/app/oracle/youdb/SYSAUX01.DBF';
10>
11>
convert datafile '/u01/app/oracle/youdb/temp/EXAMPLE01.DBF'
12>
from platform 'Microsoft Windows IA (32-bit)'
13>
format '/u01/app/oracle/youdb/EXAMPLE01.DBF';
14>
15>
convert datafile '/u01/app/oracle/youdb/temp/UNDOTBS01.DBF'
16>
from platform 'Microsoft Windows IA (32-bit)'
17>
format '/u01/app/oracle/youdb/UNDOTBS01.DBF';
18>
19>
convert datafile '/u01/app/oracle/youdb/temp/USERS01.DBF'
20>
from platform 'Microsoft Windows IA (32-bit)'
21>
format '/u01/app/oracle/youdb/USERS01.DBF';
22>
}
Starting backup at 22-FEB-07
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=150 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input filename=/u01/app/oracle/youdb/temp/SYSTEM01.DBF
converted datafile=/u01/app/oracle/youdb/SYSTEM01.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:36
Finished backup at 22-FEB-07
Starting backup at 22-FEB-07
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=/u01/app/oracle/youdb/temp/SYSAUX01.DBFCHAPTER 15 ■ DUPLICATING DATABASES AND TRANSPORTING DATA
converted datafile=/u01/app/oracle/youdb/SYSAUX01.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed
Finished backup at 22-FEB-07
Starting backup at 22-FEB-07
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=/u01/app/oracle/youdb/temp/EXAMPLE01.DBF
converted datafile=/u01/app/oracle/youdb/EXAMPLE01.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed
Finished backup at 22-FEB-07
Starting backup at 22-FEB-07
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=/u01/app/oracle/youdb/temp/UNDOTBS01.DBF
converted datafile=/u01/app/oracle/youdb/UNDOTBS01.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed
Finished backup at 22-FEB-07
Starting backup at 22-FEB-07
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=/u01/app/oracle/youdb/temp/USERS01.DBF
converted datafile=/u01/app/oracle/youdb/USERS01.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed
Finished backup at 22-FEB-07
RMAN>
time: 00:00:26
time: 00:00:16
time: 00:00:04
time: 00:00:02
When the convertscript.rman script finishes executing, all your source database files
are converted into the target database format and are ready for use by the new
instance you’re going to create in the next step.
8. Make the necessary changes in the pfile and the transportscript.sql script produced
by the convert database script. Execute the transportscript.sql script from SQL*Plus
to produce a new database instance, and create the new database (named youdb in
the example):
$ sqlplus /nolog
SQL> @transportscript
The transportscript.sql script invokes the pfile to start a new instance and create the
control files to open the new database youdb. The script also invokes the utlrp.sql script to
recompile all the invalidated objects. When the script finishes executing, you’ll have your new
database on the target platform.
How It Works
If you are copying a database from the source platform to several different operating system
platforms, you must convert the source database datafiles on each of the target platforms. If
you’re copying a production database to multiple test platforms, converting the datafiles of
the parent database on each of the target platforms also eliminates the performance overhead
on the production system.
Once you finish executing the convert script, the database is ready for use, and the Oracle
listener process will automatically register your new instances and will accept connection
requests. You must transport the unconverted, original source data files to the target platform.
These datafiles are still in the format of the original platform and hence aren’t suitable for use
in the target platform unless they are converted to the target operating system format. The
convert script (convertscript.rman) contains the actual convert datafile commands for con-
verting each of the datafiles you transport to the target platform.
