Donghua's Blog - DBAGlobe

Sunday, April 12, 2015

Different XML output format for “XML RAW”, “XML AUTO” and “ELEMENTS”

select C.CustomerID,C.AccountNumber,Soh.SalesOrderID,Soh.OrderDate
from AdventureWorks2012.Sales.SalesOrderHeader Soh
inner join AdventureWorks2012.Sales.Customer C
on Soh.CustomerID=C.CustomerID
where C.CustomerID=29580
for XML RAW

Saturday, April 11, 2015

Using WITH ENCRYPTION option obfuscates the definition of the procedure in T-SQL

CREATE PROCEDURE [dbo].[CustOrdersDetail_Encrypted] @OrderID int
WITH ENCRYPTION
AS
SELECT ProductName,
    UnitPrice=ROUND(Od.UnitPrice, 2),
    Quantity,
    Discount=CONVERT(int, Discount * 100),
    ExtendedPrice=ROUND(CONVERT(money, Quantity * (1 - Discount) * Od.UnitPrice), 2)
FROM Products P, [Order Details] Od
WHERE Od.ProductID = P.ProductID and Od.OrderID = @OrderID

Effects after encryption

Thursday, April 9, 2015

How To Set the AUDIT_SYSLOG_LEVEL Paramete

1. Edit /etc/syslog.conf (upto RHEL5) or /etc/rsyslog.conf (RHEL6 onwards) to including following lines

(Must put lines before line "*.info ...", otherwise captured to /var/log/messages, rather than /var/log/oracle-audit)

# Classify Oracle audit log into local1.warning
local1.warning /var/log/oracle-audit.log

*.info;mail.none;authpriv.none;cron.none /var/log/messages

2. Restart syslogd or rsyslogd service

[root@vmxdb01 ~]# service syslogd restart <-- font="" rhel5="">
[root@vmxdb01 ~]# systemctl restart rsyslog.service <-- font="" rhel7="">

3. Modify Oracle parameter audit_syslog_level & audit_trail

SQL> show parameter audit

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/cdborcl/
adump
audit_sys_operations boolean TRUE
audit_syslog_level string
audit_trail string DB
unified_audit_sga_queue_size integer 1048576

SQL> alter system set audit_trail=OS scope=spfile;

System altered.

SQL> alter system set audit_syslog_level="local1.warning" scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1342177280 bytes
Fixed Size 2924160 bytes
Variable Size 855638400 bytes
Database Buffers 469762048 bytes
Redo Buffers 13852672 bytes
Database mounted.
Database opened.
SQL> show parameter audit

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/cdborcl/
adump
audit_sys_operations boolean TRUE
audit_syslog_level string LOCAL1.WARNING
audit_trail string OS
unified_audit_sga_queue_size integer 1048576

4. Verify Oracle Audit Log generated

[root@vmxdb01 log]# tail -f /var/log/oracle-audit.log
Apr 9 22:18:27 vmxdb01 journal: Oracle Audit[5190]: LENGTH: "274" SESSIONID:[6] "190024" ENTRYID:[1] "1" STATEMENT:[1] "1" USERID:[10] "C##DONGHUA" USERHOST:[20] "vmxdb01.dbaglobe.com" TERMINAL:[5] "pts/1" ACTION:[3] "100" RETURNCODE:[4] "1045" COMMENT$TEXT:[26] "Authenticated by: DATABASE" OS$USERID:[6] "oracle" DBID:[10] "2860248834"
Apr 9 22:18:45 vmxdb01 journal: Oracle Audit[5196]: LENGTH: "283" SESSIONID:[6] "200019" ENTRYID:[1] "1" STATEMENT:[1] "1" USERID:[6] "SYSTEM" USERHOST:[20] "vmxdb01.dbaglobe.com" TERMINAL:[5] "pts/1" ACTION:[3] "100" RETURNCODE:[1] "0" COMMENT$TEXT:[26] "Authenticated by: DATABASE" OS$USERID:[6] "oracle" DBID:[10] "2860248834" PRIV$USED:[1] "5"
Apr 9 22:18:59 vmxdb01 journal: Oracle Audit[5196]: LENGTH: "227" SESSIONID:[6] "200019" ENTRYID:[1] "1" USERID:[6] "SYSTEM" ACTION:[3] "101" RETURNCODE:[1] "0" LOGOFF$PREAD:[1] "4" LOGOFF$LREAD:[4] "3013" LOGOFF$LWRITE:[2] "20" LOGOFF$DEAD:[1] "0" DBID:[10] "2860248834" SESSIONCPU:[2] "13"
Apr 9 22:18:59 vmxdb01 journal: Oracle Audit[5205]: LENGTH: "288" SESSIONID:[6] "200020" ENTRYID:[1] "1" STATEMENT:[1] "1" USERID:[10] "C##DONGHUA" USERHOST:[20] "vmxdb01.dbaglobe.com" TERMINAL:[5] "pts/1" ACTION:[3] "100" RETURNCODE:[1] "0" COMMENT$TEXT:[26] "Authenticated by: DATABASE" OS$USERID:[6] "oracle" DBID:[10] "2860248834" PRIV$USED:[1] "5"

More information, refer to Oracle support article: How To Set the AUDIT_SYSLOG_LEVEL Parameter? (Doc ID 553225.1)

Change the init level on Redhat Linux 7

# systemd uses 'targets' instead of runlevels. By default, there are two main targets:
#
# multi-user.target: analogous to runlevel 3

# graphical.target: analogous to runlevel 5

[root@vmxdb01 ~]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.1 (Maipo)

[root@vmxdb01 ~]# systemctl get-default
graphical.targt

[root@vmxdb01 ~]# systemctl set-default multi-user.target
rm '/etc/systemd/system/default.target'
ln -s '/usr/lib/systemd/system/multi-user.target' '/etc/systemd/system/default.target'

[root@vmxdb01 ~]# systemctl get-default
multi-user.target

Quick fix for error "memory_target needs larger /dev/shm"

2015-04-07 11:52:44.455000 +08:00
Starting ORACLE instance (normal) (OS id: 2292)
CLI notifier numLatches:3 maxDescs:519
WARNING: You are trying to use the MEMORY_TARGET feature. This feature requires the /dev/shm file system to be mounted for at least 1342177280 bytes. /dev/shm is either not mounted or is mounted with available space less than this size. Please fix this so that MEMORY_TARGET can work as expected. Current available is 1025826816 and used is 665677824 bytes. Ensure that the mount point is /dev/shm for this directory.
memory_target needs larger /dev/shm

[oracle@vmxdb01 ~]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/ol-root 36G 29G 7.9G 79% /
devtmpfs 1.6G 0 1.6G 0% /dev
tmpfs 1.6G 635M 979M 40% /dev/shm

[root@vmxdb01 ~]# echo "tmpfs /dev/shm tmpfs defaults,size=2g 0 0" >> /etc/fstab

[root@vmxdb01 ~]# mount tmpfs
[root@vmxdb01 ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/ol-root 36G 29G 7.9G 79% /
devtmpfs 1.6G 0 1.6G 0% /dev
tmpfs 2.0G 0 2.0G 0% /dev/shm
tmpfs 1.6G 8.9M 1.6G 1% /run
tmpfs 1.6G 0 1.6G 0% /sys/fs/cgroup
/dev/sda1 997M 223M 774M 23% /boot
tmpfs 2.0G 0 2.0G 0% /dev/shm

-- Start Oracle database

2015-04-09 21:37:48.768000 +08:00
Starting ORACLE instance (normal) (OS id: 3273)
CLI notifier numLatches:3 maxDescs:519
**********************************************************************
Dump of system resources acquired for SHARED GLOBAL AREA (SGA)
Per process system memlock (soft) limit = 128G
Expected per process system memlock (soft) limit to lock
SHARED GLOBAL AREA (SGA) into memory: 1280M
Available system pagesizes:
4K, 2048K
Supported system pagesize(s):
PAGESIZE AVAILABLE_PAGES EXPECTED_PAGES ALLOCATED_PAGES ERROR(s)
4K Configured 327682 327682 NONE
Reason for not supporting certain system pagesizes:
2048K - Dynamic allocate and free memory regions
**********************************************************************

[oracle@vmxdb01 ~]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/ol-root 36G 29G 7.9G 79% /
devtmpfs 1.6G 0 1.6G 0% /dev
tmpfs 2.0G 1.5G 614M 71% /dev/shm
tmpfs 1.6G 8.9M 1.6G 1% /run
tmpfs 1.6G 0 1.6G 0% /sys/fs/cgroup
/dev/sda1 997M 223M 774M 23% /boot

tmpfs 2.0G 1.5G 614M 71% /dev/shm

Tuesday, April 7, 2015

Change Oracle RAC SCAN from host file to DNS

Step 1: verify the DNS is working and remove the host file record

root@vmxdb01:~# nslookup
> vmxdb-scan.dbaglobe.com
Server: 192.168.1.1
Address: 192.168.1.1#53

Name: vmxdb-scan.dbaglobe.com
Address: 192.168.1.20
Name: vmxdb-scan.dbaglobe.com
Address: 192.168.1.21
Name: vmxdb-scan.dbaglobe.com
Address: 192.168.1.19

root@vmxdb01:~# vi /etc/hosts

## temporary hardcode scan host
#192.168.1.19 vmxdb-scan vmxdb-scan.dbaglobe.com

Step 2: Stop the scan listener and scan

root@vmxdb01:~# srvctl stop scan_listener
root@vmxdb01:~# srvctl stop scan

Step 3: Modify the scan based on the IPs returned by scan host name
root@vmxdb01:~# srvctl config scan
SCAN name: vmxdb-scan, Network: 1/192.168.1.64/255.255.255.192/net0
SCAN VIP name: scan1, IP: /vmxdb-scan/192.168.1.19

root@vmxdb01:~# srvctl modify scan -n vmxdb-scan
root@vmxdb01:~# srvctl config scan
SCAN name: vmxdb-scan, Network: 1/192.168.1.64/255.255.255.192/net0
SCAN VIP name: scan1, IP: /vmxdb-scan/192.168.1.19
SCAN VIP name: scan2, IP: /vmxdb-scan/192.168.1.20
SCAN VIP name: scan3, IP: /vmxdb-scan/192.168.1.21

Step 4: Update scan listener based on SCAN IPs

root@vmxdb01:~# srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1522

root@vmxdb01:~# srvctl modify scan_listener -u
root@vmxdb01:~# srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1522
SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1522
SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1522

Step 5: Start the scan listener and scan

root@vmxdb01:~# srvctl start scan
root@vmxdb01:~# srvctl start scan_listener

Enable Innodb lock monitor

mysql> prompt session 3>
PROMPT set to 'prompt session 3 '

session 3> CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB;
Query OK, 0 rows affected, 1 warning (0.03 sec)

As of MySQL 5.6.16, you can also enable the InnoDB Lock Monitor by setting the innodb_status_output_locks system variable to ON. As with the CREATE TABLE method for enabling InnoDB Monitors, both the InnoDB standard Monitor and InnoDB Lock Monitor must be enabled to have InnoDBLock Monitor data printed periodically:

session 3> status;

--------------

/usr/local/mysql/bin/mysql Ver 14.14 Distrib 5.6.23, for linux-glibc2.5 (x86_64) using EditLine wrapper

Connection id: 2

Current database: test

Current user: root@localhost

SSL: Not in use

Current pager: stdout

Using outfile: ''

Using delimiter: ;

Server version: 5.6.23-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Protocol version: 10

Connection: Localhost via UNIX socket

Server characterset: latin1

Db characterset: latin1

Client characterset: utf8

Conn. characterset: utf8

UNIX socket: /tmp/mysql-server1.sock

Uptime: 2 hours 40 min 41 sec

Threads: 3 Questions: 314 Slow queries: 1 Opens: 75 Flush tables: 1 Open tables: 68 Queries per second avg: 0.032

--------------

session 3> set GLOBAL innodb_status_output=ON;

Query OK, 0 rows affected (0.00 sec)

session 3> set GLOBAL innodb_status_output_locks=ON;

Query OK, 0 rows affected (0.00 sec)

session 3> show engine innodb status \G

*************************** 1. row ***************************

Type: InnoDB

Name:

Status:

=====================================

2015-04-07 14:04:05 7f0fed325700 INNODB MONITOR OUTPUT

=====================================

Per second averages calculated from the last 10 seconds

-----------------

BACKGROUND THREAD

-----------------

srv_master_thread loops: 47 srv_active, 0 srv_shutdown, 7787 srv_idle

srv_master_thread log flush and writes: 7834

----------

SEMAPHORES

----------

OS WAIT ARRAY INFO: reservation count 19

OS WAIT ARRAY INFO: signal count 19

Mutex spin waits 1, rounds 30, OS waits 0

RW-shared spins 16, rounds 480, OS waits 16

RW-excl spins 0, rounds 90, OS waits 3

Spin rounds per wait: 30.00 mutex, 30.00 RW-shared, 90.00 RW-excl

------------

TRANSACTIONS

------------

Trx id counter 3020

Purge done for trx's n:o < 3016 undo n:o < 0 state: running but idle

History list length 17

LIST OF TRANSACTIONS FOR EACH SESSION:

---TRANSACTION 0, not started

MySQL thread id 2, OS thread handle 0x7f0fed325700, query id 301 localhost root init

show engine innodb status

---TRANSACTION 3019, ACTIVE 2 sec starting index read

mysql tables in use 1, locked 1

LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)

MySQL thread id 4, OS thread handle 0x7f0fed2a3700, query id 300 localhost root updating

delete from tbl_inno

------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 6 page no 4 n bits 120 index `GEN_CLUST_INDEX` of table `test`.`tbl_inno` trx id 3019 lock_mode X waiting

Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 32

0: len 6; hex 000000000200; asc ;;

1: len 6; hex 000000000bb8; asc ;;

2: len 7; hex 2b000001bb0110; asc + ;;

3: len 4; hex 80000001; asc ;;

4: len 30; hex 612020202020202020202020202020202020202020202020202020202020; asc a ; (total 255 bytes);

------------------

TABLE LOCK table `test`.`tbl_inno` trx id 3019 lock mode IX

RECORD LOCKS space id 6 page no 4 n bits 120 index `GEN_CLUST_INDEX` of table `test`.`tbl_inno` trx id 3019 lock_mode X waiting

Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 32

0: len 6; hex 000000000200; asc ;;

1: len 6; hex 000000000bb8; asc ;;

2: len 7; hex 2b000001bb0110; asc + ;;

3: len 4; hex 80000001; asc ;;

4: len 30; hex 612020202020202020202020202020202020202020202020202020202020; asc a ; (total 255 bytes);

---TRANSACTION 3000, ACTIVE 1294 sec

5 lock struct(s), heap size 1184, 153 row lock(s), undo log entries 149

MySQL thread id 3, OS thread handle 0x7f0fed2e4700, query id 205 localhost root cleaning up

TABLE LOCK table `test`.`tbl_inno` trx id 3000 lock mode IX

RECORD LOCKS space id 6 page no 4 n bits 120 index `GEN_CLUST_INDEX` of table `test`.`tbl_inno` trx id 3000 lock_mode X

Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0

0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 32

0: len 6; hex 000000000200; asc ;;

1: len 6; hex 000000000bb8; asc ;;

2: len 7; hex 2b000001bb0110; asc + ;;

3: len 4; hex 80000001; asc ;;

4: len 30; hex 612020202020202020202020202020202020202020202020202020202020; asc a ; (total 255 bytes);

Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 32

0: len 6; hex 000000000201; asc ;;

1: len 6; hex 000000000bb8; asc ;;

2: len 7; hex 2b000001bb0136; asc + 6;;

