Oracle Database puppet module “https://github.com/biemond/biemond-oradb”

Dependency with

  • puppetlabs/concat >= 1.0.0
  • puppetlabs/stdlib >= 3.2.0

Should work on Docker, for Solaris and on all Linux version like RedHat, CentOS, Ubuntu, Debian, Suse SLES or OracleLinux

Example of Opensource Puppet 3.4.3 Puppet master configuration in a vagrant box puppet master

  • oradb (oracle database 11.2.0.1 ) with GoldenGate 12.1.2

Should work for Puppet 2.7 & 3.0

Oracle Database Features

  • Oracle Grid 11.2.0.4, 12.1.0.1 Linux / Solaris installation
  • Oracle Database 12.1.0.1,12.1.0.2 Linux / Solaris installation
  • Oracle Database 11.2.0.1,11.2.0.3,11.2.0.4 Linux / Solaris installation
  • Oracle Database Instance 11.2 & 12.1 with pluggable database or provide your own db template
  • Oracle Database Client 12.1.0.1,12.1.0.2,11.2.0.4,11.2.0.1 Linux / Solaris installation
  • Oracle Database Net configuration
  • Oracle Database Listener
  • Tnsnames entry
  • Oracle ASM
  • Oracle RAC
  • OPatch upgrade
  • Apply OPatch also for Clusterware
  • Create database instances
  • Stop/Start database instances with db_control puppet resource type

Enterprise Manager

  • Enterprise Manager Server 12.1.0.4 12c cloud installation / configuration
  • Agent installation via AgentPull.sh & AgentDeploy.sh

GoldenGate

  • GoldenGate 12.1.2, 11.2.1

Repository Creation Utility (RCU)

  • Installs RCU repositoy for Oracle SOA Suite / Webcenter ( 11.1.1.6.0 and 11.1.1.7.0 ) / Oracle Identity Management ( 11.1.2.1 )

Oracle RAC

In combination with the ora_rac module of Bert Hajee (https://forge.puppetlabs.com/hajee/ora_rac)

Oracle Database resource types

  • db_control, start stop or a restart a database instance also used by dbactions manifest.pp
  • db_opatch, used by the opatch.pp manifest
  • db_rcu, used by the rcu.pp manifest
  • db_listener, start stop or a restart the oracle listener ( supports refreshonly )

In combination with the oracle module of Bert Hajee (http://forge.puppetlabs.com/hajee/oracle) you can also create

  • create a tablespace
  • create a user with the required grants and quota’s
  • create one or more roles
  • create one or more services
  • change a database init parameter (Memory or SPFILE)

Some manifests like installdb.pp, opatch.pp or rcusoa.pp supports an alternative mountpoint for the big oracle files. When not provided it uses the files location of the oradb puppet module else you can use $puppetDownloadMntPoint => “/mnt” or “puppet:///modules/xxxx/”

Oracle Big files and alternate download location

Some manifests like oradb:installdb, opatch or rcu supports an alternative mountpoint for the big oracle setup/install files. When not provided it uses the files folder located in the orawls puppet module else you can use $source =>

  • “/mnt”
  • “/vagrant”
  • “puppet:///modules/oradb/” (default)
  • “puppet:///database/”

when the files are also locally accessible then you can also set $remote_file => false this will not move the files to the download folder, just extract or install

templates.pp

The databaseType value should contain only one of these choices.

  • EE = Enterprise Edition
  • SE = Standard Edition
  • SEONE = Standard Edition One

Installation, Disk or memory issues

# hiera
hosts:
  'emdb.example.com':
    ip:                "10.10.10.15"
    host_aliases:      'emdb'
  'localhost':
    ip:                "127.0.0.1"
    host_aliases:      'localhost.localdomain,localhost4,localhost4.localdomain4'

$host_instances = hiera('hosts', {})
create_resources('host',$host_instances)

# disable the firewall
service { iptables:
  enable    => false,
  ensure    => false,
  hasstatus => true,
}

# set the swap ,forge puppet module petems-swap_file
class { 'swap_file':
  swapfile     => '/var/swap.1',
  swapfilesize => '8192000000'
}

# set the tmpfs
mount { '/dev/shm':
  ensure      => present,
  atboot      => true,
  device      => 'tmpfs',
  fstype      => 'tmpfs',
  options     => 'size=3500m',
}

see this chapter “Linux kernel, ulimits and required packages” for more important information

Linux kernel, ulimits and required packages

install the following module to set the database kernel parameters puppet module install fiddyspence-sysctl

install the following module to set the database user limits parameters puppet module install erwbgy-limits

   $all_groups = ['oinstall','dba' ,'oper']

   group { $all_groups :
     ensure      => present,
   }

   user { 'oracle' :
     ensure      => present,
     uid         => 500,
     gid         => 'oinstall',
     groups      => ['oinstall','dba','oper'],
     shell       => '/bin/bash',
     password    => '$1$DSJ51vh6$4XzzwyIOk6Bi/54kglGk3.',
     home        => "/home/oracle",
     comment     => "This user oracle was created by Puppet",
     require     => Group[$all_groups],
     managehome  => true,
   }

   sysctl { 'kernel.msgmnb':                 ensure => 'present', permanent => 'yes', value => '65536',}
   sysctl { 'kernel.msgmax':                 ensure => 'present', permanent => 'yes', value => '65536',}
   sysctl { 'kernel.shmmax':                 ensure => 'present', permanent => 'yes', value => '2588483584',}
   sysctl { 'kernel.shmall':                 ensure => 'present', permanent => 'yes', value => '2097152',}
   sysctl { 'fs.file-max':                   ensure => 'present', permanent => 'yes', value => '6815744',}
   sysctl { 'net.ipv4.tcp_keepalive_time':   ensure => 'present', permanent => 'yes', value => '1800',}
   sysctl { 'net.ipv4.tcp_keepalive_intvl':  ensure => 'present', permanent => 'yes', value => '30',}
   sysctl { 'net.ipv4.tcp_keepalive_probes': ensure => 'present', permanent => 'yes', value => '5',}
   sysctl { 'net.ipv4.tcp_fin_timeout':      ensure => 'present', permanent => 'yes', value => '30',}
   sysctl { 'kernel.shmmni':                 ensure => 'present', permanent => 'yes', value => '4096', }
   sysctl { 'fs.aio-max-nr':                 ensure => 'present', permanent => 'yes', value => '1048576',}
   sysctl { 'kernel.sem':                    ensure => 'present', permanent => 'yes', value => '250 32000 100 128',}
   sysctl { 'net.ipv4.ip_local_port_range':  ensure => 'present', permanent => 'yes', value => '9000 65500',}
   sysctl { 'net.core.rmem_default':         ensure => 'present', permanent => 'yes', value => '262144',}
   sysctl { 'net.core.rmem_max':             ensure => 'present', permanent => 'yes', value => '4194304', }
   sysctl { 'net.core.wmem_default':         ensure => 'present', permanent => 'yes', value => '262144',}
   sysctl { 'net.core.wmem_max':             ensure => 'present', permanent => 'yes', value => '1048576',}

   class { 'limits':
     config => {
                '*'       => { 'nofile'  => { soft => '2048'   , hard => '8192',   },},
                'oracle'  => { 'nofile'  => { soft => '65536'  , hard => '65536',  },
                                'nproc'  => { soft => '2048'   , hard => '16384',  },
                                'stack'  => { soft => '10240'  ,},},
                },
     use_hiera => false,
   }

   $install = [ 'binutils.x86_64', 'compat-libstdc++-33.x86_64', 'glibc.x86_64','ksh.x86_64','libaio.x86_64',
                'libgcc.x86_64', 'libstdc++.x86_64', 'make.x86_64','compat-libcap1.x86_64', 'gcc.x86_64',
                'gcc-c++.x86_64','glibc-devel.x86_64','libaio-devel.x86_64','libstdc++-devel.x86_64',
                'sysstat.x86_64','unixODBC-devel','glibc.i686','libXext.x86_64','libXtst.x86_64']

   package { $install:
     ensure  => present,
   }

Database install

$puppetDownloadMntPoint = "puppet:///modules/oradb/"

oradb::installdb{ '12.1.0.2_Linux-x86-64':
  version                => '12.1.0.2',
  file                   => 'V46095-01',
  databaseType           => 'SE',
  oracleBase             => '/oracle',
  oracleHome             => '/oracle/product/12.1/db',
  bashProfile            => true,
  user                   => 'oracle',
  group                  => 'dba',
  group_install          => 'oinstall',
  group_oper             => 'oper',
  downloadDir            => '/data/install',
  zipExtract             => true,
  puppetDownloadMntPoint => $puppetDownloadMntPoint,
}

or with zipExtract ( does not download or extract , software is in /install/linuxamd64_12c_database )

oradb::installdb{ '12.1.0.1_Linux-x86-64':
  version                => '12.1.0.1',
  file                   => 'linuxamd64_12c_database',
  databaseType           => 'SE',
  oracleBase             => '/oracle',
  oracleHome             => '/oracle/product/12.1/db',
  bashProfile            => true,
  user                   => 'oracle',
  group                  => 'dba',
  group_install          => 'oinstall',
  group_oper             => 'oper',
  downloadDir            => '/install',
  zipExtract             => false,
}

or

oradb::installdb{ '112040_Linux-x86-64':
  version                => '11.2.0.4',
  file                   => 'p13390677_112040_Linux-x86-64',
  databaseType           => 'SE',
  oracleBase             => '/oracle',
  oracleHome             => '/oracle/product/11.2/db',
  eeOptionsSelection     => true,
  eeOptionalComponents   => 'oracle.rdbms.partitioning:11.2.0.4.0,oracle.oraolap:11.2.0.4.0,oracle.rdbms.dm:11.2.0.4.0,oracle.rdbms.dv:11.2.0.4.0,oracle.rdbms.lbac:11.2.0.4.0,oracle.rdbms.rat:11.2.0.4.0',
  user                   => 'oracle',
  group                  => 'dba',
  group_install          => 'oinstall',
  group_oper             => 'oper',
  downloadDir            => '/install',
  zipExtract             => true,
  puppetDownloadMntPoint => $puppetDownloadMntPoint,
}

or

oradb::installdb{ '112030_Linux-x86-64':
  version                => '11.2.0.3',
  file                   => 'p10404530_112030_Linux-x86-64',
  databaseType           => 'SE',
  oracleBase             => '/oracle',
  oracleHome             => '/oracle/product/11.2/db',
  user                   => 'oracle',
  group                  => 'dba',
  group_install          => 'oinstall',
  group_oper             => 'oper',
  downloadDir            => '/install',
  zipExtract             => true,
  puppetDownloadMntPoint => $puppetDownloadMntPoint,
}

or

oradb::installdb{ '112010_Linux-x86-64':
  version       => '11.2.0.1',
  file          => 'linux.x64_11gR2_database',
  databaseType  => 'SE',
  oracleBase    => '/oracle',
  oracleHome    => '/oracle/product/11.2/db',
  user          => 'oracle',
  group         => 'dba',
  group_install => 'oinstall',
  group_oper    => 'oper',
  downloadDir   => '/install',
  zipExtract    => true,
 }

Patching

For opatchupgrade you need to provide the Oracle support csiNumber and supportId and need to be online. Or leave them empty but it needs the Expect rpm to emulate OCM

# use this on a Grid or Database home
oradb::opatchupgrade{'112000_opatch_upgrade':
  oracleHome             => '/oracle/product/11.2/db',
  patchFile              => 'p6880880_112000_Linux-x86-64.zip',
  #  csiNumber              => '11111',
  #  supportId              => 'biemond@gmail.com',
  csiNumber              => undef,
  supportId              => undef,
  opversion              => '11.2.0.3.6',
  user                   => 'oracle',
  group                  => 'dba',
  downloadDir            => '/install',
  puppetDownloadMntPoint => $puppetDownloadMntPoint,
  require                =>  Oradb::Installdb['112030_Linux-x86-64'],
}

Opatch

# october 2014 11.2.0.4.4 patch
oradb::opatch{'19121551_db_patch':
  ensure                 => 'present',
  oracleProductHome      => hiera('oracle_home_dir'),
  patchId                => '19121551',
  patchFile              => 'p19121551_112040_Linux-x86-64.zip',
  user                   => hiera('oracle_os_user'),
  group                  => 'oinstall',
  downloadDir            => hiera('oracle_download_dir'),
  ocmrf                  => true,
  require                => Oradb::Opatchupgrade['112000_opatch_upgrade_db'],
  puppetDownloadMntPoint => hiera('oracle_source'),
}

or for clusterware aka opatch auto

oradb::opatch{'18706472_grid_patch':
  ensure                 => 'present',
  oracleProductHome      => hiera('grid_home_dir'),
  patchId                => '18706472',
  patchFile              => 'p18706472_112040_Linux-x86-64.zip',
  clusterWare            => true,
  bundleSubPatchId       => '18522515',  sub patchid of bundle patch ( else I can't detect it if it is already applied)
  user                   => hiera('grid_os_user'),
  group                  => 'oinstall',
  downloadDir            => hiera('oracle_download_dir'),
  ocmrf                  => true,
  require                => Oradb::Opatchupgrade['112000_opatch_upgrade'],
  puppetDownloadMntPoint => hiera('oracle_source'),
}

# this 19791420 patch contains 2 patches (in different sub folders), one bundle and a normal one.
# we want to apply the bundle and need to provide the right value for bundleSubFolder
oradb::opatch{'19791420_grid_patch':
  ensure                 => 'present',
  oracleProductHome      => hiera('grid_home_dir'),
  patchId                => '19791420',
  patchFile              => 'p19791420_112040_Linux-x86-64.zip',
  clusterWare            => true,
  bundleSubPatchId       => '19121552', # sub patchid of bundle patch ( else I can't detect it if it is already applied)
  bundleSubFolder        => '19380115', # optional subfolder inside the patch zip
  user                   => hiera('grid_os_user'),
  group                  => 'oinstall',
  downloadDir            => hiera('oracle_download_dir'),
  ocmrf                  => true,
  require                => Oradb::Opatchupgrade['112000_opatch_upgrade_asm'],
  puppetDownloadMntPoint => hiera('oracle_source'),
}

# the same patch applied with opatch auto to an oracle database home, this time we need to use the 19121551 as bundleSubPatchId
# this is the october 2014  11.2.0.4.4 patch
oradb::opatch{'19791420_grid_patch':
  ensure                 => 'present',
  oracleProductHome      => hiera('oracle_home_dir'),
  patchId                => '19791420',
  patchFile              => 'p19791420_112040_Linux-x86-64.zip',
  clusterWare            => true,
  bundleSubPatchId       => '19121551', # sub patchid of bundle patch ( else I can't detect it if it is already applied)
  bundleSubFolder        => '19380115', # optional subfolder inside the patch zip
  user                   => hiera('grid_os_user'),
  group                  => 'oinstall',
  downloadDir            => hiera('oracle_download_dir'),
  ocmrf                  => true,
  require                => Oradb::Opatchupgrade['112000_opatch_upgrade_asm'],
  puppetDownloadMntPoint => hiera('oracle_source'),
}

# same patch 19791420 but then for the oracle db home, this patch requires the bundle patch of 19791420 or
# 19121551 october 2014  11.2.0.4.4 patch
oradb::opatch{'19791420_db_patch':
  ensure                 => 'present',
  oracleProductHome      => hiera('oracle_home_dir'),
  patchId                => '19791420',
  patchFile              => 'p19791420_112040_Linux-x86-64.zip',
  clusterWare            => false,
  bundleSubPatchId       => '19282021', # sub patchid of bundle patch ( else I can't detect it)
  bundleSubFolder        => '19282021', # optional subfolder inside the patch zip
  user                   => hiera('oracle_os_user'),
  group                  => 'oinstall',
  downloadDir            => hiera('oracle_download_dir'),
  ocmrf                  => true,
  require                => Oradb::Opatch['19121551_db_patch'],
  puppetDownloadMntPoint => hiera('oracle_source'),
}

Oracle net

oradb::net{ 'config net8':
  oracleHome   => '/oracle/product/11.2/db',
  version      => '11.2' or "12.1",
  user         => 'oracle',
  group        => 'dba',
  downloadDir  => '/install',
  dbPort       => '1521', #optional
  require      => Oradb::Opatch['14727310_db_patch'],
}

Listener

db_listener{ 'startlistener':
  ensure          => 'running',  # running|start|abort|stop
  oracle_base_dir => '/oracle',
  oracle_home_dir => '/oracle/product/11.2/db',
  os_user         => 'oracle',
  listener_name   => 'listener' # which is the default and optional
}

# subscribe to changes
db_listener{ 'startlistener':
  ensure          => 'running',  # running|start|abort|stop
  oracle_base_dir => '/oracle',
  oracle_home_dir => '/oracle/product/11.2/db',
  os_user         => 'oracle',
  listener_name   => 'listener' # which is the default and optional
  refreshonly     => true,
  subscribe       => XXXXX,
}

# the old way which also calls db_listener type
oradb::listener{'start listener':
  action       => 'start',  # running|start|abort|stop
  oracleBase   => '/oracle',
  oracleHome   => '/oracle/product/11.2/db',
  user         => 'oracle',
  group        => 'dba',
  listenername => 'listener' # which is the default and optional
}

Database instance

oradb::database{ 'testDb_Create':
  oracleBase              => '/oracle',
  oracleHome              => '/oracle/product/11.2/db',
  version                 => '11.2',
  user                    => 'oracle',
  group                   => 'dba',
  downloadDir             => '/install',
  action                  => 'create',
  dbName                  => 'test',
  dbDomain                => 'oracle.com',
  dbPort                  => '1521',
  sysPassword             => 'Welcome01',
  systemPassword          => 'Welcome01',
  dataFileDestination     => "/oracle/oradata",
  recoveryAreaDestination => "/oracle/flash_recovery_area",
  characterSet            => "AL32UTF8",
  nationalCharacterSet    => "UTF8",
  initParams              => {'open_cursors'        => '1000',
                              'processes'           => '600',
                              'job_queue_processes' => '4' },
  sampleSchema            => 'TRUE',
  memoryPercentage        => "40",
  memoryTotal             => "800",
  databaseType            => "MULTIPURPOSE",
  emConfiguration         => "NONE",
  require                 => Oradb::Listener['start listener'],
}

you can also use a comma separated string for initParams

  initParams              => "open_cursors=1000,processes=600,job_queue_processes=4",

or based on your own template

The template must be have the following extension dbt.erb like dbtemplate_12.1.dbt.erb, use puppetDownloadMntPoint parameter for the template location or add your template to the template dir of the oradb module

with a template of the oradb module

oradb::database{ 'testDb_Create':
  oracleBase              => '/oracle',
  oracleHome              => '/oracle/product/12.1/db',
  version                 => '12.1',
  user                    => 'oracle',
  group                   => 'dba',
  template                => 'dbtemplate_12.1', # or dbtemplate_11gR2_asm, this will use dbtemplate_12.1.dbt.erb example template
  downloadDir             => '/install',
  action                  => 'create',
  dbName                  => 'test',
  dbDomain                => 'oracle.com',
  dbPort                  => '1521',
  sysPassword             => 'Welcome01',
  systemPassword          => 'Welcome01',
  dataFileDestination     => "/oracle/oradata",
  recoveryAreaDestination => "/oracle/flash_recovery_area",
  characterSet            => "AL32UTF8",
  nationalCharacterSet    => "UTF8",
  memoryPercentage        => "40",
  memoryTotal             => "800",
  require                 => Oradb::Listener['start listener'],
}

or your own template on your own location

  template                => 'my_dbtemplate_11gR2_asm',
  puppetDownloadMntPoint  => '/vagrant', # 'oradb' etc

12c container and pluggable databases

oradb::database{ 'oraDb':
  oracleBase              => '/oracle',
  oracleHome              => '/oracle/product/12.1/db',
  version                 => '12.1',
  user                    => 'oracle',
  group                   => 'dba'
  downloadDir             => '/install',
  action                  => 'create',
  dbName                  => 'orcl',
  dbDomain                => 'example.com',
  sysPassword             => 'Welcome01',
  systemPassword          => 'Welcome01',
  characterSet            => 'AL32UTF8',
  nationalCharacterSet    => 'UTF8',
  sampleSchema            => 'FALSE',
  memoryPercentage        => '40',
  memoryTotal             => '800',
  databaseType            => 'MULTIPURPOSE',
  emConfiguration         => 'NONE',
  dataFileDestination     => '/oracle/oradata',
  recoveryAreaDestination => '/oracle/flash_recovery_area',
  initParams              => {'open_cursors'        => '1000',
                              'processes'           => '600',
                              'job_queue_processes' => '4' },
  containerDatabase       => true,   <|-------
}

oradb::database_pluggable{'pdb1':
  ensure                   => 'present',
  version                  => '12.1',
  oracle_home_dir          => '/oracle/product/12.1/db',
  user                     => 'oracle',
  group                    => 'dba',
  source_db                => 'orcl',
  pdb_name                 => 'pdb1',
  pdb_admin_username       => 'pdb_adm',
  pdb_admin_password       => 'Welcome01',
  pdb_datafile_destination => "/oracle/oradata/orcl/pdb1",
  create_user_tablespace   => true,
  log_output               => true,
}

# remove the pluggable database
oradb::database_pluggable{'pdb1':
  ensure                   => 'absent',
  version                  => '12.1',
  oracle_home_dir          => '/oracle/product/12.1/db',
  user                     => 'oracle',
  group                    => 'dba',
  source_db                => 'orcl',
  pdb_name                 => 'pdb1',
  pdb_datafile_destination => "/oracle/oradata/orcl/pdb1",
  log_output               => true,
}

or delete a database

oradb::database{ 'testDb_Delete':
  oracleBase              => '/oracle',
  oracleHome              => '/oracle/product/11.2/db',
  user                    => 'oracle',
  group                   => 'dba',
  downloadDir             => '/install',
  action                  => 'delete',
  dbName                  => 'test',
  sysPassword             => 'Welcome01',
  require                 => Oradb::Dbactions['start testDb'],
}

Database instance actions

db_control{'emrepos start':
  ensure                  => 'running', #running|start|abort|stop
  instance_name           => 'test',
  oracle_product_home_dir => '/oracle/product/11.2/db',
  os_user                 => 'oracle',
}

db_control{'emrepos stop':
  ensure                  => 'stop', #running|start|abort|stop
  instance_name           => 'test',
  oracle_product_home_dir => '/oracle/product/11.2/db',
  os_user                 => 'oracle',
}

# the old way
oradb::dbactions{ 'stop testDb':
  oracleHome              => '/oracle/product/11.2/db',
  user                    => 'oracle',
  group                   => 'dba',
  action                  => 'stop',
  dbName                  => 'test',
  require                 => Oradb::Database['testDb'],
}

oradb::dbactions{ 'start testDb':
  oracleHome              => '/oracle/product/11.2/db',
  user                    => 'oracle',
  group                   => 'dba',
  action                  => 'start',
  dbName                  => 'test',
  require                 => Oradb::Dbactions['stop testDb'],
}

# subscribe to changes
db_control{'emrepos restart':
  ensure                  => 'running', #running|start|abort|stop
  instance_name           => 'test',
  oracle_product_home_dir => '/oracle/product/11.2/db',
  os_user                 => 'oracle',
  refreshonly             => true,
  subscribe               => Init_param['emrepos/memory_target'],
}

oradb::autostartdatabase{ 'autostart oracle':
  oracleHome              => '/oracle/product/12.1/db',
  user                    => 'oracle',
  dbName                  => 'test',
  require                 => Oradb::Dbactions['start testDb'],
}

Tnsnames.ora

oradb::tnsnames{'orcl':
  oracleHome         => '/oracle/product/11.2/db',
  user               => 'oracle',
  group              => 'dba',
  server             => { myserver => { host => soadb.example.nl, port => '1521', protocol => 'TCP' }},
  connectServiceName => 'soarepos.example.nl',
  require            => Oradb::Dbactions['start oraDb'],
}

oradb::tnsnames{'test':
  oracleHome         => '/oracle/product/11.2/db',
  user               => 'oracle',
  group              => 'dba',
  server             => { myserver => { host => soadb.example.nl, port => '1525', protocol => 'TCP' }, { host => soadb2.example.nl, port => '1526', protocol => 'TCP' }},
  connectServiceName => 'soarepos.example.nl',
  connectServer      => 'DEDICATED',
  require            => Oradb::Dbactions['start oraDb'],
}

Grid install with ASM

  $all_groups = ['oinstall','dba' ,'oper','asmdba','asmadmin','asmoper']

  group { $all_groups :
    ensure      => present,
  }

  user { 'oracle' :
    ensure      => present,
    uid         => 500,
    gid         => 'oinstall',
    groups      => ['oinstall','dba','oper','asmdba'],
    shell       => '/bin/bash',
    password    => '$1$DSJ51vh6$4XzzwyIOk6Bi/54kglGk3.',
    home        => "/home/oracle",
    comment     => "This user oracle was created by Puppet",
    require     => Group[$all_groups],
    managehome  => true,
  }

  user { 'grid' :
    ensure      => present,
    uid         => 501,
    gid         => 'oinstall',
    groups      => ['oinstall','dba','asmadmin','asmdba','asmoper'],
    shell       => '/bin/bash',
    password    => '$1$DSJ51vh6$4XzzwyIOk6Bi/54kglGk3.',
    home        => "/home/grid",
    comment     => "This user grid was created by Puppet",
    require     => Group[$all_groups],
    managehome  => true,
  }

  ####### NFS example

  file { '/home/nfs_server_data':
    ensure  => directory,
    recurse => false,
    replace => false,
    mode    => '0775',
    owner   => 'grid',
    group   => 'asmadmin',
    require =>  User['grid'],
  }

  class { 'nfs::server':
    package => latest,
    service => running,
    enable  => true,
  }

  nfs::export { '/home/nfs_server_data':
    options => [ 'rw', 'sync', 'no_wdelay','insecure_locks','no_root_squash' ],
    clients => [ "*" ],
    require => [File['/home/nfs_server_data'],Class['nfs::server'],],
  }

  file { '/nfs_client':
    ensure  => directory,
    recurse => false,
    replace => false,
    mode    => '0775',
    owner   => 'grid',
    group   => 'asmadmin',
    require =>  User['grid'],
  }

  mounts { 'Mount point for NFS data':
    ensure  => present,
    source  => 'soadbasm:/home/nfs_server_data',
    dest    => '/nfs_client',
    type    => 'nfs',
    opts    => 'rw,bg,hard,nointr,tcp,vers=3,timeo=600,rsize=32768,wsize=32768,actimeo=0  0 0',
    require => [File['/nfs_client'],Nfs::Export['/home/nfs_server_data'],]
  }

  exec { "/bin/dd if=/dev/zero of=/nfs_client/asm_sda_nfs_b1 bs=1M count=7520":
    user      => 'grid',
    group     => 'asmadmin',
    logoutput => true,
    unless    => "/usr/bin/test -f /nfs_client/asm_sda_nfs_b1",
    require   => Mounts['Mount point for NFS data'],
  }
  exec { "/bin/dd if=/dev/zero of=/nfs_client/asm_sda_nfs_b2 bs=1M count=7520":
    user      => 'grid',
    group     => 'asmadmin',
    logoutput => true,
    unless    => "/usr/bin/test -f /nfs_client/asm_sda_nfs_b2",
    require   => [Mounts['Mount point for NFS data'],
                  Exec["/bin/dd if=/dev/zero of=/nfs_client/asm_sda_nfs_b1 bs=1M count=7520"]],
  }

  exec { "/bin/dd if=/dev/zero of=/nfs_client/asm_sda_nfs_b3 bs=1M count=7520":
    user      => 'grid',
    group     => 'asmadmin',
    logoutput => true,
    unless    => "/usr/bin/test -f /nfs_client/asm_sda_nfs_b3",
    require   => [Mounts['Mount point for NFS data'],
                  Exec["/bin/dd if=/dev/zero of=/nfs_client/asm_sda_nfs_b1 bs=1M count=7520"],
                  Exec["/bin/dd if=/dev/zero of=/nfs_client/asm_sda_nfs_b2 bs=1M count=7520"],],
  }

  exec { "/bin/dd if=/dev/zero of=/nfs_client/asm_sda_nfs_b4 bs=1M count=7520":
    user      => 'grid',
    group     => 'asmadmin',
    logoutput => true,
    unless    => "/usr/bin/test -f /nfs_client/asm_sda_nfs_b4",
    require   => [Mounts['Mount point for NFS data'],
                  Exec["/bin/dd if=/dev/zero of=/nfs_client/asm_sda_nfs_b1 bs=1M count=7520"],
                  Exec["/bin/dd if=/dev/zero of=/nfs_client/asm_sda_nfs_b2 bs=1M count=7520"],
                  Exec["/bin/dd if=/dev/zero of=/nfs_client/asm_sda_nfs_b3 bs=1M count=7520"],],
  }

  $nfs_files = ['/nfs_client/asm_sda_nfs_b1','/nfs_client/asm_sda_nfs_b2','/nfs_client/asm_sda_nfs_b3','/nfs_client/asm_sda_nfs_b4']

  file { $nfs_files:
    ensure  => present,
    owner   => 'grid',
    group   => 'asmadmin',
    mode    => '0664',
    require => Exec["/bin/dd if=/dev/zero of=/nfs_client/asm_sda_nfs_b4 bs=1M count=7520"],
  }
  ###### end of NFS example


  oradb::installasm{ 'db_linux-x64':
    version                => hiera('db_version'),
    file                   => hiera('asm_file'),
    gridType               => 'HA_CONFIG',
    gridBase               => hiera('grid_base_dir'),
    gridHome               => hiera('grid_home_dir'),
    oraInventoryDir        => hiera('oraInventory_dir'),
    userBaseDir            => '/home',
    user                   => hiera('grid_os_user'),
    group                  => 'asmdba',
    group_install          => 'oinstall',
    group_oper             => 'asmoper',
    group_asm              => 'asmadmin',
    sys_asm_password       => 'Welcome01',
    asm_monitor_password   => 'Welcome01',
    asm_diskgroup          => 'DATA',
    disk_discovery_string  => "/nfs_client/asm*",
    disks                  => "/nfs_client/asm_sda_nfs_b1,/nfs_client/asm_sda_nfs_b2",
    # disk_discovery_string  => "ORCL:*",
    # disks                  => "ORCL:DISK1,ORCL:DISK2",
    disk_redundancy        => "EXTERNAL",
    downloadDir            => hiera('oracle_download_dir'),
    remoteFile             => false,
    puppetDownloadMntPoint => hiera('oracle_source'),
  }

  oradb::opatchupgrade{'112000_opatch_upgrade_asm':
    oracleHome             => hiera('grid_home_dir'),
    patchFile              => 'p6880880_112000_Linux-x86-64.zip',
    csiNumber              => undef,
    supportId              => undef,
    opversion              => '11.2.0.3.6',
    user                   => hiera('grid_os_user'),
    group                  => 'oinstall',
    downloadDir            => hiera('oracle_download_dir'),
    puppetDownloadMntPoint => hiera('oracle_source'),
    require                => Oradb::Installasm['db_linux-x64'],
  }

  oradb::opatch{'19791420_grid_patch':
    ensure                 => 'present',
    oracleProductHome      => hiera('grid_home_dir'),
    patchId                => '19791420',
    patchFile              => 'p19791420_112040_Linux-x86-64.zip',
    clusterWare            => true,
    bundleSubPatchId       => '19121552', # sub patchid of bundle patch ( else I can't detect it)
    bundleSubFolder        => '19380115', # optional subfolder inside the patch zip
    user                   => hiera('grid_os_user'),
    group                  => 'oinstall',
    downloadDir            => hiera('oracle_download_dir'),
    ocmrf                  => true,
    require                => Oradb::Opatchupgrade['112000_opatch_upgrade_asm'],
    puppetDownloadMntPoint => hiera('oracle_source'),
  }

  oradb::installdb{ 'db_linux-x64':
    version                => hiera('db_version'),
    file                   => hiera('db_file'),
    databaseType           => 'EE',
    oraInventoryDir        => hiera('oraInventory_dir'),
    oracleBase             => hiera('oracle_base_dir'),
    oracleHome             => hiera('oracle_home_dir'),
    userBaseDir            => '/home',
    user                   => hiera('oracle_os_user'),
    group                  => 'dba',
    group_install          => 'oinstall',
    group_oper             => 'oper',
    downloadDir            => hiera('oracle_download_dir'),
    remoteFile             => false,
    puppetDownloadMntPoint => hiera('oracle_source'),
    # require                => Oradb::Opatch['18706472_grid_patch'],
    require                => Oradb::Opatch['19791420_grid_patch'],
  }

  oradb::opatchupgrade{'112000_opatch_upgrade_db':
    oracleHome             => hiera('oracle_home_dir'),
    patchFile              => 'p6880880_112000_Linux-x86-64.zip',
    csiNumber              => undef,
    supportId              => undef,
    opversion              => '11.2.0.3.6',
    user                   => hiera('oracle_os_user'),
    group                  => hiera('oracle_os_group'),
    downloadDir            => hiera('oracle_download_dir'),
    puppetDownloadMntPoint => hiera('oracle_source'),
    require                => Oradb::Installdb['db_linux-x64'],
  }

  oradb::opatch{'19791420_db_patch':
    ensure                 => 'present',
    oracleProductHome      => hiera('oracle_home_dir'),
    patchId                => '19791420',
    patchFile              => 'p19791420_112040_Linux-x86-64.zip',
    clusterWare            => true,
    bundleSubPatchId       => '19121551', #,'19121552', # sub patchid of bundle patch ( else I can't detect it)
    bundleSubFolder        => '19380115', # optional subfolder inside the patch zip
    user                   => hiera('oracle_os_user'),
    group                  => 'oinstall',
    downloadDir            => hiera('oracle_download_dir'),
    ocmrf                  => true,
    require                => Oradb::Opatchupgrade['112000_opatch_upgrade_db'],
    puppetDownloadMntPoint => hiera('oracle_source'),
  }

  oradb::opatch{'19791420_db_patch_2':
    ensure                 => 'present',
    oracleProductHome      => hiera('oracle_home_dir'),
    patchId                => '19791420',
    patchFile              => 'p19791420_112040_Linux-x86-64.zip',
    clusterWare            => false,
    bundleSubPatchId       => '19282021', # sub patchid of bundle patch ( else I can't detect it)
    bundleSubFolder        => '19282021', # optional subfolder inside the patch zip
    user                   => hiera('oracle_os_user'),
    group                  => 'oinstall',
    downloadDir            => hiera('oracle_download_dir'),
    ocmrf                  => true,
    require                => Oradb::Opatch['19791420_db_patch'],
    puppetDownloadMntPoint => hiera('oracle_source'),
  }

  # with the help of the oracle and easy-type module of Bert Hajee
  ora_asm_diskgroup{ 'RECO@+ASM':
    ensure          => 'present',
    au_size         => '1',
    compat_asm      => '11.2.0.0.0',
    compat_rdbms    => '10.1.0.0.0',
    diskgroup_state => 'MOUNTED',
    disks           => {'RECO_0000' => {'diskname' => 'RECO_0000', 'path' => '/nfs_client/asm_sda_nfs_b3'},
                        'RECO_0001' => {'diskname' => 'RECO_0001', 'path' => '/nfs_client/asm_sda_nfs_b4'}},
    redundancy_type => 'EXTERNAL',
    require         => Oradb::Opatch['19791420_db_patch_2'],
  }

  # based on a template
  oradb::database{ 'oraDb':
    oracleBase              => hiera('oracle_base_dir'),
    oracleHome              => hiera('oracle_home_dir'),
    version                 => hiera('dbinstance_version'),
    user                    => hiera('oracle_os_user'),
    group                   => hiera('oracle_os_group'),
    downloadDir             => hiera('oracle_download_dir'),
    action                  => 'create',
    dbName                  => hiera('oracle_database_name'),
    dbDomain                => hiera('oracle_database_domain_name'),
    sysPassword             => hiera('oracle_database_sys_password'),
    systemPassword          => hiera('oracle_database_system_password'),
    template                => 'dbtemplate_11gR2_asm',
    characterSet            => "AL32UTF8",
    nationalCharacterSet    => "UTF8",
    sampleSchema            => 'FALSE',
    memoryPercentage        => "40",
    memoryTotal             => "800",
    databaseType            => "MULTIPURPOSE",
    emConfiguration         => "NONE",
    storageType             => "ASM",
    asmSnmpPassword         => 'Welcome01',
    asmDiskgroup            => 'DATA',
    recoveryDiskgroup       => 'RECO',
    recoveryAreaDestination => 'RECO',
    require                 => [Oradb::Opatch['19791420_db_patch_2'],
                                Ora_asm_diskgroup['RECO@+ASM'],],
  }

  # or not based on a template
  oradb::database{ 'oraDb':
    oracleBase              => hiera('oracle_base_dir'),
    oracleHome              => hiera('oracle_home_dir'),
    version                 => hiera('dbinstance_version'),
    user                    => hiera('oracle_os_user'),
    group                   => hiera('oracle_os_group'),
    downloadDir             => hiera('oracle_download_dir'),
    action                  => 'create',
    dbName                  => hiera('oracle_database_name'),
    dbDomain                => hiera('oracle_database_domain_name'),
    sysPassword             => hiera('oracle_database_sys_password'),
    systemPassword          => hiera('oracle_database_system_password'),
    characterSet            => "AL32UTF8",
    nationalCharacterSet    => "UTF8",
    sampleSchema            => 'FALSE',
    memoryPercentage        => "40",
    memoryTotal             => "800",
    databaseType            => "MULTIPURPOSE",
    emConfiguration         => "NONE",
    storageType             => "ASM",
    asmSnmpPassword         => 'Welcome01',
    asmDiskgroup            => 'DATA',
    recoveryAreaDestination => 'DATA',
    require                 => Oradb::Opatch['19791420_db_patch_2'],
  }

Oracle Database Client

oradb::client{ '12.1.0.1_Linux-x86-64':
  version                => '12.1.0.1',
  file                   => 'linuxamd64_12c_client.zip',
  oracleBase             => '/oracle',
  oracleHome             => '/oracle/product/12.1/client',
  user                   => 'oracle',
  group                  => 'dba',
  group_install          => 'oinstall',
  downloadDir            => '/install',
  remoteFile             => true,
  puppetDownloadMntPoint => "puppet:///modules/oradb/",
  logoutput               => true,
}

or

oradb::client{ '11.2.0.1_Linux-x86-64':
  version                => '11.2.0.1',
  file                   => 'linux.x64_11gR2_client.zip',
  oracleBase             => '/oracle',
  oracleHome             => '/oracle/product/11.2/client',
  user                   => 'oracle',
  group                  => 'dba',
  group_install          => 'oinstall',
  downloadDir            => '/install',
  remoteFile             => false,
  puppetDownloadMntPoint => "/software",
  logoutput              => true,
}

Enteprise Mananager

oradb::installem{ 'em12104':
  version                     => '12.1.0.4',
  file                        => 'em12104_linux64',
  oracle_base_dir             => '/oracle',
  oracle_home_dir             => '/oracle/product/12.1/em',
  agent_base_dir              => '/oracle/product/12.1/agent',
  software_library_dir        => '/oracle/product/12.1/swlib',
  weblogic_user               => 'weblogic',
  weblogic_password           => 'Welcome01',
  database_hostname           => 'emdb.example.com',
  database_listener_port      => 1521,
  database_service_sid_name   => 'emrepos.example.com',
  database_sys_password       => 'Welcome01',
  sysman_password             => 'Welcome01',
  agent_registration_password => 'Welcome01',
  deployment_size             => 'SMALL',
  user                        => 'oracle',
  group                       => 'oinstall',
  download_dir                => '/install',
  zip_extract                 => true,
  puppet_download_mnt_point   => '/software',
  remote_file                 => false,
  log_output                  => true,
}

oradb::installem_agent{ 'em12104_agent':
  version                     => '12.1.0.4',
  source                      => 'https://10.10.10.25:7802/em/install/getAgentImage',
  install_type                => 'agentPull',
  install_platform            => 'Linux x86-64',
  oracle_base_dir             => '/oracle',
  agent_base_dir              => '/oracle/product/12.1/agent',
  agent_instance_home_dir     => '/oracle/product/12.1/agent/agent_inst',
  sysman_user                 => 'sysman',
  sysman_password             => 'Welcome01',
  agent_registration_password => 'Welcome01',
  agent_port                  => 1830,
  oms_host                    => '10.10.10.25',
  oms_port                    => 7802,
  em_upload_port              => 4903,
  user                        => 'oracle',
  group                       => 'dba',
  download_dir                => '/var/tmp/install',
  log_output                  => true,
}

oradb::installem_agent{ 'em12104_agent2':
  version                     => '12.1.0.4',
  source                      => '/var/tmp/install/agent.zip',
  install_type                => 'agentDeploy',
  oracle_base_dir             => '/oracle',
  agent_base_dir              => '/oracle/product/12.1/agent2',
  agent_instance_home_dir     => '/oracle/product/12.1/agent2/agent_inst',
  agent_registration_password => 'Welcome01',
  agent_port                  => 1832,
  oms_host                    => '10.10.10.25',
  em_upload_port              => 4903,
  user                        => 'oracle',
  group                       => 'dba',
  download_dir                => '/var/tmp/install',
  log_output                  => true,
}

Database configuration

In combination with the oracle puppet module from hajee you can create/change a database init parameter, tablespace,role or an oracle user

ora_init_param{'SPFILE/processes@soarepos':
  ensure => 'present',
  value  => '1000',
}

ora_init_param{'SPFILE/job_queue_processes@soarepos':
  ensure  => present,
  value   => '4',
}

db_control{'soarepos restart':
  ensure                  => 'running', #running|start|abort|stop
  instance_name           => hiera('oracle_database_name'),
  oracle_product_home_dir => hiera('oracle_home_dir'),
  os_user                 => hiera('oracle_os_user'),
  refreshonly             => true,
  subscribe               => [Ora_init_param['SPFILE/processes@soarepos'],
                              Ora_init_param['SPFILE/job_queue_processes@soarepos'],],
}

ora_tablespace {'JMS_TS@soarepos':
  ensure                    => present,
  datafile                  => 'jms_ts.dbf',
  size                      => 100M,
  logging                   => yes,
  autoextend                => on,
  next                      => 100M,
  max_size                  => 1G,
  extent_management         => local,
  segment_space_management  => auto,
}

ora_role {'APPS@soarepos':
  ensure    => present,
}

ora_user{'JMS@soarepos':
  ensure                    => present,
  temporary_tablespace      => temp,
  default_tablespace        => 'JMS_TS',
  password                  => 'jms',
  require                   => [Ora_tablespace['JMS_TS@soarepos'],
                                Ora_role['APPS@soarepos']],
  grants                    => ['SELECT ANY TABLE', 'CONNECT', 'CREATE TABLE', 'CREATE TRIGGER','APPS'],
  quotas                    => {
                                  "JMS_TS"  => 'unlimited'
                                },
}

Oracle GoldenGate 12.1.2 and 11.2.1

  $groups = ['oinstall','dba']

  group { $groups :
    ensure      => present,
    before      => User['ggate'],
  }

  user { 'ggate' :
    ensure      => present,
    gid         => 'dba',
    groups      => $groups,
    shell       => '/bin/bash',
    password    => '$1$DSJ51vh6$4XzzwyIOk6Bi/54kglGk3.',
    home        => "/home/ggate",
    comment     => "This user ggate was created by Puppet",
    managehome  => true,
  }

  oradb::goldengate{ 'ggate12.1.2':
    version                 => '12.1.2',
    file                    => '121200_fbo_ggs_Linux_x64_shiphome.zip',
    databaseType            => 'Oracle',
    databaseVersion         => 'ORA11g',
    databaseHome            => '/oracle/product/12.1/db',
    oracleBase              => '/oracle',
    goldengateHome          => "/oracle/product/12.1/ggate",
    managerPort             => 16000,
    user                    => 'ggate',
    group                   => 'dba',
    group_install           => 'oinstall',
    downloadDir             => '/install',
    puppetDownloadMntPoint  => hiera('oracle_source'),
    require                 => User['ggate'],
  }

  file { "/oracle/product/11.2.1" :
    ensure        => directory,
    recurse       => false,
    replace       => false,
    mode          => '0775',
    owner         => 'ggate',
    group         => 'dba',
    require       => Oradb::Goldengate['ggate12.1.2'],
  }

  oradb::goldengate{ 'ggate11.2.1':
    version                 => '11.2.1',
    file                    => 'ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip',
    tarFile                 => 'fbo_ggs_Linux_x64_ora11g_64bit.tar',
    goldengateHome          => "/oracle/product/11.2.1/ggate",
    user                    => 'ggate',
    group                   => 'dba',
    downloadDir             => '/install',
    puppetDownloadMntPoint  => hiera('oracle_source'),
    require                 => File["/oracle/product/11.2.1"],
  }

  oradb::goldengate{ 'ggate11.2.1_java':
    version                 => '11.2.1',
    file                    => 'V38714-01.zip',
    tarFile                 => 'ggs_Adapters_Linux_x64.tar',
    goldengateHome          => "/oracle/product/11.2.1/ggate_java",
    user                    => 'ggate',
    group                   => 'dba',
    group_install           => 'oinstall',
    downloadDir             => '/install',
    puppetDownloadMntPoint  => hiera('oracle_source'),
    require                 => File["/oracle/product/11.2.1"],
  }

Oracle SOA Suite Repository Creation Utility (RCU)

product =

  • soasuite
  • webcenter
  • all

RCU examples

soa suite repository

oradb::rcu{'DEV_PS6':
  rcuFile          => 'ofm_rcu_linux_11.1.1.7.0_32_disk1_1of1.zip',
  product          => 'soasuite',
  version          => '11.1.1.7',
  oracleHome       => '/oracle/product/11.2/db',
  user             => 'oracle',
  group            => 'dba',
  downloadDir      => '/install',
  action           => 'create',
  dbServer         => 'dbagent1.alfa.local:1521',
  dbService        => 'test.oracle.com',
  sysPassword      => 'Welcome01',
  schemaPrefix     => 'DEV',
  reposPassword    => 'Welcome02',
}

webcenter repository with a fixed temp tablespace

oradb::rcu{'DEV2_PS6':
  rcuFile          => 'ofm_rcu_linux_11.1.1.7.0_32_disk1_1of1.zip',
  product          => 'webcenter',
  version          => '11.1.1.7',
  oracleHome       => '/oracle/product/11.2/db',
  user             => 'oracle',
  group            => 'dba',
  downloadDir      => '/install',
  action           => 'create',
  dbServer         => 'dbagent1.alfa.local:1521',
  dbService        => 'test.oracle.com',
  sysPassword      => 'Welcome01',
  schemaPrefix     => 'DEV',
  tempTablespace   => 'TEMP',
  reposPassword    => 'Welcome02',
}

delete a repository

oradb::rcu{'Delete_DEV3_PS5':
  rcuFile          => 'ofm_rcu_linux_11.1.1.6.0_disk1_1of1.zip',
  product          => 'soasuite',
  version          => '11.1.1.6',
  oracleHome       => '/oracle/product/11.2/db',
  user             => 'oracle',
  group            => 'dba',
  downloadDir      => '/install',
  action           => 'delete',
  dbServer         => 'dbagent1.alfa.local:1521',
  dbService        => 'test.oracle.com',
  sysPassword      => 'Welcome01',
  schemaPrefix     => 'DEV3',
  reposPassword    => 'Welcome02',
}

OIM, OAM repository, OIM needs an Oracle Enterprise Edition database

oradb::rcu{'DEV_1112':
  rcuFile                => 'V37476-01.zip',
  product                => 'oim',
  version                => '11.1.2.1',
  oracleHome             => '/oracle/product/11.2/db',
  user                   => 'oracle',
  group                  => 'dba',
  downloadDir            => '/data/install',
  action                 => 'create',
  dbServer               => 'oimdb.alfa.local:1521',
  dbService              => 'oim.oracle.com',
  sysPassword            => hiera('database_test_sys_password'),
  schemaPrefix           => 'DEV',
  reposPassword          => hiera('database_test_rcu_dev_password'),
  puppetDownloadMntPoint => $puppetDownloadMntPoint,
  logoutput              => true,
  require                => Oradb::Dbactions['start oimDb'],
 }

Solaris 10 kernel, ulimits and required packages

exec { "create /cdrom/unnamed_cdrom":
  command => "/usr/bin/mkdir -p /cdrom/unnamed_cdrom",
  creates => "/cdrom/unnamed_cdrom",
}

mount { "/cdrom/unnamed_cdrom":
  device   => "/dev/dsk/c0t1d0s2",
  fstype   => "hsfs",
  ensure   => "mounted",
  options  => "ro",
  atboot   => true,
  remounts => false,
  require  => Exec["create /cdrom/unnamed_cdrom"],
}

$install = [
             'SUNWarc','SUNWbtool','SUNWcsl',
             'SUNWdtrc','SUNWeu8os','SUNWhea',
             'SUNWi1cs', 'SUNWi15cs',
             'SUNWlibC','SUNWlibm','SUNWlibms',
             'SUNWsprot','SUNWpool','SUNWpoolr',
             'SUNWtoo','SUNWxwfnt'
            ]

package { $install:
  ensure    => present,
  adminfile => "/vagrant/pkgadd_response",
  source    => "/cdrom/unnamed_cdrom/Solaris_10/Product/",
  require   => [Exec["create /cdrom/unnamed_cdrom"],
                Mount["/cdrom/unnamed_cdrom"]
               ],
}
package { 'SUNWi1of':
  ensure    => present,
  adminfile => "/vagrant/pkgadd_response",
  source    => "/cdrom/unnamed_cdrom/Solaris_10/Product/",
  require   => Package[$install],
}


# pkginfo -i SUNWarc SUNWbtool SUNWhea SUNWlibC SUNWlibm SUNWlibms SUNWsprot SUNWtoo SUNWi1of SUNWi1cs SUNWi15cs SUNWxwfnt SUNWcsl SUNWdtrc
# pkgadd -d /cdrom/unnamed_cdrom/Solaris_10/Product/ -r response -a response SUNWarc SUNWbtool SUNWhea SUNWlibC SUNWlibm SUNWlibms SUNWsprot SUNWtoo SUNWi1of SUNWi1cs SUNWi15cs SUNWxwfnt SUNWcsl SUNWdtrc


$all_groups = ['oinstall','dba' ,'oper']

group { $all_groups :
  ensure      => present,
}

user { 'oracle' :
  ensure      => present,
  uid         => 500,
  gid         => 'oinstall',
  groups      => ['oinstall','dba','oper'],
  shell       => '/bin/bash',
  password    => '$1$DSJ51vh6$4XzzwyIOk6Bi/54kglGk3.',
  home        => "/home/oracle",
  comment     => "This user oracle was created by Puppet",
  require     => Group[$all_groups],
  managehome  => true,
}

$execPath     = "/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:"

exec { "projadd max-shm-memory":
  command => "projadd -p 102  -c 'ORADB' -U oracle -G dba  -K 'project.max-shm-memory=(privileged,4G,deny)' ORADB",
  require => [ User["oracle"],
               Package['SUNWi1of'],
               Package[$install],
             ],
  unless  => "projects -l | grep -c ORADB",
  path    => $execPath,
}

exec { "projmod max-sem-ids":
  command     => "projmod -s -K 'project.max-sem-ids=(privileged,100,deny)' ORADB",
  subscribe   => Exec["projadd max-shm-memory"],
  require     => Exec["projadd max-shm-memory"],
  refreshonly => true,
  path        => $execPath,
}

exec { "projmod max-shm-ids":
  command     => "projmod -s -K 'project.max-shm-ids=(privileged,100,deny)' ORADB",
  require     => Exec["projmod max-sem-ids"],
  subscribe   => Exec["projmod max-sem-ids"],
  refreshonly => true,
  path        => $execPath,
}

exec { "projmod max-sem-nsems":
  command     => "projmod -s -K 'process.max-sem-nsems=(privileged,256,deny)' ORADB",
  require     => Exec["projmod max-shm-ids"],
  subscribe   => Exec["projmod max-shm-ids"],
  refreshonly => true,
  path        => $execPath,
}

exec { "projmod max-file-descriptor":
  command     => "projmod -s -K 'process.max-file-descriptor=(basic,65536,deny)' ORADB",
  require     => Exec["projmod max-sem-nsems"],
  subscribe   => Exec["projmod max-sem-nsems"],
  refreshonly => true,
  path        => $execPath,
}

exec { "projmod max-stack-size":
  command     => "projmod -s -K 'process.max-stack-size=(privileged,32MB,deny)' ORADB",
  require     => Exec["projmod max-file-descriptor"],
  subscribe   => Exec["projmod max-file-descriptor"],
  refreshonly => true,
  path        => $execPath,
}

exec { "usermod oracle":
  command     => "usermod -K project=ORADB oracle",
  require     => Exec["projmod max-stack-size"],
  subscribe   => Exec["projmod max-stack-size"],
  refreshonly => true,
  path        => $execPath,
}

exec { "ndd 1":
  command => "ndd -set /dev/tcp tcp_smallest_anon_port 9000",
  require => Exec["usermod oracle"],
  path    => $execPath,
}
exec { "ndd 2":
  command => "ndd -set /dev/tcp tcp_largest_anon_port 65500",
  require => Exec["ndd 1"],
  path    => $execPath,
}

exec { "ndd 3":
  command => "ndd -set /dev/udp udp_smallest_anon_port 9000",
  require => Exec["ndd 2"],
  path    => $execPath,
}

exec { "ndd 4":
  command => "ndd -set /dev/udp udp_largest_anon_port 65500",
  require => Exec["ndd 3"],
  path    => $execPath,
}

exec { "ulimit -S":
  command => "ulimit -S -n 4096",
  require => Exec["ndd 4"],
  path    => $execPath,
}

exec { "ulimit -H":
  command => "ulimit -H -n 65536",
  require => Exec["ulimit -S"],
  path    => $execPath,
}

Solaris 11 kernel, ulimits and required packages

package { ['shell/ksh', 'developer/assembler']:
  ensure => present,
}

$install  = "pkg:/group/prerequisite/oracle/oracle-rdbms-server-12-1-preinstall"

package { $install:
  ensure  => present,
}

$groups = ['oinstall','dba' ,'oper' ]

group { $groups :
  ensure      => present,
}

user { 'oracle' :
  ensure      => present,
  uid         => 500,
  gid         => 'dba',
  groups      => $groups,
  shell       => '/bin/bash',
  password    => '$1$DSJ51vh6$4XzzwyIOk6Bi/54kglGk3.',
  home        => "/export/home/oracle",
  comment     => "This user oracle was created by Puppet",
  require     => Group[$groups],
  managehome  => true,
}

$execPath     = "/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:"

exec { "projadd group.dba":
  command => "projadd -U oracle -G dba -p 104 group.dba",
  require => User["oracle"],
  unless  => "projects -l | grep -c group.dba",
  path    => $execPath,
}

exec { "usermod oracle":
  command => "usermod -K project=group.dba oracle",
  require => [User["oracle"],Exec["projadd group.dba"],],
  path    => $execPath,
}

exec { "projmod max-shm-memory":
  command => "projmod -sK 'project.max-shm-memory=(privileged,4G,deny)' group.dba",
  require => [User["oracle"],Exec["projadd group.dba"],],
  path    => $execPath,
}

exec { "projmod max-sem-ids":
  command     => "projmod -sK 'project.max-sem-ids=(privileged,100,deny)' group.dba",
  require     => Exec["projadd group.dba"],
  path        => $execPath,
}

exec { "projmod max-shm-ids":
  command     => "projmod -s -K 'project.max-shm-ids=(privileged,100,deny)' group.dba",
  require     => Exec["projadd group.dba"],
  path        => $execPath,
}

exec { "projmod max-sem-nsems":
  command     => "projmod -sK 'process.max-sem-nsems=(privileged,256,deny)' group.dba",
  require     => Exec["projadd group.dba"],
  path        => $execPath,
}

exec { "projmod max-file-descriptor":
  command     => "projmod -sK 'process.max-file-descriptor=(basic,65536,deny)' group.dba",
  require     => Exec["projadd group.dba"],
  path        => $execPath,
}

exec { "projmod max-stack-size":
  command     => "projmod -sK 'process.max-stack-size=(privileged,32MB,deny)' group.dba",
  require     => Exec["projadd group.dba"],
  path        => $execPath,
}

exec { "ipadm smallest_anon_port tcp":
  command     => "ipadm set-prop -p smallest_anon_port=9000 tcp",
  path        => $execPath,
}
exec { "ipadm smallest_anon_port udp":
  command     => "ipadm set-prop -p smallest_anon_port=9000 udp",
  path        => $execPath,
}
exec { "ipadm largest_anon_port tcp":
  command     => "ipadm set-prop -p largest_anon_port=65500 tcp",
  path        => $execPath,
}
exec { "ipadm largest_anon_port udp":
  command     => "ipadm set-prop -p largest_anon_port=65500 udp",
  path        => $execPath,
}

exec { "ulimit -S":
  command => "ulimit -S -n 4096",
  path    => $execPath,
}

exec { "ulimit -H":
  command => "ulimit -H -n 65536",
  path    => $execPath,
}
Advertisements

Foreign Data Wrapper for Oracle “https://github.com/gloppasglop/oracle”

Oracle_fdw is a PostgreSQL extension that provides a Foreign Data Wrapper for
easy and efficient access to Oracle databases, including pushdown of WHERE
conditions and required columns as well as comprehensive EXPLAIN support.

oracle_fdw was written by Laurenz Albe <laurenz.albe@wien.gv.at>

This README contains the following sections:

1. Cookbook
2. Objects created by the extension
3. Options
4. Usage
5. Installation Requirements
6. Installation
7. Internals
8. Problems
9. Support

1. Cookbook
===========

This is a simple example how to use oracle_fdw.
More detailed information will be provided in the sections "Options" and
"Usage" below.  You should also read the PostgreSQL documentation on
foreign data and the commands used in the example.

For the sake of this example, let's assume you can connect as operating system
user "postgres" (or whoever starts the PostgreSQL server) with the following
command:

  sqlplus orauser/orapwd@//dbserver.mydomain.com/ORADB

That means that the Oracle client and the environment is set up correctly.
I also assume that oracle_fdw has been compiled and installed (see section
"Installation" below).

We want to access a table defined like this:

SQL> DESCRIBE oratab
 Name                            Null?    Type
 ------------------------------- -------- ------------
 ID                              NOT NULL NUMBER(5)
 TEXT                                     VARCHAR2(30)
 FLOATING                        NOT NULL NUMBER(7,2)

Then configure oracle_fdw as PostgreSQL superuser like this:

pgdb=# CREATE EXTENSION oracle_fdw;
pgdb=# CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw
          OPTIONS (dbserver '//dbserver.mydomain.com/ORADB');
pgdb=# GRANT USAGE ON FOREIGN SERVER oradb TO pguser;

(You can use other naming methods or local connections, see the description of
option "dbserver" below.)

Then you can connect to PostgreSQL as "pguser" and define:

pgdb=> CREATE USER MAPPING FOR pguser SERVER oradb
          OPTIONS (user 'orauser', password 'orapwd');

(You can use external authentication to avoid storing Oracle passwords;
see below.)

pgdb=> CREATE FOREIGN TABLE oratab (
          id        integer           OPTIONS (key 'true')  NOT NULL,
          text      character varying(30),
          floating  double precision  NOT NULL
       ) SERVER oradb OPTIONS (schema 'ORAUSER', table 'ORATAB');

(Remember that table and schema name -- the latter is optional -- must
normally be in uppercase.)

Now you can use the table like a regular PostgreSQL table.

2. Objects created by the extension
===================================

FUNCTION oracle_fdw_handler() RETURNS fdw_handler
  Oracle foreign data wrapper handler

FUNCTION oracle_fdw_validator(text[], oid) RETURNS void
  Oracle foreign data wrapper options validator

These functions are the handler and the validator function necessary to create
a foreign data wrapper.

The extension automatically creates a foreign data wrapper named "oracle_fdw".
Normally that's all you need and you can proceed to define foreign servers.
You can create additional Oracle foreign data wrappers, for example if you
need to set the "nls_lang" option (you can alter the existing "oracle_fdw"
wrapper, but all modifications will be lost after a dump/restore).

FUNCTION oracle_close_connections() RETURNS void
  closes all open Oracle connections

This function can be used to close all open Oracle connections in this session.
See "Usage" below for when this might be useful.

FUNCTION oracle_diag(name DEFAULT NULL) RETURNS text

This function is useful for diagnostic purposes only.
It will return the versions of oracle_fdw, PostgreSQL server and Oracle client.
If called with no argument or NULL, it will additionally return the values of
some environment variables used for establishing Oracle connections.
If called with the name of a foreign server, it will additionally return
the Oracle server version.

3. Options
==========

Foreign data wrapper options
----------------------------

(Caution: If you modify the default foreign data wrapper "oracle_fdw",
any changes will be lost upon dump/restore.  Create a new foreign data wrapper
if you want the options to be persistent.  The SQL script shipped with the
software contains a CREATE FOREIGN DATA WRAPPER statement you can use.)

- nls_lang (optional)

  Sets the NLS_LANG environment variable for Oracle to this value.
  NLS_LANG is in the form <language>_<territory>.<charset> (for example
  AMERICAN_AMERICA.AL32UTF8).  This must match your database encoding.
  When this value is not set, oracle_fdw will automatically do the right
  thing if it can and issue a warning if it cannot.
  Set this only if you know what you are doing.  See "Problems" below.

Foreign server options
----------------------

- dbserver (required)

  The Oracle database connection string for the remote database.
  This can be in any of the forms that Oracle supports as long as your
  Oracle client is configured accordingly.
  Set this to an empty string for local ("BEQUEATH") connections.

User mapping options
--------------------

- user (required)

  The Oracle user name for the session.
  Set this to an empty string for "external authentication" if you don't
  want to store Oracle credentials in the PostgreSQL database (one simple way
  is to use an "external password store").

- password (required)

  The password for the Oracle user.

Foreign table options
---------------------

- table (required)

  The Oracle table name.  This name must be written exactly as it occurs in
  Oracle's system catalog, so normally consist of uppercase letters only.

- schema (optional)

  The table's schema (or owner).  Useful to access tables that do not belong
  to the connecting Oracle user.  This name must be written exactly as it
  occurs in Oracle's system catalog, so normally consist of uppercase letters
  only.

- plan_costs (optional, defaults to "off")

  If set to yes/on/true, Oracle's cost estimates will be used.  The problem is
  that Oracle gives good estimates for the result size, but not for the cost
  of the execution (the only estimate you can get is "execution time" in second
  granularity).
  Since this cost estimate is almost useless but expensive to collect,
  oracle_fdw by default does not bother to get Oracle's cost estimates and
  estimates the cost to 10000+10*(row count) regardless of the actual query.

  From PostgreSQL 9.2 on, it is usually better to gather statistics on the
  foreign table (see ANALYZE below) and keep this option disabled.

  Turn this on only if a) query execution is expensive and b) it has a positive
  influence on PostgreSQL query planning.

- max_long (optional, defaults to "32767")

  The maximal length of any LONG or LONG RAW columns in the Oracle table.
  Possible values are integers between 1 and 1073741823 (the maximal size of a
  bytea in PostgreSQL).  This amount of memory will be allocated at least
  twice, so large values will consume a lot of memory.
  If "max_long" is less than the length of the longest value retrieved,
  you will receive the error message "ORA-01406: fetched column value was
  truncated".

- readonly (optional, defaults to "false")

  INSERT, UPDATE and DELETE is only allowed on tables where this option is
  not set to yes/on/true.  Since these statements can only be executed from
  PostgreSQL 9.3 on, setting this option has no effect on earlier versions.
  It might still be a good idea to set it in PostgreSQL 9.2 and earlier
  on tables that you do not wish to be changed, to be prepared for an upgrade
  to PostgreSQL 9.3 or later.

Column options (from PostgreSQL 9.2 on)
---------------------------------------

- key (optional, defaults to "false")

  If set to yes/on/true, the corresponding column on the foreign Oracle table
  is considered a primary key column.
  For UPDATE and DELETE to work, you must set this option on all columns
  that belong to the table's primary key.

4. Usage
========

Oracle permissions
------------------

The Oracle user will obviously need CREATE SESSION privilege and the right
to select from the table or view in question.

For EXPLAIN VERBOSE (and query planning if "plan_costs" is turned on),
the user will also need SELECT privileges on V$SQL and V$SQL_PLAN.

Connections
-----------

oracle_fdw caches Oracle connections because it is expensive to create an
Oracle session for each individual query.  All connections are automatically
closed when the PostgreSQL session ends.

The function oracle_close_connections() can be used to close all cached Oracle
connections.  This can be useful for long-running sessions that don't access
foreign tables all the time and want to avoid blocking the resources needed
by an open Oracle connection.
Don't call this function inside a transaction that modifies Oracle data.

Columns
-------

When you define a foreign table, the columns of the Oracle table are mapped
to the PostgreSQL columns in the order of their definition.

oracle_fdw will only include those columns in the Oracle query that are
actually needed by the PostgreSQL query.

The PostgreSQL table can have more or less columns than the Oracle table.
If it has more columns, and these columns are used, you will receive a warning
and NULL values will be returned.

If you want to UPDATE or DELETE, make sure that the "key" option is set on all
columns that belong to the table's primary key.  Failure to do so will result
in errors.

Data types
----------

You must define the PostgreSQL columns with data types that oracle_fdw can
translate (see the conversion table below).  This restriction is only enforced
if the column actually gets used, so you can define "dummy" columns for
untranslatable data types as long as you don't access them (this trick only
works with SELECT, not when modifying foreign data).  If an Oracle value
exceeds the size of the PostgreSQL column (e.g., the length of a varchar
column or the maximal integer value), you will receive a runtime error.

These conversions are automatically handled by oracle_fdw:

Oracle type                    | Possible PostgreSQL types
-------------------------------+---------------------------------------------
CHAR                           | char, varchar, text
NCHAR                          | char, varchar, text
VARCHAR                        | char, varchar, text
VARCHAR2                       | char, varchar, text
NVARCHAR2                      | char, varchar, text
CLOB                           | char, varchar, text
LONG                           | char, varchar, text
RAW                            | uuid, bytea
BLOB                           | bytea
BFILE                          | bytea (read-only)
LONG RAW                       | bytea
NUMBER                         | numeric, float4, float8, char, varchar, text
NUMBER(n,m) with m<=0          | numeric, float4, float8, int2, int4, int8,
                               |   boolean, char, varchar, text
FLOAT                          | numeric, float4, float8, char, varchar, text
BINARY_FLOAT                   | numeric, float4, float8, char, varchar, text
BINARY_DOUBLE                  | numeric, float4, float8, char, varchar, text
DATE                           | date, timestamp, timestamptz, char,
                               |   varchar, text
TIMESTAMP                      | date, timestamp, timestamptz, char,
                               |   varchar, text
TIMESTAMP WITH TIME ZOME       | date, timestamp, timestamptz, char,
                               |   varchar, text
TIMESTAMP WITH LOCAL TIME ZOME | date, timestamp, timestamptz, char,
                               |   varchar, text
INTERVAL YEAR TO MONTH         | interval, char, varchar, text
INTERVAL DAY TO SECOND         | interval, char, varchar, text
MDSYS.SDO_GEOMETRY             | geometry (see "PostGIS support" below)

If a NUMBER is converted to a boolean, "0" means "false", everything else "true".

NCLOB is currently not supported because Oracle cannot automatically convert
it to the client encoding.

If you need conversions exceeding the above, define an appropriate view in
Oracle or PostgreSQL.

WHERE conditions
----------------

PostgreSQL will use all applicaple parts of the WHERE clause as a filter
for the scan.  The Oracle query that oracle_fdw constructs will contain a WHERE
clause corresponding to these filter criteria whenever such a condition can
safely be translated to Oracle SQL.  This feature, also known as "push-down
of WHERE clauses", can greatly reduce the number of rows retrieved from Oracle
and may enable Oracle's optimizer to choose a good plan for accessing the
required tables.

To make use of that, try to use simple conditions for the foreign table.
Choose PostgreSQL column data types that correspond to Oracle's types,
because otherwise conditions cannot be translated.

The expressions "now()", "transaction_timestamp()", "current_timestamp",
"current_date" and "localtimestamp" will be translated correctly.

The output of EXPLAIN will show the Oracle query used, so you can see which
conditions were translated to Oracle and how.

Modifying foreign data
----------------------

From PostgreSQL 9.3 on, oracle_fdw supports INSERT, UPDATE and DELETE on
foreign tables.  This is allowed by default (also in databases upgraded
from an earlier PostgreSQL release) and can be disabled by setting the
"readonly" table option.

For UPDATE and DELETE to work, the columns corresponding to the primary
key columns of the Oracle table must have the "key" column option set.
These columns are used to identify a foreign table row, so make sure that
the option is set on *all* columns that belong to the primary key.

If you omit a foreign table column during INSERT, that column is set to
the value defined in the DEFAULT clause on the PostgreSQL foreign table
(or NULL if there is no DEFAULT clause).  DEFAULT clauses on the
corresponding Oracle columns are not used.
If the PostgreSQL foreign table does not include all columns of the
Oracle table, the Oracle DEFAULT clauses will be used for the columns not
included in the foreign table definition.

The RETURNING clause on INSERT, UPDATE and DELETE is supported except
for columns with Oracle data types LONG and LONG RAW (Oracle doesn't support
these data types in the RETURNING clause).

Triggers on foreign tables are supported from PostgreSQL 9.4.
Triggers defined with AFTER and FOR EACH ROW require that the foreign table
has no columns with Oracle data type LONG or LONG RAW.  This is because
such triggers make use of the RETURNING clause mentioned above.

While modifying foreign data works, the performance is not particularly
good, specifically when many rows are affected, because (owing to the way
foreign data wrappers work) each row has to be treated individually.

Transactions are forwarded to Oracle, so BEGIN, COMMIT, ROLLBACK and
SAVEPOINT work as expected.  Prepared statements involving Oracle are
not supported.  See "Internals" for details.

Since oracle_fdw uses serialized transactions, it is possible that
data modifying statements lead to a serialization failure:

  ORA-08177: can't serialize access for this transaction

This can happen if concurrent transactions modify the table and gets more
likely in long running transactions.  Such errors can be identified by their
SQLSTATE (40001).  An application using oracle_fdw should retry transactions
that fail with this error.

EXPLAIN
-------

PostgreSQL's EXPLAIN will show the query that is actually issued to Oracle.
EXPLAIN VERBOSE will show Oracle's execution plan.

ANALYZE
-------

From PostgreSQL version 9.2 on, you can use ANALYZE to gather statistics
on a foreign table.  This is supported by oracle_fdw.
Statistics will be used for query planning, and for many queries will
result in good (and much faster) row count estimates even if the table option
"plan_costs" is turned off as long as the statistics are accurate.

Keep in mind that analyzing an Oracle foreign table will result in a full
sequential table scan.

PostGIS support
---------------

The data type "geometry" is only available when PostGIS is installed.

The only supported geometry types are POINT, LINE, POLYGON, MULTIPOINT,
MULTILINE and MULTIPOLYGON in two and three dimensions.
Empty PostGIS geometries are not supported because they have no equivalent
in Oracle Spatial.

NULL values for Oracle SRID will be converted to 0 and vice versa.
For other conversions between Oracle SRID and PostGIS SRID, create a file
"srid.map" in the PostgreSQL "share" directory.  Each line of this file
shall contain an Oracle SRID and the corresponding PostGIS SRID, separated
by whitespace.  Keep the file small for good performance.

Support for IMPORT FOREIGN SCHEMA
---------------------------------

From PostgreSQL 9.5 on, IMPORT FOREIGN SCHEMA is supported to bulk import
table definitions for all tables in an Oracle schema.
In addition to the documentation of IMPORT FOREIGN SCHEMA, consider the
following:

- IMPORT FOREIGN SCHEMA will create foreign tables for all objects found in
  ALL_TAB_COLUMNS.  That includes tables, views and materialized views,
  but not synonyms.

- There is one supported option for IMPORT FOREIGN SCHEMA named "case" that
  controls case folding for table and column names during import.
  The possible values are:
  "keep": leave the names as they are in Oracle, usually in upper case.
  "lower": translate all table and column names to lower case.
  "smart": only translate names that are all upper case in Oracle
           (this is the default).

- The Oracle schema name must be written exactly as it is in Oracle, so
  normally in upper case.  Since PostgreSQL translates names to lower case
  before processing, you must protect the schema name with double quotes
  (for example "SCOTT").

- Table names in the LIMIT TO or EXCEPT clause must be written as they
  will appear in PostgreSQL after the case folding described above.

5. Installation Requirements
============================

oracle_fdw should compile and run on any platform supported by PostgreSQL and
Oracle client, although I could only test it on Linux and Windows.

PostgreSQL 9.1 or better is required.
Support for ANALYZE is available from PostgreSQL 9.2 on.
Support for INSERT, UPDATE and DELETE is available from PostgreSQL 9.3 on.

Oracle client version 10.1 or better is required.
oracle_fdw can be built and used with Oracle Instant Client as well as with
Oracle Client and Server installations installed with Universal Installer.
Binaries compiled with Oracle Client 10 can be used with later client versions
without recompilation or relink.

The supported Oracle server versions depend on the used client version (see the
Oracle Client/Server Interoperability Matrix in support document 207303.1).
For maximum coverage use Oracle Client 10.2.0.5, as this will allow you to
connect to every server version from 8.1.7 to 12.1.0 except 9.0.1.

It is advisable to use the latest Patch Set on both Oracle client and server,
particularly with desupported Oracle versions.
For a list of Oracle bugs that are known to affect oracle_fdw's usability,
see the "Problems" section below.

6. Installation
===============

If you use a binary distribution of oracle_fdw, skip to "Installing the
extension" below.

Building oracle_fdw:
--------------------

oracle_fdw has been written as a PostgreSQL extension and uses the Extension
Building Infrastructure "PGXS".  It should be easy to install.

You will need PostgreSQL headers and PGXS installed (if your PostgreSQL was
installed with packages, install the development package).
You need to install Oracle's C header files as well (SDK package for Instant
Client).

Make sure that PostgreSQL is configured "--without-ldap" (at least the server).
See "Problems" below.

Make sure that "pg_config" is in the PATH (test with "pg_config --pgxs").
Set the environment variable ORACLE_HOME to the location of the Oracle
installation.

Unpack the source code of oracle_fdw and change into the directory.
Then the software installation should be as simple as

  $ make
  $ make install

For the second step you need write permission on the directories where
PostgreSQL is installed.

If you want to build oracle_fdw in a source tree of PostgreSQL, use

 $ make NO_PGXS=1

Installing the extension:
-------------------------

Make sure that the oracle_fdw shared library is installed in the PostgreSQL
library directory and that oracle_fdw.control and the SQL files are in
the PostgreSQL extension directory.

Since the Oracle client shared library is probably not in the standard
library path, you have to make sure that the PostgreSQL server will be able
to find it.  How this is done varies from operating system to operating
system; on Linux you can set LD_LIBRARY_PATH or use /etc/ld.so.conf.

Make sure that all necessary Oracle environment variables are set in the
environment of the PostgreSQL server process (ORACLE_HOME if you don't use
Instant Client, TNS_ADMIN if you have configuration files, etc.)

To install the extension in a database, connect as superuser and

  CREATE EXTENSION oracle_fdw;

That will define the required functions and create a foreign data wrapper.

To upgrade from an oracle_fdw version before 1.0.0, use

  ALTER EXTENSION oracle_fdw UPDATE;

Running the regression tests:
-----------------------------

Unless you are developing oracle_fdw or want to test its functionality
on an exotic platform, you don't have to do that.

For the regression tests to work, you must have a PostgreSQL cluster
(9.3 or better) and an Oracle server (10.1 or better with Locator or Spatial)
running, and the oracle_fdw binaries must be installed.
The regression tests will create a database called "contrib_regression" and
run a number of tests.  For the PostGIS regression tests to succeed,
the PostGIS binaries must be installed.

The Oracle database must be prepared as follows:
- A user "scott" with password "tiger" must exist (unless you want to edit
  the regression test scripts).  The user needs CREATE SESSION and CREATE TABLE
  system privileges and enough quota on its default tablespace, as well as
  SELECT privileges on V$SQL and V$SQL_PLAN.
- Two tables must be created as follows:
  CREATE TABLE scott.typetest1 (
     id  NUMBER(5)
        CONSTRAINT typetest1_pkey PRIMARY KEY,
     c   CHAR(10 CHAR),
     nc  NCHAR(10),
     vc  VARCHAR2(10 CHAR),
     nvc NVARCHAR2(10),
     lc  CLOB,
     r   RAW(10),
     u   RAW(16),
     lb  BLOB,
     lr  LONG RAW,
     b   NUMBER(1),
     num NUMBER(7,5),
     fl  BINARY_FLOAT,
     db  BINARY_DOUBLE,
     d   DATE,
     ts  TIMESTAMP WITH TIME ZONE,
     ids INTERVAL DAY TO SECOND,
     iym INTERVAL YEAR TO MONTH
  ) SEGMENT CREATION IMMEDIATE;

  CREATE TABLE scott.gis (
     id  NUMBER(5) PRIMARY KEY,
     g   MDSYS.SDO_GEOMETRY
  ) SEGMENT CREATION IMMEDIATE;

Set the environment for the PostgreSQL server so that it can establish an
Oracle connection without connect string:
If the Oracle server is on the same machine, set the environment variables
ORACLE_SID and ORACLE_HOME appropriately, for a remote server set the
environment variable TWO_TASK (or LOCAL on Windows) to the connect string.

The regression tests are run as follows:

  $ make installcheck

7. Internals
============

oracle_fdw sets the MODULE of the Oracle session to "postgres" and the
ACTION to the backend process number.  This can help identifying the Oracle
session and allows you to trace it with DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE.

oracle_fdw uses Oracle's result prefetching to avoid unnecessary client-server
round-trips. The prefetch counter is set to 200, the memory limit to 24KB.
These limits can be changed in oracle_utils.c.

Rather than using a PLAN_TABLE to explain an Oracle query (which would require
such a table to be created in the Oracle database), oracle_fdw uses execution
plans stored in the library cache.  For that, an Oracle query is "explicitly
described", which forces Oracle to parse the query.  The hard part is to find
the SQL_ID and CHILD_NUMBER of the statement in V$SQL because the SQL_TEXT
column contains only the first 1000 bytes of the query.
Therefore, oracle_fdw adds a comment to the query that contains an MD5 hash
of the query text.  This is used to search in V$SQL.
The actual execution plan or cost information is retrieved from V$SQL_PLAN.

oracle_fdw uses transaction isolation level "serializable" on the Oracle side,
which corresponds to PostgreSQL's repeatable read.  This is necessary because
a single PostgreSQL statement can lead to multiple Oracle queries (e.g. during
a nested loop join) and the results need to be consistent.

The Oracle transaction is committed immediately before the local transaction
commits, so that a completed PostgreSQL transaction guarantees that the Oracle
transaction has completed.  However, there is a small chance that the
PostgreSQL transaction cannot complete even though the Oracle transaction
is committed.  This cannot be avoided without using two-phase transactions
and a transaction manager, which is clearly beyond what a foreign data wrapper
can reasonably provide.
Prepared statements involving Oracle are not supported for the same reason.

8. Problems
===========

Encoding
--------

Characters stored in an Oracle database that cannot be converted to the
PostgreSQL database encoding will silently be replaced by "replacement
characters", typically a normal or inverted question mark, by Oracle.
You will get no warning or error messages.

If you use a PostgreSQL database encoding that Oracle does not know
(currently, these are EUC_CN, EUC_KR, LATIN10, MULE_INTERNAL, WIN874
and SQL_ASCII), non-ASCII characters cannot be translated correctly.
You will get a warning in this case, and the characters will be replaced
by replacement characters as described above.

You can set the "nls_lang" option of the foreign data wrapper to force a
certain Oracle encoding, but the resulting characters will most likely be
incorrect and lead to PostgreSQL error messages.  This is probably only
useful for SQL_ASCII encoding if you know what you are doing.
See "Options" above.

Planning
--------

Oracle's planner does not give good cost estimates (you can only get the
estimated execution time in second granularity, and it is never less than
one).  So by default, oracle_fdw does not use these estimates.
See the description of the "plan_costs" option above.

Even without that, the current implementation of oracle_fdw requires
calls to the Oracle server for every foreign table during query planning.
That means that there might be a noticeable performance improvement
if query plans are cached, for example by using prepared statements or
PL/pgSQL functions.

LDAP libraries
--------------

The Oracle client shared library comes with its own LDAP client
implementation conforming to RFC 1823, so these functions have the same
names as OpenLDAP's.  This will lead to a name collision when the PostgreSQL
server was configured "--with-ldap".

The name collision will not be detected, because oracle_fdw is loaded at
runtime, but trouble will happen if anybody calls an LDAP function.
Typically, OpenLDAP is loaded first, so if Oracle calls an LDAP function
(for example if you use "directory naming" name resolution), the backend
will crash.  This can lead to messages like the following (seen on Linux)
in the PostgreSQL server log:

  ../../../libraries/libldap/getentry.c:29: ldap_first_entry:
  Assertion `( (ld)->ld_options.ldo_valid == 0x2 )' failed.

The best thing is to configure PostgreSQL "--without-ldap".  This is the only
safe way to avoid this problem.
Even when PostgreSQL is built "--with-ldap", it may work as long as you don't
use any LDAP client functionality in Oracle.
On some platforms, you can force Oracle's client shared library to be loaded
before the PostgreSQL server is started (LD_PRELOAD on Linux).  Then Oracle's
LDAP functions should get used.  In that case, Oracle may be able to use
LDAP functionality, but using LDAP from PostgreSQL will crash the backend.

You cannot use LDAP functionality both in PostgreSQL and in Oracle, period.

Serialization errors
--------------------

In Oracle 11.2 or above, inserting the first row into a newly created
Oracle table with oracle_fdw will lead to a serialization error.

This is because of an Oracle feature called "deferred segment creation" which
defers allocation of storage space for a new table until the first row
is inserted.  This causes a serialization failure with serializable
transactions (see document 1285464.1 in Oracle's knowledge base).

This is no serious problem; you can work around it by either ignoring that
first error or creating the table with "SEGMENT CREATION IMMEDIATE".

Oracle bugs
-----------

This is a list of Oracle bugs that have affected oracle_fdw in the past.

Bug 6039623 can cause oracle_fdw to crash with a segmentation violation
when larger amounts of data are selected from an Oracle table via an Oracle
database link.
It can occur with any Oracle server version below 12.1, and the only remedy
is to install patch 10096945 which is included in the 11.2.0.3 Patch Set.

Bug 2728408 can cause "ORA-8177 cannot serialize access for this transaction"
even if no modification of remote data is attempted.
It can occur with Oracle server 8.1.7.4 (install one-off patch 2728408) or
Oracle server 9.2 (install Patch Set 9.2.0.4 or better).

9. Support
==========

If you want to report a problem with oracle_fdw, and the name of the
foreign server is (for example) "ora_serv", please include the output of

  SELECT oracle_diag('ora_serv');

in your problem report.
If that causes an error, please also include the output of

  SELECT oracle_diag();

If you have a problem or question or any kind of feedback, the preferred
option is to send an e-mail to oracle-fdw-general@lists.pgfoundry.org
You can subscribe here:
http://lists.pgfoundry.org/mailman/listinfo/oracle-fdw-general
There is a mail archive here:
http://lists.pgfoundry.org/pipermail/oracle-fdw-general/

There is the option to open an issue on GitHub:
https://github.com/laurenz/oracle_fdw/issues
This requires a GitHub account.

You can also use the issue trackers on pgFoundry:
http://pgfoundry.org/tracker/?group_id=1000600
pgFoundry also offers forums for questions:
http://pgfoundry.org/forum/?group_id=1000600
The trackers and forums require a pgFoundry account.

Producing RSS from PL/SQL

First things first. The idea and the bulk of the code for this post are not mine, they are Sean Dillon’s. It’s a very cool idea that he came up with and it still works flawlessly

I needed to incorporate RSS into a project I’m working, so I grabbed Sean’s code. The problem is, it’s based on the AskTom table structure which means it won’t run on your database without immediately re-writing the query. Additionally, the nature of this code relies on a pretty lengthy query to generate the XML. I’ll admit, when I first looked at it, I thought “Wow, this is going to be more complex than I thought.” After looking at it for a little while longer, I realized it was actually very simple. Sean also included support for several versions of RSS, improving the functionality, but again, adding to the complexity.

So, I created an example table and simplified the code as much as possible to make it easier for everyone to understand. The table, “PLSQL_PACKAGES”, stores information about some of the built-in PL/SQL packages I use on a regular basis. The links in this table point back to the online Oracle Documentation.

This block of code is just the DDL for the sample table and the insert statements to populate it:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
create table plsql_packages(
    id          varchar2(32),
    title       varchar2(255),
    description varchar2(4000),
    link        varchar2(1000),
    updated_by  varchar2(100),
    updated_on  date)
/
create or replace trigger  biu_plsql_packages before insert or
update on plsql_packages
for each row
begin
    if inserting then
        :new.id := sys_guid();
    end if;
        :new.updated_by := nvl(v('APP_USER'),user);
        :new.updated_on := sysdate;
end;
/
insert into plsql_packages(title,description,link)
     values ('DBMS_CRYPTO','DBMS_CRYPTO provides an interface
to encrypt and decrypt stored data, and can be used in
conjunction with PL/SQL programs running network
communications. It provides support for several
industry-standard encryption and hashing algorithms,
including the Advanced Encryption Standard (AES)
encryption algorithm. AES has been approved by the National
Institute of Standards and Technology (NIST) to replace
/
insert into plsql_packages(title,description,link)
     values ('DBMS_EPG','The DBMS_EPG package implements
the embedded PL/SQL gateway that enables a web browser to
invoke a PL/SQL stored procedure through an HTTP listener.','http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_epg.htm#sthref3481')
/
insert into plsql_packages(title,description,link)
     values (' OWA_UTIL','The OWA_UTIL package contains
utility subprograms for performing operations such as
getting the value of CGI environment variables, printing
the data that is returned to the client, and printing the
/
insert into plsql_packages(title,description,link)
     values ('UTL_MAIL','The UTL_MAIL package is a utility
for managing email which includes commonly used email
features, such as attachments, CC, BCC, and return receipt.','http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/u_mail.htm#i1001258')
/

This is the important block of code as it creates the RSS procedure. Don’t be intimidated by it though as you only need to modify a few lines. The only lines you need to customize to make it work against your own table are 4-7 and 41-43!

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
create or replace procedure rss
is
    -- customizable parameters
    l_title         varchar2(255) := 'Oracle PL/SQL Packages';
    l_link          varchar2(255) := 'http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/toc.htm';
    l_description   varchar2(255) := 'This is a feed of changes to PL/SQL Package Documentation';
    l_language      varchar2(255) := 'en-us';
    -- end customizable parameters
    l_version       varchar2(10)  := '2.0';
    l_clob          clob;
    l_idx           pls_integer := 1;
    l_len           pls_integer := 255;
    l_defrows       pls_integer := 10;
    l_maxrows       pls_integer := 30;
    l_desclen       pls_integer := 250;
begin
    for i in (
      select xmlelement( "rss",
               -- Begin XML Header Block
               xmlattributes( l_version as "version"),
                 xmlelement( "channel",
                   xmlforest( l_title as "title",
                              l_link as "link",
                              l_description as "description",
                              l_language as "language"),
                 -- End XML Header Block
                 -- Begin List of Individual Articles or
                 -- Items
                 xmlagg(
                     xmlelement( "item",
                       xmlelement("title", x.title),
                       xmlelement("link", x.link),
                       xmlelement("description",
                       x.description),
                       xmlelement("pubDate",
                       to_char(x.updated_on,'Dy, DD Mon RRRR hh24:mi:ss')),
                       xmlelement("guid", XMLATTRIBUTES
                       ('false' as "isPermaLink"),x.id||
                       to_char(x.updated_on,'JHH24MISS'))
                     )
                   )
                   -- End List of Individual Articles or
Items
                 )
             ) as result
        from -- Actual Database Query that
--populates the list of Items
                select id,title,link,description,
                updated_on
                  from plsql_packages
                 where rownum < (l_maxrows+1)) x)
    loop
        l_clob := xmltype.extract(i.result,'/').getclobval;
        exit;
    end loop; --i
    --- OUTPUT RESULTS
    owa_util.mime_header('application/xml', false);
    owa_util.http_header_close;
    for i in 1..ceil(dbms_lob.getlength(l_clob)/l_len) loop
        htp.prn(substr(l_clob,l_idx,l_len));
        l_idx := l_idx + l_len;
    end loop; --i
end rss;
/

Note on line 34 the “guid” element. This is an optional element (documented here) that an aggregator can use to uniquely identify the item. I’m concatenating the ID column from the table with Julian date concatenated with hours, minutes, and seconds – to_char(sysdate,‘JHH24MISS’). This means that when you update a row, the date will change causing your aggregator to see a new guid and display a new item for the changed row.The easiest way to test this procedure is using the “OWA Output” tab in SQL Developer:SQL Developer OWA Ouput

If you’re running XE or 11g and you want to call this procedure directly through the APEX DAD, you’ll need to edit the FLOWS_XXXXXX.wwv_flow_epg_include_mod_local function and comment-out the first line as well as your procedure to the IN list.

 

 

Change exadata flashcache mode from WriteThrough to WriteBack

We can enable the WriteBack mode for flashcaches without shutting down the ASM or any instance on the Exadata. This will be done in a rolling fashion one cell disk at a time. You have to make sure you finish one cell before starting the operation on the next cell.

I will follow the following document to change the flashcache mode in a Exadata Database Machine X3-2 Eighth Rack System. Also you can find why we need to use WriteBack mode and other useful information in that document.

Exadata Write-Back Flash Cache – FAQ (Doc ID 1500257.1)

4. How to determine if you have write back flash cache enabled?

[root@testdbadm01 ~]# dcli -g ~/cell_group -l root cellcli -e "list 
cell attributes flashcachemode"
testceladm01: WriteThrough
testceladm02: WriteThrough
testceladm03: WriteThrough

5. How can we enable the write back flash cache?

Before proceeding any further, make sure all the griddisks are online and there are no problems on the cells.

[root@testdbadm01 ~]#  dcli -g cell_group -l root cellcli -e list 
griddisk attributes asmdeactivationoutcome, asmmodestatus
testceladm01: Yes        ONLINE
testceladm01: Yes        ONLINE
testceladm01: Yes        ONLINE
testceladm01: Yes        ONLINE
testceladm01: Yes        ONLINE
testceladm01: Yes        ONLINE
testceladm01: Yes        ONLINE
testceladm01: Yes        ONLINE
testceladm01: Yes        ONLINE
testceladm01: Yes        ONLINE
testceladm01: Yes        ONLINE
testceladm01: Yes        ONLINE
testceladm01: Yes        ONLINE
testceladm01: Yes        ONLINE
testceladm01: Yes        ONLINE
testceladm01: Yes        ONLINE
testceladm02: Yes        ONLINE
testceladm02: Yes        ONLINE
testceladm02: Yes        ONLINE
testceladm02: Yes        ONLINE
testceladm02: Yes        ONLINE
testceladm02: Yes        ONLINE
testceladm02: Yes        ONLINE
testceladm02: Yes        ONLINE
testceladm02: Yes        ONLINE
testceladm02: Yes        ONLINE
testceladm02: Yes        ONLINE
testceladm02: Yes        ONLINE
testceladm02: Yes        ONLINE
testceladm02: Yes        ONLINE
testceladm02: Yes        ONLINE
testceladm02: Yes        ONLINE
testceladm03: Yes        ONLINE
testceladm03: Yes        ONLINE
testceladm03: Yes        ONLINE
testceladm03: Yes        ONLINE
testceladm03: Yes        ONLINE
testceladm03: Yes        ONLINE
testceladm03: Yes        ONLINE
testceladm03: Yes        ONLINE
testceladm03: Yes        ONLINE
testceladm03: Yes        ONLINE
testceladm03: Yes        ONLINE
testceladm03: Yes        ONLINE
testceladm03: Yes        ONLINE
testceladm03: Yes        ONLINE
testceladm03: Yes        ONLINE
testceladm03: Yes        ONLINE

On the 1/8th Rack system flashcache is reduced to half size. Becuase of that the size appears to be 744 GB.

[root@testdbadm01 ~]#  dcli -g cell_group -l root cellcli -e list flashcache detail
testceladm01: name:                      testceladm01_FLASHCACHE
testceladm01: cellDisk:                  FD_03_testceladm01,FD_02_testceladm01,FD_00_testceladm01,FD_01_testceladm01,FD_06_testceladm01,FD_05_testceladm01,FD_04_testceladm01,FD_07_testceladm01
testceladm01: creationTime:              2013-07-29T17:55:28+03:00
testceladm01: degradedCelldisks:
testceladm01: effectiveCacheSize:        744.125G
testceladm01: id:                        5adas74d-asdc-4477-382d-30c14052c23d
testceladm01: size:                      744.125G
testceladm01: status:                    normal
testceladm02: name:                      testceladm02_FLASHCACHE
testceladm02: cellDisk:                  FD_03_testceladm02,FD_07_testceladm02,FD_02_testceladm02,FD_00_testceladm02,FD_06_testceladm02,FD_01_testceladm02,FD_04_testceladm02,FD_05_testceladm02
testceladm02: creationTime:              2013-07-29T17:55:40+03:00
testceladm02: degradedCelldisks:
testceladm02: effectiveCacheSize:        744.125G
testceladm02: id:                        80c140a3-0c14-40ba-8364-10c1460d802f
testceladm02: size:                      744.125G
testceladm02: status:                    normal
testceladm03: name:                      testceladm03_FLASHCACHE
testceladm03: cellDisk:                  FD_06_testceladm03,FD_03_testceladm03,FD_00_testceladm03,FD_04_testceladm03,FD_05_testceladm03,FD_02_testceladm03,FD_01_testceladm03,FD_07_testceladm03
testceladm03: creationTime:              2013-07-29T17:55:25+03:00
testceladm03: degradedCelldisks:
testceladm03: effectiveCacheSize:        744.125G
testceladm03: id:                        6950c148-992b-4347-a1e1-e60c1406bda6
testceladm03: size:                      744.125G
testceladm03: status:                    normal

Repeat the following procedure for all the cells. We have 3 cells on 1/8 the rack exadata. Operate only on one cell at a time. Before proceeding the next cell, make sure the cell is operational. I will follow the Support ID but I prefer to use the cellcli commands in the cellcli command prompt.

Also I prefer to do any disk operation during off peak hours.

1. Drop the flash cache on that cell

CellCLI>  drop flashcache
Flash cache testceladm01_FLASHCACHE successfully dropped

2. Check if ASM will be OK if the grid disks go OFFLINE. The following command should return ‘Yes’ for the grid disks being listed:

CellCLI> list griddisk attributes name,asmmodestatus,asmdeactivationoutcome
         DATA_TEST_CD_00_testceladm01    ONLINE  Yes
         DATA_TEST_CD_01_testceladm01    ONLINE  Yes
         DATA_TEST_CD_02_testceladm01    ONLINE  Yes
         DATA_TEST_CD_03_testceladm01    ONLINE  Yes
         DATA_TEST_CD_04_testceladm01    ONLINE  Yes
         DATA_TEST_CD_05_testceladm01    ONLINE  Yes
         DBFS_DG_CD_02_testceladm01      ONLINE  Yes
         DBFS_DG_CD_03_testceladm01      ONLINE  Yes
         DBFS_DG_CD_04_testceladm01      ONLINE  Yes
         DBFS_DG_CD_05_testceladm01      ONLINE  Yes
         RECO_TEST_CD_00_testceladm01    ONLINE  Yes
         RECO_TEST_CD_01_testceladm01    ONLINE  Yes
         RECO_TEST_CD_02_testceladm01    ONLINE  Yes
         RECO_TEST_CD_03_testceladm01    ONLINE  Yes
         RECO_TEST_CD_04_testceladm01    ONLINE  Yes
         RECO_TEST_CD_05_testceladm01    ONLINE  Yes

3. Inactivate the griddisk on the cell

CellCLI> alter griddisk all inactive
GridDisk DATA_TEST_CD_00_testceladm01 successfully altered
GridDisk DATA_TEST_CD_01_testceladm01 successfully altered
GridDisk DATA_TEST_CD_02_testceladm01 successfully altered
GridDisk DATA_TEST_CD_03_testceladm01 successfully altered
GridDisk DATA_TEST_CD_04_testceladm01 successfully altered
GridDisk DATA_TEST_CD_05_testceladm01 successfully altered
GridDisk DBFS_DG_CD_02_testceladm01 successfully altered
GridDisk DBFS_DG_CD_03_testceladm01 successfully altered
GridDisk DBFS_DG_CD_04_testceladm01 successfully altered
GridDisk DBFS_DG_CD_05_testceladm01 successfully altered
GridDisk RECO_TEST_CD_00_testceladm01 successfully altered
GridDisk RECO_TEST_CD_01_testceladm01 successfully altered
GridDisk RECO_TEST_CD_02_testceladm01 successfully altered
GridDisk RECO_TEST_CD_03_testceladm01 successfully altered
GridDisk RECO_TEST_CD_04_testceladm01 successfully altered
GridDisk RECO_TEST_CD_05_testceladm01 successfully altered

4. Shut down cellsrv service

CellCLI> alter cell shutdown services cellsrv 

Stopping CELLSRV services... 
The SHUTDOWN of CELLSRV services was successful.

5. Set the cell flashcache mode to writeback

CellCLI> alter cell flashCacheMode=writeback
Cell testceladm01 successfully altered

6. Restart the cellsrv service

CellCLI> alter cell startup services cellsrv

Starting CELLSRV services...
The STARTUP of CELLSRV services was successful.

7. Reactivate the griddisks on the cell

CellCLI> alter griddisk all active
GridDisk DATA_TEST_CD_00_testceladm01 successfully altered
GridDisk DATA_TEST_CD_01_testceladm01 successfully altered
GridDisk DATA_TEST_CD_02_testceladm01 successfully altered
GridDisk DATA_TEST_CD_03_testceladm01 successfully altered
GridDisk DATA_TEST_CD_04_testceladm01 successfully altered
GridDisk DATA_TEST_CD_05_testceladm01 successfully altered
GridDisk DBFS_DG_CD_02_testceladm01 successfully altered
GridDisk DBFS_DG_CD_03_testceladm01 successfully altered
GridDisk DBFS_DG_CD_04_testceladm01 successfully altered
GridDisk DBFS_DG_CD_05_testceladm01 successfully altered
GridDisk RECO_TEST_CD_00_testceladm01 successfully altered
GridDisk RECO_TEST_CD_01_testceladm01 successfully altered
GridDisk RECO_TEST_CD_02_testceladm01 successfully altered
GridDisk RECO_TEST_CD_03_testceladm01 successfully altered
GridDisk RECO_TEST_CD_04_testceladm01 successfully altered
GridDisk RECO_TEST_CD_05_testceladm01 successfully altered

8. Verify all grid disks have been successfully put online using the following command:

(Currently DATA_TEST diskgroup started syncronization)

CellCLI> list griddisk attributes name, asmmodestatus
DATA_TEST_CD_00_testceladm01 SYNCING
DATA_TEST_CD_01_testceladm01 SYNCING
DATA_TEST_CD_02_testceladm01 SYNCING
DATA_TEST_CD_03_testceladm01 SYNCING
DATA_TEST_CD_04_testceladm01 SYNCING
DATA_TEST_CD_05_testceladm01 SYNCING
DBFS_DG_CD_02_testceladm01 OFFLINE
DBFS_DG_CD_03_testceladm01 OFFLINE
DBFS_DG_CD_04_testceladm01 OFFLINE
DBFS_DG_CD_05_testceladm01 OFFLINE
RECO_TEST_CD_00_testceladm01 OFFLINE
RECO_TEST_CD_01_testceladm01 OFFLINE
RECO_TEST_CD_02_testceladm01 OFFLINE
RECO_TEST_CD_03_testceladm01 OFFLINE
RECO_TEST_CD_04_testceladm01 OFFLINE
RECO_TEST_CD_05_testceladm01 OFFLINE

9. Recreate the flash cache

CellCLI> create flashcache all
Flash cache testceladm01_FLASHCACHE successfully created

10. Check the status of the cell to confirm that it’s now in WriteBack mode:

CellCLI> list cell attributes flashCacheMode
writeback

11. Repeat these same steps again on the next cell. However, before taking another storage server offline, execute the following making sure ‘asmdeactivationoutcome’ displays YES: (Currently RECO_TEST diskgroup started synchronization)

CellCLI> list griddisk attributes name,asmmodestatus,asmdeactivationoutcome
DATA_TEST_CD_00_testceladm01 ONLINE Yes
DATA_TEST_CD_01_testceladm01 ONLINE Yes
DATA_TEST_CD_02_testceladm01 ONLINE Yes
DATA_TEST_CD_03_testceladm01 ONLINE Yes
DATA_TEST_CD_04_testceladm01 ONLINE Yes
DATA_TEST_CD_05_testceladm01 ONLINE Yes
DBFS_DG_CD_02_testceladm01 ONLINE Yes
DBFS_DG_CD_03_testceladm01 ONLINE Yes
DBFS_DG_CD_04_testceladm01 ONLINE Yes
DBFS_DG_CD_05_testceladm01 ONLINE Yes
RECO_TEST_CD_00_testceladm01 SYNCING Yes
RECO_TEST_CD_01_testceladm01 SYNCING Yes
RECO_TEST_CD_02_testceladm01 SYNCING Yes
RECO_TEST_CD_03_testceladm01 SYNCING Yes
RECO_TEST_CD_04_testceladm01 SYNCING Yes
RECO_TEST_CD_05_testceladm01 SYNCING Yes

All disk groups are synchronized now we can proceed on the next cell.

CellCLI> list griddisk attributes name,asmmodestatus,asmdeactivationoutcome
DATA_TEST_CD_00_testceladm01 ONLINE Yes
DATA_TEST_CD_01_testceladm01 ONLINE Yes
DATA_TEST_CD_02_testceladm01 ONLINE Yes
DATA_TEST_CD_03_testceladm01 ONLINE Yes
DATA_TEST_CD_04_testceladm01 ONLINE Yes
DATA_TEST_CD_05_testceladm01 ONLINE Yes
DBFS_DG_CD_02_testceladm01 ONLINE Yes
DBFS_DG_CD_03_testceladm01 ONLINE Yes
DBFS_DG_CD_04_testceladm01 ONLINE Yes
DBFS_DG_CD_05_testceladm01 ONLINE Yes
RECO_TEST_CD_00_testceladm01 ONLINE Yes
RECO_TEST_CD_01_testceladm01 ONLINE Yes
RECO_TEST_CD_02_testceladm01 ONLINE Yes
RECO_TEST_CD_03_testceladm01 ONLINE Yes
RECO_TEST_CD_04_testceladm01 ONLINE Yes
RECO_TEST_CD_05_testceladm01 ONLINE Yes

FINALLY
After changing the flashcache modes on all cells, check if flashcache modes are changed to write-back for all cells.

[root@testdbadm01 ~]# dcli -g ~/cell_group -l root cellcli -e "list cell attributes flashcachemode"
testceladm01: writeback
testceladm02: writeback
testceladm03: writeback