Oracle Database 12c: Cross platform database transport

RMAN cross platform transport can transport specific tablespaces or the entire database.

Cross platform tablespace transport
RMAN CONVERT DATAFILE is used to transport tablespaces to the destinaton platform.
The CONVERT DATAFILE command is required to achieve transformation of the datafile to the destination endian format. After the CONVERT, the Oracle transportable tablespace feature used to plug in the tablespaces at the destination.

Cross platform database transport
RMAN converts the entire database – but the destination endian format should be the same as source. For cases where destination endian format differs, tablespace transport is required.

Topics:
Using RMAN to back up the entire database to a specific platform
Using RMAN to restore the database at the target platform

Environment:
Oracle Database 12c Enterprise Edition, little endian
Source: Oracle Enterprise Linux 64bit
Destination: Windows 7 64bit

Using new BACKUP syntax, a cross-platform backup can be created for a specific target platform ( BACKUP TO PLATFORM) at the source itself, or, alternatively, the conversion can be done at the destination (BACKUP FOR TRANSPORT).

Scenario:
A CDB named CDB2 running on Linux. It has one PDB, called HRPDB.

SQL> select pdb_name from cdb_pdbs;

    PDB_NAME
    -----------
    PDB$SEED
    HRPDB

The above CDB will be transported to the Windows platform using RMAN.

Steps:

Perform pre-transport checks to ensure the database can be transported.
Backup the database at the source (platform conversion occurs here for this example)
Copy the generated backup sets to the destination using ftp
Configure a pfile and set up the windows service at the destination using oradim.
Start up the instance using the pfile and restore the backups at the destination
Script a controlfile, create it and mount the database.
Open the database with resetlogs.
Optionally , back up the new database.

Step 1 - Pre-transport checks

The database should be checked with the dbms_tdb.check_db procedure call first.
The database should be opened in read-only mode first.
This procedure accepts a destination platform name as its argument and returns a boolean to indicate whether or not the database can be transported to the target platform. For example the database can be checked with a pl/sql block as shown below:

SQL> ed

Wrote file afiedt.buf

1 DECLARE

2 db_ready BOOLEAN;

3 BEGIN

4 db_ready :=

5 DBMS_TDB.CHECK_DB('Microsoft Windows x86 64-bit');

6 if (db_ready) then

7 dbms_output.put_line('db is ready');

8 else

9 dbms_output.put_line('db is not ready');

10 end if;

11* END;

SQL> /

db is ready

Step 2 - Back up the database using BACKUP TO PLATFORM...

The RMAN BACKUP TO PLATFORM <platform> DATABASE command creates a platform-specific database backup.

RMAN> backup to platform 'Microsoft Windows x86 64-bit' database
2>      format '/u01/app/oracle/oradata/tmp/%U';
The target platform name specified in the BACKUP command above can be retrieved from
v$trasportable_platform using a query such as :

1 select platform_id, platform_name from v$transportable_platform
2* where platform_name like '%Windows%'
SQL> /

PLATFORM_ID PLATFORM_NAME
----------- --------------------------------------------------
          7 Microsoft Windows IA (32-bit)
          8 Microsoft Windows IA (64-bit)
         12 Microsoft Windows x86 64-bit
Note:
Cross-platform backups such as this one are not cataloged.
Cross-platform controlfile backups are not supported.
The platform conversion is being done at source database itself.

For example, in the rman session below, a CDB is being backed up to Windows platform:

RMAN created 3 backupsets, one per container, output files are shown in bold.

RMAN> backup to platform
2>      'Microsoft Windows x86 64-bit'
3>     database format '/u01/app/oracle/oradata/%U';

Starting backup at 25-JAN-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=11 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/u01/app/oracle/oradata/cdb2/undo.ora
input datafile file number=00001 name=/u01/app/oracle/oradata/cdb2/system.ora
input datafile file number=00003 name=/u01/app/oracle/oradata/cdb2/sysaux.ora
input datafile file number=00006 name=/u01/app/oracle/oradata/cdb2/users.ora
channel ORA_DISK_1: starting piece 1 at 25-JAN-14
channel ORA_DISK_1: finished piece 1 at 25-JAN-14

piece handle=/u01/app/oracle/oradata/01ouvobi_1_1 tag=TAG20140125T192642 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:06
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00017 name=/u01/app/oracle/oradata/cdb2/hrpdb/system.ora
input datafile file number=00018 name=/u01/app/oracle/oradata/cdb2/hrpdb/sysaux.ora
input datafile file number=00019 name=/u01/app/oracle/oradata/cdb2/hrpdb/users.ora
input datafile file number=00020 name=/u01/app/oracle/oradata/cdb2/hrpdb/hr.ora
channel ORA_DISK_1: starting piece 1 at 25-JAN-14
channel ORA_DISK_1: finished piece 1 at 25-JAN-14

