Reduce Oracle Transportable Tablespace Migration Downtime using Cross Platform Incremental Backup V4

 

Overview

I have recently be part of a big migration project and I worked in Database Team where our responsibility was to migrate around twenty Oracle Databases from Oracle Database 12c (12.1.0.2) on AIX system (7.1.) on-premises to Oracle Database 19c (19.17.0.0) on Redhat Enterprise Linux (7.9) on Oracle Cloud Infrastructure (OCI). Our big challenge was to achieve this with a minimum downtime.

After many assessments, we agreed using the last version (V4) of cross platform transportable tablespaces (XTTS) and Oracle’s cross platform incremental backup capability.

I share this document with you to covers the steps that we used. The procedure is divided in 9 compartments including:

1.  Prerequisites

2.  Import user Metadata

3.  Post user Metadata import

4.  Roll forward (XTTS Incremental Backup and Restore)

5.  Final Incremental Backup and Restore

6.  Transport tablespaces

7.  Update sequences (Option)

8.  Post Tablespace Import

This procedure have been used to migrate Databases from AIX to Linux, but it can also be used for all Oracle cross platform migration from 11.2.0.3 except Windows as either source or destination. The source and destination database can have different endian formats. The procedure works for multi-tenant environment, including transporting tablespace from non-CDB to CDB or visa-versa. It will automatically resolve added datafiles with no additional intervention. Finally, the procedure allows multiple incremental backups taken off the source database without running the recovery. After which, recovery will be run for all the incremental backups in the destination at once.

A set of supporting scripts in the file rman_xttconvert_VER4.zip must be downloaded from Oracle Support.

1. Prerequisites

 

1.1.  Parameters

Source Database:

sourcesrv.localdomain.com	Server Name
AIX version 7.1.0	OS
172.abc.def.ghi	Server Ip
dbsrc	Database Name/Alias tnsnames/pdb Name
dbsrc1	Instance Name
+DATA	Data files location
12.1.0.2	Database Version
/mnt/srcsharedloc	Alias for Shared location
src_dp_user_migr	User Name for remote connection
src_migr_password	Password for src_dp_user_migr
src_sys_password	Password for SYS user

Destination Database:

c	Server Name
Redhat Enterprise 7.9	OS
10.rst.uvw.xyz	Server Ip
dbdest	Database Name
dbdest	PDB Name/Alias tnsnames
/u01/app/19.0.0.0/grid	ASM Home
+DATA	Data files location
19.17.0.0	Database version
/mnt/destsharedloc	Alias for Shared location
dest_dp_user_migr	User Name for import
dest_migr_password	Password for dest_dp_user_migr
dest_sys_password	Password for SYS user

Note: /mnt/srcsharedloc and /mnt/destsharedloc are pointing to the same shared file system. This configuration can be done by your System Administrator.

 

1.2.  Prerequisites on source database

 

1.2.1.    Working Directories

The below directories must be created as shared directories.

Directory	Description
/mnt/srcsharedloc/migration/	Parent working directory. This directory must be shared with destination
/mnt/srcsharedloc/migration/data	Directory for backup data
/mnt/srcsharedloc/migration/impdp	Directory for shared script, parameter files and log files
/mnt/srcsharedloc/migration/xttconvertscript	Directory for xttconvert scripts

Run the below scripts to create working directories:

Server: sourcesrv

User: oracle

ssh	mkdir -p /mnt/srcsharedloc/migration/data
ssh	mkdir -p /mnt/srcsharedloc/migration/impdp
ssh	mkdir -p /mnt/srcsharedloc/migration/xttconvertscript

1.2.2.    Install xttconvert scripts

As the oracle owner, extract rman_xttconvert_VER4.zip file onto shared location and change the permissions for all the files and directories.

Server: sourcesrv

User: oracle

ssh	cd /mnt/srcsharedloc
ssh	unzip rman_xttconvert_VER4.zip -d /migration/xttconvertscript
ssh	chmod 777 migration

1.2.3.    Check if tablespaces are transportables

The below procedure will show possible errors that may arise if the tablespace is not compatible or ready to be transported.

Server: sourcesrv

User: oracle

