Restore Script using set newname

Restore Script:


1.startup database in nomount stage.

2. RMAN> restore controlfile from 'filename';

3.Execute below script in sqlplus


SQL> select 'set newname for datafile '||''''||name||''''||'  to  '||''''||'/t01/orawkly/db/apps_st/data/'||substr(name,13)||''''||';' from v$datafile;

Output:

set newname for datafile '+DATADG/prod/datafile/system.324.740403341'  to  '/t01/orawkly/db/apps_st/data/system.324.740403341';

set newname for datafile '+DATADG/prod/datafile/system.319.740403215'  to  '/t01/orawkly/db/apps_st/data/system.319.740403215';

set newname for datafile '+DATADG/prod/datafile/system.320.740403241'  to  '/t01/orawkly/db/apps_st/data/system.320.740403241';

set newname for datafile '+DATADG/prod/datafile/system.321.740403267'  to  '/t01/orawkly/db/apps_st/data/system.321.740403267';

set newname for datafile '+DATADG/prod/datafile/system.322.740403291'  to  '/t01/orawkly/db/apps_st/data/system.322.740403291';

set newname for datafile '+DATADG/prod/datafile/system.313.740403035'  to  '/t01/orawkly/db/apps_st/data/system.313.740403035';

set newname for datafile '+DATADG/prod/datafile/system.326.740403391'  to  '/t01/orawkly/db/apps_st/data/system.326.740403391';

set newname for datafile '+DATADG/prod/datafile/system.316.740403131'  to  '/t01/orawkly/db/apps_st/data/system.316.740403131';

set newname for datafile '+DATADG/prod/datafile/system.312.740402999'  to  '/t01/orawkly/db/apps_st/data/system.312.740402999';

set newname f

....

.

.

.

SQL> select 'set newname for tempfile '||''''||name||''''||'  to  '||''''||'/t01/orawkly/db/apps_st/data/'||substr(name,13)||''''||';' from v$tempfile;

Output:

set newname for tempfile '+DATADG/prod/tempfile/temp1.337.784871741'  to  '/t01/orawkly/db/apps_st/data//tempfile/temp1.337.784871741';
set newname for tempfile '+DATADG/prod/tempfile/temp2.338.766473947'  to  '/t01/orawkly/db/apps_st/data//tempfile/temp2.338.766473947';
set newname for tempfile '+DATADG/prod/tempfile/temp1.362.784871801'  to  '/t01/orawkly/db/apps_st/data//tempfile/temp1.362.784871801';
set newname for tempfile '+DATADG/prod/tempfile/temp1.345.784871805'  to  '/t01/orawkly/db/apps_st/data//tempfile/temp1.345.784871805';
set newname for tempfile '+DATADG/prod/tempfile/temp1.433.785153253'  to  '/t01/orawkly/db/apps_st/data//tempfile/temp1.433.785153253';
set newname for tempfile '+DATADG/prod/tempfile/temp1.456.785153337'  to  '/t01/orawkly/db/apps_st/data//tempfile/temp1.456.785153337';
set newname for tempfile '+DATADG/prod/tempfile/temp2.442.785153409'  to  '/t01/orawkly/db/apps_st/data//tempfile/temp2.442.785153409';
set newname for tempfile '+DATADG/prod/tempfile/temp2.441.785153447'  to  '/t01/orawkly/db/apps_st/data//tempfile/temp2.441.785153447';
set newname for tempfile '+DATADG/prod/tempfile/temp2.438.785153463'  to  '/t01/orawkly/db/apps_st/data//tempfile/temp2.438.785153463';
set newname for tempfile '+DATADG/prod/tempfile/temp2.436.785153491'  to  '/t01/orawkly/db/apps_st/data//tempfile/temp2.436.785153491';
set newname for tempfile '+DATADG/prod/tempfile/temp1.435.787813005'  to  '/t01/orawkly/db/apps_st/data//tempfile/temp1.435.787813005';
set newname for tempfile '+DATADG/prod/tempfile/temp1.476.789481103'  to  '/t01/orawkly/db/apps_st/data//tempfile/temp1.476.789481103';
set newname for tempfile '+DATADG/prod/tempfile/temp1.477.789591159'  to  '/t01/orawkly/db/apps_st/data//tempfile/temp1.477.789591159';
set newname for tempfile '+DATADG/prod/tempfile/temp1.478.789818991'  to  '/t01/orawkly/db/apps_st/data//tempfile/temp1.478.789818991';
set newname for tempfile '+DATADG/prod/tempfile/temp1.481.791669277'  to  '/t01/orawkly/db/apps_st/data//tempfile/temp1.481.791669277';
set newname for tempfile '+DATADG/prod/tempfile/temp1.482.792421145'  to  '/t01/orawkly/db/apps_st/data//tempfile/temp1.482.792421145';
set newname for tempfile '+DATADG/prod/tempfile/temp1.483.793330239'  to  '/t01/orawkly/db/apps_st/data//tempfile/temp1.483.793330239';
set newname for tempfile '+DATADG/prod/tempfile/temp1.520.800672945'  to  '/t01/orawkly/db/apps_st/data//tempfile/temp1.520.800672945';

