oracle 11g如何修改数据库名字和实例名字
这篇文章主要为大家展示了“oracle 11g如何修改数据库名字和实例名字”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下“oracle 11g如何修改数据库名字和实例名字”这篇文章吧。
第一阶段：改sid
1、登录数据库查看先前的sid，总共三步，
[javascript]viewplaincopyprint?
[oracle@localhost~]$sqlplus/assysdba
SQL*Plus:Release11.2.0.1.0ProductiononSatOct116:51:352011
Copyright(c)1982,2009,Oracle.Allrightsreserved.
Connectedtoanidleinstance
SQL>startup
ORACLEinstancestarted.
TotalSystemGlobalArea539848704bytes
FixedSize1337748bytes
VariableSize360711788bytes
DatabaseBuffers171966464bytes
RedoBuffers5832704bytes
Databasemounted.
Databaseopened.
SQL>selectinstancefromv$thread;
INSTANCE
--------------------------------------------------------------------------------
orcl
2、关闭数据库
[sql]viewplaincopyprint?
SQL>shutdownimmediate;
Databaseclosed.
Databasedismounted.
ORACLEinstanceshutdown.
SQL>exit
DisconnectedfromOracleDatabase11gEnterpriseEditionRelease11.2.0.1.0-Production
WiththePartitioning,OLAP,DataMiningandRealApplicationTestingoptions
3、编辑/etc/oratab文件，把所有orcl换成test，大部分情况其实就一条。----这几步不需要管的。
[sql]viewplaincopyprint?
[oracle@localhost~]$vim/etc/oratab
4、更改oracle用户的.bash_profile文件,把orcl改成test
[sql]viewplaincopyprint?
[oracle@localhost~]$vim.bash_profile
5、使改好的.bash_profile文件生效
[sql]viewplaincopyprint?
[oracle@localhost~]$..bash_profile
7、查看系统环境变量
[sql]viewplaincopyprint?
[oracle@localhost~]$env|grepORACLE
ORACLE_UNQNAME=test
ORACLE_SID=test
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
效果已经生成。
8、进入$ORACLE_HOME/dbs查看目录，看那些有orcl,改成test
[sql]viewplaincopyprint?
[oracle@localhost~]$cd$ORACLE_HOME/dbs
[oracle@localhostdbs]$ll
总计24
-rw-rw----1oracleoinstall154410-0116:55hc_orcl.dat
-rw-r--r--1oracleoinstall28512009-05-15init.ora
-rw-r-----1oracleoinstall2409-2820:57lkORCL
-rw-r-----1oracleoinstall153609-2909:42orapworcl
drwx------2oracleoinstall409609-2820:55peshm_orcl_0
-rw-r-----1oracleoinstall256010-0116:53spfileorcl.ora
9、更改文件名orcr>>test，ORCL>>test，命令如下：
[sql]viewplaincopyprint?
[oracle@localhostdbs]$mvhc_orcl.dathc_test.dat
[oracle@localhostdbs]$mvorapworclorapwtest
[oracle@localhostdbs]$mvlkORCLlkTEST
[oracle@localhostdbs]$mvpeshm_orcl_0/peshm_test_0/
[oracle@localhostdbs]$mvspfileorcl.oraspfiletest.ora
10、重行生成密码文件，并查看
[sql]viewplaincopyprint?
[oracle@localhostdbs]$orapwdfile=$ORACLE_HOME/dbs/orapw$ORACLE_SIDpassword=sysentries=5force=y
[oracle@localhostdbs]$ls-lrtorap*
-rw-r-----1oracleoinstall204810-0117:02orapwtest
11、登录数据库，并查看实例名字，结果表明sid已由orcl变成test了
[sql]viewplaincopyprint?
[oracle@localhostdbs]$sqlplus/assysdba
SQL*Plus:Release11.2.0.1.0ProductiononSatOct117:03:252011
Copyright(c)1982,2009,Oracle.Allrightsreserved.
Connectedtoanidleinstance.
SQL>startup
ORACLEinstancestarted.
TotalSystemGlobalArea539848704bytes
FixedSize1337748bytes
VariableSize327157356bytes
DatabaseBuffers205520896bytes
RedoBuffers5832704bytes
Databasemounted.
Databaseopened.
SQL>selectinstancefromv$thread
2;
INSTANCE
--------------------------------------------------------------------------------
test
第二部分：不用退出登录，接着开始第二部分，更改数据库名dbname
2.1备份控制文件
[sql]viewplaincopyprint?
SQL>alterdatabasebackupcontrolfiletotraceresetlogs;
Databasealtered.
2.2关闭并退出数据库
[sql]viewplaincopyprint?
SQL>
Databaseclosed.
Databasedismounted.
ORACLEinstanceshutdown.
SQL>exit
DisconnectedfromOracleDatabase11gEnterpriseEditionRelease11.2.0.1.0-Production
WiththePartitioning,OLAP,DataMiningandRealApplicationTestingoptions
2.3orcale11.2g的控制文件的备份目录为
/u01/app/oracle/diag/rdbms/ORCL/test/trace###告警日志里面,注意rdbms后面应该是ORCL，因为数据库名字还没有改变。
[sql]viewplaincopyprint?
[oracle@localhosttrace]$ls-lrt
总计80
-rw-r-----1oracleoinstall25610-0117:03test_ora_5286.trm
-rw-r-----1oracleoinstall1439310-0117:03test_ora_5286.trc
-rw-r-----1oracleoinstall6810-0117:03test_mman_5334.trm
-rw-r-----1oracleoinstall85210-0117:03test_mman_5334.trc
-rw-r-----1oracleoinstall6110-0117:03test_cjq0_5437.trm
-rw-r-----1oracleoinstall99310-0117:03test_cjq0_5437.trc
-rw-r-----1oracleoinstall8210-0117:04test_dbrm_5328.trm
-rw-r-----1oracleoinstall117210-0117:04test_dbrm_5328.trc
-rw-r-----1oracleoinstall9010-0117:05test_vktm_5320.trm
-rw-r-----1oracleoinstall123310-0117:05test_vktm_5320.trc
-rw-r-----1oracleoinstall7210-0117:05test_vkrm_5439.trm
-rw-r-----1oracleoinstall99610-0117:05test_vkrm_5439.trc
-rw-r-----1oracleoinstall17410-0117:05test_ora_5419.trm
-rw-r-----1oracleoinstall589410-0117:05test_ora_5419.trc
-rw-r-----1oracleoinstall596910-0117:05alert_test.log
[oracle@localhosttrace]$vimalert_test.log
[sql]viewplaincopyprint?
可以在alter_test.log里找到contolfile的备份trc，<em>sid_</em>ora_nnnn.trc最新的一个就是。
alter_test.log里面有这样一行字样，告诉你哪个是控制备份文件
[sql]viewplaincopyprint?
Backupcontrolfilewrittentotracefile/u01/app/oracle/diag/rdbms/orcl/test/trace/test_ora_5419.trc
2.5复制一份
[sql]viewplaincopyprint?
[oracle@localhosttrace]$cptest_ora_5419.trctest.sql
2.6编辑test.sql,也就是test_ora_5419.trc的复制品。
1）查找STARTUPNOMOUNT语句，将这一行上面的所有行都删除
2）查找所有以--开始的行，把这些行删除
3）查找所有的orcl修改为test，所有的ORCL修改为test###这个步骤不做也可以，做的目的就是为了符合规矩，实际上控制文件数据文件的目录是可以随便起的。
4）找到CREATECONTROLFILEREUSEDATABASE...语句，将其中的REUSE修改为SET
5）找到RECOVERDATABASEUSINGBACKUPCONTROLFILE语句，将其用双横线（--）注释掉
结果如下：
[sql]viewplaincopyprint?
STARTUPNOMOUNT
CREATECONTROLFILESETDATABASE"test"RESETLOGSNOARCHIVELOG
MAXLOGFILES16
MAXLOGMEMBERS3
MAXDATAFILES100
MAXINSTANCES8
MAXLOGHISTORY292
LOGFILE
GROUP1'/u01/app/oracle/oradata/test/redo01.log'SIZE50MBLOCKSIZE512,
GROUP2'/u01/app/oracle/oradata/test/redo02.log'SIZE50MBLOCKSIZE512,
GROUP3'/u01/app/oracle/oradata/test/redo03.log'SIZE50MBLOCKSIZE512
--STANDBYLOGFILE
DATAFILE
'/u01/app/oracle/oradata/test/system01.dbf',
'/u01/app/oracle/oradata/test/sysaux01.dbf',
'/u01/app/oracle/oradata/test/undotbs01.dbf',
'/u01/app/oracle/oradata/test/users01.dbf',
'/u01/app/oracle/oradata/test/example01.dbf'
CHARACTERSETZHS16GBK
;
--RECOVERDATABASEUSINGBACKUPCONTROLFILE
ALTERDATABASEOPENRESETLOGS;
ALTERTABLESPACETEMPADDTEMPFILE'/u01/app/oracle/oradata/test/temp01.dbf'
SIZE30408704REUSEAUTOEXTENDONNEXT655360MAXSIZE32767M;
2.7生成配置文件
[sql]viewplaincopyprint?
[oracle@localhosttrace]$sqlplus /assysdba
SQL*Plus:Release11.2.0.1.0ProductiononSatOct117:12:482011
Copyright(c)1982,2009,Oracle.Allrightsreserved.
Connectedtoanidleinstance.
SQL>createpfile fromspfile;
Filecreated.
SQL>exit
Disconnected
2.8目录更改，这里和oracle10g不一样，要注意。
[sql]viewplaincopyprint?
[oracle@localhost~]$cd/u01/app/oracle/
[oracle@localhostoracle]$ls
admincfgtoollogscheckpointsdiagflash_recovery_areaoradataproduct
[oracle@localhostoracle]$cdflash_recovery_area/
[oracle@localhostflash_recovery_area]$ls
orclORCL
[oracle@localhostflash_recovery_area]$mvorcl/ test/
[oracle@localhostflash_recovery_area]$mvORCL/ test/
[oracle@localhostflash_recovery_area]$cd..
[oracle@localhostoracle]$ls
admincfgtoollogscheckpointsdiagflash_recovery_areaoradataproduct
[oracle@localhostoracle]$cdoradata/
[oracle@localhostoradata]$ls
orcl
[oracle@localhostoradata]$mvorcl/test/
[oracle@localhostoradata]$ls
test
[oracle@localhostoradata]$cd..
[oracle@localhostoracle]$ls
admincfgtoollogscheckpointsdiagflash_recovery_areaoradataproduct
[oracle@localhostoracle]$cddiag/
[oracle@localhostdiag]$ls
rdbmstnslsnr
[oracle@localhostdiag]$cdrdbms/
[oracle@localhostrdbms]$ls
orcl
[oracle@localhostrdbms]$mvorcl/test/
[oracle@localhostrdbms]$ls
test
[oracle@localhostrdbms]$cdtest
[oracle@localhosttest]$ls
i_1.miftestorcl
[oracle@localhosttest]$cd..
[oracle@localhostrdbms]$cd..
[oracle@localhostdiag]$ls
rdbmstnslsnr
[oracle@localhostdiag]$cd..
[oracle@localhostoracle]$cdadmin/
[oracle@localhostadmin]$ls
orcl
[oracle@localhostadmin]$cdorcl/
[oracle@localhostorcl]$ls
adumpdpdumppfile
[oracle@localhostorcl]$cd..
[oracle@localhostadmin]$mvorcl/ test/
[oracle@localhostadmin]$ls
test
[oracle@localhostadmin]$sqlplus/assysdba
SQL*Plus:Release11.2.0.1.0ProductiononSatOct117:18:022011
Copyright(c)1982,2009,Oracle.Allrightsreserved.
Connectedtoanidleinstance.
SQL>
2.9删除的控制文件。
[sql]viewplaincopyprint?
[oracle@localhostoradata]$cdtest/
[oracle@localhosttest]$ls
control01.ctlredo01.logredo03.logsystem01.dbfundotbs01.dbf
example01.dbfredo02.logsysaux01.dbftemp01.dbfusers01.dbf
[oracle@localhosttest]$mvcontrol01.ctlcontrol01.ctl.aaa
[oracle@localhosttest]$ls
control01.ctl.aaaredo01.logredo03.logsystem01.dbfundotbs01.dbf
example01.dbfredo02.logsysaux01.dbftemp01.dbfusers01.dbf
[oracle@localhosttest]$cd..
[oracle@localhostoradata]$ls
test
[oracle@localhostoradata]$cd..
bash:cd..:commandnotfound
[oracle@localhostoradata]$cd..
[oracle@localhostoracle]$ls
admincfgtoollogscheckpointsdiagflash_recovery_areaoradataproduct
[oracle@localhostoracle]$cdflash_recovery_area/
[oracle@localhostflash_recovery_area]$ls
testtest
[oracle@localhostflash_recovery_area]$cdtest/
[oracle@localhosttest]$ls
control02.ctl
[oracle@localhosttest]$mvcontrol02.ctlcontrol02.ctl.aaa
2.10登录oracle生成spfile文件
[sql]viewplaincopyprint?
[oracle@localhosttest]$sqlplus/assysdba
SQL*Plus:Release11.2.0.1.0ProductiononSatOct117:23:342011
Copyright(c)1982,2009,Oracle.Allrightsreserved.
Connectedtoanidleinstance.
SQL>createspfilefrompfile='?/dbs/inittest.ora';
Filecreated.
2.11调用前面步骤修改好的test.sql，目的是生成链接控制文件等
[sql]viewplaincopyprint?
SQL>@/u01/app/oracle/diag/rdbms/test/test/trace/test.sql
ORACLEinstancestarted.
TotalSystemGlobalArea539848704bytes
FixedSize1337748bytes
VariableSize327157356bytes
DatabaseBuffers205520896bytes
RedoBuffers5832704bytes
Controlfilecreated.
Databasealtered.
Tablespacealtered.
2.12查看结果
[sql]viewplaincopyprint?
SQL>selectopen_modefromv$database;
OPEN_MODE
--------------------
READWRITE
SQL>showparametername
NAMETYPEVALUE
-----------------------------------------------------------------------------
db_file_name_convertstring
db_namestringtest
db_unique_namestringtest
global_namesbooleanFALSE
instance_namestringtest
lock_name_spacestring
log_file_name_convertstring
service_namesstringtest.localdomain
SQL>selectnamefromv$database;
NAME
---------
test
SQL>
上表明更改成功。
以上是“oracle 11g如何修改数据库名字和实例名字”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注亿速云行业资讯频道！
声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。
