Friday, May 27, 2016

mount to NFS server failed: RPC Error: Program not registered.

Mounting to an NFS location was failing with following error. This was an existing NFS mount location which worked fine earlier.
mount -t nfs -o rw,hard,rsize=32768,wsize=32768,vers=3,nointr,timeo=600,tcp,nolock,actimeo=0 nfs-server:/home/oracle/backup/ /home/oracle/dbbackup
mount: mount to NFS server 'nfs-server' failed: RPC Error: Program not registered.
Showmount to the nfs-server also fails with
showmount -e nfs-server
mount clntudp_create: RPC: Program not registered
To fix this restart the nfs related services in the following order on the nfs server. First rpcbind service
[root@nfs-server ~]# service rpcbind start
Starting rpcbind:                                          [  OK  ]
Second the nfs service
[root@nfs-server ~]# service nfs start
Starting NFS services:                                     [  OK  ]
Starting NFS quotas:                                       [  OK  ]
Starting NFS mountd:                                       [  OK  ]
Starting NFS daemon:                                       [  OK  ]
Starting RPC idmapd:                                       [  OK  ]
Finally the portmap service
[root@nfs-server ~]# service portmap start
Starting portmap:                                          [  OK  ]


Afterwards NFS mount works without issue
# mount -t nfs -o rw,hard,rsize=32768,wsize=32768,vers=3,nointr,timeo=600,tcp,nolock,actimeo=0 10.10.0.102:/home/oracle/backup/ /home/oracle/dbbackup
# df -h
Filesystem            Size  Used Avail Use% Mounted on

xx.xx.x.xxx:/home/oracle/backup/
                      526G  407G   93G  82% /home/oracle/dbbackup
Related Posts
RMAN Backups on NFS
Direct NFS Setup

Saturday, May 14, 2016

Identity Column Vs Trigger/Sequence Method for Populating Primary Key Column

Oracle 12c introduced a new featured called identity column which allows auto population of primary key columns, perfect for database models that use surrogate keys. Before the identity column was introduced same was achieved with the use of trigger and sequence (identity column also use a sequence behind the scene). This post is to compare the two methods and see if there's any performance regression or benefits of using one over the other.
Two tables were created first one for the trigger base approach.
SQL> create table seqpritable (a number primary key, b number, c varchar2(100));
SQL> create sequence IDSEQ cache 100 noorder;

Create Or Replace Trigger Seqinstrig Before Insert On Seqpritable REFERENCING NEW AS NEW OLD AS OLD for each row
Begin
select idseq.nextval into :NEW.a from dual;
end;
/
The second table with identity column
SQL>  create table seqnotrigtable (a number generated as identity cache 100 noorder primary key, b number, c varchar2(100));
Looking at the sequence it could seen the system generated sequence for the identity column
SQL> select * from user_sequences;

SEQUENCE_NAME    MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER PARTITION_COUNT S K
--------------- ---------- ---------- ------------ - - ---------- ----------- --------------- - -
IDSEQ                    1 1.0000E+28            1 N N        100           1                 N N
ISEQ$$_26555             1 1.0000E+28            1 N N        100           1                 N N
A multi-threaded java code was used to insert some rows into the tables (code is given at the end of the post). The testing was done on a 2 node RAC running Oracle 12 SE2 (12.1.0.2.160419). Two sets of tests were carried out, first 2 concurrent threads inserting and on second test 4 concurrent threads were inserting. The active session usage is used for comparison and is shown for the tests below (first two spikes refer to 2 threads inserting and second 2 spikes refer to 4 threads inserting).
The cluster (gc current block busy) and other (gcs log flush sync) are present on all the test same level and not related to use of sequence but on insert of data to the table. In terms of CPU usage there's not much difference either. Looking at this test result it's clear that no method is beneficial over the other in terms of resource usage/performance. However in terms of administration and management the identity column tops as there's no need to maintain a separate sequence or trigger. So when migrating to 12c it may be a worthwhile to move trigger based surrogate key population to identity columns and take advantage of this feature.



Java code used for inserts
public class SeqInsert extends Thread {

    private Connection con;

    public SeqInsert(Connection con) {
        this.con = con;
    }

    public static void main(String[] args) {
        try {
          
            OracleDataSource pool = new OracleDataSource();
            pool.setURL("jdbc:oracle:thin:@rac-scan.domain.net:1521/std12csrv");
            pool.setUser("asanga");
            pool.setPassword("asa");

            SeqInsert[] ts = new SeqInsert[Integer.parseInt(args[0])];
            
            for(int i = 0 ; i < ts.length; i++){
                Connection con = pool.getConnection();
                con.setAutoCommit(false);
                ts[i] = new SeqInsert(con);
            }

              for(int i = 0 ; i < ts.length; i++){
                ts[i].start();
            }

        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    @Override
    public void run() {

        try {

//            String SQL = "insert into seqpritable (b,c) values (?,?)";
            String SQL = "insert into seqnotrigtable (b,c) values (?,?)";
            PreparedStatement pr = con.prepareStatement(SQL);
            for (int i = 0; i < 10000; i++) {

                pr.setInt(1, i);
                pr.setString(2, getName()+ i);
                pr.execute();
            }
            con.commit();
            pr.close();
            con.close();

        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
}

Sunday, May 1, 2016

Changing SCAN Setting

The post lists steps for changing the SCAN setting in a cluster. This is a test system where the SCAN is currently resolved via the /etc/hosts file with a single IP.
$ srvctl config scan
SCAN name: rhel6m-scan, Network: 1/192.168.0.0/255.255.255.0/eth0
SCAN VIP name: scan1, IP: /rhel6m-scan/192.168.0.91

$ srvctl config scan_listener
SCAN Listener MYSCANLISTENER_SCAN1 exists. Port: TCP:9120
It uses a non-default scan listener name and non-default port.
This setup will be changed so that a new SCAN setting that resolves SCAN through DNS is used by the cluster. Ideally the scan name should remain the same so that application connecting doesn't have to change any connection strings after the change but in this case the new SCAN name is different to the currently used scan name.
1. Make the changes to /etc/resovle.conf and /etc/nsswitch.conf so that new SCAN look up works on all cluster nodes. Below shows nslookup working on one of the nodes
[grid@rhel6m1 admin]$ nslookup rac-scan.domain.net
Server:         192.168.0.66
Address:        192.168.0.66#53

Name:   rac-scan.domain.net
Address: 192.168.0.93
Name:   rac-scan.domain.net
Address: 192.168.0.94
Name:   rac-scan.domain.net
Address: 192.168.0.92

[grid@rhel6m1 admin]$ nslookup rac-scan.domain.net
Server:         192.168.0.66
Address:        192.168.0.66#53

Name:   rac-scan.domain.net
Address: 192.168.0.92
Name:   rac-scan.domain.net
Address: 192.168.0.93
Name:   rac-scan.domain.net
Address: 192.168.0.94

[grid@rhel6m1 admin]$ nslookup rac-scan.domain.net
Server:         192.168.0.66
Address:        192.168.0.66#53

Name:   rac-scan.domain.net
Address: 192.168.0.94
Name:   rac-scan.domain.net
Address: 192.168.0.92
Name:   rac-scan.domain.net
Address: 192.168.0.93
2. Stop the scan and scan listeners
$ srvctl stop scan_listener
$ srvctl stop scan
3. Modify the scan setting by specifying the new SCAN name.
# srvctl modify scan -n rac-scan.domain.net

# srvctl config scan
SCAN name: rac-scan.domain.net, Network: 1/192.168.0.0/255.255.255.0/eth0
SCAN VIP name: scan1, IP: /rac-scan.domain.net/192.168.0.93
SCAN VIP name: scan2, IP: /rac-scan.domain.net/192.168.0.94
SCAN VIP name: scan3, IP: /rac-scan.domain.net/192.168.0.92


4. Change the remote_listener parameter to reflect the new SCAN
SQL> alter system set remote_listener='rac-scan.domain.net:9120' scope=both sid='*';
5. Update the scan listener
$ srvctl modify scan_listener -u

$ srvctl config scan_listener
SCAN Listener MYSCANLISTENER_SCAN1 exists. Port: TCP:9120
SCAN Listener MYSCANLISTENER_SCAN2 exists. Port: TCP:9120
SCAN Listener MYSCANLISTENER_SCAN3 exists. Port: TCP:9120
6. Finally start the scan listener
$ srvctl start scan_listener
Useful metalink notes
How to Modify SCAN Setting or SCAN Listener Port after Installation [ID 972500.1]
How To Convert an 11gR2 GNS Configuration To A Standard Configuration Using DNS Only [ID 1489121.1]
How to Modify Public Network Information including VIP in Oracle Clusterware [ID 276434.1]

Related Posts
Changing Listener and SCAN Listener Port in 11gR2 RAC
Changing Listener and SCAN Listener Name in 11gR2 RAC
GNS Setup for RAC
SCAN (Single Client Access Name) Set Up Using DNS