Tuesday 14 January 2020

Auto Startup/Stop MongoDB with shell scripting


With MongoDB packages, We can easily start and stop mongodb with service command but we face auto start and stop issue while we install MongoDB with tarball file.
To automate start/stop mongo activity we can use shell scripting. 
Below is example of shell script which we can use for MongoDB auto startup and stop process.

First, You need to create scripts for MongoDB startup and Stop. This is how mine looks like:



  • vi /data/users/mongodb/scripts/mongo_start.sh

        #!/bin/bash
        # script to start the MongoDB
        . ~/.bash_profile
        # start the database
        mongod -f /data/users/mongodb/config/mongod.conf
        exit 0

  • vi /data/users/mongodb/scripts/mongo_stop.sh
     #!/bin/bash
     # script to stop the MongoDB
     . ~/.bash_profile
    # stop the database
    ps -ef | grep mongod | grep -v grep | awk '{print $2}' | xargs kill
    exit 0

You see that inside the scripts, we are calling the .bash_profile file of the user. This is needed to set the MONGO_HOME environment variable.

Next, give execute rights to the scripts:

  • chmod u+x mongo_stop.sh mongo_start.sh
You could now test these scripts to see if they correctly shut down and start up your Mongo database.

We will now create a wrapper script that can be used to schedule as a service.

With user root, create a file called “mongo” under /etc/init.d.

  • vi /etc/init.d/mongo
      #!/bin/bash
      # chkconfig: 345 99 10
      # description: mongo auto start-stop script.
      # Set MONGO_OWNER to the user id of the owner of the
     
      MONGO_OWNER=mongodb
      RETVAL=0

      case "$1" in
             'start')        
      su - $MONGO_OWNER -c "/data/users/mongodb/scripts/mongo_start.sh"
        touch /var/lock/subsys/mongo
        ;;
    'stop')
        su - $MONGO_OWNER -c "/data/users/mongodb/scripts/mongo_stop.sh"
        rm -f /var/lock/subsys/mongo
        ;;
    *)
        echo $"Usage: $0 {start|stop}"
        RETVAL=1
     esac
     exit $RETVAL

Next, provide required permission

  • chmod 750 /etc/init.d/mongo
To create a service of this script, run the following command
  • chkconfig --add mongo
Next, check the script by running “service mongo stop” or “service mongo start” from the command line.

After this, it’s time for the final test: reboot your server and check if your Mongo database is automatically started after the reboot.

Good luck! :-)






Saturday 25 February 2017

Manual CDB-PDB database creation steps for oracle 12c

Today I’ll show you how we can create a CDB(With PDB-pdb$seed) database without DBCA(Database Configuration Assistant).

Environment Details:

Database Server OS: Cents OS 6.7 (64 Bit)
Database Software Version: Oracle 12c (12.1.0.2) 64 Bit Enterprise version

Assumption: Oracle database software 12.1.0.2 is installed on the database server with required read/write directory permission to oracle user


Steps:

1)      Add below line in .bash_profile of oracle database server Linux user

export TMPDIR=/tmp
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/dbhome_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=orcl
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin/:$HOME/bin:$PATH


2) Make Directories for datafiles, control file, audit_file_dest and fast_recovery area

 $ mkdir –p /u01/app/oracle/fast_recovery_area/orcl
 $ mkdir –p /u01/app/oracle/admin/orcl/adump
 $ mkdir –p  /u01/app/oracle/oradata/orcl/pdbseed 
 $ mkdir –p /u01/app/oracle/oradata/orcl/pdborcl/

3)  Make init.ora file in “/u01/app/oracle/product/12c/db_1/dbs” path with below parameters

Example of init.ora file :

db_block_size=8192
open_cursors=300
db_domain=""
db_name="orcl"
control_files=("/u01/app/oracle/oradata/orcl/control01.ctl", "/u01/app/oracle/fast_recovery_area/orcl/control02.ctl")
db_recovery_file_dest="/u01/app/oracle/fast_recovery_area"
db_recovery_file_dest_size=4560m
compatible=12.1.0.2.0
diagnostic_dest=/u01/app/oracle
enable_pluggable_database=true
memory_target=720m
local_listener=LISTENER
processes=300
audit_file_dest="/u01/app/oracle/admin/orcl/adump"
audit_trail=db
remote_login_passwordfile=EXCLUSIVE
dispatchers="(PROTOCOL=TCP) (SERVICE=orclXDB)"
undo_tablespace=UNDOTBS1

