Monday, April 16, 2012

Resize Redo log for RAC

In RAC, you have seperate log groups for each instance (called threads). You can see which group belongs to which thread by querying v$log. When you add new loggroups, you specify which thread it should belong to. The syntax is

ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 5 ('firstlocation','secondlocation') SIZE 1280M;
Enable/Disable Archive logs in a RAC environment

on node 1:
set oracle_sid=racdb1
-------------Noarchivelog
sqlplus / as sysdba
alter system set cluster_database=false scope=spfile sid='racdb2';



quit



Configure rsh for CentOS
1. check if rsh-server is installed:
[root@rac1 ~]# rpm -qa|grep -i ^rsh-server

2. make sure no disable = no in /etc/xinetd.d/rlogin  :

1.  [root@rac1 ~]# cat /etc/xinetd.d/rlogin  
2.  # default: on  
3.  # description: rlogind is the server for the rlogin(1) program. The server  
4.  # provides a remote login facility with authentication based on  
5.  # privileged port numbers from trusted hosts.  
6.  service login  
7.  {disable = no 
8.  socket_type = stream 
9.  wait = no 
10. user = root 
11. log_on_success += USERID  
12. log_on_failure += USERID  
13. server = /usr/sbin/in.rlogind} 

Configure for oracle Single Instance install with ASM on centos

Create VM:




Oracle Checkpoint 

A checkpoint performs the following three operations:
Every dirty block in the buffer cache is written to the data files. That is, it synchronizes the datablocks in the buffer cache with the datafiles on disk.
It's the DBWR that writes all modified databaseblocks back to the datafiles.

To change DBIO_EXPECTED for EM

 SELECT parameter_value,is_default FROM dba_advisor_def_parameters WHERE advisor_name = 'ADDM' AND parameter_name ='DBIO_EXPECTED';

EXECUTE DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER('ADDM', 'DBIO_EXPECTED', 20000);

select * from aux_stats$;


EXECUTE IMMEDIATE option for Dynamic SQL and PL/SQL


EXECUTE IMMEDIATE is the replacement for DBMS_SQL package from Oracle 8i onwards. It parses and immediately executes a dynamic SQL statement or a PL/SQL block created on the fly. Dynamically created and executed SQL statements are performance overhead, EXECUTE IMMEDIATE aims at reducing the overhead and give better performance. It is also easier to code as compared to earlier means. The error messages generated when using this feature are more user friendly. Though DBMS_SQL is still available, it is advisable to use EXECUTE IMMEDIATE calls because of its benefits over the package.