Monday, June 25, 2012

Migrate the database From File System to ASM


I posted before Migration Database from ASM to File System,But today i will talk about How to migration File System to ASM and this is the common case I hope i will describe this topics so well,Please if you have any In-query Contact me,Comment i will answer you,Helping you is my pleasure:


Steps:


1.configure flash recovery area.
2.Migrate datafiles to ASM.
3.Control file to ASM.
4.Create Temporary tablespace.
5.Migrate Redo logfiles
6.Migrate spfile to ASM.


step 1:Configure flash recovery area.

SQL> connect sys/sys@orcl as sysdba
Connected.
 
SQL> alter database disable block change tracking;

Database altered.

SQL> alter system set db_recovery_file_dest_size=500m;

System altered.

SQL> alter system set db_recovery_file_dest=’+FRAG’;

System altered

step 2 and 3: Migrate data files and control file to ASM

Use RMAN to migrate the data files to ASM disk groups,All data files will be migrated to the newly
 created disk group, DATA
 
SQL> alter system set db_create_file_dest='+DATA';

System altered.

SQL> alter system set control_files='+DATA/control_new.ctl' scope=spfile;

System altered.

SQL> shutdown immediate 

[oracle@rac1 bin]$ ./rman target /


RMAN> startup nomount
RMAN> restore controlfile from '/u01/new/oracle/oradata/mydb/control01.ctl';
RMAN> alter database mount;
RMAN> backup as copy database format '+DATA';
RMAN> switch database to copy;
RMAN> alter database open;
RMAN>Exit ;
SQL> conn sys/oracle as sysdba

Connected.

SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME                FILE_NAME

------------------------------ ---------------------------------------------

USERS                          +DATA/mydb/datafile/users.261.705064915

UNDOTBS1                       +DATA/mydb/datafile/undotbs1.259.705064821

SYSAUX                         +DATA/mydb/datafile/sysaux.258.705064283

SYSTEM                         +DATA/mydb/datafile/system.257.705063763


SQL> select name from v$controlfile;


NAME
----

+DATA/control_new.ctl


step 4:Migrate temp tablespace to ASM.
 
SQL> alter tablespace temp add tempfile size 100m;

Tablespace altered.

SQL> select file_name from dba_temp_files; 
FILE_NAME

---------------------------------------------

+DATA/mydb/tempfile/temp.263.705065455

otherwise,Create temporary tablespace in ASM disk group.

SQL> CREATE TABLESPACE temp1 TEMPFILE ‘+diskgroup1’;

SQL> alter database default temporary tablespace temp1;

Database altered.


step 5:Migrate redo logs to ASM.
 
SQL> select member,group# from v$logfile;
you will find i above query the redo-log in file system start adding new one to ASM after this you can delete .
SQL> alter database add logfile group 5 size 5m;

Database altered.

SQL> alter database add logfile group 6 size 5m;

Database altered. 
For example to drop , you can do the following :


SQL> alter system switch logfile;

System altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database drop logfile group 4;

Database altered.

SQL> alter database drop logfile group 1;

Database altered.
Now we can say we finish But we have to do Two More Steps 


Add additional control file



SQL> connect sys/sys@ORCL as sysdba
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.

SQL> alter database backup controlfile to '+DATA/Control_new02.ctl';

Database altered.

SQL> alter system set control_files='+DATA/Control_new.ctl '
,'+DATA/Control_new02.ctl' scope=spfile;

System altered.

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.


Step 6:Migrate spfile to ASM:

Create a copy of the SPFILE in the ASM disk group.
 
If the database is using an SPFILE already, then run these commands:

run {
BACKUP AS BACKUPSET SPFILE;
RESTORE SPFILE TO "+DISK/spfile";
} 

But if you are not using One :

SQL> create spfile='+DISK/spfile' from pfile='$ORACLE_HOME/dbs/initORCL.ora';





 Thank you
Osama mustafa












 
 

3 comments:

  1. Path Infotech is in the field of oracle training program from past several years.

    For more info : Oracle Certification Courses

    ReplyDelete
  2. The information you gave is very handy to get and of Blogs.if you want to learn online course please

    notify OS DB MIGRATION ONLINE TRAINING details.

    SAP OS DB MIGRATION ONLINE

    TRAINING

    ReplyDelete
  3. Hi
    Thanks about your article , this s very good .
    i have some question :
    I have an oracle database 11gr2 on windows and this is File system (ORACLE_SID=ORCL) and i have a new database on oracle linux 5.9 and it is grid (ASM) and ORACLE_SID=ORCL .
    I want to migrate from the non asm to asm , from windows to linux .
    For my business is your post is useful? because the spfile of non asm is not useful for asm database .
    Can you help me ?
    Thanks

    ReplyDelete