DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Saturday, October 21, 2017

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.

No comments: