技术博客 数据库、运维与工程实践笔记

How Migrate All Files on ASM to Non-ASM (Unix_Linux)

2024-06-10ASM / Linux/AIX / Oracle / RAC / RMAN / 性能优化

I’ll use PL/SQL scripts and RMAN commands to accomplish this.

I don’t like to use Oracle Managed File (OMF) for filesystem because I prefer readable names when we use non-ASM filesystem.

We have some others options which is litte bit easier, for example, migrating using OMF (filesystem) and RMAN, this way will be covered on this post.

I ask you if modify the PL/SQL code used in this post, leaving more efficiently, please send it back so I can make available to others by updating this post. (thank you)

Env Info: DATABASE NAME : DROP ORACLE DATAFILES/CONTROLFILE/TEMPFILE = /u01/app/oracle/oradata/drop 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

SQL> select name from v$controlfile;

NAME ——————————————————————————– +DG_DATA/drop/controlfile/current.275.761683397 +DG_FRA/drop/controlfile/current.281.761683397

SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down.

SQL> startup nomount ORACLE instance started.

Total System Global Area 1119043584 bytes Fixed Size 2218888 bytes Variable Size 855639160 bytes Database Buffers 251658240 bytes Redo Buffers 9527296 bytes

SQL> alter system set control_files='/u01/app/oracle/oradata/drop/control01.ctl', '/u01/app/oracle/flash_recovery_area/drop/control02.ctl' SCOPE=SPFILE SID='*';

System altered.

SQL> shutdown immediate; ORA-01507: database not mounted

ORACLE instance shut down.

$ rman target /

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

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

connected to target database (not started)

RMAN> startup nomount

Oracle instance started

Total System Global Area 1119043584 bytes

Fixed Size 2218888 bytes Variable Size 855639160 bytes Database Buffers 251658240 bytes Redo Buffers 9527296 bytes

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

Starting restore at 12-SEP-11 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=134 device type=DISK

channel ORA_DISK_1: copied control file copy output file name=/u01/app/oracle/oradata/drop/control01.ctl output file name=/u01/app/oracle/flash_recovery_area/drop/control02.ctl Finished restore at 12-SEP-11

RMAN> startup mount

database is already started database mounted released channel: ORA_DISK_1

RMAN> exit

Recovery Manager complete.

sqlplus / as sysdba

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

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

SQL> select name from v$controlfile;

NAME ——————————————————————————– /u01/app/oracle/oradata/drop/control01.ctl /u01/app/oracle/flash_recovery_area/drop/control02.ctl

2. Datafiles and Tempfiles

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

 FILE# NAME

———- ————————————————–

     1 +DG_DATA/drop/datafile/system.292.761712355
     2 +DG_DATA/drop/datafile/sysaux.291.761712395
     3 +DG_DATA/drop/datafile/undotbs1.290.761712439
     4 +DG_DATA/drop/datafile/users.289.761712455
     5 +DG_DATA/drop/datafile/users.288.761712457
     6 +DG_DATA/drop/datafile/users.287.761712457
     7 +DG_DATA/drop/datafile/users.277.761712459
     8 +DG_DATA/drop/datafile/users.278.761712461
     9 +DG_DATA/drop/datafile/users.279.761712461
    10 +DG_DATA/drop/datafile/users.276.761712463

10 rows selected.

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

 FILE# NAME

———- ————————————————–

     2 +DG_DATA/drop/tempfile/temp.286.761687721
     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. SET serveroutput ON; DECLARE vcount NUMBER:=0; vfname VARCHAR2(1024); CURSOR df IS

SELECT file#,
  rtrim(REPLACE(name,'+DG_DATA/drop/datafile/','/u01/app/oracle/oradata/drop/'),'.0123456789') AS name
FROM v$datafile;

CURSOR tp IS

SELECT file#,
  rtrim(REPLACE(name,'+DG_DATA/drop/tempfile/','/u01/app/oracle/oradata/drop/'),'.0123456789') AS name
FROM v$tempfile;

BEGIN dbms_output.put_line('CONFIGURE CONTROLFILE AUTOBACKUP ON;'); FOR dfrec IN df LOOP

