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/';