【原创】使用nid命令修改数据库名称

最新推荐文章于 2022-12-22 23:13:53 发布

coldboy9981

最新推荐文章于 2022-12-22 23:13:53 发布

阅读量479

点赞数

文章标签：数据库

nid是Oracle用来更改数据库名称的自带工具.它可以直接修改数据库名称，而无需通过以前需要重建控制文件的方法来改变.

nid命令的使用方法如下：

C:\WINDOWS>nid

DBNEWID: Release 10.2.0.1.0 - Production on Thu May 22 21:56:48 2008

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

下面是使用nid修改数据名称的库实验：

实验环境：WinXP SP2

数据库版本：10.2.0.1

注：其中有些步骤可能是不需要的，主要是为了说明nid执行的两个条件：
1、数据库必须处于mount状态
2、所有的数据文件不能处于disabled状态

具体实验步骤：

1.使用nid将数据库名称由test改为t，但是提示错误：数据库不能处于open的状态

C:\WINDOWS>nid target=sys/test@test dbname=t

DBNEWID: Release 10.2.0.1.0 - Production on Thu May 22 21:58:33 2008

Connected to database TEST (DBID=1946053558)

NID-00121: Database should not be open

Change of database name failed during validation - database is intact.
DBNEWID - Completed with validation errors.

2.关闭数据库，并启动到mount状态

sys@TEST>shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@TEST>startup mount
ORACLE instance started.

Total System Global Area 268435456 bytes
Fixed Size                  1248504 bytes
Variable Size             134218504 bytes
Database Buffers          125829120 bytes
Redo Buffers                7139328 bytes
Database mounted.

3.再次运行nid命令，又报了错，说有数据文件处于disabled的状态

C:\WINDOWS>nid target=sys/test@test dbname=t

DBNEWID: Release 10.2.0.1.0 - Production on Thu May 22 22:01:29 2008

Connected to database TEST (DBID=1946053558)

Connected to server version 10.2.0

Control Files in database:
+TEST/test/control01.ctl
+TEST/test/control02.ctl

The following datafiles are disabled:
F:\ORACLE\PRODUCT\ORADATA\TEST\TEST01.DBF (8)

NID-00125: Database should have no disabled datafiles

Change of database name failed during validation - database is intact.
DBNEWID - Completed with validation errors.

4.查看数据文件的状态，果然有文件处于disabled的状态

sys@TEST>select file#,enabled from v$datafile;

     FILE# ENABLED
---------- ----------
         1 READ WRITE
         2 READ WRITE
         3 READ WRITE
         4 READ WRITE
         5 READ WRITE
         6 READ WRITE
         7 READ WRITE
         8 DISABLED
         9 DISABLED
        10 READ WRITE
        11 READ WRITE
        12 READ WRITE

12 rows selected.

5.将数据库open

sys@TEST>alter database open;

Database altered.

6.发现原来是有两个表空间offline了

sys@TEST>select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
UNDOTBS1                       ONLINE
SYSAUX                         ONLINE
TEMP                           ONLINE
USERS                          ONLINE
TEST_BIG                       ONLINE
MGMT_TABLESPACE                ONLINE
MGMT_ECM_DEPOT_TS              ONLINE
TEST                           OFFLINE
TEST1                          OFFLINE
UNDO01                         ONLINE
UNDO02                         ONLINE
TEST03                         ONLINE

13 rows selected.

7.将这两个表空间online

sys@TEST>alter tablespace test1 online;

Tablespace altered.

sys@TEST>alter tablespace test online;

Tablespace altered.

8.再次查看，所有数据文件的状态都是READ WRITE的

sys@TEST>select file#,enabled from v$datafile;

     FILE# ENABLED
---------- ----------
         1 READ WRITE
         2 READ WRITE
         3 READ WRITE
         4 READ WRITE
         5 READ WRITE
         6 READ WRITE
         7 READ WRITE
         8 READ WRITE
         9 READ WRITE
        10 READ WRITE
        11 READ WRITE
        12 READ WRITE

12 rows selected.

9.再次关闭数据库，并启动到mount状态

sys@TEST>shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@TEST>startup mount
ORACLE instance started.

10.执行nid命令，这次运行成功了，可以看到数据库id改变了，由1946053558变成544433466，这就意味着原来的备份和归档日志都不能再使用了

C:\WINDOWS>nid target=sys/test@test dbname=t

DBNEWID: Release 10.2.0.1.0 - Production on Thu May 22 22:14:16 2008

