Feed aggregator

How to tune a create table that runs in loop

Tom Kyte - Tue, 2020-02-04 09:02
Hi All, can someone help me to tune this peice of code. It is taking 45 minutes to complete the execution as of now. We have tried taking off the loop and creating 5 different create statements but did gain just 2 minutes Each of the table have and...
Categories: DBA Blogs

Truncate Multiple tables in a single line statement.

Tom Kyte - Tue, 2020-02-04 09:02
My question: Is it possible to truncate multiple tables in a single line statement? Firstly, thanks for giving this opportunity to ask a question. I have developed a code to truncate multiple tables in a single line statement. Please refer the ...
Categories: DBA Blogs

Getting error -12842 : ORA-12842: CURSOR INVALIDATED DURING PARALLEL EXECUTION

Tom Kyte - Tue, 2020-02-04 09:02
Below is my scenario. We are getting error randomly in production. when we restart the process error disappears. we are not able to reproduce even in lower environment. Can you please help in letting us know the potential issue with insert statement ...
Categories: DBA Blogs

How to retrieve the 200millions of record into txt file using select query?

Tom Kyte - Tue, 2020-02-04 09:02
Hi Tom, We have 200+millions of records in our table, we have to retrieve all the records into text file without impacting the performance. I tried using DBMS_parallel_execute but this doesn?t have return type. Is there a way to get all Millions of...
Categories: DBA Blogs

EDB PEM – Monitor your Postgres cluster

Yann Neuhaus - Tue, 2020-02-04 01:00

In my last post, I explained, how to setup a HA Postgres Cluster using EDB Postgres Advanced Server and Failover Manager. As a next step, we want to install the EDB Postgres Enterprise Manager to monitor what we setup before.

Introduction

There are, of course, many good tools to monitor your Postgres Cluster, but in case you run your Postgres Cluster using EDB tool, you should really think about using EDB Postgres Enterprise Manager . It allows you to monitor EDB Postgres Advanced Server Clusters as well as open source Postgres Clusters. But PEM is not only a nice GUI, it alerts and you can tune your clusters from one single point.

Installation

Before you start with the Installation of PEM, you need a PostgreSQL cluster on a host.
Once you have it, you can go on with the installation of edb-pem-server.

$ sudo yum install wxBase mod_wsgi mod_ssl edb-pem-server -y

Afterwards you need to set a password for the enterprisedb user in your cluster

$ psql
postgres=# alter user enterprisedb with password '******';
ALTER ROLE
postgres=# \q

That’s it. So let’s go on with the configuration.

Configuration

EDB delivers the PEM Server with a nice script to configure the server. This makes it really easy!

$ sudo /usr/edb/pem/bin/configure-pem-server.sh
------------------------------------------------------
 EDB Postgres Enterprise Manager
 -----------------------------------------------------
Install type: 1:Web Services and Database, 2:Web Services 3: Database [ ] :1
Enter local database server installation path (i.e. /usr/edb/as10 , or /usr/pgsql-10, etc.) [ ] :/usr/edb/as11
Enter database super user name [ ] :enterprisedb
Enter database server port number [ ] :5444
Enter database super user password [ ] :
Please enter CIDR formatted network address range that agents will connect to the server from, to be added to the server's pg_hba.conf file. For example, 192.168.1.0/24 [ 0.0.0.0/0 ] :192.168.22.53/32
Enter database systemd unit file or init script name (i.e. edb-as-10 or postgresql-10, etc.) [ ] :edb-as-11
Please specify agent certificate path (Script will attempt to create this directory, if it does not exists) [ ~/.pem/ ] :
CREATE EXTENSION
[Info] Configuring database server.
CREATE DATABASE
..
..
[Info] Configured the webservice for EDB Postgres Enterprise Manager (PEM) Server on port '8443'.
[Info] PEM server can be accessed at https://192.168.22.53:8443/pem at your browser

Now you can try to access PEM with your Webbrowser.

Agent installation and configuration

