修改oracle数据库的名称_oracle修改数据库名称 sql

修改oracle数据库的名称

修改oracle数据库的名称需要同时在控制文件和参数文件中进行修改。修改控制文件中数据库的名称使用操作系统命令nid，修改参数文件中数据库的名称使用alter system命令。

step 1：查看数据库的状态

所有的数据文件、临时文件和表空间状态必须是online或offline。查看数据库是否处于归档模式，如果不是，修改为归档模式。

1、查看数据文件的状态

SQL> select name,status from v$datafile;

NAME					 STATUS
---------------------------------------- -------
/usr/local/oradata/orcl/system01.dbf	 SYSTEM
/usr/local/oradata/orcl/sysaux01.dbf	 ONLINE
/usr/local/oradata/orcl/undotbs01.dbf	 ONLINE
/usr/local/oradata/orcl/users01.dbf	 ONLINE
/usr/local/oradata/orcl/data01.dbf	 ONLINE
/usr/local/oradata/orcl/data02.dbf	 ONLINE

6 rows selected.
1
2
3
4
5
6
7
8
9
10
11
12

2、查看临时文件的状态

SQL> select name,status from v$tempfile;

NAME					 STATUS
---------------------------------------- -------
/usr/local/oradata/orcl/temp01.dbf	 ONLINE
1
2
3
4
5

3、查看表空间的状态

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME 	       STATUS
------------------------------ ---------
SYSTEM			       ONLINE
SYSAUX			       ONLINE
UNDOTBS1		       ONLINE
TEMP			       ONLINE
USERS			       ONLINE
DATA01			       ONLINE
DATA02			       ONLINE

7 rows selected.
1
2
3
4
5
6
7
8
9
10
11
12
13

4、查看数据库的归档状态及归档路径

SQL> archive log list;
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       /home/oracle/archive_logs
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence	       1
1
2
3
4
5
6
7

step 2：正常停库，然后启动数据库到mount状态

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

Total System Global Area 1068937216 bytes
Fixed Size		    2220200 bytes
Variable Size		  482348888 bytes
Database Buffers	  578813952 bytes
Redo Buffers		    5554176 bytes
Database mounted.
1
2
3
4
5
6
7
8
9
10
11
12
13

step 3：在操作系统oracle用户下执行nid命令

nid命令修改控制文件中的数据库名称和DBID，命令如下：

[oracle@wgx ~]$ nid target=sys/sys dbname=BOOK

DBNEWID: Release 11.2.0.1.0 - Production on Sat Apr 11 03:20:49 2020

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

Connected to database MYDB (DBID=2952401142)

Connected to server version 11.2.0

Control Files in database:
    /usr/local/oradata/orcl/control01.ctl
    /usr/local/oracle/flash_recovery_area/orcl/control02.ctl

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

Proceeding with operation
Changing database ID from 2952401142 to 1478168914
Changing database name from MYDB to BOOK
    Control File /usr/local/oradata/orcl/control01.ctl - modified
    Control File /usr/local/oracle/flash_recovery_area/orcl/control02.ctl - modified
    Datafile /usr/local/oradata/orcl/system01.db - dbid changed, wrote new name
    Datafile /usr/local/oradata/orcl/sysaux01.db - dbid changed, wrote new name
    Datafile /usr/local/oradata/orcl/undotbs01.db - dbid changed, wrote new name
    Datafile /usr/local/oradata/orcl/users01.db - dbid changed, wrote new name
    Datafile /usr/local/oradata/orcl/data01.db - dbid changed, wrote new name
    Datafile /usr/local/oradata/orcl/data02.db - dbid changed, wrote new name
    Datafile /usr/local/oradata/orcl/temp01.db - dbid changed, wrote new name
    Control File /usr/local/oradata/orcl/control01.ctl - dbid changed, wrote new name
    Control File /usr/local/oracle/flash_recovery_area/orcl/control02.ctl - dbid changed, wrote new name
    Instance shut down

Database name changed to BOOK.
Modify parameter file and generate a new password file before restarting.
Database ID for database BOOK changed to 1478168914.
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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

step 4：重新启动数据库到nomount

SQL> startup force nomount
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size		    2220200 bytes
Variable Size		  482348888 bytes
Database Buffers	  578813952 bytes
Redo Buffers		    5554176 bytes
1
2
3
4
5
6
7
8

step 5：修改参数文件中的数据库名称信息

SQL> alter system set db_name=BOOK scope=spfile;

System altered.
1
2
3

step 6：重新启动数据库到mount

SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size		    2220200 bytes
Variable Size		  482348888 bytes
Database Buffers	  578813952 bytes
Redo Buffers		    5554176 bytes
Database mounted.
1
2
3
4
5
6
7
8
9
10
11

step 7：打开数据库

必须以resetlogs方式打开数据库。

SQL> alter database open resetlogs;

Database altered.
1
2
3

step 8：查看数据库名称

SQL> show parameter name;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert		 string
db_name 			         string	 BOOK
db_unique_name			     string	 BOOK
global_names			     boolean FALSE
instance_name			     string	 orcl
lock_name_space 		     string
log_file_name_convert		 string
service_names			     string	 BOOK
1
2
3
4
5
6
7
8
9
10
11
12

可以看到数据库的名称已经更改，但实例名没有更改。