Oracle 11g 修改数据库名字和实例名字

199次阅读

共计 9921 个字符，预计需要花费 25 分钟才能阅读完成。

Oracle 11g 修改数据库名字和实例名字，分两个阶段描述，第一阶段改 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 换成 test，大部分情况其实就一条。—- 这几步不需要管的。

[oracle@localhost ~]$ vim /etc/oratab

4、更改 oracle 用户的 .bash_profile 文件, 把 orcl 改成 test

[oracle@localhost ~]$ vim .bash_profile

5、使改好的.bash_profile 文件生效

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

[oracle@localhost ~]$ env | grep ORACLE
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

[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>> test，ORCL>>test，命令如下：

[oracle@localhost dbs]$ mv hc_orcl.dat hc_test.dat
[oracle@localhost dbs]$ mv orapworcl orapwtest
[oracle@localhost dbs]$ mv lkORCL lkTEST
[oracle@localhost dbs]$ mv peshm_orcl_0/ peshm_test_0/
[oracle@localhost dbs]$ mv spfileorcl.ora spfiletest.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 orapwtest

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

[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
——————————————————————————–
test

第二部分：不用退出登录，接着开始第二部分，更改数据库名 dbname
2.1 备份控制文件

SQL> alter database backup controlfile to trace resetlogs;

Database altered.
2.2 关闭并退出数据库

SQL>

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/ORCL/test/trace ### 告警日志里面 , 注意 rdbms 后面应该是 ORCL，因为数据库名字还没有改变。

[oracle@localhost trace]$ ls -lrt
总计 80
-rw-r—– 1 oracle oinstall 256 10-01 17:03 test_ora_5286.trm
-rw-r—– 1 oracle oinstall 14393 10-01 17:03 test_ora_5286.trc
-rw-r—– 1 oracle oinstall 68 10-01 17:03 test_mman_5334.trm
-rw-r—– 1 oracle oinstall 852 10-01 17:03 test_mman_5334.trc
-rw-r—– 1 oracle oinstall 61 10-01 17:03 test_cjq0_5437.trm
-rw-r—– 1 oracle oinstall 993 10-01 17:03 test_cjq0_5437.trc
-rw-r—– 1 oracle oinstall 82 10-01 17:04 test_dbrm_5328.trm
-rw-r—– 1 oracle oinstall 1172 10-01 17:04 test_dbrm_5328.trc
-rw-r—– 1 oracle oinstall 90 10-01 17:05 test_vktm_5320.trm
-rw-r—– 1 oracle oinstall 1233 10-01 17:05 test_vktm_5320.trc
-rw-r—– 1 oracle oinstall 72 10-01 17:05 test_vkrm_5439.trm
-rw-r—– 1 oracle oinstall 996 10-01 17:05 test_vkrm_5439.trc
-rw-r—– 1 oracle oinstall 174 10-01 17:05 test_ora_5419.trm
-rw-r—– 1 oracle oinstall 5894 10-01 17:05 test_ora_5419.trc
-rw-r—– 1 oracle oinstall 5969 10-01 17:05 alert_test.log
[oracle@localhost trace]$ vim alert_test.log

可以在 alter_test.log 里找到 contolfile 的备份 trc，<em>sid_</em>ora_nnnn.trc 最新的一个就是。
alter_test.log 里面有这样一行字样，告诉你哪个是控制备份文件

Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/orcl/test/trace/test_ora_5419.trc

2.5 复制一份

[oracle@localhost trace]$ cp test_ora_5419.trc test.sql

2.6 编辑 test.sql, 也就是 test_ora_5419.trc 的复制品。

1）查找 STARTUP NOMOUNT 语句，将这一行上面的所有行都删除
2）查找所有以 – 开始的行，把这些行删除
3）查找所有的 orcl 修改为 test，所有的 ORCL 修改为 test ### 这个步骤不做也可以，做的目的就是为了符合规矩，实际上控制文件数据文件的目录是可以随便起的。
4）找到 CREATE CONTROLFILE REUSE DATABASE… 语句，将其中的 REUSE 修改为 SET
5）找到 RECOVER DATABASE USING BACKUP CONTROLFILE 语句，将其用双横线（–）注释掉
结果如下：

STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE “test” RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ‘/u01/app/oracle/oradata/test/redo01.log’ SIZE 50M BLOCKSIZE 512,
GROUP 2 ‘/u01/app/oracle/oradata/test/redo02.log’ SIZE 50M BLOCKSIZE 512,
GROUP 3 ‘/u01/app/oracle/oradata/test/redo03.log’ SIZE 50M BLOCKSIZE 512
— STANDBY LOGFILE
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’
CHARACTER SET ZHS16GBK
;
–RECOVER DATABASE USING BACKUP CONTROLFILE
ALTER DATABASE OPEN RESETLOGS;
ALTER TABLESPACE TEMP ADD TEMPFILE ‘/u01/app/oracle/oradata/test/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 from spfile;

File created.

SQL> exit
Disconnected
2.8 目录更改，这里和 oracle 10g 不一样，要注意。

[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/ test/
[oracle@localhost flash_recovery_area]$ mv ORCL/ test/
[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/ test/
[oracle@localhost oradata]$ ls
test
[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/ test/
[oracle@localhost rdbms]$ ls
test
[oracle@localhost rdbms]$ cd test
[oracle@localhost test]$ ls
i_1.mif test orcl
[oracle@localhost test]$ 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/ test/
[oracle@localhost admin]$ ls
test
[oracle@localhost admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 1 17:18:02 2011

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

Connected to an idle instance.

SQL>

2.9 删除的控制文件。

[oracle@localhost oradata]$ cd test/
[oracle@localhost test]$ ls
control01.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
[oracle@localhost test]$ mv control01.ctl control01.ctl.aaa
[oracle@localhost test]$ ls
control01.ctl.aaa redo01.log redo03.log system01.dbf undotbs01.dbf
example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
[oracle@localhost test]$ cd ..
[oracle@localhost oradata]$ ls
test
[oracle@localhost oradata]$ cd..
bash: cd..: command not found
[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
test test
[oracle@localhost flash_recovery_area]$ cd test/
[oracle@localhost test]$ ls
control02.ctl
[oracle@localhost test]$ mv control02.ctl control02.ctl.aaa
2.10 登录 oracle 生成 spfile 文件

[oracle@localhost test]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 1 17:23:34 2011

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

Connected to an idle instance.

SQL> create spfile from pfile=’?/dbs/inittest.ora’;

File created.
2.11 调用前面步骤修改好的 test.sql，目的是生成链接控制文件等

SQL> @/u01/app/oracle/diag/rdbms/test/test/trace/test.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 test
db_unique_name string test
global_names boolean FALSE
instance_name string test
lock_name_space string
log_file_name_convert string
service_names string test.localdomain
SQL> select name from v$database;

NAME
———
test

SQL>

上表明更改成功。

小结：针对目录：$ORACLE_BASE/diag/rdbms/db_name/oracle_sid/trace
当你改掉实例名之后重启数据库后，就会在 $ORACLE_BASE/diag/rdbms/db_name 生成一个新的目录（名字就是你新起名字的 sid)，当然当你修改了数据库名字之后，$ORACLE_BASE/diag/rdbms/ 后面就生成一个和你新起的数据库名字一样的目录。
通过上面的过程我们可以看出来，修改已经创建好库的数据库名字和 sid 是相当麻烦的过程，所以尽量不去修改，创建的时候要考虑周到.