Oracle Dataguard

I am in the process of putting my notes into web pages, please keep checking in later for more contents

top   prev   next  

1): startup

STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

2): shutdown

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SHUTDOWN IMMEDIATE;

3): read only
ALTER DATABASE OPEN READ ONLY;


top   prev   next  
-- standby to read only

Cancel log apply services:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Open the database for read-only access:
SQL> ALTER DATABASE OPEN READ ONLY;

-- read only to standby

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;


top   prev   next  
-- defer and un-defer log shipping
show parameter log_archive_dest_state_3
alter system set log_archive_dest_state_3=DEFER scope=memory;
alter system set log_archive_dest_state_3=ENABLE scope=memory;


top   prev   next  
-- start standby instance

STARTUP NOMOUNT;

ALTER DATABASE MOUNT STANDBY DATABASE;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

-- check applying is on or off
SELECT PROCESS, STATUS,SEQUENCE# FROM V$MANAGED_STANDBY where process like '%MRP%';
!ps -ef|grep -i mrp

-- PROCESS   STATUS        SEQUENCE#
-- --------- ------------ ----------
-- MRP0      WAIT_FOR_LOG    1591223

select open_mode,database_role,switchover_status from v$database;

grep -i -n database alert_sid.log|egrep -i "recovery|mount"


top   prev   next  
-- shutdown standby instance

SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

SHUTDOWN IMMEDIATE;


top   prev   next  

Start and Stop Apply

alter database RECOVER MANAGED STANDBY DATABASE cancel;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

sqlplus cmd> recover managed standby database cancel;

sqlplus / as sysdba
SHUTDOWN IMMEDIATE

startup nomount ;

alter database mount standby database;

ALTER DATABASE MOUNT STANDBY DATABASE;

-- check apply is on or not
select process from  v$managed_standby where process like 'MRP%'; 
!ps -ef|grep -i mrp
-- if no row selected 
alter database recover managed standby database disconnect from session; 
or 
alter database recover managed standby database using current logfile disconnect from session; -- real  time apply

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;

sqlplus cmd>recover standby database;


ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

sqlplus cmd>recover standby database disconnect;


ALTER DATABASE RECOVER MANAGED STANDBY DATABASE parallel 8;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE parallel 8 DISCONNECT FROM SESSION;

-- delay or nodelay
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DELAY 30 DISCONNECT FROM SESSION;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY DISCONNECT FROM SESSION;


-- real time
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;

sqlplus cmd> recover managed standby database disconnect using current logfile; 

failover:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

ALTER DATABASE ACTIVATE STANDBY DATABASE;

top   prev   next  

Convert A physical Standby Database Into An Active Dataguard Database

-- in sqlplus
1. open in read-only mode
alter database recover managed standby database cancel;
alter database open; 
2. return to recovery mode
alter database recover managed standby database disconnect from session;
alter database recover managed standby database using current logfile disconnect; 

shutdown immediate;
startup;
alter database recover managed standby database disconnect from session;
alter database recover managed standby database using current logfile disconnect;


top   prev   next  
-- in dataguard broker
DGMGRL> edit database prod1dr set state='apply=off';
in sqlplus sql> alter database open read only;
DGMGRL> edit database prod1dr set state='apply=on'; 


top   prev   next  

Snapshot Standby

1. Enable Flashback Database Features On the Standby Database 
2. Using Dataguard Broker to Manage the Snapshot Standby Database

-- From physical Standby To snapshot standby
DGMGRL> convert database 'prod1s' to snapshot standby;
DGMGRL> show configuration;

## redo should still being sent to the snapshot standby database
-- Check if the Managed Recovery Process (MRP) is active on the physcial standby database.
select process,status,sequence#, block# from v$managed_standby;

-- Revert Back ( From snapshot standby To physical Standby )
DGMGRL> convert database 'prod1s' to physical standby;
DGMGRL> show configuration;

## shutdown the physical standby database and restart it mount mode



top   prev   next  

Dataguard Status Monitoring

column name format a32
column value format a32
set pagesize 1000

