Thursday, April 30, 2015

Configure Windows 2012 Server network to join domain using netsh scripting

C:\Users\Administrator>netsh interface ip show config

Configuration for interface "Ethernet0"
    DHCP enabled:                         Yes
    IP Address:                           192.168.229.134
    Subnet Prefix:                        192.168.229.0/24 (mask 255.255.255.0)
    Default Gateway:                      192.168.229.2
    Gateway Metric:                       0
    InterfaceMetric:                      10
    DNS servers configured through DHCP:  192.168.229.2
    Register with which suffix:           Primary only
    WINS servers configured through DHCP: 192.168.229.2

Configuration for interface "Loopback Pseudo-Interface 1"
    DHCP enabled:                         No
    IP Address:                           127.0.0.1
    Subnet Prefix:                        127.0.0.0/8 (mask 255.0.0.0)
    InterfaceMetric:                      50
    Statically Configured DNS Servers:    None
    Register with which suffix:           Primary only
    Statically Configured WINS Servers:   None

C:\Users\Administrator>netsh interface ipv4 set address "Ethernet0" static 192.168.229.210 255.255.255.0 192.168.229.200 1


C:\Users\Administrator>netsh interface ipv4 set dnsserver "Ethernet0" static 192.168.229.200 primary


C:\Users\Administrator>netsh interface ip show config

Configuration for interface "Ethernet0"
    DHCP enabled:                         No
    IP Address:                           192.168.229.210
    Subnet Prefix:                        192.168.229.0/24 (mask 255.255.255.0)
    Default Gateway:                      192.168.229.200
    Gateway Metric:                       1
    InterfaceMetric:                      10
    Statically Configured DNS Servers:    192.168.229.200
    Register with which suffix:           Primary only
    Statically Configured WINS Servers:   None

Configuration for interface "Loopback Pseudo-Interface 1"
    DHCP enabled:                         No
    IP Address:                           127.0.0.1
    Subnet Prefix:                        127.0.0.0/8 (mask 255.0.0.0)
    InterfaceMetric:                      50
    Statically Configured DNS Servers:    None
    Register with which suffix:           Primary only
    Statically Configured WINS Servers:   None

C:\Users\Administrator>netdom renamecomputer %computername% /newname:VMMSQL03
This operation will rename the computer WIN-7FVEEOP8PPF
to VMMSQL03.

Certain services, such as the Certificate Authority, rely on a fixed machine
name. If any services of this type are running on WIN-7FVEEOP8PPF,
then a computer name change would have an adverse impact.

Do you want to proceed (Y or N)?
y
The computer needs to be restarted in order to complete the operation.

The command completed successfully.


C:\Users\Administrator>shutdown /r /t 0

C:\Users\Administrator>netdom join VMMSQL03 /domain:dbaglobe.com /ud:dbaglobe\administrator /pd:*
Type the password associated with the domain user:

The computer needs to be restarted in order to complete the operation.

The command completed successfully.

C:\Users\Administrator>shutdown /r /t 0 /f

Saturday, April 18, 2015

Windows Server 2012 R2 prevent automatic logoff due to inactivity

Unlocked the missing Power Settings feature in Server 2012.

  1. Open the following registry key -HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Power\PowerSettings\7516b95f-f776-4464-8c53-06167f40cc99\8EC4B3A5-6868-48c2-BE75-4F3044BE88A7

  2. Set the following value - Attributes => 2

  3. Now open Control Panel>Power Options>Change Plan Settings>Change Advanced Power Settings
    a. The new Display section Console lock display off timeout is now available.
    b. Configure your “Plugged in” value accordingly (0 to disable)

  4. Set timeout to 0 to turn it off.

enter image description here

Tuesday, April 14, 2015

Differences between snapshot isolation and read committed isolation using row versioning

https://technet.microsoft.com/en-us/library/ms189050(v=sql.105).aspx

 

Property

Read-committed isolation level using row versioning

Snapshot isolation level