Note: “enable_pluggable_database=true” is the main parameter through that we can create CDB database 

4) Create listener.ora file in “/u01/app/oracle/product/12c/db_1/network/admin/” location with below parameters

LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = <OS_HOSTNAME>)(PORT = 1521))))

Note: update <OS_HOSTNAME> with database server OS hostname

5) Start database in no mount mode with the help of  pfile 

$ sqlplus / as sysdba

SQL > startup nomount pfile=’/u01/app/oracle/product/12c/db_1/dbs/init.ora’;

ORACLE instance started.

Total System Global Area  754974720 bytes
Fixed Size                  2928968 bytes
Variable Size             524291768 bytes
Database Buffers          222298112 bytes
Redo Buffers                5455872 bytes

6) Create database cdb database with pdb$seed template with below command

SQL > CREATE DATABASE "orcl"
            MAXINSTANCES 8
            MAXLOGHISTORY 1 
            MAXLOGFILES 16 
            MAXLOGMEMBERS 3 
            MAXDATAFILES 1024
            DATAFILE '/u01/app/oracle/oradata/orcl/system01.dbf' SIZE 1024M REUSE AUTOEXTEND ON EXTENT MANAGEMENT LOCAL
            SYSAUX DATAFILE '/u01/app/oracle/oradata/orcl/sysaux01.dbf' SIZE 1024M REUSE AUTOEXTEND ON
            SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf' SIZE 20M REUSE AUTOEXTEND ON
            SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE  '/u01/app/oracle/oradata/orcl/undotbs01.dbf' SIZE 200M REUSE  AUTOEXTEND ON
            CHARACTER SET AL32UTF8
            NATIONAL CHARACTER SET AL16UTF16
             LOGFILE GROUP 1 ('/u01/app/oracle/oradata/orcl/redo01.log') SIZE 50M, 
             GROUP 2 ('/u01/app/oracle/oradata/orcl/redo02.log') SIZE 50M,
             GROUP 3 ('/u01/app/oracle/oradata/orcl/redo03.log') SIZE 50M
             USER SYS IDENTIFIED BY pdbadmin USER SYSTEM IDENTIFIED BY welcome
              enable pluggable database
              seed file_name_convert=('/u01/app/oracle/oradata/orcl/system01.dbf','/u01/app/oracle/oradata/orcl/pdbseed/system01.dbf',         '/u01/app/oracle/oradata/orcl/sysaux01.dbf','/u01/app/oracle/oradata/orcl/pdbseed/sysaux01.dbf',
  '/u01/app/oracle/oradata/orcl/temp01.dbf','/u01/app/oracle/oradata/orcl/pdbseed/temp01.dbf',
  '/u01/app/oracle/oradata/orcl/undotbs01.dbf','/u01/app/oracle/oradata/orcl/pdbseed/undotbs01.dbf'); 

Note: “enable pluggable database” is required command to create CDB database in which we can create PDB database.

7) Your database is up and running now . now rRun below script from cdb database

$ Sqlplus / as sysdba

SQL > @$ORACLE_HOME/rdbms/admin/catalog.sql;
SQL> @$ORACLE_HOME/rdbms/admin/catblock.sql;
SQL>@$ORACLE_HOME/ctx/admin/catctx.sql <password> sysaux temp NOLOCK;
SQL >@$ORACLE_HOME/rdbms/admin/catproc.sql;
SQL > @$ORACLE_HOME/rdbms/admin/catoctk.sql;
SQL >@$ORACLE_HOME/rdbms/admin/owminst.plb;
SQL >@$ORACLE_HOME/sqlplus/admin/pupbld.sql;
SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql
SQL> @$ORACLE_HOME/rdbms/admin/catcdb.sql

NOTE: update ctxsys user password instead on <password> 


8)  Make Pluggable database from pdb$seed database template with the help of below command: 

Login with cdb database 

$ sqlplus / as sysdba

SQL > CREATE PLUGGABLE DATABASE pdborcl ADMIN USER pdbadmin IDENTIFIED BY welcome FILE_NAME_CONVERT=('/u01/app/oracle/oradata/orcl/pdbseed/','/u01/app/oracle/oradata/orcl/pdborcl/');


Wednesday 11 May 2016

Siebel Error: ODBC error 08003 in SQLConnect: [DataDirect][ODBC lib] Connection not open (native error 0). Unable to login using specified ODBC parameters.

Sometimes you may get below error while starting Siebel. 

Siebel Error:

ODBC error 08003 in SQLConnect:
[DataDirect][ODBC lib] Connection not open
(native error 0).
Unable to login using specified ODBC parameters.

Resolution :

1) Check ORACLE_HOME is set or not?
2) Check LD_LIBRARY_PATH is set or not?
3) Check TNS_ADMIN is set or not?
4) Check PATH is set in or not?

If all oracle parameters are not then set it.

Example :

bash-3.2$ export ORACLE_HOME=/app/oracle/product/11.2.0/client_32
bash-3.2$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
bash-3.2$ export TNS_ADMIN=/app/oracle/product/11.2.0/client_32
bash-3.2$ export PATH=$ORACLE_HOME/bin:/usr/sbin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin

Thursday 24 March 2016

PX Deq Credit: send blkd wait event in AWR

  • PX Deq Credit: send blkd 

    • The PX Deq Credit: send blkd is an idle event in a parallel execution wait event for RAC. 
    • It may happen that several Object  in schema were defined that "degree of parallelism’ different from 1.
    • Use of Oracle parallel queries may cause optimized to choose ‘full table scan’ and ‘hash join’ instead of index access and ‘nested loops join’.
    • So, parallelism is not usually employed in OLTP systems. You can alter all those objects with NOPARALLEL option.

    You can check it in any DB with the following queries:
    SELECT TABLE_NAME FROM USER_TABLES WHERE TRIM(DEGREE) <> '1' ORDER BY 1;
    SELECT INDEX_NAME FROM USER_INDEXES WHERE TRIM(DEGREE) <> '1' ORDER BY 1;

Friday 24 July 2015

How to stop and control .bdb file generation on oracle rac

Step 1. Stop the Cluster Health Monitor resource ora.crf as grid owner
[grid@rac1 ~]$crsctl stop res ora.crf -init
CRS-2673: Attempting to stop 'ora.crf' on 'rac1'
CRS-2677: Stop of 'ora.crf' on 'rac1' succeeded

Step 2. Remove the huge CHM(.bdb) files
The file can only be removed by the root user as it is owned by root.

$cd $GI_HOME/crf/db/[nodename]
$rm -rf *.bdb
Example:
[root@rac1 ~]$cd /app/grid/11.2.0.4/crf/db/rac1
[root@rac1]$rm -rf  *.bdb

Step 3. Start the Cluster Health Monitor resource ora.crf
[grid@rac1 ~]$crsctl start res ora.crf -init
CRS-2672: Attempting to start 'ora.crf' on 'rac1'
CRS-2676: Start of 'ora.crf' on 'rac1' succeeded

Step 4. To control .bdb file generation
run below command through grid user.it allowed to change Cluster Health Monitor repository size
$ oclumon manage -repos resize 259200
rac1 --> retention check successful
rac2 --> retention check successful
New retention is 259200 and will use 4524595200 bytes of disk space

Monday 20 July 2015

ORA-12514 error on RAC environment

Hello

i faced ORA-12514 error on RAC environment.
i am able to tnsping but while connecting to db i got below error:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Solution:
1) Check tnsnames service and node tnsnames.ora service,port should be same.
2) listener and both node should be up and running.
3) user below command from sys or system users.

alter system set remote_listener='<scanname>:<port>';

Example:

alter system set remote_listener='rac-scan.sigmasys.net:1521';



  
 

Tuesday 14 April 2015

Error: ora 00604 ora 12663 ora 36961 ora 06512

In alert log i found below error.

Error:

ORA-00604: error occurred at recursive SQL level 1
ORA-12663: Services required by client not available on the server
ORA-36961: Oracle OLAP is not available.
ORA-06512: at "SYS.OLAPIHISTORYRETENTION", line 1
ORA-06512: at line 15

Problem: This error occurs due to some trigger.We can see trigger name here in error.Here trigger name is OLAPIHISTORYRETENTION.

Resolution : First we need schema name of that trigger.For that try below query through sys/system user.

SQL> select OWNER,TRIGGER_NAME from DBA_TRIGGERS where TRIGGERING_EVENT like '%STARTUP%';

Or 

SQL> select OWNER,TRIGGER_NAME from DBA_TRIGGERS where TRIGGER_NAME like 'OLAPIHISTORYRETENTION';


In my case Result is like that:
SYS,'AURORA$SERVER$STARTUP'’

SYS,‘OLAPISTARTUPTRIGGER’

Now we need to disable it.
SQL> alter trigger OLAPISTARTUPTRIGGER disable;