1 2 Previous Next 20 Replies Latest reply: Feb 28, 2008 7:21 AM by yvesthib RSS

    MLOGS not purge after a fast refresh (Materialized view) ...

    yvesthib
      Hi,

      I have a database on my own site (Quebec) that has 36 materialized views refresh by a master table site that is physically at Montreal. The MVIEWs are refreshed every day of the week (monday to friday) at 7h00 until 19h00. We use the "fast refresh" technic to refresh the materialized views.

      Last weekend, I transfered this database on a new server. So, I created a new SID (I rename the old SID "ORCL" by "ATQP") and I created this instance with a DB_BLOCK_SIZE of 16k rather than 8k. It's the only difference between the two instances (the old one and the new one). I also created the owner schema "ATQP" and I made an Export on the old server followed by an Import of the schema that owned the Materialized views (not a Full import) on the new server. The export was made many hours (saturday morning) after the last refresh of the week (friday, at 19h00) on the old server.

      After the successul import on my new server, I'm looking for the information about the creation of the MVIEW. Everything seems OK. I can see that the last refresh date indicated "2008-02-22 19:00" (date / time of the last refresh of the week). I made a quick test on a small MVIEW (a fast refresh) and it worked, no error.

      Yesterday morning, the DBA of the master table site mentionned us that the DELETE on the MLOGS didn't work on the master site. The refresh was made successfully but the MLOGS continue to grow up, again and again ... On my database, I'm looking in the SYS.SNAP$ table and I saw that the SNAPTIME date is '1950-01-01 12:00' rather than '2008-02-25 19:00'. In the SYS.SNAP_REFTIME, the SNAPTIME is OK but the LOADERTIME column contains the same strange date '1950-01-01 12:00'.

      I made a quick check on my old server and the SYS.SNAP$ table is empty !!! I think it's normal because the last refresh on this node was made last friday night and I made the transfert last saturday (I shutdown the old database after).

      I know how to solve the problem, by recreate the MVIEW, but it causes a big problem for us. We are in PRODUCTION and we don't want to refresh many millions of records. I think it will take a couple of days to refresh all the materialized views of my schema. And it doesn't sound good by the master table site management team ...

      The master table site "rush" us to solve the problem before the weekend. They don't want that we overcharge the network link between them and us by refreshing those MVIEWS.

      So, is it possible to make something simple to resynchronized the SNAPTIME date without refreshing completely all the materialized views ?

      If the master site table recreate the MLOG (purge manually), is it possible that it will solve our problem ?

      Is it also possible to recreate the MVIEW without refreshing the data (specify to Oracle to recreate the MVIEW but indicate that the table associated already exist) ?

      All of my materialized view are up to date concerning the data. I don't want to refresh all of it.

      Thank's in advance ...
        • 1. Re: MLOGS not purge after a fast refresh (Materialized view) ...
          damorgan
          A have never seen the behavior you are describing so I can't offer any suggestion based on experience but I do have a couple of thoughts on your post.

          1. How did you rename the database ... state the steps.

          2. Oracle performs all development and testing using 8K blocks. Going to 16K gains you nothing and puts you into an essentially untested environment. Base on my testing any performance gains are only in the sales of books written by those promoting larger blocks.

          3. What version of Oracle (3+ decimal places)?

          Have you tried dropping and recreating the MVs?
          • 2. Re: MLOGS not purge after a fast refresh (Materialized view) ...
            yvesthib
            Hi,

            We use the Oracle 10.2.0.3 version.

            How I said before, I transferred the old database to a new server. On this new machine, I created a new instance, that I named ATQP and I specified a db_block_size of 16k because we have a lot of read on this instance, but no update. On the old server, the instance was named ORCL with a db_block_size of 8k.

            I don't try to drop and recreate the MVIEW because it will cause a lot of data to transfert between the master table site and our table site (several tens of millions records to transfer).

            Thank's

            Yves
            • 3. Re: MLOGS not purge after a fast refresh (Materialized view) ...
              yvesthib
              Hi again,

              I forgot to tell that the refresh (fast refresh) of the MVIEWs works well. It's simply the purge of the MLOGs of the master table site that it doesn't work.
              • 4. Re: MLOGS not purge after a fast refresh (Materialized view) ...
                JustinCave
                Depending on how, exactly, you transferred the old database to the new server, which is what Dan was asking, I'd suspect that the master site now believes there are two different slave databases. In that case, it would have to retain the information in the materialized view logs until the second database refreshed its materialized views.

                On the master site, does DBA_REGISTERED_MVIEWS show two rows with different MVIEW_SITE values for each of the materialized views? If so, you should be able to have the master site unregister the old server as a consumer of the materialized views.

                Justin
                • 5. Re: MLOGS not purge after a fast refresh (Materialized view) ...
                  yvesthib
                  Hi,

                  Effectively, we had 36 rows returned (it correspond of my 36 MVIEWS) and the MVIEW_SITE is associated with my old server.

                  You find the reason.

                  So, how we can unregister the old server as a consumer of the materialized views?

                  Thank you very much!
                  • 7. Re: MLOGS not purge after a fast refresh (Materialized view) ...
                    yvesthib
                    Hi,

                    Thank you very much.

                    As I undertand when I read the documentation, I need to do a DBMS_MVIEW.PURGE_MVIEW_FROM_LOG too.

                    Do you know if the execution of those packages can be done LIVE in production without a downtime or decreasing of performance or is it better to execute them tonight or during the night ?

                    Is it only the old site who is impacted (in performance) by the execution of those packages ?

                    We are in production and the master table site don't want to have down time in the middle of the day.

                    Thank you very much. Your help is appreciated.
                    • 8. Re: MLOGS not purge after a fast refresh (Materialized view) ...
                      JustinCave
                      I wouldn't expect that you'd need to purge the log. Once you unregister the materialized view on the old site, Oracle knows that the entries in the materialized view log are no longer needed by that materialized view, so it knows that they can be purged from the log. You should only need to purge the log if you want to keep the old site's materialized views registered with the master site (and thus accumulating change logs going forward) but you want to get rid of old changes.

                      The master site has more work to do when you unregister a materialized view. I wouldn't expect this to put a huge load on the master system, on the other hand, this seems like something that one would generally do overnight when there is relatively low activity.

                      Justin
                      • 9. Re: MLOGS not purge after a fast refresh (Materialized view) ...
                        yvesthib
                        Hi Justin,

                        The DBA of the master table site sent me a email about the fact he unregistered the MVIEW. He told me that the script for the unregistered worked well. He tried to purge the MLOG and he got the error ORA-23424 but I think it's normal (It's what you said yesterday about the fact that the UNREGISTER command also purge the MLOGS).

                        So, it seems that the MLOGS are still not purged (that's what the DBA of the master table site said). I ran the following query:

                        SQL> select * from DBA_MVIEW_LOGS@atqprod.world;

                        LOG_OWNER MASTER LOG_TABLE LOG_TRIGGER ROW PRI OBJ FIL SEQ INC
                        ------------------------------ ------------------------------ ------------------------------ -------
                        ATQ ALIAS_ANILOTS MLOG$_ALIAS_ANILOTS NO YES NO YES NO YES
                        ATQ ALIAS_INTERVENANTS MLOG$_ALIAS_INTERVENANTS NO YES NO YES NO YES
                        ATQ ANILOTS MLOG$_ANILOTS NO YES NO YES NO YES
                        ATQ CATEGORIES MLOG$_CATEGORIES NO YES NO YES NO YES
                        ATQ CODES_POSTAUX MLOG$_CODES_POSTAUX NO YES NO YES NO YES
                        ATQ COMMANDES MLOG$_COMMANDES NO YES NO YES NO YES
                        ATQ COMMUNICATIONS MLOG$_COMMUNICATIONS NO YES NO YES NO YES
                        ATQ DEPLACEMENTS MLOG$_DEPLACEMENTS NO YES NO YES NO YES
                        ATQ DETAILS_STATUTS_COMMANDES MLOG$_DETAILS_STATUTS_COMM NO YES NO YES NO YES
                        ATQ DOMAINES MLOG$_DOMAINES NO YES NO YES NO YES
                        ATQ ENREGISTREMENT_LOGS MLOG$_ENREGISTREMENT_LOGS NO YES NO YES NO YES
                        ATQ ENREGISTREMENT_NOTES MLOG$_ENREGISTREMENT_NOTES NO YES NO YES NO YES
                        ATQ ESPECES MLOG$_ESPECES NO YES NO YES NO YES
                        ATQ EVENEMENTS MLOG$_EVENEMENTS NO YES NO YES NO YES
                        ATQ EVENE_DIFFERES MLOG$_EVENE_DIFFERES NO YES NO YES NO YES
                        ATQ IDENTIFIANTS MLOG$_IDENTIFIANTS NO YES NO YES NO YES
                        ATQ INTERVENANTS MLOG$_INTERVENANTS NO YES NO YES NO YES
                        ATQ INTERVENANTS_CLIENTS MLOG$_INTERVENANTS_CLIENTS NO YES NO YES NO YES
                        ATQ INTERVENANTS_SITES MLOG$_INTERVENANTS_SITES NO YES NO YES NO YES
                        ATQ MAX_EVENE_DIFFERES MLOG$_MAX_EVENE_DIFFERES NO YES NO YES NO YES
                        ATQ MESSAGES MLOG$_MESSAGES NO YES NO YES NO YES
                        ATQ MUNICIPALITES MLOG$_MUNICIPALITES NO YES NO YES NO YES
                        ATQ NEW_ANILOTS MLOG$_NEW_ANILOTS NO YES NO YES NO YES
                        ATQ NEW_EVENEMENTS MLOG$_NEW_EVENEMENTS NO YES NO YES NO YES
                        ATQ NEW_IDENTIFIANTS MLOG$_NEW_IDENTIFIANTS NO YES NO YES NO YES
                        ATQ NEW_SITES MLOG$_NEW_SITES NO YES NO YES NO YES
                        ATQ PAYS MLOG$_PAYS NO YES NO YES NO YES
                        ATQ PRODUCTIONS MLOG$_PRODUCTIONS NO YES NO YES NO YES
                        ATQ PROPRIETES_ANILOTS MLOG$_PROPRIETES_ANILOTS NO YES NO YES NO YES
                        ATQ PROVINCES MLOG$_PROVINCES NO YES NO YES NO YES
                        ATQ SITES_EXPLOITATIONS MLOG$_SITES_EXPLOITATIONS NO YES NO YES NO YES
                        ATQ TYPES_IDENTIFIANTS MLOG$_TYPES_IDENTIFIANTS NO YES NO YES NO YES
                        ATQ UTILISATEURS MLOG$_UTILISATEURS NO YES NO YES NO YES
                        ATQ VALEURS MLOG$_VALEURS NO YES NO YES NO YES
                        ATQ VALEURS_DETAILS MLOG$_VALEURS_DETAILS NO YES NO YES NO YES
                        ATQ VEHICULES MLOG$_VEHICULES NO YES NO YES NO YES

                        36 ligne(s) sélectionnée(s).

                        SQL>

                        I supposed that is the result of the MLOG of the MVIEW associated with my new server. How can I be sure that the MLOG associated with the old one are really deleted ?

                        Thank you
                        • 10. Re: MLOGS not purge after a fast refresh (Materialized view) ...
                          JustinCave
                          Well, why does the DBA at the master site believe that the logs haven't been purged? Assuming the old system's materialized views were unregistered, that should resolve the problem.

                          Are all your materialized views of the simple SELECT * FROM table_name variety? Or are your materialized views joining multiple source tables?

                          Justin
                          • 11. Re: MLOGS not purge after a fast refresh (Materialized view) ...
                            yvesthib
                            Hi Justin,

                            If I query the MLOGS of one of my Materialized vies on the master table site, I have the following result:

                            SQL> select snaptime$$ from mlog$_intervenants@atqprod.world order by 1;

                            SNAPTIME$$
                            -------------------
                            2008-02-25 09:01:17
                            2008-02-25 09:01:17
                            2008-02-25 09:01:17
                            2008-02-25 09:01:17
                            2008-02-25 09:01:17
                            2008-02-25 09:01:17
                            ...
                            2008-02-26 08:01:38
                            2008-02-26 08:01:38
                            2008-02-26 08:01:38
                            2008-02-26 08:01:38
                            2008-02-26 08:01:38
                            2008-02-26 08:01:38
                            2008-02-26 09:01:23
                            ...
                            2008-02-27 09:01:28
                            2008-02-27 09:01:28
                            2008-02-27 09:01:28
                            2008-02-27 09:01:28
                            2008-02-27 11:02:01
                            ...
                            2008-02-27 11:02:01

                            146 ligne(s) sélectionnée(s).

                            SQL>

                            So, the "unregister" of the MLOG on the master table site was made yesterday around 8h00 PM. We're not supposed to found records with a date before yesterday night. It's the same thing for all the MVIEW.

                            So, how can I purge the MLOGs ?

                            Thank's
                            • 12. Re: MLOGS not purge after a fast refresh (Materialized view) ...
                              JustinCave
                              Are all your materialized views of the simple SELECT * FROM table_name variety? Or are your materialized views joining multiple source tables?

                              Have all the materialized views on the new server been refreshed since the old site's materialized views were unregistered?

                              Justin
                              • 13. Re: MLOGS not purge after a fast refresh (Materialized view) ...
                                yvesthib
                                Hi,

                                Sorry, I forgot to answer your questions in my previous reply.

                                So, all of our MV are simple as SELECT * from table_name and, all of them on the new server has been refreshed since the old site's materialized views were unregistered.
                                • 14. Re: MLOGS not purge after a fast refresh (Materialized view) ...
                                  JustinCave
                                  Are all those SNAPTIME$$ values really in the same materialized view log? Do those correspond to refreshes of the materialized views on the new system?

                                  When you look at DBA_REGISTERED_MVIEWS, are you only seeing the materialized views on the new server?

                                  What version of Oracle are you using?

                                  Justin
                                  1 2 Previous Next