Re: 18c Service Names

From: Ludovico Caldara <ludovico.caldara_at_gmail.com>
Date: Mon, 6 Jul 2020 13:32:15 +0200
Message-ID: <CALSQGrLGDHZKdcPDEFoO6eG2VatW+EacAjYOELE7zrROm11FHg_at_mail.gmail.com>

Hi Jack,

I had the same problem when I started using Multitenant... When you clone PDBs and change the name, the definitions stay in table service$. So even if the services are not started, there are some "key violations" at CDB level who does not expect the same service name to appear twice at CDB level.
To be on the safe side, I suggest to systematically delete the services when you clone a PDB and recreate only the services that you need.

This is a script to delete all the services in a PDB but the default ones:

---

• Purpose : Drop all non-default services in a PDB. Useful after clones/conversions
• Run as : SYSDBA or PDBADMIN
• Container: Any PDB except CDB$ROOT
• Author : Ludovico Caldara -- DECLARE

        CURSOR c_services IS
                SELECT  name  FROM SYS.service$ WHERE upper(name) !=

upper(rtrim(sys_context('userenv','db_name')||'.'|| sys_context('userenv','db_domain'), '.'));

        r_services c_services%ROWTYPE;
        e_service_error EXCEPTION;
        PRAGMA EXCEPTION_INIT (e_service_error    , -44786);

        e_not_in_pdb EXCEPTION;
        PRAGMA EXCEPTION_INIT (e_not_in_pdb, -20101);

        e_no_trigger EXCEPTION;
        PRAGMA EXCEPTION_INIT (e_no_trigger, -4080);

BEGIN

• check if currently in a PDB IF sys_context('userenv','con_name') = 'CDB$ROOT' THEN raise_application_error(-20101,'The current container is CDB$ROOT. It must be a PDB.'); END IF;

        OPEN c_services;
        LOOP
                FETCH  c_services  INTO r_services;
                EXIT WHEN c_services%NOTFOUND;
                BEGIN
                        DBMS_SERVICE.STOP_SERVICE (service_name =>
r_services.name);
                EXCEPTION
                        WHEN DBMS_SERVICE.SERVICE_NOT_RUNNING THEN  null;
                        WHEN DBMS_SERVICE.SERVICE_DOES_NOT_EXIST THEN  null;
                        WHEN e_service_error THEN  null;
                END;

                BEGIN
                        DBMS_SERVICE.DELETE_SERVICE (service_name =>
r_services.name);
                EXCEPTION
                        WHEN DBMS_SERVICE.SERVICE_DOES_NOT_EXIST THEN  null;
                END;
        END LOOP;

        BEGIN
                EXECUTE IMMEDIATE 'DROP TRIGGER service_trigger';
        EXCEPTION
                WHEN e_no_trigger THEN NULL;
        END;

END;
/

Il giorno dom 5 lug 2020 alle ore 23:02 Jack Applewhite < jack.applewhite_at_austinisd.org> ha scritto:

> New environment: 18c EE on X8-2M Bare Metal at ODA 18.8 - Single Instances
>
> I've been testing for migration of 11.2.0.4 DBs on an X5 ODA to the new
> environment above, multi-tenant. We have ThisDB and ThatDB on the X5.
> -- I created CDB1 on the X8 with ThisDBX and ThatDBX PDBs. Did exports
> from 11g DBs and imported them into 18c DBs. Opened them for testing by
> Users, which is why I wanted the X suffix so they'd know where they were.
> -- In the iterative process, I created CDB2 as well and cloned ThisDBX and
> ThatDBX to it as ThxxDBX1,2,3,etc. and ThxxDBX1,2,3,etc., each time then
> unplugging ThxxDBX1,2,3 to separate .pdb files. I used those to plug in to
> CDB1 for startover points. It all worked great.
> -- Now, when I want to do the final cutover from 11g ThisDB and ThatDB, I
> drop the current 18c ThxxDBX PDBs and attempt to create new PDBs from the
> last .pdb files. I want to name them ThisDB and ThatDB, and can create them
> OK. However, when I attempt to open them I get:
>
> Alter Pluggable Database ThisDB Open Read Write ;
> Error report -
> ORA-44303: service name exists
> ORA-44775: Pluggable database service cannot be created.
> 44303. 0000 - "service name exists"
> *Document: Yes
> *Cause: This service name was already in existence.
> *Action: Provide a new unique service name.
>
> I query v$Services and there is no ThisDB service. I've looked through the
> 18c docs and on MOS, but can't find anywhere that there's a restriction on
> Service Names. It looks to me like I can have ThisDBX and ThisDBY, but
> can't have the subset ThisDB. Is that a fact? If so, where is it
> documented? If not, can I fix this?
>
> I've tried it by using the Service_Name_Convert function, but that didn't
> work either and the error message said to try to use the "appropriate"
> Service_Name_Convert string but, of course, no clue as to what that might
> be.
>
> I'm at a loss.
> Thanks.
> --
> Jack C. Applewhite - Database Administrator
> Austin I.S.D. - MIS Department
> 512.414.9250 (wk)
>
> I cannot help but notice that there is no problem between us that cannot
> be solved by your departure. -- Mark Twain
> Confidentiality Notice: This email message, including all attachments, is
> for the sole use of the intended recipient(s) and may contain confidential
> student and/or employee information. Unauthorized use of disclosure is
> prohibited under the federal Family Educational Rights & Privacy Act (20
> U.S.C. §1232g, 34 CFR Part 99, 19 TAC 247.2, Gov’t Code 552.023, Educ. Code
> 21.355, 29 CFR 1630.14(b)(c)). If you are not the intended recipient, you
> may not use, disclose, copy or disseminate this information. Please call
> the sender immediately or reply by email and destroy all copies of the
> original message, including attachments.
>

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Jul 06 2020 - 13:32:15 CEST