Ansible Oracle Database

/ Automatiseren, Database /

In dit artikel geven we een voorbeeld hoe je een Oracle Database kan opzetten met behulp van Ansible.

Aanmaken Ansible Roles

Playbooks zijn de configuratie-, implementatie- en orkestratie-taal van Ansible. Hoewel het mogelijk is om een playbook in één groot bestand te schrijven, zullen we uiteindelijk bestanden willen hergebruiken en dingen organiseren. Rollen bouwen in Ansible voort op het idee van include-bestanden en combineren deze tot herbruikbare abstracties – ze stellen ons in staat meer op het grotere geheel te focussen en duiken alleen in de details wanneer dat nodig is. Ansible biedt een aantal modules die hier worden beschreven. Als de behoefte zich voordoet, kunnen we ook onze eigen modules ontwikkelen.

Voor het installeren en configureren van een Oracle-database maken we de volgende rollen:

  • oracle_database_vars – definieert de default waarden voor de gebruikte variabelen.
  • oracle_database_host – configureert de Linux-host, d.w.z., configureert SELinux, configureert de firewall, installeert packets, maakt een groep en een gebruiker aan, past kernelparameters aan (/etc/sysctl.conf) en stelt limieten in voor systeem resources die kunnen worden verkregen in een gebruikerssessie (/etc/security/limits.conf).
  • oracle_database_install – installeert de Oracle Database-software.
  • oracle_database_config – creëert een listener en een database.

De directorystructuur voor de rollen ziet er als volgt uit 

/etc/ansible
    /inventories
        /test
            /group_vars
                database_hosts.yml
            hosts.cfg
    /roles
        /oracle_database_config
            /files
                /oracle/lifecycle
                    start_database.sh
                    stop_database.sh
            /tasks
                main.yml
            /templates
                database_service.j2
                dbca_rsp_12.2.0.j2
                dbca_rsp_19.0.0.j2
                netca_rsp_12.2.0.j2
                netca_rsp_19.0.0.j2
        /oracle_database_host
            /handlers
                main.yml
            /tasks
                main.yml
            /templates
                limits_conf.j2
                sysctl_conf.j2
        /oracle_database_install
            /tasks
                main.yml
            /templates
                db_install_rsp_12.2.0.j2
                db_install_rsp_19.0.0.j2
        /oracle_database_vars
            /defaults
                main.yml
    oracle_database.yml
/mnt/hgfs/vmware_temp
    /database_12.2.0
        /install
        /response
        /rpm
        /sshsetup
        /stage
        runInstaller
        welcome.html
    /database_19.0.0
        V982063-01.zip

Configureer (Linux) host

Om de (Linux) host te configureren, gebruiken we het volgende:

  • Gebruikte variabelen
##### User and group settings #####
# User and Group under which the software needs to be installed
oracle_install_user: "oracle"
oracle_install_password: "set_in_environment_specific_group_vars_file"
oracle_install_group: "oinstall"
oracle_install_group_id: 54321

##### OS settings #####
# number of open files
number_of_open_files: 65536

# number of processes
number_of_processes: 16384

# stack size
stack_size: 10240

# memory that will be used for huge pages
memory_to_reserve_in_mega_bytes: 256

number_of_huge_pages: "{{ memory_to_reserve_in_mega_bytes // 2 }}"
mem_lock: "{{ 2048 * (memory_to_reserve_in_mega_bytes // 2) }}"
  • Op Oracle Linux is het mogelijk de host te configureren met behulp van de preinstallation RPM, voor andere Linux versies zoals bijvoorbeeld RedHat of CentOS doorlopen we de volgende stappen.
  • Installeer de benodigde packets.
  • Creëer een user en een groep waaronder de Oracle Database zal worden geïnstalleerd en geconfigureerd. Merk op dat de password optie van de Ansible user_module gehashed moet zijn. Om een gehashed wachtwoord te maken, kunnen we python -c 'importcrypt; print crypt.crypt("user_password", "$id$salt")'gebruiken, waarin id 1 (MD5), 5 (SHA-256), of 6 (SHA-512) kan zijn, en salt voor de maximaal 16 tekens volgend op $id$ staat. Meer informatie is te vinden in de crypte (3) Linux-handleiding.
  • Pas de kernel parameters aan
# Kernel sysctl configuration file for Red Hat Linux
#
# For binary values, 0 is disabled, 1 is enabled.  See sysctl(8) and
# sysctl.conf(5) for more details.

# Controls IP packet forwarding
net.ipv4.ip_forward = 0

# Controls source route verification
net.ipv4.conf.default.rp_filter = 1

# Do not accept source routing
net.ipv4.conf.default.accept_source_route = 0

# Controls the System Request debugging functionality of the kernel
kernel.sysrq = 0

# Controls whether core dumps will append the PID to the core filename.
# Useful for debugging multi-threaded applications.
kernel.core_uses_pid = 1

# Controls the use of TCP syncookies
net.ipv4.tcp_syncookies = 1

# Controls the default maxmimum size of a mesage queue
kernel.msgmnb = 65536

# Controls the maximum size of a message, in bytes
kernel.msgmax = 65536

# Controls the maximum shared segment size, in bytes
kernel.shmmax = 68719476736

# Controls the maximum number of shared memory segments, in pages
kernel.shmall = 4294967296

# semaphores
kernel.sem = 250 32000 100 128
# shared memory
kernel.shmmni = 4096

# file handles
fs.file-max = 6815744

# asynchronous i/o
fs.aio-max-nr = 1048576

# disable ipv6
net.ipv6.conf.all.disable_ipv6 = 1
net.ipv6.conf.default.disable_ipv6 = 1
net.ipv6.conf.lo.disable_ipv6 = 1

# default range of IP port numbers that are allowed for TCP and UDP traffic
net.ipv4.ip_local_port_range = 9000 65535

# increase TCP max buffer size (depending on the type of NIC and the round-trip time these values can be changed)
# Maximum TCP Receive Window
net.core.rmem_max = 8388608
net.core.rmem_default = 8388608
# Maximum TCP Send Window
net.core.wmem_max = 8388608
net.core.wmem_default = 8388608
#  memory reserved for TCP receive buffers (vector of 3 integers: [min, default, max])
net.ipv4.tcp_rmem = 4096 87380 8388608
# memory reserved for TCP send buffers (vector of 3 integers: [min, default, max])
net.ipv4.tcp_wmem = 4096 87380 8388608

# increase the length of the processor input queue
net.core.netdev_max_backlog = 30000
# maximum amount of memory buffers (could be set equal to net.core.rmem_max and net.core.wmem_max)
net.core.optmem_max = 20480
# socket of the listen backlog
net.core.somaxconn = 1024

