Yann Neuhaus

Subscribe to Yann Neuhaus feed
dbi services technical blog
Updated: 9 hours 17 min ago

Upgrade to Enterprise Manager 13.2

Tue, 2016-12-13 08:11

I will describe how to upgrade Enterprise Manager 13.1.0.0 to the new 13.2.0.0 version.

At the beginning we have to ensure that we applied the latest PSU on the repository database.

It is mandatory to apply the following patch before upgrading to Enterprise Manager Cloud Control 13.2.0.0:

DATABASE PATCH SET UPDATE 12.1.0.2.160419

To install the patch you have to check the following point:

- you have to use Oracle Interim Patch Installer version 12.2.0.1.8

Once you have downloaded the patch and unzipped it, you can check for potential conflicts:

oracle@vmCC13c:/u01/app/oracle/ [EMREP13C] opatch prereq 
CheckConflictAgainstOHWithDetail 
-phBaseDir /oracle/u01/app/oracle/software/22899531/22806133
Oracle Interim Patch Installer version 12.2.0.1.8
Copyright (c) 2016, Oracle Corporation.  All rights reserved.
PREREQ session
Oracle Home       : /u01/app/oracle/product/12.1.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/12.1.0/dbhome_1/oraInst.loc
OPatch version    : 12.2.0.1.8
OUI version       : 12.1.0.2.0
Invoking prereq "checkconflictagainstohwithdetail"
Prereq "checkConflictAgainstOHWithDetail" passed. 
OPatch succeeded.

 

oracle@vmCC13c:/u01/app/oracle/ [EMREP13C] opatch prereq 
CheckConflictAgainstOHWithDetail -phBaseDir 
/oracle/u01/app/oracle/software/22899531/23006522
Oracle Interim Patch Installer version 12.2.0.1.8
Copyright (c) 2016, Oracle Corporation.  All rights reserved.
PREREQ session
Oracle Home       : /u01/app/oracle/product/12.1.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/12.1.0/dbhome_1/oraInst.loc
OPatch version    : 12.2.0.1.8
OUI version       : 12.1.0.2.0
Invoking prereq "checkconflictagainstohwithdetail"
Prereq "checkConflictAgainstOHWithDetail" passed.
OPatch succeeded.

And finally, you check for the system space available:

oracle@vmCC13c:/u01/app/oracle/ [EMREP13C] $ORACLE_HOME/OPatch/opatch prereq 
CheckSystemSpace -phBaseFile /oracle/u01/app/oracle/software/patch_list_dbhome.txt
Oracle Interim Patch Installer version 12.2.0.1.8
Copyright (c) 2016, Oracle Corporation.  All rights reserved.
PREREQ session
Oracle Home       : /u01/app/oracle/product/12.1.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/12.1.0/dbhome_1/oraInst.loc
OPatch version    : 12.2.0.1.8
OUI version       : 12.1.0.2.0
Invoking prereq "checksystemspace"
Prereq "checkSystemSpace" passed.
OPatch succeeded.

Then once the pre requisites are ok, you stop the repository database, and you run the classical opatch apply command from the directory where you have unzipped the PSU.

You finally check the Oracle inventory:

oracle@vmtestoraCC13c:/home/oracle/ [EMREP13C] opatch lsinventory
Oracle Interim Patch Installer version 12.2.0.1.8
Copyright (c) 2016, Oracle Corporation.  All rights reserved.
Oracle Home       : /u01/app/oracle/product/12.1.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/12.1.0/dbhome_1/oraInst.loc
OPatch version    : 12.2.0.1.8
OUI version       : 12.1.0.2.0
--------------------------------------------------------------------------------
Local Machine Information:
ARU platform id: 226
ARU platform description:: Linux x86-64
Installed Top-level Products (1):
Oracle Database 12c                                      12.1.0.2.0
There are 1 products installed in this Oracle Home. 
Interim patches (1) : 
Patch  22806133     : applied on Tue Nov 22 11:19:55 CET 2016
Unique Patch ID:  19983161
Patch description:  "DATABASE BUNDLE PATCH: 12.1.0.2.160419 (22806133)"

Secondly you have to disable the optimizer_adaptive_features parameter in the repository database:

SQL> alter system set optimizer_adaptive_features=false scope=both;

System altered.

Then we have to ensure that the tables in the Management Repository do not have any snapshots created:

SQL> select master , log_table from all_mview_logs where log_owner='SYSMAN';
no rows selected

Then we have to ensure that the tables in the Management Repository do not have any snapshots created:

SQL> select master , log_table from all_mview_logs where log_owner='SYSMAN';
no rows selected

We verify if any login triggers are set:

SQL> SELECT COUNT (trigger_name) FROM sys.dba_triggers 
WHERE TRIGGERING_EVENT LIKE 'LOGON%' AND status='ENABLED';
SQL> SELECT trigger_name FROM sys.dba_triggers 
WHERE TRIGGERING_EVENT LIKE 'LOGON%' AND status='ENABLED';

We verify if any logoff triggers are set:

SQL> SELECT COUNT (trigger_name) FROM sys.dba_triggers
WHERE TRIGGERING_EVENT LIKE 'LOGOFF%' AND status='ENABLED';
SQL> SELECT trigger_name FROM sys.dba_triggers
WHERE TRIGGERING_EVENT LIKE 'LOGOFF%' AND status='ENABLED';

If we find a trigger enabled, we disable it:

SQL> SELECT trigger_name, owner from sys.dba_triggers 
WHERE TRIGGERING_EVENT LIKE 'LOGOFF%' AND status='ENABLED';
 
TRIGGER_NAME         OWNER
GSMLOGOFF            GSMADMIN_INTERNAL
 
SQL> alter trigger gsmadmin_internal.gsmlogoff disable;

Then you have to copy the EMKEY to the repository database:

oracle@vmCC13c:/u03/app/oracle/oms13c/bin/ [oms13c] emctl config emkey 
-copy_to_repos -sysman_pwd dbi05manager
Oracle Enterprise Manager Cloud Control 13c Release 1
Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.
The EMKey has been copied to the Management Repository. 
This operation will cause the EMKey to become unsecure.
After the required operation has been completed, 
secure the EMKey by running "emctl config emkey -remove_from_repos".

Concerning the OMS shutdown we follow the following procedure:

We stop the JVMD and ADP engines explicitly:

oracle@vmCC13c:/u03/app/oracle/oms13c/bin/ [oms13c] emctl extended oms jvmd stop -all
Oracle Enterprise Manager Cloud Control 13c Release 1
Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.
No verb found to be registered with emctl extensibles framework
 
oracle@vmCC13c:/u03/app/oracle/oms13c/bin/ [oms13c] emctl extended oms adp stop -all
Oracle Enterprise Manager Cloud Control 13c Release 1
Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.
No verb found to be registered with emctl extensibles framework

Then we shut down completely the OMS:

oracle@v333:/home/oracle/ [oms13c] emctl stop oms -all
Oracle Enterprise Manager Cloud Control 13c Release 1
Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.
Stopping Oracle Management Server...
WebTier Successfully Stopped
Oracle Management Server Successfully Stopped
Oracle Management Server is Down
JVMD Engine is Down
Stopping BI Publisher Server...
BI Publisher Server Successfully Stopped
AdminServer Successfully Stopped
BI Publisher Server is Down

It is mandatory to stop the management agent in order to avoid errors during the migration phase:

oracle@vmtestoraCC13c:/home/oracle/ [agent13c] emctl stop agent
Oracle Enterprise Manager Cloud Control 13c Release 1
Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.
Stopping agent ... stopped.

WATCH OUT: before running the installer unset PERLLIB and PERL5LIB variables (if for example you have environment variables defined for your oracle user) otherwise you will get the following error:

/opt/oracle/Middleware13c/perl/lib/5.10.0/x86_64-linux-thread-multi/auto/Cwd/Cwd.so: 
undefined symbol: Perl_Gthr_key_ptr
ERROR: Unable to continue with the installation 
because some one-off patches could not be applied successfully.

Then once the oms and the management agent are stopped you can run:

oracle@v333:/opt/software/ [oms13c] ./em13200_linux64.bin
Checking monitor: must be configured to display at least 256 colors.   
Actual 16777216    Passed
Checking swap space: must be greater than 512 MB.   Actual 7999 MB    Passed
Checking if this platform requires a 64-bit JVM.   Actual 64    
Passed (64-bit not required)
Preparing to launch the Oracle Universal Installer 
from /tmp/OraInstall2016-11-18_10-07-45AM
====Prereq Config Location main===
/tmp/OraInstall2016-11-18_10-07-45AM/stage/prereq
EMGCInstaller args -scratchPath
EMGCInstaller args /tmp/OraInstall2016-11-18_10-07-45AM
EMGCInstaller args -sourceType
EMGCInstaller args network
EMGCInstaller args -timestamp
EMGCInstaller args 2016-11-18_10-07-45AM
EMGCInstaller args -paramFile
EMGCInstaller args /tmp/sfx_f8wrWz/Disk1/install/linux64/oraparam.ini
EMGCInstaller args -nocleanUpOnExit
DiskLoc inside SourceLoc/opt/software
EMFileLoc:/tmp/OraInstall2016-11-18_10-07-45AM/oui/em/
ScratchPathValue :/tmp/OraInstall2016-11-18_10-07-45AM

The first installer screen appears:

em1

I choosed not to receive Security alerts then I Select Next:

em2

I select Next, I skip the software Updates

em3

We check the prerequisites are ok

em4

We choose to upgrade an existing Enterprise Manager system, we enter the old Middleware home, and we select Next

em5

We enter the new Middleware home, and we select Next

em6

We enter the repository connection details, the sys and sysman passords, we confirm we have a correct repository backupm we disable the DDMP jobs and we select Next

em7

We select Yes to fix the issues

em8

We review the plugins and we select Next

em9

We have the possibility to add plugins we want to deploy while upgrading to EM 13.2

em10

We enter the weblogic username and password and we select Next

em11

I choosed not to configure a shared location for Oracle BI publisher

em12

We choose the default ports and we select Next

em13

We select Upgrade:

em14

The upgrade is running fine:=)

At the end of the upgrade, you have to run connected as root from the new OMS home the shell root.sh:

root@vmCC13c oms13cr2]# . root.sh
/etc exist
/u03/app/oracle/oms13cr2

Finally you have successfully upgraded to EM 13.2:

em15

Once the OMs is upgraded, you have to upgrade the management agents with the classical procedure. From the EM console you select upgrade agent as follows:

em16

em17

You add the management agent, and you select Submit

em18

You can follow the upgrade phase, finally you have to run the root.sh script on the management agent and cleanup the old agent environment with the EM console as follows:

You select the Post Agent Upgrade Tasks, you select the agent previously migrated:

em19

And you select Submit

The old agent home will be removed; you will just have to adapt your /etc/oratab file to give the new ORACLE_HOME for your agent 13.2

The last phase consists in deleting the old OMS home. As we were in 13.1 version, we only have to check nothing is running with the old environment, then delete the old home.

oracle@vmCC13c:/u03/app/oracle/oms13cr2/ [oms13c] ps -ef | grep oms | grep -v 13cr2
oracle   28463 27736  0 15:52 pts/5    00:00:00 grep --color=auto oms
 
oracle@vmCC13c:/u03/app/oracle/ [oms13c] ls
agent13c  CC13c_setupinfo.txt  gc_inst  gc_inst1  oms13c  oms13cr2  swlib
oracle@vmCC13c:/u03/app/oracle/ [oms13c] rm -rf oms13c

The new features are listed in the following URL and mainly concern the virtualization, the Middleware and Cloud Management, and some new features about incident management (always on monitoring installed on a different host for example).

http://docs.oracle.com/cd/E73210_01/EMCON/GUID-503991BC-D1CD-46EC-8373-8423B2D43437.htm#EMCON-GUID-503991BC-D1CD-46EC-8373-8423B2D43437

Finally the upgrade phase to EM 13.2 is finished in a couple of hours and did not present any errors.

 

 

 

 

 

 

 

 

Cet article Upgrade to Enterprise Manager 13.2 est apparu en premier sur Blog dbi services.

Getting started with Docker – 2 – building your own base image

Mon, 2016-12-12 13:30

In the last post we quickly looked at how you can install everything you need to run docker containers on a CentOS 7 host and did bring up the official PostgreSQL image. However there are good reasons not to rely on an existing image: You want to deliver a pre-configured base image which includes everything your application requires and you want to have control over that image. When this image needs to get updated you can update the base image and then re-deploy it to wherever you need to update the base image.

For CentOS there is a script which you can use to build your base image on GitHub. For other distributions you can check the docker documentation. This script basically makes use of the “–installroot” switch of yum which allows to install packages to another location than the default one.

Using this script we can build a CentOS 7 base image. There are three parameters you can use:

  -p ""  The list of packages to install in the container.
                   The default is blank.
  -g ""    The groups of packages to install in the container.
                   The default is "Core".
  -y      The path to the yum config to install packages from. The
                   default is /etc/yum.conf for Centos/RHEL and /etc/dnf/dnf.conf for Fedora

We’ll use the “-p” switch to install all the packages which are required to build PostgreSQL from source (this depends on the compile options, of course) and some additional packages which are useful for daily work (such as screen):

[docker@centos7 ~]$ sudo ./mkimage-yum.sh -p "gcc openldap-devel python-devel readline-devel redhat-lsb bison flex perl-ExtUtils-Embed zlib-devel crypto-utils openssl-devel pam-devel libxml2-devel libxslt-devel tcl tcl-devel openssh-clients bzip2 net-tools wget screen ksh unzip" centospg

Once done the new image is available locally:

[docker@centos7 ~]$ docker images
REPOSITORY          TAG                 IMAGE ID            CREATED             SIZE
centospg            7.2.1511            184509483d52        40 seconds ago      510.6 MB
postgres            latest              78e3985acac0        2 days ago          264.7 MB
hello-world         latest              c54a2cc56cbb        5 months ago        1.848 kB

If you wonder how that image made it into docker take a look a the last lines of the script:

tar --numeric-owner -c -C "$target" . | docker import - $name:$version

What happened is that a tar file of the temporary chroot directory was created:

[docker@centos7 ~]$ ls -latr /tmp/mkimage-yum.sh.dKHtsq/
total 24
drwxrwxrwt.  2 root root    6 Aug 12  2015 tmp
dr-xr-xr-x.  2 root root    6 Aug 12  2015 sys
drwxr-xr-x.  2 root root    6 Aug 12  2015 srv
drwxr-xr-x.  2 root root    6 Aug 12  2015 run
dr-xr-x---.  2 root root    6 Aug 12  2015 root
dr-xr-xr-x.  2 root root    6 Aug 12  2015 proc
drwxr-xr-x.  2 root root    6 Aug 12  2015 opt
drwxr-xr-x.  2 root root    6 Aug 12  2015 mnt
drwxr-xr-x.  2 root root    6 Aug 12  2015 media
drwxr-xr-x.  2 root root    6 Aug 12  2015 home
drwxr-xr-x.  2 root root 4096 Aug 12  2015 dev
dr-xr-xr-x.  2 root root    6 Aug 12  2015 boot
lrwxrwxrwx.  1 root root    7 Dec 10 15:02 bin -> usr/bin
lrwxrwxrwx.  1 root root    8 Dec 10 15:02 sbin -> usr/sbin
lrwxrwxrwx.  1 root root    9 Dec 10 15:02 lib64 -> usr/lib64
lrwxrwxrwx.  1 root root    7 Dec 10 15:02 lib -> usr/lib
dr-xr-xr-x. 17 root root 4096 Dec 10 15:02 .
drwxr-xr-x. 13 root root 4096 Dec 10 15:02 usr
drwxr-xr-x. 17 root root 4096 Dec 10 15:02 var
drwxr-xr-x. 19 root root 4096 Dec 10 15:02 etc
drwxrwxrwt. 11 root root 4096 Dec 12 11:43 ..

… which then was imported into docker with the “docker import” command.

To test if it really works we can start it and execute bash:

[docker@centos7 ~]$ docker run -it --rm  centospg:7.2.1511 bash
[root@cf690e9d9476 /]$ cat /etc/os-release 
NAME="CentOS Linux"
VERSION="7 (Core)"
ID="centos"
ID_LIKE="rhel fedora"
VERSION_ID="7"
PRETTY_NAME="CentOS Linux 7 (Core)"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:centos:centos:7"
HOME_URL="https://www.centos.org/"
BUG_REPORT_URL="https://bugs.centos.org/"

