首页 > 数据库 > Oracle > 正文
修改Oracle 10G数据库名和DBID
2011-07-16       个评论      
收藏    我要投稿

 

 

本讲内容主要包括
1:数据库更名
2:数据库更名,同时修改DBID

DBID means database identifier in Oracle.The database identifier is a unique identifier. This is found in all datafile headers.The database identifier is used to identify the database a file belongs to. The database identifier is also found in the control files. It can therefore be gotten without access to the datafiles.The database identifier is important when the spfiles or controlfiles must be recovered. 
DBID是数据库的标识符,具有唯一性,在数据文件的文件头上存在dbid,用来标识数据文件所属的数据库,在控制文件中也存有DBID,当spfile和控制文件需要进行恢复的时候,就非常需要DBID

一:数据库更名

SQL> select dbid,name from v$database;

DBID NAME ---------- ---------------------------

4061806388 ORA10G

 

1.1 备份控制文件脚本

SQL> alter database backup controlfile to trace as '/home/oracle/control1.sql'; Database altered. [oracle@rhel6 ~]$ cat control1.sql CREATE CONTROLFILE set DATABASE "ORCL" RESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 ( '/u01/app/oradata/orcl/redo01_a.log', '/u01/app/oradata/orcl/redo01_b.log' ) SIZE 50M, GROUP 2 ( '/u01/app/oradata/orcl/redo02_a.log', '/u01/app/oradata/orcl/redo02_b.log' ) SIZE 50M, GROUP 3 ( '/u01/app/oradata/orcl/redo03_a.log', '/u01/app/oradata/orcl/redo03_b.log' ) SIZE 50M -- STANDBY LOGFILE DATAFILE '/u01/app/oradata/orcl/system01.dbf', '/u01/app/oradata/orcl/undotbs01.dbf', '/u01/app/oradata/orcl/sysaux01.dbf', '/u01/app/oradata/orcl/users01.dbf', '/u01/app/oradata/orcl/example01.dbf', '/u01/app/oradata/orcl/rman01.dbf', '/u01/app/oradata/orcl/sjb02.dbf', '/u01/app/oradata/orcl/sjb01.dbf', '/u01/app/oradata/orcl/t_drop01.dbf', '/u01/app/oradata/orcl/undotbs201.dbf' CHARACTER SET AL32UTF8 ;

1.2 干净的关闭数据库和监听器,dbconsole

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

[oracle@rhel6 ~]$ lsnrctl stop [oracle@rhel6 ~]$ emctl stop dbconsole

1.3 修改相关目录名和文件名

[oracle@rhel6 ~]$ mv /u01/app/oradata/ora10g/ /u01/app/oradata/orcl [oracle@rhel6 ~]$ mv /u01/app/flash_recovery_area/ORA10G/ /u01/app/flash_recovery_area/ORCL [oracle@rhel6 ~]$ mv /u01/app/admin/ora10g/ /u01/app/admin/orcl [oracle@rhel6 ~]$ cd $ORACLE_HOME/dbs [oracle@rhel6 dbs]$ mv orapwora10g orapworcl [oracle@rhel6 dbs]$ mv spfileora10g.ora spfileorcl.ora [oracle@rhel6 dbs]$ export ORACLE_SID=orcl [oracle@rhel6 dbs]$ sqlplus /nolog SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 15 15:43:34 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. SQL> conn /as sysdba Connected to an idle instance. SQL> create pfile from spfile; File created. SQL> exit Disconnected [oracle@rhel6 dbs]$ sed -i 's/ora10g/orcl/g' initorcl.ora [oracle@rhel6 ~]$ mv /u01/app/oradata/orcl/control0* /tmp

1.4 重新创建控制文件,恢复数据库,完成数据库的更名操作

