ASM_Chuyển dữ liệu từ phân vùng DATA sang RECO, BACKUP

--1.Check dung luong: 4.825.125 GB

select round(sum(bytes)/1024/1024,2) from dba_data_files where  tablespace_name='DATA2015' and file_name not like '+BACKUP%' and file_name not like '+BACKUP%';

-- Dung luong   DATA con 313912 MB

select * from v$asm_diskgroup;

--Chuyen 640GB

select sum(bytes)/1024/1024/1024 "GB" from dba_data_files where  tablespace_name='DATA2015' and file_name not like '+RECO%'  and  file_name not like '+BACKUP%'

and file_id <= 970

order by file_id;

--2.Lay file_id cho cau lenh backup as copy datafile --> Chuyen Excel --> Xoay ngan va chuyen xuong muc 3.1

select file_id ||',' from dba_data_files where  tablespace_name='DATA2015' and file_name not like '+RECO%'  and file_name not like '+BACKUP%'

and file_id <= 970

order by file_id;

--951,952,953,954,955,956,957,958,959,960,961,962,963,964,965,966,967,968,969,970

--***CHAY CUOI CUNG: Lay script cau lenh xoa datafile copy

select 'DELETE DATAFILECOPY ''' || file_name || ''';'  from dba_data_files where  tablespace_name='DATA2015' and file_name not like '+RECO%'  and file_name not like '+BACKUP%'

and file_id <= 970

order by file_id;

--3.Thuc hien

--3.1.Backup as copy datafile: tu 14h00 - 20h55, mat 11h14', dung VNC

RMAN>run{

backup as copy datafile 951,952,953,954,955,956,957,958,959,960,961,962,963,964,965,966,967,968,969,970 format '+BACKUP';

SQL 'alter tablespace data2015 read only';

SQL 'alter tablespace data2015 offline';

SWITCH datafile 951,952,953,954,955,956,957,958,959,960,961,962,963,964,965,966,967,968,969,970 TO COPY;

recover TABLESPACE data2015;

SQL 'alter tablespace data2015 online';

}

--4.Check lai, dam bao da chuyen sang BACKUP

select * from dba_data_files where file_id in (951,952,953,954,955,956,957,958,959,960,961,962,963,964,965,966,967,968,969,970);

--+BACKUP/dbavietdb/datafile/data2015.1384.913414915    921    DATA2015

--+BACKUP/dbavietdb/datafile/data2015.1162.913414915    922    DATA2015

--+BACKUP/dbavietdb/datafile/data2015.1685.913414915    923    DATA2015

--+BACKUP/dbavietdb/datafile/data2015.1568.913414915    924    DATA2015

--+BACKUP/dbavietdb/datafile/data2015.1463.913414915    925    DATA2015

--+BACKUP/dbavietdb/datafile/data2015.568.913414915    926    DATA2015

--+BACKUP/dbavietdb/datafile/data2015.482.913414915    927    DATA2015

--+BACKUP/dbavietdb/datafile/data2015.771.913414915    928    DATA2015

--+BACKUP/dbavietdb/datafile/data2015.921.913417033    944    DATA2015

--+BACKUP/dbavietdb/datafile/data2015.1213.913417033    945    DATA2015

--+BACKUP/dbavietdb/datafile/data2015.1449.913417033    946    DATA2015

--+BACKUP/dbavietdb/datafile/data2015.320.913417033    947    DATA2015

--+BACKUP/dbavietdb/datafile/data2015.650.913417035    948    DATA2015

--+BACKUP/dbavietdb/datafile/data2015.665.913417035    949    DATA2015

--+BACKUP/dbavietdb/datafile/data2015.1623.913417035    970    DATA2015

--5.Xoa datafile

--RMAN> run{

DELETE DATAFILECOPY '+DATA/dbavietdb/datafile/data2015.1099.884530523';

DELETE DATAFILECOPY '+DATA/dbavietdb/datafile/data2015.700.884530525';

DELETE DATAFILECOPY '+DATA/dbavietdb/datafile/data2015.731.884530527';

DELETE DATAFILECOPY '+DATA/dbavietdb/datafile/data2015.838.884530531';

DELETE DATAFILECOPY '+DATA/dbavietdb/datafile/data2015.769.884530535';

DELETE DATAFILECOPY '+DATA/dbavietdb/datafile/data2015.770.884530537';

DELETE DATAFILECOPY '+DATA/dbavietdb/datafile/data2015.768.884530541';

DELETE DATAFILECOPY '+DATA/dbavietdb/datafile/data2015.767.884530545';

DELETE DATAFILECOPY '+DATA/dbavietdb/datafile/data2015.766.884530575';

DELETE DATAFILECOPY '+DATA/dbavietdb/datafile/data2015.764.884530581';

DELETE DATAFILECOPY '+DATA/dbavietdb/datafile/data2015.763.884530585';

DELETE DATAFILECOPY '+DATA/dbavietdb/datafile/data2015.772.884530589';

DELETE DATAFILECOPY '+DATA/dbavietdb/datafile/data2015.621.884530593';

DELETE DATAFILECOPY '+DATA/dbavietdb/datafile/data2015.622.884530597';

DELETE DATAFILECOPY '+DATA/dbavietdb/datafile/data2015.624.884530601';

DELETE DATAFILECOPY '+DATA/dbavietdb/datafile/data2015.623.884530603';

DELETE DATAFILECOPY '+DATA/dbavietdb/datafile/data2015.483.884530607';

DELETE DATAFILECOPY '+DATA/dbavietdb/datafile/data2015.578.884530611';

DELETE DATAFILECOPY '+DATA/dbavietdb/datafile/data2015.354.888834507';

DELETE DATAFILECOPY '+DATA/dbavietdb/datafile/data2015.1096.888834511';

}

KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH: 📧 Mail: binhoracle@gmail.com ☎️ Mobile: 0902912888 ⚡️ Skype: tranbinh48ca 👨 Facebook: https://www.facebook.com/BinhOracleMaster 👨 Inbox Messenger: https://m.me/101036604657441 (profile) 👨 Fanpage: https://www.facebook.com/tranvanbinh.vn 👨 Inbox Fanpage: https://m.me/tranvanbinh.vn 👨👩 Group FB: https://www.facebook.com/groups/OracleDBAVietNam 👨 Website: http://www.tranvanbinh.vn 👨 Blogger: https://tranvanbinhmaster.blogspot.com 🎬 Youtube: http://bit.ly/ytb_binhoraclemaster 👨 Tiktok: https://www.tiktok.com/@binhoraclemaster?lang=vi 👨 Linkin: https://www.linkedin.com/in/binhoracle 👨 Twitter: https://twitter.com/binhoracle 👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội #OracleTutorial #OracleDBA #OracleDatabaseAdministration #học oracle database #oca #ocp #oce #ocm