Oracle 如何更改单实例数据库的 DB_NAME？

原创小小亮 2022-10-27

762

在这篇文章中，我们将讨论如何更改单实例数据库的DB_NAME 。

由于DB_NAME不仅仅是像DB_UNIQUE_NAME这样的简单数据库参数，所以我们需要做更多的工作。如果您在 SPFILE 中修改DB_NAME，您将收到错误 ORA-32016。

对于 RAC 数据库，您应该阅读：如何更改RAC 数据库的数据库名称。

首先，让我们看看当前的状态。

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

      DBID NAME      OPEN_MODE
---------- --------- --------------------
3411734329 ORCLCDB   READ WRITE

在这种情况下，我们希望将DB_NAME从 ORCLLCDB 更改为 TESTCDB。

我们将数据库重新启动到 MOUNT 状态。

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
...
Database mounted.
SQL> exit

使用 DBNEWID 实用程序nid更改所有文件的数据库名称。

[oracle@test ~]$ nid target=sys/password dbname=TESTCDB

DBNEWID: Release 19.0.0.0.0 - Production on Mon Dec 22 19:18:06 2020

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

Connected to database ORCLCDB (DBID=3411734329)

Connected to server version 19.9.0

Control Files in database:
    /u01/app/oracle/oradata/ORCLCDB/control01.ctl
    /u01/app/oracle/fast_recovery_area/ORCLCDB/control02.ctl

Change database ID and database name ORCLCDB to TESTCDB? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 3411734329 to 2800509118
Changing database name from ORCLCDB to TESTCDB
    Control File /u01/app/oracle/oradata/ORCLCDB/control01.ctl - modified
    Control File /u01/app/oracle/fast_recovery_area/ORCLCDB/control02.ctl - modified
    Datafile /u01/app/oracle/oradata/ORCLCDB/system01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/ORCLCDB/sysaux01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/ORCLCDB/undotbs01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/ORCLCDB/pdbseed/system01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/ORCLCDB/pdbseed/sysaux01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/ORCLCDB/users01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/ORCLCDB/pdbseed/undotbs01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/ORCLCDB/ORCLPDB1/system01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/ORCLCDB/ORCLPDB1/users01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/ORCLCDB/ORCLPDB1/example01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/ORCLCDB/temp01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/ORCLCDB/pdbseed/temp012020-07-14_23-10-41-107-PM.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/ORCLCDB/ORCLPDB1/temp01.db - dbid changed, wrote new name
    Control File /u01/app/oracle/oradata/ORCLCDB/control01.ctl - dbid changed, wrote new name
    Control File /u01/app/oracle/fast_recovery_area/ORCLCDB/control02.ctl - dbid changed, wrote new name
    Instance shut down

Database name changed to TESTCDB.
Modify parameter file and generate a new password file before restarting.
Database ID for database TESTCDB changed to 2800509118.
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.

如果您不想要新的 DBID，可以设置SETNAME=YES以防止 DBID 更改。例如：

[oracle@test ~]$ nid target=sys/password dbname=TESTCDB setname=YES

接下来，我们创建 PFILE 以供以后修改。

[oracle@test ~]$ sqlplus / as sysdba
...
Connected to an idle instance.

SQL> create pfile='/home/oracle/pfile' from spfile;

File created.

由于我们不想更改DB_UNIQUE_NAME，我们应该添加一个DB_UNIQUE_NAME并在参数文件中更改DB_NAME 。

如果您没有明确地将DB_UNIQUE_NAME添加到文件中，则DB_UNIQUE_NAME将在启动时跟随DB_NAME。

[oracle@test ~]$ vi /home/oracle/pfile
...
*.db_name='TESTCDB' # Changed by Ed Chen
*.db_unique_name='ORCLCDB' # Added by Ed Chen
...

为启动创建一个新的 SPFILE。

SQL> create spfile from pfile='/home/oracle/pfile';

File created.

我们已准备好启动数据库，但唯一不同的是我们应该使用RESETLOGS打开数据库。

SQL> startup mount;
ORACLE instance started.
...
Database mounted.
SQL> alter database open resetlogs;

Database altered.

请注意，如果您没有通过在nid命令中指定SETNAME=YES来更改 DBID ，那么您不必使用RESETLOGS打开数据库。

让我们检查一下最终结果。

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

      DBID NAME      OPEN_MODE
---------- --------- --------------------
2800509118 TESTCDB   READ WRITE

我们做到了。

原文标题：How to Change DB_NAME of a Single-Instance Database
原文作者： Ed Chen
原文链接：https://logic.edchen.org/how-to-change-db_name-of-a-single-instance-database/

oracle 墨力翻译小小亮的翻译文章

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

Oracle 如何更改单实例数据库的 DB_NAME？

评论