Create a Basic Database Manually

[gracew]

Hi

I am trying to create an oracle database with the CREATE DATABASE command. I am running windows 2000 server and have create the oradim which is fine.

I login as "sqlplus /NOLOG" then issue "connect sys as sysdba" when this connects it asks for the password and then i am able to start the database in nomount mode but it keeps failing to create the database.


Can anyone give me an idea when it doesn't work.


SQL Code :

/*
#----------------------------------------------------------
# Program : build_PROD.sql (Win 2000)
#----------------------------------------------------------
*/

CONNECT internal/oracle

spool e:\oracle\admin\prod\prod_build.log;

STARTUP PFILE=e:\oracle\admin\pmdb\pfile\initPROD.ora NOMOUNT;

CREATE DATABASE pmdb
LOGFILE GROUP 1
('e:\oracle\oradata\prod\redo01.log') SIZE 10M,
GROUP 2
('e:\oracle\oradata\prod\redo02.log') SIZE 10M,
GROUP 3
('e:\oracle\oradata\prod\redo03.log') SIZE 10M,
GROUP 4
('e:\oracle\oradata\prod\redo04.log') SIZE 10M

MAXDATAFILES 100
CHARACTER SET WE8ISO8859P1

/*System tablespace*/
DATAFILE 'e:\oracle\oradata\prod\system01.dbf' size 250m;

spool off

Run as @build_PROD.sql


Regards
Grace

If anyone can help me to get this running

Thank you

 

[alexhu]

1. Read the install manual - it includes all the steps required and an example

2. You have no undo tablespace in your statement

3. You have no temporary tablespace in your statement

Alex

 

[SantaMufasa]

Grace,

Can you please paste here the precise error message you are receiving? Also, have you confirmed that your Windows login appears as an "ORA_DBA" group member on that machine?

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:25 (05Feb04) GMT, 11:25 (05Feb04) Mountain Time)

 

[gracew]

Hi

Thanks for getting back to me here is the error message
below :

CREATE DATABASE prod
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced

Thnaks
Grace

 

[alexhu]

Look in the alert.log - it will tell you what you are doing wrong - located in $ORACLE_HOME/rdbms/log

Alex

 

[gracew]

Thanks for this.

Here is the error message :

SQL> @D:\oracle\admin\PROD\create\build_prod.sql
CREATE DATABASE prod
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced

Here is the alert log error :


Fri Feb 06 11:30:52 2004
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
SCN scheme 2
Using log_archive_dest parameter default value
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 9.2.0.1.0.
System parameters with non-default values:
processes = 150
timed_statistics = TRUE
shared_pool_size = 50331648
large_pool_size = 8388608
java_pool_size = 33554432
control_files = D:\oracle\oradata\PROD\CONTROL01.CTL, D:\oracle\oradata\PROD\CONTROL02.CTL, D:\oracle\oradata\PROD\CONTROL03.CTL
db_block_size = 8192
db_cache_size = 16777216
compatible = 9.2.0.0.0
db_file_multiblock_read_count= 32
fast_start_mttr_target = 300
undo_management = AUTO
undo_tablespace = UNDOTBS1
undo_retention = 10800
remote_login_passwordfile= EXCLUSIVE
db_domain =
instance_name = PROD
dispatchers = (PROTOCOL=TCP) (SERVICE=PRODXDB)
job_queue_processes = 10
hash_join_enabled = TRUE
hash_area_size = 1048576
background_dump_dest = D:\oracle\admin\PROD\bdump
user_dump_dest = D:\oracle\admin\PROD\udump
core_dump_dest = D:\oracle\admin\PROD\cdump
sort_area_size = 1048576
db_name = PROD
open_cursors = 300
star_transformation_enabled= TRUE
query_rewrite_enabled = TRUE
pga_aggregate_target = 33554432
aq_tm_processes = 1
PMON started with pid=2
DBW0 started with pid=3
LGWR started with pid=4
CKPT started with pid=5
SMON started with pid=6
RECO started with pid=7
CJQ0 started with pid=8
QMN0 started with pid=9
Fri Feb 06 11:30:55 2004
starting up 1 shared server(s) ...
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Fri Feb 06 11:33:13 2004
CREATE DATABASE prod
LOGFILE
GROUP 1 ('D:\oracle\oradata\PROD\redo01a.log','D:\oracle\oradata\PROD\redo01b.log') SIZE 10M,
GROUP 2 ('D:\oracle\oradata\PROD\redo02a.log','D:\oracle\oradata\PROD\redo02b.log') SIZE 10M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 25
MAXINSTANCES 1
DATAFILE 'D:\oracle\oradata\PROD\system01.dbf'
SIZE 125M AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE 'D:\oracle\oradata\PROD\temp01.dbf'
SIZE 5M
UNDO TABLESPACE undo01
DATAFILE 'D:\oracle\oradata\PROD\undo01.dbf'
SIZE 200M AUTOEXTEND ON NEXT 64K MAXSIZE 400M
Fri Feb 06 11:33:13 2004
Database mounted in Exclusive Mode.
Fri Feb 06 11:33:16 2004
Successful mount of redo thread 1, with mount id 4224153849.
Assigning activation ID 4224153849 (0xfbc778f9)
Thread 1 opened at log sequence 1
Current log# 1 seq# 1 mem# 0: D:\ORACLE\ORADATA\PROD\REDO01A.LOG
Current log# 1 seq# 1 mem# 1: D:\ORACLE\ORADATA\PROD\REDO01B.LOG
Successful open of redo thread 1.
Fri Feb 06 11:33:17 2004
SMON: enabling cache recovery
Fri Feb 06 11:33:17 2004
WARNING: Default passwords for SYS and SYSTEM will be used.
Please change the passwords.
Fri Feb 06 11:33:17 2004
create tablespace SYSTEM datafile 'D:\oracle\oradata\PROD\system01.dbf'
SIZE 125M AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED
default storage (initial 10K next 10K) EXTENT MANAGEMENT DICTIONARY online

