Dear Reader,
The purpose of this blog is to learn oracle database administration skills to address various requests we come across on our day to day job.
Thanks
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.
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.
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.
