Wednesday, June 1, 2016

Secure External Password Store and Oracle Restart with Role Separation

Oracle secure external password store allows storing of password needed for database connection in a client side Oracle wallet. It allows password-less connection to the database and useful in running scripts without having to put the password in them. A secure external password store could be setup as below.
Create a wallet (usually in TNS_ADMIN but could be different location as well)
cd $ORACLE_HOME/network/admin/

orapki wallet create -wallet . -pwd "welcome1" -auto_login_local
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

[oracle@rhel7 admin]$ ls
cwallet.sso  ewallet.p12  samples  shrept.lst  tnsnames.ora
The option "auto_login_local" prevents the wallet being moved to another host and used (1114599.1). It also ties the wallet to the operating system user preventing other users in the same host using the wallet as well (1505040.1). Create a credential, in this case std11g2 is a TNS entry that exists in tnsnames.ora file and asanga and asa are username and password
mkstore -wrl . -createCredential std11g2 asanga asa
Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:
Create credential oracle.security.client.connect_string1
Add the wallet location to sqlnet.ora file and set WALLET_OVERRIDE to true which makes user names and passwords from the wallet to be used for authentication.
more sqlnet.ora
WALLET_LOCATION =
    (SOURCE =
       (METHOD = FILE)
       (METHOD_DATA = (DIRECTORY = /opt/app/oracle/product/11.2.0/dbhome_4/network/admin))
)

SQLNET.WALLET_OVERRIDE = TRUE
The existing credentials could be listed with the following command
mkstore -wrl . -listCredential
Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:
List credential (index: connect_string username)
1: std11g2 asanga
With the external secure password store now setup, one could connected to the database without specifying the password as show below
[oracle@rhel7 orawallet]$ sqlplus  /@std11g2

SQL> show user
USER is "ASANGA"
Problem is that when secure external password store is setup for a Oracle restart (Single instance with ASM) database that has role separation (GI installed grid user and DB installed as oracle), the database fails to start during restarts and when srvctl is used. Below for a 11.2.0.4 setup
srvctl start database -d std11g2
PRCR-1079 : Failed to start resource ora.std11g2.db
CRS-5017: The resource action "ora.std11g2.db start" encountered the following error:
ORA-01078: failure in processing system parameters
. For details refer to "(:CLSN00107:)" in "/opt/app/oracle/product/11.2.0/grid_4/log/rhel6m1/agent/ohasd/oraagent_grid//oraagent_grid.log".

CRS-2674: Start of 'ora.std11g2.db' on 'rhel6m1' failed
Below for a 12.1.0.2 setup
srvctl start database -d se2db
PRCR-1079 : Failed to start resource ora.se2db.db
CRS-5017: The resource action "ora.se2db.db start" encountered the following error:
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '+DATA/se2db/spfilese2db.ora'
ORA-17503: ksfdopn:10 Failed to open file +DATA/se2db/spfilese2db.ora
ORA-12578: TNS:wallet open failed
. For details refer to "(:CLSN00107:)" in "/opt/app/oracle/diag/crs/rhel7/crs/trace/ohasd_oraagent_grid.trc".

CRS-2674: Start of 'ora.se2db.db' on 'rhel7' failed
MOS notes 1612712.1,1383938.1 says to set the permission to 750 for this issue but it didn't work.



