error in creating oracle db manually in Win XP SP2 OS

[happytjia]

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\cont rol0 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

[tyro]

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.

[happytjia]

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.

[happytjia]

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

[simply_dba]

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.