这里记录了在Windows中修改DB_NAME的简要步骤。
我的机子上装了两个10g数据库：ORCL和CATELOG。CATELOG是用于RMAN备份的，当初安装的时候把名字输错了，今天想把这个错误纠正过来，于是开始了重命名之旅。
1. 首先创建pfile，在修改dbname后需要修改参数文件中的db_name参数。
SQL> conn /@catelog as sysdba
Connected.
SQL> create pfile from spfile;
File created.
2. Mount 数据库
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1247876 bytes
Variable Size 62915964 bytes
Database Buffers 96468992 bytes
Redo Buffers 7139328 bytes
Database mounted.
3. 用DBNEWID修改dbname
C:\Documents and Settings\Zeeno>nid target=sys/system@catelog dbname=catalog setname=yes
DBNEWID: Release 10.2.0.1.0 - Production on Sun Aug 5 15:40:59 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to database CATELOG (DBID=3534875841)
Connected to server version 10.2.0
Control Files in database:
F:\CATELOG\CATELOG\CONTROL01.CTL
F:\CATELOG\CATELOG\CONTROL02.CTL
F:\CATELOG\CATELOG\CONTROL03.CTL
Change database name of database CATELOG to CATALOG? (Y/[N]) => y
Proceeding with operation
Changing database name from CATELOG to CATALOG
Control File F:\CATELOG\CATELOG\CONTROL01.CTL - modified
Control File F:\CATELOG\CATELOG\CONTROL02.CTL - modified
Control File F:\CATELOG\CATELOG\CONTROL03.CTL - modified
Datafile F:\CATELOG\CATELOG\SYSTEM01.DBF - wrote new name
Datafile F:\CATELOG\CATELOG\UNDOTBS01.DBF - wrote new name
Datafile F:\CATELOG\CATELOG\SYSAUX01.DBF - wrote new name
Datafile F:\CATELOG\CATELOG\USERS01.DBF - wrote new name
Datafile F:\CATELOG\CATELOG\TEMP01.DBF - wrote new name
Control File F:\CATELOG\CATELOG\CONTROL01.CTL - wrote new name
Control File F:\CATELOG\CATELOG\CONTROL02.CTL - wrote new name
Control File F:\CATELOG\CATELOG\CONTROL03.CTL - wrote new name
Instance shut down
Database name changed to CATALOG.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.
4. 修改参数文件中的DB_NAME和相应的dump目录以及控制文件目录，并将参数文件改名为：initCATALOG.ora
5. 修改listener.ora和tnsnames.ora，将CATELOG修改为CATALOG
6. 修改系统服务
C:\Documents and Settings\Zeeno>sc delete oracleserviceCATELOG
[SC] DeleteService SUCCESS
C:\Documents and Settings\Zeeno>oradim -NEW -SID CATALOG -STARTMODE manual
Instance created.
7. 重新启动实例和listener
C:\Documents and Settings\Zeeno>net start oracleservicecatalog
The OracleServiceCATALOG service is starting.
The OracleServiceCATALOG service was started successfully.
C:\Documents and Settings\Zeeno>lsnrctl start
LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 05-AUG-2007 16:10:37
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Starting tnslsnr: please wait...
TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
System parameter file is D:\Programs\Oracle10g\network\admin\listener.ora
Log messages written to D:\Programs\Oracle10g\network\log\listenercatelog.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=erp-xzb)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=erp-xzb)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
Start Date 05-AUG-2007 16:10:39
Uptime 0 days 0 hr. 0 min. 1 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File D:\Programs\Oracle10g\network\admin\listener.ora
Listener Log File D:\Programs\Oracle10g\network\log\listenercatelog.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=erp-xzb)(PORT=1521)))
Services Summary...
Service "CATALOG" has 1 instance(s).
Instance "CATALOG", status UNKNOWN, has 1 handler(s) for this service...
Service "ORCL" has 1 instance(s).
Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
8. 创建spfile并启动数据库。
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Aug 5 16:12:14 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile;
File created.
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1247876 bytes
Variable Size 58721660 bytes
Database Buffers 100663296 bytes
Redo Buffers 7139328 bytes
Database mounted.
Database opened.
9. 修改数据文件路径
SQL> col file_name for a40
SQL> select file_name from dba_data_files;
FILE_NAME
----------------------------------------
F:\CATELOG\CATELOG\SYSTEM01.DBF
F:\CATELOG\CATELOG\UNDOTBS01.DBF
F:\CATELOG\CATELOG\SYSAUX01.DBF
F:\CATELOG\CATELOG\USERS01.DBF
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1247876 bytes
Variable Size 58721660 bytes
Database Buffers 100663296 bytes
Redo Buffers 7139328 bytes
Database mounted.
在操作系统中将数据文件移动到相应目录下。
SQL> alter database rename file 'F:\CATELOG\CATELOG\SYSTEM01.DBF'
2 to 'F:\CATALOG\ORADATA\SYSTEM01.DBF';
Database altered.
SQL> alter database rename file 'F:\CATELOG\CATELOG\UNDOTBS01.DBF'
2 to 'F:\CATALOG\ORADATA\UNDOTBS01.DBF';
Database altered.
SQL> alter database rename file 'F:\CATELOG\CATELOG\SYSAUX01.DBF'
2 to 'F:\CATALOG\ORADATA\SYSAUX01.DBF';
Database altered.
SQL> alter database rename file 'F:\CATELOG\CATELOG\USERS01.DBF'
2 to 'F:\CATALOG\ORADATA\USERS01.DBF';
Database altered.
修改REDO文件
SQL> alter database
2 rename file 'F:\CATELOG\CATELOG\REDO01.LOG' to 'F:\CATALOG\RDO\REDO01.LOG';
Database altered.
SQL> alter database
2 rename file 'F:\CATELOG\CATELOG\REDO02.LOG' to 'F:\CATALOG\RDO\REDO02.LOG';
Database altered.
SQL> alter database
2 rename file 'F:\CATELOG\CATELOG\REDO03.LOG' to 'F:\CATALOG\RDO\REDO03.LOG';
Database altered.
SQL> alter database open;
Database altered.
SQL> select file_name from dba_data_files;
FILE_NAME
----------------------------------------
F:\CATALOG\ORADATA\SYSTEM01.DBF
F:\CATALOG\ORADATA\UNDOTBS01.DBF
F:\CATALOG\ORADATA\SYSAUX01.DBF
F:\CATALOG\ORADATA\USERS01.DBF
10. 接着修改临时表空间的数据文件目录。不过这个可以联机完成。
SQL> select file_name from dba_temp_files;
FILE_NAME
----------------------------------------
F:\CATELOG\CATELOG\TEMP01.DBF
SQL> create temporary tablespace temp02
2 tempfile 'F:\CATALOG\ORADATA\TEMP01.DBF'
3 size 50M
4 extent management local uniform size 16M;
Tablespace created.
SQL> alter database default temporary tablespace temp02;
Database altered.
SQL> alter database tempfile 'F:\CATELOG\CATELOG\TEMP01.DBF' drop
2 including datafiles;
Database altered.
如果参数GLOBAL_NAME是TRUE，则必须修改GLOBAL_NAME。否则，不改也无所谓。
SQL> alter database rename global_name to CATALOG;
Database altered.
OK，重命名之旅到此结束了。简单测试了一把，还没有发现任何后遗症。不过，花费的时间用来直接创建新的数据是不是更合算？突然又想到，如何升级CATALOG数据库？下次再玩一把。
