Using Standby for cloning

OracleDoc gave me a great idea (in that 'why RAC' thread) that I had not thought about. Taking my standby database and using that for creating a clone!

What am I missing here? I shutdown the standby and scp all the files (online redo, control included) from standby box to qa box - essentially a cold backup copy, rename the instance and opening. Perhap use 9i feature to rename the database (or am i confusing the DBID?). And I'm done.

The standby is 563.15 KMs from production and 'on the wire' with qa and dev.

Why not just use the duplicate command?

Quote:

---

Originally posted by gopi
What am I missing here? I shutdown the standby and scp all the files (online redo, control included) from standby box to qa box - essentially a cold backup copy, rename the instance and opening. Perhap use 9i feature to rename the database (or am i confusing the DBID?). And I'm done.

---

You would actualy need a controlfile from your primary, not from your standby. Standby's controlfile can not be used for cloning unless you actually want to add another standby database to your DataGuard configuration (ie one primary and several standbys).

This is also a normal method to use standby for daily backups to offload the primary - you back up databfiles on the standby site and controlfile on the primary - that makes a valid and complete backup set.

Jurij,
Due to wide area network constraints I am not using Dataguard features such as log_transport/apply. I am simply have a 'mounted standby' and i manually (with a script) transport and apply each log. So this is not failover (if it matters).

My follow up questions are:
1. If i use the standby control file, why can't I simply
ALTER DATABASE OPEN RESETLOGS;
manual open of a database mounted in standby ?

2. In obtaining and using the production control files, do i just follow the usual clone process, such as:
a) ALTER DATABASE BACKUP CONTROLFILE TO TRACE RESETLOGS;
b) edit it:
CREATE CONTROLFILE REUSE set DATABASE "QA" RESETLOGS ARCHIVELOG
- change directory names where necessary
c) STARTUP NOMOUNT
d) use the script in b) to rebuild the control file
e) ALTER DATABASE OPEN RESETLOGS

Julian...I need to purchase the Veritas MML Oracle extension in order to use rman to backup to tape (not enought disk) - my management has been warned many times. Once I have rman backups I can duplicate. thanx

1. It's a standby controlfile. You can't issue "alter database open resetlogs" with a standby controlfile. Read documentation on how to activate a standby.
2. That's fine. You're just creating a new controlfile. That'll do the job.

Quote:

---

Originally posted by gopi
My follow up questions are:
1. If i use the standby control file, why can't I simply
ALTER DATABASE OPEN RESETLOGS;
manual open of a database mounted in standby ?

---

Because the standby controlfile differs from the one that is used in your primary. It has some flags set that indicate that this is not the normal controlfile but the one used with the standby database only. You can easily find some of the diferences between your primary and standby controlfiles by isuing some queries on V$views that obtain their information directly from controlfiles. For example, check the values of CONTROLFILE_TYPE and DATABASE_ROLE in V$DATABASE fromboth of your instances and you'll se the difference.

When you open the database, the instance checks the contents of the controlfile to see if it can actualy open the database. That's why you can't mount the standby database with the "normal" controlfile, you must create standby controlfile first during standby setup. That's also why you can't open a physical standby database in read-write mode, you can only open it in read-only mode. That's also why you can put standby database in managed recovery mode while you can't do the same with the primary database. In short, the contents of the primary and standby database is not the same.

When you use a standby controlfile you are not able to open it with or without the resetlogs, Oracle simply doesn't allow you to open the database except in read-only mode.

Quote:

---

Originally posted by gopi
2. In obtaining and using the production control files, do i just follow the usual clone process, such as:
a) ALTER DATABASE BACKUP CONTROLFILE TO TRACE RESETLOGS;
b) edit it:
CREATE CONTROLFILE REUSE set DATABASE "QA" RESETLOGS ARCHIVELOG
- change directory names where necessary
c) STARTUP NOMOUNT
d) use the script in b) to rebuild the control file
e) ALTER DATABASE OPEN RESETLOGS

---

No. You would not backup the primary controlfile to trace at all. You would:
a) ALTER DATABASE BACKUP CONTROLFILE (create a binary backup of your controlfile on your primary)
b) STARTUP MOUNT the cloned database with that backup copy of the primary database and the datafiles from standby datase
c) RECOVER DATABASE USING BACKUP CONTROLFILE; (applying the needed archlogs backed up on your standby)
d) ALTER DATABASE OPEN RESETLOGS

If his/her standby is in-sync, step 2 is fine.

Thank you very much for your detailed explanation!

I've been thinking about all you said and Axr2's statement. Correct me if I'm wrong Jurij, but my way would work because this would have been the only way prior to 9.2 (to hand edit the control file and rebuild). With 9.2 the binary CF now makes sense and I will use the DBNEWID utility to change the dB name and ID.

