Migrate Database from Non-ASM to ASM

Assumption(s):
Existing Database Name: – test_db (non-asm)
New Database Name: – test_db (asm)
SQL> select name from
v$datafile;
NAME
————————————————————
/data/mount01/test_db/system_01.dbf
/data/mount01/test_db/sysaux_01.dbf
/data/mount01/test_db/undo_t01_01.dbf
/data/mount01/test_db/tools_t01_01.dbf
/data/mount01/test_db/users_t01_01.dbf
/data/mount01/test_db/xdb_01.dbf
/data/mount01/test_db/test_c.dbf
7 rows selected.
SQL> select member from
v$logfile;
MEMBER
———————————————————————-
/data/mount03/test_db/ora_log_03_01.rdo
/data/mount03/test_db/ora_log_03_02.rdo
/data/mount03/test_db/ora_log_02_01.rdo
/data/mount03/test_db/ora_log_02_02.rdo
/data/mount03/test_db/ora_log_01_01.rdo
/data/mount03/test_db/ora_log_01_02.rdo
6 rows selected.
SQL> ALTER DATABASE DISABLE
BLOCK CHANGE TRACKING;
ALTER DATABASE DISABLE BLOCK
CHANGE TRACKING
ERROR at line 1:
ORA-19759: block change tracking
is not enabled
SQL> show parameter
db_create_file_dest
NAME                                
TYPE        VALUE
————————————
———– ——————————
db_create_file_dest                 
string
SQL>  show parameter
spfile
NAME                                
TYPE        VALUE
————————————
———– ——————————
spfile                              
string      /u01/app/oracle/product/12.1.0.2/test_db/dbs/spfitetest_db.ora
SQL> !ls -ltr
/u01/app/oracle/product/12.1.0.2/test_db/dbs/spfiletest_db.ora
-rw-r—– 1 oracle dba 4608 Dec
27 00:00 /u01/app/oracle/product/12.1.0.2/test_db/dbs/spfiletest_db.ora
/*
  This parameter
(db_create_file_dest ) define the default location for data files,  
control_files etc, if no location for these files is specified  at the
time of their creation.
*/
SQL> alter system set
db_create_file_dest=’+DG1′ scope=spfile;
System altered.
/*
If you set
db_create_online_log_dest_n, controlfile will get created at the location
specified by db_create_online_log_dest. the database does not create a control
file in DB_CREATE_FILE_DEST or in DB_RECOVERY_FILE_DEST
We skipped this step as redo log
creating in diskgroup can be taken care later.
SQL> alter system set
db_create_online_log_dest_1=’XXX’ scope=spfile;
System altered.
“Specifying Control Files at
Database Creation”
*/
SQL> SHOW PARAMETER
control_files
NAME                                
TYPE        VALUE
————————————
———– ——————————
control_files                       
string      /data/mount03/test_db/control1.ctl
/*
   Here we removed the
control_files parameter from spfile.  So next time we restore the control
file it will automatically go to +DG1 diskgroup since it is defined in
db_create_file_dest, and the new path will be automatically updated in spfile.
*/
SQL> alter system reset
control_files scope=spfile sid=’*’;
System altered.
SQL> SHUT IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP NOMOUNT;
ORACLE instance started.
Total System Global Area 
835104768 bytes
Fixed
Size                 
2257840 bytes
Variable
Size            
671091792 bytes
Database
Buffers          159383552 bytes
Redo
Buffers               
2371584 bytes
SQL> SHOW PARAMETER
control_files
NAME                                
TYPE        VALUE
————————————
———– ——————————
control_files                       
string     
/u01/app/oracle/product/12.1.0.2/test_db/dbs/cntrtest_db.dbf —-Dummy
Controlfile
SQL> show parameter
db_create_file_dest
NAME                                
TYPE        VALUE
————————————
———– ——————————
db_create_file_dest                 
string      +DG1
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying
control file, check alert log for more info
$ ./rman target /
RMAN> restore controlfile
from ‘/data/mount03/test_db/control1.ctl’;
Starting restore at 08-JAN-16
using target database control
file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=178
device type=DISK
channel ORA_DISK_1: copied
control file copy
output file
name=+DG1/test_db/controlfile/current.301.900620801
Finished restore at 08-JAN-16
RMAN> alter database mount;
database mounted
RMAN>run
{
BACKUP AS COPY DATAFILE 1 FORMAT
“+DG2”;
BACKUP AS COPY DATAFILE 2 FORMAT
“+DG2”;
BACKUP AS COPY DATAFILE 3 FORMAT
“+DG1”;
BACKUP AS COPY DATAFILE 4 FORMAT
“+DG1”;
BACKUP AS COPY DATAFILE 5 FORMAT
“+DG1”;
BACKUP AS COPY DATAFILE 6 FORMAT
“+DG1”;
BACKUP AS COPY DATAFILE 7 FORMAT
“+DG1”;
}
RMAN> report schema;
Report of database schema for
database with db_unique_name TEST_DB
List of Permanent Datafiles
===========================
File Size(MB)
Tablespace           RB segs
Datafile Name
—- ——–
——————– ——- ————————
1   
500     
SYSTEM              
***     /data/mount01/test_db/system_01.dbf
2   
500     
SYSAUX              
***     /data/mount01/test_db/sysaux_01.dbf
3   
1000    
UNDO_T01           
 ***     /data/mount01/test_db/undo_t01_01.dbf
