成都创新互联网站制作重庆分公司

oracle11g更改sid和dbname

分两个阶段描述，第一阶段改sid，第二阶段改dbname
下面描述详细步骤
首先要更改sid
 
1、登录数据库查看先前的sid，总共三步

[oracle@localhost ~]$ sqlplus "/as sysdba"  
SQL*Plus: Release 11.2.0.1.0 Production on Fri Aug 9 05:53:08 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup  
ORACLE instance started.  
  
Total System Global Area  539848704 bytes  
Fixed Size                  1337748 bytes  
Variable Size             360711788 bytes  
Database Buffers          171966464 bytes  
Redo Buffers                5832704 bytes  
Database mounted.  
Database opened.  
SQL> select instance from v$thread ;  
  
INSTANCE  
--------------------------------------------------------------------------------  
orcl  
2、关闭数据库
[sql] view plaincopyprint?
SQL> shutdown immediate;  
Database closed.  
Database dismounted.  
ORACLE instance shut down.  
SQL> exit  
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production  
With the Partitioning, OLAP, Data Mining and Real Application Testing options  

3、编辑/etc/oratab文件，把所有orcl换成mzh
[oracle@localhost ~]$ vim /etc/oratab   

4、更改oracle用户的 .bash_profile文件

[oracle@localhost ~]$ vim .bash_profile  

5、使改好的.bash_profile文件生效
 :%s/oral/mzh/g

[oracle@localhost ~]$ . .bash_profile  
7、查看系统环境变量

[oracle@localhost ~]$ env |grep ORACLE  
ORACLE_SID=mzh
ORACLE_BASE=/u01/app
ORACLE_HOME=/u01/app/oracle

效果一生成。
8、进入$ORACLE_HOME/dbs查看目录，看那些有orcl

[oracle@localhost ~]$ cd $ORACLE_HOME/dbs  
[oracle@localhost dbs]$ ll  
total 64
-rw-rw---- 1 oracle oinstall 1544 Jun 29 00:35 hc_DBUA0.dat
-rw-rw---- 1 oracle oinstall 1544 Aug  9 05:56 hc_mzh.dat
-rw-r--r-- 1 oracle oinstall 2851 May 15  2009 init.ora
-rw-r----- 1 oracle oinstall   24 Aug  8 06:46 lkHXY
-rw-r----- 1 oracle oinstall   24 Jun 29 00:47 lkMZH
-rw-r----- 1 oracle oinstall 2048 Aug  9 06:09 orapwmzh
drwx------ 2 oracle oinstall 4096 Jun 28 22:50 peshm_DBUA0_0
drwx------ 2 oracle oinstall 4096 Aug  8 06:45 peshm_hxy_0
drwx------ 2 oracle oinstall 4096 Jun 28 23:02 peshm_mzh_0
-rw-r----- 1 oracle oinstall 3584 Aug  9 05:53 spfilemzh.ora
9、更改文件名orcr>> mzh，ORCL>>MZH，命令如下：
 

[oracle@localhost dbs]$ mv hc_orcl.dat hc_mzh.dat   
[oracle@localhost dbs]$ mv orapworcl orapwmzh  
[oracle@localhost dbs]$ mv lkORCL lkMZH  
[oracle@localhost dbs]$ mv peshm_orcl_0/ peshm_mzh_0/  
[oracle@localhost dbs]$ mv spfileorcl.ora spfilemzh.ora   
10、重行生成密码文件，并查看
注意最好把原来的删除掉
[oracle@localhost dbs]$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=sys entries=5 force=y  
[oracle@localhost dbs]$ ls -lrt orap*  
-rw-r----- 1 oracle oinstall 2048 Aug  9 06:09 orapwmzh

11、登录数据库，并查看实例名字，结果表明sid已由orcl变成mzh了

[oracle@localhost dbs]$ sqlplus "/as sysdba"  
  
Connected to an idle instance.  
  
SQL> startup  
ORACLE instance started.  
  
Total System Global Area  539848704 bytes  
Fixed Size                  1337748 bytes  
Variable Size             327157356 bytes  
Database Buffers          205520896 bytes  
Redo Buffers                5832704 bytes  
Database mounted.  
Database opened.  
SQL> select instance from v$thread   ;  
  
INSTANCE  
--------------------------------------------------------------------------------  
mzh  


接着开始第二部分，更改数据库名dbname
2.1备份控制文件

SQL>  alter database backup controlfile to trace resetlogs;  
  
Database altered.  
2.2关闭并退出数据库

SQL> shutdown immediate  
Database closed.  
Database dismounted.  
ORACLE instance shut down.  
SQL> exit  
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production  
With the Partitioning, OLAP, Data Mining and Real Application Testing options  
2.3 orcale 11.2g的控制文件的备份目录为
/u01/app/oracle/diag/rdbms/mzh/mzh/trace

