Tuesday, July 23, 2019

Migrate database across platform-12c (Different Endian)

We can use either 'TO PLATFORM' or 'FOR TRANSPORT' clause during the backup and restoration.

TO PLATFORM - Perform  conversion on the source database.

FOR TRANSPORT - Performs conversion on the target database.


1. Find the Platform details of both source and target database

   Source DB
SQL> select name,platform_id,platform_name from v$database;

NAME PLATFORM_ID PLATFORM_NAME
--------- ----------- ------------------------------
TESTCDB 12 Microsoft Windows x86 64-bit
   Target DB
SQL> select name,platform_id,platform_name from v$database;

NAME PLATFORM_ID PLATFORM_NAME
--------- ----------- ------------------------------
UATCDB 2 Solaris[tm] OE (64-bit)

2. Make sure both source and target databases are in the Different endian format
SQL> select PLATFORM_ID,PLATFORM_NAME,ENDIAN_FORMAT from v$transportable_platform order by platform_id;

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ------------- --------------
1 Solaris[tm] OE (32-bit) Big 
2 Solaris[tm] OE (64-bit) Big 
3 HP-UX (64-bit) Big 
4 HP-UX IA (64-bit) Big 
5 HP Tru64 UNIX Little 
6 AIX-Based Systems (64-bit) Big 
7 Microsoft Windows IA (32-bit) Little 
8 Microsoft Windows IA (64-bit) Little 
9 IBM zSeries Based Linux Big 
10 Linux IA (32-bit) Little 
11 Linux IA (64-bit) Little 
12 Microsoft Windows x86 64-bit Little 
13 Linux x86 64-bit Little 
15 HP Open VMS Little 
16 Apple Mac OS Big 
17 Solaris Operating System (x86) Little 
18 IBM Power Based Linux Big 
19 HP IA Open VMS Little 
20 Solaris Operating System (x86-64) Little 
21 Apple Mac OS (x86-64) Little 

20 rows selected.

3. Connect to Source database(Windows) and make the tablespace read only which need to transportable
RMAN target sys/manager@TESTPDB
RMAN> Alter tablespace USERS read only;

4. Backup tablespace. Make sure Database directory is available on given path

RMAN> BACKUP 
TO PLATFORM 'Solaris[tm] OE (64-bit)' 
FORMAT 'C:\Build\Backups\to_solaris\new\users.bkp' 
DATAPUMP FORMAT 'C:\Build\Backups\to_solaris\new\users_dmp.bkp'
TABLESPACE USERS;

5. Copy both backup and datapump files to target server

6. connect to target DB (Solaris) and drop specific Tablespace which need to restore
sqlplus sys/manager@UATPDB as sysdba
SQL> DROP TABLESPACE USERS INCLUDING CONTENTS AND DATAFILES;

7. Restore Tablespace
RMAN target sys/manager@UATPDB
RMAN> RESTORE
FOREIGN TABLESPACE USERS
format '/u02/oradata/UAT10CDB/UATPDB/%U'
FROM BACKUPSET '/u01/scripts/to_solaris/users.BKP'
DUMP FILE FROM BACKUPSET '/u01/scripts/to_solaris/users_dmp.BKP';

8. Check the tablespace status
sqlplus sys/manager@UATPDB as sysdba
SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='USERS';
TABLESPACE_NAME STATUS
--------------- -------
USERS READ ONLY

9. Make the tablespace online
SQL> alter tablespace USERS read write;

Solution for 'ORA-12919: Can not drop the default permanent tablespace' while drop the USERS tablespace

1. Add the USERS temporary tablespace and make it dafault permanent
sqlplus sys/manager@UATPDB as sysdba
SQL> CREATE TABLESPACE USERS_TEMP DATAFILE '/u02/oradata/UAT10CDB/UATPDB/users_temp_01.dbf' size 100M;
SQL> alter database default tablespace USERS_TEMP;

2. Make sure Dafault permanent Tablespace changed to newly created tablespace
sqlplus sys/manager@UATPDB as sysdba
SQL> SELECT PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_PERMANENT_TABLESPACE';

PROPERTY_VALUE
-------------------------------------
USERS_TEMP

3. Then drop the USERS tablespace
sqlplus sys/manager@UATPDB as sysdba
SQL> DROP TABLESPACE USERS INCLUDING CONTENTS AND DATAFILES;

No comments:

Post a Comment