Once the PEM Server is setup, you need to install the edb-pem-agent on all hosts, which you want to monitor.

$ sudo yum install edb-pem-agent -y

Be sure to have the pg_hba.conf entries correct on the Agent and PEM Node.
As soon as the installation is finished, you can register the agent to the PEM Server. For this you need the IP, the port and the user for PEM.

$ sudo bash
$ export PEM_MONITORED_SERVER_PASSWORD=edb
$ export PEM_SERVER_PASSWORD=edb
$ /usr/edb/pem/agent/bin/pemworker --register-agent --pem-server 192.168.22.53 --pem-port 5400 --pem-user enterprisedb --allow_server_restart true --allow-batch-probes true --batch-script-user enterprisedb 

As last step you need to add the configuration to the agent.cfg and start/enable the pemagent service.

$ echo "allow_streaming_replication=true" >> /usr/edb/pem/agent/etc/agent.cfg
$ echo "ca_file=/usr/libexec/libcurl-pem/share/certs/ca-bundle.crt" >> /usr/edb/pem/agent/etc/agent.cfg
$ systemctl enable pemagent
$ systemctl start pemagent

Now you can have a look at the PEM Dashboard and you will see the registered agents.

As a next step you can add the PostgreSQL clusters to PEM, as I already explained that here I want go into this here.

Conclusion

Now you can enjoy the full monitoring experience using PEM. As well as all the nice put into graphs.

Cet article EDB PEM – Monitor your Postgres cluster est apparu en premier sur Blog dbi services.

SQL query join by GUID

Tom Kyte - Tue, 2020-02-04 00:02
Hello, Ask Tom Team. I have to create a report to show some business data. The data to show is stored in two different Oracle databases. I have to write a SQL query joining two the databases (dblink). The two databases were designed for very diffe...
Categories: DBA Blogs

AZORA with Viscosity lunch and learn Thursday 2/13/20

Bobby Durrett's DBA Blog - Mon, 2020-02-03 17:51

Quick note. The next AZORA meetup will be next Thursday, 2/13/2020.

#meetup_oembed .mu_clearfix:after { visibility: hidden; display: block; font-size: 0; content: " "; clear: both; height: 0; }* html #meetup_oembed .mu_clearfix, *:first-child+html #meetup_oembed .mu_clearfix { zoom: 1; }#meetup_oembed { background:#eee;border:1px solid #ccc;padding:10px;-moz-border-radius:3px;-webkit-border-radius:3px;border-radius:3px;margin:0; font-family: 'Helvetica Neue', Helvetica, Arial, sans-serif; font-size: 12px; }#meetup_oembed h3 { font-weight:normal; margin:0 0 10px; padding:0; line-height:26px; font-family:Georgia,Palatino,serif; font-size:24px }#meetup_oembed p { margin: 0 0 10px; padding:0; line-height:16px; }#meetup_oembed img { border:none; margin:0; padding:0; }#meetup_oembed a, #meetup_oembed a:visited, #meetup_oembed a:link { color: #1B76B3; text-decoration: none; cursor: hand; cursor: pointer; }#meetup_oembed a:hover { color: #1B76B3; text-decoration: underline; }#meetup_oembed a.mu_button { font-size:14px; -moz-border-radius:3px;-webkit-border-radius:3px;border-radius:3px;border:2px solid #A7241D;color:white!important;text-decoration:none;background-color: #CA3E47; background-image: -moz-linear-gradient(top, #ca3e47, #a8252e); background-image: -webkit-gradient(linear, left bottom, left top, color-stop(0, #a8252e), color-stop(1, #ca3e47));disvplay:inline-block;padding:5px 10px; }#meetup_oembed a.mu_button:hover { color: #fff!important; text-decoration: none; }#meetup_oembed .photo { width:50px; height:50px; overflow:hidden;background:#ccc;float:left;margin:0 5px 0 0;text-align:center;padding:1px; }#meetup_oembed .photo img { height:50px }#meetup_oembed .number { font-size:18px; }#meetup_oembed .thing { text-transform: uppercase; color: #555; }
Kickstart to the Cloud Begin Your Journey and Optimize Along the Way