18 rows selected.


4.Create script from above output(file name setnew.sql) :

Example:


run 
{
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
allocate channel d5 type disk;
allocate channel d6 type disk;
allocate channel d7 type disk;
allocate channel d8 type disk;
set newname for datafile '+DATADG/prod/datafile/system.324.740403341'  to  '/t01/orawkly/db/apps_st/data/system.324.740403341';
set newname for datafile '+DATADG/prod/datafile/system.319.740403215'  to  '/t01/orawkly/db/apps_st/data/system.319.740403215';
set newname for datafile '+DATADG/prod/datafile/system.320.740403241'  to  '/t01/orawkly/db/apps_st/data/system.320.740403241';
set newname for datafile '+DATADG/prod/datafile/system.321.740403267'  to  '/t01/orawkly/db/apps_st/data/system.321.740403267';
set newname for datafile '+DATADG/prod/datafile/system.322.740403291'  to  '/t01/orawkly/db/apps_st/data/system.322.740403291';
set newname for datafile '+DATADG/prod/datafile/system.313.740403035'  to  '/t01/orawkly/db/apps_st/data/system.313.740403035';
set newname for datafile '+DATADG/prod/datafile/system.326.740403391'  to  '/t01/orawkly/db/apps_st/data/system.326.740403391';
set newname for datafile '+DATADG/prod/datafile/system.316.740403131'  to  '/t01/orawkly/db/apps_st/data/system.316.740403131';
set newname for datafile '+DATADG/prod/datafile/system.312.740402999'  to  '/t01/orawkly/db/apps_st/data/system.312.740402999';
set newname for datafile '+DATADG/prod/datafile/system.325.740403367'  to  '/t01/orawkly/db/apps_st/data/system.325.740403367';
set newname for datafile '+DATADG/prod/datafile/system.323.740403317'  to  '/t01/orawkly/db/apps_st/data/system.323.740403317';
set newname for datafile '+DATADG/prod/datafile/apps_undots1.503.800858935'  to  '/t01/orawkly/db/apps_st/data/apps_undots1.503.800858935';
set newname for datafile '+DATADG/prod/datafile/apps_ts_archive.327.740403407'  to  '/t01/orawkly/db/apps_st/data/apps_ts_archive.327.740403407';
set newname for datafile '+DATADG/prod/datafile/apps_ts_interface.317.740403155'  to  '/t01/orawkly/db/apps_st/data/apps_ts_interface.317.740403155';
set newname for datafile '+DATADG/prod/datafile/apps_ts_media.310.740402929'  to  '/t01/orawkly/db/apps_st/data/apps_ts_media.310.740402929';
set newname for datafile '+DATADG/prod/datafile/apps_ts_nologging.334.740403501'  to  '/t01/orawkly/db/apps_st/data/apps_ts_nologging.334.740403501';
set newname for datafile '+DATADG/prod/datafile/apps_ts_queues.329.740403457'  to  '/t01/orawkly/db/apps_st/data/apps_ts_queues.329.740403457';
set newname for datafile '+DATADG/prod/datafile/apps_ts_queues.330.740403473'  to  '/t01/orawkly/db/apps_st/data/apps_ts_queues.330.740403473';
set newname for datafile '+DATADG/prod/datafile/apps_ts_seed.315.740403105'  to  '/t01/orawkly/db/apps_st/data/apps_ts_seed.315.740403105';
set newname for datafile '+DATADG/prod/datafile/apps_ts_seed.314.740403069'  to  '/t01/orawkly/db/apps_st/data/apps_ts_seed.314.740403069';
set newname for datafile '+DATADG/prod/datafile/apps_ts_summary.318.740403191'  to  '/t01/orawkly/db/apps_st/data/apps_ts_summary.318.740403191';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.303.740402513'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.303.740402513';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.304.740402577'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.304.740402577';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.300.740402317'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.300.740402317';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_idx.305.740402653'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_idx.305.740402653';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_idx.306.740402709'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_idx.306.740402709';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_idx.307.740402763'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_idx.307.740402763';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_idx.308.740402819'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_idx.308.740402819';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_idx.309.740402873'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_idx.309.740402873';
set newname for datafile '+DATADG/prod/datafile/ctxd.335.740403505'  to  '/t01/orawkly/db/apps_st/data/ctxd.335.740403505';
set newname for datafile '+DATADG/prod/datafile/odm.332.740403491'  to  '/t01/orawkly/db/apps_st/data/odm.332.740403491';
set newname for datafile '+DATADG/prod/datafile/olap.333.740403499'  to  '/t01/orawkly/db/apps_st/data/olap.333.740403499';
set newname for datafile '+DATADG/prod/datafile/owapub.336.740403505'  to  '/t01/orawkly/db/apps_st/data/owapub.336.740403505';
set newname for datafile '+DATADG/prod/datafile/portal.331.740403487'  to  '/t01/orawkly/db/apps_st/data/portal.331.740403487';
set newname for datafile '+DATADG/prod/datafile/sysaux.311.740402965'  to  '/t01/orawkly/db/apps_st/data/sysaux.311.740402965';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tools.328.740403443'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tools.328.740403443';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.301.740402403'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.301.740402403';
set newname for datafile '+DATADG/prod/datafile/apps_undots2.519.800859715'  to  '/t01/orawkly/db/apps_st/data/apps_undots2.519.800859715';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.348.752325839'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.348.752325839';
set newname for datafile '+DATADG/prod/datafile/apps_undots2.502.800860085'  to  '/t01/orawkly/db/apps_st/data/apps_undots2.502.800860085';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.302.752368967'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.302.752368967';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.344.744762819'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.344.744762819';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.347.748148865'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.347.748148865';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_idx.340.752369023'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_idx.340.752369023';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_idx.349.752374219'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_idx.349.752374219';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.350.752374275'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.350.752374275';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_idx.351.752381383'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_idx.351.752381383';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_idx.352.752387159'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_idx.352.752387159';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.353.752387229'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.353.752387229';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.346.766706291'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.346.766706291';
set newname for datafile '+DATADG/prod/datafile/trace.355.767425617'  to  '/t01/orawkly/db/apps_st/data/trace.355.767425617';
set newname for datafile '+DATADG/prod/datafile/apps_undots1.521.800859177'  to  '/t01/orawkly/db/apps_st/data/apps_undots1.521.800859177';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.357.754959791'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.357.754959791';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.358.757710229'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.358.757710229';
set newname for datafile '+DATADG/prod/datafile/apps_undots2.518.800860155'  to  '/t01/orawkly/db/apps_st/data/apps_undots2.518.800860155';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.360.759275935'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.360.759275935';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.342.768581569'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.342.768581569';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.363.760571167'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.363.760571167';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_idx.364.760571247'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_idx.364.760571247';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.365.762094761'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.365.762094761';
set newname for datafile '+DATADG/prod/datafile/apps_ts_media.366.765655125'  to  '/t01/orawkly/db/apps_st/data/apps_ts_media.366.765655125';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.367.766043905'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.367.766043905';
set newname for datafile '+DATADG/prod/datafile/apps_undots1.515.800860237'  to  '/t01/orawkly/db/apps_st/data/apps_undots1.515.800860237';
set newname for datafile '+DATADG/prod/datafile/apps_ts_summary.369.769150029'  to  '/t01/orawkly/db/apps_st/data/apps_ts_summary.369.769150029';
set newname for datafile '+DATADG/prod/datafile/apps_ts_media.370.769680843'  to  '/t01/orawkly/db/apps_st/data/apps_ts_media.370.769680843';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.371.769680883'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.371.769680883';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.372.769912859'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.372.769912859';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.341.785067793'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.341.785067793';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.374.770335473'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.374.770335473';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.381.785073481'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.381.785073481';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.376.771395259'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.376.771395259';
set newname for datafile '+DATADG/prod/datafile/sysaux.377.771582653'  to  '/t01/orawkly/db/apps_st/data/sysaux.377.771582653';
set newname for datafile '+DATADG/prod/datafile/apps_ts_media.378.771857499'  to  '/t01/orawkly/db/apps_st/data/apps_ts_media.378.771857499';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.379.772389815'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.379.772389815';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.380.772892485'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.380.772892485';
set newname for datafile '+DATADG/prod/datafile/apps_ts_media.382.773481063'  to  '/t01/orawkly/db/apps_st/data/apps_ts_media.382.773481063';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.383.775786841'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.383.775786841';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_idx.384.775786987'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_idx.384.775786987';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.386.785090157'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.386.785090157';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.387.777416949'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.387.777416949';
set newname for datafile '+DATADG/prod/datafile/apps_ts_media.388.777535151'  to  '/t01/orawkly/db/apps_st/data/apps_ts_media.388.777535151';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.389.777535387'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.389.777535387';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_idx.390.778664567'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_idx.390.778664567';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.391.778941853'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.391.778941853';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.395.779631035'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.395.779631035';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_idx.396.780091313'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_idx.396.780091313';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.397.780322647'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.397.780322647';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.405.785102131'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.405.785102131';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.426.785119163'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.426.785119163';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.400.781426351'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.400.781426351';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_idx.401.781628531'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_idx.401.781628531';
set newname for datafile '+DATADG/prod/datafile/apps_ts_media.402.781847283'  to  '/t01/orawkly/db/apps_st/data/apps_ts_media.402.781847283';
set newname for datafile '+DATADG/prod/datafile/apps_undots1.514.800860295'  to  '/t01/orawkly/db/apps_st/data/apps_undots1.514.800860295';
set newname for datafile '+DATADG/prod/datafile/sysaux.404.782318611'  to  '/t01/orawkly/db/apps_st/data/sysaux.404.782318611';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_idx.406.783915179'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_idx.406.783915179';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_idx.407.783952747'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_idx.407.783952747';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.408.783959469'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.408.783959469';
set newname for datafile '+DATADG/prod/datafile/apps_ts_media.409.783960095'  to  '/t01/orawkly/db/apps_st/data/apps_ts_media.409.783960095';
set newname for datafile '+DATADG/prod/datafile/apps_undots1.513.800860335'  to  '/t01/orawkly/db/apps_st/data/apps_undots1.513.800860335';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_idx.411.783979719'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_idx.411.783979719';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_idx.412.784007589'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_idx.412.784007589';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_idx.413.784055385'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_idx.413.784055385';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_idx.414.784077437'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_idx.414.784077437';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.415.784118253'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.415.784118253';
set newname for datafile '+DATADG/prod/datafile/apps_ts_interface.416.784133427'  to  '/t01/orawkly/db/apps_st/data/apps_ts_interface.416.784133427';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_idx.417.784187347'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_idx.417.784187347';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_idx.423.785156053'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_idx.423.785156053';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.422.785187429'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.422.785187429';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.399.785187505'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.399.785187505';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.398.785220165'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.398.785220165';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.375.785242937'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.375.785242937';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.339.785321607'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.339.785321607';
set newname for datafile '+DATADG/prod/datafile/apps_undots1.512.800860375'  to  '/t01/orawkly/db/apps_st/data/apps_undots1.512.800860375';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.425.784463807'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.425.784463807';
set newname for datafile '+DATADG/prod/datafile/apps_undots1.511.800860409'  to  '/t01/orawkly/db/apps_st/data/apps_undots1.511.800860409';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.361.785535309'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.361.785535309';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.449.785535959'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.449.785535959';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_idx.444.785944455'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_idx.444.785944455';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_idx.431.784597439'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_idx.431.784597439';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.443.785966181'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.443.785966181';
set newname for datafile '+DATADG/prod/datafile/apps_undots1.510.800860483'  to  '/t01/orawkly/db/apps_st/data/apps_undots1.510.800860483';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.439.786799255'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.439.786799255';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.437.787492957'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.437.787492957';
set newname for datafile '+DATADG/prod/datafile/apps_ts_media.434.788038557'  to  '/t01/orawkly/db/apps_st/data/apps_ts_media.434.788038557';
set newname for datafile '+DATADG/prod/datafile/apps_ts_media.432.788095317'  to  '/t01/orawkly/db/apps_st/data/apps_ts_media.432.788095317';
set newname for datafile '+DATADG/prod/datafile/apps_ts_media.429.788095361'  to  '/t01/orawkly/db/apps_st/data/apps_ts_media.429.788095361';
set newname for datafile '+DATADG/prod/datafile/apps_ts_media.428.788095487'  to  '/t01/orawkly/db/apps_st/data/apps_ts_media.428.788095487';
set newname for datafile '+DATADG/prod/datafile/apps_ts_media.424.788095549'  to  '/t01/orawkly/db/apps_st/data/apps_ts_media.424.788095549';
set newname for datafile '+DATADG/prod/datafile/apps_ts_media.421.788095603'  to  '/t01/orawkly/db/apps_st/data/apps_ts_media.421.788095603';
set newname for datafile '+DATADG/prod/datafile/apps_ts_media.420.788095639'  to  '/t01/orawkly/db/apps_st/data/apps_ts_media.420.788095639';
set newname for datafile '+DATADG/prod/datafile/apps_ts_media.419.788095689'  to  '/t01/orawkly/db/apps_st/data/apps_ts_media.419.788095689';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_idx.445.784770047'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_idx.445.784770047';
set newname for datafile '+DATADG/prod/datafile/apps_ts_media.418.788095723'  to  '/t01/orawkly/db/apps_st/data/apps_ts_media.418.788095723';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_idx.447.784771559'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_idx.447.784771559';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.448.784771705'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.448.784771705';
set newname for datafile '+DATADG/prod/datafile/apps_ts_media.410.788095771'  to  '/t01/orawkly/db/apps_st/data/apps_ts_media.410.788095771';
set newname for datafile '+DATADG/prod/datafile/apps_ts_media.403.788095807'  to  '/t01/orawkly/db/apps_st/data/apps_ts_media.403.788095807';
set newname for datafile '+DATADG/prod/datafile/apps_ts_media.385.788095851'  to  '/t01/orawkly/db/apps_st/data/apps_ts_media.385.788095851';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_idx.452.784775943'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_idx.452.784775943';
set newname for datafile '+DATADG/prod/datafile/apps_ts_media.373.788095887'  to  '/t01/orawkly/db/apps_st/data/apps_ts_media.373.788095887';
set newname for datafile '+DATADG/prod/datafile/apps_ts_media.354.788095925'  to  '/t01/orawkly/db/apps_st/data/apps_ts_media.354.788095925';
set newname for datafile '+DATADG/prod/datafile/apps_ts_media.368.788095975'  to  '/t01/orawkly/db/apps_st/data/apps_ts_media.368.788095975';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.359.788096229'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.359.788096229';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.457.788096275'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.457.788096275';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.458.788096327'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.458.788096327';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.459.788096365'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.459.788096365';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.460.788096397'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.460.788096397';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.461.788096433'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.461.788096433';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.462.788096475'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.462.788096475';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.463.788096517'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.463.788096517';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_idx.464.788096671'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_idx.464.788096671';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_idx.465.788096709'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_idx.465.788096709';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_idx.466.788096751'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_idx.466.788096751';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_idx.467.788096873'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_idx.467.788096873';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_idx.468.788097007'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_idx.468.788097007';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.469.788097063'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.469.788097063';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.470.788097101'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.470.788097101';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.471.788097203'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.471.788097203';
set newname for datafile '+DATADG/prod/datafile/sysaux.472.788097487'  to  '/t01/orawkly/db/apps_st/data/sysaux.472.788097487';
set newname for datafile '+DATADG/prod/datafile/apps_ts_media.473.788138083'  to  '/t01/orawkly/db/apps_st/data/apps_ts_media.473.788138083';
set newname for datafile '+DATADG/prod/datafile/apps_ts_media.474.788730491'  to  '/t01/orawkly/db/apps_st/data/apps_ts_media.474.788730491';
set newname for datafile '+DATADG/prod/datafile/apps_ts_media.475.788741239'  to  '/t01/orawkly/db/apps_st/data/apps_ts_media.475.788741239';
set newname for datafile '+DATADG/prod/datafile/apps_undots1.509.800860531'  to  '/t01/orawkly/db/apps_st/data/apps_undots1.509.800860531';
set newname for datafile '+DATADG/prod/datafile/sysaux.480.790549755'  to  '/t01/orawkly/db/apps_st/data/sysaux.480.790549755';
set newname for datafile '+DATADG/prod/datafile/apps_ts_media.484.793866669'  to  '/t01/orawkly/db/apps_st/data/apps_ts_media.484.793866669';
set newname for datafile '+DATADG/prod/datafile/apps_ts_media.485.793866905'  to  '/t01/orawkly/db/apps_st/data/apps_ts_media.485.793866905';
set newname for datafile '+DATADG/prod/datafile/apps_undots1.508.800860567'  to  '/t01/orawkly/db/apps_st/data/apps_undots1.508.800860567';
set newname for datafile '+DATADG/prod/datafile/apps_undots1.507.800860605'  to  '/t01/orawkly/db/apps_st/data/apps_undots1.507.800860605';
set newname for datafile '+DATADG/prod/datafile/xxbkup_tbs.488.795586939'  to  '/t01/orawkly/db/apps_st/data/xxbkup_tbs.488.795586939';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.489.799314741'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.489.799314741';
set newname for datafile '+DATADG/prod/datafile/apps_undots2.506.800860655'  to  '/t01/orawkly/db/apps_st/data/apps_undots2.506.800860655';
set newname for datafile '+DATADG/prod/datafile/apps_undots2.505.800860723'  to  '/t01/orawkly/db/apps_st/data/apps_undots2.505.800860723';
set newname for datafile '+DATADG/prod/datafile/apps_undots2.504.800860761'  to  '/t01/orawkly/db/apps_st/data/apps_undots2.504.800860761';
set newname for datafile '+DATADG/prod/datafile/apps_undots2.496.800860797'  to  '/t01/orawkly/db/apps_st/data/apps_undots2.496.800860797';
set newname for datafile '+DATADG/prod/datafile/apps_undots2.493.800860829'  to  '/t01/orawkly/db/apps_st/data/apps_undots2.493.800860829';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.495.800069083'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.495.800069083';
set newname for datafile '+DATADG/prod/datafile/apps_undots2.487.800860869'  to  '/t01/orawkly/db/apps_st/data/apps_undots2.487.800860869';
set newname for datafile '+DATADG/prod/datafile/apps_undots2.486.800860903'  to  '/t01/orawkly/db/apps_st/data/apps_undots2.486.800860903';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.498.800620367'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.498.800620367';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_idx.499.800620939'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_idx.499.800620939';
set newname for datafile '+DATADG/prod/datafile/apps_ts_media.500.800621767'  to  '/t01/orawkly/db/apps_st/data/apps_ts_media.500.800621767';
set newname for datafile '+DATADG/prod/datafile/apps_undots2.427.800860947'  to  '/t01/orawkly/db/apps_st/data/apps_undots2.427.800860947';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_data.516.800669007'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_data.516.800669007';
set newname for datafile '+DATADG/prod/datafile/apps_ts_tx_idx.517.800669151'  to  '/t01/orawkly/db/apps_st/data/apps_ts_tx_idx.517.800669151';
restore database;
switch datafile all;
release channel d1;
release channel d2;
release channel d3;
release channel d4;
release channel d5;
release channel d6;
release channel d7;
release channel d8;
}

