DBA Blogs

Step by Step: Ansible Role To Setup Oracle ACFS On Multiple Nodes

Pakistan's First Oracle Blog - Thu, 2018-09-06 17:46
This post contains step by step instructions for creating an Ansible role acfssetup to setup Oracle ASM Cluster Filesystem (ACFS) on multiple nodes of a cluster. This assumes that Grid Infrastructure 12.1.0.2.0 is already installed on the nodes, and ASM is working fine. This also assumes that there already is Ansible installed on some controller host with ssh equivalency setup between root and Oracle users.




Step 1: Create directory structure for the role acfssetup:


$ cd /etc/ansible/roles
$ mkdir acfssetup
$ mkdir files handlers meta templates tasks vars

Step 2: Create the Tasks (/etc/ansible/roles/acfssetup/tasks/main.yml):

---

- name: Install ACFS/ADVM modules on the nodes
  become_user: "{{ superuser }}"
  environment: "{{ asm_instance }}"
  shell:"{ gi_home_path }}/bin/acfsroot install"
  tags:acfs

- name: Start and enable the ACFS modules on the nodes
  become_user: "{{ superuser }}"
  environment: "{{ asm_instance }}"
  shell:"{ gi_home_path }}/bin/acfsload start"
  shell:"{ gi_home_path }}/bin/acfsroot enable"
  tags:acfs
 
- name: As oracle user, create an ASM volume for ACFS on first node
  when: inventory_hostname in groups['node1']
  become_user: "{{ gi_owner }}"
  environment: "{{ asm_instance }}"
  shell:"{ gi_home_path }}/bin/asmcmd volcreate -G {{ asm_dg_name }} -s {{ acfs_vol_size }} {{ acfs_vol_name }}"
  shell:"{ gi_home_path }}/bin/asmcmd volinfo -G {{ asm_dg_name }} {{ acfs_vol_name }} | grep Device | sed 's/.*://'"
  register: {{ device_name }}
  tags:acfs

- name: As oracle user, create the filesystem on the volume which was just created
  become_user: "{{ gi_owner }}"
  environment: "{{ asm_instance }}"
  shell:"/sbin/mkfs -t acfs {{ device_name }}.stdout"
  tags:acfs

- name: As root, create an empty directory which will house the file system
  become_user: "{{ superuser }}"
  environment: "{{ asm_instance }}"
  shell:"mkdir -p /{{ acfs_mount_name }}/{{ acfs_vol_name }}; chown root:oinstall /{{ acfs_mount_name }}; chmod 770 /{{ acfs_mount_name }}; chown -R oracle:oinstall /{{ acfs_mount_name }}/{{ acfs_vol_name }}; chmod 775 /{{ acfs_mount_name }}/{{ acfs_vol_name }}"
  tags:acfs

- name: As root, setup the file system to be auto mounted by clusterware
  become_user: "{{ superuser }}"
  environment: "{{ asm_instance }}"
  shell:"{ gi_home_path }}/bin/srvctl add volume -volume {{ acfs_vol_name }} -diskgroup {{ asm_dg_name }} -device {{ device_name }}.stdout; { gi_home_path }}/bin/srvctl add filesystem -device {{ device_name }}.stdout -path {{ acfs_mount_name }}/{{ acfs_vol_name }} -diskgroup {{ asm_dg_name }} -user {{ gi_owner }} -fstype ACFS -description \"ACFS General Purpose Mount\""
  tags:acfs

  Step 3: Create the Variables (/etc/ansible/roles/acfssetup/vars/main.yml):

ver: "12.1.0.2.0"
superuser: root
asm_instance: +ASM
asm_dg_name: DATA
acfs_vol_name: ACFSVOL1
acfs_vol_size: 10G
acfs_mount_name: acfsmounts
device_name: default([])
gi_owner: oracle
gi_group: oinstall
gi_base_path: "/u01/app/oracle"
gi_home_path: "{{ gi_base_path }}/product/{{ ver |
regex_replace('^(.*)\\.(.*)\\.(.*)\\.(.*)$', '\\1.\\2.\\3') }}/grid"
gi_home_name: "OraGI{{ ver | regex_replace('^(.*)\\.(.*)\\.(.*)\\.(.*)$', '\\1\\2')}}"

Step 4: Configure Ansible host file (/etc/ansible/hosts)

node1 ansible_host=node1.foo.com
node2 ansible_host=node2.foo.com

Step 5: Create the skeleton Playbook (/etc/ansible/acfs.yml):

---
- hosts: all
become: true
roles:
- acfssetup

Step 6: Run the playbook

$ ansible-playbook acfs.yml
Categories: DBA Blogs

How to sort in alphabetical order and get the 2nd ranked rows

Tom Kyte - Thu, 2018-09-06 17:06
Hi Tom, How to do sorting the data in alphabetical order and assign the rank of them like I have data and assign the rank(rank must be assign alphabetical order) like this. for that, I used to order by, <code>A ADAMS-1 ALLEN-2 B B...
Categories: DBA Blogs

DBMS_AQ.DEQUEUE

Tom Kyte - Thu, 2018-09-06 17:06
Hi Tom, Can you please explain me difference between dequeue options dbms_aq.remove and dbms_aq.remove_nodata using in dbms_aq.dequeue. 2. Does dbms_aq.dequeue generate more redolog log that normal delete statement. Is it advisable to hav...
Categories: DBA Blogs

connecting database in network

Tom Kyte - Thu, 2018-09-06 17:06
Hi, I have installed oracle 11g and created database name XYZ in machine A Two systems are connected in same network which is reachable by ping ip address port is also open How B machine will access the DB XYZ on A machine ?? Thanks ...
Categories: DBA Blogs

Heavy swapping in database

Tom Kyte - Thu, 2018-09-06 17:06
Hi Oracle professionals, Please advice us and give right direction. We have database on 12c on OS Red Hat Enterprise Linux Server release 6.10 with memory settings: SGA: 4928M PGA: 1250M OS memory: 8GB CPU q-ty: 2 Huge Pages disabled. I...
Categories: DBA Blogs

v$bh@oracle vs innodb-buffer-page@mysql

Tom Kyte - Thu, 2018-09-06 17:06
From https://docs.oracle.com/cd/E17952_01/mysql-5.6-en/innodb-information-schema-buffer-pool-tables.html warning that Querying the INNODB_BUFFER_PAGE or INNODB_BUFFER_PAGE_LRU table can can affect performance. From the source code i can find th...
Categories: DBA Blogs

execute command scp from inside the database

Tom Kyte - Wed, 2018-09-05 22:46
good day Tom - I am writing to you because I am having problems with a development where I need to access a linux server to extract files dynamically and place them on my windows server using the scp command supported in a java library. but it is ...
Categories: DBA Blogs

How oracle identifies the "Dirty Blocks"

Tom Kyte - Wed, 2018-09-05 22:46
Hello Oracle Masters, I have a very basic question to understand the internal behavior of oracle. I read couple of blogs and unfortunately end up in confusion; So thought of asking you .. Assume, i m updaing a block (a record) and not issued...
Categories: DBA Blogs

Regexp_Replace Help- Column Level Data Masking

Tom Kyte - Wed, 2018-09-05 04:26
Hi Tom, I have this requirement but regexp_replace is behaving odd here. It gives me all NULL Requirement for function- Suppose a number 9845-089160 ? function logic should convert one digit at a time and not entire number in one go. ? First ...
Categories: DBA Blogs

Finding partitions of two tables within the same date range

Tom Kyte - Wed, 2018-09-05 04:26
Hi Tom, How can I find partitions (PARTITION BY RANGE (DATE) INTERVAL (NUMTOYMINTERVAL(1,'MONTH')) of two tables within same date range (HIGH_VALUE) to merge those partitions? I have many tables and I am trying to create a script for it. Thanks...
Categories: DBA Blogs

Veem backup solution viability

Tom Kyte - Wed, 2018-09-05 04:26
Hi, My Windows System Administrator colleague wants to replace classical RMAN backup with a proprietary tool (VEEM). That tool apparently has some ability to take the snapshot backup of the database (see more https://www.veeam.com/blog/how-to-back...
Categories: DBA Blogs

How to check Oracle server operating system from SQL or PL/SQL?

Tom Kyte - Wed, 2018-09-05 04:26
During the installation of some application software, we build the necessary schema objects in Oracle if they don't exist. Additionally, we point out any potential problems to the user doing the install. Some of these problems vary with the Oracle se...
Categories: DBA Blogs

How to set escape for special characters inside contains block of select statement.

Tom Kyte - Wed, 2018-09-05 04:26
Scenario: I have a table called test1 with 2 columns say Id(NUMBER(10,0)), SearchData(CLOB). <code>Id SearchData 1 COLL-ETTE AKACOMMON 2 @#$!%*@#$!%*@#$!%*</code> Statement1: select * from test1 where contains (SEARCHDATA ,'\-ETTE AKACOMMO...
Categories: DBA Blogs

function that does not allow a non user to login to the application

Tom Kyte - Tue, 2018-09-04 10:06
Hello, There is a requirement for us where non users should be restricted from logging to the application in a trigger in Oracle forms which use PL SQL. This needs to be done using a new database function. I only have a developer access and not a...
Categories: DBA Blogs

ORA-22165: given index error encountered when adding a materialized view in a materialized view group

Tom Kyte - Tue, 2018-09-04 10:06
Hi, We dropped and re-created a materialized view group and would like to add it in a certain materialized view group but an ORA-22165 is encountered when trying to add the materialized view in an existing group. Can I ask how this is encountered?...
Categories: DBA Blogs

How to constrain an employees salary against his manager in the same table

Tom Kyte - Tue, 2018-09-04 10:06
I just want to create triggers for checking salary that employee salary cannot exceed manager's. there are one table: EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SALARY,DEPTNO) FK:MGR references EMPNO Thanks.
Categories: DBA Blogs

Deploying Oracle RAC 18c for Linux x86_64 on OL 7.5

Tom Kyte - Tue, 2018-09-04 10:06
Hello, teams These two days I've deployed Oracle RAC 18c on OL 7.5 (my Linux server) via VirtualBox 5.2.18. During this time I encountered some problems as follows, (1) after installing RPM "oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm",...
Categories: DBA Blogs

sql performance

Tom Kyte - Tue, 2018-09-04 10:06
Hi Tom,I have met a SQL that with the latest statistics and the execution plan is in the wrong estimate-rows ,that's to say,it's a large difference between the estimate and the actual,the sql statement and execution is below: <code>select t1.card_no...
Categories: DBA Blogs

SQLLDR with multiple when conditions

Tom Kyte - Tue, 2018-09-04 10:06
Hello, we have a requirement to load multiple data based on different conditions. The details are as below, Create: <code> create table table_a (empno number, empname varchar2(50),salary number, status varchar2(30)) / Control file: opt...
Categories: DBA Blogs

Partner Webcast – Oracle New platforms for APIs and Integration on Cloud

APIs are at the center of the digital economy and new business models are associated with them. They have become a foundational technology for the development of scalable enterprise applications and...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs