Chủ Nhật, 29 tháng 11, 2020

Quản trị Archied Redo Log Files trong Oracle Database

Note: Khi enable chế độ archive log thì redo log sẽ không bị ghi đè khi đầy, khi đó sẽ đẩy vào archive redo logs (hay còn gọi archived logs)
1. Check
--Redo size
select to_char(day_,'YYYY/MM/DD') day_, redosize from
(select trunc(first_time) day_, round(sum(BLOCKS*BLOCK_SIZE) /1024/1024/1024) redosize
from V$ARCHIVED_LOG
where first_time >= to_date('2009/12/01','YYYY/MM/DD')
--and first_time < to_date('2010/01/01','YYYY/MM/DD')
and dest_id=1
group by trunc(first_time))
order by day_ desc;

--Check dung lượng archive log sinh ra theo ngày
select trunc(completion_time), round(sum(blocks*block_size)/1024/1024/1024,2) "Archived Log GB" from V$ARCHIVED_LOG
where trunc(completion_time) >= to_date('10/01/2013','dd/mm/yyyy')
and trunc(completion_time)<= to_date('20/01/2013','dd/mm/yyyy')
--and dest_id=5
group by trunc(completion_time)
order by trunc(completion_time);
• SELECT GROUP#, ARCHIVED FROM SYS.V$LOG;
GROUP#     ARC
--------   ---
       1   YES
       2   NO
• Check mod_archive:
SELECT LOG_MODE FROM SYS.V$DATABASE;
LOG_MODE
------------
NOARCHIVELOG
Hoặc:
SQL> ARCHIVE LOG LIST
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            D:\oracle\oradata\IDDB2\archive
Oldest online log sequence     11160
Next log sequence to archive   11163
Current log sequence           11163
• Check capacity archived log
select round(sum( (blocks*block_size))/1024/1024/1024) as GB from V$ARCHIVED_LOG
where dest_id=1 and trunc(completion_time)=to_date('24/11/2012','dd/mm/yyyy')
order by completion_time desc;
select round(sum( (blocks*block_size))/1024/1024/1024) as GB from V$ARCHIVED_LOG
where dest_id=1 and trunc(completion_time)=to_date('25/11/2012','dd/mm/yyyy')
order by completion_time desc; 
select round(sum( (blocks*block_size))/1024/1024/1024) as GB from V$ARCHIVED_LOG
where dest_id=1 and trunc(completion_time)=to_date('26/11/2012','dd/mm/yyyy')
order by completion_time desc;  

2. Config
• Chuyển chế độ archivelog || noarchivelog
SQL> alter system set archive_log_dest_1='LOCATION=/u05/DBAViet/archive' scope=both;
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
--SQL> alter database noarchivelog;
SQL> alter database open;
SQL>archive log list


• Thay đổi đường dẫn archive_log_dest: Online, khong can restart instance
SQL> archive log list;
SQL> show parameter archive_dest_1;
SQL> alter system set log_archive_dest_1='location=/s03/oracle/DBAViet/arch';
SQL> alter system switch logfile; 

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