{"id":269,"date":"2019-09-02T15:38:52","date_gmt":"2019-09-02T13:38:52","guid":{"rendered":"http:\/\/anjo.pt\/wp\/keyword-oracle\/?p=269"},"modified":"2019-12-02T20:58:44","modified_gmt":"2019-12-02T19:58:44","slug":"install-oracle-locator-in-12-2-and-above","status":"publish","type":"post","link":"https:\/\/anjo.pt\/keyword-oracle\/2019\/09\/02\/install-oracle-locator-in-12-2-and-above\/","title":{"rendered":"Install Oracle Locator in Oracle 12.2"},"content":{"rendered":"\n

[Update: this is not anymore valid for Oracle 19c. To install Oracle Locator on Oracle 19c, check this post<\/a>]<\/p>\n\n\n\n

Oracle Locator is the free subset of Oracle Spatial functions. It is installed in MDSYS schemas. Some applications that use geographical data need Oracle Locator in order to compile.<\/p>\n\n\n\n

There are two ways to install Oracle Locator, an easy and a complex.<\/p>\n\n\n\n

When doing on single-tenant databases and Oracle Locator is only need for one pluggable database, please use the simple way.<\/p>\n\n\n\n

Simple way to install Oracle Locator<\/h3>\n\n\n\n

In this case we install Locator directly on the pluggable database.<\/p>\n\n\n

\nSQL> alter session set container=XPTO;\nSQL> create user mdsys identified by "XXXXX" default tablespace sysaux;\n\n@?\/md\/admin\/mdprivs.sql\n@?\/md\/admin\/catmdloc.sql\nSQL> alter user mdsys password expire;\nSQL> alter user mdsys account lock;\n\nSQL> col comp_name for a50\nSQL> select comp_id,comp_name,status from dba_registry;\n\nCOMP_ID                        COMP_NAME                                          STATUS\n------------------------------ -------------------------------------------------- -----------\nCATALOG                        Oracle Database Catalog Views                      VALID\nCATPROC                        Oracle Database Packages and Types                 VALID\nXDB                            Oracle XML Database                                VALID\nOWM                            Oracle Workspace Manager                           VALID\n\n<\/pre><\/div>\n\n\n
Complex way to install Oracle Locator<\/h3>\n\n\n\n
When you have a multitnenant environment and you want that Locator is available on all future PDBs that you create, the following method is to use. Unfortunately there are a few bugs (on 12.2 at least) when I tried this. <\/p>\n\n\n\n
We need to install Oracle Spatial first and then remove Oracle Spatial and leaving Locator only objects.<\/p>\n\n\n\n
I start with a fairly empty CDB, without JAVAVM. This must first be installed. I’ve already downloaded the scripts from the note described below, so I don’t go much in detail.<\/p>\n\n\n
\nset lines 200\nselect comp_id,version, status from dba_registry;\n\nCOMP_ID                        VERSION                        STATUS\n------------------------------ ------------------------------ --------------------------------------------\nCATALOG                        12.2.0.1.0                     VALID\nCATPROC                        12.2.0.1.0                     VALID\nXDB                            12.2.0.1.0                     VALID\nOWM                            12.2.0.1.0                     VALID\n\n\n-- Install JAVAVM, XDB, XML - Repairing\/Reinstalling JVM To A Multitenant Database (CDB and all PDBs) (Doc ID 2262919.1):\n==========================================================================================================================\n\nINSTALL_DIR=\/temp\/rdms122_java\ncp $INSTALL_DIR\/reinitjvm.sql $ORACLE_HOME\/javavm\/install\nsqlplus \/ as sysdba &lt;&lt; EOF\nalter profile default limit password_verify_function null;\n@$INSTALL_DIR\/fulljvminstall.sql\nshutdown immediate;\nEOF\n\n-- Oracle restart starts back the database automatically\n\nsqlplus \/ as sysdba &lt;&lt; EOF\nhost $ORACLE_HOME\/perl\/bin\/perl $ORACLE_HOME\/rdbms\/admin\/catcon.pl -n 1 -e -b utlrp_ -d $ORACLE_HOME\/rdbms\/admin utlrp.sql\nhost $ORACLE_HOME\/perl\/bin\/perl $ORACLE_HOME\/rdbms\/admin\/catcon.pl -l $INSTALL_DIR -n 1 -b post_jvm_installation_info_ $INSTALL_DIR\/jvm_info.sql;\nset lines 200\nselect comp_id,version, status from dba_registry;\nEOF\n\n...\n\nCOMP_ID                        VERSION                        STATUS\n------------------------------ ------------------------------ --------------------------------------------\nCATALOG                        12.2.0.1.0                     VALID\nCATPROC                        12.2.0.1.0                     VALID\nXML                            12.2.0.1.0                     VALID\nJAVAVM                         12.2.0.1.0                     VALID\nCATJAVA                        12.2.0.1.0                     VALID\nXDB                            12.2.0.1.0                     VALID\nOWM                            12.2.0.1.0                     VALID\n<\/pre><\/div>\n\n\n
Next step is to install Oracle Multimedia, which is also required by Spatial:<\/p>\n\n\n
\n$ORACLE_HOME\/perl\/bin\/perl $ORACLE_HOME\/rdbms\/admin\/catcon.pl -l ${ORACLE_BASE}\/admin\/${ORACLE_SID}\/create\/logs -d $ORACLE_HOME\/md\/admin -b mdinst mdinst.sql\n\nSQL> select comp_id,version, status from dba_registry;\n\nCOMP_ID                        VERSION                        STATUS\n------------------------------ ------------------------------ --------------------------------------------\nCATALOG                        12.2.0.1.0                     VALID\nCATPROC                        12.2.0.1.0                     VALID\nXML                            12.2.0.1.0                     VALID\nJAVAVM                         12.2.0.1.0                     VALID\nCATJAVA                        12.2.0.1.0                     VALID\nXDB                            12.2.0.1.0                     VALID\nOWM                            12.2.0.1.0                     VALID\nORDIM                          12.2.0.1.0                     VALID\nSDO                            12.2.0.1.0                     VALID\n<\/pre><\/div>\n\n\n
And finally we install Spatial and remove it again, leaving Locator<\/p>\n\n\n
\n$ORACLE_HOME\/perl\/bin\/perl $ORACLE_HOME\/rdbms\/admin\/catcon.pl -l ${ORACLE_BASE}\/admin\/${ORACLE_SID}\/create\/logs -d $ORACLE_HOME\/md\/admin -b mddins mddins.sql\n\n$ORACLE_HOME\/perl\/bin\/perl $ORACLE_HOME\/rdbms\/admin\/catcon.pl -n 1 -e -b utlrp_ -d $ORACLE_HOME\/rdbms\/admin utlrp.sql\n\n\nSQL> select comp_id,version, status from dba_registry;\n\nCOMP_ID                        VERSION                        STATUS\n------------------------------ ------------------------------ --------------------------------------------\nCATALOG                        12.2.0.1.0                     VALID\nCATPROC                        12.2.0.1.0                     VALID\nXML                            12.2.0.1.0                     VALID\nJAVAVM                         12.2.0.1.0                     VALID\nCATJAVA                        12.2.0.1.0                     VALID\nXDB                            12.2.0.1.0                     VALID\nOWM                            12.2.0.1.0                     VALID\nORDIM                          12.2.0.1.0                     VALID\nSDO                            12.2.0.1.0                     REMOVED\n<\/pre><\/div>\n\n\n
There were some objects invalid, which match the Oracle note: “Invalid MDSYS objects after running mddins.sql to keep Locator option on 12.2.0.1 (Doc ID 2307454.1)”<\/p>\n\n\n
\nvi \/tmp\/bug2307454.sql\ndrop function mdsys.NDM_RESET_ID_GENERATOR;\n@$ORACLE_HOME\/md\/admin\/semremov.sql\n\n$ORACLE_HOME\/perl\/bin\/perl $ORACLE_HOME\/rdbms\/admin\/catcon.pl -l ${ORACLE_BASE}\/admin\/${ORACLE_SID}\/create\/logs -d \/tmp -b bug2307454 bug2307454.sql\n\n$ORACLE_HOME\/perl\/bin\/perl $ORACLE_HOME\/rdbms\/admin\/catcon.pl -n 1 -e -b utlrp_ -d $ORACLE_HOME\/rdbms\/admin utlrp.sql\n<\/pre><\/div>\n\n\n
There were still some invalid public synonyms left. I opened an SR, and Oracle said they can be removed:<\/p>\n\n\n
\nSQL> set lines 200\nSQL> set pages 40\nSQL> col owner for a20\nSQL> col object_name for a50\nSQL> col object_type for a30\nSQL> select owner,object_name,object_type,con_id,sharing,status from cdb_objects where status!='VALID' and owner!='MLOG' order by 1,2;\n\nOWNER                OBJECT_NAME                                        OBJECT_TYPE                        CON_ID SHARING            STATUS\n-------------------- -------------------------------------------------- ------------------------------ ---------- ------------------ -------\nPUBLIC               ALL_SDO_GEOR_SYSDATA                               SYNONYM                                 3 METADATA LINK      INVALID\n...\n21 rows selected.\n\nalter session set "_ORACLE_SCRIPT" = true;\nBEGIN\nFOR cmdline IN (select 'drop public synonym '|| object_name cmd from dba_objects where status!='VALID' and owner='PUBLIC') LOOP\ndbms_output.put_line(cmdline.cmd);\nexecute immediate(cmdline.cmd); \nEND LOOP;\nEND;\n\/\nalter session set "_ORACLE_SCRIPT" = true;\n<\/pre><\/div>\n\n\n
And that’s it. A complex way to install Locator…<\/p>\n","protected":false},"excerpt":{"rendered":"
[Update: this is not anymore valid for Oracle 19c. To install Oracle Locator on Oracle 19c, check this post] Oracle Locator is the free subset of Oracle Spatial functions. It is installed in MDSYS schemas. Some applications that use geographical data need Oracle Locator in order to compile. There are two ways to install Oracle […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7,39,45],"tags":[],"class_list":{"0":"post-269","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-oracle-12c","7":"category-oracle-19c","8":"category-oracle-locator","9":"czr-hentry"},"_links":{"self":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/269","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/comments?post=269"}],"version-history":[{"count":3,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/269\/revisions"}],"predecessor-version":[{"id":282,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/269\/revisions\/282"}],"wp:attachment":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/media?parent=269"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/categories?post=269"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/tags?post=269"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}