ODbaBlogs

Friday, September 26, 2014

ERROR :: ORA-04030: out of process memory when trying to allocate 16328 bytes

Issue: ORA-04030: out of process memory when trying to allocate 16328 bytes (koh-kghu call ,pl/sql vc2)

Following ORA-04030 error is encountered every time when the PGA allocation reaches 15GB:

The incident trace shows 15G used by pl/sql:
=======================================
TOP 10 MEMORY USES FOR THIS PROCESS
---------------------------------------
100% 15 GB, 1008569 chunks: "pl/sql vc2 " PL/SQL
koh-kghu call ds=fffffc7ffc6f51f8 dsprt=c715710
0% 15 MB, 15763 chunks: "free memory "
pga heap ds=c715710 dsprt=0

This is due to bug 14119856 when real free allocator is used even though pga_aggregate_target is set more than 16GB.
Use below query to check if real free allocator is used:

SQL> col name format a30
col cur_val format a20
select i.ksppinm name , v.ksppstvl cur_val, v.ksppstdf default_val,v.ksppstvf
from x$ksppi i, x$ksppcv v where i.indx = v.indx and i.ksppinm in
('_realfree_heap_pagesize_hint', '_use_realfree_heap');SQL> SQL> 2 3

NAME CUR_VAL DEFAULT_V KSPPSTVF
------------------------------ -------------------- --------- ----------
_realfree_heap_pagesize_hint 65536 TRUE 0
_use_realfree_heap TRUE TRUE 0

Technique 1:

Step 1:
• Restart the database and server in order to fix the issue
Or
Change the upper limit at either the OS or at the database level:

• Change the page count at the OS level:

by root user,
$ more /proc/sys/vm/max_map_count
$ sysctl -w vm.max_map_count=200000 (for example)

OR at database level,
• Adjust the realfree heap pagesize within the database by setting the following parameters in the init/spfile and restart the database.

_use_realfree_heap=TRUE
_realfree_heap_pagesize_hint = 262144
- OR -
Use Workaround:

Set "_use_realfree_heap=false" and restart database instance.

Or

Apply patch <="" a="">14119856> if available for your platform and Oracle version or request for a one-off patch.

Reference MI note :: Doc ID 1506315.1 and Thanks for the giving time and reading the post .

Thursday, September 25, 2014

ORA-00119: invalid specification for system parameter LOCAL_LISTENER

ERROR :: ORA-00119: invalid specification for system parameter LOCAL_LISTENER

Issue: ORA-00119: invalid specification for system parameter LOCAL_LISTENER

ORA-00132: syntax error or unresolved network name 'LISTENER_DATABASE NAME'

Couse: 1) You have not made the entry of local listener in TNSNAMES.ora file and parameter is added in spfile or pfile . In order to avoid the above error we have two options as per my understanding (if you have plz comment and share your solution.)

Technique 1:

Step 1: Make an entry in the tnsnames.ora as mentioned below

LISTENER_<NAME/DATABASE NAME> =

(ADDRESS = (PROTOCOL = TCP)(HOST = my.host.com)(PORT = 1521))

Where LISTENER_<NAME/DATABASE NAME> is the parameter value for LOCAL_LISTENER which is mentioned in pfile/spfile

PORT is the listener port you are using.

Also there is no need to modify the pfile/spfile.

Technique 2 :

Step 2:

a) If you are using a spfile for starting up your database, create a pfile from it and remove the LOCAL_LISTNER parameter and Create spfile from the pfile .

b) Strat the database .

sqlplus / as sysdba

startup ;

c) start the listener lsnrctl start

Friday, September 19, 2014

ORA-00845: MEMORY_TARGET not supported on this system and ORA-00093: _shared_pool_reserved_min_alloc must be between 4000 and 0

Database not coming up due to below errors (ORA-00845: MEMORY_TARGET not supported on this system and ORA-00093: _shared_pool_reserved_min_alloc must be between 4000 and 0)

