Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Copying database from one location to another
On Wed, 21 Feb 2001 19:38:01 GMT, "kush" <kdhakal_at_eline.com> wrote:
Hello,
I do a test by your situation. Maybe you could try it.
'Cause your primary database cannot shutdown :-( All you may copy directly is "INACTIVE logfiles", all archived log, and last time hotbackup datafiles,
In primary database :
step1 : "alter database backup controlfile to trace;"
for generating create controlfile script. step2 : "alter systwm switch logfile;" for change "Current" logfile and force to checkpoint. It's better to swtich for preventing lost transactions step3 : copy All INACTIVE logfiles hotbackup datafiles,archived logs to new location
for "CREATE CONTROLFILE SET ... RESETLOGS". the CREATE CONTROLFILE script could exclude Current log group of primary db. step6 : "recover database until cancel using backup controlfile;" It will ask for archived logifle and apply them. The last logfile need to apply is the CURRENT logfile of primary db.You could choose cancel or apply the file.step7 : alter database open resetlogs;
make sure all data is available!!
And another solution is from http://technet.oracle.com You could reference it too.
Violin.
violin.hsiao_at_mail.pouchen.com.tw
Subject:
RECOVERY rollforward archivelog using backup controlfile Type: BULLETIN Status: PUBLISHED
ontent Type:
EXT/PLAIN reation Date:
9-APR-1999 ast Revision
ate:
7-APR-2000 anguage:
SAENG
This test scenario demonstrates how to apply archivelog files to a
clean
but old backup.
Let's first shut the database down in it's current situation:
SVRMGR> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
We make a clean cold backup of the datbase; namely, all files,
controlfiles,
datafiles, etc. Then, start the database up again.
Checking the Archive_Dest directory, we see that the last archivelog
file for
this test is 'arc84.1'. We'll come back to that later.
SVRMGR> connect internal/oracle
Connected.
SVRMGR> startup
ORACLE instance started.
Total System Global Area 11907072 bytes Fixed Size 49152 bytes Variable Size 10657792 bytes Database Buffers 1126400 bytes Redo Buffers 73728 bytesDatabase mounted.
As a test we create a new table, insert some rows, and then make
some
logswitches:
SVRMGR> create table testtable(a number(2));
Statement processed.
SVRMGR> insert into testtable values(2);
1 row processed.
SVRMGR> insert into testtable values(2);
1 row processed.
SVRMGR> insert into testtable values(2);
1 row processed.
SVRMGR> insert into testtable values(2);
1 row processed.
SVRMGR> insert into testtable values(2);
1 row processed.
SVRMGR> commit;
Statement processed.
SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
Again checking the Archive_Dest directory, we now see the last archivelog file
is arc87.1. (Three onwards from the arc84.1 we had previously).
We now intend to restore the backup we made earlier.
The plan is to apply the three new archivelog files, so first we
start the
database up again:
SVRMGR> connect internal/oracle
Connected.
SVRMGR> startup mount
ORACLE instance started.
Total System Global Area 11907072 bytes Fixed Size 49152 bytes Variable Size 10657792 bytes Database Buffers 1126400 bytes Redo Buffers 73728 bytesDatabase mounted.
And now we issue the command to start the recovery and we apply all
the
archivelog files that are newer than any of the control or datafiles
that we
restored.
SVRMGR> alter database recover until cancel using backup
controlfile;
alter database recover until cancel using backup controlfile
*
ORA-00279: change 216252 generated at 04/09/99 16:39:56 needed for
thread 1
ORA-00289: suggestion : D:\ORA80\DATABASE\ARCHIVE\ARC85.1
ORA-00280: change 216252 for thread 1 is in sequence #85
SVRMGR> alter database recover continue default;
alter database recover continue default
*
ORA-00279: change 216265 generated at 04/09/99 16:45:34 needed for
thread 1
ORA-00289: suggestion : D:\ORA80\DATABASE\ARCHIVE\ARC86.1 ORA-00280: change 216265 for thread 1 is in sequence #86 ORA-00278: log file 'D:\ORA80\DATABASE\ARCHIVE\ARC85.1' no longerneeded for
ORA-00289: suggestion : D:\ORA80\DATABASE\ARCHIVE\ARC87.1 ORA-00280: change 216266 for thread 1 is in sequence #87 ORA-00278: log file 'D:\ORA80\DATABASE\ARCHIVE\ARC86.1' no longerneeded for
ORA-00289: suggestion : D:\ORA80\DATABASE\ARCHIVE\ARC88.1 ORA-00280: change 216267 for thread 1 is in sequence #88 ORA-00278: log file 'D:\ORA80\DATABASE\ARCHIVE\ARC87.1' no longerneeded for
ORA-27041: unable to open file OSD-04002: unable to open file O/S-Error: (OS 2) The system cannot find the file specified.
We could have stopped after 87 but there is no harm in continuing
until the
system cannot find the required file.
Now we stop the recovery and open the database:
SVRMGR> alter database recover cancel;
alter database recover cancel
Statement processed.
SVRMGR> alter database open resetlogs;
Statement processed.
And, finally, we prove that the new information that was not in the
backup
and was only stored in the archivelog files is now recovered:
SVRMGR> select * from testtable;
A
2 2 2 2 2
>hi! Could anyone tell me what are the steps to create a database in new
>location by reusing
>the controlfiles, datafiles, redologfiles and arch. files(if needed) from
>another database in another location.
>This is more like copying database from one machine to another except the
>fact that
>a. I can't shutdown the original database and take a cold backup(it has to
>be up all the time) and its running in archivelogmode.
>b. when I create(recover) database at new location, I need to do point in
>time recovery.
>
>both machine has oracle 8.1.5 standard edition.
>Thanks in advance.
>
>
Received on Fri Feb 23 2001 - 00:26:37 CST