Moving a datafile from a ASM disk group to another disk group and move database from one Disk group to another disk group



Moving a datafile from a ASM disk group to another disk group and move database from one Disk group to another disk group
This is for full database move. This will move all datafiles under ASM to a new disk group

 First place the database in mount mode
SQL>startup mount;
Then switch to RMAN ...
RMAN> backup as copy database format '+NEW_DISC_GROUP' ;
RMAN> switch database to copy ;
SQL> alter database open;
move the database from one disk group to another disk group
1. Backup the spfile and identify the spfile location,
2. start the database in mount mode

 SQL> select name from v$database;

 NAME
---------
TEST

 SQL> select open_mode from V$database;

 OPEN_MODE
----------
MOUNTED

 connect RMAN 

 RMAN> connect target /

 connected to target database: TEST (DBID=1868766554, not open)

 RMAN> backup as copy database format '+FRA';

 Starting backup at 07-APR-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1088 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00008 name=+DATA/test/datafile/demo12.290.740943031
output filename=+FRA/test/datafile/demo12.3605.844266055 tag=TAG20140407T142054 recid=27 stamp=844266074
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile fno=00011 name=+DATA/test/datafile/hello1_data.362.740943031
output filename=+FRA/test/datafile/hello1_data.3609.844266081 tag=TAG20140407T142054 recid=28 stamp=844266108
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=+DATA/test/datafile/system.361.740943031
output filename=+FRA/test/datafile/system.3624.844266115 tag=TAG20140407T142054 recid=29 stamp=844266144
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=+DATA/test/datafile/sysaux.360.740943031
output filename=+FRA/test/datafile/sysaux.3512.844266151 tag=TAG20140407T142054 recid=30 stamp=844266184
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=+DATA/test/datafile/Rajdba.359.740943031
output filename=+FRA/test/datafile/Rajdba.3546.844266187 tag=TAG20140407T142054 recid=31 stamp=844266192
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=+DATA/test/datafile/boisjmsdb.358.740943031
output filename=+FRA/test/datafile/boisjmsdb.3470.844266193 tag=TAG20140407T142054 recid=32 stamp=844266198
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile fno=00006 name=+DATA/test/datafile/xxxxxxxxxxx.357.740943031
output filename=+FRA/test/datafile/xxxxxxxxxxx.3489.844266201 tag=TAG20140407T142054 recid=33 stamp=844266205
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile fno=00007 name=+DATA/test/datafile/users.356.844256443
output filename=+FRA/test/datafile/users.3477.844266207 tag=TAG20140407T142054 recid=34 stamp=844266213
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=+DATA/test/datafile/undotbs1.355.740943057
output filename=+FRA/test/datafile/undotbs1.3471.844266215 tag=TAG20140407T142054 recid=35 stamp=844266216
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile fno=00009 name=+DATA/test/datafile/demo1.354.740943057
output filename=+FRA/test/datafile/demo1.3750.844266217 tag=TAG20140407T142054 recid=36 stamp=844266218
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile fno=00012 name=+DATA/test/datafile/hello.353.740943059
output filename=+FRA/test/datafile/hello.3450.844266219 tag=TAG20140407T142054 recid=37 stamp=844266219
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile fno=00010 name=+DATA/test/datafile/demo.302.740943059
output filename=+FRA/test/datafile/demo.3447.844266221 tag=TAG20140407T142054 recid=38 stamp=844266220
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
copying current control file
output filename=+FRA/test/controlfile/backup.3445.844266221 tag=TAG20140407T142054 recid=39 stamp=844266221
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 07-APR-14

 RMAN>
RMAN> switch database to copy ;

 datafile 1 switched to datafile copy "+FRA/test/datafile/system.3624.844266115"
