Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: snapshot

RE: snapshot

From: Linda Hagedorn <Linda_at_pets.com>
Date: Tue, 5 Sep 2000 17:13:33 -0700
Message-Id: <10610.116260@fatcity.com>


This is for master/client snapshots, read only on the client.

  1. Create a snapshotlog tablespace on the master.
  2. Create snapshot logs, one per table on the master.
  3. Create a dblink on the client.
  4. Create a snapshot tablespace on the client.
  5. Create the snapshots on the client.
  6. I use cron to refresh the snapshots because it is so easy.

BE SURE TO WORK FROM THE MASTER SIDE FIRST! Otherwise, you'll be reading the Metalink documents about runaway space problems in the snapshotlogs....

Here are the generators, etc. references are to the numbers above.

1/2. Use a unix file to hold the list of tables to generate the snapshot logs. snaptab in this case, on the master:

qabv2:/opt/oracle/app/oracle/product/8.0.5/scripts$ more snaptab DC_LOOKUP
BV_USER
BILLING_ADDRESSES

BV_ALERT_SPEC
BV_DESTINATION_TABLE
BV_EXT_ADDITEM_FAILURES
BV_EXT_BO_REL
BV_EXT_GIFT

Snapshot log generator - run from unix, snaploggen.ksh > snaploggen.sql. This will generate the snapshot log tablespace, and the snapshot logs - one for rowid and one for primary for each table. You can run both for each table; One will work and one will fail, but who cares? It's easy... Use the output file snaploggen.sql as input to sqlplus.

qabv2:/opt/oracle/app/oracle/product/8.0.5/scripts$ more snaploggen.ksh echo 'CREATE TABLESPACE snapshotlog DATAFILE '"'/opt/oracle/app/oracle/oradata/PETS/data/snapshotlog01.dbf'"' SIZE 100K AUTOEXTEND ON NEXT 10K MAXSIZE 2048M DEFAULT STORAGE ( INITIAL 100K NEXT 10K MAXEXTENTS UNLIMITED PCTINCREASE 0 ) MINIMUM EXTENT 10K; ' ; echo 'COMMIT;' ;

for file in `cat snaptab`;
do

 echo 'CREATE SNAPSHOT LOG ON BVADMIN.'$file ' TABLESPACE SNAPSHOTLOG PCTFREE 60 PCTUSED 30 MAXTRANS 255 STORAGE ( INITIAL 25K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0); ' ; echo 'CREATE SNAPSHOT LOG ON BVADMIN.'$file ' TABLESPACE SNAPSHOTLOG PCTFREE 60 PCTUSED 30 MAXTRANS 255 STORAGE ( INITIAL 25K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0) with ROWID ; ' ;

done

3. CREATE DATABASE LINK dbalink.world

    CONNECT TO xxxx identified by xxxx
    USING 'abce';

4. see #1 for example.

5. Use a unix file to hold the list of tables to generate the snapshots: snaptab in this case, on the client:

qacsrdb:/opt/oracle/app/oracle/product/8.0.5$ more snaptab DC_LOOKUP
BV_USER
BILLING_ADDRESSES

BV_ALERT_SPEC
BV_DESTINATION_TABLE
BV_EXT_ADDITEM_FAILURES
BV_EXT_BO_REL
BV_EXT_GIFT

snapshot generator - run from unix, snapgen.ksh > snapgen.sql. This will generate two snapshots - one for rowid and one for primary for each table. You can run both for each table; One will work and one will fail, but ditto above. It's easy. Use the output file snapgen.sql as input to sqlplus.  

qacsrdb:/opt/oracle/app/oracle/product/8.0.5$ more snapgen.ksh for file in `cat snaptab`;
do
 echo 'CREATE SNAPSHOT ' $file ' TABLESPACE SNAPSHOT INITRANS 5 MAXTRANS 50 STORAGE ( INITIAL 2M NEXT 2M) REFRESH FAST START WITH sysdate NEXT sysdate + 1 AS select * from ' $file'@qa2link.world;';

 echo 'CREATE SNAPSHOT ' $file ' TABLESPACE SNAPSHOT INITRANS 5 MAXTRANS 50 STORAGE ( INITIAL 2M NEXT 2M) REFRESH FAST WITH ROWID START WITH sysdate NEXT sysdate + 1 AS select * from ' $file'@qa2link.world;';

done

6. And the cron job:

qacsrdb:/opt/oracle/app/oracle/product/8.0.5$ crontab -l
#-----------------------------------------------------------------------
# Snapshot every 5 minutes for csr database 0,5,10,15,20,25,30,35,40,45,50,55 * * * * /opt/oracle/app/oracle/product/8.0.5/scripts/refresh.ksh >> /tmp/refresh.log 2>&1
#-----------------------------------------------------------------------

qacsrdb:/opt/oracle/app/oracle/product/8.0.5/scripts$ more refresh.ksh #!/bin/ksh
export PATH=$PATH:/opt/oracle/app/oracle/product/8.0.5/bin:/usr/local/bin export HOME=/opt/oracle/app/affiliate

export ORACLE_BASE=/opt/oracle/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/8.0.5
export ORACLE_SID=QCSR

export CLASSPATH=$CLASSPATH:$ORACLE_HOME/jdbc/lib/classes111.zip export
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib:$ORACLE_HOME/jdbc/lib export ORACLE_NLS=$ORACLE_HOME/ocommon/nls/admin/data export TNS_ADMIN=$ORACLE_HOME/network/admin echo "Begin --- `date`"
sqlplus xxxx/xxxx << EOJ
set pagesize 0;
set feedback off;
set termout off;
set head off;
set echo off;
spool /tmp/refresh.sql
select 'execute DBMS_SNAPSHOT.REFRESH('||''''||name||''''||',''?'''||');'

        from user_snapshots where owner='BVADMIN'; spool off;
@/tmp/refresh.sql;
exit;
EOJ
echo "End ---- `date` "
echo `date` 'qacsr snapshot refresh completed' | mailx -s 'qacsr snapshot refresh, FYI' linda_at_pets.com;

PETS disclaimer: No one is responsible for anything I say, type, email, document or anything else other than me, and I don't warrant anything in this note. Use at your own risk. This is not for commercial use.

-----Original Message-----
From: Roy Ferguson [mailto:rferguso_at_level1.com] Sent: Tuesday, September 05, 2000 5:05 PM To: Multiple recipients of list ORACLE-L Subject: snapshot

having never done this before, I was hoping that someone would be able to help...

I've been asked to take either a complete or partial snapshot of our production
database every evening...

information on how to do this is greatly appreciated...

thanks in advance...roy

-- 
Author: Roy Ferguson
  INET: rferguso_at_level1.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
Received on Tue Sep 05 2000 - 19:13:33 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US