This article explains the steps that needs to followed while migrating ORACLE 10g Database from HP UNIX to LINUX platform. This article can also be used for migrating Oracle 10g Database across any platform with slight changes to the steps.
ASSUMPTIONS
The Oracle Database in UNIX is 10g. Migration happens while Database in UNIX is up & running.
Oracle Binary is installed in Linux. It’s assumed that UNIX Database will no longer be used once LINUX Database built.
Assume the Database name in UNIX is UNIX-A & the Database name in LINUX is LINUX-B
STEPS
1. Migrate all dictionary Managed tablespaces in UNIX-A to Locally managed tablespaces except System tablespace, using procedure DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL.
2. It is mandatory to convert the Tablespaces to locally managed, as we will be using Transportable Tablespace utility.
3. Verify the status of Tablespace by querying DBA_TABLESPACES
4. V$TRANSPORTABLE_PLATFORM view gives the platforms supported and you could determine platform’s endian format (byte ordering).
5. Change all the tablespaces to read only mode except SYSTEM, DBAUNIXA, PERFSTAT_DATA, SYSAUNIX, %UNDO% tablespaces using the DDL
ALTER TABLESPACE <<TABLESPACE_NAME>> READ ONLY;
Verify the status of Tablespaces by querying DBA_TABLESPACES
6. Make sure all tablespaces, excluding SYSTEM, DBAUNIXA, PERFSTAT_DATA, SYSAUNIX, %UNDO% are self-contained i.e. there are no references from inside tablespaces pointing outside the tablespaces. This is to avoid issues while plugging in Tablespaces in Destination Database.
7. Create a PL/SQL block to set required tablespaces to self contained by executing dbms_tts.transport_set_check to TRUE
e.g.
DECLARE var1 VARCHAR2 (32767); BEGIN var1: ='TS1, TS2, TS3’; var1 : =var1 ||',TS4,TS5’; ……………………………….. ……………………………… …………………………….... DBMS_TTS.TRANSPORT_SET_CHECK(var1,TRUE); END; /
8. Query transport_set_violations view to check the violations, if any correct them.
9. Take export dump metadata of all self-contained tablespaces in UNIX server
exp userid=\’/ as sysdba\’ file=tt.dmp transport_tablespace=y tablespaces=<specify all the self-contained tablespace names separated by comma>
10. Transfer the export dump file to LINUX server where new Database LINUX-B is supposed to be built up using FTP or RCP.
11. Transfer the datafiles for all self-contained tablespaces from UNIX server to a staging area in LINUX server using FTP or RCP.
12. Build up a database LINUX-B in LINUX server, make sure the database character set and block size are same as Database UNIX-A. You could use DBCA or manual scripts to create the new database
13. Take the initialization parameter file of UNIX-A, transfer it to LINUX staging area & change all required parameters per LINUX-B need. Steps 12 & 13 can be performed anytime and independent of the steps mentioned above.
14. After all datafiles are copied to LINUX (after step 11 completes), start converting the datafiles in staging area to LINUX format.
Below shows an example of converting all datafiles inside a directory. In the same way, script can be prepared for other directories. Datafile conversion for multiple directories can be kicked off in parallel from multiple sessions.
$ rman target=/
RMAN> CONVERT DATAFILE ‘/gfs/transport_stage/oracle01/db01.dbf’, <list of all datafiles under this directory separated by comma and every single file should be within single quotes> FROM PLATFORM = ‘HP-UNIX (64-bit)’ Database_FILE_NAME_CONVERT = ‘/gfs/transport_stage/oracle01/’ , ‘/gfs/B/oracle01/’;
RMAN itself moves the datafiles from staging area to LINUX-B’s appropriate Datafile locations
15. Rename the tablespace USERS to USERS1 on LINUX-B, because LINUX-B’s USERS tablespace could conflict with USERS tablespace of UNIX-A while doing import in the following steps
16. Take the DDL of all Application specific users and roles from UNIX-A and modify the script for default tablespace to USERS1 and Temporary tablespace to TEMP.
17. Take DDL of dblinks from UNIX-A as well, you need to modify the script to have Database link user passwords manually.
18. Use the scripts generated in steps 16/17 and create the users/roles/Database-links on LINUX-B
19. Make sure tablespace users1 and temporary tablespace temp have enough free space. Increase the undo tablespace size as well in LINUX-B
20. Import metadata and plug-in the tablespaces to Database LINUX-B
imp userid=\’/ as sysdba\’ transport_tablespace=y ignore=y file=tt.dmp log=imp_tt.log statistics=none datafiles=<list of all datafiles converted to LINUX separated by comma >
Check the import log and fix the errors if any.
21. Change all the plugged in tablespaces to read write mode in LINUX-B
22. Change the default tablespace of all required users from USERS1 to USERS. Check for any segment presence in USERS1, move it to USERS tablespace if required.
23. After confirming that no segment is there in USERS1 tablespace in LINUX-B, drop it including the contents and datafiles.
24. Through TRANSPORTABLE tablespaces, some objects like procedure, function, packages etc would not have been transported to LINUX-B, so need to re-create them in LINUX-B
Better approach is, take a complete export dump of the UNIX-A database, with option rows=N and import it to LINUX-B
25. After that, check for any invalid objects and compile them in LINUX-B. You could use utlrp.sql script.
26. Compare the sizes of SYSTEM, SYSAUNIX, UNDO and TEMP between UNIX-A and LINUX-B. Make sure the sizes are approximately same (It is fine if the Tablespace size in LINUX-B is more than UNIX-A).
27. Perform a complete object validation on LINUX-B
Since it has been assumed that UNIX-A is no longer be used I’ve not included steps to revert the changes made in UNIX-A
28. Grant alter any materlized view privilege to system in LINUX-B.
Related Posts
- UNIX Command Tips for Developers
- ORA-01652 unable to extend temp segment by 64 in tablespace
- Oracle PL/SQL: Oracle System Tables
- SQL Server 2005 database DBA Checklist for database administrator
- Error: SQL0284N The table was not created
Tags: migrating oracle database, oracle 10g, oracle 10g migration, oracle linux migration, oracle migration, oracle unix to linux, unix to linux migration
