Cross platform migration using RMAN Transportable tablespace (on linux AS4)

OTA to CSR Database

Connect to ota database

[oracle@virt1 ~]$ su - ota

Password:

[ota@virt1 ~]$ sqlplus " / as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Feb 7 12:48:15 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount;

ORACLE instance started.

Total System Global Area  457179136 bytes

Fixed Size                  1219976 bytes

Variable Size             109052536 bytes

Database Buffers          343932928 bytes

Redo Buffers                2973696 bytes

Database mounted.

SQL> alter database open read only;

Database altered.

SQL> select platform_id,platform_name from v$database;

PLATFORM_ID PLATFORM_NAME

----------- --------------------------------------------------

         10 Linux IA (32-bit)

SQL> select PLATFORM_NAME, ENDIAN_FORMAT from V$TRANSPORTABLE_PLATFORM;

PLATFORM_NAME                                      ENDIAN_FORMAT

-------------------------------------------------- --------------

Solaris[tm] OE (32-bit)                            Big

Solaris[tm] OE (64-bit)                            Big

Microsoft Windows IA (32-bit)                      Little

Linux IA (32-bit)                                  Little

AIX-Based Systems (64-bit)                         Big

HP-UX (64-bit)                                     Big

HP Tru64 UNIX                                      Little

HP-UX IA (64-bit)                                  Big

Linux IA (64-bit)                                  Little

HP Open VMS                                        Little

Microsoft Windows IA (64-bit)                      Little

IBM zSeries Based Linux                            Big

Linux 64-bit for AMD                               Little

Apple Mac OS                                       Big

Microsoft Windows 64-bit for AMD                   Little

Solaris Operating System (x86)                     Little

IBM Power Based Linux                              Big

17 rows selected.

Check the ENDIAN_FORMAT on target server for migration compatibility, if compatible then

SQL> set serveroutput on

SQL> declare

  2  db_ready boolean;

  3  begin

  4  db_ready := dbms_tdb.check_db('Linux IA (32-bit)');

  5  end;

  6  /

PL/SQL procedure successfully completed.

SQL> set serveroutput on

SQL> declare

  2  external boolean;

  3  begin

  4  external := dbms_tdb.check_external;

  5  end;

  6  /

The following directories exist in the database:

SYS.ADMIN_DIR, SYS.WORK_DIR, SYS.DATA_PUMP_DIR, SYS.DP_DIR

PL/SQL procedure successfully completed.

Issue the Convert Database Script

[ota@virt1 ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Feb 7 13:33:03 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: OTA (DBID=1634082786)

RMAN> convert database on target platform

2> convert script '/u01/csr/migration/convertscript.rman'

3> transport script '/u01/csr/migration/transportscript.sql'

4> new database 'csr'

5> format '/u01/csr/migration/%U';

Starting convert at 07-FEB-12

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=153 devtype=DISK

Directory SYS.ADMIN_DIR found in the database

Directory SYS.WORK_DIR found in the database

Directory SYS.DATA_PUMP_DIR found in the database

Directory SYS.DP_DIR 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=00004 name=/u01/ota/oradata/users01.dbf

channel ORA_DISK_1: datafile checking complete, elapsed time: 00:00:00

channel ORA_DISK_1: starting to check datafiles

input datafile fno=00005 name=/u01/ota/oradata/index01.dbf

channel ORA_DISK_1: datafile checking complete, elapsed time: 00:00:00

channel ORA_DISK_1: starting to check datafiles

input datafile fno=00001 name=/u01/ota/oradata/system01.dbf

channel ORA_DISK_1: datafile checking complete, elapsed time: 00:00:00

channel ORA_DISK_1: starting to check datafiles

input datafile fno=00002 name=/u01/ota/oradata/undotbs01.dbf

channel ORA_DISK_1: datafile checking complete, elapsed time: 00:00:00

channel ORA_DISK_1: starting to check datafiles

input datafile fno=00003 name=/u01/ota/oradata/sysaux01.dbf

channel ORA_DISK_1: datafile checking complete, elapsed time: 00:00:00

Run SQL script /u01/csr/migration/transportscript.sql on the target platform to create database

Edit init.ora file /u01/csr/migration/init_00n2nlqf_1_0.ora. This PFILE will be used to create the database on the target platform

Run RMAN script /u01/csr/migration/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 07-FEB-12

Now copy all datafiles, convertscript.rman, transportscript.sql & parameter file gererated by convert script to the target machine.

Move to pfile copied from source machine to $ORACLE_HOME/dbs location and edit the environment specific parameters

[oracle@virt1 migration]$ mv init_00n2nlqf_1_0.ora $ORACLE_HOME/dbs/initcsr.ora

[oracle@virt1 migration]$ cd $ORACLE_HOME/dbs

[oracle@virt1 dbs]$ vi initcsr.ora

# Please change the values of the following parameters:

  control_files            = "/u01/csr/migration/cf_D-CSR_id-1634082786_00n2nlqf"

  background_dump_dest     = "/u00/app/oracle/admin/csr/bdump"

  user_dump_dest           = "/u00/app/oracle/admin/csr/udump"

  core_dump_dest           = "/u00/app/oracle/admin/csr/cdump"

  audit_file_dest          = "/u00/app/oracle/admin/csr/adump"

  db_name                  = "CSR"

# Please review the values of the following parameters:

  __shared_pool_size       = 92274688

  __large_pool_size        = 4194304

  __java_pool_size         = 4194304

  __streams_pool_size      = 8388608

  __db_cache_size          = 343932928

  remote_login_passwordfile= "EXCLUSIVE"

  db_domain                = "omeir.global"

  dispatchers              = "(PROTOCOL=TCP) (SERVICE=csrXDB)"

# The values of the following parameters are from source database:

  processes                = 150

  sga_target               = 457179136

  db_block_size            = 8192

  compatible               = "10.2.0.1.0"

# log_archive_dest_1       = "LOCATION=/u01/ota/oradata/arch"

  log_archive_format       = "csr_%t_%s_%r.arc"

  db_file_multiblock_read_count= 16

  undo_management          = "AUTO"

  undo_tablespace          = "UNDOTBS1"

  job_queue_processes      = 10

  open_cursors             = 300

  pga_aggregate_target     = 152043520

Start the database in nomount state to create the dummy controlfile;

[oracle@virt1 oradata]$ sqlplus " /as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Feb 7 14:25:24 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area  457179136 bytes

Fixed Size                  1219976 bytes

Variable Size             109052536 bytes

Database Buffers          343932928 bytes

Redo Buffers                2973696 bytes

SQL> CREATE CONTROLFILE REUSE SET DATABASE "CSR" RESETLOGS  ARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 2336

LOGFILE

  GROUP 1 '/u01/csr/oradata/redo01.log' SIZE 10M,

  GROUP 2 '/u01/csr/oradata/redo02.log' SIZE 10M,

  GROUP 3 '/u01/csr/oradata/redo03.log' SIZE 10M

DATAFILE

  '/u01/csr/oradata/system01.dbf',

  '/u01/csr/oradata/undotbs01.dbf',

  '/u01/csr/oradata/sysaux01.dbf',

  '/u01/csr/oradata/users01.dbf',

  '/u01/csr/oradata/index01.dbf'

CHARACTER SET WE8ISO8859P1

;

Control file created.

Now edit the file Convertscript.rman and make necessary changes with respect to the target machine & execute this script.

[oracle@virt1 migration]$ rman target / nocatalog @convertscript.rman

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Feb 7 14:31:32 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: CSR (DBID=1634082786, not open)

using target database control file instead of recovery catalog

RMAN> RUN {

2>

3>   CONVERT DATAFILE '/u01/ota/oradata/users01.dbf'

4>   FROM PLATFORM 'Linux IA (32-bit)'

5>   FORMAT '/u01/csr/migration/data_D-OTA_I-1634082786_TS-USERS_FNO-4_06n2nlqg';

6>

7>

8>   CONVERT DATAFILE '/u01/ota/oradata/index01.dbf'

9>   FROM PLATFORM 'Linux IA (32-bit)'

10>   FORMAT '/u01/csr/migration/data_D-OTA_I-1634082786_TS-INDX_FNO-5_07n2nlqg';

11>

12>

13>   CONVERT DATAFILE '/u01/ota/oradata/system01.dbf'

14>   FROM PLATFORM 'Linux IA (32-bit)'

15>   FORMAT '/u01/csr/migration/data_D-OTA_I-1634082786_TS-SYSTEM_FNO-1_08n2nlqg';

16>

17>

18>   CONVERT DATAFILE '/u01/ota/oradata/undotbs01.dbf'

19>   FROM PLATFORM 'Linux IA (32-bit)'

20>   FORMAT '/u01/csr/migration/data_D-OTA_I-1634082786_TS-UNDOTBS1_FNO-2_09n2nlqh';

21>

22>

23>   CONVERT DATAFILE '/u01/ota/oradata/sysaux01.dbf'

24>   FROM PLATFORM 'Linux IA (32-bit)'

25>   FORMAT '/u01/csr/migration/data_D-OTA_I-1634082786_TS-SYSAUX_FNO-3_0an2nlqh';

26>

27>

28> }

Starting backup at 07-FEB-12

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: starting datafile conversion

input filename=/u01/ota/oradata/users01.dbf

converted datafile=/u01/csr/migration/data_D-OTA_I-1634082786_TS-USERS_FNO-4_06n2nlqg

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:05:47

Finished backup at 07-FEB-12

Starting backup at 07-FEB-12

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile conversion

input filename=/u01/ota/oradata/index01.dbf

converted datafile=/u01/csr/migration/data_D-OTA_I-1634082786_TS-INDX_FNO-5_07n2nlqg

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:03:35

Finished backup at 07-FEB-12

Starting backup at 07-FEB-12

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile conversion

input filename=/u01/ota/oradata/system01.dbf

converted datafile=/u01/csr/migration/data_D-OTA_I-1634082786_TS-SYSTEM_FNO-1_08n2nlqg

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:55

Finished backup at 07-FEB-12

Starting backup at 07-FEB-12

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile conversion

input filename=/u01/ota/oradata/undotbs01.dbf

converted datafile=/u01/csr/migration/data_D-OTA_I-1634082786_TS-UNDOTBS1_FNO-2_09n2nlqh

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:48

Finished backup at 07-FEB-12

Starting backup at 07-FEB-12

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile conversion

input filename=/u01/ota/oradata/sysaux01.dbf

converted datafile=/u01/csr/migration/data_D-OTA_I-1634082786_TS-SYSAUX_FNO-3_0an2nlqh

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:46

Finished backup at 07-FEB-12

Recovery Manager complete.

Now shutdown the csr database, delete the dummy control file, edit the transport sql and specify datafiles, log files in create controlfile section.

And execute the transportscript.sql file

oracle@virt1 oradata]$ sqlplus " /as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Feb 7 14:25:24 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance

SQL> @transportscript.sql

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE SET DATABASE "CSR" RESETLOGS  ARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 2336

LOGFILE

  GROUP 1 '/u01/csr/oradata/redo01.log' SIZE 10M,

  GROUP 2 '/u01/csr/oradata/redo02.log' SIZE 10M,

  GROUP 3 '/u01/csr/oradata/redo03.log' SIZE 10M

DATAFILE

  '/u01/csr/oradata/system01.dbf',

  '/u01/csr/oradata/undotbs01.dbf',

  '/u01/csr/oradata/sysaux01.dbf',

  '/u01/csr/oradata/users01.dbf',

  '/u01/csr/oradata/index01.dbf'

CHARACTER SET WE8ISO8859P1

;

-- Database can now be opened zeroing the online logs.

ALTER DATABASE OPEN RESETLOGS;

ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/csr/oradata/temp01.dbf'

     SIZE 1048576000  AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

set echo off

prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

prompt * Your database has been created successfully!

prompt * There are many things to think about for the new database. Here

prompt * is a checklist to help you stay on track:

prompt * 1. You may want to redefine the location of the directory objects.

prompt * 2. You may want to change the internal database identifier (DBID)

prompt *    or the global database name for this database. Use the

prompt *    NEWDBID Utility (nid).

prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SHUTDOWN IMMEDIATE

STARTUP UPGRADE

@@ ?/rdbms/admin/utlirp.sql

SHUTDOWN IMMEDIATE

STARTUP

-- The following step will recompile all PL/SQL modules.

-- It may take serveral hours to complete.

@@ ?/rdbms/admin/utlrp.sql

set feedback 6;

Now configure the listener & password file.

And use emca to create OEM repositiory  & emctl to configure dbcontrol.