Yann Neuhaus

Subscribe to Yann Neuhaus feed
dbi services technical blog
Updated: 1 week 4 days ago

AEM Forms – WebLogic Clustering synch issue for Workbench 2-way-SSL

Thu, 2019-10-31 13:00

In a previous blog, I described the process to setup the AEM Forms to allow the AEM Workbench to connect to AEM using “2-way-SSL”. This setup is normally independent of the Application Server that you are using to host AEM. However, I already faced an issue (other than this one) which was caused by the 2-way-SSL setup for the Workbench in case of a WebLogic Cluster has been used to host AEM.

As mentioned in previous blog, I’m not an AEM expert but I know a few things about WebLogic so the idea here was to setup a fully functional WebLogic Cluster composed of two Managed Servers on two hosts/machines, test it properly and then install the AEM Forms application on top of it. Obviously, AEM Forms has been configured behind a Load Balancer for this purpose. At this point, AEM Forms was working perfectly in HA and stopping one of the nodes wasn’t a problem.

Then I configured the Workbench for 2-way-SSL and I did so while being connected to the AEM Node1 in Workbench, creating the Hybrid Domain in the AEM AdminUI Node1, aso… At the end of the setup, the AEM Workbench was working properly with the 2-way-SSL setup as well so it looked like the setup was completed. Just to be sure, I stopped the AEM Node1 and try to login to the AEM Workbench with the exact same parameters (same keystore, same truststore, same passwords) except for the target Server which I switched to the AEM Node2. Doing so, the login failed and I could see in the AEM Node2 Managed Server logs the following message:

####<Feb 12, 2019 2:14:46,277 PM UTC> <Info> <EJB> <aem-node-2> <msAEM-02> <[ACTIVE] ExecuteThread: '76' for queue: 'weblogic.kernel.Default (self-tuning)'> <<anonymous>> <> <81fe4dac-31f0-4c25-bf37-17d5b327a901-0000005e> <1549980886277> <[severity-value: 64] [rid: 0] [partition-id: 0] [partition-name: DOMAIN] > <BEA-010227> <EJB exception occurred during invocation from home or business: com.adobe.idp.dsc.transaction.impl.ejb.adapter.EjbTransactionBMTAdapter_fw85em_Intf generated exception: ALC-DSC-124-000: com.adobe.idp.dsc.DSCAuthorizationException: User does not have the Service Read Permission.>

 
Just looking at this message, it’s clear that the user account that is working properly for the AEM Node1 isn’t working for the AEM Node2. After some investigation, it looked like the Hybrid Domain wasn’t shown on the AEM AdminUI Node2, for some reason… Both nodes are using the same Oracle Database and the same GDS (Global Document Storage) path so I thought that the issue might be related to a cache somewhere in AEM. Therefore, I thought about re-creating the Hybrid Domain but I just cancelled this move right away because I assume it could have bring me more trouble than solution (I didn’t want to create 2 objects with the same name, avoid corruption or whatever…):

  • Open the AEM AdminUI Node2 (Node1 is still down) (https://<AEM_HOST_2>:<AEM_PORT>/adminui)
  • Login with an administative account (E.g.: administrator)
  • Navigate to: Settings > User Management > Domain Management
    • -> Only 1 domain is displayed, the default one: DefaultDom
  • Click on “New Hybrid Domain
    • Click on “Cancel”

 
After doing that, the Hybrid Domain (the one created in this blog, named “SSLMutualAuthProvider“) magically appeared so I assume that it forced a synchronization and an update of the cache on the AEM Node2. Trying again a login to the AEM Workbench without changing the parameters printed the following on the AEM Node2 Managed Server logs:

####<Feb 12, 2019 2:30:43,208 PM UTC> <Info> <com.adobe.livecycle.usermanager.sslauthprovider.SSLMutualAuthProvider> <aem-node-2> <msAEM-02> <[ACTIVE] ExecuteThread: '117' for queue: 'weblogic.kernel.Default (self-tuning)'> <<anonymous>> <BEA1-24A18C6CA9D79C032EFA> <81fe4dac-31f0-4c25-bf37-17d5b327a901-00000067> <1549981843208> <[severity-value: 64] [rid: 0] [partition-id: 0] [partition-name: DOMAIN] > <BEA-000000> <Got Subject DN as CN=aem-dev,OU=IT,O=dbi services,L=Delemont,ST=Jura,C=CH>

 
The above message means that the login is successful and Workbench is able to load the data from AEM properly. I guess that there are other ways to fix this issue. There is a “Sync Now” as well as a “Refresh” button on the Domain Management page of the AdminUI so maybe this would have done the same thing and forced a synchronization… I must admit that I first thought about re-creating the Hybrid Domain but cancelled that and since it solved my issue, I couldn’t test more, unfortunately. A restart of the AEM Node2 is also sufficient to force a refresh but this takes a few minutes and it requires a downtime so it’s not ideal.

Cet article AEM Forms – WebLogic Clustering synch issue for Workbench 2-way-SSL est apparu en premier sur Blog dbi services.

SLES15 SP1 – New features

Thu, 2019-10-31 05:20

With SLES15 SUSE introduced the Multimodal OS and the unified installer. Means, you only get what you really need. Your OS is flexible and you can easily add features you need and remove them as well. But this article shouldn’t be an explanation of the multimodal OS, it will show you some of the new features of SLES15 SP1.

SUSE supports the migration from SLES15 to SLES15 SP1 in online mode.
You can upgrade using two possibilities, YaST migration (GUI) and Zypper migration (command line).
Be sure that your system is registered at the SUSE Customer Center or has a local RMT server. Afterwards, just use “zypper migration”, type the number of the product you want to migrate and accept the terms of the license. That’s it.
The best way to check, if the installation was successful.

sles15:~ # cat /etc/os-release | grep PRETTY_NAME
PRETTY_NAME="SUSE Linux Enterprise Server 15 SP1"

So let’s have a look at the new features and improvements of SLES15 SP1 .

Unified Installer

SUSE Manager Server and Proxy are now available as base products. Both can be installed using the unified installer.
Point of Service and SLE Real Time are also included in the unified installer now.

Transactional Update

In OpenSUSE Leap and SUSE CaaS transactional update was already implemented, now it is also possible to run transactional updates with SLE. To install transactional update, the Transactional Server Module needs to get activated first (no additional key is needed). Afterwards the transactional-update package and its dependencies can be installed.

sle15:~ #  SUSEConnect --product sle-module-transactional-server/15.1/x86_64
Registering system to SUSE Customer Center

Updating system details on https://scc.suse.com ...

Activating sle-module-transactional-server 15.1 x86_64 ...
-> Adding service to system ...
-> Installing release package ...

Successfully registered system
sle15:~ # zypper install transactional-update
Refreshing service 'Basesystem_Module_15_SP1_x86_64'.
Refreshing service 'SUSE_Linux_Enterprise_Server_15_SP1_x86_64'.
Refreshing service 'Server_Applications_Module_15_SP1_x86_64'.
Refreshing service 'Transactional_Server_Module_15_SP1_x86_64'.
Loading repository data...
Reading installed packages...
Resolving package dependencies...

The following 6 NEW packages are going to be installed:
  attr bc openslp psmisc rsync transactional-update

6 new packages to install.
Overall download size: 686.6 KiB. Already cached: 0 B. After the operation, additional 1.3 MiB will be used.
Continue? [y/n/v/...? shows all options] (y): y

As you maybe know, SUSE uses btrfs with snapper as default for the file systems. This builds the basis for the transactional updates. Transactional updates are applied into a new snapshot, so the running system is not touched. Using this technology, the updated snapshot will be activated after the next reboot. So this is an update, that is
– Atomic: either fully applied or not.
– Easily revertabled: after a failed update the return to the previous (running) system is easy.

Simplified btrfs layout

There is only one single subvolume under /var not 10 for simplified and consistens snapshots. This takes only effect for fresh installations. Upgraded systems still use the old layout.
Startings with SLES15 SP1 there is also the possibility to have each home-directory as single subvolume. But this is not the default.

Secure encrypted virtualization (SEV)

Data encryption is a important topic in todays IT environments. Data stored on disk is widley encrypted, but how about the data in RAM? AMD’s SEV gives the opportunity to protect Linux KVM virtual machines by encrypting the memory of each VM with a unique key. It can also generate a signature, that attests the correct encryption.
This increases system security a lot and protects VM for memory scrape attachs from hypervisor.
With SLES15 SP1, Suse provides full support for this technology. For further information about SEV, click here .

Quarterly Updates

Starting with 15 SP1 SUSE offers quarterly updates of the installation and package media. They will be refreshed every quarter with all maintenance and security updates. SO for the setup of new systems there is always a recent and up-to-date state.

Conclusion

This is not the full list of new features, only an abstract. But nevertheless, especially the transactional update makes it effortable to upgrade to SLES15 SP1. And always think about the security improvements which come with every new release.

Cet article SLES15 SP1 – New features est apparu en premier sur Blog dbi services.

Patroni Operations – Changing Parameters

Wed, 2019-10-30 10:17

Sooner or later all of us have to change a parameter on the database. But how is this put into execution when using a Patroni cluster? Of course there are some specifics you have to consider.
This post will give you a short introduction into this topic.

When you want to change a parameter on a Patroni cluster you have several possibilities:
– Dynamic configuration in DCS. These changes are applied asynchronously to every node.
– Local configuration in patroni.yml. This will take precedence over the dynamic configuration.
– Cluster configuration using “alter system”.
– Environment configuration using local environment variables.

Change PostgreSQL parameters using patronictl 1. Change parameters, that do not need a restart

If you want to change a parameter (or more) for the whole cluster, you should use patronictl. If you want to change the initial configuration as well, you should also adjust patroni.yml.

postgres@patroni1:/u01/app/postgres/local/dmk/etc/ [PG1] patronictl edit-config PG1

All parameters already set are shown and can be changed like in any other file using the vi commands:

postgres@patroni1:/u01/app/postgres/local/dmk/etc/ [PG1] patronictl edit-config PG1

loop_wait: 10
maximum_lag_on_failover: 1048576
postgresql:
  parameters:
    archive_command: /bin/true
    archive_mode: 'on'
    autovacuum_max_workers: '6'
    autovacuum_vacuum_scale_factor: '0.1'
    autovacuum_vacuum_threshold: '50'
    client_min_messages: WARNING
    effective_cache_size: 512MB
    hot_standby: 'on'
    hot_standby_feedback: 'on'
    listen_addresses: '*'
    log_autovacuum_min_duration: 60s
    log_checkpoints: 'on'
    log_connections: 'on'
    log_directory: pg_log
    log_disconnections: 'on'
    log_duration: 'on'
    log_filename: postgresql-%a.log
    log_line_prefix: '%m - %l - %p - %h - %u@%d - %x'
    log_lock_waits: 'on'
    log_min_duration_statement: 30s
    log_min_error_statement: NOTICE
    log_min_messages: WARNING
    log_rotation_age: '1440'
    log_statement: ddl
    log_temp_files: '0'
    log_timezone: Europe/Zurich
    log_truncate_on_rotation: 'on'
    logging_collector: 'on'
    maintenance_work_mem: 64MB
    max_replication_slots: 10
    max_wal_senders: '20'
    port: 5432
    shared_buffers: 128MB
    shared_preload_libraries: pg_stat_statements
    wal_compression: 'off'
    wal_keep_segments: 8
    wal_level: replica
    wal_log_hints: 'on'
    work_mem: 8MB
  use_pg_rewind: true
  use_slots: true
retry_timeout: 10
ttl: 30

Once saved, you get the following:

---
+++
@@ -2,7 +2,8 @@
 maximum_lag_on_failover: 1048576
 postgresql:
   parameters:
-    archive_command: /bin/true
+    archive_command: 'test ! -f /u99/pgdata/PG1/archived_wal/%f && cp %p /u99/pgdata/PG1/archived_wal/%f'
     archive_mode: 'on'
     autovacuum_max_workers: '6'
     autovacuum_vacuum_scale_factor: '0.1'

Apply these changes? [y/N]: y
Configuration changed

When connecting to the database you will see, that the parameter is changed now. It is also changed on all the other nodes.

 postgres@patroni1:/u01/app/postgres/local/dmk/etc/ [PG1] sq
psql (11.5)
Type "help" for help.

postgres=# show archive_command;
                                  archive_command
------------------------------------------------------------------------------------
 test ! -f /u99/pgdata/PG1/archived_wal/%f && cp %p /u99/pgdata/PG1/archived_wal/%f
(1 row)
2. Change parameters, that need a restart

How can parameters be changed that need a restart? Especially as we want to have a minimal downtime of the cluster.
First of all the parameter can be changed the same way as the parameters that do not need a restart using patronictl edit-config. Once the parameter is changed the status overview of the cluster gets a new column showing which node needs a restart.

postgres@patroni1:/u01/app/postgres/local/dmk/etc/ [PG1] patronictl list
+---------+----------+----------------+--------+---------+----+-----------+-----------------+
| Cluster |  Member  |      Host      |  Role  |  State  | TL | Lag in MB | Pending restart |
+---------+----------+----------------+--------+---------+----+-----------+-----------------+
|   PG1   | patroni1 | 192.168.22.111 | Leader | running |  4 |       0.0 |        *        |
|   PG1   | patroni2 | 192.168.22.112 |        | running |  4 |       0.0 |        *        |
|   PG1   | patroni3 | 192.168.22.113 |        | running |  4 |       0.0 |        *        |
+---------+----------+----------------+--------+---------+----+-----------+-----------------+

Afterwards there are two possibilites.

2.1 Restart node by node

If you do not want to restart the whole cluster, you have the possibility to restart each node separatly. Keep in mind, that you have to restart the Leader Node first, otherwise the change does not take effect. It is also possible to schedule the restart of a node.

postgres@patroni1:/u01/app/postgres/local/dmk/etc/ [PG1] patronictl restart PG1 patroni1
When should the restart take place (e.g. 2019-10-08T15:33)  [now]:
+---------+----------+----------------+--------+---------+----+-----------+-----------------+
| Cluster |  Member  |      Host      |  Role  |  State  | TL | Lag in MB | Pending restart |
+---------+----------+----------------+--------+---------+----+-----------+-----------------+
|   PG1   | patroni1 | 192.168.22.111 | Leader | running |  4 |       0.0 |        *        |
|   PG1   | patroni2 | 192.168.22.112 |        | running |  4 |       0.0 |        *        |
|   PG1   | patroni3 | 192.168.22.113 |        | running |  4 |       0.0 |        *        |
+---------+----------+----------------+--------+---------+----+-----------+-----------------+
Are you sure you want to restart members patroni1? [y/N]: y
Restart if the PostgreSQL version is less than provided (e.g. 9.5.2)  []:
Success: restart on member patroni1
postgres@patroni1:/u01/app/postgres/local/dmk/etc/ [PG1] patronictl restart PG1 patroni2
When should the restart take place (e.g. 2019-10-08T15:34)  [now]:
+---------+----------+----------------+--------+---------+----+-----------+-----------------+
| Cluster |  Member  |      Host      |  Role  |  State  | TL | Lag in MB | Pending restart |
+---------+----------+----------------+--------+---------+----+-----------+-----------------+
|   PG1   | patroni1 | 192.168.22.111 | Leader | running |  4 |       0.0 |                 |
|   PG1   | patroni2 | 192.168.22.112 |        | running |  4 |       0.0 |        *        |
|   PG1   | patroni3 | 192.168.22.113 |        | running |  4 |       0.0 |        *        |
+---------+----------+----------------+--------+---------+----+-----------+-----------------+
Are you sure you want to restart members patroni2? [y/N]: y
Restart if the PostgreSQL version is less than provided (e.g. 9.5.2)  []:
Success: restart on member patroni2
postgres@patroni1:/u01/app/postgres/local/dmk/etc/ [PG1] patronictl restart PG1 patroni3
When should the restart take place (e.g. 2019-10-08T15:34)  [now]:
+---------+----------+----------------+--------+---------+----+-----------+-----------------+
| Cluster |  Member  |      Host      |  Role  |  State  | TL | Lag in MB | Pending restart |
+---------+----------+----------------+--------+---------+----+-----------+-----------------+
|   PG1   | patroni1 | 192.168.22.111 | Leader | running |  4 |       0.0 |                 |
|   PG1   | patroni2 | 192.168.22.112 |        | running |  4 |       0.0 |                 |
|   PG1   | patroni3 | 192.168.22.113 |        | running |  4 |       0.0 |        *        |
+---------+----------+----------------+--------+---------+----+-----------+-----------------+
Are you sure you want to restart members patroni3? [y/N]: y
Restart if the PostgreSQL version is less than provided (e.g. 9.5.2)  []:
Success: restart on member patroni3
postgres@patroni1:/u01/app/postgres/local/dmk/etc/ [PG1] patronictl list
+---------+----------+----------------+--------+---------+----+-----------+
| Cluster |  Member  |      Host      |  Role  |  State  | TL | Lag in MB |
+---------+----------+----------------+--------+---------+----+-----------+
|   PG1   | patroni1 | 192.168.22.111 | Leader | running |  4 |       0.0 |
|   PG1   | patroni2 | 192.168.22.112 |        | running |  4 |       0.0 |
|   PG1   | patroni3 | 192.168.22.113 |        | running |  4 |       0.0 |
+---------+----------+----------------+--------+---------+----+-----------+
2.2 Restart the whole cluster

In case you don’t want to restart node by node and you have the possibility of a downtime, it is also possible to restart the whole cluster (scheduled or immediately)

postgres@patroni1:/u01/app/postgres/local/dmk/etc/ [PG1] patronictl restart PG1
When should the restart take place (e.g. 2019-10-08T15:37)  [now]:
+---------+----------+----------------+--------+---------+----+-----------+-----------------+
| Cluster |  Member  |      Host      |  Role  |  State  | TL | Lag in MB | Pending restart |
+---------+----------+----------------+--------+---------+----+-----------+-----------------+
|   PG1   | patroni1 | 192.168.22.111 | Leader | running |  4 |       0.0 |        *        |
|   PG1   | patroni2 | 192.168.22.112 |        | running |  4 |       0.0 |        *        |
|   PG1   | patroni3 | 192.168.22.113 |        | running |  4 |       0.0 |        *        |
+---------+----------+----------------+--------+---------+----+-----------+-----------------+
Are you sure you want to restart members patroni1, patroni2, patroni3? [y/N]: y
Restart if the PostgreSQL version is less than provided (e.g. 9.5.2)  []:
Success: restart on member patroni1
Success: restart on member patroni2
Success: restart on member patroni3
postgres@patroni1:/u01/app/postgres/local/dmk/etc/ [PG1] patronictl list
+---------+----------+----------------+--------+---------+----+-----------+
| Cluster |  Member  |      Host      |  Role  |  State  | TL | Lag in MB |
+---------+----------+----------------+--------+---------+----+-----------+
|   PG1   | patroni1 | 192.168.22.111 | Leader | running |  4 |       0.0 |
|   PG1   | patroni2 | 192.168.22.112 |        | running |  4 |       0.0 |
|   PG1   | patroni3 | 192.168.22.113 |        | running |  4 |       0.0 |
+---------+----------+----------------+--------+---------+----+-----------+
Change PostgreSQL parameters using “alter system”

Of course you can change a parameter only on one node using “alter system”, too.

 postgres@patroni1:/home/postgres/ [PG1] sq
psql (11.5)
Type "help" for help.

postgres=# show archive_Command;
 archive_command
-----------------
 /bin/false
(1 row)

postgres=# alter system set archive_command='/bin/true';
ALTER SYSTEM

postgres=# select pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

postgres=# show archive_command;
 archive_command
-----------------
 /bin/true
(1 row)

For sure the parameter change is not automatically applied to the replicas. The parameter is only changed on that node. All the other nodes will keep the value from the DCS. So you can change the parameter using “patronictl edit-config” or with an “alter system” command on each node. But: you also have to keep in mind the order in which the parameters are applied. The “alter system” change will persist the “patronictl edit-config” command.

Conclusion

So if you consider that there are some specialities when changing parameters in a Patroni cluster, it is quite easy to change a parameter. There are some parameters that need the same value on all nodes, e.g. max_connections, max_worker_processes, wal_level. And there are as well some parameters controlled by patroni, e.g listen_addresses and port. For a more details check the Patroni documentation . And last but not least: If you change the configuration with patronictl and one node still has another configuration. Look for a postgresql.auto.conf in the PGDATA directory. Maybe there you can find the reason for different parameters on your nodes.
If you are interested in more “Patroni Operations” blogs, check also this one Patroni operations: Switchover and Failover.

Cet article Patroni Operations – Changing Parameters est apparu en premier sur Blog dbi services.

PostgreSQL 13 will come with partitioning support for pgbench

Wed, 2019-10-30 08:32

A lot of people use pgbench to benchmark a PostgreSQL instance and pgbench is also heavily used by the PostgreSQL developers. While declarative partitioning was introduced in PostgreSQL 10 there was no support for that in pgbench, even in the current version, which is PostgreSQL 12. With PostgreSQL 13, which is currently in development, this will change and pgbench will be able to create a partitioned pgbench_accounts tables you then can run your benchmark against.

Having a look at the parameters of pgbench in PostgreSQL 13, two new ones pop up:

postgres@centos8pg:/home/postgres/ [pgdev] pgbench --help
pgbench is a benchmarking tool for PostgreSQL.

Usage:
pgbench [OPTION]... [DBNAME]

Initialization options:
-i, --initialize         invokes initialization mode
-I, --init-steps=[dtgvpf]+ (default "dtgvp")
run selected initialization steps
-F, --fillfactor=NUM     set fill factor
-n, --no-vacuum          do not run VACUUM during initialization
-q, --quiet              quiet logging (one message each 5 seconds)
-s, --scale=NUM          scaling factor
--foreign-keys           create foreign key constraints between tables
--index-tablespace=TABLESPACE
create indexes in the specified tablespace
--partitions=NUM         partition pgbench_accounts in NUM parts (default: 0)
--partition-method=(range|hash)
partition pgbench_accounts with this method (default: range)
--tablespace=TABLESPACE  create tables in the specified tablespace
--unlogged-tables        create tables as unlogged tables

Options to select what to run:
-b, --builtin=NAME[@W]   add builtin script NAME weighted at W (default: 1)
(use "-b list" to list available scripts)
-f, --file=FILENAME[@W]  add script FILENAME weighted at W (default: 1)
-N, --skip-some-updates  skip updates of pgbench_tellers and pgbench_branches
(same as "-b simple-update")
-S, --select-only        perform SELECT-only transactions
(same as "-b select-only")

Benchmarking options:
-c, --client=NUM         number of concurrent database clients (default: 1)
-C, --connect            establish new connection for each transaction
-D, --define=VARNAME=VALUE
define variable for use by custom script
-j, --jobs=NUM           number of threads (default: 1)
-l, --log                write transaction times to log file
-L, --latency-limit=NUM  count transactions lasting more than NUM ms as late
-M, --protocol=simple|extended|prepared
protocol for submitting queries (default: simple)
-n, --no-vacuum          do not run VACUUM before tests
-P, --progress=NUM       show thread progress report every NUM seconds
-r, --report-latencies   report average latency per command
-R, --rate=NUM           target rate in transactions per second
-s, --scale=NUM          report this scale factor in output
-t, --transactions=NUM   number of transactions each client runs (default: 10)
-T, --time=NUM           duration of benchmark test in seconds
-v, --vacuum-all         vacuum all four standard tables before tests
--aggregate-interval=NUM aggregate data over NUM seconds
--log-prefix=PREFIX      prefix for transaction time log file
(default: "pgbench_log")
--progress-timestamp     use Unix epoch timestamps for progress
--random-seed=SEED       set random seed ("time", "rand", integer)
--sampling-rate=NUM      fraction of transactions to log (e.g., 0.01 for 1%)
--show-script=NAME       show builtin script code, then exit

Common options:
-d, --debug              print debugging output
-h, --host=HOSTNAME      database server host or socket directory
-p, --port=PORT          database server port number
-U, --username=USERNAME  connect as specified database user
-V, --version            output version information, then exit
-?, --help               show this help, then exit

Report bugs to .

That should give us partitions according to the amount of partitions and partitioning method we chose, so let’s populate a new database:

postgres@centos8pg:/home/postgres/ [pgdev] psql -c "create database pgbench" postgres
CREATE DATABASE
Time: 326.715 ms
postgres@centos8pg:/home/postgres/ [pgdev] pgbench -i -s 10 --partitions=10 --partition-method=range --foreign-keys pgbench
dropping old tables...
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
creating tables...
creating 10 partitions...
generating data...
100000 of 1000000 tuples (10%) done (elapsed 0.20 s, remaining 1.78 s)
200000 of 1000000 tuples (20%) done (elapsed 0.40 s, remaining 1.62 s)
300000 of 1000000 tuples (30%) done (elapsed 0.74 s, remaining 1.73 s)
400000 of 1000000 tuples (40%) done (elapsed 1.23 s, remaining 1.85 s)
500000 of 1000000 tuples (50%) done (elapsed 1.47 s, remaining 1.47 s)
600000 of 1000000 tuples (60%) done (elapsed 1.81 s, remaining 1.21 s)
700000 of 1000000 tuples (70%) done (elapsed 2.25 s, remaining 0.97 s)
800000 of 1000000 tuples (80%) done (elapsed 2.46 s, remaining 0.62 s)
900000 of 1000000 tuples (90%) done (elapsed 2.81 s, remaining 0.31 s)
1000000 of 1000000 tuples (100%) done (elapsed 3.16 s, remaining 0.00 s)
vacuuming...
creating primary keys...
creating foreign keys...
done in 5.78 s (drop tables 0.00 s, create tables 0.07 s, generate 3.29 s, vacuum 0.84 s, primary keys 0.94 s, foreign keys 0.65 s).

The pgbench_accounts table should now be partitioned by range:

postgres@centos8pg:/home/postgres/ [pgdev] psql -c "\d+ pgbench_accounts" pgbench
Partitioned table "public.pgbench_accounts"
Column  |     Type      | Collation | Nullable | Default | Storage  | Stats target | Description
----------+---------------+-----------+----------+---------+----------+--------------+-------------
aid      | integer       |           | not null |         | plain    |              |
bid      | integer       |           |          |         | plain    |              |
abalance | integer       |           |          |         | plain    |              |
filler   | character(84) |           |          |         | extended |              |
Partition key: RANGE (aid)
Indexes:
"pgbench_accounts_pkey" PRIMARY KEY, btree (aid)
Foreign-key constraints:
"pgbench_accounts_bid_fkey" FOREIGN KEY (bid) REFERENCES pgbench_branches(bid)
Referenced by:
TABLE "pgbench_history" CONSTRAINT "pgbench_history_aid_fkey" FOREIGN KEY (aid) REFERENCES pgbench_accounts(aid)
Partitions: pgbench_accounts_1 FOR VALUES FROM (MINVALUE) TO (100001),
pgbench_accounts_10 FOR VALUES FROM (900001) TO (MAXVALUE),
pgbench_accounts_2 FOR VALUES FROM (100001) TO (200001),
pgbench_accounts_3 FOR VALUES FROM (200001) TO (300001),
pgbench_accounts_4 FOR VALUES FROM (300001) TO (400001),
pgbench_accounts_5 FOR VALUES FROM (400001) TO (500001),
pgbench_accounts_6 FOR VALUES FROM (500001) TO (600001),
pgbench_accounts_7 FOR VALUES FROM (600001) TO (700001),
pgbench_accounts_8 FOR VALUES FROM (700001) TO (800001),
pgbench_accounts_9 FOR VALUES FROM (800001) TO (900001)

The same should work for hash partitioning:

postgres@centos8pg:/home/postgres/ [pgdev] pgbench -i -s 10 --partitions=10 --partition-method=hash --foreign-keys pgbench
dropping old tables...
creating tables...
creating 10 partitions...
generating data...
100000 of 1000000 tuples (10%) done (elapsed 0.19 s, remaining 1.69 s)
200000 of 1000000 tuples (20%) done (elapsed 0.43 s, remaining 1.71 s)
300000 of 1000000 tuples (30%) done (elapsed 0.67 s, remaining 1.55 s)
400000 of 1000000 tuples (40%) done (elapsed 1.03 s, remaining 1.54 s)
500000 of 1000000 tuples (50%) done (elapsed 1.22 s, remaining 1.22 s)
600000 of 1000000 tuples (60%) done (elapsed 1.59 s, remaining 1.06 s)
700000 of 1000000 tuples (70%) done (elapsed 1.80 s, remaining 0.77 s)
800000 of 1000000 tuples (80%) done (elapsed 2.16 s, remaining 0.54 s)
900000 of 1000000 tuples (90%) done (elapsed 2.36 s, remaining 0.26 s)
1000000 of 1000000 tuples (100%) done (elapsed 2.69 s, remaining 0.00 s)
vacuuming...
creating primary keys...
creating foreign keys...
done in 4.99 s (drop tables 0.10 s, create tables 0.08 s, generate 2.74 s, vacuum 0.84 s, primary keys 0.94 s, foreign keys 0.30 s).
postgres@centos8pg:/home/postgres/ [pgdev] psql -c "\d+ pgbench_accounts" pgbench
Partitioned table "public.pgbench_accounts"
Column  |     Type      | Collation | Nullable | Default | Storage  | Stats target | Description
----------+---------------+-----------+----------+---------+----------+--------------+-------------
aid      | integer       |           | not null |         | plain    |              |
bid      | integer       |           |          |         | plain    |              |
abalance | integer       |           |          |         | plain    |              |
filler   | character(84) |           |          |         | extended |              |
Partition key: HASH (aid)
Indexes:
"pgbench_accounts_pkey" PRIMARY KEY, btree (aid)
Foreign-key constraints:
"pgbench_accounts_bid_fkey" FOREIGN KEY (bid) REFERENCES pgbench_branches(bid)
Referenced by:
TABLE "pgbench_history" CONSTRAINT "pgbench_history_aid_fkey" FOREIGN KEY (aid) REFERENCES pgbench_accounts(aid)
Partitions: pgbench_accounts_1 FOR VALUES WITH (modulus 10, remainder 0),
pgbench_accounts_10 FOR VALUES WITH (modulus 10, remainder 9),
pgbench_accounts_2 FOR VALUES WITH (modulus 10, remainder 1),
pgbench_accounts_3 FOR VALUES WITH (modulus 10, remainder 2),
pgbench_accounts_4 FOR VALUES WITH (modulus 10, remainder 3),
pgbench_accounts_5 FOR VALUES WITH (modulus 10, remainder 4),
pgbench_accounts_6 FOR VALUES WITH (modulus 10, remainder 5),
pgbench_accounts_7 FOR VALUES WITH (modulus 10, remainder 6),
pgbench_accounts_8 FOR VALUES WITH (modulus 10, remainder 7),
pgbench_accounts_9 FOR VALUES WITH (modulus 10, remainder 8).

Looks fine. Now you can easily benchmark against a partitioned pgbench_accounts table.

Cet article PostgreSQL 13 will come with partitioning support for pgbench est apparu en premier sur Blog dbi services.

PostgreSQL check_function_bodies, what is it good for?

Sun, 2019-10-27 03:35

One of the probably lesser known PostgreSQL parameters is check_function_bodies. If you know Oracle, then you for sure faced “invalid objects” a lot. In PostgreSQL, by default, there is nothing like an invalid object. That implies that you can not create a function or procedure which references an object that does not yet exist.

Lets assume you want to create a function like this, but the table “t1” does not exist:

postgres=# create or replace function f1 () returns setof t1 as
$$
select * from t1;
$$ language 'sql';
ERROR:  type "t1" does not exist

PostgreSQL will not create the function as a dependent objects does not exist. Once the table is there the function will be created:

postgres=# create table t1 ( a int );
CREATE TABLE
postgres=# insert into t1 values(1);
INSERT 0 1
postgres=# create or replace function f1 () returns setof t1 as
$$
select * from t1;
$$ language 'sql';
CREATE FUNCTION
postgres=# select * from f1();
a
---
1

The issue with that is, that you need to follow the order in which functions gets created. Especially when you need to load functions for other users that can easily become tricky and time consuming. This is where check_function_bodies helps:

postgres=# set check_function_bodies = false;
SET
postgres=# create or replace function f2 () returns setof t1 as
$$
select * from t2;
$$ language 'sql';
CREATE FUNCTION

The function was created although t2 did not exist. Executing the function right now of course will generate an error:

postgres=# select * from f2();
ERROR:  relation "t2" does not exist
LINE 2: select * from t2;
^
QUERY:
select * from t2;

CONTEXT:  SQL function "f2" during startup

Once the table is there all is fine:

postgres=# create table t2 ( a int );
CREATE TABLE
postgres=# insert into t2 values (2);
INSERT 0 1
postgres=# select * from f2();
a
---
2

This is very helpful when loading objects provided by an external vendor. pg_dump is doing that by default.

Cet article PostgreSQL check_function_bodies, what is it good for? est apparu en premier sur Blog dbi services.

AEM Forms – No SSLMutualAuthProvider available

Sat, 2019-10-26 15:00

In the process of setting up the AEM Workbench to use 2-way-SSL, you will need at some point to use a Hybrid Domain and a specific Authentication Provider. Depending on the version of the AEM that you are using, this Authentication Provider might not be present and therefore you will never be able to set that up properly. In this blog, I will describe what was done in our case to solve this problem.

The first time we tried to set that up (WebLogic Server 12.2, AEM 6.4.0), it just wasn’t working. Therefore, we opened a case with the Adobe Support and after quite some time, we found out that the documentation was not complete (#CQDOC-13273) and that there were actually missing steps and missing configuration inside the AEM to allow the 2-way-SSL to work. So basically everything said that the 2-way-SSL was possible but there were just missing pieces inside AEM to have it really working. Therefore after discussion & investigation with the Adobe Support Engineers (#NPR-26490), they provided us the missing piece: adobe-usermanager-ssl-dsc.jar.

When you install AEM Forms, it will automatically deploy a bunch of DSC (jar file) to provide all features of the AEM Forms. These are a few examples:

  • adobe-pdfservices-dsc.jar
  • adobe-usermanager-dsc.jar
  • adobe-jobmanager-dsc.jar
  • adobe-scheduler-weblogic-dsc.jar

Therefore, our AEM Forms version at that time (mid-2018, AEM 6.4.0) was missing one of these DSC and it was the root cause of our issue. So what can you do fix that? Well you just have deploy it and since we are anyway in the middle of working with the AEM Workbench to set it up with 2-way-SSL, that’s perfect. While the Workbench is still able to use 1-way-SSL (don’t set your Application Server in 2-way-SSL or revert it to 1-way-SSL):

  • Download or request the file “adobe-usermanager-ssl-dsc.jar” for your AEM version to the Adobe Support
  • Open the AEM Workbench (run the workbench.exe file)
  • Click on “File > Login
  • Set the Log on to to: <AEM_HOST> – SimpleAuth (or whatever the name of your SimpleAuth is)
  • Set the Username to: administrator (or whatever other account you have)
  • Set the Password for this account
  • Click on “Login
  • Click on “Window > Show View > Components
  • The Components window should be opened (if not already done before) somewhere on the screen (most probably on the left side)
  • Inside the Components window, right click on the “Components” folder and select “Install Component …
  • Find the file “adobe-usermanager-ssl-dsc.jar” that has been downloaded earlier, select it and click on “Open
  • Right click on the “Components” folder and select “Refresh
  • Expand the “Components” folder (if not already done), and look for the component named “SSLAuthProvider
  • If this component isn’t started yet (there is a red square on the package), then start it using the following steps:
    • Right click on “SSLAuthProvider
    • Select “Start Component

Note: If the “SSLAuthProvider” component already exists, then you will see an error. This is fine, it just needs to be there and to be started/running. If this is the case then it’s all good.

Workbench - Open components

Workbench - Refresh components

Workbench - Start component

Once the SSLAuthProvider DSC has been installed and is running, you should be able to see the SSLMutualAuthProvider in the list of custom providers while creating the Hybrid Domain on the AdminUI. Adobe was normally supposed to fix this in the following releases but I didn’t get the opportunity to test the installation of AEM 6.5 from scratch yet. If you have this information, don’t hesitate to share!

Cet article AEM Forms – No SSLMutualAuthProvider available est apparu en premier sur Blog dbi services.

AEM Forms – “2-way-SSL” Setup and Workbench configuration

Sat, 2019-10-26 14:45

In the past two years almost, I have been working with AEM (Adobe Experience Manager) Forms. The road taken by this project was full of problem because of security constraints that AEM has/had big trouble dealing with. In this blog, I will talk about one security aspect which brings some trouble: how to setup and use the “2-way-SSL” (I will describe below why I put that in quote) for the AEM Workbench.

I have been using AEM Forms 6.4.0 initially (20180228) with its associated Workbench version. I will consider that the AEM Forms has been installed already and is working properly. In this case, I used AEM Forms on a WebLogic Server (12.2) which I configured in HTTPS. So once you have that, what do you need to do to configure and use the AEM Workbench with “2-way-SSL”? Well first, let’s ensure that the AEM Workbench is working properly and then start with the setup.

Open the AEM Workbench and configure a new “Server”:

  • Open the AEM Workbench (run the workbench.exe file)
  • Click on “File > Login
  • Click on “Configure...”
  • Click on the “+” sign to add a new Server
    • Set the Server Title to: <AEM_HOST> – SimpleAuth
    • Set the Hostname to: <AEM_HOST>
    • Set the Protocol to: Simple Object Access Protocol (SOAP/HTTPs)
    • Set the Server Port Number to: <AEM_PORT>
    • Click on “OK
  • Click on “OK
  • Set the Log on to the newly created Server (“<AEM_HOST> – SimpleAuth“)
  • Set the Username to: administrator (or whatever other account you have)
  • Set the Password for this account
  • Click on “Login

Workbench login 1-way-SSL

If everything was done properly, the login should be working. The next step is to configure AEM for the “2-way-SSL” communications. As mentioned at the beginning of this blog, I put that in quote because it’s a 2-way-SSL but there is one security layer that is bypassed when doing that. With the AEM Workbench in 1-way-SSL, you need to enter a username and a credential. Adding a 2-way-SSL instead would normally just add another layer of security where the server and client will exchange their certificate and will trust each other but the user’s authentication is still needed!

In the case of the AEM Workbench, the “2-way-SSL” setup actually completely bypass the user’s authentication and therefore I do not really consider that as a real 2-way-SSL setup… It might even be considered as a security issue (it’s a shame for a feature that is supposed to increase security) because, as you will see below, as soon as you have the Client SSL Certificate (and its password obviously), then you will be able to access AEM Workbench. So protect this certificate with great care.

To configure the AEM, you will then need to create an Hybrid Domain:

  • Open the AEM AdminUI (https://<AEM_HOST>:<AEM_PORT>/adminui)
  • Login with the administrator account (or whatever other account you have)
  • Navigate to: Settings > User Management > Domain Management
  • Click on “New Hybrid Domain
    • Set the ID to: SSLMutualAuthProvider
    • Set the Name to: SSLMutualAuthProvider
    • Check the “Enable Account Locking” checkbox
    • Uncheck the “Enable Just In Time Provisioning” checkbox
    • Click on “Add Authentication
      • Set the “Authentication Provider” to: Custom
      • Check the “SSLMutualAuthProvider” checkbox
      • Click on “OK
    • Click on “OK

Note: If “SSLMutualAuthProvider” isn’t available on the Authentication page, then please check this blog.

Hybrid Domain 1

Hybrid Domain 2

Hybrid Domain 3

Then you will need to create a user. In this example, I will use a generic account but it is possible to have several accounts for each of your devs for example, in which case each user must have their own SSL Certificate. The user Canonical Name and ID must absolutely match the CN used to generate the SSL Certificate that the Client will use. So if you generated an SSL Certificate for the Client with “/C=CH/ST=Jura/L=Delemont/O=dbi services/OU=IT/CN=aem-dev“, then the Canonical Name and ID to be used for the user in AEM should be “aem-dev“:

  • Navigate to: Settings > User Management > Users and Groups
  • Click on “New User
  • On the New User (Step 1 of 3) screen:
    • Uncheck the “System Generated” checkbox
    • Set the Canonical Name to: <USER_CN>
    • Set the First Name to: 2-way-SSL
    • Set the Last Name to: User
    • Set the Domain to: SSLMutualAuthProvider
    • Set the User Id to: <USER_CN>
    • Click on “Next
  • On the New User: 2-way-SSL (Step 2 of 3) screen:
    • Click on “Next
  • On the New User: 2-way-SSL (Step 3 of 3) screen:
    • Click on “Find Roles
      • Check the checkbox for the Role Name: Application Administrator (or any other valid role that you want this user to be able to use)
      • Click on “OK
  • Click on “Finish

User 1

User 2

User 3

At this point, you can configure your Application Server to handle the 2-way-SSL communications. In WebLogic Server, this is done by setting the “Two Way Client Cert Behavior” to “Client Certs Requested and Enforced” in the SSL subtab of the Managed Server(s) hosting the AEM Forms applications.

Finally the last step is to get back to the AEM Workbench and try your 2-way-SSL communications. If you try again to use the SimpleAuth that we defined above, it should fail because the Application Server will require the Client SSL Certificate, which isn’t provided in this case. So let’s create a new “Server”:

  • Click on “File > Login
  • Click on “Configure...”
  • Click on the “+” sign to add a new Server
    • Set the Server Title to: <AEM_HOST> – MutualAuth
    • Set the Hostname to: <AEM_HOST>
    • Set the Protocol to: Simple Object Access Protocol (SOAP/HTTPs) Mutual Auth
    • Set the Server Port Number to: <AEM_PORT>
    • Click on “OK
  • Click on “OK
  • Set the Log on to the newly created Server (“<AEM_HOST> – MutualAuth“)
  • Set the Key Store to: file:C:\Users\Morgan\Documents\AEM_Workbench\aem-dev.jks (Adapt to wherever you put the keystore)
  • Set the Key Store Password to: <KEYSTORE_PWD>
  • Set the Trust Store to: file:C:\Users\Morgan\Documents\AEM_Workbench\trust.jks (Adapt to wherever you put the truststore)
  • Set the Trust Store Password to: <TRUSTSTORE_PWD>
  • Click on “Login

Workbench login 2-way-SSL

In the above login screen, the KeyStore is the SSL Certificate that was created for the Client and the TrustStore will be used to validate/trust the SSL Certificate of the AEM Server. It can be the cacerts from the AEM Workbench for example. If you are using a Self-Signed SSL Certificate, don’t forget to add the Trust Chain into the TrustStore.

Cet article AEM Forms – “2-way-SSL” Setup and Workbench configuration est apparu en premier sur Blog dbi services.

Adding a dbvisit standby database on the ODA in a non-OMF environment

Wed, 2019-10-23 06:04

I have recently been working on a customer project where I had been challenged adding a dbvisit standby database on an ODA X7-2M, named ODA03. The existing customer environment was composed of Oracle Standard 12.2 version database. The primary database, myDB, is running on server named srv02 and using a non-OMF configuration. On the ODA side we are working with OMF configuration. The dbvisit version available at that time was version 8. You need to know that version 9 is currently the last one and brings some new cool features. Through this blog I would like to share with you my experience, the problem I have been facing and the solution I could put in place.

Preparing the instance on the ODA

First of all I have been creating an instance only database on the ODA.

root@ODA03 ~]# odacli list-dbhomes

ID                                       Name                 DB Version                               Home Location                                 Status   
---------------------------------------- -------------------- ---------------------------------------- --------------------------------------------- ----------
ec33e32a-37d1-4d0d-8c40-b358dcf5660c     OraDB12201_home1     12.2.0.1.180717                          /u01/app/oracle/product/12.2.0.1/dbhome_1     Configured

[root@ODA03 ~]# odacli create-database -m -u myDB_03 -dn domain.name -n myDB -r ACFS -io -dh ec33e32a-37d1-4d0d-8c40-b358dcf5660c
Password for SYS,SYSTEM and PDB Admin:

Job details
----------------------------------------------------------------
                     ID:  96fd4d07-4604-4158-9c25-702c01f4493e
            Description:  Database service creation with db name: myDB
                 Status:  Created
                Created:  May 15, 2019 4:29:15 PM CEST
                Message:

Task Name                                Start Time                          End Time                            Status
---------------------------------------- ----------------------------------- ----------------------------------- ----------

[root@ODA03 ~]# odacli describe-job -i 96fd4d07-4604-4158-9c25-702c01f4493e

Job details
----------------------------------------------------------------
                     ID:  96fd4d07-4604-4158-9c25-702c01f4493e
            Description:  Database service creation with db name: myDB
                 Status:  Success
                Created:  May 15, 2019 4:29:15 PM CEST
                Message:

Task Name                                Start Time                          End Time                            Status
---------------------------------------- ----------------------------------- ----------------------------------- ----------
Setting up ssh equivalance               May 15, 2019 4:29:16 PM CEST        May 15, 2019 4:29:16 PM CEST        Success
Creating volume datmyDB                    May 15, 2019 4:29:16 PM CEST        May 15, 2019 4:29:38 PM CEST        Success
Creating volume reco                     May 15, 2019 4:29:38 PM CEST        May 15, 2019 4:30:00 PM CEST        Success
Creating ACFS filesystem for DATA        May 15, 2019 4:30:00 PM CEST        May 15, 2019 4:30:17 PM CEST        Success
Creating ACFS filesystem for RECO        May 15, 2019 4:30:17 PM CEST        May 15, 2019 4:30:35 PM CEST        Success
Database Service creation                May 15, 2019 4:30:35 PM CEST        May 15, 2019 4:30:51 PM CEST        Success
Auxiliary Instance Creation              May 15, 2019 4:30:35 PM CEST        May 15, 2019 4:30:47 PM CEST        Success
password file creation                   May 15, 2019 4:30:47 PM CEST        May 15, 2019 4:30:49 PM CEST        Success
archive and redo log location creation   May 15, 2019 4:30:49 PM CEST        May 15, 2019 4:30:49 PM CEST        Success
updating the Database version            May 15, 2019 4:30:49 PM CEST        May 15, 2019 4:30:51 PM CEST        Success

Next steps are really common DBA operations :

  • Create a pfile from the current primary database
  • Transfer the pfile to the ODA
  • Update the pfile as needed (path, db_unique_name, …)
  • Create a spfile from the pfile on the new ODA database
  • Apply ODA specific instance parameters
  • Copy or create the password file with same password

The parameters that are mandatory to be set on the ODA instance are the following :
*.db_create_file_dest=’/u02/app/oracle/oradata/myDB_03′
*.db_create_online_log_dest_1=’/u03/app/oracle/redo’
*.db_recovery_file_dest=’/u03/app/oracle/fast_recovery_area’

Also all the convert parameters should be removed. Using convert parameter is incompatible with OMF.

Creating the standby database Using dbvisit

I first tried to use dbvisit to create the standby database.

As usual and common dbvisit operation, I first created the DDC configuration file from the primary server :

oracle@srv02:/u01/app/dbvisit/standby/ [myDB] ./dbvctl -o setup
...
...
...
Below are the list of configuration variables provided during the setup process:

Configuration Variable             Value Provided
======================             ==============
ORACLE_SID                         myDB
ORACLE_HOME                        /opt/oracle/product/12.2.0

SOURCE                             srv02
ARCHSOURCE                         /u03/app/oracle/dbvisit_arch/myDB
RAC_DR                             N
USE_SSH                            N
DESTINATION                        ODA03
NETPORT                            7890
DBVISIT_BASE_DR                    /u01/app/dbvisit
ORACLE_HOME_DR                     /u01/app/oracle/product/12.2.0.1/dbhome_1
DB_UNIQUE_NAME_DR                  myDB_03
ARCHDEST                           /u03/app/oracle/dbvisit_arch/myDB
ORACLE_SID_DR                      myDB
ENV_FILE                           myDBSTD1

Are these variables correct?  [Yes]:
...
...
...

I then used this DDC configuration file to create the standby database :

oracle@srv02:/u01/app/dbvisit/standby/ [myDB] ./dbvctl -d myDBSTD1 --csd


-------------------------------------------------------------------------------

INIT ORA PARAMETERS
-------------------------------------------------------------------------------
*              audit_file_dest                         /u01/app/oracle/admin/myDB/adump
*              compatible                              12.2.0
*              control_management_pack_access          NONE
*              db_block_size                           8192
*              db_create_file_dest                     /u02/app/oracle/oradata/myDB_03
*              db_create_online_log_dest_1             /u03/app/oracle/redo
*              db_domain
*              db_name                                 myDB
*              db_recovery_file_dest                   /u03/app/oracle/fast_recovery_area
*              db_recovery_file_dest_size              240G
*              db_unique_name                          myDB_03
*              diagnostic_dest                         /u01/app/oracle
*              dispatchers                             (PROTOCOL=TCP) (SERVICE=myDBXDB)
*              instance_mode                           READ-WRITE
*              java_pool_size                          268435456
*              log_archive_dest_1                      LOCATION=USE_DB_RECOVERY_FILE_DEST
*              open_cursors                            3000
*              optimizer_features_enable               12.2.0.1
*              pga_aggregate_target                    4194304000
*              processes                               8000
*              remote_login_passwordfile               EXCLUSIVE
*              resource_limit                          TRUE
*              sessions                                7552
*              sga_max_size                            53687091200
*              sga_target                              26843545600
*              shared_pool_reserved_size               117440512
*              spfile                                  OS default
*              statistics_level                        TYPICAL
*              undo_retention                          300
*              undo_tablespace                         UNDOTBS1

-------------------------------------------------------------------------------

Status: VALID

What would you like to do:
   1 - Create standby database using existing saved template
   2 - View content of existing saved template
   3 - Return to the previous menu
   Please enter your choice [1]:

This operation failed with following errors :

Cannot create standby data or temp file /usr/oracle/oradata/myDB/myDB_bi_temp01.dbf for
primary file /usr/oracle/oradata/myDB/myDB_bi_temp01.dbf as location /usr/oracle/oradata/myDB
does not exist on the standby.

A per dbvisit documentation, dbvisit standby is certified ODA and fully compatible with non-OMF and OMF databases. This is correct, the only distinction is that the full environment needs to be in same configuration. That’s to say that if the primary is OMF, the standby is expected to be OMF. If the primary is running a non-OMF configuration, the standby should be using non-OMF as well.

Using RMAN

I decided to duplicate the database using RMAN and a backup that I transferred locally on the ODA. The backup was the previous nightly inc0 backup. Before running the rman duplication I executed a last archive log backup to make sure to have the most recent archive used in the duplication.

I’m taking this opportunity to highlight that, thanks to ODA NVMe technology, the duplication of the 3 TB database without multiple channel (standard edition) took a bit more than 2 hours only. On the existing servers this took about 10 hours.

I added following tns entry in the tnsnames.ora.

myDBSRV3 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ODA03.domain.name)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = myDB)
      (UR = A)
    )
  )

