Yann Neuhaus

Subscribe to Yann Neuhaus feed
dbi services technical blog
Updated: 16 hours 6 min ago

How to prevent RMAN archivelog backup from log switch

Fri, 2018-02-23 06:18

When RMAN backups archivelogs, it does a logswitch before backing up all archivelogs. In some cases this log switching should be suppressed. This can be done with expression “until time ‘sysdate'”, which actually filters nothing.

For example:

backup archivelog until time 'sysdate';
 

Cet article How to prevent RMAN archivelog backup from log switch est apparu en premier sur Blog dbi services.

ODA – manually resetting a CPU alert using Fault Manager

Fri, 2018-02-23 02:16

While supporting since several years ODAs of different generations and versions, we faced time to time some hardware alerts sent back by the ILOM. However all of them are not related to real hardware issues and are false positive. To get rid of them the solution is to reset them manually.

When an hardware error occurs the first reaction is to open a Service Request and to provide an ILOM snapshot to the support. This can easily be done using the Maintenance menu in the ILOM web interface.

Based on support feedback, they may confirm that this alert is simply a false positive. Another solution if support answer is too slow is simply to give a try  :-D
However this will need a server reboot to ensure the alert really disappeared.

Here an example of a fault alarm about CPU we faced:

Date/Time                 Subsystems          Component
------------------------  ------------------  ------------
Tue Feb 13 14:00:26 2018  Power               PS1 (Power Supply 1)
        A loss of AC input power to a power supply has been detected.
        (Probability:100, UUID:84846f3c-036d-6941-eaca-de18c4c236bd,
        Resource:/SYS/PS1, Part Number:7333459, Serial
        Number:465824T+1734D30847, Reference
        Document:http://support.oracle.com/msg/SPX86A-8003-EL)
Thu Feb 15 14:27:04 2018  System              DBP (Disk Backplane)
        ILOM has detected that a PCIE link layer is inactive. (Probability:25,
        UUID:49015767-38b2-6372-9526-c2d2c3885a72, Resource:/SYS/DBP, Part
        Number:7341145, Serial Number:465136N+1739P2009T, Reference
        Document:http://support.oracle.com/msg/SPX86A-8009-3J)
Thu Feb 15 14:27:04 2018  System              MB (Motherboard)
        ILOM has detected that a PCIE link layer is inactive. (Probability:25,
        UUID:49015767-38b2-6372-9526-c2d2c3885a72, Resource:/SYS/MB, Part
        Number:7317636, Serial Number:465136N+1742P500BX, Reference
        Document:http://support.oracle.com/msg/SPX86A-8009-3J)
Thu Feb 15 14:27:04 2018  Processors          P1 (CPU 1)
        ILOM has detected that a PCIE link layer is inactive. (Probability:25,
        UUID:49015767-38b2-6372-9526-c2d2c3885a72, Resource:/SYS/MB/P1, Part
        Number:SR3AX, Serial Number:54-85FED07F672D3DD3, Reference
        Document:http://support.oracle.com/msg/SPX86A-8009-3J)

 

We can see that there are indeed 3 alerts for this issue.

In order to reset such an alert, you need first to log in on the server as root and access the IPMI tool

[root@oda-dbi01 ~]# ipmitool -I open sunoem cli
Connected. Use ^D to exit.

Oracle(R) Integrated Lights Out Manager

Version 4.0.0.28 r121827

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

Warning: password is set to factory default.

Warning: HTTPS certificate is set to factory default.

Hostname: oda-dbi01-ilom

 

Once in IPMI, you can list the Open Problems to get the same output than above using the following command:

-> ls /System/Open_Problems

In the list of the Open Problems we can find the UUID of the concerned component (see 3rd line)

Thu Feb 15 14:27:04 2018  Processors          P1 (CPU 1)
        ILOM has detected that a PCIE link layer is inactive. (Probability:25,
        UUID:49015767-38b2-6372-9526-c2d2c3885a72, Resource:/SYS/MB/P1, Part
        Number:SR3AX, Serial Number:54-85FED07F672D3DD3, Reference
        Document:http://support.oracle.com/msg/SPX86A-8009-3J)

 

Now it is time to access the fault manager to reset all alerts related to this UUID

-> cd SP/faultmgmt/shell/
/SP/faultmgmt/shell

-> start
Are you sure you want to start /SP/faultmgmt/shell (y/n)? y

 

The reset of the alert is done with the fmadm command

faultmgmtsp> fmadm acquit 49015767-38b2-6372-9526-c2d2c3885a72

At this point the alerts are already removed from the Open problems. However to make sure the issue is really gone, we need to reboot the ODA and check the Open Problems afterwards.

Note that I presented here the way to check Open Problems using the IPMI command line, but the same output is also available in the ILOM web page.

Hope it helps!

 

 

Cet article ODA – manually resetting a CPU alert using Fault Manager est apparu en premier sur Blog dbi services.

ODA 12.2.1.2.0: Some curious password management rules

Thu, 2018-02-22 09:33

While deploying an ODA based on the DCS stack (odacli), it is mandatory to provide a “master” password at appliance creation. The web GUI provides for that a small tooltip which describes the rules applied on password management. However it looks like there is some flexibility with those rules. Lets try to check this out with some basics tests.

First of all here are the rules as provided by the ODA interface:

41-Web-CreateAppliance-PWDRules

So basically it has to start with an alpha character and be at least 9 characters long. My first reaction was that 9 characters is not to bad even if 10 would be better as minimum. Unfortunately it is not requesting any additional complexity mixing uppercase, lowercase, numbers… My second reaction, as most of IT guys, was to try to not respect these rules and see what happen :-P

I started really basically by using an “high secured” password: test

44-Web-CreateAppliance-PWD-test

Perfect the ODA reacted as expect and tells me I should read the rules once again. Next step is try something a bit more complicated: manager

..and don’t tell me you never used it in any Oracle environment ;-)

42-Web-CreateAppliance-PWD-manager

Fine, manager is still not 9 character long, 7 indeed, and the installer is still complaining. For now, everything is okay.
Next step was to try a password respecting the rules of 9 characters: welcome123

43-Web-CreateAppliance-PWD-welcome123

Still a faultless reaction of ODA!

Then I had the strange idea to test the historical ODA password: welcome1

43-Web-CreateAppliance-PWD-welcome1

Oops! The password starts with an alpha character fine, but if I’m right welcome1 is only 8 characters long :-?
If you don’t believe me, try to count the dot on the picture above….and I swear I didn’t use Gimp to “adjust” it ;-)

Finally just to be sure I tried another password of 8 characters: welcome2

43-Web-CreateAppliance-PWD-welcome2

Ah looks better. This time the installer sees that the password is not long enough and shows a warning.

…but would it mean that welcome1 is hard-coded somewhere??

 

Not matter, let’s continue and run the appliance creation with welcome123. Once done I try log using SSH to my brandly new created ODA using my new master password

43-CreateAppliance-PWD-SSH-Login-

it doesn’t work! 8-O

I tried multiple combination from welcome123, welcome1, Welcome123 and much more. Unfortunately none of them work.
At this point there are only 2 solutions to connect back to your ODA:

  1. There is still a shell connected as root to the ODA and then the root password can easily be changed using passwd
  2. No session is open to the ODA anymore and then it requires to open the remote console to reboot the ODA in Single User mode :-(

As the master password should be set to both root, grid and oracle users, I tried the password for grid and oracle too:

43-CreateAppliance-PWD-oracle-login

Same thing there the master password provided during the appliance creation hasn’t be set properly.

Hope it help!

 

Cet article ODA 12.2.1.2.0: Some curious password management rules est apparu en premier sur Blog dbi services.

Migrate Oracle Database(s) and ASM diskgroups from VMWARE to Oracle VM

Thu, 2018-02-22 06:45

This is a step by step demonstration on how to migrate any ASM disk groups from a cluster to another. May be use, with or without virtualization and may be used with storage layer snapshot for fast environment provisioning.

Step 01 – Shutdown source database(s) on VMWARE servers

Shutdown all databases hosted in the targeted Disk groups for which you want consistency. Then unmount the disk groups.

$ORACLE_HOME/bin/srvctl stop database -db cdb001

$ORACLE_HOME/bin/asmcmd umount FRA

$ORACLE_HOME/bin/asmcmd umount DATA

 

Step 02 – Re route LUNs from the storage array to newf servers

Create a snapshot and make the snapshot LUNs visible for Oracle Virtual Server (OVS) according the third-party storage technology.

Step 03 – Add LUNs to DomUs (VMs)

Then, we refresh the storage layer from OVM Manager to present LUNs in each OVS

OVM> refresh storagearray name=STORAGE_ARRAY_01

Step 04 – Then, tell OVM Manager to add LUNs to the VMs in which we want our databases to be migrated

create VmDiskMapping slot=20 physicalDisk=sa01_clus01_asm_data01 name=sa01_clus01_asm_data01 on Vm name=rac001
create VmDiskMapping slot=21 physicalDisk=sa01_clus01_asm_data02 name=sa01_clus01_asm_data02 on Vm name=rac001
create VmDiskMapping slot=22 physicalDisk=sa01_clus01_asm_data03 name=sa01_clus01_asm_data03 on Vm name=rac001
create VmDiskMapping slot=23 physicalDisk=sa01_clus01_asm_data04 name=sa01_clus01_asm_data04 on Vm name=rac001
create VmDiskMapping slot=24 physicalDisk=sa01_clus01_asm_data05 name=sa01_clus01_asm_data05 on Vm name=rac001
create VmDiskMapping slot=25 physicalDisk=sa01_clus01_asm_data06 name=sa01_clus01_asm_data06 on Vm name=rac001
create VmDiskMapping slot=26 physicalDisk=sa01_clus01_asm_reco01 name=sa01_clus01_asm_reco01 on Vm name=rac001
create VmDiskMapping slot=27 physicalDisk=sa01_clus01_asm_reco02 name=sa01_clus01_asm_reco02 on Vm name=rac001

create VmDiskMapping slot=20 physicalDisk=sa01_clus01_asm_data01 name=sa01_clus01_asm_data01 on Vm name=rac002
create VmDiskMapping slot=21 physicalDisk=sa01_clus01_asm_data02 name=sa01_clus01_asm_data02 on Vm name=rac002
create VmDiskMapping slot=22 physicalDisk=sa01_clus01_asm_data03 name=sa01_clus01_asm_data03 on Vm name=rac002
create VmDiskMapping slot=23 physicalDisk=sa01_clus01_asm_data04 name=sa01_clus01_asm_data04 on Vm name=rac002
create VmDiskMapping slot=24 physicalDisk=sa01_clus01_asm_data05 name=sa01_clus01_asm_data05 on Vm name=rac002
create VmDiskMapping slot=25 physicalDisk=sa01_clus01_asm_data06 name=sa01_clus01_asm_data06 on Vm name=rac002
create VmDiskMapping slot=26 physicalDisk=sa01_clus01_asm_reco01 name=sa01_clus01_asm_reco01 on Vm name=rac002
create VmDiskMapping slot=27 physicalDisk=sa01_clus01_asm_reco02 name=sa01_clus01_asm_reco02 on Vm name=rac002

At this stage we have all LUNs of our both disk groups for DATA and FRA available on both nodes of the cluster.

Step 05 – Migrate disk in AFD

We can rename disk groups if required or if a disk group with the same name already exists

renamedg phase=both dgname=DATA newdgname=DATAMIG verbose=true asm_diskstring='/dev/xvdr1','/dev/xvds1','/dev/xvdt1','/dev/xvdu1','/dev/xvdv1','/dev/xvdw1'
renamedg phase=both dgname=FRA  newdgname=FRAMIG  verbose=true asm_diskstring='/dev/xvdx1','/dev/xvdy1'

 

Then we migrate disks into AFD configuration

$ORACLE_HOME/bin/asmcmd afd_label DATAMIG /dev/xvdr1 --migrate
$ORACLE_HOME/bin/asmcmd afd_label DATAMIG /dev/xvds1 --migrate
$ORACLE_HOME/bin/asmcmd afd_label DATAMIG /dev/xvdt1 --migrate
$ORACLE_HOME/bin/asmcmd afd_label DATAMIG /dev/xvdu1 --migrate
$ORACLE_HOME/bin/asmcmd afd_label DATAMIG /dev/xvdv1 --migrate
$ORACLE_HOME/bin/asmcmd afd_label DATAMIG /dev/xvdw1 --migrate
$ORACLE_HOME/bin/asmcmd afd_label FRAMIG  /dev/xvdx1 --migrate
$ORACLE_HOME/bin/asmcmd afd_label FRAMIG  /dev/xvdy1 --migrate

 

Step 06 – Mount disk groups on the new cluster and add database(s) in the cluster

$ORACLE_HOME/bin/asmcmd mount DATAMIG
$ORACLE_HOME/bin/asmcmd mount FRAMIG

 

Then add database(s) to cluster (repeat for each database)

$ORACLE_HOME/bin/srvctl add database -db cdb001 \
-oraclehome /u01/app/oracle/product/12.2.0/dbhome_1 \
-dbtype RAC \
-spfile +DATAMIG/CDB001/spfileCDB001.ora \
-diskgroup DATAMIG,FRAMIG

 

Step 06 – Startup database

In that case, we renamed the disk groups so we need to modify file locations and some parameter values

create pfile='/tmp/initcdb001.ora' from spfile='+DATAMIG/<spfile_path>' ;
-- modify controlfiles, recovery area and any other relevant paramters
create spfile='+DATAMIG/CDB001/spfileCDB001.ora' from pfile='/tmp/initcdb001.ora' ;

ALTER DATABASE RENAME FILE '+DATA/<datafile_paths>','+DATAMIG/<datafile_paths>'
ALTER DATABASE RENAME FILE '+DATA/<tempfile_paths>','+DATAMIG/<tempfile_paths>'
ALTER DATABASE RENAME FILE '+DATA/<onlinelog_paths>','+DATAMIG/<onlinelog_paths>'
ALTER DATABASE RENAME FILE '+FRA/<onlinelog_paths>', '+FRAMIG/<onlinelog_paths>'

 

Then start the database

$ORACLE_HOME/bin/srvctl start database -db cdb001

 

This method can be used to easily migrated TB of data with almost no pain, reducing at most as possible the downtime period. For near Zero downtime migration, just add a GoldenGate replication on top of that.

The method describes here is also perfectly applicable for ASM snapshot in order to duplicate huge volume from one environment to another. This permits fast environment provisioning without the need to duplicate data over the network nor impact storage layer with intensive I/Os.

I hope it may help and please do not hesitate to contact us if you have any questions or require further information.

 

 

 

Cet article Migrate Oracle Database(s) and ASM diskgroups from VMWARE to Oracle VM est apparu en premier sur Blog dbi services.

ODA X7-2S/M 12.2.1.2.0: update-repository fails after re-image

Wed, 2018-02-21 00:54

While playing with a brand new ODA X7-2M, I faced a strange behaviour after re-imaging the ODA with the latest version 12.2.1.2.0. Basically after re-imaging and doing the configure-firstnet the next step is to import the GI clone in the repository before creating the appliance. Unfortunately this command fails with an error DCS-10001:Internal error encountered: Fail to start hand shake to localhost:7070. Why not having a look on how to fix it…

First of all doing a re-image is really straight forward and work very well. I simply access to the ILOM remote console to attach the ISO file for the ODA, in this case the patch 23530609 from the MOS, and restart the box on the CDROM. After approx. 40 minutes you have a brand new ODA running the latest release.

Of course instead re-imaging, I could “simply” update/upgrade the DCS agent to the latest version. Let say that I like to start from a “clean” situation when deploying a new environment and patching a not installed system sound a bit strange for me ;-)

So once re-imaged the ODA is ready for deployment. The first step is to configure the network that I can SSH to it and go ahead with the create appliance. This takes only 2 minutes using the command configure-firstnet.

The last requirement before running the appliance creation is to import the GI Clone, here the patch p27119393_122120, in the repository. Unfortunately that’s exactly where the problem starts…

Screen Shot 2018-02-19 at 12.11.23

Hmmm… I can’t get it in the repository due to a strange hand shake error. So I will check if the web interface is working at least (…of course using Chrome…)

Screen Shot 2018-02-19 at 12.11.14

Same thing here, it is not possible to come in the web interface at all.

While searching a bit for this error, we finally landed in the Know Issue chapter of the ODA 12.2.1.2.0 Release Note, which sounds promising. Unfortunately none of the listed error did really match to our case. However doing a small search in the page for the error message pointed us the following case out:

Screen Shot 2018-02-19 at 12.12.28

Ok the error is ODA X7-2HA related, but let’s give a try.

Restart-DCS

Once DCS is restarted, just re-try the update-repository

Import-GIClone

Here we go! The job has been submitted and the GI clone is imported in the repository :-)

After that the CREATE APPLIANCE will run like a charm.

Hope it helped!

 

 

Cet article ODA X7-2S/M 12.2.1.2.0: update-repository fails after re-image est apparu en premier sur Blog dbi services.

One command database upgrade on ODA

Tue, 2018-02-20 07:00

The 12.2 finally arrived on ODA and is now available on all generations. Modern ODAs are now supporting 11.2.0.4, 12.1.0.2 and 12.2.0.1 database engines, and these 3 versions can work together without any problem.

You probably plan to upgrade some old databases to the latest engine, at least those still running on 11.2. As you may know, 11.2  is no more supported with premier support since January 2015: it’s time to think about an upgrade. Note that premier support for 12.1 will end in July 2018. Actually, running 11.2 and 12.1 databases will need extended support this year. And this extended support is not free, as you can imagine. There is still an exception for 11.2.0.4, Oracle is offering extended support to his customers until the end of 2018.

Database upgrades have always been a lot of work, and often paired with a platform change. You need to recreate the databases, the tablespaces, export and import the data with datapump, correct the problems, and so on. Sometimes you can restore the old database to the new server with RMAN, but it’s only possible if the old engine is supported on your brand new server/OS combination.

As ODA is a longer term platform, you can think about ugrading the database directly on the appliance. Few years ago you should have been using dbua or catupgr, but now latest ODA package is including a tool for one command database upgrade. Let’s try it!

odacli, the ODA Client Line Interface, has a new option: upgrade-database. Parameters are very limited:

[root@oda-dbi01 2018-02-19]# odacli upgrade-database -h
Usage: upgrade-database [options]
Options:
--databaseids, -i
Database IDs to be upgraded
Default: []
* --destDbHomeId, -to
DB HOME ID of the destination
--help, -h
get help
--json, -j
json output
--sourceDbHomeId, -from
DB HOME ID of the source

You need to provide the database identifier (ODA stores a repository of all databases, db homes, jobs in a JavaDB/DerbyDB database) and the destination db home identifier you want to upgrade to. The source db home id is optional as Oracle can determine it quite easily. There is no other option (for the moment): no pre-backup (advised) and no storage migration (switch between acfs and ASM) for example.

Imagine you have an 11.2.0.4 database you want to upgrade to 12.2.0.1. Look for the id of your database ODAWS11:

[root@oda-dbi01 2018-02-19]# odacli list-databases

ID                                       DB Name    DB Type  DB Version           CDB        Class    Shape    Storage    Status        DbHomeID
---------------------------------------- ---------- -------- -------------------- ---------- -------- -------- ---------- ------------ ----------------------------------------
45ce9de7-3115-45b0-97b0-1384b8401e69     ODAWS      Si       12.2.0.1             false      OLTP     odb2     ASM        Configured   1ca87df9-4691-47ed-90a9-2a794128539d
a948a32c-1cf2-42c8-88c6-88fd9463b297     DBTEST1    Si       12.2.0.1             false      OLTP     odb1s    ACFS       Configured   1ca87df9-4691-47ed-90a9-2a794128539d
de281792-1904-4536-b42c-8a55df489b73     ODAWS11    Si       11.2.0.4             false      OLTP     odb2     ACFS       Configured   72023166-a39c-4a93-98b7-d552029b2eeaodacli create-dbhome -v 12.1.0.2.171017

Note that this database is configured with acfs, as 11.2 databases cannot be stored directly in an ASM 12c.

You can upgrade this database to an existing db home only: if you want to upgrade it to a new home, just create this new home, for example:

[root@oda-dbi01 2018-02-19]# odacli create-dbhome -v 12.1.0.2.171017

If you want to use an existing home, just pick the db home id, for example here the one used by ODAWS database.

Let’s do the upgrade:

[root@oda-dbi01 2018-02-19]# odacli upgrade-database -i de281792-1904-4536-b42c-8a55df489b73 -to 1ca87df9-4691-47ed-90a9-2a794128539d

{
"jobId" : "782e65fd-8b2b-4d16-a542-1f5b2b78d308",
"status" : "Created",
"message" : null,
"reports" : [ ],
"createTimestamp" : "February 19, 2018 17:40:58 PM CET",
"resourceList" : [ ],
"description" : "Database service upgrade with db ids: [de281792-1904-4536-b42c-8a55df489b73]",
"updatedTime" : "February 19, 2018 17:40:58 PM CET"
}

odacli will schedule a job for that, as for other operations. You can follow the job with describe-job:

[root@oda-dbi01 2018-02-19]# odacli describe-job -i 782e65fd-8b2b-4d16-a542-1f5b2b78d308

Job details
----------------------------------------------------------------
ID:  782e65fd-8b2b-4d16-a542-1f5b2b78d308
Description:  Database service upgrade with db ids: [de281792-1904-4536-b42c-8a55df489b73]
Status:  Running
Created:  February 19, 2018 5:40:58 PM CET
Message:

Task Name                                          Start Time                          End Time                            Status
-------------------------------------------------- ----------------------------------- ----------------------------------- ----------
Setting up ssh equivalance                         February 19, 2018 5:40:58 PM CET    February 19, 2018 5:40:58 PM CET    Success
Database Upgrade                                   February 19, 2018 5:40:58 PM CET    February 19, 2018 5:40:58 PM CET    Running

You can also look at the database alert.log file during the operation.

Be patient! Database upgrade is taking time, at least 20 minutes for an empty database. And it seems that other jobs planned during the upgrade are in waiting state (like a create-database for example).

[root@oda-dbi01 2018-02-19]# odacli describe-job -i 782e65fd-8b2b-4d16-a542-1f5b2b78d308

Job details
----------------------------------------------------------------
ID:  782e65fd-8b2b-4d16-a542-1f5b2b78d308
Description:  Database service upgrade with db ids: [de281792-1904-4536-b42c-8a55df489b73]
Status:  Running
Created:  February 19, 2018 5:40:58 PM CET
Message:

Task Name                                          Start Time                          End Time                            Status
-------------------------------------------------- ----------------------------------- ----------------------------------- ----------
Setting up ssh equivalance                         February 19, 2018 5:40:58 PM CET    February 19, 2018 5:40:58 PM CET    Success
Database Upgrade                                   February 19, 2018 5:40:58 PM CET    February 19, 2018 6:01:37 PM CET    Success

Now the upgrade seems OK, let’s check that:

su - oracle
. oraenv <<< ODAWS11
oracle@oda-dbi01:/home/oracle/ # sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Mon Feb 19 18:01:49 2018
Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select instance_name, version from v$instance;

INSTANCE_NAME     VERSION
---------------- -----------------
ODAWS11      12.2.0.1.0

sho parameter spfile

NAME                 TYPE     VALUE
-------------------- -------- ---------------------------------------------------------------
spfile               string   /u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/spfileODAWS11.ora

Even the spfile has been moved to new home, quite nice.

Let’s check the repository:

[root@oda-dbi01 ~]# odacli list-databases

ID                                       DB Name    DB Type  DB Version           CDB        Class    Shape    Storage    Status        DbHomeID
---------------------------------------- ---------- -------- -------------------- ---------- -------- -------- ---------- ------------ ----------------------------------------
45ce9de7-3115-45b0-97b0-1384b8401e69     ODAWS      Si       12.2.0.1             false      OLTP     odb2     ASM        Configured   1ca87df9-4691-47ed-90a9-2a794128539d
a948a32c-1cf2-42c8-88c6-88fd9463b297     DBTEST1    Si       12.2.0.1             false      OLTP     odb1s    ACFS       Configured   1ca87df9-4691-47ed-90a9-2a794128539d
de281792-1904-4536-b42c-8a55df489b73     ODAWS11    Si       12.2.0.1             false      OLTP     odb2     ACFS       Configured   1ca87df9-4691-47ed-90a9-2a794128539d

Everything looks fine!

Now let’s test the upgrade with a 12.1 database, ODAWS12. This one is using ASM storage:

[root@oda-dbi01 ~]# odacli list-databases

ID                                       DB Name    DB Type  DB Version           CDB        Class    Shape    Storage    Status        DbHomeID
---------------------------------------- ---------- -------- -------------------- ---------- -------- -------- ---------- ------------ ----------------------------------------
45ce9de7-3115-45b0-97b0-1384b8401e69     ODAWS      Si       12.2.0.1             false      OLTP     odb2     ASM        Configured   1ca87df9-4691-47ed-90a9-2a794128539d
a948a32c-1cf2-42c8-88c6-88fd9463b297     DBTEST1    Si       12.2.0.1             false      OLTP     odb1s    ACFS       Configured   1ca87df9-4691-47ed-90a9-2a794128539d
de281792-1904-4536-b42c-8a55df489b73     ODAWS11    Si       12.2.0.1             false      OLTP     odb2     ACFS       Configured   1ca87df9-4691-47ed-90a9-2a794128539d
0276326c-cb6d-4246-9943-8289d29d6a4f     DBTEST2    Si       12.2.0.1             false      OLTP     odb1s    ACFS       Configured   7d2bbaa0-da3c-4455-abee-6bf4ff2d2630
24821a48-7474-4a8b-8f36-afca399b6def     ODAWS12    Si       12.1.0.2             false      OLTP     odb2     ASM        Configured   520167d7-59c8-4732-80a6-cc32ef745cec

[root@oda-dbi01 2018-02-19]# odacli upgrade-database -i 24821a48-7474-4a8b-8f36-afca399b6def -to 1ca87df9-4691-47ed-90a9-2a794128539d
{
"jobId" : "10a2a304-4e8e-4b82-acdc-e4c0aa8b21be",
"status" : "Created",
"message" : null,
"reports" : [ ],
"createTimestamp" : "February 19, 2018 18:36:17 PM CET",
"resourceList" : [ ],
"description" : "Database service upgrade with db ids: [24821a48-7474-4a8b-8f36-afca399b6def]",
"updatedTime" : "February 19, 2018 18:36:17 PM CET"
}

[root@oda-dbi01 ~]# odacli list-databases

ID                                       DB Name    DB Type  DB Version           CDB        Class    Shape    Storage    Status        DbHomeID
---------------------------------------- ---------- -------- -------------------- ---------- -------- -------- ---------- ------------ ----------------------------------------
45ce9de7-3115-45b0-97b0-1384b8401e69     ODAWS      Si       12.2.0.1             false      OLTP     odb2     ASM        Configured   1ca87df9-4691-47ed-90a9-2a794128539d
a948a32c-1cf2-42c8-88c6-88fd9463b297     DBTEST1    Si       12.2.0.1             false      OLTP     odb1s    ACFS       Configured   1ca87df9-4691-47ed-90a9-2a794128539d
de281792-1904-4536-b42c-8a55df489b73     ODAWS11    Si       12.2.0.1             false      OLTP     odb2     ACFS       Configured   1ca87df9-4691-47ed-90a9-2a794128539d
0276326c-cb6d-4246-9943-8289d29d6a4f     DBTEST2    Si       12.2.0.1             false      OLTP     odb1s    ACFS       Configured   7d2bbaa0-da3c-4455-abee-6bf4ff2d2630
24821a48-7474-4a8b-8f36-afca399b6def     ODAWS12    Si       12.1.0.2             false      OLTP     odb2     ASM        Updating     520167d7-59c8-4732-80a6-cc32ef745cec

[root@oda-dbi01 2018-02-19]# odacli describe-job -i 10a2a304-4e8e-4b82-acdc-e4c0aa8b21be

Job details
----------------------------------------------------------------
ID:  10a2a304-4e8e-4b82-acdc-e4c0aa8b21be
Description:  Database service upgrade with db ids: [24821a48-7474-4a8b-8f36-afca399b6def]
Status:  Running
Created:  February 19, 2018 6:36:17 PM CET
Message:

Task Name                                          Start Time                          End Time                            Status
-------------------------------------------------- ----------------------------------- ----------------------------------- ----------
Setting up ssh equivalance                         February 19, 2018 6:36:17 PM CET    February 19, 2018 6:36:17 PM CET    Success
Database Upgrade                                   February 19, 2018 6:36:17 PM CET    February 19, 2018 6:58:05 PM CET    Success

ID                                       DB Name    DB Type  DB Version           CDB        Class    Shape    Storage    Status        DbHomeID
---------------------------------------- ---------- -------- -------------------- ---------- -------- -------- ---------- ------------ ----------------------------------------
45ce9de7-3115-45b0-97b0-1384b8401e69     ODAWS      Si       12.2.0.1             false      OLTP     odb2     ASM        Configured   1ca87df9-4691-47ed-90a9-2a794128539d
a948a32c-1cf2-42c8-88c6-88fd9463b297     DBTEST1    Si       12.2.0.1             false      OLTP     odb1s    ACFS       Configured   1ca87df9-4691-47ed-90a9-2a794128539d
de281792-1904-4536-b42c-8a55df489b73     ODAWS11    Si       12.2.0.1             false      OLTP     odb2     ACFS       Configured   1ca87df9-4691-47ed-90a9-2a794128539d
0276326c-cb6d-4246-9943-8289d29d6a4f     DBTEST2    Si       12.2.0.1             false      OLTP     odb1s    ACFS       Configured   7d2bbaa0-da3c-4455-abee-6bf4ff2d2630
24821a48-7474-4a8b-8f36-afca399b6def     ODAWS12    Si       12.2.0.1             false      OLTP     odb2     ASM        Configured   1ca87df9-4691-47ed-90a9-2a794128539d

su - oracle
. oraenv <<< ODAWS12
oracle@oda-dbi01:/home/oracle/ # sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Mon Feb 19 18:59:08 2018
Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select instance_name, version from v$instance;

INSTANCE_NAME     VERSION
---------------- -----------------
ODAWS12      12.2.0.1.0

SQL> sho parameter spfile

NAME               TYPE       VALUE
------------------ ---------- ---------------------------------------------
spfile             string     +DATA/ODAWS12/PARAMETERFILE/spfileodaws12.ora

It also worked fine with an 12.1 database: and it also took about 20 minutes for an empty database.

You may have noticed that it’s possible to upgrade several databases in the same time by providing multiple database id. Not sure if you would do that in real life :-)

upgrade-database is also available on ODA that are still using oakcli (nowadays only virtualized ODA I think), but as oakcli has no repository, database id has to be replaced by database name,  and db home id by the name registered in classic oraInventory, for example:

oakcli upgrade database -db ODAWS11 -to OraDb12201_home1

 

This great feature will not revolutionize your DBA life, but it should help to upgrade your database with minimum effort.

 

Cet article One command database upgrade on ODA est apparu en premier sur Blog dbi services.

ODA 12.2.1.2.0 – VLAN Management

Tue, 2018-02-20 02:48

Virtual Local Area Network (VLAN) have become since several years a standard in enterprise class networks. Most enterprises are now segregating their network, especially for security reasons, between server and user or prod and test or applications and backup and aso. In the new release of ODA we finally get the support for VLAN on the bare metal platform. This article will briefly demonstrate how these are managed using ODAADMCLI.

First of all we have to remember that VLAN is not brand new on ODA. While using ODA HA (X5-2 or X6-2) in virtual mode, which means with OVM, it was already possible to manage VLANs. However this was a bit different than the new feature introduced in ODA 12.2.1.2.0.

First of all the HA platform in virtual mode is running using OAKCLI and not ODACLI with the DCS agent. In background the real difference is that the HA in virtualized mode is using the Linux Bridge Control (more details here). To make it simple, you have a kind of virtual switches (bridges) on which each connected VM, including the ODA BASE, can be connected and get an address on this particular network.

On the bare metal platform the principle is totally different as it is directly based on the VLAN implementation in Linux (802.1q standard) which allows activating VLAN on an interface and tagging the packets with the right VLANID.

The first place where VLAN can be configured on the ODA is during the first network plumbing phase, right after booting or re-imaging the ODA, using the configure-firstnet command.

30-ILOM-Configure-FirstNet-VLAN

As shown above the command will ask you if you want to use VLAN or not. Answering YES will then request you to provide the VLANID for the primary network of the ODA and will generate a network interface btbond1.<VLANID>.

How does it looks like in background??

The first layer as always on ODA is a bonding of 2 physical interfaces (here em2 and em3 as I’m using the copper interfaces):

[root@oak network-scripts]# cat ifcfg-em2
#File created by Oracle development
DEVICE=em2
ONBOOT=yes
BOOTPROTO=none
USERCTL=no
TYPE=ETHERNET
ETHTOOL_OFFLOAD_OPTS="lro off"
IPV6INIT=no
NM_CONTROLLED=no
PEERDNS=no
MASTER=btbond1
SLAVE=yes

Looking to the btbond1 interface we will see that it is configure in active-backup mode BUT without any IP address.

[root@oak network-scripts]# cat ifcfg-btbond1
#File created by Oracle development
DEVICE=btbond1
ONBOOT=yes
BOOTPROTO=none
USERCTL=no
TYPE=BOND
BONDING_OPTS="mode=active-backup miimon=100 primary=em2"
IPV6INIT=no
NM_CONTROLLED=no
PEERDNS=no

On top of the bonding configuration, we have then a virtual interface per VLAN. Indeed only one at the beginning as the configure-firstnet generates only the “primary” network of the ODA.

[root@oak network-scripts]# cat ifcfg-btbond1.54
#ODA_VLAN_CONFIG ===
#ODA_VLAN_CONFIG Name=vlan54
#ODA_VLAN_CONFIG VlanId=54
#ODA_VLAN_CONFIG VlanInterface=btbond1
#ODA_VLAN_CONFIG Type=VlanType
#ODA_VLAN_CONFIG VlanSetupType=public
#ODA_VLAN_CONFIG VlanIpAddr=192.168.54.10
#ODA_VLAN_CONFIG VlanNetmask=255.255.255.0
#ODA_VLAN_CONFIG VlanGateway=192.168.54.1
#ODA_VLAN_CONFIG NodeNum=0
#=== DO NOT EDIT ANYTHING ABOVE THIS LINE ===
DEVICE=btbond1.54
BOOTPROTO=none
ONBOOT=yes
VLAN=yes
NM_CONTROLLED=no
IPADDR=192.168.54.10
NETMASK=255.255.255.0
GATEWAY=192.168.54.1

Do not look for the VLANID in the configuration file (of, except in the comments  ;-) ). It is defined by the device/file name.

