Friday, September 01, 2017

apply APEX patch 5.1.2 for 5.1.1

alter session set container=pdb1;


Session altered.

SQL> @apxpatch.sql
...

PL/SQL procedure successfully completed.

...Validating Application Express
...(12:25:48) Starting validate_apex for APEX_050100
...(12:25:51) Checking missing sys privileges
...(12:25:51) Recompiling
...(12:25:53) Checking for objects that are still invalid
...(12:25:53) Key object existence check
...(12:25:53) Setting DBMS Registry for APEX to valid
...(12:25:53) Exiting validate_apex

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

timing for: Complete Patch
Elapsed: 00:04:48.58


SQL> !ls apxldimg.sql
apxldimg.sql

SQL> @apxldimg.sql  /home/oracle/Downloads/patch


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.




1 row selected.



1 row selected.




1 row selected.


PL/SQL procedure successfully completed.





1 row selected.


PL/SQL procedure successfully completed.

. Loading images directory: /home/oracle/Downloads/patch/images

Directory created.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Commit complete.


Directory dropped.

timing for: Load Images
Elapsed: 00:05:12.92
SQL> select VERSION from dba_registry where comp_id='APEX';

VERSION
------------------------------
5.1.2.00.09

1 row selected.

Wednesday, August 23, 2017

some links for APEX 5.0 upgrade to 5.1



How to upgrade from Apex 5.0 to 5.1

 

Apply These 5 Steps to Upgrade Oracle Apex from 5.0 to 5.1

 

Upgrade Oracle APEX 5.1 – Grant Issues

 

Upgrading Oracle Application Express within Oracle Database 11g Express Edition (XE)

 

 

 

 

 

 

 

something new related AWR setting in 12c multitenant DB



12c Consolidated AWR report with PDBs

Enabling Automatic AWR Snapshots on PDB 

 

below is not unique to 12c, but advanced ...

To see AWR settings query DBA_HIST_WR_CONTROL

 

 

 

 

 

Create stacked bar in APEX 5.1

It seems no "stacked bar" chart in APEX 5.1.

Here is my key steps after few tries.

1. add two series, with same test SQL query.

select * from "EVENT_RT"

2. here is the key setting. Inside Chart > Attribute > choose YES for Stacked. Default is NO, which means two bars side by side displayed.


The other method (should be prior to 5.1) uses one serie data only together with below sample query formed by UNION ALL. I guess it may still work in 5.1

select id,
       project as label,
       NVL((select sum(t.budget) from eba_demo_chart_tasks t where t.project =  p.id and t.budget > t.cost),0) as value,
       'under budget' as series ,
       'green' as color
  from eba_demo_chart_projects p
union all
select id,
       project as label,
       NVL((select sum(t.budget) from eba_demo_chart_tasks t where t.project =  p.id and t.budget <= t.cost),0) as value,
       'over budget' as series ,
       'red' as color
  from eba_demo_chart_projects p

Sunday, August 20, 2017

install sql server 2017 RC2 in CentOS 7.3

Here is the link as of writing time, and follow it without difficulty.



[oracle@hmc-P55A-UD3 bin]$ sudo su -
[sudo] password for oracle:
Last login: Sat Aug 19 10:48:19 EDT 2017 on pts/1
[root@hmc-P55A-UD3 ~]#  curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/7/mssql-server.repo
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   220  100   220    0     0    445      0 --:--:-- --:--:-- --:--:--   446
[root@hmc-P55A-UD3 ~]# ls -lrt /etc/yum.repos.d/
total 52
-rw-r--r--. 1 root root  535 Oct 14  2015 CentOS-OpenStack-kilo.repo
-rw-r--r--. 1 root root 2893 Nov 29  2016 CentOS-Vault.repo
-rw-r--r--. 1 root root 1331 Nov 29  2016 CentOS-Sources.repo
-rw-r--r--. 1 root root  630 Nov 29  2016 CentOS-Media.repo
-rw-r--r--. 1 root root  314 Nov 29  2016 CentOS-fasttrack.repo
-rw-r--r--. 1 root root  649 Nov 29  2016 CentOS-Debuginfo.repo
-rw-r--r--. 1 root root 1309 Nov 29  2016 CentOS-CR.repo
-rw-r--r--. 1 root root 1664 Nov 29  2016 CentOS-Base.repo
-rw-r--r--. 1 root root 1050 Jun 24 11:04 epel-testing.repo
-rw-r--r--. 1 root root  951 Jun 24 11:04 epel.repo
-rw-r--r--. 1 root root 2142 Jul 23 15:07 elrepo.repo
-rw-r--r--. 1 root root  193 Aug 19 10:49 msprod.repo
-rw-r--r--. 1 root root  220 Aug 19 12:07 mssql-server.repo
[root@hmc-P55A-UD3 ~]# yum update
Loaded plugins: fastestmirror, langpacks
packages-microsoft-com-mssql-server                      | 2.9 kB     00:00    
packages-microsoft-com-mssql-server/primary_db             |  12 kB   00:00    
Loading mirror speeds from cached hostfile
 * base: mirror.qoxy.com
 * elrepo: mirrors.thzhost.com
 * epel: 0xa028cd70.xtremenitro.org
 * extras: mirror.qoxy.com
 * updates: mirror.qoxy.com
