ASM QUICK REF‎ > ‎

Disk-Based Migration of a Database to ASM

posted Apr 7, 2011, 3:54 PM by Sachchida Ojha   [ updated Apr 8, 2011, 1:45 PM ]
--***************************************************************************************************
Stgtdb1 migration TO asm
***************************************************************************************************
SYS@STGTDB1  SQL>select NAME,STATUS,BLOCK_SIZE,FILE_SIZE_BLKS from  v$controlfile;

NAME                                                                             STATUS  BLOCK_SIZE FILE_SIZE_BLKS
-------------------------------------------------------------------------------- ------- ---------- --------------
/u02/TDB1/oradata/tdb1_ctl01.ctl                                                               8192           3790
/u03/TDB1/oradata/tdb1_ctl02.ctl                                                               8192           3790
/u04/TDB1/oradata/tdb1_ctl03.ctl                                                               8192           3790


SYS@STGTDB1  SQL>select GROUP#,MEMBER from v$logfile;

    GROUP# MEMBER
---------- ----------------------------------------------------------------------------------------------------
         1 /u06/TDB1/oradata/redo_01.log
         1 /u05/TDB1/oradata/redo_01.log
         2 /u06/TDB1/oradata/redo_02.log
         2 /u05/TDB1/oradata/redo_02.log
         3 /u06/TDB1/oradata/redo_03.log
         3 /u05/TDB1/oradata/redo_03.log
         4 /u06/TDB1/oradata/redo_04.log
         4 /u05/TDB1/oradata/redo_04.log
         5 /u06/TDB1/oradata/redo_05.log
         5 /u05/TDB1/oradata/redo_05.log
         6 /u06/TDB1/oradata/redo_06.log
         6 /u05/TDB1/oradata/redo_06.log

12 rows selected.


1.Create the ASM instance in ASM_HOME /mnt/u01/ARBPRDDB/app/oracle/product/11.2.0/grid.

2.Create ASM Disk groups (DATA, TDB1_FRA)

3.Change ORACLE_HOME and SID to STGTDB1

4.Run the following command

$rman target / catalog rman/rman@rman

RMAN>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;
BACKUP AS COPY INCREMENTAL LEVEL 0
DATABASE FORMAT '+DATA' 
TAG 'ORA_ASM_MIGRATION_TDB1';
 }      


SYS@STGTDB1  SQL>create table admn.testasm(lastupdated date);

Table created.

SYS@STGTDB1  SQL>insert into admn.testasm values(sysdate);

1 row created.

SYS@STGTDB1  SQL>/

1 row created.

SYS@STGTDB1  SQL>/

1 row created.

SYS@STGTDB1  SQL>/

1 row created.

SYS@STGTDB1  SQL>commit;

Commit complete.

SYS@STGTDB1  SQL>select * from admn.testasm;

LASTUPDAT
---------
07-APR-11
07-APR-11
07-APR-11
07-APR-11

SYS@STGTDB1  SQL>select count(*) from admn.testasm;

  COUNT(*)
----------
         4

SYS@STGTDB1  SQL>

SYS@STGTDB1  SQL>quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
va-iperf01:STGTDB1:/u01/ARBPRDDB/home/oracle>rman target / catalog rman/rman@rman

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Apr 7 22:07:12 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: STGTDB1 (DBID=2538883184)
connected to recovery catalog database

RMAN> sql'alter system archive log current';

sql statement: alter system archive log current


**** Stop all application pointing to STGTDB1

RMAN>backup  incremental level 1 for recover of copy with tag 'ORA_ASM_MIGRATION_TDB1' database ;
input datafile file number=00004 name=/u08/TDB1/oradata/aud01.dbf
channel ORA_DISK_1: starting piece 1 at 08-APR-11
channel ORA_DISK_1: finished piece 1 at 08-APR-11
piece handle=+TDB1_FRA/stgtdb1a/backupset/2011_04_08/nnndn1_ora_asm_migration_tdb1_0.261.747942767 tag=ORA_ASM_MIGRATION_TDB1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 08-APR-11

Starting Control File and SPFILE Autobackup at 08-APR-11
piece handle=/u01/ARBPRDDB/app/oracle/product/1020/dbs/cfauTDB1_c-2538883184-20110408-03.f comment=NONE
Finished Control File and SPFILE Autobackup at 08-APR-11


RMAN>recover copy of database with tag 'ORA_ASM_MIGRATION_TDB1';

create spfile='+DISK/spfileSTGTDB1.ora' from pfile='/u01/ARBPRDDB/app/oracle/product/11.2.0/dbhome_1/dbs/initSTGTDB1.ora;

RMAN> run {
   BACKUP AS BACKUPSET SPFILE;
   RESTORE SPFILE TO '+DATA/SPFILE';
}2> 3> 4>

Starting backup at 08-APR-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=396 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 08-APR-11
channel ORA_DISK_1: finished piece 1 at 08-APR-11
piece handle=+TDB1_FRA/stgtdb1a/backupset/2011_04_08/nnsnf0_tag20110408t180004_0.262.747943205 tag=TAG20110408T180004 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 08-APR-11

Starting Control File and SPFILE Autobackup at 08-APR-11
piece handle=/u01/ARBPRDDB/app/oracle/product/1020/dbs/cfauTDB1_c-2538883184-20110408-05.f comment=NONE
Finished Control File and SPFILE Autobackup at 08-APR-11

Starting restore at 08-APR-11
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: restoring SPFILE
output file name=+DATA/SPFILE
channel ORA_DISK_1: reading from backup piece /u01/ARBPRDDB/app/oracle/product/1020/dbs/cfauTDB1_c-2538883184-20110408-05.f
channel ORA_DISK_1: piece handle=/u01/ARBPRDDB/app/oracle/product/1020/dbs/cfauTDB1_c-2538883184-20110408-05.f tag=TAG20110408T180008
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 08-APR-11

or

create spfile='+DATA/spfileSTGTDB1.ora' from pfile='/u01/ARBPRDDB/app/oracle/product/11.2.0/dbhome_1/dbs/initSTGTDB1.ora';



