WARNING OGG-01519 Oracle GoldenGate Delivery for Oracle, r0000031.prm

Issue/Problem,

2014-11-11 13:06:58 WARNING OGG-01519 Oracle GoldenGate Delivery for Oracle, r0000031.prm: Waiting at EOF on input trail file /s01/ggsdata/dirdat/ig000035, which is not marked as complete; but succeeding trail file /s01/ggsdata/dirdat/ig000036 exists. If ALTER ETROLLOVER has been performed on source extract, ALTER EXTSEQNO must be performed on each corresponding downstream reader. 

Reason for this problem

There has been a ETROLLOVER command issued on either Source Extract or Pump, due to which GG has moved to the new trail on the source Extract or Pump. While moving to the new trail file on the source environment, Golden Gate doesn’t make EOF comment or transaction mark on the old trail file. On the replicat site, GG will be waiting for the data or EOF message to come for the current trail from the source environment but another trail file started to write on the replicate site and replicat won’t be knowing it.

We have to manually move the trail file, else it will be staying on the current position.

Step involved in moving to the next trail

#1 Checking the status of current replicat

GGSCI (c959bhfengdnp) 11> info r0000031  

REPLICAT   R0000031  Last Started 2014-11-11 13:06   Status RUNNING

Checkpoint Lag       00:00:00 (updated 00:00:04 ago)

Log Read Checkpoint  File /s01/ggsdata/dirdat/ig000035

                     First Record  RBA 12629

When you check at the OS level, we can see 3 more trail files created and replicate won’t be knowing it and replicat will be staying on the sequence number 35.

GGSCI (c959bhfengdnp) 12> exit

ggs@bhuggs1:/s01/11.2: ls -lrt  /s01/ggsdata/dirdat/ig*

-rw-r----- 1 ggs ggs     3523 Nov  5 11:50 /s01/ggsdata/dirdat/ig000026

-rw-r----- 1 ggs ggs     3015 Nov  5 20:44 /s01/ggsdata/dirdat/ig000027

-rw-r----- 1 ggs ggs     1288 Nov  5 20:56 /s01/ggsdata/dirdat/ig000028

-rw-r----- 1 ggs ggs     1422 Nov  5 21:27 /s01/ggsdata/dirdat/ig000029

-rw-r----- 1 ggs ggs     1288 Nov  5 21:34 /s01/ggsdata/dirdat/ig000030

-rw-r----- 1 ggs ggs 99999978 Nov  5 23:01 /s01/ggsdata/dirdat/ig000031

-rw-r----- 1 ggs ggs 99999929 Nov  5 23:05 /s01/ggsdata/dirdat/ig000032

-rw-r----- 1 ggs ggs 99999843 Nov  5 23:09 /s01/ggsdata/dirdat/ig000033

-rw-r----- 1 ggs ggs 60431010 Nov  8 11:35 /s01/ggsdata/dirdat/ig000034

-rw-r----- 1 ggs ggs    12629 Nov 10 14:00 /s01/ggsdata/dirdat/ig000035

-rw-r----- 1 ggs ggs     1560 Nov 10 16:12 /s01/ggsdata/dirdat/ig000036

-rw-r----- 1 ggs ggs     3859 Nov 11 01:58 /s01/ggsdata/dirdat/ig000037

-rw-r----- 1 ggs ggs    17533 Nov 11 14:37 /s01/ggsdata/dirdat/ig000038

ggs@bhuggs1:/s01/11.2: ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.27 19591627 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_141006.1156_FBO

Linux, x64, 64bit (optimized), Oracle 11g on Oct  6 2014 16:45:49

Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.

GGSCI (c959bhfengdnp) 1> info r0000031 

REPLICAT   R0000031  Last Started 2014-11-11 13:06   Status RUNNING

Checkpoint Lag       00:00:00 (updated 00:00:13 ago)

Log Read Checkpoint  File /s01/ggsdata/dirdat/ig000035

                     First Record  RBA 12629

#2 To check whether GG has updated all the transaction in the current trail file;

To check whether any transaction left (or) it has reached the EOF; to check that we will be using send <REPLICAT NAME> logend, if the output is “YES” then it means it has reached the EOF if not then you have to use

'grouptransops 1 & maxtransops 1' in the parameter file of replicat and let it run, see that the replicat has applied all the records in the trail which is currently reading and check again the logend command and this should result in YES.  Once you get Yes then you need to remove the parameter 'grouptransops 1 & maxtransops 1' from the Replicat file(if you are not clear, please don’t use it).

GGSCI (c959bhfengdnp) 2> send r0000031 logend

