notes/divers/xtts_non-cdb_to_cdb_01.md at main

support/notes

Fork 0

Files

Valeriu PLESNILA 26296b6d6a 2026-03-12 21:01:38

2026-03-12 22:01:38 +01:00

14 KiB

Raw Permalink Blame History

Context

Source: non-CDB = GREEDO@rodia-scan
Target: PDB = REEK, CDB=AERONPRD@ylesia-scan

Setup

Create tablespaces and users:

create tablespace TS1 datafile size 16M autoextend on next 16M;
create tablespace TS2 datafile size 16M autoextend on next 16M;
create tablespace TS3 datafile size 16M autoextend on next 16M;

alter tablespace TS1 add datafile size 16M autoextend on next 16M;
alter tablespace TS1 add datafile size 16M autoextend on next 16M;
alter tablespace TS2 add datafile size 16M autoextend on next 16M;
alter tablespace TS3 add datafile size 16M autoextend on next 16M;
alter tablespace TS3 add datafile size 16M autoextend on next 16M;
alter tablespace TS3 add datafile size 16M autoextend on next 16M;

create user U1 identified by secret;
grant connect, resource, create view,create job to U1;
alter user U1 quota unlimited on TS1;
alter user U1 quota unlimited on TS2;
alter user U1 quota unlimited on TS3;

create user U2 identified by secret;
grant connect, resource, create view,create job to U2;
alter user U2 quota unlimited on TS1;
alter user U2 quota unlimited on TS2;
alter user U2 quota unlimited on TS3;

For each user, create objects:

connect U1/secret
-- create objcts
connect U2/secret
-- create objcts

Create objects script:

-- TABLE 1 dans TS1
CREATE TABLE table1_ts1 (
    id NUMBER PRIMARY KEY,
    data VARCHAR2(100),
    created_at DATE DEFAULT SYSDATE
) TABLESPACE TS1;

CREATE SEQUENCE table1_seq
    START WITH 1
    INCREMENT BY 1
    NOCACHE
    NOCYCLE;

CREATE OR REPLACE TRIGGER trg_table1_id
BEFORE INSERT ON table1_ts1
FOR EACH ROW
BEGIN
    IF :NEW.id IS NULL THEN
        SELECT table1_seq.NEXTVAL INTO :NEW.id FROM dual;
    END IF;
END;
/

-- TABLE 2 dans TS2
CREATE TABLE table2_ts2 (
    id NUMBER PRIMARY KEY,
    data VARCHAR2(100),
    updated_at DATE
) TABLESPACE TS2;

CREATE SEQUENCE table2_seq
    START WITH 1
    INCREMENT BY 1
    NOCACHE
    NOCYCLE;

CREATE OR REPLACE TRIGGER trg_table2_id
BEFORE INSERT ON table2_ts2
FOR EACH ROW
BEGIN
    IF :NEW.id IS NULL THEN
        SELECT table2_seq.NEXTVAL INTO :NEW.id FROM dual;
    END IF;
END;
/

-- TABLE 3 dans TS3
CREATE TABLE table3_ts3 (
    id NUMBER PRIMARY KEY,
    info VARCHAR2(100),
    status VARCHAR2(20)
) TABLESPACE TS3;

CREATE SEQUENCE table3_seq
    START WITH 1
    INCREMENT BY 1
    NOCACHE
    NOCYCLE;

CREATE OR REPLACE TRIGGER trg_table3_id
BEFORE INSERT ON table3_ts3
FOR EACH ROW
BEGIN
    IF :NEW.id IS NULL THEN
        SELECT table3_seq.NEXTVAL INTO :NEW.id FROM dual;
    END IF;
END;
/


CREATE OR REPLACE VIEW combined_view AS
SELECT id, data, created_at, NULL AS updated_at, NULL AS status FROM table1_ts1
UNION ALL
SELECT id, data, updated_at, NULL AS created_at, NULL AS status FROM table2_ts2
UNION ALL
SELECT id, info AS data, NULL, NULL, status FROM table3_ts3;


CREATE OR REPLACE PACKAGE data_ops AS
    PROCEDURE insert_random_data;
    PROCEDURE update_random_data;
    PROCEDURE delete_random_data;
END data_ops;
/

