MongoDB : How to get config file and log file location
In MongoDB configuration files and log files are important files as far as the daily routine for DBA. We may need to check parameters in the configuration and file and errors in log files. So here we will discuss where and how to check the location of these files
Configuration files (mongod.conf)
The below command will help to list different files and their location, once you run it, the output will look like the below, but make sure you run it through the admin database, you can see highlighted part shows the location and name of the MongoDB configuration file
[Also read - Database and Collections]
db.runCommand({getCmdLineOpts:1})
> use admin
switched to
db admin
>
db.runCommand({getCmdLineOpts:1});
{
"argv" : [
"C:\\Program
Files\\MongoDB\\Server\\5.0\\bin\\mongod.exe",
"--config",
"C:\\Program
Files\\MongoDB\\Server\\5.0\\bin\\mongod.cfg",
"--service"
],
"parsed" : {
"config" : "C:\\Program
Files\\MongoDB\\Server\\5.0\\bin\\mongod.cfg",
"net" : {
"bindIp" : "127.0.0.1",
"port" : 27017
},
"service" : true,
"storage" : {
"dbPath" : "C:\\Program Files\\MongoDB\\Server\\5.0\\data",
"journal" : {
"enabled" : true
}
},
"systemLog" : {
"destination" : "file",
"logAppend" : true,
"path" : "C:\\Program
Files\\MongoDB\\Server\\5.0\\log\\mongod.log"
}
},
"ok" : 1
}
>
Log file
In this same command above, you can see the parameter "systemLog" and location is also given there only, marked in red color.
There is one other to check log file location using mongod.conf file, in the configuration file, you can below the parameter
# where to write logging data.
systemLog:
destination: file
logAppend: true
path: C:\Program Files\MongoDB\Server\5.0\log\mongod.log
Granting access on dynamic views (ORA-02030: can only select from fixed tables/views)
There were situations
when the application team asks for access to dynamics views such as V$SESSION or
similar dynamic views. Access on V$SESSION is required in
order to monitor sessions from the application end, so this is a common request
Here, we will discuss, granting access to dynamic views, if you try to grant access to these dynamic views, it will throw ORA error ORA-02030 as snipped below
SQL> grant select on v$session to tech_owner;
grant select on v$session to
tech_owner
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views
SQL>
The meaning of this
error is, we can assign select access to tables and views only. We will see the
object type of this V$SESSION dynamic view
[Also Read - Oracle SCN]
SQL> select owner, object_name, object_type from dba_objects where object_Name='V$SESSION';
OWNER OBJECT_NAME OBJECT_TYPE
------------ --------------------
-------------------
PUBLIC
V$SESSION SYNONYM
We can see, V$SESSION is neither view nor table, now we will see base object of this synonym
SQL> select owner, synonym_name, table_owner,
2 table_name from
dba_synonyms where synonym_name='V$SESSION';
OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME
----------
---------------- ------------------- ---------------------
PUBLIC V$SESSION SYS V_$SESSION
here we can see the base object for synonym V$SESSION is V_$SESSION, now we will see the type of this object V_$SESSION
SQL> select owner, object_name, object_type from
dba_objects where object_Name='V_$SESSION';
OWNER OBJECT_NAME
OBJECT_TYPE
------------ --------------------
-------------------
SYS
V_$SESSION VIEW
We can see V_$SESSION is a view so we can grant access on V_$SESSION to get access on V$SESSION dynamic view
SQL> grant select on v_$session to
tech_owner;
Grant succeeded.
PostgreSQL : How to get data directory location for PostgreSQL instance
Oracle SCN : System Change Number
System Change Number
SCN is a number generated in the database on the
occurrence of an event in the database, the event could be
- DML statement execution
- SELECT statement execution
- DDL statement
- Commit statement
Whenever this type of transaction happens, the
current timestamp converted into the system change number (SCN)
SCN is useful in MANY DATABASE SCENARIOS SUCH AS
- Instance Recovery
- Roll forward
- Rollback
- Backup and recovery
- Read consistency
SCN is always incrementing because timestamps always
increase.
Snowflake : Undrop database command
Snowflake database has a lot of features and benefits over the
traditional database, one of the features is undrop database, assume you
changed your mind after dropping a database, you can undo it using undrop
database command,
let's see how
exactly it works. this particular feature is based on the time travel feature
of the snowflake, a schema, database or table can be restored within the
parameter value of "data retention period", the default value of the
same is 24 hours or 1 day, and it can set up to 90 days for the enterprise edition.
Undrop feature can be applied
to the table, schema or database, here we will discuss database example
+-----------------------+----------------+----------------+
|
DATABASE_NAME | DATABASE_OWNER |
RETENTION_TIME |
|-----------------------+----------------+----------------|
|
EXERCISE_DB | ACCOUNTADMIN
| 1 |
|
SNOWFLAKE_SAMPLE_DATA | ACCOUNTADMIN |
1 |
|
TECHNODB | ACCOUNTADMIN
| 1 |
+-----------------------+----------------+----------------+
These are the
current databases in the snowflake instance, now I will drop the technodb
snowflake database
technosnow#TECHNO_WS@SNOWFLAKE.INFORMATION_SCHEMA>drop database TECHNODB;
+--------------------------------+
|
status
|
|--------------------------------|
|
TECHNODB successfully dropped. |
+--------------------------------+
1
Row(s) produced. Time Elapsed: 0.610s
technosnow#TECHNO_WS@SNOWFLAKE.INFORMATION_SCHEMA>
technosnow#TECHNO_WS@SNOWFLAKE.INFORMATION_SCHEMA>select
database_name , database_owner, retention_time from databases;
+-----------------------+----------------+----------------+
|
DATABASE_NAME | DATABASE_OWNER |
RETENTION_TIME |
|-----------------------+----------------+----------------|
|
EXERCISE_DB | ACCOUNTADMIN
| 1 |
|
SNOWFLAKE_SAMPLE_DATA | ACCOUNTADMIN |
1 |
+-----------------------+----------------+----------------+
2
Row(s) produced. Time Elapsed: 0.639s
technosnow#TECHNO_WS@SNOWFLAKE.INFORMATION_SCHEMA>
Here I have
dropped a database technodb, you can from the above logs, now we have only two databases
instead of three
technosnow#TECHNO_WS@SNOWFLAKE.INFORMATION_SCHEMA>undrop database TECHNODB;
+------------------------------------------+
|
status
|
|------------------------------------------|
|
Database TECHNODB successfully restored. |
+------------------------------------------+
1
Row(s) produced. Time Elapsed: 0.491s
technosnow#TECHNO_WS@SNOWFLAKE.INFORMATION_SCHEMA>
select
database_name , database_owner, retention_time from databases;
+-----------------------+----------------+----------------+
|
DATABASE_NAME | DATABASE_OWNER |
RETENTION_TIME |
|-----------------------+----------------+----------------|
|
EXERCISE_DB | ACCOUNTADMIN
| 1 |
|
SNOWFLAKE_SAMPLE_DATA | ACCOUNTADMIN |
1 |
|
TECHNODB | ACCOUNTADMIN
| 1 |
+-----------------------+----------------+----------------+
3
Row(s) produced. Time Elapsed: 0.783s
technosnow#TECHNO_WS@SNOWFLAKE.INFORMATION_SCHEMA>
Here we see, that the database has been restored back with the undrop database command, In this way, we
can do it for table and schema as well.
Snowflake : Using snowsql for snowflake database
A snowflake data warehouse
is the latest addition to the trending database technology, In this article, we
will be discussing snowsql, a command line tool used to access the snowflake
database, there is another way as well to access the database i.e. web-based
portal. still, there are many DBA's who love working on the command line
instead of a web-based portal.
Snowsql is supported on the
below platforms
- Red Hat Enterprise Linux or a compatible operating system.
- macOS (64-bit).
- Microsoft Windows (64-bit).
snowsql installer can be
downloaded using the below link
follow the traditional
approach to install snowsql i.e. next, next and finish
once the installation is
complete, execute the below command, if it gives output instead of error then
your snowsql installation is successful
C:\Users>snowsql -v
Version: 1.2.23
C:\Users>
we can see, that snowsql is
installed and the version is 1.2.23
in order to login into the
snowflake database, you need to have an account
name, username, and password
also read know your snowsql account name
PostgreSQL : Types of Shutdown
There are different ways to make a database shutdown for every
database type whether it is MySQL, Oracle, MongoDB, etc, in PostgreSQL as well,
there are 3 types of shutdown based on the signal provided
1.
SIGTERM
2.
SIGINT
3.
SIGQUIT
We will discuss them one by one
SIGTERM
This type of
shutdown is identified as a smart shutdown
After receiving a
shutdown signal, SIGTERM
disallows new connections
let’s existing connection works
normally
shuts down only after sessions
are terminated
If the server is
in online backup mode
It additionally waits
until the online backup mode is no longer active, new connections still are
allowed for superusers only, as they might need to send a request to
disable the are online backup mode
If the server is
in recovery mode
The recovery
process will be stopped only after all regular sessions are terminated.
SIGINT
This mode is
termed fast shutdown mode
once a SIGINT
signal is sent, it
disallows new connections and sends
all existing server processes SIGTERM, which makes them abort respective current
transactions and exit promptly
waits until all server process
to exit and finally shuts down.
if the server is
in online backup mode
the backup mode
will be terminated and the backup will be made useless
SIGQUIT
This mode is
termed immediate shutdown mode
The server will
send SIGQUIT to all the child processes and wait for them to terminate
and if it is not terminated within 5 sec, it sends SIGKILL
After forcing
SIGQUIT,
the database
needs recovery on the next startup
only recommended in case of
emergency.
PostgreSQL : How to check parameter values in postgresql
As part of some basic
administration in PostgreSQL databases, we will learn how to check and modify
parameters
there are a few ways to do it
- using the "Show" command
- querying pg_settings catalog
- by directly checking configuration files
Assume you need to check the
parameter related to the hba_configuration file, like the location of this file
but you are not sure about the parameter name, I just tried with a few
options, and it was not giving me proper value, however, it was throwing syntax
error like below
postgres-# show hba_config;
ERROR: syntax error at or near
"show"
LINE 2: show hba_config;
^
postgres=#
postgres=# show hba;
ERROR: unrecognized
configuration parameter "hba"
postgres=#
for using the "show"
command, you must know the exact name of the parameter, or else you can simply
use pg_settings catalog to fetch it, I just tried the
"like" parameter in pg_settings
postgres=# SELECT name, setting,
reset_val FROM pg_settings where name like '%hba%';
name |
setting
|
reset_val
----------+-------------------------------------------------+-------------------------------------------------
hba_file | C:/Program
Files/PostgreSQL/10/data/pg_hba.conf | C:/Program
Files/PostgreSQL/10/data/pg_hba.conf
(1 row)
now you can check using the "show"
command
postgres=# show hba_file;
hba_file
-------------------------------------------------
C:/Program
Files/PostgreSQL/10/data/pg_hba.conf
(1 row)
postgres=# show max_connections;
max_connections
-----------------
100
(1 row)
PostgreSQL : How to describe table in psql
For the DBA's using a
conventional database such as oracle, they have a habit of using DESC or
DESCRIBE but in PostgreSQL, it's in different ways, we will discuss the same
here
There are 3 ways to describe a table
- using \d
- using \d+
- using view INFORMATION_SCHEMA.COLUMNS
suppose you want to see the
columns in the pg_roles table from the PostgreSQL database
\d will details of columns in
particular table as shown below
\d+ is an advanced version of \d,
it provides you the definition of the table as well.
You have another way to
describe a table i.e. using SQL query on INFORMATION_SCHEMA.COLUMNS catalog, there are a number of columns in this table, you can limit what you want to see, select * will give full details, however, I will only select the column name and its data type
select * from INFORMATION_SCHEMA.COLUMNS;
select column_name, data_type from INFORMATION_SCHEMA.COLUMNS where table_name = 'pg_roles';