Server: HP-UX
Oracle DB: 10g
While connecting as sysdba user to one of my 10g database it gives me
an error
myoraserver:MYCUSTDB:/opt/oracle/product/10.1.0.5.64/dbs $ sqlplus sys@MYCUSTDB
as sysdba
SQL*Plus: Release 10.1.0.5.0 - Production on Thu Nov 3 12:27:01 2016
Copyright (c) 1982, 2005, Oracle.
All rights reserved.
Enter password:
ERROR:
ORA-01031:
insufficient privileges
myoraserver:MYCUSTDB:/opt/oracle/product/10.1.0.5.64/dbs $ sqlplus / as
sysdba
SQL*Plus: Release 10.1.0.5.0 - Production on Thu Nov 3 11:58:09 2016
Copyright (c) 1982, 2005, Oracle.
All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - 64bit
Production
With the Partitioning, OLAP and Data Mining options
SQL>
SQL> show parameter password
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
remote_login_passwordfile
string EXCLUSIVE
SQL> select * from
v$pwfile_users;
no rows selected à
Seems to be an issue now
SQL> grant sysdba to sys;
grant sysdba to sys
*
ERROR at line 1:
ORA-01999: password file mode
has changed from 'exclusive' to 'shared'
SQL> sho parameter pfile
NAME
TYPE VALUE
------------------------------------ -----------
------------------------------
spfile
string
/opt/oracle/product/10.1.0.5.64/dbs/spfileMYCUSTDB.ora
myoraserver:MYCUSTDB:/opt/oracle/admin/MYCUSTDB/bdump $ cd
$ORACLE_HOME/dbs
myoraserver:MYCUSTDB:/opt/oracle/product/10.1.0.5.64/dbs $ ls -lr
orapw*
i can see my passwordfile, lets backup existing password file and
recreate new with same password
myoraserver:MYCUSTDB:/opt/oracle/product/10.1.0.5.64/dbs $ mv orapwMYCUSTDB
orapwMYCUSTDB.Nov03
myoraserver:MYCUSTDB:/opt/oracle/product/10.1.0.5.64/dbs $ orapwd
file=orapwMYCUSTDB password=xxxxxx
myoraserver:MYCUSTDB:/opt/oracle/product/10.1.0.5.64/dbs $ sqlplus sys@MYCUSTDB
as sysdba
SQL*Plus: Release 10.1.0.5.0 - Production on Thu Nov 3 12:02:07 2016
Copyright (c) 1982, 2005, Oracle.
All rights reserved.
Enter password:
ERROR:
ORA-01031: insufficient
privileges
Enter user-name:
Still same
error when connecting as sys user…………
Myoraserver:MYCUSTDB:/opt/oracle/product/10.1.0.5.64/dbs $ sqlplus / as
sysdba
SQL*Plus: Release 10.1.0.5.0 - Production on Thu Nov 3 12:24:09 2016
Copyright (c) 1982, 2005, Oracle.
All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - 64bit
Production
With the Partitioning, OLAP and Data Mining options
SQL> ALTER SYSTEM SET
remote_login_passwordfile=shared scope=SPFILE;
System altered.
********* Need to Bounce the database *********
SQL> select name from v$database;
NAME
---------
MYCUSTDB
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 2046820352 bytes
Fixed Size
1299208 bytes
Variable Size
1179397368 bytes
Database Buffers
855638016 bytes
Redo Buffers
10485760 bytes
Database mounted.
Database opened.
SQL> select * from
v$pwfile_users;
USERNAME
SYSDB SYSOP
------------------------------ ----- -----
SYS
TRUE TRUE
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release
10.1.0.5.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
myoraserver:MYCUSTDB:/opt/oracle/product/10.1.0.5.64/dbs $ sqlplus sys@MYCUSTDB
as sysdba
SQL*Plus: Release 10.1.0.5.0 - Production on Fri Nov 4 12:24:58 2016
Copyright (c) 1982, 2005, Oracle.
All rights reserved.
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - 64bit
Production
With the Partitioning, OLAP and Data Mining options
SQL> show parameter password
NAME
TYPE VALUE
------------------------------------ -----------
------------------------------
remote_login_passwordfile
string SHARED
Now i can able to connect without any issues.............Now revert
back to EXCLUSIVE
SQL> ALTER SYSTEM SET
remote_login_passwordfile=EXCLUSIVE scope=spfile;
System altered.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 2046820352 bytes
Fixed Size 1299208
bytes
Variable Size
1179397368 bytes
Database Buffers
855638016 bytes
Redo Buffers
10485760 bytes
Database mounted.
Database opened.
SQL> show parameter password
NAME
TYPE VALUE
------------------------------------ -----------
------------------------------
remote_login_passwordfile
string EXCLUSIVE
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release
10.1.0.5.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
myoraserver:MYCUSTDB:/opt/oracle/product/10.1.0.5.64/dbs $ sqlplus sys@MYCUSTDB
as sysdba
SQL*Plus: Release 10.1.0.5.0 - Production on Fri Nov 4 12:28:42 2016
Copyright (c) 1982, 2005, Oracle.
All rights reserved.
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - 64bit
Production
With the Partitioning, OLAP and Data Mining options
SQL> select * from v$pwfile_users;
USERNAME
SYSDB SYSOP
------------------------------ ----- -----
SYS
TRUE TRUE
Reference:
ORA-1999: When Granting SYSDBA to User (Doc ID 103576.1)
0 comments:
Post a Comment