Completed: create tablespace SYSTEM datafile 'D:\oracle\orad
Fri Feb 06 11:33:24 2004
create rollback segment SYSTEM tablespace SYSTEM
storage (initial 50K next 50K)

Completed: create rollback segment SYSTEM tablespace SYSTEM

Fri Feb 06 11:33:29 2004
CREATE UNDO TABLESPACE UNDO01 DATAFILE 'D:\oracle\oradata\PROD\undo01.dbf'
SIZE 200M AUTOEXTEND ON NEXT 64K MAXSIZE 400M

Fri Feb 06 11:33:41 2004
Created Undo Segment _SYSSMU1$
Created Undo Segment _SYSSMU2$
Created Undo Segment _SYSSMU3$
Created Undo Segment _SYSSMU4$
Created Undo Segment _SYSSMU5$
Created Undo Segment _SYSSMU6$
Created Undo Segment _SYSSMU7$
Created Undo Segment _SYSSMU8$
Created Undo Segment _SYSSMU9$
Created Undo Segment _SYSSMU10$
ORA-30012 signalled during: CREATE UNDO TABLESPACE UNDO01 DATAFILE 'D:\oracle...
Fri Feb 06 11:33:41 2004
Errors in file d:\oracle\admin\prod\udump\prod_ora_2492.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-30012: undo tablespace 'UNDOTBS1' does not exist or of wrong type

Fri Feb 06 11:33:41 2004
Errors in file d:\oracle\admin\prod\udump\prod_ora_2492.trc:
ORA-01501: CREATE DATABASE failed
ORA-01519: error while processing file '%ORACLE_HOME%\RDBMS\ADMIN\SQL.BSQ' near line 5161
ORA-00604: error occurred at recursive SQL level 1
ORA-30012: undo tablespace 'UNDOTBS1' does not exist or of wrong type

Error 1519 happened during db open, shutting down database
USER: terminating instance due to error 1519
Fri Feb 06 11:33:41 2004
Errors in file d:\oracle\admin\prod\bdump\prod_lgwr_1080.trc:
ORA-01519: error while processing file '' near line

Instance terminated by USER, pid = 2492
ORA-1092 signalled during: CREATE DATABASE prod
LOGFILE
GROUP 1 ('D:\orac...


Install steps :

1. set ORACLE_SID=prod
2. sqlplus /nolog
3. SQL> connect sys as sysdba
4. Enter password:
Connected to an idle instance.
5. SQL> startup pfile=D:\oracle\admin\PROD\pfile\initPROD.ora nomount;
ORACLE instance started.

Total System Global Area 126950220 bytes
Fixed Size 453452 bytes
Variable Size 109051904 bytes
Database Buffers 16777216 bytes
Redo Buffers 667648 bytes

6. SQL> @D:\oracle\admin\PROD\create\build_prod.sql

this then produce the following error

CREATE DATABASE prod
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced

Create database script :

spool D:\oracle\admin\prod\prod_build.log;

CREATE DATABASE prod
LOGFILE
GROUP 1 ('D:\oracle\oradata\PROD\redo01a.log','D:\oracle\oradata\PROD\redo01b.log') SIZE 10M,
GROUP 2 ('D:\oracle\oradata\PROD\redo02a.log','D:\oracle\oradata\PROD\redo02b.log') SIZE 10M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 25
MAXINSTANCES 1
DATAFILE 'D:\oracle\oradata\PROD\system01.dbf'
SIZE 125M AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE 'D:\oracle\oradata\PROD\temp01.dbf'
SIZE 5M
UNDO TABLESPACE undo01
DATAFILE 'D:\oracle\oradata\PROD\undo01.dbf'
SIZE 200M AUTOEXTEND ON NEXT 64K MAXSIZE 400M;

spool off

In d:\oracle\oradata\PROD it has created the 3 control files, redo logs of 10M ,system and undo

Can you please advise

Thanks Grace

 

[alexhu]

You have created the undo tablespace as 'undo1'

UNDO TABLESPACE undo01

but Oracle is looking for UNDOTBS1

undo tablespace 'UNDOTBS1' does not exist or of wrong type


I don't know why this should be, unless you have the parameter in your init.ora file ?

A typo perhaps ?

Alex

 

[gracew]

Hi

Appended script to read as followed :

spool D:\oracle\admin\prod\prod_build.log;

CREATE DATABASE prod
LOGFILE
GROUP 1 ('D:\oracle\oradata\PROD\redo01a.log','D:\oracle\oradata\PROD\redo01b.log') SIZE 10M,
GROUP 2 ('D:\oracle\oradata\PROD\redo02a.log','D:\oracle\oradata\PROD\redo02b.log') SIZE 10M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 25
MAXINSTANCES 1
DATAFILE 'D:\oracle\oradata\PROD\system01.dbf'
SIZE 250M AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE 'D:\oracle\oradata\PROD\temp01.dbf'
SIZE 50M;

spool off


But still get the following message :

SQL> @D:\oracle\admin\PROD\create\build_prod.sql
CREATE DATABASE prod
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced

I don't understand why it keeps termianting the instance.

Grace

 

[alexhu]

You must have undo in the init.ora - show us the init.ora file please

Alex

 

[alexhu]

whats at the end of the alert.log now?

Alex