如何修改db_name及SID

cittahan

今天同事发现有个Oracle生产库执行awrrpt时，出来的db信息有些问题，相同的dbid下面有不同的dbname和instance_name，不得其解，遂赶紧找了个测试环境模拟一下。
首先要说一下，awrrpt.sql是通过查询dba_hist_database_instance来检查一个db环境中的历史db_name、instance_name、dbid等信息的。
修改sid：将oper改为oper2
结论：视图dba_hist_database_instance中dbid、dbname不变、instance_name改变；
1、create pile from spfile；
2、shutdown immediate
3、cp initoper.ora initoper2.ora
4、orapwd file=$ORACLE_HOME/dbs/orapwoper2.ora password=oracle
5、export ORACLE_SID=oper2
6、修改listener和tnsnames
7、startup
8、show parameter instance_name

修改db_name：将oper改为opertest，一共有两种方法
方法一：使用nid工具修改db_name以及dbid
结论：视图dba_hist_database_instance中dbid、dbname、instance_name都发生改变；
1、shutdown immediate
2、startup mount
3、nid target=/ dbname=opertest
ora10g@p560qnora3t$ nid target=/ dbname=opertest
DBNEWID: Release 10.2.0.4.0 - Production on Tue Jul 24 13:34:07 2012
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to database OPER (DBID=746063228)
Connected to server version 10.2.0
Control Files in database:
/oracle3040/oper/control01.ctl
/oracle3040/oper/control02.ctl
/oracle3040/oper/control03.ctl
Change database ID and database name OPER to OPERTEST? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 746063228 to 2905185232
Changing database name from OPER to OPERTEST
Control File /oracle3040/oper/control01.ctl - modified
Control File /oracle3040/oper/control02.ctl - modified
Control File /oracle3040/oper/control03.ctl - modified
Datafile /oracle3040/oper/system01.dbf - dbid changed, wrote new name
Datafile /oracle3040/oper/undotbs01.dbf - dbid changed, wrote new name
Datafile /oracle3040/oper/sysaux01.dbf - dbid changed, wrote new name
Datafile /oracle3040/oper/users01.dbf - dbid changed, wrote new name
Datafile /oracle3040/oper/temp01.dbf - dbid changed, wrote new name
Control File /oracle3040/oper/control01.ctl - dbid changed, wrote new name
Control File /oracle3040/oper/control02.ctl - dbid changed, wrote new name
Control File /oracle3040/oper/control03.ctl - dbid changed, wrote new name
Instance shut down
Database name changed to OPERTEST.
Modify parameter file and generate a new password file before restarting.
Database ID for database OPERTEST changed to 2905185232.
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.
4、修改initopertest.ora中的db_name；
5、修改orapw、listener、tnsnames文件
6、startup mount
7、alter database open resetlogs;

方法二：重建控制文件
结论：视图dba_hist_database_instance中dbid不变、dbname、instance_name改变；
1、alter database backup controlfile to trace;
去udump里去寻找dump文件
2、shutdown immediate
3、修改init、orapw、listener、tnsnames文件
4、mv老的控制文件；
5、startup nomount
6、创建控制文件
CREATE CONTROLFILE SET DATABASE "OPER2" RESETLOGS  NOARCHIVELOG
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oracle3040/oper/redo01.log'  SIZE 100M,
GROUP 2 '/oracle3040/oper/redo02.log'  SIZE 100M,
GROUP 3 '/oracle3040/oper/redo03.log'  SIZE 100M
-- STANDBY LOGFILE
DATAFILE
'/oracle3040/oper/system01.dbf',
'/oracle3040/oper/undotbs01.dbf',
'/oracle3040/oper/sysaux01.dbf',
'/oracle3040/oper/users01.dbf'
CHARACTER SET ZHS16GBK
;
7、alter database open resetlogs;


至此实验做完，了解了dba_hist_database_instance中的dbid、dbname、instance_name是如何组合的。