## 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';
```