Thursday 16 February 2012

Migrate db files from nfs to ASM filesystem

Configure flash recovery area
SQL> connect sys/sys@prod1 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=’+RECOVERYDEST’;
System altered


Migrate control file to ASM
Use RMAN to migrate the data files to ASM disk groups.
All datafiles will be migrated to newly created disk group DATA

SQL> alter system set db_create_file_dest='+DATA01';
System altered.
SQL> alter system set control_files='+DATA01/control01.dbf'
scope=spfile;
System altered.
SQL> shu immediate
[oracle@rac1 bin]$ ./rman target /
RMAN> startup nomount
Oracle instance started
RMAN> restore controlfile from
'/u01/new/oracle/oradata/rajudb/control01.ctl';
Starting restore at 18-JAN-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=146 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=+DATA01/control01.dbf
Finished restore at 18-JAN-10
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1

Migrate data files to ASM
RMAN> backup as copy database format '+DATA01';
Starting backup at 18-JAN-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=146 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001
name=/u01/new/oracle/oradata/rajudb/system01.dbf
output file name=+DATA01/rajudb/datafile/system.257.705063763
tag=TAG20091208T110241 RECID=1 STAMP=705064274
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:08:39
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002
name=/u01/new/oracle/oradata/rajudb/sysaux01.dbf
output file name=+DATA01/rajudb/datafile/sysaux.258.705064283
tag=TAG20091208T110241 RECID=2 STAMP=705064812

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:08:56
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003
name=/u01/new/oracle/oradata/rajudb/undotbs01.dbf

output file name=+DATA01/rajudb/datafile/undotbs1.259.705064821
tag=TAG20091208T110241 RECID=3 STAMP=705064897

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

copying current control file
output file name=+DATA01/rajudb/controlfile/backup.260.705064907
tag=TAG20091208T110241 RECID=4 STAMP=705064912

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

input datafile file number=00004
name=/u01/new/oracle/oradata/rajudb/users01.dbf

output file name=+DATA01/rajudb/datafile/users.261.705064915
tag=TAG20091208T110241 RECID=5 STAMP=705064915

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set

including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 18-JAN-10
channel ORA_DISK_1: finished piece 1 at 18-JAN-10

piece handle=+DATA01/rajudb/backupset/2010_01_18/nnsnf0_tag29.262.7050
tag=TAG20091208T110241 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 18-JAN-10

RMAN> switch database to copy;
datafile 1 switched to datafile copy
"+DATA01/rajudb/datafile/system.257.705063763"
datafile 2 switched to datafile copy
"+DATA01/rajudb/datafile/sysaux.258.705064283"
datafile 3 switched to datafile copy
"+DATA01/rajudb/datafile/undotbs1.259.705064821"
datafile 4 switched to datafile copy
"+DATA01/rajudb/datafile/users.261.705064915"

RMAN> alter database open;
database opened
RMAN> exit
Recovery Manager complete.

SQL> conn sys/oracle as sysdba
Connected.
SQL> select tablespace_name,file_name from dba_data_files;
TABLESPACE_NAME      FILE_NAME
------------------ ---------------------------------------------
USERS                +DATA01/rajudb/datafile/users.261.705064915
UNDOTBS1             +DATA01/rajudb/datafile/undotbs1.259.705064821
SYSAUX               +DATA01/rajudb/datafile/sysaux.258.705064283
SYSTEM               +DATA01/rajudb/datafile/system.257.705063763
SQL> select name from v$controlfile;
NAME
----
+DATA01/control01.dbf

Migrate redo logs to ASM.
SQL> select member,group# from v$logfile;
MEMBER                                                 GROUP#
-------------------------------------------------- ----------
/u01/new/oracle/oradata/rajudb/redo03.log                     3
/u01/new/oracle/oradata/rajudb/redo02.log                     2
/u01/new/oracle/oradata/rajudb/redo01.log                     1

SQL> alter database add logfile group 4 size 5m;
Database altered.
SQL> alter database add logfile group 5 size 5m;
Database altered.
SQL> alter database add logfile group 6 size 5m;
Database altered.

SQL> select member,group# from v$logfile;
MEMBER                                         GROUP#
---------------------------------------------- -------
/u01/new/oracle/oradata/rajudb/redo03.log        3
/u01/new/oracle/oradata/rajudb/redo02.log        2
/u01/new/oracle/oradata/rajudb/redo01.log        1
+DATA01/rajudb/onlinelog/group_4.264.705065691   4
+DATA01/rajudb/onlinelog/group_5.265.705065703   5
+DATA01/rajudb/onlinelog/group_6.266.705065719   6

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.
Add additional control file
If an additional control file is required for redundancy,
you can create it in ASM as you would on any other filesystem.

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

SQL> alter database backup controlfile to '+DATA01/control02.dbf';
Database altered.
SQL> alter system set control_files='+DATA01/control01.dbf '
,'+DATA01/control02.dbf' scope=spfile;
System altered.

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
SQL> select name from v$controlfile;
NAME
---------------------------------------
+DATA01/control01.dbf
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
---------------------------------------------
+DATA01/rajudb/tempfile/temp.263.705065455

Else Create new temporary tablespace in ASM disk group.
SQL> CREATE TABLESPACE temp1 TEMPFILE ‘+DATA01’;
SQL> alter database default temporary tablespace temp1;
Database altered.

No comments:

Post a Comment