Re: Snapshot Thin Clone of Physical Standby

From: Don Seiler <don_at_seiler.us>
Date: Mon, 5 Oct 2015 19:09:19 -0500
Message-ID: <CAHJZqBC4a4FuxTDqvh7m=QWyLof1h8Ma+ju1xUA-=sWwQxCA6Q_at_mail.gmail.com>

I wouldn't want to try this when the original database and clone database are on the same host. Issuing that drop statement on the cloned might touch the standby log in use by the original standby. On Oct 5, 2015 7:03 PM, "Kenny Payton" <k3nnyp_at_gmail.com> wrote:

>
> Have you tried dropping the standby redo logs. The ones that throw the
> error you should be able to clear and then drop.
>
> We do this fairly frequently with snaps of our physical standbys on Netapp.
>
> Something like this would generate the statements although the clear is
> only really necessary for the log groups that are causing you a problem.
>
> select distinct 'alter database clear logfile group '||group#||';'||chr(10)||'alter database drop standby logfile group '||group#||';' from v$logfile where type = 'STANDBY'
>
>
> You should be able to do this on the snapshot of the standby after your
> initial mount. Then you can go on to activating it.
>
> Here are some partial notes we use.
>
> startup mount
> clear/drop the standby redologs
> alter database activate standby database;
> alter database set standby database to maximize performance;
> alter system set job_queue_processes = 0;
> alter system set log_archive_dest_state_2=defer;
> alter system set log_archive_dest_state_3=defer;
> alter system set log_archive_dest_state_4=defer;
> alter database open;
>
>
>
>
>
>
>
>
>
> On Oct 5, 2015, at 5:56 PM, Mark W. Farnham <mwf_at_rsiz.com> wrote:
>
> Late to the thread. Is the exact startup rename command you’re using
> appear in the thread somewhere?
> When using less than crash recovery (ie. complete recovery) suitable
> files, does your command include the phrase “using backup controlfile?”
>
> *From:* oracle-l-bounce_at_freelists.org [
> mailto:oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org>] *On
> Behalf Of *Don Seiler
> *Sent:* Monday, October 05, 2015 5:20 PM
> *To:* Jeremy Schneider
> *Cc:* ORACLE-L
> *Subject:* Re: Snapshot Thin Clone of Physical Standby
>
> So in the case of the source DB being a primary (or standalone) DB, we can
> just mount the cloned controlfile, rename the datafiles/logs (if needed)
> and then open it and let it do crash recovery.
>
> If the source DB is a physical standby, we can create a new controlfile
> and open resetlogs.
>
> Either way we then do a DBNEWID so we'd be doing a resetlogs anyway. I'm
> curious why the CREATE CONTROLFILE option has problems when the source is a
> primary, but at least for now we have something that works.
>
> Thanks again everyone.
>
> Don.
>
> On Mon, Oct 5, 2015 at 1:25 PM, Don Seiler <don_at_seiler.us> wrote:
> Ironically now, when I take the method we have for creating a new
> controlfile and apply it to cloning from a primary database, it tells me
> that datafile 1 needs more recovery when I try to open resetlogs. I even
> tried putting the primary into backup mode first just to see if that was
> it, but I got the same error. All the online redo logs were in the same
> snapshot as the datafiles (same protection group), but it doesn't seem to
> want to use them. No errors/complaints from the CREATE CONTROLFILE
> statement.
>
> The one thing that I'm curious about is that we ARE changing the DB name
> in the CREATE CONTROLFILE statement. Would that impact our ability to use
> the online redo logs that were snapshotted?
>
> Don.
>
> On Mon, Oct 5, 2015 at 11:54 AM, Jeremy Schneider <
> jeremy.schneider_at_ardentperf.com> wrote:
> On Fri, Oct 2, 2015 at 3:32 PM, Don Seiler <don_at_seiler.us> wrote:
> > Jeremy, I'm curious why a backup primary controlfile would work when
> > recreating a controlfile would not. We've tried the latter and that's
> when
> > it said we had incomplete media recovery. Why would the backup primary
> > controlfile not also have the same issue?
>
> On Mon, Oct 5, 2015 at 10:56 AM, Don Seiler <don_at_seiler.us> wrote:
> > Seth, that did appear to work now when I create a new controlfile. I'll
> see
> > how best to script that part of it.
>
> Glad to hear it started working! I couldn't think if any reason that a
> create controlfile shouldn't work. Just was pointing out that the
> primary controlfile backup works routinely for me to avoid the issue
> with standby controlfile backups.
>
> -J
>
> --
> http://about.me/jeremy_schneider
>
>
>
> >
> > On Fri, Oct 2, 2015 at 2:26 PM, Don Seiler <don_at_seiler.us> wrote:
> >>
> >> To answer your question, no we didn't try the copy-controlfile-from-prod
> >> method. I'll look into that.
> >>
> >> Don.
> >>
> >> On Fri, Oct 2, 2015 at 1:56 PM, Don Seiler <don_at_seiler.us> wrote:
> >>>
> >>> In 11.2 I used to be able to mount the clone, and if the controlfile
> was
> >>> a standby I would just backup the controlfile and restore it from that
> >>> backup and (voila) it would now be a primary. That doesn't seem to be
> the
> >>> case in 12.1 now. Not sure if that alone is at the root of the problem.
> >>>
> >>> I was hoping to avoid having to connect to any source databases for
> this,
> >>> but maybe there's no away around it. It would have been cleaner to
> just be
> >>> able to snapshot from the storage array and do all the work from the
> cloned
> >>> instance.
> >>>
> >>> Don.
> >>>
> >>> On Fri, Oct 2, 2015 at 12:43 PM, Jeremy Schneider
> >>> <jeremy.schneider_at_ardentperf.com> wrote:
> >>>>
> >>>> On Fri, Oct 2, 2015 at 12:36 PM, Don Seiler <don_at_seiler.us> wrote:
> >>>> >
> >>>> > ORA-01511: error in renaming log/data files
> >>>> > ORA-00261: log 52 of thread 1 is being archived or modified
> >>>>
> >>>> well it's nice to hear that this problem still exists in 12c - i'm
> >>>> very aware of it in 11.2.0.3
> >>>>
> >>>> it's because the software isn't really able to handle the standby log
> >>>> which is currently active. i have run into the exact same issue doing
> >>>> a "recover datafile" on a clone which was created using a backup
> >>>> controlfile from the standby. in my case, i simply used the backup
> >>>> from the primary (and changed nothing else) and i don't have the
> >>>> issue.
> >>>>
> >>>> >
> >>>> > So we go to plan B, which is to try to create a new controlfile. The
> >>>> > new
> >>>> > CREATE CONTROLFILE script doesn't specify the standby redo logs.
> >>>> >
> >>>> > ERROR at line 1:
> >>>> > ORA-01196: file 1 is inconsistent due to a failed media recovery
> >>>> > session
> >>>> > ORA-01110: data file 1: '/file/name/here.dbf'
> >>>> >
> >>>> > Every variation of this that we've tried fails similarly.
> >>>>
> >>>> So you did also try taking a backup of the primary controlfile and
> >>>> restoring this into the standby, then renaming all your logfiles and
> >>>> datafiles to match the new locations? That has worked for me on 11203
> >>>> for avoiding the error you mentioned above.
> >>>>
> >>>> -J
> >>>>
> >>>> --
> >>>> http://about.me/jeremy_schneider
> >>>
> >>>
> >>>
> >>>
> >>> --
> >>> Don Seiler
> >>> http://www.seiler.us
> >>
> >>
> >>
> >>
> >> --
> >> Don Seiler
> >> http://www.seiler.us
> >
> >
> >
> >
> > --
> > Don Seiler
> > http://www.seiler.us
>
>
>
> --
> Don Seiler
> http://www.seiler.us
>
>
>
> --
> Don Seiler
> http://www.seiler.us
>
>
>

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

Received on Tue Oct 06 2015 - 02:09:19 CEST