修改数据库的instance_name和db_name

浏览数：27 / 时间：2015年06月12日

分成两个步骤，先修改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

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已经更改。