Oracle Full (not level 0) and incremental backup - how they support each other

 Dears,

This last week,  I was asked to perform a backup and restore exercise.

This is completely a new db, not in my support scope.

Still did study the DB setup thoroughly and gave feedbacks and brought as much as inputs as I needed.

There was a immense pressure on me to execute this adhoc backup/restore exercise for the db successfully due to some so and so reasons, which I cant discuss.

Anyway, challenge upfront - step into an unknown waters (the customizations), but it is still it is oracle DB.  The study I did , helped me to gain sufficient knowledge to carve out the plan for the restore/recovery exercise.

Objective: Refresh DEV with PROD data. The DB size was 14TB. This needs to be executed in a reasonable time frame. Last time the activity happened on Oct 2023, but no disclosure of the plan, but the only info given was restore took 7hrs.

I prepared the plan, and executed all prereqs, that night I was shared the credentials to login to the target DB where the restore is to be done.

On the 'D' day, I learnt the backups which are secured on the weekends "FULL" backup not incremental backup. Rest all days the backup admin is securing incremenal level 1.

So a FULL on sunday and all other days are an incremental level 1.

I was just blown away by this config, this is a managed backup, so no backup logs accessible before. My fault I didnt scrutinize the backup before. I kept hearing there is a incremental level 0 and level 1 but there is a mistake -- hrrrrrrrrr!

Ok, now the challenege ahead with 3TB worth of archive to recover from Full backup the PITR requested.

So I proceeded with the restore...

Attempt  1 - restore  failed due to the "convert" strings in init param not supporting non-omf db restore.

Attempt  2 - Set new name for the datafiles by dynamically generating string using regexp_replace(name,'source','tgt',1,1) from v$datafile view and set them up in the restore command.

run

{

set newname for datafile 1 to '/tgt/file1.dbf';

set until time to_date('');

restore database;

}

while the restore was ongoing I broke the archive restore command into 9 parts thinking I have to

>> restore archives

>> recover archives

>>purge archives

This is when my stuck an option where rman already automated this routine...

recover database delete archivelog XXX G;

This does all what was mentioned above.

Restore finished in 3hrs for 11TB.

Now comes the main discussion point of this blog, when I kicked this recovery on.

To my surprise, the incremental level 1 was used and it was used properly; which made me wonder what could have happened?

I didn't collect evidence, but I will when I find opportunity..

1. Full backup doesn't store the incremental scn value in the dictionary which is its nature.  So my assumption was they don't participate in incremental restore & recovery

This is where oracle turned smart, oracle after full backup was restored.

 The incremental level 1 backup it used was actually taken on top of the last incremental level 1.So ideally what happened is,

Full backup restored df1 (scn-111), df2 (scn-123) etc..

incremental level 1 had changes captured from 99 and hence this incremental level 1 was used during this recovery exercise successfully. The archives left over were used as required to roll forward the DB.

Thanks

Oracle RDBMS 11.2 - Service Setup - HA config

 Dears,

Today while I was running through a Operation Acceptance Testing (OAT) in my DB.

My DB version 11.2 (but it applies to all DB version until atleast 19c) running without SIHA or GI.

The objective of the OAT was to test the application connection during db switchover.

Mistake # 1 - we never created the HA TNS for this DB

Mistake # 2 - we didnt setup dataguard broker on this DB

Mistake#2 - I already prepared a SOP, I ran through it and it was ALL OK. Note I faced ORA-16664 & ORA-16665 errors when I enabled the configuration. Fix was to properly setup local_listener setting, since the <DBUNIQUENAME>_DGB service wasnt starting up, until the local_listener is properly setup in a listener with non default port or listener name different from "LISTENER".

So the DGMGRL is all OK.

I stumbled with my (team) mistake, which is service miss. I never have setup a service on a standalone DB with no SIHA. 

I used to run

srvctl add service...

But now I dont want to just setup the below as well, since I wanted this service to be available across both primary and standby.

alter system set service_name='XXXXXX'; -- some service name

Then threw google search & OTN I learnt, 

exec dbms_service.create_service( -

service_name=>''

,network_name=>''

,...

);

can be used to setup the service.

