DataGuard Sync Lag Troubleshooting

You know what the funny thing about DataGuard is? It's not actually that complicated. Until it is. And when sync lag starts climbing, you suddenly realize you've been treating it like this magical thing that just works, rather than something you actually need to understand.

I had this conversation with a junior DBA last month. She said, "I looked at sync lag, and it was at 50 minutes, but the standby seemed fine, the logs looked fine, everything looked fine." I asked her one question: "Did you check if the standby was actually applying redo?" Turns out, it wasn't. MRP had silently stopped applying, and nobody had noticed.

That's the thing about replication lag. It's not usually catastrophic. It's usually boring. And boring things are easy to miss until suddenly they're not.

The Basics: What We're Actually Measuring

Let me skip the long explanation and just show you what's happening:

On the primary, redo logs are getting written as people use the database. On the standby, a process called MRP (Media Recovery Process) is reading those logs and applying them. The gap between "what I just wrote" and "what standby has applied" is your lag.

We usually measure this in log sequence numbers. If the primary just finished log 12400 and the standby has only applied through log 12360, that's 40 logs of lag. If each log is 500MB, that's 20GB of unapplied redo waiting in the standby's redo log.

Under normal circumstances, this gap should be small. Seconds, maybe a minute. If your primary is generating 1GB of redo per minute and your network can push 5GB per minute, the standby is going to fall behind for a bit when traffic is heavy, then catch up. That's normal. That's life.

But if the gap keeps growing and never shrinks, something is actually broken. And broken is what we're going to talk about.

The Reality Check: Where to Actually Look

Here's what I've learned the hard way: don't start with V views. Start with the alert log. The alert log is where the database tells you what's actually happening.

tail -f $ORACLE_HOME/diag/rdbms/proddb/PRODDB_STBY/trace/alert_PRODDB_STBY.log

Watch this for a few minutes. You'll see lines like:

RFS[2]: Completed archivelog file transfer to standby (size=52M bytes)
Redo Apply progress to seqno: 12350 (15 MB/s)
RFS[3]: Error opening standby log: ORA-15028 ASM file name not found

That RFS error? That's your problem. RFS is the process that copies redo from primary to standby. If it's complaining, nothing is getting transferred. MRP can't apply redo it doesn't have.

Or sometimes you'll see:

MRP0: WARNING: Possible network disconnect detected (ospid=5678)

That tells you the network between primary and standby is flaky. Maybe losing packets, maybe latency spikes, maybe the connection just dropped.

The point is: the alert log is telling you something. You just have to listen.

When the Alert Log Says Nothing: Time to Query

Sometimes the alert log is clean as a whistle. No errors, no warnings. And lag is still climbing. That's when you actually need to do some detective work.

These queries tell you exactly what's going on:

-- Run this on STANDBY
-- What redo has been applied?
SQL> SELECT THREAD#, MAX(SEQUENCE#) FROM V$LOG_HISTORY GROUP BY THREAD#;

-- Run this on PRIMARY
-- What redo exists?
SQL> SELECT THREAD#, MAX(SEQUENCE#) FROM V$LOG WHERE ARCHIVED='NO' GROUP BY THREAD#;

-- The difference is your lag in log files

Now run this on the standby:

-- Is MRP running and actually applying?
SQL> SELECT PROCESS, PID, SEQUENCE#, BLOCK#, STATUS
     FROM V$MANAGED_STANDBY_PROCESS;

-- You want to see something like:
-- PROCESS     PID      SEQUENCE#  BLOCK#  STATUS
-- RFS         12345    12361      100     CONNECTED
-- MRP0        12346    12360      1       APPLYING_LOG
-- ARCH        12347    12359      1       CONNECTED

-- If MRP0's SEQUENCE# doesn't change over 5 minutes, it's stuck

That SEQUENCE# column on MRP0 is key. Watch it. If it's not moving, MRP is stuck. If it's moving slowly, the standby is struggling to keep up.

Three Scenarios and What They Mean

Here's the thing about replication lag: it's never random. It's always one of three things. Knowing which one matters.

