Monday, July 14, 2014

Creating Extended Statistics With Function-base Column Groups

There are nine restrictions to creating extended statistics. These restrictions are same for version 11.1, 11.2 and 12.1.. One of the constraints is "A column group can not contain expressions". Oracle documentation also provides an example what is a column group and what is an expression when it comes to extended statistics extension "An example column group can be "(c1, c2)" and an example expression can be "(c1 + c2)". In short if columns are comma separated then it will consider as a column group. However this causes a problem when creating extended statistics with functions. Give below is an example.
SQL> create table exstat (a number, b date);

SQL> select dbms_stats.create_extended_stats(user,'EXSTAT','(a,trunc(b))') from dual;
select dbms_stats.create_extended_stats(user,'EXSTAT','(a,trunc(b))') from dual
       *
ERROR at line 1:
ORA-20001: Invalid Extension: Column group can contain only columns seperated by comma
Trying to create extended statistics with column a and trunc(b) results in an error. What's clear from the error is that 1. Oracle was expecting a column group and 2. It must only contain columns separated by comma.
It is expecting a column group but the second portion of the extension is not recognized as a column hence the error. To overcome this create a function base index. For the above extended statistics extension following index was created
create index aidx on exstat(a,trunc(b));
After which the creation of the extended statistics works.
SQL> select dbms_stats.create_extended_stats(user,'EXSTAT','(a,trunc(b))') from dual;

DBMS_STATS.CREATE_EXTENDED_STATS(USER,'EXSTAT','(A,TRUNC(B))')
--------------------------------------------------------------------------------
SYS_STUE4B2X1G802ME0XHTBYWFY_Q
Simply create the index first and then extended statistics.



Trying to drop an extended statistics extension that uses function results in the following error
SQL>  exec dbms_stats.drop_extended_stats(user,'EXSTAT','(A,TRUNC(B))');
BEGIN dbms_stats.drop_extended_stats(user,'EXSTAT','(A,TRUNC(B))'); END;

*
ERROR at line 1:
ORA-20000: extension "(A,TRUNC(B))" does not exist
ORA-06512: at "SYS.DBMS_STATS", line 13055
ORA-06512: at "SYS.DBMS_STATS", line 45105
ORA-06512: at line 1
Even though the extension is present the error message says "does not exist". To resolve this drop the index created for the extended statistics extension
SQL> DROP INDEX AIDX;

Index dropped.
After which extended statistics is dropped without any issue.
SQL> exec dbms_stats.drop_extended_stats(user,'EXSTAT','(A,TRUNC(B))');

PL/SQL procedure successfully completed.
This was tested on 12.1, 11.2.0.4 and 11.2.0.3 and all exhibited the same behavior. But this test failed on 11.1.0.7. even after creating the index.
SQL> create table exstat (a number, b date);

SQL> select dbms_stats.create_extended_stats(user,'EXSTAT','(a,trunc(b))') from dual;
select dbms_stats.create_extended_stats(user,'EXSTAT','(a,trunc(b))') from dual
       *
ERROR at line 1:
ORA-20001: Invalid Extension: Column group can contain only columns seperated by comma

SQL> create index aidx on exstat(a,trunc(b));

SQL> select dbms_stats.create_extended_stats(user,'EXSTAT','(a,trunc(b))') from dual;
select dbms_stats.create_extended_stats(user,'EXSTAT','(a,trunc(b))') from dual
       *
ERROR at line 1:
ORA-20001: Invalid Extension: Column group can contain only columns seperated by comma
The index has created a hidden virtual column. Trying to add a virtual column complains of duplication
SQL>  alter table exstat add (c as (trunc(b)));
 alter table exstat add (c as (trunc(b)))
                         *
ERROR at line 1:
ORA-54015: Duplicate column expression was specified
But having a virtual column with the function expression didn't help either.
SQL> drop index aidx;

SQL>  alter table exstat add (c as (trunc(b)));

SQL>  select dbms_stats.create_extended_stats(user,'EXSTAT','(a,trunc(b))') from dual;
 select dbms_stats.create_extended_stats(user,'EXSTAT','(a,trunc(b))') from dual
        *
ERROR at line 1:
ORA-20001: Invalid Extension: Column group can contain only columns seperated by comma
It's temping to use the virtual column itself in the extended statistics column group. But "extension cannot contain a virtual column" is restriction number one!
SQL> select dbms_stats.create_extended_stats(user,'EXSTAT','(a,c)') from dual;
select dbms_stats.create_extended_stats(user,'EXSTAT','(a,c)') from dual
       *
ERROR at line 1:
ORA-20001: Error when processing extension -  virtual column is referenced in a column expression
Posted by at
Labels: ,