Axr2,
I would love for Axr2 to define 'in sync'. In sync with what? Perform a checkpoint so that the CF and data files are the same? Or, that the last archive log from prod be applied?

- Frank

I don't know what your requirement is. As long as your standby has caught-up with the primary 'sufficiently' is what I meant by 'in-sync'. Be that a SCN, the last archivelog, or a point in time.

I have some scripts that exactly do that on a daily basis. (Since I can't be bothered refreshing a database manually everyday, i prefer to script). I refresh a Test database from Production every morning. I have a standby on production. What I essentially do is exactly what you mentioned - copy standby files. Check last logs applied. Issue a 'alter datbasse bakup ctl file' production. Massage the tracefile (ctlfile) with awk/sed. Move it over, run/issue 'create ctlfile on TEST' and issue a alter database open resetlogs. It works just fine. This particular case is on a 7.3.4. No matter what version, this will work.

PS : I need to awk/sed and move controlfiles everyday..since Prod is volatile. Lots of Datafiles are constantly added, moved around etc.

As for Jurij's method, it'll work - Depends if you don't mind retaining the same dbname.

Quote:

---

Originally posted by Axr2
As for Jurij's method, it'll work - Depends if you don't mind retaining the same dbname.

---

in 9.2 the DBNEWID utility can be used to change both.

Thanks.

Yup. Pick your poison. Do whatever is appropriate/easier in your enviroment and whichever makes sense.

Quote:

---

Originally posted by gopi
in 9.2 the DBNEWID utility can be used to change both.

Thanks.

---

The utility is called NID, not DBNEWID.

Quote:

---

Originally posted by gopi

Julian...I need to purchase the Veritas MML Oracle extension in order to use rman to backup to tape (not enought disk) - my management has been warned many times. Once I have rman backups I can duplicate. thanx

---

You can use disk backups for cloning. No need for MML in order to run the duplicate command :-)

Quote:

---

Originally posted by Axr2
As for Jurij's method, it'll work - Depends if you don't mind retaining the same dbname.

---

With NID, he will change it within 5 minutes, what's the biggy?

Quote:

---

Originally posted by julian
The utility is called NID, not DBNEWID.

---

No, the utility is caled "DBNEWID utility". The executable is called "nid".

It's like you would say: No, the utility is not aclled SQL*Loader, it's caled sqlldr.

Quote:

---

Originally posted by jmodic
No, the utility is caled "DBNEWID utility". The executable is called "nid".

It's like you would say: No, the utility is not aclled SQL*Loader, it's caled sqlldr.

---

No, it is called NID or DBNEWID.

Go to note 224266.1:

"The NID (New Database ID)is a new utility introduced with Oracle 9.2. The NID
utility allows you to change only the DBNAME, or only the DBID or both
DBNAME and DBID in the same command. "

And you don't say sqlldr, because it is hard to pronounce. Unlike NID.

Oh, give us a break! It's officialy called NEWDBID. A short glimps on the manuals tells it all. http://download-west.oracle.com/docs.../ch14.htm#1656

*tap tap is this thing on?

you could do what we did at my last gig.
simply take a hot backup of the standby. ftp the datafiles over to your dev. Rename the database, startup using an edited controlfile from prod. Rinse and repeat as neccessary.
We did this on a nightly basis, we had a Reporting database that users could run thier reports off of instead of having to use prod.
It's particularly sweet if you're in an unsecure environment and have rsh available to use. If not use the ssh, A couple of scripts and voila` you've got yourself a nightly refresh job.

Quote:

---

Originally posted by OracleDoc
*tap tap is this thing on?

you could do what we did at my last gig.
simply take a hot backup of the standby. ftp the datafiles over to your dev. Rename the database, startup using an edited controlfile from prod. Rinse and repeat as neccessary.
We did this on a nightly basis, we had a Reporting database that users could run thier reports off of instead of having to use prod.
It's particularly sweet if you're in an unsecure environment and have rsh available to use. If not use the ssh, A couple of scripts and voila` you've got yourself a nightly refresh job.

---

\
can i put the standby database in backupmode in all these versions

1)7.3
2)8.1.7
3)9.2

if not from which version onwards can i put the standby database in backup mode ?

regards
Hrishy

You do not put *database* into backup mode, you put *tablespaces* or *datafiles* in backup mode! And you can do this in all versions at least since Oracle7.

But as far as stanby database and the backup mode is concerned, the question is irrelevant. If you put your standby database out of recovery mode, then the database is either:
- closed, nomounted or mounted
- opened read-only

In any of those states, the database is as good as "in backup mode", and you can safely perform a backup the same way as you would perform cold backup. No need (nor ability) to use BEGIN BACKUP and END BACKUP options with standby.

P.S.
Of course, we are talking strictly about physical standby here. With logical standby it's another story.

Hi Jurij

Thanks for the reply..So u mean to say