RMAN> SHUTDOWN IMMEDIATE;

database closed
database dismounted
Oracle instance shut down

RMAN> STARTUP MOUNT;

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area    5344731136 bytes

Fixed Size                     2216200 bytes
Variable Size               4244639480 bytes
Database Buffers            1073741824 bytes
Redo Buffers                  24133632 bytes

RMAN> BACKUP AS COPY CURRENT CONTROLFILE FORMAT '/backup/STGTDB1/pre-ASM-controfile.ctl';

Starting backup at 08-APR-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1554 device type=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/backup/STGTDB1/pre-ASM-controfile.ctl tag=TAG20110408T180554 RECID=587 STAMP=747943555
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 08-APR-11

Starting Control File and SPFILE Autobackup at 08-APR-11
piece handle=/u01/ARBPRDDB/app/oracle/product/1020/dbs/cfauTDB1_c-2538883184-20110408-06.f comment=NONE
Finished Control File and SPFILE Autobackup at 08-APR-11


RMAN> SHUTDOWN IMMEDIATE;

database dismounted
Oracle instance shut down

RMAN> exit


Recovery Manager complete.
you have mail in /usr/spool/mail/oracle
RMAN> exit
Recovery Manager complete.
you have mail in /usr/spool/mail/oracle
va-iperf01:STGTDB1:/u01/ARBPRDDB/home/oracle>cd $ORACLE_HOME/dbs
va-iperf01:STGTDB1:/u01/ARBPRDDB/app/oracle/product/11.2.0/dbhome_1/dbs>ls pfile*.ora
pfilerat1.ora  pfiletdb1.ora
va-iperf01:STGTDB1:/u01/ARBPRDDB/app/oracle/product/11.2.0/dbhome_1/dbs>cat pfiletdb1.ora
spfile='+DATA/spfileSTGTDB1.ora'
va-iperf01:STGTDB1:/u01/ARBPRDDB/app/oracle/product/11.2.0/dbhome_1/dbs>

va-iperf01:STGRAT1:/u01/ARBPRDDB/app/oracle/product/11.2.0/dbhome_1/dbs>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 7 21:24:48 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SYS@STGRAT1  SQL>


SYS@STGTDB1  SQL>startup nomount PFILE='/u01/ARBPRDDB/app/oracle/product/11.2.0/dbhome_1/dbs/pfiletdb1.ora';
ORACLE instance started.

Total System Global Area 5344731136 bytes
Fixed Size                  2216200 bytes
Variable Size            4244639480 bytes
Database Buffers         1073741824 bytes
Redo Buffers               24133632 bytes
SYS@STGTDB1  SQL>alter system set control_files='+DATA/tdb1_ctl01.ctl','+TDB1_FRA/tdb1_ctl02.ctl' scope=spfile sid='*';

System altered.


SYS@STGRAT1  SQL>alter system set DB_RECOVERY_FILE_DEST_SIZE=100G SID='STGTDB1';

System altered.

SYS@STGRAT1  SQL>alter system set DB_RECOVERY_FILE_DEST='+TDB1_FRA' SID='STGTDB1';

System altered.

SYS@STGRAT1  SQL>shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SYS@STGRAT1  SQL>

SYS@STGTDB1  SQL>startup nomount PFILE='/u01/ARBPRDDB/app/oracle/product/11.2.0/dbhome_1/dbs/pfiletdb1.ora';
ORACLE instance started.

Total System Global Area 5344731136 bytes
Fixed Size                  2216200 bytes
Variable Size            4244639480 bytes
Database Buffers         1073741824 bytes
Redo Buffers               24133632 bytes
SYS@STGTDB1  SQL>

SYS@STGRAT1  SQL>exit

va-iperf01:STGTDB1:/u01/ARBPRDDB/app/oracle/product/11.2.0/dbhome_1/dbs>rman target / catalog rman/rman@rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Apr 8 18:13:47 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: STGTDB1 (not mounted)
connected to recovery catalog database

RMAN>


RMAN> restore controlfile from '/u01/ARBPRDDB/app/oracle/product/1020/dbs/cfauTDB1_c-2538883184-20110408-06.f';

Starting restore at 08-APR-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2330 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
output file name=+DATA/tdb1_ctl01.ctl
output file name=+TDB1_FRA/tdb1_ctl02.ctl
Finished restore at 08-APR-11

RMAN>


RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1


RMAN> switch database to copy;

Starting implicit crosscheck backup at 08-APR-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2330 device type=DISK
Crosschecked 73 objects
Finished implicit crosscheck backup at 08-APR-11

Starting implicit crosscheck copy at 08-APR-11
using channel ORA_DISK_1
Crosschecked 294 objects
Finished implicit crosscheck copy at 08-APR-11

searching for all files in the recovery area
cataloging files...
no files cataloged

