Re: 18c Service Names

Hi Jack,
Good to see that you have managed to make it work. Good job! When playing with PDBs it becomes really important to have good naming conventions to avoid this kind of collision.

Il giorno lun 6 lug 2020 alle ore 23:52 Jack Applewhite < jack.applewhite_at_austinisd.org> ha scritto:

> 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
> MYDBTEST to HISDBTEST <<< 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> 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