When the disk groups 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
- 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 is for RAC Grid infrastructure
11.2.0.2
- 2 nodes : node1_name & node2_name
- Linux 64nits
oragrid:
SQL> select
state,name,type from v$asm_diskgroup;
STATE NAME TYPE
-----------
------------------------------ ------
MOUNTED DATA_ASM NORMAL
SQL> col gnum
format 99
SQL> col filnum
format 999999999999
SQL> col path
format a60
SQL> SELECT
gnum, filnum, concat('+'||gname,sys_connect_by_path(aname, '/')) path
2 FROM
(SELECT g.name gname, a.parent_index pindex, a.name aname,
3 a.reference_index
rindex, a.group_number gnum,a.file_number filnum
4 FROM
v$asm_alias a,v$asm_diskgroup g
5 WHERE
a.group_number = g.group_number)
6 START
WITH (mod(pindex, power(2, 24))) = 0
7 CONNECT
BY PRIOR rindex = pindex;
GNUM FILNUM
PATH
---- -------------
------------------------------------------------------------
1 4294967295
+DATA_ASM/scan-cluster
1 4294967295
+DATA_ASM/scan-cluster/ASMPARAMETERFILE
1 253
+DATA_ASM/scan-cluster/ASMPARAMETERFILE/REGISTRY.253.8073
78527
1 4294967295
+DATA_ASM/scan-cluster/OCRFILE
1 255
+DATA_ASM/scan-cluster/OCRFILE/REGISTRY.255.807379617
1 4294967295
+DATA_ASM/MY_DB
1 4294967295
+DATA_ASM/MY_DB/DATAFILE
1 271
+DATA_ASM/MY_DB/DATAFILE/INDX.271.809516379
1 270
+DATA_ASM/MY_DB/DATAFILE/DATAX.270.807802575
GNUM FILNUM
PATH
---- -------------
------------------------------------------------------------
1 268
+DATA_ASM/MY_DB/DATAFILE/DATAX.268.807796427
1 264
+DATA_ASM/MY_DB/DATAFILE/UNDOTBS2.264.807705403
1 259
+DATA_ASM/MY_DB/DATAFILE/USERS.259.807705229
1 258
+DATA_ASM/MY_DB/DATAFILE/UNDOTBS1.258.807705227
1 257
+DATA_ASM/MY_DB/DATAFILE/SYSAUX.257.807705225
1 256
+DATA_ASM/MY_DB/DATAFILE/SYSTEM.256.807705225
1 269
+DATA_ASM/MY_DB/DATAFILE/DATAX.269.807802377
1 4294967295
+DATA_ASM/MY_DB/CONTROLFILE
1 260
+DATA_ASM/MY_DB/CONTROLFILE/Current.260.807705377
1 4294967295
+DATA_ASM/MY_DB/ONLINELOG
1 265
+DATA_ASM/MY_DB/ONLINELOG/group_3.265.807705465
GNUM FILNUM
PATH
---- -------------
------------------------------------------------------------
1 266
+DATA_ASM/MY_DB/ONLINELOG/group_4.266.807705465
1 261
+DATA_ASM/MY_DB/ONLINELOG/group_1.261.807705379
1 262
+DATA_ASM/MY_DB/ONLINELOG/group_2.262.807705381
1 4294967295
+DATA_ASM/MY_DB/TEMPFILE
1 263
+DATA_ASM/MY_DB/TEMPFILE/TEMP.263.807705385
1 4294967295
+DATA_ASM/MY_DB/PARAMETERFILE
1 267
+DATA_ASM/MY_DB/PARAMETERFILE/spfile.267.807705467
1 267
+DATA_ASM/MY_DB/spfileMY_DB.ora
28 rows selected.
As oracle:
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 ;
NAME
--------------------------------------------------------------------------------
+DATA_ASM/MY_DB/controlfile/current.260.807705377
+DATA_ASM/MY_DB/datafile/datax.268.807796427
+DATA_ASM/MY_DB/datafile/datax.269.807802377
+DATA_ASM/MY_DB/datafile/datax.270.807802575
+DATA_ASM/MY_DB/datafile/indx.271.809516379
+DATA_ASM/MY_DB/datafile/sysaux.257.807705225
+DATA_ASM/MY_DB/datafile/system.256.807705225
+DATA_ASM/MY_DB/datafile/undotbs1.258.807705227
+DATA_ASM/MY_DB/datafile/undotbs2.264.807705403
+DATA_ASM/MY_DB/datafile/users.259.807705229
+DATA_ASM/MY_DB/onlinelog/group_1.261.807705379
NAME
--------------------------------------------------------------------------------
+DATA_ASM/MY_DB/onlinelog/group_2.262.807705381
+DATA_ASM/MY_DB/onlinelog/group_3.265.807705465
+DATA_ASM/MY_DB/onlinelog/group_4.266.807705465
+DATA_ASM/MY_DB/tempfile/temp.263.807705385
SQL> show parameter pfile;
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
spfile string +DATA_ASM/MY_DB/spfileMY_DB.ora
SQL> select A.group# as "Group",
A.thread#, B.member, round(A.bytes/1024/1024,0) as "Size (Mo)"
from v$log a, v$logfile b
where a.group# = b.group#
order by A.group#, B.member; 2 3 4
Group THREAD#
----- -------
MEMBER
--------------------------------------------------------------------------------
Size (Mo)
----------
1 1
+DATA_ASM/MY_DB/onlinelog/group_1.261.807705379
50
2 1
+DATA_ASM/MY_DB/onlinelog/group_2.262.807705381
50
Group THREAD#
----- -------
MEMBER
--------------------------------------------------------------------------------
Size (Mo)
----------
3 2
+DATA_ASM/MY_DB/onlinelog/group_3.265.807705465
50
4 2
+DATA_ASM/MY_DB/onlinelog/group_4.266.807705465
Group THREAD#
----- -------
MEMBER
--------------------------------------------------------------------------------
Size (Mo)
----------
50
1.2 Drop the existing
disk group after database backup and create a new disk group with External
redundancy
1. Shutdown (immediate) the database and then start up
mount. Take a valid RMAN backup of existing database as:
Method1:
On each node, and as oracle user:
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Startup mount:
SQL> startup
mount;
Method2:
You can also, use the following method, as oracle
user:
srvctl stop database -d MY_DB -o immediate
Startup mount:
srvctl start
database -d MY_DB -o mount
On the first node:
mkdir –p /livraison/backup/
rman target /
RMAN> run {
allocate channel chn1 device
type DISK format '/livraison/backup/%U';
backup full database spfile
plus archivelog delete all input;
}
2. Make copy of spfile to accessible location:
On the first node, and as oracle user:
SQL> create pfile='/livraison/backup/initMY_DB.ora'
from spfile;
SQL> alter database backup controlfile to '/livraison/backup/control.ctl';
SQL> alter database backup controlfile to '/livraison/backup/control.ctl';
3. Shutdown the database
srvctl stop database -d MY_DB -o immediate
4. Connect to ASM Instance and Drop the existing
Diskgroups
4.1 Stop asm on the other nodes (except the
first one).
As oargrid user:
Method1:
SQL> shutdown
immediate;
ASM diskgroups
volume disabled
ASM diskgroups
dismounted
ASM instance
shutdown
Method2:
crsctl stop resource ora.asm -n node2_name
-f
4.2 On the first node, and as oragrid user:
sqlplus '/as sysasm'
SQL> drop diskgroup DATA_ASM including
contents;
Diskgroup dropped.
If you have such errors:
ERROR at line 1:
ORA-15039:
diskgroup not dropped
ORA-15027: active
use of diskgroup "DATA_ASM" precludes its dismount
Then, ensure that asm are stopped on each node (except
the current one) and do:
SQL> alter diskgroup DATA_ASM dismount force;
SQL> drop diskgroup DATA_ASM force including contents;
5. Shutdown ASM Instance on the first node.
On the first node, and as oragrid user:
sqlplus '/as
sysasm'
SQL> shutdown immediate;
6. Startup the ASM instance in nomount state and Create the new ASM
diskgroup
On each node, as oragrid user:
Two methods to do:
Method 1:
sqlplus '/as sysasm'
SQL> startup nomount;
ORA-00099: warning: no parameter file specified for ASM instance
ASM instance started
Total System Global Area 283930624 bytes
Fixed
Size
2225792 bytes
Variable
Size
256539008 bytes
ASM
Cache
25165824 bytes
Method 2:
srvctl start asm -o nomount
Check:
crsctl stat res -t
The new diskgroups name should be same as of previous
diskgroup, it will facilitate the RMAN restore process.
Locate the asmca binary (for example
/logiciels/oracle/grid/bin/)
cd /logiciels/oracle/grid/bin/
export DISPLAY=XXXX
./asmca
7. Connect to the RDBMS instance and startup in no
mount state using pfile
On the first node, as oragrid user:
srvctl start instance -d MY_DB -n node1_name -o
nomount;
On the first node, and as oracle user:
cd /livraison/backup
SQL> startup nomount pfile='initMY_DB.ora';
sql> create spfile='+DATA_ASM/MY_DB/spfileMY_DB.ora'
from pfile='/livraison/backup/initMY_DB.ora';
Check:
ASMCMD> ls -l DATA_ASM/MY_DB/
Type Redund Striped Time Sys Name
Y PARAMETERFILE/
N spfileMY_DB.ora
=> +DATA_ASM/MY_DB/PARAMETERFILE/spfile.256.811956759
ASMCMD> ls -l DATA_ASM/MY_DB/PARAMETERFILE/
Type Redund Striped Time Sys Name
PARAMETERFILE UNPROT COARSE APR
05 15:00:00 Y spfile.256.811956759
At this moment, shutdown and restart the database on
no mount.
SQL> Shutdown immediate;
SQL> Startup nomount;
8. Now restore the controlfile and backup's using RMAN
On the first node:
rman target/
RMAN> restore controlfile from
'/livraison/backup/control.ctl';
Starting restore at 04-APR-13
using target database control file instead of recovery
catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=16 instance=MY_DB1 device
type=DISK
channel ORA_DISK_1: copied control file copy
output file name=+DATA_ASM/MY_DB/controlfile/current.256.811867053
Finished restore at 04-APR-13
RMAN> alter database mount;
RMAN> restore database;
Starting restore at 04-APR-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=16 instance=MY_DB1 device
type=DISK
channel ORA_DISK_1: starting datafile backup set
restore
channel ORA_DISK_1: specifying datafile(s) to restore
from backup set
channel ORA_DISK_1: restoring datafile 00001 to
+DATA_ASM/MY_DB/datafile/system.256.807705225
channel ORA_DISK_1: restoring datafile 00002 to
+DATA_ASM/MY_DB/datafile/sysaux.257.807705225
channel ORA_DISK_1: restoring datafile 00003 to
+DATA_ASM/MY_DB/datafile/undotbs1.258.807705227
channel ORA_DISK_1: restoring datafile 00004 to
+DATA_ASM/MY_DB/datafile/users.259.807705229
channel ORA_DISK_1: restoring datafile 00005 to
+DATA_ASM/MY_DB/datafile/undotbs2.264.807705403
channel ORA_DISK_1: restoring datafile 00006 to
+DATA_ASM/MY_DB/datafile/datax.271.811862519
channel ORA_DISK_1: restoring datafile 00007 to
+DATA_ASM/MY_DB/datafile/indx.270.811862579
channel ORA_DISK_1: reading from backup piece
/livraison/backup/04o68323_1_1
channel ORA_DISK_1: piece
handle=/livraison/backup/04o68323_1_1 tag=TAG20130404T133147
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time:
00:01:15
Finished restore at 04-APR-13
RMAN> alter database open resetlogs;
9. Restart the other nodes
On the first node, and as oracle user:
srvctl start instance -d MY_DB -n node2_name;
As oragrid:
SQL> select state,name,type from
v$asm_diskgroup;
STATE NAME TYPE
----------- ------------------------------ ------
MOUNTED DATA_ASM EXTERN
/home/dba/oragrid> crsctl status resource ora.DATA_ASM.dg
NAME=ora.DATA_ASM.dg
TYPE=ora.diskgroup.type
TARGET=ONLINE ,
ONLINE
STATE=ONLINE on node1_name, ONLINE on node2_name
