关于修改sys口令和口令文件之间关系的实验

实验目的

验证修改sys用户的口令，口令文件是否随之动态修改

实验环境

说明：此次实验的所有操作都在同一台虚拟机上

数据库版本

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE    10.2.0.1.0      Production

TNS for Linux: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 – Production

操作系统版本

Linux linux 2.6.9-67.ELsmp #1 SMP Wed Nov 7 13:58:04 EST 2007 i686i686 i386 GNU/Linux

实验步骤

        

步骤提纲

1.	启动监听
2.	启动数据库到open，验证数据库已经open
3.	清除原有口令文件
4.	修改sys用户口令为oracle
5.	用sys用户远程连接数据库，验证口令文件作用
6.	创建新的口令文件，并用strings查看
7.	用sys用户远程连接数据库
8.	修改sys用户口令为beijing，退出，用strings查看口令文件变化
9.	用sys用户使用口令oracle、beijing分别尝试远程连接数据库

 

1.    启动监听

$ lsnrctl

 

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 17-SEP-201306:27:14

 

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

 

Welcome to LSNRCTL, type "help" for information.

 

LSNRCTL> status

Connecting to(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=linux)(PORT=1521)))

TNS-12541: TNS:no listener

 TNS-12560: TNS:protocoladapter 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:protocoladapter error

  TNS-00511: No listener

   Linux Error: 2: No suchfile or directory

LSNRCTL> start

Starting /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr: pleasewait...

 

TNSLSNR for Linux: Version 10.2.0.1.0 - Production

System parameter file is/u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora

Log messages written to /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log

Listening on:(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=linux)(PORT=1521)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))

 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=linux)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias                    LISTENER

Version                  TNSLSNR for Linux: Version 10.2.0.1.0 - Production

Start Date               17-SEP-2013 06:27:25

Uptime                    0days 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/listener.log

Listening Endpoints Summary...

 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=linux)(PORT=1521)))

 (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))

Services Summary...

Service "PLSExtProc" has 1 instance(s).

  Instance"PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

2.    启动数据库到open，验证数据库已经open

$ sqlplus / as sysdba

SQL> startup;

SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS

---------------- ------------

linux            OPEN

3.    清除原有口令文件

$ cd $ORACLE_HOME/dbs/

$ ls

hc_linux.dat  initdw.ora  initlinux.ora init.ora  lkTEST  orapwlinux spfilelinux.ora

$ rm orapwlinux

4.    修改sys用户口令为oracle

SQL> alter user sys identified by oracle;

 

5.    用sys用户远程连接数据库，验证口令文件作用

$ sqlplus sys/oracle@test as sysdba

 

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 17 07:04:592013

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

ERROR:

ORA-01031: insufficient privileges

Enter user-name:

远程连接数据库，没有口令文件，虽然口令正确，也无法连接数据库

6.    创建新的口令文件，并用strings查看

$ ls

hc_linux.dat  initdw.ora  initlinux.ora init.ora  lkTEST  spfilelinux.ora

$ pwd

/u01/app/oracle/product/10.2.0/db_1/dbs

$ orapwd file=orapwlinux password=oracle entries=5

$ ls

hc_linux.dat  initdw.ora  initlinux.ora init.ora  lkTEST  orapwlinux  spfilelinux.ora

$ strings orapwlinux

]\[Z

ORACLE Remote Password file

INTERNAL

AB27B53EDC5FEF41

8A8F025737A9097A

 

7.    用sys用户远程连接数据库

$ sqlplus sys/oracle@test as sysdba

 

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 17 06:42:582013

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

 

SQL>

连接成功

8.    修改sys用户口令为beijing，退出，用strings查看口令文件变化

SQL> alter user sys identified by beijing;

SQL> exit

$ strings orapwlinux

]\[Z

ORACLE Remote Password file

INTERNAL

246D907368C2CE56

3036FFD7240E3916

对比上面和步骤5的输出结果，可以发现已经发生了变化

9.    用sys用户使用口令oracle、beijing分别尝试远程连接数据库

$ sqlplus sys/oracle@test as sysdba

 

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 17 06:56:482013

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

ERROR:

ORA-01017: invalid username/password; logon denied

 

 

Enter user-name:

使用原来的口令，已经无法连接上

$ sqlplus sys/beijing@test as sysdba

 

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 17 06:57:162013

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

SQL>

用修改后的口令连接，连接成功

 

实验结论

 

         数据库管理员远程连接数据库时，需要通过数据库服务器端的口令文件的验证，才能连接到目标数据库，口令文件中记录的是拥有sys权限的用户的口令。当sys用户口令发生变化后，口令文件也自动发生变化。如果是dataguard环境，主库修改了sys口令，备库只需将sys口令修改为和主库一致即可，无需重建口令文件。