Manually Create a Database On Personal 9i Edition
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Manually Create a Database On Personal 9i Edition

  1. #1
    Join Date
    Mar 2002
    Posts
    2
    This is my first encounter with Oracle, so I read some documentations and installed Oracle9i Personal Edition for Win98.
    The installer created a database with SID=first.
    Everything is ok (excepting that i have to start lsnrctl manually and I hat to make an PO8 account/password to use the built in PO8 user in Oracle Navigator - took me some time to figure this out).
    But afterwards, I wanted more control and insisted to create manually another database with SID=second (I want to have both of them installed and after some practice move to a Linux installation).

    I followed this steps :
    In the directory C:\oracle\ora90\database where is initfirst.ora (init$ORACLE_SID.ora), I created initsecond.ora with the same structure but slightly modified to suit the "second" SID
    It looks like this :

    ###########################################
    # Cache and I/O
    ###########################################
    db_block_size=4096
    db_cache_size=12M
    disk_asynch_io=FALSE

    ###########################################
    # Cursors and Library Cache
    ###########################################
    open_cursors=300

    ###########################################
    # Diagnostics and Statistics
    ###########################################
    background_dump_dest=c:\oracle\admin\second\bdump
    core_dump_dest=c:\oracle\admin\second\cdump
    timed_statistics=TRUE
    user_dump_dest=c:\oracle\admin\second\udump

    ###########################################
    # Distributed, Replication and Snapshot
    ###########################################
    db_domain=""
    remote_login_passwordfile=EXCLUSIVE

    ###########################################
    # File Configuration
    ###########################################
    control_files=("c:\oracle\oradata\second\CONTROL01.CTL", "c:\oracle\oradata\second\CONTROL02.CTL", "c:\oracle\oradata\second\CONTROL03.CTL")

    ###########################################
    # Miscellaneous
    ###########################################
    compatible=9.0.0
    db_name=second

    ###########################################
    # Network Registration
    ###########################################
    instance_name=second

    ###########################################
    # Pools
    ###########################################
    java_pool_size=33554432
    shared_pool_size=50331648

    ###########################################
    # Processes and Sessions
    ###########################################
    processes=100

    ###########################################
    # Redo Log and Recovery
    ###########################################
    fast_start_mttr_target=0

    ###########################################
    # Sort, Hash Joins, Bitmap Indexes
    ###########################################
    sort_area_size=524288

    ###########################################
    # System Managed Undo and Rollback Segments
    ###########################################
    undo_management=AUTO
    undo_tablespace=UNDOTBS
    #########################

    I maked the appropiate directories C:\oracle\admin\second and C:\oracle\oradata\second (at my first try, the installing process breaked because I didn't make these ones )
    then I created the script c:\create.sql :

    CREATE DATABASE second
    MAXINSTANCES 1
    MAXLOGHISTORY 1
    MAXLOGFILES 5
    MAXLOGMEMBERS 5
    MAXDATAFILES 100
    DATAFILE 'c:\oracle\oradata\second\system01.dbf' SIZE 100M REUSE
    UNDO TABLESPACE undotbs DATAFILE 'c:\oracle\oradata\second\undotbs01.dbf'
    SIZE 60M REUSE AUTOEXTEND ON NEXT 2420K MAXSIZE UNLIMITED
    DEFAULT TEMPORARY TABLESPACE tempts1
    CHARACTER SET US7ASCII
    NATIONAL CHARACTER SET AL16UTF16
    LOGFILE GROUP 1 ('c:\oracle\oradata\second\redo01.log') SIZE 40M,
    GROUP 2 ('c:\oracle\oradata\second\redo02.log') SIZE 40M,
    GROUP 3 ('c:\oracle\oradata\second\redo03.log') SIZE 40M

    Then followed this steps:

    1.C:\ sqlplus /nolog
    2 SQL>connect sys/change_on_install as sysdba
    #At this point the instance database start and mount automatically "first" SID, so I issue shutdown immediate to have oracle 9i instance started without a mounted database
    3 SQL> shutdown immediate
    4 SQL>startup nomount pfile='C:\oracle\ora90\database\initsecond.ora'
    #(if I don't use pfile option an error arise because of "first")
    5 SQL>get C:\create.sql
    6 SQL>run
    I get the following message :
    Error at line 1:
    ORA-01092 : the Oracle instance finished; forced disconnect

    Oops not so good ! I started browsing the documentation and one idea come into my mind.
    Maybe something related to ORACLE_SID environment variable.
    So I try
    1. C:\set ORACLE_SID=second
    2. C:\ sqlplus /nolog
    3. SQL>connect sys/change_on_install as sysdba
    #The Oracle 9i Startup window appear and ask me for password ??? What password ?

    I'm stucked at this point as I can't go further and I want both databases "first" and "second" created and I'd like to the "second" one manually.

    Any help would be much appreciated.

    Best regards

  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    The problem is here, in the init.ora you have:

    undo_management=AUTO
    undo_tablespace=UNDOTBS

    But the tablespace does not exist yet.

    Now, set UNDO_MANAGEMENT to MANUAL.

    Startup the database and create a new UNDO tablespace UNDOTBS. If you want the instance to work under AUM set:

    undo_tablespace=UNDOTBS and bounce.

    Remark: 9i is not a database meant for using wizards and Oracle generated scripts, they don't always work. Get to know 9i, read about it first, then try out things.




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width