In this blog, I am going to demonstrate how to move database from one location to another within ASM.
This situation arises for me because I have renamed one database and now wanted to move it to location in ASM matching database name.
Database was renamed (Using NID utility) from “BR91DMO” to “BRDMO”. Now we need to move the database files to the new location in ASM as well.
Source Database Files Location: +DATA/br91dmo
Target Database Files Location: +DATA/brdmo
1. Shutdown Immediate
2. Startup Database in Mount State (Since My database is in No archive log mode).
3. Connect to RMAN using Target Database.
4. Backup Database as copy.
[code]
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> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size 2228200 bytes
Variable Size 1224736792 bytes
Database Buffers 905969664 bytes
Redo Buffers 4952064 bytes
Database mounted.
SQL> exit
Disconnected from 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
[/code]
Connect To RMAN to take Backup. Since I am going to have “BRDMO” (Same as database name) directory under “+DATA” diskgroup, I will use ‘+DATA’ as format for the Backup command. It will create directory with database name under DATA.
[code]
RMAN> connect target /
connected to target database: BRDMO (DBID=2852526159, not open)
RMAN> backup as copy database format ‘+DATA’;
Starting backup at 2012-09-13:01:43:59
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=394 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=8 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=202 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=580 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00142 name=+DATA/br91dmo/datafile/psindex.dbf
channel ORA_DISK_2: starting datafile copy
input datafile file number=00003 name=+DATA/br91dmo/datafile/psundo01.dbf
channel ORA_DISK_3: starting datafile copy
input datafile file number=00140 name=+DATA/br91dmo/datafile/psimage.dbf
channel ORA_DISK_4: starting datafile copy
input datafile file number=00001 name=+DATA/br91dmo/datafile/system01.dbf
output file name=+DATA/brdmo/datafile/system.7589.793849447 tag=TAG20120913T014403 RECID=25 STAMP=793849519
channel ORA_DISK_4: datafile copy complete, elapsed time: 00:01:15
channel ORA_DISK_4: starting datafile copy
.
.
.
[/code]
5. Now, modify your initfile/spfile to change the location of controlfiles to new location i.e from “br91dmo” directory to “brdmo” directory. Also change location for other Parameters like ‘ADUMP’ etc…and make sure to create the directories which are not on ASM.
6. Now We have to restore controlfile from old location to New location using RMAN.
[code]
RMAN> connect target /
connected to target database: BRDMO (not mounted)
RMAN> restore controlfile from ‘+data/br91dmo/controlfile/Current.8124.775838355’;
Starting restore at 2012-09-13:01:58:14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=201 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=+DATA/brdmo/controlfile/control01.ctl
output file name=+FRA/brdmo/controlfile/control02.ctl
Finished restore at 2012-09-13:01:58:16
RMAN> exit
[/code]
7. Mount the database.
8. Connect to Rman using Target database.
9. Now just to confirm Issue following command “List copy of database”. You should see the backup copies of all the datafiles under ‘+data/brdmo….’ Location.
[code]
RMAN> list copy of database;
using target database control file instead of recovery catalog
List of Datafile Copies
=======================
Key File S Completion Time Ckp SCN Ckp Time
——- —- – ——————- ———- ——————-
25 1 A 2012-09-13:01:45:19 27190070 2012-09-13:01:28:00
Name: +DATA/brdmo/datafile/system.7589.793849447
Tag: TAG20120913T014403
1 1 A 2012-09-13:01:33:24 27190070 2012-09-13:01:28:00
Name: /backup/BRDMO/BRDMO_20120913_793848681_211_1
Tag: TAG20120913T013116
31 2 A 2012-09-13:01:46:38 27190070 2012-09-13:01:28:00
Name: +DATA/brdmo/datafile/sysaux.6601.793849571
Tag: TAG20120913T014403
4 2 A 2012-09-13:01:35:17 27190070 2012-09-13:01:28:00
Name: /backup/BRDMO/BRDMO_20120913_793848872_213_1
Tag: TAG20120913T013116
[/code]
10. Now Switch the datafiles to Copy.
[code]
RMAN> switch database to copy;
datafile 1 switched to datafile copy "+DATA/brdmo/datafile/system.7589.793849447"
datafile 2 switched to datafile copy "+DATA/brdmo/datafile/sysaux.6601.793849571"
datafile 3 switched to datafile copy "+DATA/brdmo/datafile/psundo.7583.793849447"
datafile 4 switched to datafile copy "+DATA/brdmo/datafile/psdefault.7725.793849697"
datafile 5 switched to datafile copy "+DATA/brdmo/datafile/amapp.7720.793849637"
datafile 6 switched to datafile copy "+DATA/brdmo/datafile/amarch.7592.793849775"
datafile 7 switched to datafile copy "+DATA/brdmo/datafile/amlarge.7677.793849685"
datafile 8 switched to datafile copy "+DATA/brdmo/datafile/amwork.7604.793849679"
datafile 9 switched to datafile copy "+DATA/brdmo/datafile/apapp.7582.793849645"
datafile 10 switched to datafile copy "+DATA/brdmo/datafile/aparch.7584.793849769"
datafile 11 switched to datafile copy "+DATA/brdmo/datafile/aplarge.7700.793849659"
datafile 12 switched to datafile copy "+DATA/brdmo/datafile/apwork.7701.793849621"
datafile 13 switched to datafile copy "+DATA/brdmo/datafile/arapp.7615.793849705"
datafile 14 switched to datafile copy "+DATA/brdmo/datafile/ararch.7624.793849771"
datafile 15 switched to datafile copy "+DATA/brdmo/datafile/arlarge.757.793849703"
datafile 16 switched to datafile copy "+DATA/brdmo/datafile/arwork.7709.793849625"
datafile 17 switched to datafile copy "+DATA/brdmo/datafile/aucapp.7678.793849743"
datafile 18 switched to datafile copy "+DATA/brdmo/datafile/auclrge.960.793849775"
datafile 19 switched to datafile copy "+DATA/brdmo/datafile/bcapp.7749.793849775"
datafile 20 switched to datafile copy "+DATA/brdmo/datafile/bdapp.7676.793849757"
datafile 21 switched to datafile copy "+DATA/brdmo/datafile/bdlarge.7745.793849771"
datafile 22 switched to datafile copy "+DATA/brdmo/datafile/biapp.761.793849737"
datafile 23 switched to datafile copy "+DATA/brdmo/datafile/bilarge.7757.793849729"
[/code]
11. Now Connect to sql plus and Open the database.
12. Check datafile location from V$datafile and make sure all the files are pointing to correct location.
13. Perform following steps to add temporary file. Since temp file is there in the old location, we have to add one more tempfile and then drop the old one.
[code]
SQL> select name from v$tempfile;
NAME
——————————————————————————–
+DATA/br91dmo/tempfile/pstemp01.dbf
SQL> alter tablespace pstemp add tempfile ‘+DATA/brdmo/tempfile/pstemp02.dbf’ size 1G autoextend on;
Tablespace altered.
SQL> drop tablespace temp including contents and datafiles;
Tablespace dropped.
SQL> alter tablespace pstemp drop tempfile ‘+DATA/br91dmo/tempfile/pstemp01.dbf’;
Tablespace altered.
SQL> select name from v$tempfile;
NAME
——————————————————————————–
+DATA/brdmo/tempfile/pstemp02.dbf
[/code]
14. Now we are left with Redo Log files.
For this, I just added few more redo log groups as per my requirement in the New location and then dropped the old one.
And finally We are done……