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
