OraFAQ Forum: RAC & Failsafe » sqlplus / as sysdba -- does not work

Home » Server Options » RAC & Failsafe » sqlplus / as sysdba -- does not work (Oracle 10gR2 /CRS (RAC) Windows 2008r2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

sqlplus / as sysdba -- does not work [message #485785]

Thu, 09 December 2010 22:23

ssahib
Messages: 7
Registered: December 2010
Location: Melbourne

Junior Member

The main issue is "Cannot use sqlplus / as sysdba to logon to the two nodes of a RAC".This is a new 2 node install. I can log onto sqlplus sys/password@SID as sysdba, sqlplus sys/password@SID1 as sysdba & sqlplus sys/password@SID2 as sysdba from both nodes. I cannot logon as sys / as sysdba (running Oracle 10gR2Enterprise Edition - 64bit Windows 2008r2). We need this to run because I am trying to set system in archivelog mode. Once I shut the database down -- I cannot log onto sqplus to perform the following; startup no mount. Even when all the instances are up -- I cannot logon sqlplus / as sysdba -- I get ORA-12560:TNS:protocol adapter error. The login OS user is part of the local administrator and ora_dba group (Windows), in the registery autostart, shutdown is set to TRUE, shutdowntype is set to immediate & ORAMTS_OSCREDS_MATCH_LEVEL is set to OS_Auth_Login.In sqlnet.ora suthentication_service = (NTS). I have even set environment on node 1 as set ORACLE_SID=SID1.
Any help would be appreciated.
Cheers Al

Report message to a moderator

Re: sqlplus / as sysdba -- does not work [message #485788 is a reply to message #485785]

Thu, 09 December 2010 22:31

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

http://psoug.org/reference/oradim.html

did you start the Oracle service?

Report message to a moderator

Re: sqlplus / as sysdba -- does not work [message #485897 is a reply to message #485788]

Fri, 10 December 2010 23:52

ssahib
Messages: 7
Registered: December 2010
Location: Melbourne

Junior Member

Thanks for your response. Even at start (when all services etc are running) , I can not logon to database as -- sqlplus / as sysdba.

Report message to a moderator

Re: sqlplus / as sysdba -- does not work [message #485904 is a reply to message #485897]

Sat, 11 December 2010 02:34

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

As I ubderstand it, your immediate problem is that you can't startup in mode mount, which is necessary to enable archivelog mode. Have you tried with the srvctl utility? Do this, from either node:

srvctl stop database -d SID -o immediate
srvctl start instance -d SID -i SID1 -o mount

sqlplus sys/password@sid1 as sysdba
alter database archivelog
alter database open

srvctl start instance -d SID -i SID2

Then you can sort out your problem, which is that your operating system authentication isn't working.

Report message to a moderator

Re: sqlplus / as sysdba -- does not work [message #486015 is a reply to message #485904]

Sun, 12 December 2010 18:39

ssahib
Messages: 7
Registered: December 2010
Location: Melbourne

Junior Member

Hi John, Thank you for your response. I have done as you have suggested but even if the database is mounted using the srvctl commands you have provided - I can see from the alert log
"Mon Dec 13 13:02:02 2010
ALTER DATABASE MOUNT
Mon Dec 13 13:02:02 2010
This instance was first to mount
Setting recovery target incarnation to 2
Mon Dec 13 13:02:08 2010
Successful mount of redo thread 1, with mount id 2720331146
Mon Dec 13 13:02:08 2010
Database mounted in Shared Mode (CLUSTER_DATABASE=TRUE)
Completed: ALTER DATABASE MOUNT"
I still can not logon to database as sqlplus sys/password@sid1 as sysdba -- so that I can run the alter database archivelog command -- the cursor hangs up and there is no output.
Cheers
Al

Report message to a moderator

Re: sqlplus / as sysdba -- does not work [message #486016 is a reply to message #486015]

Sun, 12 December 2010 19:17

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I cannot logon sqlplus / as sysdba -- I get ORA-12560:TNS:protocol adapter error.
Please understand that when no "@TNS_ALIAS" exists as part of connection string,
then SQL*Net should not be involved to complete the connection request & no TNS error.
ORA-12560 can result when OS/networking misconfiguration exists.

Report message to a moderator

Re: sqlplus / as sysdba -- does not work [message #486017 is a reply to message #486015]

Sun, 12 December 2010 20:36

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

"the cursor hangs up and there is no output" is not an Oracle error message. You need to describe your problem better, using copy-paste would help.
In your first post you said "I can log onto sqlplus sys/password@SID as sysdba" are you saying that you can't do this anymore?

Report message to a moderator

Re: sqlplus / as sysdba -- does not work [message #486018 is a reply to message #486017]

Sun, 12 December 2010 20:54

ssahib
Messages: 7
Registered: December 2010
Location: Melbourne

Junior Member

Hi John,
I can not log into sqlplus sys/password@sid1 as sysdba or sqlplus sys/password@sid as sysdba when I have mounted the database as per your instructions i.e. after C:\>srvctl start instance -d sid -i sid1 -o mount. Note - database at this stage is mounted and not open. Cheers Al

Report message to a moderator

Re: sqlplus / as sysdba -- does not work [message #486049 is a reply to message #486018]

Mon, 13 December 2010 04:13

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

"I can not log into sqlplus" is not an Oracle error message. I can't take this further, as you haven't provided the necessary information. Perhaps someone else can help.

Report message to a moderator

Re: sqlplus / as sysdba -- does not work [message #486323 is a reply to message #486049]

Tue, 14 December 2010 18:11

ssahib
Messages: 7
Registered: December 2010
Location: Melbourne

Junior Member

Hi John, I have managed to setup the RAC in archivelog mode. I followed your steps(again) and was successful -- thank you for that. Briefly the steps where;
From node1 (hbris01)
(1) ran the cluster status checks
C:\>crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....s1.inst application ONLINE ONLINE hbris01
ora....s2.inst application ONLINE ONLINE hbris02
ora.sid.db application ONLINE ONLINE hbris02
ora....01.lsnr application ONLINE ONLINE hbris01
ora....s01.gsd application ONLINE ONLINE hbris01
ora....s01.ons application ONLINE ONLINE hbris01
ora....s01.vip application ONLINE ONLINE hbris01
ora....02.lsnr application ONLINE ONLINE hbris02
ora....s02.gsd application ONLINE ONLINE hbris02
ora....s02.ons application ONLINE ONLINE hbris02
ora....s02.vip application ONLINE ONLINE hbris02

(2) connect to db via
C:\>sqlplus sys/password@sid as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Dec 15 10:05:42 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> exit

(3) connect to db via
C:\>sqlplus sys/password@sid1 as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Dec 15 10:06:30 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL>exit

(4) connect to db via
C:\>sqlplus sys/password@sid2 as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Dec 15 10:08:28 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> exit

(5) connect via -- (this is my 1st issue - have to press Ctrl C to break the loop)
C:\>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Dec 15 10:10:43 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
ERROR:
ORA-12560: TNS:protocol adapter error
Enter user-name:

(6) Now to follow your steps
(Step 1)
C:\>srvctl stop database -d sid -o immediate
C:\>crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....s1.inst application OFFLINE OFFLINE
ora....s2.inst application OFFLINE OFFLINE
ora.sid.db application OFFLINE OFFLINE
ora....01.lsnr application ONLINE ONLINE hbris01
ora....s01.gsd application ONLINE ONLINE hbris01
ora....s01.ons application ONLINE ONLINE hbris01
ora....s01.vip application ONLINE ONLINE hbris01
ora....02.lsnr application ONLINE ONLINE hbris02
ora....s02.gsd application ONLINE ONLINE hbris02
ora....s02.ons application ONLINE ONLINE hbris02
ora....s02.vip application ONLINE ONLINE hbris02

(Step 2)
C:\>srvctl start instance -d sid -i sid1 -o mount
C:\>crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....s1.inst application ONLINE ONLINE hbris01
ora....s2.inst application OFFLINE OFFLINE
ora.sid.db application ONLINE ONLINE hbris02
ora....01.lsnr application ONLINE ONLINE hbris01
ora....s01.gsd application ONLINE ONLINE hbris01
ora....s01.ons application ONLINE ONLINE hbris01
ora....s01.vip application ONLINE ONLINE hbris01
ora....02.lsnr application ONLINE ONLINE hbris02
ora....s02.gsd application ONLINE ONLINE hbris02
ora....s02.ons application ONLINE ONLINE hbris02
ora....s02.vip application ONLINE ONLINE hbris02
As seen from the above -- sid1 and sid are both ONLINE.
(Step 3)
C:\>sqlplus sys/password@sid1 as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Dec 15 10:18:56 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL>

(Step 4)
SQL> alter database archivelog;
Database altered.
(Step 5)
SQL> alter database open;
Database altered.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64
bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

(Step 6)
C:\>srvctl start instance -d sid -i sid2

C:\>crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....s1.inst application ONLINE ONLINE hbris01
ora....s2.inst application ONLINE ONLINE hbris02
ora.sid.db application ONLINE ONLINE hbris02
ora....01.lsnr application ONLINE ONLINE hbris01
ora....s01.gsd application ONLINE ONLINE hbris01
ora....s01.ons application ONLINE ONLINE hbris01
ora....s01.vip application ONLINE ONLINE hbris01
ora....02.lsnr application ONLINE ONLINE hbris02
ora....s02.gsd application ONLINE ONLINE hbris02
ora....s02.ons application ONLINE ONLINE hbris02
ora....s02.vip application ONLINE ONLINE hbris02

To check if db is in archivelog mode;
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination C:\oracle\product\10.2.0\db_1\RDBMS
Oldest online log sequence 81
Next log sequence to archive 82
Current log sequence 82

To set up db_recovery_file_size;
SQL> alter system set db_recovery_file_dest_size=300G scope=both sid='*';
System altered.

To set up db_recovery_file_destination;
SQL> alter system set db_recovery_file_dest='L:\FlashRecovery' scope=both sid='*';
System altered.
SQL>exit
Reboot both nodes (node 1 and then node 2)
From node 1
C:\>sqlplus sys/password@sid as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Dec 15 10:55:34 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 82
Next log sequence to archive 82
Current log sequence 83

Shows that database is in archive mode and archive destination is using parameter file db_recovery.

SQL> show parameters db_recovery;

NAME TYPE VALUE
------------------------------------ ----------- ----------------
db_recovery_file_dest string L:\FlashRecovery
db_recovery_file_dest_size big integer 300G

Shows the db_recovery_file_destination and db_recovery_file_destination size.

Thanks for all who have contributed.
Cheers Al

Report message to a moderator

Re: sqlplus / as sysdba -- does not work [message #486325 is a reply to message #486323]

Tue, 14 December 2010 19:01

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>C:\>sqlplus / as sysdba
>SQL*Plus: Release 10.2.0.4.0 - Production on Wed Dec 15 10:10:43 2010
>Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
>ERROR:
>ORA-12560: TNS:protocol adapter error
Can occur when OS/networking misconfiguration exists.
Hostname has multiple different values on same system.
Post content of "hosts" file.

Report message to a moderator

Re: sqlplus / as sysdba -- does not work [message #487201 is a reply to message #486325]

Thu, 23 December 2010 15:04

ssahib
Messages: 7
Registered: December 2010
Location: Melbourne

Junior Member

Hi BlackSwan,
Sorry for the delay -- my hosts file in both nodes are same -- i.e .
# Copyright (c) 1993-2006 Microsoft Corp.
#
# This is a sample HOSTS file used by Microsoft TCP/IP for Windows.
#
# This file contains the mappings of IP addresses to host names. Each
# entry should be kept on an individual line. The IP address should
# be placed in the first column followed by the corresponding host name.
# The IP address and the host name should be separated by at least one
# space.
#
# Additionally, comments (such as these) may be inserted on individual
# lines or following the machine name denoted by a '#' symbol.
#
# For example:
#
# 102.54.94.97 rhino.acme.com # source server
# 38.25.63.10 x.acme.com # x client host

127.0.0.1 localhost
::1 localhost
10.168.6.225 hbris01
10.168.6.226 hbris02
192.168.6.1 hbris01-priv
192.168.6.2 hbris02-priv
10.168.6.233 hbris01-vip
10.168.6.234 hbris02-vip

Report message to a moderator

Re: sqlplus / as sysdba -- does not work [message #487202 is a reply to message #487201]

Thu, 23 December 2010 16:03

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

at the OS level two services need to be running; one for the database & one for the listener.

have both been started?

Report message to a moderator

Re: sqlplus / as sysdba -- does not work [message #487204 is a reply to message #487202]

Thu, 23 December 2010 18:15

ssahib
Messages: 7
Registered: December 2010
Location: Melbourne

Junior Member

Hi BlackSwan,
I do not have any issues with Oracle RAC -- Have done all the cluster test etc and the clients connect to RAC with no issues. OS Oracle/Listener services running on both nodes. My only issue that I am trying to resolve is" why I can not log onto to either of the nodes with" sqlplus / as sysdba ?
Cheers

Report message to a moderator

Re: sqlplus / as sysdba -- does not work [message #487205 is a reply to message #487204]

Thu, 23 December 2010 18:30

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

open command window then issue following command

SET

COPY results then PASTE all back here

Report message to a moderator

Re: sqlplus / as sysdba -- does not work [message #581681 is a reply to message #485785]

Tue, 09 April 2013 18:12

indbluechip
Messages: 2
Registered: July 2006
Location: texas

Junior Member

I got the same error. This happened after I did srvctl add service. was this resolved

Report message to a moderator

Previous Topic:	RAC Configuration
Next Topic:	Steps to stop and start RAC instance

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Feb 09 04:20:20 CST 2025