Oracle database migration from AIX to Linux (Cross Platform Transportable Tablespaces)

Download from sheet note 2471245.1 archive with scriptsSpecify the list of TCs in the parameter

tablespaces=

Directories used to generate files

src_scratch_location=

dest_datafile_location=

dest_scratch_location=

ID platform

platformid=6

Preparation:

Create an empty database and configure it.The parameters must match the original database!Pay attention to the domain parameter, if it is suddenly set in the source database, then it must also be specified in the destination databaseCheck the version of the timezone file, they must match.for the time of filling in data and checking, it is better to disable job

Fill it with a source of users, while changing TS to USERS

impdp system/<password> directory=ttsdir network_link=ttslink FULL=y INCLUDE=USER,ROLE,ROLE_GRANT,PROFILE remap_tablespace=<>:USERS logfile=imp_metausr.log

Rename TS if USERS is also migrating!

alter tablespace users rename to userstbs

Backing up to an NFS share

1. Level 0 backup:

$ORACLE_HOME/perl/bin/perl xttdriver.pl --backup

2. Copy the res.txt file to the server where we restore the database (store this file, it contains the parameters for recovery)

3. We start recovery on the destination server:

$ORACLE_HOME/perl/bin/perl xttdriver.pl --restore

4. If the res.txt file is copied and the restore is started, then we immediately start a level 1 backup based on the source (AIX)

$ORACLE_HOME/perl/bin/perl xttdriver.pl --backup

5. Upon completion, copy the res.txt file to the destination server again, rename the old file !!!and start recovery

$ORACLE_HOME/perl/bin/perl xttdriver.pl --restore

4-5 we repeat as long as there is time and opportunity, each next backup is faster than the previous one.

6. Final backup:

We translate the necessary TS in read only

alter tablespace <name> read only;

.....

and run the backup:

$ORACLE_HOME/perl/bin/perl xttdriver.pl --backup

upon its completion, copy the res.txt file to the destination server and start recovery

Dumping data for linking files and objects in a new database into a dumpTo do this, create a parameter file with the following parfile structure:

dumpfile=xttdump.dmp

directory=DMP_XTTN

exclude=statistics

transport_tablespaces=comma separated list of spaces

transport_full_check=y

logfile=tts_export.log

7. All files are restored with new names in a new database, they are not associated with the database/objects, for communication, you need to generate a script

cd /devdata/data

ls -AF > list.txt

We open the file in the editor and add the path to the file, in the end we need to get something like this, while it is necessary to delete the files of the new database, they are not linked !!!

'/devdata/data/USERS_100.dbf',

'/devdata/data/USERS_101.dbf',

.....

We collect the par file for import:

vi par.imp

dumpfile=xttdump.dmp

directory=DMP_XTT

logfile=imp_log_1.log

transport_datafiles='/devdata/dbf/<file>',

.........

8. Importing data

impdp parfile=par.imp

After execution, check the log, if there are errors, correct it.but they should not be, they need to be edited at the testing stage.

9. File structure check - takes a long time

RMAN> validate tablespace <tablespace list> check logical;

10. Rule the default tablespace for users,execute the request on the source database

select 'alter user '||username||' default tablespace '||default_tablespace||';' from dba_users

and apply the output to the new DB

11. Reconciliation (it is assumed that there is a link to the original database)

11.1 Checking for tables and indexes

select * from (

select count(*), object_type, owner from dba_objects@TTSLINK where object_name not like 'BIN$%' group by owner,object_type

minus

select count(*), object_type, owner from dba_objects where object_name not like 'BIN$%' group by owner,object_type) d

where d.owner not in ('SYS','SYSTEM','WMSYS','XDB','PUBLIC','OWBSYS','ORDDATA','ORACLE_OCM','OLAPSYS','MDSYS','DBSNMP')

order by 3,2

11.2 Object sizes

select * from (

select round(sum(bytes/1024/1024/1024)), segment_name, owner from dba_segments@TTSLINK

where segment_name not like 'BIN$%' group by segment_name, owner

minus

select round(sum(bytes/1024/1024/1024)), segment_name, owner from dba_segments

where segment_name not like 'BIN$%' group by segment_name, owner ) a

where a.segment_name not like 'BIN$%'

order by 1 desc

p.s.: Make sure that all objects have been transferred, there may be cases of loss of tables

12. We translate everything into read write

alter tablespace USERS read write

.....

13. We transfer the logic, since the operations above transfer only tables, the catalog data will not be transferred.

expdp system/<> directory=<> content=metadata_only .....

14. We check the objects again to see if everything has moved.

select * from (

select count(*), object_type, owner from dba_objects@TTSLINK where object_name not like 'BIN$%' group by owner,object_type

minus

select count(*), object_type, owner from dba_objects where object_name not like 'BIN$%' group by owner,object_type) d

where d.owner not in ('SYS','SYSTEM','WMSYS','XDB','PUBLIC','OWBSYS','ORDDATA','ORACLE_OCM','OLAPSYS','MDSYS','DBSNMP')

order by 3,2

15. We transfer by hand everything that was separately created in SYS

16. We roll grants to system objects and system rights, rights to SYS objects with our hands

select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee||';' from DBA_TAB_PRIVS where owner ='SYS' and grantee not in ('SCHEDULER_ADMIN','SYS','XDB','OLAP_USER','DBA','MDSYS','OEM_USER','OLAPSYS','EM_EXPRESS_ALL','OEM_MONITOR','IMP_FULL_DATABASE','OWBSYS','DATAPUMP_IMP_FULL_DATABASE','GSMADMIN_INTERNAL','WMSYS','OWB$CLIENT','SPATIAL_WFS_ADMIN_USR','SYSBACKUP','DBSNMP','SYSTEM','ORDSYS','SI_INFORMTN_SCHEMA','AUDIT_ADMIN','CTXSYS','OJVMSYS','RECOVERY_CATALOG_OWNER','OWBSYS_AUDIT','APPQOSSYS','EXP_FULL_DATABASE','DATAPUMP_EXP_FULL_DATABASE','AQ_ADMINISTRATOR_ROLE','CONNECT','ANONYMOUS','MDDATA','OEM_ADVISOR','JAVADEBUGPRIV','SPATIAL_CSW_ADMIN_USR','RESOURCE','SYSDG','OUTLN','SYSKM','EM_EXPRESS_BASIC','CDB_DBA','PUBLIC','SELECT_CATALOG_ROLE','EXECUTE_CATALOG_ROLE','DBFS_ROLE','LOGSTDBY_ADMINISTRATOR','AQ_USER_ROLE','GATHER_SYSTEM_STATISTICS','ORACLE_OCM','ADM_PARALLEL_EXECUTE_TASK','HS_ADMIN_SELECT_ROLE','OLAP_XS_ADMIN','XDBADMIN','HS_ADMIN_EXECUTE_ROLE','ORDPLUGINS','SHOW_PLAN_QUERY','CAPTURE_ADMIN','AUDIT_VIEWER','OPTIMIZER_PROCESSING_RATE','CSMIG','PDB_DBA','GSMUSER_ROLE','XS_CACHE_ADMIN','GSMCATUSER')

select 'grant '||privilege||' to '||grantee||';' from DBA_SYS_PRIVS where grantee not in ('SCHEDULER_ADMIN','SYS','XDB','OLAP_USER','DBA','MDSYS','OEM_USER','OLAPSYS','EM_EXPRESS_ALL','OEM_MONITOR','IMP_FULL_DATABASE','OWBSYS','DATAPUMP_IMP_FULL_DATABASE','GSMADMIN_INTERNAL','WMSYS','OWB$CLIENT','SPATIAL_WFS_ADMIN_USR','SYSBACKUP','DBSNMP','SYSTEM','ORDSYS','SI_INFORMTN_SCHEMA','AUDIT_ADMIN','CTXSYS','OJVMSYS','RECOVERY_CATALOG_OWNER','OWBSYS_AUDIT','APPQOSSYS','EXP_FULL_DATABASE','DATAPUMP_EXP_FULL_DATABASE','AQ_ADMINISTRATOR_ROLE','CONNECT','ANONYMOUS','MDDATA','OEM_ADVISOR','JAVADEBUGPRIV','SPATIAL_CSW_ADMIN_USR','RESOURCE','SYSDG','OUTLN','SYSKM','EM_EXPRESS_BASIC','CDB_DBA')

17. Add to UNDO and TEMP files.

18. Set parameter job_queue_processes