Oracle 11g R2 ADG 運維

--================ Oracle ADG 運維 ================

--========測試日志傳輸========

1.在primary上，查看最后歸檔的日志，強制日志切換

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SELECT sequence#, first_time, next_time
FROM   v$archived_log
ORDER BY sequence#;

ALTER SYSTEM SWITCH LOGFILE;

2.在standby上，查看新歸檔的日志已經到達standby，并應用。

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SELECT sequence#, first_time, next_time, applied
FROM   v$archived_log
ORDER BY sequence#;

--=========保護模式==========

primary database有三種保護模式：
     【Maximum availability】
     Transactions on the primary do not commit until redo information has been written to the online redo log and the standby redo logs of at least one standby location. If no standby location is available, it acts in the same manner as maximum performance mode until a standby becomes available again.

    【Maximum Performance】
     Transactions on the primary commit as soon as redo information has been written to the online redo log. Transfer of redo information to the standby server is asynchronous, so it does not impact on performance of the primary.

    【Maximum Protection】
     Transactions on the primary do not commit until redo information has been written to the online redo log and the standby redo logs of at least one standby location. If not suitable standby location is available, the primary database shuts down.

默認，對于一個新創建的standby database，primary database是maximum performance模式。
select protection_mode from v$database;

可以使用以下命令切換。

-- Maximum Availability.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=sicilybak AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sicilybak';
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

-- Maximum Performance.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=sicilybak NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sicilybak';
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

-- Maximum Protection.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=sicilybak AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sicilybak';
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;
ALTER DATABASE OPEN;

--========Switchover========

primary和standby切換角色，而不丟失數據或者reset of redo logs。

--在primary上執行以下命令

-- Convert primary database to standby
CONNECT / AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;

-- Shutdown primary database
SHUTDOWN IMMEDIATE;

-- Mount old primary database as standby database
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

--在standby上執行以下命令

-- Convert standby database to primary
CONNECT / AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

-- Shutdown standby database
SHUTDOWN IMMEDIATE;

-- Open old standby database as primary
STARTUP;

完成后，像之前一樣測試日志傳輸。如果都工作正常，可實施另一個switchover，也叫做switchback，將primary database切換回原來服務器。

--============Failover=============

如果primary database不可用，standby database可以使用如下語句激活為primary。
## alter database recover managed standby database finish force;
## alter database commit to switchover to primary;
## alter database open;

-- Note:Oracle Corporation recommends that you perform a failover operation
-- using the ALTER DATABASE RECOVER MANAGED STANDBY DATABASE statement
-- with the FINISH or FINISH SKIP keywords rather than a forced failover operation whenever possible.
-- A forced failover operation renders other standby databases that
-- are not participating in the failover operation
-- unusable as standby databases to the newly activated primary database.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

-- This statement performs a forced failover operation,
-- in which the primary database is removed from the Data Guard environment and
-- a standby database assumes the primary database role.
-- The standby database must be mounted before it can be activated with this statement.
ALTER DATABASE ACTIVATE STANDBY DATABASE;

因為standby database現在是primary database，應該立即備份。

原來的primary database等故障恢復后，可以配置為standby。

--===========DG Gap手動同步=============

select process,sequence#,status from v$managed_standby;
select * from v$archive_gap;
select sequence#,applied from v$archived_log order by sequence#;
select name from v$archived_log where thread#=1 and dest_id=1 and sequence# between 7 and 10;

scp *4* oracle@dgtest:/data/ora11g/archivelog

--單個文件注冊
alter database register logfile '/data/ora11g/archivelog/arch_1_41_830282966.log';

select max(sequence#) from v$archived_log;

--多個文件注冊
scp gap* oracle@dgtest:/data/ora11g/archivelog/
rman target /
catalog start with '/data/ora11g/archivelog/';