3: len 4; hex 80000001; asc ;;

4: len 30; hex 612020202020202020202020202020202020202020202020202020202020; asc a ; (total 255 bytes);

RECORD LOCKS space id 6 page no 5 n bits 120 index `GEN_CLUST_INDEX` of table `test`.`tbl_inno` trx id 3000 lock_mode X

Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0

0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 32

0: len 6; hex 00000000021a; asc ;;

1: len 6; hex 000000000bb8; asc ;;

2: len 7; hex 2b000001bb04ec; asc + ;;

3: len 4; hex 80000001; asc ;;

4: len 30; hex 612020202020202020202020202020202020202020202020202020202020; asc a ; (total 255 bytes);

--------

FILE I/O

--------

I/O thread 0 state: waiting for completed aio requests (insert buffer thread)

I/O thread 1 state: waiting for completed aio requests (log thread)

I/O thread 2 state: waiting for completed aio requests (read thread)

I/O thread 3 state: waiting for completed aio requests (read thread)

I/O thread 4 state: waiting for completed aio requests (read thread)

I/O thread 5 state: waiting for completed aio requests (read thread)

I/O thread 6 state: waiting for completed aio requests (write thread)

I/O thread 7 state: waiting for completed aio requests (write thread)

I/O thread 8 state: waiting for completed aio requests (write thread)

I/O thread 9 state: waiting for completed aio requests (write thread)

Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,

ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0

Pending flushes (fsync) log: 0; buffer pool: 0

181 OS file reads, 669 OS file writes, 295 OS fsyncs

0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s

-------------------------------------

INSERT BUFFER AND ADAPTIVE HASH INDEX

-------------------------------------

Ibuf: size 1, free list len 0, seg size 2, 0 merges

merged operations:

insert 0, delete mark 0, delete 0

discarded operations:

insert 0, delete mark 0, delete 0

Hash table size 276671, node heap has 1 buffer(s)

0.00 hash searches/s, 0.00 non-hash searches/s

---

LOG

---

Log sequence number 1868688

Log flushed up to 1868688

Pages flushed up to 1868688

Last checkpoint at 1868688

0 pending log writes, 0 pending chkp writes

233 log i/o's done, 0.00 log i/o's/second

----------------------

BUFFER POOL AND MEMORY

----------------------

Total memory allocated 137363456; in additional pool allocated 0

Dictionary memory allocated 70412

Buffer pool size 8191

Free buffers 7874

Database pages 316

Old database pages 0

Modified db pages 0

Pending reads 0

Pending writes: LRU 0, flush list 0, single page 0

Pages made young 0, not young 0

0.00 youngs/s, 0.00 non-youngs/s

Pages read 165, created 151, written 414

0.00 reads/s, 0.00 creates/s, 0.00 writes/s

Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000

Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s

LRU len: 316, unzip_LRU len: 0

I/O sum[0]:cur[0], unzip sum[0]:cur[0]

--------------

ROW OPERATIONS

--------------

0 queries inside InnoDB, 0 queries in queue

0 read views open inside InnoDB

Main thread process no. 2448, id 139706340439808, state: sleeping

Number of rows inserted 149, updated 0, deleted 149, read 298

0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s

----------------------------

END OF INNODB MONITOR OUTPUT

============================

1 row in set (0.00 sec)

Donghua's Blog - DBAGlobe

Sunday, April 12, 2015

Different XML output format for “XML RAW”, “XML AUTO” and “ELEMENTS”

Saturday, April 11, 2015

Using WITH ENCRYPTION option obfuscates the definition of the procedure in T-SQL

Effects after encryption

Thursday, April 9, 2015

How To Set the AUDIT_SYSLOG_LEVEL Paramete

Change the init level on Redhat Linux 7

Quick fix for error "memory_target needs larger /dev/shm"

Tuesday, April 7, 2015

Change Oracle RAC SCAN from host file to DNS

Enable Innodb lock monitor

Disclaimer

Labels

Blog Archive

Search This Website

Tweets by Me