This article presents a brief explanation of how assorted Oracle files can be renamed or moved to a new location. The examples are based on a default Oracle 10g installation on Windows, but the method is the same for different versions of Oracle on any platform, with the exception of the host command used to rename the file.
Controlfiles
The current location of the controlfiles can be queried from the
V$CONTROLFILE view, as shown below.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>
In order to rename or move these files we must alter the value of the
control_files instance parameter.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>
To move or rename a controlfile do the following.
- Alter the
control_filesparameter using theALTER SYSTEMcomamnd. - 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.
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>
Repeating the initial query shows that the the controlfile has been renamed in the data dictionary.
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>
Logfiles
The current location of the logfiles can be queried from the
V$LOGFILE view, as shown below.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>
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
ALTER DATABASE RENAME FILEcommand to rename the file within the Oracle dictionary. - Open the database.
The following SQL*Plus output shows how this is done.
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>
Repeating the initial query shows that the the logfile has been renamed in the data dictionary.
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>
Datafiles
Online Move (12c)
Oracle 12c includes the ALTER DATABASE MOVE DATAFILE command, which performs an online move of a datafile.
SQL> ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/cdb1/system01.dbf' TO '/tmp/system01.dbf'; Database altered. SQL>
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.
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>
Copy the file(s) to the new location.
RMAN> COPY DATAFILE 8 TO '/u01/app/oracle/oradata/DB11G/soe.dbf';
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.
RMAN> SQL 'ALTER TABLESPACE soe OFFLINE';
Switch to the new datafile copy(s) and recover the tablespace.
RMAN> SWITCH DATAFILE 8 TO COPY; RMAN> RECOVER TABLESPACE soe;
Turn the tablespace online again.
RMAN> SQL 'ALTER TABLESPACE soe ONLINE';
Remove the old datafile(s).
RMAN> HOST 'rm /u01/app/oracle/product/11.2.0.2/db_1/dbs/soe.dbf';
Listing the current files shows the move is complete.
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>
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.
Manual (Almost Online)
For tablespaces other than the
SYSTEM tablespace, you can move the datafiles while the database is online, provided you take the relevant tablespace offline during the rename operation.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;
An example of this is shown below.
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>
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.
Manual (Offline)
The process for manually renaming a datafile is the same as renaming a logfile, but for the same of clarity it is repeated below. The current location of the datafiles can be queried from the
V$DATAFILE view, as shown below.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>
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
ALTER DATABASE RENAME FILEcommand to rename the file within the Oracle dictionary. - Open the database.
The following SQL*Plus output shows how this is done.
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>
Repeating the initial query shows that the the datafile has been renamed in the data dictionary.
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>
Recreating the Controlfile
For largescale rearrangements it may be easier to manipulate the controlfile contents manually by backing up the controlfile to trace.
SQL> CONN sys/password AS SYSDBA Connected. SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE; Database altered. SQL>
The resulting trace file in the
user_dump_dest directory contains commands and instructions for recreating the controlfile. The paths in the CREATE CONTROLFILE 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.
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
STANDBY_FILE_MANAGEMENT=AUTO, you should first set STANDBY_FILE_MANAGEMENT=MANUAL, make your changes in the primary and standby environment, then set STANDBY_FILE_MANAGEMENT=AUTO again.
No comments:
Post a Comment