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. [EMAIL PROTECTED] -----Original Message----- [mailto:[EMAIL PROTECTED] 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: 1) Shutdown immediate SIDB 2) Shutdown immediate SIDA, startup restrict, shutdown normal 3) Copy system datafile to target machine (I'm going to reset logs, dbs are shutdown, shouldn't require any recovery) 4) Create read-only copy of all user datafiles using a vendor feature called checkpoints (not Oracle checkpoints) 5) Startup SIDA 6) 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
