We have already ASM instance and 2 asm diskgroups DATA and FLSHBK
Our database name is mydb and it works Archivelog mode.
Be sure block change tracking is disabled
SQL> select status from v$block_change_tracking;
STATUS
----------
DISABLED
SQL>
If no, disable it
SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
Database altered.
SQL>
Firstly check filenames on the database using this SQL:
SQL> select member from v$logfile
union
select file_name from dba_data_files
union
select name from v$controlfile
union
select value from v$parameter where name='spfile' order by 1; 2 3 4 5 6 7
MEMBER
--------------------------------------------------------------------------------
/home/oracle/oracle/product/10.2.0/db_1/dbs/spfilemydb.ora
/home/oracle/oracle/product/10.2.0/oradata/mydb/control01.ctl
/home/oracle/oracle/product/10.2.0/oradata/mydb/control02.ctl
/home/oracle/oracle/product/10.2.0/oradata/mydb/control03.ctl
/home/oracle/oracle/product/10.2.0/oradata/mydb/example01.dbf
/home/oracle/oracle/product/10.2.0/oradata/mydb/redo01.log
/home/oracle/oracle/product/10.2.0/oradata/mydb/redo02.log
/home/oracle/oracle/product/10.2.0/oradata/mydb/redo03.log
/home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf
/home/oracle/oracle/product/10.2.0/oradata/mydb/system01.dbf
/home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf
/home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf
12 rows selected.
SQL>
Let`s go.
Shutdown database :
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
Create pfile from spfile and edit
SQL> create pfile from spfile;
File created.
SQL>
[oracle@localhost dbs]$ gedit initmydb.ora
..
*.control_files=(+DATA, +FLSHBK)
...
Save and exit, then create spfile from pfile
SQL> create spfile from pfile;
File created.
SQL>
Now startup database with nomount status via RMAN and restore control file from fresh backup:
[oracle@localhost dbs]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue Nov 13 11:19:43 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
RMAN> startup nomount
Oracle instance started
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 83887696 bytes
Database Buffers 197132288 bytes
Redo Buffers 2973696 bytes
RMAN> restore controlfile from '/home/oracle/oracle/product/10.2.0/db_1/dbs/control.ctl';
Starting restore at 13-NOV-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: copied control file copy
output filename=+DATA/mydb/controlfile/current.256.799240855
output filename=+FLSHBK/mydb/controlfile/current.256.799240867
Finished restore at 13-NOV-12
RMAN>
Now our control files are located in two ASM disk gorups.
Open database with RESETLOGS option.
RMAN> alter database open resetlogs;
database opened
RMAN>
Because we restored control file!
SQL> select member from v$logfile
union
select file_name from dba_data_files
union
select name from v$controlfile
union
select value from v$parameter where name='spfile' order by 1; 2 3 4 5 6 7
MEMBER
--------------------------------------------------------------------------------
+DATA/mydb/controlfile/current.256.799240855
+FLSHBK/mydb/controlfile/current.256.799240867
/home/oracle/oracle/product/10.2.0/db_1/dbs/spfilemydb.ora
/home/oracle/oracle/product/10.2.0/oradata/mydb/example01.dbf
/home/oracle/oracle/product/10.2.0/oradata/mydb/redo01.log
/home/oracle/oracle/product/10.2.0/oradata/mydb/redo02.log
/home/oracle/oracle/product/10.2.0/oradata/mydb/redo03.log
/home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf
/home/oracle/oracle/product/10.2.0/oradata/mydb/system01.dbf
/home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf
/home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf
11 rows selected.
SQL>
Now let`s migrate datafiles.
Repeat above steps:
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> create pfile from spfile;
File created.
SQL>
Edit pfile:
[oracle@localhost dbs]$ gedit initmydb.ora
*.db_create_file_dest=+DATA
*.db_recovery_file_dest=+FLSHBK
Now, create spfile from pfile
SQL> create spfile from pfile;
File created.
SQL>
Start database and backup as copy of database provide ASM disk group(s), then switch datafiles
[oracle@localhost dbs]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue Nov 13 11:35:09 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
RMAN> startup mount
Oracle instance started
database mounted
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 83887696 bytes
Database Buffers 197132288 bytes
Redo Buffers 2973696 bytes
RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA';
Starting backup at 13-NOV-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=/home/oracle/oracle/product/10.2.0/oradata/mydb/system01.dbf
output filename=+DATA/mydb/datafile/system.257.799241745 tag=TAG20121113T113544 recid=2 stamp=799241794
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf
output filename=+DATA/mydb/datafile/sysaux.258.799241803 tag=TAG20121113T113544 recid=3 stamp=799241832
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/home/oracle/oracle/product/10.2.0/oradata/mydb/example01.dbf
output filename=+DATA/mydb/datafile/example.259.799241837 tag=TAG20121113T113544 recid=4 stamp=799241844
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf
output filename=+DATA/mydb/datafile/undotbs1.260.799241853 tag=TAG20121113T113544 recid=5 stamp=799241856
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf
output filename=+DATA/mydb/datafile/users.261.799241861 tag=TAG20121113T113544 recid=6 stamp=799241863
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08
Finished backup at 13-NOV-12
Starting Control File and SPFILE Autobackup at 13-NOV-12
piece handle=+FLSHBK/mydb/autobackup/2012_11_13/s_799241353.257.799241869 comment=NONE
Finished Control File and SPFILE Autobackup at 13-NOV-12
RMAN> SWITCH DATABASE TO COPY;
datafile 1 switched to datafile copy "+DATA/mydb/datafile/system.257.799241745"
datafile 2 switched to datafile copy "+DATA/mydb/datafile/undotbs1.260.799241853"
datafile 3 switched to datafile copy "+DATA/mydb/datafile/sysaux.258.799241803"
datafile 4 switched to datafile copy "+DATA/mydb/datafile/users.261.799241861"
datafile 5 switched to datafile copy "+DATA/mydb/datafile/example.259.799241837"
RMAN>
Now, open the database
RMAN> alter database open;
database opened
RMAN>
[oracle@localhost dbs]$ sql
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Nov 13 11:41:30 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select member from v$logfile
union
select file_name from dba_data_files
union
select name from v$controlfile
union
select value from v$parameter where name='spfile' order by 1; 2 3 4 5 6 7
MEMBER
--------------------------------------------------------------------------------
+DATA/mydb/controlfile/current.256.799240855
+DATA/mydb/datafile/example.259.799241837
+DATA/mydb/datafile/sysaux.258.799241803
+DATA/mydb/datafile/system.257.799241745
+DATA/mydb/datafile/undotbs1.260.799241853
+DATA/mydb/datafile/users.261.799241861
+FLSHBK/mydb/controlfile/current.256.799240867
/home/oracle/oracle/product/10.2.0/db_1/dbs/spfilemydb.ora
/home/oracle/oracle/product/10.2.0/oradata/mydb/redo01.log
/home/oracle/oracle/product/10.2.0/oradata/mydb/redo02.log
/home/oracle/oracle/product/10.2.0/oradata/mydb/redo03.log
11 rows selected.
SQL>
Not: tempfile not located on control file so we have to re-create new one and delete existing tempfile;
SQL> select name, bytes from v$tempfile;
NAME BYTES
--------------------------------------------------------------------------------
/home/oracle/oracle/product/10.2.0/oradata/mydb/temp01.dbf 20971520
SQL> create temporary tablespace tempnew tempfile SIZE 100M extent management local uniform size 1M;
Tablespace created.
SQL> alter database default temporary tablespace tempnew;
Database altered.
SQL> drop tablespace temp including contents;
Tablespace dropped.
SQL> create temporary tablespace temp tempfile SIZE 100M extent management local uniform size 1M;
Tablespace created.
SQL> alter database default temporary tablespace temp;
Database altered.
SQL> drop tablespace tempnew including contents;
Tablespace dropped.
SQL> select name, bytes from v$tempfile;
NAME BYTES
--------------------------------------------------------------------------------
+DATA/mydb/tempfile/temp.263.799242427 104857600
Now migrate redo logs and dropping old files, Set db_create_online_log_dest_1 value
SQL> alter system set db_create_online_log_dest_1='+DATA' scope=spfile;
System altered.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 88082000 bytes
Database Buffers 192937984 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL>
SQL> select b.group# , b.status , a.member from v$logfile a , v$log b where a.group# = b.group# order by 1;
GROUP# STATUS MEMBER
--------------------------------------------------------------------------------
1 CURRENT /home/oracle/oracle/product/10.2.0/oradata/mydb/redo01.log
2 UNUSED /home/oracle/oracle/product/10.2.0/oradata/mydb/redo02.log
3 UNUSED /home/oracle/oracle/product/10.2.0/oradata/mydb/redo03.log
SQL>
As you see we have had 3 redo groups. To maintain redo files we have to use redo which status INACTIVE. To do that switch logfile.
SQL> alter system switch logfile;
System altered.
SQL> select b.group# , b.status , a.member from v$logfile a , v$log b where a.group# = b.group# order by 1;
GROUP# STATUS MEMBER
--------------------------------------------------------------------------------
1 ACTIVE /home/oracle/oracle/product/10.2.0/oradata/mydb/redo01.log
2 CURRENT /home/oracle/oracle/product/10.2.0/oradata/mydb/redo02.log
3 UNUSED /home/oracle/oracle/product/10.2.0/oradata/mydb/redo03.log
SQL> alter system checkpoint;
System altered.
SQL> select b.group# , b.status , a.member from v$logfile a , v$log b where a.group# = b.group# order by 1;
GROUP# STATUS MEMBER
--------------------------------------------------------------------------------
1 INACTIVE /home/oracle/oracle/product/10.2.0/oradata/mydb/redo01.log
2 INACTIVE /home/oracle/oracle/product/10.2.0/oradata/mydb/redo02.log
3 UNUSED /home/oracle/oracle/product/10.2.0/oradata/mydb/redo03.log
Now drop INAVCTIVE redo files and create new one
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database add logfile group 1 size 50M;
Database altered.
SQL> select b.group# , b.status , a.member from v$logfile a , v$log b where a.group# = b.group# order by 1;
GROUP# STATUS MEMBER
--------------------------------------------------------------------------------
1 UNUSED +DATA/mydb/onlinelog/group_1.262.799243295
2 INACTIVE /home/oracle/oracle/product/10.2.0/oradata/mydb/redo02.log
3 CURRENT /home/oracle/oracle/product/10.2.0/oradata/mydb/redo03.log
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database add logfile group 2 size 50M;
Database altered.
SQL> select b.group# , b.status , a.member from v$logfile a , v$log b where a.group# = b.group# order by 1;
GROUP# STATUS MEMBER
--------------------------------------------------------------------------------
1 UNUSED +DATA/mydb/onlinelog/group_1.262.799243295
2 UNUSED +DATA/mydb/onlinelog/group_2.264.799243379
3 CURRENT /home/oracle/oracle/product/10.2.0/oradata/mydb/redo03.log
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> select b.group# , b.status , a.member from v$logfile a , v$log b where a.group# = b.group# order by 1;
GROUP# STATUS MEMBER
--------------------------------------------------------------------------------
1 INACTIVE +DATA/mydb/onlinelog/group_1.262.799243295
2 ACTIVE +DATA/mydb/onlinelog/group_2.264.799243379
3 CURRENT /home/oracle/oracle/product/10.2.0/oradata/mydb/redo03.log
SQL> alter system switch logfile;
System altered.
SQL> alter system checkpoint;
System altered.
SQL> select b.group# , b.status , a.member from v$logfile a , v$log b where a.group# = b.group# order by 1;
GROUP# STATUS MEMBER
--------------------------------------------------------------------------------
1 CURRENT +DATA/mydb/onlinelog/group_1.262.799243295
2 INACTIVE +DATA/mydb/onlinelog/group_2.264.799243379
3 INACTIVE /home/oracle/oracle/product/10.2.0/oradata/mydb/redo03.log
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database add logfile group 3 size 50M;
Database altered.
SQL> alter system checkpoint;
System altered.
SQL> select b.group# , b.status , a.member from v$logfile a , v$log b where a.group# = b.group# order by 1;
GROUP# STATUS MEMBER
--------------------------------------------------------------------------------
1 INACTIVE +DATA/mydb/onlinelog/group_1.262.799243295
2 INACTIVE +DATA/mydb/onlinelog/group_2.264.799243379
3 CURRENT +DATA/mydb/onlinelog/group_3.265.799243479
SQL>
Now let`s re-create spfile on ASM disk group.
[oracle@localhost mydb]$ export ORACLE_SID=mydb
[oracle@localhost mydb]$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Nov 13 12:23:23 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> create pfile from spfile;
File created.
SQL> create spfile='+DATA/spfile' from pfile;
File created.
SQL>
Edit pfile :
[oracle@localhost dbs]$ pwd
/home/oracle/oracle/product/10.2.0/db_1/dbs
[oracle@localhost dbs]$ vi initmydb.ora
...
*.spfile='+DATA/spfile'
...
Check file in ASM
[oracle@localhost ~]$ export ORACLE_SID=+ASM
[oracle@localhost ~]$ asmcmd
ASMCMD> ls
DATA/
FLSHBK/
ASMCMD> cd DATA
ASMCMD> ls
MYDB/
spfile
ASMCMD>
SQL> startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameter spfile
NAME TYPE VALUE
----------------------------------------------------------------------------
spfile string +DATA/spfile
SQL>
Delete old datafiles
[oracle@localhost dbs]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue Nov 13 12:14:17 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: MYDB (DBID=2680024004)
RMAN> delete copy of database;
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=138 devtype=DISK
List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name
------- ---- - --------------- ---------- --------------- ----
7 1 A 13-NOV-12 526093 13-NOV-12 /home/oracle/oracle/product/10.2.0/oradata/mydb/system01.dbf
8 2 A 13-NOV-12 526093 13-NOV-12 /home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf
9 3 A 13-NOV-12 526093 13-NOV-12 /home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf
10 4 A 13-NOV-12 526093 13-NOV-12 /home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf
11 5 A 13-NOV-12 526093 13-NOV-12 /home/oracle/oracle/product/10.2.0/oradata/mydb/example01.dbf
Do you really want to delete the above objects (enter YES or NO)? YES
deleted datafile copy
datafile copy filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/system01.dbf recid=7 stamp=799241987
deleted datafile copy
datafile copy filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf recid=8 stamp=799241987
deleted datafile copy
datafile copy filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf recid=9 stamp=799241987
deleted datafile copy
datafile copy filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf recid=10 stamp=799241987
deleted datafile copy
datafile copy filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/example01.dbf recid=11 stamp=799241987
Deleted 5 objects
RMAN>
Also delete other files : redo, control and tempfile
[oracle@localhost mydb]$ ls -lrt
total 192004
-rw-r----- 1 oracle oinstall 20979712 Oct 20 2011 temp01.dbf
-rw-r----- 1 oracle oinstall 7061504 Nov 13 11:13 control03.ctl
-rw-r----- 1 oracle oinstall 7061504 Nov 13 11:13 control02.ctl
-rw-r----- 1 oracle oinstall 7061504 Nov 13 11:13 control01.ctl
-rw-r----- 1 oracle oinstall 52429312 Nov 13 11:58 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Nov 13 11:59 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Nov 13 12:03 redo03.log
drwxr-xr-x 2 oracle oinstall 4096 Nov 13 12:10 arch
[oracle@localhost mydb]$ rm temp01.dbf
[oracle@localhost mydb]$ rm redo0*.log
[oracle@localhost mydb]$ rm control0*
[oracle@localhost mydb]$
Now check ASM disk space:
[oracle@localhost mydb]$ export ORACLE_SID=+ASM
[oracle@localhost mydb]$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Nov 13 12:17:07 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select name, state, total_mb, free_mb from v$asm_diskgroup;
NAME STATE TOTAL_MB FREE_MB
------------------------------ ----------- ---------- ----------
DATA MOUNTED 3000 558
FLSHBK MOUNTED 2000 1726
SQL>
Select above query:
SQL> select member from v$logfile
union
select file_name from dba_data_files
union
select name from v$controlfile
union
select value from v$parameter where name='spfile' order by 1; 2 3 4 5 6 7
MEMBER
--------------------------------------------------------------------------------
+DATA/mydb/controlfile/current.256.799240855
+DATA/mydb/datafile/example.259.799241837
+DATA/mydb/datafile/sysaux.258.799241803
+DATA/mydb/datafile/system.257.799241745
+DATA/mydb/datafile/undotbs1.260.799241853
+DATA/mydb/datafile/users.261.799241861
+DATA/mydb/onlinelog/group_1.262.799243295
+DATA/mydb/onlinelog/group_2.264.799243379
+DATA/mydb/onlinelog/group_3.265.799243479
+DATA/spfile
+FLSHBK/mydb/controlfile/current.256.799240867
11 rows selected.
SQL>
All files were successfully moved to ASM
Now migrate Archivelogs to ASM disk group
edit pfile file
...
*.log_archive_dest_1=''
...
recreate spfile and startup database
SQL> select name,sequence# from v$archived_log;
NAME SEQUENCE#
---------------------------------------------------------------------------------------------------------
/home/oracle/oracle/product/10.2.0/oradata/mydb/arch/mydb_1_9_799241036.arc 9
/home/oracle/oracle/product/10.2.0/oradata/mydb/arch/mydb_1_10_799241036.arc 10
+FLSHBK/mydb/archivelog/2012_11_13/thread_1_seq_11.272.799253049 11
3 rows selected.
And now migrate from ASM to FileSystem.
Let`s migrate spfile to FileSystem
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/spfile
SQL> create pfile='/home/oracle/oracle/product/10.2.0/db_1/dbs/initmydb.ora' from spfile='+DATA/spfile';
File created.
SQL> create spfile='/home/oracle/oracle/product/10.2.0/db_1/dbs/spfilemydb.ora' from pfile;
File created.
SQL> startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 96470608 bytes
Database Buffers 184549376 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL> show parameter spfile
NAME TYPE VALUE
-------------------- -------- -------------------------------------------------------------
spfile string /home/oracle/oracle/product/10.2.0/db_1/dbs/spfilemydb.ora
SQL>
Good job, Ulfet. Thanks for sharing.
ReplyDeleteWelcome Neriman!
ReplyDelete