[oracle@rhel6 dbs]$ sqlplus /nolog SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 15 15:45:32 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. SQL> conn /as sysdba Connected to an idle instance. SQL> create spfile from pfile; File created. SQL> @/home/oracle/control1.sql; Control file created. SQL> recover database using backup controlfile until cancel; ORA-00279: change 9094358 generated at 07/15/2011 15:32:20 needed for thread 1 ORA-00289: suggestion : /u01/app/flash_recovery_area/ORCL/archivelog/2011_07_15/o1_mf_1_3_%u_.arc ORA-00280: change 9094358 for thread 1 is in sequence #3 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} ORA-00308: cannot open archived log '/u01/app/flash_recovery_area/ORCL/archivelog/2011_07_15/o1_mf_1_3_%u_.arc' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 SQL> recover database using backup controlfile until cancel; ORA-00279: change 9094358 generated at 07/15/2011 15:32:20 needed for thread 1 ORA-00289: suggestion : /u01/app/flash_recovery_area/ORCL/archivelog/2011_07_15/o1_mf_1_3_%u_.arc ORA-00280: change 9094358 for thread 1 is in sequence #3 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} cancel Media recovery cancelled. SQL> alter database open resetlogs; Database altered. SQL> select dbid,name from v$database; DBID NAME ---------- --------------------------- 4061806388 ORCL

 

1.5 修改ORACLE_SID,tnsnames.ora和dbconsole配置

[oracle@rhel6 ~]$ source .bash_profile [oracle@rhel6 ~]$ env |grep ORACLE_SID ORACLE_SID=orcl [oracle@rhel6 ~]$ vi /u01/app/oracle/network/admin/tnsnames.ora ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.50.4)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl.766.com) ) ) 重新配置dbconsole,需要借助DBCA工具 [root@rhel6 ~]# tail -n 1 /etc/oratab orcl:/u01/app/oracle:N: [oracle@rhel6 ~]$ emctl status dbconsole TZ set to PRC Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0 Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved. http://rhel6.766.com:5500/em/console/aboutApplication Oracle Enterprise Manager 10g is running. ------------------------------------------------------------------ Logs are generated in directory /u01/app/oracle/rhel6.766.com_orcl/sysman/log [oracle@rhel6 ~]$ netstat -ntpl |grep :5500 (Not all processes could be identified, non-owned process info will not be shown, you would have to be root to see it all.) tcp 0 0 0.0.0.0:5500 0.0.0.0:* LISTEN 13819/java

 

\

\

 

\

 

二:数据库更名,同时修改DBID
2.1 关闭数据库,将数据库启动到mount状态


  1. 		SQL> shutdown immediate  
  2. Database closed.  
  3. Database dismounted.  
  4. ORACLE instance shut down.  
  5. SQL> exit  
  6.  
  7. SQL> startup mount  
  8. ORACLE instance started.  
  9.  
  10. Total System Global Area  629145600 bytes  
  11. Fixed Size                  2022824 bytes  
  12. Variable Size             218104408 bytes  
  13. Database Buffers          402653184 bytes  
  14. Redo Buffers                6365184 bytes  
  15. Database mounted. 

 

