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.

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…


How to Migrate a Microsoft SQL Server Database to Oracle Database 11g

Overview

What Is SQL Developer?

Oracle SQL Developer is a free graphical tool that enhances productivity and simplifies database development tasks. Using Oracle SQL Developer, you can browse database objects, run SQL statements, edit and debug PL/SQL statements and run reports, whether provided or created.

Microsoft SQL Server Migration Overview

Using Oracle SQL Developer Migration Workbench, you can quickly migrate your third-party database to Oracle.

There are four main steps in the database migration process:

 

Capture the Source Database The first step is to capture a “snapshot” of the Microsoft SQL Server database. This can be done in two ways.

  1. Online Capture: This requires creating a connection in SQL Developer to a live Microsoft SQL Server database. Using JDBC, the Microsoft SQL Server database metadata can be accessed and the Capture Model created.
  2. Offline Capture: This involves BCP scripts generated by SQL Developer to extract the Microsoft SQL Server database metadata to files. These files can then be “captured” by SQL Developer to create the Captured Model.

The second method is what you will perform in this tutorial.

Using SQL Developers Offline Capture feature, the Microsoft SQL Server Northwind sample database has been extracted into offline data files. The SQLServer2005.ocp file generated by the Capture tool contains the database schema information for the Microsoft SQL Server Northwind Traders database. Oracle SQL Developer Migration Workbench uses this file as the basis for building a representation of the structure of the source Microsoft SQL Server database. This structure is called the Captured Model.

Convert the Captured Database Oracle SQL Developer Migration Workbench uses the Captured Model to convert the captured objects to Oracle-format objects, building up a representation of the structure of the destination database. This structure is called the Converted Model.
Generate the Oracle Database Oracle SQL Developer Migration Workbench generates DDL statements to create the new Oracle database, based upon the Converted Model objects. Running the DDL statements will result in the creation of the objects in the Oracle database.
Migrate the Data The last step in the process is to migrate the data. You can do this in one of two ways.

  1. Online Data Move: You can create a connection from within Oracle SQL Developer to your Microsoft SQL Server source database and migrate the data.
  2. Offline Data Move: You can export the data from Microsoft SQL Server. SQL Developer</font> will create a series of BCP and sqlldr files that you can run from a batch file.

The second method is what you will perform in this tutorial.

In this tutorial, the required scripts for the offline migration have already been generated and modified. If you do not have time to perform this tutorial, you can also view the offline method, click here.

To view the steps for the online method, click here.

See link below

http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/hol08/sqldev_migration/mssqlserver/migrate_microsoft_sqlserver_otn.htm

Enjoy

 


How Convert full Database 10g Linux-x86-64bit to AIX-64bit different Endian Format

In this post I’ll show you how to migrate full Oracle Database 10g on Linux (Little Endian) for AIX (Big Endian).

I choose Oracle 10g, so this post remains in force for higher versions.

To use transportable database feature, we need know which to recreate an entire database from one platform on another the platforms have the same endian order.

I have following scenario.

 

Source Host: libano

Platform: Linux OEL 5.5 x64

Endian Format: Little

Database Release: 10.2.0.5

Filesystem: Ext3

 

 

Destination Host: butao

Platform: AIX 6.1 64bit

Endian Format: Big

Database Release: 10.2.0.5

Filesystem: jfs2

 

We can check if is compatible (same endian format) executing query below on source host.

 SELECT * FROM  V$DB_TRANSPORTABLE_PLATFORM;
 PLATFORM_ID PLATFORM_NAME                            ENDIAN_FORMAT
 ----------- ---------------------------------------- --------------
 7 Microsoft Windows IA (32-bit)            Little
 10 Linux IA (32-bit)                        Little
 5 HP Tru64 UNIX                            Little
 11 Linux IA (64-bit)                        Little
 15 HP Open VMS                              Little
 8 Microsoft Windows IA (64-bit)            Little
 13 Linux x86 64-bit                         Little
 12 Microsoft Windows x86 64-bit             Little
 17 Solaris Operating System (x86)           Little
 20 Solaris Operating System (x86-64)        Little
 19 HP IA Open VMS                           Little

 11 rows selected.
 

AIX Platform don’t appear here because source platform is Little Endian. So we can discard the use of transportable database feature.

But we still have hope. We can use Cross-Platform Transportable Tablespace.

CONVERT does not perform in-place conversion of datafiles. It produces output files in the correct format for use on the destination platform, but does not alter the contents of the source datafiles.

If you can convert datafile on source platform you use the CONVERT TABLESPACE command, but if you can convert datafile on destination platform you must use the CONVERT DATAFILE command.

The most important thing in this migration, you know the restrictions.

Restrictions on CONVERT TABLESPACE and CONVERT DATAFILE

Note the following restrictions on CONVERT TABLESPACE and CONVERT DATAFILE:

  • Both source and destination databases must be running with the COMPATIBLE initialization parameter set to 10.0 or higher.
  • Not all combinations of source and destination platforms are supported. To determine whether your source and destination platforms are supported, query V$TRANSPORTABLE_PLATFORM. If both the source and destination platforms are listed in this view, then CONVERT can be used to prepare datafiles from one platform for use on the other.
  • The Tablespace must be made read-write at least once in release 10g before it can be transported to another platform using CONVERT. Hence, any read-only Tablespace (or previously transported Tablespace) from a previous release must first be made read-write before they can be transported to another platform.
  • RMAN does not process user datatypes that require endian conversions.
  • Prior to release 10g, CLOBs were created with a variable-width character set and stored in an endian-dependent format. The CONVERT command does not perform conversions on these CLOBs. Instead, RMAN captures the endian format of each LOB column and propagates it to the target database. Subsequent reads of this data by the SQL layer interpret the data correctly based upon either endian format, and write the data out in an endian-independent format if the Tablespace is writeable. CLOBs created in Oracle Database Release 10g are stored in character set AL16UTF16, which is platform-independent.

Kick-off

Verify Platform Support…

Source Host/Database

v$database shows what platform the database is running on.

