my-orcl@Areef DBA, my-orcl@Arif DBA

Tuesday, April 4, 2017

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

Hai, this article I will show how to fix error ORA-16191 for datagaurd setup.

The following scenario performed on EBS database

EBS Version: R12.1.3

Database Version: 11.1.0.7
List of Topics (Linux, Database, RAC, EBS)

click on the link for RMAN Datagaurd Configuration Setup

PRIMARY DB (192.168.1.11)

Primary alert log

==============================================================================================================

Tue Apr 04 13:37:55 2017

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

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

Errors in file /u01/db/tech_st/11.1.0/admin/DELL_rac1/diag/rdbms/dell_live/DELL/trace/DELL_arcp_16727.trc:

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

PING[ARCp]: Heartbeat failed to connect to standby 'DELL_STBY'. Error is 16191.

Tue Apr 04 13:42:56 2017

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

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

Errors in file /u01/db/tech_st/11.1.0/admin/DELL_rac1/diag/rdbms/dell_live/DELL/trace/DELL_arcp_16727.trc:

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

PING[ARCp]: Heartbeat failed to connect to standby 'DELL_STBY'. Error is 16191.

Tue Apr 04 13:47:28 2017

Stopping background process CJQ0

Tue Apr 04 13:47:56 2017

Error 1034 received logging on to the standby

Errors in file /u01/db/tech_st/11.1.0/admin/DELL_rac1/diag/rdbms/dell_live/DELL/trace/DELL_arcp_16727.trc:

ORA-01034: ORACLE not available

PING[ARCp]: Heartbeat failed to connect to standby 'DELL_STBY'. Error is 1034.

==============================================================================================================

SQL> SELECT DESTINATION, STATUS, ERROR FROM V$ARCHIVE_DEST WHERE DEST_ID=2;

DESTINATION

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

STATUS ERROR

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

DELL_STBY

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

SQL>

STANDBY DB (192.168.1.12)

SQL> select CONTROLFILE_TYPE from v$database;

CONTROL

-------

STANDBY

SQL> alter database recover managed standby database disconnect;

Database altered.

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/db/apps_st/arch

Oldest online log sequence 0

Next log sequence to archive 10

Current log sequence 10

SQL>

Switched Log on Primary database

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/db/apps_st/arch

Oldest online log sequence 0

Next log sequence to archive 0

Current log sequence 0

SQL>

Switched Log on Primary database

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/db/apps_st/arch

Oldest online log sequence 0

Next log sequence to archive 0

Current log sequence 0

SQL>

Archive are not Shipping because of error “ORA-16191”

SQL> alter database recover managed standby database cancel;

SQL> shut immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oraerp@rac2 dbs]$

SOLUTION:

Recreate password file with following parameters on “PRIMARY” & “STANDBY”

[oraerp@rac2 dbs]$ rm -rf orapwDELL

[oraerp@rac2 dbs]$ orapwd file=orapwDELL password=sys entries=100 ignorecase=Y

==============================================================================================================

Creation of Password file for PRIMARY DATABASE

[oraerp@rac1 dbs]$ rm -rf orapwDELL

[oraerp@rac1 dbs]$ orapwd file=orapwDELL password=sys entries=100 ignorecase=Y

==============================================================================================================

[oraerp@rac2 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Tue Apr 4 13:49:08 2017

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area 730714112 bytes

Fixed Size 2163280 bytes

Variable Size 419433904 bytes

Database Buffers 306184192 bytes

Redo Buffers 2932736 bytes

Database mounted.

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/db/apps_st/arch

Oldest online log sequence 0

Next log sequence to archive 0

Current log sequence 14

SQL> alter database recover managed standby database disconnect;

Database altered.

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/db/apps_st/arch

Oldest online log sequence 0

Next log sequence to archive 0

Current log sequence 14

SQL>

PRIMARY DB (192.168.1.11)

SQL> SELECT DESTINATION, STATUS, ERROR FROM V$ARCHIVE_DEST WHERE DEST_ID=2;

DESTINATION

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

STATUS ERROR

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

DELL_STBY

VALID

SQL> alter system switch logfile;

System altered.

STANDBY DB (192.168.1.12)

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/db/apps_st/arch

Oldest online log sequence 14

Next log sequence to archive 0

Current log sequence 15

SQL>

Switched Log on Primary database

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/db/apps_st/arch

Oldest online log sequence 15

Next log sequence to archive 0

Current log sequence 16

SQL>

SQL> select LOG_MODE,FLASHBACK_ON,FORCE_LOGGING from v$database;

LOG_MODE FLASHBACK_ON FOR

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

ARCHIVELOG NO YES

SQL>

SQL> alter database flashback on;

Database altered.

SQL> select LOG_MODE,FLASHBACK_ON,FORCE_LOGGING from v$database;

LOG_MODE FLASHBACK_ON FOR

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

ARCHIVELOG YES YES

SQL>

CONCLUSION:

ORA-16191 ISSUE RESOLVED

Thanks for Reading.

Regards,

Mohammed Areefuddin.

Suggested Topics :

Linux	DATABASE	RMAN	RAC	EBS
5.7 Install	DATABASE Install		11201 Install	R1226 Install
Open Filer Instl	Active DataGaurd	Rman DataGuard	RAC DataGuard	EBS DataGuard
		RMAN DB CLONE	RAC DB Clone	R1226 Clone
	ASMCMD ASM Migrt	RMAN ASM Migrt		RMAN Clone
	11204 M-Upgrade			11202 EBS Upgrade
	12102 M-Upgrade			12102 EBS UPGRADE


Linux Basics	DATABASE Basics	RMAN Basics	RAC Basic	EBS Basics

Sunday, April 2, 2017

ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt

Hai, this article is for clearing the error ORA-20001 when you run Gather Schema Statistics for ALL users

EBS Version: R12.1.3

Database Version: 11.1.0.7.0

SYMPTOMS

Gather Schema Statistics" program reported following errors in request log files:

ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt

Statistics Errors:

stats on table FND_CP_GSM_OPP_AQTBL is locked

Error #1: ERROR: While GATHER_TABLE_STATS:

object_name=GL.JE_BE_LINE_TYPE_MAP******

Error #2: ERROR: While GATHER_TABLE_STATS:

object_name=GL.JE_BE_LOGS***ORA-20001: invalid column name or duplicate columns/column

groups/expressions in method_opt***

Error #1: ERROR: While GATHER_TABLE_STATS:

object_name=GL.JE_BE_LINE_TYPE_MAP***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***

Error #2: ERROR: While GATHER_TABLE_STATS:

object_name=GL.JE_BE_LOGS***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***

Error #3: ERROR: While GATHER_TABLE_STATS:

object_name=GL.JE_BE_VAT_REP_RULES***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***

Error #4: ERROR: While GATHER_TABLE_STATS: object_name=FII.FII_FIN_ITEM_HIERARCHIES***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***

groups/expressions in method_opt***

Investigating the issue I find that the historgram contains duplicate records which it should has

only one record. This is a known issue after upgrade and should be handled as per below:

The query result below show that we are impacted by this issue:

CAUSE:

1 ) There are duplicate rows on FND_HISTOGRAM_COLS table for JE_BE_LINE_TYPE_MAP table.

