OraFAQ Forum: Data Guard » Recover Physical Standby Database Error

Home » Server Options » Data Guard » Recover Physical Standby Database Error (Oracle 11g database enterprise - 11.2.0.4.0 - RHEL 6)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Recover Physical Standby Database Error [message #681560]

Tue, 04 August 2020 02:19

keenweng2001
Messages: 9
Registered: August 2020

Junior Member

I am trying to resolve a DG gaps by following the link
https://dbaclass.com/article/how-to-recover-standby-database-when-archive-logs-are-missing-in-primary

When i start to recover the standby database , i hit some error. I cross check the list of arch logs are available in the /u03/uat1/arch directory . How do i fixed this issues without re-initialize the entire standby database ?

PRIMARY :

Code: [Select all] [Show/ hide]

RMAN> run {
allocate channel c1 type disk format '/u04/resync_standby_manual/uat1/rman_bkup%U.rmb';
backup incremental from scn 211342032 database;
}

alter database create standby controlfile as '/u04/resync_standby_manual/uat1/PG_standby_control.ctl';

STANDBY:
During recover database at standby server using the incremental backup and standby control file , i hit the error below:

Code: [Select all] [Show/ hide]

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
  211342032

cp /u04/temp/resync_standby_manual/uat1/PG_standby_control.ctl /u01/app/oracle/oradata/uat1/control01.ctl

catalog start with '/u04/resync_standby_manual/uat1/';

recover database

archived log for thread 1 with sequence 146446 is already on disk as file /u03/uat1/arch/1_146446_887236966.arc
archived log for thread 1 with sequence 146447 is already on disk as file /u03/uat1/arch/1_146447_887236966.arc
archived log file name=/u03/uat1/arch/1_16609_887236966.arc thread=1 sequence=16609
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 08/03/2020 16:10:19
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/u03/uat1/arch/1_16609_887236966.arc'
ORA-00328: archived log ends at change 174537997, need later change 174855353
ORA-00334: archived log: '/u03/uat1/arch/1_16609_887236966.arc'

SQL> select name, thread#, sequence#, archived, applied, status from
  2  v$archived_log
  3  where 174855353 between FIRST_CHANGE# and NEXT_CHANGE#;
no rows return

Report message to a moderator

Re: Recover Physical Standby Database Error [message #681561 is a reply to message #681560]

Tue, 04 August 2020 02:48

John Watson
Messages: 8968
Registered: January 2010
Location: Global Village

Senior Member

You need to start the redo transport and the managed recovery.

Report message to a moderator

Re: Recover Physical Standby Database Error [message #681562 is a reply to message #681561]

Tue, 04 August 2020 03:23

keenweng2001
Messages: 9
Registered: August 2020

Junior Member

Already start, but still failed :

Code: [Select all] [Show/ hide]

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select sequence#,process,status from v$managed_standby;

 SEQUENCE# PROCESS   STATUS
---------- --------- ------------
         0 ARCH      CONNECTED
         0 ARCH      CONNECTED
         0 ARCH      CONNECTED
         0 ARCH      CONNECTED
         0 ARCH      CONNECTED
         0 ARCH      CONNECTED
         0 ARCH      CONNECTED
         0 ARCH      CONNECTED
         0 ARCH      CONNECTED
         0 ARCH      CONNECTED
         0 ARCH      CONNECTED

 SEQUENCE# PROCESS   STATUS
---------- --------- ------------
         0 ARCH      CONNECTED
         0 ARCH      CONNECTED
         0 ARCH      CONNECTED
         0 ARCH      CONNECTED
         0 ARCH      CONNECTED
         0 ARCH      CONNECTED
         0 ARCH      CONNECTED
         0 ARCH      CONNECTED
         0 ARCH      CONNECTED
         0 ARCH      CONNECTED
         0 ARCH      CONNECTED

 SEQUENCE# PROCESS   STATUS
---------- --------- ------------
         0 ARCH      CONNECTED
         0 ARCH      CONNECTED
         0 ARCH      CONNECTED
         0 ARCH      CONNECTED
         0 ARCH      CONNECTED
         0 ARCH      CONNECTED
         0 ARCH      CONNECTED
         0 ARCH      CONNECTED
         0 RFS       IDLE
         0 RFS       IDLE
         0 RFS       IDLE

 SEQUENCE# PROCESS   STATUS
---------- --------- ------------
         0 RFS       IDLE
         0 RFS       IDLE
         0 RFS       IDLE
         0 RFS       IDLE
         0 RFS       IDLE

38 rows selected.

alert logs:

Code: [Select all] [Show/ hide]

Tue Aug 04 16:06:34 2020
alter database recover managed standby database disconnect from session
Attempt to start background Managed Standby Recovery process (uat1)
Tue Aug 04 16:06:34 2020
MRP0 started with pid=63, OS id=25143
MRP0: Background Managed Standby Recovery process started (uat1)
 started logmerger process
Tue Aug 04 16:06:39 2020
Managed Standby Recovery not using Real Time Apply
Parallel Media Recovery started with 4 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log /u03/uat1/arch/1_16609_887236966.arc
Errors with log /u03/uat1/arch/1_16609_887236966.arc
MRP0: Background Media Recovery terminated with error 328
Errors in file /u01/app/oracle/diag/rdbms/uat1/uat1/trace/uat1_pr00_25145.trc:
ORA-00328: archived log ends at change 174537997, need later change 174855353
ORA-00334: archived log: '/u03/uat1/arch/1_16609_887236966.arc'
Recovery interrupted!
Completed: alter database recover managed standby database disconnect from session

MRP0: Background Media Recovery process shutdown (uat1)

Tue Aug 04 16:10:23 2020
RFS[388]: Assigned to RFS process 25248
RFS[388]: Opened log for thread 1 sequence 146689 dbid 593542106 branch 887236966
Archived Log entry 198 added for thread 1 sequence 146689 rlc 887236966 ID 0x261acf07 dest 2:
Tue Aug 04 16:10:24 2020
Primary database is in MAXIMUM AVAILABILITY mode
Standby controlfile consistent with primary
Standby controlfile consistent with primary
RFS[389]: Assigned to RFS process 25250
RFS[389]: No standby redo logfiles created for thread 1
RFS[389]: No standby redo logfiles selected (reason:7)

Report message to a moderator

Re: Recover Physical Standby Database Error [message #681564 is a reply to message #681562]

Tue, 04 August 2020 05:24

John Watson
Messages: 8968
Registered: January 2010
Location: Global Village

Senior Member

Quote:

You need to start the redo transport and the managed recovery.

That is two things you need to do. It looks as though you have done only one of them.

Apart from that, you need standby logfiles and your recovery command should include the USING CURRENT LOGFILE clause.

Report message to a moderator

Re: Recover Physical Standby Database Error [message #681566 is a reply to message #681564]

Tue, 04 August 2020 10:22

keenweng2001
Messages: 9
Registered: August 2020

Junior Member

i have try using recover with current logfile option but same error . I could not start the redo transport. I try apply-off and apply-on in DGMGRL but it still showing Redo apply is stopped .

i think i need to resolve this error : ORA-16086 first before performing the recovery again . When can i find the exact problem when transferring the log file to standby server ? any trace log ?

Code: [Select all] [Show/ hide]

DGMGRL> show configuration

Configuration - uat_dgConfig

  Protection Mode: MaxAvailability
  Databases:
    uat  - Primary database
      Error: ORA-16810: multiple errors or warnings detected for the database

    uat1 - Physical standby database
      Error: ORA-16810: multiple errors or warnings detected for the database

Fast-Start Failover: DISABLED

Configuration Status:
ERROR


DGMGRL> show database uat1

Database - uat1

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   (unknown)
  Apply Lag:       868 days 23 hours 2 minutes 25 seconds (computed 137 seconds ago)
  Apply Rate:      (unknown)
  Real Time Query: OFF
  Instance(s):
    uat1

  Database Error(s):
    ORA-16766: Redo Apply is stopped

Database Status:
ERROR


SYS@uat>select dest_id, status, error from v$archive_dest where target='Y';

   DEST_ID STATUS	ERROR
-----------------------------------------------------------------
         2 ERROR	ORA-16086: Redo data cannot be written to the standby redo log

SQL> SELECT group#, type, member FROM v$logfile WHERE type = 'STANDBY' order by                                                                           group#;

    GROUP# TYPE    MEMBER
--------------------------------------------------------------------------------
         4 STANDBY /u01/app/oracle/oradata/uat/stndbyuat_01.log

         5 STANDBY /u01/app/oracle/oradata/uat/stndbyuat_02.log

         6 STANDBY /u01/app/oracle/oradata/uat/stndbyuat_03.log

         7 STANDBY /u01/app/oracle/oradata/uat/stndbyuat_04.log
 
SQL> column DBID format a20
SQL> SELECT group#, dbid, thread#, sequence#, status FROM v$standby_log;

    GROUP# DBID                    THREAD#  SEQUENCE# STATUS
---------- -------------------- ---------- ---------- ----------
         4 UNASSIGNED                    0          0 UNASSIGNED
         5 UNASSIGNED                    0          0 UNASSIGNED
         6 UNASSIGNED                    0          0 UNASSIGNED
         7 UNASSIGNED                    0          0 UNASSIGNED

[Updated on: Tue, 04 August 2020 10:24]

Report message to a moderator

Re: Recover Physical Standby Database Error [message #681567 is a reply to message #681566]

Tue, 04 August 2020 11:20

John Watson
Messages: 8968
Registered: January 2010
Location: Global Village

Senior Member

Quote:

I could not start the redo transport

How did you try to start it? What was the result?

However, the Data Guard Broker is telling you that the situation is 100% disastrous. If I were you, I would remove the Broker configuration, remove the standby database, and create a new one. It is a simple task, probably much easier than tryiung to salvage somethiung from what you have now.

Report message to a moderator

Re: Recover Physical Standby Database Error [message #681568 is a reply to message #681567]

Tue, 04 August 2020 12:22

keenweng2001
Messages: 9
Registered: August 2020

Junior Member

Yes, i plan to re-configure the standby database as well. Since this is my first Data guard configuration , i am thinking if i can just backup and restore the full database and the standby control log . The rest of the setting leave it un-touch. Will it work ?

Also, i do not have the sys user password. hopefully this will not be an issues .

Code: [Select all] [Show/ hide]

$ rman target=/

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/stby.ctl';

scp all file to standby server . then catalog and restore database .After that recreate the online redo logs . Then start recovery .

Do you have any reference on step by step remove and re-configure the Data guard with physical standby server ?

Report message to a moderator

Re: Recover Physical Standby Database Error [message #681569 is a reply to message #681568]

Tue, 04 August 2020 13:10

John Watson
Messages: 8968
Registered: January 2010
Location: Global Village

Senior Member

Have you read the docs? Your description of creating a physical standby is unlike anything in here,

https://docs.oracle.com/en/database/oracle/oracle-database/19/sbydb/creating-oracle-data-guard-physical-standby.html#GUID-B511FB6E-E3 E7-436D-94B5-071C37550170

and as for removing the existing configuration, if you look up REMOVE CONFIGURATION that may help. Then all you need do is delete the files that make up your broken standby.

Apart from that, your comment regarding not knowing the SYS password is worrying. If you are not familiar with how to use the orapwd utility, you really should not be using Data Guard. THe SYS password (and SYSDG password, if you are intending to be up to date) is quite important.

Report message to a moderator

Re: Recover Physical Standby Database Error [message #681570 is a reply to message #681569]

Tue, 04 August 2020 13:19

Michel Cadot
Messages: 68749
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

... or for your version: Data Guard Concepts and Administration, Chapter 3 Creating a Physical Standby Database

Report message to a moderator

Re: Recover Physical Standby Database Error [message #681581 is a reply to message #681570]

Wed, 05 August 2020 08:29

keenweng2001
Messages: 9
Registered: August 2020

Junior Member

I try to perform a manual switch logfile in primary server and i do see the logs transfer to the standby server :

PRIMARY:

Code: [Select all] [Show/ hide]

SYS@uat>alter system switch logfile;

System altered.

SYS@uat>ARCHIVE LOG LIST;

Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     146986
Next log sequence to archive   146988
Current log sequence           146988

STANDBY:

Code: [Select all] [Show/ hide]

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;

   THREAD# Last Standby Seq Received
---------- -------------------------
         1                    146985

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;

   THREAD# Last Standby Seq Applied
---------- ------------------------
         1                    16609

Report message to a moderator

Previous Topic:	Archive log shipping
Next Topic:	Automatic database startup issues after switchiver (split from hijacked topic)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Mar 11 12:02:03 CDT 2025