How to create a database from given data files ??

[parbhani]

Hi all,

I am not a very expert Oracle DBA.
I have all the data files from a Oracle database which we have stopped using a long ago and does not exist.
Also I do not have the control files, just all the data files.

If I have to create the database just from the data files, what steps I need to take. The version is not a problem. When abendoned it was on 9.2 (just migrated) and I have to create a database with those files on 9.2 .

Please guide.

Regards

 

[Scunningham99]

assuming you still have your product top (database software) you can issue the create controlfile statement:-

STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "DEMO" RESETLOGS NOARCHIVELOG #must use resetlogs otherwise we receive error
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 1022
MAXINSTANCES 1
MAXLOGHISTORY 10890
LOGFILE
GROUP 1 (
'/user03/oracle/demodata/redo01a.log',
'/user05/oracle/demodata/redo01b.log'
) SIZE 8M,
GROUP 2 (
'/user03/oracle/demodata/redo02a.log',
'/user05/oracle/demodata/redo02b.log'
) SIZE 8M,
GROUP 3 (
'/user03/oracle/demodata/redo03a.log',
'/user05/oracle/demodata/redo03b.log'
) SIZE 8M
DATAFILE
'/user10/oracle/demodata/system01.dbf',
'/user03/oracle/demodata/az01.dbf',
'/user06/oracle/demodata/ax01.dbf',
'/user03/oracle/demodata/ak01.dbf',
'/user03/oracle/demodata/bom01.dbf',
'/user03/oracle/demodata/inv01.dbf',
'/user03/oracle/demodata/ar01.dbf',
'/user03/oracle/demodata/oe01.dbf',
'/user03/oracle/demodata/osm01.dbf',
'/user03/oracle/demodata/mfg01.dbf',
'/user03/oracle/demodata/mrp01.dbf',
'/user03/oracle/demodata/wip01.dbf',
'/user03/oracle/demodata/eng01.dbf',
'/user03/oracle/demodata/crp01.dbf',
'/user03/oracle/demodata/hxt01.dbf',
'/user03/oracle/demodata/cz01.dbf',
'/user03/oracle/demodata/pjm01.dbf',
'/user03/oracle/demodata/flm01.dbf',
'/user03/oracle/demodata/msc01.dbf',
'/user03/oracle/demodata/wh01.dbf',
'/user03/oracle/demodata/ssp01.dbf',
'/user03/oracle/demodata/ota01.dbf',
'/user03/oracle/demodata/rla01.dbf',
'/user03/oracle/demodata/veh01.dbf',
'/user03/oracle/demodata/qa01.dbf',
'/user09/oracle/demodata/icx01.dbf',
'/user03/oracle/demodata/cn01.dbf',
'/user03/oracle/demodata/cs01.dbf',
'/user03/oracle/demodata/ec01.dbf',
'/user05/oracle/demodata/ctxd01.dbf',
'/user05/oracle/demodata/aold01.dbf',
'/user09/oracle/demodata/aolx01.dbf',
'/user05/oracle/demodata/apd01.dbf',
'/user06/oracle/demodata/apx01.dbf',
'/user05/oracle/demodata/cmd01.dbf',
'/user06/oracle/demodata/cmx01.dbf',
'/user03/oracle/demodata/custom01.dbf',
'/user05/oracle/demodata/fad01.dbf',
'/user06/oracle/demodata/fax01.dbf',
'/user07/oracle/demodata/gld01.dbf',
'/user02/oracle/demodata/gld02.dbf',
'/user09/oracle/demodata/glx01.dbf',
'/user10/oracle/demodata/abmd01.dbf',
'/user11/oracle/demodata/hrd01.dbf',
'/user09/oracle/demodata/hrx01.dbf',
'/user05/oracle/demodata/jed01.dbf',
'/user09/oracle/demodata/jex01.dbf',
'/user05/oracle/demodata/pad01.dbf',
'/user09/oracle/demodata/pax01.dbf',
'/user11/oracle/demodata/pod01.dbf',
'/user09/oracle/demodata/pox01.dbf',
'/user09/oracle/demodata/rbs01.dbf',
'/user05/oracle/demodata/rgd01.dbf',
'/user09/oracle/demodata/rgx01.dbf',
'/user09/oracle/demodata/shx01.dbf',
'/user03/oracle/demodata/tools01.dbf',
'/user03/oracle/demodata/user01.dbf',
'/user03/oracle/demodata/embar01.dbf',
'/user10/oracle/demodata/abmx01.dbf',
'/user10/oracle/demodata/ahld01.dbf',
'/user10/oracle/demodata/ahlx01.dbf',
'/user10/oracle/demodata/ahmd01.dbf',
'/user10/oracle/demodata/ahmx01.dbf',
'/user10/oracle/demodata/amsd01.dbf',
'/user04/oracle/demodata/amsx01.dbf',
'/user04/oracle/demodata/amvd01.dbf',
'/user04/oracle/demodata/amvx01.dbf',
'/user04/oracle/demodata/asfd01.dbf',
'/user04/oracle/demodata/asfx01.dbf',
'/user04/oracle/demodata/asgd01.dbf',
'/user04/oracle/demodata/asgx01.dbf',
'/user04/oracle/demodata/asld01.dbf',
'/user04/oracle/demodata/aslx01.dbf',
'/user04/oracle/demodata/asod01.dbf',
'/user04/oracle/demodata/asox01.dbf',
'/user04/oracle/demodata/aspd01.dbf',
'/user04/oracle/demodata/aspx01.dbf',
'/user04/oracle/demodata/astd01.dbf',
'/user04/oracle/demodata/astx01.dbf',
'/user04/oracle/demodata/bend01.dbf',
'/user04/oracle/demodata/benx01.dbf',
'/user04/oracle/demodata/bicd01.dbf',
'/user04/oracle/demodata/bicx01.dbf',
'/user04/oracle/demodata/bild01.dbf',
'/user04/oracle/demodata/bilx01.dbf',
'/user04/oracle/demodata/bimd01.dbf',
'/user04/oracle/demodata/bimx01.dbf',
'/user04/oracle/demodata/bisd01.dbf',
'/user04/oracle/demodata/bisx01.dbf',
'/user04/oracle/demodata/bivd01.dbf',
'/user04/oracle/demodata/bivx01.dbf',
'/user04/oracle/demodata/bixd01.dbf',
'/user04/oracle/demodata/bixx01.dbf',
'/user04/oracle/demodata/bscd01.dbf',
'/user04/oracle/demodata/bscx01.dbf',
'/user04/oracle/demodata/cctd01.dbf',
'/user04/oracle/demodata/cctx01.dbf',
'/user04/oracle/demodata/cscd01.dbf',
'/user04/oracle/demodata/cscx01.dbf',
'/user04/oracle/demodata/csdd01.dbf',
'/user04/oracle/demodata/csdx01.dbf',
'/user04/oracle/demodata/csed01.dbf',
'/user04/oracle/demodata/csex01.dbf',
'/user04/oracle/demodata/csfd01.dbf',
'/user04/oracle/demodata/csfx01.dbf',
'/user04/oracle/demodata/csid01.dbf',
'/user04/oracle/demodata/csix01.dbf',
'/user04/oracle/demodata/csld01.dbf',
'/user04/oracle/demodata/cslx01.dbf',
'/user04/oracle/demodata/cspd01.dbf',
'/user04/oracle/demodata/cspx01.dbf',
'/user04/oracle/demodata/csrd01.dbf',
'/user04/oracle/demodata/csrx01.dbf',
'/user04/oracle/demodata/cssd01.dbf',
'/user04/oracle/demodata/cssx01.dbf',
'/user04/oracle/demodata/cuad01.dbf',
'/user04/oracle/demodata/cuax01.dbf',
'/user04/oracle/demodata/cued01.dbf',
'/user04/oracle/demodata/cuex01.dbf',
'/user04/oracle/demodata/cufd01.dbf',
'/user04/oracle/demodata/cufx01.dbf',
'/user04/oracle/demodata/cugd01.dbf',
'/user04/oracle/demodata/cugx01.dbf',
'/user04/oracle/demodata/cuid01.dbf',
'/user04/oracle/demodata/cuix01.dbf',
'/user04/oracle/demodata/cund01.dbf',
'/user04/oracle/demodata/cunx01.dbf',
'/user04/oracle/demodata/cupd01.dbf',
'/user04/oracle/demodata/cupx01.dbf',
'/user04/oracle/demodata/cusd01.dbf',
'/user04/oracle/demodata/cusx01.dbf',
'/user04/oracle/demodata/eaad01.dbf',
'/user04/oracle/demodata/eaax01.dbf',
'/user04/oracle/demodata/eamd01.dbf',
'/user04/oracle/demodata/eamx01.dbf',
'/user04/oracle/demodata/ecxd01.dbf',
'/user04/oracle/demodata/ecxx01.dbf',
'/user04/oracle/demodata/enid01.dbf',
'/user04/oracle/demodata/enix01.dbf',
'/user04/oracle/demodata/evmd01.dbf',
'/user04/oracle/demodata/evmx01.dbf',
'/user04/oracle/demodata/femd01.dbf',
'/user04/oracle/demodata/femx01.dbf',
'/user04/oracle/demodata/fiid01.dbf',
'/user04/oracle/demodata/fiix01.dbf',
'/user04/oracle/demodata/fptd01.dbf',
'/user04/oracle/demodata/fptx01.dbf',
'/user04/oracle/demodata/frmd01.dbf',
'/user04/oracle/demodata/frmx01.dbf',
'/user04/oracle/demodata/fted01.dbf',
'/user04/oracle/demodata/ftex01.dbf',
'/user04/oracle/demodata/fvd01.dbf',
'/user04/oracle/demodata/fvx01.dbf',
'/user04/oracle/demodata/gmad01.dbf',
'/user04/oracle/demodata/gmax01.dbf',
'/user04/oracle/demodata/gmdd01.dbf',
'/user04/oracle/demodata/gmdx01.dbf',
'/user04/oracle/demodata/gmed01.dbf',
'/user04/oracle/demodata/gmex01.dbf',
'/user04/oracle/demodata/gmfd01.dbf',
'/user04/oracle/demodata/gmfx01.dbf',
'/user04/oracle/demodata/gmid01.dbf',
'/user04/oracle/demodata/gmix01.dbf',
'/user04/oracle/demodata/gmld01.dbf',
'/user04/oracle/demodata/gmlx01.dbf',
'/user04/oracle/demodata/gmpd01.dbf',
'/user04/oracle/demodata/gmpx01.dbf',
'/user04/oracle/demodata/gmsd01.dbf',
'/user04/oracle/demodata/gmsx01.dbf',
'/user04/oracle/demodata/grd01.dbf',
'/user04/oracle/demodata/grx01.dbf',
'/user04/oracle/demodata/hrid01.dbf',
'/user04/oracle/demodata/hrix01.dbf',
'/user04/oracle/demodata/hxcd01.dbf',
'/user04/oracle/demodata/hxcx01.dbf',
'/user04/oracle/demodata/ibad01.dbf',
'/user04/oracle/demodata/ibax01.dbf',
'/user04/oracle/demodata/ibed01.dbf',
'/user04/oracle/demodata/ibex01.dbf',
'/user04/oracle/demodata/ibpd01.dbf',
'/user04/oracle/demodata/ibpx01.dbf',
'/user04/oracle/demodata/ibud01.dbf',
'/user04/oracle/demodata/ibux01.dbf',
'/user04/oracle/demodata/ibyd01.dbf',
'/user04/oracle/demodata/ibyx01.dbf',
'/user04/oracle/demodata/iebd01.dbf',
'/user04/oracle/demodata/iebx01.dbf',
'/user04/oracle/demodata/iecd01.dbf',
'/user04/oracle/demodata/iecx01.dbf',
'/user04/oracle/demodata/iemd01.dbf',
'/user04/oracle/demodata/iemx01.dbf',
'/user04/oracle/demodata/ieod01.dbf',
'/user04/oracle/demodata/ieox01.dbf',
'/user04/oracle/demodata/iesd01.dbf',
'/user04/oracle/demodata/iesx01.dbf',
'/user04/oracle/demodata/ieud01.dbf',
'/user04/oracle/demodata/ieux01.dbf',
'/user04/oracle/demodata/iexd01.dbf',
'/user04/oracle/demodata/iexx01.dbf',
'/user04/oracle/demodata/igcd01.dbf',
'/user04/oracle/demodata/igcx01.dbf',
'/user04/oracle/demodata/igfd01.dbf',
'/user04/oracle/demodata/igfx01.dbf',
'/user04/oracle/demodata/igid01.dbf',
'/user04/oracle/demodata/igix01.dbf',
'/user04/oracle/demodata/igsd01.dbf',
'/user04/oracle/demodata/igsx01.dbf',
'/user04/oracle/demodata/igwd01.dbf',
'/user04/oracle/demodata/igwx01.dbf',
'/user04/oracle/demodata/imcd01.dbf',
'/user04/oracle/demodata/imcx01.dbf',
'/user04/oracle/demodata/imtd01.dbf',
'/user04/oracle/demodata/imtx01.dbf',
'/user04/oracle/demodata/ipad01.dbf',
'/user04/oracle/demodata/ipax01.dbf',
'/user04/oracle/demodata/ipdd01.dbf',
'/user04/oracle/demodata/ipdx01.dbf',
'/user04/oracle/demodata/iscd01.dbf',
'/user04/oracle/demodata/iscx01.dbf',
'/user04/oracle/demodata/itgd01.dbf',
'/user04/oracle/demodata/itgx01.dbf',
'/user04/oracle/demodata/jad01.dbf',
'/user04/oracle/demodata/jax01.dbf',
'/user04/oracle/demodata/jld01.dbf',
'/user04/oracle/demodata/jlx01.dbf',
'/user04/oracle/demodata/jtfd01.dbf',
'/user04/oracle/demodata/jtfx01.dbf',
'/user04/oracle/demodata/jtmd01.dbf',
'/user04/oracle/demodata/jtmx01.dbf',
'/user04/oracle/demodata/jtsd01.dbf',
'/user04/oracle/demodata/jtsx01.dbf',
'/user04/oracle/demodata/med01.dbf',
'/user04/oracle/demodata/mex01.dbf',
'/user04/oracle/demodata/msdd01.dbf',
'/user04/oracle/demodata/msdx01.dbf',
'/user04/oracle/demodata/msod01.dbf',
'/user04/oracle/demodata/msox01.dbf',
'/user04/oracle/demodata/msrd01.dbf',
'/user04/oracle/demodata/msrx01.dbf',
'/user04/oracle/demodata/mwad01.dbf',
'/user04/oracle/demodata/mwax01.dbf',
'/user04/oracle/demodata/okbd01.dbf',
'/user04/oracle/demodata/okbx01.dbf',
'/user04/oracle/demodata/okcd01.dbf',
'/user04/oracle/demodata/okcx01.dbf',
'/user04/oracle/demodata/oked01.dbf',
'/user04/oracle/demodata/okex01.dbf',
'/user04/oracle/demodata/okid01.dbf',
'/user04/oracle/demodata/okix01.dbf',
'/user04/oracle/demodata/okod01.dbf',
'/user04/oracle/demodata/okox01.dbf',
'/user04/oracle/demodata/okrd01.dbf',
'/user04/oracle/demodata/okrx01.dbf',
'/user04/oracle/demodata/oksd01.dbf',
'/user04/oracle/demodata/oksx01.dbf',
'/user04/oracle/demodata/okxd01.dbf',
'/user04/oracle/demodata/okxx01.dbf',
'/user04/oracle/demodata/ontd01.dbf',
'/user04/oracle/demodata/ontx01.dbf',
'/user04/oracle/demodata/opid01.dbf',
'/user04/oracle/demodata/opix01.dbf',
'/user04/oracle/demodata/ozfd01.dbf',
'/user04/oracle/demodata/ozfx01.dbf',
'/user04/oracle/demodata/ozpd01.dbf',
'/user04/oracle/demodata/ozpx01.dbf',
'/user04/oracle/demodata/ozsd01.dbf',
'/user04/oracle/demodata/ozsx01.dbf',
'/user04/oracle/demodata/pmid01.dbf',
'/user04/oracle/demodata/pmix01.dbf',
'/user04/oracle/demodata/pnd01.dbf',
'/user04/oracle/demodata/pnx01.dbf',
'/user04/oracle/demodata/poad01.dbf',
'/user04/oracle/demodata/poax01.dbf',
'/user04/oracle/demodata/pomd01.dbf',
'/user04/oracle/demodata/pomx01.dbf',
'/user04/oracle/demodata/posd01.dbf',
'/user04/oracle/demodata/posx01.dbf',
'/user04/oracle/demodata/psad01.dbf',
'/user04/oracle/demodata/psax01.dbf',
'/user04/oracle/demodata/psbd01.dbf',
'/user04/oracle/demodata/psbx01.dbf',
'/user04/oracle/demodata/pspd01.dbf',
'/user04/oracle/demodata/pspx01.dbf',
'/user04/oracle/demodata/pvd01.dbf',
'/user04/oracle/demodata/pvx01.dbf',
'/user04/oracle/demodata/qpd01.dbf',
'/user04/oracle/demodata/qpx01.dbf',
'/user04/oracle/demodata/rhxd01.dbf',
'/user04/oracle/demodata/rhxx01.dbf',
'/user04/oracle/demodata/rlmd01.dbf',
'/user04/oracle/demodata/rlmx01.dbf',
'/user04/oracle/demodata/vead01.dbf',
'/user04/oracle/demodata/veax01.dbf',
'/user04/oracle/demodata/wmsd01.dbf',
'/user04/oracle/demodata/wmsx01.dbf',
'/user04/oracle/demodata/wpsd01.dbf',
'/user04/oracle/demodata/wpsx01.dbf',
'/user04/oracle/demodata/wshd01.dbf',
'/user04/oracle/demodata/wshx01.dbf',
'/user04/oracle/demodata/wsmd01.dbf',
'/user04/oracle/demodata/wsmx01.dbf',
'/user04/oracle/demodata/xdpd01.dbf',
'/user04/oracle/demodata/xdpx01.dbf',
'/user04/oracle/demodata/xlad01.dbf',
'/user04/oracle/demodata/xlax01.dbf',
'/user04/oracle/demodata/xncd01.dbf',
'/user04/oracle/demodata/xncx01.dbf',
'/user04/oracle/demodata/xnid01.dbf',
'/user04/oracle/demodata/xnix01.dbf',
'/user04/oracle/demodata/xnmd01.dbf',
'/user04/oracle/demodata/xnmx01.dbf',
'/user04/oracle/demodata/xnpd01.dbf',
'/user04/oracle/demodata/xnpx01.dbf',
'/user04/oracle/demodata/xnsd01.dbf',
'/user04/oracle/demodata/xnsx01.dbf',
'/user04/oracle/demodata/xtrd01.dbf',
'/user04/oracle/demodata/xtrx01.dbf',
'/user04/oracle/demodata/system02.dbf',
'/user11/oracle/demodata/apd02.dbf',
'/user10/oracle/demodata/system03.dbf',
'/user10/oracle/demodata/aold02.dbf',
'/user07/oracle/demodata/glx02.dbf',
'/user09/oracle/demodata/ctxsys.dbf',
'/user10/oracle/demodata/slightd01.dbf',
'/user10/oracle/demodata/slighti01.dbf'
CHARACTER SET WE8ISO8859P1
;
ALTER DATABASE OPEN RESETLOGS;
ALTER TABLESPACE TEMP ADD TEMPFILE '/user02/oracle/demodata/temp01.dbf' REUSE; --only use REUSE if the datafile is there!!! else use size 100M;

ie
--ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/demodata/temp01.dbf'
SIZE 1048M AUTOEXTEND OFF;



hth

Sy UK

 

[parbhani]

Oh great ... and quick ......

Am I correct in saying that before I do the steps you mentioned, I set the environment veriable ORACLE_SID to "DEMO" ?
One more question, did you create the script you mentioned with ,
alter database backup controlfile to trade ??

Thanks again.

 

[alexhu]

Scunningham99

are you sure you have enough data files there ?

Alex

 

[Scunningham99]

yes

sql> alter database backup controlfile to trace;

This creates a trace file in the udump directory!

Yes set the ORACLE_SID env variable and check instance_name and db_name in your init.ora file.

Its probably easier changing the script to whatever the db was named previously to PROD or something.

ps. got a bit carried away with dbf files in script.

Sy UK