ORA-04031: unable to allocate bytes of shared memory during oracle startup

I was facing below error while starting oracle database and because of that database was unable to start.

SQL> startup
ORA-04031: unable to allocate 2072 bytes of shared memory ("shared pool","unknown object","sga heap(2,0)","parameter table block")
SQL> exit
Disconnected


When I checked my database memory allocation then found that sga_target was set to 2GB and pga_aggregate_target was set to 1GB. Also checked available server memory using free command. But there was lots of free memory available on the server.




Cause: SGA is not able to allocate required amount of memory to its components. More shared memory is needed than was allocated in the shared pool.

Action: If the shared pool is out of memory, either reduce your use of shared memory, or increase the sga_target value to larger than allocated.


STEP 1:
To increase the sga_target first you need to create pfile from spfile because db is down.
SQL> create pfile='/home/oracle/db12c.ora' from spfile;

File created.


STEP 2:
Edit pfile and change the value of sga_target large enough than the current settings using vi editor.


STEP 3:
$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 3 15:19:47 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup  pfile='/home/oracle/db12c.ora' ;
SQL>create spfile from pfile;
SQL> shut immediate;
SQL> startup;

Read more ...

Top 50 Most Important DB2 Commands for Database Administrator

IBM Db2 is database software that can reliably handle multiple workloads. The latest version of Db2 is optimized to provide extreme performance while lowering costs.

Below are the most useful db2 admin commands:



Command
Description
db2cmd
Open DB2 Command line Tool
db2cc
Open DB2 Control Centre
db2start
Start Database Normaly
db2stop
Stop Database Normaly
db2stop force
Stop Database forcely
db2 force application all
Close all applications that uses DB2 Database.
db2level
Display DB2 version and fix pack level
db6level
Display DB2 Client Version
db2 terminate
Close the database connection
db2_kill -all
Kill a hanged instance
db2licm -l
View license information
db2 connect to <db2sid>
Establish connection to an instance
db2 list tablespaces show detail
Displays table space information
db2 get dbm cfg
Display configuration parameter of database manager.
db2 get db cfg for <db2 sid>
Display configuration parameter of an instance
db2 update dbm cfg using <parameter_name> <new value>
Change value of a database manager configuration parameter.
db2 update db cfg for <db2 sid> using <parameter_name> <new value>
Change value of a instance configuration parameter.
db2 drop database <target db2sid>
Delete and instance
db2 activate db <dbsid>
Activate Database
db2 deactivate <dbsid>
Deactivate an active database
db2 rollforward db <SID> to end of logs
Apply all pending logs
db2 rollforward db <db2sid> query status
Display rollforward status
db2 backup database <sid> to “disk:\location”
Take an offline backup to specified location
db2 list utilities show detail
Display Database backup status
db2 restore db <sid> from “disk:\path” replace history file
Restore database from a backup image
Read more ...

How to Drop DB2 database Installed on Linux

DB2 Drop database command deletes all its objects, containers, and associated files. The dropped database is removed (uncataloged) from the database directories. You cannot drop a database if someone is connected to it. You would need to force everyone off the database first.




DROP DATABASE deletes all user data and log files, as well as any backup and restore history for the database. If the log files are needed for a rollforward recovery after a restore operation, or the backup history required to restore the database, these files should be saved before issuing this command.

When you use the DROP DATABASE command, archived log files for the dropped database are not affected. You must manually move archived log files from the log archive path. If you do not move these log files, future database recovery operations that use the same archive log path might fail.
The database must not be in use; all users must be disconnected database before the database can be dropped.

To be dropped, a database must be cataloged in the system database directory. Only the specified database alias is removed from the system database directory. If other aliases with the same database name exist, their entries remain. If the database being dropped is the last entry in the local database directory, the local database directory is deleted automatically.


Below are the Step-by-Step process to drop DB2 Database


Step 1: 
Login to the db2 instance. Example in my case db2inst1


Step 2:
$ db2 force application all
DB20000I  The FORCE APPLICATION command completed successfully.
DB21024I  This command is asynchronous and may not be effective immediately.


Step 3:
$ db2 stop
SQL1064N  DB2STOP processing was successful.
SQL1064N  DB2STOP processing was successful.


Step 4:
$ db2start
0   0   SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.


Step 5: 
$ db2 drop database airsan    --Here airsan is database name or alias name.
SQL1032N  No start database manager command was issued.  SQLSTATE=57019


Step 6:
$ db2 connect to airsan
SQL1013N  The database alias name or database name "AIRSAN" could not be
found.  SQLSTATE=42705


Now we have dropped the database named AIRSAN. When you try to connect it will throw an error because we have dropped the database and now it is not in the catalog.

Read more ...

Hash Partitioning with Examples in Oracle

Hash partitioning is a partitioning technique where a hash key is used to distribute rows evenly across the different partitions. Hash partitioning is used where ranges aren't appropriate, i.e. employee number, customer ID, etc. Using this approach, data is randomly distributed across the partitions rather than grouped.


How to Create Hash Partition Table in Oracle

  • Choose a column that is unique.
  • Create multiple partitions and subpartitions for each partition that is a power of two. For example, 2, 4, 8, 16, 32, 64, 128, and so on.



Following are the Benefits and uses of Hash Partitioning:

  1. To enable partial or full parallel partition-wise joins with likely equisized partitions.
  2. To distribute data evenly among different partitions.
  3. To use partition pruning and partition-wise joins according to a partitioning key that is mostly constrained by a distinct value or value list.
  4. To randomly distribute data to avoid I/O bottlenecks if you do not use a storage management technique that stripes and mirrors across all available devices.


Examples to Create Hash Partition Table in Oracle

CREATE TABLE CUSTOMERS
(
RECEIVED_TIME_STAMP     DATE NOT NULL,
NETWORK_ID              VARCHAR2(10) NOT NULL,
RECORD_TYPE             VARCHAR2(50),
CUSTOMER_ID             NUMBER(18) NOT NULL,
PHONE_NUMBER            VARCHAR2(80),
CUSTOMER_NAME           VARCHAR2(80),
ACCOUNT_ID              NUMBER(18),
CUSTOMER_TYPE           VARCHAR2(80),
NRC_NUMBER              VARCHAR2(80),
CUSTOMER_FIRST_NAME     VARCHAR2(80),
CUSTOMER_MIDDLE_NAME    VARCHAR2(80),
CUSTOMER_LAST_NAME      VARCHAR2(80)
)
partition BY hash (CUSTOMER_ID)
(
partition PARTITION P001 tablespace TS_CUSTOMERS_01,
partition PARTITION P002 tablespace TS_CUSTOMERS_02,
partition PARTITION P003 tablespace TS_CUSTOMERS_03,
partition PARTITION P004 tablespace TS_CUSTOMERS_04,
partition PARTITION P005 tablespace TS_CUSTOMERS_01,
partition PARTITION P006 tablespace TS_CUSTOMERS_02,
partition PARTITION P007 tablespace TS_CUSTOMERS_03,
partition PARTITION P008 tablespace TS_CUSTOMERS_04,
partition PARTITION P009 tablespace TS_CUSTOMERS_01,
partition PARTITION P010 tablespace TS_CUSTOMERS_02,
partition PARTITION P011 tablespace TS_CUSTOMERS_03,
partition PARTITION P012 tablespace TS_CUSTOMERS_04,
partition PARTITION P013 tablespace TS_CUSTOMERS_01,
partition PARTITION P014 tablespace TS_CUSTOMERS_02,
partition PARTITION P015 tablespace TS_CUSTOMERS_03,
partition PARTITION P016 tablespace TS_CUSTOMERS_04
);


Example to Create Index on the above Partition Table

CREATE  INDEX IX_CUSTOMER_ID on CUSTOMERS (CUSTOMER_ID) INITRANS 4 STORAGE(FREELISTS 16)
LOCAL
(
PARTITION P001 TABLESPACE TS_CUSTOMER_ID_IDX,
PARTITION P002 TABLESPACE TS_CUSTOMER_ID_IDX,
PARTITION P003 TABLESPACE TS_CUSTOMER_ID_IDX,
PARTITION P004 TABLESPACE TS_CUSTOMER_ID_IDX,
PARTITION P005 TABLESPACE TS_CUSTOMER_ID_IDX,
PARTITION P006 TABLESPACE TS_CUSTOMER_ID_IDX,
PARTITION P007 TABLESPACE TS_CUSTOMER_ID_IDX,
PARTITION P008 TABLESPACE TS_CUSTOMER_ID_IDX,
PARTITION P009 TABLESPACE TS_CUSTOMER_ID_IDX,
PARTITION P010 TABLESPACE TS_CUSTOMER_ID_IDX,
PARTITION P011 TABLESPACE TS_CUSTOMER_ID_IDX,
PARTITION P012 TABLESPACE TS_CUSTOMER_ID_IDX,
PARTITION P013 TABLESPACE TS_CUSTOMER_ID_IDX,
PARTITION P014 TABLESPACE TS_CUSTOMER_ID_IDX,
PARTITION P015 TABLESPACE TS_CUSTOMER_ID_IDX,
PARTITION P016 TABLESPACE TS_CUSTOMER_ID_IDX
);
Read more ...

