利用DBNEWID工具来修改Oracle数据库名

教程发布:风哥 教程分类:ITPUX技术网 更新日期:2022-02-12 浏览学习:41

利用DBNEWID工具来修改dbid和dbname,也可以只修改dbname

我们先看下dbnewid的帮助
C:\Documents and Settings\test>nid help=y
DBNEWID: Release 9.2.0.8.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
Keyword Description (Default)
----------------------------------------------------
TARGET Username/Password (NONE)
DBNAME New database name (NONE)
LOGFILE Output Log (NONE)
REVERT Revert failed change NO
SETNAME Set a new database name only NO
APPEND Append to output log NO
HELP Displays these messages NO

实验一:修改dbid和dbname
以下是操作步骤 首先数据库要处于mount状态,下面将数据库名TEST9I改为TEST,并且dbid也同时修改
C:\Documents and Settings\test>sqlplus "/as sysdba"
SQL*Plus: Release 9.2.0.8.0 - Production on 星期五 4月 24 09:40:02 2009
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

连接到:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
SQL> select dbid,name from v$database;
DBID NAME
---------- ---------
2422073917 TEST9I
SQL> select global_name from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
TEST9I.TEST

SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。

SQL> startup mount
ORACLE 例程已经启动。
Total System Global Area 135339604 bytes
Fixed Size 454228 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
数据库装载完毕。

SQL> host
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.

C:\Documents and Settings\test>nid target=/ dbname=test
DBNEWID: Release 9.2.0.8.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
Connected to database TEST9I (DBID=2422073917)
Control Files in database:
D:\ORACLE\ORADATA\TEST\CONTROL01.CTL
D:\ORACLE\ORADATA\TEST\CONTROL02.CTL
D:\ORACLE\ORADATA\TEST\CONTROL03.CTL
Change database ID and database name TEST9I to TEST? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 2422073917 to 1983160527
Changing database name from TEST9I to TEST
Control File D:\ORACLE\ORADATA\TEST\CONTROL01.CTL - modified
Control File D:\ORACLE\ORADATA\TEST\CONTROL02.CTL - modified
Control File D:\ORACLE\ORADATA\TEST\CONTROL03.CTL - modified
Datafile D:\ORACLE\ORADATA\TEST\SYSTEM01.DBF - dbid changed, wrote new name
Datafile D:\ORACLE\ORADATA\TEST\UNDOTBS01.DBF - dbid changed, wrote new name
Datafile D:\ORACLE\ORADATA\TEST\EXAMPLE01.DBF - dbid changed, wrote new name
Datafile D:\ORACLE\ORADATA\TEST\INDX01.DBF - dbid changed, wrote new name
Datafile D:\ORACLE\ORADATA\TEST\TOOLS01.DBF - dbid changed, wrote new name
Datafile D:\ORACLE\ORADATA\TEST\USERS01.DBF - dbid changed, wrote new name
Datafile D:\ORACLE\ORADATA\TEST\TEMP01.DBF - dbid changed, wrote new name
Control File D:\ORACLE\ORADATA\TEST\CONTROL01.CTL - dbid changed, wrote new
name
Control File D:\ORACLE\ORADATA\TEST\CONTROL02.CTL - dbid changed, wrote new
name
Control File D:\ORACLE\ORADATA\TEST\CONTROL03.CTL - dbid changed, wrote new
name
Database name changed to TEST.
Modify parameter file and generate a new password file before restarting.
Database ID for database TEST changed to 1983160527.
All previous backups and archived redo logs for this database are unusable.
Shut down database and open with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

修改db_name参数需要把数据库关闭再启动到nomount才可以修改
C:\Documents and Settings\test>exit
SQL> alter system set db_name='test' scope=spfile;
alter system set db_name='test' scope=spfile
*
ERROR 位于第 1 行:
ORA-32017: failure in updating SPFILE
ORA-32016: parameter "db_name" cannot be updated in SPFILE

SQL> exit
从Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production中断开

要重新建立密码文件,要不然会报ORA-01991错误 --注意这个密码文件是按SID来命名的
C:\Documents and Settings\test>
orapwd file=d:\oracle\ora92\database\PWDtest9i.ORA password=abcdefg entries=10

C:\Documents and Settings\paulyi>sqlplus "/as sysdba"
SQL*Plus: Release 9.2.0.8.0 - Production on 星期五 4月 24 09:43:32 2009
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

连接到:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> shutdown immediate
ORA-01109: 数据库未打开

已经卸载数据库。
ORACLE 例程已经关闭。

SQL> startup nomount
ORACLE 例程已经启动。
Total System Global Area 135339604 bytes
Fixed Size 454228 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes

SQL> alter system set db_name='test' scope=spfile;
系统已更改。
SQL> shutdown immediate;
ORA-01507: ??????
ORACLE 例程已经关闭。

SQL> startup mount;
ORACLE 例程已经启动。
Total System Global Area 135339604 bytes
Fixed Size 454228 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
数据库装载完毕。
用open resetlogs 选项打开数据库

SQL> alter database open resetlogs;
数据库已更改。
下面看到dbid和dbname已经更改成功
SQL> select dbid,name from v$database;
DBID NAME
---------- ---------
1983160527 TEST

SQL> alter database rename global_name to TEST.TEST;
数据库已更改。
SQL>
SQL> select global_name from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
TEST.TEST

实验二:只修改dbname 将TEST9I改为TEST,不需要用open resetlogs方式打开,因为没有修改dbid
以下是操作步骤 首先数据库要处于mount状态,下面将数据库名TEST9I改为TEST,并且dbid不修改
C:\Documents and Settings\test>sqlplus "/as sysdba"
SQL*Plus: Release 9.2.0.8.0 - Production on 星期五 4月 24 10:10:42 2009
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

连接到:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> select dbid,name from v$database;
DBID NAME
---------- ---------
1983160527 TEST9I

SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。

SQL> startup mount
ORACLE 例程已经启动。
Total System Global Area 135339604 bytes
Fixed Size 454228 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
数据库装载完毕。

SQL> host
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.
nid参数加上setname=y 选项

C:\Documents and Settings\test>nid target=/ dbname=test setname=y
DBNEWID: Release 9.2.0.8.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
Connected to database TEST9I (DBID=1983160527)
Control Files in database:
D:\ORACLE\ORADATA\TEST\CONTROL01.CTL
D:\ORACLE\ORADATA\TEST\CONTROL02.CTL
D:\ORACLE\ORADATA\TEST\CONTROL03.CTL
Change database name of database TEST9I to TEST? (Y/[N]) => Y
Proceeding with operation
Changing database name from TEST9I to TEST
Control File D:\ORACLE\ORADATA\TEST\CONTROL01.CTL - modified
Control File D:\ORACLE\ORADATA\TEST\CONTROL02.CTL - modified
Control File D:\ORACLE\ORADATA\TEST\CONTROL03.CTL - modified
Datafile D:\ORACLE\ORADATA\TEST\SYSTEM01.DBF - wrote new name
Datafile D:\ORACLE\ORADATA\TEST\UNDOTBS01.DBF - wrote new name
Datafile D:\ORACLE\ORADATA\TEST\EXAMPLE01.DBF - wrote new name
Datafile D:\ORACLE\ORADATA\TEST\INDX01.DBF - wrote new name
Datafile D:\ORACLE\ORADATA\TEST\TOOLS01.DBF - wrote new name
Datafile D:\ORACLE\ORADATA\TEST\USERS01.DBF - wrote new name
Datafile D:\ORACLE\ORADATA\TEST\TEMP01.DBF - wrote new name
Control File D:\ORACLE\ORADATA\TEST\CONTROL01.CTL - wrote new name
Control File D:\ORACLE\ORADATA\TEST\CONTROL02.CTL - wrote new name
Control File D:\ORACLE\ORADATA\TEST\CONTROL03.CTL - wrote new name
Database name changed to TEST.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.

C:\Documents and Settings\test>orapwd file=D:\oracle\ora92\DATABASE\PWDtest.OR
A password=abcdefg entries=10
C:\Documents and Settings\paulyi>exit

SQL> shutdown immediate;
ORA-01109: 数据库未打开

已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup nomount
ORACLE 例程已经启动。
Total System Global Area 135339604 bytes
Fixed Size 454228 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes

SQL> alter system set db_name='test' scope=spfile;
系统已更改。

SQL> shutdown immediate
ORA-01507: ??????

ORACLE 例程已经关闭。
SQL> startup;
ORACLE 例程已经启动。
Total System Global Area 135339604 bytes
Fixed Size 454228 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
数据库装载完毕。
数据库已经打开。
修改成功,只修改dbname,没修改dbid

SQL> select dbid,name from v$database;
DBID NAME
---------- ---------
1983160527 TEST
SQL> show parameter db_name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string test
SQL> alter database rename global_name to TEST.TEST;
数据库已更改。
SQL> select global_name from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
TEST.TEST
SQL>

本文标签:
网站声明:本文由风哥整理发布,转载请保留此段声明,本站所有内容将不对其使用后果做任何承诺,请读者谨慎使用!
【上一篇】
【下一篇】