# tcp selective acknowledgements (disable them on high-speed networks)
net.ipv4.tcp_sack = 1
net.ipv4.tcp_dsack = 1
# Timestamps add 12 bytes to the TCP header
net.ipv4.tcp_timestamps = 1
# Support for large TCP Windows - Needs to be set to 1 if the Max TCP Window is over 65535
net.ipv4.tcp_window_scaling = 1
# The interval between the last data packet sent (simple ACKs are not considered data) and the first keepalive probe
net.ipv4.tcp_keepalive_time = 1800
# The interval between subsequential keepalive probes, regardless of what the connection has exchanged in the meantime
net.ipv4.tcp_keepalive_intvl = 30
# The number of unacknowledged probes to send before considering the connection dead and notifying the application layer
net.ipv4.tcp_keepalive_probes = 5
# The time that must elapse before TCP/IP can release a closed connection and reuse its resources.
net.ipv4.tcp_fin_timeout = 30
# Size of the backlog connections queue.
net.ipv4.tcp_max_syn_backlog = 4096
# The tcp_tw_reuse setting is particularly useful in environments where numerous short connections are open and left in TIME_WAIT state, such as web servers.
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 1

# The percentage of how aggressively memory pages are swapped to disk
vm.swappiness = 0
# The percentage of main memory the pdflush daemon should write data out to the disk.
vm.dirty_background_ratio = 25
# The percentage of main memory the actual disk writes will take place.
vm.dirty_ratio = 20

# set the number of huge pages based on the Hugepagesize, i.e., 2048kB
#vm.nr_hugepages = {{ number_of_huge_pages }}
 
# give permission to the group that runs the process to access the shared memory segment
#vm.hugetlb_shm_group = {{ oracle_install_group_id }}
  • Pas de limits op de systeem resources aan:
# /etc/security/limits.conf
#
#Each line describes a limit for a user in the form:
#
#<domain>        <type>  <item>  <value>
#
#Where:
#<domain> can be:
#        - a user name
#        - a group name, with @group syntax
#        - the wildcard *, for default entry
#        - the wildcard %, can be also used with %group syntax,
#                 for maxlogin limit
#
#<type> can have the two values:
#        - "soft" for enforcing the soft limits
#        - "hard" for enforcing hard limits
#
#<item> can be one of the following:
#        - core - limits the core file size (KB)
#        - data - max data size (KB)
#        - fsize - maximum filesize (KB)
#        - memlock - max locked-in-memory address space (KB)
#        - nofile - max number of open file descriptors
#        - rss - max resident set size (KB)
#        - stack - max stack size (KB)
#        - cpu - max CPU time (MIN)
#        - nproc - max number of processes
#        - as - address space limit (KB)
#        - maxlogins - max number of logins for this user
#        - maxsyslogins - max number of logins on the system
#        - priority - the priority to run user process with
#        - locks - max number of file locks the user can hold
#        - sigpending - max number of pending signals
#        - msgqueue - max memory used by POSIX message queues (bytes)
#        - nice - max nice priority allowed to raise to values: [-20, 19]
#        - rtprio - max realtime priority
#
#<domain>      <type>  <item>         <value>
#

#*               soft    core            0
#*               hard    rss             10000
#@student        hard    nproc           20
#@faculty        soft    nproc           20
#@faculty        hard    nproc           50
#ftp             hard    nproc           0
#@student        -       maxlogins       4


# open file descriptors
@{{ oracle_install_group }} soft nofile {{ number_of_open_files }}
@{{ oracle_install_group }} hard nofile {{ number_of_open_files }}

#max number of processes
@{{ oracle_install_group }} soft nproc {{ number_of_processes }}
@{{ oracle_install_group }} hard nproc {{ number_of_processes }}

#max stack size (KB)
@{{ oracle_install_group }} soft stack {{ stack_size }}
@{{ oracle_install_group }} hard stack {{ stack_size }}

# memlock - maximum locked in-memory address space (kB)
#@{{ oracle_install_group }} soft memlock {{ mem_lock }}
#@{{ oracle_install_group }} hard memlock {{ mem_lock }}


# End of file
  • Configureer de firewall. Hiertoe gebruiken we de Ansible module firewalld en voegen we de listen port toe, in dit geval 1521.
  • Configureer SELinux, hiervoor maken gebruik van de Ansible module(s) selinux (en seboolean, sefcontext, seport). Merk op dat SELinux standaard is geconfigureerd voor de Oracle Database, we kunnen dit controleren met behulp van sesearch --allow | grep -i oracle of
[root@datanode ~]# sesearch --allow --source=httpd_t | grep -i oracle
   allow httpd_t oracle_client_packet_t : packet recv ;
   allow httpd_t oracle_client_packet_t : packet send ;
   allow httpd_t oracle_port_t : tcp_socket name_connect ;

# check ports
[root@datanode ~]# semanage port -l | grep oracle_port_t
oracle_port_t                  tcp      1521, 2483, 2484
oracle_port_t                  udp      1521, 2483, 2484

# check file context mappings
[root@datanode ~]# semanage fcontext -l | grep etc_t
/etc/.*                                            all files          system_u:object_r:etc_t:s0
...

[root@datanode ~]# ll /etc/ora*
-rw-r--r--. 1 root   root      56 May  9 13:33 /etc/oraInst.loc
-rw-rw-r--. 1 oracle oinstall 786 May  9 13:34 /etc/oratab
  • Tijdens het installeren en configureren is het handig om SELinux in permissive mode te zetten en de audit log (/var/log/audit/audit.log) te controleren . SELinux troubleshooting wordt uitgelegd in het artikel: Basic SELinux Troubleshooting in CLI.

Als we het bovenstaande in een Ansible-playbook combineren, hebben we het volgende

# /etc/ansible/roles/oracle_database_host/tasks/main.yml

#- name: install oracle database server preinstall
#  yum:
#    name: http://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm
#    name: http://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/oracle-database-server-12cR2-preinstall-1.0-4.el7.x86_64.rpm
#    state: present

- name: install necessary packages
  yum:
    name={{ item }}
    state=present
  loop:
    - binutils
    - compat-libcap1
    - compat-libstdc++-33
    - dos2unix
    - gcc
    - gcc-c++
    - glibc
    - glibc-devel
    - kmod
    - ksh
    - libaio
    - libaio-devel
    - libgcc
    - libstdc++
    - libstdc++-devel
    - libX11
    - libXau
    - libxcb
    - libXext
    - libXi
    - libXrender
    - libXtst
    - lsof
    - ksh
    - make
    - net-tools
    - perl
    - smartmontools
    - strace
    - sysstat
    - unzip

- name: create group
  group:
    name={{ oracle_install_group }}
    gid={{ oracle_install_group_id }}
    state=present
    
- name: create user
  user:
    name={{ oracle_install_user }}
    state=present
    createhome=yes
    group={{ oracle_install_group_id }}
    password={{ oracle_install_password }}

# kernel parameters
- name: set sysctl /etc/sysctl.d/10-default_sysctl.conf
  template: 
    src=roles/oracle_database_host/templates/sysctl_conf.j2 
    dest=/etc/sysctl.d/10-default_sysctl.conf
    owner=root
    group=root
    mode=0644
  register: sysctl_changed
  
- name: run sysctl -p
  command: /bin/sh -c "/sbin/sysctl -p"
  when: sysctl_changed is changed
    
# linux resource limits
- name: set /etc/security/limits.conf
  template: 
    src=roles/oracle_database_host/templates/limits_conf.j2 
    dest=/etc/security/limits.conf
    owner=root
    group=root
    mode=0644
    
# configure firewall
- name: check if iptables exists
  stat: path=/usr/lib/systemd/system/iptables.service
  register: iptables_status
    
- name: disable iptables
  service:
    name=iptables
    state=stopped
    enabled=no
  when: iptables_status.stat.exists
  
- name: check if firewalld exists
  stat: path=/usr/lib/systemd/system/firewalld.service
  register: firewalld_status

- name: enable firewalld
  systemd:
    name: firewalld
    state: started
    enabled: yes
  when: firewalld_status.stat.exists
  register: firewall_started

- name: activate public zone by adding interface to public zone
  firewalld:
    zone: public
    interface: "{{ ansible_default_ipv4.interface }}"
    state: enabled
    permanent: yes
    immediate: yes
  when: firewall_started is success
    
- name: remove dhcpv6-client service from zone public
  firewalld:
    zone: public
    service: dhcpv6-client
    state: disabled
    permanent: yes
    immediate: yes
  when: firewall_started is success

- name: add database listen port to zone public
  firewalld:
    zone: public
    port: 1521/tcp
    state: enabled
    permanent: yes
    immediate: yes
  when: firewall_started is success

# configure selinux
- name: enable selinux
  selinux:
    policy=targeted
    state=permissive
  notify:
    - restart server
    - wait for server to restart


# /etc/ansible/roles/database_host/handlers/main.yml

- name: restart server
  shell: sleep 2 && shutdown -r now "Ansible updates triggered"
  async: 1
  poll: 0
  ignore_errors: True
  
- name: wait for server to restart
  local_action: wait_for
    host={{ inventory_hostname }}
    port=22
    state=started
    delay=10
    timeout=300
  become: false

Installatie

Voor de installatie van Oracle Database-software gebruiken we het volgende:

  • Gebruikte variabelen
##### database/grid version specification #####
software_version: "19.0.0"
# Directory where the software to be installed is located
software_directory: "/mnt/hgfs/vmware_temp/database_{{ software_version }}"

# when software version is 18 or higher choose image or rpm based install
image_based_software_installation: "true"
image_name: "V982063-01.zip"

rpm_based_software_installation: "false"
rpm_path: "oracle-database-ee-19c-1.0-1.x86_64.rpm"

##### database directories #####
# The scripts create files that are placed in this directory
temporary_directory: "/u01/repsonse_files_{{ software_version }}"
# root directory
root_directory: "/u01"
# Scripts directory
scripts_directory: "{{ root_directory }}/app/scripts"
# Location of the Oracle inventory
oracle_inventory_home: "{{ root_directory }}/app/oraInventory"
# Base directory
base_directory: "{{ root_directory }}/app/oracle"
# Oracle home
oracle_home: "{{ base_directory }}/product/{{ software_version }}/dbhome"

##### install options #####
oracle_hostname: "set_in_environment_specific_group_vars_file"
oracle_install_option: "INSTALL_DB_SWONLY"
oracle_installation_edition: "EE"
oracle_execute_root_script: "false"

##### files #####
run_installer_12c: "{{ software_directory }}/runInstaller"
run_installer: "{{ oracle_home }}/runInstaller"
net_creation_assistent: "{{ oracle_home }}/bin/netca"
database_creation_assistent: "{{ oracle_home }}/bin/dbca"

silent_install_file: "{{ temporary_directory }}/silent-install.rsp"
silent_listener_config_file: "{{ temporary_directory }}/silent-listener-config.rsp"
silent_database_config_file: "{{ temporary_directory }}/silent-database-config.rsp"

root_sh: "{{ oracle_home }}/root.sh"
orainst_root_sh: "{{ oracle_inventory_home }}/orainstRoot.sh"

orainst_loc: "/etc/oraInst.loc"
oratab: "/etc/oratab"

listener_ora: "{{ oracle_home }}/network/admin/listener.ora"
sp_file: "{{ oracle_home }}/dbs/spfile{{ database_sid }}.ora"
database_service: "{{ scripts_directory }}/database_service"
  • Maak de directory structuur en stel de permissions in.
  • Gebaseerd op de image_based_software_installation en rpm_based_software_installation variabelen, wordt de software geïnstalleerd met behulp van een image of een RPM (of wanneer beide false zijn, op de 12c manier). Merk op dat
Starting with Oracle Database 18c, installation and configuration of Oracle Database software is simplified with image-based installation. To install Oracle Database, create the new Oracle home, extract the image file into the newly-created Oracle home, and run the setup wizard to register the Oracle Database product. Using image-based installation, you can install and upgrade Oracle Database for single-instance and cluster configurations. This installation feature streamlines the installation process and supports automation of large-scale custom deployments. You can also use this installation method for deployment of customized images, after you patch the base-release software with the necessary Release Updates (Updates) or Release Update Revisions (Revisions).
Starting with Oracle Database 18c, you can install a single-instance Oracle Database or an Oracle Database Instant Client software using RPM packages. An RPM-based installation performs preinstallation checks, extracts the database software, reassigns ownership of the extracted software to the preconfigured user and groups, maintains the Oracle inventory, and executes all root operations required to configure the Oracle Database software for a single-instance Oracle Database creation and configuration. The RPM–based installation process detects when the minimum requirements for an installation are not met and prompts you to finish these minimum preinstallation requirements. An RPM-based installation performs a software-only Oracle Database installation and creates an Oracle home. After the Oracle home is created, you can then use Oracle Database Configuration Assistant (Oracle DBCA) to create an Oracle Database.
  • Creëer response file, bijvoorbeeld
####################################################################
## Copyright(c) Oracle Corporation 1998,2019. All rights reserved.##
##                                                                ##
## Specify values for the variables listed below to customize     ##
## your installation.                                             ##
##                                                                ##
## Each variable is associated with a comment. The comment        ##
## can help to populate the variables with the appropriate        ##
## values.                                                        ##
##                                                                ##
## IMPORTANT NOTE: This file contains plain text passwords and    ##
## should be secured to have read permission only by oracle user  ##
## or db administrator who owns this installation.                ##
##                                                                ##
####################################################################


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

#-------------------------------------------------------------------------------
# Specify the installation option.
# It can be one of the following:
#   - INSTALL_DB_SWONLY
#   - INSTALL_DB_AND_CONFIG
#-------------------------------------------------------------------------------
oracle.install.option={{ oracle_install_option }}

#-------------------------------------------------------------------------------
# Specify the Unix group to be set for the inventory directory.  
#-------------------------------------------------------------------------------
UNIX_GROUP_NAME={{ oracle_install_group }}

#-------------------------------------------------------------------------------
# Specify the location which holds the inventory files.
# This is an optional parameter if installing on
# Windows based Operating System.
#-------------------------------------------------------------------------------
INVENTORY_LOCATION={{ oracle_inventory_home }}
#-------------------------------------------------------------------------------
# Specify the complete path of the Oracle Home. 
#-------------------------------------------------------------------------------
ORACLE_HOME={{ oracle_home }}

