7 Replies Latest reply: Jul 6, 2014 9:38 PM by MariaKarpa(MK) RSS

    Check if Zero Transaction Updates

      Hi all,

       

      11g

      aix 6

       

      We are migrating our current DB with PSU OracleHome patch (11.2.0.3.8) to another server with latest PSU OracleHome patch (11.2.0.3.10). Since the OS is just the same, I used poor man's dataguard(manual recovery) while the primary db is still actively it production. I am now currenly applying archivelogs manually from time to time. The final switchover will be at midnight today 12:00AM in which we will shutdown the app connecting the primary db and stop listener to be sure no more updates will be done on it. Then I will perform > ALTER SYSTEM SWITCH LOG FILE ; (4x) and copy the last file archivelogs to the manual standby database. (After which I will run catbundle.sql  for psu 11.2.0.3.10)

       

      My question is, how do I know if the last archivelog has no more updates on it? Just to be sure that there is no more activity on the primary due to "hidden" local server activity or perhaps longops pending running transactions? I just want to be sure that all changes has been applied since I am using manual recovery.

       

       

      Thanks all,

      mk

        • 1. Re: Check if Zero Transaction Updates
          yasinyazici

          hi

           

          You can use logminer

           

          yasin.

          • 2. Re: Check if Zero Transaction Updates

            How?

            Is there no simple select from v$ views that can show it?

            • 3. Re: Check if Zero Transaction Updates

              Can I shutdown the primary database and open it in restrict mode then > alter switch logfile to be sure it is clear?

               

              Or can I mount it and not open , then alter switch logfile?

               

              Thanks

              • 4. Re: Check if Zero Transaction Updates

                Hi all,

                 

                I can not believe this

                 

                After I shutdown  the app and listener , then a run > alter system switch logfile, there is no more archive logs generated

                Is this possible? AFIAK even if there is no updates archive logs will always switch.

                 

                Thanks,

                • 5. Re: Check if Zero Transaction Updates

                  I tried to push thru the swithover and make the standby as PROD , and It was a disaster!!! The databases are not in sync. We have to revert back to old primary. Making precious downtime go to waste.

                   

                  I can not understand why the switch log did not work  at the end?

                   

                  Now I know the reason, but the damage has been done. This is because at 12am date changed  making the archive log swith to another folder date.

                  I was only monitoring the current folder only using ls -lrt, and keep on ftp and apply logs using the current folder. I forgot that it had switch to the new folder date. What a sad way for me to learn my lessons

                  sigh!

                  • 6. Re: Check if Zero Transaction Updates
                    yasinyazici

                    Hi

                     

                    you can follow below seps to see content of arcivelog.

                     

                    1) We set utl_file_dir

                    SQL> alter system set utl_file_dir=’/tmp’ scope=spfile;
                    System altered.

                    2) We extract dictionary info to flat file

                    exec  dbms_logmnr_d.build(‘flatobject.ora’,’/tmp’, dbms_logmnr_d.store_in_flat_file);

                    3) We add archivelog which we wantto analyze

                    exec  dbms_logmnr.add_logfile(logfilename => ‘/oracle/fast_recovery_area/yayadb/archivelog/2014_06_18o1_mf_1_301_9t2mdyno_.arc’,options => dbms_logmnr.new);

                    4) We start logminer as following.

                    exec  dbms_logmnr.start_logmnr(dictfilename => ‘/tmp/flatobject.ora’);

                    5) We can use following query to see contents of archivelog.

                    SQL> SELECT username AS USR, (XIDUSN || ‘.’ || XIDSLT || ‘.’ ||  XIDSQN) AS XID,SQL_REDO,SQL_UNDO FROM V$LOGMNR_CONTENTS;

                    6)  And finally we stop logminer.

                    execute dbms_logmnr.end_logmnr();

                     

                    Regards

                    Yasin

                    • 7. Re: Check if Zero Transaction Updates

                      Thank you so much for the wonderful notes.

                       

                      By the way our app has a checker table with 1 single row, which work like an SCN.

                      I just SELECT CHECKER_VAL FROM CHECKER_TAB; from standby db.

                      If the value is the same as the PRIMARY then I am assured that the apps tables are in sync.

                       

                      How do I run this command at the standby or logminer?

                       

                       

                      Thanks,