Thursday, Feb 13, 2020, 11:00 AM

The Henry
4455 E Camelback Rd Phoenix, AZ

2 AZORAS Attending

Register for one of two Lunch & Learns at The Henry in Phoenix, or stay for both presentations! Seating is limited and an RSVP is required. To RSVP for Session 1, Session 2 or the entire event go to https://tips.viscosityna.com/modernize2020-phoenix/ and complete the form and make your selection. Oracle continues to be the fastest, state-of-the-art…

Check out this Meetup →

Be sure to RSVP on Viscosity’s web site: https://tips.viscosityna.com/modernize2020-phoenix/

I don’t know about you but we are busy moving to or implementing on 19c so these sessions related to 19c and other topics should be valuable.

Bobby

Categories: DBA Blogs

Oracle Analytics Server is here

Rittman Mead Consulting - Mon, 2020-02-03 09:47
Oracle Analytics Server is here

Oracle has been talking about this product for months and we’re pleased to tell you the wait is over (for Linux users anyway), and Oracle Analytics Server (OAS) 5.5.0 is now available for download on edelivery(search Oracle Analytics Server).

Oracle Analytics Server is hereOracle Fusion Middleware needs to be downloaded separately

If your organisation uses Oracle technology for data analytics, then the likelihood is you’ve heard of Oracle Analytics Cloud (OAC). You may have also come across the latest addition to the family, OAS, the new on-prem version of OAC, set to eventually replace OBIEE.


The umbrella term, Oracle Analytics, now includes:

Oracle Analytics Server is here
  • Oracle Analytics Server (OAS)
  • Oracle Analytics Cloud (OAC)
  • Oracle Analytics for Applications (OAX)

Whilst OAC is the jewel in the crown, and will receive regular quarterly updates, these updates will be reflected in OAS. You’ll be pleased to hear current OBIEE users will be automatically licensed for OAS - the logic behind this is that OAS becomes a stepping stone in your journey to using Cloud. If you’re buying OAS new, the licensing model is the same as the current OBIEE model.

OAS looks almost the same as OAC, minus some features, like the Natural Language Generator. This feature generates explanations of your visualisations in 28 different languages and will probably be included in a later version of the tool.  

How does OAS compare to OBIEE and OAC?

  • Licensing: OAS now includes options like Data Visualization (DV) and Mobile which were previously considered extra.
  • Data Visualization: Oracle’s self-service visualisation tool does what it says on the tin. Allows you to decipher your enterprise data with intelligent visuals. Now it includes almost all the new features available in OAC. A big step forward compared to the DV version available in the latest OBIEE
  • Data Flows: Clean and Transform your data via a GUI based tool without leaving your analytical platform.
  • Machine Learning: All the goodies related to “one-click forecast” or  “Explain” and the full ML capabilities are now included in the on-prem Oracle Analytics Edition!
  • Configuration Options: OAS provides the "OBIEE"-type configuration options, where you can tweak each componend individually

Oracle’s aim is for users to achieve “100% data literacy” and plan to do this via their vision for analytics: augmented, collaborative and integrated. OAS really plays into this strategy, allowing users to employ data science and machine learning techniques to both analyse current trends and predict future ones (find out more in this blog post)

Oracle Analytics Server is here


Talk to us about how to migrate from OBIEE to OAS or OAC. We can help you with every deployment scenario including on-prem, hybrid, full public cloud, or a mix and match of these suited to your needs. Email us: info@rittmanmead.com to arrange a chat with one of our team.

Categories: BI & Warehousing

[New Feature] Backup Boot Volume Across Region In Oracle Cloud (OCI)

Online Apps DBA - Mon, 2020-02-03 07:26

[New Feature] Backup Boot Volume Across Region In Oracle Cloud (OCI) Boot Volume that holds the Operating System and its supported files. Practically now we can migrate the complete instance to another region after taking the backup of Boot and Block volumes. Check out K21Academy’s post at https://k21academy.com/oci57 which covers: • Boot Volume Backup Overview […]

The post [New Feature] Backup Boot Volume Across Region In Oracle Cloud (OCI) appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

Transit Routing: Access To Multiple VCNs From On-Premise

Online Apps DBA - Mon, 2020-02-03 03:55

Transit Routing: Access to Multiple VCNs From On-Premise Until now, your only option to connect multiple VCNs to your On-Premise networks was to have FastConnect or IPSec VPN Connect, which means you incur costs for multiple FastConnect links or face burden of operational tasks. To overcome this, Oracle has announced the availability of OCI VCN […]

The post Transit Routing: Access To Multiple VCNs From On-Premise appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

What's new in OAC5.5?

Rittman Mead Consulting - Mon, 2020-02-03 03:01
What's new in OAC5.5?

Last Friday, alongside Oracle Analytics Server (for which a blog post is coming), new OAC version came out, let's have a quick look at all the new features it includes!

Maps

If you use Maps often, then there is a good list of options available to you! The very first is the possibility to associate a Map Layer to a Data Column directly in the data source definition.

Let's say you have a column City Zones in your dataset, which divides a city in customized areas based on your company needs and you have a map layer defining geographically those areas (e.g. with a GeoJSON file). I created an example with Verona, the city where I live. Custom GeoJSON file was created using geojson.io.

What's new in OAC5.5?

The upload of custom shapes and their usage in OAC was already available since some time, however you as project creator had to associate the City Zone column to the correct Map Layer for each map visualization included in your project. Now you can define the Map Layer to Data column association once for all at datasource level, so every Map using the City Zone column will automatically use the correct Layer.

What's new in OAC5.5?

Another cool new feature in Maps is the AutoFocus on Data, meaning that the  visualization will automatically zoom and center the map appropriately based on the dataset presented and rearrange in case of changes in the filtering.

Pivot Tables

Another new option is available in pivot tables where now you can set Totals and Subtotals Above and Below like you were used to do in Answers. Like the "old" tool you can now set a different format for the Totals and Subtotals with coloring, background and font formatting options available. You have now the full control of the layout and can make beautiful or horrible (like the below) color choices.

What's new in OAC5.5?Visualizations

The perfect visualization is now available: the Spacer Viz! This is an empty visualization that you can add to your canvas allowing you to optimize the layout in cases where you need an extra white space.

What's new in OAC5.5?

Another news in this release is related to the Custom Background: now it's possible to define a color or an image as background for the whole Project or for a single canvas. The image can be a URL reference or uploaded from the desktop. There are also options to position the image in the screen and to auto-fit the image in the window size. Adding a custom background to the whole project means that every time a new canvas is added, it will already have the selected image/color by default.

What's new in OAC5.5?

Another news is represented by the Butterfly Viz, this view was already available  as plugin from the Oracle Analytics Library, now becomes native in OAC5.5. The butterfly viz is useful when comparing two metrics across the same dimension.

What's new in OAC5.5?

By Default the two metrics are on the same scale, but there is also an option "Synchronized Scales" that, when set to OFF will show the metric on different scales.

Datasources and Data Gateway

A new datasource definition to Oracle NetSuite is now available, allowing the connection by passing the parameters Datasource, Account ID and Role ID on top of the usual Host, Username and Password.

What's new in OAC5.5?

An enhancement has been published also for the Oracle Database connection: now you can select between a Basic connection and Advanced. The Basic option should be used when connecting to single node databases. The Advanced, on the other side, is useful when connecting to Cluster RAC DBs where multiple hostnames and ports need to be listed. When selecting the Advanced option we can simply add a custom connection string like the below