v$transportable_platform shows all platforms supported by Oracle for cross-platform tablespace transport.

 SET LINESIZE 100
 COL PLATFORM_NAME FOR A40
 SELECT A.platform_id, A.platform_name, B.endian_format
 FROM   v$database A, v$transportable_platform B
 WHERE  B.platform_id (+) = A.platform_id;
 

Output of Source Platform / Host: libano:

 PLATFORM_ID PLATFORM_NAME                            ENDIAN_FORMAT
 ----------- ---------------------------------------- --------------
 13 Linux x86 64-bit                         Little
 

Output Destination Platform / Host : butao:

 PLATFORM_ID PLATFORM_NAME                            ENDIAN_FORMAT
 ----------- ---------------------------------------- --------------
 6 AIX-Based Systems (64-bit)               Big
 

In this test the database has only one user to be migrated. You can use the same procedure to migrate all users of the database (except SYS-Users) at the same time.

Set of Tablespace to be transported must be self-contained. Objects in the Tablespace set cannot reference or depend on objects outside the set (i.e. all segments from user, must be migrated same time). Self-containment problems will cause metadata export to fail.

Self-Containments

Then let’s Check Self-Containment

Let us identify which is the Tablespace are necessary for our migration.

In this example I’ll migrate the user/schema named SOE.

First step is identifying which Tablespace will be transportable:


SELECT  TABLESPACE_NAME
 FROM DBA_SEGMENTS
 WHERE OWNER='SOE'
 GROUP BY TABLESPACE_NAME
 ORDER BY 1;

TABLESPACE_NAME
 ------------------------------
 SOE
 SOE_FAST_PART
 SOE_INDX
 SOE_LOB
 SOE_SLOW_PART
 

Let’s take a look which type of segment we have:

 SELECT  SEGMENT_TYPE,TABLESPACE_NAME
 FROM DBA_SEGMENTS
 WHERE OWNER='SOE'
 GROUP BY SEGMENT_TYPE,TABLESPACE_NAME
 ORDER BY 2;

 SEGMENT_TYPE       TABLESPACE_NAME
 ------------------ ------------------------------
 TABLE              SOE
 TABLE PARTITION    SOE
 TABLE PARTITION    SOE_FAST_PART
 INDEX              SOE_INDX
 INDEX              SOE_LOB
 LOBINDEX           SOE_LOB
 LOBSEGMENT         SOE_LOB
 TABLE              SOE_LOB
 TABLE PARTITION    SOE_SLOW_PART
 

The statement below can be used to determine whether Tablespace are self-contained, with referential integrity constraints taken into consideration (indicated by TRUE).

 BEGIN
 SYS.dbms_tts.transport_set_check
 ('SOE,SOE_FAST_PART,SOE_INDX,SOE_LOB,SOE_SLOW_PART', incl_constraints=>TRUE, full_check=>FALSE);
 END;
 /
 PL/SQL procedure successfully completed.
 

After invoking this PL/SQL package, you can see all violations by selecting from the TRANSPORT_SET_VIOLATIONS view. If the set of Tablespace is self-contained, this view is empty.

 SELECT * FROM SYS.transport_set_violations;
 no rows selected
 

Good no problem with self-contained

Restriction with Data Types

Check Tablespace set for columns with problematic data types:

Data pump can’t transport XMLTypes (i.e. expdp utility)

Original export can’t transport BINARY_FLOAT or BINARY_DOUBLE (i.e. export utility)

Manual conversion could be required for RAW, LONG RAW, BFILE, ANYTYPE, user-defined data types Lets verify if we have problem with data types.

 SELECT   tc.data_type, COUNT(*)
 FROM     dba_tables t, dba_tab_columns tc
 WHERE    t.owner = 'SOE'
 AND      tc.owner = t.owner
 AND      tc.table_name = t.table_name
 GROUP BY tc.data_type
 ORDER BY 1;
 DATA_TYPE                                  COUNT(*)
 ---------------------------------------- ----------
 BLOB                                              3
 CHAR                                              1
 DATE                                             32
 INTERVAL YEAR(2) TO MONTH                         1
 NUMBER                                           88
 NVARCHAR2                                         2
 ROWID                                             1
 TIMESTAMP(6) WITH LOCAL TIME ZONE                 1
 VARCHAR2                                        122
 

Good … no problem with Data Types.

Let’s check if any of the Tablespace have some Data Type problematic.

 SELECT xt.owner, at.table_name
 FROM   dba_xml_tables xt, all_all_tables at
 WHERE  at.owner = xt.owner
 AND    at.table_name = xt.table_name
 AND    at.tablespace_name IN ('SOE','SOE_FAST_PART','SOE_INDX','SOE_LOB','SOE_SLOW_PART')
 no rows selected
 

It’s good .. no data type problematic

Checking External Objects

Using Transportable Database or Transportable Tablespace its necessary check if Database have External tables, directory or BFILES.

This is mandatory because these objects will not migrate automatically.

To complete this step will use package  DBMS_TDB.CHECK_EXTERNAL if the database has external tables, directories, or BFILEs, return TRUE. Otherwise, return FALSE.

 SET SERVEROUTPUT ON
 DECLARE
 external BOOLEAN;
 BEGIN
 external := DBMS_TDB.CHECK_EXTERNAL;
 END;
 /
 The following external tables exist in the database:
 SOE.TBE_ACTLOG_PARTNER, SOE.TBE_ASSOCIATIONS_PARTNER, SOE.TBE_AUDITOCC_PARTNER,
 SOE.TBE_CLIENT_SCHEDULES_PARTNER, SOE.TBE_DB_PARTNER, SOE.TBE_DRIVES_PARTNER,
 SOE.TBE_DRMEDIA_PARTNER, SOE.TBE_EVENTS_PARTNER, SOE.TBE_LIBRARIES_PARTNER,
 SOE.TBE_LIBVOLUMES_PARTNER, SOE.TBE_LOG_PARTNER

 The following directories exist in the database:
 SYS.EXT_PARTNER_DIR, SYS.DIR_EXP_CONV, SYS.DATA_PUMP_DIR, SYS.ORACLE_OCM_CONFIG_DIR

 

We have objects, and will need migrate manually.

Start Migration

In this migration I will use the Data Pump (expdp and impdp).