#-------------------------------------------------------------------------------
# Specify the complete path of the Oracle Base. 
#-------------------------------------------------------------------------------
ORACLE_BASE={{ base_directory }}
...
Starting with Oracle Database 19c, the database installer, or setup wizard, provides options to set up permissions to run the root configuration scripts automatically, as required, during a database installation. You continue to have the option to run the root configuration scripts manually. Setting up permissions for root configuration scripts to run without user intervention can simplify database installation and help avoid inadvertent permission errors.
  • Aangezien we gebruik maken van Ansible, staat de ‘root script execution mode’ op false
################################################################################
#                                                                              #
#                      Root script execution configuration                     #
#                                                                              #
################################################################################

#-------------------------------------------------------------------------------------------------------
# Specify the root script execution mode.
#
#   - true  : To execute the root script automatically by using the appropriate configuration methods.
#   - false : To execute the root script manually.
#
# If this option is selected, password should be specified on the console.
#-------------------------------------------------------------------------------------------------------
oracle.install.db.rootconfig.executeRootScript={{ oracle_execute_root_script }}

#--------------------------------------------------------------------------------------
# Specify the configuration method to be used for automatic root script execution.
#
# Following are the possible choices:
#   - ROOT
#   - SUDO
#--------------------------------------------------------------------------------------
oracle.install.db.rootconfig.configMethod=
#--------------------------------------------------------------------------------------
# Specify the absolute path of the sudo program.
#
# Applicable only when SUDO configuration method was chosen.
#--------------------------------------------------------------------------------------
oracle.install.db.rootconfig.sudoPath=

#--------------------------------------------------------------------------------------
# Specify the name of the user who is in the sudoers list. 
# Applicable only when SUDO configuration method was chosen.
# Note:For Single Instance database installations,the sudo user name must be the username of the user installing the database.
#--------------------------------------------------------------------------------------
oracle.install.db.rootconfig.sudoUserName=

Als we het bovenstaande in een Ansible-playbook combineren, hebben we het volgende

# /etc/ansible/roles/oracle_database_install/tasks/main.yml

- name: create root directory
  file:
    path: "{{ root_directory }}"
    state: directory
    owner: "{{ oracle_install_user }}"
    group: "{{ oracle_install_group }}"
    mode: 0755
    
- name: create directories
  file:
    path: "{{ item }}"
    state: directory
    owner: "{{ oracle_install_user }}"
    group: "{{ oracle_install_group }}"
    mode: 0755
  when: not( (item is undefined) or (item is none) or (item | trim=='') )
  with_items:
    - "{{ temporary_directory }}"
    - "{{ scripts_directory }}"
    - "{{ oracle_home }}"

- name: image based software installation
  unarchive:
    copy: no
    creates: "{{ oracle_home }}/runInstaller"
    src: "{{ software_directory }}/{{ image_name }}"
    dest: "{{ oracle_home }}"
    owner: "{{ oracle_install_user }}"
    group: "{{ oracle_install_group }}"
  when: image_based_software_installation
    
- name: rpm based software installation
  yum:
    name: "{{ software_directory }}/{{ rpm_name }}"
    state: present
  when: rpm_based_software_installation
    
- name: create silent install file
  template:
    src: roles/oracle_database_install/templates/db_install_rsp_{{ software_version }}.j2 
    dest: "{{ silent_install_file }}"
    owner: "{{ oracle_install_user }}"
    group: "{{ oracle_install_group }}"
    mode: 0644
    
- name: check if orainstroot exists
  stat: path="{{ orainst_root_sh }}"
  register: orainstroot_status
    
- name: 12c oracle database install
  become: true
  become_user: "{{ oracle_install_user }}"
  become_method: sudo
  command: /bin/sh -c "{{ software_directory }}/runInstaller -silent -ignorePrereq -waitforcompletion -responseFile {{ silent_install_file }}"
  when: not orainstroot_status.stat.exists and not image_based_software_installation and not rpm_based_software_installation
  ignore_errors: True
  register: oracle_database_installed
  
- name: debug 12c oracle database install
  debug:
    var=oracle_database_installed
  when: oracle_database_installed is changed

- name: install oracle database
  become: true
  become_user: "{{ oracle_install_user }}"
  become_method: sudo
  command: /bin/sh -c "{{ oracle_home }}/runInstaller -silent -ignorePrereq -waitforcompletion -responseFile {{ silent_install_file }}"
  when: not orainstroot_status.stat.exists and (image_based_software_installation or rpm_based_software_installation)
  ignore_errors: True
  register: oracle_database_installed
  
- name: debug install oracle database
  debug:
    var=oracle_database_installed
  when: oracle_database_installed is changed

- name: run orainstRoot.sh
  command: /bin/sh "{{ orainst_root_sh }}"
    creates="{{ orainst_loc }}"

- name: run root.sh
  command: /bin/sh "{{ root_sh }}"
    creates="{{ oratab }}"
    
- name: delete temporary setup files that contain usernames and passwords
  file:
    path: "{{ item }}"
    state: absent
  loop:
    - "{{ silent_install_file }}"

Configuratie

Voor het configureren van de Oracle-database gebruiken we het volgende:

  • Gebruikte variabelen
##### configuration options #####
listener_name: "LISTENER"
listener_protocol: "TCP;1521"

global_database_name: "orcl.ms.com"
database_sid: "orcl"
database_config_type: "SI"
policy_managed: "FALSE"
create_server_pool: "FALSE"
create_as_container_database: "FALSE"
template_name: "General_Purpose.dbc"
sys_password: "set_in_environment_specific_group_vars_file"
system_password: "set_in_environment_specific_group_vars_file"
em_configuration: "NONE"
data_vault_configuration: "FALSE"
oracle_label_security_configuration: "FALSE"
storage_type: "FS"
character_set: "AL32UTF8"
register_with_dir_service: "FALSE"
sample_schema: "FALSE"
memory_percentage: 40
database_type: "OLTP"
automatic_memory_management: "TRUE"
  • Creëer response file voor de net configuration assistent, bijvoorbeeld,
###################################################################### 
## Copyright(c) 1998, 2018 Oracle Corporation. All rights reserved. ## 
##                                                                  ## 
## Specify values for the variables listed below to customize your  ## 
## installation.                                                    ## 
##                                                                  ## 
## Each variable is associated with a comment. The comment          ## 
## identifies the variable type.                                    ## 
##                                                                  ## 
## Please specify the values in the following format:               ## 
##                                                                  ## 
##         Type         Example                                     ## 
##         String       "Sample Value"                              ## 
##         Boolean      True or False                               ## 
##         Number       1000                                        ## 
##         StringList   {"String value 1","String Value 2"}         ## 
##                                                                  ## 
######################################################################
##                                                                  ## 
## This sample response file causes the Oracle Net Configuration    ##
## Assistant (NetCA) to complete an Oracle Net configuration during ##
## a custom install of the Oracle12c server which is similar to     ##
## what would be created by the NetCA during typical Oracle12c      ##
## install. It also documents all of the NetCA response file        ##
## variables so you can create your own response file to configure  ##
## Oracle Net during an install the way you wish.                   ##
##                                                                  ## 
###################################################################### 

[GENERAL]
RESPONSEFILE_VERSION="19.0"
CREATE_TYPE="CUSTOM"

