For database migration we come up different migration requirements based on which we need to opt for migration technologies .
We will overview on different approaches available and do a deep drive of transportable tablespace .
Migration Scenarios :
1) 32bit to 64 bit Operating system
2) Cross platform --> different Indian format
3) Cloud migration
4) Storage migration
5) Migrate with upgrade to high version of database .
Methods for migration :
1) Golden gate
2) export / Import
3) Dataguard
4) Heterogeneous Dataguard
5) Transportable Tablespace and XTTS
6) Rman restore ( restoration and Duplicate , )
Consideration for choosing migration method :
1) Downtime
2) Product license cost
3) Size of database
Transportable Tablespace
Transportable Tablespace method can be used for Oracle database Migration and Upgrade when the operating systems of the source database and the target database are different and conversion between 32 bits and 64 bits.
When using Cross Platform Transportable Tablespaces (XTTS) to migrate database between systems that have different endian formats,
the amount of downtime required is related directly proportional to the size of the data set being moved. To reduce amount of downtime, Oracle recommend Cross Platform Incremental Backup with Oracle 12c.
We personally used Backup as copy 1 day before migration date and use incremental backup for update of copy on date of upgrade to reduce downtime .
What is Endian?
Endian is the storage method of multi-byte data types in memory. In other words, it determines the byte order of the data. There are two kinds of endian, Little and Big.
Little Endian
The data is stored little end first. That is, the firs byte is the biggest.
Big Endian
The data is stored big end first. That is, the first byte is the smallest.
For example ;
Assume that an integer is stored as 4 bytes (32 bits), then a variable with a value of 0x01234567 (Hexadecimal decimal representation) will be stored in the form of 0x01, 0x23, 0x45, 0x67. In systems with big endian, this data is stored in this order while in small endian systems it is stored in reverse orde
In Oracle databases, endian format is determined by the endian information in the environment in which it works. The endian format in the databases tells us which environments the related database can be moved to. It is not possible to move the database with normal methods between different endian environments. For example, you cannot transfer a database with Data Guard to a system with Big Endian from a Little Endian system.
Transportable tablespaces progressed in different Version :
We can use the transportable tablespaces feature to copy/move subset of data (set of user tablespaces), from an Oracle database and plug it in to another Oracle database. The tablespaces being transported can be either dictionary managed or locally managed.
With Oracle 8i, Oracle introduced transportable tablespace (TTS) technology that moves tablespaces between databases. Oracle 8i supports tablespace transportation between databases that run on same OS platforms and use the same database block size.
With Oracle 9i, TTS (Transportable Tablespaces) technology was enhanced to support tablespace transportation between databases on platforms of the same type, but using different block sizes.
With Oracle 10g, TTS (Transportable Tablespaces) technology was further enhanced to support transportation of tablespaces between databases running on different OS platforms (e.g. Windows to Linux, Solaris to HP-UX), which has same ENDIAN formats. Oracle Database 10g Release 1 introduced cross platform transportable tablespaces (XTTS), which allows data files to be moved between platforms of different endian format. XTTS is an enhancement to the transportable tablespace (TTS). If ENDIAN formats are different we have to use RMAN (e.g. Windows to Solaris, Tru64 to AIX).
From 10g Release2 we can transport whole database, this is called Transportable Database (TDB).
From Oracle 11g, we can transport single partition of a tablespace between databases.
Below is using traditional method of transportable taablespace however we can also use oracle provided rman_xttconvert package as per 2471245.1 that uses incremental backup concept to reduce downtime . I see below good writeup on rman_xttconvert method using incremental backup
https://dohdatabase.com/2020/12/09/how-to-migrate-a-database-using-full-transportable-export-import-and-incremental-backups/
Limitations/Restrictions
1) The source and target database must use the same character set and national character set.
2) System, undo, sysaux and temporary tablespaces cannot be transported.
3) If Automatic Storage Management (ASM) is used with either the source or destination database, you must use RMAN to transport/convert the tablespace.
4) Transportable tablespaces do not support: Materialized views/replication Function-based indexes.
5) Binary_Float and Binary_Double datatypes (new in Oracle 10g) are not supported
Key Note :
1) Source and Target Character Set must match
2) Source and Target Time Zone must match
3) Compatible parameter on target must be same or higer then source
4) Tablespace endianness can be converted with either Rman convert or Dbms_file_transfer
5) Tablespace may be encrypted with Transparent Data Encryption
Below are steps used for manual transportable tablespace : we would suggest to use rman specially for big database where need to take incremental backup on last day
1) Query the V$TRANSPORTABLE_PLATFORM view to see the platforms that are supported and to determine each platform's endian format (byte ordering).
SQL> COLUMN PLATFORM_NAME FORMAT A32
SQL> SELECT * FROM V$TRANSPORTABLE_PLATFORM;
SELECT tp.platform_id,substr(d.PLATFORM_NAME,1,30), ENDIAN_FORMAT
FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;
SQL> set lines 200
SQL> set pages 200
SQL> COL "Source" FORM a32
SQL> COL "Compatible Targets" FORM a40
SQL> select d.platform_name "Source", t.platform_name "Compatible Targets", endian_format
from v$transportable_platform t, v$database d where t.endian_format = (select endian_format from v$transportable_platform t, v$database d where d.platform_name = t.platform_name)
order by "Compatible Targets";
2) Prepare for export of the tablespace.
Check that the tablespace will be self contained:
SQL> execute sys.dbms_tts.transport_set_check('TBS1,TBS2', true);
SQL> select * from sys.transport_set_violations;
Sql> select * from transport_set_violations;
Violations
---------------------------------------------------------------------------
Constraint dept_fk between table jim.emp in tablespace sales_1 and table
Jim.dept in tablespace other
Partitioned table jim.sales is partially contained in the transportable set
These violations must be resolved before sales_1 and sales_2 are transportable. As noted in the next step, one choice for bypassing the integrity constraint violation is to not export
the integrity constraints
3) The tablespaces need to be in READ ONLY mode in order to successfully run a transport tablespace export:
SQL> ALTER TABLESPACE TBS1 READ ONLY;
SQL> ALTER TABLESPACE TBS2 READ ONLY;
4) Export the metadata.
you must have been assigned the exp_full_database role to perform a transportable tablespace export operation. If any of the tablespaces have xmltypes, you must use exp instead of data pump
expdp system/password DUMPFILE=expdat.dmp DIRECTORY = dpump_dir TRANSPORT_TABLESPACES= TBS1,TBS2 TRANSPORT_FULL_CHECK=Y
if you want to perform a transport tablespace operation with a strict containment check, use the transport_full_check parameter, as shown in the following example:
If we want to use full=y transportable=always we can use below . Please note if source database is of lower version we need to use version= to match target version .
$ expdp system/manager full=y transportable=always version=12 \
directory=dp_dir dumpfile=full_tts.dmp \
metrics=y exclude=statistics \
encryption_password=secret123word456 \
logfile=full_tts_export.log
5) Copy datafile to target
If you see that the endian formats are different and then a conversion is necessary for transporting the tablespace set:
RMAN> convert tablespace TBS1 to platform="Linux IA (32-bit)" FORMAT '/tmp/%U';
RMAN> convert tablespace TBS2 to platform="Linux IA (32-bit)" FORMAT '/tmp/%U';
6)
Transport both the datafiles and the export file of the tablespaces to a place that is accessible to the target database either using ftp or The dbms_file_transfer package
or asmcmd cp .
7) Restore file on target .
In releases lower than 11.2.0.4 you need to follow the same steps specified above for ASM files. But if the endian formats are different then you must use the RMAN convert AFTER transferring the files. The files cannot be copied directly between two ASM instances at different platforms.
This is an example of usage change of endian format is needed on target if not done in source :
RMAN> CONVERT DATAFILE
'/path/tbs_31.f',
'/path/tbs_32.f',
'/path/tbs_41.f'
TO PLATFORM="Solaris[tm] OE (32-bit)"
FROM PLATFORM="HP TRu64 UNIX"
DB_FILE_NAME_CONVERT= "/path_source/", "/path_dest/"
PARALLELISM=5;
The same example, but here showing the destination being an +ASM diskgroup:
RMAN> CONVERT DATAFILE
'/path/tbs_31.f',
'/path/tbs_32.f',
'/path/tbs_41.f'
TO PLATFORM="Solaris[tm] OE (32-bit)"
FROM PLATFORM="HP TRu64 UNIX"
DB_FILE_NAME_CONVERT="/path_source/", "+diskgroup"
PARALLELISM=5;
8) Import Metadata on target
impdp system/password DUMPFILE=expdat.dmp DIRECTORY=dpump_dir TRANSPORT_DATAFILES='/tmp/....','/tmp/...' REMAP_SCHEMA=(source:target) REMAP_SCHEMA=(source_sch2:target_schema_sch2)
or
$ impdp system@PDB2 FULL=y DIRECTORY=dp_from_source \
TRANSPORT_DATAFILES='/u02/app/oracle/oradata/ORCL/PDB2/example01.dbf', \
'/u02/app/oracle/oradata/ORCL/PDB2/fsdata01.dbf',\
'/u02/app/oracle/oradata/ORCL/PDB2/fsindex01.dbf,'\
'/u02/app/oracle/oradata/ORCL/PDB2/users01.dbf'
There’s no expdp necessary when using Data Pump over a database link (NETWORK_LINK). But in this case you will need the keywords FULL=Y and TRANSPORTABLE=ALWAYS as export parameters as the export portion of Data Pump on the source side will be triggered underneath the covers.
impdp mike/passwd@v121
NETWORK_LINK=v112
FULL=Y
TRANSPORTABLE=ALWAYS
VERSION=12
METRICS=Y
exclude=table_statistics,index_statistics
LOGTIME=ALL
LOGFILE=ftex_dir:v112fullimp.log
TRANSPORT_DATAFILES='/oracle/DQ1/sapdata50/ts1.dbf'
TRANSPORT_DATAFILES='/oracle/DQ1/sapdata50/ts2.dbf'
9) Put the tablespaces into read/write mode in source and target
Views :
SQL> select platform_name, endian_format from v$transportable_platform;
References :
1) How to Move a Database Using Transportable Tablespaces (Doc ID 1493809.1)
2) How to Migrate to different Endian Platform Using Transportable Tablespaces With RMAN (Doc ID 371556.1)
3) Reduce Transportable Tablespace Downtime using XTTS (Doc ID 1389592.1)
4) V4 Reduce Transportable Tablespace Downtime using Cross Platform Incremental Backup (Doc ID 2471245.1)
11G - Reduce Transportable Tablespace Downtime using Cross Platform Incremental Backup (Note 1389592.1)
12C - Reduce Transportable Tablespace Downtime using Cross Platform Incremental Backup (Note 2005729.1)
12c How Perform Cross-Platform Database Transport to different Endian Platform with RMAN
Backup Sets (Doc ID 2013271.1)
XTTS v4
Doc ID 2471245.1
XTTS v3
Doc ID 1389592.1
XTTS v2
Doc ID 1389592.1
XTTS v1
Doc ID 1389592.1
M5
Doc ID 2999157.1
M5 is the next-generation cross-platform transportable tablespace procedure
New RMAN functionality combined with
Full Transportable Export/Import
• Doc ID 2999157.
No comments:
Post a Comment