The database option that must be set to ON to enable the required support.

READ_COMMITTED_SNAPSHOT

ALLOW_SNAPSHOT_ISOLATION

How a session requests the specific type of row versioning.

Use the default read-committed isolation level, or run the SET TRANSACTION ISOLATION LEVEL statement to specify the READ COMMITTED isolation level. This can be done after the transaction starts.

Requires the execution of SET TRANSACTION ISOLATION LEVEL to specify the SNAPSHOT isolation level before the start of the transaction.

The version of data read by statements.

All data that was committed before the start of each statement.

All data that was committed before the start of each transaction.

How updates are handled.

Reverts from row versions to actual data to select rows to update and uses update locks on the data rows selected. Acquires exclusive locks on actual data rows to be modified. No update conflict detection.

Uses row versions to select rows to update. Tries to acquire an exclusive lock on the actual data row to be modified, and if the data has been modified by another transaction, an update conflict occurs and the snapshot transaction is terminated.

Update conflict detection.

None.

Integrated support. Cannot be disabled.

Sunday, April 12, 2015

Explain Lead/Lag with simple example

select RegionID,RegionDescription,
lag(RegionDescription,1,0) over(order by RegionID) proceding_row,
lead(RegionDescription,1,0) over(order by RegionID) following_row
from [dbo].[Region]

image

Created Indexed View in T-SQL

Wrong Example:

create view Employees_View as select * from [dbo].[Employees]
GO
create index Employees_View_Idx on Employees_View(LastName,First_name);
GO

What’s are possible errors for above 2 T-SQL Statements


Msg 1939, Level 16, State 1, Line 5
Cannot create index on view 'Employees_View' because the view is not schema bound.


Msg 1054, Level 15, State 6, Procedure Employees_View, Line 13
Syntax '*' is not allowed in schema-bound objects.


Msg 1940, Level 16, State 1, Line 36
Cannot create index on view 'Employees_View'. It does not have a unique clustered index.


Msg 1942, Level 16, State 1, Line 38
Cannot create index on view 'NORTHWND2.dbo.Employees_View'. It contains text, ntext, image, FILESTREAM or xml columns.

Correct Example:


drop view Employees_View
GO
create view Employees_View with schemabinding
as select [EmployeeID]
      ,[LastName]
      ,[FirstName]
      ,[Title]
      ,[TitleOfCourtesy]
      ,[BirthDate]
      ,[HireDate]
      ,[Address]
      ,[City]
      ,[Region]
      ,[PostalCode]
      ,[Country]
      ,[HomePhone]
      ,[Extension]
      ,[ReportsTo]
      ,[PhotoPath] from [dbo].[Employees]
GO

create unique clustered index Employees_View_Idx on Employees_View(LastName,Firstname);
GO

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

<row CustomerID="29580" AccountNumber="AW00029580" SalesOrderID="43665" OrderDate="2005-07-01T00:00:00" />
<row CustomerID="29580" AccountNumber="AW00029580" SalesOrderID="44284" OrderDate="2005-10-01T00:00:00" />
<row CustomerID="29580" AccountNumber="AW00029580" SalesOrderID="45043" OrderDate="2006-01-01T00:00:00" />
<row CustomerID="29580" AccountNumber="AW00029580" SalesOrderID="45782" OrderDate="2006-04-01T00:00:00" />
<row CustomerID="29580" AccountNumber="AW00029580" SalesOrderID="46611" OrderDate="2006-07-01T00:00:00" />
<row CustomerID="29580" AccountNumber="AW00029580" SalesOrderID="47666" OrderDate="2006-10-01T00:00:00" />
<row CustomerID="29580" AccountNumber="AW00029580" SalesOrderID="48757" OrderDate="2007-01-01T00:00:00" />
<row CustomerID="29580" AccountNumber="AW00029580" SalesOrderID="49826" OrderDate="2007-04-01T00:00:00" />
<row CustomerID="29580" AccountNumber="AW00029580" SalesOrderID="51089" OrderDate="2007-07-01T00:00:00" />
<row CustomerID="29580" AccountNumber="AW00029580" SalesOrderID="55241" OrderDate="2007-10-01T00:00:00" />
<row CustomerID="29580" AccountNumber="AW00029580" SalesOrderID="61182" OrderDate="2008-01-01T00:00:00" />
<row CustomerID="29580" AccountNumber="AW00029580" SalesOrderID="67266" OrderDate="2008-04-01T00:00:00" />

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 ,ELEMENTS

<row>
  <CustomerID>29580</CustomerID>
  <AccountNumber>AW00029580</AccountNumber>
  <SalesOrderID>43665</SalesOrderID>
  <OrderDate>2005-07-01T00:00:00</OrderDate>
</row>
<row>
  <CustomerID>29580</CustomerID>
  <AccountNumber>AW00029580</AccountNumber>
  <SalesOrderID>44284</SalesOrderID>
  <OrderDate>2005-10-01T00:00:00</OrderDate>
</row>
<row>
  <CustomerID>29580</CustomerID>
  <AccountNumber>AW00029580</AccountNumber>
  <SalesOrderID>45043</SalesOrderID>
  <OrderDate>2006-01-01T00:00:00</OrderDate>
</row>
<row>
  <CustomerID>29580</CustomerID>
  <AccountNumber>AW00029580</AccountNumber>
  <SalesOrderID>45782</SalesOrderID>
  <OrderDate>2006-04-01T00:00:00</OrderDate>
</row>
<row>
  <CustomerID>29580</CustomerID>
  <AccountNumber>AW00029580</AccountNumber>
  <SalesOrderID>46611</SalesOrderID>
  <OrderDate>2006-07-01T00:00:00</OrderDate>
</row>
<row>
  <CustomerID>29580</CustomerID>
  <AccountNumber>AW00029580</AccountNumber>
  <SalesOrderID>47666</SalesOrderID>
  <OrderDate>2006-10-01T00:00:00</OrderDate>
</row>
<row>
  <CustomerID>29580</CustomerID>
  <AccountNumber>AW00029580</AccountNumber>
  <SalesOrderID>48757</SalesOrderID>
  <OrderDate>2007-01-01T00:00:00</OrderDate>
</row>
<row>
  <CustomerID>29580</CustomerID>
  <AccountNumber>AW00029580</AccountNumber>
  <SalesOrderID>49826</SalesOrderID>
  <OrderDate>2007-04-01T00:00:00</OrderDate>
</row>
<row>
  <CustomerID>29580</CustomerID>
  <AccountNumber>AW00029580</AccountNumber>
  <SalesOrderID>51089</SalesOrderID>
  <OrderDate>2007-07-01T00:00:00</OrderDate>
</row>
<row>
  <CustomerID>29580</CustomerID>
  <AccountNumber>AW00029580</AccountNumber>
  <SalesOrderID>55241</SalesOrderID>
  <OrderDate>2007-10-01T00:00:00</OrderDate>
</row>
<row>
  <CustomerID>29580</CustomerID>
  <AccountNumber>AW00029580</AccountNumber>
  <SalesOrderID>61182</SalesOrderID>
  <OrderDate>2008-01-01T00:00:00</OrderDate>
</row>
<row>
  <CustomerID>29580</CustomerID>
  <AccountNumber>AW00029580</AccountNumber>
  <SalesOrderID>67266</SalesOrderID>
  <OrderDate>2008-04-01T00:00:00</OrderDate>
</row>

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 AUTO

