Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2008
    Posts
    4

    Unanswered: Is changes to Oracle sequence object logged into REDO log?

    Hi,

    If we do

    select supplier_seq.nextval from dual

    Does the changes in the next value of supplier_seq logged into REDO log?

    Thanks

  2. #2
    Join Date
    Dec 2007
    Posts
    253
    No. It is not

  3. #3
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    It does seems so. This was done on a test database with relatively no activity, I would not encourage anyone to do this unless you don't care about your data.
    Code:
    $ cd /oracle/app_ora/oracle/admin/x/arch
    $ sqlplus "/ as sysdba"
    
    SQL*Plus: Release 8.1.7.0.0 - Production on Thu Feb 21 15:09:11 2008
    
    (c) Copyright 2000 Oracle Corporation.  All rights reserved.
    
    
    Connected to:
    Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
    With the Partitioning option
    JServer Release 8.1.7.0.0 - Production
    
    SQL>
    SQL> select * from v$log;
    
        GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
    ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
             1          1         22   10485760          1 YES INACTIVE                 33447 21-FEB-08
             2          1         23   10485760          1 YES INACTIVE                 34794 21-FEB-08
             3          1         24   10485760          1 NO  CURRENT                  36118 21-FEB-08
    
    SQL> select * from v$logfile;
    
        GROUP# STATUS  MEMBER
    ---------- ------- ------------------------------------------------------------
             1         /oracle/app_ora/oracle/oradata/x/redo01.log
             2         /oracle/app_ora/oracle/oradata/x/redo02.log
             3         /oracle/app_ora/oracle/oradata/x/redo03.log
    
    SQL> create sequence x
      2  start with 1
      3  increment by 1
      4  nocycle
      5  cache 20;
    
    Sequence created.
    
    SQL> alter system switch logfile;
    
    System altered.
    
    SQL> !ls -lt
    total 2810
    -rw-r-----   1 oracle   system      2048 Feb 21 15:14 arch_1_27.arc
    -rw-r-----   1 oracle   system      2048 Feb 21 15:14 arch_1_26.arc
    -rw-r-----   1 oracle   system      2048 Feb 21 15:13 arch_1_25.arc
    -rw-r-----   1 oracle   system   2854912 Feb 21 15:13 arch_1_24.arc
    Then I look a HOT backup of the whole database.
    And then..
    Code:
    SQL> alter system switch logfile;
    
    System altered.
    
    SQL> !ls -lt
    total 2854
    -rw-r-----   1 oracle   system     56320 Feb 21 15:18 arch_1_28.arc
    -rw-r-----   1 oracle   system      2048 Feb 21 15:14 arch_1_27.arc
    -rw-r-----   1 oracle   system      2048 Feb 21 15:14 arch_1_26.arc
    -rw-r-----   1 oracle   system      2048 Feb 21 15:13 arch_1_25.arc
    -rw-r-----   1 oracle   system   2854912 Feb 21 15:13 arch_1_24.arc
    
    
    SQL> select x.nextval from all_objects where rownum <= 50;
    
       NEXTVAL
    ----------
             1
             2
             3
             4
             5
             6
             7
             8
             9
            10
            11
            12
            13
            14
            15
            16
            17
            18
            19
            20
            21
            22
            23
            24
            25
            26
            27
            28
            29
            30
            31
            32
            33
            34
            35
            36
            37
            38
            39
            40
            41
            42
            43
            44
            45
            46
            47
            48
            49
            50
    
    50 rows selected.
    
    SQL> select * from v$log;
    
        GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
    ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
             1          1         28   10485760          1 YES ACTIVE                   36416 21-FEB-08
             2          1         29   10485760          1 NO  CURRENT                  36437 21-FEB-08
             3          1         27   10485760          1 YES INACTIVE                 36415 21-FEB-08
    
    SQL> select * from v$logfile;
    
        GROUP# STATUS  MEMBER
    ---------- ------- ------------------------------------------------------------
             1         /oracle/app_ora/oracle/oradata/x/redo01.log
             2         /oracle/app_ora/oracle/oradata/x/redo02.log
             3         /oracle/app_ora/oracle/oradata/x/redo03.log
    
    
    SQL> shutdown abort
    ORACLE instance shut down.
    SQL>
    SQL> !rm /oracle/app_ora/oracle/oradata/x/redo01.log
    
    SQL> !rm /oracle/app_ora/oracle/oradata/x/redo02.log
    
    SQL> !rm /oracle/app_ora/oracle/oradata/x/redo03.log
    
    SQL>
    If I try to startup the database, I would get
    Code:
    SQL> startup
    ORACLE instance started.
    
    Total System Global Area  422122468 bytes
    Fixed Size                   103396 bytes
    Variable Size             115482624 bytes
    Database Buffers          306356224 bytes
    Redo Buffers                 180224 bytes
    Database mounted.
    ORA-00313: open failed for members of log group 1 of thread 1
    ORA-00312: online log 1 thread 1: '/oracle/app_ora/oracle/oradata/x/redo01.log'
    ORA-27037: unable to obtain file status
    Compaq Tru64 UNIX Error: 2: No such file or directory
    Additional information: 3
    I then restored my HOT backup taken previously after I created my SEQUENCE but BEFORE I selected from it and then..
    Code:
    SQL> startup mount
    ORACLE instance started.
    
    Total System Global Area  422122468 bytes
    Fixed Size                   103396 bytes
    Variable Size             115482624 bytes
    Database Buffers          306356224 bytes
    Redo Buffers                 180224 bytes
    Database mounted.
    SQL>
    SQL> recover database until cancel using backup controlfile;
    ORA-00279: change 36417 generated at 02/21/2008 15:14:46 needed for thread 1
    ORA-00289: suggestion : /oracle/app_ora/oracle/admin/x/arch/arch_1_28.arc
    ORA-00280: change 36417 for thread 1 is in sequence #28
    
    
    Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
    
    ORA-00279: change 36437 generated at 02/21/2008 15:18:17 needed for thread 1
    ORA-00289: suggestion : /oracle/app_ora/oracle/admin/x/arch/arch_1_29.arc
    ORA-00280: change 36437 for thread 1 is in sequence #29
    ORA-00278: log file '/oracle/app_ora/oracle/admin/x/arch/arch_1_28.arc' no longer needed for this recovery
    
    
    Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
    cancel
    Media recovery cancelled.
    SQL>
    SQL> alter database open resetlogs;
    
    Database altered.
    
    SQL> select * from v$logfile;
    
        GROUP# STATUS  MEMBER
    ---------- ------- ------------------------------------------------------------
             1         /oracle/app_ora/oracle/oradata/x/redo01.log
             2         /oracle/app_ora/oracle/oradata/x/redo02.log
             3         /oracle/app_ora/oracle/oradata/x/redo03.log
    
    SQL> select * from v$log;
    
        GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
    ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
             1          1          0   10485760          1 YES UNUSED                       0
             2          1          1   10485760          1 NO  CURRENT                  36438 21-FEB-08
             3          1          0   10485760          1 YES UNUSED                       0
    
    SQL> select x.nextval from all_objects where rownum <= 50;
    
       NEXTVAL
    ----------
             1
             2
             3
             4
             5
             6
             7
             8
             9
            10
            11
            12
            13
            14
            15
            16
            17
            18
            19
            20
            21
            22
            23
            24
            25
            26
            27
            28
            29
            30
            31
            32
            33
            34
            35
            36
            37
            38
            39
            40
            41
            42
            43
            44
            45
            46
            47
            48
            49
            50
    
    50 rows selected.
    
    So, after I restored my backup, I queried again from the sequence and it started all over again. This leaves me to the conclusion that the DICTIONARY UPDATE when I queried from my sequence got lost in what was my CURRENT online redo log. I did again a shutdown abort and startup to mimick what would happen if I didn't loose my current online log.
    Code:
    SQL> shutdown abort
    ORACLE instance shut down.
    SQL>
    SQL> startup
    ORACLE instance started.
    
    Total System Global Area  422122468 bytes
    Fixed Size                   103396 bytes
    Variable Size             115482624 bytes
    Database Buffers          306356224 bytes
    Redo Buffers                 180224 bytes
    Database mounted.
    Database opened.
    SQL>
    SQL> select x.nextval from all_objects
      2   where rownum <= 50;
    
       NEXTVAL
    ----------
            61
            62
            63
            64
            65
            66
            67
            68
            69
            70
            71
            72
            73
            74
            75
            76
            77
            78
            79
            80
            81
            82
            83
            84
            85
            86
            87
            88
            89
            90
            91
            92
            93
            94
            95
            96
            97
            98
            99
           100
           101
           102
           103
           104
           105
           106
           107
           108
           109
           110
    
    50 rows selected.
    
    SQL>

  4. #4
    Join Date
    Feb 2008
    Posts
    4
    Hi Martinez,

    Thanks. My investigation also show that it will be logged into REDO log once it hits the CACHE's value.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •