Wednesday, August 23, 2017

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)
 

Friday, August 18, 2017

30 Things to Do After Minimal RHEL/CentOS 7 Installation

30 Things to Do After Minimal RHEL/CentOS 7 Installation

 

 

auto dbstart stop in centos 7 (systemd)

a nice article, followed and works perfectly.

tested on 12.2c on centos 7.3 

Only place to replaced is the ORACLE_BASE AND ORACLE_HOME according to my env.



[root@hmc-P55A-UD3 system]# ps -ef |grep tns
root        28     2  0 06:53 ?        00:00:00 [netns]
root      5336  4752  0 07:20 pts/0    00:00:00 grep --color=auto tns
[root@hmc-P55A-UD3 system]# ps -ef |grep pmon
root      5435  4752  0 07:20 pts/0    00:00:00 grep --color=auto pmon

[root@hmc-P55A-UD3 system]# systemctl start oracle_network.service
[root@hmc-P55A-UD3 system]# ps -ef |grep tns
root        28     2  0 06:53 ?        00:00:00 [netns]
oracle    5449     1  0 07:20 ?        00:00:00 /app/oracle/product/12.2.0/dbhome_1/bin/tnslsnr LISTENER -inherit
root      5456  4752  0 07:20 pts/0    00:00:00 grep --color=auto tns

[root@hmc-P55A-UD3 system]# systemctl start oracledb

[root@hmc-P55A-UD3 system]# systemctl status oracledb
● oracledb.service - Oracle databases service
   Loaded: loaded (/usr/lib/systemd/system/oracledb.service; enabled; vendor preset: disabled)
   Active: active (running) since Fri 2017-08-18 07:20:49 EDT; 16s ago
  Process: 5463 ExecStart=/app/oracle/product/12.2.0/dbhome_1/bin/dbstart /app/oracle/product/12.2.0/dbhome_1 (code=exited, status=0/SUCCESS)
   CGroup: /system.slice/oracledb.service
           ├─5564 ora_pmon_orcl
           ├─5566 ora_clmn_orcl
           ├─5568 ora_psp0_orcl
           ├─5570 ora_vktm_orcl
           ├─5574 ora_gen0_orcl
           ├─5576 ora_mman_orcl
           ├─5580 ora_gen1_orcl
           ├─5584 ora_diag_orcl
           ├─5586 ora_ofsd_orcl
           ├─5590 ora_dbrm_orcl
           ├─5592 ora_vkrm_orcl
           ├─5594 ora_svcb_orcl
           ├─5596 ora_pman_orcl
           ├─5598 ora_dia0_orcl
           ├─5600 ora_dbw0_orcl
           ├─5602 ora_lgwr_orcl
           ├─5604 ora_ckpt_orcl
           ├─5606 ora_lg00_orcl
           ├─5608 ora_smon_orcl
           ├─5610 ora_lg01_orcl
           ├─5612 ora_smco_orcl
           ├─5615 ora_reco_orcl
           ├─5617 ora_w000_orcl
           ├─5619 ora_lreg_orcl
           ├─5621 ora_w001_orcl
           ├─5623 ora_pxmn_orcl
           ├─5627 ora_mmon_orcl
           ├─5629 ora_mmnl_orcl
           ├─5631 ora_d000_orcl
           ├─5633 ora_s000_orcl
           ├─5635 ora_tmon_orcl
           ├─5645 ora_tt00_orcl
           ├─5647 ora_tt01_orcl
           ├─5649 ora_tt02_orcl
           ├─5653 ora_aqpc_orcl
           ├─5657 ora_p000_orcl
           ├─5659 ora_p001_orcl
           ├─5661 ora_p002_orcl
           ├─5663 ora_p003_orcl
           ├─5665 ora_p004_orcl
           ├─5667 ora_p005_orcl
           ├─5669 ora_p006_orcl
           ├─5671 ora_p007_orcl
           ├─5673 ora_p008_orcl
           ├─5675 ora_p009_orcl
           ├─5677 ora_p00a_orcl
           ├─5679 ora_p00b_orcl
           ├─5681 ora_p00c_orcl
           ├─5683 ora_p00d_orcl
           ├─5685 ora_p00e_orcl
           ├─5687 ora_p00f_orcl
           ├─5841 ora_cjq0_orcl
           ├─5962 ora_j000_orcl
           ├─5964 ora_j001_orcl
           ├─5966 ora_s001_orcl
           ├─5968 ora_qm02_orcl
           ├─5970 ora_qm03_orcl
           ├─5972 ora_q002_orcl
           ├─5974 ora_q003_orcl
           └─5976 ora_q004_orcl

