{"id":810,"date":"2022-11-18T09:18:00","date_gmt":"2022-11-18T09:18:00","guid":{"rendered":"http:\/\/wp.chaoyu.nl\/?p=810"},"modified":"2023-02-12T21:40:08","modified_gmt":"2023-02-12T21:40:08","slug":"migrate-apex-to-autonomous-database-with-custom-ords-notes","status":"publish","type":"post","link":"https:\/\/chaoyu.nl\/?p=810","title":{"rendered":"Migrate APEX to Autonomous Database with custom ORDS (notes)"},"content":{"rendered":"\n<ul class=\"wp-block-list\">\n<li>OCI Identity Concepts \n\n\n\n\n\n\n<ul class=\"wp-block-list\">\n<li>create user <\/li>\n\n\n\n<li>create group (admin \/ user )<\/li>\n\n\n\n<li>assign users to group<\/li>\n\n\n\n<li>create policies ( enable [<strong>GROUP<\/strong> \/ <strong>SERVICE<\/strong>] to [manage\/ inspect\/ use\/ read ] resources )\n<ul class=\"wp-block-list\">\n<li> Click <a rel=\"noreferrer noopener\" href=\"https:\/\/docs.oracle.com\/en-us\/iaas\/Content\/Identity\/Concepts\/commonpolicies.htm\" data-type=\"URL\" data-id=\"https:\/\/docs.oracle.com\/en-us\/iaas\/Content\/Identity\/Concepts\/commonpolicies.htm\" target=\"_blank\">here<\/a> to see more common policies<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li>create compartments ( logical isolation) <\/li>\n\n\n\n<li>within compartments create resources<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-full is-style-default\"><img loading=\"lazy\" decoding=\"async\" width=\"1354\" height=\"516\" src=\"http:\/\/wp.chaoyu.nl\/wp-content\/uploads\/2022\/11\/image.png\" alt=\"OCI identity concepts\" class=\"wp-image-812\"\/><\/figure>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Virtual Network\n<ul class=\"wp-block-list\">\n<li>All OCI resources needs to reside within a virtual network<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Create And Manage Your Custom ORDs<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Create a ATP in a private Subnet<\/li>\n\n\n\n<li>In Subnet Security List open Port 1521, 1522 and 8080<\/li>\n\n\n\n<li>Create VM (oracle linux cloud developer) this image has sqlplus installed in Private Or Public Subnet<\/li>\n\n\n\n<li>Open Port 8080 with firewall-cmd (opc user)<\/li>\n\n\n\n<li>reload Firewall-cmd (opc user)<\/li>\n\n\n\n<li>download your ATP wallet and store it in \/tmp folder on VM (opc user)<\/li>\n\n\n\n<li>create folder \/opt\/oracle\/network\/admin  (oracle user)<\/li>\n\n\n\n<li>unzip \/tmp\/wallet **** .zip to \/opt\/oracle\/network\/admin (oracle user)<\/li>\n\n\n\n<li>write : export TNS_ADMIN=\/opt\/oracle\/network\/admin(oracle user)<\/li>\n\n\n\n<li>cd $TNS_ADMIN (oracle user)<\/li>\n\n\n\n<li>nano sqlnet.ora (oracle user)<\/li>\n\n\n\n<li>and change the location of the wallet, which should be the PWD (current working directory) (oracle user)<\/li>\n\n\n\n<li>sqlplus admin@YOURADMINPASS (oracle user)<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code has-small-font-size\"><code lang=\"sql\" class=\"language-sql line-numbers\">-- 1. Create the database user.\ncreate user custom_ords_public_user identified by YOURPASSWORD;\n\n-- 2. Allow the user to connect to the database.\ngrant connect to custom_ords_public_user;\n\n-- 3. Perform some magic.\nbegin\n    ords_admin.provision_runtime_role(\n        p_user =&gt; 'CUSTOM_ORDS_PUBLIC_USER'\n        , p_proxy_enabled_schemas =&gt; true\n    );\nend;\n\/<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">credits goes two these two links<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/fuzziebrain.com\/content\/id\/2004\/\">https:\/\/fuzziebrain.com\/content\/id\/2004\/<\/a><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/www.linkedin.com\/pulse\/run-your-oracle-apex-apps-autonomous-database-own-domain-kurt-liu\/\">https:\/\/www.linkedin.com\/pulse\/run-your-oracle-apex-apps-autonomous-database-own-domain-kurt-liu\/<\/a><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>also set the CDN image location<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code has-small-font-size\"><code lang=\"sql\" class=\"language-sql line-numbers\">begin \n  apex_instance_admin.set_parameter(\n      p_parameter =&gt; 'IMAGE_PREFIX',\n      p_value     =&gt; 'https:\/\/static.oracle.com\/cdn\/apex\/22.2.0\/');\n  commit;\nend;\n\/\n# Use the following to verify:\nSELECT apex_instance_admin.get_parameter('IMAGE_PREFIX') FROM dual;<\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li>and to reset it (in case u need it )<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code has-small-font-size\"><code lang=\"sql\" class=\"language-sql line-numbers\">-- On-Premise Instance.\nbegin\n  apex_instance_admin.set_parameter\n   (p_parameter =&gt; 'IMAGE_PREFIX',   \n    p_value     =&gt; '\/i\/' );\n   commit;   \n end;\n\/\n-- Autonomous example for APEX 22.1\n-- Note: The value can change after Oracle applies patch sets 22.1.0, 22.1.1, etc.\nbegin\n  apex_instance_admin.set_parameter\n   (p_parameter =&gt; 'IMAGE_PREFIX',   \n    p_value     =&gt; '\/i\/22.2.0\/' );\n   commit;   \n end;\n\/<\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li>intall ORDS (opc user ) sudo yum install ords -y<\/li>\n\n\n\n<li>switch to Oracle user <code>sudo su - oracle <\/code><\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code has-small-font-size\"><code lang=\"bash\" class=\"language-bash line-numbers\">ORDS_CONFIG_DIR=\/opt\/oracle\/ords\/config &amp;&amp; mkdir -p $ORDS_CONFIG_DIR\/ords\/conf\n\nORDS_USER=CUSTOM_ORDS_PUBLIC_USER\nORDS_PASSWORD=YOURPASSWORD\nSERVICE_NAME=prd_low(change it to your own )\nWALLET_BASE64=`base64 -w 0 \/tmp\/wallet_prd.zip`(change it to your own)\n\n\ncat &lt;&lt; EOF &gt; $ORDS_CONFIG_DIR\/ords\/conf\/apex_pu.xml\n&lt;?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"no\"?&gt;\n&lt;!DOCTYPE properties SYSTEM \"http:\/\/java.sun.com\/dtd\/properties.dtd\"&gt;\n&lt;properties&gt;\n  &lt;entry key=\"db.username\"&gt;$ORDS_USER&lt;\/entry&gt;\n  &lt;entry key=\"db.password\"&gt;!$ORDS_PASSWORD&lt;\/entry&gt;\n  &lt;entry key=\"db.wallet.zip.service\"&gt;$SERVICE_NAME&lt;\/entry&gt;\n  &lt;entry key=\"db.wallet.zip\"&gt;&lt;![CDATA[$WALLET_BASE64]]&gt;&lt;\/entry&gt;\n&lt;\/properties&gt;\nEOF\n\ncat &lt;&lt; EOF &gt; $ORDS_CONFIG_DIR\/ords\/defaults.xml\n&lt;?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"no\"?&gt;\n&lt;!DOCTYPE properties SYSTEM \"http:\/\/java.sun.com\/dtd\/properties.dtd\"&gt;\n&lt;properties&gt;\n  &lt;entry key=\"plsql.gateway.enabled\"&gt;true&lt;\/entry&gt;\n  &lt;entry key=\"jdbc.InitialLimit\"&gt;5&lt;\/entry&gt;\n  &lt;entry key=\"jdbc.MaxLimit\"&gt;10&lt;\/entry&gt;\n&lt;\/properties&gt;\nEOF<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code class=\"\"># recently found a better to install \nyum install ords -y\n\nords install\n\n# choose 1 to install ords on desired  db when prompt for 1, install , 2 pool and install , 3 pool . \n\n#follow up the requirements \n\n\n# this will create two files &lt;databases&gt; and &lt;globals&gt;\n# control + C to get out standalone mode \nords serve \n#to start it ords again <\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">How to enable web sql-developer https:\/\/host\/ords\/sql-developer<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">two ways to enable it. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Go to Apex and enable rest for the parsing schema. make your the alias is in smallcase. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">or <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">logon to the schema and write this plsql block to enable it. (read <a href=\"https:\/\/oracle-base.com\/articles\/misc\/oracle-rest-data-services-ords-sql-developer-web\" data-type=\"URL\" data-id=\"https:\/\/oracle-base.com\/articles\/misc\/oracle-rest-data-services-ords-sql-developer-web\" target=\"_blank\" rel=\"noreferrer noopener\">here<\/a> and <a href=\"https:\/\/support.oracle.com\/epmos\/faces\/DocumentDisplay?_afrLoop=583682603462803&amp;parent=EXTERNAL_SEARCH&amp;sourceId=HOWTO&amp;id=2668789.1&amp;_afrWindowMode=0&amp;_adf.ctrl-state=h3sinf1k0_4\" data-type=\"URL\" data-id=\"https:\/\/support.oracle.com\/epmos\/faces\/DocumentDisplay?_afrLoop=583682603462803&amp;parent=EXTERNAL_SEARCH&amp;sourceId=HOWTO&amp;id=2668789.1&amp;_afrWindowMode=0&amp;_adf.ctrl-state=h3sinf1k0_4\" target=\"_blank\" rel=\"noreferrer noopener\">here<\/a> for more information)<\/p>\n\n\n\n<pre class=\"wp-block-code has-small-font-size\"><code lang=\"sql\" class=\"language-sql line-numbers\">BEGIN\n  ORDS.enable_schema(\n    p_enabled             =&gt; TRUE,\n    p_schema              =&gt; '${YOURSCHEMA}',\n    p_url_mapping_type    =&gt; 'BASE_PATH',\n    p_url_mapping_pattern =&gt; '${yourschema}',\n    p_auto_rest_auth      =&gt; FALSE\n  );\n    \n  COMMIT;\nEND;\n\/<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Create And Manage Your Custom ORDs credits goes two these two links https:\/\/fuzziebrain.com\/content\/id\/2004\/ https:\/\/www.linkedin.com\/pulse\/run-your-oracle-apex-apps-autonomous-database-own-domain-kurt-liu\/ How to enable web sql-developer https:\/\/host\/ords\/sql-developer two ways to enable it. Go to Apex and enable rest for the parsing schema. make your the alias is in smallcase. or logon to the schema and write this plsql block to enable it&#8230;.<\/p>\n","protected":false},"author":1,"featured_media":811,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[13,17,11,12],"tags":[],"class_list":["post-810","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-apex","category-linux","category-oci","category-oralce-db"],"_links":{"self":[{"href":"https:\/\/chaoyu.nl\/index.php?rest_route=\/wp\/v2\/posts\/810","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/chaoyu.nl\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/chaoyu.nl\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/chaoyu.nl\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/chaoyu.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=810"}],"version-history":[{"count":16,"href":"https:\/\/chaoyu.nl\/index.php?rest_route=\/wp\/v2\/posts\/810\/revisions"}],"predecessor-version":[{"id":1126,"href":"https:\/\/chaoyu.nl\/index.php?rest_route=\/wp\/v2\/posts\/810\/revisions\/1126"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/chaoyu.nl\/index.php?rest_route=\/wp\/v2\/media\/811"}],"wp:attachment":[{"href":"https:\/\/chaoyu.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=810"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/chaoyu.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=810"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/chaoyu.nl\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=810"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}