ASM QUICK REF


eng03:stgdb1:/dev $sudo /u01/app/oracle/product/11.2.0/grid/root.sh
Running Oracle 11g root.sh script...

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u01/app/oracle/product/11.2.0/grid

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The file "dbhome" already exists in /usr/local/bin.  Overwrite it? (y/n) [n]:
The file "oraenv" already exists in /usr/local/bin.  Overwrite it? (y/n) [n]:
The file "coraenv" already exists in /usr/local/bin.  Overwrite it? (y/n) [n]:

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
2011-05-06 15:22:23: Checking for super user privileges
2011-05-06 15:22:23: User has super user privileges
2011-05-06 15:22:23: Parsing the host name
Using configuration parameter file: /u01/app/oracle/product/11.2.0/grid/crs/install/crsconfig_params
Creating trace directory
LOCAL ADD MODE
Creating OCR keys for user 'oracle', privgrp 'dba'..
Operation successful.
CRS-4664: Node va-ieng03 successfully pinned.
Adding daemon to inittab
CRS-4123: Oracle High Availability Services has been started.
ohasd is starting

va-ieng03     2011/05/06 15:23:00     /u01/app/oracle/product/11.2.0/grid/cdata/va-ieng03/backup_20110506_152300.olr
Successfully configured Oracle Grid Infrastructure for a Standalone Server
Updating inventory properties for clusterware
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 16384 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
The inventory is located at /u01/app/oracle/oraInventory
'UpdateNodeList' was successful.
eng03:stgdb1:/dev $

Step by Step - Non ASM to ASM

posted Apr 26, 2011, 6:26 AM by Sachchida Ojha

--***************************************************************************************************
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>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 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>

**************************************************************************************************

**************************************************************************************************

**************************************************************************************************

**************************************************************************************************

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

NAME                                                             STATUS  BLOCK_SIZE FILE_SIZE_BLKS
---------------------------------------------------------------- ------- ---------- --------------
/u02/RPTDB/oradata/control01.ctl                                              16384            782
/u03/RPTDB/oradata/control02.ctl                                              16384            782
/u04/RPTDB/oradata/control03.ctl                                              16384            782

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

    GROUP# MEMBER
---------- ----------------------------------------------------------------
         5 /u05/RPTDB/oradata/log5a.log
         5 /u06/RPTDB/oradata/log5b.log
         4 /u05/RPTDB/oradata/log4a.log
         4 /u06/RPTDB/oradata/log4b.log
         3 /u05/RPTDB/oradata/log3a.log
         3 /u06/RPTDB/oradata/log3b.log
         2 /u05/RPTDB/oradata/log2a.log
         2 /u06/RPTDB/oradata/log2b.log
         1 /u05/RPTDB/oradata/log1a.log
         1 /u06/RPTDB/oradata/log1b.log

10 rows selected.



SQL> select FILE_NAME from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
/u03/RPTDB/oradata/temp02.dbf
/u03/RPTDB/oradata/temp01.dbf

SQL>




$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;
BACKUP AS COPY INCREMENTAL LEVEL 0
DATABASE FORMAT '+RPTDB_DATA' 
TAG 'ORA_ASM_MIGRATION_RPTDB0422';
 }      



SQL> create spfile='+RPTDB_DATA/spfileSTGRPTDB.ora' from pfile='/u01/app/oracle/product/10gR2/dbs/initRPTDB.ora';

File created.

SQL> create spfile='+RPTDB_DATA/spfileRPTDB.ora' from pfile='/u01/app/oracle/product/10gR2/dbs/initRPTDB.ora';

File created.

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

Table created.

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

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.


SQL> select * from admn.testasm;

LASTUPDAT
---------
25-APR-11
25-APR-11
25-APR-11
25-APR-11
19-APR-11
19-APR-11
19-APR-11
19-APR-11
19-APR-11
19-APR-11

10 rows selected.

SQL>




RMAN>sql'alter system archive log current';

exit.


SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

va-idb01:RPTDB:/u01/home/oracle $rman target / catalog rman/rman@rman


Recovery Manager: Release 10.2.0.4.0 - Production on Tue Apr 19 21:34:35 2011

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

connected to target database: RPTDB (DBID=4032003294)
connected to recovery catalog database

RMAN> backup  incremental level 1 for recover of copy with tag 'ORA_ASM_MIGRATION_RPTDB0422' format '/backup/RPTDB/level1_%d_bk_%s_%p_%t' database ;

