Re: Data Guard Issue

 I agree with Mark- leaving old files, especially archive logs, can cause havoc if a snapshot standby occurred. Drop all files and redo the restore.

On Wed, Aug 28, 2024 at 4:41 PM Mark Burgess <mark_at_burgess-consulting.com.au> wrote:

> With it being opened as a snapshot standby you should have had the restore
> point in place still that was created before the snapshot standby was
> opened (unless the original standby controlfile is gone). Identify the
> restore point that would have been taken before the snapshot standby was
> opened, flashback to it, open the standby as physical and it **should**
> sort itself out.
>
>
>
> If the arch logs are not available on the primary you will most likely
> have to resync from primary. Very easy to do this in 19c with recover
> standby db from service (which has been mentioned earlier but check the
> notes in MOS as they are version specific for this command).
>
>
>
> If you are having to go down the path of recreating the standby make sure
> to remove any old files in the FRA (particularly online/standby logs, arch
> logs, control files etc) as they can throw a spanner in the works if they
> have been used by a future incarnation when doing recovery. This is what
> could be causing your incarnation message if RMAN is detecting a CF
> autobackup from the previous incarnation and restoring that automatically
> before it commences the restore/recovery.
>
>
>
> If the database is big – might be worth seeing if options above are valid.
> If its small prob easier to trash it and recreate using restore from
> service command.
>
>
>
>
>
> *From: *oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on
> behalf of Scott Canaan <dmarc-noreply_at_freelists.org>
> *Date: *Thursday, 29 August 2024 at 5:02 AM
> *To: *Andrew Kerber <andrew.kerber_at_gmail.com>
> *Cc: *Oracle-L Freelists <oracle-l_at_freelists.org>
> *Subject: *RE: Data Guard Issue
>
> This looked like it was going to work, until the very end when it got an
> error:
>
>
>
> starting media recovery
>
> media recovery failed
>
> RMAN-00571: ===========================================================
>
> RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
>
> RMAN-00571: ===========================================================
>
> RMAN-03002: failure of recover command at 08/28/2024 14:54:27
>
> RMAN-03015: error occurred in stored script Memory Script
>
> ORA-00283: recovery session canceled due to errors
>
> RMAN-11003: failure during parse/execution of SQL statement: alter
> database recover
>
> if needed standby start
>
> ORA-00283: recovery session canceled due to errors
>
> ORA-19909: datafile 1 belongs to an orphan incarnation
>
> ORA-01110: data file 1: '/oracle/data/CLAWPRDA/sys01.dbf'
>
>
>
>
>
> *Scott Canaan ‘88*
>
> *Sr Database Administrator *Information & Technology Services
> Finance & Administration
>
>
> *Rochester Institute of Technology *o: (585) 475-7886 | f: (585) 475-7520
>
> 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:* Andrew Kerber <andrew.kerber_at_gmail.com>
> *Sent:* Wednesday, August 28, 2024 1:16 PM
> *To:* Scott Canaan <srcdco_at_rit.edu>
> *Cc:* Oracle-L Freelists <oracle-l_at_freelists.org>
> *Subject:* Re: Data Guard Issue
>
>
>
> try this: on the standby side:
>
>
>
> dgmgrl> edit database CLAWPRDA set state='APPLY-OFF';
>
>
>
> exit broker
>
> on standby server:
>
>
>
> rman target sys/password; -- for standby
>
> rman> recover standby database from service <database link for primary>;
>
>
>
>
>
>
>
> On Wed, Aug 28, 2024 at 9:20 AM Scott Canaan <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
>
> 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.
>
>
>
>
>
> --
>
> Andrew W. Kerber
>
> 'If at first you dont succeed, dont take up skydiving.'
>

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