help "cloning" a database

From: Rich Holland <holland_at_guidancetech.com>
Date: Tue, 18 May 2004 13:37:56 -0400
Message-Id: <20040518173847.07688769@jester.pobox.com>

I'm trying to make a copy of a database (running SAP, Oracle 9.2.0.4 64-bit, AIX 5.1) and running into some difficulty with the system tablespace. It doesn't seem to matter if I shut the database down before the copy (offline) or put everything in backup mode first (online) -- I get the same end result either way. Maybe some oracle guru can steer me in the right direction.

SAP lays out the database with two mirrored redo log groups, log_archive_dest to /oracle/<SID>/oraarch, and all the data files in /oracle/<SID>/sapdata<##> (e.g. sapdata1, sapdata2, etc).

Here's what I'm doing to copy the system:

On the source system (SRC), I create a SQL copy of the control file:

        SQL> alter database backup controlfile to trace;

For all tablespaces except TEMP, I do:

        SQL> alter tablespace <TS> begin backup;

Then I copy the file systems from SRC to DST system:

$ cd /oracle/SRC
$ tar -cf -./sapdata* | cd (/oracle/DST ; tar -xvpf - )

Then I force log switches (four, just to be safe):

	SQL> alter system archive log current;
	SQL> alter system archive log current;
	SQL> alter system archive log current; 
	SQL> alter system archive log current;

Then I copy the redo & archived redo logs:


	$ cd /oracle/SRC

	$ tar -cf - ./mirr* ./orig* ./ora* | (cd /oracle/DST ; tar -xvpf -)

At this point it should be safe to take the source system out of backup mode, so I do that. I also fix the ownership of all the copied files:

$ cd /oracle/DST
$ find . -user orasrc -exec chown oradst {} ';'

Now I copy the control file backup I made, and edit out everything but the piece to rebuild the control file from set #2 (the backup creates two sets, one with 'noresetlogs' and one with 'resetlogs'), which looks sort of like this:

	STARTUP NOMOUNT
	CREATE CONTROLFILE REUSE DATABASE "SRC" RESETLOGS  ARCHIVELOG
	--  SET STANDBY TO MAXIMIZE PERFORMANCE
	    MAXLOGFILES 255
	    MAXLOGMEMBERS 3
	    MAXDATAFILES 254
	    MAXINSTANCES 50
	    MAXLOGHISTORY 1134
	[... LOGFILE info ...]
	DATAFILE
	  '/oracle/SRC/sapdata1/system_1/system.data1',
	[... rest of the datafiles ...]
	CHARACTER SET WE8DEC
	;

So I change all occurrences of 'SRC' to 'DST' in the file, and change 'REUSE' to 'SET' in the create statement, so it looks like:

        CREATE CONTROLFILE SET DATABASE "DST" RESETLOGS ARCHIVELOG I then run the controlfile creation script in the target system:

	oradst$ sqlplus '/ as sysdba'
	SQL> @/tmp/cntrl.sql

This creates the control files successfully. I realize there will likely be some media recovery needed, so I make sure the archived redo logs are all there:

	oradst$ cd /oracle/DST/oraarch
	oradst$ for i in SRC*
	> do
	> ln $i `echo $i |sed -e 's/SRC/DST/'`
      > done

This creates hard links so for example, SRCarch1_392.dbf can also be referenced as DSTarch1_392.dbf. This just saves some typing during the recovery:

        SQL> recover database using backup controlfile until CANCEL;

At this point I'm prompted for the first archived redo log from the log switches I did earlier:

	ORA-00279: change 83231342 generated at 05/18/2004 10:31:41 needed for 
	thread 1
	ORA-00289: suggestion : /oracle/DST/oraarch/DSTarch1_397.dbf
	ORA-00280: change 83231342 for thread 1 is in sequence #397
	Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

So I press <RET> to take it's suggestion and it goes to the next one; it does this all the way until it rolls through log 400, which was the last of the 4 I created with the 'alter system archive log current' commands earlier. When it asks for 401, there isn't anything to give it, so I CANCEL the recovery, but I see this error:

        ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error

	below
	ORA-01195: online backup of file 1 needs more recovery to be consistent
	ORA-01110: data file 1: '/oracle/DST/sapdata1/system_1/system.data1'

Now that seems odd that the system tablespace would need further recovery, especially in light of the fact that it's current SCN number matches all the other files:

	SQL> set pagesize 40
	SQL> col name format A45
	SQL> col fn format 99
	SQL> select a.file# FN, b.change#, a.name
	  2> from v$datafile a, v$recover_file b
	  3> where a.file# = b.file#;

	FN    CHANGE# NAME
	--- ---------- ---------------------------------------------
	  1   83232063 /oracle/DST/sapdata1/system_1/system.data1
	  2   83232063 /oracle/DST/sapdata2/roll_1/roll.data1
	  3   83232063 /oracle/DST/sapdata4/sol_1/src.data1
	  4   83232063 /oracle/DST/sapdata4/sol_2/src.data2
	  5   83232063 /oracle/DST/sapdata4/sol_3/src.data3
	  6   83232063 /oracle/DST/sapdata4/sol_4/src.data4
	  7   83232063 /oracle/DST/sapdata4/sol_5/src.data5
	  8   83232063 /oracle/DST/sapdata3/sol620_1/src620.data1
	  9   83232063 /oracle/DST/sapdata3/sol620_2/src620.data2
	 10   83232063 /oracle/DST/sapdata3/sol620_3/src620.data3
	 11   83232063 /oracle/DST/sapdata1/solusr_1/srcusr.data1

So how come if the SCN numbers are the same in all the files, Oracle thinks it needs media recovery on the system data file? And why can't I open the database with resetlogs?

	SQL> alter database open resetlogs;
	alter database open resetlogs
	*
	ERROR at line 1:
	ORA-01195: online backup of file 1 needs more recovery to be consistent
	ORA-01110: data file 1: '/oracle/DST/sapdata1/system_1/system.data1'

Help! What am I overlooking here? I could swear I've done it this way in previous releases with no problems....

Thanks!
Rich

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Tue May 18 2004 - 12:36:56 CDT