Skip to main content
📔
migration guide

Cross Endian Platform Migration using XTTSv4 + FTEX

Introduction​

This guide shows how to use the perl XTTSv4 script to migrate a database cross platform/endian with transportable tablespace and RMAN incremental backups for minimal downtime.
For the migration of the metadata Data Pump Full Transportable Export Import (FTEX) is used.

Objectives​

In this guide we will cover the following objectives in detail:

  • Cross Platform/Endian Migration
  • Migration to higher database release
  • Migration from non-CDB to PDB
  • Convert single instance to RAC
  • Minimal Downtime
  • Encrypt tablespaces in the target database

Prerequisites​

  • Oracle 11.2.0.4 source database running on SPARC Solaris
  • Oracle 19c CDB instance running on Exadata Cloud@Customer
    • PDB target database setup
  • Shared NFS storage mounted on source and target systems

Requirements​

  • COMPATIBLE parameter equal or higher on the target
  • Identical database character set
  • Identical national character set
  • Same or higher time zone version
  • Identical database time zone setting
    • TIMESTAMP WITH LOCAL TIME ZONE columns
  • Oracle software owner has read/write permission on the NFS share
  • NFS Storage: Free disk space to store level 0 + all incremental backups

Restrictions​

Environment Setup​

Overview​

The following table outlines the key components of our demo environment used in this guide. Please note the color code for the source and target environment. All the code elements shown in the guide are marked with a colored left border to indicate on which environment the code needs to be run.

SOURCE
TARGET
PlatformOracle SPARCExadata C@C
Operating SystemSolaris 11.4Oracle Linux 8
DB Version11.2.0.419.22
Multitenantnon-CDBPDB
High AvailabilitySingle Instance2 node RAC
Disaster Recovery--
Storage LocationASMASM
DB NameSRC11GTGT19C
PDB-TGTPDB

The following table lists different parameters and settings for the source and target database. The Gather Database Info in the source and target database chapter shows in detail how to gather the information:

SRC11G
TGTPDB
Platform ID213
TablespacesUSERS,EXAMPLE,TEST,SOE
Timezone File Version1442
DB Timezone+00:00-07:00
CharactersetWE8MSWIN1252WE8MSWIN1252
National CharactersetAL16UTF16AL16UTF16
TDE EncryptionNoYes

We need to have the same DB timezone on the source and target especially if TIMESTAMP WITH LOCAL TIME ZONE columns are used. The Change dbtimezone section shows how to change the timezone on the target to match the source database value.

Source Database​

Gather Database Info​

  • Show source database information

    select name, open_mode from gv$database;
    NAME      OPEN_MODE
    --------- --------------------
    SRC11G READ WRITE
  • Show platform id:

    select platform_id from V$DATABASE;
    PLATFORM_ID
    -----------
    2

    To show more information including the platform name and endian format use the following query:

    col platform_name format a30;
    SELECT d.platform_id, d.PLATFORM_NAME, ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d WHERE tp.PLATFORM_ID = d.PLATFORM_ID;
    PLATFORM_ID PLATFORM_NAME                  ENDIAN_FORMAT
    ----------- ------------------------------ --------------
    2 Solaris[tm] OE (64-bit) Big
  • Show information about the tablespaces:

    SELECT tablespace_name, status, encrypted FROM dba_tablespaces;
    TABLESPACE_NAME                STATUS    ENC
    ------------------------------ --------- ---
    SYSTEM ONLINE NO
    SYSAUX ONLINE NO
    UNDOTBS1 ONLINE NO
    TEMP ONLINE NO
    USERS ONLINE NO
    EXAMPLE ONLINE NO
    TEST ONLINE NO
    SOE ONLINE NO
  • Show information about the data files:

    SELECT file_name FROM dba_data_files;
    FILE_NAME
    -------------------------------------------------
    +DATA/src11g/datafile/system.260.1158601147
    +DATA/src11g/datafile/sysaux.261.1158601149
    +DATA/src11g/datafile/undotbs1.262.1158601149
    +DATA/src11g/datafile/users.264.1158601157
    +DATA/src11g/datafile/example.266.1161516935
    +DATA/src11g/datafile/test.267.1161590723
    +DATA/src11g/datafile/soe.268.1161972789
  • Check Transportable Data Set:

    exec sys.dbms_tts.transport_set_check ('EXAMPLE,SOE,TEST,USERS',TRUE);
    SELECT * FROM TRANSPORT_SET_VIOLATIONS;
  • Check timezone version:

    select * from gv$timezone_file;
    FILENAME                VERSION
    -------------------- ----------
    timezlrg_14.dat 14
  • Check dbtimezone:

    select dbtimezone from dual;
    DBTIME
    ------
    +00:00
  • Check character set:

    Source:
    SQL> col property_name format a25
    SQL> col property_value format a25
    SQL> select property_name, property_value from database_properties where property_name in ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET') order by 1;
    PROPERTY_NAME             PROPERTY_VALUE
    ------------------------- -------------------------
    NLS_CHARACTERSET WE8MSWIN1252
    NLS_NCHAR_CHARACTERSET AL16UTF16
  • Check invalid objects:

    select count(1) from dba_objects where status!='VALID';
    COUNT(1)
    ----------
    0
  • Check if there are any external tables, directories or BFILES:

    set serveroutput on;
    DECLARE
    external BOOLEAN;
    BEGIN
    external := DBMS_TDB.CHECK_EXTERNAL;
    END;
    /

    Review the output for the external tables and directories. They need to be migrated manually to the target database.

Enable Block Change Tracking​

info

Block Change Tracking is not available in Standard Edition 2 and Oracle Base Database Service Standard Edition.
Features and Licensing Information

To speed up the incremental backups we enable block change tracking on the source database.

  • Enable (use default file and location):

    alter database enable block change tracking;
  • As an alternative you can also specify a file that will be used:

    ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/<path>/bct.ora';
  • Check status if block change tracking is enabled

    select status, filename from v$block_change_tracking;
    STATUS     FILENAME
    ---------- --------------------------------------------------
    ENABLED /nfsbackup/block_change_TESTDB.log

Target Database​

Gather Database Info​

  • Show source database information

    select name, open_mode, cdb from gv$database;
    NAME      OPEN_MODE            CDB
    --------- -------------------- ---
    TGT19C READ WRITE YES
    TGT19C READ WRITE YES
  • Show the platform id:

    col platform_name format a30;
    SELECT d.platform_id, d.PLATFORM_NAME, ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d WHERE tp.PLATFORM_ID = d.PLATFORM_ID;
    PLATFORM_ID PLATFORM_NAME                  ENDIAN_FORMAT
    ----------- ------------------------------ --------------
    13 Linux x86 64-bit Little
  • Show information about the tablespaces:

    select tablespace_name, status, encrypted from dba_tablespaces;
    TABLESPACE_NAME                STATUS    ENC
    ------------------------------ --------- ---
    SYSTEM ONLINE YES
    SYSAUX ONLINE YES
    UNDOTBS1 ONLINE YES
    TEMP ONLINE YES
    USERS ONLINE YES
    UNDOTBS2 ONLINE YES
  • Show information about the data files:

    select file_name from dba_data_files;
    FILE_NAME
    --------------------------------------------------------------------------------
    +DATAC2/TGT19C/DATAFILE/system.417.1166485039
    +DATAC2/TGT19C/DATAFILE/sysaux.337.1166485017
    +DATAC2/TGT19C/DATAFILE/undotbs1.398.1166485057
    +DATAC2/TGT19C/DATAFILE/users.399.1166485075
    +DATAC2/TGT19C/DATAFILE/undotbs2.335.1166485017
  • Check the file destination:

    show parameter db_create_file_dest
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    db_create_file_dest string +DATAC2
  • Check timezone version:

    select * from gv$timezone_file;
    FILENAME                VERSION     CON_ID
    -------------------- ---------- ----------
    timezlrg_42.dat 42 0
    timezlrg_42.dat 42 0
  • Check dbtimezone in PDB:

    show pdbs;

    CON_ID CON_NAME OPEN MODE RESTRICTED
    ---------- ------------------------------ ---------- ----------
    2 PDB$SEED READ ONLY NO
    3 PDB1 READ WRITE NO
    6 TGTPDB READ WRITE NO
    alter session set container=TGTPDB;
    select dbtimezone from dual;
    DBTIME
    ------
    -07:00

    We have a different dbtimezone setting compared to the source database. We will change this in Change dbtimezone.

  • Check character set:

    Target CDB:
    SQL> select property_name, property_value from database_properties where property_name in ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET') order by 1;
    PROPERTY_NAME             PROPERTY_VALUE
    ------------------------- -------------------------
    NLS_CHARACTERSET AL32UTF8
    NLS_NCHAR_CHARACTERSET AL16UTF16
    SQL> alter session set container=TGTPDB;

    Session altered.
    SQL> select property_name, property_value from database_properties where property_name in ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET') order by 1;
    PROPERTY_NAME             PROPERTY_VALUE
    ------------------------- -------------------------
    NLS_CHARACTERSET WE8MSWIN1252
    NLS_NCHAR_CHARACTERSET AL16UTF16
    note

    In the above output we are using a different characterset in the PDB compared to the character set of the CDB (AL32UTF8). In general Oracle recommends to use AL32UTF8 as the character set for the CDB. It's possible to create PDBs with different character sets in the CDB.

Install Data Pump Bundle Patch​

Change dbtimezone​

  • Change dbtimezone to match source database:

    alter database set time_zone='+00:00';
    note

    After updating the dbtimezone, ensure you restart the PDB. This step is important for the changes to become effective.

Shared NFS​

  • Create a directory on the NFS share to hold the backup of the source database. The directory needs to be read/writeable for the oracle user on the source and target.

    mkdir -p /nfsbackup/xtt
    chmod 777 /nfsbackup/xtt

Perl Scripts​

  • Download rman_xttconvert_VER4.3.zip from Doc ID 2471245.1

  • Install rman_xttconvert_VER4.3.zip script on the source as oracle software owner:

  • Create a new directory for the xtts scripts:

    mkdir $HOME/xtt
  • Copy the file into $HOME/xtt:

    cp -p rman_xttconvert_VER4.3.zip $HOME/xtt
  • Unzip the file:

    cd $HOME/xtt
    unzip rman_xttconvert_VER4.3.zip
    Archive:  rman_xttconvert_VER4.3.zip
    inflating: xtt.newproperties
    inflating: xtt.properties
    inflating: xttcnvrtbkupdest.sql
    inflating: xttdbopen.sql
    inflating: xttdriver.pl
    inflating: xttprep.tmpl
    extracting: xttstartupnomount.sql

Migration​

1. Edit xtt.properties​

  • Modify the xtt.properties file on the source:

At a minimum the following parameters need to be set. There are some other parameters which are described in the properties file.

cat xtt.properties | egrep -v '^#|^$'
#mandatory parameters
tablespaces=EXAMPLE,TEST,USERS,SOE
platformid=2
src_scratch_location=/nfsbackup/xtt
dest_datafile_location=+DATAC2
dest_scratch_location=/nfsbackup/xtt

#optional parameters
parallel=4
rollparallel=2
getfileparallel=4
info

usermantransport=1 should be set if the source db is 12c or higher and should be never set different between the source and destination.

2. Copy files to target​

  • Create directory on target server:

    mkdir -p $HOME/xtt
    cd $HOME/xtt
  • Copy the file with scpto the target:

    scp -rp $HOME/xtt oracle@dest:/home/oracle/xtt

