Wednesday, December 10, 2008
Oracle 11gR2 Installation on RHEL 4
Wednesday, December 10, 2008
Dataguard Adventures - Part I...!!!!!
Wednesday, December 10, 2008
Dataguard Adventures - Part II...!!!!!…
Wednesday, December 10, 2008
When ASM rebalancing stops...!!!!
Monday, December 8, 2008
Reducing Downtime during database movement and migrations
Relocating datafiles in an ASM diskgroup
To relocate files, we need to take the datafile offline, then copy/move the file to the target diskgroup using
1) RMAN
2) DBMS_FILE_TRANSFER package
I prefer to user RMAN although i tested DBMS_FILE_TRANFER and it worked equally well. Finally after movement of datafile to the target diskgroup, we need to recover the offlined datafile and make it online.
Now what to do with the original(old) datafile…… No worries ASM takes care of that.
If the file to be relocated is an OMF on ASM, ASM will remove the original source file when ALTER DATABASE RENAME FILE.... is fired on the database.
On the other hand, if the file to be relocated is not an OMF, we need to manually remove the file after relocation. Hence the space after relocation would be automatically released back to source diskgroup in case of OMF file while this will not happen in case of non-OMF file.
Here are 2 scenarios to test this.One with source file as OMF and other with source file as non-OMF.
Scenario -I (File to be relocated is OMF on ASM)
The datafile 7 is being moved from ASM diskgroup +DISK_GROUP11 to +DISK_GROUP03
SQL> alter database datafile 7 offline;
Database altered.
RMAN> copy datafile 7 to '+DISK_GROUP03';
Starting backup at 07-FEB-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00007 name=+DISK_GROUP11/pimq1/datafile/xml_data01.1214.694000501
output filename=+DISK_GROUP03/pimq1/datafile/xml_data01.329.742521331 tag=TAG20110207T235531 recid=23 stamp=742521337
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 07-FEB-11
SQL> alter database rename file '+DISK_GROUP11/pimq1/datafile/xml_data01.1214.694000501' to '+DISK_GROUP03/pimq1/datafile/xml_data01.329.742521331';
Database altered.
The above statement automatically removed the original datafile '+DISK_GROUP11/pimq1/datafile/xml_data01.1214.694000501'
SQL> recover datafile 7;
Media recovery complete.
SQL> alter database datafile 7 online;
Database altered.
ASMCMD> pwd
+DISK_GROUP03/PIMQ1/DATAFILE
DATAFILE UNPROT COARSE FEB 07 23:00:00 Y TOOLS.327.742520915
DATAFILE UNPROT COARSE FEB 07 23:00:00 Y USERS.328.742521119
DATAFILE UNPROT COARSE FEB 07 23:00:00 Y XML_DATA01.329.742521331
Scenario -II (File to be relocated is not an OMF on ASM)
SQL> select file#,name,status from v$datafile where file#=234;
FILE# NAME STATUS
---------- ---------------------------------------------------------------------- -------
234 +DISK_GROUP03/orenq1/datafile/rms_data_medium11.dbf ONLINE
ASMCMD> pwd
+DISK_GROUP11/ORENQ1/DATAFILE
ASMCMD> ls –lt ----- Output truncated
+DISK_GROUP11/orenq1/datafile/rms_data_medium11.dbf => +DISK_GROUP11/ORENQ1/DATAFILE/RMS_DATA_MEDIUM.1040.721644609
SQL> alter database datafile 234 offline;
Database altered.
RMAN> copy datafile 234 to '+DISK_GROUP03';
Starting backup at 08-FEB-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=420 instance=orenq11 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00234 name=+DISK_GROUP11/orenq1/datafile/rms_data_medium11.dbf
output filename=+DISK_GROUP03/orenq1/datafile/rms_data_medium.332.742522617 tag=TAG20110208T001656 recid=1 stamp=742523058
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:07:26
Finished backup at 08-FEB-11
An important thing to be taken care while moving a non-OMF file is that if it is required to preserve the non-OMF name in the target diskgroup too,
then specify the complete path with filename during RMAN copy i.e.
RMAN> copy datafile 234 to '+DISK_GROUP03/orenq1/datafile/rms_data_medium11.dbf';
SQL> alter database rename file '+DISK_GROUP11/orenq1/datafile/rms_data_medium11.dbf' to '+DISK_GROUP03/orenq1/datafile/rms_data_medium.332.742522617';
Database altered.
SQL> SQL> recover datafile 234;
Media recovery complete.
SQL> alter database datafile 234 online;
Database altered.
SQL> select file#,name,status from v$datafile where file#=234;
FILE# NAME STATUS
---------- ---------------------------------------------------------------------- -------
234 +DISK_GROUP03/orenq1/datafile/rms_data_medium.332.742522617 ONLINE
Once this relocation is complete, the space is not released to source diskgroup (+DISK_GROUP03).It would be released once the source file gets deleted.
ASMCMD> pwd
+DISK_GROUP11/ORENQ1/DATAFILE
ASMCMD> ls -rlt
Type Redund Striped Time Sys Name
DATAFILE UNPROT COARSE FEB 07 22:00:00 Y MIG_DATA.1386.703225145
DATAFILE UNPROT COARSE FEB 07 23:00:00 Y DAILY_ITEM_FORE_I_PART.1366.704037409
DATAFILE UNPROT COARSE FEB 07 23:00:00 Y DAILY_ITEM_FORE_T_PART.1306.704037117
DATAFILE UNPROT COARSE FEB 07 23:00:00 Y DAILY_SALES_DISC_I_PART.1587.704035493
DATAFILE UNPROT COARSE FEB 07 23:00:00 Y ITEM_LOC_HIST_I_PART.1038.704040351
DATAFILE UNPROT COARSE FEB 07 23:00:00 Y MIG_INDEX.1398.703222837
DATAFILE UNPROT COARSE FEB 07 23:00:00 Y REIM_DATA_SMALL.1322.703178457
DATAFILE UNPROT COARSE FEB 07 23:00:00 Y REIM_INDEX_SMALL.1310.703178943
DATAFILE UNPROT COARSE FEB 07 23:00:00 Y REPL_RESULTS_I_PART.1042.704041773
DATAFILE UNPROT COARSE FEB 07 23:00:00 Y RESA_INDEX_SMALL.1290.703179451
DATAFILE UNPROT COARSE FEB 07 23:00:00 Y RMS_DATA_LARGE.1332.704044971
DATAFILE UNPROT COARSE FEB 07 23:00:00 Y RMS_DATA_LARGE.1585.721625715
DATAFILE UNPROT COARSE FEB 07 23:00:00 Y RMS_INDEX_LARGE.1045.704045503
DATAFILE UNPROT COARSE FEB 07 23:00:00 Y RMS_INDEX_LARGE.1396.704045725
DATAFILE UNPROT COARSE FEB 07 23:00:00 Y RMS_INDEX_SMALL.1350.703176469
DATAFILE UNPROT COARSE FEB 07 23:00:00 Y RPM_DATA_MEDIUM.1590.703179767
DATAFILE UNPROT COARSE FEB 07 23:00:00 Y RPM_DATA_SMALL.1037.703180593
DATAFILE UNPROT COARSE FEB 07 23:00:00 Y RPM_INDEX_SMALL.1392.703181677
N rms_data_large11.dbf => +DISK_GROUP11/ORENQ1/DATAFILE/RMS_DATA_LARGE.1585.721625715
DATAFILE UNPROT COARSE FEB 08 00:00:00 Y RMS_DATA_MEDIUM.1040.721644609
N rms_data_medium11.dbf => +DISK_GROUP11/ORENQ1/DATAFILE/RMS_DATA_MEDIUM.1040.721644609
ASMCMD> rm rms_data_medium11.dbf
ASMCMD> ls -rlt
Type Redund Striped Time Sys Name
DATAFILE UNPROT COARSE FEB 07 22:00:00 Y MIG_DATA.1386.703225145
DATAFILE UNPROT COARSE FEB 07 23:00:00 Y DAILY_ITEM_FORE_I_PART.1366.704037409
DATAFILE UNPROT COARSE FEB 07 23:00:00 Y DAILY_ITEM_FORE_T_PART.1306.704037117
DATAFILE UNPROT COARSE FEB 07 23:00:00 Y DAILY_SALES_DISC_I_PART.1587.704035493
DATAFILE UNPROT COARSE FEB 07 23:00:00 Y ITEM_LOC_HIST_I_PART.1038.704040351
DATAFILE UNPROT COARSE FEB 07 23:00:00 Y MIG_INDEX.1398.703222837
DATAFILE UNPROT COARSE FEB 07 23:00:00 Y REIM_DATA_SMALL.1322.703178457
DATAFILE UNPROT COARSE FEB 07 23:00:00 Y REIM_INDEX_SMALL.1310.703178943
DATAFILE UNPROT COARSE FEB 07 23:00:00 Y REPL_RESULTS_I_PART.1042.704041773
DATAFILE UNPROT COARSE FEB 07 23:00:00 Y RESA_INDEX_SMALL.1290.703179451
DATAFILE UNPROT COARSE FEB 07 23:00:00 Y RMS_DATA_LARGE.1332.704044971
DATAFILE UNPROT COARSE FEB 07 23:00:00 Y RMS_DATA_LARGE.1585.721625715
DATAFILE UNPROT COARSE FEB 07 23:00:00 Y RMS_INDEX_LARGE.1045.704045503
DATAFILE UNPROT COARSE FEB 07 23:00:00 Y RMS_INDEX_LARGE.1396.704045725
DATAFILE UNPROT COARSE FEB 07 23:00:00 Y RMS_INDEX_SMALL.1350.703176469
DATAFILE UNPROT COARSE FEB 07 23:00:00 Y RPM_DATA_MEDIUM.1590.703179767
DATAFILE UNPROT COARSE FEB 07 23:00:00 Y RPM_DATA_SMALL.1037.703180593
DATAFILE UNPROT COARSE FEB 07 23:00:00 Y RPM_INDEX_SMALL.1392.703181677
N rms_data_large11.dbf => +DISK_GROUP11/ORENQ1/DATAFILE/RMS_DATA_LARGE.1585.721625715
Cheers…!!!
Rishi
doc id 460365.1 points to some troubles in using sqlplus renaming of datafiles.
ReplyDelete