CREATE OR REPLACE PACKAGE BODY data_ops AS
    PROCEDURE insert_random_data IS
    BEGIN
        FOR i IN 1..10 LOOP
            INSERT INTO table1_ts1 (data)
            VALUES (DBMS_RANDOM.STRING('A', 10));
        END LOOP;

        FOR i IN 1..3 LOOP
            INSERT INTO table3_ts3 (info, status)
            VALUES (DBMS_RANDOM.STRING('A', 10), 'NEW');
        END LOOP;
    END;

    PROCEDURE update_random_data IS
    BEGIN
        FOR i IN 1..7 LOOP
            INSERT INTO table2_ts2 (data)
            VALUES (DBMS_RANDOM.STRING('A', 10));
        END LOOP;
        FOR rec IN (
            SELECT id FROM (
                SELECT id FROM table2_ts2 ORDER BY DBMS_RANDOM.VALUE
            ) WHERE ROWNUM <= 5
        ) LOOP
            UPDATE table2_ts2
            SET data = DBMS_RANDOM.STRING('A', 10), updated_at = SYSDATE
            WHERE id = rec.id;
        END LOOP;
    END;

    PROCEDURE delete_random_data IS
    BEGIN
        FOR rec IN (
            SELECT id FROM (
                SELECT id FROM table3_ts3 ORDER BY DBMS_RANDOM.VALUE
            ) WHERE ROWNUM <= 2
        ) LOOP
            DELETE FROM table3_ts3 WHERE id = rec.id;
        END LOOP;
    END;
END data_ops;
/

Create job to run every 1 minute:

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'random_ops_job',
    job_type        => 'PLSQL_BLOCK',
    job_action      => '
        BEGIN
            data_ops.insert_random_data;
            data_ops.update_random_data;
            data_ops.delete_random_data;
        END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=MINUTELY; INTERVAL=1',
    enabled         => TRUE,
    comments        => 'Job to insert, update and delete random data every minute.'
  );
END;
/

To restart the job:

--Restart the job
BEGIN
    DBMS_SCHEDULER.enable('random_ops_job');
END;
/

Count the lines in tables:

select 
	'u1.table1_ts1:'||count(*) from u1.table1_ts1 
union select 
	'u1.table2_ts2:'||count(*) from u1.table2_ts2
union select 
	'u1.table3_ts3:'||count(*) from u1.table3_ts3
union select 
	'u2.table1_ts1:'||count(*) from u2.table1_ts1 
union select 
	'u2.table2_ts2:'||count(*) from u2.table2_ts2
union select 
	'u2.table3_ts3:'||count(*) from u2.table3_ts3
order by 1 asc
/

To ensure the automatic opening of PDB, create a service to start automatically in the PDB:

srvctl add service -s adm_reek -db AERONPRD -preferred AERONPRD1,AERONPRD2,AERONPRD3 -pdb REEK -role PRIMARY
srvctl start service -s adm_reek -db AERONPRD

XTTS

Note MOS: V4 Reduce Transportable Tablespace Downtime using Cross Platform Incremental Backup (Doc ID 2471245.1)

Initial setup

Identify tablespaces to transport, probably all non "administrative" tablespaces:

select 
	listagg(tablespace_name, ',') 
within group 
	(order by tablespace_name) as non_sys_ts
from 
	dba_tablespaces  
where 
	contents not in ('UNDO','TEMPORARY') and 
	tablespace_name not in ('SYSTEM','SYSAUX');

For source and target servers, define folders to be used for scripts, backupset, datapump etc.
In our case, that will be a shared NFS folder /mnt/unprotected/tmp/oracle/xtts

The size of folder should be greather than the size of full database.

Unzip xtts scripts:

cd /mnt/unprotected/tmp/oracle/xtts
unzip /mnt/yavin4/kit/Oracle/XTTS/rman_xttconvert_VER4.3.zip

Configure xtt.properties file:

tablespaces=TS1,TS2,TS3,USERS
src_scratch_location=/mnt/unprotected/tmp/oracle/xtts/scratch
dest_datafile_location=+DATA/AERONPRD/389011A6CB11A654E0635000A8C07D80/xtts/
dest_scratch_location=/mnt/unprotected/tmp/oracle/xtts/scratch
asm_home=/app/oracle/grid/product/19
asm_sid=+ASM1
destconnstr=sys/"Secret00!"@ylesia-scan/adm_reek
usermantransport=1

On target server, create ASM directory where the datafile will be restored:

mkdir +DATA/AERONPRD/389011A6CB11A654E0635000A8C07D80/xtts

On both source and target servers, set TMPDIR environment variable to the path of xtts scripts:

export TMPDIR=/mnt/unprotected/tmp/oracle/xtts

Prepare Phase

This step corresponds to initial full backup/restore of source database on target system.

Initial backup on source server:

export TMPDIR=/mnt/unprotected/tmp/oracle/xtts
cd $TMPDIR
$ORACLE_HOME/perl/bin/perl xttdriver.pl --backup --debug 3

Initial restore on target server:

export TMPDIR=/mnt/unprotected/tmp/oracle/xtts
cd $TMPDIR
$ORACLE_HOME/perl/bin/perl xttdriver.pl --restore --debug 3

debug argument is optional

Roll Forward Phase

As long as necessary we can do incremental backup/resore operations.

New datafiles add to source database are automatically managed by this step.

The commands are exactly the sames (with or without debug mode).

For backup:

export TMPDIR=/mnt/unprotected/tmp/oracle/xtts
cd $TMPDIR
$ORACLE_HOME/perl/bin/perl xttdriver.pl --backup

For restore:

export TMPDIR=/mnt/unprotected/tmp/oracle/xtts
cd $TMPDIR
$ORACLE_HOME/perl/bin/perl xttdriver.pl --restore

Running succesives backup or successive restore operations does not pose a problem.

Final Incremental Backup

On source database, put tablespaces in read-only mode:

select 
	'alter tablespace '||tablespace_name||' read only;' as COMMAND
from 
	dba_tablespaces  
where 
	contents not in ('UNDO','TEMPORARY') and 
	tablespace_name not in ('SYSTEM','SYSAUX');

Check:

select distinct status
from 
	dba_tablespaces  
where 
	contents not in ('UNDO','TEMPORARY') and 
	tablespace_name not in ('SYSTEM','SYSAUX');

Take final incremental backup:

export TMPDIR=/mnt/unprotected/tmp/oracle/xtts
cd $TMPDIR
$ORACLE_HOME/perl/bin/perl xttdriver.pl --backup

Rstore final incremental backup:

export TMPDIR=/mnt/unprotected/tmp/oracle/xtts
cd $TMPDIR
$ORACLE_HOME/perl/bin/perl xttdriver.pl --restore

Metadata export

Create DATAPUMP directory on both source and destination databases.
On source (non-CDB):

SQL> create or replace directory XTTS as '/mnt/unprotected/tmp/oracle/xtts';

On destination (PDB):

export ORACLE_PDB_SID=REEK
SQL> create or replace directory XTTS as '/mnt/unprotected/tmp/oracle/xtts';

Export metadata

expdp userid="'/ as sysdba'" dumpfile=XTTS:metadata.dmp logfile=XTTS:metadata.log FULL=y TRANSPORTABLE=always

Optionally: on target, pout target datafiles read-only at OS level

Identify OMF target datafiles:

asmcmd -p
cd +DATA/AERONPRD/389011A6CB11A654E0635000A8C07D80/xtts
ls --permission

For each datafile, set read-olny permisions, example:

chmod 444 +DATA/AERONPRD/389011A6CB11A654E0635000A8C07D80/xtts/*

If you got:

ORA-15304: operation requires ACCESS_CONTROL.ENABLED attribute to be TRUE (DBD ERROR: OCIStmtExecute)

then set following diskgroup attributes and retry.

column dg_name format a20
column name format a50
column VALUE format a30

set lines 120

select 
	dg.name dg_name, attr.name, attr.value 
from 
	v$asm_attribute attr 
	join v$asm_diskgroup dg on attr.group_number=dg.group_number
where
	attr.name in ('compatible.rdbms','access_control.enabled')
order by dg.name, attr.name
/


alter diskgroup DATA set attribute 'compatible.rdbms' = '19.0.0.0.0';
alter diskgroup RECO set attribute 'compatible.rdbms' = '19.0.0.0.0';

alter diskgroup DATA set attribute 'access_control.enabled' = 'TRUE';
alter diskgroup RECO set attribute 'access_control.enabled' = 'TRUE';

Compare number of datafiles transported and the number of datafiles of non-Oracle tablespaces Check if transported tablespaces already exists on target database

Metadata import and tablespace plug-in

Create impdp parfile impo_metadata.par:

userid="/ as sysdba"
dumpfile=XTTS:metadata.dmp
logfile=XTTS:impo_metadata.log
transport_datafiles=
+DATA/AERONPRD/389011A6CB11A654E0635000A8C07D80/DATAFILE/TS1.290.1205059373,
+DATA/AERONPRD/389011A6CB11A654E0635000A8C07D80/DATAFILE/TS1.291.1205059373,
+DATA/AERONPRD/389011A6CB11A654E0635000A8C07D80/DATAFILE/TS1.298.1205060113,
+DATA/AERONPRD/389011A6CB11A654E0635000A8C07D80/DATAFILE/TS1.289.1205059373,
+DATA/AERONPRD/389011A6CB11A654E0635000A8C07D80/DATAFILE/TS2.293.1205059375,
+DATA/AERONPRD/389011A6CB11A654E0635000A8C07D80/DATAFILE/TS2.300.1205060113,
+DATA/AERONPRD/389011A6CB11A654E0635000A8C07D80/DATAFILE/TS2.292.1205059375,
+DATA/AERONPRD/389011A6CB11A654E0635000A8C07D80/DATAFILE/TS3.294.1205059381,
+DATA/AERONPRD/389011A6CB11A654E0635000A8C07D80/DATAFILE/TS3.295.1205059381,
+DATA/AERONPRD/389011A6CB11A654E0635000A8C07D80/DATAFILE/TS3.296.1205059381,
+DATA/AERONPRD/389011A6CB11A654E0635000A8C07D80/DATAFILE/TS3.297.1205059381,
+DATA/AERONPRD/389011A6CB11A654E0635000A8C07D80/DATAFILE/TS3.299.1205060113,
+DATA/AERONPRD/389011A6CB11A654E0635000A8C07D80/DATAFILE/USERS.302.1205084171

Run import:

impdp parfile=impo_metadata.par

Rebounce the PDB (or the CDB), otherwise we can get errors like:

ORA-01114: IO error writing block to file 33 (block # 1)
ORA-01110: data file 33:
'+DATA/AERONPRD/389011A6CB11A654E0635000A8C07D80/DATAFILE/ts1.298.1205060113'
ORA-27009: cannot write to file opened for read

Put plugged tablespaces in read/write mode:

select 
	'alter tablespace '||tablespace_name||' read write;' as COMMAND
from 
	dba_tablespaces  
where 
	contents not in ('UNDO','TEMPORARY') and 
	tablespace_name not in ('SYSTEM','SYSAUX');

Remove aliases in order to user only OMF datafiles:

cd +DATA/AERONPRD/389011A6CB11A654E0635000A8C07D80/xtts
rmalias ts1_8.dbf ts2_13.dbf... .... ...
cd ..
rm -rf xtts

Unxexpectd issues

In metadata import step I relize I forgot to include USER tablespace in xtt.properties and impdp failed wit error:

ORA-39352: Wrong number of TRANSPORT_DATAFILES specified: expected 13, received 12

The tablespace USER being in read-only mode I copied the datafile manually on target database.

Identify the file number:

SQL> select FILE_ID from dba_data_files where TABLESPACE_NAME='USERS';

   FILE_ID
----------
         7

Backup datafile on source:

run{
  set nocfau;
  backup datafile 7 format '/mnt/unprotected/tmp/oracle/xtts/%d_%U_%s_%t.bck';
}

Restore datafile on target;

run {
  restore from platform 'Linux x86 64-bit' 
  foreign datafile 7 format '+DATA/AERONPRD/389011A6CB11A654E0635000A8C07D80/xtts//USERS.dbf'
  from backupset '/mnt/unprotected/tmp/oracle/xtts/GREEDO_0i3t87ss_18_1_1_18_1205084060.bck';
}

Put datafile in read-ony at ASM level:

chmod 444 +DATA/AERONPRD/389011A6CB11A654E0635000A8C07D80/DATAFILE/USERS.302.1205084171

Run the impdp again.

Troubleshooting

Having datafile to plug-in in read-only mode at ASM level allow to repeat tne impdp operations as many time as necessary.
For example, to completly re-execute the impdp metadata as on initial conditions: - drop new plugged tablespaces - drop non oracle maintened users - run impdp metadata again

drop tablespace TS1 including contents;
drop tablespace TS2 including contents;
drop tablespace TS3 including contents;
drop tablespace USERS including contents;

select 'drop user '||USERNAME||' cascade;' from dba_users where ORACLE_MAINTAINED='N';

14 KiB Raw Permalink Blame History