-- 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')
--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