<C CustomerID="29580" AccountNumber="AW00029580">
  <Soh SalesOrderID="43665" OrderDate="2005-07-01T00:00:00" />
  <Soh SalesOrderID="44284" OrderDate="2005-10-01T00:00:00" />
  <Soh SalesOrderID="45043" OrderDate="2006-01-01T00:00:00" />
  <Soh SalesOrderID="45782" OrderDate="2006-04-01T00:00:00" />
  <Soh SalesOrderID="46611" OrderDate="2006-07-01T00:00:00" />
  <Soh SalesOrderID="47666" OrderDate="2006-10-01T00:00:00" />
  <Soh SalesOrderID="48757" OrderDate="2007-01-01T00:00:00" />
  <Soh SalesOrderID="49826" OrderDate="2007-04-01T00:00:00" />
  <Soh SalesOrderID="51089" OrderDate="2007-07-01T00:00:00" />
  <Soh SalesOrderID="55241" OrderDate="2007-10-01T00:00:00" />
  <Soh SalesOrderID="61182" OrderDate="2008-01-01T00:00:00" />
  <Soh SalesOrderID="67266" OrderDate="2008-04-01T00:00:00" />
</C>

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 AUTO ,ELEMENTS

<C>
  <CustomerID>29580</CustomerID>
  <AccountNumber>AW00029580</AccountNumber>
  <Soh>
    <SalesOrderID>43665</SalesOrderID>
    <OrderDate>2005-07-01T00:00:00</OrderDate>
  </Soh>
  <Soh>
    <SalesOrderID>44284</SalesOrderID>
    <OrderDate>2005-10-01T00:00:00</OrderDate>
  </Soh>
  <Soh>
    <SalesOrderID>45043</SalesOrderID>
    <OrderDate>2006-01-01T00:00:00</OrderDate>
  </Soh>
  <Soh>
    <SalesOrderID>45782</SalesOrderID>
    <OrderDate>2006-04-01T00:00:00</OrderDate>
  </Soh>
  <Soh>
    <SalesOrderID>46611</SalesOrderID>
    <OrderDate>2006-07-01T00:00:00</OrderDate>
  </Soh>
  <Soh>
    <SalesOrderID>47666</SalesOrderID>
    <OrderDate>2006-10-01T00:00:00</OrderDate>
  </Soh>
  <Soh>
    <SalesOrderID>48757</SalesOrderID>
    <OrderDate>2007-01-01T00:00:00</OrderDate>
  </Soh>
  <Soh>
    <SalesOrderID>49826</SalesOrderID>
    <OrderDate>2007-04-01T00:00:00</OrderDate>
  </Soh>
  <Soh>
    <SalesOrderID>51089</SalesOrderID>
    <OrderDate>2007-07-01T00:00:00</OrderDate>
  </Soh>
  <Soh>
    <SalesOrderID>55241</SalesOrderID>
    <OrderDate>2007-10-01T00:00:00</OrderDate>
  </Soh>
  <Soh>
    <SalesOrderID>61182</SalesOrderID>
    <OrderDate>2008-01-01T00:00:00</OrderDate>
  </Soh>
  <Soh>
    <SalesOrderID>67266</SalesOrderID>
    <OrderDate>2008-04-01T00:00:00</OrderDate>
  </Soh>
</C>

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

 

image

 

image

image

image

image

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)

Wednesday, April 1, 2015

Adding Oracle RAC Service using srvctl command

[oracle@vmxrac01 ~]$ srvctl add service -db orcl -service orcl_hr -preferred orcl1,orcl2
[oracle@vmxrac01 ~]$
srvctl start service -d orcl -service orcl_hr
[oracle@vmxrac01 ~]$ srvctl config service  -db orcl -service orcl_hr
Service name: orcl_hr
Server pool:
Cardinality: 2
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Global: false
Commit Outcome: false
Failover type:
Failover method:
TAF failover retries:
TAF failover delay:
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE
Edition:
Pluggable database name:
Maximum lag time: ANY
SQL Translation Profile:
Retention: 86400 seconds
Replay Initiation Time: 300 seconds
Session State Consistency:
GSM Flags: 0
Service is enabled
Preferred instances: orcl1,orcl2
Available instances:
[oracle@vmxrac01 ~]$

