Hendry's "Oracle and SQL Server DBA Stuff"

Oracle and SQL Server Database Solutions for DBA's please visit http://hendrydba.com for the latest posts. Thanks

Move all datafiles including system to different directory / Mount point

Posted by Hendry chinnapparaj on January 24, 2011

Steps

———–

1) Shutdown database after creating pfile

2) copy/move datafiles, control files to new directory

3) edit init file, change the location for controlfiles

4) Rename / relocate datafile

5) open the database

 

SQL> select name from v$datafile;

 

NAME

——————————————————————————–

/u04/oradata/TEST/system01.dbf

/u04/oradata/TEST/undotbs01.dbf

/u04/oradata/TEST/sysaux01.dbf

/u04/oradata/TEST/users01.dbf

 

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

 

cpsoa1:/u04/oradata/TEST> cp -p system01.dbf /u02/oradata/TEST/

cpsoa1:/u04/oradata/TEST> cp -p undotbs01.dbf /u02/oradata/TEST/

cpsoa1:/u04/oradata/TEST> cp -p sysaux01.dbf /u02/oradata/TEST/

cpsoa1:/u04/oradata/TEST> cp -p users01.dbf /u02/oradata/TEST/

cpsoa1:/u04/oradata/TEST> cp -p temp01.dbf /u02/oradata/TEST/

cpsoa1:/u04/oradata/TEST> pwd

/u04/oradata/TEST

 

SQL> startup mount

ORACLE instance started.

 

Total System Global Area  524288000 bytes

Fixed Size                  2085296 bytes

Variable Size             163581520 bytes

Database Buffers          352321536 bytes

Redo Buffers                6299648 bytes

Database mounted.

 

SQL> Alter database rename file ‘/u04/oradata/TEST/system01.dbf’ to ‘/u02/oradata/TEST/system01.dbf’;

 

Database altered.

 

SQL> Alter database rename file ‘/u04/oradata/TEST/undotbs01.dbf’ to ‘/u02/oradata/TEST/undotbs01.dbf’;

 

Database altered.

 

SQL>  Alter database rename file ‘/u04/oradata/TEST/sysaux01.dbf’ to ‘/u02/oradata/TEST/sysaux01.dbf’;

 

Database altered.

 

SQL> Alter database rename file ‘/u04/oradata/TEST/users01.dbf’ to ‘/u02/oradata/TEST/users01.dbf’;

 

Database altered.

 

SQL> Alter database rename file ‘/u04/oradata/TEST/temp01.dbf’ to ‘/u02/oradata/TEST/temp01.dbf’;

 

Database altered.

 

SQL> alter database open;

 

Database altered.

 

SQL> select name from v$datafile;

 

NAME

——————————————————————————–

/u02/oradata/TEST/system01.dbf

/u02/oradata/TEST/undotbs01.dbf

/u02/oradata/TEST/sysaux01.dbf

/u02/oradata/TEST/users01.dbf

 

SQL> select name from v$tempfile;

 

NAME

——————————————————————————–

/u02/oradata/TEST/temp01.dbf

 

control files move

———————–

SQL> show parameter control

 

NAME                                 TYPE        VALUE

———————————— ———– ——————————

control_file_record_keep_time        integer     7

control_files                        string      /u04/oradata/TEST/control01.ct

l, /u04/oradata/TEST/control02

.ctl, /u04/oradata/TEST/contro

l03.ctl

 

 

SQL> show parameter spfile

 

NAME                                 TYPE        VALUE

———————————— ———– ——————————

spfile                               string      /u01/oracle/product/10.2/db_1/

dbs/spfileTEST.ora

 

 

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

Vi initTEST.ora

*.control_files=’/u02/oradata/TEST/control01.ctl’,’/u02/oradata/TEST/control02.ctl’,’/u02/oradata/TEST/control03.ctl’

 

cpsoa1:/u04/oradata/TEST> mv control01.ctl /u02/oradata/TEST/

cpsoa1:/u04/oradata/TEST> mv control02.ctl /u02/oradata/TEST/

cpsoa1:/u04/oradata/TEST> mv control03.ctl /u02/oradata/TEST/

 

SQL> select name from v$controlfile;

 

NAME

——————————————————————————–

/u02/oradata/TEST/control01.ctl

/u02/oradata/TEST/control02.ctl

/u02/oradata/TEST/control03.ctl

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: