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;



Monday, 13 April 2015

ORA-00600: internal error code

Cause: It may happen  the error was in undo corruption.

Resolve:

Step 1:
SQL> SELECT name,value FROM v$parameter WHERE name IN ('undo_management','undo_tablespace');

NAME VALUE
-------------------- --------------------
undo_management MANUAL
undo_tablespace UNDO_TBS

Step2:
SQL> select FILE_NAME, TABLESPACE_NAME from dba_data_files where TABLESPACE_NAME like 'UNDO%';

FILE_NAME                         TABLESPACE_NAME
----------------------------------------------------------------
/ebiz/oracle/db/apps_st/data/undotbs_02.dbf     UNDO_TBS
/ebiz/oracle/db/apps_st/data/undotbs_01.dbf     UNDO_TBS

Step 3: Create a new undo tablespace
SQL> create UNDO tablespace UNDOTBS datafile '/ebiz/oracle/db/apps_st/data/undotbs01.dbf' size 1024m REUSE AUTOEXTEND ON NEXT 4096K MAXSIZE 1024M;
Tablespace created.

Step 4:
SQL> ALTER SYSTEM SET undo_tablespace = 'UNDOTBS' scope=spfile;
System altered.

Step 5: set old undo tablespace offine mode and drop
SQL> ALTER TABLESPACE UNDO_TBS offline;
Tablespace altered.

SQL> drop tablespace UNDO_TBS including contents and datafiles;
Tablespace dropped.

Step 6:
Rebounced the db services

Step 7: Changed the undo management parameter to AUTO
SQL> alter system set undo_management='AUTO' scope=spfile;
System altered.

SQL> SELECT name,value FROM v$parameter WHERE name IN ('undo_management','undo_tablespace');

NAME VALUE
-------------------- --------------------
undo_management AUTO
undo_tablespace UNDOTBS

Thursday, 2 April 2015

ORA-01000 and OPEN_CURSOR


What is OPEN_CURSOR?

Ans) OPEN_CURSOR means the maximum number of open cursors (handles to private SQL areas) a session can have at once. You can use this parameter to prevent a session from opening an excessive number of cursors.(Range 0 to 65535)

Error: 
ora-01000: maximum open cursors exceeded
  • Monitoring script which provide you full SQLTEXT,USER_NAME,CURSOR_TYPE,SID,Machine, Program information,count

select c.user_name, c.sid,s.SERIAL#, sql.sql_text,s.machine,s.PROGRAM,c.cursor_type
from v$open_cursor c, v$sql sql, v$session s
where c.ADDRESS=sql.address group by c.user_name,c.sid,sql.sql_text,s.machine,s.PROGRAM,c.cursor_type,s.SERIAL#;  

select c.user_name, c.sid,s.SERIAL#, sql.sql_text,count(*) as "OPEN CURSORS VALUE",s.machine,s.PROGRAM,c.cursor_type
from v$open_cursor c, v$sql sql, v$session s
where c.ADDRESS=sql.address group by c.user_name,c.sid,sql.sql_text,s.machine,s.PROGRAM,c.cursor_type,s.SERIAL# order by count(*) desc;
  • Monitoring Script which provide you open_cursor value with SID & Serial# value
select a.value, s.username, s.sid, s.serial# from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#  and s.sid=a.sid and b.name = 'opened cursors current';

  • To know value about current highest open cursor and maximum open cursor
select max(a.value) as highest_open_cur, p.value as max_open_cur
from v$sesstat a, v$statname b, v$parameter p where a.statistic# = b.statistic# 
and b.name = 'opened cursors current' and p.name= 'open_cursors' group by p.value;

Solution:

1) check application code and check code and check why cursor are staying open? And why its required this much number of open cursor?  
2) increase open_cursor parameter and restart db.(if its required last option)

ALTER SYSTEM SET open_cursors = 400 SCOPE=BOTH;









Monday, 2 February 2015

AWR report generation,settings and baseline


1) AWR report manually snapshot command 

EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;

Note: By default snapshots of the relevant data are taken every hour and retained for 7 days.

2) The default value of AWR report can alter by below procedure.

BEGIN
  DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
    retention => 43200,        -- Minutes (= 30 Days). Current value retained if NULL.
    interval  => 60);          -- Minutes. Current value retained if NULL.
   END;
   /

3) To Describe or understand DBMS_WORKLOAD_REPOSITORY you need to run below command which will help to check AWR settings.

desc dbms_workload_repository;

Note: The MMON Oracle background process is responsible for periodically flushing the oldest AWR tables, using a LIFO queue method. 
The only parameter listed in the procedures is the flush_level , which can have either the default value of TYPICAL or a value of ALL.  When the statistics level is set to ALL, the AWR gathers the maximum amount of performance data.


If we need to disable flushing the run time statistics for an AWR workload table, you can get the underlying WRH tables with this query:

 select
    table_id_kewrtb,

    table_name_kewrtb
from
    x$kewrtb
order by
    table_id_kewrtb;


Once you identify a specific table to disable flushing, you can use an ALTER SYSTEM command:
 alter system set “_awr_disabled_flush_tables”=’ WRH$_IC_CLIENT_STATS’; 

4) AWR setting query through it we can know AWR snapshot settings

select

       extract( day from snap_interval) *24*60+
       extract( hour from snap_interval) *60+
       extract( minute from snap_interval ) "Snapshot Interval",
       extract( day from retention) *24*60+
       extract( hour from retention) *60+
       extract( minute from retention ) "Retention Interval"
from dba_hist_wr_control;


We can know all AWR setting through 

$ORACLE_HOME/rdbms/admin/awrinfo.sql;

which can help you keep track of the AWR repository sizing. 

5) To drop Snapshot

BEGIN
DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (
low_snap_id  => 22, 
high_snap_id => 32);
END;
/

6) Snapshot information can be viewd by 

Select * from dba_hist_snapshot;

7) Baseline

A baseline is a pair of snapshots that represents a specific period of usage.   Once baselines are defined they can be used to compare current performance against similar periods in the past. You may wish to create baseline to represent a period of batch processing.

BEGIN
 DBMS_WORKLOAD_REPOSITORY.create_baseline (
   start_snap_id => 210, 
   end_snap_id   => 220,
   baseline_name => 'batch baseline');
END;
/

Drop base line:

BEGIN
 DBMS_WORKLOAD_REPOSITORY.drop_baseline (
   baseline_name => 'batch baseline',
   cascade       => FALSE); -- Deletes associated snapshots if TRUE.
END;
/