SQL to MongoDB Mapping

Terminology and Concepts

SQL Terms/ConceptsMongoDB Terms/Concepts
databasedatabase
tablecollection
rowdocument or BSON document
columnfield
indexindex
table joins$lookup, embedded documents
primary keySpecify any unique column or column combination as primary key.primary keyIn MongoDB, the primary key is automatically set to the _id field.
aggregation (e.g. group by)aggregation pipelineSee the SQL to Aggregation Mapping Chart.
SELECT INTO NEW_TABLE$outSee the SQL to Aggregation Mapping Chart.
MERGE INTO TABLE$merge (Available starting in MongoDB 4.2)See the SQL to Aggregation Mapping Chart.
UNION ALL$unionWith (Available starting in MongoDB 4.4)
transactionstransactions

Executables

MongoDB MySQL Oracle Informix DB2

Database Server mongod  mysqld oracle IDS DB2 Server

Database Client mongo  mysql sqlplus DB-Access DB2 Client

Create, Alter and Drop

Insert

Select

Update Records

Delete Records

MongoDB Collection

You can create a collection (Table) using the command line or GUI.

Here we hit the first error.

As you can see in the above error. You can’t create under the ‘admin’.

You can insert the data into collection (table) from json or csv etc.

Never to miss some important points here.

If you note it carefully from the below screenshot, you will notice that a collection(table) can be export (as you can see the arrow below 1. marked red) and even can import file or document. As already said you can import the data from csv or JSON too.

Below screenshot reminded me about the MySQL database. That also got the same options.

Data Guard Broker

 Data Guard properties more details:



$ dgmgrl [-silent | -echo] [username/password[@connect_identifier] [dgmgrl_command]]
$ dgmgrl  /
$ dgmgrl sys/pwd
$ dgmgrl sys/pwd@oltp
$ dgmgrl sys/test@dgprimary "show database 'prod'"

$ dgmgrl -logfile observer.log / "stop observer"
$ dgmgrl -silent sys/test@dgprimary "show configuration verbose"
$ dgmgrl  / "show configuration verbose"

=============
ADD - Adds a standby database to the broker configuration.
DGMGRL> ADD DATABASE db_name [AS CONNECT IDENTIFIER IS conn_identifier] [MAINTAINED AS {PHYSICAL|LOGICAL}];
DGMGRL> ADD DATABASE 'testdb' AS CONNECT IDENTIFIER IS testdb MAINTAINED AS PHYSICAL;
DGMGRL> ADD DATABASE 'logdb' AS CONNECT IDENTIFIER IS logdb MAINTAINED AS LOGICAL;
DGMGRL> ADD DATABASE 'devdb' AS CONNECT IDENTIFIER IS devdb.foo.com;

=============
CONNECT - Connects to an Oracle database instance.
DGMGRL> CONNECT username/password[@connect_identifier]
DGMGRL> CONNECT /
DGMGRL> CONNECT sys;
DGMGRL> CONNECT sys@test;
DGMGRL> CONNECT sys/pwd;
DGMGRL> CONNECT sys/pwd@dwh;
DGMGRL> CONNECT /@dwh;
$dgmgrl connect sys

=============
CONVERT - Converts a database from one type to another (from Oracle 11g).
DGMGRL> CONVERT DATABASE database_name TO {SNAPSHOT STANDBY|PHYSICAL STANDBY};
DGMGRL> CONVERT DATABASE 'devdb' to SNAPSHOT STANDBY;
DGMGRL> CONVERT DATABASE 'devdb' to PHYSICAL STANDBY;

=============
CREATE - Creates a broker configuration.
DGMGRL> CREATE CONFIGURATION config_name AS PRIMARY DATABASE IS db_name CONNECT IDENTIFIER IS conn_ident;
DGMGRL> CREATE CONFIGURATION 'dg' AS PRIMARY DATABASE IS 'prod' CONNECT IDENTIFIER IS prod.foo.com;
DGMGRL> CREATE CONFIGURATION 'dg_test' AS PRIMARY DATABASE IS 'test' CONNECT IDENTIFIER IS test;

