Oracledna

    Migrate Oracle Database from ASM to Non-ASM

    • oracledna
    • ASM
    • April 18, 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

    +++Create directories in File System to hold datafiles/redo/control file+++

    su – oracle

    mkdir -p /u01/app/oracle/product/oradata

    mkdir -p /u01/app/oracle/product/fra

    +++Change the control file location in SPFILE+++

    SQL> select name from v$controlfile;

     

    NAME

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

    +TESTDBDATA/TESTDB/CONTROLFILE/current.294.1131722387

    +TESTDBDATA/TESTDB/CONTROLFILE/current.297.1131722387

    SQL>

     

    SQL> shutdown immediate;

    Database closed.

    Database dismounted.

    ORACLE instance shut down.

     

    SQL> startup nomount

    ORACLE instance started.

    Total System Global Area 3087004336 bytes

    Fixed Size                  8901296 bytes

    Variable Size             620756992 bytes

    Database Buffers         2449473536 bytes

    Redo Buffers                7872512 bytes

    SQL>

     

    SQL> alter system set control_files=’/u01/app/oracle/product/oradata/

    control1.ctl’,’/u01/app/oracle/product/fra/control2.ctl’ scope=spfile;

    System altered.

     

    SQL> ALTER SYSTEM SET db_create_file_dest=’/u01/app/oracle/product/oradata’ scope=spfile;

    System altered.

     

    SQL> ALTER SYSTEM SET db_recovery_file_dest=’/u01/app/oracle/product/fra’ scope=spfile;

    System altered.

     

    SQL> shutdown immediate;

    ORA-01507: database not mounted

    ORACLE instance shut down.

    SQL>

    +++Migrate the control file from ASM to file system+++

    SQL> startup nomount

    ORACLE instance started.

    Total System Global Area 3087004336 bytes

    Fixed Size                  8901296 bytes

    Variable Size             620756992 bytes

    Database Buffers         2449473536 bytes

    Redo Buffers                7872512 bytes

    SQL>

     

    [oracle@testdb dbs]$ rman target /

    Recovery Manager: Release 19.0.0.0.0 – Production on Fri Mar 17 18:17:05 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 ‘+TESTDBDATA/TESTDB/CONTROLFILE/current.294.1131722387’;

     

    Starting restore at 17-MAR-2023 18:18:00

    using target database control file instead of recovery catalog

    allocated channel: ORA_DISK_1

    channel ORA_DISK_1: SID=40 device type=DISK

    channel ORA_DISK_1: copied control file copy

    output file name=/u01/app/oracle/product/oradata/control1.ctl

    output file name=/u01/app/oracle/product/fra/control2.ctl

    Finished restore at 17-MAR-2023 18:18:06

    RMAN>

     

    SQL> shut immediate

    ORA-01507: database not mounted

    ORACLE instance shut down.

    SQL>

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

    ===Query the datafiles and tempfiles

     

    SQL> startup mount

    ORACLE instance started.

    Total System Global Area 3087004336 bytes

    Fixed Size                  8901296 bytes

    Variable Size             620756992 bytes

    Database Buffers         2449473536 bytes

    Redo Buffers                7872512 bytes

    Database mounted.

    SQL>

     

    SQL> set linesize 300

    col name for a86

    select file#, name from v$datafile;

     

         FILE# NAME

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

             1 +TESTDBDATA/TESTDB/DATAFILE/system.293.1131722271

             3 +TESTDBDATA/TESTDB/DATAFILE/sysaux.275.1131722315

             4 +TESTDBDATA/TESTDB/DATAFILE/undotbs1.298.1131722341

             7 +TESTDBDATA/TESTDB/DATAFILE/users.295.1131722343

    SQL>

     

    rman target /

     

    RMAN> backup as copy database format ‘/u01/app/oracle/product/oradata/%U.dbf’;

    Starting backup at 17-MAR-2023 18:21:51

    using target database control file instead of recovery catalog

    allocated channel: ORA_DISK_1

    channel ORA_DISK_1: SID=48 device type=DISK

    channel ORA_DISK_1: starting datafile copy

    input datafile file number=00001 name=+TESTDBDATA/TESTDB/DATAFILE/system.293.1131722271

    output file name=/u01/app/oracle/product/oradata/data_D-TESTDB_I-2915050514_TS-SYSTEM_FNO-1_011n9oa3.dbf tag=TAG20230317T182155 RECID=1 STAMP=1131733320

    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=+TESTDBDATA/TESTDB/DATAFILE/sysaux.275.1131722315

    output file name=/u01/app/oracle/product/oradata/data_D-TESTDB_I-2915050514_TS-SYSAUX_FNO-3_021n9oab.dbf tag=TAG20230317T182155 RECID=2 STAMP=1131733341

    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=+TESTDBDATA/TESTDB/DATAFILE/undotbs1.298.1131722341

    output file name=/u01/app/oracle/product/oradata/data_D-TESTDB_I-2915050514_TS-UNDOTBS1_FNO-4_031n9ob4.dbf tag=TAG20230317T182155 RECID=3 STAMP=1131733353

    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=+TESTDBDATA/TESTDB/DATAFILE/users.295.1131722343

    output file name=/u01/app/oracle/product/oradata/data_D-TESTDB_I-2915050514_TS-USERS_FNO-7_041n9obb.dbf tag=TAG20230317T182155 RECID=4 STAMP=1131733355

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

    Finished backup at 17-MAR-2023 18:22:36

    Starting Control File and SPFILE Autobackup at 17-MAR-2023 18:22:36

    piece handle=/u01/app/oracle/product/fra/TESTDB/autobackup/2023_03_17/

    o1_mf_s_1131732720_l18rnnz4_.bkp comment=NONE

    Finished Control File and SPFILE Autobackup at 17-MAR-2023 18:22:37

    RMAN>

     

    RMAN> list backup;

    List of Backup Sets

    ===================

    BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

    1       Full    10.20M     DISK        00:00:01     17-MAR-2023 18:22:37

            BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20230317T182236

            Piece Name: /u01/app/oracle/product/fra/TESTDB/autobackup/

    2023_03_17/o1_mf_s_1131732720_l18rnnz4_.bkp

      SPFILE Included: Modification time: 17-MAR-2023 18:20:02

      SPFILE db_unique_name: TESTDB

      Control File Included: Ckp SCN: 2288681      Ckp time: 17-MAR-2023 18:12:00

    RMAN>

     

    RMAN> switch database to copy;

    datafile 1 switched to datafile copy “/u01/app/oracle/product/oradata/data_D-TESTDB_I-2915050514_TS-SYSTEM_FNO-1_011n9oa3.dbf”

    datafile 3 switched to datafile copy “/u01/app/oracle/product/oradata/data_D-TESTDB_I-2915050514_TS-SYSAUX_FNO-3_021n9oab.dbf”

    datafile 4 switched to datafile copy “/u01/app/oracle/product/oradata/data_D-TESTDB_I-2915050514_TS-UNDOTBS1_FNO-4_031n9ob4.dbf”

    datafile 7 switched to datafile copy “/u01/app/oracle/product/oradata/data_D-TESTDB_I-2915050514_TS-USERS_FNO-7_041n9obb.dbf”

    RMAN>

     

    ===Check now whether datafiles moved to file sysstem or not

    SQL> set linesize 300

    col name for a90

    select file#, name from v$datafile;

     

         FILE# NAME

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

             1 /u01/app/oracle/product/oradata/data_D-TESTDB_I-2915050514_TS-SYSTEM_FNO-1_011n9oa3.dbf

             3 /u01/app/oracle/product/oradata/data_D-TESTDB_I-2915050514_TS-SYSAUX_FNO-3_021n9oab.dbf

             4 /u01/app/oracle/product/oradata/data_D-TESTDB_I-2915050514_TS-UNDOTBS1_FNO-4_031n9ob4.dbf

             7 /u01/app/oracle/product/oradata/data_D-TESTDB_I-2915050514_TS-USERS_FNO-7_041n9obb.dbf

    SQL>

    +++Recreate the temp files+++

    SQL> set linesize 300

    col name for a60

    select file#, name from v$tempfile;

     

         FILE# NAME

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

             1 +TESTDBDATA/TESTDB/TEMPFILE/temp.291.1131722399

    SQL>

     

    rman target /

    RMAN> run

    {

    set newname for tempfile 1 to ‘/u01/app/oracle/product/oradata/%U.dbf’;

    switch tempfile all;

    }

    executing command: SET NEWNAME

    renamed tempfile 1 to /u01/app/oracle/product/oradata/data_D-TESTDB_TS-TEMP_FNO-1.dbf in control file

    RMAN>

    +++Check now whether temp files moved to file system or not+++

    SQL> set linesize 300

    col name for a70

    select file#, name from v$tempfile;

     

         FILE# NAME

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

             1 /u01/app/oracle/product/oradata/data_D-TESTDB_TS-TEMP_FNO-1.dbf

    SQL>

     

    SQL> set linesize 300

    SQL> col name for a15

    SQL> select name,open_mode from v$database;

     

    NAME            OPEN_MODE

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

    TESTDB          MOUNTED

    SQL>

     

    SQL> alter database open;

    Database altered.

    SQL>

    +++Drop the redo log files in ASM and recreate the online redo log files to filesystem+++

    Note:—inactive and unused group only we need to drop

    =====

     

    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 +TESTDBDATA/TESTDB/ONLINELOG/group_3.289.1131722393          CURRENT

             3 +TESTDBDATA/TESTDB/ONLINELOG/group_3.290.1131722393          CURRENT

             2 +TESTDBDATA/TESTDB/ONLINELOG/group_2.300.1131722389          INACTIVE

             2 +TESTDBDATA/TESTDB/ONLINELOG/group_2.262.1131722391          INACTIVE

             1 +TESTDBDATA/TESTDB/ONLINELOG/group_1.299.1131722389          INACTIVE

             1 +TESTDBDATA/TESTDB/ONLINELOG/group_1.301.1131722391          INACTIVE

    6 rows selected.

    SQL>

     

    SQL> alter database drop logfile group 1;

    Database altered.

    SQL> alter database add logfile group 1 size 50m;

    Database altered.

    SQL>

     

    SQL> alter database drop logfile group 2;

    Database altered.

    SQL> alter database add logfile group 2 size 50m;

    Database altered.

    SQL>

     

    SQL> alter system switch logfile;

    System altered.

     

    SQL> set linesize 300

    col member for a80

    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 +TESTDBDATA/TESTDB/ONLINELOG/group_3.289.1131722393                              ACTIVE

             3 +TESTDBDATA/TESTDB/ONLINELOG/group_3.290.1131722393                              ACTIVE

             2 /u01/app/oracle/product/oradata/TESTDB/onlinelog/o1_mf_2_l18s7hkd_.log           UNUSED

             2 /u01/app/oracle/product/fra/TESTDB/onlinelog/o1_mf_2_l18s7hm5_.log               UNUSED

             1 /u01/app/oracle/product/oradata/TESTDB/onlinelog/o1_mf_1_l18s697k_.log           CURRENT

             1 /u01/app/oracle/product/fra/TESTDB/onlinelog/o1_mf_1_l18s69bo_.log               CURRENT

     

    SQL> alter system checkpoint;

    System altered.

     

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

        GROUP# MEMBER                                                                           STATUS

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

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

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

             2 /u01/app/oracle/product/oradata/TESTDB/onlinelog/o1_mf_2_l18s7hkd_.log           UNUSED

             2 /u01/app/oracle/product/fra/TESTDB/onlinelog/o1_mf_2_l18s7hm5_.log               UNUSED

             1 /u01/app/oracle/product/oradata/TESTDB/onlinelog/o1_mf_1_l18s697k_.log           CURRENT

             1 /u01/app/oracle/product/fra/TESTDB/onlinelog/o1_mf_1_l18s69bo_.log               CURRENT

    6 rows selected.

    SQL>

     

    SQL> alter database drop logfile group 3;

    Database altered.

    SQL> alter database add logfile group 3 size 50m;

    Database altered.

    SQL>

     

    SQL> set linesize 300

    col member for a80

    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/product/oradata/TESTDB/onlinelog/o1_mf_3_l18sf59c_.log           UNUSED

             3 /u01/app/oracle/product/fra/TESTDB/onlinelog/o1_mf_3_l18sf5c8_.log               UNUSED

             2 /u01/app/oracle/product/oradata/TESTDB/onlinelog/o1_mf_2_l18s7hkd_.log           UNUSED

             2 /u01/app/oracle/product/fra/TESTDB/onlinelog/o1_mf_2_l18s7hm5_.log               UNUSED

             1 /u01/app/oracle/product/oradata/TESTDB/onlinelog/o1_mf_1_l18s697k_.log           CURRENT

             1 /u01/app/oracle/product/fra/TESTDB/onlinelog/o1_mf_1_l18s69bo_.log               CURRENT

    6 rows selected.

    SQL>

    +++Move SPFILE to file system+++

    SQL> select name,open_mode from v$database;

    NAME            OPEN_MODE

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

    TESTDB          READ WRITE

    SQL> show parameter spfile;

    NAME                                 TYPE        VALUE

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

    spfile                               string      +TESTDBDATA/TESTDB/PARAMETERFI

                                                     LE/spfile.292.1131723889



    [oracle@testdb dbs]$ rman target /

    Recovery Manager: Release 19.0.0.0.0 – Production on Fri Mar 17 18:38:35 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=2915050514)

     

    RMAN> restore spfile to ‘/u01/app/oracle/product/19.0.0/db_1/dbs/spfiletestdb.ora’;

    Starting restore at 17-MAR-2023 18:38:45

    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 backup set restore

    channel ORA_DISK_1: restoring SPFILE

    output file name=/u01/app/oracle/product/19.0.0/db_1/dbs/spfiletestdb.ora

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

    2023_03_17/o1_mf_s_1131732720_l18rnnz4_.bkp

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

    2023_03_17/o1_mf_s_1131732720_l18rnnz4_.bkp tag=TAG20230317T182236

    channel ORA_DISK_1: restored backup piece 1

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

    Finished restore at 17-MAR-2023 18:38:47

    RMAN>

     

    SQL> shutdown immediate;

    Database closed.

    Database dismounted.

    ORACLE instance shut down.

    SQL>

     

    SQL> startup

    ORACLE instance started.

    Total System Global Area 3087004336 bytes

    Fixed Size                  8901296 bytes

    Variable Size             620756992 bytes

    Database Buffers         2449473536 bytes

    Redo Buffers                7872512 bytes

    Database mounted.

    Database opened.

    SQL>

     

    SQL> show parameter spfile;

     

    NAME                                 TYPE        VALUE

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

    spfile                               string      /u01/app/oracle/product/19.0.0/db_1/dbs/spfiletestdb.ora

    SQL>

    +++Migrating Archivelog+++

    [oracle@testdb dbs]$ rman target /

    Recovery Manager: Release 19.0.0.0.0 – Production on Fri Mar 17 18:41:29 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=2915050514)

    RMAN>

    RMAN> list archivelog all;

    using target database control file instead of recovery catalog

    List of Archived Log Copies for database with db_unique_name TESTDB

    ======================================================

    Key     Thrd Seq     S Low Time

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

    1       1    9       A 17-MAR-2023 15:38:41

            Name: +TESTDBDATA/TESTDB/ARCHIVELOG/2023_03_17/thread_1_seq_9.288.1131723917

    2       1    10      A 17-MAR-2023 15:45:16

            Name: +TESTDBDATA/TESTDB/ARCHIVELOG/2023_03_17/thread_1_seq_10.287.1131731199

    3       1    11      A 17-MAR-2023 17:46:37

            Name: +TESTDBDATA/TESTDB/ARCHIVELOG/2023_03_17/thread_1_seq_11.286.1131731199

    4       1    12      A 17-MAR-2023 17:46:38

            Name: +TESTDBDATA/TESTDB/ARCHIVELOG/2023_03_17/thread_1_seq_12.285.1131731203

    5       1    13      A 17-MAR-2023 17:46:42

            Name: +TESTDBDATA/TESTDB/ARCHIVELOG/2023_03_17/thread_1_seq_13.284.1131731203

    6       1    14      A 17-MAR-2023 17:46:43

            Name: +TESTDBDATA/TESTDB/ARCHIVELOG/2023_03_17/thread_1_seq_14.271.1131731205

    7       1    15      A 17-MAR-2023 17:46:45

            Name: +TESTDBDATA/TESTDB/ARCHIVELOG/2023_03_17/thread_1_seq_15.272.1131731259

    8       1    16      A 17-MAR-2023 17:47:39

            Name: +TESTDBDATA/TESTDB/ARCHIVELOG/2023_03_17/thread_1_seq_16.273.1131731269

    9       1    17      A 17-MAR-2023 17:47:48

            Name: +TESTDBDATA/TESTDB/ARCHIVELOG/2023_03_17/thread_1_seq_17.296.1131731275

    10      1    18      A 17-MAR-2023 17:47:54

            Name: /u01/app/oracle/product/fra/TESTDB/archivelog/2023_03_17/o1_mf_1_18_l18s8hs3_.arc

    RMAN>

     

    RMAN> backup as copy archivelog all delete input;

    Starting backup at 17-MAR-2023 18:42:47

    current log archived

    allocated channel: ORA_DISK_1

    channel ORA_DISK_1: SID=76 device type=DISK

    channel ORA_DISK_1: starting archived log copy

    input archived log thread=1 sequence=9 RECID=1 STAMP=1131723918

    output file name=/u01/app/oracle/product/fra/TESTDB/archivelog/2023_03_17/o1_mf_1_9_l18stmkh_.arc RECID=12 STAMP=1131734572

    channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:03

    channel ORA_DISK_1: deleting archived log(s)

    archived log file name=+TESTDBDATA/TESTDB/ARCHIVELOG/2023_03_17/thread_1_seq_9.288.1131723917 RECID=1 STAMP=1131723918

    channel ORA_DISK_1: starting archived log copy

    input archived log thread=1 sequence=10 RECID=2 STAMP=1131731198

    output file name=/u01/app/oracle/product/fra/TESTDB/archivelog/2023_03_17/o1_mf_1_10_l18stppc_.arc RECID=13 STAMP=1131734575

    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=+TESTDBDATA/TESTDB/ARCHIVELOG/2023_03_17/thread_1_seq_10.287.1131731199 RECID=2 STAMP=1131731198

    channel ORA_DISK_1: starting archived log copy

    input archived log thread=1 sequence=19 RECID=11 STAMP=1131734567

    output file name=/u01/app/oracle/product/fra/TESTDB/archivelog/2023_03_17/o1_mf_1_19_l18stqtt_.arc RECID=14 STAMP=1131734575

    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/product/fra/TESTDB/archivelog/2023_03_17/o1_mf_1_19_l18sth8n_.arc RECID=11 STAMP=1131734567

    channel ORA_DISK_1: starting archived log copy

    input archived log thread=1 sequence=18 RECID=10 STAMP=1131733991

    output file name=/u01/app/oracle/product/fra/TESTDB/archivelog/2023_03_17/o1_mf_1_18_l18stryr_.arc RECID=15 STAMP=1131734577

    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/product/fra/TESTDB/archivelog/2023_03_17/o1_mf_1_18_l18s8hs3_.arc RECID=10 STAMP=1131733991

    channel ORA_DISK_1: starting archived log copy

    input archived log thread=1 sequence=12 RECID=4 STAMP=1131731202

    output file name=/u01/app/oracle/product/fra/TESTDB/archivelog/2023_03_17/o1_mf_1_12_l18stt3w_.arc RECID=16 STAMP=1131734578

    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=+TESTDBDATA/TESTDB/ARCHIVELOG/2023_03_17/thread_1_seq_12.285.1131731203 RECID=4 STAMP=1131731202

    channel ORA_DISK_1: starting archived log copy

    input archived log thread=1 sequence=15 RECID=7 STAMP=1131731259

    output file name=/u01/app/oracle/product/fra/TESTDB/archivelog/2023_03_17/o1_mf_1_15_l18stv8c_.arc RECID=17 STAMP=1131734579

    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=+TESTDBDATA/TESTDB/ARCHIVELOG/2023_03_17/thread_1_seq_15.272.1131731259 RECID=7 STAMP=1131731259

    channel ORA_DISK_1: starting archived log copy

    input archived log thread=1 sequence=14 RECID=6 STAMP=1131731205

    output file name=/u01/app/oracle/product/fra/TESTDB/archivelog/2023_03_17/o1_mf_1_14_l18stwdt_.arc RECID=18 STAMP=1131734580

    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=+TESTDBDATA/TESTDB/ARCHIVELOG/2023_03_17/thread_1_seq_14.271.1131731205 RECID=6 STAMP=1131731205

    channel ORA_DISK_1: starting archived log copy

    input archived log thread=1 sequence=16 RECID=8 STAMP=1131731268

    output file name=/u01/app/oracle/product/fra/TESTDB/archivelog/2023_03_17/o1_mf_1_16_l18stxkp_.arc RECID=19 STAMP=1131734581

    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=+TESTDBDATA/TESTDB/ARCHIVELOG/2023_03_17/thread_1_seq_16.273.1131731269 RECID=8 STAMP=1131731268

    channel ORA_DISK_1: starting archived log copy

    input archived log thread=1 sequence=17 RECID=9 STAMP=1131731274

    output file name=/u01/app/oracle/product/fra/TESTDB/archivelog/2023_03_17/o1_mf_1_17_l18styp5_.arc RECID=20 STAMP=1131734582

    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=+TESTDBDATA/TESTDB/ARCHIVELOG/2023_03_17/thread_1_seq_17.296.1131731275 RECID=9 STAMP=1131731274

    channel ORA_DISK_1: starting archived log copy

    input archived log thread=1 sequence=11 RECID=3 STAMP=1131731198

    output file name=/u01/app/oracle/product/fra/TESTDB/archivelog/2023_03_17/o1_mf_1_11_l18stztt_.arc RECID=21 STAMP=1131734583

    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=+TESTDBDATA/TESTDB/ARCHIVELOG/2023_03_17/thread_1_seq_11.286.1131731199 RECID=3 STAMP=1131731198

    channel ORA_DISK_1: starting archived log copy

    input archived log thread=1 sequence=13 RECID=5 STAMP=1131731203

    output file name=/u01/app/oracle/product/fra/TESTDB/archivelog/2023_03_17/o1_mf_1_13_l18sv103_.arc RECID=22 STAMP=1131734585

    channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:02

    channel ORA_DISK_1: deleting archived log(s)

    archived log file name=+TESTDBDATA/TESTDB/ARCHIVELOG/2023_03_17/thread_1_seq_13.284.1131731203 RECID=5 STAMP=1131731203

    Finished backup at 17-MAR-2023 18:43:06

    Starting Control File and SPFILE Autobackup at 17-MAR-2023 18:43:06

    piece handle=/u01/app/oracle/product/fra/TESTDB/autobackup/

    2023_03_17/o1_mf_s_1131734586_l18sv295_.bkp comment=NONE

    Finished Control File and SPFILE Autobackup at 17-MAR-2023 18:43:07

    RMAN>

    RMAN> list archivelog all;

    List of Archived Log Copies for database with db_unique_name TESTDB

    ==================================================

    Key     Thrd Seq     S Low Time

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

    12      1    9       A 17-MAR-2023 15:38:41

            Name: /u01/app/oracle/product/fra/TESTDB/archivelog/2023_03_17/o1_mf_1_9_l18stmkh_.arc

    13      1    10      A 17-MAR-2023 15:45:16

            Name: /u01/app/oracle/product/fra/TESTDB/archivelog/2023_03_17/o1_mf_1_10_l18stppc_.arc

    21      1    11      A 17-MAR-2023 17:46:37

            Name: /u01/app/oracle/product/fra/TESTDB/archivelog/2023_03_17/o1_mf_1_11_l18stztt_.arc

    16      1    12      A 17-MAR-2023 17:46:38

            Name: /u01/app/oracle/product/fra/TESTDB/archivelog/2023_03_17/o1_mf_1_12_l18stt3w_.arc

    22      1    13      A 17-MAR-2023 17:46:42

            Name: /u01/app/oracle/product/fra/TESTDB/archivelog/2023_03_17/o1_mf_1_13_l18sv103_.arc

    18      1    14      A 17-MAR-2023 17:46:43

            Name: /u01/app/oracle/product/fra/TESTDB/archivelog/2023_03_17/o1_mf_1_14_l18stwdt_.arc

    17      1    15      A 17-MAR-2023 17:46:45

            Name: /u01/app/oracle/product/fra/TESTDB/archivelog/2023_03_17/o1_mf_1_15_l18stv8c_.arc

    19      1    16      A 17-MAR-2023 17:47:39

            Name: /u01/app/oracle/product/fra/TESTDB/archivelog/2023_03_17/o1_mf_1_16_l18stxkp_.arc

    20      1    17      A 17-MAR-2023 17:47:48

            Name: /u01/app/oracle/product/fra/TESTDB/archivelog/2023_03_17/o1_mf_1_17_l18styp5_.arc

    15      1    18      A 17-MAR-2023 17:47:54

            Name: /u01/app/oracle/product/fra/TESTDB/archivelog/2023_03_17/o1_mf_1_18_l18stryr_.arc

    14      1    19      A 17-MAR-2023 18:33:11

            Name: /u01/app/oracle/product/fra/TESTDB/archivelog/2023_03_17/o1_mf_1_19_l18stqtt_.arc

    RMAN>

    +++Unregister the DB and listerner from OLR/OCR+++

    [oracle@testdb ~]$ srvctl stop database -db TESTDB

    [oracle@testdb ~]$ srvctl remove database -db TESTDB

    Remove the database TESTDB? (y/[n]) y

    [oracle@testdb ~]$

    [oracle@testdb ~]$ srvctl stop listener -listener LISTENER_TESTDB

    [oracle@testdb ~]$ srvctl remove listener -listener LISTENER_TESTDB

    [grid@testdb ~]$ crsctl status  res -t

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

    Name           Target  State        Server                   State details

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

    Local Resources

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

    ora.LISTENER.lsnr

                   ONLINE  ONLINE       testdb                   STABLE

    ora.TESTDBDATA.dg

                   ONLINE  ONLINE       testdb                   STABLE

    ora.asm

                   ONLINE  ONLINE       testdb                   Started,STABLE

    ora.ons

                   OFFLINE OFFLINE      testdb                   STABLE

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

    Cluster Resources

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

    ora.cssd

          1        ONLINE  ONLINE       testdb                   STABLE

    ora.diskmon

          1        OFFLINE OFFLINE                               STABLE

    ora.evmd

          1        ONLINE  ONLINE       testdb                   STABLE

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

    [grid@testdb ~]$

    [oracle@testdb ~]$ sqlplus / as sysdba

    SQL*Plus: Release 19.0.0.0.0 – Production on Fri Mar 17 18:51:23 2023

    Version 19.15.0.0.0

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

    Connected to an idle instance.

    SQL> startup

    ORACLE instance started.

    Total System Global Area 3087004336 bytes

    Fixed Size                  8901296 bytes

    Variable Size             620756992 bytes

    Database Buffers         2449473536 bytes

    Redo Buffers                7872512 bytes

    Database mounted.

    Database opened.

    SQL>

    Reference:

    ==========

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

     

    https://docs.oracle.com/cd/B19306_01/backup.102/b14191/rcmasm.htm#i1014926

    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