Oracle 修改DB_NAME 和 DBID

最新推荐文章于 2024-08-19 09:00:00 发布

iteye_3158

最新推荐文章于 2024-08-19 09:00:00 发布

阅读量104

点赞数

文章标签：数据库

本文详细介绍了如何使用Oracle的NID工具更改数据库的DBID和DBNAME，包括备份数据库、关闭数据库、运行NID命令等步骤。此外，还讨论了修改DBID和DBNAME对数据库的影响。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一.相关概念

关于DB_NAME,INSTANCE_NAME的定义，参考我的Blog：

DBID,SID,DB_NAME,DB_DOMAIN,INSTANCE_NAME,DB_UNIQUE_NAME,SERVICE_NAMES及监听参数的说明

http://blog.youkuaiyun.com/xujinyang/article/details/6829311

关于NID命令的使用，参考Blog：

How to Change the DBID and the DBNAME by using NID [ID 224266.1]

http://blog.youkuaiyun.com/xujinyang/article/details/6883796

在这篇blog里提到了一下信息：

The NID (New Database ID)is a new utility introduced with Oracle 9.2.The NID utility allows you to change only the DBNAME, or only the DBID or both DBNAME and DBID in the same command.

--我们可以使用NID命令只修改DBNAME，或者只修改DBID，或者2个都修改。

他们的区别如下：

1.If you change the DBID you must open the database with the RESETLOGSoption,which re-creates the online redo logs and resets their sequence to 1.

--修改DBID，必须要用resetlogs打开数据库。这个在之前的DBID的介绍里说过，因为DBID存在与数据文件，redo log和控制文件。所以如果修改了DBID，则必须要使用resetlogs打开。

2. If you change the DBNAME without changing the DBID then this does notrequire you to open with the RESETLOGS option, so database backups andarchived logs are not invalidated.You must change the DB_NAME initializationparameter after a database name change to reflect the new name.Also, you may have to re-create the Oracle password file.If you restore an old backup of the control file (before the name change, then you should use the initialization parameter file and password file from before the database name change.

关于NID修改DBID和DBNAME的具体3种用法，参考之前的那片文章。

在这里还有一点要注意，就是orapwSID和initSID.ora是和INSTANCE_NAME对应的，如果这个参数修改了，那么这2个文件也需要修改并与INSTANCE_NAME对应。修改这个参数也很简单，就是在init文件里指定一个名称即可，如：*.INSTANCE_NAME=orcl

二.修改DBID,DBNAME步骤

在这里，只演示一下修改DBID和DBNAME的操作。并不修改INSTANE_NAME.

步骤如下：

1. Backup of the database.

2. Shutdown IMMEDIATE of the database

3. STARTUP MOUNT

4. Open one session and run NID with sysdba privileges

% nid TARGET=SYS/password@test_db DBNAME=test_db2

- the value of DBNAME is the new dbname of the database

5. After DBNEWID successfully changes the DBID,Shutdown IMMEDIATE of the database

6. Set the DB_NAME initialization parameter in the initialization parameter file to the new database name.

7. Create a new password file.

8. Startup of the database with open resetlogs

在修改之前，先看一下数据库的参数：

SQL>select name,dbid,db_unique_name from v$database;

NAMEDBID DB_UNIQUE_NAME

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

ORCL1264931370 orcl

SQL>select instance_name from v$instance;

INSTANCE_NAME

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

orcl

2.1 shutdown database

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

2.2Startup mount

SQL> startup mount;

ORACLE instance started.

Total System Global Area360710144 bytes

Fixed Size1219424 bytes

Variable Size109053088 bytes

Database Buffers247463936 bytes

Redo Buffers2973696 bytes

Database mounted.

2.3 Run NID command

[oracle@singledb bdump]$nid

DBNEWID: Release 10.2.0.1.0 - Production on Mon Dec 20 14:26:13 2010

KeywordDescription(Default)

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

TARGETUsername/Password(NONE)

DBNAMENew database name(NONE)

LOGFILEOutput Log(NONE)

REVERTRevert failed changeNO

SETNAMESet a new database name onlyNO

APPENDAppend to output logNO

HELPDisplays these messagesNO

[oracle@singledb bdump]$ nid target=/ dbname=dave

DBNEWID: Release 10.2.0.1.0 - Production on Mon Dec 20 14:27:13 2010

Connected to database ORCL (DBID=1264931370)

Connected to server version 10.2.0

Control Files in database:

/u01/app/oracle/oradata/orcl/control01.ctl

/u01/app/oracle/oradata/orcl/control02.ctl

/u01/app/oracle/oradata/orcl/control03.ctl

Change database ID and database name ORCL to DAVE? (Y/[N]) =>Y--手工输入

Proceeding with operation

Changing database ID from 1264931370 to 801102850

Changing database name from ORCL to DAVE

Control File /u01/app/oracle/oradata/orcl/control01.ctl - modified

Control File /u01/app/oracle/oradata/orcl/control02.ctl - modified

Control File /u01/app/oracle/oradata/orcl/control03.ctl - modified

Datafile /u01/app/oracle/oradata/orcl/system.256.736598559 - dbid changed, wrote new name

Datafile /u01/app/oracle/oradata/orcl/undotbs1.258.736598599 - dbid changed, wrote new name

Datafile /u01/app/oracle/oradata/orcl/sysaux.257.736598563 - dbid changed, wrote new name

Datafile /u01/app/oracle/oradata/orcl/users.259.736598641 - dbid changed, wrote new name

Datafile /u01/app/oracle/oradata/orcl/temp.263.736599505 - dbid changed, wrote new name

Datafile /u01/app/oracle/oradata/orcl/temp01.dbf - dbid changed, wrote new name

Control File /u01/app/oracle/oradata/orcl/control01.ctl -dbid changed, wrote new name

Control File /u01/app/oracle/oradata/orcl/control02.ctl - dbid changed, wrote new name

Control File /u01/app/oracle/oradata/orcl/control03.ctl - dbid changed, wrote new name

Instance shut down

Database name changed to DAVE.

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

Database ID for database DAVE changed to 801102850.

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.

[oracle@singledb bdump]$

2.4修改PFILE里的DB_NAME参数

[oracle@singledb dbs]$ cat initorcl.ora

...

*.db_name='dave'

...

2.5用pfile启动数据库，并用resetlogs打开

SQL> startup mount pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora';

ORACLE instance started.

Total System Global Area360710144 bytes

Fixed Size1219424 bytes

Variable Size109053088 bytes

Database Buffers247463936 bytes

Redo Buffers2973696 bytes

Database mounted.

SQL>alter database open resetlogs;

Database altered.

2.7查看修改之后的信息

SQL>select name,dbid,db_unique_name from v$database;

NAMEDBID DB_UNIQUE_NAME

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

DAVE801102850 dave

SQL> select instance_name from v$instance;

INSTANCE_NAME

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

orcl

SQL>

在这里看到，db_name和DBID都修改了，但是instance_name没有修改。在官网的文档里提到了重建密码文件，因为我的instance_name没有修改，所以我没有进行重建。

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