Sending LOGEND request to REPLICAT R0000031 ...

YES.

#3 Stop the Replicat to change the Sequence number of the trail file.

GGSCI (c959bhfengdnp) 3> stop r0000031

Sending STOP request to REPLICAT R0000031 ...

Request processed.

#4 Change the Sequence number of the trail file and RBA

GGSCI (c959bhfengdnp) 4> alter REPLICAT R0000031, EXTSEQNO 36,  EXTRBA 0

REPLICAT altered.

#5 Just check the current state of the Replicat before starting it

GGSCI (c959bhfengdnp) 1> info r0000031 

REPLICAT   R0000031  Last Started 2014-11-11 13:06   Status RUNNING

Checkpoint Lag       00:00:00 (updated 00:00:13 ago)

Log Read Checkpoint  File /s01/ggsdata/dirdat/ig000036

                     First Record  RBA 0

#6 start the Replicat

GGSCI (c959bhfengdnp) 5> start REPLICAT R0000031

Sending START request to MANAGER ...

REPLICAT R0000031 starting

#7 checking the status of the replicat

GGSCI (c959bhfengdnp) 6> info REPLICAT R0000031

REPLICAT   R0000031  Last Started 2014-11-11 14:53   Status RUNNING

Checkpoint Lag       00:00:00 (updated 00:00:02 ago)

Log Read Checkpoint  File /s01/ggsdata/dirdat/ig000038

                     2014-11-11 14:52:42.769737  RBA 17852

It has reached end of the trail file and it is waiting for the information from the source.

Happy Reading :)

To Change Parallelism for a data pump job which is current running

To Change Parallelism for data pump job which is current running

To find the running jobs

SQL> select owner_name, job_name from dba_datapump_jobs;

OWNER_NAME                 JOB_NAME

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

SYSTEM                     GG1

$ expdp attach=SYSTEM.GG1

Export: Release 11.2.0.3.0 - Production on Tue Oct 7 10:47:53 2014

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

Username: system

Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

