Linux环境下oracle数库库改名，实例改名

转自：http://seteor.itpub.net/post/42575/502279

SQL> select instance_name from v$instance; --查看当前实例名

INSTANCE_NAME
----------------
test

SQL> select name from v$database; --查看当前dbname

NAME
---------
TEST

SQL> shutdown immediate --关闭数据库

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

Total System Global Area 205520896 bytes
Fixed Size 1218532 bytes
Variable Size 67110940 bytes
Database Buffers 134217728 bytes
Redo Buffers 2973696 bytes
Database mounted.

DBNEWID: Release 10.2.0.1.0 - Production on Wed Jul 21 16:31:20 2010

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

Connected to database TESTORA (DBID=547002923)

Connected to server version 10.2.0

Control Files in database:
/u01/oradata/oratest/control01.ctl
/u01/oradata/oratest/control02.ctl
/u01/oradata/oratest/control03.ctl

Change database ID and database name TESTORA to TEST? (Y/[N]) => y --程序询问

Proceeding with operation
Changing database ID from 547002923 to 2023088600
Changing database name from TESTORA to TEST2
Control File /u01/oradata/oratest/control01.ctl - modified
Control File /u01/oradata/oratest/control02.ctl - modified
Control File /u01/oradata/oratest/control03.ctl - modified
Datafile /u01/oradata/oratest/system01.dbf - dbid changed, wrote new name
Datafile /u01/oradata/oratest/undotbs01.dbf - dbid changed, wrote new name
Datafile /u01/oradata/oratest/sysaux01.dbf - dbid changed, wrote new name
Datafile /u01/oradata/oratest/users01.dbf - dbid changed, wrote new name
Datafile /u01/oradata/oratest/test.dbf - dbid changed, wrote new name
Datafile /u01/oradata/oratest/temp02.dbf - dbid changed, wrote new name
Control File /u01/oradata/oratest/control01.ctl - dbid changed, wrote new name
Control File /u01/oradata/oratest/control02.ctl - dbid changed, wrote new name
Control File /u01/oradata/oratest/control03.ctl - dbid changed, wrote new name
Instance shut down

Database name changed to TEST2.
Modify parameter file and generate a new password file before restarting.
Database ID for database TEST changed to 2023088600.
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.

SQL> alter system set db_name=test2 scope=spfile; --因为dbname已更改，连接丢失，需重新连接

alter system set db_name=test2 scope=spfile
*
ERROR at line 1:
ORA-03135: connection lost contact --因为dbname已更改，连接丢失，需重新连接

SQL> startup mount
ORACLE instance started.

Total System Global Area 205520896 bytes
Fixed Size 1218532 bytes
Variable Size 67110940 bytes
Database Buffers 134217728 bytes
Redo Buffers 2973696 bytes
ORA-01103: database name 'TEST2' in control file is not 'TEST' --库名已改为test2，但当前启动的数据库的spfile还是'TEST'

SQL> alter system set db_name=test2 scope=spfile; --spfile中改为test2

System altered.
SQL> shutdown immediate; --更改db_name后关闭数据库

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup mount --重新mount，没有再报ORA-01103错误ORACLE instance started.

Total System Global Area 205520896 bytes
Fixed Size 1218532 bytes
Variable Size 67110940 bytes
Database Buffers 134217728 bytes
Redo Buffers 2973696 bytes
Database mounted.

SQL> alter database open resetlogs; --resetlogs

Database altered.

SQL> select dbid,name from v$database; --验证新的dbid和name

DBID NAME
---------- ---------
808938177 TEST2

SQL> select instance_name from v$instance; --实例名仍然未变化（如果不需要改实例名也可以,如果不改sid,只需更改）

INSTANCE_NAME
----------------
test

----------------------------------------------------------------------------改sid----------------------------------------------------------------
SQL> host orapwd file=/u01/oracle/product/10.2.0/db_1/dbs/orapwtest2 password=123 entries=10; --生成新SID对应的密码文件

SQL> create pfile='/u01/oracle/product/10.2.0/db_1/dbs/inittest2.ora' from spfile; --生成新SID对应的pfile文件

File created.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

另开一终端修改.bash_profile文件中的ORACLE_SID

[oracle@wwnode2 ~]$ source .bash_profile; --修改后立刻生效

[oracle@wwnode2 ~]$ echo $ORACLE_SID --验证是否更改成功test2
[oracle@wwnode2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jul 21 16:51:28 2010

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 205520896 bytes
Fixed Size 1218532 bytes
Variable Size 62916636 bytes
Database Buffers 138412032 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.

SQL> select instance_name from v$instance; --验证SID

INSTANCE_NAME
----------------
test2

SQL> select name from v$database; --验证dbname

NAME
---------
TEST2