3 Replies Latest reply on May 15, 2014 7:48 PM by rp0428

    Error While creating migration repository

    vikramrathour

      Hi,

       

      I have installed Oracle12c database on Windows 7. I created a new PDB and a user. I then run the Create Repository utility to create a new migration repository, however I get below errors -

       

      http://i61.tinypic.com/fuaz6.jpg

       

      http://i58.tinypic.com/33yrojt.png

       

      Can you please help.

      My SQL Developer version is 4.0.2.15

       

      Thanks

      Vikram R

        • 1. Re: Error While creating migration repository
          Dermot ONeill-Oracle

          Hi Vikram,

           

          A migration repository requires the following privileges and it also required a USERS tablespace. If these are missing then the migration repository will not create successfully.

           

          --DROP MIGREP IF ALREADY EXISTS

          DROP USER MIGREP CASCADE;

           

          --Connect to SYSTEM

          CREATE USER MIGREP IDENTIFIED BY MIGREP;

          GRANT CONNECT, RESOURCE, CREATE VIEW, CREATE MATERIALIZED VIEW TO MIGREP;

           

          --Create a USERS tablespace if not present (Change DATAFILE dir to suit)

          CREATE BIGFILE TABLESPACE USERS DATAFILE '/scratch/oracledatabase/u01/oradata/DB12GR1C/migrep/USERS.dat' SIZE 20M AUTOEXTEND ON;

          ALTER DATABASE DEFAULT TABLESPACE USERS;

           

          --Allow MIGREP to write to the USERS tablespace

          ALTER USER MIGREP QUOTA UNLIMITED ON USERS;

           

          Create a connection in SQL Developer to the MIGREP user and Associate a Migration Repository with it.

           

          Hope this helps,

          Dermot

          SQL Developer Team

          1 person found this helpful
          • 2. Re: Error While creating migration repository
            dbaOnTap

            You will also need to include: Grant unlimited tablespace to "username" as dba.

            This new in 12c.  The first part of this blog My Oracle Experience: Story by a non-DBA: Database Migration with SQLDev EA1 v4.0 talks about the migration repository

            • 3. Re: Error While creating migration repository
              rp0428
              You will also need to include: Grant unlimited tablespace to "username" as dba.

              Please clarify and/or explain that advice.

               

              Why won't the normal solution of assigning the user the needed quota on the appropriate tablespace work?

              This new in 12c.

              Again - I think that statement is misleading. What is 'new in 12c' is explained in the Database Security Guide:

              http://docs.oracle.com/cd/E16655_01/network.121/e17607/release_changes.htm#BABEBGDI

              RESOURCE Role No Longer Grants the UNLIMITED TABLESPACE Privilege

               

              Starting with this release, the RESOURCE role will no longer grant the UNLIMITED TABLESPACE system privilege by default. If you want users to have this system privilege, then you must manually grant it to them.

               

              See "Predefined Roles in an Oracle Database Installation" for more information about default roles.

              It has been standard practice for several DB generations for the RESOURCE role to be granted to most every user. Unfortunately that role included the UNLIMITED TABLESPACE privilege until version 12c.

               

              That 'unlimited' grant is a security problem since the prvilege allows an unlimited quota on ANY tablespace and potentially allowed a 'rogue' user/app to exhaust the space on critical production tablespaces.

               

              The RESOURCE role was modified in 12c specifically to address that security issue. Now tablespace quotas need to be explicitly granted. That allows the DBA to maintain control over the usage of tablespace quotas.

               

              The 'new in 12c' part is just the non-grant of the UNLIMITED TABLESPACE privilege. The solution, however is NOT to grant that privilege manually; that just circumvents the security control that the 12c change was designed to address.

               

              The solution is to grant the proper quota on ONLY the tablespaces that a user needs a quota on.

                The first part of this blog My Oracle Experience: Story by a non-DBA: Database Migration with SQLDev EA1 v4.0 talks about the migration repository

              Unfortunately that blog repeats the same advice of 'grant UNLIMITED TABLESPACE'.

               

              Please explain why a normal grant of quota on a single tablespace will not solve OPs problem. Or multiple grants if quotas are needed for multiple tablespaces:

              http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_8003.htm

              create user test1 identified by test1

              quota 10m on tb1

              quota 20m on tb2

              quota 30m on tb3

              Direct grants should work. If so please modify your advice to OP to specify what tablespace(s) quotas need to be granted on and what size quotas are needed.

              1 person found this helpful