Home » Server Options » Data Guard » changing password on primary database
changing password on primary database [message #530749] Thu, 10 November 2011 04:10 Go to next message
mamingui
Messages: 83
Registered: July 2006
Member
Hi All,

I'm using 2 databases : Primary and Standby DB. It working fine. I've just one question : I want to change SYS password on primary DB. Can I change it without any action on Standby DB ?

Thanks in advance for your help.

BR
Re: changing password on primary database [message #530768 is a reply to message #530749] Thu, 10 November 2011 05:53 Go to previous messageGo to next message
John Watson
Messages: 4082
Registered: January 2010
Location: Global Village
Senior Member
Think it through. The log writer process of the primary database instance has to connect as SYS, over Oracle Net, to the standby database instance and establish a session for transmitting the redo records. The only way to authenticate a session over Oracle Net is by presenting a password. What password does the log writer on the primary have access too, and therefore what password will it present to the standby?

(Alternatively, you could just test it)
Re: changing password on primary database [message #530784 is a reply to message #530768] Thu, 10 November 2011 08:12 Go to previous messageGo to next message
snowball
Messages: 229
Registered: April 2006
Location: China
Senior Member

Hi, John
Here is what I am trying to reproduce the same scenarios of mamingui.

I did these operations on primary db:

SYS@liuzhou> select database_role from v$database;

DATABASE_ROLE
----------------
PRIMARY



SYS@liuzhou> alter user sys identified by password_changed;

User altered.


SYS@liuzhou> select * from scott.test11;

no rows selected

SYS@liuzhou> insert into scott.test11 values(sysdate);

1 row created.

SYS@liuzhou> select * from scott.test11;

TS
---------------------------------------------------------------------------
10-NOV-11 09.48.29.000000 PM

SYS@liuzhou> commit;

Commit complete.




SYS@liuzhou> alter system switch logfile;

System altered.

SYS@liuzhou> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /arch1/
Oldest online log sequence     54
Next log sequence to archive   55
Current log sequence           55



on standby:
SQL> select * from scott.test11;

TS
---------------------------------------------------------------------------
10-NOV-11 09.48.29.000000 PM



Primary db alert:
Thu Nov 10 21:50:45 2011
Thread 1 advanced to log sequence 55
  Current log# 1 seq# 55 mem# 0: /u01/app/oracle/oradata/liuzhou/redo01.log
Thu Nov 10 21:50:46 2011
LNS: Standby redo logfile selected for thread 1 sequence 55 for destination LOG_ARCHIVE_DEST_2




Standby db alert:
Sat Nov 12 08:32:54 2011
LOGMINER: Log Auto Delete - deleting: /arch1/1_53_749293317.arch
Deleted file /arch1/1_53_749293317.arch
Sat Nov 12 08:32:54 2011
RFS LogMiner: Registered logfile [/arch1/1_54_749293317.arch] to LogMiner session id [1]
Sat Nov 12 08:32:56 2011
Primary database is in MAXIMUM PERFORMANCE mode
RFS[1]: Successfully opened standby log 3: '/u01/app/oracle/oradata/liuzhou/stdbylog03a.log'
Sat Nov 12 08:32:57 2011
LOGMINER: Begin mining logfile: /u01/app/oracle/oradata/liuzhou/stdbylog03a.log



They seems to be funtion normal.
Correct me if I am wrong.

Thanks very much.
Re: changing password on primary database [message #530785 is a reply to message #530784] Thu, 10 November 2011 08:23 Go to previous messageGo to next message
John Watson
Messages: 4082
Registered: January 2010
Location: Global Village
Senior Member
What happens f you re-start the environment?
Re: changing password on primary database [message #530787 is a reply to message #530785] Thu, 10 November 2011 08:28 Go to previous messageGo to next message
snowball
Messages: 229
Registered: April 2006
Location: China
Senior Member

You means restart the db?
Re: changing password on primary database [message #530789 is a reply to message #530787] Thu, 10 November 2011 08:48 Go to previous messageGo to next message
snowball
Messages: 229
Registered: April 2006
Location: China
Senior Member

Restart standby db.
After a time out, primary db will generated this kind of messages:
Thu Nov 10 22:41:16 2011
Error 1017 received logging on to the standby
------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE, 
and that the SYS password is same in the password files.
      returning error ORA-16191
It may be necessary to define the DB_ALLOWED_LOGON_VERSION
initialization parameter to the value "10".  Check the
manual for information on this initialization parameter.
------------------------------------------------------------
Thu Nov 10 22:41:16 2011
Errors in file /u01/app/oracle/admin/liuzhou/bdump/liuzhou_arc0_4389.trc:
ORA-16191: Primary log shipping client not logged on standby
PING[ARC0]: Heartbeat failed to connect to standby 'stdby'. Error is 16191.



So can I say, the authentication between primary and standby is only triggered at the first time they connect without restart db. Because before this restart, I have restart both listeners. They seems work normal though.

Thanks very much.

[Updated on: Thu, 10 November 2011 08:49]

Report message to a moderator

Re: changing password on primary database [message #530790 is a reply to message #530789] Thu, 10 November 2011 09:05 Go to previous messageGo to next message
John Watson
Messages: 4082
Registered: January 2010
Location: Global Village
Senior Member
A comprehensive test. Makes sense, doesn't it: the password must match both sides, because the LGWR on the primary doesn't know any other password to present to the standby. The docs go even further, and say you should copy the password file:
http://download.oracle.com/docs/cd/E11882_01/server.112/e25608/manage_ps.htm#BACIBGFF
I would think that copying it is necessary with 11g, because the salt applied to an 11g password means you'll get a different hash value even if the password is the same.
Re: changing password on primary database [message #530791 is a reply to message #530789] Thu, 10 November 2011 09:08 Go to previous messageGo to next message
snowball
Messages: 229
Registered: April 2006
Location: China
Senior Member

Interesting.
When I change the sys password on primary db without any restart.
Archived log transfered to the standby db without restart db.

Re: changing password on primary database [message #530793 is a reply to message #530790] Thu, 10 November 2011 09:40 Go to previous message
snowball
Messages: 229
Registered: April 2006
Location: China
Senior Member

John Watson wrote on Thu, 10 November 2011 23:05
A comprehensive test. Makes sense, doesn't it: the password must match both sides, because the LGWR on the primary doesn't know any other password to present to the standby. The docs go even further, and say you should copy the password file:
http://download.oracle.com/docs/cd/E11882_01/server.112/e25608/manage_ps.htm#BACIBGFF
I would think that copying it is necessary with 11g, because the salt applied to an 11g password means you'll get a different hash value even if the password is the same.


Hi, John
My test case is based on 10g. I dont have 11g though.
I dont know if the password verification is based the column password on dba_users table.

I have did this test:
SYS@liuzhou> select username, password from dba_users where username='SYS';

USERNAME                       PASSWORD
------------------------------ ------------------------------
SYS                            8A8F025737A9097A

SYS@liuzhou> 
SYS@liuzhou> 
SYS@liuzhou> ho
[oracle@milo ~]$ cd $ORACLE_HOME/dbs
[oracle@milo dbs]$ mv orapwliuzhou orapwliuzhou.bak
[oracle@milo dbs]$ orapwd -h
Usage: orapwd file=<fname> password=<password> entries=<users> force=<y/n> 

  where
    file - name of password file (mand),
    password - password for SYS (mand),
    entries - maximum number of distinct DBA and     force - whether to overwrite existing file (opt),
OPERs (opt), 
  There are no spaces around the equal-to (=) character.
[oracle@milo dbs]$ orapwd password=oracle123 file=orapwliuzhou
[oracle@milo dbs]$ ls -l orapwliuzhou
-rw-r----- 1 oracle oinstall 1536 Nov 10 23:26 orapwliuzhou
[oracle@milo dbs]$ exit
exit

SYS@liuzhou> select username, password from dba_users where username='SYS';

USERNAME                       PASSWORD
------------------------------ ------------------------------
SYS                            8A8F025737A9097A

SYS@liuzhou> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options


[oracle@milo ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Nov 10 23:27:07 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SYS@liuzhou> select username, password from dba_users where username='SYS';

USERNAME                       PASSWORD
------------------------------ ------------------------------
SYS                            8A8F025737A9097A



After recreated file, the hash value is not changed. And even with session relogin. May be after instance started, this value is cached in memory at least at 10g.

Previous Topic: 10g dataguard slow apply and reset log issue
Next Topic: Inconsistent Dictionary on Standby after Reinstatement
Goto Forum:
  


Current Time: Sat Apr 19 08:53:34 CDT 2014

Total time taken to generate the page: 0.07600 seconds