更改Oracle資料庫的SID

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

[@more@]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) 版權所有 1985-2000 Microsoft Corp.

C:>nid target=sys/orasys dbname=eyglen
DBNEWID: Release 9.2.0.1.0 – Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
Connected to database EYGLEV (DBID=677189177)
Control Files in database:
E:ORACLEORADATAEYGLENCONTROL01.CTL
E:ORACLEORADATAEYGLENCONTROL02.CTL
E:ORACLEORADATAEYGLENCONTROL03.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:ORACLEORADATAEYGLENCONTROL01.CTL – modified
Control File E:ORACLEORADATAEYGLENCONTROL02.CTL – modified
Control File E:ORACLEORADATAEYGLENCONTROL03.CTL – modified
Datafile E:ORACLEORADATAEYGLENSYSTEM01.DBF - dbid changed, ...
Datafile E:ORACLEORADATAEYGLENUNDOTBS01.DBF - dbid changed, ...
Datafile E:ORACLEORADATAEYGLENCWMLITE01.DBF - dbid changed, ...
Datafile E:ORACLEORADATAEYGLENDRSYS01.DBF - dbid changed, ...
Datafile E:ORACLEORADATAEYGLENINDX01.DBF - dbid changed, ...
Datafile E:ORACLEORADATAEYGLENODM01.DBF - dbid changed, ...
Datafile E:ORACLEORADATAEYGLENTOOLS01.DBF - dbid changed, ...
Datafile E:ORACLEORADATAEYGLENUSERS01.DBF - dbid changed, ...
Datafile E:ORACLEORADATAEYGLENXDB01.DBF - dbid changed, ...
Datafile E:ORACLEORADATAEYGLENEYGLE.DBF - dbid changed, ...
Datafile E:ORACLEORADATAEYGLENTEMP01.DBF - dbid changed, ...
Control File E:ORACLEORADATAEYGLENCONTROL01.CTL - dbid changed, ...
Control File E:ORACLEORADATAEYGLENCONTROL02.CTL - dbid changed, ...
Control File E:ORACLEORADATAEYGLENCONTROL03.CTL - dbid changed, ...
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 ...
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.ora)

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

7．重建spfile檔案

如果你沒有使用spfile，當然無需重建，跳至8
SQL> startup pfile=E:Oracleadmineyglenpfileinit.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...PWDeyglen.ORA'

SQL> CREATE SPFILE='E:OracleOra9iR2databaseSPFILEEYGLEN.ORA' FROM
2 PFILE='E:Oracleadmineyglenpfileinit.ora';

File created.

8．重建口令檔案

SQL> host
Microsoft Windows 2000 [Version 5.00.2195]
(C) 版權所有 1985-2000 Microsoft Corp.

C:>orapwd file=E:Oracle...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.

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．對資料庫做個全備份