ITPub博客

Oracle 12c的DG自动同步密码文件--ASM 新特性:共享密码文件

原创 Oracle 作者:lhrbest 时间:2018-10-10 15:51:46 0 删除 编辑

Oracle 12c的DG自动同步密码文件--ASM 新特性:共享密码文件


orapwd file='+data/HTZXDBPRI/password/orapwhtzxdbpri' force=y dbuniquename=htzxdbpri format=12 sys=oracle sysbackup=oracle sysdg=oracle syskm=oracle
orapwd input_file='+data/HTZXDBPRI/password/orapwhtzxdbpri' file='+ocr/asm/password/orapwASM' asm=y force=y 
srvctl config db -d htzxdbpri -a
srvctl modify db -d htzxdbpri -pwfile '+data/HTZXDBPRI/password/orapwhtzxdbpri'



oracle 11g 12c dg环境 SYS密码同步的问题


When password file is used for Redo Transport Authentication, the password of redo transport user should be same across primary and all its physical and snapshot standby databases. By default SYS user is used to authenticate redo transport sessions when password file is used.

Till 12cR1 Dataguard setup, if there is a change in password file of Primary database like Changes in password or Changes in admin privileges, then the password file needs to be copied from Primary database to standby database server and rename it according to standby database Instance name. If the password file of Primary and Standby databases are not same, then errors will be seen.

In 12cR2, the password file of standby database gets synchronized automatically when there is a change in Primary database password file. The password file change of Primary will be included in Redo and when the Redo is applied to standby, the synchronization happens in the background.

这里延伸几个知识点:

1 、 11g中口令文件并不能实现存放于asm共享访问,修改SYS密码,需要再每个RAC节点手动实施同步 ,同样备库的口令文件也要手动进行更新覆盖
2、12.1版本可以实现口令文件ASM共享存储,RAC中只需要一个节点执行alter user sys 就可以实现主库所有节点同步,这点是11g中无法实现。但是备库中口令文件依然需要手动同步主库的口令文件过来覆盖
3 、12.2版本oracle在口令文件ASM共享存储的前提下,实现了口令文件自动同步主备所有节点

MOS --- 
Automatic Password file synchronization in 12.2 Dataguard Standby database

Data Guard Standby Automatic Password file Synchronization in 12.2 (文档 ID 2307365.1)




1 概念

This feature automatically synchronizes password files across Oracle Data Guard configurations. When the passwords of SYS, SYSDG, and so on, are changed, the password file at the primary database is updated and then the changes are propagated to all standby databases in the configuration.
该特性自动同步Oracle数据保护配置中的密码文件。当SYS、SYSDG等的密码发生更改时,主数据库中的密码文件被更新,然后将更改传播到配置中的所有备用数据库。
This feature provides additional automation that further simplifies management of Oracle Data Guard configurations.
这个特性提供了额外的自动化,进一步简化了Oracle数据保护配置的管理。
Redo Transport Authentication Using a Password File
In an Oracle Data Guard configuration, all physical and snapshot standby databases must use a copy of the password file from the primary database. That copy is automatically refreshed whenever an administrative privilege (SYSDG, SYSOPER, SYSDBA, and so on) is granted or revoked, and after the password of any user with administrative privileges is changed. The only exception to this is far sync instances. Updated password files must still be manually copied to far sync instances because far sync instances receive redo, but do not apply it. Once the password file is up-to-date at the far sync instance the redo containing the password update at the primary is automatically propagated to any standby databases that are set up to receive redo from that far sync instance. The password file is updated on the standby when the redo is applied.
在Oracle数据保护配置中,所有物理和快照备用数据库必须使用来自主数据库的密码文件副本。当管理员权限(SYSDG、SYSOPER、SYSDBA等)被授予或撤销时,该副本将自动刷新,并且任何具有管理员权限的用户的密码发生更改后,该副本将自动刷新。唯一的例外是far sync instances。更新的密码文件必须手动复制到far sync instances,因为far sync instances接收重做,但不应用它。一旦密码文件在远同步实例中是最新的,那么在主服务器上包含密码更新的重做就会自动传播到任何准备far sync instances接收重做的备用数据库。在应用重做时,在备用服务器上更新密码文件。

2 实验

2.1 tnsname.ora 中配置主备库的网络服务

PDBCNDBA_P =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.74)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = cndba)
    )
  )
PDBCNDBA_S =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.75)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = cndba)
    )
  )

2.2 修改主库sys 用户密码  

[oracle@dg1 admin]$ sqlplus sys/PDBCNDBA_P@cndba as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Tue Aug 15 10:55:49 2017
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> alter user sys identified by oracle_p; 
User altered.
2.3 验证备库密码也跟着改变。
SQL> connect sys/oracle_p@PDBCNDBA_P as sysdba
Connected.
SQL> connect sys/oracle_p@"PDBCNDBA_S" as sysdba
Connected.

3 参考链接

http://docs.oracle.com/database/122/NEWFT/new-features.htm#NEWFT-GUID-A6211BAC-0534-44B9-B988-A7CC2B97044F  

https://petesdbablog.wordpress.com/2017/03/11/12-2-new-feature-automatically-synchronize-password-files-in-oracle-data-guard/  

https://uhesse.com/2017/01/10/auto-sync-for-password-files-in-oracle-12c-data-guard/  


ORACLE 12C ASM 新特性:共享密码文件



在ORACLE 12C之前大家都知道密码文件是存放在?/dbs或者?/database中,如果要修改修改sysdba权限的用户密码时候,会去修改密码文件,而在rac数据库的sys密码文件是存在各个节点中,这个时候修改sysdba权限的密码就需要在两个节点都要做同样的操作,而对于数据库来说本身是只要在一个节点上修改即可,因为密码是记录在user$中,就是因为密码文件非共享且在各个节点中都有,因此需要在各个节点均要执行修改密码命令,确保密码文件被正常修改。因为rac 密码文件非共享的机制存在,导致修改sysdba权限密码繁琐,有些时候甚至有节点忘记修改,导致需要使用密码文件操作数据库的时候不能正常进行,DG传输日志异常等故障。在ORACLE 12C中为了解决这个问题,引入了密码文件可以存入ASM新特性,从而使得密码文件存储在ASM中实现所有节点共享,从而解决该问题.
ASM存储密码文件前提条件 COMPATIBLE.ASM>= 12.1
查询ASM信息

SQL>  select * from v$version;
 
BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0
PL/SQL Release 12.1.0.1.0 - Production                                                    0
CORE    12.1.0.1.0      Production                                                        0
TNS for Linux: Version 12.1.0.1.0 - Production                                            0
NLSRTL Version 12.1.0.1.0 - Production                                                    0
 
SQL> select NAME ,COMPATIBILITY from v$asm_diskgroup;
 
NAME                            COMPATIBILITY
------------------------------ ------------------------------------------------------------
DATA                           12.1.0.0.0

查询crs中关于db配置

[grid@xifenfei ~]$  srvctl config database -d cdb
Database unique name: cdb
Database name: cdb
Oracle home: /u01/app/oracle/product/12 .1 /db_1
Oracle user: oracle
Spfile: +DATA /cdb/spfilecdb .ora
Password file :
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: MANUAL
Database instance: cdb
Disk Groups: DATA
Services:

这里db的password file为空,即表示使用默认值,也就是为$ORACLE_HOME/dbs/orapwxifenfei

创建密码文件存储在ASM中

--创建db新密码文件
[oracle@xifenfei ~]$ orapwd file = '+data/CDB/orapwdxifenfei' dbuniquename= 'cdb'
 
Enter password for SYS:
----输入sys用户密码
 
--创建asm新密码文件
orapwd file = '+data/ASM/orapwasm' asm=y
----asm=y 表示创建的密码文件为asm的
  
--使用老密码文件创建db /asm 新密码文件
orapwd input_file= '/oraclegrid/dbs/orapwasm' file = '+data/ASM/orapwasm' [asm=y]
----input_file 表示使用老的密码文件创建新的存储在ASM中的密码文件

查看ASM中密码文件

ASMCMD> showversion
ASM version         : 12.1.0.1.0
ASMCMD> pwd
+data /cdb
ASMCMD>  ls -l orapwdxifenfei
Type      Redund  Striped  Time             Sys  Name
PASSWORD  UNPROT  COARSE   MAY 31 19:00:00  N    orapwdxifenfei => +DATA /CDB/PASSWORD/pwdcdb .290.816897265

配置crs中password file项

[grid@xifenfei ~]$ srvctl modify database -db cdb -pwfile  +data /CDB/orapwdxifenfei

查询crs中关于db配置

[grid@xifenfei ~]$  srvctl config database -d cdb
Database unique name: cdb
Database name: cdb
Oracle home: /u01/app/oracle/product/12 .1 /db_1
Oracle user: oracle
Spfile: +DATA /cdb/spfilecdb .ora
Password file : +data /CDB/orapwdxifenfei
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: MANUAL
Database instance: cdb
Disk Groups: DATA
Services:

至此数据库启动使用密码ASM中的密码文件完成,补充说明,该方式配置在ASM中的密码文件,只能是通过crs方式启动db才会生效,如果手工使用sqlplus启动数据库不会使用该密码文件,还是使用默认密码文件。这里也就提醒大家操作规范: 在RAC环境(包含单节点的GI环境)中,对数据库的启动关闭操作强烈建议使用crs相关命令来完成,而不推荐使用sqlplus命令


Data Guard Standby Automatic Password file Synchronization in 12.2 (文档 ID 2307365.1)

APPLIES TO:

Oracle Database - Enterprise Edition - Version 12.2.0.1 and later
Information in this document applies to any platform.

GOAL

Automatic Password file synchronization in 12.2 Dataguard Standby database

SOLUTION

