Database startup script not working.

[samdba]

Hi guys,

After we upgraded from Oracle application 11.0.3 on WinNT to 11i on W2K, my starting scripts are not working properly. We are on 81730.

on 11.0.3's server, following was the sequence of database and its listener starting.

CALL %SCRIPT_DIR%\odedbctl.cmd %1 start %2 %3 %4
CALL %SCRIPT_DIR%\odelsctl.cmd %1 start %1

The same script does not run successfully on new server with 11i.

it generates following errors in startup.log
SQL*Plus: Release 8.1.7.0.0 - Production on Sun Dec 29 18:41:38 2002
(c) Copyright 2000 Oracle Corporation. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
SP2-0640: Not connected

And it generates foll. errors in odedbsr.sql.
SQL*Plus: Release 8.1.7.0.0 - Production on Sun Dec 29 18:41:36 2002
(c) Copyright 2000 Oracle Corporation. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
ORA-12560: TNSrotocol adapter error

I also want to know that what should be the correct sequence of starting database, listener and 'OracleServicePROD' service on windows server.

Thanks in Adv.


odedbctl.cmd
---------------
ECHO .
IF "%1" == "" GOTO usage
IF "%2" == "" GOTO usage
IF "%3" == "" GOTO usage

SET control_code=%2

IF "%2" == "start" GOTO mode_ok
IF "%2" == "stop" GOTO mode_ok
GOTO usage
:mode_ok

SET ORA_ENVFILE=E:\oa\oracle817\%1env.cmd
SET SCRIPT_DIR=E:\OA\ADMIN\%1\SCRIPTS
SET ORACLE_HOME=e:\oa\oracle817

if "%control_code%" == "stop" GOTO stop_db

IF "%4" == "" GOTO usage
IF "%5" == "" GOTO usage

ECHO .
ECHO Building svrmgrl scripts for %1 ... .
ECHO .

ECHO connect internal/%3 as sysdba; > %SCRIPT_DIR%\odedbsr.sql
ECHO startup restrict pfile=e:\oa\oracle817\database\init%1.ora; >> %SCRIPT_DIR%\odedbsr.sql
ECHO exit; >> %SCRIPT_DIR%\odedbsr.sql
ECHO connect internal/%3 as sysdba; > %SCRIPT_DIR%\odedbup.sql
ECHO alter system disable restricted session; >> %SCRIPT_DIR%\odedbup.sql
ECHO exit; >> %SCRIPT_DIR%\odedbup.sql

ECHO .
ECHO Starting the database %1 ... .
ECHO .

net start OracleService%1
SET LOCAL=
SET ORACLE_SID=%1
E:\oa\oracle817\bin\sqlplus.exe /nolog @%SCRIPT_DIR%\odedbsr.sql >> %SCRIPT_DIR%\odedbsr.log
E:\oa\oracle817\bin\sqlplus.exe /nolog @%SCRIPT_DIR%\odedbup.sql >> %SCRIPT_DIR%\startup.log

GOTO finish

:stop_db

ECHO .
ECHO Building svrmgrl shutdown script for %1 ... .
ECHO .

ECHO connect internal/%3 as sysdba; > %SCRIPT_DIR%\odedbdn.sql
ECHO shutdown immediate; >> %SCRIPT_DIR%\odedbdn.sql
ECHO exit; >> %SCRIPT_DIR%\odedbdn.sql

ECHO .
ECHO Shutting down database %1 ... .
ECHO .

SET LOCAL=
SET ORACLE_SID=%1
E:\oa\oracle817\bin\sqlplus.exe /nolog @%SCRIPT_DIR%\odedbdn.sql >> %SCRIPT_DIR%\shutdown.log
net stop OracleStart%1
net stop OracleService%1

:finish
REM del %SCRIPT_DIR%\*.sql
ECHO success
GOTO end

:usage
ECHO .
ECHO odedbctl.cmd .
ECHO Start / Stop database .
ECHO .
ECHO Usage .
ECHO odedbctl.cmd {appl_config} {start} {internal pwd} {sys pwd} {apps pwd}.
ECHO odedbctl.cmd {appl_config} {stop} {internal pwd} .
ECHO .
GOTO end


odelsctl.cmd
---------------
ECHO .
ECHO You are running odelsctl.cmd .
ECHO .

IF "%1" == "" GOTO usage
IF "%2" == "" GOTO usage
IF "%3" == "" GOTO usage

SET control_code=%2
SET listener_name=%3

IF "%2" == "start" GOTO mode_ok
IF "%2" == "stop" GOTO mode_ok
GOTO usage

:mode_ok

SET ORA_ENVFILE="E:\oa\oracle817\%1env.cmd"
SET ORACLE_HOME=e:\oa\oracle817
SET TNS_ADMIN=e:\oa\oracle817\network\admin

if "%control_code%" == "stop" GOTO stop_lsnr

