Feed aggregator

Oracle Unified Auditing Performance Issues and 12.2 Improvements

For those of you using and/or considering Unified Auditing, in case you might have missed, Oracle has made significant changes to Unified Auditing in 12.2. Unified Auditing, new in Oracle 12c, represents a complete rewrite of how native database auditing works - see the links below for Integrigy research on Unified Auditing.

With Oracle 12.1, when using Unified Auditing, reads of the UNIFIED_AUDIT_TRAIL view were not performant. With Oracle 12.2, a new relational partitioned table (AUDSYS.AUD$UNIFIED) is created to solve the performance issue, and a patch (22782757) has been issued to backport the fix to 12.1.

For 12.1 clients using Unified Auditing, the patch and/or the workaround should be a high priority consideration.

Thank you to Mark Dietrich for pointing out the 12.1 patch.

If you have any questions, please contact us at info@integrigy.com

-Michael Miller, CISSP-ISSMP, CCSP, CCSK

References
 
 
 
 
 
 
Auditing, Oracle Database
Categories: APPS Blogs, Security Blogs

Does the wal segment size matter in PostgreSQL?

Yann Neuhaus - Tue, 2017-04-25 02:39

In PostgreSQL you configure the size of the wal (write ahead log) segments when you compile from source. If you use an installer or if you use the packages provided by your OS distribution the size of the wal segments is usually 16MB. Although 16MB seems very low you don’t need to worry about that in most of the cases, it just works fine. However there are cases where you might want to adjust this, e.g. when you have an application that generates thousands of transactions in a very short time and therefore forces PostgreSQL to generate huge amounts of wal segments. In this post we’ll look at a specific case: Usually you want to archive the wal segments for being able to do point in time recovery in case your severs crashes for some reason. Does the size of the wal segments matter for archiving?

Archiving of wal segments in PostgreSQL is done by specifying an archive_command. Whatever you put there will be executed by PostgreSQL once a new wal segment is created. Usually you’ll find something like this in archive_command (from the documentation):

archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'  # Unix
archive_command = 'copy "%p" "C:\\server\\archivedir\\%f"'  # Windows

Or something like this:

archive_command = 'rsync -a %p postgres@[SOME_OTHER_HOST]:/path/to/wal_archive/%f'

Or:

archive_command ='scp %p postgres@[SOME_OTHER_HOST]:/path/to/wal_archive/%f'

Lets test how the size of wal segments impact the three ways of archiving outlined above. To begin with lets create 100 files each 16MB (the same as the default wal segment size in PostgreSQL) and 25 files 64MB each:

rm -rf /var/tmp/test16mb
mkdir /var/tmp/test16mb
for i in {1..100}; do
   dd if=/dev/zero of=/var/tmp/test16mb/${i} bs=1M count=16
done
ls -la /var/tmp/test16mb
rm -rf /var/tmp/test64mb
mkdir /var/tmp/test64mb
for i in {1..25}; do
   dd if=/dev/zero of=/var/tmp/test64mb/${i} bs=1M count=64
done
ls -la /var/tmp/test64mb
du -sh /var/tmp/test16mb
du -sh /var/tmp/test64mb

This will give us a total size of 1.6GB for each of the wal sizes (16MB and 64MB). Lets start by testing the “cp” way:

