Feed aggregator

Advanced Compression Options in Oracle 11g

Tom Kyte - Tue, 2017-03-14 17:26
Hi Chris/Connor, Can you please share any document on Advanced Compression Options in Oracle 11g. I have searching on this and getting only theoretical stuffs. Could you please share any reference, blog to study on this to understand its use on OL...
Categories: DBA Blogs

Umlaut characters converted to junk while running PL/SQL script

Tom Kyte - Tue, 2017-03-14 17:26
Hi, I have procedure with umlaut characters in it. Below is the sample. CREATE OR REPLACE PROCEDURE procPrintHelloWorld IS BEGIN DBMS_OUTPUT.PUT_LINE('? a A, O, U, a, o Hello World!'); END; / When procedure is created through sql ...
Categories: DBA Blogs

Expdp and impdp

Tom Kyte - Tue, 2017-03-14 17:26
I have a requirement to export a full database using EXPDP and import only three schemas using remap_schemas to a different schema than the original. How can I achieve that?
Categories: DBA Blogs

Clarification of "snapshot too old" error message

Tom Kyte - Tue, 2017-03-14 17:26
Hi, I was going through Tom's book and came across "snapshot too old" section. In the section small undo tablespace is created and below block is run. Below block generates error message "snapshot too old". As per my understanding,the column values...
Categories: DBA Blogs

Web Service

Tom Kyte - Tue, 2017-03-14 17:26
A client of mine asked me posted one or more plsql functions as webservice. Which oracle tool should I use? Thanks from Italy Hi Connor, "posted" is a mistake. They want to access them via web service. Sorry for my poor english. Mass...
Categories: DBA Blogs

Reclaim the free space by truncate/Drop partition from partition table having LOB column

Tom Kyte - Tue, 2017-03-14 17:26
Hi Team, I have one partition table having almost 300GB data with LOB column which contains the 90-95% data of total table size. Partition table having 130 partition created and out of it currently 20 partition having the data. Partition table hav...
Categories: DBA Blogs

db_link and synonym

Tom Kyte - Tue, 2017-03-14 17:26
Hi Tom, I have two databases <b>wombat</b> and <b>foo</b>. Womabt has two users craig and denver. I have created a private db_link with the same name in each users of <b>wombat</b> to connect to <b>foo</b> as follows : owner DB_LINK ------ ...
Categories: DBA Blogs

ORA-12547: TNS: lost contact error in ORacle Linux 7.x

Tom Kyte - Tue, 2017-03-14 17:26
Hi, I am running Virtualbox in Mac OS (sierra) . In VB, i have installed Oracle LInux 7.x as guest OS. In guest OS i am trying to install Oracle 12c. After installing the software, I try to create a database using the dbca command. On running ...
Categories: DBA Blogs

Redo log, why minimum of 2

Tom Kyte - Tue, 2017-03-14 17:26
Hi Tom, Please let me clear that why the mini redo log groups is 2?/ Regards, Gyan
Categories: DBA Blogs

UTL_FILE FRENAME between 2 mounted directories

Tom Kyte - Tue, 2017-03-14 17:26
My question has been asked already at https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9533980100346684832 Anyway I cannot understand why I can create in that directory a file using FOPEN, why I can copy it, why I can remove it ...
Categories: DBA Blogs

Default Password Hashes for 11g Oracle Database

Pete Finnigan - Tue, 2017-03-14 17:26
I often get Oracle Security related questions from people randomly sent to my inbox or occasionally on Social media and less on on this sites forum. I get questions on average probably 4 times per week in these ways. I....[Read More]

Posted by Pete On 14/03/17 At 06:16 PM

Categories: Security Blogs

Taste of KScope 2017 Webinars

Scott Spendolini - Tue, 2017-03-14 17:05

This Thursday, I’ll be participating in the Taste of KScope 2017 webinar series by presenting GET POST ORDS JSON: Web Services for APEX Decoded.  The webinar will begin at noon EDT on Thursday, March 16th.  The webinar is completely free, and you don’t need to be an ODTUG member to attend.

Here’s a summary of the abstract:

Web Services in the APEX world are becoming more and more popular.  However, there is still a lot of confusion as to what they are and how they could benefit the APEX developer.  After a review of the syntax and jargon associated with web services, this session will review and boil down web services to their basic components.  It will then demonstrate how APEX developers can start to use these powerful components - both to send and receive data from other sites.  

Not only will I be presenting this session at KScope later this year, but I’ve also done it a few times already, so most of the kinks are (hopefully) worked out.

You can register for the webinar here: https://attendee.gotowebinar.com/register/2300788935263147265

It's A Matter Of Perspective

Floyd Teter - Tue, 2017-03-14 14:09
So I suppose that if I'm going to blow the trumpet and announce the resurrection of this blog, I'd better write something meaningful...

I'm in Northern California at Oracle HQ this week. It's always fun to observe what's happening here in Silicon Valley.  For example, I can see the tech market is still good...lots of employment ads on billboards between the San Jose and San Francisco airports.  And the highly-publicized drought is clearly broken:  the area is as green as I've ever seen it.

I will say that I've also seen some divergent behavior in response to the breaking of the drought.  On one hand, I see lots of recently-installed xeriscape landscaping.  But on the other hand, I also see a bunch of recently repaired lawn grass - with lawn sprinklers watering every day.  I guess whether you're adapting with new water-wise landscaping or salvaging your lawn depends on your perspective.  Are drought-like conditions the new norm or is the weather in Northern California returning to normal after a long anomaly of dry weather?  I suppose it's all a matter of your perspective.

I see the same type of divergent behavior among SaaS customers.

Some customers see SaaS as driving a new business norm.  They embrace the trade-off of increasing simplicity to lower operational costs with the reduction in flexibility through business process customization.  Those customers see that they get more value from SaaS by accepting less flexibility in customizing the way they do business.

