Wednesday, June 28, 2017

install APEX 5.1 in 12.2

1. Notice that APEX is not installed by default since 12.2

fond of installing 5.1 to 12.2

follow the standard step of installation guide , or 
my favorite article 

key things different with installation in 11g is , I need to set container to pluggable database pdb1.

No errors with all steps, however when I launch http://localhost:8080/apex/apex_admin , it prompts for XDB username and password.

Reviewed a few times on previous steps, finally I notice the  account_status of anonymous is EXPIRED, although I issued unlocked command.

while inside the pdb, I attempted to changes its password in order to make it non-expired. 

SYS> alter USER ANONYMOUS identified by oracle123 ;
alter USER ANONYMOUS identified by oracle123
*
ERROR at line 1:
ORA-65066: The specified changes must apply to all containers

as such, I quit back to contained database , and re-issue the command successfully .


SYS> conn / as sysdba
Connected.
SYS> alter USER ANONYMOUS  account unlock;
SYS>  select username, account_status from dba_users where username like 'ANONY%';

USERNAME
--------------------------------------------------------------------------------
ACCOUNT_STATUS
--------------------------------
ANONYMOUS
EXPIRED

SYS> alter USER ANONYMOUS identified by oracle123 ;
SYS>  select username, account_status from dba_users where username like 'ANONY%';

USERNAME
--------------------------------------------------------------------------------
ACCOUNT_STATUS
--------------------------------

ANONYMOUS
OPEN



SYS> alter session set container=pdb1;
SYS>  select username, account_status from dba_users where username like 'ANONY%';

USERNAME
--------------------------------------------------------------------------------
ACCOUNT_STATUS
--------------------------------
ANONYMOUS


SYS> conn / as sysdba
Connected.
SYS> alter USER ANONYMOUS  account unlock;
SYS>  select username, account_status from dba_users where username like 'ANONY%';

USERNAME
--------------------------------------------------------------------------------
ACCOUNT_STATUS
--------------------------------
ANONYMOUS
EXPIRED

SYS> alter USER ANONYMOUS identified by oracle123 ;
SYS>  select username, account_status from dba_users where username like 'ANONY%';

USERNAME
--------------------------------------------------------------------------------
ACCOUNT_STATUS
--------------------------------

ANONYMOUS
OPEN




SYS> alter session set container=pdb1;
SYS>  select username, account_status from dba_users where username like 'ANONY%';

USERNAME
--------------------------------------------------------------------------------
ACCOUNT_STATUS
--------------------------------
ANONYMOUS
OPEN


finally, I changed DEFAULT profile at both container and PDB level, to make password no expired.
SYS> alter profile DEFAULT limit PASSWORD_LIFE_TIME unlimited;


Once ANONYMOUS account is unlocked, I am able to logon APEX smoothly!

Tuesday, June 27, 2017

"HTTP ERROR 500" after QNAP firmware upgrade from 4.2 to 4.3

Almost php/mysql applications like joomla, mantis, drupal, wordpress are not working after firmware update to 4.3.3. The error message is "HTTP ERROR 500".  This is a headache problem since April.

Googled didn't help me, in the end I have to contact QNAP support  . After few round communications, finally the first step worked for me .  Cheers!

Anyway, I am sharing the completed instructions ...

1. login web admin page>control panel>applications>web server >maintenance>restore
2. php.ini>maintenance>restore
3. control panel>applications>sql server>re-initialize database

Tuesday, June 20, 2017

useful APEX related URL

Mark down some useful APEX related URL for look back.

https://oracle-base.com/articles/misc/oracle-application-express-apex-5-0-installation
https://community.oracle.com/community/database/developer-tools/application_express/packaged-applications
http://www.odtug.com/apexblogroll
https://mikesmithers.wordpress.com/2015/05/24/migrating-the-xe-database-management-application-to-a-new-version-of-apex/
http://www.oracle.com/technetwork/developer-tools/apex/application-express/upgrade-apex-for-xe-154969.html


ords

https://oracle-base.com/articles/misc/oracle-rest-data-services-ords-3-installation-on-tomcat-7
https://oracle-base.com/articles/misc/oracle-rest-data-services-ords-standalone-mode


