分成两个步骤，先修改instance_name,在修改db_name
修改SID
1.全备份数据库
RMAN> backup as compressed backupset database include
current controlfile plus archivelog delete all input ;
(DBID=1698783370)
2.改环境变量中的ORACLE_SID
vi
.bash_profile
3.改参数文件名称initSID.ora
mv initSBBB.ora initSBDB.ora
4.改数据文件和日志文件路径
SQL> startup mount
pfile=‘/opt/oracle/product/11.1.0/db_1/dbs/initSBDB.ora‘;
$ cp *.dbf
../SBDB/
SQL> alter database rename file
‘/opt/oracle/oradata/SBBB/sysaux.dbf‘ to ‘/opt/oracle/oradata/SBDB/sysaux.dbf‘
;
SQL> alter database rename file ‘/opt/oracle/oradata/SBBB/system.dbf‘ to
‘/opt/oracle/oradata/SBDB/system.dbf‘ ;
SQL> alter database rename file
‘/opt/oracle/oradata/SBBB/temp01.dbf‘ to ‘/opt/oracle/oradata/SBDB/temp01.dbf‘
;
SQL> alter database rename file ‘/opt/oracle/oradata/SBBB/undotbs1.dbf‘
to ‘/opt/oracle/oradata/SBDB/undotbs1.dbf‘ ;
SQL> alter database rename
file ‘/opt/oracle/oradata/SBBB/users01.dbf‘ to
‘/opt/oracle/oradata/SBDB/users01.dbf‘ ;
SQL> alter database rename file
‘/opt/oracle/oradata/SBBB/redo1.log‘ to ‘/opt/oracle/oradata/SBDB/redo1.log‘
;
SQL> alter database rename file ‘/opt/oracle/oradata/SBBB/redo2.log‘ to
‘/opt/oracle/oradata/SBDB/redo2.log‘ ;
SQL> alter database rename file
‘/opt/oracle/oradata/SBBB/redo3.log‘ to ‘/opt/oracle/oradata/SBDB/redo3.log‘
;
5.改密码文件
6.启动数据库，并做一个全库备份
接下来修改DB_NAME
1.先关闭后启动到mount状态
SQL> alter database mount ;
Database altered.
SQL>
SQL>
SQL>
SQL> exit
Disconnected from Oracle
Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the
Partitioning, OLAP, Data Mining and Real Application Testing options
2.使用ORACLE自带的nid工具,ORACLE需要启动到mount
$ nid target=sys/ocmb123 as sysdba
dbname=SBDB
DBNEWID: Release 11.1.0.6.0 - Production on Mon Mar 17 20:29:23 2014
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Password:
Connected to database SBBB (DBID=1698783370)
Connected to server version 11.1.0
Control Files in database:
/opt/oracle/oradata/SBDB/control1.ctl
/opt/oracle/oradata/SBDB/control2.ctl
/opt/oracle/oradata/SBDB/control3.ctl
Change database ID and database name SBBB to SBDB? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 1698783370 to
1234663846
Changing database name from SBBB to SBDB
Control File
/opt/oracle/oradata/SBDB/control1.ctl - modified
Control File
/opt/oracle/oradata/SBDB/control2.ctl - modified
Control File
/opt/oracle/oradata/SBDB/control3.ctl - modified
Datafile
/opt/oracle/oradata/SBDB/system.dbf - dbid changed, wrote new name
Datafile /opt/oracle/oradata/SBDB/sysaux.dbf - dbid changed, wrote new name
Datafile /opt/oracle/oradata/SBDB/undotbs1.dbf - dbid changed, wrote new
name
Datafile /opt/oracle/oradata/SBDB/users01.dbf - dbid changed, wrote
new name
Datafile /opt/oracle/oradata/SBDB/temp01.dbf - dbid changed,
wrote new name
Control File /opt/oracle/oradata/SBDB/control1.ctl - dbid
changed, wrote new name
Control File
/opt/oracle/oradata/SBDB/control2.ctl - dbid changed, wrote new name
Control File /opt/oracle/oradata/SBDB/control3.ctl - dbid changed, wrote new
name
Instance shut down
Database name changed to SBDB.
Modify parameter file and generate a new
password file before restarting.
Database ID for database SBDB changed to
1234663846.
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.
修改成功后，数据库会关闭。
3.修改initSBDB.ora里面的db_name=SBDB
SQL> startup mount
pfile=‘/opt/oracle/product/11.1.0/db_1/dbs/initSBDB.ora‘;
ORACLE instance
started.
Total System Global Area 523108352 bytes
Fixed Size
1301024 bytes
Variable Size 318768608 bytes
Database Buffers
197132288 bytes
Redo Buffers 5906432 bytes
Database
mounted.
SQL>
SQL>
SQL>
SQL> alter database open
resetlogs ;
Database altered.
至此数据库完全打开，instance_name,db_name全部改好。
还有一种方法不用nid,直接重建控制文件。
alter
database backup controlfile to trace resetlogs ;
trace文件中会生成create
controlfile的语句，相对比较麻烦，有兴趣可以尝试下。
另一种修改DB_NAME的大致思路：
1.alter system switch logifle;
2.alter database
backup controlfile to trace resetlogs ;
3.shutdown
immediate
4.user_dump_dest 查看trace文件
5拷贝出create controlfile 语句，REUSE改成set
,dbnam改成新的
6.改名control
files，为了备份
7.改参数文件中db_name
8.执行重建控制文件的语句，如果报错
recover database using
backup controlfile until cancel ;
如果继续报错，提供状态为CURRENT的日志 v$log;
select
group#,seq#,status from v$log ;
9.alter database open resetlogs
;
10.增加新的TEMP文件
alter tablespace temp add tempfile
‘/opt/oracle/oradata/...‘ size 100m resue autoextend on next 10m maxsize 32767m
;
11.改全局数据库名
alter database rename global_name to
**
12.全备数据库
13.确认dbsnmp.bsln_baselines的DBID ,INSTANCE_NAME已经更改。