[oracle@localhost trace]$ ls -lrt  
[oracle@mazh dbs]$ ls /u01/app/diag/rdbms/mzh/mzh/trace -lrth
total 116K
-rw-r----- 1 oracle oinstall  259 Aug  8 06:45 mzh_ora_4808.trm
-rw-r----- 1 oracle oinstall  15K Aug  8 06:45 mzh_ora_4808.trc
-rw-r----- 1 oracle oinstall   57 Aug  8 06:46 mzh_mman_4845.trm
-rw-r----- 1 oracle oinstall  796 Aug  8 06:46 mzh_mman_4845.trc
-rw-r----- 1 oracle oinstall   57 Aug  8 06:46 mzh_dbrm_4839.trm
-rw-r----- 1 oracle oinstall  839 Aug  8 06:46 mzh_dbrm_4839.trc
-rw-r----- 1 oracle oinstall   57 Aug  8 06:46 mzh_mmon_4857.trm
-rw-r----- 1 oracle oinstall  833 Aug  8 06:46 mzh_mmon_4857.trc
-rw-r----- 1 oracle oinstall   67 Aug  8 06:46 mzh_j004_4909.trm
-rw-r----- 1 oracle oinstall  912 Aug  8 06:46 mzh_j004_4909.trc
-rw-r----- 1 oracle oinstall  112 Aug  8 06:47 mzh_j000_4900.trm
-rw-r----- 1 oracle oinstall 2.8K Aug  8 06:47 mzh_j000_4900.trc
-rw-r----- 1 oracle oinstall   67 Aug  8 06:58 mzh_ckpt_4851.trm
-rw-r----- 1 oracle oinstall  905 Aug  8 06:58 mzh_ckpt_4851.trc
-rw-r----- 1 oracle oinstall   78 Aug  8 06:58 mzh_j000_5233.trm
-rw-r----- 1 oracle oinstall 1.8K Aug  8 06:58 mzh_j000_5233.trc
-rw-r----- 1 oracle oinstall   57 Aug  8 08:04 mzh_j000_7066.trm
-rw-r----- 1 oracle oinstall 1.1K Aug  8 08:04 mzh_j000_7066.trc
-rw-r----- 1 oracle oinstall  139 Aug  8 08:43 mzh_vktm_4831.trm
-rw-r----- 1 oracle oinstall 1.9K Aug  8 08:43 mzh_vktm_4831.trc
-rw-r----- 1 oracle oinstall  407 Aug  8 08:43 mzh_ora_4871.trm
-rw-r----- 1 oracle oinstall 6.3K Aug  8 08:43 mzh_ora_4871.trc
-rw-r----- 1 oracle oinstall 6.2K Aug  8 08:43 alert_mzh.log
[oracle@localhost trace]$ vim alert_mzh.log   

可以在 alter_mzh.log里找到contolfile的备份trc，sid_ora_nnnn.trc 最新的一个就是。  
alter_mzh.log里面有这样一行字样，告诉你哪个是控制备份文件

Backup controlfile written to trace file /u01/app/diag/rdbms/orcl/mzh/trace/mzh_ora_4871.trc 

2.5复制一份

[oracle@localhost trace]$ cp mzh_ora_4871.trc mzh.sql  

2.6编辑 mzh.sql,也就是mzh_ora_4871.trc的复制品。

1）查找STARTUP NOMOUNT语句，将这一行上面的所有行都删除
2）查找所有以--开始的行，把这些行删除
3）查找所有的orcl修改为mzh，所有的ORCL修改为mzh
4）找到CREATE CONTROLFILE REUSE DATABASE...语句，将其中的REUSE修改为SET
5）找到RECOVER DATABASE USING BACKUP CONTROLFILE语句，将其用双横线（--）注释掉
结果如下：
 
[oracle@mazh dbs]$ cat /u01/app/diag/rdbms/mzh/mzh/trace/mzh.sql 
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "MZH" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oradata/mzh/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oradata/mzh/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oradata/mzh/redo03.log'  SIZE 50M BLOCKSIZE 512
DATAFILE
  '/u01/app/oradata/mzh/system01.dbf',
  '/u01/app/oradata/mzh/sysaux01.dbf',
  '/u01/app/oradata/mzh/undotbs01.dbf',
  '/u01/app/oradata/mzh/users01.dbf',
  '/u01/app/oradata/mzh/example01.dbf'
CHARACTER SET AL32UTF8
;
--RECOVER DATABASE USING BACKUP CONTROLFILE
ALTER DATABASE OPEN RESETLOGS;
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oradata/mzh/temp01.dbf'
     SIZE 30408704  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

2.7生成配置文件

[oracle@localhost trace]$ sqlplus "/as sysdba"  
  

  
Connected to an idle instance.  
  
SQL> create pfile='?/dbs/initmzh.ora' from spfile;  
  
File created.  
  
SQL> exit  
Disconnected  
2.8目录更改，这里和oracle 10g不一样，要注意。

 
[sql] view plaincopyprint?
[oracle@localhost ~]$ cd /u01/app/oracle/  
[oracle@localhost oracle]$ ls  
admin  cfgtoollogs  checkpoints  diag  flash_recovery_area  oradata  product  
[oracle@localhost oracle]$ cd flash_recovery_area/  
[oracle@localhost flash_recovery_area]$ ls  
orcl  ORCL  
[oracle@localhost flash_recovery_area]$ mv orcl/ mzh/  
[oracle@localhost flash_recovery_area]$ mv ORCL/ mzh/  
[oracle@localhost flash_recovery_area]$ cd ..  
[oracle@localhost oracle]$ ls  
admin  cfgtoollogs  checkpoints  diag  flash_recovery_area  oradata  product  
[oracle@localhost oracle]$ cd oradata/  
[oracle@localhost oradata]$ ls  
orcl  
[oracle@localhost oradata]$ mv orcl/ mzh/  
[oracle@localhost oradata]$ ls  
mzh  
[oracle@localhost oradata]$ cd ..  
[oracle@localhost oracle]$ ls  
admin  cfgtoollogs  checkpoints  diag  flash_recovery_area  oradata  product  
[oracle@localhost oracle]$ cd diag/  
[oracle@localhost diag]$ ls  
rdbms  tnslsnr  
[oracle@localhost diag]$ cd rdbms/  
[oracle@localhost rdbms]$ ls  
orcl  
[oracle@localhost rdbms]$ mv orcl/ mzh/  
[oracle@localhost rdbms]$ ls  
mzh  
[oracle@localhost rdbms]$ cd mzh  
[oracle@localhost mzh]$ ls  
i_1.mif  mzh   
[oracle@localhost mzh]$ cd ..  
[oracle@localhost rdbms]$ cd ..  
[oracle@localhost diag]$ ls  
rdbms  tnslsnr  
[oracle@localhost diag]$ cd ..  
[oracle@localhost oracle]$ cd admin/  
[oracle@localhost admin]$ ls  
orcl  
[oracle@localhost admin]$ cd orcl/  
[oracle@localhost orcl]$ ls  
adump  dpdump  pfile  
[oracle@localhost orcl]$ cd ..  
[oracle@localhost admin]$ mv orcl/ mzh/  
[oracle@localhost admin]$ ls  
mzh  
[oracle@localhost admin]$ sqlplus / as sysdba  
  
Connected to an idle instance.  
  
SQL>                                                   

2.9删除的控制文件。

[oracle@localhost oradata]$ cd mzh/  
[oracle@localhost mzh]$ ls  
control01.ctl  redo01.log  redo03.log    system01.dbf  undotbs01.dbf  
example01.dbf  redo02.log  sysaux01.dbf  temp01.dbf    users01.dbf  
[oracle@localhost mzh]$ mv control01.ctl control01.ctl.aaa  
[oracle@localhost mzh]$ ls  
control01.ctl.aaa  redo01.log  redo03.log    system01.dbf  undotbs01.dbf  
example01.dbf      redo02.log  sysaux01.dbf  temp01.dbf    users01.dbf  
[oracle@localhost mzh]$ cd ..  
[oracle@localhost oradata]$ ls  
mzh  
[oracle@localhost oradata]$ cd ..  
[oracle@localhost oracle]$ ls  
admin  cfgtoollogs  checkpoints  diag  flash_recovery_area  oradata  product  
[oracle@localhost oracle]$ cd flash_recovery_area/  
[oracle@localhost flash_recovery_area]$ ls  
mzh  mzh  
[oracle@localhost flash_recovery_area]$ cd mzh/  
[oracle@localhost mzh]$ ls  
control02.ctl  
[oracle@localhost mzh]$ mv control02.ctl control02.ctl.aaa  
2.10登录oracle生成spfile文件
[sql] view plaincopyprint?
[oracle@localhost mzh]$  sqlplus / as sysdba  
  
Connected to an idle instance.  
  
SQL> create spfile from pfile='?/dbs/initmzh.ora';  
  
File created.  
2.11调用前面步骤修改好的mzh.sql，目的是生成链接控制文件等

SQL> @/u01/app/diag/rdbms/mzh/mzh/trace/mzh.sql  
ORACLE instance started.  
  
Total System Global Area  539848704 bytes  
Fixed Size                  1337748 bytes  
Variable Size             327157356 bytes  
Database Buffers          205520896 bytes  
Redo Buffers                5832704 bytes  
  
Control file created.  
  
  
Database altered.  
  
  
Tablespace altered.  

2.12查看结果
SQL> select open_mode from v$database;  
  
OPEN_MODE  
--------------------  
READ WRITE  
  
SQL> show parameter name  
  
NAME                                 TYPE        VALUE  
------------------------------------ ----------- ------------------------------  
db_file_name_convert                 string  
db_name                              string      mzh  
db_unique_name                       string      mzh  
global_names                         boolean     FALSE  
instance_name                        string      mzh  
lock_name_space                      string  
log_file_name_convert                string  
service_names                        string      mzh.localdomain  
SQL> select name from v$database;  
  
NAME  
---------  
mzh  
  
SQL>   


有个小插曲在SQL> @/u01/app/diag/rdbms/mzh/mzh/trace/mzh.sql 时报错如下：
[oracle@mazh trace]$  sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Fri Aug 9 09:03:56 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

ERROR:
ORA-09925: Unable to create audit trail file
Linux Error: 2: No such file or directory
Additional information: 9925
ORA-01075: you are currently logged on


Enter user-name: 
ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name: 
ERROR:
ORA-01017: invalid username/password; logon denied


SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
[oracle@mazh trace]$ ps -ef|grep smo
root       470     7  0 07:10 ?        00:00:00 [kpsmoused]
oracle    5236     1  0 09:01 ?        00:00:00 ora_smon_mzh
oracle    5263  5047  0 09:04 pts/2    00:00:00 grep smo
[oracle@mazh trace]$ kill -9 5236
[oracle@mazh trace]$ ps -ef|grep smo
root       470     7  0 07:10 ?        00:00:00 [kpsmoused]
oracle    5265  5047  0 09:04 pts/2    00:00:00 grep smo
[oracle@mazh trace]$  sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Fri Aug 9 09:04:36 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.
SQL> @mzh2.sql
ORA-09925: Unable to create audit trail file
Linux Error: 2: No such file or directory
Additional information: 9925
CREATE CONTROLFILE SET DATABASE "MZH" RESETLOGS  NOARCHIVELOG
*
ERROR at line 1:
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0


ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0


ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oradata/mzh/temp01.dbf'
*
ERROR at line 1:
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0
在网上找了各种方法都不行，后来发现是
/u01/app/oracle/dbs/initmzh.ora里的参数竟然捣的鬼
以下什么原因导致的还不是很清楚，请各位高手给与指点下。
[oracle@mazh trace]$ vi /u01/app/oracle/dbs/initmzh.ora 

orcl.__db_cache_size=83886080
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__oracle_base='/u01/app'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=79691776
orcl.__sga_target=239075328
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=138412032
orcl.__streams_pool_size=4194304
mzh.__db_cache_size=83886080
mzh.__java_pool_size=4194304
mzh.__large_pool_size=4194304
mzh.__oracle_base='/u01/app'#ORACLE_BASE set from environment
mzh.__pga_aggregate_target=79691776
mzh.__sga_target=239075328
mzh.__shared_io_pool_size=0
mzh.__shared_pool_size=109051904
mzh.__streams_pool_size=0
*.audit_file_dest='/u01/app/admin/mzh/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oradata/mzh/control01.ctl','/u01/app/flash_recovery_area/mzh/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='mzh'
*.db_recovery_file_dest='/u01/app/flash_recovery_area'
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest='/u01/app'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=mzhXDB)'
*.open_cursors=300
*.pga_aggregate_target=78643200
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=235929600
*.timed_statistics=TRUE
*.undo_tablespace='UNDOTBS1'
 

当前名称：oracle11g更改sid和dbname
URL标题：http://cxhlcq.com/article/ipcijh.html

其他资讯

全国免费咨询:

400-028-6601

业务咨询：028-86922220 / 13518219792

节假值班：18980820575 / 13518219792

联系地址：成都市太升南路288号锦天国际A幢1002号

Copyright © 成都创新互联科技有限公司重庆分公司 渝ICP备2021005571号
友情链接:： 成都网站建设 重庆网站建设 四川网站建设 重庆建设网站 移动服务器托管 成都服务器托管 云服务器 广告设计制作 重庆网页设计 重庆做网站 重庆网站制作 重庆网站建设 重庆网站公司 渝中网站制作 重庆网站设计

在线咨询

微信咨询

电话咨询

028-86922220（工作日）

18980820575（7×24）

提交需求

返回顶部