log_archive_dest 為LGWR時需要創建standby redolog,為arch時無須設置standby redolog
--必須設置standby redolog，數據實時同步
alter database recover managed standby database using current controlfile disconnect;
--數據異步同步，當主庫切換歸檔時進行數據同步
alter database recover managed standby database disconnect from session;
--關閉MRP進程
alter database recover managed standby database cancel;
select OPEN_MODE,PROTECTION_MODE,ACTIVATION#,DATABASE_ROLE,SWITCHOVER#,SWITCHOVER_STATUS FROM V$DATABASE;
確認主庫狀態為to standby或者為sessions active、保護模式應該maximum performance、角色為PRIMARY
在備庫查詢時通常為not allowed 或者sessions active，角色為PHYSICAL STANDBY
-查看應用日志延遲時間：
select value from v$dataguard_stats where name='apply lag';
-查看接收日志延遲時間：
select value from v$dataguard_stats where name='transport lag';
-查看主庫歸檔
Primary: SQL> select thread#, max(sequence#) "Last Primary Seq Generated"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;
-查看備庫已接收歸檔
PhyStdby:SQL> select thread#, max(sequence#) "Last Standby Seq Received"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;
-查看備庫已應用歸檔
PhyStdby:SQL>select thread#, max(sequence#) "Last Standby Seq Applied"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
and val.applied in ('YES','IN-MEMORY')
group by thread# order by 1;
-查看歸檔應用詳細情況
select first_time,sequence#,applied from v$archived_log;
-查看主備庫GAP
select * from v$archive_gap;
SET LINES 300 PAGES 9999
COL name FOR a15
COL USED_PERCENT FOR a15
SELECT GROUP_NUMBER,
NAME,
TOTAL_MB / 1024 total_gb,
FREE_MB / 1024,
USABLE_FILE_MB / 1024,
ROUND ( (TOTAL_MB - USABLE_FILE_MB) * 100 / TOTAL_MB) || '%'
USED_PERCENT
FROM V$ASM_DISKGROUP
ORDER BY 1;
