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

RE: snapshot - addendump

From: Linda Hagedorn <Linda_at_pets.com>
Date: Wed, 6 Sep 2000 11:50:48 -0700
Message-Id: <10611.116327@fatcity.com> 





For step 3 the master site has to be in the tnsnames on the client machine.
:) 



Linda 



-----Original Message-----



From: Linda Hagedorn [mailto:Linda_at_pets.com]
Sent: Tuesday, September 05, 2000 6:17 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: snapshot




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



Sent: Tuesday, September 05, 2000 5:05 PM
To: Multiple recipients of list ORACLE-L





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
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Author: Linda Hagedorn
  INET: Linda_at_pets.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 Wed Sep 06 2000 - 13:50:48 CDT












Original text of this message













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