Oracle Database Discussions

1) SHUTDOWN IMMEDIATE
2) OS copy all Oracle "data" file from source system to destination system in exact same directory location
3) STARTUP -- original DB
4) STARTUP -- "cloned" DB on destination.

on destination system Oracle won't know or care is now is on a different host
Oracle is just a collection of "dumb" files.

Cool. That seems easy enough. Forgive my ignorance but are SHUTDOWN IMMEDIATE and STARTUP commands that i enter somewhere? At the dos prompt or somethign like that? Or can i do that from the web manager?

sqlplus

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Nov 11 10:12:09 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Enter user-name: / as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  402653184 bytes
Fixed Size		    1267764 bytes
Variable Size		  218105804 bytes
Database Buffers	  176160768 bytes
Redo Buffers		    7118848 bytes
Database mounted.
Database opened.
SQL> 

Since you obviously work on Windows you have to do a little bit more: Oracle on Windows requires a service,normally named OracleService<SID>, check this on the machine where you have a running database. The easiest way to get a proper environment is, you install Oracle including a starter database using exactly the same locations as on the source machine. Then you can copy the files from source to target machine again and again.

Werner

When you say copy all the data files what does that entail? I see Datafiles that i named, .dbf files, .ctl files and log files.
Do i need to copy all of those?

You need the datafiles (.dbf),controlfiles (.ctl) and redologs (.log), assuming you use the default file extensions. On both sides the database must be down, you cannot access the mentioned database files when the database is open.

Werner

Yes, but not log files.
Need to include REDO files; too.
Trial & error will prove what is needed/missing.
You could create new/empty (small) instance & practice with it before trying any "production" DB.

So i have to shut down my entire oracle database in order to clone a few of the tablespaces?

You said you wanted to "move it in it's entirety" to create a "carbon copy". The answers above allow you to do that in the easiest way possible.

If you want to keep the database running, then you can take a hot backup instead of a cold backup, but then you would need to recover after you restore.

If you just wanted to move a few tablespaces, then you could use export/import, or transportable tablespaces without shutting down the database.

actually I'd have said the simplest way to create the carbon copy would be to use the RMAN duplication facility.

Niall

Okay. How do i go about doing that?

I'm not trying to be difficult. I am just being thrust into an oracl dba role because my firm does not have a true oracle dba. What I would like is to go to the enterprise manager web console click an option, select 4 tablespaces, click another button and create a backup file. Then go over to the same web manager on my other instance and import that data. And voila! Can it be that simple?

oops should have included the link...

http://download.oracle.com/docs/cd/B28359_01/backup.111/b28270/rcmdupdb.htm#i1008564

sorry. Essentially, take a hot backup, move it to the new server and restore and recover as described previously. It's a built in feature of rman though.

You start by reading the documentation at tahiti.oracle.com

Search for "transportable tablespaces" or "duplicate database" or "expdp"

Thanks for the tip, I think I figured it out - drop Oracle and start working in sql Server.

Yes, OEM has wizards to run exports, backups etc, and is probably the easiest way for you to do these tasks if you are new to Oracle.

That's an approach. Another would be to try to figure out how to express your business need succinctly. You started by describing the need to "carbon copy" a database. Various people told you how to do that - and actually the first example you were given, take the files that make up the db and plug them into a new instance on another machine is a pretty straight match for the attach/detach functionality in sql server. Now, however, you seem to want to move tablespaces only, and seem to imagine that this is in someway equivalent to attach/detach a database. It really isn't.

Does the business goal actually look a bit like

"I have a set of application objects owned by one or more named users and want to move a copy to another server for test purposes"?

Niall

Correct. And i apologize for my earlier post - tough day, very cinical, not necessary.
I should have stated my goal better, you hit it the nail on the head. I don't want a full db replica and i tried to move these tablespaces in the past so there are remnants at the target.
The web manager has a transport tablespace wizard but i can't seem to satify its desire for credentials. I'm not sure what it wants but i tried my win creds, my sysdba creds, my db user creds - all those with domains tacked on the front of the user name, and all those with "@orcl" tacked on the end of the password.
What credentials are these exactly?
And again, my apologies - know that oracle has redeeming qualities, its just hard sometimes to fight through to get to them.

I suspect you've hit the issue I describe at http://www.orawin.info/services/node/71

Incidentally there is also a pair of tools called expdp and impdp that are designed for bulk movement of, amongst other things, users and all the objects that belong to them. (transportable tablespaces uses these tools behind the web interface).

Niall