OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » Moving data files. (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Moving data files. [message #323290]

Wed, 28 May 2008 01:41

ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai

Senior Member

Hi,

I would like to move the data files from one mount point to other for the space management.

I want to move data files on the producation 24*7 environment.
Below i have mentioned 2 ways to move the datafile.
My questions are.

1. The techniques which i have specified, are those right?
2. If yes then which technique will be suitable for my production database.
3. Will performance impact if any?
4. Any suggestions.

Moving Datafiles while the Instance is Mounted

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> !mv /u04/TESTDB/oracle/oradata/ora01/TESTDB_B_IND/TESTDB_B_IND_01.dbf /u06/TESTDB/oracle/oradata/ora01/TESTDB_B_IND/TESTDB_B_IND_01.dbf
 
SQL> startup mount
ORACLE instance started.
 
Total System Global Area 1090519040 bytes
Fixed Size                  1218920 bytes
Variable Size             134219416 bytes
Database Buffers          939524096 bytes
Redo Buffers               15556608 bytes
Database mounted.

SQL> alter database rename file '/u04/TESTDB/oracle/oradata/ora01/TESTDB_B_IND/TESTDB_B_IND_01.dbf' to '/u06/TESTDB/oracle/oradata/ora01/TESTDB_B_IND/TESTDB_B_IND_01.dbf';
 
Database altered.
 
SQL> alter database open;
 
Database altered.

Moving Datafiles while the Instance is Open


SQL> alter tablespace TESTDB_B_IND offline;
 
Tablespace altered.
 
SQL> !mv /u06/TESTDB/oracle/oradata/ora01/TESTDB_B_IND/TESTDB_B_IND_01.dbf /u04/TESTDB/oracle/oradata/ora01/TESTDB_B_IND/TESTDB_B_IND_01.dbf
 
SQL>
SQL> alter tablespace TESTDB_B_IND rename datafile
  2  '/u06/TESTDB/oracle/oradata/ora01/TESTDB_B_IND/TESTDB_B_IND_01.dbf' to '/u04/TESTDB/oracle/oradata/ora01/TESTDB_B_IND/TESTDB_B_IND_01.dbf';
 
Tablespace altered.
 
SQL>  alter tablespace TESTDB_B_IND online;
 
Tablespace altered.

Thanks

[Updated on: Wed, 28 May 2008 01:42]

Report message to a moderator

Re: Moving data files. [message #323297 is a reply to message #323290]

Wed, 28 May 2008 02:07

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

You decide which you want. You want your database down or make it up and do operation.

I don't think any performance impact that can happen.

Report message to a moderator

Re: Moving data files. [message #323586 is a reply to message #323297]

Wed, 28 May 2008 22:55

Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India

Senior Member

Hi,

As it is your 24X7 production database, i think second technique would be more appropriate for you.

Regards...

Report message to a moderator

Previous Topic:	Oracle Locale Builder : NLB Generation Error
Next Topic:	extent size

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 06 13:58:51 CDT 2024