Re: ORA-12028 errors on REFRESH FAST - illegal database name

From: Lou Avrami <avramil@xxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Date: Wed, 25 Nov 2009 03:03:24 -0500 (EST)
Hi everyone,
  
Just thought someone might like to know that the failures of the REFRESH FAST 
materialized views were cause by a DB_DOMAIN value that included a hypen!  
Interestingly, database links created fine and worked, REFRESH COMPLETE 
materialized views also worked.  The only failures that appears were with fast 
refresh materialized views.  Even then, it was ORA-12028 errors that appeared, 
instad of errors like ORA-02083, the database name contains and illegal value.
  
I had to do an 'alter database rename global_name' as well as update props$ to 
remove the domain name with the hypen in it.  Once that was done, REFRESH FAST 
worked.
  
Lou Avrami
  
---- Lou Avrami <avramil@xxxxxxxxxxxxxx> wrote:

We are receiving ORA-12028 errors when trying to create REFRESH FAST 
materialized views in an 11.1.0.7 database.
 
This is a new data warehouse-type of database, taking the place of an old 
9.2.0.8 database.  
 
One of the applications has several materialized views which pull data from 
9.2.0.8 and 10.2.0.4 databases over database links.

The current production target database is 9.2.0.8, NLS characterset US7ASCII.  
One database from where data is being pulled is 10.2.0.4, NLS characterset 
AL32UTF8.
Two databases are 9.2.0.8, NLS characterset WE8ISO8859P1.
 
The REFRESH FAST materialized have been running in the old 9i database for over 
a year.
 
The new target database is 11.1.0.7, NLS characterset AL32UTF8.
 
When trying to create the REFRESH FAST materialized views in the new 11g 
database, ORA-12028 errors are received.
 
Per MetaLink Note 259506.1, I converted the new 11g database from AL32UTF8 to 
UTF8.
 
 When I then tried to create the REFRESH FAST materialized views, I still 
received ORA-12028 errors.
 
Would anyone have any troubleshooting suggestions?
 
Thanks,
Lou Avrami
avramil@xxxxxxxxxxxxxx
 
Below is a sample error from the run after the conversion to UTF8.
 
SQL> CREATE MATERIALIZED VIEW "FM1"."MV_RECENT_ALERTSS"
   2    COMPRESS
   3    TABLESPACE "SOUTH_DEFAULT"
   4    BUILD IMMEDIATE
   5    USING INDEX TABLESPACE "FM1_INDEX"
   6    REFRESH FAST ON DEMAND
   7    WITH PRIMARY KEY USING DEFAULT LOCAL ROLLBACK SEGMENT
   8    DISABLE QUERY REWRITE
   9    AS SELECT
  10    "RECENT_ALERTSS"."SEQ_NO" "SEQ_NO",
...
...
  35    "RECENT_ALERTSS"."FORWARDING_SYS" "FORWARDING_SYS"
  36  FROM "FM1"."RECENT_ALERTSS"@FM1_S_LINK "RECENT_ALERTSS";
 FROM "FM1"."RECENT_ALERTSS"@FM1_S_LINK "RECENT_ALERTSS"
                             *
 ERROR at line 36:
 ORA-12028: materialized view type is not supported by master site
 @FM1_S_LINK.NATAPPSUNIX.ENG.COM 
  
 
--
//www.freelists.org/webpage/oracle-l
Re: ORA-12028 errors on REFRESH FAST - illegal database name

Other related posts:
