Setting Up Oracle Database Permissions

---

Caution - Do not perform the steps in this section for an Oracle physical standby database.

---

Perform the procedure in this section to set up database permissions for an Oracle primary database or an Oracle logical standby database.

How to Set Up Oracle Database Permissions

1. Enable access for the user and password to be used for fault monitoring.
   • To use the Oracle authentication method, grant to this user authority on the v_$sysstat view and the v_$archive_dest view.

     # sqlplus "/ as sysdba"
     
     sql>    create user user identified by passwd;
     sql>    alter user user default tablespace system quota 1m on system;
     sql>    grant select on v_$sysstat to user;
     sql>    grant select on v_$archive_dest to user;
     sql>    grant select on v_$database to user;
     sql>    grant create session to user;
     sql>    grant create table to user;
     sql>    create profile profile limit PASSWORD_LIFE_TIME UNLIMITED;
     sql>    alter user user identified by passwd profile profile;
     
     sql> exit;
     #

     You can use this method for all the supported Oracle releases.

   • To use the Oracle Solaris authentication method, perform the following steps:
     1. Confirm that the remote_os_authent parameter is set to TRUE.

        # sqlplus "/ as sysdba"
        sql> show parameter remote_os_authent
        
        NAME                       TYPE        VALUE
        ---------------------- ----------- ---------------
        remote_os_authent         boolean     TRUE

     2. Determine the setting of the os_authent_prefix parameter.

        # sql>  show parameter os_authent_prefix
        
        NAME                       TYPE        VALUE
        ---------------------- ----------- ---------------
        os_authent_prefix         string      ops$

     3. Grant permission for the database to use Oracle Solaris authentication.

        sql> create user prefix user identified by externally default 
        tablespace system quota 1m on system;
        sql> grant connect, resource to prefix user;
        sql> grant select on v_$sysstat to prefix user;
        sql> grant select on v_$archive_dest to prefix user;
        sql> grant select on v_$database to prefix user;
        sql> grant create session to prefix user;
        sql> grant create table to prefix user;
        sql> exit;
        #

        The replaceable items in these commands are as follows:

        • prefix is the setting of the os_authent_prefix parameter. The default setting of this parameter is ops$.

        • user is the user for whom you are enabling Oracle Solaris authentication. Ensure that this user owns the files under the $ORACLE_HOME directory.

        ---

        Note - Do not type a space between prefix and user.

        ---

2. Configure Oracle Net for the Oracle Solaris Cluster software.

   The listener.ora file must be accessible from all the nodes that are in the cluster. Place these files either under the cluster file system or in the local file system of each node that can potentially run the Oracle resources.

   ---

   Note - If you place the listener.ora file in a location other than the /var/opt/oracle directory or the $ORACLE_HOME/network/admin directory, you must specify the TNS_ADMIN variable or an equivalent Oracle variable in a user-environment file. For information about Oracle variables, see the Oracle documentation.

   You must also run the clresource command to set the resource extension parameter User_env, which sources the user-environment file. See SUNW.oracle_listener Extension Properties or SUNW.oracle_server Extension Properties for format details.

   ---

   HA for Oracle imposes no restrictions on the listener name; it can be any valid Oracle listener name.

   The following code sample identifies the lines in listener.ora that are updated.

   LISTENER =
       (DESCRIPTION_LIST =
           (DESCRIPTION =
               (ADDRESS_LIST =
                   (ADDRESS =  (PROTOCOL = IPC) (KEY = EXTPROC))
                   (ADDRESS =  (PROTOCOL = TCP) (HOST = logical-hostname) (PORT = port-used))
           )
       )

   The following code sample identifies the lines in tnsnames.ora that are updated on client machines.

   service_name =
       .
               .
                           (ADDRESS = 
                                   (PROTOCOL = TCP)
                                   (HOST = logicalhostname)    <- logical hostname
                                   (PORT = 1527) <- must match port in LISTENER.ORA
                           )
                   )
                   (CONNECT_DATA =
                           (SID = <SID>)) <- database name, default is ORCL

   The following example shows how to update the listener.ora and tnsnames.ora files for the following Oracle instances.

   
   

   Instance Logical Host Listener ora8 hadbms3 LISTENER-ora8 ora9 hadbms4 LISTENER-ora9

   The corresponding listener.ora entries are the following entries.

   LISTENER-ora9 =
       (ADDRESS_LIST =
               (ADDRESS =
                   (PROTOCOL = TCP)
                   (HOST = hadbms4)
                   (PORT = 1530)
               )
           )
   SID_LIST_LISTENER-ora9 =
       (SID_LIST =
               (SID_DESC =
                   (SID_NAME = ora9)
               )
           )
   LISTENER-ora8 =
     (ADDRESS_LIST =
       (ADDRESS= (PROTOCOL=TCP) (HOST=hadbms3)(PORT=1806))
     )
   SID_LIST_LISTENER-ora8 =
     (SID_LIST =
        (SID_DESC =
               (SID_NAME = ora8)
            )    
     )

   The corresponding tnsnames.ora entries are the following entries.

   ora8 =
   (DESCRIPTION =
      (ADDRESS_LIST = 
               (ADDRESS = (PROTOCOL = TCP) 
               (HOST = hadbms3) 
               (PORT = 1806))
          )    
       (CONNECT_DATA = (SID = ora8))
   )
   ora9 =
   (DESCRIPTION =
     (ADDRESS_LIST =
           (ADDRESS = 
                   (PROTOCOL = TCP) 
                   (HOST = hadbms4) 
                   (PORT = 1530))
     )
         (CONNECT_DATA = (SID = ora9))
   )

3. Verify that the Oracle Solaris Cluster software is installed and running on all the nodes.

   # cluster status clustername

Next Steps

Go to Installing the HA for Oracle Package to install the HA for Oracle packages.