ORA-01194 after Control file Restore in Oracle and willing to do complete recovery

I got this complaint from one of the customer that they were not able to recover the database post controlfile restore, so lets have a look what happened. 

My oracle version is this : 

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

Firstly the controlfile is lost , so when i restart my database this happens: 

So lets restart our database with startup force (shutdown abort & startup) 

SQL> startup force

ORACLE instance started.

Total System Global Area 3321886736 bytes

Fixed Size                  9140240 bytes

Variable Size             704643072 bytes

Database Buffers         2600468480 bytes

Redo Buffers                7634944 bytes

ORA-00205: error in identifying control file, check alert log for more info

* So i have to restore controlfile now

[oracle@rhel7 PROD]$ rman target/

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Dec 9 00:09:27 2020

Version 19.3.0.0.0

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

connected to target database: PROD (not mounted)

RMAN> restore controlfile from '/u01/app/oracle/fast_recovery_area/PROD/autobackup/2020_12_08/o1_mf_s_1058658290_hwzjnw2h_.bkp';

Starting restore at 09-DEC-20

using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/u01/app/oracle/oradata/PROD/control01.ctl

output file name=/u01/app/oracle/fast_recovery_area/PROD/control02.ctl

Finished restore at 09-DEC-20

SQL> alter database mount;

Database altered.

SQL> alter database  open ;

alter database  open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database  open resetlogs;

alter database  open resetlogs

*

ERROR at line 1:

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/u01/app/oracle/oradata/PROD/system01.dbf'

* Thats it , this is the error : 

SQL> recover database;

ORA-00283: recovery session canceled due to errors

ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

SQL> recover database using backup controlfile;

ORA-00279: change 2290523 generated at 12/08/2020 23:41:34 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/fast_recovery_area/PROD/archivelog/2020_12_09/o1_mf_1_9_%u_.arc

ORA-00280: change 2290523 for thread 1 is in sequence #9

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

auto

ORA-00308: cannot open archived log

'/u01/app/oracle/fast_recovery_area/PROD/archivelog/2020_12_09/o1_mf_1_9_%u_.arc

'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 7

ORA-00308: cannot open archived log

'/u01/app/oracle/fast_recovery_area/PROD/archivelog/2020_12_09/o1_mf_1_9_%u_.arc

'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 7

* So the problem here is we are not able to recover the database because we dont have enough redo changes to be applied to do complete recovery.

When we do recovery from RMAN , its intelligent enough to understand it and applies the redo logfile redo03.log  that needs to applied.

RMAN> recover database;

Starting recover at 09-DEC-20

released channel: ORA_DISK_1

Starting implicit crosscheck backup at 09-DEC-20

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=39 device type=DISK

Crosschecked 3 objects

Finished implicit crosscheck backup at 09-DEC-20

Starting implicit crosscheck copy at 09-DEC-20

using channel ORA_DISK_1

Finished implicit crosscheck copy at 09-DEC-20

searching for all files in the recovery area

cataloging files...

cataloging done

List of Cataloged Files

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

File Name: /u01/app/oracle/fast_recovery_area/PROD/autobackup/2020_12_08/o1_mf_s_1058658290_hwzjnw2h_.bkp

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 9 is already on disk as file /u01/app/oracle/oradata/PROD/redo03.log

archived log file name=/u01/app/oracle/oradata/PROD/redo03.log thread=1 sequence=9

media recovery complete, elapsed time: 00:00:00

Finished recover at 09-DEC-20

** Remember ** Although RMAN works for us, but in case you wanted to use sqlplus itself , so you should have done this assuming redo01.log was required if it was sequence 1 which was missing :

SQL>  recover database using backup controlfile;

ORA-00279: change 2294948 generated at 12/09/2020 00:18:15 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/fast_recovery_area/PROD/archivelog/2020_12_09/o1_mf_1_1_%u_.arc

ORA-00280: change 2294948 for thread 1 is in sequence #1

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

'/u01/app/oracle/oradata/PROD/redo01.log'

Log applied.

Media recovery complete.

** thats it, we are able to open the database now. Happy days

SQL> alter database open resetlogs;

Database altered.

Loading plans in the baselines with a Linux Shell script.

Its a monday morning and Smith the junior dba is muddled since some developers are chasing him to load a good current plan in the baseline to avoid plan regression, Smith being relatively a new and a junior dba is not quite familiar on how he should use plsql and is complacent to do so, since he thinks the plsql code might load everything from shared pool to the baselines and might cause some issues  if any, So what Smith does is, he calls Martin the senior dba and asks him if he can help him, Martin replies he already has a shell script which does that, all Smith would need to do is pass the sql id in the shell script argument as a $1and that would do the job. Smith couldnt be more happier and that makes his day.

The shell script is given below in this article , so what it does is, $1 as we are aware is the first argument when any shell script is invoked and $2 and $3 .. and so on could be used for as many arguments as much as you want but here we just need 1 argument to the job. So here $1 we use to pass the sql id in the plsql block of the shell script, please note we use $1 not the oracle plsql &a for that matter. Make sure you give executable permission to this script to make it work with chmod u+x spm.sh

[oracle@node2 dk]$ cat spm.sh

sqlplus / as sysdba << EOF

set echo off

set feedback off

set pages 0

set lin 100

set sqlnumber off

set serveroutput on

declare

