Below are the questions asked to a candidate with 10 years of experience in Oracle DBA and postgres . There were 2 rounds.

Round 1 :

•  What are your day to day activities. And what is your role in the Team?
• Explain cluster startup sequence.
• Suppose users are connected to node1 and Os team, want to do emergency maintenance and they need to reboot. What wil happens to the transactions on node1 , and can we move them to node 2.
• What are the advantages of partitioning?
• Can we convert non-partitioned table to partitioned table online( in production)?
• What are the cloning methods. And what is the difference between duplicate method and restore method?
• How can we open the standby database as read write for testing and Once testing is done need to revert to previous position. But without using snapshot command?
• Explain how you did grid upgrade.
• Which advanced security features you have used ?
• What is audit purging?
• Lets says a query was running fine for last few days , but today it is not performing well. How you will troubleshoot it?
• What is oracle in memory ?
• Explain what type of patches you applied on grid.
• As per resume ,You have worked on ansible . So how good you are with ansible and what type of scripts you have wrote.
• How can you troubleshoot a shell script?

2nd round:

• Explain ASM architecture , means when ever user runs a query, how it gets the data from ASM.
• Why ASM performance is better
• Why VIP is used , Bcoz before 10g vip was not used. What benefits vip provide
• What is Current block and CR block in RAC
• What wait events are there in RAC??
• What is gc buffer busy wait
• What is gc 2 way current wait, gc 3 way current wait.
• What is node eviction. In which cases it happens.
• How you do load balancing using services in your project. Explain
• What activities you did using goldengate
• How you did upgrade/migration using goldengate.
• What is goldengate instantiation.
• What is bounded recovery in goldengate
• What is the difference between CSN in goldengate vs SCN in database?
• Which type of os tool/commands you have used for os monitoring/performance. And how you read it.
• How you troubleshoot I/O issues in oracle
• What type of sql performane tools you use.
• How you migrate a plan from one db to another db
• What is the dbms package for baseline and sql tuning?
• Lets say, despite importing the baseline, query is not picking the plan, What can the be reason?
• What is index fast full scan and index range scan?
• What is the purpose of standby redolog.
• Any automation tools you have used?
• Did you worked on jenkins or gitlab?
• What activities you did in postgres.
• How you implement backup in postgres.
• Is your postgres cluster active active or active passive?
• Can we create a active active postgres cluster?
• Do you remember any postgres extensions?
• What type of scripts you have written on ansible?
• Tell me some modules of ansible.
• How can we encrypt the server credentials in ansible.

If you wish to share your interview experiences, then please drop a mail to support@dbaclass.com. We will happy to publish it with your permission.

The post Real Interview questions for experienced DBAs – 1 appeared first on DBACLASS.

Ansible control node  is any machine where ansible tool is installed. 

 

IMPORTANT POINTS:

1. oracle 19c client will be installed on all nodes.
2. Make sure  the servers where  client need to be installed has connectivity from ansible control server

Below are the steps :

1. Copy the oracle 19c client software ( zip file ) to ansible control server.

[ansible_node] ls -ltr /oracle/LINUX.X64_193000_client.zip

2. Prepare the inventory file:

The list of servers where oracle 19c client will be installed.

[ansiblel_node] $ cat /home/ansible/ansible-scipts/host_inv
[appserver]
linux_host22 ansible_host=10.20.86.60 ansible_connection=ssh ansible_user=root ansible_ssh_pass=dbaclass@123
linux_host29 ansible_host=10.20.86.61 ansible_connection=ssh ansible_user=root ansible_ssh_pass=dbaclass@123
linux_host34 ansible_host=10.20.86.62 ansible_connection=ssh ansible_user=root ansible_ssh_pass=dbaclass@123

3. Prepare the client installation response file:

[ansible_node] $ cat /home/ansible/oracle_client_19c.rsp

#-------------------------------------------------------------------------------
# Do not change the following system generated value.
#-------------------------------------------------------------------------------
oracle.install.responseFileVersion=/oracle/install/rspfmt_clientinstall_response_schema_v19.0.0

#-------------------------------------------------------------------------------
# Unix group to be set for the inventory directory.
#-------------------------------------------------------------------------------
UNIX_GROUP_NAME=oinstall
#-------------------------------------------------------------------------------
# Inventory location.
#-------------------------------------------------------------------------------
INVENTORY_LOCATION=/oracle/app/oraInventory
#-------------------------------------------------------------------------------
# Complete path of the Oracle Home
#-------------------------------------------------------------------------------
ORACLE_HOME=/oracle/app/oracle/product/19c/client_1
#-------------------------------------------------------------------------------
# Complete path of the Oracle Base.
#-------------------------------------------------------------------------------
ORACLE_BASE=/oracle/app/oracle
oracle.install.client.installType=Administrator

#-------------------------------------------------------------------------------

4. Prepare the ansible playbook:

[ansible_node] $ cat oracle_client_installation.yml
- hosts: appserver
  strategy: free
  user: bvunix
  become: yes
  become_method: su
  become_user: oracle
  tasks:
    - name: check existance of mount point
      command: mountpoint -q /oracle
      register: volume_stat
      failed_when: False
    - name: Copy client software
      unarchive: src=/oracle/LINUX.X64_193000_client.zip dest=/oracle/
    - name: Copy response file
      copy: src=/home/ansible/oracle_client_19c.rsp dest=/oracle mode=0777
    - name: Install Oracle Client
      command: "/oracle/client/runInstaller -silent -showProgress -ignorePrereq -ignoreSysPrereqs -waitforcompletion -responseFile /oracle/oracle_client_19c.rsp"
      register: client_runinstaller_output
      failed_when: "'Successfully Setup Software' not in client_runinstaller_output.stdout"

5. Execute the playbook:

[ansible-node]$  ansible-playbook oracle_client_installation.yml -i /home/ansible/ansible-scipts/host_inv

PLAY [linux_host22,linux_host29,linux_host34] ***********************************************************************************************************************************
Tuesday 06 July 2021  20:30:15 +0300 (0:00:00.048)       0:00:00.048 **********
Tuesday 06 July 2021  20:30:15 +0300 (0:00:00.011)       0:00:00.060 **********
Tuesday 06 July 2021  20:30:15 +0300 (0:00:00.010)       0:00:00.071 **********

TASK [Gathering Facts] **********************************************************************************************************************************************************
[WARNING]: Module remote_tmp /home/oracle/.ansible/tmp did not exist and was created with a mode of 0700, this may cause issues when running as another user. To avoid this,
create the remote_tmp dir with the correct permissions manually
ok: [linux_host34]
ok: [linux_host22]
Tuesday 06 July 2021  20:30:18 +0300 (0:00:02.977)       0:00:03.048 **********
Tuesday 06 July 2021  20:30:18 +0300 (0:00:00.043)       0:00:03.091 **********

TASK [check existance of mount point] *******************************************************************************************************************************************
changed: [linux_host34]
Tuesday 06 July 2021  20:30:19 +0300 (0:00:01.304)       0:00:04.397 **********
changed: [linux_host22]
Tuesday 06 July 2021  20:30:19 +0300 (0:00:00.088)       0:00:04.486 **********

TASK [Gathering Facts] **********************************************************************************************************************************************************
ok: [linux_host29]
Tuesday 06 July 2021  20:30:20 +0300 (0:00:00.884)       0:00:05.370 **********

TASK [check existance of mount point] *******************************************************************************************************************************************
changed: [linux_host29]
Tuesday 06 July 2021  20:30:21 +0300 (0:00:01.329)       0:00:06.699 **********

TASK [Copy client software] *****************************************************************************************************************************************************
changed: [linux_host34]
Tuesday 06 July 2021  20:31:36 +0300 (0:01:14.560)       0:01:21.259 **********
changed: [linux_host22]
Tuesday 06 July 2021  20:31:38 +0300 (0:00:02.122)       0:01:23.382 **********

TASK [Copy response file] *******************************************************************************************************************************************************
changed: [linux_host34]
Tuesday 06 July 2021  20:31:40 +0300 (0:00:01.840)       0:01:25.222 **********
changed: [linux_host22]
Tuesday 06 July 2021  20:31:41 +0300 (0:00:01.072)       0:01:26.295 **********

TASK [Copy client software] *****************************************************************************************************************************************************
changed: [linux_host29]
Tuesday 06 July 2021  20:31:45 +0300 (0:00:04.660)       0:01:30.956 **********

TASK [Copy response file] *******************************************************************************************************************************************************
changed: [linux_host29]
Tuesday 06 July 2021  20:31:48 +0300 (0:00:02.787)       0:01:33.744 **********

TASK [Install Oracle Client] ****************************************************************************************************************************************************
changed: [linux_host22]
changed: [linux_host34]
changed: [linux_host29]

PLAY RECAP **********************************************************************************************************************************************************************
linux_host22               : ok=5    changed=4    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0
linux_host29               : ok=5    changed=4    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0
linux_host34               : ok=5    changed=4    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0

Tuesday 06 July 2021  20:33:33 +0300 (0:01:45.191)       0:03:18.935 **********
===============================================================================
Install Oracle Client -------------------------------------------------------------------------------------------------------------------------------------------------- 105.19s
Copy client software ---------------------------------------------------------------------------------------------------------------------------------------------------- 74.56s
Gathering Facts ---------------------------------------------------------------------------------------------------------------------------------------------------------- 2.98s
Copy response file ------------------------------------------------------------------------------------------------------------------------------------------------------- 2.79s
check existance of mount point ------------------------------------------------------------------------------------------------------------------------------------------- 1.33s

We have successfully installed oracle client on all the mentioned servers.

The post Automate oracle client installation using ansible appeared first on DBACLASS.

Install ansible-cmdb tool:

Download link – > https://github.com/fboender/ansible-cmdb/releases

[root@DBHOST ansible-cmdb_rpm]# tar -vxzf ansible-cmdb-1.28.tar.gz
ansible-cmdb-1.28/
ansible-cmdb-1.28/.gitignore
ansible-cmdb-1.28/LICENSE
ansible-cmdb-1.28/Makefile
ansible-cmdb-1.28/README.md
ansible-cmdb-1.28/contrib/
ansible-cmdb-1.28/contrib/ansible-cmdb.man.1
ansible-cmdb-1.28/contrib/debian/
ansible-cmdb-1.28/contrib/debian/DEBIAN/
ansible-cmdb-1.28/contrib/debian/DEBIAN/control
ansible-cmdb-1.28/contrib/debian/changelog
ansible-cmdb-1.28/contrib/debian/copyright
ansible-cmdb-1.28/contrib/release_Makefile
ansible-cmdb-1.28/contrib/screenshot-detail.png
ansible-cmdb-1.28/contrib/screenshot-overview.png
ansible-cmdb-1.28/docs/
ansible-cmdb-1.28/docs/dev.md
ansible-cmdb-1.28/docs/faq.md
ansible-cmdb-1.28/docs/index.md
ansible-cmdb-1.28/docs/installation.md
ansible-cmdb-1.28/docs/usage.md
ansible-cmdb-1.28/example/
ansible-cmdb-1.28/example/ansible.cfg
ansible-cmdb-1.28/example/dyninv_test.py
ansible-cmdb-1.28/example/dyninv_vbox.py
.
.
.
.
ansible-cmdb-1.28/test/f_inventory/mixeddir/dyninv.py
ansible-cmdb-1.28/test/f_inventory/mixeddir/hosts
ansible-cmdb-1.28/test/f_inventory/out/
ansible-cmdb-1.28/test/f_inventory/out/db.dev.local
ansible-cmdb-1.28/test/test.py
ansible-cmdb-1.28/test/test.sh


[root@DBHOST ansible-cmdb_rpm]# cd ansible-cmdb-1.28/


