修改数据库名

1.场景分析

在有些场合下，由于规划失当或者现实需要（如虚拟机拷贝来创建一个新库），需要对数据库的名字做出修改。

2. 由spfile生成pfile

SQL> create pfile from spfile;

File created.

3.查看参数文件，将db_name做出修改

*.db_name='grid'   - - 修改为后grid

4. 创建控制文件的跟踪

SQL> alter database backup controlfile to trace;

Database altered.

查看alert日志：

alter database backup controlfile to trace

Backup controlfile written to trace file /u01/oracle11R2/diag/rdbms/ql/ql/trace/ql_ora_4032.trc

Completed: alter database backup controlfile to trace

查看跟踪文件：

CREATE CONTROLFILE REUSE DATABASE "QL" NORESETLOGS FORCE LOGGING ARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 (

    '/u01/oracle11R2/oradata/QL/onlinelog/o1_mf_1_7y53ny43_.log',

    '/u01/oracle11R2/flash_recovery_area/QL/onlinelog/o1_mf_1_7y53nybp_.log'

  ) SIZE 50M BLOCKSIZE 512,

  GROUP 2 (

    '/u01/oracle11R2/oradata/QL/onlinelog/o1_mf_2_7y53o2o3_.log',

    '/u01/oracle11R2/flash_recovery_area/QL/onlinelog/o1_mf_2_7y53o2s0_.log'

  ) SIZE 50M BLOCKSIZE 512,

  GROUP 3 (

    '/u01/oracle11R2/oradata/QL/onlinelog/o1_mf_3_7y53o7lq_.log',

    '/u01/oracle11R2/flash_recovery_area/QL/onlinelog/o1_mf_3_7y53o7pb_.log'

  ) SIZE 50M BLOCKSIZE 512

DATAFILE

  '/u01/oracle11R2/oradata/QL/datafile/o1_mf_system_7y53jbj5_.dbf',

  '/u01/oracle11R2/oradata/QL/datafile/o1_mf_sysaux_7y53jbkl_.dbf',

  '/u01/oracle11R2/oradata/QL/datafile/o1_mf_undotbs1_7y53jbl5_.dbf',

  '/u01/oracle11R2/oradata/QL/datafile/o1_mf_users_7y53jblv_.dbf'

CHARACTER SET AL32UTF8

;

5.修改控制文件

CREATE CONTROLFILE set DATABASE " GRID" RESETLOGS ARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 (

    '/u01/oracle11R2/oradata/QL/onlinelog/o1_mf_1_7y53ny43_.log',

    '/u01/oracle11R2/flash_recovery_area/QL/onlinelog/o1_mf_1_7y53nybp_.log'

  ) SIZE 50M BLOCKSIZE 512,

  GROUP 2 (

    '/u01/oracle11R2/oradata/QL/onlinelog/o1_mf_2_7y53o2o3_.log',

    '/u01/oracle11R2/flash_recovery_area/QL/onlinelog/o1_mf_2_7y53o2s0_.log'

  ) SIZE 50M BLOCKSIZE 512,

  GROUP 3 (

    '/u01/oracle11R2/oradata/QL/onlinelog/o1_mf_3_7y53o7lq_.log',

    '/u01/oracle11R2/flash_recovery_area/QL/onlinelog/o1_mf_3_7y53o7pb_.log'

  ) SIZE 50M BLOCKSIZE 512

DATAFILE

  '/u01/oracle11R2/oradata/QL/datafile/o1_mf_system_7y53jbj5_.dbf',

  '/u01/oracle11R2/oradata/QL/datafile/o1_mf_sysaux_7y53jbkl_.dbf',

  '/u01/oracle11R2/oradata/QL/datafile/o1_mf_undotbs1_7y53jbl5_.dbf',

  '/u01/oracle11R2/oradata/QL/datafile/o1_mf_users_7y53jblv_.dbf'

CHARACTER SET AL32UTF8

;

6. 删除原来的控制文件，否则会报错

rm -rf /u01/oracle11R2/flash_recovery_area/QL/controlfile/o1_mf_7y53nt77_.ctl

rm -rf /u01/oracle11R2/oradata/QL/controlfile/o1_mf_7y53ns9w_.ctl

7. 将数据库使用修改后的pfile启动到nomount状态

SQL> startup nomount pfile='/u01/oracle11R2/product/11.2.0/db_1/dbs/initql.ora';

ORACLE instance started.

Total System Global Area 1068937216 bytes

Fixed Size    2235208 bytes

Variable Size  641729720 bytes

Database Buffers  419430400 bytes

Redo Buffers    5541888 bytes

8. 创建控制文件

SQL> CREATE CONTROLFILE set DATABASE "GRID" RESETLOGS ARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 (

    '/u01/oracle11R2/oradata/QL/onlinelog/o1_mf_1_7y53ny43_.log',

    '/u01/oracle11R2/flash_recovery_area/QL/onlinelog/o1_mf_1_7y53nybp_.log'

  ) SIZE 50M BLOCKSIZE 512,

  GROUP 2 (

    '/u01/oracle11R2/oradata/QL/onlinelog/o1_mf_2_7y53o2o3_.log',

    '/u01/oracle11R2/flash_recovery_area/QL/onlinelog/o1_mf_2_7y53o2s0_.log'

  ) SIZE 50M BLOCKSIZE 512,

  GROUP 3 (

    '/u01/oracle11R2/oradata/QL/onlinelog/o1_mf_3_7y53o7lq_.log',

    '/u01/oracle11R2/flash_recovery_area/QL/onlinelog/o1_mf_3_7y53o7pb_.log'

  ) SIZE 50M BLOCKSIZE 512

DATAFILE

  '/u01/oracle11R2/oradata/QL/datafile/o1_mf_system_7y53jbj5_.dbf',

  '/u01/oracle11R2/oradata/QL/datafile/o1_mf_sysaux_7y53jbkl_.dbf',

  '/u01/oracle11R2/oradata/QL/datafile/o1_mf_undotbs1_7y53jbl5_.dbf',

  '/u01/oracle11R2/oradata/QL/datafile/o1_mf_users_7y53jblv_.dbf'

CHARACTER SET AL32UTF8

 26  ;

Control file created.

9. 打开数据库

SQL> alter database open resetlogs;

Database altered.

SQL> select status from v$instance;

STATUS

------------

OPEN

SQL> show parameter db_name;

NAME     TYPE VALUE

------------------------------------ ----------- ------------------------------

db_name     string GRID

10. 由pfile生成sofile重启数据库实例

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22700344/viewspace-733793/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22700344/viewspace-733793/