Oracle-L: RE: daily clone

From: DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM>
Date: Wed, 26 Mar 2003 12:38:58 -0800
Message-ID: <F001.00573A45.20030326123858@fatcity.com>

Bill

If a tablespace is read-only, it should recover fine, nothing to write. I don't think you can recover a tablespace from another database. I think they will have different SCN numbers. I think only read-only tablespaces are allowed with different SCN numbers. However, before backup, you could put those tablespaces in read-only mode, then perform a transportable tablespace export, then after recreating your new database, import them into it.

Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
[mailto:becker.bill_at_marshfieldclinic.org] Sent: Wednesday, March 26, 2003 1:59 PM
To: Multiple recipients of list ORACLE-L

Hello,

Env: Oracle 9.2.0.2.0 on Solaris 9 (2 machines)

I'm trying to set up a daily cloning process between 2 Oracle instances (SIDA is source, SIDB is target) using the CREATE CONTROLFILE REUSE SET DATABASE SIDB ... method. The steps are:

Shutdown immediate SIDB
Shutdown immediate SIDA, startup restrict, shutdown normal
Copy system datafile to target machine (I'm going to reset logs, dbs are shutdown, shouldn't require any recovery)
Create read-only copy of all user datafiles using a vendor feature called checkpoints (not Oracle checkpoints)
Startup SIDA
Run the following script on SIDB (adapted from SIDA backup controlfile to trace)

STARTUP NOMOUNT PFILE='/oracle_home_path/dbs/initDWQ.ora' CREATE CONTROLFILE REUSE SET DATABASE "SIDB" RESETLOGS NOARCHIVELOG     MAXLOGFILES 5
    MAXLOGMEMBERS 3
    MAXDATAFILES 512
    MAXINSTANCES 1
    MAXLOGHISTORY 1817
LOGFILE

  GROUP 1 '/redo1/dws/redo01.log'  SIZE 100M,
  GROUP 2 '/redo1/dws/redo02.log'  SIZE 100M,
  GROUP 3 '/redo1/dws/redo03.log'  SIZE 100M

DATAFILE
'/dwdata1/dwq/sys/system01.dbf',
'/rbs/dws/undotbs01.dbf',
'/data1/dws/users.dbf',
'/data1/dws/tools.dbf',
'/dwdata1.chkpnt/ckpt1/dws/data/lvl1_data/LVL1_DATA01.dbf',
'/dwdata2.chkpnt/ckpt1/dws/index/lvl1_ndex/LVL1_NDEX01.dbf',
'/dwdata1.chkpnt/ckpt1/dws/data/lvl2_data/LVL2_DATA01.dbf',
'/dwdata2.chkpnt/ckpt1/dws/index/lvl2_ndex/LVL2_NDEX01.dbf',
...
CHARACTER SET WE8ISO8859P1
;

ALTER DATABASE OPEN RESETLOGS;
ALTER TABLESPACE TEMP ADD TEMPFILE '/oratmp1/dwq/temp01.dbf'

SIZE 2049M REUSE AUTOEXTEND OFF; There are a couple of twists. The datafiles listed with .chkpnt in their path are in a read-only nfs-mounted filesystem; when shutdown before copying, their status within Oracle was READ WRITE.

The databases were both shutdown when the copies were made, SIDB is using RESETLOGS, so I'm thinking this will work OK and Oracle will not try and write anything to these when opening SIDB.

Does this sound OK?

The next question is, do I need to copy any redo logs, undo tblspc (using auto undo), or temp datafiles from SIDA to SIDB? Again, since I'm shutting down cleanly, and doing a resetlogs on opening, I am hoping that I can simply reuse
the existing redo logs, undo tblspc and tempfile.

The next twist is that we want to preserve some read write tablespaces in SIDB,
like users.dbf and tools.dbf listed above, and not wipe them out when re-creating the controlfile each day. Again, since we shut down SIDB cleanly,
and I list the existing datafiles under the datafile section of the CREATE CONTROLFILE command, I am hoping this will preserve their contents. Am I wrong?

We will be testing these scenarios, but I am hoping for some insightful advice from others who have gone before.

Sorry for the lengthy message, and as always, thanks to any responders. Bill

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: becker.bill_at_marshfieldclinic.org

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  INET: DWILLIAMS_at_LIFETOUCH.COM

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).

Received on Wed Mar 26 2003 - 14:38:58 CST