datafile 1 switched to datafile copy "+DATA/stgtdb1a/datafile/system.310.747883701"
datafile 2 switched to datafile copy "+DATA/stgtdb1a/datafile/undotbs1.351.747880911"
datafile 3 switched to datafile copy "+DATA/stgtdb1a/datafile/undotbs1.350.747881237"
datafile 4 switched to datafile copy "+DATA/stgtdb1a/datafile/audit_ts.278.747883973"
datafile 5 switched to datafile copy "+DATA/stgtdb1a/datafile/drsys.277.747883971"
datafile 6 switched to datafile copy "+DATA/stgtdb1a/datafile/indx.276.747883971"
datafile 7 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_axcessrate_data.292.747883865"
datafile 8 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_tcdraggbase_day_index.325.747883203"
datafile 9 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_tcdragg_index.385.747876395"
datafile 10 switched to datafile copy "+DATA/stgtdb1a/datafile/xdb.265.747883957"
datafile 11 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_axcessrate_index.275.747883971"
datafile 12 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_axcesscode_data.291.747883869"
datafile 13 switched to datafile copy "+DATA/stgtdb1a/datafile/summary_data.384.747876549"
datafile 14 switched to datafile copy "+DATA/stgtdb1a/datafile/summary_idx.371.747878457"
datafile 15 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_arbinet_data.370.747878573"
datafile 16 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_arbinet_data.369.747878669"
datafile 17 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_arbinet_data.368.747878735"
datafile 18 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_arbinet_index.356.747880339"
datafile 19 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_arbinet_index.354.747880655"
datafile 20 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_arbinet_index.355.747880655"
datafile 21 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_arbinet_index.352.747880755"
datafile 22 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_arbinet_index.353.747880731"
datafile 23 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_arbinet_index.349.747881245"
datafile 24 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_menu_data.282.747883923"
datafile 25 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_menu_index.280.747883923"
datafile 26 switched to datafile copy "+DATA/stgtdb1a/datafile/developers.285.747883921"
datafile 27 switched to datafile copy "+DATA/stgtdb1a/datafile/users.293.747883865"
datafile 28 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_rro_data.305.747883761"
datafile 29 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_rro_idx.281.747883939"
datafile 30 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_switchaxcess_data.303.747883777"
datafile 31 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_switchaxcess_data.302.747883779"
datafile 32 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_switchaxcess_index.312.747883623"
datafile 33 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_cat_idx.397.747873483"
datafile 34 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_arbinet_data.383.747876765"
datafile 35 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_cat_data.400.747873483"
datafile 36 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_arbinet_data.382.747876765"
datafile 37 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_cat_data.388.747875995"
datafile 38 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_cat_idx.387.747876003"
datafile 39 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_auditlog_data.344.747881787"
datafile 40 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_cat_idx.386.747876379"
datafile 41 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_tam_index.316.747883475"
datafile 42 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_tam_data.284.747883921"
datafile 43 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_auditlog_data.337.747882281"
datafile 44 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_arbinet_data.381.747877101"
datafile 45 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_arbinet_index.270.747883967"
datafile 46 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_arbinet_index.271.747883969"
datafile 47 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_cat_data.395.747874229"
datafile 48 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_tcdraggbase_data.380.747877137"
datafile 49 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_tcdraggbase_data.318.747883451"
datafile 50 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_cat_idx.335.747882633"
datafile 51 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_cat_data_arch.295.747883849"
datafile 52 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_cat_idx_arch.301.747883779"
datafile 53 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_cat_data_arch.294.747883849"
datafile 54 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_cat_data_arch.300.747883805"
datafile 55 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_cat_data_arch.299.747883805"
datafile 56 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_cat_data_arch.296.747883835"
datafile 57 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_cat_idx_arch.307.747883715"
datafile 58 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_cat_idx_arch.308.747883715"
datafile 59 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_cat_idx_arch.306.747883715"
datafile 60 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_cat_data.394.747874255"
datafile 61 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_cat_idx.319.747883437"
datafile 62 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_cat_data.323.747883235"
datafile 63 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_cat_data.322.747883251"
datafile 64 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_cat_idx.298.747883831"
datafile 65 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_cat_idx.297.747883831"
datafile 92 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_pex_data.257.747883939"
datafile 102 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_arbinet_data.367.747878829"
datafile 128 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_registrar_data.258.747883939"
datafile 129 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_auditlog_data.366.747879035"
datafile 131 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_pex_index.259.747883939"
datafile 158 switched to datafile copy "+DATA/stgtdb1a/datafile/tts_tswamswcdr_data.365.747879281"
datafile 167 switched to datafile copy "+DATA/stgtdb1a/datafile/tts_tdrmetrics_data.290.747883895"
datafile 170 switched to datafile copy "+DATA/stgtdb1a/datafile/summary_data.358.747880115"
datafile 172 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_ipx_data.364.747879367"
datafile 173 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_ipx_index.326.747883017"
datafile 174 switched to datafile copy "+DATA/stgtdb1a/datafile/perfstat_ts.256.747883973"
datafile 188 switched to datafile copy "+DATA/stgtdb1a/datafile/tts_tdrmetrics_index.287.747883895"
datafile 202 switched to datafile copy "+DATA/stgtdb1a/datafile/tts_tswamswcdr_data.363.747879441"
datafile 212 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_registrar_index.263.747883955"
datafile 214 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_quartz.260.747883941"
datafile 217 switched to datafile copy "+DATA/stgtdb1a/datafile/sysaux.334.747882633"
datafile 222 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_arbinet_data.362.747879507"
datafile 229 switched to datafile copy "+DATA/stgtdb1a/datafile/tts_tcdraggrteinfo_data.266.747883965"
datafile 232 switched to datafile copy "+DATA/stgtdb1a/datafile/perfstat.289.747883895"
datafile 233 switched to datafile copy "+DATA/stgtdb1a/datafile/tools.398.747873483"
datafile 241 switched to datafile copy "+DATA/stgtdb1a/datafile/tts_tcdrcspagg_data.339.747882271"
datafile 247 switched to datafile copy "+DATA/stgtdb1a/datafile/tts_tcdraggrteinfo_index.267.747883965"
datafile 249 switched to datafile copy "+DATA/stgtdb1a/datafile/tts_tcdrcspagg_index.345.747881787"
datafile 254 switched to datafile copy "+DATA/stgtdb1a/datafile/tts_tswamswcdr_index.361.747879645"
datafile 275 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_dmx_data.288.747883895"
datafile 276 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_dmx_index.268.747883965"
datafile 405 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_backup_data.272.747883969"
datafile 406 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_backup_data.269.747883965"
datafile 407 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_ipx_data.341.747881923"
datafile 408 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_tcdraggbase_data.393.747874429"
datafile 409 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_tcdraggbase_index.379.747877213"
datafile 410 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_tcdragg_data.315.747883601"
datafile 411 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_tcdragg_index.317.747883459"
datafile 412 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_arbinet_data.360.747879979"
datafile 413 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_tcdragg_index.378.747877437"
datafile 414 switched to datafile copy "+DATA/stgtdb1a/datafile/summary_idx.309.747883701"
datafile 415 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_tcdragg_data.347.747881335"
datafile 416 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_cat_data.392.747875007"
datafile 417 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_cat_idx.391.747875007"
datafile 418 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_arbinet_data.377.747877463"
datafile 419 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_tcdraggbase_index.376.747877759"
datafile 420 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_tam_data.313.747883615"
datafile 421 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_tam_index.340.747882271"
datafile 422 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_trptotrs_data.331.747882671"
datafile 423 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_trptotrs_index.324.747883209"
datafile 424 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_tcdraggbase_day_data.375.747877855"
datafile 425 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_tcdraggbase_day_data.374.747877969"
datafile 426 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_tcdraggbase_day_index.348.747881321"
datafile 427 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_tcdragg_index.286.747883897"
datafile 428 switched to datafile copy "+DATA/stgtdb1a/datafile/tts_tswamswcdr_index.338.747882277"
datafile 429 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_tcdraggbase_day_index.359.747879979"
datafile 430 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_tcdraggbase_day_data.332.747882671"
datafile 431 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_marketview_data.261.747883955"
datafile 432 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_marketview_index.273.747883971"
datafile 433 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_tcdragg_data.373.747878045"
datafile 434 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_config_data.342.747881869"
datafile 435 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_config_index.311.747883623"
datafile 436 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_mkview_data.304.747883761"
datafile 437 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_mkview_index.264.747883957"
datafile 438 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_em_data.274.747883971"
datafile 439 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_cs_data.399.747873483"
datafile 440 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_cs_index.320.747883421"
datafile 441 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_report_data.396.747873483"
datafile 442 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_report_index.283.747883923"
datafile 443 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_axcesscode_index.262.747883955"
datafile 444 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_tcdraggbase_index.372.747878161"
datafile 445 switched to datafile copy "+DATA/stgtdb1a/datafile/tts_ipx_data.336.747882327"
datafile 446 switched to datafile copy "+DATA/stgtdb1a/datafile/tts_ipx_index.321.747883255"
datafile 447 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_ipx_data.314.747883601"
datafile 448 switched to datafile copy "+DATA/stgtdb1a/datafile/summary_data.330.747882957"
datafile 449 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_fees_data.279.747883973"
datafile 450 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_arbinet_data.346.747881445"
datafile 451 switched to datafile copy "+DATA/stgtdb1a/datafile/tts_tswamswcdr_data.357.747880123"
datafile 452 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_tcdragg_data.333.747882635"
datafile 453 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_tcdragg_data.329.747882965"
datafile 454 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_tcdraggbase_data.390.747875133"
datafile 455 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_tcdragg_index.327.747883001"
datafile 456 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_tcdraggbase_index.343.747881833"
datafile 457 switched to datafile copy "+DATA/stgtdb1a/datafile/summary_idx.328.747883001"
datafile 458 switched to datafile copy "+DATA/stgtdb1a/datafile/ts_cat_data.389.747875799"