=============
DISABLE - Disables a configuration, a database, or fast-start failover (FSFO).
DGMGRL> DISABLE CONFIGURATION;
DGMGRL> DISABLE CONFIGURATION;



DGMGRL> DISABLE DATABASE database_name;
DGMGRL> DISABLE DATABASE 'devdb';



DGMGRL> DISABLE FAST_START FAILOVER [FORCE | CONDITION condition];
DGMGRL> DISABLE FAST_START FAILOVER;
DGMGRL> DISABLE FAST_START FAILOVER FORCE;
DGMGRL> DISABLE FAST_START FAILOVER CONDITION '1578';

=============
EDIT - Edits a configuration, database, or instance.
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS {MaxProtection|MaxAvailability|MaxPerformance};
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXPROTECTION;
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXPERFORMANCE;


DGMGRL> EDIT CONFIGURATION SET PROPERTY property_name = value;
DGMGRL> EDIT CONFIGURATION SET PROPERTY FastStartFailoverThreshold = 45;
DGMGRL> EDIT CONFIGURATION SET PROPERTY FastStartFailoverAutoReinstate = FALSE;
DGMGRL> EDIT CONFIGURATION SET PROPERTY FastStartFailoverAutoReinstate = TRUE;
DGMGRL> EDIT CONFIGURATION SET PROPERTY BYSTANDERSFOLLOWROLECHANGE= 'NONE';


DGMGRL> EDIT DATABASE database_name SET PROPERTY property_name = value;
DGMGRL> EDIT DATABASE devdb SET PROPERTY 'LogArchiveFormat'='log_%t_%s_%r_%d.arc';
DGMGRL> EDIT DATABASE prodb SET PROPERTY LogXptMode=SYNC;
DGMGRL> EDIT DATABASE prodb SET PROPERTY LogXptMode=ASYNC;
DGMGRL> EDIT DATABASE prodb SET PROPERTY LogXptMode=ARCH;
DGMGRL> EDIT DATABASE devdb SET PROPERTY LogShipping=OFF;
DGMGRL> EDIT DATABASE devdb SET PROPERTY LogShipping=ON;
DGMGRL> EDIT DATABASE prodb SET PROPERTY LogArchiveTrace=8;
DGMGRL> EDIT DATABASE prodb SET PROPERTY NetTimeout=60;
DGMGRL> EDIT DATABASE devdb SET PROPERTY 'ReopenSecs'=300;
DGMGRL> EDIT DATABASE prodb SET PROPERTY ArchiveLagTarget=1200;
DGMGRL> EDIT DATABASE prodb SET PROPERTY FastStartFailoverTarget='standby_name';
DGMGRL> EDIT DATABASE devdb SET PROPERTY 'StandbyArchiveLocation'='/oradata/archive/';
DGMGRL> EDIT DATABASE devdb SET PROPERTY 'DbFileNameConvert' = '/u01/od01/datafile/, /oradisk/od01/datafile/';
DGMGRL> EDIT DATABASE testdb SET PROPERTY DelayMins='720';
DGMGRL> EDIT DATABASE prodb SET PROPERTY RedoCompression ='ENABLE'
DGMGRL> EDIT DATABASE prodb SET PROPERTY RedoCompression ='DISABLE'
DGMGRL> EDIT DATABASE testdb SET PROPERTY LogArchiveMinSucceedDest =1


DGMGRL> EDIT DATABASE database_name RENAME TO new database_name;
DGMGRL> EDIT DATABASE 'devdbb' RENAME TO 'devdb';


DGMGRL> EDIT DATABASE database_name SET STATE = state [WITH APPLY INSTANCE = instance_name];
DGMGRL> EDIT DATABASE devdb SET STATE='READ-ONLY';
DGMGRL> EDIT DATABASE devdb SET STATE='OFFLINE';
DGMGRL> EDIT DATABASE devdb SET STATE='APPLY-OFF';
DGMGRL> EDIT DATABASE devdb SET STATE='APPLY-ON';
DGMGRL> EDIT DATABASE devdb SET STATE='TRANSPORT-OFF';
DGMGRL> EDIT DATABASE devdb SET STATE='TRANSPORT-ON';
DGMGRL> EDIT DATABASE prodb SET STATE='LOG-TRANSPORT-OFF';
DGMGRL> EDIT DATABASE devdb SET STATE='ONLINE' WITH APPLY INSTANCE=devdb2;


