ORA-01017、密码文件、OPW-00009、remote_login_passwordfile等

RDBMS 12.2.0.1

参考文档：（修改密码文件），在12c中，可以将密码文件生成到asm上，也可以生成在文件系统上。如果加上了dbuniquename就会生成到asm上。

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/admin/getting-started-with-database-administration.html#GUID-2031F0DC-CC00-468C-9DFA-E6299420FE21

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/REMOTE_LOGIN_PASSWORDFILE.html#GUID-6619299E-95E8-4821-B123-3B5899F046C7

问题1

使用sys/password@service_name as sysdba连接到数据库连接不上。

原因1 没有密码文件

原因2 参数remote_login_passwordfile被设置成了none。下图就可以解释remote_login_passwordfile参数的问题导致无法通过sys、system账号连接数据库。

问题2 在使用orapwd生成密码文件的时候，提示OPW-00009: Could not establish connection to Automatic Storage Management instance

[oracle@XXX]$ orapwd file=orapwtest dbuniquename=test sys=123456

OPW-00009: Could not establish connection to Automatic Storage Management instance

原因： 使用了dbuniquename，则会生成到asm下。生成到文件系统下，不需要dbuniquename参数。可以参考官方文档中的Example 1-22 Creating a Database Password File Located in a File System

[oracle@WMS-DB-oracle dbs]$ orapwd file=orapwSCPRD 

Enter password for SYS: 

OPW-00029: Password complexity failed for SYS user : Password must contain at least 1 special character.
[oracle@WMS-DB-oracle dbs]$  -- 里面加个特殊符号就行了。

1.7.2 Creating a Database Password File with ORAPWD

You can create a database password file with ORAPWD.

To create a database password file:

• Run the ORAPWD command.

Example 1-19 Creating a Database Password File Located in an Oracle ASM Disk Group

The following command creates a database password file in 12.2 format named orapworcl that is located in an Oracle ASM disk group. The DBUNIQUENAME argument is required because the database password file is located in an Oracle ASM disk group.

orapwd FILE='+DATA/orcl/orapworcl' DBUNIQUENAME='orcl' FORMAT=12.2

Example 1-20 Creating a Database Password File with a SYSBACKUP Entry

The following example is the similar to Example 1-19 except that it creates a SYSBACKUP entry in the database password file. The password file is in 12.2 format by default.

orapwd FILE='+DATA/orcl/orapworcl' DBUNIQUENAME='orcl' SYSBACKUP=password FORMAT=12.2

Example 1-21 Creating a Database Password File with External Authentication for SYS and SYSKM

The following example is the similar to Example 1-19 except that it specifies an external name for the SYS and SYSKM administrative users.

orapwd FILE='+DATA/orcl/orapworcl' DBUNIQUENAME='orcl' FORMAT=12.2 
sys=external('KerberosUserSYS@example.com')
syskm=external('KerberosUserSYSKM@example.com')

Example 1-22 Creating a Database Password File Located in a File System

The following command creates a database password file in 12.2 format named orapworcl that is located in the default location in an operating system file system.

orapwd FILE='/u01/oracle/dbs/orapworcl' FORMAT=12.2

Example 1-23 Migrating a Legacy Database Password File to Oracle Database 12c Format

The following command migrates a database password file in legacy format 12.2 format. The password file is named orapworcl, and it is located in an operating system file system. The new database password file replaces the existing database password file. Therefore, FORCE must be set to y.

orapwd FILE='/u01/oracle/dbs/orapworcl' FORMAT=12.2 INPUT_FILE='/u01/oracle/dbs/orapworcl' FORCE=y

Example 1-24 Resetting the Password for the SYS Administrative User

The following command resets the password for the SYS administrative user. The new database password file replaces the existing database password file. Therefore, FORCE must be set to y.

orapwd FILE='/u01/oracle/dbs/orapworcl' SYS=Y INPUT_FILE='/u01/oracle/dbs/orapworcl' FORCE=y

You are prompted to enter the new password for the SYS administrative user.

Example 1-25 Describing a Password File

The following command describes the orapworcl password file.

orapwd DESCRIBE FILE='orapworcl' 
Password file Description : format=12.2

也可以多个数据库共享同一个密码，参考官网的1.7.3 Sharing and Disabling the Database Password File  这里就不贴出来了。

关于remote_login_passwordfile参数的说明

1.266 REMOTE_LOGIN_PASSWORDFILE

REMOTE_LOGIN_PASSWORDFILE specifies whether Oracle checks for a password file.

Property	Description
Parameter type	String
Syntax	REMOTE_LOGIN_PASSWORDFILE = { shared | exclusive | none }
Default value	exclusive
Modifiable	No
Modifiable in a PDB	No
Basic	Yes
Oracle RAC	Multiple instances must have the same value.

Values

• shared

  One or more databases can use the password file. The password file can contain SYS and non-SYS users.

  When REMOTE_LOGIN_PASSWORDFILE is set to shared:

  • The SYS password cannot be changed. If you try, the password change operation fails with "ORA-28046: Password change for SYS disallowed."

  • The password of any user who has SYS* admin privileges (SYSDBA, SYSOPER, SYSASM, SYSBACKUP, SYSDG, SYSKM) cannot be changed. If you try, the password change operation fails with "ORA-01999: password file cannot be updated in SHARED mode."

  • Grants of SYS* admin privileges (SYSDBA, SYSOPER, SYSASM, SYSBACKUP, SYSDG, SYSKM) to individual users are not allowed. For example, grant sysdba to scott fails with "ORA-01999: password file cannot be updated in SHARED mode." Similarly, revoke of SYS* admin privileges fails.

  • If the password file does not exist, then the behavior is the same as setting REMOTE_LOGIN_PASSWORDFILE to none.

• exclusive

  The password file can be used by only one database. The password file can contain SYS and non-SYS users.

  When REMOTE_LOGIN_PASSWORDFILE is set to exclusive, if the password file does not exist, then the behavior is the same as setting REMOTE_LOGIN_PASSWORDFILE to none.

• none

  Oracle ignores any password file. Therefore, privileged users must be authenticated by the operating system.

Note:

If you change REMOTE_LOGIN_PASSWORDFILE to exclusive or shared from none, then ensure that the password file is synchronized with the dictionary passwords.

END