Prev: Timestamp in the trace files
Next: Extracting 2.5 million rows
From: vsevolod afanassiev on 8 Jan 2010 03:22
Hi bob123,

I think you approach will work, just include step to rename datafiles
(after taking it offline and before recovering). In fact I remember
using this method a few years ago.

Not sure why people responding saying that you need to take it offline
before copying. Some time ago I needed to move a database from one
server to another (same OS) with minimal downtime. I thought that
physical standby database should help, but there was a minor
difference in OS version. Called Oracle Support and their response was
"your method will work but it is unsupported. Supported method is to
shut down the database and copy files when db is down".
From: Noons on 8 Jan 2010 07:31
Hemant K Chitale wrote,on my timestamp of 7/01/2010 6:26 PM:
> You have to take the Tablespace Offline *before* you move the
> datafiles and then use an ALTER DATABASE RENAME FILE 'oldlocation' TO
> 'newlocation' , then bring the Tablespace Online

A possible "sneaky" way of partially circumventing this restriction would be to
mark the ts read only for the duration of the copy and only offline it for the
period of renaming? At least that way it'd still be readable for the duration
of the copy.
From: Eugene Pokopac on 8 Jan 2010 09:37
On Jan 7, 3:06 pm, Mladen Gogala <n...(a)email.here.invalid> wrote:
> On Thu, 07 Jan 2010 06:29:02 -0800, Eugene Pokopac wrote:
> > 1 - ALTER TABLESPACE <ts_name> OFFLINE ;
>
> > 2 - At the server - Issue appropriate commands to copy ALL files in the
> > tablespace from the OLD location to the NEW location:
>
> >     Unix example: mv /u015/ORACLE/ofdvdata/<filename> /u004/ORACLE/
> > ofdv/<filename>
>
> > 3 - ALTER TABLESPACE <ts_name> RENAME DATAFILE
> >          /u015/ORACLE/ofdvdata/<filename>    TO
> >          /u004/ORACLE/ofdv/<filename> ;
>
> > 4 - ALTER TABLESPACE <ts_name> ONLINE ;
>
> Eugene, how about doing a little testing?
>
> --http://mgogala.byethost5.com

Oops! Forgot the quotes around the "u015" and "u004" specs in step 3.
Should read:

3 - ALTER TABLESPACE <ts_name> RENAME DATAFILE
'/u015/ORACLE/ofdvdata/<filename>' TO
'/u004/ORACLE/ofdv/<filename>' ;

Real life example from a working script (can be done totally within
SQL*Plus):

ALTER TABLESPACE apps_ts_archive OFFLINE ;

HOST mv /u015/ORACLE/ofdvdata/APPS_TS_ARCHIVE01.dbf /u005/ORACLE/ofdv/
APPS_TS_ARCHIVE01.dbf

ALTER TABLESPACE apps_ts_archive RENAME DATAFILE
'/u015/ORACLE/ofdvdata/APPS_TS_ARCHIVE01.dbf' TO
'/u005/ORACLE/ofdv/APPS_TS_ARCHIVE01.dbf' ;

ALTER TABLESPACE apps_ts_archive ONLINE ;
From: joel garry on 8 Jan 2010 12:10
On Jan 8, 12:22 am, vsevolod afanassiev
<vsevolod.afanass...(a)gmail.com> wrote:
> Hi bob123,
>
> I think you approach will work, just include step to rename datafiles
> (after taking it offline and before recovering). In fact I remember
> using this method a few years ago.
>
> Not sure why people responding saying that you need to take it offline
> before copying. Some time ago I needed to move a database from one
> server to another (same OS) with minimal downtime. I thought that
> physical standby database should help, but there was a minor
> difference in OS version. Called Oracle Support and their response was
> "your method will work but it is unsupported. Supported method is to
> shut down the database and copy files when db is down".

Something was bothering me about all this, since I had vague
recollections of being able to do it online, and you put your finger
on it. I speculate the online copy is unsupported because if there
are still transactions in the online redo log when the copied file is
bought online, the recovery may need to specify those. Seems strange
(and perhaps it is handled in automatic recovery, I don't know, the
coffee hasn't kicked in yet), but just think of all the "why do I get
file needs more recovery?" questions over the years. Maybe there are
just too many odd cases to test it all properly. Maybe someone just
decided set newname in rman makes it moot.

Test it, but I believe this is equivalent to what the OP was saying,
taking the bulk of the time before offlining:

1. Use RMAN to copy the datafile.
2. Offline the datafile.
3. Use RMAN run command: set newname to rename it, then switch
datafile.
4. Online datafile.

jg
--
@home.com is bogus.
http://www.psy.ritsumei.ac.jp/~akitaoka/saishin42e.html
From: vsevolod afanassiev on 8 Jan 2010 22:26
>> there
>> are still transactions in the online redo log when the copied file is
>> bought online,

Yes, and these online redo log files belong to the same database so
they will be used during recovery.

Let's say you have a database in archivelog mode, you place one
tablespace in hot backup mode
(alter tablespace begin backup) and 10 minutes later database crashes.
If you try to start the database it won't start,
it will complain that files in this tablespace require recovery. Why?
Because when you put tablespace in
backup mode Oracle stops updating SCN in the file header, so it looks
like files require recovery.
You don't need to run recovery, simply do 'alter database datafile end
backup'. But if you try recovery it will work.
It may require archivelog files, and after getting all information
from archiveelogs it will get transactions from redo logs.

Copying file to a different location is similar.
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Timestamp in the trace files
Next: Extracting 2.5 million rows