Scenario One: RFS is receiving, but MRP isn't applying.

This means redo is getting to the standby, but something is preventing it from being applied. Usually it's one of these: MRP crashed and nobody noticed, the standby ran out of disk space so it can't write the redo, or there's a block corruption that's making MRP choke.

Fix: Check if MRP is actually running. If it crashed, restart it. If it's a corruption, you might need to do a resync. If it's disk space, free some up and let MRP catch up.

Scenario Two: MRP is applying, but very slowly.

This usually means either the primary is generating redo so fast that the standby can't keep up, or the standby is under heavy I/O load and can't apply logs quickly.

Check the primary. Is there a huge batch job running? A datapump export? An index rebuild that's generating a ton of redo? Check the standby. Is it CPU-bound or I/O-bound? If it's I/O-bound, the apply process is waiting for disk writes. If it's CPU-bound, MRP itself is just slow.

Scenario Three: Network is the bottleneck.

This is rare in my experience, but it happens. The redo is being generated, RFS is trying to send it, but the network can't keep up. You see high latency between primary and standby, or packet loss.

Check with: ping -c 100 standby_host from the primary. Look for packet loss or high variance in latency. If the network is the problem, talk to your network team. There's not much a DBA can do about WAN latency.

The Real Story: What Usually Happens

I want to tell you something that might sound obvious but isn't: sync lag is rarely actually broken. It's usually just paused.

Like last week. One of our standby databases had lag of about 2 hours. The ops team was freaking out. I looked at the standby and MRP was applying logs at 20MB/sec, which is totally normal. RFS was receiving at the same rate. Everything was working. The reason there was 2 hours of lag is because the primary had been in backup mode for 2 hours, which suspends redo transport. Once backup finished, the standby caught up in about 20 minutes.

Nobody was broken. Everything was working exactly as designed. We just didn't understand what we were looking at.

That's why I always start with the alert log and the simple V views. Because half the time, there's no actual problem. There's just an explanation for why lag is where it is.