a pls_integer;

begin

a :=  DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '$1');

dbms_output.put_line(a|| ' plans loaded in the baseline');

end;

/

exit;

EOF

Lets run the code 

[oracle@node2 dk]$ ./spm.sh 1uw84jcq6802a

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> 

1 plans loaded in the baseline

SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

# So there you go , 1 plans loaded in the baseline is successfully returned as per our dbms_output.

Could we validate our plsql block to make sure it works as expected without the shell cover. Lets see : 

SQL> declare

a pls_integer;

begin

a :=  DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '&a');

dbms_output.put_line(a|| ' plans loaded in the baseline');

end;

/

  2    3    4    5    6    7  Enter value for a:

old   4: a :=  DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '&a');

new   4: a :=  DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '');

0 plans loaded in the baseline

PL/SQL procedure successfully completed.

So here above we have zero plans loaded because we passed null value

SQL> /

Enter value for a: 1uw84jcq6802a

old   4: a :=  DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '&a');

new   4: a :=  DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '1uw84jcq6802a');

1 plans loaded in the baseline

PL/SQL procedure successfully completed.

So here above we give a valid sql id and it makes sure it is loaded in the baseline.

SQL>  select count(*) from dba_sql_plan_baselines;

  COUNT(*)

----------

         1

So yes it does work. Happy shell scripting

Managing the network with Network manager in Redhat Enterprise Linux 7

With the introduction of RHEL7 there is a new profile based network management. Like many other Daemons Network Manager in RHEL 7 is also a Daemon, it monitors and manages the network settings. Network manager can be managed using nmcli and other graphical tools.  In Red Hat Enterprise Linux 7 please note this is the default networking service and is provided by Network Manager,  basically this service is a dynamic network service control and there is a configuration daemon to keep network devices and connections up and active when they are available. An interesting thing to note is that the traditional ifcfg type configuration files are still supported which i will cover in this post.

The main advantage of this utility is that managing the networking is way much easier: Network Manager ensures that network connectivity works. When it detects that there is no network configuration in a system but there are network devices, Network  Manager creates temporary connections to provide connectivity.

These tools will automatically update /etc/sysconfig/network-scripts  in the back-end. So as we are aware commands and graphical tools are updating the network configuration files, human errors are eliminated. 

Lets see how Network Manager works : 

# First i will start my current network interface, The ifup command basically brings the network interface up, allowing it to transmit and receive data. Technically ifup command is used to configure network interfaces based on interface definitions in the file /etc/network/interfaces.

[root@node2 Desktop]# cd /etc/sysconfig/network-scripts/

[root@node2 network-scripts]# ifup ifcfg-eno16777736 

Connection successfully activated (D-Bus active path: /org/freedesktop/NetworkManager/ActiveConnection/0)

# Lets get into network manager now, Current devices

[root@node2 ~]# nmcli con show

NAME         UUID                                  TYPE            DEVICE

eno16777736  bcc252b1-98d3-4fb6-a005-2caad8422622  802-3-ethernet  eno16777736

# Now lets make a new connection named as "RHEL_NEW" which will automatically connect as an Ethernet connection eth0 device using DHCP.

[root@node2 ~]# nmcli con add  con-name "RHEL_NEW" type ethernet ifname eth0

Connection 'RHEL_NEW' (5f87ee25-7007-4a35-b343-8e1351443f54) successfully added.

# Lets confirm it 

[root@node2 ~]# nmcli con show

NAME         UUID                                  TYPE            DEVICE

RHEL_NEW     5f87ee25-7007-4a35-b343-8e1351443f54  802-3-ethernet  --

eno16777736  bcc252b1-98d3-4fb6-a005-2caad8422622  802-3-ethernet  eno16777736

# Lets configure the static IP for the new connection, you need to specify the IP address and gateway.

[root@node2 network-scripts]# nmcli con add  con-name "RHEL_NEW"  ifname eth0 autoconnect no type ethernet ip4 192.9.0.10/24 gw4 192.9.0.254

Connection 'RHEL_NEW' (61a29c5f-72fd-4b4c-b850-1f139dd8d03b) successfully added.

# So when we try to bring up the interface we face this issue, lets troubleshoot it

[root@node2 network-scripts]#  nmcli connection up RHEL_NEW

Error: no device found for connection 'RHEL_NEW'.

[root@node2 network-scripts]#  systemctl -l status network.service

network.service - LSB: Bring up/down networking

   Loaded: loaded (/etc/rc.d/init.d/network)

   Active: failed (Result: exit-code) since Sat 2020-09-12 13:16:53 IST; 1min 0s ago

  Process: 3240 ExecStop=/etc/rc.d/init.d/network stop (code=exited, status=0/SUCCESS)

  Process: 4047 ExecStart=/etc/rc.d/init.d/network start (code=exited, status=1/FAILURE)

Sep 12 13:16:53 node2.example.com network[4047]: RTNETLINK answers: File exists

Sep 12 13:16:53 node2.example.com network[4047]: RTNETLINK answers: File exists

Sep 12 13:16:53 node2.example.com network[4047]: RTNETLINK answers: File exists

Sep 12 13:16:53 node2.example.com network[4047]: RTNETLINK answers: File exists

Sep 12 13:16:53 node2.example.com network[4047]: RTNETLINK answers: File exists