Job: GG1

  Owner: SYSTEM                        

  Operation: EXPORT                        

  Creator Privs: TRUE                          

  GUID: 04D26AD77698596AE0534F98340AEADE

  Start Time: Tuesday, 07 October, 2014 9:59:52

  Mode: TABLE                         

  Instance: orq738a2

  Max Parallelism: 1

  EXPORT Job Parameters:

  Parameter Name      Parameter Value:

     CLIENT_COMMAND        system/******** parfile=exp.par        

     INCLUDE_METADATA      0

  State: EXECUTING                     

  Bytes Processed: 0

  Current Parallelism: 1

  Job Error Count: 0

  Dump File: /s01/oradata1/export/GG_07102014.dmp

    bytes written: 4,096

 

Worker 1 Status:

  Process Name: DW00

  State: EXECUTING                     

  Object Schema: ORAGGS

  Object Name: GG_LEARNING

  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA

  Completed Objects: 1

  Total Objects: 101

  Completed Rows: 27,738

  Worker Parallelism: 1

To Change the Parallelism

Export> parallel=10

After changing the  Parallelism I could see that the value has changed.

To check the status after the existing table export is completed; So please wait for some more time.

 Export> status

Export> status

Job: GG1

  Operation: EXPORT                        

  Mode: TABLE                         

  State: EXECUTING                     

  Bytes Processed: 5,866,922,168

  Percent Done: 18

  Current Parallelism: 10

  Job Error Count: 0

  Dump File: /s01/oradata1/export/GG_07102014.dmp

    bytes written: 5,866,930,176

 

Worker 1 Status:

  Process Name: DW00

  State: EXECUTING                     

  Object Schema: ORAGGS

  Object Name: GG_STRUCTURES

  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA

  Completed Objects: 2

  Total Objects: 101

  Completed Rows: 18,758

  Worker Parallelism: 1

 

Worker 2 Status:

  Process Name: DW01

  State: EXECUTING                     

  Object Schema: ORAGGS

  Object Name: SYN_GG_SCHEMES

  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA

  Completed Objects: 1

  Total Objects: 101

  Worker Parallelism: 1

 

Worker 3 Status:

  Process Name: DW00

  State: EXECUTING                     

  Object Schema: ORAGGS

  Object Name: GG_AUTHORS

  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA

  Completed Objects: 1

  Total Objects: 101

  Worker Parallelism: 1

 

Worker 4 Status:

  Process Name: DW03

  State: EXECUTING                     

  Object Schema: ORAGGS

  Object Name: GG_ABSTRACTS

  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA

  Completed Objects: 1

  Total Objects: 101

  Worker Parallelism: 1

 

Worker 5 Status:

  Process Name: DW01

  State: EXECUTING                     

  Object Schema: ORAGGS

  Object Name: GG_DDL_NEWS_SUBSECTIONS

  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA

  Completed Objects: 1

  Total Objects: 101

  Worker Parallelism: 1

 

Worker 6 Status:

  Process Name: DW05

  State: EXECUTING                     

  Object Schema: ORAGGS

  Object Name: GG_TEXTS

  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA

  Completed Objects: 1

  Total Objects: 101

  Worker Parallelism: 1

 

Worker 7 Status:

  Process Name: DW02

  State: EXECUTING                     

  Object Schema: ORAGGS

  Object Name: GG_DDN_NEWS

  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA

  Completed Objects: 1

  Total Objects: 101

  Worker Parallelism: 1

 

Worker 8 Status:

  Process Name: DW07

  State: EXECUTING                     

  Object Schema: ORAGGS

  Object Name: GG_SEQUENCES

  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA

  Completed Objects: 1

  Total Objects: 101

  Worker Parallelism: 1

 

Worker 9 Status:

  Process Name: DW03

  State: EXECUTING                     

  Object Schema: ORAGGS

  Object Name: GG_REF_REFERENCES

  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA

  Completed Objects: 1

  Total Objects: 101

  Worker Parallelism: 1

 

Worker 10 Status:

  Process Name: DW09

  State: EXECUTING                     

  Object Schema: ORAGGS

  Object Name: GG_STRUCTURES

  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA

  Completed Objects: 1

  Total Objects: 101

  Worker Parallelism: 1

Export>

To See the status of running job

$ expdp attach=SYSTEM.GG1

Export: Release 11.2.0.3.0 - Production on Tue Oct 7 14:26:04 2014

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

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

Job: GG1

  Owner: SYSTEM                        

  Operation: EXPORT                         

  Creator Privs: TRUE                          

  GUID: 04D26AD77698596AE0534F98340AEADE

  Start Time: Tuesday, 07 October, 2014 9:59:52

  Mode: TABLE                         

  Instance: orq738a2

  Max Parallelism: 10

  EXPORT Job Parameters:

  Parameter Name      Parameter Value:

     CLIENT_COMMAND        system/******** parfile=exp.par        

     INCLUDE_METADATA      0

  State: EXECUTING                     

  Bytes Processed: 0

  Current Parallelism: 10

  Job Error Count: 0

  Dump File: /s01/oradata1/export/GG_07102014.dmp

    bytes written: 4,096

 

Worker 1 Status:

  Process Name: DW00

  State: EXECUTING                     

  Object Schema: ORAGGS

  Object Name: GG_LEARNING

  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA

  Completed Objects: 1

  Total Objects: 101

  Completed Rows: 328,461

  Worker Parallelism: 1

Export>

To See the Command prompt

select owner_name,JOB_NAME,OPERATION,JOB_MODE,STATE,DEGREE from dba_datapump_jobs;

SQL> desc dba_datapump_jobs

 Name                                      Null?    Type

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

 OWNER_NAME                                         VARCHAR2(30)

 JOB_NAME                                           VARCHAR2(30)

 OPERATION                                          VARCHAR2(90)

 JOB_MODE                                           VARCHAR2(90)

 STATE                                              VARCHAR2(30)

 DEGREE                                             NUMBER

 ATTACHED_SESSIONS                                  NUMBER

 DATAPUMP_SESSIONS                                  NUMBER

INST-5174 Oracle Internet Directory - Required Schemas already loaded

After a successful installation of Oracle Internet Directories (OID)(It is part of Oracle identity management). I attempted to remove it and re-install it with a different configuration.

I started to get a error message as INST-5174

I have removed the ODS related schemas in the database using cascade option.

SQL> DROP USER ODS CASCADE;

SQL> DROP USER ODSSM CASCADE;

That didn't not work and the error still appears.

Note:

·        The Database is 11g and it was brand new before installing OID so there is no other data doesn't show any other schemas related to OID.

Finding:

I have reviewed the installation log file and oracle is trying connect to the database and does some pre-check; in that oracle find the below entire in the sys user table

SQL> SELECT mrc_name FROM schema_version_registry where comp_id = 'OID';

MRC_NAME

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

DEFAULT_PREFIX

Table description

SQL> desc schema_version_registry

 Name                                      Null?    Type

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

 COMP_ID                                   NOT NULL VARCHAR2(30)

 COMP_NAME                                          VARCHAR2(255)

 MRC_NAME                                  NOT NULL VARCHAR2(30)

 MR_NAME                                            VARCHAR2(30)

 MR_TYPE                                            VARCHAR2(30)

 OWNER                                              VARCHAR2(30)

 VERSION                                            VARCHAR2(30)

 STATUS                                             VARCHAR2(11)

 UPGRADED                                           CHAR(1)

 START_TIME                                         TIMESTAMP(6)

 MODIFIED                                           TIMESTAMP(6)

I have found some other rows which are present in the table;

SQL> select * from schema_version_registry where COMP_ID='OID';

OID

Oracle Internet Directory

DEFAULT_PREFIX                 OID

IM                             ODS

11.1.1.7.0                     VALID       N

30-SEP-14 09.09.07.464367 AM

SQL> delete schema_version_registry where COMP_ID='OID';

1 row deleted.

SQL> commit;

Commit complete.

I have used the delete statement to delete the row; I HAVE DONE THIS IN THE DEV SYSTEM. IF YOU ARE DOING IT IN THE PRODUCTION SYSTEM, Check with oracle support.

Happy Learning

Removing a agent from oracle 12c OEM & Remove Monitoring target from OMS

Removing a agent from oracle 12c OEM & Remove Monitoring target from OMS

Stop the agent on the target server

#1 set the environment variables on the agent server

#2 stop the agent

oracle@bhudb01> emctl stop agent

Oracle Enterprise Manager Cloud Control 12c Release 4 

Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.

Stopping agent ..... stopped.

#3 Login to the oms server and login to system

oracle@ bhuoms:~> emcli login -username=sysman

Enter password :

Login successful

oracle@ bhuoms:~>

oracle@bhuoms:~> emcli delete_target -name="bhudb01.isihost.com:3872" -type="oracle_emd" -delete_monitored_targets

Target "bhudb01.isihost.com:3872:oracle_emd" deleted successfully

#4 you can De-install using Graphical(OUI) & silent one.

I am going to use GUI method

Connect to the Agent server

Make sure the agent is down.

$ cd $AGENT_HOME/12.1.0.4.0/oui/bin

$ ./runInstaller

You will get the OUI, click on the deinstall products

Step#1  chose the sbin & other plugin homes.

Step#2  remove the agent

Step#3 exit the OUI and clean the $AGENT_HOME location.

If you want you can go ahead with the different agent installation; my case I am hitting a bug where I am asked to installed 32bit agent on the 64bit server.

To Modify the Database setting in the OMS12c

To Modify the Database setting in the OMS12c

You can modify service name, sid , port number, include load balancer & failover environment

Step#1

Set the environmental variables

RAC Environment without standby DB: I am using Hostname

$OMS_HOME/bin/emctl config oms -store_repos_details -repos_conndesc '(DESCRIPTION=(FAILOVER=ON)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=oemhost1)(PORT=1521))

(ADDRESS=(PROTOCOL=TCP)(HOST=oemhost2)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=emrepsrvc))(FAILOVER_MODE=(TYPE=select)(METHOD=basic)))' -repos_user sysman

RAC Environment without standby DB: I am using scan name

emctl config oms -store_repos_details -repos_conndesc '(DESCRIPTION=(ADDRESS_LIST=(FAILOVER=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=cluster-scan)(PORT=1526)))(LOAD_BALANCE=ON)(CONNECT_DATA=(SERVICE_NAME=OEM12C.company.com)))' -repos_user sysman

Single node environment without standby DB

emctl config oms -store_repos_details -repos_conndesc '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST= oemhost1)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=emgc.nonprod)))' -repos_user sysman

Step#2 Stop the entire OMS

$ emctl stop oms –all

Step#3 Start the entire OMS

$ emctl start oms

To verify the repository Details through emctl command

$ emctl config oms -list_repos_details

Oracle Enterprise Manager Cloud Control 12c Release 4 

Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.

Repository Connect Descriptor : (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=Bhuvan-em12c)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=emgc.nonprod)))

Repository User : sysman

Identify Port Owner & Pid

To check whether port is used on the server and who is using it currently

I am checking for the port 3872

$ netstat -an |grep 3872
tcp        0      0 :::3872                     :::*                        LISTEN  

To check who is using this port on a Linux Server

$ /usr/sbin/lsof -i :3872
COMMAND  PID   USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
java    5426 oracle   65u  IPv6  25327      0t0  TCP *:oem-agent (LISTEN)


Currently this port is used by the Oracle user for the OEM-Agent.


I got this error message while trying to deploy the Oracle agent12c from the OEM12c

Failed SEVERE: Agent free port check failed