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

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;

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;

/

EXPDP AND IMPDP COMMANDS

----------------IMPDP FULL SCHEMA------------------------------
impdp system/<syspassword>  transform=segment_attributes:n DUMPFILE=<dumpfile>.dmp logfile=<logfilename>.log remap_schema=<expdp schema>:<impdp schema> PARALLEL=5

--------------IMPDP for table--------------------------------
impdp system/<syspassword> tables=<schema name>.<tablename> DUMPFILE=<dumpfile>.dmp logfile=<dumpfile>.log transform=segment_attributes:n 

--------------expdp for full database with parallel--------------
expdp system/<syspassword> FULL=y DUMPFILE=<filename>.dmp PARALLEL=5 LOGFILE=<logfilename>.log JOB_NAME=<jobname>

----------------expdp for a table----------------------------------
expdp system/<syspassword> tables=<schemaname>.<tablename> DUMPFILE=<filename>.dmp PARALLEL=5 LOGFILE=<logfilename>.log JOB_NAME=<jobname>

--------------how to stop and start job-----------------------------
IMPORT>STOP_JOB=IMMEDIATE;
IMPORT>START_JOB;
IMPORT>STATUS;
IMPORT>HELP;

---------------EXPDP for a schema--------------------
expdp system/<systempasword> SCHEMAS=<schemaname> DUMPFILE=<dumpfilename>.dmp LOGFILE=<logfilename>.log PARALLEL=5

--------------IMPDP for a schema---------------------------------
impdp system/<systempasword> SCHEMAS=<schemadname> DUMPFILE=<dumpfilename>.dmp LOGFILE=<logfilename>.log PARALLEL=5

------------IMPDP with exclude option----------------------------
impdp system/<password> SCHEMAS=<schemaname> REMAP_SCHEMA=<expdp schema>:<impdpschema>
DUMPFILE=<dumpfilename>.dmp 
EXCLUDE=constraint, ref_constraint, index,materialized_view  
logfile=<logfilename>.log 

-----------IMPDP with SQLFILE option------------------------
impdp <schemaname>/<password> DUMPFILE=<dumpfile>.dmp SQLFILE=<filename>.sql

----------How to use Exdp and Impdp over Network Link : Oracle DB--------------------

#Login in sqlplus 
sqlplus / as sysdba

#Create a connection link for the database which you want to export 
create database link remotelink connect to targetdbuser identified by targetdbpassword using 'hostname:port/sid'
#check connection 
select * from dual@remotelink

# create a local directory where the dump file will be stored and map it to your database dir by creating 
create directory dumpdir as '<path>';
# give permission to local user by whom we will be running expdp
GRANT read, write ON DIRECTORY dumpdir TO localdb;
# check if the dir is created 
 select directory_name, directory_path from dba_directories ;

# You might get following error , if you do not grant read write access of directory to the user .
#ORA-39002: invalid operation
#ORA-39070: Unable to open the log file.
#ORA-39087: directory name dumpdir is invalid

GRANT EXP_FULL_DATABASE to targetdbuser;

expdp userid=localdb/localdb@//localhost:1521/ORCL dumpfile=testdump.dmp logfile=testdump.log SCHEMAS=myschema directory=dumpdir 

grant imp_full_database to myschema ;

impdp myschema/mytest@//localhost:1521/orcl schemas=myschema directory=dumpdir dumpfile=testdump.dmp logfile=impdpnewtest1.log 


------------------------------Schema level with content dataonly and table exist action truncate--------------
impdp system/<password> schemas=<schemaname> content=data_only directory=<directoryname> dumpfile=<dumpfilename>.dmp logfile='<logfilename>.log' TABLE_EXISTS_ACTION=TRUNCATE

-----------------------------Schema level with content dataonly and table exist action replace---------------------
impdp system/<password> schemas=<schema> directory=<dictionary> dumpfile=<dumpfilename>.dmp logfile='<logfilename>.log' TABLE_EXISTS_ACTION=REPLACE

Note: TABLE_EXISTS_ACTION={SKIP | APPEND | TRUNCATE | REPLACE} 

Database properties views

Below are some query that will help you to check current database properties:

SQL>SELECT * FROM DATABASE_PROPERTIES;

SQL> SELECT  * FROM DATABASE_SUMMARY;

If you want the full version information for your DB then:

SQL>SELECT * FROM v$version;

If you want your DB parameters then:

SQL>SELECT * FROM v$parameter;

If you want more information about your DB instance then:

SQL>SELECT * FROM v$database;

SQL>SELECT * FROM v$instance;

If you want the "size" of your database then this will give you a close enough calculation:

SQL>SELECT SUM(bytes / (1024*1024)) "DB Size in MB" FROM dba_data_files;

ORA-02391:exceeded simultaneous SESSIONS_PER_USER limit

