📔
migration guideCross 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
COMPATIBLEparameter 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
- No migration to Autonomous Database
- TDE encrypted source database
- No support for windows (source or target)
- Transportable Tablespace (TTS) Restrictions and Limitations: Details, Reference, and Version Where Applicable (Doc ID 1454872.1)
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 | |
|---|---|---|
| Platform | Oracle SPARC | Exadata C@C |
| Operating System | Solaris 11.4 | Oracle Linux 8 |
| DB Version | 11.2.0.4 | 19.22 |
| Multitenant | non-CDB | PDB |
| High Availability | Single Instance | 2 node RAC |
| Disaster Recovery | - | - |
| Storage Location | ASM | ASM |
| DB Name | SRC11G | TGT19C |
| PDB | - | TGTPDB |
The following table lists different parameters and settings for the source and target database. The Gather Database Info section in the source and target database chapter shows in detail how to gather the relevant information:
SRC11G | TGTPDB | |
|---|---|---|
| Platform ID | 2 | 13 |
| Tablespaces | USERS,EXAMPLE,TEST,SOE | |
| Timezone File Version | 14 | 42 |
| DB Timezone | +00:00 | -07:00 |
| Characterset | WE8MSWIN1252 | WE8MSWIN1252 |
| National Characterset | AL16UTF16 | AL16UTF16 |
| TDE Encryption | No | Yes |
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
-----------
2To 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_destNAME 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 NOalter session set container=TGTPDB;select dbtimezone from dual;DBTIME
------
-07:00We 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 AL16UTF16SQL> 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 AL16UTF16noteIn 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
- Make sure install the latest Data Pump Bundle Patch on the target:
Change dbtimezone
-
Change dbtimezone to match source database:
alter database set time_zone='+00:00';noteAfter 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
oracleuser on the source and target.mkdir -p /nfsbackup/xtt
chmod 777 /nfsbackup/xtt
Perl Scripts
-
Download
rman_xttconvert_VER4.3.zipfrom Doc ID 2471245.1 -
Install
rman_xttconvert_VER4.3.zipscript on the source asoraclesoftware 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.zipArchive: 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.txtfile to the target server$TMPDIRscp -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:
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_sizeon 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_datafilesparameter:Execute the following on the source database. This will generate a data pump command in the
xttplugin.txtfile. We are interested in thetransport_datafiles line:cd $HOME/xtt
export TMPDIR=$HOME/xtt
$ORACLE_HOME/perl/bin/perl xttdriver.pl -ecat 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.
Option 1: DB Link
-
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=yandTRANSPORTABLE=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 existWe 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.parFULL=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.partransport_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.sqlSQL> 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 AVAILABLEThe 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 AVAILABLEIf 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
-
Known Issues for Cross Platform Transportable Tablespaces XTTS (Doc ID 2311677.1)
-
ERROR IN CONVERSION ORA-19624: operation failed, retry possible CONVERTED BACKUP PIECE/nfsbackup/XTT/xib_622or76s_1_1_7
Solution: Make sure the directory is writeable for the oracle user on the target /nfsbackup/XTT directory
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.