No packages marked for update
[root@hmc-P55A-UD3 ~]# yum install -y mssql-server
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
 * base: mirror.qoxy.com
 * elrepo: elrepo.mirror.angkasa.id
 * epel: epel.mirror.angkasa.id
 * extras: mirror.qoxy.com
 * updates: mirror.qoxy.com
Resolving Dependencies
--> Running transaction check
---> Package mssql-server.x86_64 0:14.0.900.75-1 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

================================================================================
 Package      Arch   Version          Repository                           Size
================================================================================
Installing:
 mssql-server x86_64 14.0.900.75-1    packages-microsoft-com-mssql-server 165 M

Transaction Summary
================================================================================
Install  1 Package

Total download size: 165 M
Installed size: 165 M
Downloading packages:
mssql-server-14.0.900.75-1.x86_64.rpm                      | 165 MB   00:10    
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : mssql-server-14.0.900.75-1.x86_64                            1/1

+--------------------------------------------------------------+
Please run 'sudo /opt/mssql/bin/mssql-conf setup'
to complete the setup of Microsoft SQL Server
+--------------------------------------------------------------+

  Verifying  : mssql-server-14.0.900.75-1.x86_64                            1/1

Installed:
  mssql-server.x86_64 0:14.0.900.75-1                                          

Complete!
[root@hmc-P55A-UD3 ~]#  /opt/mssql/bin/mssql-conf setup
The license terms for this product can be found in
/usr/share/doc/mssql-server or downloaded from:
https://go.microsoft.com/fwlink/?LinkId=852741&clcid=0x409

The privacy statement can be viewed at:
https://go.microsoft.com/fwlink/?LinkId=853010&clcid=0x409

Do you accept the license terms? [Yes/No]:Yes

Choose an edition of SQL Server:
  1) Evaluation (free, no production use rights, 180-day limit)
  2) Developer (free, no production use rights)
  3) Express (free)
  4) Web (PAID)
  5) Standard (PAID)
  6) Enterprise (PAID)
  7) I bought a license through a retail sales channel and have a product key to enter.

Details about editions can be found at
https://go.microsoft.com/fwlink/?LinkId=852748&clcid=0x409

Use of PAID editions of this software requires separate licensing through a
Microsoft Volume Licensing program.
By choosing a PAID edition, you are verifying that you have the appropriate
number of licenses in place to install and run this software.

Enter your edition(1-7): 2
Enter the SQL Server system administrator password:
The specified password does not meet SQL Server password policy requirements because it is too short. The password must be at least 8 characters
Enter the SQL Server system administrator password:
Confirm the SQL Server system administrator password:
Configuring SQL Server...

This is an evaluation version.  There are [157] days left in the evaluation period.
Created symlink from /etc/systemd/system/multi-user.target.wants/mssql-server.service to /usr/lib/systemd/system/mssql-server.service.
Setup has completed successfully. SQL Server is now starting.


As I have disabled the firewalld in my desktop and installed command line tools in previous blog, I can directly connect locally.

[oracle@hmc-P55A-UD3 bin]$ sqlcmd -S localhost -U sa
Password:
1> create database testdb;
2> go
1> select name from sys.databases;
2> go
name                                                                                                                           
--------------------------------------------------------------------------------------------------------------------------------
master                                                                                                                         
tempdb                                                                                                                         
model                                                                                                                          
msdb                                                                                                                           
testdb                                                                                                                         

(5 rows affected)




Install sqlcmd and bcp the SQL Server command-line tools on Linux (centos 7.3)

Followed

Install sqlcmd and bcp the SQL Server command-line tools on Linux

 

tested okay from centos7.3 to sql server express 2014 running on windows 10.

[oracle@hmc-P55A-UD3 bin]$ ls -l
total 752
-rwxr-xr-x. 1 root root 239320 May  9 15:09 bcp
-rwxr-xr-x. 1 root root 525856 May  9 15:09 sqlcmd
[oracle@hmc-P55A-UD3 bin]$ pwd
/opt/mssql-tools/bin

[oracle@hmc-P55A-UD3 ~]$ sqlcmd -U perfstat  -S 192.168.0.181,1433
Password:
1> select count(*) from sys.sql_logins;
2> go;
3> go
Msg 102, Level 15, State 1, Server MP04ISLLIQY1\SQLEXPRESS, Line 2
Incorrect syntax near 'go'.
1> select count(*) from sys.sql_logins;
2> go
          
-----------
          4

(1 rows affected)