Copying a Database 1

[jaxtell]

How complicated is it to copy a database? We take down our database every night, copy all the files, and bring it back up. Other than copying the files to a different server, what else needs to be done? I'd want the copy to come up under a different name (UAT instead of PRD), and I have some scripts I need to run to prevent it from doing some production tasks, but I think thats it. We use a PFILE, not a SPFILE, if that makes any difference. Any information would be appreciated.

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/

 

[Thargy]

jaxtell,

quite a bit actually.

Copying the files is the poorest possible way to make a backup. You can only restore to precisely the location the files came from, and you have to shut down to do it.

Those files, if copied to a different server won't work in a database, as their locations are contained in a control file. Since you will have no matching control file, you're in trouble.

A good "quick fix" is to do a full db export immediately! That can be imported into a different db on a different server (subject to certain restrictions).

However, the only correct solution is to use recovery manager, usually referred to as just RMAN. This is Oracle's built-in tool for doing back up and recovery. As a matter of urgency, I suggest you read about RMAN, and archive logs, redo logs and control files. When you've got the basic concepts in your head, ask some more. This is a very large topic, and too much to deal with in one post.

Regards

T

 

[SantaMufasa]

Jaxtell,

Frankly, you are in a very good position to bring up your copy of the database. All you should need to do is simply adjust the pfile of the new database to reflect the locations of your copied database files.

I'd want the copy to come up under a different name (UAT instead of PRD)

Keep in mind that the "coming up" name is a function (generally) of the database instance and not the database itself.


The method by which one names the instance is largely dependent upon the operating system. Could you please confirm which o/s you are using in this regard?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[SantaMufasa]

...And to reconcile what Tharg said about the control files pointing to the database datafiles, there are two methods to achieve that reconciliation:[ul][li]Ensure that the copied files reside in precisely the same directory/path structure on the target server as they were on the source server, or[/li][li]Modify the location of your database files by doing the following on the new database:[/LI]

STARTUP MOUNT
ALTER DATABASE RENAME FILE
   '<old filename and location 1>'
  ,'<old filename and location 2>'
  ...
  ,'<old filename and location n>'
TO
   '<new filename and location 1>'
  ,'<new filename and location 2>'
  ...
  ,'<new filename and location n>'
;
ALTER DATABASE OPEN;

[/ul]

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[jaxtell]

Tharg,
This isn't an emergency situation. Right now we have a DEV, TST, and PRD instance. I want to create a 4th instance, but be able to programatically copy PRD to the new one on a regular basis.

Santa,

cat /proc/version
Linux version 2.6.9-34.ELsmp (buildcentos@nasha.karan.org) (gcc version 3.4.5 20051201 (Red Hat 3.4.5-2)) #1 SMP Thu Mar 9 06:23:23 GMT 2006

PRD is a RAC setup, UAT won't be. Does that make any difference?

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/

 

[SantaMufasa]

For Linux, include for any Oracle-user profile appropriate definitions for $ORACLE_HOME and $ORACLE_SID and in the /etc/oratab file, the proper values that you entered for the $ORACLE_HOME and $ORACLE_SID system variables. If you have your pfile in the default ($ORACLE_HOME/dbs) location, and therein contains your pfile that points to the proper location of your Control File(s), you are ready to:

sqlplus /nolog
conn / as sysdba
startup (provided your database files are in the same paths as before, else run the "startup mount" sequence above.)

You should be good to go. Please advise us of your findings/results.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[KenCunningham]

Unless I misunderstood (quite possibly!), I was surprised at Tharg's comment that copying the files is a poor way of making a backup. I agree that copying active files is a recipe for disaster, but we successfully backup and clone databases by copying the necessary files (when the database is closed) on a regular basis.

It is my understanding that export/import is not a reliable backup tool and was never designed as such. Of course, for hot backups where the database remains open (and probably now for cold ones too), I agree that RMAN is the way to go.

I want to be good, is that not enough?

 

[Thargy]

Ken,

I was mostly alluding to the fact that you have to shut down to do the backup. However, I stand by my remark for the following reason:-

This approach involves copying each file, and when there are only a handful, it's not a problem. Would you like to attempt this for a data warehouse with hundreds? If there is a significant amount of data, the copying becomes onerous and error prone.

Manual intervention in any business process is anathema to me. I script all my procedures, especially backups. I don't ever want to be in a production situation, where I am reliant on correct manual activity to get me out of trouble and restore a prod db. I want to either use OEM, grid control or an RMAN script to recover, nothing else.

In jaxtells situation, where it's just for dev and test, this is probably just me being a bit officious, but on average, I still reckon either known tested RMAN scripts, or oracles provided GUI's are really the only way to fly.

Regards

T