How to recover from lost sys password

Oracle, Oracle数据库安全Security   authentication, nts, oracle net, Oracle网络SQL*NET, remote_login_passwordfile, sql net, sysdba login  

3 comments to How to recover from lost sys password

• admin

  September 12, 2010 at 12:13 AM · Reply

  How to Avoid Common Flaws and Errors Using Passwordfile
  Some information specific to currently desupported versions was retained for
  historical purposes, for example, ‘connect internal’ was deprecated and
  administrative connections should now use the ‘connect AS SYSDBA’ syntax.

  Purpose:
  ~~~~~~~~
  This bulletin discusses

  -> most common passwordfile issues (ora-199x) and their causes/solutions
  -> some hints when migrating, (re)creating controlfile/database, import/export
  with password files

  Scope & Application:
  ~~~~~~~~~~~~~~~~~~~~
  -> How to generate a password file is explained in the following notes:
  Note:1029539.6 UNIX: How to Set up the Oracle Password File
  or
  Note:114384.1 WIN: Checklist for Resolving CONNECT AS SYSDBA Issues

  -> Setup for remote startup/shutdown using Oracle Enterprise Manager is explained in :
  

  -> Strong advice to read before proceeding:
  Note:18089.1 UNIX: Connect INTERNAL / AS SYSBDA Privilege on Oracle 7/8
  Note:50507.1 SYSDBA and SYSOPER Privileges in Oracle
  Note:184696.1 Adding Users to Passwordfile Using Passwordfile Authentication

  Common Flaws and Errors:
  ~~~~~~~~~~~~~~~~~~~~~~~~
  In almost all situations, as a last resort, the passwordfile can be recreated

  1) If for some reason, you cannot connect as SYSDBA / SYSOPER or internal due to
  passwordfile issues, you have to recreate the passwordfile.
  The instance has to be restarted before this gets effective: you can add an
  OS administrator to do this if not available:
  -> Note:18088.1 for Unix
  -> Note:77665.1 for Windows
  Otherwise stop the Oracle service on Windows or kill the pmon process on Unix to
  stop the instance.
  2) Remind to add additional privileged users from v$pwfile_users (SYSDBA or SYSOPER)

  Errors:
  ~~~~~~~
  *** ******************************
  ORA-01031: insufficient privileges
  *** ******************************
  When : SQL> connect sym/sym as SYSDBA

  Additional : init.ora remote_login_passwordfile=EXCLUSIVE or SHARED
  Cause : Username/password is not correct (if the user is added by granting
  SYSDBA to the user, this user is added to the passwordfile with the
  password from the data dictionary)
  or:
  Password file has
  -> incorrect name orapw
  -> is not in $ORACLE_HOME/dbs
  -> has wrong privileges
  They should be -rwSr—–
  For example:
  -rwSr—– 1 server sdb 1536 Mar 25 08:12 orapwv901

  Solve : You can set the correct privileges (Unix) by issueing
  $ chmod 4640 passwordfile
  Also check Note:114384.1
  As a last resort, recreate the password file and remind to add
  additional privileged (sysdba or sysoper) users

  *** **********************************
  ORA-01990: error opening password file
  *** **********************************
  When : SQL> grant SYSDBA to sym;
  Additional : init.ora remote_login_passwordfile=EXCLUSIVE or SHARED
  Cause : Passwordfile not in correct directory or has the wrong name.
  It should be in $ORACLE_HOME/dbs and named orapw
  Example : orapwv901
  Solve : Restore original passwordfile to $ORACLE_HOME/dbs or recreate it
  Also check Note:118367.1

  If you have a shared passwordfile on Windows (NT/Win2K) , you may need to set
  registry parameter ORA_PWFILE to point to your shared passwordfile because the
  default name is not used, detailed in note 114384.1 ’2. Connecting With a Password’.

  *** **************************************************
  ORA-01991: invalid password file ‘/9.0.1/dbs/orapwv901′
  *** **************************************************
  When : After startup of instance
  Additional : Alert file shows: ORA-1991 signalled during: ALTER DATABASE MOUNT
  Database does not mount
  Cause : Incorrect passwordfile
  Solve : Restore original passwordfile to $ORACLE_HOME/dbs or recreate it
  Also check Note:118367.1 and bug 994821
  OR
  When : After recreating controlfile or database
  Additional : remote_login_passwordfile=EXCLUSIVE or SHARED and passwordfile
  already exists
  Alert file shows: ORA-1991 signalled during: ALTER DATABASE MOUNT
  Database does not mount
  Cause : Not correctly documented before 8.1.5 Bug:507683
  Solve : 1. Set init.ora remote_login_passwordfile = NONE
  2. Starting instance to create the database/controlfile
  3. Recreate the passwordfile

  *** ***************************************
  ORA-01993: error writing password file ‘%s’
  *** ***************************************
  When : during start database
  Additional : accompanied by ORA-27091 or ORA-17608 sometimes accompanied by OS
  errors (ie Unix Error code 9 [EBADF] Text: Bad file number )
  Cause : Privileges or OS issue
  Solve : A) If wrong privileges, they should be :
  -rwSr—–
  for example:
  -rwSr—– 1 server sdb 1536 Mar 25 08:12 orapwv901
  The privileges, if inappropriate, are corrected by oracle on
  startup.
  You can set the correct privileges (Unix) by issueing
  $ chmod 4640 passwordfile
  B) Otherwise correct OS issues:
  If wrong owner, use chown command to change it :
  For example:
  $ ls -al orapwV817
  -rwSr—– 1 root dba 1536 Nov 7 11:20 orapwV817
  Connected as root:
  $ chown oracle orapwV817

  C) Or recreate the passwordfile (if corrupted for example)

  *** *************************************************************
  ORA-01994: GRANT failed: cannot add users to public password file
  *** *************************************************************
  When : SQL> grant sysdba to test;
  ERROR at line 1:
  ORA-01994: GRANT failed: cannot add users to public password file
  Additional : init.ora remote_login_passwordfile = NONE
  Cause : cannot add users to passwordfile when remote_login_passwordfile = NONE
  Solve : Set remote_login_passwordfile = EXCLUSIVE and restart instance

  *** ***************************************
  ORA-01995: error reading password file ‘%s’
  *** ***************************************
  When : Recreating controlfile or database
  Additional : remote_login_passwordfile = EXCLUSIVE
  Cause : There is no password file available
  Solve : Either restore correct passwordfile or set remote_login_passwordfile =NONE
  and restart the database
  and retry the operation (recreate database or controlfile)
  and create a passwordfile afterwards

  *** ***********************************************
  ORA-01996: GRANT failed: password file ‘%s’ is full
  *** ***********************************************
  When : SQL> grant sysdba to U1;
  ORA-01996: GRANT failed: password file ‘/DB/dbs/orapwv901′ is full
  Additional : remote_login_passwordfile = EXCLUSIVE
  Cause : The passwordfile is initially created with the parameter entries
  too small. As the file is static after creation, the space is used
  and no more users can be added.
  Solve : Recreate the password file as indicated above: use a larger value
  for parameter entries (larger than the number of users you are
  going to add + 2)
  Do not forget to add existing users : check v$pwfile_users

  *** *******************************************************
  ORA-01997: GRANT failed: user ‘%s’ is identified externally
  *** *******************************************************
  Additional : SQL> grant SYSDBA to test;
  test is an OS authenticated user (identified externally) and can
  connect to Oracle without a password
  Cause : A user identified externally can not be granted SYSDBA/SYSOPER
  Solve : A user identified externally has no Oracle Password: that is why
  this user cannot be added to the passwordfile

  *** ************************************************************
  ORA-01998: REVOKE failed: user SYS always has SYSOPER and SYSDBA
  *** ************************************************************
  When : SQL> alter user SYS identified externally;
  ORA-01998: REVOKE failed: user SYS always has SYSOPER and SYSDBA
  Cause : To maintain database integrity for user SYS, there is a restriction
  with the ALTER USER command. To connect as SYS, you must be
  identifiable by passwordfile or OS user should be in DBA group
  Solve : This is not allowed

  *** *****************************************************************
  ORA-01999: password file mode has changed from ‘exclusive’ to ‘shared’
  *** *****************************************************************
  When : SQL> grant SYSBDA to sym;
  Additional : init.ora remote_login_passwordfile= EXCLUSIVE or SHARED
  Cause : You recreated the passwordfile
  Solve : Restore original passwordfile or bounce the instance so that the
  new passwordfile is in sync.
  Bouncing can be done if you connect as OS authenticated administrator.
  Remind to add additional privileged users (SYSDBA or SYSOPER) from
  v$pwfile_users
  Also check Note:103576.1

  Hints when Using a Passwordfile:
  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  * Migration:
  Note 144804.1: Checklist migration Oracle7 to Oracle9.0.1 and flaws and errors
  1) Advisable to set init.ora remote_login_passwordfile = NONE before migration.
  2) Create a new passwordfile after successfull migration using orapwd utility.
  3) Check which users are in the passwordfile (V$PWFILE_USERS) to be able to
  add them after successfull migration.

  * Create a new database and copying init.ora (bug 825414, bug 572916)
  Set init.ora remote_login_passwordfile = NONE before starting instance to
  create the database.

  * Import (bug:428379)
  Full database import should preferably be done with init.ora
  remote_login_passwordfile = NONE as some versions change the internal password

  * Keep the output of v$pwfile_users somewhere so that you can reconstruct the
  content of the password file in case you lose it.
  Especially if you use additional users with SYSDBA or SYSOPER privileges.

  SQL> select * from V$pwfile_users;

  Example of output

  USERNAME SYSDB SYSOP
  —————————— —– —–
  INTERNAL TRUE TRUE
  SYS TRUE TRUE
  SYSTEM TRUE FALSE
  SYM TRUE FALSE

  Related Documents:
  ~~~~~~~~~~~~~~~~~~
  Note:50507.1 SYSDBA and SYSOPER Privileges in Oracle
  Note:18089.1 UNIX: Connect INTERNAL / AS SYSBDA Privilege on Oracle 7/8
  Note:1029539.6 UNIX: How to Set up the Oracle Password File
  Note:18088.1 UNIX: OS Authentication on Oracle Server
  Note:118367.1 UNIX: ORA-1990 at Startup DB After Creating Password File
  with Wrong Case
  Note:114384.1 WIN: Checklist for Resolving CONNECT AS SYSDBA (INTERNAL)
  Issues
  Note:50429.1 How to Startup/Shutdown a Remote Database Through EM
  Note:1030043.6 ORA-1994 GRANT FAILED, CANNOT ADD USERS TO PUBLIC PASSWORD FILE
  Note:144804.1 Checklist migration Oracle7 to Oracle9.0.1 and flaws and errors.
  Note:103576.1 ORA-1999: When Granting sysdba to User
  Note:77665.1 Connecting to Oracle without a password: OS authentication NT
  Note:184696.1 Adding Users to Passwordfile Using Passwordfile Authentication
  Note:100867.1 Troubleshooting Failures Due to Password Problems Within Oracle
  Failsafe

  Bug:994821 ORA-1991 AND ORAPWSID CANNOT BE MOVED
  Bug:825414 CREATE DATABASE FAILS WITH ORA-1990 ‘ERROR OPENING PASSWORDFILE %S’
  Bug:428379 FULL DB IMPORT W/ REMOTE_LOGIN_PASSWORDFILE EXCLUSIVE CHANGES
  INTERNAL PASSWORD
  Bug:507683 DOCUMENT PROBLEM WITH PASSWORD FILE AND CREATE CONTROL
  FILE/DATABASE
  Bug:381109 ORACLE7 SERVER MESSAGES”,REMOTE_LOGIN_PASSWORDFILE SHOULD BE
  SET TO “EXCLUSIVE
  Bug:284891 CANNOT GRANT SYSDBA TO SCOTT
  Bug:878968 ORA-600 [KZSRSDN: 1], [32] RECREATING CONTROLFILE WITHOUT
  PASSWORD FILE
  Bug:819345 ORAPWD’S ENTRIES= VALUE DOES NOT CORRESPOND TO THE MAXIMUM THAT
  CAN BE ADDED
  Bug:891965 ALTER USER SYS IDENTIFIED EXTERNALLY GIVES ORA-1998
  Bug:572916 CREATING DB WITH REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE FAILING
  WITH ORA-603

  Search words:
  ~~~~~~~~~~~~~
  passwordfile v$pwfile_users ORA-01990 ORA-01031 ORA-01999 ORA-27091 ORA-17608
  rwSr ORA-01991 ORA-01992 ORA-01993 ORA-01994 ORA-01995 ORA-01996 ORA-01997
  ORA-01998 orapwd remote_login_password_file

• admin

  September 12, 2010 at 12:14 AM · Reply

  How to reset the password for the administrative login ‘AS SYSDBA’ if it is lost?
  Applies to:
  Oracle Server – Enterprise Edition – Version: 9.2.0.8 to 11.1.0.6
  Information in this document applies to any platform.
  Checked for relevance on 09-Mar-2010
  Goal
  How to reset the password for the administrative login ‘AS SYSDBA’ if it is lost?

  If for some reason you forgot the remote login password or the passwordfile was corrupted, this document describes how you can recreate the passwordfile. You can only do this if you have the proper privileges to access the passwordfile on the Operating System filesystem. The alternative to this is to use OS authentication to connect to the database as SYSDBA and then change the password with “alter user sys identified by newpassword”.
  Solution
  1. Check v$pwfile_users to list the users having been granted SYSDBA or SYSOPER currently.

  SQL> connect system/manager
  Connected.
  SQL> select * from v$pwfile_users;

  USERNAME SYSDB SYSOP
  —————————— —– —–
  SYS TRUE TRUE
  EXTJOB_USER TRUE FALSE
  SYSTEM TRUE FALSE

  If the database cannot be accessed anymore, use strings on the passwordfile (Unix only):

  $ strings $ORACLE_HOME/dbs/orapw$ORACLE_SID

  ORACLE Remote Password file
  INTERNAL
  EXTJOB_USER
  SYSTEM

  This will give you the usernames that have been granted SYSDBA or SYSOPER.

  2. Check that ‘init.ora’ parameter REMOTE_LOGIN_PASSWORDFILE was set to to EXCLUSIVE:

  SQL> show parameter remote_login_passwordfile

  NAME TYPE VALUE
  ———————————— ———– ——————————
  remote_login_passwordfile string EXCLUSIVE

  3. Shutdown the instance for which this passwordfile can provide authentication, use OS authentication to login, if this was disabled, terminate the instance by killing the smon background process.

  4. Just in case backup your current passwordfile.

  5. Create a new password file using the following command syntax:

  orapwd file= password= entries= force= nosysdba=

  where
  file – name of password file (mand),
  password – password for SYS (mand),
  entries – maximum number of distinct DBA,
  force – whether to overwrite existing file (opt),
  nosysdba – whether to shut out the SYSDBA logon (opt for Database Vault only).
  There are no spaces around the equal-to (=) character.

  On Unix/Linux the passwordfile convention is : $ORACLE_HOME/dbs/orapw$ORACLE_SID
  On MS Windows the passwordfile convention is : %ORACLE_HOME%\database\PWD%ORACLE_SID%.ORA

  6. You can now restart the database.

  7. Now you can try to connect using the new passwordfile, note that for local connections you can likely use OS authentication, so for a true test, make a remote connection as follows:

  $ sqlplus sys/manager@v1020 as sysdba

  8. Grant all the users (except internal) you listed in step 1 the SYSDBA or SYSOPER privilege again:

  SQL> grant sysdba to SYSTEM;

• admin

  September 12, 2010 at 12:15 AM · Reply

  SYSDBA and SYSOPER Privileges in Oracle

  0) Introduction
  ~~~~~~~~~~~~~~~
  This article describes the different ways you can connect to Oracle as an administrative
  user. It describes the options available to connect as SYSDBA and SYSOPER.

  A checklist to troubleshoot SYSDBA/SYSOPER connections is documented separately :

  Note:69642.1 – UNIX: Checklist for Resolving Connect AS SYSDBA Issues

  Oracle 8.1 was the last release to support the ‘CONNECT INTERNAL’ syntax :
  therefore you must use SYSDBA or SYSOPER privileges in current releases.

  1) Administrative Users
  ~~~~~~~~~~~~~~~~~~~~~~~
  There are two main administrative privileges in Oracle: SYSOPER and SYSDBA

  (In version 11g this has been augmented by the SYSASM privilege, this basically
  works in the same manner technically but will not be addressed here
  see note 429098.1 “11g ASM New Feature” for more information)

  SYSDBA and SYSOPER are special privileges as they allow access to a database instance
  even when it is not running and so control of these privileges is totally outside of
  the database itself.

  SYSOPER privilege allows operations such as:
  Instance startup, mount & database open ;
  Instance shutdown, dismount & database close ;
  Alter database BACKUP, ARCHIVE LOG, and RECOVER.
  This privilege allows the user to perform basic operational tasks without the ability to look at user data.

  SYSDBA privilege includes all SYSOPER privileges plus full system privileges
  (with the ADMIN option), plus ‘CREATE DATABASE’ etc..
  This is effectively the same set of privileges available when previously
  connected INTERNAL.

  2) Password or Operating System Authentication
  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

  Password Authentication
  ~~~~~~~~~~~~~~~~~~~~~~~
  Unless a connection to the instance is considered ‘secure’ then you MUST use a
  password to connect with SYSDBA or SYSOPER privilege.

  When the passwordfile is initially created with the uility orapwd it holds the password for
  user SYS, other users can be added to the password file with the ‘GRANT SYSDBA to &USER;’ command.

  Such a user can then connect to the instance for administrative purposes using
  the syntax:

  CONNECT username/password AS SYSDBA

  or

  CONNECT username/password AS SYSOPER

  This is described in more detail in section (5) below.

  Operating System Authentication
  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  If the connection to the instance is local or ‘secure’ then it is possible to
  use the operating system to determine if a user is allowed SYSDBA or SYSOPER
  access. In this case no password is required.
  The syntax to connect using operating system authentication is:

  CONNECT / AS SYSDBA
  or
  CONNECT / AS SYSOPER

  Oracle determines if you can connect thus:

  On Unix/Linux:

  On UNIX the Oracle executable has two group names compiled into it,
  one for SYSOPER and one for SYSDBA.
  These are known as the OSOPER and OSDBA groups.
  Typically these can be set when the Oracle software is installed.

  When you issue the command ‘CONNECT / AS SYSOPER’ Oracle checks if
  your Unix logon is a member of the ‘OSOPER’ group and if so allows you
  to connect.
  Similarly to connect as SYSDBA your Unix logon should be a member of
  the Unix ‘OSDBA’ group.
  The OSDBA groups is the same group as has been historically used to
  allow CONNECT INTERNAL.

  On MS Windows NT/2000/2003/XP:

  On MS Windows the OSOPER and OSDBA groups are hard coded groups thus:

  Group Name Oracle uses this as…
  ~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~
  ORA_OPER OSOPER group for all instances
  ORA_DBA OSDBA group for all instances

  or

  ORA_sid_OPER OSOPER group for a specific Oracle SID
  ORA_sid_DBA OSDBA group for a specific Oracle SID

  When you issue a ‘CONNECT / AS SYSDBA’ , Oracle checks if your MS Windows logon is a
  member of the ‘ORA_sid_DBA’ or ‘ORA_DBA’ group.

  3) OSDBA & OSOPER Groups on Unix/Linux
  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  The ‘OSDBA’ and ‘OSOPER’ groups are chosen at installation time and usually both default
  to the group ‘dba’. These groups are compiled into the ‘oracle’ executable and so are the
  same for all databases running from a given ORACLE_HOME directory.
  The actual groups being used for OSDBA and OSOPER can be checked thus:

  cd $ORACLE_HOME/rdbms/lib
  cat config.[cs]

  The line ‘#define SS_DBA_GRP “group”‘ should name the chosen OSDBA group.
  The line ‘#define SS_OPER_GRP “group”‘ should name the chosen OSOPER group.

  If you wish to change the OSDBA or OSOPER groups this file needs to be modified
  either directly or using the installer.

  Eg: For an OSDBA group of ‘mygroup’

  If your platform has config.c (this is the case for HP-UX, Compaq Tru64
  Unixware and Linux):

  Change: #define SS_DBA_GRP “dba”
  to: #define SS_DBA_GRP “mygroup”

  If your platform has config.s:
  Due to the way different compilers under different architectures generate
  assembler code, it’s not possible to give a universal rule.

  Here are some examples:
  Sun SPARC Solaris:
  ——————
  Change both ocurrences of
  .ascii “dba\0″
  to
  .ascii “mygroup\0″

  IBM AIX/Intel Solaris:
  ———————-
  Change both ocurrences of
  .string “dba”
  to
  .string “mygroup”

  To effect any changes to the groups and to be sure you are using the groups
  defined in this file relink the Oracle executable.
  Be sure to shutdown all databases before relinking:

  Eg:
  mv config.o config.o.orig
  make -f ins_rdbms.mk ioracle

  (Note config.o will be re-created by make because of dependencies automatically)

  For a group to be accepted by Oracle as the OSDBA or OSOPER group it must:

  - Be compiled into the Oracle executable
  - The group name must exist in /etc/group (or in ‘ypcat group’ if NIS is being
  used)
  - It CANNOT be the group called ‘daemon’

  Note: The commands above are examples and may vary between platforms.
  Note: Some Oracle documentation refers to the ability to define OSDBA and OSOPER
  roles using group names of the form ‘ORA_sid_OSDBA’.
  This functionality has not been implemented on Unix (See bug 224071)

  Disabling Operating System Authentication
  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

  Given the above information about the technical implementation details of OS authenication it is
  possible to disable OS authentication by putting non-existant OS group names in the config.c
  (or config.s) file, then (re)move the config.o and relink oracle, however this is not supported
  for the following reasons:

  - Many tools like RMAN rely on the OS authentication to work, in any documentation and references
  this behaviour is expected to work.
  - If you disable OS authentication like this the administrative connections AS SYSDBA/SYSOPER can only
  make use of the passwordfile, if there’s something wrong with it no one can login, if you consider
  in a broader sense that availability is also part of security then this means it negatively impacts
  the security of your system.
  - Moreover it only provides a false sense of security since a DBA with access to the oracle software
  owner can rebuild the password file or relink oracle to restore it.

  Important notes about ‘CONNECT / AS SYSDBA’
  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  On Unix systems a user may be a member of more than one group.
  To connect as an administrative user without supplying a password:

  - One of the groups of which the user is a member should be either the OSDBA or
  OSOPER groups as defined in config.c (config.s on some platforms) and as
  linked into the ‘oracle’ executable.
  - The group must be a valid group as defined in /etc/group (or as defined in NIS
  by ‘ypcat group’)
  - The users PRIMARY group (Ie: the one shown by the ‘id’ command) cannot be the
  special group ‘daemon’.

  It is quite common for the ‘root’ user to be required to have SYSDBA or SYSOPER
  privilege. Unfortunately it is also common for the root users’ primary group to be the
  group ‘daemon’ which may prevent it from being allowed to connect without a password.
  There are two ways to tackle this problem:

  a) Make the root users PRIMARY group the OSDBA group
  OR
  b) Where available use the ‘newgrp’ command to change the users primary group to
  the DBA group.
  Eg: $ newgrp dbagroup
  $ sqlplus /nolog
  SQL> connect / as sysdba

  This can also be used in shellscripts thus:

  :
  newgrp dbagroup < # Commands requiring connect internal privilege
  # Eg: dbstart
  !

  OR
  c) For systems where 'newgrp' is not available or does not work from scripts you
  can use 'su' instead.
  Eg:
  :
  su - oracle < # Commands requiring administrative connect privilege
  !

  Note: The user you 'su' to should be able to 'connect / as sysdba' without a
  password, for example by having their primary group as the OSDBA group.

  Some Oracle releases have problems with identifying the OSDBA group when it is
  not the users primary group.
  If you encounter problems with connecting and the OSDBA group is set correctly
  try making the users primary group the OSDBA group, or use 'newgrp' as in (b)
  above.

  4) OSDBA & OSOPER Groups on MS Windows
  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  The 'OSDBA' and 'OSOPER' groups on NT are simply groups with the name "ORA_DBA",
  "ORA_OPER", "ORA_sid_DBA" or "ORA_sid_OPER", where 'sid' is the instance name.

  Eg: To make a user an administrative user simply:

  a) Ensure there is a line in the SQLNET.ORA file which reads:
  SQLNET.AUTHENTICATION_SERVICES = (NTS)
  b) Create a LOCAL user
  c) Create a local NT group ORA_DBA or ORA_sid_DBA where 'sid' is in upper case
  d) Add the user to the ORA_DBA or ORA_sid_DBA group
  e) That user should now be able to "connect / as sysdba"

  If these requirements are not met, you get an ORA-01031 error.

  Domain prefixed usernames
  ~~~~~~~~~~~~~~~~~~~~~~~~~
  It is possible to set up usernames which include the domain as a prefix to the
  username.
  Eg: "OPS$\“.
  To do this you need to use the registry entry OSAUTH_PREFIX_DOMAIN and creating
  users with USERNAMEs of the form “OPS$\“.
  This is described in detail in note 60634.1

  5) Password Authentication
  ~~~~~~~~~~~~~~~~~~~~~~~~~~
  Remote connections require the database to be configured to allow remote DBA
  operations. The remote user will have to supply a password in order to connect
  as either SYSDBA or SYSOPER. The only real exception to this is on MS Windows
  where remote connections may be secure.

  Ie: To perform a remote connect as SYSDBA or SYSOPER you must use the syntax
  ‘CONNECT username/password AS SYSDBA’

  To allow remote administrative connections you must:

  - Set up a password file for the database on the server
  - Set up any relevant init.ora parameters

  5.1) Setting up a Password File
  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  The SYSDBA/SYSOPER password protection is controlled by an Oracle ‘Password’
  file. The basic concept is that a special file is created to hold the ‘SYSDBA’ and
  ‘SYSOPER’ passwords. Users with SYSDBA or SYSOPER privilege granted in the
  password file can be seen in the view V$PWFILE_USERS.

  To create a password file log in as the Oracle software owner and issue the
  command:

  orapwd file= password= entries=

  using the required password.

  On Unix/Linux the passwordfile convention is : $ORACLE_HOME/dbs/orapw$ORACLE_SID
  On MS Windows the passwordfile convention is : %ORACLE_HOME%\database\PWD%ORACLE_SID%.ORA

  Except in a Database Vault installation, the location on Windows 32-bit is
  %ORACLE_HOME%\dbs\orapw%ORACLE_SID%, see note 429818.1

  The file name is important and should be specified as above.
  You should create this file when the database is shut down.

  To change a password you can use the syntax: ALTER USER &DBAUSER identified by &newpassword,
  the changes will be synchronized in the passwordfile, in case this does not work you can recreate
  the passwordfile as follows:

  - Check v$pwfile_users and note the SYSDBA and SYSOPER privileges being granted.
  - Shut down the database.
  - Rename the password file.
  - Issue a new ORAPWD command with a new password to set the SYS password
  - Grant SYSDBA and/or SYSOPER to the other users from the first step.

  5.2) Setting up the Init.Ora file
  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  To enable remote administrative connections set the init.ora parameters thus:

  REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

  EXCLUSIVE forces the password file to be tied exclusively to a single instance.
  To disable remote administrative connections set REMOTE_LOGIN_PASSWORDFILE=NONE

  Note: The setting of REMOTE_OS_AUTHENT does NOT affect the ability to connect as
  SYSDBA or SYSOPER from a remote machine. This parameter was deprecated in 11g and
  should not be used, it is for ‘normal’ users that use OS authentication and therefore
  it is not relevant to this discussion.

  Note: Some (old) documentation may indicate SQL*Net needs configuring to connect
  from remote machines.
  In particular the following are NOT used:

  SQL*Net V2: The REMOTE_DBA_OPS_ALLOWED / REMOTE_DBA_OPS_DENIED parameters are
  irrelevant

  6) Special Notes
  ~~~~~~~~~~~~~~~~~~~~~~~~~

  Common Errors
  ~~~~~~~~~~~~~

  ORA-01031: insufficient privileges
  Connect Internal has been issued with no password.
  For local connections the user is NOT in the DBA group as compiled
  into the ‘oracle’ executable.
  For remote connections you must always supply a password.

  This error can also occur after a successful connect internal/password if there
  REMOTE_LOGIN_PASSWORDFILE is either unset or set to NONE in the init.ora file.

  ORA-01017: invalid username/password; logon denied
  This is a fairly general error that indicates one of the following:
  – REMOTE_LOGIN_PASSWORDFILE is set to NONE
  – The password file does not exist
  – The password supplied does not match the one in the password file
  – The password file been changed since the instance was started

  Deleting/Changing the Password File
  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

  If you delete the Oracle password file while the instance is running you will
  NOT be able to connect AS SYSDBA from remote machines, even if you re-create the
  file.
  You must:
  – Shutdown the instance (using a local connection)
  – Create the new password file
  – You can now connect remotely and restart the instance

Copyright © 2012 Oracle Clinic – Maclean Liu的个人技术博客 - All Rights Reserved
Powered by WordPress & Atahualpa