oracle dbnewid,使用Dbnewid更改oracle的dbid和dbname

使用Dbnewid更改oracle的dbid和dbname

平台;redhat linux as5, oracle10g

内容介绍:本文介绍如何使用dbnewid修改oracle10g的dbid和dbname及操作程中常见的故障排除,其中dbid是标识数据库的内部标识符,dbname是标识数据库的名称.使用dbnewid可以避免重建控

制文件。更改db_name的时候，由于数据库名还存在于参数文件中，因此，更改数据库名时也要更改相应的参数。如果使用了spfile，那么要重建它。另外，还需要重建密码文件。最后介绍了

如何更改global_name和instance_naem

1.更改dbname与dbid

查看现在的dbname与dbid

SQL> show parameter name;

NAME                                   TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_file_name_convert              string

db_name                                 string          denver

global_names                           boolean      denver

instance_name                          string         denver

oracle_trace_collection_name   string

oracle_trace_facility_name        string        oracled

plsql_native_make_file_name    string

service_names                          string         denver

可见dbname,dbid,instance_name, global_names都为denver

1.修改DBID和DBNAME

如果只更改dbid

SQL>! nid

如果只更改db_name

SQL>! nid dbname=taaa setname=y

如果你想同时更改数据库名(db_name)和数据库ID(dbid)，

SQL> ! nid dbname=aaa

或:

SQL> ! nid target=sys dbname=aaa

本文同时更改dbid和dbname

SQL> shutdown immediate

SQL> startup mount;

SQL> ! nid target=sys dbname=aaa

DBNEWID: Release 9.2.0.4.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

Password: (输入密码)

Connected to database DENVER (DBID=3973837782)

Control Files in database:

/u01/oradata/denver/control01.ctl

/u01/oradata/denver/control02.ctl

/u01/oradata/denver/control03.ctl

Change database ID and database name DENVER to AAA? (Y/[N]) => y

Proceeding with operation

Changing database ID from 3973837782 to 1841424434

#省略一部分

Shut down database and open with RESETLOGS option.

Succesfully changed database name and ID.

DBNEWID - Completed succesfully.

简单排错

1.如果数据文件不正常这一步很可能执行不成功,如下

SQL> select file#,status from v$datafile;

FILE# STATUS

---------- -------------------

1 SYSTEM

2 ONLINE

3 ONLINE

4 ONLINE

5 ONLINE

6 RECOVER

把文件6恢复

SQL> recover datafile 6;

2.查看归档日志

有时候在归档方式下,没有归档日录,这时要新建一个归档日录

SQL> archive log list;

SQL> host ls -l /home/oracle/oracle/oradata/db2/arch

ls: /home/oracle/oracle/oradata/db2/arch: No such file or directory

[oracle@oracle ~]$ cd /home/oracle/oracle/oradata/db2/

[oracle@oracle db2]$ mkdir arch

2.重建参数文件

查看使用什么参数文件

SQL> show parameter spfile;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

spfile                                   string

重建spfile

SQL> create pfile from spfile;

[oracle01@vfast dbs]$ vi initdenver.ora

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='aaa'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=denverXDB)'

*.fast_start_mttr_target=300

[oracle01@vfast dbs]$ mv spfiledenver.ora spfiledenver.ora.bak

SQL> create spfile from pfile;

3.修改密码文件

[oracle01@vfast ~]$ cd $ORACLE_HOME/dbs

[oracle01@vfast ~]$ mv orapwdenver orapwdever.bak

[oracle01@vfast dbs]$ orapwd file=orapwdenver password=123456

4.用resetlogs参数启动

SQL> shutdown immediate;

SQL>startup mount;

SQL> alter database open resetlogs;

SQL> show parameter name;

Database altered.

db_name                              string      aaa

global_names                          boolean     denver

instance_name                         string      denver

service_names                         string      aaa

数据库能启动了,db_name与service_name也修改成功了

二:更改global_names

修改global_names

SQL> alter database rename global_names to aaa;

Database altered.

三:更改instance_name

SQL>alter system set instance_name=’aaa’ scope=spfile;

SQL>shutdown immediate;

SQL>startup;

现在再查看:

SQL> show parameter name;

Database altered.

db_name                              string      aaa

global_names                          boolean     aaa

instance_name                         string      aaa

service_names                         string      aaa

可见global_name和instnace_name也更改了