Solved

Trying to run a script to create the first database after installing Oracle and receiving errors...

Posted on 2008-10-14
19
1,940 Views
Last Modified: 2013-12-19
Trying to run a script to manually create the first database after installing Oracle and receiving errors...

My steps to reproduce are:

Log on to server as oracle and enter password
cd to directory where script is located
type "sqlplus /nolog"
type "connect /as sysdba;"
type "@crdbADCORDEV.sql" (the script name)
I get a prompt for a password (which password are they asking for?)

I get a long list of errors like below:

SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0641: "SHOW ERRORS" requires connection to server
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0641: "SHOW ERRORS" requires connection to server
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0641: "SHOW ERRORS" requires connection to server
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0641: "PRINT" requires connection to server
SP2-0640: Not connected
SP2-0641: "PRINT" requires connection to server
SP2-0640: Not connected
Enter value for owa_file_var:

0
Comment
Question by:la_colibri
  • 8
  • 7
  • 3
  • +1
19 Comments
 
LVL 68

Expert Comment

by:woolmilkporc
Comment Utility
Does the connect / as sysdba return an error?
Please post the first lines of your script so we can see what it is attempting to do.
I think we will find something like a new 'connect' statement there.
0
 

Author Comment

by:la_colibri
Comment Utility
No error when connecting as sysdba except stating it's an idle instance...

sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Tue Oct 14 10:21:19 2008

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> connect /as sysdba;
Connected to an idle instance.
SQL>

-------------------------------------------------------
Script I am trying to run starts out as...

spool crdbADCORDEV.lst
#connect internal
#startup nomount pfile=/r00/app/oracle/admin/ADCORDEV/pfile/initADCORDEV.ora
/* please verify/change the following parameters as needed */
CREATE DATABASE "ADCORDEV"
 NOARCHIVELOG
 /* using actual control file values */
 MAXLOGFILES 50
 MAXLOGMEMBERS 2
 MAXDATAFILES 50
 MAXINSTANCES 1
 MAXLOGHISTORY 400
 DATAFILE '/r00/oradata/ADCORDEV/ADCORDEVsystem01.dbf' SIZE 150M
 LOGFILE
 GROUP 1 ('/r83/oradata/ADCORDEV/ADCORDEVredo01a.log' ) SIZE 90M,
 GROUP 2 ('/r83/oradata/ADCORDEV/ADCORDEVredo02a.log' ) SIZE 90M,
 GROUP 3 ('/r83/oradata/ADCORDEV/ADCORDEVredo03a.log' ) SIZE 90M,
 GROUP 4 ('/r83/oradata/ADCORDEV/ADCORDEVredo04a.log' ) SIZE 90M,
 GROUP 5 ('/r83/oradata/ADCORDEV/ADCORDEVredo05a.log' ) SIZE 90M,
 GROUP 6 ('/r83/oradata/ADCORDEV/ADCORDEVredo06a.log' ) SIZE 90M,
 GROUP 7 ('/r83/oradata/ADCORDEV/ADCORDEVredo07a.log' ) SIZE 90M;
;
rem ----------------------------------------
rem
rem Need a basic rollback segment before proceeding
rem
0
 
LVL 23

Expert Comment

by:David
Comment Utility
May not be the issue, but are your ORACLE_HOME and ORACLE_SID defined before you start the database?
0
 
LVL 10

Expert Comment

by:dfke
Comment Utility
It means the client side tool SQL *PLUS is not connected to the server or was disconnected because of server restart or network disconnect. Try to use the alias in tnsnames.ora.

The TNSNAMES.ORA files is located on both client and server systems. If you make configuration changes on the server ($ORACLE_HOME/network/admin/tnsnames.ora) ensure you can connect to the database through the listener if you are logged on to the server. If you make configuration changed on the client (c:\oracle\product\9.x\db_1\network\admin\tnsnames.ora) ensure you can connect from your client workstation to the database through the listener running on the server.


sqlplus sysdba/<sysdba_password>@<alias in tnsnames.ora>
0
 
LVL 68

Expert Comment

by:woolmilkporc
Comment Utility
You'll have to change your '#connect internal' to '#connect / as sysdba',
because 'connect internal' doesn't work anymore. 'internal' is now treated as normal username, and if this user doesnt exist and/or you supply an empty/wrong password, you get disconnected and the above errors occur.
0
 

Author Comment

by:la_colibri
Comment Utility
I changed '#connect internal' to '#connect / as sysdba' and received the errors below. Could this have to do with the listener.ora file? Do I have to create this myself after I install Oracle? This is not a client to server connection so believe the tnsnames.ora file is not relevant but please correct me if I am wrong.

CREATE OR REPLACE PACKAGE dbms_fga AS
*
ERROR at line 1:
ORA-01034: ORACLE not available


CREATE OR REPLACE PUBLIC SYNONYM dbms_fga FOR sys.dbms_fga
*
ERROR at line 1:
ORA-01034: ORACLE not available


