Reduce Transportable Tablespace Downtime using Cross Platform Incremental Backup 11gR2 to 19c – Non CDB

Assumption

Source: – Oracle Database version: – 11.2.0.3 Operating system version: – Little Endian Target: – Oracle Database version: – 19.20.0 Operating system version: – Little Endian

Phase 1 – Initial Setup

1.1 –Verify the source and install the destination Oracle database software

Source – Oracle Database Version – 11.2.0.3.0

Target – Oracle Database Version – 19.20.0.0.0 (Non-CDB)

Verify Endian Format: –

Source Oracle Database

Target Oracle Database

set lines 200; col platform_name for a35 select * from v$transportable_platform order by platform_id; SELECT tp.platform_id,substr(d.PLATFORM_NAME,1,30), ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

In Source Oracle Database

1.2 Identify Tablespaces to be Transported from Source to Target Oracle Database

 Tablespaces Identified = APPS, FLASH_DATA_ARCHIVE, RECOVERY

select tablespace_name, block_size from dba_tablespaces where tablespace_name not in (‘SYSTEM’,’SYSAUX’,’USERS’) and contents = ‘PERMANENT’;

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 where action=0);

1.4 Run the script for the DBMS_TTS.TRANSPORT_SET_CHECK function to perform the self-containment also check for the list of tablespaces to be transported.

declare checklist varchar2(4000); 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; / select * from transport_set_violations;

1.5: – From Oracle document download and install xttconvert scripts in the source system

mkdir -p /u001/MIGRATION/SOURCE/XTTS

In Target Oracle Database also create directory to keep xttconvert scripts

mkdir -p /db/MIGRATION/TARGET/XTTS

1.6 :-  In both source and Target Oracle Database create necessary directories structure

Source Oracle Database

mkdir -p /u001/SOURCE/XTTS

For e.g src_scratch_location = /u001/SOURCE/XTTS

Target Oracle Database

mkdir -p /db/TARGET/XTTS

For e.g dest_scratch_location =  /db/TARGET/XTTS

Also given location is retrieved from Target Oracle Database data file location

For e.g dest_datafile_location = /oem/db/app/oracle/19c/oradata/PRODB/datafile/

Note: –

• Location for full and incremental backups location in Target Oracle Database as defined by dest_scratch_location parameter in the xtt.properties file
• Location for full and incremental backups in source Oracle Database location as defined by src_scratch_location parameter in the xtt.properties file
• Location of data files from Oracle Database Target as defined by dest_datafile_location parameter in the xtt.properties file

1.6:-  Configure xtt.properties on the source system

src_scratch_location = /u001/SOURCE/XTTS dest_scratch_location =  /db/TARGET/XTTS dest_datafile_location = /oem/db/app/oracle/19c/oradata/PRODB/datafile

1.7 – Copy xttconvert scripts and xtt.properties to the destination system

And verify

1.8 – Set TMPDIR environment variable – create env variable along with TMPDIR

Source Oracle Database

Target Oracle Database

Note: –

• In the shell environment on both source and destination systems, set environment variable TMPDIR to the location where the supporting scripts exist.
• Use this shell to run the Perl script xttdriver.pl. If TMPDIR is not set, output files are created in and input files are expected to be in /tmp

Phase 2 – Prepare Phase

2.1:-  Execute the scripts to take initial or level 0 backup on the source system

For initial or level 0 backup I have created given data for user DEMOAPPS with default tablespace as APPS

cd /u001/MIGRATION/SOURCE/XTTS

$ORACLE_HOME/perl/bin/perl xttdriver.pl –backup –debug 3

2.2 – Transfer the backup taken to the target Oracle Database

And verify

And also transfer res.txt from source to target

2.3 – Restore the datafiles on the Target Oracle Database system

cd /db/MIGRATION/TARGET/XTTS

$ORACLE_HOME/perl/bin/perl xttdriver.pl –restore –debug 3

Note:-

• Till this step we have taken initial or level 0 backup at source and restored at target Oracle Database.
• Tablespaces will be restored at the destination defined by dest_datafile_location

Phase 3 – Roll Forward Phase

During this phase an incremental backup is created from the source database, transferred to the destination system, converted to the destination system endian format, then applied to the converted destination datafile copies to roll them forward. This phase may be run multiple times. Each successive incremental backup should take less time than the prior incremental backup, and will bring the destination datafile copies more current with the source database. The data being transported (source) is fully accessible during this phase.

Note: –

• The script will shut down and startup, in NOMOUNT, the destination database before the restore.
• For consecutive Incremental back up clean up directory at both source and destination i.e

               src_scratch_location = /u001/SOURCE/XTTS

               dest_scratch_location =  /db/TARGET/XTTS

3.1 – Create an incremental backup of the tablespaces being transported on the source system.

Note: –

• For Incremental backup I have created data as given in source database

$ORACLE_HOME/perl/bin/perl xttdriver.pl –backup –debug 3

And verify src_scratch_location = /u001/SOURCE/XTTS