CENTOS_MANTISBT_PROJECT="CentOS-7"
CENTOS_MANTISBT_PROJECT_VERSION="7"
REDHAT_SUPPORT_PRODUCT="centos"
REDHAT_SUPPORT_PRODUCT_VERSION="7"

Fine, so far so good. Now we can really begin: We have our own CentOS base image where we want our PostgreSQL image to be based on. How then can we create a PostgreSQL image on top of our base image?

We’ll execute the commands necessary first and provide explanations afterwards. So, the first step:

[docker@centos7 ~]$ sudo yum install -y git
[docker@centos7 ~]$ mkdir centospg
[docker@centos7 ~]$ cd centospg
[docker@centos7 centospg]$ git init
Initialized empty Git repository in /home/docker/centospglatest/.git/
[docker@centos7 centospg]$ git config --global user.email "daniel@abc.def"
[docker@centos7 centospg]$ git config --global user.name "Daniel"

Why do we need git? It is not required to use git at all but you probably would like to have your files which are used to build your containers managed by git so that you can use all advantages of GIT combined with the advantages of docker. It will will make more and more sense as we step through all the commands.

What we need to create now is a so called Dockerfile. This file lists the instructions that Docker will execute to build you image. Lets go:

[docker@centos7 centospg]$ touch Dockerfile
[docker@centos7 centospg]$ git add Dockerfile 
[docker@centos7 centospg]$ git commit -m "initial" Dockerfile 
[master (root-commit) ce3727a] initial
 1 file changed, 0 insertions(+), 0 deletions(-)
 create mode 100644 Dockerfile

Our very basic Dockerfile will look like this:

# use our CentOS base images as source
FROM centospg:7.2.1511
# set the PostgreSQL we will download
ENV PG_VERSION 9.6.1
# include the PostgreSQL binaries in the PATH
ENV PATH /u01/app/postgres/product/96/db_01/bin:$PATH
# add a postgres group and postgres user
RUN groupadd postgres
RUN useradd -g postgres -m postgres
# prepare the directories
RUN mkdir -p /u01/app/postgres
RUN chown postgres:postgres /u01/app/postgres
# allow sudo for the postgres user
RUN echo 'postgres ALL=(ALL) NOPASSWD:ALL' >> /etc/sudoers
# download, configure, compile and install PostgreSQL from source
USER postgres
RUN wget https://ftp.postgresql.org/pub/source/v${PG_VERSION}/postgresql-${PG_VERSION}.tar.bz2 -O /var/tmp/postgresql-${PG_VERSION}.tar.bz2
RUN cd /var/tmp; tar -axf /var/tmp/postgresql-${PG_VERSION}.tar.bz2
RUN rm -f /var/tmp/postgresql-${PG_VERSION}.tar.bz2
RUN cd /var/tmp/postgres*; ./configure --prefix=/u01/app/postgres/product/96/db_01
RUN cd /var/tmp/postgres*; make
RUN cd /var/tmp/postgres*; make install
# cleanup
RUN rm -rf /var/tmp/postgres*

Using this Dockerfile we can build our PostgreSQL image:

[docker@centos7 centospg]$ docker build -t centospg:PG9.6.1 .

The output of this is quite long, here a snippet:

Sending build context to Docker daemon 45.06 kB
Step 1 : FROM centospg:7.2.1511
 ---> 184509483d52
Step 2 : ENV PG_VERSION 9.6.1
 ---> Running in 054900c7ebe1
 ---> 866815b9f092
Removing intermediate container 054900c7ebe1
Step 3 : ENV PATH /u01/app/postgres/product/96/db_01/bin:$PATH
 ---> Running in 46bcf7667a06
 ---> 94c9adb0402b
Removing intermediate container 46bcf7667a06
Step 4 : RUN groupadd postgres
 ---> Running in 24a7d9b7a1ea
 ---> eb4ff8268e2e
Removing intermediate container 24a7d9b7a1ea
Step 5 : RUN useradd -g postgres -m postgres
 ---> Running in 3e09b556fed8
 ---> acff1dcf2d4c
Removing intermediate container 3e09b556fed8
Step 6 : RUN mkdir -p /u01/app/postgres
 ---> Running in 999a62d075c0
 ---> fa4bdfa74d31
Removing intermediate container 999a62d075c0
Step 7 : RUN chown postgres:postgres /u01/app/postgres
 ---> Running in 37773e484260
 ---> 668c491b534b
Removing intermediate container 37773e484260
Step 8 : RUN echo 'postgres ALL=(ALL) NOPASSWD:ALL' >> /etc/sudoers
 ---> Running in bb9cbfd20623
 ---> 589959efbda5
Removing intermediate container bb9cbfd20623
Step 9 : USER postgres
 ---> Running in f70b8c70c3fc
 ---> 32d3d3d603d2
Removing intermediate container f70b8c70c3fc
Step 10 : RUN wget https://ftp.postgresql.org/pub/source/v${PG_VERSION}/postgresql-${PG_VERSION}.tar.bz2 -O /var/tmp/postgresql-${PG_VERSION}.tar.bz2
 ---> Running in c5cc11840a15
--2016-12-12 12:43:05--  https://ftp.postgresql.org/pub/source/v9.6.1/postgresql-9.6.1.tar.bz2
Resolving ftp.postgresql.org (ftp.postgresql.org)... 174.143.35.246, 217.196.149.55, 87.238.57.227, ...
Connecting to ftp.postgresql.org (ftp.postgresql.org)|174.143.35.246|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 19260568 (18M) [application/x-bzip]
Saving to: '/var/tmp/postgresql-9.6.1.tar.bz2'

     0K .......... .......... .......... .......... ..........  0%  180K 1m44s
    50K .......... .......... .......... .......... ..........  0%  368K 77s
...
 ---> 645cf59717f4
Removing intermediate container c5cc11840a15
Step 11 : RUN cd /var/tmp; tar -axf /var/tmp/postgresql-${PG_VERSION}.tar.bz2
 ---> Running in 6a47968ddeb5
...
# PostgreSQL configure, make, make install
...
PostgreSQL installation complete.
 ---> 7f6b11b357d7
Removing intermediate container 041441816c4d
Step 16 : RUN rm -rf /var/tmp/postgres*
 ---> Running in 480cc2157b9a
 ---> a7b0bf1d1c35
Removing intermediate container 480cc2157b9a
Successfully built a7b0bf1d1c35

Once all of that completed we have a new Docker image:

[docker@centos7 centospg]$ docker images
REPOSITORY          TAG                 IMAGE ID            CREATED             SIZE
centospg            PG9.6.1             a7b0bf1d1c35        45 seconds ago      706.7 MB
centospg            7.2.1511            184509483d52        47 hours ago        510.6 MB
postgres            latest              78e3985acac0        4 days ago          264.7 MB
hello-world         latest              c54a2cc56cbb        5 months ago        1.848 kB

Using this image we can now bring up a container which complies to all our standards we build into the base images:

[docker@centos7 centospg]$ docker run -it centospg:PG9.6.1 bash
[postgres@7ac7780b0b1b /]$ which initdb
/u01/app/postgres/product/96/db_01/bin/initdb
[postgres@7ac7780b0b1b /]$ initdb --version
initdb (PostgreSQL) 9.6.1

Finally we commit our Dockerfile:

[docker@centos7 centospg]$ git commit -m "First working version" Dockerfile 
[master f0ba897] First working version
 1 file changed, 25 insertions(+)

Something to start with, isn’t it?

Note: The Docker best practices tell you to add only few instructions to a Dockerfile because every instruction creates a new layer. In general you should run only one service per image. This makes it easier to scale you containers.

 

Cet article Getting started with Docker – 2 – building your own base image est apparu en premier sur Blog dbi services.

Getting started with Docker – 1 – overview and installation

Sun, 2016-12-11 04:30

Everybody is talking about Docker nowadays. What it is about? Do you remember Solaris Zones or Containers? It is more or less the same although development of Docker during the last years made Linux Containers the de-facto standard for deploying applications in a standardized and isolated way. Docker is build in a classical client server model: There is the docker server (or daemon) which servers the requests of docker clients. The client is the one you’ll use to tell the server what you want to do. The main difference from the classical client/server model is that docker uses the same binary for the server as well as for the client. It is just a matter of how you invoke the docker binary that makes it a server or client application. In contrast to the Solaris Zones Docker containers are stateless by default, that means: When you shutdown a docker container you’ll lose everything that was done when the container started to what happened when container got destroyed (Although there are ways to avoid that). This is important to remember.

When you start a docker container on a host the host’s resources are shared with the container (Although you can limit that). It is not like when you fire up a virtual machine (which brings up an instance of a whole operating system) but more like a process that shares resources with the host it is running on. This might be as simple as running a “wget” command but it might be as complicated as bringing up a whole infrastructure that serves your service desk. Docker containers should be lightweight.

So what does make docker unique then? It is the concept of a layered filesystem. We’ll come to that soon. Lets start by installing everything we need to run a docker daemon. As always we’ll start with as CentOS 7 minimal installation:

[root@centos7 ~]$ cat /etc/centos-release
CentOS Linux release 7.2.1511 (Core) 
[root@centos7 ~]$ 

The easiest way to get docker installed is to add the official docker yum repository (for CentOS in this case):

[root@centos7 ~]$ echo "[dockerrepo]
name=Docker Repository
baseurl=https://yum.dockerproject.org/repo/main/centos/7/
enabled=1
gpgcheck=1
gpgkey=https://yum.dockerproject.org/gpg" > /etc/yum.repos.d/docker.repo

Working directly as root never is a good idea so lets create a user for that and let this user do everything via sudo ( not a good practice, I know :) ):

[root@centos7 ~]$ groupadd docker
[root@centos7 ~]$ useradd -g docker docker
[root@centos7 ~]$ passwd docker
[root@centos7 ~]$ echo "docker ALL=(ALL)   NOPASSWD: ALL" >> /etc/sudoers
[root@centos7 ~]$ su - docker
[docker@centos7 ~]$ sudo ls

Ready to install:

[docker@centos7 ~]$ sudo yum install docker-engine

This will install the docker engine and these additional packages:

======================================================================================================================================
 Package                                Arch                   Version                               Repository                  Size
======================================================================================================================================
Installing:
 docker-engine                          x86_64                 1.12.3-1.el7.centos                   dockerrepo                  19 M
Installing for dependencies:
 audit-libs-python                      x86_64                 2.4.1-5.el7                           base                        69 k
 checkpolicy                            x86_64                 2.1.12-6.el7                          base                       247 k
 docker-engine-selinux                  noarch                 1.12.3-1.el7.centos                   dockerrepo                  28 k
 libcgroup                              x86_64                 0.41-8.el7                            base                        64 k
 libseccomp                             x86_64                 2.2.1-1.el7                           base                        49 k
 libsemanage-python                     x86_64                 2.1.10-18.el7                         base                        94 k
 libtool-ltdl                           x86_64                 2.4.2-21.el7_2                        updates                     49 k
 policycoreutils-python                 x86_64                 2.2.5-20.el7                          base                       435 k
 python-IPy                             noarch                 0.75-6.el7                            base                        32 k
 setools-libs                           x86_64                 3.3.7-46.el7                          base                       485 k

Transaction Summary
======================================================================================================================================

Enable the service:

[docker@centos7 ~]$ sudo systemctl enable docker.service
Created symlink from /etc/systemd/system/multi-user.target.wants/docker.service to /usr/lib/systemd/system/docker.service.

Start the service:

[docker@centos7 ~]$ sudo systemctl start docker
[docker@centos7 ~]$ sudo systemctl status docker
● docker.service - Docker Application Container Engine
   Loaded: loaded (/usr/lib/systemd/system/docker.service; enabled; vendor preset: disabled)
   Active: active (running) since Sat 2016-12-10 12:26:46 CET; 6s ago
     Docs: https://docs.docker.com
 Main PID: 2957 (dockerd)
   Memory: 12.9M
   CGroup: /system.slice/docker.service
           ├─2957 /usr/bin/dockerd
           └─2960 docker-containerd -l unix:///var/run/docker/libcontainerd/docker-containerd.sock --shim docker-containerd-shim --...

Dec 10 12:26:45 centos7.local dockerd[2957]: time="2016-12-10T12:26:45.481380483+01:00" level=info msg="Graph migration to co...conds"
Dec 10 12:26:45 centos7.local dockerd[2957]: time="2016-12-10T12:26:45.481751429+01:00" level=warning msg="mountpoint for pid...found"
Dec 10 12:26:45 centos7.local dockerd[2957]: time="2016-12-10T12:26:45.481751451+01:00" level=info msg="Loading containers: start."
Dec 10 12:26:45 centos7.local dockerd[2957]: time="2016-12-10T12:26:45.574330143+01:00" level=info msg="Firewalld running: false"
Dec 10 12:26:45 centos7.local dockerd[2957]: time="2016-12-10T12:26:45.822997195+01:00" level=info msg="Default bridge (docke...dress"
Dec 10 12:26:46 centos7.local dockerd[2957]: time="2016-12-10T12:26:46.201798804+01:00" level=info msg="Loading containers: done."
Dec 10 12:26:46 centos7.local dockerd[2957]: time="2016-12-10T12:26:46.201984648+01:00" level=info msg="Daemon has completed ...ation"
Dec 10 12:26:46 centos7.local dockerd[2957]: time="2016-12-10T12:26:46.202003760+01:00" level=info msg="Docker daemon" commit...1.12.3
Dec 10 12:26:46 centos7.local dockerd[2957]: time="2016-12-10T12:26:46.207416263+01:00" level=info msg="API listen on /var/ru....sock"
Dec 10 12:26:46 centos7.local systemd[1]: Started Docker Application Container Engine.
Hint: Some lines were ellipsized, use -l to show in full.

And we’re done. Lets check if docker is working as expected:

[docker@centos7 ~]$ sudo docker run --rm hello-world
Unable to find image 'hello-world:latest' locally
latest: Pulling from library/hello-world
c04b14da8d14: Pull complete 
Digest: sha256:0256e8a36e2070f7bf2d0b0763dbabdd67798512411de4cdcf9431a1feb60fd9
Status: Downloaded newer image for hello-world:latest

Hello from Docker!
This message shows that your installation appears to be working correctly.

To generate this message, Docker took the following steps:
 1. The Docker client contacted the Docker daemon.
 2. The Docker daemon pulled the "hello-world" image from the Docker Hub.
 3. The Docker daemon created a new container from that image which runs the
    executable that produces the output you are currently reading.
 4. The Docker daemon streamed that output to the Docker client, which sent it
    to your terminal.

To try something more ambitious, you can run an Ubuntu container with:
 $ docker run -it ubuntu bash

Share images, automate workflows, and more with a free Docker Hub account:

https://hub.docker.com

For more examples and ideas, visit:

https://docs.docker.com/engine/userguide/

What happened here is that we already executed our first docker image: “hello-world”. The “–rm” flag tells docker to automatically remove the image once it exits. As the image was not available on our host it was automatically downloaded from the docker hub:

Unable to find image 'hello-world:latest' locally
latest: Pulling from library/hello-world
c04b14da8d14: Pull complete 
Digest: sha256:0256e8a36e2070f7bf2d0b0763dbabdd67798512411de4cdcf9431a1feb60fd9
Status: Downloaded newer image for hello-world:latest

You can browse the docker hub for many, many other images using your favorite browser or you can use the command line:

[docker@centos7 ~]$ docker search postgres
NAME                      DESCRIPTION                                     STARS     OFFICIAL   AUTOMATED
postgres                  The PostgreSQL object-relational database ...   2939                 [OK]       
kiasaki/alpine-postgres   PostgreSQL docker image based on Alpine Linux   28                   [OK]
abevoelker/postgres       Postgres 9.3 + WAL-E + PL/V8 and PL/Python...   10                   [OK]
onjin/alpine-postgres     PostgreSQL / v9.1 - v9.6 / size <  50MB      9                    [OK]
macadmins/postgres        Postgres that accepts remote connections b...   8                    [OK]
jamesbrink/postgres       Highly configurable PostgreSQL container.       5                    [OK]
eeacms/postgres           Docker image for PostgreSQL (RelStorage re...   4                    [OK]
cptactionhank/postgres                                                    4                    [OK]
azukiapp/postgres         Docker image to run PostgreSQL by Azuki - ...   2                    [OK]
kampka/postgres           A postgresql image build on top of an arch...   2                    [OK]
clkao/postgres-plv8       Docker image for running PLV8 1.4 on Postg...   2                    [OK]
2020ip/postgres           Docker image for PostgreSQL with PLV8           1                    [OK]
steenzout/postgres        Steenzout's docker image packaging for Pos.1                    [OK]
blacklabelops/postgres    Postgres Image for Atlassian Applications       1                    [OK]
buker/postgres            postgres                                        0                    [OK]
kobotoolbox/postgres      Postgres image for KoBo Toolbox.                0                    [OK]
vrtsystems/postgres       PostgreSQL image with added init hooks, bu...   0                    [OK]
timbira/postgres          Postgres  containers                            0                    [OK]
coreroller/postgres       official postgres:9.4 image but it adds 2 ...   0                    [OK]
livingdocs/postgres       Postgres v9.3 with the plv8 extension inst...   0                    [OK]
1maa/postgres             PostgreSQL base image                           0                    [OK]
opencog/postgres          This is a configured postgres database for...   0                    [OK]
khipu/postgres            postgres with custom uids                       0                    [OK]
travix/postgres           A container to run the PostgreSQL database.     0                    [OK]
beorc/postgres            Ubuntu-based PostgreSQL server                  0                    [OK]

The first one is the official PostgreSQL image. How do I run it?

[docker@centos7 ~]$ docker run -it postgres
Unable to find image 'postgres:latest' locally
latest: Pulling from library/postgres
386a066cd84a: Pull complete 
e6dd80b38d38: Pull complete 
9cd706823821: Pull complete 
40c17ac202a9: Pull complete 
7380b383ba3d: Pull complete 
538e418b46ce: Pull complete 
c3b9d41b7758: Pull complete 
dd4f9522dd30: Pull complete 
920e548f9635: Pull complete 
628af7ef2ee5: Pull complete 
004275e6f5b5: Pull complete 
Digest: sha256:e761829c4b5ec27a0798a867e5929049f4cbf243a364c81cad07e4b7ac2df3f1
Status: Downloaded newer image for postgres:latest
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D /var/lib/postgresql/data -l logfile start

****************************************************
WARNING: No password has been set for the database.
         This will allow anyone with access to the
         Postgres port to access your database. In
         Docker's default configuration, this is
         effectively any other container on the same
         system.

         Use "-e POSTGRES_PASSWORD=password" to set
         it in "docker run".
****************************************************
waiting for server to start....LOG:  database system was shut down at 2016-12-10 11:42:01 UTC
LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started
 done
server started
ALTER ROLE


/docker-entrypoint.sh: ignoring /docker-entrypoint-initdb.d/*

waiting for server to shut down....LOG:  received fast shutdown request
LOG:  aborting any active transactions
LOG:  autovacuum launcher shutting down
LOG:  shutting down
LOG:  database system is shut down
 done
server stopped

PostgreSQL init process complete; ready for start up.

LOG:  database system was shut down at 2016-12-10 11:42:04 UTC
LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started

And ready. As with the “hello:world” image docker had to download the image as it was not available locally. Once that was done the image was started and new PostgreSQL instance was created automatically. Here you can see what the layered filesystem is about:

386a066cd84a: Pull complete 
e6dd80b38d38: Pull complete 
9cd706823821: Pull complete 
40c17ac202a9: Pull complete 
7380b383ba3d: Pull complete 
538e418b46ce: Pull complete 
c3b9d41b7758: Pull complete 
dd4f9522dd30: Pull complete 
920e548f9635: Pull complete 
628af7ef2ee5: Pull complete 
004275e6f5b5: Pull complete 

Each of this lines represents a layered/stacked filesystem on top of the previous one. This is an important concept because when you change things only the layer that contains the change needs to be rebuild, but not the layers below. In other words you could build an image based on a CentOS 7 image and then deploy your changes on top of that. You deliver that image and some time later you need to make some modifications: The only thing you need to deliver are the modifications you did because the layers below did not change.

You will notice that you cannot type any command when the image was started. As soon as you enter “CRTL-C” the container will shutdown (this is because of the “-it” switch, which is “interactive” and “pseudo terminal”):

^CLOG:  received fast shutdown request
LOG:  aborting any active transactions
LOG:  autovacuum launcher shutting down
LOG:  shutting down
LOG:  database system is shut down

Everything what happened inside the container is now gone. The correct way to launch it is:

[docker@centos7 ~]$ docker run --name my-first-postgres -e POSTGRES_PASSWORD=postgres -d postgres
d51abc52108d3040817474fa8c85ab15020c12cb753515543c2d064143277155

The “-d” switch tells docker to detach, so we get back our shell. The magic string dockers returns is the container id:

[docker@centos7 ~]$ docker ps --no-trunc
CONTAINER ID                                                       IMAGE               COMMAND                            CREATED             STATUS              PORTS               NAMES
d51abc52108d3040817474fa8c85ab15020c12cb753515543c2d064143277155   postgres            "/docker-entrypoint.sh postgres"   3 minutes ago       Up 3 minutes        5432/tcp            my-first-postgres

When you want to know what images you have available locally you can ask docker for that:

[docker@centos7 ~]$ docker images 
REPOSITORY          TAG                 IMAGE ID            CREATED             SIZE
postgres            latest              78e3985acac0        2 days ago          264.7 MB
hello-world         latest              c54a2cc56cbb        5 months ago        1.848 kB

How do you now connect to the PostgreSQL image?

[docker@centos7 ~]$ docker run -it --rm --link my-first-postgres:postgres postgres psql -h postgres -U postgres
Password for user postgres: 
psql (9.6.1)
Type "help" for help.

postgres=# \l+
                                                                   List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   |  Size   | Tablespace |                Description                 
-----------+----------+----------+------------+------------+-----------------------+---------+------------+--------------------------------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |                       | 7063 kB | pg_default | default administrative connection database
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +| 6953 kB | pg_default | unmodifiable empty database
           |          |          |            |            | postgres=CTc/postgres |         |            | 
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +| 6953 kB | pg_default | default template for new databases
           |          |          |            |            | postgres=CTc/postgres |         |            | 
(3 rows)

Or to get bash:

[docker@centos7 ~]$ docker run -it --rm --link my-first-postgres:postgres postgres bash
root@f8c3b3738336:/$ cat /etc/os-release 
PRETTY_NAME="Debian GNU/Linux 8 (jessie)"
NAME="Debian GNU/Linux"
VERSION_ID="8"
VERSION="8 (jessie)"
ID=debian
HOME_URL="http://www.debian.org/"
SUPPORT_URL="http://www.debian.org/support"
BUG_REPORT_URL="https://bugs.debian.org/"

Ok, this PostgreSQL image is based on Debian 8. Lets say this is not what I like because I want my PostgreSQL image based on CentOS. This is the topic for the next post: We’ll build our own CentOS image and get deeper in what the stacked filesystem is about. Once we’ll have that available we’ll use that image to build a PostgreSQL image on top of that.

 

Cet article Getting started with Docker – 1 – overview and installation est apparu en premier sur Blog dbi services.

Live from SQL Saturday Slovenia 2016!

Sat, 2016-12-10 04:18

SQL Saturday

After a little trip, just 1-hour flying from Zürich to Ljubljana yesterday, the SQL Saturday Slovenia 2016 begins this morning at the Faculty of Computer and Information Science of the University of Ljubljana

IMG_3774

I needed to wake up very fast because my session was the first of the day at 9:00 AM.

IMG_3775

I also very happy to meet and to share my expertise with Slovenian and other SQL Server experts.

My session was about the famous ErrorLog.

As a DBA, the Error log is an essential daily tool in our life.

Learning and understanding its content are not the last part of the job, we also have to manage it to obtain a better interpretation.

This session is to answer of questions that you perhaps never ask yourself:

  • What are ErrorLog files?
  • Where are ErrorLog files?
  • Need I manage the ErrorLog?
  • How to read and understand?
  • How to write in the ErrorLog?

IMG_3776

I hope that people have learn something on this session!

You can download the presentation here.

I will thank all organizers of this very nice event and a special thanks to Vedran for these photos!

Now, I will also go to see the others sessions. ;-)

I give you « rendez-vous » in the IT-Tage in Tuesday for this session in german!

 

Cet article Live from SQL Saturday Slovenia 2016! est apparu en premier sur Blog dbi services.

Oracle 12c – RMAN and Unified Auditing – Does it really work?

Fri, 2016-12-09 09:44

The new Oracle Unified Auditing feature, audits RMAN operation per default as soon as
you relink your Oracle binary and start your instance. A quite cool new feature, because it allows me
to audit RMAN operation out of the box. For example, someone could create a RMAN backup to ‘/tmp’ and then copy it to somewhere else. And I would like to know that. ;-)

Oracle added 5 columns to the unified_audit_trail view only for RMAN, to find out what RMAN operation was done on the database. The new columns are the following.

  • RMAN_SESSION_RECID
  • RMAN_SESSION_STAMP
  • RMAN_OPERATION
  • RMAN_OBJECT_TYPE
  • RMAN_DEVICE_TYPE

Due to the Oracle documentation, the column description is the following.

RMAN_SESSION_RECID

RMAN session identifier. Together with RMAN_SESSION_STAMP uniquely identifies an RMAN job
(note that this is not same as user session ID; the value is a recid in controlfile that identifies RMAN job)

RMAN_SESSION_STAMP

Timestamp for the session

RMAN_OPERATION

The RMAN operation executed by the job. One row will be added for each distinct operation within an RMAN session. For example, a backup job would contain BACKUP in the RMAN_OPERATION column.

RMAN_OBJECT_TYPE

Type of objects involved for backup or restore/recover or change/delete/crosscheck commands. It contains one of the following values. If RMAN command does not satisfy one of them,  then preference is given in order, from top to bottom of the list:

  • DB FULL
  • RECVR AREA
  • DB INCR
  • DATAFILE FULL
  • DATAFILE INCR
  • ARCHIVELOG
  • CONTROLFILE
  • SPFILE
RMAN_DEVICE_TYPE

Device involved in the RMAN job. It may be DISK or SBT_TAPE or *  (An * indicates that more than one location is involved). For a backup job, it will be the output device type. For other commands (such as restore or crosscheck),  it will be the input device type.

Ok. Let’s start with a first test. Just for the protocol, I am using here 12cR1 PSU Oct 2016.

First of all, I am activating the “immediate-write” feature, meaning that audit records are written immediately,  and not to the audit buffer first.

SQL> select parameter, value from v$option where parameter like '%Unified%';

PARAMETER              VALUE
---------------------- --------
Unified Auditing       TRUE

-- Modify OUA to use the immediate-write mode

SQL> BEGIN
  2  DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(
  3  DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
  4  DBMS_AUDIT_MGMT.AUDIT_TRAIL_WRITE_MODE,
  5  DBMS_AUDIT_MGMT.AUDIT_TRAIL_IMMEDIATE_WRITE);
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL> select * from DBA_AUDIT_MGMT_CONFIG_PARAMS where PARAMETER_NAME = 'AUDIT WRITE MODE';

PARAMETER_NAME                   PARAMETER_VALUE        AUDIT_TRAIL
-------------------------------- ---------------------- ----------------------------
AUDIT WRITE MODE                 IMMEDIATE WRITE MODE   UNIFIED AUDIT TRAIL

 

Ok. Cool. So far so good. Let’s start a RMAN backup job now.

 

oracle@dbidg01:/home/oracle/ [DBIT121] rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Dec 9 15:59:44 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DBIT121 (DBID=172831209)

RMAN> backup database plus archivelog delete input;
Starting backup at 09-DEC-2016 16:03:41
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=12 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=22 RECID=6 STAMP=930153822
channel ORA_DISK_1: starting piece 1 at 09-DEC-2016 16:03:43
channel ORA_DISK_1: finished piece 1 at 09-DEC-2016 16:03:44
piece handle=/u03/fast_recovery_area/DBIT121_SITE1/backupset/2016_12_09/o1_mf_annnn_TAG20161209T160342_d4okyh1t_.bkp tag=TAG20161209T160342 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u03/fast_recovery_area/DBIT121_SITE1/archivelog/2016_12_09/o1_mf_1_22_d4okyfo5_.arc RECID=6 STAMP=930153822
Finished backup at 09-DEC-2016 16:03:44

Starting backup at 09-DEC-2016 16:03:44
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/u02/oradata/DBIT121_SITE1/datafile/o1_mf_example_d4fjz1fz_.dbf
input datafile file number=00001 name=/u02/oradata/DBIT121_SITE1/datafile/o1_mf_system_d4fjt03j_.dbf
input datafile file number=00003 name=/u02/oradata/DBIT121_SITE1/datafile/o1_mf_sysaux_d4fjrlvs_.dbf
input datafile file number=00004 name=/u02/oradata/DBIT121_SITE1/datafile/o1_mf_undotbs1_d4fjvtd1_.dbf
input datafile file number=00006 name=/u02/oradata/DBIT121_SITE1/datafile/o1_mf_users_d4fjvqb1_.dbf
channel ORA_DISK_1: starting piece 1 at 09-DEC-2016 16:03:44
channel ORA_DISK_1: finished piece 1 at 09-DEC-2016 16:05:19
piece handle=/u03/fast_recovery_area/DBIT121_SITE1/backupset/2016_12_09/o1_mf_nnndf_TAG20161209T160344_d4okyjny_.bkp tag=TAG20161209T160344 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:35
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 09-DEC-2016 16:05:20
channel ORA_DISK_1: finished piece 1 at 09-DEC-2016 16:05:21
piece handle=/u03/fast_recovery_area/DBIT121_SITE1/backupset/2016_12_09/o1_mf_ncsnf_TAG20161209T160344_d4ol1jnj_.bkp tag=TAG20161209T160344 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 09-DEC-2016 16:05:21

Starting backup at 09-DEC-2016 16:05:21
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=23 RECID=7 STAMP=930153921
channel ORA_DISK_1: starting piece 1 at 09-DEC-2016 16:05:21
channel ORA_DISK_1: finished piece 1 at 09-DEC-2016 16:05:23
piece handle=/u03/fast_recovery_area/DBIT121_SITE1/backupset/2016_12_09/o1_mf_annnn_TAG20161209T160521_d4ol1ktz_.bkp tag=TAG20161209T160521 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u03/fast_recovery_area/DBIT121_SITE1/archivelog/2016_12_09/o1_mf_1_23_d4ol1kpj_.arc RECID=7 STAMP=930153921
Finished backup at 09-DEC-2016 16:05:23

RMAN>

 

After my RMAN Backup has finished, I open another session and checked the unified_audit_trail, but nothing is there.

SQL> select EVENT_TIMESTAMP, ACTION_NAME, RMAN_SESSION_RECID,
  2  RMAN_SESSION_STAMP, RMAN_OPERATION, RMAN_OBJECT_TYPE, RMAN_DEVICE_TYPE
  3  from unified_audit_trail where ACTION_NAME like '%RMAN%' order by 1;

no rows selected

Now I do a clean exit of my RMAN session, and here we go. Now I have an audit entry, saying that a RMAN backup to disk took place. Perfect, this is exactly what I wanted to see.

...
RMAN> exit

Recovery Manager complete.


SQL> select EVENT_TIMESTAMP, ACTION_NAME, RMAN_SESSION_RECID,
  2  RMAN_SESSION_STAMP, RMAN_OPERATION, RMAN_OBJECT_TYPE, RMAN_DEVICE_TYPE
  3  from unified_audit_trail where ACTION_NAME like '%RMAN%' order by 1;

EVENT_TIMESTAMP              ACTION_NAME    RMAN_SESSION_RECID RMAN_SESSION_STAMP RMAN_OPERATION       RMAN_OBJECT_TYPE     RMAN_
---------------------------- -------------- ------------------ ------------------ -------------------- -------------------- -----
09-DEC-16 04.08.10.532931 PM RMAN ACTION                    22          930153584 Backup               DB Full              Disk

 

This brings me to an idea. What happens if a Hacker logs into my system, starts a RMAN backup, and kills his own RMAN session, after the backup has finished? Sounds crazy, but Hackers are usually very creative.

Ok. The Hacker logs in now, and because the Hacker is smart, he gives his RMAN backup a TAG, so it is easier to delete it afterwards.

oracle@dbidg01:/home/oracle/ [DBIT121] rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Dec 9 16:09:58 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DBIT121 (DBID=172831209)

RMAN> alter system archive log current;

using target database control file instead of recovery catalog
Statement processed

RMAN> backup archivelog all format '/tmp/%U' TAG 'HACKER';

Starting backup at 09-DEC-2016 16:11:58
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=12 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=24 RECID=8 STAMP=930154279
input archived log thread=1 sequence=25 RECID=9 STAMP=930154318
channel ORA_DISK_1: starting piece 1 at 09-DEC-2016 16:11:59
channel ORA_DISK_1: finished piece 1 at 09-DEC-2016 16:12:00
piece handle=/tmp/0ern21qf_1_1 tag=HACKER comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 09-DEC-2016 16:12:00

 

At this point, still no further entry in the unified_audit_trail.

SQL> r
  1  select EVENT_TIMESTAMP, ACTION_NAME, RMAN_SESSION_RECID,
  2  RMAN_SESSION_STAMP, RMAN_OPERATION, RMAN_OBJECT_TYPE, RMAN_DEVICE_TYPE
  3* from unified_audit_trail where ACTION_NAME like '%RMAN%' order by 1

EVENT_TIMESTAMP              ACTION_NAME    RMAN_SESSION_RECID RMAN_SESSION_STAMP RMAN_OPERATION       RMAN_OBJECT_TYPE     RMAN_
---------------------------- -------------- ------------------ ------------------ -------------------- -------------------- -----
09-DEC-16 04.08.10.532931 PM RMAN ACTION                    22          930153584 Backup               DB Full              Disk

Meanwhile, the Hacker copies the data away, and because the Hacker is a good boy, he cleans up everything afterwards. :-)

RMAN> delete noprompt backuppiece tag=HACKER;

using channel ORA_DISK_1

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
14      14      1   1   AVAILABLE   DISK        /tmp/0ern21qf_1_1
deleted backup piece
backup piece handle=/tmp/0ern21qf_1_1 RECID=14 STAMP=930154319
Deleted 1 objects

At the moment, there is still nothing new in the unified_audit_trail. Now, to avoid entries into the unified_audit_trail view, the hacker kills his own session.

oracle@dbidg01:/tmp/ [DBIT121] ps -ef | grep rman | grep -v grep
oracle    8829  2839  0 16:09 pts/1    00:00:00 rman target /
oracle@dbidg01:/tmp/ [DBIT121] kill -9 8829


...
RMAN> Killed

And now the 1Million Dollar question … do we have a new entry or not?

SQL> r
  1  select EVENT_TIMESTAMP, ACTION_NAME, RMAN_SESSION_RECID,
  2  RMAN_SESSION_STAMP, RMAN_OPERATION, RMAN_OBJECT_TYPE, RMAN_DEVICE_TYPE
  3* from unified_audit_trail where ACTION_NAME like '%RMAN%' order by 1

EVENT_TIMESTAMP              ACTION_NAME    RMAN_SESSION_RECID RMAN_SESSION_STAMP RMAN_OPERATION       RMAN_OBJECT_TYPE     RMAN_
---------------------------- -------------- ------------------ ------------------ -------------------- -------------------- -----
09-DEC-16 04.08.10.532931 PM RMAN ACTION                    22          930153584 Backup               DB Full              Disk

No, no new entry. This entry is still the one from my regular RMAN backup with the clean exit.

Conclusion

Don’t rely too much on the unified_audit_trail records, in case you want to audit RMAN backups.

Cheers,
William

 

 

Cet article Oracle 12c – RMAN and Unified Auditing – Does it really work? est apparu en premier sur Blog dbi services.

EnterpriseDB Backup and Recovery Tool (BART)

Thu, 2016-12-08 14:21

Many tools are proposed for backup and recovery operation of a PostgreSQL database. EnterpriseDB has developed a product named BART (Backup and Recovery Tool). Use of this product needs a subscription but you can download a trial version for test purpose.
In this blog we are using BART with PostgreSQL 9.6.1. BART allows backing up over the net and can centralize backups of many PostgreSQL servers.
We are using the following configuration:
Bart Host: pgserver.localdomain
PostgreSQL server 1: pgserve1.localdomain
PostgreSQL server 2: pgserve2.localdomain
We suppose that there are clusters running on pgserver1 and pgserver2 and the archive mode is activated for both clusters
What is needed on the server hosting BART (i.e. pgserver in our case)?
1. We need to install rpm package using yum or rpm command
2. We need PostgreSQL binaries installed. Note that the version of the binaries should be at least the same than those installed on the servers to be backed up. In our case as we are using 9.6.1 for databases, binaries should be 9.6.1 or higher. Note that we don’t need any cluster running on pgserver.
For the package installation, we are using yum.

[root@pgserver 96]# yum localinstall edb-bart-1.1.0-1.rhel7.x86_64.rpm

By default BART is installed in /usr/edb-bart-1.1.

[root@pgserver 96]# cd /usr/edb-bart-1.1/
[root@pgserver edb-bart-1.1]# ls
bart_license.txt bin etc
[root@pgserver edb-bart-1.1]# ls -l
total 16
-rw-r--r--. 1 root root 15272 Jul 3 2015 bart_license.txt
drwxr-xr-x. 2 root root 17 Dec 6 14:15 bin
drwxr-xr-x. 2 root root 21 Dec 6 14:15 etc
[root@pgserver edb-bart-1.1]#

Before configuring the BART configuration file, the ssh connectivity should be configured between servers for the Linux user used for backups, here we are using postgres.

[postgres@pgserver ~]$ ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/home/postgres/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/postgres/.ssh/id_rsa.
Your public key has been saved in /home/postgres/.ssh/id_rsa.pub.
The key fingerprint is:
b9:fb:1a:90:c2:76:73:6e:fe:28:38:cf:42:52:7a:81 postgres@pgserver.localdomain
The key's randomart image is:
+--[ RSA 2048]----+
| |
| |
| . |
| E.o . . |
| o+.= S |
| o.oo = . |
| + . = |
| +..o + |
| +o.=+o |
+-----------------+
[postgres@pgserver ~]$ ssh-copy-id -i .ssh/id_rsa.pub pgserver1
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
postgres@pgserver1's password:
Number of key(s) added: 1
Now try logging into the machine, with: "ssh 'pgserver1'"
and check to make sure that only the key(s) you wanted were added.
[postgres@pgserver ~]$ ssh-copy-id -i .ssh/id_rsa.pub pgserver2
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
postgres@pgserver2's password:
Number of key(s) added: 1
Now try logging into the machine, with: "ssh 'pgserver2'"
and check to make sure that only the key(s) you wanted were added.
[postgres@pgserver ~]$


[postgres@pgserver ~]$ ssh pgserver1 date
Tue Dec 6 14:24:35 CET 2016
[postgres@pgserver ~]$ ssh pgserver2 date
Tue Dec 6 14:24:40 CET 2016
[postgres@pgserver ~]$

We also need a super user created on each cluster to be backed up

postgres=# CREATE ROLE backupuser WITH LOGIN SUPERUSER PASSWORD 'root';
CREATE ROLE
postgres=#

And we will create a .pgpass for this user to be able to connect without being asked for password

[postgres@pgserver1 ~]$ echo "*:5432:*:backupuser:root" >> ~/.pgpass
[postgres@pgserver1 ~]$ chmod 600 .pgpass

[postgres@pgserver2 ~]$ echo "*:5432:*:backupuser:root" >> ~/.pgpass
[postgres@pgserver2 ~]$ chmod 600 .pgpass

The configuration file is located under $BART_INSTALL_DIRECTORY/etc. It must contain a part for BART itself and a part for each server to be backed up. Below an example of our bart.cfg

[root@pgserver etc]# pwd
/usr/edb-bart-1.1/etc


[root@pgserver etc]# cat bart.cfg | grep -v ^#
[BART] bart-host= postgres@192.168.56.35
backup_path = /u01/app/backup
pg_basebackup_path = /u01/app/PostgreSQL/9.6/bin/pg_basebackup
logfile = /tmp/bart.log
xlog-method = fetch
retention_policy = 1 DAYS
wal_compression = enabled

[PGSERVER1] host = 192.168.56.36
port = 5432
user = backupuser
backup-name = PGSERVER1_%year-%month-%dayT%hour:%minute
remote-host = postgres@pgserver1
description = "Postgres server1"

[PGSERSERVER2] host = 192.168.56.37
port = 5432
user = backupuser
backup-name = PGSERVER2_%year-%month-%dayT%hour:%minute
remote-host = postgres@pgserver2
description = "Postgres server2"
[root@pgserver etc]#

The backup_path /u01/app/backup is only needed on the BART host (pgserver) as backup will be done over the net. But on both servers pgserver1 and pgserver2 the pg_hba.conf need to be updated to allow connection from the BART host. Below two lines we add

[postgres@pgserver1 data]$ grep 192.168.56.0 pg_hba.conf
host all all 192.168.56.0/24 md5
host replication backupuser 192.168.56.0/24 md5
[postgres@pgserver1 data]$


[postgres@pgserver2 data]$ grep 192.168.56.0 pg_hba.conf
host all all 192.168.56.0/24 md5
host replication backupuser 192.168.56.0/24 md5
[postgres@pgserver2 data]$

Now we can initialize the catalog, first for pgserver1 and after for pgserver2 (note this can be done for both servers in a single command)

[postgres@pgserver bin]$ ./bart -d -c /usr/edb-bart-1.1/etc/bart.cfg init -s PGSERVER1 -o
DEBUG: Server: Global, Now: 2016-12-06 15:51:04 CET, RetentionWindow: 86400 (secs) ==> 24 hour(s)
INFO: setting archive_command for server 'pgserver1'
DEBUG: SHOW archive_command: test ! -f /u01/app/archive/%f && cp %p /u01/app/archive/%f
DEBUG: archive command set to: ALTER SYSTEM SET archive_command TO 'scp %p postgres@192.168.56.35:/u01/app/backup/pgserver1/archived_wals/%f'
WARNING: archive_command is set. server restart is required
[postgres@pgserver bin]$


[postgres@pgserver bin]$ ./bart -d -c /usr/edb-bart-1.1/etc/bart.cfg init -s PGSERVER2 -o
DEBUG: Server: Global, Now: 2016-12-06 19:01:08 CET, RetentionWindow: 86400 (secs) ==> 24 hour(s)
INFO: setting archive_command for server 'pgserver2'
DEBUG: SHOW archive_command: test ! -f /u01/app/archive/%f && cp %p /u01/app/archive/%f
DEBUG: archive command set to: ALTER SYSTEM SET archive_command TO 'scp %p postgres@192.168.56.35:/u01/app/backup/pgserver2/archived_wals/%f'
WARNING: archive_command is set. server restart is required
[postgres@pgserver bin]$

Now it’s time to launch a backup

[postgres@pgserver bin]$ ./bart -c /usr/edb-bart-1.1/etc/bart.cfg BACKUP -s PGSERVER1

INFO: creating backup for server 'pgserver1'
INFO: backup identifier: '1481037296469'
39433/39433 kB (100%), 1/1 tablespace

INFO: backup completed successfully
INFO: backup checksum: fb105a542d8856e9d5bd95410337463f of base.tar
INFO:
BACKUP DETAILS:
BACKUP STATUS: active
BACKUP IDENTIFIER: 1481037296469
BACKUP NAME: PGSERVER1_2016-12-06T16:14
BACKUP LOCATION: /u01/app/backup/pgserver1/1481037296469
BACKUP SIZE: 38.51 MB
BACKUP FORMAT: tar
XLOG METHOD: fetch
BACKUP CHECKSUM(s): 1
ChkSum File
fb105a542d8856e9d5bd95410337463f base.tar

TABLESPACE(s): 0
START WAL LOCATION: 000000010000000000000008
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 2016-12-06 16:14:56 CET
STOP TIME: 2016-12-06 16:14:58 CET
TOTAL DURATION: 2 sec(s)

[postgres@pgserver bin]$


postgres@pgserver bin]$ ./bart -c /usr/edb-bart-1.1/etc/bart.cfg BACKUP -s PGSERVER2

INFO: creating backup for server 'pgserver2'
INFO: backup identifier: '1481047475191'
39216/39216 kB (100%), 1/1 tablespace

INFO: backup completed successfully
INFO: backup checksum: 052d82f3df1def72e9962cdef965fe8f of base.tar
INFO:
BACKUP DETAILS:
BACKUP STATUS: active
BACKUP IDENTIFIER: 1481047475191
BACKUP NAME: PGSERVER2_2016-12-06T19:04
BACKUP LOCATION: /u01/app/backup/pgserver2/1481047475191
BACKUP SIZE: 38.30 MB
BACKUP FORMAT: tar
XLOG METHOD: fetch
BACKUP CHECKSUM(s): 1
ChkSum File
052d82f3df1def72e9962cdef965fe8f base.tar

TABLESPACE(s): 0
START WAL LOCATION: 000000010000000000000003
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 2016-12-06 19:04:36 CET
STOP TIME: 2016-12-06 19:04:38 CET
TOTAL DURATION: 2 sec(s)

[postgres@pgserver bin]$

We can list backups for pgserver2 for example

[postgres@pgserver bin]$ ./bart SHOW-BACKUPS -s pgserver2
SERVER NAME BACKUP ID BACKUP TIME BACKUP SIZE WAL(s) SIZE WAL FILES STATUS

pgserver2 1481047475191 2016-12-06 19:04:38 CET 38.30 MB 26.54 kB 1 active

[postgres@pgserver bin]$

It’s good to have a backup, but it is better to know how to restore. And now we are going to simulate a crash of pgserver2 and then do a restore. First let’s create a database and a table.

[postgres@pgserver2 data]$ psql
Password:
psql.bin (9.6.1)
Type "help" for help.
postgres=# create database sales;
CREATE DATABASE
postgres=# \c sales
You are now connected to database "sales" as user "postgres".
sales=# create table article(id int);
CREATE TABLE
sales=# insert into article values (1);
INSERT 0 1
sales=# table article;
id
----
1
(1 row)
sales=#

And then let’s do a backup

[postgres@pgserver bin]$ ./bart -c /usr/edb-bart-1.1/etc/bart.cfg BACKUP -s PGSERVER2
INFO: creating backup for server 'pgserver2'
INFO: backup identifier: '1481051725125'
46814/46814 kB (100%), 1/1 tablespace
INFO: backup completed successfully
INFO: backup checksum: 19069965fa53444b31459f6d984682d0 of base.tar
INFO:
BACKUP DETAILS:
BACKUP STATUS: active
BACKUP IDENTIFIER: 1481051725125
BACKUP NAME: PGSERVER2_2016-12-06T20:15
BACKUP LOCATION: /u01/app/backup/pgserver2/1481051725125
BACKUP SIZE: 45.72 MB
BACKUP FORMAT: tar
XLOG METHOD: fetch
BACKUP CHECKSUM(s): 1
ChkSum File
19069965fa53444b31459f6d984682d0 base.tar
TABLESPACE(s): 0
START WAL LOCATION: 000000010000000000000005
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 2016-12-06 20:15:28 CET
STOP TIME: 2016-12-06 20:15:28 CET
TOTAL DURATION: 0 sec(s)


[postgres@pgserver bin]$ ./bart SHOW-BACKUPS -s PGSERVER2
SERVER NAME BACKUP ID BACKUP TIME BACKUP SIZE WAL(s) SIZE WAL FILES STATUS
pgserver2 1481051725125 2016-12-06 20:15:28 CET 45.72 MB 16.00 MB 1 active
pgserver2 1481047475191 2016-12-06 19:04:38 CET 38.30 MB 16.03 MB 2 active
[postgres@pgserver bin]$

Now let’s crash our cluster

[root@pgserver2 9.6]# pwd
/u01/app/PostgreSQL/9.6
[root@pgserver2 9.6]# mv data/ data_crash
[root@pgserver2 9.6]#


[postgres@pgserver2 9.6]$ pg_ctl start
pg_ctl: directory "/u01/app/PostgreSQL/9.6/data" does not exist
[postgres@pgserver2 9.6]$

For the restore, we have to provide the backup id and the path to restore. This path must exist.

[postgres@pgserver bin]$ ./bart RESTORE -s pgserver2 -i 1481047475191 -p /u01/app/PostgreSQL/9.6/data
INFO: restoring backup '1481047475191' of server 'pgserver2'
INFO: restoring backup to postgres@pgserver2:/u01/app/PostgreSQL/9.6/data
INFO: base backup restored
INFO: copying WAL file(s) to postgres@pgserver2:/u01/app/PostgreSQL/9.6/data/archived_wals
INFO: archiving is disabled
[postgres@pgserver bin]$

Before starting cluster, let’s create a recovery.conf file

[postgres@pgserver2 data]$ cat recovery.conf
restore_command='cp /u01/app/PostgreSQL/9.6/data/archived_wals/%f %p'
[postgres@pgserver2 data]$

And then let’s start the cluster

[postgres@pgserver2 data]$ pg_ctl start
server starting
[postgres@pgserver2 data]$ 2016-12-06 20:33:01 CET LOG: redirecting log output to logging collector process
2016-12-06 20:33:01 CET HINT: Future log output will appear in directory "pg_log".

After the restore finished, the recovery.conf is automatically renamed to recovery.done because PostgreSQL will go into recovery mode again after a restart otherwise.

[postgres@pgserver2 data]$ cat recovery.done
restore_command='cp /u01/app/PostgreSQL/9.6/data/archived_wals/%f %p'
[postgres@pgserver2 data]$

While connecting we can verify our data

[postgres@pgserver2 data]$ psql
Password:
psql.bin (9.6.1)
Type "help" for help.
postgres=# \c sales
You are now connected to database "sales" as user "postgres".
sales=# table article
sales-# ;
id
----
1
(1 row)
sales=#

In this blog we have seen how to configure EDB BART, how to use it for backup and recovery.

 

Cet article EnterpriseDB Backup and Recovery Tool (BART) est apparu en premier sur Blog dbi services.

Installing PostgreSQL 9.6.1 with EnterpriseDB Installer

Thu, 2016-12-08 14:20

As you may know PostgreSQL 9.6 was released a few months ago. And EnterpriseDB (EDB) offers a package installer for installing the community version. This EDB package can be downloaded here.
In this blog we are going to install PostgreSQL using edb installer. The installation is done using a graphical tool. A user with root privileges must be used otherwise we will get an error. Indeed the default installation creates the systemd service which need root privileges. For example using the user postgres for installation

[postgres@pgserver1 96]$ chmod +x postgresql-9.6.1-1-linux-x64.run
[postgres@pgserver1 96]$

[postgres@pgserver1 96]$ ./postgresql-9.6.1-1-linux-x64.run

install1
An alternative is to launch the installer with the option “–extract-only” which does not require root (but then we do not get the service)

Now logged as root, let’s start

[root@pgserver1 96]# ./postgresql-9.6.1-1-linux-x64.run

install2
Click on Next
install3
Specify PGHOME
install4
Specify the PGDATA
install5
Specify the password for user postgres
install6
The default port is 5432
install7
Choose your Locale
install8
And launch installation process
install9
install10
Once installation done we can verify that PostgreSQL is running

[postgres@pgserver1 ~]$ ps -ef | grep postgres
root 3135 976 0 09:53 ? 00:00:00 sshd: postgres [priv] postgres 3140 3135 0 09:54 ? 00:00:00 sshd: postgres@pts/0
postgres 3142 3140 0 09:54 pts/0 00:00:00 -bash
root 3657 976 0 09:55 ? 00:00:00 sshd: postgres [priv] postgres 3661 3657 0 09:55 ? 00:00:00 sshd: postgres@pts/2
postgres 3664 3661 0 09:55 pts/2 00:00:00 -bash
postgres 6255 1 0 10:23 ? 00:00:00 /u01/app/PostgreSQL/9.6/bin/postgres -D /u01/app/PostgreSQL/9.6/data
postgres 6256 6255 0 10:23 ? 00:00:00 postgres: logger process
postgres 6258 6255 0 10:23 ? 00:00:00 postgres: checkpointer process
postgres 6259 6255 0 10:23 ? 00:00:00 postgres: writer process
postgres 6260 6255 0 10:23 ? 00:00:00 postgres: wal writer process
postgres 6261 6255 0 10:23 ? 00:00:00 postgres: autovacuum launcher process
postgres 6262 6255 0 10:23 ? 00:00:00 postgres: stats collector process
postgres 6332 3664 0 10:26 pts/2 00:00:00 ps -ef
postgres 6333 3664 0 10:26 pts/2 00:00:00 grep --color=auto postgres
[postgres@pgserver1 ~]$

A file pg_env.sh is created during installation and can be used to setup all variables we need for the connection

[postgres@pgserver1 9.6]$ pwd
/u01/app/PostgreSQL/9.6
[postgres@pgserver1 9.6]$ cp pg_env.sh /home/postgres/
[postgres@pgserver1 9.6]$ cd
[postgres@pgserver1 ~]$


[postgres@pgserver1 ~]$ chmod +x pg_env.sh
[postgres@pgserver1 ~]$ . ./pg_env.sh
[postgres@pgserver1 ~]$ env | grep PG
PGPORT=5432
PGUSER=postgres
PGDATABASE=postgres
PGLOCALEDIR=/u01/app/PostgreSQL/9.6/share/locale
PGDATA=/u01/app/PostgreSQL/9.6/data
[postgres@pgserver1 ~]$

And now we can connect

[postgres@pgserver1 ~]$ psql
Password:
psql.bin (9.6.1)
Type "help" for help.

postgres=#

We can see that installation of PostgreSQL edb 9.6 is very easy.

 

Cet article Installing PostgreSQL 9.6.1 with EnterpriseDB Installer est apparu en premier sur Blog dbi services.

Can I do it with PostgreSQL? – 7 – Partitioning

Thu, 2016-12-08 04:19

PostgreSQL supports tables up to 32TB. Do you want to be the one responsible for managing such a table? I guess not. Usually you start to partition your tables when they grow very fast and consume more than hundreds of gigabytes. Can PostgreSQL do this? Do you you know what table inheritance is? No? PostgreSQL implements partitioning by using table inheritance and constraint exclusion. Sounds strange? Lets have a look …

Us usual I am running the currently latest version of PostgreSQL:

postgres@pgbox:/home/postgres/ [PG961] psql postgres
psql (9.6.1 dbi services build)
Type "help" for help.

(postgres@[local]:5439) [postgres] > select version();
                                                          version                                                           
----------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.6.1 dbi services build on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit
(1 row)

Time: 0.513 ms
(postgres@[local]:5439) [postgres] > 

So, what is table inheritance. In PostgreSQL you do things like this:

(postgres@[local]:5439) [postgres] > create table databases ( name varchar(10), vendor varchar(10) );
CREATE TABLE
Time: 20.477 ms
(postgres@[local]:5439) [postgres] > create table databases_rdbms ( rdbms boolean ) inherits (databases);
CREATE TABLE
Time: 20.080 ms
(postgres@[local]:5439) [postgres] > create table databases_nosql ( nosql boolean ) inherits (databases);
CREATE TABLE
Time: 22.048 ms

What we’ve done here is: We created three tables in total. The “databases_rdbms” and “databases_nosql” tables inherit from the “databases” table. What does that mean? Lets insert some data into the tables that inherit from the “databases” table:

(postgres@[local]:5439) [postgres] > insert into databases_rdbms values ('PostgreSQL','Community',true);
INSERT 0 1
Time: 20.215 ms
(postgres@[local]:5439) [postgres] > insert into databases_rdbms values ('MariaDB','MariaDB',true);
INSERT 0 1
Time: 1.666 ms
(postgres@[local]:5439) [postgres] > insert into databases_nosql values ('MongoDB','MongoDB',true);
INSERT 0 1
Time: 1.619 ms
(postgres@[local]:5439) [postgres] > insert into databases_nosql values ('Cassandra','Apache',true);
INSERT 0 1
Time: 0.833 ms

Note that we did not insert any data into the “databases” table, but when we query the “databases” table we get this result:

(postgres@[local]:5439) [postgres] > select * from databases;
    name    |  vendor   
------------+-----------
 PostgreSQL | Community
 MariaDB    | MariaDB
 MongoDB    | MongoDB
 Cassandra  | Apache
(4 rows)

All the data from all child tables has been retrieved (of course without the additional column on the child tables). We can still query the child tables:

(postgres@[local]:5439) [postgres] > select * from databases_rdbms;
    name    |  vendor   | rdbms 
------------+-----------+-------
 PostgreSQL | Community | t
 MariaDB    | MariaDB   | t
(2 rows)

Time: 0.224 ms
(postgres@[local]:5439) [postgres] > select * from databases_nosql;
   name    | vendor  | nosql 
-----------+---------+-------
 MongoDB   | MongoDB | t
 Cassandra | Apache  | t
(2 rows)

But when we query “only” on the master table there is no result:

(postgres@[local]:5439) [postgres] > select * from only databases;
 name | vendor 
------+--------
(0 rows)

Of course for this specific example it would be better to add an additional column to the master table which specifies if a database is a NoSQL database or not. This is just to show how it works. There is a good example for another use case in the documentation.

What does all this have to do with partitioning? When you want to partition your tables in PostgreSQL you’ll do exactly the same thing:

(postgres@[local]:5439) [postgres] > create table log_data ( id int, some_data varchar(10), ts date );
CREATE TABLE
(postgres@[local]:5439) [postgres] > create table log_data_2016() inherits ( log_data );
CREATE TABLE
(postgres@[local]:5439) [postgres] > create table log_data_2015() inherits ( log_data );
CREATE TABLE

We want to partition our log data by year, so we create a child table for each year we know we have data for. We additionally need is a check constraint on each of the child tables:

(postgres@[local]:5439) [postgres] > \d+ log_data_2016
                             Table "public.log_data_2016"
  Column   |         Type          | Modifiers | Storage  | Stats target | Description 
-----------+-----------------------+-----------+----------+--------------+-------------
 id        | integer               |           | plain    |              | 
 some_data | character varying(10) |           | extended |              | 
 ts        | date                  |           | plain    |              | 
Check constraints:
    "cs1" CHECK (ts >= '2016-01-01'::date AND ts  \d+ log_data_2015
                             Table "public.log_data_2015"
  Column   |         Type          | Modifiers | Storage  | Stats target | Description 
-----------+-----------------------+-----------+----------+--------------+-------------
 id        | integer               |           | plain    |              | 
 some_data | character varying(10) |           | extended |              | 
 ts        | date                  |           | plain    |              | 
Check constraints:
    "cs1" CHECK (ts >= '2015-01-01'::date AND ts < '2016-01-01'::date)
Inherits: log_data

This guarantees that the child tables only get data for a specific year. So far so good. But how does PostgreSQL know that inserts into the master table should get routed to the corresponding child table? This is done by using triggers:

(postgres@[local]:5439) [postgres] > CREATE OR REPLACE FUNCTION log_data_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF ( NEW.ts >= DATE '2015.01.01' AND
NEW.ts < DATE '2016-01-01' ) THEN INSERT INTO log_data_2015 VALUES (NEW.*); ELSIF ( NEW.ts >= DATE '2016-01-01' AND
NEW.ts < DATE '2017-01-01' ) THEN
INSERT INTO log_data_2016 VALUES (NEW.*);
ELSE
RAISE EXCEPTION 'Date out of range!';
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER insert_log_data_trigger
BEFORE INSERT ON log_data
FOR EACH ROW EXECUTE PROCEDURE log_data_insert_trigger();

When there are inserts against the master table, from now on these go to the corresponding child table:

(postgres@[local]:5439) [postgres] > insert into log_data values ( 1, 'aaaa', date('2016.03.03'));
INSERT 0 0
(postgres@[local]:5439) [postgres] > insert into log_data values ( 2, 'aaaa', date('2015.03.03'));
INSERT 0 0
(postgres@[local]:5439) [postgres] > select * from log_data;
 id | some_data |     ts     
----+-----------+------------
  1 | aaaa      | 2016-03-03
  2 | aaaa      | 2015-03-03
(2 rows)
(postgres@[local]:5439) [postgres] > select * from log_data_2015;
 id | some_data |     ts     
----+-----------+------------
  2 | aaaa      | 2015-03-03
(1 row)

(postgres@[local]:5439) [postgres] > select * from log_data_2016;
 id | some_data |     ts     
----+-----------+------------
  1 | aaaa      | 2016-03-03
(1 row)

Selects against the master table where we use the ts column in the where condition now only select from the child table:

(postgres@[local]:5439) [postgres] > explain analyze select * from log_data where ts = date ('2016.03.03');
                                                  QUERY PLAN                                                   
---------------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..23.75 rows=7 width=46) (actual time=0.006..0.006 rows=1 loops=1)
   ->  Seq Scan on log_data  (cost=0.00..0.00 rows=1 width=46) (actual time=0.002..0.002 rows=0 loops=1)
         Filter: (ts = '2016-03-03'::date)
   ->  Seq Scan on log_data_2016  (cost=0.00..23.75 rows=6 width=46) (actual time=0.004..0.004 rows=1 loops=1)
         Filter: (ts = '2016-03-03'::date)
 Planning time: 0.131 ms
 Execution time: 0.019 ms
(7 rows)
(postgres@[local]:5439) [postgres] > explain analyze select * from log_data where ts = date ('2015.03.03');
                                                  QUERY PLAN                                                   
---------------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..23.75 rows=7 width=46) (actual time=0.007..0.007 rows=1 loops=1)
   ->  Seq Scan on log_data  (cost=0.00..0.00 rows=1 width=46) (actual time=0.002..0.002 rows=0 loops=1)
         Filter: (ts = '2015-03-03'::date)
   ->  Seq Scan on log_data_2015  (cost=0.00..23.75 rows=6 width=46) (actual time=0.004..0.004 rows=1 loops=1)
         Filter: (ts = '2015-03-03'::date)
 Planning time: 0.102 ms
 Execution time: 0.019 ms
(7 rows)

Of course you can create indexes on the child tables as well. This is how partitioning basically works in PostgreSQL. To be honest, this is not the most beautiful way to do partitioning and this can become tricky to manage. But as always there are projects that assist you, e.g. pg_partman or pg_pathman.

Wouldn’t it be nice to have a SQL syntax to do table partitioning? Exactly this was committed yesterday and will probably be there in PostgreSQL 10 next year. The development documentation already describes the syntax:

[ PARTITION BY { RANGE | LIST } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]
 

Cet article Can I do it with PostgreSQL? – 7 – Partitioning est apparu en premier sur Blog dbi services.

OEL 7 – Project Quotas on Oracle Homes with XFS on Oracle Linux 7

Thu, 2016-12-08 02:43

User and group quotas do exist for quite a while for all kind of file systems, like ext4 or vxfs and maybe many others.

However, for my use case, I do need quotas for different directories on the same file system. My mount point is /u01 and in that file system, I do have different Oracle Homes which belong to the same user, oracle. However, I do not want that my 11.2.0.4 Oracle Home influences my 12.1.0.2 Oracle Home in respect of file system usage. e.g. if the 11.2.0.4 home is core dumping, it should not fill up the space of the 12.1.0.2 home, and the other way around. But how can I do that?

The idea is to create XFS project quotas, and this is how it works.

First of all, we need to enable project quotas on the XFS file system.  /u01 is currently mounted with XFS default options, which are (rw,relatime,attr2,inode64,noquota). As you can see, the default is “noquota”.

[root@dbidg01 ~]# mount | grep u01
/dev/mapper/vg_ora_u01-lv_ora_u01 on /u01 type xfs (rw,relatime,attr2,inode64,noquota)

We can enable project quotas by adding the “prjquota” mount option to the /etc/fstab and afterwards remounting the file system.

[root@dbidg01 ~]# mount | grep u01
/dev/mapper/vg_ora_u01-lv_ora_u01 on /u01 type xfs (rw,relatime,attr2,inode64,noquota)

Unfortunately, the remount option does not work with the XFS filesystem. Meaning, I can remount the file system, but my new option “prjquota” is not reflected.

[root@dbidg01 ~]# mount -o remount,rw,relatime,attr2,inode64,prjquota /u01
[root@dbidg01 ~]# mount | grep u01
/dev/mapper/vg_ora_u01-lv_ora_u01 on /u01 type xfs (rw,relatime,attr2,inode64,noquota)

So I have to do a umount, mount. Not a good thing from my point of view, because it means that I cannot enable project quotas online for my /u01 directory, where I have different Oracle homes located. In other words, I need to shutdown all Oracle databases.

[root@dbidg01 ~]# umount /u01
[root@dbidg01 ~]# mount /u01
[root@dbidg01 ~]# mount | grep u01
/dev/mapper/vg_ora_u01-lv_ora_u01 on /u01 type xfs (rw,relatime,attr2,inode64,prjquota)

Ok. Now it looks better. The next step is to define unique project id’s for the different directory hierarchies in the /etc/projects file.

For example, to set a project ID of 11 for the directory hierarchy /u01/app/oracle/product/11.2.0, and the project ID of 12 for the directory hierarchy /u01/app/oracle/product/12.1.0 we can do the following.

# echo "11:/u01/app/oracle/product/11.2.0" >> /etc/projects
# echo "12:/u01/app/oracle/product/12.1.0" >> /etc/projects

[root@dbidg01 ~]# cat /etc/projects
11:/u01/app/oracle/product/11.2.0
12:/u01/app/oracle/product/12.1.0

If you don’t want to work with ID’s, you have the possibility to map project names to the project ID’s in your /etc/projid file. It is much easier in regards of reporting quota usage, which we will see later.

For example, to map the project name oracle11gR2 to the project with ID 11 or to map the project name oracle12cR1 to 12 do the following.

# echo "oracle11gR2:11" >> /etc/projid
# echo "oracle12cR1:12" >> /etc/projid

Now use the project subcommand of xfs_quota to define a managed tree in the XFS file system for the different projects.

For example, to define a managed tree in the /u01 file system for the project oracle11gR2, which corresponds to the directory hierarchy /u01/app/oracle/product/11.2.0, do the following.

# xfs_quota -x -c 'project -s oracle11gR2' /u01

[root@dbidg01 etc]# xfs_quota -x -c 'project -s oracle11gR2' /u01
Setting up project oracle11gR2 (path /u01/app/oracle/product/11.2.0)...
xfs_quota: skipping special file /u01/app/oracle/product/11.2.0/dbhome_1/bin/lbuilder
xfs_quota: skipping special file /u01/app/oracle/product/11.2.0/dbhome_1/jdk/jre/lib/amd64/server/libjsig.so
xfs_quota: skipping special file /u01/app/oracle/product/11.2.0/dbhome_1/lib/libagtsh.so
xfs_quota: skipping special file /u01/app/oracle/product/11.2.0/dbhome_1/lib/libodm11.so
xfs_quota: skipping special file /u01/app/oracle/product/11.2.0/dbhome_1/lib/libclntsh.so.10.1
xfs_quota: skipping special file /u01/app/oracle/product/11.2.0/dbhome_1/lib/libocci.so
xfs_quota: skipping special file /u01/app/oracle/product/11.2.0/dbhome_1/lib/libclntsh.so
xfs_quota: skipping special file /u01/app/oracle/product/11.2.0/dbhome_1/lib/liborasdk.so
xfs_quota: skipping special file /u01/app/oracle/product/11.2.0/dbhome_1/lib/liborasdkbase.so
xfs_quota: skipping special file /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
xfs_quota: skipping special file /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
xfs_quota: skipping special file /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
xfs_quota: skipping special file /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/ldap.ora
xfs_quota: skipping special file /u01/app/oracle/product/11.2.0/dbhome_1/precomp/public/SQLCA.H
xfs_quota: skipping special file /u01/app/oracle/product/11.2.0/dbhome_1/precomp/public/ORACA.H
xfs_quota: skipping special file /u01/app/oracle/product/11.2.0/dbhome_1/precomp/public/SQLDA.H
xfs_quota: skipping special file /u01/app/oracle/product/11.2.0/dbhome_1/precomp/public/SQLCA.COB
xfs_quota: skipping special file /u01/app/oracle/product/11.2.0/dbhome_1/precomp/public/ORACA.COB
Processed 1 (/etc/projects and cmdline) paths for project oracle11gR2 with recursion depth infinite (-1).

The same applies to project oracle12cR1.

# xfs_quota -x -c 'project -s oracle12cR1' /u01

[root@dbidg01 etc]# xfs_quota -x -c 'project -s oracle12cR1' /u01
Setting up project oracle12cR1 (path /u01/app/oracle/product/12.1.0)...
xfs_quota: skipping special file /u01/app/oracle/product/12.1.0/dbhome_1/bin/lbuilder
xfs_quota: skipping special file /u01/app/oracle/product/12.1.0/dbhome_1/javavm/admin/classes.bin
xfs_quota: skipping special file /u01/app/oracle/product/12.1.0/dbhome_1/javavm/admin/cbp.jar
xfs_quota: skipping special file /u01/app/oracle/product/12.1.0/dbhome_1/javavm/admin/libjtcjt.so
xfs_quota: skipping special file /u01/app/oracle/product/12.1.0/dbhome_1/javavm/lib/security/US_export_policy.jar
xfs_quota: skipping special file /u01/app/oracle/product/12.1.0/dbhome_1/javavm/lib/security/cacerts
xfs_quota: skipping special file /u01/app/oracle/product/12.1.0/dbhome_1/javavm/lib/security/java.security
xfs_quota: skipping special file /u01/app/oracle/product/12.1.0/dbhome_1/javavm/lib/security/local_policy.jar
xfs_quota: skipping special file /u01/app/oracle/product/12.1.0/dbhome_1/javavm/lib/jce.jar
xfs_quota: skipping special file /u01/app/oracle/product/12.1.0/dbhome_1/javavm/lib/sunjce_provider.jar
xfs_quota: skipping special file /u01/app/oracle/product/12.1.0/dbhome_1/jdk/bin/ControlPanel
xfs_quota: skipping special file /u01/app/oracle/product/12.1.0/dbhome_1/jdk/jre/bin/ControlPanel
xfs_quota: skipping special file /u01/app/oracle/product/12.1.0/dbhome_1/jdk/jre/javaws/javaws
xfs_quota: skipping special file /u01/app/oracle/product/12.1.0/dbhome_1/jdk/jre/lib/amd64/server/libjsig.so
xfs_quota: skipping special file /u01/app/oracle/product/12.1.0/dbhome_1/lib/libagtsh.so
xfs_quota: skipping special file /u01/app/oracle/product/12.1.0/dbhome_1/lib/libjavavm12.a
xfs_quota: skipping special file /u01/app/oracle/product/12.1.0/dbhome_1/lib/libodm12.so
xfs_quota: skipping special file /u01/app/oracle/product/12.1.0/dbhome_1/lib/libocci.so
xfs_quota: skipping special file /u01/app/oracle/product/12.1.0/dbhome_1/lib/libclntshcore.so
xfs_quota: skipping special file /u01/app/oracle/product/12.1.0/dbhome_1/lib/libclntsh.so
xfs_quota: skipping special file /u01/app/oracle/product/12.1.0/dbhome_1/lib/libclntsh.so.10.1
xfs_quota: skipping special file /u01/app/oracle/product/12.1.0/dbhome_1/lib/libclntsh.so.11.1
xfs_quota: skipping special file /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
xfs_quota: skipping special file /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora
xfs_quota: skipping special file /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/ldap.ora
xfs_quota: skipping special file /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora
xfs_quota: skipping special file /u01/app/oracle/product/12.1.0/dbhome_1/precomp/public/SQLCA.H
xfs_quota: skipping special file /u01/app/oracle/product/12.1.0/dbhome_1/precomp/public/ORACA.H
xfs_quota: skipping special file /u01/app/oracle/product/12.1.0/dbhome_1/precomp/public/SQLDA.H
xfs_quota: skipping special file /u01/app/oracle/product/12.1.0/dbhome_1/precomp/public/SQLCA.COB
xfs_quota: skipping special file /u01/app/oracle/product/12.1.0/dbhome_1/precomp/public/ORACA.COB
Processed 1 (/etc/projects and cmdline) paths for project oracle12cR1 with recursion depth infinite (-1).

Now the fun part begins, and we can start using the limit subcommand to set the limits on the disk usage for the different projects. My 11.2.0.4 Oracle home is currently 5.7G in size, and the 12.1.0.2 Oracle home is 7.1G big. I want to configure for the 11g home a soft limit of 8G and a hard limit of 12G, and for the 12c home a soft limit of 10G and a hard limit of 16G.

oracle@dbidg01:/u01/app/oracle/product/ [rdbms112] du -hs 11.2.0
5.7G    11.2.0

oracle@dbidg01:/u01/app/oracle/product/ [rdbms112] du -hs 12.1.0
7.1G    12.1.0

# xfs_quota -x -c 'limit -p bsoft=8g bhard=12g oracle11gR2' /u01
# xfs_quota -x -c 'limit -p bsoft=10g bhard=16g oracle12cR1' /u01

[root@dbidg01 ~]# xfs_quota -x -c 'report -p' /u01
Project quota on /u01 (/dev/mapper/vg_ora_u01-lv_ora_u01)
                               Blocks
Project ID       Used       Soft       Hard    Warn/Grace
---------- --------------------------------------------------
#0            8776636          0          0     00 [--------]
oracle11gR2    5883604    8388608   12582912     00 [--------]
oracle12cR1    7415292   10485760   16777216     00 [--------]

[root@dbidg01 ~]# xfs_quota -x -c 'report -h -p' /u01
Project quota on /u01 (/dev/mapper/vg_ora_u01-lv_ora_u01)
                        Blocks
Project ID   Used   Soft   Hard Warn/Grace
---------- ---------------------------------
#0           8.4G      0      0  00 [------]
oracle11gR2   5.6G     8G    12G  00 [------]
oracle12cR1   7.1G    10G    16G  00 [------]

The quotas are immediately seen by the df command. The df on the /u01 shows the full filesystem size, which is 50G, however, if I navigate to my 11g or 12c home, I see the soft limit which I have configured beforehand.

[root@dbidg01 product]# df -h /u01
Filesystem                         Size  Used Avail Use% Mounted on
/dev/mapper/vg_ora_u01-lv_ora_u01   50G   22G   29G  43% /u01

[root@dbidg01 ~]# cd /u01/app/oracle/product/11.2.0
[root@dbidg01 11.2.0]# df -h .
Filesystem                         Size  Used Avail Use% Mounted on
/dev/mapper/vg_ora_u01-lv_ora_u01  8.0G  5.7G  2.4G  71% /u01

[root@dbidg01 ~]# cd /u01/app/oracle/product/12.1.0
[root@dbidg01 12.1.0]# df -h .
Filesystem                         Size  Used Avail Use% Mounted on
/dev/mapper/vg_ora_u01-lv_ora_u01   10G  7.1G  3.0G  71% /u01
Conclusion

The project quotas with XFS are a quite cool feature. Limiting file system usage per projects gives you a huge flexibility. Setting quotas on individual Oracle homes is one thing, but you could also limit filesystem usage for your Oracle databases in the DIAG directory based on ORACLE_SID, so that /u01/app/oracle/diag/rdbms/<SID1> can never fill up /u01/app/oracle/diag/rdbms/<SID2>. The only drawback I see, is that enabling project quotas is not an online operation. You need to umount and mount the file system to activate this feature.

Have fun with XFS quotas.

Cheers,
William

 

Cet article OEL 7 – Project Quotas on Oracle Homes with XFS on Oracle Linux 7 est apparu en premier sur Blog dbi services.

UKOUG 2016 DAY 4

Wed, 2016-12-07 09:29

uk4

Today is the last day at UKOUG in Birmingham; the first session I attended this morning was presented by Julian Dyke about installing and upgrading Oracle 12c release 2 Grid infrastructure and RAC.

He had the possibility to test the installation and upgrade phases at Oracle during 5 days at Oracle last spring. The following tests were done:

single instance : install 12.2.0.1, create database with dbca, upgrade 12.1.0.2 to 12..2.0.1 with dbua

RAC: install 12.2.0.2 grid infrastructure, install 12.2.0.1 RDBMS software, create ASM disk groups (ASMCA), create 12.2.0.2 RAC database (DBCA) , upgrade 12.1.0.2 Grid infrastructure to 12.2.0.1 (gridSetup.sh), upgrade 12.1.0.2 RAC database to 12.2.0.1.

He showed us the main different screenshots describing the installation phases and told us that they did not meet a lot of problems during their installation or upgrade  phases. To upgrade the Grid infrastructure, it is important to run the CVU connected as grid user for example :

runcluvfy.sh -src_crshome=/u00/app/12.1.0.2 -dest_crshome=/u00/app/12.2.0.1
 -dest_version=12.2.0.1 -fixupnoexec

Then after you have the possibility to resolve any issues detected using the generated fixup script.

In his opinion, the use of DBUA is sufficiently robust to use for most upgrades, expecially when the upgrade concerns non critical databases, or databases with fast recovery times or databases on virtual machines. By the way he also mentioned that Oracle is still recommending using scripts for upgrades of large or business critical databases.

He encountered some isssues concerning the upgrade phase for Grid Infrastructure. In particular with the memory_target parameter setting because the ASM and GIMR instances use more memory than in 12.1.0.2, he received the classical ORA-00845 error message. He also encountered problems with invalid objects  and had to extend the root file system of his virtual machine.

Then I attended to Franck Pachot’s session about Statistics Gathering, Best Practices  and Statistic Advisor:

uk5

His session described us his findings and recommendations about how to gather statistics, with a lot of technical demonstrations done on the Cloud. A lot of cases were shown, for example volatile tables, preferences for partitioned tables. index gathering statistics.

He showed us the Oracle 12c release 2 statistics Advisor which might be a useful tool, I will check if it is available in Enterprise Manager 13.2.

He finished his by giving us hsi own recommendations: use automatic job for most of the tables, customize the statistics gathering for volatile tables, gather statistics for tables that you load, and important customize the maintenance window for the gathering statistics job.

Finally I wanted to attend at the OEM round table, but unfortunately the session has been canceled :=((

UK6

Well,this was a very interesting week with a lot of exchanges and sharing experiences with other Oracle DBA. hope to come back at UKOUG next year !

 

 

 

Cet article UKOUG 2016 DAY 4 est apparu en premier sur Blog dbi services.

Oracle 12cR2: Statistics Advisor

Wed, 2016-12-07 04:48

Today at #ukoug_tech16 12:30 in hall 11A I’ll talk about Statistics Gathering Best Practice & 12cR2 Statistics Advisor
Rather than taking the Optimizer Statistics Advisor rules one by one I’ll show the things to take care (some people may call it best practices) when gathering statistics and they mention the Statistics Advisor Rule.
If you need a reference about all rules, you can get it from V$STATS_ADVISOR_RULES

09:41:19 SQL> select to_char(rule_id,99)||' '||description||' ('||name||')' from V$STATS_ADVISOR_RULES where rule_id>0 order by rule_id;
&nbsp
TO_CHAR(RULE_ID,99)||''||DESCRIPTION||'('||NAME||')'
----------------------------------------------------------------------------------------------------

I’ll explain them briefly here.

You should always enable to automatic statistic gathering job. You may manage special cases manually, but do not disable it.
1 Use Auto Job for Statistics Collection (UseAutoJob)
For sure if the job does not complete successfully, the advisor detects the probable cause.
2 Auto Statistics Gather Job should complete successfully (CompleteAutoJob)
Statistics history may save your life in case of a regression. But be sure the retention is not too large and purge occurs or SYSAUX will grow
3 Maintain Statistics History (MaintainStatsHistory)
The faster the statistics gathering run, the more statistics you can gather. Use all your server resources for it.
4 Use Concurrent preference for Statistics Collection (UseConcurrent)
Default options for global preferences are what the optimizer developers think are the best for most cases.
5 Use Default Preference for Stats Collection (UseDefaultPreference)
Humm.. this one was introduced before the decision not to activate SPD by default
(see http://blog.dbi-services.com/oracle-12cr2-optimizer-adaptive-statistics/)
6 SQL Plan Directives should not be disabled (TurnOnSQLPlanDirective)
Setting statistics manually may be used as a workaround but not the general case
7 Avoid Set Statistics Procedures (AvoidSetProcedures)
When you run dbms_stats.gather_…_stats manually, default options are what the optimizer developers think are the best for most cases.
8 Use Default Parameters in Statistics Collection Procedures (UseDefaultParams)
And in those cases, better to run it for a schema so that you are sure to include newly created tables
9 Use gather_schema_stats procedure (UseGatherSchemaStats)
You waste time and ressources if you gather statistics in a addition to what is done with online statistics gathering
10 Avoid inefficient statistics operation sequences (AvoidInefficientStatsOprSeq)
You waste time and ressources if you gather statistics when nothing has changed
11 Avoid unnecessary statistics collection (AvoidUnnecessaryStatsCollection)
You need statistics for all tables
12 Avoid objects with stale or no statistics (AvoidStaleStats)
Statistics gathered before bulk inserts will be immediately stale
13 Do not gather statistics right before bulk DML (GatherStatsAfterBulkDML)
You don’t want the automatic statistics gathering run on a table between a truncate and an insert
14 Statistics for objects with volatile data should be locked (LockVolatileTable)
But let it run for tables with no massive change
15 Statistics for objects with non-volatile should not be locked (UnlockNonVolatileTable)
16 Statistics of dependent objects should be consistent (MaintainStatsConsistency)

Better truncate, make indexes unusable, and insert /*+ append */ than drop and recreate the table (which removes statistics).
17 Avoid drop and recreate object seqauences (AvoidDropRecreate)
Statistics advisor may detect when incremental statistics gathering is me efficient for partitioned tables
18 Statistics should be maintained incrementally when it is beneficial (UseIncremental)
19 Statistics should not be maintained incrementally when it is not beneficial (NotUseIncremental)

Stale statistics may lead to under-estimation because of linear decay
20 Avoid Out of Range Histogram endpoints (AvoidOutOfRange)
Large tables can be scanned in parallel, recommendation is default degree
21 Use Auto Degree for statistics collection (UseAutoDegree)
As we have seen about global preferences, table preference should be default for most cases (rolling invalidation, auto sample size, auto histogram size)
22 Use Default Object Preference for statistics collection (UseDefaultObjectPreference)
And for sure dbms_stats is the way to gather statistics for the optimizer. ANALYZE is deprecated for that since 91
23 Avoid using analyze table commands for statistics collection (AvoidAnalyzeTable)

Those are only my interpretation. 12.2 is new (and cloud first) and I’ve not observed all those recommandations yet. But there are properly described by the advisor.
This is the kind of output we can get:

----------------------------------------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------------
 
Task Name : MY_TASK
Execution Name : EXEC_52
Created : 12-07-16 11:31:40
Last Modified : 12-07-16 11:32:37
-------------------------------------------------------------------------------
SUMMARY
-------------------------------------------------------------------------------
For execution EXEC_52 of task MY_TASK, the Statistics Advisor has 6
finding(s). The findings are related to the following rules: USECONCURRENT,
AVOIDSETPROCEDURES, USEDEFAULTPARAMS, USEGATHERSCHEMASTATS, AVOIDSTALESTATS,
UNLOCKNONVOLATILETABLE. Please refer to the finding section for detailed
information.
-------------------------------------------------------------------------------
FINDINGS
-------------------------------------------------------------------------------
Rule Name: UseConcurrent
Rule Description: Use Concurrent preference for Statistics Collection
Finding: The CONCURRENT preference is not used.
 
Recommendation: Set the CONCURRENT preference.
Example:
dbms_stats.set_global_prefs('CONCURRENT', 'ALL');
Rationale: The system's condition satisfies the use of concurrent statistics
gathering. Using CONCURRENT increases the efficiency of statistics
gathering.
----------------------------------------------------
...

 

Cet article Oracle 12cR2: Statistics Advisor est apparu en premier sur Blog dbi services.

OEL 7 – How to disable IPv6 on Oracle Linux 7

Wed, 2016-12-07 02:36

In case you are not interested in IPv6, you can use the following HowTo to disable it on Oracle Linux 7. Unless you have something very very special on your System, these 10 Steps should do it.

  1. First of all, check if IPv6 is active at all
  2. Add the disable_ipv6 = 1 entries to the /etc/sysctl.conf file
  3. Disable IPv6 in all /etc/sysconfig/network-scripts/ifcfg-* files, e.g.
  4. Disable IPv6 in /etc/sysconfig/network
  5. Remove the “::1″ line from the /etc/hosts file
  6. Remove the “restrict -6″ line from the /etc/ntp.conf
  7. Add ipv6.disable=1 to the GRUB_CMDLINE_LINUX entry in the /etc/default/grub file
  8. Regenerate a GRUB configuration file and overwrite the existing one
  9. Reboot the server
  10. Confirm if IPV6 is disabled

 

First of all, check if IPv6 is active at all

[root@dbidg01 ~]# /sbin/ip -6 addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: enp0s3: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qlen 1000
    inet6 fe80::ad02:9b6a:bf40:5a3a/64 scope link
       valid_lft forever preferred_lft forever
3: enp0s8: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qlen 1000
    inet6 fe80::a00:27ff:feb8:3544/64 scope link
       valid_lft forever preferred_lft forever

 

Add the disable_ipv6 = 1 entries to the /etc/sysctl.conf file

#-- Disable IPv6
net.ipv6.conf.all.disable_ipv6 = 1
net.ipv6.conf.default.disable_ipv6 = 1

 

Disable IPv6 in all /etc/sysconfig/network-scripts/ifcfg-* files, e.g.

cat /etc/sysconfig/network-scripts/ifcfg-enp0s3 | grep IPV6INIT
IPV6INIT=no

 

Disable IPv6 in /etc/sysconfig/network

cat /etc/sysconfig/network | grep NETWORKING_IPV6
NETWORKING_IPV6=no

 

Remove the following line from the /etc/hosts file

::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

 

Remove the following line from the /etc/ntp.conf

cat /etc/ntp.conf | egrep ' -6'
restrict -6 default kod nomodify notrap nopeer noquery
restrict -6 ::1

 

Add ipv6.disable=1 to the GRUB_CMDLINE_LINUX entry in the /etc/default/grub file

[root@dbidg01 /]# cat /etc/default/grub | grep GRUB_CMDLINE_LINUX
GRUB_CMDLINE_LINUX="ipv6.disable=1 crashkernel=auto rd.lvm.lv=ol/root rd.lvm.lv=ol/swap rhgb quiet numa=off transparent_hugepage=never"

 

Regenerate a GRUB configuration file and overwrite the existing one

[root@dbidg01 /]# grub2-mkconfig -o /boot/grub2/grub.cfg
Generating grub configuration file ...
Found linux image: /boot/vmlinuz-4.1.12-61.1.19.el7uek.x86_64
Found initrd image: /boot/initramfs-4.1.12-61.1.19.el7uek.x86_64.img
Found linux image: /boot/vmlinuz-4.1.12-61.1.18.el7uek.x86_64
Found initrd image: /boot/initramfs-4.1.12-61.1.18.el7uek.x86_64.img
Found linux image: /boot/vmlinuz-3.10.0-514.el7.x86_64
Found initrd image: /boot/initramfs-3.10.0-514.el7.x86_64.img
Found linux image: /boot/vmlinuz-0-rescue-547c48bd53614a2ca2d16909b3c14419
Found initrd image: /boot/initramfs-0-rescue-547c48bd53614a2ca2d16909b3c14419.img
done

 

Reboot the server

init 6

 

Confirm if IPV6 is disabled

[root@dbidg01 ~]# /sbin/ip -6 addr
[root@dbidg01 ~]# lsmod | grep -i v6

 

In case the ip and the lsmod command do not return anything back, then you have successfully disabled IPv6.

Cheers, William

 

 

 

 

Cet article OEL 7 – How to disable IPv6 on Oracle Linux 7 est apparu en premier sur Blog dbi services.

UKOUG 2016 – Third day

Tue, 2016-12-06 11:41

Birmingham

Today, it’s the third day in Birmingham for the UKOUG Tech16 event. We had a good time yesterday evening in English pubs.

Today, I attended several sessions today. Sessions that I was mostly interested in was the “Application Express 5.1 New Features part 1 an part 2.

The 1st session was presented by David Peake from Oracle. He provided main new features that will come with Apex 5.1. He demonstrates new capability for developer usage improvement in terms of productivity. In Apex 5.1, we will be able to define the page designer as we want, customising tabs order, displaying the page designer in different pane layout and page rendering. He also presented the Interactive Grid and quickly created a master detail, detail, detail, detail view. The number of detail is unlimited but he strongly advises to carefully minimise the number of detail.

The 2nd session, APEX 5.1 part 2 was presented by Patrick Wolf. He concentrated his session by presenting improvement made on the Universal Theme that was already rolled out with APEX 5.0. So they continued to improve it in APEX 5.1. In my opinion, the important information is the upgrade of the Universal Theme for any existing 5.0 application. You will have to refresh the theme in order to use the improvement made in APEX 5.1. This has to be done by going in the shared component theme visualisation and click on the refresh theme. This will upgrade the already used UT with 5.1 capabilities. There is lot’s of new capabilities and would wait until the final rollout date to do some tests on my side.

Another session I followed was “APEX, Meet the Rest of the Web – Modern Web Technologies in Your APEX Application”. It was good to know how to create a search like google with auto-complete support depending on the value entered in the search field. Presenter also showed us how to quickly integrate google charts using APEX JSON packages. He also showed us how to integrate google map, showed us how to call Facebook and Twitter API in our application in order to follow twits a.s.o. It’s quite easy to integrate Modern Web Technologies in any APEX Application.

See you tomorrow for the last day in Birmingham.

 

Cet article UKOUG 2016 – Third day est apparu en premier sur Blog dbi services.

UKOUG 2016 DAY 3

Tue, 2016-12-06 11:25

uk3

Today at UKOUG 2016, the Cloud has won against the sun :=)

