Member Avatar for 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

  • 1 Contributor
  • 1 Reply
  • 209 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by happytjia
Member Avatar for happytjia

Tq for correcting my thread Mr. Peter.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.