Use Nid to Change dbname

javayestome

原文链接:

http://www.eygle.com/faq/Use.Nid.to.Change.Your.dbname.htm


Nid是Oracle从9iR2开始提供的工具，可以用来更改数据库名称，而无需通过之前重建控制文件等繁琐方式.
需要说明的是，虽然这个工具来自9iR2，但是仍然可以被用于Oracle8i.
<div class="left style25">先看一下帮助:

C:\>nid -helpDBNEWID: Release 10.1.0.2.0 - ProductionCopyright (c) 2001, 2004, Oracle.  All rights reserved.关键字      说明                    (默认值)----------------------------------------------------TARGET      用户名/口令              (无)DBNAME      新的数据库名             (无)LOGFILE     输出日志                     (无)REVERT      还原失败的更改            否SETNAME     仅设置新的数据库名        否APPEND      附加至输出日志            否HELP        显示这些消息              否

我们通过范例来看一下用法:
1．数据库当前设置

SQL> select * from v$version;BANNER----------------------------------------------------------------Oracle9i Enterprise Edition Release 9.2.0.1.0 - ProductionPL/SQL Release 9.2.0.1.0 - ProductionCORE    9.2.0.1.0       ProductionTNS for 32-bit Windows: Version 9.2.0.1.0 - ProductionNLSRTL Version 9.2.0.1.0 - ProductionSQL> show parameter nameNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------db_file_name_convert                 stringdb_name                              string      eyglevglobal_names                         boolean     FALSEinstance_name                        string      eyglevlock_name_space                      stringlog_file_name_convert                stringoracle_trace_collection_name         stringoracle_trace_facility_name           string      oracledplsql_native_make_file_name          stringservice_names                        string      eyglev

2．Shutdown数据库

SQL> connect sys/orasys as sysdbaConnected.SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.

3．Startup mount

SQL> startup mount ORACLE instance started. Total System Global Area 135338868 bytes Fixed Size 453492 bytes Variable Size 109051904 bytes Database Buffers 25165824 bytes Redo Buffers 667648 bytes Database mounted.

4. 使用NID更改

SQL> hostMicrosoft Windows 2000 [Version 5.00.2195](C) 版权所有 1985-2000 Microsoft Corp.C:\>nid target=sys/orasys dbname=eyglenDBNEWID: Release 9.2.0.1.0 - ProductionCopyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.Connected to database EYGLEV (DBID=677189177)Control Files in database:    E:\ORACLE\ORADATA\EYGLEN\CONTROL01.CTL    E:\ORACLE\ORADATA\EYGLEN\CONTROL02.CTL    E:\ORACLE\ORADATA\EYGLEN\CONTROL03.CTLChange database ID and database name EYGLEV to EYGLEN? (Y/[N]) => YProceeding with operationChanging database ID from 677189177 to 3955758099Changing database name from EYGLEV to EYGLEN    Control File E:\ORACLE\ORADATA\EYGLEN\CONTROL01.CTL - modified    Control File E:\ORACLE\ORADATA\EYGLEN\CONTROL02.CTL - modified    Control File E:\ORACLE\ORADATA\EYGLEN\CONTROL03.CTL - modified    Datafile E:\ORACLE\ORADATA\EYGLEN\SYSTEM01.DBF - dbid changed, wrote new name    Datafile E:\ORACLE\ORADATA\EYGLEN\UNDOTBS01.DBF - dbid changed, wrote new name    Datafile E:\ORACLE\ORADATA\EYGLEN\CWMLITE01.DBF - dbid changed, wrote new name    Datafile E:\ORACLE\ORADATA\EYGLEN\DRSYS01.DBF - dbid changed, wrote new name    Datafile E:\ORACLE\ORADATA\EYGLEN\INDX01.DBF - dbid changed, wrote new name    Datafile E:\ORACLE\ORADATA\EYGLEN\ODM01.DBF - dbid changed, wrote new name    Datafile E:\ORACLE\ORADATA\EYGLEN\TOOLS01.DBF - dbid changed, wrote new name    Datafile E:\ORACLE\ORADATA\EYGLEN\USERS01.DBF - dbid changed, wrote new name    Datafile E:\ORACLE\ORADATA\EYGLEN\XDB01.DBF - dbid changed, wrote new name    Datafile E:\ORACLE\ORADATA\EYGLEN\EYGLE.DBF - dbid changed, wrote new name    Datafile E:\ORACLE\ORADATA\EYGLEN\TEMP01.DBF - dbid changed, wrote new name    Control File E:\ORACLE\ORADATA\EYGLEN\CONTROL01.CTL - dbid changed, wrote new name    Control File E:\ORACLE\ORADATA\EYGLEN\CONTROL02.CTL - dbid changed, wrote new name    Control File E:\ORACLE\ORADATA\EYGLEN\CONTROL03.CTL - dbid changed, wrote new nameDatabase name changed to EYGLEN.Modify parameter file and generate a new password file before restarting.Database ID for database EYGLEN changed to 3955758099.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.

5．Shutdown database

SQL> shutdown immediate ORA-01109: database not open Database dismounted. ORACLE instance shut down.

6．修改初始化参数文件、spfile文件（init.ora/spfile）

########################################### instance_name=eyglen #instance_name=eyglev ########################################### db_domain="" db_name=eyglen # db_name=eyglev ###########################################

7．重建spfile文件
如果你没有使用spfile，当然无需重建，跳至8

SQL> startup pfile=E:\Oracle\admin\eyglen\pfile\init.oraORACLE instance started.Total System Global Area  135338868 bytesFixed Size                   453492 bytesVariable Size             109051904 bytesDatabase Buffers           25165824 bytesRedo Buffers                 667648 bytesORA-01991: invalid password file 'e:\oracle\Ora9iR2\DATABASE\PWDeyglen.ORA'SQL> CREATE SPFILE='E:\Oracle\Ora9iR2\database\SPFILEEYGLEN.ORA' FROM  2  PFILE='E:\Oracle\admin\eyglen\pfile\init.ora';File created.

