一、Oracle5大名字：
● 实例名（instance name，对应SID）
● 全局名（global name）
● 数据库名（db_name）
● 数据库别名（db_unique_name）
● 服务名（server names）
二、修改db_name
范例：修改数据库名字（db_name）：wolex → wolex_db
要修改数据库的名字，必须重建控制文件了，因为控制文件中记录着数据库的名字
数据库open下
SQL> show parameter user_dump
NAME TYPE VALUE
---------------- ------------ --------------------------------
user_dump_dest string /u01/app/oracle/admin/wolex/udump
[oracle@redhat4 udump]$ pwd
/u01/app/oracle/admin/wolex/udump
[oracle@redhat4 udump]$ rf -fr*
SQL> alter databasebackup controlfile to trace;
Database altered.
[oracle@redhat4 udump]$ pwd
/u01/app/oracle/admin/wolex/udump
[oracle@redhat4 udump]$ ls
wolex_ora_20960.trc
查看此文件，提取其中创建控制文件的那段语句，创建脚本createCF.sql将数据库名字修改即可：
| CREATE CONTROLFILE REUSE SET DATABASE "WOLEX_DB" RESETLOGS NOARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/u01/app/oracle/oradata/wolex/redo01.log' SIZE 50M, GROUP 2 '/u01/app/oracle/oradata/wolex/redo02.log' SIZE 50M, GROUP 3 '/u01/app/oracle/oradata/wolex/redo03.log' SIZE 50M -- STANDBY LOGFILE DATAFILE '/u01/app/oracle/oradata/wolex/system01.dbf', '/u01/app/oracle/oradata/wolex/undotbs01.dbf', '/u01/app/oracle/oradata/wolex/sysaux01.dbf', '/u01/app/oracle/oradata/wolex/users01.dbf', '/u01/app/oracle/oradata/wolex/example01.dbf', '/u01/app/oracle/oradata/wolex/tbs1_01.dbf' CHARACTER SET ZHS16GBK ; |
关闭数据库
SQL> shutdownimmediate
Database closed.
Database dismounted.
ORACLE instance shut down.
在初始化文件中也添加新修改的数据库名字（注意此时应该没有spfile文件，否则不生效，因为instance启动时会首先检索是否存在spfile文件）
[oracle@redhat4 dbs]$ viinitwolex.ora
db_name='wolex_db'
启动数据库到nomount
[oracle@redhat4 ~]$ echo$ORACLE_SID
wolex
[oracle@redhat4 ~]$ sqlplus /as sysdba
SQL*Plus: Release 10.2.0.1.0 -Production on Sun Jul 22 10:40:40 2012
Copyright (c) 1982, 2005,Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 524288000 bytes
Fixed Size 2022080 bytes
Variable Size 180356416 bytes
Database Buffers 339738624 bytes
Redo Buffers 2170880 bytes
创建控制文件，执行以上创建控制文件的脚本createCF.sql：
SQL> @createCF.sql
Control file created.
创建完控制文件数据库此时已经达到了mount状态
SQL> selectopen_mode,name from v$database;
OPEN_MODE NAME
---------- ---------
MOUNTED WOLEX_DB
打开数据库
SQL> alter databaseopen resetlogs;
Database altered.
查看实例名：
SQL> selectinstance_name from v$instance;
INSTANCE_NAME
----------------
wolex
三、修改sid
[oracle@redhat4 ~]$ exportORACLE_SID=wolex_sid
[oracle@redhat4 ~]$ echo$ORACLE_SID
wolex_sid
修改SID后，在打开数据库时需要手动指定参数文件，因为打开数据库时，数据库默认查找$ORACLE_HOME/dbs/下的spfile<$ORACLE_SID>.ora，如果没有则查找init<$ORACLE_SID>.ora，如果没有就找init.ora，如果都没有则报错。
[oracle@redhat4 ~]$ sqlplus /as sysdba
SQL*Plus: Release 10.2.0.1.0 -Production on Sun Jul 22 11:17:28 2012
Copyright (c) 1982, 2005,Oracle. All rights reserved.
Connected to an idle instance.
SQL>startup pfile=$ORACLE_HOME/dbs/initwolex.ora
ORACLE instance started.
Total System Global Area 524288000 bytes
Fixed Size 2022080 bytes
Variable Size 146801984 bytes
Database Buffers 373293056 bytes
Redo Buffers 2170880 bytes
Database mounted.
Database opened.
四、修改服务名、别名
逐一修改数据库中各种名字（service_name,db_unique_name）
SQL> show parametername
NAME TYPE VALUE
-------------------------------------------------------------------- ------------------------------
db_file_name_convert string
db_name string wolex_db
db_unique_name string wolex_db
global_names boolean FALSE
instance_name string wolex_sid
lock_name_space string
log_file_name_convert string
service_names string wolex_db
我们可以看到，数据库的db_unique_name和service_name默认都是取数据库名字（db_name），下面修改这两个名字，因为此库没有spfile文件，我们关闭数据库后直接修改参数文件：
SQL> shutdownimmediate
Database closed.
Database dismounted.
ORACLE instance shut down.
[oracle@redhat4 dbs]$ viinitwolex.ora
| db_unique_name='wolex_uni' service_names='wolex_ser' |
SQL> startuppfile=$ORACLE_HOME/dbs/initwolex.ora
ORACLE instance started.
Total System Global Area 524288000 bytes
Fixed Size 2022080 bytes
Variable Size 146801984 bytes
Database Buffers 373293056 bytes
Redo Buffers 2170880 bytes
Database mounted.
Database opened.
查看现在的各个名字：
SQL> show parametername
NAME TYPE VALUE
-------------------------------------------------------------------- ------------------------------
db_file_name_convert string
db_name string wolex_db
db_unique_name string wolex_uni
global_names boolean FALSE
instance_name string wolex_sid
lock_name_space string
log_file_name_convert string
service_names string wolex_ser
五、配置监听
为了理清Oracle中各个名字与启动监听中对应的关系，所以上面特意把所有名字都改成独立的。我们分别通过动态监听和静态监听来验证。
5.1、动态监听
我们现在查看监听，检测什么服务启动了监听：
配置动态监听：
[oracle@redhat4admin]$ pwd
/u01/app/oracle/product/10.2.0/db_1/network/admin
[oracle@redhat4admin]$ vi listener.ora
| SID_LIST_LISTENER = (SID_LIST = (SID_DESC= (SID_NAME = PLSExtProc) (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1) (PROGRAM = extproc) ？这里program和global_dbname各是什么意思？与上面boolean值的global_name有什么联系？应该没有！ ) )
LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.200)(PORT = 1521)) ) |
启动后查看状态：
[oracle@redhat4~]$ lsnrctl start
[oracle@redhat4~]$ lsnrctl status
LSNRCTL forLinux: Version 10.2.0.1.0 - Production on 22-JUL-2012 12:41:57
Copyright (c)1991, 2005, Oracle. All rights reserved.
Connecting to(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.200)(PORT=1521)))
STATUS of theLISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version10.2.0.1.0 - Production
StartDate 21-JUL-2012 21:07:23
Uptime 0 days 15 hr. 34 min. 34sec
TraceLevel off
Security ON: Local OS Authentication
SNMP OFF
ListenerParameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener LogFile /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
ListeningEndpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.200)(PORT=1521)))
ServicesSummary...
Service "PLSExtProc" has 1 instance(s). --系统自动启用的，此处不作深究，详细可阅读David相关博客文章。
Instance "PLSExtProc", statusUNKNOWN, has 1 handler(s) for this service...
Service "wolexXDB" has 1 instance(s). -- 同上
Instance "wolex_sid", status READY,has 1 handler(s) for this service...
Service "wolex_ser" has 1instance(s).
Instance "wolex_sid", status READY,has 1 handler(s) for this service...
Service "wolex_uni" has 1instance(s).
Instance "wolex_sid", status READY,has 1 handler(s) for this service...
Service "wolex_uni_XPT" has 1 instance(s). -- 同上
Instance "wolex_sid", status READY,has 1 handler(s) for this service...
The commandcompleted successfully
由上面也可以看出，一个实例（wolex_sid）可以提供多个服务名，提供的服务名根据db_unique_name和server_names来确定。如果db_unique_name和server_names一样，则只启动一个服务名。
此时测试远程连接：
先配置密码文件
[oracle@redhat4dbs]$ pwd
/u01/app/oracle/product/10.2.0/db_1/dbs
[oracle@redhat4dbs]$ orapwd file=orapwwolex_sid password=oracle
？密码文件的用途有哪些，文件命名与SID为准？我的密码只是oracle，那其他用户的密码呢？
答:其他用户的密码以密文的形式存储在数据库中的数据字典中。
[oracle@redhat4~]$ echo $ORACLE_SID
wolex_sid
[oracle@redhat4~]$ sqlplus sys/oracle@192.168.0.200:1521/wolex_ser as sysdba
SQL*Plus:Release 10.2.0.1.0 - Production on Sun Jul 22 13:21:39 2012
Copyright (c)1982, 2005, Oracle. All rights reserved.
Connected to:
OracleDatabase 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With thePartitioning, OLAP and Data Mining options
SQL>!
[oracle@redhat4~]$ sqlplus sys/oracle@192.168.0.200:1521/wolex_uni as sysdba
SQL*Plus:Release 10.2.0.1.0 - Production on Sun Jul 22 13:23:23 2012
Copyright (c)1982, 2005, Oracle. All rights reserved.
Connected to:
OracleDatabase 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With thePartitioning, OLAP and Data Mining options
此时只有用上面两个名字才能连接，验证了用除服务名（server_names）和别名（db_unique_name）外的wolex、wolex_sid、wolex_db都不能成功。
5.1.1、通过net service name连接数据库
范例：我们此时在tnsnames.ora文件中添加net service name（网络服务名，也成为数据库别名）连接数据库
[oracle@redhat4admin]$ vi tnsnames.ora
追加：
| wolex_dynamic = (description = (address_list = (address = (protocol = tcp)(host = 192.168.0.200)(port = 1521)) ) (connect_data = (service_name = wolex_ser) -- 因为这里对应的端口号（1521）在listener.ora中只有动态注册，所以此处的service_name必须是参数文件中的service_names。 ) ) |
总之，上面的service_name必须是已经注册到监听中的服务名。
[oracle@redhat4~]$ sqlplus sys/oracle@wolex_dynamic as sysdba
SQL*Plus:Release 10.2.0.1.0 - Production on Sun Jul 22 13:34:37 2012
Copyright (c)1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With thePartitioning, OLAP and Data Mining options
总结：以上为动态注册，通过实验可以发现对外提供服务连接的是service_names和db_unique_name，和实例名无关。
5.2、静态注册
停止动态注册的监听：
[oracle@redhat4~]$ lsnrctl stop
修改监听，添加静态注册：
[oracle@redhat4admin]$ vi listener.ora
| SID_LIST_LSNR2= (SID_LIST= (SID_DESC = (GLOBAL_DBNAME = wolex_casual) -- 此处的名字可以随便起 (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1) (SID_NAME = wolex_sid) -- 这里必须对应提供服务的SID ) )
LSNR2 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.200)(PORT = 1526)) ) |
[oracle@redhat4~]$ lsnrctl start lsnr2
[oracle@redhat4~]$ lsnrctl status lsnr2
LSNRCTL forLinux: Version 10.2.0.1.0 - Production on 22-JUL-2012 14:04:34
Copyright (c)1991, 2005, Oracle. All rights reserved.
Connecting to(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.200)(PORT=1526)))
STATUS of theLISTENER
------------------------
Alias lsnr2
Version TNSLSNR for Linux: Version10.2.0.1.0 - Production
Start Date 22-JUL-2012 14:04:17
Uptime 0 days 0 hr. 0 min. 16 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
ListenerParameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener LogFile /u01/app/oracle/product/10.2.0/db_1/network/log/lsnr2.log
ListeningEndpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.200)(PORT=1526)))
ServicesSummary...
Service "wolex_casual" has 1instance(s).
Instance "wolex_sid", status UNKNOWN, has 1 handler(s)for this service...
The commandcompleted successfully
为什么上面的状态是unknown？和no service有什么不同？
答：因为静态注册是监听一打开，就立刻根据已经设置好的实例名把设置好的“GLOBAL_DBNAME”注册到监听，
而不管实例是否打开！所以它的状态会一直为“UNKNOWN”（即使实例启动了数据库）。
对于no service，是监听确定没有把实例提供的服务名注册进来，如果以后服务名注册进来，则状态也会随之更改为“READY”。
连接数据库
[oracle@redhat4~]$ echo $ORACLE_SID
wolex_sid
[oracle@redhat4~]$ sqlplus sys/oracle@192.168.0.200:1526/wolex_casual as sysdba
SQL*Plus:Release 10.2.0.1.0 - Production on Sun Jul 22 14:07:45 2012
Copyright (c)1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning,OLAP and Data Mining options
我们继续测试，为静态注册的lsnr2监听添加net service name：
[oracle@redhat4admin]$ vi tnsnames.ora
| WOLEX_LSNR2 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.200)(PORT = 1526)) ) (CONNECT_DATA = (SERVICE_NAME = wolex_casual) -- 注意此处的名要和上面监听中的GLOBAL_DBNAME一样，原因在见上。总之，这里的服务名必须已经注册到监听。 ) |
[oracle@redhat4~]$ sqlplus sys/oracle@wolex_lsnr2 as sysdba
SQL*Plus:Release 10.2.0.1.0 - Production on Sun Jul 22 14:17:11 2012
Copyright (c)1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With thePartitioning, OLAP and Data Mining options
总结：可以发现，静态注册与参数文件中的各个名字（db_name、db_unique_name、service_names）都无关，只由DBA设置的GLOBAL_DBNAME对外提供服务。
两种注册方式的比较：
● 动态注册比较方便，只需要在SID_DESC中的SID_NAME名设为“PLSExtProc”和PROGRAM名设为“extproc”即可。以后不管是什么实例名，什么数据库名都不需要重新配置，只要启动监听，监听就会由名为“extproc”的PROGRAM去解析，如果检测到有实例启动，则会被自动的注册到监听当中，提供服务。缺点就是不稳定，由于需要检测到有实例启动才注册，所以速度较静态注册慢。
● 静态注册是一启动监听后就立刻把监听文件（listener.ora）中的全局名（GOLBAL_DBNAME）注册进来，状态为UNKNOWN。静态注册的时候只需要设置GOLBAL_NAME和SID_NAME，要注意的是SID_NAME必须和此时打开数据库的实例名一致，而GOLBAL_NAME则可以由DBA随意起。可见静态注册比动态注册稳定，快速。
5.3、非默认端口的动态注册
把默认端口号修改为1521，现在我们再添加一个监听：
[oracle@redhat4 admin]$ vi listener.ora
| SID_LIST_LSNR3 = (SID_LIST = (SID_DESC= (SID_NAME = PLSExtProc) (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1) (PROGRAM = extproc) ) )
lsnr3 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.200)(PORT = 1234)) |
我们再为lsnr3添加一个net service name（网络服务名）：
[oracle@redhat4admin]$ vi tnsnames.ora
| lsnr3_tns = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.200)(PORT = 1234)) ) (CONNECT_DATA = (SERVICE_NAME = wolex_ser) ) ) |
我们把默认的listener停用
[oracle@redhat4~]$ lsnrctl stop
[oracle@redhat4~]$ echo $ORACLE_SID
wolex_sid
[oracle@redhat4~]$ sqlplus / as sysdba
SQL*Plus:Release 10.2.0.1.0 - Production on Sun Jul 22 20:28:37 2012
Copyright (c)1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With thePartitioning, OLAP and Data Mining options
设置lacal_listener的值为刚才配置的TNS服务名。
SQL> show parameterlocal_listener
NAME TYPE VALUE
-------------------------------------------------------------------- ------------------------------
local_listener string
SQL> alter system setlocal_listener=lsnr3_tns;
System altered.
[oracle@redhat4admin]$ lsnrctl start lsnr3
LSNRCTL forLinux: Version 10.2.0.1.0 - Production on 22-JUL-2012 22:46:38
Copyright (c)1991, 2005, Oracle. All rights reserved.
Starting/u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...
TNSLSNR forLinux: Version 10.2.0.1.0 - Production
System parameterfile is /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messageswritten to /u01/app/oracle/product/10.2.0/db_1/network/log/lsnr3.log
Listening on:(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.200)(PORT=1234)))
Connecting to(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.200)(PORT=1234)))
STATUS of the LISTENER
------------------------
Alias lsnr3
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 22-JUL-2012 22:46:38
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/product/10.2.0/db_1/network/log/lsnr3.log
ListeningEndpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.200)(PORT=1234)))
ServicesSummary...
Service"PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", statusUNKNOWN, has 1 handler(s) for this service...
The commandcompleted successfully
因为是动态注册，所以一开监听还没检索到实例，片刻继续查看lsnr3监听状态：
[oracle@redhat4admin]$ lsnrctl status lsnr3
LSNRCTL forLinux: Version 10.2.0.1.0 - Production on 22-JUL-2012 22:49:34
Copyright (c)1991, 2005, Oracle. All rights reserved.
Connecting to(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.200)(PORT=1234)))
STATUS of theLISTENER
------------------------
Alias lsnr3
Version TNSLSNR for Linux: Version10.2.0.1.0 - Production
Start Date 22-JUL-2012 22:46:38
Uptime 0 days 0 hr. 2 min. 56 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
ListenerParameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener LogFile /u01/app/oracle/product/10.2.0/db_1/network/log/lsnr3.log
ListeningEndpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.200)(PORT=1234)))
ServicesSummary...
Service"PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", statusUNKNOWN, has 1 handler(s) for this service...
Service"wolexXDB" has 1 instance(s).
Instance "wolex_sid", status READY,has 1 handler(s) for this service...
Service "wolex_ser" has 1instance(s).
Instance "wolex_sid", status READY,has 1 handler(s) for this service...
Service "wolex_uni" has 1instance(s).
Instance "wolex_sid", status READY,has 1 handler(s) for this service...
Service"wolex_uni_XPT" has 1 instance(s).
Instance "wolex_sid", status READY,has 1 handler(s) for this service...
The commandcompleted successfully
可以看到，由实例“wolex_sid”提供的各种服务名都注册到了监听lsnr3当中。
测试模拟远程登录：
[oracle@redhat4~]$ sqlplus sys/oracle@lsnr3_tns as sysdba
SQL*Plus:Release 10.2.0.1.0 - Production on Sun Jul 22 22:57:32 2012
Copyright (c)1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With thePartitioning, OLAP and Data Mining options
5.4、还有一种不配置“SID_LIST_<listener_name>”
配置监听lsnr4：
[oracle@redhat4admin]$ lsnrctl stop lsnr3
[oracle@redhat4admin]$ vi listener.ora
| lsnr4 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.200)(PORT = 1520)) ) |
配置对应的netservice name：
[oracle@redhat4admin]$ vi tnsnames.ora
| lsnr4_tns = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.200)(PORT = 1520)) ) (CONNECT_DATA = (SERVICE_NAME = wolex_ser) ) ) |
启动监听：
[oracle@redhat4admin]$ lsnrctl start lsnr4
[oracle@redhat4admin]$ lsnrctl status lsnr4
LSNRCTL forLinux: Version 10.2.0.1.0 - Production on 22-JUL-2012 23:07:07
Copyright (c)1991, 2005, Oracle. All rights reserved.
Connecting to(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.200)(PORT=1520)))
STATUS of theLISTENER
------------------------
Alias lsnr4
Version TNSLSNR for Linux: Version 10.2.0.1.0 -Production
Start Date 22-JUL-2012 23:06:01
Uptime 0 days 0 hr. 1 min. 6 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
ListenerParameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener LogFile /u01/app/oracle/product/10.2.0/db_1/network/log/lsnr4.log
ListeningEndpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.200)(PORT=1520)))
The listener supports noservices
The commandcompleted successfully
问：那么以上提示是属于静态注册还是动态注册呢？
答：我认为现在还不能确定是什么样的注册，因为完全没有服务。但是继续实验下去，可以看到如果将local_listener设置成对应的net service name，则监听lsnr4可以把相关的服务名注册进来，并且注册了其他的系统相关的服务名。
由这些特点可以知道，此时为动态注册。
此时并没有任何实例被注册到监听中。
对刚才配的netservice name进行测试能否ping通
[oracle@redhat4admin]$ tnsping lsnr4_tns
TNS Ping Utilityfor Linux: Version 10.2.0.1.0 - Production on 22-JUL-2012 23:08:04
Copyright (c)1997, 2005, Oracle. All rights reserved.
Used parameterfiles:
Used TNSNAMESadapter to resolve the alias
Attempting tocontact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.0.200)(PORT = 1520))) (CONNECT_DATA = (SERVICE_NAME = wolex_ser)))
OK (0 msec)
net service name（网络服务名）为lsnr4_tns能ping通，将实例连接的数据库的local_listener更改端口：
[oracle@redhat4~]$ sqlplus / as sysdba
SQL*Plus:Release 10.2.0.1.0 - Production on Sun Jul 22 23:13:28 2012
Copyright (c)1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With thePartitioning, OLAP and Data Mining options
SQL> alter system setlocal_listener=lsnr4_tns;
System altered.
查看监听lsnr4的状态：
[oracle@redhat4admin]$ lsnrctl status lsnr4
LSNRCTL forLinux: Version 10.2.0.1.0 - Production on 22-JUL-2012 23:15:51
Copyright (c)1991, 2005, Oracle. All rights reserved.
Connecting to(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.200)(PORT=1520)))
STATUS of theLISTENER
------------------------
Alias lsnr4
Version TNSLSNR for Linux: Version10.2.0.1.0 - Production
Start Date 22-JUL-2012 23:06:01
Uptime 0 days 0 hr. 9 min. 49 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
ListenerParameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener LogFile /u01/app/oracle/product/10.2.0/db_1/network/log/lsnr4.log
ListeningEndpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.200)(PORT=1520)))
ServicesSummary...
Service"wolexXDB" has 1 instance(s).
Instance "wolex_sid", status READY,has 1 handler(s) for this service...
Service "wolex_ser" has 1instance(s).
Instance "wolex_sid", status READY,has 1 handler(s) for this service...
Service "wolex_uni" has 1instance(s).
Instance "wolex_sid", status READY,has 1 handler(s) for this service...
Service"wolex_uni_XPT" has 1 instance(s).
Instance "wolex_sid", status READY,has 1 handler(s) for this service...
The commandcompleted successfully
可以看到，由实例wolex_sid提供的各种服务名都注册到了监听lsnr4当中。
尝试模拟远程连接：
[oracle@redhat4~]$ echo $ORACLE_SID
wolex_sid
[oracle@redhat4~]$ sqlplus sys/oracle@lsnr4_tns as sysdba
SQL*Plus:Release 10.2.0.1.0 - Production on Sun Jul 22 23:17:41 2012
Copyright (c)1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With thePartitioning, OLAP and Data Mining options
可以看到，更改默认端口号后，动态监听lsnr4能检索到实例wolex_sid了。
法二：对于local_listener的修改，可以直接改为对应端口号和服务器（主机）ip：
为公平起见，先停用lsnr4：
[oracle@redhat4admin]$ lsnrctl stop lsnr4
修改local_listener值为lsnr4（或lsnr4_tns）中ADDRESS：
SQL>alter system set local_listener="(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.0.200)(PORT = 1520))";
System altered.
SQL>show parameter local_listener
NAME TYPE VALUE
--------------- ----------- --------------------------------------------------------------
local_listener string (ADDRESS = (PROTOCOL = TCP)(HOST= 192.168.0.200)(PORT = 1520))
[oracle@redhat4admin]$ lsnrctl status lsnr4
启动监听，此时并没有任何实例没注册，稍等片刻查看lsnr4监听状态：
[oracle@redhat4admin]$ lsnrctl status lsnr4
LSNRCTL forLinux: Version 10.2.0.1.0 - Production on 22-JUL-2012 23:26:42
Copyright (c)1991, 2005, Oracle. All rights reserved.
Connecting to(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.200)(PORT=1520)))
STATUS of theLISTENER
------------------------
Alias lsnr4
Version TNSLSNR for Linux: Version10.2.0.1.0 - Production
Start Date 22-JUL-2012 23:26:23
Uptime 0 days 0 hr. 0 min. 19 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
ListenerParameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener LogFile /u01/app/oracle/product/10.2.0/db_1/network/log/lsnr4.log
ListeningEndpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.200)(PORT=1520)))
The listener supports no services
The commandcompleted successfully
现在为止，实例都未能注册进来。此时可以通过手动去令PMON将实例注册到监听：
SQL>alter system register;
System altered.
再次看出监听状态：
[oracle@redhat4admin]$ lsnrctl status lsnr4
LSNRCTL forLinux: Version 10.2.0.1.0 - Production on 22-JUL-2012 23:34:38
Copyright (c)1991, 2005, Oracle. All rights reserved.
Connecting to(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.200)(PORT=1520)))
STATUS of theLISTENER
------------------------
Alias lsnr4
Version TNSLSNR for Linux: Version10.2.0.1.0 - Production
Start Date 22-JUL-2012 23:26:23
Uptime 0 days 0 hr. 8 min. 14 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
ListenerParameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener LogFile /u01/app/oracle/product/10.2.0/db_1/network/log/lsnr4.log
Listening EndpointsSummary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.200)(PORT=1520)))
ServicesSummary...
Service"wolexXDB" has 1 instance(s).
Instance "wolex_sid", status READY,has 1 handler(s) for this service...
Service"wolex_ser" has 1 instance(s).
Instance "wolex_sid", status READY,has 1 handler(s) for this service...
Service"wolex_uni" has 1 instance(s).
Instance "wolex_sid", status READY,has 1 handler(s) for this service...
Service"wolex_uni_XPT" has 1 instance(s).
Instance "wolex_sid", status READY,has 1 handler(s) for this service...
The commandcompleted successfully
可以发现，由实例wolex_sid提供的各种服务名已经注册到监听当中了。
？两种修改的区别，为什么后面一种需要alter system register？
六、模拟两台Linux服务器之间oracle数据库的连接
蓝色表示Linux_01，米色表示Linux_02
Linux_01的IP：192.168.0.200
Linux_01上oracle数据库的SID：
[oracle@redhat4~]$ echo $ORACLE_SID
powter
本机（Linux_01）的ORACLE的各个名字都统一为powter：
[oracle@redhat4admin]$ sqlplus / as sysdba
SQL*Plus:Release 10.2.0.1.0 - Production on Tue Jul 24 12:08:38 2012
Copyright (c)1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With thePartitioning, OLAP and Data Mining options
SQL> set line 130
SQL> show parametername
NAME TYPE VALUE
---------------------------------------------------------- ------------------------------
db_file_name_convert string
db_name string powter
db_unique_name string powter
global_names boolean TRUE
instance_name string powter
lock_name_space string
log_file_name_convert string
service_names string powter
修改监听：
[oracle@redhat4admin]$ pwd
/u01/app/oracle/product/10.2.0/db_1/network/admin
[oracle@redhat4admin]$ vi listener.ora
| SID_LIST_LISTENER = (SID_LIST = (SID_DESC= (SID_NAME = PLSExtProc) (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1) (PROGRAM = extproc) ) )
LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.200)(PORT = 1521)) ) |
此处为动态注册，修改TNS：
| POWTER_TNSNAME = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.200)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = powter) ) )
WOLEX_TNSNAME = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.211)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = wolex_ser) -- 这里的服务名对应着SID为wolex连接的数据库的全局名 ) ) |
对于tnsname“WOLEX_TNSNAME”是用于连接Linux_02上的服务名为“wolex_ser”的数据库。
注意：tnsname实际上是在客户端上的，即服务器上的tnsname.ora文件里面配置的内容对于客户端来说，是用不到的，客户端上必须配置连接服务器的tnsname.ora文件。
所以，在这里的模拟就是：Linux_02上虽然在tnsname.ora文件中有配置TNS服务名，但只能在本机（Linux_02）上使用，所以客户端Linux_01（相对Linux_02来说Linus_01是客户端，反之亦然）必须配置相应的TNS服务名才能通过TNS服务名直接连接数据库wolexdb。
还需注意的是，连接数据库wolexdb的net service name必须根据wolexdb的实际信息来配置，这里是在已经查看wolexdb相关信息后配置的net service name。
Linux_02的IP:192.168.0.211
Linux_01上oracle数据库的SID：
[oracle@localhost~]$ echo $ORACLE_SID
wolex
Linux_02上的ORACLE的各个名字有所差异：
[oracle@localhostadmin]$ sqlplus / as sysdba
SQL> show parametername
NAME TYPE VALUE
---------------------------------------------------------- ------------------------------
db_file_name_convert string
db_name string wolexdb
db_unique_name string wolexdb
global_names boolean TRUE
instance_name string wolex
lock_name_space string
log_file_name_convert string
service_names string wolexdb
修改监听：
[oracle@localhost admin]$ pwd
/opt/oracle/product/10.2.0/db_1/network/admin
[oracle@localhost admin]$ vilistener.ora
| SID_LIST_LISTENER = (SID_LIST = (SID_DESC= (SID_NAME = wolex) (ORACLE_HOME = /opt/oracle/product/10.2.0/db_1) (GLOBAL_DBNAME = wolex_ser) -- 全局名可由DBA随意更改，不需要对应什么参数 ) )
LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.211)(PORT = 1521)) ) |
此处可以看到，由SID为wolex连接的数据库的监听配置为静态注册，也正因为是静态注册，所以全局名可以由DBA随意更改。
修改tnsname.ora：
| wolex_tns = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.211)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = wolex_ser) -- 这里的服务名必须是SID为wolex连接的数据库的全局名 ) )
powter_tns = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.200)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = powter) --因为powter数据库的监听是动态注册的，所以服务名对应参数service_names ) ) |
相关配置都设好了，下面开始分别在两台Linux机器上连接对方的ORACLE数据库。
Linux_01:
直接通过指定IP、端口号、对外服务名连接Linux_02上的wolexdb数据库
[oracle@redhat4~]$ sqlplus sys/oracle@192.168.0.211:1521/wolex_ser as sysdba
SQL*Plus:Release 10.2.0.1.0 - Production on Tue Jul 24 13:04:02 2012
Copyright (c)1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database10g Enterprise Edition Release 10.2.0.1.0 - Production
With thePartitioning, OLAP and Data Mining options
通过本地配置netservice name连接：
SQL>！
[oracle@redhat4~]$ sqlplus sys/oracle@wolex_tnsname as sysdba
SQL*Plus:Release 10.2.0.1.0 - Production on Tue Jul 24 13:05:07 2012
Copyright (c)1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database10g Enterprise Edition Release 10.2.0.1.0 - Production
With thePartitioning, OLAP and Data Mining options
Linux_02:
[oracle@localhost~]$ sqlplus sys/oracle@192.168.0.200:1521/powter as sysdba
SQL*Plus:Release 10.2.0.1.0 - Production on Tue Jul 24 13:07:48 2012
Copyright (c)1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With thePartitioning, OLAP and Data Mining options
通过net servicename连接：
SQL>！
[oracle@localhost~]$ sqlplus sys/oracle@powter_tns as sysdba
SQL*Plus:Release 10.2.0.1.0 - Production on Tue Jul 24 13:09:07 2012
Copyright (c)1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database10g Enterprise Edition Release 10.2.0.1.0 - Production
With thePartitioning, OLAP and Data Mining options
● 未完成实验：两个数据库之间实现“导入导出”：
附：易混淆术语介绍
（整理源于网络：http://blog.csdn.net/pyunsong/article/details/7443576）
Db_name：对一个数据库（Oracle database）的唯一标识，该数据库为第一章讲到的Oracledatabase。这种表示对于单个数据库是足够的，但是随着由多个数据库构成的分布式数据库的普及，这种命令数据库的方法给数据库的管理造成一定的负担，因为各个数据库的名字可能一样，造成管理上的混乱。为了解决这种情况，引入了Db_domain参数，这样在数据库的标识是由Db_name和 Db_domain两个参数共同决定的，避免了因为数据库重名而造成管理上的混乱。这类似于互连网上的机器名的管理。我们将Db_name和 Db_domain两个参数用’.’连接起来，表示一个数据库，并将该数据库的名称称为Global_name，即它扩展了Db_name。 Db_name参数只能由字母、数字、’_’、’#’、’$’组成，而且最多8个字符。
Db_domain：定义一个数据库所在的域，该域的命名同互联网的’域’没有任何关系，只是数据库管理员为了更好的管理分布式数据库而根据实际情况决定的。当然为了管理方便，可以将其等于互联网的域。
Global_name：对一个数据库（Oracle database）的唯一标识，oracle建议用此种方法命令数据库。该值是在创建数据库是决定的，缺省值为Db_name.Db_domain。在以后对参数文件中Db_name与Db_domain参数的任何修改不影响Global_name的值，如果要修改Global_name，只能用ALTER DATABASE RENAMEGLOBAL_NAME TO <db_name.db_domain>命令进行修改，然后修改相应参数。
Service_name：该参数是oracle8i新引进的。在8i以前，我们用SID来表示标识数据库的一个实例，但是在Oracle的并行环境中，一个数据库对应多个实例，这样就需要多个网络服务名，设置繁琐。为了方便并行环境中的设置，引进了Service_name参数，该参数对应一个数据库，而不是一个实例，而且该参数有许多其它的好处。该参数的缺省值为Db_name.Db_domain，即等于Global_name。一个数据库可以对应多个Service_name，以便实现更灵活的配置。该参数与SID没有直接关系，即不必Servicename 必须与SID一样。
Net service name：网络服务名，又可以称为数据库别名（database alias）。是客户端程序访问数据库时所需要，屏蔽了客户端如何连接到服务器端的细节，实现了数据库的位置透明的特性。
扫一扫
1045
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
