oracle 修改 dbid,ORACLE 11gr2利用nid修改DBID,DBNAME

dbid改变后，之前的备份和归档都会失效；主库中的dbid更改后，其备库需要重新处理；修改dbname后，其dbid会自动生成。

所以，一般情况下，都不会去随意更改其dbname；因为在更改的过程中需要起停数据库。

修改前信息记录如下：

SQL> select name,dbid from v$database;

NAME DBID

———– ———-

TRSENDB 98841159

利用nid工具进行处理，其输出记录如下信息：

[oracle@gpsdb ~]$ nid target=sys/oracle dbname=trsen

DBNEWID: Release 11.2.0.4.0 – Production on Mon Dec 14 13:36:24 2015

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

Connected to database TRSENDB (DBID=98841159)

Connected to server version 11.2.0

Control Files in database:

/oradata/TRSENDB/controlfile/o1_mf_c6nty7hy_.ctl

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

Proceeding with operation

Changing database ID from 98841159 to 3731845208

Changing database name from TRSENDB to TRSEN

Control File /oradata/TRSENDB/controlfile/o1_mf_c6nty7hy_.ctl – modified

Datafile /oradata/TRSENDB/datafile/o1_mf_system_c6nty8kw_.db – dbid changed, wrote new name

Datafile /oradata/TRSENDB/datafile/o1_mf_sysaux_c6ntydjv_.db – dbid changed, wrote new name

Datafile /oradata/TRSENDB/datafile/o1_mf_undotbs1_c6ntyh03_.db – dbid changed, wrote new name

Datafile /oradata/TRSENDB/datafile/o1_mf_users_c6ntyp7q_.db – dbid changed, wrote new name

Datafile /oradata/TRSENDB/datafile/o1_mf_temp_c6ntyhsk_.tm – dbid changed, wrote new name

Control File /oradata/TRSENDB/controlfile/o1_mf_c6nty7hy_.ctl – dbid changed, wrote new name

Instance shut down

Database name changed to TRSEN.

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

Database ID for database TRSEN changed to 3731845208.

All previous backups and archived redo logs for this database are unusable.

Database has been shutdown, open database with RESETLOGS option.

Succesfully changed database name and ID.

DBNEWID – Completed succesfully.==========================》》》说明修改已经成功，

创建新的参数文件修改db_name为新的名字：

[oracle@gpsdb ~]$ export ORACLE_SID=trsendb

[oracle@gpsdb ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 14 13:40:53 2015

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

Connected to an idle instance.

SQL> create pfile=’/tmp/inittrsendb.ora’ from spfile;

File created.

SQL> exit

Disconnected

[oracle@gpsdb ~]$ export ORACLE_SID=trsen

[oracle@gpsdb ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 14 13:45:28 2015

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

Connected to an idle instance.

SQL> startup mount pfile=’/tmp/inittrsendb.ora’;================》》》》此时参数文件已经修改

ORACLE instance started.

Total System Global Area 6.0663E+10 bytes

Fixed Size 2268032 bytes

Variable Size 5637145728 bytes

Database Buffers 5.4895E+10 bytes

Redo Buffers 128344064 bytes

Database mounted.

SQL> show parameter db_name;

NAME TYPE VALUE

———————————— ———– ——————————

db_name string trsen===》》》此时参数文件的信息已经更改

设置从spfile启动：

SQL> create spfile from pfile=’/tmp/inittrsendb.ora’;

File created.

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 6.0663E+10 bytes

Fixed Size 2268032 bytes

Variable Size 5637145728 bytes

Database Buffers 5.4895E+10 bytes

Redo Buffers 128344064 bytes

Database mounted.

SQL> show parameter spfile;

NAME TYPE VALUE

———————————— ———– ——————————

spfile string /u01/app/oracle/product/11.2.0

/dbhome_1/dbs/spfiletrsen.ora

以resetlog 的方式打开数据库：

SQL> alter database open resetlogs;

Database altered.

其他文件信息的修改：

重建密码文件

[oracle@gpsdb tmp]$ orapwd file=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwtrsen password=oracle entries=5

如果配置了tnsnames.ora和listener.ora,需要修改tnsname.ora及listener.ora文件

看是否需要修改global_name

SQL> show parameter global

NAME TYPE VALUE

———————————— ———– ——————————

global_context_pool_size string

global_names boolean FALSE

验证说明修改dbname和dbid已经成功，至此，重新做备份

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

NAME DBID OPEN_MODE

——— ———- ——————–

TRSEN 3731845208 READ WRITE