ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 利用DBNEWID工具来修改DBNAME

利用DBNEWID工具来修改DBNAME

原创 Linux操作系统 作者:paulyibinyi 时间:2009-04-24 10:19:28 0 删除 编辑

   利用DBNEWID工具来修改dbid和dbname,也可以只修改dbname
   我们先看下dbnewid的帮助
   C:\Documents and Settings\paulyi>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\paulyi>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.HNISI.COM.CN

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\paulyi>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\paulyi>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\paulyi>orapwd file=d:\oracle\ora92\database\PWDtest9i.OR
A 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.HNISI.COM.CN
  2  ;

数据库已更改。

SQL>

SQL> select global_name from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------

TEST.HNISI.COM.CN

实验二:只修改dbname 将TEST9I改为TEST,不需要用open resetlogs方式打开,因为没有修改dbid
以下是操作步骤 首先数据库要处于mount状态,下面将数据库名TEST9I改为TEST,并且dbid不修改
C:\Documents and Settings\paulyi>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\paulyi>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\paulyi>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.HNISI.COM.CN;

数据库已更改。

SQL> select global_name from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------

TEST.HNISI.COM.CN

SQL>

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7199859/viewspace-591684/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
学习数据库

注册时间:2007-12-11

  • 博文量
    902
  • 访问量
    6550734