Can snapshot log be migrated without rebuilding snapshots?

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

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.
.