Of course I could have been using a local connection.

I made sure the database to be in nomount status and ran the rman duplication :

oracle@ODA03:/opt/oracle/backup/ [myDB] rmanh

Recovery Manager: Release 12.2.0.1.0 - Production on Mon May 20 13:24:29 2019

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

RMAN> connect auxiliary sys@myDBSRV3

auxiliary database Password:
connected to auxiliary database: myDB (not mounted)

RMAN> run {
2> duplicate target database for standby dorecover backup location '/opt/oracle/backup/myDB';
3> }

Starting Duplicate Db at 20-MAY-2019 13:25:51

contents of Memory Script:
{
   sql clone "alter system set  control_files =
  ''/u03/app/oracle/redo/myDB_03/controlfile/o1_mf_gg4qvpnn_.ctl'' comment=
 ''Set by RMAN'' scope=spfile";
   restore clone standby controlfile from  '/opt/oracle/backup/myDB/ctl_myDB_myDB_s108013_p1_newbak.ctl';
}
executing Memory Script

sql statement: alter system set  control_files =   ''/u03/app/oracle/redo/myDB_03/controlfile/o1_mf_gg4qvpnn_.ctl'' comment= ''Set by RMAN'' scope=spfile

Starting restore at 20-MAY-2019 13:25:51
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=9186 device type=DISK

channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u03/app/oracle/redo/myDB_03/controlfile/o1_mf_gg4qvpnn_.ctl
Finished restore at 20-MAY-2019 13:25:52

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=9186 device type=DISK

