ORA-01994 的解决以及口令文件的相关测试
今天下午，最美在群里寻求解决 ORA-01994 错误的方法，错误信息：
当无法给用户授予 sysdba 权限时，可能有两个原因，一是参数 REMOTE_LOGIN_PASSWORDFILE 没有设置成 exclusive，二是 sysdba 用户超过了口令文件参数的 entries。查了一下 Oralce Error Messages ，确定该错误是由 REMOTE_LOGIN_PASSWORDFILE 参数的设置引起的：
Cause: A grant failed because a user could not be added to the password file. This is because the value of the REMOTE_LOGIN_PASSWORDFILE initialization parameter is set to SHARED.
Action: To add a user to the password file, shut down the database, change the REMOTE_LOGIN_PASSWORDFILE initialization parameter to EXCLUSIVE, and restart the database.
1.REMOTE_LOGIN_PASSWORDFILE 参数设置的测试：
SQL> alter system set remote_login_passwordfile=shared scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 80813176 bytes
Fixed Size 453752 bytes
Variable Size 58720256 bytes
Database Buffers 20971520 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> grant sysdba to sky;
grant sysdba to sky
*
ERROR at line 1:
ORA-01994: GRANT failed: cannot add users to public password file
但据最美描述，当他查询 v$pwfile_users 时，是空的，可是当 REMOTE_LOGIN_PASSWORDFILE=SHARED 时，该视图不应该是空的：
SQL> show parameter REMOTE_LOGIN_PASSWORDFILE
NAME TYPE VALUE
------------------------------------ ------- --------
remote_login_passwordfile string SHARED
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE
显然，最美的情况一定是将 REMOTE_LOGIN_PASSWORDFILE 设为 NONE 了，当设为 NONE 时是禁用使用口令文件验证的。
SQL> alter system set remote_login_passwordfile=none scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 80813176 bytes
Fixed Size 453752 bytes
Variable Size 58720256 bytes
Database Buffers 20971520 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> show parameter REMOTE_LOGIN_PASSWORDFILE
NAME TYPE VALUE
------------------------------------ ------- --------------------------
remote_login_passwordfile string NONE
SQL> select * from v$pwfile_users;
no rows selected
SQL> grant sysdba to sky;
grant sysdba to sky
*
ERROR at line 1:
ORA-01994: GRANT failed: cannot add users to public password file
此时，我在本地再开一个命令行窗口用 sys 登陆试试看：
C:\Documents and Settings\Administrator>sqlplus /nolog
SQL*Plus: Release 9.2.0.3.0 - Production on Sat Mar 18 02:06:27 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> conn sys/forever@sky as sysdba
Connected.
SQL> show user
USER is "SYS"
其实此时并非用口令文件登陆的，而是用操作系统验证登陆的，在这种情况下，只要有 as sysdba，/ 两边输入任何字符都可以登陆，如果不输入 / 则会提示输入密码，但密码可以随便输入，不过 as sysdba 之前必须有字符，否则会出现 SP2-0306: Invalid option 错误，由于条件所限我没有做远程登陆测试，不过可以禁止本地的操作系统认证来试试看：
SQL> conn sys/foreve as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL>
无法登陆，这里其实不是非法的用户名/密码，而是无法通过口令文件认证。
总结一下先：
用 SYSDBA 登陆时，可以用口令文件验证和操作系统验证两种方式，如果要用操作系统验证，需要在 sqlnet.ora 中添加 SQLNET.AUTHENTICATION_SERVICES= (NTS)，此时，/ 左右可以任意输入字符，只要最后跟着 as sysdba 都可以作为 sysdba 登陆，当然也可以 / 左右没有字符，如果不输入 / ，随便输入任何字符 + as sysdba 会要求输入密码，但可以随便输入密码，在不加 / 时，as sydba 前必须有字符，否则会产生错误。如果用口令文件验证需要建立口令文件，并将参数 REMOTE_LOGIN_PASSWORDFILE 设为 EXCLUSIVE 或者 SHARED，如果设为 NONE，则无法通过口令文件认证，此外，如果想将 SYSDBA 权限授予其他用户必须保证 REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE，其他选项在授权时都会产生错误。另外，当在数据库本地登陆时，登陆选项中不加 @SID，就不需要使用监听器。
2.口令文件参数 entries 的测试：
删掉口令文件，重建，并设置 entries 为 1：
sky.ora password=forever entries=1
用 sys 登陆，并授权：
C:\Documents and Settings\Administrator>sqlplus /nolog
SQL*Plus: Release 9.2.0.3.0 - Production on Sat Mar 18 02:58:51 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> conn sys/forever as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 80813176
bytes
Fixed Size 453752
bytes
Variable Size 58720256
bytes
Database Buffers 20971520
bytes
Redo Buffers 667648
bytes
Database mounted.
Database opened.
SQL> grant sysdba to sky;
Grant succeeded.
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE
SKY TRUE FALSE
SQL> grant sysdba to system;
Grant succeeded.
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE
SKY TRUE FALSE
SYSTEM TRUE FALSE
SQL> conn sky/forever as sysdba
Connected.
SQL>
可以看到，即使超出了 entries 的值依然可以授予 sysdba 权限，为什么呢，我依稀记得在哪里看到过超出 entries 的确可以再授权的，直到口令文件的大小达到 1 个 BLOCK（忘了是数据库数据块的大小，还是操作系统数据块的大小）的大小，具体的还要再查查资料，等以后再补充。
更多的介绍可以看看 Eygle 关于口令文件的介绍：
http://www.eygle.com/faq/passwordfile.htm
2006.04.02 补充：
今天在 CNOUG 上看到一篇《orapwd 命令中 entries 参数的作用》，该文详细说明了 entries 参数的设置，也证明了 entries 的设置与文件系统的 block size 大小无关，看来我前面的说法有误，不过 linux 和 windows 下不知道是不是一样，具体实验等以后有空再做。根据该文所说，entries 最大的有效数值是 65535，如果超过 65536，指定的数值将对 65536求模。并且，entries 按照 0-4,5-8,9-12,13-16,17-20 分组，每组的文件大小相同，可容纳的 SYSDBA 的用户数也相同，这就是为什么用户数可以超过 entries 的值。口令文件的大小 = (n + 1) * 512，其中 n 是每组口令文件中 0x9 上的值，既 2，3，4……
全文引用于此：
orapwd命令中entries参数的作用
作者 blue_stone
E-mail: blue_stone@xinhuanet.com
Blog: http://bluestone.cublog.cn
原始链接: http://www.cublog.cn/u/7667/?u=http://www.cublog.cn/u/7667/showart.php?id=93939
转载请保留作者信息和原始链接. 本文档的最新版本, 可以在原始链接处找到.
本文分析了orapwd中entries参数的一些性质，测试环境操作系统使用Debian GNU/Linux SID,数据库使用Oracle Database Enterprise Edition 9204。
Oracle数据库的orapwd用来建立密码文件，不加参数运行orapwd将给出orapwd命令的参数
blue_stone@blueice:~/test$ orapwd
Usage: orapwd file=
where
file - name of password file (mand),
password - password for SYS (mand),
entries - maximum number of distinct DBA and OPERs (opt),
There are no spaces around the equal-to (=) character.
orapwd命令可以有三个参数，file指生成的密码文件的名称，password指sys用户的密码，这两个参数是强制的，如果缺失任何参数都将输出orapwd的帮助信息。
blue_stone@blueice:~/test$ orapwd file=233.ora
Usage: orapwd file=
where
file - name of password file (mand),
password - password for SYS (mand),
entries - maximum number of distinct DBA and OPERs (opt),
There are no spaces around the equal-to (=) character.
blue_stone@blueice:~/test$ orapwd password=abcdef
Usage: orapwd file=
where
file - name of password file (mand),
password - password for SYS (mand),
entries - maximum number of distinct DBA and OPERs (opt),
There are no spaces around the equal-to (=) character.
如果成功的生成密码文件，orapwd将无提示的退出。
blue_stone@blueice:~/test$ orapwd file=233.ora password=abcdef
blue_stone@blueice:~/test$ ls -al 233.ora
-rwSr----- 1 blue_stone user 1536 2006-04-01 09:27 233.ora
第三个参数是entries。从orapwd的帮助信息上理解，entries是可以保存的记录个数，每个具有sysdba或sysoper权限的用户算一个记录，如果一个用户同时具有sysdba和sysoper的权限，则只占一个记录。数据库启动后可以通过v$pwfile_users来查看密码文件中记录的情况。
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE
但是entries并不是一个准确的值，也就是说，如果我们设置entries为10,可能会有超过10个用户可以具有sysdba或者sysoper的权限。为了证实这个观点，我们使用如下脚本来创建多个密码文件，并对生成的密码文件进行初步的分析。
#!/bin/bash
# File Name : pwtest.sh
# Usage : Create some oracle password file and analyze them.
n=0
while [ $n -le 20 ]
do
orapwd file=$n.ora password=oracle entries=$n
hexdump $n.ora > $n.dmp
n=$(($n+1))
done
n=0
while [ $n -le 20 ]
do
ls -al $n.ora
n=$((n+1))
done
n=0
while [ $n -le 20 ]
do
md5sum $n.ora
n=$((n+1))
done
这个脚本会生成20个密码文件1.ora到20.ora ， entries参数分别是1到20,将生成的密码文件使用hexdump导出成可读的txt文档。运行结果如下：
blue_stone@blueice:~/test$ ./pwtest.sh
-rwSr----- 1 blue_stone user 1536 2006-04-01 10:07 0.ora
-rwSr----- 1 blue_stone user 1536 2006-04-01 10:07 1.ora
-rwSr----- 1 blue_stone user 1536 2006-04-01 10:07 2.ora
-rwSr----- 1 blue_stone user 1536 2006-04-01 10:07 3.ora
-rwSr----- 1 blue_stone user 1536 2006-04-01 10:07 4.ora
-rwSr----- 1 blue_stone user 2048 2006-04-01 10:07 5.ora
-rwSr----- 1 blue_stone user 2048 2006-04-01 10:07 6.ora
-rwSr----- 1 blue_stone user 2048 2006-04-01 10:07 7.ora
-rwSr----- 1 blue_stone user 2048 2006-04-01 10:07 8.ora
-rwSr----- 1 blue_stone user 2560 2006-04-01 10:07 9.ora
-rwSr----- 1 blue_stone user 2560 2006-04-01 10:07 10.ora
-rwSr----- 1 blue_stone user 2560 2006-04-01 10:07 11.ora
-rwSr----- 1 blue_stone user 2560 2006-04-01 10:07 12.ora
-rwSr----- 1 blue_stone user 3072 2006-04-01 10:07 13.ora
-rwSr----- 1 blue_stone user 3072 2006-04-01 10:07 14.ora
-rwSr----- 1 blue_stone user 3072 2006-04-01 10:07 15.ora
-rwSr----- 1 blue_stone user 3072 2006-04-01 10:07 16.ora
-rwSr----- 1 blue_stone user 3584 2006-04-01 10:07 17.ora
-rwSr----- 1 blue_stone user 3584 2006-04-01 10:07 18.ora
-rwSr----- 1 blue_stone user 3584 2006-04-01 10:07 19.ora
-rwSr----- 1 blue_stone user 3584 2006-04-01 10:07 20.ora
c16f9293603f290409d2f495217e6262 0.ora
c16f9293603f290409d2f495217e6262 1.ora
c16f9293603f290409d2f495217e6262 2.ora
c16f9293603f290409d2f495217e6262 3.ora
c16f9293603f290409d2f495217e6262 4.ora
1bc8a7b9789922356fc9b804e02ea2c8 5.ora
1bc8a7b9789922356fc9b804e02ea2c8 6.ora
1bc8a7b9789922356fc9b804e02ea2c8 7.ora
1bc8a7b9789922356fc9b804e02ea2c8 8.ora
3c76d4e09b4cf3e7cf8ad69db2519ff6 9.ora
3c76d4e09b4cf3e7cf8ad69db2519ff6 10.ora
3c76d4e09b4cf3e7cf8ad69db2519ff6 11.ora
3c76d4e09b4cf3e7cf8ad69db2519ff6 12.ora
6604f4088b58fc57cad8d496814ee928 13.ora
6604f4088b58fc57cad8d496814ee928 14.ora
6604f4088b58fc57cad8d496814ee928 15.ora
6604f4088b58fc57cad8d496814ee928 16.ora
9c16648155e3387d74c8c63845c33595 17.ora
9c16648155e3387d74c8c63845c33595 18.ora
9c16648155e3387d74c8c63845c33595 19.ora
9c16648155e3387d74c8c63845c33595 20.ora
从输出的结果我们初步可以判定，生成的密码文件可以分为几个不同的组，entries参数分别为0-4,5-8,9-12,13-16,17-20，每组内各个文件内容想同。hexdump的结果进一步证实了这一点。
blue_stone@blueice:~/test$ diff 0.dmp 1.dmp
blue_stone@blueice:~/test$ diff 0.dmp 2.dmp
blue_stone@blueice:~/test$ diff 0.dmp 3.dmp
blue_stone@blueice:~/test$ diff 0.dmp 4.dmp
blue_stone@blueice:~/test$ diff 7.dmp 5.dmp
blue_stone@blueice:~/test$ diff 11.dmp 9.dmp
blue_stone@blueice:~/test$ diff 16.dmp 13.dmp
blue_stone@blueice:~/test$ diff 19.dmp 17.dmp
不同组的密码文件则并不相同（大小不同，md5sum的结果也不相同），对各个密码文件使用hexdump生成的dmp文件进行比较后可以看出不同的地方非常少
1.ora的dmp文件1.dmp内容如下：
0000000 0000 0000 0200 0000 0002 0000 5c5d 5a5b
0000010 0000 0000 0000 0000 0000 0000 0000 0000
*
0000200 524f 4341 454c 5220 6d65 746f 2065 6150
0000210 7373 6f77 6472 6620 6c69 0065 0000 001b
0000220 0000 0000 0000 0000 0000 0000 0000 0000
*
0000260 4e49 4554 4e52 4c41 0000 0000 0000 0000
0000270 0000 0000 0000 0000 0000 0000 0000 0000
0000280 0008 0000 4241 3732 3542 4533 4344 4635
0000290 4645 3134 0000 0000 0000 0000 0000 0000
00002a0 0000 0000 0010 0000 000f 0000 0000 0000
00002b0 0000 0000 0000 0000 0000 0000 0000 0000
00002c0 0000 0000 0000 0000 5953 0053 0000 0000
00002d0 0000 0000 0000 0000 0000 0000 0000 0000
00002e0 0000 0000 0000 0000 0003 0000 4138 4638
00002f0 3230 3735 3733 3941 3930 4137 0000 0000
0000300 0000 0000 0000 0000 0000 0000 0010 0000
0000310 000f 0000 0000 0000 0000 0000 0000 0000
0000320 0000 0000 0000 0000 0000 0000 0000 0000
*
0000600
5.ora的dmp文件5.dmp内容如下：
blue_stone@blueice:~/test$ more 5.dmp
0000000 0000 0000 0200 0000 0003 0000 5c5d 5a5b
0000010 0000 0000 0000 0000 0000 0000 0000 0000
*
0000200 524f 4341 454c 5220 6d65 746f 2065 6150
0000210 7373 6f77 6472 6620 6c69 0065 0000 001b
0000220 0000 0000 0000 0000 0000 0000 0000 0000
*
0000260 4e49 4554 4e52 4c41 0000 0000 0000 0000
0000270 0000 0000 0000 0000 0000 0000 0000 0000
0000280 0008 0000 4241 3732 3542 4533 4344 4635
0000290 4645 3134 0000 0000 0000 0000 0000 0000
00002a0 0000 0000 0010 0000 000f 0000 0000 0000
00002b0 0000 0000 0000 0000 0000 0000 0000 0000
00002c0 0000 0000 0000 0000 5953 0053 0000 0000
00002d0 0000 0000 0000 0000 0000 0000 0000 0000
00002e0 0000 0000 0000 0000 0003 0000 4138 4638
00002f0 3230 3735 3733 3941 3930 4137 0000 0000
0000300 0000 0000 0000 0000 0000 0000 0010 0000
0000310 000f 0000 0000 0000 0000 0000 0000 0000
0000320 0000 0000 0000 0000 0000 0000 0000 0000
*
0000800
每个dmp文件的第一列标明了这一列的数据在文件中的偏移量，后面是用16进制表示的文件的内容，每行16字节，32个16进制字符，如果多行内容相同，则只显示第一行的内容，下面重复的行将以星号（*）替代。运行hexdump时使用-v参数可以强制hexdump输出重复的行。最后一行是文件末尾的偏移量，可以看出，1.dmp文件的大小为0x600（1536）字节，5.dmp文件的大小为0x800（2048）字节。
使用diff对1.dmp和5.dmp进行比较，结果如下：
blue_stone@blueice:~/test$ diff 1.dmp 5.dmp
1c1
< 0000000 0000 0000 0200 0000 0002 0000 5c5d 5a5b
---
> 0000000 0000 0000 0200 0000 0003 0000 5c5d 5a5b
22c22
< 0000600
---
> 0000800
也就是说只有第一行的一个数值（偏移量为0xa)不同，而这个数字和文件的大小成正比，文件大小=(0x9的数值 +1) * 512。如果在使用orapwd建立密码文件时指定的参数足够大，比如65535，则0x8位上也将为非0值，如果entries为65535则0x8,0x9的值分别为0x40,0x01。 初始的密码文件非0的内容只有文件开始不到1k字节，剩下的部分都是0。
entries最大的有效数值是65535,如果超过65536,指定的数值将对65536求模。用下面的程序可以验证这一点：
#!/bin/bash
# File Name : pwtest2.sh
# Usage : Create some oracle password file with the entires parameter
# : like 0*65536 + m, 1*65536 + m ......, then analyze them.
m=5
n=0
while [ $n -le 20 ]
do
orapwd file=$(($n*65536+$m)).ora password=oracle entries=$(($n*65536+$m))
n=$(($n+1))
done
n=0
while [ $n -le 20 ]
do
ls -al $(($n*65536+$m)).ora
n=$((n+1))
done
n=0
while [ $n -le 20 ]
do
md5sum $(($n*65536+$m)).ora
n=$((n+1))
done
运行结果如下：
blue_stone@blueice:~/test$ ./pwtest2.sh
-rwSr----- 1 blue_stone user 2048 2006-04-01 12:23 5.ora
-rwSr----- 1 blue_stone user 2048 2006-04-01 12:23 65541.ora
-rwSr----- 1 blue_stone user 2048 2006-04-01 12:23 131077.ora
-rwSr----- 1 blue_stone user 2048 2006-04-01 12:23 196613.ora
-rwSr----- 1 blue_stone user 2048 2006-04-01 12:23 262149.ora
-rwSr----- 1 blue_stone user 2048 2006-04-01 12:23 327685.ora
-rwSr----- 1 blue_stone user 2048 2006-04-01 12:23 393221.ora
-rwSr----- 1 blue_stone user 2048 2006-04-01 12:23 458757.ora
-rwSr----- 1 blue_stone user 2048 2006-04-01 12:23 524293.ora
-rwSr----- 1 blue_stone user 2048 2006-04-01 12:23 589829.ora
-rwSr----- 1 blue_stone user 2048 2006-04-01 12:23 655365.ora
-rwSr----- 1 blue_stone user 2048 2006-04-01 12:23 720901.ora
-rwSr----- 1 blue_stone user 2048 2006-04-01 12:23 786437.ora
-rwSr----- 1 blue_stone user 2048 2006-04-01 12:23 851973.ora
-rwSr----- 1 blue_stone user 2048 2006-04-01 12:23 917509.ora
-rwSr----- 1 blue_stone user 2048 2006-04-01 12:23 983045.ora
-rwSr----- 1 blue_stone user 2048 2006-04-01 12:23 1048581.ora
-rwSr----- 1 blue_stone user 2048 2006-04-01 12:23 1114117.ora
-rwSr----- 1 blue_stone user 2048 2006-04-01 12:23 1179653.ora
-rwSr----- 1 blue_stone user 2048 2006-04-01 12:23 1245189.ora
-rwSr----- 1 blue_stone user 2048 2006-04-01 12:23 1310725.ora
1bc8a7b9789922356fc9b804e02ea2c8 5.ora
1bc8a7b9789922356fc9b804e02ea2c8 65541.ora
1bc8a7b9789922356fc9b804e02ea2c8 131077.ora
1bc8a7b9789922356fc9b804e02ea2c8 196613.ora
1bc8a7b9789922356fc9b804e02ea2c8 262149.ora
1bc8a7b9789922356fc9b804e02ea2c8 327685.ora
1bc8a7b9789922356fc9b804e02ea2c8 393221.ora
1bc8a7b9789922356fc9b804e02ea2c8 458757.ora
1bc8a7b9789922356fc9b804e02ea2c8 524293.ora
1bc8a7b9789922356fc9b804e02ea2c8 589829.ora
1bc8a7b9789922356fc9b804e02ea2c8 655365.ora
1bc8a7b9789922356fc9b804e02ea2c8 720901.ora
1bc8a7b9789922356fc9b804e02ea2c8 786437.ora
1bc8a7b9789922356fc9b804e02ea2c8 851973.ora
1bc8a7b9789922356fc9b804e02ea2c8 917509.ora
1bc8a7b9789922356fc9b804e02ea2c8 983045.ora
1bc8a7b9789922356fc9b804e02ea2c8 1048581.ora
1bc8a7b9789922356fc9b804e02ea2c8 1114117.ora
1bc8a7b9789922356fc9b804e02ea2c8 1179653.ora
1bc8a7b9789922356fc9b804e02ea2c8 1245189.ora
1bc8a7b9789922356fc9b804e02ea2c8 1310725.ora
如果不指定entries参数，则生成的密码文件和entries参数为0-4时生成的参数文件一样。
blue_stone@blueice:~/test$ orapwd file=1.ora password=oracle entries=1
blue_stone@blueice:~/test$ orapwd file=default.ora password=oracle
blue_stone@blueice:~/test$ md5sum 1.ora default.ora
c16f9293603f290409d2f495217e6262 1.ora
c16f9293603f290409d2f495217e6262 default.ora
blue_stone@blueice:~/test$ ls -al 1.ora default.ora
-rwSr----- 1 blue_stone user 1536 2006-04-01 13:09 1.ora
-rwSr----- 1 blue_stone user 1536 2006-04-01 13:09 default.ora
在linux中可以使用tune2fs -l 可以查看文件系统的属性。
blueice:~# tune2fs -l /dev/hda6
tune2fs 1.39-WIP (31-Dec-2005)
Filesystem volume name:
Last mounted on:
Filesystem UUID: 0f4eb2b4-1360-48a1-8b66-3916a6838210
Filesystem magic number: 0xEF53
Filesystem revision #: 1 (dynamic)
Filesystem features: has_journal filetype needs_recovery sparse_super
Default mount options: (none)
Filesystem state: clean
Errors behavior: Continue
Filesystem OS type: Linux
Inode count: 3057824
Block count: 6104692
Reserved block count: 305234
Free blocks: 1002160
Free inodes: 2930491
First block: 0
Block size: 4096
Fragment size: 4096
Blocks per group: 32768
Fragments per group: 32768
Inodes per group: 16352
Inode blocks per group: 511
Filesystem created: Sat Sep 3 16:33:16 2005
Last mount time: Sat Apr 1 08:10:43 2006
Last write time: Sat Apr 1 08:10:43 2006
Mount count: 8
Maximum mount count: 22
Last checked: Sat Mar 25 20:15:04 2006
Check interval: 15552000 (6 months)
Next check after: Thu Sep 21 20:15:04 2006
Reserved blocks uid: 0 (user root)
Reserved blocks gid: 0 (group root)
First inode: 11
Inode size: 128
Journal inode: 8
Default directory hash: tea
Directory Hash Seed: 70ac7131-5a92-4ca3-865d-4ce83f38acb1
Journal backup: inode blocks
从输出的结果可以看出该文件系统块大小为4096字节。这也是ext3文件系统的默认块大小。使用mkfs.ext3 -b n devicename 可以在devicename上创建块大小为n的的文件系统，n可取的值有1024,2048,4096。
blueice:/home/blue_stone/test# dd if=/dev/zero of=testfile bs=1M count=20
20+0 records in
20+0 records out
20971520 bytes (21 MB) copied, 0.449763 seconds, 46.6 MB/s
blueice:/home/blue_stone/test# mkfs.ext3 -b 1024 testfile
mke2fs 1.39-WIP (31-Dec-2005)
testfile is not a block special device.
Proceed anyway? (y,n) y
Filesystem label=
OS type: Linux
Block size=1024 (log=0)
Fragment size=1024 (log=0)
5136 inodes, 20480 blocks
1024 blocks (5.00%) reserved for the super user
First data block=1
3 block groups
8192 blocks per group, 8192 fragments per group
1712 inodes per group
Superblock backups stored on blocks:
8193
Writing inode tables: done
Creating journal (1024 blocks): done
Writing superblocks and filesystem accounting information: done
This filesystem will be automatically checked every 21 mounts or
180 days, whichever comes first. Use tune2fs -c or -i to override.
blueice:/home/blue_stone/test# mkdir test
blueice:/home/blue_stone/test# mount testfile test -o loop
blueice:/home/blue_stone/test# mount
/dev/hda1 on / type ext3 (rw,errors=remount-ro)
proc on /proc type proc (rw)
sysfs on /sys type sysfs (rw)
usbfs on /proc/bus/usb type usbfs (rw)
tmpfs on /dev/shm type tmpfs (rw)
devpts on /dev/pts type devpts (rw,gid=5,mode=620)
/dev/hda3 on /mnt/amd64 type ext3 (rw)
/dev/hda6 on /home type ext3 (rw)
/dev/hda9 on /bak type ext3 (rw)
/dev/hda7 on /vmware type ext3 (rw)
tmpfs on /dev type tmpfs (rw,size=10M,mode=0755)
nfsd on /proc/fs/nfsd type nfsd (rw)
binfmt_misc on /proc/sys/fs/binfmt_misc type binfmt_misc (rw)
/home/blue_stone/test/testfile on /home/blue_stone/test/test type ext3 (rw,loop=/dev/loop/0)
blueice:/home/blue_stone/test# tune2fs -l testfile
tune2fs 1.39-WIP (31-Dec-2005)
Filesystem volume name:
Last mounted on:
Filesystem UUID: 561bb9bf-2455-4677-aa0d-27c868aa99fd
Filesystem magic number: 0xEF53
Filesystem revision #: 1 (dynamic)
Filesystem features: has_journal filetype needs_recovery sparse_super
Default mount options: (none)
Filesystem state: clean
Errors behavior: Continue
Filesystem OS type: Linux
Inode count: 5136
Block count: 20480
Reserved block count: 1024
Free blocks: 18784
Free inodes: 5125
First block: 1
Block size: 1024
Fragment size: 1024
Blocks per group: 8192
Fragments per group: 8192
Inodes per group: 1712
Inode blocks per group: 214
Filesystem created: Sat Apr 1 12:33:59 2006
Last mount time: Sat Apr 1 12:34:15 2006
Last write time: Sat Apr 1 12:34:15 2006
Mount count: 1
Maximum mount count: 21
Last checked: Sat Apr 1 12:33:59 2006
Check interval: 15552000 (6 months)
Next check after: Thu Sep 28 12:33:59 2006
Reserved blocks uid: 0 (user root)
Reserved blocks gid: 0 (group root)
First inode: 11
Inode size: 128
Journal inode: 8
Default directory hash: tea
Directory Hash Seed: 02dfc011-04f6-4a92-b763-99fe8aa76e96
Journal backup: inode blocks
在新创建的文件系统中做上面的测试，可以得出相同的结果，由此可见，oracle密码文件和文件系统的大小并无关系。
当不指定entries值或者指定的entries的值为0-4的时候，可以有五个用户具有sysdba或者sysoper的权限。
oracle@blueice:~/product/9.2.0/dbs$ rm orapworcl
oracle@blueice:~/product/9.2.0/dbs$ orapwd file=orapworcl password=oracle
oracle@blueice:~/product/9.2.0/dbs$ hexdump -v orapworcl >default.dmp
oracle@blueice:~/product/9.2.0/dbs$ sqlplus /nolog
SQL*Plus: Release 9.2.0.4.0 - Production on Sat Apr 1 13:14:22 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> conn sys/oracle as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 147920392 bytes
Fixed Size 452104 bytes
Variable Size 121634816 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> select * from v$pwfile_users
2 ;
USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE
SQL> grant sysdba to scott,hr,sh,xdb;
Grant succeeded.
SQL> grant sysdba to wmsys;
grant sysdba to wmsys
*
ERROR at line 1:
ORA-01996: GRANT failed: password file '/home/oracle/product/9.2.0/dbs/orapworcl' is full
SQL> select * from v$pwfile_users
2 ;
USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE
SCOTT TRUE FALSE
HR TRUE FALSE
SH TRUE FALSE
XDB TRUE FALSE
使用string可以看到oracle密码文件中的一些内容
oracle@blueice:~/product/9.2.0/dbs$ strings orapworcl
]\[Z
ORACLE Remote Password file
ORCL
INTERNAL
2C0791C2F3E3BB92
D4C5016086B2DC6A
r1BSCOTT
F894844C34402B67
6399F3B38EDF3288
9793B3777CD3BD1A
88D8364765FCE6AF
oracle@blueice:~/product/9.2.0/dbs$ sqlplus /nolog
SQL*Plus: Release 9.2.0.4.0 - Production on Sat Apr 1 13:51:20 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> select password from dba_users where username in ('HR','SH','SCOTT','XDB');
PASSWORD
------------------------------
88D8364765FCE6AF
F894844C34402B67
9793B3777CD3BD1A
6399F3B38EDF3288
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
给用户授与sysdba权限的过程就是把密码从数据字典复制到了密码文件中的过程。
Posted by Sky at 11:15 PM | Permalink | comments(1) | Edit | Database
Comments
俺懂，山是不会动的，俺得去找山！
针对您分析的这个问题，我想我遇到的实际情况和"最美"应该是相同的！
跟随您的文章，我把参数REMOTE_LOGIN_PASSWORDFILE值改为EXCLUSIVE或SAHRED均导致数据库不能OPEN，必须通过alter database open来打开，只有将其值改回NONE才能正常启动挂载数据库。
但我明白如果需要alter sysdba to system;就必须改参数的值为EXCLUSIVE。但数据库无法正常打开就无法继续了！
下文描述了orapwd，我也照做了，但entries无论指定任何值，都会提示帮助信息，若不指定entries这个参数，则命令可以被执行，但没有什么结果！
不知道问题出在哪里，希望可以回复邮件或ITPUB
http://www.itpub.net/878362.html
非常感谢
Commented by ccnp_server | October 24, 2007 04:38 PM