#-------------------------------------------------------------------------------
# Name       : SHOW_GUI
# Datatype   : Boolean
# Description: This variable controls appearance/suppression of the NetCA GUI,
# Pre-req    : N/A
# Default    : TRUE
# Note:
# This must be set to false in order to run NetCA in silent mode. 
# This is a substitute of "/silent" flag in the NetCA command line.
# The command line flag has precedence over the one in this response file.
# This feature is present since 10.1.0.3.
#-------------------------------------------------------------------------------
#SHOW_GUI=false

#-------------------------------------------------------------------------------
# Name       : LOG_FILE
# Datatype   : String
# Description: If present, NetCA will log output to this file in addition to the
#	       standard out.
# Pre-req    : N/A
# Default    : NONE
# Note:
# 	This is a substitute of "/log" in the NetCA command line.
# The command line argument has precedence over the one in this response file.
# This feature is present since 10.1.0.3.
#-------------------------------------------------------------------------------
#LOG_FILE=""$ORACLE_BASE/cfgtoollogs/netca/netca.log""

[oracle.net.ca]
#INSTALLED_COMPONENTS;StringList;list of installed components
# The possible values for installed components are:
# "net8","server","client","aso", "cman", "javavm" 
INSTALLED_COMPONENTS={"server","net8","javavm"}

#INSTALL_TYPE;String;type of install
# The possible values for install type are:
# "typical","minimal" or "custom"
INSTALL_TYPE=""typical""

#LISTENER_NUMBER;Number;Number of Listeners
# A typical install sets one listener 
LISTENER_NUMBER=1

#LISTENER_NAMES;StringList;list of listener names
# The values for listener are:
# "LISTENER","LISTENER1","LISTENER2","LISTENER3", ...
# A typical install sets only "LISTENER" 
LISTENER_NAMES={"{{ listener_name }}"}

#LISTENER_PROTOCOLS;StringList;list of listener addresses (protocols and parameters separated by semicolons)
# The possible values for listener protocols are:
# "TCP;1521","TCPS;2484","NMP;ORAPIPE","IPC;IPCKEY","VI;1521" 
# For multiple listeners, separate them with commas ex "TCP;1521","TCPS;2484"
# For multiple protocols in single listener, separate them with "&" ex  "TCP;1521&TCPS;2484"
# A typical install sets only "TCP;1521" 
LISTENER_PROTOCOLS={"{{ listener_protocol }}"}

#LISTENER_START;String;name of the listener to start, in double quotes
LISTENER_START=""{{ listener_name }}""

#NAMING_METHODS;StringList;list of naming methods
# The possible values for naming methods are: 
# LDAP, TNSNAMES, ONAMES, HOSTNAME, NOVELL, NIS, DCE
# A typical install sets only: "TNSNAMES","ONAMES","HOSTNAMES" 
# or "LDAP","TNSNAMES","ONAMES","HOSTNAMES" for LDAP
NAMING_METHODS={"TNSNAMES","ONAMES","HOSTNAME"}
...
  • Run netca (meer informatie met betrekking tot de net configuration assistent kan hier gevonden worden).
  • Creëer response file voor de database configuration assistent, bijvoorbeeld,
##############################################################################
##                                                                          ##
##                            DBCA response file                            ##
##                            ------------------                            ##
## Copyright(c) Oracle Corporation 1998,2019. All rights reserved.          ##
##                                                                          ##
## Specify values for the variables listed below to customize               ##
## your installation.                                                       ##
##                                                                          ##
## Each variable is associated with a comment. The comment                  ##
## can help to populate the variables with the appropriate                  ##
## values.                                                                  ##
##                                                                          ##
## IMPORTANT NOTE: This file contains plain text passwords and              ##
## should be secured to have read permission only by oracle user            ##
## or db administrator who owns this installation.                          ##
##############################################################################
#-------------------------------------------------------------------------------
# Do not change the following system generated value. 
#-------------------------------------------------------------------------------
responseFileVersion=/oracle/assistants/rspfmt_dbca_response_schema_v19.0.0

#-----------------------------------------------------------------------------
# Name          : gdbName
# Datatype      : String
# Description   : Global database name of the database
# Valid values  : <db_name>.<db_domain> - when database domain isn't NULL
#                 <db_name>             - when database domain is NULL
# Default value : None
# Mandatory     : Yes
#-----------------------------------------------------------------------------
gdbName={{ global_database_name }}

#-----------------------------------------------------------------------------
# Name          : sid
# Datatype      : String
# Description   : System identifier (SID) of the database
# Valid values  : Check Oracle19c Administrator's Guide
# Default value : <db_name> specified in GDBNAME
# Mandatory     : No
#-----------------------------------------------------------------------------
sid={{ database_sid }}

#-----------------------------------------------------------------------------
# Name          : databaseConfigType
# Datatype      : String
# Description   : database conf type as Single Instance, Real Application Cluster or Real Application Cluster One Nodes database
# Valid values  : SI\RAC\RACONENODE
# Default value : SI
# Mandatory     : No
#-----------------------------------------------------------------------------
databaseConfigType={{ database_config_type }}
...
  • Run dbca (meer informatie met betrekking tot de database configuration assistent kan hier gevonden worden).

Als we het bovenstaande in een Ansible-playbook combineren, hebben we het volgende

# /etc/ansible/roles/oracle_database_config/tasks/main.yml

- name: create silent listener config file
  template:
    src: roles/oracle_database_config/templates/netca_rsp_{{ software_version }}.j2 
    dest: "{{ silent_listener_config_file }}"
    owner: "{{ oracle_install_user }}"
    group: "{{ oracle_install_group }}"
    mode: 0644
  register: silent_listener_config_file_created

- name: configure listener
  become: true
  become_user: "{{ oracle_install_user }}"
  become_method: sudo
  command: /bin/sh -c "{{ net_creation_assistent }} -silent -responsefile {{ silent_listener_config_file }}"
    creates="{{ listener_ora }}"
  when: silent_listener_config_file_created is success
  register: listener_configured
  
- name: debug configure listener
  debug:
    var=listener_configured
  when: listener_configured is changed

- name: create silent database config file
  template:
    src: roles/oracle_database_config/templates/dbca_rsp_{{ software_version }}.j2 
    dest: "{{ silent_database_config_file }}"
    owner: "{{ oracle_install_user }}"
    group: "{{ oracle_install_group }}"
    mode: 0644
  register: silent_database_config_file_created

- name: configure oracle database
  become: true
  become_user: "{{ oracle_install_user }}"
  become_method: sudo
  command: /bin/sh -c "{{ database_creation_assistent }} -silent -createDatabase -responseFile {{ silent_database_config_file }}"
    creates="{{ sp_file }}"
  when: silent_database_config_file_created is success
  register: oracle_database_configured
  
- name: debug configure oracle database
  debug:
    var=oracle_database_configured
  when: oracle_database_configured is changed
    
- name: create has script
  template:
    src: roles/oracle_database_config/templates/has_service.j2
    dest: "{{ has_service }}"
    owner: "{{ oracle_install_user }}"
    group: "{{ oracle_install_group }}"
    mode: 0744
    