[oracle@vmxrac01 ~]$ crsctl status res ora.orcl.orcl_hr.svc
NAME=ora.orcl.orcl_hr.svc
TYPE=ora.service.type
TARGET=ONLINE            , ONLINE
STATE=ONLINE on vmxrac01, ONLINE on vmxrac02

[oracle@vmxrac01 ~]$ crsctl status res ora.orcl.orcl_hr.svc -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.orcl.orcl_hr.svc
      1        ONLINE  ONLINE       vmxrac01                 STABLE
      2        ONLINE  ONLINE       vmxrac02                 STABLE
--------------------------------------------------------------------------------

[oracle@vmxrac01 ~]$ crsctl status res ora.orcl.orcl_hr.svc -p
NAME=ora.orcl.orcl_hr.svc
TYPE=ora.service.type
ACL=owner:oracle:rwx,pgrp:oinstall:r--,other::r--,group:dba:r-x,user:oracle:r-x
ACTIONS=
ACTION_SCRIPT=
ACTION_TIMEOUT=60
ACTIVE_PLACEMENT=0
AGENT_FILENAME=%CRS_HOME%/bin/oraagent%CRS_EXE_SUFFIX%
AGENT_PARAMETERS=
AQ_HA_NOTIFICATION=0
AUTO_START=restore
CARDINALITY=2
CHECK_INTERVAL=600
CHECK_TIMEOUT=30
CLB_GOAL=LONG
CLEAN_TIMEOUT=60
COMMIT_OUTCOME=0
DEGREE=1
DELETE_TIMEOUT=60
DESCRIPTION=Oracle Service resource
DTP=0
EDITION=
ENABLED=1
FAILOVER_DELAY=0
FAILOVER_METHOD=
FAILOVER_RETRIES=
FAILOVER_TYPE=
FAILURE_INTERVAL=0
FAILURE_THRESHOLD=0
GEN_SERVICE_NAME=orcl_hr
GLOBAL=false
GSM_FLAGS=0
HOSTING_MEMBERS=
INSTANCE_FAILOVER=1
INTERMEDIATE_TIMEOUT=0
LOAD=1
LOGGING_LEVEL=1
MANAGEMENT_POLICY=AUTOMATIC
MAX_LAG_TIME=ANY
MODIFY_TIMEOUT=60
NLS_LANG=
OFFLINE_CHECK_INTERVAL=0
PLACEMENT=restricted
PLUGGABLE_DATABASE=
RELOCATE_BY_DEPENDENCY=1
REPLAY_INITIATION_TIME=300
RESTART_ATTEMPTS=0
RETENTION=86400
RLB_GOAL=NONE
ROLE=PRIMARY
SCRIPT_TIMEOUT=60
SERVER_CATEGORY=
SERVER_POOLS=ora.orcl_orcl_hr
SERVICE_NAME=orcl_hr
SERVICE_NAME_PQ=
SERVICE_TYPE=MAIN
SESSION_NOREPLAY=false
SESSION_STATE_CONSISTENCY=
SQL_TRANSLATION_PROFILE=
START_CONCURRENCY=0
START_DEPENDENCIES=hard(ora.orcl.db,type:ora.cluster_vip_net1.type) weak(type:ora.listener.type) pullup(type:ora.cluster_vip_net1.type) pullup:always(ora.orcl.db)
START_TIMEOUT=600
STOP_CONCURRENCY=0
STOP_DEPENDENCIES=hard(intermediate:ora.orcl.db,type:ora.cluster_vip_net1.type)
STOP_TIMEOUT=600
TAF_FAILOVER_DELAY=
TAF_POLICY=NONE
TYPE_VERSION=3.2
UPTIME_THRESHOLD=1h
USER_WORKLOAD=yes
USE_STICKINESS=0
USR_ORA_DISCONNECT=false
USR_ORA_ENV=
USR_ORA_FLAGS=
USR_ORA_OPEN_MODE=
USR_ORA_OPI=false
USR_ORA_STOP_MODE=