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-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';

  
 

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;

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

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;

/

Create user through procedure on another database via database link

create or REPLACE PROCEDURE hostname1   

  (host_name in varchar2,user_name in VARCHAR2, pass_word in VARCHAR2,    

  table_space in varchar2,pro_file in varchar2)    

as    

  db_link_name      varchar2(30);

  l_ddl_sql         varchar2(4000);

begin    

  select db_link into db_link_name from all_db_links where host=host_name; 

  l_ddl_sql := 'begin dbms_utility.exec_ddl_statement@'||db_link_name||'(''CREATE     USER '||user_name||' IDENTIFIED BY '||pass_word||'     

    DEFAULT TABLESPACE '||table_space||' PROFILE '|| pro_file||' ACCOUNT UNLOCK''); END;';

  EXECUTE IMMEDIATE l_ddl_sql;

l_ddl_sql := 'begin dbms_utility.exec_ddl_statement@'||db_link_name||    

    '(''GRANT CONNECT,RESOURCE,EXECUTE_CATALOG_ROLE,Create table,create session,create     view,create sequence,create procedure,create job,create synonym TO '||user_name || '''); END;';

  EXECUTE IMMEDIATE l_ddl_sql;

end;

/