ORA-02391 is user session error. It means you need to kill some user session if its not required or you need to increase sessions per use limit. Killing sessions can be very destructive if you kill the wrong session so be careful when killing session. for that first we need to identify that sessions for that query is given below. . SQL>SELECT s.inst_id, s.sid,s.serial#,p.spid,s.username,s.program FROM   gv$session s JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id WHERE  s.type != 'BACKGROUND'; The basic syntax for killing sessions is given below. SQL>ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id' immediate; if you want to increase user session process are given below: 1) SELECT name, value FROM gv$parameter WHERE name = 'resource_limit'; 2) ALTER SYSTEM SET resource_limit=TRUE SCOPE=BOTH; 3) SELECT name, value FROM gv$parameter WHERE name = 'resource_limit'; 4) ALTER PROFILE <profile name>  LIMIT sessions_per_user 80;

Structure of dba_users

Name                                                          Null?                Type
 -----------------------------------------                           --------           ----------------------------
 USERNAME                                              NOT NULL     VARCHAR2(30)
 USER_ID                                                   NOT NULL       NUMBER
 PASSWORD                                                                        VARCHAR2(30)
 ACCOUNT_STATUS                                NOT NULL       VARCHAR2(32)
 LOCK_DATE                                                                       DATE
 EXPIRY_DATE                                                                    DATE
 DEFAULT_TABLESPACE                        NOT NULL       VARCHAR2(30)
 TEMPORARY_TABLESPACE                 NOT NULL        VARCHAR2(30)
 CREATED                                                  NOT NULL          DATE
 PROFILE                                                   NOT NULL         VARCHAR2(30)
 INITIAL_RSRC_CONSUMER_GROUP                              VARCHAR2(30)
 EXTERNAL_NAME                                                              VARCHAR2(4000)

List Of Dynamic Perfomance View-Oracle 10g

Dynamic v$ Performance Views

select * from v$archive_dest;

Select * from v$archive_dest_status;

Select * from v$archive_gap;

Select * from v$archive_processes;

Select * from v$archived_log;

Select * from v$bh;

Select * from v$buffer_pool;

Select * from v$buffer_pool_statistics;

Select * from v$controlfile

Select * from v$controlfile_record_section

Select * from v$database

Select * from v$datafile

Select * from v$datafile_header

Select * from v$dataguard_status

Select * from v$db_object_cache

Select * from v$enqueue_stat

Select * from v$event_name

Select * from v$eventmetric

Select * from v$filemetric

Select * from v$filestat

Select * from v$fixed_table

Select * from v$fixed_view_definition

Select * from v$instance

Select * from v$latch

Select * from v$librarycache

Select * from v$lock

Select * from v$locked_object

Select * from v$log

Select * from v$log_history

Select * from v$logfile

Select * from v$logmnr_contents

Select * from v$logstdby

Select * from v$managed_standby

Select * from v$mystat

Select * from v$nls_parameters

Select * from v$nls_valid_values

Select * from v$object_usage

Select * from  v$open_cursor

Select * from v$option

Select * from v$parameter

Select * from v$pgastat

Select * from v$process

Select * from v$pwfile_users

Select * from v$recover_file

Select * from v$reserved_words

Select * from v$resource_limit

Select * from v$rollname

Select * from v$rollstat

Select * from v$session

Select * from v$session_event

Select * from v$session_longops

Select * from v$session_wait

Select * from v$session_wait_history

Select * from v$sessmetric

Select * from v$sesstat

Select * from v$sga

Select * from v$sga_dynamic_components

Select * from v$sga_resize_ops

Select * from v$sgastat

Select * from v$sort_segment

Select * from v$sort_usage

Select * from v$spparameter

Select * from v$sql

Select * from v$sql_bind_capture

Select * from v$sql_bind_data

Select * from v$sql_cursor

Select * from v$sql_plan

Select * from v$sql_text_with_newlines

Select * from v$sql_workarea

Select * from v$sqlarea

Select * from v$sqltext

Select * from v$sqltext_with_newlines

Select * from v$standby_log

Select * from v$statname

Select * from v$sysaux_occupants

Select * from  v$sysmetric

Select * from v$sysmetric_history

Select * from v$sysstat

Select * from v$system_event

Select * from v$tempfile

Select * from v$tempseg_usage

Select * from v$tempseg_usage

Select * from v$tempstat

Select * from v$thread

Select * from v$timer

Select * from v$timezone_names

Select * from v$transaction

Select * from v$transportable_platform

Select * from  v$undostat

Select * from  v$version

Select * from  v$waitstat

Select * from v$nfs_clients

Select * from v$nfs_open_files

Select * from v$nfs_locks

Select * from v$iostat_network

Backup and Recovery

Select * from v$rman_backup_subjob_details

Select * from v$rman_backup_job_details

Select * from v$backup_set_details

Select * from v$backup_piece_details

Select * from v$backup_copy_details

Select * from $backup

Select * from v$recovery_status

Select * from v$recovery_file_status

Select * from v$backup_set

Select * from v$backup_piece