Scene 1)

##########################################
SQL> startup nomount;

ORA-00845: MEMORY_TARGET not supported on this system

SQL> shut abort

ORACLE instance shut down.

SQL> exit

Disconnected

Scene 2)
#########################################

SQL> startup nomount;

ORA-01078: failure in processing system parameters

ORA-00093: _shared_pool_reserved_min_alloc must be between 4000 and 0

SQL> shut abort;

ORACLE instance shut down.

SQL> exit

Disconnected

Scene 3)
#########################################

SQL> startup nomount

ORA-01078: failure in processing system parameters

ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 3072M

SQL>

SQL> exit

Disconnected

#########################################

These are all due to memory Memory related parameter which is not specified as it should be.

Solution:

Scene 1) This error always come if /dev/shm FS size is less then memory target parameter.

To overcome from this situation either increase the /dev/shm to greater than memory target or keep less value of memory target from /dev/shm.

Other way is to use SGA_MAX_SIZE instead of MEMORY_TARGET.

Scene 2) This error only came if SGA_MAX_SIZE is set but aggregate value of individual memory parameter (db_cache_size, shared_pool_size, java_pool_size, stream_pool_size etc..) value is more than SGA_MAX_SIZE

To overcome from this situation either increase the SGA_MAX_SIZE or decrease aggregate value of individual memory parameter.

Scene 3) in case by mistaken SGA_MAX_SIZE and MEMORY_TARGET both has been define, and SGA_MAX_SIZE is greater than MEMORY_TARGET.

To overcome from this situation remove or keep size 0 either SGA_MAX_SIZE or MEMORY_TARGET parameter value

[oracle@abcdbhost01 dbs]$ sqlplus '"as sysdba"

SQL*Plus: Release 11.2.0.3.0 Production on Wed Sep 10 20:48:05 2014

Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 3340451840 bytes

Fixed Size 2232960 bytes

Variable Size 771755392 bytes

Database Buffers 2550136832 bytes

Redo Buffers 16326656 bytes

SQL>

Friday, September 5, 2014

Database not starting due to ORA 00205 error in identifying control file

Database not starting up due to ORA-00205 error in identifying control file

SQL> startup
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size 2085360 bytes
Variable Size 1040190992 bytes
Database Buffers 1090519040 bytes
Redo Buffers 14688256 bytes
ORA-00205: error in identifying control file, check alert log for more info

If you are getting above error during starting up database,

shutdown the database and check database datafiles and controlfiles file system mount option.

sometime it is not mounted with nolock option in mostly linux system.

run mount command to check current mount option:

mount

If output is like below for FS which contains your datafiles

xxxxxxxxxxxxxxxxxx:/vol/xxxxxxxxx01/oradata on /oradata type nfs (rw,hard,wsize=32768,rsize=32768,addr=10.20.16.52)

Check FSTAB file

cat /etc/fstab

take backup of fstab file

sudo cp /etc/fstab /etc/fstab_bkp

change the same entry in fstab

sudo vi /etc/fstab

edit the /etc/fstab file like below:

xxxxxxxxxxxxxxxx:/vol/xxxxxxxxxxxxxxxxxoradata /oradata nfs rw,nolock,hard,wsize=32768,rsize=32768,actimeo=120,timeo=600,tcp 1 2

Aftet this, unmount the /oradata FS

sudo umount /oradata

Remount the same:

sudo mount xxxxxxxxxxxxxxxxxxx:/vol/xxxxxxxxxxxxxx/oradata /oradata

If it doesn't work then

use mount the same Fs with below comand:

mount the FS with proper mount option for nfs

sudo mount -t nfs -o rw,nolock,hard,wsize=32768,rsize=32768,actimeo=120,timeo=600,tcp fcgsnprdsng03b:/vol/gemdbval01/oradata /oradata

check again for mount option

mount

if it looks good this time then

start the database

Tuesday, July 8, 2014

