SQL> alter system set control_files='+DGRP2' scope=spfile;
SQL> alter system set db_create_file_dest='+DGRP2' scope=spfile;
SQL> alter system set db_recovery_file_dest='+DGRP2' scope=spfile;
Step: 2 Restart DB server to take above parameter value.
shutdown immediate;Step:3 Connect with RMAN session & restore controlfile on ASM system.
startup nomount
C:\>RMAN target=sys
Recovery Manager: Release 11.1.0.6.0 - Production on Thu Apr 17 10:12:53 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
target database Password:connected to target database: TEST (not mounted)
Restore Controlfile.
RMAN> restore controlfile from 'C:\app\m.taj\oradata\test\CONTROL01.ctl';
Starting restore at 17-APR-08using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=151 device type=DISK
channel ORA_DISK_1: copied control file copyoutput file name=+DGRP2/test/controlfile/current.256.652270419Finished restore at 17-APR-08
RMAN> alter database mount;
RMAN> backup as copy database format '+DGRP2';
RMAN> switch database to copy;
datafile 1 switched to datafile copy "+DGRP2/test/datafile/system.257.652270565"
datafile 2 switched to datafile copy "+DGRP2/test/datafile/sysaux.258.652270761"
datafile 3 switched to datafile copy "+DGRP2/test/datafile/undotbs1.259.652270927"
datafile 4 switched to datafile copy "+DGRP2/test/datafile/users.261.652270989"
C:\>sqlplus sys/oracle as sysdba
SQL> recover database using backup controlfile until cancel;
CANCEL
Media recovery cancelled.
SQL> alter database open resetlogs;
Database altered.
SQL> alter database tempfile 'c:\app\m.taj\oradata\test\temp01.dbf' 2 drop including datafiles;
SQL> alter tablespace temp add tempfile size 512m 2 autoextend on next 250m maxsize unlimited;
SQL> select tablespace_name, file_name, bytes from dba_temp_files;
TABLESPACE_NAME FILE_NAME BYTES
------------------------------ ------------------------------ ----------
TEMP +DGRP2/test/tempfile/temp.266. 536870912 652271571
SQL> select a.group#, a.member, b.bytes from v$logfile a, v$log b where a.group# = b.group#;Step:9 Recreate SPFILE on ASM diskgroup
GROUP# MEMBER BYTES
---------- ------------------------------------ ---------
3 C:\APP\M.TAJ\ORADATA\TEST\REDO03.LOG 52428800
2 C:\APP\M.TAJ\ORADATA\TEST\REDO02.LOG 52428800
1 C:\APP\M.TAJ\ORADATA\TEST\REDO01.LOG 52428800
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 UNUSED
3 UNUSED
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance test (thread 1)
ORA-00312: online log 1 thread 1: 'C:\APP\M.TAJ\ORADATA\TEST\REDO01.LOG'
When you get above error message then set checkpoint with below command.
SQL> alter system checkpoint global;
SQL> alter database drop logfile group 1;
SQL> alter database add logfile group 1 size 10m;
SQL> alter system checkpoint global;
SQL> alter database drop logfile group 2;
SQL> alter database add logfile group 2 size 10m;
SQL> alter system checkpoint global;
SQL> alter database drop logfile group 3;
SQL> alter database add logfile group 3 size 10m;
SQL> column member format a30
SQL> select a.group#, a.member, b.bytes from v$logfile a, v$log b where a.group# = b.group#;
GROUP# MEMBER BYTES
---------- ------------------------------ ----------
3 +DGRP2/test/onlinelog/group_3. 10485760 277.652273117
2 +DGRP2/test/onlinelog/group_2. 10485760 274.652273019
1 +DGRP2/test/onlinelog/group_1. 10485760 271.652272977
1 +DGRP2/test/onlinelog/group_1. 10485760 272.652272979
2 +DGRP2/test/onlinelog/group_2. 10485760 275.652273021
3 +DGRP2/test/onlinelog/group_3. 10485760 278.652273119
SQL> create pfile='c:\initTEST.ora' from spfile;
SQL> create spfile='+DGRP2/spfileTEST.ora' from pfile='c:\initTEST.ora';
RMAN> delete noprompt force copy;