Excerpt below from last two articles, which is important to me.


$ $JAVA_HOME/bin/java -jar ords.war configdir /u01/ords/conf
Dec 24, 2016 4:43:18 PM
INFO: Set config.dir to /u01/ords/conf in: /u01/ords/ords.war
$
Configure ORDS using the following command. This is the equivalent of specifying the "install simple" command line parameters. Make sure you agree to configure the PL/SQL Gateway and not to enable "standalone mode".

Auto SSL (HTTPS)

ORDS will automatically create a self-signed certificate for use with SSL if you don't specify a valid certificate and key.

Edit the "/u01/ords/conf/ords/standalone/standalone.properties" file, setting the following parameters. Adjust the port as desired.

jetty.secure.port=8443
ssl.cert=
ssl.cert.key=
ssl.host=
Restart ORDS.

~/scripts/stop_ords.sh
~/scripts/start_ords.sh

Saturday, June 17, 2017

high "virtual circuit wait " due to open cursor still holds the shared server

curious on one database has 90% time spent on virtual circuit wait.

Luckily noted a blog says it is related a bug ,  Bug 5689608 : INACTIVE SESSION IS NOT RELEASING SHARED SERVER PROCESS .

when read the bug note, I realize this is a behaviou change since 10g , and it is true that 

Once the cursor is open, next doing fetch but idling there, the shared server is still held up  in a virtual circuit wait state from the time the cursor is first fetched until the application closes the
cursor or exits the session. 



This impacts the configuration of shared_servers and max_shared_servers, which should be high enough to avoid becoming an unnecessary bottleneck.

 
Below is my additional test on top the bug note scripts.

-- session 1
sqlplus liqy@pst01

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 7 17:07:39 2017

Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

LIQY@pst01> select server from v$session where username='LIQY';

SERVER
---------
SHARED



--monitor again in another session 2

SYS@PST01> select sid, server from v$session where username='LIQY';

SERVER
---------
NONE



--session 1

LIQY@pst01> begin
 open cursor_package.mycursor;
  2    3  end;
  4  /

PL/SQL procedure successfully completed.

LIQY@pst01> select * from v$session_event
where sid=
  2    3  (SELECT sid from v$session where audsid=USERENV('SESSIONID')) and
  4  event='virtual circuit wait';

no rows selected


--session 2

SYS@PST01> /

SERVER
---------
NONE


--session 1 , fetch the cursor but do nothing

LIQY@pst01> exec test_case;      

PL/SQL procedure successfully completed.


--session 2
SYS@PST01> /

SERVER
---------
SHARED

--session 1

LIQY@pst01> select * from v$session_event
where sid=
  2    3  (SELECT sid from v$session where audsid=USERENV('SESSIONID')) and
  4  event='virtual circuit wait';

       SID EVENT                                                            TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT
---------- ---------------------------------------------------------------- ----------- -------------- ----------- ------------
  MAX_WAIT TIME_WAITED_MICRO   EVENT_ID WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- ----------------- ---------- ------------- ----------- ----------------------------------------------------------------
        83 virtual circuit wait                                                       2              0         733       366.58
       733           7331552 2900469894    2000153315           7 Network


LIQY@pst01> /

       SID EVENT                                                            TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT
---------- ---------------------------------------------------------------- ----------- -------------- ----------- ------------
  MAX_WAIT TIME_WAITED_MICRO   EVENT_ID WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- ----------------- ---------- ------------- ----------- ----------------------------------------------------------------
        83 virtual circuit wait                                                       6              0        1046       174.34
       733          10460632 2900469894    2000153315           7 Network


LIQY@pst01> host sleep 5

LIQY@pst01> select * from v$session_event
where sid=
  2    3  (SELECT sid from v$session where audsid=USERENV('SESSIONID')) and
  4  event='virtual circuit wait';

       SID EVENT                                                            TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT
---------- ---------------------------------------------------------------- ----------- -------------- ----------- ------------
  MAX_WAIT TIME_WAITED_MICRO   EVENT_ID WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- ----------------- ---------- ------------- ----------- ----------------------------------------------------------------
        83 virtual circuit wait                                                      11              1        4704       427.62
      3000          47038538 2900469894    2000153315           7 Network


