Tag: listener

When A Refreshable Clone Takes Over The Service

Following my advice, a customer migrated a database to multitenant using refreshable clone PDB. The CDB was on the same host as the non-CDB.

When we prepare the migration and create the refreshable clone, the users can no longer connect to the source database.

The users were getting ORA-01109: database not open when connecting to the source database.

But this was before the final refresh and before the migration was supposed to happen.

Why did the refreshable clone PDB interfere with operations in the source database?

The Details

The customer has a non-CDB called SALES, which they wanted to migrate into CDB1. They wanted the PDB to keep the original name, SALES.

Any database registers a default service at the listener. The name of the default service is the same as the name of the database.

In this case, the non-CDB registered the sales service:

$ lsnrctl status

Service "sales" has 1 instance(s).
  Instance "SALES", status READY, has 1 handler(s) for this service...

The customer used AutoUpgrade for the migration. When preparing for the migration, they started in deploy mode, and AutoUpgrade created the refreshable clone.

Bear in mind that these steps are preparations only. The switch to the PDB should happen at a later time.

Look what happens to the listener:

$ lsnrctl status

Service "sales" has 2 instance(s).
  Instance "CDB1", status READY, has 1 handler(s) for this service...
  Instance "SALES", status READY, has 1 handler(s) for this service...

The CDB also registers a sales service. It does so because of the refreshable clone PDB with the same name.

The users were connecting to the default service, sales. The listener handed off the connections to the CDB, not the non-CDB.

Since the PDB was still a refreshable clone PDB, it was not open, and users received ORA-01109: database not open.

Besides that, the refreshing process didn’t work either. The refresh of the PDB happens over a database link to the source database. Guess what happened?

2025-05-09T07:25:58.854934+00:00
Errors in file /u01/app/oracle/diag/rdbms/cdb19/CDB19/trace/CDB19_ora_61633.trc:
ORA-17627: ORA-01109: database not open
ORA-17629: Cannot connect to the remote database server
ORA-17627 signalled during: ALTER PLUGGABLE DATABASE FTEX REFRESH...

Yes, it couldn’t connect to the source database either. It ended up trying to connect to itself.

Optimal Solution

The real problem is the connections to the default service, sales. The service with the same name as the database.

This service is not meant for general use. You should create your own service and have your application connect through that.

Why is using default services a bad idea?

  • You can’t customize the default service.
  • The default service is for administrative use only.
  • You easily end up with collisions like this one. This can also happen with two PDBs in different CDBs on the same host.
  • If you rename the database, you also rename the default service and have to update all connection strings.

Why are custom services a good idea?

  • Custom services allow you to set many attributes. While this might not be important for a single-instance database, it is essential for Data Guard and RAC.
  • When you clone a database, a custom service doesn’t follow with it. You have to create the services in the clone when and if it is appropriate.

You can create custom services using DBMS_SERVICE or srvctl. You can find more about that in a previous blog post.

Other Solutions

Other feasible solutions exist, but none of them address the real issue, which I believe is the use of the default service.

  • Rename the PDB so it creates a default service with a different name. After migration, you can rename it.
  • Create a static listener entry that forces the listener to route connections to the non-CDB. However, static listener entries are really not nice, and you should use dynamic registration whenever possible.
  • Create a second listener for the CDB. That’s just cumbersome.

Recommendation

  • Keep using refreshable clone PDB for migrations. It’s a great way to migrate, patch, or upgrade databases.

  • Always create your own custom service. Don’t use the default service.

How to Patch Oracle Net Listener

When running Oracle Database on a server, the Oracle Net Listener is a vital component.

Let’s discuss how you can patch it.

The Basics

Normally, on a server, you have:

  • One listener
  • One or many database instances

That one listener handles new connections to all database instances. You patch the listener simply by starting it in the new Oracle home.

You can have multiple listeners and use remote listeners, but if you do that, you’re smart and probably won’t need the advice in this blog post.

