Forgot your password?    
+ Reply to Thread
Results 1 to 2 of 2

Thread: Migrate / Convert Oracle Database from Non-ASM to ASM Using RMAN

  1. #1
    Expert Oracle Administrator
    Join Date
    Oct 2011
    Location
    New Delhi, India
    Posts
    427

    Migrate / Convert Oracle Database from Non-ASM to ASM Using RMAN

    The following method shows how a Non-ASM database can be migrated to ASM using RMAN:

    Modify the parameter file of the target database as follows:
    - ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
    - Set the DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n parameters to the relevant ASM disk groups.
    - Remove the CONTROL_FILES parameter from the spfile so the control files will be moved to the DB_CREATE_* destination and the spfile gets updated automatically.

    ALTER SYSTEM SET db_create_file_dest='+DATA' SCOPE=SPFILE;
    ALTER SYSTEM SET db_recovery_file_dest='+FLASH' SCOPE=SPFILE;
    ALTER SYSTEM SET control_files='+DATA' SCOPE=SPFILE;

    - If you are using a pfile these parameter must be set to the appropriate ASM files or aliases.

    Shutdown the database
    SQL> SHUTDOWN IMMEDIATE

    Start the database in nomount mode.

    Make sure the Environment is set properly

    RMAN> CONNECT TARGET /
    RMAN> STARTUP NOMOUNT

    Restore the controlfile into the new location from the old location.
    RMAN> RESTORE CONTROLFILE FROM 'old_control_file_name';

    Mount the database.
    RMAN> ALTER DATABASE MOUNT;

    Copy the database into the ASM disk group.
    RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA';

    Switch all datafile to the new ASM location.
    RMAN> SWITCH DATABASE TO COPY;

    Recover the database.
    RMAN> RECOVER DATABASE;

    Using SQL*Plus to migrate flashback logs, change tracking file and temp files:
    SQL> ALTER DATABASE FLASHBACK OFF;
    SQL> ALTER DATABASE FLASHBACK ON;
    SQL> ALTER DATABASE OPEN;

    Create New temporary tablespace in ASM disk group and Drop the old ones.
    SQL> CREATE TEMPORARY TABLESPACE temp1 TEMPFILE ‘+DATA’;
    SQL> DROP TABLESPACE ‘old_temporary_tablespace’ including contents and datafiles;

    SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

    Create new redo logs in ASM Diskgroup and delete the old ones.

  2. #2
    SQL> ALTER SYSTEM SET db_create_file_dest='+DATA' SCOPE=SPFILE;
    ALTER SYSTEM SET db_recovery_file_dest='+FLASH' SCOPE=SPFILE;
    ALTER SYSTEM SET control_files='+DATA'

    System altered.

    SQL>
    System altered.

    SQL>
    System altered.

    SQL> SHUTDOWN IMMEDIATE

    rac1-> rman

    Recovery Manager: Release 10.2.0.1.0 - Production on Sun Nov 27 17:33:16 2011

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

    RMAN> connect target /

    connected to target database (not started)

    RMAN> startup nomount;


    RMAN> RESTORE CONTROLFILE FROM '<original location>/control02.ctl';

    Starting restore at 27-NOV-11
    using target database control file instead of recovery catalog
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: sid=156 devtype=DISK

    channel ORA_DISK_1: copied control file copy
    output filename=+DATA/stagedb/controlfile/backup.267.768333209
    Finished restore at 27-NOV-11

    RMAN>

    RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA';


    Starting backup at 27-NOV-11
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: sid=156 devtype=DISK
    channel ORA_DISK_1: starting datafile copy
    input datafile fno=00001 name=/u01/app/oracle/oradata/stagedb/system01.dbf
    output filename=+DATA/stagedb/datafile/system.268.768333503 tag=TAG20111127T175818 recid=1 stamp=768333584
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:26
    channel ORA_DISK_1: starting datafile copy
    input datafile fno=00003 name=/u01/app/oracle/oradata/stagedb/sysaux01.dbf
    output filename=+DATA/stagedb/datafile/sysaux.269.768333589 tag=TAG20111127T175818 recid=2 stamp=768333626
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:46
    channel ORA_DISK_1: starting datafile copy
    input datafile fno=00002 name=/u01/app/oracle/oradata/stagedb/undotbs01.dbf
    output filename=+DATA/stagedb/datafile/undotbs1.270.768333635 tag=TAG20111127T175818 recid=3 stamp=768333641
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
    channel ORA_DISK_1: starting datafile copy
    input datafile fno=00004 name=/u01/app/oracle/oradata/stagedb/users01.dbf
    output filename=+DATA/stagedb/datafile/users.271.768333651 tag=TAG20111127T175818 recid=4 stamp=768333653
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08
    channel ORA_DISK_1: starting datafile copy
    copying current control file
    output filename=+DATA/stagedb/controlfile/backup.272.768333659 tag=TAG20111127T175818 recid=5 stamp=768333660
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
    channel ORA_DISK_1: starting full datafile backupset
    channel ORA_DISK_1: specifying datafile(s) in backupset
    including current SPFILE in backupset
    channel ORA_DISK_1: starting piece 1 at 27-NOV-11
    channel ORA_DISK_1: finished piece 1 at 27-NOV-11
    piece handle=+DATA/stagedb/backupset/2011_11_27/nnsnf0_tag20111127t175818_0.273.768333663 tag=TAG20111127T175818 comment=NONE
    channel ORA_DISK_1: backup set complete, elapsed time: 00:00:05
    Finished backup at 27-NOV-11

    RMAN>
    RMAN> SWITCH DATABASE TO COPY;

    datafile 1 switched to datafile copy "+DATA/stagedb/datafile/system.268.768333503"
    datafile 2 switched to datafile copy "+DATA/stagedb/datafile/undotbs1.270.768333635"
    datafile 3 switched to datafile copy "+DATA/stagedb/datafile/sysaux.269.768333589"
    datafile 4 switched to datafile copy "+DATA/stagedb/datafile/users.271.768333651"


    RMAN> RECOVER DATABASE;

    Starting recover at 27-NOV-11
    using channel ORA_DISK_1

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

    Finished recover at 27-NOV-11


    RMAN> exit

    Recovery Manager complete.

    rac1-> sqlplus

    SQL*Plus: Release 10.2.0.1.0 - Production on Sun Nov 27 18:04:35 2011

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

    Enter user-name: / as sysdba

    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
    With the Partitioning, OLAP and Data Mining options

    SQL> select flashback_on from v$database;

    FLASHBACK_ON
    ------------------
    NO

    SQL>
    SQL> alter database open;

    Database altered.
    SQL> select tablespace_name from dba_tablespaces;

    TABLESPACE_NAME
    ------------------------------
    SYSTEM
    UNDOTBS1
    SYSAUX
    TEMP
    USERS


    SQL> create temporary tablespace temp1 tempfile '+DATA';

    Tablespace created.

    SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP1;

    Database altered.

    SQL> DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

    Tablespace dropped.



    SQL> select * from v$logfile;

    GROUP# STATUS TYPE
    ---------- ------- -------
    MEMBER
    --------------------------------------------------------------------------------
    IS_
    ---
    3 ONLINE
    /u01/app/oracle/oradata/stagedb/redo03.log
    NO

    2 ONLINE
    /u01/app/oracle/oradata/stagedb/redo02.log
    NO

    1 ONLINE
    /u01/app/oracle/oradata/stagedb/redo01.log
    NO


    SQL> alter database add logfile group 4 '+DATA';

    Database altered.

    SQL> alter database add logfile group 5 '+DATA';

    Database altered.

    SQL> alter database add logfile group 6 '+DATA';

    alter system switch logfile;


    Drop the old redo log files - Need to make sure that the redo logs groups that are dropped
    are not CURRENT ot ACTIVE

    SQL> select group#, status from v$log;

    GROUP# STATUS
    ---------- ----------------
    1 INACTIVE
    2 INACTIVE
    3 INACTIVE
    4 CURRENT
    5 UNUSED
    6 UNUSED

    6 rows selected.

    SQL> alter database drop logfile group 1;

    Database altered.

    SQL> alter database drop logfile group 2;

    Database altered.

    SQL> alter database drop logfile group 3;

    Database altered.

    SQL> select group#, status from v$log;

    GROUP# STATUS
    ---------- ----------------
    4 CURRENT
    5 UNUSED
    6 UNUSED
    Last edited by Hemant; 11-29-2011 at 03:52 PM.