echo 3 > /proc/sys/vm/drop_caches
mkdir -p /var/tmp/target
rm -rf /var/tmp/target/*
time for i in `ls /var/tmp/test16mb`; do
    cp /var/tmp/test16mb/${i} /var/tmp/target/
done

My result (on a VM local on my notebook):

real	0m17.444s
user	0m0.275s
sys	0m8.569s

The same test for the 64MB files:

echo 3 > /proc/sys/vm/drop_caches
mkdir -p /var/tmp/target
rm -rf /var/tmp/target/*
time for i in `ls /var/tmp/test64mb`; do
    cp /var/tmp/test16mb/${i} /var/tmp/target/
done

It is almost 3 times as fast to copy the large files than to copy the smaller files:

real	0m5.365s
user	0m0.065s
sys	0m1.835s

Of course, for production systems, you would copy the files not locally but rather to e.g. NFS mount and then the numbers will change.

What are the numbers for scp? For the smaller files:

echo 3 > /proc/sys/vm/drop_caches
mkdir -p /var/tmp/target
rm -rf /var/tmp/target/*
time for i in `ls /var/tmp/test16mb`; do
    scp /var/tmp/test16mb/${i} root@localhost:/var/tmp/target/
done

The result:

real	2m51.708s
user	0m14.136s
sys	0m35.292s

Quite a huge overhead. What is the result with the 64MB files?:

echo 3 > /proc/sys/vm/drop_caches
mkdir -p /var/tmp/target
rm -rf /var/tmp/target/*
time for i in `ls /var/tmp/test64mb`; do
    scp /var/tmp/test64mb/${i} root@localhost:/var/tmp/target/
done

Approximately double as fast:

real	1m23.326s
user	0m10.353s
sys	0m30.814s

And finally rsync, for the smaller files:

echo 3 > /proc/sys/vm/drop_caches
mkdir -p /var/tmp/target
rm -rf /var/tmp/target/*
time for i in `ls /var/tmp/test16mb`; do
    rsync -a /var/tmp/test16mb/${i} root@localhost:/var/tmp/target/${i}
done

The result:

real	0m51.624s
user	0m4.488s
sys	0m10.247s

For the larger ones:

echo 3 > /proc/sys/vm/drop_caches
mkdir -p /var/tmp/target
rm -rf /var/tmp/target/*
time for i in `ls /var/tmp/test64mb`; do
    rsync -a /var/tmp/test64mb/${i} root@localhost:/var/tmp/target/${i}
done

The result:

real	0m34.342s
user	0m3.623s
sys	0m9.685s

Conclusion: When you have applications with high transaction rates it can make sense to increase the default wal segment size as archiving will usually be much faster when you use bigger segments. Of course you’ll need to test this on your specific hardware and for your specific workload. In a next post we’ll look at how bigger segments affect performance of PostgreSQL.

 

Cet article Does the wal segment size matter in PostgreSQL? est apparu en premier sur Blog dbi services.

Accepting SQL profiles

Tom Kyte - Mon, 2017-04-24 22:46
Hi - we are doing some data conversion of our database associated with a vendor product. This means migrating from one version of the vendor schema to another. so remapping the data. During a performance run, one of the SQLs was taking longer to run....
Categories: DBA Blogs

Correctly identifying Dynamic Sampling queries run by Optimizer

Tom Kyte - Mon, 2017-04-24 22:46
It is clear that dynamic sampling queries run by the optimizer contains <b>/* DS_SVC */ </b>clause in them (when traced). e.g. SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel...
Categories: DBA Blogs

Pivot and null values

Tom Kyte - Mon, 2017-04-24 22:46
Hi team, I have a table test having column as name,id,language.... <code>name id language a 1 eng b 2 eng c 3 fer d 4 (null) select * from TEST pivot (min(id) for language in('eng' as "english",'fer' as "french",)) </code> ...
Categories: DBA Blogs

RANGE Partition in DATE column DD/MM/YYYY HH24:MM:SS

Tom Kyte - Mon, 2017-04-24 22:46
Hi team, I need to partition TEST_PARTITIONS table on the basis of end_date using RAGE INTERVAL partition. <code>create table TEST_PARTITIONS partition by range(end_date) ( partition p2010 values less than (to_date('01-Jan-2011','dd-mon-yyy...
Categories: DBA Blogs

Passing Business Object Values to Custom UI Components in ABCS

Shay Shmeltzer - Mon, 2017-04-24 17:29

This quick one is based on a customer question about Oracle Application Builder Cloud Service. The scenario is that we have a business object that has a field that contains the URL to an image. We want to be able to show that image on a page in Oracle Application Builder Cloud Service.

animated GIF

To do that I add a custom UI component object to the details (or edit) page of a record - then I switched the HTML of that object to be: <img id="logoimg"/>

custom code

I then added a button to the page and added a bit of custom JavaScript code in its action as follow:

var img = document.getElementById('logoimg');

img.src=$Company.getValue('Logo');

resolve();

This code simply locates the custom object on the page using the object id and then sets the src property of the img html tag to match the value of the field in the business object.

Code in Button

Categories: Development

Custom UI Components in Oracle ABCS for Dynamic Image Display

Shay Shmeltzer - Mon, 2017-04-24 17:29

This quick one is based on a customer question about Oracle Application Builder Cloud Service. The scenario is that we have a business object that has a field that contains the URL to an image. We want to be able to show that image on a page in Oracle Application Builder Cloud Service.

animated GIF

To do that I add a custom UI component object to the details (or edit) page of a record - then I switched the HTML of that object to be: <img id="logoimg"/>

custom code

I then added a button to the page and added a bit of custom JavaScript code in its action as follow:

var img = document.getElementById('logoimg');

img.src=$Company.getValue('Logo');

resolve();

This code simply locates the custom object on the page using the object id and then sets the src property of the img html tag to match the value of the field in the business object.

Code in Button

Categories: Development

Benefits of Analytics for Non-Profit Organizations

Nilesh Jethwa - Mon, 2017-04-24 14:49

Analytics is a process that uses tools to collect those ever-increasing volumes of diverse types of data from multiple sources, sort them out at record speeds, analyze them, and use them to gain new insights. This concept has existed for decades, and it has been recreated with modern and more powerful tools to consolidate today's data upsurge.

Non-profits run the extent in terms of using analytics. Some are only getting started, utilizing business intelligence and dashboard software for their budgeting and forecasting procedures, while others have gone far along the continuum and are considering effective ways to get more unstructured data to further enhance their existing analytics models. 
 

Read more at http://www.infocaptor.com/dashboard/non-profit-dashboards-benefits-of-analytics-for-non-profit

ADF BC Groovy Expression Security Policy Configuration

Andrejus Baranovski - Mon, 2017-04-24 13:23
Today I'm going to explain how to configure Groovy expression security policy. This could be helpful, if you dont want to change trustMode property to trusted everywhere across the app, but looking for single configuration point.

My sample app - GroovyPermissionApp.zip, contains bind variable with expression reference pointing towards custom method located in AM implementation class:


JDEV 12.2.1.2 returns compilation error for Groovy expression, can't resolve applicationModule property:


Such kind of checks can be disabled in Model project configuration. Uncheck option for Groovy Expression Type Validation:


JDEV 12.2.1.2 by default creates Groovy expressions in untrusted mode. If you change it to trusted, expression would work OK. However, if your app contains many expressions like this, you may want to ignore trustMode property:


If you run ADF BC tester in JDEV 12.2.1.2, it will show error text in the log for untrusted expression. ADF BC tester will fail to start, if there is any error - we logged this issue with Oracle Support. Property applicationModule can't be resolved, when trustMode is set to untrusted:


To disable this check, we can create new class extending ExprSecurityPolicy class. Override checkProperty method to allow calls to applicationModule property:


This class should be registered in adf-config.xml:


Try to run ADF BC tester again. Error about applicationModule will be gone. This time it will complain about permission error to call getCurrentRegion method:


Method access can be granted by annotation in AM implementation class:


ADF BC tester runs, and returns VO row data:

Java Web Start Now Available for EBS 12.1 and 12.2

Steven Chan - Mon, 2017-04-24 12:20

Java Web Start (JWS) is now available for Oracle E-Business Suite 12.1 and 12.2:

What is Java Web Start?

Java Web Start launches E-Business Suite Java-based functionality as Java Web Start applications instead of as applets.  Java Web Start is part of the Java Runtime Environment (JRE).

Does EBS use Java on desktop clients?

Yes.  The E-Business Suite requires Oracle Forms.  Oracle Forms requires Java. 

Other EBS products also have functionality that require Java.

What is the new approach with Java Web Start?

It's not technically "new" (it is a mature Java technology originally released in 2004), but we're using it for the first time with the E-Business Suite.  This approach launches EBS Forms-based screens and other functionality as Java Web Start applications instead of as applets.

What prerequisites are needed for Java Web Start?

 Oracle E-Business Suite Release  Minimum JRE Release  12.2  JRE 8 Update 121 b33  12.1.3  JRE 8 Update 121 b33

A small number of server-side patches for Forms and EBS are needed. See:

Why is this important?

Until now, E-Business Suite's Java-based content required a browser that supports Netscape Plug-in Application Programming Interface (NPAPI) plug-ins.

Some browsers are phasing out NPAPI plug-in support.  Some browsers were released without NPAPI plug-in support.  This prevents the Java plug-in from working.

With the release of Java Web Start, E-Business Suite 12.1 and 12.2 users can launch Java-based content (e.g. Oracle Forms) from browsers that do not support Java plug-ins via NPAPI.  Java Web Start in EBS works with:

  • Microsoft Internet Explorer
  • Microsoft Edge
  • Firefox Rapid Release (32-bit and 64-bit)
  • Firefox Extended Support Release (32-bit and 64-bit)
  • Google Chrome

How does the technology architecture change?

Java Web Start changes the way that Java runs on end-users' computers but this technical change is generally invisible to end-users.

Java Web Start applications are launched from browsers using the Java Network Launching Protocol (JNLP).

E-Business Suite Java Web Start architecture diagram

Will the end-user's experience change?

Generally not. We have worked hard to ensure that your end-users' experience with Java Web Start applications is as similar as possible to applets via the Java browser plugin.  The differences between the Java Plug-in and Java Web Start are expected to be almost-invisible to end-users.

Will E-Business Suite still require Java in the future?

Yes.  It is expected that our ongoing use of Oracle Forms for high-volume professional users of the E-Business Suite means that EBS will continue to require Java.  We replicate, simplify, or migrate selected Forms-based flows to OA Framework-based (i.e. web-based HTML) equivalents with every EBS update, but Oracle Forms is expected to continue to be part of the E-Business Suite technology stack for the foreseeable future. 

Does the E-Business Suite have other Java applet dependencies?

Yes.  In addition to Oracle Forms, various E-Business Suite products have functionality that runs as Java applets.  These Java applets require browsers that offer plugin support.  These products include applets:

  • Oracle General Ledger (GL): Account Hierarchy Manager
  • Oracle Customers Online (IMC): Party Relationships
  • Oracle Call Center Technology (CCT)
  • Oracle Sourcing (PON): Auction Monitor
  • Oracle Installed Base (CSI): Visualizer
  • Oracle Process Manufacturing (OPM): Recipe Designer
  • Oracle Advanced Supply Chain Planning (MSC): Plan Editor (PS/SNO)
  • Workflow (WF): Status Diagram, Notification Signing with Digital Signatures
  • Scripting (IES): Script Author

What is the roadmap for browser support for plug-ins?

Plug-in support has various names, including:

This article will simply use the term "plug-in support," which refers to all of the different types listed above.

Some browsers are phasing out plug-in support. Some browsers were never released with plug-in support.

Some organizations may wish to use browsers that do not offer plugin support.  The Java Web Start approach works with all browsers, regardless of whether they have plugin support. 

What is the roadmap for Java's support for plug-ins?

The Java team recently published their plans
for removing the Java browser plugin in a future version of Java. The announcement states (highlighted for emphasis):

Oracle plans to deprecate the Java browser plugin in JDK 9. This technology will be removed from the Oracle JDK and JRE in a future Java SE release.

What does "deprecate" mean?

In this context, "deprecate" means there will still be a Java Plug-in in JRE 9.

In other words, JRE 9 will include the Java Browser Plug-in and Java Web Start.  Users will still be able to run Java-based applications using the Java Plug-in and Java Web Start in JRE 9.

What does this mean for E-Business Suite users running the Java Plug-in with JRE 9?

The release of Java 9 is not expected to affect E-Business Suite users.

JRE 9 is expected to continue to work with the E-Business Suite in browsers that support the Java Browser Plug-in via the NPAPI protocol.

JRE 9 is expected to work with the E-Business Suite in browsers that support Java Web Start.

What browsers are expected to support the JRE 9 plug-in?

Internet Explorer, Firefox ESR 32-bit, and Safari are expected continue to support NPAPI -- and, therefore, Java and Forms. 

Firefox Rapid Release, Firefox ESR 64-bit, Google Chrome, and Microsoft Edge do not support NPAPI, so Java-based apps cannot run in those browsers using the Java Plug-in.  EBS users can run Java-based content using Java Web Start with JRE 9.

What are the timelines for browsers' plugin support?

Individual browser vendors have been updating their plans regularly.  Here's a snapshot of what some browser vendors have stated as of today:

Microsoft Internet Explorer (IE)

Microsoft has indicated that they intend to continue to offer plug-in support in IE.

Microsoft Edge

Microsoft Edge was released in Windows 10 without Browser Helper Object (BHO, aka. plugin) support.  Microsoft has no plans to add plugin support to Edge.

Mozilla Firefox Extended Support Release (ESR)

Mozilla indicated in early 2016 that Firefox ESR 52 32-bit will be the last version to offer NPAPI (and JRE) support.  Firefox ESR 52 32-bit was released in March 2017 and will be supported until May 2018. 

Mozilla removed NPAPI support from Firefox ESR 52 64-bit in March 2017.  

Mozilla Firefox Rapid Release

Mozilla removed NPAPI support from the Firefox 52 Rapid Release version in March 2017. 

Apple Safari for macOS

Safari offers Internet plug-in support for macOS users.  Apple has not made any statements about deprecating plugin support for macOS users.

Google Chrome for Windows

Chrome offered support for plugins until version 45, released in September 2015.  They removed NPAPI support in later Chrome releases.

Will I need to change browsers for EBS 12.1 or 12.2?

Not generally, but it depends on your choice of browsers and whether you wish to use Java Plug-in or Java Web Start.

Here's the compatibility matrix for EBS 12.1 and 12.2 certified combinations:

   Java Plug-In  Java Web Start  Microsoft Internet Explorer  Yes  Yes  Microsoft Edge    Yes  Firefox Rapid Release 32-bit    See Note 1  Firefox Rapid Release 64-bit    See Note 1  Firefox Extended Support Release 32-bit  Yes  Yes  Firefox Extended Support Release 64-bit    Yes  Google Chrome    Yes  Safari on macOS  Yes  See Note 2

Note 1: Expected to work but not tested.

New personal versions of Firefox on the Rapid Release channel are released roughly every six weeks.  It is impractical for us to certify these new personal Rapid Release versions of Firefox with the Oracle E-Business Suite because a given Firefox release is generally obsolete by the time we complete the certification.

From Firefox 10 and onwards, Oracle E-Business Suite is certified only with selected Firefox Extended Support Release versions. Oracle has no current plans to certify new Firefox personal releases on the Rapid Release channel with the E-Business Suite.

Note 2: Not certified.

Apple changed the Gatekeeper permissions in macOS Sierra 10.12.  These changes prevent JNLP execution, making the Java Web Start user experience very challenging.  We are investigating options right now. 

Will Oracle release its own browser for the E-Business Suite?

No.  Long-time Oracle users may remember the Oracle PowerBrowser. The industry has since moved away from software that requires proprietary browsers.  We have no plans to release a browser specifically for E-Business Suite users. 

Will this work on Android or iOS?

No. Neither of these operating systems are compatible with Java. 

E-Business Suite users who need to run Oracle Forms-based content or other Java-based functionality should use Windows or macOS.

Will Java Web Start be mandatory?

Not immediately. It is expected that the use of Java Web Start will be optional at least up to, and including, Java 9, which may be the last Java release to include the JRE browser plugin. 

Will Java Web Start coexist with JRE?

Yes.  You can have a mixed environment where some end-users launch Java Web Start applications, while others use applets via the Java plug-in.  This mixed group of end-users can connect to the same E-Business Suite environment.

EBS system administrators have full server-side control over these choices.

Will this affect EBS customizations?

Maybe. It depends upon which of the following apply to your environment:

  • Scenario 1You have modified standard EBS screens running in Forms: 
    No actions needed. These customizations are expected to work with Java Web Start without any additional changes.
  • Scenario 2You have built custom Java applets of your own to extend the E-Business Suite:  These will continue to run with the Java plug-in, but you may wish to update those applets to use Java Web Start.
  • Scenario 3You have third-party extensions or products that depend upon the Java plug-in:
    These will continue to run with the Java plug-in but you may wish to contact your third-party vendor for details about their plans for Java Web Start.

Are there any additional licensing costs?

No. Java Web Start is included with EBS licenses and does not introduce any new licensing costs.

Related Articles

Disclaimer

The preceding is intended to outline our general product direction.  It is intended for information purposes only, and may not be incorporated into any contract.   It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decision.  The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.

 

Categories: APPS Blogs

Simple Steps to Perform oPatch Maintenance with Ansible

Pythian Group - Mon, 2017-04-24 10:44

Like the Universe, IT growth seems to be infinite, we always have more environments, more servers, more users, more disk usage, more databases to manage and it won’t stop. In fact, we are pretty sure that this expansion is going to be faster and faster.

We then have to adapt to this new, mutating IT environment being more productive in order to manage more and more targets in less time.

How to achieve this goal? Like human beings have always done from the early days – by using tools and by making better tools with the tools we have.

1/ The Ansible Tool

 

1.1/ A Word on Ansible

Ansible is an open source IT automation tool that was launched in early 2013 and bought by Red Hat in 2015. The most recent 2.3 version was released few days ago.

1.2/ Why Ansible?

Other the automation tools are professed to be easy, fast, able to manage thousands of thousands of targets, etc… so why Ansible instead of Puppet or Chef? For me, it’d because Ansible is agentless and does everything through standard SSH (or Paramiko which is a Python SSH implementation).

Indeed, ‘no agent’ really means easy to deploy, no agent to maintain (!), and it is very secure since it uses SSH. I am accustomed to working with companies that have tough security processes and challenging processes for any kind of installations. Be sure that it is easier to quickly deploy everything with these features:

  • Is it secure? Yes, it goes through SSH.
  • Anything to install on the targets? No.
  • Do you need root access? No, as long as what I need to do is doable with no root privilege.
  • Can it go through sudo? Yes, no worries.
  • What do you need then? An SSH key deployed on the targets (which also means that it is very easy to unsetup, you just have to remove that SSH key from the target)

For more information on the differences between Ansible, Puppet and Chef, just perform an online search.  You will find many in-depth comparatives.

2/ Manage oPatch with Ansible

To illustrate how quick and easy it is to use Ansible, I will demonstrate how to update oPatch with Ansible. oPatch is a very good candidate for Ansible as it needs to be frequently updated, exists in every Oracle home and also needs to be current every time you apply a patch (and for those who read my previous blogs, you know that I like to update opatch :))

2.1/ Install Ansible

The best way to install Ansible is to first refer to the official installation documentation .  There you will find the specific commands for your favorite platform (note that Ansible is not designed for Windows).

2.2/ Configure Ansible

To start, Ansible has to know the hosts you want to manage in a “host” file like:

oracle@control:~/work$ cat hosts_dev
[loadbalancer]
lb01

[database]
db01
db02 ansible_host=192.168.135.101
oracle@control:~/work$

We can split the hosts by group like [loadbalancer], [database] to have various hosts group. It is also possible that the host you are running Ansible on cannot resolve a host. We can then use the ansible_host parameter to specify the IP for it like I did for the db02 server. In fact, ansible_host defines the host Ansible will connect to and the name at the start of the line is an alias used if ansible_host is not defined

Note that I named the hosts file “hosts_dev” in my example. This was done so I would not use the default ansible hosts file which make it more modular. We then have to tell Ansible that we want to use this file instead of the default file in the ansible.cfg configuration file.

oracle@control:~/work$ cat ansible.cfg
[defaults]
inventory=./hosts_dev
oracle@control:~/work$

Please remember that Ansible uses SSH connectivity so you’ll need to exchange the SSH key of your “control” server to your targets. More extensive documentation on the subject can be found online. Here is an example with ssh-copy-id (if you don’t know the target user password, conduct a Google search for authorized_keys and you will find how to exchange an SSH key when you don’t know the target user password):

  oracle@control:~$ ssh-keygen                          # This will generate your SSH keys

  ... press ENTER at all prompts) ...

  oracle@control:~$ ssh-copy-id oracle@db01
  ...
  Are you sure you want to continue connecting (yes/no)? yes
  ...
  oracle@db01's password:                             # You will be prompted for the target password once
  ...
  Now try logging into the machine, with:   "ssh 'oracle@db01'"
  and check to make sure that only the key(s) you wanted were added.

  oracle@control:~$ ssh ansible@db01                   # Try to connect now
  Welcome to Ubuntu 14.04.5 LTS (GNU/Linux 3.13.0-112-generic x86_64)
  Last login: Thu Apr 20 02:17:24 2017 from control
  oracle@db01:~$                                       # We are now connected with no password

 

2.3/ A First Playbook

A playbook is a collection of Ansible commands that are used to orchestrate what you want to do. Ansible uses the YAML language (please have a look at the official YAML website) for this purpose.

Let’s start with a first easy playbook that checks if the /etc/oratab file exists on my [database] hosts:

oracle@control:~/work$ cat upgrade_opatch.yml
---
- hosts: database                              # Specify only the hosts contained in the [database] group
  tasks:
  - name: Check if /etc/oratab exists          # A name for the task
    stat:                                      # I will use the stat module to check if /etc/oratab exists
      path: /etc/oratab                        # The file or directory I want to check the presence
    register: oratab                           # Put the return code in a variable named "oratab"

  - debug:                                     # A debug task to show an error message if oratab does not exist
      msg: "/etc/oratab does not exists"       # The debug message
    when: oratab.stat.exists == false          # The message is printed only when the /etc/oratab file does not exist

oracle@control:~/work$

Let’s run it now (we use ansible-playbook to run a playbook):

oracle@control:~/work$ ansible-playbook upgrade_opatch.yml

PLAY [database] ***************************************************************************************************************************************************************************************************

TASK [Gathering Facts] ********************************************************************************************************************************************************************************************
ok: [db02]
ok: [db01]

TASK [Check if /etc/oratab exists] ********************************************************************************************************************************************************************************
ok: [db02]
ok: [db01]

TASK [debug] ******************************************************************************************************************************************************************************************************
skipping: [db01]
ok: [db02] => {
    "changed": false,
    "msg": "/etc/oratab does not exists"
}

PLAY RECAP ********************************************************************************************************************************************************************************************************
db01                       : ok=2    changed=0    unreachable=0    failed=0
db02                       : ok=3    changed=0    unreachable=0    failed=0

oracle@control:~/work$

Since I removed /etc/oratab from db02 on purpose, I received the “/etc/oratab does not exists” error message (as expected).

Before going further, let’s add a test to see if unzip exists (we’ll need unzip to unzip the opatch zipfile). Put the db02’s oratab file back where it should be and run the playbook again:

  oracle@control:~/work$ cat upgrade_opatch.yml
  ---
  - hosts: database
    tasks:
    - name: Check if /etc/oratab exists
      stat:
        path: /etc/oratab
      register: oratab

    - debug:
        msg: "/etc/oratab does not exists"
      when: oratab.stat.exists == false

    - name: Check if unzip exists (if not we wont be able to unzip the opatch zipfile)
      shell: "command -v unzip"
      register: unzip_exists

    - debug:
        msg: "unzip cannot be found"
      when: unzip_exists == false
  oracle@control:~/work$ ansible-playbook upgrade_opatch.yml

  PLAY [database] ***************************************************************************************************************************************************************************************************

  TASK [Gathering Facts] ********************************************************************************************************************************************************************************************
  ok: [db02]
  ok: [db01]

  TASK [Check if /etc/oratab exists] ********************************************************************************************************************************************************************************
  ok: [db01]
  ok: [db02]

  TASK [debug] ******************************************************************************************************************************************************************************************************
  skipping: [db01]
  skipping: [db02]

  TASK [Check if unzip exists (if not we wont be able to unzip the opatch zipfile)] *********************************************************************************************************************************
  changed: [db02]
  changed: [db01]

  TASK [debug] ******************************************************************************************************************************************************************************************************
  skipping: [db01]
  skipping: [db02]

  PLAY RECAP ********************************************************************************************************************************************************************************************************
  db01                       : ok=3    changed=1    unreachable=0    failed=0
  db02                       : ok=3    changed=1    unreachable=0    failed=0

  oracle@control:~/work$

Please note that I used the shell built-in module to test if unzip is present or not.

2.4/ Upgrade oPatch

To upgrade oPatch, we need to copy the zipfile to the target Oracle home and then unzip it — easy and straightforward. Let’s ask Ansible to do it for us.

First, let’s use the copy module to copy the oPatch zipfile to the target Oracle home:

- name: Copy the opatch zipfile to the target oracle home
   copy:
     src: p6880880_112000_Linux-x86-64.zip
     dest: /u01/oracle/11204

Unzip the zipfile in the target Oracle home (I use the shell module to unzip instead of the unarchive module on purpose. This will trigger a warning during the playbook execution, but I am not a big fan of the unarchive module… we could discuss that later on):

  - name: Upgrade opatch
    shell: unzip -o /u01/oracle/11204/p6880880_112000_Linux-x86-64.zip -d /u01/oracle/11204
    register: unzip
    failed_when: unzip.rc != 0

Let’s cleanup the zipfile we copied earlier using the file module (note that this is the keyword state: absent which will remove the file), we do not want to leave any leftovers:

  - name: Cleanup the zipfile from the target home
    file:
      name: /u01/oracle/11204/p6880880_112000_Linux-x86-64.zip
      state: absent

Now review the whole playbook:

  oracle@control:~/work$ cat upgrade_opatch.yml
---
- hosts: database
  tasks:
  - name: Check if /etc/oratab exists
    stat:
      path: /etc/oratab
    register: oratab

  - debug:
      msg: "/etc/oratab does not exists"
    when: oratab.stat.exists == false

  - name: Check if unzip exists (if not we wont be able to unzip the opatch zipfile)
    shell: "command -v unzip"
    register: unzip_exists

  - debug:
      msg: "unzip cannot be found"
    when: unzip_exists == false

  - name: Copy the opatch zipfile to the target oracle home
    copy:
      src: p6880880_112000_Linux-x86-64.zip
      dest: /u01/oracle/11204

  - name: Upgrade opatch
    shell: unzip -o /u01/oracle/11204/p6880880_112000_Linux-x86-64.zip -d /u01/oracle/11204
    register: unzip
    failed_when: unzip.rc != 0

  - name: Cleanup the zipfile from the target home
    file:
      name: /u01/oracle/11204/p6880880_112000_Linux-x86-64.zip
      state: absent

oracle@control:~/work$

and execute it:

oracle@control:~/work$ ansible-playbook upgrade_opatch.yml

PLAY [database] ***************************************************************************************************************************************************************************************************

TASK [Gathering Facts] ********************************************************************************************************************************************************************************************
ok: [db02]
ok: [db01]

TASK [Check if /etc/oratab exists] ********************************************************************************************************************************************************************************
ok: [db01]
ok: [db02]

TASK [debug] ******************************************************************************************************************************************************************************************************
skipping: [db01]
skipping: [db02]

TASK [Check if unzip exists (if not we wont be able to unzip the opatch zipfile)] *********************************************************************************************************************************
changed: [db02]
changed: [db01]

TASK [debug] ******************************************************************************************************************************************************************************************************
skipping: [db01]
skipping: [db02]

TASK [Copy the opatch zipfile to the target oracle home] **********************************************************************************************************************************************************
changed: [db01]
changed: [db02]

TASK [Upgrade opatch] *********************************************************************************************************************************************************************************************
 [WARNING]: Consider using unarchive module rather than running unzip

changed: [db01]
changed: [db02]

TASK [Cleanup the zipfile from the target home] *******************************************************************************************************************************************************************
changed: [db02]
changed: [db01]

PLAY RECAP ********************************************************************************************************************************************************************************************************
db01                       : ok=6    changed=4    unreachable=0    failed=0
db02                       : ok=6    changed=4    unreachable=0    failed=0

oracle@control:~/work$

We now have a playbook that can update all your oPatches in a blink!

Please note that this example is a very basic one since this is to give an overview on how to manage oPatch with Ansible.
Many features could be implemented here (and are implemented in the code we use here at Pythian) like:

  • Check the list of Oracle homes on each server — there are often many.
  • Check the version of each Oracle home’s oPatch.
  • Manager different oPatch versions : 11, 12 and 13.
  • Use the Ansible roles to make the code more modular and reusable.
  • Upgrade opatch only if it needs to and more…

I hope you enjoyed this Ansible overview!

Categories: DBA Blogs

Why Exadata can not go for redundancy in Disk Storage ?

Tom Kyte - Mon, 2017-04-24 04:26
Hi , All MPP systems suffer from re-distribution at run time . The Fast data Loading is a Myth as u Load once but read as long u wish . Exadata with storage cells still constrained by shared disk if we consider RAC env . Why Oracle can n...
Categories: DBA Blogs

How to remove orphaned breadcrumb ?

Tom Kyte - Mon, 2017-04-24 04:26
Hello Tom, I have deleted a page in an APEX application. THat page was built with a breadcrumb. But I forgot to specify I wanted the breadcrumb to be deleted with the page. THe result is I have an orphaned breadcrumb... How can I delete it ? ...
Categories: DBA Blogs

ODBC does not support interval data type

Tom Kyte - Mon, 2017-04-24 04:26
Hi, In Oracle through the ODBC API query a type of data for the Interval day to second, call SQLBindCol interface returned that the data does not support the type; I binding the column with varchar, call SQLFetch interface directly crash. Check some ...
Categories: DBA Blogs

DR during database migration

Tom Kyte - Mon, 2017-04-24 04:26
We currently have a production database 11.2.0.4 (A) and an active physical standby (PSA). we are in the process of developing migration code for this database because it is vendor supplied schemas. The target database is 12.1.0.2 (B). when we go to ...
Categories: DBA Blogs

distributed query join local table want to run local sub select first

Tom Kyte - Mon, 2017-04-24 04:26
I have a view (huge data set) on remote, and a local table. when join them restrict on local table down to one row, it does not run on local first, it always run the complete view on remote which takes forever. Tried couple different hint did nto he...
Categories: DBA Blogs

Install oracle RAC 11Gr2

Tom Kyte - Mon, 2017-04-24 04:26
Hello ,Can you help me for install oracle RAc11gR2? i give you the steps that i follown I use oraclevm virtual box and the os is oracle linux 6.5 the Memory is 3072GO swap 3072GO create group groupadd -g 501 oinstall groupadd -g 502 dba g...
Categories: DBA Blogs

Calculating partial table size

Tom Kyte - Mon, 2017-04-24 04:26
Hello, I need to copy 30% of SOME_TABLE data, which occupies 3TB in total. Is there a way to estimate "actual" size of 30% of table records? I know that it is ~900GB by using simple math, but this may vary due to CLOB datatype and etc... so I w...
Categories: DBA Blogs

12cR2 RMAN> REPAIR

Yann Neuhaus - Sun, 2017-04-23 15:39

Do you know the RMAN Recovery advisor? It detects the problems, and then you:

RMAN> list failure;
RMAN> advise failure;
RMAN> repair failure;

You need to have a failure detected. You can run Health Check if it was not detected automatically (see https://blog.dbi-services.com/oracle-12c-rman-list-failure-does-not-show-any-failure-even-if-there-is-one/). In 12.2 you can run the repair directly, by specifying what you want to repair.

Syntax

There is no online help on RMAN but you can list which keywords are expected by supplying a wrong one:
RMAN> repair xxx;
 
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "identifier": expecting one of: "failure"
RMAN-01008: the bad identifier was: xxx
RMAN-01007: at line 1 column 8 file: standard input

This is 12.1.0.2 where the only option is REPAIR FAILURE. In 12.2 we have a lot more:


RMAN> repair xxx
 
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "identifier": expecting one of: "database, database root, datafile, failure, pluggable, tablespace, ("
RMAN-01008: the bad identifier was: xxx
RMAN-01007: at line 1 column 8 file: standard input

When you know what is broken, you can repair it without having to know what to restore and what to recover. You can repair:

  • database: the whole database
  • database root: the CDB$ROOT container, which means all its tablespaces
  • pluggable database: it means all the PDB tablespaces
  • a specific datafile
Repair pluggable database

I corrupt one datafile from PDB01:


RMAN> host "> /u01/oradata/CDB2/CDB2_SITE1/46EA7EF707457B4FE0531416A8C027F2/datafile/o1_mf_system_d8k2t4wj_.dbf";
host command complete

And I repair the pluggable database:


RMAN> repair pluggable database PDB01;
 
Starting restore at 23-APR-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=203 device type=DISK
Executing: alter database datafile 21 offline
Executing: alter database datafile 22 offline
Executing: alter database datafile 23 offline
 
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00021 to /u01/oradata/CDB2/CDB2_SITE1/46EA7EF707457B4FE0531416A8C027F2/datafile/o1_mf_system_d8k2t4wj_.dbf
channel ORA_DISK_1: restoring datafile 00022 to /u01/oradata/CDB2/CDB2_SITE1/46EA7EF707457B4FE0531416A8C027F2/datafile/o1_mf_sysaux_d8k2t4wn_.dbf
channel ORA_DISK_1: restoring datafile 00023 to /u01/oradata/CDB2/CDB2_SITE1/46EA7EF707457B4FE0531416A8C027F2/datafile/o1_mf_users_d8kbmy6w_.dbf
channel ORA_DISK_1: reading from backup piece /u90/fast_recovery_area/CDB2_SITE1/46EA7EF707457B4FE0531416A8C027F2/backupset/2017_04_23/o1_mf_nnndf_B_dht2d4ow_.bkp
channel ORA_DISK_1: piece handle=/u90/fast_recovery_area/CDB2_SITE1/46EA7EF707457B4FE0531416A8C027F2/backupset/2017_04_23/o1_mf_nnndf_B_dht2d4ow_.bkp tag=B
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 23-APR-17
 
Starting recover at 23-APR-17
using channel ORA_DISK_1
 
starting media recovery
media recovery complete, elapsed time: 00:00:00
 
Executing: alter database datafile 21 online
Executing: alter database datafile 22 online
Executing: alter database datafile 23 online
Finished recover at 23-APR-17

The good thing is that it automatically restores and recovers the datafiles with only one command.
But we see here that all datafiles have been restored. AsI knew that only one datafile was corrupted, it would have been faster to use REPAIR DATAFILE for it.

However, doing the same and calling the recovery advisor is not better: it advises to:

1 Restore and recover datafile 21; Restore and recover datafile 23; Recover datafile 22

When dealing with recovery, you need to understand how it works, what was the scope of the failure, and how to repair it. The advisors or automatic actions can help but do not alleviate the need to understand.

 

Cet article 12cR2 RMAN> REPAIR est apparu en premier sur Blog dbi services.

Pages

Subscribe to Oracle FAQ aggregator