123
 123

Tip: 看不到本站引用 Flickr 的图片? 下载 Firefox Access Flickr 插件 | AD: 订阅 DBA notes --

2017-09-07 Thu

22:57 Data Guard automation with Ansible (9522 Bytes) » Official Pythian Blog

Most DBAs record all they have learned from different projects which they are working on and have completed.
This knowledge can then be applied in new environments; new builds mostly just with a change of names, values, etc.

One example may be Data Guard configuration. It can be single instance
or RAC database, file systems or ASM, different in number of nodes at primary
and standby sites, etc., but in general, commands to create the configuration
are the same – you would need to setup proper parameters, create logs, static listeners, tns names, etc.

Building Ansible playbook/roles to manage all different conditions,
perform all checks and to vary scenarios based on existing configuration
can be time consuming and complicated.

However, it is much easier to use Ansible in local mode
with a Jinja2 template. Jinja2 is the engine for Python and with simple coding
and variables for the template output in text or html format can be built easily
to have a list of commands to run and configurations to change.

It is definitely not a full automation, but it is very helpful
to have commands ready and adopted for certain environments
and to have nothing needed to run remotely,
just everything on a control machine where Ansible is installed.
And if something is already created or configured it can be ignored or adjusted
by a DBA on the fly which is much quicker than having code managing it in an Ansible playbook.

I used the playbook recently while preparing DG configuration on 2 nodes clusters.
There are playbook and template below to generate very simple html with commands:

---
  - hosts: 127.0.0.1
    vars:
      db_home_prim: /u01/app/oracle/11.2.0.4/db1
      gi_home_prim: /u01/app/11.2.0.4/grid
      db_home_stby: /u01/app/oracle/11.2.0.4/db1
      gi_home_stby: /u01/app/11.2.0.4/grid
      db_name: db

      db_uniq_prim: db
      nodes_prim: 2
      node_pattern_prim: racnode-dc1-
      scan_prim: vbox-rac-dc1.internal.lab
      port_prim: 1521
      dg_name_prim: +DATA
      stby_log_size: 100M
      stby_logs_num: 4

      db_uniq_stby: db_s
      nodes_stby: 2
      node_pattern_stby: racnode-dc2-
      scan_stby: vbox-rac-dc2.internal.lab
      port_stby: 1521
      dg_name_stby: +DATA

    tasks:
    - template:
        src: dg_config.html.j2
        dest: ./dg_config_{{ db_name }}.html

where the contents of dg_config.html.j2 are:

Removal of configuration

 

removal of configuration on primary

