Standby - Issues

#Standby is behind

 

Check#: Chk Arch Log process , Kill if needed on Both side

 

Check#: Chk if Arch Dest & dest State is ENABLE

 

Check#: Chk if we have enough Arch Process available

 

Check#: if Main is sending logs and Standby is Receiving Logs

 

Check#: if  at standby mrp is active

 

Check# primary  DG_COFIG  see example below

 

 ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(DB11G,DB11G_STBY)';

 

Disable/Enable archive log destinations Primary

alter system set log_archive_dest_state_2 = 'defer';
alter system set log_archive_dest_state_2 = 'enable';

 

Stop the Data Guard broker [both]

alter system set dg_broker_start=false;

alter system set dg_broker_start=true;

  

ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;

ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30;

ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

 

#Check if primary has errors shipping logs

 

Select Status, Error from v$Archive_dest where dest_id=2;    < Change this number when applicable>

 

#Resolving GAP or MRP is not applying or log is not getting shipped

 

Try Killing Arc Proces on both Databases primary & Stby  , Try Disabling and enabling Dest to start the process (  in Primary Database)

 

# Archivelog Gaps in Standby     

 

 select * from v$archive_gap;

 

# Monitor in Standby database Check for MRP & Arch Processes, Which node MRP is active 

 

select inst_id,process, status, thread#, sequence#, block#, blocks  from   gv$managed_standby;

 

#which standby node is applying the Logs (run this on Standby ,Check for MRP process and Node)

 

select INST_ID,PROCESS, STATUS FROM GV$MANAGED_STANDBY;

  

SELECT DEST_ID,dest_name,status,type,srl,RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS;

RECOVERY_MODE
-----------------------
MANAGED REAL TIME APPLY

RECOVERY_MODE
-------------
MANAGED

 

-- query dest Error run from Primary

 

select dest_name,status,type,archived_seq#,applied_seq#,applied_thread#,error,destination

from v$archive_dest_status

where destination is not null;

 

Tables

 

v$logfile

V$log_history 

v$archived_log

v$archive_gap

V$ARCHIVE_DEST

V$ARCHIVE_DEST_STATUS

 

select name,database_role role,log_mode, protection_mode,protection_level from v$database;

 

select force_logging,remote_archive, supplemental_log_data_pk,

supplemental_log_data_ui, switchover_status,dataguard_broker 

from v$database;

 

 

Finding Missing Logs on 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#)
/

 

Data Guard Queries

 

select message, timestamp

from gv$dataguard_status

where severity in ('Error','Fatal') order by timestamp

/

 

select INST_ID ,ERROR_CODE,MESSAGE_NUM,TIMESTAMP,substr(MESSAGE,1,30)

from

gv$dataguard_status

/

 

select group#,thread#,sequence#,bytes,archived,status from v$log;

 

Is Standby in synch

 

  -- Run this in both Side Primary / Standby they should be same or close

  select max(sequence#) from gv$log_history;

  

 Archive Delay ( run this on Standby node)

select INST_ID,PROCESS,PID,STATUS,THREAD#,DELAY_MINS
from gv$managed_standby;

 

-- run this on Standby To see the Received and Applied Logs
 

set linesize 200
select INSTANCE_NAME,HOST_NAME,STARTUP_TIME from v$instance;
column  Genetated_time format a20
SELECT a.thread#,a.SEQUENCE# MaxSeq, to_char(a.FIRST_TIME,'dd-mm-yy hh24:mi AM') Genetated_time,'Received and Applied' Comments, applied
FROM GV$ARCHIVED_LOG a
where a.sequence# = ( select max(b.sequence#) from V$ARCHIVED_LOG b where applied ='YES' and a.thread#=b.thread#)
union
SELECT a.thread#,a.SEQUENCE# MaxSeq, to_char(a.FIRST_TIME,'dd-mm-yy hh24:mi AM') Genetated_time,'Max Received but not applied' Comments,applied
FROM GV$ARCHIVED_LOG a
where a.sequence# = ( select max(b.sequence#) from V$ARCHIVED_LOG b where applied !='YES' and a.thread#=b.thread#)
/ 

---------------------------------------------------

Restarting Standby server

---------------------------------------------------

 

 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

shutdown immediate;

startup mount;

alter database recover managed standby database disconnect from session;

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

 

=====================================================================================What to do if Oracle would wait forever for the completion of the transfer.

Stop apply process and tell not use the standby redologs.(ie  not use the “using current logfile”)

Once it caught up then tell it to use the current logfile.

=====================================================================================

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

 

alter database recover managed standby database PARALLEL 16 disconnect from session;

 

Watched the alert log to see that it started to apply logs.

 

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

 

alter database recover managed standby database using current logfile PARALLEL 16 disconnect from session;
 

===================================================================================== Recover Standby after proving logs

=====================================================================================

 

STARTUP NOMOUNT

ALTER DATABASE MOUNT STANDBY DATABASE;

RECOVER STANDBY DATABASE # uses archiving location for logs specified in init.ora

OR

RECOVER FROM '/apps/oracle/product/10.2.0/db/dbs/arch' STANDBY DATABASE # specifies non-default location

 

 

Test Connectivity Using SQLPLUS Command

 

log_archive_dest_3                   string      service="(DESCRIPTION=(ADDRESS

                                                 _LIST=(ADDRESS=(PROTOCOL=tcp)(

                                                 HOST=oramo02)(PORT=1521)))(CON

                                                 NECT_DATA=(SERVICE_NAME=pweb_X

                                                 PT.oracle.cfx)(INSTANCE_NAME=p

                                                 web2)(SERVER=dedicated)))",

                                                  LGWR ASYNC NOAFFIRM delay=0 O

                                                 PTIONAL max_failure=0 max_conn

                                                 ections=1   reopen=300 db_uniq

                                                 ue_name="pweb" register net_ti

                                                 meout=180  valid_for=(online_l

                                                 ogfile,primary_role)

 

 