Typically, I see the guys using the following steps to migrate.

  1. Export Schema
  2. Exports the Tablespace
  3. Converts Tablespace with Rman
  4. Move the files to the destination host.
  5. Creates users in the destination database and provides the necessary grants.
  6. Import tables attaching Tablespace in destination database
  7. Import other objects such as procedures, packages, etc…

But this migration I will use a different way.

  1. Export Schema
  2. Export External Tables
  3. Export Tablespace
  4. Convert Tablespace with Rman
  5. Move the files to destination host.
  6. Import Schema
  7. Import External Tables
  8. Import Tables attaching tablespace in destination database.

 

You can ask: Why you import the schema first? Why not attach the Tablespace with tables and after import schema?

Simple answer, it’s more easy to me recompile objects invalidated of than recreate all users manually.

Remember, to attach Tablespace in database user must be created.

I choose this way, because it saves time.

A Data Pump schema import will recreate the user and execute all of the associated security privileges (grants, user password history), whereas the old imp utility required the DBA to create the user ID before importing.

Then when you migrate only one user it’s easy recreate user and executes associated security privileges, but when you migrate entire database we can have a many users. In my last migration the database had more than 3,000 users with different passwords and privileges.  I know which I can edit the dump file of the export and recreate user with same password and grant, but it’s a hard work.

Using my strategy of migration when you import schema all objects will be invalidated, because no tables in schema and import will finish with many errors.

But don’t worry, it’s easily solved when attach Tablespace in database and recompiling all objects invalidated.

I don’t need worry anymore with map users and grant. The impdp do this automatically.

Exporting Source Database

To use Data pump will need a Logical Directory in database.

Them my stage area to dump is ‘/u01/app/oracle/flashrecovery_area/DBCONV/export’

 SQL> CREATE DIRECTORY DIR_EXP_CONV AS '/u01/app/oracle/flashrecovery_area/DBCONV/export';
 Directory created.
 SQL> GRANT READ,WRITE ON DIRECTORY DIR_EXP_CONV TO SYSTEM;
 Grant succeeded.
 SQL>
 

We need change default Tablespace to another Tablespace that which already exists in destination database.

This is necessary because import (impdp) will fail on creation of user if default Tablespace not exists.

In my case I choose USERS.

First I need identify and create command to configure correct default tablespace to user on destination database.


SELECT 'ALTER USER '||USERNAME||' DEFAULT TABLESPACE '||DEFAULT_TABLESPACE||';' CMD

FROM DBA_USERS

WHERE USERNAME IN ('SOE');

CMD

--------------------------------------------------

ALTER USER SOE DEFAULT TABLESPACE SOE;

Now I can change default tablespace to Tablespace USERS  before export.

 SQL> ALTER USER SOE DEFAULT TABLESPACE USERS;
 User altered.
 

Before export database or convert datafiles, we need put all Tablespace from user migrate in READ-ONLY, this will prevent DML in my schema during process migration.

This step is mandatory.

 SELECT  'ALTER TABLESPACE "'||TABLESPACE_NAME||'" READ ONLY;' as "CMD READ ONLY"
 FROM DBA_SEGMENTS
 WHERE OWNER='SOE'
 GROUP BY TABLESPACE_NAME
 ORDER BY 1
 
 SQL> SELECT  'ALTER TABLESPACE "'||TABLESPACE_NAME||'" READ ONLY;' as "CMD READ ONLY"
 FROM DBA_SEGMENTS
 WHERE OWNER='SOE'
 GROUP BY TABLESPACE_NAME
 ORDER BY 1  2    3    4    5
 6  /

 CMD READ ONLY
 ------------------------------------------------------------
 ALTER TABLESPACE "SOE" READ ONLY;
 ALTER TABLESPACE "SOE_FAST_PART" READ ONLY;
 ALTER TABLESPACE "SOE_INDX" READ ONLY;
 ALTER TABLESPACE "SOE_LOB" READ ONLY;
 ALTER TABLESPACE "SOE_SLOW_PART" READ ONLY;

 SQL> ALTER TABLESPACE "SOE" READ ONLY;
 Tablespace altered.

 SQL>ALTER TABLESPACE "SOE_FAST_PART" READ ONLY;
 Tablespace altered.

 SQL>ALTER TABLESPACE "SOE_INDX" READ ONLY;
 Tablespace altered.

 SQL>ALTER TABLESPACE "SOE_LOB" READ ONLY;
 Tablespace altered.

 SQL>ALTER TABLESPACE "SOE_SLOW_PART" READ ONLY;
 Tablespace altered.

 

Let’s identify how many objects are in my schema, this will help us at end of migration. We need be sure if all object were migrated.

 SELECT COUNT(OBJECT_NAME)
 FROM DBA_OBJECTS
 WHERE OWNER='SOE';

 COUNT(OBJECT_NAME)
 ------------------
 246

 SELECT COUNT(OBJECT_TYPE),OBJECT_TYPE
 FROM DBA_OBJECTS
 WHERE OWNER='SOE'
 GROUP BY OBJECT_TYPE
 ORDER BY OBJECT_TYPE;

 COUNT(OBJECT_TYPE) OBJECT_TYPE
 ------------------ -------------------
 4 FUNCTION
 30 INDEX
 3 JAVA CLASS
 1 JAVA SOURCE
 3 LOB
 1 PACKAGE
 1 PACKAGE BODY
 2 PROCEDURE
 3 SEQUENCE
 34 TABLE
 160 TABLE PARTITION
 1 TRIGGER
 1 TYPE
 2 VIEW

 14 rows selected.

 

The first export I will export entire schema with users, grants and objects except tables and indexes.

