单实例情况下修改数据库DB_NAME和SID

单实例情况下修改数据库DB_NAME和SID

基础信息
db_name sid
修改前 ora193 ora193
修改后 ora1917 ora1917
一．修改DB_NAME
（1）生成pfile文件
sqlplus / as sysdba;
create pfile from spfile;
在$ORACLE_HOME/dbs下生成一个pfile文件
initora193.ora spfileora193.ora
（2）数据库启动到mount状态
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1073737800 bytes
Fixed Size 8904776 bytes
Variable Size 289406976 bytes
Database Buffers 767557632 bytes
Redo Buffers 7868416 bytes
Database mounted.
SQL>

（3）修改DB_NAME
使用nid命令修改db_name
[oracle@hbdb1 dbs]$ nid -help

DBNEWID: Release 19.0.0.0.0 - Production on Wed Mar 29 16:00:29 2023

Copyright © 1982, 2019, Oracle and/or its affiliates. All rights reserved.

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 target=sys/oracle dbname=ora1917 setname=yes ### setname=yes只更改db_name,不更改dbid。nid命令修改的是控制文件和数据文件，其它文件需要手工修改。

[oracle@hbdb1 dbs]$ nid target=sys/oracle dbname=ora1917 setname=yes

DBNEWID: Release 19.0.0.0.0 - Production on Wed Mar 29 16:03:10 2023

Copyright © 1982, 2019, Oracle and/or its affiliates. All rights reserved.

Connected to database ORA193 (DBID=1927564332)

Connected to server version 19.3.0

Control Files in database:
/app/oracle/oradata/ORA193/controlfile/o1_mf_kvpz7d64_.ctl

Change database name of database ORA193 to ORA1917? (Y/[N]) => Y

Proceeding with operation
Changing database name from ORA193 to ORA1917
Control File /app/oracle/oradata/ORA193/controlfile/o1_mf_kvpz7d64_.ctl - modified
Datafile /app/oracle/oradata/ORA193/datafile/o1_mf_system_kvpz4h14_.db - wrote new name
Datafile /app/oracle/oradata/ORA193/datafile/o1_mf_sysaux_kvpz5l3x_.db - wrote new name
Datafile /app/oracle/oradata/ORA193/datafile/o1_mf_undotbs1_kvpz616b_.db - wrote new name
Datafile /app/oracle/oradata/ORA193/datafile/o1_mf_tbs0805_kxpvcv4n_.db - wrote new name
Datafile /app/oracle/oradata/ORA193/datafile/o1_mf_users_kvpz629v_.db - wrote new name
Datafile /app/oracle/oradata/ORA193/datafile/o1_mf_temp_kvpz7olp_.tm - wrote new name
Control File /app/oracle/oradata/ORA193/controlfile/o1_mf_kvpz7d64_.ctl - wrote new name
Instance shut down

Database name changed to ORA1917.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.
（4）修改环境变量.bash_profile
[oracle@hbdb1 dbs]$ echo $ORACL{E}_{S}IDora193vi/.bas{h}_{p}rofile将ora193改成ora1917source/.bas{h}_{p}rofile（5）修改tns和lsnrctl[oracle@hbdb1admin]$ cat listener.ora

listener.ora Network Configuration File: /app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora

Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hbdb1)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
监听文件没有静态监听，所以不用修改

（6）修改密码文件
可以通过orapwd命令重新生成又给密码文件，也可以复制原文件改个名字，我这里使用第二种方式。

[oracle@hbdb1 dbs]$ cp orapwora193 orapwora1917
[oracle@hbdb1 dbs]$

（7）修改pfile文件并以之启动创建spfile
[oracle@hbdb1 dbs]$ cp initora193.ora initora1917.ora
*.audit_file_dest=‘/app/oracle/admin/ora193/adump’
*.audit_trail=‘db’
*.compatible=‘19.0.0’
*.control_files=‘/app/oracle/oradata/ORA193/controlfile/o1_mf_kvpz7d64_.ctl’
*.db_block_size=8192
*.db_create_file_dest=‘/app/oracle/oradata’
*.db_name=‘ora193’ —改成ora1917
*.diagnostic_dest=‘/app/oracle’
*.dispatchers=‘(PROTOCOL=TCP) (SERVICE=ora193XDB)’
*.enable_ddl_logging=TRUE
*.local_listener=‘LISTENER_ORA193’
*.nls_language=‘AMERICAN’
*.nls_territory=‘AMERICA’
*.open_cursors=300
*.pga_aggregate_target=741m
*.processes=320
*.remote_login_passwordfile=‘EXCLUSIVE’
*.service_names=‘ora194’
*.sga_target=1024m
*.undo_tablespace=‘UNDOTBS1’
（8）以resetlogs方式启动(没有修改dbid，可以不用resetlogs启动)
[oracle@hbdb1 dbs]$ sqlplus / as sysdba;

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 29 16:20:16 2023
Version 19.3.0.0.0

Copyright © 1982, 2019, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup mount pfile=‘/app/oracle/product/19.0.0/dbhome_1/dbs/initora1917.ora’;
ORACLE instance started.

Total System Global Area 1073737800 bytes
Fixed Size 8904776 bytes
Variable Size 276824064 bytes
Database Buffers 780140544 bytes
Redo Buffers 7868416 bytes
Database mounted.
SQL> show parameter name

NAME TYPE VALUE

---

cdb_cluster_name string
cell_offloadgroup_name string
db_file_name_convert string
db_name string ora1917
db_unique_name string ora1917
global_names boolean FALSE
instance_name string ora1917

SQL> alter database open;

Database altered.

SQL> create spfile from pfile;

File created. 在$ORACLE_HOME/dbs/会生成一个spfile文件spfileora1917.ora

至此修改完成。