ORA-28009: connection to sys should be as sysdba or sysoper
Cause: Connect sys/password is no longer a valid syntax.
Action: Try connect sys/password as sysdba or connect sys/password as sysoper. ORA-28009 oracle error is raised when the sys user tries to connect without providing "as sysdba" . This s controlled by the O7_DICTIONARY_ACCESSIBILITY parameter. When this parameter value is set to FALSE, sys users experience this ORA-28009 error. Access to dictionary objects is restricted to the users with the system privileges SYSDBA and SYSOPER. Connecting as SYSDBA gives a user unrestricted privileges to perform any operation on a database or the objects within a database. Data dictionary objects is under SYS schema and is protected by O7_DICTIONARY_ACCESSIBILITY to FALSE settings. Example for the ORA-28009 error:
Connecting as sys user without sysdba privilege. SQL> alter user SYS identified by SYS; SQL> connect sys/sys ERROR: ORA-28009: connection to sys should be as sysdba or sysoper Warning: You are no longer connected to ORACLE. Connecting as sys user with sysdba privilege. SQL> connect sys/sys as sysdba Connected. SQL> O7_DICTIONARY_ACCESSIBILITY parameter info: SQL> SHOW PARAMETER O7_DICTIONARY_ACCESSIBILITY NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ O7_DICTIONARY_ACCESSIBILITY boolean FALSE Modifying the O7_DICTIONARY_ACCESSIBILITY parameter value:
SQL> ALTER SYSTEM SET O7_DICTIONARY_ACCESSIBILITY=TRUE scope=spfile; System altered.
In order make the chnages effective shutdown and startup the database:
SQL> show parameter O7_DICTIONARY_ACCESSIBILITY NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ O7_DICTIONARY_ACCESSIBILITY boolean TRUE Now sys user should be able connect with out "as sysdba" as verified below: SQL> conn sys/sys Connected.
CONCLUSION: Oracle Recommendation is not to set O7_DICTIONARY_ACCESSIBILITY to TRUE.
So in order to avoid this ORA-28009 error, always explicitly specify " as sysdba " when connecting as sys user.
More Details at http://ora-28009.ora-code.info/.
Mendus, Oracle OCP
http://www.ora-code.info/
Cause: Connect sys/password is no longer a valid syntax.
Action: Try connect sys/password as sysdba or connect sys/password as sysoper. ORA-28009 oracle error is raised when the sys user tries to connect without providing "as sysdba" . This s controlled by the O7_DICTIONARY_ACCESSIBILITY parameter. When this parameter value is set to FALSE, sys users experience this ORA-28009 error. Access to dictionary objects is restricted to the users with the system privileges SYSDBA and SYSOPER. Connecting as SYSDBA gives a user unrestricted privileges to perform any operation on a database or the objects within a database. Data dictionary objects is under SYS schema and is protected by O7_DICTIONARY_ACCESSIBILITY to FALSE settings. Example for the ORA-28009 error:
Connecting as sys user without sysdba privilege. SQL> alter user SYS identified by SYS; SQL> connect sys/sys ERROR: ORA-28009: connection to sys should be as sysdba or sysoper Warning: You are no longer connected to ORACLE. Connecting as sys user with sysdba privilege. SQL> connect sys/sys as sysdba Connected. SQL> O7_DICTIONARY_ACCESSIBILITY parameter info: SQL> SHOW PARAMETER O7_DICTIONARY_ACCESSIBILITY NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ O7_DICTIONARY_ACCESSIBILITY boolean FALSE Modifying the O7_DICTIONARY_ACCESSIBILITY parameter value:
SQL> ALTER SYSTEM SET O7_DICTIONARY_ACCESSIBILITY=TRUE scope=spfile; System altered.
In order make the chnages effective shutdown and startup the database:
SQL> show parameter O7_DICTIONARY_ACCESSIBILITY NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ O7_DICTIONARY_ACCESSIBILITY boolean TRUE Now sys user should be able connect with out "as sysdba" as verified below: SQL> conn sys/sys Connected.
CONCLUSION: Oracle Recommendation is not to set O7_DICTIONARY_ACCESSIBILITY to TRUE.
So in order to avoid this ORA-28009 error, always explicitly specify " as sysdba " when connecting as sys user.
More Details at http://ora-28009.ora-code.info/.
Mendus, Oracle OCP
http://www.ora-code.info/
1 comment:
This function is well described to find out the error So in order to avoid this ORA-28009 error, always explicitly specify " as sysdba " when connecting as sys user.
sap upgrade testing
Post a Comment