Connected to database TEST (DBID=1946053558)

Connected to server version 10.2.0

Control Files in database:
+TEST/test/control01.ctl
+TEST/test/control02.ctl

Change database ID and database name TEST to T? (Y/[N]) => y

Proceeding with operation
Changing database ID from 1946053558 to 544433466
Changing database name from TEST to T
    Control File +TEST/test/control01.ctl - modified
    Control File +TEST/test/control02.ctl - modified
    Datafile +TEST/test/datafile/system.258.650496175 - dbid changed, wrote new name
    Datafile +TEST/test/datafile/undotbs1.259.650496245 - dbid changed, wrote new name
    Datafile +TEST/test/datafile/sysaux.257.650496219 - dbid changed, wrote new name
    Datafile +TEST/test/datafile/users.260.650496247 - dbid changed, wrote new name
    Datafile +TEST/test/datafile/test_big.262.650496255 - dbid changed, wrote new name
    Datafile F:\ORACLE\PRODUCT\ORADATA\TEST\MGMT.DBF - dbid changed, wrote new name
    Datafile F:\ORACLE\PRODUCT\ORADATA\TEST\MGMT_ECM_DEPOT1.DBF - dbid changed, wrote new name
    Datafile F:\ORACLE\PRODUCT\ORADATA\TEST\TEST01.DBF - dbid changed, wrote new name
    Datafile F:\ORACLE\PRODUCT\ORADATA\TEST\TEST02.DBF - dbid changed, wrote new name
    Datafile F:\ORACLE\PRODUCT\ORADATA\TEST\UNDO01.DBF - dbid changed, wrote new name
    Datafile F:\ORACLE\PRODUCT\ORADATA\TEST\UNDO02.DBF - dbid changed, wrote new name
    Datafile F:\ORACLE\PRODUCT\ORADATA\TEST\TEST03.DBF - dbid changed, wrote new name
    Datafile +TEST/temp01.dbf - dbid changed, wrote new name
    Control File +TEST/test/control01.ctl - dbid changed, wrote new name
    Control File +TEST/test/control02.ctl - dbid changed, wrote new name
    Instance shut down

Database name changed to T.
Modify parameter file and generate a new password file before restarting.
Database ID for database T changed to 544433466.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

11.关闭数据库

sys@TEST>shut immediate
ORA-01507: database not mounted

ORACLE instance shut down.

12.重新启动数据库，在从nomount状态启动至mount状态时报错，这是因为控制文件中的数据库名称已经改成t了，但参数文件中的db_name仍然是test，所以报错了

idle>startup
ORACLE instance started.

Total System Global Area 268435456 bytes
Fixed Size                  1248504 bytes
Variable Size             125829896 bytes
Database Buffers          134217728 bytes
Redo Buffers                7139328 bytes
ORA-01103: database name 'T' in control file is not 'TEST'

13.查看参数文件中的db_name设置

idle>show parameter name

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- --------------------------
db_file_name_convert                 string
db_name                              string                 test
db_unique_name                       string                 test
global_names                         boolean                FALSE
instance_name                        string                 test
lock_name_space                      string
log_file_name_convert                string
service_names                        string                 test

14.修改参数文件中的db_name参数，并重启数据库

idle>alter system set db_name='t' scope=spfile;

System altered.

idle>shut immediate
ORA-01507: database not mounted

ORACLE instance shut down.
idle>startup
ORACLE instance started.

Total System Global Area 268435456 bytes
Fixed Size                  1248504 bytes
Variable Size             125829896 bytes
Database Buffers          134217728 bytes
Redo Buffers                7139328 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

15.由于数据库的id变化了，需要以resetlogs的方式启动数据库

idle>alter database open resetlogs;

Database altered.

16.到此数据库的名称修改完毕

idle>show parameter name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string
db_name                              string      t
db_unique_name                       string      test
global_names                         boolean     FALSE
instance_name                        string      test
lock_name_space                      string
log_file_name_convert                string
service_names                        string      t

17.重建密码文件，进行数据库备份，这里就不详述了

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

转载于:http://blog.itpub.net/498744/viewspace-293561/

coldboy9981

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
【原创】使用nid命令修改数据库名称

nid是Oracle用来更改数据库名称的自带工具.它可以直接修改数据库名称，而无需通过以前需要重建控制文件的方法来改变.nid命令的使用方法如下：C:\WINDOWS>nidDBNEWID: Release 1...
复制链接

扫一扫