dbawiki

https://community.oracle.com/mosc/discussion/comment/14183132Converting physical standby to READ /WRITE without using Snapshot Standby

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

• Log Gap Detection and Resolution - Jeff Hunter
• http://www.oracle-base.com/articles/11g/data-guard-setup-11gr2.php
• Oracle 10g - Manually Create a Physical Standby Database Using Data Guard
• Rolling Forward a Physical Standby Database Using the RECOVER Command