SELECT 'db_name' name, name value FROM v$database
union all
SELECT 'DB_UNIQUE_NAME' name, DB_UNIQUE_NAME value FROM v$database
union all
select 'open_mode' name, open_mode value from v$database
union all
select 'instance_name' name, instance_name from v$database,v$instance
union all
select 'instance status' name, status from v$instance
union all
SELECT 'log_mode' name, log_mode value FROM v$database
union all
SELECT 'DATABASE_ROLE' name,DATABASE_ROLE from v$database
union all
SELECT 'PROTECTION_MODE' name,PROTECTION_MODE from v$database
union all
SELECT 'PROTECTION_LEVEL' name,PROTECTION_LEVEL from v$database
union all
SELECT 'SWITCHOVER_STATUS' name,SWITCHOVER_STATUS from v$database
union all
select 'CONTROLFILE_TYPE' name, CONTROLFILE_TYPE from v$database
union all
select 'PRIMARY_DB_UNIQUE_NAME' name, PRIMARY_DB_UNIQUE_NAME from v$database
union all
select name, value status from v$parameter where name='db_unique_name'
;



top   prev   next  
SELECT SEQUENCE#,FIRST_TIME, NEXT_TIME, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SELECT SEQUENCE#,FIRST_TIME, NEXT_TIME, APPLIED FROM V$ARCHIVED_LOG where APPLIED='YES' ORDER BY SEQUENCE#;

