寒風凌厲大雪節氣的週末凍得出不了門，坐在家中喝熱茶，突然接到朋友從公司打來的電話：為Oracle 12C RAC主庫安裝好的單機standby DB日誌無法和主庫同步報錯ORA-16191

DB版本：Oracle 12.1.0.2 RAC+ single DB (PS:這版本其實不穩定啦)

檢查alert.log：

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

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

FAL[client, USER]: Error 16191 connecting to CMMDB for fetching gap sequence

Sun Dec 09 11:42:34 2018

Errors in file /u01/app/oracle/diag/rdbms/cmmdbs/CMMDB/trace/CMMDB_pr00_18146.trc:

ORA-16191: Primary log shipping client not logged on standby

Sun Dec 09 11:42:44 2018

FAL[client]: Failed to request gap sequence

GAP - thread 2 sequence 788218-788218

DBID 3690862883 branch 928463524

FAL[client]: All defined FAL servers have been attempted.

分析：

此問題通常和remote_login_passwordfile引數，密碼檔案，sys賬號是否有lock等相關

診斷：

1. 檢查remote_login_passwordfile主備庫中均設定為EXCLUSIVE，說明與此無關

2. 檢查sys賬號沒有被lock，說明與此無關

3. 檢查密碼檔案，朋友說：standby中是使用orapwd命令建立。這裡問題就來了，12C聯機文件及文件 ID 1984091.1 中明確指出密碼檔案需求從主庫RAC中pwcopy到備庫。注，12.1以後版本中密碼檔案是所有節點共享的存放在ASM中

If the SSL authentication requirements are not met, then each database must use a remote login 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, and that copy must be 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.

If you have stored the password file in an Oracle ASM disk group at the standby database, then you must copy the updated password file from the primary database to the Oracle ASM location at the standby database. See for information about the ASMCMD pwcopy command used to copy an Oracle ASM or database instance password file to a specified location. See for information about using the srvctl utility to modify a database configuration.

處理：

登入RAC主庫重新pwcopy至備庫

1>檢視密碼檔案的地方：

ASMCMD> pwget --dbuniquename CMMDB

+DATA/CMMDB/PASSWORD pwdcmmdb.276.928463387

2>從ASM中將密碼檔案複製到本地OS

ASMCMD> pwcopy --dbuniquename CMMDB +DATA/CMMDB/PASSWORD/pwdcmmdb.276.928463387 /tmp/orapcmmdb

copying +DATA/CMMDB/PASSWORD/pwdcmmdb.276.928463387 -> /tmp/orapcmmdb

ASMCMD-9456: password file should be located on an ASM disk group

3> 從主庫 本地OS scp到備庫

重啟備庫後，主備庫同步redo正常。

至此，問題解決，好了繼續喝茶。。

附：文件 ID 1984091.1

12c: Data Guard Physical Standby - Managing password files in a RAC Physical Standby (文件 ID 1984091.1)

In this Document

GoalSolutionReferences

APPLIES TO:

Oracle Database - Enterprise Edition - Version 12.1.0.1 to 12.1.0.2 [Release 12.1]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.

GOAL

During the creation of a standby database or after having altered the password for any user granted the SYSDBA,SYSOPER or SYSDG privileges the password file must be copied from the primary site to the standby site.

The Oracle Database Enterprise Edition 12.1.0.1 and above can now store password files in ASM diskgroups. By default if dbca is used to create a RAC database, the password file is created in an ASM diskgroup on the Primary site.

The documentation states that the password file must be copied from the Primary to the Standby sites:

This HOW TO details the procedure and commands for copying the password file from the Primary site to the standby site when these password files are held in an ASM diskgroup.

IMPORTANT: The ASM diskgroup used to store the password files on the standby site MUST have it's COMPATIBLE.ASM attribute set to 12.1.

SOLUTION

The procedure for copying the file across from the Primary site to the standby site is as the following:

1. On the Primary site as the grid user, copy the password file out of ASM on to a file system based location, in this case /tmp

Primary Site:

[oracle@grid2vm1 dbs]$ su - grid
Password:

[grid@grid2vm1 ~]$ . oraenv
ORACLE_SID = [grid] ? +ASM1
The Oracle base has been set to /u01/app/grid

[grid@grid2vm1 ~]$ asmcmd

ASMCMD> pwcopy +DATA/DB121/PASSWORD/pwddb121.256.867607273 /tmp/orapwdb121
copying +DATA/DB121/PASSWORD/pwddb121.256.867607273 -> /tmp/orapwdb121
ASMCMD> exit

2. Copy the password file to one of the Standby RAC nodes

Primary Site:

[oracle@grid2vm1 dbs]$ scp /tmp/orapwdb121 grid1vm1:/tmp/orapwdb1211
..
.
Are you sure you want to continue connecting (yes/no)? yes

oracle@grid1vm2's password:
orapwdb121 100% 7680 7.5KB/s 00:00

3. On the standby node that now has a copy of the password file, copy the password file into ASM as grid user. The ASM command pwcopy can be used to perform this task. Make sure the file is placed in the diskgroup and sub-directory for the standby identified through its db_unique_name value. In this case +DATA and DB121STB.

Standby Site:

$ su - grid
Password:

[grid@grid1vm1 ~]$ . oraenv
ORACLE_SID = [grid] ? +ASM1
The Oracle base has been set to /u01/app/grid

[grid@grid1vm1 ~]$ asmcmd

ASMCMD> pwcopy /tmp/orapwdb1211 +DATA/DB121STB/orapwdb1211
copying /tmp/orapwdb1211 -> +DATA/DB121STB/orapwdb1211

ASMCMD> ls -l +DATA/DB121STB/orapwdb1211
Type Redund Striped Time Sys Name
PASSWORD UNPROT COARSE FEB 26 11:00:00 N orapwdb1211 => +DATA/ASM/PASSWORD/pwdasm.279.872680185
ASMCMD> exit

4. As the owner of the RDBMS software for the database, update the clusterware resource for the database and set the location of the password file to be used by the database using srvctl modify database command.

Standby Site:

$ su - oracle
Password:

[oracle@grid1vm1 ~]$ . oraenv
ORACLE_SID = [oracle] ? db1211
The Oracle base has been set to /u01/app/oracle

[oracle@grid1vm1 ~]$ srvctl modify database -d db121stb -pwfile +DATA/DB121STB/orapwdb1211

[oracle@grid1vm1 ~]$ srvctl config database -d db121stb

Database unique name: db121stb
Database name:
Oracle home: /u01/app/oracle/product/12.1.0/dbhome_1
Oracle user: oracle
Spfile: /u01/app/oracle/product/12.1.0/dbhome_1/dbs/spfiledb1211.ora
Password file: +DATA/DB121STB/orapwdb1211
Domain: au.oracle.com
Start options: mount
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATA,FRA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group:
Database instances: db1211,db1212
Configured nodes: grid1vm1,grid1vm2
Database is administrator managed

Note: if the --dbuniquename switch in pwcopy is used, the following errors will appear after the file has been copied.

ASMCMD> pwcopy --dbuniquename db121stb /tmp/orapwdb1211 +DATA/DB121STB/orapwdb1211
copying /tmp/orapwdb1211 -> +DATA/DB121STB/orapwdb1211
PRCD-1163 : Failed to modify database db121stb
PRCR-1071 : Failed to register or update resource ora.db121stb.db
CRS-0245: User doesn't have enough privilege to perform the operation
ASMCMD-9453: failed to register password file as a CRS resource

These errors are expected due to user role separation. Please use steps above instead.

Oracle 12C RAC的單機Standby returning error ORA-16191

APPLIES TO:

GOAL

SOLUTION

相關文章