contents of Memory Script:
{
   set until scn  49713361973;
   set newname for clone tempfile  1 to new;
   set newname for clone tempfile  2 to new;
   switch clone tempfile all;
   set newname for clone datafile  1 to new;
   set newname for clone datafile  2 to new;
   set newname for clone datafile  3 to new;
   set newname for clone datafile  4 to new;
   set newname for clone datafile  5 to new;
   set newname for clone datafile  6 to new;
   set newname for clone datafile  7 to new;
   set newname for clone datafile  8 to new;
   set newname for clone datafile  10 to new;
   set newname for clone datafile  11 to new;
   set newname for clone datafile  12 to new;
   set newname for clone datafile  13 to new;
   set newname for clone datafile  14 to new;
   set newname for clone datafile  15 to new;
   set newname for clone datafile  16 to new;
   set newname for clone datafile  17 to new;
   set newname for clone datafile  18 to new;
   restore
   clone database
   ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /u02/app/oracle/oradata/myDB_03/myDB_03/datafile/o1_mf_temp_%u_.tmp in control file
renamed tempfile 2 to /u02/app/oracle/oradata/myDB_03/myDB_03/datafile/o1_mf_lx_bi_te_%u_.tmp in control file

executing command: SET NEWNAME

...
...
...

executing command: SET NEWNAME

Starting restore at 20-MAY-2019 13:25:57
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u02/app/oracle/oradata/myDB_03/myDB_03/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u02/app/oracle/oradata/myDB_03/myDB_03/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u02/app/oracle/oradata/myDB_03/myDB_03/datafile/o1_mf_lxdataid_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to /u02/app/oracle/oradata/myDB_03/myDB_03/datafile/o1_mf_renderz2_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00007 to /u02/app/oracle/oradata/myDB_03/myDB_03/datafile/o1_mf_lx_ods_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00008 to /u02/app/oracle/oradata/myDB_03/myDB_03/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00013 to /u02/app/oracle/oradata/myDB_03/myDB_03/datafile/o1_mf_renderzs_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00015 to /u02/app/oracle/oradata/myDB_03/myDB_03/datafile/o1_mf_lx_stagi_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /opt/oracle/backup/myDB/inc0_myDB_s107963_p1
...
...
...
archived log file name=/opt/oracle/backup/myDB/1_58043_987102791.dbf thread=1 sequence=58043
archived log file name=/opt/oracle/backup/myDB/1_58044_987102791.dbf thread=1 sequence=58044
archived log file name=/opt/oracle/backup/myDB/1_58045_987102791.dbf thread=1 sequence=58045
archived log file name=/opt/oracle/backup/myDB/1_58046_987102791.dbf thread=1 sequence=58046
archived log file name=/opt/oracle/backup/myDB/1_58047_987102791.dbf thread=1 sequence=58047
archived log file name=/opt/oracle/backup/myDB/1_58048_987102791.dbf thread=1 sequence=58048
archived log file name=/opt/oracle/backup/myDB/1_58049_987102791.dbf thread=1 sequence=58049
archived log file name=/opt/oracle/backup/myDB/1_58050_987102791.dbf thread=1 sequence=58050
media recovery complete, elapsed time: 00:12:40
Finished recover at 20-MAY-2019 16:06:22
Finished Duplicate Db at 20-MAY-2019 16:06:39

I could check and see that my standby database has been successfully created on the ODA :

oracle@ODA03:/u01/app/oracle/local/dmk/etc/ [myDB] myDB
********* dbi services Ltd. *********
STATUS                 : MOUNTED
DB_UNIQUE_NAME         : myDB_03
OPEN_MODE              : MOUNTED
LOG_MODE               : ARCHIVELOG
DATABASE_ROLE          : PHYSICAL STANDBY
FLASHBACK_ON           : NO
FORCE_LOGGING          : YES
CDB Enabled            : NO
*************************************

As a personal note, I really found using oracle RMAN more convenient to duplicate a database. Albeit dbvisit script and tool is really stable, I think that this will give you more flexibility.

Registering the database in the grid cluster

As next step I registered the database in the grid.

oracle@ODA03:/u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/ [LX] srvctl add database -db MyDB_03 -oraclehome /u01/app/oracle/product/12.2.0.1/dbhome_1 -dbtype SINGLE -instance MyDB -domain team-w.local -spfile /u02/app/oracle/oradata/MyDB_03/dbs/spfileMyDB.ora -pwfile /u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/orapwMyDB -role PHYSICAL_STANDBY -startoption MOUNT -stopoption IMMEDIATE -dbname MyDB -node ODA03 -acfspath "/u02/app/oracle/oradata/MyDB_03,/u03/app/oracle"

I stopped the database :

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

And started it again with the grid infrastructure :

oracle@ODA03:/u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/ [MyDB] MyDB
********* dbi services Ltd. *********
STATUS          : STOPPED
*************************************

oracle@ODA03:/u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/ [MyDB] srvctl status database -d MyDB_03
Instance MyDB is not running on node ODA03

oracle@ODA03:/u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/ [MyDB] srvctl start database -d MyDB_03

oracle@ODA03:/u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/ [MyDB] srvctl status database -d MyDB_03
Instance MyDB is running on node ODA03
dbvisit synchronization

We now have our standby database created on the ODA. We just need to synchronize it with the primary.

Run a gap report

Executing a gap report, we can see that the newly created database is running almost 4 hours behind.

oracle@srv02:/u01/app/dbvisit/standby/ [rdbms12201] ./dbvctl -d myDBSTD1 -i
=============================================================
Dbvisit Standby Database Technology (8.0.26_0_g3fdeaadd) (pid 321953)
dbvctl started on srv02: Mon May 20 16:24:35 2019
=============================================================


Dbvisit Standby log gap report for myDB thread 1 at 201905201624:
-------------------------------------------------------------
Destination database on ODA03 is at sequence: 58050.
Source database on srv02 is at log sequence: 58080.
Source database on srv02 is at archived log sequence: 58079.
Dbvisit Standby last transfer log sequence: .
Dbvisit Standby last transfer at: .

Archive log gap for thread 1:  29.
Transfer log gap for thread 1: 58079.
Standby database time lag (DAYS-HH:MI:SS): +03:39:01.


=============================================================
dbvctl ended on srv02: Mon May 20 16:24:40 2019
=============================================================
Send the archive logs from primary to the standby database

I have been shipping the last archive logs from the primary database to the newly created standby.

oracle@srv02:/u01/app/dbvisit/standby/ [rdbms12201] ./dbvctl -d myDBSTD1
=============================================================
Dbvisit Standby Database Technology (8.0.26_0_g3fdeaadd) (pid 326409)
dbvctl started on srv02: Mon May 20 16:29:14 2019
=============================================================

>>> Obtaining information from standby database (RUN_INSPECT=Y)... done
    Thread: 1 Archive log gap: 30. Transfer log gap: 58080
>>> Sending heartbeat message... skipped
>>> First time Dbvisit Standby runs, Dbvisit Standby configuration will be copied to
    ODA03...
>>> Transferring Log file(s) from myDB on srv02 to ODA03 for thread 1:

    thread 1 sequence 58051 (1_58051_987102791.dbf)
    thread 1 sequence 58052 (1_58052_987102791.dbf)
...
...
...
    thread 1 sequence 58079 (1_58079_987102791.dbf)
    thread 1 sequence 58080 (1_58080_987102791.dbf)

=============================================================
dbvctl ended on srv02: Mon May 20 16:30:50 2019
=============================================================
Apply archive logs on the standby database

Then I could finally apply the archive logs on the standby database.

oracle@ODA03:/u01/app/dbvisit/standby/ [myDB] ./dbvctl -d myDBSTD1
=============================================================
Dbvisit Standby Database Technology (8.0.26_0_g3fdeaadd) (pid 21504)
dbvctl started on ODA03: Mon May 20 16:33:42 2019
=============================================================

>>> Sending heartbeat message... skipped

>>> Applying Log file(s) from srv02 to myDB on ODA03:

    thread 1 sequence 58051 (1_58051_987102791.arc)
    thread 1 sequence 58052 (1_58052_987102791.arc)
...
...
...
    thread 1 sequence 58079 (1_58079_987102791.arc)
    thread 1 sequence 58080 (1_58080_987102791.arc)
    Last applied log(s):
    thread 1 sequence 58080

    Next SCN required for recovery 49719323442 generated at 2019-05-20:16:27:09 +02:00.
    Next required log thread 1 sequence 58081

=============================================================
dbvctl ended on ODA03: Mon May 20 16:36:52 2019
=============================================================
Run a gap report

Running a new gap report, we can see that there is no delta between the primary and the standby database.

oracle@srv02:/u01/app/dbvisit/standby/ [rdbms12201] ./dbvctl -d myDBSTD1 -i
=============================================================
Dbvisit Standby Database Technology (8.0.26_0_g3fdeaadd) (pid 335068)
dbvctl started on srv02: Mon May 20 16:37:53 2019
=============================================================


Dbvisit Standby log gap report for myDB thread 1 at 201905201637:
-------------------------------------------------------------
Destination database on ODA03 is at sequence: 58081.
Source database on srv02 is at log sequence: 58082.
Source database on srv02 is at archived log sequence: 58081.
Dbvisit Standby last transfer log sequence: 58081.
Dbvisit Standby last transfer at: 2019-05-20 16:37:36.

Archive log gap for thread 1:  0.
Transfer log gap for thread 1: 0.
Standby database time lag (DAYS-HH:MI:SS): +00:00:01.


=============================================================
dbvctl ended on srv02: Mon May 20 16:37:57 2019
=============================================================
Preparing the database for switchover

Are we done? Absolutely not. In order to be able to successfully perform a switchover, 3 main modifications are mandatory on the non-ODA Server (running non-OMF database) :

  • The future database files should be OMF
  • The online redo log should be newly created
  • The temporary file should be newly created

Otherwise you might end with unsuccessfull switchover having below errors :

>>> Starting Switchover between srv02 and ODA03

Running pre-checks       ... failed
No rollback action required

>>> Database on server srv02 is still a Primary Database
>>> Database on server ODA03 is still a Standby Database


<<<>>>
PID:40386
TRACEFILE:40386_dbvctl_switchover_myDBSTD1_201905272153.trc
SERVER:srv02
ERROR_CODE:1
Remote execution error on ODA03.

====================Remote Output start: ODA03=====================
<<<>>>
PID:92292
TRACEFILE:92292_dbvctl_f_gs_get_info_standby_myDBSTD1_201905272153.trc
SERVER:ODA03
ERROR_CODE:2146
Dbvisit Standby cannot proceed:
Cannot create standby data or temp file /usr/oracle/oradata/myDB/temp01.dbf for primary
file /usr/oracle/oradata/myDB/temp01.dbf as location /usr/oracle/oradata/myDB does not
exist on the standby.
Cannot create standby data or temp file /usr/oracle/oradata/myDB/lx_bi_temp01.dbf for
primary file /usr/oracle/oradata/myDB/lx_bi_temp01.dbf as location /usr/oracle/oradata/myDB
does not exist on the standby.
Review the following standby database parameters:
        db_create_file_dest = /u02/app/oracle/oradata/myDB_03
        db_file_name_convert =
>>>> Dbvisit Standby terminated <<<>>> Dbvisit Standby terminated <<<<
Having new OMF configuration

There is no need to convert the full database into OMF. A database can run having both file naming configuration, non-OMF and OMF. We just need to have the database working now with OMF configuration. For this we will just apply the appropriate value to the init parameter. In my case the existing primary database was storing all data and redo files in the /opt/oracle/oradata directory.

SQL> alter system set DB_CREATE_FILE_DEST='/opt/oracle/oradata' scope=both;

System wurde geändert.

SQL> alter system set DB_CREATE_ONLINE_LOG_DEST_1='/opt/oracle/oradata' scope=both;

System wurde geändert.
Refresh the online log

We will create new OMF redo log files as described below.

The current redo log configuration :

SQL> select v$log.group#, member, v$log.status from v$logfile, v$log where v$logfile.group#=v$log.group#;

    GROUP# MEMBER                                             STATUS
---------- -------------------------------------------------- ----------
        12 /opt/oracle/oradata/myDB/redo12.log                  ACTIVE
        13 /opt/oracle/oradata/myDB/redo13.log                  CURRENT
        15 /opt/oracle/oradata/myDB/redo15.log                  INACTIVE
        16 /opt/oracle/oradata/myDB/redo16.log                  INACTIVE
         1 /opt/oracle/oradata/myDB/redo1.log                   INACTIVE
         2 /opt/oracle/oradata/myDB/redo2.log                   INACTIVE
        17 /opt/oracle/oradata/myDB/redo17.log                  INACTIVE
        18 /opt/oracle/oradata/myDB/redo18.log                  INACTIVE
        19 /opt/oracle/oradata/myDB/redo19.log                  INACTIVE
        20 /opt/oracle/oradata/myDB/redo20.log                  INACTIVE
         3 /opt/oracle/oradata/myDB/redo3.log                   INACTIVE
         4 /opt/oracle/oradata/myDB/redo4.log                   INACTIVE
         5 /opt/oracle/oradata/myDB/redo5.log                   INACTIVE
         6 /opt/oracle/oradata/myDB/redo6.log                   INACTIVE
         7 /opt/oracle/oradata/myDB/redo7.log                   INACTIVE
         8 /opt/oracle/oradata/myDB/redo8.log                   ACTIVE
         9 /opt/oracle/oradata/myDB/redo9.log                   ACTIVE
        10 /opt/oracle/oradata/myDB/redo10.log                  ACTIVE
        11 /opt/oracle/oradata/myDB/redo11.log                  ACTIVE
        14 /opt/oracle/oradata/myDB/redo14.log                  INACTIVE

For all INACTIVE redo log groups, we will be able to drop the group and create it again with the OMF naming convention :

SQL> alter database drop logfile group 1;

Datenbank wurde geändert.

SQL> alter database add logfile group 1;

Datenbank wurde geändert.

In order to move to the next redo group and release the current one, we will run a switch log file :

SQL> alter system switch logfile;

System wurde geändert.

To move the ACTIVE redo log to INACTIVE we will run a checkpoint :

SQL> alter system checkpoint;

System wurde geändert.

And then drop and recreate the last INACTIVE redo groups :

SQL> alter database drop logfile group 10;

Datenbank wurde geändert.

SQL> alter database add logfile group 10;

Datenbank wurde geändert.

To finally have all our online log with OMF format :

SQL> select v$log.group#, member, v$log.status from v$logfile, v$log where v$logfile.group#=v$log.group# order by group#;

    GROUP# MEMBER                                                       STATUS
---------- ------------------------------------------------------------ ----------
         1 /opt/oracle/oradata/myDB/onlinelog/o1_mf_1_ggqx5zon_.log       INACTIVE
         2 /opt/oracle/oradata/myDB/onlinelog/o1_mf_2_ggqxjky2_.log       INACTIVE
         3 /opt/oracle/oradata/myDB/onlinelog/o1_mf_3_ggqxjodl_.log       INACTIVE
         4 /opt/oracle/oradata/myDB/onlinelog/o1_mf_4_ggqxkddc_.log       INACTIVE
         5 /opt/oracle/oradata/myDB/onlinelog/o1_mf_5_ggqxkj1t_.log       INACTIVE
         6 /opt/oracle/oradata/myDB/onlinelog/o1_mf_6_ggqxkmnm_.log       CURRENT
         7 /opt/oracle/oradata/myDB/onlinelog/o1_mf_7_ggqxn373_.log       UNUSED
         8 /opt/oracle/oradata/myDB/onlinelog/o1_mf_8_ggqxn7b3_.log       UNUSED
         9 /opt/oracle/oradata/myDB/onlinelog/o1_mf_9_ggqxnbxd_.log       UNUSED
        10 /opt/oracle/oradata/myDB/onlinelog/o1_mf_10_ggqxvlbf_.log      UNUSED
        11 /opt/oracle/oradata/myDB/onlinelog/o1_mf_11_ggqxvnyg_.log      UNUSED
        12 /opt/oracle/oradata/myDB/onlinelog/o1_mf_12_ggqxvqyp_.log      UNUSED
        13 /opt/oracle/oradata/myDB/onlinelog/o1_mf_13_ggqxvv2o_.log      UNUSED
        14 /opt/oracle/oradata/myDB/onlinelog/o1_mf_14_ggqxxcq7_.log      UNUSED
        15 /opt/oracle/oradata/myDB/onlinelog/o1_mf_15_ggqxxgfg_.log      UNUSED
        16 /opt/oracle/oradata/myDB/onlinelog/o1_mf_16_ggqxxk67_.log      UNUSED
        17 /opt/oracle/oradata/myDB/onlinelog/o1_mf_17_ggqxypwg_.log      UNUSED
        18 /opt/oracle/oradata/myDB/onlinelog/o1_mf_18_ggqy1z78_.log      UNUSED
        19 /opt/oracle/oradata/myDB/onlinelog/o1_mf_19_ggqy2270_.log      UNUSED
        20 /opt/oracle/oradata/myDB/onlinelog/o1_mf_20_ggqy26bj_.log      UNUSED

20 Zeilen ausgewählt.
Refresh temporary file

The database was using 2 temp tablespaces : TEMP and MyDB_BI_TEMP.

We first need to add new temp files in OMF format for both tablespaces.

SQL> alter tablespace TEMP add tempfile size 20G;

Tablespace wurde geändert.

SQL> alter tablespace myDB_BI_TEMP add tempfile size 20G;

Tablespace wurde geändert.

Both tablespace will now include 2 files : a previous non-OMF one and a new OMF one :

SQL> @qdbstbsinf.sql
Enter a tablespace name filter (US%): TEMP

TABLESPACE_NAME      FILE_NAME                                                    STATUS             SIZE_MB AUTOEXTENSIB MAXSIZE_MB
-------------------- ------------------------------------------------------------ --------------- ---------- ------------ ----------
TEMP                 /opt/oracle/oradata/myDB/datafile/o1_mf_temp_ggrjzm9o_.tmp     ONLINE               20480 NO                    0
TEMP                 /usr/oracle/oradata/myDB/temp01.dbf                            ONLINE               20480 NO                    0

SQL> @qdbstbsinf.sql
Enter a tablespace name filter (US%): myDB_BI_TEMP

TABLESPACE_NAME      FILE_NAME                                                    STATUS             SIZE_MB AUTOEXTENSIB MAXSIZE_MB
-------------------- ------------------------------------------------------------ --------------- ---------- ------------ ----------
myDB_BI_TEMP           /opt/oracle/oradata/myDB/datafile/o1_mf_lx_bi_te_ggrk0wxz_.tmp ONLINE               20480 NO                    0
myDB_BI_TEMP           /usr/oracle/oradata/myDB/lx_bi_temp01.dbf                      ONLINE               20480 YES                5120

Dropping temporary file will end into error :

SQL> alter database tempfile '/usr/oracle/oradata/myDB/temp01.dbf' drop including datafiles;
alter database tempfile '/usr/oracle/oradata/myDB/temp01.dbf' drop including datafiles
*
FEHLER in Zeile 1:
ORA-25152: TEMPFILE kann momentan nicht gelöscht werden

We need to restart the database. This will only be possible during the maintenance windows scheduled to run the switchover.

SQL> shutdown immediate;
Datenbank geschlossen.
Datenbank dismounted.
ORACLE-Instanz heruntergefahren.

SQL> startup
ORACLE-Instanz hochgefahren.

Total System Global Area 5,3687E+10 bytes
Fixed Size                 26330584 bytes
Variable Size            3,3152E+10 bytes
Database Buffers         2,0401E+10 bytes
Redo Buffers              107884544 bytes
Datenbank mounted.
Datenbank geöffnet.

The previous non-OMF temporary file can now be deleted :

SQL>  alter database tempfile '/usr/oracle/oradata/myDB/temp01.dbf' drop including datafiles;

Datenbank wurde geändert.

SQL> alter database tempfile '/usr/oracle/oradata/myDB/lx_bi_temp01.dbf' drop including datafiles;

Datenbank wurde geändert.

And we only have OMF temporary files now :

SQL>  @qdbstbsinf.sql
Enter a tablespace name filter (US%): TEMP

TABLESPACE_NAME      FILE_NAME                                                    STATUS             SIZE_MB AUTOEXTENSIB MAXSIZE_MB
-------------------- ------------------------------------------------------------ --------------- ---------- ------------ ----------
TEMP                 /opt/oracle/oradata/myDB/datafile/o1_mf_temp_ggrjzm9o_.tmp     ONLINE               20480 NO                    0

SQL>  @qdbstbsinf.sql
Enter a tablespace name filter (US%): myDB_BI_TEMP

TABLESPACE_NAME      FILE_NAME                                                    STATUS             SIZE_MB AUTOEXTENSIB MAXSIZE_MB
-------------------- ------------------------------------------------------------ --------------- ---------- ------------ ----------
myDB_BI_TEMP           /opt/oracle/oradata/myDB/datafile/o1_mf_lx_bi_te_ggrk0wxz_.tmp ONLINE               20480 NO                    0

Testing switchover

We are now ready to test the switchover from current srv02 primary to ODA03 server after making sure both databases are synchronized.

oracle@srv02:/u01/app/dbvisit/standby/ [MyDB] ./dbvctl -d MyDBSTD1 -o switchover
=============================================================
Dbvisit Standby Database Technology (8.0.26_0_g3fdeaadd) (pid 12196)
dbvctl started on srv02: Tue May 28 00:07:34 2019
=============================================================

>>> Starting Switchover between srv02 and ODA03

Running pre-checks       ... done
Pre processing           ... done
Processing primary       ... done
Processing standby       ... done
Converting standby       ... done
Converting primary       ... done
Completing               ... done
Synchronizing            ... done
Post processing          ... done

>>> Graceful switchover completed.
    Primary Database Server: ODA03
    Standby Database Server: srv02

>>> Dbvisit Standby can be run as per normal:
    dbvctl -d MyDBSTD1


PID:12196
TRACE:12196_dbvctl_switchover_MyDBSTD1_201905280007.trc

=============================================================
dbvctl ended on srv02: Tue May 28 00:13:31 2019
=============================================================
Conclusion

With dbvisit standby it is possible to mix non-OMF and OMF databases after completing several manual steps. The final recommendation would be to run a unique configuration. This is why, after having run a switchover to the new ODA03 database, and making sure the new database is stable, we created from scratch the old primary srv02 database with OMF configuration. Converting a database to OMF using move option is not possible with standard edition.

Cet article Adding a dbvisit standby database on the ODA in a non-OMF environment est apparu en premier sur Blog dbi services.

Having multiple standby databases and cascading with dbvisit

Wed, 2019-10-23 05:47

Dbvisit standy is a disaster recovery solution that you will be able to use with Oracle standard edition. I have been working on a customer project where I had to setup a system having one primary and two standby databases. One of the standby database had to run with a gap of 24 hours. Knowing that flashback possibilities are very limited on standard edition, this would give customer the ability to extract and restore some data been wrongly lost following human errors.

The initial configuration would be the following one :

Database instance, db_name : MyDB
MyDB_02 (db_unique_name) primary database running on srv02 server.
MyDB_01 (db_unique_name) expected standby database running on srv01 server.
MyDB_03 (db_unique_name) expected standby database running on srv03 server.

The following DDC configuration file will be used :
MyDBSTD1 : Configuration file for first standby been synchronized every 10 minutes.
MyDBSTD2 : Configuration file for second standby been synchronized every 24 hours.

Let me walk you through the steps to setup such configuration. This article is not intended to show the whole process of implementing a dbvisit solution, but only the steps required to work with multiple standby. We will also talk about how we can implement cascaded standby and apply lag delay within dbvisit.

Recommendations

In order to limit the manual configuration changes in the DDC file after a switchover, it is recommended to use as much as possible same ORACLE_HOME, ARCHIVE Destination and DBVISIT home directory.

Creating MyDBSTD1 DDC configuration file

The first standby configuration file will be created and used between MyDB_03 (srv03) and MyDB_02 (srv02).

oracle@srv02:/u01/app/dbvisit/standby/ [MyDB] ./dbvctl -o setup


=========================================================

     Dbvisit Standby Database Technology (8.0.26_0_g3fdeaadd)
           http://www.dbvisit.com

=========================================================

=>dbvctl only needs to be run on the primary server.

Is this the primary server?  [Yes]:
The following Dbvisit Database configuration (DDC) file(s) found on this
server:

     DDC
     ===
1)   Create New DDC
2)   Cancel

Please enter choice [] : 1

Is this correct?  [Yes]:

...
...
...

Below are the list of configuration variables provided during the setup process:

Configuration Variable             Value Provided
======================             ==============
ORACLE_SID                         MyDB
ORACLE_HOME                        /opt/oracle/product/12.2.0

SOURCE                             srv02
ARCHSOURCE                         /u03/app/oracle/dbvisit_arch/MyDB
RAC_DR                             N
USE_SSH                            N
DESTINATION                        srv03
NETPORT                            7890
DBVISIT_BASE_DR                    /u01/app/dbvisit
ORACLE_HOME_DR                     /u01/app/oracle/product/12.2.0.1/dbhome_1
DB_UNIQUE_NAME_DR                  MyDB_03
ARCHDEST                           /u03/app/oracle/dbvisit_arch/MyDB
ORACLE_SID_DR                      MyDB
ENV_FILE                           MyDBSTD1

Are these variables correct?  [Yes]:

>>> Dbvisit Database configuration (DDC) file MyDBSTD1 created.

>>> Dbvisit Database repository (DDR) MyDB created.
   Repository Version          8.4
   Software Version            8.4
   Repository Status           VALID


Do you want to enter license key for the newly created Dbvisit Database configuration (DDC) file?  [Yes]:

Enter license key and press Enter: []: XXXXXXXXXXXXXXXXXXXXXXXXXXX
>>> Dbvisit Standby License
License Key     : XXXXXXXXXXXXXXXXXXXXXXXXXXX
customer_number : XXXXXX
dbname          : MyDB
expiry_date     : 2099-05-06
product_id      : 8
sequence        : 1
status          : VALID
updated         : YES

PID:423545
TRACE:dbvisit_install.log
Synchronizing both MyDB_02 and MyDB_03 Shippping logs from primary to standby
oracle@srv02:/u01/app/dbvisit/standby/ [rdbms12201] ./dbvctl -d MyDBSTD1
=============================================================
Dbvisit Standby Database Technology (8.0.26_0_g3fdeaadd) (pid 326409)
dbvctl started on srv02: Mon May 20 16:29:14 2019
=============================================================

>>> Obtaining information from standby database (RUN_INSPECT=Y)... done
    Thread: 1 Archive log gap: 30. Transfer log gap: 58080
>>> Sending heartbeat message... skipped
>>> First time Dbvisit Standby runs, Dbvisit Standby configuration will be copied to
    srv03...
>>> Transferring Log file(s) from MyDB on srv02 to srv03 for thread 1:

    thread 1 sequence 58051 (1_58051_987102791.dbf)
    thread 1 sequence 58052 (1_58052_987102791.dbf)
...
...
...
    thread 1 sequence 58079 (1_58079_987102791.dbf)
    thread 1 sequence 58080 (1_58080_987102791.dbf)

=============================================================
dbvctl ended on srv02: Mon May 20 16:30:50 2019
=============================================================
Applying log on standby database
oracle@srv03:/u01/app/dbvisit/standby/ [MyDB] ./dbvctl -d MyDBSTD1
=============================================================
Dbvisit Standby Database Technology (8.0.26_0_g3fdeaadd) (pid 21504)
dbvctl started on srv03: Mon May 20 16:33:42 2019
=============================================================

>>> Sending heartbeat message... skipped

>>> Applying Log file(s) from srv02 to MyDB on srv03:

    thread 1 sequence 58051 (1_58051_987102791.arc)
    thread 1 sequence 58052 (1_58052_987102791.arc)
...
...
...
    thread 1 sequence 58079 (1_58079_987102791.arc)
    thread 1 sequence 58080 (1_58080_987102791.arc)
    Last applied log(s):
    thread 1 sequence 58080

    Next SCN required for recovery 49719323442 generated at 2019-05-20:16:27:09 +02:00.
    Next required log thread 1 sequence 58081

=============================================================
dbvctl ended on srv03: Mon May 20 16:36:52 2019
=============================================================
Running a gap report
oracle@srv02:/u01/app/dbvisit/standby/ [MyDB] ./dbvctl -d MyDBSTD1 -i
=============================================================
Dbvisit Standby Database Technology (8.0.26_0_g3fdeaadd) (pid 335068)
dbvctl started on srv02: Mon May 20 16:37:53 2019
=============================================================


Dbvisit Standby log gap report for MyDB thread 1 at 201905201637:
-------------------------------------------------------------
Destination database on srv03 is at sequence: 58081.
Source database on srv02 is at log sequence: 58082.
Source database on srv02 is at archived log sequence: 58081.
Dbvisit Standby last transfer log sequence: 58081.
Dbvisit Standby last transfer at: 2019-05-20 16:37:36.

Archive log gap for thread 1:  0.
Transfer log gap for thread 1: 0.
Standby database time lag (DAYS-HH:MI:SS): +00:00:01.


=============================================================
dbvctl ended on srv02: Mon May 20 16:37:57 2019
=============================================================
Switchover to srv03

At that time in the project we did a switchover to the newly created srv03 in order to test its stability. The switchover has been performed as described below, but this step is not mandatory when implementing several standby databases. As best practices, we will always test the first configuration by running a switchover before moving forward.

oracle@srv02:/u01/app/dbvisit/standby/ [MyDB] ./dbvctl -d MyDBSTD1 -o switchover
=============================================================
Dbvisit Standby Database Technology (8.0.26_0_g3fdeaadd) (pid 12196)
dbvctl started on srv02: Tue May 28 00:07:34 2019
=============================================================

>>> Starting Switchover between srv02 and srv03

Running pre-checks       ... done
Pre processing           ... done
Processing primary       ... done
Processing standby       ... done
Converting standby       ... done
Converting primary       ... done
Completing               ... done
Synchronizing            ... done
Post processing          ... done

>>> Graceful switchover completed.
    Primary Database Server: srv03
    Standby Database Server: srv02

>>> Dbvisit Standby can be run as per normal:
    dbvctl -d MyDBSTD1


PID:12196
TRACE:12196_dbvctl_switchover_MyDBSTD1_201905280007.trc

=============================================================
dbvctl ended on srv02: Tue May 28 00:13:31 2019
=============================================================

srv03 is now the new primary and srv02 a new standby database.

Creating MyDBSTD2 DDC configuration file

Once myDB_01 standby database is up and running, we can create its related DDC configuration file. To do so, we simply copy previous DDC configuration file, MyDBSTD1, and update it as needed.

I first transferred the file from current primary srv03 to new standby server srv01 :

oracle@srv03:/u01/app/dbvisit/standby/conf/ [MyDB] scp dbv_MyDBSTD1.env oracle@srv01:$PWD
dbv_MyDBSTD1.env		100% 	23KB 	22.7KB/s 		00:00

I copied it into the new DDC configuration file name :

oracle@srv01:/u01/app/dbvisit/standby/conf/ [MyDB] cp dbv_MyDBSTD1.env dbv_MyDBSTD2.env

I updated new DDC configuration accordingly to have :

  • DESTINATION as srv01 instead of srv02
  • DB_UNIQUE_NAME_DR as MyDB_01 instead of MyDB_02
  • MAILCFG to see the alerts coming from STD2 configuration.

The primary will remain the same : srv03.

oracle@srv01:/u01/app/dbvisit/standby/conf/ [MyDB] vi dbv_MyDBSTD2.env

oracle@srv01:/u01/app/dbvisit/standby/conf/ [MyDB] diff dbv_MyDBSTD1.env dbv_MyDBSTD2.env
86c86
DESTINATION = srv02
---
DESTINATION = srv01
93c93
DB_UNIQUE_NAME_DR = MyDB
---
DB_UNIQUE_NAME_DR = MyDB_01
135,136c135,136
MAILCFG_FROM = dbvisit_conf_1@domain.name MAILCFG_FROM_DR = dbvisit_conf_1@domain.name
---
MAILCFG_FROM = dbvisit_conf_2@domain.name
MAILCFG_FROM_DR = dbvisit_conf_2@domain.name

In case the ORACLE_HOME and ARCHIVE destination are not the same, these parameters will have to be updated as well.

Synchronizing both MyDB_03 and MyDB_01 Shippping logs from primary to standby
oracle@srv03:/u01/app/dbvisit/standby/ [MyDB] ./dbvctl -d MyDBSTD2
=============================================================
Dbvisit Standby Database Technology (8.0.26_0_g3fdeaadd) (pid 25914)
dbvctl started on srv03: Wed Jun  5 20:32:09 2019
=============================================================

>>> Obtaining information from standby database (RUN_INSPECT=Y)... done
    Thread: 1 Archive log gap: 383. Transfer log gap: 67385
>>> Sending heartbeat message... done
>>> First time Dbvisit Standby runs, Dbvisit Standby configuration will be copied to
    srv01...
>>> Transferring Log file(s) from MyDB on srv03 to srv01 for thread 1:

    thread 1 sequence 67003 (o1_mf_1_67003_ghgwj0z2_.arc)
    thread 1 sequence 67004 (o1_mf_1_67004_ghgwmj1w_.arc)
...
...
...
    thread 1 sequence 67384 (o1_mf_1_67384_ghj2fbgj_.arc)
    thread 1 sequence 67385 (o1_mf_1_67385_ghj2g883_.arc)

=============================================================
dbvctl ended on srv03: Wed Jun  5 20:42:05 2019
=============================================================
Applying log on standby database
oracle@srv01:/u01/app/dbvisit/standby/ [MyDB] ./dbvctl -d MyDBSTD2
=============================================================
Dbvisit Standby Database Technology (8.0.26_0_g3fdeaadd) (pid 69764)
dbvctl started on srv01: Wed Jun  5 20:42:45 2019
=============================================================

>>> Sending heartbeat message... done

>>> Applying Log file(s) from srv03 to MyDB on srv01:

    thread 1 sequence 67003 (1_67003_987102791.arc)
    thread 1 sequence 67004 (1_67004_987102791.arc)
...
...
...
    thread 1 sequence 67384 (1_67384_987102791.arc)
    thread 1 sequence 67385 (1_67385_987102791.arc)
    Last applied log(s):
    thread 1 sequence 67385

    Next SCN required for recovery 50112484332 generated at 2019-06-05:20:28:24 +02:00.
    Next required log thread 1 sequence 67386

>>> Dbvisit Archive Management Module (AMM)

    Config: number of archives to keep      = 0
    Config: number of days to keep archives = 3
    Config: diskspace full threshold        = 80%
==========

Processing /u03/app/oracle/dbvisit_arch/MyDB...
    Archive log dir: /u03/app/oracle/dbvisit_arch/MyDB
    Total number of archive files   : 383
    Number of archive logs deleted = 0
    Current Disk percent full       : 8%

=============================================================
dbvctl ended on srv01: Wed Jun  5 21:16:30 2019
=============================================================
Running a gap report
oracle@srv03:/u01/app/dbvisit/standby/ [MyDB] ./dbvctl -d MyDBSTD2 -i
=============================================================
Dbvisit Standby Database Technology (8.0.26_0_g3fdeaadd) (pid 44143)
dbvctl started on srv03: Wed Jun  5 21:17:03 2019
=============================================================


Dbvisit Standby log gap report for MyDB_03 thread 1 at 201906052117:
-------------------------------------------------------------
Destination database on srv01 is at sequence: 67385.
Source database on srv03 is at log sequence: 67387.
Source database on srv03 is at archived log sequence: 67386.
Dbvisit Standby last transfer log sequence: 67385.
Dbvisit Standby last transfer at: 2019-06-05 20:42:05.

Archive log gap for thread 1:  1.
Transfer log gap for thread 1: 1.
Standby database time lag (DAYS-HH:MI:SS): +00:48:41.
Switchover to srv01

Now we are having both srv01 and srv02 standby databases up and running and connected with current srv03 primary database. Let’s switchover to srv01 and see what would be the required steps. After each switchover the other standby DDC configuration files will have to be manually updated.

Checking srv03 and srv02 are synchronized

Both srv03 and srv02 databases should be in sync otherwise ship and apply archive logs.

