DBA Hub

📋Steps in this guide1/8

Renaming or Moving Oracle Files

An explanation of how assorted Oracle files can be renamed or moved to a new location.

oracle miscconfigurationintermediate
by OracleDba
15 views
1

Controlfiles

The current location of the controlfiles can be queried from the view, as shown below. In order to rename or move these files we must alter the value of the instance parameter. To move or rename a controlfile do the following. - Alter the parameter using the comamnd. - Shutdown the database. - Rename the physical file on the OS. - Start the database. The following SQL*Plus output shows how this is done for an instance using an spfile. For instances using a pfile replace the spfile manipulation steps with an amendment of the parameter in the init.ora file. Repeating the initial query shows that the the controlfile has been renamed in the data dictionary.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
SQL> select name from v$controlfile;

NAME
-------------------------------------------------------------
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\CONTROL01.CTL
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\CONTROL02.CTL
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\CONTROL03.CTL

3 rows selected.

SQL>

SQL> show parameter control_files

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
control_files                        string                           C:\ORACLE\ORADATA\DB10G\CONTRO
                                                                      L01.CTL, C:\ORACLE\ORADATA\DB1
                                                                      0G\CONTROL02.CTL, C:\ORACLE\OR
                                                                      ADATA\DB10G\CONTROL03.CTL
SQL>

SQL> ALTER SYSTEM SET control_files='C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\RENAME_CONTROL01.CTL', -
> 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\CONTROL02.CTL', -
> 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\CONTROL03.CTL' SCOPE=SPFILE;

System altered.

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> HOST MOVE C:\ORACLE\ORADATA\DB10G\CONTROL01.CTL C:\ORACLE\ORADATA\DB10G\RENAME_CONTROL01.CTL

SQL> STARTUP
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                   787968 bytes
Variable Size              61864448 bytes
Database Buffers          104857600 bytes
Redo Buffers                 262144 bytes
Database mounted.
SQL>

SQL> select name from v$controlfile;

NAME
-------------------------------------------------------------
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\RENAME_CONTROL01.CTL
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\CONTROL02.CTL
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\CONTROL03.CTL

3 rows selected.

SQL>
2

Logfiles

The current location of the logfiles can be queried from the view, as shown below. To move or rename a logfile do the following. - Shutdown the database. - Rename the physical file on the OS. - Start the database in mount mode. - Issue the command to rename the file within the Oracle dictionary. - Open the database. The following SQL*Plus output shows how this is done. Repeating the initial query shows that the the logfile has been renamed in the data dictionary.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
SQL> SELECT member FROM v$logfile;

MEMBER
-------------------------------------------------
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\REDO03.LOG
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\REDO02.LOG
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\REDO01.LOG

3 rows selected.

SQL>

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> HOST MOVE C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\REDO01.LOG C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\RENAME_REDO01.LOG

SQL> STARTUP MOUNT
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                   787968 bytes
Variable Size              61864448 bytes
Database Buffers          104857600 bytes
Redo Buffers                 262144 bytes
Database mounted.
SQL> ALTER DATABASE RENAME FILE 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\REDO01.LOG' -
>  TO 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\RENAME_REDO01.LOG';

Database altered.

SQL> ALTER DATABASE OPEN;

Database altered.

SQL>

SQL> SELECT member FROM v$logfile;

MEMBER
-------------------------------------------------
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\REDO03.LOG
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\REDO02.LOG
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\RENAME_REDO01.LOG

3 rows selected.

SQL>
3

Online Move (12c)

Oracle 12c includes the ALTER DATABASE MOVE DATAFILE command, which performs an online move of a datafile.

Code/Command (click line numbers to comment):

1
2
3
4
5
SQL> ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/cdb1/system01.dbf' TO '/tmp/system01.dbf';

Database altered.

SQL>
4

RMAN

RMAN can be used to move files with less downtime by copying them in advance of the move, then recovering them as part of the move itself. First, log in to RMAN and list the current files. Copy the file(s) to the new location. Turn the tablespace to offline. We could have turned the tablespace offline before the copy, removing the need for a recovery, but the tablespace would have been offline longer using that method. Switch to the new datafile copy(s) and recover the tablespace. Turn the tablespace online again. Remove the old datafile(s). Listing the current files shows the move is complete. Moving the SYSTEM tablespace is possible using a similar method, but the database must be shutdown and mounted before the switch and recover can be done.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
RMAN> REPORT SCHEMA;

Report of database schema for database with db_unique_name DB11G

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    750      SYSTEM               ***     /u01/app/oracle/oradata/DB11G/system01.dbf
2    1150     SYSAUX               ***     /u01/app/oracle/oradata/DB11G/sysaux01.dbf
3    444      UNDOTBS1             ***     /u01/app/oracle/oradata/DB11G/undotbs01.dbf
4    120      USERS                ***     /u01/app/oracle/oradata/DB11G/users01.dbf
5    345      EXAMPLE              ***     /u01/app/oracle/oradata/DB11G/example01.dbf
8    3277     SOE                  ***     /u01/app/oracle/product/11.2.0.2/db_1/dbs/soe.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    370      TEMP                 32767       /u01/app/oracle/oradata/DB11G/temp01.dbf

RMAN>

RMAN> COPY DATAFILE 8 TO '/u01/app/oracle/oradata/DB11G/soe.dbf';

