help "cloning " a database 2004-05-18 - By Rich Holland
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 (See ORA-00279.ora-code.com): change 83231342 generated at 05/18/2004 10:31:41 needed for
thread 1
ORA-00289 (See ORA-00289.ora-code.com): suggestion : /oracle/DST/oraarch/DSTarch1_397.dbf
ORA-00280 (See ORA-00280.ora-code.com): 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 (See ORA-01547.ora-code.com): warning: RECOVER succeeded but OPEN RESETLOGS would get
error
below
ORA-01195 (See ORA-01195.ora-code.com): online backup of file 1 needs more recovery to be consistent
ORA-01110 (See ORA-01110.ora-code.com): 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 (See ORA-01195.ora-code.com): online backup of file 1 needs more recovery to be consistent
ORA-01110 (See ORA-01110.ora-code.com): 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@(protected)
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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
|
|