The first sesssion I attended this morning was animated by Kamil Stawiarski from ORA 600 company: Securing the database againt Unauthorized attacks, but the real title was Oracle Hacking Session.

The session was amazing, as usual with Kamil, no slides , only technical demos :=))

He first showed us that after creating a standard user in an Oracle database with the classical privileges connect, resource and create any index, and using a simple function he created, the standard user could receive the DBA privilege.

The seconf demonstration was about DirtyCow (a computer vulnerability under Linux that allows remote execution of non-privileged code to achieve remote root access on a computer). He showed us how easy it is to get connected root under Linux.

In the last demo he showed us how it is possible to read the data from a particular table directly from the data file, only by using one of his C program and the data_object_id of the table.

He finished his session by asking himself why a lot of money is wasted to protect data, and why it should not be more intelligent to spend less money and to write correct applications with correct privileges.

The second session was more corporate: Oracle database 12cR2, the overview by Dominic Giles from Oracle. He talked us about Oracle 12cR2 on the cloud; What is available now: Exadata Express Cloud Server and Database Cloud Service. Comming soon: Exadata Cloud Machine.

Then he talked about the new features of Oracle database 12cR2:

Performances: The main idea for 12cR2 is: go faster, he gave us some examples: a high compression rate of indexes (subject to licensing option of course) which might result in I/O improvement and significantly space savings.

Security: Oracle 12cR2 introduces online encryption of existing data files. There is also the posiibility of full encryption of internal database structures such as SYSTEM SYSAUX or UNDO. Also a Database Vault simulation mode which defines and tests security protection profiles through application lifecycle.

Developpers: AL32UTF8 is the default character set for databases. Object name for tables or columns can now be 128 bits long.

Manageability: PDB number per container increased from 252 to 4096. The PDB are optimized for RAC. And interesting it will be possible to realize PDB hot clones, PDB refresh and PDB relocate without downtime.

Availability: a lot of improvements for RAC: RAC reader nodes, ASM flex disk groups, Autonomous Health Framework (identifies issues, notifies with corrective actions). For active dataguard, diagnostic tuning and SQL plan advisor will be available on standby side, no user disconnection on failover, high speed block comparaison between primary and standby database. And finally there will be the possibility to use SSL redo transport to be more secure.

Finally, I attended at the last session of the day, but one the most active essentially because of the speaker’s talent and of course the subject: Upgrade to the next generation of Oracle Database; live and uncensored !

He talked us about the different ways to upgrade to 12.1.0.2 or 12.2.0.2 abording subjects like extended support, direct upgrade and DBUA.

A new upgrade script is available : preupgrade.jar executes checks in source environment, generates detailed recommendations, generates also fixup scripts and last but not least is rerunnable :=))

