alter database recover managed standby cancel; -- stops redo apply
alter database recover managed standby database finish; -- initiates failover on the target standby, finish applying all received redo data and recovers current standby logs
alter database activate standby database; -- activates standby in case of errors (not all received redo data are applied).
startup nomount alter database mount standby database; alter database recover managed standby database disconnect;
alter database recover managed standby database cancel;
alter database register physical logfile '<fullpath/filename>';
select process , status , client_process , sequence# , block# , active_agents , known_agents from v$managed_standby /
shows something like this
PROCESS STATUS CLIENT_P SEQUENCE# BLOCK# ACTIVE_AGENTS KNOWN_AGENTS --------- ------------ -------- ---------- ---------- ------------- ------------ ARCH CLOSING ARCH 321071 45056 0 0 ARCH CONNECTED ARCH 0 0 0 0 ARCH CLOSING ARCH 197886 6144 0 0 ARCH CLOSING ARCH 197887 10240 0 0 MRP0 APPLYING_LOG N/A 197888 2063 17 17 RFS IDLE UNKNOWN 0 0 0 0 RFS IDLE LGWR 197888 2063 0 0 RFS IDLE ARCH 0 0 0 0 RFS RECEIVING LGWR 321072 34506 0 0 RFS IDLE UNKNOWN 0 0 0 0 RFS IDLE UNKNOWN 0 0 0 0 11 rows selected.
If data guard is not functioning correctly, check the errors in this log.
select gvi.thread#
, timestamp
, message
from gv$dataguard_status gvds
, gv$instance gvi
where gvds.inst_id = gvi.inst_id
and severity in ('Error','Fatal')
order by timestamp
, thread#
/
and
select thread# , dest_id , gvad.status , error , fail_sequence from gv$archive_dest gvad , gv$instance gvi where gvad.inst_id = gvi.inst_id and destination is not null order by thread# , dest_id /
Assuming dest_2 is the standby
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#);
On the primary
alter system set log_archive_dest_state_2 = 'defer'; alter system set log_archive_dest_state_2 = 'enable';
alter system set LOG_ARCHIVE_TRACE = 128;
alter system set dg_broker_start=false;
select database_role from v$database;
-- This script is to be run on the Standby of a Data Guard Physical Standby Site
set echo off
set feedback off
column timecol new_value tstamp
column spool_extension new_value suffix
select to_char(sysdate,'Mondd_hhmi') timecol from sys.dual;
column output new_value dbname
select value || '_' output from v$parameter where name = 'db_name';
-- Output the results to this file
spool dg_Standby_diag_&&dbname&&tstamp
set lines 132
set pagesize 500
set numformat 999999999999999
set trim on
set trims on
-- Get the current Date
set feedback on
select systimestamp from dual;
-- Standby Site Details
set heading off
set feedback off
select 'Standby Site Details' from dual;
select '********************' from dual;
set heading on
set feedback on
col db_unique_name format a15
col flashb_on format a10
select DB_UNIQUE_NAME,DATABASE_ROLE DB_ROLE,FORCE_LOGGING F_LOG,FLASHBACK_ON FLASHB_ON,LOG_MODE,OPEN_MODE,
GUARD_STATUS GUARD,PROTECTION_MODE PROT_MODE
from v$database;
-- Current SCN - this value on the primary and standby sites where real time apply is in place should be nearly the same
select DB_UNIQUE_NAME,SWITCHOVER_STATUS,CURRENT_SCN from v$database;
-- Incarnation Information
--
set heading off
set feedback off
select 'Incarnation Destination Configuration' from dual;
select '*************************************' from dual;
set heading on
set feedback on
select INCARNATION# INC#, RESETLOGS_CHANGE# RS_CHANGE#, RESETLOGS_TIME, PRIOR_RESETLOGS_CHANGE# PRIOR_RS_CHANGE#, STATUS,FLASHBACK_DATABASE_ALLOWED FB_OK from v$database_incarnation;
set heading off
set feedback off
select 'Archive Destination Configuration' from dual;
select '*********************************' from dual;
set heading on
set feedback on
-- Current Archive Locations
--
column host_name format a30 tru
column version format a10 tru
select INSTANCE_NAME,HOST_NAME,VERSION,ARCHIVER from v$instance;
column destination format a35 wrap
column process format a7
column archiver format a8
column dest_id format 99999999
select DEST_ID,DESTINATION,STATUS,TARGET,ARCHIVER,PROCESS,REGISTER,TRANSMIT_MODE
from v$archive_dest
where DESTINATION IS NOT NULL;
column name format a22
column value format a100
select NAME,VALUE from v$parameter where NAME like 'log_archive_dest%' and upper(VALUE) like 'SERVICE%';
set heading off
set feedback off
select 'Archive Destination Errors' from dual;
select '**************************' from dual;
set heading on
set feedback on
column error format a55 tru
select DEST_ID,STATUS,ERROR from v$archive_dest
where DESTINATION IS NOT NULL;
column message format a80
select MESSAGE, TIMESTAMP
from v$dataguard_status
where SEVERITY in ('Error','Fatal')
order by TIMESTAMP;
-- Redo Log configuration
-- The size of the standby redo logs must match exactly the size on the online redo logs
set heading off
set feedback off
select 'Data Guard Redo Log Configuration' from dual;
select '*********************************' from dual;
set heading on
set feedback on
select GROUP# STANDBY_GROUP#,THREAD#,SEQUENCE#,BYTES,USED,ARCHIVED,STATUS from v$standby_log order by GROUP#,THREAD#;
select GROUP# ONLINE_GROUP#,THREAD#,SEQUENCE#,BYTES,ARCHIVED,STATUS from v$log order by GROUP#,THREAD#;
-- Data Guard Parameters
--
set heading off
set feedback off
select 'Data Guard Related Parameters' from dual;
select '*****************************' from dual;
set heading on
set feedback on
column name format a30
column value format a100
select NAME,VALUE from v$parameter where NAME IN ('db_unique_name','cluster_database','dg_broker_start','dg_broker_config_file1','dg_broker_config_file2','fal_client','fal_server','log_archive_config','log_archive_trace','log_archive_max_processes','archive_lag_target','remote_login_password_file','redo_transport_user') order by name;
-- Managed Recovery State
set heading off
set feedback off
select 'Data Guard Apply Status' from dual;
select '***********************' from dual;
set heading on
set feedback on
select systimestamp from dual;
column client_pid format a10
select PROCESS,STATUS,CLIENT_PROCESS,CLIENT_PID,THREAD#,SEQUENCE#,BLOCK#,ACTIVE_AGENTS,KNOWN_AGENTS
from v$managed_standby order by CLIENT_PROCESS,THREAD#,SEQUENCE#;
exec DBMS_LOCK.SLEEP(10);
select systimestamp from dual;
select PROCESS,STATUS,CLIENT_PROCESS,CLIENT_PID,THREAD#,SEQUENCE#,BLOCK#,ACTIVE_AGENTS,KNOWN_AGENTS
from v$managed_standby order by CLIENT_PROCESS,THREAD#,SEQUENCE#;
exec DBMS_LOCK.SLEEP(10);
select systimestamp from dual;
select PROCESS,STATUS,CLIENT_PROCESS,CLIENT_PID,THREAD#,SEQUENCE#,BLOCK#,ACTIVE_AGENTS,KNOWN_AGENTS
from v$managed_standby order by CLIENT_PROCESS,THREAD#,SEQUENCE#;
set heading off
set feedback off
select 'Data Guard Apply Lag' from dual;
select '********************' from dual;
set heading on
set feedback on
column name format a12
column lag_time format a20
column datum_time format a20
column time_computed format a20
SELECT NAME, VALUE LAG_TIME, DATUM_TIME, TIME_COMPUTED
from V$DATAGUARD_STATS where name like 'apply lag';
-- If there is a lag remove the comment for the select below
--SELECT * FROM V$STANDBY_EVENT_HISTOGRAM WHERE NAME = 'apply lag' AND COUNT > 0;
set heading off
set feedback off
select 'Data Guard Gap Problems' from dual;
select '***********************' from dual;
set heading on
set feedback on
select * from v$archive_gap;
set heading off
set feedback off
select 'Data Guard Errors in the Last Hour' from dual;
select '**********************************' from dual;
set heading on
set feedback on
select TIMESTAMP,SEVERITY,ERROR_CODE,MESSAGE from v$dataguard_status where timestamp > systimestamp-1/24;
On the primary
set numwidth 15 select max(sequence#) current_seq from v$log;
On the standby
set numwidth 15
select max(applied_seq#) last_seq
from v$archive_dest_status;
select arch.thread# "Thread"
, arch.sequence# "Last Sequence Received"
, appl.sequence# "Last Sequence Applied"
, (arch.sequence# - appl.sequence#) "Difference"
from (
select thread#
, sequence#
from v$archived_log
where 1=1
and ( thread#,first_time ) in (
select thread#, max(first_time)
from v$archived_log
group by thread#
)
) arch
,
(
select thread#
, sequence#
from v$log_history
where 1=1
and ( thread#,first_time ) in ( select thread# ,max(first_time)
from v$log_history
group by thread#
)
) appl
where 1=1
and arch.thread# = appl.thread#
order by 1;
or
select a.thread#
, b.last_seq
, a.applied_seq
, to_char(a.last_app_timestamp,'DD-MON-YYYY HH24:MI:SS') 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 gv$archived_log
where applied = 'YES'
group by thread#
) a
, (select thread#
, max(sequence#) last_seq
from gv$archived_log
group by thread#
) b
where a.thread# = b.thread#
/
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
/
STANDBY_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
/
STANDBY_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
/
On the primary
set lines 100 set numwidth 15 column ID format 99 column "SRLs" format 99 column active format 99 col type format a4 select ds.dest_id id , ad.status , ds.database_mode db_mode , ad.archiver type , ds.recovery_mode , ds.protection_mode , ds.standby_logfile_count "SRLs" , ds.standby_logfile_active active , ds.archived_seq# from v$archive_dest_status ds , v$archive_dest ad where 1=1 and ds.dest_id = ad.dest_id and ad.status != 'INACTIVE' order by ds.dest_id;
On the standby
set numwidth 8 set lines 100 column id format 99 select dest_id id , archiver , transmit_mode , affirm , async_blocks async , net_timeout net_time , delay_mins delay , reopen_secs reopen , register , binding from v$archive_dest order by dest_id;
set lines 100 set pages 999 col member format a70 select st.group# , st.sequence# , ceil(st.bytes/1048576) mb , lf.member from v$standby_log st , v$logfile lf where 1=1 and st.group# = lf.group#
Assuming primary and standby already exist and have standby redologs setup, force logging etc…
Example: server1 - db_primary, server2 - db_standby
On both systems…
alter system set dg_broker_start=true;
From server1
dgmgrl sys/sys@db_primary create configuration dg_config as primary database is db_primary connect identifier is db_primary;
Configuration created, add the physical standby to it
add database db_standby as connect identifier is db_standby maintained as physical;
enable configuration;
show configuration;
show database db_primary; show database db_standby;
From server1
dgmgrl sys/sys@db_primary switchover to db_standby;
From server2
dgmgrl sys/sys@db_standby switchover to db_primary;
From server1, connect to the primary
connect / as sysdba alter database commit to switchover to standby;
Now shut it down and restart it as a standby
shutdown immediate; startup nomount; alter database mount standby database; alter database recover managed standby database disconnect from session;
connect / as sysdba alter database commit to switchover to primary;
Now shut it down and restart it as a primary
shutdown immediate; startup;
The difference between switchover and failover is that you control a switchover, a failover happens bacause the primary db is no longer available
From server2
dgmgrl sys/sys@db_standby failover to db_standby;
Backup this database immediately as it is now the only database and has no standby. This can be done with RMAN while it is running.
If flashback was not enabled, the old primary is now useless and will need to be recreated as a standby.
If, however flashback was enabled, we can use this to restart the old primary as a standby.
From server1
dgmgrl sys/sys@db_primary reinstate database db_primary;
sqlplus / as sysdba shu abort; exit;
rm $ORACLE_HOME/dbs/*<SID>.ora rm -Rf /oracle/<SID>/oradata*/* rm -Rf /oracle/<SID>/fra/* rm -Rf /oracle/<SID>/admin/* mkdir -p /oracle/<SID>/oradata1/ /oracle/<SID>/oradata2/ /oracle/<SID>/oradata3/ mkdir -p /oracle/<SID>/fra/ mkdir -p /oracle/<SID>/admin/adump
echo "*.db_name='db'" > /tmp/initdb_primary.ora export ORACLE_SID=db sqlplus / as sysdba startup nomount pfile='/tmp/initdb_primary.ora'; exit
rman target sys/sys@db_standby auxiliary sys/sys@db_primary
duplicate target database
for standby
from active database
dorecover
spfile
set db_unique_name='DB_PRIMARY' comment 'Standby'
set db_file_name_convert='/old/dbfile/dir1/','/new/dbfile/dir1/','/old/dbfile/dir2/','/new/dbfile/dir2/'
set log_file_name_convert='/old/logfile/dir1/','/new/logfile/dir1/','/old/logfile/dir2/','/new/logfile/dir2/'
nofilenamecheck;
dgmgrl sys/sys@db_standby enable database db_primary; show configuration;
After restarting the Primary and Standby databases manually, the redo logs were not being applied on the standby.
The command used to start the standby database was:
alter database recover managed standby database using current logfile disconnect from session;
It seems this is not good enough, we see a problem in the broker configuration…
(0) OTMPRODS oracle@ravotm14:/home/oracle]] dgmgrl DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information. DGMGRL> connect sys/********@otmprod Connected. DGMGRL> show configuration
Configuration - dg_otmprod
Protection Mode: MaxPerformance
Databases:
otmprod - Primary database
otmprods - **Physical standby database (disabled)**
Fast-Start Failover: DISABLED
Configuration Status: SUCCESS
DGMGRL>
We need to re-enable the Standby database in the broker configuration
DGMGRL> enable database OTMPRODS; Enabled. DGMGRL> show configuration
Configuration - dg_otmprod
Protection Mode: MaxPerformance
Databases:
otmprod - Primary database
otmprods - **Physical standby database**
Fast-Start Failover: DISABLED
Configuration Status: SUCCESS
Now check alert log. All is working again.
Run from the standby server. Trivial mods to parameterise it soon!
- !/usr/bin/ksh
- -----------
- How to call: ./dataguard_rebuild.ksh OTMPROD
- -----------
- Set environment Variables
- #########################################
export ORACLE_SID=$1
ORAENV_ASK="NO"
. oraenv
ORAENV_ASK="YES"
echo ' ORACLE_HOME is ==> ' $ORACLE_HOME
export PATH=$ORACLE_HOME/bin:$PATH
$ORACLE_HOME/bin/rman <<EORMAN >> /oracle/${ORACLE_SID}/admin/change/rebuild_dataguard_${ORACLE_SID}_$(date +%Y%m%d_%H%M).log
connect target sys/********@OTMPROD
connect auxiliary sys/********@OTMPRODS
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
SPFILE
SET db_unique_name='OTMPRODS'
set LOG_ARCHIVE_DEST_1='LOCATION=/oracle/arch/OTMPROD/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=OTMPRODS'
SET LOG_ARCHIVE_DEST_2='service=OTMPROD LGWR ASYNC valid_for=(ONLINE_LOGFILE,Primary_ROLE) db_unique_name=OTMPROD'
SET FAL_SERVER='OTMPROD'
SET FAL_CLIENT='OTMPRODS'
SET LOCAL_LISTENER='(ADDRESS = (PROTOCOL = TCP)(HOST = <standby_server>)(PORT = 1522))'
NOFILENAMECHECK;
EORMAN
Essential steps using nfs mounted filesystem (/shared_backups) shared between both primary and secondary nodes to avoid scp copies
Primary
SQL> alter system set log_archive_dest_state_2=defer scope=both;
Standby
SQL> select min(checkpoint_change#) lowest_scn from v$datafile_header order by 1;
Primary
RMAN> backup incremental from scn <lowest_scn> database format '/shared_backups/catchup_backup_%U';
Standby
SQL> alter database recover managed standby database cancel;
Standby
RMAN> catalog start with '/shared_backups';
RMAN> recover database noredo;
Primary
SQL> backup current controlfile for standby format '/shared_backups/catchup_control.ctl';
Standby
RMAN> report schema;
RMAN> shutdown immediate;
RMAN> startup nomount;
RMAN> restore standby controlfile from '/shared_backups/catchup_control.ctl'
SQL> alter database mount;
RMAN> report schema;
RMAN> run {
set newname for datafile 1 to '+DATA/<standbySID>/data1/system.dbf';
set newname for datafile 2 to '+DATA/<standbySID>/data1/sysaux.dbf';
set newname for datafile 3 to '+DATA/<standbySID>/data2/users.dbf';
set newname for datafile 4 to '+DATA/<standbySID>/data3/undotbs.dbf';
switch datafile all;
}
RMAN> run {
set newname for tempfile 1 to '+DATA/<standbySID>/data2/temptbs.dbf';
switch tempfile all;
}
RMAN> report schema;
Primary
SQL> alter system set log_archive_dest_state_2=enable scope=both;
Standby
SQL> alter database recover managed standby database cancel;
SQL> alter database recover managed standby database using current logfile disconnect from session;
SQL> select inst_id, process, thread#, sequence#, blocks, status from gv$managed_standby where process like '%MRP%';