使用NID修改Oracle資料庫名

 

1        登陸資料庫把資料庫變成mount 狀態

C:\>sqlplus /nolog

 

SQL*Plus: Release 9.2.0.1.0 - Production on 星期一 4 月 17 15:10:42 2006

 

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

 

SQL> conn /as sysdba

已連線。

NAME                                 TYPE        VALUE

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

db_file_name_convert                 string

db_name                              string      drew

global_names                         boolean     FALSE

instance_name                        string      drew

 

SQL> shutdown immediate;

資料庫已經關閉。

已經解除安裝資料庫。

ORACLE 例程已經關閉。

SQL> startup mount

ORACLE 例程已經啟動。

 

Total System Global Area  135338868 bytes

Fixed Size                   453492 bytes

Variable Size             109051904 bytes

Database Buffers           25165824 bytes

Redo Buffers                 667648 bytes

資料庫裝載完畢。

SQL>

 

2        使用NID 命令來修改資料庫instanc_name 和dbname

 

 C:\>nid target=sys/oracle@drew dbname= kbdv2

DBNEWID: Release 9.2.0.1.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

 

Connected to database DREW (DBID=2198138346)

 

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 DREW to KBDV2? (Y/[N]) => Y

 

Proceeding with operation

Changing database ID from 2198138346 to 1048877256

Changing database name from DREW to KBDV2

    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\DRSYS01.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\XDB01.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 KBDV2.

Modify parameter file and generate a new password file before restarting.

Database ID for database KBDV2 changed to 1048877256.

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.

 

SQL> shutdown immediate;

ORA-01109: 資料庫未開啟

 

 

已經解除安裝資料庫。

ORACLE 例程已經關閉。

3        修改建立引數檔案(pfile/Spfile)   

 instance_name= kbdv2

 

###########################################

# Miscellaneous

###########################################

compatible=9.2.0.0.0

 

###########################################

# Security and Auditing

###########################################

remote_login_passwordfile=EXCLUSIVE

 

###########################################

# Sort, Hash Joins, Bitmap Indexes

###########################################

pga_aggregate_target=25165824

sort_area_size=524288

 

###########################################

# Database Identification

###########################################

db_domain=""

db_name= kbdv2

 

SQL> CREATE SPFILE FROM PFILE='D:\oracle\admin\test\pfile\init.ora';

 

檔案已建立。

 

4        建立修改口令檔案[ 區別windows 系統和unix （linux ）系統]

本人操作是在windows 系統下的，所以先修改物理服務名C:\>oradim -edit -sid drew -newsid kbdv2

並且修改登錄檔 HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0\ORACLE_SID

 

如果是unix 系統，就在oracle 使用者下修改.profile 裡的oracle_sid

建立口令檔案

orapwd file=D:\oracle\ora92\database\PWDkbdv2.ora password=oracle entries=5

修改listener.ora 和tnsname.ora 檔案

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

      )

      (ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))

      )

    )

  )

 

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = d:\oracle\ora92)

      (PROGRAM = extproc)

    )

    (SID_DESC =

      (GLOBAL_DBNAME = kbdv2)

      (ORACLE_HOME = d:\oracle\ora92)

      (SID_NAME = kbdv2)

    )

  )

 

 

kbdv2 =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = kbdv2)

    )

  )

 

5        登陸資料庫檢查修改是否正確

SQL> startup

ORACLE 例程已經啟動。

 

Total System Global Area  135338868 bytes

Fixed Size                   453492 bytes

Variable Size             109051904 bytes

Database Buffers           25165824 bytes

Redo Buffers                 667648 bytes

資料庫裝載完畢。

ORA-01589: 要開啟資料庫則必須使用 RESETLOGS 或 NORESETLOGS 選項

SQL> alter database open resetlogs;

 

資料庫已更改。

 

SQL> show parameter name

 

NAME                                 TYPE        VALUE

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

db_file_name_convert                 string

db_name                              string      kbdv2

global_names                         boolean     FALSE

instance_name                        string      kbdv2

 

 

SQL> select instance_name from v$instance;

 

INSTANCE_NAME

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

kbdv2

 

這樣資料庫的instance 和dbname 就全部修改完成