Because of this problem, FND_STATS tries to gather histogram information using wrong command and

it fails with ora-20001 errors.

The following SQL should return one row, not two:

SQL> show user

USER is "APPS"

SQL>

SQL>select column_name, nvl(hsize,254) hsize

from FND_HISTOGRAM_COLS

where table_name = 'JE_BE_LINE_TYPE_MAP'

order by column_name;

COLUMN_NAME HSIZE

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

SOURCE 254

SQL>

Since there are two rows in histograms table, FND_STATS creates following command to gather statistics on table 'JE_BE_LINE_TYPE_MAP' :

SOLUTION:

Find out all duplicates and/or obsolete rows in FND_HISTOGRAM_COLS and delete one of them logged in as the applsys user.

Remember to take backup of the FND_HISTOGRAM_COLS table before deleting any data.

SQL> select table_name, column_name, count(*)

from FND_HISTOGRAM_COLS

group by table_name, column_name

having count(*) > 1;

TABLE_NAME COLUMN_NAME COUNT(*)

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

JE_BE_LOGS DECLARATION_TYPE_CODE 2

JE_FR_DAS_010 TYPE_ENREG 2

JE_FR_DAS_010_NEW TYPE_ENREG 2

JE_BE_LINE_TYPE_MAP SOURCE 2

JE_BE_VAT_REP_RULES SOURCE 2

JE_BE_VAT_REP_RULES LINE_TYPE 2

JE_BE_VAT_REP_RULES VAT_REPORT_BOX 2

JG_ZZ_SYS_FORMATS_ALL_B JGZZ_EFT_TYPE 2

-- Use above results on the following SQL to delete duplicates

SQL> delete from FND_HISTOGRAM_COLS

where table_name = '&TABLE_NAME'

and column_name = '&COLUMN_NAME'

and rownum=1;

Enter value for table_name: JE_BE_LOGS

old 2: where table_name = '&TABLE_NAME'

new 2: where table_name = 'JE_BE_LOGS'

Enter value for column_name: DECLARATION_TYPE_CODE

old 3: and column_name = '&COLUMN_NAME'

new 3: and column_name = 'DECLARATION_TYPE_CODE'

1 row deleted.

SQL> /

Enter value for table_name: JE_FR_DAS_010

old 2: where table_name = '&TABLE_NAME'

new 2: where table_name = 'JE_FR_DAS_010'

Enter value for column_name: TYPE_ENREG

old 3: and column_name = '&COLUMN_NAME'

new 3: and column_name = 'TYPE_ENREG'

1 row deleted.

SQL> /

Enter value for table_name: JE_FR_DAS_010_NEW

old 2: where table_name = '&TABLE_NAME'

new 2: where table_name = 'JE_FR_DAS_010_NEW'

Enter value for column_name: TYPE_ENREG

old 3: and column_name = '&COLUMN_NAME'

new 3: and column_name = 'TYPE_ENREG'

1 row deleted.

SQL> /

Repeat same till you delete all the duplicate rows and then commit.

SQL> select table_name, column_name, count(*)

from FND_HISTOGRAM_COLS

group by table_name, column_name

having count(*) > 1;

no rows selected

SQL>

SQL> select column_name, nvl(hsize,254) hsize

from FND_HISTOGRAM_COLS

where table_name = 'JE_BE_LINE_TYPE_MAP'

order by column_name;

COLUMN_NAME HSIZE

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

SOURCE 254

SQL>

SQL> commit;

Commit complete.

SQL>

ORA-20001 ISSUE RESOLVED

This article helps those who facing issue in gather schema stats Request.

Thanks for Reading.

Regards,

Mohammed Areefuddin.

Suggested Topics :

Linux	DATABASE	RMAN	RAC	EBS
5.7 Install	DATABASE Install		11201 Install	R1226 Install
Open Filer Instl	Active DataGaurd	Rman DataGuard	RAC DataGuard	EBS DataGuard
		RMAN DB CLONE	RAC DB Clone	R1226 Clone
	ASMCMD ASM Migrt	RMAN ASM Migrt		RMAN Clone
	11204 M-Upgrade			11202 EBS Upgrade
	12102 M-Upgrade			12102 EBS UPGRADE


Linux Basics	DATABASE Basics	RMAN Basics	RAC Basic	EBS Basics