实例讲解如何修改Oracle数据库名称的技巧

实例讲解如何修改Oracle数据库名称的技巧 在本例中小编将使用这款名为nid的自带工具其在oracle_home/bin目录中。在以下的 方法中我们假设登陆到数据库本机做。 1.1 sqlplus sys/password as sysdba 1.2 sqlshutdown immediate --先停掉数据库 1.3 sqlstartup mount --nid需要在mount状态下 才能做。 1.4sqlhost nid targetsys/password dbnamenew_dbname --nid是一个在操作系 统下执行的命令在sqlplus环境中用host 去调用操作系统环境下的命令。命令中当你 想将数据库名字改为SOFT568X时应写成dbnamesoft568X 1.5执行后程序会询问 Change database ID and database name SOFT568 to SOFT568X Y/N y 敲y确定。 之后 程序会对控制文件数据文件进行修改 Control File C:ORACLEORADATASOFT568CONTROL01.CTL - modified Control File C:ORACLEORADATASOFT568CONTROL02.CTL - modified Control File C:ORACLEORADATASOFT568CONTROL03.CTL - modified Datafile C:ORACLEORADATASOFT568 YSTEM01.DBF - dbid changed wrote new name Datafile C:ORACLEORADATASOFT568UNDOTBS01.DBF - dbid changed wrote new nam Datafile C:ORACLEORADATASOFT568INDX01.DBF - dbid changed wrote new name Datafile C:ORACLEORADATASOFT568TOOLS01.DBF - dbid changed wrote new name Datafile C:ORACLEORADATASOFT568USERS01.DBF - dbid changed wrote new name Datafile D:DATAFILEPORMALS_SPA.DBF - dbid changed wrote new name Datafile C:ORACLEORADATASOFT568OEM_REPOSITORY.DBF - dbid changed wrote ne name Datafile D:DATAFILEPORMALS_SPA_01.DBF - dbid changed wrote new name Datafile C:ORACLEORADATASOFT568TEMP01.DBF - dbid changed wrote new name Control File C:ORACLEORADATASOFT568CONTROL01.CTL - dbid changed wrote new name Control File C:ORACLEORADATASOFT568CONTROL02.CTL - dbid changed wrote new name Control File C:ORACLEORADATASOFT568CONTROL03.CTL - dbid changed wrote new name Database name changed to SOFT568X. Modify parameter file and generate a new password file before restarting. Database ID for database SOFT568X changed to 2321050327. All previous backups and archived redo logs for this database are unusable. Shut down database and open with RESETLOGS option. Succesfully changed database name and ID. DBNEWID - Completed succesfully. 1.6 停库再到 mount状态下改db_name: sqlshutdown immediate sqlstartup mount sqlalter system set db_namesoft568X scopespfile sqlshutdown immediate 1.7 重新创建sys的password文件 passwd文件通常放在oracle_home/database目录下文件命名形式为PWDsid.orasid为实 例名Service_name如当前的数据库名及service_name为soft568则passwd文件为 PWDsoft568.ora sqlhost orapwd filec:oracleora92databasepwdsoft568.ora password123456 entries5 要注意一下此时虽然数据库名已经改成soft568X了但 instance_name还是soft568所以passwd文件必须跟以前一样。否则会出错。 1.8 开启 数据库要open resetlogs sqlstartup mount sqlalter database open resetlogs 1.9 检查 sqlselect dbidname from vdatabase 2.改好数据库名后接着改instance_name 2.1如果是 windows系统要先把所有的oracle服务先关闭否则会出错。 2.2先将原来的 service_name删除: 在操作系统中进入cmd oradim -delete -sid soft568 2.3创建密码文 件 orapwd filec:/oracle/ora92/database/pwdsoft568X.ora password....entries 2.4创建一 个新的sid也就是你想改名的sid oradim -new -sid soft568X -intpwd 密码 -startmode a -pfile c:oracleora92databaseinitsoft568X.ora 当数据库启动时会在database目录中找 spfile如果spfile不在就找initSID.ora这个文件来顶。所以可以把pfile直接创建在这里。 方便数据库的启动。 2.5 进入oracle并创建spfile c: set oracle_sidsoft568X c:sqlplus sys/password as sysdba sqlcreate spfile from pilec:oracleora92databaseinitsoft568X.ora 2.6 reload listener c:lsnrctl reload 2.7 open resetlogs: 改数据库名字精简版 1.用oracle 自带的工具nid改数据库名 1.1 sqlplus sys/zhuzerp as sysdba 1.2 sqlshutdown immediate 1.3 sqlstartup mount 1.4 sqlhost nid targetsys/zhuzerp dbnameORCLERP 1.5 Change database ID and database name zhuzerp to ORCLERP Y/N y 1.6 sqlshutdown immediate sqlstartup mount sqlalter system set db_nameORCLERP scopespfile sqlshutdown immediate 1.7 重新创建sys的password文件 host orapwd fileD:OracledatabasePWDzhuzerp.ora passwordorclerp entries5 1.8 sqlstartup mount sqlalter database open resetlogs 1.9 sqlselect dbidname from vdatabase 2.更改 instance_name 2.1 net stop oracleservicezhuzerp 2.2 oradim -delete -sid zhuzerp 2.3 orapwd fileD:OracledatabasePWDORCLERP.ora passwordorclerp entries5 2.4 修改文 件中的db_name和instance_name D:Oracleadminsoft568pfileinit.ora.8252009155525 2.5 oradim -new -sid ORCLERP -intpwd orclerp -startmode a -pfile D:Oracleadminzhuzerppfileinit.ora 2.6 set oracle_sidorclerp 2.7 sqlplus sys/orclerp as sysdba 2.8 create spfile from pfileD:Oracleadminzhuzerppfileinit.ora 2.9 lsnrctl reload