How Migrate All Files on ASM to Non-ASM (Unix/Linux)

In this post I’ll show a way to migrate the database stored in ASM to Filesystem.
I’ll use PL/SQL scripts and RMAN commands to accomplish this.


Env Info:

1	DATABASE NAME : DROP

2	ORACLE DATAFILES/CONTROLFILE/TEMPFILE = /u01/app/oracle/oradata/drop

3	ORACLE FLASH RECOVERY AREA= /u01/app/oracle/flash_recovery_area/

Migration  steps:

1. Controlfile
2. Datafile and Tempfile
3. Online Logs (redo)
4. Archived Online logs (archivelogs) and BackupSet in FRA
5. Server Parameter File (SPFILE)

1.  Controlfiles

1	SQL> select name from v$controlfile;

2

3

NAME

4	--------------------------------------------------------------------------------

5	+DG_DATA/drop/controlfile/current.275.761683397

6	+DG_FRA/drop/controlfile/current.281.761683397

01	SQL> shutdown immediate;

02	Database closed.

03	Database dismounted.

04	ORACLE instance shut down.

05

06	SQL> startup nomount

07	ORACLE instance started.

08

09	Total System Global Area 1119043584 bytes

10	Fixed Size                  2218888 bytes

11	Variable Size             855639160 bytes

12	Database Buffers          251658240 bytes

13	Redo Buffers                9527296 bytes

14

15	SQL> alter system set

16	control_files='/u01/app/oracle/oradata/drop/control01.ctl',

17	'/u01/app/oracle/flash_recovery_area/drop/control02.ctl'

18	SCOPE=SPFILE SID='*';

19

20	System altered.

21

22	SQL> shutdown immediate;

23	ORA-01507: database not mounted

24

25	ORACLE instance shut down.

26

27	$ rman target /

28

29	Recovery Manager: Release 11.2.0.2.0 - Production on Mon Sep 12 18:36:53 2011

30

31	Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

32

33	connected to target database (not started)

34

35	RMAN> startup nomount

36

37	Oracle instance started

38

39	Total System Global Area    1119043584 bytes

40

41	Fixed Size                     2218888 bytes

42	Variable Size                855639160 bytes

43	Database Buffers             251658240 bytes

44	Redo Buffers                   9527296 bytes

45

46	RMAN> restore controlfile from '+DG_DATA/drop/controlfile/current.275.761683397';

47

48	Starting restore at 12-SEP-11

49	using target database control file instead of recovery catalog

50	allocated channel: ORA_DISK_1

51	channel ORA_DISK_1: SID=134 device type=DISK

52

53	channel ORA_DISK_1: copied control file copy

54	output file name=/u01/app/oracle/oradata/drop/control01.ctl

55	output file name=/u01/app/oracle/flash_recovery_area/drop/control02.ctl

56	Finished restore at 12-SEP-11

57

58	RMAN> startup mount

59

60	database is already started

61	database mounted

62	released channel: ORA_DISK_1

63

64	RMAN> exit

65

66	Recovery Manager complete.

01	sqlplus / as sysdba

02

03	SQL*Plus: Release 11.2.0.2.0 Production on Mon Sep 12 18:53:19 2011

04

05	Copyright (c) 1982, 2010, Oracle.  All rights reserved.

06

07	SQL> select name from v$controlfile;

08

09

NAME

10	--------------------------------------------------------------------------------

11	/u01/app/oracle/oradata/drop/control01.ctl

12	/u01/app/oracle/flash_recovery_area/drop/control02.ctl

2.  Datafiles and Tempfiles

01	SQL> select file#, name from v$datafile;

02

03	FILE# NAME

04	---------- --------------------------------------------------

05	1 +DG_DATA/drop/datafile/system.292.761712355

06	2 +DG_DATA/drop/datafile/sysaux.291.761712395

07	3 +DG_DATA/drop/datafile/undotbs1.290.761712439

08	4 +DG_DATA/drop/datafile/users.289.761712455

09	5 +DG_DATA/drop/datafile/users.288.761712457

10	6 +DG_DATA/drop/datafile/users.287.761712457

11	7 +DG_DATA/drop/datafile/users.277.761712459

12	8 +DG_DATA/drop/datafile/users.278.761712461

13	9 +DG_DATA/drop/datafile/users.279.761712461

14	10 +DG_DATA/drop/datafile/users.276.761712463

15

16	10 rows selected.

17

18	SQL> select file#, name from v$tempfile;

19

20	FILE# NAME

21	---------- --------------------------------------------------

22	2 +DG_DATA/drop/tempfile/temp.286.761687721

23	1 +DG_DATA/drop/tempfile/temp.293.761712609

Using this PL/SQL is enough to generate RMAN command to migrate all datafiles and tempfiles at same time.

01	SET serveroutput ON;

02

DECLARE

03	vcount  NUMBER:=0;

04	vfname VARCHAR2(1024);

05

CURSOR df

06

IS

07	SELECT file#,

08	rtrim(REPLACE(name,'+DG_DATA/drop/datafile/','/u01/app/oracle/oradata/drop/'),'.0123456789') AS name

09	FROM v$datafile;

10

CURSOR tp

11

IS

12	SELECT file#,

13	rtrim(REPLACE(name,'+DG_DATA/drop/tempfile/','/u01/app/oracle/oradata/drop/'),'.0123456789') AS name

14	FROM v$tempfile;

15

BEGIN

16	dbms_output.put_line('CONFIGURE CONTROLFILE AUTOBACKUP ON;');

17	FOR dfrec IN df

18

LOOP

19	IF dfrec.name  != vfname THEN

20	vcount      :=1;

21	vfname     := dfrec.name;

22

ELSE

23	vcount := vcount+1;

24	vfname:= dfrec.name;

25

END IF;

26	dbms_output.put_line('backup as copy datafile ' || dfrec.file# ||' format  "'||dfrec.name ||vcount||'.dbf";');

27

END LOOP;

28	dbms_output.put_line('run');

29	dbms_output.put_line('{');

30	FOR dfrec IN df

31

LOOP

32	IF dfrec.name  != vfname THEN

33	vcount      :=1;

34	vfname     := dfrec.name;

35

ELSE

36	vcount := vcount+1;

37	vfname:= dfrec.name;

38

END IF;

39	dbms_output.put_line('set newname for datafile ' || dfrec.file# ||'  to  '''||dfrec.name ||vcount||'.dbf'' ;');

40

END LOOP;

41	FOR tprec IN tp

42

LOOP

43	IF tprec.name  !=  vfname THEN

44	vcount      :=1;

45	vfname     := tprec.name;

46

ELSE

47	vcount := vcount+1;

48	vfname:= tprec.name;

49

END IF;

50	dbms_output.put_line('set newname for tempfile ' || tprec.file# ||'  to  '''||tprec.name ||vcount||'.dbf'' ;');

51

END LOOP;

52	dbms_output.put_line('switch tempfile all;');

53	dbms_output.put_line('switch datafile all;');

54	dbms_output.put_line('restore database;');

55	dbms_output.put_line('recover database;');

56	dbms_output.put_line('}');

57	dbms_output.put_line('alter database open;');

58	dbms_output.put_line('exit');

59

END;

60

/

Database must be Mounted

01	SQL> SET serveroutput ON;

02	SQL> DECLARE

03

.

04

.

05

.

06	57      dbms_output.put_line('exit');

07

58  END;

08

59  /

09

10	CONFIGURE CONTROLFILE AUTOBACKUP ON;

11	backup as copy datafile 1 format  "/u01/app/oracle/oradata/drop/system1.dbf";

12	backup as copy datafile 2 format  "/u01/app/oracle/oradata/drop/sysaux1.dbf";

13	backup as copy datafile 3 format  "/u01/app/oracle/oradata/drop/undotbs1.dbf";

14	backup as copy datafile 4 format  "/u01/app/oracle/oradata/drop/users1.dbf";

15	backup as copy datafile 5 format  "/u01/app/oracle/oradata/drop/users2.dbf";

16	backup as copy datafile 6 format  "/u01/app/oracle/oradata/drop/users3.dbf";

17	backup as copy datafile 7 format  "/u01/app/oracle/oradata/drop/users4.dbf";

18	backup as copy datafile 8 format  "/u01/app/oracle/oradata/drop/users5.dbf";

19	backup as copy datafile 9 format  "/u01/app/oracle/oradata/drop/users6.dbf";

20	backup as copy datafile 10 format  "/u01/app/oracle/oradata/drop/users7.dbf";

21

run

22

{

23	set newname for datafile 1  to  '/u01/app/oracle/oradata/drop/system1.dbf' ;

24	set newname for datafile 2  to  '/u01/app/oracle/oradata/drop/sysaux1.dbf' ;

25	set newname for datafile 3  to  '/u01/app/oracle/oradata/drop/undotbs1.dbf' ;

26	set newname for datafile 4  to  '/u01/app/oracle/oradata/drop/users1.dbf' ;

27	set newname for datafile 5  to  '/u01/app/oracle/oradata/drop/users2.dbf' ;

28	set newname for datafile 6  to  '/u01/app/oracle/oradata/drop/users3.dbf' ;

29	set newname for datafile 7  to  '/u01/app/oracle/oradata/drop/users4.dbf' ;

30	set newname for datafile 8  to  '/u01/app/oracle/oradata/drop/users5.dbf' ;

31	set newname for datafile 9  to  '/u01/app/oracle/oradata/drop/users6.dbf' ;

32	set newname for datafile 10  to  '/u01/app/oracle/oradata/drop/users7.dbf' ;

33	set newname for tempfile 1  to  '/u01/app/oracle/oradata/drop/temp1.dbf' ;

34	set newname for tempfile 2  to  '/u01/app/oracle/oradata/drop/temp2.dbf' ;

35	switch tempfile all;

36	switch datafile all;

37	restore database;

38	recover database;

39

}

40	alter database open;

41

exit

Important: Setting AUTOBACKUP ON is mandatory (Backup Fails ORA-27038 Creating Automatic Controlfile Backup [ID 382989.1])
Create a RMAN script file “migrate_db.rcv” and paste command generate by PL/SQL.

001	nohup rman target / cmdfile migrate_db.rcv log migrate_db.log &

002

003	cat migrate_db.log

004

005	using target database control file instead of recovery catalog

006	old RMAN configuration parameters:

007	CONFIGURE CONTROLFILE AUTOBACKUP ON;

008	new RMAN configuration parameters:

009	CONFIGURE CONTROLFILE AUTOBACKUP ON;

010	new RMAN configuration parameters are successfully stored

011

012	Starting backup at 13-SEP-11

013	allocated channel: ORA_DISK_1

014	channel ORA_DISK_1: SID=137 device type=DISK

015	channel ORA_DISK_1: starting datafile copy

016	input datafile file number=00001 name=+DG_DATA/drop/datafile/system.292.761712355

017	output file name=/u01/app/oracle/oradata/drop/system1.dbf tag=TAG20110913T025233 RECID=68 STAMP=761712766

018	channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15

019	Finished backup at 13-SEP-11

020

021	Starting Control File and SPFILE Autobackup at 13-SEP-11

022	piece handle=/u01/app/oracle/flash_recovery_area/DROP/autobackup/2011_09_13/o1_mf_s_761712652_76xvxlw3_.bkp comment=NONE

023	Finished Control File and SPFILE Autobackup at 13-SEP-11

024

025	Starting backup at 13-SEP-11

026	using channel ORA_DISK_1

027	channel ORA_DISK_1: starting datafile copy

028	input datafile file number=00002 name=+DG_DATA/drop/datafile/sysaux.291.761712395

029	output file name=/u01/app/oracle/oradata/drop/sysaux1.dbf tag=TAG20110913T025253 RECID=69 STAMP=761712784

030	channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15

031	Finished backup at 13-SEP-11

032

033	Starting Control File and SPFILE Autobackup at 13-SEP-11

034	piece handle=/u01/app/oracle/flash_recovery_area/DROP/autobackup/2011_09_13/o1_mf_s_761712652_76xvy5h5_.bkp comment=NONE

035	Finished Control File and SPFILE Autobackup at 13-SEP-11

036

037	Starting backup at 13-SEP-11

038	using channel ORA_DISK_1

039	channel ORA_DISK_1: starting datafile copy

040	input datafile file number=00003 name=+DG_DATA/drop/datafile/undotbs1.290.761712439

041	output file name=/u01/app/oracle/oradata/drop/undotbs1.dbf tag=TAG20110913T025312 RECID=70 STAMP=761712796

042	channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

043	Finished backup at 13-SEP-11

044

045	Starting Control File and SPFILE Autobackup at 13-SEP-11

046	piece handle=/u01/app/oracle/flash_recovery_area/DROP/autobackup/2011_09_13/o1_mf_s_761712652_76xvyjkb_.bkp comment=NONE

047	Finished Control File and SPFILE Autobackup at 13-SEP-11

048

049	Starting backup at 13-SEP-11

050	using channel ORA_DISK_1

051	channel ORA_DISK_1: starting datafile copy

052	input datafile file number=00004 name=+DG_DATA/drop/datafile/users.289.761712455

053	output file name=/u01/app/oracle/oradata/drop/users1.dbf tag=TAG20110913T025321 RECID=71 STAMP=761712802

054	channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

055	Finished backup at 13-SEP-11

056

057	Starting Control File and SPFILE Autobackup at 13-SEP-11

058	piece handle=/u01/app/oracle/flash_recovery_area/DROP/autobackup/2011_09_13/o1_mf_s_761712652_76xvymrv_.bkp comment=NONE

059	Finished Control File and SPFILE Autobackup at 13-SEP-11

060

061	Starting backup at 13-SEP-11

062	using channel ORA_DISK_1

063	channel ORA_DISK_1: starting datafile copy

064	input datafile file number=00005 name=+DG_DATA/drop/datafile/users.288.761712457

065	output file name=/u01/app/oracle/oradata/drop/users2.dbf tag=TAG20110913T025326 RECID=72 STAMP=761712807

066	channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

067	Finished backup at 13-SEP-11

068

069	Starting Control File and SPFILE Autobackup at 13-SEP-11

070	piece handle=/u01/app/oracle/flash_recovery_area/DROP/autobackup/2011_09_13/o1_mf_s_761712652_76xvys74_.bkp comment=NONE

071	Finished Control File and SPFILE Autobackup at 13-SEP-11

072

073	Starting backup at 13-SEP-11

074	using channel ORA_DISK_1

075	channel ORA_DISK_1: starting datafile copy

076	input datafile file number=00006 name=+DG_DATA/drop/datafile/users.287.761712457

077	output file name=/u01/app/oracle/oradata/drop/users3.dbf tag=TAG20110913T025332 RECID=73 STAMP=761712812

078	channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

079	Finished backup at 13-SEP-11

080

081	Starting Control File and SPFILE Autobackup at 13-SEP-11

082	piece handle=/u01/app/oracle/flash_recovery_area/DROP/autobackup/2011_09_13/o1_mf_s_761712652_76xvyydl_.bkp comment=NONE

083	Finished Control File and SPFILE Autobackup at 13-SEP-11

084

085	Starting backup at 13-SEP-11

086	using channel ORA_DISK_1

087	channel ORA_DISK_1: starting datafile copy

088	input datafile file number=00007 name=+DG_DATA/drop/datafile/users.277.761712459

089	output file name=/u01/app/oracle/oradata/drop/users4.dbf tag=TAG20110913T025335 RECID=74 STAMP=761712816

090	channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

091	Finished backup at 13-SEP-11

092

093	Starting Control File and SPFILE Autobackup at 13-SEP-11

094	piece handle=/u01/app/oracle/flash_recovery_area/DROP/autobackup/2011_09_13/o1_mf_s_761712652_76xvz1qq_.bkp comment=NONE

095	Finished Control File and SPFILE Autobackup at 13-SEP-11

096

097	Starting backup at 13-SEP-11

098	using channel ORA_DISK_1

099	channel ORA_DISK_1: starting datafile copy

100	input datafile file number=00008 name=+DG_DATA/drop/datafile/users.278.761712461

101	output file name=/u01/app/oracle/oradata/drop/users5.dbf tag=TAG20110913T025338 RECID=75 STAMP=761712819

102	channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

103	Finished backup at 13-SEP-11

104

105	Starting Control File and SPFILE Autobackup at 13-SEP-11

106	piece handle=/u01/app/oracle/flash_recovery_area/DROP/autobackup/2011_09_13/o1_mf_s_761712652_76xvz4xm_.bkp comment=NONE

107	Finished Control File and SPFILE Autobackup at 13-SEP-11

108

109	Starting backup at 13-SEP-11

110	using channel ORA_DISK_1

111	channel ORA_DISK_1: starting datafile copy

112	input datafile file number=00009 name=+DG_DATA/drop/datafile/users.279.761712461

113	output file name=/u01/app/oracle/oradata/drop/users6.dbf tag=TAG20110913T025342 RECID=76 STAMP=761712822

114	channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

115	Finished backup at 13-SEP-11

116

117	Starting Control File and SPFILE Autobackup at 13-SEP-11

118	piece handle=/u01/app/oracle/flash_recovery_area/DROP/autobackup/2011_09_13/o1_mf_s_761712652_76xvz878_.bkp comment=NONE

119	Finished Control File and SPFILE Autobackup at 13-SEP-11

120

121	Starting backup at 13-SEP-11

122	using channel ORA_DISK_1

123	channel ORA_DISK_1: starting datafile copy

124	input datafile file number=00010 name=+DG_DATA/drop/datafile/users.276.761712463

125	output file name=/u01/app/oracle/oradata/drop/users7.dbf tag=TAG20110913T025345 RECID=77 STAMP=761712825

126	channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

127	Finished backup at 13-SEP-11

128

129	Starting Control File and SPFILE Autobackup at 13-SEP-11

130	piece handle=/u01/app/oracle/flash_recovery_area/DROP/autobackup/2011_09_13/o1_mf_s_761712652_76xvzcjb_.bkp comment=NONE

131	Finished Control File and SPFILE Autobackup at 13-SEP-11

132

133	executing command: SET NEWNAME

134

135	executing command: SET NEWNAME

136

137	executing command: SET NEWNAME

138

139	executing command: SET NEWNAME

140

141	executing command: SET NEWNAME

142

143	executing command: SET NEWNAME

144

145	executing command: SET NEWNAME

146

147	executing command: SET NEWNAME

148

149	executing command: SET NEWNAME

150

151	executing command: SET NEWNAME

152

153	executing command: SET NEWNAME

154

155	executing command: SET NEWNAME

156

157	renamed tempfile 1 to /u01/app/oracle/oradata/drop/temp1.dbf in control file

158	renamed tempfile 2 to /u01/app/oracle/oradata/drop/temp2.dbf in control file

159

160	datafile 1 switched to datafile copy

161	input datafile copy RECID=68 STAMP=761712766 file name=/u01/app/oracle/oradata/drop/system1.dbf

162	datafile 2 switched to datafile copy

163	input datafile copy RECID=69 STAMP=761712784 file name=/u01/app/oracle/oradata/drop/sysaux1.dbf

164	datafile 3 switched to datafile copy

165	input datafile copy RECID=70 STAMP=761712796 file name=/u01/app/oracle/oradata/drop/undotbs1.dbf

166	datafile 4 switched to datafile copy

167	input datafile copy RECID=71 STAMP=761712802 file name=/u01/app/oracle/oradata/drop/users1.dbf

168	datafile 5 switched to datafile copy

169	input datafile copy RECID=72 STAMP=761712807 file name=/u01/app/oracle/oradata/drop/users2.dbf

170	datafile 6 switched to datafile copy

171	input datafile copy RECID=73 STAMP=761712812 file name=/u01/app/oracle/oradata/drop/users3.dbf

172	datafile 7 switched to datafile copy

173	input datafile copy RECID=74 STAMP=761712816 file name=/u01/app/oracle/oradata/drop/users4.dbf

174	datafile 8 switched to datafile copy

175	input datafile copy RECID=75 STAMP=761712819 file name=/u01/app/oracle/oradata/drop/users5.dbf

176	datafile 9 switched to datafile copy

177	input datafile copy RECID=76 STAMP=761712822 file name=/u01/app/oracle/oradata/drop/users6.dbf

178	datafile 10 switched to datafile copy

179	input datafile copy RECID=77 STAMP=761712825 file name=/u01/app/oracle/oradata/drop/users7.dbf

180

181	Starting restore at 13-SEP-11

182	using channel ORA_DISK_1

183

184	skipping datafile 1; already restored to file /u01/app/oracle/oradata/drop/system1.dbf

185	skipping datafile 2; already restored to file /u01/app/oracle/oradata/drop/sysaux1.dbf

186	skipping datafile 3; already restored to file /u01/app/oracle/oradata/drop/undotbs1.dbf

187	skipping datafile 4; already restored to file /u01/app/oracle/oradata/drop/users1.dbf

188	skipping datafile 5; already restored to file /u01/app/oracle/oradata/drop/users2.dbf

189	skipping datafile 6; already restored to file /u01/app/oracle/oradata/drop/users3.dbf

190	skipping datafile 7; already restored to file /u01/app/oracle/oradata/drop/users4.dbf

191	skipping datafile 8; already restored to file /u01/app/oracle/oradata/drop/users5.dbf

192	skipping datafile 9; already restored to file /u01/app/oracle/oradata/drop/users6.dbf

193	skipping datafile 10; already restored to file /u01/app/oracle/oradata/drop/users7.dbf

194	restore not done; all files read only, offline, or already restored

195	Finished restore at 13-SEP-11

196

197	Starting recover at 13-SEP-11

198	using channel ORA_DISK_1

199

200	starting media recovery

201	media recovery complete, elapsed time: 00:00:02

202

203	Finished recover at 13-SEP-11

204

205	database opened

206

207	Recovery Manager complete.

01	SQL> select file#, name from v$datafile;

02

03	FILE# NAME

04	---------- --------------------------------------------------

05	1 /u01/app/oracle/oradata/drop/system1.dbf

06	2 /u01/app/oracle/oradata/drop/sysaux1.dbf

07	3 /u01/app/oracle/oradata/drop/undotbs1.dbf

08	4 /u01/app/oracle/oradata/drop/users1.dbf

09	5 /u01/app/oracle/oradata/drop/users2.dbf

10	6 /u01/app/oracle/oradata/drop/users3.dbf

11	7 /u01/app/oracle/oradata/drop/users4.dbf

12	8 /u01/app/oracle/oradata/drop/users5.dbf

13	9 /u01/app/oracle/oradata/drop/users6.dbf

14	10 /u01/app/oracle/oradata/drop/users7.dbf

15

16	10 rows selected.

17

18	SQL> select file#, name from v$tempfile;

19

20	FILE# NAME

21	---------- --------------------------------------------------

22	2 /u01/app/oracle/oradata/drop/temp2.dbf

23	1 /u01/app/oracle/oradata/drop/temp1.dbf

24

25

SQL>

3.  Online Logs (Redo)

Database must be Open

01	SQL> select group#,member from v$logfile order by 1;

02

03	GROUP# MEMBER

04	---------- ------------------------------------------------------------------------------------------

05	1 +DG_DATA/drop/onlinelog/group_1.285.761755579

06	2 +DG_DATA/drop/onlinelog/group_2.284.761755615

07	3 +DG_DATA/drop/onlinelog/group_3.278.761755651

08	4 +DG_DATA/drop/onlinelog/group_4.283.761754951

09	5 +DG_DATA/drop/onlinelog/group_5.276.761754957

10	6 +DG_DATA/drop/onlinelog/group_6.282.761754963

11	7 +DG_DATA/drop/onlinelog/group_7.279.761754967

12

13	SQL> SELECT group# grp,

14	thread# thr,

15	bytes/1024 bytes_k,

16

'NO' srl,

17

status

18	FROM v$log

19

UNION

20	SELECT group# grp,

21	thread# thr,

22	bytes/1024 bytes_k,

23	'YES' srl,

24

status

25	FROM v$standby_log

26	ORDER BY 1;

27

28	GRP        THR    BYTES_K SRL STATUS

29	---------- ---------- ---------- --- ----------------

30	1          1     102400 NO  INACTIVE

31	2          1     102400 NO  CURRENT

32	3          1     102400 NO  UNUSED

33	4          1     102400 YES UNASSIGNED

34	5          1     102400 YES UNASSIGNED

35	6          1     102400 YES UNASSIGNED

36	7          1     102400 YES UNASSIGNED

Another PL/SQL, will work only if database have more than 2 Logfile Group

01

DECLARE

02	vgroup NUMBER;

03	CURSOR rlc

04

IS

05	SELECT group# grp,

06	thread# thr,

07	bytes/1024 bytes_k,

08

'NO' srl,

09

status

10	FROM v$log

11

UNION

12	SELECT group# grp,

13	thread# thr,

14	bytes/1024 bytes_k,

15	'YES' srl,

16

status

17	FROM v$standby_log

18	ORDER BY status DESC;

19	stmt    VARCHAR2(2048);

20	swtstmt VARCHAR2(1024) := 'alter system switch logfile';

21	ckpstmt VARCHAR2(1024) := 'alter system checkpoint global';

22

BEGIN

23	FOR rlcRec IN rlc

24

LOOP

25	IF (rlcRec.srl = 'YES') THEN

26

BEGIN

27	SELECT group#

28	INTO vgroup

29	FROM v$standby_log

30	WHERE group# = rlcRec.grp

31	AND thread#  = rlcRec.thr

32	AND (status  ='CURRENT'

33	OR status    = 'ACTIVE');

34	stmt        := 'alter database drop standby logfile group ' || rlcRec.grp;

35	EXECUTE IMMEDIATE swtstmt;

36	EXECUTE IMMEDIATE ckpstmt;

37	EXECUTE immediate stmt;

38	stmt := 'alter database add standby logfile thread ' || rlcRec.thr ||' group '||rlcRec.grp||' ''/u01/app/oracle/oradata/drop/redo_stby'||rlcRec.grp||'_'||rlcRec.thr||'_a.log'' size ' || rlcRec.bytes_k || 'K';

39	EXECUTE immediate stmt;

40

EXCEPTION

41	WHEN NO_DATA_FOUND THEN

42

BEGIN

43	EXECUTE immediate ckpstmt;

44	stmt := 'alter database drop standby logfile group ' || rlcRec.grp;

45	EXECUTE immediate stmt;

46	stmt := 'alter database add standby logfile thread ' || rlcRec.thr ||' group '||rlcRec.grp||' ''/u01/app/oracle/oradata/drop/redo_stby'||rlcRec.grp||'_'||rlcRec.thr||'_a.log'' size ' || rlcRec.bytes_k || 'K';

47	EXECUTE immediate stmt;

48

END;

49

END;

50

ELSE

51

BEGIN

52	SELECT group#

53	INTO vgroup

54	FROM v$log

55	WHERE group# = rlcRec.grp

56	AND thread#  = rlcRec.thr

57	AND (status  ='CURRENT'

58	OR status    = 'ACTIVE');

59	stmt        := 'alter database drop logfile group ' || rlcRec.grp;

60	EXECUTE IMMEDIATE swtstmt;

61	EXECUTE IMMEDIATE ckpstmt;

62	EXECUTE immediate stmt;

63	stmt := 'alter database add logfile thread ' || rlcRec.thr || ' group '||rlcRec.grp||'  ''/u01/app/oracle/oradata/drop/redo_'||rlcRec.grp||'_'||rlcRec.thr||'_a.log'' size ' || rlcRec.bytes_k || 'K';

64	EXECUTE immediate stmt;

65

EXCEPTION

66	WHEN NO_DATA_FOUND THEN

67

BEGIN

68	EXECUTE immediate ckpstmt;

69	stmt := 'alter database drop logfile group ' || rlcRec.grp;

70	EXECUTE immediate stmt;

71	stmt := 'alter database add logfile thread ' || rlcRec.thr || ' group '||rlcRec.grp||'  ''/u01/app/oracle/oradata/drop/redo_'||rlcRec.grp||'_'||rlcRec.thr||'_a.log'' size ' || rlcRec.bytes_k || 'K';

72	EXECUTE immediate stmt;

73

END;

74

END;

75

END IF;

76

END LOOP;

77

END;

78

/

01	SQL> select group#,member from v$logfile order by 1;

02

03	GROUP# MEMBER

04	---------- ------------------------------------------------------------------------------------------

05	1 /u01/app/oracle/oradata/drop/redo_1_1_a.log

06	2 /u01/app/oracle/oradata/drop/redo_2_1_a.log

07	3 /u01/app/oracle/oradata/drop/redo_3_1_a.log

08	4 /u01/app/oracle/oradata/drop/redo_stby4_1_a.log

09	5 /u01/app/oracle/oradata/drop/redo_stby5_1_a.log

10	6 /u01/app/oracle/oradata/drop/redo_stby6_1_a.log

11	7 /u01/app/oracle/oradata/drop/redo_stby7_1_a.log

12

13	7 rows selected.

14

15	SQL> SELECT group# grp,

16	thread# thr,

17	bytes/1024 bytes_k,

18

'NO' srl,

19

status

20	FROM v$log

21

UNION

22	SELECT group# grp,

23	thread# thr,

24	bytes/1024 bytes_k,

25	'YES' srl,

26

status

27	FROM v$standby_log

28	ORDER BY 1;  2    3    4    5    6    7    8    9   10   11   12   13   14

29

30	GRP        THR    BYTES_K SRL STATUS

31	---------- ---------- ---------- --- ----------------

32	1          1     102400 NO  CURRENT

33	2          1     102400 NO  UNUSED

34	3          1     102400 NO  UNUSED

35	4          1     102400 YES UNASSIGNED

36	5          1     102400 YES UNASSIGNED

37	6          1     102400 YES UNASSIGNED

38	7          1     102400 YES UNASSIGNED

4.  Archived Online logs (archivelogs) and BackupSet stored in FRA

Migrating BackupSet and Archivelogs from FRA (Flash Recovery Area) on ASM to FRA on NON-ASM
The first step is set new FRA.

01	SQL> show parameter db_recovery_file

02	NAME                                 TYPE        VALUE

03	------------------------------------ ----------- ------------------------------

04	db_recovery_file_dest                string      +DG_FRA

05	db_recovery_file_dest_size           big integer 100G

06

07	SQL> alter system set db_recovery_file_dest='/u01/app/oracle/flash_recovery_area' scope=both;

08

09	SQL> show parameter db_recovery_file

10	NAME                                 TYPE        VALUE

11	------------------------------------ ----------- ------------------------------

12	db_recovery_file_dest                string      /u01/app/oracle/flash_recovery_area

13	db_recovery_file_dest_size           big integer 100G

Migrating Archivelog

01	RMAN> list archivelog all;

02

03	using target database control file instead of recovery catalog

04	List of Archived Log Copies for database with db_unique_name DROP

05	=====================================================================

06

07	Key     Thrd Seq     S Low Time

08	------- ---- ------- - ---------

09	80      1    57      A 16-SEP-11

10	Name: +DG_FRA/drop/archivelog/2011_09_16/thread_1_seq_57.343.762030975

11

12	81      1    58      A 16-SEP-11

13	Name: +DG_FRA/drop/archivelog/2011_09_16/thread_1_seq_58.333.762030975

14

.

15

.

16

.

17	89      1    66      A 16-SEP-11

18	Name: +DG_FRA/drop/archivelog/2011_09_16/thread_1_seq_66.340.762030987

19

20	90      1    67      A 16-SEP-11

21	Name: +DG_FRA/drop/archivelog/2011_09_16/thread_1_seq_67.341.762030989

22

23	$ rman target /

24

25	Recovery Manager: Release 11.2.0.2.0 - Production on Fri Sep 16 19:20:01 2011

26

27	Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

28

29	connected to target database: DROP (DBID=3027542406)

30

31	RMAN>  BACKUP AS COPY ARCHIVELOG ALL DELETE INPUT;

32

33	Starting backup at 16-SEP-11

34	current log archived

35	using target database control file instead of recovery catalog

36	allocated channel: ORA_DISK_1

37	channel ORA_DISK_1: SID=25 device type=DISK

38	channel ORA_DISK_1: starting archived log copy

39	input archived log thread=1 sequence=57 RECID=92 STAMP=762031143

40	output file name=/u01/app/oracle/flash_recovery_area/DROP/archivelog/2011_09_16/o1_mf_1_57_777lwyyp_.arc RECID=105 STAMP=762031215

41

.

42

.

43

.

44	channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01

45	channel ORA_DISK_1: deleting archived log(s)

46	archived log file name=/u01/app/oracle/flash_recovery_area/DROP/archivelog/2011_09_16/o1_mf_1_69_777lww4t_.arc RECID=104 STAMP=762031212

47	Finished backup at 16-SEP-11

48

49	Starting Control File and SPFILE Autobackup at 16-SEP-11

50	piece handle=/u01/app/oracle/flash_recovery_area/DROP/autobackup/2011_09_16/o1_mf_s_762031234_777lxlx5_.bkp comment=NONE

51	Finished Control File and SPFILE Autobackup at 16-SEP-11

52

53	RMAN> list archivelog all;

54

55	List of Archived Log Copies for database with db_unique_name DROP

56	=====================================================================

57

58	Key     Thrd Seq     S Low Time

59	------- ---- ------- - ---------

60	105     1    57      A 16-SEP-11

61	Name: /u01/app/oracle/flash_recovery_area/DROP/archivelog/2011_09_16/o1_mf_1_57_777lwyyp_.arc

62

63	106     1    58      A 16-SEP-11

64	Name: /u01/app/oracle/flash_recovery_area/DROP/archivelog/2011_09_16/o1_mf_1_58_777lx0dh_.arc

65

66

.

67

.

68

.

69	116     1    68      A 16-SEP-11

70	Name: /u01/app/oracle/flash_recovery_area/DROP/archivelog/2011_09_16/o1_mf_1_68_777lxhfl_.arc

71

72	117     1    69      A 16-SEP-11

73	Name: /u01/app/oracle/flash_recovery_area/DROP/archivelog/2011_09_16/o1_mf_1_69_777lxjwv_.arc

Migrating Backupset

01	list backupset summary;

02

03	List of Backups

04	===============

05	Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag

06	------- -- -- - ----------- --------------- ------- ------- ---------- ---

07	49      B  A  A DISK        16-SEP-11       1       1       NO         TAG20110916T190201

08	50      B  F  A DISK        16-SEP-11       1       1       NO         TAG20110916T190208

09	51      B  A  A DISK        16-SEP-11       1       1       NO         TAG20110916T190235

10	52      B  F  A DISK        16-SEP-11       1       1       NO         TAG20110916T190237

11	53      B  A  A DISK        16-SEP-11       1       1       NO         TAG20110916T190919

12	54      B  F  A DISK        16-SEP-11       1       1       NO         TAG20110916T190921

13	55      B  A  A DISK        16-SEP-11       1       1       NO         TAG20110916T190937

14	56      B  F  A DISK        16-SEP-11       1       1       NO         TAG20110916T190939

15

16	RMAN> LIST BACKUPPIECE TAG TAG20110916T190201;

17

18	List of Backup Pieces

19	BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name

20	------- ------- --- --- ----------- ----------- ----------

21	49      49      1   1   AVAILABLE   DISK        +DG_FRA/drop/backupset/2011_09_16/annnf0_tag20110916t190201_0.332.762030121

22

23	RMAN> BACKUP DEVICE TYPE DISK BACKUPSET ALL DELETE INPUT;

24

25	Starting backup at 16-SEP-11

26	using channel ORA_DISK_1

27	channel ORA_DISK_1: input backup set: count=107, stamp=762030121, piece=1

28	channel ORA_DISK_1: starting piece 1 at 16-SEP-11

29	channel ORA_DISK_1: backup piece +DG_FRA/drop/backupset/2011_09_16/annnf0_tag20110916t190201_0.332.762030121

30	piece handle=/u01/app/oracle/flash_recovery_area/DROP/backupset/2011_09_16/o1_mf_annnn_TAG20110916T190201_777ly4p4_.bkp comment=NONE

31

.

32

.

33

.

34	channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:00

35	channel ORA_DISK_1: input backup set: count=153, stamp=762031234, piece=1

36	channel ORA_DISK_1: starting piece 1 at 16-SEP-11

37	skipping backup piece handle /u01/app/oracle/flash_recovery_area/DROP/autobackup/2011_09_16/o1_mf_s_762031234_777lxlx5_.bkp; already exists

38	channel ORA_DISK_1: finished piece 1 at 16-SEP-11

39	channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:00

40	Finished backup at 16-SEP-11

41

42	Starting Control File and SPFILE Autobackup at 16-SEP-11

43	piece handle=/u01/app/oracle/flash_recovery_area/DROP/autobackup/2011_09_16/o1_mf_s_762031278_777lyz23_.bkp comment=NONE

44	Finished Control File and SPFILE Autobackup at 16-SEP-11

45

46	RMAN> LIST BACKUPSET SUMMARY;

47

48	List of Backups

49	===============

50	Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag

51	------- -- -- - ----------- --------------- ------- ------- ---------- ---

52	49      B  A  A DISK        16-SEP-11       1       1       NO         TAG20110916T190201

53	50      B  F  A DISK        16-SEP-11       1       1       NO         TAG20110916T190208

54	51      B  A  A DISK        16-SEP-11       1       1       NO         TAG20110916T190235

55	52      B  F  A DISK        16-SEP-11       1       1       NO         TAG20110916T190237

56	53      B  A  A DISK        16-SEP-11       1       1       NO         TAG20110916T190919

57	54      B  F  A DISK        16-SEP-11       1       1       NO         TAG20110916T190921

58	55      B  A  A DISK        16-SEP-11       1       1       NO         TAG20110916T190937

59	56      B  F  A DISK        16-SEP-11       1       1       NO         TAG20110916T190939

60	57      B  F  A DISK        16-SEP-11       1       1       NO         TAG20110916T191630

61	58      B  F  A DISK        16-SEP-11       1       1       NO         TAG20110916T191920

62	59      B  F  A DISK        16-SEP-11       1       1       NO         TAG20110916T192034

63	60      B  F  A DISK        16-SEP-11       1       1       NO         TAG20110916T192118

64

65	RMAN> LIST BACKUPPIECE TAG TAG20110916T190201;

66

67	List of Backup Pieces

68	BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name

69	------- ------- --- --- ----------- ----------- ----------

70	60      49      1   2   AVAILABLE   DISK        /u01/app/oracle/flash_recovery_area/DROP/backupset/2011_09_16/o1_mf_annnn_TAG20110916T190201_777ly4p4_.bkp

5.  Server Parameter File (SPFILE)

01	SQL> show parameter spfile

02	NAME                                 TYPE        VALUE

03	------------------------------------ ----------- ------------------------------

04	spfile                               string      +DG_DATA/drop/spfiledrop.ora

05

06	srvctl config database -d drop |grep Spfile

07	Spfile: +DG_DATA/drop/spfiledrop.ora

08

09	rman target /

10

11	Recovery Manager: Release 11.2.0.2.0 - Production on Fri Sep 16 19:56:33 2011

12

13	Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

14

15	connected to target database: DROP (DBID=3027542406)

16

17	RMAN> restore spfile to '$ORACLE_HOME/dbs/spfiledrop.ora';

18

19	Starting restore at 16-SEP-11

20	using target database control file instead of recovery catalog

21	allocated channel: ORA_DISK_1

22	channel ORA_DISK_1: SID=136 device type=DISK

23

24	channel ORA_DISK_1: starting datafile backup set restore

25	channel ORA_DISK_1: restoring SPFILE

26	output file name=$ORACLE_HOME/dbs/spfiledrop.ora

27	channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/DROP/autobackup/2011_09_16/o1_mf_s_762031278_777lyz23_.bkp

28	channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/DROP/autobackup/2011_09_16/o1_mf_s_762031278_777lyz23_.bkp tag=TAG20110916T192118

29	channel ORA_DISK_1: restored backup piece 1

30	channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

31	Finished restore at 16-SEP-11

32

33	# edit initdrop.ora on $ORACLE_HOME/dbs

34	vi initdrop

35	spfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfiledrop.ora'

36

37	# If you are using Oracle Restart you must change configuration with srvct.

38	$ srvctl modify database -d drop -p $ORACLE_HOME/dbs/spfiledrop.ora

39	$ srvctl config database -d drop |grep Spfile

40	Spfile: /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfiledrop.ora

41

42	SQL> shutdown immediate;

43	Database closed.

44	Database dismounted.

45	ORACLE instance shut down.

46	SQL> startup

47	ORACLE instance started.

48

49	Total System Global Area 1119043584 bytes

50	Fixed Size                  2218888 bytes

51	Variable Size             855639160 bytes

52	Database Buffers          251658240 bytes

53	Redo Buffers                9527296 bytes

54	Database mounted.

55	Database opened.

56	SQL> show parameter spfile

57

58	NAME                                 TYPE        VALUE

59	------------------------------------ ----------- ------------------------------

60	spfile                               string      /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfiledrop.ora