Aug 18 07:20:38 hmc-P55A-UD3 systemd[1]: Starting Oracle databases service...
Aug 18 07:20:38 hmc-P55A-UD3 dbstart[5463]: Processing Database instance "orcl": log file /app/oracle/product/12.2.0/dbhome_1/startup.log
Aug 18 07:20:49 hmc-P55A-UD3 systemd[1]: Started Oracle databases service.
[root@hmc-P55A-UD3 system]# systemctl stop oracledb

[oracle@hmc-P55A-UD3 bin]$ ps -ef |grep pmon
oracle    6147  4949  0 07:22 pts/1    00:00:00 grep --color=auto pmon
[oracle@hmc-P55A-UD3 bin]$ ps -ef |grep tns
root        28     2  0 06:53 ?        00:00:00 [netns]
oracle    6152  4949  0 07:22 pts/1    00:00:00 grep --color=auto tns

Tuesday, August 08, 2017

SQL server audit related links

Collected some related to SQL server audit setup, writing to windows event log.



Auditing Failed Logins in SQL Server


How-To Write SQL Server Audit Events To Windows Security Log

After following these steps, you should successfully start seeing your Microsoft SQL Server Audit Logs in the Windows Security Log. They’re usually listed as Event Code 33205, so you can filter your Security Logs to only show those events.


SQL Server Audit (Database Engine)

All editions of SQL Server support server level audits. All editions support database level audits beginning with SQL Server 2016 SP1. Prior to that, database level auditing was limited to Enterprise, Developer, and Evaluation editions


Any authenticated user can read and write to the Windows Application event log. The Application event log requires lower permissions than the Windows Security event log and is less secure than the Windows Security event log.


Attaching a Database with an Audit Defined


Attaching a database that has an audit specification and specifies a GUID that does not exist on the server will cause an orphaned audit specification. Because an audit with a matching GUID does not exist on the server instance, no audit events will be recorded. To correct this situation, use the ALTER DATABASE AUDIT SPECIFICATION command to connect the orphaned audit specification to an existing server audit. Or, use the CREATE SERVER AUDIT command to create a new server audit with the specified GUID.
You can attach a database that has an audit specification defined on it to another edition of SQL Server that does not support SQL Server audit, such as SQL Server Express but it will not record audit events.



Auditing Administrators


Members of the sysadmin fixed server role are identified as the dbo user in each database. To audit actions of the administrators, audit the actions of the dbo user.


Creating and Managing Audits with SQL Server Management Studio

Audit-related nodes are located as follows in the Object Explorer hierarchy in Management Studio.
SQL Server Instance
---- Security
-------- Audits
-------- Server Audit Specifications
SQL Server Instance
---- Databases
-------- Database Name
------------ Security
---------------- Database Audit Specifications


Create a Server Audit and Server Audit Specification

Create a Server Audit and Database Audit Specification




SQL Server Audit Action Groups and Actions


Write SQL Server Audit Events to the Security Log



SQL Server Audit Support in Different Editions and Versions  ( info for version prior to sql 2016)
Sat, 23 Nov 2013 14:02:05 GMT


Here is a summary of the SQL Server Audit support in the different editions:

click meclick meclick me
Edition SQL Server 2008 and 2008 R2 SQL Server 2012 and 2014
Enterprise Server- and database-level Server- and database-level
Evaluation Server- and database-level Server- and database-level
Developer Server- and database-level Server- and database-level
Datacenter Server- and database-level N/A
Business Intelligence None Server-level
Standard None Server-level
Web None Server-level
Express None Server-level



sql server ports 445 and 1433


445 is not a SQL port, is a SMB port. It is involved in SQL Server only if you use named pipes protocol, as named pipes are over SMB and this in turn uses 445 for 'SMB over IP', aka. as SMB 'NETBIOSless' as opposed to the old NetBIOS based SMB, which uses 137-139.


To configure the server to listen on specific protocols, use SQL Server configuration Manager. To configure the client allowed protocols, see Configuring Client Network Protocols.