使用NID更改数据库实例名

1、登陆数据库把数据库变成mount状态

[/app/oracle]$ ps -ef|grep pmon

oracle 2532 1 0 09:42:18 ? 0:00 ora_pmon_DBATEST

oracle 2619 2602 0 09:51:40 pts/ta 0:00 grep pmon

[/app/oracle]$ sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.8.0 - Production on Wed Apr 2 09:51:58 2008

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

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.8.0 - Production

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 320300808 bytes

Fixed Size 734984 bytes

Variable Size 285212672 bytes

Database Buffers 33554432 bytes

Redo Buffers 798720 bytes

Database mounted.

SQL> exit

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.8.0 - Production

[@more@]

2、使用NID命令来修改数据库instanc_name和dbname

注意，运行命令前，原listener不能停掉

[/app/oracle]$ nid target=sys/DBATEST151@DBATEST dbname=TADBA

DBNEWID: Release 9.2.0.8.0 - 64bit Production

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

Connected to database DBATEST (DBID=3757393467)

Control Files in database:

/app/oradata/EAITEST/control01.ctl

/app/oradata/EAITEST/control02.ctl

/app/oradata/EAITEST/control03.ctl

The following datafiles are read-only:

/app/oradata/EAITEST/perfstat01.dbf (10)

These files must be writable by this utility.

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

Proceeding with operation

Changing database ID from 3757393467 to 3375181722

Changing database name from DBATEST to TADBA

Control File /app/oradata/EAITEST/control01.ctl - modified

Control File /app/oradata/EAITEST/control02.ctl - modified

Control File /app/oradata/EAITEST/control03.ctl - modified

Datafile /app/oradata/EAITEST/system01.dbf - dbid changed, wrote new name

Datafile /app/oradata/EAITEST/undotbs01.dbf - dbid changed, wrote new name

Datafile /app/oradata/EAITEST/cwmlite01.dbf - dbid changed, wrote new name

Datafile /app/oradata/EAITEST/drsys01.dbf - dbid changed, wrote new name

Datafile /app/oradata/EAITEST/example01.dbf - dbid changed, wrote new name

Datafile /app/oradata/EAITEST/indx01.dbf - dbid changed, wrote new name

Datafile /app/oradata/EAITEST/odm01.dbf - dbid changed, wrote new name

Datafile /app/oradata/EAITEST/tools01.dbf - dbid changed, wrote new name

Datafile /app/oradata/EAITEST/users01.dbf - dbid changed, wrote new name

Datafile /app/oradata/EAITEST/perfstat01.dbf - dbid changed, wrote new name

Control File /app/oradata/EAITEST/control01.ctl - dbid changed, wrote new name

Control File /app/oradata/EAITEST/control02.ctl - dbid changed, wrote new name

Control File /app/oradata/EAITEST/control03.ctl - dbid changed, wrote new name

Database name changed to TADBA.

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

Database ID for database TADBA changed to 3375181722.

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

Shut down database and open with RESETLOGS option.

Succesfully changed database name and ID.

DBNEWID - Completed succesfully.

[/app/oracle]$ sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.8.0 - Production on Wed Apr 2 10:01:31 2008

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

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.8.0 - Production

SQL> shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL>

3、修改创建参数文件、密码文件等

[/app/oracle]$ cd admin

[/app/oracle/admin]$ mv DBATEST TADBA

[/app/oracle/admin]$ cd $ORACLE_HOME/dbs

[/app/oracle/product/9205/dbs]$ cp initDBATEST.ora initTADBA.ora

[/app/oracle/product/9205/dbs]$ vi initTADBA.ora

修改以下几行：

*.background_dump_dest='/app/oracle/admin/TADBA/bdump'

*.core_dump_dest='/app/oracle/admin/TADBA/cdump'

*.db_name='TADBA'

*.instance_name='TADBA'

*.user_dump_dest='/app/oracle/admin/TADBA/udump'

[/app/oracle/product/9205/dbs]$ orapwd file=/app/oracle/product/9205/dbs/orapwTADBA password=TADBA151

[/app/oracle/product/9205/dbs]$ cd ../network/admin

修改listener.ora和tnsnames.ora

注：如是在windows系统下的，所以先修改物理服务名C:>oradim -edit -sid drew -newsid kbdv2

并且修改注册表 HKEY_LOCAL_MACHINESOFTWAREORACLEHOME0ORACLE_SID

4、登陆数据库检查修改是否正确

[/app/oracle/product/9205/dbs]$ export ORACLE_SID=TADBA

[/app/oracle/product/9205/dbs]$ sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.8.0 - Production on Wed Apr 2 10:10:14 2008

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

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 320300808 bytes

Fixed Size 734984 bytes

Variable Size 285212672 bytes

Database Buffers 33554432 bytes

Redo Buffers 798720 bytes

Database mounted.

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;

Database altered.

SQL> select instance_name from v$instance;

INSTANCE_NAME

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

TADBA

SQL> create spfile from pfile;

File created.

SQL>

之后再重启一次数据库，重启listener就可以了

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 320300808 bytes

Fixed Size 734984 bytes

Variable Size 285212672 bytes

Database Buffers 33554432 bytes

Redo Buffers 798720 bytes

Database mounted.

Database opened.

SQL> show parameter spfile

NAME TYPE VALUE

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

spfile string ?/dbs/spfile@.ora

SQL>

[/app/oracle/admin]$ cd TADBA

[/app/oracle/admin/TADBA]$ cd bdump

[/app/oracle/admin/TADBA/bdump]$ ls

alert_DBATEST.log

alert_TADBA.log

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13171581/viewspace-1001784/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13171581/viewspace-1001784/