6 Replies Latest reply on Dec 20, 2012 5:40 PM by rp0428

    Databases consolidation with schema rename

    Nicosa-Oracle
      Hi,

      We are planning on consolidating different databases <i>(each on a different server)</i> into one unique database.

      My main concern is that the schema name is the same in each country <i>(let's call it SCOTT for the example)</i>, and that it should be "renamed" to a username that includes the country code <i>(such as SCOTT_FR for France, SCOTT_UK for UnitedKingdom, SCOTT_SP for Spain, etc...)</i>.
      This would be easily handled using REMAP_SCHEMA parameter of impdp, but the SCOTT schema is not the only one in each country.

      Let me explain :
      - Each country actually have 2 schemas : SCOTT and SCOTT_RP who both have objects in their own schema
      - SCOTT also have privileges granted on SCOTT_RP's object, and SCOTT_RP has privileges on SCOTT's objects

      I was about to do things as follows :<ol>
      <li>Extract user DDL for each schema, using some replace to rename it including the country code <i>(For France, SCOTT becomes SCOTT_FR, and SCOTT_RP becomes SCOTT_RP_FR)</i>
      <li>Extract granted_ddl for each schema doing the same replace on names to include country code
      <li>Expdp each schema
      <li>Create the new users <i>(whose name includes country code)</i> on the new database from DDL extracted at step 1
      <li>Impdp the objects using REMAP_SCHEMA
      <li>Grant privileges extracted at step 2
      <li>Go back to step one for the next country
      </ol>
      The source databases are 11.2.0.1, and target is 11.2.0.3

      My concerns are :
      - Is that a good way to do it <i>(I actually don't see any other way of doing it)</i>
      - I am missing something ? <i>(My biggest fear is PL/SQL code that would reference packages in another schema using the old name that does not include the country code)</i>

      Feel free to shed any light on aspect you would feel appropriate.
        • 1. Re: Databases consolidation with schema rename
          Dean Gagne-Oracle
          Nicosa,

          You have a valid concern. The Metadata api gets some of the ddl from the dictionary and does not parse it. So, if you have a pl/sql object, like package body, that specifically references a different schema, then that reference may not be remapped if you do a remap_schema. If it were me, then I would run the import using sqlfile and then check the generated sqlfile for the occurrence of the schema that you remapped. If it all looks good, then run the import command without the sqlfile.

          Is there a reason you don't do all of the schemas at the same time. This may be a better approach. Let's say you have this:

          schema a granting x to schema b
          schema b granting x to schema a

          If you were to just export schema a and remap a to a_usa, then the grant to schema b would still go to schema b. If you exported both a and b then

          remap_schema=a:ausa b:b_usa

          Then both would be remapped and your grants should be ok.. Again, it would be best to try this with sqlfile and verify the sqlfile before running.

          Dean
          1 person found this helpful
          • 2. Re: Databases consolidation with schema rename
            Nicosa-Oracle
            Hi Dean,

            Thanks for taking some time to answer.

            SQLFILE parameter looks like a good idea to me. +(didn't know this parameter)+

            I have a "sandbox" database so I can give all the tries I want before doing it "live".
            I did the export of both schema at once, and used the remap_schema "remap_schema=a:ausa b:b_usa"

            As you mentioned and as expected, my packages are mostly invalid because they either :
            - reference PL/SQL from the other schema
            - relies on table that were not created because the table is using "user defined types" that raised error on import +(I barely remember to have read some docs stating that there is something about OID and import of User Defined Types...)+

            I'll also have to handle materialized views and triggers that raise errors while importing +(didn't had time yet to check why they're failing)+

            I'm not on site this week, so I can't give it a try for the moment, but I'll sure try to benefit from the SQLFILE next week.
            I'm leaving the thread marked as unanswered so that anyone with an idea can share.

            Thanks again for your valuable answer.
            • 3. Re: Databases consolidation with schema rename
              Nicosa-Oracle
              Ok, more info on the question :

              I've had very little time to work on that subject. Now I have the opportunity to try the sqlfile parameter, which is great !

              Here's How I proceed :
              I do an impdp with sqlfile parameter and some include filters on PACKAGE/PROC/FUNCTION. This spools the packages to a text file.

              Using sed, I managed to change every occurence of "USERA" into "USERA_TW" and every occurence of "USERB" into "USERB_TW".
              The sed I use is the following :
              sed -e 's/\(^\|\s\)\"\?\(USERA\|USERB\)\"\?\./\1\2_'${CC}'./g'  -e 's/^-- CONNECT SYS\(TEM\)\?$/CONNECT \/ AS SYSDBA/g' -e 's/^-- \(CONNECT USER.*$\)/\1\/USERPWD/g' < $infile >$outfile
              The ${CC}being first set to TW (it's a country code).
              This sed also :
              - changes the "-- CONNECT SYS" or "-- CONNECT SYSTEM" into "CONNECT / AS SYSDBA"
              - adds the password for USERA and USERB (which is at this time set to a dummy value).

              One the sed done, I just have to execute the $outfile.

              I can then do the real impdp (using exclude parameters for PACKAGE/PROC/FUNCTION this time).

              SQLFILE parameter rocks ! (and so sed)
              ------
              All this would be perfect if I hadn't hit an unexpected bump....

              Few tables are using USER DEFINED DATA TYPE (some kind of object type...).
              For some unknown reason the impdp can succesfully create the DATA TYPE and the TABLE structure and (hash) partitions, but then raise an error :
              ORA-31693: Table data object "USERA_TW"."MY_SPEC_TABLE":"SYS_P869" failed to load/unload and is being skipped due to error:
              ORA-02354: error in exporting/importing data
              ORA-26014: unexpected error on type MY_USER_DEFINED_DTYPE while retrieving type name
              ORA-01403: no data found
              After a few tests, my guess is that the loading of the data might reference the datatype using the "old" name (USERA) instead of the new one (USERA_TW).

              I'll have to find a workaround... which might certainly be to load such tables in a USERA schema then copy the content to that same table in schema USERA_TW.
              • 4. Re: Databases consolidation with schema rename
                rp0428
                >
                I was about to do things as follows :
                Extract user DDL for each schema, using some replace to rename it including the country code (For France, SCOTT becomes SCOTT_FR, and SCOTT_RP becomes SCOTT_RP_FR)

                Extract granted_ddl for each schema doing the same replace on names to include country code

                Expdp each schema

                Create the new users (whose name includes country code) on the new database from DDL extracted at step 1

                Impdp the objects using REMAP_SCHEMA

                Grant privileges extracted at step 2

                Go back to step one for the next country
                >
                Just use that plan and use REMAP_SCHEMA when you extract the DDL. In your other abandoned thread you were not using REMAP_SCHEMA properly.
                GET_DDL('USER',...) of userA remapped to userB

                The REMAP_SCHEMA needs to be applied to a MODIFY transform and you weren't doing that.

                See example 20-5 in the Utilities doc
                Example 20-5 Modifying an XML Document
                
                1.Create a function named remap_schema:
                
                CREATE OR REPLACE FUNCTION remap_schema RETURN CLOB IS
                -- Define local variables.
                h NUMBER; --handle returned by OPEN
                th NUMBER; -- handle returned by ADD_TRANSFORM
                doc CLOB;
                BEGIN
                
                -- Specify the object type.
                h := DBMS_METADATA.OPEN('TABLE');
                
                -- Use filters to specify the particular object desired.
                DBMS_METADATA.SET_FILTER(h,'SCHEMA','HR');
                DBMS_METADATA.SET_FILTER(h,'NAME','TIMECARDS');
                
                -- Request that the schema name be modified.
                th := DBMS_METADATA.ADD_TRANSFORM(h,'MODIFY');
                DBMS_METADATA.SET_REMAP_PARAM(th,'REMAP_SCHEMA','HR','SCOTT');
                
                -- Request that the metadata be transformed into creation DDL.
                th := DBMS_METADATA.ADD_TRANSFORM(h,'DDL');
                
                -- Specify that segment attributes are not to be returned.
                DBMS_METADATA.SET_TRANSFORM_PARAM(th,'SEGMENT_ATTRIBUTES',false);
                
                -- Fetch the object.
                doc := DBMS_METADATA.FETCH_CLOB(h);
                
                -- Release resources.
                DBMS_METADATA.CLOSE(h);
                RETURN doc;
                END;
                / 
                2.Perform the following query:
                
                SELECT remap_schema FROM dual;
                The output looks similar to the following:
                
                CREATE TABLE "SCOTT"."TIMECARDS"
                   (    "EMPLOYEE_ID" NUMBER(6,0),
                        "WEEK" NUMBER(2,0),
                        "JOB_ID" VARCHAR2(10),
                        "HOURS_WORKED" NUMBER(4,2),
                         FOREIGN KEY ("EMPLOYEE_ID")
                          REFERENCES "SCOTT"."EMPLOYEES" ("EMPLOYEE_ID") ENABLE
                   )
                If you are familiar with XSLT, you can add your own user-written transforms to process the XML.
                Did you notice these two lines?
                -- Request that the schema name be modified.
                th := DBMS_METADATA.ADD_TRANSFORM(h,'MODIFY');
                DBMS_METADATA.SET_REMAP_PARAM(th,'REMAP_SCHEMA','HR','SCOTT');
                The REMAP is NOT applied to the OPEN handle - it is applied to the TRANSFORM handle of the MODIFY transform. In your original thread you were applying remap to the session transform but you hadn't added a MODIFY transform to it.

                See the DBMS_METADATA chapter of the PL/SQL Packages and Types doc
                http://docs.oracle.com/cd/E14072_01/appdev.112/e10577/d_metada.htm
                >
                •MODIFY - The document is modified as directed by transform and remap parameters. The output of this transform is an XML document. If no transform or remap parameters are specified, the document is unchanged.
                >


                Try the example and you will see that it works. After all DBMS_METADATA is what Oracle's impdp and expdp use.

                If you know XSLT you can write your own transforms and you can also use GET_SXML to get the metadata in XML format and then modify that XML and use the XML to recreate the objects. You don't have to use DDL - you can use XML: see the PUT function of DBMS_METADATA.
                1 person found this helpful
                • 5. Re: Databases consolidation with schema rename
                  Nicosa-Oracle
                  Hi rp0428,

                  Thanks a lot for the explanation.

                  Still the documentation allows to think that DBMS_METADATA.SESSION_TRANSFORM can be used for [url http://docs.oracle.com/cd/E14072_01/appdev.112/e10577/d_metada.htm#BGBCGCII]SET_REMAP_PARAM :
                  <u>transform_handle :</u>
                  Either (1) the handle returned from ADD_TRANSFORM, or (2) the enumerated constant SESSION_TRANSFORM that designates the DDL transform for the whole session.

                  Note that the handle returned by OPEN is not a valid transform handle.

                  For SET_REMAP_PARAM, the transform handle must designate the MODIFY transform.
                  The part, here in bold, misled my understanding.

                  I fail to fully understand the MODIFY concept. The doc seems to relate this to XML and/or XSLT (which I know nothing about).
                  But I'm not dealing with XML/XSLT at all (even if I understand that it is certainly being used "under the hood").



                  Moreover, I don't know if the remap would correctly handle PL/SQL code from USERA schema that explicitly references objects from USERB schema.
                  (I might give it a try, once I found a valid workaround for my user defined data type issue with impdp)
                  • 6. Re: Databases consolidation with schema rename
                    rp0428
                    >
                    Still the documentation allows to think that DBMS_METADATA.SESSION_TRANSFORM can be used for SET_REMAP_PARAM :
                    transform_handle :
                    Either (1) the handle returned from ADD_TRANSFORM, or (2) the enumerated constant SESSION_TRANSFORM that designates the DDL transform for the whole session.
                    
                    Note that the handle returned by OPEN is not a valid transform handle.
                    
                    For SET_REMAP_PARAM, the transform handle must designate the MODIFY transform.
                    The part, here in bold, misled my understanding.
                    >
                    I agree that it is misleading. The reason is that the section discusses two different functions and each function requires a different type of transform_handle.
                    >
                    DBMS_METADATA.SET_TRANSFORM_PARAM (
                    transform_handle IN NUMBER,
                    . . .
                    DBMS_METADATA.SET_REMAP_PARAM (
                    transform_handle IN NUMBER,
                    >
                    The SET_TRANSFORM_PARAM sets values for the transform you specify for 'transform_handle'. And that can be either of the two types of 'handles' the quote says.

                    But the SET_REMAP_PARAM only sets values for a MODIFY transform
                    For SET_REMAP_PARAM, the transform handle must designate the MODIFY transform.
                    It would be clearer if they used separate sections for each of those.
                    >
                    I fail to fully understand the MODIFY concept. The doc seems to relate this to XML and/or XSLT (which I know nothing about).
                    But I'm not dealing with XML/XSLT at all (even if I understand that it is certainly being used "under the hood").
                    >
                    There are several types of transforms. Table 84-4 ADD_TRANSFORM Function Parameters itemizes the predefined types: ALTERDDL, ALTERXML, DDL, MODIFY, MODIFYSXML, SXML, SXMLDDL.

                    Each of those types can take different parameters. The MODIFY type is the one that takes the SET_REMAP_PARAM values. You have to manually create a MODIFY transform and then modify it using SET_REMAP_PARAM or there won't be any remapping.

                    You are correct - XML/XSLT is being used. If you use 'GET_SXML' instead of 'GET_DDL' this is a sample of what you would get:
                    SELECT dbms_metadata.get_sxml('TABLE', 'EMP', 'SCOTT') FROM dual;
                    
                      <TABLE xmlns="http://xmlns.oracle.com/ku" version="1.0">
                       <SCHEMA>SCOTT</SCHEMA>
                       <NAME>EMP</NAME>
                       <RELATIONAL_TABLE>
                          <COL_LIST>
                             <COL_LIST_ITEM>
                                <NAME>EMPNO</NAME>
                                <DATATYPE>NUMBER</DATATYPE>
                                <PRECISION>4</PRECISION>
                                <SCALE>0</SCALE>
                             </COL_LIST_ITEM>
                             <COL_LIST_ITEM>
                    Notice the SCHEMA element? That is the value that will get modified when you remap. When you use 'GET_DDL' Oracle first gets XML and then transforms it using XSLT by applying the transforms you have specified. For a remap it looks for the SCHEMA element value of SCOTT (if that is one of the remaps you used) and replaces it with the value you specified. Then it converts the XML to DDL using the 'pretty' and other parameters you specified.

                    To use REMAP you have to first add a MODIFY transform to add the remap values to. You can't use SQL to add a MODIFY transform to the default session you have to use code and the OPEN or OPENW functions as shown in the example code I posted.
                    >
                    Moreover, I don't know if the remap would correctly handle PL/SQL code from USERA schema that explicitly references objects from USERB schema.
                    (I might give it a try, once I found a valid workaround for my user defined data type issue with impdp)
                    >
                    No - it won't. Oracle does not get involved with what references your code uses except during compilation or execution. Resolving those naming conflicts is something you will have to do manually.







                    For SET_REMAP_PARAM, the transform handle must designate the MODIFY transform.