Tuesday, July 1, 2014

GNS Setup for RAC

Setting up a GNS is not a must to install a RAC unless it's a flex cluster where the use of a GNS is mandatory. There are some advantages to using GNS, especially when it comes to adding and removing nodes and their IP assignment. This post list steps for a GNS setup that could be used for clusterware installation with GNS. The clusterware used in this case is 12cR1. GNS setup is independent of any cluster version and steps listed here could be used for a GNS setup to be used with 11gR2 clusterware as well. In this configuration public host names are resolved through the DNS and the private IPs are resolved through hosts files on the node.
GNS was setup on a separate server, in the following text 192.168.0.85 is the IP of this separate server (unimaginatively named rhel5new) where the DNS will run and 192.168.0.87 is the GNS VIP and GNS sub-domain is rac.mydomain.net.
It must be stated by no means this is comprehensive GNS setup and intended as a help for DBAs get test system setup. For production system setup always seek the services of a network administrator to setup the GNS.
1. Install rpm required to setup the GNS, this include DHCP related rpms (dhcp-3.0.5-31.el5_8.1) and DNS related rpms.
2. Modify the /etc/dhcpd.conf file and add the domain, DNS server IP and the range of IPs handed out by dhcp
cat /etc/dhcpd.conf
#
# DHCP Server Configuration file.
#   see /usr/share/doc/dhcp*/dhcpd.conf.sample
#
ddns-update-style interim;
ignore client-updates;

subnet 192.168.0.0 netmask 255.255.255.0 {

 option subnet-mask              255.255.255.0;
 option domain-name               "rac.mydomain.net";
 option domain-name-servers      192.168.0.85;

 range  192.168.0.86 192.168.0.98;
 default-lease-time 21600;
 max-lease-time 43200;

}
3. Edit the /etc/named.conf file and add the entries related to DNS setup.
# cat /etc/named.conf
options {
        listen-on port 53 { 192.168.0.85; 127.0.0.1; };
        directory       "/var/named";
        dump-file       "/var/named/data/cache_dump.db";
        statistics-file "/var/named/data/named_stats.txt";
        memstatistics-file "/var/named/data/named_mem_stats.txt";
        recursion yes;
        allow-transfer {"none";};
};

logging {
        channel default_debug {
                file "data/named.run";
                severity dynamic;
        };
};

zone "mydomain.net" IN {
        type master;
        file "mydomain.net.zone";
        allow-update { none; };
};

zone "0.168.192.in-addr.arpa" IN {
        type master;
        file "rev.mydomain.net.zone";
        allow-update { none; };
};

#include "/etc/named.rfc1912.zones";
#include "/etc/named.root.key";
4. Create the forward look-up file with an entry for sub-domain delegation.
cat /var/named/mydomain.net.zone
$TTL 1H         ; Time to live
$ORIGIN mydomain.net.
@       IN      SOA     rhel5new  root.mydomain.net.  (
                        2009011201      ; serial (todays date + todays serial #)
                        3H              ; refresh 3 hours
                        1H              ; retry 1 hour
                        1W              ; expire 1 week
                        1D )            ; minimum 24 hour

                A         192.168.0.85
                NS        rhel5new

rhel5new    A         192.168.0.85
gns         A         192.168.0.87

$ORIGIN rac.mydomain.net.
@      IN         NS        gns.mydomain.net.
5. Reverse look-up file. In this case reverse look up entry is added only for the GNS VIP
cat /var/named/rev.mydomain.net.zone
$ORIGIN 0.168.192.in-addr.arpa.
$TTL 1H
@       IN      SOA     rhel5new     root.mydomain.net. (      2
                                                3H
                                                1H
                                                1W
                                                1H )
0.168.192.in-addr.arpa.         IN NS      rhel5new.

85     IN PTR  rhel5new.mydomain.net.
87     IN PTR  gns.mydomain.net.


6. Use cluvfy tool with precrsinst option to check the suitability of GNS setup. This seem to check mainly if the GNS sub domain and VIP are in use, if so will flag unsuccessful. This doesn't check if the actual delegation happens which could only be checked after the clusterware has been installed.
$ ./runcluvfy.sh comp gns -precrsinst -domain rac.mydomain.net -vip 192.168.0.87 -verbose -n rhel12c1,rhel12c2

Verifying GNS integrity

Checking GNS integrity...
Checking if the GNS subdomain name is valid...
The GNS subdomain name "rac.mydomain.net" is a valid domain name
Checking if the GNS VIP is a valid address...
GNS VIP "192.168.0.87" resolves to a valid IP address
Checking the status of GNS VIP...

GNS integrity check passed

Verification of GNS integrity was successful.
7. Use the GNS VIP and the sub-domain name during the clusterware installation.

When using GNS the virtual hostname is auto generated.
Summary

8. Use nslookup to verify the delegation is working. If the delegation is working nslookup with the DNS IP will resolve the SCAN name with a non-authoritive answer. 
$ nslookup rhel12c-scan.rac.mydomain.net 192.168.0.85
Server:         192.168.0.85
Address:        192.168.0.85#53

Non-authoritative answer:
Name:   rhel12c-scan.rac.mydomain.net
Address: 192.168.0.89
Name:   rhel12c-scan.rac.mydomain.net
Address: 192.168.0.96
Name:   rhel12c-scan.rac.mydomain.net
Address: 192.168.0.88

$ nslookup rhel12c-scan.rac.mydomain.net 192.168.0.85
Server:         192.168.0.85
Address:        192.168.0.85#53

Non-authoritative answer:
Name:   rhel12c-scan.rac.mydomain.net
Address: 192.168.0.88
Name:   rhel12c-scan.rac.mydomain.net
Address: 192.168.0.89
Name:   rhel12c-scan.rac.mydomain.net
Address: 192.168.0.96

$ nslookup rhel12c-scan.rac.mydomain.net 192.168.0.85
Server:         192.168.0.85
Address:        192.168.0.85#53

Non-authoritative answer:
Name:   rhel12c-scan.rac.mydomain.net
Address: 192.168.0.96
Name:   rhel12c-scan.rac.mydomain.net
Address: 192.168.0.88
Name:   rhel12c-scan.rac.mydomain.net
Address: 192.168.0.89
Non-authoritative answer is given when the query was answered with the help of another namesapce. Using the direct GNS VIP will give also resolve the scan name but this will be a "direct" answer
$ nslookup rhel12c-scan.rac.mydomain.net 192.168.0.87
Server:         192.168.0.87
Address:        192.168.0.87#53

Name:   rhel12c-scan.rac.mydomain.net
Address: 192.168.0.96
Name:   rhel12c-scan.rac.mydomain.net
Address: 192.168.0.89
Name:   rhel12c-scan.rac.mydomain.net
Address: 192.168.0.88

$ nslookup rhel12c-scan.rac.mydomain.net 192.168.0.87
Server:         192.168.0.87
Address:        192.168.0.87#53

Name:   rhel12c-scan.rac.mydomain.net
Address: 192.168.0.96
Name:   rhel12c-scan.rac.mydomain.net
Address: 192.168.0.89
Name:   rhel12c-scan.rac.mydomain.net
Address: 192.168.0.88
When nslookup called by specifying the GNS VIP the IPs associated with the SCAN do not rotate whereas the when SCAN is resolved through the DNS IP it does rotated in a round robin fashion. Oracle has confirmed that this expected behavior. 11gR2 also exhibited the same behavior.
Dig could be used to find out what is the authority section.
dig rhel12c-scan.rac.mydomain.net

; <<>> DiG 9.8.2rc1-RedHat-9.8.2-0.17.rc1.el6 <<>> rhel12c-scan.rac.mydomain.net
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 35411
;; flags: qr rd ra; QUERY: 1, ANSWER: 3, AUTHORITY: 1, ADDITIONAL: 1

;; QUESTION SECTION:
;rhel12c-scan.rac.mydomain.net.  IN      A

;; ANSWER SECTION:
rhel12c-scan.rac.mydomain.net. 120 IN    A       192.168.0.96
rhel12c-scan.rac.mydomain.net. 120 IN    A       192.168.0.88
rhel12c-scan.rac.mydomain.net. 120 IN    A       192.168.0.89

;; AUTHORITY SECTION:
rac.mydomain.net.        3600    IN      NS      gns.mydomain.net.

;; ADDITIONAL SECTION:
gns.mydomain.net.        3600    IN      A       192.168.0.87

;; Query time: 5 msec
;; SERVER: 192.168.0.85#53(192.168.0.85)
;; WHEN: Tue Jun 10 12:40:50 2014
;; MSG SIZE  rcvd: 128
Beside SCAN the host VIPs could also be resolved through the GNS
$ nslookup rhel12c1-vip.rac.mydomain.net 192.168.0.85
Server:         192.168.0.85
Address:        192.168.0.85#53

Non-authoritative answer:
Name:   rhel12c1-vip.rac.mydomain.net
Address: 192.168.0.95

$ nslookup rhel12c2-vip.rac.mydomain.net 192.168.0.85
Server:         192.168.0.85
Address:        192.168.0.85#53

Non-authoritative answer:
Name:   rhel12c2-vip.rac.mydomain.net
Address: 192.168.0.91
9. Edit the resolve.conf and include the DNS IP so the SCAN resolution and delegation happens automatically. Edit the nsswitch.conf and place the nis entry as the end of the search list. For more on this follow Oracle documentation.

10.Cluvfy also provides postcrsinst option to check the GNS.
$ cluvfy comp gns -postcrsinst -verbose

Verifying GNS integrity

Checking GNS integrity...
Checking if the GNS subdomain name is valid...
The GNS subdomain name "rac.mydomain.net" is a valid domain name
Checking if the GNS VIP belongs to same subnet as the public network...
Public network subnets "192.168.0.0, 192.168.0.0, 192.168.0.0, 192.168.0.0, 192.168.0.0" match with the GNS VIP "192.168.0.0, 192.168.0.0, 192.168.0.0, 192.168.0.0, 192.168.0.0"
Checking if the GNS VIP is a valid address...
GNS VIP "gns.mydomain.net" resolves to a valid IP address
Checking the status of GNS VIP...
Checking if FDQN names for domain "rac.mydomain.net" are reachable

GNS resolved IP addresses are reachable

GNS resolved IP addresses are reachable

GNS resolved IP addresses are reachable
Checking status of GNS resource...
  Node          Running?                  Enabled?
  ------------  ------------------------  ------------------------
  rhel12c1      no                        yes
  rhel12c2      yes                       yes

GNS resource configuration check passed
Checking status of GNS VIP resource...
  Node          Running?                  Enabled?
  ------------  ------------------------  ------------------------
  rhel12c1      no                        yes
  rhel12c2      yes                       yes

GNS VIP resource configuration check passed.

GNS integrity check passed

Verification of GNS integrity was successful.
11. srvctl config will list all GNS related information.
srvctl config gns -list -a
GNS is enabled.
GNS is listening for DNS server requests on port 53
GNS is using port 5,353 to connect to mDNS
GNS status: OK
Domain served by GNS: rac.mydomain.net
GNS version: 12.1.0.1.0
Globally unique identifier of the cluster where GNS is running: 4217101cdaea4fbebf2339cfa673b58b
Name of the cluster where GNS is running: rhel12c
Cluster type: server.
GNS log level: 1.
GNS listening addresses: tcp://192.168.0.87:60360.
Oracle-GNS A 192.168.0.87 Unique Flags: 0x15
rhel12c-scan A 192.168.0.88 Unique Flags: 0x81
rhel12c-scan A 192.168.0.89 Unique Flags: 0x81
rhel12c-scan A 192.168.0.96 Unique Flags: 0x81
rhel12c-scan1-vip A 192.168.0.96 Unique Flags: 0x81
rhel12c-scan2-vip A 192.168.0.89 Unique Flags: 0x81
rhel12c-scan3-vip A 192.168.0.88 Unique Flags: 0x81
rhel12c.Oracle-GNS SRV Target: Oracle-GNS Protocol: tcp Port: 60360 Weight: 0 Priority: 0 Flags: 0x15
rhel12c.Oracle-GNS TXT CLUSTER_NAME="rhel12c", CLUSTER_GUID="4217101cdaea4fbebf2339cfa673b58b", NODE_ADDRESS="192.168.0.87", SERVER_STATE="RUNNING", VERSION="12.1.0.1.0", DOMAIN="rac.mydomain.net" Flags: 0x15
rhel12c1-vip A 192.168.0.95 Unique Flags: 0x81
rhel12c2-vip A 192.168.0.91 Unique Flags: 0x81
The IPs assigned to VIPs and SCAN are stored in the OCR (possible to read from the ocrdump file) but could change across cluster reboots.
srvctl config gns -list -a
GNS is enabled.
GNS is listening for DNS server requests on port 53
GNS is using port 5,353 to connect to mDNS
GNS status: OK
Domain served by GNS: rac.mydomain.net
GNS version: 12.1.0.1.0
Globally unique identifier of the cluster where GNS is running: 4217101cdaea4fbebf2339cfa673b58b
Name of the cluster where GNS is running: rhel12c
Cluster type: server.
GNS log level: 1.
GNS listening addresses: tcp://192.168.0.87:60360.
Oracle-GNS A 192.168.0.87 Unique Flags: 0x15
rhel12c-scan A 192.168.0.88 Unique Flags: 0x81
rhel12c-scan A 192.168.0.89 Unique Flags: 0x81
rhel12c-scan A 192.168.0.96 Unique Flags: 0x81
rhel12c-scan1-vip A 192.168.0.96 Unique Flags: 0x81
rhel12c-scan2-vip A 192.168.0.89 Unique Flags: 0x81
rhel12c-scan3-vip A 192.168.0.88 Unique Flags: 0x81
rhel12c.Oracle-GNS SRV Target: Oracle-GNS Protocol: tcp Port: 60360 Weight: 0 Priority: 0 Flags: 0x15
rhel12c.Oracle-GNS TXT CLUSTER_NAME="rhel12c", CLUSTER_GUID="4217101cdaea4fbebf2339cfa673b58b", NODE_ADDRESS="192.168.0.87", SERVER_STATE="RUNNING", VERSION="12.1.0.1.0", DOMAIN="rac.mydomain.net" Flags: 0x15
rhel12c1-vip A 192.168.0.95 Unique Flags: 0x81
rhel12c2-vip A 192.168.0.91 Unique Flags: 0x81

srvctl config gns -list -a
GNS is enabled.
GNS is listening for DNS server requests on port 53
GNS is using port 5,353 to connect to mDNS
GNS status: OK
Domain served by GNS: rac.mydomain.net
GNS version: 12.1.0.1.0
Globally unique identifier of the cluster where GNS is running: 4217101cdaea4fbebf2339cfa673b58b
Name of the cluster where GNS is running: rhel12c
Cluster type: server.
GNS log level: 1.
GNS listening addresses: tcp://192.168.0.87:28251.
Oracle-GNS A 192.168.0.87 Unique Flags: 0x15
rhel12c-scan A 192.168.0.89 Unique Flags: 0x81
rhel12c-scan A 192.168.0.92 Unique Flags: 0x1
rhel12c-scan A 192.168.0.96 Unique Flags: 0x81
rhel12c-scan1-vip A 192.168.0.96 Unique Flags: 0x81
rhel12c-scan2-vip A 192.168.0.89 Unique Flags: 0x81
rhel12c-scan3-vip A 192.168.0.92 Unique Flags: 0x1
rhel12c.Oracle-GNS SRV Target: Oracle-GNS Protocol: tcp Port: 28251 Weight: 0 Priority: 0 Flags: 0x15
rhel12c.Oracle-GNS TXT CLUSTER_NAME="rhel12c", CLUSTER_GUID="4217101cdaea4fbebf2339cfa673b58b", NODE_ADDRESS="192.168.0.87", SERVER_STATE="RUNNING", VERSION="12.1.0.1.0", DOMAIN="rac.mydomain.net" Flags: 0x15
rhel12c1-vip A 192.168.0.98 Unique Flags: 0x81
rhel12c2-vip A 192.168.0.91 Unique Flags: 0x81

srvctl config gns -list -a
GNS is enabled.
GNS is listening for DNS server requests on port 53
GNS is using port 5,353 to connect to mDNS
GNS status: OK
Domain served by GNS: rac.mydomain.net
GNS version: 12.1.0.1.0
Globally unique identifier of the cluster where GNS is running: 4217101cdaea4fbebf2339cfa673b58b
Name of the cluster where GNS is running: rhel12c
Cluster type: server.
GNS log level: 1.
GNS listening addresses: tcp://192.168.0.87:28251.
Oracle-GNS A 192.168.0.87 Unique Flags: 0x15
rhel12c-scan A 192.168.0.88 Unique Flags: 0x81
rhel12c-scan A 192.168.0.89 Unique Flags: 0x81
rhel12c-scan A 192.168.0.96 Unique Flags: 0x81
rhel12c-scan1-vip A 192.168.0.96 Unique Flags: 0x81
rhel12c-scan2-vip A 192.168.0.89 Unique Flags: 0x81
rhel12c-scan3-vip A 192.168.0.88 Unique Flags: 0x81
rhel12c.Oracle-GNS SRV Target: Oracle-GNS Protocol: tcp Port: 28251 Weight: 0 Priority: 0 Flags: 0x15
rhel12c.Oracle-GNS TXT CLUSTER_NAME="rhel12c", CLUSTER_GUID="4217101cdaea4fbebf2339cfa673b58b", NODE_ADDRESS="192.168.0.87", SERVER_STATE="RUNNING", VERSION="12.1.0.1.0", DOMAIN="rac.mydomain.net" Flags: 0x15
rhel12c1-vip A 192.168.0.98 Unique Flags: 0x81
rhel12c2-vip A 192.168.0.91 Unique Flags: 0x81

Useful metalink notes
DNS and DHCP Setup Example for Grid Infrastructure GNS [ID 946452.1]
Posted by at
Labels: , , , , ,
Subscribe to: Posts (Atom)