ORA-01031: insufficient privileges

This article is a follow-up to the previous article:

• Simplify Cloning by Using Hosts File^[1]

As pointed out in that article, there are limitations and issues with cloning (either an application or a database). This article describes one of the issues (see also [2, 3]) when you clone a database.

Cloning

In [1], we have outlined the cloning tasks step by step.  In this article, we will discuss:

• How to resolve "ORA-01031: insufficient privileges"
• What is config.c

To do cloning (see [3]), we need to duplicate a software installation from a source to a destination by preserving its path structure and as the same UNIX user (say "oracle").  However, the same UNIX user can have different effective group ID's in the source and destination servers and this can cause issue such as ORA-01031.

ORA-01031

When we invoked "sqlplus / as sysdba" from the cloned environment, we have seen:

ERROR:
ORA-01031: insufficient privileges


When you clone a database, there could be differences in UNIX user/group setup between source server and destination server.

For example, our source server has the following user/group settings for the sqlplus executable:

-rwxr-x--x 1 oracle oracle 9221 Dec  5 01:25 /export/home/oracle/atg/Oracle11gR2/product/11.2.0/dbhome_1/bin/sqlplus

but, our destination server has different user/group settings for it:

-rwxr-x--x 1 oracle oinstall 9221 Dec  5 01:25 /export/home/oracle/atg/Oracle11gR2/product/11.2.0/dbhome_1/bin/sqlplus

To look into this issue, you can  check the UNIX group ID defined for sqldba adminstrative access in:

• $ORACLE_HOME/rdbms/lib/config.c

config.c

When you see the following error:
ORA-01031: insufficient privileges

you want to check config.c if your password file is ok and the group is correct for the Oracle account (i.e., "oracle").  This file tells you which UNIX group ID is assigned for sqldba administrative access by this Oracle Installation.


This is the content of config.c from source server:

/*  SS_DBA_GRP defines the UNIX group ID for sqldba adminstrative access.  */
/*  Refer to the Installation and User's Guide for further information.  */

/* IMPORTANT: this file needs to be in sync with
              rdbms/src/server/osds/config.c, specifically regarding the
              number of elements in the ss_dba_grp array.
 */

#define SS_DBA_GRP "oracle"
#define SS_OPER_GRP ""
#define SS_ASM_GRP ""

char *ss_dba_grp[] = {SS_DBA_GRP, SS_OPER_GRP, SS_ASM_GRP};

However, this is the content of config.c from another working Oracle instance on destination server:

/*  SS_DBA_GRP defines the UNIX group ID for sqldba adminstrative access.  */

/*  Refer to the Installation and User's Guide for further information.  */

/* IMPORTANT: this file needs to be in sync with

              rdbms/src/server/osds/config.c, specifically regarding the

              number of elements in the ss_dba_grp array.

 */

#define SS_DBA_GRP "dba"

#define SS_OPER_GRP "oper"

#define SS_ASM_GRP ""

char *ss_dba_grp[] = {SS_DBA_GRP, SS_OPER_GRP, SS_ASM_GRP};

From the differences, we know that our cloned Oracle binary expects "oracle"  UNIX group ID for sqldba adminstrative access.   However, our "oracle" UNIX user belongs to the following UNIX groups:

• oinstall
• dba
• oper

but, not "oracle".

$id

uid=507(oracle) gid=507(oinstall) groups=507(oinstall),8500(dba),8501(oper)

Solution

One way to resolve this issue is to create a new group named "oracle" on our destination server.  So, we have created a new group named "oracle" and add our "oracle" user to the "oracle" group (as a "root" user):


#groupadd -g 8502 oracle
#usermod -G oinstall,dba,oper,oracle oracle

For more information, read [4, 5].

References

1. Simplify Cloning by Using Hosts File
2. ORA-00313: open failed for members of log group 1 of thread 1
3. Using rsync to Clone Local and Remote Systems
4. Thread: ORA-01031: insufficient privileges While trying / as sysdba
5. Creating and Maintaining a Password File
6. ORA-27101: shared memory realm does not exist tips
7. Migrating Oracle B2B from Test to Production (T2P) (Chap 10 of the Book "Getting Started with Oracle SOA B2B Integration: A Hands-On Tutorial")
• This section provides a real-world scenario to replicate (clone) the test environment to production for Oracle SOA.
• Oracle Fusion Middleware provides a series of scripts for this task.
8. Oracle Products: What Patching, Migration, and Upgrade Mean? (Xml and More)
• For your Oracle production systems, follow official recommendations as shown in this article.