This discussion is archived
6 Replies Latest reply: Nov 14, 2012 3:21 AM by Shivananda Rao RSS

Change user name and password of both Primary and DR instance

Richa Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points