Once you have your first VLAN you can easily configure additional ones using the command line. Remember that on the DCS stack ODAs you have 2 different CLIs: ODACLI and ODAADMCLI. The VLAN management is done using ODAADMCLI.

So lets have a look to the help:

[root@oda-dbi01 ~]
# odaadmcli -h
Usage: odaadmcli <command> <object> [<options>]
 commands: show|manage|stordiag|power|expand
 objects : disk|diskgroup|controller|server|processor|memory|iraid|
 power|cooling|network|storage|fs|raidsyncstatus|env_hw|vlan

Usage: odaadmcli show - Shows disk, diskgroup, controller, server, processor,
 memory, iraid, power, cooling, network,
 storage, fs, raidsyncstatus, env_hw
 odaadmcli manage - Manages the OAK repository, diagcollect etc.,
 odaadmcli stordiag - Run storage diagnostic tool on this Node
 odaadmcli power - Power on|off|status disk
 odaadmcli expand - Expand storage

Hmmm, looks strange as there is no command CREATE 8-O
I can SHOW the VLANs but it looks like I can’t CREATE them… Let’s have a look to the online documentation (here)

Screen Shot 2018-02-20 at 09.16.48

It looks like the CREATE VLAN command finally exist. A good advice to remember here is that even the inline help of ODACLI and ODAADMCLI are quite good, it is still a good practice to have a look to the online documentation and especially the chapters 14 and 15.

The good news here is that the help for the CREATE command does exist ;-)

[root@oda-dbi01 ~]# odaadmcli create vlan -h
odaadmcli create vlan <vlan_name> -vlanid <vlanid> -if <interface> -node <node_num> -setuptype <type> -ip <ip address> -netmask <netmask> -gateway <gateway>, Where:
	 vlan                  -  VLAN name (unique per Node)
	 vlanid                -  Identifies the ID[valid range: 2 to 4094] to which the VLAN belongs to (unique per Node)
	 interface             -  Interface on which the VLAN is to be created [Valid interfaces are btbond1]
	 node                  -  Node number < 0 >
	 setuptype             -  the type of the VLAN setup for [Valid value are: management, database, dataguard, backup, private and other]
	 ip                    -  IP address for the VLAN
	 netmask               -  Netmask address for the VLAN
	 gateway               -  Gateway address for the VLAN

I guess it’s now time to try to create a new VLAN.

[root@oda-dbi01 ~]# odaadmcli create vlan vlan55-backup -vlanid 55 -if btbond1 -node 0 -setuptype backup -ip 192.168.55.10 -netmask 255.255.255.0 -gateway 192.168.55.1

Created Vlan : vlan55-backup

Note that even on a single node ODA (S or M) you must provide the node number. Otherwise you will get the following error message

ERROR : -node is not available

Let check our newly created VLAN:

[root@oda-dbi01 network-scripts]# odaadmcli show vlan
	NAME                     ID    INTERFACE   CONFIG_TYPE IP_ADDRESS      NETMASK         GATEWAY         NODENUM
	vlan55-backup            55    btbond1     backup      192.168.55.10   255.255.255.0   192.168.54.1    0

Of course in /etc/sysconfig/network-scripts we will find the corresponding IFCFG file:

[root@oda-dbi01 network-scripts]# ls -lrt
total 248
-rw-r--r--. 1 root root 29853 Apr 12  2016 network-functions-ipv6
-rw-r--r--. 1 root root 14731 Apr 12  2016 network-functions
...
...
...
-rw-r--r--. 1 root root   264 Feb 19 11:40 ifcfg-lo
-rw-r--r--  3 root root   194 Feb 19 12:04 ifcfg-em3
-rw-r--r--  3 root root   194 Feb 19 12:04 ifcfg-em2
-rw-r--r--  3 root root   169 Feb 19 12:04 ifcfg-em1
drwxr-xr-x  2 root root  4096 Feb 19 12:04 backupifcfgFiles
-rw-r--r--. 3 root root   259 Feb 19 12:17 ifcfg-btbond1
-rw-r--r--  1 root root   538 Feb 19 14:43 ifcfg-btbond1.54
-rw-r--r--  1 root root   538 Feb 19 15:31 ifcfg-btbond1.55

Should a VLAN not be required anymore, deleting it works straight forward using DELETE VLAN:

[root@oda-dbi01 network-scripts]# odaadmcli delete vlan vlan55-backup -node 0

Deleted Vlan : vlan55-backup

Easy isn’t it? 8-)

Last but not least, for those who deployed their ODA without VLAN do not worry you won’t need to re-image it. Even if the ODA has been deployed without VLAN during the configure-firstnet you can still create VLAN afterwards.

Enjoy!

 

Cet article ODA 12.2.1.2.0 – VLAN Management est apparu en premier sur Blog dbi services.

18c Read Only Oracle Home

Sun, 2018-02-18 13:49

Capture18c000This is the big new feature of Oracle 18c about database software installation. Something that was needed for decades for the ease of software deployment. Piet de Visser raised this to Oracle a long time ago, and we were talking about that recently when discussing this new excitement to deploy software in Docker containers. Docker containers are by definition immutable images. You need a Read Only Oracle Home, all the immutable files (configuration, logs, database) being in an external volume. Then, to upgrade the software, you just open this volume with an image of the new database version.

roohctl

In 12.2 you may have seen a ‘roohctl’ script in ORACLE_HOME/bin. The help explains that ‘rooh’ stands for Read-Only Oracle Home:

[oracle@VM122 ~]$ roohctl -help
Usage: roohctl [] [ ] Following are the possible flags:
-help
 
Following are the possible commands:
-enable Enable Read-only Oracle Home
-disable Disable Read-only Oracle Home

Note that in 18c the help does not show ‘-disable’ even if it is accepted….
So in 12cR2 you were able to run ‘roohctl -enable’ but the only thing it did was changing the Yes/No flag in orabasetab:

cat $ORACLE_HOME/install/orabasetab
#orabasetab file is used to track Oracle Home associated with Oracle Base
/u01/app/oracle/product/12.2.0/dbhome_1:/u01/app/oracle:OraDB12Home1:Y:

Oracle 18

Here is an Oracle 18 that I re-installed (as an Oracle Home Clone) with the following:

runInstaller -clone ORACLE_HOME=/u01/app/oracle/product/181 ORACLE_HOME_NAME=O181 ORACLE_BASE=/u00/app/oracle

My idea is to be able to easily differentiate the different paths (ORACLE_HOME under /u01 and ORACLE_BASE under /u00)

The $ORACLE_HOME/install/orabasetab records the ORACLE_HOME, ORACLE_BASE and ORACLE_HOME_NAME:

[oracle@VM181 18c]$ cat $ORACLE_HOME/install/orabasetab
#orabasetab file is used to track Oracle Home associated with Oracle Base
/u01/app/oracle/product/181:/u01/app/oracle:O181:N:

ORACLE_HOME: This may seem useless because this file is under ORACLE_HOME, so if you read it you are supposed to know the ORACLE_HOME. However, you may find it from different paths (symbolic links, /../.) and this is a good way to normalize it.

ORACLE_BASE: This will be used to externalize the mutable files outside of the ORACLE_HOME

ORACLE_HOME_NAME: is the name of Oracle Home that you provide when installing and you can find in the Oracle Inventory.

The last field is ‘N’ when the mutable files are under ORACLE_HOME and ‘Y’ when they are externalized to have an immutable Read Only Oracle Home.

We are not supposed to use this file directly. It is modified by runInstaller and roohctl. And it is read by orabasehome and orabaseconfig

orabasehome and orabaseconfig

We have two new location name derived from the orabasetab content.

One is the ‘Oracle Base Config’ which is mostly there to find the configuration files (.ora, .dat) in the /dbs subdirectory. With Read Only Oracle Home, this is set to the ORACLE_BASE:

[oracle@VM181 18c]$ orabaseconfig
/u00/app/oracle

Most of the files in /dbs have the ORACLE_SID in their name, which is unique in the host, and this is why they can all go into the same directory. However, I would prefer a subdirectory per database. When you move a database from one system to another, it is easier to move a directory. You can do per-file symbolic links but be sure to maintain them as they may be re-created as files.

The other is the ‘Oracle Base Home’ which is mostly there for the /network subdirectory (with the SQL*Net configuration files, logs and trace) and the /assistant (DBCA templates) and /install ones. With Read Only Oracle Home, this goes to a /homes subdirectory of ORACLE_BASE

[oracle@VM181 18c]$ orabasehome
/u00/app/oracle/homes/O181

As you see, there is an additional subdirectory with the name of the Oracle Home. In my opinion, it is not a good idea to put sqlnet.ora, tnsnames.ora and listener.ora here. It is better to have one common TNS_ADMIN. However, because the default was one directory per Oracle Home, the Read Only Oracle Home feature had to keep this possibility. In 12.2 an ORACLE_HOME/env.ora was introduced to set TNS_ADMIN in a consistent way.

With Read Only Oracle Home enabled, I strace-ed a ‘startup’ to show which files are read:

[oracle@VM181 18c]$ ORACLE_SID=CDB18 strace -e trace=file -f sqlplus / as sysdba <<&1 | grep /u00
...
open("/u00/app/oracle/homes/O181/network/admin/oraaccess.xml", O_RDONLY) = -1 ENOENT (No such file or directory)
access("/u00/app/oracle/homes/O181/network/admin/oraaccess.xml", F_OK) = -1 ENOENT (No such file or directory)
open("/u00/app/oracle/homes/O181/network/admin/oraaccess.xml", O_RDONLY) = -1 ENOENT (No such file or directory)
access("/u00/app/oracle/homes/O181/network/admin/sqlnet.ora", F_OK) = -1 ENOENT (No such file or directory)
...
access("/u00/app/oracle/homes/O181/network/admin/sqlnet.ora", F_OK) = -1 ENOENT (No such file or directory)
access("/u00/app/oracle/homes/O181/network/admin/intchg.ora", F_OK) = -1 ENOENT (No such file or directory)
access("/u00/app/oracle/homes/O181/network/admin/tnsnav.ora", F_OK) = -1 ENOENT (No such file or directory)
...
open("/u00/app/oracle/dbs/cm_CDB18.dat", O_RDONLY|O_SYNC) = -1 ENOENT (No such file or directory)
[pid 15339] access("/u00/app/oracle/homes/O181/network/admin/sqlnet.ora", F_OK) = -1 ENOENT (No such file or directory)
...
[pid 15339] stat("/u00/app/oracle/dbs/spfileCDB18.ora", 0x7ffe6a5785b8) = -1 ENOENT (No such file or directory)
[pid 15339] open("/u00/app/oracle/dbs", O_RDONLY) = 8
[pid 15339] stat("/u00/app/oracle/dbs/spfileCDB18.ora", 0x7ffe6a578010) = -1 ENOENT (No such file or directory)
[pid 15339] stat("/u00/app/oracle/homes/O181/dbs/spfile.ora", 0x7ffe6a5785b8) = -1 ENOENT (No such file or directory)
[pid 15339] open("/u00/app/oracle/homes/O181/dbs", O_RDONLY) = 8
[pid 15339] stat("/u00/app/oracle/homes/O181/dbs/spfile.ora", 0x7ffe6a578010) = -1 ENOENT (No such file or directory)
[pid 15339] access("/u00/app/oracle/dbs/initCDB18.ora", F_OK) = -1 ENOENT (No such file or directory)
[pid 15339] open("/u00/app/oracle/dbs/initCDB18.ora", O_RDONLY) = -1 ENOENT (No such file or directory)
LRM-00109: could not open parameter file '/u00/app/oracle/dbs/initCDB18.ora'

The files were not there as I’ve not created any database here. The goal is to show that there is no attempt to read any configuration file under ORACLE_HOME.

You can also see that DBCA will search for templates in this new directory:

Capture18c003

I mentioned network and assistant subdirectories. But it concerns all directories where the instance can write files:

[oracle@VM181 18c]$ du $ORACLE_BASE/homes
4 /u01/app/oracle/homes/O181/assistants/dbca/templates
8 /u01/app/oracle/homes/O181/assistants/dbca
12 /u01/app/oracle/homes/O181/assistants
4 /u01/app/oracle/homes/O181/network/trace
4 /u01/app/oracle/homes/O181/network/admin
4 /u01/app/oracle/homes/O181/network/log
16 /u01/app/oracle/homes/O181/network
4 /u01/app/oracle/homes/O181/dbs
4 /u01/app/oracle/homes/O181/install
64 /u01/app/oracle/homes/O181/rdbms/log
72 /u01/app/oracle/homes/O181/rdbms/audit
140 /u01/app/oracle/homes/O181/rdbms
180 /u01/app/oracle/homes/O181
184 /u01/app/oracle/homes

You may wonder why we see a /dbs subdirectory here as the instance configuration files are in the common /u01/app/oracle/dbs. The /dbs is also the current working directory for oracle processes. And this one will be set to ORACLE_BASE/homes/oracle_home_name/dbs.

We can also see /rdbms/log here. I opened a bug 2 years ago about SBTIO.LOG not going to the right place under ADR_HOME, but going to ORACLE_HOME/rdbms/log (Bug 23559013 USER_DUMP_DEST VALUE NOT IGNORED EVEN THOUGH DIAGNOSTIC_DEST IS SET). I’ve no idea about the status of the bug, but at least this will not go to Oracle Home anymore. Even if you don’t really have the need to have a Read Only Oracle Home, this feature is a good way to ensure that it will not grow and fill the filesystem.

Enable Read Only Oracle Home

You enable this feature with ‘roohctl -enable’ after software installation and before any creation of databases or listeners:

[oracle@VM181 18c]$ roohctl -enable
Enabling Read-Only Oracle home.
Update orabasetab file to enable Read-Only Oracle home.
Orabasetab file has been updated successfully.
Create bootstrap directories for Read-Only Oracle home.
Bootstrap directories have been created successfully.
Bootstrap files have been processed successfully.
Read-Only Oracle home has been enabled successfully.
Check the log file /u01/app/oracle/cfgtoollogs/roohctl/roohctl-180217PM111551.log.

If the utility tool finds an existing database or listener related to this Oracle Home, it will return this kind of error:

Cannot enable Read-Only Oracle home in a configured Oracle home.
The Oracle Home is configured with databases 'CDB18'.
The Oracle Home is configured with listeners 'LISTENER'.

There is an undocumented ‘-force’ parameter to add to ‘roohctl -enable’ which can proceed anyway, but it will not move the configuration files.

I have not tested all possibilities because the General Availability of 18c is currently limited to Exadata and Oracle Public Cloud. But it seems that this roohctl will work the same on Windows (with /database instead of /dbs and with registry settings instead of orabasetab) and with Grid Infrastructure (there’s a -nodeList argument).

I mentioned above that the ORACLE_HOME/install goes to $(orabasehome)/install. I don’t know which files go there when ROOH is enabled. The orabasetab remains under ORACLE_HOME, of course. And some logs, such as re-running root.sh, still go to ORACLE_HOME/install:

[oracle@VM181 ~]$ sudo $ORACLE_HOME/root.sh
Check /u01/app/oracle/product/181/install/root_VM181_2018-02-18_19-06-23-833474515.log for the output of root script

This looks strange, but remember that the idea of a Read Only Oracle Home is to ship it after all changes are done. If you have something to change (patch, re-link, …) that will go to another Oracle Home. Maybe cloned from the other, then made Read Only after the changes.

?/

Do you use the question mark as a shortcut to ORACLE_HOME? This does not change and remains the ORACLE_HOME:

[oracle@VM181 ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 18.0.0.0.0 Production on Sun Feb 18 20:26:33 2018
Version 18.1.0.0.0
Copyright (c) 1982, 2017, Oracle. All rights reserved.
Connected to an idle instance.
SQL> start ?
SP2-0310: unable to open file "/u01/app/oracle/product/181.sql"
SQL> exit
Disconnected

This is ok as I mostly use it to read files from the software distribution (such as ?/rdbms/admin/awrrpt)

If you use it in database configuration files, then be careful. Here I have enabled ROOH and defined a pfile mentioning the spfile with the ‘?’ shortcut

[oracle@VM181 ~]$ orabaseconfig
/u00/app/oracle
[oracle@VM181 ~]$ cat $(orabaseconfig)/dbs/init$ORACLE_SID.ora
spfile=?/dbs/xxxx

However, the ‘?’ is resolved to ORACLE_HOME and not Oracle Base Config:

[oracle@VM181 ~]$ strace -f sqlplus / as sysdba <<&1 | grep xxx
[pid 1898] read(10, "spfile=?/dbs/xxx\n", 4096) = 17
[pid 1898] stat("/u01/app/oracle/product/181/dbs/xxx", 0x7ffc5ac1c758) = -1 ENOENT (No such file or directory)
[pid 1898] stat("/u01/app/oracle/product/181/dbs/xxx", 0x7ffc5ac1c1b0) = -1 ENOENT (No such file or directory)
ORA-01565: error in identifying file '?/dbs/xxx'

So what?

Having a read-only Oracle Home, or at least be sure that you have no files written into it, is a good idea. Easier to manage space. Easier to deploy by cloning Oracle Home. Good practice to separate big software directory from small configuration files. And to have the current working directory outside of that. Having configuration files at the same place as the software is always a bad idea (and reminds me the .ini files in C:\WIN directory a long time ago). So, even if it is not enabled by default, Read Only Oracle Home is the way to go.

I think the risks are very limited once tested, as it is just changing the directories and any problem can be worked around with symbolic links on directories. However, this may change some habits and scripts. Not finding the right configuration file in a stressful situation may be annoying.

So, don’t wait, and even in 12c, you can change your habits and replace all references to ${ORACLE_HOME}/dbs by $(orabaseconfig)/dbs and other ${ORACLE_HOME} to $(orabasehome). In 12c they will go to the same ORACLE_HOME. And they you will be ready to enable ROOH in 18c.

 

Cet article 18c Read Only Oracle Home est apparu en premier sur Blog dbi services.

My personal journey with Linux on the desktop

Sun, 2018-02-18 10:19

My Linux experience started back in 1999 when we needed a router for our ISDN connection in our shared apartment when we were students. I don’t remember the hardware we used for that but I am sure at that time it was a SUSE operating system. Not sure about the version, but based on this it must have been version 4 something or 5 something. The KDE desktop environment looked liked this in version 1 which was released July 12, 1998.

Selection_001

At the company I worked at that time we had no choice and Windows was the only platform available. When I moved to the next company in 2002 it was the same situation: Having all the infrastructure based on Microsoft products (Active directory, Outlook, Office … ) it was nearly impossible to switch to Linux on the desktop. So my Linux experience focused on server platforms (SUSE as well at that time) and mainly in combination with Oracle databases. It was then when I had my first experiences with Oracle RAC 9.x on SUSE. Believe or not: SUSE already had a rpm which installed all dependencies you need for Oracle in the Oracle 9i ages. As far as I know Oracle came up with that for Oracle Linux years later. I did some experiments with Linux on my personal workstation but because of the non availability of games and everyone used Windows it was not more than playing around.

Things started to change when I moved on the next company in 2007. All was based on Microsoft as well but we had several terminal servers mainly used for VPN. But that opened doors for Linux on the Desktop. As the terminal servers had the complete Microsoft Office package installed I could use them for all the Word, Excel, Outlook stuff but use Linux on my notebook. The only bits I had to figure out were:

  • What software to use for remote connections to Windows machines (rdp)?
  • Can I copy paste between the terminal session and my Linux workstation? (especially for pictures pasted into Outlook)
  • Can I share a common drive for exchanging files between the terminal server and my workstation?

What worked really well was freerdp. Using an alias like this:

alias termserver='nohup xfreerdp --plugin rdpdr --data disk:dwetemp:/home/dwe/Downloads -- --plugin cliprdr -z -x l -g 1280x1024 -u dwe -p Rev0luti0n -k 0x00000807 192.168.22.1 &'

… I could easily connect to the terminal server, use the clipboard to copy/paste into and out of the terminal server session and have a shared drive I could use for exchanging files. For all the other stuff we needed to work on that time (we had site to site VPN connections to our customers) I could use my Linux Desktop. All the Oracle stuff was mainly based on Solaris 9 and 10 so defining aliases for all the connections I required and exchanging my public ssh key brought a great speed up compared to my colleagues that worked with putty on Windows. Later on all the Solaris machines have been replaced with RedHat but that did not change my way of working.

Maybe the biggest issue was to find the distribution that worked for me. I tried openSUSE, Fedora and pure Debian. Nothing made me really happy as I didn’t want to compile software from source or add additional software repositories just to have the basic things working: Music, Video, Flash and so on. For a desktop things have to work out of the box, at least for me. I never really liked Ubuntu but I think this was mainly because of the Unity desktop. I know you can have Ubuntu with various other desktops but somehow I did not consider them. Then I came across Linux Mint (Maya, 2012) and I must say this is one of the best distributions out there. Everything just worked out of the box and the Cinnamon desktop is really great:

Selection_002

I was really happy with that for next one or two years until I re-installed Linux Mint but this time with the KDE desktop, maybe just to see how it evolved over time. There were big discussion when the KDE project switched from version three:

Selection_003

… to version four:
Selection_004

… and I really was interested how the product looked and how it feels. Starting then, KDE is what I really love and use every day. It comes with a powerful editor called Kate, and that is essential for me. Beside vim this is one of the best editors I’ve ever used. But then, the Linux Mint project decided to drop the KDE edition and I again I had to search for a stable KDE distribution. I tried Kubuntu, Manjaro, the KDE spin of Fedora and just a few weeks ago I gave GNOME a try with Fedora Workstation. Finally I switched to KDE neon and I am quite happy with it. This is how my desktop looks today:

Selection_005

Not much on it, you might think, but I feel there is no need for that. On KDE you can do almost anything with the keyboard and all I need is Krunner. ALT-F2 brings it up by default and from there you can start whatever you want, no need to use the mouse for that. Define a bunch of custom keyboard shortcuts, all the aliases required, configure Dolphin the way I like it(especially the places), install terminator and the Desktop is ready. For running Microsoft Office CrossOver Linux works fine for me.

Almost all the applications required are available for Linux today but there are still a few trade-offs. I still need a Windows VM for some stuff (mainly VPN clients to connect to customers which are only available for Windows). But the most important point is that the company you work for has an environment you can work with Linux. As more and more stuff is web based today this is becoming easier and easier but still you can mostly not use Linux in big companies as it is just not supported. Some companies switch to Apple products but I never really considered that for myself. What I recommend from my personal experience: Use something that is either based on Debian (Ubuntu, Mint, …) or rpm based (RedHat, CentOS, SUSE, …). A lot of software is either available as rpm or deb, but nothing else. For a desktop things must just work out of the box.

The good thing with Linux and all the available desktops on top of it: You have plenty of products to choose from. Maybe it takes years until you find the right one for you but I am sure there is something which fits your needs.

 

Cet article My personal journey with Linux on the desktop est apparu en premier sur Blog dbi services.

What you can do when your Veritas cluster shows interfaces as down

Sat, 2018-02-17 07:40

Recently we had the situation that the Veritas cluster (InfoScale 7.3) showed interfaces as down on the two RedHat 7.3 nodes. This e.g. can happen when you change hardware. Although all service groups were up and running this is a situation you usually want to avoid as you never know what happens when the cluster is in such a state. When you have something like this:

[root@xxxxx-node1 ~]$ lltstat -nvv | head
LLT node information:
Node State Link Status Address
  * 0 xxxxx-node1 OPEN
      eth3 UP yy:yy:yy:yy:yy:yy
      eth1 UP xx:xx:xx:xx:xx:xx
      bond0 UP rr:rr:rr:rr:rr:rr
    1 xxxxx-node2 OPEN
      eth3 UP ee:ee:ee:ee:ee:ee
      eth1 DOWN tt:tt:tt:tt:tt:tt
      bond0 DOWN qq:qq:qq:qq:qq:qq

… what can you do?

In our configuration eth1 and eth3 are used for the interconnect and bond0 is the public network. As you can see above the eth1 and bond0 are reported as down for the second node. Of course, the first check you need to do is to check the interface status on the operating system level, but that was fine in our case.

Veritas comes with a tiny little utility (dlpiping) you can use to check connectivity on the Veritas level. Using the information from the lltstat command you can start dlpiping in “send” mode on the first node:

[root@xxxxx-node1 ~]$ /opt/VRTSllt/dlpiping -vs eth1

When that is running (will not detach from the terminal) you should start in “receive” mode on the second node:

[root@xxxxx-node1 ~]$ /opt/VRTSllt/dlpiping -vc eth1 xx:xx:xx:xx:xx:xx
using packet size = 78
dlpiping: sent a request to xx:xx:xx:xx:xx:xx
dlpiping: received a packet from xx:xx:xx:xx:xx:xx

This confirms that connectivity is fine for eth1. When you repeat that for the remaining interfaces (eth3 and bond0) and all is fine then you you can proceed. If not, then you have another issue than what we faced.

The next step is to freeze all your service groups so the cluster will not touch them:

[root@xxxxx-node1 ~]$ haconf -makerw
[root@xxxxx-node1 ~]$ hagrp -freeze SERVICE_GROUP -persistent # do that for all service groups you have defined in the cluster
[root@xxxxx-node1 ~]$ haconf -dump -makerw

Now the magic:

[root@xxxxx-node1 ~]$ hastop -all -force 

Why magic? This command will stop the cluster stack on all nodes BUT it will leave all the resources running. So you can do that without shutting down any user defined cluster services (Oracle databases in our case). Once the stack is down on all the nodes stop gab and ltt on both nodes as well:

[root@xxxxx-node1 ~]$ systemctl stop gab
[root@xxxxx-node1 ~]$ systemctl stop llt

Having stopped llt and gab you just need to start them again in the correct order on both systems:

[root@xxxxx-node1 ~]$ systemctl start llt
[root@xxxxx-node1 ~]$ systemctl start gab

… and after that start the cluster:

[root@xxxxx-node1 ~]$ systemctl start vcs

In our case that was enough to make llt work as expected again and the cluster is fine:

[root@xxxxx-node1 ~]$ gabconfig -a
GAB Port Memberships
===============================================================
Port a gen f44203 membership 01
Port h gen f44204 membership 01
[root@xxxxx-node1 ~]#

[root@xxxxx-node1 ~]$ lltstat -nvv | head
LLT node information:
   Node State Link Status Address
    * 0 xxxxx-node1 OPEN
      eth3 UP yy:yy:yy:yy:yy:yy
      eth1 UP xx:xx:xx:xx:xx:xx
      bond0 UP rr:rr:rr:rr:rr:rr
    1 xxxxx-node2 OPEN
      eth3 UP ee:ee:ee:ee:ee:ee
      eth1 UP qq:qq:qq:qq:qq:qq
      bond0 UP tt:tt:tt:tt:tt:tt 

Hope that helps …

 

Cet article What you can do when your Veritas cluster shows interfaces as down est apparu en premier sur Blog dbi services.

CPUs: Cores versus Threads on an Oracle Server

Sat, 2018-02-17 06:49

When doing a performance review I often do talk with the DBA about the CPU utilization of the server. How reliable is the server CPU utilization with tools like top or the host CPU utilization in the AWR-report? E.g. on an Linux Intel x86-64 server with 8 Cores and 16 logical CPUs (Intel Hyperthreading), what does a utilization of 50% mean?
As I had an ODA X7-M in a test lab available, I thought I’ll do some tests on that.

In my old days at Oracle Support we used a small script to test the CPU single thread performance of an Oracle DB-server:


set echo on
set linesize 120
set timing on time on
with t as ( SELECT rownum FROM dual CONNECT BY LEVEL <= 60 )
select /*+ ALL_ROWS */ count(*) from t,t,t,t,t
/

The SQL just burns a CPU-Core for around 20 seconds. Depending on your CPU single thread performance it may take a bit longer or completes faster.

On the ODA X7-M I have 16 Cores enabled and as hyperthreading enabled I do get 32 CPUs in /proc/cpuinfo:


oracle@dbi-oda01:/home/oracle/cbleile/ [CBL122] grep processor /proc/cpuinfo | wc -l
32
oracle@dbi-oda01:/home/oracle/cbleile/ [CBL122] lscpu | egrep "Thread|Core|Socket|Model name"
Thread(s) per core: 2
Core(s) per socket: 8
Socket(s): 2
Model name: Intel(R) Xeon(R) Gold 6140 CPU @ 2.30GHz

The CPU-speed was at 2.3 GHZ all the time:


[root@dbi-oda01 ~]# for a in `ls -l /sys/devices/system/cpu/cpu*/cpufreq | grep cpufreq | cut -d "/" -f6 | cut -d "u" -f2`; do echo "scale=3;`cat /sys/devices/system/cpu/cpu${a}/cpufreq/cpuinfo_cur_freq`/1000000" | bc; done
2.301
2.301
2.301
2.301
2.301
2.301
2.301
2.301
2.301
2.301
2.301
2.301
2.301
2.301
2.301
2.301
2.301
2.301
2.301
2.301
2.301
2.301
2.301
2.301
2.301
2.301
2.301
2.301
2.301
2.301
2.301
2.301

The CPU is capable of running up to 3.7 GHZ, but that did not happen on my machine.

Running my SQL-script on the ODA X7-M actually took 17.49 seconds:


18:44:00 SQL> with t as ( SELECT rownum FROM dual CONNECT BY LEVEL <= 60 )
18:44:00 2 select /*+ ALL_ROWS */ count(*) from t,t,t,t,t
18:44:00 3 /
 
COUNT(*)
----------
777600000
 
Elapsed: 00:00:17.49

I continued to do the following tests (a job means running above SQL-script):
– 1 Job alone
– 2 Jobs concurrently
– 4 Jobs concurrently
– 8 Jobs concurrently
– 16 Jobs concurrently
– 24 Jobs concurrently
– 32 Jobs concurrently
– 40 Jobs concurrently
– 50 Jobs concurrently
– 60 Jobs concurrently
– 64 Jobs concurrently
– 128 Jobs concurrently

Here the result:


Jobs Min Time Max Time Avg Time Jobs/Cores Jobs/Threads Avg/Single-Time Thread utilization
 
1 17.49 17.49 17.49 0.06 0.03 1.00 1.00
2 17.51 17.58 17.55 0.13 0.06 1.00 1.00
4 17.47 17.86 17.62 0.25 0.13 1.01 0.99
8 17.47 17.66 17.55 0.50 0.25 1.00 1.00
16 17.64 21.65 18.50 1.00 0.50 1.06 0.95
24 18 27.38 24.20 1.50 0.75 1.38 0.72
32 32.65 34.57 33.21 2.00 1.00 1.90 0.53
40 34.76 42.74 40.31 2.50 1.25 2.30 0.54
50 48.26 52.64 51.21 3.13 1.56 2.93 0.53
60 52.4 63.6 60.63 3.75 1.88 3.47 0.54
64 54.2 68.4 64.27 4.00 2.00 3.67 0.54
128 119.49 134.34 129.01 8.00 4.00 7.38 0.54

When running with 16 Jobs top showed a utilization of around 50-52%. However running more than 16 Jobs showed an increase of the average time a job takes. I.e. with 16 Jobs the 16-Cores-Server is already almost fully utilized. Running with 32 Jobs results in an average elapsed time of 1.9 times compared to running 16 jobs (or less) concurrently. As it is 1.9 times and not 2 times I can conclude that there is an advantage of running with hyperthreading enabled, but it’s only around 5-10%.

So when calculating the utilization of your server then base it on the number of cores and not on the number of threads. When looking at your host CPU-utilization in top or in the AWR-report on an hyperthreaded-enabled server then it’s a good idea to multiply the server-utilization by 1.9.

 

Cet article CPUs: Cores versus Threads on an Oracle Server est apparu en premier sur Blog dbi services.

Duplex RMAN backups between disk and tape

Fri, 2018-02-16 09:46

Below a workaround is shown how to “duplex” archivelog backups between disk and tape:

Backup on disk (normal way):

backup device type disk archivelog all;

 

Immediately  backup on tape:

backup device type sbt archivelog until time 'sysdate' not backed up 2 times;

 

This backup command backs up all archivelogs, that are not backed up twice, so all which are backed up with the first command. As in the first backup command a logfile switch is included, between the two backup commands, no logfile switch should occur, otherwise “duplexing” does not work. The until time clause in the second command is added to prevent RMAN from another logfile switch, which would lead to different contents of the backups. And this clause does not filter anything, because sysdate means date and time when issuing the command.

 

Cet article Duplex RMAN backups between disk and tape est apparu en premier sur Blog dbi services.

SQL Server Management Studio 17.4: Vulnerability Assessment

Thu, 2018-02-15 10:01

SQL Server Management Studio is a well know integrated environment used to manage SQL Server infrastructure.
This new version 17.4 can support SQL Server from 2008 up to 2017. It enhances existing features like Showplan, XE Profiler (complete list here) but also add an interesting one which is the Vulnerability Assessment.

Vulnerability Assessment will scan a database in order to help you to track security holes and deviations. Rules to define those deviations are based on Microsoft SQL Server best practices.
Let’s have a look to this new feature.

Once SSMS 17.4 installed, just choose the database you want to scan, right click on the database and select Task, Vulnerability Assessment and Scan For Vulnerabilities…:

VAss1

Select where you want to save the scan report and click OK:

VAss2

Once you clicked OK the scan is starting:

VAss3

At the end of the scan a Vulnerability Assessment Results is displayed:

VAss4

The report is displayed in a Management studio pane with the number of checks that have been run, how many issues have been found with different level of risk from Low to High and provide also some links about SQL Server security best practices.
Review all failed checks to validate that there are really security issues for your environment and go through results.
For each failed issue you will have a description of the issue, the impact, also the rule query applied and a possible remediation script:

VAss5

There is also a possibility to accept results even if there are considered as Potential Risk as a baseline. This will validate results that match the baseline.

VAss6

Once issues are solved or Baseline settled, the Vulnerability Assessment can be run again to see the result of the performed actions:

VAss7

This new feature integrated in Management Studio gives the ability to check that all your databases have a good level of security but also to keep this level.
Great new feature ;-)

 

Cet article SQL Server Management Studio 17.4: Vulnerability Assessment est apparu en premier sur Blog dbi services.

Backup and Restore PostgreSQL with PgBackRest II

Thu, 2018-02-15 09:41

In a precedent blog I shown a basic utilization of PgBackRest which is a tool to backup and restore PostgreSQL databases. In this blog I am going to talk some useful features of this tool. In practical examples we will see some tasks we can do with this tool. Of course the official documentation remains the best source of knowledges.

Encryption
Nowadays encryption of backups is very critical and is mandatory for many companies. PgBackRest allows us to encrypt the repository where backups are stored. A passphrase is used to encrypt/decrypt files of the repository. As you may already know, it is recommended to use a strong passphrase. In the following demonstration we use the openssl to generate a passphrase.

[postgres@pgserver ~]$ openssl rand -base64 48
FhXg7oW2pZb9UICZ4iYZPn3X4I6fF0ni7IL6QjaB1IL8qz4LIrP+GW+XqCZqIi3w
[postgres@pgserver ~]$

Once the passphrase generated, we can update the PgBackRest configuration file with
2 options: repo-cipher-pass and repo-cipher-type

[postgres@pgserver clustpgserver]$ cat /etc/pgbackrest.conf
[global] repo-path=/var/lib/pgbackrest
repo-cipher-pass=FhXg7oW2pZb9UICZ4iYZPn3X4I6fF0ni7IL6QjaB1IL8qz4LIrP+GW+XqCZqIi3w
repo-cipher-type=aes-256-cbc
[clustpgserver] db-path=/var/lib/pgsql/10/data
retention-full=2

The next step is to create the stanza

[postgres@pgserver ~]$ pgbackrest --stanza=clustpgserver --log-level-console=info --db-port=5435 stanza-create
2018-02-13 13:54:50.447 P00 INFO: stanza-create command begin 1.28: --db1-path=/var/lib/pgsql/10/data --db1-port=5435 --log-level-console=info --repo-cipher-pass= --repo-cipher-type=aes-256-cbc --repo-path=/var/lib/pgbackrest --stanza=clustpgserver
2018-02-13 13:55:04.520 P00 INFO: stanza-create command end: completed successfully
[postgres@pgserver ~]$

As we can see the system automatically detect that the repository is encrypted and then will rewrite the command including the –repo-cipher-pass and the –repo-cipher-type options. After the creation of the stanza we can check the status of our stanza

[postgres@pgserver ~]$ pgbackrest --stanza=clustpgserver --log-level-console=info --db-port=5435 check
2018-02-13 13:56:08.999 P00 INFO: check command begin 1.28: --db1-path=/var/lib/pgsql/10/data --db1-port=5435 --log-level-console=info --repo-cipher-pass= --repo-cipher-type=aes-256-cbc --repo-path=/var/lib/pgbackrest --stanza=clustpgserver
2018-02-13 13:57:08.026 P00 INFO: WAL segment 00000002000000000000004C successfully stored in the archive at '/var/lib/pgbackrest/archive/clustpgserver/10-1/0000000200000000/00000002000000000000004C-f5ced60cd351d74a91c9ce2e913b761144165e28.gz'
2018-02-13 13:57:08.030 P00 INFO: check command end: completed successfully

Everything seems fine, so let’s run a backup. Note that outputs are truncated

[postgres@pgserver ~]$ pgbackrest --stanza=clustpgserver --log-level-console=info --db-port=5435 backup
2018-02-13 14:01:40.012 P00 INFO: backup command begin 1.28: --db1-path=/var/lib/pgsql/10/data --db1-port=5435 --log-level-console=info --repo-cipher-pass= --repo-cipher-type=aes-256-cbc --repo-path=/var/lib/pgbackrest --retention-full=2 --stanza=clustpgserver
WARN: no prior backup exists, incr backup has been changed to full
2018-02-13 14:01:54.118 P00 INFO: execute non-exclusive pg_start_backup() with label "pgBackRest backup started at 2018-02-13 14:01:52": backup begins after the next regular checkpoint completes
...
type=aes-256-cbc --repo-path=/var/lib/pgbackrest --retention-archive=2 --retention-full=2 --stanza=clustpgserver
2018-02-13 14:35:08.281 P00 INFO: full backup total < 2 - using oldest full backup for 10-1 archive retention
2018-02-13 14:35:08.801 P00 INFO: expire command end: completed successfully
[postgres@pgserver ~]$

In a non-encrypted repository, file backup.info can be read. Now with encryption the if we try to read the file backup.info in the repository, we cannot.

[postgres@pgserver clustpgserver]$ less /var/lib/pgbackrest/backup/clustpgserver/backup.info
"/var/lib/pgbackrest/backup/clustpgserver/backup.info" may be a binary file. See it anyway?

And using the command strings, we can see that the file is encrypted.

[postgres@pgserver clustpgserver]$ strings /var/lib/pgbackrest/backup/clustpgserver/backup.info
Salted__Fx
.;Ru
cz4@
do:t
\pi3"E
VUSO
}a.R*
Wx5M
,?,W
3CXWB
[postgres@pgserver clustpgserver]$

From now, backups cannot be used unless the password is provided.

Restore in another location
PgBackRest allows to restore to another location. This can be useful if we want to duplicate our cluster on the same server or to another server. In the following demonstration, let’s duplicate on the same server.
The data directory of the source cluster is /var/lib/pgsql/10/data

postgres=# show data_directory;
data_directory
------------------------
/var/lib/pgsql/10/data
(1 row)
postgres=#

To duplicate to a new data directory /u01/devdata for example, the option –db-path is used

[postgres@pgserver log]$ pgbackrest --stanza=clustpgserver --log-level-console=info --db-path=/u01/devdata restore


2018-02-14 09:40:05.755 P01 INFO: restore file /u01/devdata/base/1/13657 (0B, 100%)
2018-02-14 09:40:05.773 P01 INFO: restore file /u01/devdata/base/1/13652 (0B, 100%)
2018-02-14 09:40:05.811 P01 INFO: restore file /u01/devdata/base/1/13647 (0B, 100%)
2018-02-14 09:40:05.983 P01 INFO: restore file /u01/devdata/base/1/13642 (0B, 100%)
2018-02-14 09:40:06.067 P00 INFO: write /u01/devdata/recovery.conf
2018-02-14 09:40:14.403 P00 INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)
2018-02-14 09:40:30.187 P00 INFO: restore command end: completed successfully

After the duplicate don’t forget to change the port (as we are in the same server) and then start your new cluster

postgres=# show data_directory ;
data_directory
----------------
/u01/devdata
(1 row)
postgres=#

Restore specific databases
With PgBackRest, we can restore specific user databases. Note that built-in databases (template0, template1 and postgres) are always restored.
Let’s show an example. In our source cluster we actually have two databases test and sandbox.

sandbox=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
sandbox | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
test | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(5 rows)

In sandbox we have a table mytab with 2 rows

sandbox=# \c sandbox
You are now connected to database "sandbox" as user "postgres".
sandbox=# table mytab;
id
----
1
2
(2 rows)

Now let’s restore the cluster but only with test database, the option –db-include will be used.

[postgres@pgserver log]$ pgbackrest --stanza=clustpgserver --log-level-console=info --db-path=/u01/devdata --db-include=test restore
2018-02-14 10:11:00.948 P00 INFO: restore command begin 1.28: --db-include=test=1 --db1-path=/u01/devdata --log-level-console=info --repo-cipher-pass= --repo-cipher-type=aes-256-cbc --repo-path=/var/lib/pgbackrest --stanza=clustpgserver
2018-02-14 10:11:05.137 P00 INFO: restore backup set 20180214-095439F_20180214-100446I
2018-02-14 10:11:25.110 P00 INFO: remap $PGDATA directory to /u01/devdata
...

After the restore completed, let’s start the new cluster and let’s verify present databases.

[postgres@pgserver devdata]$ psql -p 5436
psql (10.1)
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
sandbox | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
test | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(5 rows)

What!! the sandbox is still present despite the use of option –include-db=test. But if we try to connect to sandbox database. We get an error.

postgres=# \c sandbox
FATAL: relation mapping file "base/24581/pg_filenode.map" contains invalid data
Previous connection kept
postgres=#

And if we compare at OS level the size of files of the database at the source cluster and at the target

[postgres@pgserver log]$ du -sh /var/lib/pgsql/10/data/base/24581
7.8M /var/lib/pgsql/10/data/base/24581
[postgres@pgserver log]$ du -sh /u01/devdata/base/24581
16K /u01/devdata/base/24581
[postgres@pgserver log]$

We can see that at the target cluster, sandbox uses less disk space during the selective restore than it would have if the entire database had been restored. To finish the selective restore, we have to manually drop the sandbox database. Indeed PgBackRest cannot automatically drop the database because the cluster is not accessible until the recovery process finishes.

postgres=# drop database sandbox;
DROP DATABASE
postgres=#

Automatic cleanup of expired backups
Another nice feature of PgBackRest is that expired backups are automatically removed.
If we check our pgbackrest.conf file, we see that the retention-full is set to 2. This means that 2 full backups will be maintained. So if we do a third full backup, the first full backup and all corresponding incremental and differential backups will be expired and removed

[postgres@pgserver log]$ cat /etc/pgbackrest.conf
[global] repo-path=/var/lib/pgbackrest
repo-cipher-pass=FhXg7oW2pZb9UICZ4iYZPn3X4I6fF0ni7IL6QjaB1IL8qz4LIrP+GW+XqCZqIi3w
repo-cipher-type=aes-256-cbc
[clustpgserver] db-path=/var/lib/pgsql/10/data
retention-full=2
[postgres@pgserver log]$

Let’s do a quick demonstration. Actually we have 2 full backups

[postgres@pgserver log]$ pgbackrest --stanza=clustpgserver info
stanza: clustpgserver
status: ok
db (current)
wal archive min/max (10-1): 00000002000000000000004E / 000000020000000000000056
full backup: 20180213-140152F
timestamp start/stop: 2018-02-13 14:01:52 / 2018-02-13 14:32:00
wal start/stop: 00000002000000000000004E / 00000002000000000000004E
database size: 577MB, backup size: 577MB
repository size: 28.8MB, repository backup size: 28.8MB
incr backup: 20180213-140152F_20180213-152509I
timestamp start/stop: 2018-02-14 09:31:03 / 2018-02-14 09:33:17
wal start/stop: 000000020000000000000052 / 000000020000000000000052
database size: 30.7MB, backup size: 285.3KB
repository size: 3.6MB, repository backup size: 24.3KB
backup reference list: 20180213-140152F
full backup: 20180214-095439F
timestamp start/stop: 2018-02-14 09:54:39 / 2018-02-14 09:58:53
wal start/stop: 000000020000000000000054 / 000000020000000000000054
database size: 30.7MB, backup size: 30.7MB
repository size: 3.6MB, repository backup size: 3.6MB
incr backup: 20180214-095439F_20180214-100446I
timestamp start/stop: 2018-02-14 10:04:46 / 2018-02-14 10:07:43
wal start/stop: 000000020000000000000056 / 000000020000000000000056
database size: 38.3MB, backup size: 7.6MB
repository size: 4.5MB, repository backup size: 928.5KB
backup reference list: 20180214-095439F
[postgres@pgserver log]$

And we can confirm by executing a simple ls in the repository where backups are stored

[postgres@pgserver clustpgserver]$ ls -ld *
drwxr-x---. 3 postgres postgres 69 Feb 13 14:32 20180213-140152F
drwxr-x---. 3 postgres postgres 69 Feb 14 09:33 20180213-140152F_20180213-152509I
drwxr-x---. 3 postgres postgres 69 Feb 14 09:59 20180214-095439F
drwxr-x---. 3 postgres postgres 69 Feb 14 10:07 20180214-095439F_20180214-100446I
drwxr-x---. 3 postgres postgres 17 Feb 13 14:33 backup.history
-rw-r-----. 1 postgres postgres 2992 Feb 14 10:08 backup.info
-rw-r-----. 1 postgres postgres 2992 Feb 14 10:08 backup.info.copy
lrwxrwxrwx. 1 postgres postgres 33 Feb 14 10:08 latest -> 20180214-095439F_20180214-100446I
[postgres@pgserver clustpgserver]$ ls -ld
drwxr-x---. 7 postgres postgres 4096 Feb 14 10:08 .
[postgres@pgserver clustpgserver]$ ls -ld *

Now let’s do a third full backup

[postgres@pgserver clustpgserver]$ pgbackrest --stanza=clustpgserver --log-level-console=info --db-port=5435 --type=full backup
2018-02-14 10:55:52.250 P00 INFO: backup command begin 1.28: --db1-path=/var/lib/pgsql/10/data --db1-port=5435 --log-level-console=info --repo-cipher-pass= --repo-cipher-type=aes-256-cbc --repo-path=/var/lib/pgbackrest --retention-full=2 --stanza=clustpgserver --type=full

2018-02-14 11:19:02.001 P00 INFO: backup command end: completed successfully
2018-02-14 11:19:02.107 P00 INFO: expire command begin 1.28: --log-level-console=info --repo-cipher-pass= --repo-cipher-type=aes-256-cbc --repo-path=/var/lib/pgbackrest --retention-archive=2 --retention-full=2 --stanza=clustpgserver
2018-02-14 11:19:02.928 P00 INFO: expire full backup set: 20180213-140152F, 20180213-140152F_20180213-152509I
2018-02-14 11:22:08.759 P00 INFO: remove expired backup 20180213-140152F_20180213-152509I
2018-02-14 11:22:09.000 P00 INFO: remove expired backup 20180213-140152F

2018-02-14 11:22:49.387 P00 INFO: expire command end: completed successfully
[postgres@pgserver clustpgserver]$

We can see that at the end of backups, some old backups are expired and removed. We can also confirm this by listing files in the repository

