MOVE ORACLE DATAFILES IN ASM
1°) Ouvrir la DB en mode "OPEN RESTRICTED"
NB: Les fichiers de données à deplacer appartiennent à une BD 11g
Noeud01:
* Pour les BD 12C
$ . oraenv
ORACLE_SID = [BIEDW1] ? biedw1
SQL> ALTER PLUGGABLE DATABASE EDW2P,EDW2T,BIDBBIS CLOSE IMMEDIATE;
SQL> ALTER PLUGGABLE DATABASE EDW2P,EDW2T,BIDBBIS OPEN READ ONLY RESTRICTED;
* Pour les BD 11g
Version 01:
srvctl stop database -d BIDB -o immediate;
srvctl start database -d BIDB -o restrict;
Version 02:
[oracle@svr-test-01 ~]$ . oraenv
ORACLE_SID = [sid] ? BIDB1
$SQLPLUS / AS SYSDBA
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP RESTRICT OPEN;
Noeud02:
* Pour les BD 12C
[oracle@svr-test-01 ~]$ . oraenv
ORACLE_SID = [BIEDW1] ? biedw2
SQL> ALTER PLUGGABLE DATABASE EDW2P,EDW2T,BIDBBIS CLOSE IMMEDIATE;
SQL> ALTER PLUGGABLE DATABASE EDW2P,EDW2T,BIDBBIS OPEN READ ONLY RESTRICTED;
* Pour les BD 11g
[oracle@svr-test-01 ~]$ . oraenv
ORACLE_SID = [sid] ? BIDB2
$SQLPLUS / AS SYSDBA
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP RESTRICT OPEN;
2°) Mettre en commentaire le programme (en crontab) de suppression des fichiers d'archives de la base
$ crontab -l
#-------------------------------------------Node1----------------------------------------------
0 * * * * /home/oracle/scripts/rman_archivelog.sh
00 22 * * 5 /home/oracle/scripts/expdp_wbs.sh
#---- Cleanup archive logs more than 3 days old -------------------------
00 00,05,07,10,12,14,16,18,20 * * * sh /home/oracle/scripts/clean_wbs_xml.sh
00 00 * * * sh /home/oracle/scripts/purge.sh > /home/oracle/scripts/purge.log
$ crontab -e
# 0 * * * * /home/oracle/scripts/rman_archivelog.sh
3°) Rediriger l'emplacement des fichiers d'archives vers un emplacement disposant de plus d'espace:
SQL> SHOW PARAMETER DB_RECOVERY_FILE_DEST;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +RECOC1
db_recovery_file_dest_size big integer 4096G
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST='+RECOC2' SCOPE=BOTH;
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 6000G SCOPE=BOTH;
4°) Mettre les fichiers a deplacer OFFLINE
$. oraenv
ORACLE_SID = [sid] ? BIDB1
sqlplus /nolog
conn / as sysdba
SQL> ALTER DATABASE DATAFILE '+DATAC1/bidb/datafile/tbs_mtn_bi.1861.880221655' OFFLINE;
SQL> ALTER DATABASE DATAFILE '+DATAC1/bidb/datafile/tbs_fact_ahc_01.2179.879666471'
OFFLINE;
SQL> ALTER DATABASE DATAFILE '+DATAC1/bidb/datafile/tbs_dwh_ods_ahc4.2230.874861485'
OFFLINE;
SQL> ALTER DATABASE DATAFILE '+DATAC1/bidb/datafile/tbs_fact_ahc_02.1167.880135599'
OFFLINE;
SQL> ALTER DATABASE DATAFILE '+DATAC1/bidb/datafile/tbs_dwh_ods.921.920632493' OFFLINE;
SQL> ALTER DATABASE DATAFILE '+DATAC1/bidb/datafile/tbs_dwh_edm.1190.901040507'
OFFLINE;
SQL> ALTER DATABASE DATAFILE '+DATAC1/bidb/datafile/tbs_dwh_ods_012017.754.924982571'
OFFLINE;
5°) Script RMAN de copies des fichiers:
===============================
* Rman_Move_TBS_MTN_BI.rman
run
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK ;
ALLOCATE CHANNEL disk2 DEVICE TYPE DISK ;
ALLOCATE CHANNEL disk3 DEVICE TYPE DISK ;
ALLOCATE CHANNEL disk4 DEVICE TYPE DISK ;
ALLOCATE CHANNEL disk5 DEVICE TYPE DISK ;
ALLOCATE CHANNEL disk6 DEVICE TYPE DISK ;
ALLOCATE CHANNEL disk7 DEVICE TYPE DISK ;
ALLOCATE CHANNEL disk8 DEVICE TYPE DISK ;
ALLOCATE CHANNEL disk9 DEVICE TYPE DISK ;
ALLOCATE CHANNEL disk10 DEVICE TYPE DISK ;
BACKUP AS COPY SECTION SIZE 220G DATAFILE 12 FORMAT "+RECOC2";
==============================
* Rman_Move_TBS_FACT_AHC_01.rman
run
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK ;
ALLOCATE CHANNEL disk2 DEVICE TYPE DISK ;
ALLOCATE CHANNEL disk3 DEVICE TYPE DISK ;
ALLOCATE CHANNEL disk4 DEVICE TYPE DISK ;
ALLOCATE CHANNEL disk5 DEVICE TYPE DISK ;
ALLOCATE CHANNEL disk6 DEVICE TYPE DISK ;
BACKUP AS COPY SECTION SIZE 350G DATAFILE 9 FORMAT "+RECOC2";
================================
* Rman_Move_TBS_DWH_ODS_AHC4.rman
run
{
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK ;
ALLOCATE CHANNEL disk2 DEVICE TYPE DISK ;
ALLOCATE CHANNEL disk3 DEVICE TYPE DISK ;
ALLOCATE CHANNEL disk4 DEVICE TYPE DISK ;
ALLOCATE CHANNEL disk5 DEVICE TYPE DISK ;
BACKUP AS COPY SECTION SIZE 350G DATAFILE 326 FORMAT "+RECOC2";
================================
* Rman_Move_TBS_FACT_AHC_02.rman
run
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK ;
ALLOCATE CHANNEL disk2 DEVICE TYPE DISK ;
ALLOCATE CHANNEL disk3 DEVICE TYPE DISK ;
ALLOCATE CHANNEL disk4 DEVICE TYPE DISK ;
ALLOCATE CHANNEL disk5 DEVICE TYPE DISK ;
BACKUP AS COPY SECTION SIZE 300G DATAFILE 15 FORMAT "+RECOC2";
===============================
* Rman_Move_TBS_DWH_ODS_092016.rman
run
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK ;
ALLOCATE CHANNEL disk2 DEVICE TYPE DISK ;
ALLOCATE CHANNEL disk3 DEVICE TYPE DISK ;
ALLOCATE CHANNEL disk4 DEVICE TYPE DISK ;
ALLOCATE CHANNEL disk5 DEVICE TYPE DISK ;
BACKUP AS COPY SECTION SIZE 300G DATAFILE 51 FORMAT "+RECOC2";
================================
* Rman_Move_TBS_DWH_EDM.rman
run
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK ;
ALLOCATE CHANNEL disk2 DEVICE TYPE DISK ;
ALLOCATE CHANNEL disk3 DEVICE TYPE DISK ;
BACKUP AS COPY SECTION SIZE 225G DATAFILE 28 FORMAT "+RECOC2";
==================================
* Rman_Move_TBS_DWH_ODS_012017.rman
run
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK ;
ALLOCATE CHANNEL disk2 DEVICE TYPE DISK ;
ALLOCATE CHANNEL disk3 DEVICE TYPE DISK ;
BACKUP AS COPY SECTION SIZE 225G DATAFILE 58 FORMAT "+RECOC2";
}
6°) Executer les scripts RMAN pour la copie des datafiles:
Sur le noeud 1 svr-test-01: nohup rman target /
cmdfile=/home/oracle/all/bidb/move/RMAN/Rman_Move_TBS_MTN_BI.rman
log=/home/oracle/all/bidb/move/RMAN/Rman_Move_TBS_MTN_BI.log &
Sur le noeud 1 svr-test-01: nohup rman target /
cmdfile=/home/oracle/all/bidb/move/RMAN/Rman_Move_TBS_FACT_AHC_01.rman
log=/home/oracle/all/bidb/move/Rman_Move_TBS_FACT_AHC_01.log &
Sur le noeud 1 svr-test-01: nohup rman target /
cmdfile=/home/oracle/all/bidb/move/RMAN/Rman_Move_TBS_DWH_ODS_AHC4.rman
log=/home/oracle/all/bidb/move/RMAN/Rman_Move_TBS_DWH_ODS_AHC4.log &
Sur le noeud 1 svr-test-01: nohup rman target /
cmdfile=/home/oracle/all/bidb/move/RMAN/Rman_Move_TBS_FACT_AHC_02.rman
log=/home/oracle/all/bidb/move/RMAN/Rman_Move_TBS_FACT_AHC_02.log &
Sur le noeud 2 svr-test-02: nohup rman target /
cmdfile=/home/oracle/all/bidb/move/RMAN/Rman_Move_TBS_DWH_ODS_092016.rman
log=/home/oracle/all/bidb/move/RMAN/Rman_Move_TBS_DWH_ODS_092016.log &
Sur le noeud 2 svr-test-02: nohup rman target /
cmdfile=/home/oracle/all/bidb/move/RMAN/Rman_Move_TBS_DWH_EDM.rman
log=/home/oracle/all/bidb/move/RMAN/Rman_Move_TBS_DWH_EDM.log &
Sur le noeud 2 svr-test-02: nohup rman target /
cmdfile=/home/oracle/all/bidb/move/RMAN/Rman_Move_TBS_DWH_ODS_012017.rman
log=/home/oracle/all/bidb/move/RMAN/Rman_Move_TBS_DWH_ODS_012017.log &
7°) Changer dans le controlfile de la base les noms des anciens fichiers avec les noms des nouveaux;
rman target /
run
SWITCH DATAFILE "+DATAC1/bidb/datafile/tbs_mtn_bi.1861.880221655" TO COPY;
SWITCH DATAFILE "+DATAC1/bidb/datafile/tbs_fact_ahc_01.2179.879666471" TO COPY;
SWITCH DATAFILE "+DATAC1/bidb/datafile/tbs_dwh_ods_ahc4.2230.874861485" TO COPY;
SWITCH DATAFILE "+DATAC1/bidb/datafile/tbs_fact_ahc_02.1167.880135599" TO COPY;
SWITCH DATAFILE "+DATAC1/bidb/datafile/tbs_dwh_ods.921.920632493" TO COPY;
SWITCH DATAFILE "+DATAC1/bidb/datafile/tbs_dwh_edm.1190.901040507" TO COPY;
SWITCH DATAFILE "+DATAC1/bidb/datafile/tbs_dwh_ods_012017.754.924982571" TO COPY;
}
8°) Faire un "RECOVER" de tous les nouveaux fichiers cré
* Rman_Recover_Datafiles.rman
run
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK ;
ALLOCATE CHANNEL disk2 DEVICE TYPE DISK ;
ALLOCATE CHANNEL disk3 DEVICE TYPE DISK ;
ALLOCATE CHANNEL disk4 DEVICE TYPE DISK ;
ALLOCATE CHANNEL disk5 DEVICE TYPE DISK ;
ALLOCATE CHANNEL disk6 DEVICE TYPE DISK ;
RECOVER DATAFILE 12, 9, 326, 15, 51,28,58;
* Sur le noeud 1 svr-test-01:
nohup rman target / cmdfile=/home/oracle/all/bidb/move/RMAN/Rman_Recover_Datafiles.rman
log=/home/oracle/all/bidb/move/RMAN/Rman_Recover_Datafiles.log &
9°) Identifier la liste des nouveaux fichiers qui en encore "OFFLINE" est les mettre "ONLINE"
Exéter la requete ci-dessous sur le noeud 1 svr-test-01 SQL*PLUS
SQL> set lines 250 pages 50000
SQL> select 'alter database datafile '''||NAME||''' ONLINE;' from v$datafile where status = 'OFFLINE';
10°) Exécuter le résultat (les lignes) de la requête Etape 9°)
11°) Lorsque toutes les épes de 1°) à°) auront é correctement exété, les fichiers d'origine pouront êe
suppriméSur le noeud 1 svr-test-01, faire :
rman target /
list datafilecopy all;
RMAN> DELETE DATAFILECOPY "+DATAC1/bidb/datafile/tbs_mtn_bi.1861.880221655";
RMAN> DELETE DATAFILECOPY "+DATAC1/bidb/datafile/tbs_fact_ahc_01.2179.879666471";
RMAN> DELETE DATAFILECOPY "+DATAC1/bidb/datafile/tbs_dwh_ods_ahc4.2230.874861485";
RMAN> DELETE DATAFILECOPY "+DATAC1/bidb/datafile/tbs_fact_ahc_02.1167.880135599";
RMAN> DELETE DATAFILECOPY "+DATAC1/bidb/datafile/tbs_dwh_ods.921.920632493";
RMAN> DELETE DATAFILECOPY "+DATAC1/bidb/datafile/tbs_dwh_edm.1190.901040507";
RMAN> DELETE DATAFILECOPY "+DATAC1/bidb/datafile/tbs_dwh_ods_012017.754.924982571";
12°) Reouvrir la BD