ECHO .
ECHO Starting listener process %3 ... .
ECHO .

E:\oa\oracle817\bin\lsnrctl start %3

GOTO finish

:stop_lsnr

ECHO .
ECHO Shutting down listener process %3 ... .
ECHO .

E:\oa\oracle817\bin\lsnrctl stop %3

:finish

ECHO success
GOTO end

:usage
ECHO .
ECHO odelsctl.cmd .
ECHO Start / Stop Net8 Listener process .
ECHO .
ECHO Usage .
ECHO odelsctl.cmd {appl_config} {start/stop} {listener} .
ECHO .
ECHO In general, available listeners are: .
ECHO Database Net8 listener : {ORACLE_SID} .
ECHO Applications RPC listener : APPS_{ORACLE_SID} .
ECHO .
GOTO end

:end

[clio_usa]

ECHO connect internal/%3 as sysdba; > %SCRIPT_DIR%\odedbsr.sql
ECHO startup restrict pfile=e:\oa\oracle817\database\init%1.ora; >> %SCRIPT_DIR%\odedbsr.sql
ECHO exit; >> %SCRIPT_DIR%\odedbsr.sql
ECHO connect internal/%3 as sysdba; > %SCRIPT_DIR%\odedbup.sql
ECHO alter system disable restricted session; >> %SCRIPT_DIR%\odedbup.sql
ECHO exit; >> %SCRIPT_DIR%\odedbup.sql

Before these lines, you are not setting up the ORACLE_SID, and that's why you are getting:

ORA-12560: TNSrotocol adapter error

Also, you can connect just as INTERNAL, instead of "internal/password as sysdba". Did you ran the above lines step-by-step from the MS-DOS command line?


Hope that helps,

clio_usa - OCP 8/8i/9i DBA

Visit our Web Site

[samdba]

Hi Clio,

I am setting ORACLE_SID before actually running echoed command scripts.

Thanks,

[clio_usa]

You mean in this segment:

odedbctl.cmd
---------------
ECHO .
IF "%1" == "" GOTO usage
IF "%2" == "" GOTO usage
IF "%3" == "" GOTO usage

SET control_code=%2

IF "%2" == "start" GOTO mode_ok
IF "%2" == "stop" GOTO mode_ok
GOTO usage
:mode_ok

SET ORA_ENVFILE=E:\oa\oracle817\%1env.cmd
SET SCRIPT_DIR=E:\OA\ADMIN\%1\SCRIPTS
SET ORACLE_HOME=e:\oa\oracle817

if "%control_code%" == "stop" GOTO stop_db

IF "%4" == "" GOTO usage
IF "%5" == "" GOTO usage

ECHO .
ECHO Building svrmgrl scripts for %1 ... .
ECHO .

ECHO connect internal/%3 as sysdba; > %SCRIPT_DIR%\odedbsr.sql
ECHO startup restrict pfile=e:\oa\oracle817\database\init%1.ora; >> %SCRIPT_DIR%\odedbsr.sql
ECHO exit; >> %SCRIPT_DIR%\odedbsr.sql
ECHO connect internal/%3 as sysdba; > %SCRIPT_DIR%\odedbup.sql
ECHO alter system disable restricted session; >> %SCRIPT_DIR%\odedbup.sql
ECHO exit; >> %SCRIPT_DIR%\odedbup.sql

Can you highlight it with some collor? Don't see ORACLE_SID anywhere !!


Hope that helps,

clio_usa - OCP 8/8i/9i DBA

Visit our Web Site

[samdba]

See here...

net start OracleService%1
SET LOCAL=
SET ORACLE_SID=%1
E:\oa\oracle817\bin\sqlplus.exe /nolog @%SCRIPT_DIR%\odedbsr.sql >> %SCRIPT_DIR%\odedbsr.log
E:\oa\oracle817\bin\sqlplus.exe /nolog @%SCRIPT_DIR%\odedbup.sql >> %SCRIPT_DIR%\startup.log

Thanks

[abhaysk]

SAMDBA:

Please post ur Listener.ora,SQLnet.ora & TNSnames.ora.

In case if u are using authentication as
SQLNET.AUTHENTICATION_SERVICES= (NTS)
Then u need to check in the USER Groups ORA_DBA that the current NT loginID is added in it.

If u r not using NTS authentication then the Pswd u r giving is wrong or might have been changed due upgrade.......am not sure...

In any case i would suggest u to follow the step below.

1) Add the NT loginID of the server in ORA_DBA group.
2) In SQLnet.ora give NTS authentication.
3) Wat ever pswd u give to login oracle database is immaterial if u have chosen NTS as above.
4) Startup DB with SYS or Internal and then try ALTER user SYS,SYSTEM with old pswd u have & are usging in ur CMD files to connect.
5) Now try loginng in without NTS authentication.

Also see if ur listener & TNS are configured properly.

Regards
Abhay.

[Thomasps]

Hi

