Oracle Technologies Blog

By ASKM

Archive for the ‘ASM’ Category

Online Trainings On Oracle Database 11g

Posted by Srikrishna Murthy Annam on September 13, 2013

We are starting new trainings on oracle database 11g. Please review our site http://askmlabs.com or attend our free demo to know more details.
1) Oracle Database 11g Administration ( with 12c new features )
2) Oracle Database 11gR2 Grid with ASM and RAC Administration
3) Oracle Database 11gR2 DataGuard Administration

All these trainings including lab sessions are handled by Srikrishna Murthy Annam.

askmlabs-online-trainings1

Thanks

SRI

 

Advertisements

Posted in 11gR2 RAC, ASM, Data Guard, ORACLE Tech, RAC | 3 Comments »

relocate spfile from ASM to file system

Posted by Srikrishna Murthy Annam on February 3, 2011

In the present article i am going to show you how to move the spfile located on ASM diskgroup to the file system. This may not be the practical requirement in the RAC as we need common place for the spfile to store. If we move spfile to local file system in RAC, then it is very hard to keep the spfiles on all the nodes in sync. But it will be useful if you are trying to change any non-dynamic parameter in the spfile.

Thats fine, how do we manage spfile, but lets see  the process how do we move the spfile located in ASM to the file system.

SQL> select name,user from v$database; 

NAME                                               USER
————————————————– ——————————
TESTDB                                             SYS

SQL> show parameter spfile

NAME                                 TYPE        VALUE
———————————— ———– ——————————
spfile                               string      +DATA/testdb/spfiletestdb.ora
SQL>

[oracle@dhcppc1 ~]$ asmcmd
ASMCMD> cd +DATA/testdb/
ASMCMD> pwd
+DATA/testdb
ASMCMD> ls -lt spfile*
Type           Redund  Striped  Time             Sys  Name
N    spfiletestdb.ora => +DATA/TESTDB/PARAMETERFILE/spfile.270.738607517
ASMCMD>

[oracle@dhcppc1 datafiles]$ cd $ORACLE_HOME/dbs
[oracle@dhcppc1 dbs]$ pwd
/u01/app/11.2.0/db/dbs
[oracle@dhcppc1 dbs]$ ls -lrt init* spfile*
ls: spfile*: No such file or directory
-rw-r–r– 1 oracle oinstall 2851 May 15  2009 init.ora
-rw-r—– 1 oracle oinstall   39 Dec 24 16:45 inittestdb.ora
[oracle@dhcppc1 dbs]$ mv inittestdb.ora inittestdb.ora_bak
[oracle@dhcppc1 dbs]$ ls -lrt init* spfile*
ls: spfile*: No such file or directory
-rw-r–r– 1 oracle oinstall 2851 May 15  2009 init.ora
-rw-r—– 1 oracle oinstall   39 Dec 24 16:45 inittestdb.ora_bak
[oracle@dhcppc1 dbs]$

SQL> create pfile=’/u01/app/11.2.0/db/dbs/inittestdb.ora’ from spfile;

File created.

SQL> !ls -lrt /u01/app/11.2.0/db/dbs/inittestdb.ora
-rw-r–r– 1 oracle dba 862 Dec 31 16:42 /u01/app/11.2.0/db/dbs/inittestdb.ora

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

ASMCMD> ls -lt spfile*
Type           Redund  Striped  Time             Sys  Name
N    spfiletestdb.ora => +DATA/TESTDB/PARAMETERFILE/spfile.270.738607517
ASMCMD> rm spfiletestdb.ora
ASMCMD> ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
TEMPFILE/
ASMCMD> ls -lt spfile*
ASMCMD-08002: entry ‘spfile*’ does not exist in directory ‘+DATA/testdb/’
ASMCMD>

SQL> create spfile=’/u01/app/11.2.0/db/dbs/spfiletestdb.ora’ from pfile=’/u01/app/11.2.0/db/dbs/inittestdb.ora’;

File created.

SQL> startup
ORACLE instance started.

Total System Global Area  723984384 bytes
Fixed Size                  1338980 bytes
Variable Size             486539676 bytes
Database Buffers          230686720 bytes
Redo Buffers                5419008 bytes
Database mounted.
Database opened.
SQL> select name,user from v$database;

NAME                                               USER
————————————————– ——————————
TESTDB                                             SYS

SQL> show parameter spfile

NAME                                 TYPE        VALUE
———————————— ———– ——————————
spfile                               string      /u01/app/11.2.0/db/dbs/spfiletestdb.ora
SQL>

Hope it helps

SRI

Posted in ASM, Database Articles | Tagged: , , | 2 Comments »

Troubleshooting ASM disk drop

Posted by Srikrishna Murthy Annam on February 2, 2011

The present article describes how to troubleshoot  if you are not able delete an ASM disk from  the ASM storage. The troubleshooting technique may vary from person to person and the following article describes the technique  i used to drop an ASM disk. There may be so many other possible solutions to the issue, but the one presented here worked for me.

The present issue is that , i have to delete an ASM disk from the ASM storage. The disk is already marked as ASM disk and when i try to unmark it for ASM disk, it throws an error. So i cant delete the OS disk without deleting it from the ASM disks.

Present issue occured in 11gR2 RAC on linux and we are using multi path ASM disks for the storage.

[root@rac01 disks]# /etc/init.d/oracleasm deletedisk DATAVOL1
Removing ASM disk “DATAVOL1”:                              [FAILED] 

 

So i verified the log file  /var/log/oracleasm and the content of the log file shows me the following error

Unable to clear disk “DATAVOL1”
Clearing disk header: oracleasm-write-label: Unable to open device “/dev/oracleasm/disks/DATAVOL1”: Device or resource busy
failed
Unable to clear disk “DATAVOL1”

I tried creating a diskgroup with this ASM disk and droped it and then tried to delete the ASM disk, but no use.

SQL> create diskgroup test external redundancy disk ‘/dev/oracleasm/disks/DATAVOL1’; 

Diskgroup created.

SQL> drop diskgroup test;
Diskgroup dropped.
SQL>

[root@rac01 disks]# /etc/init.d/oracleasm deletedisk DATAVOL1
Removing ASM disk “DATAVOL1”:                              [FAILED]

I verified with the following command if the disk is still marked as ASM disk or not. It is still marked as ASM disk.

[root@rac01 disks]# blkid|grep sd.*oracleasm|while read a b;do echo -n $a$b” scsi_id=”;(echo $a|tr -d [:digit:]|tr -d [:]|cut -d”/” -f3|xargs -i scsi_id -g -s /block/{})done | grep -i DATAVOL1;
/dev/sdc:LABEL=”DATAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036b
/dev/sdf:LABEL=”DATAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036b
/dev/sdi:LABEL=”DATAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036b
/dev/sdl:LABEL=”DATAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036b
/dev/sdo:LABEL=”DATAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036b
/dev/sdp:LABEL=”DATAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036b
/dev/sdu:LABEL=”DATAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036b
/dev/sdx:LABEL=”DATAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036b
[root@rac01 disks]# 

[root@rac01 disks]# multipath -ll

..

..

vpath0 (360050768019600fba80000000000036b) dm-11 IBM,2145
[size=500G][features=1 queue_if_no_path][hwhandler=0][rw]
\_ round-robin 0 [prio=200][active]
\_ 2:0:7:0 sdo  8:224  [active][ready]
\_ 1:0:7:0 sdp  8:240  [active][ready]
\_ 2:0:6:0 sdu  65:64  [active][ready]
\_ 1:0:6:0 sdx  65:112 [active][ready]
\_ round-robin 0 [prio=40][enabled]
\_ 1:0:4:0 sdc  8:32   [active][ready]
\_ 1:0:5:0 sdf  8:80   [active][ready]
\_ 2:0:4:0 sdi  8:128  [active][ready]
\_ 2:0:5:0 sdl  8:176  [active][ready]
[root@rac01 disks]#

