4 Replies Latest reply: Feb 12, 2013 6:36 PM by jgarry RSS

    Log Switch/Check Point Happening ..Even after deleting the controlfile.

    836600
      HI Folks,
      Here is the scenario. I moved controlfile of my Oracle database. After that , i am able to query the database, and was even able to do the logswich/checkpoint in the database. Same was visible in alert log.
      I was able to add datafiles also.
      I killed the PMON for the database. Restored the old controlfile, and opened the database. I found strange that the database open with seq no. "544". But the control file was moved when seq no. was only "508".
      Can , any one explain , how it's happening.


      SQL> select name from v$controlfile;*

      NAME*
      --------------------------------------------------------------------------------*
      +/data/app/oracle/product/dbhome/dbs/cntrlTEST.dbf+

      SQL>*
      SQL> !mv /data/app/oracle/product/dbhome/dbs/cntrlTEST.dbf /data/app/oracle/product/dbhome/dbs/cntrlTEST.dbf_bak*

      SQL> alter system switch logfile;*

      System altered.*

      SQL> !ls -ltr /data/app/oracle/product/dbhome/dbs/cntrlTEST.dbf*
      +/data/app/oracle/product/dbhome/dbs/cntrlTEST.dbf: No such file or directory+

      SQL> alter system switch logfile;*

      System altered.*

      SQL>*

      System altered.*

      SQL> alter tablespace T24_TBS add datafile '/data/app/oracle/product/dbhome/dbs/T24_TBS09.dbf' size 100M;*

      Tablespace altered.*



      SQL>*
      Current log# 4 seq# 506 mem# 0: /data/app/oracle/product/dbhome/dbs/log2TEST.d                                                                                        bf_bbkkup*
      Thread 1 advanced to log sequence 507 (LGWR switch)*
      Current log# 3 seq# 507 mem# 0: /data/app/oracle/product/dbhome/dbs/log2TEST.d                                                                                        bf_bkup*
      Mon Feb 11 16:48:08 2013*
      Thread 1 advanced to log sequence 508 (LGWR switch)*
      Current log# 4 seq# 508 mem# 0: /data/app/oracle/product/dbhome/dbs/log2TEST.dbf_bbkkup*
      Mon Feb 11 16:52:18 2013*
      Thread 1 advanced to log sequence 509 (LGWR switch)*
      Current log# 3 seq# 509 mem# 0: /data/app/oracle/product/dbhome/dbs/log2TEST.dbf_bkup*
      Mon Feb 11 16:52:43 2013*
      Thread 1 advanced to log sequence 510 (LGWR switch)*
      Current log# 4 seq# 510 mem# 0: /data/app/oracle/product/dbhome/dbs/log2TEST.dbf_bbkkup*
      Mon Feb 11 16:54:10 2013*
      +/Thread 1 advanced to log sequence 542 (LGWR switch)+

      +++++++++

      Current log# 4 seq# 542 mem# 0: /data/app/oracle/product/dbhome/dbs/log2TEST.dbf_bbkkup*
      Mon Feb 11 17:28:36 2013*
      alter tablespace T24_TBS add datafile '/data/app/oracle/product/dbhome/dbs/T24_TBS09.dbf' size 100M*
      Completed: alter tablespace T24_TBS add datafile '/data/app/oracle/product/dbhome/dbs/T24_TBS09.dbf' size 100M*
      Mon Feb 11 17:32:23 2013*
        • 1. Re: Log Switch/Check Point Happening ..Even after deleting the controlfile.
          EdStevens
          user8524537 wrote:
          HI Folks,
          Here is the scenario. I moved controlfile of my Oracle database. After that , i am able to query the database, and was even able to do the logswich/checkpoint in the database. Same was visible in alert log.
          I was able to add datafiles also.
          I killed the PMON for the database. Restored the old controlfile, and opened the database. I found strange that the database open with seq no. "544". But the control file was moved when seq no. was only "508".
          Can , any one explain , how it's happening.


          SQL> select name from v$controlfile;*

          NAME*
          --------------------------------------------------------------------------------*
          +/data/app/oracle/product/dbhome/dbs/cntrlTEST.dbf+

          SQL>*
          SQL> !mv /data/app/oracle/product/dbhome/dbs/cntrlTEST.dbf /data/app/oracle/product/dbhome/dbs/cntrlTEST.dbf_bak*

          SQL> alter system switch logfile;*

          System altered.*

          SQL> !ls -ltr /data/app/oracle/product/dbhome/dbs/cntrlTEST.dbf*
          +/data/app/oracle/product/dbhome/dbs/cntrlTEST.dbf: No such file or directory+

          SQL> alter system switch logfile;*

          System altered.*

          SQL>*

          System altered.*

          SQL> alter tablespace T24_TBS add datafile '/data/app/oracle/product/dbhome/dbs/T24_TBS09.dbf' size 100M;*

          Tablespace altered.*



          SQL>*
          Current log# 4 seq# 506 mem# 0: /data/app/oracle/product/dbhome/dbs/log2TEST.d                                                                                        bf_bbkkup*
          Thread 1 advanced to log sequence 507 (LGWR switch)*
          Current log# 3 seq# 507 mem# 0: /data/app/oracle/product/dbhome/dbs/log2TEST.d                                                                                        bf_bkup*
          Mon Feb 11 16:48:08 2013*
          Thread 1 advanced to log sequence 508 (LGWR switch)*
          Current log# 4 seq# 508 mem# 0: /data/app/oracle/product/dbhome/dbs/log2TEST.dbf_bbkkup*
          Mon Feb 11 16:52:18 2013*
          Thread 1 advanced to log sequence 509 (LGWR switch)*
          Current log# 3 seq# 509 mem# 0: /data/app/oracle/product/dbhome/dbs/log2TEST.dbf_bkup*
          Mon Feb 11 16:52:43 2013*
          Thread 1 advanced to log sequence 510 (LGWR switch)*
          Current log# 4 seq# 510 mem# 0: /data/app/oracle/product/dbhome/dbs/log2TEST.dbf_bbkkup*
          Mon Feb 11 16:54:10 2013*
          +/Thread 1 advanced to log sequence 542 (LGWR switch)+

          +++++++++

          Current log# 4 seq# 542 mem# 0: /data/app/oracle/product/dbhome/dbs/log2TEST.dbf_bbkkup*
          Mon Feb 11 17:28:36 2013*
          alter tablespace T24_TBS add datafile '/data/app/oracle/product/dbhome/dbs/T24_TBS09.dbf' size 100M*
          Completed: alter tablespace T24_TBS add datafile '/data/app/oracle/product/dbhome/dbs/T24_TBS09.dbf' size 100M*
          Mon Feb 11 17:32:23 2013*
          Most likely, if you moved the control file while the database was up, oracle's access to the control file was by inode, which didn't change. As a result, even though you moved the file, the OS kept it open and the internal connection was still good, so oracle was able to continue to work with it.
          • 2. Re: Log Switch/Check Point Happening ..Even after deleting the controlfile.
            836600
            Thanks For the reply.
            Can you please explain where this inode information is kept, at the server level or at the client session level? Because everything runs smooth until i am runnig the command from the same session. The moment i am disconnecting that session, and login again to the database, it's showing the error of control file missing.
            • 3. Re: Log Switch/Check Point Happening ..Even after deleting the controlfile.
              EdStevens
              user8524537 wrote:
              Thanks For the reply.
              Can you please explain where this inode information is kept, at the server level or at the client session level? Because everything runs smooth until i am runnig the command from the same session. The moment i am disconnecting that session, and login again to the database, it's showing the error of control file missing.
              The inode is an OS issue. It is how nix really* identifies a file. you can see the same behaivour in a much less destructive manner by renaming the listener.log. You can rename it, but the listener is still holding it open and writing to it until such time as the listener is stopped. You can also see the same behavior if you delete a file that is open to some process, checking the disk space before and after. The OS can't really drop the file and release its disk space until the process that has the file open releases it.

              You can find more about inodes [url http://lmgtfy.com/?q=what+is+linux+inode]here
              • 4. Re: Log Switch/Check Point Happening ..Even after deleting the controlfile.
                jgarry
                I'm wondering if something a little more subtle is happening here. I think if the OP tried to recover again across this gap he wouldn't be completely able to. Just like all the archive log gap issues you run into when the log shipping is down for a while in a standby configuration.

                Perhaps we should be looking at the views that show what the controlfile thinks about archives. Are 511-541 there or not? v$log_history may be of interest.

                On the other hand, maybe some special feature for RAC or something allows Oracle to figure out no transactions happened in the missing logs.