- name: create database script
  template:
    src: roles/oracle_database_config/templates/database_service.j2 
    dest: "{{ database_service }}"
    owner: "{{ oracle_install_user }}"
    group: "{{ oracle_install_group }}"
    mode: 0744
    
- name: copy scripts
  copy: 
    src: roles/oracle_database_config/files/oracle/lifecycle/
    dest: "{{ scripts_directory }}"
    owner: "{{ oracle_install_user }}"
    group: "{{ oracle_install_group }}"
    mode: 0744

- name: delete temporary setup files that contain usernames and passwords
  file:
    path: "{{ item }}"
    state: absent
  loop:
    - "{{ silent_listener_config_file }}"
    - "{{ silent_database_config_file }}"

Run Ansible

Een goede manier om productieomgevingen en andere omgevingen gescheiden te houden, is door afzonderlijke inventory files te gebruiken. In ons voorbeeld heeft de inventory voor de test omgeving de volgende inhoud

# /etc/ansible/inventories/test/hosts.cfg

[database_host]
datanode

[database_hosts:children]
database_host

[all:children]
database_hosts

We maken gebruik van group_var files voor het overriden van defaults

##### User and group settings #####
oracle_install_password:         "..."

##### install options #####
oracle_hostname:                 "{{ inventory_hostname  }}"

##### configuration options #####
sys_password:                    "..."
system_password:                 "..."

Merk op dat dit bestand de verschillen tussen omgevingen regelt, zoals host/ip instellingen. Dit bestand bevat ook gevoelige gegevens, zoals wachtwoorden, en als gevolg hiervan moet dit bestand worden versleuteld. We kunnen bestanden versleutelen met behulp van Vault.

Het playbook dat de hierboven gedefinieerde rollen gebruikt, heeft de volgende inhoud

##########
# database host configuration
# ansible-playbook oracle_database.yml --inventory=inventories/test/hosts.cfg --tags "host"
#
# database installation
# ansible-playbook oracle_database.yml --inventory=inventories/test/hosts.cfg --tags "install"
#
# database configuration
# ansible-playbook oracle_database.yml --inventory=inventories/test/hosts.cfg --tags "config"

- hosts: database_hosts
  user: ansible
  become: true
  become_user: root
  become_method: sudo
  
  roles:
    - { role: oracle_database_vars, tags: ['host','install','config'] }
    - { role: oracle_database_host, tags: ['host'] }
    - { role: oracle_database_install, tags: ['install'] }
    - { role: oracle_database_config, tags: ['config'] }

waarin we ook tags hebben gedefinieerd.

Om de host te configureren, gebruiken we 

[ansible@datanode ansible]$ ansible-playbook oracle_database.yml --inventory=inventories/test/hosts.cfg --tags "host"

PLAY [database_hosts] ******************************************************************************************************************************************************************************************

TASK [Gathering Facts] *****************************************************************************************************************************************************************************************
ok: [datanode]

TASK [oracle_database_host : install necessary packages] *******************************************************************************************************************************************************
ok: [datanode] => (item=binutils)
changed: [datanode] => (item=compat-libcap1)
changed: [datanode] => (item=compat-libstdc++-33)
ok: [datanode] => (item=dos2unix)
ok: [datanode] => (item=gcc)
changed: [datanode] => (item=gcc-c++)
ok: [datanode] => (item=glibc)
ok: [datanode] => (item=glibc-devel)
ok: [datanode] => (item=kmod)
changed: [datanode] => (item=ksh)
ok: [datanode] => (item=libaio)
changed: [datanode] => (item=libaio-devel)
ok: [datanode] => (item=libgcc)
ok: [datanode] => (item=libstdc++)
ok: [datanode] => (item=libstdc++-devel)
changed: [datanode] => (item=libX11)
ok: [datanode] => (item=libXau)
ok: [datanode] => (item=libxcb)
changed: [datanode] => (item=libXext)
changed: [datanode] => (item=libXi)
changed: [datanode] => (item=libXrender)
changed: [datanode] => (item=libXtst)
changed: [datanode] => (item=lsof)
ok: [datanode] => (item=ksh)
ok: [datanode] => (item=make)
ok: [datanode] => (item=net-tools)
ok: [datanode] => (item=perl)
changed: [datanode] => (item=smartmontools)
changed: [datanode] => (item=strace)
changed: [datanode] => (item=sysstat)
changed: [datanode] => (item=unzip)

TASK [oracle_database_host : create group] *********************************************************************************************************************************************************************
changed: [datanode]

TASK [oracle_database_host : create user] **********************************************************************************************************************************************************************
changed: [datanode]

TASK [oracle_database_host : set sysctl /etc/sysctl.d/10-default_sysctl.conf] **********************************************************************************************************************************
changed: [datanode]

TASK [oracle_database_host : run sysctl -p] ********************************************************************************************************************************************************************
changed: [datanode]

TASK [oracle_database_host : set /etc/security/limits.conf] ****************************************************************************************************************************************************
changed: [datanode]

TASK [oracle_database_host : check if iptables exists] *********************************************************************************************************************************************************
ok: [datanode]

TASK [oracle_database_host : disable iptables] *****************************************************************************************************************************************************************
skipping: [datanode]

TASK [oracle_database_host : check if firewalld exists] ********************************************************************************************************************************************************
ok: [datanode]

TASK [oracle_database_host : enable firewalld] *****************************************************************************************************************************************************************
ok: [datanode]

TASK [oracle_database_host : activate public zone by adding interface to public zone] **************************************************************************************************************************
changed: [datanode]

TASK [oracle_database_host : remove dhcpv6-client service from zone public] ************************************************************************************************************************************
changed: [datanode]

TASK [oracle_database_host : add database listen port to zone public] ******************************************************************************************************************************************
changed: [datanode]

TASK [oracle_database_host : enable selinux] *******************************************************************************************************************************************************************
changed: [datanode]

RUNNING HANDLER [oracle_database_host : restart server] ********************************************************************************************************************************************************
changed: [datanode]

RUNNING HANDLER [oracle_database_host : wait for server to restart] ********************************************************************************************************************************************
ok: [datanode -> localhost]

PLAY RECAP *****************************************************************************************************************************************************************************************************
datanode                       : ok=16   changed=11   unreachable=0    failed=0

Om de databasesoftware te installeren, gebruiken we 

[ansible@datanode ansible]$ ansible-playbook oracle_database.yml --inventory=inventories/test/hosts.cfg --tags "install"

PLAY [database_hosts] ******************************************************************************************************************************************************************************************

TASK [Gathering Facts] *****************************************************************************************************************************************************************************************
ok: [datanode]

TASK [oracle_database_install : create root directory] *********************************************************************************************************************************************************
changed: [datanode]

TASK [oracle_database_install : create directories] ************************************************************************************************************************************************************
changed: [datanode] => (item=/u01/repsonse_files_19.0.0)
changed: [datanode] => (item=/u01/app/scripts)
changed: [datanode] => (item=/u01/app/oracle/product/19.0.0/dbhome)

TASK [oracle_database_install : image based software installation] *********************************************************************************************************************************************
changed: [datanode]

TASK [oracle_database_install : rpm based software installation] ***********************************************************************************************************************************************
skipping: [datanode]