oracle@srv03:/u01/app/dbvisit/standby/ [MyDB] ./dbvctl -d MyDBSTD1 -i
=============================================================
Dbvisit Standby Database Technology (8.0.26_0_g3fdeaadd) (pid 93307)
dbvctl started on srv03: Wed Jun  5 21:27:02 2019
=============================================================


Dbvisit Standby log gap report for MyDB_03 thread 1 at 201906052127:
-------------------------------------------------------------
Destination database on srv02 is at sequence: 67386.
Source database on srv03 is at log sequence: 67387.
Source database on srv03 is at archived log sequence: 67386.
Dbvisit Standby last transfer log sequence: 67386.
Dbvisit Standby last transfer at: 2019-06-05 21:24:47.

Archive log gap for thread 1:  0.
Transfer log gap for thread 1: 0.
Standby database time lag (DAYS-HH:MI:SS): +00:27:02.


=============================================================
dbvctl ended on srvxdb03: Wed Jun  5 21:27:08 2019
=============================================================
Checking srv03 and srv01 are synchronized

Both srv03 and srv01 databases should be in sync otherwise ship and apply archive logs.

oracle@srv03:/u01/app/dbvisit/standby/ [MyDB] ./dbvctl -d MyDBSTD2 -i
=============================================================
Dbvisit Standby Database Technology (8.0.26_0_g3fdeaadd) (pid 90871)
dbvctl started on srv03: Wed Jun  5 21:26:31 2019
=============================================================


Dbvisit Standby log gap report for MyDB_03 thread 1 at 201906052126:
-------------------------------------------------------------
Destination database on srv01 is at sequence: 67386.
Source database on srv03 is at log sequence: 67387.
Source database on srv03 is at archived log sequence: 67386.
Dbvisit Standby last transfer log sequence: 67386.
Dbvisit Standby last transfer at: 2019-06-05 21:26:02.

Archive log gap for thread 1:  0.
Transfer log gap for thread 1: 0.
Standby database time lag (DAYS-HH:MI:SS): +00:26:02.
Switchover to srv01
oracle@srv03:/u01/app/dbvisit/standby/ [MyDB] ./dbvctl -d MyDBSTD2 -o switchover
=============================================================
Dbvisit Standby Database Technology (8.0.26_0_g3fdeaadd) (pid 20334)
dbvctl started on srv03: Wed Jun  5 21:31:56 2019
=============================================================

>>> Starting Switchover between srv03 and srv01

Running pre-checks       ... done
Pre processing           ... done
Processing primary       ... done
Processing standby       ... done
Converting standby       ... done
Converting primary       ... done
Completing               ... done
Synchronizing            ... done
Post processing          ... done

>>> Graceful switchover completed.
    Primary Database Server: srv01
    Standby Database Server: srv03

>>> Dbvisit Standby can be run as per normal:
    dbvctl -d MyDBSTD2


PID:20334
TRACE:20334_dbvctl_switchover_MyDBSTD2_201906052131.trc

=============================================================
dbvctl ended on srv03: Wed Jun  5 21:37:40 2019
=============================================================
Attach srv02 to srv01 (new primary)

Previously to the switchover :

  • srv03 and srv01 was using MyDBSTD2 DDC configuration file
  • srv03 and srv02 was using MyDBSTD1 DDC configuration file

srv02 standby database needs now to be attach to new primary srv01. For this we will copy the MyDBSTD1 DDC configuration file from srv02 to srv01 as it is the first time srv01 is primary. Otherwise, we would only need to update accordingly the already existing file.

I have been transferring the DDC file :

oracle@srv02:/u01/app/dbvisit/standby/conf/ [MyDB] scp dbv_MyDBSTD1.env oracle@srv01:$PWD
dbv_MyDBSTD1.env    100%   23KB  14.8MB/s   00:00

MyDBSTD1 configuration file has been updated accordingly to reflect new changes and configuration :

  • SOURCE needs to be replaced from srv03 to srv01
  • DESTINATION will remain srv02
  • DB_UNIQUE_NAME needs to be replaced fromMyDB_03 to MyDB_01
  • DB_UNIQUE_NAME_DR will remain MyDB_02
oracle@srv01:/u01/app/dbvisit/standby/conf/ [MyDB] vi dbv_MyDBSTD1.env

oracle@srv01:/u01/app/dbvisit/standby/conf/ [MyDB] grep ^SOURCE dbv_MyDBSTD1.env
SOURCE = srv01

oracle@srv01:/u01/app/dbvisit/standby/conf/ [MyDB] grep DB_UNIQUE_NAME dbv_MyDBSTD1.env
# DB_UNIQUE_NAME      - Primary database db_unique_name
DB_UNIQUE_NAME = MyDB_01
# DB_UNIQUE_NAME_DR   - Standby database db_unique_name
DB_UNIQUE_NAME_DR = MyDB_02
Checking that databases are all synchronized

After performing several switch logfile on the primary in order to generate archive logs, I transferred and applied needed archive log files on both srv02 and srv03 standby databases. I made sure both are synchronized.

srv01 and srv03 databases :

oracle@srv01:/u01/app/dbvisit/standby/ [MyDB] ./dbvctl -d MyDBSTD2 -i
=============================================================
Dbvisit Standby Database Technology (8.0.26_0_g3fdeaadd) (pid 98156)
dbvctl started on srv01: Wed Jun  5 21:52:08 2019
=============================================================


Dbvisit Standby log gap report for MyDB_01 thread 1 at 201906052152:
-------------------------------------------------------------
Destination database on srv03 is at sequence: 67413.
Source database on srv01 is at log sequence: 67414.
Source database on srv01 is at archived log sequence: 67413.
Dbvisit Standby last transfer log sequence: 67413.
Dbvisit Standby last transfer at: 2019-06-05 21:51:13.

Archive log gap for thread 1:  0.
Transfer log gap for thread 1: 0.
Standby database time lag (DAYS-HH:MI:SS): +00:00:00.


=============================================================
dbvctl ended on srv01: Wed Jun  5 21:52:18 2019
=============================================================

srv01 and srv02 databases :

oracle@srv01:/u01/app/dbvisit/standby/ [MyDB] ./dbvctl -d MyDBSTD1 -i
=============================================================
Dbvisit Standby Database Technology (8.0.26_0_g3fdeaadd) (pid 100393)
dbvctl started on srv01: Wed Jun  5 21:56:06 2019
=============================================================


Dbvisit Standby log gap report for MyDB_01 thread 1 at 201906052156:
-------------------------------------------------------------
Destination database on srv02 is at sequence: 67413.
Source database on srv01 is at log sequence: 67414.
Source database on srv01 is at archived log sequence: 67413.
Dbvisit Standby last transfer log sequence: 67413.
Dbvisit Standby last transfer at: 2019-06-05 21:55:22.

Archive log gap for thread 1:  0.
Transfer log gap for thread 1: 0.
Standby database time lag (DAYS-HH:MI:SS): +00:05:13.


=============================================================
dbvctl ended on srv01: Wed Jun  5 21:56:07 2019
=============================================================
Apply delay lag

MyDBSTD2 configuration should at the end have an apply lag of 24 hours. This can be achieved using APPLY_DELAY_LAG_MINUTES in the configuration. In order to test it, I have decided with customer to use 60 minutes delay.

Update MyDBSTD2 DDC configuration file

Following parameters have been updated in the configuration :
APPLY_DELAY_LAG_MINUTES = 60
DMN_MONITOR_INTERVAL_DR = 0
TRANSFER_LOG_GAP_THRESHOLD = 0
ARCHIVE_LOG_GAP_THRESHOLD = 60

APPLY_DELAY_LAG_MINUTES is the delay in minutes to take in account before applying the vector changes.
DMN_MONITOR_INTERVAL_DR is the interval in sec for log monitor schedule on destination. 0 mean deactivated.
TRANSFER_LOG_GAP_THRESHOLD is the difference allowed between the last archived sequence on the primary and the last sequence transferred to the standby server.
ARCHIVE_LOG_GAP_THRESHOLD is the difference allowed between the last archived sequence on the primary and the last applied sequence on the standby database before an alert is sent.

oracle@srv03:/u01/app/dbvisit/standby/conf/ [MyDB] cp dbv_MyDBSTD2.env dbv_MyDBSTD2.env.201906131343

oracle@srv03:/u01/app/dbvisit/standby/conf/ [MyDB] vi dbv_MyDBSTD2.env

oracle@srv03:/u01/app/dbvisit/standby/conf/ [MyDB] diff dbv_MyDBSTD2.env dbv_MyDBSTD2.env.201906131343
281c281
DMN_MONITOR_INTERVAL_DR = 0
---
DMN_MONITOR_INTERVAL_DR = 5
331c331
APPLY_DELAY_LAG_MINUTES = 60
---
APPLY_DELAY_LAG_MINUTES = 0
374c374
ARCHIVE_LOG_GAP_THRESHOLD = 60
---
ARCHIVE_LOG_GAP_THRESHOLD = 0

oracle@srv03:/u01/app/dbvisit/standby/conf/ [MyDB] grep ^TRANSFER_LOG_GAP_THRESHOLD dbv_MyDBSTD2.env
TRANSFER_LOG_GAP_THRESHOLD = 0
Report displayed with an apply delay lag been configured

When generating a report, we can see that there is no gap in the log transfer as the archive log would be transferred through the crontab every 10 minutes. On the other side, we can see that there is an expected delay of 60 minutes in applying the logs.

oracle@srv03:/u01/app/dbvisit/standby/ [MyDBTEST] ./dbvctl -d MyDBSTD2 -i
=============================================================
Dbvisit Standby Database Technology (8.0.26_0_g3fdeaadd) (pid 66003)
dbvctl started on srv03: Thu Jun 13 15:21:29 2019
=============================================================


Dbvisit Standby log gap report for MyDB_03 thread 1 at 201906131521:
-------------------------------------------------------------
Destination database on srv01 is at sequence: 73856.
Source database on srv03 is at log sequence: 73890.
Source database on srv03 is at archived log sequence: 73889.
Dbvisit Standby last transfer log sequence: 73889.
Dbvisit Standby last transfer at: 2019-06-13 15:20:15.

Archive log gap for thread 1:  33 (apply_delay_lag_minutes=60).
Transfer log gap for thread 1: 0.
Standby database time lag (DAYS-HH:MI:SS): +01:00:00.


=============================================================
dbvctl ended on srv03: Thu Jun 13 15:21:35 2019
=============================================================
Cascading standby database

What about cascading standby database? Cascading standby database is possible with dbvisit. We would be using a cascaded standby for a reporting server that needs to be updated less frequently or if we would like to unload the primary database in sending archive logs to multiple standby databases. The cascaded standby database will remain updated through the first standby. Cascading is possible since dbvisit version 8.

Following needs to be known :

  • Switchover will not be possible between the primary and the cascaded standby database.
  • The DDC configuration file between the first standby and the cascaded standby needs to have :
    • As SOURCE the first standby database
    • CASCADE parameter set to Y. This will be done automatically when creating the DDC configuration with dbvctl -o setup. From the traces you will see : >>> Source database is a standby database. CASCADE flag will be turned on.
    • ARCHDEST and ARCHSOURCE location on the first standby needs to have same values.

    The principle is then exactly the same, and running dbvctl -d from the first standby will ship the archive log to the second standby.

I had been running some tests in my lab.

Environment

DBVP is the primary server.
DBVS is the first standby server.
DBVS2 is the second cascaded server.

oracle@DBVP:/u01/app/dbvisit/standby/ [DBVPDB] DBVPDB
********* dbi services Ltd. *********
STATUS                 : OPEN
DB_UNIQUE_NAME         : DBVPDB_SITE1
OPEN_MODE              : READ WRITE
LOG_MODE               : ARCHIVELOG
DATABASE_ROLE          : PRIMARY
FLASHBACK_ON           : NO
FORCE_LOGGING          : YES
VERSION                : 12.2.0.1.0
CDB Enabled            : NO
*************************************

oracle@DBVS:/u01/app/dbvisit/standby/ [DBVPDB] DBVPDB
********* dbi services Ltd. *********
STATUS                 : MOUNTED
DB_UNIQUE_NAME         : DBVPDB_SITE2
OPEN_MODE              : MOUNTED
LOG_MODE               : ARCHIVELOG
DATABASE_ROLE          : PHYSICAL STANDBY
FLASHBACK_ON           : NO
FORCE_LOGGING          : YES
CDB Enabled            : NO
*************************************


oracle@DBVS2:/u01/app/dbvisit/standby/ [DBVPDB] DBVPDB
********* dbi services Ltd. *********
STATUS                 : MOUNTED
DB_UNIQUE_NAME         : DBVPDB_SITE3
OPEN_MODE              : MOUNTED
LOG_MODE               : ARCHIVELOG
DATABASE_ROLE          : PHYSICAL STANDBY
FLASHBACK_ON           : NO
FORCE_LOGGING          : YES
CDB Enabled            : NO
*************************************
Create cascaded DDC configuration file

The DDC configuration file will be created from the first standby node.
DBVS (first standby server) will be the SOURCE.
DBVS2 (cascaded standby server) will be the DESTINATION.

oracle@DBVS:/u01/app/dbvisit/standby/ [DBVPDB] ./dbvctl -o setup


=========================================================

     Dbvisit Standby Database Technology (8.0.20_0_g7e6bd51b)
           http://www.dbvisit.com

=========================================================

=>dbvctl only needs to be run on the primary server.

Is this the primary server?  [Yes]:
The following Dbvisit Database configuration (DDC) file(s) found on this
server:

     DDC
     ===
1)   Create New DDC
2)   DBVPDB
3)   DBVPDB_SITE1
4)   DBVPOMF_SITE1
5)   Cancel

Please enter choice [] : 1

Is this correct?  [Yes]:

...


Continue ?  [No]: yes

=========================================================
Dbvisit Standby setup begins.
=========================================================
The following Oracle instance(s) have been found on this server:

     SID            ORACLE_HOME
     ===            ===========
1)   rdbms12201     /u01/app/oracle/product/12.2.0/dbhome_1
2)   DBVPDB         /u01/app/oracle/product/12.2.0/dbhome_1
3)   DBVPOMF        /u01/app/oracle/product/12.2.0/dbhome_1
4)   DUP            /u01/app/oracle/product/12.2.0/dbhome_1
5)   Enter own ORACLE_SID and ORACLE_HOME
Please enter choice [] : 2

Is this correct?  [Yes]:
=>ORACLE_SID will be: DBVPDB
=>ORACLE_HOME will be: /u01/app/oracle/product/12.2.0/dbhome_1

>>> Source database is a standby database. CASCADE flag will be turned on.

Yes to continue or No to cancel setup?  [Yes]:

...
...
...

Below are the list of configuration variables provided during the setup process:

Configuration Variable             Value Provided
======================             ==============
ORACLE_SID                         DBVPDB
ORACLE_HOME                        /u01/app/oracle/product/12.2.0/dbhome_1

SOURCE                             DBVS
ARCHSOURCE                         /u90/dbvisit_arch/DBVPDB_SITE2
RAC_DR                             N
USE_SSH                            Y
DESTINATION                        DBVS2
NETPORT                            22
DBVISIT_BASE_DR                    /oracle/u01/app/dbvisit
ORACLE_HOME_DR                     /u01/app/oracle/product/12.2.0/dbhome_1
DB_UNIQUE_NAME_DR                  DBVPDB_SITE3
ARCHDEST                           /u90/dbvisit_arch/DBVPDB_SITE3
ORACLE_SID_DR                      DBVPDB
ENV_FILE                           DBVPDB_CASCADED

Are these variables correct?  [Yes]:

>>> Dbvisit Database configuration (DDC) file DBVPDB_CASCADED created.

>>> Dbvisit Database repository (DDR) already installed.
   Repository Version          8.3
   Software Version            8.3
   Repository Status           VALID


Do you want to enter license key for the newly created Dbvisit Database configuration (DDC) file?  [Yes]:

Enter license key and press Enter: []: 4jo6z-8aaai-u09b6-ijjxe-cxks5-1114a-ozfvp
oracle@dbvs2's password:
>>> Dbvisit Standby License
License Key     : 4jo6z-8aaai-u09b6-ijjxe-cxks5-1114a-ozfvp
customer_number : 1
dbname          :
expiry_date     : 2019-05-29
product_id      : 8
sequence        : 1
status          : VALID
updated         : YES

PID:25571
TRACE:dbvisit_install.log

dbvisit software could see that the SOURCE is already a standby database. The software will then automatically configured the CASCADE flag to Y.

>>> Source database is a standby database. CASCADE flag will be turned on.
oracle@DBVS:/u01/app/dbvisit/standby/conf/ [DBVPDB] grep CASCADE dbv_DBVPDB_CASCADED.env
# Variable: CASCADE
#      CASCADE = Y
CASCADE = Y
Synchronize first standby with primary Ship archive log from primary to first standby
oracle@DBVP:/u01/app/dbvisit/standby/ [DBVPDB] ./dbvctl -d DBVPDB
=============================================================
Dbvisit Standby Database Technology (8.0.20_0_g7e6bd51b) (pid 23506)
dbvctl started on DBVP: Wed May 15 01:24:55 2019
=============================================================

>>> Obtaining information from standby database (RUN_INSPECT=Y)... done
    Thread: 1 Archive log gap: 3. Transfer log gap: 3
>>> Transferring Log file(s) from DBVPDB on DBVP to DBVS for thread 1:

    thread 1 sequence 50 (o1_mf_1_50_gfpmk7sg_.arc)
    thread 1 sequence 51 (o1_mf_1_51_gfpmkc7p_.arc)
    thread 1 sequence 52 (o1_mf_1_52_gfpmkf7w_.arc)

=============================================================
dbvctl ended on DBVP: Wed May 15 01:25:06 2019
=============================================================
Apply archive log on first standby
oracle@DBVS:/u01/app/dbvisit/standby/ [DBVPDB] ./dbvctl -d DBVPDB
=============================================================
Dbvisit Standby Database Technology (8.0.20_0_g7e6bd51b) (pid 27769)
dbvctl started on DBVS: Wed May 15 01:25:25 2019
=============================================================


>>> Applying Log file(s) from DBVP to DBVPDB on DBVS:

>>> No new logs to apply.
    Last applied log(s):
    thread 1 sequence 52

    Next SCN required for recovery 885547 generated at 2019-05-15:01:24:29 +02:00.
    Next required log thread 1 sequence 53

=============================================================
dbvctl ended on DBVS: Wed May 15 01:25:27 2019
=============================================================
Run a gap report
oracle@DBVP:/u01/app/dbvisit/standby/ [DBVPDB] ./dbvctl -d DBVPDB -i
=============================================================
Dbvisit Standby Database Technology (8.0.20_0_g7e6bd51b) (pid 23625)
dbvctl started on DBVP: Wed May 15 01:25:55 2019
=============================================================


Dbvisit Standby log gap report for DBVPDB_SITE1 thread 1 at 201905150125:
-------------------------------------------------------------
Destination database on DBVS is at sequence: 52.
Source database on DBVP is at log sequence: 53.
Source database on DBVP is at archived log sequence: 52.
Dbvisit Standby last transfer log sequence: 52.
Dbvisit Standby last transfer at: 2019-05-15 01:25:06.

Archive log gap for thread 1:  0.
Transfer log gap for thread 1: 0.
Standby database time lag (DAYS-HH:MI:SS): +00:00:33.


=============================================================
dbvctl ended on DBVP: Wed May 15 01:25:58 2019
=============================================================
Synchronize cascaded standby with first standby Ship archive log from first standby to cascaded standby
oracle@DBVS:/u01/app/dbvisit/standby/ [DBVPDB] ./dbvctl -d DBVPDB_CASCADED
=============================================================
Dbvisit Standby Database Technology (8.0.20_0_g7e6bd51b) (pid 27965)
dbvctl started on DBVS: Wed May 15 01:26:41 2019
=============================================================

>>> Obtaining information from standby database (RUN_INSPECT=Y)... done
    Thread: 1 Archive log gap: 3. Transfer log gap: 3
>>> Transferring Log file(s) from DBVPDB on DBVS to DBVS2 for thread 1:

    thread 1 sequence 50 (1_50_979494498.arc)
    thread 1 sequence 51 (1_51_979494498.arc)
    thread 1 sequence 52 (1_52_979494498.arc)

=============================================================
dbvctl ended on DBVS: Wed May 15 01:26:49 2019
=============================================================
Apply archive log on cascaded standby
oracle@DBVS2:/u01/app/dbvisit/standby/ [DBVPDB] ./dbvctl -d DBVPDB_CASCADED
=============================================================
Dbvisit Standby Database Technology (8.0.20_0_g7e6bd51b) (pid 21118)
dbvctl started on DBVS2: Wed May 15 01:27:21 2019
=============================================================


>>> Applying Log file(s) from DBVS to DBVPDB on DBVS2:

    thread 1 sequence 50 (1_50_979494498.arc)
    thread 1 sequence 51 (1_51_979494498.arc)
    thread 1 sequence 52 (1_52_979494498.arc)
    Last applied log(s):
    thread 1 sequence 52

    Next SCN required for recovery 885547 generated at 2019-05-15:01:24:29 +02:00.
    Next required log thread 1 sequence 53

=============================================================
dbvctl ended on DBVS2: Wed May 15 01:27:33 2019
=============================================================
Run a gap report
oracle@DBVS:/u01/app/dbvisit/standby/ [DBVPDB] ./dbvctl -d DBVPDB_CASCADED -i
=============================================================
Dbvisit Standby Database Technology (8.0.20_0_g7e6bd51b) (pid 28084)
dbvctl started on DBVS: Wed May 15 01:28:07 2019
=============================================================


Dbvisit Standby log gap report for DBVPDB_SITE2 thread 1 at 201905150128:
-------------------------------------------------------------
Destination database on DBVS2 is at sequence: 52.
Source database on DBVS is at applied log sequence: 52.
Dbvisit Standby last transfer log sequence: 52.
Dbvisit Standby last transfer at: 2019-05-15 01:26:49.

Archive log gap for thread 1:  0.
Transfer log gap for thread 1: 0.
Standby database time lag (DAYS-HH:MI:SS): +00:00:00.


=============================================================
dbvctl ended on DBVS: Wed May 15 01:28:11 2019
=============================================================
Conclusion

With dbvisit we are able to configure several standby databases, choose apply lag delay and also configure cascaded standby. The cons would be that the DDC configuration file needs to be manually adapted after each switchover.

Cet article Having multiple standby databases and cascading with dbvisit est apparu en premier sur Blog dbi services.

Why you really should use peer authentication in PostgreSQL

Tue, 2019-10-22 14:36

It is always a bit of a surprise that many people do not know peer authentication in PostgreSQL. You might ask why that is important as initdb creates a default pg_hba.conf which does not allow any connections from outside the PostgreSQL server. While that is true there is another important point to consider.

Let’s assume you executed initdb without any options like this:

postgres@centos8pg:/home/postgres/ [pgdev] mkdir /var/tmp/test
postgres@centos8pg:/home/postgres/ [pgdev] initdb -D /var/tmp/test
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.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 disabled.

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

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

Success. You can now start the database server using:

pg_ctl -D /var/tmp/test -l logfile start

Did you ever notice the warning at the end of the output?

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

You might think that this is not important as only the DBAs will have access to the operating system user postgres (or whatever user you used when you executed initdb). Although this might be true in your case, the server eventually might have other local users. Before creating a new user lets start the instance:

postgres@centos8pg:/home/postgres/ [pgdev] export PGPORT=9999
postgres@centos8pg:/home/postgres/ [pgdev] pg_ctl -D /var/tmp/test/ start -l /dev/null
waiting for server to start.... done
server started

You really need to be aware of is this:

postgres@centos8pg:/home/postgres/ [pgdev] sudo useradd test
postgres@centos8pg:/home/postgres/ [pgdev] sudo su - test
[test@centos8pg ~]$ /u01/app/postgres/product/DEV/db_1/bin/psql -p 9999 -U postgres postgres
psql (13devel)
Type "help" for help.

postgres=#

… and you are in as the superuser! So any local user can connect as the superuser by default. What you might want to do is this:

postgres@centos8pg:/home/postgres/ [pgdev] sudo chmod o-rwx /u01/app/postgres/product
postgres@centos8pg:/home/postgres/ [pgdev] sudo su - test
Last login: Tue Oct 22 21:19:58 CEST 2019 on pts/0
[test@centos8pg ~]$ /u01/app/postgres/product/DEV/db_1/bin/psql -p 9999 -U postgres postgres
-bash: /u01/app/postgres/product/DEV/db_1/bin/psql: Permission denied

This prevents all other users on the system from executing the psql binary. If you can guarantee that nobody installs psql in another way on the system that might be sufficient. As soon as psql is available somewhere on the system you’re lost again:

postgres@centos8pg:/home/postgres/ [pgdev] sudo dnf provides psql
Last metadata expiration check: 0:14:53 ago on Tue 22 Oct 2019 09:09:23 PM CEST.
postgresql-10.6-1.module_el8.0.0+15+f57f353b.x86_64 : PostgreSQL client programs
Repo        : AppStream
Matched from:
Filename    : /usr/bin/psql

postgres@centos8pg:/home/postgres/ [pgdev] sudo dnf install -y postgresql-10.6-1.module_el8.0.0+15+f57f353b.x86_64
[test@centos8pg ~]$ /usr/bin/psql -p 9999 -U postgres -h /tmp postgres
psql (10.6, server 13devel)
WARNING: psql major version 10, server major version 13.
Some psql features might not work.
Type "help" for help.

postgres=#

Not really an option. This is where peer authentication becomes very handy.

postgres@centos8pg:/home/postgres/ [pgdev] sed -i 's/local   all             all                                     trust/local   all             all                                     peer/g' /var/tmp/test/pg_hba.conf

Once you switched from trust to peer for local connections only the operating system user that created the instance will be able to connect locally without providing a password:

postgres@centos8pg:/home/postgres/ [pgdev] pg_ctl -D /var/tmp/test/ reload
server signaled
postgres@centos8pg:/home/postgres/ [pgdev] psql postgres
psql (13devel)
Type "help" for help.

[local]:9999 postgres@postgres=#

Other local users will not be able to connect anymore:

postgres@centos8pg:/home/postgres/ [pgdev] sudo su - testLast login: Tue Oct 22 21:25:36 CEST 2019 on pts/0
[test@centos8pg ~]$ /usr/bin/psql -p 9999 -U postgres -h /tmp postgres
psql: FATAL:  Peer authentication failed for user "postgres"
[test@centos8pg ~]$

So, please, consider enabling peer authentication or at least go for md5 for local connections as well.

Cet article Why you really should use peer authentication in PostgreSQL est apparu en premier sur Blog dbi services.

Solr Sharding – Concepts & Methods

Sun, 2019-10-20 03:06

A few weeks ago, I published a series of blog on the Alfresco Clustering, including Solr Sharding. At that time, I planned to first explain what is really the Solr Sharding, what are the different concepts and methods around it. Unfortunately, I didn’t get the time to write this blog so I had to post the one related to Solr even before explaining the basics. Today, I’m here to rights my wrong! Obviously, this blog has a focus on Alfresco related Solr Sharding since that’s what I do.

I. Solr Sharding – Concepts

The Sharding in general is the partitioning of a set of data in a specific way. There are several possibilities to do that, depending on the technology you are working on. In the scope of Solr, the Sharding is therefore the split of the Solr index into several smaller indices. You might be interested in the Solr Sharding because it improves the following points:

  • Fault Tolerance: with a single index, if you lose it, then… you lost it. If the index is split into several indices, then even if you are losing one part, you will still have all others that will continue working
  • High Availability: it provides more granularity than the single index. You might want for example to have a few small indices without HA and then have some others with HA because you configured them to contain some really important nodes of your repository
  • Automatic Failover: Alfresco knows automatically (with Dynamic Registration) which Shards are up-to-date and which ones are lagging behind so it will choose automatically the best Shards to handle the search queries so that you get the best results possible. In combination with the Fault Tolerance above, this gives the best possible HA solution with the less possible resources
  • Performance improvements: better performance in indexing since you will have several Shards indexing the same repository so you can have less work done by each Shards for example (depends on Sharding Method). Better performance in searches since the search query will be processes by all Shards in parallel on smaller parts of the index instead of being one single query on the full index

Based on benchmarks, Alfresco considers that a Solr Shard can contain up to 50 to 80 000 000 nodes. This is obviously not a hard limit, you can have a single Shard with 200 000 000 nodes but it is more of a best practice if you want to keep a fast and reliable index. With older versions of Alfresco (before the version 5.1), you couldn’t create Shards because Alfresco didn’t support it. So, at that time, there were no other solutions than having a single big index.

There is one additional thing that must be understood here: the 50 000 000 nodes soft limit is 50M nodes in the index, not in the repository. Let’s assume that you are using a DB_ID_RANGE method (see below for the explanation) with an assumed split of 65% live nodes, 20% archived nodes, 15% others (not indexed: renditions, other stores, …). So, if we are talking about the “workspace://SpacesStore” nodes (live ones), then if we want to fill a Shard with 50M nodes, we will have to use a DB_ID_RANGE of 100*50M/65 = 77M. Basically, the Shard should be more or less “full” once there are 77M IDs in the Database. For the “archive://SpacesStore” nodes (archived ones), it would be 100*50M/20 = 250M.