Sep 12 13:16:53 node2.example.com network[4047]: RTNETLINK answers: File exists

Sep 12 13:16:53 node2.example.com network[4047]: RTNETLINK answers: File exists

Sep 12 13:16:53 node2.example.com systemd[1]: network.service: control process exited, code=exited status=1

Sep 12 13:16:53 node2.example.com systemd[1]: Failed to start LSB: Bring up/down networking.

Sep 12 13:16:53 node2.example.com systemd[1]: Unit network.service entered failed state.

Not to forget i am in directory /etc/sysconfig/network-scripts

[root@node2 network-scripts]# pwd

/etc/sysconfig/network-scripts

Making sure hardware address was put in the network interface was up post that

[root@node2 network-scripts]# cat ifcfg-RHEL_NEW

TYPE=Ethernet

BOOTPROTO=none

DEFROUTE=yes

IPV4_FAILURE_FATAL=no

IPV6INIT=yes

IPV6_AUTOCONF=yes

IPV6_DEFROUTE=yes

IPV6_FAILURE_FATAL=no

NAME=RHEL_NEW

UUID=5f87ee25-7007-4a35-b343-8e1351443f54

ONBOOT=yes

HWADDR=00:0C:29:63:30:DE

NM_CONTROLLED=no

IPADDR0=192.9.0.10

PREFIX0=24

GATEWAY0=192.9.0.254

DNS1=192.9.0.254

IPV6_PEERDNS=yes

IPV6_PEERROUTES=yes

[root@node2 network-scripts]#  nmcli connection up RHEL_NEW

Connection successfully activated 

# As mentioned earlier in this post this works with ifdown and ifup as well

[root@node2 network-scripts]# ifdown ifcfg-RHEL_NEW

[root@node2 network-scripts]# ifup ifcfg-RHEL_NEW

NetworkManager is installed by default on Red Hat Enterprise Linux. If it is not, enter as root:

~]# yum install NetworkManager

To check whether NetworkManager is running:

~]$ systemctl status NetworkManager

NetworkManager.service - Network Manager

   Loaded: loaded (/lib/systemd/system/NetworkManager.service; enabled)

   Active: active (running) since Fri, 11 Sep 2020 11:30:04 +0100; 1 days ago

Note that the systemctl status command displays Active: inactive (dead) when NetworkManager is not running.

To start NetworkManager:

~]# systemctl start NetworkManager

To enable NetworkManager automatically at boot time:

~]# systemctl enable NetworkManager

nmcli is the one way of working with networking manager, while you can still use nmtui, nm-connection-editor, control-center, network connection icon.

Lets understand about these options : 

nmcli : A command-line tool which enables users and scripts to interact with NetworkManager. Note that nmcli can be used on systems without a GUI such as servers to control all aspects of NetworkManager. It has the same functionality as GUI tools.

nmtui : A simple curses-based text user interface (TUI) for NetworkManager

nm-connection-editor : A graphical user interface tool for certain tasks not yet handled by the control-center utility such as configuring bonds and teaming connections. You can add, remove, and modify network connections stored by NetworkManager. To start it, enter nm-connection-editor in a terminal.

control-center : A graphical user interface tool provided by the GNOME Shell, available for desktop users. It incorporates a Network settings tool. To start it, press the Super key to enter the Activities Overview, type Network and then press Enter. The Network settings tool appears.

network connection icon : A graphical user interface tool provided by the GNOME Shell representing network connection states as reported by NetworkManager. The icon has multiple states that serve as visual indicators for the type of connection you are currently using.

 

Create a gold image for 19c Oracle Database Software Home

Creating a gold image of 19c Oracle Database software with below command. Please note you would need sufficient amount of disk space before you begin, for a 9905899041 byte file it required almost 76 GB space for me to make it available on the file system, in one of my other post i had explained how to extend rhel7 file systems at run time online with root user :

[oracle@rhel7 ~]$ $ORACLE_HOME/runInstaller -silent  -createGoldImage -destinationLocation /u01/software/19c_dbhome
Launching Oracle Database Setup Wizard...

[FATAL] [INS-32707] The specified destination location (/u01/software/19c_dbhome) does not have enough free space.
   ACTION: Provide a destination location path with at least (76,112) MB of free space.

So once the space was made available i was able to make the gold image from the software home

[oracle@rhel7 ~]$ $ORACLE_HOME/runInstaller -silent  -createGoldImage -destinationLocation /u01/software/19c_dbhome
Launching Oracle Database Setup Wizard...

......

.................

.................................
Successfully Setup Software.
Gold Image location: /u01/software/19c_dbhome/db_home_2020-07-14_09-15-53PM.zip

OGYatra 2020 Oracle Events of All India Oracle Users Group (AIOUG)

The biggest Oracle User group conference is here from AIOUG https://www.aioug.org/ogyatra

The Oracle Groundbreakers / Oracle ACEs/ Oracle User Group Evangelists in the region are organizing an event called ‘Oracle Groundbreakers Yatra’ during the month of July 2020. India is a primary hub for information technology and a station for most software development centers and support centers for Oracle applications. Oracle community in India comprises of several 400000 users. In a large country like India, such user concentration is not in one location or one IT park but spread across its length and breadth.

The health of All India Oracle Users Community (AIOUG) is our primary concern. Considering global precautions for the COVID-19 Coronavirus, and building upon recommendations from the World Health Organization, AIOUG is taking a new approach to its Oracle Groundbreaker Yatra event. The event is a highly concentrated 15-day collaboration and transformation while providing the deep technical education needed for our Indian Oracle Community.

Join us Yatra and meet industry Gurus /celebrities. You will be able to acquire knowledge directs from the experts and get a deep dive into different skill areas.

Highlights:

• Global Webinar Series covering APAC, EMEA, AMS
• 14 days, 125+ hours of learning and networking
• 100+ Sessions, 100+ Speakers from 25+ Countries
• 60+ Oracle ACE community Speakers
• 7+ Hands-on Lab Sessions
• 5+ Master Classes
• 7 Tracks & No. of sessions
  • Database (DBA) – 32
  • Applications – 8
  • Analytics – 8
  • Future Technology - 8
  • Cloud - 8 
  • Java – 8
  • Community Sessions – 16
• Keynote, Interviews, and many more...

Register for FREE and Secure Your Seat! Hurry Up! We have Limited Place!!

Registration: 3 simple steps to register

1. Go to https://www.aioug.org/join page and join the AIOUG FREE membership
2. Log in and go to https://www.aioug.org/ogyatra page  
3. Check the schedule and register your favorite session(s). 

How to Join the Webinars:
Login to the AIOUG website and Go to https://www.aioug.org/ogyatra page and join the webinar 10 minutes before the actual time.

Who should attend?
If you are working with Oracle Database & Application in any aspect of Administration, Development this is a must-attend event for you. In the event, you get to meet and learn from the Oracle experts around the world at a single place. You discuss, ask questions, socialize, share knowledge, and meet fellow Oracle enthusiasts.  The event will be led on multiple tracks to target all the professionals at all levels to make the most of the event. Do you want more? It doesn’t stop there. You can meet and discuss with the Oracle experts out of the sessions after hours if you are not satisfied with the daylong sessions. 

Oracle Database Administrator, Developers, Technical Consultants, Functional Consultants, Data Scientists, System Administrator, Architects, Middleware Administrators, Operations Manager, Application Managers, Business Analysts

Reasons to Attend?

1. Be part of Yatra - Your one-stop shop for knowledge on Oracle where you can learn from experts from around the globe.
2. Build your network - A not-to-be-missed opportunity to meet in-person with your Oracle experts from across continents community with countless networking opportunities, you'll get to share thoughts and hear success stories all in person.
3. Learn best practices - Learn how to make the best of Oracle
4. Carry back the knowledge - Return from this conference with the confidence in knowledge gain that you can apply right back to your work. With the knowledge, you gain you can retrain your colleagues and showcase the best you can do with Oracle.
5. To expand your knowledge and get the answers you need all in Yatra
6. Share the knowledge - Meet the fellow Oracle folks and share your knowledge in various discussions in a session or over a coffee in the hallway. There is no restriction on where you talk about what you know or what you want to learn

Heartbeat table in Oracle Goldengate

Managing heartbeat in goldengate is important specially when it comes to monitor the lag and the heartbeat, this post covers how to add a heartbeat table at target, clean it up and reconfigure the heartbeat table

GGSCI (rhel7.example.com as ggadmin@euro) 3> add heartbeattable

2020-07-13 00:22:30  INFO    OGG-14001  Successfully created heartbeat seed table ""GGADMIN"."GG_HEARTBEAT_SEED"".

2020-07-13 00:22:30  INFO    OGG-14089  Successfully tracking extract restart position with heartbeat table ""GGADMIN"."GG_HEARTBEAT_SEED"".

2020-07-13 00:22:30  INFO    OGG-14032  Successfully added supplemental logging for heartbeat seed table ""GGADMIN"."GG_HEARTBEAT_SEED"".

2020-07-13 00:22:30  INFO    OGG-14000  Successfully created heartbeat table ""GGADMIN"."GG_HEARTBEAT"".

2020-07-13 00:22:30  INFO    OGG-14089  Successfully tracking extract restart position with heartbeat table ""GGADMIN"."GG_HEARTBEAT"".

2020-07-13 00:22:30  INFO    OGG-14033  Successfully added supplemental logging for heartbeat table ""GGADMIN"."GG_HEARTBEAT"".

2020-07-13 00:22:30  INFO    OGG-14016  Successfully created heartbeat history table ""GGADMIN"."GG_HEARTBEAT_HISTORY"".

2020-07-13 00:22:30  INFO    OGG-14089  Successfully tracking extract restart position with heartbeat table ""GGADMIN"."GG_HEARTBEAT_HISTORY"".

2020-07-13 00:22:30  INFO    OGG-14086  Successfully disabled partitioning for heartbeat history table ""GGADMIN"."GG_HEARTBEAT_HISTORY"".

2020-07-13 00:22:30  INFO    OGG-14023  Successfully created heartbeat lag view ""GGADMIN"."GG_LAG"".

2020-07-13 00:22:30  INFO    OGG-14024  Successfully created heartbeat lag history view ""GGADMIN"."GG_LAG_HISTORY"".

2020-07-13 00:22:30  INFO    OGG-14003  Successfully populated heartbeat seed table with "EURO".

2020-07-13 00:22:31  INFO    OGG-14004  Successfully created procedure ""GGADMIN"."GG_UPDATE_HB_TAB"" to update the heartbeat tables.