sqlplus  system/@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=oramo02)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=pweb_XPT.oracle.cfx)(INSTANCE_NAME=pweb2)(SERVER=dedicated)))

  

Recover Standby Using primary Database Logs

 

Step1:

----------

copy File from pweb to  /mnt/oracle/software/pweb_11g_upgrade/

  

Step2:

----------  

cp /mnt/oracle/software/pweb_11g_upgrade/thread_2_seq_103286.38830.795929303 +dg_backup2/pcfxm/archivelog/logsfrompweb/pweb_2_103286_774872180.dbf

cp /mnt/oracle/software/pweb_11g_upgrade/thread_2_seq_103287.38993.795931233 +dg_backup2/pcfxm/archivelog/logsfrompweb/pweb_2_103287_774872180.dbf

  

Step3:

----------  

ALTER DATABASE REGISTER OR REPLACE LOGFILE  '+dg_backup2/pcfxm/archivelog/logsfrompweb/pweb_2_103286_774872180.dbf' for 'WEB_DOWNSTREAM_CAPTURE';

ALTER DATABASE REGISTER OR REPLACE LOGFILE  '+dg_backup2/pcfxm/archivelog/logsfrompweb/pweb_2_103287_774872180.dbf' for 'WEB_DOWNSTREAM_CAPTURE';

 

 

Step4:

========

a)            Killed All Arch Processes in oramo18

b)            Increased Archive processes to 10

c)            Switch Logfile

  

alter system set dg_broker_start=false;

alter system set dg_broker_config_file1='+DG_TIER1/DVLP/DATAGUARD/dvlp_dg1.dat' scope = both  sid='*';

alter system set dg_broker_config_file2='+DG_TIER1/DVLP/DATAGUARD/dvlp_dg2.dat' scope = both  sid='*';

ALTER SYSTEM SET DG_BROKER_START=TRUE scope=both sid='*';

 

SELECT LAST_FAILOVER_TIME, LAST_FAILOVER_REASON FROM V$FS_FAILOVER_STATS;

 

select name,database_role,switchover_status,dataguard_broker,guard_status from  gv$database;

   

Issue : Stsnby was receiving logs from primary but it was not able to resolve gap

 

Standby SQL> select * from v$archive_gap;

 

   THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#

---------- ------------- --------------

         1        263144         263157

         2        219779         219792

 

-- primary database

 

 

rman

connect target /

 

-- check if archlog is included in the backup

RMAN> list backup

 

run {

ALLOCATE CHANNEL ch1 DEVICE TYPE DISK ;

RESTORE ARCHIVELOG FROM SEQUENCE 263143 UNTIL SEQUENCE 263157;

RELEASE CHANNEL ch1;

}

 

run {

ALLOCATE CHANNEL ch1 DEVICE TYPE DISK;

RESTORE ARCHIVELOG FROM SEQUENCE 219779 UNTIL SEQUENCE 219792 thread 2;

RELEASE CHANNEL ch1;

}

 

Once restore is done standby got it and everything looked good

 

 How to change the Archive of Primary going to ASMDISKS on Standby Node

 

Standby Node:

 

SQL> show parameter db_recovery_file_dest

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest                string      +DG_BACKUP

db_recovery_file_dest_size           big integer 100000G

 

alter system set log_archive_dest_1='LOCATION=use_db_recovery_file_dest, valid_for=(ALL_ROLES,ONLINE_LOGFILE)';

 

DGMGRL> show database verbose <dbname> StandbyArchiveLocation

 

DGMGRL>  edit database dvlpb set property StandbyArchiveLocation='USE_DB_RECOVERY_FILE_DEST'

  

Issue Error at Standby Host alert log

 

Fatal NI connect error 12545, connecting to:

 (DESCRIPTION=(LOAD_BALANCE=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=scan-dbaovm.svc.cfx)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dvlp.oracle.cfx)(CID=(PROGRAM=oracle)(HOST=twebs01)(USER=oracle))))

 

  VERSION INFORMATION:

        TNS for Linux: Version 11.2.0.2.0 – Production

 

        Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.2.0 - Production

        TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.2.0 - Production

  Time: 25-AUG-2014 09:20:16

  Tracing not turned on.

  Tns error struct:

    ns main err code: 12545

 

