1. Check
Sử dụng Oracle Broker kiểm tra trạng thái cấu hình DataGuard:
dgmgrl /
show configuration;
show database verbose dbaviet
Hoặc sử dụng SQL kiểm tra apply lag trên Standby:
Kiểm tra trạng thái các process sử dụng cho đồng bộ dữ liệu trên Standby
ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS'
SELECT PROCESS, CLIENT_PROCESS, THREAD#, SEQUENCE#, STATUS, BLOCK#, BLOCKS FROM GV$MANAGED_STANDBY order by PROCESS, thread#, SEQUENCE#;
Check apply archived log:
SQL> !date
Mon Apr 5 15:23:15 +07 2021
SELECT sequence#, first_time, next_time, applied FROM v$archived_log
where next_time>sysdate-1 ;
-- Đảm bảo primary và standby max sequence# bằng nhau:
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
19988
--Check sự chênh lệch sequence
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received",
APPL.SEQUENCE# "Last Sequence Applied",
(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME )
IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME )
IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;
SQL>
Thread Last SequenceReceived Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 19988 19988 0
-- Đảm bảo không có row nào
SELECT NAME, thread#, sequence#, next_time, first_change#, creator, fal,
registrar, archived, applied, deleted,
TRUNC (blocks * block_size / 1024 / 1024) || 'M' arc
FROM v$archived_log
WHERE thread# = 1 AND sequence# >= (SELECT MAX (sequence#)
FROM v$archived_log
WHERE applied = 'YES' AND thread# = 1)
and dest_id=2
UNION ALL
SELECT NAME, thread#, sequence#, next_time, first_change#, creator, fal,
registrar, archived, applied, deleted,
TRUNC (blocks * block_size / 1024 / 1024) || 'M' arc
FROM v$archived_log
WHERE thread# = 2 AND sequence# >= (SELECT MAX (sequence#)
FROM v$archived_log
WHERE applied = 'YES' AND thread# = 2)
and dest_id=2
ORDER BY thread#, sequence# DESC;
Kiểm tra transfer được archive log từ Primary sang Standby
§ Primary: Tạo log switch
ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';
-- Kiểm tra sequence# mới nhất: ví dụ 1131595 1/29/2021 2:16:20 PM 1/29/2021 3:32:26 PM
SELECT sequence#, first_time, next_time
FROM v$archived_log;
ALTER SYSTEM SWITCH LOGFILE;
-- Kiểm tra sequence# hiện tại mới sinh ra: ví dụ 1131596 1/29/2021 3:32:26 PM 1/29/2021 4:47:06 PM
SELECT sequence#, first_time, next_time
FROM v$archived_log;
§ Standby: Kiểm tra xem archive log mới sinh ra đã sang standby và đã apply chưa
ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';
-- Nếu equence# là 131596 thì OK
SELECT sequence#, first_time, next_time
FROM v$archived_log;
Note: Nếu không transfer được archive log từ primary sang standby hãy kiểm tra:
- Đường mạng giữa primary & standby
- Listener trên standby
- Standby có đang mount hoặc open hay không.
- Vùng chứa archive log (+RECO) trên standby có bị đầy hay không
--2.START/STOP
2.1. Start/Stop broker
alter system set dg_broker_start=true sid='*'
alter system set dg_broker_start=false sid='*';
2.2. Start/Stop chế độ apply trên Primary
Cách 1: Dùng Broker
--Khởi động truyền log/archive trên 1 node database dbaviet
dgmgrl /
edit database dbavietset state='TRANSPORT-ON';
--Dừng truyền log/archive trên database dbaviet
dgmgrl /
edit database dbavietset state='TRANSPORT-OFF';
Check lai:
DGMGRL> SHOW DATABASE dbaviet;
Cách 2: Dùng SQL
-- dbaviet
SQL> show parameter archive_dest_state_2
-- Enable
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=MEMORY SID='*';
--Disable đẩy archivelog sang dest_2
ALTER SYSTEM SET log_archive_dest_state_2='DEFER' SCOPE=BOTH SID='*';
Khi tắt sẽ có log:
Fri Jul 16 10:31:10 2021
FAL[server, ARC3]: FAL archive failed, see trace file.
ARCH: FAL archive failed. Archiver continuing
ORACLE Instance dbaviet_dg1 - Archival Error. Archiver continuing.
FAL[server, ARC3]: FAL archive failed, see trace file.
ARCH: FAL archive failed. Archiver continuing
ORACLE Instance dbaviet_dg1 - Archival Error. Archiver continuing.
FAL[server, ARC3]: FAL archive failed, see trace file.
ARCH: FAL archive failed. Archiver continuing
ORACLE Instance dbaviet_dg1 - Archival Error. Archiver continuing.
FAL[server, ARC3]: FAL archive failed, see trace file.
2.3. Start/Stop chế độ apply trên standby
-- Cách 1: Dùng Broker
1. Bật apply trên database dbaviet_standby
dgmgrl /
edit database dbaviet_standby set state='APPLY-ON';
show database dbaviet_standby
2. Dừng apply trên database dbaviet_standby
dgmgrl /
edit database dbaviet_standby set state='APPLY-OFF';
-- Cách 2: Dùng SQL
-- Start
alter database recover managed standby database using current logfile disconnect;
hoặc:
alter database recover managed standby database disconnect from session;
-- Stop
alter database recover managed standby database cancel;
2.4. Tự động, manual thêm datafile
alter system set standby_file_management=AUTO;
alter system set standby_file_management=MANUAL;
