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';


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';

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';

10G 11G 12C

ORA-04045 Errors And ORA-01031 during 12.2 DB upgrade via dbua

notice this in alert.log while running dbua.

ORA-12012: error on auto execute of job "APEX_050000"."ORACLE_APEX_MAIL_QUEUE"
ORA-04045: errors during recompilation/revalidation of RMAN.VPC_CONTEXT_TRG
ORA-01031: insufficient privileges

after upgrade, notice the object is invalid.

As this a RMAN catalog database, I will need to upgrade the catalog too.

connect catalog database as RMAN schema

> rman target /

Recovery Manager: Release - Production on Thu May 4 19:14:10 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

connected to target database: RCATT (DBID=4101766741)

RMAN> connect catalog rman@rcatpdb1

recovery catalog database Password:
connected to recovery catalog database
PL/SQL package RMAN.DBMS_RCVCAT version in RCVCAT database is too old

RMAN> upgrade catalog;

recovery catalog owner is RMAN
enter UPGRADE CATALOG command again to confirm catalog upgrade

RMAN> upgrade catalog;

recovery catalog upgraded to version
DBMS_RCVMAN package upgraded to version
DBMS_RCVCAT package upgraded to version

check invalid object again, and now no more invalid objects.

Tuesday, April 18, 2017

how to find sqltext by using old_hash_value

In Oracle standard edition, AWR isn't there, as workaround, we still rely on STATSPACK.

one issue facing is incompleted SQL text listed in statspack report, when it is long ...

below is my method, to dig out the completed sql text.
1. with old_hash_value, I find sql_id from STATS$SQL_PLAN_usage
2. with sql_id, pull from dbms_xplan.display_cursor (if still in memory), or get from STATS$SQLTEXT


Enter value for ohv: 3353767585
old   1: select distinct sql_id from STATS$SQL_PLAN_usage where OLD_HASH_VALUE=&ohv
new   1: select distinct sql_id from STATS$SQL_PLAN_usage where OLD_HASH_VALUE=3353767585


Enter value for sqlid: 6mtr4m135zf22
old   1: select * from table(dbms_xplan.display_cursor('&sqlid'))
new   1: select * from table(dbms_xplan.display_cursor('6mtr4m135zf22'))

SQL_ID: 6mtr4m135zf22, child number: 0 cannot be found

Enter value for sql_id: 6mtr4m135zf22
old   1: select sql_text from STATS$SQLTEXT where sql_id='&sql_id' order by piece
new   1: select sql_text from STATS$SQLTEXT where sql_id='6mtr4m135zf22' order by piece


11 rows selected.

Next, can use below sql to find out historical execution plan too.

>  cat sp_hist_sqlplan_oldhash.sql

set lines 180
select snap_id, id,parent_id, position,operation,object_name, bytes,cardinality, cost,cpu_cost,temp_space,plan_hash_value from STATS$SQL_PLAN
where plan_hash_value in (select distinct plan_hash_value from  STATS$SQL_PLAN_usage where
order by snap_id, id,parent_id, position, plan_hash_value

Monday, April 10, 2017

sqlplus hang after linux server cloning

Cloned a server to AWS EC2 server, including oracle client software .

firewall port is open, however tnsping/sqlplus hangs.

Even tried instant client, same symptoms.

Googled, check against latest installation guide , and finally found some packages is missing.  In my case, they are :

compat-libcap1-1.10-1 (x86_64)
libstdc++-4.4.4-13.el6 (x86_64)

this may happen when cloned by sysadmin, as packages need to be installed with both 32 and 64 bits.

Monday, March 06, 2017

Samsung M2070 wireless printer installation , even supporting Linux

Excited with my very first new laser printer, started to set it up.

Followed the quick installation guide, insert the CD to ROM, choose wireless, however the stuck at connecting printer, time out after waiting for 10 minutes. intially suspect it is related to default OOTB 1 minute autosleep, adjust to longer time. still the same problem  -- the installation from the CD failed to detect the printer, although I had the USB cable connected. Real bad user experience, I started to regret chose this brand.

Thinking not right time to call the hot line at weekend night, while daytime I need to work.  Does it mean I need to take half day to stay at to call service centre and follow the instruction.

Searched to hard copy manual comes with the box, have to read the  guide again and again, finally I paid attention the sentence "use the CDROM and download installer from "  . I started to guess something with the installer from CDROM.

Tried my luck to download the installer online, in few steps, the new installer detect the wireless printer in few seconds.  I had spent new two hours for that buggy installer from CDROM.

What if customer not so techy like me? Isn't that very bad user experience?

Next I tested with mobile app "samsung mobile print" smoothly.

My final challenge is print from my Ubuntu PC, which I never thought when I decided to buy a laser print.

Tried to add a printer from Ubuntu, but failed to find right model.
Googled and find there is Driver for Linux, thanks to Samsung.

The filename I downloaded is ULD_v1.00.29.tar.gz.

tar xvfz it

cd uld

sudo  ./


If you have any questions regarding this agreement and other products, please co
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

**** Do you agree ? [y/n] : y
**** Are you going to use network devices ? If yes, it is recommended to configure your firewall.
**** If you want to configure firewall automatically, enter 'y' or just press 'Enter'. To skip, enter 'n'. : n
**** Registering CUPS backend ...
**** CUPS restart OK.
**** Print driver has been installed successfully.
**** Registering SANE backend ...
**** Restarting udev ...
**** Scan driver has been installed successfully.
**** Install finished.
hmc@hmc-P55A-UD3:~/Downloads/uld$ sudo

Added the printer again , the correct printer model is shown.

Print the Test Page!

Thanks! I shall have no more worry about dry ink when I want to print!

Other References:


From the , I even see there is installer for more platforms like Linux, AIX , Solarix, HPUX !  Appreciate this .