##########################
## Error
##########################
ORA-00990: missing or invalid privilege
Failing sql is:
GRANT GLOBAL REWRITE TO "ABC_OWNER"
### Full Error
ORA-39083: Object type SYSTEM_GRANT failed to create with error:
ORA-00990: missing or invalid privilege
Failing sql is:
GRANT GLOBAL REWRITE TO "ABC_OWNER"
##########################
# Error Occurred
##########################
Error occured while import of database in 11.2.0.4.0 version
##########################
## Command Executed
##########################
Schema level import...
**************************************** Step By Step Analysis ******************************************************
#########################################
# 1) Check Database registry
#########################################
set lines 200
col action_time for a30
col action for a25
col namespace for a9
col version for a10
col id for 99999999
col comments for a25
col bundle_series for a25
select * from registry$history;
ACTION_TIME ACTION NAMESPACE VERSION ID COMMENTS BUNDLE_SERIES=====================================================================================================================
------------------------------ ------------------------- --------- ---------- --------- ------------------------- -------------------------
22-JAN-07 04.03.57.000000 PM APPLY 5689799 CPUJan2007
13-AUG-08 11.42.39.793310 AM UPGRADE SERVER 10.2.0.3.0 Upgraded from 8.1.7.0.0
29-JAN-09 08.23.06.616350 PM CPU SERVER 10.2.0.3.0 7592354 CPUJan2009
27-MAY-09 10.15.51.127263 PM APPLY SERVER 10.2.0.4 4 CPUApr2009 CPU
27-MAY-09 12.56.09.358348 AM UPGRADE SERVER 10.2.0.4.0 Upgraded from 10.2.0.3.0
01-AUG-09 04.06.44.638446 PM APPLY SERVER 10.2.0.4 5 CPUJul2009 CPU
31-OCT-09 05.23.10.775509 PM APPLY SERVER 10.2.0.4 6 CPUOct2009 CPU
24-APR-10 03.34.35.916439 PM APPLY SERVER 10.2.0.4 7 CPUApr2010 CPU
27-JUL-10 07.47.22.432114 PM APPLY SERVER 10.2.0.4 8 CPUJul2010 CPU
14-NOV-10 08.56.47.965186 AM APPLY SERVER 10.2.0.4 9 CPUOct2010 CPU
14-NOV-10 08.59.00.083118 AM CPU 6452863 view recompilation
30-JAN-11 07.49.41.612062 AM APPLY SERVER 10.2.0.4 10 CPUJan2011 CPU
20-MAY-11 11.13.20.184868 AM APPLY SERVER 10.2.0.4 11 CPUApr2011 CPU
10-NOV-11 06.31.40.801048 PM VIEW RECOMPILE 8289601 view recompilation
10-NOV-11 06.31.40.830187 PM UPGRADE SERVER 10.2.0.5.0 Upgraded from 10.2.0.4.0
24-JAN-12 02.57.57.932675 PM APPLY SERVER 10.2.0.5 6 CPUJan2012 CPU
24-APR-12 07.11.15.922326 PM APPLY SERVER 10.2.0.5 7 CPUApr2012 CPU
24-JUL-12 09.14.33.976507 PM APPLY SERVER 10.2.0.5 8 CPUJul2012 CPU
23-OCT-12 02.30.22.601483 PM APPLY SERVER 10.2.0.5 9 CPUOct2012 CPU
24-JAN-13 01.22.55.748863 PM APPLY SERVER 10.2.0.5 10 CPUJan2013 CPU
24-APR-13 11.05.11.000911 AM APPLY SERVER 10.2.0.5 11 CPUApr2013 CPU
24-JUL-13 05.15.10.280957 PM APPLY SERVER 10.2.0.5 12 CPUJul2013 CPU
01-NOV-13 04.54.12.078568 PM APPLY SERVER 10.2.0.5 12 CPUJul2013 CPU
10-SEP-14 12.44.10.156902 PM VIEW INVALIDATE 8289601 view invalidation
10-SEP-14 12.44.28.942689 PM UPGRADE SERVER 11.2.0.4.0 Upgraded from 10.2.0.5.0
10-SEP-14 12.51.57.396320 PM APPLY SERVER 11.2.0.4 0 Patchset 11.2.0.2.0 PSU
18-OCT-14 10.26.17.616975 AM APPLY SERVER 11.2.0.4 4 CPUOct2014 CPU
03-FEB-15 10.20.24.622435 PM APPLY SERVER 11.2.0.4 5 CPUJan2015 CPU
18-APR-15 10.59.02.947126 PM APPLY SERVER 11.2.0.4 6 CPUApr2015 CPU
18-JUL-15 10.21.02.380054 PM APPLY SERVER 11.2.0.4 7 CPUJul2015 CPU
24-OCT-15 10.47.20.282415 PM APPLY SERVER 11.2.0.4 8 CPUOct2015 CPU
22-JAN-16 09.47.02.078327 PM APPLY SERVER 11.2.0.4 160119 CPUJan2016 CPU
Elapsed: 00:00:00.04
12:33:16 SQL>
#########################################
# 2) Check Database System Privilege
#########################################
Select * from SYSTEM_PRIVILEGE_MAP where name like '%REWRITE%' order by name;
13:20:42 SQL> Select * from SYSTEM_PRIVILEGE_MAP where name like '%REWRITE%' order by name;
PRIVILEGE NAME PROPERTY
---------- ---------------------------------------- ----------
-211 GLOBAL REWRITE 0
-210 QUERY REWRITE 0
=====================================================================================================================
#########################################
# 3) Reason for Failure
#########################################
From Step 1), we can see that Database was upgraded from 8.1.7 version.
From Step 2), System privilege name is "GLOBAL REWRITE".
The GLOBAL REWRITE and REWRITE privileges were introduced in v8.1.3. Then the privileges were renamed to GLOBAL QUERY REWRITE and QUERY REWRITE in v8.1.5.
The upgrade script does update both system privilege names when upgrading the database to v8.1.5. So, the database was probably upgraded without this step being completed.
=====================================================================================================================
##########################
## Solution
##########################
#### Solution would be to update the rows with the new privilege in Source Database
update SYSTEM_PRIVILEGE_MAP
set name='QUERY REWRITE' where name='REWRITE';
update SYSTEM_PRIVILEGE_MAP
set name='GLOBAL QUERY REWRITE' where name='GLOBAL REWRITE';
Commit;
=====================================================================================================================
I didnt update the rows in Source because we are migrating the database to 11.2.0.4 which already has the right system privilge name to it.
=====================================================================================================================
Target :
SQL> Select * from SYSTEM_PRIVILEGE_MAP where name like '%REWRITE%' order by name;
PRIVILEGE NAME PROPERTY
---------- ---------------------------------------- ----------
-211 GLOBAL QUERY REWRITE 0
-210 QUERY REWRITE 0
=====================================================================================================================
Comments Are Always welcome
=====================================================================================================================
No comments:
Post a Comment