ssh	sqlplus sys/src_sys_password@dbsrc as sysdba
sql	declare       checklist varchar2(32000);       i number := 0; begin       for ts in           (select tablespace_name         from dba_tablespaces              where tablespace_name not in ('SYSTEM','SYSAUX')              and contents = 'PERMANENT')       loop             if (i=0) then                   checklist := ts.tablespace_name;             else                   checklist := checklist||','||ts.tablespace_name;             end if;         i := 1;       end loop;       dbms_tts.transport_set_check(checklist,TRUE,TRUE); end; /
sql	select * from transport_set_violations;   If there is rows from this query, you have to fix the issue.

1.2.4.    Check Objects in SYSTEM and SYSAUX tablespaces

Check if there are user objects that need to be moved on user tablespaces or must be migrated separately.

Server: sourcesrv

User: oracle

ssh	sys/src_sys_password@dbsrc as sysdba
sql	select owner, segment_name, segment_type   from dba_segments   where tablespace_name in ('SYSTEM', 'SYSAUX')     and owner not in           (select name            from system.logstdby$skip_support --Exclude oracle_maintained users           where action=0) and owner not like  'APEX%' and owner not in ('FLOWS_FILES','ORDS_METADATA') order by 1,2,3;

1.2.5.    Generate script to create temporaries tablespaces

This command generates temp_tbs.sql script which contents all the SQL commands that will allow you to create other temporaries tablespaces than TEMP on destination like on source.

Server: sourcesrv

User: oracle

ssh

sqlplus -s sys/src_sys_password@dbsrc  as sysdba <<EOF SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF SET HEAD OFF ECHO OFF VERIFY OFF TRIMSPOOL ON exec DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true); exec DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true); spool temp_tbs.sql replace SELECT DBMS_METADATA.get_ddl ('TABLESPACE', tablespace_name) FROM   dba_tablespaces WHERE  CONTENTS='TEMPORARY' AND TABLESPACE_NAME NOT IN ('TEMP') / EOF

1.2.6.    Generate scripts to create initial tablespaces

The below commands generate xtts_create_tbs.sql script which contents all the SQL that allow you to create initial tablespaces on destination database based on tablespaces on source. These tablespaces will be dropped before the final restore.

Server: sourcesrv

User: oracle

ssh

cd /mnt/srcsharedloc/migration/impdp

ssh

sqlplus -s sys/src_sys_password@dbsrc as sysdba <<EOF set long 20000 longchunksize 20000 pagesize 0 linesize 1000 feedback off set head off echo off verify off trimspool on exec DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true); exec DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true); col TBSDDL format A2000 spool xtts_create_tbs.sql prompt /* ========================= */ prompt /* creation of tbs with a minimum space */ prompt /* ========================= */ select             regexp_replace(                 regexp_replace(                     regexp_replace(dbms_metadata.get_ddl('TABLESPACE',tablespace_name),                     '(,.*SIZE .*[0-9]).*LOGGING',                     ' '||logging,1,1,'mn'),                 '(SIZE .*[0-9])',                  'SIZE 12M',1,1,''                 ),             '(ALTER .*).*$',             '',1,1,'n') from dba_tablespaces where tablespace_name not in ('SYSTEM','SYSAUX')   and contents = 'PERMANENT'  order by tablespace_name; spool off EOF

1.2.7.    Generate scripts to drop initial tablespaces

The below commands generate xtts_drop_tbs.sql script which contents all the SQL that allow you to drop initial tablespaces on destination database.

Server: sourcesrv

User: oracle

ssh

cd /mnt/srcsharedloc/migration/impdp

ssh

sqlplus -s sys/src_sys_password@dbsrc as sysdba <<EOF set heading off feedback off trimspool on linesize 500 spool xtts_drop_tbs.sql prompt /* ===================== */ prompt /* Drop user tablespaces */ prompt /* ===================== */ select 'DROP TABLESPACE ' || tablespace_name || ' INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;' from dba_tablespaces where tablespace_name not in     ('SYSTEM','SYSAUX') and contents = 'PERMANENT'; order by REGEXP_substr(tablespace_name,'_I0[1-3]*_') nulls last, tablespace_name; spool off EOF

1.2.8.    Generate scripts to change tablespaces mode

The below commands allow you to generate scripts xtts_tbs_ro.sql to put tablespaces in read only mode and xtts_tbs_rw.sql to put tablespaces in read write respectively.

Server: sourcesrv

User: oracle

