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.

2017-09-06 Wed

07:41 Oracle OpenWorld 2017 Bloggers Meetup (4474 Bytes) » Official Pythian Blog

DON’T PANIC. Yes, we are doing the OOW Bloggers Meetup this year. Yes, it’s the same time. Yes, it’s the same location. Yes, it’s more fun every year.

What: Oracle Bloggers Meetup 2017.

When: Wed, 04-Oct-2017, 5:30pm.

Where: Main Dining Room, Jillian’s Billiards @ Metreon, 175 Fourth Street, San Francisco, CA 94103 (street view). Yes, it’s a good time to try taking our blog down with DoS attack of “COUNT ME IN” comments below – if you’re coming, comment away!

As usual Pythian sponsors the venue, drinks and cool fun social stuff. The competition is on — you know what that means (new unique swag this year) and if not, come and learn. All blogger community participants are invited (self qualifying).

As usual, vintage t-shirts, ties, or bandanas from previous meetups will make you look extremely cool — feel free to wear them. The new wearable will be a nice addition to your collection!

For those of you who don’t know the history: The Bloggers Meetup during Oracle OpenWorld was started by Mark Rittman and continued by Eddie Awad. Alex Gorbachev picked up the flag in 2009 and delegated to me in 2017.

The meetups have been a great success for making new friends and catching up with the old, so let’s keep them this way! To give you an idea, here are some photos and a video of past events.

All Oracle technologies — Oracle Database, MySQL, Apps, Sun technologies, Java and ALL THE CLOUD PEEPS — come and meet like-minded “techies”. All bloggers are welcome. We’ve reached 150 attendees in the last few years — maybe another record breaking gathering this year.

If you are planning to attend, please comment here with the phrase “COUNT ME IN”. This will help us ensure we have the attendance numbers right. Please provide your blog URL with your comment — it’s a Bloggers Meetup after all! Please do make sure you comment here if you are attending so that we have enough room, food, and (most important) drinks.

And even if you choose to not attend the meetup, check out all Oracle OpenWorld sessions with Pythian speakers and book your seat in these sessions today.

And you can now fill your twitter feeds (linkedin, G+, instagram, mailing lists) by talking about how excited you are to attend, and talk about this year’s meetup with fellow bloggers you’ll meet at the show. See you all there — it will be fun!