Oracle10g Data guard會不會自動更新sys使用者密碼？

透過如下測試證明，在Oracle 10g中修改主庫的sys使用者密碼，不會自動更新到備庫的資料庫和密碼檔案上。測試過程如下：

1、在主庫

$sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Fri Aug 12 15:59:14 2011

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

SQL> select open_mode from v$database;

OPEN_MODE

----------

READ WRITE

SQL> create user test identified by test;

User created.

SQL> select * from v$pwfile_users;

USERNAME SYSDB SYSOP

------------------------------ ----- -----

SYS TRUE TRUE

[@more@]

SQL> grant connect , alter user to test;

Grant succeeded.

SQL> conn test/test

Connected.

SQL> grant select on dba_users to test;

Grant succeeded.

SQL> conn test/test

Connected.

SQL> col username format a10

SQL> col password format a30

SQL> select password , username from dba_users where username ='TEST';

PASSWORD USERNAME

------------------------------ ----------

7A0F2B316C212D67 TEST

SQL> alter user test identified by test123;

User altered.

SQL> select password , username from dba_users where username ='TEST';

PASSWORD USERNAME

------------------------------ ----------

7C029A1EC393C725 TEST

SQL> conn / as sysdba

Connected.

SQL> show user

USER is "SYS"

SQL> select * from v$pwfile_users;

USERNAME SYSDB SYSOP

---------- ----- -----

SYS TRUE TRUE

透過以上可以看出，如果沒有給sysdba或sysoper許可權，則使用者雖然在資料庫字典中密碼發生變化，但是不會寫到密碼檔案中。

SQL> grant sysdba to test;

Grant succeeded.

SQL> select * from v$pwfile_users;

USERNAME SYSDB SYSOP

---------- ----- -----

SYS TRUE TRUE

TEST TRUE FALSE

只要給予sysdba許可權，才能寫到密碼檔案中；

2、備庫的查詢結果如下：

$sqlplus sys/abc123@standby as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Fri Aug 12 15:57:35 2011

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

SQL> select open_mode from v$database;

OPEN_MODE

----------

MOUNTED

SQL> select * from v$pwfile_users;

USERNAME SYSDB SYSOP

------------------------------ ----- -----

SYS TRUE TRUE

在主庫給test使用者授權sysdba許可權前後多次執行以上命令，發現使用者根本沒有同步過來

3、在主庫重新修改Test使用者（sysdba）密碼

SQL> alter user test identified by test;

User altered.

SQL> select password , username from dba_users where username ='TEST';

PASSWORD USERNAME

------------------------------ ----------

7A0F2B316C212D67 TEST

4、在備庫再查詢v$pwfile_users

SQL> select * from v$pwfile_users;

USERNAME SYSDB SYSOP

------------------------------ ----- -----

SYS TRUE TRUE