ssh	cd /mnt/srcsharedloc/migration/impdp
ssh	sqlplus -s sys/src_sys_password@dbsrc as sysdba <<EOF set heading off feedback off pagesize 0 trimspool on linesize 500 spool xtts_tbs_ro.sql prompt /* =================================== */ prompt /* Make all user tablespaces READ ONLY */ prompt /* =================================== */ select 'ALTER TABLESPACE ' || tablespace_name || ' READ ONLY;' from dba_tablespaces where tablespace_name not in     ('SYSTEM','SYSAUX') and contents = 'PERMANENT' order by 1; spool off EOF
ssh	sqlplus -s sys/src_sys_password@dbsrc as sysdba <<EOF set heading off feedback off pagesize 0 trimspool on linesize 500 spool xtts_tbs_rw.sql prompt /* =================================== */ prompt /* Make all user tablespaces READ WRITE */ prompt /* =================================== */ select 'ALTER TABLESPACE ' || tablespace_name || ' READ WRITE;' from dba_tablespaces where tablespace_name not in     ('SYSTEM','SYSAUX') and contents = 'PERMANENT' order by 1; spool off EOF

1.2.9.    Generate scripts to list tablespaces to migrate

The following commands allow you to create a file listTbs.lst of tablespaces to migrate. After that, you may use sed command to format the file and then allow the privileges to the file to be acceded anywhere.

Server: sourcesrv

User: oracle

ssh	cd /mnt/srcsharedloc/migration/scriptxttconvert
ssh	sqlplus -s sys/src_sys_password@dbsrc as sysdba << EOF set head off set echo off set feed off set term off set trimspool on set pagesize 0 spool listTbs.lst select rtrim( TABLESPACE_NAME ) from dba_tablespaces where  CONTENTS = 'PERMANENT' and (TABLESPACE_NAME not in ('SYSTEM','SYSAUX') ) order by 1; spool off EOF
ssh	sed -i ':a;N;$!ba;s/\n/,/g' listTbs.lst
ssh	chmod 777 listTbs.lst

1.2.10.  Configure xtt.properties

The xtt.properties file contents the parameters that are needed for xttdriver.pl. Edit the xtt.properties file on the source system with your specific configuration. For this procedure, only the following parameter are mandatory. Others are optional and/or available to use.

-   Platformid

-   src_scratch_location

-   dest_scratch_location

-   dest_datafile_location

-   asm_home

-   asm_sid

-   tablespaces

Server: sourcesrv

User: oracle

ssh	cd /mnt/srcsharedloc/migration/scriptxttconvert
ssh	cp xtt.properties xtt.properties_BEFORE
ssh	/usr/bin/awk ' /platformid/{sub("=.*","=6")} /src_scratch_location/{sub("=.*","=/mnt/srcsharedloc/migration/data")} /dest_scratch_location/{sub("=.*","=/mnt/srcsharedloc/migration/data ")} /dest_datafile_location/{sub("=.*","=+DATA")} /asm_home/{sub("=.*","=/u01/app/19.0.0.0/grid")} /asm_sid/{sub("=.*","=+ASM")} /parallel/{sub("=.*","=8")} /rollparallel/{sub("=.*","=8")} /getfileparallel/{sub("=.*","=8")} /^#destconnstr/{sub(".*","destconnstr= sys/dest_sys_password@pdbdest ")} /^#usermantransport/{sub(".*","usermantransport=1")} BEGIN{getline tbs < "listTbs.lst"}/tablespace/{gsub("=.*","="tbs)} {print}' < xtt.properties_AVANT > xtt.properties

1.2.11.  Generate metadata import parameter file

The below script will generate a parameter file impdpovernetwork_metadata.par that is used to import metadata (CONTENT=METADATA) from source to destination. In this script, we excluded statistics, tablespaces and some schemas. So you can customize relating to your environment. The metadata import will be done using a database link.

Server: sourcesrv

User: oracle

ssh

cd /mnt/srcsharedloc/migration/impdp

ssh

sqlplus -s sys/src_sys_password@dbsrc as sysdba <<EOF set heading off feedback off trimspool on linesize 5000 set serveroutput on size 1000000 spool impdpovernetwork_metadata.par declare   auser varchar2(4000);   i number := 0; begin       dbms_output.put_line('FULL=Y');       dbms_output.put_line('DIRECTORY=DATA_PUMP_DIR');       dbms_output.put_line('LOGFILE=impdpovernetwork_metadata.log');       dbms_output.put_line('NETWORK_LINK=ttslink');     dbms_output.put_line('PARALLEL=8');     dbms_output.put_line('LOGTIME=ALL');     dbms_output.put_line('METRICS=YES');     dbms_output.put_line('CONTENT=METADATA_ONLY');     dbms_output.put_line('EXCLUDE=STATISTICS');     dbms_output.put_line('EXCLUDE=TABLESPACE');     for u in             (select name luser         from system.logstdby\$skip_support         where action=0         union         select username luser from dba_users where username like 'APEX%'         or username like 'FLOWS%'         or username like 'ORDS%'         or username like 'SQLTXPLAIN'         order by 1)       loop             if (i=0) then                   auser := ''''||u.luser||'''';             else            auser := auser||','''||u.luser||'''';             end if;             i := 1;         end loop;       dbms_output.put_line('EXCLUDE=SCHEMA:"IN('||auser||')"');       dbms_output.put_line(''); end; / EOF

1.2.12.  Generate tablespace import parameter file

The below script will generate a parameter file impdpovernetwork_xtts.par that is used to import tablespaces from source to destination. We will use transport_full_check=yes to specify whether to check for dependencies between the objects inside the transportable tablespace set and those outside the transportable set.

Server: sourcesrv

User: oracle

ssh

cd /mnt/srcsharedloc/migration/impdp

ssh