If you are using the password file please check it out the password file is courrepted.



THomas

[samdba]

Hi Abhaysk,

Please post ur Listener.ora,SQLnet.ora & TNSnames.ora.

Listener.ora
----------------
PROD =
(ADDRESS_LIST =
(ADDRESS= (PROTOCOL= IPC)(KEY= EXTPROCPROD))
(ADDRESS= (PROTOCOL= TCP)(Host= xyz)(Port= 1521))
)

SID_LIST_PROD =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME= PROD)
(ORACLE_HOME= f:\oa\proddb\8.1.7)
(SID_NAME = PROD)
)
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = f:\oa\proddb\8.1.7)
(PROGRAM = extproc)
)
)

STARTUP_WAIT_TIME_PROD = 0
CONNECT_TIMEOUT_PROD = 10
#TRACE_LEVEL_PROD = OFF

trace_level_PROD=16
LOG_DIRECTORY_PROD = f:\oa\proddb\8.1.7\network\admin
LOG_FILE_PROD = PROD
TRACE_DIRECTORY_PROD = f:\oa\proddb\8.1.7\network\admin
TRACE_FILE_PROD = PROD

SQLNET.ORA
--------------
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH=(TNSNAMES, ONAMES, HOSTNAME)

TNSNAMES.ORA
----------------
PROD = (DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=xyz)(PORT=1521))
(CONNECT_DATA=(SID=PROD))
)

#
# Intermedia
#
extproc_connection_data =
(DESCRIPTION=
(ADDRESS_LIST =
(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROCPROD))
)
(CONNECT_DATA=
(SID=PLSExtProc)
(PRESENTATION = RO)
) )

In case if u are using authentication as
SQLNET.AUTHENTICATION_SERVICES= (NTS)
Then u need to check in the USER Groups ORA_DBA that the current NT loginID is added in it.

I checked and it is there in the user group ORA_DBA.

If u r not using NTS authentication then the Pswd u r giving is wrong or might have been changed due upgrade.......am not sure...

We do use NTS Authentication and password is correct.

If I start the database listener and 'OracleServicePROD' service first and then start the database, I do not get any error messages but in my prev. version, I used to start the database directly thru SQL*PLUS without even starting the listener and 'OracleServicePROD' service.

Thanks,

[abhaysk]

Originally posted by samdba
Hi Abhaysk,

I checked and it is there in the user group ORA_DBA.

We do use NTS Authentication and password is correct.

Then try commenting NTS & use Oracle SYS pswd to connect.

Originally posted by samdba
I used to start the database directly thru SQL*PLUS without even starting the listener and 'OracleServicePROD' service.

How could u start DB without starting 'ORACLESERVICEdb' service????
I mean how could u atleast login to the idle instance without Oracle Service start??????

C:\>net stop oracleservicetest
The OracleServiceTEST service is stopping...
The OracleServiceTEST service was stopped successfully.


C:\>sqlplus

SQL*Plus: Release 9.0.1.0.1 - Production on Tue Jan 14 09:57:33 2003

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

Enter user-name: Sys as Sysdba
Enter password:
ERROR:
ORA-24314: service handle not initialized


C:\>net start oracleservicetest
The OracleServiceTEST service is starting.
The OracleServiceTEST service was started successfully.


C:\>sqlplus

SQL*Plus: Release 9.0.1.0.1 - Production on Tue Jan 14 09:58:10 2003

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

Enter user-name: sys as sysdba
Enter password:
Connected to an idle instance.

SQL>

I belive u have to start Orcle Service & then start up ur DB.

Sanjay had posted me about config of listener when i had som problem in connecting using IPC/Beq session. Hope this helps in ur case.

LISTENER =  

(ADDRESS_LIST =  

  (ADDRESS =  

   (PROTOCOL = IPC)  

   (KEY = oracle.world)  

  )  

  (ADDRESS =  

   (PROTOCOL = IPC)  

   (KEY = ORCL)  

  )  

  (ADDRESS =                      <-- add these lines  

   (PROTOCOL = IPC)               <-- add these lines  

   (KEY = extproc)                <-- add these lines  

  )                               <-- add these lines  

  (ADDRESS =   

   (PROTOCOL = TCP)  

   (Host = )  

   (PORT = 1521)  

  )  

)  

STARTUP_WAIT_TIME_LISTENER = 0  

CONNECT_TIMEOUT_LISTENER = 10  

TRACE_LEVEL_LISTENER = ADMIN  

SID_LIST_LISTENER =  

(SID_LIST =   

  (SID_DESC =  

   (SID_NAME = ORCL)  

  )  

  (SID_DESC =                    <-- add these lines  

   (SID_NAME = extproc)          <-- add these lines  

   (PROGRAM = extproc)           <-- add these lines   

  )                              <-- add these lines  

)

Regards
Abhay.

[pando]

you dont need to do anything with extproc, that is for external procedures nothing to do with this