Manzoor's Blog - Stuffs on Oracle Database Administration

Tuesday, January 27, 2015

Recovering Physical Standby using Incremental backup of Primary

Recovering Physical Standby using Incremental backup of Primary
===============================================================

Scenario :- Physical standby went out of sync from the primary as some of the archive logs are missing,
even those archivelog is deleted in the primary as well and no backup is available. To overcome this
we can take the incremental backup from primary and apply to the standby to keep it is in sync.

Primary DB

SQL> select current_scn from V$database;

CURRENT_SCN
-----------
2923116

SQL> select max(sequence#) from V$archived_log where applied = 'YES' and dest_id = 2;

MAX(SEQUENCE#)
--------------
91

Standby DB
----------

SQL> select current_scn from V$database;

CURRENT_SCN
-----------
2921713

SQL> select process, STATUS, sequence# from V$managed_standby where process like '%MRP%';

PROCESS STATUS SEQUENCE#
--------- ------------ ----------
MRP0 WAIT_FOR_GAP 92

SQL> SELECT * FROM v$ARCHIVE_GAP;

THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
1 92 113

--

As per the above we could see that there is a gap between 93 and 113 sequence which is missing on the standby site,
the current scn in standby site is 2921713, around 20 archive logs are missing also it is not availble in backup
in the primary site. Lets take the incremental backup from the primary site.

RMAN> run
{
allocate channel a1 device type disk format '/oracle/scn_incre_bkp_%U_%T.bkp';
backup incremental from scn 2921713 database;
}

using target database control file instead of recovery catalog
allocated channel: a1
channel a1: SID=34 device type=DISK

Starting backup at 28-JAN-15

backup will be obsolete on date 04-FEB-15
archived logs will not be kept or backed up
channel a1: starting full datafile backup set
channel a1: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/oradata/source/undotbs01.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/source/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/source/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/source/test_tbls_01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/source/users01.dbf
channel a1: starting piece 1 at 28-JAN-15
channel a1: finished piece 1 at 28-JAN-15
piece handle=/oracle/scn_incre_bkp_13ptrh7n_1_1_20150128.bkp tag=TAG20150128T102454 comment=NONE
channel a1: backup set complete, elapsed time: 00:02:05

backup will be obsolete on date 04-FEB-15
archived logs will not be kept or backed up
channel a1: starting full datafile backup set
channel a1: specifying datafile(s) in backup set
including current control file in backup set
channel a1: starting piece 1 at 28-JAN-15
channel a1: finished piece 1 at 28-JAN-15
piece handle=/oracle/scn_incre_bkp_14ptrhbl_1_1_20150128.bkp tag=TAG20150128T102454 comment=NONE
channel a1: backup set complete, elapsed time: 00:00:01
Finished backup at 28-JAN-15
released channel: a1

-- Take the current controlfile trace for standby.

SQL> alter database create standby controlfile as '/oracle/for_standby_ctl.ctl';

Database altered.

--

scp the controlfile and the backup piece to the standby location.

[oracle@rhel11gr2rac1 oracle]$ scp *.bkp oracle@standalone2.manzoor.com:/u01/app/oracle/bkp/
oracle@standalone2.manzoor.com's password:
scn_incre_bkp_13ptrh7n_1_1_20150128.bkp 100% 2552KB 2.5MB/s 00:00
scn_incre_bkp_14ptrhbl_1_1_20150128.bkp 100% 9792KB 9.6MB/s 00:01

[oracle@rhel11gr2rac1 oracle]$ scp for_standby_ctl.ctl oracle@standalone2.manzoor.com:/u01/app/oracle/bkp/
oracle@standalone2.manzoor.com's password:
for_standby_ctl.ctl 100% 9744KB 9.5MB/s 00:01
[oracle@rhel11gr2rac1 oracle]$

On standby, stop the MRP process , shut down db and start with the controlfile which been copied from the primary site.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> show parameter control_files;

NAME TYPE VALUE
------------------------------------ ----------- -----------------------------------------------------------------------------------------------------
control_files string /u01/app/oracle/oradata/source/control01.ctl, /u01/app/oracle/fast_recovery_area/source/control02.ctl

SQL> shut immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

-- Overwrite the old controlfiles with the new one.

[oracle@standalone2 bkp]$ cp for_standby_ctl.ctl /u01/app/oracle/oradata/source/control01.ctl
[oracle@standalone2 bkp]$ cp for_standby_ctl.ctl /u01/app/oracle/fast_recovery_area/source/control02.ctl

[oracle@standalone2 bkp]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jan 28 03:04:45 2015

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

connected to target database (not started)

RMAN> startup mount;

Oracle instance started
database mounted

Total System Global Area 313159680 bytes

Fixed Size 2227944 bytes
Variable Size 226492696 bytes
Database Buffers 79691776 bytes
Redo Buffers 4747264 bytes

-- catalog the backup piece

RMAN> catalog start with '/u01/app/oracle/bkp/scn';

Starting implicit crosscheck backup at 28-JAN-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=28 device type=DISK
Crosschecked 17 objects
Finished implicit crosscheck backup at 28-JAN-15

Starting implicit crosscheck copy at 28-JAN-15
using channel ORA_DISK_1
Crosschecked 4 objects
Finished implicit crosscheck copy at 28-JAN-15

searching for all files in the recovery area
cataloging files... files cataloged

searching for all files that match the pattern /u01/app/oracle/bkp/scn

List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/bkp/scn_incre_bkp_14ptrhbl_1_1_20150128.bkp
File Name: /u01/app/oracle/bkp/scn_incre_bkp_13ptrh7n_1_1_20150128.bkp

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/bkp/scn_incre_bkp_14ptrhbl_1_1_20150128.bkp
File Name: /u01/app/oracle/bkp/scn_incre_bkp_13ptrh7n_1_1_20150128.bkp

-- Start the recover.

RMAN> recover database;

Starting recover at 28-JAN-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/app/oracle/oradata/source/system01.dbf
destination for restore of datafile 00002: /u01/app/oracle/oradata/source/sysaux01.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/source/undotbs01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/source/users01.dbf
destination for restore of datafile 00005: /u01/app/oracle/oradata/source/test_tbls_01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/bkp/scn_incre_bkp_13ptrh7n_1_1_20150128.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/bkp/scn_incre_bkp_13ptrh7n_1_1_20150128.bkp tag=TAG20150128T102454
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04

starting media recovery

archived log for thread 1 with sequence 116 is already on disk as file /u01/app/oracle/archive/1_116_868359284.dbf
archived log file name=/u01/app/oracle/archive/1_116_868359284.dbf thread=1 sequence=116
unable to find archived log
archived log thread=1 sequence=117
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/28/2015 03:07:01
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 117 and starting SCN of 2928165

-- Now the restore is completed.

-- Lets check the scn now.

SQL> select current_scn from V$database;

CURRENT_SCN
-----------
2928164

-- Now start the MRP process, both primary and standby are in sync now.

Friday, August 8, 2014

Using 11gr2 Clusterware for Providing HA for Single Instance Database

We can use the Oracle cluster ware for providing the High availability instead of using the
third party cluster-ware.

In this practical session we can see how we are use the 11gr2 cluster ware can be used
to to provide the HA for a single instance database.

OS - OEL 6.5
Cluster-ware - 11.2.0.3

Below is the status of the fresh installed clusterware.

[oracle@oelrac1 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATADG.dg
ONLINE ONLINE oelrac1
ONLINE ONLINE oelrac2
ora.FRA.dg
ONLINE ONLINE oelrac1
ONLINE ONLINE oelrac2
ora.LISTENER.lsnr
ONLINE ONLINE oelrac1
ONLINE ONLINE oelrac2
ora.OCR_MIRROR.dg
ONLINE ONLINE oelrac1
ONLINE ONLINE oelrac2
ora.OCR_VOTING.dg
ONLINE ONLINE oelrac1
ONLINE ONLINE oelrac2
ora.asm
ONLINE ONLINE oelrac1 Started
ONLINE ONLINE oelrac2 Started
ora.gsd
OFFLINE OFFLINE oelrac1
OFFLINE OFFLINE oelrac2
ora.net1.network
ONLINE ONLINE oelrac1
ONLINE ONLINE oelrac2
ora.ons
ONLINE ONLINE oelrac1
ONLINE ONLINE oelrac2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE oelrac2
ora.cvu
1 ONLINE ONLINE oelrac2
ora.oc4j
1 ONLINE ONLINE oelrac2
ora.oelrac1.vip
1 ONLINE ONLINE oelrac1
ora.oelrac2.vip
1 ONLINE ONLINE oelrac2
ora.scan1.vip
1 ONLINE ONLINE oelrac2

1) Install Standalone oracle home on both the nodes.
2) Create the database using dbca named testdb

--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------

ora.testdb.db
1 ONLINE ONLINE oelrac1 Open

3) Take the configuration properties of the database and remove it from the cluster resource.