GRANT EXECUTE ON sys.dbms_fga TO execute_catalog_role
*
ERROR at line 1:
ORA-01034: ORACLE not available


CREATE OR REPLACE LIBRARY dbms_fga_lib wrapped
*
ERROR at line 1:
ORA-01034: ORACLE not available


CREATE OR REPLACE PACKAGE BODY dbms_fga wrapped
*
ERROR at line 1:
ORA-01034: ORACLE not available


CREATE OR REPLACE PACKAGE sys.dbms_logmnr_session as
*
ERROR at line 1:
ORA-01034: ORACLE not available


No errors.
grant execute on dbms_logmnr_session to execute_catalog_role
*
ERROR at line 1:
ORA-01034: ORACLE not available


CREATE OR REPLACE PACKAGE BODY sys.dbms_logmnr_session wrapped
*
ERROR at line 1:
ORA-01034: ORACLE not available


No errors.
CREATE OR REPLACE LIBRARY sys.dbms_logstdby_lib wrapped
*
ERROR at line 1:
ORA-01034: ORACLE not available


CREATE OR REPLACE PACKAGE BODY sys.dbms_logstdby wrapped
*
ERROR at line 1:
ORA-01034: ORACLE not available


No errors.
CREATE OR REPLACE PACKAGE BODY sys.dbms_internal_logstdby wrapped
*
ERROR at line 1:
ORA-01034: ORACLE not available


No errors.
grant select on dba_logstdby_progress to select_catalog_role
*
ERROR at line 1:
ORA-01034: ORACLE not available


grant select on dba_logstdby_log to select_catalog_role
*
ERROR at line 1:
ORA-01034: ORACLE not available


CREATE OR REPLACE PACKAGE dbms_type_utility AS
*
ERROR at line 1:
ORA-01034: ORACLE not available


CREATE OR REPLACE PUBLIC SYNONYM DBMS_TYPE_UTILITY FOR DBMS_TYPE_UTILITY
*
ERROR at line 1:
ORA-01034: ORACLE not available


CREATE OR REPLACE PACKAGE BODY dbms_type_utility wrapped
*
ERROR at line 1:
ORA-01034: ORACLE not available


No errors.
drop package sys.wpiutl
*
ERROR at line 1:
ORA-01034: ORACLE not available


create or replace package sys.wpiutl as
*
ERROR at line 1:
ORA-01034: ORACLE not available


No errors.
create or replace package body sys.wpiutl as
*
ERROR at line 1:
ORA-01034: ORACLE not available


grant execute on sys.wpiutl to public
*
ERROR at line 1:
ORA-01034: ORACLE not available


CREATE OR REPLACE PACKAGE dbms_epgc AUTHID CURRENT_USER IS
*
ERROR at line 1:
ORA-01034: ORACLE not available


No errors.
CREATE OR REPLACE PUBLIC SYNONYM dbms_epgc FOR sys.dbms_epgc
*
ERROR at line 1:
ORA-01034: ORACLE not available


GRANT EXECUTE ON dbms_epgc TO PUBLIC
*
ERROR at line 1:
ORA-01034: ORACLE not available


DECLARE
*
ERROR at line 1:
ORA-01034: ORACLE not available


CREATE OR REPLACE PACKAGE prvt_egutl wrapped
*
ERROR at line 1:
ORA-01034: ORACLE not available


No errors.
CREATE OR REPLACE PACKAGE BODY prvt_egutl wrapped
*
ERROR at line 1:
ORA-01034: ORACLE not available


No errors.
GRANT EXECUTE ON prvt_egutl TO PUBLIC
*
ERROR at line 1:
ORA-01034: ORACLE not available


CREATE OR REPLACE PACKAGE prvt_epgc wrapped
*
ERROR at line 1:
ORA-01034: ORACLE not available


No errors.
CREATE OR REPLACE PACKAGE BODY prvt_epgc wrapped
*
ERROR at line 1:
ORA-01034: ORACLE not available


No errors.
CREATE OR REPLACE PACKAGE BODY dbms_epgc wrapped
*
ERROR at line 1:
ORA-01034: ORACLE not available


No errors.
begin :owa_file_name := 'dummy_value'; end;
*
ERROR at line 1:
ORA-01034: ORACLE not available


alter package owa_util compile
*
ERROR at line 1:
ORA-01034: ORACLE not available


alter package owa_util compile body
*
ERROR at line 1:
ORA-01034: ORACLE not available


DECLARE
*
ERROR at line 1:
ORA-01034: ORACLE not available


ERROR:
ORA-01034: ORACLE not available


ERROR:
ORA-01034: ORACLE not available


SELECT :owa_file_name FROM DUAL
*
ERROR at line 1:
ORA-01034: ORACLE not available


Enter value for owa_file_var:
0
 
LVL 68

Expert Comment

