Thứ Hai, 30 tháng 11, 2020

Thủ tục chuyển dữ liệu sang phân vùng SAN khác_file system

-- 1.Kiểm tra dung lượng tablespace
-- 280376.95 MB
select round(sum(bytes)/1024/1024, 2) 
from dba_data_files 
where (tablespace_name like 'INDX%' or tablespace_name like 'DATA%')
and tablespace_name  in ('DATA2016','INDX2016','DATA2015','INDX2015') 
order by file_name desc;

--361128
select round(sum(bytes)/1024/1024, 2) 
from dba_data_files 
where (tablespace_name like 'INDX%' or tablespace_name like 'DATA%')
and tablespace_name  in ('DATA2017','INDX2017','DATA','INDX') 
order by file_name desc;

-- Kiểm tra đảm bảo file_name năm hết trên phân vùng /data
select *
from dba_data_files 
where (tablespace_name like 'INDX%' or tablespace_name like 'DATA%')
and tablespace_name  in ('DATA2016','INDX2016','DATA2015','INDX2015')  and file_name not like '%data%'
order by file_name desc;

-- 2.Read only tablespace
-- Chuyen tablespace ve che do read only, sau do offline
Select 'alter tablespace ' || tablespace_name || ' read only;' 
from dba_tablespaces 
where (tablespace_name like 'INDX%' or tablespace_name like 'DATA%')
and tablespace_name  in ('DATA2016','INDX2016','DATA2015','INDX2015') ;

-- 3.Copy datafile  sang phân vùng mới: Chia làm 2 node để cùng copy, tong 280GBTB, moi file 140GB
select 'cp ' || file_name || ' /u02/oradata/dbavietdb/' || substr(file_name,23,23)  ||';'
from dba_data_files  
where (tablespace_name like 'INDX%' or tablespace_name like 'DATA%')
and tablespace_name  in ('DATA2016','INDX2016','DATA2015','INDX2015')  
order by file_name desc;

--select * from dba_data_files where file_name in('indx2015_001.dbf','data2015_011.dbf')

--+ Monitoring: Khi nao het IO la OK
dbavietdb01$du -ks /u02/oradata/dbavietdb/
dbavietdb01# iostat -xnd 30
-- Read 50MB/s, Write 50MB/s --> 180GB/h --> 280G mat 1.5h
 r/s    w/s   kr/s   kw/s wait actv wsvc_t asvc_t  %w  %b device
   51.5    4.6 56353.6   21.6  0.0  1.5    0.0   27.5   0  88 c7t60060E8004A53B000000A53B00000000d0 --> /data
    0.2   62.5    0.2 50790.3  0.0  0.3    0.0    4.5   0   4 c7t6005076307FFD2BD0000000000000022d0 --> /u02

-- Khi nao khong con tien trinh cp la OK    
oracle@dbavietdb02 # ps -ef |grep cp
  oracle 18526  4403   0 22:38:38 pts/3       0:05 cp /data/oradata/dbavietdb/data2015_453.dbf /u02/oradata/dbavietdb/data2015_453.dbf
  oracle  4403  3976   0 22:14:29 pts/3       0:00 sh ./3.cp_data_u02_2.sh    

-- 4.Offline tablespace
Select 'alter tablespace ' || tablespace_name || ' offline;' 
from dba_tablespaces 
where (tablespace_name like 'INDX%' or tablespace_name like 'DATA%')
and tablespace_name  in ('DATA2016','INDX2016','DATA2015','INDX2015') ;

-- 5.Lấy danh sách các datafile cần xóa, rename tên, sau 1 ngày xóa (buoc 9)
select 'mv ' || file_name || ' ' || file_name ||'.bkp;' 
from dba_data_files 
where (tablespace_name like 'INDX%' or tablespace_name like 'DATA%')
and tablespace_name  in ('DATA2016','INDX2016','DATA2015','INDX2015') ;

-- 5.Lấy danh sách các datafile cần rename, chạy cuối cùng (buoc 9)
--select 'rm -rf ' || file_name || ' ;' 
--from dba_data_files 
--where (tablespace_name like 'INDX%' or tablespace_name like 'DATA%')
--and tablespace_name  in ('DATA2016','INDX2016','DATA2015','INDX2015') ;

-- 6.Rename data files sang đường dẫn mới 
select 'alter database' || ' rename file ''' || file_name || ''' to ' ||   '''/u02/oradata/dbavietdb/' || substr(file_name,23,23) ||''';' 
from dba_data_files  
where (tablespace_name like 'INDX%' or tablespace_name like 'DATA%')
and tablespace_name  in ('DATA2016','INDX2016','DATA2015','INDX2015') 
and file_name like '/data%'
order by file_name desc;

-- 7.Online, read only tablespace
Select 'alter tablespace ' || tablespace_name || ' online;' 
from dba_tablespaces 
where tablespace_name  in ('DATA2016','INDX2016','DATA2015','INDX2015');

Select 'alter tablespace ' || tablespace_name || ' read only;' 
from dba_tablespaces 
where  tablespace_name  in ('DATA2016','INDX2016','DATA2015','INDX2015');

-- 8.Kiểm tra
--Đảm bỏa File_Name đều nằm trong /u02
-- Đảm bảo check point time gần thời điểm nhau (chênh nhau 1p)
select a.file_name, a.file_id, a.tablespace_name,a.status,a.relative_fno,a.online_status,
b."CREATION_CHANGE#",b.enabled,b."CHECKPOINT_CHANGE#",b."LAST_CHANGE#",b.checkpoint_time
from dba_data_files a,v$datafile b
where a.file_id=b.file#
and (a.tablespace_name  in ('DATA2016','INDX2016','DATA2015','INDX2015'))
--and a.file_name not like '/u02%'
order by tablespace_name,file_name;

-- Đảm bảo không còn row nào
Select  file_name, tablespace_name from dba_data_files where (tablespace_name like 'INDX%' or tablespace_name like 'DATA%')
and tablespace_name  in ('DATA2016','INDX2016','DATA2015','INDX2015')
and file_name not like '%u02%'
order by file_name desc;

-- Check d/s các segment
select * from dba_segments 
where (tablespace_name like 'INDX%' or tablespace_name like 'DATA%')
and tablespace_name  in ('DATA2016','INDX2016','DATA2015','INDX2015')
and segment_type like 'TABLE%'
order by segment_type;

-- select du lieu tại 1 số partition bất kỳ, đảm bảo có dữ liệu
Select * from binhtv.REG_LOG partition(DATA201501) where rownum<10;
Select * from binhtv.REG_LOG partition(DATA201502) where rownum<10;
Select * from binhtv.REG_LOG partition(DATA201503) where rownum<10;
Select * from binhtv.REG_LOG partition(DATA201504) where rownum<10;
Select * from binhtv.REG_LOG partition(DATA201505) where rownum<10;
Select * from binhtv.REG_LOG partition(DATA201506) where rownum<10;
Select * from binhtv.REG_LOG partition(DATA201507) where rownum<10;
Select * from binhtv.REG_LOG partition(DATA201508) where rownum<10;
Select * from binhtv.REG_LOG partition(DATA201509) where rownum<10;
Select * from binhtv.REG_LOG partition(DATA201510) where rownum<10;
Select * from binhtv.REG_LOG partition(DATA201511) where rownum<10;
Select * from binhtv.REG_LOG partition(DATA201512) where rownum<10;

Select * from binhtv.REG_LOG partition(DATA201608) where rownum<10;

-- 9.Đổi tên các datafile theo sript bước 4, sau 1 vài ngày theo dõi sẽ xóa


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

ĐỌC NHIỀU

Trần Văn Bình - Oracle Database Master