Section 1: Notes to DBA
Notes to send a DBA to prepare customer's Oracle environment before
you arrive onsite.
Section 2: Oracle and/or SQL*Net Notes
How to configure the Oracle and/or SQL*Net environment ready
Section 3: Tips on using SQL*Net
From my experience at one of our customers in late 1998
Matthew Reiser
[email protected]
Created: 1/7/1999
Last revised 9/24/1999
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
Section 1: Notes to DBA
Oracle Prerequisites for Vignette CMS:
--------------------------------------------------------------------------------
Assuming user="VignUser", SID = "VignSID", tablespace = "VignTablespace"
At a SQL> prompt type:
SQL>create tablespace VignTablespace datafile /export/home/oradata/Vign/Vign01.dbf
size 1024M;
SQL>create user VignUser
2>default tablespace VignTablespace
3>temporary tablespace temp
4>quota unlimited on VignTablespace
5>grant CONNECT, RESOURCE to VignUser;
IMPORTANT:
In order to install Vignette CMS:
1. You should set up a tablespace to be at least 1024M
3. 'tnsping VignSID' must succeed (even if all software is on
one host)
================================================================================
The following must match:
client and server versions
Oracle libraries
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
Section 2: Oracle and/or SQL*Net Notes
# su - oracle
#---------------------------------------------------
# Set up ORACLE initialization environment variables
# (if not already done in ~oracle/.profile)
#---------------------------------------------------
ORACLE_HOME=/opt/oracle
ORACLE_SID=VignSID
PATH=$ORACLE_HOME/bin:/usr/ccs/bin:/usr/X/bin:/usr/openwin/bin:/usr/bin/X11:/usr/dt/bin:/usr/openwin/bin:/bin:/usr/bin:/usr/ucb:/usr/sbin
LD_LIBRARY_PATH=/usr/X/lib:/usr/openwin/lib:/usr/dt/lib:$ORACLE_HOME/lib
#Optional
TNS_ADMIN=/opt/oracle/network/admin
WGSHOST=sunday.corp.kinkos.com # Needed for Oracle Workgroup Server
NLS_LANG=american_america.US7ASCII # Needed?
$
$
#---------------------------------------------------
# Check status of listener on Oracle server
#---------------------------------------------------
$
$ lsnrctl status listener
LSNRCTL for Solaris: Version 2.3.2.1.0 - Production on 02-DEC-98 05:30:48
Copyright (c) Oracle Corporation 1994. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=TCP)(HOST=sunday)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias tcp_listener
Version TNSLSNR for Solaris: Version 2.3.2.1.0 - Production
Start Date 25-NOV-98 07:17:55
Uptime 6 days 22 hr. 12 min. 38 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File /var/opt/oracle/listener.ora
Listener Log File /opt/oracle/network/log/tcp_listener.log
Services Summary...
VignSID has 1 service handler(s)
The command completed successfully
#---------------------------------------------------
# The above two lines are a Good Thing
#---------------------------------------------------
$
$
$ sqlplus system/manager
SQL*Plus: Release 3.3.2.0.0 - Production on Wed Dec 2 05:31:14 1998
Copyright (c) Oracle Corporation 1979, 1994. All rights reserved.
Connected to:
Oracle7 Server Release 7.3.2.2.0 - Production Release
With the distributed option
PL/SQL Release 2.3.2.2.0 - Production
#---------------------------------------------------
# Create a 1Gb Vignette tablespace (VignTablespace)
#---------------------------------------------------
SQL> create tablespace VignTablespace datafile '/space/Vign/data01.dbf' size 1000M;
Tablespace created.
#---------------------------------------------------
# Create Vignette CMS user/pw (VignUser/VignPassword) using a Vignette
tablespace
#---------------------------------------------------
SQL> create user VignUser identified by VignUser default tablespace VignTablespace;
User created.
SQL> alter user VignUser temporary tablespace temp;
User altered.
SQL> alter user VignUser quota unlimited on VignTablespace;
User altered.
SQL> grant connect, resource to VignUser;
Grant succeeded.
SQL> exit
Disconnected from Oracle7 Server Release 7.3.2.2.0 - Production Release
With the distributed option
PL/SQL Release 2.3.2.2.0 - Production
$
$
#---------------------------------------------------
# Verify proper Vignette CMS user setup
#---------------------------------------------------
$ sqlplus VignUser/VignPassword@VignSID
SQL*Plus: Release 3.3.2.0.0 - Production on Wed Dec 2 05:35:58 1998
Copyright (c) Oracle Corporation 1979, 1994. All rights reserved.
Connected to:
Oracle7 Server Release 7.3.2.2.0 - Production Release
With the distributed option
PL/SQL Release 2.3.2.2.0 - Production
SQL> desc user_users;
Name Null? Type
------------------------------- -------- ----
USERNAME NOT NULL VARCHAR2(30)
USER_ID NOT NULL NUMBER
DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)
TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)
CREATED NOT NULL DATE
SQL> select * from user_users where username ="VignUser";
USERNAME USER_ID DEFAULT_TABLESPACE
------------------------------ ---------- ------------------------------
TEMPORARY_TABLESPACE CREATED
------------------------------ ---------
VignUser 12 VignTablespace
TEMP 03-DEC-98
#---------------------------------------------------
# Verify StoryServer user can create tables
#---------------------------------------------------
SQL> create table x(x int);
Table created.
SQL> select tablespace_name, status from user_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
TOOLS ONLINE
RBS ONLINE
USERS ONLINE
TEMP ONLINE
USER_DATA ONLINE
TEMPORARY_DATA ONLINE
VIGNTABLESPACE ONLINE
SQL> select * from user_ts_quotas;
TABLESPACE_NAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS
------------------------------ ---------- ---------- ---------- ----------
VignTablespace 10240 -1 5 -1
SQL> quit
Disconnected from Oracle7 Server Release 7.3.2.2.0 - Production Release
With the distributed option
PL/SQL Release 2.3.2.2.0 - Production
$
$
#---------------------------------------------------
# This cmd *must* succeed for StoryServer installation
#---------------------------------------------------
$ tnsping VignSID
TNS Ping Utility for Solaris: Version 2.3.2.1.0 - Production on 02-DEC-98 07:34:59
Copyright (c) Oracle Corporation 1995. All rights reserved.
Attempting to contact (ADDRESS=(PROTOCOL=TCP)(HOST=saturday)(PORT=1521))
OK (80 msec)
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
Section 3: Tips on using SQL*Net
#---------------------------------------------------
# Check the listener
#---------------------------------------------------
$ lsnrctl status
Connecting to (ADDRESS=(PROTOCOL=TCP)(HOST=saturday)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Solaris: Version 2.3.2.1.0 - Production
Start Date 03-DEC-98 16:34:04
Uptime 0 days 0 hr. 0 min. 6 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File /var/opt/oracle/listener.ora
Listener Log File /opt/oracle7/network/log/listener.log
Services Summary...
WG73 has 1 service handler(s)
The command completed successfully
# If 2nd-to-last line is not....
<SID> has 1 service handler(s)
...then your LISTENER (/var/opt/oracle/listener.ora) is FUBAR
It should look like this:
###############################################################
###### listener.ora ###########################################
#ls -l /var/opt/oracle/listener.ora
-rw-r--r-- 1 oracle7 dba 1376 Dec 3 14:45 /var/opt/oracle/listener.ora
#
#cat /var/opt/oracle/listener.ora
#--------------- Relevent portion of listener.ora -----------------
#Vanilla installation of WG73 produces:
#TCP_LISTENER=
LISTENER=
(ADDRESS_LIST=
(ADDRESS =
(PROTOCOL = TCP)
(HOST = saturday)
(PORT = 1521)
))
#Vanilla installation of WG73 produces:
#SID_LIST_TCP_LISTENER =
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = WG73)
(ORACLE_HOME = /opt/oracle7)
))
#--------------------------------------------------------------
###############################################################
######## tnsnames.ora #########################################
#
#ls -l /opt/oracle7/network/admin/tnsnames.ora
-rw-r--r-- 1 oracle7 dba 114 Dec 3 13:18 /opt/oracle7/network/admin/tnsnames.ora
#
#ls -l /opt/oracle7/network/admin/tnsnames.ora
#--------------------------- Entire tnsnames.ora ----------------------
WG73=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=saturday)
(PORT=1521))
(CONNECT_DATA=(SID=WG73)))
#--------------------------------------------------------------
###############################################################