TASK [oracle_database_install : create silent install file] ****************************************************************************************************************************************************
changed: [datanode]

TASK [oracle_database_install : 12c oracle database install] ***************************************************************************************************************************************************
skipping: [datanode]

TASK [oracle_database_install : debug 12c oracle database install] *********************************************************************************************************************************************
skipping: [datanode]

TASK [oracle_database_install : install oracle database] *******************************************************************************************************************************************************
[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
changed: [datanode]

TASK [oracle_database_install : debug install oracle database] *************************************************************************************************************************************************
ok: [datanode] => {
    "oracle_database_installed": {
        "changed": true,
        "cmd": [
            "/bin/sh",
            "-c",
            "/u01/app/oracle/product/19.0.0/dbhome/runInstaller -silent -ignorePrereq -waitforcompletion -responseFile /u01/repsonse_files_19.0.0/silent-install.rsp"
        ],
        "delta": "0:01:02.026905",
        "end": "2019-05-08 11:27:39.449180",
        "failed": false,
        "rc": 0,
        "start": "2019-05-08 11:26:37.422275",
        "stderr": "",
        "stderr_lines": [],
        "stdout": "Launching Oracle Database Setup Wizard...\n\nThe response file for this session can be found at:\n /u01/app/oracle/product/19.0.0/dbhome/install/response/db_2019-05-08_11-26-37AM.rsp\n\nYou can find the log of this install session at:\n /tmp/InstallActions2019-05-08_11-26-37AM/installActions2019-05-08_11-26-37AM.log\n\nAs a root user, execute the following script(s):\n\t1. /u01/app/oraInventory/orainstRoot.sh\n\t2. /u01/app/oracle/product/19.0.0/dbhome/root.sh\n\nExecute /u01/app/oraInventory/orainstRoot.sh on the following nodes: \n[datanode]\nExecute /u01/app/oracle/product/19.0.0/dbhome/root.sh on the following nodes: \n[datanode]\n\n\nSuccessfully Setup Software.\nMoved the install session logs to:\n /u01/app/oraInventory/logs/InstallActions2019-05-08_11-26-37AM",
        "stdout_lines": [
            "Launching Oracle Database Setup Wizard...",
            "",
            "The response file for this session can be found at:",
            " /u01/app/oracle/product/19.0.0/dbhome/install/response/db_2019-05-08_11-26-37AM.rsp",
            "",
            "You can find the log of this install session at:",
            " /tmp/InstallActions2019-05-08_11-26-37AM/installActions2019-05-08_11-26-37AM.log",
            "",
            "As a root user, execute the following script(s):",
            "\t1. /u01/app/oraInventory/orainstRoot.sh",
            "\t2. /u01/app/oracle/product/19.0.0/dbhome/root.sh",
            "",
            "Execute /u01/app/oraInventory/orainstRoot.sh on the following nodes: ",
            "[datanode]",
            "Execute /u01/app/oracle/product/19.0.0/dbhome/root.sh on the following nodes: ",
            "[datanode]",
            "",
            "",
            "Successfully Setup Software.",
            "Moved the install session logs to:",
            " /u01/app/oraInventory/logs/InstallActions2019-05-08_11-26-37AM"
        ]
    }
}

TASK [oracle_database_install : run orainstRoot.sh] ************************************************************************************************************************************************************
changed: [datanode]

TASK [oracle_database_install : run root.sh] *******************************************************************************************************************************************************************
changed: [datanode]

TASK [oracle_database_install : delete temporary setup files that contain usernames and passwords] *************************************************************************************************************
changed: [datanode] => (item=/u01/repsonse_files_19.0.0/silent-install.rsp)

PLAY RECAP *****************************************************************************************************************************************************************************************************
datanode                       : ok=10    changed=10    unreachable=0    failed=0

Om de database te configureren, gebruiken we 

[ansible@datanode ansible]$ ansible-playbook oracle_database.yml --inventory=inventories/test/hosts.cfg --tags "config"

PLAY [database_hosts] ******************************************************************************************************************************************************************************************

TASK [Gathering Facts] *****************************************************************************************************************************************************************************************
ok: [datanode]

TASK [oracle_database_config : create silent listener config file] *********************************************************************************************************************************************
changed: [datanode]

TASK [oracle_database_config : configure listener] *************************************************************************************************************************************************************
changed: [datanode]

TASK [oracle_database_config : debug configure listener] *******************************************************************************************************************************************************
ok: [datanode] => {
    "listener_configured": {
        "changed": true,
        "cmd": [
            "/bin/sh",
            "-c",
            "/u01/app/oracle/product/19.0.0/dbhome/bin/netca -silent -responsefile /u01/repsonse_files_19.0.0/silent-listener-config.rsp"
        ],
        "delta": "0:00:01.370024",
        "end": "2019-05-08 11:31:38.365648",
        "failed": false,
        "rc": 0,
        "start": "2019-05-08 11:31:36.995624",
        "stderr": "",
        "stderr_lines": [],
        "stdout": "\nParsing command line arguments:\n    Parameter \"silent\" = true\n    Parameter \"responsefile\" = /u01/repsonse_files_19.0.0/silent-listener-config.rsp\nDone parsing command line arguments.\nOracle Net Services Configuration:\nProfile configuration complete.\nOracle Net Listener Startup:\n    Running Listener Control: \n      /u01/app/oracle/product/19.0.0/dbhome/bin/lsnrctl start LISTENER\n    Listener Control complete.\n    Listener started successfully.\nListener configuration complete.\nOracle Net Services configuration successful. The exit code is 0",
        "stdout_lines": [
            "",
            "Parsing command line arguments:",
            "    Parameter \"silent\" = true",
            "    Parameter \"responsefile\" = /u01/repsonse_files_19.0.0/silent-listener-config.rsp",
            "Done parsing command line arguments.",
            "Oracle Net Services Configuration:",
            "Profile configuration complete.",
            "Oracle Net Listener Startup:",
            "    Running Listener Control: ",
            "      /u01/app/oracle/product/19.0.0/dbhome/bin/lsnrctl start LISTENER",
            "    Listener Control complete.",
            "    Listener started successfully.",
            "Listener configuration complete.",
            "Oracle Net Services configuration successful. The exit code is 0"
        ]
    }
}

TASK [oracle_database_config : create silent database config file] *********************************************************************************************************************************************
changed: [datanode]

TASK [oracle_database_config : configure oracle database] ******************************************************************************************************************************************************
changed: [datanode]

TASK [oracle_database_config : debug configure oracle database] ************************************************************************************************************************************************
ok: [datanode] => {
    "oracle_database_configured": {
        "changed": true,
        "cmd": [
            "/bin/sh",
            "-c",
            "/u01/app/oracle/product/19.0.0/dbhome/bin/dbca -silent -createDatabase -responseFile /u01/repsonse_files_19.0.0/silent-database-config.rsp"
        ],
        "delta": "0:08:13.818718",
        "end": "2019-05-08 11:39:52.847777",
        "failed": false,
        "rc": 0,
        "start": "2019-05-08 11:31:39.029059",
        "stderr": "",
        "stderr_lines": [],
        "stdout": "Prepare for db operation\n10% complete\nCopying database files\n40% complete\nCreating and starting Oracle instance\n42% complete\n46% complete\n50% complete\n54% complete\n60% complete\nCompleting Database Creation\n66% complete\n69% complete\n70% complete\nExecuting Post Configuration Actions\n100% complete\nDatabase creation complete. For details check the logfiles at:\n /u01/app/oracle/cfgtoollogs/dbca/ORCL.\nDatabase Information:\nGlobal Database Name:ORCL\nSystem Identifier(SID):ORCL\nLook at the log file \"/u01/app/oracle/cfgtoollogs/dbca/ORCL/ORCL.log\" for further details.",
        "stdout_lines": [
            "Prepare for db operation",
            "10% complete",
            "Copying database files",
            "40% complete",
            "Creating and starting Oracle instance",
            "42% complete",
            "46% complete",
            "50% complete",
            "54% complete",
            "60% complete",
            "Completing Database Creation",
            "66% complete",
            "69% complete",
            "70% complete",
            "Executing Post Configuration Actions",
            "100% complete",
            "Database creation complete. For details check the logfiles at:",
            " /u01/app/oracle/cfgtoollogs/dbca/ORCL.",
            "Database Information:",
            "Global Database Name:ORCL",
            "System Identifier(SID):ORCL",
            "Look at the log file \"/u01/app/oracle/cfgtoollogs/dbca/ORCL/ORCL.log\" for further details."
        ]
    }
}

TASK [oracle_database_config : create has script] **************************************************************************************************************************************************************
changed: [datanode]

TASK [oracle_database_config : create database script] *********************************************************************************************************************************************************
changed: [datanode]

TASK [oracle_database_config : copy scripts] *******************************************************************************************************************************************************************
changed: [datanode]

TASK [oracle_database_config : delete temporary setup files that contain usernames and passwords] **************************************************************************************************************
changed: [datanode] => (item=/u01/repsonse_files_19.0.0/silent-listener-config.rsp)
changed: [datanode] => (item=/u01/repsonse_files_19.0.0/silent-database-config.rsp)

PLAY RECAP *****************************************************************************************************************************************************************************************************
datanode                       : ok=11   changed=8    unreachable=0    failed=0

met het resultaat (a running database) 

[oracle@datanode scripts]$ ./database_service status
The Oracle base has been set to /u01/app/oracle
Checking Database

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 08-MAY-2019 13:12:49

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=datanode.ms.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                08-MAY-2019 11:46:32
Uptime                    0 days 1 hr. 26 min. 16 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.0.0/dbhome/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/datanode/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=datanode.ms.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "ORCL" has 1 instance(s).
  Instance "ORCL", status READY, has 1 handler(s) for this service...
Service "ORCLXDB" has 1 instance(s).
  Instance "ORCL", status READY, has 1 handler(s) for this service...
The command completed successfully

Om de set-up te testen, creëren we een data source in WebLogic, d.w.z. 

[weblogic@javanode1 scripts]$ cat /u01/app/oracle/weblogic12.2.1/configuration/domains/tryout_domain/config/jdbc/data_source-jdbc.xml
<jdbc-data-source ...>
  <name>data_source</name>
  <jdbc-driver-params>
    <url>jdbc:oracle:thin:@//datanode.ms.com:1521/orcl.ms.com</url>
    <driver-name>oracle.jdbc.xa.client.OracleXADataSource</driver-name>
    <properties>
      <property>
        <name>user</name>
        <value>example</value>
      </property>
    </properties>
    <password-encrypted>{AES}iJxm6loPE9PHASnV4wMS4xkje9JxdNj/u1Vp9sW5lp0=</password-encrypted>
  </jdbc-driver-params>
  <jdbc-connection-pool-params>
    <initial-capacity>1</initial-capacity>
    <max-capacity>15</max-capacity>
    <min-capacity>1</min-capacity>
    <connection-creation-retry-frequency-seconds>30</connection-creation-retry-frequency-seconds>
    <test-table-name>SQL ISVALID</test-table-name>
    <statement-cache-size>0</statement-cache-size>
    <statement-cache-type>LRU</statement-cache-type>
  </jdbc-connection-pool-params>
  <jdbc-data-source-params>
    <jndi-name>jdbc/exampleDS</jndi-name>
    <global-transactions-protocol>TwoPhaseCommit</global-transactions-protocol>
  </jdbc-data-source-params>
</jdbc-data-source>

en gebruiken de applicatie hier om de database te pesten

[weblogic@javanode1 scripts]$ cat /u01/app/oracle/weblogic12.2.1/configuration/domains/tryout_domain/servers/application_server_1/logs/application_server_1.out
...
RECEIVED OBJECT MESSAGE 145vbizoe7myg 5418
RECEIVED OBJECT MESSAGE fb6bol1924w4 708
RECEIVED OBJECT MESSAGE 1ch63dy8yqai9 2187
RECEIVED OBJECT MESSAGE 1g7gaw57fzotn 1331
RECEIVED OBJECT MESSAGE oguc0qagbp4u 9137
RECEIVED OBJECT MESSAGE bqvwlsb9zv89 7516
RECEIVED OBJECT MESSAGE dx4mjshkh1vk 7154
RECEIVED OBJECT MESSAGE 1oiykwu7nzfhk 7478
RECEIVED OBJECT MESSAGE 1wkdoab0estec 9915
RECEIVED OBJECT MESSAGE 19hi8q8q6x7yu 9398
RECEIVED OBJECT MESSAGE 1cbh6wevr0tpx 8587
RECEIVED OBJECT MESSAGE 1iw33pa5jzm1y 9239
RECEIVED OBJECT MESSAGE 1fgn7a86yvn3k 1431
RECEIVED OBJECT MESSAGE 1sxe62ll5vyz3 976
RECEIVED OBJECT MESSAGE uycy5zje69yt 5053
RECEIVED OBJECT MESSAGE 1vu33l45a4nv1 9714
RECEIVED OBJECT MESSAGE henvcnmrykhx 2779
RECEIVED OBJECT MESSAGE 1sysqs60tsjka 8564
RECEIVED OBJECT MESSAGE 162nwi88unxav 5078
RECEIVED OBJECT MESSAGE 1bev9nc1b2t6a 778

[oracle@datanode scripts]$ . /usr/local/bin/oraenv
ORACLE_SID = [oracle] ? orcl
The Oracle base has been set to /u01/app/oracle
[oracle@datanode scripts]$ sqlplus

SQL*Plus: Release 19.0.0.0.0 - Production on Mon May 20 13:27:52 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Enter user-name: example
Enter password:
Last Successful login time: Mon May 20 2019 13:23:37 +02:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> describe persoon;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SOFINUMMER                                NOT NULL NUMBER(9)
 NAAM                                      NOT NULL VARCHAR2(50)
 FAMILYSOFINUMMER                                   NUMBER(9)

SQL> select sofinummer from persoon where naam like '1bev9nc1b2t6a';

SOFINUMMER
----------
       778

Met het automatiseren van omgevingen maken we IT simpel (en foutloos).

Scroll to Top