2.2 使用nid命令修改数据库名和dbid


  1. 		[oracle@rhel6 dbs]$ nid  dbname=orcl2 target=sys/oracle   
  2. DBNEWID: Release 10.2.0.1.0 - Production on Fri Jul 15 17:19:29 2011  
  3. Copyright (c) 1982, 2005, Oracle.  All rights reserved.  
  4. Connected to database ORCL (DBID=4061806388)  
  5. Connected to server version 10.2.0  
  6.  
  7. Control Files in database:  
  8.     /u01/app/oradata/orcl/control01.ctl  
  9.     /u01/app/oradata/orcl/control02.ctl  
  10.     /u01/app/oradata/orcl/control03.ctl  
  11.  
  12. Change database ID and database name ORCL to ORCL2? (Y/[N]) => Y  
  13.  
  14. Proceeding with operation  
  15. Changing database ID from 4061806388 to 737391906  
  16. Changing database name from ORCL to ORCL2  
  17.     Control File /u01/app/oradata/orcl/control01.ctl - modified  
  18.     Control File /u01/app/oradata/orcl/control02.ctl - modified  
  19.     Control File /u01/app/oradata/orcl/control03.ctl - modified  
  20.     Datafile /u01/app/oradata/orcl/system01.dbf - dbid changed, wrote new name  
  21.     Datafile /u01/app/oradata/orcl/undotbs01.dbf - dbid changed, wrote new name  
  22.     Datafile /u01/app/oradata/orcl/sysaux01.dbf - dbid changed, wrote new name  
  23.     Datafile /u01/app/oradata/orcl/users01.dbf - dbid changed, wrote new name  
  24.     Datafile /u01/app/oradata/orcl/example01.dbf - dbid changed, wrote new name  
  25.     Datafile /u01/app/oradata/orcl/rman01.dbf - dbid changed, wrote new name  
  26.     Datafile /u01/app/oradata/orcl/sjb02.dbf - dbid changed, wrote new name  
  27.     Datafile /u01/app/oradata/orcl/sjb01.dbf - dbid changed, wrote new name  
  28.     Datafile /u01/app/oradata/orcl/t_drop01.dbf - dbid changed, wrote new name  
  29.     Datafile /u01/app/oradata/orcl/undotbs201.dbf - dbid changed, wrote new name  
  30.     Datafile /u01/app/oradata/orcl/temp01.dbf - dbid changed, wrote new name  
  31.     Control File /u01/app/oradata/orcl/control01.ctl - dbid changed, wrote new name  
  32.     Control File /u01/app/oradata/orcl/control02.ctl - dbid changed, wrote new name  
  33.     Control File /u01/app/oradata/orcl/control03.ctl - dbid changed, wrote new name  
  34.     Instance shut down  
  35.  
  36. Database name changed to ORCL2.  
  37. Modify parameter file and generate a new password file before restarting.  
  38. Database ID for database ORCL2 changed to 737391906.  
  39. All previous backups and archived redo logs for this database are unusable.  
  40. Database is not aware of previous backups and archived logs in Recovery Area.  
  41. Database has been shutdown, open database with RESETLOGS option.  
  42. Succesfully changed database name and ID.  
  43. DBNEWID - Completed succesfully. 

2.3 修改相关目录和文件


  1. 		[oracle@rhel6 ~]$ mv /u01/app/oradata/orcl/ /u01/app/oradata/orcl2  
  2. [oracle@rhel6 ~]$ mv /u01/app/flash_recovery_area/ORCL/ /u01/app/flash_recovery_area/ORCL2  
  3. [oracle@rhel6 ~]$ mv /u01/app/admin/orcl/ /u01/app/admin/orcl2  
  4. [oracle@rhel6 ~]$ cd $ORACLE_HOME/dbs  
  5. [oracle@rhel6 dbs]$ mv initorcl.ora  initorcl2.ora   
  6. [oracle@rhel6 dbs]$ mv spfileorcl.ora  spfileorcl2.ora   
  7. [oracle@rhel6 dbs]$ mv orapworcl  orapworcl2  
  8. [oracle@rhel6 dbs]$ sed -i 's/orcl/orcl2/g' initorcl2.ora   
  9. [oracle@rhel6 dbs]$ export ORACLE_SID=orcl2 

2.4 将数据库启动到mount状态,修改控制文件中数据文件和日志文件的路径


  1. 		[oracle@rhel6 ~]$ sqlplus /nolog  
  2. SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 15 17:05:39 2011  
  3. Copyright (c) 1982, 2005, Oracle.  All rights reserved.  
  4.  
  5. SQL> conn /as sysdba  
  6. Connected to an idle instance.  
  7. SQL> create spfile from pfile;  
  8. File created  
  9.  
  10. SQL> startup mount  
  11. ORACLE instance started.  
  12.  
  13. Total System Global Area  629145600 bytes  
  14. Fixed Size                  2022824 bytes  
  15. Variable Size             218104408 bytes  
  16. Database Buffers          402653184 bytes  
  17. Redo Buffers                6365184 bytes  
  18. Database mounted.  
  19.  
  20. SQL> alter database rename file '/u01/app/oradata/orcl/system01.dbf' to '/u01/app/oradata/orcl2/system01.dbf';  
  21. Database altered  
  22. SQL> alter database rename file '/u01/app/oradata/orcl/undotbs01.dbf' to '/u01/app/oradata/orcl2/undotbs01.dbf';  
  23. Database altered.  
  24.  
  25. SQL> alter database rename file '/u01/app/oradata/orcl/sysaux01.dbf' to '/u01/app/oradata/orcl2/sysaux01.dbf';  
  26. Database altered  
  27.  
  28. SQL> alter database rename file '/u01/app/oradata/orcl/users01.dbf' to '/u01/app/oradata/orcl2/users01.dbf';  
  29. Database altered.  
  30.  
  31. SQL> alter database rename file '/u01/app/oradata/orcl/example01.dbf' to '/u01/app/oradata/orcl2/example01.dbf';  
  32. Database altered.  
  33.  
  34. SQL> alter database rename file '/u01/app/oradata/orcl/rman01.dbf' to '/u01/app/oradata/orcl2/rman01.dbf';  
  35. Database altered.  
  36.  
  37. SQL> alter database rename file '/u01/app/oradata/orcl/sjb01.dbf' to '/u01/app/oradata/orcl2/sjb01.dbf';  
  38. Database altered.  
  39.  
  40. SQL> alter database rename file '/u01/app/oradata/orcl/sjb02.dbf' to '/u01/app/oradata/orcl2/sjb02.dbf';  
  41. Database altered  
  42.  
  43.  
  44. SQL> alter database rename file '/u01/app/oradata/orcl/t_drop01.dbf' to '/u01/app/oradata/orcl2/t_drop01.dbf';  
  45. Database altered.  
  46.  
  47. SQL> alter database rename file '/u01/app/oradata/orcl/undotbs201.dbf' to '/u01/app/oradata/orcl2/undotbs201.dbf';  
  48. Database altered  
  49.  
  50. SQL> alter database rename file '/u01/app/oradata/orcl/temp01.dbf' to '/u01/app/oradata/orcl2/temp01.dbf';  
  51. Database altered.  
  52.  
  53.  
  54. SQL> alter database rename file '/u01/app/oradata/orcl/redo01_a.log' to '/u01/app/oradata/orcl2/redo01_a.log';  
  55. Database altered.  
  56.  
  57. SQL> alter database rename file '/u01/app/oradata/orcl/redo01_b.log' to '/u01/app/oradata/orcl2/redo01_b.log';  
  58. Database altered.  
  59.  
  60. SQL> alter database rename file '/u01/app/oradata/orcl/redo02_a.log' to '/u01/app/oradata/orcl2/redo02_a.log';  
  61. Database altered.  
  62.  
  63. SQL> alter database rename file '/u01/app/oradata/orcl/redo02_b.log' to '/u01/app/oradata/orcl2/redo02_b.log';  
  64. Database altered.  
  65.  
  66. SQL> alter database rename file '/u01/app/oradata/orcl/redo03_a.log' to '/u01/app/oradata/orcl2/redo03_a.log';  
  67. Database altered.  
  68.  
  69. SQL> alter database rename file '/u01/app/oradata/orcl/redo03_b.log' to '/u01/app/oradata/orcl2/redo03_b.log';  
  70. Database altered. 

2.5 打开数据库并验证


  1. 		SQL> alter database open resetlogs;  
  2. Database altered.  
  3.  
  4. SQL> select name,dbid from v$database;  
  5.  
  6. NAME                              DBID  
  7. --------------------------- ----------  
  8. ORCL2                        737391906 

总结:数据库更名后,原来所有的备份信息和归档全部无效,数据库需要以resetlogs方式打开,意味着数据库需要换化身,如果方法一更改数据库名,需要注意临时文件的添加,同时可以使用emca命令重新配置dbconsole

本文出自 “月牙天冲” 博客

点击复制链接 与好友分享!回本站首页
相关TAG标签 数据库
上一篇:Oracle中的NVL,NVL2,NULLIF,COALESCE通用函数
下一篇:oracle 的workload的权威解释
相关文章
图文推荐
文章
推荐
点击排行

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训
版权所有: 红黑联盟--致力于做实用的IT技术学习网站