如何修改数据库的dbid

如何修改数据库名称和dbid

从Oracle9.2之后就提供了哦工NID ( New Database ID),使用该工具修改DBNAME和DBID。
注意：
1 如果修改DBID则必须resetlogs打开数据库，这样会重建在线日志，reset日志序列号到1，此时是一个新
的数据库。
2 如果仅仅修改DBNAME,无需resetlogs开库，此时的备份和归档日志依然有效。还需要重建密码文件。
如需恢复旧的控制文件备份，则需要修改之前的初始化参数文件和密码文件。


该工具有诸多限制，感兴趣课参考How to Change the DBID and the DBNAME by using NID (Doc ID 224266.1)
下面测试修改DBID。

1 查下DBID
[oracle@rac1 ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Aug 19 15:11:13 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

connected to target database: PROD (DBID=486718737)

2 关闭数据库，否则连接报错
[oracle@rac1 ~]$ nid target=sys/oracle@prod

DBNEWID: Release 19.0.0.0.0 - Production on Thu Aug 19 15:16:11 2021

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

Connected to database PROD (DBID=486718737)

NID-00121: Database should not be open


Change of database ID failed during validation - database is intact.
DBNEWID - Completed with validation errors.
[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Aug 19 15:16:36 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle. All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down

3 启动到mount阶段
SQL> startup mount;
ORACLE instance started.

Total System Global Area 3070227080 bytes
Fixed Size 8901256 bytes
Variable Size 738197504 bytes
Database Buffers 2315255808 bytes
Redo Buffers 7872512 bytes
Database mounted.
SQL>

4 nid通过oracle用户连接数据库,修改DBID
[oracle@rac1 ~]$ nid target=sys/oracle@prod

DBNEWID: Release 19.0.0.0.0 - Production on Thu Aug 19 15:28:53 2021

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

Connected to database PROD (DBID=486718737)

Connected to server version 19.3.0

Control Files in database:
/oracle/base/oradata/PROD/control01.ctl
/oracle/base/oradata/PROD/control02.ctl

Change database ID of database PROD? (Y/[N]) => y

Proceeding with operation
Changing database ID from 486718737 to 492834486
Control File /oracle/base/oradata/PROD/control01.ctl - modified
Control File /oracle/base/oradata/PROD/control02.ctl - modified
Datafile /oracle/base/oradata/PROD/system01.db - dbid changed
Datafile /oracle/base/oradata/PROD/tbs_u1.db - dbid changed
Datafile /oracle/base/oradata/PROD/sysaux01.db - dbid changed
Datafile /oracle/base/oradata/PROD/undotbs01.db - dbid changed
Datafile /oracle/base/19.3/dbs/cachetblsp.db - dbid changed
Datafile /oracle/base/oradata/PROD/users01.db - dbid changed
Datafile /oracle/base/oradata/PROD/tbs_lob.db - dbid changed
Datafile /oracle/base/oradata/PROD/creccoa.db - dbid changed
Datafile /oracle/base/oradata/PROD/temp01.db - dbid changed
Control File /oracle/base/oradata/PROD/control01.ctl - dbid changed
Control File /oracle/base/oradata/PROD/control02.ctl - dbid changed
Instance shut down

Database ID for database PROD changed to 492834486.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID - Completed succesfully.


5 关闭数据库
SQL> select open_mode from v$database;

OPEN_MODE
----------------------------------------
MOUNTED

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

6 启动数据库，resetlogs打开数据库，验证修改结果。
SQL> startup mount;
ORACLE instance started.

Total System Global Area 3070227080 bytes
Fixed Size 8901256 bytes
Variable Size 738197504 bytes
Database Buffers 2315255808 bytes
Redo Buffers 7872512 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;

Database altered.

SQL> set line 120
SQL> select dbid,name,open_mode,activation#,created from v$database

DBID NAME OPEN_MODE ACTIVATION# CREATED
---------- ------------------ ---------------------------------------- ----------- -------------------
492834486 PROD READ WRITE 492796355 2021-06-10 17:53:21

The NID 修改旧的 DBID (DBID=486718737)为新的DBID 492834486

至此修改完毕！