MongoDB DataTypes

 MongoDB stores documents in BSON, which is the binary encoded format of JSON. Basically, the name BSON itself comes from Binary encoded JSON. 

The BSON data format provides various types, used when we store the JavaScript objects in the binary form. We can make remote procedure calls in MongoDB by using BSON. 

All the BSON data-types are supported in MongoDB. Below are the enlisted MongoDB data types. 

Each MongoDB datatypes corresponds a unique number which is used to identify them in $type method.

For more details about MongoDB Datatype: Click Here

BulkWrite/BulkInsert Operation in MongoDB

 BulkWrite Operation in simple word perform CRUD operation in single collection. Which is mainly use in sharding but you can do in single node on single collection also below the command/Syntax.

> db.collection.bulkWrite()

Supports the following write operations:

• insertOne
• updateOne
• updateMany
• replaceOne
• deleteOne
• deleteMany

try {
db.student.bulkWrite(
   [
        { insertOne : { “document” : {“name” : “Dimitry”, “rollno” : 106, “contact” : “89xxxxxxxx”}}}},
        { insertOne : { “document” : {“name” : “Dorian”, “rollno” : 107, “contact” : “88xxxxxxxxxx”}}},
        { updateOne : { “filter” : { “rollno” : 105 },”update” : { $set : { “name” : “Test Name” }}}},
        { deleteOne : { “filter” : { “rollno” : 101}}},
        { replaceOne :{ “filter” : { “rollno” : 102 },”replacement” : { “name” : “Karan”, “contact” : “77xxxxxxxxxx” }}}
   ]
);
}
catch (e) {
print(e);
}

 

BulkInsert Command 

> db.student.insertMany()

 

try {
   db.student.insertMany( [
          { “name”: “A”, “rollno” : 201 },
          { “name”: “B”, “rollno” :202 },
          { “name”: “C”, “rollno” :203 },
          { “name”: “D”, “rollno” :204 },
          { “name”: “E”, “rollno” :205 }
] );
} catch (e) {
print (e);
}

 

For more details about BulkWrite/BulkInsert: Click Here

MongoDB Sorting/Limits of Records.

 Now i’ll give you a small commands for show you the limited records fetch from collection and sorting for collection data.

 

Record Limits

syntax: limit() 

> db.collection.find().limit(NUMBER)

> db.student.find().limit(3) <——– It’s showing 3 records from collections

 

Record Sorting

syntax: sort()

> db.collection.find().sort({KEY:1})    

> db.student.find().sort({KEY:1}) <—- Key:1 showing the data in Ascending order and if you mention -1 showing data in Descending order

Indexes in MongoDB

 In MongoDB also have indexes same as other database but what kind of indexes and functionality below we are discuss.

Indexes are used to quickly locate data without having to search every row in a database table every time a database table is accessed. Indexes can be created using one or more columns of a database table, providing the basis for both rapid random lookups and efficient access of ordered records. In mongodb documents is uniquely defined with _id columns you can see when executing the db.collection.find() but application team some time gives the object id as of own understanding. If you getting some performance issue all type of issue written in mongod.log file as per select the profiling late to dicuss about these topics Mongod.log / Profiling.

 

Create Index

Syntax: ensureIndex()    <——– Now 3.2 onword its depricated 

Syntax: createIndex()

> db.student.createIndex({“rollno”:1})

> db.student.reIndex()           <———- Index rebuild for defrag the indexes

> db.student.dropIndex( { “rollno”: 1 } )   <——- drop index from collection

> db.student.getIndexes();     <——– Get the all list of indexes which is available on student collection

 

Below Using Test Database in mongoDB and below the show you complete list of indexes available in TEST database only.

> use TEST 

> db.getCollectionNames().forEach(function(collection) {
indexes = db[collection].getIndexes();
print(“Indexes for ” + collection + “:”);
printjson(indexes);
});

For the details about indexes and type of indexes available in mongoDB : Click Here

MongoDB Security

 MongoDB provides various features, such as authentication, access control, encryption, to secure your MongoDB deployments. Some key security features include:

 

By Default not any authentication enabled in mongoDB env.

1.) Basic authentication like a role based give the privileges/role etc to the user.

2.) Database authentication enabled with the help of below the parameter set in configuration file.

create user with admin role

security.authorization so after enable when you try to login they not permit to execute any command so swith the admin user and login to authenticate user then try.

Login to authenticate user.

> use admin

