Oracle 11g R2 ADG Operations

--================ Oracle ADG operations and Maintenance ================

--======== Test Log Transfer ========

1. On primary, view the last archived log, Force log switchover

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. On standby, check that the new archived log has reached standby and applied.

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#;

--========= Protection Mode ==========

Primary database has three modes of protection:
???? "Maximum Availability"
???? Transactions on the primary does not commit until redo information have been written to the online redo log and the standby R Edo logs of least one standby location. If no standby location was available, it acts in the same manner as maximum performance mode until a standby becomes Availa Ble again.

??? "Maximum Performance"
???? Transactions on the primary commit as soon as redo information have been written to the online redo log. Transfer of redo information to the standby server are asynchronous, so it does no impact on performance of the primary.

??? "Maximum Protection"
???? Transactions on the primary does not commit until redo information have been written to the online redo log and the standby R Edo logs of least one standby location. If not suitable standby are available, the primary database shuts down.

By default, for a newly created standby database,primary database is maximum performance mode.
Select Protection_mode from V$database;

You can use the following command to switch.

--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 and Standby switch roles without losing data or reset of redo logs.

--Execute the following command on the 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;

--Execute the following command on the 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;

When you are finished, test the log transfer as before. If all works fine, you can implement another switchover, also called switchback, to switch primary database back to the original server.

--============failover=============

If primary database is not available, standby database can be activated as primary using the following statement.
# ALTER DATABASE recover managed standby database finish force;
# ALTER DATABASE commit to switchover to primary;
# # ALTER DATABASE open;

--Note:oracle Corporation recommends that 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
--is 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 was removed from the Data Guard environment and
--A standby database assumes the primary database role.
--The standby database must is mounted before it can be activated with this statement.
ALTER database ACTIVATE STANDBY database;

Because standby database is now primary database, it should be backed up immediately.

The original primary database and other failure recovery, can be configured as standby.

--===========DG Gap Manual Sync =============

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* [Email protected]:/data/ora11g/archivelog

--Single File registration
ALTER DATABASE register logfile '/data/ora11g/archivelog/arch_1_41_830282966.log ';

Select Max (sequence#) from V$archived_log;

--Multiple file registration
SCP gap* [Email protected]:/data/ora11g/archivelog/
Rman Target/
Catalog start with '/data/ora11g/archivelog/';

Oracle 11g R2 ADG Operations