[ORACLE] Move PDB (arch multitenant)

Move on PDB from an instance A to instance B on the same linux machine.

#!/bin/bash
# ---------------------------------------------------------------------------
# move_pdb.sh  —  Déplacement complet d’une PDB d’un CDB source vers un CDB cible
# ---------------------------------------------------------------------------

set -euo pipefail
IFS=$'nt'

if [[ $# -ne 3 ]]; then
    echo "Usage: $0 <PDB_NAME> <SOURCE_CDB> <TARGET_CDB>"
    exit 1
fi

PDB_NAME="${1^^}"   # Uppercase obligatoire
SRC_CDB="$2"
TGT_CDB="$3"

XML_FILE="/tmp/${PDB_NAME}.xml"
LOG_DIR="/appli/oracle/log"
LOG_FILE="${LOG_DIR}/move_${PDB_NAME}_$(date +%Y%m%d%H%M).log"

mkdir -p "$LOG_DIR"
exec > >(tee -a "$LOG_FILE") 2>&1

echo "====================================================================="
echo "      MOVE PDB  : $PDB_NAME"
echo "      FROM CDB  : $SRC_CDB"
echo "      TO   CDB  : $TGT_CDB"
echo "      DATE      : $(date '+%F %T')"
echo "====================================================================="

# ---------------------------------------------------------------------------
# Helper SQL
# ---------------------------------------------------------------------------
run_sql() {
    local sid="$1"; shift
    local sql="$*"
    export ORAENV_ASK=NO
    export ORACLE_SID="$sid"
    . oraenv >/dev/null 2>&1
    echo "$sql" | sqlplus -s / as sysdba
}

# ---------------------------------------------------------------------------
# Fonction robuste de test PDB (supprime espaces + retours ligne)
# ---------------------------------------------------------------------------
pdb_exists() {
   local pdb="$1"
   local cdb="$2"

   local result
   result=$(run_sql "$cdb" "
      SET HEADING OFF FEEDBACK OFF VERIFY OFF ECHO OFF PAGES 0
      SELECT COUNT(*) FROM cdb_pdbs WHERE UPPER(pdb_name)=UPPER('${pdb}');
   " | tr -d '[:space:]')

   echo "$result"
}

# ---------------------------------------------------------------------------
# 1. Contrôle existence PDB dans le CDB source
# ---------------------------------------------------------------------------
echo "[1] Vérification de la PDB dans le CDB source..."

COUNT_SRC=$(pdb_exists "$PDB_NAME" "$SRC_CDB")

if [[ "$COUNT_SRC" != "1" ]]; then
    echo "ERREUR : La PDB $PDB_NAME n'existe pas dans $SRC_CDB"
    exit 1
fi

echo "OK – PDB trouvée dans $SRC_CDB"
echo ""

# ---------------------------------------------------------------------------
# 2. Contrôle : PDB non existante dans CDB cible
# ---------------------------------------------------------------------------
echo "[2] Vérification de non-existence dans CDB cible..."

COUNT_TGT=$(pdb_exists "$PDB_NAME" "$TGT_CDB")

if [[ "$COUNT_TGT" != "0" ]]; then
    echo "ERREUR : La PDB existe déjà dans $TGT_CDB, abandon."
    exit 1
fi

echo "OK – Aucune PDB collision dans $TGT_CDB"
echo ""
# ---------------------------------------------------------------------------
# 2b. Vérification du nombre de PDB existantes dans le CDB cible
# ---------------------------------------------------------------------------
echo "[2b] Vérification du nombre de PDB dans le CDB cible..."

PDB_COUNT_TGT=$(run_sql "$TGT_CDB" "
   SET HEADING OFF FEEDBACK OFF VERIFY OFF ECHO OFF PAGES 0
   SELECT COUNT(*)
   FROM cdb_pdbs
   WHERE pdb_name NOT IN ('CDB$ROOT', 'PDB$SEED');
" | tr -d '[:space:]')

if (( PDB_COUNT_TGT >= 3 )); then
    echo "ERREUR : Le CDB cible $TGT_CDB contient déjà $PDB_COUNT_TGT PDB (limite = 3)."
    echo "Migration impossible."
    exit 1
fi

echo "OK – Seulement $PDB_COUNT_TGT PDB dans $TGT_CDB (limite = 3)."
echo ""
# ---------------------------------------------------------------------------
# 3. UNPLUG
# ---------------------------------------------------------------------------
echo "[3] UNPLUG de la PDB depuis le CDB source..."

run_sql "$SRC_CDB" "
    ALTER PLUGGABLE DATABASE ${PDB_NAME} CLOSE IMMEDIATE;
    ALTER PLUGGABLE DATABASE ${PDB_NAME} UNPLUG INTO '${XML_FILE}';
"

echo "XML généré : $XML_FILE"
echo ""

# ---------------------------------------------------------------------------
# 4. DROP KEEP DATAFILES
# ---------------------------------------------------------------------------
echo "[4] DROP PDB KEEP DATAFILES..."

run_sql "$SRC_CDB" "
    DROP PLUGGABLE DATABASE ${PDB_NAME} KEEP DATAFILES;
"

echo "PDB supprimée du CDB source (datafiles conservés)"
echo ""

# ---------------------------------------------------------------------------
# 5. Vérification compatibilité du plug
# ---------------------------------------------------------------------------
echo "[5] Vérification de la compatibilité du plug..."

COMPAT=$(run_sql "$TGT_CDB" "
SET SERVEROUTPUT ON
DECLARE v BOOLEAN;
BEGIN
  v := DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
         pdb_descr_file => '${XML_FILE}',
         pdb_name       => '${PDB_NAME}');
  IF v THEN DBMS_OUTPUT.PUT_LINE('YES');
  ELSE      DBMS_OUTPUT.PUT_LINE('NO');
  END IF;
END;
/
")

if ! echo "$COMPAT" | grep -q "YES"; then
    echo "ERREUR : PDB incompatible avec le CDB $TGT_CDB"
    exit 2
fi

echo "Compatibilité OK."
echo ""

# ---------------------------------------------------------------------------
# 6. CREATE PLUGGABLE DATABASE
# ---------------------------------------------------------------------------
echo "[6] Création de la PDB dans $TGT_CDB..."

run_sql "$TGT_CDB" "
    CREATE PLUGGABLE DATABASE ${PDB_NAME}
        USING '${XML_FILE}'
        NOCOPY;
"

echo "PDB créée dans $TGT_CDB."
echo ""

# ---------------------------------------------------------------------------
# 7. OPEN READ WRITE
# ---------------------------------------------------------------------------
echo "[7] Ouverture READ WRITE..."

run_sql "$TGT_CDB" "
    ALTER PLUGGABLE DATABASE ${PDB_NAME} OPEN;
    ALTER PLUGGABLE DATABASE ${PDB_NAME} SAVE STATE;
"

echo "PDB ouverte."
echo ""

# ---------------------------------------------------------------------------
# 8. TEMPFILE
# ---------------------------------------------------------------------------
echo "[8] Vérification TEMPFILE..."

TEMP_COUNT=$(run_sql "$TGT_CDB" "
  ALTER SESSION SET CONTAINER=${PDB_NAME};
  SET PAGES 0 FEEDBACK OFF
  SELECT COUNT(*) FROM dba_temp_files;
" | tr -d '[:space:]')

if [[ "$TEMP_COUNT" == "0" ]]; then
    echo "Aucun TEMPFILE → création..."
    run_sql "$TGT_CDB" "
      ALTER SESSION SET CONTAINER=${PDB_NAME};
      CREATE TEMPORARY TABLESPACE temp TEMPFILE SIZE 1G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;
      ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
    "
else
    echo "TEMPFILE OK"
fi

# ---------------------------------------------------------------------------
# FIN
# ---------------------------------------------------------------------------
echo ""
echo "====================================================================="
echo "          MOVE PDB → TERMINÉ AVEC SUCCÈS"
echo "====================================================================="
echo "PDB         : $PDB_NAME"
echo "Nouveau CDB : $TGT_CDB"
echo "Log         : $LOG_FILE"
echo ""

exit 0