Refreshing Development database

I need to refresh our development database (8.1.7.4) with production data. The database is about 500 Gb in size. So I don't want to do export, import, etc. RMAN is not yet in use. So no refresh from RMAN backup.

I have a standby of the prodcution database on the same server as the development database.

Here is what I am planning to do.

1. Shutdown the standby database.
2. Delete the datafiles on the development database.
3. Copy the .dbf files from the standby database to development database.
4. Copy the $APPL_TOP from standby to development.

There has got to be more than this. Am I missing anything here? Thanks.

I don't know how to do this for sure...BUT
my only suggestion is to zip and save the old datafiles rather than delete them. This way if things go boom, you can go back to where you were.
Backups R gud!!

MH

How does your prod database get backed up ?
If you do hot backups on it, why cann't you use that for restoring to dev environment and recover ?

In relation to the question I have another question. If the standby database and development servers are in different UNIX boxes will the following solution work?

1. Shutdown the standby database.
2. Shutdown the Development database.
3. Copy the control files, datafiles, redolog files, parameter files from the standby database to the development database, with the same directory structure.

Open the development database pointing to the parameter file of the new copied parameter file.

Will this replicate the development database with that of my standby database? I think it should exactly replicate right?

Would appreciate if any one can clear this doubt. With the theoretical knowledge of Architecture and Administration of Oracle database I got to this solution.

What you meantioned is exactly what we did at my last client.

Shut down the standby copy all realtive files to where ever. Then crank up the copy using the controlfile from trace. Of course you have to make sure you reset all your port#s, app server locations, bla bla. but it will work.

I have trouble right at the beginning. I had shutdown the standby. Then I started deleting the data files from the development database. By mistake I deleted several redo and temp datafiles from the STANDBY!!

Can I restore the data files from the cold backup of the standby?

Repeat after me...

Backups R gud..
Backups R my friend..
Measure twice, cut once.
An ounce of prevention.....you get the idea.


I would think you would be able to restore temp and redo, not sure, anyone?

MH

Backups are my best friend. I love them.

Anyways, I asked my SA to restore the datafiles. I will bring up the standby and see if it is in a consistent state. I think it will be. If not, you can help me find a new job considereing I am a NJ resident too. Thanks.:)

I talked to my senior and he said if there were no active transactions you should be ok. The temp should be fine but the redo is in question.

I already filled the position of database destroyer in my company, sorry. If I wasn't so good I'd tell you to send me your resume.

MH

;)

The standby was shutdown. All it does is accept archivelogs shipped over from production every 15 minutes and those logs are applied. May be redo will come in there?! Would applying some older archivelogs (those that came over from production since the standby came up this weekend after cold backup) take care of that? Once restore is complete, I am planning to mount the standby and try applying a couple of logs to see if it gives me any errors. I sure hope not.

Since it is a standby database only, you need not worry as long as you primary is intact. If nothing works you can always rebuild the standby from primary.

I coped over all the datafiles to the Development database. Created a control file script and when I executed the script to create the control file, it fails.

In the alert.log, all I see is the following:

ORA-1503 signalled during: CREATE CONTROLFILE set DATABASE "DEV" RESETLO..

There is no other accompanying message.

Has anyone seen this? I am stumped as to what to do next.

It's been a while since I've done this kind of thing, what with the ability to clone using RMAN these days, but you might want to check the following in the create controlfile script if your cloning from a cold backup:

1)'CREATE CONTROLFILE REUSE DATABASE "prod" RESETLOGS' becomes 'CREATE CONTROLFILE REUSE set DATABASE "dev" RESETLOGS'

2) All paths in the controlfile are correct for the copied database e.g. /u02/oradata/prod/system01.dbf is where the copied file resides and has that name. If the dev box has the same filesystems as the standby box then this should not be a problem

3)The CREATE CONTROLFILE command will be preceded by a series of comments and a STARTUP NOMOUNT command. These need to be stripped out of the file. In addition, after the create controlfile command, there will be a number of comments and the commands RECOVER DATABASE and ALTER DATABASE OPEN, which should also be stripped out, leaving just the create controlfile command itself.

4) The init.ora for the copied database needs to be correct. Check the db_name is set to DEV or whatever. Check controlfiles parameter etc.

When you're happy then you can startup nomount the database using the pfile parameter to ensure your using the init.ora file you made the changes to e.g.

startup nomount pfile=/u01/app/oracle/admin/dev/initdev.ora

Now run your amended create controlfile script and then

'alter database open resetlogs'

Hope this helps, and good luck.

Austin