使用Oracle如何修改数据库名
1.用oracle自带的工具nid改数据库名
在本例中,假设原来的数据库名为test,要改成testdb,原实例名(service_name,instance_name)test,要改成testdb.
nid是自带的工具,在oracle_home/bin目录中。以下方法假设登陆到数据库本机做。
1.1 sqlplus “sys/password as sysdba”
1.2 sql》shutdown immediate --先停掉数据库
1.3 sql》startup mount --nid需要在mount状态下才能做。
1.4sql》host nid target=sys/password dbname=new_dbname --nid是一个在操作系统下执行的命令,在sqlplus环境中用host 去调用操作系统环境下的命令。命令中,当你想将数据库名字改为TESTDB时,应写成dbname=testdb
1.5执行后程序会询问:
Change database ID and database name TEST to TESTDB? (Y/[N]) =》y
敲y确定。
之后程序会对控制文件,数据文件进行修改:
Control File C:ORACLEORADATATESTCONTROL01.CTL - modified
Control File C:ORACLEORADATATESTCONTROL02.CTL - modified
Control File C:ORACLEORADATATESTCONTROL03.CTL - modified
Datafile C:ORACLEORADATATEST YSTEM01.DBF - dbid changed, wrote new name
Datafile C:ORACLEORADATATESTUNDOTBS01.DBF - dbid changed, wrote new nam
Datafile C:ORACLEORADATATESTINDX01.DBF - dbid changed, wrote new name
Datafile C:ORACLEORADATATESTTOOLS01.DBF - dbid changed, wrote new name
Datafile C:ORACLEORADATATESTUSERS01.DBF - dbid changed, wrote new name
Datafile D:DATAFILEPORMALS_SPA.DBF - dbid changed, wrote new name
Datafile C:ORACLEORADATATESTOEM_REPOSITORY.DBF - dbid changed, wrote ne
name
Datafile D:DATAFILEPORMALS_SPA_01.DBF - dbid changed, wrote new name
Datafile C:ORACLEORADATATESTTEMP01.DBF - dbid changed, wrote new name
Control File C:ORACLEORADATATESTCONTROL01.CTL - dbid changed, wrote new
name
Control File C:ORACLEORADATATESTCONTROL02.CTL - dbid changed, wrote new
name
Control File C:ORACLEORADATATESTCONTROL03.CTL - dbid changed, wrote new
name
Database name changed to TESTDB.
Modify parameter file and generate a new password file before restarting.
Database ID for database TESTDB changed to .
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:
sql》shutdown immediate;
sql》startup mount
sql》alter system set db_name=testdb scope=spfile;
sql》shutdown immediate;
1.7 重新创建sys的password文件:
passwd文件通常放在oracle_home/database目录下,文件命名形式为PWDsid.ora,sid为实例名(Service_name),如当前的数据库名及service_name为test,则passwd文件为PWDtest.ora
sql》host orapwd file=c:oracleora92databasepwdtest.ora password= entries=5
要注意一下,此时虽然数据库名已经改成testdb了,但instance_name还是test,所以,passwd文件必须跟以前一样。否则会出错。
1.8 开启数据库:(要open resetlogs)
sql》startup mount
sql》alter database open resetlogs;
1.9 检查:
sql》select dbid,name from v$database;
2.改好数据库名后,接着改instance_name
2.1如果是windows系统,要先把所有的oracle服务先关闭,否则会出错。
2.2先将原来的service_name删除:
在操作系统中,进入cmd,
oradim -delete -sid test
2.3创建密码文件
orapwd file=c:/oracle/ora92/database/pwdtestdb.ora password=。…entries=
2.4创建一个新的sid,也就是你想改名的sid:
oradim -new -sid testdb -intpwd 密码 -startmode a -pfile c:oracleora92databaseinittestdb.ora
当数据库启动时,会在database目录中找spfile,如果spfile不在就找initSID.ora这个文件来顶。所以,可以把pfile直接创建在这里。方便数据库的启动。
2.5 进入oracle并创建spfile:
c: set oracle_sid=testdb
c:sqlplus “sys/password as sysdba”
sql》create spfile from pile=‘c:oracleora92databaseinittestdb.ora’;
2.6 reload listener:
c:lsnrctl reload
2.7 open resetlogs:
====================================================
============改数据库名字==精简版================
1.用oracle自带的工具nid改数据库名
1.1 sqlplus “sys/zhuzerp as sysdba”
1.2 sql》shutdown immediate
1.3 sql》startup mount
1.4 sql》host nid target=sys/zhuzerp dbname=ORCLERP
1.5 Change database ID and database name zhuzerp to ORCLERP? (Y/[N]) =》y
1.6 sql》shutdown immediate
sql》startup mount
sql》alter system set db_name=ORCLERP scope=spfile;
sql》shutdown immediate
1.7 重新创建sys的password文件
host orapwd file=D:\Oracle\database\PWDzhuzerp.ora password=orclerp entries=5
1.8 sql》startup mount
sql》alter database open resetlogs;
1.9 sql》select dbid,name from v$database;
2.更改instance_name
2.1 net stop oracleservicezhuzerp
2.2 oradim -delete -sid zhuzerp
2.3 orapwd file=D:\Oracle\database\PWDORCLERP.ora password=orclerp entries=5
2.4 修改文件中的db_name和instance_name
D:\Oracle\admin\test\pfile\init.ora.525
2.5 oradim -new -sid ORCLERP -intpwd orclerp -startmode a -pfile D:\Oracle\admin\zhuzerp\pfile\init.ora
2.6 set oracle_sid=orclerp
2.7 sqlplus “sys/orclerp as sysdba”
2.8 create spfile from pfile=‘D:\Oracle\admin\zhuzerp\pfile\init.ora’;
2.9 lsnrctl reload
在本例中,假设原来的数据库名为test,要改成testdb,原实例名(service_name,instance_name)test,要改成testdb.
nid是自带的工具,在oracle_home/bin目录中。以下方法假设登陆到数据库本机做。
1.1 sqlplus “sys/password as sysdba”
1.2 sql》shutdown immediate --先停掉数据库
1.3 sql》startup mount --nid需要在mount状态下才能做。
1.4sql》host nid target=sys/password dbname=new_dbname --nid是一个在操作系统下执行的命令,在sqlplus环境中用host 去调用操作系统环境下的命令。命令中,当你想将数据库名字改为TESTDB时,应写成dbname=testdb
1.5执行后程序会询问:
Change database ID and database name TEST to TESTDB? (Y/[N]) =》y
敲y确定。
之后程序会对控制文件,数据文件进行修改:
Control File C:ORACLEORADATATESTCONTROL01.CTL - modified
Control File C:ORACLEORADATATESTCONTROL02.CTL - modified
Control File C:ORACLEORADATATESTCONTROL03.CTL - modified
Datafile C:ORACLEORADATATEST YSTEM01.DBF - dbid changed, wrote new name
Datafile C:ORACLEORADATATESTUNDOTBS01.DBF - dbid changed, wrote new nam
Datafile C:ORACLEORADATATESTINDX01.DBF - dbid changed, wrote new name
Datafile C:ORACLEORADATATESTTOOLS01.DBF - dbid changed, wrote new name
Datafile C:ORACLEORADATATESTUSERS01.DBF - dbid changed, wrote new name
Datafile D:DATAFILEPORMALS_SPA.DBF - dbid changed, wrote new name
Datafile C:ORACLEORADATATESTOEM_REPOSITORY.DBF - dbid changed, wrote ne
name
Datafile D:DATAFILEPORMALS_SPA_01.DBF - dbid changed, wrote new name
Datafile C:ORACLEORADATATESTTEMP01.DBF - dbid changed, wrote new name
Control File C:ORACLEORADATATESTCONTROL01.CTL - dbid changed, wrote new
name
Control File C:ORACLEORADATATESTCONTROL02.CTL - dbid changed, wrote new
name
Control File C:ORACLEORADATATESTCONTROL03.CTL - dbid changed, wrote new
name
Database name changed to TESTDB.
Modify parameter file and generate a new password file before restarting.
Database ID for database TESTDB changed to .
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:
sql》shutdown immediate;
sql》startup mount
sql》alter system set db_name=testdb scope=spfile;
sql》shutdown immediate;
1.7 重新创建sys的password文件:
passwd文件通常放在oracle_home/database目录下,文件命名形式为PWDsid.ora,sid为实例名(Service_name),如当前的数据库名及service_name为test,则passwd文件为PWDtest.ora
sql》host orapwd file=c:oracleora92databasepwdtest.ora password= entries=5
要注意一下,此时虽然数据库名已经改成testdb了,但instance_name还是test,所以,passwd文件必须跟以前一样。否则会出错。
1.8 开启数据库:(要open resetlogs)
sql》startup mount
sql》alter database open resetlogs;
1.9 检查:
sql》select dbid,name from v$database;
2.改好数据库名后,接着改instance_name
2.1如果是windows系统,要先把所有的oracle服务先关闭,否则会出错。
2.2先将原来的service_name删除:
在操作系统中,进入cmd,
oradim -delete -sid test
2.3创建密码文件
orapwd file=c:/oracle/ora92/database/pwdtestdb.ora password=。…entries=
2.4创建一个新的sid,也就是你想改名的sid:
oradim -new -sid testdb -intpwd 密码 -startmode a -pfile c:oracleora92databaseinittestdb.ora
当数据库启动时,会在database目录中找spfile,如果spfile不在就找initSID.ora这个文件来顶。所以,可以把pfile直接创建在这里。方便数据库的启动。
2.5 进入oracle并创建spfile:
c: set oracle_sid=testdb
c:sqlplus “sys/password as sysdba”
sql》create spfile from pile=‘c:oracleora92databaseinittestdb.ora’;
2.6 reload listener:
c:lsnrctl reload
2.7 open resetlogs:
====================================================
============改数据库名字==精简版================
1.用oracle自带的工具nid改数据库名
1.1 sqlplus “sys/zhuzerp as sysdba”
1.2 sql》shutdown immediate
1.3 sql》startup mount
1.4 sql》host nid target=sys/zhuzerp dbname=ORCLERP
1.5 Change database ID and database name zhuzerp to ORCLERP? (Y/[N]) =》y
1.6 sql》shutdown immediate
sql》startup mount
sql》alter system set db_name=ORCLERP scope=spfile;
sql》shutdown immediate
1.7 重新创建sys的password文件
host orapwd file=D:\Oracle\database\PWDzhuzerp.ora password=orclerp entries=5
1.8 sql》startup mount
sql》alter database open resetlogs;
1.9 sql》select dbid,name from v$database;
2.更改instance_name
2.1 net stop oracleservicezhuzerp
2.2 oradim -delete -sid zhuzerp
2.3 orapwd file=D:\Oracle\database\PWDORCLERP.ora password=orclerp entries=5
2.4 修改文件中的db_name和instance_name
D:\Oracle\admin\test\pfile\init.ora.525
2.5 oradim -new -sid ORCLERP -intpwd orclerp -startmode a -pfile D:\Oracle\admin\zhuzerp\pfile\init.ora
2.6 set oracle_sid=orclerp
2.7 sqlplus “sys/orclerp as sysdba”
2.8 create spfile from pfile=‘D:\Oracle\admin\zhuzerp\pfile\init.ora’;
2.9 lsnrctl reload
新闻详情