1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

error in creating oracle db manually in Win XP SP2 OS

Discussion in 'Installation - Windows' started by happytjia, Jun 21, 2009.

  1. happytjia

    happytjia Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    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.

    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
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    Hi

    First of all, since you are on XP, why are you trying to manually script your way to an Oracle install? Installing Oracle on XP is one of the easiest things out there if you use the Oracle Universal Installer (OUI). You have the ORA-04031 error which means there is a problem with shared memory pool. Trying to rectify this manually would not be asne asy prospect. I suggest you use the OUI.
     
  3. happytjia

    happytjia Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    I do that because I try to develop application for converting Oracle DB into SQL Server DB and vice versa for my thesis. For that reason I try to make my application able to create the database automatically. Is it possible to do that???

    Am I right that my application shall use script to create the database???Or maybe I've had a wrong concept???Thanks.
     
  4. happytjia

    happytjia Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    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??
    thank you
     
  5. simply_dba

    simply_dba Forum Advisor

    Messages:
    95
    Likes Received:
    5
    Trophy Points:
    140
    Location:
    Kolkata, India
    The above scripts looks like they are created from dbca (generate scripts).
    You have not given shared_pool_size (In fact, i am unable to find any memory related parameters in that init ora).
    The default sizes of these memory parameters are too low to create database objects specially java objects.The error you are facing above is due to low shared_pool_size.
    I would advice you to first understand the basic concepts of Oracle Architecture before creating any database.