Re: daily clone
REUSE applies to the ControlFile [ie, reuse if file exists, error if file exists and REUSE not specified]. SET applies to the Database Name [SET is used when changing the datababase name] Hemant At 05:44 AM 03-04-03 -0800, you wrote: Jared, Thanks for the reply. I will try next time. I just feel like combine the reuse and set together, seems not that logical. Thanks, Joan [EMAIL PROTECTED] wrote: From TFM: Specify REUSE to indicate that existing control files identified by the initialization parameter CONTROL_FILES can be reused, thus ignoring and overwriting any information they may currently contain. If you omit this clause and any of these control files already exists, Oracle returns an error. So it would seem a valid syntax. Jared Joan Hsieh [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/02/2003 12:48 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: daily clone Bill, Kind of curiosity, I don't know this syntax; I do create controlfile set database or create controlfile resue database. But never did reuse set. Is that a valid syntax? I don't have time to check it by myself. sorry to ask. Joan CREATE CONTROLFILE REUSE SET DATABASE SIDB RESETLOGS NOARCHIVELOG Niall Litchfield wrote: Obviously the usual caveats of 'test this first' and ' don't just listen to some bloke off of email' apply but here would be my contributions. 1. Don't copy temp - create a new one - it by definition has nothing in it. 2. Don't copy online redo logs - resetlogs means that a) the old ones are useless and the SCN will be reset b) new ones will get created if necessary. 3. I'm not quite clear what is going on with these read/write-read-only nfs files, are these genuinely copied across to a new location, or are they the actual datafiles mounted read-only in an nfs environment, or are they some sort of weird vendor-provided copy of the datafiles? This is probably all my stupidity in not reading you clearly enough. Niall -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: 26 March 2003 19:59 To: Multiple recipients of list ORACLE-L Subject: daily clone 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
Re: daily clone
Jared, Thanks for the reply. I will try next time. I just feel like combine the reuse and set together, seems not that logical. Thanks, Joan [EMAIL PROTECTED] wrote: From TFM: Specify REUSE to indicate that existing control files identified by the initialization parameter CONTROL_FILES can be reused, thus ignoring and overwriting any information they may currently contain. If you omit this clause and any of these control files already exists, Oracle returns an error. So it would seem a valid syntax. Jared Joan Hsieh [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/02/2003 12:48 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: daily clone Bill, Kind of curiosity, I don't know this syntax; I do create controlfile set database or create controlfile resue database. But never did reuse set. Is that a valid syntax? I don't have time to check it by myself. sorry to ask. Joan CREATE CONTROLFILE REUSE SET DATABASE SIDB RESETLOGS NOARCHIVELOG Niall Litchfield wrote: Obviously the usual caveats of 'test this first' and ' don't just listen to some bloke off of email' apply but here would be my contributions. 1. Don't copy temp - create a new one - it by definition has nothing in it. 2. Don't copy online redo logs - resetlogs means that a) the old ones are useless and the SCN will be reset b) new ones will get created if necessary. 3. I'm not quite clear what is going on with these read/write-read-only nfs files, are these genuinely copied across to a new location, or are they the actual datafiles mounted read-only in an nfs environment, or are they some sort of weird vendor-provided copy of the datafiles? This is probably all my stupidity in not reading you clearly enough. Niall -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: 26 March 2003 19:59 To: Multiple recipients of list ORACLE-L Subject: daily clone 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
Re: daily clone
Bill, Kind of curiosity, I don't know this syntax; I do create controlfile set database or create controlfile resue database. But never did reuse set. Is that a valid syntax? I don't have time to check it by myself. sorry to ask. Joan CREATE CONTROLFILE REUSE SET DATABASE SIDB RESETLOGS NOARCHIVELOG Niall Litchfield wrote: Obviously the usual caveats of 'test this first' and ' don't just listen to some bloke off of email' apply but here would be my contributions. 1. Don't copy temp - create a new one - it by definition has nothing in it. 2. Don't copy online redo logs - resetlogs means that a) the old ones are useless and the SCN will be reset b) new ones will get created if necessary. 3. I'm not quite clear what is going on with these read/write-read-only nfs files, are these genuinely copied across to a new location, or are they the actual datafiles mounted read-only in an nfs environment, or are they some sort of weird vendor-provided copy of the datafiles? This is probably all my stupidity in not reading you clearly enough. Niall -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: 26 March 2003 19:59 To: Multiple recipients of list ORACLE-L Subject: daily clone 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
Re: daily clone
From TFM: Specify REUSE to indicate that existing control files identified by the initialization parameter CONTROL_FILES can be reused, thus ignoring and overwriting any information they may currently contain. If you omit this clause and any of these control files already exists, Oracle returns an error. So it would seem a valid syntax. Jared Joan Hsieh [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/02/2003 12:48 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: daily clone Bill, Kind of curiosity, I don't know this syntax; I do create controlfile set database or create controlfile resue database. But never did reuse set. Is that a valid syntax? I don't have time to check it by myself. sorry to ask. Joan CREATE CONTROLFILE REUSE SET DATABASE SIDB RESETLOGS NOARCHIVELOG Niall Litchfield wrote: Obviously the usual caveats of 'test this first' and ' don't just listen to some bloke off of email' apply but here would be my contributions. 1. Don't copy temp - create a new one - it by definition has nothing in it. 2. Don't copy online redo logs - resetlogs means that a) the old ones are useless and the SCN will be reset b) new ones will get created if necessary. 3. I'm not quite clear what is going on with these read/write-read-only nfs files, are these genuinely copied across to a new location, or are they the actual datafiles mounted read-only in an nfs environment, or are they some sort of weird vendor-provided copy of the datafiles? This is probably all my stupidity in not reading you clearly enough. Niall -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: 26 March 2003 19:59 To: Multiple recipients of list ORACLE-L Subject: daily clone 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
RE: daily clone
Obviously the usual caveats of 'test this first' and ' don't just listen to some bloke off of email' apply but here would be my contributions. 1. Don't copy temp - create a new one - it by definition has nothing in it. 2. Don't copy online redo logs - resetlogs means that a) the old ones are useless and the SCN will be reset b) new ones will get created if necessary. 3. I'm not quite clear what is going on with these read/write-read-only nfs files, are these genuinely copied across to a new location, or are they the actual datafiles mounted read-only in an nfs environment, or are they some sort of weird vendor-provided copy of the datafiles? This is probably all my stupidity in not reading you clearly enough. Niall -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: 26 March 2003 19:59 To: Multiple recipients of list ORACLE-L Subject: daily clone 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: Niall Litchfield 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
daily clone
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).
RE: daily clone
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).
