Scripte de restore de base de données Oracle via Duplicate noopen
cat .dba
sys:<mdp sys>
#!/bin/sh
# =====================================================================
# Script : ora_duplicate.sh
# Description : Restore d'une instance Oracle avec option renommage de PDB
# Date : 2026-01-27
# =====================================================================
while getopts "I:B:P:D:S:" opt; do
case "$opt" in
I) TARGET_SID="$OPTARG" ;;
B) BACKUP_PATH="$OPTARG" ;;
P) POST_DIR="$OPTARG" ;;
D) PDB_TARGET="$OPTARG" ;;
S) PDB_SOURCE="$OPTARG" ;;
esac
done
if [ -z "${TARGET_SID}" ]
then
echo "Usage: $0 -I <ORACLE_SID> [-B <backup_path>] [-P <post_script_folder>] [-D <target_pdb_name>] [-S <source_pdb_name>]"
exit 1
fi
if [ -n "${PDB_TARGET}" ] && [ -z "${PDB_SOURCE}" ]
then
echo "Usage: $0 -I <ORACLE_SID> [-B <backup_path>] [-P <post_script_folder>] [-D <target_pdb_name>] [-S <source_pdb_name>]"
echo "If you specify the target_pdb_name, the source_pdb_name is mandatory"
exit 1
fi
MAIL_TO="listeDBA@jjlogdb.fr"
MAIL_FROM="dba-noreply@jjlogdb.fr"
PASS_FILE="${HOME}/.dba"
LOGIN="sys"
LOG_DIR="/appli/home/oracle/dbascripts/logs"
TS=$(date +"%Y-%m-%d_%H%M")
LOG_FILE="${LOG_DIR}/rman_duplicate_${TARGET_SID}_${TS}.log"
SAVE_PWD="${LOG_DIR}/g_savepwd_${TARGET_SID}.save.sql"
TMP_PWD="${LOG_DIR}/g_pwdtmp_${TARGET_SID}_${TS}.sql"
DBA_SRIPTS="/appli/home/oracle/dbascripts"
DUMP_DIR="/appli/oracle/backup"
EXTRACT_SQL="${DBA_SRIPTS}/sql/extract_passwords_one_pdb.sql"
export ORACLE_SID="${TARGET_SID}"
export ORAENV_ASK=NO
. oraenv >/dev/null 2>&1
PW=$(grep "^${LOGIN}:" "${PASS_FILE}" | head -n1 | cut -d: -f2)
if [ -z "${BACKUP_PATH}" ]
then
ROOT="${DUMP_DIR}/${TARGET_SID}"
BACKUP_PATH=$(ls -1dt ${ROOT}/* 2>/dev/null | head -n1)
fi
if [ -z "${POST_DIR}" ]
then
POST_DIR="${DBA_SRIPTS}/PostScript"
fi
HOST_SHORT=$(hostname | cut -d. -f1)
echo "Log file is located on ${HOST_SHORT} : ${LOG_FILE}"
echo "===== START ${TS} =====" | tee -a "${LOG_FILE}"
sqlplus -s / as sysdba <<EOF > /tmp/checkdb_${TARGET_SID}.log
set pages 0 feedback off
select open_mode from v$database;
EOF
if ! grep -q "READ" /tmp/checkdb_${TARGET_SID}.log
then
echo "L'instance ${TARGET_SID} n'est pas en READ, on utilise le ${SAVE_PWD}" >> "${LOG_FILE}"
if [ -f "${SAVE_PWD}" ]; then
FINAL_PWD_FILE="${SAVE_PWD}"
echo "Fichier ${SAVE_PWD} bien present" >> "${LOG_FILE}"
else
echo "ERREUR : Aucun fichier passwords : ${SAVE_PWD}" | mailx -r "${MAIL_FROM}" -s "[DBA][KO] Duplicate ${TARGET_SID}" "${MAIL_TO}"
exit 1
fi
else
SINGLE_PDB=$(sqlplus -s / as sysdba <<EOF
set pages 0 feedback off
select name from v$pdbs where name <> 'PDB$SEED' and name <> 'CDB$ROOT';
EOF
)
SINGLE_PDB=$(echo "$SINGLE_PDB" | sed 's/ //g' | sed '/^$/d' | head -n1)
if [ -n "${PDB_TARGET}" ]
then
TARGET_PDB="${PDB_TARGET}"
else
TARGET_PDB="${SINGLE_PDB}"
fi
: > "${TMP_PWD}"
## if [ -n "${PDB_SOURCE}" ]; then EXTRACT_PDB="${PDB_SOURCE}"; else EXTRACT_PDB="${TARGET_PDB}"; fi
sqlplus -s / as sysdba <<EOF >> "${TMP_PWD}"
alter session set container=${TARGET_PDB};
@"${EXTRACT_SQL}"
exit
EOF
FINAL_PWD_FILE="${TMP_PWD}"
cp "${TMP_PWD}" "${SAVE_PWD}"
echo "Fichier de password ${FINAL_PWD_FILE} generé" >> "${LOG_FILE}"
fi
echo "Shutdown abort de l'instance $ORACLE_SID" >> "${LOG_FILE}"
sqlplus -s / as sysdba >> "${LOG_FILE}" 2>&1 <<EOF
SHUTDOWN ABORT;
EOF
export ORACLE_SID=+ASM
. oraenv >/dev/null 2>&1
echo "ORACLE_SID=$ORACLE_SID . remove ASM files : asmcmd rm -rf +DATA_DG/${TARGET_SID}_*/" >> "${LOG_FILE}"
asmcmd rm -r +DATA_DG/${TARGET_SID}_*/ >> "${LOG_FILE}" 2>&1
asmcmd rm -r +FRA_DG/${TARGET_SID}_*/ >> "${LOG_FILE}" 2>&1
asmcmd rm -r +REDO_DG/${TARGET_SID}_*/ >> "${LOG_FILE}" 2>&1
asmcmd rm -r +REDOLOG_DG/${TARGET_SID}_*/ >> "${LOG_FILE}" 2>&1
export ORACLE_SID="${TARGET_SID}"
. oraenv >/dev/null 2>&1
PFILE="${ORACLE_HOME}/dbs/init${TARGET_SID}.ora"
if [ ! -f "${PFILE}" ]
then
echo "ERROR: PFILE not found: ${PFILE}" | tee -a "${LOG_FILE}"
echo "PFILE missing for ${TARGET_SID}: ${PFILE}" | mailx -r "${MAIL_FROM}" -s "[DBA][KO] Duplicate ${TARGET_SID} - missing PFILE" "${MAIL_TO}"
exit 1
fi
sqlplus -s / as sysdba >> "${LOG_FILE}" 2>&1 <<EOF
STARTUP NOMOUNT PFILE='${PFILE}';
CREATE SPFILE FROM PFILE='${PFILE}';
SHUTDOWN IMMEDIATE;
STARTUP NOMOUNT;
EOF
RMAN_STEP_LOG="${LOG_DIR}/rman_step_${TARGET_SID}_${TS}.log"
>"${RMAN_STEP_LOG}"
echo "RMAN log dans ${RMAN_STEP_LOG}" >> "${LOG_FILE}"
rman log="${RMAN_STEP_LOG}" >/dev/null 2>&1 <<EOF
CONNECT AUXILIARY ${LOGIN}/${PW}
RUN {
ALLOCATE AUXILIARY CHANNEL AUX1 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL AUX2 DEVICE TYPE DISK;
DUPLICATE DATABASE TO ${TARGET_SID}
NOOPEN
NOFILENAMECHECK
BACKUP LOCATION '${BACKUP_PATH}';
}
EXIT
EOF
RC=$?
cat "${RMAN_STEP_LOG}" >> "${LOG_FILE}"
if [ ${RC} -ne 0 ] || grep -E -q "RMAN-0(3002|[56][0-9]{2})|RMAN-200|ORA-01(1|2|3|4|5)[0-9]{2}" "${RMAN_STEP_LOG}"
then
echo "Duplicate failed on ${TARGET_SID}" | tee -a "${LOG_FILE}"
mailx -r "${MAIL_FROM}" -s "[DBA][KO] Duplicate ${TARGET_SID}" "${MAIL_TO}" < "${LOG_FILE}"
TS=$(date +"%Y-%m-%d_%H%M")
echo "===== END ${TS} =====" >> "${LOG_FILE}"
exit 1
fi
CHECK=$(sqlplus -s / as sysdba <<EOF
SET PAGES 0 FEEDBACK OFF
SELECT CASE
WHEN (SELECT value FROM v$parameter WHERE name='db_name') = '${TARGET_SID}'
AND (SELECT value FROM v$parameter WHERE name='instance_name') = '${TARGET_SID}'
THEN 'OK'
ELSE 'BAD'
END AS status
FROM dual;
EOF
)
if echo "${CHECK}" | grep -q "BAD"
then
echo "ERROR: DB_NAME / INSTANCE_NAME mismatch!" >> "${LOG_FILE}"
echo "Duplicate failed on ${TARGET_SID}" |
mailx -r "${MAIL_FROM}" -s "[DBA][KO] Duplicate ${TARGET_SID}" "${MAIL_TO}"
exit 1
fi
sqlplus -s / as sysdba >> "${LOG_FILE}" 2>&1 <<EOF
ALTER DATABASE OPEN RESETLOGS;
EOF
if [ -n "${PDB_TARGET}" ]
then
echo "Step to rename ${PDB_SOURCE} to ${PDB_TARGET}" >> "${LOG_FILE}"
sqlplus -s / as sysdba >> "${LOG_FILE}" 2>&1 <<EOF
alter pluggable database ${PDB_SOURCE} OPEN RESTRICTED;
alter session set container=${PDB_SOURCE};
alter pluggable database rename global_name to ${PDB_TARGET};
alter pluggable database close immediate;
alter pluggable database open;
shutdown immediate;
startup;
EOF
else
echo "No rename of ${SINGLE_PDB} : alter pluggable database all open" >> "${LOG_FILE}"
sqlplus -s / as sysdba >> "${LOG_FILE}" 2>&1 <<EOF
ALTER PLUGGABLE DATABASE ALL OPEN;
EOF
fi
if [ -n "${PDB_TARGET}" ]
then
FINAL_PDB="${PDB_TARGET}"
else FINAL_PDB="${SINGLE_PDB}"
if [ -z "${FINAL_PDB}" ]
then
FINAL_PDB=$(sqlplus -s / as sysdba >> "${LOG_FILE}" 2>&1 <<EOF
set pages 0 feedback off
select name from v$pdbs where name <> 'PDB$SEED' and name <> 'CDB$ROOT';
EOF
)
FINAL_PDB=$(echo "$FINAL_PDB" | sed 's/ //g' | sed '/^$/d' | head -n1)
echo "FINAL_PDB : ${FINAL_PDB} "
fi
fi
##ajout d'un sleep pour eviter le deadlock lors de alter login
sleep 300
echo "Executing password file: ${FINAL_PWD_FILE} on ${FINAL_PDB} container" >> "${LOG_FILE}"
sqlplus -s / as sysdba >> "${LOG_FILE}" 2>&1 <<EOF
alter session set container=${FINAL_PDB};
alter session set ddl_lock_timeout = 300;
@${FINAL_PWD_FILE}
EOF
if [ -d "${POST_DIR}" ]
then
for SQLFILE in ${POST_DIR}/*.sql
do
echo "Executing Post script file: ${SQLFILE} on ${FINAL_PDB} container" >> "${LOG_FILE}"
sqlplus -s / as sysdba >> "${LOG_FILE}" 2>&1 <<EOF
alter session set container=${FINAL_PDB};
@${SQLFILE}
EOF
done
fi
echo "Duplicate ${TARGET_SID} ${FINAL_PDB} SUCCESS" | tee -a "${LOG_FILE}"
echo "Duplicate ${TARGET_SID} ${FINAL_PDB} SUCCESS" | mailx -r "${MAIL_FROM}" -s "[DBA][OK] Duplicate ${TARGET_SID} SUCCESS" "${MAIL_TO}"
TS=$(date +"%Y-%m-%d_%H%M")
echo "===== END ${TS} =====" | tee -a "${LOG_FILE}"
exit 0