RMAN> recover database;

Starting recover at 08-APR-11
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 582 is already on disk as file /u05/TDB1/oradata/redo_06.log
archived log file name=/u05/TDB1/oradata/redo_06.log thread=1 sequence=582
media recovery complete, elapsed time: 00:00:03
Finished recover at 08-APR-11

RMAN>


RMAN> run {
set newname for tempfile 1 to '+DATA';
set newname for tempfile 2 to '+DATA';
switch tempfile all;
}2> 3> 4> 5>

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to +DATA in control file
renamed tempfile 2 to +DATA in control file

RMAN>


RMAN> exit


Recovery Manager complete.
you have mail in /usr/spool/mail/oracle
va-iperf01:STGTDB1:/u01/ARBPRDDB/app/oracle/product/11.2.0/dbhome_1/dbs>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Apr 8 18:19:42 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options



SYS@STGTDB1  SQL>alter database open resetlogs;

Database altered.

SYS@STGTDB1  SQL>


SYS@STGTDB1  SQL>select * from admn.testasm;

LASTUPDAT
---------
07-APR-11
07-APR-11
07-APR-11
07-APR-11
08-APR-11
08-APR-11

6 rows selected.

SYS@STGTDB1  SQL>col MEMBER format a70
SYS@STGTDB1  SQL>set linesize 200

SYS@STGRAT1  SQL>select GROUP#,STATUS,MEMBER from V$LOGFILE order by GROUP#;


    GROUP# STATUS  MEMBER
---------- ------- ----------------------------------------------------------------------
         1         /u06/TDB1/oradata/redo_01.log
         1         /u05/TDB1/oradata/redo_01.log
         2         /u06/TDB1/oradata/redo_02.log
         2         /u05/TDB1/oradata/redo_02.log
         3         /u06/TDB1/oradata/redo_03.log
         3         /u05/TDB1/oradata/redo_03.log
         4         /u06/TDB1/oradata/redo_04.log
         4         /u05/TDB1/oradata/redo_04.log
         5         /u06/TDB1/oradata/redo_05.log
         5         /u05/TDB1/oradata/redo_05.log
         6         /u06/TDB1/oradata/redo_06.log
         6         /u05/TDB1/oradata/redo_06.log

12 rows selected.

SYS@STGTDB1  SQL>select BYTES/(1024*1024) from v$log;

BYTES/(1024*1024)
-----------------
              749
              749
              749
              749
              749
              749

6 rows selected.




ALTER DATABASE ADD LOGFILE ('+DATA/log1a.log', '+TDB1_FRA/log1b.log') SIZE 719M;
ALTER DATABASE ADD LOGFILE ('+DATA/log2a.log', '+TDB1_FRA/log2b.log') SIZE 719M;
ALTER DATABASE ADD LOGFILE ('+DATA/log3a.log', '+TDB1_FRA/log3b.log') SIZE 719M;
ALTER DATABASE ADD LOGFILE ('+DATA/log4a.log', '+TDB1_FRA/log4b.log') SIZE 719M;
ALTER DATABASE ADD LOGFILE ('+DATA/log5a.log', '+TDB1_FRA/log5b.log') SIZE 719M;
ALTER DATABASE ADD LOGFILE ('+DATA/log6a.log', '+TDB1_FRA/log6b.log') SIZE 719M;