Excluding/Including an object, will also exclude/include it’s dependent objects, so constraints from tables not be export at this time.

 [oracle@libano ~]$ export NLS_DATE_FORMAT="DD-MM-YYYY HH24:MI:SS"
 [oracle@libano ~]$ expdp DIRECTORY=DIR_EXP_CONV DUMPFILE=exp_schema_soe.dmp SCHEMAS=SOE EXCLUDE=TABLE,INDEXES

 Export: Release 10.2.0.5.0 - 64bit Production on Monday, 17 January, 2011 17:33:57

 Copyright (c) 2003, 2007, Oracle.  All rights reserved.

 Username: system
 Password:

 Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options
 Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** DIRECTORY=DIR_EXP_CONV DUMPFILE=exp_schema_soe.dmp SCHEMAS=SOE EXCLUDE=TABLE,INDEXES
 Estimate in progress using BLOCKS method...
 Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
 Total estimation using BLOCKS method: 0 KB
 Processing object type SCHEMA_EXPORT/USER
 Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
 Processing object type SCHEMA_EXPORT/ROLE_GRANT
 Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
 Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
 Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
 Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
 Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
 Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
 Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
 Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
 Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
 Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
 Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
 Processing object type SCHEMA_EXPORT/VIEW/VIEW
 Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
 Processing object type SCHEMA_EXPORT/JAVA_SOURCE/JAVA_SOURCE
 Processing object type SCHEMA_EXPORT/JAVA_CLASS/JAVA_CLASS
 Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
 ******************************************************************************
 Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
 /u01/app/oracle/flashrecovery_area/DBCONV/export/exp_schema_soe.dmp
 Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 17:34:27

 

As exclude tables from export schema, I will need export External Tables separately.

Identifying External Tables ….

 SELECT OWNER||'.'||TABLE_NAME||',' CMD
 FROM DBA_EXTERNAL_TABLES
 WHERE OWNER ='SOE'
 
 CMD
 --------------------------------------------------------------
 SOE.TBE_ACTLOG_PARTNER,
 SOE.TBE_ASSOCIATIONS_PARTNER,
 SOE.TBE_AUDITOCC_PARTNER,
 SOE.TBE_CLIENT_SCHEDULES_PARTNER,
 SOE.TBE_DB_PARTNER,
 SOE.TBE_DRIVES_PARTNER,
 SOE.TBE_DRMEDIA_PARTNER,
 SOE.TBE_EVENTS_PARTNER,
 SOE.TBE_LIBRARIES_PARTNER,
 SOE.TBE_LIBVOLUMES_PARTNER,
 SOE.TBE_LOG_PARTNER,
 SOE.TBE_MEDIA_PARTNER,
 SOE.TBE_OCCUPANCY_PARTNER,
 SOE.TBE_PATHS_PARTNER,
 SOE.TBE_QTD_SCRATCH_PARTNER,
 SOE.TBE_STGPOOLS_PARTNER,
 SOE.TBE_SUMMARY_PARTNER,
 SOE.TBE_VOLHISTORY_PARTNER,
 SOE.TBE_VOLUMES_PARTNER

 19 rows selected.

 

And exporting ….

 [oracle@libano ~]$ cat exp_exttalbes.par
 DUMPFILE=exp_exttables_soe.dmp
 LOGFILE=exp_exttables_soe.log
 TABLES=SOE.TBE_ACTLOG_PARTNER,SOE.TBE_ASSOCIATIONS_PARTNER,SOE.TBE_AUDITOCC_PARTNER,
 SOE.TBE_CLIENT_SCHEDULES_PARTNER,SOE.TBE_DB_PARTNER,SOE.TBE_DRIVES_PARTNER,
 SOE.TBE_DRMEDIA_PARTNER,SOE.TBE_EVENTS_PARTNER,SOE.TBE_LIBRARIES_PARTNER,
 SOE.TBE_LIBVOLUMES_PARTNER,SOE.TBE_LOG_PARTNER,SOE.TBE_MEDIA_PARTNER,
 SOE.TBE_OCCUPANCY_PARTNER,SOE.TBE_PATHS_PARTNER,SOE.TBE_QTD_SCRATCH_PARTNER,
 SOE.TBE_STGPOOLS_PARTNER,SOE.TBE_SUMMARY_PARTNER,SOE.TBE_VOLHISTORY_PARTNER,
 SOE.TBE_VOLUMES_PARTNER

 [oracle@libano ~]$ expdp parfile=exp_exttalbes.par

 Export: Release 10.2.0.5.0 - 64bit Production on Saturday, 22 January, 2011 18:07:22

 Copyright (c) 2003, 2007, Oracle.  All rights reserved.

 Username: system
 Password:

 Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options
 Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** parfile=exp_exttalbes.par
 Estimate in progress using BLOCKS method...
 Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
 Total estimation using BLOCKS method: 0 KB
 Processing object type TABLE_EXPORT/TABLE/TABLE
 Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
 ******************************************************************************
 Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
 /u01/app/oracle/flashrecovery_area/DBCONV/export/exp_exttables_soe.dmp
 Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 18:07:30

 

All Oracle Directories must be created manually ….

