In this Document
APPLIES TO:Oracle Database - Enterprise Edition - Version 10.1.0.2 to 12.1.0.1 [Release 10.1 to 12.1]Oracle Database - Standard Edition - Version 10.1.0.2 to 12.1.0.1 [Release 10.1 to 12.1] Information in this document applies to any platform. GOALAutomatic Storage Management (ASM) is an integrated file system and volume manager expressly built for Oracle database files.This note is applicable : 1. If you wish to move to different ASM storage / Hardware. 2. If you wish to change the redundancy of the diskgroup When the diskgroups are created with some redundancy say External,Normal or High then its redundancy cannot be changed. Need to change redundancy can arise if : - DBA's want to switch from Normal/High Redundancy to External Redundancy due to disk space constraints or due to plans of using External methods of maintaining redundancy (like RAID 10 , etc) . - Switch to ASM based Redundancy i.e converting from External redundancy to Normal/High Redundancy This note discusses the steps to change the redundancy of existing diskgroups in ASM.
Note : - Please note that this steps have been tried and tested internally. But we would suggest users to test the steps in a Test Environment before executing them in Production.
Also having a Full Cold Backup for the database is recommended. SOLUTIONTo collect the list of files in ASM with their full path, use the Note 888943.1 named "How to collect the full path name of the files in ASM diskgroups"There are two ways to perform this: 1. Create a new Diskgroup with desired redundancy and move the existing data to newly created Diskgroup. 2. Drop the existing Diskgroup after backing up data and create a new Diskgroup with desired redundancy. CASE 1: Create a new diskgroup with desired redundancy and move the existing data to newly created diskgroup.1) If we have extra disk space available,then we can create a new diskgroup and move the files from old diskgroup to it. -- Initially we have two diskgroup with external redundancy as:
SQL> select state,name from v$asm_diskgroup;
STATE NAME ----------- -------------------- MOUNTED DG2 MOUNTED DG3 2) Create a new diskgroup with normal redundancy as :
SQL > create diskgroup DG1 normal redundancy failgroup <failgroup1_name> disk 'disk1_name' failgroup <failgroup2_name> disk 'disk2_name';
SQL> select state,name,type from v$asm_diskgroup; STATE NAME TYPE ----------- ------------------- ------ MOUNTED DG2 EXTERN MOUNTED DG3 EXTERN MOUNTED DG1 NORMAL 3)Backup the current database as follows:
SQL> show parameter db_name
NAME TYPE VALUE ---------------- ----------- ---------------------------- db_name string orcl10g SQL> create pfile='d:\initsid.ora' from spfile; SQL> alter database backup controlfile to '+DG1'; SQL> alter system set control_files='+DG1\ORCL10G\CONTROLFILE\<system generated control file name from diskgroup DG1>' SCOPE=SPFILE; -- Connect to rman $ rman target / RMAN > shutdown immediate; RMAN > startup nomount; RMAN> restore controlfile to '<new_diskgroup i.e +DG1>' from '+DG2\ORCL10G\CONTROLFILE\mycontrol.ctl'; (specify the original (old) location of controlfile here) Mount the database and validate the controlfiles from v$controlfile RMAN > alter database mount; RMAN> backup as copy database format '+DG1';
With "BACKUP AS COPY", RMAN copies the files as image copies, bit-for-bit copies of database files created on disk.These are identical to copies of the same files that you can create with operating system commands like cp on Unix or COPY on Windows.However, using BACKUP AS COPY will be recorded in the RMAN repository and RMAN can use them in restore operations.
4)Switch the database to copy. At this moment we are switching to the new Diskgroup
RMAN> switch database to copy;
A SWITCH is equivalent to using the PL/SQL "alter database rename file" statement. RMAN> recover database ; This will recover the backup controlfile taken and restored before to be in sync with database/datafiles RMAN> alter database open resetlogs; 5)Add new tempfile to newly created database.
SQL> alter tablespace TEMP add tempfile '+DG1' SIZE 10M;
Drop any existing tempfile on the old diskgroup
SQL> alter database tempfile '+DG2/orcl10g/tempfile/temp.265.626631119' drop;
6)Find out how many members we have in redolog groups, make sure that we have only one member in each log group.(drop other members). Suppose we have 3 log groups, then add one member to each log group as following:
SQL> alter database add logfile member '+DG1' to group 1;
SQL> alter database add logfile member '+DG1' to group 2; SQL> alter database add logfile member '+DG1' to group 3; Then we can drop the old logfile member from earlier diskgroups as:
SQL> alter database drop logfile member 'complete_name';
7)Use the following query to verify that all the files are moved to new diskgroup with desired redundancy: SQL> select name from v$controlfile union select name from v$datafile union select name from v$tempfile union select member from v$logfile union select filename from v$block_change_tracking 8) Enable block change tracking using ALTER DATABASE command.
SQL> alter database enable block change tracking using file ‘<FILE_NAME>’;
Case 2:Drop the existing diskgroup after database backup and create a new diskgroup with desired redundancy.1.Shutdown(immediate) the database and then startup mount. Take a valid RMAN backup of existing database as:
RMAN> backup device type disk format 'd:\backup\%U' database ;
RMAN> backup device type disk format 'd:\backup\%U'archivelog all; 2. Make copy of spfile to accessible location: SQL> create pfile='d:\initsid.ora' from spfile; SQL> alter database backup controlfile to 'd:\control.ctl'; 3. Shutdown the RDBMS instance
SQL> shutdown immediate
4. Connect to ASM Instance and Drop the existing Diskgroups
SQL> drop diskgroup DG1 including contents;
5. Shutdown ASM Instance; 6.Startup the ASM instance in nomount state and Create the new ASM diskgroup
SQL>startup nomount
SQL> create diskgroup dg1 external redundancy disk'disk_name'; The new diskgroups name should be same as of previous diskgroup, it will facilitate the RMAN restore process. 7. Connect to the RDBMS instance and startup in nomount state using pfile
startup nomount pfile='d:\initsid.ora'
SQL> create spfile from pfile='d:\initsid.ora' 8. Now restore the controlfile and backup's using RMAN
RMAN > restore controlfile from 'd:\control.ctl';
RMAN> alter database mount; RMAN> restore database; RMAN> recover database; unable to find archive log archive log thread=1 sequence=4 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 07/05/2007 18:24:32 RMAN-06054: media recovery requesting unknown log: thread 1 seq 4 lowscn 570820 While recovery it will give an error for archive log missing, this is expected we need to open the database with resetlogs as: RMAN> alter database open resetlogs; -We also need to change Flash Recovery Area to newly created diskgroup location.
SQL> alter system set db_recovery_file_dest='+DG1' scope=both;
-We must now disable and re-enable Flashback Database so that the flashback log files are recreated in the +DG1 disk group and this can be done in mount state only.
SQL> alter database flashback off ;
SQL> alter database flashback on ; - In case you want to use new name for diskgroup,in step 8 after mounting the database , you can use :
RMAN> run{
(assuming that we have 5 datafiles in our database)set newname for datafile 1 to '+DG2'; set newname for datafile 2 to '+DG2'; set newname for datafile 3 to '+DG2'; set newname for datafile 4 to '+DG2'; set newname for datafile 5 to '+DG2'; restore database; switch datafile all; recover database; } |
No comments:
Post a Comment