Then i tried clearing the disk with the OS command dd.

[root@rac01 disks]# dd if=/dev/zero of=/dev/dm-11 bs=1024 count=100
100+0 records in
100+0 records out
102400 bytes (102 kB) copied, 0.05526 seconds, 1.9 MB/s
[root@rac01 disks]# /etc/init.d/oracleasm deletedisk DATAVOL1
Removing ASM disk “DATAVOL1”:                              [  OK  ]
[root@rac01 disks]# /etc/init.d/oracleasm listdisks
CRSVOL1
DATA1
FRADISK1
FRAVOL1
[root@rac01 disks]#

Now i am able to successfully delete the disk from ASM disk list.

On all other nodes in the RAC , do the following to sync

[root@rac02 disks]# /etc/init.d/oracleasm listdisks
CRSVOL1
DATA1
DATAVOL1
FRADISK1
FRAVOL1
[root@rac02 disks]# /etc/init.d/oracleasm scandisks
Scanning the system for Oracle ASMLib disks:               [  OK  ]
[root@rac02 disks]# /etc/init.d/oracleasm listdisks
CRSVOL1
DATA1
FRADISK1
FRAVOL1
[root@rac02 disks]#

Content from the log file /var/log/oracleasm now shows …

Disk “DATAVOL1” defines an unmarked device
Dropping disk: done
Reloading disk partitions: done
Cleaning any stale ASM disks…
Validating disk “CRSVOL1”
Validating disk “DATA1”
Validating disk “FRADISK1”
Validating disk “FRAVOL1” 

 

Hope it helps

SRI

Posted in 11gR2 RAC, ASM, Database Articles, Troubleshooting | Tagged: , , , , , , , , | 3 Comments »

Moving/Renaming database files from ASM to file system

Posted by Srikrishna Murthy Annam on January 26, 2011

In the present article we will move/rename the datafiles which reside on ASM storage to normal file system. There are different ways to achieve this. We can use ASMCMD utility or RMAN  to move the datafiles from ASM to file system and then use the database rename command to update the moved location in the database.

With ASMCMD Utility :

SQL> select tablespace_name,status from dba_tablespaces where  tablespace_name=’TS1′; 

TABLESPACE_NAME STATUS
————— ———
TS1             ONLINE

SQL> select file_name,status from dba_data_files where file_id=8;

FILE_NAME                                          STATUS
————————————————– ———
+DG1/testdb/datafile/ts1.256.739201677 AVAILABLE

SQL> select file_name,status from dba_data_files where file_id=8;

FILE_NAME                                          STATUS
————————————————– ———
+DG1/testdb/datafile/ts1.256.739201677             AVAILABLE

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  723984384 bytes
Fixed Size                  1338980 bytes
Variable Size             486539676 bytes
Database Buffers          230686720 bytes
Redo Buffers                5419008 bytes
Database mounted.

ASMCMD> cd +DG1/testdb/datafile
ASMCMD> ls -lt
Type      Redund  Striped  Time             Sys  Name
DATAFILE  UNPROT  COARSE   DEC 31 14:00:00  Y    TS1.256.739201677
ASMCMD> cp ‘TS1.256.739201677’ ‘/u01/datafiles/ts1.dbf’
copying +DG1/testdb/datafile/TS1.256.739201677 -> /u01/datafiles/ts1.dbf
ASMCMD>

SQL> alter database rename file ‘+DG1/testdb/datafile/TS1.256.739201677’ to ‘/u01/datafiles/ts1.dbf’;

Database altered.

SQL> alter database open;

Database altered.

SQL> select tablespace_name,status from dba_tablespaces where  tablespace_name=’TS1′;

TABLESPACE_NAME STATUS
————— ———
TS1             ONLINE

SQL> select file_name,status from dba_data_files where file_id=8;

FILE_NAME                                          STATUS
————————————————– ———
/u01/datafiles/ts1.dbf AVAILABLE

 

Using RMAN with DB downtime.

SQL> create tablespace ts1 datafile ‘+DG1’ size 20m; 

Tablespace created.

SQL> select tablespace_name,status from dba_tablespaces where  tablespace_name=’TS1′;

TABLESPACE_NAME STATUS
————— ———
TS1             ONLINE

SQL> select file_name,status from dba_data_files where file_id=8;

FILE_NAME                                          STATUS
————————————————– ———
+DG1/testdb/datafile/ts1.256.739203881 AVAILABLE

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  723984384 bytes
Fixed Size                  1338980 bytes
Variable Size             486539676 bytes
Database Buffers          230686720 bytes
Redo Buffers                5419008 bytes
Database mounted.
SQL>

[oracle@dhcppc1 ~]$ asmcmd ls -lt +DG1/testdb/datafile/
Type      Redund  Striped  Time             Sys  Name
DATAFILE  UNPROT  COARSE   DEC 31 14:00:00  Y    TS1.256.739203881
[oracle@dhcppc1 ~]$

[oracle@dhcppc1 datafiles]$ rman target / nocatalog

Recovery Manager: Release 11.2.0.1.0 – Production on Fri Dec 31 14:29:11 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TESTDB (DBID=2521935115, not open)
using target database control file instead of recovery catalog

RMAN> copy datafile 8 to ‘/u01/datafiles/ts1.dbf’
2> ;

Starting backup at 31-DEC-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=26 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00008 name=+DG1/testdb/datafile/ts1.256.739203881
output file name=/u01/datafiles/ts1.dbf tag=TAG20101231T142940 RECID=6 STAMP=739204183
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04
Finished backup at 31-DEC-10

RMAN>

SQL> alter database rename file ‘+DG1/testdb/datafile/ts1.256.739203881’ to ‘/u01/datafiles/ts1.dbf’;

Database altered.

SQL> alter database open;

Database altered.

SQL> select tablespace_name,status from dba_tablespaces where  tablespace_name=’TS1′;

TABLESPACE_NAME STATUS
————— ———
TS1             ONLINE

SQL> select file_name,status from dba_data_files where file_id=8;

FILE_NAME                                          STATUS
————————————————– ———
/u01/datafiles/ts1.dbf AVAILABLE

 

Using RMAN without DB downtime :

SQL> create tablespace ts1 datafile ‘+DG1’ size 20m; 

Tablespace created.

SQL> select tablespace_name,status from dba_tablespaces where  tablespace_name=’TS1′;

TABLESPACE_NAME STATUS
————— ———
TS1             ONLINE

SQL> select file_name,status from dba_data_files where file_id=8;

FILE_NAME                                          STATUS
————————————————– ———
+DG1/testdb/datafile/ts1.256.739204397 AVAILABLE

SQL> alter tablespace ts1 offline;

Tablespace altered.

SQL>

[oracle@dhcppc1 datafiles]$ rman target / nocatalog

Recovery Manager: Release 11.2.0.1.0 – Production on Fri Dec 31 14:34:15 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TESTDB (DBID=2521935115)
using target database control file instead of recovery catalog

RMAN> copy datafile 8 to ‘/u01/datafiles/ts1.dbf’;

Starting backup at 31-DEC-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=31 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00008 name=+DG1/testdb/datafile/ts1.256.739204397
output file name=/u01/datafiles/ts1.dbf tag=TAG20101231T143435 RECID=7 STAMP=739204478
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 31-DEC-10

RMAN>

SQL> alter database rename file ‘+DG1/testdb/datafile/ts1.256.739204397’ to ‘/u01/datafiles/ts1.dbf’;

Database altered.

SQL> alter tablespace ts1 online;

Tablespace altered.

SQL> select tablespace_name,status from dba_tablespaces where  tablespace_name=’TS1′;

TABLESPACE_NAME STATUS
————— ———
TS1             ONLINE

SQL> select file_name,status from dba_data_files where file_id=8;

FILE_NAME                                          STATUS
————————————————– ———
/u01/datafiles/ts1.dbf AVAILABLE

 

Hope it helps

