Re: Problems cloning a database/database instance

Hi,

In the trace file generated with "alter database backup controlfile ..." there two sections, one with noresetlogs and another with resetlogs.

You have to modify the trace file to use it. For example, use the first part :

CONNECT /@test AS SYSDBA
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS NOARCHIVELOG     MAXLOGFILES 50
    MAXLOGMEMBERS 5
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 226
LOGFILE

  GROUP 1 'E:\Cloned\REDO01.LOG'  SIZE 100M,
  GROUP 2 'E:\Cloned\REDO02.LOG'  SIZE 100M,
  GROUP 3 'E:\Cloned\REDO03.LOG'  SIZE 100M

  'E:\Cloned\SYSTEM01.DBF',
  'E:\Cloned\UNDOTBS01.DBF',
  'E:\Cloned\CWMLITE01.DBF',
  'E:\Cloned\DRSYS01.DBF',
  'E:\Cloned\EXAMPLE01.DBF',
  'E:\Cloned\INDX01.DBF',
  'E:\Cloned\ODM01.DBF',
  'E:\Cloned\TOOLS01.DBF',
  'E:\Cloned\USERS01.DBF',
  'E:\Cloned\XDB01.DBF'

RECOVER DATABASE ALTER DATABASE OPEN; ALTER TABLESPACE TEMP ADD TEMPFILE 'E:\Cloned\TEMP01.DBF'

     SIZE 41943040 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M; Dias