On top of it, we need to setup a DB startup trigger which kicks off the service when the database_role matches "PRIMARY".

So this saved my day.

I proceeded to change the description part  (to include new service name) of the app TNS, since I cant change the alias now, which is used everywhere in app.

I started around 11:00AM to prepare for the OAT, finished all the setup by 2PM

Good news is the OAT finished fine. The switchover, switchback ALL went just fine.

Thanks

Query to check rman backup details in oracle

Query to check rman backup details in oracle:

set lines 220
set pages 1000
col cf for 9,999
col df for 9,999
col elapsed_seconds heading "ELAPSED|SECONDS"
col i0 for 9,999
col i1 for 9,999
col l for 9,999
col output_mbytes for 999,999,999 heading "OUTPUT|MBYTES"
col session_recid for 999999 heading "SESSION|RECID"
col session_stamp for 99999999999 heading "SESSION|STAMP"
col status for a10 trunc
col time_taken_display for a10 heading "TIME|TAKEN"
col output_instance for 9999 heading "OUT|INST"
select
  j.session_recid, j.session_stamp,
  to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,
  to_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss') end_time,
  (j.output_bytes/1024/1024) output_mbytes, j.status, j.input_type,
  decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',
                                     3, 'Tuesday', 4, 'Wednesday',
                                     5, 'Thursday', 6, 'Friday',
                                     7, 'Saturday') dow,
  j.elapsed_seconds, j.time_taken_display,
  x.cf, x.df, x.i0, x.i1, x.l,
  ro.inst_id output_instance
from V$RMAN_BACKUP_JOB_DETAILS j
  left outer join (select
                     d.session_recid, d.session_stamp,
                     sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,
                     sum(case when d.controlfile_included = 'NO'
                               and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,
                     sum(case when d.backup_type||d.incremental_level in ('I0', 'D0') then d.pieces else 0 end) I0,
                     sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,
                     sum(case when d.backup_type = 'L' then d.pieces else 0 end) L
                   from
                     V$BACKUP_SET_DETAILS d
                     join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
                   where s.input_file_scan_only = 'NO'
                   group by d.session_recid, d.session_stamp) x
    on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp
  left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id
                   from GV$RMAN_OUTPUT o
                   group by o.session_recid, o.session_stamp)
    ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp
where j.start_time > trunc(sysdate)-&NUMBER_OF_DAYS
order by j.start_time;

>> on the prompt enter the # of days you want the backup for.

This is controlled by below setting in rman:

Configure RMAN output to keep for 'n' days;

where n is the number of days the information is retained.

This is a quick query sharing blog.

Thanks

Oracle GoldenGate - 1403 and KeyCols defination by GoldenGate automatically

********************* READ My update below for quick read **********

Dec 30 2023:

Can we trust the oracle GG's default mechanism to pick keycols for target when there is no primary key or unique key with not null constraint defined?

NO!!!

Oracle GG tries it level best to pick to pick a proper keycol for the target to perform the DMLs.

But at times it fails, so be prepare to face it, in case your table list part of replication doesn't have PK or UK with not null defined.

GG replication will throw 1403 errors during delete, update operations.

Since the keycol it developed at times may not identify the record in target.

For ex.

You may have a source record with below signature...

N,C1,C2,C3 -- attributes

1,A,C,D

Target:

1,B,D,D

In this case if oracle picks N as keycol and N happened to be a unique key, your good.

If oracle GG goes an extra mile and identifies N,C1 as keycols for target.

Then you will get 1403 error (which is data not found in target).

So it is wise, as soon as you face a 1403 error or proactively identify all the tables with non unique identifiers and get your fixup routine ready.

The discard will tell you the reason and report file will tell you the mapping info. These two piece of info should help you identify the cause and fix.

Hope this helps!

Thanks

****************** Update to this blog **********************

Jan 27 2024:

After handling a migration project involving golden gate and working with oracle support, my experience with oracle goldengate improved. I learnt that 1403 error in my case popped up due to the fact, the keycol values are being updated.

Table 1: cola, colb, colc

unique index; cola,colb

OGG, properly picks unique index as the keycol definition for replication.

Imagine if the keycol value is changed meaning updated in source, the corresponding keycol value pair mightnt be available in target, this means a no data found error.

imagine:

1,2,'A'

kecol: 1,2

if this value is changed to 2,3 --> then this wont be found in target.

Oracle assumes your not changing the search field or index field, which is expected as a product.

So it is upto the GG DBA to ensure this is the case. In my it was my responsibility.

Thank you!

Oracle: Will "set newname for database to new" work in active DB duplication?

Dears,

Today, while I was assisting my colleague to get a standby db build using active db duplication command.

Source DB is 30TB in size.

Background: the standby database was activated last saturday due to primary server hardware issue.

We were left with no flashback option, since it wasnt enabled. So db restore in the old primary was the only way to reinstate (we differed OS utilities).

Challenge: The db had a mix of non-OFM and OFM format of datafiles.

So I discussed with my colleague and proposed to move all the files to OMF format for 

1. auto standby file management
2. ease of datafile maintenance operations later

We noticed the DB had

• db_file_name_convert
• db_create_file_dest

parameters set. When I fired the active db duplication asis, with no change to these 2 param setting.

I noticed, Oracle trying to restore even the OMF files into the same path in target as if it is restoring the non-OMF files (file names had encrypted identity).

So I decided to remove db_file_name_convert, after reading oracle doc, I felt this is the main culprit.

To ensure the datafiles gets the benefit of db_create_file_dest parameter, I introduced

"set newname for database to new" as well to the duplicate rman run command.

run

{

allocate channel prmry1 type disk;

...

allocate channel auxiliary channel stby1 type disk;

...

set newname for database to new;

duplicate target database for standby from active databvase

dorecover

spfile

   parameter...

...

NOFILENAMECHECK;

}

All the files got restored to the location indicated by db_create_file_dest as I expected.

This worked perfectly fine.

Thanks

Block Change Tracking File Invalid (RMAN-08606)

Dears,

Today while I was performing a FULL db backup of my production database to disk, I received a weird RMAN-08606 error.

RMAN-08606: WARNING: The Change Tracking File Is Invalid

On reading oracle note, I learnt it is risk to our incremental level 1s. Since the change tracker is missing changed block information.

Oracle says to ignore any backup taken since last incremental level 0, if you have this error. Since those backups potentially are incomplete due to missing changed block info.

So oracle proposes few options like

1. placing this bct file on a relatively less hot filesystem
2. increasing large_pool_size
3. increasing _bct_public_dba_buffer_size to a larger value (use x$krcstat view for getting this value).

This should reduce the wait events related to bct and avoid the situation highlighted.

What I did is as a first measure disabled the bct, since a risk to backup is huge for me. My DB is small around 2TB. So I am not bothered reading 2TB in place of 200GB.

Thanks

 

DBMS_SQLTUNE.REPORT_SQL_MONITOR - reporting "DONE(ERROR)"

 Dears,

Today while I was watching a SQL query execution statsistics, I noticed a weired behaviour coming out of oracle's DBMS_SQLTUNE.REPORT_SQL_MONITOR package.

DBMS_SQLTUNE.REPORT_SQL_MONITOR is used to report the live execution statistics of the SQL.

This particular package reported my active SQL as "DONE(ERROR)" as if like some one killed the session or it failed due to a temp or undo or datafile space error.

It didnt report why it failed though still. Interestingly the elapsed time is 7748 vs it's overall query duration reported was 317s.

It took me by surprise, then I check v$session and noticed it isnt rolling back the merge it was running; since it wasnt reading from undo datafile.

So I suspected this is a weird reporting issue. So I continued checking if the session is really active.

Yes the session was really active and v$sql perfectly increments elapsed, cpu, io wait times along with its corresponding other metrics.

Cause: the query ran using parallel slaves, query had plan regression and it ended up running longer with few slaves staying idle for more than 30mins.

Now oracle says this particular condition where a px slave part of the query execution staying idle is an anomaly and is reported as a failed sql execution from oracle end; but really isnt the case :)

RDBMS: Oracle

Version: 12.2

Oracle note: 1900060.1

Bug: 13523091 (Incorrect status in v$sql_monitor with parallel query)

Thank you!