SRI

Posted in ASM, Database Articles | Tagged: , , , , | 2 Comments »

Copying files from ASM to file system

Posted by Srikrishna Murthy Annam on January 9, 2011

In the present article i am going to describe the various methods that we have to copy(Not moving,just copying) a datafile from ASM storage to file system storage.

1) Using ASMCMD Utility ( Latest method )
2) Using RMAN ( alternative method )
3) Using DBMS package ( old method )

In each method, we will copy the file from ASM to file system.

Method 1 : Using ASMCMD Utility

In this method, we are copying the datafile “TS1.256.739191187” in diskgroup DG1 from ASM to “ts2.dbf” on to the file system. 

[oracle@dhcppc1 datafiles]$ pwd
/u01/datafiles
[oracle@dhcppc1 datafiles]$ ls
[oracle@dhcppc1 datafiles]$

[oracle@dhcppc1 ~]$ asmcmd
ASMCMD> ls -lt
State    Type    Rebal  Name
MOUNTED  EXTERN  N      DATA/
MOUNTED  EXTERN  N      DG1/
MOUNTED  EXTERN  N      FRA/
MOUNTED  EXTERN  N      OCR/
ASMCMD> cd dg1
ASMCMD> cd testdb/datafile
ASMCMD> ls -lt
Type      Redund  Striped  Time             Sys  Name
DATAFILE  UNPROT  COARSE   DEC 31 13:00:00  Y    TS1.256.739191187
ASMCMD> cp TS1.256.739191187 /u01/datafiles/ts2.dbf
copying +dg1/testdb/datafile/TS1.256.739191187 -> /u01/datafiles/ts2.dbf
ASMCMD>

[oracle@dhcppc1 datafiles]$ ls -lrt
total 525208
-rw-r—– 1 oracle oinstall 314580992 Dec 31 13:17 ts2.dbf
[oracle@dhcppc1 datafiles]$

Method 2 : Using RMAN

[oracle@dhcppc1 datafiles]$ pwd
/u01/datafiles
[oracle@dhcppc1 datafiles]$ ls
[oracle@dhcppc1 datafiles]$ 

SQL> create tablespace ts1 datafile ‘+DG1’ size 20m;

Tablespace created.

SQL> select tablespace_name,status from dba_tablespaces where  tablespace_name=’TS1′;

TABLESPACE_NAME STATUS
————— ———
TS1             ONLINE

SQL> select file_name,status from dba_data_files where file_id=8;

FILE_NAME                                          STATUS
————————————————– ———
+DG1/testdb/datafile/ts1.256.739204397             AVAILABLE

[oracle@dhcppc1 datafiles]$ rman target / nocatalog

Recovery Manager: Release 11.2.0.1.0 – Production on Fri Dec 31 14:34:15 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TESTDB (DBID=2521935115)
using target database control file instead of recovery catalog

RMAN> copy datafile 8 to ‘/u01/datafiles/ts1.dbf’;

Starting backup at 31-DEC-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=31 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00008 name=+DG1/testdb/datafile/ts1.256.739204397
output file name=/u01/datafiles/ts1.dbf tag=TAG20101231T143435 RECID=7 STAMP=739204478
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 31-DEC-10

RMAN>

[oracle@dhcppc1 datafiles]$ ls -lrt
total 525208
-rw-r—– 1 oracle dba      314580992 Dec 31 14:40 ts1.dbf
[oracle@dhcppc1 datafiles]$

Method 3 : Using DBMS package

SQL> select file_name from dba_data_files; 

FILE_NAME
——————————————————————————–
+DATA/testdb/datafile/users.259.738606665
+DATA/testdb/datafile/undotbs1.258.738606665
+DATA/testdb/datafile/sysaux.257.738606663
+DATA/testdb/datafile/system.256.738606661
+DATA/testdb/datafile/example.269.738607053
+DATA/testdb/datafile/test.271.738755277
+TESTDG/testdb/datafile/test1.257.738755445
+DG1/testdb/datafile/ts1.256.739191187

8 rows selected.

SQL> desc dbms_file_transfer
PROCEDURE COPY_FILE
Argument Name                  Type                    In/Out Default?
—————————— ———————– —— ——–
SOURCE_DIRECTORY_OBJECT        VARCHAR2                IN
SOURCE_FILE_NAME               VARCHAR2                IN
DESTINATION_DIRECTORY_OBJECT   VARCHAR2                IN
DESTINATION_FILE_NAME          VARCHAR2                IN
PROCEDURE GET_FILE
Argument Name                  Type                    In/Out Default?
—————————— ———————– —— ——–
SOURCE_DIRECTORY_OBJECT        VARCHAR2                IN
SOURCE_FILE_NAME               VARCHAR2                IN
SOURCE_DATABASE                VARCHAR2                IN
DESTINATION_DIRECTORY_OBJECT   VARCHAR2                IN
DESTINATION_FILE_NAME          VARCHAR2                IN
PROCEDURE PUT_FILE
Argument Name                  Type                    In/Out Default?
—————————— ———————– —— ——–
SOURCE_DIRECTORY_OBJECT        VARCHAR2                IN
SOURCE_FILE_NAME               VARCHAR2                IN
DESTINATION_DIRECTORY_OBJECT   VARCHAR2                IN
DESTINATION_FILE_NAME          VARCHAR2                IN
DESTINATION_DATABASE           VARCHAR2                IN

SQL> create directory TEST_DIR as ‘+DG1/testdb/datafile/’;

Directory created.

SQL> create directory TARGET_DIR as ‘/u01/datafiles/’;

Directory created.

SQL> !ls -lrt /u01/datafiles/
total 0

SQL> BEGIN
dbms_file_transfer.copy_file(source_directory_object =>
‘TEST_DIR’, source_file_name => ‘ts1.256.739191187’,
destination_directory_object => ‘TARGET_DIR’,
destination_file_name => ‘ts1.dbf’);
END;
/   2    3    4    5    6    7

PL/SQL procedure successfully completed.

SQL> SQL>
SQL> !ls -lrt /u01/datafiles/
total 307512
-rw-r—– 1 oracle dba 314580992 Dec 31 13:12 ts1.dbf

SQL> select file_name from dba_data_files where tablespace_name=’TS1′;

FILE_NAME
——————————————————————————–
+DG1/testdb/datafile/ts1.256.739191187

SQL>

Hope it helps

SRI

Posted in ASM, Database Articles, RMAN | Tagged: , , , , | 7 Comments »

Drop a disk in an ASM Disk Group

Posted by Srikrishna Murthy Annam on January 8, 2011

The big challenge in ASM is to reconfigure the storage online. This is due to the feature that ASM distributes data across all disks in a disk group evenly.
So when you are planning to drop any disk from the diskgroup, we need to see if the data in the disk we are removing can be readjusted in the other available disks in the same diskgroup.
When you drop a disk from diskgroup ASM will seamlessly migrate the data to the existing disks in the disk group.

SQL> select group_number, name, TOTAL_MB, FREE_MB from V$asm_disk_stat; 

GROUP_NUMBER NAME              TOTAL_MB    FREE_MB
———— ————— ———- ———-
1 ASM_DISK1             3067       3008
2 ASM_DISK2             3067       1248
3 ASM_DISK3             5114       5062
4 DISK_E                1019        898
4 DISK_F                1019        901
4 DISK_G                1019        901

6 rows selected.

From the above output, diskgroup 4 has three disks each with the same size. If you see the FREE_MB, it is almost same in all the 3 disks.
Now we will drop DISK_F and DISK_G from diskgroup 4 and will see how the data is rebalanced in the last remaining disk DISK_E.

SQL> alter diskgroup DG1 drop disk DISK_G; 

Diskgroup altered.

SQL> alter diskgroup DG1 drop disk DISK_F;

Diskgroup altered.

SQL> select * from v$asm_operation;

GROUP_NUMBER OPERA STATE           POWER     ACTUAL      SOFAR   EST_WORK   EST_RATE EST_MINUTES ERROR_CODE
———— —– ———- ———- ———- ———- ———- ———- ———– ——————————————–
4 REBAL RUN                 1          1        145        231        660           0

SQL> select group_number, name, TOTAL_MB, FREE_MB from V$asm_disk_stat;

GROUP_NUMBER NAME              TOTAL_MB    FREE_MB
———— ————— ———- ———-
1 ASM_DISK1             3067       3008
2 ASM_DISK2             3067       1248
3 ASM_DISK3             5114       5062
4 DISK_E                1019        746
4 DISK_F                1019        977
4 DISK_G                1019        977

6 rows selected.

Now we can see from the above result that the FREE_MB for disks DISK_F and DISK_G is increasing as data is moving to DISK_E and FREE_MB is decreasing in DISK_E as data is coming from other two disks to this disk.

When the total rebalncing is completed,the disks will be removed from the diskgroup 4.

SQL> select * from v$asm_operation; 

no rows selected

SQL> select group_number, name, TOTAL_MB, FREE_MB from V$asm_disk_stat;

GROUP_NUMBER NAME              TOTAL_MB    FREE_MB
———— ————— ———- ———-
1 ASM_DISK1             3067       3008
2 ASM_DISK2             3067       1248
3 ASM_DISK3             5114       5062
4 DISK_E                1019        666

Hope it helps

SRI

Posted in ASM, Database Articles | Tagged: , , , | Leave a Comment »

ORA-01157: cannot identify/lock data file

Posted by Srikrishna Murthy Annam on January 7, 2011

In the present article, we will see how to start the database working with ASM if someone accidentally deleted moved the datafile which resides on ASM storage to a different location.

The present case is similar to the case we do on normal storage.
If we delete moved database file to a different location and if it is not updated in database, we will not be able to start the database.
So we need to update the database with this information to start it successfully.

ASMCMD> cd +DG1/testdb/datafile
ASMCMD> ls -lt
Type      Redund  Striped  Time             Sys  Name
DATAFILE  UNPROT  COARSE   DEC 31 14:00:00  Y    TS1.256.739201677
ASMCMD> cp ‘TS1.256.739201677’ ‘/u01/datafiles/ts1.dbf’
copying +DG1/testdb/datafile/TS1.256.739201677 -> /u01/datafiles/ts1.dbf
ASMCMD> 

Remove  the datafile from ASM using rm command.

[oracle@dhcppc1 ~]$ srvctl start database -d testdb
PRCR-1079 : Failed to start resource ora.testdb.db
ORA-01157: cannot identify/lock data file 7 – see DBWR trace file
ORA-01110: data file 7: ‘+TESTDG/testdb/datafile/test1.257.738755445’

CRS-2674: Start of ‘ora.testdb.db’ on ‘dhcppc1’ failed
[oracle@dhcppc1 ~]$

SQL> startup
ORACLE instance started.

Total System Global Area  723984384 bytes
Fixed Size                  1338980 bytes
Variable Size             478151068 bytes
Database Buffers          239075328 bytes
Redo Buffers                5419008 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 7 – see DBWR trace file
ORA-01110: data file 7: ‘+TESTDG/testdb/datafile/test1.257.738755445’

SQL>

SQL> alter database datafile 7 offline drop;

Database altered.

SQL> alter database open;

Database altered.

SQL> alter database rename file ‘+DG1/testdb/datafile/TS1.256.739201677’ to ‘/u01/datafiles/ts1.dbf’;

Database altered.

SQL> alter database open;

Database altered.

SQL> select tablespace_name,status from dba_tablespaces where  tablespace_name=’TS1′;

TABLESPACE_NAME STATUS
————— ———
TS1             ONLINE

SQL> select file_name,status from dba_data_files where file_id=8;

FILE_NAME                                          STATUS
————————————————– ———
/u01/datafiles/ts1.dbf                             AVAILABLE

SQL>

 

Hope it helps

SRI

ASMCMD> cd +DG1/testdb/datafile
ASMCMD> ls -lt
Type      Redund  Striped  Time             Sys  Name
DATAFILE  UNPROT  COARSE   DEC 31 14:00:00  Y    TS1.256.739201677
ASMCMD> cp ‘TS1.256.739201677’ ‘/u01/datafiles/ts1.dbf’
copying +DG1/testdb/datafile/TS1.256.739201677 -> /u01/datafiles/ts1.dbf
ASMCMD>

Posted in ASM, Database Articles, Troubleshooting | Tagged: , , , | 5 Comments »

CRS-2640

Posted by Srikrishna Murthy Annam on January 6, 2011

This article describes how to modify the  resource dependencies in the cluster.

Some time back i dropped a diskgroup DG1 from the SQL prompt and it is dropped successfully without any errors. But when i see the cluster resources status, the diskgroup resource still displays in offline status. Further investigations revealed that the disk group details still exist in OCR.

When i try to restart the database, it gives the error CRS-2640. I tried to delete the OCR details as well with srvctl command and able to delete the OCR details. Tried again to start the database but failed with error. The work around to this error is to modify the OCR with the existing diskgroups.

[oracle@dhcppc1 ~]$ crs_stat -t
Name           Type           Target    State     Host
————————————————————
ora.DATA.dg    ora….up.type ONLINE    ONLINE    dhcppc1
ora.DG1.dg     ora….up.type ONLINE    OFFLINE
ora.FRA.dg     ora….up.type ONLINE    ONLINE    dhcppc1
ora….ER.lsnr ora….er.type ONLINE    ONLINE    dhcppc1
ora.OCR.dg     ora….up.type ONLINE    ONLINE    dhcppc1
ora.asm        ora.asm.type   ONLINE    ONLINE    dhcppc1
ora.cssd       ora.cssd.type  ONLINE    ONLINE    dhcppc1
ora.diskmon    ora….on.type ONLINE    ONLINE    dhcppc1
ora.testdb.db  ora….se.type ONLINE    OFFLINE
[oracle@dhcppc1 ~]$ srvctl start database -d testdb
PRCR-1079 : Failed to start resource ora.testdb.db
ORA-15032: not all alterations performed
ORA-15017: diskgroup “DG1” cannot be mounted
ORA-15063: ASM discovered an insufficient number of disks for diskgroup “DG1”

CRS-2674: Start of ‘ora.DG1.dg’ on ‘dhcppc1’ failed
[oracle@dhcppc1 ~]$ crs_stat -t
Name           Type           Target    State     Host
————————————————————
ora.DATA.dg    ora….up.type ONLINE    ONLINE    dhcppc1
ora.DG1.dg     ora….up.type ONLINE    OFFLINE
ora.FRA.dg     ora….up.type ONLINE    ONLINE    dhcppc1
ora….ER.lsnr ora….er.type ONLINE    ONLINE    dhcppc1
ora.OCR.dg     ora….up.type ONLINE    ONLINE    dhcppc1
ora.asm        ora.asm.type   ONLINE    ONLINE    dhcppc1
ora.cssd       ora.cssd.type  ONLINE    ONLINE    dhcppc1
ora.diskmon    ora….on.type ONLINE    ONLINE    dhcppc1
ora.testdb.db  ora….se.type ONLINE    OFFLINE

