How to Set Up Oracle Database Permissions (Sun Cluster Data Service for Oracle Guide for Solaris OS)

Sun Cluster Data Service for Oracle Guide for Solaris OS

How to Set Up Oracle Database Permissions

Steps

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>	grant connect, resource to 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 create session to user;
sql>	grant create table to user;

sql>	exit;
#

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

To use the Solaris authentication method, perform the following steps:

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

Determine the setting of the os_authent_prefix parameter.

# sql>  show parameter os_authent_prefix

NAME                       TYPE        VALUE
---------------------- ----------- ---------------
os_authent_prefix         string      ops$

Grant permission for the database to use 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 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 Solaris authentication. Ensure that this user owns the files under the $ORACLE_HOME directory.

Note –

Do not type a space between prefix and user.

Configure NET8 for the Sun Cluster software.

The listener.ora file must be accessible from all of 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 scrgadm(1M) 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.

Sun Cluster 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 =
	(ADDRESS_LIST =
			(ADDRESS =
				(PROTOCOL = TCP) 
					(HOST = logical-hostname) <- use logical hostname
				(PORT = 1527)
			)
	)
.
.
SID_LIST_LISTENER =
	.
			.
						(SID_NAME = SID) <- Database name, 
default is ORCL

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))
)

Verify that the Sun Cluster software is installed and running on all of the nodes.
# scstat

Next Steps

Go to Installing the Sun Cluster HA for Oracle Packages to install the Sun Cluster HA for Oracle packages.