Oracledna

    Migrate Oracle database from Non-ASM to ASM

    • oracledna
    • ASM
    • April 12, 2023

    +++Environment+++

    DB Version: 19.15.0.0.0

    DB Type: Oracle Restart Standalone

    GI Restart Version: asmcmd version 19.18.0.0.0

    ASM Disk Group : TESTDBDATA

    +++In SPFILE, change the control file location from file system to ASM+++

    –Verify the Controlfile location

    SQL> select name from v$controlfile;

    NAME

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

    /u01/app/oracle/oradata/TESTDB/control01.ctl

    /u01/app/oracle/fast_recovery_area/TESTDB/control02.ctl

    –Shutdown the DB, startup DB in  mount state and change the controlfile location to ASM.

     

    SQL> shutdown immediate;

    Database closed.

    Database dismounted.

    ORACLE instance shut down.

    SQL>

     

    SQL> startup nomount

    ORACLE instance started.

    Total System Global Area 3187667920 bytes

    Fixed Size                  8901584 bytes

    Variable Size             637534208 bytes

    Database Buffers         2533359616 bytes

    Redo Buffers                7872512 bytes

    SQL>

     

    SQL> alter system set control_files=’+TESTDBDATA’ scope=spfile;

    System altered.

    SQL>

     

    SQL> alter system set control_files=’+TESTDBDATA’ scope=spfile;

    System altered.

     

    SQL> shutdown immediate;

    ORA-01507: database not mounted

    ORACLE instance shut down.

    SQL>

    +++Restore the control file from File System to ASM+++

    –startup DB in nomount state and restore control file using RMAN

     

    SQL> startup nomount

    ORACLE instance started.

     

    Total System Global Area 3187667920 bytes

    Fixed Size                  8901584 bytes

    Variable Size             637534208 bytes

    Database Buffers         2533359616 bytes

    Redo Buffers                7872512 bytes

    SQL>

     

    SQL> exit

    Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production

    Version 19.15.0.0.0

    [oracle@testdb ~]$

     

    [oracle@testdb ~]$ rman target /

    Recovery Manager: Release 19.0.0.0.0 – Production on Thu Mar 16 20:14:41 2023

    Version 19.15.0.0.0

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

    connected to target database: TESTDB (not mounted)

    RMAN>

     

    RMAN> restore controlfile from ‘/u01/app/oracle/oradata/TESTDB/control01.ctl’;

    Starting restore at 16-MAR-2023 20:16:51

    using target database control file instead of recovery catalog

    allocated channel: ORA_DISK_1

    channel ORA_DISK_1: SID=44 device type=DISK

    channel ORA_DISK_1: copied control file copy

    output file name=+TESTDBDATA/TESTDB/CONTROLFILE/current.275.1131653813

    Finished restore at 16-MAR-2023 20:16:53

    RMAN>

     

    RMAN> sql ‘alter database mount’;

    using target database control file instead of recovery catalog

    sql statement: alter database mount

    RMAN>

     

    [grid@testdb ~]$ su – oracle

    Password:

    Last login: Thu Mar 16 19:34:34 IST 2023 from 192.168.56.1 on pts/3

    [oracle@testdb ~]$

    [oracle@testdb ~]$

    [oracle@testdb ~]$ su – grid

    Password:

    Last login: Thu Mar 16 20:21:03 IST 2023 on pts/2

    [grid@testdb ~]$ asmcmd

    ASMCMD> find –type CONTROLFILE +TESTDBDATA *

    +TESTDBDATA/TESTDB/CONTROLFILE/current.275.1131653813

    ASMCMD>

    +++Copy the datafiles from ASM to filesystem using RMAN+++

    –Check the DB state, it should be in mount state

     

    SQL> select name,open_mode from v$database;

    NAME      OPEN_MODE

    ——— ——————–

    TESTDB    MOUNTED

    SQL>

     

    ===Query the datafiles locations before moving to asm.

     

    SQL> set linesize 300

    col name for a86

    select file#, name from v$datafile;

     

         FILE# NAME

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

             1 /u01/app/oracle/oradata/TESTDB/system01.dbf

             3 /u01/app/oracle/oradata/TESTDB/sysaux01.dbf

             4 /u01/app/oracle/oradata/TESTDB/undotbs01.dbf

             7 /u01/app/oracle/oradata/TESTDB/users01.dbf

    SQL>

     

    [oracle@testdb ~]$ rman target /

    Recovery Manager: Release 19.0.0.0.0 – Production on Thu Mar 16 20:30:19 2023

    Version 19.15.0.0.0

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

    connected to target database: TESTDB (DBID=2914977916, not open)

    RMAN>

     

    RMAN> backup as copy database format ‘+TESTDBDATA’;

    Starting backup at 16-MAR-2023 20:31:03

    using target database control file instead of recovery catalog

    allocated channel: ORA_DISK_1

    channel ORA_DISK_1: SID=46 device type=DISK

    channel ORA_DISK_1: starting datafile copy

    input datafile file number=00001 name=/u01/app/oracle/oradata/TESTDB/system01.dbf

    output file name=+TESTDBDATA/TESTDB/DATAFILE/system.262.1131654665 tag=TAG20230316T203104 RECID=1 STAMP=1131654668

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

    channel ORA_DISK_1: starting datafile copy

    input datafile file number=00003 name=/u01/app/oracle/oradata/TESTDB/sysaux01.dbf

    output file name=+TESTDBDATA/TESTDB/DATAFILE/sysaux.301.1131654671 tag=TAG20230316T203104 RECID=2 STAMP=1131654688

    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25

    channel ORA_DISK_1: starting datafile copy

    input datafile file number=00004 name=/u01/app/oracle/oradata/TESTDB/undotbs01.dbf

    output file name=+TESTDBDATA/TESTDB/DATAFILE/undotbs1.300.1131654697 tag=TAG20230316T203104 RECID=3 STAMP=1131654699

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

    channel ORA_DISK_1: starting datafile copy

    input datafile file number=00007 name=/u01/app/oracle/oradata/TESTDB/users01.dbf

    output file name=+TESTDBDATA/TESTDB/DATAFILE/users.299.1131654703 tag=TAG20230316T203104 RECID=4 STAMP=1131654704

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

    Finished backup at 16-MAR-2023 20:31:44

    Starting Control File and SPFILE Autobackup at 16-MAR-2023 20:31:44

    piece handle=/u01/app/oracle/fast_recovery_area/TESTDB/autobackup/

    2023_03_16/o1_mf_s_1131653176_l16cts7h_.bkp comment=NONE

    Finished Control File and SPFILE Autobackup at 16-MAR-2023 20:31:46

    RMAN>

     

    RMAN> switch database to copy;

    datafile 1 switched to datafile copy “+TESTDBDATA/TESTDB/DATAFILE/system.262.1131654665”

    datafile 3 switched to datafile copy “+TESTDBDATA/TESTDB/DATAFILE/sysaux.301.1131654671”

    datafile 4 switched to datafile copy “+TESTDBDATA/TESTDB/DATAFILE/undotbs1.300.1131654697”

    datafile 7 switched to datafile copy “+TESTDBDATA/TESTDB/DATAFILE/users.299.1131654703”

    RMAN>

     

    ===Check now whether datafiles moved to ASM or not

    set linesize 300

    col name for a86

    select file#, name from v$datafile;

     

         FILE# NAME

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

             1 +TESTDBDATA/TESTDB/DATAFILE/system.262.1131654665

             3 +TESTDBDATA/TESTDB/DATAFILE/sysaux.301.1131654671

             4 +TESTDBDATA/TESTDB/DATAFILE/undotbs1.300.1131654697

             7 +TESTDBDATA/TESTDB/DATAFILE/users.299.1131654703

    +++Switch the temp files to ASM and open the database from mount state+++

    ===Query the TEMP files locations and  then switch the temp file location to ASM.

    SQL> set linesize 300

    col name for a60

    select file#, name from v$tempfile;

     

         FILE# NAME

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

             1 /u01/app/oracle/oradata/TESTDB/temp01.dbf

     

    SQL>

     

    [oracle@testdb ~]$ rman target /

    Recovery Manager: Release 19.0.0.0.0 – Production on Thu Mar 16 20:38:49 2023

    Version 19.15.0.0.0

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

    connected to target database: TESTDB (DBID=2914977916, not open)

     

    RMAN> run

    2> {

    3> set newname for tempfile 1 to ‘+TESTDBDATA’;

    4> switch tempfile 1;

    5> }

    executing command: SET NEWNAME

    using target database control file instead of recovery catalog

    renamed tempfile 1 to +TESTDBDATA in control file

    RMAN>

     

    ===Check now whether temp switched to ASM or not

    sqlplus / as sysdba

    SQL> set linesize 300

    col name for a60

    select file#, name from v$tempfile;

     

         FILE# NAME

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

             1 +TESTDBDATA

    SQL>

     

    RMAN> sql ‘alter database open’;

    sql statement: alter database open

    RMAN>

    +++Drop the redo log files in file system and recreate the online redo log files in ASM+++

    Note:—We need to drop inactive and unused group only.

    =====

    SQL> set linesize 300

    col member for a60

    col status for a10

    select a.group#,b.member,a.status from v$log a, v$logfile b where a.group#=b.group#;

     

        GROUP# MEMBER                                             STATUS

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

             3 /u01/app/oracle/oradata/TESTDB/redo03.log          INACTIVE

             2 /u01/app/oracle/oradata/TESTDB/redo02.log          INACTIVE

             1 /u01/app/oracle/oradata/TESTDB/redo01.log          CURRENT

    SQL>

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

        GROUP# STATUS

    ———- ———-

             1 CURRENT

             2 INACTIVE

             3 INACTIVE

     

    ==Add the logfile member to each group

    alter database add logfile member ‘+TESTDBDATA’ to group 1;

    alter database add logfile member ‘+TESTDBDATA’ to group 2;

    alter database add logfile member ‘+TESTDBDATA’ to group 3;

     

    ==Use below statements to force a log switches and to change the status to inactive, if necessary.

    alter system switch logfile;

    alter system checkpoint;

     

    alter database drop logfile member ‘/u01/app/oracle/oradata/TESTDB/redo01.log’;

    alter database drop logfile member ‘/u01/app/oracle/oradata/TESTDB/redo02.log’;

    alter database drop logfile member ‘/u01/app/oracle/oradata/TESTDB/redo03.log’;

     

    SQL> alter system switch logfile;

    System altered.

     

    SQL> set linesize 300

    col member for a60

    col status for a10

    select a.group#,b.member,a.status from v$log a, v$logfile b where a.group#=b.group#;

     

        GROUP# MEMBER                                                       STATUS

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

             1 +TESTDBDATA/TESTDB/ONLINELOG/group_1.294.1131656711          ACTIVE

             2 +TESTDBDATA/TESTDB/ONLINELOG/group_2.297.1131656129          CURRENT

             3 +TESTDBDATA/TESTDB/ONLINELOG/group_3.295.1131656187          INACTIVE

     

    SQL>

    +++Move Server Parameter File (SPFILE)+++

    SQL> show parameter spfile;

    NAME                                 TYPE        VALUE

    ———————————— ———– ——————————

    spfile                               string      /u01/app/oracle/product/19.0.0

                                                     /db_1/dbs/spfiletestdb.ora

    SQL>

    SQL> select name,open_mode from v$database;

    NAME      OPEN_MODE

    ——— ——————–

    TESTDB    READ WRITE

    [oracle@testdb ~]$ rman target /

    Recovery Manager: Release 19.0.0.0.0 – Production on Thu Mar 16 21:23:32 2023

    Version 19.15.0.0.0

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

    connected to target database: TESTDB (DBID=2914977916)

    RMAN>

    RMAN> restore spfile to ‘+TESTDBDATA’;

    Starting restore at 16-MAR-2023 21:23:53

    using target database control file instead of recovery catalog

    allocated channel: ORA_DISK_1

    channel ORA_DISK_1: SID=86 device type=DISK

    channel ORA_DISK_1: starting datafile backup set restore

    channel ORA_DISK_1: restoring SPFILE

    output file name=+TESTDBDATA

    channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/TESTDB/autobackup/

    2023_03_16/o1_mf_s_1131657483_l16gkmxp_.bkp

    channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/TESTDB/autobackup/

    2023_03_16/o1_mf_s_1131657483_l16gkmxp_.bkp tag=TAG20230316T211803

    channel ORA_DISK_1: restored backup piece 1

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

    Finished restore at 16-MAR-2023 21:23:56

    RMAN>

    SQL> shutdown immediate;

    Database closed.

    Database dismounted.

    ORACLE instance shut down.

    SQL>

    cd /u01/app/oracle/product/19.0.0/db_1/dbs

    mv spfiletestdb.ora spfiletestdb.ora_bkp

    vi inittestdb.ora

    spfile=’+TESTDBDATA/TESTDB/PARAMETERFILE/spfile.274.1127912285′

    :wq!

    startup

    show parameter spfile;

    +++Migrating Archivelog+++

    rman target /

    list archivelog all;

    backup as copy archivelog all delete input;

    list archivelog all;

    +++Check the filesystem whether files deleted in file system+++

    –Delete files manually from file system

    +++Register the DB and listerner to OLR/OCR+++

    srvctl add database -db testdb -oraclehome /u01/app/oracle/product/19.0.0/db_1 -spfile +TESTDBDATA/TESTDB/PARAMETERFILE/spfile.274.1127912285 

     -startoption OPEN  -stopoption IMMEDIATE -role PRIMARY -policy AUTOMATIC

     

    srvctl add listener -listener LISTENER_TESTDB -oraclehome /u01/app/oracle/product/19.0.0/db_1 -p TCP:1525

     

    srvctl start database -db testdb

    srvctl status database -db testdb -v

    srvctl status listener -listener LISTENER_TESTDB

    crsctl status res -t

    Reference:

    =========

    Steps To Migrate/Move a Database From Non-ASM to ASM And Vice-Versa (Doc ID 252219.1

    Disclaimer:

    ==========

    Please note the above information is only for educational purpose and practised in personal test database only. Always test in test database before implementing in production database. The pre-requisites and ways of implementing may vary from one environment to another. Hence, not providing guarantee that it will work in your environment.

    Popular Posts
    Categories