How to Share Windows Drives and Folders using Oracle VM VirtualBox With a Guest Linux OS

There are several virtual machine available in the market for free use. Oracle VM VirtualBox is one of it which enables you to deploy operating system and application software with supported virtualization environment. It is freely available for Windows, Mac OS X, Linux and Solaris x-86 platforms under GPLv2.  However, the main functionality of all VMs are the same.  The host operating system can support a number of virtual machines, each of which has the characteristics of a particular OS. The kernel manages all system resources and tasks, including process scheduling, memory, disk space, and processor time.

Download VM 



share windows folder with oracle vmware virtual machine



Once vmware is downloaded, you can install and setup your Guest OS. However, sharing host drives\folders is one of the tricky task and is not as simple as clicking and enabling sharing in windows. During my VM setup there was a need to mount and share windows folder which should be accessible from Linux OS. I went through many articles and finally able to mount windows folders to the Linux OS which was running on the top of VM.


Below are the steps by step guide using which you will be able to mount any folders or Drives.



STEP 1: 

Run host OS and launch VM VirtualBox.


STEP 2:

Start Linux box which you have installed in VM.


STEP 3:

Click on "Devices" tab which is located on the top of VM VirtualBox taskbar and select "Shared Folders" from the drop-down list.






STEP 4:

VirtualBox will open a window "Shared Folders". Click on the top icon in the right-hand corner to add the drive/folder. Another window will open—"Add Share".



Step 5:

You can also type the name of the folder in the "Folder Name" box. It can be any name you wish to have on Guest OS. Also, check the boxes "Auto Mount" and "Make Permanent" and click "OK".




STEP 7:

To mount shared folders in linux machine, Login to Linux box and type the below command 


$ mount -t vboxsf Softi /media

HERE Softi is the shared windows folder and you are mounting this folder on /media mount point or folder.


Finally you will be able to see you mounted folders using below command:

df -h

Read more ...

EXPDP IMPDP Failed with ORA-39065, ORA-06512, ORA-23603, ORA-39079, ORA-25205


Datapump Import/Export fails with ORA-39065, ORA-39078, ORA-06512, ORA-25205, ORA-24033,ORA-39014



ORA-39065: unexpected master process exception in RECEIVE
ORA-39078: unable to dequeue message for agent MCP from queue "KUPC$C_1_20090220124816"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPC$QUE_INT", line 609
ORA-25205: the QUEUE SYS.KUPC$C_1_20090220124816 does not exist
Job "SYSTEM"."SYS_EXPORT_FULL_01" stopped due to fatal error at 14:21:39
ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 1 with process name "DW01" prematurely terminated
ORA-31671: Worker process DW01 had an unhandled exception.
ORA-39079: unable to enqueue message DG,KUPC$C_1_20090220124816,KUPC$A_1_20090220124822,MCP,35193,Y
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.KUPC$QUE_INT", line 969
ORA-23603: STREAMS enqueue aborted due to low SGA
ORA-06512: at "SYS.KUPW$WORKER", line 1602
ORA-06512: at line 2



Resolution Steps:
===============

The error message "ORA-23603: STREAMS enqueue aborted due to low SGA" clearly indicates that the streams pool is insufficiently sized.

The Streams Pool size was not sufficiently sized.


Increase the STREAMS_POOL_SIZE parameter or if using Automatic Memory Management (AMM) in 11g, increase the MEMORY_TARGET and MEMORY_MAX_TARGET.
Using AMM, you can also still specify a STREAMS_POOL_SIZE parameter that will be used as a minimum value.


To increase streams pool size:

SQL> show parameter streams

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size                    big integer 0

SQL> alter system set streams_pool_size=100m scope=both;

System altered.

After increasing strams_pool_size clear all the dump and log file and start the export again.


Done!!!!

Read more ...

Error in Process orapwd.exe and unable to find error file opw.msb

I was trying to install Oracle 11g R2(64bit) on Windows 7 64 bit OS.
But While Creating Database using DBCA i got this error. Below is the screenshot.


Error in Process: %ORACLE_HOME%\server\bin\orapwd.exe
Unable to find error file %ORACLE_HOME%\RDBMS\opw<lang>.msb




Resolution:

Set proper Home and Path using cmd and launch the dbca.


 C:\Users\orahow>set ORACLE_HOME=C:\Oracle\app\oracle\product\11.2.0\dbhome_1
 C:\Users\orahow>set PATH=C:\Oracle\app\oracle\product\11.2.0\dbhome_1\server\bin:$PATH:.
  C:\Users\orahow> dbca

Read more ...

CONTACT

Name

Email *

Message *