实验目的
验证修改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口令修改为和主库一致即可,无需重建口令文件。