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 $ |
ASM QUICK REF
Step by Step - Non ASM to ASM
--*************************************************************************************************** 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; |
| Attachments:
migratetoasmRAT1.txt
migratetoasmTDB1.txt
migratetoasmchecklist.txt
[INS-20802] Automatic storage Management Configuration Failed.
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
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
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
--*************************************************************************************************** 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
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
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
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
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
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