IF dfrec.name  != vfname THEN
  vcount      :=1;
  vfname     := dfrec.name;
ELSE
  vcount := vcount+1;
  vfname:= dfrec.name;
END IF;
dbms_output.put_line('backup as copy datafile ' || dfrec.file# ||' format  "'||dfrec.name ||vcount||'.dbf";');

END LOOP; dbms_output.put_line('run'); dbms_output.put_line('{'); FOR dfrec IN df LOOP

IF dfrec.name  != vfname THEN
  vcount      :=1;
  vfname     := dfrec.name;
ELSE
  vcount := vcount+1;
  vfname:= dfrec.name;
END IF;
dbms_output.put_line('set newname for datafile ' || dfrec.file# ||'  to  '''||dfrec.name ||vcount||'.dbf'' ;');

END LOOP; FOR tprec IN tp LOOP

IF tprec.name  !=  vfname THEN
  vcount      :=1;
  vfname     := tprec.name;
ELSE
  vcount := vcount+1;
  vfname:= tprec.name;
END IF;
dbms_output.put_line('set newname for tempfile ' || tprec.file# ||'  to  '''||tprec.name ||vcount||'.dbf'' ;');
END LOOP;
dbms_output.put_line('switch tempfile all;');
dbms_output.put_line('switch datafile all;');
dbms_output.put_line('restore database;');
dbms_output.put_line('recover database;');
dbms_output.put_line('}');
dbms_output.put_line('alter database open;');
dbms_output.put_line('exit');

END; / Database must be Mounted SQL> SET serveroutput ON; SQL> DECLARE . . . 57 dbms_output.put_line('exit'); 58 END; 59 /

CONFIGURE CONTROLFILE AUTOBACKUP ON; backup as copy datafile 1 format "/u01/app/oracle/oradata/drop/system1.dbf"; backup as copy datafile 2 format "/u01/app/oracle/oradata/drop/sysaux1.dbf"; backup as copy datafile 3 format "/u01/app/oracle/oradata/drop/undotbs1.dbf"; backup as copy datafile 4 format "/u01/app/oracle/oradata/drop/users1.dbf"; backup as copy datafile 5 format "/u01/app/oracle/oradata/drop/users2.dbf"; backup as copy datafile 6 format "/u01/app/oracle/oradata/drop/users3.dbf"; backup as copy datafile 7 format "/u01/app/oracle/oradata/drop/users4.dbf"; backup as copy datafile 8 format "/u01/app/oracle/oradata/drop/users5.dbf"; backup as copy datafile 9 format "/u01/app/oracle/oradata/drop/users6.dbf"; backup as copy datafile 10 format "/u01/app/oracle/oradata/drop/users7.dbf"; run { set newname for datafile 1 to '/u01/app/oracle/oradata/drop/system1.dbf' ; set newname for datafile 2 to '/u01/app/oracle/oradata/drop/sysaux1.dbf' ; set newname for datafile 3 to '/u01/app/oracle/oradata/drop/undotbs1.dbf' ; set newname for datafile 4 to '/u01/app/oracle/oradata/drop/users1.dbf' ; set newname for datafile 5 to '/u01/app/oracle/oradata/drop/users2.dbf' ; set newname for datafile 6 to '/u01/app/oracle/oradata/drop/users3.dbf' ; set newname for datafile 7 to '/u01/app/oracle/oradata/drop/users4.dbf' ; set newname for datafile 8 to '/u01/app/oracle/oradata/drop/users5.dbf' ; set newname for datafile 9 to '/u01/app/oracle/oradata/drop/users6.dbf' ; set newname for datafile 10 to '/u01/app/oracle/oradata/drop/users7.dbf' ; set newname for tempfile 1 to '/u01/app/oracle/oradata/drop/temp1.dbf' ; set newname for tempfile 2 to '/u01/app/oracle/oradata/drop/temp2.dbf' ; switch tempfile all; switch datafile all; restore database; recover database; } alter database open; 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. nohup rman target / cmdfile migrate_db.rcv log migrate_db.log &

cat migrate_db.log

using target database control file instead of recovery catalog old RMAN configuration parameters: CONFIGURE CONTROLFILE AUTOBACKUP ON; new RMAN configuration parameters: CONFIGURE CONTROLFILE AUTOBACKUP ON; new RMAN configuration parameters are successfully stored

Starting backup at 13-SEP-11 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=137 device type=DISK channel ORA_DISK_1: starting datafile copy input datafile file number=00001 name=+DG_DATA/drop/datafile/system.292.761712355 output file name=/u01/app/oracle/oradata/drop/system1.dbf tag=TAG20110913T025233 RECID=68 STAMP=761712766 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15 Finished backup at 13-SEP-11

Starting Control File and SPFILE Autobackup at 13-SEP-11 piece handle=/u01/app/oracle/flash_recovery_area/DROP/autobackup/2011_09_13/o1_mf_s_761712652_76xvxlw3_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 13-SEP-11

Starting backup at 13-SEP-11 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00002 name=+DG_DATA/drop/datafile/sysaux.291.761712395 output file name=/u01/app/oracle/oradata/drop/sysaux1.dbf tag=TAG20110913T025253 RECID=69 STAMP=761712784 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15 Finished backup at 13-SEP-11

Starting Control File and SPFILE Autobackup at 13-SEP-11 piece handle=/u01/app/oracle/flash_recovery_area/DROP/autobackup/2011_09_13/o1_mf_s_761712652_76xvy5h5_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 13-SEP-11

Starting backup at 13-SEP-11 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00003 name=+DG_DATA/drop/datafile/undotbs1.290.761712439 output file name=/u01/app/oracle/oradata/drop/undotbs1.dbf tag=TAG20110913T025312 RECID=70 STAMP=761712796 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07 Finished backup at 13-SEP-11

Starting Control File and SPFILE Autobackup at 13-SEP-11 piece handle=/u01/app/oracle/flash_recovery_area/DROP/autobackup/2011_09_13/o1_mf_s_761712652_76xvyjkb_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 13-SEP-11

Starting backup at 13-SEP-11 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00004 name=+DG_DATA/drop/datafile/users.289.761712455 output file name=/u01/app/oracle/oradata/drop/users1.dbf tag=TAG20110913T025321 RECID=71 STAMP=761712802 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 13-SEP-11

Starting Control File and SPFILE Autobackup at 13-SEP-11 piece handle=/u01/app/oracle/flash_recovery_area/DROP/autobackup/2011_09_13/o1_mf_s_761712652_76xvymrv_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 13-SEP-11

Starting backup at 13-SEP-11 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00005 name=+DG_DATA/drop/datafile/users.288.761712457 output file name=/u01/app/oracle/oradata/drop/users2.dbf tag=TAG20110913T025326 RECID=72 STAMP=761712807 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 13-SEP-11

Starting Control File and SPFILE Autobackup at 13-SEP-11 piece handle=/u01/app/oracle/flash_recovery_area/DROP/autobackup/2011_09_13/o1_mf_s_761712652_76xvys74_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 13-SEP-11

Starting backup at 13-SEP-11 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00006 name=+DG_DATA/drop/datafile/users.287.761712457 output file name=/u01/app/oracle/oradata/drop/users3.dbf tag=TAG20110913T025332 RECID=73 STAMP=761712812 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 13-SEP-11

Starting Control File and SPFILE Autobackup at 13-SEP-11 piece handle=/u01/app/oracle/flash_recovery_area/DROP/autobackup/2011_09_13/o1_mf_s_761712652_76xvyydl_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 13-SEP-11

Starting backup at 13-SEP-11 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00007 name=+DG_DATA/drop/datafile/users.277.761712459 output file name=/u01/app/oracle/oradata/drop/users4.dbf tag=TAG20110913T025335 RECID=74 STAMP=761712816 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 13-SEP-11

Starting Control File and SPFILE Autobackup at 13-SEP-11 piece handle=/u01/app/oracle/flash_recovery_area/DROP/autobackup/2011_09_13/o1_mf_s_761712652_76xvz1qq_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 13-SEP-11

Starting backup at 13-SEP-11 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00008 name=+DG_DATA/drop/datafile/users.278.761712461 output file name=/u01/app/oracle/oradata/drop/users5.dbf tag=TAG20110913T025338 RECID=75 STAMP=761712819 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 13-SEP-11

Starting Control File and SPFILE Autobackup at 13-SEP-11 piece handle=/u01/app/oracle/flash_recovery_area/DROP/autobackup/2011_09_13/o1_mf_s_761712652_76xvz4xm_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 13-SEP-11

Starting backup at 13-SEP-11 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00009 name=+DG_DATA/drop/datafile/users.279.761712461 output file name=/u01/app/oracle/oradata/drop/users6.dbf tag=TAG20110913T025342 RECID=76 STAMP=761712822 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 13-SEP-11

Starting Control File and SPFILE Autobackup at 13-SEP-11 piece handle=/u01/app/oracle/flash_recovery_area/DROP/autobackup/2011_09_13/o1_mf_s_761712652_76xvz878_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 13-SEP-11

Starting backup at 13-SEP-11 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00010 name=+DG_DATA/drop/datafile/users.276.761712463 output file name=/u01/app/oracle/oradata/drop/users7.dbf tag=TAG20110913T025345 RECID=77 STAMP=761712825 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 13-SEP-11

Starting Control File and SPFILE Autobackup at 13-SEP-11 piece handle=/u01/app/oracle/flash_recovery_area/DROP/autobackup/2011_09_13/o1_mf_s_761712652_76xvzcjb_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 13-SEP-11

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

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

datafile 1 switched to datafile copy input datafile copy RECID=68 STAMP=761712766 file name=/u01/app/oracle/oradata/drop/system1.dbf datafile 2 switched to datafile copy input datafile copy RECID=69 STAMP=761712784 file name=/u01/app/oracle/oradata/drop/sysaux1.dbf datafile 3 switched to datafile copy input datafile copy RECID=70 STAMP=761712796 file name=/u01/app/oracle/oradata/drop/undotbs1.dbf datafile 4 switched to datafile copy input datafile copy RECID=71 STAMP=761712802 file name=/u01/app/oracle/oradata/drop/users1.dbf datafile 5 switched to datafile copy input datafile copy RECID=72 STAMP=761712807 file name=/u01/app/oracle/oradata/drop/users2.dbf datafile 6 switched to datafile copy input datafile copy RECID=73 STAMP=761712812 file name=/u01/app/oracle/oradata/drop/users3.dbf datafile 7 switched to datafile copy input datafile copy RECID=74 STAMP=761712816 file name=/u01/app/oracle/oradata/drop/users4.dbf datafile 8 switched to datafile copy input datafile copy RECID=75 STAMP=761712819 file name=/u01/app/oracle/oradata/drop/users5.dbf datafile 9 switched to datafile copy input datafile copy RECID=76 STAMP=761712822 file name=/u01/app/oracle/oradata/drop/users6.dbf datafile 10 switched to datafile copy input datafile copy RECID=77 STAMP=761712825 file name=/u01/app/oracle/oradata/drop/users7.dbf

Starting restore at 13-SEP-11 using channel ORA_DISK_1

skipping datafile 1; already restored to file /u01/app/oracle/oradata/drop/system1.dbf skipping datafile 2; already restored to file /u01/app/oracle/oradata/drop/sysaux1.dbf skipping datafile 3; already restored to file /u01/app/oracle/oradata/drop/undotbs1.dbf skipping datafile 4; already restored to file /u01/app/oracle/oradata/drop/users1.dbf skipping datafile 5; already restored to file /u01/app/oracle/oradata/drop/users2.dbf skipping datafile 6; already restored to file /u01/app/oracle/oradata/drop/users3.dbf skipping datafile 7; already restored to file /u01/app/oracle/oradata/drop/users4.dbf skipping datafile 8; already restored to file /u01/app/oracle/oradata/drop/users5.dbf skipping datafile 9; already restored to file /u01/app/oracle/oradata/drop/users6.dbf skipping datafile 10; already restored to file /u01/app/oracle/oradata/drop/users7.dbf restore not done; all files read only, offline, or already restored Finished restore at 13-SEP-11

Starting recover at 13-SEP-11 using channel ORA_DISK_1

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

Finished recover at 13-SEP-11

database opened

Recovery Manager complete. SQL> select file#, name from v$datafile;

 FILE# NAME

———- ————————————————–

     1 /u01/app/oracle/oradata/drop/system1.dbf
     2 /u01/app/oracle/oradata/drop/sysaux1.dbf
     3 /u01/app/oracle/oradata/drop/undotbs1.dbf
     4 /u01/app/oracle/oradata/drop/users1.dbf
     5 /u01/app/oracle/oradata/drop/users2.dbf
     6 /u01/app/oracle/oradata/drop/users3.dbf
     7 /u01/app/oracle/oradata/drop/users4.dbf
     8 /u01/app/oracle/oradata/drop/users5.dbf
     9 /u01/app/oracle/oradata/drop/users6.dbf
    10 /u01/app/oracle/oradata/drop/users7.dbf

10 rows selected.

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

 FILE# NAME

———- ————————————————–

     2 /u01/app/oracle/oradata/drop/temp2.dbf
     1 /u01/app/oracle/oradata/drop/temp1.dbf

SQL>

3. Online Logs (Redo)

Database must be Open SQL> select group#,member from v$logfile order by 1;

GROUP# MEMBER

———- ——————————————————————————————

     1 +DG_DATA/drop/onlinelog/group_1.285.761755579
     2 +DG_DATA/drop/onlinelog/group_2.284.761755615
     3 +DG_DATA/drop/onlinelog/group_3.278.761755651
     4 +DG_DATA/drop/onlinelog/group_4.283.761754951
     5 +DG_DATA/drop/onlinelog/group_5.276.761754957
     6 +DG_DATA/drop/onlinelog/group_6.282.761754963
     7 +DG_DATA/drop/onlinelog/group_7.279.761754967

SQL> SELECT group# grp,

  thread# thr,
  bytes/1024 bytes_k,
  'NO' srl,
  status
FROM v$log

UNION SELECT group# grp,

thread# thr,
bytes/1024 bytes_k,
'YES' srl,
status

FROM v$standby_log ORDER BY 1;

   GRP        THR    BYTES_K SRL STATUS

———- ———- ———- — —————-

     1          1     102400 NO  INACTIVE
     2          1     102400 NO  CURRENT
     3          1     102400 NO  UNUSED
     4          1     102400 YES UNASSIGNED
     5          1     102400 YES UNASSIGNED
     6          1     102400 YES UNASSIGNED
     7          1     102400 YES UNASSIGNED

Another PL/SQL, will work only if database have more than 2 Logfile Group DECLARE vgroup NUMBER; CURSOR rlc IS

SELECT group# grp,
  thread# thr,
  bytes/1024 bytes_k,
  'NO' srl,
  status
FROM v$log

UNION SELECT group# grp,

thread# thr,
bytes/1024 bytes_k,
'YES' srl,
status

FROM v$standby_log ORDER BY status DESC; stmt VARCHAR2(2048); swtstmt VARCHAR2(1024) := 'alter system switch logfile'; ckpstmt VARCHAR2(1024) := 'alter system checkpoint global'; BEGIN FOR rlcRec IN rlc LOOP

IF (rlcRec.srl = 'YES') THEN
  BEGIN
    SELECT group#
    INTO vgroup
    FROM v$standby_log
    WHERE group# = rlcRec.grp
    AND thread#  = rlcRec.thr
    AND (status  ='CURRENT'
    OR status    = 'ACTIVE');
    stmt        := 'alter database drop standby logfile group ' || rlcRec.grp;
    EXECUTE IMMEDIATE swtstmt;
    EXECUTE IMMEDIATE ckpstmt;
    EXECUTE immediate stmt;
    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';
    EXECUTE immediate stmt;
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    BEGIN
      EXECUTE immediate ckpstmt;
      stmt := 'alter database drop standby logfile group ' || rlcRec.grp;
      EXECUTE immediate stmt;
      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';
      EXECUTE immediate stmt;
    END;
  END;
ELSE
  BEGIN
    SELECT group#
    INTO vgroup
    FROM v$log
    WHERE group# = rlcRec.grp
    AND thread#  = rlcRec.thr
    AND (status  ='CURRENT'
    OR status    = 'ACTIVE');
    stmt        := 'alter database drop logfile group ' || rlcRec.grp;
    EXECUTE IMMEDIATE swtstmt;
    EXECUTE IMMEDIATE ckpstmt;
    EXECUTE immediate stmt;
    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';
    EXECUTE immediate stmt;
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    BEGIN
      EXECUTE immediate ckpstmt;
      stmt := 'alter database drop logfile group ' || rlcRec.grp;
      EXECUTE immediate stmt;
      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';
      EXECUTE immediate stmt;
    END;
  END;
END IF;

END LOOP; END; / SQL> select group#,member from v$logfile order by 1;

GROUP# MEMBER

———- ——————————————————————————————

     1 /u01/app/oracle/oradata/drop/redo_1_1_a.log
     2 /u01/app/oracle/oradata/drop/redo_2_1_a.log
     3 /u01/app/oracle/oradata/drop/redo_3_1_a.log
     4 /u01/app/oracle/oradata/drop/redo_stby4_1_a.log
     5 /u01/app/oracle/oradata/drop/redo_stby5_1_a.log
     6 /u01/app/oracle/oradata/drop/redo_stby6_1_a.log
     7 /u01/app/oracle/oradata/drop/redo_stby7_1_a.log

7 rows selected.

SQL> SELECT group# grp,

  thread# thr,
  bytes/1024 bytes_k,
  'NO' srl,
  status
FROM v$log

UNION SELECT group# grp,

thread# thr,
bytes/1024 bytes_k,
'YES' srl,
status

FROM v$standby_log ORDER BY 1; 2 3 4 5 6 7 8 9 10 11 12 13 14

   GRP        THR    BYTES_K SRL STATUS

———- ———- ———- — —————-

     1          1     102400 NO  CURRENT
     2          1     102400 NO  UNUSED
     3          1     102400 NO  UNUSED
     4          1     102400 YES UNASSIGNED
     5          1     102400 YES UNASSIGNED
     6          1     102400 YES UNASSIGNED
     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. SQL> show parameter db_recovery_file NAME TYPE VALUE ———————————— ———– —————————— db_recovery_file_dest string +DG_FRA db_recovery_file_dest_size big integer 100G

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

SQL> show parameter db_recovery_file NAME TYPE VALUE ———————————— ———– —————————— db_recovery_file_dest string /u01/app/oracle/flash_recovery_area db_recovery_file_dest_size big integer 100G Migrating Archivelog RMAN> list archivelog all;

using target database control file instead of recovery catalog List of Archived Log Copies for database with db_unique_name DROP =====================================================================

Key Thrd Seq S Low Time ——- —- ——- – ——— 80 1 57 A 16-SEP-11

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

81 1 58 A 16-SEP-11

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

. . . 89 1 66 A 16-SEP-11

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

90 1 67 A 16-SEP-11

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

$ rman target /

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

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

connected to target database: DROP (DBID=3027542406)

RMAN> BACKUP AS COPY ARCHIVELOG ALL DELETE INPUT;

Starting backup at 16-SEP-11 current log archived using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=25 device type=DISK channel ORA_DISK_1: starting archived log copy input archived log thread=1 sequence=57 RECID=92 STAMP=762031143 output file name=/u01/app/oracle/flash_recovery_area/DROP/archivelog/2011_09_16/o1_mf_1_57_777lwyyp_.arc RECID=105 STAMP=762031215 . . . channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01 channel ORA_DISK_1: deleting archived log(s) 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 Finished backup at 16-SEP-11

Starting Control File and SPFILE Autobackup at 16-SEP-11 piece handle=/u01/app/oracle/flash_recovery_area/DROP/autobackup/2011_09_16/o1_mf_s_762031234_777lxlx5_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 16-SEP-11

RMAN> list archivelog all;

List of Archived Log Copies for database with db_unique_name DROP =====================================================================

Key Thrd Seq S Low Time ——- —- ——- – ——— 105 1 57 A 16-SEP-11

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

106 1 58 A 16-SEP-11

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

. . . 116 1 68 A 16-SEP-11

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

117 1 69 A 16-SEP-11

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

Migrating Backupset list backupset summary;

List of Backups =============== Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag ——- — — – ———– ————— ——- ——- ———- — 49 B A A DISK 16-SEP-11 1 1 NO TAG20110916T190201 50 B F A DISK 16-SEP-11 1 1 NO TAG20110916T190208 51 B A A DISK 16-SEP-11 1 1 NO TAG20110916T190235 52 B F A DISK 16-SEP-11 1 1 NO TAG20110916T190237 53 B A A DISK 16-SEP-11 1 1 NO TAG20110916T190919 54 B F A DISK 16-SEP-11 1 1 NO TAG20110916T190921 55 B A A DISK 16-SEP-11 1 1 NO TAG20110916T190937 56 B F A DISK 16-SEP-11 1 1 NO TAG20110916T190939

RMAN> LIST BACKUPPIECE TAG TAG20110916T190201;

List of Backup Pieces BP Key BS Key Pc# Cp# Status Device Type Piece Name ——- ——- — — ———– ———– ———- 49 49 1 1 AVAILABLE DISK +DG_FRA/drop/backupset/2011_09_16/annnf0_tag20110916t190201_0.332.762030121

RMAN> BACKUP DEVICE TYPE DISK BACKUPSET ALL DELETE INPUT;

Starting backup at 16-SEP-11 using channel ORA_DISK_1 channel ORA_DISK_1: input backup set: count=107, stamp=762030121, piece=1 channel ORA_DISK_1: starting piece 1 at 16-SEP-11 channel ORA_DISK_1: backup piece +DG_FRA/drop/backupset/2011_09_16/annnf0_tag20110916t190201_0.332.762030121 piece handle=/u01/app/oracle/flash_recovery_area/DROP/backupset/2011_09_16/o1_mf_annnn_TAG20110916T190201_777ly4p4_.bkp comment=NONE . . . channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:00 channel ORA_DISK_1: input backup set: count=153, stamp=762031234, piece=1 channel ORA_DISK_1: starting piece 1 at 16-SEP-11 skipping backup piece handle /u01/app/oracle/flash_recovery_area/DROP/autobackup/2011_09_16/o1_mf_s_762031234_777lxlx5_.bkp; already exists channel ORA_DISK_1: finished piece 1 at 16-SEP-11 channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:00 Finished backup at 16-SEP-11

Starting Control File and SPFILE Autobackup at 16-SEP-11 piece handle=/u01/app/oracle/flash_recovery_area/DROP/autobackup/2011_09_16/o1_mf_s_762031278_777lyz23_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 16-SEP-11

RMAN> LIST BACKUPSET SUMMARY;

List of Backups =============== Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag ——- — — – ———– ————— ——- ——- ———- — 49 B A A DISK 16-SEP-11 1 1 NO TAG20110916T190201 50 B F A DISK 16-SEP-11 1 1 NO TAG20110916T190208 51 B A A DISK 16-SEP-11 1 1 NO TAG20110916T190235 52 B F A DISK 16-SEP-11 1 1 NO TAG20110916T190237 53 B A A DISK 16-SEP-11 1 1 NO TAG20110916T190919 54 B F A DISK 16-SEP-11 1 1 NO TAG20110916T190921 55 B A A DISK 16-SEP-11 1 1 NO TAG20110916T190937 56 B F A DISK 16-SEP-11 1 1 NO TAG20110916T190939 57 B F A DISK 16-SEP-11 1 1 NO TAG20110916T191630 58 B F A DISK 16-SEP-11 1 1 NO TAG20110916T191920 59 B F A DISK 16-SEP-11 1 1 NO TAG20110916T192034 60 B F A DISK 16-SEP-11 1 1 NO TAG20110916T192118

RMAN> LIST BACKUPPIECE TAG TAG20110916T190201;

List of Backup Pieces BP Key BS Key Pc# Cp# Status Device Type Piece Name ——- ——- — — ———– ———– ———- 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)

SQL> show parameter spfile NAME TYPE VALUE ———————————— ———– —————————— spfile string +DG_DATA/drop/spfiledrop.ora

srvctl config database -d drop |grep Spfile Spfile: +DG_DATA/drop/spfiledrop.ora

rman target /

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

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

connected to target database: DROP (DBID=3027542406)

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

Starting restore at 16-SEP-11 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=136 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: restoring SPFILE output file name=$ORACLE_HOME/dbs/spfiledrop.ora 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 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 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 16-SEP-11

edit initdrop.ora on $ORACLE_HOME/dbs

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

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

$ srvctl modify database -d drop -p $ORACLE_HOME/dbs/spfiledrop.ora $ srvctl config database -d drop |grep Spfile Spfile: /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfiledrop.ora

SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started.

Total System Global Area 1119043584 bytes Fixed Size 2218888 bytes Variable Size 855639160 bytes Database Buffers 251658240 bytes Redo Buffers 9527296 bytes Database mounted. Database opened. SQL> show parameter spfile

NAME TYPE VALUE ———————————— ———– —————————— spfile string /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfiledrop.ora

You can comment … I accept hints ….

Rodrigo Murillo G (Quote)

> I did a little change in the PL/SQL script which generates rman commands for backup and restore. The change allows numbers on the datafile names (i.e. datafiles for table partitions tablespaces by years, quarters, etc). Also, the restore database and switch commands were changed in their order (originally: first switch then restore), and the order that worked for me was first restore then switch.

> SET serveroutput ON; DECLARE vcount NUMBER:=0; vfname VARCHAR2(1024); CURSOR df IS SELECT file#, replace (substr ( name, 1, instr( name, '.', 1 , 1 ) -1) || '_', '+DATA', '/data_oracle' ) AS name FROM v$datafile order by name; CURSOR tp IS SELECT file#, rtrim(REPLACE(name,'+DATA/ictxcyc/tempfile/','/data_oracle/ictxcyc/datafile/'),'.0123456789′) AS name FROM v$tempfile; BEGIN dbms_output.put_line('CONFIGURE CONTROLFILE AUTOBACKUP ON;'); FOR dfrec IN df LOOP IF dfrec.name != vfname THEN vcount :=1; vfname := dfrec.name; ELSE vcount := vcount+1; vfname:= dfrec.name; END IF; dbms_output.put_line('backup as copy datafile ' || dfrec.file# ||' format "'||dfrec.name ||vcount||'.dbf";'); END LOOP; dbms_output.put_line('run'); dbms_output.put_line('{'); FOR dfrec IN df LOOP IF dfrec.name != vfname THEN vcount :=1; vfname := dfrec.name; ELSE vcount := vcount+1; vfname:= dfrec.name; END IF; dbms_output.put_line('set newname for datafile ' || dfrec.file# ||' to "'||dfrec.name ||vcount||'.dbf" ;' ); END LOOP; FOR tprec IN tp LOOP IF tprec.name != vfname THEN vcount :=1; vfname := tprec.name; ELSE vcount := vcount+1; vfname:= tprec.name; END IF; dbms_output.put_line('set newname for tempfile ' || tprec.file# ||' to "'||tprec.name ||vcount||'.dbf" ;'); END LOOP; dbms_output.put_line('restore database;'); dbms_output.put_line('switch tempfile all;'); dbms_output.put_line('switch datafile all;'); dbms_output.put_line('recover database;'); dbms_output.put_line('}'); dbms_output.put_line('alter database open;'); dbms_output.put_line('exit'); END; / Enjoy… Advertisements### Rate this:

Share this:

		Like
	](https://widgets.wp.com/likes/#)Be the first to like this.### _Related_

ACFS Technical Overview and Deployment GuideIn "11g R2"

How Convert full Database 10g Linux-x86-64bit to AIX-64bit different Endian FormatIn "10g R1"

Using ACFS Snapshot to create a consistent recovery point of ORACLE_HOMEIn "11g R2"