select a.sequence#, a.* from v$archived_log a where a.sequence# = (
SELECT max(SEQUENCE#) FROM V$ARCHIVED_LOG where APPLIED='YES');


dataguard 920  
dataguard 1120


top   prev   next  
SELECT MESSAGE FROM V$DATAGUARD_STATUS;
set linesize 180 trimspool on

column name format A64
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

SELECT THREAD#, MAX(SEQUENCE#) AS "LAST_APPLIED_LOG" FROM V$LOG_HISTORY GROUP BY THREAD#;

SELECT thread#, max(SEQUENCE#) FROM V$ARCHIVED_LOG where APPLIED='YES' group by thread#;

SELECT   a.thread#,  b.last_seq, a.applied_seq, a.last_app_timestamp, b.last_seq-a.applied_seq   ARC_DIFF 
FROM 
(SELECT  thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp 
 FROM v$archived_log WHERE applied = 'YES' GROUP BY thread#
) a,           
(SELECT  thread#, MAX (sequence#) last_seq FROM v$archived_log GROUP BY thread#) b WHERE a.thread# = b.thread#;

SELECT name, SEQUENCE#,APPLIED,FIRST_TIME, NEXT_TIME 
FROM V$ARCHIVED_LOG 
where SEQUENCE# > ( select max(sequence#) from v$archived_log ) - 10
ORDER BY SEQUENCE#;




top   prev   next  
-- archivelog group by date
SET PAGESIZE 9999
col day format a15
SELECT A.*, Round(A.Count#*B.AVG#/1024/1024) Daily_Avg_Mb 
FROM
(
SELECT To_Char(First_Time,'YYYY-MM-DD') DAY, Count(1) Count#, Min(RECID) Min#, Max(RECID) Max# 
FROM gv$log_history GROUP BY To_Char(First_Time,'YYYY-MM-DD') 
ORDER BY 1 DESC
) A,
(SELECT Avg(BYTES) AVG#, Count(1) Count#, Max(BYTES) Max_Bytes, Min(BYTES) Min_Bytes FROM gv$log 
) B;


top   prev   next  
-- floating dest_id for archived_log
select dest_id, count(*) cnt from v$archived_log group by dest_id order by dest_id;

-- too long to run
SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# 
FROM (SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1) LOCAL 
WHERE LOCAL.SEQUENCE# NOT IN (SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND THREAD# = LOCAL.THREAD#);


top   prev   next  
set lines 200 trimspool on
col name format a40
col value format a20
select * from v$dataguard_stats;


top   prev   next  
set serveroutput on;
DECLARE
   v_diff NUMBER := 0;
   v_hrs NUMBER := 0;
   v_min NUMBER := 0;
   v_sec NUMBER := 0;
   p_dte1 DATE;
   p_dte2 DATE;
   date1 long;
   date2 long;
   BEGIN
   date1 := 'select sysdate from dual';
   date2 := 'select MAX (next_time) FROM gv$archived_log where APPLIED=''YES''';
   execute immediate date1 into p_dte1;
   execute immediate date2 into p_dte2;
   v_diff := ABS(p_dte2 - p_dte1);
   v_hrs := TRUNC(v_diff, 0)*24;
   v_diff := (v_diff - TRUNC(v_diff, 0))*24; 
   v_hrs := v_hrs + TRUNC(v_diff, 0);
   v_diff := (v_diff - TRUNC(v_diff, 0))*60;
   v_min := TRUNC(v_diff, 0);
   v_sec := TRUNC((v_diff - TRUNC(v_diff, 0))*60, 0);
   DBMS_OUTPUT.put_line(
   'Gap between current time and applied time: ' ||
   TO_CHAR(v_hrs) ||' hours '||
   TO_CHAR(v_min) ||' minutes '||
   TO_CHAR(v_sec) ||' seconds ');
END;
/
set serveroutput on;


top   prev   next  
-- v$recovery_progress, applying speed and estimate
set lines 200 trimspool on
col type format a30
col ITEM format a20
col comments format a20
select * from v$recovery_progress;


top   prev   next  
-- Estimated Catchup Time
set serveroutput on feedback off
DECLARE
   v_diff NUMBER := 0;
   v_hrs NUMBER := 0;
   v_min NUMBER := 0;
   v_sec NUMBER := 0;
   p_dte1 DATE;
   p_dte2 DATE;
   date1 long;
   date2 long;
   BEGIN
   date1 := 'select sysdate from dual';
   date2 := 'select max(TIMESTAMP) from v$recovery_progress';
   execute immediate date1 into p_dte1;
   execute immediate date2 into p_dte2;
   v_diff := ABS(p_dte2 - p_dte1);
   v_hrs := TRUNC(v_diff, 0)*24;
   v_diff := (v_diff - TRUNC(v_diff, 0))*24; 
   v_hrs := v_hrs + TRUNC(v_diff, 0);
   v_diff := (v_diff - TRUNC(v_diff, 0))*60;
   v_min := TRUNC(v_diff, 0);
   v_sec := TRUNC((v_diff - TRUNC(v_diff, 0))*60, 0);
   DBMS_OUTPUT.put_line(
   'Current Time Delay '||
   TO_CHAR(v_hrs) ||' hours '||
   TO_CHAR(v_min) ||' minutes '||
   TO_CHAR(v_sec) ||' seconds ');
END;
/


top   prev   next  
-- primary db or  read-only db ?
-- not working for physical standby db
select APPLY_RATE from V$STANDBY_APPLY_SNAPSHOT;


top   prev   next  
SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS where dest_id=2;

set linesize 180 trimspool on pagesize 5000
column dest_name format a32
SELECT dest_id, dest_name,RECOVERY_MODE,db_unique_name, SYNCHRONIZATION_STATUS,SYNCHRONIZED,gap_status 
FROM V$ARCHIVE_DEST_STATUS
where db_unique_name not in ( 'NONE' );

select dest_id,status, gap_status,delay_mins from v$archive_dest_status order by dest_id;



top   prev   next  
select process, status, thread#, sequence#, block#, blocks,delay_mins from v$managed_standby ;


top   prev   next  
select a.event, a.wait_time, a.seconds_in_wait from gv$session_wait a, gv$session b 
where 
a.sid=b.sid and 
b.sid=
(
select SID from v$session where PADDR=(select PADDR from v$bgprocess where NAME='MRP0')
);


top   prev   next  
col name format a10
select * from  V$STANDBY_EVENT_HISTOGRAM;


top   prev   next  
-- compare primary db with standby db

select * from v$archive_gap;
SELECT thread#, low_sequence#, high_sequence# FROM v$archive_gap;

select current_scn from v$database;
select scn_to_timestamp(1447102) from dual;

select current_scn, scn_to_timestamp(current_scn) from v$database;


top   prev   next  
tail -10000 /u01/app/oracle/product/diag/rdbms/testdb/TESTDB/trace/alert_TESTDB.log |awk -v x="" '{if (index($0,"Media Recovery Log ")!=0) print $0" "x; else if($1=="Mon"||$1=="Tue"||$1=="Wed"||$1=="Thu"||$1=="Fri"||$1=="Sat"||$1=="Sun") x=$0}'

http://emrebaransel.blogspot.com/2008/08/how-to-query-dataguard-status.html

http://emrebaransel.blogspot.com/2013/07/data-guard-queries.html

http://docs.oracle.com/cd/E11882_01/server.112/e17022/create_ps.htm#SBYDB4728


top   prev   next  
http://www.datadisk.co.uk/html_docs/oracle_dg/active_dg.htm
http://shivanandarao-oracle.com/2012/03/19/how-to-setup-active-dataguard-in-oracle-11g/

http://shivanandarao-oracle.com/2012/03/10/creating-physical-standby-database-on-oracle-11g/

http://gavinsoorma.com/2010/09/11g-active-data-guard-enabling-real-time-query/

http://www.juliandyke.com/Blog/?p=107


http://www.oracledistilled.com/oracle-database/data-guard-switchover-to-a-physical-standby/
http://jaydeep-mukherjee.blogspot.com/2013/07/oracle-data-guard-switchoverstatus.html

-- password has to be the same:
orapwd file=$ORACLE_HOME/dbs/orapwPRIM password=oracle force=y

listener.ora
ADR_BASE_LISTENER = /u01/app/oracle
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON



top   prev   next  

Create Standby Controlfile

-- Create Control File For Physical Standby 	
ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/u02/mydb_stby.ctl';
ALTER DATABASE CREATE PHYSICAL STANDBY CONTROLFILE AS 'd:\oradata\control03.ctl';

ALTER DATABASE CREATE LOGICAL STANDBY CONTROLFILE AS 'd:\oradata\control03.ctl';


top   prev   next  

Adding Standby Logfiles

-- create standby logfile group on both standby and primary db

ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo01.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo02.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo03.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo04.log') SIZE 50M;

ALTER DATABASE 
  ADD LOGFILE GROUP 5 
(
    '/u01/oracle/data/db11g/db11glog5a.log',
    '/u02/oracle/data/db11g/db11glog5b.log'
  )
      SIZE 100M BLOCKSIZE 512;

top   prev   next  

Registering archive logfiles on a standby

ALTER DATABASE REGISTER LOGFILE '/standbydb/arch_dest/arcr_1_7.arc';
ALTER DATABASE REGISTER LOGFILE '/standbydb/arch_dest/arcr_1_8.arc';
ALTER DATABASE REGISTER LOGFILE '/standbydb/arch_dest/arcr_1_9.arc';
alter database register logfile '/standbydb/arch_dest/arch_1_10.arc'; 

rman> catalog start with '/standbydb/arch_dest'; 


top   prev   next  

standby db file and tablespace management

-- flip this flag to let standby db follow or not-follow primary db for tablespace and datafile management
alter system set standby_file_management=AUTO;
alter system set standby_file_management=MANUAL;


top   prev   next  

Manual switchover between primary database and standby database

primary-to-standby

select switchover_status from v$database;
select DATABASE_ROLE,  switchover_status  from v$database;

The SWITCHOVER_STATUS column of v$database can have the following values:

NOT ALLOWED - Either this is a standby database and the primary database has not been switched first, or this is a primary database and there are no standby databases.

SESSIONS ACTIVE - Indicates that there are active SQL sessions attached to the primary or standby database that need to be disconnected before the switchover operation is permitted.

SWITCHOVER PENDING - This is a standby database and the primary database, switchover request has been received but not processed.

SWITCHOVER LATENT - The switchover was in pending mode, but did not complete and went back to the primary database.

TO PRIMARY - This is a standby database, with no active sessions, that is allowed to switch over to a primary database.

TO STANDBY - This is a primary database, with no active sessions, that is allowed to switch over to a standby database.

RECOVERY NEEDED - This is a standby database that has not received the switchover request.


During normal operations it is acceptable to see the following values for SWITCHOVER_STATUS on the primary to be SESSIONS ACTIVE or TO STANDBY. 

During normal operations on the standby it is acceptable to see the values of NOT ALLOWED or SESSIONS ACTIVE. 

-- waiting sessions or kill sessions, then do switchover
select sid, process, program from v$session where type = 'USER';
alter session set tracefile_identifier='SWITCHOVERTEST_06032010';

alter database commit to switchover to standby;
alter database commit to switchover to physical standby;
alter database commit to switchover to physical standby with session shutdown;

-- shutdown old primary db, and start up as new standby db
shutdown immediate;
startup mount
or
startup nomount 
alter database mount standby database; 

-- Defer the remote archive destination on the old primary:
alter system set log_archive_dest_state_2=defer;

select switchover_status from v$database;
SWITCHOVER_STATUS
------------------
SWITCHOVER PENDING

if the status returns SESSIONS ACTIVE then you should append the with session shutdown clause
(alter database commit to switchover to physical standby with session shutdown;)


top   prev   next  

standby-to-primary

select  DATABASE_ROLE, switchover_status  from v$database;
select switchover_status from v$database;
SESSIONS ACTIVE
TO PRIMARY

RECOVER MANAGED STANDBY DATABASE FINISH;

alter session set tracefile_identifier='SWITCHOVERTEST_STBY_06032010';

alter database commit to switchover to primary;
alter database commit to switchover to primary with session shutdown;

alter database open;
or
shutdown immediate ;
startup;

alter system set log_archive_dest_state_2=enable;
or
alter system set log_archive_dest_2='service="proddb" LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="proddb" net_timeout=30 valid_for=(all_logfiles,primary_role)' scope = both;

-- new-standby:
recover managed standby database cancel;

recover standby database;
recover managed standby database disconnect; 
alter database recover managed standby database disconnect from session;
alter database recover managed standby database using current logfile disconnect from session;
alter database recover managed standby database parallel 2 using current logfile disconnect; 

set fal_client and fal_server and unset log_archive_dest_2 parameter in the new standby

-- new-primary:
alter system archive log current;
alter system switch logfile;

SQL> audit session whenever not successful;


top   prev   next  

Disable Active Data Guard For License Issue

Parameter to prevent license violation with Active Data Guard _

MOS Note 1436313.1

SQL> alter system set "_query_on_physical"=false scope=spfile;                             

System altered.

SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  521936896 bytes
Fixed Size		    2214936 bytes
Variable Size		  314573800 bytes
Database Buffers	  201326592 bytes
Redo Buffers		    3821568 bytes
Database mounted.
ORA-16669: instance cannot be opened because the Active Data Guard option is
disabled

SQL> host oerr ora 16669
16669, 00000, "instance cannot be opened because the Active Data Guard option is disabled"
// *Cause:  The attempt to open the instance failed because the Active Data
//          Guard option was not enabled and Redo Apply was either running
//          or was about to be started by the Data Guard broker. 
// *Action: Stop Redo Apply or set the database state to APPLY-OFF and then 
//          open the database.

It is still possible to open the Physical Standby READ ONLY when the MRP background process is stopped:

DGMGRL> edit database physt set state=apply-off;

SQL> alter database open;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY

Only drawback is that it is an undocumented parameter, 
so you should confirm with Oracle Support that it is okay to use it in your case.
Conclusion: There is an easy way to prevent license violation by accident with Real-Time Query. 

-- fix UNAME file due to standby_file_management off
alter system set standby_file_management=MANUAL;
alter database recover managed standby database cancel;

column name format A64

select * from v$recover_file where error like '%FILE%';

select file#,name from v$datafile where name like '%UNNAMED%';

select file#,name from  v$datafile where file# in (309,316,317,319);

alter database create datafile '/u01/oracle/ora11.203/dbs/UNNAMED00309' as '/u03/oracle/data/rightdbfile01.dbf';
alter database create datafile '/u01/oracle/ora11.203/dbs/UNNAMED00316' as '/u03/oracle/data/rightdbfile02.dbf';
alter database create datafile '/u01/oracle/ora11.203/dbs/UNNAMED00317' as '/u03/oracle/data/rightdbfile03.dbf';
alter database create datafile '/u01/oracle/ora11.203/dbs/UNNAMED00319' as '/u03/oracle/data/rightdbfile04.dbf';

-- for ASM or OMF files, just use the following 
SQL> alter database create datafile '/u01/oracle/ora11.203/dbs/UNNAMED00309' as new;

alter system set standby_file_management=AUTO;

select open_mode from v$database;
alter database recover managed standby database disconnect from session;