Finding oracle physical database files

Question

I'm using oracle 11g and I need to find the database files to copied to another server. The database was created from migrating from SQL Server 2008 to 11g. There doesn't seem to be a counterpart for sql server's mdf/ldf files.

I read that there are dbf's/ctl's instead but I'm not sure which one is what I need. Also there aren't any files named after the database user that owns the database (after migration, it's the user that has the database) which leaves me puzzled.

I need to know which files are needed so I can copy the oracle database to another remote oracle server easily.

• Hennes: These links might be helpful to you: http://www.sql-server-performance.com/2003/migrating-from-oracle-to-sql-server/ and http://stackoverflow.com/questions/3482067/how-to-migrate-database-from-oracle-to-sql-server
• Bahamut: @Hennes I only need a guide to get the physical files and I'm not migrating them back to sql server. Sorry if my question is confusing. I need oracle database files to be copied into another oracle server.
• Philᵀᴹ: select * from dba_data_files, then you need the control files and the init.ora/spfile. You really need a DBA to do this for you though ....

Answer 1

An Oracle database uses two basic kinds of disk files - .dbf (data) and .ctl (control) files. You can also have .idx files (for indices). In most environments I've been in, these are located in /u01, /u02, /u03, and on, for however many /u0X's your DBAs had created.

On the other hand, many Oracle installations don't use filesystem files, they use raw disk and ASM storage. If this is the case, you won't ever find any .mdf file analogues. If you look at a process listing when the database is up and you see oracle processes with the string "ASM" in the process name, stop looking for disk files because they're not there.

• Luis Santos: There is no *.idx file for indexes on Oracle. Oracle has not distinction from datafiles containing data blocks or indexes blocks. In fact Oracle has no endorsement for the usually called extension on datafile name. There are only conventions. This can be manual performed by the DBA (and it´s usually done), but using name (or extension) convention.

Answer 2

An Oracle database instance is made basically of three kinds of files: database files (aka datafiles), controlfile, and redo log files.

You can check their names and paths by issuing these simple queries, logged with a DBA privileged account:

select name from v$datafile;
select name from v$controlfile;
select member from v$logfile;

Your question is a very newbie one. No problem with it, of course, but you should take a read on Oracle Database Concepts manual.

https://docs.oracle.com/database/121/CNCPT/toc.htm

• Gaius: This is the correct answer