RMAN

SET NEWNAME in action for RMAN clone

During RMAN clone operation If your diskgroup do not have enough space for all the datafiles then you will get an error and the process will not finish.

If you have enough free space over all the diskgroups then you can split the datafiles using those available diskgroups. For this purpose you can use the “SET NEW NAME FOR DATAFILE .. TO .. ” clause but It is a tedious job If you have tons of datafiles. Instead of doing it in manually you can use these scripts.

The logic is simple 🙂 find the cumulative sum for the datafiles that you can place in the first diskgroup. The remaining datafiles can go into the other diskgroups.

– Find the cut point assume the first 189 datafiles cumulative sum is good for the free space in the DATA.

select t.FILE#,
       trunc(bytes / 1024 / 1024 / 1024) datafile_size,
       t.name datafile_name,
       sum(trunc(bytes / 1024 / 1024 / 1024)) over(order by file#) cumulative_datafile_size
  from v$datafile t;

– Generate the SET command outputs

select file#,
case
when file# < 190 then
'SET NEWNAME FOR DATAFILE ' || file# || ' TO ''+DATA'';'
else
'SET NEWNAME FOR DATAFILE ' || file# || ' TO ''+RECO'';'
end case
from v$datafile;

– Put all the pieces together.

run {
SET NEWNAME FOR DATAFILE 1 TO '+DATA';
SET NEWNAME FOR DATAFILE 2 TO '+DATA';
SET NEWNAME FOR DATAFILE 3 TO '+DATA';
SET NEWNAME FOR DATAFILE 4 TO '+DATA';
SET NEWNAME FOR DATAFILE 5 TO '+DATA';
SET NEWNAME FOR DATAFILE 6 TO '+DATA';
SET NEWNAME FOR DATAFILE 7 TO '+DATA';
.
.
SET NEWNAME FOR DATAFILE 185 TO '+DATA';
SET NEWNAME FOR DATAFILE 186 TO '+DATA';
SET NEWNAME FOR DATAFILE 187 TO '+DATA';
SET NEWNAME FOR DATAFILE 188 TO '+DATA';
SET NEWNAME FOR DATAFILE 189 TO '+DATA';
SET NEWNAME FOR RECOFILE 190 TO '+RECO';
SET NEWNAME FOR RECOFILE 191 TO '+RECO';
SET NEWNAME FOR RECOFILE 192 TO '+RECO';
.
.
.
SET NEWNAME FOR RECOFILE 348 TO '+RECO';
SET NEWNAME FOR RECOFILE 349 TO '+RECO';
SET NEWNAME FOR RECOFILE 350 TO '+RECO';
SET NEWNAME FOR RECOFILE 351 TO '+RECO';
SET NEWNAME FOR RECOFILE 352 TO '+RECO';
SET NEWNAME FOR RECOFILE 353 TO '+RECO';
ALLOCATE CHANNEL tgt40 TYPE DISK;
ALLOCATE CHANNEL tgt50 TYPE DISK;
ALLOCATE CHANNEL tgt60 TYPE DISK;
ALLOCATE CHANNEL tgt70 TYPE DISK;
ALLOCATE CHANNEL tgt80 TYPE DISK;
ALLOCATE AUXILIARY CHANNEL dup4 TYPE DISK;
ALLOCATE AUXILIARY CHANNEL dup5 TYPE DISK;
ALLOCATE AUXILIARY CHANNEL dup6 TYPE DISK;
ALLOCATE AUXILIARY CHANNEL dup7 TYPE DISK;
ALLOCATE AUXILIARY CHANNEL dup8 TYPE DISK;
duplicate target database for standby from active database nofilenamecheck;
}
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s