datafile 2 switched to datafile copy "+FRA/test/datafile/undotbs1.3471.844266215"
datafile 3 switched to datafile copy "+FRA/test/datafile/sysaux.3512.844266151"
datafile 4 switched to datafile copy "+FRA/test/datafile/Rajdba.3546.844266187"
datafile 5 switched to datafile copy "+FRA/test/datafile/boisjmsdb.3470.844266193"
datafile 6 switched to datafile copy "+FRA/test/datafile/xxxxxxxxxxx.3489.844266201"
datafile 7 switched to datafile copy "+FRA/test/datafile/users.3477.844266207"
datafile 8 switched to datafile copy "+FRA/test/datafile/demo12.3605.844266055"
datafile 9 switched to datafile copy "+FRA/test/datafile/demo1.3750.844266217"
datafile 10 switched to datafile copy "+FRA/test/datafile/demo.3447.844266221"
datafile 11 switched to datafile copy "+FRA/test/datafile/hello1_data.3609.844266081"
datafile 12 switched to datafile copy "+FRA/test/datafile/hello.3450.844266219"
RMAN> sql 'alter database open';

 sql statement: alter database open

 RMAN>

 SQL> select FILE_NAME,TABLESPACE_NAME from dba_data_files;

 FILE_NAME                                                              TABLESPACE_NAME
---------------------------------------------------------------------- ------------------------------
+FRA/test/datafile/system.3624.844266115                               SYSTEM
+FRA/test/datafile/undotbs1.3471.844266215                             UNDOTBS1
+FRA/test/datafile/sysaux.3512.844266151                               SYSAUX
+FRA/test/datafile/Rajdba.3546.844266187                          Rajdba
+FRA/test/datafile/boisjmsdb.3470.844266193                           xxxxxyyyy
+FRA/test/datafile/xxxxxxxxxxx.3489.844266201                         xxxxxxxxxxx
+FRA/test/datafile/users.3477.844266207                                USERS
+FRA/test/datafile/demo12.3605.844266055                           demo12
+FRA/test/datafile/demo1.3750.844266217                          demo1
+FRA/test/datafile/demo.3447.844266221                           demo
+FRA/test/datafile/hello1_data.3609.844266081                    hello1_DATA
+FRA/test/datafile/hello.3450.844266219                           hello

I'll explain two ways of moving a datafile from a ASM disk group to another disk group.

Here is the first way:

1.       Find the name of the datafile that you're going to migrate:

SQL> SELECT FILE_NAME FROM DBA_DATA_FILES WHERE tablespace_name='USERS';
FILE_NAME
--------------------------------------------------------------------------------
+DATA/test/datafile/users.356.740943031

2. Take the tablespace offline:
SQL> alter tablespace USERS offline;
Tablespace altered.

3. Copy the file using RMAN:
RMAN> COPY datafile '+DATA/test/datafile/users.356.740943031' to '+FRA';

4. Change datafile's name to the name displayed as output of the command above:    
SQL> ALTER DATABASE RENAME FILE '<old_name>' TO '<output file name written at rman output>';

SQL> ALTER DATABASE RENAME FILE '+DATA/test/datafile/users.356.740943031' to '+FRA/test/datafile/users.3605.844255353';
Database altered.

SQL> alter tablespace USERS online;
Tablespace altered.
SQL> select FILE_NAME,TABLESPACE_NAME ,ONLINE_STATUS from DBA_DATA_FILES where TABLESPACE_NAME='USERS';

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME                ONLINE_
------------------------------ -------
+DATA/test/datafile/users.356.844256443
USERS                          ONLINE

And here is the second way:

1. Find the name of the datafile that you're going to migrate just like firt method:
SQL> SELECT FILE_NAME FROM DBA_DATA_FILES WHERE tablespace_name='USERS';
FILE_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+FRA/test/datafile/users.3605.844255353

2. Take the datafile offline instead this time:
SQL> ALTER DATABASE DATAFILE '+FRA/test/datafile/users.3605.844255353' OFFLINE;
Database altered.

3. Copy the file using RMAN:

RMAN> connect target /

connected to target database: TEST (DBID=1868766554)

RMAN> COPY datafile '+FRA/test/datafile/users.3605.844255353' to '+DATA';

Starting backup at 07-APR-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1074 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00007 name=+FRA/test/datafile/users.3605.844255353
output filename=+DATA/test/datafile/users.356.844256443 tag=TAG20140407T114042 recid=26 stamp=844256446
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 07-APR-14

4. Change datafile's name to the new name displayed as output of the command above to update data dictionary: 

SQL> ALTER DATABASE RENAME FILE '+FRA/test/datafile/users.3605.844255353' to '+DATA/test/datafile/users.356.844256443';
Database altered.

5. Rename the datafile using RMAN to update ASM. This command is equivalent of "alter database rename file":  

RMAN> SWITCH DATAFILE '<new_name>' TO COPY;
RMAN> SWITCH DATAFILE '+DATA/test/datafile/users.356.844256443' to copy;
datafile 7 switched to datafile copy "+DATA/test/datafile/users.356.844256443"

RMAN>
RMAN> sql 'alter tablespace USERS online';
sql statement: alter tablespace USERS online
RMAN>

6. Recover  the new datafile:
RMAN> RECOVER DATAFILE '<new_name>';
RMAN> RECOVER DATAFILE '+DATA/test/datafile/users.356.844256443';
Starting recover at 07-APR-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1081 devtype=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:02
Finished recover at 07-APR-14

RMAN>

7. Bring it online
SQL> ALTER DATABASE DATAFILE '<new_name>' ONLINE;
RMAN> sql 'alter tablespace USERS online';
sql statement: alter tablespace USERS online

SQL> select FILE_NAME,TABLESPACE_NAME ,ONLINE_STATUS from DBA_DATA_FILES where TABLESPACE_NAME='USERS';
FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME                ONLINE_
------------------------------ -------
+DATA/test/datafile/users.356.844256443

USERS                          ONLINE

Comments

Post a Comment

Popular posts from this blog

QUERY DATAGUARD AND STANDBY STATUS

-- Find which logs were applied in the last day  SELECT SEQUENCE#, to_char(FIRST_TIME,'hh24:mi:ss dd/mm/yyyy'), to_char(NEXT_TIME,'hh24:mi:ss dd/mm/yyyy'),APPLIED FROM V$ARCHIVED_LOG where next_time>sysdate-1 ORDER BY SEQUENCE# ; -- Find last applied log   select to_char(max(FIRST_TIME),'hh24:mi:ss dd/mm/yyyy') FROM V$ARCHIVED_LOG where applied='YES';   -- What are the managed standby processes doing?   SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;   -- Are we on production or standby?  SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;   -- Check for errors  SELECT MESSAGE FROM V$DATAGUARD_STATUS;   -- Check that the DB was openned correctly   SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS; -- important lag statistics  select * from v$dataguard_stats;  -- co...
Read more

Finding object size in Oracle database

Ref: http://www.runningoracle.com/product_info.php?cPath=2_44&products_id=153 Finding object size in Oracle database is very important and common. Is it very useful to know the exact size occupied by the object at the tablespace. The object size in the following scripts is in Mbytes. The scripts have been formatted to work very easily with TOAD SQL Editor. For example you can filter with tablespace_name, or owner, or size (for example more than 1GByte)  SELECT owner, segment_name, segment_type, partition_name, ROUND(bytes/(1024*1024),2) SIZE_MB, tablespace_name  FROM DBA_SEGMENTS  WHERE SEGMENT_TYPE IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION',  'INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION', 'TEMPORARY')  --AND TABLESPACE_NAME LIKE 'COSTE%'  --AND SEGMENT_NAME LIKE 'P2010201%'  --AND partition_name LIKE 'P20100201%' --AND segment_type = 'TABLE' --AND OWNER = 'TARG...
Read more

Blocking session on RAC database

Blocking session on RAC database Blocking session of the databases Generate the report for Blocking session The SQL will provide Blocking sessions with SID, node information SELECT DISTINCT S1.USERNAME || '@' || S1.MACHINE || ' ( INST=' || S1.INST_ID || ' SID=' || S1.SID || ' ) IS BLOCKING ' || S2.USERNAME || '@' || S2.MACHINE || ' ( INST=' || S1.INST_ID || ' SID=' || S2.SID || ' ) ' AS BLOCKING_STATUS FROM GV$LOCK L1, GV$SESSION S1, GV$LOCK L2, GV$SESSION S2 WHERE S1.SID=L1.SID AND S2.SID=L2.SID AND S1.INST_ID=L1.INST_ID AND S2.INST_ID=L2.INST_ID AND L1.BLOCK > 0 AND L2.REQUEST > 0 AND L1.ID1 = L2.ID1 AND L1.ID2 = L2.ID2; Blockers information select s1.username || '@' || s1.machine  || ' ( SID=' || s1.sid || ' ) is blocking '  || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status from gv$lock l1, gv$session s...
Read more