[oracle@dhcppc1 ~]$ srvctl disable diskgroup -g dg1
[oracle@dhcppc1 ~]$ srvctl remove diskgroup -g dg1
[oracle@dhcppc1 ~]$ crs_stat -t
Name           Type           Target    State     Host
————————————————————
ora.DATA.dg    ora….up.type ONLINE    ONLINE    dhcppc1
ora.FRA.dg     ora….up.type ONLINE    ONLINE    dhcppc1
ora….ER.lsnr ora….er.type ONLINE    ONLINE    dhcppc1
ora.OCR.dg     ora….up.type ONLINE    ONLINE    dhcppc1
ora.asm        ora.asm.type   ONLINE    ONLINE    dhcppc1
ora.cssd       ora.cssd.type  ONLINE    ONLINE    dhcppc1
ora.diskmon    ora….on.type ONLINE    ONLINE    dhcppc1
ora.testdb.db  ora….se.type ONLINE    OFFLINE
[oracle@dhcppc1 ~]$ srvctl start database -d testdb
PRCR-1079 : Failed to start resource ora.testdb.db
CRS-2640: Required resource ‘ora.DG1.dg’ is missing.
[oracle@dhcppc1 ~]$ srvctl config database -d testdb
Database unique name: testdb
Database name: testdb
Oracle home: /u01/app/11.2.0/db
Oracle user: oracle
Spfile: +DATA/testdb/spfiletestdb.ora
Domain: testing.com
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: DATA
Services:
[oracle@dhcppc1 ~]$

[oracle@dhcppc1 ~]$ srvctl start database -d testdb
PRCR-1079 : Failed to start resource ora.testdb.db
CRS-2640: Required resource ‘ora.DG1.dg’ is missing.
[oracle@dhcppc1 ~]$ srvctl remove diskgroup -g DG1 -f
PRCR-1001 : Resource ora.DG1.dg does not exist
[oracle@dhcppc1 ~]$ crsctl status resource ora.DG1.dg -f
CRS-210: Could not find resource ‘ora.DG1.dg’.

[oracle@dhcppc1 ~]$ srvctl start database -d testdb
PRCR-1079 : Failed to start resource ora.testdb.db
CRS-2640: Required resource ‘ora.DG1.dg’ is missing.
[oracle@dhcppc1 ~]$ srvctl status database -d testdb
Database is not running.
[oracle@dhcppc1 ~]$ srvctl stop database -d testdb
PRCC-1016 : testdb was already stopped
[oracle@dhcppc1 ~]$

[oracle@dhcppc1 ~]$ srvctl modify database -d testdb -a ‘DATA,FRA,OCR’
[oracle@dhcppc1 ~]$ srvctl status database -d testdb
Database is not running.
[oracle@dhcppc1 ~]$ srvctl start database -d testdb
[oracle@dhcppc1 ~]$ srvctl status database -d testdb
Database is running.
[oracle@dhcppc1 ~]$

Hope it helps
SRI

Posted in ASM, Troubleshooting | Leave a Comment »

How to identify the physical disk bound to the ASMLIB disk?

Posted by Srikrishna Murthy Annam on September 24, 2010

We will create ASM disks with oracleasm and we can list the ASM disks using the listdisks option.
While creating the ASM disk we will supply the physical disk path, as

[root@dhcppc1 rpms]# /etc/init.d/oracleasm createdisk ASK_DISK1 /dev/sdc1
Marking disk “ASK_DISK1” as an ASM disk:                   [  OK  ]
[root@dhcppc1 rpms]#

Once after creating the ASM disks, there is no direct command which actually lists the mapping between ASM disks and physical disks.

If you check the syntax,

[root@dhcppc1 rpms]# /etc/init.d/oracleasm -h
Usage: /etc/init.d/oracleasm {start|stop|restart|enable|disable|configure|createdisk|deletedisk|querydisk|listdisks|scandisks|status}
[root@dhcppc1 rpms]#

[root@dhcppc1 rpms]# /etc/init.d/oracleasm listdisks
CRSVOL1
DATAVOL1
FRAVOL1
[root@dhcppc1 rpms]# /etc/init.d/oracleasm querydisk
Usage: oracleasm-querydisk [-l <manager>] [-v] [-d|-p] <label>|<device> …
[root@dhcppc1 rpms]# /etc/init.d/oracleasm querydisk CRSVOL1
Disk “CRSVOL1” is a valid ASM disk
[root@dhcppc1 rpms]#

So there is no option from oracleasm executable to check how ASM_DISK1 is linked to /dev/sdc1.

We will try the SQL Commands ..

SQL> select name from v$asm_diskgroup;

NAME
——————————
CRS
FRA
RACDB_DATA

SQL> select name,path,label from v$asm_disk;

NAME            PATH                                LABEL
————— ———————————– —————
CRS_0000        /dev/oracleasm/disks/CRSVOL1
FRA_0000        /dev/oracleasm/disks/FRAVOL1
RACDB_DATA_0000 /dev/oracleasm/disks/DATAVOL1

SQL>

Then how do we find, Which Disks Are Handled by ASMLib Kernel Driver?

There are two ways to identify the physical disk bound to the ASMLIB disk

1.

[root@dhcppc1 rpms]# find /dev -type b -exec ‘/etc/init.d/oracleasm’ ‘querydisk’ ‘{}’ ‘;’ 2>/dev/null | grep “is marked an ASM disk”
Device “/dev/oracleasm/disks/CRSVOL1” is marked an ASM disk with the label “CRSVOL1”
Device “/dev/oracleasm/disks/FRAVOL1” is marked an ASM disk with the label “FRAVOL1”
Device “/dev/oracleasm/disks/DATAVOL1” is marked an ASM disk with the label “DATAVOL1”
Device “/dev/dm-9” is marked an ASM disk with the label “DATAVOL1”
Device “/dev/dm-11” is marked an ASM disk with the label “CRSVOL1”
Device “/dev/dm-10” is marked an ASM disk with the label “FRAVOL1”
Device “/dev/sdc” is marked an ASM disk with the label “DATAVOL1”
Device “/dev/sdd” is marked an ASM disk with the label “FRAVOL1”
Device “/dev/sde” is marked an ASM disk with the label “CRSVOL1”
Device “/dev/sdf” is marked an ASM disk with the label “DATAVOL1”
Device “/dev/sdg” is marked an ASM disk with the label “FRAVOL1”
Device “/dev/sdh” is marked an ASM disk with the label “CRSVOL1”
Device “/dev/sdi” is marked an ASM disk with the label “DATAVOL1”
Device “/dev/sdj” is marked an ASM disk with the label “FRAVOL1”
Device “/dev/sdk” is marked an ASM disk with the label “CRSVOL1”
Device “/dev/sdl” is marked an ASM disk with the label “DATAVOL1”
Device “/dev/sdm” is marked an ASM disk with the label “FRAVOL1”
Device “/dev/sdn” is marked an ASM disk with the label “CRSVOL1”
Device “/dev/sdo” is marked an ASM disk with the label “DATAVOL1”
Device “/dev/sdp” is marked an ASM disk with the label “FRAVOL1”
Device “/dev/sdq” is marked an ASM disk with the label “CRSVOL1”
Device “/dev/sdr” is marked an ASM disk with the label “DATAVOL1”
Device “/dev/sds” is marked an ASM disk with the label “FRAVOL1”
Device “/dev/sdt” is marked an ASM disk with the label “CRSVOL1”
Device “/dev/sdu” is marked an ASM disk with the label “DATAVOL1”
Device “/dev/sdv” is marked an ASM disk with the label “FRAVOL1”
Device “/dev/sdw” is marked an ASM disk with the label “CRSVOL1”
Device “/dev/sdx” is marked an ASM disk with the label “DATAVOL1”
Device “/dev/sdy” is marked an ASM disk with the label “FRAVOL1”
Device “/dev/sdz” is marked an ASM disk with the label “CRSVOL1”
Device “/dev/mapper/vpath2” is marked an ASM disk with the label “CRSVOL1”
Device “/dev/mapper/vpath1” is marked an ASM disk with the label “FRAVOL1”
Device “/dev/mapper/vpath0” is marked an ASM disk with the label “DATAVOL1”
[root@dhcppc1 rpms]#

2.

