EXTRACT Stuck in RECOVERY since long time in GG HUB

 

OGG : 19c Classic Architecture 

Source DB : 19C Oracle

Goldengate and Database are running on two different Servers , Hub Server (where OGG is installed) is running in UTC time zone but the database server is running in US Central Time zone.

 

GGSCI (ec2-devhostas ggadmin@DEVDB) 41> start EDEVDB

 

Sending START request to MANAGER ...

EXTRACT EDEVDB starting

 

GSCI (ec2-devhost as ggadmin@DEVDB) 45> info all

 

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

 

MANAGER     RUNNING

EXTRACT     RUNNING     EDEVDB     01:18:52      00:00:05

EXTRACT     STOPPED     PDEVDB     00:00:00      01:18:56

 

GGSCI (ec2-devhost as ggadmin@DEVDB) 47> send EDEVDB status

 

Sending STATUS request to EXTRACT EDEVDB ...

EXTRACT EDEVDB (PID 4790)

  Current status: In recovery[1]: Processing data

 

  Current read position:

  Redo thread #: 1

  Sequence #: 0

  RBA: 0

  Timestamp: 2021-06-19 16:11:01.000000

  SCN: 0.0 (0)

  Current write position:

  Sequence #: 4

  RBA: 1449

  Timestamp: 2021-06-19 17:30:04.008729

  Extract Trail: /u02/goldengate/home/dirdat/SRC/DEVDB/tgtdb/sc

 

 

GGSCI (ec2-devhost as ggadmin@DEVDB) 57> view params EDEVDB

 

EXTRACT EDEVDB

SETENV (ORACLE_HOME='/u01/app/oracle/product/19.0.0.0/dbhome_1')

SETENV (ORACLE_SID='DEVDB')

USERIDALIAS ggadmin_DEVDB

TRANLOGOPTIONS INTEGRATEDPARAMS (max_sga_size 1024)

GETUPDATEBEFORES

NOCOMPRESSDELETES

NOCOMPRESSUPDATES

REPORTCOUNT EVERY 1 HOUR, RATE

WARNLONGTRANS 1D, CHECKINTERVAL 10M, SKIPEMPTYTRANS

EXTTRAIL /u02/goldengate/home/dirdat/SRC/DEVDB/tgtdb/sc

TABLE SCOTT.CB_GG GETBEFORECOLS(ON UPDATE ALL, ON DELETE ALL) ;

 

Solution:

 

Source DB Time :

 

12:01:36 ADMIN@DEVDB > select systimestamp from dual;

 SYSTIMESTAMP

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

19-JUN-21 12.30.36.030634 PM -05:00

 

GG HUB Server/DB version :

 

[oracle@ec2-devhost home]$ date

Sat Jun 19 17:41:26 UTC 2021

 

 Add below parameter on source param: (introduced in OGG 12.3.0.1.2)

 TRANLOGOPTIONS SOURCE_OS_TIMEZONE GMT-5

 

GGSCI (ec2-devhost as ggadmin@DEVDB) 49> edit params EDEVDB

GGSCI (ec2-devhost as ggadmin@DEVDB) 50> start EDEVDB

 Sending START request to MANAGER ...

EXTRACT EDEVDB starting

 

 

GGSCI (ec2-devhost as ggadmin@DEVDB) 51> send EDEVDB status

 Sending STATUS request to EXTRACT EDEVDB ...

EXTRACT EDEVDB (PID 5013)

  Current status: Recovery complete: At EOF

 

  Current read position:

  Redo thread #: 0

  Sequence #: 0

  RBA: 0

  Timestamp: 2021-06-19 17:34:05.000000

  SCN: 0.20900639 (20900639)

  Current write position:

  Sequence #: 5

  RBA: 1449

  Timestamp: 2021-06-19 17:34:00.090907

  Extract Trail: /u02/goldengate/home/dirdat/SRC/DEVDB/tgtdb/sc

 

GGSCI (ec2-devhost as ggadmin@DEVDB) 57> view params EDEVDB

 EXTRACT EDEVDB

SETENV (ORACLE_HOME='/u01/app/oracle/product/19.0.0.0/dbhome_1')

SETENV (ORACLE_SID='DEVDB')

USERIDALIAS ggadmin_DEVDB

