Use Nid to Change dbname

原文链接:

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

Nid是Oracle从9iR2开始提供的工具，可以用来更改数据库名称，而无需通过之前重建控制文件等繁琐方式.

需要说明的是，虽然这个工具来自9iR2，但是仍然可以被用于Oracle8i.

先看一下帮助:

C:\>nid -help

DBNEWID: Release 10.1.0.2.0 - Production

关键字说明 (默认值)

----------------------------------------------------

TARGET 用户名/口令 (无)

DBNAME 新的数据库名 (无)

LOGFILE 输出日志 (无)

REVERT 还原失败的更改否

SETNAME 仅设置新的数据库名否

APPEND 附加至输出日志否

HELP 显示这些消息否

我们通过范例来看一下用法:

1．数据库当前设置

SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

PL/SQL Release 9.2.0.1.0 - Production

CORE 9.2.0.1.0 Production

TNS for 32-bit Windows: Version 9.2.0.1.0 - Production

NLSRTL Version 9.2.0.1.0 - Production

SQL> show parameter name

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_file_name_convert string

db_name string eyglev

global_names boolean FALSE

instance_name string eyglev

lock_name_space string

log_file_name_convert string

oracle_trace_collection_name string

oracle_trace_facility_name string oracled

plsql_native_make_file_name string

service_names string eyglev

2．Shutdown数据库

SQL> connect sys/orasys as sysdba

Connected.

SQL> shutdown immediate

Database 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> host

Microsoft Windows 2000 [Version 5.00.2195]

C:\>nid target=sys/orasys dbname=eyglen

DBNEWID: Release 9.2.0.1.0 - Production

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.CTL

Change database ID and database name EYGLEV to EYGLEN? (Y/[N]) => Y

Proceeding with operation

Changing database ID from 677189177 to 3955758099

Changing 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 name

Database 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.ora

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

ORA-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:\>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 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.

SQL> alter database open resetlogs

2 /

Database altered.

SQL>

11．修改后的参数

SQL> show parameter name

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_file_name_convert string

db_name string eyglen

global_names boolean FALSE

instance_name string eyglen

lock_name_space string

log_file_name_convert string

oracle_trace_collection_name string

oracle_trace_facility_name string oracled

plsql_native_make_file_name string

service_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 name

NAME TYPE VALUE

----------------------------------- ------- ------------------------------

db_file_name_convert 字符串

db_name 字符串 vilen

global_names 布尔值 TRUE

instance_name 字符串 vilen

lock_name_space 字符串

log_file_name_convert 字符串

oracle_trace_collection_name 字符串

oracle_trace_facility_name 字符串 oracled

service_names 字符串 vilen

SVRMGR>

2．修改

C:\>nid target=sys/orasys@vilen dbname=vilene

DBNEWID: Release 9.2.0.1.0 - Production

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.CTL

Change database ID and database name VILEN to VILENE? (Y/[N]) => Y

Proceeding with operation

Changing database ID from 1535443189 to 681857412

Changing 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 name

Database 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 mount

ORACLE instance started.

Total System Global Area 61970460 bytes

Fixed Size 75804 bytes

Variable Size 17645568 bytes

Database Buffers 44171264 bytes

Redo Buffers 77824 bytes

Database mounted.

7．打开数据库

SVRMGR> alter database open resetlogs

2> /

Statement processed.

8．修改后的参数

SVRMGR> show parameter name

NAME TYPE VALUE

----------------------------------- ------- ------------------------------

db_file_name_convert string

db_name string vilene

global_names boolean TRUE

instance_name string vilene

lock_name_space string

log_file_name_convert string

oracle_trace_collection_name string

oracle_trace_facility_name string oracled

service_names string vilen