Other customers seem to simply look at SaaS as the latest trend to arrive in the enterprise tech world.  They're willing to have a vendor host their technology platform, but still want the flexibility to customize the software in order to make it fit their existing business processes.

It's possible for either type of customer to get what they want.  I'll maintain that the former type gets more value from SaaS than the latter.  But, in the end, I suppose the choice of adapting to the new norm or attempting to salvage what you had before really depends on your perspective.

Significant Improvement for WebLogic Start-Up Time on macOS Sierra

Andrejus Baranovski - Tue, 2017-03-14 11:59
I have faced really slow WebLogic start-up times after upgrade to recent versions of macOS Sierra. It turns out to be common problem related to JVM start-up on macOS systems, nothing to do with WebLogic itself. Solution is to register mapping between 127.0.0.1 and your computer name in hosts file, read more on Stack Overflow - Jvm takes a long time to resolve ip-address for localhost. This issue seems to appear with newer JVMs.

Originally WebLogic was starting up in 157 seconds:


After config was applied in hosts file, start-up time improved a lot, it is 24 seconds now:


Changes in hosts file - 127.0.0.1 was mapped with my computer name, along with localhost. Same applies for ::1 mapping:


You can get computer name in System Preferences -> Sharing:


Hope this hint will be useful for those developers, who are working on macOS.

Content and Experience Cloud REST API Consolidation

WebCenter Team - Tue, 2017-03-14 11:20

Authored by Victor Owuor, Senior Director, Software Development, Oracle

You are probably aware of our efforts to rebrand Content and Experience Cloud platform to offer a cohesive application suite that allows convenient development of applications, which take advantage of our product offerings.  It is our intent to present a consolidated package of our feature set, abstracting away the different applications that comprise the Content and Experience Cloud product suite.   A consolidation of our REST API is a critical part of that effort.

In previous releases, the REST API was in two separate packages, one for “Social” and another for “Documents.”  That separation did not reflect the needs of our developers, who, for example, may need to obtain a conversation related to a document.  Another use case that spanned both of those packages is a developer that wants to embed a document in a conversation.  Those use cases are typical for developers and it is our goal to streamline the experience when write such applications.  Other developer documentation, such as documentation of the Sites SDK or the DOCS Application Integration Framework was also separate.   We have started making changes to the product and documentation to address those issues.


The first change is already evident in documentation for Content and Experience Cloud, shown below.   We now have a single landing page for developers that clearly lists the separate aspects of the developer interfaces that we offer.  For the REST API, we have a link for Content Management that includes the documentation for the DOCS REST API, and other content management API Calls.  We also have a link for Collaboration that includes the documentation for what was previously the Social REST API, and other Collaboration API calls. Additionally, the same landing page includes information about the JavaScript SDK for developing Sites and the Application Integration Framework for extending Documents.

The REST service end-points will be as follows:

  1. Content Management
    • Documents - /documents/api/…Collaboration
    • Social - /social/api/… 

We have plans to do additional work to make it easier to develop using the API.   The most important of those plans is our effort to harmonize the treatment of various common REST resources, such as people, groups, documents across the suite, which we hope to achieve later this year.   We are also working to harmonize the authentication and security model across all REST end-points in the same timeframe.

Please try the new REST API and documentation and share any feedback or reaction in the comments.

Content and Experience Cloud REST API Consolidation

WebCenter Team - Tue, 2017-03-14 11:20

Authored by Victor Owuor, Senior Director, Software Development, Oracle

You are probably aware of our efforts to rebrand Content and Experience Cloud platform to offer a cohesive application suite that allows convenient development of applications, which take advantage of our product offerings.  It is our intent to present a consolidated package of our feature set, abstracting away the different applications that comprise the Content and Experience Cloud product suite.   A consolidation of our REST API is a critical part of that effort.

In previous releases, the REST API was in two separate packages, one for “Social” and another for “Documents.”  That separation did not reflect the needs of our developers, who, for example, may need to obtain a conversation related to a document.  Another use case that spanned both of those packages is a developer that wants to embed a document in a conversation.  Those use cases are typical for developers and it is our goal to streamline the experience when write such applications.  Other developer documentation, such as documentation of the Sites SDK or the DOCS Application Integration Framework was also separate.   We have started making changes to the product and documentation to address those issues.


The first change is already evident in documentation for Content and Experience Cloud, shown below.   We now have a single landing page for developers that clearly lists the separate aspects of the developer interfaces that we offer.  For the REST API, we have a link for Content Management that includes the documentation for the DOCS REST API, and other content management API Calls.  We also have a link for Collaboration that includes the documentation for what was previously the Social REST API, and other Collaboration API calls. Additionally, the same landing page includes information about the JavaScript SDK for developing Sites and the Application Integration Framework for extending Documents.

The REST service end-points will be as follows:

  1. Content Management
    • Documents - /documents/api/…Collaboration
    • Social - /social/api/… 

We have plans to do additional work to make it easier to develop using the API.   The most important of those plans is our effort to harmonize the treatment of various common REST resources, such as people, groups, documents across the suite, which we hope to achieve later this year.   We are also working to harmonize the authentication and security model across all REST end-points in the same timeframe.

Please try the new REST API and documentation and share any feedback or reaction in the comments.

Oracle Unveils Oracle US Tennis Awards for Young Professionals

Oracle Press Releases - Tue, 2017-03-14 11:00
Press Release
Oracle Unveils Oracle US Tennis Awards for Young Professionals Grants to assist former collegiate players in their pro careers

Indian Wells, Calif.—Mar 14, 2017

