Solved

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

Posted on 2008-10-14
19
2,031 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
ID: 22711444
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
ID: 22711549
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
ID: 22711793
May not be the issue, but are your ORACLE_HOME and ORACLE_SID defined before you start the database?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 10

Expert Comment

by:dfke
ID: 22711808
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
ID: 22711835
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
ID: 22712829
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
ID: 22713402
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
ID: 22714552
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
ID: 22714688
What are those strange characters  around the directory name? Problem of my browser?
What about the startup command?
0
 
LVL 23

Expert Comment

by:David
ID: 22714716
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
ID: 22714737
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
ID: 22715287
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
ID: 22715544
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
ID: 22722202
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
ID: 22722269
Can't find any attachment. Where is the problem? My browser?
0
 

Author Comment

by:la_colibri
ID: 22722311
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
ID: 22722328
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
ID: 22722798
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
ID: 22722841
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
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…
Via a live example, show how to take different types of Oracle backups using RMAN.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

776 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