Exadata Database machine consists of 3 ASM Disk Groups:
+DATA for Database Files
+RECO for Online Redo log and Archive log files
+DBFS_DG for Cluster configuration files such as OCR and Voting disks
In a Customized environment Customers can choose to have more than 3 Disk Groups. But it is recommended to have 3 Disk Groups. The DATA and RECO disk groups can be sized 80%-20% or 40%-60% respectively of over all storage capacity. Sometimes it is possible that +DATA disk group can be filled very fast if you have several databases.
In this article we will demostrate how to move a Database from +DATA disk group to +RECO disk group.
Steps to move a database from +DATA to +RECO ASM Disk Group:
Step 1: Get the ASM Disk Information
SQL> select state,name from v$asm_diskgroup;
STATE NAME
----------- ------------------------------
MOUNTED RECO
MOUNTED DBFS_DG
MOUNTED DATA
Step 2: Get the Database files details
SQL> select name, open_mode,database_role from gv$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
DBM01 READ WRITE PRIMARY
DBM01 READ WRITE PRIMARY
DBM01 READ WRITE PRIMARY
DBM01 READ WRITE PRIMARY
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
+DATA/dbm01/controlfile/current.256.976374731
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA/dbm01/datafile/system.259.976374739
+DATA/dbm01/datafile/sysaux.260.976374743
+DATA/dbm01/datafile/undotbs1.261.976374745
+DATA/dbm01/datafile/undotbs2.263.976374753
+DATA/dbm01/datafile/undotbs3.264.976374755
+DATA/dbm01/datafile/undotbs4.265.976374757
+DATA/dbm01/datafile/users.266.976374757
7 rows selected.
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
+DATAC1/dbm01/tempfile/temp.262.976375229
SQL>
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
+DATA/dbm01/onlinelog/group_1.257.976374733
+DATA/dbm01/onlinelog/group_2.258.976374735
+DATA/dbm01/onlinelog/group_7.267.976375073
+DATA/dbm01/onlinelog/group_8.268.976375075
+DATA/dbm01/onlinelog/group_5.269.976375079
+DATA/dbm01/onlinelog/group_6.270.976375083
+DATA/dbm01/onlinelog/group_3.271.976375085
+DATA/dbm01/onlinelog/group_4.272.976375087
+DATA/dbm01/onlinelog/group_9.274.976375205
+DATA/dbm01/onlinelog/group_10.275.976375209
+DATA/dbm01/onlinelog/group_11.276.976375211
+DATA/dbm01/onlinelog/group_12.277.976375215
+DATA/dbm01/onlinelog/group_13.278.976375217
+DATA/dbm01/onlinelog/group_14.279.976375219
+DATA/dbm01/onlinelog/group_15.280.976375223
+DATA/dbm01/onlinelog/group_16.281.976375225
16 rows selected.
SQL> select filename from v$block_change_tracking;
FILENAME
--------------------------------------------------------------------
+DATA/dbm01/changetracking/ctf.282.976375227
Step 3: Backup Database using RMAN copy command as shown below. Here we are moving database to +RECO ASM Disk Group.
RMAN> run {
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
allocate channel c5 device type disk;
allocate channel c6 device type disk;
allocate channel c7 device type disk;
allocate channel c8 device type disk;
backup as copy database include current controlfile format '+RECO';
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;
release channel c7;
release channel c8;
}
released channel: ORA_DISK_1
allocated channel: c1
channel c1: SID=1189 instance=dbm011 device type=DISK
allocated channel: c2
channel c2: SID=1321 instance=dbm011 device type=DISK
allocated channel: c3
channel c3: SID=1343 instance=dbm011 device type=DISK
allocated channel: c4
channel c4: SID=1387 instance=dbm011 device type=DISK
allocated channel: c5
channel c5: SID=1497 instance=dbm011 device type=DISK
allocated channel: c6
channel c6: SID=1519 instance=dbm011 device type=DISK
allocated channel: c7
channel c7: SID=1541 instance=dbm011 device type=DISK
allocated channel: c8
channel c8: SID=1563 instance=dbm011 device type=DISK
Starting backup at 26-MAY-18
channel c1: starting datafile copy
input datafile file number=00001 name=+DATA/dbm01/datafile/system.259.976374739
channel c2: starting datafile copy
input datafile file number=00002 name=+DATA/dbm01/datafile/sysaux.260.976374743
channel c3: starting datafile copy
input datafile file number=00003 name=+DATA/dbm01/datafile/undotbs1.261.976374745
channel c4: starting datafile copy
input datafile file number=00004 name=+DATA/dbm01/datafile/undotbs2.263.976374753
channel c5: starting datafile copy
input datafile file number=00005 name=+DATA/dbm01/datafile/undotbs3.264.976374755
channel c6: starting datafile copy
input datafile file number=00006 name=+DATA/dbm01/datafile/undotbs4.265.976374757
channel c7: starting datafile copy
input datafile file number=00007 name=+DATA/dbm01/datafile/users.266.976374757
channel c8: starting datafile copy
copying current control file
output file name=+RECO/dbm01/datafile/users.284.977121353 tag=TAG20180526T063551 RECID=16 STAMP=977121353
channel c7: datafile copy complete, elapsed time: 00:00:02
output file name=+RECO/dbm01/controlfile/backup.283.977121353 tag=TAG20180526T063551 RECID=17 STAMP=977121353
channel c8: datafile copy complete, elapsed time: 00:00:01
output file name=+RECO/dbm01/datafile/system.291.977121353 tag=TAG20180526T063551 RECID=18 STAMP=977121389
channel c1: datafile copy complete, elapsed time: 00:00:46
output file name=+RECO/dbm01/datafile/sysaux.290.977121353 tag=TAG20180526T063551 RECID=23 STAMP=977121392
channel c2: datafile copy complete, elapsed time: 00:00:46
output file name=+RECO/dbm01/datafile/undotbs1.289.977121353 tag=TAG20180526T063551 RECID=21 STAMP=977121392
channel c3: datafile copy complete, elapsed time: 00:00:46
output file name=+RECO/dbm01/datafile/undotbs2.288.977121353 tag=TAG20180526T063551 RECID=19 STAMP=977121392
channel c4: datafile copy complete, elapsed time: 00:00:46
output file name=+RECO/dbm01/datafile/undotbs3.287.977121353 tag=TAG20180526T063551 RECID=20 STAMP=977121392
channel c5: datafile copy complete, elapsed time: 00:00:46
output file name=+RECO/dbm01/datafile/undotbs4.286.977121353 tag=TAG20180526T063551 RECID=22 STAMP=977121392
channel c6: datafile copy complete, elapsed time: 00:00:46
Finished backup at 26-MAY-18
Starting Control File and SPFILE Autobackup at 26-MAY-18
piece handle=+RECO/dbm01/autobackup/2018_05_26/s_977121397.282.977121399 comment=NONE
Finished Control File and SPFILE Autobackup at 26-MAY-18
released channel: c1
released channel: c2
released channel: c3
released channel: c4
released channel: c5
released channel: c6
released channel: c7
released channel: c8
Step 4: Verify the RMAN Database Copy using RMAN
RMAN> list copy of database;
List of Datafile Copies
=======================
Key File S Completion Time Ckp SCN Ckp Time
------- ---- - --------------- ---------- ---------------
18 1 A 26-MAY-18 1330853 26-MAY-18
Name: +RECO/dbm01/datafile/system.291.977121353
Tag: TAG20180526T063551
9 1 A 26-MAY-18 1330410 26-MAY-18
Name: +RECO/dbm01/datafile/system.286.977120961
Tag: TAG20180526T062919
3 1 A 26-MAY-18 1330155 26-MAY-18
Name: +RECO/dbm01/datafile/system.280.977120795
Tag: TAG20180526T062633
23 2 A 26-MAY-18 1330856 26-MAY-18
Name: +RECO/dbm01/datafile/sysaux.290.977121353
Tag: TAG20180526T063551
12 2 A 26-MAY-18 1330413 26-MAY-18
Name: +RECO/dbm01/datafile/sysaux.287.977120961
Tag: TAG20180526T062919
2 2 A 26-MAY-18 1330158 26-MAY-18
Name: +RECO/dbm01/datafile/sysaux.281.977120795
Tag: TAG20180526T062633
21 3 A 26-MAY-18 1330859 26-MAY-18
Name: +RECO/dbm01/datafile/undotbs1.289.977121353
Tag: TAG20180526T063551
11 3 A 26-MAY-18 1330416 26-MAY-18
Name: +RECO/dbm01/datafile/undotbs1.288.977120961
Tag: TAG20180526T062919
4 3 A 26-MAY-18 1330154 26-MAY-18
Name: +RECO/dbm01/datafile/undotbs1.279.977120795
Tag: TAG20180526T062633
19 4 A 26-MAY-18 1330862 26-MAY-18
Name: +RECO/dbm01/datafile/undotbs2.288.977121353
Tag: TAG20180526T063551
10 4 A 26-MAY-18 1330419 26-MAY-18
Name: +RECO/dbm01/datafile/undotbs2.289.977120961
Tag: TAG20180526T062919
1 4 A 26-MAY-18 1330153 26-MAY-18
Name: +RECO/dbm01/datafile/undotbs2.278.977120795
Tag: TAG20180526T062633
20 5 A 26-MAY-18 1330865 26-MAY-18
Name: +RECO/dbm01/datafile/undotbs3.287.977121353
Tag: TAG20180526T063551
13 5 A 26-MAY-18 1330422 26-MAY-18
Name: +RECO/dbm01/datafile/undotbs3.290.977120961
Tag: TAG20180526T062919
7 5 A 26-MAY-18 1330184 26-MAY-18
Name: +RECO/dbm01/datafile/undotbs3.282.977120829
Tag: TAG20180526T062633
22 6 A 26-MAY-18 1330868 26-MAY-18
Name: +RECO/dbm01/datafile/undotbs4.286.977121353
Tag: TAG20180526T063551
15 6 A 26-MAY-18 1330425 26-MAY-18
Name: +RECO/dbm01/datafile/undotbs4.291.977120961
Tag: TAG20180526T062919
6 6 A 26-MAY-18 1330187 26-MAY-18
Name: +RECO/dbm01/datafile/undotbs4.283.977120829
Tag: TAG20180526T062633
16 7 A 26-MAY-18 1330871 26-MAY-18
Name: +RECO/dbm01/datafile/users.284.977121353
Tag: TAG20180526T063551
8 7 A 26-MAY-18 1330428 26-MAY-18
Name: +RECO/dbm01/datafile/users.292.977120961
Tag: TAG20180526T062919
5 7 A 26-MAY-18 1330190 26-MAY-18
Name: +RECO/dbm01/datafile/users.284.977120829
Tag: TAG20180526T062633
RMAN> list copy of controlfile;
List of Control File Copies
===========================
Key S Completion Time Ckp SCN Ckp Time
------- - --------------- ---------- ---------------
17 A 26-MAY-18 1330876 26-MAY-18
Name: +RECO/dbm01/controlfile/backup.283.977121353
Tag: TAG20180526T063551
14 A 26-MAY-18 1330434 26-MAY-18
Name: +RECO/dbm01/controlfile/backup.293.977120965
Tag: TAG20180526T062919
Step 5: Verify the RMAN Database Copy backup in ASM
[oracle@dm01db01 ~]$ asmcmd -p
ASMCMD [+] > lsdg
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED HIGH N 512 4096 4194304 272154624 271558968 6479872 88359698 0 N DATA/
MOUNTED HIGH N 512 4096 4194304 2404640 2402468 68704 777921 0 Y DBFS_DG/
MOUNTED NORMAL N 512 4096 4194304 45389568 45183784 540352 22321716 0 N RECO/
ASMCMD [+] > cd +RECO
ASMCMD [+RECO] > ls -l
Type Redund Striped Time Sys Name
Y DBM01/
ASMCMD [+RECO] > cd DBM01
ASMCMD [+RECO/DBM01] > ls -l
Type Redund Striped Time Sys Name
Y ARCHIVELOG/
Y AUTOBACKUP/
Y CONTROLFILE/
Y DATAFILE/
N snapcf_dbm01.f => +RECO/DBM01/CONTROLFILE/Backup.285.977120961
ASMCMD [+RECO/DBM01] > ls -l DATAFILE/
Type Redund Striped Time Sys Name
DATAFILE MIRROR COARSE MAY 26 06:00:00 Y SYSAUX.290.977121353
DATAFILE MIRROR COARSE MAY 26 06:00:00 Y SYSTEM.291.977121353
DATAFILE MIRROR COARSE MAY 26 06:00:00 Y UNDOTBS1.289.977121353
DATAFILE MIRROR COARSE MAY 26 06:00:00 Y UNDOTBS2.288.977121353
DATAFILE MIRROR COARSE MAY 26 06:00:00 Y UNDOTBS3.287.977121353
DATAFILE MIRROR COARSE MAY 26 06:00:00 Y UNDOTBS4.286.977121353
DATAFILE MIRROR COARSE MAY 26 06:00:00 Y USERS.284.977121353
ASMCMD [+RECO/DBM01] > ls -l CONTROLFILE/
Type Redund Striped Time Sys Name
CONTROLFILE HIGH FINE MAY 26 06:00:00 Y Backup.283.977121353
CONTROLFILE HIGH FINE MAY 26 06:00:00 Y Backup.285.977120961
CONTROLFILE HIGH FINE MAY 26 06:00:00 Y Backup.293.977120965
Step 6: Switch Database to RMAN backup copy. This command will switch the database from +DATA to +RECO ASM Disk Group.
[oracle@dm01db01 ~]$ srvctl stop database -d dbm01
[oracle@dm01db01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat May 26 07:22:06 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 2.5655E+10 bytes
Fixed Size 2265224 bytes
Variable Size 4160753528 bytes
Database Buffers 2.1341E+10 bytes
Redo Buffers 151113728 bytes
Database mounted.
[oracle@dm01db01 ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Sat May 26 07:23:09 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: DBM01 (DBID=1180720008, not open)
RMAN> switch database to copy;
using target database control file instead of recovery catalog
datafile 1 switched to datafile copy "+RECO/dbm01/datafile/system.291.977121353"
datafile 2 switched to datafile copy "+RECO/dbm01/datafile/sysaux.290.977121353"
datafile 3 switched to datafile copy "+RECO/dbm01/datafile/undotbs1.289.977121353"
datafile 4 switched to datafile copy "+RECO/dbm01/datafile/undotbs2.288.977121353"
datafile 5 switched to datafile copy "+RECO/dbm01/datafile/undotbs3.287.977121353"
datafile 6 switched to datafile copy "+RECO/dbm01/datafile/undotbs4.286.977121353"
datafile 7 switched to datafile copy "+RECO/dbm01/datafile/users.284.977121353"
RMAN> recover database;
Starting recover at 26-MAY-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=991 instance=dbm011 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:02
Finished recover at 26-MAY-18
RMAN> alter database open resetlogs;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 05/26/2018 07:25:06
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
RMAN> alter database open;
database opened
[oracle@dm01db01 ~]$ srvctl stop database -d dbm01
[oracle@dm01db01 ~]$ srvctl start database -d dbm01
[oracle@dm01db01 ~]$ srvctl status database -d dbm01
Instance dbm011 is running on node dm01db01
Instance dbm012 is running on node dm01db02
Instance dbm013 is running on node dm01db03
Instance dbm014 is running on node dm01db04
[oracle@dm01db01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat May 26 07:28:11 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.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,database_role from gv$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
DBM01 READ WRITE PRIMARY
DBM01 READ WRITE PRIMARY
DBM01 READ WRITE PRIMARY
DBM01 READ WRITE PRIMARY
Step 7: Move Temp and online redo log files
SQL> alter database tempfile '+DATAC1/dbm01/tempfile/temp.262.976375229' drop;
Database altered.
SQL> alter tablespace TEMP add tempfile '+RECO' SIZE 1024M;
Tablespace altered.
SQL> alter database add logfile member '+RECO' to group 1;
Database altered.
SQL> alter database add logfile member '+RECO' to group 2;
Database altered.
SQL> alter database add logfile member '+RECO' to group 3;
Database altered.
SQL> alter database add logfile member '+RECO' to group 4;
Database altered.
SQL> alter database add logfile member '+RECO' to group 5;
Database altered.
SQL> alter database add logfile member '+RECO' to group 6;
Database altered.
SQL> alter database add logfile member '+RECO' to group 7;
Database altered.
SQL> alter database add logfile member '+RECO' to group 8;
Database altered.
SQL> alter database add logfile member '+RECO' to group 9;
Database altered.
SQL> alter database add logfile member '+RECO' to group 10;
Database altered.
SQL> alter database add logfile member '+RECO' to group 11;
Database altered.
SQL> alter database add logfile member '+RECO' to group 12;
Database altered.
SQL> alter database add logfile member '+RECO' to group 13;
Database altered.
SQL> alter database add logfile member '+RECO' to group 14;
Database altered.
SQL> alter database add logfile member '+RECO' to group 15;
Database altered.
SQL> alter database add logfile member '+RECO' to group 16;
Database altered.
SQL> alter database drop logfile member '+DATA/dbm01/onlinelog/group_1.257.976374733';
SQL> alter database drop logfile member '+DATA/dbm01/onlinelog/group_2.258.976374735';
SQL> alter database drop logfile member '+DATA/dbm01/onlinelog/group_7.267.976375073';
SQL> alter database drop logfile member '+DATA/dbm01/onlinelog/group_8.268.976375075';
SQL> alter database drop logfile member '+DATA/dbm01/onlinelog/group_5.269.976375079';
SQL> alter database drop logfile member '+DATA/dbm01/onlinelog/group_6.270.976375083';
SQL> alter database drop logfile member '+DATA/dbm01/onlinelog/group_3.271.976375085';
SQL> alter database drop logfile member '+DATA/dbm01/onlinelog/group_4.272.976375087';
SQL> alter database drop logfile member '+DATA/dbm01/onlinelog/group_9.274.976375205';
SQL> alter database drop logfile member '+DATA/dbm01/onlinelog/group_10.275.976375209';
SQL> alter database drop logfile member '+DATA/dbm01/onlinelog/group_11.276.976375211';
SQL> alter database drop logfile member '+DATA/dbm01/onlinelog/group_12.277.976375215';
SQL> alter database drop logfile member '+DATA/dbm01/onlinelog/group_13.278.976375217';
SQL> alter database drop logfile member '+DATA/dbm01/onlinelog/group_14.279.976375219';
SQL> alter database drop logfile member '+DATA/dbm01/onlinelog/group_15.280.976375223';
SQL> alter database drop logfile member '+DATA/dbm01/onlinelog/group_16.281.976375225';
Step 8: Move control file to +RECO Disk Group
[oracle@dm01db01 ~]$ srvctl stop database -d dbm01
[oracle@dm01db01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat May 26 08:53:35 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 2.5655E+10 bytes
Fixed Size 2265224 bytes
Variable Size 4429188984 bytes
Database Buffers 2.1072E+10 bytes
Redo Buffers 151113728 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
[oracle@dm01db01 ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Sat May 26 08:53:59 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: DBM01 (not mounted)
RMAN> restore controlfile to '+RECO' from '+DATA/dbm01/controlfile/current.256.976374731';
Starting restore at 26-MAY-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=969 instance=dbm011 device type=DISK
channel ORA_DISK_1: copied control file copy
Finished restore at 26-MAY-18
RMAN> exit
Recovery Manager complete.
[oracle@dm01db01 ~]$ . oraenv
ORACLE_SID = [dbm011] ? +ASM1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@dm01db01 ~]$ asmcmd -p
ASMCMD [+] > cd +RECO
ASMCMD [+RECO] > ls -l
Type Redund Striped Time Sys Name
Y DBM01/
ASMCMD [+RECO] > cd DBM01
ASMCMD [+RECO/DBM01] > ls -l
Type Redund Striped Time Sys Name
Y ARCHIVELOG/
Y AUTOBACKUP/
Y CHANGETRACKING/
Y CONTROLFILE/
Y DATAFILE/
Y ONLINELOG/
Y TEMPFILE/
N snapcf_dbm01.f => +RECO/DBM01/CONTROLFILE/Backup.285.977120961
ASMCMD [+RECO/DBM01] > cd CONTROLFILE/
ASMCMD [+RECO/DBM01/CONTROLFILE] > ls -l
Type Redund Striped Time Sys Name
CONTROLFILE HIGH FINE MAY 26 06:00:00 Y Backup.283.977121353
CONTROLFILE HIGH FINE MAY 26 08:00:00 Y Backup.285.977120961
CONTROLFILE HIGH FINE MAY 26 06:00:00 Y Backup.293.977120965
CONTROLFILE HIGH FINE MAY 26 08:00:00 Y Backup.321.977128799
CONTROLFILE HIGH FINE MAY 26 08:00:00 Y current.331.977129649
ASMCMD [+RECO/DBM01/CONTROLFILE] > pwd
+RECO/DBM01/CONTROLFILE
ASMCMD [+RECO/DBM01/CONTROLFILE] > exit
[oracle@dm01db01 ~]$ . oraenv
ORACLE_SID = [+ASM1] ? dbm011
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@dm01db01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat May 26 08:55:09 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> alter system set control_files='+RECO/DBM01/CONTROLFILE/current.331.977129649' scope=spfile sid='*';
System altered.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> exit
[oracle@dm01db01 ~]$ srvctl start database -d dbm01
[oracle@dm01db01 ~]$ srvctl status database -d dbm01
Instance dbm011 is running on node dm01db01
Instance dbm012 is running on node dm01db02
Instance dbm013 is running on node dm01db03
Instance dbm014 is running on node dm01db04
Step 9: Move block change tracking file to +RECO Disk Group
SQL> select filename from v$block_change_tracking;
FILENAME
--------------------------------------------------------------------
+DATA/dbm01/changetracking/ctf.282.976375227
SQL> alter database disable block change tracking;
Database altered.
SQL> alter database enable block change tracking using file '+RECO';
Database altered.
SQL> select filename from v$block_change_tracking;
FILENAME
--------------------------------------------------------------------
+RECO/dbm01/changetracking/ctf.319.977128195
Step 10: Move Flash Recovery Area to +RECO Disk Group
SQL> show parameter recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +DATA
SQL> alter system set db_recovery_file_dest='+RECO';
System altered.
SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +RECO
Step 11: Update OMF parameter to point to +RECO
SQL> show parameter online
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_online_log_dest_1 string +DATA
SQL> alter system set db_create_online_log_dest_1='+RECO';
System altered.
SQL> show parameter db_create_online_log_dest_1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_online_log_dest_1 string +RECO
Step 12: Verify the entire database is moved to +RECO ASM Disk Group
[oracle@dm01db01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat May 26 08:57:57 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.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,database_role from gv$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
DBM01 READ WRITE PRIMARY
DBM01 READ WRITE PRIMARY
DBM01 READ WRITE PRIMARY
DBM01 READ WRITE PRIMARY
SQL> set lines 200
SQL> set pages 200
SQL> select name from v$tempfile;
NAME
-------------------------------------------------------
+RECO/dbm01/tempfile/temp.297.977125145
SQL> select name from v$controlfile;
NAME
-------------------------------------------------------
+RECO/dbm01/controlfile/current.331.977129649
SQL> select name from v$datafile;
NAME
--------------------------------------------------------
+RECO/dbm01/datafile/system.291.977121353
+RECO/dbm01/datafile/sysaux.290.977121353
+RECO/dbm01/datafile/undotbs1.289.977121353
+RECO/dbm01/datafile/undotbs2.288.977121353
+RECO/dbm01/datafile/undotbs3.287.977121353
+RECO/dbm01/datafile/undotbs4.286.977121353
+RECO/dbm01/datafile/users.284.977121353
7 rows selected.
SQL> select member from v$logfile;
MEMBER
---------------------------------------------------------
+RECO/dbm01/onlinelog/group_1.298.977127719
+RECO/dbm01/onlinelog/group_2.299.977125295
+RECO/dbm01/onlinelog/group_3.300.977125299
+RECO/dbm01/onlinelog/group_4.301.977125309
+RECO/dbm01/onlinelog/group_5.302.977125313
+RECO/dbm01/onlinelog/group_6.303.977125317
+RECO/dbm01/onlinelog/group_7.304.977125321
+RECO/dbm01/onlinelog/group_8.305.977125327
+RECO/dbm01/onlinelog/group_9.306.977125329
+RECO/dbm01/onlinelog/group_10.307.977125333
+RECO/dbm01/onlinelog/group_11.308.977125335
+RECO/dbm01/onlinelog/group_12.309.977125339
+RECO/dbm01/onlinelog/group_13.310.977125343
+RECO/dbm01/onlinelog/group_14.311.977125345
+RECO/dbm01/onlinelog/group_15.312.977125349
+RECO/dbm01/onlinelog/group_16.313.977125351
16 rows selected.
Conclusion
In this article we have learned how to move a Database from +DATA ASM Disk Group to +RECO Disk Group. Using RMAN along with FRA makes it easy to move a database from one location to another.
+DATA for Database Files
+RECO for Online Redo log and Archive log files
+DBFS_DG for Cluster configuration files such as OCR and Voting disks
In a Customized environment Customers can choose to have more than 3 Disk Groups. But it is recommended to have 3 Disk Groups. The DATA and RECO disk groups can be sized 80%-20% or 40%-60% respectively of over all storage capacity. Sometimes it is possible that +DATA disk group can be filled very fast if you have several databases.
In this article we will demostrate how to move a Database from +DATA disk group to +RECO disk group.
Steps to move a database from +DATA to +RECO ASM Disk Group:
Step 1: Get the ASM Disk Information
SQL> select state,name from v$asm_diskgroup;
STATE NAME
----------- ------------------------------
MOUNTED RECO
MOUNTED DBFS_DG
MOUNTED DATA
Step 2: Get the Database files details
SQL> select name, open_mode,database_role from gv$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
DBM01 READ WRITE PRIMARY
DBM01 READ WRITE PRIMARY
DBM01 READ WRITE PRIMARY
DBM01 READ WRITE PRIMARY
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
+DATA/dbm01/controlfile/current.256.976374731
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA/dbm01/datafile/system.259.976374739
+DATA/dbm01/datafile/sysaux.260.976374743
+DATA/dbm01/datafile/undotbs1.261.976374745
+DATA/dbm01/datafile/undotbs2.263.976374753
+DATA/dbm01/datafile/undotbs3.264.976374755
+DATA/dbm01/datafile/undotbs4.265.976374757
+DATA/dbm01/datafile/users.266.976374757
7 rows selected.
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
+DATAC1/dbm01/tempfile/temp.262.976375229
SQL>
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
+DATA/dbm01/onlinelog/group_1.257.976374733
+DATA/dbm01/onlinelog/group_2.258.976374735
+DATA/dbm01/onlinelog/group_7.267.976375073
+DATA/dbm01/onlinelog/group_8.268.976375075
+DATA/dbm01/onlinelog/group_5.269.976375079
+DATA/dbm01/onlinelog/group_6.270.976375083
+DATA/dbm01/onlinelog/group_3.271.976375085
+DATA/dbm01/onlinelog/group_4.272.976375087
+DATA/dbm01/onlinelog/group_9.274.976375205
+DATA/dbm01/onlinelog/group_10.275.976375209
+DATA/dbm01/onlinelog/group_11.276.976375211
+DATA/dbm01/onlinelog/group_12.277.976375215
+DATA/dbm01/onlinelog/group_13.278.976375217
+DATA/dbm01/onlinelog/group_14.279.976375219
+DATA/dbm01/onlinelog/group_15.280.976375223
+DATA/dbm01/onlinelog/group_16.281.976375225
16 rows selected.
SQL> select filename from v$block_change_tracking;
FILENAME
--------------------------------------------------------------------
+DATA/dbm01/changetracking/ctf.282.976375227
Step 3: Backup Database using RMAN copy command as shown below. Here we are moving database to +RECO ASM Disk Group.
RMAN> run {
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
allocate channel c5 device type disk;
allocate channel c6 device type disk;
allocate channel c7 device type disk;
allocate channel c8 device type disk;
backup as copy database include current controlfile format '+RECO';
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;
release channel c7;
release channel c8;
}
released channel: ORA_DISK_1
allocated channel: c1
channel c1: SID=1189 instance=dbm011 device type=DISK
allocated channel: c2
channel c2: SID=1321 instance=dbm011 device type=DISK
allocated channel: c3
channel c3: SID=1343 instance=dbm011 device type=DISK
allocated channel: c4
channel c4: SID=1387 instance=dbm011 device type=DISK
allocated channel: c5
channel c5: SID=1497 instance=dbm011 device type=DISK
allocated channel: c6
channel c6: SID=1519 instance=dbm011 device type=DISK
allocated channel: c7
channel c7: SID=1541 instance=dbm011 device type=DISK
allocated channel: c8
channel c8: SID=1563 instance=dbm011 device type=DISK
Starting backup at 26-MAY-18
channel c1: starting datafile copy
input datafile file number=00001 name=+DATA/dbm01/datafile/system.259.976374739
channel c2: starting datafile copy
input datafile file number=00002 name=+DATA/dbm01/datafile/sysaux.260.976374743
channel c3: starting datafile copy
input datafile file number=00003 name=+DATA/dbm01/datafile/undotbs1.261.976374745
channel c4: starting datafile copy
input datafile file number=00004 name=+DATA/dbm01/datafile/undotbs2.263.976374753
channel c5: starting datafile copy
input datafile file number=00005 name=+DATA/dbm01/datafile/undotbs3.264.976374755
channel c6: starting datafile copy
input datafile file number=00006 name=+DATA/dbm01/datafile/undotbs4.265.976374757
channel c7: starting datafile copy
input datafile file number=00007 name=+DATA/dbm01/datafile/users.266.976374757
channel c8: starting datafile copy
copying current control file
output file name=+RECO/dbm01/datafile/users.284.977121353 tag=TAG20180526T063551 RECID=16 STAMP=977121353
channel c7: datafile copy complete, elapsed time: 00:00:02
output file name=+RECO/dbm01/controlfile/backup.283.977121353 tag=TAG20180526T063551 RECID=17 STAMP=977121353
channel c8: datafile copy complete, elapsed time: 00:00:01
output file name=+RECO/dbm01/datafile/system.291.977121353 tag=TAG20180526T063551 RECID=18 STAMP=977121389
channel c1: datafile copy complete, elapsed time: 00:00:46
output file name=+RECO/dbm01/datafile/sysaux.290.977121353 tag=TAG20180526T063551 RECID=23 STAMP=977121392
channel c2: datafile copy complete, elapsed time: 00:00:46
output file name=+RECO/dbm01/datafile/undotbs1.289.977121353 tag=TAG20180526T063551 RECID=21 STAMP=977121392
channel c3: datafile copy complete, elapsed time: 00:00:46
output file name=+RECO/dbm01/datafile/undotbs2.288.977121353 tag=TAG20180526T063551 RECID=19 STAMP=977121392
channel c4: datafile copy complete, elapsed time: 00:00:46
output file name=+RECO/dbm01/datafile/undotbs3.287.977121353 tag=TAG20180526T063551 RECID=20 STAMP=977121392
channel c5: datafile copy complete, elapsed time: 00:00:46
output file name=+RECO/dbm01/datafile/undotbs4.286.977121353 tag=TAG20180526T063551 RECID=22 STAMP=977121392
channel c6: datafile copy complete, elapsed time: 00:00:46
Finished backup at 26-MAY-18
Starting Control File and SPFILE Autobackup at 26-MAY-18
piece handle=+RECO/dbm01/autobackup/2018_05_26/s_977121397.282.977121399 comment=NONE
Finished Control File and SPFILE Autobackup at 26-MAY-18
released channel: c1
released channel: c2
released channel: c3
released channel: c4
released channel: c5
released channel: c6
released channel: c7
released channel: c8
Step 4: Verify the RMAN Database Copy using RMAN
RMAN> list copy of database;
List of Datafile Copies
=======================
Key File S Completion Time Ckp SCN Ckp Time
------- ---- - --------------- ---------- ---------------
18 1 A 26-MAY-18 1330853 26-MAY-18
Name: +RECO/dbm01/datafile/system.291.977121353
Tag: TAG20180526T063551
9 1 A 26-MAY-18 1330410 26-MAY-18
Name: +RECO/dbm01/datafile/system.286.977120961
Tag: TAG20180526T062919
3 1 A 26-MAY-18 1330155 26-MAY-18
Name: +RECO/dbm01/datafile/system.280.977120795
Tag: TAG20180526T062633
23 2 A 26-MAY-18 1330856 26-MAY-18
Name: +RECO/dbm01/datafile/sysaux.290.977121353
Tag: TAG20180526T063551
12 2 A 26-MAY-18 1330413 26-MAY-18
Name: +RECO/dbm01/datafile/sysaux.287.977120961
Tag: TAG20180526T062919
2 2 A 26-MAY-18 1330158 26-MAY-18
Name: +RECO/dbm01/datafile/sysaux.281.977120795
Tag: TAG20180526T062633
21 3 A 26-MAY-18 1330859 26-MAY-18
Name: +RECO/dbm01/datafile/undotbs1.289.977121353
Tag: TAG20180526T063551
11 3 A 26-MAY-18 1330416 26-MAY-18
Name: +RECO/dbm01/datafile/undotbs1.288.977120961
Tag: TAG20180526T062919
4 3 A 26-MAY-18 1330154 26-MAY-18
Name: +RECO/dbm01/datafile/undotbs1.279.977120795
Tag: TAG20180526T062633
19 4 A 26-MAY-18 1330862 26-MAY-18
Name: +RECO/dbm01/datafile/undotbs2.288.977121353
Tag: TAG20180526T063551
10 4 A 26-MAY-18 1330419 26-MAY-18
Name: +RECO/dbm01/datafile/undotbs2.289.977120961
Tag: TAG20180526T062919
1 4 A 26-MAY-18 1330153 26-MAY-18
Name: +RECO/dbm01/datafile/undotbs2.278.977120795
Tag: TAG20180526T062633
20 5 A 26-MAY-18 1330865 26-MAY-18
Name: +RECO/dbm01/datafile/undotbs3.287.977121353
Tag: TAG20180526T063551
13 5 A 26-MAY-18 1330422 26-MAY-18
Name: +RECO/dbm01/datafile/undotbs3.290.977120961
Tag: TAG20180526T062919
7 5 A 26-MAY-18 1330184 26-MAY-18
Name: +RECO/dbm01/datafile/undotbs3.282.977120829
Tag: TAG20180526T062633
22 6 A 26-MAY-18 1330868 26-MAY-18
Name: +RECO/dbm01/datafile/undotbs4.286.977121353
Tag: TAG20180526T063551
15 6 A 26-MAY-18 1330425 26-MAY-18
Name: +RECO/dbm01/datafile/undotbs4.291.977120961
Tag: TAG20180526T062919
6 6 A 26-MAY-18 1330187 26-MAY-18
Name: +RECO/dbm01/datafile/undotbs4.283.977120829
Tag: TAG20180526T062633
16 7 A 26-MAY-18 1330871 26-MAY-18
Name: +RECO/dbm01/datafile/users.284.977121353
Tag: TAG20180526T063551
8 7 A 26-MAY-18 1330428 26-MAY-18
Name: +RECO/dbm01/datafile/users.292.977120961
Tag: TAG20180526T062919
5 7 A 26-MAY-18 1330190 26-MAY-18
Name: +RECO/dbm01/datafile/users.284.977120829
Tag: TAG20180526T062633
RMAN> list copy of controlfile;
List of Control File Copies
===========================
Key S Completion Time Ckp SCN Ckp Time
------- - --------------- ---------- ---------------
17 A 26-MAY-18 1330876 26-MAY-18
Name: +RECO/dbm01/controlfile/backup.283.977121353
Tag: TAG20180526T063551
14 A 26-MAY-18 1330434 26-MAY-18
Name: +RECO/dbm01/controlfile/backup.293.977120965
Tag: TAG20180526T062919
Step 5: Verify the RMAN Database Copy backup in ASM
[oracle@dm01db01 ~]$ asmcmd -p
ASMCMD [+] > lsdg
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED HIGH N 512 4096 4194304 272154624 271558968 6479872 88359698 0 N DATA/
MOUNTED HIGH N 512 4096 4194304 2404640 2402468 68704 777921 0 Y DBFS_DG/
MOUNTED NORMAL N 512 4096 4194304 45389568 45183784 540352 22321716 0 N RECO/
ASMCMD [+] > cd +RECO
ASMCMD [+RECO] > ls -l
Type Redund Striped Time Sys Name
Y DBM01/
ASMCMD [+RECO] > cd DBM01
ASMCMD [+RECO/DBM01] > ls -l
Type Redund Striped Time Sys Name
Y ARCHIVELOG/
Y AUTOBACKUP/
Y CONTROLFILE/
Y DATAFILE/
N snapcf_dbm01.f => +RECO/DBM01/CONTROLFILE/Backup.285.977120961
ASMCMD [+RECO/DBM01] > ls -l DATAFILE/
Type Redund Striped Time Sys Name
DATAFILE MIRROR COARSE MAY 26 06:00:00 Y SYSAUX.290.977121353
DATAFILE MIRROR COARSE MAY 26 06:00:00 Y SYSTEM.291.977121353
DATAFILE MIRROR COARSE MAY 26 06:00:00 Y UNDOTBS1.289.977121353
DATAFILE MIRROR COARSE MAY 26 06:00:00 Y UNDOTBS2.288.977121353
DATAFILE MIRROR COARSE MAY 26 06:00:00 Y UNDOTBS3.287.977121353
DATAFILE MIRROR COARSE MAY 26 06:00:00 Y UNDOTBS4.286.977121353
DATAFILE MIRROR COARSE MAY 26 06:00:00 Y USERS.284.977121353
ASMCMD [+RECO/DBM01] > ls -l CONTROLFILE/
Type Redund Striped Time Sys Name
CONTROLFILE HIGH FINE MAY 26 06:00:00 Y Backup.283.977121353
CONTROLFILE HIGH FINE MAY 26 06:00:00 Y Backup.285.977120961
CONTROLFILE HIGH FINE MAY 26 06:00:00 Y Backup.293.977120965
Step 6: Switch Database to RMAN backup copy. This command will switch the database from +DATA to +RECO ASM Disk Group.
[oracle@dm01db01 ~]$ srvctl stop database -d dbm01
[oracle@dm01db01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat May 26 07:22:06 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 2.5655E+10 bytes
Fixed Size 2265224 bytes
Variable Size 4160753528 bytes
Database Buffers 2.1341E+10 bytes
Redo Buffers 151113728 bytes
Database mounted.
[oracle@dm01db01 ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Sat May 26 07:23:09 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: DBM01 (DBID=1180720008, not open)
RMAN> switch database to copy;
using target database control file instead of recovery catalog
datafile 1 switched to datafile copy "+RECO/dbm01/datafile/system.291.977121353"
datafile 2 switched to datafile copy "+RECO/dbm01/datafile/sysaux.290.977121353"
datafile 3 switched to datafile copy "+RECO/dbm01/datafile/undotbs1.289.977121353"
datafile 4 switched to datafile copy "+RECO/dbm01/datafile/undotbs2.288.977121353"
datafile 5 switched to datafile copy "+RECO/dbm01/datafile/undotbs3.287.977121353"
datafile 6 switched to datafile copy "+RECO/dbm01/datafile/undotbs4.286.977121353"
datafile 7 switched to datafile copy "+RECO/dbm01/datafile/users.284.977121353"
RMAN> recover database;
Starting recover at 26-MAY-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=991 instance=dbm011 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:02
Finished recover at 26-MAY-18
RMAN> alter database open resetlogs;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 05/26/2018 07:25:06
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
RMAN> alter database open;
database opened
[oracle@dm01db01 ~]$ srvctl stop database -d dbm01
[oracle@dm01db01 ~]$ srvctl start database -d dbm01
[oracle@dm01db01 ~]$ srvctl status database -d dbm01
Instance dbm011 is running on node dm01db01
Instance dbm012 is running on node dm01db02
Instance dbm013 is running on node dm01db03
Instance dbm014 is running on node dm01db04
[oracle@dm01db01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat May 26 07:28:11 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.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,database_role from gv$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
DBM01 READ WRITE PRIMARY
DBM01 READ WRITE PRIMARY
DBM01 READ WRITE PRIMARY
DBM01 READ WRITE PRIMARY
Step 7: Move Temp and online redo log files
SQL> alter database tempfile '+DATAC1/dbm01/tempfile/temp.262.976375229' drop;
Database altered.
SQL> alter tablespace TEMP add tempfile '+RECO' SIZE 1024M;
Tablespace altered.
SQL> alter database add logfile member '+RECO' to group 1;
Database altered.
SQL> alter database add logfile member '+RECO' to group 2;
Database altered.
SQL> alter database add logfile member '+RECO' to group 3;
Database altered.
SQL> alter database add logfile member '+RECO' to group 4;
Database altered.
SQL> alter database add logfile member '+RECO' to group 5;
Database altered.
SQL> alter database add logfile member '+RECO' to group 6;
Database altered.
SQL> alter database add logfile member '+RECO' to group 7;
Database altered.
SQL> alter database add logfile member '+RECO' to group 8;
Database altered.
SQL> alter database add logfile member '+RECO' to group 9;
Database altered.
SQL> alter database add logfile member '+RECO' to group 10;
Database altered.
SQL> alter database add logfile member '+RECO' to group 11;
Database altered.
SQL> alter database add logfile member '+RECO' to group 12;
Database altered.
SQL> alter database add logfile member '+RECO' to group 13;
Database altered.
SQL> alter database add logfile member '+RECO' to group 14;
Database altered.
SQL> alter database add logfile member '+RECO' to group 15;
Database altered.
SQL> alter database add logfile member '+RECO' to group 16;
Database altered.
SQL> alter database drop logfile member '+DATA/dbm01/onlinelog/group_1.257.976374733';
SQL> alter database drop logfile member '+DATA/dbm01/onlinelog/group_2.258.976374735';
SQL> alter database drop logfile member '+DATA/dbm01/onlinelog/group_7.267.976375073';
SQL> alter database drop logfile member '+DATA/dbm01/onlinelog/group_8.268.976375075';
SQL> alter database drop logfile member '+DATA/dbm01/onlinelog/group_5.269.976375079';
SQL> alter database drop logfile member '+DATA/dbm01/onlinelog/group_6.270.976375083';
SQL> alter database drop logfile member '+DATA/dbm01/onlinelog/group_3.271.976375085';
SQL> alter database drop logfile member '+DATA/dbm01/onlinelog/group_4.272.976375087';
SQL> alter database drop logfile member '+DATA/dbm01/onlinelog/group_9.274.976375205';
SQL> alter database drop logfile member '+DATA/dbm01/onlinelog/group_10.275.976375209';
SQL> alter database drop logfile member '+DATA/dbm01/onlinelog/group_11.276.976375211';
SQL> alter database drop logfile member '+DATA/dbm01/onlinelog/group_12.277.976375215';
SQL> alter database drop logfile member '+DATA/dbm01/onlinelog/group_13.278.976375217';
SQL> alter database drop logfile member '+DATA/dbm01/onlinelog/group_14.279.976375219';
SQL> alter database drop logfile member '+DATA/dbm01/onlinelog/group_15.280.976375223';
SQL> alter database drop logfile member '+DATA/dbm01/onlinelog/group_16.281.976375225';
Step 8: Move control file to +RECO Disk Group
[oracle@dm01db01 ~]$ srvctl stop database -d dbm01
[oracle@dm01db01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat May 26 08:53:35 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 2.5655E+10 bytes
Fixed Size 2265224 bytes
Variable Size 4429188984 bytes
Database Buffers 2.1072E+10 bytes
Redo Buffers 151113728 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
[oracle@dm01db01 ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Sat May 26 08:53:59 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: DBM01 (not mounted)
RMAN> restore controlfile to '+RECO' from '+DATA/dbm01/controlfile/current.256.976374731';
Starting restore at 26-MAY-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=969 instance=dbm011 device type=DISK
channel ORA_DISK_1: copied control file copy
Finished restore at 26-MAY-18
RMAN> exit
Recovery Manager complete.
[oracle@dm01db01 ~]$ . oraenv
ORACLE_SID = [dbm011] ? +ASM1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@dm01db01 ~]$ asmcmd -p
ASMCMD [+] > cd +RECO
ASMCMD [+RECO] > ls -l
Type Redund Striped Time Sys Name
Y DBM01/
ASMCMD [+RECO] > cd DBM01
ASMCMD [+RECO/DBM01] > ls -l
Type Redund Striped Time Sys Name
Y ARCHIVELOG/
Y AUTOBACKUP/
Y CHANGETRACKING/
Y CONTROLFILE/
Y DATAFILE/
Y ONLINELOG/
Y TEMPFILE/
N snapcf_dbm01.f => +RECO/DBM01/CONTROLFILE/Backup.285.977120961
ASMCMD [+RECO/DBM01] > cd CONTROLFILE/
ASMCMD [+RECO/DBM01/CONTROLFILE] > ls -l
Type Redund Striped Time Sys Name
CONTROLFILE HIGH FINE MAY 26 06:00:00 Y Backup.283.977121353
CONTROLFILE HIGH FINE MAY 26 08:00:00 Y Backup.285.977120961
CONTROLFILE HIGH FINE MAY 26 06:00:00 Y Backup.293.977120965
CONTROLFILE HIGH FINE MAY 26 08:00:00 Y Backup.321.977128799
CONTROLFILE HIGH FINE MAY 26 08:00:00 Y current.331.977129649
ASMCMD [+RECO/DBM01/CONTROLFILE] > pwd
+RECO/DBM01/CONTROLFILE
ASMCMD [+RECO/DBM01/CONTROLFILE] > exit
[oracle@dm01db01 ~]$ . oraenv
ORACLE_SID = [+ASM1] ? dbm011
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@dm01db01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat May 26 08:55:09 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> alter system set control_files='+RECO/DBM01/CONTROLFILE/current.331.977129649' scope=spfile sid='*';
System altered.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> exit
[oracle@dm01db01 ~]$ srvctl start database -d dbm01
[oracle@dm01db01 ~]$ srvctl status database -d dbm01
Instance dbm011 is running on node dm01db01
Instance dbm012 is running on node dm01db02
Instance dbm013 is running on node dm01db03
Instance dbm014 is running on node dm01db04
Step 9: Move block change tracking file to +RECO Disk Group
SQL> select filename from v$block_change_tracking;
FILENAME
--------------------------------------------------------------------
+DATA/dbm01/changetracking/ctf.282.976375227
SQL> alter database disable block change tracking;
Database altered.
SQL> alter database enable block change tracking using file '+RECO';
Database altered.
SQL> select filename from v$block_change_tracking;
FILENAME
--------------------------------------------------------------------
+RECO/dbm01/changetracking/ctf.319.977128195
Step 10: Move Flash Recovery Area to +RECO Disk Group
SQL> show parameter recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +DATA
SQL> alter system set db_recovery_file_dest='+RECO';
System altered.
SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +RECO
Step 11: Update OMF parameter to point to +RECO
SQL> show parameter online
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_online_log_dest_1 string +DATA
SQL> alter system set db_create_online_log_dest_1='+RECO';
System altered.
SQL> show parameter db_create_online_log_dest_1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_online_log_dest_1 string +RECO
Step 12: Verify the entire database is moved to +RECO ASM Disk Group
[oracle@dm01db01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat May 26 08:57:57 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.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,database_role from gv$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
DBM01 READ WRITE PRIMARY
DBM01 READ WRITE PRIMARY
DBM01 READ WRITE PRIMARY
DBM01 READ WRITE PRIMARY
SQL> set lines 200
SQL> set pages 200
SQL> select name from v$tempfile;
NAME
-------------------------------------------------------
+RECO/dbm01/tempfile/temp.297.977125145
SQL> select name from v$controlfile;
NAME
-------------------------------------------------------
+RECO/dbm01/controlfile/current.331.977129649
SQL> select name from v$datafile;
NAME
--------------------------------------------------------
+RECO/dbm01/datafile/system.291.977121353
+RECO/dbm01/datafile/sysaux.290.977121353
+RECO/dbm01/datafile/undotbs1.289.977121353
+RECO/dbm01/datafile/undotbs2.288.977121353
+RECO/dbm01/datafile/undotbs3.287.977121353
+RECO/dbm01/datafile/undotbs4.286.977121353
+RECO/dbm01/datafile/users.284.977121353
7 rows selected.
SQL> select member from v$logfile;
MEMBER
---------------------------------------------------------
+RECO/dbm01/onlinelog/group_1.298.977127719
+RECO/dbm01/onlinelog/group_2.299.977125295
+RECO/dbm01/onlinelog/group_3.300.977125299
+RECO/dbm01/onlinelog/group_4.301.977125309
+RECO/dbm01/onlinelog/group_5.302.977125313
+RECO/dbm01/onlinelog/group_6.303.977125317
+RECO/dbm01/onlinelog/group_7.304.977125321
+RECO/dbm01/onlinelog/group_8.305.977125327
+RECO/dbm01/onlinelog/group_9.306.977125329
+RECO/dbm01/onlinelog/group_10.307.977125333
+RECO/dbm01/onlinelog/group_11.308.977125335
+RECO/dbm01/onlinelog/group_12.309.977125339
+RECO/dbm01/onlinelog/group_13.310.977125343
+RECO/dbm01/onlinelog/group_14.311.977125345
+RECO/dbm01/onlinelog/group_15.312.977125349
+RECO/dbm01/onlinelog/group_16.313.977125351
16 rows selected.
Conclusion
In this article we have learned how to move a Database from +DATA ASM Disk Group to +RECO Disk Group. Using RMAN along with FRA makes it easy to move a database from one location to another.
No comments:
Post a Comment