by:woolmilkporc
Comment Utility
Did you check what dvz suggested (ORACLE_HOME, ORACLE_SID)?
Perhaps your database isn't started correctliy?
Try the 'startup' command in your script by hand and correct possible errors.
When it finally works, comment it out in the script and run the rest again.
0
 

Author Comment

by:la_colibri
Comment Utility
I did check the variables ORACLE_HOME AND ORACLE_SID and they are correct so that's not the issue.

Additionally it says the following in the first few lines of the .lst file it spools to&.

Connected to an idle instance

ORA-07446: sdnfy: bad value /r00/app/oracle/admin/ADCORDEV/udump for parameter user_dump_dest

CREATE DATABASE ADCORDEV

*

Error at line 1:

ORA-01034: ORACLE not available

 
0
 
LVL 68

Expert Comment

by:woolmilkporc
Comment Utility
What are those strange characters  around the directory name? Problem of my browser?
What about the startup command?
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 23

Expert Comment

by:David
Comment Utility
I don't see in your samples how you're getting a bad UDUMP location.  If you haven't already done so, please "ls -la /r00/app/oracle/admin/ADCORDEV/*/* " to confirm what's in the parent directory.  Any errors in the logfile from your crdb step?
0
 
LVL 23

Expert Comment

by:David
Comment Utility
And no, I've never seen such delimiters before;  I just supposed it was due to your character set setting.  In they're in the script, you may have found the skunk at the party....
0
 

Author Comment

by:la_colibri
Comment Utility
This is what happens when I tried to startup...

SQL> connect /as sysdba
Connected to an idle instance.
SQL> startup nomount
ORA-07446: sdnfy: bad value '/r00/app/oracle/admin/ADCORDEV/udump' for parameter user_dump_dest.
SQL>

I attached the logfile so you may view errors.

As for the strange characters I just saw them after I submitted my comment. Looks normal before I clicked submit.
0
 
LVL 68

Expert Comment

by:woolmilkporc
Comment Utility
Is /r00/app/oracle/admin/ADCORDEV/udump a valid directory?
Writeable?
Check your pfile. Strange things in there?
Perhaps you could post it?

0
 

Author Comment

by:la_colibri
Comment Utility
Everything looks normal to me but I am attaching the initSID.ora file for your review. The udump location is valid and writable. I tried starting up the database in mount and nomount but no avail.
0
 
LVL 68

Expert Comment

by:woolmilkporc
Comment Utility
Can't find any attachment. Where is the problem? My browser?
0
 

Author Comment

by:la_colibri
Comment Utility
Everything looks normal to me but I am attaching the initSID.ora file for your review. The udump location is valid and writable. I tried starting up the database in mount and nomount but no avail.
0
 

Author Comment

by:la_colibri
Comment Utility
Sorry PC is locking up... I will copy and paste below...

*.background_dump_dest='/r00/app/oracle/admin/ADCORDEV/bdump'
*.compatible='9.2.0.0.0'
*.control_files='/r00/oradata/ADCORDEV/control00.ctl','/r01/oradata/ADCORDEV/control01.ctl'
*.core_dump_dest='/r00/app/oracle/admin/ADCORDEV/cdump'
*.db_block_size=8192
*.db_cache_size=70M# per ADP 11/15/04
*.db_file_multiblock_read_count=16
*.db_name='ADCORDEV'
*.event='10499 trace name context forever, level 1'
*.hash_join_enabled=TRUE
*.java_pool_size=33554432
*.large_pool_size=8388608
*.log_archive_dest_1='LOCATION=/r02/gcunx2_archive/ADCORDEV/log MANDATORY'
*.log_archive_dest_2='LOCATION=/r02/hqunx2_archive/ADCORDEV/log OPTIONAL'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_format='%s.arc'
*.log_archive_start=TRUE
*.open_cursors=500
*.optimizer_features_enable='9.2.0'# per ADP 11/15/04
*.pga_aggregate_target=250M# per ADP 11/15/04
*.processes=150
*.query_rewrite_enabled='TRUE'# per ADP 11/15/04
*.query_rewrite_integrity='TRUSTED'# per ADP 11/15/04
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=325M# to 300 05/15/04
*.shared_pool_size=80000000# to 80  5/10  ROS
*.sort_area_size=524288
*.star_transformation_enabled='TRUE'# per ADP 11/15/04
*.statistics_level='ALL'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='ADP_UNDO'
*.user_dump_dest='/r00/app/oracle/admin/ADCORDEV/udump'
*.workarea_size_policy='AUTO'# per ADP 11/15/04
0
 
LVL 68

Accepted Solution

by:
woolmilkporc earned 500 total points
Comment Utility
Looks OK. No idea what it could be.
Perhaps you can edit your spfile to define a new udump location and then try again to start the DB?
Silly, but who knows?
 
 
0
 

Author Comment

by:la_colibri
Comment Utility
I will try that.. if not I fear it may be something with the installation although the log shows no errors. Appreciate your efforts!
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now