Re: 18c Service Names

Turns out I misused the Service_Name_Convert part of creating the PDBs from the .pdb files. It's all better now.

However, a "gotcha" of using it is that it will replace the leading part of any target Service_Name matching the new Service_Name, as in:

   Service_Name_Convert('MYDB','HISDB')
...will change the following...

MYDB          to HISDB          <<< Expected
MYDB10      to HISDB10      <<< NOT Expected

I had to play with that a couple times. Even using double quotes didn't limit the changes. I'm so glad I had been cloning, unplugging to .pdb files, and re-creating from them. I did a Lot of dropping PDBs and re-creating them, either from .pdb files or as clones.

Also, when you use DBMS_Service.Delete_Service, it just marks the service as Deleted, but it's still in Sys.Service$. I couldn't get rid of 'em, not even with srvctl. However, it could be I'm still buggering things up.

Anyway, I'm OK and forging ahead.

--
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
________________________________
From: Jack Applewhite <jack.applewhite_at_austinisd.org>
Sent: Monday, July 6, 2020 10:31
To: ludovico.caldara_at_gmail.com <ludovico.caldara_at_gmail.com>; Jack Applewhite <jack.applewhite_at_austinisd.org>
Cc: oracle-l_at_freelists.org <oracle-l_at_freelists.org>
Subject: Re: 18c Service Names

After a bit more reading, I'm thinking my problem is with Oracle Restart. This from the Administrator's Guide:

"If you install Oracle Restart by installing the Oracle Grid Infrastructure for Standalone Servers and then create your database, the database is automatically added to the Oracle Restart configuration, and is then automatically restarted when required."

That's exactly my situation on these X8s. In fact, I used dbca to create the CDBs and add PDBs originally. So I think I either need to learn the srvctl commands or revisit dbca to see if I can modify these services.

Will let you know.
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
________________________________
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Jack Applewhite <jack.applewhite_at_austinisd.org>
Sent: Monday, July 6, 2020 09:39
To: ludovico.caldara_at_gmail.com <ludovico.caldara_at_gmail.com>
Cc: oracle-l_at_freelists.org <oracle-l_at_freelists.org>
Subject: Re: 18c Service Names

Hi Ludovico,

I tried this in the 2nd CDB that I'd created named FCC.
First, I created a new PDB from the last .pdb  file I'd unplugged to. The names are real, pretty straightforward, as we're AISD and AISDDW is our Data Warehouse
Then I created a new PDB from it simply named DW and was able to open it.  Sure enough, the old service was still there.

sys_at_fcc-dw > select name,network_name from sys.service$ ;
AISDDW
AISDDW

DW
DW

I ran your procedure, but it changed nothing, so I tried to operate on the AISDDW service alone, but encountered this:

sys_at_fcc-dw > execute dbms_service.stop_service(service_name => 'AISDDW')

Error starting at line : 1 in command -
BEGIN dbms_service.stop_service(service_name => 'AISDDW'); END;
Error report -
ORA-44304: service AISDDW 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 519
ORA-06512: at line 1
44304. 0000 -  "service %s does not exist"


*Document: Yes

*Cause:    The specified service was not in existence.


*Action:   Provide a valid service name.


sys_at_fcc-dw >

sys_at_fcc-dw > select name,network_name from sys.service$;
AISDDW
AISDDW

DW
DW

sys_at_fcc-dw > execute dbms_service.delete_service(service_name => 'AISDDW')

Error starting at line : 1 in command -
BEGIN dbms_service.delete_service(service_name => 'AISDDW'); END;
Error report -
ORA-44304: service AISDDW 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
44304. 0000 -  "service %s does not exist"


*Document: Yes

*Cause:    The specified service was not in existence.


*Action:   Provide a valid service name.


sys_at_fcc-dw >

So, how does it show up if it doesn't exist?

From the FCC CDB I created the AISDDW PDB in the FCA CDB, but couldn't open it because of the service name error.  Since it's not open I can't even query Sys.Service$.

I am quite confused. Any further advice? Am I missing something basic?

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
________________________________
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Ludovico Caldara <ludovico.caldara_at_gmail.com>
Sent: Monday, July 6, 2020 06:32
Cc: oracle-l_at_freelists.org <oracle-l_at_freelists.org>
Subject: Re: 18c Service Names

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<https://linkprotect.cudasvc.com/url?a=http%3a%2f%2fr_services.name%2f&c=E,1,Y55dnoFAieu4W6BV7wLoPl0Y_HbDnua4CdJGEpxISvCE5MmZSbtJr16V8jw5-7fCzEc7wY8T__-UXuPDbM5MFkbpZDyyt4d_WEEflHXj32w,&typo=1>);
                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<https://linkprotect.cudasvc.com/url?a=http%3a%2f%2fr_services.name%2f&c=E,1,be2EW0fK-dFJ6Q9KkZA0dSp_bqUBjJwWnZ2BmLXhoktrEU1XEpc8Y6j3yfdx36Zb5Yq71WtrWvqizBwFMJuwqTXj7diDHHSrYBeJTWiVttQLXyZM&typo=1>);
                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<mailto: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.
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.
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