RE: Data Guard Issue

Yes Dg can have issues, but most of them are self-inflicted or other teams around you inflicted on you.

I say that from over the years having made most every mistake possible across thousands of DG setups.  

Here is the Oracle reference note that I review for standbys

Step By Step Guide To Create Physical Standby Database Using RMAN Backup and Restore (Doc ID 469493.1)

It makes some assumptions around some of the commands that you just know the answer instead of being a highly detailed not, but it is complete at least on the restore part.  

Too many times each of us are in a rush just pick up a script we have used before for restores, but for a real restore instead of a standby restore and pick up some commandlets in the rman restore that are not correct for a standby setup.

It happens to everyone.        

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> On Behalf Of Scott Canaan ("srcdco") Sent: Wednesday, August 28, 2024 10:22 AM To: dimensional.dba_at_comcast.net; 'Jon Crisler' <joncrisler_at_gmail.com> Cc: 'Oracle-L Freelists' <oracle-l_at_freelists.org> Subject: RE: Data Guard Issue  

I did not do a resetlogs, but I’m thinking that RMAN did. Ultimately we are dumping data guard and going to O/S clustering for failover. Data guard has been nothing but problems.  

Scott Canaan ‘88
Sr Database Administrator
Information & Technology Services
Finance & Administration

Rochester Institute of Technology
o: (585) 475-7886 | f: (585) 475-7520

<mailto:srcdco_at_rit.edu> srcdco_at_rit.edu | c: (585) 339-8659

CONFIDENTIALITY NOTE: The information transmitted, including attachments, is intended only for the person(s) or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and destroy any copies of this information.  

From: oracle-l-bounce_at_freelists.org <mailto:oracle-l-bounce_at_freelists.org> <oracle-l-bounce_at_freelists.org <mailto:oracle-l-bounce_at_freelists.org> > On Behalf Of dimensional.dba Sent: Wednesday, August 28, 2024 1:11 PM To: Scott Canaan <srcdco_at_rit.edu <mailto:srcdco_at_rit.edu> >; 'Jon Crisler' <joncrisler_at_gmail.com <mailto:joncrisler_at_gmail.com> > Cc: 'Oracle-L Freelists' <oracle-l_at_freelists.org <mailto:oracle-l_at_freelists.org> > Subject: RE: Data Guard Issue  

Which is why I posted those SQL statements to see what SCN the datafiles think they are at.

Normally when it asks for a sequence number that is old the headers can be out of sync or as another person was asking if you had issued either through the rman restore or directly a resetlogs command.

The resetlogs command is the primary way you get to the recovery asking for redolog 1.      

From: oracle-l-bounce_at_freelists.org <mailto:oracle-l-bounce_at_freelists.org> <oracle-l-bounce_at_freelists.org <mailto:oracle-l-bounce_at_freelists.org> > On Behalf Of Scott Canaan ("srcdco") Sent: Wednesday, August 28, 2024 9:48 AM To: dimensional.dba_at_comcast.net <mailto:dimensional.dba_at_comcast.net> ; 'Jon Crisler' <joncrisler_at_gmail.com <mailto:joncrisler_at_gmail.com> > Cc: 'Oracle-L Freelists' <oracle-l_at_freelists.org <mailto:oracle-l_at_freelists.org> > Subject: RE: Data Guard Issue  

A standby control file. Cloud control isn’t the only issue. The secondary won’t apply the logs. It keeps saying that it needs log 1 first. I don’t know how to get past that.  

Scott Canaan ‘88
Sr Database Administrator
Information & Technology Services
Finance & Administration

Rochester Institute of Technology
o: (585) 475-7886 | f: (585) 475-7520

<mailto:srcdco_at_rit.edu> srcdco_at_rit.edu | c: (585) 339-8659

CONFIDENTIALITY NOTE: The information transmitted, including attachments, is intended only for the person(s) or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and destroy any copies of this information.  

From: dimensional.dba_at_comcast.net <mailto:dimensional.dba_at_comcast.net> <dimensional.dba_at_comcast.net <mailto:dimensional.dba_at_comcast.net> > Sent: Wednesday, August 28, 2024 12:37 PM To: Scott Canaan <srcdco_at_rit.edu <mailto:srcdco_at_rit.edu> >; 'Jon Crisler' <joncrisler_at_gmail.com <mailto:joncrisler_at_gmail.com> > Cc: 'Oracle-L Freelists' <oracle-l_at_freelists.org <mailto:oracle-l_at_freelists.org> > Subject: RE: Data Guard Issue  

You stated you backed up system with controlfile. Did you put into place a standby controlfile or a regular controlfile?  