[root@dhcppc1 rpms]# blkid|grep sd.*oracleasm|while read a b;do echo -n $a$b” scsi_id=”;(echo $a|tr -d [:digit:]|tr -d [:]|cut -d”/” -f3|xargs -i scsi_id -g -s /block/{})done;
/dev/sdc:LABEL=”DATAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036b
/dev/sdd:LABEL=”FRAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036c
/dev/sde:LABEL=”CRSVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036d
/dev/sdf:LABEL=”DATAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036b
/dev/sdg:LABEL=”FRAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036c
/dev/sdh:LABEL=”CRSVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036d
/dev/sdi:LABEL=”DATAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036b
/dev/sdj:LABEL=”FRAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036c
/dev/sdk:LABEL=”CRSVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036d
/dev/sdl:LABEL=”DATAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036b
/dev/sdm:LABEL=”FRAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036c
/dev/sdn:LABEL=”CRSVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036d
/dev/sdo:LABEL=”DATAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036b
/dev/sdp:LABEL=”FRAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036c
/dev/sdq:LABEL=”CRSVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036d
/dev/sdr:LABEL=”DATAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036b
/dev/sds:LABEL=”FRAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036c
/dev/sdt:LABEL=”CRSVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036d
/dev/sdu:LABEL=”DATAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036b
/dev/sdv:LABEL=”FRAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036c
/dev/sdw:LABEL=”CRSVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036d
/dev/sdx:LABEL=”DATAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036b
/dev/sdy:LABEL=”FRAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036c
[root@dhcppc1 rpms]#

[root@dhcppc1 rpms]# multipath -ll
vpath2 (360050768019600fba80000000000036d) dm-11 IBM,2145
[size=10G][features=1 queue_if_no_path][hwhandler=0][rw]
\_ round-robin 0 [prio=200][active]
\_ 1:0:6:2 sdk 8:160  [active][ready]
\_ 1:0:7:2 sdn 8:208  [active][ready]
\_ 2:0:6:2 sdw 65:96  [active][ready]
\_ 2:0:7:2 sdz 65:144 [active][ready]
\_ round-robin 0 [prio=40][enabled]
\_ 1:0:4:2 sde 8:64   [active][ready]
\_ 1:0:5:2 sdh 8:112  [active][ready]
\_ 2:0:4:2 sdq 65:0   [active][ready]
\_ 2:0:5:2 sdt 65:48  [active][ready]
vpath1 (360050768019600fba80000000000036c) dm-10 IBM,2145
[size=500G][features=1 queue_if_no_path][hwhandler=0][rw]
\_ round-robin 0 [prio=200][active]
\_ 1:0:4:1 sdd 8:48   [active][ready]
\_ 1:0:5:1 sdg 8:96   [active][ready]
\_ 2:0:4:1 sdp 8:240  [active][ready]
\_ 2:0:5:1 sds 65:32  [active][ready]
\_ round-robin 0 [prio=40][enabled]
\_ 1:0:6:1 sdj 8:144  [active][ready]
\_ 1:0:7:1 sdm 8:192  [active][ready]
\_ 2:0:6:1 sdv 65:80  [active][ready]
\_ 2:0:7:1 sdy 65:128 [active][ready]
vpath0 (360050768019600fba80000000000036b) dm-9 IBM,2145
[size=500G][features=1 queue_if_no_path][hwhandler=0][rw]
\_ round-robin 0 [prio=200][active]
\_ 1:0:6:0 sdi 8:128  [active][ready]
\_ 1:0:7:0 sdl 8:176  [active][ready]
\_ 2:0:6:0 sdu 65:64  [active][ready]
\_ 2:0:7:0 sdx 65:112 [active][ready]
\_ round-robin 0 [prio=40][enabled]
\_ 1:0:4:0 sdc 8:32   [active][ready]
\_ 1:0:5:0 sdf 8:80   [active][ready]
\_ 2:0:4:0 sdo 8:224  [active][ready]
\_ 2:0:5:0 sdr 65:16  [active][ready]
[root@dhcppc1 rpms]#

[root@dhcppc1 rpms]# ls -lrt /dev/mapper/vpath*
brw-rw—- 1 root disk 253, 11 Jul  9 12:49 /dev/mapper/vpath2
brw-rw—- 1 root disk 253, 10 Jul  9 12:49 /dev/mapper/vpath1
brw-rw—- 1 root disk 253,  9 Jul  9 12:49 /dev/mapper/vpath0
[root@dhcppc1 rpms]#

[root@dhcppc1 rpms]# find /dev/oracleasm/disks -type b | xargs -i sh -c “echo {}; dd if={} count=100 2>/dev/null | strings”
/dev/oracleasm/disks/CRSVOL1
ORCLDISKCRSVOL1
CRS_0000
CRS_0000
/dev/oracleasm/disks/FRAVOL1
ORCLDISKFRAVOL1
FRA_0000
FRA_0000
/dev/oracleasm/disks/DATAVOL1
ORCLDISKDATAVOL1
RACDB_DATA_0000
RACDB_DATA
RACDB_DATA_0000
[root@dhcppc1 rpms]#

Hope it helps

SRI

How to identify the physical disk bound to the ASMLIB disk?

We will create ASM disks with oracleasm and we can list the ASM disks using the listdisks option.
While creating the ASM disk we will supply the physical disk path, as

[root@dhcppc1 rpms]# /etc/init.d/oracleasm createdisk ASK_DISK1 /dev/sdc1
Marking disk “ASK_DISK1” as an ASM disk:                   [  OK  ]
[root@dhcppc1 rpms]#

Once after creating the ASM disks, there is no direct command which actually lists the mapping between ASM disks and physical disks.

If you check the syntax,
[root@dhcppc1 rpms]# /etc/init.d/oracleasm -h
Usage: /etc/init.d/oracleasm {start|stop|restart|enable|disable|configure|createdisk|deletedisk|querydisk|listdisks|scandisks|status}
[root@dhcppc1 rpms]#

[root@dhcppc1 rpms]# /etc/init.d/oracleasm listdisks
CRSVOL1
DATAVOL1
FRAVOL1
[root@dhcppc1 rpms]# /etc/init.d/oracleasm querydisk
Usage: oracleasm-querydisk [-l <manager>] [-v] [-d|-p] <label>|<device> …
[root@dhcppc1 rpms]# /etc/init.d/oracleasm querydisk CRSVOL1
Disk “CRSVOL1” is a valid ASM disk
[root@dhcppc1 rpms]#

So there is no option from oracleasm executable to check how ASM_DISK1 is linked to /dev/sdc1.

We will try the SQL Commands ..

SQL> select name from v$asm_diskgroup;

NAME
——————————
CRS
FRA
RACDB_DATA

SQL> select name,path,label from v$asm_disk;

NAME            PATH                                LABEL
————— ———————————– —————
CRS_0000        /dev/oracleasm/disks/CRSVOL1
FRA_0000        /dev/oracleasm/disks/FRAVOL1
RACDB_DATA_0000 /dev/oracleasm/disks/DATAVOL1

SQL>

Then how do we find, Which Disks Are Handled by ASMLib Kernel Driver?

There are two ways to identify the physical disk bound to the ASMLIB disk

[root@dhcppc1 rpms]# find /dev -type b -exec ‘/etc/init.d/oracleasm’ ‘querydisk’ ‘{}’ ‘;’ 2>/dev/null | grep “is marked an ASM disk”
Device “/dev/oracleasm/disks/CRSVOL1” is marked an ASM disk with the label “CRSVOL1”
Device “/dev/oracleasm/disks/FRAVOL1” is marked an ASM disk with the label “FRAVOL1”
Device “/dev/oracleasm/disks/DATAVOL1” is marked an ASM disk with the label “DATAVOL1”
Device “/dev/dm-9” is marked an ASM disk with the label “DATAVOL1”
Device “/dev/dm-11” is marked an ASM disk with the label “CRSVOL1”
Device “/dev/dm-10” is marked an ASM disk with the label “FRAVOL1”
Device “/dev/sdc” is marked an ASM disk with the label “DATAVOL1”
Device “/dev/sdd” is marked an ASM disk with the label “FRAVOL1”
Device “/dev/sde” is marked an ASM disk with the label “CRSVOL1”
Device “/dev/sdf” is marked an ASM disk with the label “DATAVOL1”
Device “/dev/sdg” is marked an ASM disk with the label “FRAVOL1”
Device “/dev/sdh” is marked an ASM disk with the label “CRSVOL1”
Device “/dev/sdi” is marked an ASM disk with the label “DATAVOL1”
Device “/dev/sdj” is marked an ASM disk with the label “FRAVOL1”
Device “/dev/sdk” is marked an ASM disk with the label “CRSVOL1”
Device “/dev/sdl” is marked an ASM disk with the label “DATAVOL1”
Device “/dev/sdm” is marked an ASM disk with the label “FRAVOL1”
Device “/dev/sdn” is marked an ASM disk with the label “CRSVOL1”
Device “/dev/sdo” is marked an ASM disk with the label “DATAVOL1”
Device “/dev/sdp” is marked an ASM disk with the label “FRAVOL1”
Device “/dev/sdq” is marked an ASM disk with the label “CRSVOL1”
Device “/dev/sdr” is marked an ASM disk with the label “DATAVOL1”
Device “/dev/sds” is marked an ASM disk with the label “FRAVOL1”
Device “/dev/sdt” is marked an ASM disk with the label “CRSVOL1”
Device “/dev/sdu” is marked an ASM disk with the label “DATAVOL1”
Device “/dev/sdv” is marked an ASM disk with the label “FRAVOL1”
Device “/dev/sdw” is marked an ASM disk with the label “CRSVOL1”
Device “/dev/sdx” is marked an ASM disk with the label “DATAVOL1”
Device “/dev/sdy” is marked an ASM disk with the label “FRAVOL1”
Device “/dev/sdz” is marked an ASM disk with the label “CRSVOL1”
Device “/dev/mapper/vpath2” is marked an ASM disk with the label “CRSVOL1”
Device “/dev/mapper/vpath1” is marked an ASM disk with the label “FRAVOL1”
Device “/dev/mapper/vpath0” is marked an ASM disk with the label “DATAVOL1″
[root@dhcppc1 rpms]#

[root@dhcppc1 rpms]# blkid|grep sd.*oracleasm|while read a b;do echo -n $a$b” scsi_id=”;(echo $a|tr -d [:digit:]|tr -d [:]|cut -d”/” -f3|xargs -i scsi_id -g -s /block/{})done;
/dev/sdc:LABEL=”DATAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036b
/dev/sdd:LABEL=”FRAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036c
/dev/sde:LABEL=”CRSVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036d
/dev/sdf:LABEL=”DATAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036b
/dev/sdg:LABEL=”FRAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036c
/dev/sdh:LABEL=”CRSVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036d
/dev/sdi:LABEL=”DATAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036b
/dev/sdj:LABEL=”FRAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036c
/dev/sdk:LABEL=”CRSVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036d
/dev/sdl:LABEL=”DATAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036b
/dev/sdm:LABEL=”FRAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036c
/dev/sdn:LABEL=”CRSVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036d
/dev/sdo:LABEL=”DATAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036b
/dev/sdp:LABEL=”FRAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036c
/dev/sdq:LABEL=”CRSVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036d
/dev/sdr:LABEL=”DATAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036b
/dev/sds:LABEL=”FRAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036c
/dev/sdt:LABEL=”CRSVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036d
/dev/sdu:LABEL=”DATAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036b
/dev/sdv:LABEL=”FRAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036c
/dev/sdw:LABEL=”CRSVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036d
/dev/sdx:LABEL=”DATAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036b
/dev/sdy:LABEL=”FRAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036c
[root@dhcppc1 rpms]#

[root@dhcppc1 rpms]# multipath -ll
vpath2 (360050768019600fba80000000000036d) dm-11 IBM,2145
[size=10G][features=1 queue_if_no_path][hwhandler=0][rw]
\_ round-robin 0 [prio=200][active]
\_ 1:0:6:2 sdk 8:160  [active][ready]
\_ 1:0:7:2 sdn 8:208  [active][ready]
\_ 2:0:6:2 sdw 65:96  [active][ready]
\_ 2:0:7:2 sdz 65:144 [active][ready]
\_ round-robin 0 [prio=40][enabled]
\_ 1:0:4:2 sde 8:64   [active][ready]
\_ 1:0:5:2 sdh 8:112  [active][ready]
\_ 2:0:4:2 sdq 65:0   [active][ready]
\_ 2:0:5:2 sdt 65:48  [active][ready]
vpath1 (360050768019600fba80000000000036c) dm-10 IBM,2145
[size=500G][features=1 queue_if_no_path][hwhandler=0][rw]
\_ round-robin 0 [prio=200][active]
\_ 1:0:4:1 sdd 8:48   [active][ready]
\_ 1:0:5:1 sdg 8:96   [active][ready]
\_ 2:0:4:1 sdp 8:240  [active][ready]
\_ 2:0:5:1 sds 65:32  [active][ready]
\_ round-robin 0 [prio=40][enabled]
\_ 1:0:6:1 sdj 8:144  [active][ready]
\_ 1:0:7:1 sdm 8:192  [active][ready]
\_ 2:0:6:1 sdv 65:80  [active][ready]
\_ 2:0:7:1 sdy 65:128 [active][ready]
vpath0 (360050768019600fba80000000000036b) dm-9 IBM,2145
[size=500G][features=1 queue_if_no_path][hwhandler=0][rw]
\_ round-robin 0 [prio=200][active]
\_ 1:0:6:0 sdi 8:128  [active][ready]
\_ 1:0:7:0 sdl 8:176  [active][ready]
\_ 2:0:6:0 sdu 65:64  [active][ready]
\_ 2:0:7:0 sdx 65:112 [active][ready]
\_ round-robin 0 [prio=40][enabled]
\_ 1:0:4:0 sdc 8:32   [active][ready]
\_ 1:0:5:0 sdf 8:80   [active][ready]
\_ 2:0:4:0 sdo 8:224  [active][ready]
\_ 2:0:5:0 sdr 65:16  [active][ready]
[root@dhcppc1 rpms]#

[root@orrcdbtsl01 ~]# ls -lrt /dev/mapper/vpath*
brw-rw—- 1 root disk 253, 11 Jul  9 12:49 /dev/mapper/vpath2
brw-rw—- 1 root disk 253, 10 Jul  9 12:49 /dev/mapper/vpath1
brw-rw—- 1 root disk 253,  9 Jul  9 12:49 /dev/mapper/vpath0
[root@orrcdbtsl01 ~]#

[root@orrcdbtsl01 ~]# find /dev/oracleasm/disks -type b | xargs -i sh -c “echo {}; dd if={} count=100 2>/dev/null | strings”
/dev/oracleasm/disks/CRSVOL1
ORCLDISKCRSVOL1
CRS_0000
CRS_0000
/dev/oracleasm/disks/FRAVOL1
ORCLDISKFRAVOL1
FRA_0000
FRA_0000
/dev/oracleasm/disks/DATAVOL1
ORCLDISKDATAVOL1
RACDB_DATA_0000
RACDB_DATA
RACDB_DATA_0000
[root@orrcdbtsl01 ~]#

Posted in 11gR2 RAC, ASM | Tagged: , , , , , , , , | Leave a Comment »

Not able to start 10gR2 RAC Database Running on 11gR2 Grid infra

Posted by Srikrishna Murthy Annam on September 1, 2010

We have 2 node 10gRAC database running on 11gR2 grid infra structure.
When the database was created, both the instances were running good.
Recently one instance was down due to some reason and we are not able to start the instance successfully.

When we are trying to start the instance , it says , it cant identify the control file. But the other instance is able to start and stop successfully.

When we start the database , we get the following error …

ORA-00205: error in identifying control file, check alert log for more info