Select * from v$backup_datafile

Select * from v$backup_redolog

Select * from v$backup_corruption

Select * from v$backup_device
Select * from v$backup_spfile
Select * from v$backup_sync_io
Select * from v$backup_async_io

Select * from v$recover_file
Select * from v$rman_status
Select * from v$rman_output
Select * from v$backup_datafile_details
Select * from v$backup_controlfile_details
Select * from v$backup_archivelog_details
Select * from v$backup_spfile_details
Select * from v$backup_datafile_summary
Select * from v$backup_controlfile_summary
Select * from v$backup_archivelog_summary
Select * from v$backup_spfile_summary
Select * from v$backup_set_summary
Select * from v$recovery_progress

Select * from v$rman_backup_type
Select * from v$rman_configuration

Dataguard

Select * from v$dataguard_config
Select * from v$dataguard_status
Select * from v$managed_standby
Select * from v$logstdby
Select * from v$logstdby_stats
Select * from v$logstdby_transaction
Select * from v$logstdby_process
Select * from v$logstdby_progress
Select * from v$logstdby_state

Streams

Select * from v$streams_apply_coordinator

Select * from v$streams_apply_server
Select * from v$streams_apply_reader

Select * from v$streams_capture
Select * from v$streams_transaction

Select * from v$streams_message_tracking

 

Rac

Select * from v$cluster_interconnects
Select * from v$configured_interconnects

Select * from v$dynamic_remaster_stats
Select * from v$dlm_misc
Select * from v$dlm_latch
Select * from v$dlm_convert_local
Select * from v$dlm_convert_remote
Select * from v$ges_enqueue
Select * from v$ges_blocking_enqueue
Select * from v$dlm_all_locks
Select * from v$dlm_locks
Select * from v$dlm_ress
Select * from v$global_blocked_locks

ASM

Select * from v$asm_template
Select * from v$asm_alias

Select * from v$asm_file
Select * from v$asm_client
Select * from v$asm_diskgroup
Select * from v$asm_diskgroup_stat

Select * from v$asm_disk

Select * from v$asm_disk_stat
Select * from $asm_disk_iostat
Select * from v$asm_operation

Select * from v$asm_attribute

 

Concurrency and SQL Tuning

Select * from v$session
Select * from v$waitclassmetric
Select * from v$waitclassmetric_history
Select * from v$waitstat
Select * from v$wait_chains

Select * from v$lock
Select * from v$sql
Select * from v$sqlarea
Select * from v$sesstat
Select * from v$mystat
Select * from v$sess_io
Select * from v$sysstat
Select * from v$statname
Select * from v$osstat
Select * from v$active_session_history
Select * from v$active_sess_pool_mth
Select * from v$session_wait
Select * from v$session_wait_class
Select * from v$system_wait_class
Select * from v$transaction
Select * from v$locked_object

Select * from v$latch

Select * from v$latch_children
Select * from v$latch_parent
Select * from v$latchname
Select * from v$latchholder
Select * from v$latch_misses
Select * from v$enqueue_lock
Select * from v$transaction_enqueue
Select * from v$sys_optimizer_env
Select * from v$ses_optimizer_env
Select * from v$sql_optimizer_env
Select * from v$sql_plan
Select * from v$sql_plan_statistics

Select * from v$sql_plan_statistics_all

 

Memory Tuning 

Select * from v$sga
Select * from v$sgastat
Select * from v$sgainfo

Select * from v$sga_current_resize_ops
Select * from v$sga_resize_ops
Select * from v$sga_dynamic_components

Select * from v$sga_dynamic_free_memory
Select * from v$pgastat
Select * from v$sql_workarea_histogram
Select * from v$pga_target_advice_histogram                                              
Select * from v$pga_target_advice
Select * from v$memory_current_resize_ops
Select * from v$memory_resize_ops
Select * from v$memory_dynamic_components
Select * from v$library_cache_memory
Select * from v$shared_pool_advice
Select * from v$java_library_cache_memory
Select * from v$java_pool_advice
Select * from v$streams_pool_advice

Workload Repository Views




Select * from V$ACTIVE_SESSION_HISTORY - Displays the active session history (ASH) sampled every second.

select * from V$METRIC - Displays metric information.

select * from V$METRICNAME - Displays the metrics associated with each metric group.

select * from V$METRIC_HISTORY - Displays historical metrics.

select  *  from V$METRICGROUP - Displays all metrics groups.

select * from  DBA_HIST_ACTIVE_SESS_HISTORY - Displays the history contents of the active session history.

select * from DBA_HIST_BASELINE - Displays baseline information.

select * from DBA_HIST_DATABASE_INSTANCE - Displays database environment information.

select * from DBA_HIST_SNAPSHOT - Displays snapshot information.

select * from DBA_HIST_SQL_PLAN - Displays SQL execution plans.

select * from DBA_HIST_WR_CONTROL - Displays AWR settings.