Chauhan 0 Report post Posted March 4, 2007 Manual Creation of database in windows with oracle 9i (Step-by-Step)(Name of the database=db18)(Note: all commands are in bold letters)1. Open the command prompt and execute the command sqlplus/nolog2. Connect to the default database as sysdba in sql prompt SQL>conn sys/oracle assysdba you can see the name of that database by executing select name fromv$database;3. Now open another command prompt and set oracle SID as set oracle_sid=db184. Start a windows service with internal password oradim new sid <database name>intpwd <password> is the syntax. In this case I use ceylonlinux_suranga as passwordto create db18 service like, oradim new sid db18 intpwd ceylonlinux_suranga5. Create a directory called db18. In my case I created it in d:\ drive (Note: all myparameter files and .sql file that are going to discuss following are based on mylocation, you can change the location according to yours)6. Here is my initdb18.ora that I saved it in d:\db18 folder. This is the static parameterfile that I used in my database creation (Note: If you are creating a database with adifferent name and in a different location make sure to edit the relevant fields in thisfile)############################################################################### Copyright © 1991, 2001, 2002 by Oracle Corporation########################################################################################################################## Cache and I/O###########################################db_block_size=8192db_cache_size=25165824db_file_multiblock_read_count=16############################################ Cursors and Library Cache###########################################open_cursors=300############################################ Database Identification###########################################db_domain=""db_name=db18############################################ Diagnostics and Statistics###########################################2background_dump_dest=d:\db18core_dump_dest=d:\db18timed_statistics=TRUEuser_dump_dest=d:\db18############################################ File Configuration###########################################control_files=("d:\db18\control01.ctl", "d:\db18\CONTROL02.ctl","d:\db18\CONTROL03.ctl")############################################ Instance Identification###########################################instance_name=db18############################################ Job Queues###########################################job_queue_processes=10############################################ MTS###########################################dispatchers="(PROTOCOL=TCP) (SERVICE=orcl1XDB)"############################################ Miscellaneous###########################################aq_tm_processes=1compatible=9.2.0.0.0############################################ Optimizer###########################################hash_join_enabled=TRUEquery_rewrite_enabled=FALSEstar_transformation_enabled=FALSE############################################ Pools###########################################java_pool_size=33554432large_pool_size=8388608shared_pool_size=50331648############################################ Processes and Sessions###########################################processes=1503############################################ Redo Log and Recovery###########################################fast_start_mttr_target=300############################################ Security and Auditing###########################################remote_login_passwordfile=EXCLUSIVE############################################ Sort, Hash Joins, Bitmap Indexes###########################################pga_aggregate_target=25165824sort_area_size=524288############################################ System Managed Undo and Rollback Segments###########################################undo_management=AUTOundo_retention=1undo_tablespace=UNDOTBS7. Now type following in your current command prompt sqlplus/nolog and in sqlprompt type conn sys/ceylonlinux_suranga as sysdba then you should see that youare connected to an idle instance8. Now start the instance in nomount mode as, startup nomountpfile=d:\db18\initdb18.ora why are you starting the database in nomount mode ?The reason is still we are not created control files. An instance would be started inthe NOMOUNT stage only during database creation or the re-creation of control files.9. This step is to create the database using dbca.sql script that I saved in d:\db18 folderappears followsCREATE DATABASE db18LOGFILE GROUP 1('d:\db18\redo01.log') SIZE 100M,GROUP 2('d:\db18\redo02.log') SIZE 100M,GROUP 3('d:\db18\redo03.log') SIZE 100MMAXLOGFILES 5MAXLOGMEMBERS 5MAXLOGHISTORY 1MAXDATAFILES 100MAXINSTANCES 1CHARACTER SET US7ASCIINATIONAL CHARACTER SET AL16UTF16DATAFILE 'd:\db18\system01.dbf' SIZE 325MUNDO TABLESPACE UNDOTBSDATAFILE 'd:\db18\UNDOTBS.dbf'SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED;410. Run the script in the SQL prompt as this, SQL>@d:\db18\dbca.sql once you run thisscript you can see the control files, redo log file, Alert log file, .dbf files and .trc(Background Trace files & User Trace files) files are created in d:\db18 folder.11. Now you can shutdown the database using shutdown command.12. Once the database shutdown reboot your PC13. Connect again as sysdba to default database sqlplus/nolog, SQL>conn sys/oracle assysdba check which database you are in.14. If it is not db18 set oracle sid as we did before in another command prompt as setoracle_sid=db1815. If db18 windows service is not started start it manually or execute this oradim STARTUP sid db18 intpwd ceylonlinux_suranga16. Now connect to the database sqlplus sys/ceylonlinux_suranga as sysdba17. startup pfile=d:\db18\initdb18.ora (Note: Here we dont need to start the databasein nomount mode because we have already created control files)18. Execute catalog.sql SQL>@d:\ORANT\rdbms\admin\catalog.sql19. Execute catproc.sql SQL>@d:\ORANT\rdbms\admin\catproc.sqlNote: if the password file is corrupted or if you get an error in authentication you can recreatethe password file as follows, but make sure to delete the existing password file.C:>orapwd file=d:\ORANT\database \PWDdb18.ORA password=ceylonlinux_surangaThis is what you need to do every time when you start your database..C:\Documents and Settings\qq>set oracle_sid=db18C:\Documents and Settings\qq>oradim -STARTUP -sid db18 -intpwdceylonlinux_surangaORA-01078: failure in processing system parametersLRM-00109: could no t open parameter file 'D:\ORANT\DATABASE\INITDB18.ORA'C:\Documents and Settings\qq>sqlplus "sys/ceylonlinux_suranga as sysdba"SQL*Plus: Release 9.2.0.1.0 - Production on Mon Dec 6 21:22:02 2004Copyright © 1982, 2002, Oracle Corporation. All rights reserved.Connected to an idle instance.SQL> startup pfile=d:\db18\initdb18.oraORACLE instance started.Total System Global Area 135338868 bytesFixed Size 453492 bytesVariable Size 109051904 bytesDatabase Buffers 25165824 bytes5Redo Buffers 667648 bytesDatabase mounted.Database opened.SQL>Now you need to edit the following filesˇ D:\ORANT\network\admin\tnsnames.oraˇ D:\ORANT\network\admin\listener.oraHere are the files that I usedyou can change those accordingly# TNSNAMES.ORA Network Configuration File: D:\ORANT\network\admin\tnsnames.ora# Generated by Oracle configuration tools.SURANGA =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = NEWP4)(PORT = 1521)))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = suranga)))db18 =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = NEWP4)(PORT = 1521)))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = db18)))INST1_HTTP =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = NEWP4)(PORT = 1521)))(CONNECT_DATA =(SERVER = SHARED)(SERVICE_NAME = MODOSE)(PRESENTATION = http://forums.xisto.com/no_longer_exists/)))6EXTPROC_CONNECTION_DATA =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0)))(CONNECT_DATA =(SID = PLSExtProc)(PRESENTATION = RO)))# LISTENER.ORA Network Configuration File: D:\ORANT\network\admin\listener.ora# Generated by Oracle configuration tools.LISTENER =(DESCRIPTION_LIST =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = NEWP4)(PORT = 1521)))))SID_LIST_LISTENER =(SID_LIST =(SID_DESC =(SID_NAME = PLSExtProc)(ORACLE_HOME = D:\ORANT)(PROGRAM = extproc))(SID_DESC =(GLOBAL_DBNAME = suranga)(ORACLE_HOME = D:\ORANT)(SID_NAME = suranga))(SID_DESC =(GLOBAL_DBNAME = db18)(ORACLE_HOME = D:\db18)(SID_NAME = db18)))Now start OEM console and click Add Database To Tree under Navigator menu item.From the window select second radio button saying Add selected databases from your localtnsnames.ora fileFrom there select db18. Once you select it you should see OEM console as follows Share this post Link to post Share on other sites
iGuest 3 Report post Posted December 13, 2009 ora-00401 errorMannual Creation Of Oracle DatabaseReplying to ChauhanHi Chauhan,When I follow the instructions I am fine up until instruction 7, I get presented with ora-00401 the value compatible is not supported by this release, I am trying to run it using oracle 11g. As I am new to creating oracle databases I was woundering if you could help me.Many thanksShane Pickerill-question by shane Share this post Link to post Share on other sites
k_nitin_r 8 Report post Posted January 1, 2010 Hi!@iGuest (Shane Pickerill)The Automatic Storage Management (ASM) feature introduced in the Oracle 10g database makes some of the settings obsolete. Although this means that you have less administrative tasks to perform, it also means that some of the older scripts will not run correctly. Share this post Link to post Share on other sites