piece handle=/u01/app/oracle/oradata/02ouvodk_1_1 tag=TAG20140125T192642 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/u01/app/oracle/oradata/cdb2/seed/system.ora
input datafile file number=00004 name=/u01/app/oracle/oradata/cdb2/seed/sysaux.ora
input datafile file number=00007 name=/u01/app/oracle/oradata/cdb2/seed/users.ora
channel ORA_DISK_1: starting piece 1 at 25-JAN-14
channel ORA_DISK_1: finished piece 1 at 25-JAN-14

piece handle=/u01/app/oracle/oradata/03ouvoed_1_1 tag=TAG20140125T192642 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
Finished backup at 25-JAN-14

RMAN>

RMAN created 3 backupsets, one per container.

Step 3 - Copy the backupsets to the destination

The next step is to copy the four backupsets to the Windows server using ftp. For this example, the backupsets were copied to the c:\temp folder. Select a location for the files to be restored. It will be c:\temp\cdb2 for this example.

Step 4 - Configure pfile and a Windows database service for the new db instance.

Create new service on windows using this command:
oradim -new -sid CDB2
Create a password file for the new instance using orapwd
orapwd password=oracle file=PWDCDB2.ORA
Create a temp parameter file init.ora containing these lines.

   DB_NAME=CDB2
   ENABLE_PLUGGABLE_DATABASE=TRUE
   DB_CREATE_FILE_DEST='C:\temp\CDB2'
   CONTROL_FILES='c:\temp\CDB2\control1.ctl','c:\temp\CDB2\control2.ctl'

Ensure that the Oracle listener, and the new oradim-created Windows service are up.

Step 5 - Start up the instance and use RMAN to restore at the destination

Start up rman, connect to the new instance as target and restore the backupsets as shown. RMAN output is edited for clarity.

RMAN> startup nomount pfile='c:\temp\init.ora';
Oracle instance started
Total System Global Area     275615744 bytes
Fixed Size                     2401624 bytes
Variable Size                218106536 bytes
Database Buffers              50331648 bytes
Redo Buffers                   4775936 bytes
RMAN> restore all foreign datafiles to new
2>      from backupset 'c:\temp\03ouvoed_1_1';

Starting restore at 26-JAN-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=429 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring all foreign files in backup piece
channel ORA_DISK_1: reading from backup piece c:\temp\03ouvoed_1_1
channel ORA_DISK_1: restoring foreign file 2 to C:\TEMP\CDB2\CDB2\DATAFILE\O1_MF_SYSTEM_9G8M4RSP_.DBF
channel ORA_DISK_1: restoring foreign file 4 to C:\TEMP\CDB2\CDB2\DATAFILE\O1_MF_SYSAUX_9G8M4RT0_.DBF
channel ORA_DISK_1: restoring foreign file 7 to C:\TEMP\CDB2\CDB2\DATAFILE\O1_MF_USERS_9G8M4RTB_.DBFchannel ORA_DISK_1: foreign piece handle=c:\temp\03ouvoed_1_1
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:26
Finished restore at 26-JAN-14

Repeat the step for the other two backupsets as shown.

RMAN> restore all foreign datafiles to new
2>      from backupset 'c:\temp\02ouvodk_1_1';

...
channel ORA_DISK_1: restoring foreign file 17 to C:\TEMP\CDB2\CDB2\DATAFILE\O1_MF_SYSTEM_9G8M91VY_.DBF
channel ORA_DISK_1: restoring foreign file 18 to C:\TEMP\CDB2\CDB2\DATAFILE\O1_MF_SYSAUX_9G8M9203_.DBF
channel ORA_DISK_1: restoring foreign file 19 to C:\TEMP\CDB2\CDB2\DATAFILE\O1_MF_USERS_9G8M920M_.DBF
channel ORA_DISK_1: restoring foreign file 20 to C:\TEMP\CDB2\CDB2\DATAFILE\O1_MF_SALES_9G8M920M_.DBF

RMAN> restore all foreign datafiles to new
2>      from backupset 'c:\temp\01ouvobi_1_1';
...
channel ORA_DISK_1: reading from backup piece c:\temp\01ouvobi_1_1
channel ORA_DISK_1: restoring foreign file 5 to C:\TEMP\CDB2\CDB2\DATAFILE\O1_MF_UNDOTBS_9G8MD5NK_.DBF
channel ORA_DISK_1: restoring foreign file 1 to C:\TEMP\CDB2\CDB2\DATAFILE\O1_MF_SYSTEM_9G8MD5RG_.DBF
channel ORA_DISK_1: restoring foreign file 3 to C:\TEMP\CDB2\CDB2\DATAFILE\O1_MF_SYSAUX_9G8MD5RG_.DBF
channel ORA_DISK_1: restoring foreign file 6 to C:\TEMP\CDB2\CDB2\DATAFILE\O1_MF_USERS_9G8MD5RY_.DBF

Step 6 - Script a CREATE CONTROLFILE command, create it at the destination.

The next step is to create the controlfile.
Using the RMAN restore output, create a script to do a CREATE CONTROLFILE. The controlfile datafile section should list all the restored datafiles. The CREATE CONTROLFILE statement should be scripted with care.

RESETLOGS will be needed as the online log files are not transported. The database will be mounted automatically after the statement runs.

For example:
RMAN> CREATE CONTROLFILE DATABASE "CDB2" RESETLOGS NOARCHIVELOG
2> maxlogfiles 2
3> maxdatafiles 50
4> LOGFILE
5> GROUP 1 'c:\app\oracle\oradata\cdb2\redo1.ora' SIZE 50M,
6> GROUP 2 'c:\app\oracle\oradata\cdb2\redo2.ora' SIZE 50M
7> DATAFILE
8> 'C:\temp\cdb2\CDB2\DATAFILE\O1_MF_SALES_9G8Q8DVX_.DBF',
9> 'C:\temp\cdb2\CDB2\DATAFILE\O1_MF_SYSAUX_9G8Q3GF3_.DBF',
10> 'C:\temp\cdb2\CDB2\DATAFILE\O1_MF_SYSAUX_9G8Q8DSS_.DBF',
11> 'C:\temp\cdb2\CDB2\DATAFILE\O1_MF_SYSAUX_9G8QCBTQ_.DBF',
12> 'C:\temp\cdb2\CDB2\DATAFILE\O1_MF_SYSTEM_9G8Q3GBF_.DBF',
13> 'C:\temp\cdb2\CDB2\DATAFILE\O1_MF_SYSTEM_9G8Q8DQF_.DBF',
14> 'C:\temp\cdb2\CDB2\DATAFILE\O1_MF_SYSTEM_9G8QCBTG_.DBF',
15> 'C:\temp\cdb2\CDB2\DATAFILE\O1_MF_UNDOTBS_9G8Q3G92_.DBF',
16> 'C:\temp\cdb2\CDB2\DATAFILE\O1_MF_USERS_9G8Q3GFD_.DBF',
17> 'C:\temp\cdb2\CDB2\DATAFILE\O1_MF_USERS_9G8Q8DVO_.DBF',
18> 'C:\temp\cdb2\CDB2\DATAFILE\O1_MF_USERS_9G8QCC3L_.DBF'
19> CHARACTER SET AL32UTF8
20> ;
Statement processed
released channel: ORA_DISK_1

Step 7 - Open the new destination database with resetlogs

Open the database with RESETLOGS and examine file structure from SqlPlus

RMAN> alter database open resetlogs;

Statement processed

SQL> select b.pdb_name, substr(a.name,1,60) name from v$datafile a
2      join cdb_pdbs b on (a.con_id=b.con_id) order by b.pdb_name;

PDB_NAME   NAME
---------- -------------------------------------------------------------------
HRPDB      C:\TEMP\CDB2\CDB2\DATAFILE\O1_MF_SYSAUX_9G8Q3GF3_.DBF
HRPDB      C:\TEMP\CDB2\CDB2\DATAFILE\O1_MF_UNDOTBS_9G8Q3G92_.DBF
HRPDB      C:\TEMP\CDB2\CDB2\DATAFILE\O1_MF_USERS_9G8Q3GFD_.DBF
HRPDB      C:\TEMP\CDB2\CDB2\DATAFILE\O1_MF_SYSTEM_9G8Q3GBF_.DBF
PDB$SEED   C:\TEMP\CDB2\CDB2\DATAFILE\O1_MF_SYSAUX_9G8Q3GF3_.DBF
PDB$SEED   C:\TEMP\CDB2\CDB2\DATAFILE\O1_MF_UNDOTBS_9G8Q3G92_.DBF
PDB$SEED   C:\TEMP\CDB2\CDB2\DATAFILE\O1_MF_USERS_9G8Q3GFD_.DBF
PDB$SEED   C:\TEMP\CDB2\CDB2\DATAFILE\O1_MF_SYSTEM_9G8Q3GBF_.DBF

8 rows selected.

Oracle Database 12c

Sunday 26 January 2014

Cross platform database transport