5.RMAN>CATALOG START WITH '/pbp/oracle/backup_coldbkp' NOPROMPT;

6. Run the below command.

rman target / cmdfile=/t01/oracle/db/tech_st/11.1.0/dbs/setnew.sql log=/t01/oracle/db/tech_st/11.1.0/dbs/restore.log

Find Command in AIX

Find Command in AIX


find . -mmin -60 -name "*.dbf" -exec ls -ltr "{}" ";"


find . -mtime -1 -name "*.dbf" -exec ls -ltr "{}" ";"

UNIX Remove junk character

UNIX Remove junk character

Step 1:
vi <file_name>

Step 2:
Esc :%s/<Ctrl+v><Ctrl+M>//g

Step 3: Save file

Find ORACL_HOME

How Find ORACLE_HOME

SQL> var OHM varchar2(100);
SQL> EXEC dbms_system.get_env('ORACLE_HOME', :OHM)
BEGIN dbms_system.get_env('ORACLE_HOME', :OHM); END;

                                               *
ERROR at line 1:
ORA-06550: line 1, column 48:
PLS-00103: Encountered the symbol "" when expecting one of the following:
:= . ( % ;
The symbol "" was ignored.


SQL> EXEC dbms_system.get_env('ORACLE_HOME', :OHM);

PL/SQL procedure successfully completed.

SQL> PRINT OHM

OHM
--------------------------------------------------------------------------------
/u03/orauat/db/tech_st/11.1.0

SQL>

OEM Agent Installation in AIX(comman for all unix flavours)

Step 1:Download and unzip the agent 11g software

$ pwd

/agt12c/agent11g

$ unzip –o Linux_x86_64_Grid_Control_agent_download_11_1_0_1_0.zip

Step 2: Copy the ‘additional_agent.rsp’ file . 

$ cd /agt12c/agent11g/aix/response

$pwd

/agt12c/agent11g/aix/response

$cp additional_agent.rsp /agt12c/agent11g/aix/agent /

Step 3: Edit the copied file and modify the parameters given below according to your environment.

$ cd  agt12c/agent11g/aix/agent /

$ vi additional_agent.rsp

Change the Values in ‘additional_agent.rsp’  ,

SECURITY_UPDATES_VIA_MYORACLESUPPORT=false

DECLINE_SECURITY_UPDATES=true

INSTALL_UPDATES_SELECTION="skip"

ORACLE_AGENT_HOME_LOCATION=/agt12c/agent11g

b_silentInstall=true

OMS_HOST=190.01.01.14

OMS_PORT=7799

AGENT_REGISTRATION_PASSWORD=****** (provide your password here)

FROM_LOCATION="/agt12c/agent11g/aix/agent/stage/products.xml"

b_upgrade=false

EM_INSTALL_TYPE="AGENT"

Step 4:  Run the runInstaller,

$ pwd

/agt12c/agent11g/aix/agent

$ ls

additional_agent.rsp  install               runInstaller          stage

$ ./runInstaller -silent –responsefile /agt12c/agent11g/aix/agent/additional_agent.rsp

 Error Recieved:

For the above Error Open the below ports for connectivity,  In our case 190.01.01.14 is oem server and 190.10.100.127 is the Target where we installed the Agent.

Soruce IP /Subnet	Protocol	Source  Port	Destination  IP/Subnet	Destination  Port	Remarks
190.10.100.127	TCP	ANY	190.01.01.14	4889	OEM Ports
190.10.100.127	TCP	ANY	190.01.01.14	4900
190.10.100.127	TCP	ANY	190.01.01.14	7799
190.10.100.127	TCP	ANY	190.01.01.14	7403
190.10.100.127	TCP	ANY	190.01.01.14	7202
190.10.100.127	TCP	ANY	190.01.01.14	7788
190.10.100.127	TCP	ANY	190.01.01.14	3872
190.10.100.127	TCP	ANY	190.01.01.14	7101
190.10.100.127	TCP	ANY	190.01.01.14	7301
190.01.01.14	TCP	ANY	190.10.100.127	1524
190.01.01.14	TCP	ANY	190.10.100.127	8003
190.01.01.14	TCP	ANY	190.10.100.127	1830 & 1831

Perform The Step 4 Again.

$ ./runInstaller -silent –responsefile /agt12c/agent11g/aix/agent/additional_agent.rsp

Completion of Installation will display these below lines finally,

Configuration assistant "Agent Configuration Assistant" Succeeded

AgentPlugIn:agent configuration finished with status = true

Running Configuration assistant "Agent Add-on Plug-in"

Configuration assistant "Agent Add-on Plug-in" Succeeded

Step 5: Check the status of Agent .

$ cd  /agt12c/agent11g/agent11g/bin

$ pwd

/agt12c/agent11g/agent11g/bin

$ ./emctl status agent

Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0

Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.

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

Agent Version     : 11.1.0.1.0

OMS Version       : 11.1.0.1.0

Protocol Version  : 11.1.0.0.0

Agent Home        : /agt12c/agent11g/agent11g

Agent binaries    : /agt12c/agent11g/agent11g

Agent Process ID  : 34406466

Parent Process ID : 23527534

Agent URL         : https://TSTKKKMAAIND.ds.name.local:3872/emd/main/

Repository URL    : https://MANKKKMAAIND.ds.name.local:4900/em/upload

Started at        : 2012-06-21 15:08:32

Started by user   : oragnt

Last Reload       : 2012-06-21 15:08:32

Last successful upload                       : 2012-06-21 16:49:04

Total Megabytes of XML files uploaded so far :    11.95

Number of XML files pending upload           :        0

Size of XML files pending upload(MB)         :     0.00

Available disk space on upload filesystem    :    13.27%

Last successful heartbeat to OMS             : 2012-06-21 17:01:57

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

Agent is Running and Ready

Step 6:  Check the Target in OEM.