Oracle XE 18c with Apex 19 on top has got a default password expiration setup and user profile setup which one day simply ends up as a closed down service. Only place to find a decent log is webservers access log (if you are lucky enough to have it).
"Common user" is really unhelpfull, basically your APEX_PUBLIC_USER account password has expired. Hopefully you recall original password, otherwise you will have to change password hashes in dads.conf.
SELECT username, account_status FROM dba_users WHERE ACCOUNT_STATUS LIKE '%EXPIRED%';
ALTER USER APEX_PUBLIC_USER IDENTIFIED BY yourpassword (dont use SEMICOLON; - use new line)
/
alter user APEX_PUBLIC_USER account unlock;
In case you still stuck, somehow Apex 19 likes anonymous account being unlocked:
alter user ANONYMOUS identified by anonymous;
alter user ANONYMOUS account unlock;
office space
deep thoughts about space and office stuff
Sunday, June 21, 2020
Monday, December 16, 2019
Oracle Apex 19.2 on Oracle database 18c XE issues when running on HTTP server 11.1.1
System setup
Oracle Apex 19.2
Oracle Database XE 18c (18.4)
Oracle OFM HTTP server 11 (11.1.1.9)
List of articles that I have been following
Oracle Apex 19.2 installation instructions
Popular CDB/PDB issue article online
Issues encountered after setting up Oracle Apex with HTTP server using articles above
Error provisioning XXXXXXXXXXXX. ORA-20001: Request 1824748984765804 could not be processed. PROVISION_COMPANY - this basically means you are in the wrong DB. You are connected to CDB$ROOT instead of XEPDB1 (and not PDB1!)
ORA-65096: invalid common user or role name in oracle.
ORA-28040: No matching authentication protocol.
Lessons learned
You do not need install Apex as root
Oracle original documentation is missing examples or better explanation
XE has its own PDB naming prefix
Example dads.conf configuration file that worked
#Alias /i/ "ORACLE_HTTPSERVER_HOME/user_projects/domains/base_domain/images/"
Alias /i/ "/home/oracle/apex/images/"
<Location /apex>
Order deny,allow
AllowOverride None
PlsqlDatabaseConnectString i7mr:1521:XEPDB1 ServiceNameFormat
# PlsqlDatabaseConnectString XEPDB1 TNSFormat
PlsqlNLSLanguage AMERICAN_AMERICA.AL32UTF8
PlsqlAuthenticationMode Basic
SetHandler pls_handler
PlsqlDocumentTablename wwv_flow_file_objects$
PlsqlDatabaseUsername APEX_PUBLIC_USER
PlsqlDefaultPage apex
PlsqlDatabasePassword **********
PlsqlRequestValidationFunction wwv_flow_epg_include_modules.authorize
PlsqlPathAlias r
PlsqlPathAliasProcedure wwv_flow.resolve_friendly_url
Allow from all
</Location>
TL;DR
When you install Oracle XE 19.2 using installation manual there are 3 key steps missing:
1) you have to unlock APEX_PUBLIC_USER and ANONYMOUS user in PDB (XEPDB1) database
2) you have to setup HTTP server configuration file dads.conf to be connecting to the same PDB (XEPDB1)
3) Oracle XE has different PDB naming, they start with XE - XEPDB1 instead of normal PDB1 used in Oracle Std.
Its ok to install XE into CDB$ROOT. It goes there by default following installation instructions. After installation when you select:
select r.COMP_NAME, r.VERSION, c.NAME, c.CON_ID from CDB_REGISTRY r, V$CONTAINERS c where r.CON_ID=c.CON_ID and r.COMP_ID='APEX' order by CON_ID;
You get:
Oracle Application Express 19.2.0.00.18 CDB$ROOT 1
Oracle Application Express 19.2.0.00.18 XEPDB1 3
alter session set container=XEPDB1;
exec dbms_xdb.sethttpport(0);
alter system register;
@apxchpwd
alter user APEX_PUBLIC_USER account unlock;
alter user ANONYMOUS account unlock;
The key problem here is that Apex installs to CDB$ROOT and XEPDB1, just you have to unlock users in XEPDB1 and setup your dads.conf configuration to connect to XEPDB1.
After that configure dads.conf, just pay attention to the bolded part - when connecting to instance, its not XE, it has to be XEPDB1.
In case you still get error ORA-28040: No matching authentication protocol, make sure you add these to /opt/oracle/product/18c/dbhomeXE/network/admin/sqlnet.ora
SQLNET.ALLOWED_LOGON_VERSION_CLIENT = 8
SQLNET.ALLOWED_LOGON_VERSION_SERVER = 8
And restart HTTP server:
opmnctl stopall
opmnctl startall
The key problem indicators after installing Apex and connecting it to HTTP server is path that can be seen when creating empty Apex workspace. Here is how it looks like before PROVISION_COMPANY error:
And here is where it should go:
Monday, January 29, 2018
ORA-01152: file 1 was not restored from a sufficiently old backup
This post covers a very specific situation and may not be the general solution for this Oracle error. Details for current situation:
- New Oracle 10.2.0.4 installation
- Restored database copy of the same version (using RMAN)
- Recovery noarchivelog
- Restart
When booting up the restored database it complaints about DBID for the first time, then it stops when restoration is repeated.
After next restart we have this particular error:
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1:
'/data/oracle/product/10.2.0/db_1/oradata/aws/system01.dbf'
Fix in this situation is rather stupid - stop dartabase, delete redo log files (all of them, redo*), recover and startup your database (resetlogs).
- New Oracle 10.2.0.4 installation
- Restored database copy of the same version (using RMAN)
- Recovery noarchivelog
- Restart
When booting up the restored database it complaints about DBID for the first time, then it stops when restoration is repeated.
After next restart we have this particular error:
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1:
'/data/oracle/product/10.2.0/db_1/oradata/aws/system01.dbf'
Fix in this situation is rather stupid - stop dartabase, delete redo log files (all of them, redo*), recover and startup your database (resetlogs).
Tuesday, December 19, 2017
Error in writing to directory /tmp Please ensure that this directory is writable and has atleast 60 MB of disk space. Installation cannot continue.
I recently got an increasing flood of the same error in all the server environments:
Error in writing to directory /tmp/OraInstallYYYY-MM-DD_HH-MM-SSAM Please ensure that this directory is writable and has atleast 60 MB of disk space. Installation cannot continue.
Tmp has always got 0777, always plenty of space, Oracle Installer creates a folder in Tmp, but fails writing. Some sources online suggest Temp2 type solution of creating another temporary folder, pass TMP and TEMP variables to the shell, but not all shells and environments accept those.
I have no cure, but based on recent testing it all boils down to this checklist:
- place installer in Oracle home directory (or complete equivalent)
- unpack zips, cpios with Oracle user you are going to use for installation
- run installer from an installation directory with Oracle user (not using full path)
- check if unzip is installed
At least last couple of times first step solved the problem. It looks like Oracle user fails writing somewhere, but it reports it with dummy error.
Error in writing to directory /tmp/OraInstallYYYY-MM-DD_HH-MM-SSAM Please ensure that this directory is writable and has atleast 60 MB of disk space. Installation cannot continue.
Tmp has always got 0777, always plenty of space, Oracle Installer creates a folder in Tmp, but fails writing. Some sources online suggest Temp2 type solution of creating another temporary folder, pass TMP and TEMP variables to the shell, but not all shells and environments accept those.
I have no cure, but based on recent testing it all boils down to this checklist:
- place installer in Oracle home directory (or complete equivalent)
- unpack zips, cpios with Oracle user you are going to use for installation
- run installer from an installation directory with Oracle user (not using full path)
- check if unzip is installed
At least last couple of times first step solved the problem. It looks like Oracle user fails writing somewhere, but it reports it with dummy error.
Tuesday, December 5, 2017
ApexLib on APEX 4.2, G_VALIDATION_IDS_IN_ERROR, APEXLIB_ERROR
People say ApexLib will not work on Apex > 4.1 (http://www.oracle-and-apex.com/apexlib-framework-on-oracle-apex-4-2/). After Apex upgrade to 4.2 ApexLib failed to compile - thats true. After primary installation (which was 3.2) all the Synonyms for user Apexlib are pointing to APEX_030200 user schema. First attempt was to recreate all the synonyms and I did that.
The only problem I bumped into was G_VALIDATION_IDS_IN_ERROR. Downloaded a new version of ApexLib, repeated the installation referencing to docs (http://apexlib.oracleapex.info/#apexlib_schema) - result is the same: G_VALIDATION_IDS_IN_ERROR.
The type G_VALIDATION_IDS_IN_ERROR is referenced in changelog for APEX_040200 WWV_FLOWS package, but is not found anywhere in the code, as well as a few other packages. So it looks like they were simply discarded sometime in 4.2.
There are 2 package errors you will see when ApexLib is failing, usually its just package that cant be compiled, one of them will be "ORA-04063: package body APEXLIB.
APEXLIB_ERROR has errors". Once you manage to fix all the synonyms and views - in the end you will still bump into G_VALIDATION_IDS_IN_ERROR.
Fix is rather simple, declare the missing types using possible expected Apex internal types in your package that is failing (in our case its APEXLIB.APEXLIB_ERROR). Simply define (there are 2 types missing) the types in APEXLIB_ERROR body, someplace were variables reside:
g_validation_ids_in_error wwv_flow_global.n_arr;
g_validation_message wwv_flow_global.vc_arr2;
Then go and replace all the references to these types (they will be like "wwv_flows.g_validation_ids_in_error and wwv_flows.g_validation_message) - simply remove the schema reference wwv_flows. A few snippets how does it look like:
The only problem I bumped into was G_VALIDATION_IDS_IN_ERROR. Downloaded a new version of ApexLib, repeated the installation referencing to docs (http://apexlib.oracleapex.info/#apexlib_schema) - result is the same: G_VALIDATION_IDS_IN_ERROR.
The type G_VALIDATION_IDS_IN_ERROR is referenced in changelog for APEX_040200 WWV_FLOWS package, but is not found anywhere in the code, as well as a few other packages. So it looks like they were simply discarded sometime in 4.2.
There are 2 package errors you will see when ApexLib is failing, usually its just package that cant be compiled, one of them will be "ORA-04063: package body APEXLIB.
APEXLIB_ERROR has errors". Once you manage to fix all the synonyms and views - in the end you will still bump into G_VALIDATION_IDS_IN_ERROR.
Fix is rather simple, declare the missing types using possible expected Apex internal types in your package that is failing (in our case its APEXLIB.APEXLIB_ERROR). Simply define (there are 2 types missing) the types in APEXLIB_ERROR body, someplace were variables reside:
g_validation_ids_in_error wwv_flow_global.n_arr;
g_validation_message wwv_flow_global.vc_arr2;
Then go and replace all the references to these types (they will be like "wwv_flows.g_validation_ids_in_error and wwv_flows.g_validation_message) - simply remove the schema reference wwv_flows. A few snippets how does it look like:
Friday, October 27, 2017
Oracle installer 12 on Xming copy paste keyboard problem
Was running a few Oracle Installers via SSH X tunnel forward and Xming client on local Windows 10 box. I noticed that in some installers keyboard does not work.
Its ok sometimes, but Oracle HTTP Server 12.1.0.3 installer needs username and password input. Took a few days (maybe its just me), but looks like Windows Paste is mapped to a middle mouse button. I've found some hints confirming this in forum here. Although selecting and cutting did not work.
Editing still does not work, so some settings have to be changed manually after installation, but username and password can be prepared in Notepad or wherever and middle-clicked into fields. Be sure to pick exact values since its not possible to change them, they will be merged with previous Paste and you will have to run installer from the start.
Its ok sometimes, but Oracle HTTP Server 12.1.0.3 installer needs username and password input. Took a few days (maybe its just me), but looks like Windows Paste is mapped to a middle mouse button. I've found some hints confirming this in forum here. Although selecting and cutting did not work.
Editing still does not work, so some settings have to be changed manually after installation, but username and password can be prepared in Notepad or wherever and middle-clicked into fields. Be sure to pick exact values since its not possible to change them, they will be merged with previous Paste and you will have to run installer from the start.
Thursday, October 26, 2017
Unix (Solaris) display forwarding after user switch (su)
Using X forward via SSH, Xming client running locally on Windows box. After login to SSH with your preferred tool (Putty or SSH Secure Shell) X applications do work, but they stop working after you switch to another user.
My objective was to run Oracle Installer without having root access or Oracle user via SSH only. User is not part of DBA group and new Oracle user (ora12213) does not have X access.
bash-4.1$ xclock
Error: Can't open display: localhost:10.0
Found plenty of resources online, but all of them missing something, here is a full list of steps to make it work.
1) setup your SSH tool for X forwarding (settings, checkbox)
2) save settings and restart the tool and connection (it appears that setting is only activated after a new connection or window is opened)
3) test xclock
bash-4.1$ xclock
4) echo these variables:
-bash-4.1$ xauth list
solserv102/unix:10 MIT-MAGIC-COOKIE-1 2359b71c685cf706bf4dec49e4136558
-bash-4.1$ echo $DISPLAY
localhost:10.0
5) do the user switch with parameters, minus is essential. Username is "ora12213":
-bash-4.1$ sudo su - ora12213
Oracle Corporation SunOS 5.11 11.2 March 2015
6) export the variables listed above, looks like error can be ignored. In most of the tested examples "xauth add" part was not working, but after user switching with su, it looks like it okayed:
-bash-4.1$ xauth add solserv102/unix:10 MIT-MAGIC-COOKIE-1 2359b71c685cf706bf4dec49e4136558
xauth: file /export/home/ora12213/.Xauthority does not exist
7) test the X, should be working just fine.
-bash-4.1$ xclock
BONUS
Run the Solaris installer executable (it was downloaded, unzipped, moved to /stage_oracle folder, owned by DBA group):
-bash-4.1$ cd /stage_oracle/
-bash-4.1$ ./fmw_12.2.1.3.0_ohs_solaris_sparc64.bin
Launcher log file is /var/tmp/OraInstall2017-10-26_09-46-21AM/launcher2017-10-26_09-46-21AM.log.
Checking if CPU speed is above 300 MHz. Actual 3600 MHz Passed
Checking monitor: must be configured to display at least 256 colors. Actual 16777216 Passed
Checking swap space: must be greater than 512 MB. Actual 85678 MB Passed
Checking if this platform requires a 64-bit JVM. Actual 64 Passed
Checking temp space: must be greater than 300 MB. Actual 1672 MB Passed
Preparing to launch the Oracle Universal Installer from /var/tmp/OraInstall2017-10-26_09-46-21AM
Log: /var/tmp/OraInstall2017-10-26_09-46-21AM/install2017-10-26_09-46-21AM.log
My objective was to run Oracle Installer without having root access or Oracle user via SSH only. User is not part of DBA group and new Oracle user (ora12213) does not have X access.
bash-4.1$ xclock
Error: Can't open display: localhost:10.0
Found plenty of resources online, but all of them missing something, here is a full list of steps to make it work.
1) setup your SSH tool for X forwarding (settings, checkbox)
2) save settings and restart the tool and connection (it appears that setting is only activated after a new connection or window is opened)
3) test xclock
bash-4.1$ xclock
4) echo these variables:
-bash-4.1$ xauth list
solserv102/unix:10 MIT-MAGIC-COOKIE-1 2359b71c685cf706bf4dec49e4136558
-bash-4.1$ echo $DISPLAY
localhost:10.0
-bash-4.1$ sudo su - ora12213
Oracle Corporation SunOS 5.11 11.2 March 2015
6) export the variables listed above, looks like error can be ignored. In most of the tested examples "xauth add" part was not working, but after user switching with su, it looks like it okayed:
-bash-4.1$ xauth add solserv102/unix:10 MIT-MAGIC-COOKIE-1 2359b71c685cf706bf4dec49e4136558
xauth: file /export/home/ora12213/.Xauthority does not exist
-bash-4.1$ export DISPLAY=localhost:10.0
7) test the X, should be working just fine.
-bash-4.1$ xclock
BONUS
Run the Solaris installer executable (it was downloaded, unzipped, moved to /stage_oracle folder, owned by DBA group):
-bash-4.1$ cd /stage_oracle/
-bash-4.1$ ./fmw_12.2.1.3.0_ohs_solaris_sparc64.bin
Launcher log file is /var/tmp/OraInstall2017-10-26_09-46-21AM/launcher2017-10-26_09-46-21AM.log.
Checking if CPU speed is above 300 MHz. Actual 3600 MHz Passed
Checking monitor: must be configured to display at least 256 colors. Actual 16777216 Passed
Checking swap space: must be greater than 512 MB. Actual 85678 MB Passed
Checking if this platform requires a 64-bit JVM. Actual 64 Passed
Checking temp space: must be greater than 300 MB. Actual 1672 MB Passed
Preparing to launch the Oracle Universal Installer from /var/tmp/OraInstall2017-10-26_09-46-21AM
Log: /var/tmp/OraInstall2017-10-26_09-46-21AM/install2017-10-26_09-46-21AM.log
Voila
Subscribe to:
Posts (Atom)