(DESCRIPTION 
      (ADDRESS_LIST= (LOAD_BALANCE=on)(FAILOVER=on)
      (ADDRESS=(PROTOCOL=tcp)(HOST=hostname1.subnet.com)(PORT=1529))
      (ADDRESS=(PROTOCOL=tcp)(HOST=hostname2.subnet.com)(PORT=1529))
      (ADDRESS=(PROTOCOL=tcp)(HOST=hostname3.subnet.com)(PORT=1529))
      ...
      (ADDRESS=(PROTOCOL=tcp)(HOST=hostnamen.subnet.com)(PORT=1529))
      )
)

A option is also available to use Data Gateway with Essbase sources, making the OAC  on-premises Essbase connection only one click away by just enabling Use Data Gateway option in the screen. The usage of Data Gateway is also available now on BI Publisher allowing the pixel perfect reporting from on-premises datasources.

What's new in OAC5.5?

Another option in BI Publisher is Data Chunking, extremely useful for big reports since it allows the report execution in multiple sub-jobs in parallel with a final job to consolidate the results in a unique output.

The above are the news for this release, do you want more detailed examples on a particular features? Let me know in the comments and I'll write about it!

Categories: BI & Warehousing

Setup a two node Postgres HA Cluster using EDB Advanced Server and EFM

Yann Neuhaus - Mon, 2020-02-03 01:00

Some time ago I had a customer looking for a two node PostgreSQL Master/Replica solution. As we need Oracle compatibility in a later step, we decided to go with the EnterpriseDB tools. This article should give you an introduction on how to setup the environment.

Prerequisites

There are just some few things, that you need to prepare.
You need (at least) three servers with:

  • EPEL repository available
  • Subscription for EDB
  • EDB repository available

To make everything working with our DMK some folders and links are needed:

mkdir -p /u01/app/postgres/product/as11
mkdir -p /u01as11
mkdir -p /usr/edb
mkdir -p /u02/pgdata/11/PG1
ln -s /u02/pgdata/11/PG1/ /u01as11/data
ln -s /u01/app/postgres/product/as11/ /usr/edb/as11
yum install -y unzip xorg-x11-xauth screen
EDB Advanced Server Installation

Let’s start with the installation of the EDB Advanced Server This is really straight forward:

$ yum install edb-as11-server
$ chown enterprisedb:enterprisedb /u02/pgdata/11/epg1/
$ chown -R enterprisedb:enterprisedb /u01/app/
$ rm -rf /u01as11/backups/
$ passwd enterprisedb

Now you can install and configure our DMK. Make sure to adjust var::PGUSER::=::nooption::”enterprisedb” in the dmk.conf.

As soon as the installation is done, you can initialize a new primary cluster.

enterprisedb@edb1:/var/lib/edb/ [PG1] cat /etc/pgtab
PG1:/u01/app/postgres/product/as11/:/u02/pgdata/11/PG1/:5444:Y
enterprisedb@edb1:/var/lib/edb/ [PG1] dmk
enterprisedb@edb1:/var/lib/edb/ [pg950] PG1

********* dbi services Ltd. ****************

STATUS           : CLOSED

********************************************
enterprisedb@ad1:/var/lib/edb/ [PG1] sudo mkdir -p /u02/pgdata/PG1
enterprisedb@ad1:/var/lib/edb/ [PG1] sudo chown enterprisedb:enterprisedb /u02/pgdata/PG1
enterprisedb@ad1:/var/lib/edb/ [PG1] /u01/app/postgres/product/as11/bin/initdb --pgdata=/u02/pgdata/PG1/ --pwprompt --data-checksums --auth=md5
The files belonging to this database system will be owned by user "enterprisedb".
This user must also own the server process.

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

Data page checksums are enabled.

Enter new superuser password:
Enter it again:

