Archive

Posts Tagged ‘FRA’

How to migrate Fast Recovery Area from file system to an ASM Disk Group in Oracle 11.2?

November 26, 2010 Leave a comment

 

It is a good practice to have the Fast Recovery Area(FRA) as a storage location for the recovery related files( backups, archived logs, control file backups, multiplied redologs, flashback logs). Having said that, it is even better reliability and performance point of view to have the FRA on a separate ASM Disk Group instead on the file system.

This post will show the step-by-step procedure I used to migrate successfully the FRA from the file system to ASM. The environment is Oracle 11.2.0.1 64 bit database on Windows 2008 server.

1. From amca or command line create a new FRA ASM Disk Group with external redundancy

2. Change db_recovery parameters:

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

System altered.

SQL> alter system set db_recovery_file_dest_size=199G;

System altered.

SQL> show parameter db_recovery_file_dest

NAME TYPE VALUE

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

db_recovery_file_dest string +FRA

db_recovery_file_dest_size big integer 199G

 

3. Perform switch log file:

SQL> alter system switch logfile;

System altered.

 

4. The database is in ARCHIVELOG and check if the archive logs go in the new locations after switch log file:

SQL> set head off;
SQL> set linesize 100;

SQL> select sequence#, first_time, name from v$archived_log order by first_time desc;
4 2010-11-22 +FRA/SID/archivelog/2010_11_22/thread_1_seq_4.256.735758149
3 2010-11-20 D:\ORACLEFRA\SID\ARCHIVELOG\2010_11_22\O1_MF_1_3_6GNYOWQO_.ARC
2 2010-11-19 D:\ORACLEFRA\SID\ARCHIVELOG\2010_11_20\O1_MF_1_2_6GGQW2LL_.ARC
1 2010-11-17 D:\ORACLEFRA\SID\ARCHIVELOG\2010_11_19\O1_MF_1_1_6GFOT8G4_.ARC

 

5. The datafiles are located on a separate ASM Disk Group +ASMDATA:

SQL> select name from v$datafile;
NAME
———————————————————————–
+ASMDATA/SID/datafile/system.256.735341597
+ASMDATA/SID/datafile/sysaux.257.735341597
+ASMDATA/SID/datafile/undotbs1.258.735341597
+ASMDATA/SID/datafile/users.259.735341597

 

6. Recreate online redologs

SQL> select group#, member from v$logfile order by group#;

1 +ASMDATA/SID/onlinelog/group_1.261.735341775
1 D:\ORACLEFRA\SID\ONLINELOG\O1_MF_1_6G8COLN1_.LOG
2 +ASMDATA/SID/onlinelog/group_2.262.735341787
2 D:\ORACLEFRA\SID\ONLINELOG\O1_MF_2_6G8COYWQ_.LOG
3 +ASMDATA/SID/onlinelog/group_3.263.735341799
3 D:\ORACLEFRA\SID\ONLINELOG\O1_MF_3_6G8CPC56_.LOG
6 rows selected.

 

6.1

SQL> alter database add logfile member ‘+FRA/SID/ONLINELOG/redolog4_group1.log’ to group 1;

Database altered.

SQL> alter database add logfile member ‘+FRA/SID/ONLINELOG/redolog5_group2.log’ to group 2;

Database altered.

SQL> alter database add logfile member ‘+FRA/SID/ONLINELOG/redolog6_group3.log’ to group 3;

Database altered.

SQL> select group#, member, status from v$logfile
2 order by status, group#;

1 +FRA/SID/onlinelog/redolog4_group1.log INVALID
2 +FRA/SID/onlinelog/redolog5_group2.log INVALID
3 +FRA/SID/onlinelog/redolog6_group3.log INVALID
1 +ASMDATA/SID/onlinelog/group_1.261.735341775 (null)
1 D:\ORACLEFRA\SID\ONLINELOG\O1_MF_1_6G8COLN1_.LOG (null)
2 +ASMDATA/SID/onlinelog/group_2.262.735341787 (null)
2 D:\ORACLEFRA\SID\ONLINELOG\O1_MF_2_6G8COYWQ_.LOG (null)
3 +ASMDATA/SID/onlinelog/group_3.263.735341799 (null)
3 D:\ORACLEFRA\SID\ONLINELOG\O1_MF_3_6G8CPC56_.LOG (null)
9 rows selected.
SQL>

 

6.2 At the moment 1 and 3 redolog group are not CURRENT. Drop old logfile member.