Starting backup at 19-APR-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1062 devtype=DISK
channel ORA_DISK_1: starting incremental level 1 datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00006 name=/u07/RPTDB/oradata/ts_cdr_view_dat_02.dbf
input datafile fno=00019 name=/u07/RPTDB/oradata/ts_cdr_view_idx_03.dbf
input datafile fno=00044 name=/u09/RPTDB/oradata/ts_scpcdragg_data09.dbf
input datafile fno=00022 name=/u10/RPTDB/oradata/ts_voipdm_data01.dbf
input datafile fno=00031 name=/u10/RPTDB/oradata/ts_voipdm_data02.dbf
input datafile fno=00035 name=/u09/RPTDB/oradata/ts_scpcdragg_data06.dbf
input datafile fno=00037 name=/u09/RPTDB/oradata/ts_scpcdragg_data07.dbf
input datafile fno=00042 name=/u09/RPTDB/oradata/ts_scpcdragg_idx_10.dbf
input datafile fno=00049 name=/u09/RPTDB/oradata/ts_scpcdragg_data10.dbf
input datafile fno=00054 name=/u09/RPTDB/oradata/ts_scpcdragg_dat12.dbf
input datafile fno=00056 name=/u09/RPTDB/oradata/ts_scpcdragg_dat13.dbf
input datafile fno=00061 name=/u09/RPTDB/oradata/ts_scpcdragg_idx_18.dbf
input datafile fno=00026 name=/u09/RPTDB/oradata/ts_scpcdragg_idx_04.dbf
input datafile fno=00050 name=/u09/RPTDB/oradata/ts_scpcdragg_idx_14.dbf
input datafile fno=00007 name=/u07/RPTDB/oradata/ts_cdr_view_idx_01.dbf
input datafile fno=00014 name=/u12/RPTDB/oradata/ts_scpcdr_dat01.dbf
input datafile fno=00025 name=/u09/RPTDB/oradata/ts_scpcdragg_idx_03.dbf
input datafile fno=00033 name=/u09/RPTDB/oradata/ts_scpcdragg_idx_06.dbf
input datafile fno=00039 name=/u09/RPTDB/oradata/ts_scpcdragg_idx_09.dbf
input datafile fno=00048 name=/u09/RPTDB/oradata/ts_scpcdragg_idx_13.dbf
input datafile fno=00021 name=/u09/RPTDB/oradata/ts_scpcdragg_idx_02.dbf
input datafile fno=00051 name=/u09/RPTDB/oradata/ts_scpcdragg_idx_15.dbf
input datafile fno=00085 name=/u07/RPTDB/oradata/ts_voipdm_data04.dbf
input datafile fno=00068 name=/u09/RPTDB/oradata/ts_scpcdragg_dat16.dbf
input datafile fno=00078 name=/u07/RPTDB/oradata/ts_scpcdragg_dat24.dbf
input datafile fno=00046 name=/u10/RPTDB/oradata/ts_voipdm_data03.dbf
input datafile fno=00079 name=/u07/RPTDB/oradata/ts_scpcdragg_dat23.dbf
input datafile fno=00083 name=/u07/RPTDB/oradata/ts_scpcdragg_idx_28.dbf
input datafile fno=00074 name=/u09/RPTDB/oradata/ts_scpcdragg_idx_21.dbf
input datafile fno=00053 name=/u07/RPTDB/oradata/undotbs1-02.dbf
input datafile fno=00090 name=/u07/RPTDB/oradata/ts_cdr_view_dat_05.dbf
input datafile fno=00091 name=/u07/RPTDB/oradata/ts_cdr_view_dat_06.dbf
input datafile fno=00064 name=/u04/RPTDB/oradata/undotbs1-03.dbf
input datafile fno=00081 name=/u07/RPTDB/oradata/ts_scpcdragg_dat21.dbf
input datafile fno=00084 name=/u07/RPTDB/oradata/ts_scpcdragg_idx_27.dbf
input datafile fno=00015 name=/u12/RPTDB/oradata/ts_scpcdr_idx_01.dbf
input datafile fno=00076 name=/u09/RPTDB/oradata/ts_scpcdragg_idx_24.dbf
input datafile fno=00017 name=/u11/RPTDB/oradata/ts_nolog_idx_01.dbf
input datafile fno=00003 name=/u03/RPTDB/oradata/sysaux01.dbf
input datafile fno=00028 name=/u09/RPTDB/oradata/ts_summ_data01.dbf
input datafile fno=00089 name=/u07/RPTDB/oradata/ts_scpcdragg_idx_30.dbf
input datafile fno=00093 name=/u12/RPTDB/oradata/ts_scpcdr_dat02.dbf
input datafile fno=00004 name=/u08/RPTDB/oradata/users01.dbf
input datafile fno=00012 name=/u09/RPTDB/oradata/ts_utils_data01.dbf
input datafile fno=00058 name=/u09/RPTDB/oradata/ts_utils_data02.dbf
input datafile fno=00087 name=/u07/RPTDB/oradata/ts_summ_data03.dbf
channel ORA_DISK_1: starting piece 1 at 19-APR-11


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




RMAN> run {
   BACKUP AS BACKUPSET SPFILE;
   RESTORE SPFILE TO "+RPTDB_DATA/spfile";
}2> 3> 4>

Starting backup at 25-APR-11
using channel ORA_DISK_1
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 25-APR-11
channel ORA_DISK_1: finished piece 1 at 25-APR-11
piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/2kmam145_1_1 tag=TAG20110425T160837 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 25-APR-11

Starting Control File and SPFILE Autobackup at 25-APR-11
piece handle=/backup/RPTDB/cfauRPTDB_c-4032003294-20110425-02.f comment=NONE
Finished Control File and SPFILE Autobackup at 25-APR-11

Starting restore at 25-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=+RPTDB_DATA/spfile
channel ORA_DISK_1: reading from backup piece /backup/RPTDB/cfauRPTDB_c-4032003294-20110425-02.f
channel ORA_DISK_1: piece handle=/backup/RPTDB/cfauRPTDB_c-4032003294-20110425-02.f tag=TAG20110425T160840
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 25-APR-11

RMAN>



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/RPTDB/pre-ASM-controfile.ctl';

Starting backup at 25-APR-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1302 device type=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/backup/RPTDB/pre-ASM-controfile.ctl tag=TAG20110425T161540 RECID=566 STAMP=749405741
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 25-APR-11

Starting Control File and SPFILE Autobackup at 25-APR-11
piece handle=/backup/RPTDB/cfauRPTDB_c-4032003294-20110425-03.f comment=NONE
Finished Control File and SPFILE Autobackup at 25-APR-11

RMAN>

RMAN> SHUTDOWN IMMEDIATE;

database dismounted
Oracle instance shut down

RMAN> exit


Recovery Manager complete.

va-idb01:RPTDB:/u01/home/oracle $cd $ORACLE_HOME/dbs

va-idb01:RPTDB:/u01/app/oracle/product/11.2.0/dbhome_1/dbs $ls pfile*.ora
pfilerptdb.ora

va-idb01:RPTDB:/u01/app/oracle/product/11.2.0/dbhome_1/dbs $cat pfilerptdb.ora
spfile='+RPTDB_DATA/spfileRPTDB.ora'

va-idb01:RPTDB:/u01/app/oracle/product/11.2.0/dbhome_1/dbs $sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 25 16:17:03 2011

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

Connected to an idle instance.

SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/pfilerptdb.ora';
ORACLE instance started.

Total System Global Area 5745586176 bytes
Fixed Size                  2217160 bytes
Variable Size            1308625720 bytes
Database Buffers         4429185024 bytes
Redo Buffers                5558272 bytes
SQL>


SYS@STGRAT1  SQL>alter system set control_files='+RPTDB_DATA/rptdb_ctl01.ctl','+RPTDB_FRA/rptdb_ctl02.ctl' scope=spfile sid='*';

System altered.

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

System altered.

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

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 '/backup/RPTDB/cfauRPTDB_c-4032003294-20110425-03.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 {
set newname for tempfile 1 to '+RPTDB_DATA';
set newname for tempfile 2 to '+RPTDB_DATA';
switch tempfile all;
}

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 2 to +RPTDB_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.


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


SQL> select GROUP#,MEMBERS,STATUS from v$log;

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
         1          2 ACTIVE
         2          2 CURRENT
         3          2 INACTIVE
         4          2 INACTIVE
         5          2 ACTIVE

SQL>


SYS@STGRAT1  SQL>




ALTER DATABASE ADD LOGFILE ('+RPTDB_DATA/rptdblog1a.log', '+RPTDB_FRA/rptdblog1b.log') SIZE 719M;
ALTER DATABASE ADD LOGFILE ('+RPTDB_DATA/rptdblog2a.log', '+RPTDB_FRA/rptdblog2b.log') SIZE 719M;
ALTER DATABASE ADD LOGFILE ('+RPTDB_DATA/rptdblog3a.log', '+RPTDB_FRA/rptdblog3b.log') SIZE 719M;
ALTER DATABASE ADD LOGFILE ('+RPTDB_DATA/rptdblog4a.log', '+RPTDB_FRA/rptdblog4b.log') SIZE 719M;
ALTER DATABASE ADD LOGFILE ('+RPTDB_DATA/rptdblog5a.log', '+RPTDB_FRA/rptdblog5b.log') SIZE 719M;
ALTER DATABASE ADD LOGFILE ('+RPTDB_DATA/rptdblog6a.log', '+RPTDB_FRA/rptdblog6b.log') SIZE 719M;

[INS-20802] Automatic storage Management Configuration Failed.

posted Apr 14, 2011, 12:42 PM by Sachchida Ojha

Cause - The plug-in failed in its perform method.

Action: Refer to the logs or contact oracle Services.

Log File Location

/u01/app/oracle/oraInventory/logs/installActions2011-04-14_07-04-32PM.log

va-idb01:RPTDB:/u01/app/oracle/oraInventory/logs $ls installActions2011-04-14_07-04-32PM.log
installActions2011-04-14_07-04-32PM.log

va-idb01:RPTDB:/u01/app/oracle/oraInventory/logs $

INFO: Read: SYS_PASSWORD_PROMPT
INFO: Processing: SYS_PASSWORD_PROMPT for argument tag -sysAsmPassword
INFO: Read: ASMSNMP_PASSWORD_PROMPT
INFO: Processing: ASMSNMP_PASSWORD_PROMPT for argument tag -asmMonitorPassword
INFO: End of argument passing to stdin
INFO: Read:
INFO: Read: Configuring ASM failed with the following message:
INFO: Read: Configuring HA resource failed. The following error occured:
INFO: Read: PRCR-1079 : Failed to start resource ora.asm
INFO: Read: ORA-00119: invalid specification for system parameter %s
INFO: Read: CRS-2674: Start of 'ora.asm' on 'va-idb01' failed
INFO: Read:
INFO: Completed Plugin named: Automatic Storage Management Configuration Assistant

Improper Oracle Clusterware configuration found on this host

posted Apr 14, 2011, 12:33 PM by Sachchida Ojha   [ updated Apr 14, 2011, 12:36 PM ]

va-idb01:RPTDB:/u01/home/oracle $sudo /u01/app/oracle/product/11.2.0/grid/root.sh
Running Oracle 11g root.sh script...

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u01/app/oracle/product/11.2.0/grid

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The file "dbhome" already exists in /usr/local/bin.  Overwrite it? (y/n) [n]:
The file "oraenv" already exists in /usr/local/bin.  Overwrite it? (y/n) [n]:
The file "coraenv" already exists in /usr/local/bin.  Overwrite it? (y/n) [n]:

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
2011-04-14 19:25:59: Checking for super user privileges
2011-04-14 19:25:59: User has super user privileges
2011-04-14 19:25:59: Parsing the host name
Using configuration parameter file: /u01/app/oracle/product/11.2.0/grid/crs/install/crsconfig_params
Creating trace directory
Improper Oracle Clusterware configuration found on this host
Deconfigure the existing cluster configuration before starting
to configure a new Clusterware
run '/u01/app/oracle/product/11.2.0/grid/crs/install/rootcrs.pl -deconfig'
to configure existing failed configuration and then rerun root.sh

va-idb01:RPTDB:/u01/home/oracle $/u01/app/oracle/product/11.2.0/grid/crs/install/rootcrs.pl -deconfig
2011-04-14 19:26:53: Parsing the host name
2011-04-14 19:26:53: Checking for super user privileges
You must be logged in as root to run this script.
Log in as root and rerun this script.
2011-04-14 19:26:53: Not running as authorized user
Insufficient privileges to execute this script

va-idb01:RPTDB:/u01/home/oracle $

From Root user

va-idb01:RPTDB:/u01/home/oracle $/u01/app/oracle/product/11.2.0/grid/crs/install/rootcrs.pl -deconfig
2011-04-14 19:27:08: Parsing the host name
2011-04-14 19:27:08: Checking for super user privileges
2011-04-14 19:27:08: User has super user privileges
Using configuration parameter file: /u01/app/oracle/product/11.2.0/grid/crs/install/crsconfig_params
Failure to execute: No such file or directory for command /u01/app/oracle/product/11.2.0/grid/bin/crsctl check cluster -n va-idb01
Failure to execute: No such file or directory for command /u01/app/oracle/product/11.2.0/grid/bin/crsctl check cluster -n va-idb01
Oracle Clusterware stack is not active on this node
Restart the clusterware stack (use /u01/app/oracle/product/11.2.0/grid/bin/crsctl start crs) and retry
Failed to verify resources

va-idb01:RPTDB:/u01/home/oracle $/u01/app/oracle/product/11.2.0/grid/crs/install/rootcrs.pl -deconfig -force
2011-04-14 19:31:17: Parsing the host name
2011-04-14 19:31:17: Checking for super user privileges
2011-04-14 19:31:17: User has super user privileges
Using configuration parameter file: /u01/app/oracle/product/11.2.0/grid/crs/install/crsconfig_params
Failure to execute: No such file or directory for command /u01/app/oracle/product/11.2.0/grid/bin/crsctl check cluster -n va-idb01
Failure to execute: No such file or directory for command /u01/app/oracle/product/11.2.0/grid/bin/crsctl check cluster -n va-idb01
Usage: srvctl <command> <object> [<options>]
    commands: enable|disable|start|stop|status|add|remove|modify|getenv|setenv|unsetenv|config
    objects: database|service|asm|diskgroup|listener|home|ons|eons
For detailed help on each command and object and its options use:
  srvctl <command> -h or
  srvctl <command> <object> -h
PRKO-2012 : nodeapps object is not supported in Oracle Restart
Failure to execute: No such file or directory for command /u01/app/oracle/product/11.2.0/grid/bin/crsctl check cluster -n va-idb01
You must kill crs processes or reboot the system to properly
cleanup the processes started by Oracle clusterware
2560+0 records in
2560+0 records out
Successfully deconfigured Oracle clusterware stack on this node

va-idb01:RPTDB:/u01/home/oracle $


va-idb01:RPTDB:/u01/home/oracle $sudo /u01/app/oracle/product/11.2.0/grid/root.sh
Password:
Running Oracle 11g root.sh script...

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u01/app/oracle/product/11.2.0/grid

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The file "dbhome" already exists in /usr/local/bin.  Overwrite it? (y/n) [n]:
The file "oraenv" already exists in /usr/local/bin.  Overwrite it? (y/n) [n]:
The file "coraenv" already exists in /usr/local/bin.  Overwrite it? (y/n) [n]:

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
2011-04-14 19:35:01: Checking for super user privileges
2011-04-14 19:35:01: User has super user privileges
2011-04-14 19:35:01: Parsing the host name
Using configuration parameter file: /u01/app/oracle/product/11.2.0/grid/crs/install/crsconfig_params
LOCAL ADD MODE
Creating OCR keys for user 'oracle', privgrp 'oinstall'..
Operation successful.
CRS-4664: Node va-idb01 successfully pinned.
Adding daemon to inittab
CRS-4123: Oracle High Availability Services has been started.
ohasd is starting

va-idb01     2011/04/14 19:35:41     /u01/app/oracle/product/11.2.0/grid/cdata/va-idb01/backup_20110414_193541.olr
Successfully configured Oracle Grid Infrastructure for a Standalone Server
Updating inventory properties for clusterware
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 10240 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
The inventory is located at /u01/app/oracle/oraInventory
'UpdateNodeList' was successful.

va-idb01:RPTDB:/u01/home/oracle $



ORA-15001: diskgroup "TDB1_FLASH" does not exist or is not mounted

posted Apr 8, 2011, 1:03 AM by Sachchida Ojha

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 04/08/2011 02:16:15
ORA-19504: failed to create file "+TDB1_FLASH"
ORA-15001: diskgroup "TDB1_FLASH" does not exist or is not mounted


SYS@STGTDB1  SQL>show parameter recove

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      +TDB1_FLASH
db_recovery_file_dest_size           big integer 328G
recovery_parallelism                 integer     4
SYS@STGTDB1  SQL>alter system set db_recovery_file_dest_size=100G;

System altered.

SYS@STGTDB1  SQL>>alter system set db_recovery_file_dest=+TDB1_FRA;
SP2-0734: unknown command beginning ">alter sys..." - rest of line ignored.
SYS@STGTDB1  SQL>alter system set db_recovery_file_dest=+TDB1_FRA;
alter system set db_recovery_file_dest=+TDB1_FRA
                                        *
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM


SYS@STGTDB1  SQL>alter system set db_recovery_file_dest='+TDB1_FRA';

System altered.

SYS@STGTDB1  SQL>show parameter FLASH

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flash_cache_file                  string
db_flash_cache_size                  big integer 0
db_flashback_retention_target        integer     1440
SYS@STGTDB1  SQL>show parameter recove

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      +TDB1_FRA
db_recovery_file_dest_size           big integer 100G
recovery_parallelism                 integer     4
SYS@STGTDB1  SQL>alter system set db_recovery_file_dest_size=100G scope=spfile;

System altered.

SYS@STGTDB1  SQL>alter system set db_recovery_file_dest='+TDB1_FRA' scope=spfile;

System altered.

SYS@STGTDB1  SQL>create pfile from spfile;

File created.

SYS@STGTDB1  SQL>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@STGTDB1  SQL>
SYS@STGTDB1  SQL>startup ;
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
Database mounted.
Database opened.
SYS@STGTDB1  SQL>alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST' scope=spfile;

System altered.

SYS@STGTDB1  SQL>create pfile from spfile;

File created.

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

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Apr 8 08:02:05 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>

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>


















ASM Diskgroup Compatibility -- 11G

posted Mar 16, 2011, 8:57 AM by Sachchida Ojha   [ updated Mar 16, 2011, 9:12 AM ]

Every diskgroup in ASM has two compatibility attributes — compatible.asm and compatible.rdbms. In 10.2, the V$ASM_DISKGROUP view has a couple new columns added — COMPATIBILITY and DATABASE_COMPATIBILITY, but only in 11g did Oracle introduce the concept of diskgroup attributes and the V$ASM_ATTRIBUTE view. Thus, there are two ways to check the diskgroup attributes in 11g:

va-idevdb02:+ASM:/dev $sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 16 15:59:30 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 Automatic Storage Management option

SQL> col COMPATIBILITY form a10
SQL> col DATABASE_COMPATIBILITY form a10
SQL> select group_number, name,compatibility, database_compatibility from v$asm_diskgroup;

GROUP_NUMBER NAME                           COMPATIBIL DATABASE_C
------------ ------------------------------ ---------- ----------
           1 DEV_DATA                       10.1.0.0.0 10.1.0.0.0
           2 QA_FLASH                       10.1.0.0.0 10.1.0.0.0

SQL>


SQL> select group_number, name, value from v$asm_attribute;

no rows selected

SQL> select group_number, name, value from v$asm_attribute;

GROUP_NUMBER NAME                 VALUE
------------ -------------------- ----------
           1 disk_repair_time     3.6h
           1 au_size              1048576
           1 compatible.asm       11.1.0.0.0
           1 compatible.rdbms     11.1

Note that V$ASM_ATTRIBUTES is filled only when compatible.asm is set to 11.1. What’s important is that you can only change compatibility level upwards; there is no way to reset it back to the lower value. Compatibility attributes can be changed online one at a time:

SQL> alter diskgroup DEV_DATA set attribute 'compatible.asm'='11.1';

Diskgroup altered.

SQL> alter diskgroup QA_FLASH set attribute 'compatible.rdbms'='11.1';

Diskgroup altered.

SQL> select group_number, name, compatibility, database_compatibility from v$asm_diskgroup;

GROUP_NUMBER NAME                 COMPATIBIL DATABASE_C
------------ -------------------- ---------- ----------
           1 DEV_DATA                  11.1.0.0.0 11.1.0.0.0
           2 QA_FLASH                  11.1.0.0.0 11.1.0.0.0

What are compatibility attributes are useful for? They are very handy for upgrades and migrations when ASM diskgroups need to be available for ASM instances and database instances of different versions. Depending on the migration path, you might need to be able to access some diskgroups from different versions of ASM and different database instances. It might also be useful for transportable tablespaces between 10g and 11g databases.The compatible.asm diskgroup attribute controls the format of the ASM diskgroup metadata. Only ASM instances with a software version equal to or greater than compatible.asm can mount the diskgroup.The compatible.rdbms diskgroup attribute determines the format of ASM files themselves. The diskgroup can be accessed by any database instance with a compatible init.ora parameter set equal to or higher than the compatible.rdbms attribute. Note that the compatible.rdbms attribute can be set to 10.2 as well, but I couldn’t see if there were any differences in the feature set except that the compatible.rdbms=10.2 setting requires a database instance with the compatible parameter set to 10.2 or higher. compatible.asm cannot be set below 11.1 except when it’s already 10.1 by default.
The compatible.rdbms attribute can be changed only if compatible.asm is advanced to 11.1. Otherwise, you get the following error message:

SQL> alter diskgroup QA_FLASH set attribute 'compatible.rdbms'='11.1';
alter diskgroup QA_FLASH set attribute 'compatible.rdbms'='11.1'
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15242: could not set attribute compatible.rdbms
ORA-15221: ASM operation requires compatible.asm of 11.1.0.0.0 or higher

When creating a new diskgroup, compatibility attributes can be explicitly specified. It is interesting that the default is still 10.1 for both attributes even when the diskgroup is created from an ASM 10g instance and all connected databases are of 11g version. Oracle is conservative here.

During the COLLABORATE presentation, Jeremy Schneider asked me if there is a way to control the default compatibility version and, at that time, I didn’t know how to set the default compatibility to 11.1. Now that I have had a bit of time to look into it, I found couple hidden underscore parameters: _asm_compatibility and _rdbms_compatibility:

SQL> select i.ksppinm, v.ksppstvl from x$ksppi i, x$ksppcv v where i.ksppinm in ('_rdbms_compatibility','_asm_compatibility') and i.indx=v.indx;

KSPPINM             KSPPSTVL
--------             -------
_asm_compatibility     10.1
_rdbms_compatibility     10.1



Let’s test.

SQL> alter system set "_asm_compatibility"='11.1' scope=spfile;

System altered.

SQL> alter system set "_rdbms_compatibility"='11.1' scope=spfile;

System altered.

SQL> shutdown immediate
ASM diskgroups dismounted
ASM instance shutdown
SQL> startup
ASM instance started

Total System Global Area  284565504 bytes
Fixed Size                  1299428 bytes
Variable Size             258100252 bytes
ASM Cache                  25165824 bytes
ASM diskgroups mounted
SQL> select i.ksppinm, v.ksppstvl from x$ksppi i, x$ksppcv v
  2  where i.ksppinm in ('_rdbms_compatibility','_asm_compatibility')
  3    and i.indx=v.indx;

KSPPINM                        KSPPSTVL
------------------------------ ----------
_asm_compatibility             11.1
_rdbms_compatibility           11.1

SQL> create diskgroup DEV_DATA2 external redundancy disk '/asm1/DEV_DATA2-50mb-1.asm';

Diskgroup created.

SQL> select name, compatibility, database_compatibility
  2  from v$asm_diskgroup where name='DEV_DATA2';

NAME                 COMPATIBIL DATABASE_C
-------------------- ---------- ----------
DEV_DATA2                  11.1.0.0.0 11.1.0.0.0

Now what features are available with different compatibility settings?

Diskgroup attributes

compatible.asm : 11.1
compatible.rdbms : any

Non-default allocation unit size

For AU sizes 1-8 MB, both compatibility attributes can be 10.1.

SQL> select group_number, allocation_unit_size, compatibility, database_compatibility
  2  from v$asm_diskgroup where name='DEV_DATA2';

GROUP_NUMBER ALLOCATION_UNIT_SIZE COMPATIBIL DATABASE_C
------------ -------------------- ---------- ----------
           3              2097152 10.1.0.0.0 10.1.0.0.0

SQL> select group_number, name, value from v$asm_attribute
  2  where group_number=3;

no rows selected

The absence of the attribute au_size in V$ASM_ATTRIBUTE is confusing, but recall that attributes are displayed only when compatible.asm is set to 11.1.

SQL> select group_number, name, value from v$asm_attribute
  2  where group_number=3;

GROUP_NUMBER NAME                 VALUE
------------ -------------------- --------------------
           3 disk_repair_time     3.6h
           3 au_size              2097152
           3 compatible.asm       11.1.0.0.0
           3 compatible.rdbms     10.1.0.0.0

For AU sizes 16-64 MB both compatibility attributes must be 11.1.

The rest of 11g’s ASM new features fast mirror resync, variable size extents, preferred read failure groups

Both compatible.asm and compatible.rdbms must be set to 11.1.

How to Copy Archivelog Files From ASM to Filesystem and vice versa

posted Mar 16, 2011, 7:23 AM by Sachchida Ojha

RMAN allow you to copy archivelog files to a tape or filesystem. The following examples provide RMAN scripts for copying all archivelog files to '/tmp'.

-- Copy all archivelog files to a location in filesystem
rman>
run
{
allocate channel c1 type disk format '/tmp/arc_%U';
backup archivelog all;
}

-- Back up all archived logs created more than 7 and less than 30 days ago.
run {
allocate channel ch1 type disk format '/tmp/arc_%U';
backup archivelog from time 'SYSDATE-30' until time 'SYSDATE-7';
}

-- Back up all archived logs from sequence # 250 to sequence # 301 and deletes the archived redo logs after the backup is complete.

run {
allocate channel ch1 type disk format '/tmp/arc_%U';
backup
archivelog from logseq 250 until logseq 301 thread 1
}

In FORMAT clause, %U is replaced with unique file names when creating archivelog backups.

The above RMAN statements create a file in the following format in the destination directory (/etc).

-rw-r----- 1 oracle dba2 46623232 Sep 28 13:14 arc_03kqcj6g_1_1

Then, one can restore archive logs that are already located on a filesystem using restore command:

run {
set archivelog destination to '/tmp';
restore archivelog all;
}

If you do not specify SET ARCHIVELOG DESTINATION, then RMAN restores archived redo log files to the flash recovery area (when FRA is configured ).  Refer to RMAN options to restore only the required archive logfiles based on sequence number or creation timestamp.

How to move a datafile from a file system to ASM

posted Mar 16, 2011, 7:21 AM by Sachchida Ojha

Moving a datafile from the file system can be achived in two ways.


i. While the database is shutdown (in mount stage).

ii. While the database is running (with the selected tablespace offline).

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

i.  While the database is shutdown (in mount stage).


Moving oracle datafile while the database is in mount stage is performed in the following way:


1. Shutdown and mount the database.

[oracle@linux] sqlplus '/as sysdba'

SQL> shutdown immediate;

SQL> startup mount;

2. Ensure you have enough space in the ASM diskgroup to copy the datafile.

     First identify the size of the datafile you wish to move.

SQL> select file#, name, (bytes/1048576) File_Size_MB from v$datafile;

FILE#   NAME                         FILE_SIZE_MB
-----   ---------------------------- --------------
...
4       /oradata/PROD/users01.dbf    2500
...

* In this example we will be moving users01.dbf


[oracle@linux] export ORACLE_SID=+ASM

SQL> select NAME, STATE, TOTAL_MB, FREE_MB from v$asm_diskgroup;

NAME                           STATE       TOTAL_MB   FREE_MB
------------------------------ ----------- ---------- ----------
DGROUP1                        MOUNTED     100        3
DGROUP2                        MOUNTED     4882       4830


3. Connect to RMAN and copy the datafile from the filesystem to the select ASM diskgroup.

[oracle@linux] rman target=/

RMAN> copy datafile 4 to '+DGROUP2';

Starting backup at 2006/09/05 12:14:23
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=31 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/oradata/PROD/users01.dbf
output filename=+DGROUP2/PROD/datafile/users01.258.600351265 tag=TAG20060905T121424 recid=10 stamp=600351264
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:05:01
Finished backup at 2006/09/05 12:19:24


4.  Update the controlfile with the new location of the datafile.

[oracle@linux] rman target /


RMAN> switch datafile 4 to copy;

datafile 4 switched to datafile copy "+DGROUP2/PROD/datafile/users01.258.600351265".
5. The file is now if the new location.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
..
+DGROUP2/PROD/datafile/users01.258.600351265
..
6. The database may now be opened.


While the database is running (with the select tablespace offline).


In order to move a datafile on a running active database the tablespace where the datafile resides must be placed offline.

1. Identify the tablespace which contains the datafile and offline the tablespace.

SQL> select tablespace_name, file_name from dba_data_files where file_id=4;

TABLESPACE_NAME    FILE_NAME
------------------ ------------------------------
USERS              /oradata/RMAN/users01.dbf


SQL> alter tablespace USERS offline;

* * * * * Continue with Steps 2 - 5 above. * * * * *


6. After you have successfully completed the above steps (2 -5) place the tablespace online;


SQL> alter tablespace USERS online;

     The datafile has now been successfully moved to the ASM diskgroup.

How to move a datafile from ASM to the file system

posted Mar 16, 2011, 7:20 AM by Sachchida Ojha

Moving a datafile from ASM to the file system can be performed in two ways:

i. While the database is shutdown (in mount stage). This is the only option if datafiles to be
 moved are from system or undo tablespaces.

ii. While the database is running (with the selected tablespace offline).

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

i. While the database is shutdown (in mount stage).


Moving oracle datafile while the database is in mount stage is performed in the following way:

1. Shutdown and mount the database.

[oracle@linux] sqlplus '/as sysdba'

SQL> shutdown immediate;

SQL> startup mount;
2. Ensure you have enough space on the file system to copy the datafile.

First identify the size of the datafile you wish to move.

SQL> select file#, name, (bytes/1048576) File_Size_MB from v$datafile;

FILE#  NAME                                           FILE_SIZE_MB
-----  ---------------------------------------------  ------------
...
4      +DGROUP2/PROD/datafile/users01.258.600351265   2500
...

* In this example we will be moving the users01 datafile.


[oracle@linux] df -k

Filesystem                  1K-blocks      Used      Available   Use% Mounted on
/dev/vg01/root              10321208       3716884   6080036     38%  /
/dev/sda1                   147766         15521     124616      12%  /boot
none                        1022976        0         1022976     0%   /dev/shm
/dev/vg01/tmp               2064208        58380     1900972     3%   /tmp
/dev/vg01/u01               20158332       18557600  576732      97%  /u01
/dev/vg01/backups           6092608        5266016   517140      92%  /backups
/dev/vg01/oradata           15487600       11876492  2824676     81%  /oradata
/dev/vg01/recovery_area     15487600       11465932  3235236     78%  /recovery_area


3. Connect to RMAN and copy the datafile from the ASM diskgroup to the filesystem.

[oracle@linux] rman target=/

RMAN> copy datafile 4 to '/oradata/PROD/users01.dbf';

Starting backup at 2006/09/06 15:46:13
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=+DGROUP3/rman/datafile/users.258.600450259
output filename=/oradata/PROD/users01.dbf tag=TAG20060906T154614 recid=22 stamp=600450375
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2006/09/06 15:46:15

4. Update the controlfile with the new location of the datafile.

[oracle@linux] sqlplus '/as sysdba'

SQL> alter database rename file '+DGROUP3/PROD/datafile/users.258.600450259' to '/oradata/PROD/users01.dbf';

Database altered.

5. The file is now if the new location.

SQL> select file#, name from v$datafile;

FILE#    NAME
------   ------------------------------------------------------------------
..
4        /oradata/PROD/users01.dbf
..
6. The database may now be opened.


While the database is running (with the select tablespace offline).


In order to move a datafile on a running active database the tablespace where the datafile resides must be placed offline.

1. Identify the tablespace which contains the datafile and offline the tablespace.


SQL> select tablespace_name, file_name from dba_data_files where file_id=4;

TABLESPACE_NAME    FILE_NAME
------------------ ------------------------------------------
USERS              +DGROUP3/PROD/datafile/users.258.600450259


SQL> alter tablespace USERS offline;

* * * * * Continue with Steps 2 - 5 above. * * * * *


6. After you have successfully completed the above steps (2 -5) place the tablespace online;

SQL> alter tablespace USERS online;
The datafile has now been successfully moved to the ASM diskgroup.

How to duplicate a controlfile in ASM

posted Mar 16, 2011, 7:17 AM by Sachchida Ojha

Duplicating a controlfile into ASM when original controlfile is stored on a file system.

On the database instance:

1. Identify the location of the current controlfile:
    SQL> select name from v$controlfile;

    NAME
    --------------------------------------------------------------------------------
    /oradata2/102b/oradata/P10R2/control01.ctl'
    

2. Shutdown the database and start the instance:
    SQL> shutdown normal
    SQL> startup nomount

3. Use RMAN to duplicate the controlfile:
    $ rman nocatalog
    RMAN>connect target
    RMAN>restore controlfile to '<DISKGROUP_NAME>' from '<OLD_PATH>';


RMAN> restore controlfile to '+DG1' from '/oradata2/102b/oradata/P10R2/control01.ctl';

Starting restore at 23-DEC-05
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: copied control file copy
Finished restore at 23-DEC-05


We are only specifying the name of the diskgroup, so Oracle will create an OMF (Oracle Managed File).  Use ASMCMD or sqlplus to identify the name assigned to the controlfile

4. On the ASM instance, identify the name of the controlfile:

    Using ASMCMD:
    $ asmcmd
    ASMCMD> cd <DISKGROUP_NAME>
    ASMCMD> find -t controlfile . *


    Changing the current directory to the diskgroup where the controlfile was created will speed the search.

    Output:


        ASMCMD> find -t controlfile . *
        +DG1/P10R2/CONTROLFILE/backup.308.577785757
        ASMCMD>
        

    Note the name assigned to the controlfile. Although the name starts with the backup word, that does not indicate is a backup of the file.  This just the name assigned for the identical copy of the current controlfile.

5. On the database side:

        * Modify init.ora or spfile, adding the new path to parameter control_files.

            * if using init<SID>.ora, just modify the control_files parameter and restart the database.

            * If using spfile,

            1) startup nomount the database instance
            2) alter system set control_files='+DG1/P10R2/CONTROLFILE/backup.308.577785757','/oradata2/102b/oradata/P10R2/control01.ctl' scope=spfile;

            For RAC instance:

            alter system set control_files='+DG1/P10R2/CONTROLFILE/backup.308.577785757','/oradata2/102b/oradata/P10R2/control01.ctl' scope=spfile sid='*';

            3) shutdown immediate


        * start the instance.

            Verify that new control file has been recognized. If the new controlfile was not used, the complete procedure needs to be repeated.

Duplicating a controlfile into ASM  using a specific name

It is also possible to duplicate the controlfile using a specific name for the new controlfile.  In the following example, the controlfile is duplicated into a new diskgroup where   controlfiles have not been created before.

On the ASM instance:

A. Create the directory  to store the new controlfile.

     SQL> alter diskgroup <DISKGROUP_NAME> add directory '+<DG_NAME>/<DB_NAME>/CONTROLFILE';

    Note that ASM uses directories to store the files and those are created automatically when using OMF files. (just specifying the diskgroup name).  Asumming that other OMF files were created on the diskgroup, the first directory (DB_NAME) already exist, so it is only required to create the directory for the controlfile.

    
SQL> alter diskgroup DG1 add directory '+DG1/P10R2/CONTROLFILE';
      ASMCMD can also be used

ASMCMD>cd dg1
ASMCMD>mkdir controlfile
On the database instance:

B. Edit init.ora or spifile and modify parameter control_file:
     control_files='+DG1/P10R2/CONTROLFILE/control02.ctl','/oradata2/102b/oradata/P10R2/control01.ctl'

C. Identify the location of the current controlfile:
  
SQL> select name from v$controfile;
          NAME
          --------------------------------------------------------------------------------
/oradata2/102b/oradata/P10R2/control01.ctl'


D. Shutdown the database and start the instance:
    SQL> shutdown normal
    SQL> startup nomount

E. Use RMAN to duplicate the controlfile:
    $ rman nocatalog
    RMAN>connect target
    RMAN>restore controlfile to '<FULL PATH>' from '<OLD_PATH>';


RMAN> restore controlfile to '+DG1/PROD/controlfile/control02.ctl' from '/oradata2/102b/oradata/P10R2/control01.ctl';

Starting restore at 23-DEC-05
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: copied control file copy
Finished restore at 23-DEC-05


F. Start the database:
    SQL> alter database mount;
    SQL> alter database open;


Now, using ASMCMD to search for information for the controlfiles,  the find -t contrlfile command will return two records.  That does not indicate there were created two controlfiles.  The name specified is an alias name and is only an entry in the ASM metadata (V$ASM_ALIAS). Oracle will create the alias and the OMF entry when user specifies the file name.

Duplicating a controlfile into ASM when original controlfile is stored on ASM

If using spfile to start the instance:

1. Modify the spfile specifically the parameter control_files. In this example, a second controlfile
is going to be created on same diskgroup DATA1.

sql> alter system set control_files='+DATA1/v102/controlfile/current.261.637923577','+DATA1'
scope=spfile sid='*';


2. Start the instance in NOMOUNT mode.

3. From rman, duplicate the controlfile
$ rman nocatalog
RMAN>connect target
RMAN> restore controlfile from '+DATA1/v102/controlfile/current.261.637923577';

The output for the execution is like:

Starting restore at 08-NOV-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=147 instance=V1021 devtype=DISK

channel ORA_DISK_1: copied control file copy
output filename=+DATA1/v102/controlfile/current.261.637923577
output filename=+DATA1/v102/controlfile/current.269.638120375
Finished restore at 08-NOV-07

Note that the command prints the name of the new created file: +DATA1/v102/controlfile/current.269.638120375

4. Mount and Open the database

RMAN> sql 'alter database mount';
RMAN> sql 'alter database open';


5. Validate both controlfiles are present
SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
+DATA1/v102/controlfile/current.261.637923577
+DATA1/v102/controlfile/current.269.638120375


6. Modify the control_file parameter with the complete path of the new file:

sql> alter system set control_files='+DATA1/v102/controlfile/current.261.637923577','+DATA1/v102/controlfile/current.269.638120375'
scope=spfile sid='*';

Next time instance are restarted, will pick both files.


When using init.ora file:


1) Edit init.ora and add new disk group name or same disk group name for mirroring controlfiles.

Example:

control_files=('+GROUP1','+GROUP2')


(2) Start the instance in NOMOUNT mode.

(3)  Execute restore command, to duplicate the controlfile using the original location. Presuming, your current controlfile location DISK path is '+data/V10G/controlfile/Current.260.605208993' , execute:

RMAN> restore controlfile from '+data/V10G/controlfile/Current.260.605208993';

Starting restore at 29-APR-05
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=317 devtype=DISK

channel ORA_DISK_1: copied controlfile copy
output filename=+GROUP2/v10g/controlfile/backup.268.7
output filename=+GROUP2/v10g/controlfile/backup.260.5
Finished restore at 29-APR-05

(4) Mount and open the database:

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> alter database open;

database opened

RMAN> exit

(5) Verify new mirrored controlfiles via sqlplus

SQL> show parameter control_files

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string +GROUP2/v10g/controlfile/backup.268.7, +GROUP2/v10g/controlfile/backup.260.5

1-10 of 18