TRANLOGOPTIONS SOURCE_OS_TIMEZONE GMT-5

TRANLOGOPTIONS INTEGRATEDPARAMS (max_sga_size 1024)

GETUPDATEBEFORES

NOCOMPRESSDELETES

NOCOMPRESSUPDATES

REPORTCOUNT EVERY 1 HOUR, RATE

WARNLONGTRANS 1D, CHECKINTERVAL 10M, SKIPEMPTYTRANS

EXTTRAIL /u02/goldengate/home/dirdat/SRC/DEVDB/tgtdb/sc

TABLE SCOTT.CB_GG GETBEFORECOLS(ON UPDATE ALL, ON DELETE ALL) ;

 

 Reference :

OGG Newly created extract is stuck in recovery mode (Doc ID 2434196.1)

 

 

Steps to Migrate OEM jobs to Another OEM

 

On SOURCE:

On Source OMS server login with sysman credentials

SRC_OMS_SERVER:NOTSET:/opt/oracle/product/middleware/oms/bin $ ./emcli login -username=sysman

Enter password :

Login successful

SRC_OMS_SERVER:NOTSET:/opt/oracle/product/middleware/oms/bin $ ./emcli get_reports | grep -i orcldb

"ORCLDB Failed Logons - Scheduled1 - 12c","SYSMAN","(ORCLDB Failed Logons - Scheduled1 - 12c)"

SRC_OMS_SERVER:NOTSET:/opt/oracle/product/middleware/oms/bin $ ./emcli export_report -output_file=/home/oracle/ORCLDB.xml -title="ORCLDB Failed Logons - Scheduled1 - 12c" -owner=SYSMAN

Report "ORCLDB Failed Logons - Scheduled1 - 12c", owned by "SYSMAN", has been exported from the repository.

Exported reports may not contain all information from original report.  Repository specific information such as targets, administrator access, and schedules are not exported.  Imported reports should be edited after import to supply all necessary report parameters.

Now, scp the xml file generated to the target OMS server

SRC_OMS_SERVER:NOTSET:/opt/oracle/product/middleware/oms/bin $ scp /home/oracle/ORCLDB.xml oracle@TRG_OMS_SERVER:/home/oracle

oracle@TRG_OMS_SERVER's password:

ORCLDB.xml                         

On TARGET:

[oracle@TRG_OMS_SERVER bin]$ ./emcli login -username=sysman

Enter password :

Login successful

[oracle@TRG_OMS_SERVER ~]$ cd $OMS_HOME/bin

[oracle@TRG_OMS_SERVER bin]$ ./emcli import_report -files=/home/oracle/ORCLDB.xml

File "/home/oracle/ORCLDB.xml" has been imported into the repository.

Imported reports should be edited after import to supply all necessary report parameters.

Verify:

Go to oem reports then you'll see this report

Refer: 

Doc ID 831165.1

Script to export OEM 13c Jobs

 

#!/bin/ksh

################################################

# This script will export all OEM 13c jobs

#

# AUTHOR : 

#################################################

echo -e "\n*************************************"

echo  "DATE = `date`"

echo  "HOSTNAME = " `hostname`

echo -e "*************************************\n"

#export WORD =''

export OMS_HOME=${OMS_HOME}

export LOG_DIR=

#$OMS_HOME/bin/emcli login -username=sysman

$OMS_HOME/bin/emcli get_reports | grep 13c > FILE

cat FILE | egrep -o '^[^,]+'|sed -e 's/^"//' -e 's/"$//'  | while read -r LINE

do

echo

echo -e "**********************************************************\n"

echo -e "\nOEM JOB NAME :" $LINE

#echo $LINE | awk -F" " '{print $1}' > MYWORD

WORD1="`echo $LINE | awk -F" " '{print $1}'`"

WORD2="`echo $LINE | tr ":" " " | awk -F" " '{print $2}'`"

WORD3="`echo $LINE | tr ":" " " | awk -F" " '{print $3}'`"

echo -e "\nExporting "$WORD1 $WORD2 $WORD3 " .......\n"

$OMS_HOME/bin/emcli export_report -output_file="$LOG_DIR/${WORD1}_${WORD2}_${WORD3}.xml" -title="$LINE" -owner=SYSMAN

#echo "$OMS_HOME/bin/${WORD1}_${WORD2}_${WORD3}.xml"

done

echo -e "\n**********************************************************"

echo -e "\nSHELL Script ENDED at `date`\n"

echo -e "EXPORTED Files are =>\n"

ls -lrth $LOG_DIR/*.xml

echo -e "\nZipping above 'xml' Files.........."

gzip -f $LOG_DIR/*.xml

echo -e "\nCompleted Zipping all Files "

ls -lrth $LOG_DIR/*.gz

Refer more :

how to migrade specific OEM job to another OEM can be found below

http://chandu208.blogspot.com/2021/02/steps-to-migrate-oem-jobs-to-another-oem.html

ORA-15234: target RDBMS compatibility (12.1.0.2.0) exceeds ASM compatibility (10.1.0.0.0)

New Disks are provisioned , which is greater than 2TB but unable to create disk group using those disks
 
devhost:NOTSET:/home/oracle $ oracleasm listdisks  | grep -i CBDEV
CBDEV_DATA1
CBDEV_RECO1
 
devhost:NOTSET:/home/oracle $ oracleasm querydisk CBDEV_DATA1
Disk "CBDEV_DATA1" is a valid ASM disk
 
devhost:NOTSET:/home/oracle $ oracleasm querydisk CBDEV_RECO1
Disk "CBDEV_RECO1" is a valid ASM disk
 
devhost:NOTSET:/home/oracle $ oracleasm querydisk /dev/asm/asm_CBDEV_DATA1
Device "/dev/asm/asm_cbdev_DATA1" is marked an ASM disk with the label "CBDEV_DATA1"
 
devhost:NOTSET:/home/oracle $  oracleasm querydisk /dev/asm/asm_CBDEV_RECO1
Device "/dev/asm/asm_cbdev_RECO1" is marked an ASM disk with the label "CBDEV_RECO1"
 
 
devhost:+ASM:/home/oracle $ sqlplus / as sysasm
 
SQL*Plus: Release 12.1.0.2.0 Production on Fri Mar 27 11:34:48 2020
 
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Automatic Storage Management option
 
SQL> col header for a22
SQL> set pages 999 lines 280
SQL> col path for a33
SQL>  select header_status "Header", mode_status  "Mode", path "Path", lpad(round(os_mb/1024),7)||'Gb' "Disk Size" from   v$asm_disk where path like ‘%CB%’;
 
Header                 Mode    Path                              Disk Size
---------------------- ------- --------------------------------- ---------
PROVISIONED            ONLINE  ORCL:CBDEV_DATA1                    4096Gb
PROVISIONED            ONLINE  ORCL:CBDEV_RECO1                    1024Gb
 
 
SQL> select GROUP_NUMBER,MOUNT_STATUS,STATE,REDUNDANCY,NAME,PATH from v$asm_disk where path like ‘%CB%’;
 
GROUP_NUMBER MOUNT_S STATE    REDUNDA NAME                           PATH
------------ ------- -------- ------- ------------------------------ ---------------------------------
           0 CLOSED  NORMAL   UNKNOWN                                ORCL:CBDEV_DATA1
           0 CLOSED  NORMAL   UNKNOWN                                ORCL:CBDEV_RECO1
 
SQL> select state, name, type from v$asm_diskgroup;
 
STATE       NAME                           TYPE
----------- ------------------------------ ------
MOUNTED     RECO1                        EXTERN
MOUNTED     DATA1                   EXTERN
 
 
SQL> create diskgroup CBDEVDATA1 external redundancy disk  'ORCL:CBDEV_DATA1' name CBDEVDATA1;
create diskgroup CBDEVDATA1 external redundancy disk  'ORCL:CBDEV_DATA1' name CBDEVDATA1
*
ERROR at line 1:
ORA-15018: diskgroup cannot be created
ORA-15099: disk 'ORCL:CBDEV_DATA1' is larger than maximum size of 2097152 MBs
 
 
SQL> select group_number,name,state,compatibility, database_compatibility from v$asm_diskgroup where name like ‘%CB%’ ;
 
GROUP_NUMBER NAME                STATE       COMPATIBILITY                                                DATABASE_COMPATIBILITY
------------ ------------------------------ ----------- ---------------------------------------------------    --------------------------------------------
          11 RECO1                        MOUNTED     10.1.0.0.0                                                   10.1.0.0.0
          13 DATA1                       MOUNTED     10.1.0.0.0                                                   10.1.0.0.0
          
 
 
SQL> col name for a28
SQL> col VALUE for a22
SQL> select name, value,GROUP_NUMBER from  v$asm_attribute where name like 'comp%';
 
 
NAME                         VALUE                  GROUP_NUMBER
---------------------------- ---------------------- ------------
compatible.asm               12.1.0.2.0                        3
compatible.rdbms             12.1.0.2.0                        3
compatible.asm               12.1.0.2.0                        8
compatible.rdbms             12.1.0.2.0                        8
compatible.asm               12.1.0.2.0                       14
compatible.rdbms             12.1.0.2.0                       14
compatible.asm               12.1.0.2.0                       18
compatible.rdbms             12.1.0.2.0                       18
 
8 rows selected.
 
 
SQL> create diskgroup CBRECO1 external redundancy disk  'ORCL:CBDEV_RECO1' name CBDEV_RECO1;
 
Diskgroup created.
 
SQL> select group_number,name,state,compatibility, database_compatibility from v$asm_diskgroup;
 
GROUP_NUMBER NAME                         STATE       COMPATIBILITY                                                DATABASE_COMPATIBILITY
------------ ---------------------------- ----------- ------------------------------------------------------------ -------------------------------------------------
          11 RECO1                      MOUNTED     10.1.0.0.0                                                   10.1.0.0.0
          13 DATA1                     MOUNTED     10.1.0.0.0                                                   10.1.0.0.0
          15 CBRECO1                     MOUNTED     10.1.0.0.0                                                   10.1.0.0.0
 
16 rows selected.
 
Note:
Starting with Oracle ASM version 12.2.0.1, the minimum and default settings for Oracle ASM disk group attributes are:
COMPATIBLE.ASM = 11.2.0.2 and COMPATIBLE.RDBMS = 10.1 for EXTERNAL, NORMAL, and HIGH redundancy disk groups
 
COMPATIBLE.ASM = 12.2.0.1 and COMPATIBLE.RDBMS = 12.2.0.1 for FLEX and EXTENDED disk groups
 
 
SQL> create diskgroup CBDEVDATA1 external redundancy disk  'ORCL:CBDEV_DATA1' name CBDEVDATA1 attribute  'compatible.rdbms'='12.1.0.2.0';
create diskgroup CBDEVDATA1 external redundancy disk  'ORCL:CBDEV_DATA1' name CBDEVDATA1 attribute  'compatible.rdbms'='12.1.0.2.0'
*
ERROR at line 1:
ORA-15018: diskgroup cannot be created
ORA-15234: target RDBMS compatibility (12.1.0.2.0) exceeds ASM compatibility (10.1.0.0.0)
 
SQL>  create diskgroup CBDEVDATA1 external redundancy disk  'ORCL:CBDEV_DATA1' name CBDEVDATA1 attribute  'compatible.asm'='12.1.0.2.0' ,  'compatible.rdbms'='12.1.0.2.0';
 
Diskgroup created.
 
SQL>  select group_number,name,state,compatibility, database_compatibility from v$asm_diskgroup where name like '%CB%';
 
GROUP_NUMBER NAME                         STATE       COMPATIBILITY                                                DATABASE_COMPATIBILITY
------------ ---------------------------- --------------------------------- ---------------------------------
          16 CBDEVDATA1                  MOUNTED     12.1.0.2.0                                                   12.1.0.2.0
          15 CBRECO1                     MOUNTED     10.1.0.0.0                                                   10.1.0.0.0
 
SQL> select GROUP_NUMBER,MOUNT_STATUS,STATE,REDUNDANCY,NAME,PATH from v$asm_disk where name like '%CB%';
 
GROUP_NUMBER MOUNT_S STATE    REDUNDA NAME                         PATH
------------ ------- -------- ------- ---------------------------- ---------------------------------
          16 CACHED  NORMAL   UNKNOWN CBDEVDATA1                  ORCL:CBDEV_DATA1
          15 CACHED  NORMAL   UNKNOWN CBDEV_RECO1                 ORCL:CBDEV_RECO1
 
SQL> select header_status "Header", mode_status  "Mode", path "Path", lpad(round(os_mb/1024),7)||'Gb' "Disk Size" from   v$asm_disk where path like '%CB%';
 
Header                 Mode    Path                              Disk Size
---------------------- ------- --------------------------------- ---------
MEMBER                 ONLINE  ORCL:CBDEV_DATA1                    4096Gb
MEMBER                 ONLINE  ORCL:CBDEV_RECO1                    1024Gb
 
So finally, I have successfully created DG with 4TB disk
 

ORA-29283: invalid file operation: path traverses a symlink [29433]

DB : Oracle 19.5

OS : RHEL 7

Expdp is failing due to the below error in my 19c database

Export: Release 19.0.0.0.0 - Production on Thu Apr 30 06:06:42 2020

Version 19.5.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

ORA-39002: invalid operation

ORA-39070: Unable to open the log file.

ORA-29283: invalid file operation: path traverses a symlink [29433]

Reason:

here my export directory is using symlink and as per oracle from 18c onwards No symbolic links for Data Pump directories

  

lrwxrwxrwx  1 oracle oinstall   10 Aug 24  2017 exp_symlk -> /export/gold

Solution :

Remove symlink for that directory and rerun export again

$ rm -f expimp

$ mkdir expimp

(OR)

If you don’t want to remove symlink then you have to restore back to old behavior

To restore the old behavior, the following underscore parameter must be set.

SQL> ALTER SYSTEM SET "_disable_directory_link_check" = TRUE SCOPE=SPFILE; (Recommend NOT to use)

NOTE :

To identify directory objects with symbolic links in the path name, run

      $ORACLE_HOME/rdbms/admin/utldirsymlink.sql AS SYSDBA

Refer :

DataPump Export (EXPDP) Fails Due to ORA-39155 ORA-48128 (Doc ID 2654167.1)

https://docs.oracle.com/en/database/oracle/oracle-database/19/sutil/oracle-data-pump-overview.html#GUID-06B2DF71-2A66-498F-B659-1EF5859B1648

ORA-20005: object statistics are locked (stattype = ALL)

DB : Oracle 19.5

OS : RHEL 7

When try to run stats on table the following error appears 

SQL>  execute dbms_stats.gather_table_stats(ownname => 'ADMIN', tabname =>'CUST_TBL', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZEAUTO');

BEGIN dbms_stats.gather_table_stats(ownname => 'ADMIN', tabname =>'CUST_TBL', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZEAUTO'); END;

*

ERROR at line 1:

ORA-20005: object statistics are locked (stattype = ALL)

ORA-06512: at "SYS.DBMS_STATS", line 40751

ORA-06512: at "SYS.DBMS_STATS", line 40035

ORA-06512: at "SYS.DBMS_STATS", line 9393

ORA-06512: at "SYS.DBMS_STATS", line 10317

ORA-06512: at "SYS.DBMS_STATS", line 39324

ORA-06512: at "SYS.DBMS_STATS", line 40183

ORA-06512: at "SYS.DBMS_STATS", line 40732

ORA-06512: at line 1

SQL> select table_name, stattype_locked,last_analyzed from dba_tab_statistics where owner='ADMIN' and table_name='CUST_TBL';

TABLE_NAME         STATT          LAST_ANAL

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

CUST_TBL                 ALL     06-APR-20

CUST_TBL                          06-APR-20

Use below command to unlock the table 

SQL>  exec dbms_stats.unlock_table_stats ('ADMIN','CUST_TBL');

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.31

SQL>  select table_name, stattype_locked from dba_tab_statistics where owner='ADMIN' and table_name='CUST_TBL';

TABLE_NAME             STATT

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

CUST_TBL

CUST_TBL

SQL>  execute dbms_stats.gather_table_stats(ownname => 'ADMIN', tabname =>'CUST_TBL', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.00

To find all table stats locked in a database:

SQL> select owner,count(*) from dba_tab_statistics where stattype_locked is not null group by owner;

OWNER                    COUNT(*)

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

SYS                            13

ADMIN                        331

SCOTT                         51

GSMADMIN_INTERNAL               1

GGOLD                        2

5 rows selected.

You can generate script to unlock all at once

select 'exec dbms_stats.unlock_table_stats  (''’ ||a.owner || ‘'',''' || a.table_name || ''')' ||’;’  from dba_tab_statistics a 

 where stattype_locked is not null and owner='<OWNER_NAME>';