> db.auth(“TEST”,”tesT123″);

1

> show dbs

Another authentication like SSL/TSL also provide the mongodb i’m giving you some idea how to configure.

For SSL. Generate Certificate > then verify the signature > .cert/pem file allocated to the server the pass the path through ops-manager / CLI also doing this thing > configure with LDAP .. then try to login like this.:

Connection string using LDAP :
mongo –ssl –sslCAFile /var/lib/mongod/cert/TESTca.pem –host $(hostname).$(dnsdomainname) –port 27022 -u “TEST” -p “” –authenticationMechanism ‘PLAIN’ –authenticationDatabase ‘$external’ admin

So security enable is not a part of only DBA involve more teams like . Application/LDAP/Network/Security/DBA

 

For more detail about Security : Click Here

 

MongoDB Storage/Storage Engine

 The storage engine is the primary component of MongoDB responsible for managing data. MongoDB provides a variety of storage engines, allowing you to choose one most suited to your application.

WiredTiger Storage Engine (Default)

MMAPv1 Storage Engine (Deprecated as of MongoDB 4.0)

In-Memory Storage Engine (Rarely Used)

Pluggable Storage Engine. (Customized accordingly requirement)

Click Here For More details about above mention the storage Engine.

*.Check default storage Engine.
db.serverStatus().storageEngine;

*.Difference :
WiredTiger better write performance.
WiredTiger Support Compression.
WiredTiger is used for snapshots and checkpoints system.
WiredTiger uses document level concurrency, where as MMAPV1 uses collection level locking (i.e table level locking).
WiredTiger is not available on Solaris platform whereas MMPAV1 is.
MMPAv1 use for databware housing.

MongoDB Journaling

 It’s also part of storage : the journal is a log that helps the database recover in the event of a hard shutdown. There are several configurable options that allows the journal to strike a balance between performance and reliability that works for your particular use case.

In simple Words:

With journaling, MongoDB’s storage layer has two internal views of the data set:
Private view, used to write to the journal files,  and
Shared view, used to write to the data files: MongoDB first applies write operations to the private view.

In this process, a write operation occurs in mongod, which then creates changes in private view.
The first block is memory and the second block is ‘my disc’. After a specified interval, which is called a ‘journal commit interval’,  the private view writes those operations in journal directory (residing in the disc).

Once the journal commit happens, mongod pushes data into shared view. As part of the process, it gets written to actual data directory  from the shared view (as this process happens in background). The basic advantage is,we have a reduced cycle from 60 seconds to 200 milliseconds.

If you want to change the value of Interval commng and flush shared view follow below the link

For More details about Journaling: Click Here

MongoDB Checkpoint

 Yes checkpoint also available in MongoDB: Read my Previous Post Journaling clear the all deatils how the data will be written on physical files. Every 60 seconds or journal file reach 2 GB . whichever first

MongoDB GridFS

 GridFS is a versatile storage system that is suited to handling large files, such as those exceeding the 16 MB document size limit.

 

For more details about GridFs : Click Here

MongoDB User Creation Details

Now today give you a small demo for creating user, gives permission and getting user info.

# Create user
Syntax:
db.createuser(
{user : “mylogin” , pwd : “mylogin”,
roles : []
});

db.createuser(
{user : “mylogin” , pwd : “mylogin”,
roles : [{role : “userAdminAnyDatabase”, db : “admin”}]});     <—– Full admin level Permission on admin user

db.createuser(
{user : “mylogin” , pwd : “mylogin”,
roles : [
{ role : “read”, db : “admin”},                   <—– Read Permission on Local admin DB
{role : “readWrite” , db: “local”}               <—– Read/Write Permission on Local DB
] }
);

# Change User Password
use mylogin
db.changeUserPassword(“accountUser”, “test#123”)

# Roles
db.grantRolesToUser({ “<username>”,”roles” : [ { “role” : “assetsReader”, “db” : “assets” }]});  <—- Grant role to User

db.revokeRoleFromUser({});                    <———– Revoke role from User

Few RolesuserAadminAnydatabase,read, readWrite

 

# Userinfo getting
use admin
db.system.users.find();

db.getUsers();             <———- It’s also showing the details of all databases user

db.runCommand( { usersInfo: 1 } );                 <———— view all users of databases

db.runCommand(
{
usersInfo: { user: “Aman”, db: “home” },
showPrivileges: true,
showCredentials : true })

db.runCommand( { usersInfo: [ { user: “Aman”, db: “home” }, { user: “Tom”, db: “myApp” } ],
showPrivileges: true
} )

 

For More Detail about

User Creation: Click Here

User Roles: Click Here

User Privileges: Click Here

Manage User and Roles: Click Here

MongoDB Rename Collection

 Yes you can rename the collection if required but it is not supported on sharded collections.

db.collection.renameCollection() 
db.collection.renameCollection();

db.test_my.renamecollection(“test”);

use admin
db.adminCommand( { renameCollection: “mydb.test_my”, to: “mydb.test” } )

MongoDB Host and Port

 Get the detail of mongodb Host and mongodb runing on which port fo you get all these type of info from mongo shell.

getHostName(); # Hostname info

db.serverCmdLineOpts(); # Port info
db.serverCmdLineOpts().parsed.net.port

db.runCommand({whatsmyuri : 1}) # Its is showing both Hostname and Port

MongoDB Clone Collection

 Mongos does not support db.cloneCollection().

1st Method

db.cloneCollection(from, collection, query);

db.cloneCollection(‘mongodb.example.net:27017’, ‘profiles’,{ ‘active’ : true } )

2nd Method depricated since 3.0 version

db.collection.copyTo(“new collection name”); # But its depricated in 3.4 but it works.

3st Method With Mongodump

mongodump –db db-name –collection collection-name –archive=collection-name.archive

For more detail about Clone Collection: Click Here

MongoDB Statistics

 If you want to objects statistics like (No. of object, collections, datasize etc. Available) follow below the commands.

use database
db.stats();                             <—————- Get the database level statistics
db.technology.stats();          <—————- Get the  collection level statistics

db.runCommand( { collStats : “restaurant”, scale: 1024 } )

 

For more Detail about Stats:

Database Stats: Click Here

Collection Stats: Click Here

Index Stats: Click Here

MongoDB Profiler

Profiler collects fine grained data about MongoDB write operations, cursors, database commands on a running mongod instance.

You can enable profiling on a per-database or per-instance basis.

The profiler is off by default.

Profiler writes all the data it collects to the system.profile collection, which is a capped collection.

*.Profiling Levels

0 – the profiler is off, does not collect any data

1 – collects profiling data for slow operations only. By default slow operations are those slower than 100 milliseconds.

2 – collects profiling data for all database operations.

Enable Profiling :  db.setProfilingLevel(1, { slowms: 20 })

Disable Profiling :  db.setProfilingLevel(0)

Get Profiling Info: db.getProfilingStatus()

All details regarding performance issues related written in mongod.log file by default located in /var/log/messages/mongd/mongd.og

 

For more detail about profiling : ClickHere

How To setup Oracle RDA and Use

RDA captures data and provides Oracle support with a comprehensive picture of the customer's environment which aids in problem diagnosis. The goal of this article is to detail the steps on how to gather basic configuration, log and platform information from Oracle OpenSSO using the Remote Diagnostic Agent.

Instructions for UNIX/ZLinux Type Operating Systems

Choose or create a directory or area on your UNIX server. Make sure you have sufficient space for the RDA output (150MB). It does not matter where you create this directory or what it is named, but the same user that runs RDA must own it. Do not use a directory that contains an older version of RDA unless you have deleted the previous version of RDA first. If necessary, you can reuse prior setup files.

Note: The rda.zip creates a directory named "rda" containing all the required files when you extract it.

Installation Instruction

Do not extract the contents of the RDA archive on a Windows client first or you will have to remove the ^M characters from the end of each line in all of the shell scripts in order for them to run.

downloaded rda.zip file from oracle support as compitable your OS, I'm using UNIX server.

step 1.

Extract the .zip archive contents into a new directory, preserving the directory structure of the archive. Do not extract into a directory that contains an older RDA version. For example:

   unzip rda.zip

step 2.

Change directory to the 'rda' directory created by the above command, for example:

   cd rda

setp 3.

Make sure the RDA command 'rda.sh' is executable. To verify, enter the following command:

    ls -l rda.sh

If the script is not executable, update it using the command:

    chmod +x rda.sh

step 4.

The following command checks that you have a viable installation of RDA by checking that all file versions are as expected:

  ./rda.sh -cv

output like below
===================
Loading the file list ...
Checking the directory [D_RDA] . ...
Checking the directory [D_RDA] engine ...
Checking the directory [D_RDA_ADM] model ...
Checking the directory [D_RDA_CHK] APPS ...
Checking the directory [D_RDA_CHK] BI ...
Checking the directory [D_RDA_CHK] CGBU ...
Checking the directory [D_RDA_CHK] DB ...
Checking the directory [D_RDA_CHK] EM ...
Checking the directory [D_RDA_CHK] OFM ...
Checking the directory [D_RDA_CHK] TEST ...
Checking the directory [D_RDA_COL] APPS ...
Checking the directory [D_RDA_COL] BI ...
Checking the directory [D_RDA_COL] CGBU ...
Checking the directory [D_RDA_COL] CLOUD ...
Checking the directory [D_RDA_COL] DA ...
Checking the directory [D_RDA_COL] DB ...
Checking the directory [D_RDA_COL] EM ...
Checking the directory [D_RDA_COL] EXPLORER ...
Checking the directory [D_RDA_COL] OFM ...
Checking the directory [D_RDA_COL] OS ...
Checking the directory [D_RDA_COL] PGBU ...
Checking the directory [D_RDA_COL] RDA ...
Checking the directory [D_RDA_COL] SAMPLE ...
Checking the directory [D_RDA_COL] TOOL ...
Checking the directory [D_RDA_CSS] . ...
Checking the directory [D_RDA_DAT] . ...
Checking the directory [D_RDA_DFW] cv0200 ...
Checking the directory [D_RDA_INC] Convert/Common ...
Checking the directory [D_RDA_INC] Convert/DB/LOG ...
Checking the directory [D_RDA_INC] Convert/OFM/OIM ...
Checking the directory [D_RDA_INC] Convert/OS/INST ...
Checking the directory [D_RDA_INC] Convert/OS/OS ...
Checking the directory [D_RDA_INC] Convert/RDA/CONFIG ...
Checking the directory [D_RDA_INC] Convert/TOOL/ALERT ...
Checking the directory [D_RDA_INC] Convert/TOOL/COMPLY ...
Checking the directory [D_RDA_INC] IRDA ...
Checking the directory [D_RDA_INC] IRDA/CV0200 ...
Checking the directory [D_RDA_INC] RDA ...
Checking the directory [D_RDA_INC] RDA/Agent ...
Checking the directory [D_RDA_INC] RDA/Driver ...
Checking the directory [D_RDA_INC] RDA/Handle ...
Checking the directory [D_RDA_INC] RDA/Library ...
Checking the directory [D_RDA_INC] RDA/Limit ...
Checking the directory [D_RDA_INC] RDA/Local ...
Checking the directory [D_RDA_INC] RDA/Object ...
Checking the directory [D_RDA_INC] RDA/Operator ...
Checking the directory [D_RDA_INC] RDA/Request ...
Checking the directory [D_RDA_INC] RDA/SDCL ...
Checking the directory [D_RDA_INC] RDA/SDSL ...
Checking the directory [D_RDA_INC] RDA/Target ...
Checking the directory [D_RDA_INC] RDA/Token ...
Checking the directory [D_RDA_INC] RDA/UI ...
Checking the directory [D_RDA_INC] RDA/Value ...
Checking the directory [D_RDA_INC] RDA/Web ...
Checking the directory [D_RDA_MSG] charset ...
Checking the directory [D_RDA_MSG] desc ...
Checking the directory [D_RDA_MSG] en ...
Checking the directory [D_RDA_MSG] fr ...
Checking the directory [D_RDA_POD] en ...
No issues found

step 5.

Now run RDA here for Preinstallation checks and co-ordinate with SA for failed checks/fixes.

./rda.sh -T hcve

Output like below
==============

Processing HCVE tests ...
Available Pre-Installation Rule Sets:
   1.  Oracle Database 10g R1 (10.1.0) Preinstall (Linux)
   2.  Oracle Database 10g R2 (10.2.0) Preinstall (Linux)
   3.  Oracle Database 11g R1 (11.1) Preinstall (Linux)
   4.  Oracle Database 11g R2 (11.2.0) Preinstall (Linux)
   5.  Oracle Database 12c R1 (12.1.0) Preinstallation (Linux)
   6.  Oracle Database 12c R2 (12.2.0) Preinstallation (Linux)
   7.  Oracle Database 18c Preinstallation (Linux)
   8.  Oracle Database 19c Preinstallation (Linux)
   9.  Oracle Identity and Access Management PreInstall Check: Oracle Identity
       and Access Management 11g Release 2 (11.1.2) Linux
  10.  Oracle JDeveloper PreInstall Check: Oracle JDeveloper 11g Release 2
       (11.1.2.4) Linux
  11.  Oracle JDeveloper PreInstall Check: Oracle JDeveloper 12c (12.1.3)
       Linux
  12.  OAS PreInstall Check: Application Server 10g R2 (10.1.2) Linux
  13.  OAS PreInstall Check: Application Server 10g R3 (10.1.3) Linux
  14.  OFM PreInstall Check: Oracle Fusion Middleware 11g R1 (11.1.1) Linux
  15.  OFM PreInstall Check: Oracle Fusion Middleware 12c (12.1.3) Linux
  16.  OFM PreInstall Check: Oracle Fusion Middleware 12c (12.2.1.3.0) Linux
  17.  Oracle Forms and Reports PreInstall Check: Oracle Forms and Reports 11g
       Release 2 (11.1.2) Linux
  18.  Portal PreInstall Check: Oracle Portal Generic
  19.  IDM PreInstall Check: Identity Management 10g (10.1.4) Linux
  20.  BIEE PreInstall Check: Business Intelligence Enterprise Edition 11g
       (11.1.1) Linux
  21.  EPM PreInstall Check: Enterprise Performance Management Server (11.1.2)
       Generic
  22.  Oracle Enterprise Manager Cloud Control PreInstall Check: Oracle
       Enterprise Manager Cloud Control 12c Release 4 (12.1.0.4) Linux
  23.  Oracle E-Business Suite Release 11i (11.5.10) Preinstall (Linux x86 and
       x86_64)
  24.  Oracle E-Business Suite Release 12 (12.1.1) Preinstall (Linux x86 and
       x86_64)
  25.  Oracle E-Business Suite Release 12 (12.2.0) Preinstall (Linux x86_64)
Available Post-Installation Rule Sets:
  26.  RAC 10G DB and OS Best Practices (Linux)
  27.  Data Guard Postinstall (Generic)
  28.  WLS PostInstall Check: WebLogic Server 11g (10.3.x) Generic
  29.  WLS PostInstall Check: WebLogic Server 12c (12.x) Generic
  30.  Portal PostInstall Check: Oracle Portal Generic
  31.  OC4J PostInstall Check: Oracle Containers for J2EE 10g (10.1.x) Generic
  32.  SOA PostInstall Check: Service-Oriented Architecture 11g and Later
       Generic
  33.  OSB PostInstall Check: Service Bus 11g and Later Generic
  34.  Oracle Forms 11g Post Installation (Generic)
  35.  Oracle Enterprise Manager Agent 12c Post Installation (Generic)
  36.  Oracle Management Server 12c Post Installation (Generic)
  37.  Network Charging and Control Database Post Installation (Generic)
Enter the HCVE rule set number or 0 to cancel the test
Press Return to accept the default (0)
> 8
Performing HCVE checks ...
Enter value for < Planned ORACLE_HOME location >
Press Return to accept the default (/u01/app/oracle/product/12.1.0/db_1)
>

Test "Oracle Database 19c Preinstallation (Linux)" executed at 16-Jul-2022 21:36:39
Test Results
~~~~~~~~~~~~
(Note : Current Health Check rulesets do not have logic to support checks against Oracle Cloud or Exadata environments. If your product is installed in such an environment some test results will be erroneous. Refer instead to the product documentation rather than rely on the data below.)
ID     NAME                 RESULT  VALUE
====== ==================== ======= ==========================================
A00100 OS Certified?        FAILED  Not certified [Oracle Linux version]
A01020 User in /etc/passwd? PASSED  userOK
A01040 Group in /etc/group? PASSED  GroupOK
A01050 Enter ORACLE_HOME    RECORD  /u01/app/oracle/product/12.1.0/db_1
A01060 ORACLE_HOME Valid?   PASSED  OHexists
A01070 O_H Permissions OK?  PASSED  CorrectPerms
A01410 oraInventory Permiss PASSED  oraInventoryOK
A01420 Other OUI Up?        PASSED  NoOtherOUI
A01430 Got Software Tools?  PASSED  ld_nm_ar_make_found
A01440 Other O_Hs in PATH?  FAILED  OratabEntryInPath
A02010 Umask Set to 022?    PASSED  UmaskOK
A02030 Limits Processes     PASSED  Adequate
A02040 Limits Stacksize     PASSED  Adequate
A02050 Limits Descriptors   PASSED  Adequate
A02100 LDLIBRARYPATH Unset? FAILED  IsSet
A02170 JAVA_HOME Unset?     PASSED  UnSet
A03100 RAM (in MB)          PASSED  5982
A02210 Kernel Parameters OK PASSED  KernelOK
A02300 Tainted Kernel?      PASSED  NotVerifiable
A03010 Temp Adequate?       PASSED  TempSpaceOK
A03020 Disk Space OK?       PASSED  DiskSpaceOK
A03050 Swap (in MB)         RECORD  3999
A03100 RAM (in MB)          PASSED  5982
A03150 SwapToRam OK?        FAILED  SwapToRamTooLow
A03500 Network              PASSED  Connected
A03510 IP Address           RECORD  192.168.219.2
A03530 Domain Name          RECORD  NotFound
A03540 /etc/hosts Format    FAILED  No entry found
A03550 DNS Lookup           FAILED  nslookup host.domain
A03600 ip_local_port_range  PASSED  RangeOK
A04305 OL7 Server RPMs OK?  SKIPPED NotOL7
A04315 RHEL7 Server RPMs OK SKIPPED NotRedHat
A04321 SLES12 Server RPMs O SKIPPED NotSuSE
A04325 SLES15 Server RPMs O SKIPPED NotSuSE
Result file: output/collect/DB_HCVE_A_DB19c_lin_res.htm

In my case below the file location you just ftp on local machine and check the htm ext. file. 

/home/oracle/software/rda/output/collect

-rw-r----- 1 oracle oinstall 19312 Jul 16 21:36 DB_HCVE_A_DB19c_lin_res.txt

-rw-r----- 1 oracle oinstall 29553 Jul 16 21:36 DB_HCVE_A_DB19c_lin_res.htm

Reference:

Remote Diagnostic Agent (RDA) - Getting Started (Doc ID 314422.1)

Query to find ASM Freespace with Redundancy

Below Query will show how much space is available to use in case of High or Normal Redundancy

TOTAL_MB:- Refers to Total Capacity of the Diskgroup

FREE_MB :- Refers to raw Free Space Available in Diskgroup in MB.

FREE_MB = (TOTAL_MB – (HOT_USED_MB + COLD_USED_MB))

REQUIRED_MIRROR_FREE_MB :- Indicates how much free space is required in an ASM disk group to restore redundancy after the failure of an ASM disk or ASM failure group.In exadata it is the disk capacity of one failure group.

USABLE_FILE_MB :- Indicates how much space is available in an ASM disk group considering the redundancy level of the disk group.

Its calculated as :-

USABLE_FILE_MB=(FREE_MB – REQUIRED_MIRROR_FREE_MB ) / 2 –> For Normal Redundancy

USABLE_FILE_MB=(FREE_MB – REQUIRED_MIRROR_FREE_MB ) / 3 –> For High Redundancy

Query to Run:

column total format 999,999 Heading "Total(G)"

column free format 999,999 Heading "Free (G)"

column Mirror_GB format 999,999 Heading "Space Used |for Mirroring(G)"

column Usable_GB format 999,999 Heading "Space Available |to Use(G)"

column pct format 999.0 Heading "% Free |in DG" 

column pct2 format 999.0 Heading "Real % Free |in DG" 

column type format a10

column name format a20

set linesize 200

set colsep '|'

prompt

Prompt "NOTE **** Incase of High or Normal Redundancy the Usable Space is lower than actual shown because of Mirroring *****"

prompt

select name,type, TOTAL_MB/1024 total, FREE_MB/1024 free, REQUIRED_MIRROR_FREE_MB/1024 Mirror_GB, USABLE_FILE_MB/1024 Usable_GB ,100-((total_MB-FREE_MB)/total_mb)*100 pct, 100-((total_MB-USABLE_FILE_MB)/total_mb)*100 pct2  from v$asm_diskgroup;

Sample Output :

"NOTE **** Incase of High or Normal Redundancy the Usable Space is lower than actual shown because of Mirroring *****"

                    |          |        |        |     Space Used |Space Available |% Free |Real % Free

NAME                |TYPE      |Total(G)|Free (G)|for Mirroring(G)|       to Use(G)|  in DG|       in DG

--------------------|----------|--------|--------|----------------|----------------|-------|------------

DATA1              |HIGH      | 260,496|  57,951|          14,472|          14,493|   22.2|         5.6

REDO1              |HIGH      |  65,124|  33,322|           3,618|           9,901|   51.2|        15.2

oracle : Scheduling ASH reports through Crontab

I was asked to capture ASH reports every 5 minutes for an ongoing Database issue. Below is the process to schedule it through Crontab

Script to capture the ASH reports every 5 Minutes

$ cat ash.ksh

#!/bin/bash

export TZ=US/Central

dateString=`date +%d-%b-%Y_%H:%M:%S`

sqlplus -s / as sysdba << EOD1

define report_type = 'html'

define begin_time = '-5'

define duration = ''

define report_name = '/u01/user/local/reports/ashrpt.${dateString}.html'

@?/rdbms/admin/ashrpt

exit

EOF

To run it through Cron

0,5,10,15,20,22,25,30,32,35,40,45,50,55 * * * * /u01/user/local/ash/ash.ksh > /u01/user/local/reports/log/ash_collect.log 1>/dev/null 2>&1

ORACLE RAC : TERMINATING THE INSTANCE DUE TO ERROR 304

 After refreshing my QA database using RMAN DUPLICATE, my instance startup was failing with the below error

USER (ospid: 60897): terminating the instance due to error 304

Instance terminated by USER, pid = 60897

Wed Mar 06 02:14:34 2019

Starting ORACLE instance (normal)

Looking into the spfile, I noticed the database was pulling wrong instance_number and thread numbers even though the DB configuration was correct

$ srvctl config database -d oradb

Database unique name: oradb

Database name: oradb

Oracle home: /opt/app/oradb/oracle/product/11.2.0.4

Oracle user: oradb

Spfile: +oradb_DATA/oradb/spfileoradb.ora

Domain: db.abc.com

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools: oradb

Database instances: oradb1,oradb2

Disk Groups: oradb_DATA,oradb_FRA,oradb_REDO1,oradb_REDO2

Mount point paths: 

Services: oradb_1_2.db.abc.com,oradb_2_1.db.abc.com

Type: RAC

Database is administrator managed

From the pfile I created from the current spfile I could see 

*.instance_number=2

*.thread=2

To resolve this, bring down the complete database and just start the failing instance, in our case instance 1

srvctl start instance -d oradb -i oradb1

Once the instance is started, login to SQL and run below

SQL> alter system set instance_number=1 scope=spfile sid='oradb1';

System altered.

SQL> alter system set thread=1 scope=spfile sid='oradb1';

System altered.

SQL> alter system set undo_tablespace='UNDO01' sid='oradb1';

System altered.

shutdown the instance and start the complete database

SQL> shutdown immediate

srvctl start database -d oradb

Hope this resolves your issue. 

ORA-12537: TNS:connection closed - Oracle RAC 11g and above

 The client was getting Below error while connecting to the RAC database

sqlplus test@RACDB_1

SQL*Plus: Release 11.2.0.4.0 - Production on Tue Sep 22 11:34:07 2020

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

ERROR:

ORA-12537: TNS:connection closed

Issue : 

The Oracle Binary permissions got changed and were not allowing the connections

Current Permissions under Oracle Home

[oracle@Node1 ~]$ ls -lrt $ORACLE_HOME/bin/oracle

-rwxr-sr-x 1 oracle asmadmin 243043788 Jun  2 01:12 oracle

It should be set to 6751 and should look like "-rwsr-s--x"

But doing chmod 6751 on oracle binary was not setting the correct permissions

[oracle@Node1 bin]$ chmod 6751 oracle

[oracle@Node1 bin]$ ls -lrt $ORACLE_HOME/bin/oracle

-rwsr-x--x 1 oracle asmadmin 243043788 Jun  2 01:12 oracle

Running below as RDBMS database Owner user helped, in this case, "oracle" user

1) Stop the database instance where the permissions got changed

 srvctl stop instance -d RACDB -i RACDB1

 2) Run as Oracle Database owner, in this case its Oracle OS user.  

[oracle@Node1 ~]$ $GRID_HOME/bin/setasmgidwrap o=$ORACLE_HOME/bin/oracle

3) The permissions got changed and resolved the connection issues

[oracle@Node1 ~]$ ls -lrt $ORACLE_HOME/bin/oracle

-rwsr-s--x 1 oracle asmadmin 243043788 Jun  2 01:12 oracle

Hope this resolves your issue..