ohaya <ohaya_at_cox.net> wrote in message news:<40CD4736.F213987C_at_cox.net>...
> Hi,
>
> I'm running Oracle 9i on a Win2K Server, and I've been trying to follow
> a procedure for cloning a database ("TEST") as shown here:
>
> http://www.sap-img.com/oracle-database/cloning-a-oracle-database.htm
>
> Eventually, if I can get past "Step 1", I actually want to clone both
> the database and the database instance, to set up a test environment.
>
> But, I'm having problems just getting past "Step 1".
>
> The first problem that I've run into is that the steps indicate that I
> should edit the .trc file that gets created by the ALTER DATABASE. But,
> in 'c', it says to add a reference to "the new init.ora" on the STARTUP
> NOMOUNT line. Also, after 'd', it says to "Copy the init.ora from the
> source database, and edit it so that it is appropriate for the Clone."
>
> The problem is, I don't have a init.ora (or inittest.ora) on the
> system. I do have an SPFILETEST.ora.
>
> I've been kind of floundering, and I ended up with the following
> mkcloned.sql, which was the result of editing the .TRC file and renaming
> it:
>
>
> ============= mkcloned.sql
> ==================================================
>
> CONNECT /@test AS SYSDBA
> STARTUP NOMOUNT
> CREATE CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS NOARCHIVELOG
> -- SET STANDBY TO MAXIMIZE PERFORMANCE
> MAXLOGFILES 50
> MAXLOGMEMBERS 5
> MAXDATAFILES 100
> MAXINSTANCES 1
> MAXLOGHISTORY 226
> LOGFILE
> GROUP 1 'E:\Cloned\REDO01.LOG' SIZE 100M,
> GROUP 2 'E:\Cloned\REDO02.LOG' SIZE 100M,
> GROUP 3 'E:\Cloned\REDO03.LOG' SIZE 100M
> -- STANDBY LOGFILE
> DATAFILE
> 'E:\Cloned\SYSTEM01.DBF',
> 'E:\Cloned\UNDOTBS01.DBF',
> 'E:\Cloned\CWMLITE01.DBF',
> 'E:\Cloned\DRSYS01.DBF',
> 'E:\Cloned\EXAMPLE01.DBF',
> 'E:\Cloned\INDX01.DBF',
> 'E:\Cloned\ODM01.DBF',
> 'E:\Cloned\TOOLS01.DBF',
> 'E:\Cloned\USERS01.DBF',
> 'E:\Cloned\XDB01.DBF'
> CHARACTER SET WE8MSWIN1252
> ;
> -- Recovery is required if any of the datafiles are restored backups,
> -- or if the last shutdown was not normal or immediate.
> RECOVER DATABASE
> -- Database can now be opened normally.
> ALTER DATABASE OPEN;
> ALTER TABLESPACE TEMP ADD TEMPFILE 'E:\Cloned\TEMP01.DBF'
> SIZE 41943040 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
> -- End of tempfile additions.
> --
> -- Set --2. RESETLOGS case
> --
> -- The following commands will create a new control file and use it
> -- to open the database.
> -- The contents of online logs will be lost and all backups will
> -- be invalidated. Use this only if online logs are damaged.
> STARTUP NOMOUNT
> CREATE CONTROLFILE REUSE DATABASE "TEST" RESETLOGS NOARCHIVELOG
> -- SET STANDBY TO MAXIMIZE PERFORMANCE
> MAXLOGFILES 50
> MAXLOGMEMBERS 5
> MAXDATAFILES 100
> MAXINSTANCES 1
> MAXLOGHISTORY 226
> LOGFILE
> GROUP 1 'E:\Cloned\REDO01.LOG' SIZE 100M,
> GROUP 2 'E:\Cloned\REDO02.LOG' SIZE 100M,
> GROUP 3 'E:\Cloned\REDO03.LOG' SIZE 100M
> -- STANDBY LOGFILE
> DATAFILE
> 'E:\Cloned\SYSTEM01.DBF',
> 'E:\Cloned\UNDOTBS01.DBF',
> 'E:\Cloned\CWMLITE01.DBF',
> 'E:\Cloned\DRSYS01.DBF',
> 'E:\Cloned\EXAMPLE01.DBF',
> 'E:\Cloned\INDX01.DBF',
> 'E:\Cloned\ODM01.DBF',
> 'E:\Cloned\TOOLS01.DBF',
> 'E:\Cloned\USERS01.DBF',
> 'E:\Cloned\XDB01.DBF'
> CHARACTER SET WE8MSWIN1252
> ;
> -- Recovery is required if any of the datafiles are restored backups,
> -- or if the last shutdown was not normal or immediate.
> RECOVER DATABASE USING BACKUP CONTROLFILE
> -- Database can now be opened zeroing the online logs.
> ALTER DATABASE OPEN RESETLOGS;
> -- Commands to add tempfiles to temporary tablespaces.
> -- Online tempfiles have complete space information.
> -- Other tempfiles may require adjustment.
> ALTER TABLESPACE TEMP ADD TEMPFILE 'E:\Cloned\TEMP01.DBF'
> SIZE 41943040 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
> -- End of tempfile additions.
> --
>
> ===============================================================
>
>
> I then copied the *.DBF and the REDO*.LOG files to E:\Cloned, and tried
> to run mkcloned.sql, BUT, when I run the mkcloned.sql, I am getting the
> following errors:
>
> ========== results of running mkcloned.sql ====================
>
> E:\Cloned>sqlplus /nolog
>
> SQL*Plus: Release 9.2.0.1.0 - Production on Mon Jun 14 02:10:15 2004
>
> Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
>
> SQL> @mkcloned.sql
> Connected to an idle instance.
> ORACLE instance started.
>
> Total System Global Area 135338868 bytes
> Fixed Size 453492 bytes
> Variable Size 109051904 bytes
> Database Buffers 25165824 bytes
> Redo Buffers 667648 bytes
>
> Control file created.
>
> ORA-00283: recovery session canceled due to errors
> ORA-00264: no recovery required
>
>
>
> Database altered.
>
>
> Tablespace altered.
>
> ORA-01081: cannot start already-running ORACLE - shut it down first
> CREATE CONTROLFILE REUSE DATABASE "TEST" RESETLOGS NOARCHIVELOG
> *
> ERROR at line 1:
> ORA-01503: CREATE CONTROLFILE failed
> ORA-01100: database already mounted
>
>
> ORA-00283: recovery session canceled due to errors
> ORA-01124: cannot recover data file 1 - file is in use or recovery
> ORA-01110: data file 1: 'E:\CLONED\SYSTEM01.DBF'
>
>
> ALTER DATABASE OPEN RESETLOGS
> *
> ERROR at line 1:
> ORA-01531: a database already open by the instance
>
>
> ALTER TABLESPACE TEMP ADD TEMPFILE 'E:\Cloned\TEMP01.DBF'
> *
> ERROR at line 1:
> ORA-01537: cannot add data file 'E:\Cloned\TEMP01.DBF' - file already
> part of
> database
>
>
> SQL> exit
> Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 -
> Production
> With the Partitioning, OLAP and Oracle Data Mining options
> JServer Release 9.2.0.1.0 - Production
>
> E:\Cloned>
>
> ===============================================================
>
>
> At this point, I'm really stuck! It looks like part of the process did
> SOMETHING, because if I look at the locations of the database files in
> Enterprise Manager, it's showing "e:\Cloned", but it still thinks that
> the SPFILE, Control files, etc. are in their original locations.
>
>
> Can someone tell me, what do I do for an init.ora or inittest.ora?
>
> Also, do I need to add the "PFILE=something" to the "START NOMOUNT"?
>
> Also, any ideas why I'm getting the other errors (e.g., the recovery
> session cancelled, CREATE CONTROLFILE failed, and database already
> mounted, etc.)?
>
> Thanks very much in advance!!!
Received on Mon Jun 14 2004 - 15:11:40 CDT