一、更改ORACLE SID名称

更改ORACLE SID名称

1、切换到ORACLE用户，登陆到ORACLE数据库：
[root@ora10g ~]# su - oracle
[oracle@ora10g ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Dec 6 23:52:04 2011

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

2、查看当前的实例名
SQL> col host_name format a20
SQL> select host_name,instance_name,status from v$instance;

HOST_NAME            INSTANCE_NAME    STATUS
-------------------- ---------------- ------------
ora10g.localdomain   wwl              OPEN

3、创建参数文件
SQL> create pfile from spfile;

File created.

4、关闭实例
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

5、停止监听
[oracle@ora10g ~]$ lsnrctl stop

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 06-DEC-2011 23:54:53

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora10g.localdomain)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused

6、检查是否有数据库相关的进程，这里查看到ORACLE的进程都没有启动
[oracle@ora10g ~]$ ps -ef|grep ora
root      1907  1885  0 23:49 ?        00:00:00 hald-addon-storage: polling /dev/hdc
avahi     2086     1  0 23:49 ?        00:00:00 avahi-daemon: running [ora10g.local]
root      2455  2259  0 23:51 pts/1    00:00:00 su - oracle
oracle    2456  2455  0 23:51 pts/1    00:00:00 -bash
oracle    2501  2456  0 23:55 pts/1    00:00:00 ps -ef
oracle    2502  2456  0 23:55 pts/1    00:00:00 grep ora
[oracle@ora10g ~]$ ps -ef|grep list
68        1893  1885  0 23:49 ?        00:00:00 hald-addon-acpi: listening on acpid socket /var/run/acpid.socket
68        1897  1885  0 23:49 ?        00:00:00 hald-addon-keyboard: listening on /dev/input/event0
root      2297  2292  0 23:49 tty7     00:00:00 /usr/bin/Xorg :0 -br -audit 0 -auth /var/gdm/:0.Xauth -nolisten tcp vt7
oracle    2504  2456  0 23:55 pts/1    00:00:00 grep list
[oracle@ora10g ~]$ ps -ef|grep tns
oracle    2506  2456  0 23:55 pts/1    00:00:00 grep tns

7、修改刚才创建的pfile参数文件，主要添加和修改instance_name和service_names的值为新值：
[oracle@ora10g ~]$ cd $ORACLE_HOME/dbs

[oracle@ora10g dbs]$ ls -rtl
总计 68
-rw-r----- 1 oracle oinstall  8385 1998-09-11 init.ora
-rw-r----- 1 oracle oinstall 12920 2001-05-03 initdw.ora
-rw-rw---- 1 oracle oinstall  1544 11-17 15:39 hc_wwl.dat
-rw-r----- 1 oracle oinstall  1536 11-17 15:41 orapwwwl
-rw-r----- 1 oracle oinstall  2048 11-19 02:47 orawwl02
-rw-rw---- 1 oracle oinstall    24 11-19 03:12 lkWWL
-rw-rw---- 1 oracle oinstall  1544 11-19 03:17 hc_wwl02.dat
-rw-r----- 1 oracle oinstall   667 11-19 03:18 initwwl02.ora
-rw-rw---- 1 oracle oinstall    24 11-19 03:19 lkWWL02
-rw-r----- 1 oracle oinstall  2560 11-19 04:22 spfilewwl02.ora
-rw-r----- 1 oracle oinstall  3584 12-06 23:51 spfilewwl.ora
-rw-r--r-- 1 oracle oinstall  1038 12-06 23:54 initwwl.ora

[oracle@ora10g dbs]$ vi initwwl.ora
*.__db_cache_size=218103808
*.__java_pool_size=4194304
*.__large_pool_size=4194304
*.__shared_pool_size=54525952
*.__streams_pool_size=0
*.audit_file_dest='/orasoft//admin/wwl/adump'
*.background_dump_dest='/orasoft//admin/wwl/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/oradata/wwl/control01.ctl','/oradata/wwl/control02.ctl','/oradata/wwl/control03.ctl'
*.core_dump_dest='/orasoft//admin/wwl/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_recovery_file_dest='/oradata/flash_back'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=wwlXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=/oradata/archive'
*.log_archive_format='%t_%s_%r.dbf'
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=285212672
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS2'
*.user_dump_dest='/orasoft//admin/wwl/udump'

--添加如下内容
*.db_name='wwl'
*.instance_name = wwl01
*.service_names = wwl01

~
~
8、检查刚才添加的参数文件，是否已添加进来。
[oracle@ora10g dbs]$ cat initwwl.ora
*.__db_cache_size=218103808
*.__java_pool_size=4194304
*.__large_pool_size=4194304
*.__shared_pool_size=54525952
*.__streams_pool_size=0
*.audit_file_dest='/orasoft//admin/wwl/adump'
*.background_dump_dest='/orasoft//admin/wwl/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/oradata/wwl/control01.ctl','/oradata/wwl/control02.ctl','/oradata/wwl/control03.ctl'
*.core_dump_dest='/orasoft//admin/wwl/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='wwl'
*.db_recovery_file_dest='/oradata/flash_back'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=wwlXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=/oradata/archive'
*.log_archive_format='%t_%s_%r.dbf'
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=285212672
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS2'
*.user_dump_dest='/orasoft//admin/wwl/udump'
*.instance_name = wwl01
*.service_names = wwl01
~
9、并且创建新的密码文件
[oracle@ora10g dbs]$ orapwd file=orapwwwl01.ora password=oracle;
[oracle@ora10g dbs]$ cd /orasoft/product/10.2.0/db_1/network/admin/
[oracle@ora10g admin]$ ls
listener11113011AM1905.bak  listener.ora  samples  shrept.lst  tnsnames11113011AM1905.bak  tnsnames.ora

10、检查监听文件，如果有SID_NAME这个参数，将其修改为新的SID即可，我这里没有，就不修改了
[oracle@ora10g admin]$ vi listener.ora
# listener.ora Network Configuration File: /orasoft//product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

"tnsnames.ora" 24L, 527C 已写入                                                                                                           

10、使用刚才修改后的spfile参数启动数据库：
[oracle@ora10g dbs]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Dec 7 00:04:50 2011

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

Connected to an idle instance.

SQL> startup pfile='/orasoft/product/10.2.0/db_1/dbs/initwwl.ora';
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              62916176 bytes
Database Buffers          218103808 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.

11、将刚才创建的pfile参数创建为spfile，以后启动默认使用该spfile参数启动数据库
SQL> create spfile from pfile;

File created.

 

12、检查修改后的SID的名称，我们看到确实是已经修改过来了，但是实例名和DATABASE名称是不会修改的
SQL> show parameter instance_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      wwl01
SQL> show parameter service_names

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      wwl01

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
wwl

SQL> select name from v$database;

NAME
---------
WWL

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/20674423/viewspace-713739/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/20674423/viewspace-713739/