Oracle 11g 更改sid和dbname oracle 11g 数据库改名
Oracle 11g 更改sid和dbname oracle 11g 数据库改名
环境:
Windows XP +VM 7.1.3 + Red Hat linux enterprise 5.5 + Oracle 11g2
安装好的oracle 11g2改默认sid orcl 和默认数据库名ORCL
分两个阶段描述,第一阶段改sid,第二阶段改dbname
下面描述详细步骤
第一阶段:改sid
1、登录数据库查看先前的sid,总共三步,大家看的懂得,不懂得跟帖提问。
- [oracle@localhost ~]$ sqlplus "/as sysdba"
- SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 1 16:51:35 2011
- 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> 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换成nihao,大部分情况其实就一条。
- [oracle@localhost ~]$ vim /etc/oratab
4、更改oracle用户的 .bash_profile文件
- [oracle@localhost ~]$ vim .bash_profile
5、使改好的.bash_profile文件生效
- [oracle@localhost ~]$ . .bash_profile
7、查看系统环境变量
- [oracle@localhost ~]$ env |grep ORACLE
- ORACLE_UNQNAME=nihao
- ORACLE_SID=nihao
- ORACLE_BASE=/u01/app/oracle
- ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
效果一生成。
8、进入$ORACLE_HOME/dbs查看目录,看那些有orcl
- [oracle@localhost ~]$ cd $ORACLE_HOME/dbs
- [oracle@localhost dbs]$ ll
- 总计 24
- -rw-rw---- 1 oracle oinstall 1544 10-01 16:55 hc_orcl.dat
- -rw-r--r-- 1 oracle oinstall 2851 2009-05-15 init.ora
- -rw-r----- 1 oracle oinstall 24 09-28 20:57 lkORCL
- -rw-r----- 1 oracle oinstall 1536 09-29 09:42 orapworcl
- drwx------ 2 oracle oinstall 4096 09-28 20:55 peshm_orcl_0
- -rw-r----- 1 oracle oinstall 2560 10-01 16:53 spfileorcl.ora
9、更改文件名orcr=》nihao,ORCL=》NIHAO,命令如下:
- [oracle@localhost dbs]$ mv hc_orcl.dat hc_nihao.dat
- [oracle@localhost dbs]$ mv orapworcl orapwnihao
- [oracle@localhost dbs]$ mv lkORCL lkNIHAO
- [oracle@localhost dbs]$ mv peshm_orcl_0/ peshm_nihao_0/
- [oracle@localhost dbs]$ mv spfileorcl.ora spfilenihao.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 10-01 17:02 orapwnihao
11、登录数据库,并查看实例名字,结果表明sid已由orcl变成nihao了
- [oracle@localhost dbs]$ sqlplus "/as sysdba"
- SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 1 17:03:25 2011
- 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 327157356 bytes
- Database Buffers 205520896 bytes
- Redo Buffers 5832704 bytes
- Database mounted.
- Database opened.
- SQL> select instance from v$thread
- 2 ;
- INSTANCE
- --------------------------------------------------------------------------------
- nihao
不用退出登录,接着开始第二部分,更改数据库名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/nihao/nihao/trace
- [oracle@localhost trace]$ ls -lrt
- 总计 80
- -rw-r----- 1 oracle oinstall 256 10-01 17:03 nihao_ora_5286.trm
- -rw-r----- 1 oracle oinstall 14393 10-01 17:03 nihao_ora_5286.trc
- -rw-r----- 1 oracle oinstall 68 10-01 17:03 nihao_mman_5334.trm
- -rw-r----- 1 oracle oinstall 852 10-01 17:03 nihao_mman_5334.trc
- -rw-r----- 1 oracle oinstall 61 10-01 17:03 nihao_cjq0_5437.trm
- -rw-r----- 1 oracle oinstall 993 10-01 17:03 nihao_cjq0_5437.trc
- -rw-r----- 1 oracle oinstall 82 10-01 17:04 nihao_dbrm_5328.trm
- -rw-r----- 1 oracle oinstall 1172 10-01 17:04 nihao_dbrm_5328.trc
- -rw-r----- 1 oracle oinstall 90 10-01 17:05 nihao_vktm_5320.trm
- -rw-r----- 1 oracle oinstall 1233 10-01 17:05 nihao_vktm_5320.trc
- -rw-r----- 1 oracle oinstall 72 10-01 17:05 nihao_vkrm_5439.trm
- -rw-r----- 1 oracle oinstall 996 10-01 17:05 nihao_vkrm_5439.trc
- -rw-r----- 1 oracle oinstall 174 10-01 17:05 nihao_ora_5419.trm
- -rw-r----- 1 oracle oinstall 5894 10-01 17:05 nihao_ora_5419.trc
- -rw-r----- 1 oracle oinstall 5969 10-01 17:05 alert_nihao.log
- [oracle@localhost trace]$ vim alert_nihao.log
- 可以在 alter_nihao.log里找到contolfile的备份trc,<em>sid_</em>ora_nnnn.trc 最新的一个就是。
alter_nihao.log里面有这样一行字样,告诉你哪个是控制备份文件
- Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/orcl/nihao/trace/nihao_ora_5419.trc
2.5复制一份
- [oracle@localhost trace]$ cp nihao_ora_5419.trc nihao.sql
2.6编辑 nihao.sql,也就是nihao_ora_5419.trc的复制品。
1)查找STARTUP NOMOUNT语句,将这一行上面的所有行都删除
2)查找所有以--开始的行,把这些行删除
3)查找所有的orcl修改为nihao,所有的ORCL修改为NIHAO
4)找到CREATE CONTROLFILE REUSE DATABASE...语句,将其中的REUSE修改为SET
5)找到RECOVER DATABASE USING BACKUP CONTROLFILE语句,将其用双横线(--)注释掉
结果如下:
- STARTUP NOMOUNT
- CREATE CONTROLFILE SET DATABASE "NIHAO" RESETLOGS NOARCHIVELOG
- MAXLOGFILES 16
- MAXLOGMEMBERS 3
- MAXDATAFILES 100
- MAXINSTANCES 8
- MAXLOGHISTORY 292
- LOGFILE
- GROUP 1 '/u01/app/oracle/oradata/nihao/redo01.log' SIZE 50M BLOCKSIZE 512,
- GROUP 2 '/u01/app/oracle/oradata/nihao/redo02.log' SIZE 50M BLOCKSIZE 512,
- GROUP 3 '/u01/app/oracle/oradata/nihao/redo03.log' SIZE 50M BLOCKSIZE 512
- -- STANDBY LOGFILE
- DATAFILE
- '/u01/app/oracle/oradata/nihao/system01.dbf',
- '/u01/app/oracle/oradata/nihao/sysaux01.dbf',
- '/u01/app/oracle/oradata/nihao/undotbs01.dbf',
- '/u01/app/oracle/oradata/nihao/users01.dbf',
- '/u01/app/oracle/oradata/nihao/example01.dbf'
- CHARACTER SET ZHS16GBK
- ;
- --RECOVER DATABASE USING BACKUP CONTROLFILE
- ALTER DATABASE OPEN RESETLOGS;
- ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/nihao/temp01.dbf'
- SIZE 30408704 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
2.7生成配置文件
- [oracle@localhost trace]$ sqlplus "/as sysdba"
- SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 1 17:12:48 2011
- Copyright (c) 1982, 2009, Oracle. All rights reserved.
- Connected to an idle instance.
- SQL> create pfile='?/dbs/initnihao.ora' from spfile;
- File created.
- SQL> exit
- Disconnected
|
评论暂时关闭