[root@DBHOST ansible-cmdb-1.28]# make install
umask 0022 && mkdir -p /usr/local/lib/ansible-cmdb
umask 0022 && mkdir -p /usr/local/man/man1
umask 0022 && cp -a src/* /usr/local/lib/ansible-cmdb
umask 0022 && cp -r lib/* /usr/local/lib/ansible-cmdb
umask 0022 && cp LICENSE /usr/local/lib/ansible-cmdb
umask 0022 && cp README.md /usr/local/lib/ansible-cmdb
umask 0022 && gzip -9 -c contrib/ansible-cmdb.man.1 > /usr/local/man/man1/ansible-cmdb.man.1.gz
umask 0022 && ln -s /usr/local/lib/ansible-cmdb/ansible-cmdb /usr/local/bin/ansible-cmdb

[root@DBHOST ansible-cmdb-1.28]# which ansible-cmdb
/usr/local/bin/ansible-cmdb

Create inventory file

cat /home/ansible/ansible-scipts/dbaclass_inventory

linux_host1 ansible_host=182.34.86.60 ansible_connection=ssh ansible_user=root ansible_ssh_pass=dbaclass@123
linux_host2 ansible_host=182.34.86.61 ansible_connection=ssh ansible_user=root ansible_ssh_pass=dbaclass@123
linux_host3 ansible_host=182.34.86.62 ansible_connection=ssh ansible_user=root ansible_ssh_pass=dbaclass@123
linux_host4 ansible_host=182.34.86.63 ansible_connection=ssh ansible_user=root ansible_ssh_pass=dbaclass@123
linux_host5 ansible_host=182.34.86.64 ansible_connection=ssh ansible_user=root ansible_ssh_pass=dbaclass@123
linux_host6 ansible_host=182.34.86.65 ansible_connection=ssh ansible_user=root ansible_ssh_pass=dbaclass@123
linux_host7 ansible_host=182.34.86.40 ansible_connection=ssh ansible_user=root ansible_ssh_pass=dbaclass@123
linux_host8 ansible_host=182.34.86.41 ansible_connection=ssh ansible_user=root ansible_ssh_pass=dbaclass@123
linux_host9 ansible_host=182.34.86.42 ansible_connection=ssh ansible_user=root ansible_ssh_pass=dbaclass@123
linux_host10 ansible_host=182.34.86.43 ansible_connection=ssh ansible_user=root ansible_ssh_pass=dbaclass@123
linux_host11 ansible_host=182.34.86.44 ansible_connection=ssh ansible_user=root ansible_ssh_pass=dbaclass@123
linux_host12 ansible_host=182.34.86.45 ansible_connection=ssh ansible_user=root ansible_ssh_pass=dbaclass@123
linux_host13 ansible_host=182.34.86.46 ansible_connection=ssh ansible_user=root ansible_ssh_pass=dbaclass@123
linux_host14 ansible_host=182.34.86.47 ansible_connection=ssh ansible_user=root ansible_ssh_pass=dbaclass@123
linux_host15 ansible_host=182.34.86.48 ansible_connection=ssh ansible_user=root ansible_ssh_pass=dbaclass@123
linux_host16 ansible_host=182.35.36.114 ansible_connection=ssh ansible_user=root ansible_ssh_pass=dbaclass@123

Generate report:

Before generating report, first step is to collect the facts gathering details of all hosts.

mkdir out 

ansible -i /home/ansible/ansible-scipts/dbaclass_inventory  -m setup --tree out/ all

this command will create unique file for each host in out directory .

ansible-cmdb provides different templates to generate report. Most popular and default one is the html template.

1. Generate html report:

root# ansible-cmdb -i /home/ansible/ansible-scipts/dbaclass_inventory -t html_fancy out/ > dbaclass_inventory.html

Now view the dbaclass_inventory.html report in your browser. You can select different columns and view the updated reports.

       3. Generate csv report:

You can generate the inventory overview report in csv format also.

 [ansible@DBHOST ansible-scipts]$ ansible-cmdb  -i /home/ansible/ansible-scipts/dbaclass_inventory   -t csv out > hostinv.csv

       3. Generate text output on command prompt:

[ansible@DBHOST ansible-scipts]$  ansible-cmdb  -i /home/ansible-scipts/dbaclass_inventory  -t txt_table --columns ip,os,mem,cpus,memfree,memused out/

OS            IP              Mem   MemFree  MemUsed  CPUs
------------  --------------  ----  -------  -------  ----
RedHat 6.10   182.34.86.41    32g   29g      3g       1
RedHat 6.10   182.34.86.42    64g   61g      3g       2
RedHat 7.9    182.34.86.65    64g   60g      4g       2
RedHat 7.7    182.34.86.40    161g  45g      116g     4
RedHat 7.9    182.34.86.63    64g   18g      46g      2
RedHat 7.9    182.34.86.64    64g   59g      6g       2
RedHat 6.10   182.34.86.61    32g   29g      3g       1
RedHat 7.9    182.34.86.62    64g   30g      34g      2
RedHat 6.10   182.34.86.60    161g  27g      135g     4
RedHat 6.10   182.35.62.68    32g   26g      6g       2
RedHat 6.10   182.35.62.67    32g   23g      9g       2

      4. Generate a sql file:

You can generate a sql file, which can be run on any sql database to create a host table and load the inventory data.

[ansible@DBHOST ansible-scipts]$  ansible-cmdb  -i /home/ansible-scipts/dbaclass_inventory  -t sql out > hostinv.sql

cat hostinv.sql

DROP TABLE IF EXISTS hosts;
CREATE TABLE hosts (
    name VARCHAR(255),
    fqdn VARCHAR(255),
    main_ip VARCHAR(15),
    os_name VARCHAR(80),
    os_version VARCHAR(40),
    system VARCHAR(40),
    kernel VARCHAR(40),
    arch_hardware VARCHAR(12),
    arch_userspace VARCHAR(12),
    virt_type VARCHAR(20),
    virt_role VARCHAR(20),
    cpu_type VARCHAR(60),
    vcpus INT,
    ram FLOAT,
    disk_total FLOAT,
    disk_free FLOAT
);

    INSERT INTO hosts (
        name,
        fqdn,
        main_ip,
        os_name,
        os_version,
        system,
        kernel,
        arch_hardware,
        arch_userspace,
        virt_type,
        virt_role,
        cpu_type,
        vcpus,
        ram,
        disk_total,
        disk_free
    ) VALUES (
        "linux_host8",
.
.
.
.
.

NOTE – > For more information regarding ansible-cmd visit https://ansible-cmdb.readthedocs.io/en/latest/usage/

The post How to create host configuration report using ansible-cmdb appeared first on DBACLASS.

Write an ansible script ,that will
1. Copy the script collect_info.sh to all the hosts.
1. Run the shell script ( collect_info.sh ) , against the hosts mentioned in host file.
2. Script will be run as root user.
3. The output file of the script ( will be like collect*txt) need to be copied to local host.

Below is the ansible playbook script:

cat collect_info.yml

– hosts: all
  strategy: free
  user: bvunix
  become: yes
  become_method: sudo
  become_user: root
  tasks:
    – name: Copy script collect_audit.sh
      copy: src=collect_audit.sh dest=/home/bvunix mode=0777
    – name: Run script for audit
      command: sh /home/bvunix/collect_audit.sh
    – name: find the output file
      shell: (cd /home/bvunix; find collect*.txt)
      register: files_to_copy
    – debug:
        var: files_to_copy
    – name: Fetch the file from remote to local
      fetch: src=/home/bvunix/{{ item }} dest=/home/bvunix/ mode=0777 flat=yes
      with_items: “{{ files_to_copy.stdout_lines }}”

– Execute the playbook

Ansible-playbook collect_info.yml -f 10

-HOSTFILE :

— If password less ssh connectivity is established, then use the below simple hostfile

172.20.192.1
172.20.192.2
172.20.192.3

— If no passwordless ssh setup is there, then hardcode the credentials as below:

linux1 ansible_host=172.20.192.1 ansible_connection=ssh ansible_user=bvunix ansible_ssh_pass=classic123
linux2 ansible_host=172.20.192.3 ansible_connection=ssh ansible_user=bvunix ansible_ssh_pass=classic123
linux3 ansible_host=172.20.192.3 ansible_connection=ssh ansible_user=bvunix ansible_ssh_pass=classic123

In the later tutorial,  we will explain how to use ansible-vault to encrypt the host file.

The post Ansible script to run script on remote server and fetch the output appeared first on DBACLASS.