If there is no scp connection between the server you can also use the shared nfs storage for sharing files between the servers. We will use this approach in this guide:

  • Create a directory to share the files under xtt on the nfs share and copy the files to that directory:

    mkdir -p /nfsbackup/xtt/copy
    cp -rp $HOME/xtt /nfsbackup/xtt/copy/
  • On the target copy the files from the shared directory to the local directory on $HOME:

    # on target
    cp -p /nfsbackup/xtt/copy/xtt/* $HOME/xtt
    ls -la $HOME/xtt

3. Start backup on source​

note

Before you run any xtt perl command make always sure that TMPDIR is set. In this guide we will export TMPDIR before every command. Additionally make sure that ORACLE_HOME and ORACLE_SID are set correctly.

export TMPDIR=$HOME/xtt
info

You can enable additional debug information by setting the XTTDEBUG=1 environment variable before running the xttdriver.pl script or adding the --debug [1/2/3] flag to the command. (Level 3 generates the most information)

cd $HOME/xtt
export TMPDIR=$HOME/xtt
nohup $ORACLE_HOME/perl/bin/perl xttdriver.pl --backup --debug 3 > lev0.out 2>&1 &
Output
OUTPUT:

============================================================
trace file is /export/home/oracle/xtt/backup_Apr22_Mon_16_48_10_97//Apr22_Mon_16_48_10_97_.log
=============================================================

--------------------------------------------------------------------
Parsing properties
--------------------------------------------------------------------

Key: platformid
Values: 2
Key: src_scratch_location
Values: /nfsbackup/xtt
Key: parallel
Values: 4
Key: rollparallel
Values: 2
Key: dest_scratch_location
Values: /nfsbackup/xtt
Key: dest_datafile_location
Values: +DATAC2
Key: tablespaces
Values: EXAMPLE,TEST,USERS,SOE
Key: getfileparallel
Values: 4

--------------------------------------------------------------------
Done parsing properties
--------------------------------------------------------------------


--------------------------------------------------------------------
Checking properties
--------------------------------------------------------------------

ARGUMENT tablespaces
ARGUMENT platformid
ARGUMENT backupformat

--------------------------------------------------------------------
Done checking properties
--------------------------------------------------------------------

ORACLE_SID : SRC11G
ORACLE_HOME : /u01/app/oracle/product/11.2.0/dbhome_1
112040

PRIMARY

Running on PRIMARY

--------------------------------------------------------------------
Starting prepare phase
--------------------------------------------------------------------

Parallel:4
scalar(or4
XXX: adding here for 2, 0, EXAMPLE,TEST,USERS,SOE
XXX: adding proper here for index 0, b4 added 'USERS'
,

XXX: adding proper here for index 0, added 'USERS'

XXX: adding proper here for index 1, b4 added 'SOE'
,

XXX: adding proper here for index 1, added 'SOE'


--------------------------------------------------------------------
Find list of datafiles in system
--------------------------------------------------------------------

sqlplus -L -s / as sysdba @/export/home/oracle/xtt/backup_Apr22_Mon_16_48_10_97//diff.sql +DATAC2
::USERS4,+DATAC2/USERS_4.dbf
::EXAMPLE
5,+DATAC2/EXAMPLE_5.dbf
::TEST
6,+DATAC2/TEST_6.dbf
::SOE
7,+DATAC2/SOE_7.dbf

--------------------------------------------------------------------
Done finding list of datafiles in system
--------------------------------------------------------------------

/ as sysdba
size of tablespace 4
No. of tablespaces per batch 1
TABLESPACE STRING :'EXAMPLE'
Prepare source for Tablespaces:
'EXAMPLE' /nfsbackup/xtt
xttpreparesrc.sql for 'EXAMPLE' started at Mon Apr 22 16:48:10 2024
#PLAN:EXAMPLE::::11590185
#CONVERT:host 'echo ts::EXAMPLE';
#CONVERT: convert from platform 'Solaris[tm] OE (64-bit)'
#CONVERT: datafile
backup as copy tag 'prepare' datafile
#CONVERT: '/nfsbackup/xtt/EXAMPLE_5.tf'
5
#PLAN:5
#CONVERT: format '+DATAC2/%N_%f.dbf'
#CONVERT: parallelism 4;
format '/nfsbackup/xtt/%N_%f.tf';

xttpreparesrc.sql for ended at Mon Apr 22 16:48:10 2024
#PLAN:EXAMPLE::::11590185#CONVERT:host 'echo ts::EXAMPLE';
#CONVERT: convert from platform 'Solaris[tm] OE (64-bit)'
#CONVERT: datafile
backup as copy tag 'prepare' datafile
#CONVERT: '/nfsbackup/xtt/EXAMPLE_5.tf'
5
#PLAN:5
#CONVERT: format '+DATAC2/%N_%f.dbf'
#CONVERT: parallelism 4;
format '/nfsbackup/xtt/%N_%f.tf';

/export/home/oracle/xtt/backup_Apr22_Mon_16_48_10_97//xttprepare.cmd

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Apr 22 16:48:10 2024

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

RMAN-06005: connected to target database: SRC11G (DBID=107228278)

RMAN> #PLAN:EXAMPLE::::11590185
2> #CONVERT:host 'echo ts::EXAMPLE';
3> #CONVERT: convert from platform 'Solaris[tm] OE (64-bit)'
4> #CONVERT: datafile
5> backup as copy tag 'prepare' datafile
6> #CONVERT: '/nfsbackup/xtt/EXAMPLE_5.tf'
7> 5
8> #PLAN:5
9> #CONVERT: format '+DATAC2/%N_%f.dbf'
10> #CONVERT: parallelism 4;
11> format '/nfsbackup/xtt/%N_%f.tf';
12>
RMAN-03090: Starting backup at 22-APR-24
RMAN-06009: using target database control file instead of recovery catalog
RMAN-08030: allocated channel: ORA_DISK_1
RMAN-08500: channel ORA_DISK_1: SID=65 device type=DISK
RMAN-08580: channel ORA_DISK_1: starting datafile copy
RMAN-08522: input datafile file number=00005 name=+DATA/src11g/datafile/example.266.1161516935
RMAN-08586: output file name=/nfsbackup/xtt/EXAMPLE_5.tf tag=PREPARE RECID=95 STAMP=1166978942
RMAN-08581: channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55
RMAN-03091: Finished backup at 22-APR-24

Recovery Manager complete.

TABLESPACE STRING :'TEST'
Prepare source for Tablespaces:
'TEST' /nfsbackup/xtt
xttpreparesrc.sql for 'TEST' started at Mon Apr 22 16:49:06 2024
#PLAN:TEST::::11590173
#CONVERT:host 'echo ts::TEST';
#CONVERT: convert from platform 'Solaris[tm] OE (64-bit)'
#CONVERT: datafile
backup as copy tag 'prepare' datafile
#CONVERT: '/nfsbackup/xtt/TEST_6.tf'
6
#PLAN:6
#CONVERT: format '+DATAC2/%N_%f.dbf'
#CONVERT: parallelism 4;
format '/nfsbackup/xtt/%N_%f.tf';

xttpreparesrc.sql for ended at Mon Apr 22 16:49:06 2024
#PLAN:TEST::::11590173#CONVERT:host 'echo ts::TEST';
#CONVERT: convert from platform 'Solaris[tm] OE (64-bit)'
#CONVERT: datafile
backup as copy tag 'prepare' datafile
#CONVERT: '/nfsbackup/xtt/TEST_6.tf'
6
#PLAN:6
#CONVERT: format '+DATAC2/%N_%f.dbf'
#CONVERT: parallelism 4;
format '/nfsbackup/xtt/%N_%f.tf';
/export/home/oracle/xtt/backup_Apr22_Mon_16_48_10_97//xttprepare.cmd

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Apr 22 16:49:07 2024

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

RMAN-06005: connected to target database: SRC11G (DBID=107228278)

RMAN> #PLAN:TEST::::11590173
2> #CONVERT:host 'echo ts::TEST';
3> #CONVERT: convert from platform 'Solaris[tm] OE (64-bit)'
4> #CONVERT: datafile
5> backup as copy tag 'prepare' datafile
6> #CONVERT: '/nfsbackup/xtt/TEST_6.tf'
7> 6
8> #PLAN:6
9> #CONVERT: format '+DATAC2/%N_%f.dbf'
10> #CONVERT: parallelism 4;
11> format '/nfsbackup/xtt/%N_%f.tf';
12>
RMAN-03090: Starting backup at 22-APR-24
RMAN-06009: using target database control file instead of recovery catalog
RMAN-08030: allocated channel: ORA_DISK_1
RMAN-08500: channel ORA_DISK_1: SID=65 device type=DISK
RMAN-08580: channel ORA_DISK_1: starting datafile copy
RMAN-08522: input datafile file number=00006 name=+DATA/src11g/datafile/test.267.1161590723
RMAN-08586: output file name=/nfsbackup/xtt/TEST_6.tf tag=PREPARE RECID=96 STAMP=1166978948
RMAN-08581: channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
RMAN-03091: Finished backup at 22-APR-24

Recovery Manager complete.

TABLESPACE STRING :'USERS'
Prepare source for Tablespaces:
'USERS' /nfsbackup/xtt
xttpreparesrc.sql for 'USERS' started at Mon Apr 22 16:49:09 2024
#PLAN:USERS::::11590209
#CONVERT:host 'echo ts::USERS';
#CONVERT: convert from platform 'Solaris[tm] OE (64-bit)'
#CONVERT: datafile
backup as copy tag 'prepare' datafile
#CONVERT: '/nfsbackup/xtt/USERS_4.tf'
4
#PLAN:4
#CONVERT: format '+DATAC2/%N_%f.dbf'
#CONVERT: parallelism 4;
format '/nfsbackup/xtt/%N_%f.tf';

xttpreparesrc.sql for ended at Mon Apr 22 16:49:09 2024
#PLAN:USERS::::11590209#CONVERT:host 'echo ts::USERS';
#CONVERT: convert from platform 'Solaris[tm] OE (64-bit)'
#CONVERT: datafile
backup as copy tag 'prepare' datafile
#CONVERT: '/nfsbackup/xtt/USERS_4.tf'
4
#PLAN:4
#CONVERT: format '+DATAC2/%N_%f.dbf'
#CONVERT: parallelism 4;
format '/nfsbackup/xtt/%N_%f.tf';
/export/home/oracle/xtt/backup_Apr22_Mon_16_48_10_97//xttprepare.cmd

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Apr 22 16:49:09 2024

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

RMAN-06005: connected to target database: SRC11G (DBID=107228278)

RMAN> #PLAN:USERS::::11590209
2> #CONVERT:host 'echo ts::USERS';
3> #CONVERT: convert from platform 'Solaris[tm] OE (64-bit)'
4> #CONVERT: datafile
5> backup as copy tag 'prepare' datafile
6> #CONVERT: '/nfsbackup/xtt/USERS_4.tf'
7> 4
8> #PLAN:4
9> #CONVERT: format '+DATAC2/%N_%f.dbf'
10> #CONVERT: parallelism 4;
11> format '/nfsbackup/xtt/%N_%f.tf';
12>
RMAN-03090: Starting backup at 22-APR-24
RMAN-06009: using target database control file instead of recovery catalog
RMAN-08030: allocated channel: ORA_DISK_1
RMAN-08500: channel ORA_DISK_1: SID=65 device type=DISK
RMAN-08580: channel ORA_DISK_1: starting datafile copy
RMAN-08522: input datafile file number=00004 name=+DATA/src11g/datafile/users.264.1158601157
RMAN-08586: output file name=/nfsbackup/xtt/USERS_4.tf tag=PREPARE RECID=97 STAMP=1166978979
RMAN-08581: channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
RMAN-03091: Finished backup at 22-APR-24

Recovery Manager complete.

TABLESPACE STRING :'SOE'
Prepare source for Tablespaces:
'SOE' /nfsbackup/xtt
xttpreparesrc.sql for 'SOE' started at Mon Apr 22 16:49:45 2024
#PLAN:SOE::::11590197
#CONVERT:host 'echo ts::SOE';
#CONVERT: convert from platform 'Solaris[tm] OE (64-bit)'
#CONVERT: datafile
backup as copy tag 'prepare' datafile
#CONVERT: '/nfsbackup/xtt/SOE_7.tf'
7
#PLAN:7
#CONVERT: format '+DATAC2/%N_%f.dbf'
#CONVERT: parallelism 4;
format '/nfsbackup/xtt/%N_%f.tf';

xttpreparesrc.sql for ended at Mon Apr 22 16:49:46 2024
#PLAN:SOE::::11590197#CONVERT:host 'echo ts::SOE';
#CONVERT: convert from platform 'Solaris[tm] OE (64-bit)'
#CONVERT: datafile
backup as copy tag 'prepare' datafile
#CONVERT: '/nfsbackup/xtt/SOE_7.tf'
7
#PLAN:7
#CONVERT: format '+DATAC2/%N_%f.dbf'
#CONVERT: parallelism 4;
format '/nfsbackup/xtt/%N_%f.tf';
/export/home/oracle/xtt/backup_Apr22_Mon_16_48_10_97//xttprepare.cmd

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Apr 22 16:49:46 2024

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

RMAN-06005: connected to target database: SRC11G (DBID=107228278)

RMAN> #PLAN:SOE::::11590197
2> #CONVERT:host 'echo ts::SOE';
3> #CONVERT: convert from platform 'Solaris[tm] OE (64-bit)'
4> #CONVERT: datafile
5> backup as copy tag 'prepare' datafile
6> #CONVERT: '/nfsbackup/xtt/SOE_7.tf'
7> 7
8> #PLAN:7
9> #CONVERT: format '+DATAC2/%N_%f.dbf'
10> #CONVERT: parallelism 4;
11> format '/nfsbackup/xtt/%N_%f.tf';
12>
RMAN-03090: Starting backup at 22-APR-24
RMAN-06009: using target database control file instead of recovery catalog
RMAN-08030: allocated channel: ORA_DISK_1
RMAN-08500: channel ORA_DISK_1: SID=65 device type=DISK
RMAN-08580: channel ORA_DISK_1: starting datafile copy
RMAN-08522: input datafile file number=00007 name=+DATA/src11g/datafile/soe.268.1161972789
RMAN-08586: output file name=/nfsbackup/xtt/SOE_7.tf tag=PREPARE RECID=98 STAMP=1166979013
RMAN-08581: channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
RMAN-03091: Finished backup at 22-APR-24

Recovery Manager complete.


--------------------------------------------------------------------
Done with prepare phase
--------------------------------------------------------------------

/ as sysdba
size of tablespace 4
No. of tablespaces per batch 1
TABLESPACE STRING :'EXAMPLE'
Prepare newscn for Tablespaces: 'EXAMPLE'

TABLESPACE STRING :'TEST'
Prepare newscn for Tablespaces: 'TEST'

TABLESPACE STRING :'USERS'
Prepare newscn for Tablespaces: 'USERS'

TABLESPACE STRING :'SOE'
Prepare newscn for Tablespaces: 'SOE'

New /export/home/oracle/xtt/xttplan.txt with FROM SCN's generated
scalar(or4
XXX: adding here for 2, 0, EXAMPLE,TEST,USERS,SOE
XXX: adding proper here for index 0, b4 added 'USERS'
,

XXX: adding proper here for index 0, added 'USERS'

XXX: adding proper here for index 1, b4 added 'SOE'
,

XXX: adding proper here for index 1, added 'SOE'

Number of tb arrays is 2

::USERS:::SCN:::115944584=11594458,USERS,+DATAC2/USERS_4.dbf
::EXAMPLE:::SCN:::11594386
5=11594386,EXAMPLE,+DATAC2/EXAMPLE_5.dbf
::TEST:::SCN:::11594431
6=11594431,TEST,+DATAC2/TEST_6.dbf
::SOE:::SCN:::11594496
7=11594496,SOE,+DATAC2/SOE_7.dbf
::USERS:::SCN:::11594458
/export/home/oracle/xtt/newfile.txt: ::USERS:::SCN:::11594458
/export/home/oracle/xtt/newfile.txt: 4=11594458,USERS,+DATAC2/USERS_4.dbf
/export/home/oracle/xtt/newfile.txt: ::EXAMPLE:::SCN:::11594386
/export/home/oracle/xtt/newfile.txt: 5=11594386,EXAMPLE,+DATAC2/EXAMPLE_5.dbf
/export/home/oracle/xtt/newfile.txt: ::TEST:::SCN:::11594431
/export/home/oracle/xtt/newfile.txt: 6=11594431,TEST,+DATAC2/TEST_6.dbf
/export/home/oracle/xtt/newfile.txt: ::SOE:::SCN:::11594496
/export/home/oracle/xtt/newfile.txt: 7=11594496,SOE,+DATAC2/SOE_7.dbf
/export/home/oracle/xtt/backup_Apr22_Mon_16_48_10_97//xttnewdatafiles.txt.added: ::USERS:::SCN:::11594458
/export/home/oracle/xtt/backup_Apr22_Mon_16_48_10_97//xttnewdatafiles.txt.added: 4=11594458,USERS,+DATAC2/USERS_4.dbf
Writing new 4=11594458,USERS,+DATAC2/USERS_4.dbf

Writing1 new 4, +DATAC2/USERS_4.dbf

/export/home/oracle/xtt/backup_Apr22_Mon_16_48_10_97//xttnewdatafiles.txt.added: ::EXAMPLE:::SCN:::11594386
/export/home/oracle/xtt/backup_Apr22_Mon_16_48_10_97//xttnewdatafiles.txt.added: 5=11594386,EXAMPLE,+DATAC2/EXAMPLE_5.dbf
Writing new 5=11594386,EXAMPLE,+DATAC2/EXAMPLE_5.dbf

Writing1 new 5, +DATAC2/EXAMPLE_5.dbf

/export/home/oracle/xtt/backup_Apr22_Mon_16_48_10_97//xttnewdatafiles.txt.added: ::TEST:::SCN:::11594431
/export/home/oracle/xtt/backup_Apr22_Mon_16_48_10_97//xttnewdatafiles.txt.added: 6=11594431,TEST,+DATAC2/TEST_6.dbf
Writing new 6=11594431,TEST,+DATAC2/TEST_6.dbf

Writing1 new 6, +DATAC2/TEST_6.dbf

/export/home/oracle/xtt/backup_Apr22_Mon_16_48_10_97//xttnewdatafiles.txt.added: ::SOE:::SCN:::11594496
/export/home/oracle/xtt/backup_Apr22_Mon_16_48_10_97//xttnewdatafiles.txt.added: 7=11594496,SOE,+DATAC2/SOE_7.dbf
Writing new 7=11594496,SOE,+DATAC2/SOE_7.dbf

Writing1 new 7, +DATAC2/SOE_7.dbf

Added fname here 1:/nfsbackup/xtt/USERS_4.tf
Added fname here 1:/nfsbackup/xtt/EXAMPLE_5.tf
Added fname here 1:/nfsbackup/xtt/TEST_6.tf
Added fname here 1:/nfsbackup/xtt/SOE_7.tf
YYY: ::USERS:::SCN:::11594458

YYY: ::EXAMPLE:::SCN:::11594386

YYY: ::TEST:::SCN:::11594431

YYY: ::SOE:::SCN:::11594496

============================================================
No new datafiles added
=============================================================

Logfile is written to $TMPDIR/backup_[timestamp]/[timestamp].log

After the first backup the perl script created the res.txt file and added information to it:

cat res.txt
#0:::4,2,USERS_4.dbf,0,4455484,0,0,0,USERS,USERS_4.dbf
#0:::5,2,EXAMPLE_5.dbf,0,4455484,0,0,0,EXAMPLE,EXAMPLE_5.dbf
#0:::6,2,TEST_6.dbf,0,4455484,0,0,0,TEST,TEST_6.dbf
#0:::7,2,SOE_7.dbf,0,4455484,0,0,0,SOE,SOE_7.dbf

We need to transfer the res.txt file to the target system.
For this we will use the directory to share files on the NFS storage we created earlier. As an alternative you can also copy the file via scp to the dest_scratch_location on the target system.

  • Copy the file via the nfs share:

    #on source:
    cp -p $HOME/xtt/res.txt /nfsbackup/xtt/copy/
    # on target:
    cp -p /nfsbackup/xtt/copy/res.txt $HOME/xtt

  • Alternative with scp: Copy over the $TMPDIR/res.txt file to the target server $TMPDIR

    scp -p $HOME/xtt/res.txt oracle@dest:$HOME/xtt

4. Restore on the target​

note

Again make sure that TMPDIR is set. In this guide we will export TMPDIR before every command. Additionally make sure that ORACLE_HOME and ORACLE_SID are set correctly.

cd $HOME/xtt
export TMPDIR=$HOME/xtt
nohup $ORACLE_HOME/perl/bin/perl xttdriver.pl --restore --debug 3 >lev0.out 2>&1 &
Output
OUTPUT:

============================================================
trace file is /home/oracle/xtt/restore_Apr22_Mon_16_59_31_576//Apr22_Mon_16_59_31_576_.log
=============================================================

--------------------------------------------------------------------
Parsing properties
--------------------------------------------------------------------

Key: parallel
Values: 4
Key: dest_scratch_location
Values: /nfsbackup/xtt
Key: getfileparallel
Values: 4
Key: src_scratch_location
Values: /nfsbackup/xtt
Key: dest_datafile_location
Values: +DATAC2
Key: rollparallel
Values: 2
Key: tablespaces
Values: EXAMPLE,TEST,USERS,SOE
Key: platformid
Values: 2

--------------------------------------------------------------------
Done parsing properties
--------------------------------------------------------------------


--------------------------------------------------------------------
Checking properties
--------------------------------------------------------------------

ARGUMENT tablespaces
ARGUMENT platformid
ARGUMENT backupformat

--------------------------------------------------------------------
Done checking properties
--------------------------------------------------------------------

ORACLE_SID : TGT19C1
ORACLE_HOME : /u02/app/oracle/product/19.0.0.0/dbhome_1
190000

#0:::4,2,USERS_4.dbf,0,11590209,0,0,0,USERS,USERS_4.dbf

#0:::5,2,EXAMPLE_5.dbf,0,11590185,0,0,0,EXAMPLE,EXAMPLE_5.dbf


--------------------------------------------------------------------
Performing convert for file 4
--------------------------------------------------------------------


--------------------------------------------------------------------
Performing convert for file 5
--------------------------------------------------------------------

/home/oracle/xtt/restore_Apr22_Mon_16_59_31_576//rman_convert_USERS_4.cmd

Recovery Manager: Release 19.0.0.0.0 - Production on Mon Apr 22 16:59:31 2024
Version 19.22.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

RMAN-06005: connected to target database: TGT19C (DBID=2904230085)

RMAN> convert from platform 'Solaris[tm] OE (64-bit)' datafile '/nfsbackup/xtt/USERS_4.tf' format '+DATAC2/USERS_4.dbf' ;
2>
RMAN-03090: Starting conversion at target at 22-APR-24
RMAN-06009: using target database control file instead of recovery catalog
RMAN-08030: allocated channel: ORA_DISK_1
RMAN-08605: channel ORA_DISK_1: SID=54 instance=TGT19C1 device type=DISK
RMAN-08589: channel ORA_DISK_1: starting datafile conversion
RMAN-08506: input file name=/nfsbackup/xtt/USERS_4.tf
RMAN-08588: converted datafile=+DATAC2/users_4.dbf
RMAN-08590: channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:45
RMAN-03091: Finished conversion at target at 22-APR-24

Recovery Manager complete.

#0:::6,2,TEST_6.dbf,0,11590173,0,0,0,TEST,TEST_6.dbf


--------------------------------------------------------------------
Performing convert for file 6
--------------------------------------------------------------------

/home/oracle/xtt/restore_Apr22_Mon_16_59_31_576//rman_convert_TEST_6.cmd

Recovery Manager: Release 19.0.0.0.0 - Production on Mon Apr 22 17:00:24 2024
Version 19.22.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

RMAN-06005: connected to target database: TGT19C (DBID=2904230085)

RMAN> convert from platform 'Solaris[tm] OE (64-bit)' datafile '/nfsbackup/xtt/TEST_6.tf' format '+DATAC2/TEST_6.dbf' ;
2>
RMAN-03090: Starting conversion at target at 22-APR-24
RMAN-06009: using target database control file instead of recovery catalog
RMAN-08030: allocated channel: ORA_DISK_1
RMAN-08605: channel ORA_DISK_1: SID=54 instance=TGT19C1 device type=DISK
RMAN-08589: channel ORA_DISK_1: starting datafile conversion
RMAN-08506: input file name=/nfsbackup/xtt/TEST_6.tf
RMAN-08588: converted datafile=+DATAC2/test_6.dbf
RMAN-08590: channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
RMAN-03091: Finished conversion at target at 22-APR-24

Recovery Manager complete.

#0:::7,2,SOE_7.dbf,0,11590197,0,0,0,SOE,SOE_7.dbf


--------------------------------------------------------------------
Performing convert for file 7
--------------------------------------------------------------------

/home/oracle/xtt/restore_Apr22_Mon_16_59_31_576//rman_convert_EXAMPLE_5.cmd

Recovery Manager: Release 19.0.0.0.0 - Production on Mon Apr 22 16:59:31 2024
Version 19.22.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

RMAN-06005: connected to target database: TGT19C (DBID=2904230085)

RMAN> convert from platform 'Solaris[tm] OE (64-bit)' datafile '/nfsbackup/xtt/EXAMPLE_5.tf' format '+DATAC2/EXAMPLE_5.dbf' ;
2>
RMAN-03090: Starting conversion at target at 22-APR-24
RMAN-06009: using target database control file instead of recovery catalog
RMAN-08030: allocated channel: ORA_DISK_1
RMAN-08605: channel ORA_DISK_1: SID=47 instance=TGT19C1 device type=DISK
RMAN-08589: channel ORA_DISK_1: starting datafile conversion
RMAN-08506: input file name=/nfsbackup/xtt/EXAMPLE_5.tf
RMAN-08588: converted datafile=+DATAC2/example_5.dbf
RMAN-08590: channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:55
RMAN-03091: Finished conversion at target at 22-APR-24

Recovery Manager complete.


/home/oracle/xtt/restore_Apr22_Mon_16_59_31_576//rman_convert_SOE_7.cmd

Recovery Manager: Release 19.0.0.0.0 - Production on Mon Apr 22 17:00:32 2024
Version 19.22.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

RMAN-06005: connected to target database: TGT19C (DBID=2904230085)

RMAN> convert from platform 'Solaris[tm] OE (64-bit)' datafile '/nfsbackup/xtt/SOE_7.tf' format '+DATAC2/SOE_7.dbf' ;
2>
RMAN-03090: Starting conversion at target at 22-APR-24
RMAN-06009: using target database control file instead of recovery catalog
RMAN-08030: allocated channel: ORA_DISK_1
RMAN-08605: channel ORA_DISK_1: SID=69 instance=TGT19C1 device type=DISK
RMAN-08589: channel ORA_DISK_1: starting datafile conversion
RMAN-08506: input file name=/nfsbackup/xtt/SOE_7.tf
RMAN-08588: converted datafile=+DATAC2/soe_7.dbf
RMAN-08590: channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:25
RMAN-03091: Finished conversion at target at 22-APR-24

Recovery Manager complete.

YYY:

We can now login on the target with asm to check the datafiles. At this point the datafiles have only been restored to the ASM directory. They are currently not attached to the database.

#as opc user:
sudo su - grid
asmcmd
cd DATAC2
ls -l
...
DATAFILE HIGH COARSE APR 22 17:00:00 N example_5.dbf => +DATAC2/TGT19C/DATAFILE/EXAMPLE.429.1166979579
DATAFILE HIGH COARSE APR 22 17:00:00 N soe_7.dbf => +DATAC2/TGT19C/DATAFILE/SOE.430.1166979637
DATAFILE HIGH COARSE APR 22 17:00:00 N test_6.dbf => +DATAC2/TGT19C/DATAFILE/TEST.431.1166979631
DATAFILE HIGH COARSE APR 22 17:00:00 N users_4.dbf => +DATAC2/TGT19C/DATAFILE/USERS.292.1166979579
...

We see the 4 files have been created directly under DATAC2. But this is just a link to the datafiles that are stored under the actual DB directory. In that case it's the CDB directory. We will fix this at a later step.

ASMCMD> cd +DATAC2/TGT19C/DATAFILE
ASMCMD> ls -l
Type      Redund  Striped  Time             Sys  Name
...
DATAFILE HIGH COARSE APR 22 17:00:00 Y EXAMPLE.429.1166979579
DATAFILE HIGH COARSE APR 22 17:00:00 Y SOE.430.1166979637
DATAFILE HIGH COARSE APR 22 17:00:00 Y TEST.431.1166979631
DATAFILE HIGH COARSE APR 22 17:00:00 Y USERS.292.1166979579
...

Checking the cdb_data_files view. The datafiles are currently not attached whether in the CDB nor in the PDB.

SQL> select file_name from cdb_data_files;
FILE_NAME
--------------------------------------------------------------------------------
+DATAC2/TGT19C/DATAFILE/system.417.1166485039
+DATAC2/TGT19C/DATAFILE/sysaux.337.1166485017
+DATAC2/TGT19C/DATAFILE/undotbs1.398.1166485057
+DATAC2/TGT19C/DATAFILE/users.399.1166485075
+DATAC2/TGT19C/DATAFILE/undotbs2.335.1166485017
+DATAC2/TGT19C/163F77E054E01616E0636309038AA1BB/DATAFILE/system.288.1166485511
+DATAC2/TGT19C/163F77E054E01616E0636309038AA1BB/DATAFILE/sysaux.287.1166485521
+DATAC2/TGT19C/163F77E054E01616E0636309038AA1BB/DATAFILE/undotbs1.281.1166485529
+DATAC2/TGT19C/163F77E054E01616E0636309038AA1BB/DATAFILE/undo_2.282.1166485535
+DATAC2/TGT19C/163F77E054E01616E0636309038AA1BB/DATAFILE/users.286.1166485509
+DATAC2/TGT19C/16B0B12081F5A501E0636309038A0B59/DATAFILE/system.289.1166971727
+DATAC2/TGT19C/16B0B12081F5A501E0636309038A0B59/DATAFILE/sysaux.290.1166971727
+DATAC2/TGT19C/16B0B12081F5A501E0636309038A0B59/DATAFILE/undotbs1.291.1166971727
+DATAC2/TGT19C/16B0B12081F5A501E0636309038A0B59/DATAFILE/undo_2.403.1166971727

5. Roll Forward​

In this phase we will create multiple incremental backups to update the target database.

5.1. Level1 Backup​

We start another backup on the source db:

cd $HOME/xtt
export TMPDIR=$HOME/xtt
nohup $ORACLE_HOME/perl/bin/perl xttdriver.pl --backup --debug 3 > roll1.out 2>&1 &
Output
OUTPUT:

============================================================
trace file is /export/home/oracle/xtt/backup_Apr22_Mon_17_20_00_16//Apr22_Mon_17_20_00_16_.log
=============================================================

--------------------------------------------------------------------
Parsing properties
--------------------------------------------------------------------

Key: platformid
Values: 2
Key: src_scratch_location
Values: /nfsbackup/xtt
Key: parallel
Values: 4
Key: rollparallel
Values: 2
Key: dest_scratch_location
Values: /nfsbackup/xtt
Key: dest_datafile_location
Values: +DATAC2
Key: tablespaces
Values: EXAMPLE,TEST,USERS,SOE
Key: getfileparallel
Values: 4

--------------------------------------------------------------------
Done parsing properties
--------------------------------------------------------------------


--------------------------------------------------------------------
Checking properties
--------------------------------------------------------------------

ARGUMENT tablespaces
ARGUMENT platformid
ARGUMENT backupformat

--------------------------------------------------------------------
Done checking properties
--------------------------------------------------------------------

ORACLE_SID : SRC11G
ORACLE_HOME : /u01/app/oracle/product/11.2.0/dbhome_1
112040

PRIMARY

Running on PRIMARY

--------------------------------------------------------------------
Backup incremental
--------------------------------------------------------------------

scalar(or4
XXX: adding here for 2, 0, EXAMPLE,TEST,USERS,SOE
XXX: adding proper here for index 0, b4 added 'USERS'
,

XXX: adding proper here for index 0, added 'USERS'

XXX: adding proper here for index 1, b4 added 'SOE'
,

XXX: adding proper here for index 1, added 'SOE'

Number of tb arrays is 2

::USERS:::SCN:::115944584=11594458,USERS,+DATAC2/USERS_4.dbf
::EXAMPLE:::SCN:::11594386
5=11594386,EXAMPLE,+DATAC2/EXAMPLE_5.dbf
::TEST:::SCN:::11594431
6=11594431,TEST,+DATAC2/TEST_6.dbf
::SOE:::SCN:::11594496
7=11594496,SOE,+DATAC2/SOE_7.dbf
::USERS:::SCN:::11594458
/export/home/oracle/xtt/newfile.txt: ::USERS:::SCN:::11594458
/export/home/oracle/xtt/newfile.txt: 4=11594458,USERS,+DATAC2/USERS_4.dbf
/export/home/oracle/xtt/newfile.txt: ::EXAMPLE:::SCN:::11594386
/export/home/oracle/xtt/newfile.txt: 5=11594386,EXAMPLE,+DATAC2/EXAMPLE_5.dbf
/export/home/oracle/xtt/newfile.txt: ::TEST:::SCN:::11594431
/export/home/oracle/xtt/newfile.txt: 6=11594431,TEST,+DATAC2/TEST_6.dbf
/export/home/oracle/xtt/newfile.txt: ::SOE:::SCN:::11594496
/export/home/oracle/xtt/newfile.txt: 7=11594496,SOE,+DATAC2/SOE_7.dbf
/export/home/oracle/xtt/backup_Apr22_Mon_17_20_00_16//xttnewdatafiles.txt.added: ::USERS:::SCN:::11594458
/export/home/oracle/xtt/backup_Apr22_Mon_17_20_00_16//xttnewdatafiles.txt.added: 4=11594458,USERS,+DATAC2/USERS_4.dbf
Writing new 4=11594458,USERS,+DATAC2/USERS_4.dbf

Writing1 new 4, +DATAC2/USERS_4.dbf

/export/home/oracle/xtt/backup_Apr22_Mon_17_20_00_16//xttnewdatafiles.txt.added: ::EXAMPLE:::SCN:::11594386
/export/home/oracle/xtt/backup_Apr22_Mon_17_20_00_16//xttnewdatafiles.txt.added: 5=11594386,EXAMPLE,+DATAC2/EXAMPLE_5.dbf
Writing new 5=11594386,EXAMPLE,+DATAC2/EXAMPLE_5.dbf

Writing1 new 5, +DATAC2/EXAMPLE_5.dbf

/export/home/oracle/xtt/backup_Apr22_Mon_17_20_00_16//xttnewdatafiles.txt.added: ::TEST:::SCN:::11594431
/export/home/oracle/xtt/backup_Apr22_Mon_17_20_00_16//xttnewdatafiles.txt.added: 6=11594431,TEST,+DATAC2/TEST_6.dbf
Writing new 6=11594431,TEST,+DATAC2/TEST_6.dbf

Writing1 new 6, +DATAC2/TEST_6.dbf

/export/home/oracle/xtt/backup_Apr22_Mon_17_20_00_16//xttnewdatafiles.txt.added: ::SOE:::SCN:::11594496
/export/home/oracle/xtt/backup_Apr22_Mon_17_20_00_16//xttnewdatafiles.txt.added: 7=11594496,SOE,+DATAC2/SOE_7.dbf
Writing new 7=11594496,SOE,+DATAC2/SOE_7.dbf

Writing1 new 7, +DATAC2/SOE_7.dbf

Added fname here 1:/nfsbackup/xtt/USERS_4.tf
Added fname here 1:/nfsbackup/xtt/EXAMPLE_5.tf
Added fname here 1:/nfsbackup/xtt/TEST_6.tf
Added fname here 1:/nfsbackup/xtt/SOE_7.tf
YYY: ::USERS:::SCN:::11594458

YYY: ::EXAMPLE:::SCN:::11594386

YYY: ::TEST:::SCN:::11594431

YYY: ::SOE:::SCN:::11594496

============================================================
No new datafiles added
=============================================================
/ as sysdba
size of tablespace 4
No. of tablespaces per batch 1
TABLESPACE STRING :'EXAMPLE'
Prepare newscn for Tablespaces: 'EXAMPLE'
EXAMPLE::::11594386 5
TABLESPACE STRING :'TEST'
Prepare newscn for Tablespaces: 'TEST'
TEST::::11594431 6
TABLESPACE STRING :'USERS'
Prepare newscn for Tablespaces: 'USERS'
USERS::::11594458 4
TABLESPACE STRING :'SOE'
Prepare newscn for Tablespaces: 'SOE'
SOE::::11594496 7

--------------------------------------------------------------------
Starting incremental backup
--------------------------------------------------------------------

/export/home/oracle/xtt/backup_Apr22_Mon_17_20_00_16//rmanincr.cmd

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Apr 22 17:20:00 2024

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

RMAN-06005: connected to target database: SRC11G (DBID=107228278)

RMAN> set nocfau;
2> host 'echo ts::EXAMPLE';
3> backup incremental from scn 11590185
4> tablespace 'EXAMPLE' format
5> '/nfsbackup/xtt/%U';
6> set nocfau;
7> host 'echo ts::TEST';
8> backup incremental from scn 11590173
9> tablespace 'TEST' format
10> '/nfsbackup/xtt/%U';
11> set nocfau;
12> host 'echo ts::USERS';
13> backup incremental from scn 11590209
14> tablespace 'USERS' format
15> '/nfsbackup/xtt/%U';
16> set nocfau;
17> host 'echo ts::SOE';
18> backup incremental from scn 11590197
19> tablespace 'SOE' format
20> '/nfsbackup/xtt/%U';
21>
RMAN-03023: executing command: SET NOCFAU
RMAN-06009: using target database control file instead of recovery catalog

ts::EXAMPLE
RMAN-06134: host command complete

RMAN-03090: Starting backup at 22-APR-24
RMAN-08030: allocated channel: ORA_DISK_1
RMAN-08500: channel ORA_DISK_1: SID=63 device type=DISK
RMAN-08008: channel ORA_DISK_1: starting full datafile backup set
RMAN-08010: channel ORA_DISK_1: specifying datafile(s) in backup set
RMAN-08522: input datafile file number=00005 name=+DATA/src11g/datafile/example.266.1161516935
RMAN-08038: channel ORA_DISK_1: starting piece 1 at 22-APR-24
RMAN-08044: channel ORA_DISK_1: finished piece 1 at 22-APR-24
RMAN-08530: piece handle=/nfsbackup/xtt/6f2otdm1_1_1 tag=TAG20240422T172001 comment=NONE
RMAN-08540: channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
RMAN-03091: Finished backup at 22-APR-24

RMAN-03023: executing command: SET NOCFAU

ts::TEST
RMAN-06134: host command complete

RMAN-03090: Starting backup at 22-APR-24
RMAN-12016: using channel ORA_DISK_1
RMAN-08008: channel ORA_DISK_1: starting full datafile backup set
RMAN-08010: channel ORA_DISK_1: specifying datafile(s) in backup set
RMAN-08522: input datafile file number=00006 name=+DATA/src11g/datafile/test.267.1161590723
RMAN-08038: channel ORA_DISK_1: starting piece 1 at 22-APR-24
RMAN-08044: channel ORA_DISK_1: finished piece 1 at 22-APR-24
RMAN-08530: piece handle=/nfsbackup/xtt/6g2otdm2_1_1 tag=TAG20240422T172002 comment=NONE
RMAN-08540: channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
RMAN-03091: Finished backup at 22-APR-24

RMAN-03023: executing command: SET NOCFAU

ts::USERS
RMAN-06134: host command complete

RMAN-03090: Starting backup at 22-APR-24
RMAN-12016: using channel ORA_DISK_1
RMAN-08008: channel ORA_DISK_1: starting full datafile backup set
RMAN-08010: channel ORA_DISK_1: specifying datafile(s) in backup set
RMAN-08522: input datafile file number=00004 name=+DATA/src11g/datafile/users.264.1158601157
RMAN-08038: channel ORA_DISK_1: starting piece 1 at 22-APR-24
RMAN-08044: channel ORA_DISK_1: finished piece 1 at 22-APR-24
RMAN-08530: piece handle=/nfsbackup/xtt/6h2otdm4_1_1 tag=TAG20240422T172003 comment=NONE
RMAN-08540: channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
RMAN-03091: Finished backup at 22-APR-24

RMAN-03023: executing command: SET NOCFAU

ts::SOE
RMAN-06134: host command complete

RMAN-03090: Starting backup at 22-APR-24
RMAN-12016: using channel ORA_DISK_1
RMAN-08008: channel ORA_DISK_1: starting full datafile backup set
RMAN-08010: channel ORA_DISK_1: specifying datafile(s) in backup set
RMAN-08522: input datafile file number=00007 name=+DATA/src11g/datafile/soe.268.1161972789
RMAN-08038: channel ORA_DISK_1: starting piece 1 at 22-APR-24
RMAN-08044: channel ORA_DISK_1: finished piece 1 at 22-APR-24
RMAN-08530: piece handle=/nfsbackup/xtt/6i2otdm5_1_1 tag=TAG20240422T172005 comment=NONE
RMAN-08540: channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
RMAN-03091: Finished backup at 22-APR-24

Recovery Manager complete.


--------------------------------------------------------------------
Done backing up incrementals
--------------------------------------------------------------------

/ as sysdba
size of tablespace 4
No. of tablespaces per batch 1
TABLESPACE STRING :'EXAMPLE'
Prepare newscn for Tablespaces: 'EXAMPLE'

TABLESPACE STRING :'TEST'
Prepare newscn for Tablespaces: 'TEST'

TABLESPACE STRING :'USERS'
Prepare newscn for Tablespaces: 'USERS'

TABLESPACE STRING :'SOE'
Prepare newscn for Tablespaces: 'SOE'

New /export/home/oracle/xtt/xttplan.txt with FROM SCN's generated

The res.txt file was updated with the new backup:

cat res.txt
#0:::4,2,USERS_4.dbf,0,4455484,0,0,0,USERS,USERS_4.dbf
#0:::5,2,EXAMPLE_5.dbf,0,4455484,0,0,0,EXAMPLE,EXAMPLE_5.dbf
#0:::6,2,TEST_6.dbf,0,4455484,0,0,0,TEST,TEST_6.dbf
#0:::7,2,SOE_7.dbf,0,4455484,0,0,0,SOE,SOE_7.dbf
#1:::6,2,602or76p_1_1,4455484,4456146,0,0,0,TEST_6.dbf,TEST_6.dbf
#1:::4,2,612or76q_1_1,4455484,4456173,0,0,0,USERS_4.dbf,USERS_4.dbf
#1:::7,2,622or76s_1_1,4455484,4456211,0,0,0,SOE_7.dbf,SOE_7.dbf
#1:::5,2,5v2or76o_1_1,4455484,4456101,0,0,0,EXAMPLE_5.dbf,EXAMPLE_5.dbf

Again we need to transfer the file to the target system:

# on source:
cp -p $HOME/xtt/res.txt /nfsbackup/xtt/copy/
# on target:
cp -p /nfsbackup/xtt/copy/res.txt $HOME/xtt
caution

The following restore restarts the current instance into nomount mode!
This is especially relevant if there are multiple PDBS running in the target CDB.

5.2. Level1 Restore​

We restore the level1 backup on the target system:

```bash className=code-tgt
cd $HOME/xtt
export TMPDIR=$HOME/xtt
nohup $ORACLE_HOME/perl/bin/perl xttdriver.pl --restore --debug 3 >roll1.out 2>&1 &
```
Output
OUTPUT

============================================================
trace file is /home/oracle/xtt/restore_Apr22_Mon_17_28_17_828//Apr22_Mon_17_28_17_828_.log
=============================================================

--------------------------------------------------------------------
Parsing properties
--------------------------------------------------------------------

Key: getfileparallel
Values: 4
Key: rollparallel
Values: 2
Key: platformid
Values: 2
Key: tablespaces
Values: EXAMPLE,TEST,USERS,SOE
Key: src_scratch_location
Values: /nfsbackup/xtt
Key: parallel
Values: 4
Key: dest_datafile_location
Values: +DATAC2
Key: dest_scratch_location
Values: /nfsbackup/xtt

--------------------------------------------------------------------
Done parsing properties
--------------------------------------------------------------------


--------------------------------------------------------------------
Checking properties
--------------------------------------------------------------------

ARGUMENT tablespaces
ARGUMENT platformid
ARGUMENT backupformat

--------------------------------------------------------------------
Done checking properties
--------------------------------------------------------------------

ORACLE_SID : TGT19C1
ORACLE_HOME : /u02/app/oracle/product/19.0.0.0/dbhome_1
190000

5

6

4

7

YYY: EXAMPLE_5.dbf::5:::1=6f2otdm1_1_1 TEST_6.dbf::6:::1=6g2otdm2_1_1 USERS_4.dbf::4:::1=6h2otdm4_1_1 SOE_7.dbf::7:::1=6i2otdm5_1_1


--------------------------------------------------------------------
Start rollforward
--------------------------------------------------------------------

ROLLFORWARD: Starting DB in nomount mode
ORACLE instance started.
Total System Global Area 7953314304 bytes
Fixed Size 9200128 bytes
Variable Size 2097152000 bytes
Database Buffers 5570035712 bytes
Redo Buffers 276926464 bytes
rdfno 5

BEFORE ROLLPLAN

datafile number : 5

datafile name : +DATAC2/EXAMPLE_5.dbf

AFTER ROLLPLAN

rdfno 7

BEFORE ROLLPLAN

datafile number : 7

datafile name : +DATAC2/SOE_7.dbf

AFTER ROLLPLAN

rdfno 6

BEFORE ROLLPLAN

datafile number : 6

datafile name : +DATAC2/TEST_6.dbf

AFTER ROLLPLAN

sqlplus -L -s "/ as sysdba" @/home/oracle/xtt/restore_Apr22_Mon_17_28_17_828//xxttconv_6f2otdm1_1_1_5.sql /nfsbackup/xtt/6f2otdm1_1_1 /nfsbackup/xtt 2

CONVERTED BACKUP PIECE/nfsbackup/xtt/xib_6f2otdm1_1_1_5
PL/SQL procedure successfully completed.
sqlplus -L -s "/ as sysdba" @/home/oracle/xtt/restore_Apr22_Mon_17_28_17_828//xxttconv_6i2otdm5_1_1_7.sql /nfsbackup/xtt/6i2otdm5_1_1 /nfsbackup/xtt 2

CONVERTED BACKUP PIECE/nfsbackup/xtt/xib_6i2otdm5_1_1_7
PL/SQL procedure successfully completed.
Entering RollForwardAfter applySetDataFile
Done: applyDataFileTo
Done: applyDataFileTo
Done: RestoreSetPiece
Done: RestoreBackupPiece

PL/SQL procedure successfully completed.
rdfno 4

BEFORE ROLLPLAN

datafile number : 4

datafile name : +DATAC2/USERS_4.dbf

AFTER ROLLPLAN

Entering RollForwardAfter applySetDataFile
Done: applyDataFileTo
Done: applyDataFileTo
Done: RestoreSetPiece
Done: RestoreBackupPiece

PL/SQL procedure successfully completed.
sqlplus -L -s "/ as sysdba" @/home/oracle/xtt/restore_Apr22_Mon_17_28_17_828//xxttconv_6g2otdm2_1_1_6.sql /nfsbackup/xtt/6g2otdm2_1_1 /nfsbackup/xtt 2

CONVERTED BACKUP PIECE/nfsbackup/xtt/xib_6g2otdm2_1_1_6
PL/SQL procedure successfully completed.
sqlplus -L -s "/ as sysdba" @/home/oracle/xtt/restore_Apr22_Mon_17_28_17_828//xxttconv_6h2otdm4_1_1_4.sql /nfsbackup/xtt/6h2otdm4_1_1 /nfsbackup/xtt 2

CONVERTED BACKUP PIECE/nfsbackup/xtt/xib_6h2otdm4_1_1_4
PL/SQL procedure successfully completed.
Entering RollForwardAfter applySetDataFile
Done: applyDataFileTo
Done: applyDataFileTo
Done: RestoreSetPiece
Done: RestoreBackupPiece

PL/SQL procedure successfully completed.
Entering RollForwardAfter applySetDataFile
Done: applyDataFileTo
Done: applyDataFileTo
Done: RestoreSetPiece
Done: RestoreBackupPiece

PL/SQL procedure successfully completed.

--------------------------------------------------------------------
End of rollforward phase
--------------------------------------------------------------------

We can repeat this phase as often as desired.

  • Let's create a table and add some rows on the source:

    SQL> create user macsdata identified by WELcome##1234 default tablespace example temporary tablespace temp quota unlimited on example;

    SQL> create table macsdata.mactab1 (id number, name varchar(50));
    SQL> insert into macsdata.mactab1 values (1,'MACS');

    1 row created.

    SQL> insert into macsdata.mactab1 values (2,'DATA');

    1 row created.
    SQL> commit;

    Commit complete.

5.3. Level1 Backup​

Now create another Level 1 backup

cd $HOME/xtt
export TMPDIR=$HOME/xtt
nohup $ORACLE_HOME/perl/bin/perl xttdriver.pl --backup --debug 3 >roll2.out 2>&1 &
cp -p $HOME/xtt/res.txt /nfsbackup/XTT/copy/
Output
OUTPUT:

============================================================
trace file is /export/home/oracle/xtt/backup_Apr22_Mon_17_33_57_128//Apr22_Mon_17_33_57_128_.log
=============================================================

--------------------------------------------------------------------
Parsing properties
--------------------------------------------------------------------

Key: platformid
Values: 2
Key: src_scratch_location
Values: /nfsbackup/xtt
Key: parallel
Values: 4
Key: rollparallel
Values: 2
Key: dest_scratch_location
Values: /nfsbackup/xtt
Key: dest_datafile_location
Values: +DATAC2
Key: tablespaces
Values: EXAMPLE,TEST,USERS,SOE
Key: getfileparallel
Values: 4

--------------------------------------------------------------------
Done parsing properties
--------------------------------------------------------------------


--------------------------------------------------------------------
Checking properties
--------------------------------------------------------------------

ARGUMENT tablespaces
ARGUMENT platformid
ARGUMENT backupformat

--------------------------------------------------------------------
Done checking properties
--------------------------------------------------------------------

ORACLE_SID : SRC11G
ORACLE_HOME : /u01/app/oracle/product/11.2.0/dbhome_1
112040

PRIMARY

Running on PRIMARY

--------------------------------------------------------------------
Backup incremental
--------------------------------------------------------------------

scalar(or4
XXX: adding here for 2, 0, EXAMPLE,TEST,USERS,SOE
XXX: adding proper here for index 0, b4 added 'USERS'
,

XXX: adding proper here for index 0, added 'USERS'

XXX: adding proper here for index 1, b4 added 'SOE'
,

XXX: adding proper here for index 1, added 'SOE'

Number of tb arrays is 2

::USERS:::SCN:::115955634=11595563,USERS,+DATAC2/USERS_4.dbf
::EXAMPLE:::SCN:::11595522
5=11595522,EXAMPLE,+DATAC2/EXAMPLE_5.dbf
::TEST:::SCN:::11595542
6=11595542,TEST,+DATAC2/TEST_6.dbf
::SOE:::SCN:::11595583
7=11595583,SOE,+DATAC2/SOE_7.dbf
::USERS:::SCN:::11595563
/export/home/oracle/xtt/newfile.txt: ::USERS:::SCN:::11595563
/export/home/oracle/xtt/newfile.txt: 4=11595563,USERS,+DATAC2/USERS_4.dbf
/export/home/oracle/xtt/newfile.txt: ::EXAMPLE:::SCN:::11595522
/export/home/oracle/xtt/newfile.txt: 5=11595522,EXAMPLE,+DATAC2/EXAMPLE_5.dbf
/export/home/oracle/xtt/newfile.txt: ::TEST:::SCN:::11595542
/export/home/oracle/xtt/newfile.txt: 6=11595542,TEST,+DATAC2/TEST_6.dbf
/export/home/oracle/xtt/newfile.txt: ::SOE:::SCN:::11595583
/export/home/oracle/xtt/newfile.txt: 7=11595583,SOE,+DATAC2/SOE_7.dbf
/export/home/oracle/xtt/backup_Apr22_Mon_17_33_57_128//xttnewdatafiles.txt.added: ::USERS:::SCN:::11595563
/export/home/oracle/xtt/backup_Apr22_Mon_17_33_57_128//xttnewdatafiles.txt.added: 4=11595563,USERS,+DATAC2/USERS_4.dbf
Writing new 4=11595563,USERS,+DATAC2/USERS_4.dbf

Writing1 new 4, +DATAC2/USERS_4.dbf

/export/home/oracle/xtt/backup_Apr22_Mon_17_33_57_128//xttnewdatafiles.txt.added: ::EXAMPLE:::SCN:::11595522
/export/home/oracle/xtt/backup_Apr22_Mon_17_33_57_128//xttnewdatafiles.txt.added: 5=11595522,EXAMPLE,+DATAC2/EXAMPLE_5.dbf
Writing new 5=11595522,EXAMPLE,+DATAC2/EXAMPLE_5.dbf

Writing1 new 5, +DATAC2/EXAMPLE_5.dbf

/export/home/oracle/xtt/backup_Apr22_Mon_17_33_57_128//xttnewdatafiles.txt.added: ::TEST:::SCN:::11595542
/export/home/oracle/xtt/backup_Apr22_Mon_17_33_57_128//xttnewdatafiles.txt.added: 6=11595542,TEST,+DATAC2/TEST_6.dbf
Writing new 6=11595542,TEST,+DATAC2/TEST_6.dbf

Writing1 new 6, +DATAC2/TEST_6.dbf

/export/home/oracle/xtt/backup_Apr22_Mon_17_33_57_128//xttnewdatafiles.txt.added: ::SOE:::SCN:::11595583
/export/home/oracle/xtt/backup_Apr22_Mon_17_33_57_128//xttnewdatafiles.txt.added: 7=11595583,SOE,+DATAC2/SOE_7.dbf
Writing new 7=11595583,SOE,+DATAC2/SOE_7.dbf

Writing1 new 7, +DATAC2/SOE_7.dbf

Added fname here 1:/nfsbackup/xtt/USERS_4.tf
Added fname here 1:/nfsbackup/xtt/EXAMPLE_5.tf
Added fname here 1:/nfsbackup/xtt/TEST_6.tf
Added fname here 1:/nfsbackup/xtt/SOE_7.tf
YYY: ::USERS:::SCN:::11595563

YYY: ::EXAMPLE:::SCN:::11595522

YYY: ::TEST:::SCN:::11595542

YYY: ::SOE:::SCN:::11595583

============================================================
No new datafiles added
=============================================================
/ as sysdba
size of tablespace 4
No. of tablespaces per batch 1
TABLESPACE STRING :'EXAMPLE'
Prepare newscn for Tablespaces: 'EXAMPLE'
EXAMPLE::::11595522 5
TABLESPACE STRING :'TEST'
Prepare newscn for Tablespaces: 'TEST'
TEST::::11595542 6
TABLESPACE STRING :'USERS'
Prepare newscn for Tablespaces: 'USERS'
USERS::::11595563 4
TABLESPACE STRING :'SOE'
Prepare newscn for Tablespaces: 'SOE'
SOE::::11595583 7

--------------------------------------------------------------------
Starting incremental backup
--------------------------------------------------------------------

/export/home/oracle/xtt/backup_Apr22_Mon_17_33_57_128//rmanincr.cmd

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Apr 22 17:33:57 2024

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

RMAN-06005: connected to target database: SRC11G (DBID=107228278)

RMAN> set nocfau;
2> host 'echo ts::EXAMPLE';
3> backup incremental from scn 11594386
4> tablespace 'EXAMPLE' format
5> '/nfsbackup/xtt/%U';
6> set nocfau;
7> host 'echo ts::TEST';
8> backup incremental from scn 11594431
9> tablespace 'TEST' format
10> '/nfsbackup/xtt/%U';
11> set nocfau;
12> host 'echo ts::USERS';
13> backup incremental from scn 11594458
14> tablespace 'USERS' format
15> '/nfsbackup/xtt/%U';
16> set nocfau;
17> host 'echo ts::SOE';
18> backup incremental from scn 11594496
19> tablespace 'SOE' format
20> '/nfsbackup/xtt/%U';
21>
RMAN-03023: executing command: SET NOCFAU
RMAN-06009: using target database control file instead of recovery catalog

ts::EXAMPLE
RMAN-06134: host command complete

RMAN-03090: Starting backup at 22-APR-24
RMAN-08030: allocated channel: ORA_DISK_1
RMAN-08500: channel ORA_DISK_1: SID=65 device type=DISK
RMAN-08008: channel ORA_DISK_1: starting full datafile backup set
RMAN-08010: channel ORA_DISK_1: specifying datafile(s) in backup set
RMAN-08522: input datafile file number=00005 name=+DATA/src11g/datafile/example.266.1161516935
RMAN-08038: channel ORA_DISK_1: starting piece 1 at 22-APR-24
RMAN-08044: channel ORA_DISK_1: finished piece 1 at 22-APR-24
RMAN-08530: piece handle=/nfsbackup/xtt/6j2oteg6_1_1 tag=TAG20240422T173358 comment=NONE
RMAN-08540: channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
RMAN-03091: Finished backup at 22-APR-24

RMAN-03023: executing command: SET NOCFAU

ts::TEST
RMAN-06134: host command complete

RMAN-03090: Starting backup at 22-APR-24
RMAN-12016: using channel ORA_DISK_1
RMAN-08008: channel ORA_DISK_1: starting full datafile backup set
RMAN-08010: channel ORA_DISK_1: specifying datafile(s) in backup set
RMAN-08522: input datafile file number=00006 name=+DATA/src11g/datafile/test.267.1161590723
RMAN-08038: channel ORA_DISK_1: starting piece 1 at 22-APR-24
RMAN-08044: channel ORA_DISK_1: finished piece 1 at 22-APR-24
RMAN-08530: piece handle=/nfsbackup/xtt/6k2oteg7_1_1 tag=TAG20240422T173359 comment=NONE
RMAN-08540: channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
RMAN-03091: Finished backup at 22-APR-24

RMAN-03023: executing command: SET NOCFAU

ts::USERS
RMAN-06134: host command complete

RMAN-03090: Starting backup at 22-APR-24
RMAN-12016: using channel ORA_DISK_1
RMAN-08008: channel ORA_DISK_1: starting full datafile backup set
RMAN-08010: channel ORA_DISK_1: specifying datafile(s) in backup set
RMAN-08522: input datafile file number=00004 name=+DATA/src11g/datafile/users.264.1158601157
RMAN-08038: channel ORA_DISK_1: starting piece 1 at 22-APR-24
RMAN-08044: channel ORA_DISK_1: finished piece 1 at 22-APR-24
RMAN-08530: piece handle=/nfsbackup/xtt/6l2oteg9_1_1 tag=TAG20240422T173401 comment=NONE
RMAN-08540: channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
RMAN-03091: Finished backup at 22-APR-24

RMAN-03023: executing command: SET NOCFAU

ts::SOE
RMAN-06134: host command complete

RMAN-03090: Starting backup at 22-APR-24
RMAN-12016: using channel ORA_DISK_1
RMAN-08008: channel ORA_DISK_1: starting full datafile backup set
RMAN-08010: channel ORA_DISK_1: specifying datafile(s) in backup set
RMAN-08522: input datafile file number=00007 name=+DATA/src11g/datafile/soe.268.1161972789
RMAN-08038: channel ORA_DISK_1: starting piece 1 at 22-APR-24
RMAN-08044: channel ORA_DISK_1: finished piece 1 at 22-APR-24
RMAN-08530: piece handle=/nfsbackup/xtt/6m2otega_1_1 tag=TAG20240422T173402 comment=NONE
RMAN-08540: channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
RMAN-03091: Finished backup at 22-APR-24

Recovery Manager complete.


--------------------------------------------------------------------
Done backing up incrementals
--------------------------------------------------------------------

/ as sysdba
size of tablespace 4
No. of tablespaces per batch 1
TABLESPACE STRING :'EXAMPLE'
Prepare newscn for Tablespaces: 'EXAMPLE'

TABLESPACE STRING :'TEST'
Prepare newscn for Tablespaces: 'TEST'

TABLESPACE STRING :'USERS'
Prepare newscn for Tablespaces: 'USERS'

TABLESPACE STRING :'SOE'
Prepare newscn for Tablespaces: 'SOE'

New /export/home/oracle/xtt/xttplan.txt with FROM SCN's generated

The res.txt file was updated with the new backup:

cat res.txt
#0:::4,2,USERS_4.dbf,0,4455484,0,0,0,USERS,USERS_4.dbf
#0:::5,2,EXAMPLE_5.dbf,0,4455484,0,0,0,EXAMPLE,EXAMPLE_5.dbf
#0:::6,2,TEST_6.dbf,0,4455484,0,0,0,TEST,TEST_6.dbf
#0:::7,2,SOE_7.dbf,0,4455484,0,0,0,SOE,SOE_7.dbf
#1:::6,2,602or76p_1_1,4455484,4456146,0,0,0,TEST_6.dbf,TEST_6.dbf
#1:::4,2,612or76q_1_1,4455484,4456173,0,0,0,USERS_4.dbf,USERS_4.dbf
#1:::7,2,622or76s_1_1,4455484,4456211,0,0,0,SOE_7.dbf,SOE_7.dbf
#1:::5,2,5v2or76o_1_1,4455484,4456101,0,0,0,EXAMPLE_5.dbf,EXAMPLE_5.dbf
#2:::6,2,642orcrh_1_1,4456146,4459923,0,0,0,TEST_6.dbf,TEST_6.dbf
#2:::4,2,652orcri_1_1,4456173,4459943,0,0,0,USERS_4.dbf,USERS_4.dbf
#2:::7,2,662orcrj_1_1,4456211,4459963,0,0,0,SOE_7.dbf,SOE_7.dbf
#2:::5,2,632orcrg_1_1,4456101,4459902,0,0,0,EXAMPLE_5.dbf,EXAMPLE_5.dbf

Again we need to transfer the file to the target system:

# on source:
cp -p $HOME/xtt/res.txt /nfsbackup/xtt/copy/
# on target:
cp -p /nfsbackup/xtt/copy/res.txt $HOME/xtt

5.4. Level1 Restore​

Again we restore the level1 backup on the target system:

cd $HOME/xtt
export TMPDIR=$HOME/xtt
nohup $ORACLE_HOME/perl/bin/perl xttdriver.pl --restore --debug 3 >roll2.out 2>&1 &
Output
OUTPUT:

============================================================
trace file is /home/oracle/xtt/restore_Apr22_Mon_17_37_19_844//Apr22_Mon_17_37_19_844_.log
=============================================================

--------------------------------------------------------------------
Parsing properties
--------------------------------------------------------------------

Key: parallel
Values: 4
Key: dest_datafile_location
Values: +DATAC2
Key: src_scratch_location
Values: /nfsbackup/xtt
Key: tablespaces
Values: EXAMPLE,TEST,USERS,SOE
Key: platformid
Values: 2
Key: dest_scratch_location
Values: /nfsbackup/xtt
Key: rollparallel
Values: 2
Key: getfileparallel
Values: 4

--------------------------------------------------------------------
Done parsing properties
--------------------------------------------------------------------


--------------------------------------------------------------------
Checking properties
--------------------------------------------------------------------

ARGUMENT tablespaces
ARGUMENT platformid
ARGUMENT backupformat

--------------------------------------------------------------------
Done checking properties
--------------------------------------------------------------------

ORACLE_SID : TGT19C1
ORACLE_HOME : /u02/app/oracle/product/19.0.0.0/dbhome_1
190000

5

6

4

7

YYY: EXAMPLE_5.dbf::5:::1=6j2oteg6_1_1 TEST_6.dbf::6:::1=6k2oteg7_1_1 USERS_4.dbf::4:::1=6l2oteg9_1_1 SOE_7.dbf::7:::1=6m2otega_1_1


--------------------------------------------------------------------
Start rollforward
--------------------------------------------------------------------

ROLLFORWARD: Starting DB in nomount mode
ORACLE instance started.
Total System Global Area 7953314304 bytes
Fixed Size 9200128 bytes
Variable Size 2097152000 bytes
Database Buffers 5570035712 bytes
Redo Buffers 276926464 bytes
rdfno 5

BEFORE ROLLPLAN

datafile number : 5

datafile name : +DATAC2/EXAMPLE_5.dbf

AFTER ROLLPLAN

rdfno 7

BEFORE ROLLPLAN

datafile number : 7

datafile name : +DATAC2/SOE_7.dbf

AFTER ROLLPLAN

rdfno 6

BEFORE ROLLPLAN

datafile number : 6

datafile name : +DATAC2/TEST_6.dbf

AFTER ROLLPLAN

sqlplus -L -s "/ as sysdba" @/home/oracle/xtt/restore_Apr22_Mon_17_37_19_844//xxttconv_6j2oteg6_1_1_5.sql /nfsbackup/xtt/6j2oteg6_1_1 /nfsbackup/xtt 2

CONVERTED BACKUP PIECE/nfsbackup/xtt/xib_6j2oteg6_1_1_5
PL/SQL procedure successfully completed.
sqlplus -L -s "/ as sysdba" @/home/oracle/xtt/restore_Apr22_Mon_17_37_19_844//xxttconv_6m2otega_1_1_7.sql /nfsbackup/xtt/6m2otega_1_1 /nfsbackup/xtt 2

CONVERTED BACKUP PIECE/nfsbackup/xtt/xib_6m2otega_1_1_7
PL/SQL procedure successfully completed.
Entering RollForwardAfter applySetDataFile
Done: applyDataFileTo
Done: applyDataFileTo
Done: RestoreSetPiece
Done: RestoreBackupPiece

PL/SQL procedure successfully completed.
rdfno 4

BEFORE ROLLPLAN

datafile number : 4

datafile name : +DATAC2/USERS_4.dbf

AFTER ROLLPLAN

Entering RollForwardAfter applySetDataFile
Done: applyDataFileTo
Done: applyDataFileTo
Done: RestoreSetPiece
Done: RestoreBackupPiece

PL/SQL procedure successfully completed.
sqlplus -L -s "/ as sysdba" @/home/oracle/xtt/restore_Apr22_Mon_17_37_19_844//xxttconv_6k2oteg7_1_1_6.sql /nfsbackup/xtt/6k2oteg7_1_1 /nfsbackup/xtt 2

CONVERTED BACKUP PIECE/nfsbackup/xtt/xib_6k2oteg7_1_1_6
PL/SQL procedure successfully completed.
Entering RollForwardAfter applySetDataFile
Done: applyDataFileTo
Done: applyDataFileTo
Done: RestoreSetPiece
Done: RestoreBackupPiece

PL/SQL procedure successfully completed.
sqlplus -L -s "/ as sysdba" @/home/oracle/xtt/restore_Apr22_Mon_17_37_19_844//xxttconv_6l2oteg9_1_1_4.sql /nfsbackup/xtt/6l2oteg9_1_1 /nfsbackup/xtt 2

CONVERTED BACKUP PIECE/nfsbackup/xtt/xib_6l2oteg9_1_1_4
PL/SQL procedure successfully completed.
Entering RollForwardAfter applySetDataFile
Done: applyDataFileTo
Done: applyDataFileTo
Done: RestoreSetPiece
Done: RestoreBackupPiece

PL/SQL procedure successfully completed.

--------------------------------------------------------------------
End of rollforward phase
--------------------------------------------------------------------
  • Let's add some more rows into the table before our final backup:

    SQL> insert into macsdata.mactab1 values (3,'BEFORE');
    1 row created.

    SQL> insert into macsdata.mactab1 values (4,'FINAL');
    1 row created.

    SQL> insert into macsdata.mactab1 values (5,'BACKUP');
    1 row created.

    SQL> commit;

6. Final Backup​

info

This is the beginning of the Downtime

  • Set tablespace read only

    SQL> alter tablespace TEST read only;

    Tablespace altered.

    SQL> alter tablespace EXAMPLE read only;

    Tablespace altered.

    SQL> alter tablespace SOE read only;

    Tablespace altered.

    SQL> alter tablespace USERS read only;

    Tablespace altered.
    SQL> select tablespace_name, status from dba_tablespaces;           
    TABLESPACE_NAME                STATUS
    ------------------------------ ---------
    SYSTEM ONLINE
    SYSAUX ONLINE
    UNDOTBS1 ONLINE
    TEMP ONLINE
    USERS READ ONLY
    EXAMPLE READ ONLY
    TEST READ ONLY
    SOE READ ONLY

    8 rows selected.

Now we take the final backup:

cd $HOME/xtt
export TMPDIR=$HOME/xtt
# make sure ORACLE_SID and ORACLE_HOME are set correct
nohup $ORACLE_HOME/perl/bin/perl xttdriver.pl --backup --debug 3 >final.out 2>&1 &
Output
OUTPUT:

============================================================
trace file is /export/home/oracle/xtt/backup_Apr22_Mon_17_41_41_243//Apr22_Mon_17_41_41_243_.log
=============================================================

--------------------------------------------------------------------
Parsing properties
--------------------------------------------------------------------

Key: platformid
Values: 2
Key: src_scratch_location
Values: /nfsbackup/xtt
Key: parallel
Values: 4
Key: rollparallel
Values: 2
Key: dest_scratch_location
Values: /nfsbackup/xtt
Key: dest_datafile_location
Values: +DATAC2
Key: tablespaces
Values: EXAMPLE,TEST,USERS,SOE
Key: getfileparallel
Values: 4

--------------------------------------------------------------------
Done parsing properties
--------------------------------------------------------------------


--------------------------------------------------------------------
Checking properties
--------------------------------------------------------------------

ARGUMENT tablespaces
ARGUMENT platformid
ARGUMENT backupformat

--------------------------------------------------------------------
Done checking properties
--------------------------------------------------------------------

ORACLE_SID : SRC11G
ORACLE_HOME : /u01/app/oracle/product/11.2.0/dbhome_1
112040

PRIMARY

Running on PRIMARY

--------------------------------------------------------------------
Backup incremental
--------------------------------------------------------------------

scalar(or4
XXX: adding here for 2, 0, EXAMPLE,TEST,USERS,SOE
XXX: adding proper here for index 0, b4 added 'USERS'
,

XXX: adding proper here for index 0, added 'USERS'

XXX: adding proper here for index 1, b4 added 'SOE'
,

XXX: adding proper here for index 1, added 'SOE'

Number of tb arrays is 2

::USERS:::SCN:::115961874=11596187,USERS,+DATAC2/USERS_4.dbf
::EXAMPLE:::SCN:::11596167
5=11596167,EXAMPLE,+DATAC2/EXAMPLE_5.dbf
::TEST:::SCN:::11596158
6=11596158,TEST,+DATAC2/TEST_6.dbf
::SOE:::SCN:::11596177
7=11596177,SOE,+DATAC2/SOE_7.dbf
::USERS:::SCN:::11596187
/export/home/oracle/xtt/newfile.txt: ::USERS:::SCN:::11596187
/export/home/oracle/xtt/newfile.txt: 4=11596187,USERS,+DATAC2/USERS_4.dbf
/export/home/oracle/xtt/newfile.txt: ::EXAMPLE:::SCN:::11596167
/export/home/oracle/xtt/newfile.txt: 5=11596167,EXAMPLE,+DATAC2/EXAMPLE_5.dbf
/export/home/oracle/xtt/newfile.txt: ::TEST:::SCN:::11596158
/export/home/oracle/xtt/newfile.txt: 6=11596158,TEST,+DATAC2/TEST_6.dbf
/export/home/oracle/xtt/newfile.txt: ::SOE:::SCN:::11596177
/export/home/oracle/xtt/newfile.txt: 7=11596177,SOE,+DATAC2/SOE_7.dbf
/export/home/oracle/xtt/backup_Apr22_Mon_17_41_41_243//xttnewdatafiles.txt.added: ::USERS:::SCN:::11596187
/export/home/oracle/xtt/backup_Apr22_Mon_17_41_41_243//xttnewdatafiles.txt.added: 4=11596187,USERS,+DATAC2/USERS_4.dbf
Writing new 4=11596187,USERS,+DATAC2/USERS_4.dbf

Writing1 new 4, +DATAC2/USERS_4.dbf

/export/home/oracle/xtt/backup_Apr22_Mon_17_41_41_243//xttnewdatafiles.txt.added: ::EXAMPLE:::SCN:::11596167
/export/home/oracle/xtt/backup_Apr22_Mon_17_41_41_243//xttnewdatafiles.txt.added: 5=11596167,EXAMPLE,+DATAC2/EXAMPLE_5.dbf
Writing new 5=11596167,EXAMPLE,+DATAC2/EXAMPLE_5.dbf

Writing1 new 5, +DATAC2/EXAMPLE_5.dbf

/export/home/oracle/xtt/backup_Apr22_Mon_17_41_41_243//xttnewdatafiles.txt.added: ::TEST:::SCN:::11596158
/export/home/oracle/xtt/backup_Apr22_Mon_17_41_41_243//xttnewdatafiles.txt.added: 6=11596158,TEST,+DATAC2/TEST_6.dbf
Writing new 6=11596158,TEST,+DATAC2/TEST_6.dbf

Writing1 new 6, +DATAC2/TEST_6.dbf

/export/home/oracle/xtt/backup_Apr22_Mon_17_41_41_243//xttnewdatafiles.txt.added: ::SOE:::SCN:::11596177
/export/home/oracle/xtt/backup_Apr22_Mon_17_41_41_243//xttnewdatafiles.txt.added: 7=11596177,SOE,+DATAC2/SOE_7.dbf
Writing new 7=11596177,SOE,+DATAC2/SOE_7.dbf

Writing1 new 7, +DATAC2/SOE_7.dbf

Added fname here 1:/nfsbackup/xtt/USERS_4.tf
Added fname here 1:/nfsbackup/xtt/EXAMPLE_5.tf
Added fname here 1:/nfsbackup/xtt/TEST_6.tf
Added fname here 1:/nfsbackup/xtt/SOE_7.tf
YYY: ::USERS:::SCN:::11596187

YYY: ::EXAMPLE:::SCN:::11596167

YYY: ::TEST:::SCN:::11596158

YYY: ::SOE:::SCN:::11596177

============================================================
No new datafiles added
=============================================================
/ as sysdba
size of tablespace 4
No. of tablespaces per batch 1
TABLESPACE STRING :'EXAMPLE'
Prepare newscn for Tablespaces: 'EXAMPLE'
EXAMPLE::::11596167 5
TABLESPACE STRING :'TEST'
Prepare newscn for Tablespaces: 'TEST'
TEST::::11596158 6
TABLESPACE STRING :'USERS'
Prepare newscn for Tablespaces: 'USERS'
USERS::::11596187 4
TABLESPACE STRING :'SOE'
Prepare newscn for Tablespaces: 'SOE'
SOE::::11596177 7

--------------------------------------------------------------------
Starting incremental backup
--------------------------------------------------------------------

/export/home/oracle/xtt/backup_Apr22_Mon_17_41_41_243//rmanincr.cmd

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Apr 22 17:41:42 2024

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

RMAN-06005: connected to target database: SRC11G (DBID=107228278)

RMAN> set nocfau;
2> host 'echo ts::EXAMPLE';
3> backup incremental from scn 11595522
4> tablespace 'EXAMPLE' format
5> '/nfsbackup/xtt/%U';
6> set nocfau;
7> host 'echo ts::TEST';
8> backup incremental from scn 11595542
9> tablespace 'TEST' format
10> '/nfsbackup/xtt/%U';
11> set nocfau;
12> host 'echo ts::USERS';
13> backup incremental from scn 11595563
14> tablespace 'USERS' format
15> '/nfsbackup/xtt/%U';
16> set nocfau;
17> host 'echo ts::SOE';
18> backup incremental from scn 11595583
19> tablespace 'SOE' format
20> '/nfsbackup/xtt/%U';
21>
RMAN-03023: executing command: SET NOCFAU
RMAN-06009: using target database control file instead of recovery catalog

ts::EXAMPLE
RMAN-06134: host command complete

RMAN-03090: Starting backup at 22-APR-24
RMAN-08030: allocated channel: ORA_DISK_1
RMAN-08500: channel ORA_DISK_1: SID=65 device type=DISK
RMAN-08008: channel ORA_DISK_1: starting full datafile backup set
RMAN-08010: channel ORA_DISK_1: specifying datafile(s) in backup set
RMAN-08522: input datafile file number=00005 name=+DATA/src11g/datafile/example.266.1161516935
RMAN-08038: channel ORA_DISK_1: starting piece 1 at 22-APR-24
RMAN-08044: channel ORA_DISK_1: finished piece 1 at 22-APR-24
RMAN-08530: piece handle=/nfsbackup/xtt/6n2oteun_1_1 tag=TAG20240422T174143 comment=NONE
RMAN-08540: channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
RMAN-03091: Finished backup at 22-APR-24

RMAN-03023: executing command: SET NOCFAU

ts::TEST
RMAN-06134: host command complete

RMAN-03090: Starting backup at 22-APR-24
RMAN-12016: using channel ORA_DISK_1
RMAN-08008: channel ORA_DISK_1: starting full datafile backup set
RMAN-08010: channel ORA_DISK_1: specifying datafile(s) in backup set
RMAN-08522: input datafile file number=00006 name=+DATA/src11g/datafile/test.267.1161590723
RMAN-08038: channel ORA_DISK_1: starting piece 1 at 22-APR-24
RMAN-08044: channel ORA_DISK_1: finished piece 1 at 22-APR-24
RMAN-08530: piece handle=/nfsbackup/xtt/6o2oteuo_1_1 tag=TAG20240422T174144 comment=NONE
RMAN-08540: channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
RMAN-03091: Finished backup at 22-APR-24

RMAN-03023: executing command: SET NOCFAU

ts::USERS
RMAN-06134: host command complete

RMAN-03090: Starting backup at 22-APR-24
RMAN-12016: using channel ORA_DISK_1
RMAN-08008: channel ORA_DISK_1: starting full datafile backup set
RMAN-08010: channel ORA_DISK_1: specifying datafile(s) in backup set
RMAN-08522: input datafile file number=00004 name=+DATA/src11g/datafile/users.264.1158601157
RMAN-08038: channel ORA_DISK_1: starting piece 1 at 22-APR-24
RMAN-08044: channel ORA_DISK_1: finished piece 1 at 22-APR-24
RMAN-08530: piece handle=/nfsbackup/xtt/6p2oteup_1_1 tag=TAG20240422T174145 comment=NONE
RMAN-08540: channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
RMAN-03091: Finished backup at 22-APR-24

RMAN-03023: executing command: SET NOCFAU

ts::SOE
RMAN-06134: host command complete

RMAN-03090: Starting backup at 22-APR-24
RMAN-12016: using channel ORA_DISK_1
RMAN-08008: channel ORA_DISK_1: starting full datafile backup set
RMAN-08010: channel ORA_DISK_1: specifying datafile(s) in backup set
RMAN-08522: input datafile file number=00007 name=+DATA/src11g/datafile/soe.268.1161972789
RMAN-08038: channel ORA_DISK_1: starting piece 1 at 22-APR-24
RMAN-08044: channel ORA_DISK_1: finished piece 1 at 22-APR-24
RMAN-08530: piece handle=/nfsbackup/xtt/6q2oteur_1_1 tag=TAG20240422T174147 comment=NONE
RMAN-08540: channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
RMAN-03091: Finished backup at 22-APR-24

Recovery Manager complete.


--------------------------------------------------------------------
Done backing up incrementals
--------------------------------------------------------------------

/ as sysdba
size of tablespace 4
No. of tablespaces per batch 1
TABLESPACE STRING :'EXAMPLE'
Prepare newscn for Tablespaces: 'EXAMPLE'
DECLARE*
ERROR at line 1:
ORA-20001: TABLESPACE(S) IS READONLY OR,
OFFLINE JUST CONVERT, COPY
ORA-06512: at line 284

####################################################################
Warning:
------
Warnings found in executing /export/home/oracle/xtt/backup_Apr22_Mon_17_41_41_243//xttpreparenextiter.sql
####################################################################
DECLARE*
ERROR at line 1:
ORA-20001: TABLESPACE(S) IS READONLY OR,
OFFLINE JUST CONVERT, COPY
ORA-06512: at line 284
TABLESPACE STRING :'TEST'
Prepare newscn for Tablespaces: 'TEST'
DECLARE*
ERROR at line 1:
ORA-20001: TABLESPACE(S) IS READONLY OR,
OFFLINE JUST CONVERT, COPY
ORA-06512: at line 284

####################################################################
Warning:
------
Warnings found in executing /export/home/oracle/xtt/backup_Apr22_Mon_17_41_41_243//xttpreparenextiter.sql
####################################################################
DECLARE*
ERROR at line 1:
ORA-20001: TABLESPACE(S) IS READONLY OR,
OFFLINE JUST CONVERT, COPY
ORA-06512: at line 284
TABLESPACE STRING :'USERS'
Prepare newscn for Tablespaces: 'USERS'
DECLARE*
ERROR at line 1:
ORA-20001: TABLESPACE(S) IS READONLY OR,
OFFLINE JUST CONVERT, COPY
ORA-06512: at line 284

####################################################################
Warning:
------
Warnings found in executing /export/home/oracle/xtt/backup_Apr22_Mon_17_41_41_243//xttpreparenextiter.sql
####################################################################
DECLARE*
ERROR at line 1:
ORA-20001: TABLESPACE(S) IS READONLY OR,
OFFLINE JUST CONVERT, COPY
ORA-06512: at line 284
TABLESPACE STRING :'SOE'
Prepare newscn for Tablespaces: 'SOE'
DECLARE*
ERROR at line 1:
ORA-20001: TABLESPACE(S) IS READONLY OR,
OFFLINE JUST CONVERT, COPY
ORA-06512: at line 284

####################################################################
Warning:
------
Warnings found in executing /export/home/oracle/xtt/backup_Apr22_Mon_17_41_41_243//xttpreparenextiter.sql
####################################################################
DECLARE*
ERROR at line 1:
ORA-20001: TABLESPACE(S) IS READONLY OR,
OFFLINE JUST CONVERT, COPY
ORA-06512: at line 284
New /export/home/oracle/xtt/xttplan.txt with FROM SCN's generated
info

There is a warning. This can be ignored:

DECLARE*
ERROR at line 1:
ORA-20001: TABLESPACE(S) IS READONLY OR,
OFFLINE JUST CONVERT, COPY
ORA-06512: at line 284

The res.txt file was updated with the final backup:

cat res.txt
#0:::4,2,USERS_4.dbf,0,11590209,0,0,0,USERS,USERS_4.dbf
#0:::5,2,EXAMPLE_5.dbf,0,11590185,0,0,0,EXAMPLE,EXAMPLE_5.dbf
#0:::6,2,TEST_6.dbf,0,11590173,0,0,0,TEST,TEST_6.dbf
#0:::7,2,SOE_7.dbf,0,11590197,0,0,0,SOE,SOE_7.dbf
#1:::6,2,6g2otdm2_1_1,11590173,11594431,0,0,0,TEST_6.dbf,TEST_6.dbf
#1:::4,2,6h2otdm4_1_1,11590209,11594458,0,0,0,USERS_4.dbf,USERS_4.dbf
#1:::7,2,6i2otdm5_1_1,11590197,11594496,0,0,0,SOE_7.dbf,SOE_7.dbf
#1:::5,2,6f2otdm1_1_1,11590185,11594386,0,0,0,EXAMPLE_5.dbf,EXAMPLE_5.dbf
#2:::6,2,6k2oteg7_1_1,11594431,11595542,0,0,0,TEST_6.dbf,TEST_6.dbf
#2:::4,2,6l2oteg9_1_1,11594458,11595563,0,0,0,USERS_4.dbf,USERS_4.dbf
#2:::7,2,6m2otega_1_1,11594496,11595583,0,0,0,SOE_7.dbf,SOE_7.dbf
#2:::5,2,6j2oteg6_1_1,11594386,11595522,0,0,0,EXAMPLE_5.dbf,EXAMPLE_5.dbf
#3:::6,2,6o2oteuo_1_1,11595542,11596158,0,0,0,TEST_6.dbf,TEST_6.dbf
#3:::4,2,6p2oteup_1_1,11595563,11596187,0,0,0,USERS_4.dbf,USERS_4.dbf
#3:::7,2,6q2oteur_1_1,11595583,11596177,0,0,0,SOE_7.dbf,SOE_7.dbf
#3:::5,2,6n2oteun_1_1,11595522,11596167,0,0,0,EXAMPLE_5.dbf,EXAMPLE_5.dbf

We need to transfer the file to the target system:

# on source:
cp -p $HOME/xtt/res.txt /nfsbackup/xtt/copy/
# on target: 
cp -p /nfsbackup/xtt/copy/res.txt $HOME/xtt

Restore the final incremental backup:

cd $HOME/xtt
export TMPDIR=$HOME/xtt
nohup $ORACLE_HOME/perl/bin/perl xttdriver.pl --restore --debug 3 >final.out 2>&1 &
Output
OUTPUT:

============================================================
trace file is /home/oracle/xtt/restore_Apr22_Mon_17_47_59_642//Apr22_Mon_17_47_59_642_.log
=============================================================

--------------------------------------------------------------------
Parsing properties
--------------------------------------------------------------------

Key: tablespaces
Values: EXAMPLE,TEST,USERS,SOE
Key: getfileparallel
Values: 4
Key: dest_datafile_location
Values: +DATAC2
Key: src_scratch_location
Values: /nfsbackup/xtt
Key: rollparallel
Values: 2
Key: parallel
Values: 4
Key: platformid
Values: 2
Key: dest_scratch_location
Values: /nfsbackup/xtt

--------------------------------------------------------------------
Done parsing properties
--------------------------------------------------------------------


--------------------------------------------------------------------
Checking properties
--------------------------------------------------------------------

ARGUMENT tablespaces
ARGUMENT platformid
ARGUMENT backupformat

--------------------------------------------------------------------
Done checking properties
--------------------------------------------------------------------

ORACLE_SID : TGT19C1
ORACLE_HOME : /u02/app/oracle/product/19.0.0.0/dbhome_1
190000

5

6

4

7

YYY: EXAMPLE_5.dbf::5:::1=6n2oteun_1_1 TEST_6.dbf::6:::1=6o2oteuo_1_1 USERS_4.dbf::4:::1=6p2oteup_1_1 SOE_7.dbf::7:::1=6q2oteur_1_1


--------------------------------------------------------------------
Start rollforward
--------------------------------------------------------------------

ROLLFORWARD: Starting DB in nomount mode
ORACLE instance started.
Total System Global Area 7953314304 bytes
Fixed Size 9200128 bytes
Variable Size 2097152000 bytes
Database Buffers 5570035712 bytes
Redo Buffers 276926464 bytes
rdfno 5

BEFORE ROLLPLAN

datafile number : 5

datafile name : +DATAC2/EXAMPLE_5.dbf

AFTER ROLLPLAN

rdfno 7

BEFORE ROLLPLAN

datafile number : 7

datafile name : +DATAC2/SOE_7.dbf

AFTER ROLLPLAN

rdfno 6

BEFORE ROLLPLAN

datafile number : 6

datafile name : +DATAC2/TEST_6.dbf

AFTER ROLLPLAN

sqlplus -L -s "/ as sysdba" @/home/oracle/xtt/restore_Apr22_Mon_17_47_59_642//xxttconv_6q2oteur_1_1_7.sql /nfsbackup/xtt/6q2oteur_1_1 /nfsbackup/xtt 2

CONVERTED BACKUP PIECE/nfsbackup/xtt/xib_6q2oteur_1_1_7
PL/SQL procedure successfully completed.
sqlplus -L -s "/ as sysdba" @/home/oracle/xtt/restore_Apr22_Mon_17_47_59_642//xxttconv_6n2oteun_1_1_5.sql /nfsbackup/xtt/6n2oteun_1_1 /nfsbackup/xtt 2

CONVERTED BACKUP PIECE/nfsbackup/xtt/xib_6n2oteun_1_1_5
PL/SQL procedure successfully completed.
Entering RollForwardAfter applySetDataFile
Done: applyDataFileTo
Done: applyDataFileTo
Done: RestoreSetPiece
Done: RestoreBackupPiece

PL/SQL procedure successfully completed.
rdfno 4

BEFORE ROLLPLAN

datafile number : 4

datafile name : +DATAC2/USERS_4.dbf

AFTER ROLLPLAN

Entering RollForwardAfter applySetDataFile
Done: applyDataFileTo
Done: applyDataFileTo
Done: RestoreSetPiece
Done: RestoreBackupPiece

PL/SQL procedure successfully completed.
sqlplus -L -s "/ as sysdba" @/home/oracle/xtt/restore_Apr22_Mon_17_47_59_642//xxttconv_6o2oteuo_1_1_6.sql /nfsbackup/xtt/6o2oteuo_1_1 /nfsbackup/xtt 2

CONVERTED BACKUP PIECE/nfsbackup/xtt/xib_6o2oteuo_1_1_6
PL/SQL procedure successfully completed.
sqlplus -L -s "/ as sysdba" @/home/oracle/xtt/restore_Apr22_Mon_17_47_59_642//xxttconv_6p2oteup_1_1_4.sql /nfsbackup/xtt/6p2oteup_1_1 /nfsbackup/xtt 2

CONVERTED BACKUP PIECE/nfsbackup/xtt/xib_6p2oteup_1_1_4
PL/SQL procedure successfully completed.
Entering RollForwardAfter applySetDataFile
Done: applyDataFileTo
Done: applyDataFileTo
Done: RestoreSetPiece
Done: RestoreBackupPiece

PL/SQL procedure successfully completed.
Entering RollForwardAfter applySetDataFile
Done: applyDataFileTo
Done: applyDataFileTo
Done: RestoreSetPiece
Done: RestoreBackupPiece

PL/SQL procedure successfully completed.

--------------------------------------------------------------------
End of rollforward phase
--------------------------------------------------------------------

7. Import Metadata​

To import the metadata we use Data Pump Full Transportable Export Import (FTEX).

  • Gather dictionary stats on the source and target to make sure the export runs performant:

    exec dbms_stats.gather_schema_stats('SYS');
    exec dbms_stats.gather_schema_stats('SYSTEM');
    exec dbms_stats.gather_dictionary_stats;
    exec dbms_stats.gather_fixed_objects_stats;
    exec dbms_stats.gather_schema_stats('SYS');
    exec dbms_stats.gather_schema_stats('SYSTEM');
    exec dbms_stats.gather_dictionary_stats;
    exec dbms_stats.gather_fixed_objects_stats;
  • Set streams_pool_size on both databases:

    alter system set streams_pool_size=256m scope=both;
    alter system set streams_pool_size=256m scope=both;
  • Create a Data Pump directory on the target PDB:

    mkdir -p /nfsbackup/xtt/dp
    chmod -R 777 /nfsbackup/xtt/dp/
    export ORACLE_PDB_SID=TGTPDB
    sqlplus "/ as sysdba"
    show pdbs;
    create directory dpxtt as '/nfsbackup/xtt/dp';
    GRANT READ, WRITE ON DIRECTORY dpxtt TO system;
  • Generate transport_datafiles parameter:

    Execute the following on the source database. This will generate a data pump command in the xttplugin.txt file. We are interested in the transport_datafiles line:

    cd $HOME/xtt
    export TMPDIR=$HOME/xtt
    $ORACLE_HOME/perl/bin/perl xttdriver.pl -e
    cat xttplugin.txt
    impdp directory=<DATA_PUMP_DIR> logfile=<tts_imp.log> \
    network_link=<ttslink> transport_full_check=no \
    transport_tablespaces=EXAMPLE,TEST,USERS,SOE \
    transport_datafiles='+DATAC2/USERS_4.dbf','+DATAC2/EXAMPLE_5.dbf','+DATAC2/TEST_6.dbf','+DATAC2/SOE_7.dbf'

There are 2 options to import the metadata.
In the first example we will use a db link and run the import over sqlnet.

  • Create DB Link

    export ORACLE_PDB_SID=TGTPDB
    sqlplus "/ as sysdba"
    SQL> create public database link xttlink connect to system identified by welcome1 using '//host:1521/SRC11G';
  • Test DB Link:

    SQL> select name from v$database@xttlink;
    NAME
    ---------
    SRC11G
  • Create import par file:

    $ cd /nfsbackup/xtt/dp
    $ vi imp.par
  • Add the following parameters. The transport_datafiles parameter you can copy from the xttplugin.txt file from the source we created earlier. The 2 most important parameters are FULL=y and TRANSPORTABLE=always. These 2 parameters initiate a Full Transportable Export Import operation.

    FULL=y
    TRANSPORTABLE=always
    transport_datafiles='+DATAC2/USERS_4.dbf','+DATAC2/EXAMPLE_5.dbf','+DATAC2/TEST_6.dbf','+DATAC2/SOE_7.dbf'
    NETWORK_LINK=xttlink
    PARALLEL=2 # only for 21c and above
    EXCLUDE=TABLE_STATISTICS,INDEX_STATISTICS
    EXCLUDE=SYS_USER # SYS_USER should be excluded as best practice for import in PDB
    EXCLUDE=TABLESPACE:"IN('TEMP')"
    EXCLUDE=SCHEMA:"IN('WMSYS','ORDDATA','SYSTEM','SYS')"
    EXCLUDE=SCHEMA:"IN('SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR')" # exclude spatial users - removed in 19c
    DIRECTORY=xttdir
    LOGFILE=xttimp.log
    METRICS=y
    LOGTIME=all # only available in 12g
    VERSION=12.0 # needs to be specified if the source database compatible init.ora parameter is not set to a value of at least 12.0
  • Run the imp.sh script:

    cd /nfsbackup/xtt/dp
    export ORACLE_PDB_SID=TGTPDB
    nohup impdp system/WELcome##1234 parfile=imp.par >imp.out 2>&1 &
Output
OUTPUT:

;;;
Import: Release 19.0.0.0.0 - Production on Mon Apr 22 18:13:42 2024
Version 19.22.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
22-APR-24 18:13:45.026: ;;; **************************************************************************
22-APR-24 18:13:45.033: ;;; Parfile values:
22-APR-24 18:13:45.043: ;;; parfile: version=12.0
22-APR-24 18:13:45.050: ;;; parfile: logtime=all
22-APR-24 18:13:45.059: ;;; parfile: metrics=Y
22-APR-24 18:13:45.066: ;;; parfile: logfile=xttimp.log
22-APR-24 18:13:45.072: ;;; parfile: directory=dpxtt
22-APR-24 18:13:45.078: ;;; parfile: exclude=TABLE_STATISTICS,INDEX_STATISTICS,SYS_USER,
22-APR-24 18:13:45.086: ;;; _parfile: TABLESPACE:"IN('TEMP')",
22-APR-24 18:13:45.094: ;;; _parfile: SCHEMA:"IN('WMSYS','ORDDATA','SYSTEM','SYS')",
22-APR-24 18:13:45.100: ;;; _parfile: SCHEMA:"IN('SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR')"
22-APR-24 18:13:45.106: ;;; parfile: parallel=2
22-APR-24 18:13:45.112: ;;; parfile: network_link=xttlink
22-APR-24 18:13:45.118: ;;; parfile: transport_datafiles=+DATAC2/USERS_4.dbf,+DATAC2/EXAMPLE_5.dbf,
22-APR-24 18:13:45.125: ;;; _parfile: +DATAC2/TEST_6.dbf,+DATAC2/SOE_7.dbf
22-APR-24 18:13:45.132: ;;; parfile: transportable=always
22-APR-24 18:13:45.138: ;;; parfile: full=Y
22-APR-24 18:13:45.175: ;;; **************************************************************************
22-APR-24 18:13:46.062: Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** parfile=imp.par
22-APR-24 18:13:47.460: W-1 Startup took 1 seconds
22-APR-24 18:13:47.579: W-1 Estimate in progress using BLOCKS method...
22-APR-24 18:13:48.643: W-1 Processing object type DATABASE_EXPORT/PLUGTS_FULL/FULL/PLUGTS_TABLESPACE
22-APR-24 18:13:48.719: W-1 Completed 0 PLUGTS_TABLESPACE objects in 0 seconds
22-APR-24 18:13:48.722: W-1 Processing object type DATABASE_EXPORT/PLUGTS_FULL/PLUGTS_BLK
22-APR-24 18:13:50.942: W-1 Completed 1 PLUGTS_BLK objects in 2 seconds
22-APR-24 18:13:50.957: W-1 Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
22-APR-24 18:13:51.037: W-1 Estimated 1 TABLE_DATA objects in 1 seconds
22-APR-24 18:13:51.037: W-1 Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
22-APR-24 18:13:51.076: W-1 Estimated 32 TABLE_DATA objects in 1 seconds
22-APR-24 18:13:51.076: W-1 Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
22-APR-24 18:13:51.792: W-1 Estimated 3 TABLE_DATA objects in 0 seconds
22-APR-24 18:13:51.792: W-1 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
22-APR-24 18:13:51.857: W-1 Estimated 4 TABLE_DATA objects in 0 seconds
22-APR-24 18:13:51.869: W-1 Total estimation using BLOCKS method: 1.765 MB
22-APR-24 18:13:52.574: W-1 Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
22-APR-24 18:13:54.194: W-1 Completed 1 MARKER objects in 2 seconds
22-APR-24 18:13:54.196: W-1 Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
22-APR-24 18:13:54.238: W-1 Completed 1 MARKER objects in 0 seconds
22-APR-24 18:13:54.241: W-1 Processing object type DATABASE_EXPORT/TABLESPACE
22-APR-24 18:13:54.309: ORA-31684: Object type TABLESPACE:"UNDOTBS1" already exists
22-APR-24 18:13:54.311: W-1 Completed 1 TABLESPACE objects in 0 seconds
22-APR-24 18:13:54.313: W-1 Processing object type DATABASE_EXPORT/PROFILE
22-APR-24 18:13:54.662: W-1 Completed 2 PROFILE objects in 0 seconds
22-APR-24 18:13:54.664: W-1 Processing object type DATABASE_EXPORT/SCHEMA/USER
22-APR-24 18:13:54.941: ORA-31684: Object type USER:"OUTLN" already exists
22-APR-24 18:13:54.941: ORA-31684: Object type USER:"OLAPSYS" already exists
22-APR-24 18:13:54.941: ORA-31684: Object type USER:"MDDATA" already exists
22-APR-24 18:13:54.942: W-1 Completed 13 USER objects in 0 seconds
22-APR-24 18:13:54.943: W-1 Processing object type DATABASE_EXPORT/ROLE
22-APR-24 18:13:55.409: ORA-31684: Object type ROLE:"SELECT_CATALOG_ROLE" already exists
22-APR-24 18:13:55.409: ORA-31684: Object type ROLE:"EXECUTE_CATALOG_ROLE" already exists
22-APR-24 18:13:55.409: ORA-31684: Object type ROLE:"DBFS_ROLE" already exists
22-APR-24 18:13:55.409: ORA-31684: Object type ROLE:"AQ_ADMINISTRATOR_ROLE" already exists
22-APR-24 18:13:55.409: ORA-31684: Object type ROLE:"AQ_USER_ROLE" already exists
22-APR-24 18:13:55.409: ORA-31684: Object type ROLE:"ADM_PARALLEL_EXECUTE_TASK" already exists
22-APR-24 18:13:55.409: ORA-31684: Object type ROLE:"GATHER_SYSTEM_STATISTICS" already exists
22-APR-24 18:13:55.410: ORA-31684: Object type ROLE:"RECOVERY_CATALOG_OWNER" already exists
22-APR-24 18:13:55.410: ORA-31684: Object type ROLE:"SCHEDULER_ADMIN" already exists
22-APR-24 18:13:55.410: ORA-31684: Object type ROLE:"HS_ADMIN_SELECT_ROLE" already exists
22-APR-24 18:13:55.410: ORA-31684: Object type ROLE:"HS_ADMIN_EXECUTE_ROLE" already exists
22-APR-24 18:13:55.410: ORA-31684: Object type ROLE:"HS_ADMIN_ROLE" already exists
22-APR-24 18:13:55.410: ORA-31684: Object type ROLE:"GLOBAL_AQ_USER_ROLE" already exists
22-APR-24 18:13:55.410: ORA-31684: Object type ROLE:"OEM_ADVISOR" already exists
22-APR-24 18:13:55.410: ORA-31684: Object type ROLE:"OEM_MONITOR" already exists
22-APR-24 18:13:55.410: ORA-31684: Object type ROLE:"WM_ADMIN_ROLE" already exists
22-APR-24 18:13:55.410: ORA-31684: Object type ROLE:"JAVAUSERPRIV" already exists
22-APR-24 18:13:55.412: ORA-31684: Object type ROLE:"JAVAIDPRIV" already exists
22-APR-24 18:13:55.412: ORA-31684: Object type ROLE:"JAVASYSPRIV" already exists
22-APR-24 18:13:55.412: ORA-31684: Object type ROLE:"JAVADEBUGPRIV" already exists
22-APR-24 18:13:55.412: ORA-31684: Object type ROLE:"EJBCLIENT" already exists
22-APR-24 18:13:55.412: ORA-31684: Object type ROLE:"JMXSERVER" already exists
22-APR-24 18:13:55.412: ORA-31684: Object type ROLE:"JAVA_ADMIN" already exists
22-APR-24 18:13:55.412: ORA-31684: Object type ROLE:"CTXAPP" already exists
22-APR-24 18:13:55.412: ORA-31684: Object type ROLE:"XDBADMIN" already exists
22-APR-24 18:13:55.412: ORA-31684: Object type ROLE:"XDB_SET_INVOKER" already exists
22-APR-24 18:13:55.412: ORA-31684: Object type ROLE:"AUTHENTICATEDUSER" already exists
22-APR-24 18:13:55.412: ORA-31684: Object type ROLE:"XDB_WEBSERVICES" already exists
22-APR-24 18:13:55.412: ORA-31684: Object type ROLE:"XDB_WEBSERVICES_WITH_PUBLIC" already exists
22-APR-24 18:13:55.412: ORA-31684: Object type ROLE:"XDB_WEBSERVICES_OVER_HTTP" already exists
22-APR-24 18:13:55.412: ORA-31684: Object type ROLE:"ORDADMIN" already exists
22-APR-24 18:13:55.414: W-1 Completed 44 ROLE objects in 1 seconds
22-APR-24 18:13:55.416: W-1 Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
22-APR-24 18:14:02.812: W-1 Completed 5 PROC_SYSTEM_GRANT objects in 7 seconds
22-APR-24 18:14:02.854: W-1 Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
22-APR-24 18:14:03.522: W-1 Completed 130 SYSTEM_GRANT objects in 1 seconds
22-APR-24 18:14:03.525: W-1 Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
22-APR-24 18:14:04.068: W-1 Completed 47 ROLE_GRANT objects in 1 seconds
22-APR-24 18:14:04.071: W-1 Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
22-APR-24 18:14:04.171: W-1 Completed 13 DEFAULT_ROLE objects in 0 seconds
22-APR-24 18:14:04.174: W-1 Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
22-APR-24 18:14:04.290: W-1 Completed 5 TABLESPACE_QUOTA objects in 0 seconds
22-APR-24 18:14:04.291: W-1 Processing object type DATABASE_EXPORT/RESOURCE_COST
22-APR-24 18:14:04.346: W-1 Completed 1 RESOURCE_COST objects in 0 seconds
22-APR-24 18:14:04.348: W-1 Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
22-APR-24 18:14:04.425: W-1 Completed 1 TRUSTED_DB_LINK objects in 0 seconds
22-APR-24 18:14:04.428: W-1 Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
22-APR-24 18:14:04.609: W-1 Completed 5 SEQUENCE objects in 0 seconds
22-APR-24 18:14:04.612: W-1 Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
22-APR-24 18:14:04.790: ORA-31684: Object type DIRECTORY:"DATA_PUMP_DIR" already exists
22-APR-24 18:14:04.790: ORA-31684: Object type DIRECTORY:"XMLDIR" already exists
22-APR-24 18:14:04.791: W-1 Completed 8 DIRECTORY objects in 0 seconds
22-APR-24 18:14:04.796: W-1 Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
22-APR-24 18:14:05.014: W-1 Completed 8 OBJECT_GRANT objects in 1 seconds
22-APR-24 18:14:05.019: W-1 Processing object type DATABASE_EXPORT/CONTEXT
22-APR-24 18:14:05.232: ORA-31684: Object type CONTEXT:"GLOBAL_AQCLNTDB_CTX" already exists
22-APR-24 18:14:05.232: ORA-31684: Object type CONTEXT:"DBFS_CONTEXT" already exists
22-APR-24 18:14:05.232: ORA-31684: Object type CONTEXT:"REGISTRY$CTX" already exists
22-APR-24 18:14:05.232: ORA-31684: Object type CONTEXT:"LT_CTX" already exists
22-APR-24 18:14:05.232: ORA-31684: Object type CONTEXT:"DR$APPCTX" already exists
22-APR-24 18:14:05.233: W-1 Completed 7 CONTEXT objects in 1 seconds
22-APR-24 18:14:05.237: W-1 Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM
22-APR-24 18:14:15.045: W-1 Completed 771 SYNONYM objects in 10 seconds
22-APR-24 18:14:15.081: W-1 Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM
22-APR-24 18:14:15.523: W-1 Completed 12 SYNONYM objects in 1 seconds
22-APR-24 18:14:15.528: W-1 Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
22-APR-24 18:14:18.736: W-1 Completed 3 PROCACT_SYSTEM objects in 3 seconds
22-APR-24 18:14:18.741: W-1 Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
22-APR-24 18:14:20.168: W-1 Completed 17 PROCOBJ objects in 5 seconds
22-APR-24 18:14:20.170: W-1 Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
22-APR-24 18:14:50.663: ORA-39083: Object type PROCACT_SYSTEM failed to create with error:
ORA-04042: procedure, function, package, or package body does not exist

Failing sql is:
BEGIN
SYS.DBMS_UTILITY.EXEC_DDL_STATEMENT('GRANT EXECUTE ON DBMS_DEFER_SYS TO "DBA"');COMMIT; END;
22-APR-24 18:14:50.676: W-1 Completed 4 PROCACT_SYSTEM objects in 31 seconds
22-APR-24 18:14:50.678: W-1 Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
22-APR-24 18:14:55.506: W-1 Completed 13 PROCACT_SCHEMA objects in 5 seconds
22-APR-24 18:14:55.507: W-1 Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE
22-APR-24 18:14:57.112: W-1 Completed 1 TABLE objects in 7 seconds
22-APR-24 18:14:57.562: W-1 . . imported "SYS"."KU$_EXPORT_USER_MAP" 32 rows in 0 seconds using network link
22-APR-24 18:14:58.038: W-2 Startup took 0 seconds
22-APR-24 18:14:58.314: W-1 Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
22-APR-24 18:14:59.060: W-1 Completed 1 MARKER objects in 2 seconds
22-APR-24 18:14:59.067: W-1 Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
22-APR-24 18:15:05.131: W-1 Completed 32 TABLE objects in 7 seconds
22-APR-24 18:15:06.071: W-2 . . imported "WMSYS"."E$CONSTRAINTS_TABLE" 0 rows in 0 seconds using automatic
22-APR-24 18:15:06.085: W-1 . . imported "SYS"."AMGT$DP$AUD$" 75 rows in 1 seconds using network link
22-APR-24 18:15:06.413: W-1 . . imported "SYS"."AMGT$DP$DAM_CLEANUP_EVENTS$" 0 rows in 0 seconds using network link
22-APR-24 18:15:06.423: W-2 . . imported "SYS"."AMGT$DP$DAM_CLEANUP_JOBS$" 0 rows in 0 seconds using automatic
22-APR-24 18:15:06.674: W-2 . . imported "SYS"."NET$_ACL" 0 rows in 0 seconds using automatic
22-APR-24 18:15:06.770: W-1 . . imported "SYS"."AMGT$DP$DAM_CONFIG_PARAM$" 10 rows in 0 seconds using network link
22-APR-24 18:15:07.118: W-2 . . imported "SYS"."WALLET$_ACL" 0 rows in 0 seconds using automatic
22-APR-24 18:15:07.424: W-1 . . imported "WMSYS"."E$ENV_VARS" 3 rows in 0 seconds using network link
22-APR-24 18:15:07.771: W-2 . . imported "WMSYS"."E$EVENTS_INFO" 12 rows in 0 seconds using network link
22-APR-24 18:15:07.837: W-1 . . imported "WMSYS"."E$HINT_TABLE" 72 rows in 0 seconds using network link
22-APR-24 18:15:08.290: W-2 . . imported "WMSYS"."E$NEXTVER_TABLE" 1 rows in 1 seconds using network link
22-APR-24 18:15:08.543: W-1 . . imported "WMSYS"."E$VERSION_HIERARCHY_TABLE" 1 rows in 0 seconds using network link
22-APR-24 18:15:08.725: W-2 . . imported "WMSYS"."E$WORKSPACES_TABLE" 1 rows in 0 seconds using network link
22-APR-24 18:15:08.881: W-2 . . imported "WMSYS"."E$BATCH_COMPRESSIBLE_TABLES" 0 rows in 0 seconds using network link
22-APR-24 18:15:08.887: W-1 . . imported "WMSYS"."E$WORKSPACE_PRIV_TABLE" 8 rows in 0 seconds using network link
22-APR-24 18:15:09.153: W-1 . . imported "WMSYS"."E$CONS_COLUMNS" 0 rows in 0 seconds using network link
22-APR-24 18:15:09.166: W-2 . . imported "WMSYS"."E$INSTEADOF_TRIGS_TABLE" 0 rows in 0 seconds using network link
22-APR-24 18:15:09.293: W-1 . . imported "WMSYS"."E$LOCKROWS_INFO" 0 rows in 0 seconds using network link
22-APR-24 18:15:09.308: W-2 . . imported "WMSYS"."E$MODIFIED_TABLES" 0 rows in 0 seconds using network link
22-APR-24 18:15:09.383: W-1 . . imported "WMSYS"."E$MP_GRAPH_WORKSPACES_TABLE" 0 rows in 0 seconds using network link
22-APR-24 18:15:09.455: W-2 . . imported "WMSYS"."E$MP_PARENT_WORKSPACES_TABLE" 0 rows in 0 seconds using network link
22-APR-24 18:15:09.601: W-1 . . imported "WMSYS"."E$NESTED_COLUMNS_TABLE" 0 rows in 0 seconds using network link
22-APR-24 18:15:09.616: W-2 . . imported "WMSYS"."E$REMOVED_WORKSPACES_TABLE" 0 rows in 0 seconds using network link
22-APR-24 18:15:09.810: W-1 . . imported "WMSYS"."E$RESOLVE_WORKSPACES_TABLE" 0 rows in 0 seconds using network link
22-APR-24 18:15:09.823: W-2 . . imported "WMSYS"."E$RIC_LOCKING_TABLE" 0 rows in 0 seconds using network link
22-APR-24 18:15:10.002: W-1 . . imported "WMSYS"."E$RIC_TABLE" 0 rows in 0 seconds using network link
22-APR-24 18:15:10.026: W-2 . . imported "WMSYS"."E$RIC_TRIGGERS_TABLE" 0 rows in 0 seconds using network link
22-APR-24 18:15:10.118: W-1 . . imported "WMSYS"."E$UDTRIG_DISPATCH_PROCS" 0 rows in 0 seconds using network link
22-APR-24 18:15:10.333: W-2 . . imported "WMSYS"."E$UDTRIG_INFO" 0 rows in 0 seconds using network link
22-APR-24 18:15:10.434: W-1 . . imported "WMSYS"."E$VERSION_TABLE" 0 rows in 0 seconds using network link
22-APR-24 18:15:10.520: W-2 . . imported "WMSYS"."E$VT_ERRORS_TABLE" 0 rows in 0 seconds using network link
22-APR-24 18:15:10.579: W-1 . . imported "WMSYS"."E$WORKSPACE_SAVEPOINTS_TABLE" 0 rows in 0 seconds using network link
22-APR-24 18:15:11.004: W-1 Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
22-APR-24 18:15:12.863: W-1 Completed 3 TABLE objects in 2 seconds
22-APR-24 18:15:12.874: W-1 Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/PROCEDURE
22-APR-24 18:15:14.125: W-1 Completed 1 PROCEDURE objects in 2 seconds
22-APR-24 18:15:14.458: W-1 . . imported "SYS"."AMGT$DP$FGA_LOG$FOR_EXPORT" 0 rows in 0 seconds using network link
22-APR-24 18:15:14.510: W-2 . . imported "WMSYS"."E$EXP_MAP" 0 rows in 0 seconds using network link
22-APR-24 18:15:14.922: W-1 Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/OPTION_PACKAGE/PACKAGE_SPEC
22-APR-24 18:15:15.056: W-1 Completed 2 PACKAGE objects in 2 seconds
22-APR-24 18:15:15.076: W-1 Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/OPTION_PACKAGE/PACKAGE_BODY
22-APR-24 18:15:15.488: W-1 Completed 1 PACKAGE_BODY objects in 0 seconds
22-APR-24 18:15:15.490: W-1 Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOU/MARKER
22-APR-24 18:15:16.708: W-1 Completed 1 MARKER objects in 1 seconds
22-APR-24 18:15:16.712: W-1 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
22-APR-24 18:15:20.853: W-1 Completed 28 TABLE objects in 4 seconds
22-APR-24 18:15:21.139: W-1 . . imported "OUTLN"."OL$HINTS" 0 rows in 0 seconds using network link
22-APR-24 18:15:21.148: W-2 . . imported "OUTLN"."OL$" 0 rows in 0 seconds using network link
22-APR-24 18:15:21.242: W-1 . . imported "OUTLN"."OL$NODES" 0 rows in 0 seconds using network link
22-APR-24 18:15:21.251: W-2 . . imported "OWBSYS"."OWBRTPS" 0 rows in 0 seconds using network link
22-APR-24 18:15:21.638: W-1 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
22-APR-24 18:15:21.889: W-1 Completed 11 OBJECT_GRANT objects in 0 seconds
22-APR-24 18:15:21.892: W-1 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
22-APR-24 18:15:22.490: W-1 Completed 88 COMMENT objects in 1 seconds
22-APR-24 18:15:22.493: W-1 Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/PACKAGE_SPEC
22-APR-24 18:15:22.821: W-1 Completed 1 PACKAGE objects in 0 seconds
22-APR-24 18:15:22.824: W-1 Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/PROCEDURE
22-APR-24 18:15:22.942: W-1 Completed 0 PACKAGE objects in 0 seconds
22-APR-24 18:15:22.945: W-1 Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE
22-APR-24 18:15:23.222: W-1 Completed 0 PACKAGE objects in 1 seconds
22-APR-24 18:15:23.226: W-1 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
22-APR-24 18:15:25.129: W-1 Completed 32 INDEX objects in 3 seconds
22-APR-24 18:15:25.503: W-1 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
22-APR-24 18:15:25.983: W-1 Completed 1 INDEX objects in 1 seconds
22-APR-24 18:15:25.987: W-1 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
22-APR-24 18:15:27.336: W-1 Completed 22 CONSTRAINT objects in 2 seconds
22-APR-24 18:15:27.360: W-1 Processing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW
22-APR-24 18:15:28.031: W-1 Completed 1 VIEW objects in 1 seconds
22-APR-24 18:15:28.035: W-1 Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE/PACKAGE_BODY
22-APR-24 18:15:28.790: W-1 Completed 1 PACKAGE_BODY objects in 1 seconds
22-APR-24 18:15:28.793: W-1 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
22-APR-24 18:15:29.514: W-1 Completed 16 REF_CONSTRAINT objects in 1 seconds
22-APR-24 18:15:29.954: W-1 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_TABLE_ACTION
22-APR-24 18:15:30.129: W-1 Processing object type DATABASE_EXPORT/SCHEMA/DIMENSION
22-APR-24 18:15:30.507: W-1 Completed 5 DIMENSION objects in 1 seconds
22-APR-24 18:15:30.508: W-1 Processing object type DATABASE_EXPORT/END_PLUGTS_BLK
22-APR-24 18:15:30.621: W-1 Completed 1 PLUGTS_BLK objects in 0 seconds
22-APR-24 18:15:30.623: W-1 Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
22-APR-24 18:15:45.200: W-1 Completed 1 MARKER objects in 15 seconds
22-APR-24 18:15:45.232: W-1 Processing object type DATABASE_EXPORT/AUDIT
22-APR-24 18:15:45.392: W-1 Completed 29 AUDIT objects in 5 seconds
22-APR-24 18:15:45.396: W-1 Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
22-APR-24 18:15:49.045: W-1 Completed 1 MARKER objects in 4 seconds
22-APR-24 18:15:50.344: W-2 Completed 1 DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA objects in 0 seconds
22-APR-24 18:15:50.347: W-2 Completed 32 DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA objects in 5 seconds
22-APR-24 18:15:50.351: W-2 Completed 2 DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA objects in 0 seconds
22-APR-24 18:15:50.354: W-2 Completed 4 DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA objects in 0 seconds
22-APR-24 18:15:50.705: Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 43 error(s) at Mon Apr 22 18:15:50 2024 elapsed 0 00:02:07
  • Check the logfile for errors:

    [oracle@eccjc2n3 dp]$ grep -i ORA- xttimp.log | grep -v 'already exists'
    22-APR-24 18:14:50.663: ORA-39083: Object type PROCACT_SYSTEM failed to create with error:
    ORA-04042: procedure, function, package, or package body does not exist

    We only have "already exists" errors. There is only 1 error related to PROCACT_SYSTEM

    This is ok and expected according to the following support note:
    ORA-39083 And ORA-04042 Errors On DBMS_DEFER_SYS When Importing Into 12.2 Database (Doc ID 2335846.1)

Option 2: Export / Import​

In case we don't have direct access from the target to the source to create the database link we can also run the export and import operation in 2 separate steps:

  • Create directory

    We also need to create a directory on the source:

    sqlplus "/ as sysdba"
    create directory dpxtt as '/nfsbackup/xtt/dp';
    GRANT READ, WRITE ON DIRECTORY dpxtt TO system;
  • Create parameter files:

    $ cd /nfsbackup/xtt/dp
    $ vi exp.par
    FULL=y
    TRANSPORTABLE=always
    PARALLEL=2 # only for 21c and above
    EXCLUDE=TABLE_STATISTICS,INDEX_STATISTICS
    EXCLUDE=SYS_USER # SYS_USER should be excluded as best practice for import in PDB
    EXCLUDE=TABLESPACE:"IN('TEMP')"
    DIRECTORY=xttdir
    LOGFILE=xttexp.log
    METRICS=y
    #LOGTIME=all # only available in 12g
    VERSION=12.0 # needs to be specified if the source database compatible init.ora parameter is not set to a value of at least 12.0
  • Next create import parameter file:

    vi imp.par
    transport_datafiles='+DATAC2/USERS_4.dbf','+DATAC2/EXAMPLE_5.dbf','+DATAC2/TEST_6.dbf','+DATAC2/SOE_7.dbf'
    PARALLEL=2 # only for 21c and above
    EXCLUDE=SCHEMA:"IN('WMSYS','ORDDATA','SYSTEM','SYS')"
    EXCLUDE=SCHEMA:"IN('SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR')" # exclude spatial users - removed in 19c
    DIRECTORY=xttdir
    LOGFILE=xttimp.log
    METRICS=y
    LOGTIME=all
  • Run the export on the source:

    cd /nfsbackup/xtt/dp
    nohup expdp system/welcome1 parfile=exp.par >exp.out 2>&1 &
  • Check for errors:

    grep -i ORA- xttexp.log 
  • Run the import on the target:

    cd /nfsbackup/xtt/dp
    export ORACLE_PDB_SID=TGTPDB
    nohup impdp system/WELcome##1234 parfile=imp.par >imp.out 2>&1 &
  • Check for errors:

    grep -i ORA- xttimp.log | grep -v 'already exists'

8. Validate​

  • Recompile and Check Invalid Objects

    SQL> @?/rdbms/admin/utlrp.sql
    SQL> select count(1) from dba_objects where status!='VALID';
    COUNT(1)
    ----------
    0
  • Check Datafiles

    RMAN> connect target /

    connected to target database: TGT19C:TGTPDB (DBID=1944147887)
    using target database control file instead of recovery catalo

    RMAN> validate database check logical;
Output
OUTPUT: 

Starting validate at 22-APR-24
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00028 name=+DATAC2/example_5.dbf
input datafile file number=00031 name=+DATAC2/users_4.dbf
input datafile file number=00029 name=+DATAC2/soe_7.dbf
input datafile file number=00023 name=+DATAC2/TGT19C/16B0B12081F5A501E0636309038A0B59/DATAFILE/system.289.1166971727
input datafile file number=00024 name=+DATAC2/TGT19C/16B0B12081F5A501E0636309038A0B59/DATAFILE/sysaux.290.1166971727
input datafile file number=00025 name=+DATAC2/TGT19C/16B0B12081F5A501E0636309038A0B59/DATAFILE/undotbs1.291.1166971727
input datafile file number=00026 name=+DATAC2/TGT19C/16B0B12081F5A501E0636309038A0B59/DATAFILE/undo_2.403.1166971727
input datafile file number=00030 name=+DATAC2/test_6.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:35
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
23 OK 0 26832 76800 12097909
File Name: +DATAC2/TGT19C/16B0B12081F5A501E0636309038A0B59/DATAFILE/system.289.1166971727
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 111
Index 0 106
Other 0 49751

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
24 OK 0 14122 76800 12096877
File Name: +DATAC2/TGT19C/16B0B12081F5A501E0636309038A0B59/DATAFILE/sysaux.290.1166971727
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 140
Index 0 159
Other 0 62379

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
25 OK 0 45057 76800 12096655
File Name: +DATAC2/TGT19C/16B0B12081F5A501E0636309038A0B59/DATAFILE/undotbs1.291.1166971727
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 31743

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
26 OK 0 74465 76800 12097908
File Name: +DATAC2/TGT19C/16B0B12081F5A501E0636309038A0B59/DATAFILE/undo_2.403.1166971727
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 2335

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
28 OK 0 1304137 1310720 11596151
File Name: +DATAC2/example_5.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 5603
Index 0 126
Other 0 854

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
29 OK 0 254905 655360 1732291
File Name: +DATAC2/soe_7.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 395570
Other 0 4885

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
30 OK 0 12665 12800 3242173
File Name: +DATAC2/test_6.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 5
Index 0 0
Other 0 130

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
31 OK 0 73698 706720 4381242
File Name: +DATAC2/users_4.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 627663
Index 0 2230
Other 0 3129

Finished validate at 22-APR-24
  • Check data on the source:

    col owner format a10
    col object_type format a20
    Select owner, object_type, count(1) from dba_objects where owner in ('MACSDATA','ADLDEMO','SOE') group by owner, object_type order by 1;
    SOURCE:
    OWNER OBJECT_TYPE COUNT(1)
    ---------- -------------------- ----------
    ADLDEMO DIMENSION 5
    ADLDEMO INDEX 6
    ADLDEMO TABLE 13
    ADLDEMO TABLE PARTITION 56
    ADLDEMO VIEW 1
    MACSDATA TABLE 1
    SOE INDEX 27
    SOE SEQUENCE 5
    SOE TABLE 12

    9 rows selected.
  • Also check on Target

    col owner format a10
    col object_type format a20
    Select owner, object_type, count(1) from dba_objects where owner in ('MACSDATA','ADLDEMO','SOE') group by owner, object_type order by 1;
    OWNER      OBJECT_TYPE            COUNT(1)
    ---------- -------------------- ----------
    ADLDEMO DIMENSION 5
    ADLDEMO INDEX 6
    ADLDEMO TABLE 13
    ADLDEMO TABLE PARTITION 56
    ADLDEMO VIEW 1
    MACSDATA TABLE 1
    SOE INDEX 27
    SOE SEQUENCE 5
    SOE TABLE 12

    9 rows selected.
  • Let's check the macsdata table on the source:

    SQL> col name format a10
    SQL> select * from macsdata.mactab1;
            ID NAME
    ---------- ----------
    1 MACS
    2 DATA
    3 BEFORE
    4 FINAL
    5 BACKUP
  • On target:

    SQL> col name format a10
    SQL> select * from macsdata.mactab1;
            ID NAME
    ---------- ----------
    1 MACS
    2 DATA
    3 BEFORE
    4 FINAL
    5 BACKUP
  • Check tablespaces on the target database:

    SQL> select tablespace_name, status from dba_tablespaces;
    TABLESPACE_NAME                STATUS
    ------------------------------ ---------
    SYSTEM ONLINE
    SYSAUX ONLINE
    UNDOTBS1 ONLINE
    TEMP ONLINE
    UNDO_2 ONLINE
    USERS ONLINE
    EXAMPLE ONLINE
    SOE ONLINE
    TEST ONLINE
  • Check datafiles on the target database:

    col file_name format a80
    col tablespace_name format a20
    set pages 100
    set lines 120
    select file_name, tablespace_name, status from dba_data_files;
    FILE_NAME                                                                        TABLESPACE_NAME      STATUS
    -------------------------------------------------------------------------------- -------------------- ---------
    +DATAC2/TGT19C/16B0B12081F5A501E0636309038A0B59/DATAFILE/system.289.1166971727 SYSTEM AVAILABLE
    +DATAC2/TGT19C/16B0B12081F5A501E0636309038A0B59/DATAFILE/sysaux.290.1166971727 SYSAUX AVAILABLE
    +DATAC2/TGT19C/16B0B12081F5A501E0636309038A0B59/DATAFILE/undotbs1.291.1166971727 UNDOTBS1 AVAILABLE
    +DATAC2/TGT19C/16B0B12081F5A501E0636309038A0B59/DATAFILE/undo_2.403.1166971727 UNDO_2 AVAILABLE
    +DATAC2/users_4.dbf USERS AVAILABLE
    +DATAC2/example_5.dbf EXAMPLE AVAILABLE
    +DATAC2/soe_7.dbf SOE AVAILABLE
    +DATAC2/test_6.dbf TEST AVAILABLE

    The datafiles are plugged in to the database but they are still in the wrong location.

  • Check in ASM:

    asmcmd
    cd DATAC2
    ...
    DATAFILE HIGH COARSE APR 22 18:00:00 N example_5.dbf => +DATAC2/TGT19C/DATAFILE/EXAMPLE.429.1166979579
    DATAFILE HIGH COARSE APR 22 18:00:00 N soe_7.dbf => +DATAC2/TGT19C/DATAFILE/SOE.430.1166979637
    DATAFILE HIGH COARSE APR 22 18:00:00 N test_6.dbf => +DATAC2/TGT19C/DATAFILE/TEST.431.1166979631
    DATAFILE HIGH COARSE APR 22 18:00:00 N users_4.dbf => +DATAC2/TGT19C/DATAFILE/USERS.292.1166979579
    ...
  • This can be fixed by moving the datafiles:

    export ORACLE_PDB_SID=TGTPDB
    . TGT19C.env
    sqlplus "/ as sysdba"
    show pdbs;
    alter database move datafile '+DATAC2/users_4.dbf';
    col file_name format a80
    col tablespace_name format a20
    set pages 100
    set lines 120
    select file_name, tablespace_name, status from dba_data_files;
    FILE_NAME                                                                        TABLESPACE_NAME      STATUS
    -------------------------------------------------------------------------------- -------------------- ---------
    +DATAC2/TGT19C/16B0B12081F5A501E0636309038A0B59/DATAFILE/system.289.1166971727 SYSTEM AVAILABLE
    +DATAC2/TGT19C/16B0B12081F5A501E0636309038A0B59/DATAFILE/sysaux.290.1166971727 SYSAUX AVAILABLE
    +DATAC2/TGT19C/16B0B12081F5A501E0636309038A0B59/DATAFILE/undotbs1.291.1166971727 UNDOTBS1 AVAILABLE
    +DATAC2/TGT19C/16B0B12081F5A501E0636309038A0B59/DATAFILE/undo_2.403.1166971727 UNDO_2 AVAILABLE
    +DATAC2/TGT19C/16B0B12081F5A501E0636309038A0B59/DATAFILE/users.440.1166987077 USERS AVAILABLE
    +DATAC2/example_5.dbf EXAMPLE AVAILABLE
    +DATAC2/soe_7.dbf SOE AVAILABLE
    +DATAC2/test_6.dbf TEST AVAILABLE

    If you also check again the ASM output you will see the link for the users datafile is gone now. But in our case we also want to encrypt the datafile so we can encrypt and move in one operation.

9. Encryption​

  • Let's check the tablespace encryption. After the restore from the unencrypted source the tablespaces are also unencrypted on the target:

    select tablespace_name, status, encrypted from dba_tablespaces;
    TABLESPACE_NAME      STATUS    ENC
    -------------------- --------- ---
    SYSTEM ONLINE YES
    SYSAUX ONLINE YES
    UNDOTBS1 ONLINE YES
    TEMP ONLINE YES
    UNDO_2 ONLINE YES
    USERS ONLINE NO
    EXAMPLE ONLINE NO
    SOE ONLINE NO
    TEST ONLINE NO
  • To get more information also the following view can be used:

    select * from V$ENCRYPTED_TABLESPACES;
  • Encrypt the tablespaces. This is an online operation:

    -- Default AES128
    -- encryption algorythm can also be specified
    -- alter tablespace example encryption online using 'AES128' encrypt;

    alter tablespace example encryption online encrypt;
    alter tablespace soe encryption online encrypt;
    alter tablespace test encryption online encrypt;
    alter tablespace users encryption online encrypt;
  • Check again the tablespace encryption status:

    select tablespace_name, status, encrypted from dba_tablespaces;
    TABLESPACE_NAME      STATUS    ENC
    -------------------- --------- ---
    SYSTEM ONLINE YES
    SYSAUX ONLINE YES
    UNDOTBS1 ONLINE YES
    TEMP ONLINE YES
    UNDO_2 ONLINE YES
    USERS ONLINE YES
    EXAMPLE ONLINE YES
    SOE ONLINE YES
    TEST ONLINE YES
  • Check the datafiles:

    col file_name format a80
    col tablespace_name format a20
    set pages 100
    set lines 120
    select file_name, tablespace_name, status from dba_data_files;
    FILE_NAME                                                                        TABLESPACE_NAME      STATUS
    -------------------------------------------------------------------------------- -------------------- ---------
    +DATAC2/TGT19C/16B0B12081F5A501E0636309038A0B59/DATAFILE/system.289.1166971727 SYSTEM AVAILABLE
    +DATAC2/TGT19C/16B0B12081F5A501E0636309038A0B59/DATAFILE/sysaux.290.1166971727 SYSAUX AVAILABLE
    +DATAC2/TGT19C/16B0B12081F5A501E0636309038A0B59/DATAFILE/undotbs1.291.1166971727 UNDOTBS1 AVAILABLE
    +DATAC2/TGT19C/16B0B12081F5A501E0636309038A0B59/DATAFILE/undo_2.403.1166971727 UNDO_2 AVAILABLE
    +DATAC2/TGT19C/16B0B12081F5A501E0636309038A0B59/DATAFILE/users.431.1166987531 USERS AVAILABLE
    +DATAC2/TGT19C/16B0B12081F5A501E0636309038A0B59/DATAFILE/example.292.1166987349 EXAMPLE AVAILABLE
    +DATAC2/TGT19C/16B0B12081F5A501E0636309038A0B59/DATAFILE/soe.429.1166987463 SOE AVAILABLE
    +DATAC2/TGT19C/16B0B12081F5A501E0636309038A0B59/DATAFILE/test.430.1166987521 TEST AVAILABLE
  • The files in ASM are also corrected. They are now stored in the PDB directory:

    asmcmd
    cd DATAC2
    ls -l
    #CDB Directory
    cd +DATAC2/TGT19C/DATAFILE
    ls -l
    #PDB Directory
    [grid@eccjc2n3 ~]$ asmcmd
    ASMCMD> cd DATAC2
    ASMCMD> ls -l
    Type Redund Striped Time Sys Name
    Y ASM/
    N SWING_WHF_LHR/
    N TEST1/
    N TGT19C/
    N WINTMP/
    Y lhr1465clu02001/
    PASSWORD HIGH COARSE FEB 19 07:00:00 N orapwasm => +DATAC2/ASM/PASSWORD/pwdasm.256.1161330457
    PASSWORD HIGH COARSE FEB 19 07:00:00 N orapwasm_backup => +DATAC2/ASM/PASSWORD/pwdasm.257.1161330671
    PARAMETERFILE HIGH COARSE FEB 27 13:00:00 N spfileSWING1.ora => +DATAC2/SWING_WHF_LHR/PARAMETERFILE/spfile.300.1162041179

    ASMCMD> cd +DATAC2/TGT19C/DATAFILE
    ASMCMD> ls -l
    Type Redund Striped Time Sys Name
    DATAFILE HIGH COARSE APR 22 17:00:00 Y SYSAUX.337.1166485017
    DATAFILE HIGH COARSE APR 22 17:00:00 Y SYSTEM.417.1166485039
    DATAFILE HIGH COARSE APR 22 17:00:00 Y UNDOTBS1.398.1166485057
    DATAFILE HIGH COARSE APR 22 17:00:00 Y UNDOTBS2.335.1166485017
    DATAFILE HIGH COARSE APR 22 17:00:00 Y USERS.399.1166485075

    ASMCMD> cd +DATAC2/TGT19C/16B0B12081F5A501E0636309038A0B59/DATAFILE
    ASMCMD> ls -l
    Type Redund Striped Time Sys Name
    DATAFILE HIGH COARSE APR 22 19:00:00 Y EXAMPLE.292.1166987349
    DATAFILE HIGH COARSE APR 22 19:00:00 Y SOE.429.1166987463
    DATAFILE HIGH COARSE APR 22 17:00:00 Y SYSAUX.290.1166971727
    DATAFILE HIGH COARSE APR 22 17:00:00 Y SYSTEM.289.1166971727
    DATAFILE HIGH COARSE APR 22 19:00:00 Y TEST.430.1166987521
    DATAFILE HIGH COARSE APR 22 17:00:00 Y UNDOTBS1.291.1166971727
    DATAFILE HIGH COARSE APR 22 17:00:00 Y UNDO_2.403.1166971727
    DATAFILE HIGH COARSE APR 22 19:00:00 Y USERS.431.1166987531

10. Post Tasks​

The following tasks should be executed after the migration:

  • Gather statistics on the target
    • Dictionary
    • Table
  • Backup
  • Optional restart PDB and check violations:

    alter pluggable database TGTPDB close immediate instances=all;
    alter pluggable database TGTPDB open instances=all;
    select name, type, status, cause, message, action from PDB_PLUG_IN_VIOLATIONS where status != 'RESOLVED';
  • Optional change tablespaces back to read write on the source:

    alter tablespace TEST read write;
    alter tablespace EXAMPLE read write;
    alter tablespace SOE read write;
    alter tablespace USERS read write;

11. Cleanup​

  • Drop the $HOME/xtt directories
  • Remove the Files and NFS Share
  • Drop the public database link in the target db

    drop public database link xttlink;

Troubleshooting​

Conclusion​

In this guide we used the XTTSv4 perl scripts to successfully migrate a database across different platform/endian from SPARC Solaris to Exadata Cloud@Customer.

Additional Resources​