--session 2
SYS@PST01> /

SERVER
---------
SHARED



--session 1
LIQY@pst01> begin
 close cursor_package.mycursor;
  2    3  end;
  4  /

PL/SQL procedure successfully completed.

--session 2,  finally the shared server is released

SYS@PST01> /

SERVER
---------
NONE



LIQY@pst01> select * from v$session_event
where sid=
  2    3  (SELECT sid from v$session where audsid=USERENV('SESSIONID')) and
  4  event='virtual circuit wait';

       SID EVENT                                                            TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT
---------- ---------------------------------------------------------------- ----------- -------------- ----------- ------------
  MAX_WAIT TIME_WAITED_MICRO   EVENT_ID WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- ----------------- ---------- ------------- ----------- ----------------------------------------------------------------
        83 virtual circuit wait                                                      14              1        7341       524.37
      3000          73411576 2900469894    2000153315           7 Network


LIQY@pst01> /

       SID EVENT                                                            TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT
---------- ---------------------------------------------------------------- ----------- -------------- ----------- ------------
  MAX_WAIT TIME_WAITED_MICRO   EVENT_ID WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- ----------------- ---------- ------------- ----------- ----------------------------------------------------------------
        83 virtual circuit wait                                                      14              1        7341       524.37
      3000          73411576 2900469894    2000153315           7 Network



2nd test,

if I open more sessions of session 1, once hit the minimum number of shared_servers, a new shared is raised up.  we can monitor their OS processes too.

ps -ef |grep s0 |grep

handy command line - sqlcl

happen to notice this command line tool called sqlcl from oracle.com

with this , it is portable, no need to administrator to modify sqlnet.ora on windows platform (commonly, in my environment). and not like instantclient, I need to asseble library files and sqlplus.exe into same directory.

Only 3 files after download, for linux, windows platform respectively. but requires JRE.

If no JRE installed, download it , and add %JAVA_HOME%/bin to %PATH% variable (for windows platform)

it supports ezconnect too. This is it.


/cygdrive/d/setup.dsk/apex/sqlcl-4.2.0.17.097.0719-no-jre/sqlcl/bin
$ ./sql.exe liqy/xxxxxx@//dbdev:1582/DB05

SQLcl: Release 4.2.0 Production on Tue Jun 13 13:52:30 2017

Copyright (c) 1982, 2017, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> exit
exit


Monday, May 15, 2017

spare6 in sys.use$

Security related values in the 12c SYS.USER$ table are

CTIME: Date & Time when user was created

PTIME: Date & Time when user password was last changed

LTIME: Date & Time when the user account was locked

LCOUNT: Number of failed login attempts

Added in Oracle Database 12c:

SPARE6: Last login time of the user.

Thursday, May 04, 2017

12.2c dbua complains 10G version password being used

since dbua complains, and I checked the 12.1 database to be upgraded.
"In exclusive mode, accounts which oly have the 10G password version (see DBA_USERS.PASSWORD_VERSIONS)"   ...


There is one account RMAN somehow is having 10G version password.

SQL> select username, password_versions from dba_users where username='RMAN';

USERNAME
--------------------------------------------------------------------------------
PASSWORD_VER
------------
RMAN
10G

although the parameter sensitive is already true, set the minimum allowed version in sqlnet.ora (SQLNET.ALLOWD_LOGON_VERSION_SERVER) to 10. but still not worked for me.

Finally, solution worked for me is to change its password manually.

SQL> password
Changing password for RMAN
Old password:
New password:
Retype new password:
Password changed
SQL> select username, password_versions from dba_users where username='RMAN';

USERNAME
--------------------------------------------------------------------------------
PASSWORD_VER
------------
RMAN
10G 11G 12C


SQL> password
Changing password for RMAN
Old password:
New password:
Retype new password:
Password changed
SQL> select username, password_versions from dba_users where username='RMAN';

USERNAME
--------------------------------------------------------------------------------
PASSWORD_VER
------------
RMAN
10G 11G 12C