6 Replies Latest reply: Nov 14, 2012 5:21 AM by Shivananda Rao RSS

    Change user name and password of both Primary and DR instance

    Richa
      Hi,

      Primary database name - TEST
      DR database name - TEST

      Right now we use / as sysdba to login in our database. This doesn’t require any password to login as we login as sys user.

      SQL*Plus: Release 11.1.0.7.0 - Production on Wed Nov 7 00:25:19 2012
      Copyright (c) 1982, 2008, Oracle. All rights reserved.

      Enter user-name: */ as sysdba*

      Connected to:
      Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
      With the Partitioning, OLAP, Data Mining and Real Application Testing options

      Is there any way to change it to sys@test as sysdba and to assign some password as example:-

      Here username is used as sys and password as abc
      SQL*Plus: Release 11.1.0.7.0 - Production on Wed Nov 7 00:25:19 2012
      Copyright (c) 1982, 2008, Oracle. All rights reserved.

      Enter user-name: sys@test as sysdba
      Enter password:

      Connected to:
      Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
      With the Partitioning, OLAP and Data Mining options


      And for our DR as example

      Here username is used as sysdr and password as abc
      Enter user-name: sysdr@test as sysdba
      Enter password:

      Connected to:
      Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
      With the Partitioning, OLAP and Data Mining options

      If there is any doc with commands or some video link then it will be more helpful.
      Thanks
        • 1. Re: Change user name and password of both Primary and DR instance
          Shivananda Rao
          Hello,

          To change the password of SYS user in a dataguard environment:

          1. Recreate the password file in primary database server using "orapwd" utility.
          2. Copy the newly created password file from primary database server to the standby database server and rename it according to your standby SID. (If on windows, then PWD<Standby_SID>.ora , if on linux then orapw<Standby_SID>.)

          Refer this http://emrebaransel.blogspot.com/2008/11/changing-sys-password-in-dataguard.html

          Please consider closing your old threads by providing appropriate poinsts if you feel that they have been answered. Please keep the forum clean.
          • 2. Re: Change user name and password of both Primary and DR instance
            mseberg
            Why not just use :
            sqlplus "/ as sysdba"
            Changing the password probably does not help you much.

            However if it helps we change our SYS and SYSTEM password on a regular basis using this method :
            ALTER USER SYSTEM IDENTIFIED BY newpassword;
            You don't have to recreate the password file ( at least not in 11.2.0.2 or 11.2.0.3 ).

            Just copy it ( scp it ) to the standby and rename it. And remember to cancel recovery and restart the Standby on the new password file.

            Best Regards

            mseberg
            • 3. Re: Change user name and password of both Primary and DR instance
              LaserSoft
              Hi,

              The password for the sys user needs to be the same on the standby as the primary's when a remote login password file is used for redo transport authentication.

              That's why you need to copy the password file from the primary to the standby after changing the sys password on the primary database.

              However, the 11g database introduces a new init parameter REDO_TRANSPORT_USER which specifies the name of the user whose password verifier is used when a remote login password file is used for redo transport authentication. This user must have SYSOPER privileges and must have the same password in the database that initiates the redo transport session and in the database that is the target of the redo transport session.

              If this parameter is not specified, then the password verifier of the SYS user will be used when a remote login password file is used for redo transport authentication.

              Conclusion: As long as you specify REDO_TRANSPORT_USER to a user who has SYSOPER privileges on both the primary and the standby databases, then you don't need to copy the password file from the primary database to the standby database whenever you change the sys password on the primary database.

              SQL>GRANT SYSOPER to &USER;

              SQL>ALTER SYSTEM SET REDO_TRANSPORT_USER = <user_name> SID='*';

              The user you specified for REDO_TRANSPORT_USER must be an existing user in the database which exactly matches the value of the USERNAME column of a row in the V$PWFILE_USERS view and the value of the SYSDBA or SYSOPER column of the row must also be TRUE.

              If you need to change the password for the user specified in REDO_TRANSPORT_USER, then you would need to copy the password file from the primary to the standby site.

              Thanks
              LaserSoft
              • 4. Re: Change user name and password of both Primary and DR instance
                972755
                Hi,
                You can change the password for sys user by re-creating the password file..use the following steps to change it

                on primary (primary db name is cds in my example)-replace the password with your password

                cd $ORACLE_HOME/dbs
                orapwd FILE=orapwcds password=singh123 ENTRIES=30 force=y

                now copy this new password file to standby oracle_home

                on standby (standby db name is cdssty in my example)

                cd cd $ORACLE_HOME/dbs
                and then rename it
                mv orapwcds orapwcdssty
                • 5. Re: Change user name and password of both Primary and DR instance
                  Richa
                  Hi,
                  What will be in this case:-

                  -PRIMARY-
                  Enter user-name: sys@test as sysdba
                  Enter password: 1234

                  -DR-
                  Enter user-name: sys@testdr as sysdba
                  Enter password: 1234

                  Here in DR its testdr.
                  Thanks
                  • 6. Re: Change user name and password of both Primary and DR instance
                    Shivananda Rao
                    Richa wrote:
                    Hi,
                    What will be in this case:-

                    -PRIMARY-
                    Enter user-name: sys@test as sysdba
                    Enter password: 1234

                    -DR-
                    Enter user-name: sys@testdr as sysdba
                    Enter password: 1234

                    Here in DR its testdr.
                    Thanks
                    Hi Richa,

                    "TEST" and "TESTDR" are just the connection strings that are used to connect to the database. The connection strings do not have the password. The main concept of password in Dataguard Env is that the password of SYS user needs to be same on both the primary and the standby database. So, copy the password file from the primary server to the standby server and rename it on the standby server according to the standby SID.