2020-07-13 00:22:31  INFO    OGG-14017  Successfully created procedure ""GGADMIN"."GG_PURGE_HB_TAB"" to purge the heartbeat history table.

2020-07-13 00:22:31  INFO    OGG-14005  Successfully created scheduler job ""GGADMIN"."GG_UPDATE_HEARTBEATS"" to update the heartbeat tables.

2020-07-13 00:22:31  INFO    OGG-14018  Successfully created scheduler job ""GGADMIN"."GG_PURGE_HEARTBEATS"" to purge the heartbeat history table.


GGSCI (rhel7.example.com as ggadmin@euro) 2> delete heartbeattable

2020-07-13 00:22:25  INFO    OGG-14007  Heartbeat seed table ""GGADMIN"."GG_HEARTBEAT_SEED"" dropped.

2020-07-13 00:22:25  INFO    OGG-14009  Heartbeat table ""GGADMIN"."GG_HEARTBEAT"" dropped.

2020-07-13 00:22:25  INFO    OGG-14011  Heartbeat history table ""GGADMIN"."GG_HEARTBEAT_HISTORY"" dropped.

2020-07-13 00:22:25  INFO    OGG-14026  Heartbeat lag view ""GGADMIN"."GG_LAG"" dropped.

2020-07-13 00:22:25  INFO    OGG-14028  Heartbeat lag history view ""GGADMIN"."GG_LAG_HISTORY"" dropped.

2020-07-13 00:22:25  INFO    OGG-14013  Procedure ""GGADMIN"."GG_UPDATE_HB_TAB"" dropped.

2020-07-13 00:22:25  INFO    OGG-14020  Procedure ""GGADMIN"."GG_PURGE_HB_TAB"" dropped.

2020-07-13 00:22:25  INFO    OGG-14015  Scheduler job ""GGADMIN"."GG_UPDATE_HEARTBEATS"" dropped.

2020-07-13 00:22:25  INFO    OGG-14022  Scheduler job ""GGADMIN"."GG_PURGE_HEARTBEATS"" dropped.

GGSCI (rhel7.example.com as ggadmin@euro) 3> add heartbeattable

2020-07-13 00:22:30  INFO    OGG-14001  Successfully created heartbeat seed table ""GGADMIN"."GG_HEARTBEAT_SEED"".

2020-07-13 00:22:30  INFO    OGG-14089  Successfully tracking extract restart position with heartbeat table ""GGADMIN"."GG_HEARTBEAT_SEED"".

2020-07-13 00:22:30  INFO    OGG-14032  Successfully added supplemental logging for heartbeat seed table ""GGADMIN"."GG_HEARTBEAT_SEED"".

2020-07-13 00:22:30  INFO    OGG-14000  Successfully created heartbeat table ""GGADMIN"."GG_HEARTBEAT"".

2020-07-13 00:22:30  INFO    OGG-14089  Successfully tracking extract restart position with heartbeat table ""GGADMIN"."GG_HEARTBEAT"".

2020-07-13 00:22:30  INFO    OGG-14033  Successfully added supplemental logging for heartbeat table ""GGADMIN"."GG_HEARTBEAT"".

2020-07-13 00:22:30  INFO    OGG-14016  Successfully created heartbeat history table ""GGADMIN"."GG_HEARTBEAT_HISTORY"".

2020-07-13 00:22:30  INFO    OGG-14089  Successfully tracking extract restart position with heartbeat table ""GGADMIN"."GG_HEARTBEAT_HISTORY"".

2020-07-13 00:22:30  INFO    OGG-14086  Successfully disabled partitioning for heartbeat history table ""GGADMIN"."GG_HEARTBEAT_HISTORY"".

2020-07-13 00:22:30  INFO    OGG-14023  Successfully created heartbeat lag view ""GGADMIN"."GG_LAG"".

2020-07-13 00:22:30  INFO    OGG-14024  Successfully created heartbeat lag history view ""GGADMIN"."GG_LAG_HISTORY"".

2020-07-13 00:22:30  INFO    OGG-14003  Successfully populated heartbeat seed table with "EURO".

2020-07-13 00:22:31  INFO    OGG-14004  Successfully created procedure ""GGADMIN"."GG_UPDATE_HB_TAB"" to update the heartbeat tables.

2020-07-13 00:22:31  INFO    OGG-14017  Successfully created procedure ""GGADMIN"."GG_PURGE_HB_TAB"" to purge the heartbeat history table.

2020-07-13 00:22:31  INFO    OGG-14005  Successfully created scheduler job ""GGADMIN"."GG_UPDATE_HEARTBEATS"" to update the heartbeat tables.

2020-07-13 00:22:31  INFO    OGG-14018  Successfully created scheduler job ""GGADMIN"."GG_PURGE_HEARTBEATS"" to purge the heartbeat history table.

Bad magic number in super-block while trying to open /dev/rhel/root

While running an oracle upgrade i ran into file system space issues, in Rhel 7 without any downtime, how we can extend the volumes please check here, There are some steps that we have to follow :

1. Present a new physical disk to the server

2. Adding the new physical disk to an exisiting Volume Group.

3. Expanding a logical volume within that Volume Group to use the new disk space.

4. Notify the operating system about the change in size.