During further investigation of the issue following observations were made.
1. The problem doesn't happen when both grid home and oracle home are installed under one user (usually as oracle user).
2. Problem doesn't happen in RAC environments even if grid home and oracle home are installed as different users (grid home as grid user and oracle home as oracle user)
3. Problem only happens in single instance environments (tested on 12c and 11.2.0.4) where grid home is owned by grid user and oracle home is owned by oracle user.
Looking at the trace file shows that srvctl fails to connect to idle instance to start the database
2016-03-30 11:11:12.474778 :CLSDYNAM:2930841344: [ora.se2db.db]{0:0:45668} [start] clsnInstConnection::makeConnectStr UsrOraEnv ,ORACLE_BASE= m_oracleHome /opt/app/oracle/product/12.1.0/dbhome_2 Crshome /opt/app/oracle/product/12.1.0/grid
2016-03-30 11:11:12.474822 :CLSDYNAM:2930841344: [ora.se2db.db]{0:0:45668} [start] makeConnectStr = (DESCRIPTION=(ADDRESS=(PROTOCOL=beq)(PROGRAM=/opt/app/oracle/product/12.1.0/dbhome_2/bin/oracle)(ARGV0=oraclese2db)(ENVS='ORACLE_HOME=/opt/app/oracle/product/12.1.0/dbhome_2,ORACLE_SID=se2db,LD_LIBRARY_PATH=,ORACLE_BASE=')(ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))')(CONNECT_DATA=(SID=se2db))))
2016-03-30 11:11:12.475460 :CLSDYNAM:2930841344: [ora.se2db.db]{0:0:45668} [start] InstAgent::stop non pool pConnxn 1 9434f9e0
2016-03-30 11:11:12.475519 :CLSDYNAM:2930841344: [ora.se2db.db]{0:0:45668} [start] InstConnection::connectInt: server not attached
2016-03-30 11:11:13.498240 :CLSDYNAM:2930841344: [ora.se2db.db]{0:0:45668} [start] InstConnection:connectInt connected
2016-03-30 11:11:13.498315 :CLSDYNAM:2930841344: [ora.se2db.db]{0:0:45668} [start] InstConnection::shutdown mode 4
2016-03-30 11:11:13.499364 :CLSDYNAM:2930841344: [ora.se2db.db]{0:0:45668} [start] ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0

2016-03-30 11:11:13.499453 :CLSDYNAM:2930841344: [ora.se2db.db]{0:0:45668} [start] InstConnection::disassociateEdition OCI error 1034
Then the start of the database was tested a wallet created without the "local" option. As mentioned earlier local option prevents wallet being used in another host and is tied to the OS user. On 11.2.0.4
orapki wallet create -wallet . -pwd "welcome1" -auto_login
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

srvctl start database -d std11g2
srvctl status database -d std11g2
Database is running.
On 12.1.0.2
orapki wallet create -wallet . -pwd "welcome1" -auto_login
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

srvctl start database -d se2db
srvctl status database -d se2db
Database is running.
So it appears the problem seem to be use of auto_login_local since there's no issue when using auto_login. Since wallet wasn't moved to a different server, the only reason that could prevent DB from starting with a wallet created with auto_login_local option is if the starting command is executed by a user other than the user tied to the wallet. Right now the wallet has been created as Oracle user and srvctl start was executed as oracle user. So it appears that grid user gets involved in starting the DB even when srvctl is run as oracle user (why the same is not happening in RAC role separated config is under SR)
So the solution in this case was to create an empty wallet as grid user and set permission to 750.
[grid@rhel7 wallet]$ orapki wallet create -wallet . -pwd "welcome1" -auto_login_local
[grid@rhel7 wallet]$ chmod 750 cwallet.sso ewallet.p12
[grid@rhel7 wallet]$ ls -lrt
-rw-rw-rw-. 1 grid   oinstall     0 Apr 15 17:35 ewallet.p12.lck
-rwxr-x---. 1 grid   oinstall    75 Apr 15 17:35 ewallet.p12
-rw-rw-rw-. 1 grid   oinstall     0 Apr 15 17:35 cwallet.sso.lck
-rwxr-x---. 1 grid   oinstall   120 Apr 15 17:35 cwallet.sso
No credentials are added to this wallet as such nothing to list
[grid@rhel7 wallet]$  mkstore -wrl . -listCredential
Oracle Secret Store Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:
List credential (index: connect_string username)
Used a non-default location to this wallet file and this was added to $ORACLE_HOME/network/admin/sqlnet.ora file(not to $GI_HOME/network/admin/sqlnet.ora)
pwd
/opt/app/oracle/product/12.1.0/dbhome_2/network/admin

more sqlnet.ora
WALLET_LOCATION =
    (SOURCE =
       (METHOD = FILE)
       (METHOD_DATA = (DIRECTORY = /usr/local/wallet))
)

SQLNET.WALLET_OVERRIDE = TRUE
With this setup in place srvctl works fine and database get started automatically after restarts. However password-less access fails as the wallet is owned by grid user
[oracle@rhel7 orawallet]$ sqlplus  /@se2db

ERROR:
ORA-12578: TNS:wallet open failed
To fix this, create another wallet at a different location as Oracle user.
ls /usr/local/wallet/orawallet

-rwxrwx---. 1 oracle oinstall 528 Apr 15 17:20 ewallet.p12
-rwxrwx---. 1 oracle oinstall 573 Apr 15 17:20 cwallet.sso
Create another sqlnet.ora file in the same directory (orawallet). Add this directory path as the wallet location
WALLET_LOCATION =
    (SOURCE =
       (METHOD = FILE)
       (METHOD_DATA = (DIRECTORY = /usr/local/wallet/orawallet))
)

SQLNET.WALLET_OVERRIDE = TRUE
Whenever password-less access using external password store is needed export this location as the TNS_ADMIN and connect.
export TNS_ADMIN=/usr/local/wallet/orawallet
sqlplus  /@se2db

SQL> show user
USER is "ASANGA"
If any database scripts require password-less access before running export the TNS_ADMIN to location where second sqlnet.ora file was set.
Why the auto_login_local fails this way in Oracle restart with role separation is still under SR.

Useful metalink notes
Using a Secure External Password Store with the JDBC Thin Driver [ID 1441745.1]
Using The Secure External Password Store [ID 340559.1]
How To Avoid Expdp And Impdp Passwords Being Visible By "ps" Unix Command? [ID 869825.1]
How To Prevent The Secure Password Store Wallet From Being Moved to Another Host [ID 1114599.1]
Oracle Cluster failed to start with ASM instance getting ORA-00443 [ID 2000868.1]
Database Failed To Start [ID 1922401.1]
Oracle Restart: srvctl fails to start database with error CRS-5010 if RDBMS and Grid under different users [ID 1335607.1]
How to configure SEPS for the pluggable databases [ID 1980698.1]
Bug 11706168 - ORA-00000 during STARTUP with SQLNET.WALLET_OVERRIDE=TRUE [ID 11706168.8]
How To Configure The Secure External Password Store To Allow The Connection To RMAN Catalog? [ID 1383938.1]
ORA-15055 and ORA-12578 on database startup with external wallet store [ID 1612712.1]
How To Check Whether The Wallet Is A Local Auto Login Wallet [ID 1505040.1]

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();
        }
    }
}