DGMGRL> EDIT INSTANCE instance_name [ON DATABASE database_name] SET AUTO PFILE [={init_file_path|OFF}];
DGMGRL> EDIT INSTANCE 'devdb1' ON DATABASE 'devdb' SET AUTO PFILE='initdevdb1.ora';

DGMGRL> EDIT INSTANCE instance_name [ON DATABASE database_name] SET PROPERTY property_name = value;
DGMGRL> EDIT INSTANCE * ON DATABASE database_name SET PROPERTY property_name = value;
DGMGRL> EDIT INSTANCE 'proddb' ON DATABASE 'proddb' SET PROPERTY 'StandbyArchiveLocation'='/oradata/arch/';

=============
ENABLE - Enables a configuration, a database, or fast-start failover (FSFO).
DGMGRL> ENABLE CONFIGURATION;
DGMGRL> ENABLE CONFIGURATION;



DGMGRL> ENABLE DATABASE database_name;
DGMGRL> ENABLE DATABASE 'devdb';



DGMGRL> ENABLE FAST_START FAILOVER [CONDITION condition];
DGMGRL> ENABLE FAST_START FAILOVER;
DGMGRL> ENABLE FAST_START FAILOVER CONDITION '1578';
DGMGRL> ENABLE FAST_START FAILOVER CONDITION "Stuck Archiver";
DGMGRL> ENABLE FAST_START FAILOVER CONDITION 'Corrupted Controlfile';
DGMGRL> ENABLE FAST_START FAILOVER CONDITION 'Corrupted Dictionary';
DGMGRL> ENABLE FAST_START FAILOVER CONDITION 'Inaccessible Logfile';

=============
EXIT - Exits the program.
DGMGRL> EXIT;

=============
FAILOVER - Changes a standby database to be the primary database.
DGMGRL> FAILOVER TO standby_database_name [IMMEDIATE]
DGMGRL> FAILOVER TO "testdb";
DGMGRL> FAILOVER TO "snapdb" IMMEDIATE;

=============
HELP - Displays description and syntax for a command.
DGMGRL> HELP [command];
DGMGRL> HELP REINSTATE
DGMGRL> HELP EDIT

=============
QUIT - Exits the program.
DGMGRL> QUIT;

=============
REINSTATE - Changes a database marked for reinstatement into a viable standby.
DGMGRL> REINSTATE DATABASE database_name;
DGMGRL> REINSTATE DATABASE prim1;

=============
REM - Comment to be ignored by DGMGRL.
DGMGRL> REM [comment];

=============
REMOVE - Removes a configuration, Oracle database, or instance.
DGMGRL> REMOVE CONFIGURATION [PRESERVE DESTINATIONS];
DGMGRL> REMOVE CONFIGURATION;
DGMGRL> REMOVE CONFIGURATION PRESERVE DESTINATIONS;


DGMGRL> REMOVE DATABASE database_name [PRESERVE DESTINATIONS];
DGMGRL> REMOVE DATABASE devdb;
DGMGRL> REMOVE DATABASE standby PRESERVE DESTINATIONS;



DGMGRL> REMOVE INSTANCE instance_name [ON DATABASE database_name];
DGMGRL> REMOVE INSTANCE inst1 ON DATABASE racdb;

=============
SHOW - Displays information about a configuration, database, instance or FSFO.
DGMGRL> SHOW CONFIGURATION [VERBOSE];
DGMGRL> SHOW CONFIGURATION;
DGMGRL> SHOW CONFIGURATION VERBOSE;