+ Reply to Thread

Similar Threads

  1. Convert Single Instance ( Non-RAC ) Database to RAC
    By Hemant in forum RAC Concepts and Administration
    Replies: 3
    Last Post: 08-05-2012, 08:46 PM
  2. Migrate from database file system to ASM
    By devesh_ocp in forum RAC Installation, ASM Install , ASM Administration
    Replies: 0
    Last Post: 04-26-2012, 10:55 AM
  3. Oracle 10g Using RMAN to Roll Forward a Physical Standby Database.
    By ajaychandi in forum DataGuard Setup and Management
    Replies: 0
    Last Post: 01-25-2012, 02:56 PM
  4. Oracle 11g RMAN Active Database Duplication Feature.
    By ajaychandi in forum Backup, Restoration & Recovery, RMAN, Flashback, Datapump, Export, Import
    Replies: 0
    Last Post: 12-25-2011, 01:12 AM
  5. Oracle 11g RMAN Active Database Duplication Feature.
    By ajaychandi in forum Backup, Restoration & Recovery, RMAN, Flashback, Datapump, Export, Import
    Replies: 0
    Last Post: 12-25-2011, 01:12 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

DBA Lounge (P) Ltd. deals in Oracle Technologies on Consulting, Resourcing, Corporate Training


Online and corporate training available on Oracle Database 11g, Oracle 11g Real Application Cluster (RAC), Oracle Applications 11i/R12, Oracle Fusion Middleware 11g, Oracle Identity Management-OIM, Oracle Internet Directory 11g-OID, Oracle Business Intelligence Enterprise Edition-OBIEE, Oracle Golden Gate, Oracle Access Management-OAM, Oracle Internet Directory-ODS, Oracle Identity Analytics Architecture-OIA Statistics