How to Patch the Listener

  1. Copy listener.ora from the old Oracle home to the new Oracle home.
    export OLD_ORACLE_HOME=/u01/app/oracle/product/19.26.0/dbhome_1
export NEW_ORACLE_HOME=/u01/app/oracle/product/19.27.0/dbhome_1
cp $OLD_ORACLE_HOME/network/admin/listener.ora $NEW_ORACLE_HOME/network/admin
    • You can avoid this step by redirecting the location of your network files using the TNS_ADMIN environment variable.
    • If you have a completely default installation with no listener configuration, you don’t have a listener.ora file and can skip this step.
    • In an advanced configuration you might have references to the listener Oracle home. Those you must update. But don’t confuse the listener Oracle home, with the Oracle homes of the databases with static registration. Those you update, when you patch the matching database.
  2. Restart the listener in the new Oracle home:
    export ORACLE_HOME=$OLD_ORACLE_HOME
$OLD_ORACLE_HOME/bin/lsnrctl stop
export ORACLE_HOME=$NEW_ORACLE_HOME
$NEW_ORACLE_HOME/bin/lsnrctl start
    • If you use Oracle Grid Infrastructure or Oracle Restart, they manage the listener. You automatically restart, and thus patch, the listener when you patch those components.
  3. If you use the dbstart script to start the listener, update it with the new Oracle home.
    • On Windows, you must delete the matching Windows service when you stop the listener. The lsnrctl start command should create a new listener. Be sure to set it to start automatically.

The Outage

  • When you restart the listener, there’s a short period where there’s no listener. It takes a second or so for the new listener to start. In that period, any new connection attempt fails:

    ORA-12541: TNS:no listener

    This affects new connections only. Existing connections continue to work unaffected.

  • As soon as the listener starts, it’ll read listener.ora and go through the static listener registrations defined by the SID_LIST. Connections to those databases are ready immediately.

  • For optimal flexibility, however, it’s better to use dynamic listener registration, where the database instance automatically registers with the listener. When you restart the listener, it doesn’t know of all the dynamic registrations and will have to wait until the database registers (up to 60 seconds).

    • You can force a database to register with the listener:
      SQL> alter system register;
    • It’s a good idea to run through all of your databases after the listener restart and force a listener registration with the above command.
    • Until the dynamic registration completes, any new connection to the database will fail:
      ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

  • You can hide the short outage from the client by using RETRY_COUNT and RETRY_DELAY in your connect strings. Allow the client to retry for a short period before throwing the above errors.

  • You can avoid the outage completely by using multiple listeners and patching them separately. Your connect strings should have an ADDRESS_LIST containing both listeners.

Do I Need to Patch the Listener and Database At the Same Time?

No, that’s up to you. If you want just one interruption, then do it all at once. Otherwise, you can do it in separate maintenance windows.

What About AutoUpgrade?

At the moment, AutoUpgrade doesn’t patch the listener. But it’s on the backlog.

Happy patching!

Files to Move During Oracle Grid Infrastructure Out-Of-Place Patching

Like with Oracle Database, I strongly recommend patching Oracle Grid Infrastructure using the out-of-place method. It has many advantages over in-place patching

A while ago, I wrote about the files you must move during Oracle Database out-of-place patching. There are quite many files to consider. A blog reader left a comment asking about a similar blog post for Oracle Grid Infrastructure, so here it is.

Listener

The listener runs out of the GI home. When you patch out-of-place, the listener must also start in the new GI home.

If you patch using any of the below methods, it will move the listener for you:

The two methods also move listener.ora as part of the process.

If you use opatchauto you should note that the tool moves listener.ora when preparing the new GI using opatchauto apply ... -prepare-clone. You can run that command hours or days before you move the listener. If you add things to listener.ora in between, you must also add it to listener.ora in the new GI home.

Conclusion

There is really nothing to worry about when you patch Oracle Grid Infrastructure out-of-place. The above-mentioned two tools will take care of it for you.

Happy Patching