新建pdb 打不开 ORA-65104 ORA-25153

select * from pdb_plug_in_violations；

有个waring service_name 冲突，应该是不影响的

-------------------------------------service 冲突解决-------------------

Select message, action from DB_PLUG_IN_VIOLATIONS and get the result:

MESSAGE                                                                                                                                                ACTION

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

Service name or network name of service dossqa3 in the PDB is invalid or conflicts with an existing service name or network name in the CDB.           Drop the service and recreate it with an appropriate name.

SELECT SERVICE_ID,NAME,NETWORK_NAME,CREATION_DATE,PDB,CON_ID  FROM CDB_SERVICES;

5. Find out service name 'dossqa3' was cloned from pdb 'DOSS_QA3'. So, there are one service name 'dossqa3' on two pdb, one is on 'DOSS_QA3' and another is on 'MIXEDPDB'.

6. I try to remove it using 'srvctl remove service -d cdb1206 -s dossqa3, I can only remove the one that was registered on pdb 'DOSS_QA3',

I cannot specify the pdb name on srvctl.

How to remove the cloned service name?

> srvctl status service -d cdb1206 -v

Service dossqa2 is running on instance(s) cdb12061,cdb12062

Service dossqa3 is running on instance(s) cdb12061,cdb12062

Service smalldb1 is running on instance(s) cdb12061,cdb12062

> srvctl stop service -d cdb1206 -s dossqa3

> srvctl status service -d cdb1206 -v

Service dossqa2 is running on instance(s) cdb12061,cdb12062

Service dossqa3 is not running.

Service smalldb1 is running on instance(s) cdb12061,cdb12062

>

> srvctl remove service -d cdb1206 -s dossqa3

> srvctl status service -d cdb1206 -v

Service dossqa2 is running on instance(s) cdb12061,cdb12062

Service smalldb1 is running on instance(s) cdb12061,cdb12062

Then verify the service name is still on PDB MIXEDPDB.

SQL> select SERVICE_ID,NAME,NETWORK_NAME,PDB FROM CDB_SERVICES ORDER BY PDB,SERVICE_ID;

SERVICE_ID NAME                           NETWORK_NAME                             PDB

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

         1 SYS$BACKGROUND                                                          CDB$ROOT

         2 SYS$USERS                                                               CDB$ROOT

         5 cdb1206XDB                     cdb1206XDB                               CDB$ROOT

         6 cdb1206                        cdb1206                                  CDB$ROOT

         1 doss_qa2                       doss_qa2                                 DOSS_QA2

         2 dossqa2                        dossqa2                                  DOSS_QA2

         1 doss_qa3                       doss_qa3                                 DOSS_QA3

         1 mixedpdb                       mixedpdb                                 MIXEDPDB

         2 dossqa3                        dossqa3                                  MIXEDPDB

         3 smalldb1                       smalldb1                                 MIXEDPDB

10 rows selected.

SQL> exec dbms_service.delete_service('dossqa3');

BEGIN dbms_service.delete_service('dossqa3'); END;

*

ERROR at line 1:

ORA-44304: service dossqa3 does not exist

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86

ORA-06512: at "SYS.DBMS_SERVICE_ERR", line 23

ORA-06512: at "SYS.DBMS_SERVICE", line 352

ORA-06512: at line 1

SQL>

The service name is still exist on CDB_SERVICES view.

• SQL> exec dbms_service.delete_service('dossqa3');

  BEGIN dbms_service.delete_service('dossqa3'); END;

  *

  ERROR at line 1:

  ORA-44304: service dossqa3 does not exist

  ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86

  ORA-06512: at "SYS.DBMS_SERVICE_ERR", line 23

  ORA-06512: at "SYS.DBMS_SERVICE", line 352

  ORA-06512: at line 1

• It works now after I go to PDB.

  SQL> alter session set container=mixedpdb;

  Session altered.

  SQL> exec dbms_service.delete_service('dossqa3');

  PL/SQL procedure successfully completed.

  SQL> select SERVICE_ID,NAME,NETWORK_NAME,PDB FROM dba_services;

  SERVICE_ID NAME                           NETWORK_NAME                             PDB

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

           1 mixedpdb                       mixedpdb                                 MIXEDPDB

           3 smalldb1                       smalldb1                                 MIXEDPDB

  -------------pdb删除不了 delete service cdb下的应该可以srvctl--

• I have a very similar situation, except that the SERVICE_NAME is pointing at the CDB$ROOT and not at the appropriate PDB.

  SERVICE_ID NAME      NETWORK_NAME PDB

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

       3 VADVCOUTDB  VADVCOUTDB   CDB$ROOT === CLONE

    4 VADVCDITDB   VADVCDITDB    CDB$ROOT === CLONE

  I tried from CDB$ROOT:

  SQL> exec dbms_service.delete_service('VADVCUATDB');

  BEGIN dbms_service.delete_service('VADVCUATDB'); END;

  *

  ERROR at line 1:

  ORA-44304: service VADVCUATDB does not exist

  ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86

  ORA-06512: at "SYS.DBMS_SERVICE_ERR", line 23

  ORA-06512: at "SYS.DBMS_SERVICE", line 453

  ORA-06512: at line 1

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

  Please let me know how to drop the service name from CDB$ROOT.

• deleted from srvctl config

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

SQL> create pluggable database abcd1  from pdb;
create pluggable database abcd1  from pdb
*
ERROR at line 1:
ORA-25153: Temporary Tablespace is Empty

SQL> alter pluggable database abcd1 open;
alter pluggable database abcd1 open
*
ERROR at line 1:
ORA-65104: operation not allowed on an inactive pluggable database
 

create pluggable database abcd from pdb
2024-06-12T22:44:55.223990+08:00
PDB(3): AUDSYS.AUD$UNIFIED (SQL_TEXT) - CLOB populated
2024-06-12T22:44:57.741998+08:00
ABCD(4):Endian type of dictionary set to little
****************************************************************
Pluggable Database ABCD with pdb id - 4 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
local undo-1, localundoscn-0x0000000000000118
****************************************************************
ORA-25153 signalled during: create pluggable database abcd from pdb...
2024-06-12T22:45:05.095541+08:00
alter pluggable database abcd open
2024-06-12T22:45:05.103040+08:00
ABCD(4):Pluggable database ABCD opening in read write
ABCD(4):Error 65104 during pluggable database ABCD opening in read write 
2024-06-12T22:45:05.103176+08:00
ABCD(4):Errors in file /u01/app/oracle/diag/rdbms/cdb/cdb1/trace/cdb1_ora_31840.trc:
ORA-65104: operation not allowed on an inactive pluggable database
ORA-65104 signalled during: alter pluggable database abcd open...