Oracle Corp. today announced the creation of the Oracle US Tennis Awards, player grants that are to be awarded annually at the BNP Paribas Open to assist young American players as they transition from college into the professional ranks.

The two $100,000 grants are to be awarded each year at the BNP Paribas Open to a male and female professional who have demonstrated exemplary sportsmanship and an aptitude for success on the pro tour. They must have played collegiate tennis prior to turning professional.

“Making the transition from college to the professional ranks is a real challenge,” said Oracle CEO Mark Hurd. “We hope these awards will provide young players with support to develop their games and improve their mental and physical fitness. Our goal is to grow the program and we invite input and support from other companies who are committed to U.S. athletics.”

The awards will be administered by the Intercollegiate Tennis Association, the governing body of college tennis.

“The ITA is proud of our partnership with Oracle,” said ITA CEO Timothy Russell. “Together we are growing the brand of college tennis and enhancing student-athlete experiences, and in doing so helping to return the leaders of tomorrow in America and around the world.”

Recipients will be selected by the newly created Oracle US Tennis Awards Advisory Council, a six-member body that includes individuals committed to the growth and improvement of American tennis. The inaugural members of the Advisory Council are:

  • Chris Evert: Former singles world No. 1; current ESPN tennis commentator; co-founder of Evert Tennis Academy.
  • Ilana Kloss: Former singles world No. 19; commissioner of Mylan World Team Tennis.
  • Peggy Michel: three-time grand slam doubles champion; played college tennis at Arizona State; current Assistant Tournament Director & Vice President of Sales at the BNP Paribas Open.
  • Dr. Timothy Russell: CEO ITA; college educator for three decades.
  • Martin Blackman: General Manager, USTA Player Development; played college tennis at Stanford.
  • Todd Martin: former singles world #4; CEO International Tennis Hall of Fame and Tournament Director, Dell Technologies Hall of Fame Open; played college tennis at Northwestern University.

“The transition into professional tennis is a great challenge that I experienced myself. The grants will be a great help to these young athletes,” said Todd Martin. “It’s great to see a company like Oracle step up to support American tennis.”

Added Chris Evert: “The people at Oracle understand that becoming a top player requires a strong support system. We are confident that these awards will help some young players through those daunting early years on the tour.”

Contact Info
Deborah Hellinger
Oracle
212.508.7935
deborah.hellinger@oracle.com
Dan Johnson
ITA
303.579.4878
djohnson@itatennis.com
About Oracle

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit www.oracle.com.

About The ITA

The Intercollegiate Tennis Association (ITA) is committed to serving college tennis and returning the leaders of tomorrow. As the governing body of college tennis, the ITA oversees men's and women's varsity tennis at NCAA Divisions I, II and III, NAIA and Junior/Community College divisions. The ITA administers a comprehensive awards and rankings program for men's and women's varsity players, coaches and teams in all divisions, providing recognition for their accomplishments on and off the court. For more information on the ITA, visit the ITA website at www.itatennis.com, like the ITA on Facebook or follow @ITA_Tennis on Twitter and Instagram.

Trademarks

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Safe Harbor

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

Talk to a Press Contact

Deborah Hellinger

  • 212.508.7935

Dan Johnson

  • 303.579.4878

Oracle Linux and Software Collections make it a great 'current' developer platform

Wim Coekaerts - Tue, 2017-03-14 10:57
Oracle Linux major releases happen every few years. Oracle Linux 7 is the current version and this was released back in 2014, Oracle Linux 6 is from 2011, etc... When a major release goes out the door, it sort of freezes the various packages at a point in time as well. It locks down which major version of glibc, etc.

Now, that doesn't mean that there won't be anything new added over time, of course security fixes and critical bugfixes get backported from new versions into these various packages and a good number of enhancements/features also get backported over the years. Very much so on the kernel side but in some cases or in a number of cases also in the various userspace packages. However for the most part the focus is on stability and consistency. This is also the case with the different tools and compiler/languages. A concrete example would be, OL7 provides Python 2.7.5. This base release of python will not change in OL7 in newer updates, doing a big chance would break compatibility etc so it's kept stable at 2.7.5.

A very important thing to keep reminding people of, however, again, is the fact that CVEs do get backported into these versions. I often hear someone ask if we ship a newer version of, say, openssl, because some CVE or other is fixed in that newer version - but typically that CVE would also be fixed in the versions we ship with OL. There is a difference between openssl the open source project and CVE's fixed 'upstream' and openssl shipped as part of Oracle Linux versions and maintained and bug fixed overtime with backports from upstream. We take care of critical bugs and security fixes in the current shipping versions.

Anyway - there are other Linux distributions out there that 'evolve' much more frequently and by doing so, out of the box tend to come with newer versions of libraries and tools and packages and that makes it very attractive for developers that are not bound to longer term stability and compatibility. So the developer goes off and installs the latest version of everything and writes their apps using that. That's a fine model in some cases but when you have enterprise apps that might be deployed for many years and have a dependency on certain versions of scripting languages or libraries or what have you, you can't just replace those with something that's much newer, in particular much newer major versions. I am sure many people will agree that if you have an application written in python using 2.7.5 and run that in production, you're not going to let the sysadmin or so just go rip that out and replace it with python 3.5 and assume it all just works and is transparently compatible....

So does that mean we are stuck? No... there is a yum repository called Software Collections Library which we make available to everyone on our freely accessible yum server. That Library gets updated on a regular basis, we are at version 2.3 right now, and it containers newer versions of many popular packages, typically newer compilers, toolkits etc, (such as GCC, Python, PHP, Ruby...) Things that developers want to use and are looking for more recent versions.

The channel is not enabled by default, you have to go in and edit /etc/yum.repos.d/public-yum-ol7.repo and set the ol7_software_collections' repo to enabled=1. When you do that, you can then go and install the different versions that are offered. You can just browse the repo using yum or just look online. (similar channels exist for Oracle Linux 6). When you go and install these different versions, they get installed in /opt and they won't replace the existing versions. So if you have python installed by default with OL7 (2.7.5) and install Python 3.5 from the software collections, this new version goes into /opt/rh/rh-python35. You can then use the scl utility to selectively enable which application uses which version.
An example :scl enable rh-python35 -- bash

One little caveat to keep in mind, if you have an early version of OL7 or OL6 installed, we do not modify the /etc/yum.repo.d/public-yum-ol7.repo file after initial installation (because we might overwrite changes you made) so it is always a good idea to get the latest version from our yum server. (You can find them here.) The channel/repo name might have changed or a new one could have been added or so...

As you can see, Oracle Linux is/can be a very current developer platform. The packages are there, they are just provided in a model that keeps stability and consistency. There is no need to go download upstream package source code and compile it yourself and replacing system toolkits/compilers that can cause incompatibilities.

Oracle Linux and Software Collections make it a great 'current' developer platform

Wim Coekaerts - Tue, 2017-03-14 10:57
Oracle Linux major releases happen every few years. Oracle Linux 7 is the current version and this was released back in 2014, Oracle Linux 6 is from 2011, etc... When a major release goes out the door, it sort of freezes the various packages at a point in time as well. It locks down which major version of glibc, etc.

Now, that doesn't mean that there won't be anything new added over time, of course security fixes and critical bugfixes get backported from new versions into these various packages and a good number of enhancements/features also get backported over the years. Very much so on the kernel side but in some cases or in a number of cases also in the various userspace packages. However for the most part the focus is on stability and consistency. This is also the case with the different tools and compiler/languages. A concrete example would be, OL7 provides Python 2.7.5. This base release of python will not change in OL7 in newer updates, doing a big chance would break compatibility etc so it's kept stable at 2.7.5.

A very important thing to keep reminding people of, however, again, is the fact that CVEs do get backported into these versions. I often hear someone ask if we ship a newer version of, say, openssl, because some CVE or other is fixed in that newer version - but typically that CVE would also be fixed in the versions we ship with OL. There is a difference between openssl the open source project and CVE's fixed 'upstream' and openssl shipped as part of Oracle Linux versions and maintained and bug fixed overtime with backports from upstream. We take care of critical bugs and security fixes in the current shipping versions.

Anyway - there are other Linux distributions out there that 'evolve' much more frequently and by doing so, out of the box tend to come with newer versions of libraries and tools and packages and that makes it very attractive for developers that are not bound to longer term stability and compatibility. So the developer goes off and installs the latest version of everything and writes their apps using that. That's a fine model in some cases but when you have enterprise apps that might be deployed for many years and have a dependency on certain versions of scripting languages or libraries or what have you, you can't just replace those with something that's much newer, in particular much newer major versions. I am sure many people will agree that if you have an application written in python using 2.7.5 and run that in production, you're not going to let the sysadmin or so just go rip that out and replace it with python 3.5 and assume it all just works and is transparently compatible....

So does that mean we are stuck? No... there is a yum repository called Software Collections Library which we make available to everyone on our freely accessible yum server. That Library gets updated on a regular basis, we are at version 2.3 right now, and it containers newer versions of many popular packages, typically newer compilers, toolkits etc, (such as GCC, Python, PHP, Ruby...) Things that developers want to use and are looking for more recent versions.

The channel is not enabled by default, you have to go in and edit /etc/yum.repos.d/public-yum-ol7.repo and set the ol7_software_collections' repo to enabled=1. When you do that, you can then go and install the different versions that are offered. You can just browse the repo using yum or just look online. (similar channels exist for Oracle Linux 6). When you go and install these different versions, they get installed in /opt and they won't replace the existing versions. So if you have python installed by default with OL7 (2.7.5) and install Python 3.5 from the software collections, this new version goes into /opt/rh/rh-python35. You can then use the scl utility to selectively enable which application uses which version.
An example :

scl enable rh-python35 -- bash 

One little caveat to keep in mind, if you have an early version of OL7 or OL6 installed, we do not modify the /etc/yum.repo.d/public-yum-ol7.repo file after initial installation (because we might overwrite changes you made) so it is always a good idea to get the latest version from our yum server. (You can find them here.) The channel/repo name might have changed or a new one could have been added or so...

As you can see, Oracle Linux is/can be a very current developer platform. The packages are there, they are just provided in a model that keeps stability and consistency. There is no need to go download upstream package source code and compile it yourself and replacing system toolkits/compilers that can cause incompatibilities.

Postgres Barman and DMK

Yann Neuhaus - Tue, 2017-03-14 10:21

As PostgreSQL is more and more present in our client’s infrastructure, I wanted to describe you the barman installation and configuration. Barman is the backup and recovery tool for PostgreSQL, I configured it using DMK out tool for infrastructure administrators on Oracle, MySQL, and PostgreSQL.

I used two virtual severs running under RedHat Enterprise Libux 7.1, one for PostgreSQL database server (pg1) ands the second for barman (pg2).

At first I install PostgreSQL 9.6 on both servers:

[root@pg1 ~]# wget https://download.postgresql.org/pub/repos/yum/9.6/redhat/
rhel-7-x86_64/pgdg-redhat96-9.6-3.noarch.rpm
--2017-02-06 15:08:05--  https://download.postgresql.org/pub/repos/yum/9.6/redhat
/rhel-7-x86_64/pgdg-redhat96-9.6-3.noarch.rpm
Resolving download.postgresql.org (download.postgresql.org)... 
217.196.149.55, 174.143.35.246, 87.238.57.227, ...
Connecting to download.postgresql.org (download.postgresql.org)|
217.196.149.55|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 4816 (4.7K) [application/x-redhat-package-manager]
Saving to: âpgdg-redhat96-9.6-3.noarch.rpm
 
100%[======================================>] 4,816       
 
2017-02-06 15:08:05 (2.71 MB/s) - pgdg-redhat96-9.6-3.noarch.rpm saved 
 
[root@pg1 ~]# sudo yum localinstall -y pgdg-redhat96-9.6-3.noarch.rpm
Examining pgdg-redhat96-9.6-3.noarch.rpm: pgdg-redhat96-9.6-3.noarch
Marking pgdg-redhat96-9.6-3.noarch.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package pgdg-redhat96.noarch 0:9.6-3 will be installed
--> Finished Dependency Resolution
 
Dependencies Resolved
 
================================================================================
 Package           Arch       Version     Repository                       Size
================================================================================
Installing:
 pgdg-redhat96     noarch     9.6-3       /pgdg-redhat96-9.6-3.noarch     2.7 k
 
Transaction Summary
================================================================================
Install  1 Package
 
Total size: 2.7 k
Installed size: 2.7 k
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : pgdg-redhat96-9.6-3.noarch                                   1/1
  Verifying  : pgdg-redhat96-9.6-3.noarch                                   1/1
 
Installed:
  pgdg-redhat96.noarch 0:9.6-3
 
Complete!

I install barman on the barman server (pg2):

[root@pg2 ~]# sudo yum install barman
pgdg96                                                   | 4.1 kB     00:00
(1/2): pgdg96/7Server/x86_64/group_gz                      |  249 B   00:00
(2/2): pgdg96/7Server/x86_64/primary_db                    | 129 kB   00:02
Resolving Dependencies
--> Running transaction check
---> Package barman.noarch 0:2.1-1.rhel7 will be installed
--> Processing Dependency: python-psycopg2 >= 2.4.2 for package:
barman-2.1-1.rhel7.noarch
--> Processing Dependency: python-argh >= 0.21.2 for package: 
barman-2.1-1.rhel7.noarch
--> Processing Dependency: python-dateutil for package: 
barman-2.1-1.rhel7.noarch
--> Processing Dependency: python-argcomplete for package: 
barman-2.1-1.rhel7.noarch
--> Running transaction check
---> Package python-argcomplete.noarch 0:0.3.7-1.rhel7 will be installed
---> Package python-argh.noarch 0:0.23.0-1.rhel7 will be installed
---> Package python-dateutil.noarch 1:2.5.3-3.rhel7 will be installed
--> Processing Dependency: python-six for package: 1:
python-dateutil-2.5.3-3.rhel7.noarch
---> Package python-psycopg2.x86_64 0:2.6.2-3.rhel7 will be installed
--> Processing Dependency: postgresql96-libs for package: 
python-psycopg2-2.6.2-3.rhel7.x86_64
--> Running transaction check
---> Package postgresql96-libs.x86_64 0:9.6.1-1PGDG.rhel7 will be installed
---> Package python-six.noarch 0:1.9.0-2.el7 will be installed
--> Finished Dependency Resolution
 
Dependencies Resolved
 
================================================================================
 Package                Arch       Version                 Repository      Size
================================================================================
Installing:
 barman                 noarch     2.1-1.rhel7             pgdg96         248 k
Installing for dependencies:
 postgresql96-libs      x86_64     9.6.1-1PGDG.rhel7       pgdg96         308 k
 python-argcomplete     noarch     0.3.7-1.rhel7           pgdg96          23 k
 python-argh            noarch     0.23.0-1.rhel7          pgdg96          33 k
 python-dateutil        noarch     1:2.5.3-3.rhel7         pgdg96         241 k
 python-psycopg2        x86_64     2.6.2-3.rhel7           pgdg96         131 k
 python-six             noarch     1.9.0-2.el7             ol7_latest      28 k
 
Transaction Summary
================================================================================
Install  1 Package (+6 Dependent packages)
 
Total download size: 1.0 M
Installed size: 3.6 M
Is this ok [y/d/N]: y
Downloading packages:
(1/7): barman-2.1-1.rhel7.noarch.rpm                       | 248 kB   00:03
(2/7): python-argcomplete-0.3.7-1.rhel7.noarch.rpm         |  23 kB   00:00
(3/7): python-argh-0.23.0-1.rhel7.noarch.rpm               |  33 kB   00:00
(4/7): postgresql96-libs-9.6.1-1PGDG.rhel7.x86_64.rpm      | 308 kB   00:04
(5/7): python-six-1.9.0-2.el7.noarch.rpm                   |  28 kB   00:00
(6/7): python-dateutil-2.5.3-3.rhel7.noarch.rpm            | 241 kB   00:01
(7/7): python-psycopg2-2.6.2-3.rhel7.x86_64.rpm            | 131 kB   00:01
--------------------------------------------------------------------------------
Total                                              163 kB/s | 1.0 MB  00:06
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : python-argh-0.23.0-1.rhel7.noarch                            1/7
  Installing : postgresql96-libs-9.6.1-1PGDG.rhel7.x86_64                   2/7
  Installing : python-psycopg2-2.6.2-3.rhel7.x86_64                         3/7
  Installing : python-argcomplete-0.3.7-1.rhel7.noarch                      4/7
  Installing : python-six-1.9.0-2.el7.noarch                                5/7
  Installing : 1:python-dateutil-2.5.3-3.rhel7.noarch                       6/7
  Installing : barman-2.1-1.rhel7.noarch                                    7/7
  Verifying  : python-psycopg2-2.6.2-3.rhel7.x86_64                         1/7
  Verifying  : python-six-1.9.0-2.el7.noarch                                2/7
  Verifying  : python-argcomplete-0.3.7-1.rhel7.noarch                      3/7
  Verifying  : postgresql96-libs-9.6.1-1PGDG.rhel7.x86_64                   4/7
  Verifying  : python-argh-0.23.0-1.rhel7.noarch                            5/7
  Verifying  : barman-2.1-1.rhel7.noarch                                    6/7
  Verifying  : 1:python-dateutil-2.5.3-3.rhel7.noarch                       7/7
 
Installed:
  barman.noarch 0:2.1-1.rhel7
 
Dependency Installed:
  postgresql96-libs.x86_64 0:9.6.1-1PGDG.rhel7
  python-argcomplete.noarch 0:0.3.7-1.rhel7
  python-argh.noarch 0:0.23.0-1.rhel7
  python-dateutil.noarch 1:2.5.3-3.rhel7
  python-psycopg2.x86_64 0:2.6.2-3.rhel7
  python-six.noarch 0:1.9.0-2.el7
Complete!

Everything is installed on both servers :

– PostgreSQL 9.6

– DMK last version

– barman

Now we configure as follows:

The barman server is pg2 : 192.168.1.101

The database server is pg1 : 192.168.1.100

 

On the database server, we create a barman user:

postgres@:5432) [postgres] > create user barman superuser login encrypted password 
'barman';
CREATE ROLE

And a barman_streaming user:

postgres@: [postgres] > create user barman_streaming replication encrypted password 
'barman';
CREATE ROLE

We modify the following parameters max_replication_slots (which specifies the maximum number of replication slots the server can support), and max_wal_senders (specifies the maximum number of simultaneously running wal sender processes):

postgres@:5432) [postgres] > alter system set max_replication_slots=10;
ALTER SYSTEM
postgres@:5432) [postgres] > alter system set max_wal_senders=10;
ALTER SYSTEM

As those previous parameters have been modified, we need to restart the database, we use pgrestart which is a DMK alias for pg_ctl -D ${PGDATA} restart -m fast

postgres@pg1:/home/postgres/ [PG1] pgrestart
waiting for server to shut down.... done
server stopped
server starting
postgres@pg1:/home/postgres/ [PG1] 2017-02-06 15:59:14.756 CET - 1 - 17008 -  
- @ LOG:  redirecting log output to logging collector process
2017-02-06 15:59:14.756 CET - 2 - 17008 -  - 
@ HINT:  Future log output will appear in directory 
"/u01/app/postgres/admin/PG1/pg_log".

We modify the pg_hba.conf on the barman server in order to allow connections from the barman server to the database server as follows:

host    all             barman          192.168.1.101/24       md5
host    replication     barman_streaming 192.168.1.101/24      md5

We modify the .pgpass file on the barman server in order not to be asked for passwords:

postgres@pg2:/home/postgres/ [pg96] cat .pgpass
*:*:*:postgres:postgres
192.168.1.100:*:*:barman:barman
192.168.1.100:*:*:barman_streaming:barman

Finally we test the connection from the barman server to the database server:

postgres@pg2:/home/postgres/ [pg96] psql -c 'select version()'
 -U barman -h 192.168.1.100 -p 5432 postgres
                                                 version
 
--------------------------------------------------------------------------------

 PostgreSQL 9.6.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (
Red Hat 4.8.5-11), 64-bit
(1 row)
postgres@pg2:/home/postgres/ [pg96] psql -U barman_streaming -h 192.168.1.100 
-p 5432 -c "IDENTIFY_SYSTEM" replication=1
      systemid       | timeline |  xlogpos  | dbname
---------------------+----------+-----------+--------
 6384063115439945376 |        1 | 0/F0006F0 |
(1 row)

Now it’s time to create a configuration file pg96.conf in $DMK_HOME/etc/barman.d in the barman server:

[pg96]
description =  "PostgreSQL 9.6 server"
conninfo = host=192.168.1.100 port=5432 user=barman dbname=postgres
backup_method = postgres
streaming_conninfo = host=192.168.1.100 port=5432 user=barman_streaming 
dbname=postgres
streaming_wals_directory = /u99/received_wal
streaming_archiver = on
slot_name = barman

We create a barman.conf file in $DMK_HOME/etc as follows, mainly defining the barman_user, the configuration file directory and the barman backup home, the barman lock directory and the log directory:

postgres@pg2:/u01/app/postgres/local/dmk/etc/ [pg96] cat barman.conf
; Barman, Backup and Recovery Manager for PostgreSQL
; http://www.pgbarman.org/ - http://www.2ndQuadrant.com/
;
; Main configuration file
 
[barman]
; System user
barman_user = postgres
 
; Directory of configuration files. Place your sections in separate files 
with .conf extension
; For example place the 'main' server section in /etc/barman.d/main.conf
configuration_files_directory = /u01/app/postgres/local/dmk/etc/barman.d
 
; Main directory
barman_home = /u99/backup
 
; Locks directory - default: %(barman_home)s
barman_lock_directory = /u01/app/postgres/local/dmk/etc/
 
; Log location
log_file = /u01/app/postgres/local/dmk/log/barman.log
 
; Log level (see https://docs.python.org/3/library/logging.html#levels)
log_level = DEBUG
 
; Default compression level: possible values are None (default), 
bzip2, gzip, pigz, pygzip or pybzip2
compression = gzip
 
; Pre/post backup hook scripts
;pre_backup_script = env | grep ^BARMAN
;pre_backup_retry_script = env | grep ^BARMAN
;post_backup_retry_script = env | grep ^BARMAN
;post_backup_script = env | grep ^BARMAN
 
; Pre/post archive hook scripts
;pre_archive_script = env | grep ^BARMAN
;pre_archive_retry_script = env | grep ^BARMAN
;post_archive_retry_script = env | grep ^BARMAN
;post_archive_script = env | grep ^BARMAN
 
; Global retention policy (REDUNDANCY or RECOVERY WINDOW) - default empty
retention_policy = RECOVERY WINDOW OF 4 WEEKS
 
; Global bandwidth limit in KBPS - default 0 (meaning no limit)
;bandwidth_limit = 4000
 
; Immediate checkpoint for backup command - default false
;immediate_checkpoint = false
 
; Enable network compression for data transfers - default false
;network_compression = false
 
; Number of retries of data copy during base backup after an error - default 0
;basebackup_retry_times = 0
 
; Number of seconds of wait after a failed copy, before retrying - default 30
;basebackup_retry_sleep = 30
 
; Maximum execution time, in seconds, per server
; for a barman check command - default 30
;check_timeout = 30
 
; Time frame that must contain the latest backup date.
; If the latest backup is older than the time frame, barman check
; command will report an error to the user.
; If empty, the latest backup is always considered valid.
; Syntax for this option is: "i (DAYS | WEEKS | MONTHS)" where i is an
; integer > 0 which identifies the number of days | weeks | months of
; validity of the latest backup for this check. Also known as 'smelly backup'.
;last_backup_maximum_age =
 
; Minimum number of required backups (redundancy)
;minimum_redundancy = 1

 

In order to enable streaming of transaction logs and to use replication slots, we run the following command on the barman server:

postgres@pg2:/u01/app/postgres/local/dmk/etc/ [pg96] barman receive-wal 
--create-slot pg96
Creating physical replication slot 'barman' on server 'pg96'
Replication slot 'barman' created

Then we can test:

We can force a log switch on the database server:

postgres@pg2:/u01/app/postgres/local/dmk/etc/ [pg96] barman switch-xlog 
--force pg96
The xlog file 00000001000000000000000F has been closed on server 'pg96'

 

We start receive wal:

postgres@pg2:/u99/received_wal/ [pg96] barman -c 
/u01/app/postgres/local/dmk/etc/barman.conf receive-wal pg96
Starting receive-wal for server pg96
pg96: pg_receivexlog: starting log streaming at 0/68000000 (timeline 3)
pg96: pg_receivexlog: finished segment at 0/69000000 (timeline 3)
pg96: pg_receivexlog: finished segment at 0/6A000000 (timeline 3)
pg96: pg_receivexlog: finished segment at 0/6B000000 (timeline 3)
pg96: pg_receivexlog: finished segment at 0/6C000000 (timeline 3)

 

We can check the barman configuration:

postgres@pg2:/u99/restore_test/ [pg96] barman check pg96
Server pg96:
                    PostgreSQL: OK
                    superuser: OK
                    PostgreSQL streaming: OK
                    wal_level: OK
                    replication slot: OK
                    directories: OK
                    retention policy settings: OK
                    backup maximum age: OK (no last_backup_maximum_age provided)
                    compression settings: OK
                    failed backups: FAILED (there are 1 failed backups)
                    minimum redundancy requirements: OK (have 3 backups, 
                    expected at least 0)
                    pg_basebackup: OK
                    pg_basebackup compatible: OK
                    pg_basebackup supports tablespaces mapping: OK
                    pg_receivexlog: OK
                    pg_receivexlog compatible: OK
                    receive-wal running: OK
                    archiver errors: OK

We can run a barman archive-wal command:

postgres@pg2:/home/postgres/ [pg96] barman archive-wal pg96
Processing xlog segments from streaming for pg96
                    00000003.history
                    000000030000000000000067
                    000000030000000000000068

And finally you can run a backup with the command:

postgres@pg2:/home/postgres/ [pg96] barman backup pg96
Starting backup using postgres method for server pg96 in 
/u99/backup/pg96/base/20170214T103226
Backup start at xlog location: 0/69000060 (000000030000000000000069, 00000060)
Copying files.
Copy done.
Finalising the backup.
Backup size: 60.1 MiB
Backup end at xlog location: 0/6B000000 (00000003000000000000006A, 00000000)
Backup completed
Processing xlog segments from streaming for pg96
                    000000030000000000000069

We can list the backups :

postgres@pg2:/u02/pgdata/ [pg96] barman list-backup pg96
pg96 20170214T103226 - Tue Feb 14 09:32:27 2017 - Size: 60.2 MiB - WAL Size: 0 B 
(tablespaces: tab1:/u02/pgdata/PG1/mytab)
pg96 20170207T061338 - Tue Feb  7 06:19:38 2017 - Size: 29.0 MiB - WAL Size: 0 B
pg96 20170207T060633 - Tue Feb  7 06:12:33 2017 - Size: 29.0 MiB - WAL Size: 0 B

 

We have the possibility to test a restore for example on the barman server :

postgres@pg2:/u02/pgdata/ [pg96] barman recover pg96 20170214T103226 
/u99/restore_test/
Starting local restore for server pg96 using backup 20170214T103226
Destination directory: /u99/restore_test/
                    24648, tab1, /u02/pgdata/PG1/mytab
Copying the base backup.
Copying required WAL segments.
Generating archive status files
Identify dangerous settings in destination directory.
 
IMPORTANT
These settings have been modified to prevent data losses
 
postgresql.conf line 71: archive_command = false
postgresql.auto.conf line 4: archive_command = false

Your PostgreSQL server has been successfully prepared for recovery, the /u99/test_restore directory contains:

postgres@pg2:/u99/restore_test/ [pg96] ll

total 64
-rw-------  1 postgres postgres  208 Feb 14 10:32 backup_label
-rw-------  1 postgres postgres  207 Feb 14 10:32 backup_label.old
drwx------ 10 postgres postgres   98 Feb 14 10:32 base
drwx------  2 postgres postgres 4096 Feb 14 10:32 global
drwx------  2 postgres postgres    6 Feb 14 10:32 mytab
drwx------  2 postgres postgres   17 Feb 14 10:32 pg_clog
drwx------  2 postgres postgres    6 Feb 14 10:32 pg_commit_ts
drwx------  2 postgres postgres    6 Feb 14 10:32 pg_dynshmem
-rw-------  1 postgres postgres 4416 Feb 14 10:32 pg_hba.conf
-rw-------  1 postgres postgres 4211 Feb 14 10:32 pg_hba.conf_conf
-rw-------  1 postgres postgres 1636 Feb 14 10:32 pg_ident.conf
drwx------  4 postgres postgres   65 Feb 14 10:32 pg_logical
drwx------  4 postgres postgres   34 Feb 14 10:32 pg_multixact
drwx------  2 postgres postgres   17 Feb 14 10:32 pg_notify
drwx------  2 postgres postgres    6 Feb 14 10:32 pg_replslot
drwx------  2 postgres postgres    6 Feb 14 10:32 pg_serial
drwx------  2 postgres postgres    6 Feb 14 10:32 pg_snapshots
drwx------  2 postgres postgres    6 Feb 14 10:32 pg_stat
drwx------  2 postgres postgres    6 Feb 14 10:32 pg_stat_tmp
drwx------  2 postgres postgres   17 Feb 14 10:32 pg_subtrans
drwx------  2 postgres postgres   18 Feb 14 10:32 pg_tblspc
drwx------  2 postgres postgres    6 Feb 14 10:32 pg_twophase
-rw-------  1 postgres postgres    4 Feb 14 10:32 PG_VERSION
drwx------  3 postgres postgres   81 Feb 14 10:39 pg_xlog
-rw-------  1 postgres postgres  391 Feb 14 10:39 postgresql.auto.conf
-rw-------  1 postgres postgres  358 Feb 14 10:32 postgresql.auto.conf.origin
-rw-------  1 postgres postgres 7144 Feb 14 10:39 postgresql.conf
-rw-------  1 postgres postgres 7111 Feb 14 10:32 postgresql.conf.origin
-rw-------  1 postgres postgres   56 Feb 14 10:32 recovery.done

If you need to  restore your backup on the pg1 original database server, you have to use the –remote-ssh-command as follows (you specify the hostname where you want restore, and the PGDATA directory)

postgres@pg2:/home/postgres/.ssh/ [pg96] barman recover --remote-ssh-command "ssh postgres@pg1" pg96 20170214T103226 /u02/pgdata/PG1
Starting remote restore for server pg96 using backup 20170214T103226
Destination directory: /u02/pgdata/PG1
       24648, tab1, /u02/pgdata/PG1/mytab
Copying the base backup.
Copying required WAL segments.
Generating archive status files
Identify dangerous settings in destination directory.
 
IMPORTANT
These settings have been modified to prevent data losses
 
postgresql.conf line 71: archive_command = false
postgresql.auto.conf line 4: archive_command = false
 
Your PostgreSQL server has been successfully prepared for recovery!

You also have the possibility to realise a point in time recovery.

In my PG1 database I create a table employes and insert some data :

postgres@[local]:5432) [blubb] > create table employes (name varchar(10));
CREATE TABLE
(postgres@[local]:5432) [blubb] > insert into employes values ('fiona');
INSERT 0 1
(postgres@[local]:5432) [blubb] > insert into employes values ('cathy');
INSERT 0 1
(postgres@[local]:5432) [blubb] > insert into employes values ('helene');
INSERT 0 1
(postgres@[local]:5432) [blubb] > select * from employes;
  name  
--------
 fiona
 cathy
 helene

A few minutes later I insert some more records in the employes table:

postgres@[local]:5432) [blubb] > insert into employes values ('larry');
INSERT 0 1
(postgres@[local]:5432) [blubb] > insert into employes values ('bill');
INSERT 0 1
(postgres@[local]:5432) [blubb] > insert into employes values ('steve');
INSERT 0 1
(postgres@[local]:5432) [blubb] > select * from employes;
  name  
--------
 fiona
 cathy
 helene
 larry
 bill
 steve

The first data were create at 15:15, let’s see if the pitr barman restore works correctly:

I stop the PG1 database :

postgres@pg1:/u02/pgdata/ [PG1] pgstop
waiting for server to shut down....... done
server stopped

I delete the PGDATA directory:

postgres@pg1:/u02/pgdata/ [PG1] rm -rf PG1

And from the barman server I run the pitr recovery command using the –target-time argument:

postgres@pg2:/home/postgres/ [pg96] barman recover --remote-ssh-command "ssh postgres@pg1" pg96 
--target-time "2017-02-14 15:15:48"  20170214T141055 /u02/pgdata/PG1 
Starting remote restore for server pg96 using backup 20170214T141055
Destination directory: /u02/pgdata/PG1
Doing PITR. Recovery target time: '2017-02-14 15:15:48'
       24648, tab1, /u02/pgdata/PG1/mytab
Copying the base backup.
Copying required WAL segments.
Generating recovery.conf
Identify dangerous settings in destination directory.
 
IMPORTANT
These settings have been modified to prevent data losses
 
postgresql.conf line 72: archive_command = false
postgresql.auto.conf line 4: archive_command = false
 
Your PostgreSQL server has been successfully prepared for recovery!

I restart my PG1 database the data are correctly restored, just before the Larry, Bill and Steve insertion into the employes tables

postgres@[local]:5432) [blubb] > select * from employes;
  name  
--------
 fiona
 cathy
 helene
(3 rows)

 

 

Cet article Postgres Barman and DMK est apparu en premier sur Blog dbi services.

Pages

Subscribe to Oracle FAQ aggregator