SYS@STGTDB1  SQL> select GROUP#,STATUS,MEMBER from V$LOGFILE order by GROUP#
  2  ;

    GROUP# STATUS  MEMBER
---------- ------- ----------------------------------------------------------------------
         1         /u06/TDB1/oradata/redo_01.log
         1         /u05/TDB1/oradata/redo_01.log
         2         /u06/TDB1/oradata/redo_02.log
         2         /u05/TDB1/oradata/redo_02.log
         3         /u06/TDB1/oradata/redo_03.log
         3         /u05/TDB1/oradata/redo_03.log
         4         /u06/TDB1/oradata/redo_04.log
         4         /u05/TDB1/oradata/redo_04.log
         5         /u06/TDB1/oradata/redo_05.log
         5         /u05/TDB1/oradata/redo_05.log
         6         /u06/TDB1/oradata/redo_06.log

    GROUP# STATUS  MEMBER
---------- ------- ----------------------------------------------------------------------
         6         /u05/TDB1/oradata/redo_06.log
         7         +DATA/log1a.log
         7         +TDB1_FRA/log1b.log
         8         +DATA/log2a.log
         8         +TDB1_FRA/log2b.log
         9         +DATA/log3a.log
         9         +TDB1_FRA/log3b.log
        10         +DATA/log4a.log
        10         +TDB1_FRA/log4b.log
        11         +DATA/log5a.log
        11         +TDB1_FRA/log5b.log

    GROUP# STATUS  MEMBER
---------- ------- ----------------------------------------------------------------------
        12         +DATA/log6a.log
        12         +TDB1_FRA/log6b.log



SYS@STGTDB1  SQL>SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

    GROUP# ARC STATUS
---------- --- ----------------
         1 NO  CURRENT
         2 YES UNUSED
         3 YES UNUSED
         4 YES UNUSED
         5 YES UNUSED
         6 YES UNUSED
         7 YES UNUSED
         8 YES UNUSED
         9 YES UNUSED
        10 YES UNUSED
        11 YES UNUSED
        12 YES UNUSED

12 rows selected.

SYS@STGTDB1  SQL>alter system switch logfile;

System altered.

SYS@STGTDB1  SQL>/

System altered.

SYS@STGTDB1  SQL>/

System altered.

SYS@STGTDB1  SQL>/

System altered.

SYS@STGTDB1  SQL>/

System altered.

SYS@STGTDB1  SQL>/

System altered.

SYS@STGTDB1  SQL>





SYS@STGTDB1  SQL>SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

    GROUP# ARC STATUS
---------- --- ----------------
         1 YES INACTIVE
         2 YES INACTIVE
         3 YES INACTIVE
         4 YES INACTIVE
         5 YES INACTIVE
         6 YES INACTIVE
         7 NO  CURRENT
         8 YES UNUSED
         9 YES UNUSED
        10 YES UNUSED
        11 YES UNUSED

    GROUP# ARC STATUS
---------- --- ----------------
        12 YES UNUSED

12 rows selected.

SYS@STGTDB1  SQL>ALTER DATABASE DROP LOGFILE GROUP 1;

Database altered.

SYS@STGTDB1  SQL>ALTER DATABASE DROP LOGFILE GROUP 2;

Database altered.

SYS@STGTDB1  SQL>ALTER DATABASE DROP LOGFILE GROUP 3;

Database altered.

SYS@STGTDB1  SQL>ALTER DATABASE DROP LOGFILE GROUP 4;

Database altered.

SYS@STGTDB1  SQL>ALTER DATABASE DROP LOGFILE GROUP 5;

Database altered.

SYS@STGTDB1  SQL>ALTER DATABASE DROP LOGFILE GROUP 6;

Database altered.


SYS@STGTDB1  SQL>SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

    GROUP# ARC STATUS
---------- --- ----------------
         7 NO  CURRENT
         8 YES UNUSED
         9 YES UNUSED
        10 YES UNUSED
        11 YES UNUSED
        12 YES UNUSED

6 rows selected.

SYS@STGTDB1  SQL>



SYS@STGTDB1  SQL>select GROUP#,STATUS,MEMBER from V$LOGFILE order by GROUP#
  2  ;

    GROUP# STATUS  MEMBER
---------- ------- ----------------------------------------------------------------------
         7         +DATA/log1a.log
         7         +TDB1_FRA/log1b.log
         8         +DATA/log2a.log
         8         +TDB1_FRA/log2b.log
         9         +DATA/log3a.log
         9         +TDB1_FRA/log3b.log
        10         +DATA/log4a.log
        10         +TDB1_FRA/log4b.log
        11         +DATA/log5a.log
        11         +TDB1_FRA/log5b.log
        12         +DATA/log6a.log

    GROUP# STATUS  MEMBER
---------- ------- ----------------------------------------------------------------------
        12         +TDB1_FRA/log6b.log

12 rows selected.

SYS@STGTDB1  SQL>




 select group# grp, thread# thr, bytes/1024 bytes_k, 'NO' srl from v$log
 union
 select group# grp, thread# thr, bytes/1024 bytes_k, 'YES' srl from v$standby_log
 order by 1;



declare
   cursor rlc is
      select group# grp, thread# thr, bytes/1024 bytes_k, 'NO' srl
        from v$log
      union
      select group# grp, thread# thr, bytes/1024 bytes_k, 'YES' srl
        from v$standby_log
      order by 1;
   stmt     varchar2(2048);
   swtstmt  varchar2(1024) := 'alter system switch logfile';
   ckpstmt  varchar2(1024) := 'alter system checkpoint global';
begin
   for rlcRec in rlc loop
      if (rlcRec.srl = 'YES') then
         stmt := 'alter database add standby logfile thread ' ||
                 rlcRec.thr || ' ''+DISK'' size ' ||
                 rlcRec.bytes_k || 'K';
         execute immediate stmt;
         stmt := 'alter database drop standby logfile group ' || rlcRec.grp;
         execute immediate stmt;
      else
         stmt := 'alter database add  logfile thread ' ||
                 rlcRec.thr || ' size ' ||
                 rlcRec.bytes_k || 'K';
         execute immediate stmt;
         begin
            stmt := 'alter database drop logfile group ' || rlcRec.grp;
            dbms_output.put_line(stmt);
            execute immediate stmt;
         exception
            when others then
               execute immediate swtstmt;
               execute immediate ckpstmt;
               execute immediate stmt;
         end;
      end if;
   end loop;
end;

--***************************************************************************************************
Stgrat1 migration TO asm
--***************************************************************************************************

SYS@STGRAT1  SQL>select NAME,STATUS,BLOCK_SIZE,FILE_SIZE_BLKS from  v$controlfile;

NAME                                                                             STATUS  BLOCK_SIZE FILE_SIZE_BLKS
-------------------------------------------------------------------------------- ------- ---------- --------------
/u02/ARBPRDDB/oradata/arbi_ctl01.ctl                                                           8192           3984
/u03/ARBPRDDB/oradata/arbi_ctl02.ctl                                                           8192           3984
/u04/ARBPRDDB/oradata/arbi_ctl03.ctl                                                           8192           3984

SYS@STGRAT1  SQL>select GROUP#,MEMBER from v$logfile

    GROUP# MEMBER
---------- ----------------------------------------------------------------------------------------------------
         1 /u06/ARBPRDDB/oradata/redo_01.log
         1 /u05/ARBPRDDB/oradata/redo_01.log
         2 /u06/ARBPRDDB/oradata/redo_02.log
         2 /u05/ARBPRDDB/oradata/redo_02.log
         3 /u06/ARBPRDDB/oradata/redo_03.log
         3 /u05/ARBPRDDB/oradata/redo_03.log
         4 /u06/ARBPRDDB/oradata/redo_04.log
         4 /u05/ARBPRDDB/oradata/redo_04.log
         5 /u06/ARBPRDDB/oradata/redo_05.log
         5 /u05/ARBPRDDB/oradata/redo_05.log
         6 /u06/ARBPRDDB/oradata/redo_06.log
         6 /u05/ARBPRDDB/oradata/redo_06.log
         7 /u06/ARBPRDDB/oradata/STGRAT1_srl0.f
         8 /u06/ARBPRDDB/oradata/STGRAT1_srl1.f
         9 /u06/ARBPRDDB/oradata/STGRAT1_srl2.f
        10 /u06/ARBPRDDB/oradata/STGRAT1_srl3.f
        11 /u06/ARBPRDDB/oradata/STGRAT1_srl4.f
        12 /u06/ARBPRDDB/oradata/STGRAT1_srl5.f
        13 /u06/ARBPRDDB/oradata/STGRAT1_srl6.f

19 rows selected.

Starting Control File and SPFILE Autobackup at 07-APR-11
piece handle=/u01/ARBPRDDB/app/oracle/product/1020/dbs/cfauRAT1_c-4264422613-20110407-00.f comment=NONE


1.Create the ASM instance in ASM_HOME /mnt/u01/ARBPRDDB/app/oracle/product/11.2.0/grid.

2.Create ASM Disk groups (RAT1_DATA, RAT1_FRA)

3.Change ORACLE_HOME and SID to STGTDB1

4.Run the following command

$rman target / catalog rman/rman@rman

RMAN>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;
BACKUP AS COPY INCREMENTAL LEVEL 0
DATABASE FORMAT '+RAT1_DATA' 
TAG 'ORA_ASM_MIGRATION_RAT1';
 }      

RMAN>sql'alter system archive log current';

exit.

$sqlplus / as sysdba

SYS@STGRAT1  SQL>create spfile='+RAT1_DATA/spfileSTGRAT1.ora' from pfile='/u01/ARBPRDDB/app/oracle/product/11.2.0/dbhome_1/dbs/initSTGRAT1.ora';


**** Stop all application pointing to STGRAT1


SYS@STGRAT1  SQL>select to_char(sysdate,'mon-dd-yyyy hh:mi:ss') from dual;