[oracle@rhel7 ~]$ su -
Password:
Last login: Sat Mar 28 21:11:53 IST 2020 on pts/3
[root@rhel7 ~]# fdisk -l

Disk /dev/sda: 128.8 GB, 128849018880 bytes, 251658240 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk label type: dos
Disk identifier: 0x000b49f1

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *        2048     2050047     1024000   83  Linux
/dev/sda2         2050048   100370431    49160192   8e  Linux LVM

Disk /dev/mapper/rhel-root: 41.9 GB, 41943040000 bytes, 81920000 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes


Disk /dev/mapper/rhel-swap: 8388 MB, 8388608000 bytes, 16384000 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes

Step 1: Present the new physical disk to the server.

And Step 2: Add the new physical disk to an existing Volume Group.

[root@rhel7 ~]# fdisk /dev/sda
Welcome to fdisk (util-linux 2.23.2).

Changes will remain in memory only, until you decide to write them.
Be careful before using the write command.


Command (m for help): n
Partition type:
   p   primary (2 primary, 0 extended, 2 free)
   e   extended
Select (default p): e
Partition number (3,4, default 3):
First sector (100370432-251658239, default 100370432):
Using default value 100370432
Last sector, +sectors or +size{K,M,G} (100370432-251658239, default 251658239): +40G
Partition 3 of type Extended and of size 40 GiB is set

Command (m for help): m
Command action
   a   toggle a bootable flag
   b   edit bsd disklabel
   c   toggle the dos compatibility flag
   d   delete a partition
   g   create a new empty GPT partition table
   G   create an IRIX (SGI) partition table
   l   list known partition types
   m   print this menu
   n   add a new partition
   o   create a new empty DOS partition table
   p   print the partition table
   q   quit without saving changes
   s   create a new empty Sun disklabel
   t   change a partition's system id
   u   change display/entry units
   v   verify the partition table
   w   write table to disk and exit
   x   extra functionality (experts only)

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.

WARNING: Re-reading the partition table failed with error 16: Device or resource busy.
The kernel still uses the old table. The new table will be used at
the next reboot or after you run partprobe(8) or kpartx(8)
Syncing disks.

# Now run the partprobe command to sync the changes

[root@rhel7 ~]# partprobe
[root@rhel7 ~]# fdisk -l

Disk /dev/sda: 128.8 GB, 128849018880 bytes, 251658240 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk label type: dos
Disk identifier: 0x000b49f1

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *        2048     2050047     1024000   83  Linux
/dev/sda2         2050048   100370431    49160192   8e  Linux LVM
/dev/sda3       100370432   184256511    41943040    5  Extended

Disk /dev/mapper/rhel-root: 41.9 GB, 41943040000 bytes, 81920000 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes


Disk /dev/mapper/rhel-swap: 8388 MB, 8388608000 bytes, 16384000 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes


[root@rhel7 ~]# vgdisplay
  --- Volume group ---
  VG Name               rhel
  System ID           
  Format                lvm2
  Metadata Areas        3
  Metadata Sequence No  5
  VG Access             read/write
  VG Status             resizable
  MAX LV                0
  Cur LV                2
  Open LV               2
  Max PV                0
  Cur PV                3
  Act PV                3
  VG Size               86.87 GiB
  PE Size               4.00 MiB
  Total PE              22239
  Alloc PE / Size       12000 / 46.88 GiB
  Free  PE / Size       10239 / 40.00 GiB
  VG UUID               NvVNw2-XR30-7oS3-eGkz-81ZP-YziW-G238Kw

Step 3: Expand the logical volume to use the new space
 
[root@rhel7 ~]# lvdisplay
  --- Logical volume ---
  LV Path                /dev/rhel/root
  LV Name                root
  VG Name                rhel
  LV UUID                QibbKI-Hu0u-5I16-nYhe-VRXI-a14N-JgyX8N
  LV Write Access        read/write
  LV Creation host, time localhost, 2020-03-28 20:23:37 +0530
  LV Status              available
  # open                 1
  LV Size                39.06 GiB
  Current LE             10000
  Segments               1
  Allocation             inherit
  Read ahead sectors     auto
  - currently set to     8192
  Block device           253:0
 
  --- Logical volume ---
  LV Path                /dev/rhel/swap
  LV Name                swap
  VG Name                rhel
  LV UUID                ATHN2H-ycQ6-Z0az-pkxp-jCvF-2JBd-OWs7KG
  LV Write Access        read/write
  LV Creation host, time localhost, 2020-03-28 20:23:37 +0530
  LV Status              available
  # open                 2
  LV Size                7.81 GiB
  Current LE             2000
  Segments               1
  Allocation             inherit
  Read ahead sectors     auto
  - currently set to     8192
  Block device           253:1
 
[root@rhel7 ~]# lvextend -L+30G /dev/rhel/root
  Extending logical volume root to 69.06 GiB
  Logical volume root successfully resized


[root@rhel7 ~]# lvextend -L+8G /dev/rhel/root
  Extending logical volume root to 77.06 GiB
  Logical volume root successfully resized

# I ran into an issue with below command, the fix for that is given below,
# this is step 4 Step 4: Update the filesystem to use the new space

[root@rhel7 ~]# resize2fs /dev/rhel/root
resize2fs 1.42.9 (28-Dec-2013)
resize2fs: Bad magic number in super-block while trying to open /dev/rhel/root
Couldn't find valid filesystem superblock.

Check the space is still low :

[root@rhel7 ~]# df -h
Filesystem             Size  Used Avail Use% Mounted on
/dev/mapper/rhel-root   40G   37G  2.4G  95% /
devtmpfs               6.0G     0  6.0G   0% /dev
tmpfs                  6.0G  148K  6.0G   1% /dev/shm
tmpfs                  6.0G  8.9M  6.0G   1% /run
tmpfs                  6.0G     0  6.0G   0% /sys/fs/cgroup
/dev/sda1              997M  126M  872M  13% /boot



[root@rhel7 ~]# xfs_growfs /dev/rhel/root
meta-data=/dev/mapper/rhel-root  isize=256    agcount=4, agsize=2560000 blks
         =                       sectsz=512   attr=2, projid32bit=1
         =                       crc=0
data     =                       bsize=4096   blocks=10240000, imaxpct=25
         =                       sunit=0      swidth=0 blks
naming   =version 2              bsize=4096   ascii-ci=0 ftype=0
log      =internal               bsize=4096   blocks=5000, version=2
         =                       sectsz=512   sunit=0 blks, lazy-count=1
realtime =none                   extsz=4096   blocks=0, rtextents=0
data blocks changed from 10240000 to 20201472


# Now check the space, its 78 GB now

[root@rhel7 ~]# df -h
Filesystem             Size  Used Avail Use% Mounted on
/dev/mapper/rhel-root   78G   37G   41G  48% /
devtmpfs               6.0G     0  6.0G   0% /dev
tmpfs                  6.0G  148K  6.0G   1% /dev/shm
tmpfs                  6.0G  8.9M  6.0G   1% /run
tmpfs                  6.0G     0  6.0G   0% /sys/fs/cgroup
/dev/sda1              997M  126M  872M  13% /boot

Monitoring the classic replicat

Sometimes we see huge lag in our goldengate configuration that is because there are pending long running transaction at the target side, lets see how we can monitor the replicat status in the target system


GGSCI (node2.example.com) 27> send replicat rep1 status

Sending STATUS request to REPLICAT REP1 ...
  Current status: Processing data
  Sequence #: 22
  RBA: 9315748
  87049 records in current transaction


GGSCI (node2.example.com) 28> send replicat rep1 status

Sending STATUS request to REPLICAT REP1 ...
  Current status: Processing data
  Sequence #: 22
  RBA: 10275752
  96021 records in current transaction


GGSCI (node2.example.com) 31> dblogin useridlias ogg
Password:
ERROR: Unrecognized parameter (USERIDLIAS), expected USERID/USERIDALIAS.

GGSCI (node2.example.com) 32> dblogin useridalias ogg
Successfully logged into database.

GGSCI (node2.example.com as ggadmin@east) 33> info replicat rep1, detail

REPLICAT   REP1      Last Started 2020-03-28 14:28   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:02:28 ago)
Process ID           7179
Log Read Checkpoint  File /u01/app/oracle/product/12.1.2/ggate/dirdat/rt000020
                     First Record  RBA 55224002

Current Log BSN value: 1877190

Last Committed Transaction CSN value: 1897958


  Extract Source                          Begin             End           

  /u01/app/oracle/product/12.1.2/ggate/dirdat/rt000020  2020-03-28 13:33  First Record 
  /u01/app/oracle/product/12.1.2/ggate/dirdat/rt000020  2020-03-24 23:48  2020-03-28 13:33
  /u01/app/oracle/product/12.1.2/ggate/dirdat/rt000018  2020-03-13 23:07  2020-03-24 23:48
  /u01/app/oracle/product/12.1.2/ggate/dirdat/rt000016  2020-03-13 23:07  First Record 
  /u01/app/oracle/product/12.1.2/ggate/dirdat/rt000016  2019-11-22 13:10  2020-03-13 23:07
  /u01/app/oracle/product/12.1.2/ggate/dirdat/rt000010  * Initialized *   2019-11-22 13:10
  /u01/app/oracle/product/12.1.2/ggate/dirdat/rt000010  * Initialized *   First Record 
  /u01/app/oracle/product/12.1.2/ggate/dirdat/rt000009  2019-11-14 22:52  2019-11-22 12:53
  /u01/app/oracle/product/12.1.2/ggate/dirdat/rt000002  2019-11-14 22:49  2019-11-14 22:52
  /u01/app/oracle/product/12.1.2/ggate/dirdat/rt000002  2019-11-14 22:49  First Record 
  /u01/app/oracle/product/12.1.2/ggate/dirdat/rt000002  * Initialized *   2019-11-14 22:49
  /u01/app/oracle/product/12.1.2/ggate/dirdat/rt000000  * Initialized *   First Record 
  /u01/app/oracle/product/12.1.2/ggate/dirdat/rt000000  * Initialized *   First Record 
  /u01/app/oracle/product/12.1.2/ggate/dirdat/rt000000  * Initialized *   First Record 
  /u01/app/oracle/product/12.1.2/ggate/dirdat/rt000000  * Initialized *   First Record 


Current directory    /u01/app/oracle/product/12.1.2/ggate