Alright so what are the main concepts in the Solr Sharding? There are several terms that need to be understood:

  • Node: It’s a Solr Server (a Solr installed using the Alfresco Search Services). Below, I will use “Solr Server” instead because I already use “nodes” (lowercase) for the Alfresco Documents so using “Node” (uppercase) for the Solr Server, it might be a little bit confusing…
  • Cluster: It’s a set of Solr Servers all working together to index the same repository
  • Shard: A part of the index. In other words, it’s a representation (virtual concept) of the index composed of a certain set of nodes (Alfresco Documents)
  • Shard Instance: It’s one Instance of a specific Shard. A Shard is like a virtual concept while the Instance is the implementation of that virtual concept for that piece of the index. Several Shard Instances of the same Shard will therefore contain the same set of Alfresco nodes
  • Shard Group: It’s a collection of Shards (several indices) that forms a complete index. Shards are part of the same index (same Shard Group) if they:
    • Track the same store (E.g.: workspace://SpacesStore)
    • Use the same template (E.g.: rerank)
    • Have the same number of Shards max (“numShards“)
    • Use the same configuration (Sharding methods, Solr settings, …)

Shard is often (wrongly) used in place of Shard Instance which might lead to some confusion… When you are reading “Shard”, sometimes it means the Shard itself (the virtual concept), sometimes it’s all its Shard Instances. This is these concepts can look like:
Solr Sharding - Concepts

II. Solr Sharding – Methods

Alfresco supports several methods for the Solr Sharding and they all have different attributes and different ways of working:

  • MOD_ACL_ID (ACL v1): Alfresco nodes and ACLs are grouped by their ACL ID and stored together in the same Shard. Different ACL IDs will be assigned randomly to different Shards (depending on the number of Shards you defined). Each Alfresco node using a specific ACL ID will be stored in the Shard already containing this ACL ID. This simplifies the search requests from Solr since ACLs and nodes are together, so permission checking is simple. If you have a lot of documents using the same ACL, then the distribution will not be even between Shards. Parameters:
    • shard.method=MOD_ACL_ID
    • shard.instance=<shard.instance>
    • shard.count=<shard.count>
  • ACL_ID (ACL v2): This is the same as the MOD_ACL_ID, the only difference is that it changes the method to assign to ACL to the Shards so it is more evenly distributed but if you still have a lot of documents using the same ACL then you still have the same issue. Parameters:
    • shard.method=ACL_ID
    • shard.instance=<shard.instance>
    • shard.count=<shard.count>
  • DB_ID: This is the default Sharding Method in Solr 6 which will evenly distribute the nodes in the different Shards based on their DB ID (“alf_node.id“). The ACLs are replicated on each of the Shards so that Solr is able to perform the permission checking. If you have a lot of ACLs, then this will obviously make the Shards a little bit bigger, but this is usually insignificant. Parameters:
    • shard.method=DB_ID
    • shard.instance=<shard.instance>
    • shard.count=<shard.count>
  • DB_ID_RANGE: Pretty much the same thing as the DB_ID but instead of looking into each DB ID one by one, it will just dispatch the DB IDs from the same range into the same Shard. The ranges are predefined at the Shard Instance creation and you cannot change them later, but this is also the only Sharding Method that allows you to add new Shards dynamically (auto-scaling) without the need to perform a full reindex. The lower value of the range is included and the upper value is excluded (for Math lovers: [begin-end[ ;)). Since DB IDs are incremental (increase over time), performing a search query with a date filter might end-up as simple as checking inside a single Shard. Parameters:
    • shard.method=DB_ID_RANGE
    • shard.range=<begin-end>
    • shard.instance=<shard.instance>
  • DATE: Months will be assigned to a specific Shard sequentially and then nodes are indexed into the Shard that was assigned the current month. Therefore, if you have 2 Shards, each one will contain 6 months (Shard 1 = Months 1,3,5,7,9,11 // Shard 2 = Months 2,4,6,8,10,12). It is possible to assign consecutive months to the same Shard using the “shard.date.grouping” parameter which defines how many months should be grouped together (a semester for example). If there is no date on a node, the fallback method is to use DB_ID instead. Parameters:
    • shard.method=DATE
    • shard.key=exif:dateTimeOriginal
    • shard.date.grouping=<1-12>
    • shard.instance=<shard.instance>
    • shard.count=<shard.count>
  • PROPERTY: A property is specified as the base for the Shard assignment. The first time that a node is indexed with a new value for this property, the node will be assigned randomly to a Shard. Each node coming in with the same value for this property will be assigned to the same Shard. Valid properties are either d:text (single line text), d:date (date only) or d:datetime (date+time). It is possible to use only a part of the property’s value using “shard.regex” (To keep only the first 4 digit of a date for example: shard.regex=^\d{4}). If this property doesn’t exist on a node or if the regex doesn’t match (if any is specified), the fallback method is to use DB_ID instead. Parameters:
    • shard.method=PROPERTY
    • shard.key=cm:creator
    • shard.instance=<shard.instance>
    • shard.count=<shard.count>
  • EXPLICIT_ID: Pretty much similar to the PROPERTY but instead of using the value of a “random” property, this method requires a specific property (d:text) to define explicitly on which Shard the node should be indexed. Therefore, this will require an update of the Data Model to have one property dedicated to the assignment of a node to a Shard. In case you are using several types of documents, then you will potentially want to do that for all. If this property doesn’t exist on a node or if an invalid Shard number is given, the fallback method is to use DB_ID instead. Parameters:
    • shard.method=EXPLICIT_ID
    • shard.key=<property> (E.g.: cm:targetShardInstance)
    • shard.instance=<shard.instance>
    • shard.count=<shard.count>

As you can see above, each Sharding Method has its own set of properties. You can define these properties in:

  • The template’s solrcore.properties file in which case it will apply to all Shard Instance creations
    • E.g.: $SOLR_HOME/solrhome/templates/rerank/conf/solrcore.properties
  • The URL/Command used to create the Shard Instance in which case it will only apply to the current Shard Instance creation
    • E.g.: curl -v “http://host:port/solr/admin/cores?action=newCore&…&property.shard.method=DB_ID_RANGE&property.shard.range=0-50000000&property.shard.instance=0

Summary of the benefits of each method:
Solr Sharding - Benefits

First supported versions for the Solr Sharding in Alfresco:
Solr Sharding - Availability

Hopefully, this is a good first look into the Solr Sharding. In a future blog, I will talk about the creation process and show some example of what is possible. If you want to read more on the subject, don’t hesitate to take a look at the Alfresco documentation, it doesn’t explain everything, but it is still a very good starting point.

Cet article Solr Sharding – Concepts & Methods est apparu en premier sur Blog dbi services.

pgconf.eu – Welcome to the community

Fri, 2019-10-18 15:41

On tuesday I started my journey to Milan to attend my first pgconf.eu, which was also my first big conference. I was really excited what will come up to me. How will it be, to become a visible part of the community. How will it be, to give my first presentation in front of so many people?

The conference started with the welcome and opening session. It took place in a huge room, to give all of the participants a seat. Really amazing, how big this community is and it is still growing. So many people from all over the world (Japan, USA, Chile, Canada….) attending this conference.

And suddenly I realized, this is the room, where I have to give my session. Some really strange feelings came up. This is my first presentation at a conference, this is the main stage, there is space for so many people! And I really hoped, they will make it smaller for me. But there was something else: Anticipation.

But first I want to give you some impressions from my time at the pgconf. Amazing to talk to one of the main developers of Patroni. I was really nervous when I just went to him and said: “Hi, may I ask you a question?” For sure he didn’t say NO. Even all the other ladies and gentlemen I met (the list is quite long), they all are so nice and all of them really open minded (is this because they all work with an open source database?). And of course a special thanks to Pavel Golub for the great picture. Find it in Daniel’s blog
Beside meeting all that great people, I enjoyed some really informative and cool sessions.


Although I still hoped they are going to make the room smaller for my presentation, of course they didn’t do it. So I had only one chance:

And I did it and afterwards I book it under “good experience”. A huge room is not so much different than a small one.

As I am back home now, I want to say: Thanks pgconf.eu and dbi services for giving me this opportunity and thanks to the community for this warm welcome.

Cet article pgconf.eu – Welcome to the community est apparu en premier sur Blog dbi services.

Creating a customized PostgreSQL container using buildah

Wed, 2019-10-16 09:09

Quite some time ago I blogged about how you could build your customzized PostgreSQL container by using a Dockerfile and Docker build. In the meantime Red Hat replaced Docker in OpenShift and SUSE replaced Docker as well in CaaS. As a consequence there need to be other ways of building containers and one of them is buildah. You can use buildah to build from a Docker file as well, but in this post we will use a simple bash script to create the container.

We start be defining four variables that define PGDATA, the PostgreSQL major version, the full version string and the minor version which will be used to create our standard installation location (these will also go into the entrypoint, see below):

#!/bin/bash
_PGDATA="/u02/pgdata"
_PGMAJOR=12
_PGVERSION=12.0
_PGMINOR="db_0"

As mentioned in the beginning buildah will be used to create the container. For running the container we need something else, and that is podman. You can run the container buildah creates with plain Docker as well, if you want, as it is oci compliant but as Red Hat does not ship Docker anymore we will use the recommended way of doing it by using podman. So the natural next step in the script is do install buildah and podman:

dnf install -y buildah podman

Buildah can create containers from scratch, which means you start with a container that contains nothing except some meta data:

newcontainer=$(buildah from scratch)

Once we have the new scratch container it gets mounted so dnf can be used to install the packages we need into the container without actually using dnf in the container:

scratchmnt=$(buildah mount $newcontainer)
ls -la $scratchmnt
dnf install --installroot $scratchmnt --releasever 8 bash coreutils gcc openldap-devel platform-python-devel readline-devel bison flex perl-ExtUtils-Embed zlib-devel openssl-devel pam-devel libxml2-devel libxslt-devel bzip2 wget policycoreutils-python-utils make tar --setopt install_weak_deps=false --setopt=tsflags=nodocs --setopt=override_install_langs=en_US.utf8 -y

Using “buildah config” the container can be configured. Here it is about the author, environment variables, the default user and the entrypoint that will be used once the conatiner will be started:

buildah config --created-by "dbi services"  $newcontainer
buildah config --author "dbi services" --label name=dbiservices $newcontainer
buildah run $newcontainer groupadd postgres
buildah run $newcontainer useradd -g postgres -m postgres
buildah config --user postgres $newcontainer
buildah config --workingdir /home/postgres $newcontainer
buildah config --env PGDATABASE="" $newcontainer
buildah config --env PGUSERNAME="" $newcontainer
buildah config --env PGPASSWORD="" $newcontainer
buildah config --env PGDATA=${_PGDATA} $newcontainer
buildah config --env PGMAJOR=${_PGMAJOR} $newcontainer
buildah config --env PGMINOR=${_PGMINOR} $newcontainer
buildah config --env PGVERSION=${_PGVERSION} $newcontainer
buildah config --entrypoint /usr/bin/entrypoint.sh $newcontainer
buildah copy $newcontainer ./entrypoint.sh /usr/bin/entrypoint.sh
buildah run $newcontainer chmod +x /usr/bin/entrypoint.sh

What follows is basically installing PostgreSQL from source code:

buildah run --user root $newcontainer mkdir -p /u01 /u02
buildah run --user root $newcontainer chown postgres:postgres /u01 /u02
buildah run --user postgres $newcontainer wget https://ftp.postgresql.org/pub/source/v${_PGVERSION}/postgresql-${_PGVERSION}.tar.bz2 -O /home/postgres/postgresql-${_PGVERSION}.tar.bz2
buildah run --user postgres $newcontainer /usr/bin/bunzip2 /home/postgres/postgresql-${_PGVERSION}.tar.bz2
buildah run --user postgres $newcontainer /usr/bin/tar -xvf /home/postgres/postgresql-${_PGVERSION}.tar -C /home/postgres/
buildah run --user postgres $newcontainer /home/postgres/postgresql-12.0/configure --prefix=/u01/app/postgres/product/${_PGMAJOR}/${_PGMINOR} --exec-prefix=/u01/app/postgres/product/${_PGMAJOR}/${_PGMINOR} --bindir=/u01/app/postgres/product/${_PGMAJOR}/${_PGMINOR}/bin --libdir=/u01/app/postgres/product/${_PGMAJOR}/${_PGMINOR}/lib --includedir=/u01/app/postgres/product/${_PGMAJOR}/${_PGMINOR}/include 
buildah run --user postgres $newcontainer /usr/bin/make -C /home/postgres all
buildah run --user postgres $newcontainer /usr/bin/make -C /home/postgres install
buildah run --user postgres $newcontainer /usr/bin/make -C /home/postgres/contrib install

Containers shoud be as small as possible so lets do some cleanup:

buildah run --user postgres $newcontainer /usr/bin/rm -rf /home/postgres/postgresql-${_PGVERSION}.tar
buildah run --user postgres $newcontainer /usr/bin/rm -rf /home/postgres/config
buildah run --user postgres $newcontainer /usr/bin/rm -rf /home/postgres/config.log
buildah run --user postgres $newcontainer /usr/bin/rm -rf /home/postgres/config.status
buildah run --user postgres $newcontainer /usr/bin/rm -rf /home/postgres/contrib
buildah run --user postgres $newcontainer /usr/bin/rm -rf /home/postgres/GNUmakefile
buildah run --user postgres $newcontainer /usr/bin/rm -rf /home/postgres/postgresql-12.0
buildah run --user postgres $newcontainer /usr/bin/rm -rf /home/postgres/src
buildah run --user postgres $newcontainer /usr/bin/rm -rf /home/postgres/doc
buildah run --user postgres $newcontainer /usr/bin/rm -rf /home/postgres/Makefile
buildah run --user postgres $newcontainer /usr/bin/rm -rf /home/postgres/.wget-hsts

When you want to run PostgreSQL inside a container you do not need any of the following binaries, so these can be cleaned as well:

buildah run --user postgres $newcontainer /usr/bin/rm -rf /u01/app/postgres/product/${_PGMAJOR}/${_PGMINOR}/bin/vacuumlo
buildah run --user postgres $newcontainer /usr/bin/rm -rf /u01/app/postgres/product/${_PGMAJOR}/${_PGMINOR}/bin/vacuumdb
buildah run --user postgres $newcontainer /usr/bin/rm -rf /u01/app/postgres/product/${_PGMAJOR}/${_PGMINOR}/bin/reindexdb
buildah run --user postgres $newcontainer /usr/bin/rm -rf /u01/app/postgres/product/${_PGMAJOR}/${_PGMINOR}/bin/pgbench
buildah run --user postgres $newcontainer /usr/bin/rm -rf /u01/app/postgres/product/${_PGMAJOR}/${_PGMINOR}/bin/pg_waldump
buildah run --user postgres $newcontainer /usr/bin/rm -rf /u01/app/postgres/product/${_PGMAJOR}/${_PGMINOR}/bin/pg_test_timing
buildah run --user postgres $newcontainer /usr/bin/rm -rf /u01/app/postgres/product/${_PGMAJOR}/${_PGMINOR}/bin/pg_test_fsync
buildah run --user postgres $newcontainer /usr/bin/rm -rf /u01/app/postgres/product/${_PGMAJOR}/${_PGMINOR}/bin/pg_standby
buildah run --user postgres $newcontainer /usr/bin/rm -rf /u01/app/postgres/product/${_PGMAJOR}/${_PGMINOR}/bin/pg_restore
buildah run --user postgres $newcontainer /usr/bin/rm -rf /u01/app/postgres/product/${_PGMAJOR}/${_PGMINOR}/bin/pg_recvlogical
buildah run --user postgres $newcontainer /usr/bin/rm -rf /u01/app/postgres/product/${_PGMAJOR}/${_PGMINOR}/bin/pg_receivewal
buildah run --user postgres $newcontainer /usr/bin/rm -rf /u01/app/postgres/product/${_PGMAJOR}/${_PGMINOR}/bin/pg_isready
buildah run --user postgres $newcontainer /usr/bin/rm -rf /u01/app/postgres/product/${_PGMAJOR}/${_PGMINOR}/bin/pg_dumpall
buildah run --user postgres $newcontainer /usr/bin/rm -rf /u01/app/postgres/product/${_PGMAJOR}/${_PGMINOR}/bin/pg_dump
buildah run --user postgres $newcontainer /usr/bin/rm -rf /u01/app/postgres/product/${_PGMAJOR}/${_PGMINOR}/bin/pg_checksums
buildah run --user postgres $newcontainer /usr/bin/rm -rf /u01/app/postgres/product/${_PGMAJOR}/${_PGMINOR}/bin/pg_basebackup
buildah run --user postgres $newcontainer /usr/bin/rm -rf /u01/app/postgres/product/${_PGMAJOR}/${_PGMINOR}/bin/pg_archivecleanup
buildah run --user postgres $newcontainer /usr/bin/rm -rf /u01/app/postgres/product/${_PGMAJOR}/${_PGMINOR}/bin/oid2name
buildah run --user postgres $newcontainer /usr/bin/rm -rf /u01/app/postgres/product/${_PGMAJOR}/${_PGMINOR}/bin/dropuser
buildah run --user postgres $newcontainer /usr/bin/rm -rf /u01/app/postgres/product/${_PGMAJOR}/${_PGMINOR}/bin/dropdb
buildah run --user postgres $newcontainer /usr/bin/rm -rf /u01/app/postgres/product/${_PGMAJOR}/${_PGMINOR}/bin/createuser
buildah run --user postgres $newcontainer /usr/bin/rm -rf /u01/app/postgres/product/${_PGMAJOR}/${_PGMINOR}/bin/createdb
buildah run --user postgres $newcontainer /usr/bin/rm -rf /u01/app/postgres/product/${_PGMAJOR}/${_PGMINOR}/bin/clusterdb

Last, but not least remove all the packages we do not require anymore and get rid of the dnf cache:

dnf remove --installroot $scratchmnt --releasever 8 gcc openldap-devel readline-devel bison flex perl-ExtUtils-Embed zlib-devel openssl-devel pam-devel libxml2-devel libxslt-devel bzip2 wget policycoreutils-python-utils make tar -y
dnf clean all -y --installroot $scratchmnt --releasever 8
# Clean up yum cache
if [ -d "${scratchmnt}" ]; then
rm -rf "${scratchmnt}"/var/cache/yum
fi
buildah unmount $newcontainer

Ready to publish the container:

buildah commit $newcontainer dbi-postgres

When you put all those steps into a script and run that you should see the just created container:

[root@doag2019 ~]$ buildah containers
CONTAINER ID  BUILDER  IMAGE ID     IMAGE NAME                       CONTAINER NAME
47946e4b4fc8     *                  scratch                          working-container
[root@doag2019 ~]$

… but now we also have a new image that can be started:

IMAGE NAME                                               IMAGE TAG            IMAGE ID             CREATED AT             SIZE
localhost/dbi-postgres                                   latest               dfcd3e8d5273         Oct 13, 2019 13:22     461 MB

Once we start that the entrypoint will be executed:

#!/bin/bash
# this are the environment variables which need to be set
PGDATA=${PGDATA}/${PGMAJOR}
PGHOME="/u01/app/postgres/product/${PGMAJOR}/${PGMINOR}"
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
${PGHOME}/bin/psql -c "create extension pg_stat_statements" postgres
}
# start the PostgreSQL instance
_pg_prestart()
{
${PGHOME}/bin/pg_ctl -D ${PGDATA} -w start
}
# Start PostgreSQL without detaching 
_pg_start()
{
exec ${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() {
if [ -z $PGMEMORY ]; then MEM="128MB"
else                      MEM=$PGMEMORY; fi
# PostgreSQL parameters
echo "shared_buffers='$MEM'" >> ${PGAUTOCONF}
echo "effective_cache_size='128MB'" >> ${PGAUTOCONF}
echo "listen_addresses = '*'" >> ${PGAUTOCONF}
echo "logging_collector = 'off'" >> ${PGAUTOCONF}
echo "log_truncate_on_rotation = 'on'" >> ${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 "wal_compression=on" >> ${PGAUTOCONF}
echo "max_replication_slots=20" >> ${PGAUTOCONF}
echo "max_wal_senders=20" >> ${PGAUTOCONF}
echo "hot_standby_feedback = 'on'" >> ${PGAUTOCONF}
echo "cluster_name = '${PGDATABASENAME}'" >> ${PGAUTOCONF}
echo "max_replication_slots = '10'" >> ${PGAUTOCONF}
echo "work_mem=8MB" >> ${PGAUTOCONF}
echo "maintenance_work_mem=64MB" >> ${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}
echo "archive_mode=on" >> ${PGAUTOCONF}
echo "archive_command='/bin/true'" >> ${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
# restart database with correct pid
_pg_stop
_pg_start
}
# 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 "${DEBUG}" ]; then
/bin/bash
elif [ -e "${PGDATA}/base" ]; then
# when there is the base directory this
# probably is a valid PostgreSQL cluster
# so we just start it
_pg_start
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
# create PGDATA
mkdir -p ${PGDATA}
# initialze and start the new cluster
_pg_init_and_start
fi

Starting that up using podman:

[root@doag2019 ~]$ podman run -e PGDATABASE=test -e PGUSERNAME=test -e PGPASSWORD=test --detach -p 5432:5432 localhost/dbi-postgres
f933df8216de83b3c2243860ace02f231748a05273c16d3ddb0308231004552f
CONTAINER ID  IMAGE                          COMMAND               CREATED             STATUS             PORTS                   NAMES
f933df8216de  localhost/dbi-postgres:latest  /bin/sh -c /usr/b...  About a minute ago  Up 59 seconds ago  0.0.0.0:5432->5432/tcp  nervous_leavitt

… and connecting from the host system:

[root@doag2019 ~]$ psql -p 5432 -h localhost -U test test
Password for user test:
psql (10.6, server 12.0)
WARNING: psql major version 10, server major version 12.
Some psql features might not work.
Type "help" for help.
test=> select version();
version
--------------------------------------------------------------------------------------------------------
PostgreSQL 12.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.2.1 20180905 (Red Hat 8.2.1-3), 64-bit
(1 row)
test=> \q

One you have that scripted and ready it is a very convinient way for creating images. What I like most is, that you can make changes afterwards without starting from scratch:

[root@doag2019 ~]$ podman inspect localhost/dbi-postgres
[
{
"Id": "dfcd3e8d5273116e5678806dfe7bbf3ca2276549db73e62f27b967673df8084c",
"Digest": "sha256:b2d65e569becafbe64e8bcb6d49b065188411f596c04dea2cf335f677e2db68e",
"RepoTags": [
"localhost/dbi-postgres:latest"
],
"RepoDigests": [
"localhost/dbi-postgres@sha256:b2d65e569becafbe64e8bcb6d49b065188411f596c04dea2cf335f677e2db68e"
],
"Parent": "",
"Comment": "",
"Created": "2019-10-13T11:22:15.096957689Z",
"Config": {
"User": "postgres",
"Env": [
"PGDATABASE=",
"PGUSERNAME=",
"PGPASSWORD=",
"PGDATA=/u02/pgdata",
"PGMAJOR=12",
"PGMINOR=db_0",
"PGVERSION=12.0"
],
"Entrypoint": [
"/bin/sh",
"-c",
"/usr/bin/entrypoint.sh"
],
"WorkingDir": "/home/postgres",
"Labels": {
"name": "dbiservices"
}
},
"Version": "",
"Author": "dbiservices",
"Architecture": "amd64",
"Os": "linux",
"Size": 460805033,
"VirtualSize": 460805033,
"GraphDriver": {
"Name": "overlay",
"Data": {
"MergedDir": "/var/lib/containers/storage/overlay/89de699f19781bb61eec12cf61a097a9daa31d7725fc3c078c76d0d6291cb074/merged",
"UpperDir": "/var/lib/containers/storage/overlay/89de699f19781bb61eec12cf61a097a9daa31d7725fc3c078c76d0d6291cb074/diff",
"WorkDir": "/var/lib/containers/storage/overlay/89de699f19781bb61eec12cf61a097a9daa31d7725fc3c078c76d0d6291cb074/work"
}
},
"RootFS": {
"Type": "layers",
"Layers": [
"sha256:89de699f19781bb61eec12cf61a097a9daa31d7725fc3c078c76d0d6291cb074"
]
},
"Labels": {
"name": "dbiservices"
},
"Annotations": {},
"ManifestType": "application/vnd.oci.image.manifest.v1+json",
"User": "postgres",
"History": [
{
"created": "2019-10-13T11:22:15.096957689Z",
"created_by": "dbi services",
"author": "dbiservices"
}
]
}
]

Assume we want to add a new environment variable. All we need to do is this:

[root@doag2019 ~]$ buildah containers
CONTAINER ID  BUILDER  IMAGE ID     IMAGE NAME                       CONTAINER NAME
47946e4b4fc8     *                  scratch                          working-container
[root@doag2019 ~]$ buildah config --env XXXXXXX="xxxxxxxx" 47946e4b4fc8
[root@doag2019 ~]$ buildah commit 47946e4b4fc8 dbi-postgres
Getting image source signatures
Skipping fetch of repeat blob sha256:9b74f2770486cdb56539b4a112b95ad7e10aced3a2213d33878f8fd736b5c684
Copying config sha256:e2db86571bfa2e64e6079077fe023e38a07544ccda529ba1c3bfc04984f2ac74
606 B / 606 B [============================================================] 0s
Writing manifest to image destination
Storing signatures
e2db86571bfa2e64e6079077fe023e38a07544ccda529ba1c3bfc04984f2ac74

The new image with the new variable is ready:

[root@doag2019 ~]$ buildah images
IMAGE NAME                                               IMAGE TAG            IMAGE ID             CREATED AT             SIZE
                                                                              dfcd3e8d5273         Oct 13, 2019 13:22     461 MB
localhost/dbi-postgres                                   latest               e2db86571bfa         Oct 13, 2019 13:52     461 MB
[root@doag2019 ~]$ buildah inspect localhost/dbi-postgres
...
"Env": [
"PGDATABASE=",
"PGUSERNAME=",
"PGPASSWORD=",
"PGDATA=/u02/pgdata",
"PGMAJOR=12",
"PGMINOR=db_0",
"PGVERSION=12.0",
"XXXXXXX=xxxxxxxx"
],
...

Nice. If you are happy with the image the scratch container can be deleted.

Cet article Creating a customized PostgreSQL container using buildah est apparu en premier sur Blog dbi services.

Patroni Operations – switchover and failover

Fri, 2019-10-11 09:22

In this post we will have a look at switchover and failover of a Patroni cluster. As well as a look at the maintenance mode Patroni offers, which gives the opportunity to prevent from an automatic failover.

Switchover

There are two possibilities to run a switchover, either in scheduled mode or immediately.

1. Scheduled Switchover
postgres@patroni1:/home/postgres/ [PG1] patronictl switchover
Master [patroni1]:
Candidate ['patroni2', 'patroni3'] []: patroni2
When should the switchover take place (e.g. 2019-10-08T11:31 )  [now]: 2019-10-08T10:32
Current cluster topology
+---------+----------+----------------+--------+---------+----+-----------+
| Cluster |  Member  |      Host      |  Role  |  State  | TL | Lag in MB |
+---------+----------+----------------+--------+---------+----+-----------+
|   PG1   | patroni1 | 192.168.22.111 | Leader | running |  2 |       0.0 |
|   PG1   | patroni2 | 192.168.22.112 |        | running |  2 |       0.0 |
|   PG1   | patroni3 | 192.168.22.113 |        | running |  2 |       0.0 |
+---------+----------+----------------+--------+---------+----+-----------+
Are you sure you want to schedule switchover of cluster PG1 at 2019-10-08T10:32:00+02:00, demoting current master patroni1? [y/N]: y
2019-10-08 10:31:14.89236 Switchover scheduled
+---------+----------+----------------+--------+---------+----+-----------+
| Cluster |  Member  |      Host      |  Role  |  State  | TL | Lag in MB |
+---------+----------+----------------+--------+---------+----+-----------+
|   PG1   | patroni1 | 192.168.22.111 | Leader | running |  2 |       0.0 |
|   PG1   | patroni2 | 192.168.22.112 |        | running |  2 |       0.0 |
|   PG1   | patroni3 | 192.168.22.113 |        | running |  2 |       0.0 |
+---------+----------+----------------+--------+---------+----+-----------+
 Switchover scheduled at: 2019-10-08T10:32:00+02:00
                    from: patroni1
                      to: patroni2
postgres@patroni1:/home/postgres/ [PG1]

That’s it. At the given time, the switchover will take place. All you see in the logfile is an entry like this

Oct  8 10:32:00 patroni1 patroni: 2019-10-08 10:32:00,006 INFO: Manual scheduled failover at 2019-10-08T10:32:00+02:00
Oct  8 10:32:00 patroni1 patroni: 2019-10-08 10:32:00,016 INFO: Got response from patroni2 http://192.168.22.112:8008/patroni: {"database_system_identifier": "6745341072751547355", "postmaster_start_time": "2019-10-08 10:09:40.217 CEST", "timeline": 2, "cluster_unlocked": false, "patroni": {"scope": "PG1", "version": "1.6.0"}, "state": "running", "role": "replica", "xlog": {"received_location": 83886560, "replayed_timestamp": null, "paused": false, "replayed_location": 83886560}, "server_version": 110005}
Oct  8 10:32:00 patroni1 patroni: 2019-10-08 10:32:00,113 INFO: manual failover: demoting myself
Oct  8 10:32:01 patroni1 patroni: 2019-10-08 10:32:01,256 INFO: Leader key released
Oct  8 10:32:03 patroni1 patroni: 2019-10-08 10:32:03,271 INFO: Local timeline=2 lsn=0/6000028
Oct  8 10:32:03 patroni1 patroni: 2019-10-08 10:32:03,279 INFO: master_timeline=3
Oct  8 10:32:03 patroni1 patroni: 2019-10-08 10:32:03,281 INFO: master: history=1#0110/5000098#011no recovery target specified
Oct  8 10:32:03 patroni1 patroni: 2#0110/6000098#011no recovery target specified
Oct  8 10:32:03 patroni1 patroni: 2019-10-08 10:32:03,282 INFO: closed patroni connection to the postgresql cluster
Oct  8 10:32:03 patroni1 patroni: 2019-10-08 10:32:03,312 INFO: postmaster pid=11537
Oct  8 10:32:03 patroni1 patroni: 192.168.22.111:5432 - no response
Oct  8 10:32:03 patroni1 patroni: 2019-10-08 10:32:03.325 CEST - 1 - 11537 -  - @ - 0LOG:  listening on IPv4 address "192.168.22.111", port 5432
Oct  8 10:32:03 patroni1 patroni: 2019-10-08 10:32:03.328 CEST - 2 - 11537 -  - @ - 0LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
Oct  8 10:32:03 patroni1 patroni: 2019-10-08 10:32:03.339 CEST - 3 - 11537 -  - @ - 0LOG:  redirecting log output to logging collector process
Oct  8 10:32:03 patroni1 patroni: 2019-10-08 10:32:03.339 CEST - 4 - 11537 -  - @ - 0HINT:  Future log output will appear in directory "pg_log".
Oct  8 10:32:04 patroni1 patroni: 192.168.22.111:5432 - accepting connections
Oct  8 10:32:04 patroni1 patroni: 192.168.22.111:5432 - accepting connections
Oct  8 10:32:04 patroni1 patroni: 2019-10-08 10:32:04,895 INFO: Lock owner: patroni2; I am patroni1
Oct  8 10:32:04 patroni1 patroni: 2019-10-08 10:32:04,895 INFO: does not have lock
Oct  8 10:32:04 patroni1 patroni: 2019-10-08 10:32:04,896 INFO: establishing a new patroni connection to the postgres cluster
2. Immediate switchover

Here you start the same way as for planned switchover, but the switchover will take place immediatelly.

postgres@patroni1:/home/postgres/ [PG1] patronictl list
+---------+----------+----------------+--------+---------+----+-----------+
| Cluster |  Member  |      Host      |  Role  |  State  | TL | Lag in MB |
+---------+----------+----------------+--------+---------+----+-----------+
|   PG1   | patroni1 | 192.168.22.111 |        | running |  1 |       0.0 |
|   PG1   | patroni2 | 192.168.22.112 | Leader | running |  1 |       0.0 |
|   PG1   | patroni3 | 192.168.22.113 |        | running |  1 |       0.0 |
+---------+----------+----------------+--------+---------+----+-----------+
postgres@patroni1:/home/postgres/ [PG1] patronictl switchover
Master [patroni2]:
Candidate ['patroni1', 'patroni3'] []: patroni1
When should the switchover take place (e.g. 2019-10-08T11:09 )  [now]:
Current cluster topology
+---------+----------+----------------+--------+---------+----+-----------+
| Cluster |  Member  |      Host      |  Role  |  State  | TL | Lag in MB |
+---------+----------+----------------+--------+---------+----+-----------+
|   PG1   | patroni1 | 192.168.22.111 |        | running |  1 |       0.0 |
|   PG1   | patroni2 | 192.168.22.112 | Leader | running |  1 |       0.0 |
|   PG1   | patroni3 | 192.168.22.113 |        | running |  1 |       0.0 |
+---------+----------+----------------+--------+---------+----+-----------+
Are you sure you want to switchover cluster PG1, demoting current master patroni2? [y/N]: y
2019-10-08 10:09:38.88046 Successfully switched over to "patroni1"
+---------+----------+----------------+--------+---------+----+-----------+
| Cluster |  Member  |      Host      |  Role  |  State  | TL | Lag in MB |
+---------+----------+----------------+--------+---------+----+-----------+
|   PG1   | patroni1 | 192.168.22.111 | Leader | running |  1 |           |
|   PG1   | patroni2 | 192.168.22.112 |        | stopped |    |   unknown |
|   PG1   | patroni3 | 192.168.22.113 |        | running |  1 |       0.0 |
+---------+----------+----------------+--------+---------+----+-----------+
postgres@patroni1:/home/postgres/ [PG1] patronictl list
+---------+----------+----------------+--------+---------+----+-----------+
| Cluster |  Member  |      Host      |  Role  |  State  | TL | Lag in MB |
+---------+----------+----------------+--------+---------+----+-----------+
|   PG1   | patroni1 | 192.168.22.111 | Leader | running |  2 |       0.0 |
|   PG1   | patroni2 | 192.168.22.112 |        | running |  2 |       0.0 |
|   PG1   | patroni3 | 192.168.22.113 |        | running |  2 |       0.0 |
+---------+----------+----------------+--------+---------+----+-----------+
postgres@patroni1:/home/postgres/ [PG1]
Failover

In difference to the switchover, the failover is executed automatically, when the Leader node is getting unavailable for unplanned reason.
You can only adjust some database parameter to affect the failover.

The parameters for failover arre also managed using patronictl. But they are not in the parameter section, they are above. so let’s say, we adjust one parameter and add one paramter to not use the default anymore.

postgres@patroni1:/u01/app/postgres/local/dmk/etc/ [PG1] patronictl edit-config
postgres@patroni1:/u01/app/postgres/local/dmk/etc/ [PG1] patronictl edit-config
---
+++
@@ -1,5 +1,6 @@
-loop_wait: 7
+loop_wait: 10
 maximum_lag_on_failover: 1048576
+master_start_timeout: 240
 postgresql:
   parameters:
     archive_command: /bin/true

Apply these changes? [y/N]: y
Configuration changed

Afterwards there is no need to restart the database. Changes take affect immediately. So the failover can be configured according to every special need. A list of all possible parameter changes can be found here .

Maintenance mode

In some cases it is necessary to do maintenance on a single node and you do not want Patroni to manage the cluster. This can be needed for e.g. release updates.
When Patroni paused, it won’t change the state of PostgeSQL. For example it will not try to start the cluster when it is stopped.

So let’s do an example. We will pause the cluster, stop the replica, upgrade from 9.6.8 to 9.6.13 and afterwards start the replica again. In case we do not pause the replica, the database will be started automatically by Patroni.

postgres@patroni1:/home/postgres/ [PG1] patronictl pause
Success: cluster management is paused
You have new mail in /var/spool/mail/opendb
postgres@patroni1:/home/postgres/ [PG1] patronictl list
+---------+----------+----------------+--------+---------+----+-----------+
| Cluster |  Member  |      Host      |  Role  |  State  | TL | Lag in MB |
+---------+----------+----------------+--------+---------+----+-----------+
|   PG1   | patroni1 | 192.168.22.111 | Leader | running |  2 |       0.0 |
|   PG1   | patroni2 | 192.168.22.112 |        | running |  2 |       0.0 |
|   PG1   | patroni3 | 192.168.22.113 |        | running |  2 |       0.0 |
+---------+----------+----------------+--------+---------+----+-----------+
 Maintenance mode: on

On the replica

postgres@patroni2:/home/postgres/ [PG1] pg_ctl stop -D /u02/pgdata/96/PG1/ -m fast

postgres@patroni2:/home/postgres/ [PG1] export PATH= /u01/app/postgres/product/PG96/db_13/bin:$PATH
postgres@patroni2:/home/postgres/ [PG1] export PORT=5432
postgres@patroni2:/home/postgres/ [PG1] which pg_ctl
/u01/app/opendb/product/PG96/db_13/bin/pg_ctl

postgres@patroni2:/home/postgres/ [PG1] pg_ctl -D /u02/pgdata/96/PG1 start
server starting
postgres@patroni2:/home/postgres/ [PG1] 2019-10-08 17:25:28.358 CEST - 1 - 23192 -  - @ - 0LOG:  redirecting log output to logging collector process
2019-10-08 17:25:28.358 CEST - 2 - 23192 -  - @ - 0HINT:  Future log output will appear in directory "pg_log".

postgres@patroni2:/home/postgres/ [PG1] psql -c "select version()" postgres
                                                           version
------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.6.13 dbi services build on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
(1 row)

postgres@patroni2:/home/postgres/ [PG1] patronictl resume
Success: cluster management is resumed

postgres@patroni2:/home/postgres/ [PG1] patronictl list
+---------+----------+----------------+--------+---------+----+-----------+
| Cluster |  Member  |      Host      |  Role  |  State  | TL | Lag in MB |
+---------+----------+----------------+--------+---------+----+-----------+
|   PG1   | patroni1 | 192.168.22.111 | Leader | running |  5 |       0.0 |
|   PG1   | patroni2 | 192.168.22.112 |        | running |  5 |       0.0 |
|   PG1   | patroni3 | 192.168.22.113 |        | running |  5 |       0.0 |
+---------+----------+----------------+--------+---------+----+-----------+

You can do this on the other nodes as well.

Conclusion

Switchover is quite easy and for all the test I did so far it was really reliable. As well as the failover, here you just have to think about adjusting the parameters to your needs. Not in every case it is the best solution to wait 5 min for a failover.

Cet article Patroni Operations – switchover and failover est apparu en premier sur Blog dbi services.

Creating archived redolog-files in group dba instead of oinstall

Wed, 2019-10-09 15:07
Since Oracle 11g files created by the database belong by default to the Linux group oinstall. Changing the default group after creating the central inventory is difficult. In this Blog I want to show how locally created archived redo can be in group dba instead of oinstall.

One of my customers had the requirement to provide read-access on archived redo to an application for logmining. To ensure the application can access the archived redo, we created an additinal local archive log destination:


LOG_ARCHIVE_DEST_9 = 'LOCATION=/logmining/ARCHDEST/NCEE19C valid_for=(online_logfile,primary_role)'

and provided NFS-access to that directory for the application. To ensure that the application can access the archived redo, the remote user was part of a remote dba-group, which had the same group-id (GID) as the dba-group on the DB-server. Everything worked fine until we migrated to a new server and changed the setup to use oinstall as the default group for Oracle. The application could no longer read the files, because they were created with group oinstall:


oracle@19c:/logmining/ARCHDEST/NCEE19C/ [NCEE19C] ls -ltr
-rw-r-----. 1 oracle oinstall 24403456 Oct 9 21:21 1_32_1017039068.dbf
-rw-r-----. 1 oracle oinstall 64000 Oct 9 21:25 1_33_1017039068.dbf
-rw-r-----. 1 oracle oinstall 29625856 Oct 9 21:27 1_34_1017039068.dbf
oracle@19c:/logmining/ARCHDEST/NCEE19C/ [NCEE19C]

One possibility to workaround this would have been to use the id-mapper on Linux, but there’s something better:

With the group-sticky-bit on Linux we can achieve, that all files in a directory are part of the group of the directory.

I.e.


oracle@19c:/logmining/ARCHDEST/ [NCEE19C] ls -l
total 0
drwxr-xr-x. 1 oracle dba 114 Oct 9 21:27 NCEE19C
oracle@19c:/logmining/ARCHDEST/ [NCEE19C] chmod g+s NCEE19C
oracle@19c:/logmining/ARCHDEST/ [NCEE19C] ls -l
drwxr-sr-x. 1 oracle dba 114 Oct 9 21:27 NCEE19C

Whenever an archived redo is created in that directory it will be in the dba-group:


SQL> alter system switch logfile;
 
System altered.
 
SQL> exit
 
oracle@19c:/logmining/ARCHDEST/ [NCEE19C] cd NCEE19C/
oracle@19c:/logmining/ARCHDEST/NCEE19C/ [NCEE19C] ls -ltr
-rw-r-----. 1 oracle oinstall 24403456 Oct 9 21:21 1_32_1017039068.dbf
-rw-r-----. 1 oracle oinstall 64000 Oct 9 21:25 1_33_1017039068.dbf
-rw-r-----. 1 oracle oinstall 29625856 Oct 9 21:27 1_34_1017039068.dbf
-rw-r-----. 1 oracle dba 193024 Oct 9 21:50 1_35_1017039068.dbf
oracle@19c:/logmining/ARCHDEST/NCEE19C/ [NCEE19C]

To make all files part of the dba-group use chgrp and use the newest archivelog as a reference:


oracle@19c:/logmining/ARCHDEST/NCEE19C/ [NCEE19C] chgrp --reference 1_35_1017039068.dbf 1_3[2-4]*.dbf
oracle@19c:/logmining/ARCHDEST/NCEE19C/ [NCEE19C] ls -ltr
-rw-r-----. 1 oracle dba 24403456 Oct 9 21:21 1_32_1017039068.dbf
-rw-r-----. 1 oracle dba 64000 Oct 9 21:25 1_33_1017039068.dbf
-rw-r-----. 1 oracle dba 29625856 Oct 9 21:27 1_34_1017039068.dbf
-rw-r-----. 1 oracle dba 193024 Oct 9 21:50 1_35_1017039068.dbf
oracle@19c:/logmining/ARCHDEST/NCEE19C/ [NCEE19C]

Hope this helps somebody.

Cet article Creating archived redolog-files in group dba instead of oinstall est apparu en premier sur Blog dbi services.

Oracle 19c

Wed, 2019-10-09 05:33
Oracle 19c has been released quite a while ago already and some customers already run it in Production. However, as it is the long term supported release, I thought I blog about some interesting information and features around 19c to encourage people to migrate to it.

Download Oracle 19c:

https://www.oracle.com/technetwork/database/enterprise-edition/downloads
or
https://edelivery.oracle.com (search e.g. for “Database Enterprise Edition”)

Docker-Images:
https://github.com/oracle/docker-images/tree/master/OracleDatabase

Oracle provides different offerings for 19c:

On-premises:
– Oracle Database Standard Edition 2 (SE2)
– Oracle Database Enterprise Edition (EE)
– Oracle Database Enterprise Edition on Engineered Systems (EE-ES)
– Oracle Database Personal Edition (PE)

Cloud:
– Oracle Database Cloud Service Standard Edition (DBCS SE)
– Oracle Database Cloud Service Enterprise Edition (DBCS EE)
– Oracle Database Cloud Service Enterprise Edition -High Performance (DBCS EE-HP)
– Oracle Database Cloud Service Enterprise Edition -Extreme Performance (DBCS EE-EP)
– Oracle Database Exadata Cloud Service (ExaCS)

REMARK: When this Blog was released the Autonomous DB offerings provided by Oracle did not run on 19c yet (they actually ran on 18c).

Unfortunately some promising 19c new features are only available on Exadata. If that’s the case (like for Automatic Indexing) then you can still test the feature on EE after setting:


SQL> alter system set "_exadata_feature_on"=TRUE scope=spfile;

and a DB-Restart.

REMARK: DO THAT ON YOUR OWN TESTSYSTEMS ONLY AND USE INTERNAL ORACLE PARAMETERS ONLY WHEN ORACLE SUPPORT RECOMMENDS TO DO SO.

Anyway, there are lots of new features and I wanted to share some interesting of them with you and provide some examples.

REMARK: You may check https://www.oracle.com/a/tech/docs/database19c-wp.pdf as well

1. Automatic Indexing (only available on EE-ES and ExaCS)

Oracle continually evaluates the executing SQL and the underlying tables to determine which indexes to automatically create and which ones to potentially remove.

Documentation:

You can use the AUTO_INDEX_MODE configuration setting to enable or disable automatic indexing in a database.

The following statement enables automatic indexing in a database and creates any new auto indexes as visible indexes, so that they can be used in SQL statements:


EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');

The following statement enables automatic indexing in a database, but creates any new auto indexes as invisible indexes, so that they cannot be used in SQL statements:


EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','REPORT ONLY');

The following statement disables automatic indexing in a database, so that no new auto indexes are created, and the existing auto indexes are disabled:


EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','OFF');

Show a report of automatic indexing activity:


set serveroutput on size unlimited lines 200 pages 200
declare
report clob := null;
begin
report := DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY();
dbms_output.put_line(report);
end;
/

In a test I ran some statements repeatedly on a table T1 (which contains 32 times the data of all_objects). The table has no index:


SQL> select * from t1 where object_id=:b1;
SQL> select * from t1 where data_object_id=:b2;

After some time indexes were created automatically:


SQL> select table_name, index_name, auto from ind;
 
TABLE_NAME INDEX_NAME AUT
-------------------------------- -------------------------------- ---
T1 SYS_AI_5mzwj826444wv YES
T1 SYS_AI_gs3pbvztmyaqx YES
 
2 rows selected.
 
SQL> select dbms_metadata.get_ddl('INDEX','SYS_AI_5mzwj826444wv') from dual;
 
DBMS_METADATA.GET_DDL('INDEX','SYS_AI_5MZWJ826444WV')
------------------------------------------------------------------------------------
CREATE INDEX "CBLEILE"."SYS_AI_5mzwj826444wv" ON "CBLEILE"."T1" ("OBJECT_ID") AUTO
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"

2. Real-Time Statistics (only available on EE-ES and ExaCS)

The database automatically gathers real-time statistics during conventional DML operations. You can see in the Note-section of dbms_xplan.display_cursor when stats used to optimize a Query were gathered during DML:


SQL> select * from table(dbms_xplan.display_cursor);
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
SQL_ID 7cd3thpuf7jxm, child number 0
-------------------------------------
 
select * from t2 where object_id=:y
 
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 24048 (100)| |
|* 1 | TABLE ACCESS FULL| T2 | 254 | 31242 | 24048 (1)| 00:00:01 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
1 - filter("OBJECT_ID"=:Y)
 
Note
-----
- dynamic statistics used: statistics for conventional DML

3. Quarantine problematic SQL (only available on EE-ES and ExaCS)

Runaway SQL statements terminated by Resource Manager due to excessive consumption of processor and I/O resources can now be automatically quarantined. I.e. instead of letting the SQL run until it reaches a resource plan limit, the SQL is not executed at all.

E.g. create a resource plan which limits SQL-exec-time for User CBLEILE to 16 seconds:


begin
-- Create a pending area
dbms_resource_manager.create_pending_area();
...
dbms_resource_manager.create_plan_directive(
plan => 'LIMIT_RESOURCE',
group_or_subplan => 'TEST_RUNAWAY_GROUP',
comment => 'Terminate SQL statements when they exceed the' ||'execution time of 16 seconds',
switch_group => 'CANCEL_SQL',
switch_time => 16,
switch_estimate => false);
...
-- Set the initial consumer group of the 'CBLEILE' user to 'TEST_RUNAWAY_GROUP'
dbms_resource_manager.set_initial_consumer_group('CBLEILE','TEST_RUNAWAY_GROUP');
end;
/

A SQL-Statement with SQL_ID 12jc0zpmb85tm executed by CBLEILE runs in the 16 seconds limit:


SQL> select count(*) X
2 from kill_cpu
3 connect by n > prior n
4 start with n = 1
5 ;
from kill_cpu
*
ERROR at line 2:
ORA-00040: active time limit exceeded - call aborted
 
Elapsed: 00:00:19.85

So I quarantine the SQL now:


set serveroutput on size unlimited
DECLARE
quarantine_config VARCHAR2(80);
BEGIN
quarantine_config := DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_ID(
SQL_ID => '12jc0zpmb85tm');
dbms_output.put_line(quarantine_config);
END;
/
 
SQL_QUARANTINE_1d93x3d6vumvs
 
PL/SQL procedure successfully completed.
 
SQL> select NAME,ELAPSED_TIME,ENABLED from dba_sql_quarantine;
 
NAME ELAPSED_TIME ENA
---------------------------------------- -------------------------------- ---
SQL_QUARANTINE_1d93x3d6vumvs ALWAYS YES

Other CBLEILE-session:


SQL> select count(*) X
2 from kill_cpu
3 connect by n > prior n
4 start with n = 1
5 ;
from kill_cpu
*
 
ERROR at line 2:
ORA-56955: quarantined plan used
Elapsed: 00:00:00.00
 
SQL> !oerr ora 56955
56955, 00000, "quarantined plan used"
// *Cause: A quarantined plan was used for this statement.
// *Action: Increase the Oracle Database Resource Manager limits or use a new plan.

–> The SQL does not run for 16 seconds, but is stopped immediately (is under quarantine). You can define the Plan-Hash-Value for which a SQL should be in quarantine and define quarantine thresholds. E.g. 20 seconds for the elapsed time. As long as the resource plan is below those 20 seconds the SQL is under quarantine. If the resource plan is defined to be above 20 seconds execution time limit, the SQL is executed.

4. Active Standby DML Redirect (only available with Active Data Guard)

On Active Data Guard you may allow moderate write activity. These writes are then transparently redirected to the primary database and written there first (to ensure consistency) and then the changes are shipped back to the standby. This approach allows applications to use the standby for moderate write workloads.

5. Hybrid Partitioned Tables

Create partitioned tables where some partitions are inside and some partitions are outside the database (on filesystem, on a Cloud-Filesystem-service or on a Hadoop Distributed File System (HDFS)). This allows e.g. “cold” partitions to remain accessible, but on cheap storage.

Here an example with 3 partitions external (data of 2016-2018) and 1 partition in the DB (data of 2019):


!mkdir -p /u01/my_data/sales_data1
!mkdir -p /u01/my_data/sales_data2
!mkdir -p /u01/my_data/sales_data3
!echo "1,1,01-01-2016,1,1,1000,2000" > /u01/my_data/sales_data1/sales2016_data.txt
!echo "2,2,01-01-2017,2,2,2000,4000" > /u01/my_data/sales_data2/sales2017_data.txt
!echo "3,3,01-01-2018,3,3,3000,6000" > /u01/my_data/sales_data3/sales2018_data.txt
 
connect / as sysdba
alter session set container=pdb1;
 
CREATE DIRECTORY sales_data1 AS '/u01/my_data/sales_data1';
GRANT READ,WRITE ON DIRECTORY sales_data1 TO cbleile;
 
CREATE DIRECTORY sales_data2 AS '/u01/my_data/sales_data2';
GRANT READ,WRITE ON DIRECTORY sales_data2 TO cbleile;
 
CREATE DIRECTORY sales_data3 AS '/u01/my_data/sales_data3';
GRANT READ,WRITE ON DIRECTORY sales_data3 TO cbleile;
 
connect cbleile/difficult_password@pdb1
 
CREATE TABLE hybrid_partition_table
( prod_id NUMBER NOT NULL,
cust_id NUMBER NOT NULL,
time_id DATE NOT NULL,
channel_id NUMBER NOT NULL,
promo_id NUMBER NOT NULL,
quantity_sold NUMBER(10,2) NOT NULL,
amount_sold NUMBER(10,2) NOT NULL
)
EXTERNAL PARTITION ATTRIBUTES (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY sales_data1
ACCESS PARAMETERS(
FIELDS TERMINATED BY ','
(prod_id,cust_id,time_id DATE 'dd-mm-yyyy',channel_id,promo_id,quantity_sold,amount_sold)
)
REJECT LIMIT UNLIMITED
)
PARTITION BY RANGE (time_id)
(
PARTITION sales_2016 VALUES LESS THAN (TO_DATE('01-01-2017','dd-mm-yyyy')) EXTERNAL
LOCATION ('sales2016_data.txt'),
PARTITION sales_2017 VALUES LESS THAN (TO_DATE('01-01-2018','dd-mm-yyyy')) EXTERNAL
DEFAULT DIRECTORY sales_data2 LOCATION ('sales2017_data.txt'),
PARTITION sales_2018 VALUES LESS THAN (TO_DATE('01-01-2019','dd-mm-yyyy')) EXTERNAL
DEFAULT DIRECTORY sales_data3 LOCATION ('sales2018_data.txt'),
PARTITION sales_2019 VALUES LESS THAN (TO_DATE('01-01-2020','dd-mm-yyyy'))
);
 
insert into hybrid_partition_table values (4,4,to_date('01-01-2019','dd-mm-yyyy'),4,4,4000,8000);
 
commit;
 
SQL> select * from hybrid_partition_table where time_id in (to_date('01-01-2017','dd-mm-yyyy'),to_date('01-01-2019','dd-mm-yyyy'));
 
PROD_ID CUST_ID TIME_ID CHANNEL_ID PROMO_ID QUANTITY_SOLD AMOUNT_SOLD
---------- ---------- --------- ---------- ---------- ------------- -----------
2 2 01-JAN-17 2 2 2000 4000
4 4 01-JAN-19 4 4 4000 8000
 
2 rows selected.
 
SQL> select * from table(dbms_xplan.display_cursor);
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
SQL_ID c5s33u5kanzb5, child number 0
-------------------------------------
select * from hybrid_partition_table where time_id in
(to_date('01-01-2017','dd-mm-yyyy'),to_date('01-01-2019','dd-mm-yyyy'))
 
Plan hash value: 2612538111
 
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 83 (100)| | | |
| 1 | PARTITION RANGE INLIST | | 246 | 21402 | 83 (0)| 00:00:01 |KEY(I) |KEY(I) |
|* 2 | TABLE ACCESS HYBRID PART FULL| HYBRID_PARTITION_TABLE | 246 | 21402 | 83 (0)| 00:00:01 |KEY(I) |KEY(I) |
|* 3 | TABLE ACCESS FULL | HYBRID_PARTITION_TABLE | | | | |KEY(I) |KEY(I) |
-------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
2 - filter((SYS_OP_XTNN("HYBRID_PARTITION_TABLE"."AMOUNT_SOLD","HYBRID_PARTITION_TABLE"."QUANTITY_SOLD","HYBRID_PARTITION_TABLE"."PROMO_ID","HYBRID_PARTITION_TABLE"."CHANNEL_ID","HYBRID_PARTITION_TABLE"."TIME_ID","HYBRID_PARTITION_TABLE"."CUST_ID","HYBRID_PARTITION_TABLE"."PROD_ID") AND INTERNAL_FUNCTION("TIME_ID")))
 
3 - filter((SYS_OP_XTNN("HYBRID_PARTITION_TABLE"."AMOUNT_SOLD","HYBRID_PARTITION_TABLE"."QUANTITY_SOLD","HYBRID_PARTITION_TABLE"."PROMO_ID","HYBRID_PARTITION_TABLE"."CHANNEL_ID","HYBRID_PARTITION_TABLE"."TIME_ID","HYBRID_PARTITION_TABLE"."CUST_ID","HYBRID_PARTITION_TABLE"."PROD_ID") AND INTERNAL_FUNCTION("TIME_ID")))

6. Memoptimized Rowstore

Enables fast data inserts into Oracle Database 19c from applications, such as Internet of Things (IoT), which ingest small, high volume transactions with a minimal amount of transactional overhead.

7. 3 PDBs per Multitenant-DB without having to pay for the Multitenant option

Beginning with 19c it is allowed to create 3 PDBs in a Container-DB without requiring the Mutitenant-Option license from Oracle. As the single- or multi-tenant DB becomes a must in Oracle 20, it is a good idea to start using the container-DB architecture with 19c already.

Please let me know your experience with Oracle 19c.

Cet article Oracle 19c est apparu en premier sur Blog dbi services.

SQL Server 2019 Accelerated Database Recovery – Instantaneous rollback and aggressive log truncation

Mon, 2019-10-07 11:37

In my previous article about Accelerated Database Recovery (ADR), I wrote mostly about the new Persistent Volume Store (PVS), how important it was important in the new SQL database engine recovery process and the potential impact it may have on the application workload. This time let’s talk a little bit more about ADR feature benefits we may get with instantaneous rollback and aggressive log truncation. These two capabilities will address some DBA pains especially when rollback or crash recovery kick in with open long running transactions.

 

First, let’s set the context by running the following long running transaction without ADR enabled:

BEGIN TRAN;
UPDATE dbo.bigTransactionHistory
SET Quantity = Quantity + 1;
GO

UPDATE dbo.bigTransactionHistory
SET Quantity = Quantity + 1;
GO
ROLLBACK TRAN;

 

The above query generates 10GB of log records (roughly 90% of the total transaction log space size) as shown below:

SELECT 
	DB_NAME(database_id) AS database_name,
	total_log_size_in_bytes / 1024 / 1024 / 1024 AS total_GB,
	used_log_space_in_bytes / 1024 / 1024 / 1024 AS used_GB,
	used_log_space_in_percent
FROM sys.dm_db_log_space_usage

 

Before cancelling my previous query to trigger a rollback operation, let’s run the following concurrent update:

BEGIN TRAN;

DECLARE @begin_date DATETIME = GETDATE();

UPDATE dbo.bigTransactionHistory
SET Quantity = Quantity + 1
Where TransactionID = 1;

DECLARE @end_date DATETIME = GETDATE();

SELECT DATEDIFF(SECOND, @begin_date, @end_date);

 

As expected, the second query is blocked during the rollback process of the first one because they compete on the same resource:

SELECT 
	spid,
	blocked,
	lastwaittype,
	waitresource,
	cmd,
	program_name
FROM sys.sysprocesses
WHERE spid IN (64, 52)

 

In my case, the second query was blocked during 135s. Regarding your scenario, it could be less or more. I experienced this annoying issue myself at some customer shops and I’m pretty sure it is the case of many of SQL Server DBAs.

Let’s now perform the same test after enabling ADR. Executing the query below (used in my first test) gave interesting results.

BEGIN TRAN;
UPDATE dbo.bigTransactionHistory
SET Quantity = Quantity + 1;
GO

UPDATE dbo.bigTransactionHistory
SET Quantity = Quantity + 1;
GO

 

First, rolling back the transaction was pretty instantaneous and the concurrent query executed faster without being blocked by ROLLBACK process. This is where the logical revert comes into play. As stated to the Microsoft documentation, when rollback is triggered all locks are released immediately. Unlike the usual recovery process, ADR uses the additional PVS is to cancel operations for identified aborted transactions by restoring the latest committed version of concerned rows. The sys.dm_tran_aborted_transactions DMV provides a picture of aborted transactions:

SELECT *
FROM sys.dm_tran_aborted_transactions;
GO

 

For a sake of curiosity, I tried to dig further into the transaction log file to compare rollback operations between usual recovery process and ADR-based recovery process. I used a simpler scenario including a simple dbo.test_adr table with one id column and that consists in insert 2 rows and updating them afterwards. To get log record data, the sys.fn_db_log function is your friend.

CREATE TABLE dbo.test_adr (
	id INT
);

CHECKPOINT;

BEGIN TRAN;

INSERT INTO dbo.test_adr ( id ) VALUES (1), (2);

UPDATE dbo.test_adr SET id = id + 1;

ROLLBACK TRAN;

SELECT 
	[Current LSN]
	,Operation
	,Context
	,[Transaction ID]
	,[Lock Information]
	,[Description]
       ,[AllocUnitName]
FROM sys.fn_dblog(NULL, NULL);

 

Without ADR, we retrieve usual log records for rollback operations including compensation records and the transaction’s end rollback mark. In such case, remind the locks are released only at the end of the rollback operation (LOP_ABORT_XACT). 

With ADR, the story is a little bit different:

Let’s precise it is only a speculation stuff from my own here and I just tried to correlate information from the Microsoft documentation. So, don’t take my word for it. When a transaction is roll backed, it is marked as aborted and tracked by the logical revert operation. The good news is that locks are immediately released afterwards. My guess is that the LOP_FORGET_XACT record corresponds to the moment when the transaction is marked as aborted and since this moment no blocking issues related to the ROLLBACK can occur. At the same time the logical revert is an asynchronous process and comes into play by providing instantaneous transaction rollback and undo for all versioned operations by using the PVS. 

 

Second, reverting to this first test scenario …

BEGIN TRAN;
UPDATE dbo.bigTransactionHistory
SET Quantity = Quantity + 1;
GO

UPDATE dbo.bigTransactionHistory
SET Quantity = Quantity + 1;
GO

 

… I noticed the transaction log file space was used differently and even less compared to my first previous test without ADR enabled. I performed the same test several times and I got results in the same order of magnitude.  

I got some clues by adding some perfmon counters:

  • SQL Server databases:PVS in-row diff generated/sec
  • SQL Server databases:PVS off-row records generated/sec
  • SQL Server databases:Percent Log Used
  • SQL Server Buffer Manager:Checkpoints/sec

My two update operations use different storage strategies for storing row versions. Indeed, in the first shot, row versions fit in the data page whereas in the second shot SQL Server must go through the off-row storage to store additional versions. In addition, we are also seeing interesting behavior of the ADR sLog component with the aggressive log truncation at the moment of the different checkpoint operations. Due to the changes in logging, only certain operations require log space and because the sLog content on every checkpoint operation it makes possible aggressive log truncation.

In my case, it led to keep under control the space used by my long running transaction even if it is in open state.

In this blog post we’ve seen how ADR may address database unavailability paint point through instantaneous rollback and aggressive log truncation. Good to know that we may get benefit from such features in SQL Server 2019!

See you!

 

 

Cet article SQL Server 2019 Accelerated Database Recovery – Instantaneous rollback and aggressive log truncation est apparu en premier sur Blog dbi services.

Documentum – Large documents in xPlore indexing

Thu, 2019-10-03 14:30

Documentum is using xPlore for the Full Text indexing/search processes. If you aren’t very familiar with how xPlore is working, you might want to know how it is possible to index large documents or you might be confused about some documents not being indexed (and therefore not searchable). In this blog, I will try to explain how xPlore can be configured to be able to index these big documents without causing too much trouble because by default, these documents are just not indexed which might be an issue. Documents tend to be bigger and bigger and therefore the thresholds for the xPlore indexing might be a little bit outdated…

In this blog, I will go through all thresholds that can be configured on the different components and I will try to explain a little bit what it’s all about. Before starting, I believe a very short (and definitively not exhaustive) introduction on the indexing process of the xPlore is therefore required. As soon as you install an IndexAgent, it will trigger the creation of several things on the associated repository, including new events registration on the ‘dmi_registry‘. When working with documents, these events (‘dm_save‘, ‘dm_saveasnew‘, …) will generate new entries in the ‘dmi_queue_item‘. The IndexAgent will then access the ‘dmi_queue_item‘ and retrieve the document that needs indexing (add/update/remove from index). Then from here a CPS is called and processing the document (language identification, text extraction, tokenization, lemmatization, stemming, …). My point here is that there are two main sides of the indexing process: the IndexAgent and then the CPS. This is also true for the thresholds: you will need to configure them properly on both sides.

 

I. IndexAgent

 
On the IndexAgent side, there isn’t much configuration possible strictly related to the size of documents since there is only one but it’s kind of the most important one since it’s the first barrier that will block your indexing if not configured properly.

In the file indexagent.xml (found under $JBOSS_HOME/server/DctmServer_Indexagent/deployments/IndexAgent.war/WEB-INF/classes), in the exporter section, you can find the parameter ‘contentSizeLimit‘. This parameter controls the maximum size of a document that can be send to indexing. This is the real size of the document (‘content_size‘/’full_content_size‘); it is not the size of its text once extracted. The reason for that is simple: this limit is on the IndexAgent size and the text hasn’t been extracted yet so the IndexAgent do not know how big the extracted text will be. If the size of the document exceeds the value defined for ‘contentSizeLimit‘, then the IndexAgent will not even try to process it, it will just reject it and in this case, you will see a message that the document exceeded the limit on both the IndexAgent logs as well as in the ‘dmi_queue_item‘ object. Other documents of the same batch aren’t impacted, the parameter ‘contentSizeLimit‘ is for each and every document. The default value for this parameter is 20 000 000 bytes (19,07 MB).

If you are going to change this value, then you might need some other updates. You can tweak some other parameters if you are seeing issues while indexing large documents, all of them can be configured inside this indexagent.xml file. For example, you might want to look at the ‘content_clean_interval‘ (in milliseconds) which controls when the export of the document (dftxml document) will be removed from the staging area of the IndexAgent (location of ‘local_content_area‘). If the value is too small, then the CPS might try to retrieve a file to process it for indexing but the IndexAgent might have removed the file already. The default value for this parameter is 1 200 000 (20 minutes).

 

II. CPS

 
On the CPS side, you can look at several other size related parameters. You can find these parameters (and many others) in two main locations. The first is global to the Federation: indexserverconfig.xml (found under $XPLORE_HOME/config by default but you can change it (E.g.: a shared location for a Multi-Node FT)). The second one is a CPS-specific configuration file: PrimaryDsearch_local_configuration.xml for a PrimaryDsearch or <CPS_Name>_configuration.xml for a CPS Only (found under $XPLORE_HOME/dsearch/cps/cps_daemon/).

The first parameter to look for is ‘max_text_threshold‘. This parameter controls the maximum text size of a document. This is the size of its text after extraction; it is not the real size of the document. If the text size of the document exceeds the value defined for ‘max_text_threshold‘, then the CPS will act according to the value defined for the ‘cut_off_text‘. With a ‘cut_off_text‘ set to true, the documents that exceed ‘max_text_threshold‘ will have the first ‘max_text_threshold‘ MB indexed but the CPS will stop once it reached the limit. In this case the CPS log will contain something like ‘doc**** is partially processed’ and the dftxml of this document will contain the mention ‘partialIndexed‘. This means that the CPS stopped at the limit defined and therefore the index might be missing some content. With a ‘cut_off_text‘ set to false (default value), the documents that exceed ‘max_text_threshold‘ will be rejected and therefore not full text indexed at all (only metadata is indexed). Other documents of the same batch aren’t impacted, the parameter ‘max_text_threshold‘ is for each and every document. The default value for this parameter is 10 485 760 bytes (10 MB) and the maximum value possible is 2 147 483 648 (2 GB).

The second parameter to look for is ‘max_data_per_process‘. This parameter controls the maximum text size that a CPS Batch should handle. The CPS is indexing documents/items per batches (‘CPS-requests-batch-size‘). By default, a CPS will process up to 5 documents per batch but, if I’m not mistaken, it can be less if there isn’t enough documents to process. If the total text size to be processed by the CPS for the complete batch is above ‘max_data_per_process‘, then the CPS will reject the full batch and it will therefore not full text index the content of any of the documents. This is going to be an issue if you increased the previous parameters but miss/forget this one. Indeed, you might end-up with very small documents not indexed because they were in a batch containing some big documents. To be sure that this parameter doesn’t block any batch, you can set it to ‘CPS-requests-batch-size‘*’max_text_threshold‘. The default value for this parameter is 31 457 280 bytes (30 MB) and the maximum value possible is 2 147 483 648 (2 GB).

As for the IndexAgent, if you are going to change these values, then you might need some other updates. There are a few timeouts values like ‘request_time_out‘ (default 600 seconds), ‘text_extraction_time_out‘ (between 60 and 300 – default to 300 seconds) or ‘linguistic_processing_time_out‘ (between 60 and 360 – default to 360 seconds) that are probably going to be exceeded if you are processing large documents so you might need to tweak these values.

 

III. Summary

 

Parameter Limit on Short Description Default Value Sample Value contentSizeLimit IndexAgent (indexagent.xml) Maximum size of document 20 000 000 bytes (19,07 MB) 104 857 600 bytes (100 MB) max_text_threshold CPS (*_configuration.xml) Maximum text size of the document’s content 10 485 760 bytes (10 MB) 41 943 040 bytes (40 MB) max_data_per_process CPS (*_configuration.xml) Maximum text size of the CPS batch 31 457 280 bytes (30 MB) 5*41 943 040 bytes = 209 715 200 (200 MB)

 
In summary, the first factor to consider is ‘contentSizeLimit‘ on the IndexAgent side. All documents with a size (document size) bigger than ‘contentSizeLimit‘ won’t be submitted to full text indexing, they will be skipped. The second factor is then either ‘max_text_threshold‘ or ‘max_data_per_process‘ or both, it depends which size you assigned them. They both rely on text size after extraction and they can both cause a document (or the batch) to be rejected from indexing.

Increasing the size thresholds is a somewhat complex exercise that needs careful thinking and alignment of numerous satellite parameters so that they can all work together without disrupting the performance or stability of the xPlore processes. These satellite parameters can be timeouts, cleanup, batch size, request size or even JVM size.

 

Cet article Documentum – Large documents in xPlore indexing est apparu en premier sur Blog dbi services.

Migrating Oracle database from windows to ODA

Tue, 2019-10-01 02:47

Nowadays I have been working on an interesting customer project where I had to migrate windows oracle standard databases to ODA. The ODAs are X7-2M Models, running version 18.5. This version is coming with Red Hat Enterprise Linx 6.10 (Santiago). Both windows databases and target ODA databases are running PSU 11.2.0.4.190115. But this would definitively also be working for oracle 12c and oracle 18c databases. The databases are licensed with Standard Edition, so migrating through data guard was not possible. Through this blog I would like to share the experience I could get on this topic as well as the method and steps I have been using to successfully migrate those databases.

Limitations

Windows and Linux platform been on the same endian, I have been initially thinking that it would not be more complicated than simply duplicating the windows database to an ODA instance using the last backup. ODA databases are OMF databases, so can not be easier, as no convert parameter is needed.
After having created a single instance database on the ODA, exported the current database pfile and adapted it for the ODA, created the needed TNS connections, I have been running a single RMAN duplicate command :

RMAN> run {
2> set newname for database to new;
3> duplicate target database to 'ODA_DBNAME' backup location '/u99/app/oracle/backup';
4> }

Note : If the database is huge, as for example, more than a Tera bytes, and your sga is small, you might want to increase it. Having a bigger sga size will lower the restore time. Minimum 50 GB would be a good compromise. Also if your ODA is from the ODA-X7 family you will benefit from the NVMe technologie. As per my experience, a duplication of 1.5 TB database, with backup stored locally, did not take more than 40 minutes.

I have been more than happy to see the first duplication step been successfully achieved :

Finished restore at 17-JUL-2019 16:45:10

And I was expecting the same for the next recovery part.

Unfortunately, this didn’t end as expected and I quickly got following restore errors :

Errors in memory script
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-01507: database not mounted
ORA-06512: at "SYS.X$DBMS_RCVMAN", line 13661
ORA-06512: at line 1
RMAN-03015: error occurred in stored script Memory Script
RMAN-20000: abnormal termination of job step
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/u03/app/oracle/fast_recovery_area/ODA_DBNAME_RZA/archivelog/2019_07_17/o1_mf_1_25514_glyf3yd3_.arc'
RMAN-11001: Oracle Error:
ORA-10562: Error occurred while applying redo to data block (file# 91, block# 189)
ORA-10564: tablespace DBVISIT
ORA-01110: data file 91: '/u02/app/oracle/oradata/ODA_DBNAME_RZA/ODA_DBNAME_RZA/datafile/o1_mf_dbvisit_glyczqcj_.dbf'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 501874
ORA-00600: internal error code, arguments: [4502], [0], [], [], [], [], [], [], [], [], [], [] RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 07/17/2019 16:45:32
RMAN-05501: aborting duplication of target database

Troubleshooting the problem I could understand that migrating database from Windows to Linux might not be so simple. Following oracle Doc ID is describing the problem :
Restore From Windows To Linux using RMAN Fails (Doc ID 2003327.1)
Cross-Platform Database Migration (across same endian) using RMAN Transportable Database (Doc ID 1401921.1)
RMAN DUPLICATE/RESTORE/RECOVER Mixed Platform Support (Doc ID 1079563.1)
Restore From Windows To Linux using RMAN Fails (Doc ID 2003327.1)

The problem is coming from the fact that recovering redo transactions between windows and linux platform is not supported if the database is not a standby one. For standard database version, the only possibility would be to go through a cold backup which, in my case, was impossible knowing the database size, the time taken to execute a backup and the short maintenance windows.

Looking for other solution and doing further tests, I could find a solution that I’m going to describe in the next steps.

Restoring the database from the last backup

In order to restore the database, I have been running next steps.

  1. Start the ODA instance in no mount :

  2. SQL> startup nomount

  3. Restore the last available control file from backup with rman :

  4. RMAN> connect target /
     
    RMAN> restore controlfile from '/mnt/backupNFS/oracle/ODA_DBNAME/20190813_233004_CTL_ODA_DBNAME_1179126808_S2864_P1.BCK';

  5. Mount the database :

  6. SQL> alter database mount;

  7. Catalog the backup path :

  8. RMAN> connect target /
     
    RMAN> catalog start with '/mnt/backupNFS/oracle/ODA_DBNAME';

  9. And finally restore the database :

  10. RMAN> connect target /
     
    RMAN> run {
    2> set newname for database to new;
    3> restore database;
    4> switch datafile all;
    5> }

Convert the primary database to a physical standby database

In order to be able to recover the database we will convert the primary database to a physical standby one.

  1. We can check the actual status and see that our database is a primary one in mounted state :

  2. SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;
     
    STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
    ------------ ---------------- ---------------- --------------------
    MOUNTED ODA_DBNAME PRIMARY MOUNTED

  3. We will convert the database to a physical standby

  4. SQL> alter database convert to physical standby;
     
    Database altered.

  5. We need to restart the database.

  6. SQL> shutdown immediate
     
    SQL> startup mount

  7. We can check new database status

  8. SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;
     
    STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
    ------------ ---------------- ---------------- --------------------
    MOUNTED ODA_DBNAME PHYSICAL STANDBY MOUNTED

Get the current windows SCN database

We are now ready to recover the database and the application can be stopped. The next steps will now be executed during the maintenance windows. The windows database listener can be stopped to make sure there is no new connection.

  1. We will make sure there is no existing application session on the database :

  2. SQL> set linesize 300
    SQL> set pagesize 500
    SQL> col machine format a20
    SQL> col service_name format a20
     
    SQL> select SID, serial#, username, machine, process, program, status, service_name, logon_time from v$session where username not in ('SYS', 'PUBLIC') and username is not null order by status, username;

  3. We will create a restore point :

  4. SQL> create restore point for_migration_14082019;
     
    Restore point created.

  5. We will get the last online log transactions archived :

  6. SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
     
    System altered.

  7. We will retrieve the SCN corresponding to the restore point :

  8. SQL> col scn format 999999999999999
     
    SQL> select scn from v$restore_point where lower(name)='for_migration_14082019';
     
    SCN
    ----------------
    13069540631

  9. We will backup the last archive log. This will be executed on the windows database using our dbi services internal DMK tool (https://www.dbi-services.com/offering/products/dmk-management-kit/) :

  10. servicedbi@win_srv:E:\app\oracle\local\dmk_custom\bin\ [ODA_DBNAME] ./rman_backup_ODA_DBNAME_arc.bat
     
    E:\app\oracle\local\dmk_custom\bin>powershell.exe -command "E:\app\oracle\local\dmk_ha\bin\check_primary.ps1 ODA_DBNAME 'dmk_rman.ps1 -s ODA_DBNAME -t bck_arc.rcv -c E:\app\oracle\admin\ODA_DBNAME\etc\rman.cfg
     
    [OK]::KSBL::RMAN::dmk_dbbackup::ODA_DBNAME::bck_arc.rcv
     
    Logfile is : E:\app\oracle\admin\ODA_DBNAME\log\ODA_DBNAME_bck_arc_20190814_141754.log
     
    RMAN return Code: 0
    2019-08-14_02:19:01::check_primary.ps1::MainProgram ::INFO ==> Program completed

Recover the database

The database can now be recovered till our 13069540631 SCN number.

  1. We will first need to catalog new archive log backups :

  2. RMAN> connect target /
     
    RMAN> catalog start with '/mnt/backupNFS/oracle/ODA_DBNAME';

  3. And recover the database till SCN 13069540632 :

  4. RMAN> connect target /
     
    RMAN> run {
    2> set until scn 13069540632;
    3> recover database;
    4> }
     
    archived log file name=/u03/app/oracle/fast_recovery_area/ODA_DBNAME_RZA/archivelog/2019_08_14/o1_mf_1_30098_go80084r_.arc RECID=30124 STAMP=1016289320
    archived log file name=/u03/app/oracle/fast_recovery_area/ODA_DBNAME_RZA/archivelog/2019_08_14/o1_mf_1_30099_go80084x_.arc thread=1 sequence=30099
    channel default: deleting archived log(s)
    archived log file name=/u03/app/oracle/fast_recovery_area/ODA_DBNAME_RZA/archivelog/2019_08_14/o1_mf_1_30099_go80084x_.arc RECID=30119 STAMP=1016289320
    archived log file name=/u03/app/oracle/fast_recovery_area/ODA_DBNAME_RZA/archivelog/2019_08_14/o1_mf_1_30100_go8008bg_.arc thread=1 sequence=30100
    channel default: deleting archived log(s)
    archived log file name=/u03/app/oracle/fast_recovery_area/ODA_DBNAME_RZA/archivelog/2019_08_14/o1_mf_1_30100_go8008bg_.arc RECID=30121 STAMP=1016289320
    media recovery complete, elapsed time: 00:00:02
    Finished recover at 14-AUG-2019 14:35:23

  5. We can check the alert log and see that recovering has been performed until SCN 13069540632 :

  6. oracle@ODA02:/u02/app/oracle/oradata/ODA_DBNAME_RZA/ODA_DBNAME_RZA/datafile/ [ODA_DBNAME] taa
    ORA-279 signalled during: alter database recover logfile '/u03/app/oracle/fast_recovery_area/ODA_DBNAME_RZA/archivelog/2019_08_14/o1_mf_1_30098_go80084r_.arc'...
    alter database recover logfile '/u03/app/oracle/fast_recovery_area/ODA_DBNAME_RZA/archivelog/2019_08_14/o1_mf_1_30099_go80084x_.arc'
    Media Recovery Log /u03/app/oracle/fast_recovery_area/ODA_DBNAME_RZA/archivelog/2019_08_14/o1_mf_1_30099_go80084x_.arc
    ORA-279 signalled during: alter database recover logfile '/u03/app/oracle/fast_recovery_area/ODA_DBNAME_RZA/archivelog/2019_08_14/o1_mf_1_30099_go80084x_.arc'...
    alter database recover logfile '/u03/app/oracle/fast_recovery_area/ODA_DBNAME_RZA/archivelog/2019_08_14/o1_mf_1_30100_go8008bg_.arc'
    Media Recovery Log /u03/app/oracle/fast_recovery_area/ODA_DBNAME_RZA/archivelog/2019_08_14/o1_mf_1_30100_go8008bg_.arc
    Wed Aug 14 14:35:23 2019
    Incomplete Recovery applied until change 13069540632 time 08/14/2019 14:13:46
    Media Recovery Complete (ODA_DBNAME)
    Completed: alter database recover logfile '/u03/app/oracle/fast_recovery_area/ODA_DBNAME_RZA/archivelog/2019_08_14/o1_mf_1_30100_go8008bg_.arc'

  7. We can check the new ODA database current SCN :

  8. SQL> col current_scn format 999999999999999
     
    SQL> select current_scn from v$database;
     
    CURRENT_SCN
    ----------------
    13069540631

Convert database to primary again

Database can now be converted back to primary.

SQL> alter database activate standby database;
 
Database altered.


SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;
 
STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
------------ ---------------- ---------------- --------------------
MOUNTED ODA_DBNAME PRIMARY MOUNTED

At this step if the windows source database would be running 11.2.0.3 version, we could successfully upgrade the new ODA database to 11.2.0.4 following common oracle database upgrade process.

And finally we can open our database and have the database been migrated from windows to linux.


SQL> alter database open;
 
Database altered.


SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;
 
STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN ODA_DBNAME PRIMARY READ WRITE


oracle@ODA02:/u02/app/oracle/oradata/ODA_DBNAME_RZA/ODA_DBNAME_RZA/datafile/ [ODA_DBNAME] ODA_DBNAME
********* dbi services Ltd. *********
STATUS : OPEN
DB_UNIQUE_NAME : ODA_DBNAME_RZA
OPEN_MODE : READ WRITE
LOG_MODE : ARCHIVELOG
DATABASE_ROLE : PRIMARY
FLASHBACK_ON : NO
FORCE_LOGGING : YES
VERSION : 11.2.0.4.0
*************************************

Post migration steps

There will be a few post migration steps to be executed.

Created redo logs again

Redo logs are still stamped with windows path and therefore have been created in $ORACLE_HOME/dbs folder. In this steps we will create new OMF one again.

  1. Checking current online log members :

  2. SQL> set linesize 300
    SQL> set pagesize 500
    SQL> col member format a100
     
    SQL> select a.GROUP#, b.member, a.status, a.bytes/1024/1024 MB from v$log a, v$logfile b where a.GROUP#=b.GROUP#;
     
    GROUP# MEMBER STATUS MB
    ---------- ---------------------------------------------------------------------------------------------------- ---------------- ----------
    6 /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/I:FAST_RECOVERY_AREAODA_DBNAME_SITE1ONLINELOGREDO_6_1.LOG UNUSED 500
    6 /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/I:FAST_RECOVERY_AREAODA_DBNAME_SITE1ONLINELOGREDO_6_2.LOG UNUSED 500
    5 /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/I:FAST_RECOVERY_AREAODA_DBNAME_SITE1ONLINELOGREDO_5_2.LOG UNUSED 500
    5 /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/I:FAST_RECOVERY_AREAODA_DBNAME_SITE1ONLINELOGREDO_5_1.LOG UNUSED 500
    4 /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/I:FAST_RECOVERY_AREAODA_DBNAME_SITE1ONLINELOGREDO_4_2.LOG UNUSED 500
    4 /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/I:FAST_RECOVERY_AREAODA_DBNAME_SITE1ONLINELOGREDO_4_1.LOG UNUSED 500
    3 /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/I:FAST_RECOVERY_AREAODA_DBNAME_SITE1ONLINELOGREDO_3_2.LOG UNUSED 500
    3 /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/I:FAST_RECOVERY_AREAODA_DBNAME_SITE1ONLINELOGREDO_3_1.LOG UNUSED 500
    2 /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/I:FAST_RECOVERY_AREAODA_DBNAME_SITE1ONLINELOGREDO_2_2.LOG UNUSED 500
    2 /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/I:FAST_RECOVERY_AREAODA_DBNAME_SITE1ONLINELOGREDO_2_1.LOG UNUSED 500
    1 /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/I:FAST_RECOVERY_AREAODA_DBNAME_SITE1ONLINELOGREDO_1_2.LOG CURRENT 500
    1 /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/I:FAST_RECOVERY_AREAODA_DBNAME_SITE1ONLINELOGREDO_1_1.LOG CURRENT 500

  3. Drop the first unused redo log group keeping only one :

  4. SQL> alter database drop logfile group 6;
     
    Database altered.
     
    SQL> alter database drop logfile group 5;
     
    Database altered.
     
    SQL> alter database drop logfile group 4;
     
    Database altered.
     
    SQL> alter database drop logfile group 3;
     
    Database altered.
     
    SQL> alter database add logfile group 3 size 500M;
     
    Database altered.

  5. Create the recent dropped group again :

  6. SQL> alter database add logfile group 3 size 500M;
     
    Database altered.
     
    SQL> alter database add logfile group 4 size 500M;
     
    Database altered.
     
    SQL> alter database add logfile group 5 size 500M;
     
    Database altered.
     
    SQL> alter database add logfile group 6 size 500M;
     
    Database altered.

  7. Drop the last unused redo log group and create it again :

  8. SQL> alter database drop logfile group 2;
     
    Database altered.
     
    SQL> alter database add logfile group 2 size 500M;
     
    Database altered.

  9. Execute a switch log file and checkpoint so the current redo group becomes unused :

  10. SQL> alter system switch logfile;
     
    System altered.
     
    SQL> alter system checkpoint;
     
    System altered.

  11. Drop it and create it again :

  12. SQL> alter database drop logfile group 1;
     
    Database altered.
     
    SQL> alter database add logfile group 1 size 500M;
     
    Database altered.

  13. Check redo group members :

  14. SQL> select a.GROUP#, b.member, a.status, a.bytes/1024/1024 MB from v$log a, v$logfile b where a.GROUP#=b.GROUP#;
     
    GROUP# MEMBER STATUS MB
    ---------- ---------------------------------------------------------------------------------------------------- ---------------- ----------
    3 /u03/app/oracle/redo/ODA_DBNAME_RZA/onlinelog/o1_mf_3_go81rj4t_.log INACTIVE 500
    3 /u02/app/oracle/oradata/ODA_DBNAME_RZA/redo/ODA_DBNAME_RZA/onlinelog/o1_mf_3_go81rjqn_.log INACTIVE 500
    4 /u03/app/oracle/redo/ODA_DBNAME_RZA/onlinelog/o1_mf_4_go81ron1_.log UNUSED 500
    4 /u02/app/oracle/oradata/ODA_DBNAME_RZA/redo/ODA_DBNAME_RZA/onlinelog/o1_mf_4_go81rp6o_.log UNUSED 500
    5 /u03/app/oracle/redo/ODA_DBNAME_RZA/onlinelog/o1_mf_5_go81rwhs_.log UNUSED 500
    5 /u02/app/oracle/oradata/ODA_DBNAME_RZA/redo/ODA_DBNAME_RZA/onlinelog/o1_mf_5_go81rx1g_.log UNUSED 500
    6 /u03/app/oracle/redo/ODA_DBNAME_RZA/onlinelog/o1_mf_6_go81s1rk_.log UNUSED 500
    6 /u02/app/oracle/oradata/ODA_DBNAME_RZA/redo/ODA_DBNAME_RZA/onlinelog/o1_mf_6_go81s2bx_.log UNUSED 500
    2 /u03/app/oracle/redo/ODA_DBNAME_RZA/onlinelog/o1_mf_2_go81sgdf_.log CURRENT 500
    2 /u02/app/oracle/oradata/ODA_DBNAME_RZA/redo/ODA_DBNAME_RZA/onlinelog/o1_mf_2_go81sgxd_.log CURRENT 500
    1 /u03/app/oracle/redo/ODA_DBNAME_RZA/onlinelog/o1_mf_1_go81vpls_.log UNUSED 500
    1 /u02/app/oracle/oradata/ODA_DBNAME_RZA/redo/ODA_DBNAME_RZA/onlinelog/o1_mf_1_go81vq4v_.log UNUSED 500

  15. Delete the wrong previous redo log members files :

  16. oracle@ODA02:/u02/app/oracle/oradata/ODA_DBNAME_RZA/ODA_DBNAME_RZA/datafile/ [ODA_DBNAME] cdh
     
    oracle@ODA02:/u01/app/oracle/product/11.2.0.4/dbhome_1/ [ODA_DBNAME] cd dbs
     
    oracle@ODA02:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/ [ODA_DBNAME] ls -ltrh *REDO*.LOG
    -rw-r----- 1 oracle asmadmin 501M Aug 14 14:59 I:FAST_RECOVERY_AREAODA_DBNAME_SITE1ONLINELOGREDO_6_2.LOG
    -rw-r----- 1 oracle asmadmin 501M Aug 14 14:59 I:FAST_RECOVERY_AREAODA_DBNAME_SITE1ONLINELOGREDO_6_1.LOG
    -rw-r----- 1 oracle asmadmin 501M Aug 14 14:59 I:FAST_RECOVERY_AREAODA_DBNAME_SITE1ONLINELOGREDO_5_2.LOG
    -rw-r----- 1 oracle asmadmin 501M Aug 14 14:59 I:FAST_RECOVERY_AREAODA_DBNAME_SITE1ONLINELOGREDO_5_1.LOG
    -rw-r----- 1 oracle asmadmin 501M Aug 14 14:59 I:FAST_RECOVERY_AREAODA_DBNAME_SITE1ONLINELOGREDO_4_2.LOG
    -rw-r----- 1 oracle asmadmin 501M Aug 14 14:59 I:FAST_RECOVERY_AREAODA_DBNAME_SITE1ONLINELOGREDO_4_1.LOG
    -rw-r----- 1 oracle asmadmin 501M Aug 14 14:59 I:FAST_RECOVERY_AREAODA_DBNAME_SITE1ONLINELOGREDO_3_2.LOG
    -rw-r----- 1 oracle asmadmin 501M Aug 14 14:59 I:FAST_RECOVERY_AREAODA_DBNAME_SITE1ONLINELOGREDO_3_1.LOG
    -rw-r----- 1 oracle asmadmin 501M Aug 14 14:59 I:FAST_RECOVERY_AREAODA_DBNAME_SITE1ONLINELOGREDO_2_2.LOG
    -rw-r----- 1 oracle asmadmin 501M Aug 14 14:59 I:FAST_RECOVERY_AREAODA_DBNAME_SITE1ONLINELOGREDO_2_1.LOG
    -rw-r----- 1 oracle asmadmin 501M Aug 14 15:05 I:FAST_RECOVERY_AREAODA_DBNAME_SITE1ONLINELOGREDO_1_2.LOG
    -rw-r----- 1 oracle asmadmin 501M Aug 14 15:05 I:FAST_RECOVERY_AREAODA_DBNAME_SITE1ONLINELOGREDO_1_1.LOG
     
    oracle@ODA02:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/ [ODA_DBNAME] rm *REDO*.LOG

Created temp file again
  1. Checking current temp file we can see that the path is still the windows one :

  2. SQL> set linesize 300
    SQL> col name format a100
     
    SQL> select b.name, b.status, b.bytes/1024/1024 MB, a.name from v$tablespace a, v$tempfile b where a.TS#=b.TS#;
     
    NAME STATUS MB NAME
    ---------------------------------------------------------------------------------------------------- ------- ---------- -------------------------------------------
    F:\ORADATA\ODA_DBNAME\TEMPORARY_DATA_1.DBF ONLINE 8192 TEMPORARY_DATA

  3. We can check that the default temporary tablespace is TEMPORARY_DATA

  4. SQL> col property_value format a50
     
    SQL> select property_name, property_value from database_properties where property_name like '%DEFAULT%TABLESPACE%';
     
    PROPERTY_NAME PROPERTY_VALUE
    ------------------------------ --------------------------------------------------
    DEFAULT_TEMP_TABLESPACE TEMPORARY_DATA
    DEFAULT_PERMANENT_TABLESPACE USER_DATA

  5. Let’s create a new temp tablespace and make it the default one

  6. SQL> create temporary tablespace TEMP tempfile size 8G;
     
    Tablespace created.
     
    SQL> alter database default temporary tablespace TEMP;
     
    Database altered.
     
    SQL> select property_name, property_value from database_properties where property_name like '%DEFAULT%TABLESPACE%';
     
    PROPERTY_NAME PROPERTY_VALUE
    ------------------------------ --------------------------------------------------
    DEFAULT_TEMP_TABLESPACE TEMP
    DEFAULT_PERMANENT_TABLESPACE USER_DATA

  7. Drop previous TEMPORARY_DATA tablespace

  8. SQL> drop tablespace TEMPORARY_DATA including contents and datafiles;
     
    Tablespace dropped.
     
    SQL> select b.file#, b.name, b.status, b.bytes/1024/1024 MB, a.name from v$tablespace a, v$tempfile b where a.TS#=b.TS#;
     
    FILE# NAME STATUS MB NAME
    ---------- ---------------------------------------------------------------------------------------------------- ------- ----------
    3 /u02/app/oracle/oradata/ODA_DBNAME_RZA/ODA_DBNAME_RZA/datafile/o1_mf_temp_go83m1tp_.tmp ONLINE 8192 TEMP

  9. Create TEMPORARY_DATA tablespace again and make it the default one :

  10. SQL> create temporary tablespace TEMPORARY_DATA tempfile size 8G;
     
    Tablespace created.
     
    SQL> select b.file#, b.name, b.status, b.bytes/1024/1024 MB, a.name from v$tablespace a, v$tempfile b where a.TS#=b.TS#;
     
    FILE# NAME STATUS MB NAME
    ---------- ---------------------------------------------------------------------------------------------------- ------- ----------
    1 /u02/app/oracle/oradata/ODA_DBNAME_RZA/ODA_DBNAME_RZA/datafile/o1_mf_temporar_go83wfd7_.tmp ONLINE 8192 TEMPORARY_DATA
    3 /u02/app/oracle/oradata/ODA_DBNAME_RZA/ODA_DBNAME_RZA/datafile/o1_mf_temp_go83m1tp_.tmp ONLINE 8192 TEMP
     
    SQL> alter database default temporary tablespace TEMPORARY_DATA;
     
    Database altered.
     
    SQL> select property_name, property_value from database_properties where property_name like '%DEFAULT%TABLESPACE%';
     
    PROPERTY_NAME PROPERTY_VALUE
    ------------------------------ --------------------------------------------------
    DEFAULT_TEMP_TABLESPACE TEMPORARY_DATA
    DEFAULT_PERMANENT_TABLESPACE USER_DATA

  11. And finally drop the intermediare temp tablespace :

  12. SQL> drop tablespace TEMP including contents and datafiles;
     
    Tablespace dropped.
     
    SQL> select b.file#, b.name, b.status, b.bytes/1024/1024 MB, a.name from v$tablespace a, v$tempfile b where a.TS#=b.TS#;
     
    FILE# NAME STATUS MB NAME
    ---------- ---------------------------------------------------------------------------------------------------- ------- ----------
    1 /u02/app/oracle/oradata/ODA_DBNAME_RZA/ODA_DBNAME_RZA/datafile/o1_mf_temporar_go83wfd7_.tmp ONLINE 8192 TEMPORARY_DATA

  13. Appropriate max size can be given to the new created temp tablespace

  14. SQL> alter database tempfile '/u02/app/oracle/oradata/ODA_DBNAME_RZA/ODA_DBNAME_RZA/datafile/o1_mf_temporar_go83wfd7_.tmp' autoextend on maxsize 31G;
     
    Database altered.

  15. Remove wrong temp file stored in $ORACLE_HOME/dbs

  16. oracle@ODA02:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/ [ODA_DBNAME] ls -ltr
    -rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora
    -rw-r--r-- 1 oracle oinstall 64 Jul 25 08:10 initODA_DBNAME.ora.old
    -rw-r----- 1 oracle oinstall 2048 Jul 25 08:10 orapwODA_DBNAME
    -rw-r--r-- 1 oracle oinstall 67 Jul 25 08:31 initODA_DBNAME.ora
    -rw-r----- 1 oracle asmadmin 8589942784 Aug 14 08:14 F:ORADATAODA_DBNAMETEMPORARY_DATA_1.DBF
    -rw-rw---- 1 oracle asmadmin 1544 Aug 14 14:59 hc_ODA_DBNAME.dat
    -rw-r----- 1 oracle asmadmin 43466752 Aug 14 15:48 snapcf_ODA_DBNAME.f
     
    oracle@RZA-ODA02:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/ [ODA_DBNAME] rm F:ORADATAODA_DBNAMETEMPORARY_DATA_1.DBF

Apply specific ODA parameters

Following specific ODA parameters can be updated to the new created instance.


SQL> alter system set "_datafile_write_errors_crash_instance"=false scope=spfile;
 
System altered.
 
SQL> alter system set "_db_writer_coalesce_area_size"=16777216 scope=spfile;
 
System altered.
 
SQL> alter system set "_disable_interface_checking"=TRUE scope=spfile;
 
System altered.
 
SQL> alter system set "_ENABLE_NUMA_SUPPORT"=FALSE scope=spfile;
 
System altered.
 
SQL> alter system set "_FILE_SIZE_INCREASE_INCREMENT"=2143289344 scope=spfile;
 
System altered.
 
SQL> alter system set "_gc_policy_time"=0 scope=spfile;
 
System altered.
 
SQL> alter system set "_gc_undo_affinity"=FALSE scope=spfile;
 
System altered.
 
SQL> alter system set db_block_checking='FULL' scope=spfile;
 
System altered.
 
SQL> alter system set db_block_checksum='FULL' scope=spfile;
 
System altered.
 
SQL> alter system set db_lost_write_protect='TYPICAL' scope=spfile;
 
System altered.
 
SQL> alter system set sql92_security=TRUE scope=spfile;
 
System altered.
 
SQL> alter system set use_large_pages='only' scope=spfile;
 
System altered.

“_fix_control”parameter is specific to Oracle12c and not compatible Oracle 11g. See Doc ID 2145105.1.

Register database in grid

After applying specific ODA instance parameters, we can register the database in the grid and start it with the grid.


oracle@ODA02:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/ [ODA_DBNAME] srvctl add database -d ODA_DBNAME_RZA -o /u01/app/oracle/product/11.2.0.4/dbhome_1 -c SINGLE -i ODA_DBNAME -x RZA-ODA02 -m ksbl.local -p /u02/app/oracle/oradata/ODA_DBNAME_RZA/dbs/spfileODA_DBNAME.ora -r PRIMARY -s OPEN -t IMMEDIATE -n ODA_DBNAME -j "/u02/app/oracle/oradata/ODA_DBNAME_RZA,/u03/app/oracle"
 
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
 
oracle@ODA02:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/ [ODA_DBNAME] srvctl start database -d ODA_DBNAME_RZA
 
oracle@ODA02:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/ [ODA_DBNAME] srvctl status database -d ODA_DBNAME_RZA
Instance ODA_DBNAME is running on node rza-oda02
 
oracle@ODA02:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/ [ODA_DBNAME] ODA_DBNAME
********* dbi services Ltd. *********
STATUS : OPEN
DB_UNIQUE_NAME : ODA_DBNAME_RZA
OPEN_MODE : READ WRITE
LOG_MODE : ARCHIVELOG
DATABASE_ROLE : PRIMARY
FLASHBACK_ON : NO
FORCE_LOGGING : YES
VERSION : 11.2.0.4.0
*************************************

We can check the well functionning :

oracle@ODA02:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/ [ODA_DBNAME] srvctl stop database -d ODA_DBNAME_RZA
 
oracle@ODA02:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/ [ODA_DBNAME] srvctl status database -d ODA_DBNAME_RZA
Instance ODA_DBNAME is not running on node rza-oda02
 
oracle@ODA02:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/ [ODA_DBNAME] ODA_DBNAME
********* dbi services Ltd. *********
STATUS : STOPPED
*************************************
 
oracle@ODA02:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/ [ODA_DBNAME] srvctl start database -d ODA_DBNAME_RZA
 
oracle@ODA02:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/ [ODA_DBNAME] srvctl status database -d ODA_DBNAME_RZA
Instance ODA_DBNAME is running on node rza-oda02
 
oracle@ODA02:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/ [ODA_DBNAME] ODA_DBNAME
********* dbi services Ltd. *********
STATUS : OPEN
DB_UNIQUE_NAME : ODA_DBNAME_RZA
OPEN_MODE : READ WRITE
LOG_MODE : ARCHIVELOG
DATABASE_ROLE : PRIMARY
FLASHBACK_ON : NO
FORCE_LOGGING : YES
VERSION : 11.2.0.4.0
*************************************

Conclusion

Going through a physical standby database, I was able to migrate successfully the windows databases into ODA linux one. I have been able to achieve migration of source 11.2.0.4 databases but also 11.2.0.3 database by adding an upgrade step in the process.

Cet article Migrating Oracle database from windows to ODA est apparu en premier sur Blog dbi services.

Using non-root SQL Server containers on Docker and K8s

Sun, 2019-09-29 11:54

This is something that I waited for a while, in fact since SQL Server 2017 … and the news came out on Wednesday 09th September 2019. Running Non-Root SQL Server Containers is now possible either on the next version of SQL Server (2019) and it has been backported on SQL Server 2017 as well. Non-root SQL Server containers will likely be part of hidden gem of SQL Server new features, but this is definitely a good news for me because it will facilitate the transition of SQL Server containers on production from a security standpoint.

At this stage, no need to precise why it is not a best practice to run SQL Server containers or more generally speaking applications with root privileges within a container. For further information, I invite you to take a look at the different threats implied by a such configuration with your google-fu. 

Let’s start with docker environments. First, Microsoft provides a Docker file to build an image either for SQL Server 2017 and SQL Server 2019. We may notice the Docker file is already based on a SQL Server docker image and performs some extra configuration for non-root privilege capabilities. I put here the interesting part:

# Exmple of creating a SQL Server 2019 container image that will run as a user 'mssql' instead of root
# This is example is based on the official image from Microsoft and effectively changes the user that SQL Server runs as
# and allows for dumps to generate as a non-root user


FROM mcr.microsoft.com/mssql/server:2019-latest

# Create non-root user and update permissions
#
RUN useradd -M -s /bin/bash -u 10001 -g 0 mssql
RUN mkdir -p -m 770 /var/opt/mssql && chgrp -R 0 /var/opt/mssql

# Grant sql the permissions to connect to ports <1024 as a non-root user
#
RUN setcap 'cap_net_bind_service+ep' /opt/mssql/bin/sqlservr

# Allow dumps from the non-root process
# 
RUN setcap 'cap_sys_ptrace+ep' /opt/mssql/bin/paldumper
RUN setcap 'cap_sys_ptrace+ep' /usr/bin/gdb

# Add an ldconfig file because setcap causes the os to remove LD_LIBRARY_PATH
# and other env variables that control dynamic linking
#
RUN mkdir -p /etc/ld.so.conf.d && touch /etc/ld.so.conf.d/mssql.conf
RUN echo -e "# mssql libs\n/opt/mssql/lib" >> /etc/ld.so.conf.d/mssql.conf
RUN ldconfig

USER mssql
CMD ["/opt/mssql/bin/sqlservr"]

 

Note the different sections where the mssql user is created and is used when running the image. So, the new image specification implies running the sqlservr process using this mssql user as shown below:

$ docker exec -ti sql19 top

 

The user process is well identified by its name because it is already defined in the /etc/password file within the container namespace:

$ docker exec -ti sql19 cat /etc/passwd | grep mssql
mssql:x:10001:0::/home/mssql:/bin/bash

 

Let’s go ahead and let’s talk about persisting SQL Server database files on an external storage. In this case, we need to refer to the Microsoft documentation to configure volumes and underlying storage permissions regarding the scenario we will have to deal with.

If you don’t specify any user (and group) when spinning up the container, the sqlservr process will run with the identity of the mssql user created inside the container and as part of the root group. The underlying host filesystem must be configured accordingly, either a user with same UID = 10001 or the root group GUID = 0). Otherwise chances are you will experience permission issues with the following error message:

SQL Server 2019 will run as non-root by default.
This container is running as user mssql.
To learn more visit https://go.microsoft.com/fwlink/?linkid=2099216.
/opt/mssql/bin/sqlservr: Error: Directory [/var/opt/mssql/system/] could not be created.  Errno [13]

 

If you want to run the container as part of a custom user and group created on your own, you must be aware of the different database file placement scenarios. The first one consists in using the default configuration with all the SQL Server logs, data and transaction log files in /var/opt/mssql path. In this case, your custom user UID and GUID can be part of the security context of the hierarchy folder on the host as follows:

$ ls -l | grep sqlserver
drwxrwx---. 6 mssql mssql 59 Sep 27 19:08 sqlserver

$ id mssql
uid=1100(mssql) gid=1100(mssql) groups=1100(mssql),100(users)

 

The docker command below specifies the UID and GUID of my custom user through the -u parameter:

docker run -d \
 --name sql19 \
 -u $(id -u mssql):$(id -g mssql) \
 -e "MSSQL_PID=Developer" \
 -e "ACCEPT_EULA=Y" \
 -e "SA_PASSWORD=Password1" \
 -e "MSSQL_AGENT_ENABLED=True" \
 -e "MSSQL_LCID=1033" \
 -e "MSSQL_MEMORY_LIMIT_MB=2048" \
 -v "/u00/sqlserver:/var/opt/mssql" \
 -p 1451:1433 -d 2019-latest-non-root

 

Note the username is missing and replaced by the UID of the mssql user created on my own.

This is a normal behavior because my user is not known within the container namespace. There is no record from my user with UID = 1001. The system only knows the mssql user with UID = 10001 as shown below:

I have no name!@e698c3db2180:/$ whoami
whoami: cannot find name for user ID 1100

$ cat /etc/passwd | grep mssql | cut -d":" -f 1,3
mssql:10001

 

For a sake of curiosity, we may wonder how SQL Server makes the choice of using the correct user for the sqlservr process. Indeed, I created two users with the same name but with different UIDs and I think that after some investigations, taking a look at the uid_entry point definition in the microsoft/mssql-docker github project could help understanding this behavior:

If we don’t specify the UID / GUID during the container’s creation, the whoami command will fail and the mssql user’s UID defined in the Dockerfile (cf. USER mssql) will be chosen.

 The second scenario consists in introducing some SQL Server best practices in terms of database file placement. In a previous blog post, I wrote about a possible implementation based on a flexible architecture for SQL Server on Linux and which may fit with containers. In this case, database files will be stored outside of the /var/opt/mssql default path and in this case, the non-root container has the restriction that it must run as part of the root group as mentioned in the Microsoft documentation:

The non-root container has the restriction that it must run as part of the root group unless a volume is mounted to '/var/opt/mssql' that the non-root user can access. The root group doesn’t grant any extra root permissions to the non-root user.

 

Here my implementation of the flexible architecture template with required Linux permissions in my context:

$ ls -ld /u[0-9]*/sql*2/
drwxrwx---. 2 mssql root    6 Sep 24 22:02 /u00/sqlserver2/
drwxrwx---. 2 mssql root 4096 Sep 27 14:20 /u01/sqlserverdata2/
drwxrwx---. 2 mssql root   25 Sep 27 14:20 /u02/sqlserverlog2/
drwxrwx---. 2 mssql root    6 Sep 24 22:04 /u03/sqlservertempdb2/
drwxrwx---. 2 mssql root    6 Sep 27 10:09 /u98/sqlserver2/

 

… with:

  • /u00/sqlserver2 (binaries structure that will contain remaining files in /var/opt/mssql path)
  • /u01/sqlserverdata2 (data files including user, system and tempdb databases)
  • /u02/sqlserverlog2 (transaction log files)
  • /u98/sqlserver2 (database backups)

And accordingly, my docker command and parameters to start my SQL Server container that will sit on my flexible architecture:

docker run -d \
 --name sql19 \
 -u $(id -u mssql):0 \
 -e "MSSQL_PID=Developer" \
 -e "ACCEPT_EULA=Y" \
 -e "SA_PASSWORD=Password1" \
 -e "MSSQL_AGENT_ENABLED=True" \
 -e "MSSQL_LCID=1033" \
 -e "MSSQL_MEMORY_LIMIT_MB=2048" \
 -e "MSSQL_MASTER_DATA_FILE=/u01/sqlserverdata/master.mdf" \
 -e "MSSQL_MASTER_LOG_FILE=/u02/sqlserverlog/mastlog.ldf" \
 -e "MSSQL_DATA_DIR=/u01/sqlserverdata" \
 -e "MSSQL_LOG_DIR=/u02/sqlserverlog" \
 -e "MSSQL_BACKUP_DIR=/u98/sqlserver" \
 -v "/u00/sqlserver2:/var/opt/mssql" \
 -v "/u01/sqlserverdata2:/u01/sqlserverdata" \
 -v "/u02/sqlserverlog2:/u02/sqlserverlog" \
 -v "/u98/sqlserver2:/u98/sqlserver" \
 -p 1451:1433 -d 2019-latest-non-root

 

The mssql user created on my own from the host (with UID = 1100) is used by the sqlservr process:

The system and user database files are placed according to my specification:

master> create database test;
Commands completed successfully.
Time: 0.956s
master> \n ldd %%
+--------+----------------+---------------------------------+-----------+
| DB     | logical_name   | physical_name                   | size_MB   |
|--------+----------------+---------------------------------+-----------|
| master | master         | /u01/sqlserverdata/master.mdf   | 71        |
| master | mastlog        | /u02/sqlserverlog/mastlog.ldf   | 32        |
| tempdb | tempdev        | /u01/sqlserverdata/tempdb.mdf   | 128       |
| tempdb | templog        | /u01/sqlserverdata/templog.ldf  | 128       |
| tempdb | tempdev2       | /u01/sqlserverdata/tempdb2.ndf  | 128       |
| tempdb | tempdev3       | /u01/sqlserverdata/tempdb3.ndf  | 128       |
| tempdb | tempdev4       | /u01/sqlserverdata/tempdb4.ndf  | 128       |
| model  | modeldev       | /u01/sqlserverdata/model.mdf    | 128       |
| model  | modellog       | /u01/sqlserverdata/modellog.ldf | 128       |
| msdb   | MSDBData       | /u01/sqlserverdata/MSDBData.mdf | 236       |
| msdb   | MSDBLog        | /u01/sqlserverdata/MSDBLog.ldf  | 12        |
| test   | test           | /u01/sqlserverdata/test.mdf     | 128       |
| test   | test_log       | /u02/sqlserverlog/test_log.ldf  | 128       |
+--------+----------------+---------------------------------+-----------+

 

I may correlate the above output with corresponding files persisted on underlying storage according to my flexible architecture specification:

$ sudo ls -lR /u[0-9]*/sqlserver*2/
/u00/sqlserver2/:
total 4
drwxrwx---. 2 mssql root 4096 Sep 28 17:39 log
drwxr-xr-x. 2 mssql root   25 Sep 28 17:39 secrets

/u00/sqlserver2/log:
total 428
-rw-r-----. 1 mssql root  10855 Sep 28 17:39 errorlog
-rw-r-----. 1 mssql root  10856 Sep 28 17:37 errorlog.1
-rw-r-----. 1 mssql root      0 Sep 28 17:37 errorlog.2
-rw-r-----. 1 mssql root  77824 Sep 28 17:37 HkEngineEventFile_0_132141586653320000.xel
-rw-r-----. 1 mssql root  77824 Sep 28 17:39 HkEngineEventFile_0_132141587692350000.xel
-rw-r-----. 1 mssql root   2560 Sep 28 17:39 log_1.trc
-rw-r-----. 1 mssql root   2560 Sep 28 17:37 log.trc
-rw-r-----. 1 mssql root   6746 Sep 28 17:37 sqlagent.1
-rw-r-----. 1 mssql root   6746 Sep 28 17:39 sqlagent.out
-rw-r-----. 1 mssql root    114 Sep 28 17:39 sqlagentstartup.log
-rw-r-----. 1 mssql root 106496 Sep 28 17:37 system_health_0_132141586661720000.xel
-rw-r-----. 1 mssql root 122880 Sep 28 17:41 system_health_0_132141587698940000.xel

/u00/sqlserver2/secrets:
total 4
-rw-------. 1 mssql root 44 Sep 28 17:39 machine-key

/u01/sqlserverdata2/:
total 105220
-rw-r-----. 1 mssql root      256 Sep 27 14:20 Entropy.bin
-rw-r-----. 1 mssql root  4653056 Sep 28 17:39 master.mdf
-rw-r-----. 1 mssql root  8388608 Sep 28 17:39 modellog.ldf
-rw-r-----. 1 mssql root  8388608 Sep 28 17:39 model.mdf
-rw-r-----. 1 mssql root 14024704 Sep 27 14:20 model_msdbdata.mdf
-rw-r-----. 1 mssql root   524288 Sep 27 14:20 model_msdblog.ldf
-rw-r-----. 1 mssql root   524288 Sep 27 14:20 model_replicatedmaster.ldf
-rw-r-----. 1 mssql root  4653056 Sep 27 14:20 model_replicatedmaster.mdf
-rw-r-----. 1 mssql root 15466496 Sep 28 17:39 msdbdata.mdf
-rw-r-----. 1 mssql root   786432 Sep 28 17:39 msdblog.ldf
-rw-r-----. 1 mssql root  8388608 Sep 28 17:39 tempdb2.ndf
-rw-r-----. 1 mssql root  8388608 Sep 28 17:39 tempdb3.ndf
-rw-r-----. 1 mssql root  8388608 Sep 28 17:39 tempdb4.ndf
-rw-r-----. 1 mssql root  8388608 Sep 28 17:39 tempdb.mdf
-rw-r-----. 1 mssql root  8388608 Sep 28 17:39 templog.ldf
-rw-r-----. 1 mssql root  8388608 Sep 28 17:39 test.mdf

/u02/sqlserverlog2/:
total 10240
-rw-r-----. 1 mssql root 2097152 Sep 28 17:39 mastlog.ldf
-rw-r-----. 1 mssql root 8388608 Sep 28 17:39 test_log.ldf

/u03/sqlservertempdb2/:
total 0

/u98/sqlserver2/:
total 0

 

What next? Because in production your containers will run on the top of orchestrator like Kubernetes, the question is how to implement such privilege restriction in this context? Kubernetes provides security context at different levels including pod and containers. In this blog post example, I applied the security context at the container level within the container specification.  

Let’s set the context. Here the picture of my K8s environment:

$ kubectl get nodes
NAME                     STATUS   ROLES    AGE   VERSION
k8m.dbi-services.test    Ready    master   97d   v1.14.1
k8n1.dbi-services.test   Ready    <none>   97d   v1.14.1
k8n2.dbi-services.test   Ready    <none>   97d   v1.14.1

 

I used the new local-storage Storage class (available with K8s v.1.14+):

$ kubectl get sc
NAME            PROVISIONER                    AGE
local-storage   kubernetes.io/no-provisioner   4d

$ kubectl describe sc local-storage
Name:                  local-storage
IsDefaultClass:        No
Annotations:           <none>
Provisioner:           kubernetes.io/no-provisioner
Parameters:            <none>
AllowVolumeExpansion:  <unset>
MountOptions:          <none>
ReclaimPolicy:         Delete
VolumeBindingMode:     WaitForFirstConsumer
Events:                <none>

 

I configured a persistent volume based on this local-storage class and that pointing to the /mnt/local-storage on my K81n node. The access mode and Retain policy are configured according to meet the best practices for databases.

$ cat StoragePV.yaml
apiVersion: v1
kind: PersistentVolume
metadata:
  name: my-local-pv
spec:
  capacity:
    storage: 5Gi
  accessModes:
  - ReadWriteOnce
  persistentVolumeReclaimPolicy: Retain
  volumeMode: Filesystem
  storageClassName: local-storage
  local:
    path: /mnt/localstorage
  nodeAffinity:
    required:
      nodeSelectorTerms:
      - matchExpressions:
        - key: kubernetes.io/hostname
          operator: In
          values:
          - k8n1.dbi-services.test

 

For a sake of simplicity, I applied the default configuration with all SQL Server related files related stored in /var/opt/mssql. I configured the underlying storage and folder permissions accordingly with my custom mssql user (UID = 10001) and group (GUID = 10001) created on the K8n1 host. Note that the UID matches with that of the mssql user created within the container.

$ hostname
k8n1.dbi-services.test

$ id mssql
uid=10001(mssql) gid=10001(mssql) groups=10001(mssql)

$ ls -ld /mnt/localstorage/
drwxrwx--- 6 mssql mssql 59 Sep 26 20:57 /mnt/localstorage/

 

My deployment file is as follows. It includes the security context that specifies a non-root container configuration with my custom user’s UID / GUID created previously (runAsUser and runAsGroup parameters):

$ cat ReplicaSet.yaml
apiVersion: apps/v1beta1
kind: Deployment
metadata:
  name: mssql-deployment-2
spec:
  replicas: 1
  template:
    metadata:
      labels:
        app: mssql-2
    spec:
      securityContext:
        runAsUser: 10001
        runAsGroup: 10001
      terminationGracePeriodSeconds: 10
      containers:
      - name: mssql-2
        image: trow.kube-public:31000/2019-latest-non-root
        ports:
        - containerPort: 1433
        env:
        - name: MSSQL_PID
          value: "Developer"
        - name: ACCEPT_EULA
          value: "Y"
        - name: MSSQL_SA_PASSWORD
          valueFrom:
            secretKeyRef:
              name: sql-secrets
              key: sapassword
        volumeMounts:
        - name: mssqldb
          mountPath: /var/opt/mssql
      volumes:
      - name: mssqldb
        persistentVolumeClaim:
          claimName: mssql-data-2

 

Obviously, if you don’t meet the correct security permissions on the underlying persistent volume, you will get an error when provisioning the MSSQL pod because the sqlservr process will not get the privileges to create or to access the SQL Server related files as shown below:

$ kubectl get pod
NAME                                 READY   STATUS   RESTARTS   AGE
mssql-deployment-2-8b4d7f7b7-x4x8w   0/1     Error    2          30s

$ kubectl logs mssql-deployment-2-8b4d7f7b7-x4x8w
SQL Server 2019 will run as non-root by default.
This container is running as user mssql.
To learn more visit https://go.microsoft.com/fwlink/?linkid=2099216.
/opt/mssql/bin/sqlservr: Error: Directory [/var/opt/mssql/system/] could not be created.  Errno [13]

 

If well configured, everything should work as expected and your container should run and interacts correctly with the corresponding persistent volume in the security context defined in your YAML specification:

All this stuff applies to SQL Server 2017.

See you!

 

 

 

 

 

 

Cet article Using non-root SQL Server containers on Docker and K8s est apparu en premier sur Blog dbi services.

Pages