He showed us that the upgrade process is faster and has less downtime, and we have the possibility to run databse upgrade in parallel (by using catctlpl.pl with the -n 8 option for example). It deals with non CDBs and CDBs. During his upgrade from 11.2.0.4 to 12.1.0.2 he interrupted the upgrade process by typing CTRL-C during the upgrade process to 12.1.0.2 … and he proved that the process upgrade is rerunnable by running catctl.pl with the -R option :=)

He is not a great fan of DBUA for multiple reasons : for him it is hard to debug, the parallel option is by default to cpu_count, the progress bar is impredictive and sometimes we have to wait a lot without knowing what’s happening in the source database, we have to be careful with datapatch in 12.1 version. For me the only advantage is the timezone  automatic upgrade by using dbua.

Well this was another exciting day at UKOUG 2016, tomorrow is the last day with other interesting sessions and an OEM round table :=)

 

Cet article UKOUG 2016 DAY 3 est apparu en premier sur Blog dbi services.

From MySQL (Oracle) to Postgres using the EDB Migration Toolkit

Tue, 2016-12-06 11:24

Why should you migrate?
If your current MySQL database does not offer some needed functionnalities according to your business as:
– more security
– more high availibilty options (hot standby)
– Strong Data Warehouse capabilities
If you want to consolidate the number of different instances (Postgres, MySQL, MS-SQL,…)
If you want to reduce administrative costs by using fewer database platforms
Which tool should you use?
the migration Toolkit command-line from EnterpriseDB that can be found below
http://www.enterprisedb.com/products-services-training/products-overview/postgres-plus-solution-pack/migration-toolkit
Why ?
Really easy to use
Which MySQL Objects are supported for the migration?
– Schemas
– Tables
– Constraints
– Indexes
– Table Data
What about partitionned table?
You have to remove the partitions before the migration
mysql> ALTER TABLE Table_name REMOVE PARTITIONING;
My environment:
MySQL: 5.7.14 on Oracle Linux Server 7.1
PostgreSQL: 9.6.1.4 on Oracle Linux Server 7.1
What are the prerequisites?
– download the migration toolkit from EnterpriseDB
Note that it can be only installed by registered users but the registration is free and can be done directly on the EnterpriseDB website.
– Install it and follow the instructions
./edb-migrationtoolkit-50.0.1-2-linux-x64.run
– download the MySQL JDBC driver: mysql-connector-java-5.1.40-bin.jar
http://www.enterprisedb.com/downloads/third-party-jdbc-drivers
– Install the driver by moving it to the right directory:
/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.111-2.b15.el7_3.x86_64/jre/lib/ext
– To facilitate  the migration, you have to prepare the configuration file: toolkit.properties located in your installation directory
the most important is to associate the right JDBC URL to the SRC_DB_URL parameter
SRC_DB_URL=jdbc:mysql://hostname[:port]/database
Following is the content of the config file
SRC_DB_URL=jdbc:mysql://192.168.56.200:33001/employees
SRC_DB_USER=root
SRC_DB_PASSWORD=manager
TARGET_DB_URL=jdbc:edb://192.168.56.200:5433/employees # the database must be created in Postgres before
TARGET_DB_USER=postgres
TARGET_DB_PASSWORD=manager

In case you get MySQL connection problems (SSL), modify the parameter SRC_DB_URL
SRC_DB_URL=jdbc:mysql://192.168.56.200:33001/employees?autoReconnect=true&useSSL=false
This will disable SSL and also suppress SSL errors.
Before starting the Migration, it is mandatory to create a blank target database in the Postgres instance
What options for the migration ?
-sourcedbtype is mysql
-targetdbtype is enterprisedb
-fetchsize is 1  to avoid  an ‘out of heap space’ error and force the toolkit to load data one row at a time
How to start the migration?
[root@pg_essentials_p1 mtk]# bin/runMTK.sh -sourcedbtype mysql -targetdbtype enterprisedb -fetchSize 1 employees
Running EnterpriseDB Migration Toolkit (Build 50.0.1) ...
Source database connectivity info...
conn =jdbc:mysql://192.168.56.200:33001/employees?autoReconnect=true&useSSL=false
user =root
password=******
Target database connectivity info...
conn =jdbc:edb://192.168.56.200:5433/employees
user =postgres
password=******
Connecting with source MySQL database server...
Connected to MySQL, version '5.7.14-enterprise-commercial-advanced-log'
Connecting with target EDB Postgres database server...
Connected to EnterpriseDB, version '9.6.1.4'
Importing mysql schema employees...
Creating Schema...employees
Creating Tables...
Creating Table: departments
..........................
Created 6 tables.
Loading Table Data in 8 MB batches...
Loading Table: departments ...
[departments] Migrated 9 rows.
..............................
Loading Table: salaries ...
[salaries] Migrated 246480 rows.
................................
[salaries] Migrated 2844047 rows.
[salaries] Table Data Load Summary: Total Time(s): 20.143 Total Rows: 2844047 Total Size(MB): 94.1943359375
Loading Table: titles ...
[titles] Migrated 211577 rows.
[titles] Migrated 419928 rows.
[titles] Migrated 443308 rows.
[titles] Table Data Load Summary: Total Time(s): 3.898 Total Rows: 443308 Total Size(MB): 16.8955078125
Data Load Summary: Total Time (sec): 33.393 Total Rows: 3919015 Total Size(MB): 138.165
Creating Constraint: PRIMARY
Creating Constraint: dept_name
................................
Creating Index: dept_no1
Schema employees imported successfully.
Migration process completed successfully.
Migration logs have been saved to /root/.enterprisedb/migration-toolkit/logs
******************** Migration Summary ********************
Tables: 6 out of 6
Constraints: 11 out of 11
Indexes: 2 out of 2
Total objects: 19
Successful count: 19
Failed count: 0
Invalid count: 0
************************************************************

So as you can see, this migration process is really easy and you can take immediately benefits of all the standard features.

 

Cet article From MySQL (Oracle) to Postgres using the EDB Migration Toolkit est apparu en premier sur Blog dbi services.

UKOUG 2016 – Second day

Mon, 2016-12-05 13:51

IMG_1965

This second day at UKOUG was quite good. I slept well at the Jurys Inn hotel and this morning, I enjoyed one more time a real English breakfast with beans, bacons, eggs and sausages. I like that to be fit over all the day ;)

Today, I attended the general Keynote and several sessions around integration, APEX & Database Development and Database. My colleague, Franck Pachot also presented today and I attended his session “12c Multitenant: Not a Revolution, Just an Evolution”. His session reminds me the article I wrote some years ago about Oracle Multitenant architecture and APEX.

Early in the morning, I followed the “Application Container Cloud Service: Backend Integration Using Node.js”. The presenter described what Node.js is, give javascript framework that can be easily integrated with Node.js such as Express.js to create HTTP server and retrieve Node.js data by creating HTTP server. He also presented the architecture where we can have Node.js hosted in Docker on the cloud.

After that, I attended the session “APEX Version Control & Team Working”. During that session, I learned more on Apex Version Control best practices and which nice commands can be done through SQL cli, apex java utility and so on. I was quite happy learning that for internal development we were not so bad and we already properly control version, make backup of APEX workspace, applications and themes. I now have information to improve our internal works around APEX development activities such as APEX ATAF “Apex Test Automation Framework”

Next session was “Interactive Grids in Application Express 5.1″. This session was a demonstration oriented session in which the presenter showed us new amazing features that will be incorporated in APEX 5.1. Most of the demonstration was based on the sample package application.

The next session was “Real Time Vehicle Tracking with APEX5″. For me it was great to see the power of Apex and the Oracle Database to store and display data in real time through the APEX5 MapViewer. The application uses Oracle Spatial getting data from each vehicle GPS where PL/SQL converts data for geospatial information.

During the last session, “A RESTful MicroService for JSON Processing in the Database” I learned how to execute JavaScript directly from the database. In fact, with Java 8 and the Nashhorn project it’s now possible to execute JavaScript codes from the JVM and so directly in the database avoiding data shipping.

This is all for today and see you tomorrow, we will now take time with my blog reviewer to drink some pints in an English pub.

 

Cet article UKOUG 2016 – Second day est apparu en premier sur Blog dbi services.

SQL Server 2016: distributed availability groups and cascaded replicas

Mon, 2016-12-05 12:28

During the last MVP summit, we had some interesting discussions about availability groups with the SQL Server team and I remember someone asked for managing scenarios like Oracle cascaded destinations and the good news is that SQL Server 2016 already addresses this kind of scenario with distributed availability groups. For example, let’s say you have to manage heavily reporting activity on your application and a solution would be to offload this activity across several secondary read-only replicas. So, a typical architecture as follows:

blog 110 - 0 - initial infra AG

We basically want to achieve high availability on the primary datacenter (DATACENTER1) and to use the secondary datacenter as DR and at the same time to offload reporting activity on secondary replicas. But let’s say you get a low network bandwidth – (WAN classified with ~= 150 / 200 mbps) between your two datacenters which are geographically dispersed from each other. Regarding your current workload against the availability group, we may potentially experience high network traffic when the number of secondary replicas increases on the DR site. Indeed, the number of log blocks to replicate is directly proportional to the number of secondary replicas for the same payload.

I decided to simulate this scenario on my lab environment which reflects the above scenario (2 replicas on the first datacenter and four other replicas on the second datacenter). I used two Lenovo T530 laptop with Hyper-V to simulate the whole environment with a cross datacenter network connection handled by two RRAS servers.

In addition, for a sake of precision, let’s describe the test protocol:

  • I used a script which inserts a bunch of data from the primary replica (~ 900MB of data)
  • I ran the same workload test after adding one asynchronous read-only replica at time on each test up to 4 replicas.
  • I collected performance data from various perfmon counters focused on the availability group network stack (both primary site and DR site)

Here the output of the whole test.

blog 110 - 1 - network usage trend AG

The picture above is pretty clear here. We notice the network bandwidth grows up when adding secondary replicas. In the last test, the network bandwidth reached 400 Mbps (received traffic) on the remote datacenter while that reached for primary replica 600 Mbps (send traffic). Why have we got a difference between network bandwidth consumption between the primary replica and remote datacenter? Well, the answer is simple: network bandwidth consumption on remote datacenter doesn’t include network traffic from the secondary located on the first datacenter for high-availability.

We may also notice the third iteration of the test (1 primary + 1 secondary sync + 2 secondaries async) is showing up a critical point if we have to face a scenario that includes a WAN connection between the two datacenters with a maximum network bandwidth of 200 Mbps. Indeed in this case, the network bandwidth could be quickly saturated by the replication traffic between all the replicas and here probably the first symptoms you may encountered in this case:

 blog 110 - 3 - AG DMV monitoring

A continuous high log send queue size for each concerned secondary replica on the remote datacenter (250 MB on average in my case)…

blog 110 - 3 - AG wait stats

You may minimize the network overhead by isolating the replication traffic to its own network but in some cases if you’re unlucky it will not be enough. This is a situation which may be solved by introducing distributed availability groups and the cascaded destinations principle as shown below:

blog 110 - 4 - distributed infra AG

Distributed availability group feature will permit to offload the replication traffic from the primary to the read-only secondaries by using a replica on the second datacenter. Thus, we are able to reduce drastically the network bandwidth from 4 replicas to only one. In addition, adding one or several other replicas may be considered because this new architecture is more scalable and we will only impact local network bandwidth on the second datacenter.

Here my new lab configuration after applying distributed availability groups on the previous architecture.

  • In the first datacenter, one availability group AdvGrp that includes two replicas in synchronous replication and automatic failover for HA purpose
  • In the second datacenter, one availability AdvGrpDR that includes four replicas enrolled as read-only.
  • One distributed availability group AdvDistGrp which makes the cascade between the two aforementioned availability groups

blog 110 - 5 - distributed cfg AG

Let’s run the same workload test on the new architecture and let’s have a look at the new output:

The log send queue size got back to normal at the primary replica level on the first datacenter by cascading all the previous replication traffic from the primary replica located to the second datacenter (AdvGrpDR availability group).

blog 110 - 6 - AG DMV monitoring distrib

From a wait statistics perspective, we got rid of HADR_DATABASE_FLOW_CONTROL meaning we did not saturated the network link between the 2 datacenters

blog 110 - 7 - AG wait stats distributed

The picture below confirms the replication traffic dropped drastically with this new configuration (150 Mbps vs 400 Mbps from the first architecture).

blog 110 - 8 - perfmon AG distributed

Bottom line

In this blog post I tried to demonstrate using distributed availability groups to cascade the replication traffic to another replica may be a good idea in order to address scenarios which include many secondary replicas on a remote location with a low network bandwidth. However introducing distributed availability groups has a cost in terms of management because we have to deal with an additional layer of complexity. But if the rewards make the effort worthwhile we should consider this kind of architecture.

 

 

 

 

 

Cet article SQL Server 2016: distributed availability groups and cascaded replicas est apparu en premier sur Blog dbi services.

UKOUG 2016 Day 2

Mon, 2016-12-05 12:07

uk2

Today I assisted at a first session about one of my favorite tool: Upgrade to EM 13c now. The session was presented by Phil Gric from Red Stack Tech.

At the begining he described us the most common mistakes while implementing Enterprise Manager:

- EM 13c is an enterprise application

- It is a critical part of your infrastructure

- it is designed to help you

- EM 13c is not a glorified db console

- IT manager should not see EM as a job for DBA

He described us the main pre requisites before to realize an EM 13c upgrade ( for example disable optimizer_adaptive_features). He also talked about isssues such as the upgrade will create users with the sysman password, we should ensure that the repository password policy accept such a password.

There is also an issue while upgrading agent on AIX to 13.2 version. There is a problem securing the agent due to SHA encryption (Metalink Note 1965676.1).

To complete his presentation, he described us the main new features in EM 13c: export and import of incident rules, incident compression, always on monitoring, in emcli more than 300 new verbs and a general functionnality improved, system broadcast , comparaison and drift management.

He finally explained us why for him it is important to regularly upgrade to the last EM13c version: it is easy to upgrade, and the longer you wait, the closer it is to the next upgrade :=))

The second presentation was about the 12c upgrade : the good , the bad and the ugly presented by Niall Litchfield. He talked about his experiences about upgrading to 12c a very huge infrastructure composed of more than 100 servers, with database version from 10.1 to 11.2.0.3, with RAC or single instances.

His first advice was to read the Mike Dietrich documentation (Update, Migrate , Consolidate to 12c), and to have a look at the Oracle recommanded patch list.

A good reason to upgrade is because the support for 11g ends at teh end of the year, and the extended support is expensive.

The good news after this huge upgrade was that there has been no upgrade failures (tens of clusters, hundreds of servers and databases), a performance benchmark showed a 50 % improvement.

The bad and ugly news concern the number of patches. It also concern the JSON bundle patches which require database bundle patches. He also adviced us to turn off the optimizer_adaptive_features (recommanded also to be disabled with EM13c, PeopleSoft and EBS). Finally a last ugly point is the documentation, there is no one place to read the documenation but many. He also recommended to allow significant time for testing the database and the applications after the upgrade to 12c.

Then I assisted at a session talking about Oracle database 12c on Windows animated by Christian Shay of Oracle.

He showed us the database certification on 64-bit Windows. In a short resume Oracle 12..2 is certified on Windows server 2012, Windows Server 2012 R2, Windows 10 and Windows Server 2016, as Oracle 12.1 is certified on the same servers except Windows Server 2016.

In Windows 8 and Windows Server 2012, Microsoft has introduced the Group Managed service Account (GMSA), i.e. a domain level account which can be used by multiple servers in that domain to run their services under this account. A GMSA can be the Oracle Home user for Oracle Database Real Application Clusters (Oracle RAC), single instance, and client installations. It has similarities with the ‘oracle’ user on Linux, as you are able to connect on windows with this user and perform administrative tasks  like create database, install Oracle or upgrade databases.

In Windows 7 and Windows Server 2008 R2, Microsoft introduced virtual accounts. A virtual account can be the Oracle home user for Oracle Database single instance and client installations.

The recommandations are the following: for DB server (single instance) use virtual account to avoid password management (12.2), for 12.1 specify a Windows user account during installation. For RAC DB and Grid infrastructure, use a domain user or group managed service account, for a GMSA you do not need to provide the password for any database operation.

He also talked about large page support for windows. When large page support is enabled, the CU are able to access the Oracle database buffers im RAM more quickly. It will address the buffers in 2 MB page size instead of 4 KB increments.

Large pages can be used in two modes : Regular or Mixed mode. The regular one means all the SGA is attempted to be allocated in large pages. By the way if the amount of large pages is not available the database will not come up. Thats the reason using the mixed mode is perhaps better, if all the SGA cannot be allocated in large pages, the rest of the pages will be allocated with regular pages and the instance will come up.

I finished my UKOUG day by assisting at Franck Pachot’s session talking about 12c Mutltitenant (not a revolution but an evolution). He clearly explained us that we did not have to fear about 12c mutlitenant, from the begining of Oracle there has been a lot of new features a lot people feared, but now they are impelemented and work correctly. By the way the patch upgrade optimization is partially implemented, we will see how 12c multitenant will evolve in the next years.

 

 

 

 

 

Cet article UKOUG 2016 Day 2 est apparu en premier sur Blog dbi services.

UKOUG – Tech16 – Super Sunday

Mon, 2016-12-05 04:38

Screen Shot 2016-12-05 at 11.31.49

 

This year, I had the opportunity to attend the UKOUG 2016 which took place in Birmingham. This event normally begin on Monday but each year, there is a complimentary afternoon with high technical presentation for those who are registered for the Tech16 called super Sunday.

For this first Super Sunday afternoon at UKOUG 2016, I followed 2 sessions and I participated to an hands on lab around the cloud.

The 1st session was very interesting with lost of useful information about APEX and nodeJS.

This session was called NodeJS & Oracle – A Match Made in Heaven and the presenter, Mr Trond Enstad, focused the session to demonstrate the power of using Node.js.

He installed, Node.js, an Oracle Database client and created Node.js config file extracting sar command informations storing them in a remote Oracle Database. After that, he quickly created D3 charts in APEX showing real time monitoring of those stored information. I’m really enthousiaste to do some tests.

The 2nd session “Auto REST Enabling Your Tables, Views, & More With SQL Developer & REST Data Services” from Mr Jeff Smith was also interesting providing useful information about the ORDS product from Oracle.

After these 2 interesting sessions, I followed an Oracle Cloud Platform Hands On Lab called “Cloud Native Stack on Oracle Bare Metal Compute”.

In this labs, we created a Virtual Cloud Network (VCN) in which we were able to create a bare metal instance with Oracle Linux 7.2. Once launched, we installed MongoDB, we setup Node.js and MEAN.js. At the end, we were able to access the mean home page.

It was interesting to see how fast, we were able to provision a Bare Metal instance to install application components on it.

See you tomorrow for other interesting sessions.

 

Cet article UKOUG – Tech16 – Super Sunday est apparu en premier sur Blog dbi services.

Multitenant internals – Summary

Mon, 2016-12-05 02:02

Today at UKOUG TECH16 conference I’m presenting the internals of the new multitenant architecture: 12c Multitenant: Not a Revolution, Just an Evolution. My goal is to show how it works, that metadata links and object links are not blind magic.
Here are the links to the blog posts I’ve published about multitenant internals.

Fichier 05.12.16 07 39 43
The dictionary separation, METADATA LINK and OBJECT LINK (now called DATA LINK): :
http://blog.dbi-services.com/multitenant-dictionary-what-is-stored-only-in-cdbroot/
http://blog.dbi-services.com/oracle-12c-cdb-metadata-a-object-links-internals/
http://blog.dbi-services.com/oracle-multitenant-dictionary-metadata-links/
http://blog.dbi-services.com/oracle-multitenant-dictionary-object-links/
http://blog.dbi-services.com/multitenant-internals-how-object-links-are-parsedexecuted/
http://blog.dbi-services.com/multitenant-internals-object-links-on-fixed-tables/
An exemple with the AWR views:
http://blog.dbi-services.com/12c-multitenant-internals-awr-tables-and-views/
How the upgrades should work:
http://blog.dbi-services.com/oracle-multitenant-dictionary-upgrade/
What about shared pool rowcache and library cache:
http://blog.dbi-services.com/oracle-multitenant-dictionary-rowcache/
http://blog.dbi-services.com/12c-multitenant-cursor-sharing-in-cdb/
And how to see when session switches to CDB$ROOT:
http://blog.dbi-services.com/oracle-12cr2-multitenant-containers-in-sql_trace/

If you are in Birmingham, I’m speaking on Monday and Wednesday.

CaptureUKOUGFeaturedSpeaker

 

Cet article Multitenant internals – Summary est apparu en premier sur Blog dbi services.

Pages