4   
100      TOOLS_T01           
***     /data/mount01/test_db/tools_t01_01.dbf
5   
1024    
USERS_T01           
***     /data/mount01/test_db/users_t01_01.dbf
6   
200     
XDB            
     ***    
/data/mount01/test_db/xdb_01.dbf
7   
100     
TEST_C              
***     /data/mount01/test_db/test_c.dbf
RMAN> SWITCH DATABASE TO COPY;
datafile 1 switched to datafile
copy “+DG2/test_db/datafile/system.294.900618889”
datafile 2 switched to datafile
copy “+DG2/test_db/datafile/sysaux.300.900618895”
datafile 3 switched to datafile
copy “+DG1/test_db/datafile/undo_t01.297.900618897”
datafile 4 switched to datafile
copy “+DG1/test_db/datafile/tools_t01.301.900618905”
datafile 5 switched to datafile
copy “+DG1/test_db/datafile/users_t01.257.900618907”
datafile 6 switched to datafile
copy “+DG1/test_db/datafile/xdb.267.900618913”
datafile 7 switched to datafile
copy “+DG1/test_db/datafile/test_c.268.900618917”
RMAN> run
 { set newname for tempfile
1 to “+DG1”;
   switch tempfile all;
 }
executing command: SET NEWNAME
renamed tempfile 1 to +DG1 in
control file
RMAN> alter database open;
database opened
RMAN> report schema;
Report of database schema for
database with db_unique_name TEST_DB
List of Permanent Datafiles
===========================
File Size(MB)
Tablespace           RB segs
Datafile Name
—- ——–
——————– ——- ————————
1   
500     
SYSTEM              
***       
+DG2/test_db/datafile/system.297.900620831
2   
500     
SYSAUX              
***        
+DG2/test_db/datafile/sysaux.298.900620837
3   
1000    
UNDO_T01            
***    +DG1/test_db/datafile/undo_t01.299.900620839
4   
100     
TOOLS_T01           
***     +DG1/test_db/datafile/tools_t01.296.900620847
5   
1024     USERS_T01           
***    +DG1/test_db/datafile/users_t01.269.900620849
6   
200     
XDB                 
***           
+DG1/test_db/datafile/xdb.268.900620855
7   
100     
TEST_C              
***        
+DG1/test_db/datafile/test_c.267.900620857
List of Temporary Files
=======================
File Size(MB)
Tablespace          
Maxsize(MB) Tempfile Name
—- ——–
——————– ———– ——————–
1   
500     
TEMP_T01            
500        
+DG1/test_db/tempfile/temp_t01.257.900620955
Update the redo log file
location from non-asm to asm
SQL> SELECT a.group#,
b.member, a.status FROM v$log a, v$logfile b WHERE a.group#=b.group#;
    GROUP#
MEMBER                                              
———-
——————————————————-
        
3
/data/mount01/test_db/ora_log_03_01.rdo         
        
3
/data/mount01/test_db/ora_log_03_02.rdo         
        
2
/data/mount01/test_db/ora_log_02_01.rdo         
        
2
/data/mount01/test_db/ora_log_02_02.rdo         
        
1
/data/mount01/test_db/ora_log_01_01.rdo         
        
1
/data/mount01/test_db/ora_log_01_02.rdo         
6 rows selected.
SQL> ALTER DATABASE DROP
LOGFILE GROUP 3;
Database altered.
SQL> ALTER DATABASE ADD
LOGFILE group 3 (‘+REDO1’);
Database altered.
SQL>  ALTER DATABASE ADD
LOGFILE MEMBER ‘+REDO2’ TO GROUP 3;
Database altered.
SQL>  ALTER DATABASE
DROP LOGFILE GROUP 2;
Database altered.
SQL>  ALTER DATABASE ADD
LOGFILE group 2 (‘+REDO1’);
Database altered.
SQL> ALTER DATABASE ADD
LOGFILE MEMBER ‘+REDO2’ TO GROUP 2;
Database altered.
SQL> SELECT a.group#,
b.member, a.status FROM v$log a, v$logfile b WHERE a.group#=b.group#;
    GROUP#
MEMBER                                              
———-
———————————————————-
        
3 +REDO1/test_db/onlinelog/group_3.257.898874349   
     
        
3 +REDO2/test_db/onlinelog/group_3.269.898874371        
        
2
+REDO1/test_db/onlinelog/group_2.268.898874411        
        
2
+REDO2/test_db/onlinelog/group_2.267.898874417        
        
1
/data/mount01/test_db/ora_log_01_01.rdo         
        
1 /data/mount01/test_db/ora_log_01_02.rdo         
6 rows selected.
SQL> alter system switch
logfile;
System altered.
SQL> /
System altered.
SQL> alter system checkpoint;
System altered.
SQL>  ALTER DATABASE
DROP LOGFILE GROUP 1;
Database altered.
SQL> ALTER DATABASE ADD
LOGFILE group 1 (‘+REDO1’);
Database altered.
SQL> ALTER DATABASE ADD
LOGFILE MEMBER ‘+REDO2’ TO GROUP 1;
Database altered.
SQL> SELECT a.group#,
b.member, a.status FROM v$log a, v$logfile b WHERE a.group#=b.group#;
    GROUP#
MEMBER                                       
———-
———————————————
        
3 +REDO1/test_db/onlinelog/group_3.257.898874349
        
3 +REDO2/test_db/onlinelog/group_3.269.898874371
        
2 +REDO1/test_db/onlinelog/group_2.268.898874411
        
2 +REDO2/test_db/onlinelog/group_2.267.898874417
        
1 +REDO1/test_db/onlinelog/group_1.266.898874499
        
1 +REDO2/test_db/onlinelog/group_1.265.898874509
Multiplex Controlfile
SQL> select name from
v$controlfile;
NAME
————————————————–
+DG1/test_db/controlfile/current.301.900620801
SQL> alter system set
control_files=’+DG1/test_db/controlfile/current.301.900620801′,’+REDO1′,’+DG1′
scope=spfile sid=’*’;
System altered.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>  startup nomount
ORACLE instance started.
Total System Global Area 
835104768 bytes
Fixed
Size                 
2257840 bytes
Variable
Size            
671091792 bytes
Database
Buffers          159383552 bytes
Redo
Buffers               
2371584 bytes
$ ./rman target /
RMAN> restore controlfile
from ‘+DG1/test_db/controlfile/current.301.900620801’;
Starting restore at 08-JAN-16
using target database control
file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=416
device type=DISK
channel ORA_DISK_1: copied
control file copy
output file
name=+DG1/test_db/controlfile/current.301.900620801
output file
name=+REDO1/test_db/controlfile/current.272.900623351
output file
name=+DG1/test_db/controlfile/current.304.900623351
Finished restore at 08-JAN-16
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> alter database open;
database opened
SQL> select name from
v$controlfile;
NAME
—————————————-
+DG1/test_db/controlfile/current.301.900620801
+REDO/test_db/controlfile/current.272.900623351
+DG1/test_db/controlfile/current.304.900623351
Enable Block change tracking
SQL> select status from
V$BLOCK_CHANGE_TRACKING;
 STATUS
———-
DISABLED
 SQL> SELECT filename
FROM V$BLOCK_CHANGE_TRACKING;
 FILENAME
—————————————————————————–  
SQL> ALTER DATABASE ENABLE
BLOCK CHANGE TRACKING;
 Database altered.
SQL>  select status from
V$BLOCK_CHANGE_TRACKING;
 STATUS
———-
ENABLED
SQL>  SELECT filename
FROM V$BLOCK_CHANGE_TRACKING;
 FILENAME
—————————————————————————–
+DG1/test_db/changetracking/ctf.563.900723605
Move spfile in diskgroup
SQL>create
pfile=’/tmp/inittest_db.ora’ from spfile;
SQL>create spfile=’+DG1’ from
pfile=’/tmp/inittest_db.ora’;
————————————————End
of Document———————————–
Note :- When I used below script
RMAN>run {
BACKUP AS COPY DATAFILE 7 FORMAT
“+REDO”;
BACKUP AS COPY DATABASE FORMAT
“+DG1”;
}
RMAN> SWITCH DATABASE TO COPY;
All the datafiles including
datafile 7 was present in “+DG1” diskgroup only. So we should do the mapping
for each datafile with their respective diskgroup.
  • October 21, 2017 | 15 views
  • Comments