Wednesday, August 19, 2015

Automation for DBA - Vagrant part 1

Vagrant is an open source tool for building a virtual environment. It is based on text based configuration file and concept of prebuilt OS boxes.
One can find a lot of boxes on Atlas webpage (, ready to download and use.  Although if there are any security concerns about using a OS from 3rd party webpage, I published a blog post about creating a new Vagrant box using Oracle Linux here and Oracle Virtual Box.

For simplicity of this series, predefined Linux box will be used in all examples.
There are two boxes that I’m using in my own work.
First one “kikitux/oracle6-racattack” created by Alvaro Miranda ( member of Rac Attack team. The other one is “racattack/oracle65” created by Jeremy Schneider who is also member of RacAttack team.

Vagrant itself is not a Virtualization provider. It can control different virtualization tools, including:
-    Virtual Box
-    AWS EC2 (with an additional plugin)
-    KVM (with an additional plugin)
-    VMWare Fusion / Workstation (paid version of Vagrant)

Most of the differences between providers are hidden by Vagrant workflow, and a configuration file defining a Vagrant box is similar between different providers.
Vagrant user is using same set of commands to start, stop or destroy virtual machine and does not have to know which provider is used for that.

There is a list of typical Vagrant commands:

-    vagrant init – creating a basic configuration file
-    vagrant up – starting a virtual machine
-    vagrant halt – stopping a virtual machine
-    vagrant destroy – delete a virtual machine
-    vagrant ssh – open a ssh session to vagrant user on virtual machine

After this short introduction let’s get started with a Vagrant on Laptop/Desktop (using Virtual Box as a provider)

-    vagrant software –
-    Virtual Box -

In the first step a new directory will be created and used for Vagrant to initiate build of new virtual machine.

mkdir machine1
cd machine1

In the next step Vagrant machine will be initialized

vagrant init racattack/oracle65
A `Vagrantfile` has been placed in this directory. You are now
ready to `vagrant up` your first virtual environment! Please read
the comments in the Vagrantfile as well as documentation on
`` for more information on using Vagrant.

A new Vagrantfile has been created with basic configuration. By default Vagrant is trying to change a default set of public / private key with a new pair generated on user machine. But there are some problem with that on latest version of Vagrant, so as a workaround please add this line to Vagrantfile (see

config.ssh.insert_key = false

This is how Vagrantfile looks like without commented lines

Vagrant.configure(2) do |config| = "racattack/oracle65"
    config.ssh.insert_key = false

Now this machine can be started. If a box selected in an init stage is not existing yet in the provider catalog, it will be automatically downloaded and cloned to a new machine.

mprzepiorowski$ vagrant up
Bringing machine 'default' up with 'virtualbox' provider...
==> default: Box 'racattack/oracle65' could not be found. Attempting to find and install...
    default: Box Provider: virtualbox
    default: Box Version: >= 0
==> default: Loading metadata for box 'racattack/oracle65'
    default: URL:
==> default: Adding box 'racattack/oracle65' (v14.11.01) for provider: virtualbox
    default: Downloading:
==> default: Successfully added box 'racattack/oracle65' (v14.11.01) for 'virtualbox'!
==> default: Importing base box 'racattack/oracle65'...
==> default: Matching MAC address for NAT networking...
==> default: Checking if box 'racattack/oracle65' is up to date...
==> default: Setting the name of the VM: machine1_default_1439847872931_79029
==> default: Clearing any previously set network interfaces...
==> default: Preparing network interfaces based on configuration...
    default: Adapter 1: nat
==> default: Forwarding ports...
    default: 22 => 2222 (adapter 1)
==> default: Booting VM...
==> default: Waiting for machine to boot. This may take a few minutes...
    default: SSH address:
    default: SSH username: vagrant
    default: SSH auth method: private key
    default: Warning: Connection timeout. Retrying...
==> default: Machine booted and ready!
==> default: Checking for guest additions in VM...
==> default: Mounting shared folders...
    default: /vagrant => /Users/mprzepiorowski/Documents/machine1
Is this that simple? Yes it is – a new virtual machine with Oracle Linux is installed and ready. Next step is to ssh into a new created machine. There is no need to know neither a password nor an IP address,

mprzepiorowski$ vagrant ssh
[vagrant@oracle6-racattack ~]$ 

Now it is a time to customize this machine a little bit. Before that an existing machine has to be stopped using a Vagrant command

mprzepiorowski$ vagrant halt
==> default: Attempting graceful shutdown of VM...

As it was mentioned at the beginning of the post, Vagrant is using a text based configuration file. All customizations can be done by editing this file.
The configuration file structure can be divided into two sections.
First section defines a provider configuration and this part will change when a provider will be changed. Other section is provider independent and describes all other parameters.

Vagrant.configure(2) do |config|

# this part is common for all providers = "racattack/oracle65"
  config.ssh.insert_key = false
  config.vm.hostname = "machine1"

# provider specific configuration

  config.vm.provider "virtualbox" do |vb|
    vb.memory = 1024 
    vb.cpus = 2


Starting a machine with a new configuration

mprzepiorowski$ vagrant up
Bringing machine 'default' up with 'virtualbox' provider...
==> default: Checking if box 'racattack/oracle65' is up to date...
==> default: Clearing any previously set forwarded ports...
==> default: Clearing any previously set network interfaces...
==> default: Preparing network interfaces based on configuration...
    default: Adapter 1: nat
==> default: Forwarding ports...
    default: 22 => 2222 (adapter 1)
==> default: Running 'pre-boot' VM customizations...
==> default: Booting VM...
==> default: Waiting for machine to boot. This may take a few minutes...
    default: SSH address:
    default: SSH username: vagrant
    default: SSH auth method: private key
    default: Warning: Connection timeout. Retrying...
    default: Warning: Connection timeout. Retrying...
==> default: Machine booted and ready!
==> default: Checking for guest additions in VM...
==> default: Setting hostname...
==> default: Mounting shared folders...
    default: /vagrant => /Users/mprzepiorowski/Documents/machine1
==> default: Machine already provisioned. Run `vagrant provision` or use the `--provision`
==> default: flag to force provisioning. Provisioners marked to run always will still run.

Checking changes

[vagrant@machine1 ~]$ free
             total       used       free     shared    buffers     cached
Mem:       1020696     121356     899340          0      10980      42100
-/+ buffers/cache:      68276     952420
Swap:      8191996          0    8191996
[vagrant@machine1 ~]$ cat /proc/cpuinfo | grep processor
processor : 0
processor : 1

All looks good there. Virtual machine has 1 GB of RAM and two vCPU. Machine name has been changed to machine1 as well.

The last change tested here will be a new disk assigned to virtual machine. The box used for an example already has a “sdb” device and an “u01” file system defined inside a box. In this case device “sdc” will be added to the box and can be configured later as a “u02” file system if necessary. The file system creation and configuration will be presented in the next posts about Ansible.

disk_filename = 'disk-u02.vdi'

Vagrant.configure(2) do |config|

# this part is common for all providers = "racattack/oracle65"
  config.ssh.insert_key = false
  config.vm.hostname = "machine1"

# provider specific configuration

  config.vm.provider "virtualbox" do |vb|
    vb.memory = 1024
    vb.cpus = 2
    unless File.exist?(disk_filename)
       vb.customize ['createhd', '--filename', disk_filename, '--size', (5*1024), '--variant', 'Standard']
    vb.customize ['storageattach', :id, '--storagectl', 'SATA Controller', '--port', 2, '--device', 0, '--type', 'hdd', '--medium', disk_filename]


Options specified in vb.customize are options of VBoxManage command of Virtual Box installation.

mprzepiorowski$ vagrant ssh
Last login: Mon Aug 17 22:01:36 2015 from
[vagrant@machine1 ~]$ ls -l /dev/sdc*
brw-rw---- 1 root disk 8, 32 Aug 17 22:25 /dev/sdc
[vagrant@machine1 ~]$ 

New disk is added and ready for other activities.

This is end of this post and I will present similar steps for KVM and EC2 virtual machines in the next one.


Monday, August 10, 2015

Preparing a Oracle Linux for a Vagrant box

This post is an overview of an installation and configuration process of the Oracle Linux, which will be used as a machine “box” for Vagrant software using a Virtual Box platform.

Post itself is divided into two parts:

  1. Oracle Linux installation (points 1 to 12)
  2. Vagrant box configuration (points 13 to 16)

Part 1 – Oracle Linux installation


1. Create a new Virtual Machine - machine name will be used later to create Vagrant box

2. Set memory size - it does matter only for a installation process, as it can be changed later by Vagrant

3. Create a new disk, which will be used to keep a root and a swap file systems only. Any additional disks will be added dynamically via Vagrant. In this example, a disk size is set to 8 GB.

4. Mount Oracle Linux DVD into Virtual Machine drive

5. Configure 1st network interface to be NAT 


6. Add port forwarding for SSH

6.Start Virtual Machine and start a Oracle Linux installation. Some of the screens have been skipped in this post but a default values have been used for those ones.

7.Choose language and keyboard

8.Configure disk using a default basic definition

9.Configure a host name for a box, configure a network with DHCP and start up at boot time.

10.Default packages will be installed only at this time. Some will be added later.

11.Wait for process to be completed

12.Restart machine

Part 2 – Vagrant box configuration

13. Add Vagrant related configuration
  • Add user "vagrant" with password "vagrant"
  • Add Vagrant public insecure public key to .authorized_keys
  • Install Guest additional tools
Connect to VM using ssh
ssh -p 2022 root@localhost
The authenticity of host '[localhost]:2022 ([]:2022)' can't be established.
RSA key fingerprint is 3e:4f:bd:ca:45:d6:e8:d4:6b:4d:02:bb:1f:c3:ad:a2.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '[localhost]:2022' (RSA) to the list of known hosts.
root@localhost's password:
Last login: Sat Jul 18 21:25:57 2015 from
Add user using password vagrant
[root@oelbox ~]# useradd vagrant
[root@oelbox ~]# passwd vagrant
Changing password for user vagrant.
New password:
BAD PASSWORD: it is based on a dictionary word
BAD PASSWORD: is too simple
Retype new password:
passwd: all authentication tokens updated successfully.
Add Vagrant public key
[root@oelbox ~]# su - vagrant
[vagrant@oelbox ~]$ mkdir .ssh
[vagrant@oelbox ~]$ chmod 700 .ssh
[vagrant@oelbox ~]$ cd .ssh
[vagrant@oelbox .ssh]$ wget --no-check-certificate -O authorized_keys
--2015-07-18 21:29:16--
Connecting to||:443... connected.
WARNING: certificate common name “” doesn’t match requested host name “”.
HTTP request sent, awaiting response... 200 OK
Length: 409 [text/plain]
Saving to: “authorized_keys”

100%[===========================================================================================================================================================>] 409         --.-K/s   in 0s

2015-07-18 21:29:17 (94.1 MB/s) - “authorized_keys” saved [409/409]

[vagrant@oelbox .ssh]$ chmod 600 authorized_keys

Choose Install Guest Additional CD from Virtual Box menu and check if CDROM it was mounted

Install required packages and add Guest tools
[root@oelbox ~]# yum install kernel-uek-devel-3.8.13-16.2.1.el6uek.x86_64
[root@oelbox ~]# mount /dev/cdrom /mnt/
mount: block device /dev/sr0 is write-protected, mounting read-only
[root@oelbox ~]# /mnt/
Verifying archive integrity... All good.
Uncompressing VirtualBox 4.3.20 Guest Additions for Linux............
VirtualBox Guest Additions installer
Removing installed version 4.3.20 of VirtualBox Guest Additions...
Copying additional installer modules ...
Installing additional modules ...
Removing existing VirtualBox non-DKMS kernel modules       [  OK  ]
Building the VirtualBox Guest Additions kernel modules
Building the main Guest Additions module                   [  OK  ]
Building the shared folder support module                  [  OK  ]
Building the OpenGL support module                         [  OK  ]
Doing non-kernel setup of the Guest Additions              [  OK  ]
Starting the VirtualBox Guest Additions                    [  OK  ]
Installing the Window System drivers
Could not find the X.Org or XFree86 Window System, skipping. 

14. Install packages required for Oracle database and EPEL repository and Ansible. 
If you are using RedHat or Centos you can’t use oracle-rdbms packages. Oracle-ansible playbook, which will be used later will take care about all packages during a first installation.

[root@oelbox ~]# yum install oracle-rdbms-server-11gR2-preinstall.x86_64 oracle-rdbms-server-12cR1-preinstall.x86_64
[root@oelbox ~]# rpm -Uvh
warning: /var/tmp/rpm-tmp.SCcj3K: Header V3 RSA/SHA256 Signature, key ID 0608b895: NOKEY
Preparing...                ########################################### [100%]
   1:epel-release           ########################################### [100%]
[root@oelbox ~]# yum install ansible

15. Configure sudo for vagrant user - disable requiretty and allow vagrant user to run sudo without password for all commands.
[root@oelbox ~]# visudo
#Defaults    requiretty

16. Shutdown Virtual Machine.

This is end of configuration on the Linux level and this Virtual Machine can be used now as a source of the Vagrant box.


Monday, July 20, 2015

How to avoid boring work

Over the past few years working as an IT consultant, I've learned that having easy access to a robust lab where I can learn or test solutions is critical. But my approach to building a test lab has changed over the years.
These days I prefer not to "build" a lab but to "define" one — it is a better use of my time.
Building requires a long step-by-step process of installing all the necessary components: OS, databases, app servers, one by one based on the documentation. 
Instead, I can define a lab using tools such as Ansible, Chef, Puppet, Oracle Enterprise Manager, on top of tools that manage Virtual Machines like Vagrant or libvirt.

I presented my thoughts during Irish Oracle User Group conference in March 2015 ( How to avoid boring workand now I would like to start a series of blog post about this.

Here is a list of topics I plan to cover in the next few weeks:
  • Creating and managing a Virtual Machine using Vagrant (create a new box, add storage, use KVM or AWS EC2 as providers )
  • Installing and configuring Oracle Database using Ansible ( Thanks to great work of Mikael Sandstr├Âm on his Github Ansible repository )
  • Integration of Delphix "Data as a Service" product with an automated test environment build


Monday, December 22, 2014

Data Guard and Oracle Restart, aka relink always !!!

During my early years with PC I love to play "Leisure Suit Larry" (especially part 1 to 3 which has running on CGA or Hercules graphics card). Author of this games, Al Lowe had a good sense of humor and every time you failed your mission or stuck in a place - there was an pop up on the screen "Al says: save early, save often". After today fight with Oracle I should remember a next saying - "Relink after, relink always".

Let me explain what what happen today.

There used to be old Oracle bug in 11.2g which I described here. Basically Oracle Restart was not aware of role changes and could open a database in wrong mode after role switch. I created a workaround for it but I was really waiting for a patch. During a UKOUG 2014 I was speaking with Oracle Data Guard Product Manager - Larry Carpenter - and I learned that patch is ready. I asked @UweHesse for details and I got my numbers back - patch 15986647 and it is included in latest PSU

After I got this information I was happy - I just applied this PSU last week, so let's test it.

The first run looked OK - after switchover Oracle Restart displayed proper role and start-up option on new primary
2014-12-22 09:02:36.511: [    AGFW][2057]{0:0:44372} Agent received the message: RESOURCE_MODIFY_ATTR[ora.apppre11.db 1 1] ID 4355:617901
2014-12-22 09:02:36.513: [ora.apppre11.db][9207]{0:0:44372} [res_attr_modified] clsn_agent::modify {
2014-12-22 09:02:36.513: [ora.apppre11.db][9207]{0:0:44372} [res_attr_modified] clsn_agent::modify }
2014-12-22 09:02:36.513: [    AGFW][9207]{0:0:44372} Command: res_attr_modified for resource: ora.apppre11.db 1 1 completed with status: SUCCESS
2014-12-22 09:02:36.516: [    AGFW][2057]{0:0:44372} Attribute: ROLE for resource ora.apppre11.db modified to: PRIMARY
2014-12-22 09:02:36.516: [    AGFW][2057]{0:0:44372} Attribute: USR_ORA_OPEN_MODE for resource ora.apppre11.db modified to: open
2014-12-22 09:02:36.516: [    AGFW][2057]{0:0:44372}  config version updated to : 69 for ora.apppre11.db 1 1
2014-12-22 09:02:36.516: [    AGFW][2057]{0:0:44372} Agent sending last reply for: RESOURCE_MODIFY_ATTR[ora.apppre11.db 1 1] ID 4355:617901
2014-12-22 09:02:36.663: [    AGFW][2057]{0:0:44374} Agent received the message: RESOURCE_MODIFY_ATTR[ora.apppre11.apppre_adf.svc 1 1] ID 4355:617914
but only proper role on the new standby.

2014-12-22 09:03:10.420: [    AGFW][2057]{0:0:11369} Agent received the message: RESOURCE_MODIFY_ATTR[ora.apppre21.db 1 1] ID 4355:189787
2014-12-22 09:03:10.423: [ora.apppre21.db][2412]{0:0:11369} [res_attr_modified] clsn_agent::modify {
2014-12-22 09:03:10.424: [ora.apppre21.db][2412]{0:0:11369} [res_attr_modified] clsn_agent::modify }
2014-12-22 09:03:10.424: [    AGFW][2412]{0:0:11369} Command: res_attr_modified for resource: ora.apppre21.db 1 1 completed with status: SUCCESS
2014-12-22 09:03:10.424: [    AGFW][2057]{0:0:11369} Attribute: ROLE for resource ora.apppre21.db modified to: PHYSICAL_STANDBY
2014-12-22 09:03:10.424: [    AGFW][2057]{0:0:11369}  config version updated to : 142 for ora.apppre21.db 1 1
2014-12-22 09:03:10.424: [    AGFW][2057]{0:0:11369} Agent sending last reply for: RESOURCE_MODIFY_ATTR[ora.apppre21.db 1 1] ID 4355:189787
Start up option was still open instead of mount. Hmmm, I was sure that both nodes are same but again I reconfigure Data Guard Broker and Oracle Restart - no change - issue was resolved on the one server only.  Then I decided to double check if I have this PSU installed, and I found out that everything was in order in terms of PSU.
-bash-3.2$ /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch lsinventory
Oracle Interim Patch Installer version
Copyright (c) 2013, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/11.2.0/dbhome_1/oraInst.loc
OPatch version    :
OUI version       :
Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2014-12-22_15-08-59PM_1.log

Lsinventory Output file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2014-12-22_15-08-59PM.txt

Installed Top-level Products (1):

Oracle Database 11g                                        
There are 1 product(s) installed in this Oracle Home.

Interim patches (3) :

Patch  18235390     : applied on Mon Dec 22 14:54:02 GMT 2014
Unique Patch ID:  18349043
   Created on 4 Dec 2014, 18:50:09 hrs PST8PDT
   Bugs fixed:

Patch  19121549     : applied on Mon Nov 17 14:06:54 GMT 2014
Unique Patch ID:  18060349
Patch description:  "OCW Patch Set Update : (19121549)"
   Created on 7 Oct 2014, 03:38:04 hrs PST8PDT
   Bugs fixed:
     18328800, 18691572, 14525998, 18187697, 18348155, 17516024, 17387214
     17750548, 17617807, 17551223, 14671408, 14207615, 18272135, 18180541
     17292250, 17378618, 17500165, 18875012, 18464784, 17065496, 18848125
     13991403, 17955615, 14693336, 17273020, 17238586, 17089344, 17405605
     17531342, 17155238, 17159489, 18053580, 16543190, 17039197, 16317771
     17947785, 10052729, 16281493, 18346135, 17481314, 18199185, 18399991
     18024089, 18428146, 18352845, 18352846, 17391726, 18414137, 17001914
     17927970, 14378120, 16346413, 17305100, 15832129, 15986647, 16901346
     18068871, 17985714, 18536826, 16206997, 18752378, 16876500, 16429265
     18343490, 18336452, 16613232, 17273003, 19276791, 12928658, 18226143
     17172091, 18229842, 18053631, 16867761, 18231837, 15869775, 17483479
     18729166, 17405302, 15920201, 18709496

Patch  19121551     : applied on Mon Nov 17 14:05:34 GMT 2014
Unique Patch ID:  17971200
Patch description:  "Database Patch Set Update : (19121551)"
   Created on 9 Oct 2014, 13:06:55 hrs PST8PDT
Sub-patch  18522509; "Database Patch Set Update : (18522509)"
Sub-patch  18031668; "Database Patch Set Update : (18031668)"
Sub-patch  17478514; "Database Patch Set Update : (17478514)"
   Bugs fixed:
     16929165, 16220077, 17235750, 17468141, 18191164, 17006183, 16315398
     17501491, 13955826, 17288409, 12905058, 17446237, 17375354, 16992075
     16855292, 17050888, 17546973, 18554871, 17726838, 17614134, 9756271
     18673325, 17227277, 17232014, 13853126, 17545847, 17390160, 18096714
     12747740, 17016369, 17786518, 19271443, 10136473, 16785708, 17311728
     18018515, 16268425, 17610798, 18280813, 17082359, 17783588, 14245531
     18094246, 13866822, 17477958, 16943711, 18673304, 18031668, 19463897
     19463893, 19211724, 13498382, 16450169, 17397545, 17786278, 17767676
     14458214, 19289642, 17622427, 17824637, 17716305, 16399083, 18744139
     14852021, 19727057, 17242746, 17174582, 18277454, 13645875, 14084247
     17551709, 17393683, 17614227, 17705023, 17883081, 16042673, 18996843
     16285691, 17393915, 16228604, 17655634, 17596908, 17600719, 16180763
     17754782, 17323222, 18264060, 17325413, 17343514, 17865671, 16613964
     17811447, 16069901, 17390431, 16494615, 16043574, 17006570, 17027426
     14602788, 17080436, 18673342, 17186905, 17011832, 17394950, 13944971
     17752121, 17284817, 17811456, 17238511, 17239687, 17042658, 14764829
     17602269, 17672719, 17891946, 17205719, 18262334, 16721594, 17071721
     14565184, 17265217, 17389192, 17761775, 16360112, 17982555, 17842825
     19121551, 16837842, 18139690, 17313525, 18203837, 18203838, 18203835
     18436307, 17546761, 17721717, 17344412, 17787259, 16472716, 17346671
     17588480, 13364795, 14657740, 11733603, 17811438, 19466309, 17040527
     17037130, 17088068, 17612828, 18180390, 17449815, 19458377, 19554106
     18973907, 18230522, 19544839, 17811429, 16863422, 17237521, 17951233
     17752995, 16392068, 17437634, 14338435, 13609098, 17332800, 18199537
     17465741, 17441661, 18522509, 18061914, 14133975, 17570240, 16692232
     18247991, 18328509, 16956380, 17587063, 19049453, 16618694, 17586955
     18009564, 14285317, 16542886, 17341326, 17571306, 17036973, 18641419
     16524926, 17297939, 17478145, 17040764, 17299889, 16912439, 15861775
     14054676, 13951456, 17267114, 16850630, 17648596, 14010183, 17296856
     17478514, 16875449, 18681862, 14829250, 17385178, 17443671, 16731148
     16314254, 17165204, 15979965, 19584068, 17381384, 17892268, 16198143


OPatch succeeded.
But on one server I was testing patch for OEM (18235390) to allow it to monitor tablespaces without ORA-1000 errors.Is it possible that my OEM patch add anything special ?
I was thinking about that for a while but I was not sure. So I did simple test - installed this patch on standby server and ... yes - Oracle Restart issue has been fixed. 
Strange, very strange - what this patch did that Data Guard was fixed ? Well it did one important thing - it relinked oracle binaries again.
Lucky enough I got one more pair with latest PSU ready for test.I run test with PSU installed and bug was still there. OK - so let's relink - and it was it

2014-12-22 14:33:40.942: [    AGFW][2057]{0:0:12827} Agent received the message: RESOURCE_MODIFY_ATTR[ora.apppre21.db 1 1] ID 4355:199223
2014-12-22 14:33:40.944: [ora.apppre21.db][9494]{0:0:12827} [res_attr_modified] clsn_agent::modify {
2014-12-22 14:33:40.945: [ora.apppre21.db][9494]{0:0:12827} [res_attr_modified] clsn_agent::modify }
2014-12-22 14:33:40.945: [    AGFW][9494]{0:0:12827} Command: res_attr_modified for resource: ora.apppre21.db 1 1 completed with status: SUCCESS
2014-12-22 14:33:40.946: [    AGFW][2057]{0:0:12827} Attribute: ROLE for resource ora.apppre21.db modified to: PHYSICAL_STANDBY
2014-12-22 14:33:40.946: [    AGFW][2057]{0:0:12827} Attribute: USR_ORA_OPEN_MODE for resource ora.apppre21.db modified to: mount
2014-12-22 14:33:40.946: [    AGFW][2057]{0:0:12827}  config version updated to : 153 for ora.apppre21.db 1 1
2014-12-22 14:33:40.946: [    AGFW][2057]{0:0:12827} Agent sending last reply for: RESOURCE_MODIFY_ATTR[ora.apppre21.db 1 1] ID 4355:199223
This is quite strange and a little bit scary. I have installed all PSU using opatch auto from Grid Infrastructure Home and there was no errors but relinking Oracle Home actually fix a problem which was included in the patch. 
So remember "Relink after (patch), relink always"

Sunday, August 24, 2014

AWR Warehouse

I just noticed last week that there is a new patch for Enterprise Manager and it is enabling AWR Warehouse feature. There is a note ID 1901202.1 which describe bundle patch for OEM 12c release 4.

Today I had a chance to install it in my lab and now I can start testing new OEM feature.
There is some documentation here and on Kellyn's blog.

It is not configured so first task is to configure AWR Warehouse repository. In my case I will use same database which is used for OEM repository.

Retention period and staging area for snapshot files has to be configured as well.

 After these two steps AWR Warehouse configuration job is started and when it will be finished AWR Warehouse will be ready to use.

When repository is ready we can start adding databases which will be a source of AWR data.

To add a new database to warehouse it has be already configured in OEM and has a default credentials.

If all conditions are met database has been successfully added.

Now it's time to play with these new feature and see what we can achieve using it.


Crossplatform transportable tablespaces - part 2

It took some time since I wrote a first post about TTS migration but I finished that project literally hours before my summer break. Now after couple of days while I enjoyed thermal waters and good wine of Hungary it's time to write next post.

As I described in my previous post I had to migrate database from HP-UX into Linux and also upgrade it from 10g into 12c. This time it was only PoC but my goal was to minimize downtime of production database.

Source database datasheet:
- version 
- OS - HP-UX 
- existing backup using data files copy 
- there is a one backup set per data file 
- daily incremental backups are recovered into data files and keep in FRA

On target server a new version of Oracle has been installed and configured with ASM. New database with same character set as source database has been created as well.

Target database datasheet:
- version
- OS -Linux 64 bit
- storage - ASM

Transportable tablespaces (TTS) allow us to migrate data between databases but it is DBA responsibility to migrate rest of objects like views and PL/SQL code using for example DataPump. Before I have started a work on TTS I did the following preparation steps:
  1. On source database identify list of tablespaces and it's datafiles to move to new server
  2. On source database identify owners of objects included in TTS
    select distinct owner from dba_tables where tablespace_name like ('LIST','OF','TABLESPACES','TO','MIGRATE');
  3. On source database verify that tablespaces are self contained
  4. On target database create owners for all objects included in TTS 

This is list of steps I performed to achieve my goal.
  1. Copy existing data files copies into new server - if other location is used on new server change script in point 2
  2. Create a script to convert data file from data file copy into data file in new location
    select 'convert datafile ''' || || ''' format ''+DATA/POCDB/TTS/' || REGEXP_REPLACE(,'(/disk\d/oradata/XXX/)','') || ''' from platform ''HP-UX IA (64-bit)'';' from V$BACKUP_COPY_DETAILS b, v$datafile f where f.file# = b.file#;
  3. Convert file using script from point 2. Example output
    convert datafile '/oracle/fra/o1_mf_pocdb_rep_9x3xjcon_.dbf' format '+DATA/POCDB/TTS/reports01.dbf' from platform 'HP-UX IA (64-bit)';
    convert datafile '/oracle/fra/o1_mf_pocdb_rep_aas24412_.dbf' format '+DATA/POCDB/TTS/reports02.dbf' from platform 'HP-UX IA (64-bit)';
    convert datafile '/oracle/fra/o1_mf_pocdb_rep_22ee1445_.dbf' format '+DATA/POCDB/TTS/reports03.dbf' from platform 'HP-UX IA (64-bit)';
    convert datafile '/oracle/fra/o1_mf_pocdb_rep_34ddr545_.dbf' format '+DATA/POCDB/TTS/reports04.dbf' from platform 'HP-UX IA (64-bit)';
  4. Copy daily incremental backupsets into new server - if other location is used on new server change script in point 5
  5. Create a script to apply incremental backupset into new files
    set linesize 600 pagesize 999 feedback off head off trimspool on
    select 'recover from platform ''HP-UX IA (64-bit)'' foreign datafilecopy ''' || name || ''' from backupset ''' || handle || ''';'
    from V$BACKUP_DATAFILE bd, v$datafile d, V$BACKUP_PIECE bp where bd.file# = d.file#
    and bp.set_count = bd.set_count and handle is not null
    and bp.COMPLETION_TIME > sysdate -1
    order by bp.set_count;
  6. Recover data files copies
    recover from platform 'HP-UX IA (64-bit)' foreign datafilecopy '+DATA/POCDB/TTS/reports01.dbf' from backupset '/oracle/fra/POCDB/backupset/2014_07_20/o1_mf_nnnd1_TAG20140720T065649_9wppkp6w_.bkp';
    recover from platform 'HP-UX IA (64-bit)' foreign datafilecopy '+DATA/POCDB/TTS/reports02.dbf' from backupset '/oracle/fra/POCDB/backupset/2014_07_20/o1_mf_nnnd1_TAG20140720T065649_9wppkxg5_.bkp';
    recover from platform 'HP-UX IA (64-bit)' foreign datafilecopy '+DATA/POCDB/TTS/reports03.dbf' from backupset '/oracle/fra/POCDB/backupset/2014_07_20/o1_mf_nnnd1_TAG20140720T065649_9wppk4w9_.bkp';
    recover from platform 'HP-UX IA (64-bit)' foreign datafilecopy '+DATA/POCDB/TTS/reports04.dbf' from backupset '/oracle/fra/POCDB/backupset/2014_07_20/o1_mf_nnnd1_TAG20140720T065649_9wppkbws_.bkp';
  7. Run steps 4 to 6 until cut over date
  8. Run incremental backup on source
  9. Switch all required tablespace into read only mode
  10. Export transportable tablespaces using DataPump  using parameter file like this
    EXPDP command
    expdp parfile=tts.par
  11. Run incremental backup on source
  12. Copy backupsets from point 8 and 11 into new server
  13. Create a script to apply incremental backupset into new files (like in point 5)
  14. Import transportable tablespaces using dump file from point 9 and all converted files. In my case first attempt took very long as I didn't excluded stats and Oracle was gathering stats during importing process. This operation can be postponed to next phase using EXCLUDE option. Example IMPDP parameter file
    Run IMPDP command
    impdp parfile=imp.par
  15. Export source database code and users
    expdp directory=DPDUMP dumpfile=code.dmp exclude=TABLE_DATA full=y
  16. Import PL/SQL code - quick, dirty approach - but it was enough fot that case
    impdp directory=AWS TABLE_EXISTS_ACTION=SKIP dumpfile=code.dmp log=code_import.log full=y
  17. Perform backup of new database and gather new statistics

Performing all steps above allow me to migrate 1 TB database from HP-UX into Linux with 30 min downtime on source database. As it was POC I left source database working as main production database. For real migration time it's necessary to add time to recover last incremental backup and import TTS on new platform and also resolve issue with time necessary to gather statistics on new platform. Probably copy existing stats using PL/SQL will be solution there but it has to be check in next phase.

This post is long enough so I leave lesson learned to the next one.


Saturday, July 26, 2014

Beauty of command line - OEM 12c

Why all software should have a command line and automation plugin ? Answer is simple - if you have to repeat number of operation for different targets - scripts can help you save your precious time.

I really enjoy a fact that Oracle added a command line to Oracle Enterprise Manager line, and now you can script lot of boring tasks like adding new administrator to list of users who can access Named Credentials.

To add new admin (przepiorom) it's enough to run the following script przepiorom

This is first draft of this script (no error handling but it's doing his work)


PRIV_LIST=`emcli list_named_credentials | awk '{ print $1; }' | grep -v Credential  > /tmp/priv_$TPID`

while read LINE ; do
        echo $LINE
        emcli grant_privs -name="${NEW_ADMIN}" -privilege="FULL_CREDENTIAL;CRED_NAME=${LINE}:CRED_OWNER=sysman"
done > /tmp/priv_$TPID

rm /tmp/priv_$PPID

The next example is an another script which is refreshing a Weblogic domain components.
When a new version of application is deployed a previous one are still registered as a targets and you will see it as down in your OEM.

There is a domain refresh command in OEM menu but if you have more systems going through all of those is not what you want. Using a command line and configuration file you can be done with one line.
emcli login -username=sysman -password=xxxxxxx -force
emcli refresh_wls -input_file=domain_refresh_file:/home/oracle/bin/domain_refresh_file.csv –debug

Content of domain_refresh_file.csv looks like this:


There is a one line per target split into two parts.

First part of line is a target name and domain name, ex. /xxx_soa_mot_domain_soa/soa Second part is operation: 
R - remove target which doesn't exist in domain anymore 
E - enable refresh of domain (aka. add monitoring targets)