sqlplus -s sys/src_sys_password@dbsrc as sysdba <<EOF set heading off feedback off trimspool on linesize 500 set serveroutput on size 1000000 spool impdpovernetwork_xtts.par declare   fname varchar(2000);   i number := 0; begin       dbms_output.put_line('DIRECTORY=DATA_PUMP_DIR');       dbms_output.put_line('LOGFILE=impdpovernetwork_xtts.log');       dbms_output.put_line('NETWORK_LINK=ttslink');     dbms_output.put_line('EXCLUDE=STATISTICS');     dbms_output.put_line('LOGTIME=ALL');     dbms_output.put_line('METRICS=YES');     dbms_output.put_line('transport_full_check=yes');     dbms_output.put('transport_tablespaces=');       for df in             (select a.tablespace_name tbs         from dba_tablespaces a         where         a.tablespace_name not in ('SYSTEM','SYSAUX')                and a.contents = 'PERMANENT'              order by a.tablespace_name)       loop             if (i!=0) then                   dbms_output.put_line(''''||fname||''',');             end if;             i := 1;             fname := df.tbs;       end loop;       dbms_output.put_line(''''||fname||'''');       dbms_output.put('transport_datafiles=');       dbms_output.put_line(''); end; / EOF

1.2.13.   Enable Block Change Tracking

Enable Block Change Tracking to allow RMAN (Recovery Manager) to check every data block in all datafiles and compares its scn (system change number) with the scn value that is the incremental level 0 backup. Run the following script:

Server: sourcesrv

User: oracle

ssh	sqlplus -s sys/src_sys_password@dbsrc as sysdba <<EOF alter database enable block change tracking / EOF

1.2.14.  Create profile and user for database link

Create a profile and user used by database link during the import.

Server: sourcesrv

User: oracle

ssh	sqlplus sys/src_sys_password@dbsrc as sysdba
sql	create profile "impcloud" limit     composite_limit unlimited     sessions_per_user unlimited     cpu_per_session unlimited     cpu_per_call unlimited     logical_reads_per_session unlimited     logical_reads_per_call unlimited     idle_time unlimited     connect_time unlimited     private_sga unlimited     failed_login_attempts 10     password_life_time unlimited     password_reuse_time unlimited     password_reuse_max unlimited     password_verify_function null     password_lock_time unlimited     password_grace_time 7;
sql	create user src_dp_user_migr identified by src_migr_password profile impcloud default tablespace USERS temporary tablespace TEMP;
sql	grant aq_administrator_role to src_dp_user_migr; grant exp_full_database to src_dp_user_migr; grant imp_full_database to src_dp_user_migr; grant connect to src_dp_user_migr; grant resource" to src_dp_user_migr; grant unlimited tablespace to src_dp_user_migr;

 

1.3.  Prerequisites on destination database

 

1.3.1.    Add source alias in tnsnames.ora

We add a local alias to connect to the local destination database. We will also add an alias to allow destination database to connect to source database directly or by using a database link.

Server: destsrv

User: oracle

ssh	cd $ORACLE_HOME/network/admin/
	printf ' pdbdest =     (DESCRIPTION=       (ADDRESS_LIST=             (ADDRESS=                 (PROTOCOL=TCP)                 (HOST= destsrv or Ip.clouddomain.com)                 (PORT=1521)             )         )         (CONNECT_DATA=             (SERVICE_NAME= pdbdest)         )     ) ' >> tnsnames.ora
	printf ' dbsrc =     (DESCRIPTION=       (ADDRESS_LIST=             (ADDRESS=                 (PROTOCOL=TCP)                 (HOST= sourcesrv.localdomain.com)                 (PORT=1521)             )         )         (CONNECT_DATA=             (SERVICE_NAME=dbsrc )         )     ) ' >> tnsnames.ora

1.3.2.    Create profile and user for database for import

Create a profile and user used to import metadata and data.

Server: destsrv

User: oracle

ssh	sqlplus sys/dest_sys_password@pdbdest as sysdba
sql	create profile impcloud_dest limit     composite_limit unlimited     sessions_per_user unlimited     cpu_per_session unlimited     cpu_per_call unlimited     logical_reads_per_session unlimited     logical_reads_per_call unlimited     idle_time unlimited     connect_time unlimited     private_sga unlimited     failed_login_attempts 10     password_life_time unlimited     password_reuse_time unlimited     password_reuse_max unlimited     password_verify_function null     password_lock_time unlimited     password_grace_time 7;
sql	create user dest_dp_user_migr identified by dest_migr_password profile impcloud default tablespace SYSTEM temporary tablespace TEMP;
sql	grant aq_administrator_role to dest_dp_user_migr; grant imp_full_database to dest_dp_user_migr; grant connect to dest_dp_user_migr; grant resource to dest_dp_user_migr; grant exp_full_database to dest_dp_user_migr; grant unlimited tablespace to dest_dp_user_migr;

1.3.3.    Create public database link for import

The below database link will be used to import metadata and data remotely, and also to cross join destination and source objects in the same sql command.

 

Server: destsrv

User: oracle

ssh	sqlplus sys/dest_sys_password@pdbdest as sysdba
sql	create public database link ttslink connect to src_dp_user_migr identified by src_migr_password using 'dbsrc';

2. Import user Metadata (Destination Database)

 

2.1.  Initial tablespaces

 

2.1.1.    Creation of tablespaces

Create initial tablespaces with a minimum space using a script generated at 1.2.6

Server: destsrv

User: oracle

ssh	cd /mnt/destsharedloc/migration/impdp
ssh	sqlplus sys/dest_sys_password@pdbdest as sysdba
sql	@xtts_create_tbs.sql
sql	exit

2.1.2.    Make Data files in autoextend mode

Server: destsrv

User: oracle

ssh	cd /mnt/destsharedloc/migration/impdp
ssh	sqlplus sys/dest_sys_password@pdbdest as sysdba
sql	set pagesize 0 linesize 1000 feedback off set head off echo off verify off trimspool on spool xtts_alter_tbs_autoextent.sql prompt /* ========================= */ prompt /* mettre les tbs en autoextent */ prompt /* ========================= */ select 'ALTER DATABASE DATAFILE '''||file_name||''' AUTOEXTEND ON;' from dba_data_files where autoextensible='NO'; / spool off
sql	@xtts_alter_tbs_autoextent.sql
sql	exit

2.2.  Import Metadata

 

2.2.1.    Run the import script

The following script will import user metadata using a parameter file defined at 1.2.10. This task must run in background and will generate a log/error file.

Server: destsrv

User: oracle

ssh	cd /mnt/destsharedloc/migration/impdp
ssh	nohup impdp dest_dp_user_migr/dest_migr_password@pdbdest parfile=impdpovernetwork_metadata.par 1> impdpovernetwork_metadata.out 2> impdpovernetwork_metadata.err  &

2.2.2.    Check the log/error file

After the import completed, you can fix errors in impdpovernetwork_metadata.err file. You can exclude some common errors.

Server: destsrv

User: oracle

ssh	cd /mnt/destsharedloc/migration/impdp
ssh	grep ORA- impdpovernetwork_metadata.err|grep -v ORA-31684|grep -v ORA-39082|grep -v ORA-31625|grep -v ORA-01435|less

3. Post Import Metadata (Destination Database)

Now, you may list all the missing user objects or the wrong objects status on the destination database. This task allow you to manually fix some issues. You can use graphical tool like SQL Developer to have the formatted output. You can connect on destination database using the user created for import.

3.1.  Compare Objects

sql developer   Note: You may adjust the script according to your environment

select owner,object_name, object_type from dba_objects@ttslink where owner not in (select name luser         from system.logstdby$skip_support@ttslink         where action=0         union         select username luser from dba_users@ttslink where username like 'APEX%'         or username like 'FLOWS%'         or username like 'ORDS%')   and object_type not in ('JAVA CLASS','JAVA RESOURCE','LOB') and object_name not like ('SYS_%') and object_name not like ('/%') and dba_objects.oracle_maintained = 'N' minus (select owner,object_name, object_type from dba_objects where owner not in (select name luser         from system.logstdby$skip_support@ttslink         where action=0         union         select username luser from dba_users@ttslink where username like 'APEX%'         or username like 'FLOWS%'         or username like 'ORDS%')   union select owner,table_name, 'TABLE' from dba_external_tables@ttslink) order by 3,1,2;

3.2.  Compare Privileges

sql developer   Note: You may adjust the script according to your environment

select grantee, owner,table_name,grantor,privilege from dba_tab_privs@ttslink where grantor not in (select name luser         from system.logstdby$skip_support@ttslink         where action=0         union         select username luser from dba_users@ttslink where username like 'APEX%'         or username like 'FLOWS%'         or username like 'ORDS%')  and table_name not like 'SYS_PLSQL%'        minus select grantee, owner,table_name,grantor,privilege from dba_tab_privs;

4. Roll forward (xtts incremental backup and restore)

During this phase, an incremental backup is created from the source database in the shared directories. The necessary privileges are granted to the backup files to be acceded from the destination system. This phase may be run multiple times. Each successive incremental backup should take less time than the prior incremental backup, and bring the destination datafile copies more current with the source database.

4.1.  Backup phase (Source database)

On the source system, logged in as the oracle user with the environment variables (ORACLE_HOME and ORACLE_BASE) pointing to the source database, run the following commands:

Server: sourcesrv

User: oracle

ssh	export TMPDIR=/mnt/srcsharedloc/migration/
ssh	export NLS_DATE_FORMAT='YYYY-MM-DD-HH24MISS'
ssh	cd /mnt/srcsharedloc/migration/scriptxttconvert
ssh	$ORACLE_HOME/perl/bin/perl xttdriver.pl --backup --debug 3
ssh	chmod 777 /mnt/srcsharedloc/migration/data/*

4.2.  Restore phase (Destination database)

On the destination system, logged in as oracle user with the environment variables (ORACLE_HOME and ORACLE_BASE) pointing to the destination database, run the following commands:

Server: destsrv

User: oracle

ssh	export TMPDIR=/mnt/destsharedloc/migration/
ssh	export NLS_DATE_FORMAT='YYYY-MM-DD-HH24MISS'
ssh	cd /mnt/destsharedloc/migration/scriptxttconvert
ssh	$ORACLE_HOME/perl/bin/perl xttdriver.pl --restore --debug 3

Repeat the roll forward phase (4.1 and 4.2) or proceed to the next phase.

5. Final incremental backup and restore (Big Bang)

During this phase the source database is made READ ONLY and the destination datafiles are made consistent with the source database by creating and applying a final incremental backup.

5.1.  Put the tablespace READ ONLY(Source database)

Run the following scripts on source database to make all the user tablespaces on READ ONLY mode.

Server: sourcesrv

User: oracle

ssh	cd /mnt/srcsharedloc/migration/impdp
ssh   Note: the xtts_tbs_ro.sql has been generated at 1.2.8	sqlplus -s sys/src_sys_password sysdba << EOF set head off set echo off set feed off set term off set trimspool on set pagesize 0 spool xtts_tbs_ro.log @xtts_tbs_ro.sql spool off EOF

You may check in the xtts_tbs_ro.log file to fix some errors. You may also run the following script to check that only SYSTEM and SYSAUX tablespaces remain ONLINE.

Server: sourcesrv

User: oracle

ssh	sqlplus sys/src_sys_password as sysdba
sql	select tablespace_name from dba_tablespaces where status = 'ONLINE';

 

5.2.  Take the final backup (Source database)

Like on the backup on roll forward phase, on the source system, logged in as the oracle user with the environment variables (ORACLE_HOME and ORACLE_BASE) pointing to the source database, run the following commands:

Server: sourcesrv

User: oracle

ssh	export TMPDIR=/mnt/srcsharedloc/migration/
ssh	export NLS_DATE_FORMAT='YYYY-MM-DD-HH24MISS'
ssh	cd /mnt/srcsharedloc/migration/scriptxttconvert
ssh	$ORACLE_HOME/perl/bin/perl xttdriver.pl --backup --debug 3
ssh	chmod 777 /mnt/srcsharedloc/migration/data/*

During this phase, you will have the following warning, as the tablespaces are in READ ONLY mode. This warning can be ignored.

####################################################################

Warning:

------

Warnings found in executing =/mnt/srcsharedloc/migration/backup_May9_Tue_10_

####################################################################

DECLARE*

ERROR at line 1:

ORA-20001: TABLESPACE(S) IS READONLY OR,

OFFLINE JUST CONVERT, COPY

ORA-06512: a ligne 335

TABLESPACE STRING :'UNO05_L01_TBS','USERS'

Prepare newscn for Tablespaces: 'XXXXX','YYYYYY'

DECLARE*

ERROR at line 1:

ORA-20001: TABLESPACE(S) IS READONLY OR,

OFFLINE JUST CONVERT, COPY

ORA-06512: a ligne 285"

5.3.  Run the final restore (Destination database)

Like on the restore on forward phase, on the destination system, logged in as oracle user with the environment variables (ORACLE_HOME and ORACLE_BASE) pointing to the destination database, run the following commands:

Server: destsrv

User: oracle

ssh	export TMPDIR=/mnt/destsharedloc/migration/
ssh	export NLS_DATE_FORMAT='YYYY-MM-DD-HH24MISS'
ssh	cd /mnt/destsharedloc/migration/scriptxttconvert
ssh	$ORACLE_HOME/perl/bin/perl xttdriver.pl --restore --debug 3

6. Transport tablespaces (Destination database)

During this phase the metadata of tablespaces will be transported from the source database and plugged to the destination database using transportable mode Datapump import. But first, we need to clean the destinations metadata of tablespaces.

6.1.  Drop initial tablespaces

Now, it’s time to drop all the initial tablespaces created on 1.2.6 by using the script generated on 1.2.7.

Server: destsrv

User: oracle

ssh	cd /mnt/destsharedloc/migration/impdp
ssh	sqlplus sys/dest_sys_password@pdbdest as sysdba
sql	@xtts_drop_tbs.sql

In this step, it’s possible to have the following error for some tablespaces:

DROP TABLESPACE TBSXXXX INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS

*

ERREUR a la ligne 1 :

ORA-29857: domain indexes and/or secondary objects exist in the tablespace

In this case, you will find the blocking indexes by running the following sql scripts:

Server: destsrv

User: oracle

ssh	sqlplus sys/dest_sys_password@pdbdest as sysdba
sql	select index_name,i.table_name from dba_indexes i, dba_tables t where t.table_name=i.table_name and t.owner=i.owner and i.index_type='DOMAIN' and t.tablespace_name='TBSXXXX';

 

Drop those indexes and then manually drop the related tablespaces.

 

Server: destsrv

User: oracle

ssh	sqlplus sys/dest_sys_password@pdbdest as sysdba
sql	DROP TABLESPACE TBXXXX INCLUDING CONTENTS AND DATAFILES;

6.2.  Update the parameter file

The parameter file generated on 1.2.12 is looking like this:

DIRECTORY=DATA_PUMP_DIR

LOGFILE=impdpovernetwork_xtts.log

NETWORK_LINK=ttslink

EXCLUDE=STATISTICS

LOGTIME=ALL

METRICS=YES

transport_full_check=yes

transport_tablespaces='TBS01',

'TBS02',

...,

...

transport_datafiles=

This file must be updated by the datafiles list generated on 5.3 by the final restore phase. The datafiles are located on +DATA directories.

ssh	sudo su - grid
ssh	cd /mnt/destsharedloc/migration/impdp
ssh	asmcmd ls -l data|grep dbf |grep |awk '{print $10}'|sed 's/^/'\''/g'|sed 's/$/'\','/g'| sed '$ s/,$//g' > listDatafiles.txt
ssh	cat listDatafiles.txt >> impdpovernetwork_xtts.par
ssh	exit

6.3.  Import metadata of tablespaces

The following script will import metadata of tablespace using a parameter file defined at 1.2.12 and updated at 6.2. This task must run in background and will generate a log/error file.

Server: destsrv

User: oracle

ssh	cd /mnt/destsharedloc/migration/impdp
ssh	nohup impdp dest_dp_user_migr/dest_migr_password@pdbdest parfile=impdpovernetwork_xtts.par 1> impdpovernetwork_xtts.out 2> impdpovernetwork_xtts.err &

6.4.  Check the log/error file

After the import completed, you can fix errors in impdpovernetwork_xtts.err file. You can exclude some common errors.

Server: destsrv

User: oracle

ssh	cd /mnt/destsharedloc/migration/impdp
ssh	grep ORA- impdpovernetwork_xtts.err|grep -v ORA-31684|grep -v ORA-39082|grep -v ORA-31625|grep -v ORA-01435|less

6.5.  Make tablespaces READ WRITE

As the tablespaces are imported in READ ONLY mode, run the following scripts on destination database to make all the user tablespaces on READ WRITE mode. After that, you should check that there is no error and all the tablespaces are in READ WRITE mode.

Server: destsrv

User: oracle

 

ssh	cd /mnt/destsharedloc/migration/impdp
ssh	sqlplus -s  sys/dest_sys_password@pdbdest as sysdba << EOF set head off set echo off set feed off set term off set trimspool on set pagesize 0 spool xtts_tbs_rw_cloud.log @xtts_tbs_rw.sql spool off EOF
ssh	sqlplus / as sysdba
sql	select tablespace_name from dba_tablespaces where status != 'ONLINE';

 

Note: This last script must not return any tablespace.

7. Update sequences

Optionally, you may update the sequences information to be aligned with the source database. Run the following script to collect sequences information from the source database and allow to drop and recreate the sequences and also grant the permissions.

Server: destsrv

User: oracle

ssh	cd /mnt/destsharedloc/migration/impdp
ssh	sqlplus -s sys/src_sys_password@dbsrc as sysdba <<EOF set echo off heading off feedback off trimspool on escape off long 1000 linesize 1000 pagesize 0 col SEQDDL format A300 spool recreate_seq.sql prompt /* ========================= */ prompt /* Drop and create sequences */ prompt /* ========================= */ select regexp_replace( dbms_metadata.get_ddl('SEQUENCE',sequence_name,sequence_owner), '^.*(CREATE SEQUENCE.*).*$', 'DROP SEQUENCE "'||sequence_owner||'"."'||sequence_name ||'";'||chr(10)||'\1;',1,0,'m') SEQDDL from dba_sequences where sequence_owner not in     (select name      from system.logstdby\$skip_support      where action=0      union      select username luser      from dba_users      where username like 'APEX%'      or username like 'ORDS%') order by sequence_owner,sequence_name; spool off EOF
ssh	sqlplus -s sys/src_sys_password@dbsrc as sysdba <<EOF set echo off heading off feedback off trimspool on escape off long 1000 linesize 1000 pagesize 0 col SEQDDL format A300 spool recreate_seq_grant.sql prompt /* ========================= */ prompt /* Recréer les grants des sequences */ prompt /* ========================= */ select 'GRANT SELECT ON "'||grantor||'"."'||table_name||'" TO "'||grantee||'" '||decode(grantable,'YES','WITH GRANT OPTION;',';') grt from dba_tab_privs      where table_name in     (select sequence_name      from dba_sequences      where sequence_owner not in (          select name      from system.logstdby\$skip_support      where action=0      union      select username luser      from dba_users      where username like 'APEX%'      or username like 'ORDS%')) order by grantor; spool off EOF
ssh	exit
ssh	sed -i '/NOPARTITION/ s///'  recreate_seq.sql
ssh	sqlplus sys/dest_sys_password@pdbdest as sysdba
sql	@recreate_seq.sql
sql	@recreate_seq_grant.sql
sql	exit

8. Post Import Tablespaces (Destination Database)

The last things to do are to restart the destination database, cleanup, gather statistics, recompile procedural objects etc. Other additional actions can be done depending to your database.

Server: destsrv

User: oracle

ssh	sqlplus sys/dest_sys_password@pdbdest as sysdba
sql	alter pluggable database close immediate;
sql	alter pluggable database open;
sql	@?/rdbms/admin/utlrp.sql
sql	exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS();

 

Server: destsrv

User: grid

ssh	asmcmd ls data|grep dbf|sed 's/^/asmcmd rmalias data\//g' > rmalias.sh
ssh	sh rmalias.sh
ssh	asmcmd ls data
ssh	rm rmalias.sh