SQL> startup
ORACLE instance started.Total System Global Area 1610612736 bytes
Fixed Size 2084296 bytes
Variable Size 369099320 bytes
Database Buffers 1224736768 bytes
Redo Buffers 14692352 bytes
ORA-00205: error in identifying control file, check alert log for more info

Alert Log content :
ORA-00204: error in reading (block 1, # blocks 1) of control file
ORA-00202: control file: ‘+RACDB_DATA/rac10g/control01.ctl’
ORA-15081: failed to submit an I/O operation to a disk
Mon Aug 30 07:40:52 2010
ORA-205 signalled during: ALTER DATABASE MOUNT…
Mon Aug 30 07:40:55 2010
Errors in file /u01/apps/oracle/admin/rac10g/bdump/rac10g2_mmon_32634.trc:
ORA-00600: internal error code, arguments: [kmgs_parameter_update_timeout_1], [15081], [], [], [], [], [], []
ORA-15081: failed to submit an I/O operation to a disk
Mon Aug 30 07:40:56 2010
Trace dumping is performing id=[cdmp_20100830074056]
Mon Aug 30 07:41:59 2010
Restarting dead background process MMON
MMON started with pid=16, OS id=329

Other Details :
SQL> select GROUP_NUMBER,name,state from v$asm_diskgroup;

GROUP_NUMBER NAME STATE
———— —————————— ———–
1 CRS MOUNTED
2 FRA MOUNTED
3 RACDB_DATA MOUNTED

SQL> select group_number,instance_name ,db_name , status from v$asm_client;

GROUP_NUMBER INSTANCE_NAME DB_NAME STATUS
———— ——————– ——————– ———-
1 +ASM2 +ASM CONNECTED
3 +ASM2 +ASM CONNECTED

===> After starting the rac10g2 ( instance on rac02) with nomount.(the problematic instance) :
SQL> select group_number,instance_name ,db_name , status from v$asm_client;

GROUP_NUMBER INSTANCE_NAME DB_NAME STATUS
———— ——————– ——————– ———-
1 +ASM2 +ASM CONNECTED
3 +ASM2 +ASM CONNECTED
3 rac10g2 rac10g CONNECTED

SQL>

SQL> sho parameter control

NAME TYPE VALUE
———————————— ———– ——————————
control_file_record_keep_time integer 7
control_files string +RACDB_DATA/rac10g/control01.c
tl, +RACDB_DATA/rac10g/control
02.ctl, +RACDB_DATA/rac10g/con
trol03.ctl

Troubleshooting
From the database owner ( oracle ) on all the nodes

[oracle@rac01 ~]$ hostname
rac01.example.com
[oracle@rac01 ~]$ whoami
oracle
[oracle@rac01 ~]$ ls -l $ORACLE_HOME/bin/oracle
-rwsr-s–x 1 oracle dba 112468374 Aug 17 10:47 /u01/apps/oracle/product/10.2.0/db_1/bin/oracle
[oracle@rac01 ~]$ echo $ORACLE_HOME
/u01/apps/oracle/product/10.2.0/db_1
[oracle@rac01 ~]$
[oracle@rac01 ~]$ whoami
oracle
[oracle@rac01 ~]$ id
uid=1659(oracle) gid=501(dba) groups=501(dba),503(oper),552(admin2),1000(oinstall),1201(asmdba)
[oracle@rac01 ~]$

[oracle@rac02 ~]$ hostname
rac02.example.com
[oracle@rac02 ~]$ whoami
oracle
[oracle@rac02 ~]$ ls -l $ORACLE_HOME/bin/oracle
-rwsr-s–x 1 oracle dba 112468374 Aug 17 10:47 /u01/apps/oracle/product/10.2.0/db_1/bin/oracle
[oracle@rac02 ~]$ echo $ORACLE_HOME
/u01/apps/oracle/product/10.2.0/db_1
[oracle@rac02 ~]$
[oracle@rac02 ~]$ whoami
oracle
[oracle@rac02 ~]$ id
uid=1659(oracle) gid=501(dba) groups=501(dba),552(admin2),1000(oinstall),1201(asmdba),1301(oper)
[oracle@rac02 ~]$

From the Grid owner ( grid ) on all the nodes

[grid@rac01 grid]$ hostname
rac01.example.com
[grid@rac01 grid]$
[grid@rac01 grid]$ whoami
grid
[grid@rac01 grid]$ echo $ORACLE_HOME;echo $ORACLE_SID
/o001/home/11.2.0/grid
+ASM1
[grid@rac01 grid]$ ls -l $ORACLE_HOME/bin/oracle
-rwsrws–x 1 grid dba 184286251 Apr 5 15:51 /o001/home/11.2.0/grid/bin/oracle*
[grid@rac01 grid]$ whoami
grid
[grid@rac01 grid]$ id
uid=1100(grid) gid=501(dba) groups=501(dba),1200(asmadmin),1201(asmdba),1202(asmoper)
[grid@rac01 grid]$

[grid@rac02 grid]$ hostname
rac02.example.com
[grid@rac02 grid]$ whoami
grid
[grid@rac02 grid]$ echo $ORACLE_HOME;echo $ORACLE_SID
/o001/home/11.2.0/grid
+ASM2
[grid@rac02 grid]$ ls -l $ORACLE_HOME/bin/oracle
-rwsrws–x 1 grid dba 184286251 Apr 5 15:54 /o001/home/11.2.0/grid/bin/oracle*
[grid@rac02 grid]$ whoami
grid
[grid@rac02 grid]$ id
uid=1100(grid) gid=501(dba) groups=501(dba),1200(asmadmin),1201(asmdba),1202(asmoper)
[grid@rac02 grid]$

Then we changed the group ownership of Oracle executable to OS ASM group using below command after logging as ROOT user :

cd /u01/apps/oracle/product/10.2.0/db_1/bin
chgrp asmadmin oracle
chmod 6751 oracle
ls -l oracle

“This allows the database instance to access the ASM disks without having to make the database software owner a member of the OS ASM group.”

Then re-login newly as ORACLE user and then started ORACLE Database instance.

[oracle@rac01 bin]$ srvctl status database -d rac10g
Instance rac10g1 is running on node rac01
Instance rac10g2 is running on node rac02
[oracle@rac01 bin]$

Hope it helps
SRI

<div style=”border: 1px solid; background: #cccccc none repeat scroll 10px 50%; overflow: auto; margin: 20px; padding: .5em;”>

Posted in 11gR2 RAC, ASM | Tagged: , , , , | Leave a Comment »

RMAN COLD BACKUP FOR RAC , HOW ?

Posted by Srikrishna Murthy Annam on August 26, 2010

What is meant by COLD BACKUP? Is it possible to take COLD BACKUP with RMAN?

In the normal backup and recovery terms , COLD BACKUP is the backup taken when the database is completely down. Then how do we connect to database with RMAN when the database is down?
The RMAN differentiates between “inconsistent” and “consistent” backups on the basis of whether the database is OPEN or not during the Backup. It will not use the terms “COLD BACKUP” and “HOT BACKUP”.

For consistance RMAN RAC backup :

  1. Shutdown all the instances in a cluster
  2. connect to any one node and execute the following script

$rman target / nocatalog
RMAN>
run{
startup mount;
allocate channel backup_disk1 type disk format ‘+FRA’;
backup full database;
shutdown immediate;
release channel backup_disk1;
}

Now we have the RMAN cold backup. Does it include online redo logs. How do we restore the RMAN backup taken earlier. Can we directly restore and open the database.

No.

RMAN will not backup online redologs. So when you restore the database from the backup taken above , you cant directly open the database as you dont have the online redologs. You have to open the database with reset logs.

This article is an example to backup RAC database with ASM. When the RAC with ASM Storage provides the high availability , why should we think of cold backup !!!!!!. This articles is only for concepts purpose and the real time RAC backups with RMAN  are completely different.

Hope it helps ….

–SRI

Posted in 11gR2 RAC, ASM, RMAN | Tagged: , , , | 6 Comments »

 
%d bloggers like this: