Thứ Hai, 30 tháng 11, 2020

Quy trình tạo lại UNDO TABLESPACE trong Oracle Database

1. Check


--Hiển thị dung lượng trống của tablespace UNDOTBS1 UNDOTBS2
SELECT  a.tablespace_name,100 - ROUND ( (NVL (b.bytes_free, 0) / a.bytes_alloc) * 100) "%Usage",
    ROUND (a.bytes_alloc / 1024 / 1024) "Size MB",
    ROUND (a.bytes_alloc / 1024 / 1024)- ROUND (NVL (b.bytes_free, 0) / 1024 / 1024) "Used MB",
    ROUND (NVL (b.bytes_free, 0) / 1024 / 1024) "Free MB",
    --ROUND ( (NVL (b.bytes_free, 0) / a.bytes_alloc) * 100) "%Free",
    ROUND (maxbytes / 1048576)  "Max MB",
    round(maxbytes/1048576-(ROUND (a.bytes_alloc / 1024 / 1024)- ROUND (NVL (b.bytes_free, 0) / 1024 / 1024)),0) "Free_MB_Max",
    ROUND (ROUND ( (a.bytes_alloc - NVL (b.bytes_free, 0)) / 1024 / 1024)/  ROUND (maxbytes / 1048576) * 100) "%Used of Max"
    FROM (SELECT f.tablespace_name, SUM (f.bytes) bytes_alloc,  SUM (DECODE (f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes)) maxbytes
            FROM dba_data_files f
            GROUP BY tablespace_name) a,
        (SELECT f.tablespace_name, SUM (f.bytes) bytes_free  FROM dba_free_space f  GROUP BY tablespace_name) b
 WHERE a.tablespace_name = b.tablespace_name(+)  and  (a.tablespace_name in ('UNDOTBS1','UNDOTBS1'))
 order by "%Used of Max" desc;

--Kiểm tra session nào đang sử dụng:
SELECT s.username,
       s.sid,
       s.serial#,
       t.used_ublk,
       t.used_urec,
       rs.segment_name,
       r.rssize,
       r.status
FROM   v$transaction t,
       v$session s,
       v$rollstat r,
       dba_rollback_segs rs
WHERE  s.saddr = t.ses_addr
AND    t.xidusn = r.usn
AND    rs.segment_id = t.xidusn
ORDER BY t.used_ublk DESC;

select * from dba_data_Files where tablespace_name in ('UNDOTBS1','UNDOTBS21');

--/data/oradata/dbaviet/undotbs01.dbf    2    UNDOTBS1    25674383360 --> Node 1
--/data/oradata/dbaviet/undotbs22_1.dbf    62    UNDOTBS21    6442450944 --> Node 2
--/data/oradata/dbaviet/undotbs22_002.dbf    66    UNDOTBS21    2215641088
--/data/oradata/dbaviet/undotbs22_003.dbf    87    UNDOTBS21    2064646144
--/data/oradata/dbaviet/undotbs22_004.dbf    88    UNDOTBS21    773849088

2.Tạo undo tablespace


create undo tablespace UNDOTBS11 datafile '/u02/oradata/dbaviet/undotbs01.dbf' size 1g autoextend on next 100m;

create undo tablespace UNDOTBS22 datafile '/u02/oradata/dbaviet/undotbs02.dbf' size 1g autoextend on next 100m;

3.Set undo về instance 1, instance 2


select * From gv$instance;

alter system set undo_tablespace=UNDOTBS11 sid='dbaviet1';

alter system set undo_tablespace=UNDOTBS22 sid='dbaviet2';

-- Check lai
select * from gv$parameter where name like '%undo%'

4.RESTARTTẠI TỪNG INSTANCE

SQL> shutdown immediate
SQL> startup

5.Drop undo

DROP TABLESPACE UNDOTBS1 including contents and datafiles;
DROP TABLESPACE UNDOTBS2 including contents and datafiles;

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