ORACLE

Saturday, December 3, 2016

ORA-01031: insufficient privileges

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

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

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

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

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

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

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)

Saturday, November 12, 2016

Recovery Writer (RVWR) is stuck until more space is available in the recovery area

Today I got an alert for My OMS is down and when I checked the oem url I see OMS down message.

[oracle@OMS_SERVER bin]$ ./emctl status oms

Oracle Enterprise Manager Cloud Control 13c Release 2

WebTier is Up

Oracle Management Server is Down

JVMD Engine is Down

BI Publisher Server is Up

Checked OMS repository database(EMREPO) and i see database and listener is up and running fine but found below error in alert log…………

*************************************************************

Unable to allocate flashback log of 6377 blocks from

current recovery area of size 32212254720 bytes.

Recovery Writer (RVWR) is stuck until more space

is available in the recovery area.

recovery area is full, presence of a guaranteed

restore point and no reusable flashback logs.

Mon Nov 07 10:51:46 2016

*************************************************************

SQL> select space_limit/1024/1024/1024 maxGB,space_used/1024/1024/1024 usedGB from v$recovery_file_dest;

MAXGB USEDGB

---------- ----------

30 29.9878693

SQL> SELECT FILE_TYPE "Type",PERCENT_SPACE_USED "% Used",PERCENT_SPACE_RECLAIMABLE "% Reclaim",

NUMBER_OF_FILES "# Files" FROM V$FLASH_RECOVERY_AREA_USAGE;

Type % Used % Reclaim # Files

--------------------------------------------------------------------- ---------- ---------- ----------

CONTROL FILE 0 0 0

REDO LOG 0 0 0

ARCHIVED LOG 0 0 0

BACKUP PIECE .06 .06 1

IMAGE COPY 0 0 0

FLASHBACK LOG 99.9 0 154

FOREIGN ARCHIVED LOG 0 0 0

AUXILIARY DATAFILE COPY 0 0 0

8 rows selected.

SQL> show parameter db_recovery

NAME TYPE VALUE

------------------------------------ ----------------------------------------------------------------------------- ------

db_recovery_file_dest string /opt/oem/u20

db_recovery_file_dest_size big integer 30G

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT;

NAME SCN TIME DATABASE_INCARNATION# GUARANTEE STORAGE_SIZE

--------------------------------- ---------- -------------------------------------------- --------------------- --------- ------------

EMREPO_BEFORE_UPGRADE13C 3.1001E+11 27-OCT-16 11.04.22.000000000 AM 2 YES 3.2181E+10

Now, I remember it is because of the Grantee Restore point which I have created during my recent OMS upgrade and now I can drop this since it’s been so many days and everything looks good.

SQL> DROP RESTORE POINT EMREPO_BEFORE_UPGRADE13C;

Restore point dropped.

SQL>

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT;

no rows selected

SQL> select space_limit/1024/1024/1024 maxGB,space_used/1024/1024/1024 usedGB from v$recovery_file_dest;

MAXGB USEDGB

---------- ----------

30 .651931763

[oracle@OMS_SERVER bin]$ ./emctl start oms

Oracle Enterprise Manager Cloud Control 13c Release 2

Starting Oracle Management Server...

WebTier Successfully Started

Oracle Management Server Already Started

Oracle Management Server is Up

JVMD Engine is Up

Starting BI Publisher Server ...

BI Publisher Server Already Started

BI Publisher Server is Up

Now I see my OEM url is working fine…………….

Saturday, October 8, 2016

Error: The command name "list_plugins_on_server" is not a recognized command.

To identify the Plug-ins deployed on OMS, Go to $OMS_HOME/bin and execute below

[oracle@OMSHOST6 bin]$ ./emcli list_plugins_on_server

Error: The command name "list_plugins_on_server" is not a recognized command.

Run the "help" command for a list of recognized commands.

You may also need to run the "sync" command to synchronize with the current OMS.

[oracle@OMSHOST6 bin]$ ./emcli login -username=sysman

Enter password

[oracle@OMSHOST6 bin]$ ./emcli sync

Synchronized successfully

[oracle@OMSHOST6 bin]$ ./emcli list_plugins_on_server

OMS name is OMSHOST6.ux.corp.local:4889_Management_Service

Plug-in Name Plugin-id Version [revision]

Oracle Cloud Framework oracle.sysman.cfw 13.1.1.0.0

Oracle Database oracle.sysman.db 13.1.1.0.0

Oracle Fusion Middleware oracle.sysman.emas 13.1.1.0.0

Systems Infrastructure oracle.sysman.si 13.1.1.0.0

Oracle Exadata oracle.sysman.xa 13.1.1.0.0