mysql instance name_修改数据库的instance_name和db_name

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