How to recreate the control file and rename the database and the datafiles

To change a database name or file names, it can be practical to recreate the control file, and in some cases your only option.

In this example, I will rename a database and its file structure from "OID11UI" to "OID11U1".

Start by generating a file which holds the current layout of all the database's files:

SQL> alter database backup controlfile to trace as '/u01/oracle/product/11204/dbs/cntr_trace.sql' resetlogs;

Database altered.

The generated file typically look like this:

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "OID11UI" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u02/oradata/OID11UI/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u02/oradata/OID11UI/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u02/oradata/OID11UI/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u02/oradata/OID11UI/system01.dbf',
  '/u02/oradata/OID11UI/sysaux01.dbf',
  '/u02/oradata/OID11UI/undotbs01.dbf',
  '/u02/oradata/OID11UI/users01.dbf'
CHARACTER SET WE8MSWIN1252
;
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/OID11UI/temp01.dbf'
     SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
-- End of tempfile additions.

Exchange the word "REUSE" against the word "SET":

CREATE CONTROLFILE SET DATABASE "OID11U1"

Optionally, on the first line, add a pointer to a correct parameter file:

STARTUP NOMOUNT PFILE='/u01/oracle/product/11204/dbs/initOID11U1.ora'

Change all references to "OID11UI" (the old name):

STARTUP NOMOUNT PFILE='/u01/oracle/product/11204/dbs/initOID11U1.ora'
CREATE CONTROLFILE SET DATABASE "OID11U1" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u02/oradata/OID11U1/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u02/oradata/OID11U1/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u02/oradata/OID11U1/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u02/oradata/OID11U1/system01.dbf',
  '/u02/oradata/OID11U1/sysaux01.dbf',
  '/u02/oradata/OID11U1/undotbs01.dbf',
  '/u02/oradata/OID11U1/users01.dbf'
CHARACTER SET WE8MSWIN1252
;
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/OID11U1/temp01.dbf'
     SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
-- End of tempfile additions.

Change the folder name that oracle uses:

usu0oid01:OID11UI>cd /u02/oradata
usu0oid01:OID11UI>mv OID11UI OID11U1

Change the control_files directive in the parameter file:
Before

*.control_files='/u02/oradata/OID11UI/control01.ctl','/u02/oradata/OID11UI/control02.ctl'

After:

*.control_files='/u02/oradata/OID11U1/control01.ctl','/u02/oradata/OID11U1/control02.ctl'

Remove the old controlfiles physically from disk:

cd /u02/oradata/OID11U1
rm *.ctl

Shutdown the database:

SQL> shutdown abort
ORACLE instance shut down.

Change the oracle profile in the operating system:

cd
vi .profile

Before:

export ORACLE_SID=OID11UI

After:

export ORACLE_SID=OID11U1

Source the new profile, and check that the ORACLE_SID environmental variable is correctly set:

. .profile
usu0oid01:OID11U1>echo $ORACLE_SID
OID11U1

Start sqlplus and run script:

usu0oid01:OID11UI>sqlplus / as sysdba @cntr_trace.sql

SQL*Plus: Release 11.2.0.4.0 Production on Wed May 28 14:00:50 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

ORACLE instance started.

Total System Global Area 1720328192 bytes
Fixed Size                  2247072 bytes
Variable Size            1107297888 bytes
Database Buffers          603979776 bytes
Redo Buffers                6803456 bytes

Control file created.

Database altered.

Tablespace altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

Check the location of the data files after the change:

SQL> select file_name from dba_data_files union select member from v$logfile  union  select name from v$controlfile;

FILE_NAME
--------------------------------------------------
/u02/oradata/OID11U1/control01.ctl
/u02/oradata/OID11U1/control02.ctl
/u02/oradata/OID11U1/redo01.log
/u02/oradata/OID11U1/redo02.log
/u02/oradata/OID11U1/redo03.log
/u02/oradata/OID11U1/sysaux01.dbf
/u02/oradata/OID11U1/system01.dbf
/u02/oradata/OID11U1/undotbs01.dbf
/u02/oradata/OID11U1/users01.dbf

Lag en spfile (anbefales):

SQL> create spfile from pfile; 

File created.

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

Startup the database for normal use:

SQL> startup