Work on jobs for expdp an impdp

Work on jobs for expdp an impdp

While using the expdp use job_name clause to create a user defined job.

[oracle@myserver01 ABC11TST]$ cat par_test.par

directory=DATA_PUMP_DIR

dumpfile=ABC11TST_testing.dmp

logfile=ABC11TST_testing.log

JOB_NAME=test123

full=y

run the expdp and starts play with jobs

[oracle@myserver01 ABC11TST]$ expdp parfile=par_test.par

Export: Release 11.1.0.7.0 - 64bit Production on Sunday, 06 July, 2014 22:34:30

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYS"."TEST123": /******** AS SYSDBA parfile=par_test.par

Estimate in progress using BLOCKS method...

Once expdp is running:

To Stop/Start/Kill the jobs check the job status and use below to stop/kill/start

SELECT owner_name, job_name, operation, job_mode, state FROM dba_datapump_jobs;

OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE

---------- -------------- --------------- ---------- --------

SYS TEST123 EXPORT FULL EXECUTING

[oracle@myserver01 ~]$ ps -ef |grep exp

root 3789 1 0 Feb02 ? 00:00:24 gpm -m /dev/input/mice -t exps2

oracle 5477 8147 0 22:34 pts/2 00:00:00 expdp

oracle 5535 1552 0 22:34 pts/1 00:00:00 grep exp

its internal process is DW01 [n]

myserver01:GEM11TST\sys> !ps -ef |grep -i DW01

oracle 5492 1 84 22:34 ? 00:00:55 ora_dw01_ABC11TST

oracle 5672 5652 0 22:35 pts/1 00:00:00 /bin/bash -c ps -ef |grep -i DW01

To Stop the job:

Use attach clause to get into the job like below:

[oracle@myserver01~]$ expdp attach=sys.TEST123

Export: Release 11.1.0.7.0 - 64bit Production on Sunday, 06 July, 2014 22:43:06

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Job: TEST123

Owner: SYS

Operation: EXPORT

Creator Privs: TRUE

GUID: FD94D4B483EF4A7BE040140A2613156C

Start Time: Sunday, 06 July, 2014 22:38:04

Mode: FULL

Instance: ABC11TST

Max Parallelism: 1

EXPORT Job Parameters:

Parameter Name Parameter Value:

CLIENT_COMMAND /******** AS SYSDBA parfile=par_test.par

State: EXECUTING

Bytes Processed: 0

Current Parallelism: 1

Job Error Count: 0

Dump File: /orabkup_nonprod/ABC11TST/ABC11TST_testing.dmp

bytes written: 4,755,456

Worker 1 Status:

Process Name: DW01

State: EXECUTING

Object Schema: TEST_SOAINFRA

Object Name: REFERENCE_INSTANCE

Object Type: DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

Completed Objects: 1

Total Objects: 879

Completed Rows: 98,326

Worker Parallelism: 1

Export> STOP_JOB=IMMEDIATE

Are you sure you wish to stop this job ([yes]/no): yes

[oracle@myserver01 ~]$

Now again check the status

SELECT owner_name, job_name, operation, job_mode, state FROM dba_datapump_jobs;

OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE

---------- -------------- -------------- ---------- ----------------

SYS TEST123 EXPORT FULL NOT RUNNING

[oracle@myserver01 ~]$ ps -ef |grep -i expdp

oracle 5840 5759 0 22:37 pts/1 00:00:00 grep -i expdp

[oracle@myserver01 ~]$

[oracle@myserver01 ~]$ ps -ef |grep -i 5492

oracle 5847 5759 0 22:37 pts/1 00:00:00 grep -i 5492

[oracle@myserver01 ~]$ ps -ef |grep -i 5477

oracle 5854 5759 0 22:37 pts/1 00:00:00 grep -i 5477

[oracle@myserver01 ~]$

Now start the Job:

[oracle@myserver01~]$ expdp attach=sys.TEST123

Export: Release 11.1.0.7.0 - 64bit Production on Sunday, 06 July, 2014 22:37:59

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Job: TEST123

Owner: SYS

Operation: EXPORT

Creator Privs: TRUE

GUID: FD94D4B483EF4A7BE040140A2613156C

Start Time: Sunday, 06 July, 2014 22:38:04

Mode: FULL

Instance: ABC11TST

Max Parallelism: 1

EXPORT Job Parameters:

Parameter Name Parameter Value:

CLIENT_COMMAND /******** AS SYSDBA parfile=par_test.par

State: IDLING

Bytes Processed: 0

Current Parallelism: 1

Job Error Count: 0

Dump File: /orabkup_nonprod/ABC11TST/ABC11TST_testing.dmp

bytes written: 4,751,360

Worker 1 Status:

Process Name: DW01

State: UNDEFINED

Object Schema: TEST_SOAINFRA

Object Name: REFERENCE_INSTANCE

Object Type: DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

Completed Objects: 1

Total Objects: 879

Completed Rows: 9,076

Worker Parallelism: 1

Export> START_JOB

Export> status

Job: TEST123

Operation: EXPORT

Mode: FULL

State: EXECUTING

Bytes Processed: 0

Current Parallelism: 1

Job Error Count: 0

Dump File: /orabkup_nonprod/ABC11TST/ABC11TST_testing.dmp

bytes written: 4,755,456

Worker 1 Status:

Process Name: DW01

State: EXECUTING

Object Schema: TEST_SOAINFRA

Object Name: REFERENCE_INSTANCE

Object Type: DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

Completed Objects: 1

Total Objects: 879

Completed Rows: 88,962

Worker Parallelism: 1

Export> exit

[oracle@myserver01~]$ ps -ef |grep -i DW01

oracle 5952 1 9 22:38 ? 00:00:16 ora_dw01_ABC11TST

oracle 6257 5877 0 22:41 pts/1 00:00:00 grep -i dw01

[oracle@myserver01~]$ ps -ef |grep -i expdp

oracle 6299 5877 0 22:41 pts/1 00:00:00 grep -i expdp

[oracle@myserver01~]$

SELECT owner_name, job_name, operation, job_mode, state FROM dba_datapump_jobs;

OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE

---------- -------------- --------------- ---------- --------

SYS TEST123 EXPORT FULL EXECUTING

To Kill the Jobs:

Check job status

OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE

--------------- --------------- -------------------- ------------------- ---------------

SYS TEST123 EXPORT FULL EXECUTING

[oracle@myserver01 ~]$ ps -ef |grep expdp

oracle 12703 8147 0 00:05 pts/2 00:00:00 expdp

oracle 15100 15053 0 00:36 pts/1 00:00:00 grep expdp

[oracle@myserver01 ~]$ ps -ef |grep DW01

oracle 15102 15053 0 00:36 pts/1 00:00:00 grep DW01

[oracle@myserver01 ~]$

Attach to job

[oracle@myserver01 ~]$ expdp ATTACH=sys.TEST123

Export: Release 11.1.0.7.0 - 64bit Production on Monday, 07 July, 2014 0:36:14

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Job: TEST123

Owner: SYS

Operation: EXPORT

Creator Privs: TRUE

GUID: FD96194AF4EA35C6E040140A261331A8

Start Time: Monday, 07 July, 2014 0:05:25

Mode: FULL

Instance: ABC11TST

Max Parallelism: 1

EXPORT Job Parameters:

Parameter Name Parameter Value:

CLIENT_COMMAND /******** AS SYSDBA parfile=par_test.par

State: EXECUTING

Bytes Processed: 0

Current Parallelism: 1

Job Error Count: 0

Dump File: /orabkup_nonprod/ABC11TST/ABC11TST_testing.dmp

bytes written: 4,751,360

Worker 1 Status:

Process Name: DW01

State: EXECUTING

Object Schema: TEST_SOAINFRA

Object Name: REFERENCE_INSTANCE

Object Type: DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

Completed Objects: 1

Total Objects: 879

Completed Rows: 241,615

Worker Parallelism: 1

Export> exit

Export> KILL_JOB

Are you sure you wish to stop this job ([yes]/no): yes

[oracle@myserver01 ~]$

After killed the job everything will gone.

[oracle@myserver01 ~]$ expdp attach=sys.TEST123

Export: Release 11.1.0.7.0 - 64bit Production on Monday, 07 July, 2014 0:46:24

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORA-31626: job does not exist

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79

ORA-06512: at "SYS.KUPV$FT", line 386

ORA-31638: cannot attach to job TEST123 for user SYS

ORA-31632: master table "SYS.TEST123" not found, invalid, or inaccessible

ORA-00942: table or view does not exist

[oracle@myserver01 ~]$ ps -ef |grep expdp

oracle 15543 15053 0 00:41 pts/1 00:00:00 grep expdp

[oracle@myserver01 ~]$ ps -ef |grep DW01

oracle 15548 15053 0 00:41 pts/1 00:00:00 grep DW01

[oracle@myserver01 ~]$

SELECT owner_name, job_name, operation, job_mode, state FROM dba_datapump_jobs;

no rows selected

Issue Faced during Job stop and start:

ORA-39095

Job "SYS"."TEST123" stopped due to fatal error at 22:09:39

Job TEST123 has been reopened at Sunday, 06 July, 2014 22:09

Restarting "SYS"."TEST123": /******** AS SYSDBA parfile=par_test.par

ORA-39095: Dump file space has been exhausted: Unable to allocate 0 bytes

Job "SYS"."TEST123" stopped due to fatal error at 22:10:19

These kind of error comes for couple of reason.

1) If you use parallel option while exporting through expdp

2) Another reason if you are using compression method in expdp.

Both reason will cause the same error.

Error comes while starting the job, job will stop and it won’t be able to start. So avoid using compression and parallel in such scenario.

EXPDP and IMPDP through Network link

EXPDP and IMPDP through network link:

For EXPDP

1) Create database link on target database.

myserver01:ABC11TST\sys> create public database link eqedge_to_gem connect to scott identified by amaresh123 using 'EQEGDEV';

Database link created.

myserver01:ABC11TST\sys> select * from dual@eqedge_to_gem;

1) grant EXP_FULL_DATABASE privs to the source user or db link created user (source database)

[oracle@myserver01 ~]$ export ORACLE_SID=ABCDEV

[oracle@myserver01 ~]$ sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jul 7 22:18:47 2014

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> grant EXP_FULL_DATABASE to scott;

Grant succeeded.

If we will not give this privs below error will come.

ORA-31631: privileges are required

ORA-39149: cannot link privileged user to non-privileged user

Now create a parfile on target backup location

myserver01:ABC11TST\sys> create or replace directory DATA_PUMP_DIR as '/orabkup_nonprod/ABC11TST';

Directory created.

[oracle@myserver01 ABC11TST]$ pwd

/orabkup_nonprod/ABC11TST

[oracle@myserver01 ABC11TST]$ cat par_test_network.par

directory=DATA_PUMP_DIR

dumpfile=IQSONAR_testing.dmp

logfile=IQSONAR_testing.log

JOB_NAME=test123

schemas=IQSONAR

network_link=eqedge_to_gem

now use the expdp to take export backup.

[oracle@myserver01 ABC11TST]$ expdp parfile=par_test_network.par

Export: Release 11.1.0.7.0 - 64bit Production on Monday, 07 July, 2014 22:42:10

Username: aniraj/amaresh23

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "ANIRAJ"."TEST123": aniraj/******** parfile=par_test_network.par

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 0 KB

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Master table "ANIRAJ"."TEST123" successfully loaded/unloaded

******************************************************************************

Dump file set for ANIRAJ.TEST123 is:

/orabkup_nonprod/ABC11TST/IQSONAR_testing.dmp

Job "ANIRAJ"."TEST123" successfully completed at 22:42:24

Check the dump file at target backup directory location:

[oracle@myserver01 ABC11TST]$ pwd

/orabkup_nonprod/ABC11TST

[oracle@fcdbfrm14 ABC11TST]$ ls -ltr IQSONAR_testing.dmp IQSONAR_testing.log

-rw-r----- 1 oracle oradba 147456 Jul 7 22:41 IQSONAR_testing.dmp

-rw-r--r-- 1 oracle oradba 1021 Jul 7 22:41 IQSONAR_testing.log

[oracle@fcdbfrm14 ABC11TST]$

For IMPDP

Grant below privs to schema through which you are going to take expdp:

NOTE: on source database user which is being user in dblink must have EXP_FULL_DATABASE privs as well if we are going to expdp for full db or any other schemas of source db.

myserver01:ABC11TST\sys> grant IMP_FULL_DATABASE to aniraj;

Grant succeeded.

Create parfile for impdp

No dumpfile parameter needed here to import through network link

[oracle@myserver01 ABC11TST]$ cat impdp_test_network.par

directory=DATA_PUMP_DIR

logfile=impdp_scott_eqd.log

JOB_NAME=test123

schemas=SCOTT

remap_schema=SCOTT:AMARESH

network_link=eqedge_to_gem

[oracle@myserver01 ABC11TST]$ impdp parfile=impdp_test_network.par

Import: Release 11.1.0.7.0 - 64bit Production on Monday, 07 July, 2014 22:31:04

Username: aniraj/amaresh123

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "ANIRAJ"."TEST123": aniraj/******** parfile=impdp_test_network.par

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 192 KB

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

. . imported "AMARESH"."DEPT" 4 rows

. . imported "AMARESH"."EMP" 14 rows

. . imported "AMARESH"."SALGRADE" 5 rows

. . imported "AMARESH"."BONUS" 0 rows

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "ANIRAJ"."TEST123" successfully completed at 22:31:15

select name from v$database;

NAME

---------

ABC11TST

select count(*) from dba_tables where owner='AMARESH';

COUNT(*)

--------------------

Friday, July 4, 2014

Database Health checkup script

Database Health checkup script

This script need to run with INSTANCE_NAME. without this it will errored out where script is connecting to db.
Script Name : db_healthcheck.sh

run like ./db_healthcheck.sh <Instance Name>

Script is below

#/bin/ksh

#Create by Amaresh

#Created Date 03/12/2009

#Modified Date 03/07/2014

# This script is created for doing quick health check.

export logfile=/tmp/healthcheck.log

export ORACLE_SID=$1

#System uptime

echo -e "\n\n============================System Uptime================================"

echo hostname

hostname

echo

uptime

echo "==============================End System Uptime=============================="

echo -e "\n\n===========================Sar Output===================================="

sar 2 10

echo "==============================End Sar Output================================="

#echo -e "\n\n==========================Memory Free Stats=============================="

#free -m

#echo "=============================End Memory Status==============================="

echo -e "\n\n============================Disk Status=================================="

df -h

echo "==============================End Disk Status================================"

echo -e "\n\n================Top 20 process Stats 3 times from Top command============"

echo First Time

echo

top -c -n 1|head -30

echo

echo 2nd Time

top -c -n 1|head -30

echo

echo 3rd Time

top -c -n 1|head -30

echo

echo " =================================End of Top Command==========================="

echo -e "\n\n=======================Listener Status==================================="

echo

#ps -ef |grep tns

ps -ef |grep tns|grep inherit

lsn=`ps -ef |grep tns|grep -v grep| awk '{print $9}'`

echo $lsn

lsnrctl status $lsn

echo

echo "=========================End Listener Status================================="

echo -e "\n\n================Oracle Enterprise Manager================================"

ps -ef |grep -v grep|grep -i "/bin/emagent"

agent=`ps -ef |grep -i "/bin/emagent"|grep -v grep| awk '{print $8}'|rev| cut -c 8- | rev`

echo $agent

cd $agent

./emctl status agent

echo "=========================End Enterprise Manager=============================="

echo -e "\n\n========================================================================="

echo -e "\n\n=======================Checkign Backup logs====================================="

echo -e "\n\n========Archive Log Backup checks ==========="

ls -ltr /orabkup/$ORACLE_SID/rman_arch*.log

echo -e "\n\n========RMAN Full Backup checks ============="

ls -ltr /orabkup/$ORACLE_SID/rman_$ORACLE_SID*.log

echo -e "\n\n========Export Backup checks ================"

ls -ltr /orabkup/$ORACLE_SID/exp*.log

echo -e "\n\n========Backup checksthrough RMAN ==========="

sqlplus /nolog <<!

conn /as sysdba

set lines 120

set pages 1000

col RMAN_Status FORMAT A20 heading "Status"

col INPUT_TYPE FORMAT A15 heading "Backup Type"

col Hrs FORMAT 999.99 heading "Backup Time"

col Start_Time FORMAT A20 heading "Backup Start Time"

col End_Time FORMAT A20 heading "Backup End Time"

SELECT INPUT_TYPE,

TO_CHAR(START_TIME,'DY mm/dd hh24:mi') Start_Time,

TO_CHAR(END_TIME,'DY mm/dd hh24:mi') End_Time,

ELAPSED_SECONDS/3600 Hrs,

STATUS RMAN_Status

FROM V\$RMAN_BACKUP_JOB_DETAILS where START_TIME > sysdate -15

ORDER BY SESSION_KEY desc;

echo "=========================End Backup Checks==================================="

echo -e "\n\n========================================================================="

echo -e "\n============================Database statistics============================"

echo -e "\n==========================================================================="

# check startup time

export blk_size=`

sqlplus -s /nolog <<END

conn /as sysdba

clear columns

set pagesize 0 feedback off verify off heading off echo off

show parameter db_block_size

exit;

END`

block_size=`echo $blk_size| awk '{print $3}'`

#echo $block_size

echo -e "\n=============Checks for AUTO EXTEND Data file and TS Utilization======================"

sqlplus /nolog <<!

conn /as sysdba

select instance_name, inst_id,to_char(startup_time,'dd/mm/yyyy hh24:mi:ss') startuptime from gv\$instance;

set line 150

set pages 1000

define block_size =$block_size;

col TABLESPACE_NAME for a30

select a.tablespace_name, b.no_of_data_files , b.max_size_M,a.BLOCK_SIZE,a.ALLOCATION_TYPE,a.SEGMENT_SPACE_MANAGEMENT,a.EXTENT_MANAGEMENT,b.AUTOEXTENSIBLE from dba_tablespaces a , (select tablespace_name, AUTOEXTENSIBLE, count(*) as no_of_data_files , sum(maxblocks*&block_size)/1024/1024 as max_size_M from dba_data_files group by tablespace_name,AUTOEXTENSIBLE) b where a.tablespace_name=b.tablespace_name;

set line 199

set pages 1000

col TABLESPACE_NAME for a30

col file_name for a50

show parameter db_block_size

select tablespace_name, file_name, AUTOEXTENSIBLE, sum(maxblocks*&block_size)/1024/1024 as max_size_M from dba_data_files group by tablespace_name, file_name, AUTOEXTENSIBLE order by 1;

!echo checking file ststus

select distinct status from dba_data_files;

select count(*) from v\$recover_file;

!echo Tablespace Utiliazation Report

column tablespace_name heading "TableSpace" format a30

column total_space heading "TotalSpace" format 9999999

column free_space heading "FreeSpace" format 9999999

column used heading "UsedSpace" format 9999999

column pct heading "Used %" format 999.99

select a.tablespace_name,total_space,free_space,

(total_space-free_space) used,

((total_space-free_space)/total_space)*100 pct

from

(select tablespace_name,sum(bytes)/(1024*1024) total_space

from dba_data_files

group by tablespace_name) a,

(select tablespace_name,sum(Bytes)/(1024*1024) free_space

from dba_free_space

group by tablespace_name) b

where a.tablespace_name = b.tablespace_name(+)

order by 5 desc;

!echo checking blocking locks

SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,

id1, id2, lmode, request, type

FROM V\$LOCK

WHERE (id1, id2, type) IN

(SELECT id1, id2, type FROM V\$LOCK WHERE request>0)

ORDER BY id1, request;

!echo Session holding Library cache pin locks

select /*+ ordered */ w1.sid waiting_session,

h1.sid holding_session,

w.kgllktype lock_or_pin,

w.kgllkhdl address,

decode(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive', 'Unknown') mode_held,

decode(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive', 'Unknown') mode_requested

from dba_kgllock w, dba_kgllock h, v\$session w1, v\$session h1

where (((h.kgllkmod != 0) and (h.kgllkmod != 1)

and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))

and (((w.kgllkmod = 0) or (w.kgllkmod= 1))

and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))

and w.kgllktype = h.kgllktype

and w.kgllkhdl = h.kgllkhdl

and w.kgllkuse = w1.saddr

and h.kgllkuse = h1.saddr;

echo -e "\n\n==================================END of Database status report========="

echo -e "\n\n========================== DR checks ==================================="

sqlplus /nolog <<!

conn /as sysdba

clear column

!echo "================ Checks for DR parameters ================"

!echo "================== Checks for DR parameters ================"

show parameter log_archive_dest_state_2

show parameter log_archive_dest_2

set line 150

set pages 1000

select switchover_status from v\$database;

!echo "=============================== Checks DR Log Applied and Log Archived =============="

!echo -e "\n \n"

select rtrim(ltrim(max(al.sequence#))) as LOG_APPLIED from v\$archived_log al, v\$log_history lh where al.sequence# = lh.sequence# and al.applied='YES' and lh.sequence# is NOT NULL and al.thread#=1;

select rtrim(ltrim(max(al.sequence#))) as LOG_ARCHIVED from v\$archived_log al, v\$log_history lh where al.sequence# = lh.sequence# and al.ARCHIVED='YES' and lh.sequence# is NOT NULL and al.thread#=1;

select PROCESS,STATUS,CLIENT_PROCESS,SEQUENCE# from V\$MANAGED_STANDBY;

echo -e "\n\n===================Last 50 Lines of Alertlog for DR checks=============="

VALUE=`

sqlplus -s /nolog <<END

conn /as sysdba

clear columns

set pagesize 0 feedback off verify off heading off echo off

show parameter background_dump_dest;

exit;

END`

echo $VALUE

tmp=`echo $VALUE| awk '{print $3}'`

echo $tmp

tail -50 $tmp/alert_*.log

echo -e "\n\n\n\n\n======================End of Database alter Messaes================"

Please give me feedback if you want to anything in this script, Will try to put those as well.

ODbaBlogs

Friday, September 26, 2014

ERROR :: ORA-04030: out of process memory when trying to allocate 16328 bytes

Thursday, September 25, 2014

ORA-00119: invalid specification for system parameter LOCAL_LISTENER

Friday, September 19, 2014

ORA-00845: MEMORY_TARGET not supported on this system and ORA-00093: _shared_pool_reserved_min_alloc must be between 4000 and 0

Friday, September 5, 2014

Database not starting due to ORA 00205 error in identifying control file

Tuesday, July 8, 2014

Work on jobs for expdp an impdp

EXPDP and IMPDP through Network link

Friday, July 4, 2014

Database Health checkup script

Convert snapshot standby database to Physical standby database: Dataguard 11gR2

UNIX Scripts

DB scripts