DGMGRL> SHOW DATABASE [VERBOSE] db_name [property_name];
DGMGRL> SHOW DATABASE 'devdb';
DGMGRL> SHOW DATABASE VERBOSE 'test';
DGMGRL> SHOW DATABASE 'dwhdb' 'StatusReport';
DGMGRL> SHOW DATABASE 'proddb' 'LogXptStatus';
DGMGRL> SHOW DATABASE 'proddb' 'InconsistentProperties';
DGMGRL> SHOW DATABASE 'proddb' 'InconsistentLogXptProps';
DGMGRL> SHOW DATABASE 'testdb' 'ArchiveLagTarget';
DGMGRL> SHOW DATABASE 'testdb' 'LogShipping';
DGMGRL> SHOW DATABASE 'testdb' 'PreferredApplyInstance';
DGMGRL> SHOW DATABASE 'proddb' 'StatusReport';
DGMGRL> SHOW DATABASE 'testdb' 'RecvQEntries';
DGMGRL> SHOW DATABASE 'proddb' 'SendQEntries';


DGMGRL> SHOW INSTANCE [VERBOSE] instance_name [property_name] [ON DATABASE db_name];
DGMGRL> SHOW INSTANCE inst1;
DGMGRL> SHOW INSTANCE VERBOSE inst3;
DGMGRL> SHOW INSTANCE testdb 'TopWaitEvents';


DGMGRL> SHOW FAST_START FAILOVER;
DGMGRL> SHOW FAST_START FAILOVER;

From 18c Oracle Database, SHOW ALL command shows the values of DGMGRL command line utility properties.
DGMGRL> SHOW ALL;
   debug ON
   echo OFF
   time OFF
   observerconfigfile = observer.ora

=============
SHUTDOWN - Shuts down a currently running Oracle instance.
DGMGRL> SHUTDOWN [NORMAL
 | IMMEDIATE | ABORT];
DGMGRL> SHUTDOWN;
DGMGRL> SHUTDOWN NORMAL;
DGMGRL> SHUT IMMEDIATE;
DGMGRL> SHUT ABORT;

=============
SQL - Executes a SQL statement
DGMGRL> SQL "sql_statement";


=============
START - Starts the fast-start failover(FSFO) observer.
DGMGRL> START OBSERVER [FILE=observer_configuration_file];
DGMGRL> START OBSERVER;

=============
STARTUP - Starts an Oracle database instance.
DGMGRL> STARTUP [RESTRICT] [FORCE] [PFILE=filespec]
[NOMOUNT  |  MOUNT | OPEN
 [READ ONLY|READ WRITE]];
DGMGRL> STARTUP;
DGMGRL> STARTUP NOMOUNT;
DGMGRL> STARTUP MOUNT;
DGMGRL> STARTUP OPEN;

DGMGRL> STARTUP FORCE;
DGMGRL> STARTUP FORCE RESTRICT NOMOUNT;
DGMGRL> STARTUP PFILE=initdwh.ora NOMOUNT;

=============
STOP - Stops the fast-start failover(FSFO) observer.
DGMGRL> STOP OBSERVER;

=============
SWITCHOVER - Switches roles between a primary and standby database.
DGMGRL> SWITCHOVER TO standby_database_name;
DGMGRL> SWITCHOVER TO "standby";

=============
VALIDATE - command to checks whether the database is ready for a role transition or not.

DGMGRL> VALIDATE DATABASE 'DB_NAME';    -- From Oracle Database 12c


From Oracle Database 18c,
DGMGRL> VALIDATE DATABASE standby-database-name SPFILE;

DGMGRL> VALIDATE NETWORK CONFIGURATION FOR { ALL | member name };
DGMGRL> VALIDATE NETWORK CONFIGURATION FOR stdby;

DGMGRL> VALIDATE STATIC CONNECT IDENTIFIER FOR { ALL | database name };
DGMGRL> VALIDATE STATIC CONNECT IDENTIFIER FOR stdby;



$BDUMP/drc*.log
$ORACLE_HOME/rdbms/log/drc*.log

alter system set dg_broker_start=false;
alter system set dg_broker_start=false sid='*';
alter system set dg_broker_start=FALSE SCOPE=spfile SID='*';
alter system set dg_broker_start=true;
alter system set dg_broker_start=true sid='*';
alter system set dg_broker_start=TRUE SCOPE=spfile SID='*';

alter system set dg_broker_config_file1='/u01/dg_broker_config_files/dr1TESTP.dat' sid='*';
alter system set dg_broker_config_file2='/u01/dg_broker_config_files/dr2TESTP.dat' sid='*';
Scroll Up