Report file          /u01/app/oracle/product/12.1.2/ggate/dirrpt/REP1.rpt
Parameter file       /u01/app/oracle/product/12.1.2/ggate/dirprm/rep1.prm
Checkpoint file      /u01/app/oracle/product/12.1.2/ggate/dirchk/REP1.cpr
Checkpoint table     ggadmin.ckpt
Process file         /u01/app/oracle/product/12.1.2/ggate/dirpcs/REP1.pcr
Error log            /u01/app/oracle/product/12.1.2/ggate/ggserr.log

ERROR OGG-00664 OCI Error beginning session status = 1017-ORA-01017

Fixing credential store issue in Goldengate, i faced this issue recently so thought to update here on the blog, usually when we refresh our environments there is an issue with credentials of goldengate administrator, in that case you have to check credential store and make sure to update in extract or replicat process parameter files.


GGSCI (node1.example.com) 2> start *

Sending START request to MANAGER ...
EXTRACT DPUMP1 starting

Sending START request to MANAGER ...
EXTRACT EXT1 starting


GGSCI (node1.example.com) 3> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                         
EXTRACT     STOPPED     DPUMP1      00:00:00      84:16:29 
EXTRACT     STOPPED     EXT1            00:00:00      84:16:28 

GGSCI (node1.example.com) 12> view report ext1


***********************************************************************
                 Oracle GoldenGate Capture for Oracle
   Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO
   Linux, x64, 64bit (optimized), Oracle 12c on Aug  7 2014 10:40:21

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


                    Starting at 2020-03-28 12:53:43
***********************************************************************

***********
***
                          : [/u01/app/oracle/product/12.1.2/ggate/extract(ggs::gglib::MultiThreading::MainThread::ExecMain()+0x4f) [0x6ab4df]]
                          : [/u01/app/oracle/product/12.1.2/ggate/extract(ggs::gglib::MultiThreading::Thread::RunThread(ggs::gglib::MultiThreading::Thread::ThreadArgs*)
+0x104) [0x6ab9e4]]
                          : [/u01/app/oracle/product/12.1.2/ggate/extract(ggs::gglib::MultiThreading::MainThread::Run(int, char**)+0x8b) [0x6abdab]]
                          : [/u01/app/oracle/product/12.1.2/ggate/extract(main+0x3f) [0x5bfa2f]]
                          : [/lib64/libc.so.6(__libc_start_main+0xf4) [0x3ed681d994]]
                          : [/u01/app/oracle/product/12.1.2/ggate/extract [0x534319]]

2020-03-28 12:53:45  ERROR   OGG-00664  OCI Error beginning session (status = 1017-ORA-01017: invalid username/password; logon denied).

2020-03-28 12:53:45  ERROR   OGG-01668  PROCESS ABENDING.


GGSCI (node1.example.com) 13>   info credentialstore

Reading from ./dircrd/:

Default domain: OracleGoldenGate

  Alias: ggadmin
  Userid: ggadmin

  Alias: ggus
  Userid: ggus

GGSCI (node1.example.com) 14> delete credentialstore

Credential store deleted from ./dircrd/.

GGSCI (node1.example.com) 15> info credentialstore

ERROR: Unable to open credential store in ./dircrd/.


# Here you need to know the password of ggadmin user (administrater user of goldengate) if you dont know you can reset as well and update the credential store.

GGSCI (node1.example.com) 16> alter credentialstore add useridalias ogg user ggadmin, password g

ERROR: Invalid argument 'useridalias'.

GGSCI (node1.example.com) 17> alter credentialstore add user ggadmin alias ogg
Password:

ERROR: Unable to open credential store in ./dircrd/.

GGSCI (node1.example.com) 18> ALTER CREDENTIALSTORE ADD USER ggadmin alias ogg password g

ERROR: Invalid argument 'password'.

GGSCI (node1.example.com) 19>  ALTER CREDENTIALSTORE ADD USER ggadmin alias ogg
Password:

ERROR: Unable to open credential store in ./dircrd/.

GGSCI (node1.example.com) 20> add credentialstore

Credential store created in ./dircrd/.

GGSCI (node1.example.com) 21>  ALTER CREDENTIALSTORE ADD USER ggadmin alias ogg
Password:

Credential store in ./dircrd/ altered.

GGSCI (node1.example.com) 22> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                         
EXTRACT      STOPPED     DPUMP1      00:00:00      84:25:11 
EXTRACT      STOPPED     EXT1            00:00:00      84:25:10 


## Update the parameter file with this alias

GGSCI (node1.example.com) 30> view params ext1

EXTRACT ext1
USERIDALIAS ogg

EXTTRAIL /u01/app/oracle/product/12.1.2/ggate/dirdat/lt
TABLE SCOTT.TAB1;
TABLE SCOTT.TAB2;


GGSCI (node1.example.com) 26> start *

Sending START request to MANAGER ...
EXTRACT DPUMP1 starting

Sending START request to MANAGER ...
EXTRACT EXT1 starting


GGSCI (node1.example.com) 27> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                         
EXTRACT       RUNNING     DPUMP1      00:00:00      84:25:43 
EXTRACT      RUNNING     EXT1        84:26:08      00:00:01 


GGSCI (node1.example.com) 28> info ext1

EXTRACT    EXT1      Last Started 2020-03-28 13:02   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:05 ago)
Process ID           5950
Log Read Checkpoint  Oracle Redo Logs
                     2020-03-28 13:10:24  Seqno 17, RBA 5702656
                     SCN 0.1896693 (1896693)