Friday, September 9, 2011

MOVING TABLESPACE/DATAFILES ACROSS DISK GROUP IN ASM (ONLINE & OFFLINE)

move a datafile from a file system to ASM(this includes system tablespace, data tablespace and datafiles etc)

1. MOVING SYSTEM TABLESPACE FROM ONE DISK GROUP TO ANOTHER DISK GROUP
$sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Sep 8 12:00:24 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1236111360 bytes
Fixed Size                  2225952 bytes
Variable Size             620759264 bytes
Database Buffers          587202560 bytes
Redo Buffers               25923584 bytes
Database mounted.


$rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Thu Sep 8 12:01:52 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: Bhuvan1 (DBID=787350752, not open)

RMAN>  backup as copy tablespace system format '+SYSTEM';

Starting backup at 08-SEP-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA1/bhuvan/datafile/system.261.756901437
output file name=+SYSTEM/bhuvan/datafile/system.259.761314013 tag=TAG20110908T120653 RECID=16 STAMP=761314015
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=+DATA1/bhuvan/datafile/system.262.756901455
output file name=+SYSTEM/bhuvan/datafile/system.260.761314017 tag=TAG20110908T120653 RECID=17 STAMP=761314018
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=+SYSTEM/bhuvan/controlfile/backup.261.761314021 tag=TAG20110908T120653 RECID=18 STAMP=761314022
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
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-SEP-11
channel ORA_DISK_1: finished piece 1 at 08-SEP-11
piece handle=+SYSTEM/bhuvan/backupset/2011_09_08/nnsnf0_tag20110908t120653_0.262.761314023 tag=TAG20110908T120653 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 08-SEP-11

RMAN> switch tablespace system to copy;

datafile 1 switched to datafile copy "+SYSTEM/bhuvan/datafile/system.259.761314013"
datafile 7 switched to datafile copy "+SYSTEM/bhuvan/datafile/system.260.761314017"

$sqlplus  / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Sep 8 12:14:08 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,Data Mining and Real Application Testing options

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
bhuvan       MOUNTED

SQL> alter database open;

Database altered.

SQL>

2. MOVING SYSTEM DATAFILE FROM ONE DISK GROUP TO ANOTHER DISK GROUP

STEP#1: Start the database in the mount stage
STEP#2: connect to the rman and copy the datafile
RMAN> copy datafile 3 to '+SYSTEM';
Starting backup at 08-SEP-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=388 instance=BE1_2 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA1/bhuvan/datafile/psapundo.259.756901419
output file name=+SYSTEM/bhuvan/datafile/undo.263.761315651 tag=TAG20110908T123410 RECID=21 STAMP=761315652
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 08-SEP-11

STEP#3: switch the datafile to the new disk group
RMAN> switch datafile 3 to copy;
datafile 3 switched to datafile copy "+SYSTEM/bhuvan/datafile/undo.263.761315651"
STEP#4: open the database
SQL> alter database open;

3. MOVING NON-SYSTEM TABLESPACE FROM ONE DISK GROUP TO ANOTHER DISK GROUP(WITH OFFLINE OPITION)

STEP#1: Offline the tablespace
RMAN>  sql ‘alter tablespace <TBS_NAME> offline’;
STEP#2: connect to the rman and copy the tablespace
RMAN> backup as copy tablespace <TBS_NAME> format ‘<+NEW_DISK’;
STEP#3: Switch the tablespace to the new location
RMAN> switch tablespace <TBS_NAME> to copy;
STEP#4: Online the tablespace
RMAN> sql ‘alter tablespace <TBS_NAME>  online’;

4. MOVING NON-SYSTEM TABLESPACE FROM ONE DISK GROUP TO ANOTHER DISK GROUP(WITH ONLINE OPITION)

STEP#1: connect to the rman and copy the tablespace
RMAN> backup as copy tablespace <TBS_NAME> format ‘<+NEW_DISK’;
STEP#2: Offline the tablespace
RMAN> sql ‘alter tablespace <TBS_NAME> offline’;
STEP#3: Switch the tablespace to the new location
RMAN> switch tablespace <TBS_NAME> to copy;
STEP#4: Recover the tablespace
RMAN> recover tablespace <TBS_NAME>;
STEP#4: Online the tablespace
RMAN> sql ‘alter tablespace <TBS_NAME> online’;
5. MOVING NON-SYSTEM DATAFILE FROM ONE DISK GROUP TO ANOTHER DISK GROUP(ONLINE & OFFLINE)

STEP#1: Offline the datafile
RMAN> sql ‘alter datafile <datafile_number or name> offline’;
STEP#2: connect to the rman and copy the tablespace
RMAN> COPY DATAFILE <datafile_number or name> to ‘<+NEW_DISK’>;
STEP#3: Switch the tablespace to the new location
RMAN> switch datafile <datafile_number or name> to copy;
STEP#4: Recover the datafile
RMAN> recover datafile <datafile_number or name>;
STEP#5: Online the tablespace
RMAN> ‘alter datafile <datafile_number or name> online’;

Happy Learning!!!!!

2 comments:

  1. Thanks for your Doc, it is going to help me big time!

    ReplyDelete