8．重建口令文件

SQL> host Microsoft Windows 2000 [Version 5.00.2195] (C) 版权所有 1985-2000 Microsoft Corp. C:\>orapwd file=E:\Oracle\Ora9iR2\database\PWDeyglen.ORA password=oracle entries=5

9.shutdown数据库
如果不使用spfile,则可以跳至10

SQL> shutdown immediate ORA-01109: database not open Database dismounted. ORACLE instance shut down.

10．Startup mount,resetlogs打开

SQL> startup mountORACLE instance started.Total System Global Area  135338868 bytesFixed Size                   453492 bytesVariable Size             109051904 bytesDatabase Buffers           25165824 bytesRedo Buffers                 667648 bytesDatabase mounted.SQL> alter database open resetlogs  2  /Database altered.SQL>

11．修改后的参数

SQL> show parameter nameNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------db_file_name_convert                 stringdb_name                              string      eyglenglobal_names                         boolean     FALSEinstance_name                        string      eyglenlock_name_space                      stringlog_file_name_convert                stringoracle_trace_collection_name         stringoracle_trace_facility_name           string      oracledplsql_native_make_file_name          stringservice_names                        string      eyglen

12．对数据库做个全备份
附：使用nid更改817的数据库

1． 更改前

SVRMGR> startup mount已启动 ORACLE 实例。系统全局区域合计有                         61970460个字节Fixed Size                                          75804个字节Variable Size                                    17645568个字节Database Buffers                                 44171264个字节Redo Buffers                                        77824个字节已装入数据库。SVRMGR> show parameter nameNAME                                TYPE    VALUE----------------------------------- ------- ------------------------------db_file_name_convert                字符串db_name                             字符串  vilenglobal_names                        布尔值  TRUEinstance_name                       字符串  vilenlock_name_space                     字符串log_file_name_convert               字符串oracle_trace_collection_name        字符串oracle_trace_facility_name          字符串  oracledservice_names                       字符串  vilenSVRMGR>

2． 修改

C:\>nid target=sys/orasys@vilen dbname=vileneDBNEWID: Release 9.2.0.1.0 - ProductionCopyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.Connected to database VILEN (DBID=1535443189)Control Files in database:    C:\ORACLE\ORADATA\VILEN\CONTROL01.CTL    C:\ORACLE\ORADATA\VILEN\CONTROL02.CTL    C:\ORACLE\ORADATA\VILEN\CONTROL03.CTLChange database ID and database name VILEN to VILENE? (Y/[N]) => YProceeding with operationChanging database ID from 1535443189 to 681857412Changing database name from VILEN to VILENE    Control File C:\ORACLE\ORADATA\VILEN\CONTROL01.CTL - modified    Control File C:\ORACLE\ORADATA\VILEN\CONTROL02.CTL - modified    Control File C:\ORACLE\ORADATA\VILEN\CONTROL03.CTL - modified    Datafile C:\ORACLE\ORADATA\VILEN\SYSTEM01.DBF - dbid changed, wrote new name    Datafile C:\ORACLE\ORADATA\VILEN\RBS01.DBF - dbid changed, wrote new name    Datafile C:\ORACLE\ORADATA\VILEN\USERS01.DBF - dbid changed, wrote new name    Datafile C:\ORACLE\ORADATA\VILEN\TEMP01.DBF - dbid changed, wrote new name    Datafile C:\ORACLE\ORADATA\VILEN\TOOLS01.DBF - dbid changed, wrote new name    Datafile C:\ORACLE\ORADATA\VILEN\INDX01.DBF - dbid changed, wrote new name    Datafile C:\ORACLE\ORADATA\VILEN\EQSP01.DBF - dbid changed, wrote new name    Datafile C:\ORACLE\ORADATA\VILEN\PERFSTAT.DBF - dbid changed, wrote new name    Control File C:\ORACLE\ORADATA\VILEN\CONTROL01.CTL - dbid changed, wrote new name    Control File C:\ORACLE\ORADATA\VILEN\CONTROL02.CTL - dbid changed, wrote new name    Control File C:\ORACLE\ORADATA\VILEN\CONTROL03.CTL - dbid changed, wrote new nameDatabase name changed to VILENE.Modify parameter file and generate a new password file before restarting.Database ID for database VILENE changed to 681857412.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.

3． 关闭数据库

SVRMGR> shutdown immediate ORA-01109: 数据库未打开 已卸下数据库。 已关闭 ORACLE 实例。

4． 修改参数文件

db_name = "vilene" #db_name = "vilen" instance_name = vilene #instance_name = vilen

5． 重建口令文件

C:\oracle\database>orapwd file=PWDvilen.ORA password=oracle entries=5 C:\oracle\database>

6． mount数据库

SVRMGR> startup mountORACLE instance started.Total System Global Area                         61970460 bytesFixed Size                                          75804 bytesVariable Size                                    17645568 bytesDatabase Buffers                                 44171264 bytesRedo Buffers                                        77824 bytesDatabase mounted.

7． 打开数据库

SVRMGR> alter database open resetlogs 2> / Statement processed.

8． 修改后的参数

SVRMGR> show parameter nameNAME                                TYPE    VALUE----------------------------------- ------- ------------------------------db_file_name_convert                stringdb_name                             string  vileneglobal_names                        boolean TRUEinstance_name                       string  vilenelock_name_space                     stringlog_file_name_convert               stringoracle_trace_collection_name        stringoracle_trace_facility_name          string  oracledservice_names                       string  vilen