Oracle rename ( all the datafiles )

Oracle rename all datafile
Oracle move all datafile
oracle rename datafile

1. shutdown the database
2. move or rename the datafiles in the file system
3. startup mount the database
4. rename datafiles in the database





aim to change dir from /aptus/oracle/oradata/aptdb/aptdb to /aptus/oracle/oradata/aptdb/
for all the file


sql > create pfile from spfile ;


-bash-3.2$ pwd
/aptus/oracle/product/11.2.0/db_1/dbs


-bash-3.2$ vi init.ora

change the path of control file because I am moving control file as well .




current file location

-bash-3.2$ ls -ltr /aptus/oracle/oradata/aptdb/aptdb
total 2449428
-rw-r----- 1 oracle dba 52429312 Jun 2 22:01 redo02.log
-rw-r----- 1 oracle dba 104865792 Jun 3 06:56 temp01.dbf
-rw-r----- 1 oracle dba 52429312 Jun 3 10:10 redo03.log
-rw-r----- 1 oracle dba 5251072 Jun 3 10:16 users01.dbf
-rw-r----- 1 oracle dba 104865792 Jun 3 11:48 jtime79.dbf
-rw-r----- 1 oracle dba 838868992 Jun 3 12:05 undotbs01.dbf
-rw-r----- 1 oracle dba 629153792 Jun 3 12:06 sysaux01.dbf
-rw-r----- 1 oracle dba 734011392 Jun 3 12:06 system01.dbf
-rw-r----- 1 oracle dba 52429312 Jun 3 12:07 redo01.log
-rw-r----- 1 oracle dba 9748480 Jun 3 12:08 control02.ctl
-rw-r----- 1 oracle dba 9748480 Jun 3 12:08 control01.ctl




==========================================================
shutdown the database <<<<<<<<<<<<<<<<----

==========================================================


SQL> shutdown immediate;


cd /aptus/oracle/oradata/aptdb/aptdb

-bash-3.2$ mv *.* /aptus/oracle/oradata/aptdb/


-bash-3.2$ ls -ltr /aptus/oracle/oradata/aptdb/
total 2449440
-rw-r--r-- 1 oracle dba 0 Jun 1 14:06 aaa
-rw-r----- 1 oracle dba 52429312 Jun 2 22:01 redo02.log
-rw-r----- 1 oracle dba 104865792 Jun 3 06:56 temp01.dbf
-rw-r----- 1 oracle dba 52429312 Jun 3 10:10 redo03.log
-rw-r----- 1 oracle dba 5251072 Jun 3 12:09 users01.dbf
-rw-r----- 1 oracle dba 838868992 Jun 3 12:09 undotbs01.dbf
-rw-r----- 1 oracle dba 734011392 Jun 3 12:09 system01.dbf
-rw-r----- 1 oracle dba 629153792 Jun 3 12:09 sysaux01.dbf
-rw-r----- 1 oracle dba 52429312 Jun 3 12:09 redo01.log
-rw-r----- 1 oracle dba 104865792 Jun 3 12:09 jtime79.dbf
-rw-r----- 1 oracle dba 9748480 Jun 3 12:09 control02.ctl
-rw-r----- 1 oracle dba 9748480 Jun 3 12:09 control01.ctl
drwxr-x--- 2 oracle dba 4096 Jun 3 12:15 aptdb




SQL> startup mount pfile='/aptus/oracle/product/11.2.0/db_1/dbs/init.ora'
ORACLE instance started.

Total System Global Area 1436389376 bytes
Fixed Size 2213376 bytes
Variable Size 905972224 bytes
Database Buffers 520093696 bytes
Redo Buffers 8110080 bytes
Database mounted.



Note: We can't rename temporary files, for that we need to just recreate temporary tablespace with rename or new located tempfile.


ALTER DATABASE RENAME FILE '/aptus/oracle/oradata/aptdb/aptdb/system01.dbf' to '/aptus/oracle/oradata/aptdb/system01.dbf' ;
ALTER DATABASE RENAME FILE '/aptus/oracle/oradata/aptdb/aptdb/sysaux01.dbf' to '/aptus/oracle/oradata/aptdb/sysaux01.dbf' ;
ALTER DATABASE RENAME FILE '/aptus/oracle/oradata/aptdb/aptdb/undotbs01.dbf' to '/aptus/oracle/oradata/aptdb/undotbs01.dbf' ;
ALTER DATABASE RENAME FILE '/aptus/oracle/oradata/aptdb/aptdb/users01.dbf' to '/aptus/oracle/oradata/aptdb/users01.dbf' ;
ALTER DATABASE RENAME FILE '/aptus/oracle/oradata/aptdb/aptdb/redo01.log' to '/aptus/oracle/oradata/aptdb/redo01.log' ;
ALTER DATABASE RENAME FILE '/aptus/oracle/oradata/aptdb/aptdb/redo02.log' to '/aptus/oracle/oradata/aptdb/redo02.log' ;
ALTER DATABASE RENAME FILE '/aptus/oracle/oradata/aptdb/aptdb/redo03.log' to '/aptus/oracle/oradata/aptdb/redo03.log' ;
ALTER DATABASE RENAME FILE '/aptus/oracle/oradata/aptdb/aptdb/jtime79.dbf' to '/aptus/oracle/oradata/aptdb/jtime79.dbf' ;


Note: We can't rename temporary files, for that we need to just recreate temporary tablespace with rename or new located tempfile.


SQL> alter database open;

Database altered.


for temp tablespace <<<<


DROP TABLESPACE temp;

CREATE TEMPORARY TABLESPACE TEMP1
TEMPFILE '/aptus/oracle/oradata/aptdb/temp01.dbf' SIZE 500M REUSE
AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;



create temporary tablespace temp1 tempfile 'c:\oracle\oradata\acme\temp1.dbf' size 10M;



SQL> CREATE TEMPORARY TABLESPACE TEMP1
TEMPFILE '/aptus/oracle/oradata/aptdb/temp01.dbf' SIZE 500M REUSE
AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.

SQL> alter database default temporary tablespace temp1;

Database altered.

SQL> drop tablespace temp ;

Tablespace dropped.


at the end create spfile

create spfile from pfile='/aptus/oracle/product/11.2.0/db_1/dbs/init.ora' ;