ssh oracle@{{ node_pattern_prim }}1 . oraenv <<< {{ db_name }}1 dgmgrl / DISABLE CONFIGURATION; REMOVE CONFIGURATION; sqlplus / as sysdba {% if nodes_prim > 1 %} alter system set dg_broker_start = false sid = ‘*’; alter system set fal_server = ” sid = ‘*’; alter system set fal_client = ” sid = ‘*’; alter system set log_archive_config = ” sid = ‘*’; {% elif nodes_prim == 1 %} alter system set dg_broker_start = false; alter system set fal_server = ”; alter system set fal_client = ”; alter system set log_archive_config = ”; {% endif %} begin for c in (select group# from v$standby_log) loop execute immediate ‘alter database drop standby logfile group ‘||c.group#; end loop; end; /

removal of configuration on standby

ssh oracle@{{ node_pattern_stby }}1 . oraenv <<< {{ db_name }}1 dgmgrl / DISABLE CONFIGURATION; REMOVE CONFIGURATION; sqlplus / as sysdba {% if nodes_stby > 1 %} alter system set dg_broker_start = false sid = ‘*’; alter system set fal_server = ” sid = ‘*’; alter system set fal_client = ” sid = ‘*’; alter system set log_archive_config = ” sid = ‘*’; {% elif nodes_stby == 1 %} alter system set dg_broker_start = false; alter system set fal_server = ”; alter system set fal_client = ”; alter system set log_archive_config = ”; {% endif %} begin for c in (select group# from v$standby_log) loop execute immediate ‘alter database drop standby logfile group ‘||c.group#; end loop; end; /

Creation of configuration

 

changes on nodes of primary

{% for i in range(nodes_prim) %} # configuration of static listener for primary on {{ node_pattern_prim }}{{ loop.index }} ssh oracle@{{ node_pattern_prim }}{{ loop.index }} . oraenv <<< +ASM{{ loop.index }} cd $ORACLE_HOME/network/admin vi listener.ora SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = {{ db_uniq_prim }}_DGMGRL) (ORACLE_HOME = {{ db_home_prim }}) (SID_NAME = {{ db_uniq_prim }}{{ loop.index }}) ) ) . oraenv <<< {{ db_name }}{{ loop.index }} cd $ORACLE_HOME/network/admin vi tnsnames.ora {{ db_uniq_prim.upper() }} = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = {{ scan_prim }})(PORT = {{ port_prim }})) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = {{ db_uniq_prim }}) ) ) {{ db_uniq_stby.upper() }} = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = {{ scan_stby }})(PORT = {{ port_stby }})) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = {{ db_uniq_stby }}) ) ) {% endfor %}

changes on nodes of standby

{% for i in range(nodes_stby) %} # configuration of static listener for standby on {{ node_pattern_stby }}{{ loop.index }} ssh oracle@{{ node_pattern_stby }}{{ loop.index }} . oraenv <<< +ASM{{ loop.index }} cd $ORACLE_HOME/network/admin vi listener.ora SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = {{ db_uniq_stby }}_DGMGRL) (ORACLE_HOME = {{ db_home_stby }}) (SID_NAME = {{ db_uniq_stby }}{{ loop.index }}) ) ) . oraenv <<< {{ db_name }}{{ loop.index }} cd $ORACLE_HOME/network/admin vi tnsnames.ora {{ db_uniq_prim.upper() }} = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = {{ scan_prim }})(PORT = {{ port_prim }})) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = {{ db_uniq_prim }}) ) ) {{ db_uniq_stby.upper() }} = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = {{ scan_stby }})(PORT = {{ port_stby }})) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = {{ db_uniq_stby }}) ) ) {% endfor %}

changes in primary database

ssh oracle@{{ node_pattern_prim }}1 . oraenv <<< {{ db_name }}1 sqlplus / as sysdba {% if nodes_prim > 1 %} alter system set dg_broker_config_file1 = ‘{{ dg_name_prim }}/{{ db_uniq_prim }}/dr1{{ db_uniq_prim}}.dat’ sid=’*’; alter system set dg_broker_config_file2 = ‘{{ dg_name_prim }}/{{ db_uniq_prim }}/dr2{{ db_uniq_prim}}.dat’ sid=’*’; {% elif nodes_prim == 1 %} alter system set dg_broker_config_file1 = ‘{{ dg_name_prim }}/{{ db_uniq_prim }}/dr1{{ db_uniq_prim}}.dat’; alter system set dg_broker_config_file2 = ‘{{ dg_name_prim }}/{{ db_uniq_prim }}/dr2{{ db_uniq_prim}}.dat’; {% endif %} {% for i in range(nodes_prim) %} {% set rowloop = loop %} {% for j in range(stby_logs_num) %} {% if nodes_prim > 1 %} alter database add standby logfile thread {{ rowloop.index }} ‘{{ dg_name_prim }}’ size {{ stby_log_size }}; {% elif nodes_prim == 1 %} alter database add standby logfile ‘{{ dg_name_prim }}’ size {{ stby_log_size }}; {% endif %} {% endfor %} {% endfor %} alter system set dg_broker_start = true sid=’*’;

changes in standby database

ssh oracle@{{ node_pattern_stby }}1 . oraenv <<< {{ db_name }}1 sqlplus / as sysdba {% if nodes_prim > 1 %} alter system set dg_broker_config_file1 = ‘{{ dg_name_stby }}/{{ db_uniq_stby }}/dr1{{ db_uniq_stby}}.dat’ sid=’*’; alter system set dg_broker_config_file2 = ‘{{ dg_name_stby }}/{{ db_uniq_stby }}/dr2{{ db_uniq_stby}}.dat’ sid=’*’; {% elif nodes_prim == 1 %} alter system set dg_broker_config_file1 = ‘{{ dg_name_stby }}/{{ db_uniq_stby }}/dr1{{ db_uniq_stby}}.dat’; alter system set dg_broker_config_file2 = ‘{{ dg_name_stby }}/{{ db_uniq_stby }}/dr2{{ db_uniq_stby}}.dat’; {% endif %} {% for i in range(nodes_stby) %} {% set rowloop = loop %} {% for j in range(stby_logs_num) %} {% if nodes_stby > 1 %} alter database add standby logfile thread {{ rowloop.index }} ‘{{ dg_name_stby }}’ size {{ stby_log_size }}; {% elif nodes_stby == 1 %} alter database add standby logfile ‘{{ dg_name_stby }}’ size {{ stby_log_size }}; {% endif %} {% endfor %} {% endfor %} alter system set dg_broker_start = true sid=’*’;

creation of DG broker configuration

ssh oracle@{{ node_pattern_prim }}1 . oraenv <<< {{ db_name }}1 dgmgrl / CREATE CONFIGURATION dg_{{ db_name }} AS PRIMARY DATABASE IS {{ db_uniq_prim }} CONNECT IDENTIFIER IS {{ db_uniq_prim }}; ADD DATABASE {{ db_uniq_stby }} AS CONNECT IDENTIFIER IS {{ db_uniq_stby }} MAINTAINED AS PHYSICAL; ENABLE CONFIGURATION; SHOW CONFIGURATION VERBOSE; SHOW DATABASE VERBOSE {{ db_uniq_prim }}; SHOW DATABASE VERBOSE {{ db_uniq_stby }};

Nothing fancy but the playbook generates an html output with simple commands
to create and adjust a Data Guard configuration.