RMAN> SQL 'ALTER TABLESPACE soe OFFLINE';

RMAN> SWITCH DATAFILE 8 TO COPY;
RMAN> RECOVER TABLESPACE soe;

RMAN> SQL 'ALTER TABLESPACE soe ONLINE';

RMAN> HOST 'rm /u01/app/oracle/product/11.2.0.2/db_1/dbs/soe.dbf';

RMAN> REPORT SCHEMA;

Report of database schema for database with db_unique_name DB11G

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    750      SYSTEM               ***     /u01/app/oracle/oradata/DB11G/system01.dbf
2    1150     SYSAUX               ***     /u01/app/oracle/oradata/DB11G/sysaux01.dbf
3    444      UNDOTBS1             ***     /u01/app/oracle/oradata/DB11G/undotbs01.dbf
4    120      USERS                ***     /u01/app/oracle/oradata/DB11G/users01.dbf
5    345      EXAMPLE              ***     /u01/app/oracle/oradata/DB11G/example01.dbf
8    3277     SOE                  ***     /u01/app/oracle/oradata/DB11G/soe.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    370      TEMP                 32767       /u01/app/oracle/oradata/DB11G/temp01.dbf

RMAN>
5

Manual (Almost Online)

For tablespaces other than the tablespace, you can move the datafiles while the database is online, provided you take the relevant tablespace offline during the rename operation. An example of this is shown below. The downtime associated with the tablespace rename is dependent on the length of time the physical rename/move takes. For a simple rename in place, it should happen immediately. If the file has to be moved to a new location, it will take as long as the file move takes to complete. Thanks Noons for pointing out this glaring omission from the article.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
ALTER TABLESPACE tablespace-name OFFLINE NORMAL;

-- Move/Rename the physical file.

ALTER TABLESPACE tablespace-name
    RENAME DATAFILE '/original/path/to/file1',
                    '/original/path/to/file2'
                 TO '/new/path/to/file1', 
                    '/new/path/to/file2';

ALTER TABLESPACE tablespace-name ONLINE;

SQL> SELECT name FROM v$datafile WHERE name LIKE '%users01%';

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb1/users01.dbf

SQL>

ALTER TABLESPACE users OFFLINE NORMAL;

HOST mv /u01/app/oracle/oradata/cdb1/users01.dbf /u01/app/oracle/oradata/cdb1/users02.dbf

ALTER TABLESPACE users
    RENAME DATAFILE '/u01/app/oracle/oradata/cdb1/users01.dbf'
                 TO '/u01/app/oracle/oradata/cdb1/users02.dbf';

ALTER TABLESPACE users ONLINE;

SQL> SELECT name FROM v$datafile WHERE name LIKE '%users02%';

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb1/users02.dbf

SQL>
6

Manual (Offline)

The process for manually renaming a datafile is the same as renaming a logfile, but for the sake of clarity it is repeated below. The current location of the datafiles can be queried from the view, as shown below. To move or rename a datafile do the following. - Shutdown the database. - Rename the physical file on the OS. - Start the database in mount mode. - Issue the command to rename the file within the Oracle dictionary. - Open the database. The following SQL*Plus output shows how this is done. Repeating the initial query shows that the the datafile has been renamed in the data dictionary.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
SQL> SELECT name FROM v$datafile;

NAME
---------------------------------------------------------
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\SYSTEM01.DBF
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\UNDOTBS01.DBF
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\SYSAUX01.DBF
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\USERS01.DBF

4 rows selected.

SQL>

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> HOST MOVE C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\USERS01.DBF C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\RENAME_USERS01.DBF

SQL> STARTUP MOUNT
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                   787968 bytes
Variable Size              61864448 bytes
Database Buffers          104857600 bytes
Redo Buffers                 262144 bytes
Database mounted.
SQL> ALTER DATABASE RENAME FILE 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\USERS01.DBF' -
>  TO 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\RENAME_USERS01.DBF';

Database altered.

SQL> ALTER DATABASE OPEN;

Database altered.

SQL>

SQL> SELECT name FROM v$datafile;

NAME
---------------------------------------------------------
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\SYSTEM01.DBF
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\UNDOTBS01.DBF
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\SYSAUX01.DBF
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\RENAME_USERS01.DBF

4 rows selected.

SQL>
7

Recreating the Controlfile

For largescale rearrangements it may be easier to manipulate the controlfile contents manually by backing up the controlfile to trace. The resulting trace file in the directory contains commands and instructions for recreating the controlfile. The paths in the command can be manipulated to rename all datafiles and logfiles on one step. This is quite a drastic step and it may affect the usefulness of existing backups, especially if the controlfile is being used as the recovery catlog.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
SQL> CONN sys/password AS SYSDBA
Connected.
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Database altered.

SQL>
8

Data Guard Environments

In Data Guard environments you have to be careful about renaming and moving files. The STANDBY_FILE_MANAGEMENT parameter determines how file changes on the primary server are applied to the standby server. When set to AUTO, files added or deleted under normal database use will be automatically created or deleted on the standby server. When set to MANUAL, this automatic maintenance will not happen. If you are manually moving or renaming files in a Data Guard environment where , you should first set , make your changes in the primary and standby environment, then set again. For more information see: Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!