fixing permissions on existing directory /u02/pgdata/PG1 ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default timezone ... Europe/Berlin
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
creating edb sys ... ok
loading edb contrib modules ...
edb_redwood_bytea.sql ok
edb_redwood_date.sql ok
dbms_alert_public.sql ok
dbms_alert.plb ok
dbms_job_public.sql ok
dbms_job.plb ok
dbms_lob_public.sql ok
dbms_lob.plb ok
dbms_output_public.sql ok
dbms_output.plb ok
dbms_pipe_public.sql ok
dbms_pipe.plb ok
dbms_rls_public.sql ok
dbms_rls.plb ok
dbms_sql_public.sql ok
dbms_sql.plb ok
dbms_utility_public.sql ok
dbms_utility.plb ok
dbms_aqadm_public.sql ok
dbms_aqadm.plb ok
dbms_aq_public.sql ok
dbms_aq.plb ok
dbms_profiler_public.sql ok
dbms_profiler.plb ok
dbms_random_public.sql ok
dbms_random.plb ok
dbms_redact_public.sql ok
dbms_redact.plb ok
dbms_lock_public.sql ok
dbms_lock.plb ok
dbms_scheduler_public.sql ok
dbms_scheduler.plb ok
dbms_crypto_public.sql ok
dbms_crypto.plb ok
dbms_mview_public.sql ok
dbms_mview.plb ok
dbms_session_public.sql ok
dbms_session.plb ok
edb_bulkload.sql ok
edb_gen.sql ok
edb_objects.sql ok
edb_redwood_casts.sql ok
edb_redwood_strings.sql ok
edb_redwood_views.sql ok
utl_encode_public.sql ok
utl_encode.plb ok
utl_http_public.sql ok
utl_http.plb ok
utl_file.plb ok
utl_tcp_public.sql ok
utl_tcp.plb ok
utl_smtp_public.sql ok
utl_smtp.plb ok
utl_mail_public.sql ok
utl_mail.plb ok
utl_url_public.sql ok
utl_url.plb ok
utl_raw_public.sql ok
utl_raw.plb ok
commoncriteria.sql ok
waitstates.sql ok
installing extension edb_dblink_libpq ... ok
installing extension edb_dblink_oci ... ok
installing extension pldbgapi ... ok
snap_tables.sql ok
snap_functions.sql ok
dblink_ora.sql ok
sys_stats.sql ok
finalizing initial databases ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

    /u01/app/postgres/product/as11/bin/pg_ctl -D /u02/pgdata/PG1/ -l logfile start

enterprisedb@ad1:/var/lib/edb/ [PG1]

Than adjust wal_keep_segments and afterwards the edb-as service can be enabled and started.

$ echo "wal_keep_segments=100" >> $PGDATA/postgresql.auto.conf
$ sudo systemctl enable edb-as-11.service
$ sudo systemctl start edb-as-11

To be sure everything works as expected, reboot the server (if possible).

All above steps should also be done on your additional nodes, but without the systemctl start.

Configuration

First, on Node 1 (Master) you need to create the replication role.

postgres=# create role replication with REPLICATioN PASSWORD 'replication' login;
CREATE ROLE

Second, you need to add replication to pg_hba.conf.

local   replication    all             127.0.0.1/32            trust
host    replication    all             192.168.22.53/32        trust
host    replication    all             192.168.22.51/32        trust
host    replication    all             192.168.22.52/32        trust
host    replication    all             ::1/128                 trust

And last but not least, your should exchange the ssh-key of all nodes:

enterprisedb@edb1:/u01 [PG1] ssh-keygen
enterprisedb@edb1:/u01 [PG1] ssh-copy-id enterprisedb@edb1
enterprisedb@edb1:/u01 [PG1] ssh-copy-id enterprisedb@edb2
enterprisedb@edb1:/u01 [PG1] ssh-copy-id enterprisedb@edb3
Create the replica

As already mentioned, you need almost all steps done on Node 2 as well, but without starting the service.
Make sure to have all hosts in pg_hba.conf of Master.

To create the replication create a pg_basebackup into Node 2:

enterprisedb@edb2:/u01 [PG1] pg_basebackup -h 192.168.22.51 -U replication -p 5432 -D $PGDATA -Fp -Xs -P -R
49414/49414 kB (100%), 1/1 tablespace

Once finish, check if the recovery.conf is available and add the following lines:

enterprisedb@edb2:/u01 [PG1] echo "recovery_target_timeline = 'latest'" >> /u02/pgdata/11/PG1/recovery.conf
enterprisedb@edb2:/u01 [PG1] echo "trigger_file='/u02/pgdata/11/PG1/trigger_failover'" >> /u02/pgdata/11/PG1/recovery.conf

To test, if the recovery is working, start the cluster and check the recovery status.

enterprisedb@edb2:/u01 [PG1] pgstart
enterprisedb@edb2:/u01 [PG1] psql -U enterprisedb -c "select pg_is_in_recovery()" postgres
 pg_is_in_recovery
-------------------
 t
(1 row)

enterprisedb@edb2:/u01 [PG1] sudo systemctl enable edb-as-11.service
enterprisedb@edb2:/u01 [PG1] pgstop
enterprisedb@edb2:/u01 [PG1] systemctl start edb-as-11
EDB Postgres Failover Manager (EFM)

To make our two Node setup High Available, we need to install the EDB Postgres Failover Manager on three nodes. On the both installed with the Master / Replica and on a third one as a witness server.

Installation

Installation for EFM is straight forward as well, therefore your have to do the following steps on all three nodes. To use EFM toghether with our DMK, you need to create some links.

$ sudo yum install edb-efm37
$ sudo yum install java-1.8.0-openjdk
$ sudo chown -R enterprisedb:enterprisedb /etc/edb/efm-3.7/
$ cat /etc/edb/efm-3.7/efm.nodes
$ sudo ln -s /usr/edb/efm-3.7 /usr/edb/efm
$ sudo ln -s /etc/edb/efm-3.7 /etc/edb/efm
Configuration

On the master you have to set a password for the enterprisedb user and encrypt the password using EFM.

$ psql -U enterprisedb -c "alter user enterprisedb with password '*******'" postgres
$ /usr/edb/efm/bin/efm encrypt efm

The enrypted password generated by efm encrypt will be needed in the efm.properties files

As next step we need an efm.properties file on Node 1 and 2 (parameters to adjust below).

$ cp /u01/app/postgres/local/dmk/templates/postgres/efm.properties.in /etc/edb/efm-3.7/efm.properties
$ vi /etc/edb/efm-3.7/efm.properties
  db.user=enterprisedb
  db.password.encrypted=f17db6033ef1be48ec1955d38b4c9c46
  db.port=5400
  db.database=postgres
  db.bin=/u01/app/postgres/product/as11/bin
  db.recovery.dir=/u02/pgdata/11/EPAS
  bind.address=192.168.22.51:7800
  admin.port=7809
  is.witness=false 
  virtualIp=192.168.22.55
  virtualIp.interface=enp0s8
  virtualIp.prefix=24
  virtualIp.single=true
$ sudo chown enterprisedb:enterprisedb /etc/edb/efm/efm.properties

We also need a efm.nodes file to have all nodes of the cluster.

$ cat /etc/edb/efm/efm.nodes
# List of node address:port combinations separated by whitespace.
# The list should include at least the membership coordinator's address.
192.168.22.51:7800 192.168.22.52:7800 192.168.22.53:7800
$ chown efm:efm efm.nodes
$ chmod 660 /etc/edb/efm/efm.nodes

To conclude, enable and start the efm-3.7.service.

sudo systemctl enable efm-3.7.service
sudo systemctl start efm-3.7.service

On node 3 we need to create a efm.properties file as well, but we need the efm.properties_witness file of dmk as draft.

$ cp /u01/app/postgres/local/dmk/templates/postgres/efm.properties_witness /etc/edb/efm-3.7/efm.properties

Adjust the parameters as shown in the step for node 1 and 2, but be careful to have:

is.witness=true

Afterwards start the efm-3.7 service on node 3 as well.

$ sudo systemctl start efm-3.7.service

In the end, you can check if everything is running as expected using EFM.

$ efm cluster-status efm
Cluster Status: efm

        Agent Type  Address              Agent  DB       VIP
        -----------------------------------------------------------------------
        Standby     192.168.22.51        UP     UP       192.168.22.55
        Master      192.168.22.52        UP     UP       192.168.22.55*
        Witness     192.168.22.53        UP     N/A      192.168.22.55

Allowed node host list:
        192.168.22.52 192.168.22.51 192.168.22.53

Membership coordinator: 192.168.22.52

Standby priority host list:
        192.168.22.51

Promote Status:

        DB Type     Address              WAL Received LSN   WAL Replayed LSN   Info
        ---------------------------------------------------------------------------
        Master      192.168.22.52                           0/110007B8
        Standby     192.168.22.51        0/110007B8         0/110007B8

        Standby database(s) in sync with master. It is safe to promote.

That’s it, now you have a Master/Replica system using EDB tools.
In a next step we will have a look at the setup of the cluster monitoring using EDB Enterprise Manager.

Cet article Setup a two node Postgres HA Cluster using EDB Advanced Server and EFM est apparu en premier sur Blog dbi services.

Oracle Flashback Data Archive

Tom Kyte - Sat, 2020-02-01 15:01
Hi All, We are using Oracle Flashback Data Archive in our database to track Audit in most of the critical tables. We initially planned to store data for 6months but there are few errors / issues we face making it unstable. So we have it as retentio...
Categories: DBA Blogs

Java procedure for host calls on Unix environment

Tom Kyte - Sat, 2020-02-01 15:01
Steve, I'm looking for a Java-procedure executing host calls on a Unix environment from the Oracle-server. I know the standard way of doing it by means of ProC (for Oracle versions before 8.1) but I do not have the ProC compiler available. I do...
Categories: DBA Blogs

Audit Vault and Database Firewall licensing

Tom Kyte - Sat, 2020-02-01 15:01
Dear Experts, Imagine that a partner as 8 Database EE Embedded licenses for end users and 4 Database EE Full licenses for their own internal application. Now, they want to implement only one Audit Vault appliance to collect and audit logs for all th...
Categories: DBA Blogs

Memory parameters - simple and auto tune

Tom Kyte - Sat, 2020-02-01 15:01
Hi Tom, We have 16 GB of memory on Windows server. Database is Oracle 12.2.01. 6.6 GB is using Oraclekernel exe. We have set: SGA_MAX_SIZE = 10016 M SGA_TARGET = 6016 M PGA_AGGREGATE_LIMIT = 6000 M PGA_AGGREGATE_TARGET = 2900 M I hop...
Categories: DBA Blogs

Calling executable from Scheduler

Tom Kyte - Sat, 2020-02-01 15:01
<b></b><code></code><u></u>Hi Tom I have been trying to call an executable shell script placed on AIX 7.2 from Oracle 12.2 via following code: <code>BEGIN DBMS_SCHEDULER.create_job ( job_name => 'GEN_DAILY_CNT_FILE', job_typ...
Categories: DBA Blogs

Data Safe private endpoints

Tom Kyte - Sat, 2020-02-01 15:01
Hi, customer is using Data Safe with DB System on OCI, but it wants to use DB System on private subnet. Now is possible to use Data Safe only with a DB System on a public subnet but this is not acceptable for customer security department. When will ...
Categories: DBA Blogs

How To Pass a List Object From C# to an Oracle Stored Procedure?

Tom Kyte - Sat, 2020-02-01 15:01
Dear Sirs, I know you?re probably tired from shaving your yak, but I?ve been doing research on this topic for a few months now with very little luck. Is there a way you can pass a list object from C# over to a stored procedure? I was able to do s...
Categories: DBA Blogs

How to find last DDL / DML timestamp for IOT tables

Tom Kyte - Sat, 2020-02-01 15:01
Hi, One of my Customer is asking for last DDL/DML timestamp change for IOT tables. They have many tables and they need to drop some of them which is not used / modified / altered frequently. I checked internally for IOT and Normal tables using OR...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator