WildCards To Jeopardize Execuation Plan

Symptom:

 The incorrect usage of wildcard '%'  will Jeopardize Execuation Plan

create index username_idx on mytest(lower(USERNAME));
select *  from mytest where lower(USERNAME) like 'scot%';


Execution Plan
----------------------------------------------------------
Plan hash value: 3746642705

--------------------------------------------------------------------------------------------
| Id  | Operation            | Name       | Rows  | Bytes | Cost (%CPU)| Time       |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |           |    10 |  1280 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MYTEST       |    10 |  1280 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN        | USERNAME_IDX |     2 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(LOWER("USERNAME") LIKE 'scot%')
       filter(LOWER("USERNAME") LIKE 'scot%')

If you put % in front of a string, it tells oracle look for the string 'scot' anywhere in the column.
It results full tablescan as normal B-tree index can't handle such search. If it is key word, we may consider to use domain index

select *  from mytest where lower(USERNAME) like '%scot%'; --full scan

Execution Plan
----------------------------------------------------------
Plan hash value: 1692938441

----------------------------------------------------------------------------
| Id  | Operation      | Name   | Rows  | Bytes | Cost (%CPU)| Time       |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |    10 |  1280 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| MYTEST |    10 |  1280 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(LOWER("USERNAME") LIKE '%scot%')

Invalid Oracle Cluster Registry (OCR) location.

Symptom:

 We clone grid home (CRS)  from other node .Details see oracle support Doc ID 1493677.1
  Try to reconfigure GI home with new nodes. We always get below error

 ./config.sh -debug -logLEvel finest 2>&1 | tee `hostname -s`.Grid_config.log

[INS-41321] Invalid Oracle Cluster Registry (OCR) location

Diagnose:

   Compare ZFS mount options with good ones. No luck.
   Compare ZFS shares on filer side with good ones.No luck.
   Modify OS privilege, chmod.....etc .No luck.

Solution:

   Finally we found the OS kernel version of the source GI home is different from the target
2.6.39-400.298.2.el6uek.x86_64 vs 3.8.13-118.20.2.el6uek.x86_64

  The clone files must be from same kernel level source home

Ansible Temporary Files Issues with Pbrun

Symptom

   When we do a simple pwd command via pbrun from ansible , it error out

ansible xxxx.com -i ./hosts_t -u guid --become-user=oracle -k -K -b -m command -a 'pwd'

FAILED | rc=-1 >>
Failed to set permissions on the temporary files Ansible needs to create when becoming an unprivileged user (rc: 1, err: chown: changing ownership of `/tmp/ansible-tmp-1521520911.62-5031142116596/': Operation not permitted
chown: changing ownership of `/tmp/ansible-tmp-1521520911.62-5031142116596/command.py': Operation not permitted
}). For information on working around this, see https://docs.ansible.com/ansible/become.html#becoming-an-unprivileged-user

 Diagnosis:

 Find the note in github to set parameter in ansible.cfg

 allow_world_readable_tmpfiles = true

 However after that, we received another error: 

 (127, 'pbrun9.3.0-07[340105]: 3201.08 Exec of /bin/bash failed: Resource temporarily unavailable\r\n', 'Shared connection to ucmdx4007.us.oracle.com closed.\r\n')

 The error is related to OS settings . It seems pbrun has issues to run a bash and we can reproduce it via pbrun content-ansible -u pwd

  After digging around,  we found by default OS has limit for number of user's processes. The pbrun needs more than 1024 to exec bash commands.
cat /etc/security/limits.d/90-nproc.conf
# Default limit for number of user's processes to prevent
# accidental fork bombs.
# See rhbz #432903 for reasoning.

*          soft    nproc     1024
root       soft    nproc     unlimited

Solution:

comment  "*          soft    nproc     1024" in the /etc/security/limits.d/90-nproc.conf

set parameter in ansible.cfg -- allow_world_readable_tmpfiles = true


 

Ansible How to Hide Expected Stderr Output

Symptom:

   There are some cases the SHELL scripts we run via ansible, the return code (rc) is not 0
   And it is expected to return non-zero.

   Ansible always output stderr for you which can be annoyed.  ie

TASK [run the script  to get the result] ************************************************************************************************
fatal: [acmsdbv3053.us.oracle.com]: FAILED! => {"changed": true, "cmd": ". /tmp/spectre-meltdown-checker-ansible.sh --batch text", "delta": "0:00:01.688319", "end": "2018-03-19 00:36:12.670522", "failed": true, "msg": "non-zero return code", "rc": 2, "start": "2018-03-19 00:36:10.982203", "stderr": 
 We try to hide them

Solution:

   Use  ignore_errors to continue the ansible yml file without disruption by the failed error

   Use  failed_when to be set as false. It will force the task never to be failed
   Example codes like

- name: run the script  to get the result
     shell: |
           . /tmp/spectre-meltdown-checker-ansible.sh --batch text
     register: output
     failed_when: false
     ignore_errors: True



Before set  failed_when
"changed": true,
    "cmd": ". /tmp/spectre-meltdown-checker-ansible.sh --batch text",
    "delta": "0:00:01.724031",
    "end": "2018-03-19 00:55:37.677224",
    "failed": true


After set  failed_when
"changed": true,
    "cmd": ". /tmp/spectre-meltdown-checker-ansible.sh --batch text",
    "delta": "0:00:01.966887",
    "end": "2018-03-19 00:53:10.673414",
    "failed": false,
    "failed_when_result": false, 

Ansible replace \t or '\t' in Sqlplus Output

Symptom:

     When we run sql via sqlplus to get some output from database via ansible playbook, we get some special characters. ie \t ,example below. The format is not quite friendly.

TASK [display details of sql output] *****************************************************************************************************************************************
ok: [acmsdbv3053.us.oracle.com] => {
    "msg": [
        "",
        "NAME\t\t\t\t\t INST_ID VALUE",
        "---------------------------------------- ------- --------------------------------------------------",
        "compatible\t\t\t\t       1 11.2.0.2.0",
        "compatible\t\t\t\t       2 11.2.0.2.0",
        "noncdb_compatible\t\t\t       1 FALSE",
        "noncdb_compatible\t\t\t       2 FALSE"
    ]
}

Solution:

We use replace function to replace \t (the string)  with ' ' or we need to replace real tab with ' '

To replace replace \t (the string)  with ' ' in the output
 - name: display details of sql output
     debug: msg="{{ sqloutput.stdout_lines|replace('\\t',' ') }}"

To replace real tab with ' ' in the output
- name: display details of sql output
     debug: msg="{{ sqloutput.stdout_lines|replace('\t',' ') }}"