Yann Neuhaus
pgconf.eu 2025 – RECAP
I was fortunate to be able to attend at the pgconf.eu 2025.
This year event was happening in RIGA and joined together once again key members of the community, contributors, committers, sponsors and users from across the world.
I would summarize this year event with those three main topics : AI/LLM – PG18- Monitoring.
Compared to last year the formula changed a bit regarding the Community events day of Tuesday where for the first time different “Summits” where organized. If you want full details on the event and the schedule as well as the presentation slides of each talk you may find it here : Schedule — PostgreSQL Conference Europe 2025
I had the chance to be chosen as a speaker for the AI Summit. It was quite interesting for me. In total there was 13 short talks (10min) on various topics related to PostgreSQL and AI/LLMs it was dense with a lot of interesting ideas of implementations – you can find the details and slides here PGConf.EU 2025 PostgreSQL AI Summit – PostgreSQL wiki. AI/LLMs are the hot topic of the moment and naturally it came up often during this event, in the talks and in the discussions. You can find the pdf of my presentation here. I explained a business case implementation of a BI self-service agentic RAG to find relevant fields for a target KPI and data marts creation as output. Since the talks were short, it allowed to have a debate at the end between the audience and the speakers. The discussion nicely moderated by organizers was interesting because it exposed the same strong thoughts people have in general about AI/LLMs. A blend of distrust and not fully understanding of what it is about or how it could help organizations. Which, in itself, shows that the PostgreSQL community has the same difficulties at explaining technical challenges versus organizational/human challenges. My view here is that we don’t have technical challenges, they are almost un-relevant to most arguments but rather human relation and understanding of what values a DBA for example, brings to the organization. To me installing and configuring PostgreSQL has no benefits in terms of personal growth so automating it is quite natural and adding AI/LLMs on top is “nice to have” but not fundamentally different than an Ansible playbook. But for the junior DBA this an additional abstraction that can be dangerous because it provides tools that users can’t grasp the full extent of their consequences. This outlines that the main issue of integrating AI/LLMs workflows is more a governance/ C-management issue than a technical one and it can’t be the last excuse for adding to the technological debt.
Jay Miller from Aiven explained how you can fail at exposing PII from LLMs and MCPs. This is rely a relevant topic knowing that more and more organization are facing issues like shadow IT. He also was quite the show host and was funny to hear. I recommend strongly watching the recording when it will be released.
This year was just after the PostgreSQL 18 version release which is one the version that brought major improvements and is initiating changes for future release to come. I was quite enthusiast to listen to Melanie Plagemen on how she worked on the improvements on freezing in this release. I have to say, usually when I am going at an advanced internal talk, I am more confused after than before. But here, Melanie did an amazing job at talking about a technical complex topic without loosing the audience.
Gülçin Yıldırım Jelínek, on her side explained what’s new in PG18 about constraints like NOT ENFORCED and NOT NULL and how to use them. The COO of Cybertec Raj Verma, during a sponsor talk, explained why compliance matters and how to minimize the risks and how PostgreSQL is helping us to be PCI DSS, GDPR, nLPD or HIPAA compliant.
Another interesting talk I was happy to attend was from Floor Drees and Gabriele Bartolini. they explain how they went on joining the CloudNativePG project to the CNCF.
Monitoring
This leads me to another important topic, I wasn’t looking for it but became a bit of a main subject for my over the years as a DBA that was interested in performance tuning. Monitoring on PostgreSQL was introduced by several talks like Luigi Nardi and his idea of workload fingerprint with the DBtune tool they have. Additionally, Lukas Fittl presented pg_stat_plans, an extension which aims at tracking execution plans over time. This is definitely something I am going to try and will push for implementation in the core extensions if not the core code itself.
The reason for that is obvious for me, PostgreSQL is becoming more and more central to enterprise organizations and appart from subject like TDE, monitoring is going to become a key aspect of automation, CloudNativePG and AI/LLM workflows. Having PostgreSQL being able to be monitored better and easier at the core will allow leveraging at all this levels. Cloud companies release that already hence there involvement in similar projects.
In the end, this year was once again the occasion for me to think about many relevant topics and exchange with PostgreSQL hackers as well as users from around the world. I came back home with the head full of ideas to investigate.
Additionally after the conference the videos of the each talks will be uploaded to the pgconf Europe Youtube channel : PostgreSQL Europe, but you can already check previous amazing talks and this year pgday Paris.
So once again the PostgreSQL flag was floating up high !
L’article pgconf.eu 2025 – RECAP est apparu en premier sur dbi Blog.
Alfresco – When a JDBC driver breaks after years of stability
A customer recently contacted us regarding an issue with an old Alfresco Enterprise 4.2.2 environment. The system had been running stably for years, with a nightly restart of the Tomcat/Alfresco service to help recycle the memory and fix long-standing issues in this version. Earlier this week, one of these nightly restarts suddenly failed and since then, Alfresco would completely refuse to start. Unfortunately, that was on Production, so it was rather important! Yes, it is a very old version of Alfresco (on an old OS as well, obviously), but the customer doesn’t want to upgrade or patch it, so it was left as is since its installation by another company that isn’t around anymore.
The errorWhen reviewing the Tomcat logs, the startup sequence looked normal until Alfresco attempted to initialize Hibernate and connect to its MySQL database. Here is the relevant part of the log with the error:
oct. 22, 2025 5:45:37 AM org.apache.catalina.core.AprLifecycleListener lifecycleEvent
INFOS: Loaded APR based Apache Tomcat Native library 1.1.33 using APR version 1.4.6.
oct. 22, 2025 5:45:37 AM org.apache.catalina.core.AprLifecycleListener lifecycleEvent
INFOS: APR capabilities: IPv6 [true], sendfile [true], accept filters [false], random [true].
oct. 22, 2025 5:45:37 AM org.apache.catalina.core.AprLifecycleListener initializeSSL
INFOS: OpenSSL successfully initialized (OpenSSL 0.9.8y 5 Feb 2013)
oct. 22, 2025 5:45:37 AM org.apache.coyote.AbstractProtocol init
INFOS: Initializing ProtocolHandler ["http-apr-8080"]
oct. 22, 2025 5:45:37 AM org.apache.coyote.AbstractProtocol init
INFOS: Initializing ProtocolHandler ["ajp-apr-8009"]
oct. 22, 2025 5:45:37 AM org.apache.coyote.AbstractProtocol init
INFOS: Initializing ProtocolHandler ["http-bio-8443"]
oct. 22, 2025 5:45:37 AM org.apache.catalina.startup.Catalina load
INFOS: Initialization processed in 995 ms
...
2025-10-22 05:46:15,118 INFO [alfresco.repo.admin] [localhost-startStop-1] Using database URL 'jdbc:mysql://localhost:3306/alfresco?autoReconnect=true' with user 'alfresco'.
2025-10-22 05:46:15,491 INFO [alfresco.repo.admin] [localhost-startStop-1] Connected to database MySQL version 5.6.28-log
2025-10-22 05:46:21,836 INFO [management.subsystems.ChildApplicationContextFactory] [localhost-startStop-1] Starting 'sysAdmin' subsystem, ID: [sysAdmin, default]
2025-10-22 05:46:21,863 INFO [management.subsystems.ChildApplicationContextFactory] [localhost-startStop-1] Startup of 'sysAdmin' subsystem, ID: [sysAdmin, default] complete
2025-10-22 05:46:21,902 INFO [domain.schema.SchemaBootstrap] [localhost-startStop-1] Ignoring script patch (post-Hibernate): patch.db-V4.2-metadata-query-indexes
2025-10-22 05:46:31,210 WARN [hibernate.cfg.SettingsFactory] [localhost-startStop-1] Could not obtain connection metadata
org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (java.lang.NullPointerException)
at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1549)
at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1388)
at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
at org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider.getConnection(LocalDataSourceConnectionProvider.java:81)
at org.hibernate.cfg.SettingsFactory.buildSettings(SettingsFactory.java:84)
at org.hibernate.cfg.Configuration.buildSettings(Configuration.java:2079)
at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1304)
at org.springframework.orm.hibernate3.LocalSessionFactoryBean.newSessionFactory(LocalSessionFactoryBean.java:860)
at org.springframework.orm.hibernate3.LocalSessionFactoryBean.buildSessionFactory(LocalSessionFactoryBean.java:779)
at org.springframework.orm.hibernate3.AbstractSessionFactoryBean.afterPropertiesSet(AbstractSessionFactoryBean.java:211)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1477)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1417)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:519)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:456)
at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:291)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:288)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:190)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:563)
at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:895)
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:425)
at org.springframework.web.context.ContextLoader.createWebApplicationContext(ContextLoader.java:276)
at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:197)
at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:47)
at org.alfresco.web.app.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:63)
at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:5068)
at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5584)
at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:147)
at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:899)
at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:875)
at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:652)
at org.apache.catalina.startup.HostConfig.deployWAR(HostConfig.java:1092)
at org.apache.catalina.startup.HostConfig$DeployWar.run(HostConfig.java:1984)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
at java.util.concurrent.FutureTask.run(FutureTask.java:262)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLException: java.lang.NullPointerException
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
at com.mysql.jdbc.ConnectionImpl.buildCollationMapping(ConnectionImpl.java:1023)
at com.mysql.jdbc.ConnectionImpl.initializePropsFromServer(ConnectionImpl.java:3451)
at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2443)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2213)
at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:797)
at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:389)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:305)
at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38)
at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:582)
at org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1556)
at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1545)
... 37 more
Caused by: java.lang.NullPointerException
at java.util.TreeMap.put(TreeMap.java:556)
at com.mysql.jdbc.Util.resultSetToMap(Util.java:506)
at com.mysql.jdbc.ConnectionImpl.buildCollationMapping(ConnectionImpl.java:964)
... 53 more
2025-10-22 05:46:31,597 INFO [management.subsystems.ChildApplicationContextFactory] [localhost-startStop-1] Stopping 'sysAdmin' subsystem, ID: [sysAdmin, default]
2025-10-22 05:46:31,597 INFO [management.subsystems.ChildApplicationContextFactory] [localhost-startStop-1] Stopped 'sysAdmin' subsystem, ID: [sysAdmin, default]
2025-10-22 05:46:31,607 ERROR [web.context.ContextLoader] [localhost-startStop-1] Context initialization failed
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'extranetDialectResourceLoader' defined in class path resource [alfresco/extension/context/typo3-context.xml]: Cannot create inner bean 'org.springframework.beans.factory.config.PropertyPathFactoryBean#70397d85' of type [org.springframework.beans.factory.config.PropertyPathFactoryBean] while setting bean property 'dialectClass'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'org.springframework.beans.factory.config.PropertyPathFactoryBean#70397d85' defined in class path resource [alfresco/extension/context/typo3-context.xml]: Initialization of bean failed; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'extranetDialect': FactoryBean threw exception on object creation; nested exception is org.hibernate.exception.GenericJDBCException: Cannot open connection
at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveInnerBean(BeanDefinitionValueResolver.java:281)
at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveValueIfNecessary(BeanDefinitionValueResolver.java:120)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.applyPropertyValues(AbstractAutowireCapableBeanFactory.java:1325)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1086)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:517)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:456)
at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:291)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:288)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:190)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:580)
at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:895)
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:425)
at org.springframework.web.context.ContextLoader.createWebApplicationContext(ContextLoader.java:276)
at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:197)
at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:47)
at org.alfresco.web.app.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:63)
at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:5068)
at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5584)
at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:147)
at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:899)
at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:875)
at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:652)
at org.apache.catalina.startup.HostConfig.deployWAR(HostConfig.java:1092)
at org.apache.catalina.startup.HostConfig$DeployWar.run(HostConfig.java:1984)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
at java.util.concurrent.FutureTask.run(FutureTask.java:262)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'org.springframework.beans.factory.config.PropertyPathFactoryBean#70397d85' defined in class path resource [alfresco/extension/context/typo3-context.xml]: Initialization of bean failed; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'extranetDialect': FactoryBean threw exception on object creation; nested exception is org.hibernate.exception.GenericJDBCException: Cannot open connection
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:527)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:456)
at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveInnerBean(BeanDefinitionValueResolver.java:270)
... 29 more
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'extranetDialect': FactoryBean threw exception on object creation; nested exception is org.hibernate.exception.GenericJDBCException: Cannot open connection
at org.springframework.beans.factory.support.FactoryBeanRegistrySupport.doGetObjectFromFactoryBean(FactoryBeanRegistrySupport.java:149)
at org.springframework.beans.factory.support.FactoryBeanRegistrySupport.getObjectFromFactoryBean(FactoryBeanRegistrySupport.java:102)
at org.springframework.beans.factory.support.AbstractBeanFactory.getObjectForBeanInstance(AbstractBeanFactory.java:1429)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:245)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:190)
at org.springframework.beans.factory.config.PropertyPathFactoryBean.setBeanFactory(PropertyPathFactoryBean.java:186)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeAwareMethods(AbstractAutowireCapableBeanFactory.java:1439)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1408)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:519)
... 31 more
Caused by: org.hibernate.exception.GenericJDBCException: Cannot open connection
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:29)
at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:426)
at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:144)
at org.hibernate.jdbc.BorrowedConnectionProxy.invoke(BorrowedConnectionProxy.java:150)
at com.sun.proxy.$Proxy5.setAutoCommit(Unknown Source)
at org.alfresco.hibernate.DialectFactoryBean.getObject(DialectFactoryBean.java:67)
at org.alfresco.hibernate.DialectFactoryBean.getObject(DialectFactoryBean.java:39)
at org.springframework.beans.factory.support.FactoryBeanRegistrySupport.doGetObjectFromFactoryBean(FactoryBeanRegistrySupport.java:142)
... 39 more
Caused by: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (java.lang.NullPointerException)
at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1549)
at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1388)
at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
at org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider.getConnection(LocalDataSourceConnectionProvider.java:81)
at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:423)
... 45 more
Caused by: java.sql.SQLException: java.lang.NullPointerException
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
at com.mysql.jdbc.ConnectionImpl.buildCollationMapping(ConnectionImpl.java:1023)
at com.mysql.jdbc.ConnectionImpl.initializePropsFromServer(ConnectionImpl.java:3451)
at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2443)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2213)
at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:797)
at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
at sun.reflect.GeneratedConstructorAccessor103.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:389)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:305)
at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38)
at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:582)
at org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1556)
at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1545)
... 49 more
Caused by: java.lang.NullPointerException
at java.util.TreeMap.put(TreeMap.java:556)
at com.mysql.jdbc.Util.resultSetToMap(Util.java:506)
at com.mysql.jdbc.ConnectionImpl.buildCollationMapping(ConnectionImpl.java:964)
... 64 more
oct. 22, 2025 5:46:31 AM org.apache.catalina.core.StandardContext listenerStart
GRAVE: Exception lors de l'envoi de l'évènement contexte initialisé (context initialized) à l'instance de classe d'écoute (listener) org.alfresco.web.app.ContextLoaderListener
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'extranetDialectResourceLoader' defined in class path resource [alfresco/extension/context/typo3-context.xml]: Cannot create inner bean 'org.springframework.beans.factory.config.PropertyPathFactoryBean#70397d85' of type [org.springframework.beans.factory.config.PropertyPathFactoryBean] while setting bean property 'dialectClass'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'org.springframework.beans.factory.config.PropertyPathFactoryBean#70397d85' defined in class path resource [alfresco/extension/context/typo3-context.xml]: Initialization of bean failed; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'extranetDialect': FactoryBean threw exception on object creation; nested exception is org.hibernate.exception.GenericJDBCException: Cannot open connection
at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveInnerBean(BeanDefinitionValueResolver.java:281)
at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveValueIfNecessary(BeanDefinitionValueResolver.java:120)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.applyPropertyValues(AbstractAutowireCapableBeanFactory.java:1325)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1086)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:517)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:456)
at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:291)
...
The key clue here appears to be the NullPointerException from the buildCollationMapping(), which probably indicates a problem during the JDBC driver’s initialization of MySQL collation data.
DB & JDBC Connector DetailsGiven that the error appeared to be linked to the DB, I checked that it was properly running and responding, while also retrieving its version:
mysql> SELECT VERSION();
+------------+
| VERSION() |
+------------+
| 5.6.28-log |
+------------+
1 row in set (0.01 sec)
Everything looked fine, and the collation list was consistent with no apparent missing data and no “null” values:
mysql> SHOW COLLATION;
+---------------------+----------+-----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+---------------------+----------+-----+---------+----------+---------+
| big5_chinese_ci | big5 | 1 | Yes | Yes | 1 |
| big5_bin | big5 | 84 | | Yes | 1 |
| dec8_swedish_ci | dec8 | 3 | Yes | Yes | 1 |
| dec8_bin | dec8 | 69 | | Yes | 1 |
...
Another important thing to check is the JDBC Connector used by Tomcat to connect to the database (as it’s not using the mysql binaries to connect, as I did above):
[alfresco@alf01 ~]$ cd $CATALINA_HOME
[alfresco@alf01 tomcat]$
[alfresco@alf01 tomcat]$ ls -l lib/mysql-*
-rw-r--r--. 1 alfresco alfresco 802721 Jan 21 2015 lib/mysql-connector-java-5.1.20-bin.jar
[alfresco@alf01 tomcat]$
The MySQL JDBC Connector version 5.1.20 was released in May 2012. Looking into known MySQL Connector/J bugs, it looked like there were several problems affecting it, like long-running MySQL instances, some related to the buildCollationMapping, etc… The MySQL DB of the environment was running for the past 2.5 years without any restart. Therefore, the first thing we suggested was simply to restart the DB. Unfortunately, it didn’t help.
Patching the JDBC ConnectorSince patching/upgrading Alfresco and the DB was out of the question, the only easy thing we could try, as a next step, was to patch the JDBC Connector. Looking at the MySQL download website for that component (c.f. here), the latest minor version available is 5.1.49, released in April 2020. Therefore, I tried to download and extract it and then I stopped the (not-really) running Tomcat process:
[alfresco@alf01 tomcat]$ wget "https://cdn.mysql.com/archives/mysql-connector-java-5.1/mysql-connector-java-5.1.49.zip"
--2025-10-22 09:43:47-- https://cdn.mysql.com/archives/mysql-connector-java-5.1/mysql-connector-java-5.1.49.zip
Resolving cdn.mysql.com... 2.19.75.100, 2001:918:ffcb:1a6::1d68, 2001:918:ffcb:181::1d68
Connecting to cdn.mysql.com|2.19.75.100|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 3722067 (3.5M) [application/zip]
Saving to: “mysql-connector-java-5.1.49.zip”
100%[============================================================>] 3,722,067 19.1M/s in 0.2s
2025-10-22 09:43:48 (19.1 MB/s) - “mysql-connector-java-5.1.49.zip” saved [3722067/3722067]
[alfresco@alf01 tomcat]$
[alfresco@alf01 tomcat]$ unzip mysql-connector-java-5.1.49.zip
Archive: mysql-connector-java-5.1.49.zip
creating: mysql-connector-java-5.1.49/
creating: mysql-connector-java-5.1.49/src/
creating: mysql-connector-java-5.1.49/src/com/
...
inflating: mysql-connector-java-5.1.49/src/testsuite/ssl-test-certs/server-cert.pem
inflating: mysql-connector-java-5.1.49/src/testsuite/ssl-test-certs/server-key.pem
[alfresco@alf01 tomcat]$
[alfresco@alf01 tomcat]$ ../alfresco.sh stop tomcat
**
** Alfresco has been stopped successfully
**
[alfresco@alf01 tomcat]$
The final step was to backup the current and new versions of the JARs. Then I replaced the file in the Tomcat lib folder before restarting the process:
[alfresco@alf01 tomcat]$ ls -l lib/mysql-*
-rw-r--r--. 1 alfresco alfresco 802721 Jan 21 2015 lib/mysql-connector-java-5.1.20-bin.jar
[alfresco@alf01 tomcat]$
[alfresco@alf01 tomcat]$ mv lib/mysql-connector-java-5.1.20-bin.jar ./
[alfresco@alf01 tomcat]$ mv mysql-connector-java-5.1.49/mysql-connector-java-5.1.49.jar ./
[alfresco@alf01 tomcat]$ cp -p mysql-connector-java-5.1.49.jar lib/
[alfresco@alf01 tomcat]$ rm -rf mysql-connector-java-5.1.49.zip mysql-connector-java-5.1.49
[alfresco@alf01 tomcat]$
[alfresco@alf01 tomcat]$ ls -l lib/mysql-*
-rw-r--r--. 1 alfresco alfresco 1006904 Apr 20 2020 lib/mysql-connector-java-5.1.49.jar
[alfresco@alf01 tomcat]$
[alfresco@alf01 tomcat]$ ../alfresco.sh start tomcat
**
** Alfresco has been started
**
[alfresco@alf01 tomcat]$
Once done, the Tomcat logs looked much better, there were no errors related to database connection anymore. Alfresco was able to finish its startup and users were able to log in to Alfresco Share again:
oct. 22, 2025 8:27:35 AM org.apache.catalina.core.AprLifecycleListener lifecycleEvent
INFOS: Loaded APR based Apache Tomcat Native library 1.1.33 using APR version 1.4.6.
oct. 22, 2025 8:27:35 AM org.apache.catalina.core.AprLifecycleListener lifecycleEvent
INFOS: APR capabilities: IPv6 [true], sendfile [true], accept filters [false], random [true].
oct. 22, 2025 8:27:35 AM org.apache.catalina.core.AprLifecycleListener initializeSSL
INFOS: OpenSSL successfully initialized (OpenSSL 0.9.8y 5 Feb 2013)
oct. 22, 2025 8:27:35 AM org.apache.coyote.AbstractProtocol init
INFOS: Initializing ProtocolHandler ["http-apr-8080"]
oct. 22, 2025 8:27:35 AM org.apache.coyote.AbstractProtocol init
INFOS: Initializing ProtocolHandler ["ajp-apr-8009"]
oct. 22, 2025 8:27:35 AM org.apache.coyote.AbstractProtocol init
INFOS: Initializing ProtocolHandler ["http-bio-8443"]
oct. 22, 2025 8:27:35 AM org.apache.catalina.startup.Catalina load
INFOS: Initialization processed in 989 ms
...
2025-10-22 08:28:16,988 INFO [alfresco.repo.admin] [localhost-startStop-1] Using database URL 'jdbc:mysql://localhost:3306/alfresco?autoReconnect=true' with user 'alfresco'.
2025-10-22 08:28:17,300 INFO [alfresco.repo.admin] [localhost-startStop-1] Connected to database MySQL version 5.6.28-log
2025-10-22 08:28:23,797 INFO [management.subsystems.ChildApplicationContextFactory] [localhost-startStop-1] Starting 'sysAdmin' subsystem, ID: [sysAdmin, default]
2025-10-22 08:28:23,822 INFO [management.subsystems.ChildApplicationContextFactory] [localhost-startStop-1] Startup of 'sysAdmin' subsystem, ID: [sysAdmin, default] complete
2025-10-22 08:28:23,859 INFO [domain.schema.SchemaBootstrap] [localhost-startStop-1] Ignoring script patch (post-Hibernate): patch.db-V4.2-metadata-query-indexes
Wed Oct 22 08:28:33 CEST 2025 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Wed Oct 22 08:28:33 CEST 2025 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
SETTING APPLICATION CONTEXT
2025-10-22 08:28:33,976 INFO [extensions.webscripts.TemplateProcessorRegistry] [localhost-startStop-1] Registered template processor Repository Template Processor for extension ftl
2025-10-22 08:28:33,982 INFO [extensions.webscripts.ScriptProcessorRegistry] [localhost-startStop-1] Registered script processor Repository Script Processor for extension js
2025-10-22 08:28:40,396 INFO [domain.schema.SchemaBootstrap] [localhost-startStop-1] Connecting to database: jdbc:mysql://localhost:3306/alfresco?autoReconnect=true, UserName=alfresco@localhost, MySQL Connector Java
2025-10-22 08:28:40,396 INFO [domain.schema.SchemaBootstrap] [localhost-startStop-1] Schema managed by database dialect org.hibernate.dialect.MySQLInnoDBDialect.
2025-10-22 08:28:41,104 INFO [domain.schema.SchemaBootstrap] [localhost-startStop-1] No changes were made to the schema.
2025-10-22 08:28:42,093 INFO [management.subsystems.ChildApplicationContextFactory] [localhost-startStop-1] Starting 'Authentication' subsystem, ID: [Authentication, managed, alfinst]
2025-10-22 08:28:42,545 INFO [management.subsystems.ChildApplicationContextFactory] [localhost-startStop-1] Startup of 'Authentication' subsystem, ID: [Authentication, managed, alfinst] complete
...
oct. 22, 2025 8:30:09 AM org.apache.coyote.AbstractProtocol start
INFOS: Starting ProtocolHandler ["http-apr-8080"]
oct. 22, 2025 8:30:09 AM org.apache.coyote.AbstractProtocol start
INFOS: Starting ProtocolHandler ["ajp-apr-8009"]
oct. 22, 2025 8:30:09 AM org.apache.coyote.AbstractProtocol start
INFOS: Starting ProtocolHandler ["http-bio-8443"]
oct. 22, 2025 8:30:09 AM org.apache.catalina.startup.Catalina start
INFOS: Server startup in 153447 ms
2025-10-22 08:30:10,121 INFO [web.site.EditionInterceptor] [ajp-apr-8009-exec-7] Successfully retrieved license information from Alfresco.
2025-10-22 08:30:39,780 INFO [web.scripts.ImapServerStatus] [ajp-apr-8009-exec-3] Successfully retrieved IMAP server status from Alfresco: disabled
2025-10-22 08:30:40,107 INFO [web.scripts.SyncModeConfig] [ajp-apr-8009-exec-3] Successfully retrieved Sync Mode configuration from Alfresco: ON_PREMISE
This issue was pretty interesting, as it shows that even if you do not change anything on a system running for more than 10 years (same DB/Alfresco/Tomcat/Java/JDBC Connector versions), and that restarts daily, you can still face bugs at any time… I can’t check all details of the bugs while I’m writing this blog as the MySQL bugs website is horribly slow and crashing today, but clearly this shows that even if you’re maintaining old platforms that can’t be upgraded, it’s still worth keeping small components like JDBC connectors up to date.
L’article Alfresco – When a JDBC driver breaks after years of stability est apparu en premier sur dbi Blog.
Dctm – D2-DAR installation fails because of workflow trackers
In a Documentum upgrade project from 20.x to 23.4 earlier this year, I faced an issue which prevented the installation/upgrade of D2. The problem was located inside the D2-DAR while it was trying to update the workflow trackers, which failed because of an ORA-01427 error. The exact error displayed was this one:
[emc.installer] dmbasic output : [DM_SERVER_E_EXEC_SQL]error: "The execsql method failed due to a database error: ORA-01427: single-row subquery returns more than one row"
Before that happened, I successfully upgraded 7 or 8 environments from 20.x to 23.4, for that customer, and after that, I did around 20 more. However, this issue only happened once, so not sure what exactly was wrong, and the Application/Business Teams couldn’t find what really caused the initial issue (inside the DB) either… In any cases, to start my investigation, I tried to quickly compare the generated logs for the D2-DAR installation on a working vs non-working Repository.
Working Repository logs:
###
## Working repo:
###
[dmadmin@cs-0 DARsInternal]$ cat dar-deploy-D2-DAR.dar-REPO1-20250703-133024.log
[INFO] ******************************************************
[INFO] * Headless Composer
[INFO] * Version: 23.4.0000.0120
[INFO] * Java version: 17.0.13 (64bit)
[INFO] * Java home: $JAVA_HOME
[INFO] * Set storage type: false
[INFO] *
[INFO] * DAR file: $DM_HOME/install/DARsInternal/D2-DAR.dar
[INFO] * Project name: D2-DAR
[INFO] * Built by Composer: 23.4.0000.0027
[INFO] *
...
[INFO] dmbasic output : Finished creating required indexes on D2 types.
[INFO] dmbasic output : Copied workflow_id in to d2c_workflow_tracker object
[INFO] dmbasic output : Copied process_id in to d2c_workflow_tracker object
[INFO] dmbasic output : Copied supervisor_name in to d2c_workflow_tracker object
[INFO] dmbasic output : Adding d2_digital_sign_tracker_user user to the group d2_digital_signature_tracker
[INFO] dmbasic output : Fetched the group d2_digital_signature_tracker
[INFO] dmbasic output : Check for 'Public searches' folder to migrate.
[INFO] dmbasic output : Folder '/Resources/D2/[MIGRATE] Public searches' not found. Do not migrate.
[INFO] dmbasic output : Disconnected from the server.
[INFO] Finished executing post-install script Thu Jul 03 13:32:13 UTC 2025
[INFO] Project 'D2-DAR' was successfully installed.
[dmadmin@cs-0 DARsInternal]$
Non-working Repository logs:
###
## Non working repo:
###
[dmadmin@cs-0 DARsInternal]$ cat dar-deploy-D2-DAR.dar-REPO2-20250704-144219.log
[INFO] ******************************************************
[INFO] * Headless Composer
[INFO] * Version: 23.4.0000.0120
[INFO] * Java version: 17.0.13 (64bit)
[INFO] * Java home: $JAVA_HOME
[INFO] * Set storage type: false
[INFO] *
[INFO] * DAR file: $DM_HOME/install/DARsInternal/D2-DAR.dar
[INFO] * Project name: D2-DAR
[INFO] * Built by Composer: 23.4.0000.0027
[INFO] *
...
[INFO] dmbasic output : Finished creating required indexes on D2 types.
[INFO] dmbasic output : Copied workflow_id in to d2c_workflow_tracker object
[INFO] dmbasic output : [DM_SERVER_E_EXEC_SQL]error: "The execsql method failed due to a database error: ORA-01427: single-row subquery returns more than one row"
[INFO] dmbasic output :
[INFO] dmbasic output : [DM_TYPE_MGR_E_INDEX_ALREADY_EXISTS]error: "The index being created on type d2_recently_used_profile already exists. The existing index is represented by dmi_index object with id 1f012345800005a0."
[INFO] dmbasic output :
[INFO] dmbasic output : [DM_TYPE_MGR_E_INDEX_ALREADY_EXISTS]error: "The index being created on type d2_recently_used_profile already exists. The existing index is represented by dmi_index object with id 1f0123458000059f."
[INFO] dmbasic output :
[INFO] dmbasic output :
[INFO] dmbasic output : Exiting with dmExit(-1)
[ERROR] Procedure execution failed with dmbasic exit value : 255
[INFO] D2-DAR install failed.
[ERROR] Unable to install dar file $DM_HOME/install/DARsInternal/D2-DAR.dar
com.emc.ide.installer.PostInstallException: Error running post-install procedure "PostInstall". Please contact the procedure owner to verify if it is functioning properly.
at internal.com.emc.ide.installer.DarInstaller.postInstall(DarInstaller.java:1574)
at internal.com.emc.ide.installer.DarInstaller.doInstall(DarInstaller.java:669)
...
Caused by: com.emc.ide.external.dfc.procedurerunner.ProcedureRunnerException: Procedure execution failed with dmbasic exit value : 255
at com.emc.ide.external.dfc.procedurerunner.ProcedureRunnerUtils.executeDmBasic(ProcedureRunnerUtils.java:285)
at com.emc.ide.external.dfc.procedurerunner.ProcedureRunner.execute(ProcedureRunner.java:55)
...
[ERROR] Failed to install DAR
Unable to install dar file $DM_HOME/install/DARsInternal/D2-DAR.dar
at com.emc.ant.installer.api.InstallerAntTask.installDar(InstallerAntTask.java:273)
at com.emc.ant.installer.api.InstallerAntTask.execute(InstallerAntTask.java:135)
...
Caused by: com.emc.ide.installer.PostInstallException: Error running post-install procedure "PostInstall". Please contact the procedure owner to verify if it is functioning properly.
at internal.com.emc.ide.installer.DarInstaller.postInstall(DarInstaller.java:1574)
at internal.com.emc.ide.installer.DarInstaller.doInstall(DarInstaller.java:669)
...
Caused by: com.emc.ide.external.dfc.procedurerunner.ProcedureRunnerException: Procedure execution failed with dmbasic exit value : 255
at com.emc.ide.external.dfc.procedurerunner.ProcedureRunnerUtils.executeDmBasic(ProcedureRunnerUtils.java:285)
at com.emc.ide.external.dfc.procedurerunner.ProcedureRunner.execute(ProcedureRunner.java:55)
at internal.com.emc.ide.installer.DarInstaller.postInstall(DarInstaller.java:1570)
... 42 more
As often with DAR installation failures, there aren’t a lot of information. It’s clear that the installation failed because there is apparently some wrong data inside the DB (a query that should return only 1 row is giving more results), but there isn’t much more details expect that. By comparing the 2 different logs, you can understand that the issue is located between these 2 lines:
[INFO] dmbasic output : Copied workflow_id in to d2c_workflow_tracker object
[INFO] dmbasic output : Copied process_id in to d2c_workflow_tracker object
Therefore, finding the root cause is essentially finding what is being done between these 2 lines. A DAR is essentially a zip (Documentum Archive), so you can just extract it and look at its content to see what is being done. In this case (D2 23.4), I could find the necessary source code inside the file named “D2-DAR/bin/content/55/-1569930955/runnableContent.crtext“. Here is an extract of that file:
Print "Copied workflow_id in to d2c_workflow_tracker object"
sQuery = "update d2c_workflow_tracker_s set process_id = " & _
"(select child_id from dm_relation_s where dm_relation_s.parent_id = " & _
"d2c_workflow_tracker_s.r_object_id and dm_relation_s.relation_name = " & _
"'D2_WF_TRACKER_TEMPLATE') where (process_id IS NULL or process_id = '0000000000000000') " & _
" and exists (select child_id from dm_relation_s where dm_relation_s.parent_id = " & _
"d2c_workflow_tracker_s.r_object_id and dm_relation_s.relation_name = 'D2_WF_TRACKER_TEMPLATE')"
bRet = dmAPIExec("execsql," & sess & "," & sQuery)
if (not bRet) then
Call Err_Handle(sess, "0")
Print "Exiting with dmExit(-1)"
dmExit(-1)
end if
Print "Copied process_id in to d2c_workflow_tracker object"
Therefore, the query that is causing the issue is the one referenced above as “sQuery”, i.e. this one, transformed into plain SQL:
update d2c_workflow_tracker_s
set process_id=(select child_id from dm_relation_s
where dm_relation_s.parent_id=d2c_workflow_tracker_s.r_object_id
and dm_relation_s.relation_name='D2_WF_TRACKER_TEMPLATE')
where (process_id IS NULL or process_id='0000000000000000')
and exists (select child_id from dm_relation_s
where dm_relation_s.parent_id=d2c_workflow_tracker_s.r_object_id
and dm_relation_s.relation_name='D2_WF_TRACKER_TEMPLATE');
And indeed, executing this query produces the same result:
SQL> update d2c_workflow_tracker_s
set process_id=(select child_id from dm_relation_s
where dm_relation_s.parent_id=d2c_workflow_tracker_s.r_object_id
and dm_relation_s.relation_name='D2_WF_TRACKER_TEMPLATE')
where (process_id IS NULL or process_id='0000000000000000')
and exists (select child_id from dm_relation_s
where dm_relation_s.parent_id=d2c_workflow_tracker_s.r_object_id
and dm_relation_s.relation_name='D2_WF_TRACKER_TEMPLATE');
update d2c_workflow_tracker_s set process_id=(select child_id from dm_relation_s where dm_relation_s.parent_id=d2c_workflow_tracker_s.r_object_id and dm_relation_s.relation_name='D2_WF_TRACKER_TEMPLATE') where (process_id IS NULL or process_id='0000000000000000') and exists (select child_id from dm_relation_s where dm_relation_s.parent_id=d2c_workflow_tracker_s.r_object_id and dm_relation_s.relation_name='D2_WF_TRACKER_TEMPLATE')
*
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row
Unfortunately, because of the way the query is formed (mapping all d2c_workflow_tracker_s.process_id 1-by-1 from the dm_relation_s.child_id), you cannot just execute the sub-query (whatever is present between the parenthesis):
SQL> select child_id from dm_relation_s
where dm_relation_s.parent_id=d2c_workflow_tracker_s.r_object_id
and dm_relation_s.relation_name='D2_WF_TRACKER_TEMPLATE';
select child_id from dm_relation_s where dm_relation_s.parent_id=d2c_workflow_tracker_s.r_object_id and dm_relation_s.relation_name='D2_WF_TRACKER_TEMPLATE'
*
ERROR at line 1:
ORA-00904: "D2C_WORKFLOW_TRACKER_S"."R_OBJECT_ID": invalid identifier
Even if you “correct” it (by adding the 2nd table name in the “from”), then it will just display something that isn’t directly usable:
SQL> select child_id from dm_relation_s, d2c_workflow_tracker_s
where dm_relation_s.parent_id=d2c_workflow_tracker_s.r_object_id
and dm_relation_s.relation_name='D2_WF_TRACKER_TEMPLATE';
CHILD_ID
----------------
4b01234580044d0f
4b01234580057910
4b01234580002572
...
4b01234580057910
4b01234580057910
4b01234580311d91
1406 rows selected.
What to do then? Well, you need to go further. There are 1406 rows from the above query (number of dm_process being tracked or to be more accurate, the number of relations between a Workflow Tracker (parent_id) and its dm_process (child_id)). Assuming that the error is legit, then that would probably mean that there could be less Workflow Trackers than that number. Therefore, after a bit of reflection, I executed the following query to try to find all Workflow Trackers and the count of relations that each and every one of them has. Under normal conditions, each Workflow Tracker should have a single relation (meaning a single dm_process (1-by-1 mapping)) but that wasn’t the case:
SQL> select d2c_workflow_tracker_s.r_object_id, count(dm_relation_s.parent_id)
from d2c_workflow_tracker_s, dm_relation_s
where dm_relation_s.parent_id=d2c_workflow_tracker_s.r_object_id
and dm_relation_s.relation_name='D2_WF_TRACKER_TEMPLATE'
group by d2c_workflow_tracker_s.r_object_id
order by 2;
R_OBJECT_ID COUNT(DM_RELATION_S.PARENT_ID)
---------------- ------------------------------
09012345800df7eb 1
09012345800df7ed 1
...
0901234580313e31 1
090123458004fdde 2
090123458005cdf5 2
090123458005fb44 2
1403 rows selected.
There are only 1403 Workflow Trackers, which means that there are 3 “additional” relations (or 3 dm_process which are assigned to an already-used Workflow Tracker). To get more details on the Workflow and their Workflow Trackers, these kinds of queries can be executed:
SQL> select parent_id, child_id from dm_relation_s
where parent_id IN ('090123458004fdde','090123458005cdf5','090123458005fb44')
and relation_name='D2_WF_TRACKER_TEMPLATE'
order by 1, 2;
PARENT_ID CHILD_ID
---------------- ----------------
090123458004fdde 4b01234580002575
090123458004fdde 4b01234580057910
090123458005cdf5 4b01234580057910
090123458005cdf5 4b01234580057910
090123458005fb44 4b01234580057910
090123458005fb44 4b01234580057910
SQL> select r_object_id, object_name, r_creator_name from dm_sysobject_sp
where r_object_id IN ('090123458004fdde','090123458005cdf5','090123458005fb44');
R_OBJECT_ID OBJECT_NAME R_CREATOR_NAME
---------------- ---------------------------------------------------------------------- -----------------
090123458004fdde Start Import Approval October 29, 2021 at 10:34:45 AM UTC OQSOP01
090123458005cdf5 Start Ready For Approval November 11, 2021 at 3:55:04 PM UTC OQAuthor01
090123458005fb44 Start In Review November 16, 2021 at 12:01:32 PM UTC OQSOP01
SQL> select r_object_id, object_name from dm_sysobject_sp
where r_object_id IN ('4b01234580002575','4b01234580057910');
R_OBJECT_ID OBJECT_NAME
---------------- ----------------------
4b01234580002575 WF_Import
4b01234580057910 WF_Review_Approve
SQL> select * from dm_relation_sp
where parent_id='090123458005cdf5'
and relation_name='D2_WF_TRACKER_TEMPLATE';
R_OBJECT_ID RELATION_NAME PARENT_ID CHILD_ID CHILD_LABEL PERMANENT_LINK ORDER_NO EFFECTIVE EXPIRATIO DESCRIPTION I_PARTITION I_IS_REPLICA I_VSTAMP
---------------- -------------------------------- ---------------- ---------------- -------------------------------- -------------- ---------- --------- --------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ------------ ----------
370123458000d92a D2_WF_TRACKER_TEMPLATE 090123458005cdf5 4b01234580057910 0 0 01-JAN-01 01-JAN-01 0 0 0
370123458000cd4f D2_WF_TRACKER_TEMPLATE 090123458005cdf5 4b01234580057910 0 0 01-JAN-01 01-JAN-01 0 0 0
Clearly, there is a Data/Business problem. Two of the three Workflow Trackers have an exact duplicate relation for the same dm_process while the third one tracks two different dm_process, which is even more surprising.
To be able to proceed, I noted all the IDs and informed the Application/Business teams and then I did what the SQL command from the D2-DAR was supposed to do. Therefore, I took one of the 2 values and assigned it to the process_id of the Workflow Tracker:
SQL> update d2c_workflow_tracker_s
set process_id='4b01234580002575'
where (process_id IS NULL or process_id='0000000000000000')
and r_object_id='090123458004fdde';
1 row updated.
SQL> update d2c_workflow_tracker_s
set process_id='4b01234580057910'
where (process_id IS NULL or process_id='0000000000000000')
and r_object_id='090123458005fb44';
1 row updated.
SQL> update d2c_workflow_tracker_s
set process_id='4b01234580057910'
where (process_id IS NULL or process_id='0000000000000000')
and r_object_id='090123458005cdf5';
1 row updated.
After that, I re-triggered the query which was previously failing and this time it worked properly for all the remaining Workflow Trackers:
SQL> update d2c_workflow_tracker_s
set process_id=(select child_id from dm_relation_s
where dm_relation_s.parent_id=d2c_workflow_tracker_s.r_object_id
and dm_relation_s.relation_name='D2_WF_TRACKER_TEMPLATE')
where (process_id IS NULL or process_id='0000000000000000')
and exists (select child_id from dm_relation_s
where dm_relation_s.parent_id=d2c_workflow_tracker_s.r_object_id
and dm_relation_s.relation_name='D2_WF_TRACKER_TEMPLATE');
1400 rows updated.
I was then able to start the upgrade pipeline from scratch again, and it was able to complete properly. As previously stated, the Application/Business teams still didn’t find what caused these 3 rows, but it’s only a DEV, so I can only assume that it was some human errors at some points, like a wrong DQL being executed that duplicated some values or something similar. In any cases, what is interesting for this blog is the investigation process and the way to get to the bottom of things by searching inside the D2 DAR and analyzing the DB content.
L’article Dctm – D2-DAR installation fails because of workflow trackers est apparu en premier sur dbi Blog.
SQL Server: Do you know how to use SERVERPROPERTY() function in a useful way?
The SERVERPROPERTY() function is so nice to use to have information about the instance and server very quickly. Sometimes it’s good to come back to basis…
How to begin? Read the documentation of course here but you will see a lot of informations…
After reading the documentation, how to have the first useful information?
The first information will be about the SQL Server installed like the server name, instance name, build and version with also some advanced information like last update date, the KB or CU.
Here this first query:
SELECT
SERVERPROPERTY('MachineName') AS [Server_Name],
SERVERPROPERTY('InstanceName') AS [Instance_Name],
SERVERPROPERTY('Edition') AS [Edition],
SERVERPROPERTY('ProductVersion') AS [Product_Version],
SERVERPROPERTY('ProductLevel') AS [Product _Level],
SERVERPROPERTY('ProductBuild') AS [Product _Build],
SERVERPROPERTY('ProductMajorVersion') AS [Advanced_Product_ Major_Version],
SERVERPROPERTY('ProductMinorVersion') AS [Advanced_Product_Minor_Version],
SERVERPROPERTY('ProductUpdateReference ') AS [Advanced_KB_Version],
SERVERPROPERTY('ProductUpdateLevel') AS [Advanced_Update _Level],
SERVERPROPERTY(' ResourceLastUpdateDateTime ') AS [Advanced_Last_Update_Date]
Result of a test:
I run all queries on my sql server container under Visual Studio Code and sql server 2025 CPT2.1.
You can have more info about it here (dbi Blog)
After, some other useful information will be if the instance has features installed like Polybase or Full-Text seach but also some configuration like filestream or HA.
Here the query:
SELECT
SERVERPROPERTY('MachineName') AS [Server_Name],
SERVERPROPERTY('InstanceName') AS [Instance_Name],
SERVERPROPERTY('IsPolyBaseInstalled') AS [Is_Polybase_Installed],
SERVERPROPERTY('IsFullTextInstalled') AS [Is_Full-Text_Search_Installed],
SERVERPROPERTY('IsAdvancedAnalyticsInstalled') AS [Is_Advanced_Analytics_Installed],
SERVERPROPERTY('FilestreamConfiguredLevel') AS [is_Filestream_enabled],
SERVERPROPERTY('IsBigDataCluster') AS [Is_BigData_Cluster_enabled],
SERVERPROPERTY('IsClustered') AS [Is_Clustered],
SERVERPROPERTY('IsHadrEnabled') AS [Is_HADR_enabled],
SERVERPROPERTY('IsTempDbMetadataMemoryOptimized') AS [Is_TempDB_for_Memory_Optimized_Tables_Enabled],
SERVERPROPERTY('IsXTPSupported') AS [Is_IN_Memory_OLTP_Supported],
SERVERPROPERTY('IsExternalGovernanceEnabled') AS [Is_External_Governance_Enabled];
Result of a test:
You can notice that the last property is the External governance. This is linked to the Microsoft Purview access policies.
The next useful information will be about the security.
Here the query:
SELECT
SERVERPROPERTY('MachineName') AS [Server_Name],
SERVERPROPERTY('InstanceName') AS [Instance_Name],
SERVERPROPERTY('IsIntegratedSecurityOnly') AS [Is_Integrated_Security_Only],
SERVERPROPERTY('IsSingleUser') AS [Is_Single_User],
SERVERPROPERTY('IsExternalAuthenticationOnly') AS [s_External_Authentication_Only];
Result of a test:
The External Authentication concerns the Microsoft Entra-only authentication for Azure SQL Database & Azure SQL Managed Instance.
The last information package is the collation/character set with this query:
SELECT
SERVERPROPERTY('MachineName') AS [Server_Name],
SERVERPROPERTY('InstanceName') AS [Instance_Name],
SERVERPROPERTY('Collation') AS [Collation],
SERVERPROPERTY('LCID') AS [Windows_Locale_Identifier],
SERVERPROPERTY('SqlCharSetName') AS [SQL_Character_Set_Name],
SERVERPROPERTY('SqlSortOrderName') AS [SQL_Sort_Order_Name];
Result of a test:
To conclude, you have now in this blog 4 useful queries to find information on the server level.
Don’t hesitate to add your comments or give me other useful property that you use.!
L’article SQL Server: Do you know how to use SERVERPROPERTY() function in a useful way? est apparu en premier sur dbi Blog.
Alfresco – Documents not searchable when uploaded via REST-API?
It’s a pretty common (and should be known) issue in Alfresco but even to this day, it still happens that enterprises contact us because of documents that cannot be found. The installation in question is a docker-compose for Alfresco Community 23.4, managed by the customer directly. Only some slight customizations were done but it’s a pretty out-of-the-box setup for the search part. There is an interface that uploads some documents (using a custom type) into a specific Site using the REST-API but each of these documents is “apparently” not searchable in Alfresco Share. When downloading and re-uploading manually through Share (either default or custom type), the customer was saying that the documents can indeed be found. So, what is going on here?
As mentioned, this is a pretty common problem. No latter than last week, there was again discussions around that topic on the Alfresco Discord channel, and yesterday at that customer, they were also facing that exact same thing. Therefore, I think it’s high time for me to write a blog on that topic, to share a bit of experience and hopefully reduce the amount of people that are impacted, probably even without knowing it. I will use a local development environment to demonstrate the issue and show how to fix it.
The ProblemSo, what is the issue? In short, it’s linked to Solr6 cross-locale, i.e. a language problem. When using Solr6 with Alfresco, by default, the cross-locale configuration is commented out, which means it is effectively disabled. When someone or something upload a document to Alfresco, it will be assigned a property “sys:locale” that is the language used by the client that created said document. If it’s a web browser, then it would be assigned the language configured on your browser. If it’s a command line request, it would be the OS session language, etc… Then, when users are trying to search for that document, the locale of the user is also assigned to the search request and if it doesn’t match the initial locale (the one used to create the document), then you will probably never be able to find anything.
Unfortunately, Solr6 for Alfresco is assuming that everybody speak and uses only one language, which is definitively not true for a lot of large-scale or international enterprises or even smaller ones but in countries where there are multiple official languages, like Switzerland.
Environment PreparationIn the environments that I install, I always configure the cross-locale support. So, first of all, I will need to revert to the default Solr6 configuration. More details on that will be in the solution section below. With a default Solr6 installation/index, I will create a custom type with a single property that I will use to perform the searches (it’s not mandatory, you can use the default type/properties too). You can also use Full-Text Search, but it’s probably easier for me to showcase the problem using a very specific property.
Therefore, I created a custom type (dbi:demo_doc) and a property (dbi:demo_prop) using the model manager. “dbi:demo_prop” is a simple text property that is mandatory, indexed as “Free Text” (c.f. the documentation here for more details) and I didn’t set any constraints for this example but it could have had one. I will just use values such as “NB-[0-9]{4}” (so NB-0000, NB-0001, etc.):
Showcase – Web Browser in English
Now that I have a cleaned and out-of-the-box Solr6 index and my property to play with, let’s start with creating a first test document in a first browser with an English display. Assigning this document to my custom type “dbi:demo_doc” and using the value “NB-0001” for my property “dbi:demo_prop“:
As you can see above, the “sys:locale” has been set to “en_US” (coming from my browser settings) and please also note the DBID 1519 for later.
Then on that same browser, the document is properly searchable, there is 1 result, so far so good:
Note: I’m specifically not adding an “=” at the beginning of the search term so that it goes to Solr. If you have TMDQ (Transactional MetaData Query) enabled and if you use an exact match for a property, then it will normally use the database to answer the search, which would defeat the purpose of this blog…
Showcase – Web Browser in FrenchNow let’s proceed with opening a second browser, that is configured with French display this time and doing the exact same thing with a new document:
As you can see above, the “sys:locale” has been set to “fr” this time, for the same document, simply from another browser with a different display language (which you can confirm on above screenshots as everything is written in French). Same as before, please also note the DBID 1523 for later.
Then on that same browser, the document itself is also searchable, but in normal cases, we would have expected 2 results, no…? There is only one here:
This is the issue I’m talking about. Because the cross-locale is disabled, the document imported with a different locale isn’t found when you are searching for some text on content or properties.
It’s pretty obvious with the above already, but if you really want to confirm that the documents are indexed inside Solr, you can do something like that on the Solr UI “q=dbi:demo_prop:’NB-0001′” and it will give you 2 results, 2 documents with DBID 1519 as well as 1523. If you remember or if you look at the above screenshots, you can see that’s the values of the “sys:node-dbid” property for the 2 documents. Therefore, these are indexed in Solr:
How to Solve
So now, how do we fix that? Fortunately, it’s not very complex, but depending on your repository size, it might take some time as it will require to enable the cross-locale and then to perform a complete reindex… Since I’m using a nearly empty development environment, it will be very fast for me.
On the Solr host, let’s start with checking the cross-local configuration:
[solr@solr01 ~]$ cd $SOLR_HOME/solrhome/conf/
[solr@solr01 conf]$
[solr@solr01 conf]$ grep "cross.locale.datatype" shared.properties
#alfresco.cross.locale.datatype.0={http://www.alfresco.org/model/dictionary/1.0}text
#alfresco.cross.locale.datatype.1={http://www.alfresco.org/model/dictionary/1.0}content
#alfresco.cross.locale.datatype.2={http://www.alfresco.org/model/dictionary/1.0}mltext
[solr@solr01 conf]$
As you can see, the 3 lines for cross-locale configuration of datatypes are commented out by default (by the way, it’s also indicated in the documentation here). Therefore, let’s simply uncomment these and then we will scratch the index so that Solr6 can perform a full reindex:
[solr@solr01 conf]$ sed -i 's,^#\(alfresco.cross.locale.datatype\),\1,' shared.properties
[solr@solr01 conf]$
[solr@solr01 conf]$ grep "cross.locale.datatype" shared.properties
alfresco.cross.locale.datatype.0={http://www.alfresco.org/model/dictionary/1.0}text
alfresco.cross.locale.datatype.1={http://www.alfresco.org/model/dictionary/1.0}content
alfresco.cross.locale.datatype.2={http://www.alfresco.org/model/dictionary/1.0}mltext
[solr@solr01 conf]$
[solr@solr01 conf]$ sudo systemctl stop solr
[solr@solr01 conf]$
[solr@solr01 conf]$ ls -l $SOLR_DATA_HOME/
total 4
drwxr-x---. 4 alfresco alfresco 37 Oct 16 12:53 index
drwxr-x---. 2 alfresco alfresco 4096 Oct 16 12:54 models
[solr@solr01 conf]$
[solr@solr01 conf]$ rm -rf $SOLR_DATA_HOME/*
[solr@solr01 conf]$
[solr@solr01 conf]$ sudo systemctl start solr
[solr@solr01 conf]$
Once the documents are indexed, you can go back to the English or French browsers and execute the previous search, and voila, 2 results:
You can now create new documents with any locale, and it should be transparent from a search perspective. If you would like to see which locale is being sent during Search requests, you can enable DEBUG logs for “org.alfresco.repo.search.impl.solr.SolrQueryHTTPClient” and something like that should appear:
2025-10-16 15:35:29,732 DEBUG [impl.solr.SolrQueryHTTPClient] [http-apr-8080-exec-39] with: {"tenants":[""],"locales":["en_US"],"defaultNamespace":"http://www.alfresco.org/model/content/1.0","textAttributes":[],"defaultFTSOperator":"AND","defaultFTSFieldOperator":"AND","anyDenyDenies":true,"query":"dbi:demo_prop:'NB-0001' ","templates":[{"template":"%(cm:name cm:title cm:description ia:whatEvent ia:descriptionEvent lnk:title lnk:description TEXT TAG)","name":"keywords"}],"allAttributes":[],"queryConsistency":"DEFAULT","authorities":["GROUP_EVERYONE","ROLE_ADMINISTRATOR","ROLE_AUTHENTICATED","admin"]}
2025-10-16 15:35:29,732 DEBUG [impl.solr.SolrQueryHTTPClient] [http-apr-8080-exec-39] Got: 2 in 47 ms
Happy searching!
L’article Alfresco – Documents not searchable when uploaded via REST-API? est apparu en premier sur dbi Blog.
User Adoption Strategies for M-Files: Lessons Learned
Implementing M-Files is never just a technical project. It’s a journey through an organization’s operations that we take with each customer.
As consultants, we often find that the success of an M-Files deployment hinges not on the software itself, but on how well users embrace it. After working with organizations across industries, here are some key lessons and strategies that consistently drive user adoption.
Before introducing M-Files, it’s crucial to articulate why the change is happening. Users need to understand how M-Files will make their work easier, whether it’s faster document retrieval, reduced email clutter, or improved compliance. Tailor the message to each department’s pain points and goals.
Lesson: Adoption improves when users see M-Files as a solution to their problems, not just another IT tool.
Identify and empower internal key users, people who are respected, tech-savvy, and enthusiastic. These champions can provide feedback during configuration, test workflows, and help train their peers. Their support creates a ripple effect across teams.
Lesson: Peer influence is more powerful than management’s decisions that members have to follow.
Configure for Simplicity, Not Just CapabilityM-Files is incredibly flexible, and we can accomplish so much with it. However, if it is poorly designed, that can be its biggest flaw.
Avoid overwhelming users with too many metadata fields, complex workflows, or unfamiliar terminology. Start with a clean, intuitive structure and evolve it based on feedback.
Lesson: A well-designed vault should feel natural and frictionless to users.
Integrate with Familiar ToolsUsers are more likely to adopt M-Files when it fits into their existing habits. Integrating with Microsoft Teams, Outlook, or SharePoint can make M-Files feel like an extension of their daily workspace rather than a separate system.
Lesson: Seamless integration optimizes employee engagement and reduces resistance.
Provide Hands-On Training and SupportTraining should be role-specific and scenario-based. Instead of generic demonstrations, show users how M-Files can help them with their daily tasks, such as managing contracts, onboarding employees, and tracking project documents. Follow up with cheat sheets, videos, and a support channel.
Lesson: Ongoing support is key to sustained adoption.
Celebrate Quick WinsRome wasn’t built in a day. Highlight early successes, such as reducing the time spent searching for documents or passing an audit, in internal newsletters or at team meetings. Recognition creates momentum and validates efforts.
Lesson: There’s no need to wait until the end of the project to celebrate! Celebrating small victories boosts morale and paves the way for long-term transformation.
UltimatelyHowever brilliant a solution may be, choosing it is not an end in itself. On the contrary, it is only the beginning of the journey.
User adoption is not just about training. It is also based on trust, relevance, and experience. At dbi services, we focus on the human aspect. We can transform M-Files from a basic system into a valuable solution.
L’article User Adoption Strategies for M-Files: Lessons Learned est apparu en premier sur dbi Blog.
You still need reliable and tested backups for your Oracle databases!
These days, I was reviewing and updating the “Oracle Backup and Recovery Workshop” from dbi services. It’s all about RMAN, a powerful tool that always impresses my students. But I must admit that this workshop is less popular than before. Today, backup of an Oracle database is not a hot topic anymore. First, nothing is really new regarding RMAN compared to a few years ago. Hardware reliability has also increased significantly, and data centers are more secured than ever. Both making failures less likely to happen. Another point is that Disaster Recovery has become a standard in most projects, meaning that for a lot of failures, enabling the standby database will solve the problem in a minute or two. Finally, Oracle-embedded flashback technologies bring you faster solutions compared to restoring a backup when database is not broken (these technologies are also part of the workshop). That said, it doesn’t mean that you don’t need a strong backup strategy. This is still your very last protection for dramatic scenarios. And you need to test it from time to time. Let’s review what’s important and how to check and test your backup strategy.
Why you still need a reliable backup at any moment?Let’s remind the main reasons for having a reliable backup:
- you may loose everything on your primary and secondary site
- you may have logically corrupted data from a software error or a human error, also replicated to your standby database
- you may want to recover partial data that flashback technologies cannot bring back
- you may have a security breach widely altering your data (corrupted storage, ransomware, aso)
For sure, this is rather unlikely to happen, but as a DBA, you’re supposed to have a plan in case of any kind of disaster. And a solid RMAN backup is your trustable ally.
Why you probably won’t use a RMAN restore in most cases?You probably have a Disaster Recovery (DR) setup, meaning 2 sites with database replication using Data Guard (for Enterprise Edition) or Dbvisit Standby (for Standard Edition 2). In most cases, if you need a quick solution for bringing back your database to life after a crash, you will failover to the DR database. It’s a couple of minutes, and if you test your DR database on a regular basis (I mean doing a switchover at least once a year), this operation will succeed without any doubt. A failover is adequate when your primary server is not responding or when your primary database has corrupted files. Doing a failover is always the best thing to do if you want minimum data loss.
If you need data loss, I mean if there is a data corruption due to an error (incorrect SQL commands, application bug), you will probably use the Oracle-embedded flashback technologies. Flashback relies on multiple different technologies for different purposes. If you drop a table, you can bring it back from the recycle bin (FLASHBACK TABLE TO BEFORE DROP), if you delete lines from a table, you can do a query in time (AS OF TIMESTAMP) for example. If the overall database needs to go back in time, and if you’re using Enterprise Edition, you will use FLASHBACK DATABASE. FLASHBACK DATABASE, once enabled, is a mechanism that generates flashback logs, a kind of “INCR -1” automatic backup. With this feature, you can go back at any point in time from a couple of minutes to several hours. It’s much faster than an equivalent RMAN restore. And you can do multiple flashback operations to find the desired Point In Time.
Restoring a backup will be done when nothing else is possible, just because it’s slower to put back older datafiles on disk from backupsets, and recover them.
Probably the most important: make sure RPO and RTO are aligned with the company strategyAs a DBA, you must ensure that these metrics, defined by the company, are met:
- Recovery Point Objective: until when you must be able to go back in time regarding data
- Recovery Time Objective: the maximum time you need to restore the database to any Point In Time within the RPO
You should consider these RPO/RTO as a contract between the DBA and the management. The DBA will require resources, like adequate infrastructure, disk capacity and licenses to reach these objectives.
Regarding RPO, it’s mainly a matter of storage capacity. For RTO, it involves considering database edition, number of licenses/cores and storage bandwidth.
You must test your backup on a regular basis, just to ensure that you still respect the RTO with an increasing amount of data months after months.
Too often, RPO and RTO are defined by the DBA himself. In other words, the DBA makes what’s possible with the resources he has. This is definitely not the best approach.
Check and test your backupsFirst, you should never trust your backup! Not because it’s not reliable, but because backup is done when the database is opened, meaning that backups are not consistent. Restore is only possible if a complete set of backups (full + incremental + archivelogs) is available, with an adequate controlfile. Green lights from your backup monitoring tool or the successful word at the end of your backup log is simply not enough. The only trustable backup strategy is the one you test on a regular basis.
These are the checks and tests I would do to make sure my backup is OK.
Check RMAN viewsWith this statement, you are able to guess the backup strategy and see if it works fine at a glance.
set lines 180
set pages 300
col min for 999.9
col status for a20
alter session set NLS_DATE_FORMAT="DD/MM-HH24:MI";
select start_time "Start", round (input_bytes/1024/1024,1) "Source MB", round(output_bytes/1024/1024) "Backup MB", input_type "Type", status "Status", round(elapsed_seconds/60,1) "Min", round(input_bytes/1024/1024/elapsed_seconds,1) "read MB/s", round(output_bytes/1024/1024/elapsed_seconds,1) "write MB/s", round(compression_ratio,1) RATIO from v$rman_backup_job_details where start_time >= SYSDATE-7 order by 1 desc;
Start Source MB Backup MB Type Status Min read MB/s write MB/s RATIO
-------------- ---------- ---------- ------------- ------------ ------ ---------- ---------- ----------
25/08-15:10:06 1443 380 ARCHIVELOG COMPLETED .7 36.1 9.5 3.8
25/08-14:10:06 123 50 ARCHIVELOG COMPLETED .1 20.6 8.3 2.5
25/08-13:10:05 33 27 ARCHIVELOG COMPLETED .1 6.5 5.4 1.2
25/08-12:10:05 32 27 ARCHIVELOG COMPLETED .1 8 6.8 1.2
25/08-11:10:05 46 31 ARCHIVELOG COMPLETED .1 7.7 5.1 1.5
25/08-10:10:05 44 31 ARCHIVELOG COMPLETED .1 7.4 5.1 1.4
25/08-09:10:04 31 27 ARCHIVELOG COMPLETED .1 5.1 4.5 1.1
25/08-08:10:05 32 27 ARCHIVELOG COMPLETED .1 8 6.8 1.2
25/08-07:10:05 30 26 ARCHIVELOG COMPLETED .1 4.9 4.4 1.1
25/08-06:10:05 30 26 ARCHIVELOG COMPLETED .1 5.9 5.3 1.1
25/08-05:10:04 30 27 ARCHIVELOG COMPLETED .1 5 4.4 1.1
25/08-04:10:05 32 27 ARCHIVELOG COMPLETED .1 8 6.8 1.2
25/08-03:10:05 38 29 ARCHIVELOG COMPLETED .1 6.3 4.9 1.3
25/08-02:10:05 30 27 ARCHIVELOG COMPLETED .1 5 4.4 1.1
25/08-01:10:05 30 26 ARCHIVELOG COMPLETED .1 5.9 5.3 1.1
25/08-00:10:05 39 30 ARCHIVELOG COMPLETED .1 7.8 6 1.3
24/08-22:30:06 133858 17371 DB INCR COMPLETED 26.3 84.8 11 7.7
24/08-22:10:05 1238 376 ARCHIVELOG COMPLETED .5 41.3 12.5 3.3
24/08-21:10:05 29 26 ARCHIVELOG COMPLETED .1 7.2 6.6 1.1
24/08-20:10:05 29 26 ARCHIVELOG COMPLETED .1 4.1 3.8 1.1
24/08-19:10:05 34 28 ARCHIVELOG COMPLETED .1 5.6 4.6 1.2
24/08-18:10:05 39 29 ARCHIVELOG COMPLETED .1 7.8 5.8 1.3
24/08-17:10:05 1037 204 ARCHIVELOG COMPLETED .5 35.8 7 5.1
24/08-16:10:05 42 31 ARCHIVELOG COMPLETED .1 6 4.4 1.4
...
In this example, everything is fine (Status=COMPLETED), a full (INCR level 0) is done every night, archivelog backup is done every hour and compression is enabled. Here, read MB/s and write MB/s give you estimated values. If you use incremental backups (INCR level 1), you can identify them with the “Backup MB” column: the figure should be much lower than the one of an INCR level 0.
Having a catalog is definitely more convenient as you can get an overview for all your databases from a unique statement:
set lines 180
set pages 300
col min for 999.9
col status for a20
alter session set NLS_DATE_FORMAT="DD/MM-HH24:MI:SS";
select db_name, start_time "Start", round (input_bytes/1024/1024,1) "Source MB", round(output_bytes/1024/1024,1) "Backup MB", input_type "Type", status "Status", round(elapsed_seconds/60,1) "Min", round(input_bytes/1024/1024/elapsed_seconds,1) "read MB/s", round(output_bytes/1024/1024/elapsed_seconds,1) "write MB/s", round(compression_ratio,1) RATIO from rc_rman_backup_job_details where start_time >= SYSDATE-7 order by 1,2 desc;
...But this query is not enough to make sure that you can restore your database.
Check if retention is the one you expectIt’s not rare that a DBA is surprised when he checks backup retention and discovers a much lower value than expected. Retention can be changed in a minute, often for solving storage capacity issues. This could have dramatic consequences if you miss the RPO your boss sign up for. But most probably, nobody will know about that…
rman target /
show RETENTION POLICY ;
RMAN configuration parameters for database with db_unique_name ROP_S1 are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;With this retention, you should be able to restore the database at a consistent state between now and 7 days before. It implies keeping backupsets of database for more than 7 days, because you need the FULL or INCR 0 backup before 7 days to reach the oldest Point In Time. And for sure, all the INCR 1 and archivelog backups done after this FULL or INCR 0.
But checking the retention is not enough to make sure you have everything needed for a restore.
Check if backups are known by RMANWhen deleting backups, this retention can be overridden if needed. So there is no guarantee that backups are still in the catalog. You should be able to find backups within this retention, backups of database (FULL or INCR 0) but also backups of archivelogs and INCR 1:
LIST BACKUP OF DATABASE COMPLETED BETWEEN 'sysdate -7' AND 'sysdate';
...
LIST BACKUP OF ARCHIVELOG COMPLETED BETWEEN 'sysdate -7' AND 'sysdate';
...This is pretty verbose but you will find the various backusets and what’s inside.
Unfortunately, this is still not enough to make sure you can restore your database.
Do a crosscheck to validate that backup pieces are availableLIST BACKUP is only having a look at backup references in the catalog, it doesn’t guarantee that backupset files (backup pieces) are available. Therefore, a crosscheck is needed to match references with files on disk:
CROSSCHECK BACKUPSET;
You can do crosscheck only on a subset of backups, for example the most recents:
CROSSCHECK BACKUPSET COMPLETED AFTER 'sysdate -1';
...Or backups between 2 dates:
CROSSCHECK BACKUPSET COMPLETED BETWEEN 'sysdate -7' AND 'sysdate -6';
...All backupsets must be AVAILABLE. If not, they are EXPIRED, meaning not on disk anymore. This is not a normal behavior.
Is it enough for making sure you can restore? Not at all, it doesn’t mean the restore is possible.
Do a restore database validate with and without a Point In TimeWithout any downtime and disk usage, you can do a RESTORE DATABASE VALIDATE. It will read backupsets from the backup location and validate that these backupsets are OK to restore the datafiles.
run {
ALLOCATE CHANNEL C1 DEVICE TYPE DISK;
ALLOCATE CHANNEL C2 DEVICE TYPE DISK;
ALLOCATE CHANNEL C3 DEVICE TYPE DISK;
RESTORE DATABASE VALIDATE;
}This RESTORE DATABASE VALIDATE is also possible with a Point In Time. RMAN is then able to simulate a full restore of the adequate backupsets within the rentention.
run {
SET UNTIL TIME "TO_DATE('10-OCT-2025 14:00:00','DD-MON-YYYY HH24:MI:SS')";
ALLOCATE CHANNEL C1 DEVICE TYPE DISK;
ALLOCATE CHANNEL C2 DEVICE TYPE DISK;
ALLOCATE CHANNEL C3 DEVICE TYPE DISK;
RESTORE DATABASE VALIDATE;
}Note that it restores the datafiles to /dev/null, meaning nowhere. This is why a RECOVER DATABASE VALIDATE does not exist, it’s the main limit. You will be able to restore the database, but you might not be able to recover the datafiles and open the database.
This test is nice, but not good enough. The recover is mandatory to open the database.
Do a restore AND recover on another server with or without a Point In TimeThe best test would be a complete restore/recover on another server. Considering this server has the same system settings, the same Oracle version and the same filesystems, it’s rather easy. First, identify the latest controlfile and spfile autobackup, then restore the spfile and the controlfile from this backupset. RMAN is able to start an instance without spfile, for the only purpose of restoring the spfile:
su - oracle
echo "BMC3PRD:/u02/app/oracle/product/19.0.0.0/dbhome_1:Y" >> /etc/oratab
ls -lrt /backup/BMC3PRD/ | tail -n 1
-rw-r-----. 1 oracle oinstall 19136512 Sep 4 09:20 o1_mf_s_1156169494_lr8gd9h4_.bkp
. oraenv <<< BMC3PRD
rman target /
startup nomount;
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u02/app/oracle/product/19.0.0.0/dbhome_1/dbs/initBMC3PRD.ora'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area 1070428224 bytes
Fixed Size 8904768 bytes
Variable Size 281018368 bytes
Database Buffers 771751936 bytes
Redo Buffers 8753152 bytes
restore spfile from '/backup/BMC3PRD/o1_mf_s_1156169494_lr8gd9h4_.bkp';
Starting restore at 04-SEP-25
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=433 device type=DISK
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /backup/BMC3PRD/o1_mf_s_1156169494_lr8gd9h4_.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 04-SEP-25
startup force nomount;
restore controlfile from '/backup/BMC3PRD/o1_mf_s_1156169494_lr8gd9h4_.bkp';
Starting restore at 04-SEP-25
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=26 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:05
output file name=+RECO/BMC3PRD_CR/CONTROLFILE/current.486.1210937913
Finished restore at 04-SEP-25
startup force mount;
run {
ALLOCATE CHANNEL C1 DEVICE TYPE DISK;
ALLOCATE CHANNEL C2 DEVICE TYPE DISK;
ALLOCATE CHANNEL C3 DEVICE TYPE DISK;
ALLOCATE CHANNEL C4 DEVICE TYPE DISK;
RESTORE DATABASE ;
RECOVER DATABASE ;
ALTER DATABASE OPEN RESETLOGS;
}
...
Testing a restore/recover at a random Point In Time within your retention window is also quite easy: identify the autobackup of spfile and controlfile corresponding to the first one after your Point in Time target, and specify an UNTIL clause in your restore/recover database block:
rman target /
startup nomount;
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u02/app/oracle/product/19.0.0.0/dbhome_1/dbs/initBMC3PRD.ora'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area 1070428224 bytes
Fixed Size 8904768 bytes
Variable Size 281018368 bytes
Database Buffers 771751936 bytes
Redo Buffers 8753152 bytes
restore spfile from '/backup/BMC3PRD/o1_mf_s_1156169494_124efa11_.bkp';
...
startup force nomount;
restore controlfile from '/backup/BMC3PRD/o1_mf_s_1156169494_124efa11_.bkp';
...
startup force mount;
run {
SET UNTIL TIME "TO_DATE('10-SEP-2025 09:27:42','DD-MON-YYYY HH24:MI:SS')";
ALLOCATE CHANNEL C1 DEVICE TYPE DISK;
ALLOCATE CHANNEL C2 DEVICE TYPE DISK;
ALLOCATE CHANNEL C3 DEVICE TYPE DISK;
ALLOCATE CHANNEL C4 DEVICE TYPE DISK;
RESTORE DATABASE ;
RECOVER DATABASE ;
ALTER DATABASE OPEN RESETLOGS;
}
...This time, this test is enough to make sure your backups are OK, because you just did the exact same procedure as if you were facing a real disaster case.
Duplicate from backup with or without a Point In TimeYou may not want to restore the database with the same name than the one used for production database, for obvious reasons. You can then restore with a different name thanks to the DUPLICATE command. This command is used for duplicating a target (source) to an auxiliary database (destination), but you can use it without any target, only pointing to a folder containing the backupsets. Starting the instance without a spfile is still possible with RMAN, but you’ll need to connect using target keyword first:
echo "RESTO:/u02/app/oracle/product/19.0.0.0/dbhome_1:Y" >> /etc/oratab
. oraenv <<< RESTO
rman target /
startup nomount;
exit;
rman auxiliary /
run {
ALLOCATE AUXILIARY CHANNEL C1 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL C2 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL C3 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL C4 DEVICE TYPE DISK;
DUPLICATE DATABASE TO RESTO BACKUP LOCATION '/backup/BMC3PRD/' NOFILENAMECHECK;
}
It also works with a Point In Time:
rman auxiliary /
run {
SET UNTIL TIME "TO_DATE('10-OCT-2025 16:30:00','DD-MON-YYYY HH24:MI:SS')";
ALLOCATE AUXILIARY CHANNEL C1 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL C2 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL C3 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL C4 DEVICE TYPE DISK;
DUPLICATE DATABASE TO RESTO BACKUP LOCATION '/backup/BMC3PRD/' NOFILENAMECHECK;
}All these commands are fine when using OMF: I would recommend using OMF everywhere. If you don’t use OMF, you will need to take care of file names and use path conversions within RMAN commands.
Please, never do a DUPLICATE on a server where a database with the same name as the source database exists: at some point, RMAN needs to restore the controlfile with the name of the source database before changing for the new name, it can be hazardous…
This test is as good as the previous one: it can definitely validate your backups as if you were restoring your production database.
ConclusionI would summarize this blog post with these 3 things:
- you still need a strong backup strategy: do not underestimate the high importance your backups still have
- RPO and RTO are decided by your company and the DBA asks for resources allowing him to reach these targets. He must warn if resources cannot guarantee these RPO/RTO
- do regular and extensive checks of your backups, and simulate a disaster scenario at least once a year: this is good for validating RPO/RTO and good for validating the reliability of your backup strategy. This is also good for your training as you normally won’t do regular restore/recover operations
L’article You still need reliable and tested backups for your Oracle databases! est apparu en premier sur dbi Blog.
APEX IR Subscriptions #JoelKallmanDay
Interactive Reports allow to send their content per e-mail on a regular basis thanks to the subscription feature. But this can also raise some security concerns. Let’s see how to bring some kind of control into the game…
RequirementsWe developed some reporting solution for an Insurance company based on #orclAPEX. Some of the reports need to be sent on a regular basis, as Excel, to people like executive managers and that’s where Interactive Reports Subscription comes into the game.
As you can imagine, such reports may contain confidential data which should not be sent outside of the company (at least not by the reporting application itself).
There are many ways to control the e-mail flows, but in some cases it takes time to implement changes in company global rules. So, let’s start with small changes until the bigger ones can be implemented…
The need is to let the application Administrators see all IR subscriptions, identify the ones with a target e-mail address outside of the company and delete them.
ImplementationPrior to use of subscription, e-mail server must be configured at APEX instance level.
First we need to build the query of the report. To do so, let’s identify the APEX views which can provide the required information about the IR subscription within the APEX_DICTIONARY.
We are looking for information within the application, so view name will start by
APEX_APPLICATION_…
The reports are located in application pages, so view name will add as following APEX_APPLICATION_PAGE_…
Within the page we are looking for the Interactive Report
APEX_APPLICATION_PAGE_IR_…
And for the Interactive Report, Subscription definition is required: APEX_APPLICATION_PAGE_IR_SUB
The structure of the reports pages in scope is basic and contains only one Interactive Report. The name of the page is enough to identify the report in the list, so the APEX_APPLICATION_PAGES view needs to be joined.
There is only one workspace, so there is no need to identify the workspace, but only the application.
The query looks as following:
SELECT ap.page_id,
ap.page_name,
apis.notify_id,
apis.owner,
apis.email_address,
apis.email_subject,
apis.start_date,
apis.notify_internal,
apis.end_date,
apis.download_format,
apis.created_on,
apis.status
FROM apex_application_pages ap, apex_application_page_ir_sub apis
WHERE ap.application_id = v('APP_ID')
AND ap.application_id = apis.application_id
AND ap.page_id = apis.page_id
The best place to add the report in the application, is the standard administration reports provided by APEX and already in use:
The report output is an Interactive Report in a Modal Dialog like the other Administration reports. We use field highlighting to identify e-mail addresses outside of company domain …
Let’s check for a blog within the community to see how to add the delete column:
https://vinish.dev/create-delete-row-button-for-interactive-report-in-oracle-apex
The deletion procedure is DELETE_SUBSCRIPTION given by the APEX_IR PL/SQL API package. The required subscription_id parameter is given by the notify_id field in the apex_application_page_ir_sub view.
And the JavaScript Confirmation message looks like this:
thanks to following parameters:
With some basic add-on, based on APEX views and PL/SQL APIs it is easy to provide monitoring and control upon Interactive Report Subscriptions within an application.
I hope this helps you to start with such requirements…
… enjoy APEX development
L’article APEX IR Subscriptions #JoelKallmanDay est apparu en premier sur dbi Blog.
Using Oracle API for MongoDB on OCI
If you’ve ever had to deal with MongoDB after years (or even decades) of working with SQL, you’ve probably realized that querying JSON documents can sometimes be painful, to say the least.
In response to this, Oracle (and others) use a not-so-native adaptation of SQL to query JSON documents. And if you’ve ever had to deal with these SQL-based JSON queries, you may have found yourself thinking (in hindsight) that querying JSON documents with MQL (MongoDB Query Language) wasn’t that painful after all. And rightly so : JSON documents are very different from relational tables, and there is no easy way to continue using SQL without learning some new syntax.
Oracle likely came to the same conclusion, and offers an API dedicated to MongoDB natives. The idea is simple : to ease the work of developers and database administrators when deciding to migrate from MongoDB to Oracle (and the other way around ?…)
On Oracle Cloud Infrastructure (OCI), this means being able to connect to Autonomous Databases (ADB). As a reminder, you have two of them included in the Always Free Tier of OCI, so you can play around with this feature for free before deciding to migrate to the cloud.
Setting up the Oracle MongoDB API on OCIWhen creating an Autonomous Database, you can decide on multiple workloads types, including a JSON workload. However, this workload type isn’t strictly required for the MongoDB API to work.
However, the network access setting of your Autonomous Database must be non-default with one of the following options enabled :
- Secure access from allowed IPs and VCNs only
- Private endpoint access only
For instance, when using the Secure access from allowed IPs and VCNs only option, you can add any IP address to the Access Control List (ACL), including your own.
Warning : If your IP address changes, you will have to update the ACL !
Once the ADB (Autonomous Database) is created, you can check in the Tool configuration tab whether the MongoDB API is enabled (it should be, by default).
Then you can go in Database actions > Database Users to either create a user or modify an existing one, with the following privileges : CREATE SESSION, CREATE RESOURCE (default), and the SODA_APP role. After creation, you should enable REST as shown below. This will allow the API to work on that particular schema.
Back on the Tool configuration tab of your ADB instance, you’ll find the Public access URL. Copy it, this will be your connection string when using the API.
Connection String Example
For this to work, you have to replace the user and password in the connection string. For instance, if your user is called USER_DB, and your password is userpwd, then you would use this connection string (without the brackets). Make sure to escape any special character in the password when doing so (see documentation).
mongodb://user_db:userpwd@A49E7CHE9B320E6-PCCVX8PAE2NZPVBQ.adb.eu-zurich-1.oraclecloudapps.com:27017/user_db?authMechanism=PLAIN&authSource=$external&ssl=true&retryWrites=false&loadBalanced=true'Use this connection string with mongosh or any tool provided by MongoDB. With mongosh, you would connect with this command :
mongosh 'mongodb://user_db:userpwd@A49E7CHE9B320E6-PCCVX8PAE2NZPVBQ.adb.eu-zurich-1.oraclecloudapps.com:27017/adb_admin?authMechanism=PLAIN&authSource=$external&ssl=true&retryWrites=false&loadBalanced=true'You are now connected to the Oracle ADB instance through the mongosh utility.
If you connect to an existing schema with tables, you will notice that running show collections (or show tables) doesn’t return anything. In Oracle 19c, only collections/tables created through the API appear in this list. Standard tables create with a CREATE TABLE SQL statement won’t be displayed.
Once connected, you can query the different databases (schemas, in Oracle terms) of your instance. However, unlike in MongoDB, you can’t administer the instance through mongosh.
You can look at the documentation to check whether the features you’re interested in are included in the API, but here is an extract of the most notable changes :
- All the most basic aggregated pipelines are available in the API, but more complex ones like
$currentOpor$mergeare not yet supported. - Other aggregation operators, like
$bucket,$documentsor$sampleare only available starting with Oracle 23ai. db.dropDatabase()doesn’t drop the Oracle schema. In fact, it doesn’t even deletes everything in the schema. It only deletes all real collections inside of it. So if you have a traditional table that is not a considered as a collection (not shown inshow collections), it will not be deleted by this command.
MongoServerError: Database connection unavailable
You might encounter the following error :
MongoServerError: Database connection unavailable. Ensure that the user exists and the schema is enabled for use with Oracle REST Data Services. A schema can be enabled by calling the PL/SQL procedure ORDS.ENABLE_SCHEMAIf you see this message, you can either follow the procedure or remember to enable REST directly on the OCI Database Actions panel, as shown above.
In short, Oracle’s MongoDB API provides an interesting bridge between the relational and document worlds. Even though some MongoDB features are supported yet, the API offers a straightforward way to connect MongoDB and Oracle Autonomous Database, making it ideal for testing, migration or even hybrid setups. For developers used to MQL, it can significantly ease the transition to Oracle’s ecosystem.
L’article Using Oracle API for MongoDB on OCI est apparu en premier sur dbi Blog.
Oracle OEM Holistic Patch
Starting with Oracle Enterprise Manager (OEM) 13.5 RU24 (October 2024), Oracle introduced the Holistic Patch for OEM — a unified patching model that consolidates several separate updates into a single coordinated package.
Previously, administrators had to patch:
* The OMS and Agents with the quarterly Release Update (RU).
* OEM Application via the System Patch Bundle (SPB).
* WebLogic CPU and JDK manually.
There was an element of complication because of the need to keep the solution homogeneous. With the Holistic Patch, the last two steps are merged into a single patch bundle.
Now we have a new Holistic patch for OEM that is released with each new OMS Release Update.
How It Works ?Each quarter, it is recommended to do the following procedure:
1- Apply the RU to update the OMS and Agents.
2- Then apply the corresponding Holistic Patch to update WebLogic, the JDK, and all related security components.
The following Oracle Notes should guide you into a safe deployment of this strategy :
* If you are using OEM 13.5 : (Doc ID 3002450.1) .
* If you are using OEM 24.1 : (Doc ID 3082475.1).
Below is a walk-through of the steps I performed to apply the July 2025 Holistic Patch for OEM 13.5.
This procedure assumes the OMS and Agents have already been patched to 13.5 RU27 (July 2025).
Download, copy, and unzip the related patches, in this case :
- Patch 34761383: DB Client 19c Upgrade for FMW 12.2.1.4.0
- Patch 35039230: PATCH 34761383 FAILS DURING UPGRADE DUE TO UPDATED ORACLE_COMMON/JDK/JRE
- Patch 31190532: PROVIDE A PATCH USING INSTALLER AUTOUPDATE PATCH FEATURE FOR OL8 OS PACKAGES CHECKS
- Patch 38194776: Oracle Enterprise Manager 13c Release 5 Holistic Patch for Oracle Management Service – July 2025
Before proceeding, plan your rollback strategy — For instance I took a VM snapshot of my OEM machine and created a guaranteed restore point for the repository database:
SQL> select name from v$restore_point; no rows selected SQL> create restore point CLEAN_DB guarantee flashback database; Restore point created. SQL> select name from v$restore_point; NAME -------------------------------------------------------------------------------- CLEAN_DBStep 1 — Upgrade the DB Client (19c)
This step is required only once before applying your first Holistic Patch (see Doc ID 3060775.1).
If you haven’t done this before, check your current client version of your OMS Home :
export ORACLE_HOME = /opt/app/oracle/product/MiddleWare13cR5 cd $ORACLE_HOME/bin ./sqlplus -version SQL*Plus: Release 12.1.0.2.0 Production
The client is not in 19c, so let’s upgrade this client :
Stop the OMSoracle@Machine1:/home/oracle/ [EM13CR5] emctl stop oms -all Oracle Enterprise Manager Cloud Control 13c Release 5 Copyright (c) 1996, 2021 Oracle Corporation. All rights reserved. Stopping Oracle Management Server... WebTier Successfully Stopped Oracle Management Server Successfully Stopped AdminServer Successfully Stopped Oracle Management Server is Down JVMD Engine is DownRun Preliminary Scripts
Run changePerm.sh from Patch 34761383 on the OMS Home :
oracle@Machine1:/opt/software/34761383/ [EM13CR5] ./changePerm.sh /opt/app/oracle/product/MiddleWare13cR5 Release 6 is Not in the /etc/redhat-release Change permission of /opt/app/oracle/product/MiddleWare13cR5/QOpatch/qopiprep.bat Update areasQueries Rename /opt/app/oracle/product/MiddleWare13cR5/bin/skgxpinfo to /opt/app/oracle/product/MiddleWare13cR5/bin/skgxpinfo.12c.bak Starting rollback of Patches. This action may take some time .......... ORACLE_HOME = /opt/app/oracle/product/MiddleWare13cR5 PATCHES_LIST_IN_ORDER = 29494060,33779451,33897347,34454476 ONEOFF_PATCHES = 34830313 ####################### Begin : rollbackOneoffPatches() ######################## ORACLE_HOME=/opt/app/oracle/product/MiddleWare13cR5 Patches to be rolled back=34830313 Running : /opt/app/oracle/product/MiddleWare13cR5/OPatch/opatch lspatches -oh /opt/app/oracle/product/MiddleWare13cR5 | grep -E "34830313" Patch ID(s): Rollback of Oneoff patch operation is not needed.Continue.... ########################## End : rollbackOneoffPatches() ######################## real 0m5.871s user 0m13.874s sys 0m0.584s ####################### Begin : rollbackPatches() ######################## ORACLE_HOME=/opt/app/oracle/product/MiddleWare13cR5 Patches to be rolled back=29494060|33779451|33897347|34454476 Running : /opt/app/oracle/product/MiddleWare13cR5/OPatch/opatch lspatches -oh /opt/app/oracle/product/MiddleWare13cR5 | grep -E "29494060|33779451|33897347|34454476" Patch ID(s): Rollback operation is not needed.Continue with Thin Installer ########################## End : rollbackPatches() ######################## real 0m5.957s user 0m14.772s sys 0m0.530s All Success
Then, from Patch 35039230, run changePermJDK.sh on the OMS Home :
oracle@Machine1:/opt/software/35039230/ [EM13CR5] ./changePermJDK.sh /opt/app/oracle/product/MiddleWare13cR5 Change permission of /opt/app/oracle/product/MiddleWare13cR5/oracle_common/jdk/jre/legal/jdk Change permission of /opt/app/oracle/product/MiddleWare13cR5/oracle_common/jdk/jre/legal/javafx Change permission of /opt/app/oracle/product/MiddleWare13cR5/oracle_common/jdk/legal/javafx Change permission of /opt/app/oracle/product/MiddleWare13cR5/oracle_common/jdk/jre/COPYRIGHT Change permission of /opt/app/oracle/product/MiddleWare13cR5/oracle_common/jdk/jre/LICENSE Change permission of /opt/app/oracle/product/MiddleWare13cR5/oracle_common/jdk/jre/THIRDPARTYLICENSEREADME.txt oracle@Machine1:/opt/software/35039230/ [EM13CR5]
These scripts fix permissions in the Middleware home and JDK directories.
Attempt the DB Client Upgradeoracle@Machine1:/opt/software/34761383/ [EM13CR5] export ORACLE_HOME=/opt/app/oracle/product/MiddleWare13cR5
oracle@Machine1:/opt/software/34761383/ [EM13CR5] ./fmw_12.2.1.19.0_dbclient_linux64.bin -invPtrLoc ${ORACLE_HOME}/oraInst.loc -silent ORACLE_HOME=${ORACLE_HOME}
Launcher log file is /tmp/OraInstall2025-09-10_10-34-53AM/launcher2025-09-10_10-34-53AM.log.
Checking if CPU speed is above 300 MHz. Actual 2893.202 MHz Passed
Checking swap space: must be greater than 512 MB. Actual 4051 MB Passed
Checking if this platform requires a 64-bit JVM. Actual 64 Passed (-d64 flag is not required)
Checking temp space: must be greater than 300 MB. Actual 7269 MB Passed
Preparing to launch the Oracle Universal Installer from /tmp/OraInstall2025-09-10_10-34-53AM
Log: /tmp/OraInstall2025-09-10_10-34-53AM/install2025-09-10_10-34-53AM.log
Setting ORACLE_HOME...
Copyright (c) 1996, 2022, Oracle and/or its affiliates. All rights reserved.
Skipping Software Updates
Starting check : CertifiedVersions
Expected result: One of oracle-8, redhat-7, oracle-7, SuSE-11, SuSE-12, SuSE-15
Actual Result: redhat-n/a
Check complete. The overall result of this check is: Passed
CertifiedVersions Check: Success.
Starting check : Packages
Checking for binutils-2.23.52.0.1; found binutils-2.30-117.0.3.el8-x86_64. Passed
Checking for compat-libcap1-1.10; Not found. Failed
Checking for compat-libstdc++-33-3.2.3-x86_64; Not found. Failed
Checking for libgcc-4.8.2-x86_64; found libgcc-8.5.0-16.0.2.el8_7-x86_64. Passed
Checking for libstdc++-4.8.2-x86_64; found libstdc++-8.5.0-16.0.2.el8_7-x86_64. Passed
Checking for libstdc++-devel-4.8.2-x86_64; found libstdc++-devel-8.5.0-16.0.2.el8_7-x86_64. Passed
Checking for sysstat-10.1.5; found sysstat-11.7.3-7.0.1.el8_7.1-x86_64. Passed
Checking for gcc-4.8.2; found gcc-8.5.0-16.0.2.el8_7-x86_64. Passed
Checking for gcc-c++-4.8.2; found gcc-c++-8.5.0-16.0.2.el8_7-x86_64. Passed
Checking for ksh-...; found ksh-20120801-257.0.1.el8-x86_64. Passed
Checking for make-3.82; found make-1:4.2.1-11.el8-x86_64. Passed
Checking for glibc-2.17-x86_64; found glibc-2.28-211.0.1.el8-x86_64. Passed
Checking for glibc-devel-2.17-x86_64; found glibc-devel-2.28-211.0.1.el8-x86_64. Passed
Checking for libaio-0.3.109-x86_64; found libaio-0.3.112-1.el8-x86_64. Passed
Checking for libaio-devel-0.3.109-x86_64; found libaio-devel-0.3.112-1.el8-x86_64. Passed
Check complete. The overall result of this check is: Failed
Problem: Some recommended packages are missing (see above).
Recommendation: You may actually have installed packages which have obsoleted these, in which case you can successfully continue with the install. If you have not, it is recommended that you do not continue. Refer to the product release notes to find out how to get the missing packages and update the system.
Error: Check:Packages failed.
Starting check : Kernel
Checking for VERSION=3.10.0; found VERSION=5.15.0-100.96.32.el8uek.x86_64. Passed
Checking for hardnofiles=4096; found hardnofiles=262144. Passed
Checking for softnofiles=4096; found softnofiles=262144. Passed
Check complete. The overall result of this check is: Passed
Kernel Check: Success.
Starting check : TotalMemory
Expected result: 1024MB
Actual Result: 15704MB
Check complete. The overall result of this check is: Passed
TotalMemory Check: Success.
Starting check : CheckJDKVersion
Expected result: 1.8.0_341
Actual Result: 1.8.0_341
Check complete. The overall result of this check is: Passed
CheckJDKVersion Check: Success.
Prereq failed. Aborting Install
Sep 10, 2025 10:35:04 AM oracle.as.install.engine.modules.configuration.ConfigurationModule onExit
INFO: Cleaning up Configuration Module
The log(s) can be found here: /tmp/OraInstall2025-09-10_10-34-53AM.
ERROR: Installer execution failed (1).
This upgrade might work for you right away.
If it fails due to missing packages (As it was the case for me here), follow Doc ID 3060775.1 and rerun the installer with the 31190532 patch metadata:
We restart the client upgrade with -prereqConfigLoc clause :
[oracle@Machine1 34761383]$ ./fmw_12.2.1.19.0_dbclient_linux64.bin -invptrloc $ORACLE_HOME/oraInst.loc -silent ORACLE_HOME=$ORACLE_HOME -prereqConfigLoc /opt/software/31190532/prereq_metadata/oracle.as.install.ohs.prerequisite/prereq/ Launcher log file is /tmp/OraInstall2025-09-10_11-10-02AM/launcher2025-09-10_11-10-02AM.log. Checking if CPU speed is above 300 MHz. Actual 2893.202 MHz Passed Checking swap space: must be greater than 512 MB. Actual 4051 MB Passed Checking if this platform requires a 64-bit JVM. Actual 64 Passed (-d64 flag is not required) Checking temp space: must be greater than 300 MB. Actual 7050 MB Passed Preparing to launch the Oracle Universal Installer from /tmp/OraInstall2025-09-10_11-10-02AM Log: /tmp/OraInstall2025-09-10_11-10-02AM/install2025-09-10_11-10-02AM.log Setting ORACLE_HOME... Copyright (c) 1996, 2022, Oracle and/or its affiliates. All rights reserved. Skipping Software Updates Starting check : CertifiedVersions Expected result: One of redhat-8, redhat-7, redhat-6, oracle-6, oracle-8, oracle-7, SuSE-11, SuSE-12, SuSE-15 Actual Result: redhat-n/a Check complete. The overall result of this check is: Passed CertifiedVersions Check: Success. Starting check : Packages Checking for binutils-2.23.52.0.1; found binutils-2.30-117.0.3.el8-x86_64. Passed Checking for libcap-2.25-9; found libcap-2.48-4.el8-x86_64. Passed Checking for libstdc++-8.2.1-3.5.0.1.el8-x86_64; found libstdc++-8.5.0-16.0.2.el8_7-x86_64. Passed Checking for libgcc-4.8.2-x86_64; found libgcc-8.5.0-16.0.2.el8_7-x86_64. Passed Checking for libstdc++-devel-4.8.2-x86_64; found libstdc++-devel-8.5.0-16.0.2.el8_7-x86_64. Passed Checking for sysstat-10.1.5; found sysstat-11.7.3-7.0.1.el8_7.1-x86_64. Passed Checking for gcc-4.8.2; found gcc-8.5.0-16.0.2.el8_7-x86_64. Passed Checking for gcc-c++-4.8.2; found gcc-c++-8.5.0-16.0.2.el8_7-x86_64. Passed Checking for ksh-...; found ksh-20120801-257.0.1.el8-x86_64. Passed Checking for make-3.82; found make-1:4.2.1-11.el8-x86_64. Passed Checking for glibc-2.17-x86_64; found glibc-2.28-211.0.1.el8-x86_64. Passed Checking for glibc-devel-2.17-x86_64; found glibc-devel-2.28-211.0.1.el8-x86_64. Passed Checking for libaio-0.3.109-x86_64; found libaio-0.3.112-1.el8-x86_64. Passed Checking for libaio-devel-0.3.109-x86_64; found libaio-devel-0.3.112-1.el8-x86_64. Passed Check complete. The overall result of this check is: Passed Packages Check: Success. Starting check : Kernel Checking for VERSION=3.10.0; found VERSION=5.15.0-100.96.32.el8uek.x86_64. Passed Checking for hardnofiles=4096; found hardnofiles=262144. Passed Checking for softnofiles=4096; found softnofiles=262144. Passed Check complete. The overall result of this check is: Passed Kernel Check: Success. Starting check : TotalMemory Expected result: 1024MB Actual Result: 15704MB Check complete. The overall result of this check is: Passed TotalMemory Check: Success. Starting check : CheckJDKVersion Expected result: 1.8.0_241 Actual Result: 1.8.0_341 Check complete. The overall result of this check is: Passed CheckJDKVersion Check: Success. Validations are enabled for this session. Verifying data Copying Files Percent Complete : 10 Percent Complete : 20 Percent Complete : 30 Percent Complete : 40 Percent Complete : 50 Percent Complete : 60 Percent Complete : 70 Percent Complete : 80 Percent Complete : 90 Percent Complete : 100 The installation of Database Client 12.2.1.19.0 completed successfully. Logs successfully copied to /opt/app/oracle/oraInventory/logs.
This time the upgrade is fine.
Verify the client version (19.14 is the expected value, As per july 2025, regardless of your repository database or OMS patch level):
[oracle@Machine1 34761383]$ $ORACLE_HOME/bin/sqlplus -version SQL*Plus: Release 19.0.0.0.0 - Production Version 19.14.0.0.0 Holistic Patch 13.5.0.27 :Step 2 — Apply the Holistic Patch
Start the OMS in admin_only mode :
[oracle@Machine1 software]$ emctl start oms -admin_only Oracle Enterprise Manager Cloud Control 13c Release 5 Copyright (c) 1996, 2021 Oracle Corporation. All rights reserved. Starting Admin Server only... Admin Server Successfully Started
Ensure the OMSPatcher utility is in your path:
[oracle@Machine1 software]$ echo $ORACLE_HOME /opt/app/oracle/product/MiddleWare13cR5 [oracle@Machine1 software]$ export PATH=$PATH:/opt/app/oracle/product/MiddleWare13cR5/OMSPatcher [oracle@Machine1 software]$ which omspatcher /opt/app/oracle/product/MiddleWare13cR5/OMSPatcher/omspatcherAnalyze the Patch
We analyze the patch before applying it, the omspatcher tool is used, as for the OMS RU patching, but with the -spb_patch clause :
[oracle@Machine1 software]$ omspatcher apply /opt/software/38194776/ -spb_patch -analyze SUCCESS :: The minimum Release Update (RU) check passed for applying the given holistic patch. Minimum OMS RU Version is 13.5.0.27 JDK update log location :: /opt/app/oracle/product/MiddleWare13cR5/cfgtoollogs/omspatcher/SPB_PATCH/jdk_update_Wed_Sep_10_11_25_12_2025.log SPB JDK version :: 1.8.0.461, Env JDK version :: 1.8.0.341 The Env version is lower as compared to JDK included in patch :: JDK will be updated OPatch update log :: /opt/app/oracle/product/MiddleWare13cR5/cfgtoollogs/omspatcher/SPB_PATCH/opatch_update_Wed_Sep_10_11_25_14_2025.log The Env Opatch version :: 13.9.4.2.5 The SPB Opatch version :: 13.9.4.2.20 The Environment Opatch version is low as per OPatch included in Patch :: OPatch will be updated OMSPatcher analyze log file :: /opt/app/oracle/product/MiddleWare13cR5/cfgtoollogs/omspatcher/SPB_PATCH/omspatcher_analyze_Wed_Sep_10_11_25_15_2025.log Running Command :: /opt/app/oracle/product/MiddleWare13cR5/OPatch/opatch lspatches -oh /opt/app/oracle/product/MiddleWare13cR5 Running Command :: /opt/app/oracle/product/MiddleWare13cR5/OPatch/opatch prereq CheckForNoOpPatches -oh /opt/app/oracle/product/MiddleWare13cR5 -phbasefile /opt/app/oracle/product/MiddleWare13cR5/linux64_patchlist.txt -silent Running Command :: /opt/app/oracle/product/MiddleWare13cR5/OPatch/opatch prereq CheckConflictAgainstOH -oh /opt/app/oracle/product/MiddleWare13cR5 -phbasefile /opt/app/oracle/product/MiddleWare13cR5/linux64_patchlist.txt -silent OMSPatcher succeeded. [oracle@Machine1 software]$Apply the Patch
As the analysis passes successfully, we proceed to applying the patch.
This command:
* Updates JDK (e.g., to 1.8.0_461)
* Updates OPatch if needed
* Applies the SPB sub-patches automatically
* Restarts the OMS as part of the process
[oracle@Machine1 software]$ omspatcher apply /opt/software/38194776/ -spb_patch SUCCESS :: The minimum Release Update (RU) check passed for applying the given holistic patch. Minimum OMS RU Version is 13.5.0.27 JDK update log location :: /opt/app/oracle/product/MiddleWare13cR5/cfgtoollogs/omspatcher/SPB_PATCH/jdk_update_Wed_Sep_10_11_30_13_2025.log SPB JDK version :: 1.8.0.461, Env JDK version :: 1.8.0.341 The Env version is lower as compared to JDK included in patch :: JDK will be updated Updating the JDK now... Stopping the OMS... Oracle Enterprise Manager Cloud Control 13c Release 5 Copyright (c) 1996, 2021 Oracle Corporation. All rights reserved. Stopping Oracle Management Server... Oracle Management Server Already Stopped Oracle Management Server is Down JVMD Engine is Down Back_up Java folder :: /opt/app/oracle/product/MiddleWare13cR5/oracle_common/jdk_1757496623 New Java version is... java version "1.8.0_461" Java(TM) SE Runtime Environment (build 1.8.0_461-b25) Java HotSpot(TM) 64-Bit Server VM (build 25.461-b25, mixed mode) Updated Java Successfully OPatch update log :: /opt/app/oracle/product/MiddleWare13cR5/cfgtoollogs/omspatcher/SPB_PATCH/opatch_update_Wed_Sep_10_11_30_23_2025.log The Env Opatch version :: 13.9.4.2.5 The SPB Opatch version :: 13.9.4.2.20 The Environment Opatch version is low as per OPatch included in Patch :: OPatch will be updated Updating the Opatch now....... Updated Opatch Successfully....... OMSPatcher analyze log file :: /opt/app/oracle/product/MiddleWare13cR5/cfgtoollogs/omspatcher/SPB_PATCH/omspatcher_analyze_Wed_Sep_10_11_31_01_2025.log Running Command :: /opt/app/oracle/product/MiddleWare13cR5/OPatch/opatch lspatches -oh /opt/app/oracle/product/MiddleWare13cR5 Running Command :: /opt/app/oracle/product/MiddleWare13cR5/OPatch/opatch prereq CheckForNoOpPatches -oh /opt/app/oracle/product/MiddleWare13cR5 -phbasefile /opt/app/oracle/product/MiddleWare13cR5/linux64_patchlist.txt -silent Running Command :: /opt/app/oracle/product/MiddleWare13cR5/OPatch/opatch prereq CheckConflictAgainstOH -oh /opt/app/oracle/product/MiddleWare13cR5 -phbasefile /opt/app/oracle/product/MiddleWare13cR5/linux64_patchlist.txt -silent OMSPatcher Automation Tool Copyright (c) 2017, Oracle Corporation. All rights reserved. OMSPatcher version : 13.9.5.26.0 OUI version : 13.9.4.0.0 Running from : /opt/app/oracle/product/MiddleWare13cR5 Log file location : /opt/app/oracle/product/MiddleWare13cR5/cfgtoollogs/omspatcher/opatch2025-09-10_11-33-20AM_1.log OMSPatcher log file: /opt/app/oracle/product/MiddleWare13cR5/cfgtoollogs/omspatcher/SPB_PATCH/omspatcher_2025-09-10_11-33-21AM_apply.log Please enter OMS weblogic admin server URL(t3s://Machine1.sig-ge.ch:7102):> Please enter OMS weblogic admin server username(weblogic):> Please enter OMS weblogic admin server password:> Performing prerequisites..... The following sub-patches are already applied to the OMS system: 36910011 The following sub-patches will be applied to the OMS system: 38015786 34809489 35965629 1221426 37894414 36789759 38108218 37925688 38051795 36905789 37894601 36316422 38042142 34065178 38073767 37074199 37887285 37284722 38156117 38194914 Loaded SPB properties successfully Stopping the oms.... Oracle Enterprise Manager Cloud Control 13c Release 5 Copyright (c) 1996, 2021 Oracle Corporation. All rights reserved. Stopping Oracle Management Server... WebTier Successfully Stopped Oracle Management Server Successfully Stopped AdminServer Successfully Stopped Oracle Management Server is Down JVMD Engine is Down Running the command :: /opt/app/oracle/product/MiddleWare13cR5/OPatch/opatch napply -oh /opt/app/oracle/product/MiddleWare13cR5 -phbasefile /opt/app/oracle/product/MiddleWare13cR5/linux64_patchlist.txt -silent Log file to be checked to get the list of applied patches : /opt/app/oracle/product/MiddleWare13cR5/cfgtoollogs/opatch/opatch2025-09-10_11-41-24AM_1.log Command ran successfully Starting the oms.... Oracle Enterprise Manager Cloud Control 13c Release 5 Copyright (c) 1996, 2021 Oracle Corporation. All rights reserved. Starting Oracle Management Server... WebTier Successfully Started Oracle Management Server Successfully Started Oracle Management Server is Up JVMD Engine is Up Log file location: /opt/app/oracle/product/MiddleWare13cR5/cfgtoollogs/omspatcher/SPB_PATCH/omspatcher_2025-09-10_11-33-21AM_apply.log OMSPatcher succeeded.Conclusion
Patching OEM used to be tedious, with separate updates for OMS, Agents, WebLogic, and JDK — often leading to inconsistent environments or outdated components. And ignoring some of these patches is a security risk.
The Holistic Patch simplifies the post-OMS patching into one coordinated update.
If you’re maintaining Oracle Enterprise Manager, adopting the holistic patching approach is highly recommended.
L’article Oracle OEM Holistic Patch est apparu en premier sur dbi Blog.
RAG Series – Adaptive RAG, understanding Confidence, Precision & nDCG
In this RAG series we tried so far to introduce new concepts of the RAG workflow each time. This new article is going to introduce also new key concepts at the heart of Retrieval. Adaptive RAG will allow us to talk about measuring the quality of the retrieved data and how we can leverage it to push our optimizations further.
A now famous study from MIT is stating how 95% of organizations fail to get ROI within the 6 months of their “AI projects”. Although we could argue about the relevancy of the study and what it actually measured, one of the key element to have a successful implementation is measurement.
An old BI principle is to know your KPI, what it really measures but also when it fails to measure. For example if you would use the speedometer on your dashboard’s car to measure the speed at which you are going, you’d be right as long as the wheels are touching the ground. So with that in mind, let’s see how we can create smart and reliable retrieval.
Hybrid search significantly improves retrieval quality by combining dense semantic vectors with sparse lexical signals. However, real-world queries vary:
- Some are factual, asking for specific names, numbers, or entities.
- Others are conceptual, exploring ideas, reasons, or relationships.
A single static weighting between dense and sparse methods cannot perform optimally across all query types.
Adaptive RAG introduces a lightweight classifier that analyzes each query to determine its type and dynamically adjusts the hybrid weights before searching.
For example:
This dynamic weighting ensures that each search leverages the right signals:
- Sparse when exact matching matters.
- Dense when semantic similarity matters.
Under the hood, our AdaptiveSearchEngine wraps dense and sparse retrieval modules. Before executing, it classifies the query, assigns weights, and fuses the results via a weighted Reciprocal Rank Fusion (RRF), giving us the best of both worlds — adaptivity without complexity.
Once we make retrieval adaptive, the next challenge is trust. How confident are we in the results we just returned?
Confidence from ClassificationEach query classification includes a confidence score (e.g., 0.92 “factual” vs 0.58 “conceptual”).
When classification confidence is low, Adaptive RAG defaults to a balanced retrieval (dense 0.5, sparse 0.5) — avoiding extreme weighting that might miss relevant content.
We also compute confidence based on retrieval statistics:
- The similarity gap between the first and second ranked results (large gap = high confidence).
- Average similarity score of the top-k results.
- Ratio of sparse vs dense agreement (when both find the same document, confidence increases).
These metrics are aggregated into a normalized confidence score between 0 and 1:
def compute_confidence(top_scores, overlap_ratio):
sim_conf = min(1.0, sum(top_scores[:3]) / 3)
overlap_conf = 0.3 + 0.7 * overlap_ratio
return round((sim_conf + overlap_conf) / 2, 2)
If confidence < 0.5, the system triggers a fallback strategy:
- Expands
top_kresults (e.g., from 10 → 30). - Broadens search to both dense and sparse equally.
- Logs the event for later evaluation.
The retrieval API now returns a structured response:
{
"query": "When was PostgreSQL 1.0 released?",
"query_type": "factual",
"confidence": 0.87,
"precision@10": 0.8,
"recall@10": 0.75
}
This allows monitoring not just what was retrieved, but how sure the system is. Enabling alerting, adaptive reruns, or downstream LLM prompt adjustments (e.g., “Answer cautiously” when confidence < 0.6).
Evaluating Quality with nDCGPrecision and recall are fundamental metrics for retrieval systems, but they don’t consider the order of results. If a relevant document appears at rank 10 instead of rank 1, the user experience is still poor even if recall is high.
That’s why we now add nDCG@k (normalized Discounted Cumulative Gain) — a ranking-aware measure that rewards systems for ordering relevant results near the top.
The idea:
- DCG@k evaluates gain by position:
- nDCG@k normalizes this against the ideal order (IDCG):
A perfect ranking yields nDCG = 1.0. Poorly ordered but complete results may still have high recall, but lower nDCG.
In practice, we calculate nDCG@10 for each query and average it over the dataset.
Our evaluation script (lab/04_evaluate/metrics.py) integrates this directly:
from evaluation import ndcg_at_k
score = ndcg_at_k(actual=relevant_docs, predicted=retrieved_docs, k=10)
print(f"nDCG@10: {score:.3f}")
These results confirm that adaptive weighting not only improves raw accuracy but also produces better-ranked results, giving users relevant documents earlier in the list.
Implementation in our LABYou can explore the implementation in the GitHub repository:
git clone https://github.com/boutaga/pgvector_RAG_search_lab
cd pgvector_RAG_search_lab
Key components:
lab/04_search/adaptive_search.py— query classification, adaptive weights, confidence scoring.lab/04_evaluate/metrics.py— precision, recall, and nDCG evaluation.- Streamlit UI (
streamlit run streamlit_demo.py) — visualize retrieved chunks, scores, and confidence in real time.
Example usage:
python lab/04_search/adaptive_search.py --query "Who invented SQL?"
Output:
Query type: factual (0.91 confidence)
Dense weight: 0.3 | Sparse weight: 0.7
Precision@10: 0.82 | Recall@10: 0.77 | nDCG@10: 0.84
This feedback loop closes the gap between research and production — making RAG not only smarter but measurable.
What is “Relevance”?When we talk about precision, recall, or nDCG, all three depend on one hidden thing:
a ground truth of which documents are relevant for each query.
There are two main ways to establish that ground truth:
ApproachWho decides relevanceProsConsHuman labelingExperts mark which documents correctly answer each queryMost accurate; useful for benchmarksExpensive and slowAutomated or LLM-assisted labelingAn LLM (or rules) judges if a retrieved doc contains the correct answerScalable and repeatableRisk of bias / noiseIn some business activity you are almost forced to use human labeling because the business technicalities are so deep that automating it is hard. Labeling can be slow and expensive for a business but I learned that it also is a way to introduce change management towards AI workflow by enabling key employees of the company to participate and build a solution with their expertise and without going through a harder project of asking to an external organization to create specific business logic into a software that was never made to handle it in the first place. As a DBA, I witnessed business logic move away from databases towards ORMs and application code and this time the business logic is going towards AI workflow. Starting this human labeling project my be the first step towards it and guarantees solid foundations.
Managers need to keep in mind that AI workflows are not just a technical solution, they are social-technical framework to allow organizational growth. You can’t just ship an AI chatbot into an app and expect 10x returns with minimal effort, this is a simplistic state of mind that already cost billions according the MIT study.
In a research setup (like your pgvector_RAG_search_lab), you can mix both approach:
- Start with a seed dataset of
(query, relevant_doc_ids)pairs (e.g. small set labeled manually). - Use the LLM to extend or validate relevance judgments automatically.
For example:
prompt = f"""
Query: {query}
Document: {doc_text[:2000]}
Is this document relevant to answering the query? (yes/no)
"""
llm_response = openai.ChatCompletion.create(...)
label = llm_response['choices'][0]['message']['content'].strip().lower() == 'yes'
Then you store that in a simple table or CSV:
query_iddoc_idrelevant1101true1102false2104true Precision & Recall in PracticeOnce you have that table of true relevances, you can compute:
- Precision@k → “Of the top k documents I retrieved, how many were actually relevant?”
- Recall@k → “Of all truly relevant documents, how many did I retrieve in my top k?”
They’re correlated but not the same:
- High precision → few false positives.
- High recall → few false negatives.
For example:
QueryRetrieved docs (top 5)True relevantPrecision@5Recall@5“Who founded PostgreSQL?”[d3, d7, d9, d1, d4][d1, d4]0.41.0You got both relevant docs (good recall = 1.0), but only 2 of the 5 retrieved were correct (precision = 0.4).
Why nDCG is NeededPrecision and recall only measure which docs were retrieved, not where they appeared in the ranking.
nDCG@k adds ranking quality:
- Each relevant document gets a relevance grade (commonly 0, 1, 2 — irrelevant, relevant, highly relevant).
- The higher it appears in the ranked list, the higher the gain.
So if a highly relevant doc is ranked 1st, you get more credit than if it’s ranked 10th.
In your database, you can store relevance grades in a table like:
query_iddoc_idrel_grade110121102111030Then your evaluator computes:
import math
def dcg_at_k(relevances, k):
return sum((2**rel - 1) / math.log2(i+2) for i, rel in enumerate(relevances[:k]))
def ndcg_at_k(actual_relevances, k):
ideal = sorted(actual_relevances, reverse=True)
return dcg_at_k(actual_relevances, k) / dcg_at_k(ideal, k)
You do need to keep track of rank (the order in which docs were returned).
In PostgreSQL, you could log that like:
Then it’s easy to run SQL to evaluate:
SELECT query_id,
SUM((POWER(2, rel_grade) - 1) / LOG(2, rank + 1)) AS dcg
FROM eval_results
WHERE rank <= 10
GROUP BY query_id;
In a real system (like your Streamlit or API demo), you can:
- Log each retrieval attempt (query, timestamp, ranking list, scores, confidence).
- Periodically recompute metrics (precision, recall, nDCG) using a fixed ground-truth set.
This lets you track if tuning (e.g., changing dense/sparse weights) is improving performance.
Structure of your evaluation log table could be:
run_idquery_idmethodrankdoc_idscoreconfidencerel_grade2025-10-12_011adaptive_rrf11010.920.8722025-10-12_011adaptive_rrf21020.850.871From there, you can generate:
- nDCG@10 trend over runs (e.g., in Prometheus or Streamlit chart)
- Precision vs Confidence correlation
- Recall improvements per query type
⚠️ Note: While nDCG is a strong metric for ranking quality, it’s not free from bias. Because it normalizes per query, easier questions (with few relevant documents) can inflate the average score. In our lab, we mitigate this by logging both raw DCG and nDCG, and by comparing results across query categories (factual vs conceptual vs exploratory). This helps ensure improvements reflect true retrieval quality rather than statistical artifacts.
Human + LLM Hybrid Evaluation (Practical Middle Ground)For your PostgreSQL lab setup:
- Label a small gold set manually (e.g., 20–50 queries × 3–5 relevant docs each).
- For larger coverage, use the LLM as an auto-grader.
You can even use self-consistency: ask the LLM to re-evaluate relevance twice and keep consistent labels only.
This gives you a semi-automated evaluation dataset, good enough to monitor:
- Precision@10
- Recall@10
- nDCG@10 over time
Through Adaptive RAG, we’ve transformed retrieval from a static process into a self-aware one.
- Precision increased by ~6–7%, especially for conceptual queries.
- Recall improved by ~8% for factual questions thanks to better keyword anchoring.
- nDCG@10 rose from 0.75 → 0.82, confirming that relevant results are appearing earlier.
- Confidence scoring provides operational visibility: we now know when the system is uncertain, enabling safe fallbacks and trust signals.
The combination of adaptive routing, confidence estimation, and nDCG evaluation makes this pipeline suitable for enterprise-grade RAG use cases — where explainability, reliability, and observability are as important as accuracy.
Conclusion and Next StepsAdaptive RAG is the bridge between smart retrieval and reliable retrieval.
By classifying queries, tuning dense/sparse balance dynamically, and measuring ranking quality with nDCG, we now have a system that understands what kind of question it’s facing and how well it performed in answering it.
This version of the lab introduces the first metrics-driven feedback loop for RAG in PostgreSQL:
- Retrieve adaptively,
- Measure precisely,
- Adjust intelligently.
In the next part, we’ll push even further — introducing Agentic RAG, and how it plans and executes multi-step reasoning chains to improve retrieval and answer quality even more.
Try Adaptive RAG in the pgvector_RAG_search_lab repository, explore your own datasets, and start measuring nDCG@10 to see how adaptive retrieval changes the game.
L’article RAG Series – Adaptive RAG, understanding Confidence, Precision & nDCG est apparu en premier sur dbi Blog.
How to check if my Oracle dbhome is RO or RW?
As you might know, RO (Read Only) dbhome was introduced in Oracle 18c and became default configuration in Oracle 21c. Oracle came back with RW (Read Write) dbhome as default configuration in 23ai. It might be interesting to know how we can check if the oracle dbhome is RO (Read Only) or RW (Read Write). I had to recently look for it and just wanted to share it through this blog.
EnvironmentFor my test I used a FREE Oracle 23ai version. Here are my ORACLE_HOME and my ORACLE_BASE environment:
oracle@vmdmkdev:~/ [FREE (CDB$ROOT)] echo $ORACLE_HOME /opt/oracle/product/23ai/dbhomeFree oracle@vmdmkdev:~/ [FREE (CDB$ROOT)] echo $ORACLE_BASE /opt/oracle
We know that for RW dbhome the dbs folder will be used in $ORACLE_HOME and for RO in $ORACLE_BASE.
ORACLE_BASE_HOME will be $ORACLE_HOME for RW and $ORACLE_BASE/homes/<HOME_NAME> for RO.
I did not activate RO on my dbhome, so I should not have so far any dbs or homes directory in my $ORACLE_BASE:
oracle@vmdmkdev:~/ [rdbms2300] ls $ORACLE_BASE admin audit cfgtoollogs diag oraInventory product
ROOH stands for Read Only Oracle HOME.
Activate ROOHI will active ROOH for my dbhome.
oracle@vmdmkdev:~/ [rdbms2300] roohctl -enable Enabling Read-Only Oracle home. Cannot enable Read-Only Oracle home in a configured Oracle home. The Oracle Home is configured with databases 'FREE'.
I still have my FREE instance database using the dbhome. I have just updated oratab file to comment the database.
oracle@vmdmkdev:~/ [rdbms2300] vi /etc/oratab
And ran roohctl command again, which this time will be successful.
oracle@vmdmkdev:~/ [rdbms2300] roohctl -enable Enabling Read-Only Oracle home. Update orabasetab file to enable Read-Only Oracle home. Orabasetab file has been updated successfully. Create bootstrap directories for Read-Only Oracle home. Bootstrap directories have been created successfully. Bootstrap files have been processed successfully. Bootstrap files have been processed successfully. Read-Only Oracle home has been enabled successfully. Check the log file /opt/oracle/cfgtoollogs/roohctl/roohctl-251003PM023449.log for more details.
Now I have got a homes folder in $ORACLE_BASE as well as a dbs one.
oracle@vmdmkdev:~/ [rdbms2300] ls $ORACLE_BASE admin audit cfgtoollogs dbs diag homes oraInventory product
Contents of homes folder:
oracle@vmdmkdev:~/ [rdbms2300] ls $ORACLE_BASE/homes OraDBHome23aiFree oracle@vmdmkdev:~/ [rdbms2300] ls $ORACLE_BASE/homes/OraDBHome23aiFree/ assistants dbs drdaas hs install mgw network rdbms oracle@vmdmkdev:~/ [rdbms2300] ls $ORACLE_BASE/homes/OraDBHome23aiFree/dbs oracle@vmdmkdev:~/ [rdbms2300]
My dbs folder in $ORACLE_HOME remains, of course, unchanged:
oracle@vmdmkdev:~/ [rdbms2300] cdh oracle@vmdmkdev:/opt/oracle/product/23ai/dbhomeFree/ [rdbms2300] cd dbs oracle@vmdmkdev:/opt/oracle/product/23ai/dbhomeFree/dbs/ [rdbms2300] ls hc_FREE.dat init.ora lkFREE_SITE1 orapwFREE spfileFREE.oraDeactivate ROOH again
Let’s deactivate ROOH again and make the Oracle Home RW again to start all the tests.
oracle@vmdmkdev:/opt/oracle/product/23ai/dbhomeFree/dbs/ [rdbms2300] roohctl -disable Disabling Read-Only Oracle home. Update orabasetab file to disable Read-Only Oracle home. Orabasetab file has been updated successfully. Read-Only Oracle home has been disabled successfully. Check the log file /opt/oracle/cfgtoollogs/roohctl/roohctl-251003PM024030.log for more details.
dbs folder in $ORACLE_BASE will remain unchanged.
oracle@vmdmkdev:/opt/oracle/product/23ai/dbhomeFree/dbs/ [rdbms2300] cd $ORACLE_BASE oracle@vmdmkdev:/opt/oracle/ [rdbms2300] ls admin audit cfgtoollogs dbs diag homes oraInventory productCheck dbhome status with orabasehome binary
There is a binary file, orabasehome, stored in $ORACLE_HOME/bin which will tell me if my Oracle Home is RO or RW. It will just provide the value of ORACLE_BASE_HOME. So either $ORACLE_HOME folder if it is a RW dbhome or $ORACLE_BASE/homes/<HOME_NAME> folder if it is a RO dbhome.
oracle@vmdmkdev:/opt/oracle/dbs/ [rdbms2300] $ORACLE_HOME/bin/orabasehome /opt/oracle/product/23ai/dbhomeFree oracle@vmdmkdev:/opt/oracle/dbs/ [rdbms2300]
The binary file will display in my case $ORACLE_HOME, which is correct.
Start the databaseI tested instance startup with file either in $ORACLE_HOME/dbs or $ORACLE_BASE/dbs folder.
oracle@vmdmkdev:/opt/oracle/dbs/ [FREE (CDB$ROOT)] sqh SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Fri Oct 3 14:49:46 2025 Version 23.5.0.24.07 Copyright (c) 1982, 2024, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance ORACLE instance started. Total System Global Area 1070721856 bytes Fixed Size 5368640 bytes Variable Size 335544320 bytes Database Buffers 725614592 bytes Redo Buffers 4194304 bytes Database mounted. Database opened. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down.
Database is starting correctly.
I moved the instance file from $ORACLE_HOME/dbs to $ORACLE_BASE/dbs:
oracle@vmdmkdev:/opt/oracle/dbs/ [FREE (CDB$ROOT)] ls -ltrh $ORACLE_HOME/dbs total 18M -rw-r-----. 1 oracle oinstall 3.1K May 14 2015 init.ora lrwxrwxrwx. 1 oracle oinstall 38 Sep 26 2024 orapwFREE -> /opt/oracle/admin/FREE/pfile/orapwFREE -rw-r-----. 1 oracle oinstall 24 Sep 26 2024 lkFREE_SITE1 -rw-r-----. 1 oracle oinstall 4.5K Oct 3 14:49 spfileFREE.ora -rw-r-----. 1 oracle oinstall 18M Oct 3 14:59 snapcf_FREE.f -rw-rw----. 1 oracle oinstall 1.6K Oct 3 15:06 hc_FREE.dat oracle@vmdmkdev:/opt/oracle/dbs/ [FREE (CDB$ROOT)] ls -ltrh $ORACLE_BASE/dbs total 0 oracle@vmdmkdev:/opt/oracle/dbs/ [FREE (CDB$ROOT)] mv $ORACLE_HOME/dbs/* $ORACLE_BASE/dbs oracle@vmdmkdev:/opt/oracle/dbs/ [FREE (CDB$ROOT)] ls -ltrh $ORACLE_HOME/dbs total 0 oracle@vmdmkdev:/opt/oracle/dbs/ [FREE (CDB$ROOT)] ls -ltrh $ORACLE_BASE/dbs total 18M -rw-r-----. 1 oracle oinstall 3.1K May 14 2015 init.ora lrwxrwxrwx. 1 oracle oinstall 38 Sep 26 2024 orapwFREE -> /opt/oracle/admin/FREE/pfile/orapwFREE -rw-r-----. 1 oracle oinstall 24 Sep 26 2024 lkFREE_SITE1 -rw-r-----. 1 oracle oinstall 4.5K Oct 3 14:49 spfileFREE.ora -rw-r-----. 1 oracle oinstall 18M Oct 3 14:59 snapcf_FREE.f -rw-rw----. 1 oracle oinstall 1.6K Oct 3 15:06 hc_FREE.dat
Starting the database would fail:
oracle@vmdmkdev:/opt/oracle/dbs/ [FREE (CDB$ROOT)] sqh SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Fri Oct 3 15:18:36 2025 Version 23.5.0.24.07 Copyright (c) 1982, 2024, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file '/opt/oracle/product/23ai/dbhomeFree/dbs/initFREE.ora' SQL>
So Oracle is looking only for instance file in $ORACLE_HOME/dbs when configured in RW mode, which makes sense.
I moved database’s instance files again to $ORACLE_HOME/dbs, and delete $ORACLE_BASE/dbs and $ORACLE_BASE/homes.
oracle@vmdmkdev:/opt/oracle/ [FREE (CDB$ROOT)] mv $ORACLE_BASE/dbs/* $ORACLE_HOME/dbs/ oracle@vmdmkdev:/opt/oracle/ [FREE (CDB$ROOT)] ls $ORACLE_BASE/dbs/ oracle@vmdmkdev:/opt/oracle/ [FREE (CDB$ROOT)] ls $ORACLE_HOME/dbs/ hc_FREE.dat init.ora lkFREE_SITE1 orapwFREE snapcf_FREE.f spfileFREE.ora oracle@vmdmkdev:/opt/oracle/ [FREE (CDB$ROOT)] rmdir $ORACLE_BASE/dbs/ oracle@vmdmkdev:/opt/oracle/ [FREE (CDB$ROOT)] rm -rf $ORACLE_BASE/homesActivate ROOH again
I activated ROOH for the dbhome and checked dbs and homes folders to be created again.
oracle@vmdmkdev:~/ [FREE (CDB$ROOT)] roohctl -enable Enabling Read-Only Oracle home. Update orabasetab file to enable Read-Only Oracle home. Orabasetab file has been updated successfully. Create bootstrap directories for Read-Only Oracle home. Bootstrap directories have been created successfully. Bootstrap files have been processed successfully. Bootstrap files have been processed successfully. Read-Only Oracle home has been enabled successfully. Check the log file /opt/oracle/cfgtoollogs/roohctl/roohctl-251003PM032952.log for more details. oracle@vmdmkdev:~/ [FREE (CDB$ROOT)] ls -l $ORACLE_BASE | grep -iE 'dbs|homes' drwxr-x---. 2 oracle oinstall 6 Oct 10 15:39 dbs drwxr-x---. 3 oracle oinstall 31 Oct 10 15:39 homes oracle@vmdmkdev:~/ [FREE (CDB$ROOT)]Check dbhome status with orabasehome binary
Binary file is now showing $ORACLE_BASE/homes/HOME_NAME, so RO is really activated on my dbhome.
oracle@vmdmkdev:~/ [FREE (CDB$ROOT)] $ORACLE_HOME/bin/orabasehome /opt/oracle/homes/OraDBHome23aiFree oracle@vmdmkdev:~/ [FREE (CDB$ROOT)]Start the database
Starting the database would fail, as there is no instance file in $ORACLE_BASE/dbs folder.
oracle@vmdmkdev:~/ [FREE (CDB$ROOT)] sqh SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Fri Oct 3 15:30:55 2025 Version 23.5.0.24.07 Copyright (c) 1982, 2024, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file '/opt/oracle/dbs/initFREE.ora' SQL>
As expected…
By the way, the inventory.xml file is not displaying anything about RW or RO.
Let’s move the instance files from $ORACLE_HOME/dbs to $ORACLE_BASE/dbs:
oracle@vmdmkdev:~/ [FREE (CDB$ROOT)] ls -l $ORACLE_BASE/dbs total 0 oracle@vmdmkdev:~/ [FREE (CDB$ROOT)] ls -l $ORACLE_HOME/dbs total 18340 -rw-rw----. 1 oracle oinstall 1544 Oct 3 15:06 hc_FREE.dat -rw-r-----. 1 oracle oinstall 3079 May 14 2015 init.ora -rw-r-----. 1 oracle oinstall 24 Sep 26 2024 lkFREE_SITE1 lrwxrwxrwx. 1 oracle oinstall 38 Sep 26 2024 orapwFREE -> /opt/oracle/admin/FREE/pfile/orapwFREE -rw-r-----. 1 oracle oinstall 18759680 Oct 3 14:59 snapcf_FREE.f -rw-r-----. 1 oracle oinstall 4608 Oct 3 14:49 spfileFREE.ora oracle@vmdmkdev:~/ [FREE (CDB$ROOT)] mv $ORACLE_HOME/dbs/* $ORACLE_BASE/dbs oracle@vmdmkdev:~/ [FREE (CDB$ROOT)] ls -l $ORACLE_BASE/dbs total 18340 -rw-rw----. 1 oracle oinstall 1544 Oct 3 15:06 hc_FREE.dat -rw-r-----. 1 oracle oinstall 3079 May 14 2015 init.ora -rw-r-----. 1 oracle oinstall 24 Sep 26 2024 lkFREE_SITE1 lrwxrwxrwx. 1 oracle oinstall 38 Sep 26 2024 orapwFREE -> /opt/oracle/admin/FREE/pfile/orapwFREE -rw-r-----. 1 oracle oinstall 18759680 Oct 3 14:59 snapcf_FREE.f -rw-r-----. 1 oracle oinstall 4608 Oct 3 14:49 spfileFREE.ora oracle@vmdmkdev:~/ [FREE (CDB$ROOT)] ls -l $ORACLE_HOME/dbs total 0 oracle@vmdmkdev:~/ [FREE (CDB$ROOT)]
And database can be started:
oracle@vmdmkdev:~/ [FREE (CDB$ROOT)] sqh SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Fri Oct 3 15:48:23 2025 Version 23.5.0.24.07 Copyright (c) 1982, 2024, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance ORACLE instance started. Total System Global Area 1070721856 bytes Fixed Size 5368640 bytes Variable Size 335544320 bytes Database Buffers 725614592 bytes Redo Buffers 4194304 bytes Database mounted. Database opened. SQL>To wrap up…
If dbhome in RW or RO it looks in appropriate dbs directory, $ORACLE_HOME for RW and $ORACLE_BASE for RO.
Oracle inventory will not give us more details about if the Oracle dbhome is RW or RO.
orabasehome binary is the solution to know if the Oracle dbhome is in RW or RO.
L’article How to check if my Oracle dbhome is RO or RW? est apparu en premier sur dbi Blog.
Possible to relocate a PDB on ExaCC with move option?
In some of my previous blogs, I showed how we were migrating on-premise databases to the ExaCC using Oracle ZDM at one of our customer. I also explained in some article how it was important in some case to relocate the PDB in a final CDB. Relocating a PDB on ExaCC is usually done using dbaascli pdb relocate command. The only drawback of this way, is that dbaascli is doing a copy of the datafiles during the relocate, which for a huge terabytes database, is taking time. In oder to minimise the downtime, I had to study another way to relocate the pdb, by moving the datafiles instead of doing a copy, and could manually relocate it in a few minutes. In this blog, I would like to share with you my findings and hoping it can help some of you.
In my case, I would like to relocate PDB, named PDB_DRY_999T, from the source CDB, named CDB_CHZ2, into the target CDB, named CDBTGT_CHZ2.
Source CDB information:
oracle@exacc-cl01n1:~/ [CDB1 (CDB$ROOT)] CDB1 *********************************** INSTANCE_NAME : CDB1 DB_NAME : CDB DB_UNIQUE_NAME : CDB_CHZ2 STATUS : OPEN READ WRITE LOG_MODE : ARCHIVELOG USERS/SESSIONS : 2/9 DATABASE_ROLE : PRIMARY FLASHBACK_ON : YES FORCE_LOGGING : YES VERSION : 19.26.0.0.0 CDB_ENABLED : YES PDBs : PDB_DRY_999T PDB$SEED ***********************************
Target CDB information:
oracle@exacc-cl01n1:~/ [CDB1 (CDB$ROOT)] CDBTGT1 *********************************** INSTANCE_NAME : CDBTGT1 DB_NAME : CDBTGT DB_UNIQUE_NAME : CDBTGT_CHZ2 STATUS : OPEN READ WRITE LOG_MODE : ARCHIVELOG USERS/SESSIONS : 2/10 DATABASE_ROLE : PRIMARY FLASHBACK_ON : YES FORCE_LOGGING : YES VERSION : 19.26.0.0.0 CDB_ENABLED : YES PDBs : PDB$SEED ***********************************
Knowing we are using TDE with ExaCC, it will be needed to export and import the encryption key. I’m using the FORCE option during export and import of the encryption key because I’m using AUTO LOGIN.
Export the encryption key on the PDB from the source CDB_CHZ2Let’s connect to the PDB and check encryption.
oracle@exacc-cl01n1:~/ [CDB1 (CDB$ROOT)] sqh
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Aug 29 10:05:55 2025
Version 19.26.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.26.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB_DRY_999T READ WRITE NO
SQL> alter session set container=PDB_DRY_999T;
Session altered.
SQL> set lines 300 pages 500
SQL> col WRL_PARAMETER format a30
SQL> select WRL_PARAMETER, WRL_TYPE,WALLET_TYPE, status from V$ENCRYPTION_WALLET;
WRL_PARAMETER WRL_TYPE WALLET_TYPE STATUS
------------------------------ -------------------- -------------------- ------------------------------
FILE AUTOLOGIN OPEN
SQL> col status for a20
SQL> col name for a30
SQL> select a.con_id, name, status, keystore_mode from v$pdbs a, v$encryption_wallet b where a.con_id=b.con_id;
CON_ID NAME STATUS KEYSTORE
---------- ------------------------------ -------------------- --------
3 PDB_DRY_999T OPEN UNITED
We are in united mode so all cdb and pdb encryption keys are stored in same keystore.
If we try to export the encryption key, we will get an ORA-46659 error:
SQL> ADMINISTER KEY MANAGEMENT EXPORT ENCRYPTION KEYS WITH SECRET "********************" to '/home/oracle/mwagner/TDE_for_unplug/PDB_DRY_999T_export.p12' FORCE keystore IDENTIFIED BY "********************"; ADMINISTER KEY MANAGEMENT EXPORT ENCRYPTION KEYS WITH SECRET "********************" to '/home/oracle/mwagner/TDE_for_unplug/PDB_DRY_999T_export.p12' FORCE keystore IDENTIFIED BY "********************" * ERROR at line 1: ORA-46659: master keys for the given PDB not found
Let’s create a new master key for the PDB.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB_DRY_999T READ WRITE NO
SQL> ADMINISTER KEY MANAGEMENT SET KEY FORCE KEYSTORE IDENTIFIED BY "********************" WITH BACKUP USING 'pre-unplug-PDB_DRY_999T_20250829';
keystore altered.
SQL>
And now we can export the pdb encryption key:
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB_DRY_999T READ WRITE NO
SQL> ADMINISTER KEY MANAGEMENT EXPORT ENCRYPTION KEYS WITH SECRET "********************" to '/home/oracle/mwagner/TDE_for_unplug/PDB_DRY_999T_export_pdb_level.p12' FORCE keystore IDENTIFIED BY "********************";
keystore altered.
SQL>
Unplug PDB_DRY_999T PDB from source CDB
Let’s close the PDB and run ALTER PLUGGABLE DATABASE <PDB_NAME> UNPLUG INTO '<XML_FILE>' command:
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
CDB1
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB_DRY_999T READ WRITE NO
SQL> alter pluggable database PDB_DRY_999T close instances=all;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB_DRY_999T MOUNTED
SQL> ALTER PLUGGABLE DATABASE PDB_DRY_999T UNPLUG INTO '/home/oracle/mwagner/unplug_pdb/PDB_DRY_999T.xml';
Pluggable database altered.
SQL> !ls -ltrh /home/oracle/mwagner/unplug_pdb/PDB_DRY_999T.xml
-rw-r--r-- 1 oracle asmdba 2.7M Aug 29 10:50 /home/oracle/mwagner/unplug_pdb/PDB_DRY_999T.xml
SQL>
In the alert log we would see some warnings to inform that the encryption key will need to be imported. Otherwise the pdb can only be opened in restricted mode:
PDB_DRY_999T(3):KZTDE:WARNING: Detected that PDB needs to import keys from source. PDB can only open in restricted mode until import.
This is of course makes sense.
Drop the PDB but keep datafiles!!!As we are going to relocate the PDB moving the datafiles, it is VERY IMPORTANT to drop the PDB by KEEPING the datafiles.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB_DRY_999T MOUNTED
SQL> drop pluggable database PDB_DRY_999T keep datafiles;
Pluggable database dropped.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
SQL>
From the alert log file we can see that only the temporary file are dropped. This file will be recreated again by the oracle plug command.
2025-08-29T10:57:26.137914+02:00
Deleted Oracle managed file +DATAC2/CDB_CHZ2/3D6E40CA5F218D4EE063181FA10A38B0/TEMPFILE/temp.26231.1210348651
2025-08-29T10:57:26.168729+02:00
Stopped service pdb_dry_999t
If we connect to the ASM, we will see that the datafiles are still present. I still have 7.45 TB of datafiles for my PDB.
ASMCMD> du PDB_DRY_999T Used_MB Mirror_used_MB 7809404 23428212 ASMCMD>Check compatibility for plugging the PDB in the target CDB
I will now connect to the target CDB, and check if the PDB is compatible to be plug in.
I will run following SQL command to do so:
set serveroutput on
DECLARE
compatible BOOLEAN := FALSE;
BEGIN
compatible := DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
pdb_descr_file => '/home/oracle/mwagner/unplug_pdb/PDB_DRY_999T.xml');
if compatible then
DBMS_OUTPUT.PUT_LINE('Is pluggable PDB_DRY_999T compatible? YES');
else DBMS_OUTPUT.PUT_LINE('Is pluggable PDB_DRY_999T compatible? NO');
end if;
END;
Let’s connect to the target CDB and check:
oracle@exacc-cl01n1:~/ [CDB1 (CDB$ROOT)] CDBTGT1
***********************************
INSTANCE_NAME : CDBTGT1
DB_NAME : CDBTGT
DB_UNIQUE_NAME : CDBTGT_CHZ2
STATUS : OPEN READ WRITE
LOG_MODE : ARCHIVELOG
USERS/SESSIONS : 2/10
DATABASE_ROLE : PRIMARY
FLASHBACK_ON : YES
FORCE_LOGGING : YES
VERSION : 19.26.0.0.0
CDB_ENABLED : YES
PDBs : PDB$SEED
***********************************
PDB color: pdbname=mount, pdbname=open read-write, pdbname=open read-only
Statustime: 2025-08-29 11:07:00
oracle@exacc-cl01n1:~/ [CDBTGT1 (CDB$ROOT)] sqh
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Aug 29 11:07:02 2025
Version 19.26.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.26.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
SQL> set serveroutput on
SQL> DECLARE
2 compatible BOOLEAN := FALSE;
3 BEGIN
4 compatible := DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
5 pdb_descr_file => '/home/oracle/mwagner/unplug_pdb/PDB_DRY_999T.xml');
6 if compatible then
7 DBMS_OUTPUT.PUT_LINE('Is pluggable PDB_DRY_999T compatible? YES');
8 else DBMS_OUTPUT.PUT_LINE('Is pluggable PDB_DRY_999T compatible? NO');
9 end if;
10 END;
11 /
Is pluggable PDB_DRY_999T compatible? YES
PL/SQL procedure successfully completed.
SQL>
The PDB can be plug in my target CDB.
Import PDB encryption key in the target CDB keystoreWe first need to import the PDB encryption key in the CDB, otherwise the plugging command would failed with ORA-28374 error:
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
CDBTGT1
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> create pluggable database PDB_DRY_999T using '/home/oracle/mwagner/unplug_pdb/PDB_DRY_999T.xml' move;
create pluggable database PDB_DRY_999T using '/home/oracle/mwagner/unplug_pdb/PDB_DRY_999T.xml' move
*
ERROR at line 1:
ORA-28374: typed master key not found in wallet
So let’s import the PDB encryption key into the CDB:
SQL> select instance_name from v$instance; INSTANCE_NAME ---------------- CDBTGT1 SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> ADMINISTER KEY MANAGEMENT IMPORT ENCRYPTION KEYS WITH SECRET "********************" from '/home/oracle/mwagner/TDE_for_unplug/PDB_DRY_999T_export_pdb_level.p12' FORCE keystore IDENTIFIED BY "********************" with backup using 'pre-plug-PDB_DRY_999T_20250829'; keystore altered. SQL>Plug the PDB in the target CDB CDBTGT_CHZ2
Now we can plug the PDB in the target CDB:
SQL> !date
Fri Aug 29 11:27:36 CEST 2025
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
CDBTGT1
SQL> create pluggable database PDB_DRY_999T using '/home/oracle/mwagner/unplug_pdb/PDB_DRY_999T.xml' move;
Pluggable database created.
SQL> !date
Fri Aug 29 11:30:36 CEST 2025
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB_DRY_999T MOUNTED
SQL>
We can see that instead of taking several hours to relocate the PDB with dbaascli, we took about 3 minutes to relocate the PDB moving the datafiles.
We can check the alert log file for any errors. In my case, I can see the completed operation:
Completed: create pluggable database PDB_DRY_999T using '/home/oracle/mwagner/unplug_pdb/PDB_DRY_999T.xml' move
2025-08-29T11:30:52.168375+02:00
Checking the ASM I can see that the source datafile directory is now empty:
ASMCMD> pwd +DATAC2 ASMCMD> du PDB_DRY_999T Used_MB Mirror_used_MB 48780 146340 ASMCMD> cd PDB_DRY_999T ASMCMD> ls CHANGETRACKING/ DATAGUARDCONFIG/ TEMPFILE/ controlfile/ datafile/ onlinelog/ password/ ASMCMD> cd datafile ASMCMD> pwd +DATAC2/PDB_DRY_999T/datafile ASMCMD> ls ASMCMD>Start the PDB
Let’s start the PDB.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB_DRY_999T MOUNTED
SQL> alter pluggable database PDB_DRY_999T open instances=all;
Warning: PDB altered with errors.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB_DRY_999T READ WRITE YES
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB_DRY_999T READ WRITE YES
The PDB is started in restricted mode. This was given as warning in the alert log during the unplug, by informing that the encryption key needs to be imported in the PDB in order to open it without any restriction.
Let’s check the violations.
SQL> select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where status 'RESOLVED';
NAME CAUSE TYPE MESSAGE STATUS
--------------- -------------------------------------------------- --------- ------------------------------------------------------------------------------------------------------------------------ --------------------
PDB_DRY_999T Parameter WARNING CDB parameter processes mismatch: Previous 2048 Current 1000 PENDING
PDB_DRY_999T Parameter WARNING CDB parameter sga_max_size mismatch: Previous 4G Current 9G PENDING
PDB_DRY_999T Parameter WARNING CDB parameter archive_lag_target mismatch: Previous 1800 Current 0 PENDING
PDB_DRY_999T Parameter WARNING CDB parameter cluster_database mismatch: Previous FALSE Current TRUE PENDING
PDB_DRY_999T Parameter WARNING CDB parameter db_lost_write_protect mismatch: Previous 'typical' Current 'NONE' PENDING
PDB_DRY_999T Parameter WARNING CDB parameter inmemory_force mismatch: Previous 'cellmemory_level' Current 'DEFAULT' PENDING
PDB_DRY_999T Parameter WARNING CDB parameter wallet_root mismatch: Previous '/var/opt/oracle/dbaas_acfs/CDB/wallet_root' Current '/var/opt/oracle/dbaa PENDING
s_acfs/CDBTGT/wallet_root'
PDB_DRY_999T Parameter WARNING CDB parameter distributed_lock_timeout mismatch: Previous 360 Current 60 PENDING
PDB_DRY_999T Parameter WARNING CDB parameter unified_audit_common_systemlog mismatch: Previous 'LOCAL5.INFO' Current NULL PENDING
PDB_DRY_999T Parameter WARNING CDB parameter enable_ddl_logging mismatch: Previous TRUE Current FALSE PENDING
PDB_DRY_999T Parameter WARNING CDB parameter awr_pdb_max_parallel_slaves mismatch: Previous 2 Current 10 PENDING
PDB_DRY_999T Parameter WARNING CDB parameter awr_snapshot_time_offset mismatch: Previous 1000000 Current 0 PENDING
PDB_DRY_999T OPTION WARNING Database option CONTEXT mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0. PENDING
PDB_DRY_999T OPTION WARNING Database option DV mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0. PENDING
PDB_DRY_999T OPTION WARNING Database option OLS mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0. PENDING
PDB_DRY_999T OPTION WARNING Database option ORDIM mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0. PENDING
PDB_DRY_999T OPTION WARNING Database option OWM mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0. PENDING
PDB_DRY_999T OPTION WARNING Database option SDO mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0. PENDING
PDB_DRY_999T Wallet Key Needed ERROR PDB needs to import keys from source. PENDING
19 rows selected.
SQL>
Here we have:
PDB_DRY_999T Wallet Key Needed ERROR PDB needs to import keys from source.
Let’s connect to the PDB and import the encryption key:
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB_DRY_999T READ WRITE YES
SQL> alter session set container=PDB_DRY_999T;
Session altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB_DRY_999T READ WRITE YES
SQL> ADMINISTER KEY MANAGEMENT IMPORT ENCRYPTION KEYS WITH SECRET "********************" from '/home/oracle/mwagner/TDE_for_unplug/PDB_DRY_999T_export_pdb_level.p12' FORCE keystore IDENTIFIED BY "********************" with backup using 'post-plug-PDB_DRY_999T_20250829';
keystore altered.
SQL>
Restart the PDB
Let’s restart the PDB and check the status.
SQL> alter session set container=cdb$root;
Session altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB_DRY_999T READ WRITE YES
SQL> alter pluggable database PDB_DRY_999T close instances=all;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB_DRY_999T MOUNTED
SQL> alter pluggable database PDB_DRY_999T open instances=all;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB_DRY_999T READ WRITE NO
SQL>
We can see that the PDB is now opened without any restriction. Let’s check the PDB violations:
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB_DRY_999T READ WRITE NO
SQL> col name for a15
SQL> col message for a120
SQL> col cause for a50
SQL> select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where status 'RESOLVED';
NAME CAUSE TYPE MESSAGE STATUS
--------------- -------------------------------------------------- --------- ------------------------------------------------------------------------------------------------------------------------ --------------------
PDB_DRY_999T OPTION WARNING Database option CONTEXT mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0. PENDING
PDB_DRY_999T OPTION WARNING Database option DV mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0. PENDING
PDB_DRY_999T OPTION WARNING Database option OLS mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0. PENDING
PDB_DRY_999T OPTION WARNING Database option ORDIM mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0. PENDING
PDB_DRY_999T OPTION WARNING Database option OWM mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0. PENDING
PDB_DRY_999T OPTION WARNING Database option SDO mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0. PENDING
6 rows selected.
SQL>
There is no more violations.
Check the PDBCheck datafiles location:
SQL> select name from v$datafile where rownum select count(*) from v$datafile where name like '+DATAC2/CDBTGT_CHZ2/3D6E40CA5F218D4EE063181FA10A38B0/DATAFILE%';
COUNT(*)
----------
2830
SQL> select count(*) from v$datafile where name not like '+DATAC2/CDBTGT_CHZ2/3D6E40CA5F218D4EE063181FA10A38B0/DATAFILE%';
COUNT(*)
----------
0
SQL>
All good, PDB datafiles are in the expected ASM folder.
Check that there is no invalid objects:
SQL> select count(*) from dba_invalid_objects;
COUNT(*)
----------
0
And finally check that all tablespace are encrypted:
SQL> select count(*) from cdb_tablespaces a, (select x.con_id, y.ENCRYPTIONALG, x.name from V$TABLESPACE x, V$ENCRYPTED_TABLESPACES y
2 where x.ts#=y.ts# and x.con_id=y.con_id) b where a.con_id=b.con_id(+) and a.tablespace_name=b.name(+) and b.ENCRYPTIONALG='AES128';
COUNT(*)
----------
2831
SQL> select count(*) from cdb_tablespaces a, (select x.con_id, y.ENCRYPTIONALG, x.name from V$TABLESPACE x, V$ENCRYPTED_TABLESPACES y
2 where x.ts#=y.ts# and x.con_id=y.con_id) b where a.con_id=b.con_id(+) and a.tablespace_name=b.name(+) and b.ENCRYPTIONALG is null;
COUNT(*)
----------
0
SQL>
The PDB is as well registered in the listener:
oracle@exacc-cl01n1:~/ [CDBTGT1 (CDB$ROOT)] export TNS_ADMIN=/u02/app/oracle/product/19.0.0.0/dbhome_1/network/admin/listener_test/ oracle@exacc-cl01n1:~/ [CDBTGT1 (CDB$ROOT)] lsnrctl status LISTENER_TEST| grep -i PDB_DRY_999T Service "pdb_dry_999t.domain.com" has 1 instance(s). oracle@exacc-cl01n1:~/ [CDBTGT1 (CDB$ROOT)]To wrap up
Here we have a good alternative for relocating a PDB on ExaCC avoiding a copy of the datafiles. This will have the benefit to limit the maintenance windows.
L’article Possible to relocate a PDB on ExaCC with move option? est apparu en premier sur dbi Blog.
MongoDB Log Analysis : A Comprehensive Guide
Contrary to other DBMS, MongoDB self-managed deployments generate logs in a JSON format, which can be quite intimidating at first. But instead of hoping to never have to look at the logs, you can use some tools and tips to navigate them more easily and not waste time finding the information you’re looking for.
Summary Log format overviewInside /var/log/mongodb/mongod.log (or a custom path if you set one), a typical log entry looks like this (shortened, for readability) :
{"t":{"$date":"2025-03-06T14:54:28.298+01:00"},"s":"I", "c":"CONTROL", "id":8423403, "ctx":"initandlisten","msg":"mongod startup complete","attr":{"Summary of time elapsed":{"Startup from clean shutdown?":false,"Statistics":{"Set up periodic runner":"0 ms","Set up online certificate status protocol manager":"0 ms",[...],"Start transport layer":"0 ms","_initAndListen total elapsed time":"626 ms"}}}}At first glance, it’s pretty difficult to extract the essential information, but let’s see how the document looks like when it’s formatted (we’ll see how to do that later).
{
"t": {
"$date": "2025-03-06T14:54:28.298+01:00"
},
"s": "I",
"c": "CONTROL",
"id": 8423403,
"ctx": "initandlisten",
"msg": "mongod startup complete",
"attr": {
"Summary of time elapsed": {
"Startup from clean shutdown?": false,
"Statistics": {
"Set up periodic runner": "0 ms",
"Set up online certificate status protocol manager": "0 ms",
[...] # lines hidden
"Start transport layer": "0 ms",
"_initAndListen total elapsed time": "626 ms"
}
}
}
}Here is a description of the main fields of the log document :
t: Timestamp of the log entry.s: Severity code associated with the log entry (Efor error,Wfor warning,Ifor information andD1toD5for debug).c: Category of the log entry. Most common categories areCONTROL,COMMAND,ELECTION,REPL(for replication) orNETWORK. An extensive list is available in the official MongoDB documentation.id: Unique log entry ID.ctx: Thread that generated the log.msg: Usually a short message describing the log.attr: Optional additional attributes.
This will help us when looking at the logs, first with mongosh.
mongosh
You can query logs inside the MongoDB shell called mongosh. To do so, use the getLog admin command :
db.adminCommand({ getLog: "global"}); // display all log entriesAnother useful option is to view startup warnings, which will only display warning logs since last startup.
db.adminCommand({ getLog: "startupWarnings" }) // display startup warnings
{
totalLinesWritten: 2,
log: [
'{"t":{"$date":"2025-03-07T08:32:41.005+01:00"},"s":"W", "c":"NETWORK", "id":5123300, "ctx":"initandlisten","msg":"vm.max_map_count is too low","attr":{"currentValue":65530,"recommendedMinimum":102400,"maxConns":51200},"tags":["startupWarnings"]}\n',
'{"t":{"$date":"2025-03-07T08:32:41.005+01:00"},"s":"W", "c":"CONTROL", "id":8386700, "ctx":"initandlisten","msg":"We suggest setting swappiness to 0 or 1, as swapping can cause performance problems.","attr":{"sysfsFile":"/proc/sys/vm/swappiness","currentValue":60},"tags":["startupWarnings"]}\n'
],
ok: 1
}Even though this can sometimes be useful, it requires an authenticated access to the database, and it only works when the mongod process is running. You won’t be able to use this method when the database crashes, for instance. Moreover, the logs are difficult to read.
Most of the time, you will be better served by the jq utility.
jq
jq is a powerful utility that helps you navigate JSON documents, and even though it is not an official MongoDB product, you should always integrate it in your MongoDB deployments.
The first benefit of the jq command is to display MongoDB logs in a readable format :
> head -1 mongod.log | jq
{
"t": {
"$date": "2025-03-05T14:44:28.531+01:00"
},
"s": "I",
"c": "CONTROL",
"id": 23285,
"ctx": "main",
"msg": "Automatically disabling TLS 1.0"
}Of course, a single line of log will now span multiple lines in the output. But thanks to the log structure explained above, we can write our first queries with jq to filter the results and only display what’s important.
I would definitely recommend to build aliases with the following commands in order to quickly access the information that you find more valuable in the logs.
Display error messagesBy using the s field (severity), we can filter the logs to only display error messages. This is especially useful when failing to start a MongoDB instance.
jq 'select(.s == "E")' mongod.logYou can also include warnings by slightly modifying the command.
jq 'select(.s == "E" or .s == "W")' mongod.logWhen selecting fields, you should pipe the jq filtering to a json object like this one :
{time: .t["$date"], message: .msg, error: .attr.error}From now on, I will use the -c option to display the JSON in the compact format. Even in this format, logs can be readable when you select or exclude specific fields. Here, I want to select the .t["$date"], .msg and .attr.error fields. To improve the display, I will rename them :
> jq -c 'select(.s == "E") | {time: .t["$date"], message: .msg, error: .attr.error}' mongod.log
{"time":"2025-03-05T14:44:28.665+01:00","message":"WiredTiger error message","error":13}
{"time":"2025-03-05T14:44:28.665+01:00","message":"WiredTiger error message","error":13}
{"time":"2025-03-05T14:44:28.665+01:00","message":"WiredTiger error message","error":13}
{"time":"2025-03-06T10:17:07.383+01:00","message":"DBException in initAndListen, terminating","error":"Location28596: Unable to determine status of lock file in the data directory /var/lib/mongodb: boost::filesystem::status: Permission denied [system:13]: \"/var/lib/mongodb/mongod.lock\""}Similarly, you can exclude a field with the del function. For instance, this will remove the message sub-field located inside the attr field.
jq 'del(.attr.message)' mongod.logTo check for recurrent warnings or errors, you can pipe the jq output to group the results by msg content.
jq 'select(.s == "E" or .s=="W") | .msg' mongod.log | sort | uniq -c | sort -nr | headIf you want to quickly look for the number of every severity, you can do so with the s field.
> jq '.s' mongod.log | sort | uniq -c
10 "E"
3 "F"
1727 "I"
88 "W"As mentioned before, the category of the logs might be interesting to filter (only the replication logs, for instance).
jq -c 'select(.c == "REPL")' mongod.logWhether you use log rotation or not, you might want to quickly access the last minutes/hours/days of logs. With the date utility, you can retrieve the most recent logs :
jq -c --arg since "$(date -d '10 minutes ago' +%Y-%m-%dT%H:%M:%S)" 'select(.t["$date"] >= $since)' mongod.logStill filtering on the .t["$date"] field, you can also extract a portion of the logs :
jq -c 'select(.t["$date"] >= "2025-03-06T14:30:00" and .t["$date"] <= "2025-03-06T14:40:00")' mongod.logOf course, you can always use grep followed by jq to find a pattern in the logs : grep -i "pattern" mongod.log | jq
But if you want to look for a specific pattern inside a specific field, you can do so with the test function :
> jq -c 'select(.msg | test("failed to authenticate"; "i"))' mongod.log // (i option for case insensitivity)
{"t":{"$date":"2025-03-07T08:37:52.950+01:00"},"s":"I","c":"ACCESS","id":5286307,"ctx":"conn18","msg":"Failed to authenticate","attr":{"client":"xxx.xxx.xxx.xxx(ip):xxxxx(port)","isSpeculative":true,"isClusterMember":false,"mechanism":"SCRAM-SHA-256","user":"root","db":"admin","error":"AuthenticationFailed: SCRAM authentication failed, storedKey mismatch","result":18,"metrics":{"conversation_duration":{"micros":5091,"summary":{"0":{"step":1,"step_total":2,"duration_micros":62},"1":{"step":2,"step_total":2,"duration_micros":48}}}},"extraInfo":{}}}For filtering connections logs, search for the attr.remote field :
jq -c 'select(.attr.remote)' mongod.logjq
Inside the mongo shell, you can activate logging for slow queries with db.setProfilingLevel(1, <slowms>), with <slowms> being the threshold (in milliseconds) to log such queries.
Warnings related to slow queries in MongoDB :
- Once activated, the slow queries logging could slow down the database, so be very careful when activating it.
- There is a security threat when combining slow query logging and queryable encryption, since queries will not be encrypted in the
mongod.logfile.
Slow query logs look like this :
{
"t": { "$date": "2024-03-06T12:34:56.789Z" },
"s": "I",
"c": "COMMAND",
"id": 123,
"ctx": "conn20",
"msg": "Slow query",
"attr": {
"ns": "mydb.coll",
"command": { "find": "coll", "filter": { "status": "active" } },
"planSummary": "COLLSCAN",
"keysExamined": 0,
"docsExamined": 5000,
"numYields": 0,
"reslen": 2000,
"locks": { "Global": { "acquireCount": { "r": 1 } } },
"durationMillis": 150
}
}With this in mind, and with what we have already seen, you can filter the logs with the fields you want, like attr.durationMillis (duration of the query, in milliseconds), or attr.ns, which is the object on which the query is made.
For instance, if you want to retrieve slow queries above a given threshold (one second, in the example below) :
jq 'select(.attr.durationMillis >= 1000)' mongod.logOr if you want to filter slow queries on a specific database mydb and collection coll :
jq 'select(.msg == "Slow query" and .attr.ns == "mydb.coll")' mongod.logYou can also select only queries that are run on a given database mydb :
jq 'select(.msg == "Slow query" and .attr.command["$db"] == "mydb")' mongod.log
While being a bit complex at first sight, MongoDB logs are very useful if you know how to apprehend them. By leveraging the jq utility for advanced filtering, and combining it with monitoring tools, you can efficiently analyze logs and improve your efficiency as a DBA.
L’article MongoDB Log Analysis : A Comprehensive Guide est apparu en premier sur dbi Blog.
From Oracle’s PL/SQL to PostgreSQL’s PL/pgSQL – 2 – Records, outputs and constraints
In the last post we’ve learned the basics for migrating a sample procedure from PL/SQL to PL/pgSQL. In this post we’ll actually start with migrating the code. The first step in the original procedure was to iterate over the foreign key constraints of the user’s tables and to disable them:
for i in ( select table_name
, constraint_name
from user_constraints
where constraint_type ='R'
and status = 'ENABLED' )
loop
execute immediate 'alter table ' || i.table_name || ' disable constraint ' || i.constraint_name;
end loop;
dbms_output.put_line ( 'Disabled foreign key constraints');
If you try this as it is in PL/pgSQL this will fail for several reasons:
postgres=> \! cat a.sql
create or replace procedure p1() as
$$
declare
begin
for i in ( select table_name
, constraint_name
from user_constraints
where constraint_type ='R'
and status = 'ENABLED' )
loop
execute immediate 'alter table ' || i.table_name || ' disable constraint ' || i.constraint_name;
end loop;
dbms_output.put_line ( 'Disabled foreign key constraints');
end;
$$ language plpgsql;
postgres=> \i a.sql
psql:a.sql:16: ERROR: loop variable of loop over rows must be a record variable or list of scalar variables
LINE 5: for i in ( select table_name
The error message is pretty clear, we need to use a variable of type “record” if we want to iterate over a number of rows, so:
postgres=> \! cat a.sql
create or replace procedure p1() as
$$
declare
i record;
begin
for i in ( select table_name
, constraint_name
from user_constraints
where constraint_type ='R'
and status = 'ENABLED' )
loop
execute immediate 'alter table ' || i.table_name || ' disable constraint ' || i.constraint_name;
end loop;
dbms_output.put_line ( 'Disabled foreign key constraints');
end;
$$ language plpgsql;
postgres=> \i a.sql
psql:a.sql:17: ERROR: syntax error at or near "dbms_output"
LINE 14: dbms_output.put_line ( 'Disabled foreign key constraints'...
dbms_output is an Oracle package, this does not exist in PostgreSQL. We have two options:
- Create a schema called “dbms_output” and in there create a procedure “put_line” which does what we want
- Use what PL/pgSQL provides by default, and this is RAISE
(There is a third option, which is orafce, but we’re not going to look into this one)
postgres=> \! cat a.sql
create or replace procedure p1() as
$$
declare
i record;
begin
for i in ( select table_name
, constraint_name
from user_constraints
where constraint_type ='R'
and status = 'ENABLED' )
loop
execute immediate 'alter table ' || i.table_name || ' disable constraint ' || i.constraint_name;
end loop;
raise notice 'Disabled foreign key constraints';
end;
$$ language plpgsql;
postgres=> \i a.sql
CREATE PROCEDURE
Now the procedure can be created, but there are more issues to fix. If we execute the procedure we get this:
postgres=> call p1();
ERROR: relation "user_constraints" does not exist
LINE 3: from user_constraints
^
QUERY: ( select table_name
, constraint_name
from user_constraints
where constraint_type ='R'
and status = 'ENABLED' )
CONTEXT: PL/pgSQL function p1() line 5 at FOR over SELECT rows
“user_constraints” is an Oracle catalog view, and this view is of course not existing in PostgreSQL. But PostgreSQL provides the catalog table pg_constraint, so:
postgres=> \! cat a.sql
create or replace procedure p1() as
$$
declare
i record;
begin
for i in ( select conrelid::regclass as table_name
, conname as constraint_name
from pg_constraint
where contype ='f' )
loop
execute immediate 'alter table ' || i.table_name || ' disable constraint ' || i.constraint_name;
end loop;
raise notice 'Disabled foreign key constraints';
end;
$$ language plpgsql;
postgres=> \i a.sql
CREATE PROCEDURE
postgres=> call p1();
ERROR: type "immediate" does not exist
LINE 1: immediate 'alter table ' || i.table_name || ' disable constr...
^
QUERY: immediate 'alter table ' || i.table_name || ' disable constraint ' || i.constraint_name
CONTEXT: PL/pgSQL function p1() line 10 at EXECUTE
Next issue: “IMMEDIATE” does not exist in PL/pgSQL, but this is easy to fix, just remove it:
postgres=> \! cat a.sql
create or replace procedure p1() as
$$
declare
i record;
begin
for i in ( select conrelid::regclass as table_name
, conname as constraint_name
from pg_constraint
where contype ='f' )
loop
execute 'alter table ' || i.table_name || ' disable constraint ' || i.constraint_name;
end loop;
raise notice 'Disabled foreign key constraints';
end;
$$ language plpgsql;
postgres=> \i a.sql
CREATE PROCEDURE
postgres=> call p1();
ERROR: syntax error at or near "constraint"
LINE 1: alter table t2 disable constraint t2_b_fkey
^
QUERY: alter table t2 disable constraint t2_b_fkey
CONTEXT: PL/pgSQL function p1() line 10 at EXECUTE
In PostgreSQL you cannot disable constraints as you can do it in Oracle:
postgres=# alter table t2 disable
ROW LEVEL SECURITY RULE TRIGGER
The way to do this in PostgreSQL is to drop the constraints and re-create them afterwards but for this we need to store the “create” commands. One way of doing that would be something like this:
create or replace procedure p1() as
$$
declare
i record;
constraint_create text[];
tmp text;
tmp2 text;
counter int := 1;
begin
for i in ( select oid
, conrelid::regclass as table_name
, conname as constraint_name
from pg_constraint
where contype ='f' )
loop
select 'alter table ' || i.table_name || ' add constraint ' || i.constraint_name || ' ' || pg_get_constraintdef ( i.oid ) into tmp;
constraint_create[counter] := tmp;
execute 'alter table ' || i.table_name || ' drop constraint ' || i.constraint_name;
counter := counter + 1;
end loop;
raise notice 'Dropped foreign key constraints';
-- load data
-- re-create the foreign keys
foreach tmp2 in array constraint_create
loop
raise notice 'Re-creating foreign key constraint: %', tmp2;
execute tmp2;
end loop;
end;
$$ language plpgsql;
This almost completes the migration of the code but there is still the topic of “exceptions”, and that’s for the next post.
L’article From Oracle’s PL/SQL to PostgreSQL’s PL/pgSQL – 2 – Records, outputs and constraints est apparu en premier sur dbi Blog.
M-Files Electronic and Digital Signatures
Who has never during his or her business working daily life had to deal with one document signature. There is no more room for doubt concerning the usefulness of this functionality especially in ECM software like M-Files. A lot of solutions exist. Let me share with you, here, few of them that you may consider to use and integrate in your beloved M-Files in order to fulfill your needs.
M-Files out of the box signing solutionIn M-Files, an electronic signature is always tied to a workflow state transition or to an assignment completion, where end users provide system credentials to complete the action.
“HR Approval” Workflow and signing state transition example.
After providing the user credentials and the reason for transition, the signing event is logged in the event log.
Let’s pause for a moment and review the terminology used so that we are all on the same page.
Relying on Code of Federal Regulation (21 CFR Part 11) definitions, specific to electronic records and electronic signatures, including electronic submissions to the Food and Drug Administration:
- An electronic signature is computer data compilation of any symbol or series of symbols executed, adopted, or authorized by an individual to be the legally binding equivalent of the individual’s handwritten signature.
- A digital signature is an electronic signature based upon cryptographic methods of originator authentication, computed by using a set of rules and a set of parameters such that the identity of the signer and the integrity of the data can be verified.
As you may understood, legal concept comes into play to prove who signed what based on signer’s intent and consent. All gathered data around signing process is saved to M-Files database either in text properties and/or in dedicated electronic signatures objects that can be optionally rendered into a PDF.
Hence, rest assured, all M-Files signing solutions fulfill the technical requirements of common regulatory guidelines such as 21 CFR Part 11 and EU GMP Annex 11.
This said, note that PDF processing is mostly used to set up the final document signed. Possibly, additional overlays, watermark stamping and other metadata information are generated. Portable Document Format contributes heavily to stick to authenticity, conformity and falsification detection. To achieve that, a cryptography-based process is used in digital signature, ensuring signed documents authenticity, providing tampering detection and saving digital signature certificate to document files.
Let’s take a look at a few examples.
Trusted Digital SignaturePDF documents can be digitally signed with digital signature certificates. Typically, for two purposes:
– To prove the identity of the signer
– To prove that the document has not been modified after signing
There are two kinds of digital signature certificates: hardware-based and software-based.
AATL CA certificate – digital signature details
This solution is very useful to sign PDF files (only) with digital signature certificates installed on your M-Files server. This requires a license and, unfortunately, is not supported in M-Files Cloud.
M-Files for DocuSignDocuSign cloud service is a web application that can be used to digitally sign PDF documents. You still compose the document in M-Files, but then, move it to the “Sent for signing” state in dedicated workflow so it will automatically be uploaded to DocuSign to handle signing process. Of course, all signers receive an email assignment.
M-Files for Adobe Acrobat Sign
Similarly, using Adobe Acrobat Sign cloud service allows M-Files users and external persons to edit and sign documents remotely. Sender(s) and signer(s) receive e-mail notification without requiring any Adobe subscription for signers.
From an M-Files technical point of view, in both above solutions, as always, configuration is all about Workflows settings, Object types, Classes and Properties definitions. Agreed that it is managed by your Business needs.
Hence, your M-Files Business Administrator will bring all this to life, mapping required metadata properties into “M-Files for DocuSign” or “M-Files for Adobe Acrobat Sign” application vault console:
Last but not least, do not forget to consider these Signing Cloud Services solutions plans and subscriptions prerequisite. By default, licenses bound to one or another service are not integrated into M-Files add-on modules potentially subscribed. The way one may choose such or such features is totally tied in firms business processes and the amount/volume of end users documents signatures generated monthly.
Eventually, you may also consider to use additional M-Files modules such as PDF processor or Electronic Signatures from M-Files Compliance Kit. This will allow you to greatly extend your Business tasks capabilities and facilitate in a way signing process integration.
Do not hesitate to contact dbi services If you need assistance, for any M-Files support and guidance around this topic and others.
L’article M-Files Electronic and Digital Signatures est apparu en premier sur dbi Blog.
From Oracle’s PL/SQL to PostgreSQL’s PL/pgSQL – 1 – Basics
Migrating from one database system to another is not an easy task. It might be easy if you only need to migrate the data, but as soon as you have business logic in the database this can become a lot work quite fast. When you want to migrate from Oracle to PostgreSQL this means rewriting all your PL/SQL code into something which PostgreSQL understands, and this usually is PL/pgSQL as it is very close to PL/SQL. Some might say that you mustn’t put any business logic into the database at all, but this is another discussion and will not be handled here or in any of the follow posts.
There are some tools, such as ora2pg, which give you some automatic translation from PL/SQL to PL/pgSQL, but you carefully have to check the result and go through all the code anyway. ora2pg gives you a great starting point, but the works begins after the automatic translation.
In this first post, we’ll look into the very basics of both languages. The starting point in Oracle is this:
sqlplus sys@ora.it.dbi-services.com/XEPDB1 as sysdba
create user u
identified by u
default tablespace users
quota 10M on users
temporary tablespace temp;
grant create session to u;
grant create table to u;
grant create procedure to u;
exit;
sqlplus u@ora.it.dbi-services.com/XEPDB1
drop table t2;
drop table t;
create table t ( a number primary key , b number check ( b> 10 ));
create table t2 ( a number primary key, b number references t(a) );
insert into t (a,b) values (1,11);
insert into t (a,b) values (2,12);
insert into t2 (a,b) values (1,1);
insert into t2 (a,b) values (2,1);
insert into t2 (a,b) values (3,1);
insert into t2 (a,b) values (4,1);
insert into t2 (a,b) values (5,1);
insert into t2 (a,b) values (6,2);
commit;
This gives you a dedicated user which enough permissions to connect, create tables and procedures. This user is then used to create two simple tables connected by a foreign key constraint. While this is really simple, the following (and all follow up) examples are redacted and simplified real customer use cases.
The procedure to be migrated is this one:
create or replace procedure p as
ln_count number;
begin
for i in ( select table_name
, constraint_name
from user_constraints
where constraint_type ='R'
and status = 'ENABLED' )
loop
execute immediate 'alter table ' || i.table_name || ' disable constraint ' || i.constraint_name;
end loop;
dbms_output.put_line ( 'Disabled foreign keys constraints');
select count(*)
into ln_count
from t2
where b = 2;
-- some more logic around counting
--
delete
from t
where a = 2;
delete
from t2
where b = 2;
commit;
dbms_output.put_line ( 'Commit done');
for i in ( select table_name
, constraint_name
from user_constraints
where constraint_type ='R'
and status = 'ENABLED' )
loop
execute immediate 'alter table ' || i.table_name || ' disable constraint ' || i.constraint_name;
end loop;
dbms_output.put_line ( 'Enabling foreign keys constraints');
exception
when others then
for i in ( select table_name
, constraint_name
from user_constraints
where constraint_type ='R'
and status = 'ENABLED' )
loop
execute immediate 'alter table ' || i.table_name || ' disable constraint ' || i.constraint_name;
end loop;
dbms_output.put_line ( 'Enabling foreign key constraints');
raise_application_error(-50001, 'runtime_exception', true );
end;
/
Again, this is very simplified and there was more code in the real procedure, but this is enough to talk about the topic of this post.
When this procedure is executed we get the following result in Oracle:
SQL> select * from t;
A B
---------- ----------
1 11
SQL> select * from t2;
A B
---------- ----------
1 1
2 1
3 1
4 1
5 1
The starting point in PostgreSQL is this:
$ psql
create user u with login password 'u';
create schema u;
alter schema u owner to u;
\c postgres u
set search_path=u;
create table t ( a int primary key, b int check ( b > 10 ));
create table t2 ( a int primary key, b int references t(a));
insert into t (a,b) values (1,11);
insert into t (a,b) values (2,12);
insert into t2 (a,b) values (1,1);
insert into t2 (a,b) values (2,1);
insert into t2 (a,b) values (3,1);
insert into t2 (a,b) values (4,1);
insert into t2 (a,b) values (5,1);
insert into t2 (a,b) values (6,2);
… which gives you pretty much the same as in Oracle (except for the schema and integer data types).
Let’s start with migrating that procedure, step by step. The “create procedure” command in PostgreSQL works like this (there is a bit more, but it is the simplest form to start with):
create procedure p() as
$$
declare
begin
null;
end; $$ language plpgsql;
PostgreSQL uses dollar quoting which saves you from constantly escaping special characters in strings, e.g.:
postgres=> select $$""""'''''%%%&&&&&$$;
?column?
-------------------
""""'''''%%%&&&&&
(1 row)
If you have the “$” sign itself in the string you can use tags ($xx$ in the example below):
postgres=> select $xx$""""$$'''''%%%&&&&&$xx$;
?column?
---------------------
""""$$'''''%%%&&&&&
(1 row)
The same concept is used for the body (code) of user defined functions and procedures.
“language” tells PostgreSQL which language you’re writing the code in, here we use PL/pgSQL but this could also be Python, Perl or Tcl in a typical PostgreSQL installation (more languages are available as external extensions).
The “declare” block is used to declare variables you want to use in the code (more on that in a later post) and “begin” and “end” wraps the actual code.
Now, that we know the basic building blocks we can go further and start to code the procedure so that it is doing the same as the procedure in PL/SQL. This is the topic for the next post.
L’article From Oracle’s PL/SQL to PostgreSQL’s PL/pgSQL – 1 – Basics est apparu en premier sur dbi Blog.
RAG Series – Naive RAG
Since my last series on pgvector I had quite a fun time to work on RAG workflows on pgvector and learned some valuable lessons and decided to share some of it in a blog post series on the matter.
We will discover together where all RAG best practices are landing for the past 2 years and how can as a DBA or “AI workflow engineer” improve your designs to be production fit.
We start this series with Naïve RAG, this is quite known but important and foundational for the next posts of this series.
Retrieval-Augmented Generation (RAG) is a technique that combines the power of large language models (LLMs) with information retrieval. Instead of relying solely on an LLM’s internal knowledge (which may be outdated or limited, and prone to hallucinations), a RAG system retrieves relevant external documents and provides them as context for the LLM to generate a response. In practice, this means when a user asks a question, the system will retrieve a set of relevant text snippets (often from a knowledge base or database) and augment the LLM’s input with those snippets, so that the answer can be grounded in real data. This technique is key for integrating businesses or organizations data with LLMs capabilities because it allows you to implement business rules, guidelines, governance, data privacy constraints…etc.
Naïve RAG is the first logical step to understand how the retrieval part works and how it can impact the LLM output.
A RAG pipeline typically involves the following steps:
- Document Embedding Storage – Your knowledge base documents are split into chunks and transformed into vector embeddings, which are stored in a vector index or database.
- Query Embedding & Retrieval – The user’s query is converted into an embedding and the system performs a similarity search in the vector index to retrieve the top-$k$ most relevant chunks.
- Generation using LLM – The retrieved chunks (as context) plus the query are given to an LLM which generates the final answer.
Clone the repository and explore this implementation:
git clone https://github.com/boutaga/pgvector_RAG_search_lab
cd pgvector_RAG_search_lab
The lab includes:
- Streamlit interface for testing different search methods
- n8n workflows for orchestrating the RAG pipeline
- Embedding generation scripts supporting multiple models
- Performance comparison tools to evaluate different approaches
Before diving deeper, it’s worth contrasting the vector-based semantic search used in RAG with traditional keyword-based search techniques (like SQL LIKE queries or full-text search indexes). This is especially important for DBAs who are familiar with SQL and may wonder why a vector approach is needed.
Traditional Search (SQL LIKE, full-text): Matches literal terms or boolean combinations. Precise for exact matches but fails when queries use different wording. A search for “car” won’t find documents about “automobiles” without explicit synonym handling.
Semantic Vector Search: Converts queries and documents into high-dimensional vectors encoding semantic meaning. Finds documents whose embeddings are closest to the query’s embedding in vector space, enabling retrieval based on context rather than exact matches.
The key advantage: semantic search improves recall when wording varies and excels with natural language queries. However, traditional search still has value for exact phrases or specific identifiers. Many production systems implement hybrid search combining both approaches (covered in a later post).
I am not going to go through all types of searches available in PostgreSQL but here is a diagram that is showing the historical and logical steps we went through the past decades.
Key point: moving to vector search enables semantic retrieval that goes beyond what SQL LIKE or standard full-text indexes can achieve. It allows your RAG system to find the right information even when queries use different phrasing, making it far more robust for knowledge-based Q&A.
Let’s break down how to implement a Naïve RAG pipeline properly, using the example from the pgvector_RAG_search_lab repository. We’ll go through the major components and discuss best practices at each step: document chunking, embedding generation, vector indexing, the retrieval query, and finally the generation step.
Here is a diagram of the entire data process :
1. Document Ingestion – Chunking and Embeddings
Chunking documents: Large documents (e.g. long articles, manuals, etc.) need to be split into smaller pieces called chunks before embedding. Choosing the right chunking strategy is crucial. If chunks are too large, they may include irrelevant text along with relevant info; if too small, you might lose context needed to answer questions.
- Chunk size: test on your own data but a rule of thumb could be 100-150 tokens for factoid queries, 300+ for contextual queries but sentence or paragraph chunking are also an option.
Generating embeddings: Once the documents are chunked, each chunk is converted to a vector embedding by an embedding model. The choice of embedding model has a big impact on your RAG system’s effectiveness and is generally coupled with the LLM model you are going to choose. Since I am using ChatGPT-5, I went for OpenAI embedding models with 3072(large) and 1536(small) dimensions. The lab supports OpenAI’s text-embedding-3-large, small (dvdrental db) and open-source alternatives. Check MTEB benchmarks for model selection.
In the LAB repository you can generate the embeddings with the following Python script on the wikipedia database (Note: in the example bellow the SPLADE model is loaded but not used for dense vectors, the script is handling both dense and sparse embedding generation, we will cover this on the next blog post) :
(.venv) 12:56:43 postgres@PG1:/home/postgres/RAG_lab_demo/lab/embeddings/ [PG17] python generate_embeddings.py --source wikipedia --type dense
2025-09-27 12:57:28,407 - __main__ - INFO - Loading configuration...
2025-09-27 12:57:28,408 - __main__ - INFO - Initializing services...
2025-09-27 12:57:28,412 - lab.core.database - INFO - Database pool initialized with 1-20 connections
2025-09-27 12:57:29,093 - lab.core.embeddings - INFO - Initialized OpenAI embedder with model: text-embedding-3-large
2025-09-27 12:57:33,738 - lab.core.embeddings - INFO - Loading SPLADE model: naver/splade-cocondenser-ensembledistil on device: cpu
Some weights of BertModel were not initialized from the model checkpoint at naver/splade-cocondenser-ensembledistil and are newly initialized: ['pooler.dense.bias', 'pooler.dense.weight']
You should probably TRAIN this model on a down-stream task to be able to use it for predictions and inference.
2025-09-27 12:57:34,976 - lab.core.embeddings - INFO - SPLADE embedder initialized on cpu
============================================================
EMBEDDING GENERATION JOB SUMMARY
============================================================
Source: wikipedia
Embedding Type: dense
Update Existing: False
============================================================
CURRENT STATUS:
Wikipedia Articles: 25000 total, 25000 with title embeddings
25000 with content embeddings
Proceed with embedding generation? (y/N): y
============================================================
EXECUTING JOB 1/1
Table: articles
Type: dense
Columns: ['title', 'content'] -> ['title_vector_3072', 'content_vector_3072']
============================================================
2025-09-27 12:57:41,367 - lab.embeddings.embedding_manager - INFO - Starting embedding generation job: wikipedia - dense
2025-09-27 12:57:41,389 - lab.embeddings.embedding_manager - WARNING - No items found for embedding generation
Job 1 completed:
Successful: 0
Failed: 0
============================================================
FINAL SUMMARY
============================================================
Total items processed: 0
Successful: 0
Failed: 0
FINAL EMBEDDING STATUS:
============================================================
EMBEDDING GENERATION JOB SUMMARY
============================================================
Source: wikipedia
Embedding Type: dense
Update Existing: False
============================================================
CURRENT STATUS:
Wikipedia Articles: 25000 total, 25000 with title embeddings
25000 with content embeddings
2025-09-27 12:57:41,422 - __main__ - INFO - Embedding generation completed successfully
2025-09-27 12:57:41,422 - lab.core.database - INFO - Database connection pool closed
(.venv) 12:57:42 postgres@PG1:/home/postgres/RAG_lab_demo/lab/embeddings/ [PG17]
After these steps, you will have a vectorized database: each document chunk is represented by a vector, stored in a table (if using a DB like Postgres/pgvector) or in a vector store. Now it’s ready to be queried.
2. Vector Indexing and Search in Postgres (pgvector + DiskANN)For production-scale RAG, how you index and search your vectors is critical for performance. In a naive setup, you might simply do a brute-force nearest neighbor search over all embeddings – which is fine for a small dataset or testing, but too slow for large collections. Instead, you should use an Approximate Nearest Neighbor (ANN) index to speed up retrieval. The pgvector extension for PostgreSQL allows you to create such indexes in the database itself.
Using pgvector in Postgres: pgvector stores vectors and supports IVFFlat and HNSW for ANN. For larger-than-RAM or cost-sensitive workloads, add the pgvectorscale extension, which introduces a StreamingDiskANN index inspired by Microsoft’s DiskANN, plus compression and filtered search.
! Not all specialized Vector databases or Vector stores have this feature, if your data needs to scale this is a critical aspect !
For our Naïve RAG example :
CREATE INDEX idx_articles_content_vec
ON articles
USING diskann (content_vector vector_cosine_ops);
Why use Postgres for RAG? For many organizations, using Postgres with pgvector is convenient because it keeps the vectors alongside other relational data and leverages existing operational familiarity (backup, security, etc.). It avoids introducing a separate vector database. Storing vectors in your existing operational DB can eliminate the complexity of syncing data with a separate vector store, while still enabling semantic search (not just keyword search) on that data. With extensions like pgvector (and vectorscale), Postgres can achieve performance close to specialized vector DBs, if not better. Of course, specialized solutions (Pinecone, Weaviate, etc.) are also options – but the pgvector approach is very appealing for DBAs who want everything in one familiar ecosystem.
3. Query Processing and RetrievalWith the data indexed, the runtime query flow of Naïve RAG is straightforward:
- Query embedding: When a user question comes in (for example: “What is WAL in PostgreSQL and why is it important?”), we first transform that query into an embedding vector using the same model we used for documents. This could be a real-time call to an API (if using an external service like OpenAI for embeddings) or a local model inference. Caching can be applied for repeated queries, though user queries are often unique. Ensure the text of the query is cleaned or processed in the same way document text was (e.g., if you did lowercasing, removal of certain stopwords, etc., apply consistently if needed – though modern embedding models typically handle raw text well without special preprocessing).
- Vector similarity search: We then perform the ANN search in the vector index with the query embedding. As shown earlier, this is an
ORDER BY vector <=> query_vector LIMIT ktype query in SQL (or the equivalent call in your vector DB’s client). The result is the top k most similar chunks to the query. Choosing k (the number of chunks) to retrieve is another design parameter: common values are in the range 3–10. You want enough pieces of context to cover the answer, but not so many that you overwhelm the LLM or introduce irrelevant noise. A typical default isk=5. In the example lab workflow, they use atop_kof 5 by default. If you retrieve too few, you might miss part of the answer; too many, and the prompt to the LLM becomes long and could confuse it with extraneous info.
The outcome of the retrieval step is a set of top-k text chunks (contexts) that hopefully contain the information needed to answer the user’s question.
4. LLM Answer GenerationFinally, the retrieved chunks are fed into the prompt of a large language model to generate the answer. This step is often implemented with a prompt template such as:
“Use the following context to answer the question. If the context does not have the answer, say you don’t know.
Context:
[Chunk 1 text]
[Chunk 2 text]
…
Question: [User’s query]
Answer:”
The LLM (which could be GPT-5, or an open-source model depending on your choice) will then produce an answer, hopefully drawing facts from the provided context rather than hallucinating. Naïve RAG doesn’t include complex prompt strategies or multiple prompt stages; it’s usually a single prompt that includes all top chunks at once (this is often called the “stuffing” approach – stuffing the context into the prompt). This is simple and works well when the amount of context is within the model’s input limit.
Best practices for the generation step:
- Order and formatting of contexts: Usually, chunks can be simply concatenated. It can help to separate them with headings or bullet points, or any delimiter that clearly resets context. Some frameworks sort retrieved chunks by similarity score (highest first) under the assumption that the first few are most relevant – this makes sense so that if the prompt gets truncated or the model gives more weight to earlier context (which can happen), the best info is first.
- Avoid exceeding token limits: If each chunk is, say, ~100 tokens and you include 5 chunks, that’s ~500 tokens of context plus the prompt overhead and question. This should fit in most LLMs with 4k+ token contexts. But if your chunks or k are larger, be mindful not to exceed the model’s max token limit for input. If needed, reduce k or chunk size, or consider splitting the question into sub-queries (advanced strategy) to handle very broad asks.
- Prompt instructions: In naive usage, you rely on the model to use the context well. It’s important to instruct the model clearly to only use the provided context for answering, and to indicate if the context doesn’t have the answer. This mitigates hallucination. For example, telling it explicitly “If you don’t find the answer in the context, respond that you are unsure or that it’s not in the provided data.” This way, if retrieval ever fails (e.g., our top-k didn’t actually contain the needed info), the model won’t fabricate an answer. It will either abstain or say “I don’t know.” Depending on your application, you might handle that case by maybe increasing k or using a fallback search method.
- Citing sources: A nice practice, especially for production QA systems, is to have the LLM output the source of the information (like document titles or IDs). Since your retrieval returns chunk metadata, you can either have the model include them in the answer or attach them after the fact. This builds trust with users and helps for debugging. For instance, the lab workflow tracks the titles of retrieved articles and could enable showing which Wikipedia article an answer came from. In a naive setup, you might just append a list of sources (“Source: [Title of Article]”) to the answer.
With that, the Naïve RAG pipeline is complete: the user’s query is answered by the LLM using real data fetched from your database. Despite its simplicity, this approach can already dramatically improve the factual accuracy of answers and allow your system to handle queries about information that the base LLM was never trained on (for example, very recent or niche knowledge).
In our LAB setup on n8n the workflow without the chunking and embedding generation looks like that :
Note that we use
In the Streamlit interface also provided in the repo, we have the following :
Monitoring and Improving RAG in Production
Implementing the pipeline is only part of the story. In a production setting, we need to monitor the system’s performance and gather feedback to continuously improve it.
The comparison workflow allows side-by-side testing of:
- Different embedding models
- Chunk sizes and strategies
- Retrieval parameters (top-k values)
- LLM prompting approaches
In summary, treat your RAG system as an evolving product: monitor retrieval relevance, answer accuracy (groundedness), and system performance. Use a combination of automated metrics and human review to ensure quality. Tools like LangSmith can provide infrastructure for logging queries and scoring outputs on metrics like faithfulness or relevance, flagging issues like “bad retrievals” or hallucinated responses. By keeping an eye on these aspects, you can iterate and improve your Naïve RAG system continuously, making it more robust and trustworthy. Although Langsmith is very usefull, be carefull with the necessary pitfall that come along with any abstraction. A good rule of thumb would be to keep your core logic into custom code while taking leverage of Langsmith tools for peripherals.
Conclusion and Next StepsNaïve RAG provides the basic blueprint of how to augment LLMs with external knowledge using semantic search. We discussed how to implement it using PostgreSQL with pgvector, covering best practices in chunking your data, selecting suitable embeddings, indexing with advanced methods like DiskANN for speed, and ensuring that you monitor the system’s effectiveness in production. This straightforward dense retrieval approach is often the first step toward building a production-grade QA system. It’s relatively easy to set up and already yields substantial gains in answer accuracy and currency of information.
However, as powerful as Naïve RAG is, it has its limitations. Pure dense vector similarity can sometimes miss exact matches (like precise figures or rare terms) that a keyword search would catch, and it might bring in semantically relevant but not factually useful context in some cases. In the upcoming posts of this series, we’ll explore more advanced RAG techniques that address these issues:
- Hybrid RAG: combining dense vectors with sparse (lexical) search to get the best of both worlds – we’ll see how a hybrid approach can improve recall and precision by weighting semantic and keyword signals GitHub.
- Adaptive RAG: introducing intelligent query classification and dynamic retrieval strategies – for example, automatically detecting when to favor lexical vs. semantic, or how to route certain queries to specialized retrievers.
- and other more trendy RAG types like Self RAG or Agentic RAG….
As you experiment with the provided lab or your own data, remember the core best practices: ensure your retrieval is solid (that often solves most problems), and always ground the LLM’s output in real, retrieved data. The repository will introduce other RAG and lab examples over time, the hybrid and adaptive workflows are already built in though.
With all the fuss around AI and LLM it might seem chaotic from an outsider with a lack of stability and maturity. There a good chance that the RAG fundamental component will still be there in the coming years if not decade, it lasted 2 years already and proved useful, we just might see those components be summed and integrated to other systems especially with components provided normative monitoring and evaluation which the big open subject. We can’t say that RAG patterns we have are mature but we know for sure they are fundamental to what is coming next.
Stay tuned for the next part, where we dive into Hybrid RAG and demonstrate how combining search strategies can boost performance beyond what naive semantic search can do alone.
L’article RAG Series – Naive RAG est apparu en premier sur dbi Blog.
M-Files with Microsoft Co-Authoring
Following the announcement of the partnership between M-Files and Microsoft, they have confirmed the availability of native Microsoft Co-Authoring within the Microsoft 365 ecosystem.
What means native and what is the differentiator to other ECM’sIn this context, “native” means that documents stored in M-Files can be edited using the native capabilities of Microsoft 365, such as Co-Authoring. It is not necessary to move or copy the data outside of M-Files in order to use the Co-Authoring capabilities of Microsoft 365.
As we are aware, the functionality of these features is contingent upon the storage of data within the Microsoft 365 Tenant of the collaborating users working on a document.
This new experience is only possible because the documents are stored in the same back-end storage system. Currently, Microsoft has made this possible especially for M-Files as they work close together in the development of the M-Files Sharepoint Embedded Application. No other content management vendor has this deep partnership and integration in Sharepoint Embedded.
Benefits and Differentiator- No need to move the documents outside of M-Files
- The security is always managed by M-Files
- The desktop application of the Microsoft Office can be used
- True Collaboration Anywhere, it doesn’t matter from which application the co-authoring is started (Microsoft Office Suite or M-Files Vault)
- No More Version Chaos
- User stay in the application they know and benefit from M-Files features
It is called native because the data is stored in the Microsoft 365 storage of the client tenant, like his other Microsoft 365 native documents.
How is this possible ?
M-Files developed a Microsoft Sharepoint Embedded (SPE) application to store the M-Files data in a SPE storage container. This enables the possibility to get use of the Microsoft 365 capabilities like a Microsoft Co-Authoring. In addition, such a SPE container allows to respect the defined security boundaries of a M-Files Vault.
The diagram below illustrates the high-level architecture. The key point to take away from this is the importance of understanding the change in data storage location.
At present, M-Files stores data in Azure Blob Storage, as illustrated in the orange box of the diagram. By enabling of the co-authoring function this will change.
Please note that data for Microsoft 365 will be stored in a SPE container in the Microsoft 365 Tenant, as shown in the blue box in the diagram. All other data is stored in a new Blob Storage area, as shown in the green box.
The Master Storage, displayed centrally, is responsible for managing the allocation of data to its designated storage locations.
It is my hope that this will provide a clearer understanding of the new storage management approach developed by M-Files. Should you require further information, I would be happy to provide it in a separate blog post. Please do not hesitate to leave your comments below.
Enablement
To enable the function the following prerequisites must be taken in consideration.
- M-Files Cloud is required
- M-Files Sharepoint Embedded requires admin consent in the Microsoft 365 Tenant
- At least a M-Files Business user license is required
- The M-Files Enterprise Application must be configured in the Microsoft Admin Center and the users in scope must be added
- In M-Files Manage the user provisioning configuration for the M-Files Enterprise Application must be configured
The process to enable it is very straightforward, only the two steps below are required, and these are clearly documented by M-Files.
- Grant tenant-wide admin consent to the M-Files SharePoint Embedded application in Microsoft 365.
- Enable desktop co-authoring in the M-Files Admin tool vault properties
It is important to understand that all data will move to the new storage and this cannot be reverted!
Co-Authoring in actionI am delighted to share that, to showcase the capability in a recorded video, a demo environment was created. Please find below a high-level description of this exciting project.
- M-Files Cloud Vault
- M-Files Desktop Client (new version)
- M-Files Web Client
- Microsoft 365 Tenant with two demo users
- Microsoft Word application with logged in demo users
By leveraging the capabilities of both Microsoft and M-Files, businesses can optimize the use of their data while ensuring its secure storage within the designated domain. The use of the Sharepoint Embedded application ensures that data is stored within the client-owned Tenant, with Microsoft’s trusted security and authentication functions providing robust protection. This marks the start of a collaborative effort to leverage the native Microsoft functionalities in conjunction with M-Files, integrating the capabilities of Microsoft Sharepoint Embedded.
We are able to assist your business in advancing to the next level of digitalisation. Please do not hesitate to get in contact with us for further details.
L’article M-Files with Microsoft Co-Authoring est apparu en premier sur dbi Blog.
Errorhandling in Ansible
Per default, if a task in a playbook fails, then the execution of the playbook is stopped for that host.
- name: PLAY1
hosts: localhost
gather_facts: no
tasks:
- name: let this shell-command fail
ansible.builtin.shell: exit 1
- name: let this shell-command complete
ansible.builtin.shell: exit 0
As you can see, the 2nd task is not executed. If you want to continue in such a case, the ignore_errors parameter is your friend
- name: let this shell-command fail
ansible.builtin.shell: exit 1
ignore_errors: true
Custom error conditions
Per default, Ansible evaluates the exit-code of the module, in case of the shell-module, the exit-code of the last command.
But for some commands, that is not adequate. Example: The Oracle commandline tool sqlplus to submit sql-commands will have an exit-code of 0 if it can connect to the database-instance. It is not related to the result of your SQL-commands. Error-messages in Oracle are prefixed by ORA-.
So, if you want to check for application errors, you have to implement it yourself. For that, you can use the failed_when option.
- name: let this shell-command fail
ansible.builtin.shell: |
. ~/.profile
echo "select count(*) from all_users;" | sqlplus / as sysdba
register: number_of_users
failed_when: "'ORA-' in number_of_users.stdout"
Caution: In this case the exit-code of the shell is no longer evaluated. To also get the exit-code of the sqlplus call (e.g., sqlplus can not connect to the database, or sqlplus binary not found), you have to add this (default) condition:
failed_when: "number_of_users.rc != 0 or 'ORA-' in number_of_users.stdout"
But caution! Not all modules will have an rc field.
Conceptually, Ansible is not the fastest tool. For each task, it will usually login with ssh to the remote server. If you have to run several checks in the shell, then, instead of running each in a separate task, you can run all these check-commands in one shell-task, and evaluate the result afterwards.
- name: run many check commands
ansible.builtin.shell: |
mount | grep ' on /u00 ' #check if /u00 is mounted
rpm -q ksh 2>&1 #check if ksh is installed
exit 0 # do not fail if rpm exit != 0; we will do our own errorhandling
register: checks
- name: fail if no /u00 is mounted
fail:
msg: "/u00 is not mounted"
when: "' on /u00 ' not in checks.stdout"
- name: No ksh found, try to install it
yum:
name: ksh
state: present
when: "'package ksh is not installed' in checks.stdout"
If you only want to throw an error, then you can do it directly in the shell-task:
when: "' on /u00 ' not in checks.stdout" or 'package ksh is not installed' in checks.stdout
But if you parse the output afterwards, you can run tasks to fix the error.
Sometimes it is difficult to parse the output if some commands return the same output, e.g. “OK”.
If your check-commands always return exactly 1 line, then you can directly parse the output of the command. The output of the 3rd command is in checks.stdout_lines[2].
In the above example that will not work because grep will return the exit-code 0 (not found) or 1 (found) plus the found line. So, expand it as: mount | grep ' on /u00 ' || echo error
Do not fail the task itself, it is very usually unreadable because all information is printed on one line.
Instead, use ignore_errors: true and failed_when: false in the task. Do the errorhandling in a separate task with a customized errormessage. To print the multiline list of stdout_lines, use debug: otherwise you can directy use ansible.builtin.fail: with a customized message:
- name: force sqlplus to throw error because of missing environment
ansible.builtin.shell: |
/u00/app/oracle/product/19.7.0/bin/sqlplus -L / as sysdba @myscript.sql 2>&1
register: checks
ignore_errors: true
failed_when: false
- name: Check for errors of task before
debug: var=checks.stdout_lines
failed_when: checks.rc != 0 or 'ORA-' in checks.stdout
when: checks.rc != 0 or 'ORA-' in checks.stdout
Re-run failed hosts
As an example for this scenario: A customer of mine will do an offline backup of the development databases. And I have to run an Ansible playbook against all databases. If the playbook for this host is run at the backup time, it will fail because the database is down. But after some minutes the database will be restarted.
What we can do now?
Wait until the database is up again. That is possible, see the example of ansible.builtin.wait_for in my blog post Parallel execution of Ansible roles. But for this scenario it is a waste of time. The database can be stopped (not for backup) and will not be restarted within the next few minutes.
Try later after a while. My playbook for all hosts (parallel forks=5) takes about 1 hour. The idea now is to remember the host with the stopped database and to continue with the next host. After the play finished for all hosts, restart the play for the remembered hosts.
- The 1st play running against all database hosts:
- gets the status of the databases on the host
- assigns the database instances to a
is_runningand anot_openlist - Include the role to run against the running databases
- Dynamically add the host to the group
re_run_if_not_openif there arenot_opendatabases
- The next play only runs for the
re_run_if_not_opengroup - Include the role to run against the (hopefully now running) databases
- If the database then is still down, we assume it is stopped permanently.
L’article Errorhandling in Ansible est apparu en premier sur dbi Blog.