2009-09-13

Convert database from Solaris to AIX on destination server

Oracle RDBMS 10.2.0.3

1. Make sure source and destination oracle homes are the same version.

2. Get destination platform name from
select PLATFORM_NAME from V$DB_TRANSPORTABLE_PLATFORM;

3. Open database read only and check if it is ok to convert.
startup mount;
alter database open read only;

set serveroutput on
declare
db_ready boolean;
begin
db_ready := dbms_tdb.check_db('AIX-Based Systems (64-bit)',0);
end;
/
-- if no message is returned it is ok to convert db

set serveroutput on
declare
external boolean;
begin
/* value of external is ignored, but with SERVEROUTPUT set to ON
* dbms_tdb.check_external displays report of external objects
* on console */
external := dbms_tdb.check_external;
end;
/

output:
The following directories exist in the database:
SYS.WORK_DIR, SYS.ADMIN_DIR, SYS.DATA_PUMP_DIR

4. Run RMAN command to create conversion scripts and init.ora file:
FORMAT defines location of init.ora file
DB_FILE_NAME_CONVERT defines final data files location on destination server.
If source database is not available (only backup) you can skip this step. Convert and transport scripts can be created manually.

CONVERT DATABASE ON TARGET PLATFORM
CONVERT SCRIPT '/oracle/oradata/01/DHMERM03/testdb/convertscript.rman'
TRANSPORT SCRIPT '/oracle/oradata/01/DHMERM03/testdb/transportscript.sql'
new database 'dhmerm10'
FORMAT '/oracle/oradata/01/DHMERM03/testdb/%U'
DB_FILE_NAME_CONVERT =('DHMERM03/testdb/dhmerm09','DHMERM04/testdb/dhmerm10');

5. Prepare environment file. Unset LD_LIBRARY_PATH and LD_LIBRARY_PATH_64 for AIX.
6. Copy init.ora file to destination server $ORACLE_HOME/dbs, rename and edit.
Make sure *dump directories exist.

7.
Copy convertscript.rman to destination server.
Copy transportscript.sql to destination server.


8. Identify datafiles that contain undo data by running the following query:

select distinct(file_name)
from dba_data_files a, dba_rollback_segs b
where a.tablespace_name=b.tablespace_name;

Copy unconverted data files (system, undo) into stage area on destination server.
Copy rest datafiles to final location on destination server.

9. Edit the convert script and leave the CONVERT DATAFILE commands for only the datafiles that contain undo data.
CONVERT DATAFILE points to unconverted files (local stage area or NFS to source file system).
FORMAT defines final destination.
You can convert all datafiles, especially if source database is not available and you are not sure about undo segments.

RUN {
CONVERT DATAFILE '/oracle/oradata/01/DHMERM04/testdb/stage/system01.dbf'
FROM PLATFORM 'Solaris[tm] OE (64-bit)'
FORMAT '/oracle/oradata/01/DHMERM04/testdb/dhmerm10/system01.dbf';

CONVERT DATAFILE '/oracle/oradata/01/DHMERM04/testdb/stage/undotbs01.dbf'
FROM PLATFORM 'Solaris[tm] OE (64-bit)'
FORMAT '/oracle/oradata/01/DHMERM04/testdb/dhmerm10/undotbs01.dbf';
}

10. Create script for temporary control file create_temp_cf.sql. Datafile path must be current, unconverted files.
You can create it from transport script or from trace backup (alter database backup controlfile to trace).

CREATE CONTROLFILE REUSE SET DATABASE "DHMERM10" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oracle/oradata/01/DHMERM03/testdb/dhmerm10/redo01.log' SIZE 50M,
GROUP 2 '/oracle/oradata/01/DHMERM03/testdb/dhmerm10/redo02.log' SIZE 50M,
GROUP 3 '/oracle/oradata/01/DHMERM03/testdb/dhmerm10/redo03.log' SIZE 50M
DATAFILE
'/oracle/oradata/01/DHMERM04/testdb/stage/system01.dbf',
'/oracle/oradata/01/DHMERM04/testdb/stage/undotbs01.dbf',
'/oracle/oradata/01/DHMERM04/testdb/dhmerm10/sysaux01.dbf',
'/oracle/oradata/01/DHMERM04/testdb/dhmerm10/users01.dbf'
CHARACTER SET AL32UTF8
;

11. Check environment variables:
echo $ORACLE_SID
echo $ORACLE_HOME

12. Startup instance on destination server and create temporary control file.
sqlplus / as sysdba
startup nomount
@create_temp_cf.sql

13. Run conversion:
rman target /
@convertscript.rman
shutdown immediate;

14. Configure listener.
15. Create password file.
16. Edit transport script, change names for log and temp files, check other paths.
sqlplus / as sysdba
@transportscript.sql

Transport script:


STARTUP NOMOUNT
CREATE CONTROLFILE REUSE SET DATABASE "DHMERM10" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oracle/oradata/01/DHMERM04/testdb/dhmerm10/redo01.log' SIZE 50M,
GROUP 2 '/oracle/oradata/01/DHMERM04/testdb/dhmerm10/redo02.log' SIZE 50M,
GROUP 3 '/oracle/oradata/01/DHMERM04/testdb/dhmerm10/redo03.log' SIZE 50M
DATAFILE
'/oracle/oradata/01/DHMERM04/testdb/dhmerm10/system01.dbf',
'/oracle/oradata/01/DHMERM04/testdb/dhmerm10/undotbs01.dbf',
'/oracle/oradata/01/DHMERM04/testdb/dhmerm10/sysaux01.dbf',
'/oracle/oradata/01/DHMERM04/testdb/dhmerm10/users01.dbf'
CHARACTER SET AL32UTF8
;
ALTER DATABASE OPEN RESETLOGS;
ALTER TABLESPACE TEMP ADD TEMPFILE '/oracle/oradata/01/DHMERM04/testdb/dhmerm10/temp01.dbf' SIZE 20971520 AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
SHUTDOWN IMMEDIATE
STARTUP UPGRADE
@@ ?/rdbms/admin/utlirp.sql
SHUTDOWN IMMEDIATE
STARTUP
-- The following step will recompile all PL/SQL modules.
@@ ?/rdbms/admin/utlrp.sql

Useful info:
Metalink 415884.1 Cross Platform Database Conversion with same Endian
Metalink 414878.1 Cross-Platform Migration on Destination Host Using Rman Convert Database
Metalink 732053.1 Avoid Datafile Conversion during Transportable Database
Metalink 417455.1 Datafiles are not converted in parallel for transportable database
Platform Migration using Transportable Database
RMAN Cross-Platform Transportable Databases and Tablespaces