TO_CHAR(SYSDATE,'MON
--------------------
apr-07-2011 09:00:26

SYS@STGRAT1  SQL>/

TO_CHAR(SYSDATE,'MON
--------------------
apr-07-2011 09:00:31

SYS@STGRAT1  SQL>commit;

Commit complete.

SYS@STGRAT1  SQL>select * from admn.testasm;

LASTUPDAT
---------
07-APR-11
07-APR-11
07-APR-11
07-APR-11
07-APR-11
07-APR-11
07-APR-11
07-APR-11
07-APR-11
07-APR-11

10 rows selected.

SYS@STGRAT1  SQL>select count(*) from admn.testasm;

  COUNT(*)
----------
        10


RMAN>backup  incremental level 1 for recover of copy with tag 'ORA_ASM_MIGRATION_RAT1' database ;

RMAN>recover copy of database with tag 'ORA_ASM_MIGRATION_RAT1';

run {
   BACKUP AS BACKUPSET SPFILE;
   RESTORE SPFILE TO "+RAT1_DATA/spfile";
}


RMAN> SHUTDOWN IMMEDIATE;

database closed
database dismounted
Oracle instance shut down


RMAN> STARTUP MOUNT;

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area    5344731136 bytes

Fixed Size                     2216200 bytes
Variable Size               4244639480 bytes
Database Buffers            1073741824 bytes
Redo Buffers                  24133632 bytes

RMAN>

RMAN> BACKUP AS COPY CURRENT CONTROLFILE FORMAT '/backup/STGRAT1/pre-ASM-controfile.ctl';

Starting backup at 07-APR-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2136 device type=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/backup/STGRAT1/pre-ASM-controfile.ctl tag=TAG20110407T211905 RECID=329 STAMP=747868746
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 07-APR-11

Starting Control File and SPFILE Autobackup at 07-APR-11
piece handle=/u01/ARBPRDDB/app/oracle/product/1020/dbs/cfauRAT1_c-4264422613-20110407-05.f comment=NONE
Finished Control File and SPFILE Autobackup at 07-APR-11

RMAN> SHUTDOWN IMMEDIATE;

database dismounted
Oracle instance shut down

RMAN> exit


Recovery Manager complete.
you have mail in /usr/spool/mail/oracle
va-iperf01:STGRAT1:/u01/ARBPRDDB/app/oracle/product/11.2.0/dbhome_1/dbs>
va-iperf01:STGRAT1:/u01/ARBPRDDB/app/oracle/product/11.2.0/dbhome_1/dbs>ls pfile*.ora
pfilerat1.ora
va-iperf01:STGRAT1:/u01/ARBPRDDB/app/oracle/product/11.2.0/dbhome_1/dbs>cat pfile*.ora
spfile='+RAT1_DATA/spfileSTGRAT1.ora'
va-iperf01:STGRAT1:/u01/ARBPRDDB/app/oracle/product/11.2.0/dbhome_1/dbs>

va-iperf01:STGRAT1:/u01/ARBPRDDB/app/oracle/product/11.2.0/dbhome_1/dbs>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 7 21:24:48 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SYS@STGRAT1  SQL>


SYS@STGRAT1  SQL>startup nomount PFILE='/u01/ARBPRDDB/app/oracle/product/11.2.0/dbhome_1/dbs/pfilerat1.ora';
ORACLE instance started.

Total System Global Area 5344731136 bytes
Fixed Size                  2216200 bytes
Variable Size            4244639480 bytes
Database Buffers         1073741824 bytes
Redo Buffers               24133632 bytes
SYS@STGRAT1  SQL>

SYS@STGRAT1  SQL>alter system set control_files='+RAT1_DATA/arbi_ctl01.ctl','+RAT1_FRA/arbi_ctl02.ctl' scope=spfile sid='*';

System altered.

SYS@STGRAT1  SQL>alter system set DB_RECOVERY_FILE_DEST_SIZE=100G SID='STGRAT1';

System altered.

SYS@STGRAT1  SQL>alter system set DB_RECOVERY_FILE_DEST='+RAT1_FRA' SID='STGRAT1';

System altered.

SYS@STGRAT1  SQL>shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SYS@STGRAT1  SQL>

SYS@STGRAT1  SQL>startup nomount PFILE='/u01/ARBPRDDB/app/oracle/product/11.2.0/dbhome_1/dbs/pfilerat1.ora';
ORACLE instance started.

Total System Global Area 5344731136 bytes
Fixed Size                  2216200 bytes
Variable Size            4244639480 bytes
Database Buffers         1073741824 bytes
Redo Buffers               24133632 bytes
SYS@STGRAT1  SQL>exit

va-iperf01:STGRAT1:/u01/ARBPRDDB/app/oracle/product/11.2.0/dbhome_1/dbs>rman target / catalog rman/rman@rman

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Apr 7 21:37:53 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: STGRAT1 (not mounted)
connected to recovery catalog database

RMAN>


RMAN> restore controlfile from '/u01/ARBPRDDB/app/oracle/product/1020/dbs/cfauRAT1_c-4264422613-20110407-05.f';

Starting restore at 07-APR-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2330 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
output file name=+RAT1_DATA/arbi_ctl01.ctl
output file name=+RAT1_FRA/arbi_ctl02.ctl
Finished restore at 07-APR-11

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> switch database to copy;

......................
.........................
datafile 188 switched to datafile copy "+RAT1_DATA/stgrat1a/datafile/tts_tsanteracdr_index.340.747857033"
datafile 192 switched to datafile copy "+RAT1_DATA/stgrat1a/datafile/ts_ucx_data.408.747861363"
datafile 198 switched to datafile copy "+RAT1_DATA/stgrat1a/datafile/ts_ucx_index.410.747861367"
datafile 201 switched to datafile copy "+RAT1_DATA/stgrat1a/datafile/report_ts.399.747861307"
datafile 333 switched to datafile copy "+RAT1_DATA/stgrat1a/datafile/sysaux.379.747860875"
datafile 349 switched to datafile copy "+RAT1_DATA/stgrat1a/datafile/ts_archiver_data.395.747861255"
datafile 350 switched to datafile copy "+RAT1_DATA/stgrat1a/datafile/ts_archiver_index.385.747861115"
datafile 351 switched to datafile copy "+RAT1_DATA/stgrat1a/datafile/tts_tcdr_index.341.747857341"
datafile 352 switched to datafile copy "+RAT1_DATA/stgrat1a/datafile/tts_tsanteracdr_index.342.747857517"
datafile 353 switched to datafile copy "+RAT1_DATA/stgrat1a/datafile/tts_tcdr_index.343.747857593"
datafile 354 switched to datafile copy "+RAT1_DATA/stgrat1a/datafile/tts_tsanteracdr_index.344.747857687"
datafile 355 switched to datafile copy "+RAT1_DATA/stgrat1a/datafile/tts_tcdr_index.345.747857725"
datafile 356 switched to datafile copy "+RAT1_DATA/stgrat1a/datafile/ts_scpcdr_data.406.747861361"
datafile 357 switched to datafile copy "+RAT1_DATA/stgrat1a/datafile/ts_scpcdr_idx.384.747861061"

RMAN> recover database;

Starting recover at 07-APR-11
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 20 is already on disk as file /u05/ARBPRDDB/oradata/redo_02.log
archived log file name=/u05/ARBPRDDB/oradata/redo_02.log thread=1 sequence=20
media recovery complete, elapsed time: 00:00:01
Finished recover at 07-APR-11

RMAN> run {
2> set newname for tempfile 1 to '+RAT1_DATA';
3>  set newname for tempfile 2 to '+RAT1_DATA';
4> switch tempfile all;
5> }

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 2 to +RAT1_DATA in control file

RMAN> exit


SYS@STGRAT1  SQL>alter database open resetlogs;

Database altered.

SYS@STGRAT1  SQL>select * from admn.testasm;

LASTUPDAT
---------
07-APR-11
07-APR-11
07-APR-11
07-APR-11
07-APR-11
07-APR-11
07-APR-11
07-APR-11
07-APR-11
07-APR-11

10 rows selected.

SYS@STGRAT1  SQL>




ALTER DATABASE ADD LOGFILE ('+RAT1_DATA/log1a.log', '+RAT1_FRA/log1b.log') SIZE 719M;
ALTER DATABASE ADD LOGFILE ('+RAT1_DATA/log2a.log', '+RAT1_FRA/log2b.log') SIZE 719M;
ALTER DATABASE ADD LOGFILE ('+RAT1_DATA/log3a.log', '+RAT1_FRA/log3b.log') SIZE 719M;
ALTER DATABASE ADD LOGFILE ('+RAT1_DATA/log4a.log', '+RAT1_FRA/log4b.log') SIZE 719M;
ALTER DATABASE ADD LOGFILE ('+RAT1_DATA/log5a.log', '+RAT1_FRA/log5b.log') SIZE 719M;
ALTER DATABASE ADD LOGFILE ('+RAT1_DATA/log6a.log', '+RAT1_FRA/log6b.log') SIZE 719M;




SYS@STGRAT1  SQL>alter system switch logfile;

System altered.

SYS@STGRAT1  SQL>/

System altered.

SYS@STGRAT1  SQL>/

System altered.

SYS@STGRAT1  SQL>/

System altered.

SYS@STGRAT1  SQL>/

System altered.

SYS@STGRAT1  SQL>/

System altered.

SYS@STGRAT1  SQL>/

System altered.

SYS@STGRAT1  SQL>


ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE DROP LOGFILE GROUP 3;
ALTER DATABASE DROP LOGFILE GROUP 4;
ALTER DATABASE DROP LOGFILE GROUP 5;
ALTER DATABASE DROP LOGFILE GROUP 6;

SYS@STGRAT1  SQL>select GROUP#,STATUS,MEMBER from V$LOGFILE order by GROUP#;

    GROUP# STATUS  MEMBER
---------- ------- --------------------------------------------------------------------------------
         7         /u06/ARBPRDDB/oradata/STGRAT1_srl0.f
         8         /u06/ARBPRDDB/oradata/STGRAT1_srl1.f
         9         /u06/ARBPRDDB/oradata/STGRAT1_srl2.f
        10         /u06/ARBPRDDB/oradata/STGRAT1_srl3.f
        11         /u06/ARBPRDDB/oradata/STGRAT1_srl4.f
        12         /u06/ARBPRDDB/oradata/STGRAT1_srl5.f
        13         /u06/ARBPRDDB/oradata/STGRAT1_srl6.f
        14         +RAT1_DATA/log1a.log
        14         +RAT1_FRA/log1b.log
        15         +RAT1_DATA/log2a.log
        15         +RAT1_FRA/log2b.log

    GROUP# STATUS  MEMBER
---------- ------- --------------------------------------------------------------------------------
        16         +RAT1_DATA/log3a.log
        16         +RAT1_FRA/log3b.log
        17         +RAT1_DATA/log4a.log
        17         +RAT1_FRA/log4b.log
        18         +RAT1_DATA/log5a.log
        18         +RAT1_FRA/log5b.log
        19         +RAT1_DATA/log6a.log
        19         +RAT1_FRA/log6b.log

19 rows selected.

SYS@STGRAT1  SQL>
SYS@STGRAT1  SQL>

SYS@STGRAT1  SQL>select group#,ARCHIVED,STATUS from v$standby_log;

    GROUP# ARC STATUS
---------- --- ----------
         7 YES UNASSIGNED
         8 YES UNASSIGNED
         9 YES UNASSIGNED
        10 YES UNASSIGNED
        11 YES UNASSIGNED
        12 YES UNASSIGNED
        13 YES UNASSIGNED

7 rows selected.

alter database drop standby logfile group 7;
alter database drop standby logfile group 8;
alter database drop standby logfile group 9;
alter database drop standby logfile group 10;
alter database drop standby logfile group 11;
alter database drop standby logfile group 12;
alter database drop standby logfile group 13;


SYS@STGRAT1  SQL>alter database drop standby logfile group 7;
alter database drop standby logfile group 8;
alter database drop standby logfile group 9;
alter database drop standby logfile group 10;
alter database drop standby logfile group 11;
alter database drop standby logfile group 12;
alter database drop standby logfile group 13;
Database altered.

SYS@STGRAT1  SQL>

SYS@STGRAT1  SQL>select GROUP#,STATUS,MEMBER from V$LOGFILE order by GROUP#;

    GROUP# STATUS  MEMBER
---------- ------- --------------------------------------------------------------------------------
        14         +RAT1_DATA/log1a.log
        14         +RAT1_FRA/log1b.log
        15         +RAT1_DATA/log2a.log
        15         +RAT1_FRA/log2b.log
        16         +RAT1_DATA/log3a.log
        16         +RAT1_FRA/log3b.log
        17         +RAT1_DATA/log4a.log
        17         +RAT1_FRA/log4b.log
        18         +RAT1_DATA/log5a.log
        18         +RAT1_FRA/log5b.log
        19         +RAT1_DATA/log6a.log

    GROUP# STATUS  MEMBER
---------- ------- --------------------------------------------------------------------------------
        19         +RAT1_FRA/log6b.log

12 rows selected.

Database altered.

SYS@STGRAT1  SQL>
Database altered.

SYS@STGRAT1  SQL>
Database altered.

SYS@STGRAT1  SQL>
Database altered.

SYS@STGRAT1  SQL>

Database altered.

SYS@STGRAT1  SQL>
SYS@STGRAT1  SQL>
SYS@STGRAT1  SQL>
SYS@STGRAT1  SQL>
SYS@STGRAT1  SQL>
SYS@STGRAT1  SQL>


















Comments