The Root Cause Decision Tree (For When It's Actually Broken)

But sometimes it is actually broken. When that happens, this is how I think about it:

If RFS isn't receiving: network problem or primary stopped sending.
If RFS is receiving but MRP isn't applying: standby problem or corruption.
If MRP is applying but lag keeps growing: primary is too fast or standby is too slow.

Each branch has a different fix. And knowing which branch you're on is 80% of the work.

One Last Thing: Monitor Before You Panic

The best defense against sync lag problems is seeing them coming before they're a problem. A simple query scheduled every minute:

SELECT
  TRUNC(SYSDATE, 'MI') as check_time,
  (SELECT MAX(SEQUENCE#) FROM V$LOG WHERE STATUS='CURRENT')
    - (SELECT MAX(SEQUENCE#) FROM V$MANAGED_STANDBY_PROCESS WHERE PROCESS='MRP0')
    AS lag_sequences,
  (SELECT VALUE FROM V$PARAMETER WHERE NAME='log_archive_dest_2') as dest_status
FROM DUAL;

Store the results in a table. Plot them. Alert when lag starts growing. Most of the time, it'll show you exactly when something changed on the primary. A backup started. A batch job kicked off. Or it'll show you a real problem before your SLA gets breached.

That's the actual secret. Monitoring doesn't prevent problems, but it makes them obvious way earlier. And "obvious at 5 minutes" is better than "obvious at 2 hours."

Bottom Line

DataGuard sync lag isn't complicated. It's just easy to misunderstand because the database doesn't always scream when something's wrong. It whispers in the alert log. It sits quietly while MRP slowly processes redo. It looks fine until suddenly it's not.

The fix is simple: learn to read the alert log. Run a few queries. Understand what RFS and MRP actually do. Then when lag spikes, you'll spend 5 minutes figuring out what's happening instead of 2 hours panicking.

Your disaster recovery setup is only as good as your ability to understand it. So take some time to actually understand it.

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

Thank you,
Amit Pawar
Email: amitpawar.dba@gmail.com
WhatsApp No: +91-8454841011

How I Troubleshoot a RAC Node Eviction - Step by Step

Last month my phone rang at 2:14 AM. Not an alarm. An actual call from the on-call number, which on our team usually means one thing: something in the cluster fell over. It was one of our 2-node RAC databases. Node 2 had gone down hard, the application team was already seeing connection errors, and the person who called me said the one sentence every DBA dreads: "It just rebooted itself, nobody touched anything."

That's a RAC node eviction. And if you've been an Oracle DBA for more than a few months, you've either seen one already or you will soon. The frustrating part isn't fixing it. Most of the time, the fix is simple. The frustrating part is that when you're half asleep, and the phone is buzzing, it's very easy to open the wrong log first, chase the wrong clue, and burn 40 minutes before you even understand what actually happened.

So this post is not a theory dump on RAC internals. It's the actual sequence I follow, in the order I follow it, when a node gets evicted. Which files I open first, what I'm looking for in each one, and how I narrow down whether it's a network problem, a storage problem, or the node just choking on load. I'll use that 2 AM incident as the running example.

First: 30 seconds on who's talking to whom

Before jumping into logs, it helps to have this picture in your head, because every troubleshooting step below maps to one of these connections.

The part that matters most for eviction troubleshooting is the bottom half of this picture. Every node in a RAC cluster proves it's "alive" to the other nodes in two independent ways:

1. A network heartbeat over the private interconnect. Basically, the nodes pinging each other constantly.
2. A disk heartbeat. Each node writes to the voting disk at a fixed interval, and the others can see that write.

CSSD (Cluster Synchronization Services) is the process watching both. If a node misses too many of either kind of heartbeat (the threshold is controlled by the misscount parameter, 30 seconds by default in most versions), CSSD on the healthy node(s) declares the quiet node dead and, to avoid a split-brain situation where two nodes think they separately own the same data, the cluster forces that node to reboot. That's the eviction. It's not a bug; it's the cluster doing its job and protecting your data. Which is exactly why panicking and just bouncing the instance without checking the logs first usually doesn't help.

Step 1: Don't Touch the Database Alert Log Yet

This is the mistake I see most often, including from myself a few years back. The instance is down, so the instinct is to go straight to alert_<SID>.log. You'll find something like this:

Errors in file .../ORCL2_lmon_12345.trc:
ORA-29740: evicted by instance number 1, group incarnation 7
LMON (ospid: 12345): terminating the instance due to error 29740
Instance terminated by LMON, pid = 12345

And that's it. That's all the DB alert log will ever tell you. The instance was told to die by the clusterware. It's a symptom, not a cause. The actual explanation lives one layer down, in the Grid Infrastructure logs. This is the order I go through, and honestly writing it down as a diagram is the only reason I stopped skipping steps under pressure:

A quick note on paths, since these move around release to release and people always ask me for them: from 11.2 onwards everything sits under $GRID_HOME/log/<hostname>/, with a subfolder per daemon (cssd, crsd, ohasd, etc). If you're on 19c and can't find ocssd.log by browsing, adrci or just find $GRID_HOME/log/$(hostname -s) -iname "*.log" -mmin -180 will get you there fast.

Step 2: Read ocssd.log for the Actual Heartbeat Message

This is where the real story is. In our 2 AM case, ocssd.log on the surviving node (racnode1) had this, trimmed down:

[CSSD]CLSSNM00008: node racnode2 (2) at 90% heartbeat fatal, removal in 2.910 seconds
[CSSD]CLSSNM00008: node racnode2 (2) missed(6) network heartbeats
[CSSD]CLSSNM00008: node racnode2 (2) is impending reconfig, flag 918030, misstime 27090
[CSSD]clssnmSendingThread: sending remove message for racnode2, number 2
[CSSD]clssscExit: CSSD signal 11 received
Removal of node racnode2 (2) from cluster complete

"missed network heartbeats" is the key phrase. If it had said something about the voting disk or a "disk timeout," I'd have gone hunting in storage instead. This one line is basically a fork in the road, and it's the reason I now keep this little decision tree taped (mentally, at least) next to the incident checklist:

In our case, it was squarely the left branch. Network. So the next stop wasn't ASM; it was the network team's territory, except I checked it myself first because waiting for a ticket to get picked up at 2 AM is not a plan.

Step 3: Chase the Network Heartbeat Miss

A few commands I actually ran, in this order:

# confirm which interface is carrying the private interconnect
$ oifcfg getif
eth0  10.10.20.0  global  public
eth1  192.168.10.0  global  cluster_interconnect

# from the surviving node, hammer the private IP of the node that got evicted
$ ping -c 20 192.168.10.12

# check the interface for errors/drops, not just "is it up"
$ ifconfig eth1
$ ethtool -S eth1 | egrep -i "error|drop|discard"

# and don't forget the switch side if you have access
$ netstat -s | grep -i retrans

The ping came back fine a few minutes later. Which is normal and also unhelpful, because by the time you're checking, the blip is usually over. What actually gave it away was ethtool -S on eth1 showing a jump in rx_missed_errors right around 02:11, which lined up almost to the second with the "missed(6) network heartbeats" entry in ocssd.log. We pulled in the network team the next morning, and it turned out to be a flaky SFP module on that host's NIC that had been intermittently dropping packets for a couple of weeks without crossing any monitoring threshold. Nobody had noticed because it never dropped enough packets to trip the switch's own alerting. Just enough, once, to blow past the interconnect's 30-second misscount window.

The point of walking through this isn't the specific root cause. Yours will probably be different. The point is that the log told us which of three very different investigations to start (network vs storage vs OS load), and that saved a good hour of looking in the wrong place.

What It Looks Like When It's Storage, Not Network

Just so this isn't a one-scenario post. If ocssd.log instead shows something like:

[CSSD]clssnmvDiskCheck: (0x7f2a4c003) voting file /dev/oracleasm/disks/VOTE01 not written for 210610 ms
[CSSD]clssnmvDiskCheck: Aborting, 1 of 3 configured voting disks available, need 2

Then you stop looking at the network entirely and go straight to storage: check the ASM alert log for the same time window, run iostat -x 2 (or check your storage array's own latency dashboard) for I/O latency spikes, and if you're on multipath, check whether a path failover was in progress (multipath -ll, or the DM-Multipath logs). Voting disk write timeouts are almost always storage latency, not the disk being literally unreachable. The array is just too slow to answer within the CSS timeout, often during a SAN-side maintenance window or a sudden IO storm from something unrelated sharing the same array.

And the third case: no heartbeat message at all in ocssd.log, node just stopped responding. That's usually the node itself. CPU pegged, memory swapping, or occasionally a massive GC-style pause from something running on the host (yes, even on a "DB-only" server, someone eventually runs an ad-hoc script that eats all the CPU). For that one, OS Watcher or Cluster Health Monitor (oclumon dumpnodeview if it's still running, or CHM's own repository if OSW isn't installed) is what actually saved me the one time I had a genuinely hung node. vmstat from OSW showed run queue length going from single digits to 400+ in about ninety seconds.

Step 4: After You've Found the Cause, Close the Loop

Once the node came back up on its own (self-healing after a clean reboot, which is the usual case) I still ran through this before calling it done:

1. crsctl check crs and crsctl status resource -t on the recovered node. Make sure every resource is actually ONLINE and not just "starting."
2. crsctl query css votedisk. Confirm all voting disks are visible again.
3. Checked the alert log of the recovered instance for a clean startup, no repeat ORA-29740 within the next hour.
4. Opened a ticket with the network team with the exact timestamp and the ethtool output, instead of a vague "cluster had an issue last night."

That last point matters more than people give it credit for. "The node got evicted" is not a root cause and won't get fixed. "eth1 on racnode2 showed rx_missed_errors spiking at 02:11:04, correlating with a 6-heartbeat miss in ocssd.log" gets you a network engineer pulling an SFP module the same day.

Checklist Version: For When You're Getting Paged

1. Alert log just confirms the instance died. Don't stop there.
2. CRS alert log tells you who decided to evict and roughly when.
3. ocssd.log tells you WHY. Network heartbeat, disk heartbeat, or neither.
4. Network miss. Check the interconnect NIC/switch first, with timestamps.
5. Disk miss. Check storage/ASM latency around the same window.
6. Neither. The node was hung. Pull OSW/CHM data for that exact time.
7. Fix the actual cause. Don't just restart things and hope.
8. Write down the timestamp-to-cause mapping before you forget it by morning.

RAC troubleshooting has a reputation for being intimidating, mostly because there are four or five log files involved instead of one. Once you know the order to read them in, and you know that ocssd.log is really the file doing the talking, it stops being scary and becomes a fairly mechanical process. Which is exactly what you want at 2 AM.

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

Thank you,
Amit Pawar
Email: amitpawar.dba@gmail.com
WhatsApp No: +91-8454841011

Why Oracle Performs Full Table Scan for DISTINCT Queries

This question was recently asked by a colleague:

"I have an index on a column, but when I run a DISTINCT query on that column, Oracle still performs a full table scan. Why?”

At first glance, this feels counter-intuitive. If an index exists, Oracle should use it, right?

The answer is simple, but understanding why Oracle behaves this way is important for every DBA.

In this post, we’ll walk through a small test case and see exactly when and why Oracle uses (or ignores) an index for a DISTINCT query.

Test Setup

Let’s create a sample table with some data and an index on the column we’ll use in the DISTINCT query.

 

SQL>  create table some_data as select trunc(dbms_random.value(1,100)) val, a.* from dba_objects a;
Table created.
SQL> @desc some_data
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 VAL                                                NUMBER
 OWNER                                              VARCHAR2(128)
 OBJECT_NAME                                        VARCHAR2(128)
 SUBOBJECT_NAME                                     VARCHAR2(128)
 OBJECT_ID                                          NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(23)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
 NAMESPACE                                          NUMBER
 EDITION_NAME                                       VARCHAR2(128)
 SHARING                                            VARCHAR2(18)
 EDITIONABLE                                        VARCHAR2(1)
 ORACLE_MAINTAINED                                  VARCHAR2(1)
 APPLICATION                                        VARCHAR2(1)
 DEFAULT_COLLATION                                  VARCHAR2(100)
 DUPLICATED                                         VARCHAR2(1)
 SHARDED                                            VARCHAR2(1)
 CREATED_APPID                                      NUMBER
 CREATED_VSNID                                      NUMBER
 MODIFIED_APPID                                     NUMBER
 MODIFIED_VSNID                                     NUMBER

SQL> create index some_data_val_indx on some_data(val);
Index created.

SQL>

SQL>

SQL>  exec dbms_stats.gather_table_stats(null, 'SOME_DATA', cascade=>true);

PL/SQL procedure successfully completed.

SQL> set autot trace

SQL>  select distinct val from some_data;

99 rows selected.

Execution Plan

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

Plan hash value: 443606636

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

| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT   |           |    99 |   297 |   632   (1)| 00:00:01 |

|   1 |  HASH UNIQUE       |           |    99 |   297 |   632   (1)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| SOME_DATA |   116K|   341K|   628   (1)| 00:00:01 |

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

Statistics

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

         36  recursive calls

          0  db block gets

       2340  consistent gets

          0  physical reads

          0  redo size

       2207  bytes sent via SQL*Net to client

        463  bytes received via SQL*Net from client

          8  SQL*Net roundtrips to/from client

          4  sorts (memory)

          0  sorts (disk)

         99  rows processed

Even though an index exists on VAL, Oracle chooses a full table scan.

What If We Force the Index Using a Hint?

SQL> select /*+ index(some_data) */ distinct val from some_data;

99 rows selected.

Execution Plan

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

Plan hash value: 443606636

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

| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT   |           |    99 |   297 |   632   (1)| 00:00:01 |

|   1 |  HASH UNIQUE       |           |    99 |   297 |   632   (1)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| SOME_DATA |   116K|   341K|   628   (1)| 00:00:01 |

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

Hint Report (identified by operation id / Query Block Name / Object Alias):

Total hints for statement: 1 (U - Unused (1))

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

   2 -  SEL$1 / SOME_DATA@SEL$1

         U -  index(some_data)

Statistics

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

          1  recursive calls

          0  db block gets

       2312  consistent gets

          0  physical reads

          0  redo size

       2207  bytes sent via SQL*Net to client

        486  bytes received via SQL*Net from client

          8  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

         99  rows processed

Result

• Same execution plan

• Index hint is ignored

• Full table scan is still chosen

Oracle clearly doesn’t want to use the index here.

Why Is Oracle Ignoring the Index?

The reason is straightforward:

A B-Tree index does NOT store NULL values.

Since VAL is nullable, Oracle cannot guarantee that all distinct values can be derived by scanning only the index. To be 100% correct, it must scan the table to account for possible NULLs.

That’s why:

• Full table scan is chosen

• Index hints are ignored

What If the Column Is NOT NULL?

Let’s change the column definition.

SQL>  alter table some_data modify(val not null);

Table altered.

SQL>  select distinct val from some_data;

99 rows selected.

Execution Plan

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

Plan hash value: 1329759908

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

| Id  | Operation             | Name               | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT      |                    |    99 |   297 |    69   (8)| 00:00:01 |

|   1 |  HASH UNIQUE          |                    |    99 |   297 |    69   (8)| 00:00:01 |

|   2 |   INDEX FAST FULL SCAN| SOME_DATA_VAL_INDX |   116K|   341K|    65   (2)| 00:00:01 |

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

Statistics

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

          0  recursive calls

          0  db block gets

        236  consistent gets

          0  physical reads

          0  redo size

       2207  bytes sent via SQL*Net to client

        463  bytes received via SQL*Net from client

          8  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

         99  rows processed

Oracle now uses an INDEX FAST FULL SCAN, resulting in:

• Lower cost

• Fewer consistent gets

• Multiblock reads

• No table access

This is a significant improvement.

What If the Column Cannot Be Changed to NOT NULL?

In real systems, modifying column definitions is not always possible.

In that case, we can help Oracle by explicitly excluding NULLs.

SQL> alter table some_data modify (val null);

Table altered.

SQL> select /*+ index_ffs(some_data) */ distinct val from some_data where val is not null;

99 rows selected.

Execution Plan

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

Plan hash value: 1329759908

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

| Id  | Operation             | Name               | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT      |                    |    99 |   297 |    69   (8)| 00:00:01 |

|   1 |  HASH UNIQUE          |                    |    99 |   297 |    69   (8)| 00:00:01 |

|*  2 |   INDEX FAST FULL SCAN| SOME_DATA_VAL_INDX |   116K|   341K|    65   (2)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - filter("VAL" IS NOT NULL)

Statistics

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

          1  recursive calls

          0  db block gets

        236  consistent gets

          0  physical reads

          0  redo size

       2207  bytes sent via SQL*Net to client

        512  bytes received via SQL*Net from client

          8  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

         99  rows processed

Oracle now safely uses the index because:

• NULL values are explicitly filtered

• Index contains all required rows

Key Takeaways

1. B-Tree indexes do not store NULL values

2. DISTINCT on a nullable column often results in a full table scan

3. Oracle may ignore index hints if correctness cannot be guaranteed

4. DISTINCT on a NOT NULL column can use an index efficiently

5. Adding WHERE column IS NOT NULL can enable index usage

6. INDEX FAST FULL SCAN is especially efficient when the index covers all required columns

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

Thank you,
Amit Pawar
Email: amitpawar.dba@gmail.com
WhatsApp No: +91-8454841011

How to move a datafile that was added by mistake on local storage to shared location

In Real Application Cluster (RAC) environments, data files need to be on shared storage. It is possible that a data file gets added to a tablespace on the local filesystem instead of the shared storage subsystem by mistake. 

When another instance tries to contact the local file it will error out with:

ORA-01157: cannot identify/lock data file 17 - see DBWR trace file

ORA-01110: data file 17: '/home/oracle/test01.dbf'

Typically, this happens when the data file needs to be added to ASM but the '+'-sign is omitted when specifying the disk group. In this case, the data file will be created in the default directory specified by the 'db_create_file_dest' parameter, which defaults to $ORACLE_HOME/DBS.

In the scenario below, it was created by mistake on a different local storage.

This post explains how you can resolve this issue when the database is in archivelog mode and when the database is running in noarchivelog mode.

Note: 

Starting in 12c the process can be simplified using the new "online move" feature.

You need to have all the archive files since the creation of the datafile (when it was added to the tablespace)

1.  Find out the exact file name, file location, size and file number: 

SQL> select file_id, file_name, bytes, online_status from dba_data_files where tablespace_name = '<tablespace_name>';
FILE_ID FILE_NAME                          BYTES     ONLINE_
---------- ---------------------------------------- ---------- -------
16 +DATA/RAC/DATAFILE/test.309.1199529051  104857600 ONLINE
17 /home/oracle/test01.dbf                 104857600 ONLINE <<----

2. Put the datafile offline

SQL> alter database datafile 17 offline;
Database altered.

3. Recreate the datafile on the shared storage, please note that you need to do this on the node where the physical file resides and you need to specify the size retrieved in step 1

SQL> alter database create datafile '/home/oracle/test01.dbf' as '+DATA' size 100M;
Database altered.

4. Check the datafile status on second node.It will show RECOVER

SQL> select file_id, file_name, bytes, online_status from dba_data_files where tablespace_name ='TEST';
   FILE_ID FILE_NAME                                     BYTES ONLINE_
---------- ---------------------------------------- ---------- ------
        16 +DATA/RAC/DATAFILE/test.309.1199529051    104857600 ONLINE
        17 +DATA/RAC/DATAFILE/test.276.1199530103              RECOVER

5. Now Recover the datafile

SQL> recover datafile 17;
Media recovery complete.

6. Place the datafile back online

SQL>alter database datafile 17 online;
Database altered.

7. Verify the datafile status

 select file_id, file_name, bytes, online_status from dba_data_files where tablespace_name ='TEST';

   FILE_ID FILE_NAME                                     BYTES ONLINE_

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

        16 +DATA/RAC/DATAFILE/test.309.1199529051    104857600 ONLINE

        17 +DATA/RAC/DATAFILE/test.276.1199530103    104857600 ONLINE

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

Thank you,
Amit Pawar
Email: amitpawar.dba@gmail.com
WhatsApp No: +91-8454841011

Patching a DB System in OCI

Let's learn how to patch an Oracle Database 19c - 19.23 to 19.24 using the OCI DB System.

1. Validate the current version :

Login to the OCI Cloud Console

Navigate under Oracle Database --> Database (BM/VM) - BM - Bare Metal / VM - Virtual Machine

Now navigate to the DB System in question click on the Node and login to the box to validate the current version: 19.23.0.0.0

You can do the same check using the Database Command Line(CLI)

2. Check the latest version available.

Now navigate to the console:

Click on the DB Systems section --> At the bottom left of the screen click on

Databases

Click on Databases

Now navigate to the Database Page.

Scroll down to the section where it is says Version:

Click on the View hyperlink to view the latest version:

Click View latest Version Parch

As you can see below, the latest is 19.24. Let's Patch this database.

Latest 19.24 version

Note : Patching database required downtime, so perform this activity during maintenance window.

3. Run precheck

Once precheck is completed, now apply the actual patch.

4. Apply Database Patch

Navigate to the Database and the hamburger menu and click on Apply

Note with Apply, it does a pre-check and then applies the patch

You will now see the status change from Available to Applying

Applying the Patch 19.24.0.0.0

You can monitor the status using CLI using the below command.

[root@srv1 ~]# dbcli list-jobs

[root@srv1 ~]# dbcli describe-job -i a276b6a6-af06-4baa-ac9d-dd45c55f9ae7

Now the database patch has completed successfully.

4. Verify the database Patch

Let's verify the same using database command line(CLI)

Successful Patch 19.24.0.0.0

Congratulation..!! We have successfully patched an OCI bound DB system!

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

Thank you,
Amit Pawar
Email: amitpawar.dba@gmail.com
WhatsApp No: +91-8454841011