1)i would shutdown the standby database (take it out of recovery mode)

2)take a cold backup of standby database

3)copy the archived logs generated during this time from the primary server and i have a valid Backup from which i cna recover

is my understanding right

regards
Hrishy

Yes, except you don't need to actualy perform a shutdown of the stanbdby instance (it won't hurt if you do, though). As soon as you take it out of the recovery mode, all the datafiles are allready "cold", ie consistent, so you can back them up immediately.

And yes, if you have the archlogs from the primary (plus the backup of the controlfile of the primary) you can recover your primary database from this backup exactly the same as you would do if the backup was taken directly on the primary.

Woahh woahh guys hold your horses.

If you have a live standby database you cannot shut it down to take a backup then bring it back up and expect everything to be all honkey dorie. If you were to do so, any changes that prod shipped over to you via redo or archive would not be able to be applied. The easiest way is to simply take a hot backup of the standby. That way anything that's shipped over can be applied while'st the hot backup is taking place.

If any of you are seriously considering using a hot standby for cloning purposes let me know and I'll contact my old client for the scripts.


shutup Jeff yes I know I should of kept them

Quote:

---

Originally posted by OracleDoc

If any of you are seriously considering using a hot standby for cloning purposes let me know and I'll contact my old client for the scripts.

---

I beleive I have chosen my method but i would indeed want to see the scripts. [email protected] Please put a clear subject line (ie cloning standby database) in the email and ignore the spam filtor message "you have been banished to the suspect folder"

Quote:

---

Originally posted by OracleDoc
Woahh woahh guys hold your horses.

If you have a live standby database you cannot shut it down to take a backup then bring it back up and expect everything to be all honkey dorie. If you were to do so, any changes that prod shipped over to you via redo or archive would not be able to be applied. The easiest way is to simply take a hot backup of the standby. That way anything that's shipped over can be applied while'st the hot backup is taking place.

If any of you are seriously considering using a hot standby for cloning purposes let me know and I'll contact my old client for the scripts.


shutup Jeff yes I know I should of kept them

---

Hey oracle Doc

Can you briefly outline the steps you too to offline the database backup to the standby server.

regards
Hrishy

LOL I knew you guys were going to want them. Ok, gimmie about a week to get them for you. I'm heading out to Rome for the weekend and won't be back till Wednesday.
Better yet.. shoot me an email next week to remind me.

Quote:

---

Originally posted by OracleDoc
Woahh woahh guys hold your horses.

If you have a live standby database you cannot shut it down to take a backup then bring it back up and expect everything to be all honkey dorie. If you were to do so, any changes that prod shipped over to you via redo or archive would not be able to be applied.

---

So what? All changes will be applied once the standby is up again and everything will be just honkey dorie, no? Nothing lost, no harm done.

You know, I'm feeling like an idiot now. Cause I forgot about this.

Just got done talking to my buddy at the old client site and he's like "if you're running 9i and you want to have a database that continually updated by prod just make a lodgical standby and let your users play on that".

He's right you have two standbys. A physical one ready to go incase prod $hits and a lodgical one for test/dev.

Quote:

---

Originally posted by jmodic
So what? All changes will be applied once the standby is up again and everything will be just honkey dorie, no? Nothing lost, no harm done.

---

yes, once the changes are applied. My thinking is just working smarter and not harder. It's just one less step you have to take and also not having your standby actually down.

Quote:

---

Originally posted by OracleDoc
I'm heading out to Rome for the weekend and won't be back till Wednesday.

---

if u have a date, go to Tivoli - about 45-60 minute drive from Rome ;)

Quote:

---

Originally posted by OracleDoc
yes, once the changes are applied. My thinking is just working smarter and not harder. It's just one less step you have to take and also not having your standby actually down.

---

I agree. I was thinking exactly along with your thoughts. That's why I said: "Yes, except you don't need to actualy perform a shutdown of the stanbdby instance". But it won't be a mortal sin if he (hrishy) does and if that makes him sleep better - that's why I added "....it won't hurt if you do, though".

Bottom line:
There is absolutely nothing wrong if you don't constantly apply changes to the standby. After all, that's why they provided a readonly open mode of the standby for us! The important thing is that the standby site can constantly recieve the chaanges from the primary, even if they are not applied immediately. And even if you temporary disable the transport of changes to the standby, that shouldn't be much of the problem - that's why FAL process is there for in DataGuard.

Quote:

---

Originally posted by jmodic
Bottom line:
There is absolutely nothing wrong if you don't constantly apply changes to the standby.....The important thing is that the standby site can constantly recieve the chaanges from the primary, even if they are not applied immediately.

---

I realized this recently. My "zip,xfer,unzip,apply" agent will simply not do the apply durng the process of cloning the standby.

I am here to thank you!
Second method still works in 2025 on oracle 12.1.0.2