[oracle@oelrac1 trace]$ crsctl stat res ora.testdb.db -p > /home/oracle/test_db_properties.txt

[oracle@oelrac1 trace]$ cat /home/oracle/test_db_properties.txt
NAME=ora.testdb.db
TYPE=ora.database.type
ACL=owner:oracle:rwx,pgrp:oinstall:rwx,other::r--
ACTION_FAILURE_TEMPLATE=
ACTION_SCRIPT=
ACTIVE_PLACEMENT=1
AGENT_FILENAME=%CRS_HOME%/bin/oraagent%CRS_EXE_SUFFIX%
AUTO_START=restore
CARDINALITY=1
CHECK_INTERVAL=1
CHECK_TIMEOUT=30
CLUSTER_DATABASE=false
DATABASE_TYPE=SINGLE
DB_UNIQUE_NAME=testdb
DEFAULT_TEMPLATE=PROPERTY(RESOURCE_CLASS=database) PROPERTY(DB_UNIQUE_NAME= CONCAT(PARSE(%NAME%, ., 2), %USR_ORA_DOMAIN%, .)) ELEMENT(INSTANCE_NAME= %GEN_USR_ORA_INST_NAME%) ELEMENT(DATABASE_TYPE= %DATABASE_TYPE%)
DEGREE=1
DESCRIPTION=Oracle Database resource
ENABLED=1
FAILOVER_DELAY=0
FAILURE_INTERVAL=60
FAILURE_THRESHOLD=1
GEN_AUDIT_FILE_DEST=/u01/app/oracle/admin/testdb/adump
GEN_START_OPTIONS=
GEN_START_OPTIONS@SERVERNAME(oelrac1)=open
GEN_USR_ORA_INST_NAME=
GEN_USR_ORA_INST_NAME@SERVERNAME(oelrac1)=testdb
HOSTING_MEMBERS=
INSTANCE_FAILOVER=1
LOAD=1
LOGGING_LEVEL=1
MANAGEMENT_POLICY=AUTOMATIC
NLS_LANG=
NOT_RESTARTING_TEMPLATE=
OFFLINE_CHECK_INTERVAL=0
ONLINE_RELOCATION_TIMEOUT=0
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db
ORACLE_HOME_OLD=
PLACEMENT=restricted
PROFILE_CHANGE_TEMPLATE=
RESTART_ATTEMPTS=2
ROLE=PRIMARY
SCRIPT_TIMEOUT=60
SERVER_POOLS=ora.testdb
SPFILE=+DATADG/testdb/spfiletestdb.ora
START_DEPENDENCIES=hard(ora.DATADG.dg) weak(type:ora.listener.type,uniform:ora.ons) pullup(ora.DATADG.dg)
START_TIMEOUT=600
STATE_CHANGE_TEMPLATE=
STOP_DEPENDENCIES=hard(intermediate:ora.asm,shutdown:ora.DATADG.dg)
STOP_TIMEOUT=600
TYPE_VERSION=3.2
UPTIME_THRESHOLD=1h
USR_ORA_DB_NAME=testdb
USR_ORA_DOMAIN=
USR_ORA_ENV=
USR_ORA_FLAGS=
USR_ORA_INST_NAME=testdb
USR_ORA_OPEN_MODE=open
USR_ORA_OPI=false
USR_ORA_STOP_MODE=immediate
VERSION=11.2.0.3.0

[oracle@oelrac1 dbs]$ srvctl status database -d testdb
Instance testdb is running on node oelrac1

[oracle@oelrac1 dbs]$ srvctl stop database -d testdb

[oracle@oelrac1 dbs]$ srvctl remove database -d testdb
Remove the database testdb? (y/[n]) y

Now the db resource will not get listed in crs resource. (crsctl stat res -t).

4) Copy the pfile and password file to the 2nd node. Create adump directory in 2nd node.

[oracle@oelrac1 dbs]$ scp inittestdb.ora oracle@oelrac2:/u01/app/oracle/product/11.2.0/db/dbs/
inittestdb.ora 100% 41 0.0KB/s 00:00

[oracle@oelrac1 dbs]$ scp orapwtestdb oracle@oelrac2:/u01/app/oracle/product/11.2.0/db/dbs/
orapwtestdb

[oracle@oelrac2 ~]$ mkdir -p /u01/app/oracle/admin/testdb/adump

5) Test the database startup in node 2.

[oracle@oelrac2 ~]$ export ORACLE_SID=testdb
[oracle@oelrac2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Aug 9 10:35:59 2014

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 626327552 bytes
Fixed Size 2230952 bytes
Variable Size 381683032 bytes
Database Buffers 239075328 bytes
Redo Buffers 3338240 bytes
Database mounted.
Database opened.

-- Database has opened in node 2 without issues.

Register the database with local listeners, since the local listeners are listening on the
vip there is no need to setup a seperate vip for this database. We can use the existing
vip and scan vips for this setup.

[oracle@oelrac2 ~]$ srvctl config scan
SCAN name: oelrac-scan.manzoor.com, Network: 1/192.168.0.0/255.255.255.0/eth0
SCAN VIP name: scan1, IP: /oelrac-scan.manzoor.com/192.168.0.44

[oracle@oelrac2 ~]$ srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521

[oracle@oelrac2 ~]$ srvctl config vip -n oelrac1
VIP exists: /oelrac1-vip/192.168.0.42/192.168.0.0/255.255.255.0/eth0, hosting node oelrac1

[oracle@oelrac2 ~]$ srvctl config vip -n oelrac2
VIP exists: /oelrac2-vip/192.168.0.43/192.168.0.0/255.255.255.0/eth0, hosting node oelrac2

Enter the below on the tnsnames.ora file

On Node 1:-

TESTDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oelrac-scan.manzoor.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = testdb)
)
)

LISTENER_TESTDB =
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.42)(PORT=1521))))

On Node 2:-

TESTDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oelrac-scan.manzoor.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = testdb)
)
)

LISTENER_TESTDB =
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.43)(PORT=1521))))

5) Set the local_listener parameter in the db to listener on the above configuration.

SQL> alter system set local_listener = 'LISTENER_TESTDB' scope=both ;

System altered.

SQL> alter system set remote_listener = 'oelrac-scan:1521' scope=both;

System altered.

6) Check the dummy connection.

[oracle@oelrac2 admin]$ sqlplus -L a/a@testdb

SQL*Plus: Release 11.2.0.3.0 Production on Sat Aug 9 10:51:24 2014

Copyright (c) 1982, 2011, Oracle. All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

SP2-0751: Unable to connect to Oracle. Exiting SQL*Plus

-- Dummy connection working fine.

-- Now shutdown the node 2 and start the db in node 1 and check the connections.

[oracle@oelrac2 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Aug 9 10:52:51 2014

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

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

[oracle@oelrac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Aug 9 10:54:46 2014

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 626327552 bytes
Fixed Size 2230952 bytes
Variable Size 381683032 bytes
Database Buffers 239075328 bytes
Redo Buffers 3338240 bytes
Database mounted.
Database opened.

[oracle@oelrac1 ~]$ sqlplus -L a/a@testdb

SQL*Plus: Release 11.2.0.3.0 Production on Sat Aug 9 10:56:21 2014

Copyright (c) 1982, 2011, Oracle. All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

SP2-0751: Unable to connect to Oracle. Exiting SQL*Plus

-- So far manual cold failover has been succeeded with out any issues. Now lets create an action script
to perform this action by clusterware.

11gr2 clusterware action script requires 4 inputs (start, stop, check & clean). Lets create the action
script.

Create the below directories in both the nodes and change the directory owner to oracle.

[root@oelrac1 11.2.0]# mkdir ha_scripts
[root@oelrac1 11.2.0]# chown oracle:oinstall ha_scripts

[root@oelrac2 11.2.0]# mkdir ha_scripts
[root@oelrac2 11.2.0]# chown oracle:oinstall ha_scripts

Create the below scripts as oracle user.

vi testdb_action_script.sh

#!/bin/sh
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db
export ORACLE_SID=testdb

case $1 in
'start')
$ORACLE_HOME/bin/sqlplus /nolog <conn / as sysdba
startup;
exit;
EOF
RET=0
;;

'stop')
$ORACLE_HOME/bin/sqlplus /nolog <conn / as sysdba
shut immediate;
exit;
EOF
RET=0
;;

'clean')
$ORACLE_HOME/bin/sqlplus /nolog <conn / as sysdba
shut abort;
exit;
EOF
RET=0
;;

'check')
os=`ps -ef | grep smon | grep $ORACLE_SID | wc -l`
if [ $os -eq 0 ]; then
RET=1
else
RET=0
fi
;;

'*')
RET=0
;;
esac

if [ $RET -eq 0 ]; then
echo "Return value is zero"
exit 0
else
echo "Return value is one"
exit 1
fi

Test the script manually.

[oracle@oelrac1 ha_scripts]$ ./testdb_action_script.sh start

SQL*Plus: Release 11.2.0.3.0 Production on Sat Aug 9 11:36:12 2014

Copyright (c) 1982, 2011, Oracle. All rights reserved.

SQL> Connected to an idle instance.
SQL> ORACLE instance started.

Total System Global Area 626327552 bytes
Fixed Size 2230952 bytes
Variable Size 381683032 bytes
Database Buffers 239075328 bytes
Redo Buffers 3338240 bytes
Database mounted.
Database opened.
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

[oracle@oelrac1 ha_scripts]$ ./testdb_action_script.sh stop

SQL*Plus: Release 11.2.0.3.0 Production on Sat Aug 9 11:38:05 2014

Copyright (c) 1982, 2011, Oracle. All rights reserved.

SQL> Connected.
SQL> Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

--- Script is working fine. Copy the script to the other node and test.

[oracle@oelrac2 ha_scripts]$ ./testdb_action_script.sh start

SQL*Plus: Release 11.2.0.3.0 Production on Sat Aug 9 11:44:16 2014

Copyright (c) 1982, 2011, Oracle. All rights reserved.

SQL> Connected to an idle instance.
SQL> ORACLE instance started.

Total System Global Area 626327552 bytes
Fixed Size 2230952 bytes
Variable Size 381683032 bytes
Database Buffers 239075328 bytes
Redo Buffers 3338240 bytes
Database mounted.
Database opened.
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Return value is zero
[oracle@oelrac2 ha_scripts]$ ./testdb_action_script.sh check
Return value is zero
[oracle@oelrac2 ha_scripts]$ ./testdb_action_script.sh stop

SQL*Plus: Release 11.2.0.3.0 Production on Sat Aug 9 11:46:24 2014

Copyright (c) 1982, 2011, Oracle. All rights reserved.

SQL> Connected.
SQL> Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Return value is zero

-- Now we need a new cluster resource which will manage the failover automatically, Since we
are creating as cluster_resource type we can remove all the properties which are related to
database resource. Below is the modified properties.

NAME=TESTDB.db
TYPE=cluster_resource
ACL=owner:oracle:rwx,pgrp:oinstall:rwx,other::r--
ACTION_SCRIPT=/u01/grid/11.2.0/ha_scripts/testdb_action_script.sh
ACTIVE_PLACEMENT=0
AUTO_START=restore
CARDINALITY=1
CHECK_INTERVAL=10
DEGREE=1
DESCRIPTION=Oracle Database resource
ENABLED=1
LOGGING_LEVEL=1
PLACEMENT=restricted
RESTART_ATTEMPTS=1
SCRIPT_TIMEOUT=60
START_DEPENDENCIES=hard(ora.DATADG.dg) weak(type:ora.listener.type,uniform:ora.ons) pullup(ora.DATADG.dg)
START_TIMEOUT=600
STOP_DEPENDENCIES=hard(intermediate:ora.asm,shutdown:ora.DATADG.dg)
STOP_TIMEOUT=600
UPTIME_THRESHOLD=1h
HOSTING_MEMBERS=oelrac1 oelrac2

copy the contents to one text file eg. myresource.txt and add the cluster resource.

[oracle@oelrac2 ha_scripts]$ crsctl add res TESTDB.db -type cluster_resource -file myresource.txt

[oracle@oelrac2 ha_scripts]$ crsctl stat res TESTDB.db
NAME=TESTDB.db
TYPE=cluster_resource
TARGET=OFFLINE
STATE=OFFLINE

[oracle@oelrac2 ha_scripts]$ crsctl start res TESTDB.db
CRS-2672: Attempting to start 'TESTDB.db' on 'oelrac1'
CRS-2676: Start of 'TESTDB.db' on 'oelrac1' succeeded

Cluster Resources
--------------------------------------------------------------------------------
TESTDB.db
1 ONLINE ONLINE oelrac1

[oracle@oelrac1 ~]$ crsctl stop res TESTDB.db
CRS-2673: Attempting to stop 'TESTDB.db' on 'oelrac1'
CRS-2677: Stop of 'TESTDB.db' on 'oelrac1' succeeded

[oracle@oelrac1 ~]$ crsctl start res TESTDB.db
CRS-2672: Attempting to start 'TESTDB.db' on 'oelrac1'
CRS-2676: Start of 'TESTDB.db' on 'oelrac1' succeeded

-- Now relocate the res to another server.

[oracle@oelrac1 ~]$ crsctl relocate res TESTDB.db -s oelrac1 -n oelrac2 -f
CRS-2673: Attempting to stop 'TESTDB.db' on 'oelrac1'
CRS-2677: Stop of 'TESTDB.db' on 'oelrac1' succeeded
CRS-2672: Attempting to start 'TESTDB.db' on 'oelrac2'
CRS-2676: Start of 'TESTDB.db' on 'oelrac2' succeeded

-- Now lets check the failover test...

Cluster Resources
--------------------------------------------------------------------------------
TESTDB.db
1 ONLINE ONLINE oelrac2

[oracle@oelrac2 ha_scripts]$ ps -ef | grep pmon
oracle 3529 1 0 08:50 ? 00:00:02 asm_pmon_+ASM2
oracle 13194 1 0 12:15 ? 00:00:00 ora_pmon_testdb

[oracle@oelrac2 ha_scripts]$ kill -9 13194

[oracle@oelrac2 ha_scripts]$ ps -ef | grep pmon
oracle 3529 1 0 08:50 ? 00:00:02 asm_pmon_+ASM2
oracle 13434 12098 0 12:17 pts/0 00:00:00 grep pmon

[oracle@oelrac2 ha_scripts]$ ps -ef | grep pmon
oracle 3529 1 0 08:50 ? 00:00:02 asm_pmon_+ASM2
oracle 13498 1 0 12:17 ? 00:00:00 ora_pmon_testdb
oracle 13768 12098 0 12:18 pts/0 00:00:00 grep pmon

-- Could see that the database automatically comes up.

-- Let shutdown the node 2.

--- Monitor the cluster res in node 1

--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
TESTDB.db
1 ONLINE ONLINE oelrac2 STOPPING

--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
TESTDB.db
1 ONLINE OFFLINE STARTING

--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
TESTDB.db
1 ONLINE ONLINE oelrac1

Advantageous:-

1) No need to rely on third party clustware for managing the db resources and cost saving on acquiring those license.
2) Easy maintenance (using crsctl commands).
3) Automatic fail over.
4) Can be useful for application which will not support RAC.

Sunday, July 20, 2014

OCR / VOTING disk adminstration in 11gr2

Current OCR disks
-----------------

#/etc/init.d/oracleasm querydisk /dev/sd*

Device "/dev/sdb1" is marked an ASM disk with the label "NEW_OCR_1"
Device "/dev/sdc" is not marked as an ASM disk
Device "/dev/sdc1" is marked an ASM disk with the label "NEW_OCR_2"
Device "/dev/sdd" is not marked as an ASM disk
Device "/dev/sdd1" is marked an ASM disk with the label "NEW_OCR_3"
Device "/dev/sdm1" is marked an ASM disk with the label "OCR_VOTING_1"
Device "/dev/sdn" is not marked as an ASM disk
Device "/dev/sdn1" is marked an ASM disk with the label "OCR_VOTING_2"
Device "/dev/sdo" is not marked as an ASM disk
Device "/dev/sdo1" is marked an ASM disk with the label "OCR_VOTING_3"

[root@oelrac1 bin]# ./ocrconfig -showbackup

oelrac1 2014/05/10 01:26:20 /u01/grid/11.2.0/cdata/oelrac/backup00.ocr

oelrac1 2014/05/09 21:26:10 /u01/grid/11.2.0/cdata/oelrac/backup01.ocr

oelrac2 2014/03/22 01:06:56 /u01/grid/11.2.0/cdata/oelrac/backup02.ocr

oelrac1 2014/05/09 21:26:10 /u01/grid/11.2.0/cdata/oelrac/day.ocr

oelrac1 2014/05/09 21:26:10 /u01/grid/11.2.0/cdata/oelrac/week.ocr

Preparing the disks for fresh use
---------------------------------

1) Delete all the ocr disks.

/etc/init.d/oracleasm deletedisk NEW_OCR_1
/etc/init.d/oracleasm deletedisk NEW_OCR_2
/etc/init.d/oracleasm deletedisk NEW_OCR_3
/etc/init.d/oracleasm deletedisk OCR_VOTING_1
/etc/init.d/oracleasm deletedisk OCR_VOTING_2
/etc/init.d/oracleasm deletedisk OCR_VOTING_3

2) Format the /dev/sdb1 /dev/sdc1 /dev/sdd1 /dev/sdm1 /dev/sdn1 /dev/sdo1

[root@oelrac1 bin]# dd if=/dev/zero of=/dev/sdb1
dd: writing to `/dev/sdb1': No space left on device
2088388+0 records in
2088387+0 records out
1069254144 bytes (1.1 GB) copied, 13.4004 s, 79.8 MB/s

-- Format all the mentioned devices using above method.

3) Check whether the header been removed.

[root@oelrac1 bin]# for i in /dev/sdb1 /dev/sdc1 /dev/sdd1 /dev/sdm1 /dev/sdn1 /dev/sdo1
> do
> echo "$i" >> /tmp/disk_header.log
> /u01/grid/11.2.0/bin/kfed read $i | grep "kfbh.type" >> /tmp/disk_header.log
> done

[root@oelrac1 bin]# cat /tmp/disk_header.log
/dev/sdb1
kfbh.type: 0 ; 0x002: KFBTYP_INVALID
/dev/sdc1
kfbh.type: 0 ; 0x002: KFBTYP_INVALID
/dev/sdd1
kfbh.type: 0 ; 0x002: KFBTYP_INVALID
/dev/sdm1
kfbh.type: 0 ; 0x002: KFBTYP_INVALID
/dev/sdn1
kfbh.type: 0 ; 0x002: KFBTYP_INVALID
/dev/sdo1
kfbh.type: 0 ; 0x002: KFBTYP_INVALID

-- All the disk has been formatted.

[root@oelrac1 bin]# /etc/init.d/oracleasm createdisk OCR_VOTE_1 /dev/sdb1
Marking disk "OCR_VOTE_1" as an ASM disk: [ OK ]
[root@oelrac1 bin]# /etc/init.d/oracleasm createdisk OCR_VOTE_2 /dev/sdc1
Marking disk "OCR_VOTE_2" as an ASM disk: [ OK ]
[root@oelrac1 bin]# /etc/init.d/oracleasm createdisk OCR_VOTE_3 /dev/sdd1
Marking disk "OCR_VOTE_3" as an ASM disk: [ OK ]
[root@oelrac1 bin]# /etc/init.d/oracleasm createdisk OCR_MIRR_1 /dev/sdm1
Marking disk "OCR_MIRR_1" as an ASM disk: [ OK ]
[root@oelrac1 bin]# /etc/init.d/oracleasm createdisk OCR_MIRR_2 /dev/sdn1
Marking disk "OCR_MIRR_2" as an ASM disk: [ OK ]
[root@oelrac1 bin]# /etc/init.d/oracleasm createdisk OCR_MIRR_3 /dev/sdo1
Marking disk "OCR_MIRR_3" as an ASM disk: [ OK ]

-- OCR disk are ready now.

=============================================================================================================

I) Scenario 1 ( Restore the OCR from the backup)
================================================

a) Backup file of ocr is below.

[root@oelrac1 bin]# ls -lrt /u01/grid/11.2.0/cdata/oelrac/backup00.ocr
-rw------- 1 root root 7176192 May 18 15:44 /u01/grid/11.2.0/cdata/oelrac/backup00.ocr

b) Start the crs in exclusive mode.

[root@oelrac1 bin]# ./crsctl start crs -excl -nocrs
CRS-4123: Oracle High Availability Services has been started.
CRS-2672: Attempting to start 'ora.mdnsd' on 'oelrac1'
CRS-2676: Start of 'ora.mdnsd' on 'oelrac1' succeeded
CRS-2672: Attempting to start 'ora.gpnpd' on 'oelrac1'
CRS-2676: Start of 'ora.gpnpd' on 'oelrac1' succeeded
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'oelrac1'
CRS-2672: Attempting to start 'ora.gipcd' on 'oelrac1'
CRS-2676: Start of 'ora.cssdmonitor' on 'oelrac1' succeeded
CRS-2676: Start of 'ora.gipcd' on 'oelrac1' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'oelrac1'
CRS-2672: Attempting to start 'ora.diskmon' on 'oelrac1'
CRS-2676: Start of 'ora.diskmon' on 'oelrac1' succeeded
CRS-2676: Start of 'ora.cssd' on 'oelrac1' succeeded
CRS-2679: Attempting to clean 'ora.cluster_interconnect.haip' on 'oelrac1'
CRS-2672: Attempting to start 'ora.ctssd' on 'oelrac1'
CRS-2681: Clean of 'ora.cluster_interconnect.haip' on 'oelrac1' succeeded
CRS-2672: Attempting to start 'ora.cluster_interconnect.haip' on 'oelrac1'
CRS-2676: Start of 'ora.ctssd' on 'oelrac1' succeeded
CRS-2676: Start of 'ora.cluster_interconnect.haip' on 'oelrac1' succeeded
CRS-2679: Attempting to clean 'ora.asm' on 'oelrac1'
CRS-2681: Clean of 'ora.asm' on 'oelrac1' succeeded
CRS-2672: Attempting to start 'ora.asm' on 'oelrac1'
CRS-2676: Start of 'ora.asm' on 'oelrac1' succeeded

c) Switch as grid owner and login to asm instance.

SQL> select name, state from V$asm_diskgroup;

NAME STATE
------------------------------ -----------
FRA DISMOUNTED
DATADG DISMOUNTED

SQL> select path, header_status from V$asm_disk;

PATH HEADER_STATU
-------------------------------------------------- ------------
/dev/oracleasm/disks/OCR_MIRR_3 PROVISIONED
/dev/oracleasm/disks/OCR_MIRR_2 PROVISIONED
/dev/oracleasm/disks/OCR_MIRR_1 PROVISIONED
/dev/oracleasm/disks/OCR_VOTE_3 PROVISIONED
/dev/oracleasm/disks/OCR_VOTE_2 PROVISIONED
/dev/oracleasm/disks/OCR_VOTE_1 PROVISIONED
/dev/oracleasm/disks/DISK_NEW_1 MEMBER
/dev/oracleasm/disks/DATA_DISK_3 MEMBER
/dev/oracleasm/disks/DATA_DISK_2 MEMBER
/dev/oracleasm/disks/DATA_DISK_1 MEMBER
/dev/oracleasm/disks/DISK_NEW_4 MEMBER
/dev/oracleasm/disks/DISK_NEW_3 MEMBER
/dev/oracleasm/disks/DISK_NEW_2 MEMBER
/dev/oracleasm/disks/DATA_DISK_4 MEMBER

14 rows selected.

SQL> ! cat /etc/oracle/ocr.loc
ocrconfig_loc=+OCR_VOTING
local_only=false

d) Create the Diskgroup with the same name.

SQL> create diskgroup OCR_VOTING NORMAL REDUNDANCY DISK '/dev/oracleasm/disks/OCR_VOTE_1','/dev/oracleasm/disks/OCR_VOTE_2','/dev/oracleasm/disks/OCR_VOTE_3'
attribute 'compatible.asm' = '11.2', 'au_size' = '4M';

Diskgroup created.

SQL> exit;

e) Now restore the OCR from backup.

[root@oelrac1 bin]# ./ocrconfig -restore /u01/grid/11.2.0/cdata/oelrac/backup00.ocr
[root@oelrac1 bin]# ./ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 262120
Used space (kbytes) : 2828
Available space (kbytes) : 259292
ID : 516257634
Device/File Name : +OCR_VOTING
Device/File integrity check succeeded

Device/File not configured

Device/File not configured

Device/File not configured

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check succeeded

f) Now re-start the crs in exclusive mode

# crsctl stop crs -f
# crsctl start crs -excl -nocrs

[root@oelrac1 trace]# /u01/grid/11.2.0/bin/crsctl stat res -t -init
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.asm
1 ONLINE ONLINE oelrac1 Started
ora.cluster_interconnect.haip
1 ONLINE ONLINE oelrac1
ora.crf
1 OFFLINE OFFLINE
ora.crsd
1 OFFLINE OFFLINE
ora.cssd
1 ONLINE ONLINE oelrac1
ora.cssdmonitor
1 ONLINE ONLINE oelrac1
ora.ctssd
1 ONLINE ONLINE oelrac1 ACTIVE:0
ora.diskmon
1 OFFLINE OFFLINE
ora.evmd
1 OFFLINE OFFLINE
ora.gipcd
1 ONLINE ONLINE oelrac1
ora.gpnpd
1 ONLINE ONLINE oelrac1
ora.mdnsd
1 ONLINE ONLINE oelrac1
[root@oelrac1 trace]# /u01/grid/11.2.0/bin/crsctl query css votedisk
Located 0 voting disk(s).

[root@oelrac1 trace]# /u01/grid/11.2.0/bin/crsctl replace votedisk +OCR_VOTING
Successful addition of voting disk 3eca999b99794f83bf37adb42833f263.
Successful addition of voting disk 2ed8677861f74f9abf25c98a802d95fe.
Successful addition of voting disk 15f88f5c74ce4f77bf1fd2a3c10e4597.
Successfully replaced voting disk group with +OCR_VOTING.
CRS-4266: Voting file(s) successfully replaced

-- Now restart the cluster fully.

[root@oelrac1 bin]# ./crsctl start crs
CRS-4123: Oracle High Availability Services has been started.

[root@oelrac1 bin]# ./crsctl query css votedisk
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE 3eca999b99794f83bf37adb42833f263 (/dev/oracleasm/disks/OCR_VOTE_1) [OCR_VOTING]
2. ONLINE 2ed8677861f74f9abf25c98a802d95fe (/dev/oracleasm/disks/OCR_VOTE_2) [OCR_VOTING]
3. ONLINE 15f88f5c74ce4f77bf1fd2a3c10e4597 (/dev/oracleasm/disks/OCR_VOTE_3) [OCR_VOTING]
Located 3 voting disk(s).

[root@oelrac1 bin]# ./ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 262120
Used space (kbytes) : 2828
Available space (kbytes) : 259292
ID : 516257634
Device/File Name : +OCR_VOTING
Device/File integrity check succeeded

Device/File not configured

Device/File not configured

Device/File not configured

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check succeeded

[root@oelrac1 bin]# ./crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATADG.dg
ONLINE ONLINE oelrac1
ora.LISTENER.lsnr
ONLINE ONLINE oelrac1
ora.OCR_VOTING.dg
ONLINE ONLINE oelrac1
ora.asm
ONLINE ONLINE oelrac1 Started
ora.gsd
OFFLINE OFFLINE oelrac1
ora.net1.network
ONLINE ONLINE oelrac1
ora.ons
ONLINE ONLINE oelrac1
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE oelrac1
ora.cvu
1 ONLINE ONLINE oelrac1
ora.firstdb.db
1 ONLINE OFFLINE Instance Shutdown
2 ONLINE OFFLINE
ora.oc4j
1 ONLINE ONLINE oelrac1
ora.oelrac1.vip
1 ONLINE ONLINE oelrac1
ora.oelrac2.vip
1 ONLINE INTERMEDIATE oelrac1 FAILED OVER
ora.scan1.vip
1 ONLINE ONLINE oelrac1

================================================================================================================

II) Add one more OCR Mirror Location (upto 4 ocrmirrors can be added)

SQL> set lines 200 pages 200;
SQL> col path for a40;
SQL> select path, header_status from V$asm_disk;

PATH HEADER_STATU
---------------------------------------- ------------
/dev/oracleasm/disks/OCR_MIRR_3 PROVISIONED
/dev/oracleasm/disks/OCR_MIRR_2 PROVISIONED
/dev/oracleasm/disks/OCR_MIRR_1 PROVISIONED

SQL> create diskgroup OCR_MIRROR normal redundancy disk '/dev/oracleasm/disks/OCR_MIRR_1','/dev/oracleasm/disks/OCR_MIRR_2','/dev/oracleasm/disks/OCR_MIRR_3'
attribute
'compatible.asm' = '11.2', 'au_size'='4M'
/

[root@oelrac1 bin]# ./ocrconfig -add +OCR_MIRROR

[root@oelrac1 bin]# ./ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 262120
Used space (kbytes) : 2840
Available space (kbytes) : 259280
ID : 516257634
Device/File Name : +OCR_VOTING
Device/File integrity check succeeded
Device/File Name : +OCR_MIRROR
Device/File integrity check succeeded

Device/File not configured

Device/File not configured

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check succeeded

--When votedisk is on ASM diskgroup, no add option available.
The number of votedisk is determined by the diskgroup redundancy. If more copies of votedisks are desired, one can move votedisk to a diskgroup
with higher redundancy.

-- As for the voting disk concerned when the voting disk is placed in asm diskgroup below is the no. of vote
disk base on the redundancy type of the diskgroup.

EXTERNAL - 1 vote disk
NORMAL - 3 vote disk
HIGH - 5 vote disk

so there is no add command and only replace can be used for votedisk.

[root@oelrac1 bin]# ./crsctl replace votedisk +OCR_MIRROR
Successful addition of voting disk 5f7d9d544a7b4fa7bf2b2df10f4cd61f.
Successful addition of voting disk 1ca712cdf06a4f5dbf1843f0d98770d1.
Successful addition of voting disk 5afe0a1d5cbe4fc6bf1a8e04c27be20c.
Successful deletion of voting disk 3eca999b99794f83bf37adb42833f263.
Successful deletion of voting disk 2ed8677861f74f9abf25c98a802d95fe.
Successful deletion of voting disk 15f88f5c74ce4f77bf1fd2a3c10e4597.
Successfully replaced voting disk group with +OCR_MIRROR.
CRS-4266: Voting file(s) successfully replaced

[root@oelrac1 bin]# ./crsctl query css votedisk
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE 5f7d9d544a7b4fa7bf2b2df10f4cd61f (/dev/oracleasm/disks/OCR_MIRR_1) [OCR_MIRROR]
2. ONLINE 1ca712cdf06a4f5dbf1843f0d98770d1 (/dev/oracleasm/disks/OCR_MIRR_2) [OCR_MIRROR]
3. ONLINE 5afe0a1d5cbe4fc6bf1a8e04c27be20c (/dev/oracleasm/disks/OCR_MIRR_3) [OCR_MIRROR]
Located 3 voting disk(s).

-- the disks are deleted from the old diskgroup and added to the new diskgroup.

-- Now lets see how the mirror is usefull.

SQL> alter diskgroup ocr_voting dismount force;

Diskgroup altered.

[root@oelrac1 bin]# ./crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATADG.dg
ONLINE ONLINE oelrac1
ora.LISTENER.lsnr
ONLINE ONLINE oelrac1
ora.OCR_MIRROR.dg
ONLINE ONLINE oelrac1
ora.OCR_VOTING.dg
OFFLINE OFFLINE oelrac1
ora.asm
ONLINE ONLINE oelrac1 Started
ora.gsd
OFFLINE OFFLINE oelrac1
ora.net1.network
ONLINE ONLINE oelrac1
ora.ons
ONLINE ONLINE oelrac1
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE oelrac1
ora.cvu
1 ONLINE ONLINE oelrac1
ora.firstdb.db
1 ONLINE OFFLINE Instance Shutdown
2 ONLINE OFFLINE
ora.oc4j
1 ONLINE ONLINE oelrac1
ora.oelrac1.vip
1 ONLINE ONLINE oelrac1
ora.oelrac2.vip
1 ONLINE INTERMEDIATE oelrac1 FAILED OVER
ora.scan1.vip
1 ONLINE ONLINE oelrac1

-- The OCR_VOTING diskgroup has been dismounted but still the clusterware is online.

--Lets dismount the mirror diskgroup as well.

SQL> alter diskgroup ocr_mirror dismount force;

Diskgroup altered.

[root@oelrac1 bin]# ./crsctl stat res -t
CRS-4535: Cannot communicate with Cluster Ready Services
CRS-4000: Command Status failed, or completed with errors.

-- Now the crs is not functional.

SQL> alter diskgroup ocr_mirror mount;

Diskgroup altered.

SQL> alter diskgroup ocr_voting mount;

Diskgroup altered.

[root@oelrac1 bin]# ./crsctl start res ora.crsd -init
CRS-2672: Attempting to start 'ora.crsd' on 'oelrac1'
CRS-2676: Start of 'ora.crsd' on 'oelrac1' succeeded

[root@oelrac1 bin]# ./crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATADG.dg
ONLINE ONLINE oelrac1
ora.LISTENER.lsnr
ONLINE ONLINE oelrac1
ora.OCR_MIRROR.dg
ONLINE ONLINE oelrac1
ora.OCR_VOTING.dg
ONLINE ONLINE oelrac1
ora.asm
ONLINE ONLINE oelrac1 Started
ora.gsd
OFFLINE OFFLINE oelrac1
ora.net1.network
ONLINE ONLINE oelrac1
ora.ons
ONLINE ONLINE oelrac1
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE oelrac1
ora.cvu
1 ONLINE ONLINE oelrac1
ora.firstdb.db
1 ONLINE OFFLINE Instance Shutdown,S
TARTING
2 ONLINE OFFLINE
ora.oc4j
1 ONLINE ONLINE oelrac1
ora.oelrac1.vip
1 ONLINE ONLINE oelrac1
ora.oelrac2.vip
1 ONLINE INTERMEDIATE oelrac1 FAILED OVER
ora.scan1.vip
1 ONLINE ONLINE oelrac1

====================================================================================

III) Remove an OCR Device.
--------------------------

[root@oelrac1 bin]# ./ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 262120
Used space (kbytes) : 2836
Available space (kbytes) : 259284
ID : 516257634
Device/File Name : +OCR_VOTING
Device/File integrity check succeeded
Device/File Name : +OCR_MIRROR
Device/File integrity check succeeded

Device/File not configured

Device/File not configured

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check succeeded

-- Lets remove the primary device.

[root@oelrac1 bin]# ./ocrconfig -delete +OCR_VOTING

[root@oelrac1 bin]# ./ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 262120
Used space (kbytes) : 2836
Available space (kbytes) : 259284
ID : 516257634
Device/File Name : +OCR_MIRROR
Device/File integrity check succeeded

Device/File not configured

Device/File not configured

Device/File not configured

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check succeeded

[root@oelrac1 bin]# cat /etc/oracle/ocr.loc
#Device/file +OCR_VOTING getting replaced by device +OCR_MIRROR
ocrconfig_loc=+OCR_MIRROR
local_only=false

-- Could see that the primary has been replaced with mirror one.

-- Lets add the diskgroup back.

[root@oelrac1 bin]# ./ocrconfig -add +OCR_VOTING
[root@oelrac1 bin]# ./ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 262120
Used space (kbytes) : 2836
Available space (kbytes) : 259284
ID : 516257634
Device/File Name : +OCR_MIRROR
Device/File integrity check succeeded
Device/File Name : +OCR_VOTING
Device/File integrity check succeeded

Device/File not configured

Device/File not configured

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check succeeded

[root@oelrac1 bin]# cat /etc/oracle/ocr.loc
#Device/file getting replaced by device +OCR_VOTING
ocrconfig_loc=+OCR_MIRROR
ocrmirrorconfig_loc=+OCR_VOTING
local_only=false

III) Replace or move Existing OCR location to New Location.

Note.
1. An ocrmirror must be in place before trying to replace the OCR device. The ocrconfig will fail
with PROT-16, if there is no ocrmirror exists.
2. If an OCR device is replaced with a device of a different size, the size of the new device will
not be reflected until the clusterware is restarted.

-- ocrconfig -replace +OCR_VOTING -replacement +NEW_OCR_VOTING

-- Voting Disk adminstration.

What happens if I lose my voting disk(s)?

If you lose 1/2 or more of all of your voting disks, then nodes get evicted from the cluster, or nodes kick
themselves out of the cluster. It doesn't threaten database corruption. Alternatively you can use external
redundancy which means you are providing redundancy at the storage level using RAID.
For this reason when using Oracle for the redundancy of your voting disks, Oracle recommends that customers
use 3 or more voting disks in Oracle RAC 10g Release 2. Note: For best availability, the 3 voting files should
be physically separate disks. It is recommended to use an odd number as 4 disks will not be any more highly
available than 3 disks, 1/2 of 3 is 1.5...rounded to 2, 1/2 of 4 is 2, once we lose 2 disks, our cluster will fail
with both 4 voting disks or 3 voting disks.

Restoring corrupted voting disks is easy since there isn't any significant persistent data stored in the voting disk.
See the Oracle Clusterware Admin and Deployment Guide for information on backup and restore of voting disks.

For 11.2+, it is no longer required to back up the voting disk. The voting disk data is automatically backed up in OCR
as part of any configuration change. The voting disk files are backed up automatically by Oracle Clusterware if the
contents of the files have changed in the following ways:

Configuration parameters, for example misscount, have been added or modified
After performing voting disk add or delete operations

Add - When votedisk is on ASM diskgroup, no add option available. The number of votedisk is determined by the
diskgroup redundancy. If more copies of votedisks are desired, one can move votedisk to a diskgroup
with higher redundancy.

delete - When votedisk is on ASM, no delete option available, one can only replace the existing votedisk group with another ASM diskgroup.

Move the voting disk- as per below.

[root@oelrac1 bin]# ./crsctl query css votedisk
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE 5f7d9d544a7b4fa7bf2b2df10f4cd61f (/dev/oracleasm/disks/OCR_MIRR_1) [OCR_MIRROR]
2. ONLINE 1ca712cdf06a4f5dbf1843f0d98770d1 (/dev/oracleasm/disks/OCR_MIRR_2) [OCR_MIRROR]
3. ONLINE 5afe0a1d5cbe4fc6bf1a8e04c27be20c (/dev/oracleasm/disks/OCR_MIRR_3) [OCR_MIRROR]
Located 3 voting disk(s).

-- Current location id ocr_mirror diskgroup , lets move it to OCR_VOTING diskgroup, it can be done online.

[root@oelrac1 bin]# ./crsctl replace votedisk +OCR_VOTING
Successful addition of voting disk 85ae96c5a26e4f0cbf35f6dd50cb8765.
Successful addition of voting disk 761064c6565c4f54bfedc332938412f8.
Successful addition of voting disk 8484395588014f8abf7274198aa21b1a.
Successful deletion of voting disk 5f7d9d544a7b4fa7bf2b2df10f4cd61f.
Successful deletion of voting disk 1ca712cdf06a4f5dbf1843f0d98770d1.
Successful deletion of voting disk 5afe0a1d5cbe4fc6bf1a8e04c27be20c.
Successfully replaced voting disk group with +OCR_VOTING.
CRS-4266: Voting file(s) successfully replaced

Related-
How to fix the "DiscoveryString in profile.xml" or "asm_diskstring in ASM" if set wrongly (Doc ID 1077094.1)
NOTE:1376225.1 - How to Modify an Existing ASM Spfile in a RAC Environment
http://www.oracle.com/technetwork/database/clusterware/overview/grid-infra-thirdvoteonnfs-131158.pdf

Saturday, July 19, 2014

How spfile is discovered in 11gr2 Grid

How spfile is discovered in 11gr2 Grid
--------------------------------------

As we all aware in 11gr2 grid infrastructure the asm spfile will be placed
under the asm diskgroup.

[oracle@oelrac1 ~]$ gpnptool get
Warning: some command line parameters were defaulted. Resulting command line:
/u01/grid/11.2.0/bin/gpnptool.bin get -o-

Cannot get GPnP profile. Error CLSGPNP_NO_DAEMON (GPNPD daemon is not running).
GPnP service is not running on localhost. Found locally cached profile...

micVNAZ3LnolkupldzDRgpM/sEw=HgZp5wx5t55NW7mWiEHSQsevlOd4jpzkDDoyNXYaVUf3fOYK+FLueGTLRr9WZLYmRedUuGVORGECF7v/apacc6dC+nLIa6XxutkHAu6rYuSbjcKoqpB8aCdSlG1gV1v7mzuSRQqGizs3ZW74Np/HZHXpwKsUGvbr6TLzLOwBT2Y=

Success.

--In the above output we could see that the asm_diskstring is mentioned as '/dev/oracleasm/disk*' which is
used to identify the spfile and the voting disks.

[oracle@oelrac1 ~]$ crsctl query css votedisk
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE 8f20abfc981c4f0dbf4707016284a667 (/dev/oracleasm/disks/OCR_VOTING_1) [OCR_VOTING]
2. ONLINE 3434c752d9654f2dbf5225b202432ce3 (/dev/oracleasm/disks/OCR_VOTING_2) [OCR_VOTING]
3. ONLINE 20a19fe75c3f4f94bf4f3e95866128ba (/dev/oracleasm/disks/OCR_VOTING_3) [OCR_VOTING]
Located 3 voting disk(s).

SQL> show parameter asm_disk;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
asm_diskgroups string DATADG
asm_diskstring string /dev/oracleasm/disks/*
SQL> ! ls -lrt /dev/oracleasm/disks/*
brw-rw---- 1 oracle dba 8, 113 Mar 15 09:27 /dev/oracleasm/disks/DATA_DISK_4
brw-rw---- 1 oracle dba 8, 81 Mar 15 09:28 /dev/oracleasm/disks/DATA_DISK_2
brw-rw---- 1 oracle dba 8, 49 Mar 15 09:28 /dev/oracleasm/disks/OCR_VOTING_3
brw-rw---- 1 oracle dba 8, 33 Mar 15 09:28 /dev/oracleasm/disks/OCR_VOTING_2
brw-rw---- 1 oracle dba 8, 17 Mar 15 09:28 /dev/oracleasm/disks/OCR_VOTING_1
brw-rw---- 1 oracle dba 8, 97 Mar 15 09:28 /dev/oracleasm/disks/DATA_DISK_3
brw-rw---- 1 oracle dba 8, 65 Mar 15 09:28 /dev/oracleasm/disks/DATA_DISK_1

SQL> alter system set asm_diskstring = '/dev/oracleasm/disks/DATA*' scope=spfile;

System altered.

[root@oelrac1 ~]# cd /u01/grid/11.2.0/bin/
[root@oelrac1 bin]# ./crsctl stop crs
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'oelrac1' has completed
CRS-4133: Oracle High Availability Services has been stopped.

[root@oelrac1 bin]# ./crsctl start crs
CRS-4123: Oracle High Availability Services has been started.

[root@oelrac1 bin]# ./crsctl stat res -t -init
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.asm
1 ONLINE OFFLINE Instance Shutdown
ora.cluster_interconnect.haip
1 ONLINE OFFLINE
ora.crf
1 ONLINE ONLINE oelrac1
ora.crsd
1 ONLINE OFFLINE
ora.cssd
1 ONLINE OFFLINE STARTING
ora.cssdmonitor
1 ONLINE ONLINE oelrac1
ora.ctssd
1 ONLINE OFFLINE
ora.diskmon
1 OFFLINE OFFLINE
ora.evmd
1 ONLINE OFFLINE
ora.gipcd
1 ONLINE ONLINE oelrac1
ora.gpnpd
1 ONLINE ONLINE oelrac1
ora.mdnsd
1 ONLINE ONLINE oelrac1

-- Now the CSSD process is not coming up.. this is expected.

Some snaps from occsd.log

2014-03-14 17:38:23.393: [ GPNP][3455813376]clsgpnp_profileCallUrlInt: [at clsgpnp.c:2234] Result: (0) CLSGPNP_OK. Successful get-profile CALL to remote "ipc://GPNPD
_oelrac1" disco ""
2014-03-14 17:38:23.393: [ CSSD][3455813376]clssscGetParameterProfile: buffer passed for parameter ASM discovery (3) is too short, required 27, passed 20
2014-03-14 17:38:23.393: [ CSSD][3455813376]clssnmReadDiscoveryProfile: voting file discovery string(/dev/oracleasm/disks/DATA*)
2014-03-14 17:38:23.393: [ CSSD][3455813376]clssnmvDDiscThread: using discovery string /dev/oracleasm/disks/DATA* for initial discovery
2014-03-14 17:38:23.393: [ SKGFD][3455813376]Discovery with str:/dev/oracleasm/disks/DATA*:

2014-03-14 17:38:23.393: [ SKGFD][3455813376]UFS discovery with :/dev/oracleasm/disks/DATA*:

2014-03-14 17:38:23.398: [ SKGFD][3455813376]Fetching UFS disk :/dev/oracleasm/disks/DATA_DISK_1:

2014-03-14 17:38:23.398: [ SKGFD][3455813376]Fetching UFS disk :/dev/oracleasm/disks/DATA_DISK_2:

2014-03-14 17:38:23.398: [ SKGFD][3455813376]Fetching UFS disk :/dev/oracleasm/disks/DATA_DISK_3:

2014-03-14 17:38:23.398: [ SKGFD][3455813376]Fetching UFS disk :/dev/oracleasm/disks/DATA_DISK_4:

2014-03-14 17:38:23.398: [ SKGFD][3455813376]OSS discovery with :/dev/oracleasm/disks/DATA*:

2014-03-14 17:38:23.398: [ SKGFD][3455813376]Handle 0x7fd5b80930f0 from lib :UFS:: for disk :/dev/oracleasm/disks/DATA_DISK_1:

2014-03-14 17:38:23.398: [ SKGFD][3455813376]Handle 0x7fd5b8137d80 from lib :UFS:: for disk :/dev/oracleasm/disks/DATA_DISK_2:

2014-03-14 17:38:23.398: [ SKGFD][3455813376]Handle 0x7fd5b8125730 from lib :UFS:: for disk :/dev/oracleasm/disks/DATA_DISK_3:

2014-03-14 17:38:23.399: [ SKGFD][3455813376]Handle 0x7fd5b8136cf0 from lib :UFS:: for disk :/dev/oracleasm/disks/DATA_DISK_4:

2014-03-14 17:38:23.399: [ SKGFD][3455813376]Lib :UFS:: closing handle 0x7fd5b80930f0 for disk :/dev/oracleasm/disks/DATA_DISK_1:

2014-03-14 17:38:23.399: [ SKGFD][3455813376]Lib :UFS:: closing handle 0x7fd5b8137d80 for disk :/dev/oracleasm/disks/DATA_DISK_2:

2014-03-14 17:38:23.399: [ SKGFD][3455813376]Lib :UFS:: closing handle 0x7fd5b8125730 for disk :/dev/oracleasm/disks/DATA_DISK_3:

2014-03-14 17:38:23.399: [ SKGFD][3455813376]Lib :UFS:: closing handle 0x7fd5b8136cf0 for disk :/dev/oracleasm/disks/DATA_DISK_4:

2014-03-14 17:38:23.399: [ CSSD][3455813376]clssnmvDiskVerify: Successful discovery of 0 disks
2014-03-14 17:38:23.399: [ CSSD][3455813376]clssnmCompleteInitVFDiscovery: Completing initial voting file discovery
2014-03-14 17:38:23.399: [ CSSD][3455813376]clssnmvFindInitialConfigs: No voting files found

--- Its is not able to find the voting disks.

[oracle@oelrac1 ~]$ cd /u01/grid/11.2.0/gpnp/oelrac1/profiles/peer/
[oracle@oelrac1 peer]$ cat profile.xml
xmlns:gpnp="http://www.grid-pnp.org/2005/11/gpnp-profile" xmlns:orcl="http://www.oracle.com/gpnp/2005/11/gpnp-profile"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.grid-pnp.org/2005/11/gpnp-profile
gpnp-profile.xsd" ProfileSequence="5" ClusterUId="5785d8b1a7aa5ff5ff072fe8b2bf44d1" ClusterName="oelrac"
PALocation="">Adapter="eth0" Use="public"/>

DiscoveryString="+asm" LeaseDuration="400"/> 7jxgRZIBJookyQCAC5wBSF4s31I=iwoqZDP0rKW2D+uUI/NJZ5ckxgsHW49XQqzabeYi/9ebvXPb/bN2p0z9nuIrvRqj9a3E1DwEfP7JAKfD1ApHCRrK+v1vQ6VfypVXR2dToMrSwQk0tG6sEKl3wL3Pcm6DYEY3eVjRc+qA6bB0Xr/ENcZ0Hs9apAmYY6/19i0xi80=

[oracle@oelrac1 peer]$

-- From the bove we could see DiscoveryString="/dev/oracleasm/disks/DATA*"

[oracle@oelrac1 peer]$ crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4535: Cannot communicate with Cluster Ready Services
CRS-4530: Communications failure contacting Cluster Synchronization Services daemon
CRS-4534: Cannot communicate with Event Manager

[oracle@oelrac1 peer]$ cp profile.xml profile.xml.bkp

[oracle@oelrac1 peer]$ gpnptool unsign -p=profile.xml.bkp
Warning: some command line parameters were defaulted. Resulting command line:
/u01/grid/11.2.0/bin/gpnptool.bin unsign -p=profile.xml.bkp -o-

Success.

[oracle@oelrac1 peer]$ gpnptool edit -asm_dis='/dev/oracleasm/disks/*' -p=profile.xml.bkp -o=profile.xml.bkp -ovr
Resulting profile written to "profile.xml.bkp".
Success.

[oracle@oelrac1 peer]$ gpnptool sign -p=profile.xml.bkp -w=/u01/grid/11.2.0/gpnp/oelrac1/wallets/peer/ -o=profile.new
Resulting profile written to "profile.new".
Success.

Restart the cluster.

[root@oelrac1 bin]# ./crsctl stop crs -f
CRS-4133: Oracle High Availability Services has been stopped.

[root@oelrac1 bin]# ./crsctl start crs
CRS-4123: Oracle High Availability Services has been started.

[root@oelrac1 bin]# ./crsctl stat res -t -init
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.asm
1 ONLINE ONLINE oelrac1 Started
ora.cluster_interconnect.haip
1 ONLINE ONLINE oelrac1
ora.crf
1 ONLINE ONLINE oelrac1
ora.crsd
1 ONLINE INTERMEDIATE oelrac1
ora.cssd
1 ONLINE ONLINE oelrac1
ora.cssdmonitor
1 ONLINE ONLINE oelrac1
ora.ctssd
1 ONLINE ONLINE oelrac1 ACTIVE:0
ora.diskmon
1 OFFLINE OFFLINE
ora.evmd
1 ONLINE INTERMEDIATE oelrac1
ora.gipcd
1 ONLINE ONLINE oelrac1
ora.gpnpd
1 ONLINE ONLINE oelrac1
ora.mdnsd
1 ONLINE ONLINE oelrac1

[oracle@oelrac1 ~]$ gpnptool get
Warning: some command line parameters were defaulted. Resulting command line:
/u01/grid/11.2.0/bin/gpnptool.bin get -o-

bOe6eT67erYiu4fT4ZwLIyKW9EA=KfwbH0soTVN3yVuwMA7ln57LUlvRgIPWGdxdRQlkghZZisg31NaZYXg4AHeXDo9xEs5LsYf5wR+Ay4ez84JWSCZ31G0k7o4FS43hB9CvrBW6q1+54JsC8iQGPZT61uGjqAVkTw8K9E0Kp5TIwTg0Tm54KwnxSu+UGo/O+w22ppY=

Success.

Now the - DiscoveryString="/dev/oracleasm/disks/*"

and also the crs started without issues.