Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2009
    Posts
    5

    Unanswered: creating db manually script

    First of all. Sorry if I don't have a good English...
    My operating system is Windows XP SP2
    I'd created script files below for creating Oracle database manually:

    dbcamin.bat
    mkdir C:\oracle\product\10.2.0\admin\dbcamin\adump
    mkdir C:\oracle\product\10.2.0\admin\dbcamin\bdump
    mkdir C:\oracle\product\10.2.0\admin\dbcamin\cdump
    mkdir C:\oracle\product\10.2.0\admin\dbcamin\udump
    mkdir C:\oracle\product\10.2.0\admin\dbcamin\dpdump
    mkdir C:\oracle\product\10.2.0\flash_recovery_area\dbcam in
    mkdir C:\oracle\product\10.2.0\admin\dbcamin\pfile
    mkdir C:\oracle\product\10.2.0\cfgtoollogs\emca\dbcamin
    mkdir C:\oracle\product\10.2.0\flash_recovery_area
    mkdir C:\oracle\product\10.2.0\oradata\dbcamin
    set ORACLE_SID=dbcamin
    C:\oracle\product\10.2.0\db_1\bin\oradim.exe -new -sid DBCAMIN -startmode manual -spfile
    C:\oracle\product\10.2.0\db_1\bin\oradim.exe -edit -sid DBCAMIN -startmode auto -srvcstart system
    C:\oracle\product\10.2.0\db_1\bin\sqlplus /nolog @C:\oracle\product\10.2.0\admin\dbcamin\scripts\db camin.sql

    CreateDB.sql
    connect SYS/dbcamin as SYSDBA
    set echo on
    spool C:\oracle\product\10.2.0\admin\dbcamin\scripts\Cre ateDB.log
    startup nomount pfile="C:\oracle\product\10.2.0\db_1\database\init dbcamin.ora";
    CREATE DATABASE dbcamin
    MAXINSTANCES 8
    MAXLOGHISTORY 1
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    DATAFILE 'C:\oracle\product\10.2.0\oradata\dbcamin\system01 .dbf' SIZE 300M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
    EXTENT MANAGEMENT LOCAL
    SYSAUX DATAFILE 'C:\oracle\product\10.2.0\oradata\dbcamin\sysaux01 .dbf' SIZE 120M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
    SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE 'C:\oracle\product\10.2.0\oradata\dbcamin\temp01.d bf' SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
    SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE 'C:\oracle\product\10.2.0\oradata\dbcamin\undotbs0 1.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
    CHARACTER SET AL32UTF8
    NATIONAL CHARACTER SET AL16UTF16
    LOGFILE GROUP 1 ('C:\oracle\product\10.2.0\oradata\dbcamin\redo01. log') SIZE 51200K,
    GROUP 2 ('C:\oracle\product\10.2.0\oradata\dbcamin\redo02. log') SIZE 51200K,
    GROUP 3 ('C:\oracle\product\10.2.0\oradata\dbcamin\redo03. log') SIZE 51200K
    USER SYS IDENTIFIED BY dbcamin USER SYSTEM IDENTIFIED BY dbcamin;
    spool off

    CreateDBCatalog.sql
    connect SYS/dbcamin as SYSDBA
    set echo on
    spool C:\oracle\product\10.2.0\admin\dbcamin\scripts\Cre ateDBCatalog.log
    @C:\oracle\product\10.2.0\db_1\rdbms\admin\catalog .sql;
    @C:\oracle\product\10.2.0\db_1\rdbms\admin\catbloc k.sql;
    @C:\oracle\product\10.2.0\db_1\rdbms\admin\catproc .sql;
    @C:\oracle\product\10.2.0\db_1\rdbms\admin\catoctk .sql;
    @C:\oracle\product\10.2.0\db_1\rdbms\admin\owminst .plb;
    connect SYSTEM/dbcamin
    @C:\oracle\product\10.2.0\db_1\sqlplus\admin\pupbl d.sql;
    connect SYSTEM/dbcamin
    set echo on
    spool C:\oracle\product\10.2.0\admin\dbcamin\scripts\sql PlusHelp.log
    @C:\oracle\product\10.2.0\db_1\sqlplus\admin\help\ hlpbld.sql helpus.sql;
    spool off
    spool off

    CreateDBFiles.sql
    connect SYS/dbcamin as SYSDBA
    set echo on
    spool C:\oracle\product\10.2.0\admin\dbcamin\scripts\Cre ateDBFiles.log
    CREATE SMALLFILE TABLESPACE "USERS" LOGGING DATAFILE 'C:\oracle\product\10.2.0\oradata\dbcamin\users01. dbf' SIZE 5M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
    ALTER DATABASE DEFAULT TABLESPACE "USERS";
    spool off

    dbcamin.sql
    set verify off
    PROMPT specify a password for sys as parameter 1;
    DEFINE sysPassword = dbcamin
    PROMPT specify a password for system as parameter 2;
    DEFINE systemPassword = dbcamin
    host C:\oracle\product\10.2.0\db_1\bin\orapwd.exe file=C:\oracle\product\10.2.0\db_1\database\PWDdbc amin.ora password=dbcamin force=y
    @C:\oracle\product\10.2.0\admin\dbcamin\scripts\Cr eateDB.sql
    @C:\oracle\product\10.2.0\admin\dbcamin\scripts\Cr eateDBFiles.sql
    @C:\oracle\product\10.2.0\admin\dbcamin\scripts\Cr eateDBCatalog.sql
    @C:\oracle\product\10.2.0\admin\dbcamin\scripts\lo ckAccount.sql
    @C:\oracle\product\10.2.0\admin\dbcamin\scripts\po stDBCreation.sql

    lockAccount.sql
    set echo on

    spool C:\oracle\product\10.2.0\admin\dbcamin\scripts\loc kAccount.log

    BEGIN
    FOR item IN ( SELECT USERNAME FROM DBA_USERS WHERE USERNAME NOT IN ('SYS','SYSTEM') )
    LOOP
    dbms_output.put_line('Locking and Expiring: ' || item.USERNAME);
    execute immediate 'alter user ' || item.USERNAME || ' password expire account lock' ;
    END LOOP;
    END;
    /

    spool off

    postDBCreation.sql
    connect SYS/dbcamin as SYSDBA
    set echo on
    spool C:\oracle\product\10.2.0\admin\dbcamin\scripts\pos tDBCreation.log
    connect SYS/dbcamin as SYSDBA
    set echo on
    create spfile='C:\oracle\product\10.2.0\db_1\dbs\spfiledb camin.ora' FROM pfile='C:\oracle\product\10.2.0\db_1\database\init dbcamin.ora';
    shutdown immediate;
    connect SYS/dbcamin as SYSDBA
    startup ;
    select 'utl_recomp_begin: ' || to_char(sysdate, 'HH:MIS') from dual;
    execute utl_recomp.recomp_serial();
    select 'utl_recomp_end: ' || to_char(sysdate, 'HH:MIS') from dual;
    connect SYS/dbcamin as SYSDBA
    spool C:\oracle\product\10.2.0\admin\dbcamin\scripts\pos tDBCreation.log

    initdbcamin.ora
    ################################################## ############################
    # Copyright (c) 1991, 2001, 2002 by Oracle Corporation
    ################################################## ############################

    ###########################################
    # NLS
    ###########################################
    nls_language="ENGLISH"
    nls_territory="AMERICA"

    ###########################################
    # Miscellaneous
    ###########################################
    compatible=10.2.0.1.0

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

    ###########################################
    # Archive
    ###########################################
    LOG_ARCHIVE_DEST_1='LOCATION=C:\oracle\product\10. 2.0\flash_recovery_area\dbcamin\ARCHIVELOG'
    LOG_ARCHIVE_DEST_STATE_1=ENABLE
    LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
    LOG_ARCHIVE_START=TRUE

    ###########################################
    # Diagnostics and Statistics
    ###########################################
    BACKGROUND_DUMP_DEST=C:\oracle\product\10.2.0\admi n\dbcamin\bdump
    CORE_DUMP_DEST=C:\oracle\product\10.2.0\admin\dbca min\cdump
    TIMED_STATISTICS=TRUE
    USER_DUMP_DEST=C:\oracle\product\10.2.0\admin\dbca min\udump

    ###########################################
    # Cache and I/O
    ###########################################
    db_block_size=4096
    db_cache_size=25165824
    db_file_multiblock_read_count=16

    ###########################################
    # System Managed Undo and Rollback Segments
    ###########################################
    undo_management=auto
    undo_retention=120
    undo_tablespace=UNDOTBS1

    ###########################################
    # Security and Auditing
    ###########################################
    audit_file_dest=C:\oracle\product\10.2.0\admin\dbc amin\adump
    audit_trail=db
    remote_login_passwordfile=EXCLUSIVE

    ###########################################
    # Database Identification
    ###########################################
    db_domain=""
    db_name=dbcamin
    instance_name=dbcamin

    ###########################################
    # File Configuration
    ###########################################
    control_files=("C:\oracle\product\10.2.0\oradata\d bcamin\control01.ctl", "C:\oracle\product\10.2.0\oradata\dbcamin\control0 2.ctl")
    db_recovery_file_dest=C:\oracle\product\10.2.0\fla sh_recovery_area
    db_recovery_file_dest_size=2147483648

    ###########################################
    # Processes and Sessions
    ###########################################
    processes=60
    sessions=71

    ###########################################
    # Distributed, Replication and Snapshot
    ###########################################
    DB_DOMAIN=us.oracle.com
    REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

    ###########################################
    # Redo Log and Recovery
    ###########################################
    FAST_START_MTTR_TARGET=300

    but I'd got some errors when I tried to start up or alter database open the database...
    This is the message..

    Oracle instance terminated. Disconnection forced.

  2. #2
    Join Date
    Jun 2009
    Posts
    5
    when I tried to check the process inside the command prompt I list some of the errors like written below...

    SQL> create or replace view v_$_lock as select * from v$_lock;
    create or replace view v_$_lock as select * from v$_lock;
    *
    ERROR at line 1:
    ORA-00604: error occurred at recursive SQL level 2
    ORA-04031: unable to allocate 84 bytes of shared memory ("shared pool","select inst_id,addr,ksqlkadr...","Typecheck","opndef:qkex rAddMatching1")

    SQL> grant select on v_$_lock to select_catalog_role;
    grant select on v_$_lock to select_catalog_role;
    *
    ERROR at line 1:
    ORA-00942: table or view does not exist

    SQL> grant select on v_$flashback_database_logfile to select_catalog_role;
    grant select on v_$flashback_database_logfile to select_catalog_role
    *
    ERROR at line 1:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-04031: unable to allocate 212 bytes of shared memory ("shared pool","select i.obj#,i.ts#,i.file#,...","sql area","KGHSC_ALLOC_BUF:buf")

    SQL> create or replace public synonim gv$dlm_all_locks;
    create or replace public synonim gv$dlm_all_locks
    *
    ERROR at line 1:
    ORA-04031: unable to allocate 3904 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","kglsim object batch")

    SQL> grant select on gv$dlm_all_locks to select_catalog_role;
    grant select on gv$dlm_all_locks to select_catalog_role;
    *
    ERROR at line 1:
    ORA-04031: unable to allocate 3904 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","kglsim object batch")

    CREATE OR REPLACE PACKAGE dbms_registry_server IS
    *
    ERROR at line 1:
    ORA-06554: package DBMS_STANDARD must be created before using PL/SQL

    CREATE OR REPLACE PACKAGE BODY dbms_registry
    *
    ERROR at line 1:
    ORA-06554: package DBMS_STANDARD must be created before using PL/SQL

    SQL> BEGIN
    2 dbms_registry.loading('CATALOG', 'Oracle Database Catalog Views',
    3 'dbms_registry_sys.validate_catalog');
    4 END;
    5 /
    BEGIN
    *ERROR at line 1:
    ORA-06553: PLS-213: package STANDARD not accessible

    at last... what's wrong???what should I do???
    TQ before and after.
    GBU

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    at a minimum I suggest you add the following parameters

    dbcamin.__db_cache_size=171966464
    dbcamin.__java_pool_size=4194304
    dbcamin.__large_pool_size=4194304
    dbcamin.__shared_pool_size=79691776
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Jun 2009
    Posts
    5
    hmmm in what file I should add those parameters???
    inside initdbcamin.ora????

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    just use dbca instead
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Jun 2009
    Posts
    5

    Question

    Hmmm is it possible for my application to create oracle database using dbca???Then what command I can use to control dbca???
    I'm developing Visual Studio application that can create database automatically.
    TQ

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Hmmm is it possible for my application to create oracle database using dbca???
    DBCA is Data Base Creation Assistant that is a nice point & click GUI!

    >Then what command I can use to control dbca???
    dbca

    >I'm developing Visual Studio application that can create database automatically.
    You are trying to reinvent the wheel & you are not qualified to do so.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Jun 2009
    Posts
    5

    Post

    is it possible for me to edit the script that is created when I use DBCA to create new database, so I can use the edited one to make new database??

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >is it possible for me to edit the script
    It is possible for me to edit script.
    I can not answer for you. Only you can answer.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  10. #10
    Join Date
    Jun 2009
    Posts
    7
    DBCA can be used in silent mode by using resposne file.

    dbca -silent -resonpsefile <Response File>

    Google for reponse file examaple.


    your manually script also look good. Make sure to set the parameter suggested in your init<SID>.ora file

Posting Permissions

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