Ansible Online Notes

Online notes on technical issues I encountered using Ansible and the resolution. Posting online in case I need to refer to it again

1.)    The remote machine needs to have python simplejson or json module

Resolution: Run command to remote install module
ansible hostname -i inventory/hosts -m raw -a "sudo yum install -y python-simplejson"  -k  -u root -vvvv

2.) authorized_keys does not work on target ssh server
Symptom: When ssh from Ansible server to target server, it ask for a password even when .ssh/authorized_keys are set

http://unix.stackexchange.com/questi...authentication 

Make sure the permissions on the ~/.ssh directory and its contents are proper. When I first set up my ssh key auth, I didn't have the ~/.ssh folder properly set up, and it yelled at me. Your home directory ~, your ~/.ssh directory and the ~/.ssh/authorized_keys file on the remote machine must be writable only by you: rwx------ and rwxr-xr-x are fine, but rwxrwx--- is no good¹, even if you are the only user in your group (if you prefer numeric modes: 700 or 755, not 775). If ~/.ssh or authorized_keys is a symbolic link, the canonical path (with symbolic links expanded) is checked. Your ~/.ssh/authorized_keys file (on the remote machine) must be readable (at least 400), but you'll need it to be also writable (600) if you will add any more keys to it. Your private key file (on the local machine) must be readable and writable only by you: rw-------, i.e. 600. Also, if SELinux is set to enforcing, you may need to run restorecon -R -v ~/.ssh (see e.g. Ubuntu bug 965663 and Debian bug report #658675; this is patched in CentOS 6).

 If that does not work, on the target server

sudo su -
service sshd stop   (Note: this will not kill your current session)
/use/sbin/sshd -d   (Note: debug mode)

service sshd start  (Note: do this when finished debugging or else no one can ssh into the VM. Try ssh from a new terminal before exiting the main root terminal)

In debug mode, you will see what sshd is doing when it is trying to read the authorized_keys file

In my case, sshd was reading the wrong file. To fix I had to

Edit /etc/ssh/sshd_config, and uncomment

RSAAuthentication yes

PubkeyAuthentication yes

AuthorizedKeysFile      .ssh/authorized_keys

Then restart sshd

service sshd stop
service sshd start

3.)    Issue: The remote user needs to be able to “sudo su –“ without password. Needed to  configure iptables firewall and other super-admin commands

Resolution: Have user add entry in /etc/sudoers file

Backlog Enhancement: Have precondition check for root access. Or find a way to make root access unnecessary

4.)    Ansible 2.2.0 had a bug
https://github.com/ansible/ansible/issues/16128

Resolution: Updated Ansible from Git with the latest version

git pull --rebase

git submodule update --init –recursive

5.) Ansible has issues transfering files to target server.

Ansible uses sftp to transfer files behind the scenes. Try

sftp user@target-server

to see if you can sftp without a password.

If you cannot, sftp sometimes has issues with echo in .bashrc . Comment out the echo in .bashrc and try again.

If that does not work, force Ansible to use scp instead of sftp.

In /etc/ansible/ansible.cfg, add the line

scp_if_ssh = True

or if you cannot edit the ansible.cfg file, then from shell type

export ANSIBLE_SCP_IF_SSH=y

Oracle foreign key analysis

Find Oracle foreign key by name
For Oracle error

SQL Error: ORA-02291: integrity constraint (SCHEMA_OWNER.FOREIGN_KEY_NAME) violated - parent key not found

Find the source and reference Oracle table of the foreign key name using the SQL query below

select uc.CONSTRAINT_NAME, 
  src_ucc.OWNER source_schema ,src_ucc.TABLE_NAME source_table, 
  src_ucc.COLUMN_NAME source_column, src_ucc.POSITION, 
  dest_ucc.TABLE_NAME reference_table, dest_ucc.COLUMN_NAME reference_column,
  dest_ucc.POSITION reference_position,
  uc.DELETE_RULE, uc.STATUS, uc.DEFERRABLE, uc.DEFERRED, uc.VALIDATED  
from sys.USER_CONSTRAINTS uc 
  join sys.USER_CONS_COLUMNS src_ucc 
  on uc.OWNER=src_ucc.OWNER and uc.CONSTRAINT_NAME=src_ucc.CONSTRAINT_NAME
  join sys.USER_CONS_COLUMNS dest_ucc 
  on uc.OWNER=dest_ucc.OWNER and uc.R_CONSTRAINT_NAME=dest_ucc.CONSTRAINT_NAME  
where uc.OWNER='SCHEMA_OWNER' and uc.CONSTRAINT_NAME='FOREIGN_KEY_NAME'
  and uc.CONSTRAINT_TYPE='R' 
  and src_ucc.POSITION=dest_ucc.POSITION
order by src_ucc.TABLE_NAME ,src_ucc.POSITION, dest_ucc.POSITION;

Find all Oracle foreign keys on a table
To find all the foreign keys that a Oracle table has, use the SQL query below

select uc.CONSTRAINT_NAME, 
  src_ucc.OWNER source_schema ,src_ucc.TABLE_NAME source_table, 
  src_ucc.COLUMN_NAME source_column, src_ucc.POSITION, 
  dest_ucc.TABLE_NAME reference_table, dest_ucc.COLUMN_NAME reference_column,
  dest_ucc.POSITION reference_position,
  uc.DELETE_RULE, uc.STATUS, uc.DEFERRABLE, uc.DEFERRED, uc.VALIDATED  
from sys.USER_CONSTRAINTS uc 
  join sys.USER_CONS_COLUMNS src_ucc 
  on uc.OWNER=src_ucc.OWNER and uc.CONSTRAINT_NAME=src_ucc.CONSTRAINT_NAME
  join sys.USER_CONS_COLUMNS dest_ucc 
  on uc.OWNER=dest_ucc.OWNER and uc.R_CONSTRAINT_NAME=dest_ucc.CONSTRAINT_NAME  
where src_ucc.OWNER='SCHEMA_OWNER' and src_ucc.TABLE_NAME='SOURCE_TABLE_NAME'
  and uc.CONSTRAINT_TYPE='R' 
  and src_ucc.POSITION=dest_ucc.POSITION
order by src_ucc.TABLE_NAME ,src_ucc.POSITION, dest_ucc.POSITION;

Find all Oracle foreign keys referenced to a table
To find all the Oracle tables that references a table via foreign key, use the SQL query below

select uc.CONSTRAINT_NAME, 
  src_ucc.OWNER source_schema ,src_ucc.TABLE_NAME source_table, 
  src_ucc.COLUMN_NAME source_column, src_ucc.POSITION, 
  dest_ucc.TABLE_NAME reference_table, dest_ucc.COLUMN_NAME reference_column,
  dest_ucc.POSITION reference_position,
  uc.DELETE_RULE, uc.STATUS, uc.DEFERRABLE, uc.DEFERRED, uc.VALIDATED  
from sys.USER_CONSTRAINTS uc 
  join sys.USER_CONS_COLUMNS src_ucc 
  on uc.OWNER=src_ucc.OWNER and uc.CONSTRAINT_NAME=src_ucc.CONSTRAINT_NAME
  join sys.USER_CONS_COLUMNS dest_ucc 
  on uc.OWNER=dest_ucc.OWNER and uc.R_CONSTRAINT_NAME=dest_ucc.CONSTRAINT_NAME  
where dest_ucc.OWNER='SCHEMA_OWNER' and dest_ucc.TABLE_NAME='REFERENCE_TABLE_NAME'
  and uc.CONSTRAINT_TYPE='R' 
  and src_ucc.POSITION=dest_ucc.POSITION
order by src_ucc.TABLE_NAME ,src_ucc.POSITION, dest_ucc.POSITION;

Column meaning

CONSTRAINT_NAME	The foreign key name
SOURCE_SCHEMA	The schema of the foreign key source table
SOURCE_TABLE	The table that has the foreign key
SOURCE_COLUMN	The column in the source table enforced by the foreign key
POSITION	For foreign keys with multiple columns, the order of the columns in the foreign key
REFERENCE_TABLE	The table referenced by the foreign key
REFERENCE_COLUMN	The column of the table referenced by the foreign key
REFERENCE_POSITION	For foreign keys with multiple columns, the order of the referenced columns in the foreign key
DELETE_RULE	The action Oracle performs with the row associated with the foreign key in the reference table is deleted NO ACTION: Oracle respond with a referential integrity error and will not allow the row to be removed CASCADE: Oracle automatically deletes the row in the tables with the associated foreign key SET NULL: Oracle automatically sets the foreign key column value to NULL
STATUS	Is the foreign key enforced ENABLED: The foreign key is enforced and active DISABLED: The foreign key is not enforced and inactive
DEFERRABLE	If a transaction can use SET CONSTRAINT[S] to dynamically defer a foreign key referential integrity check DEFERRABLE: Yes you can use SET CONSTRAINTS NOT DEFERRABLE: No you cannot use SET CONSTRAINTS
DEFERRED	If a foreign key is DEFERRABLE, when in the transaction will Oracle check for foreign key referential integrity violations by default. NOT DEFERRABLE is always IMMEDIATE. DEFERRED: Oracle will check when after a commit has been issued IMMEDIATELY Oracle will immediately check when the SQL is executed.
VALIDATED	When adding foreign keys to a Oracle table with existing data, checks to see if the existing data violates foreign key referential integrity. VALIDATED: Oracle will check to see if the existing data violates integrity. If it does then the foreign key will not be created NOT VALIDATED: Oracle will not check to see if the existing data violates integrity. The foreign key will be created even if there is existing bad data

See the Oracle language reference on constraints for more details.