TNS-12545: Connect failed because target host or object does not exist

    ns secondary err code: 12560

    nt main err code: 515

 

TNS-00515: Connect failed because target host or object does not exist

    nt secondary err code: 0

    nt OS err code: 0

 

Fix

 

Look into FAL_SERVER parameter and check for the Host Name  Fix the Host Name

  

Issue ERROR at line 1: ORA-32021: parameter value longer than 255 characters  

 

Fix : Replace ,   with ',' somewhere in the string to use this as Cancat

 

 

Bad : alter system set FAL_SERVER='(DESCRIPTION=(LOAD_BALANCE=ON)(ADDRESS = (PROTOCOL = TCP)(HOST = scan-dbaovm.svc.cfx)(PORT = 1521))(CONNECT_DATA=(SERVICE_NAME = dvlp.oracle.cfx))),(DESCRIPTION = (LOAD_BALANCE = ON)(ADDRESS = (PROTOCOL= TCP)(HOST = scan-dbaovm-b.svc.cfx)(PORT = 1521))(CONNECT_DATA = (SERVICE_NAME = dvlpb.oracle.cfx)))'

 

Good : alter system set FAL_SERVER='(DESCRIPTION=(LOAD_BALANCE=ON)(ADDRESS = (PROTOCOL = TCP)(HOST = scan-dbaovm.svc.cfx)(PORT = 1521))(CONNECT_DATA=(SERVICE_NAME = dvlp.oracle.cfx)))','(DESCRIPTION = (LOAD_BALANCE = ON)(ADDRESS = (PROTOCOL= TCP)(HOST = scan-dbaovm-b.svc.cfx)(PORT = 1521))(CONNECT_DATA = (SERVICE_NAME = dvlpb.oracle.cfx)))'

 

 

Turn on fal tracing on the primary db

alter system set LOG_ARCHIVE_TRACE = 128;

 

Failover

 

login to standby host as oracle

 

sqlplus / as sysdba

 

SQL> alter database recover managed standby database cancel;

SQL> alter database activate  standby database;

SQL> alter database open;

  

DGMGRL

 

SHOW CONFIGURATION VERBOSE

 

SHOW DATABASE VERBOSE 'pwebb';

 

SHOW INSTANCE  pweb1 on database    <dbuniquename>

 

show database 'pweb' 'InconsistentProperties'

 

show database 'pweb' 'InconsistentLogXptProps';

 

SHOW DATABASE 'pwebb'  'StatusReport';

 

SHOW DATABASE 'pwebc' 'latestlog';

 

SHOW DATABASE 'pweb' 'LogXptStatus';

 

SHOW DATABASE 'pwebb' 'LogXptStatus';

 

- List Info for Specific DB eg DB Mode(primary or Stby), Apply Status,

 

SHOW DATABASE 'pwebc'        No of Instances,Which inst is applying logs

 

- List All Above,Properties & Values

 

SHOW DATABASE VERBOSE 'pwebc'   

 

- Inst level info & Standby Archive location & connect identifier

 

SHOW INSTANCE VERBOSE pweb1 on database pwebc; 

 

SHOW DATABASE VERBOSE  pwebc StandbyArchiveLocation

 

- Good one Mostly return blank if ok

 

SHOW DATABASE 'pwebc' 'StatusReport'; 

 

- Get  dbUniqueName & instance from "SHOW CONFIGURATION

 

SHOW  INSTANCE VERBOSE <instName> 'LogXptStatus' on database <dbUniqueName> 

 

---------------------------------------------------------------------------------

Changing Dataguard Properties , More Examples are below

----------------------------------------------------------------------------------

 

--EDIT CONFIGURATION SET PROTECTION MODE AS MAXPROTECTION;

 

--EDIT DATABASE 'dvlpb' SET PROPERTY 'LogXptMode'='SYNC';

 

--EDIT DATABASE 'dvlpb' SET PROPERTY 'StandbyArchiveLocation'='+DG_BACKUP';

 

-----------

Primary

-----------

--edit database prod1 set state=transport-off;

--edit database prod1 set state=transport-on;

 

-----------

Standby

-------------

--edit database prod1dr set state=apply-off;

--edit database prod1dr set state=apply-on;

change the protection mode

--------------------------

Primary

 

# Choose what level of protection you require

 sql> alter database set standby to maximize performance;

 sql> alter database set standby to maximize availability;

 sql> alter database set standby to maximize protection;

 

========================================================================

How to change the Archive of Primary going to ASMDISKS on Standby Node

=========================================================================

Standby Node:

--------------

SQL> show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest                string      +DG_BACKUP

db_recovery_file_dest_size           big integer 100000G

 

--alter system set log_archive_dest_1='LOCATION=use_db_recovery_file_dest, valid_for=(ALL_ROLES,ONLINE_LOGFILE)';

 

DGMGRL> show database verbose <dbname> StandbyArchiveLocation

 

DGMGRL>  edit database dvlpb set property StandbyArchiveLocation='USE_DB_RECOVERY_FILE_DEST'