Yesterday
I was troubleshooting Standby database sync issue with primary, everything
was normal then what was the issue?
1.
Standby Server was rebooted 2 days ago
oracle@hostdr0007:/home/oracle>
uptime
03:19am up
2 days 0:29, 2 users, load average: 0.20, 0.28, 0.36
2.
Investigate MRP and lag
SQL>
select PROCESS,THREAD#,SEQUENCE#,STATUS from gv$managed_standby;
PROCESS
THREAD# SEQUENCE# STATUS
---------
---------- ---------- ------------
ARCH
0 0 CONNECTED
ARCH
0 0 CONNECTED
ARCH
0 0 CONNECTED
ARCH
0
0 CONNECTED
MRP0
1 95886 WAIT_FOR_LOG
**Mrp was
running , RFS was not receiving data .
3.
Check error from Primary
SQL>
select inst_id,status,error from gv$archive_dest where dest_id=2;
INST_ID STATUS ERROR
----------
--------- -----------------------------------------------------------------
1 ERROR ORA-01033: ORACLE initialization or
shutdown in progress
** looks
like pwd issue
SQL>
archive log list
Database
log
mode
Archive Mode
Automatic
archival
Enabled
Archive
destination
USE_DB_RECOVERY_FILE_DEST
Oldest
online log sequence 96160
Next
log sequence to archive 96163
Current
log sequence 96163
** lag was not huge
4. Compare
pwd file from Standby and Primary: Password file was ok, output of
cksum from both server.
cd
$ORACLE_HOME/dbs
/oracle/app/oracle/product/12102/db1a16/dbs
PACMAN]>cksum orapwPACMAN
2969142013
53760 orapwPACMAN
cd
$ORACLE_HOME/dbs
/oracle/app/oracle/product/11203/db1a13/dbs
PACMANDG]>cksum orapwPACMANDG
2969142013
53760 orapwPACMANDG
5. Voila!
Standby was running with incorrect home, notice above. May be mixed version
between a primary database and a DG physical standby supports, but no one wants
to keep it that way.
6. Oratab
was having incorrect entry of 11.2.0.3 , one last confirmation was done on
crosschecking the alert logs
PACMANDG:/oracle/app/oracle/product/11203/db1a13:N
# line added by Agent
#PACMANDG:/oracle/app/oracle/product/11203/db1a13:N
# line added by Agent
#PACMANDG:/oracle/app/oracle/product/11203/db1a13:N
# line added by Agent
7. Look
further srvctl was not configured properly, possibly upgrade misfall
/oracle/app/oracle/product/12102/db1a16/dbs
PACMANDG]>srvctl config database -d PACMAN
PRCD-1229
: An attempt to access configuration of database PACMAN was rejected because
its version 11.2.0.3.0 differs from the program version 12.1.0.2.0. Instead,
run the program from /oracle/app/oracle/product/11203/db1a13.
8. Upgrade
the configuration of the DB and all of its services
/oracle/app/oracle/product/12102/db1a16/dbs
PACMANDG]>$ORACLE_HOME/bin/srvctl upgrade database -db PACMAN -o
/oracle/app/oracle/product/12102/db1a16
9. Modify
necessary configurations, in case of any challenges try to remove and add new
config .
/oracle/app/oracle/product/12102/db1a16/dbs
PACMANDG]>srvctl modify database -d PACMAN -o
/oracle/app/oracle/product/12102/db1a16
/oracle/app/oracle/product/12102/db1a16/dbs
PACMANDG]>srvctl config database -d PACMAN
Database
unique name: PACMAN
Database
name: PACMANDG
Oracle
home: /oracle/app/oracle/product/12102/db1a16
Oracle
user: oracle
Spfile:
+DATA01_T2/PACMANdg/spfilePACMANdg.ora
Domain:
Start
options: mount
Stop
options: immediate
Database
role: PHYSICAL_STANDBY
Management
policy: AUTOMATIC
Database
instance: PACMANDG
Disk
Groups: DATA01_T2,ARCH01_T2
Services:
PACMAN_SRV
10. Restart
using srvctl, start mrp and watch for FRA .
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 (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 (THREAD#,FIRST_TIME ) IN (SELECT
THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
Thread Last Sequence Received Last Sequence Applied Difference
----------
---------------------- --------------------- ----------
1
96179
96130 49
It
worked and standby started to caught up with primary.
Good start Way to Go!!
ReplyDelete