更改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

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

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

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

一、更改ORACLE SID名稱

相關文章