From: dimensional.dba_at_comcast.net <mailto:dimensional.dba_at_comcast.net> <dimensional.dba_at_comcast.net <mailto:dimensional.dba_at_comcast.net> > Sent: Wednesday, August 28, 2024 9:36 AM To: 'srcdco_at_rit.edu' <srcdco_at_rit.edu <mailto:srcdco_at_rit.edu> >; 'Jon Crisler' <joncrisler_at_gmail.com <mailto:joncrisler_at_gmail.com> > Cc: 'Oracle-L Freelists' <oracle-l_at_freelists.org <mailto:oracle-l_at_freelists.org> > Subject: RE: Data Guard Issue  

Dg looks fine. The Cloud control differential probably just means it needs to be rediscovered after you get it fixed.  

ON STANDBY column CHECKPOINT_CHANGE# format 999999999999999

column UNRECOVERABLE_CHANGE# format 999999999999999

column LAST_CHANGE# format 999999999999999

column OFFLINE_CHANGE# format 999999999999999

set linesize 200

select CHECKPOINT_CHANGE#,UNRECOVERABLE_CHANGE#,LAST_CHANGE#,OFFLINE_CHANGE#,count(*) from v$datafile group by CHECKPOINT_CHANGE#,UNRECOVERABLE_CHANGE#,LAST_CHANGE#,OFFLINE_CHANGE#;  

column RESETLOGS_CHANGE# format 999999999999999

select RESETLOGS_CHANGE#,CHECKPOINT_CHANGE#,count(*) from v$datafile_header group by RESETLOGS_CHANGE#,CHECKPOINT_CHANGE#;    

ON PRIMARY column minfc format 99999999999999

column maxfc format 99999999999999

select min(FIRST_CHANGE#) minfc, max(FIRST_CHANGE#) maxfc from v$archived_log;      

From: oracle-l-bounce_at_freelists.org <mailto:oracle-l-bounce_at_freelists.org> <oracle-l-bounce_at_freelists.org <mailto:oracle-l-bounce_at_freelists.org> > On Behalf Of Scott Canaan ("srcdco") Sent: Wednesday, August 28, 2024 9:08 AM To: dimensional.dba_at_comcast.net <mailto:dimensional.dba_at_comcast.net> ; 'Jon Crisler' <joncrisler_at_gmail.com <mailto:joncrisler_at_gmail.com> > Cc: 'Oracle-L Freelists' <oracle-l_at_freelists.org <mailto:oracle-l_at_freelists.org> > Subject: RE: Data Guard Issue  

This is the configuration from the primary:  

DGMGRL> show configuration;  

Configuration - CLAWPRDA  

  Protection Mode: MaxPerformance

  Members:

  CLAWPRDB - Primary database

    CLAWPRDA - Physical standby database

      Error: ORA-16810: multiple errors or warnings detected for the member  

Fast-Start Failover: Disabled  

Configuration Status:

ERROR (status updated 28 seconds ago)    

Scott Canaan ‘88
Sr Database Administrator
Information & Technology Services
Finance & Administration

Rochester Institute of Technology
o: (585) 475-7886 | f: (585) 475-7520

<mailto:srcdco_at_rit.edu> srcdco_at_rit.edu | c: (585) 339-8659

CONFIDENTIALITY NOTE: The information transmitted, including attachments, is intended only for the person(s) or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and destroy any copies of this information.  

From: dimensional.dba_at_comcast.net <mailto:dimensional.dba_at_comcast.net> <dimensional.dba_at_comcast.net <mailto:dimensional.dba_at_comcast.net> > Sent: Wednesday, August 28, 2024 12:00 PM To: Scott Canaan <srcdco_at_rit.edu <mailto:srcdco_at_rit.edu> >; 'Jon Crisler' <joncrisler_at_gmail.com <mailto:joncrisler_at_gmail.com> > Cc: 'Oracle-L Freelists' <oracle-l_at_freelists.org <mailto:oracle-l_at_freelists.org> > Subject: RE: Data Guard Issue  

Execute the show configuration from both the primary and the standby.  

From: dimensional.dba_at_comcast.net <mailto:dimensional.dba_at_comcast.net> <dimensional.dba_at_comcast.net <mailto:dimensional.dba_at_comcast.net> > Sent: Wednesday, August 28, 2024 8:56 AM To: 'srcdco_at_rit.edu' <srcdco_at_rit.edu <mailto:srcdco_at_rit.edu> >; 'Jon Crisler' <joncrisler_at_gmail.com <mailto:joncrisler_at_gmail.com> > Cc: 'Oracle-L Freelists' <oracle-l_at_freelists.org <mailto:oracle-l_at_freelists.org> > Subject: RE: Data Guard Issue  

When you execute dgmgrl show configuration, does the database show as a snapshot database or a regular standby database?

I have had to remove the dg broker config completely including wiping out dg broker files to get some of the previous configs removed properly.  

What error do you get if you run

DBMS_DG.CONVERT_TO_PHYSICAL (      db_name IN VARCHAR2,

     severity OUT BINARY_INTEGER)

RETURN BINARY_INTEGER;   On the primary? Since you don’t have a real snapshot db anymore that command should error.    

As to

SQL> recover standby database;

ORA-01153: an incompatible media recovery is active  

To just get things rolling forward, you get this error all the time if you don’t

Alter database recover managed database cancel;

Then do your

recover standby database until cancel;    

From: oracle-l-bounce_at_freelists.org <mailto:oracle-l-bounce_at_freelists.org> <oracle-l-bounce_at_freelists.org <mailto:oracle-l-bounce_at_freelists.org> > On Behalf Of Scott Canaan ("srcdco") Sent: Wednesday, August 28, 2024 7:51 AM To: Jon Crisler <joncrisler_at_gmail.com <mailto:joncrisler_at_gmail.com> > Cc: Oracle-L Freelists <oracle-l_at_freelists.org <mailto:oracle-l_at_freelists.org> > Subject: RE: Data Guard Issue  

I’ve done the cancel / restart at least 3 times. I did do an RMAN backup, copy, “restore” along with the control file which was created just after the RMAN backup.  

Scott Canaan ‘88
Sr Database Administrator
Information & Technology Services
Finance & Administration

Rochester Institute of Technology
o: (585) 475-7886 | f: (585) 475-7520

<mailto:srcdco_at_rit.edu> srcdco_at_rit.edu | c: (585) 339-8659

CONFIDENTIALITY NOTE: The information transmitted, including attachments, is intended only for the person(s) or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and destroy any copies of this information.  

From: Jon Crisler <joncrisler_at_gmail.com <mailto:joncrisler_at_gmail.com> > Sent: Wednesday, August 28, 2024 10:42 AM To: Scott Canaan <srcdco_at_rit.edu <mailto:srcdco_at_rit.edu> > Cc: Oracle-L Freelists <oracle-l_at_freelists.org <mailto:oracle-l_at_freelists.org> > Subject: Re: Data Guard Issue  

In this case you need to cancel mrp before you try to restart it . Double check the incarnations once you get media recovery running (rman or dg mrp) . Worst case you will need a full rman copy to restore the standby , and use a control file from “after” the rman backup , otherwise you have to manually catalog your rman backups.

Sent from my Atari 2600  

On Aug 28, 2024, at 10:20 AM, Scott Canaan <dmarc-noreply_at_freelists.org <mailto:dmarc-noreply_at_freelists.org> > wrote:



We are running Oracle 19.23. We have a data guard configuration that isn’t working. About 2 months ago, someone changed the secondary to a snapshot standby instead of a physical standby. That was just discovered and it’s too late to recover all the now missing archive logs (backups are only kept for 30 days). I tried to restore the database from the primary via an RMAN backup/recovery. That appears to have worked. Now the archive logs are being brought over, but not applied. The secondary says it’s a physical standby, but cloud control says it’s still a snapshot standby. When I tried to recover it, I get an ORA-01153. I also noticed that the MRP0 is waiting for log 1, which doesn’t exist. I’ve been searching online and nothing is working. Of course this is a critical production database.  

How do I get this data guard mess straightened out?  

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;  

STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE

• ---------------- ---------------- --------------------

MOUNTED CLAWPRDA PHYSICAL STANDBY MOUNTED   SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;   PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS

• ------------ ---------- ---------- ---------- ----------

ARCH CLOSING 1 140772 411648 298 DGRD ALLOCATED 0 0 0 0 DGRD ALLOCATED 0 0 0 0 ARCH CLOSING 1 140773 411648 1219 ARCH CONNECTED 0 0 0 0 ARCH CONNECTED 0 0 0 0

RFS       IDLE                  1          0          0          0

RFS       RECEIVING             1     140774     258804          2

MRP0      WAIT_FOR_LOG          1          1          0          0

 

9 rows selected.  

SQL> select sequence#, archived, applied from v$archived_log order by sequence#;  

SEQUENCE# ARC APPLIED

• --- ---------

    140768 YES NO     140769 YES NO     140770 YES NO     140771 YES NO     140772 YES NO     140773 YES NO   6 rows selected.  

SQL> recover standby database;

ORA-01153: an incompatible media recovery is active    

Scott Canaan ‘88
Sr Database Administrator
Information & Technology Services
Finance & Administration

Rochester Institute of Technology
o: (585) 475-7886 | f: (585) 475-7520

<mailto:srcdco_at_rit.edu> srcdco_at_rit.edu | c: (585) 339-8659

CONFIDENTIALITY NOTE: The information transmitted, including attachments, is intended only for the person(s) or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and destroy any copies of this information.  

--
http://www.freelists.org/webpage/oracle-l