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
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)
[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
[oracle@hmc-P55A-UD3 bin]$ ls -l
[oracle@hmc-P55A-UD3 ~]$ sqlcmd -U perfstat -S 192.168.0.181,1433
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
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
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 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.
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:
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.
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 me | click me | click 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.
Saturday, August 05, 2017
SQL commands for getting bind variable value
$ cat chk_bind_value.sql
Prompt Enter SQL_ID
select SQL_ID,name,VALUE_STRING,VALUE_ANYDATA,POSITION,DUP_POSITION,last_captured
from V$SQL_BIND_CAPTURE i
where SQL_ID = '&1'
$ cat chk_hist_bind_value.sql
set pages 1000
select SQL_ID,name,VALUE_STRING,VALUE_ANYDATA,POSITION,DUP_POSITION,last_captured from dba_hist_sqlbind
where SQL_ID='&1' order by snap_id
/
Prompt Enter SQL_ID
select SQL_ID,name,VALUE_STRING,VALUE_ANYDATA,POSITION,DUP_POSITION,last_captured
from V$SQL_BIND_CAPTURE i
where SQL_ID = '&1'
$ cat chk_hist_bind_value.sql
set pages 1000
select SQL_ID,name,VALUE_STRING,VALUE_ANYDATA,POSITION,DUP_POSITION,last_captured from dba_hist_sqlbind
where SQL_ID='&1' order by snap_id
/
Using bind variables in SQL*Plus
mark two links for quick reference
You can declare a bind variable in SQL*Plus though, and select into that:
var l_test_quote varchar2(80); -- or whatever type/size you need
var l_test_id varchar2(80);
declare
l_id varchar2(80) :='test123';
begin
select test_quote, test_id
into :l_test_quote, :l_test_id
from order_link
where id = l_id;
end;
/
print l_test_quote
print l_test_id
Note the : before the references to the variables defined outside the block, indicating they are bind variables. l_id is declared inside the block so it does not have a preceding :.
In this case you could also define l_id outside the block, and avoid PL/SQL while still using a bind variable for that:
var l_id varchar2(80);
exec :l_id := 'test123';
select test_quote, test_id
from order_link
where id = :l_id;
Because the main query isn't PL/SQL any more (although the exec is; that's just a shorthand for a one-line anonymous block), you don't need to select ... into so you don't need to declare those variables.
Declare bind variables in SQL*Plus
You can declare a bind variable in SQL*Plus though, and select into that:
var l_test_quote varchar2(80); -- or whatever type/size you need
var l_test_id varchar2(80);
declare
l_id varchar2(80) :='test123';
begin
select test_quote, test_id
into :l_test_quote, :l_test_id
from order_link
where id = l_id;
end;
/
print l_test_quote
print l_test_id
Note the : before the references to the variables defined outside the block, indicating they are bind variables. l_id is declared inside the block so it does not have a preceding :.
In this case you could also define l_id outside the block, and avoid PL/SQL while still using a bind variable for that:
var l_id varchar2(80);
exec :l_id := 'test123';
select test_quote, test_id
from order_link
where id = :l_id;
Because the main query isn't PL/SQL any more (although the exec is; that's just a shorthand for a one-line anonymous block), you don't need to select ... into so you don't need to declare those variables.
amazing server side result cache (2) - benchmark
select sysdate from dual for 1 million times -- 40 seconds
set serveroutput on
DECLARE
x NUMBER := 1000000;
CRM_DATE varchar2(20) ;
n1 number;
BEGIN
n1 := dbms_utility.get_time;
FOR i IN 1..x LOOP
--CRM_DATE :=TO_char(SYSDATE);
select sysdate into CRM_DATE from dual;
END LOOP;
dbms_output.put_line( dbms_utility.get_time - n1 );
END;
/
4041
count a empty table for 1 millions times - 61.28 seconds
set serveroutput on
DECLARE
x NUMBER := 1000000;
CRM_DATE varchar2(20) ;
n1 number;
c number;
BEGIN
n1 := dbms_utility.get_time;
FOR i IN 1..x LOOP
--CRM_DATE :=TO_char(SYSDATE);
-- select sysdate into CRM_DATE from dual;
SELECT COUNT(*) into c FROM C_DATE WHERE EXPIRY_DT > SYSDATE;
END LOOP;
dbms_output.put_line( dbms_utility.get_time - n1 );
END;
/
6128
18057 for looping 3 millions times
now test a function and cache it
CREATE OR REPLACE FUNCTION count_crm_date
RETURN NUMBER
RESULT_CACHE
AS
c number;
BEGIN
SELECT count(*) INTO c FROM C_DATE WHERE EXPIRY_DT > SYSDATE;
RETURN c;
END count_crm_date;
/
set serveroutput on
DECLARE
x NUMBER := 1000000;
CRM_DATE varchar2(20) ;
n1 number;
BEGIN
n1 := dbms_utility.get_time;
FOR i IN 1..x LOOP
--CRM_DATE :=TO_char(SYSDATE);
select sysdate into CRM_DATE from dual;
END LOOP;
dbms_output.put_line( dbms_utility.get_time - n1 );
END;
/
4041
count a empty table for 1 millions times - 61.28 seconds
set serveroutput on
DECLARE
x NUMBER := 1000000;
CRM_DATE varchar2(20) ;
n1 number;
c number;
BEGIN
n1 := dbms_utility.get_time;
FOR i IN 1..x LOOP
--CRM_DATE :=TO_char(SYSDATE);
-- select sysdate into CRM_DATE from dual;
SELECT COUNT(*) into c FROM C_DATE WHERE EXPIRY_DT > SYSDATE;
END LOOP;
dbms_output.put_line( dbms_utility.get_time - n1 );
END;
/
6128
18057 for looping 3 millions times
now test a function and cache it
CREATE OR REPLACE FUNCTION count_crm_date
RETURN NUMBER
RESULT_CACHE
AS
c number;
BEGIN
SELECT count(*) INTO c FROM C_DATE WHERE EXPIRY_DT > SYSDATE;
RETURN c;
END count_crm_date;
/
ensure it works , meanwhile cached it.
> var r number;
> exec :r :=count_crm_date(1);
PL/SQL procedure successfully completed.
> set serveroutput on
> print r
R
----------
0
create a procedure to loop the function.
CREATE OR REPLACE PROCEDURE run_test AS
l_start NUMBER;
l_loops NUMBER := 1000000;
l_value number;
BEGIN
l_start := DBMS_UTILITY.get_time;
FOR i IN 1 .. l_loops LOOP
l_value := count_crm_date;
END LOOP;
DBMS_OUTPUT.put_line('First Loop: ' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');
l_start := DBMS_UTILITY.get_time;
FOR i IN 1 .. l_loops LOOP
l_value := count_crm_date;
END LOOP;
DBMS_OUTPUT.put_line('Second Loop: ' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');
END run_test;
/
Procedure created.
> EXEC run_test;
First Loop: 86 hsecs (0.86 second)
Second Loop: 81 hsecs (0.81 second)
PL/SQL procedure successfully completed.
That is much faster , 0.81 seconds compare to not-cached 61 seconds, about 75x faster
SELECT DBMS_RESULT_CACHE.status FROM dual;
STATUS
------------------------------------------------------------------------------------------------------------------------------------------------------
ENABLED
SYS@VCRMDV> EXEC DBMS_RESULT_CACHE.memory_report(detailed => true);
PL/SQL procedure successfully completed.
> set serveroutput on
> l
1* SELECT DBMS_RESULT_CACHE.status FROM dual
> EXEC DBMS_RESULT_CACHE.memory_report(detailed => true);
R e s u l t C a c h e M e m o r y R e p o r t
[Parameters]
Block Size = 1K bytes
Maximum Cache Size = 5248K bytes (5248 blocks)
Maximum Result Size = 262K bytes (262 blocks)
[Memory]
Total Memory = 5516768 bytes [1.028% of the Shared Pool]
... Fixed Memory = 5352 bytes [0.001% of the Shared Pool]
....... Memory Mgr = 200 bytes
....... Cache Mgr = 208 bytes
....... Bloom Fltr = 2K bytes
....... State Objs = 2896 bytes
... Dynamic Memory = 5511416 bytes [1.027% of the Shared Pool]
....... Overhead = 137464 bytes
........... Hash Table = 64K bytes (4K buckets)
........... Chunk Ptrs = 24K bytes (3K slots)
........... Chunk Maps = 12K bytes
........... Miscellaneous = 35064 bytes
....... Cache Memory = 5248K bytes (5248 blocks)
........... Unused Memory = 0 blocks
........... Used Memory = 5248 blocks
............... Dependencies = 2 blocks (2 count)
............... Results = 5246 blocks
................... PLSQL = 5246 blocks (5246 count)
PL/SQL procedure successfully completed.
open another session to see if caching works globally.
--in another session
> conn perfstat
Enter password:
Connected.
> set serveroutput on
> exec run_test;
First Loop: 92 hsecs
Second Loop: 83 hsecs
PL/SQL procedure successfully completed.
It works !
loopig for 3000000 times,
exec run_test;
First Loop: 267 hsecs
Second Loop: 276 hsecs
PL/SQL procedure successfully completed.
SQL> SELECT * FROM V$RESULT_CACHE_STATISTICS;
ID
----------
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
1
Block Size (Bytes)
1024
2
Block Count Maximum
3936
3
Block Count Current
32
4
Result Size Maximum (Blocks)
196
5
Create Count Success
1
6
Create Count Failure
0
7
Find Count
6000001
8
Invalidation Count
0
9
Delete Count Invalid
0
10
Delete Count Valid
0
11
Hash Chain Length
1
12
Find Copy Count
6000001
13
Latch (Share)
0
13 rows selected.
I also captured AWR snapshot before and after executing test .
From AWR report, I don't see millions of executions of such query.
This helped me to solve a production issue due to such query selecting from DUAL table, which DUAL table is a dictionary table causing latch contention (row cache objects) in extreme high concurrency environment.
Nice feature in 11gR2 comes default - no caching related parameter to adjust in my case.
amazing server side result cache (1)
We are facing a functioned called about 5 thousands times per second, by hundreds of stored procedure from 20+ app server.
The result is static, so server side result cache come into my picture.
started from PL/SQL result cache after I learned it is different from sql*plus. (I am not PL/SQL expert).
a.) RESULT_CACHE is the key word when defining a funciton.
i.e below is wrong
set serveroutput on
DECLARE
x NUMBER := 3000000;
CRM_DATE varchar2(20) ;
n1 number;
c number;
BEGIN
n1 := dbms_utility.get_time;
FOR i IN 1..x LOOP
--CRM_DATE :=TO_char(SYSDATE);
-- select sysdate into CRM_DATE from dual;
SELECT /*+ RESULT_CACHE */ COUNT(*) into c FROM C_DATE WHERE EXPIRY_DT > SYSDATE;
END LOOP;
dbms_output.put_line( dbms_utility.get_time - n1 );
END;
/
b.) DBMS_RESULT_CACHE.memory_report is immediate tool I like, as long as I see few rows as output -- nothing is cached.
-- simple function for test
CREATE OR REPLACE FUNCTION count_crm_date
2 RETURN NUMBER
3 RESULT_CACHE
4 AS
5 c number;
6 BEGIN
7 SELECT count(*) INTO c FROM C_DATE WHERE EXPIRY_DT > SYSDATE;
8 --C :=P1;
9 -- Pause for 1 second.
10 --DBMS_LOCK.sleep(1);
11 RETURN c;
12 END count_crm_date;
13 /
Function created.
set serveroutput on
> var r number;
> print r;
R
----------
> EXEC DBMS_RESULT_CACHE.memory_report(detailed => true);
R e s u l t C a c h e M e m o r y R e p o r t
[Parameters]
Block Size = 1K bytes
Maximum Cache Size = 5248K bytes (5248 blocks)
Maximum Result Size = 262K bytes (262 blocks)
[Memory]
Total Memory = 166400 bytes [0.031% of the Shared Pool]
... Fixed Memory = 5352 bytes [0.001% of the Shared Pool]
....... Memory Mgr = 200 bytes
....... Cache Mgr = 208 bytes
....... Bloom Fltr = 2K bytes
....... State Objs = 2896 bytes
... Dynamic Memory = 161048 bytes [0.030% of the Shared Pool]
....... Overhead = 128280 bytes
........... Hash Table = 64K bytes (4K buckets)
........... Chunk Ptrs = 24K bytes (3K slots)
........... Chunk Maps = 12K bytes
........... Miscellaneous = 25880 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 29 blocks
........... Used Memory = 3 blocks
............... Dependencies = 2 blocks (2 count)
............... Results = 1 blocks
................... Invalid = 1 blocks (1 count)
PL/SQL procedure successfully completed.
> EXEC DBMS_RESULT_CACHE.flush;
PL/SQL procedure successfully completed.
> EXEC DBMS_RESULT_CACHE.memory_report(detailed => true);
R e s u l t C a c h e M e m o r y R e p o r t
[Parameters]
Block Size = 1K bytes
Maximum Cache Size = 5248K bytes (5248 blocks)
Maximum Result Size = 262K bytes (262 blocks)
[Memory]
Total Memory = 5352 bytes [0.001% of the Shared Pool]
... Fixed Memory = 5352 bytes [0.001% of the Shared Pool]
....... Memory Mgr = 200 bytes
....... Cache Mgr = 208 bytes
....... Bloom Fltr = 2K bytes
....... State Objs = 2896 bytes
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]
PL/SQL procedure successfully completed.
> exec :r :=count_crm_date;
PL/SQL procedure successfully completed.
> EXEC DBMS_RESULT_CACHE.memory_report(detailed => true);
R e s u l t C a c h e M e m o r y R e p o r t
[Parameters]
Block Size = 1K bytes
Maximum Cache Size = 5248K bytes (5248 blocks)
Maximum Result Size = 262K bytes (262 blocks)
[Memory]
Total Memory = 165152 bytes [0.031% of the Shared Pool]
... Fixed Memory = 5352 bytes [0.001% of the Shared Pool]
....... Memory Mgr = 200 bytes
....... Cache Mgr = 208 bytes
....... Bloom Fltr = 2K bytes
....... State Objs = 2896 bytes
... Dynamic Memory = 159800 bytes [0.030% of the Shared Pool]
....... Overhead = 127032 bytes
........... Hash Table = 64K bytes (4K buckets)
........... Chunk Ptrs = 24K bytes (3K slots)
........... Chunk Maps = 12K bytes
........... Miscellaneous = 24632 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 28 blocks
........... Used Memory = 4 blocks
............... Dependencies = 3 blocks (3 count)
............... Results = 1 blocks
................... PLSQL = 1 blocks (1 count)
PL/SQL procedure successfully completed.
> print r;
R
----------
0
The result is static, so server side result cache come into my picture.
started from PL/SQL result cache after I learned it is different from sql*plus. (I am not PL/SQL expert).
a.) RESULT_CACHE is the key word when defining a funciton.
i.e below is wrong
set serveroutput on
DECLARE
x NUMBER := 3000000;
CRM_DATE varchar2(20) ;
n1 number;
c number;
BEGIN
n1 := dbms_utility.get_time;
FOR i IN 1..x LOOP
--CRM_DATE :=TO_char(SYSDATE);
-- select sysdate into CRM_DATE from dual;
SELECT /*+ RESULT_CACHE */ COUNT(*) into c FROM C_DATE WHERE EXPIRY_DT > SYSDATE;
END LOOP;
dbms_output.put_line( dbms_utility.get_time - n1 );
END;
/
-- simple function for test
CREATE OR REPLACE FUNCTION count_crm_date
2 RETURN NUMBER
3 RESULT_CACHE
4 AS
5 c number;
6 BEGIN
7 SELECT count(*) INTO c FROM C_DATE WHERE EXPIRY_DT > SYSDATE;
8 --C :=P1;
9 -- Pause for 1 second.
10 --DBMS_LOCK.sleep(1);
11 RETURN c;
12 END count_crm_date;
13 /
Function created.
set serveroutput on
> var r number;
> print r;
R
----------
> EXEC DBMS_RESULT_CACHE.memory_report(detailed => true);
R e s u l t C a c h e M e m o r y R e p o r t
[Parameters]
Block Size = 1K bytes
Maximum Cache Size = 5248K bytes (5248 blocks)
Maximum Result Size = 262K bytes (262 blocks)
[Memory]
Total Memory = 166400 bytes [0.031% of the Shared Pool]
... Fixed Memory = 5352 bytes [0.001% of the Shared Pool]
....... Memory Mgr = 200 bytes
....... Cache Mgr = 208 bytes
....... Bloom Fltr = 2K bytes
....... State Objs = 2896 bytes
... Dynamic Memory = 161048 bytes [0.030% of the Shared Pool]
....... Overhead = 128280 bytes
........... Hash Table = 64K bytes (4K buckets)
........... Chunk Ptrs = 24K bytes (3K slots)
........... Chunk Maps = 12K bytes
........... Miscellaneous = 25880 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 29 blocks
........... Used Memory = 3 blocks
............... Dependencies = 2 blocks (2 count)
............... Results = 1 blocks
................... Invalid = 1 blocks (1 count)
PL/SQL procedure successfully completed.
> EXEC DBMS_RESULT_CACHE.flush;
PL/SQL procedure successfully completed.
> EXEC DBMS_RESULT_CACHE.memory_report(detailed => true);
R e s u l t C a c h e M e m o r y R e p o r t
[Parameters]
Block Size = 1K bytes
Maximum Cache Size = 5248K bytes (5248 blocks)
Maximum Result Size = 262K bytes (262 blocks)
[Memory]
Total Memory = 5352 bytes [0.001% of the Shared Pool]
... Fixed Memory = 5352 bytes [0.001% of the Shared Pool]
....... Memory Mgr = 200 bytes
....... Cache Mgr = 208 bytes
....... Bloom Fltr = 2K bytes
....... State Objs = 2896 bytes
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]
PL/SQL procedure successfully completed.
> exec :r :=count_crm_date;
PL/SQL procedure successfully completed.
> EXEC DBMS_RESULT_CACHE.memory_report(detailed => true);
R e s u l t C a c h e M e m o r y R e p o r t
[Parameters]
Block Size = 1K bytes
Maximum Cache Size = 5248K bytes (5248 blocks)
Maximum Result Size = 262K bytes (262 blocks)
[Memory]
Total Memory = 165152 bytes [0.031% of the Shared Pool]
... Fixed Memory = 5352 bytes [0.001% of the Shared Pool]
....... Memory Mgr = 200 bytes
....... Cache Mgr = 208 bytes
....... Bloom Fltr = 2K bytes
....... State Objs = 2896 bytes
... Dynamic Memory = 159800 bytes [0.030% of the Shared Pool]
....... Overhead = 127032 bytes
........... Hash Table = 64K bytes (4K buckets)
........... Chunk Ptrs = 24K bytes (3K slots)
........... Chunk Maps = 12K bytes
........... Miscellaneous = 24632 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 28 blocks
........... Used Memory = 4 blocks
............... Dependencies = 3 blocks (3 count)
............... Results = 1 blocks
................... PLSQL = 1 blocks (1 count)
PL/SQL procedure successfully completed.
> print r;
R
----------
0
Subscribe to:
Posts (Atom)
