对于有些系统由于Oracle的数据库名称不符合规范,在不想重新创建数据库的情况下使用Oracle自带的nid工具修改数据库名称。在整个修改过程中,对控制文件和数据文件进行修改,且重置dbid,建议在修改前对数据进行备份。
在本例子中,我们将数据库中的instance_name和db_name从“oratest”修改为“test”,数据库版本为10.2.0.3。
查看数据库信息。
SQL> show parameter instance_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
stance_name string ORATEST
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string ORATEST
SQL>show parameter service_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string ORATEST
SQL>select dbid from v$database;
DBID
----------
3213300265
修改前数据库名为"oratest",dbid="3213300265"。接下来修改数据库名称,先停止数据库,然后启动到mount状态下,使用nid工具修改dbname。
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
Total System Global Area 268435456 bytes
Fixed Size 1261272 bytes
Variable Size 117440808 bytes
Database Buffers 146800640 bytes
Redo Buffers 2932736 bytes
Database mounted.
SQL> host;
$ nid target="sys/password" dbname=test
DBNEWID: Release 10.2.0.3.0 - Production on Wed Feb 4 11:12:36 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to database ORATEST (DBID=3213300265)
Connected to server version 10.2.0
Control Files in database:
/u02/oradata/test/control01.ctl
/u02/oradata/test/control02.ctl
/u02/oradata/test/control03.ctl
Change database ID and database name ORATEST to TEST? (Y/[N]) => y
Proceeding with operation
Changing database ID from 3213300265 to 1976074532
Changing database name from ORATEST to TEST
Control File /u02/oradata/test/control01.ctl - modified
Control File /u02/oradata/test/control02.ctl - modified
Control File /u02/oradata/test/control03.ctl - modified
Datafile /u02/oradata/test/system01.dbf - dbid changed, wrote new name
Datafile /u02/oradata/test/undotbs01.dbf - dbid changed, wrote new name
Datafile /u02/oradata/test/sysaux01.dbf - dbid changed, wrote new name
Datafile /u02/oradata/test/users01.dbf - dbid changed, wrote new name
Datafile /u02/oradata/test/example01.dbf - dbid changed, wrote new name
Datafile /u02/oradata/test/temp01.dbf - dbid changed, wrote new name
Control File /u02/oradata/test/control01.ctl - dbid changed, wrote new name
Control File /u02/oradata/test/control02.ctl - dbid changed, wrote new name
Control File /u02/oradata/test/control03.ctl - dbid changed, wrote new name
NID-00600: Internal Error - [28] [12152] [0] [0]
Change of database name and ID failed during validation - database is intact.
DBNEWID - Completed with validation errors.
由于在修改过程中命令挂死,但查看输出信息,确定了控制文件和数据文件中dbid都已修改,就强行结束了instance,输出上面报错信息。
修改参数文件中相关信息,启动数据库。
SQL> startup mount
ORACLE instance started.
Total System Global Area 268435456 bytes
Fixed Size 1261272 bytes
Variable Size 121635112 bytes
Database Buffers 142606336 bytes
Redo Buffers 2932736 bytes
ORA-01103: database name 'TEST' in control file is not 'ORATEST'
SQL> alter system set db_name=test scope=spfile;
System altered.
SQL>
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 268435456 bytes
Fixed Size 1261272 bytes
Variable Size 125829416 bytes
Database Buffers 138412032 bytes
Redo Buffers 2932736 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string TEST
SQL> show parameter instance_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string ORATEST
SQL> show parameter service_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string TEST
SQL> alter database open resetlogs ;
Database altered.
SQL>
SQL> select dbid from v$database;
DBID
----------
1976074532
SQL>
至此,数据库称成功修改,由于dbid发生了变化,数据库已不能使用以前的备份和归档日志,应立即备份数据库