Multiplexing Controlfile in ASM

 Manual Method:

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

1.

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

control_files                        string      +DATA/racdb/controlfile/current.260.979677429

[oracle@rac1 ~]$ srvctl stop database -d racdb

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

2.

ASMCMD> lsdg

State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name

MOUNTED  NORMAL  N         512   4096  1048576     30717    27208             1171           13018              0             N  DATA/

MOUNTED  NORMAL  N         512   4096  1048576      3069     2143              309             917              0             N  OCR/

cd OCR

mkdir racdb

cd racdb

mkdir controlfile

ASMCMD> pwd

+DATA/RACDB/CONTROLFILE

ASMCMD> cp +DATA/RACDB/CONTROLFILE/Current.260.979677429 +OCR/racdb/controlfile/current

copying +DATA/RACDB/CONTROLFILE/Current.260.979677429 -> +OCR/racdb/controlfile/current

ASMCMD> cd +OCR/racdb/controlfile/

ASMCMD> ls -l

Type         Redund  Striped  Time             Sys  Name

                                               N    current => +OCR/ASM/CONTROLFILE/current.256.1144794611

--asm_alert.log

Sun Aug 13 22:27:42 2023

SQL> /* ASMCMD */alter diskgroup /*ASMCMD*/ "OCR" add directory '+OCR/racdb'

SUCCESS: /* ASMCMD */alter diskgroup /*ASMCMD*/ "OCR" add directory '+OCR/racdb'

Sun Aug 13 22:27:56 2023

SQL> /* ASMCMD */alter diskgroup /*ASMCMD*/ "OCR" add directory '+OCR/racdb/controlfile'

SUCCESS: /* ASMCMD */alter diskgroup /*ASMCMD*/ "OCR" add directory '+OCR/racdb/controlfile'

--Output

ASMCMD> pwd

+OCR/racdb/controlfile

ASMCMD> ls -l

Type         Redund  Striped  Time             Sys  Name

                                               N    current => +OCR/ASM/CONTROLFILE/current.256.1144794611

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

3.

[oracle@rac1 ~]$ srvctl start database -d racdb -o nomount

show controlfile

alter system set control_files='+DATA/racdb/controlfile/current.260.979677429','+OCR/racdb/controlfile/current' scope=spfile sid='*';

srvctl stop database -d racdb

[oracle@rac1 ~]$ srvctl start database -d racdb

[oracle@rac1 ~]$ srvctl status database -d racdb

Instance racdb1 is running on node rac1

Instance racdb2 is running on node rac2

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

control_files                        string      +DATA/racdb/controlfile/current.260.979677429, +OCR/racdb/controlfile/current

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

4. clean controlfile from old DG if you do moving.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

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

RMAN CONTROLFILE Multiplexing:

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

1. Identify the location of the current controlfile:

SQL> select name from v$controlfile;

NAME

--------------------------------------------------------------------------------

+DATA/racdb/controlfile/current.260.979677429

2. Shutdown the database and start the instance:

    SQL> shutdown normal

    SQL> startup nomount

3. Use RMAN to duplicate the controlfile:

    $ rman nocatalog

    RMAN>connect target

    RMAN>restore controlfile to '<DISKGROUP_NAME>' from '<OLD_PATH>';

    RMAN> restore controlfile to '+OCR' from '+DATA/racdb/controlfile/current.260.979677429';

4. On the ASM instance, identify the name of the controlfile:

    Using ASMCMD:

    $ asmcmd

    ASMCMD> cd OCR

    ASMCMD> find -t controlfile . *

 Changing the current directory to the diskgroup where the controlfile was created will speed the search.

    Output:

ASMCMD> find -t controlfile . *

WARNING:option 't' is deprecated for 'find'

please use 'type'

+OCR/RACDB/CONTROLFILE/current.256.1144796825

5. On the database side:

##OPTIONAL STEP spfile and init file manual changes.

            * Modify init.ora or spfile, adding the new path to parameter control_files.

            * if using init<SID>.ora, just modify the control_files parameter and restart the database.

            * If using spfile,

            1) startup nomount the database instance

            2) alter system set control_files='+DG1/P10R2/CONTROLFILE/backup.308.577785757','/oradata2/102b/oradata/P10R2/control01.ctl' scope=spfile;

            For RAC instance:

            alter system set control_files='+DATA/racdb/controlfile/current.260.979677429','+OCR/RACDB/CONTROLFILE/current.256.1144796825' scope=spfile sid='*';

            3) shutdown immediate

        * start the instance.

            Verify that new control file has been recognized. If the new controlfile was not used, the complete procedure needs to be repeated.

6. 

[oracle@rac1 ~]$ srvctl start database -d racdb

[oracle@rac1 ~]$ srvctl status database -d racdb

Instance racdb1 is running on node rac1

Instance racdb2 is running on node rac2

What is MongoDB ?

MongoDB is open source database system. It’s a document database with the scalability and flexibility that you want with the querying and indexing that you need.

Its support to Dyanmic schema, using a map-reduce, GeoSpatial support the main feature.

• MongoDB stores data in flexible, JSON-like documents, meaning fields can vary from document to document and data structure can be changed over time
• The document model maps to the objects in your application code, making data easy to work with
• Ad hoc queries, indexing, and real time aggregation provide powerful ways to access and analyze your data
• MongoDB is a distributed database at its core, so high availability, horizontal scaling, and geographic distribution are built in and easy to use
• MongoDB is free and open-source, published under the GNU Affero General Public License

 

For more detail about Key Feature: Click Here

Overview of MongoDB Collection and Documnets

 MongoDB works on concept of collection and document.

 

RDBMS	MongoDB
Database	Database
Table	Collection
Tuple/Row	Document
column	Field
Table Join	Embedded Documents
Primary Key	Primary Key (Default key _id provided by mongodb itself)

 

Database Server and Client
Mysqld/Oracle	mongod
mysql/sqlplus	mongo

Advantage of mongodb as compare to SQL database

 The MongoDB data bases on the JSON (Java Script Object Notation) format. JSON allows for the transfer of data

between web applications and servers using a human readable format.

Before JSON, XML was used for this.
JSON is defined in MongoDB’s BSON (Binary JSON). The binary format of BSON provides reliability and greater efficiency when it comes to speed and storage space.

 

JSON & BSON
JSON can only represent a subset of the types supported by BSON.

For More Details about JSON Click Here and BSON Click Here

MongoDB Installation

 MongoDB Provide two types of software.

1. Community Edition :   Its For freeware no mongo support available on this.
2. Enterprise Edition : Its License Based.

For More Detail about Installation and Packages and Environment Support : Click Here

For any query regarding MongoDB installation/Upgrade feel free contact to Oracle-Help Team.

Terminology with MongoDB

 

Database

Database is a physical container for collections. Each database gets its own set of files on the file system. A single MongoDB server typically has multiple databases.

Collection

A grouping of MongoDB documents. A collection is the equivalent of an RDBMS table. A collection exists within a single database. Collections do not enforce a schema.

Document

MongoDB stores data records as BSON documents. BSON is a binary representation of JSON documents, though it contains more data types than JSON.

CRUD Operations

 CRUD operations create, read, update, and delete documents as same like in RDBMS (Select and DMLs).

 

Firstly knows how to create database in mongoDB

MongoDB use DATABASE_NAME is used to create database. The command will create a new database if it doesn’t exist, otherwise it will return the existing database.

Execute simple command : > use TEST

Switch the Database :  > use admin

check your currently selected database : > db

List of Databases available with size  : > show databases / show dbs     <———- But this command is used directly when your authorization is disable. Later to discuss about authorization.

 

 

Create Collection 

There is two type of collection normal collection and capped collection. Capped collection is auto purge which is used for like a auditing/Oplog later to discuss about oplog which is part of replication. And mongoDB is schema less so when you create a database its highly recommended to create a any single collection, if you not follow this after switching the newly created database to another one your newly created database is flush i.e not visible when you check with ” > show dbs “ command.

Below the syntax of Collection

> db.createCollection(name, options);

> use Test
switched to db Test

db.createCollection(“student”)

> db.createCollection(“Audit”, { capped : true, autoIndexId : true, size : 6142800, max : 10000 } )  <—– Creating a Capped Collection Syntax

You can check the created collection

> show collections      

 

Document Insert 

Above mention the command also working but not need to create collection . In MongoDB, you don’t need to create collection. MongoDB creates collection automatically, when you insert some document

Syntax: > db.Collection.insert ({“filed” : “value”});

> use Test

> db.student.insert({ “name” : “Aman”});

> db.student.insert({“name” : “Steve”, “rollno” : 101,”contact” : “99xxxxxxxx”});

> db.student.insert({“name” : “Kate”, “rollno” : 101,”contact” : “98xxxxxxxx”});

> db.student.insert({“name” : “Tarun”, “rollno” : 102,”contact” : “97xxxxxxxx”});

> db.student.insert({“name” : “Dan”, “rollno” : 103,”contact” : “96xxxxxxxx”});

> db.student.insert({“name” : “Tom”, “rollno” : 104,”contact” : “95xxxxxxxx”});

> db.student.insert({“name” : “Mohan”, “rollno” : 105,”contact” : “93xxxxxxxx”});

> db.student.insert({“name” : “Tin”, “rollno” : 104,”contact” : “92xxxxxxxx”});

> db.student.insert({“name” : “Vince”, “rollno” : 105,”contact” : “91xxxxxxxx”});

 

 

Document Read

You can just check the details available in collections.

Syntax: db.collection.find();

> db.student.find()    <——- It’s showing complete collection 

> db.student.findOne()    <——– Showing only single record.

> db.student.find().pretty()    <——- Display the results in a formatted.

Later to discuss about all opetions like sorting, orderby, less then, greater than etc.

 

 

Document Update

In MongoDB update document into a collection. The update() method updates the values in the existing document while the save() method replaces the existing document with the document passed in save() method.

Syntax: db.collection.update(selection_data, update_data)

> db.student.update.({“name” : “Kate”},{$set:{“rollno”: 104}});

> db.student.find();

 

Document Delete

Deleting a document from collection. remove() clause used for this.

Syntax : >db.collection.remove(DELLETION_DATA)

 > db.student.remove({ “rollno” : 104})      <———- Now Its removing the all entries which is belongs to 104

db.student.find()

> db.student.remove({ “rollno” : 105,1})   <——— Now Its removing only the first record which is belongs to 105

db.student.find()

> db.student.remove() <——— Remove all he documents from the collection.

db.student.find()

For More Detail about the CRUD operations: Click Here

Drop Database Drop Collection

 Drop collection from database in mongoDB

db.collection.drop() is used to drop a collection from the database.

Syntax: db.collection.drop()

use TEST

show collections     <——— check the available collections into your database TEST.

db.student.drop()    <——– Check and verify the student collection has been drop from TEST Database.

 

Drop Database in mongoDB

db.dropDatabase() command is used to drop a existing database.

Syntax: db.dropDatabase()

Example:

show dbs     <—– check the database list and select you want to drop.

use TEST      <—– Put the db in usable state.

db.dropDatabase()    <—— Now the Test database has been drop check & verify with show dbs command.

Where Condition in MongoDB

 Below some example of Where condition in MongoDB.

pretty: using for just formatting of data view.

Equal To:

{<key>:<value>}

          > db.student.find({“name”: “Mohan” }).pretty()

Less Than:

{<key>:{$lt:<value>}}

          > db.student.find({“marks”:{$lt:50}}).pretty()

Less Than Equals:

{<key>:{$lte:<value>}}

          > db.student.find({“marks”:{$lte:50}}).pretty()

Greater Than:

{<key>:{$gt:<value>}}

          > db.student.find({“marks”:{$gt:50}}).pretty()

Greater Than Equals:

{<key>:{$gte:<value>}}

          > db.student.find({“marks”:{$gte:50}}).pretty()

Not Equals:

{<key>:{$ne:<value>}}

          > db.student.find({“marks”:{$ne:50}}).pretty()

AND Condition in MongoDB

Multiple keys by separating them by ‘,’ then MongoDB treat as AND condition.

          > db.student.find({$and:[{“rollno”:105},{“name”: “Dan”}]})

OR Condition in MongoDB

          > db.student.find({$or:[{“rollno”:104},{“rollno”: 105}]})

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