The script above will generate command to recreate Oracle directories, except defaults Oracle Directory.

 set linesize 100
 SELECT 'CREATE DIRECTORY '|| DIRECTORY_NAME||' AS '''||DIRECTORY_PATH||''';' CMD
 FROM DBA_DIRECTORIES
 WHERE DIRECTORY_NAME NOT IN ('ORACLE_OCM_CONFIG_DIR','DATA_PUMP_DIR')
 /

 CMD
 ----------------------------------------------------------------------------------------------------
 CREATE DIRECTORY DIR_TXT_FILES AS '/u01/app/external_table/partner';
 CREATE DIRECTORY EXT_PARTNER_DIR AS '/u01/app/external_table/partner';
 CREATE DIRECTORY DIR_EXP_CONV AS '/u01/app/oracle/flashrecovery_area/DBCONV/export';

 

No sys-object privileges are granted during the import (using imp or impdp), so we must manually grant access.

This script will create the commands needed to grant access due.

 SELECT grantor, grantee, table_schema, table_name, privilege
 FROM all_tab_privs
 WHERE grantee = 'SOE'

 GRANTOR              GRANTEE              TABLE_SCHEMA         TABLE_NAME           PRIVILEGE
 -------------------- -------------------- -------------------- -------------------- ----------
 SYS                  SOE                  SYS                  DBMS_LOCK            EXECUTE
 SYS                  SOE                  SYS                  EXT_PARTNER_DIR      WRITE
 SYS                  SOE                  SYS                  EXT_PARTNER_DIR      READ
 SYS                  SOE                  SYS                  EXT_PARTNER_DIR      EXECUTE
 SYS                  SOE                  SYS                  DIR_TXT_FILES        WRITE
 SYS                  SOE                  SYS                  DIR_TXT_FILES        READ
 SYS                  SOE                  SYS                  DIR_TXT_FILES        EXECUTE

 

This script will generate command to Oracle Directory

 SELECT 'GRANT '
 ||tp.privilege
 ||' ON DIRECTORY '
 || tp.table_name
 ||' TO '
 ||tp.grantee
 ||';' CMD
 FROM dba_tab_privs tp,
 dba_objects DO
 WHERE do.object_name = tp.table_name
 AND object_type      ='DIRECTORY'
 AND grantee          = 'SOE'
 /
 
 CMD
 -----------------------------------------------------------------------
 GRANT EXECUTE ON DIRECTORY EXT_PARTNER_DIR TO SOE;
 GRANT READ ON DIRECTORY EXT_PARTNER_DIR TO SOE;
 GRANT WRITE ON DIRECTORY EXT_PARTNER_DIR TO SOE;
 GRANT EXECUTE ON DIRECTORY DIR_TXT_FILES TO SOE;
 GRANT READ ON DIRECTORY DIR_TXT_FILES TO SOE;
 GRANT WRITE ON DIRECTORY DIR_TXT_FILES TO SOE;

 

And this script will generate command another sys-objects.

 SELECT 'GRANT '
 ||tp.privilege
 ||' ON '
 ||tp.owner
 ||'.'
 ||tp.table_name
 ||' TO '
 ||tp.grantee
 ||';' CMD
 FROM DBA_TAB_PRIVS tp,
 dba_objects DO
 WHERE do.object_name = tp.table_name
 AND object_type     <>'DIRECTORY'
 AND grantee          = 'SOE'
 GROUP BY tp.privilege,
 tp.owner,
 tp.table_name,
 tp.grantee
 /

 
 CMD
 -----------------------------------------------------------
 GRANT EXECUTE ON CTXSYS.CTX_DDL TO SOE;
 GRANT EXECUTE ON SYS.DBMS_LOCK TO SOE;
 

Let’s export Objects from Transportable Tablespace.

 [oracle@libano ~]$ expdp DIRECTORY=DIR_EXP_CONV DUMPFILE=exp_transport_soe.dmp TRANSPORT_FULL_CHECK=Y TRANSPORT_TABLESPACES=SOE,SOE_FAST_PART,SOE_INDX,SOE_LOB,SOE_SLOW_PART

 Export: Release 10.2.0.5.0 - 64bit Production on Monday, 17 January, 2011 17:39:48

 Copyright (c) 2003, 2007, Oracle.  All rights reserved.

 Username: system
 Password:

 Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options
 Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** DIRECTORY=DIR_EXP_CONV DUMPFILE=exp_transport_soe.dmp TRANSPORT_FULL_CHECK=Y TRANSPORT_TABLESPACES=SOE,SOE_FAST_PART,SOE_INDX,SOE_LOB,SOE_SLOW_PART
 Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
 Processing object type TRANSPORTABLE_EXPORT/TABLE
 Processing object type TRANSPORTABLE_EXPORT/INDEX
 Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
 Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
 Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
 Processing object type TRANSPORTABLE_EXPORT/TRIGGER
 Processing object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
 Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
 Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
 Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/TABLE
 Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/SECONDARY_TABLE/INDEX
 Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/INDEX
 Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
 Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
 ******************************************************************************
 Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
 /u01/app/oracle/flashrecovery_area/DBCONV/export/exp_transport_soe.dmp
 Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 17:40:36

 

Let’s convert datafiles to this stage area ‘/u01/app/oracle/flashrecovery_area/DBCONV/stage/’ with RMAN.

 CONVERT TABLESPACE SOE,SOE_FAST_PART,SOE_INDX,SOE_LOB,SOE_SLOW_PART
 TO PLATFORM = "AIX-Based Systems (64-bit)"
 DB_FILE_NAME_CONVERT ('/u01/app/oracle/oradata/dbconv/', '/u01/app/oracle/flashrecovery_area/DBCONV/stage/');
 PARALLELISM = 2

RMAN> CONVERT TABLESPACE SOE,SOE_FAST_PART,SOE_INDX,SOE_LOB,SOE_SLOW_PART
 TO PLATFORM = "AIX-Based Systems (64-bit)"
 DB_FILE_NAME_CONVERT ('/u01/app/oracle/oradata/dbconv/', '/u01/app/oracle/flashrecovery_area/DBCONV/stage/');
 PARALLELISM = 22> 3>
 Starting backup at 17-01-2011 17:45:50
 using target database control file instead of recovery catalog
 allocated channel: ORA_DISK_1
 channel ORA_DISK_1: sid=137 devtype=DISK
 channel ORA_DISK_1: starting datafile conversion
 input datafile fno=00005 name=/u01/app/oracle/oradata/dbconv/soe.dbf
 converted datafile=/u01/app/oracle/flashrecovery_area/DBCONV/stage/soe.dbf
 channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:02:06
 channel ORA_DISK_1: starting datafile conversion
 input datafile fno=00006 name=/u01/app/oracle/oradata/dbconv/soe_indx01.dbf
 converted datafile=/u01/app/oracle/flashrecovery_area/DBCONV/stage/soe_indx01.dbf
 channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:01:15
 channel ORA_DISK_1: starting datafile conversion
 input datafile fno=00009 name=/u01/app/oracle/oradata/dbconv/soe_lob01.dbf
 converted datafile=/u01/app/oracle/flashrecovery_area/DBCONV/stage/soe_lob01.dbf
 channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:15
 channel ORA_DISK_1: starting datafile conversion
 input datafile fno=00007 name=/u01/app/oracle/oradata/dbconv/soe_fast_part01.dbf
 converted datafile=/u01/app/oracle/flashrecovery_area/DBCONV/stage/soe_fast_part01.dbf
 channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:15
 channel ORA_DISK_1: starting datafile conversion
 input datafile fno=00008 name=/u01/app/oracle/oradata/dbconv/soe_slow_part01.dbf
 converted datafile=/u01/app/oracle/flashrecovery_area/DBCONV/stage/soe_slow_part01.dbf
 channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
 Finished backup at 17-01-2011 17:49:50

 RMAN>
 Recovery Manager complete.

 
Moving Files

Moving files to destination host…

 [oracle@libano DBCONV]$ scp -r export stage oracle@butao:/u01/app/db_convert
 oracle@butao's password:
 exp_schema_soe.dmp                                            100%  208KB 208.0KB/s   00:00
 export.log                                                    100% 1819     1.8KB/s   00:00
 exp_transport_soe.dmp                                         100%  832KB 832.0KB/s   00:00
 soe_indx01.dbf                                                100% 2249MB  21.4MB/s   01:45
 soe_lob01.dbf                                                 100%  364MB  22.8MB/s   00:16
 soe_slow_part01.dbf                                           100%  161MB  20.1MB/s   00:08
 soe.dbf                                                       100% 3341MB  21.7MB/s   02:34
 soe_fast_part01.dbf                                           100%  181MB  22.6MB/s   00:08
 exp_exttables_soe.dmp                                       100%  112KB 112.0KB/s   00:00
 exp_exttables_soe.log                                        100%  938     0.9KB/s   00:00
 
Importing on Destination Database

Checking if user exists on destination database:

Note: User must be not exists.

 SELECT USERNAME
 FROM DBA_USERS
 WHERE USERNAME ='SOE';

 no rows selected
 

Creating Oracle Directory to be used with impdp …


 SQL> CREATE DIRECTORY DIR_IMP_CONV AS '/u01/app/db_convert/export';

 Directory created.

 SQL> GRANT READ,WRITE ON DIRECTORY DIR_IMP_CONV TO SYSTEM;

 Grant succeeded.

 SQL>
 

Creating all Oracle Directories from source database in destination database…

 SQL> CREATE DIRECTORY DIR_TXT_FILES AS '/u01/app/external_table/partner';
 Directory created.

 SQL>CREATE DIRECTORY EXT_PARTNER_DIR AS '/u01/app/external_table/partner';
 Directory created.

 SQL>CREATE DIRECTORY DIR_EXP_CONV AS '/u01/app/oracle/flashrecovery_area/DBCONV/export';
 Directory created.

 SQL>

 

Importing Schema SOE in destination database…

 oracle@butao:/u01/app/db_convert/export> impdp DIRECTORY=DIR_IMP_CONV DUMPFILE=exp_schema_soe.dmp LOGFILE=imp_schema_soe.log

 Import: Release 10.2.0.5.0 - 64bit Production on Saturday, 22 January, 2011 14:38:32

 Copyright (c) 2003, 2007, Oracle.  All rights reserved.

 Username: system
 Password:

 Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options
 Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
 Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** DIRECTORY=DIR_IMP_CONV DUMPFILE=exp_schema_soe.dmp LOGFILE=imp_schema_soe.log
 Processing object type SCHEMA_EXPORT/USER
 Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
 Processing object type SCHEMA_EXPORT/ROLE_GRANT
 Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
 Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
 ORA-39083: Object type TABLESPACE_QUOTA failed to create with error:
 ORA-00959: tablespace 'SOE' does not exist
 Failing sql is:
 DECLARE   TEMP_COUNT NUMBER;   SQLSTR VARCHAR2(200); BEGIN   SQLSTR := 'ALTER USER "SOE" QUOTA UNLIMITED ON "SOE"';  EXECUTE IMMEDIATE SQLSTR;EXCEPTION   WHEN OTHERS THEN    IF SQLCODE = -30041 THEN       SQLSTR := 'SELECT COUNT(*) FROM USER_TABLESPACES               WHERE TABLESPACE_NAME = ''SOE'' AND CONTENTS = ''TEMPORARY''';      EXECUTE IMMEDIATE SQLSTR INTO TEMP_COUNT;
 Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
 Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
 Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
 Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
 Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
 Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
 Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
 ORA-39082: Object type ALTER_PACKAGE_SPEC:"SOE"."ORDERENTRY" created with compilation warnings
 Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
 Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
 ORA-39082: Object type ALTER_PROCEDURE:"SOE"."LOAD_BLOB_FROM_FILE" created with compilation warnings
 Processing object type SCHEMA_EXPORT/VIEW/VIEW
 ORA-39082: Object type VIEW:"SOE"."PRODUCTS" created with compilation warnings
 ORA-39082: Object type VIEW:"SOE"."PRODUCT_PRICES" created with compilation warnings
 Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
 ORA-39082: Object type PACKAGE_BODY:"SOE"."ORDERENTRY" created with compilation warnings
 Processing object type SCHEMA_EXPORT/JAVA_SOURCE/JAVA_SOURCE
 Processing object type SCHEMA_EXPORT/JAVA_CLASS/JAVA_CLASS
 Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 6 error(s) at 14:38:44

 

Some errors be raised,  “Object type TABLESPACE_QUOTA failed to create with error” this error is because Tablespace SOE  don’t yet exists.

It’s completely normal.

Granting  privileges on Oracle Directories to user SOE.

 SQL> GRANT EXECUTE ON DIRECTORY EXT_PARTNER_DIR TO SOE;
 Grant succeeded.

 SQL> GRANT READ ON DIRECTORY EXT_PARTNER_DIR TO SOE;
 Grant succeeded.

 SQL> GRANT WRITE ON DIRECTORY EXT_PARTNER_DIR TO SOE;
 Grant succeeded.

 SQL>GRANT EXECUTE ON DIRECTORY DIR_TXT_FILES TO SOE;
 Grant succeeded.

 SQL> GRANT READ ON DIRECTORY DIR_TXT_FILES TO SOE;
 Grant succeeded.

 SQL>GRANT WRITE ON DIRECTORY DIR_TXT_FILES TO SOE;
 Grant succeeded.

 

Importing External tables…

 oracle@butao:/u01/app/db_convert/export> impdp DIRECTORY=DIR_IMP_CONV DUMPFILE=exp_exttables_soe.dmp LOGFILE=imp_exttable_soe.log

 Import: Release 10.2.0.5.0 - 64bit Production on Saturday, 22 January, 2011 18:57:02

 Copyright (c) 2003, 2007, Oracle.  All rights reserved.

 Username: system
 Password:

 Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options
 Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
 Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** DIRECTORY=DIR_IMP_CONV DUMPFILE=exp_exttables_soe.dmp LOGFILE=imp_exttable_soe.log
 Processing object type TABLE_EXPORT/TABLE/TABLE
 Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 18:57:14

 

Attaching Tablespaces on destination database and importing tables…

 imp_soe.par

 DIRECTORY=DIR_IMP_CONV
 DUMPFILE=exp_transport_soe.dmp
 LOGFILE=imp_transport_soe.log
 TRANSPORT_DATAFILES='/u01/app/db_convert/stage/soe.dbf', '/u01/app/db_convert/stage/soe_fast_part01.dbf', '/u01/app/db_convert/stage/soe_indx01.dbf', '/u01/app/db_convert/stage/soe_lob01.dbf', '/u01/app/db_convert/stage/soe_slow_part01.dbf'

 oracle@butao:/u01/app/db_convert/stage> impdp parfile=imp_soe.par

 Import: Release 10.2.0.5.0 - 64bit Production on Saturday, 22 January, 2011 14:52:56

 Copyright (c) 2003, 2007, Oracle.  All rights reserved.

 Username: system
 Password:

 Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options
 Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
 Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/******** parfile=imp_soe.par
 Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
 Processing object type TRANSPORTABLE_EXPORT/TABLE
 Processing object type TRANSPORTABLE_EXPORT/INDEX
 Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
 Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
 Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
 Processing object type TRANSPORTABLE_EXPORT/TRIGGER
 Processing object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
 Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
 Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
 Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/TABLE
 Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/SECONDARY_TABLE/INDEX
 Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/INDEX
 Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
 Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 15:01:43

 

This step can take long time because Oracle always will Analyzes the Index and Tables.

If you can’t this happens, you can use note below…

EXCLUDE=STATISTICS Or EXCLUDE=INDEX_STATISTICS During Datapump Import Still Analyzes The Indexes [ID 793585.1]

Checking Tablespace after import and change to READ WRITE…

 select TABLESPACE_NAME, STATUS
 FROM DBA_TABLESPACES
 WEHRE TABLESPACE_NAME LIKE '%SOE%';

 TABLESPACE_NAME                STATUS
 ------------------------------ ---------
 SOE_SLOW_PART                  READ ONLY
 SOE_LOB                        READ ONLY
 SOE_INDX                       READ ONLY
 SOE_FAST_PART                  READ ONLY
 SOE                            READ ONLY

SELECT 'ALTER TABLESPACE '||TABLESPACE_NAME||' READ WRITE;' CMD
 FROM DBA_TABLESPACES
 WHERE TABLESPACE_NAME LIKE '%SOE%' AND STATUS='READ ONLY';

 CMD
 -----------------------------------------------------------
 ALTER TABLESPACE SOE_SLOW_PART READ WRITE;
 ALTER TABLESPACE SOE_LOB READ WRITE;
 ALTER TABLESPACE SOE_INDX READ WRITE;
 ALTER TABLESPACE SOE_FAST_PART READ WRITE;
 ALTER TABLESPACE SOE READ WRITE;

 SQL> ALTER TABLESPACE SOE_SLOW_PART READ WRITE;
 Tablespace altered.

 SQL>ALTER TABLESPACE SOE_LOB READ WRITE;
 Tablespace altered.

 SQL>ALTER TABLESPACE SOE_INDX READ WRITE;
 Tablespace altered.

 SQL>ALTER TABLESPACE SOE_FAST_PART READ WRITE;
 Tablespace altered.

 SQL>ALTER TABLESPACE SOE READ WRITE;
 Tablespace altered.

 

Compiling Objects Invalidated ….

 set pagesize 0  linesize 120  heading on feedback off
 SELECT 'ALTER '||
 decode(object_type, 'PACKAGE BODY', 'PACKAGE',object_type) || ' "' ||
 owner||'"."'||
 object_name||'" compile' ||
 decode(object_type, 'PACKAGE BODY', ' BODY') || ';'
 FROM dba_objects
 WHERE status = 'INVALID'
 and object_type in ( 'PACKAGE',
 'PACKAGE BODY',
 'PROCEDURE',
 'VIEW',
 'TRIGGER','SYNONYM','FUNCTION' );
 
 ALTER PACKAGE "SOE"."ORDERENTRY" compile;
 ALTER PROCEDURE "SOE"."LOAD_BLOB_FROM_FILE" compile;
 ALTER VIEW "SOE"."PRODUCTS" compile;
 ALTER VIEW "SOE"."PRODUCT_PRICES" compile;
 ALTER PACKAGE "SOE"."ORDERENTRY" compile BODY;
 SQL> ALTER PACKAGE "SOE"."ORDERENTRY" compile;
 ALTER PROCEDURE "SOE"."LOAD_BLOB_FROM_FILE" compile;
 ALTER VIEW "SOE"."PRODUCTS" compile;
 ALTER VIEW "SOE"."PRODUCT_PRICES" compile;
 ALTER PACKAGE "SOE"."ORDERENTRY" compile BODY;
 

Checking again ….

 SQL> SELECT 'ALTER '||
 2               decode(object_type, 'PACKAGE BODY', 'PACKAGE',object_type) || ' "' ||
 3               owner||'"."'||
 4               object_name||'" compile' ||
 5               decode(object_type, 'PACKAGE BODY', ' BODY') || ';'
 6           FROM dba_objects
 7           WHERE status = 'INVALID'
 8            and object_type in ( 'PACKAGE',
 9                                 'PACKAGE BODY',
 10                                 'PROCEDURE',
 11                                 'VIEW',
 12                                 'TRIGGER','SYNONYM','FUNCTION' );

 no rows selected

 

Good … all objects validated …

Verifying if all Objects still on destination Database…


 SELECT COUNT(OBJECT_NAME)
 FROM DBA_OBJECTS
 WHERE OWNER='SOE';

 COUNT(OBJECT_NAME)
 ------------------
 246

 COUNT(OBJECT_TYPE) OBJECT_TYPE
 ------------------ -------------------
 4 FUNCTION
 30 INDEX
 3 JAVA CLASS
 1 JAVA SOURCE
 3 LOB
 1 PACKAGE
 1 PACKAGE BODY
 2 PROCEDURE
 3 SEQUENCE
 34 TABLE
 160 TABLE PARTITION
 1 TRIGGER
 1 TYPE
 2 VIEW
 14 rows selected.

 

Good .. all objects were migrated.

You can start your application and pray no error being raised by Oracle.
If everything is ok … you can celebrate.


Oracle Database Cross Platform Migration to AIX

Oracle database operations are similar on all operating systems. However deployment strategies can vary on different operating systems. Migration of an existing Oracle database to a new architecture can prove to be overwhelming initially due to many factors like

• New operating system
• Operating system preparedness
• Installation nuances
• Oracle patches
• Database migration

This document is targeted towards users migrating from Solaris and HP-UX operating systems to AIX and covers topics mentioned above for Oracle database version 10gR2 and higher. It also provides a step by step migration
example using transportable databases, talks about tuning a migration and avoiding and overcoming migration pitfalls.

The migration steps documented in this paper have been leveraged at numerous customer sites successfully and lessons learnt during these migrations are also included.

See link below

Oracle_Generic_Migration_Version_1

Enjoy


Cloning an Oracle Database to the Same Server Using FlashCopy and VolumeCopy on DS3400, DS4000, and DS5000

Maximizing hardware return on investment is an important business concern. To conserve hardware resources, many IT environments run multiple databases on a single server. With this proliferation of databases also comes the proliferation of database clones. Database clones are very useful for testing, developing, or keeping a point-intime image for evaluation purposes. However, too many database clones take up a lot of server space.

When there is a shortage of server hardware, and when the source server has enough resources to run another database, the clone can run on the same server as the source database. For example, if needed, you could clone a development database back onto the development server.

Cloning a database onto the same server is more complicated than cloning the database onto a different server. To isolate the clone from its source database on the same server, you have to know which files to edit and how to edit them. Otherwise, any mistake that you might make when you create the clone might overwrite the source database and corrupt it. Not surprisingly, Oracle Database administrators are often reluctant to try to create clones on the same server. Even though doubling up would conserve hardware resources, sometimes the risk is not worth taking.

This document explains how to isolate a cloned database from its source. This document explains how to set up the cloned database using different storage locations from the source database. Initially, all of the pointers in the cloned database point to the original locations for the source database. You have to change the locations before you can use the cloned database. This document describes how to edit all of the references to the file systems so that it is safe to use the cloned database.

This document also describes a solution that simplifies cloning. Specifically, this document focuses on expediting the important task of cloning an Oracle Database to the same server. The solution combines Oracle Database products with IBM® storage subsystems and the premium features, FlashCopy ® and VolumeCopy, which come with both DS Storage Manager and IBM DS Storage Manager 10. This method of cloning a database requires less effort than using an Export/Import function or backing up and restoring the database using Oracle RMAN.

 

The intended reader for this document is an Oracle Database administrator with experience in the following areas:

• Oracle Database and its related components

• Cloning an Oracle Database

• Storage, including an understanding

See link below

Cloning Oracle DB to same server using DS5000 DS4000 DS3400

Enjoy


RMAN scripts for Oracle Database Cross Platform Migration

This is a technical brief which demonstrates how to concurrently migrate multiple oracle datafiles in a cross platform migration project. A SQL/Shell script generates the RMAN commands for migration.

Download Script and PDF below

rman_scripts_4_xplat_mig_rev_B.zip

The pdf document explain how to exploit this feature.

rman_scripts_4_xplat_mig_rev_B


#!/bin/kshDEST=/data1/oracle   ## target destination of the datafileSTAGE=/stage/oracle  ## staging area on target where converted file residesSEP="'"              ## single quote      SCOL=";"             ## semi-colonSLASH="/"            ## slashBG="' &"
if [ $USER = 'root' ]; then   echo "Cannot run as " $USER   echo "EXITING ..."    exitfi
if [ $USER = 'root' ]; then   echo "Cannot run as " $USER   echo "EXITING ..."    exitfi
# Check for Oracle environment
# End of Check for Oracle Environment

# Find out about the source platform  SOURCEPLATFORM=`sqlplus -s '/as sysdba' <<!   set heading off   select ''''||platform_name||'''' from v\\\$database; ! `## End of Source Platform checke
# `sqlplus -s '/ as sysdba' >db_files.lst <<!#   set heading off#   set feedback off#   select substr(file_name,(instr(file_name,'/',-1,1)+1),length(file_name))#   from dba_data_files;#   !#  `
sqlplus -s '/ as sysdba' <<ENDSQLspool db_files.lstset heading offset feedback off   select substr(file_name,(instr(file_name,'/',-1,1)+1),length(file_name)) from dba_data_files;spool offexit;ENDSQL
## get rid of the blank lines in the lst file with sed command     cat db_files.lst | sed '/^$/d' > db_files.lst## done
## Generate the rman  conversion script for individual datafiles       echo RUN > ${ORACLE_SID}_CONVERT.RMAN       echo ' {' >> ${ORACLE_SID}_CONVERT.RMAN      for file in `cat db_files.lst`     do  	echo RUN >> ${file}.rman  	echo ' {' >> ${file}.rman  	echo ' CONVERT DATAFILE ' ${SEP}$DEST${SLASH}${file}${SEP} >> ${file}.rman  	echo ' FROM PLATFORM ' $SOURCEPLATFORM >> ${file}.rman  	echo ' FORMAT '${SEP}${STAGE}${SLASH}${file}${SEP}${SCOL} >> ${file}.rman  	echo ' }' >> ${file}.rman        echo 'rman target=/ @'${file}.rman log=${SEP}${file}.log${BG} >> MASTER_${ORACLE_SID}.sh
 echo ' CONVERT DATAFILE ' ${SEP}$DEST${SLASH}${file}${SEP} >> ${ORACLE_SID}_CONVERT.RMAN  	echo ' FROM PLATFORM ' $SOURCEPLATFORM >> ${ORACLE_SID}_CONVERT.RMAN  	echo ' FORMAT '${SEP}${STAGE}${SLASH}${file}${SEP}${SCOL} >> ${ORACLE_SID}_CONVERT.RMAN        echo '                                                    ' >> ${ORACLE_SID}_CONVERT.RMAN      done  	echo ' }' >> ${ORACLE_SID}_CONVERT.RMAN