Home » RDBMS Server » Server Administration » ORA 01092 during database creation. (Win2K3, 10.2.0.3)
ORA 01092 during database creation. [message #379352] Tue, 06 January 2009 03:08 Go to next message
abdulaziz
Messages: 102
Registered: May 2008
Location: Douala
Senior Member
Hello,

I am having the ORA-01092 while trying to create an OMF database from scratch.
Here's what happens after I run the create database command:
SQL> conn / as sysdba
ConnectÚ Ó une instance inactive.
SQL> startup nomount pfile='c:\pfilesample.ora'
Instance ORACLE lancÚe.

Total System Global Area  113246208 bytes
Fixed Size                  1247588 bytes
Variable Size              58721948 bytes
Database Buffers           50331648 bytes
Redo Buffers                2945024 bytes
SQL> create database sample;
create database sample
*
ERREUR Ó la ligne 1 :
ORA-01092: instance ORACLE interrompue. DÚconnexion imposÚe


below is the pfile I used for my database:
db_name=sample
db_create_file_dest='D:\oracle\product\10.2.0\oradata'
log_archive_dest_1='location=D:\oracle\sample\archives'
log_archive_dest_2='location=use_db_recovery_file_dest'
db_recovery_file_dest='D:\oracle\product\10.2.0/flash_recovery_area'
db_recovery_file_dest_size=2147483648


I thought of having a look at the alert log file but ...none has been created.

Can anyone guide me?

Thanks in advance.
Re: ORA 01092 during database creation. [message #379355 is a reply to message #379352] Tue, 06 January 2009 03:20 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Where did you look for the alert.log file? It may be in your %ORACLE_HOME%/RDMBS/trace directory.
Re: ORA 01092 during database creation. [message #379356 is a reply to message #379352] Tue, 06 January 2009 03:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think you have not see alert.log in the correct directory as it is created at your first statement.

In addition, as far as I know, ORACLE_SID is limited to 4 characters in Windows.

Regards
Michel
Re: ORA 01092 during database creation. [message #379375 is a reply to message #379355] Tue, 06 January 2009 04:25 Go to previous messageGo to next message
abdulaziz
Messages: 102
Registered: May 2008
Location: Douala
Senior Member
Frank Naude
Where did you look for the alert.log file? It may be in your %ORACLE_HOME%/RDMBS/trace directory.



Uhmmm... I was looking in the bdump. But I found it where you specified, and below is the content of the alert_log file.
Starting up ORACLE RDBMS Version: 10.2.0.3.0.
System parameters with non-default values:
  log_archive_dest_1       = location=D:\oracle\sample\archives
  log_archive_dest_2       = location=use_db_recovery_file_dest
  db_create_file_dest      = D:\oracle\product\10.2.0\oradata
  db_recovery_file_dest    = D:\oracle\product\10.2.0/flash_recovery_area
  db_recovery_file_dest_size= 2147483648
  db_name                  = sample
PMON started with pid=2, OS id=3384
PSP0 started with pid=3, OS id=2988
MMAN started with pid=4, OS id=2272
DBW0 started with pid=5, OS id=3700
LGWR started with pid=6, OS id=3088
CKPT started with pid=7, OS id=448
SMON started with pid=8, OS id=1616
RECO started with pid=9, OS id=2820
MMON started with pid=10, OS id=3772
MMNL started with pid=11, OS id=1676
Mon Jan 05 15:17:54 2009
create database
Mon Jan 05 15:17:54 2009
WARNING: Default Temporary Tablespace not specified in CREATE DATABASE command
Default Temporary Tablespace will be necessary for a locally managed database in future release
Mon Jan 05 15:17:56 2009
Database mounted in Exclusive Mode
db_recovery_file_dest_size of 2048 MB is 5.31% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Mon Jan 05 15:18:17 2009
Successful mount of redo thread 1, with mount id 3725679586
Assigning activation ID 3725679586 (0xde115be2)
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: D:\ORACLE\PRODUCT\10.2.0\ORADATA\SAMPLE\ONLINELOG\O1_MF_1_4P45NO0H_.LOG
  Current log# 1 seq# 1 mem# 1: D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\SAMPLE\ONLINELOG\O1_MF_1_4P45NS3X_.LOG
Successful open of redo thread 1
Mon Jan 05 15:18:18 2009
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Mon Jan 05 15:18:18 2009
SMON: enabling cache recovery
Mon Jan 05 15:18:18 2009
create tablespace SYSTEM datafile /* OMF datafile */ 
  default storage (initial 10K next 10K) EXTENT MANAGEMENT DICTIONARY online

Mon Jan 05 15:18:23 2009
Completed: create tablespace SYSTEM datafile /* OMF datafile */ 
  default storage (initial 10K next 10K) EXTENT MANAGEMENT DICTIONARY online
Mon Jan 05 15:18:23 2009
create rollback segment SYSTEM tablespace SYSTEM
  storage (initial 50K next 50K)

Completed: create rollback segment SYSTEM tablespace SYSTEM
  storage (initial 50K next 50K)
Mon Jan 05 15:18:28 2009
[COLOR=orangered]Errors in file d:\oracle\product\10.2.0\db_1\rdbms\trace\m4prod_ora_364.trc:
ORA-00604: une erreur s'est produite au niveau SQL recursif 1
ORA-04031: impossible d'affecter 2208 octets de memoire partagee ("shared pool","unknown object","KGLS heap","KGLS MEM BLOCK")[/COLOR]
Mon Jan 05 15:18:28 2009
Errors in file d:\oracle\product\10.2.0\db_1\rdbms\trace\m4prod_ora_364.trc:
ORA-01501: echec de CREATE DATABASE
ORA-01519: erreur pendant traitement de fichier '%ORACLE_HOME%\RDBMS\ADMIN\SQL.BSQ' pres de la ligne 1393
ORA-00604: une erreur s'est produite au niveau SQL recursif 1
ORA-04031: impossible d'affecter 2208 octets de memoire partagee ("shared pool","unknown object","KGLS heap","KGLS MEM BLOCK")

Error 1519 happened during db open, shutting down database
USER: terminating instance due to error 1519
Mon Jan 05 15:18:29 2009
Errors in file d:\oracle\product\10.2.0\db_1\rdbms\trace\m4prod_lgwr_3088.trc:
ORA-01519: error while processing file '' near line 

Mon Jan 05 15:18:30 2009
Errors in file d:\oracle\product\10.2.0\db_1\rdbms\trace\m4prod_pmon_3384.trc:
ORA-01519: error while processing file '' near line 

Mon Jan 05 15:18:30 2009
Errors in file d:\oracle\product\10.2.0\db_1\rdbms\trace\m4prod_mman_2272.trc:
ORA-01519: error while processing file '' near line 

Mon Jan 05 15:18:30 2009
Errors in file d:\oracle\product\10.2.0\db_1\rdbms\trace\m4prod_dbw0_3700.trc:
ORA-01519: error while processing file '' near line 

Mon Jan 05 15:18:30 2009
Errors in file d:\oracle\product\10.2.0\db_1\rdbms\trace\m4prod_ckpt_448.trc:
ORA-01519: error while processing file '' near line 

Mon Jan 05 15:18:31 2009
Errors in file d:\oracle\product\10.2.0\db_1\rdbms\trace\m4prod_reco_2820.trc:
ORA-01519: error while processing file '' near line 

Mon Jan 05 15:18:31 2009
Errors in file d:\oracle\product\10.2.0\db_1\rdbms\trace\m4prod_smon_1616.trc:
ORA-01519: error while processing file '' near line 

Mon Jan 05 15:18:32 2009
Errors in file d:\oracle\product\10.2.0\db_1\rdbms\trace\m4prod_psp0_2988.trc:
ORA-01519: error while processing file '' near line 

Instance terminated by USER, pid = 364
ORA-1092 signalled during: create database...

I could see in the alert log,the first statement that returned an error (in red color). I had a look on that trace file but didn't understand anything. However from the error message, it appears that the 2208kb of memory could not be allocated(ORA-04031)I mention that I have 1GB of RAM. I don't understand the other error, ORA 00604. Any tips?

I would also like to precise what I did before issuing the create database command: As there was already a database in my machine, I shut it down immediate, then started an instance in nomount with the pfile pointing to the file having the parameters for the database I wanted to create. Dunno whether I did the right thing, if not please tell me how I should have proceeded.

Re: ORA 01092 during database creation. [message #379379 is a reply to message #379375] Tue, 06 January 2009 04:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As you didn't specify any memory parameter, Oracle allocates one granule (I think 16MB) for each area.
Specify "sga_target" parameter.

Regards
Michel
Re: ORA 01092 during database creation. [message #379383 is a reply to message #379379] Tue, 06 January 2009 04:39 Go to previous messageGo to next message
abdulaziz
Messages: 102
Registered: May 2008
Location: Douala
Senior Member
What value should I give to that parameter?? 2208 bytes Embarassed ?
Re: ORA 01092 during database creation. [message #379387 is a reply to message #379383] Tue, 06 January 2009 04:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, set it following free RAM you have on your server.
If you have no other application, you can try to start with 300MB.

Regards
Michel
Re: ORA 01092 during database creation. [message #379594 is a reply to message #379387] Wed, 07 January 2009 04:11 Go to previous messageGo to next message
abdulaziz
Messages: 102
Registered: May 2008
Location: Douala
Senior Member

I tried your thing Michel and I worked...what I mean is, I got the 'database created' message. Now am going to run the catproc and the catalog scripts, then try to connect to that database. If I again get an error, I'll report it here.

Thanks again.
Re: ORA 01092 during database creation. [message #379595 is a reply to message #379594] Wed, 07 January 2009 04:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback, it is appreciated.

Regards
Michel
Re: ORA 01092 during database creation. [message #379653 is a reply to message #379595] Wed, 07 January 2009 08:15 Go to previous messageGo to next message
abdulaziz
Messages: 102
Registered: May 2008
Location: Douala
Senior Member
uhmm...I am confronted to a new problem now. The scripts catalog and catproc executed normaly and I could connect to my new database. The problem I am having now is that, that database uses the service name of another database. I explain:

Before I create the database 'sample' from scratch, I had a database that I created using the dbca named 'm4prod'. So when I started the creation of 'sample', as I needed to connect as sysdba, I first connected to 'm4prod' then I shut it down immediate. Then I issued a startup nomount, with the pfile sets to the pfile of the database I was about to create, that is 'sample'. The startup nomount command was executed successfully and I entered the 'create database sample' command. That is where I was having the ora 01902. I got a solution to fix that from Michel that worked. I ran the scripts I had to run and the database is ok. The service name of the database 'm4prod' is 'm4prod', but when I set oracle_sid=m4prod, it connects me to 'sample' database. I now it because when I enter
select name from v$database
, the result is 'sample'.

This is what I tried to fix the problem:

-I created a new service name called 'sample' through net manager. I performed a connection test that was successful.
-Then I deleted the existing service m4prod, to create a new one making sure to set the sid to 'm4prod', which is the database I would like to connect to.
- When I performed a connection test via a user which I know does exit, I got the error 0107.

Just to make sur the new database was 'seen', I opened the dbca and chose the "delete database" option. Only the 'm4prod' database was displayed. 'sample' database wasn't in the list of databases. Is it a normal behaviour? How can I fix that? What can I do to get a connection to 'm4prod'?

Thanks in advance?
Re: ORA 01092 during database creation. [message #379933 is a reply to message #379653] Thu, 08 January 2009 08:56 Go to previous message
abdulaziz
Messages: 102
Registered: May 2008
Location: Douala
Senior Member

I finaly understood why I couldn't connect to my first database: I hadn't created a service through the "oradim" command.

Thanks for your assistance.
Previous Topic: Update table containing millions of records
Next Topic: Oracle 9i release
Goto Forum:
  


Current Time: Sat Dec 03 03:43:10 CST 2016

Total time taken to generate the page: 0.12047 seconds