Forum OpenACS Development: Fresh install of OpenACS 5.10 on Oracle 19c

Very interesting, Gustaf! Thanks for taking the time to publish this.

Brian

We are now at 47 changes for Oracle, more and more is working. When someone wants to work with this, make sure to get an updated version of nsoracle [1]. The recent changes add timestamps as data types for output columns to the driver. It seems that in the past, the Oracle developers were just returning output fields of type DATE.

Supporting timestamps is necessary when connecting to external databases, and one gets a much better compatibility with the PostgreSQL version. The output format for timestamps can be controlled very similar to DATE via the environment variables NLS_TIMESTAMP_FORMAT and NLS_TIMESTAMP_TZ_FORMAT (see [2](.

all the best
-g

[1] https://bitbucket.org/naviserver/nsoracle/
[2] https://bitbucket.org/naviserver/naviserver/commits/926407346d07412c3cd9814a7229a89881c8c7f0

Dear Raul,

I cannot comment on the vagrant machine setup from the tutorial, but I have recently installed an OpenACS instance together with the Oracle driver and this worked fine (note that it was a Postgres OpenACS using the Oracle driver only to access some tables from a remote Oracle and no vagrant was involved).

Summarizing, these were the steps (on the machine running NaviServer):
* download basic and sdk from https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html
* compile nsoracle located in the NaviServer scr folder following the instructions in the nsoracle README (see also the tutorial for pointers). Basically, you want to point the compiler to your basic and sdk installation from before.

It could be that the makefile does not find the basic and sdk installations and some massaging of the paths will do.

Hope this helps

Compiled nsoracle using the basic client and sdk for 19c. Installed the client on RHEL8 VM and connected to a different VM with 19c. RHEL8 did not have CVS in default repositories and had to find it for installation script. I have been fixing the errors along the way.

loadjava error:
--------------------------------------
[07/Oct/2022:14:50:10][74372.7fc2b88ed700][-conn:oacs-5-10-0:default:0:1-] Error: apm_package_install: Error installing ACS Content Repository version 5.10.1d4: couldn't execute "/usr/lib/oracle/21/client64/bin/loadjava": no such file or directory
:    couldn't execute "/usr/lib/oracle/21/client64/bin/loadjava": no such file or directory
:        while executing
:    "open "|[ad_file join $::env(ORACLE_HOME) bin loadjava] -verbose -user $user_pass $file" "r""
:        (procedure "::nsf::procs::db_source_sqlj_file" line 3)
:        invoked from within
:    "db_source_sqlj_file -callback $callback "$path/$file_path""
:        (procedure "::nsf::procs::apm_package_install_data_model" line 38)
:        invoked from within
:    "apm_package_install_data_model -callback $callback -data_model_files $data_model_files $spec_file_path"
:        ("::try" body line 55)
--------------------------------------
Resolved by installing the full client, install oacs on same machine as DB, or manually skipping/loading sqlj files.

Updated /var/www/oacs-5-10-0/openacs-4/packages/acs-tcl/tcl/site-nodes-procs.tcl
--------------------------------------
108c108,112
\<        acs::dc call site_node delete -node_id $node_id
---
\>        if {[db_driverkey ""] eq "oracle"} {
\>            acs::dc call site_node del -node_id $node_id
\>        } else {
\>            acs::dc call site_node delete -node_id $node_id
\>        }
612c616
\<                      select child.node_id, child.parent_id from site_node_tree, site_nodes as child
---
\>                      select child.node_id, child.parent_id from site_node_tree, site_nodes child
1006c1010
\<                      select child.node_id, child.parent_id, child.object_id from site_node_tree, site_nodes as child
---
\>                      select child.node_id, child.parent_id, child.object_id from site_node_tree, site_nodes child
1014a1019,1023
\>                    if { $with_subtree } {
\>                        set sql [string map [list ":with_subtree" "1 = 1"] $sql]
\>                    } else {
\>                        set sql [string map [list ":with_subtree" "1 = 0"] $sql]
\>                    }
--------------------------------------

Updated /var/www/oacs-5-10-0/openacs-4/packages/acs-subsite/tcl/subsite-procs.tcl
--------------------------------------
1106c1106
\<        where  not (singleton_p and exists (select 1 from apm_packages
---
\>        where  not (singleton_p = 't' and exists (select 1 from apm_packages
1253,1254c1253,1254
\<            order by host = :search_vhost desc
\<            fetch first 1 row only
---
\>         order by case when host = :search_vhost then 2 else 1 end desc
\>         fetch first 1 row only
--------------------------------------

http://localhost:8000/admin/applications/
Updated /var/www/oacs-5-10-0/openacs-4/packages/acs-subsite/www/admin/applications/index.tcl
--------------------------------------
85c85
\<            where_clause {(:search is null or upper(coalesce(coalesce(m.message, md.message), p.instance_name) || n.name || pt.pretty_name) like '%' || upper(:search) || '%')}
---
\>            where_clause {(:search is null or upper(coalesce(coalesce(m.message, md.message), to_clob(p.instance_name)) || n.name || pt.pretty_name) like '%' || upper(:search) || '%')}
--------------------------------------

http://localhost:8000/admin/applications/
Updated /var/www/oacs-5-10-0/openacs-4/packages/acs-subsite/www/admin/applications/index-oracle.xql
--------------------------------------
22c22
\<    and [template::list::filter_where_clauses -and -name applications]
---
\>    [template::list::filter_where_clauses -and -name applications]
--------------------------------------

http://localhost:8000/admin/site-map/site-map
Updated /var/www/oacs-5-10-0/openacs-4/packages/acs-subsite/www/admin/site-map/site-map.tcl
--------------------------------------
58,71c58
\< db_foreach path_select {
\<    WITH RECURSIVE site_node_path AS (
\<        select node_id, parent_id, name, object_id, directory_p, 1 as level
\<        from site_nodes where node_id = :root_id
\<    UNION ALL
\<        select c.node_id, c.parent_id, c.name, c.object_id, c.directory_p, p.level+1
\<        from site_node_path p, site_nodes as c where  c.node_id = p.parent_id
\<    )
\<    select
\<        node_id, name, directory_p, level,
\<        acs_object.name(object_id) as obj_name,
\<        acs_permission.permission_p(object_id, :user_id, 'admin') as admin_p
\<    from  site_node_path order by level desc
\< } {
---
\> db_foreach path_select {} {
333c320
\<    and acs_permission.permission_p(package_id, :user_id,'admin')
---
\>    and acs_permission.permission_p(package_id, :user_id,'admin') = 't'
--------------------------------------

Updated /var/www/oacs-5-10-0/openacs-4/packages/acs-subsite/www/admin/site-map/site-map-postgresql.xql
--------------------------------------
43a44,64
\>
\>    \<fullquery name="path_select"\>
\>        \<rdbms\>\<type\>postgresql\</type\>\<version\>8.4\</version\>\</rdbms\>
\>        \<querytext\>
\>    WITH RECURSIVE site_node_path AS (
\>        select node_id, parent_id, name, object_id, directory_p, 1 as level
\>        from site_nodes where node_id = :root_id
\>    UNION ALL
\>        select c.node_id, c.parent_id, c.name, c.object_id, c.directory_p, p.level+1
\>        from site_node_path p, site_nodes as c where  c.node_id = p.parent_id
\>    )
\>    select
\>        node_id, name, directory_p, level,
\>        acs_object.name(object_id) as obj_name,
\>        acs_permission.permission_p(object_id, :user_id, 'admin') as admin_p
\>    from  site_node_path order by level desc
\>        \</querytext\>
\>    \</fullquery\>
\>
\>
\>
--------------------------------------

Updated /var/www/oacs-5-10-0/openacs-4/packages/acs-subsite/www/admin/site-map/site-map-oracle.xql
--------------------------------------
52a53,69
\>    \<fullquery name="path_select"\>
\>        \<querytext\>
\>    WITH site_node_path(node_id,parent_id,name,object_id,directory_p,mylevel) AS (
\>        select node_id, parent_id, name, object_id, directory_p, 1 as mylevel
\>        from site_nodes where node_id = :root_id
\>    UNION ALL
\>        select c.node_id, c.parent_id, c.name, c.object_id, c.directory_p, p.mylevel+1 mylevel
\>        from site_node_path p, site_nodes c where  c.node_id = p.parent_id
\>    )
\>    select
\>        node_id, name, directory_p, mylevel,
\>        acs_object.name(object_id) as obj_name,
\>        acs_permission.permission_p(object_id, :user_id, 'admin') as admin_p
\>    from  site_node_path order by mylevel desc
\>        \</querytext\>
\>    \</fullquery\>
\>
--------------------------------------

http://localhost:8000/admin/themes/
Updated /var/www/oacs-5-10-0/openacs-4/packages/acs-subsite/www/admin/themes/index.xql
--------------------------------------
4d3
\<    \<rdbms\>\<type\>postgresql\</type\>\<version\>7.1\</version\>\</rdbms\>
12c11
\< \</queryset\>
\ No newline at end of file
---
\> \</queryset\>
--------------------------------------

http://localhost:8000/acs-lang/admin/
Updated /var/www/oacs-5-10-0/openacs-4/packages/acs-lang/www/admin/index.xql
--------------------------------------
17,20c17,20
\<                case when num_messages is null          then 0 else num_messages end,
\<                case when num_translated is null        then 0 else num_translated end,
\<                case when num_untranslated is null      then 0 else num_untranslated end,
\<                case when num_deleted is null          then 0 else num_deleted end
---
\>             case when num_messages is null          then 0 else num_messages end as num_messages,
\>             case when num_translated is null        then 0 else num_translated end as num_translated,
\>             case when num_untranslated is null      then 0 else num_untranslated end as num_untranslated,
\>             case when num_deleted is null          then 0 else num_deleted end as num_deleted
52a53,56
\>         al.label,
\>             language,
\>             default_p,
\>             enabled_p,
--------------------------------------

http://localhost:8000/admin/groups/?view_by=rel_type
Updated /var/www/oacs-5-10-0/openacs-4/packages/acs-subsite/www/admin/groups/elements-by-rel-type.tcl
--------------------------------------
44c44
\<        g.group_id = null as rel_type_valid_p,
---
\>        null as rel_type_valid_p,
54,55c54,55
\<          left outer join rel_segments s using (group_id, rel_type)
\<          left outer join group_rels gr using (group_id, rel_type)
---
\>          left outer join rel_segments s on (s.group_id = g.group_id and s.rel_type = g.rel_type)
\>          left outer join group_rels gr on (gr.group_id = g.group_id and gr.rel_type = g.rel_type)
68c68
\<          ) rels using (rel_type),
---
\>          ) rels on (rels.rel_type = g.rel_type),
--------------------------------------

Add file:
http://localhost:8000/admin/group-types/
Updated /var/www/oacs-5-10-0/openacs-4/packages/acs-subsite/www/admin/group-types/index-oracle.xql
--------------------------------------
\<?xml version="1.0"?\>

\<queryset\>
  \<rdbms\>\<type\>oracle\</type\>\<version\>8.1.6\</version\>\</rdbms\>

\<fullquery name="select_group_types"\>
      \<querytext\>
    with group_types(object_type,pretty_plural,mylevel) as (
        select object_type, pretty_plural, 0 as mylevel
          from acs_object_types
        where object_type = 'group'

        union all

        select t.object_type, t.pretty_plural, s.mylevel + 1 as mylevel
          from acs_object_types t,
              group_types s
        where t.supertype = s.object_type
    )
    select t.object_type as group_type, t.pretty_plural,
          coalesce(num.number_groups,0) as number_groups,
          t.mylevel * 4 as indent
      from group_types t left outer join
          (select object_type, count(group_id) as number_groups from
            (select distinct o.object_type, g.group_id
                from groups g, acs_objects o,
                    application_group_element_map app_group
              where acs_permission.permission_p(g.group_id, :user_id, 'read') = 't'
                and o.object_id = g.group_id
                and ((app_group.package_id = :package_id and app_group.element_id = g.group_id)
                -- the or-clause below is just needed for "Registered Users" (-2)
                -- which is an application group for e.g. a subsite,
                -- but not mapped to it via application_group_element_map
                or  (o.object_id = :registered_users)
            )) counts
            group by object_type) num
            on (num.object_type = t.object_type)
    order by indent asc
      \</querytext\>
\</fullquery\>
\</queryset\>
--------------------------------------

http://localhost:8000/admin/group-types/one?group_type=group
Updated /var/www/oacs-5-10-0/openacs-4/packages/acs-subsite/www/admin/group-types/one.tcl
--------------------------------------
63,85c63
\< db_multirow -extend {one_attribute_url} attributes attributes_select {
\<    with recursive group_hierarchy as (
\<        select object_type, pretty_name, 1 as type_level
\<        from acs_object_types
\<        where object_type = 'group'
\<
\<        union all
\<
\<        select t.object_type, t.pretty_name, h.type_level + 1 as type_level
\<        from acs_object_types t,
\<            group_hierarchy h
\<        where t.supertype = h.object_type
\<    )
\<    select a.attribute_id,
\<            a.pretty_name,
\<            a.ancestor_type,
\<            t.pretty_name as ancestor_pretty_name
\<      from acs_object_type_attributes a,
\<            group_hierarchy t
\<      where a.object_type = :group_type
\<        and t.object_type = a.ancestor_type
\<    order by type_level
\< } {
---
\> db_multirow -extend {one_attribute_url} attributes attributes_select { } {
--------------------------------------

Updated /var/www/oacs-5-10-0/openacs-4/packages/acs-subsite/www/admin/group-types/one-postgresql.xql
--------------------------------------
22a23,48
\> \<fullquery name="attributes_select"\>
\>      \<querytext\>
\>    with recursive group_hierarchy as (
\>        select object_type, pretty_name, 1 as type_level
\>        from acs_object_types
\>        where object_type = 'group'
\>
\>        union all
\>
\>        select t.object_type, t.pretty_name, h.type_level + 1 as type_level
\>        from acs_object_types t,
\>            group_hierarchy h
\>        where t.supertype = h.object_type
\>    )
\>    select a.attribute_id,
\>            a.pretty_name,
\>            a.ancestor_type,
\>            t.pretty_name as ancestor_pretty_name
\>      from acs_object_type_attributes a,
\>            group_hierarchy t
\>      where a.object_type = :group_type
\>        and t.object_type = a.ancestor_type
\>    order by type_level
\>      \</querytext\>
\> \</fullquery\>
\>
--------------------------------------

Updated /var/www/oacs-5-10-0/openacs-4/packages/acs-subsite/www/admin/group-types/one-oracle.xql
--------------------------------------
25a26,50
\> \<fullquery name="attributes_select"\>
\>      \<querytext\>
\>    with group_hierarchy(object_type,pretty_name,type_level) as (
\>        select object_type, pretty_name, 1 as type_level
\>        from acs_object_types
\>        where object_type = 'group'
\>
\>        union all
\>
\>        select t.object_type, t.pretty_name, h.type_level + 1 as type_level
\>        from acs_object_types t,
\>            group_hierarchy h
\>        where t.supertype = h.object_type
\>    )
\>    select a.attribute_id,
\>            a.pretty_name,
\>            a.ancestor_type,
\>            t.pretty_name as ancestor_pretty_name
\>      from acs_object_type_attributes a,
\>            group_hierarchy t
\>      where a.object_type = :group_type
\>        and t.object_type = a.ancestor_type
\>    order by type_level
\>      \</querytext\>
\> \</fullquery\>
--------------------------------------

Has anyone found a way to use encryption to replace the use of plain text oracle database password in the config file?

Thank you

I was reviewing the logs and found 'errno 11'. Does anyone have any information on this?

Thank you

[04/Dec/2022:17:57:57][13704.7fe68a1e4700][-conn:oacs-5-10-0:default:0:18-] Debug(request): end GET /resources/acs-templating/forms
.css HTTP/1.1
[04/Dec/2022:17:57:57][13704.7fe68a1e4700][-conn:oacs-5-10-0:default:0:18-] Debug(ns:driver): NsGetRequest got the pre-parsed reque
st GET /resources/acs-subsite/action-link-marker.png HTTP/1.1 from the driver
[04/Dec/2022:17:57:57][13704.7fe6889e1700][-writer1-] Debug(ns:driver): ### Writer poll collect 0x7fe661bd3e80 size 14181 streaming
0 rateLimit 0
[04/Dec/2022:17:57:57][13704.7fe6889e1700][-writer1-] Debug(ns:driver): ### Writer final pollTimeout -1
[04/Dec/2022:17:57:57][13704.7fe6889e1700][-writer1-] Debug(ns:driver): Socket of pool '' is writable, writer limit 0 nsent 0
[04/Dec/2022:17:57:57][13704.7fe67bfff700][-writer0-] Debug(ns:driver): ### Writer poll collect 0x7fe661bd4080 size 1851 streaming
0 rateLimit 0
[04/Dec/2022:17:57:57][13704.7fe67bfff700][-writer0-] Debug(ns:driver): ### Writer final pollTimeout -1
[04/Dec/2022:17:57:57][13704.7fe67bfff700][-writer0-] Debug(ns:driver): Socket of pool '' is writable, writer limit 0 nsent 0
[04/Dec/2022:17:57:57][13704.7fe67bfff700][-writer0-] Debug(ns:driver): ### Writer 0x7fe661bd4080 can write to client fd 14 (trigge
r 0) streaming 000000 size 1851 nsent 0 bufsize 349
[04/Dec/2022:17:57:57][13704.7fe67bfff700][-writer0-] Debug(ns:driver): ### WriterReadFromSpool [0]: fd 27 tosend 1502 files 0
[04/Dec/2022:17:57:57][13704.7fe67bfff700][-writer0-] Debug(ns:driver): ### WriterReadFromSpool 0x7fe661bd4080 1030000 leftover 349
offset 0
[04/Dec/2022:17:57:57][13704.7fe67bfff700][-writer0-] Debug(ns:driver): Writer 0x7fe661bd4080 done OK (size 0) => RELEASE
[04/Dec/2022:17:57:57][13704.7fe67bfff700][-writer0-] Debug(ns:driver): WriterSockRelease 0x7fe661bd4080 refCount 0 keep 1
[04/Dec/2022:17:57:57][13704.7fe67bfff700][-writer0-] Debug(ns:driver): Writer: closed sock 14, file fd 27, error 0/0, sent=1851, f
lags=1030000
[04/Dec/2022:17:57:57][13704.7fe67bfff700][-writer0-] Debug(ns:driver): NsSockClose sockPtr 0x7fe67400ab40 (14) keep 1
[04/Dec/2022:17:57:57][13704.7fe67bfff700][-writer0-] Debug(ns:driver): NsSockClose calls RequestFree
[04/Dec/2022:17:57:57][13704.7fe67bfff700][-writer0-] Debug(ns:driver): === RequestFree cleans 0x7fe63906e170 (avail 0 keep 1 lengt
h 0 contentLength 0)
[04/Dec/2022:17:57:57][13704.7fe67bfff700][-writer0-] Debug(ns:driver): RequestFree does not call Ns_ResetRequest on 0x7fe63906e178
[04/Dec/2022:17:57:57][13704.7fe67bfff700][-writer0-] Debug(ns:driver): === Push request structure 0x7fe63906e170 in (to pool)
[04/Dec/2022:17:57:57][13704.7fe67bfff700][-writer0-] Debug(ns:driver): ### Writer final pollTimeout 30000
[04/Dec/2022:17:57:57][13704.7fe6889e1700][-writer1-] Debug(ns:driver): ### Writer 0x7fe661bd3e80 can write to client fd 24 (trigge
r 0) streaming 000000 size 14181 nsent 0 bufsize 350
[04/Dec/2022:17:57:57][13704.7fe6889e1700][-writer1-] Debug(ns:driver): ### WriterReadFromSpool [0]: fd 26 tosend 13831 files 0
[04/Dec/2022:17:57:57][13704.7fe6889e1700][-writer1-] Debug(ns:driver): ### WriterReadFromSpool 0x7fe661bd3e80 1030000 leftover 350
offset 0
[04/Dec/2022:17:57:57][13704.7fe6891e2700][-driver:nssock:0-] Debug(ns:driver): === PollWait returned 1, trigger[0] 1
[04/Dec/2022:17:57:57][13704.7fe6891e2700][-driver:nssock:0-] Debug(ns:driver): setting keepwait 5.000000 for socket 14
[04/Dec/2022:17:57:57][13704.7fe6891e2700][-driver:nssock:0-] Debug(ns:driver): === PollWait returned 1, trigger[0] 0
[04/Dec/2022:17:57:57][13704.7fe6891e2700][-driver:nssock:0-] Debug(ns:driver): Peer has closed 0x7fe67400ab40
[04/Dec/2022:17:57:57][13704.7fe6891e2700][-driver:nssock:0-] Debug(ns:driver): SockRelease reason SOCK_CLOSE err 0 (sock 14)
[04/Dec/2022:17:57:57][13704.7fe6889e1700][-writer1-] Debug(ns:driver): Writer 0x7fe661bd3e80 continue OK (size 5989) => PUSH
[04/Dec/2022:17:57:57][13704.7fe6889e1700][-writer1-] Debug(ns:driver): ### Writer poll collect 0x7fe661bd3e80 size 5989 streaming
0 rateLimit 0
[04/Dec/2022:17:57:57][13704.7fe6889e1700][-writer1-] Debug(ns:driver): ### Writer final pollTimeout -1
[04/Dec/2022:17:57:57][13704.7fe6889e1700][-writer1-] Debug(ns:driver): ### Writer 0x7fe661bd3e80 reached POLLHUP fd 24
[04/Dec/2022:17:57:57][13704.7fe6889e1700][-writer1-] Debug(ns:driver): Writer 0x7fe661bd3e80 fd 24 release, not OK (status -2) =>
RELEASE
[04/Dec/2022:17:57:57][13704.7fe6889e1700][-writer1-] Debug(ns:driver): WriterSockRelease 0x7fe661bd3e80 refCount 0 keep 1
[04/Dec/2022:17:57:57][13704.7fe6889e1700][-writer1-] Debug(ns:driver): Writer: closed sock 24, file fd 26, error -2/0, sent=8192,
flags=1030000
[04/Dec/2022:17:57:57][13704.7fe6889e1700][-writer1-] Debug(ns:driver): NsSockClose sockPtr 0x7fe67400ad00 (24) keep 0
[04/Dec/2022:17:57:57][13704.7fe6889e1700][-writer1-] Debug(ns:driver): NsSockClose calls RequestFree
[04/Dec/2022:17:57:57][13704.7fe6889e1700][-writer1-] Debug(ns:driver): === RequestFree cleans 0x7fe638709550 (avail 0 keep 0 lengt
h 0 contentLength 0)
[04/Dec/2022:17:57:57][13704.7fe6889e1700][-writer1-] Debug(ns:driver): RequestFree does not call Ns_ResetRequest on 0x7fe638709558
[04/Dec/2022:17:57:57][13704.7fe6889e1700][-writer1-] Debug(ns:driver): === Push request structure 0x7fe638709550 in (to pool)
[04/Dec/2022:17:57:57][13704.7fe6889e1700][-writer1-] Debug(ns:driver): ### Writer final pollTimeout 30000
[04/Dec/2022:17:57:57][13704.7fe6891e2700][-driver:nssock:0-] Debug(ns:driver): === PollWait returned 1, trigger[0] 1
[04/Dec/2022:17:57:57][13704.7fe6891e2700][-driver:nssock:0-] Debug(ns:driver): SockRelease reason SOCK_CLOSE err 11 (sock -1)
[04/Dec/2022:17:57:57][13704.7fe6891e2700][-driver:nssock:0-] Debug(ns:driver): SockRelease bypasses SockClose, since we have an invalid socket
[04/Dec/2022:17:57:57][13704.7fe6891e2700][-driver:nssock:0-] Debug(ns:driver): DRIVER SockRelease: errno 11 drvPtr->closewait 2.000000
[04/Dec/2022:17:57:57][13704.7fe68a1e4700][-conn:oacs-5-10-0:default:0:19-] Dev: config: ns/server/oacs-5-10-0/acs:ExcludedFiles value= (string)
[04/Dec/2022:17:57:57][13704.7fe68a1e4700][-conn:oacs-5-10-0:default:0:19-] Dev: config: ns/server/oacs-5-10-0/acs:ResourcesExpireInterval value= (string)
[04/Dec/2022:17:57:57][13704.7fe68a1e4700][-conn:oacs-5-10-0:default:0:19-] Debug(ns:driver): NsWriterQueue: size 181 bufs (nil) (0) flags 1030000 stream 000000 chan (nil) fd 14 thread 2
[04/Dec/2022:17:57:57][13704.7fe68a1e4700][-conn:oacs-5-10-0:default:0:19-] Debug(ns:driver): NsWriterQueue: file is too small(181 < 1024)