HOW TO TAKE RMAN BACKUP WHEN DATABASE IS IN OPEN MODE AND IN NOARCHIVELOG

Two days before I was assigned a task to take RMAN backup of a tablespace (MORCASZEMYAN) which was in Oracle 19c and had 200 datafiles.

It looks very easy to have backup of tablespace but the situation turns by 180 degree when I check the archivelog mode which was set to NOARCHIVELOG.

Now the problem was that the database was OLTP and we can't take database in mount stage nither the specific tablespace offline at any cost and you know Oracle doesn't support Open database backup in no Archivelog mode due to some valid reasons.

Now it was a huge headache but after long RND finally I succeeded to take backup of the tablespace in with database and tabelsapce both online.

HOW WAS IT DONE?

###First of all I found the datafiles ids of MORCASZEMYAN tablespace using below query.

SQL> select file#,name,CHECKPOINT_CHANGE#,LAST_CHANGE#,status from v$datafile where TS#=(select ts# from v$tablespace where name='MORCASZEMYAN');        

###Then I select first 100 ids and took the datafiles offline using the below query.

SQL> alter database datafile 1,2,...,100 offline for drop;   --NOTE THAT THE FILE IDS ARE NOT THE ACTUAL ONE DUE TO SECURITY REASONS I HAVE CHANGED THE IDS.        

## Again I run the below query which shows the status=RECOVER

SQL> select file#,name,CHECKPOINT_CHANGE#,LAST_CHANGE#,status from v$datafile where TS#=(select ts# from v$tablespace where name='MORCASZEMYAN');        

##I just run the below query so that the status becomes OFFLINE

SQL> recover datafile 1,2,..,100;        

##After that I took backup of datafiles taken offline using the RMAN command.

RMAN> backup datafile 1,2,...,100;        

##AFTER TAKING THE BACKUP I JUST MAKE THE PREVIOUS 100 OFFLINE DATAFILES ONLINE USING BELOW QUERY.

SQL> alter database datafile 1,2,..,100 online;        

##AND TAKE THE REMANING 100 DATAFILES OFFLINE AND REPEAT THE SAME PROCESS.

SO THIS WAS THE WHOLE STRATEGY I ADOPTED WHILE PROVIDING THE REQUIRED BACKUP TO THEM HOPE THIS WILL HELP YOU UNDERSTAND THAT IN REAL LIFE SENARIO THINGS DON'T ALWAYS GO STRAIGHT FORWARD SOMETIME YOU WILL HAVE TO THINK OUT OF THE BOX AS WELL.

REGARDS,

DOST MUHAMMAD KHALIL (OCP)

+92 311 4848475