3.2 – Transfer incremental backups and res.txt to the destination system

Transfer the incremental backup(s) between src_scratch_location and dest_scratch_location) and the res.txt The list of incremental backup files from current backup can be checked in the incrbackups.txt file on the source system

Note: –

• While tranfering give individual file name as above i.e specify individual file name
• For files verify at the destination dest_scratch_location =  /db/TARGET/XTTS

Also copy res.txt from source to target

3.3 – Apply the incremental backup to the datafile copies on the destination system

===================================================

Note 3.4 – Repeat the roll forward phase 3 (3.1 – 3.3) or proceed to phase 4, final incremental backup

==================================================

Phase 4 – Final Incremental Backup

During this phase the source data is made READ ONLY and the destination datafiles are made consistent with the source database by creating and applying a final incremental backup. After the destination datafiles are made consistent, the normal transportable tablespace steps are performed to export object metadata from the source database and import it into the destination database. The data being transported is accessible only in READ ONLY mode until the end of this phase.

For the final incremental backup, I have created data as given.

4.1 Alter source tablespace(s) to READ ONLY in the source database

4.2 Create the final incremental backup of the tablespaces being transported on the source system

Note: – Make sure at both source and destination directories are cleaned up before taking Incremental backup and transfer i.e.

src_scratch_location = /u001/SOURCE/XTTS

dest_scratch_location =  /db/TARGET/XTTS

4.3 Transfer incremental backups, incrbackups.txt and res.txt to the destination system

Transfer res.txt and incrbackups.txt

Transfer backup from source to target Oracle Database

Verify at the Target

4.4 Apply last incremental backup to destination datafiles

cd /db/MIGRATION/TARGET/XTTS $ORACLE_HOME/perl/bin/perl xttdriver.pl –restore –debug 3

Phase 5 – Transport Phase: Export Metadata and Plug-in Tablespaces into Destination Database

Prerequisite: –

Make sure the destination database has the necessary objects to allow the import to succeed. This includes pre-creating the owners of the tables in the tablespace being plugged in.

Note: –

• Create users before importing metadata into Target Oracle Database
• And at Target Oracle Database create required users for which Tablespace is being migrated

5.1 Run datapump export on source database

Create par file to export metadata from source Oracle Database

Note: – Make user data pump directory exists

Execute export and review the log files

Transfer the dump from source to target

Also, before importing metadata into Target Oracle Database. Copy all the data files from source data file’s location to the Target data files location mentioned while metadata was exported.

And verify at Target Oracle Database

5.2 Run datapump import in Oracle Target database

Note: – Make sure data pump exist in Target Oracle Database

Execute import and review the log files

Phase 6 – Validate the Transported Data i.e Check tablespaces for corruption

In Target Oracle Database

validate tablespace “RECOVERY”, “FLASH_DATA_ARCHIVE”, “APPS” check logical;

6.1 – Alter the tablespace(s) READ WRITE in the destination database

ALTER TABLESPACE RECOVERY READ WRITE; ALTER TABLESPACE FLASH_DATA_ARCHIVE READ WRITE; ALTER TABLESPACE APPS READ WRITE; select TABLESPACE_NAME,STATUS from dba_tablespaces;

7.. Post Upgrade and Migration

Check Components of Target Oracle Database

column comp_id format A10 column comp_name format A35 column version format A14 select comp_id,comp_name,version from dba_registry;

Check Invalid objects

Note: – In case of INVALID objects then re-compile invalid objects

@$ORACLE_HOME/rdbms/admin/utlrp.sql

Check stale object statistics and stale index statistics

column OWNER format A10 column last_analyzed format A10 column stale_stats format A10 select owner, table_name, last_analyzed, stale_stats from dba_tab_statistics where table_name=’ MIGRATION’ and owner=’DEMOAPPS’;   column OWNER format A10 column last_analyzed format A10 column stale_stats format A10 select owner, table_name, index_name last_analyzed, stale_stats from dba_ind_statistics  where table_name=’MIGRATION’ and owner = ‘DEMOAPPS’;

Gathering schema stats

Given package is used to gather system stats & fixed objects stats when there is a major change on server hardware or major update to the database. exec dbms_stats.gather_system_stats; exec dbms_stats.gather_fixed_objects_stats; Given package is used to gather entire database stats exec dbms_stats.gather_database_stats; Given package is used to gather dictionary statistics exec dbms_stats.gather_dictionary_stats; Given package is used to gather entire schema stats exec dbms_stats.gather_schema_stats(‘DEMOAPPS’); Given package is used to gather index stats exec dbms_stats.gather_index_stats(‘ DEMOAPPS ‘,’INDEX_NAME’); Given package is used to gather stats for a single table exec dbms_stats.gather_table_stats(‘DEMOAPPS’,’ MIGRATION’); exec dbms_stats.gather_table_stats(‘DEMOAPPS’,’ MIGRATION ‘,cascade=>TRUE);

Happy Learning !!!!