SQL> alter database drop logfile member ‘D:\ORACLEFRA\SID\ONLINELOG\O1_MF_1_6G8COLN1_.LOG’;
Database altered.
SQL> alter database drop logfile member ‘D:\ORACLEFRA\SID\ONLINELOG\O1_MF_3_6G8CPC56_.LOG’;
Database altered.
SQL>

 

6.3 Switch logfile

SQL> alter system switch logfile;
System altered.

SQL> alter database drop logfile member ‘D:\ORACLEFRA\SID\ONLINELOG\O1_MF_2_6G8COYWQ_.LOG’;
Database altered.

SQL> alter system switch logfile;
System altered.

 

6.4 After recreating and the redologs are migrated from old FRA to ASM, the situation should look like:

SQL> select group#, type, status , member from v$logfile
2 order by status, group#, member;
1 ONLINE +ASMDATA/SID/onlinelog/group_1.261.735341775 (null)
1 ONLINE +FRA/SID/onlinelog/redolog4_group1.log (null)
2 ONLINE +ASMDATA/SID/onlinelog/group_2.262.735341787 (null)
2 ONLINE +FRA/SID/onlinelog/redolog5_group2.log (null)
3 ONLINE +ASMDATA/SID/onlinelog/group_3.263.735341799 (null)
3 ONLINE +FRA/SID/onlinelog/redolog6_group3.log (null)
6 rows selected.
SQL>

 

7. Duplicating a controlfile to FRA ASM when original controlfile is stored on +ASMDATA:

7.1 Identify the location of the current controlfile:

SQL> select name, is_recovery_dest_file as is_recovery from v$controlfile;
NAME IS_RECOVERY
—————————————————————
+ASMDATA/SID/controlfile/current.260.735341771 NO
D:\ORACLEFRA\SID\CONTROLFILE\O1_MF_6G8COBC5_.CTL YES
SQL>

We can double check on the ASM Instance with ASMCMD:

D:\>set ORACLE_SID=+ASM
D:\>set ORACLE_HOME=D:\grid\11.2
D:\Windows\system32>asmcmd
ASMCMD> pwd
+
ASMCMD> ls
ASMDATA/
FRA/
ASMCMD> cd ASMDATA
ASMCMD> ls
ASM/
SID/
ASMCMD> cd SID/
ASMCMD> cd CONTROLFILE
ASMCMD> ls
Current.260.735341771
ASMCMD>

Go to FRA and create CONTROLFILE directory:

ASMCMD> cd +FRA
ASMCMD> ls
SID/
ASMCMD> cd SID
ASMCMD> ls
ARCHIVELOG/
ONLINELOG/
ASMCMD> mkdir CONTROLFILE
ASMCMD> pwd
+FRA/SID
ASMCMD> ls
ARCHIVELOG/
CONTROLFILE/
ONLINELOG/

Or from sqlplus on the ASM Instance:

SQL> conn / as sysdba;
Connected.
SQL> alter diskgroup FRA add directory ‘+FRA/SID/CONTROLFILE’;
Diskgroup altered.

 

7.2 On Database Instance

D:\>set ORACLE_SID=SID
D:\>set ORACLE_HOME=D:\oracle\11.2

SQL> conn / as sysdba;
SQL> shutdown immediate
SQL> startup nomount
ORACLE instance started.

….> rman nocatalog
RMAN>connect target
RMAN> restore controlfile to ‘+FRA/SID/CONTROLFILE/control02.ctl’ from ‘+ASMDATA/SID/controlfile/current.260.735341771′;

SQL> alter system set control_files=’+ASMDATA/SID/controlfile/current.260.735341771′,’+FRA/SID/CONTROLFILE/control02.ctl’ scope=spfile;
System altered.

SQL> shutdown immediate

SQL> startup

Verify the new controlfile has been recognized:

SQL> show parameter control_files
NAME TYPE VALUE
———————————— ———– ——————————
control_files string +ASMDATA/SID/controlfile/current.260.735341771, +FRA/SID/controlfile/control02.ctl

 

7.3 Eventually may need to restart dbconsole if EM is not working:

…>emctl stop dbconsole
…>emctl start dbconsole

 

8. Check FRA usage

…>select * from v$flash_recovery_area_usage;

and use space :

…>select space_limit/1024/1024/1024, space_used/1024/1024/1024 from v$recovery_file_dest;

 

9. Files in ASM are not accessible as OS files. To double secure the backup files and archived logs we can choose to copy them from ASM to disk/tape using RMAN:

BACKUP RECOVERY AREA;

 

 

Cheers,

Maria

Advertisements
Categories: DBA Tags: ,
%d bloggers like this: