Oracle 12c RAC - Voting Disk - Location | Relocate | Backup | Repair

Check the location of voting disk

$ crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   0b268c78cdb24f96bfd4d9e01083c9b0 (/dev/oracleasm/asm-data01) [DATA]
Located 1 voting disk(s).
$

– we can see that only one copy of the voting disk is there on data diskgroup which has external redundancy. Oracle writes the voting devices to the underlying disks at pre-designated locations so that it can get the contents of these files when the cluster starts up.

To Relocate voting disk

– Create new disk-group OCRVD with normal redundancy and 2 disks.
– Try to move voting disk from disk-group DATA to OCRVD disk-group
– Fails as we should have at least 3 disks in the OCRVD disk-group

$ crsctl replace votedisk +OCRVD
Failed to create voting files on disk group OCRVD.
Change to configuration failed, but was successfully rolled back.
CRS-4000: Command Replace failed, or completed with errors.

– Add another disk to test diskgroup and mark it as quorum disk. The quorum disk is one small Disk (500 MB should be on the safe side here, since the Voting File is only about 280 MB in size) to keep one Mirror of the Voting File. Other two disks will contain each one Voting File and all the other stripes of the Database Area as well, but quorum  will only get that one Voting File.
– Now try to move the voting disk from DATA disk-group to OCRVD disk-group.
– Now the operation is successful

$ crsctl replace votedisk +OCRVD
Successful addition of voting disk 00ce3c95c6534f44bfffa645a3430bc3.
Successful addition of voting disk a3751063aec14f8ebfe8fb89fccf45ff.
Successful addition of voting disk 0fce89ac35834f99bff7b04ccaaa8006.
Successful deletion of voting disk 243ec3b2a3cf4fbbbfed6f20a1ef4319.
Successfully replaced voting disk group with +OCRVD.
CRS-4266: Voting file(s) successfully replaced

Why odd number of voting disk?

It can be seen that number of voting disks whose failure can be tolerated is same for (2n-1) as well as 2n voting disks where n can be 1, 2 or 3. Hence to save a redundant voting disk, (2n-1) i.e. an odd number of voting disks are desirable.

Space occupied by ocr & vote disk

[oracle@example1a ~]$ ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          4
         Total space (kbytes)     :     409568
         Used space (kbytes)      :       4044
         Available space (kbytes) :     405524
         ID                       :  903643710
         Device/File Name         :      +DATA
                                    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 bypassed due to non-privileged user
[oracle@example1a ~]$ 


Viewing Available OCR Backups

[oracle@example1a ~]$ ocrconfig -showbackup

example1a     2018/07/31 11:59:50     /u01/app/12.1.0/grid/cdata/example1/backup00.ocr     0
example1a     2018/07/31 07:59:50     /u01/app/12.1.0/grid/cdata/example1/backup01.ocr     0
example1a     2018/07/31 03:59:50     /u01/app/12.1.0/grid/cdata/example1/backup02.ocr     0
example1a     2018/07/30 03:59:48     /u01/app/12.1.0/grid/cdata/example1/day.ocr     0
example1a     2018/07/20 23:59:31     /u01/app/12.1.0/grid/cdata/example1/week.ocr     0
PROT-25: Manual backups for the Oracle Cluster Registry are not available
[oracle@example1a ~]$ 

To manually backup the contents of the OCR:

Log in as the root user.
Use the following command to force Oracle Clusterware to perform an immediate backup of the OCR:
   # ocrconfig -manualbackup
The date and identifier of the recently generated OCR backup is displayed.
If you must change the location for the OCR backup files, then use the following command, where directory_name is the new location for the backups:
# ocrconfig -backuploc directory_name

Find current/ active version of OCR

[oracle@example1a ~]$ crsctl query crs activeversion
Oracle Clusterware active version on the cluster is [12.1.0.2.0]
[oracle@example1a ~]$ 

Repairing an OCR Configuration on a Local Node

As the root user, run one or more of the following commands on the node on which Oracle Clusterware is stopped, depending on the number and type of changes that were

made to the OCR configuration:
[root]# ocrconfig –repair -add new_ocr_file_name
[root]# ocrconfig –repair -delete ocr_file_name
[root]# ocrconfig –repair -replace source_ocr_file -replacement dest_ocr_file
These commands update the OCR configuration only on the node from which you run the command.

Restart Oracle Clusterware on the node you have just repaired.
As the root user, check the OCR configuration integrity of your cluster using the following command:
[root]# ocrcheck

Guide to free Oracle tuning tools

When you don't have any licencing tools like OEM, To get more information about system typically takes time and it is difficult to show in single dash-board or via graphs. So here I will take few tools name what I discovered during my research. These tools are developed by somebody and kept in GitHub. Thought this page, I am not claiming any thing that is developed or modified by me. Thanks to them who really developed those and kept in GitHub for us to make our DBAs life easy. Here are some tools what can help you:

A) TUNAS360 ( TUNing Active Sessions)
B) eDB360 & SqlDB360
C) snapper
D) Trace Analyze Tool

These above tools are free and but you should have licence of Enterprise Edition with Tuning and Diagnostic Pack. You are sole responsible to execute of scripts. Neither I am the owner of script not I can give you any solution if any damage happen post run. But I have not faced any issue with these tools. Even I have not seen any claim in sites.

A) TUNAS360 ( TUNing Active Sessions):

(TUN)ing with (A)ctive (s)essions, aka TUNAs360, requires no installation and no parameter when executed; it observes the workload for few minutes and then collects a set of reports on such load.

Mauro Pagano descried about his tool. Please click here to read more.

Steps to use:

1. Unzip tunas360-master.zip, navigate to the root tunas360 directory, and connect as SYS,
   DBA, or any User with Data Dictionary access: Click here to download script.

   $ unzip tunas360-master.zip
   $ cd tunas360-master
   $ sqlplus dba_user/dba_pwd

2. Execute tunas360.sql passing no parameters. TUNAs360 will observe the workload for a few minutes and it will then report on such workload

   SQL> @tunas360.sql
 
3. Unzip output tunas360_<dbname>_<host>_YYYYMMDD_HH24MI.zip into a directory on your PC

4. Review main html file 0001_tunas360_<dbname>_index.html

Sample Screen-shot:

B) SQLdb360 ( Just like SQLT)SQLdb360 is a "free to use toolset" to perform an initial assessment of an entire Oracle database or a particular SQL statement.SQLdb360 is made of two independent tools, eDB360 (database-wide analysis) and SQLd360 (individual SQL analysis). Steps To use:1. Download the tool into target database server2. Navigate to master directory and connect into SQLPlus as DBA or user with access to data dictionary3. Execute edb360.sql (database view) of sqld360.sql (one SQL focus)   - Both tools will prompt for the license available on the target database.     - [T | D | N] For Tuning, Diagnostics or None   - Both tools accept an optimal configuration file   - SQLd360 requires the SQL ID of interest to be provided   4. Copy output zip to client (PC or Mac), unzip and open in browser file 00001_*_index.htmlNotes:1. eDB360 and SQLd360 run transparently on and support RAC, Exadata and In-Memory. In a multitenant environment, connect to PDB of interest.2. No application data is collected, only metadata is accessed.3. Both tools work in a "no evidence left behind" fashion, meaning there is no post execution step that needs to be executed, the tools clean after themselves.4. It is recommended to download the latest version of the tool before using it, this is to minimize the impact of known bugs and benefit from latest features.Troubleshootingedb360 takes up to 24 hours to execute on a large database. But option-wise, it may complete within few minutes. On smaller ones or on Exadata it may take a few hours or less. In rare cases it may require even more than 24 hrs. By default, eDB360 executes a pre-check and asks for confirmation in case the execution is estimated to take more than 8 hours. Multiple options are available to speed up large executions, for details click here to refer as per the site.SQLd360 is generally faster, given the reduced scope, and as such no pre-check is executed.Sample Example:( To Trace and get details analysis report of a sql_id)$ unzip sqld360-master.zip$ cd sqld360-master$ sqlplus / as sysdbaSQL> @sqld360.sql   18nu9mw27r4dm   TSQLd360 usually completes within a few minutes but it can take longer in case the amount of data to analyze in AWR/ASH is significant. The output file can reach up to 100Mb so make sure the directory where the tool is executed from has enough free space.Following .zip file will be created. sqld360_986800_18nu9mw27r4dm_351649_20180725_1516.zipUnzip and open the index file and go through the options. Beauty of this is, it also give you 10053 trace file including query, execution plan etc.

Another Sample screen-shot with other options:

C) SNAPPER:As the Snapper script allows you to easily take performance snapshots of Oracle sessions and fish for solutions to your performance problems from the ocean of possible root causes. The major new features include:- RAC support – ability to query stats from remote instances- Manual Before and After snapshot support – no need to use DBMS_LOCK sleeps anymoreShow useful averages and ratios *in addition to* raw metrics for faster troubleshootingAnd more! :)Snapper is still a free-to-use tool and it still does NOT require any object creation nor changes in your databases for use. Now even DBMS_LOCK access isn’t needed, although it’s still useful for convenience. Click here to find script location.Sample Examples:1) Test-1:SQL> @snapper ash 5 1 all

mode can be ash, stat or custom column list– seconds to run/sample– number of runs– “all” or filter on v$session columnsSimilarly, you can use bellow also. In the script syntax is written. Test-2:SQL> @snapper ash=wait_class 5 1 allSampling SID all with interval 5 seconds, taking 1 snapshots...Test-3:SQL> @snapper stats,gather=tw 15 1 sid=802Sampling SID sid=802 with interval 15 seconds, taking 1 snapshots...Test-4:SQL> @snapper ash=sql_id+wait_class 15 1 user=scottSampling SID user=scott with interval 15 seconds, taking 1 snapshots...Other Examples:@snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id,ash2=program+module+action 5 1 all@snapper ash=sid+event+wait_class,ash1=sid+sqlid+module,stats,gather=ts,tinclude=CPU,sinclude=parse 5 1 14167@snapper stats 5 1 14074@snapper ash,stats 50 1 795
Note: 14167, 14074 etc. are oracle trace types.

D) Trace Analyze Tool ( Oracle Trace Parsing):When you are tracing session, if you have analyze tool, then you can easily find out the root cause. Oracle has trace analyzer. Even some free tools are there. I found this tool good enough for me during sql trace.
To download script, click here.How to use?steps:1) Dowmload the script and unzip2) Go the script folder3) Run as like below:$ ./parsetrc.pl  mytrace.trc( taken from readme.txt)

Metric error on OEM 12c/13c - Recollect the metrics from the Target

While working with various targets and OEM, there may be some issues with metric collection. Ti elaborate, let me put one example:
Due to some reason one server got hung and it doesn't allow any connections. At that time your oem will show the old values what are collected earlier. But actually sever is hung state. Then I have rebooted the server. After server came up, still you may observer oem has old metric values. Becuase it has not collected fresh values.

What to do next?

It is advised, in such scenarios, collect the fresh metric collection with reload the agent.

Example:

-- To reload agent
$ emctl reload agent

-- To find help regarding metric collection

 $emctl control agent runCollection -help 
 Oracle Enterprise Manager Cloud Control 13c Release 2 
 Copyright (c) 1996, 2016 Oracle Corporation. All rights reserved. 
 --------------------------------------------------------------- 
 Usage: 
 emctl control agent runCollection TARGETNAME:TARGETTYPE COLLECTIONITEM ... 
 Runs the collections item(s) for the specified target instance 

 Example: 

 $ emctl control agent runCollection example01.xxxx.com:host oracle_si_time_perf 

 emctl control agent runCollection example01.xxxx.com:host oracle_si_time_perf
 Oracle Enterprise Manager Cloud Control 13c Release 2  
 Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
 ---------------------------------------------------------------
 EMD runCollection completed successfully