How to do cross platform migration in Oracle:

Transportable Tablespaces (TTS) Overview:

Transportable Tablespaces (TTS) is an Oracle feature that allows you to move large amounts of data between Oracle databases, even across different platforms, by copying the tablespace files instead of doing a traditional export/import. This method is particularly useful for platform migrations, database consolidation, or data warehouse refreshes where minimizing downtime is crucial.

Key Benefits:

• Faster than full database export/import because you only move metadata and tablespace files.

• Platform independence (with the Cross-Platform Transportable Tablespace option).

• Minimal downtime required.

Case Study: Cross-Platform Migration using Transportable Tablespaces (TTS)

Scenario:

A company, ABC Corporation, runs an Oracle database on a Windows server, and due to cost-saving and performance reasons, they want to migrate it to a Linux environment. The database is large, and they want minimal downtime. ABC Corporation chooses Cross-Platform Transportable Tablespaces (XTTS) to migrate the data from Windows to Linux.

Problem:

• The Oracle database is hosted on Windows OS.

• They need to move it to a Linux OS, but the platforms have different file structures and storage formats.

• Downtime needs to be minimized, and a full database export/import process would be too slow.

Solution: Cross-Platform Transportable Tablespaces (XTTS)

Using Oracle's XTTS feature allows ABC Corporation to transport only the tablespaces of interest between Windows and Linux.

Step-by-Step Process:

1. Check Platform Compatibility:

Verify the endian format of the source (Windows) and target (Linux) platforms using the following query:
sql
Copy code
SELECT * FROM v$transportable_platform;

1. • 
     

   • If the source and target have different endian formats (e.g., Windows and Linux), Oracle will need to convert the data files as part of the process.

2. Make Tablespaces Read-Only on Source (Windows):

To transport the tablespaces, they must be in a read-only state:
sql
Copy code
ALTER TABLESPACE users READ ONLY;

2. • 
     

3. Export Tablespace Metadata:

Use Data Pump (EXPDP) to export only the metadata of the tablespaces. This contains no actual data, just the information about tablespace structures:
bash
Copy code
expdp system/password directory=dump_dir dumpfile=tts_metadata.dmp logfile=tts_export.log transport_tablespaces=users

3. • 
     

4. Copy the Data Files to Target (Linux):

   • Copy the tablespace data files from the Windows server to the Linux server using a file transfer tool like scp or rsync.

5. Convert Data Files (if Endianness is Different):

If the endian format is different (as it usually is between Windows and Linux), use RMAN on the target (Linux) platform to convert the data files:
bash
Copy code
RMAN> CONVERT DATAFILE '/windows_path/users01.dbf' 

      TO PLATFORM 'Linux x86 64-bit' 

      FROM PLATFORM 'Microsoft Windows x86 64-bit'

      DB_FILE_NAME_CONVERT ('/windows_path/','/linux_path/');

5. • 
     

6. Import Tablespace Metadata on Target (Linux):

On the target system, use Data Pump (IMPDP) to import the metadata for the transported tablespaces:
bash
Copy code
impdp system/password directory=dump_dir dumpfile=tts_metadata.dmp logfile=tts_import.log transport_datafiles='/linux_path/users01.dbf'

6. • 
     

7. Make Tablespaces Read-Write on Target:

Once the tablespaces are successfully transported and attached, switch the tablespaces to read-write mode:
sql
Copy code
ALTER TABLESPACE users READ WRITE;

7. • 
     

8. Update or Validate Users and Objects:

   • Verify that all database objects and users associated with the transported tablespaces are intact.

9. Post-Transport Cleanup:

On the source system, if necessary, set the tablespaces back to read-write (if still in use):
sql
Copy code
ALTER TABLESPACE users READ WRITE;

9. • 
     

   • Cleanup any temporary files or scripts used during the migration.

Conclusion:

Using Transportable Tablespaces, ABC Corporation was able to migrate their Oracle database from Windows to Linux with minimal downtime and effort, avoiding the lengthy full export/import process. The XTTS method not only saved time but also ensured data integrity and efficient resource use.

Key Points to Remember:

• Endianness matters: Cross-platform migrations often require endian conversion using RMAN.

• Minimal downtime: Because the tablespaces are only in read-only mode for the duration of the migration, the system remains available for use.

• XTTS simplifies cross-platform migration: Transporting tablespaces is more efficient than traditional methods, especially for large datasets.

Restoring an Oracle database using RMAN from a backup taken on a different machine (also called a restore and recovery to a new host) requires careful steps. The process involves copying the backup pieces and other required files (control files, spfile, etc.) from the source system to the destination machine. Below are the detailed steps to perform an RMAN restore from a different machine.

Scenario:

• The source machine (where the backup was taken) is running Oracle, and RMAN backups have been taken.

• The destination machine (where you want to restore the backup) has Oracle installed but may not have any database files yet.

Pre-Requisites:

1. Ensure the Oracle software is installed on the destination machine (same version as the source).

2. Backup files, SPFILE, and control files must be available.

3. The DBID of the source database is required for the restore.

4. The filesystem structure on the new machine should be compatible with the old one, or you need to rename the files during the restore.

Steps to Restore RMAN Backup on a Different Machine:

1. Obtain the DBID from the Source System

The DBID (Database Identifier) is critical for starting RMAN on the target system. You can get it from the source database before starting the restore:

sql

Copy code

RMAN> CONNECT TARGET /

RMAN> LIST DBID;

Alternatively, if you don't have access to the source database, you can find the DBID in the backup logs or metadata.

2. Prepare the Target Machine

• Install the Oracle software on the target machine (same version as the source).

• Set up the environment variables (ORACLE_HOME, ORACLE_SID, PATH, etc.) correctly on the target machine.

• Ensure there is enough disk space on the target machine to accommodate the data files, control files, and archived redo logs.

3. Transfer the RMAN Backup Files to the Target Machine

• Copy the RMAN backup pieces, SPFILE (if available), and control file backups from the source machine to the destination machine. You can use scp, rsync, or another method.

• Backup files are typically located in the DB_RECOVERY_FILE_DEST directory or another defined backup directory.

4. Start RMAN and Set the DBID on the Target Machine

Connect to RMAN on the new machine and set the DBID from the source database:

sql

Copy code

RMAN> CONNECT TARGET /

RMAN> SET DBID <dbid>;

5. Restore the SPFILE (if needed)

If you don't have the SPFILE, you need to restore it from the RMAN backup:

sql

Copy code

RMAN> STARTUP FORCE NOMOUNT;

RMAN> RESTORE SPFILE FROM '<path_to_backup_piece>';

RMAN> STARTUP FORCE NOMOUNT;

If you do have the SPFILE, place it in the appropriate location (typically $ORACLE_HOME/dbs).

6. Restore the Control File

Restore the control file from the backup:

sql

Copy code

RMAN> RESTORE CONTROLFILE FROM '<path_to_backup_piece>';

RMAN> ALTER DATABASE MOUNT;

7. Catalog the Backup Pieces

Catalog the backup files that you copied from the source machine so RMAN is aware of them:

sql

Copy code

RMAN> CATALOG START WITH '<path_to_backup_files>';

This command will scan the directory and add the RMAN backup pieces to the RMAN repository.

8. Restore the Database

Now, restore the data files from the backup:

sql

Copy code

RMAN> RESTORE DATABASE;

If the file paths differ between the source and target systems, use the SET NEWNAME command to specify new file paths:

sql

Copy code

RMAN> RUN {

   SET NEWNAME FOR DATAFILE 1 TO '/new_path/system01.dbf';

   RESTORE DATABASE;

   SWITCH DATAFILE ALL;

}

9. Recover the Database

After restoring the data files, apply the archived redo logs or incremental backups to bring the database to a consistent state:

sql

Copy code

RMAN> RECOVER DATABASE;

10. Open the Database with RESETLOGS

After the recovery is complete, open the database with the RESETLOGS option:

sql

Copy code

RMAN> ALTER DATABASE OPEN RESETLOGS;

11. Post-Restore Steps

• Check the database by running a few queries to ensure everything is functioning as expected.

• Back up the database again after opening it with the RESETLOGS option to ensure you have a valid backup of the restored state.

If needed, recreate or relocate the tempfiles for the temporary tablespaces:
sql
Copy code
SQL> ALTER TABLESPACE temp ADD TEMPFILE '/new_path/temp01.dbf' SIZE 500M;

Summary of Key Steps:

1. Get the DBID from the source database.

2. Install Oracle on the target machine.

3. Copy the RMAN backup files, SPFILE, and control files to the target.

4. Start RMAN, set the DBID, and restore the SPFILE and control files.

5. Catalog the backup pieces on the target machine.

6. Restore the database and recover the data files.

7. Open the database with the RESETLOGS option.

This process ensures that you can successfully restore an RMAN backup from one machine to another. Let me know if you need additional details on any step!