When password file is used for Redo Transport Authentication, the password of redo transport user should be same across primary and all its physical and snapshot standby databases. By default SYS user is used to authenticate redo transport sessions when password file is used. 

Till 12cR1 Dataguard setup, if there is a change in password file of Primary database like Changes in password or Changes in admin privileges, then the password file needs to be copied from Primary database to standby database server and rename it according to standby database Instance name. If the password file of Primary and Standby databases are not same, then errors will be seen.

In 12cR2, the password file of standby database gets synchronized automatically when there is a change in Primary database password file. The password file change of Primary will be included in Redo and when the Redo is applied to standby, the synchronization happens in the background.

Exception: Far-sync Instances (where the redo will not be applied). We need to manually copy the password file. Once the password file is sync at Far-Sync instance, the redo having password file information will get transmitted to standby which are suppose to receive from Far-sync instance.

Example:

PRIMARY>select username, sysdba, sysoper, sysasm,sysbackup,authentication_type AT from v$pwfile_users;

USERNAME SYSDB SYSOP SYSAS SYSBA AT
-------------------- ----- ----- ----- ----- --------
SYS TRUE TRUE FALSE FALSE PASSWORD
SYSDG FALSE FALSE FALSE FALSE PASSWORD
SYSBACKUP FALSE FALSE FALSE TRUE PASSWORD
SYSKM FALSE FALSE FALSE FALSE PASSWORD

STANDBY>select username, sysdba, sysoper, sysasm,sysbackup,authentication_type AT from v$pwfile_users;

USERNAME SYSDB SYSOP SYSAS SYSBA AT
---------- ----- ----- ----- ----- --------
SYS TRUE TRUE FALSE FALSE PASSWORD
SYSDG FALSE FALSE FALSE FALSE PASSWORD
SYSBACKUP FALSE FALSE FALSE TRUE PASSWORD
SYSKM FALSE FALSE FALSE FALSE PASSWORD

Let's grant a role to a user at Primary database which will make changes in Password file

PRIMARY>Grant SYSOPER to user1;

Grant succeeded.

PRIMARY>select username, sysdba, sysoper, sysasm,sysbackup,authentication_type AT from v$pwfile_users;

USERNAME SYSDB SYSOP SYSAS SYSBA AT
-------------------- ----- ----- ----- ----- --------
SYS TRUE TRUE FALSE FALSE PASSWORD
SYSDG FALSE FALSE FALSE FALSE PASSWORD
SYSBACKUP FALSE FALSE FALSE TRUE PASSWORD
SYSKM FALSE FALSE FALSE FALSE PASSWORD
USER1 FALSE TRUE FALSE FALSE PASSWORD

Password file of Primary database:

-rw-r-----. 1 oracle oinstall 4096 /u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapwprimdb

Switch the logfile, so that the Redo reach its standby databases

PRIMARY>alter system switch logfile;

System altered.

At standby Media recovery is not active. Verify v$pwfile_users for the granted role 

STANDBY>select username, sysdba, sysoper, sysasm,sysbackup,authentication_type AT from v$pwfile_users;

USERNAME SYSDB SYSOP SYSAS SYSBA AT
---------- ----- ----- ----- ----- --------
SYS TRUE TRUE FALSE FALSE PASSWORD
SYSDG FALSE FALSE FALSE FALSE PASSWORD
SYSBACKUP FALSE FALSE FALSE TRUE PASSWORD
SYSKM FALSE FALSE FALSE FALSE PASSWORD

Password file of Standby database:

-rw-r-----. 1 oracle oinstall 3584 /u01/app/oracle/product/12.2.0/dbhome_2/dbs/orapwstdbydb

Newly granted role is not reflected in Standby, lets start Media recovery and then query v$pwfile_users.

STANDBY>recover managed standby database disconnect;
Media recovery complete.


STANDBY>select username, sysdba, sysoper, sysasm,sysbackup,authentication_type AT from v$pwfile_users;

USERNAME SYSDB SYSOP SYSAS SYSBA AT
---------- ----- ----- ----- ----- --------
SYS TRUE TRUE FALSE FALSE PASSWORD
SYSDG FALSE FALSE FALSE FALSE PASSWORD
SYSBACKUP FALSE FALSE FALSE TRUE PASSWORD
SYSKM FALSE FALSE FALSE FALSE PASSWORD
USER1 FALSE TRUE FALSE FALSE PASSWORD

 

Password file of standby database:

-rw-r-----. 1 oracle oinstall 4096 /u01/app/oracle/product/12.2.0/dbhome_2/dbs/orapwstdbydb

 We could see after media recovery v$pwfile_users has been updated





来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26736162/viewspace-2215881/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
QQ:646634621| 网名:小麦苗| 微信公众号:xiaomaimiaolhr| 11g OCM| QQ群:618766405 微信群:私聊| 《数据库笔试面试宝典》作者| OCP、OCM、高可用(RAC+DG+OGG)网络班开讲啦,有需要的小伙伴可以私聊我。

注册时间:2012-09-23

  • 博文量
    1115
  • 访问量
    7019585