DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Can snapshot log be migrated without rebuilding snapshots?

  1. #1
    Join Date
    Jun 2000
    Posts
    55
    Oracle 7.3.4
    replication environment

    We are trying to rebuild the tablespaces. Can we migrate the snapshot logs to another tablespace with rebuilding the snapshots on the snapshot site? Complete refresh is not a good option because some tables have 8 million rows.

    But when I was trying to export the snapshot log table, I got error like this:

    EXP-00011: scott.MLOG does not exist


    The command was

    exp system/manager file=dummy.dmp log=dummy.log tables=scott.MLOG$_DUMMY

  2. #2
    Join Date
    Sep 2001
    Location
    Fort Smith
    Posts
    184


    Bookmark Fixed font Go to End

    Doc ID: Note:119228.1
    Subject: MOVING SNAPSHOTS OR MASTER TABLES FROM ONE TABLESPACE TO ANOTHER
    Type: BULLETIN
    Status: PUBLISHED
    Content Type: TEXT/PLAIN
    Creation Date: 07-SEP-2000
    Last Revision Date: 09-NOV-2000


    PURPOSE
    -------

    The purpose of this article is to allow you to be able to move a
    SNAPSHOT or the snapshot's MASTER TABLE from one tablespace to
    another while preserving the ability to continue FAST REFRESHes
    AFTER the move.


    MOVING SNAPSHOTS OR MASTER TABLES FROM ONE TABLESPACE TO ANOTHER
    ================================================================

    I. MOVING SNAPSHOTS FROM ONE TABLESPACE TO ANOTHER

    To move snapshots from one tablespace to another, you only have to
    follow the same instructions as moving any other table in the database
    using the export/import utility.

    But before you begin, you must know which table to move.
    For Oracle Release 7.3.X - 8.0.X, the snapshot's base table that you will
    can relocate is called 'SNAP$_snapshotname'. Beginning with Oracle
    Release 8.1.X, the snapshot's base table name is the snapshotname you gave
    it on creation. See [NOTE:73318.1] READ ONLY SNAPSHOTS OVERVIEW (ORACLE8)
    for further explanation of this.


    or this example, our snapshot on Oracle Release 7.3.4 is 'SNAP_T1'.
    The snapshot's base table becomes 'SNAP$_SNAP_T1'. If this were an
    Oracle8i release, the snapshot and snapshot base table will both be
    called 'SNAP_T1'.

    SQL> connect scott/tiger
    Connected

    SQL> select table_name, tablespace_name from user_tables where
    2 table_name = 'SNAP$_SNAP_T1';

    TABLE_NAME TABLESPACE_NAME
    ------------------------------ ------------------------------
    SNAP$_SNAP_T1 TEST1


    1) export the table

    r2734> exp scott/tiger file=expdat.dmp

    Export: Release 7.3.4.5.0 - Production on Tue Sep 5 09:45:44 2000

    Copyright (c) Oracle Corporation 1979, 1996. All rights reserved.

    Connected to: Oracle7 Server Release 7.3.4.5.0 - Production
    With the distributed, replication and parallel query options
    PL/SQL Release 2.3.4.5.0 - Production
    Enter array fetch buffer size: 4096 >

    (2)U(sers), or (3)T(ables): (2)U > 3

    Export table data (yes/no): yes >

    Compress extents (yes/no): yes >

    Export done in US7ASCII character set

    About to export specified tables via Conventional Path ...
    Table to be exported: (RETURN to quit) > SNAP$_SNAP_T1

    . . exporting table SNAP$_SNAP_T1 4 rows exported


    2) Drop or rename the table. Renaming the table will guarantee a backup
    of the table you're moving. This however, will require more space since
    you will have 2 copies of the table after import.

    SQL> connect scott/tiger
    Connected.

    SQL> drop table snap$_snap_t1;

    Table dropped.

    3) Import the table with indexfile. Using the INDEXFILE option will give
    us the create table and create index statements we will need to relocate the
    table and/or index to a different tablespace.

    r2734> more impparfile
    file=expdat.dmp
    indexfile=indfile.txt
    tables=(snap$_snap_t1)

    r2734> imp scott/tiger parfile=impparfile

    Import: Release 7.3.4.5.0 - Production on Tue Sep 5 09:52:03 2000

    Copyright (c) Oracle Corporation 1979, 1996. All rights reserved.


    Connected to: Oracle7 Server Release 7.3.4.5.0 - Production
    With the distributed, replication and parallel query options
    PL/SQL Release 2.3.4.5.0 - Production

    Export file created by EXPORT:V07.03.04 via conventional path
    . . skipping table "SNAP$_SNAP_T1"

    Import terminated successfully without warnings.


    4) The INDEXFILE will look like the following:

    REM CREATE TABLE "SCOTT"."SNAP$_SNAP_T1" ("COL1" NUMBER(38, 0) NOT NULL,
    REM "COL2" VARCHAR2(10), "M_ROW$$" VARCHAR2(18) DEFAULT
    REM '00000000.0000.0000' ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
    REM STORAGE(INITIAL 32768 NEXT 24576 MINEXTENTS 1 MAXEXTENTS 505
    REM PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "TEST1" ;
    REM ... 4 rows
    CONNECT SCOTT;
    CREATE UNIQUE INDEX "SCOTT"."I_SNAP$_SNAP_T1" ON "SNAP$_SNAP_T1"
    ("M_ROW$$" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (INITIAL 16384
    NEXT 16384 MINEXTENTS 1 MAXEXTENTS 505 PCTINCREASE 50 FREELISTS 1)
    TABLESPACE "TEST1" ;


    In this example, I only want to change the tablespace for the table not
    the index so I will change only the following:

    CREATE TABLE "SCOTT"."SNAP$_SNAP_T1" ("COL1" NUMBER(38, 0) NOT NULL,
    "COL2" VARCHAR2(10), "M_ROW$$" VARCHAR2(18) DEFAULT
    '00000000.0000.0000' ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
    STORAGE(INITIAL 32768 NEXT 24576 MINEXTENTS 1 MAXEXTENTS 505
    PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "TEST2" ;

    Where tablespace TEST1 is now TEST2

    5) Pre-create the table before IMPORT.

    SQL> connect scott/tiger
    Connected.

    SQL> @indfile.txt

    Table created.

    SQL> select table_name, tablespace_name from user_tables where
    table_name='SNAP$_SNAP_T1';


    TABLE_NAME TABLESPACE_NAME
    ------------------------------ ------------------------------
    SNAP$_SNAP_T1 TEST2


    6) Import the rows and the indexes.

    r2734> more impparfile
    file=expdat.dmp
    tables=(snap$_snap_t1)
    ignore=y

    [stsun6]/export/home/rserna/r2734> imp scott/tiger parfile=impparfile

    Import: Release 7.3.4.5.0 - Production on Tue Sep 5 09:56:46 2000

    Copyright (c) Oracle Corporation 1979, 1996. All rights reserved.


    Connected to: Oracle7 Server Release 7.3.4.5.0 - Production
    With the distributed, replication and parallel query options
    PL/SQL Release 2.3.4.5.0 - Production

    Export file created by EXPORT:V07.03.04 via conventional path
    . importing SCOTT's objects into SCOTT
    . . importing table "SNAP$_SNAP_T1" 4 rows imported
    Import terminated successfully without warnings.


    SQL> connect scott/tiger
    Connected.

    SQL> select object_name, object_type, status from user_objects
    2 where object_name like '%SNAP%';

    OBJECT_NAME OBJECT_TYPE STATUS
    --------------- ------------- -------
    I_SNAP$_SNAP_T1 INDEX VALID
    MVIEW$_SNAP_T1 VIEW VALID
    SNAP$_SNAP_T1 TABLE VALID
    SNAP_T1 VIEW INVALID


    The view SNAP_T1 is currently INVALID because of the operations performed
    on its underlying table SNAP$_SNAP_T1. This will be validated after the
    first refresh.

    7. Verify the fast refresh still works.

    MASTER SITE:
    ~~~~~~~~~~~~
    SQL> connect scott/tiger
    Connected.

    SQL> insert into t1 values (s1.nextval, 'test'||s1.nextval);

    1 row created.

    SQL> commit;

    Commit complete.

    SQL> select * from t1;

    COL1 COL2
    ---------- ----------
    1 test1
    2 test2
    3 test3
    4 test4
    5 test5


    SNAPSHOT SITE:
    ~~~~~~~~~~~~~~
    SQL> connect scott/tiger
    Connected.

    SQL> exec dbms_snapshot.refresh('snap_t1','f');

    PL/SQL procedure successfully completed.

    SQL> select * from snap_t1;

    COL1 COL2
    ---------- ----------
    1 test1
    2 test2
    3 test3
    4 test4
    5 test5


    SQL> select object_name, object_type, status from user_objects
    2 where object_name like '%SNAP%';

    OBJECT_NAME OBJECT_TYPE STATUS
    --------------- ------------- -------
    I_SNAP$_SNAP_T1 INDEX VALID
    MVIEW$_SNAP_T1 VIEW VALID
    SNAP$_SNAP_T1 TABLE VALID
    SNAP_T1 VIEW VALID





    II. MOVING THE MASTER TABLE OF A SNAPSHOT FROM ONE TABLESPACE TO ANOTHER
    ========================================================================


    Whether or not you can continue a fast refresh after moving the Master Table
    of a snapshot depends on what Oracle Release you are on and whether or not
    you are refreshing by rowid or primary key. You may not be able to continue
    to FAST REFRESH your snapshot after the move.

    Restrictions:
    ~~~~~~~~~~~~~

    On Oracle Release 7.X, there are no utilities to allow you to move the
    Master Table of a snapshot AND still maintain the ability to FAST REFRESH
    your snapshot. You can move the Master Table from one tablespace to another
    just like any ordinary table, but you will have to perform a COMPLETE REFRESH
    of the snapshot after the master table's move.

    On Oracle Release 8.X, you can move the Master Table from one tablespace to
    another just like any ordinary table IF your FAST REFRESH is 'WITH ROWID'.
    You will then have to perform a COMPLETE REFRESH of the snapshot after the
    master table's move.

    Refreshes WITH ROWID will always need a complete refresh of a snapshot because
    once you drop and recreate the master table onto another tablespace, the rowids
    needed for fast refresh will change.

    Oracle Release 8.X does provide utilities to allow you to move the master
    tables of updatable snapshots and read-only snapshots that are being refreshed
    'WITH PRIMARY KEY'. However, for read-only snapshots, you will have to
    temporarily setup advanced replication, because of [BUG:1246941] ORA-26500
    while executing dbms_snapshot.begin_table_reorganization.


    Example:
    ~~~~~~~~

    he following is an example of relocating a MASTER table on Oracle
    release 8.1.5. The snapshot is a read only snapshot on Oracle
    release 8.1.5 and has been setup to refresh WITH PRIMARY KEY.

    In this example, we want to relocate table REORG1 from tablespace TEST1
    to tablespace TEST2. There is currently a snapshot log on REORG1 that is
    keeping track of the primary keys.
    On the MASTER site:


    SQL> connect scott/tiger
    Connected.

    SQL> SELECT TABLE_NAME, TABLESPACE_NAME FROM USER_TABLES
    2 WHERE TABLE_NAME = 'REORG1';

    TABLE_NAME TABLESPACE_NAME
    ------------------------------ ------------------------------
    REORG1 TEST1



    1. Execute a fast refresh on all snapshots refreshing against the Master
    Table to clear the snapshot log and ensure that there are no new transactions
    being performed on the master table.


    2. Execute DBMS_SNAPSHOT.BEGIN_TABLE_REORGANIZATION

    SQL> connect scott2/tiger
    Connected.

    SQL> exec dbms_snapshot.begin_table_reorganization('SCOTT2','REORG1');
    BEGIN dbms_snapshot.begin_table_reorganization('SCOTT2','REORG1'); END;

    *
    ERROR at line 1:
    ORA-26500: error on caching "SCOTT2"."REORG1"
    ORA-06512: at "SYS.DBMS_SNAP_INTERNAL", line 249
    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 689
    ORA-06512: at line 1


    !Note! If you receive the errors above, execute the following steps.
    Because this is a read-only snapshot, we will have to temporarily setup
    advanced replication on the master table because of [BUG:1246941].
    Otherwise, proceed to Step #3.

    SQL> connect repadmin/repadmin
    Connected.

    The following commands will allow us to use
    'begin_table_reorganization'.

    SQL> EXEC DBMS_REPCAT.CREATE_MASTER_REPGROUP('SCOTT2');

    PL/SQL procedure successfully completed.

    SQL> EXEC DBMS_REPCAT.CREATE_MASTER_REPOBJECT('SCOTT2','REORG1','TABLE');

    PL/SQL procedure successfully completed.

    SQL> EXEC DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT('SCOTT2','REORG1','TABLE');

    PL/SQL procedure successfully completed.

    SQL> EXEC DBMS_SNAPSHOT.BEGIN_TABLE_REORGANIZATION('SCOTT2','REORG1');

    PL/SQL procedure successfully completed.


    3. Export the table.

    r1815> exp scott2/tiger file=expdat.dmp tables=reorg1

    Export: Release 8.1.5.0.0 - Production on Wed Sep 6 21:37:16 2000

    (c) Copyright 1999 Oracle Corporation. All rights reserved.


    Connected to: Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
    With the Partitioning and Java options
    PL/SQL Release 8.1.5.0.0 - Production
    Export done in US7ASCII character set and WE8ISO8859P1 NCHAR character set
    server uses WE8ISO8859P1 character set (possible charset conversion)

    About to export specified tables via Conventional Path ...
    . . exporting table REORG1 3 rows exported
    Export terminated successfully without warnings.


    4. Rename the table.

    SQL> connect scott2/tiger
    Connected.

    SQL> rename reorg1 to t_reorg1;

    Table renamed.

    SQL> alter table t_reorg1 drop primary key;

    Table altered.


    5. Import the table using indexfile to get the create table statement.

    stsun6]/export/home/rserna/r1815> more impparfile
    file=expdat.dmp
    indexfile=indfile.txt
    tables=(reorg1)

    r1815> imp scott2/tiger parfile=impparfile

    Import: Release 8.1.5.0.0 - Production on Wed Sep 6 11:30:02 2000

    (c) Copyright 1999 Oracle Corporation. All rights reserved.


    Connected to: Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
    With the Partitioning and Java options
    PL/SQL Release 8.1.5.0.0 - Production

    Export file created by EXPORT:V08.01.05 via conventional path
    import done in US7ASCII character set and WE8ISO8859P1 NCHAR character set
    import server uses WE8ISO8859P1 character set (possible charset conversion)
    . . skipping table "REORG1"

    Import terminated successfully without warnings.


    6. Edit the indexfile: INDFILE.TXT to change the tablespace from TEST1 to
    TEST2 and precreate the table.

    CREATE TABLE "SCOTT2"."REORG1" ("COL1" NUMBER(*,0), "COL2"
    VARCHAR2(10)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
    STORAGE(INITIAL 51200 NEXT 51200 MINEXTENTS 1 MAXEXTENTS 121
    PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
    TABLESPACE "TEST2" ;

    SQL> connect scott2/tiger
    Connected.

    SQL> @indfile.txt

    Table created.


    7. Run the import again to include all the rows

    r1815> more impparfile
    file=expdat.dmp
    tables=(reorg1)
    ignore=y

    [stsun6]/export/home/rserna/r1815> imp scott2/tiger parfile=impparfile

    Import: Release 8.1.5.0.0 - Production on Wed Sep 6 11:35:47 2000

    (c) Copyright 1999 Oracle Corporation. All rights reserved.


    Connected to: Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
    With the Partitioning and Java options
    PL/SQL Release 8.1.5.0.0 - Production

    Export file created by EXPORT:V08.01.05 via conventional path
    import done in US7ASCII character set and WE8ISO8859P1 NCHAR character set
    import server uses WE8ISO8859P1 character set (possible charset conversion)
    . importing SCOTT2's objects into SCOTT2
    . . importing table "REORG1" 3 rows imported
    About to enable constraints...
    Import terminated successfully without warnings.


    8. Execute END_TABLE_REORGANIZATION

    SQL> connect repadmin/repadmin
    Connected.
    SQL> exec dbms_snapshot.end_table_reorganization('SCOTT2','REORG1');

    PL/SQL procedure successfully completed.

    !Note! If you had to setup advanced replication, you can now drop all
    objects we had to temporarily create.
    Otherwise, proceed to Step #9.

    SQL> exec dbms_repcat.drop_master_repobject('SCOTT2','REORG1','TABLE',FALSE);

    PL/SQL procedure successfully completed.

    SQL> EXEC DBMS_REPCAT.DROP_MASTER_REPGROUP('SCOTT2',FALSE,FALSE);

    PL/SQL procedure successfully completed.


    9. Test your fast refresh and verify all is okay. If everything is fine,
    you can drop the temporary table you created.


    MASTER SITE:
    ~~~~~~~~~~~~

    SQL> connect scott2/tiger;
    Connected.

    SQL> SELECT TABLE_NAME, TABLESPACE_NAME FROM USER_TABLES
    2 WHERE TABLE_NAME = 'REORG1';

    TABLE_NAME TABLESPACE_NAME
    ------------------------------ ------------------------------
    REORG1 TEST2

    SQL> select * From reorg1;

    COL1 COL2
    ---------- ----------
    1 TEST1
    2 TEST2
    3 TEST3

    SQL> insert into reorg1 values (S1.NEXTVAL,'TEST'||S1.NEXTVAL);

    1 row created.

    SQL> COMMIT;

    Commit complete.


    SNAPSHOT SITE:
    ~~~~~~~~~~~~~~
    SQL> connect scott2/tiger
    connected.

    SQL> exec dbms_snapshot.refresh('snap_reorg1','f');

    PL/SQL procedure successfully completed.

    SQL> select * from snap_reorg1;

    COL1 COL2
    ---------- ----------
    1 TEST1
    2 TEST2
    3 TEST3
    4 TEST4


    MASTER SITE:
    ~~~~~~~~~~~~

    SQL> connect scott2/tiger
    Connected

    SQL> drop table t_reorg1;

    Table dropped.
    .
    sonofsita
    http://www.ordba.net

Posting Permissions

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


Click Here to Expand Forum to Full Width