Home » RDBMS Server » Server Administration » Error while trying to create a database manually
Error while trying to create a database manually [message #289682] Tue, 25 December 2007 05:53 Go to next message
Muddassar
Messages: 28
Registered: March 2007
Junior Member
Hi,
I am trying to create a database manually. My requirement is to use the already existing controlfiles. So here i go showing the steps which i took.
1. I shutdown the database
2. I connect to sqlplus with sqlplus/nolog
3. Connect as sysdba with the command: sys/password as sysdba.
I get connected to an idle instance.
4. I start the instance with my pfile as:
startup nomount pfile='<My Location of pfile>'
The instance gets started. I am showing the pfile contents:

db_block_size=4096
db_cache_size=33554432

open_cursors=300

background_dump_dest=D:\oracle\admin\myDB\bdump
core_dump_dest=D:\oracle\admin\myDB\cdump
timed_statistics=TRUE
user_dump_dest=D:\oracle\admin\myDB\udump

db_domain=""
remote_login_passwordfile=EXCLUSIVE

control_files=("D:\oracle\oradata\myDB\CONTROL01.CTL", "D:\oracle\oradata\myDB\CONTROL02.CTL", "D:\oracle\oradata\myDB\CONTROL03.CTL")

dispatchers="(PROTOCOL=TCP)(SER=MODOSE)", "(PROTOCOL=TCP)(PRE=oracle.aurora.server.GiopServer)", "(PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer)"

compatible=9.0.0
db_name=myDB

instance_name=myDB

java_pool_size=33554432
large_pool_size=1048576
shared_pool_size=33554432

processes=150

fast_start_mttr_target=300

sort_area_size=524288

undo_management=AUTO
undo_tablespace=UNDOTBS

I accordingly create the directories beforehand as i have mentioned in the parameters(control_files,background_dump_dest etc)

The instance gets started.

5. Then i issue the CREATE DATABASE command as follows:

CREATE DATABASE myDB controlfile reuse
logfile
GROUP 1 ('D:\oracle\oradata\myDB\log_01_myDB.rdo') SIZE 15M,
GROUP 2 ('D:\oracle\oradata\myDB\log_02_myDB.rdo') SIZE 15M,
GROUP 3 ('D:\oracle\oradata\myDB\log_03_myDB.rdo') SIZE 15M
datafile 'D:\oracle\oradata\myDB\system_01_myDB.dbf' SIZE 100M
undo tablespace UNDO
datafile 'D:\oracle\oradata\myDB\undo_01_myDB.dbf' SIZE 40M
default temporary tablespace TEMP
tempfile 'D:\oracle\oradata\myDB\temp_01_myDB.dbf' SIZE 20M
extent management local uniform size 128k
character set AL32UTF8
national character set AL16UTF16
set time_zone = 'America/New_York';

The command runs for sometime and then throws the following error:

create database myDB controlfile reuse
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced

I noticed that all the controlfiles, logfiles and datafiles are created in the location, i specified.

I tried to google for the error above and found that its a generic error which can occur for a lot number of reasons. How do i understand for which reason i am getting the error. Any help will be appreciated.

Re: Error while trying to create a database manually [message #289685 is a reply to message #289682] Tue, 25 December 2007 06:17 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Correct your undo_tablespace parameter.
In your create statement undo tablespace name is "UNDO" and in your parameter file it is "UNDOTBS".

Re: Error while trying to create a database manually [message #290142 is a reply to message #289685] Thu, 27 December 2007 12:24 Go to previous messageGo to next message
Muddassar
Messages: 28
Registered: March 2007
Junior Member
Thanks for pointing out the error. I have corrected the same but i am still getting the same error. , i.e
create database myDB controlfile reuse
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced

What can be the error? Am i missing something out here?
(I still notice that all the controlfiles, logfiles and datafiles are created in the location, i specified.)

[Updated on: Thu, 27 December 2007 12:28]

Report message to a moderator

Re: Error while trying to create a database manually [message #290144 is a reply to message #290142] Thu, 27 December 2007 12:45 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Post Last 50-100 lines from alert log file.
Re: Error while trying to create a database manually [message #290166 is a reply to message #289682] Thu, 27 December 2007 21:07 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Note:332476.1 on Metalink
Re: Error while trying to create a database manually [message #290366 is a reply to message #290144] Fri, 28 December 2007 10:07 Go to previous messageGo to next message
Muddassar
Messages: 28
Registered: March 2007
Junior Member
Ok, I now looked into the alert log file and found some errors being mentioned. I am posting some lines logged into the alert log file where the last few lines shows the error:

Fri Dec 28 21:29:28 2007
Database mounted in Exclusive Mode.
Fri Dec 28 21:29:30 2007
Successful mount of redo thread 1, with mount id 2557555127.
Assigning activation ID 2557555127 (0x987131b7)
Thread 1 opened at log sequence 1
Current log# 1 seq# 1 mem# 0: D:\ORACLE\ORADATA\MYDB\LOG_01_MYDB.RDO
Successful open of redo thread 1.
Fri Dec 28 21:29:30 2007
SMON: enabling cache recovery
Fri Dec 28 21:29:30 2007
create tablespace SYSTEM datafile 'D:\oracle\oradata\myDB\system_01_myDB.dbf' SIZE 100M
default storage (initial 10K next 10K) EXTENT MANAGEMENT DICTIONARY online

Fri Dec 28 21:29:33 2007
Completed: create tablespace SYSTEM datafile 'D:\oracle\orad
Fri Dec 28 21:29:33 2007
create rollback segment SYSTEM tablespace SYSTEM
storage (initial 50K next 50K)

Completed: create rollback segment SYSTEM tablespace SYSTEM

Fri Dec 28 21:29:38 2007
CREATE UNDO TABLESPACE UNDOTBS DATAFILE 'D:\oracle\oradata\myDB\undo_01_myDB.dbf' SIZE 40M

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$
Undo Segment 1 Onlined
Undo Segment 2 Onlined
Undo Segment 3 Onlined
Undo Segment 4 Onlined
Undo Segment 5 Onlined
Undo Segment 6 Onlined
Undo Segment 7 Onlined
Undo Segment 8 Onlined
Undo Segment 9 Onlined
Undo Segment 10 Onlined
Successfully onlined Undo Tablespace 1.
Completed: CREATE UNDO TABLESPACE UNDOTBS DATAFILE 'D:\oracl
Fri Dec 28 21:29:40 2007
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE 'D:\oracle\oradata\myDB\temp_01_myDB.dbf' SIZE 20M UNIFORM SIZE 131072

Completed: CREATE TEMPORARY TABLESPACE TEMP TEMPFILE 'D:\ora
Fri Dec 28 21:29:40 2007
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP

Completed: ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP
Fri Dec 28 21:29:41 2007
SMON: enabling tx recovery
Fri Dec 28 21:29:41 2007
Errors in file D:\oracle\admin\myDB\udump\ORA00928.TRC:
ORA-01501: CREATE DATABASE failed
ORA-01991: invalid password file 'D:\oracle\ora90\DATABASE\PWDtest.ORA'

Fri Dec 28 21:29:41 2007
Error 1991 happened during db open, shutting down database
USER: terminating instance due to error 1991
Instance terminated by USER, pid = 928
ORA-1092 signalled during: create database myDB controlfile reuse
logfile
GRO...


Its showing an invalid password file. Why is it invalid? What can i do to make it valid as it is only a binary file. Any help would be highly appreciable.

[Updated on: Fri, 28 December 2007 10:12]

Report message to a moderator

Re: Error while trying to create a database manually [message #290370 is a reply to message #290366] Fri, 28 December 2007 10:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use REMOTE_LOGIN_PASSWORDFILE=NONE during database creation then create a password file.

Regards
Michel
Re: Error while trying to create a database manually [message #290485 is a reply to message #290370] Sat, 29 December 2007 02:18 Go to previous messageGo to next message
Muddassar
Messages: 28
Registered: March 2007
Junior Member
Thank you to all of you. I finally got my database created as i can see a final output of 'Database Created'. Smile
After this i exited from sqlplus. Now i try to connect to my newly created database (myDB) as system/manager@myDB. But i get the following error:
ERROR:
ORA-12154: TNS:could not resolve service name
I could understand that the tnsnames.ora file does not contains the entry for my new instance. So, i added an entry for the same as:

MYDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = india)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = myDB)
)
)

I did this as looking into the entry for my already working instance 'test'.
I try to connect again, but am getting the same error. Any suggestions/comments would be appreciated.

[Updated on: Sat, 29 December 2007 02:19]

Report message to a moderator

Re: Error while trying to create a database manually [message #290488 is a reply to message #290485] Sat, 29 December 2007 02:23 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Give us what you did so your database created successfully just want your feedback.

ORA-12154: TNS:could not resolve service name
tnsping "MYDB" post here what you see ?
Re: Error while trying to create a database manually [message #290496 is a reply to message #290485] Sat, 29 December 2007 02:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is now a new question, start a new topic, in the correct forum (networking).

Regards
Michel

[Updated on: Sat, 29 December 2007 02:46]

Report message to a moderator

Re: Error while trying to create a database manually [message #290522 is a reply to message #290488] Sat, 29 December 2007 08:04 Go to previous messageGo to next message
Muddassar
Messages: 28
Registered: March 2007
Junior Member
I was making two mistakes which was pointed out in the forum. I rectified them and was successfull in creating the database.
The mistakes were:
1. The names for the UNDO tablespace were different in my parameter file (initmyDB.ora) and in the CREATE DATABASE statement.
2. The parameter REMOTE_LOGIN_PASSWORDFILE was set to EXCLUSIVE in the parameter file. I was adviced to change it to NONE during database creation.
Rectifying these mistakes worked for me.

Now, i tried to do a tnsping to MYDB, i.e tnsping MYDB and i get the following output:

TNS Ping Utility for 32-bit Windows: Version 9.0.1.1.1 - Production on 29-DEC-20
07 19:26:09

Copyright (c) 1997 Oracle Corporation. All rights reserved.

Used parameter files:
D:\oracle\ora90\network\admin\sqlnet.ora
D:\oracle\ora90\network\admin\tnsnames.ora

TNS-03505: Failed to resolve name
Re: Error while trying to create a database manually [message #290523 is a reply to message #290522] Sat, 29 December 2007 08:07 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Please create new topic at networking forms then we will continue, thanks for feedback.
Re: Error while trying to create a database manually [message #290526 is a reply to message #290523] Sat, 29 December 2007 08:22 Go to previous message
Muddassar
Messages: 28
Registered: March 2007
Junior Member
I have created a new topic in the forum Networking and Gateways with the topic name:

ORA-12154: TNS:could not resolve service name when trying to connect to a newly created database

Thanks
Previous Topic: Dedicated server VS Shared Server
Next Topic: Incomplete but installation had reported succesful
Goto Forum:
  


Current Time: Sat Dec 03 05:37:24 CST 2016

Total time taken to generate the page: 0.10398 seconds