1

We have 2 pluggable databases in the same cdb. Lets call them pdb1 and pdb2. These are in out test environment and you can think of thwm as separate uat environments. Each pdb has the defaul service (pdb1/pdb2) as well as 1 service i created (pdb1_s1/pdb2_s2). Several java applications point at either of the pdbs (uat1 app servers point at pdb1 sercices, uat2 servers point at pdb2). Pbd1 and pdb2 have the same tables but with different data.

I would like to 'swap' the pdbs such that uat1 servers still use pdb_s1 service but that service really points at pdb2. Why? Lets say uat1 is the main env we test in but the data in that database (pdb1) is old/corrupted. We cant switch to uat2 for testing since that has other issues in the java servers.

I have 12c installed locally to test a solution. I am using dbms_services since srvctl doesnt seem to work on non clustered env (but in the end will need to get this solution using srvctl since uat is clustered).

I found the only way to accomplish swapping the services is to delete the services from both pdbs and then add them back in opposite pdb (create pdb1_s1 in pdb2 and create pdb2_s1 in pdb1). Then i rename the pdbs (pdb1-> pdb2 and pdb2->pdb1). It sort of works.

The issue is that the default service (pdb1) still points to pdb1. I tried deleting pdb1 service from pdb1 and creating pdb2 service in pdb1 but caused issues (and the service does not start when i restart the database).

Is it possible to delete default service and create a new default service with new name?

How do i make non default services start when the server starts? I tried save state but that doesn seem to work.

George
  • 1,021
  • 15
  • 32
  • Have you considered exporting (expdp) pdb1 and pdb2, then importing (impdp) pdb1 into pdb2 and vice versa? Although it sounds clunky, you can script it to automate the process. – TenG Oct 05 '19 at 21:05
  • No. Its 4 + terabytes. – George Oct 06 '19 at 02:27
  • The default service must not be ever changed, its something that oracle needs for working correctly and should only be used for admin purposes and not for ordinary users to connect. Every pdb should be exposed by a newly created service. Naming the service with the pdb name in its name is highly discouraged and a source of your problems. Simply recreate the application service names and you are done. – steve Oct 06 '19 at 05:52
  • Out of curiosity I looked up naming pluggable database on MOS and for 12c there is a note Doc ID 2439885.1. I will add the command to a answer below in case it helps. – TenG Oct 06 '19 at 12:16
  • Interesting. Did you find a solution in the meantime? – wolφi May 20 '20 at 21:04

2 Answers2

1

Renaming PDBFRED to PDBBURT.

In the case of the OP's question since he wants to do a swap he would use the following to construct a process of:

  1. Rename PDB1 to PDB0
  2. Rename PDB2 to PDB1
  3. Rename PDB0 to PDB2

This has not been tested ... based on Doc ID 2439885.1 from MOS:

From the CDB check the PDB status:

select name, open_mode, restricted from v$pdbs;

select name, con_id, dbid, con_uid, guid from v$containers order by con_id;

select service_id, name, network_name, creation_date, pdb, con_id from cdb_services;

Let's say we're renaming PDBFRED to PDBBURT:

Put PDB in RESTRICTED mode for a rename operation:

alter pluggable database PDBFRED close;

alter pluggable database PDBFRED open restricted;

select name, open_mode, restricted from v$pdbs;

Then connect to PDBFRED and rename it:

alter session set container=PDBFRED;

alter pluggable database rename global_name to PDBBURT;

Very Important: You must close the PDB and open it in read/write mode for Oracle Database to complete the integration of the new PDB service name into the CDB.

Close the PDB.

alter pluggable database close immediate;

Open the PDB in read/write mode.

alter pluggable database open;

Connect to the CDB and check the PDBs again:

alter session set container=CDB$ROOT;

select name, open_mode, restricted from v$pdbs;

select name, con_id, dbid, con_uid, guid from v$containers order by con_id;

select service_id, name, network_name, creation_date, pdb, con_id from cdb_services;

The datafiles for the PDB will still be under the original name, e.g :

/u03/oradata/CDB1/PDBFRED

If you would want to move the files to a new directory matching with the name of the PDBBURT, then you would have to create the new directory manually and then move the files using the ONLINE MOVE facility of the 12c database:

$ mkdir '/u03/oradata/CDB1/PDBBURT

Then move each datafile (generate this from dba_data_files):

SQL> alter database move datafile '/u03/oradata/CDB1/PDBFRED/system01.dbf' to '/u03/oradata/CDB1/PDBBURT/system01.dbf'

To rename Oracle Managed Files (OMFs), follow the document "How to rename Oracle-Managed Files (OMFs) (Doc ID 191574.1) ".

The tempfiles of the temporary tablespace cannot be moved by the 'online move' command though, so you would have to drop the tempfile associated with the temp tablespace and recreate a new tempfile in the new directory.

SQL> alter database tempfile '/u03/oradata/CDB1/pdb1/temp01.dbf' drop including datafiles;

SQL> alter tablespace TEMP add tempfile '/u03/oradata/CDB1/PDBBURST/temp01.dbf' size 10M reuse;

TenG
  • 3,843
  • 2
  • 25
  • 42
  • Thanks for your response. Yes that is the process I am following. rename pdb1 -> pdb1_temp, rename pdb2 -> pdb1. rename pdb1_temp -> pbd2. What I did not understand was that renaming the pbd also renames the default service. For instance I renamed pbd2 to pbd1 and then do select * from DBA_SERVICES on pbd1 and I can see the default service is actually also renamed. I dont think we need to rename the folders but I give you credit for mentioning it. I think the reason our DBAs were having issues was because they were trying to first rename the services but the new service already exists. – George Oct 09 '19 at 14:18
0

The issue was our DBAs were trying to rename the service to one that already exists without deleting those

existing services:

  • PDB1 / PBD1_S1
  • PDB2 / PBD2_S1

So if you name PBD1_S1 to PBD2_S1 it is failing since the service already exists in another PBD.

The solution is to also have temp name for the services as well as the pbds.

  • rename service : PBD1_S1 to PBD1_S1_TEMP
  • rename service : PBD2_S1 to PBD1_S1
  • rename service : PBD1_S1_TEMP to PBD2_S1
  • rename pbd : PBD1 to PBD1_TEMP
  • rename pbd : PBD2 to PBD1
  • rename pbd : PBD1_TEMP to PBD2

Of course this took me (not a DBA) a week to realize what the issue was. Our DBAs are on a separate team, dont seem to care about what we are trying to do, work mainly during IST hours (10 hour time difference), dont respond to emails or chats, are overloaded with prod support issues hence all other requests take forever, wont share error logs or much of anything they tried to fix the issue, and mainly respond they are waiting for oracle support and are themselves blocked.

George
  • 1,021
  • 15
  • 32