[postgres@pgserver clustpgserver]$ ls -ld *
drwxr-x---. 3 postgres postgres 69 Feb 14 09:59 20180214-095439F
drwxr-x---. 3 postgres postgres 69 Feb 14 10:07 20180214-095439F_20180214-100446I
drwxr-x---. 3 postgres postgres 69 Feb 14 11:13 20180214-105603F
drwxr-x---. 3 postgres postgres 17 Feb 13 14:33 backup.history
-rw-r-----. 1 postgres postgres 2320 Feb 14 11:19 backup.info
-rw-r-----. 1 postgres postgres 2320 Feb 14 11:20 backup.info.copy
lrwxrwxrwx. 1 postgres postgres 16 Feb 14 11:14 latest -> 20180214-105603F
[postgres@pgserver clustpgserver]$

Point-in-Time Recovery
PgBackRest can also do a point-in-time recovery. Let’s drop table article in the database test

test=# table article;
nom
---------
printer
(1 row)
.
test=# select now();
now
-------------------------------
2018-02-14 11:39:28.024378+01
(1 row)
.
test=# drop table article;
DROP TABLE
.
test=# table article;
ERROR: relation "article" does not exist
LINE 1: table article;
^
test=#

And now let’s restore until just before we drop the table let’s say 2018-02-14 11:39:28.
But as we have many backup sets we have to restore from a backup done before the table was dropped.
If we check our backups, we have to restore from the full backup: 20180214-105603F
which was taken before table article was dropped.

[postgres@pgserver devdata]$ pgbackrest --stanza=clustpgserver --log-level-console=info info
stanza: clustpgserver
status: ok
db (current)
wal archive min/max (10-1): 000000020000000000000054 / 00000002000000000000005A
full backup: 20180214-095439F
timestamp start/stop: 2018-02-14 09:54:39 / 2018-02-14 09:58:53
wal start/stop: 000000020000000000000054 / 000000020000000000000054
database size: 30.7MB, backup size: 30.7MB
repository size: 3.6MB, repository backup size: 3.6MB
incr backup: 20180214-095439F_20180214-100446I
timestamp start/stop: 2018-02-14 10:04:46 / 2018-02-14 10:07:43
wal start/stop: 000000020000000000000056 / 000000020000000000000056
database size: 38.3MB, backup size: 7.6MB
repository size: 4.5MB, repository backup size: 928.5KB
backup reference list: 20180214-095439F
full backup: 20180214-105603F
timestamp start/stop: 2018-02-14 10:56:03 / 2018-02-14 11:12:26

wal start/stop: 000000020000000000000058 / 000000020000000000000058
database size: 38.3MB, backup size: 38.3MB
repository size: 4.5MB, repository backup size: 4.5MB
incr backup: 20180214-105603F_20180214-121044I
timestamp start/stop: 2018-02-14 12:10:44 / 2018-02-14 12:15:14
wal start/stop: 00000002000000000000005A / 00000002000000000000005A
database size: 38.3MB, backup size: 1.1MB
repository size: 4.5MB, repository backup size: 140.8KB
backup reference list: 20180214-105603F
[postgres@pgserver devdata]$

For the restore we use the option –set which allows us to specify the backup set we want to use for the restore. Note also the use of –type=time and –target

[postgres@pgserver log]$ pgbackrest --stanza=clustpgserver --log-level-console=info --type=time "--target=2018-02-14 11:39:28.024378+01" --db-path=/u01/devdata --set=20180214-105603F restore
2018-02-14 13:36:50.848 P00 INFO: restore command begin 1.28: --db1-path=/u01/devdata --log-level-console=info --repo-cipher-pass= --repo-cipher-type=aes-256-cbc --repo-path=/var/lib/pgbackrest --set=20180214-105603F --stanza=clustpgserver "--target=2018-02-14 11:39:28.024378+01" --type=time
2018-02-14 13:37:03.406 P00 INFO: restore backup set 20180214-105603F
...

At the end of the restore let’s see the contents of the recovery.done file

[postgres@pgserver devdata]$ cat recovery.conf
restore_command = '/usr/bin/pgbackrest --db1-path=/u01/devdata --log-level-console=info --stanza=clustpgserver archive-get %f "%p"'
recovery_target_time = '2018-02-14 11:39

If we start our new cluster, we can see in log files that PITR is starting
2018-02-14 13:54:23.824 CET [10049] LOG: starting point-in-time recovery to 2018-02-14 11:39:28.024378+01
And once the recovery finished, we can verify that the table article is present

postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# \d article
Table "public.article"
Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
nom | character varying(50) | | |
.
test=# table article;
nom
---------
printer
(1 row)
test=#

Conclusion:
In this blog I talked about some features about PgBackRest. But as already specified, there are many, many other options with this wonderful tool. The official documentation can give more information. In future blogs we will explore more with advanced configuration.

 

Cet article Backup and Restore PostgreSQL with PgBackRest II est apparu en premier sur Blog dbi services.

(DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS_LIST=(FAILOVER=YES)(LOAD_BALANCE=NO)

Thu, 2018-02-15 00:02

Do you have complex connection strings with DESCRIPTION_LIST, DESCRIPTION, ADDRESS_LIST, ADDRESS and a nice combination of FAILOVER and LOAD_BALANCE? You probably checked the documentation, telling you that FAILOVER=YES is the default at all levels, but LOAD_BALANCE=YES is the default only for DESCRIPTION_LIST. But when disaster recovery and availability is concerned, the documentation is not sufficient. I want to test it. And here is how I do it.

I don’t want to test it with the real configuration and stop the different instances. And I don’t need to. My way to test an address list is to define a tnsnames.ora with the connection string, such as the following:

NET_SERVICE_NAME=
NET_SERVICE_NAME=
(DESCRIPTION_LIST=
(DESCRIPTION=
(CONNECT_DATA=(SERVICE_NAME=pdb1))
(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=101))
(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=102))
)
(DESCRIPTION=
(CONNECT_DATA=(SERVICE_NAME=pdb1))
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=201))
(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=202))
)
)
)

I used localhost because I know it’s there and I don’t want to wait for the TCP timeout. But I use fake ports, which do not exist. So finally, a connection will never be established but I will be able to see all that are tried. I check them with strace on the connect() system call, with the following script:


for i in {1..10}
do
TNS_ADMIN=/tmp strace -T -e trace=connect sqlplus -s -L sys/oracle@NET_SERVICE_NAME as sysdba <<< "" 2>&1 | awk '
/sa_family=AF_INET, sin_port=htons/{
gsub(/[()]/," ") ; printf "%s ",$5
}
END{
print ""
}
'
done | sort | uniq

So, I used meaningful numbers for my fake ports: 101 and 102 for the addresses in the first description of the description list, and 201 and 202 for the address list in the second description. The awk script shows the sequence that was tried. And, because of the random round robin, I run them in a loop several times to see all patterns, aggregated by sort|uniq

So here is the result from the connection string above using the defaults for load balancing and failover:

101 102 201 202
201 202 101 102

The sequence within the address list is always in order (101,102 and 201,202) because LOAD_BALANCE=NO is the default there. But I have two combinations for the descriptions because LOAD_BALANCE=YES is the default in DESCRIPTION_LIST. Finally, all adresses are tried because FAILOVER=YES is the default at all levels.

LOAD_BALANCE

If I define LOAD_BALANCE at all levels, such as:

NET_SERVICE_NAME=
(DESCRIPTION_LIST=(FAILOVER=YES)(LOAD_BALANCE=YES)
(DESCRIPTION=(FAILOVER=YES)(LOAD_BALANCE=YES)
(CONNECT_DATA=(SERVICE_NAME=pdb1))
(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=101))
(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=102))
)
(DESCRIPTION=(FAILOVER=YES)
(CONNECT_DATA=(SERVICE_NAME=pdb1))
(ADDRESS_LIST=(FAILOVER=YES)(LOAD_BALANCE=YES)
(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=201))
(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=202))
)
)
)

The result shows that all combinations can be tried in any order:

101 102 201 202
101 102 202 201
102 101 201 202
102 101 202 201
201 202 101 102
201 202 102 101
202 201 101 102
202 201 102 101

By running it in a large loop you will confirm that any address will be tried at most once.

FAILOVER

Now, If I set FAILOVER=NO within the first description:

NET_SERVICE_NAME=
(DESCRIPTION_LIST=(FAILOVER=YES)(LOAD_BALANCE= NO)
(DESCRIPTION=(FAILOVER= NO)(LOAD_BALANCE=YES)
(CONNECT_DATA=(SERVICE_NAME=pdb1))
(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=101))
(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=102))
)
(DESCRIPTION=(LOAD_BALANCE=NO )
(CONNECT_DATA=(SERVICE_NAME=pdb1))
(ADDRESS_LIST=(FAILOVER=YES)(LOAD_BALANCE=YES)
(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=201))
(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=202))
)
)
)

the first attempt can be 101 or 102 (because of LOAD_BALANCING) but only one will be tried in this address list, because of no failover. Then, the second description is attempted (because FAILOVER=YES at description list level) and with all addresses there (because of LOAD_BALANCING=YES). The result of all possible combinations is:


101 201 202
102 201 202
102 202 201

So here it is. You can test any complex connection description to check what will be the possible connections and in which order they will be tried. From this, you can infer what will happen with a real configuration: the wait for TCP timeout for addresses tested on hosts that are not up, and the load balancing given be the different possible combinations.

 

Cet article (DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS_LIST=(FAILOVER=YES)(LOAD_BALANCE=NO) est apparu en premier sur Blog dbi services.

Full page logging in Postgres and Oracle

Wed, 2018-02-14 14:13

In my opinion, the volume of logging (aka redo log, aka xlog, aka WAL) is the most important factor for OLTP performance, availability and scalability, for several reasons:

  • This is the only structure where disk latency is a mandatory component of response time
  • This is a big part of the total volume of backups
  • This is sequential by nature, and very difficult to scale by parallelizing

In this post, I look at the volume of logging generated by some DML in Postgres and Oracle. I know Oracle quite well and just start to look at Postgres. The comparison here is not a contest but a way to better understand. For example, the default behavior of Postgres, with full_page_writes=on, is very similar to Oracle ‘begin backup’ mode. The comparison makes no sense for most of Postgres DBAs, but probably helps Oracle DBAs to understand it.

Measure WAL segment writes

Here is how I measured the volume of transaction log written: start the Postgres server with ‘strace -f’ and parse with ‘awk’ the open(), write() and close() calls:

sudo su postgres <<'END'
export PGDATA=/u01/pgdata
/usr/pgsql-10/bin/pg_ctl stop
strace -e trace=open,close,write,recvfrom -f /usr/pgsql-10/bin/pg_ctl start 2>&1 | awk '
/^[^[]/{
$0="[pid MAIN] "$0
}
/strace: Process [0-9][0-9]* attached/{
sub(/^.*strace: /,"strace: ") ; "ps -o cmd -hp " $3 |& getline proc[$3"]"] ; print "" ; print $0,proc[$3"]"] }
/open[(].*pg_wal[/].* = [0-9]*$/{
z=$0 ; gsub(qq," ") ; fd_wal[$2 $NF]=$4
}
/checkpoint;/{
total_written_wal=0
}
/write[(]/{
#pid=$2 ; sub("]","",$2) ; "ps -o cmd -hp " p |& getline proc[p"]"] z=$0 ; gsub("[(,]"," ") ; if ( fd_wal[$2 $4]>0 ) { written_wal[$2 $4]=written_wal[$2 $4]+$NF ; total_written_wal=total_written_wal+$NF } next
}
/close[(]/{
pid=$2 ; sub("[^0-9]","",pid) ;
z=$0 ; gsub("[()]"," ") ; if ( ( fd_wal[$2 $4]!="" ) && ( written_wal[$2 $4] > 0 ) ) {
printf " ( written %d bytes to %s -> total WAL segments: %.2f MB ) cmd=%s\n",written_wal[$2 $4],fd_wal[$2 $4],total_written_wal/1024/1024 , proc[$2] ; fd_wal[$2 $4]=""
} next
}
' qq='"'
END

Do not do that in production. This is experimentation in a lab. Do not attach strace to a critical process in production.

There’s probably an easier way to get the same information, maybe with postgres activity statistics, or through a size counting archive_command, so please don’t hesitate to comment. Anyway, from the ‘write()’ calls I am sure that I’m counting exactly what I want: the volume of logging written to disk. As an Oracle DBA used to LogWriter and its slave threads managing all writes, I started to trace only the WAL writer process but quickly realized that part ot the logging is directly written by my server process.

Postgres: insert

I create a table with some numbers and a 100 bytes character string.

create table demo as select generate_series a,generate_series b,generate_series c,generate_series d,generate_series e,generate_series f,lpad('x',100,'x') g from generate_series(0,0);

The first operation I test is the insert of 1 million rows.

insert into demo select generate_series a,generate_series b,generate_series c,generate_series d,generate_series e,generate_series f,lpad('x',100,'x') g from generate_series(1,1000000);
( written 4349952 bytes to pg_wal/000000010000000A0000005F -> total WAL segments: 4.16 MB ) cmd=postgres: demo demo 192.168.56.122(38013)
( written 8192 bytes to pg_wal/000000010000000A0000005F -> total WAL segments: 9.00 MB ) cmd=postgres: wal writer process
( written 17735680 bytes to pg_wal/000000010000000A00000060 -> total WAL segments: 20.07 MB ) cmd=postgres: demo demo 192.168.56.122(38013)
( written 3309568 bytes to pg_wal/000000010000000A00000060 -> total WAL segments: 31.40 MB ) cmd=postgres: wal writer process
( written 33783808 bytes to pg_wal/000000010000000A00000061 -> total WAL segments: 36.03 MB ) cmd=postgres: demo demo 192.168.56.122(38013)
( written 3997696 bytes to pg_wal/000000010000000A00000061 -> total WAL segments: 39.80 MB ) cmd=postgres: wal writer process
( written 49676288 bytes to pg_wal/000000010000000A00000062 -> total WAL segments: 51.19 MB ) cmd=postgres: demo demo 192.168.56.122(38013)
( written 65273856 bytes to pg_wal/000000010000000A00000063 -> total WAL segments: 66.06 MB ) cmd=postgres: demo demo 192.168.56.122(38013)
( written 79364096 bytes to pg_wal/000000010000000A00000064 -> total WAL segments: 82.04 MB ) cmd=postgres: demo demo 192.168.56.122(38013)
( written 6660096 bytes to pg_wal/000000010000000A00000064 -> total WAL segments: 82.39 MB ) cmd=postgres: wal writer process
( written 88285184 bytes to pg_wal/000000010000000A00000065 -> total WAL segments: 98.02 MB ) cmd=postgres: demo demo 192.168.56.122(38013)
( written 14491648 bytes to pg_wal/000000010000000A00000065 -> total WAL segments: 106.82 MB ) cmd=postgres: wal writer process
( written 101703680 bytes to pg_wal/000000010000000A00000066 -> total WAL segments: 113.99 MB ) cmd=postgres: demo demo 192.168.56.122(38013)
( written 17825792 bytes to pg_wal/000000010000000A00000066 -> total WAL segments: 117.19 MB ) cmd=postgres: wal writer process
( written 115769344 bytes to pg_wal/000000010000000A00000067 -> total WAL segments: 128.20 MB ) cmd=postgres: demo demo 192.168.56.122(38013)
( written 18661376 bytes to pg_wal/000000010000000A00000067 -> total WAL segments: 135.09 MB ) cmd=postgres: wal writer process
( written 19824640 bytes to pg_wal/000000010000000A00000068 -> total WAL segments: 144.17 MB ) cmd=postgres: wal writer process
( written 131350528 bytes to pg_wal/000000010000000A00000068 -> total WAL segments: 148.16 MB ) cmd=postgres: demo demo 192.168.56.122(38013)
( written 27435008 bytes to pg_wal/000000010000000A00000069 -> total WAL segments: 159.80 MB ) cmd=postgres: wal writer process
( written 140132352 bytes to pg_wal/000000010000000A00000069 -> total WAL segments: 159.80 MB ) cmd=postgres: demo demo 192.168.56.122(38013)
INSERT 0 1000000

You can see that my ‘strace|awk’ script is running in the background and has counted about 160 MB of logging, partially from the ‘postgres: wal writer process’ and partly from ‘postgres: demo demo 192.168.56.122(38013)’ serving my connection.

The relation size as stored on disk is about 150 MB;

analyze demo;
ANALYZE
select relname, relnamespace, reltype, reloftype, relowner, relam, relfilenode, reltablespace, relpages, reltuples, relallvisible, relpages*8/1024 MB from pg_class where relname = 'demo';
relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relallvisible | mb
---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+-----
demo | 2200 | 25157 | 0 | 16385 | 0 | 25155 | 0 | 19231 | 1e+06 | 0 | 150
(1 row)

This makes sense. An insert has to write all new data into the log in order to be able to recover the pages until they are checkpointed.

Note that I have no index on this table for this test.

Postgres: update

I’m now updating one column for all rows.

update demo set b=b+1;
( written 150528000 bytes to pg_wal/000000010000000A0000006A -> total WAL segments: 4.01 MB ) cmd=postgres: demo demo 192.168.56.122(38013)
( written 162693120 bytes to pg_wal/000000010000000A0000006B -> total WAL segments: 17.84 MB ) cmd=postgres: demo demo 192.168.56.122(38013)
( written 29769728 bytes to pg_wal/000000010000000A0000006B -> total WAL segments: 28.44 MB ) cmd=postgres: wal writer process
...
( written 84287488 bytes to pg_wal/000000010000000A00000081 -> total WAL segments: 343.65 MB ) cmd=postgres: wal writer process
( written 453705728 bytes to pg_wal/000000010000000A00000082 -> total WAL segments: 347.36 MB ) cmd=postgres: demo demo 192.168.56.122(38013)
UPDATE 1000001

I touched only a small part of the volume in bytes, but I touched all rows and all pages. An, even if only a few bytes are modified, Postgres logs the whole page to protect from fractured blocks in case of crash (pages partially written). So that’s about 150 MB. But postgres do not update rows in-place. The whole row is inserted in its new version, which means the whole volume again, which is another 150 MB. If we look at the size of the table, we can see 300MB of pages:

analyze demo;
ANALYZE
select relname, relnamespace, reltype, reloftype, relowner, relam, relfilenode, reltablespace, relpages, reltuples, relallvisible, relpages*8/1024 MB from pg_class where relname = 'demo';
relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relallvisible | mb
---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-------------+---------------+-----
demo | 2200 | 25157 | 0 | 16385 | 0 | 25155 | 0 | 38462 | 1.21882e+06 | 0 | 300
(1 row)

So this update has generated even more logging: 347 MB.

Postgres: sparse update

Now updating only 1 row out of ten, still one column only:

update demo set b=b+1 where mod(a,10)=1;
( written 89923584 bytes to pg_wal/000000010000000A00000083 -> total WAL segments: 13.88 MB ) cmd=postgres: wal writer process
( written 469123072 bytes to pg_wal/000000010000000A00000084 -> total WAL segments: 22.98 MB ) cmd=postgres: demo demo 192.168.56.122(38013)
...
( written 563576832 bytes to pg_wal/000000010000000A0000008D -> total WAL segments: 151.07 MB ) cmd=postgres: demo demo 192.168.56.122(38013)
( written 130940928 bytes to pg_wal/000000010000000A0000008D -> total WAL segments: 151.27 MB ) cmd=postgres: wal writer process
UPDATE 100000
analyze demo;

So, 10% of the rows had to be copied to their new version, which brings the table size to additional 15 MB.

analyze demo;
ANALYZE
select relname, relnamespace, reltype, reloftype, relowner, relam, relfilenode, reltablespace, relpages, reltuples, relallvisible, relpages*8/1024 MB from pg_class where relname = 'demo';
relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relallvisible | mb
---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-------------+---------------+-----
demo | 2200 | 25157 | 0 | 16385 | 0 | 25155 | 0 | 40385 | 1.07267e+06 | 0 | 315
(1 row)

For these additional 15 MB, half of the table pages had to be modified (the current version having to point to the new version), and the logging generated was 150 MB. Because of MVCC at tuple level, doing something similar to ‘chained rows’ and ‘row migration’ for all updates, and because of full page logging, even sparse updates generate a lot log writes.

Postgres: delete

Here is a delete of those million rows:

delete from demo;
( written 576364544 bytes to pg_wal/000000010000000A0000008E -> total WAL segments: 6.44 MB ) cmd=postgres: demo demo 192.168.56.122(38013)
( written 134930432 bytes to pg_wal/000000010000000A0000008E -> total WAL segments: 6.73 MB ) cmd=postgres: wal writer process
( written 589225984 bytes to pg_wal/000000010000000A0000008F -> total WAL segments: 18.70 MB ) cmd=postgres: demo demo 192.168.56.122(38013)
...
( written 162054144 bytes to pg_wal/000000010000000A00000099 -> total WAL segments: 184.70 MB ) cmd=postgres: wal writer process
( written 740352000 bytes to pg_wal/000000010000000A0000009A -> total WAL segments: 189.80 MB ) cmd=postgres: demo demo 192.168.56.122(38013)
DELETE 1000001
( written 163217408 bytes to pg_wal/000000010000000A0000009A -> total WAL segments: 196.22 MB ) cmd=postgres: wal writer process

Marking tuples as deleted does not increase the table:

analyze demo;
ANALYZE
select relname, relnamespace, reltype, reloftype, relowner, relam, relfilenode, reltablespace, relpages, reltuples, relallvisible, relpages*8/1024 MB from pg_class where relname = 'demo';
relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relallvisible | mb
---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+-----
demo | 2200 | 25157 | 0 | 16385 | 0 | 25155 | 0 | 40385 | 275837 | 0 | 315
(1 row)

But all current tuples have to be marked as deleted and not visible once the transaction is committed. This touches all pages for the current version, which is more than 150 MB of logging here.

Postgres: vacuum

After two updates and a delete, I have old tuples in this table. It seems that VACUUM does not generate any logging:

vacuum demo;
( written 762445824 bytes to pg_wal/000000010000000A0000009B -> total WAL segments: 14.67 MB ) cmd=postgres: demo demo 192.168.56.122(38013)
VACUUM

My guess (but remember that I am a newbie in Postgres) is that in case of a crash occurring before the next checkpoint we will just have to vacuum again. But this is not what was answered in the postgres-general list a few years ago.

Note that full page logging is not necessary for all changes, but only for the first change after the page was read from disk after a checkpoint. This is sufficient to cover future writes failures because recovery will start from there. Once we have full page logged, change vector is sufficient for further recovery. However, I had the same amount of WAL, 15 MB, when vacuuming after a checkpoint.

Oracle: insert

Let’s do some similar things in Oracle, which MVCC implementation is completely different: at block level, with undo logging.

SQL> create table demo as select rownum a,rownum b,rownum c,rownum d,rownum e,rownum f,lpad('x',100,'x') g from xmltable('0 to 0');
Table created.

I have exposed in a previous post how I get the delta values from V$MYSTAT join V$STATNAME using (STATISTIC#) for ‘redo size’, so no need to strace here. But we can see the same result by measuring the writes to redo log groups (do not double count the multiplexed members).

SQL> insert into demo select rownum a,rownum b,rownum c,rownum d,rownum e,rownum f,lpad('x',100,'x') g from xmltable('1 to 1000000');
1000001 rows inserted.
 
SQL> @ _mystat_diff.sql
 
db block changes redo size undo change vector size redo entries
---------------- ---------------- ----------------------- ----------------
141,342 155,218,876 4,380,448 104,411

This is about 150MB, which is the volume of the table:

SQL> exec dbms_stats.gather_table_stats(user,'DEMO');
PL/SQL procedure successfully completed.
SQL> select table_name,num_rows,blocks,(blocks*block_size/1024/1024) MB, avg_row_len from user_tables join dba_tablespaces using(tablespace_name) where table_name='DEMO';
 
TABLE_NAME NUM_ROWS BLOCKS MB AVG_ROW_LEN
------------------------------ ---------- ---------- ---------- -----------
DEMO 1000001 19280 150.625 131

Conclusion for inserts: all databases have to log the whole data inserted in order to be protected from instance crash. Note that Oracle has a way to insert directly into the file, bypassing the buffer cache, and then reduce the logging required for crash recovery. But I’m not doing bulk inserts here.

Oracle: update

The update in Oracle is done in-place. There is no need to copy the whole row (except in the rare cases where the row increases and do not fit into the block). However, the old value of the column must be copied for MVCC, into the UNDO segment. This is why we see 46 MB of ‘undo change vector size’ here.

SQL> update demo set b=b+1;
1000001 rows updated.
SQL> commit;
Commit complete.
SQL> @ _mystat_diff.sql
 
db block changes redo size undo change vector size redo entries
---------------- ---------------- ----------------------- ----------------
170,777 105,301,308 48,641,772 82,221

The UNDO is only the change vector, not the full block. If you read about copies of full blocks to rollback segments, it is a confusion either from veterans of Oracle 5, or a misunderstanding of flashback features. The UNDO being stored in segments, written first into buffer cache, it is protected by redo logging, so about 46 MB of redo is actually the redo vector of undo vectors. The other 54 MB of redo is the new value of the update.

Oracle: sparse update

The logging of change vectors rather than full pages is even cheaper with sparse updates:

SQL> update demo set b=b+1 where mod(a,10)=1;
100001 rows updated.
SQL> commit;
Commit complete.
SQL> @ _mystat_diff.sql
 
db block changes redo size undo change vector size redo entries
---------------- ---------------- ----------------------- ----------------
56,583 15,414,328 6,111,608 36,921

The volume of undo and redo generated is only 15 MB here, including 6 MB of undo vectors. This is really optimized and this is one reason why you should update only the columns changed (and not use the default non-dynamic update of Hibernate for example).

Oracle: delete

The delete has to mark all rows as deleted and because the space can immediately be reused then whole row must be logged into the UNDO, and this has to be logged into the REDO, so the delete generates lot of logging:

SQL> delete from demo;
1000001 rows deleted.
SQL> commit;
Commit complete.
SQL> @ _mystat_diff.sql
 
db block changes redo size undo change vector size redo entries
---------------- ---------------- ----------------------- ----------------
2,124,823 403,755,892 240,302,088 1,093,821

I have no indexes here. With indexes, all index entries have to be marked as deleted, and this generates undo and redo vector because MVCC in Oracle is at block level: each block modification – for table or index – have to be logged.

Deleting a lot of rows is an expensive operation in Oracle. For bulk purges, it is often better to truncate and insert /*+ append */ when possible (as in non-atomic materialized view refresh). Partitioning helps for that for example to purge old data when partitioned on date.

Postgres without full page logging

Given the huge overhead, is full page logging really required? There are plans to avoid it, mentioned in the Postgres ToDo wiki, or at least to keep it only short term for crash recovery and not media recovery. Another possibility is to implement a checksum on the blocks so that fractured blocks can be detected. Then, when detected, the fractured blocks may not need full page logging to recover them if we can restore a previous backup. This takes longer to recover, but can be acceptable given the low probability of this kind of failure. In addition to that, when you have a physical standby synchronized with log-shipping, you have a easy way to recover without having to restore files. But you need a checksum to detect the problem.

Without a checksum, the problem is the detection of partial writes. But if you trust your storage and if you failover to the standby in case of a crash, you may accept to set full_page_writes=off and this is what I did here.


insert into demo select generate_series a,generate_series b,generate_series c,generate_series d,generate_series e,generate_series f,lpad('x',100,'x') g from generate_series(1,1000000);
...
( written 125255680 bytes to pg_wal/000000010000000A000000E3 -> total WAL segments: 140.65 MB ) cmd=postgres: demo demo 192.168.56.122(38109)
INSERT 0 1000000

The insert still have to log all new data: 140 MB.


update demo set b=b+1;
...
( written 72613888 bytes to pg_wal/000000010000000A000000F2 -> total WAL segments: 213.02 MB ) cmd=postgres: wal writer process
UPDATE 1000001

The update has to log only what is modified, but because of Postgres MVCC implementation, the whole row has to be written in its new version, and the old ones have their pointer updated: 210 MB here.


update demo set b=b+1 where mod(a,10)=1;
( written 305709056 bytes to pg_wal/000000010000000A000000F3 -> total WAL segments: 1.96 MB ) cmd=postgres: demo demo 192.168.56.122(38109)
( written 72613888 bytes to pg_wal/000000010000000A000000F3 -> total WAL segments: 5.62 MB ) cmd=postgres: wal writer process
( written 75718656 bytes to pg_wal/000000010000000A000000F4 -> total WAL segments: 9.65 MB ) cmd=postgres: wal writer process
( written 310665216 bytes to pg_wal/000000010000000A000000F4 -> total WAL segments: 9.65 MB ) cmd=postgres: demo demo 192.168.56.122(38109)
UPDATE 100000

The sparse update benefits from logging only the changed rows: 10 MB here. This one is even smaller than with Oracle because there’s no UNDO to write here: the old values stay in-place.


delete from demo;
( written 323256320 bytes to pg_wal/000000010000000A000000F5 -> total WAL segments: 11.27 MB ) cmd=postgres: demo demo 192.168.56.122(38109)
( written 338829312 bytes to pg_wal/000000010000000A000000F6 -> total WAL segments: 26.92 MB ) cmd=postgres: demo demo 192.168.56.122(38109)
( written 76562432 bytes to pg_wal/000000010000000A000000F6 -> total WAL segments: 31.41 MB ) cmd=postgres: wal writer process
( written 345415680 bytes to pg_wal/000000010000000A000000F7 -> total WAL segments: 39.73 MB ) cmd=postgres: demo demo 192.168.56.122(38109)
( written 83410944 bytes to pg_wal/000000010000000A000000F7 -> total WAL segments: 40.41 MB ) cmd=postgres: wal writer process
DELETE 1000001

The delete is cheap when full_page_writes=off because there’s only the visibility is changed but data remains (until committed and vacuumed). If you have a lot of rows to delete, then consider to set full_page_writes=off and be sure to have a backup to restore in case of crash.

Oracle full page logging in backup mode

So, Oracle by default does not need to protect from fractured blocks, because they can be detected. If the storage crashes while a block is partially written, the block is corrupt. Thanks to the checksum, this corruption will be detected during recovery (or even earlier depending on DB_BLOCK_CHECKSUM and DB_LOST_WRITE_PROTECT). The redo is not sufficient, as it contains only change vectors, but you can recover from the last backup and Oracle can do a simple block recover. This recovery can also be done from the standby database.

However, full page logging exists in Oracle. When running backup from a non-Oracle tool, not aware of block checksum, you need to enclose the copy or snapshot between ‘begin backup’ and ‘end backup’. You do this because online backup may read partially updated blocks, and without the checksum, cannot detect it. A corrupt backup is not very useful and this is why this backup mode will generate more redo to be able to recover them. This is very similar to full page logging: the redo generated for the first modification of the buffer will store the whole block. Next modifications, until buffer is checkpointed, will need only the change vectors.

I think the first article I’ve ever written was a description of the Oracle backup mode. And it is still visible thanks to archive.org only because it was published on… Google Knol!

So, here is the same run with Oracle in backup mode.

Insert does not change a lot as it fills full blocks:

SQL> insert into demo select rownum a,rownum b,rownum c,rownum d,rownum e,rownum f,lpad('x',100,'x') g from xmltable('1 to 1000000');
1000000 rows created.
 
SQL> @ _mystat_diff.sql
 
db block changes redo size undo change vector size redo entries
---------------- ---------------- ----------------------- ----------------
141,376 156,527,072 4,380,448 124,195

Full update of one column generates same undo, but more than 2x redo because of full page logging:

SQL> update demo set b=b+1;
1000001 rows updated.
 
SQL> commit;
Commit complete.
 
SQL> @ _mystat_diff.sql
 
db block changes redo size undo change vector size redo entries
---------------- ---------------- ----------------------- ----------------
170,778 238,317,632 48,641,772 104,640

Sparse update is exactly the same as full update because this 10% touches all pages:

SQL> update demo set b=b+1 where mod(a,10)=1;
100001 rows updated.
 
SQL> commit;
Commit complete.
 
SQL> @ _mystat_diff.sql
 
db block changes redo size undo change vector size redo entries
---------------- ---------------- ----------------------- ----------------
319,622 240,502,284 17,832,196 192,815

Delete generates even more because there’s all the the UNDO in addition to all data pages:

SQL> delete from demo;
1000001 rows deleted.
 
SQL> commit;
Commit complete.
 
SQL> @ _mystat_diff.sql
 
db block changes redo size undo change vector size redo entries
---------------- ---------------- ----------------------- ----------------
2,125,285 558,510,928 240,303,768 1,143,131

So what?

Beyond the very different implementation of Postgres and Oracle, we can see that we have flexibility: the large logging generated by Postgres by default may be reduced in some cases, and the minimal logging which is the default for Oracle may be larger in some situations. The most important, as for all technologies, is to understand how it works. Only then you can do the right choice to balance between performance, availability, and cost. Understand how it works means: read the docs (how it is supposed to work) and test (how it actually works). With Oracle there’s additional information from a huge community testing and using it for decades. With Postgres, as with all Open Source projects, the source code with comments is an amazing documentation.

 

Cet article Full page logging in Postgres and Oracle est apparu en premier sur Blog dbi services.

Backup and Restore PostgreSQL with PgBackRest I

Wed, 2018-02-14 09:58

Many tools can be used to backup PostgreSQL databases. In this blog I will talk about PgBackRest which is a simple tool that can be used to backup and restore a PostgreSQL database. Full, differential, and incremental backups are supported.
In this first blog I will present a basic configuration of pgbackprest. Our configuration is composed of only one cluster and pgbackrest is installed on the server hosting the database. The goal is to explain a first use of PgBackRest.
Below our configuration
Server with Oracle Linux 7
PostgreSQL 10.1
PgBackRest 1.28
We supposed that the linux box and PostgreSQL 10.1 are already installed. So let’s install PgBackRest.

root@pgserver ~]# yum search pgbackrest
Loaded plugins: langpacks, ulninfo
=========================== N/S matched: pgbackrest ============================
pgbackrest.noarch : Reliable PostgreSQL Backup & Restore
pgbackrest.x86_64 : Reliable PostgreSQL Backup & Restore
Name and summary matches only, use "search all" for everything

And then we can install PgBackRest
[root@pgserver ~]# yum install pgbackrest.x86_64
After we can check the installation using pgbackrest command

[postgres@pgserver ~]$ /usr/bin/pgbackrest
pgBackRest 1.28 - General help
Usage:
pgbackrest [options] [command] Commands:
archive-get Get a WAL segment from the archive.
archive-push Push a WAL segment to the archive.
backup Backup a database cluster.
check Check the configuration.
expire Expire backups that exceed retention.
help Get help.
info Retrieve information about backups.
restore Restore a database cluster.
stanza-create Create the required stanza data.
stanza-delete Delete a stanza.
stanza-upgrade Upgrade a stanza.
start Allow pgBackRest processes to run.
stop Stop pgBackRest processes from running.
version Get version.
Use 'pgbackrest help [command]' for more information.

The configuration of PgBackRest is very easy, it consists of a configuration pgbackrest.conf file that must be edited. In my case the file is located in /etc. As specified, we will use a very basic configuration file.
Below the contents of my configuration file

[root@pgserver etc]# cat pgbackrest.conf
[global] repo-path=/var/lib/pgbackrest
[clustpgserver] db-path=/var/lib/pgsql/10/data
retention-full=2
[root@pgserver etc]#

In the file above,
• repo-path is where backup will be stored,
• clusterpgserver is the name of my cluster stanza (free to take what you want as name). A stanza is the configuration for a PostgreSQL database cluster that defines where it is located, how it will be backed up, archiving options, etc.
• db-path is the path of my database files
• retention-full : configure retention to 2 full backups
A complete list can be found here
Once the configuration file done, we can now create the stanza with the command create-stanza. Note that my PostgreSQL cluster is using the port 5435.

[postgres@pgserver ~]$ pgbackrest --stanza=clustpgserver --log-level-console=info --db-port=5435 stanza-create
2018-02-08 14:01:49.293 P00 INFO: stanza-create command begin 1.28: --db1-path=/var/lib/pgsql/10/data --db1-port=5435 --log-level-console=info --repo-path=/var/lib/pgbackrest --stanza=clustpgserver
2018-02-08 14:01:50.707 P00 INFO: stanza-create command end: completed successfully
[postgres@pgserver ~]$

After we create the stanza, we can verify that the configuration is fine using the check command

[postgres@pgserver ~]$ pgbackrest --stanza=clustpgserver --log-level-console=info --db-port=5435 check
2018-02-08 14:03:42.095 P00 INFO: check command begin 1.28: --db1-path=/var/lib/pgsql/10/data --db1-port=5435 --log-level-console=info --repo-path=/var/lib/pgbackrest --stanza=clustpgserver
2018-02-08 14:03:48.805 P00 INFO: WAL segment 00000001000000000000000C successfully stored in the archive at '/var/lib/pgbackrest/archive/clustpgserver/10-1/0000000100000000/00000001000000000000000C-c387b901a257bac304f27865478fd9f768de83d6.gz'
2018-02-08 14:03:48.808 P00 INFO: check command end: completed successfully
[postgres@pgserver ~]$

Since we did not take yet any backup with PgBackRest, the command info for the backups returns error

[postgres@pgserver ~]$ pgbackrest --stanza=clustpgserver --log-level-console=info info
stanza: clustpgserver
status: error (no valid backups)
db (current)
wal archive min/max (10-1): 00000001000000000000000C / 00000001000000000000000C
[postgres@pgserver ~]$

Now let’s take a backup

[postgres@pgserver ~]$ pgbackrest --stanza=clustpgserver --log-level-console=info --db-port=5435 backup
2018-02-08 14:06:52.706 P00 INFO: backup command begin 1.28: --db1-path=/var/lib/pgsql/10/data --db1-port=5435 --log-level-console=info --repo-path=/var/lib/pgbackrest --retention-full=2 --stanza=clustpgserver
WARN: no prior backup exists, incr backup has been changed to full
2018-02-08 14:06:54.734 P00 INFO: execute non-exclusive pg_start_backup() with label "pgBackRest backup started at 2018-02-08 14:06:53": backup begins after the next regular checkpoint completes
2018-02-08 14:06:55.159 P00 INFO: backup start archive = 00000001000000000000000E, lsn = 0/E000060
2018-02-08 14:07:09.867 P01 INFO: backup file /var/lib/pgsql/10/data/base/13805/1255 (592KB, 2%) checksum 61f284092cabf44a30d1442ef6dd075b2e346b7f


2018-02-08 14:08:34.709 P00 INFO: expire command begin 1.28: --log-level-console=info --repo-path=/var/lib/pgbackrest --retention-archive=2 --retention-full=2 --stanza=clustpgserver
2018-02-08 14:08:34.895 P00 INFO: full backup total < 2 - using oldest full backup for 10-1 archive retention
2018-02-08 14:08:34.932 P00 INFO: expire command end: completed successfully
[postgres@pgserver ~]$

We can see that by default PgBackRest will try to do an incremental backup. But as there is no full backup yet, a full backup will be done. Once full backup done, all future backups will be incremental unless we specify the type of backup.

[postgres@pgserver ~]$ pgbackrest --stanza=clustpgserver --log-level-console=info --db-port=5435 backup
2018-02-08 14:26:25.590 P00 INFO: backup command begin 1.28: --db1-path=/var/lib/pgsql/10/data --db1-port=5435 --log-level-console=info --repo-path=/var/lib/pgbackrest --retention-full=2 --stanza=clustpgserver
2018-02-08 14:26:29.314 P00 INFO: last backup label = 20180208-140653F, version = 1.28
2018-02-08 14:26:30.135 P00 INFO: execute non-exclusive pg_start_backup() with label "pgBackRest backup started at 2018-02-08 14:26:26": backup begins after the next regular checkpoint completes
...
2018-02-08 14:27:01.408 P00 INFO: expire command begin 1.28: --log-level-console=info --repo-path=/var/lib/pgbackrest --retention-archive=2 --retention-full=2 --stanza=clustpgserver
2018-02-08 14:27:01.558 P00 INFO: full backup total < 2 - using oldest full backup for 10-1 archive retention
2018-02-08 14:27:01.589 P00 INFO: expire command end: completed successfully
[postgres@pgserver ~]$

If we want to perform another full backup we can specify the option –type=full

[postgres@pgserver ~]$ pgbackrest --stanza=clustpgserver --log-level-console=info --db-port=5435 --type=full backup
2018-02-08 14:30:05.961 P00 INFO: backup command begin 1.28: --db1-path=/var/lib/pgsql/10/data --db1-port=5435 --log-level-console=info --repo-path=/var/lib/pgbackrest --retention-full=2 --stanza=clustpgserver --type=full
2018-02-08 14:30:08.472 P00 INFO: execute non-exclusive pg_start_backup() with label "pgBackRest backup started at 2018-02-08 14:30:06": backup begins after the next regular checkpoint completes
2018-02-08 14:30:08.993 P00 INFO: backup start archive = 000000010000000000000012, lsn = 0/12000028
….
….

To have info about our backups
[postgres@pgserver ~]$ pgbackrest --stanza=clustpgserver info
stanza: clustpgserver
status: ok
db (current)
wal archive min/max (10-1): 00000001000000000000000E / 000000010000000000000012
full backup: 20180208-140653F
timestamp start/stop: 2018-02-08 14:06:53 / 2018-02-08 14:08:19
wal start/stop: 00000001000000000000000E / 00000001000000000000000E
database size: 23.2MB, backup size: 23.2MB
repository size: 2.7MB, repository backup size: 2.7MB
incr backup: 20180208-140653F_20180208-142626I
timestamp start/stop: 2018-02-08 14:26:26 / 2018-02-08 14:26:52
wal start/stop: 000000010000000000000010 / 000000010000000000000010
database size: 23.2MB, backup size: 8.2KB
repository size: 2.7MB, repository backup size: 472B
backup reference list: 20180208-140653F
full backup: 20180208-143006F
timestamp start/stop: 2018-02-08 14:30:06 / 2018-02-08 14:31:30
wal start/stop: 000000010000000000000012 / 000000010000000000000012
database size: 23.2MB, backup size: 23.2MB
repository size: 2.7MB, repository backup size: 2.7MB
[postgres@pgserver ~]$

Now that we see how to perform backup with pgbackrest, let’s see how to restore.
First let identify the directory of our database files

[postgres@pgserver ~]$ psql
psql (10.1)
Type "help" for help.
postgres=# show data_directory ;
data_directory
------------------------
/var/lib/pgsql/10/data
(1 row)
postgres=#

And let’s remove all files in the directory

[postgres@pgserver data]$ pwd
/var/lib/pgsql/10/data
[postgres@pgserver data]$ ls
base pg_dynshmem pg_notify pg_stat_tmp pg_wal postmaster.pid
current_logfiles pg_hba.conf pg_replslot pg_subtrans pg_xact
global pg_ident.conf pg_serial pg_tblspc postgresql.auto.conf
log pg_logical pg_snapshots pg_twophase postgresql.conf
pg_commit_ts pg_multixact pg_stat PG_VERSION postmaster.opts
[postgres@pgserver data]$ rm -rf *
[postgres@pgserver data]$

Now if we try to connect, of course we will get errors

[postgres@pgserver data]$ psql
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5435"?
[postgres@pgserver data]$

So let’s restore with PgBackRest with the restore command

[postgres@pgserver ~]$ pgbackrest --stanza=clustpgserver --log-level-console=info restore
2018-02-08 14:52:01.845 P00 INFO: restore command begin 1.28: --db1-path=/var/lib/pgsql/10/data --log-level-console=info --repo-path=/var/lib/pgbackrest --stanza=clustpgserver
2018-02-08 14:52:03.490 P00 INFO: restore backup set 20180208-143006F
2018-02-08 14:52:21.904 P01 INFO: restore file /var/lib/pgsql/10/data/base/13805/1255 (592KB, 2%) checksum 61f284092cabf44a30d1442ef6dd075b2e346b7f
….
….
2018-02-08 14:53:21.186 P00 INFO: write /var/lib/pgsql/10/data/recovery.conf
2018-02-08 14:53:23.948 P00 INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)
2018-02-08 14:53:28.258 P00 INFO: restore command end: completed successfully
[postgres@pgserver ~]$

At the end of the backup, a recovery.conf file is created in the data directory

[postgres@pgserver data]$ cat recovery.conf
restore_command = '/usr/bin/pgbackrest --log-level-console=info --stanza=clustpgserver archive-get %f "%p"'

Now we can restart the PostgreSQL cluster

[postgres@pgserver data]$ pg_ctl start
waiting for server to start....2018-02-08 14:57:06.519 CET [4742] LOG: listening on IPv4 address "0.0.0.0", port 5435
2018-02-08 14:57:06.522 CET [4742] LOG: listening on IPv6 address "::", port 5435
2018-02-08 14:57:06.533 CET [4742] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5435"
2018-02-08 14:57:06.551 CET [4742] LOG: listening on Unix socket "/tmp/.s.PGSQL.5435"
2018-02-08 14:57:06.645 CET [4742] LOG: redirecting log output to logging collector process
2018-02-08 14:57:06.645 CET [4742] HINT: Future log output will appear in directory "log".
...... done
server started

And then connect

[postgres@pgserver data]$ psql
psql (10.1)
Type "help" for help.
postgres=#

Conclusion
In this blog we shown in a simple configuration how to perform backup using PgBackRest. This basic configuration can help for first use of PgBackRest. In future articles we will go further in an advanced use of this tool.

 

Cet article Backup and Restore PostgreSQL with PgBackRest I est apparu en premier sur Blog dbi services.

SQL Server on Docker and network bridge considerations

Wed, 2018-02-14 06:46

Let’s continue with this blog post series about SQL Server and Docker. A couple of days ago, I was in a customer shop that already implemented SQL Server 2017 on Linux as Docker containers. It was definitely a very interesting day with a lot of customer experience and feedbacks. We discussed with him about lot of architecture scenarios.

The interesting point here is I was able to compare with a previous customer who used docker containers for a while in a completely different way. Indeed, my new customer implemented a Docker infrastructure exclusively based on SQL Server containers whereas the older one already containerized its applications that were connected to an external and non-containerized SQL Server environment.

Use case 1 – Containerized apps and virtualized SQL Server environments Use case 2 – SQL Server containers and virtualized applications  blog 128 - 1- docker archi 2  blog 128 - 1 - docker archi 1

 

In this blog post I want to focus on the first use case in terms of networks.

Connecting to an outside SQL Server (from a docker perspective) is probably an intermediate solution for many customers who already deal with mission-critical environments implying very restrictive high-availability scenarios and when very high performance is required as well. Don’t get me wrong. I’m not saying docker is not designed for mission critical scenarios but let’s say that fear of unknown things, as virtualization before, is still predominant, at least for this kind of scenario. I always keep in mind the repetitive customer question: is Docker ready for production and for databases? Connecting to a non-containerized SQL Server environment may make sense here at least to speed containers adoption. That’s my guess but feel free to comment with your thoughts!

So, in this context we may use different Docker network topologies. I spent some times to study and to discuss with customers about implemented network topologies in their context. For simple Docker infrastructures (without orchestrators like Swarm or Kubernetes) Docker bridges seem to be predominant with either Docker0 bridges or user-defined bridges.

 

  • Docker default bridge (Docker0)

For very limited Docker topologies, default network settings will be probably sufficient with Docker0 bridge. It is probably the case of my latest customer with only 5 SQL Server containers on the top of one Docker engine. By default, each container created without any network specification (and any Docker engine setting customization) will have one network interface sitting on the docker0 bridge with an IP from 172.17.0.0/16 CIDR or whichever CIDR you have configured docker to use. But did you wonder what is exactly a bridge on Docker world?

Let’s have a deeper look on it with a very simple example concerning one docker engine that includes two containers based on microsoft/mssql-tools each and one outside SQL Server that runs on the top of Hyper-V virtual machine. The below picture shows some network details that I will explain later in this blog post.

blog 128 - 3 - docker network bridge

My 2 containers can communicate together because they are sitting on the same network bridge and they are also able to communicate with my database server through the NAT mechanism. IP masquerading and IP forwarding is enabled on my Docker host.

$ sudo docker run -tid --name docker1 microsoft/mssql-tools
77b501fe29af322dd2d1da2824d339a60ba3080c1e61a2332b3cf563755dd3e3

$ sudo docker run -tid --name docker2 microsoft/mssql-tools
3f2ba669591a1889068240041332f02faf970e3adc85619adbf952d5c135d3f4

$ sudo docker ps
CONTAINER ID        IMAGE                   COMMAND                  CREATED             STATUS              PORTS               NAMES
3f2ba669591a        microsoft/mssql-tools   "/bin/sh -c /bin/bash"   7 seconds ago       Up 6 seconds                            docker2
77b501fe29af        microsoft/mssql-tools   "/bin/sh -c /bin/bash"   11 seconds ago      Up 10 seconds                           docker1

 

Let’s take a look at the network configuration of each container. As a reminder, each network object represents a layer 2 broadcast domain with a layer 3 subnet as shown below. Each container is attached to a network through a specific endpoint.

$ sudo docker inspect docker1
[
"Gateway": "172.17.0.1",
            "GlobalIPv6Address": "",
            "GlobalIPv6PrefixLen": 0,
            "IPAddress": "172.17.0.2",
            "IPPrefixLen": 16,
            "IPv6Gateway": "",
            "MacAddress": "02:42:ac:11:00:02",
            "Networks": {
                "bridge": {
                    "IPAMConfig": null,
                    "Links": null,
                    "Aliases": null,
                    "NetworkID": "985f25500e3d0c55d419790f1ac446f92c8d1090dddfd69987a52aab0717e630",
                    "EndpointID": "bd82669031ad87ddcb61eaa2dad823d89ca86cae92c4034d4925009aae634c14",
                    "Gateway": "172.17.0.1",
                    "IPAddress": "172.17.0.2",
                    "IPPrefixLen": 16,
                    "IPv6Gateway": "",
                    "GlobalIPv6Address": "",
                    "GlobalIPv6PrefixLen": 0,
                    "MacAddress": "02:42:ac:11:00:02",
                    "DriverOpts": null
                }
            }
]

$sudo docker inspect docker2
[
"Gateway": "172.17.0.1",
            "GlobalIPv6Address": "",
            "GlobalIPv6PrefixLen": 0,
            "IPAddress": "172.17.0.3",
            "IPPrefixLen": 16,
            "IPv6Gateway": "",
            "MacAddress": "02:42:ac:11:00:03",
            "Networks": {
                "bridge": {
                    "IPAMConfig": null,
                    "Links": null,
                    "Aliases": null,
                    "NetworkID": "985f25500e3d0c55d419790f1ac446f92c8d1090dddfd69987a52aab0717e630",
                    "EndpointID": "140cd8764506344958e9a9725d1c2513f67e56b2c4a1fc67f317c3e555764c1e",
                    "Gateway": "172.17.0.1",
                    "IPAddress": "172.17.0.3",
                    "IPPrefixLen": 16,
                    "IPv6Gateway": "",
                    "GlobalIPv6Address": "",
                    "GlobalIPv6PrefixLen": 0,
                    "MacAddress": "02:42:ac:11:00:03",
                    "DriverOpts": null
                }
            }
]

 

To summarize, two IP addresses have been assigned for Docker1 container (172.17.0.2) and Docker2 container (172.17.0.3) in the IP address interval defined by the Docker0 bridge from the Docker internal IPAM module. Each network interface is created with their own MAC address and the gateway IP address (172.17.0.1) for both containers corresponds to the Docker0 bridge interface.

$ sudo ip a show docker0
4: docker0: <NO-CARRIER,BROADCAST,MULTICAST,UP> mtu 1500 qdisc noqueue state DOWN
    link/ether 02:42:2a:d0:7e:76 brd ff:ff:ff:ff:ff:ff
    inet 172.17.0.1/16 brd 172.17.255.255 scope global docker0
       valid_lft forever preferred_lft forever
    inet6 fe80::42:2aff:fed0:7e76/64 scope link
       valid_lft forever preferred_lft forever

 

Let’s try to connect from the both containers to my SQL Server database:

$ sudo docker exec -it docker1
…
$ sudo docker exec -it docker2
...

 

Then on each container let’s run the following sqlcmd command:

sqlcmd -S 192.168.40.30,1450 -Usa -Ptoto

 

Finally let’s switch on the SQL Server instance and let’s get a picture of existing connections (IP Address 192.168.40.30 and port 1450).

SELECT 
	c.client_net_address,
	c.client_tcp_port,
	c.local_net_address,
	c.protocol_type,
	c.auth_scheme,
	s.program_name,
	s.is_user_process
FROM sys.dm_exec_connections AS c
JOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_id
WHERE client_net_address <> '<local machine>'

 

blog 128 - 4 - docker network bridge sqlcmd

We may notice that the IP address is basically the same (192.168.40.50) indicating we are using NAT to connect from each container.

Let’s go back to the Docker engine network configuration. After creating my 2 containers, we may notice the creation of 2 additional network interfaces.

$ ip a show | grep veth*
12: veth45297ff@if11: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc noqueue master docker0 state UP
14: veth46a8316@if13: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc noqueue master docker0 state UP

 

What are they? At this point, we are entering to Linux network namespace world. You can read further technical details on the internet but to keep simple network namespace concepts, I would say they allow to run different and separate network instances (including routing tables) that operate independent of each other. In other words, there is a way to isolate different networks from each other based on the same physical network device. Assuming we are using docker bridge type networks, when creating a container, in background we are creating a dedicated network namespace that includes a virtual ethernet interface which comes in interconnected pairs. In fact, a virtual ethernet interface acts as a tube to connect a Docker container namespace (in this context) to the outside world via the default / global namespace where the physical interface exists.

Before digging further into details about virtual interfaces let’s say by default Docker doesn’t expose network namespace information because it uses it own libcontainer and the microsoft/mssql-tools docker image is based on a simplified Linux image that doesn’t include network tools to easily show virtual interface information. So, a workaround is to expose a Docker container namespace into the host.

First we have to find out the process id of the container and then link its corresponding proc namespace to /var/run/netns host directory as shown below:

$ sudo docker inspect --format '{{.State.Pid}}' docker1
2094
$ sudo ln -s /proc/2094/ns/net /var/run/netns/ns-2094

 

Then we may use ip netns command to extract the network information

$ sudo ip netns
ns-2094 (id: 0)
$ sudo ip netns exec ns-2094 ip link
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN mode DEFAULT qlen 1
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
11: eth0@if12: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc noqueue state UP mode DEFAULT
    link/ether 02:42:ac:11:00:02 brd ff:ff:ff:ff:ff:ff link-netnsid 0

 

Here we go. The interesting information is the container network interface 11: eth0@if12

So, the first pair is the eth0 interface on the Docker container and the “outside” pair corresponds to the interface number 12. On the host the interface 12 corresponds to the virtual ethernet adapter veth45297ff. Note we may also find out the pair corresponding to the container interface (@if11).

$ ip a | grep "^12"
12: veth45297ff@if11: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc noqueue master docker0 state UP

 

Finally, let’s take a look at the bridge used by the virtual ethernet adapter veth45297ff

$ sudo brctl show
bridge name     bridge id               STP enabled     interfaces
docker0         8000.02422ad07e76       no              veth45297ff
                                                        veth46a8316

 

The other veth (46a8316) corresponds to my second docker2 container.

 

  • User-defined network bridges

But as said previously using the Docker0 bridge is only suitable for very limited scenarios. User-defined bridges are more prevalent with more complex scenarios like microservice applications because they offer a better isolation between containers and the outside world as well as a better manageability and customization. At this stage we may also introduce macvlan networks but probably in the next blog post …

For example, let’s say you want to create 2 isolated network bridges for a 3-tiers application. The users will access the web server (from the exposed port) throughout the first network (frontend-server). But in the same time, you also want to prevent containers that sit on this network to make connections to the outside world. The second network (backend-server) will host containers that must have access to both the outside SQL Server database and the web server.

blog 128 - 5 - docker network bridge segregation

User-defined networks is a good solution to address these requirements. Let’s create two user-defined networks. Note by default containers may make connections to the outside world but the outside is not able to make connections to the containers without exposing listen ports. This is why I disabled ip masquerading (com.docker.network.bridge.enable_ip_masquerade=false) for the frontend-server network to meet the above requirements.

$sudo docker network create \
    --driver bridge \
    --subnet 172.20.0.0/16 \
  --gateway 172.20.0.1 \
  backend-server  
$sudo docker network create \
    --driver bridge \
    --subnet 172.19.0.0/16 \
    --gateway 172.19.0.1 \
    --opt com.docker.network.bridge.enable_ip_masquerade=false \
  frontend-server
$ sudo docker network ls 
NETWORK ID          NAME                DRIVER              SCOPE
5c6f48269d2b        backend-server      bridge              local
985f25500e3d        bridge              bridge              local
b1fbde4f4674        frontend-server     bridge              local
ad52b859e3f9        host                host                local
1beda56f93d3        none                null                local

 

Let’s now take a look at the corresponding iptables masquerading rules on my host machine:

$ sudo iptables -t nat -L -n | grep -i "masquerade"
MASQUERADE  all  --  172.20.0.0/16        0.0.0.0/0
MASQUERADE  all  --  172.17.0.0/16        0.0.0.0/0

 

You may notice only the Docker0 (172.17.0.0/16) and backend-server (172.20.0.0/16) bridges are allowed for ip masquerading.

Then let’s create 2 containers with the two first ones (docker1 and docker2) that will sit on the frontend-server network and the second one (docker2) on the backend-server network. For convenient purposes, I setup fixed hostnames for each container. I also used a different ubuntu image that provides this time all necessary network tools including ping command.

$ sudo docker run -d --rm --name=docker1 --hostname=docker1 --net=frontend-server -it smakam/myubuntu:v6 bash

$ sudo docker run -d --rm --name=docker2 --hostname=docker2 --net=frontend-server -it smakam/myubuntu:v6 bash

$sudo docker run -d --rm --name=docker3 --hostname=docker3 --net=backend-server -it smakam/myubuntu:v6 bash

$ sudo docker ps
CONTAINER ID        IMAGE                COMMAND             CREATED             STATUS              PORTS               NAMES
225ee13c38f7        smakam/myubuntu:v6   "bash"              2 minutes ago       Up 2 minutes                            docker3
d95014602fe2        smakam/myubuntu:v6   "bash"              4 minutes ago       Up 4 minutes                            docker2
1d9645f61245        smakam/myubuntu:v6   "bash"              4 minutes ago       Up 4 minutes                            docker1

 

First, probably one of the biggest advantages of using user-defined networks (unlike Docker0 bridge) is the ability to use automatic DNS resolution between containers on the same user-defined subnet on the same host (this is default behavior but you can override DNS settings by specifying –dns parameter at the container creation time). In fact, Docker applies update on the /etc/hosts file of each container when adding / deleting containers.

As expected, I may ping docker2 container from docker1 container and vice-versa but the same doesn’t apply between neither docker1 and docker3 nor docker2 and docker3 because they are not sitting on the same network bridge.

$ sudo docker exec -ti docker1 ping -c2 docker2
PING docker2 (172.19.0.3) 56(84) bytes of data.
64 bytes from docker2.frontend-server (172.19.0.3): icmp_seq=1 ttl=64 time=0.088 ms
64 bytes from docker2.frontend-server (172.19.0.3): icmp_seq=2 ttl=64 time=0.058 ms
…
$ sudo docker exec -ti docker2 ping -c2 docker1
PING docker1 (172.19.0.2) 56(84) bytes of data.
64 bytes from docker1.frontend-server (172.19.0.2): icmp_seq=1 ttl=64 time=0.084 ms
64 bytes from docker1.frontend-server (172.19.0.2): icmp_seq=2 ttl=64 time=0.054 ms
...
$ sudo docker exec -ti docker1 ping -c2 docker3
ping: unknown host docker3
...

 

From a network perspective, on the host we may notice the creation of two additional bridge interfaces and 3 virtual Ethernet adapters after the creation of the containers.

$ brctl show
bridge name     bridge id               STP enabled     interfaces
br-5c6f48269d2b         8000.0242ddad1660       no              veth79ae355
br-b1fbde4f4674         8000.02424bebccdd       no              vethb66deb8
                                                        vethbf4ab2d
docker0         8000.02422ad07e76       no
$ ip a | egrep "^[1-9][1-9]"
25: br-5c6f48269d2b: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc noqueue state UP
28: br-b1fbde4f4674: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc noqueue state UP
58: vethb66deb8@if57: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc noqueue master br-b1fbde4f4674 state UP
64: veth79ae355@if63: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc noqueue master br-5c6f48269d2b state UP

 

If I want to make the docker3 container reachable from docker2 container I may simply connect the latter to the corresponding network as shown below:

$ sudo docker network connect backend-server docker2

$ sudo docker inspect docker2
[
"Networks": {
                "backend-server": {
                    "IPAMConfig": {},
                    "Links": null,
                    "Aliases": [
                        "d95014602fe2"
                    ],
                    "NetworkID": "5c6f48269d2b752bf1f43efb94437957359c6a72675380c16e11b2f8c4ecaaa1",
                    "EndpointID": "4daef42782b22832fc98485c27a0f117db5720e11d806ab8d8cf83e844ca6b81",
                    "Gateway": "172.20.0.1",
                    "IPAddress": "172.20.0.3",
                    "IPPrefixLen": 16,
                    "IPv6Gateway": "",
                    "GlobalIPv6Address": "",
                    "GlobalIPv6PrefixLen": 0,
                    "MacAddress": "02:42:ac:14:00:03",
                    "DriverOpts": null
                },
                "frontend-server": {
                    "IPAMConfig": null,
                    "Links": null,
                    "Aliases": [
                        "d95014602fe2"
                    ],
                    "NetworkID": "b1fbde4f4674386a0e01b7ccdee64ed8b08bd8505cd7f0021487d32951035570",
                    "EndpointID": "651ad7eaad994a06658941cda7e51068a459722c6d10850a4b546382c44fff86",
                    "Gateway": "172.19.0.1",
                    "IPAddress": "172.19.0.3",
                    "IPPrefixLen": 16,
                    "IPv6Gateway": "",
                    "GlobalIPv6Address": "",
                    "GlobalIPv6PrefixLen": 0,
                    "MacAddress": "02:42:ac:13:00:03",
                    "DriverOpts": null
                }
            }
]

 

You may notice the container is connected to the frontend-server and backend-server as well thanks to an additional network interface created at same time.

$ sudo docker exec -it docker2 ip a show | grep eth
59: eth0@if60: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc noqueue state UP group default
    link/ether 02:42:ac:13:00:03 brd ff:ff:ff:ff:ff:ff
    inet 172.19.0.3/16 brd 172.19.255.255 scope global eth0
68: eth2@if69: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc noqueue state UP group default
    link/ether 02:42:ac:14:00:03 brd ff:ff:ff:ff:ff:ff
    inet 172.20.0.3/16 brd 172.20.255.255 scope global eth2

 

Pinging both docker1 container and docker3 container from docker2 container is successful now.

$ sudo docker exec -it docker2 ping -c2 docker1
PING docker1 (172.19.0.2) 56(84) bytes of data.
64 bytes from docker1.frontend-server (172.19.0.2): icmp_seq=1 ttl=64 time=0.053 ms
64 bytes from docker1.frontend-server (172.19.0.2): icmp_seq=2 ttl=64 time=0.052 ms
…
$ sudo docker exec -it docker2 ping -c2 docker3
PING docker3 (172.20.0.2) 56(84) bytes of data.
64 bytes from docker3.backend-server (172.20.0.2): icmp_seq=1 ttl=64 time=0.082 ms
64 bytes from docker3.backend-server (172.20.0.2): icmp_seq=2 ttl=64 time=0.054 ms
…

 

In this blog post, we surfaced Docker network bridges and use cases we may have to deal with SQL Server instances regarding the context. As a reminder, user-defined networks may allow to define fine-grained policy rules to interconnect containers on different subnets. This is basically what we may want to achieve with microservices applications. Indeed, such applications include some components that need to span multiple networks (backend and frontend networks) whereas other ones should by isolated (even from outside) regarding their role.

Happy containerization!

 

 

 

 

Cet article SQL Server on Docker and network bridge considerations est apparu en premier sur Blog dbi services.

Could you trust option_packs_usage_statistics.sql ?

Wed, 2018-02-14 04:17
Introduction

As a former Oracle LMS qualified auditor my opinion is sometimes requested before/during/after an Oracle LMS audit or simply to ensure a customer that his Oracle database is 100% in conformity with Oracle Licensing Policy. Even if

“The Oracle License Management Services (LMS) Group is the only Oracle group authorized to review and provide opinions on compliance status and will provide guidance, education and impartial opinions on a customer or partner’s compliance state. For more information please visit the following website: http://www.oracle.com/corporate/lms.”

I very do hope that you will find interesting tips in this blog.

Most of the time when a customer would like to check which Oracle options are used by his database infrastructure he is using the well known script “option_packs_usage_statistics.sql”. dbi services did checks of the options detected by the script provided by Oracle (My Oracle Support DOC ID 1317265.1). Depending on your database usage this script will detect the usage of different options, but could you really trust the output of this script and how to interpret the output ?

Could we trust the output of option_packs_usage_statistics.sql ?

The answer is quite easy and short: NO you can’t !

Why? Because as for any software there are some bugs and these bugs lead to false positive detection. The good news is that some of these false positive are documented on My Oracle Support. Indeed the script options_packs_usage_statistics.sql used and provided by Oracle has 14 documented bugs (My Oracle Support Doc ID 1309070.1) and some other non-documented bugs (eg. My Oracle Support BUG 17164904). These bugs are related to:

1.    Bug 11902001 – Exclude default users for Feature usage tracking for Securefiles option
2.    Bug 11902142 – Exclude default users for Feature usage tracking for Advanced Compression option
3.    Bug 19618850 – SOLUTION TO PREVENT UNINTENTED ORACLE OPTION USAGE
4.    Query against DBA_FEATURE_USAGE_STATISTICS is not a true test for use of SDO
5.    Bug 16088534 : RMAN default Backup BZIP2 Compression feature is reported wrongly as as an Advanced Compression feature
6.    Bug 22122625 – GETTING FALSE POSITIVES ON USAGE OF ADVANCED INDEX COMPRESSION
7.    Bug 24844549 – ADVANCED INDEX COMPRESSION SHOWS USAGE IN DBA_FEATURE_USAGE_STATISTICS WITH HCC
8.    Bug 16859747 – DBA_FEATURE_USAGE_STATISTICS SHOWS INCORRECT USAGE FOR HEAPCOMPRESSION
9.    Bug 16563444 – HEAT MAP FEATURE USAGE TRACKING IS NOT CORRECT
10.    Bug 19317899 – IMC: IN-MEMORY OPTION IS REPORTED AS BEING USED EVEN INMEMORY_SIZE IS 0
11.    Bug 19308780 – DO NOT FEATURE TRACK OBJECTS FOR IM WHEN INMEMORY_SIZE = 0
12.    Bug 21248059 – DBA_FEATURE_USAGE_STATISTICS BUG IN TRACKING “HYBRID COLUMNAR COMPRESSION” FEAT
13.    Bug 25661076 – DBA_FEATURE_USAGE_STATISTICS INCORRECTLY SHOWS SPATIAL USAGE IN 12C
14.    Bug 23734270 – DBA_FEATURE_USAGE_STATISTICS SHOWS PERMANENT USAGE OF REAL-TIME SQL MONITORING

These bugs may lead to the detection of features such as : Automatic Maintenance – SQL Tuning advisor & Automatic SQL Tuning Advisor, Real-Time SQL monitoring, Advanced security – Oracle Utility Datapump (Export) and Oracle Utility Datapump (Import), Advanced Compression – Heat Map, Advanced Compression – Oracle Utility Datapump (Export) and Oracle Utility Datapump (Import), aso….

Of course these bugs make the real options usage analysis especially difficult even for an experimented Database Administrator. Additionally the Oracle database in version 12 could make usage of options in maintenance windows without manual activation. That the case for instance of options such as : Automatic Maintenance – SQL Tuning Advisor, Automatic SQL Tuning Advisor and Automatic SQL Tuning Advisor.

14. Bug 23734270 – DBA_FEATURE_USAGE_STATISTICS SHOWS PERMANENT USAGE OF REAL-TIME SQL MONITORING
On a freshly created 12c database, DBA_FEATURE_USAGE_STATISTICS shows usage of Real-Time SQL Monitoring even if no reports have been run from OEM pages or with DBMS_SQL_MONITOR.
Reason :SQL Monitor reports are automatically generated and saved in AWR but should be considered as system usage.
This behavior is the same for all 12 releases and is not present in 11.2. – Extract of My Oracle Support Bug 23734270

Even if LMS team is not using option_packs_usage_statistics.sql script, the output of LMS_Collection_Tool (ReviewLite.sql) is quite the same. The direct consequence in case of an Oracle LMS audit is that the auditor could detect options that you simply never used and you will have to make the proof of non usage… if not you will have to pay the invoice following the final LMS report as stated in your LMS preliminary/final report.

“Based upon the information provided to License Management Services, the following licensing issues need to be resolved within 30 days from the date of the Final Report.”

“In accordance to Oracle compliance policies, backdated support charges are due for the period of unlicensed usage of Oracle Programs.
Please provide your feedback on this preliminary report within 10 days from the presentation of this report.”- extract of an Oracle LMS audit

Even if I do not have hundreds of cases where the LMS department made wrong detection, I’ve concrete stories where LMS team detected some false positives. Last case was related to the detection of more than 700 usage of Advanced compression due to unpublished BUG 17164904. Thanks to the metalink Doc ID 1993134.1, the bug is explained:

In 12.1.0.1,  the compression counter is incorrectly incremented (COMPRESSCNT=1) for compression=metadata_only (either explicitly or by default) due to unpublished BUG 17164904 – INCORRECT FEATURE USAGE STATISTICS FOR DATA PUMP COMPRESSION, fixed with 12.1.0.2.

How to interpret the output of option_packs_usage_statistics.sql ?

Sometimes this script could provide you some non sense option usage. That the case for instance for features provided only since database version 12c but detected on your old database version 11g. In such a case simply edit the option_packs_usage_statistics.sql script and have a look on the comments. A perfect example of that is illustrated by the detection of Heat Map usage in database version 11g whereas this option is available since version 12c. You can see below another example of wrong options detection related to “Automatic Maintenance – SQL Tuning Advisor” and “Automatic SQL Tuning Advisor”:


SELECT ‘Tuning Pack’                                         , ‘Automatic Maintenance – SQL Tuning Advisor’              , ‘^12\.‘                      , ‘INVALID‘ from dual union all  – system usage in the maintenance window
SELECT ‘Tuning Pack’                                         , ‘Automatic SQL Tuning Advisor’                            , ‘^11\.2|^12\.’               , ‘INVALID‘ from dual union all  — system usage in the maintenance window
SELECT ‘Tuning Pack’                                         , ‘Real-Time SQL Monitoring’                                , ‘^11\.2′                     , ‘ ‘       from dual union all

This INVALID clause explain that the detection of this option is due to system usage in the maintenance window in version 12 (Automatic Maintenance – SQL Tuning Advisor) and in version 11.2 and 12 for Automatic SQL Tuning Advisor. This is also explained few lines after in the option_packs_usage_statistics.sql script :


where nvl(CONDITION, ‘-‘) != ‘INVALID‘                   — ignore features for which licensing is not required without further conditions

    and not (CONDITION = ‘C003′ and CON_ID not in (0, 1))  — multiple PDBs are visible only in CDB$ROOT; PDB level view is not relevant
)

In such a case the option does not have to be considered since the normal behavior of an oracle database in version 12 is to use this option in the maintenance window. This is just an example to illustrate that some detected option does not have to be licensed as explained in the script.

Conclusion

I very do hope that this blog helps you to have a better understanding of how to detect what your database infrastructure really uses in terms of Oracle options. Anyway if you are convinced that you do not use an Oracle database option despite the output of scripts such as option-packs_usage_statistics or ReviewLite which proofs the opposite, have a look on My Oracle Support. Look for bug related to wrong detection of this feature and with a little bit of luck you will find something interesting. Oracle is definitively engineered for heroes…

Oracle Engineered For Heroes

Oracle Engineered For Heroes

 

Cet article Could you trust option_packs_usage_statistics.sql ? est apparu en premier sur Blog dbi services.

How we build our customized PostgreSQL Docker image

Tue, 2018-02-13 13:21

Docker becomes more and more popular these days and a lot of companies start to really use it. At one project we decided to build our own customized Docker image instead of using the official PostgreSQL one. The main reason for that is that we wanted to compile from source so that we only get want is really required. Why having PostgreSQL compiled with tcl support when nobody will ever use that? Here is how we did it …

To dig in right away, this is the simplified Dockerfile:

FROM debian

# make the "en_US.UTF-8" locale so postgres will be utf-8 enabled by default
ENV LANG en_US.utf8
ENV PG_MAJOR 10
ENV PG_VERSION 10.1
ENV PG_SHA256 3ccb4e25fe7a7ea6308dea103cac202963e6b746697366d72ec2900449a5e713
ENV PGDATA /u02/pgdata
ENV PGDATABASE "" \
    PGUSERNAME "" \
    PGPASSWORD ""

COPY docker-entrypoint.sh /

RUN set -ex \
        \
        && apt-get update && apt-get install -y \
           ca-certificates \
           curl \
           procps \
           sysstat \
           libldap2-dev \
           libpython-dev \
           libreadline-dev \
           libssl-dev \
           bison \
           flex \
           libghc-zlib-dev \
           libcrypto++-dev \
           libxml2-dev \
           libxslt1-dev \
           bzip2 \
           make \
           gcc \
           unzip \
           python \
           locales \
        \
        && rm -rf /var/lib/apt/lists/* \
        && localedef -i en_US -c -f UTF-8 en_US.UTF-8 \
        && mkdir /u01/ \
        \
        && groupadd -r postgres --gid=999 \
        && useradd -m -r -g postgres --uid=999 postgres \
        && chown postgres:postgres /u01/ \
        && mkdir -p "$PGDATA" \
        && chown -R postgres:postgres "$PGDATA" \
        && chmod 700 "$PGDATA" \
        \
        && curl -o /home/postgre/postgresql.tar.bz2 "https://ftp.postgresql.org/pub/source/v$PG_VERSION/postgresql-$PG_VERSION.tar.bz2" \
        && echo "$PG_SHA256 /home/postgres/postgresql.tar.bz2" | sha256sum -c - \
        && mkdir -p /home/postgres/src \
        && chown -R postgres:postgres /home/postgres \
        && su postgres -c "tar \
                --extract \
                --file /home/postgres/postgresql.tar.bz2 \
                --directory /home/postgres/src \
                --strip-components 1" \
        && rm /home/postgres/postgresql.tar.bz2 \
        \
        && cd /home/postgres/src \
        && su postgres -c "./configure \
                --enable-integer-datetimes \
                --enable-thread-safety \
                --with-pgport=5432 \
                --prefix=/u01/app/postgres/product/$PG_VERSION \\
                --with-ldap \
                --with-python \
                --with-openssl \
                --with-libxml \
                --with-libxslt" \
        && su postgres -c "make -j 4 all" \
        && su postgres -c "make install" \
        && su postgres -c "make -C contrib install" \
        && rm -rf /home/postgres/src \
        \
        && apt-get update && apt-get purge --auto-remove -y \
           libldap2-dev \
           libpython-dev \
           libreadline-dev \
           libssl-dev \
           libghc-zlib-dev \
           libcrypto++-dev \
           libxml2-dev \
           libxslt1-dev \
           bzip2 \
           gcc \
           make \
           unzip \
        && apt-get install -y libxml2 \
        && rm -rf /var/lib/apt/lists/*

ENV LANG en_US.utf8
USER postgres
EXPOSE 5432
ENTRYPOINT ["/docker-entrypoint.sh"]

We based the image on the latest Debian image, that is line 1. The following lines define the PostgreSQL version we will use and define some environment variables we will user later. What follows is basically installing all the packages required for building PostgreSQL from source, adding the operating system user and group, preparing the directories, fetching the PostgreSQL source code, configure, make and make install. Pretty much straight forward. Finally, to shrink the image, we remove all the packages that are not any more required after PostgreSQL was compiled and installed.

The final setup of the PostgreSQL instance happens in the docker-entrypoint.sh script which is referenced at the very end of the Dockerfile:

#!/bin/bash

# this are the environment variables which need to be set
PGDATA=${PGDATA}/${PG_MAJOR}
PGHOME="/u01/app/postgres/product/${PG_VERSION}"
PGAUTOCONF=${PGDATA}/postgresql.auto.conf
PGHBACONF=${PGDATA}/pg_hba.conf
PGDATABASENAME=${PGDATABASE}
PGUSERNAME=${PGUSERNAME}
PGPASSWD=${PGPASSWORD}

# create the database and the user
_pg_create_database_and_user()
{
    ${PGHOME}/bin/psql -c "create user ${PGUSERNAME} with login password '${PGPASSWD}'" postgres
    ${PGHOME}/bin/psql -c "create database ${PGDATABASENAME} with owner = ${PGUSERNAME}" postgres
}

# start the PostgreSQL instance
_pg_prestart()
{
    ${PGHOME}/bin/pg_ctl -D ${PGDATA} -w start
}

# start postgres and do not disconnect
# required for docker
_pg_start()
{
    ${PGHOME}/bin/postgres "-D" "${PGDATA}"
}

# stop the PostgreSQL instance
_pg_stop()
{
    ${PGHOME}/bin/pg_ctl -D ${PGDATA} stop -m fast
}

# initdb a new cluster
_pg_initdb()
{
    ${PGHOME}/bin/initdb -D ${PGDATA} --data-checksums
}


# adjust the postgresql parameters
_pg_adjust_config() {
    # PostgreSQL parameters
    echo "shared_buffers='128MB'" >> ${PGAUTOCONF}
    echo "effective_cache_size='128MB'" >> ${PGAUTOCONF}
    echo "listen_addresses = '*'" >> ${PGAUTOCONF}
    echo "logging_collector = 'on'" >> ${PGAUTOCONF}
    echo "log_truncate_on_rotation = 'on'" >> ${PGAUTOCONF}
    echo "log_filename = 'postgresql-%a.log'" >> ${PGAUTOCONF}
    echo "log_rotation_age = '1440'" >> ${PGAUTOCONF}
    echo "log_line_prefix = '%m - %l - %p - %h - %u@%d '" >> ${PGAUTOCONF}
    echo "log_directory = 'pg_log'" >> ${PGAUTOCONF}
    echo "log_min_messages = 'WARNING'" >> ${PGAUTOCONF}
    echo "log_autovacuum_min_duration = '60s'" >> ${PGAUTOCONF}
    echo "log_min_error_statement = 'NOTICE'" >> ${PGAUTOCONF}
    echo "log_min_duration_statement = '30s'" >> ${PGAUTOCONF}
    echo "log_checkpoints = 'on'" >> ${PGAUTOCONF}
    echo "log_statement = 'none'" >> ${PGAUTOCONF}
    echo "log_lock_waits = 'on'" >> ${PGAUTOCONF}
    echo "log_temp_files = '0'" >> ${PGAUTOCONF}
    echo "log_timezone = 'Europe/Zurich'" >> ${PGAUTOCONF}
    echo "log_connections=on" >> ${PGAUTOCONF}
    echo "log_disconnections=on" >> ${PGAUTOCONF}
    echo "log_duration=off" >> ${PGAUTOCONF}
    echo "client_min_messages = 'WARNING'" >> ${PGAUTOCONF}
    echo "wal_level = 'replica'" >> ${PGAUTOCONF}
    echo "hot_standby_feedback = 'on'" >> ${PGAUTOCONF}
    echo "max_wal_senders = '10'" >> ${PGAUTOCONF}
    echo "cluster_name = '${PGDATABASENAME}'" >> ${PGAUTOCONF}
    echo "max_replication_slots = '10'" >> ${PGAUTOCONF}
    echo "work_mem=8MB" >> ${PGAUTOCONF}
    echo "maintenance_work_mem=64MB" >> ${PGAUTOCONF}
    echo "wal_compression=on" >> ${PGAUTOCONF}
    echo "max_wal_senders=20" >> ${PGAUTOCONF}
    echo "shared_preload_libraries='pg_stat_statements'" >> ${PGAUTOCONF}
    echo "autovacuum_max_workers=6" >> ${PGAUTOCONF}
    echo "autovacuum_vacuum_scale_factor=0.1" >> ${PGAUTOCONF}
    echo "autovacuum_vacuum_threshold=50" >> ${PGAUTOCONF}
    # Authentication settings in pg_hba.conf
    echo "host    all             all             0.0.0.0/0            md5" >> ${PGHBACONF}
}

# initialize and start a new cluster
_pg_init_and_start()
{
    # initialize a new cluster
    _pg_initdb
    # set params and access permissions
    _pg_adjust_config
    # start the new cluster
    _pg_prestart
    # set username and password
    _pg_create_database_and_user
}

# check if $PGDATA exists
if [ -e ${PGDATA} ]; then
    # when $PGDATA exists we need to check if there are files
    # because when there are files we do not want to initdb
    if [ -e "${PGDATA}/base" ]; then
        # when there is the base directory this
        # probably is a valid PostgreSQL cluster
        # so we just start it
        _pg_prestart
    else
        # when there is no base directory then we
        # should be able to initialize a new cluster
        # and then start it
        _pg_init_and_start
    fi
else
    # initialze and start the new cluster
    _pg_init_and_start
    # create PGDATA
    mkdir -p ${PGDATA}
    # create the log directory
    mkdir -p ${PGDATA}/pg_log
fi
# restart and do not disconnect from the postgres daemon
_pg_stop
_pg_start

The important point here is: PGDATA is a persistent volume that is linked into the Docker container. When the container comes up we need to check if something that looks like a PostgreSQL data directory is already there. If yes, then we just start the instance with what is there. If nothing is there we create a new instance. Remember: This is just a template and you might need to do more checks in your case. The same is true for what we add to pg_hba.conf here: This is nothing you should do on real systems but can be handy for testing.

Hope this helps …

 

Cet article How we build our customized PostgreSQL Docker image est apparu en premier sur Blog dbi services.

Pages