Oracle RAC

Oracle ASM Administration

http://oracledbarac.wordpress.com/category/asm-administartion/

http://dbasolutions.wikispaces.com/Adminstration-ASM

Oracle RAC Administration

Increase the redo log to 2/3 GB
by default each instance having 50m size of redo group

if your application generate more redo or frequency of log switch >= 2 on per hour
then you should increase the redo size accordingly

You can also check the alert log for cannot complete checkpoint information
OR
You should generate the AWR during pick hour and check the top 5 event for log file sync event analysis.

Node-1
SQL> show parameter instance_name

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
instance_name			     string	 orcl1
SQL>
SQL> set colsep "|"
	set linesize 32767
	set pagesize 10000
select inst_id,group#,status,type,member from gv$logfile;SQL> SQL> SQL>

   INST_ID|    GROUP#|STATUS |TYPE   |MEMBER
----------|----------|-------|-------|---------------------------------------------------
	 1|	    2|	     |ONLINE |+ORASYS/orcl/onlinelog/group_2.262.833543367
	 1|	    2|	     |ONLINE |+ORAFRA/orcl/onlinelog/group_2.258.833543369
	 1|	    1|	     |ONLINE |+ORASYS/orcl/onlinelog/group_1.261.833543361
	 1|	    1|	     |ONLINE |+ORAFRA/orcl/onlinelog/group_1.257.833543365
	 1|	    3|	     |ONLINE |+ORASYS/orcl/onlinelog/group_3.265.833543503
	 1|	    3|	     |ONLINE |+ORAFRA/orcl/onlinelog/group_3.259.833543507
	 1|	    4|	     |ONLINE |+ORASYS/orcl/onlinelog/group_4.266.833543509
	 1|	    4|	     |ONLINE |+ORAFRA/orcl/onlinelog/group_4.260.833543511
	 2|	    2|	     |ONLINE |+ORASYS/orcl/onlinelog/group_2.262.833543367
	 2|	    2|	     |ONLINE |+ORAFRA/orcl/onlinelog/group_2.258.833543369
	 2|	    1|	     |ONLINE |+ORASYS/orcl/onlinelog/group_1.261.833543361
	 2|	    1|	     |ONLINE |+ORAFRA/orcl/onlinelog/group_1.257.833543365
	 2|	    3|	     |ONLINE |+ORASYS/orcl/onlinelog/group_3.265.833543503
	 2|	    3|	     |ONLINE |+ORAFRA/orcl/onlinelog/group_3.259.833543507
	 2|	    4|	     |ONLINE |+ORASYS/orcl/onlinelog/group_4.266.833543509
	 2|	    4|	     |ONLINE |+ORAFRA/orcl/onlinelog/group_4.260.833543511

SQL> select inst_id,group#,thread#,sequence#,bytes,members,status from gv$log;

   INST_ID|    GROUP#|	 THREAD#| SEQUENCE#|	 BYTES|   MEMBERS|STATUS
----------|----------|----------|----------|----------|----------|----------------
	 1|	    1|	       1|      1407|1073741824| 	2|CURRENT
	 1|	    2|	       1|      1406|1073741824| 	2|INACTIVE
	 1|	    3|	       2|      1163|1073741824| 	2|INACTIVE
	 1|	    4|	       2|      1164|1073741824| 	2|CURRENT
	 2|	    1|	       1|      1407|1073741824| 	2|CURRENT
	 2|	    2|	       1|      1406|1073741824| 	2|INACTIVE
	 2|	    3|	       2|      1163|1073741824| 	2|INACTIVE
	 2|	    4|	       2|      1164|1073741824| 	2|CURRENT

SQL> alter database add logfile thread 1 group 5 ('+ORASYS/orcl/onlinelog/redo5a.log','+ORAFRA/orcl/onlinelog/redo5b.log') size 3072M;

Database altered.

SQL> alter database add logfile thread 1 group 6 ('+ORASYS/orcl/onlinelog/redo6a.log','+ORAFRA/orcl/onlinelog/redo6b.log') size 3072M;

Database altered.

Node-2
SQL> show parameter instance_name

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
instance_name			     string	 orcl2

SQL> alter database add logfile thread 2 group 8 ('+ORASYS/orcl/onlinelog/redo8a.log','+ORAFRA/orcl/onlinelog/redo8b.log') size 3072M;

Database altered.

SQL> alter database add logfile thread 2 group 9 ('+ORASYS/orcl/onlinelog/redo9a.log','+ORAFRA/orcl/onlinelog/redo9b.log') size 3072M;

Database altered.

SQL>  select inst_id,group#,status,type,member from gv$logfile;

   INST_ID|    GROUP#|STATUS |TYPE   |MEMBER
----------|----------|-------|-------|------------------------------------------------------------------------------------------------------------	 2|	    2|	     |ONLINE |+ORASYS/orcl/onlinelog/group_2.262.833543367
	 2|	    2|	     |ONLINE |+ORAFRA/orcl/onlinelog/group_2.258.833543369
	 2|	    1|	     |ONLINE |+ORASYS/orcl/onlinelog/group_1.261.833543361
	 2|	    1|	     |ONLINE |+ORAFRA/orcl/onlinelog/group_1.257.833543365
	 2|	    3|	     |ONLINE |+ORASYS/orcl/onlinelog/group_3.265.833543503
	 2|	    3|	     |ONLINE |+ORAFRA/orcl/onlinelog/group_3.259.833543507
	 2|	    4|	     |ONLINE |+ORASYS/orcl/onlinelog/group_4.266.833543509
	 2|	    4|	     |ONLINE |+ORAFRA/orcl/onlinelog/group_4.260.833543511
	 2|	    5|	     |ONLINE |+ORASYS/orcl/onlinelog/redo5a.log
	 2|	    5|	     |ONLINE |+ORAFRA/orcl/onlinelog/redo5b.log
	 2|	    6|	     |ONLINE |+ORASYS/orcl/onlinelog/redo6a.log
	 2|	    6|	     |ONLINE |+ORAFRA/orcl/onlinelog/redo6b.log
	 2|	    7|	     |ONLINE |+ORASYS/orcl/onlinelog/redo7a.log
	 2|	    7|	     |ONLINE |+ORAFRA/orcl/onlinelog/redo7b.log
	 2|	    8|	     |ONLINE |+ORASYS/orcl/onlinelog/redo8a.log
	 2|	    8|	     |ONLINE |+ORAFRA/orcl/onlinelog/redo8b.log
	 2|	    9|	     |ONLINE |+ORASYS/orcl/onlinelog/redo9a.log
	 2|	    9|	     |ONLINE |+ORAFRA/orcl/onlinelog/redo9b.log
	 2|	   10|	     |ONLINE |+ORASYS/orcl/onlinelog/redo10a.log
	 2|	   10|	     |ONLINE |+ORAFRA/orcl/onlinelog/redo10b.log
	 1|	    2|	     |ONLINE |+ORASYS/orcl/onlinelog/group_2.262.833543367
	 1|	    2|	     |ONLINE |+ORAFRA/orcl/onlinelog/group_2.258.833543369
	 1|	    1|	     |ONLINE |+ORASYS/orcl/onlinelog/group_1.261.833543361
	 1|	    1|	     |ONLINE |+ORAFRA/orcl/onlinelog/group_1.257.833543365
	 1|	    3|	     |ONLINE |+ORASYS/orcl/onlinelog/group_3.265.833543503
	 1|	    3|	     |ONLINE |+ORAFRA/orcl/onlinelog/group_3.259.833543507
	 1|	    4|	     |ONLINE |+ORASYS/orcl/onlinelog/group_4.266.833543509
	 1|	    4|	     |ONLINE |+ORAFRA/orcl/onlinelog/group_4.260.833543511
	 1|	    5|	     |ONLINE |+ORASYS/orcl/onlinelog/redo5a.log
	 1|	    5|	     |ONLINE |+ORAFRA/orcl/onlinelog/redo5b.log
	 1|	    6|	     |ONLINE |+ORASYS/orcl/onlinelog/redo6a.log
	 1|	    6|	     |ONLINE |+ORAFRA/orcl/onlinelog/redo6b.log
	 1|	    7|	     |ONLINE |+ORASYS/orcl/onlinelog/redo7a.log
	 1|	    7|	     |ONLINE |+ORAFRA/orcl/onlinelog/redo7b.log
	 1|	    8|	     |ONLINE |+ORASYS/orcl/onlinelog/redo8a.log
	 1|	    8|	     |ONLINE |+ORAFRA/orcl/onlinelog/redo8b.log
	 1|	    9|	     |ONLINE |+ORASYS/orcl/onlinelog/redo9a.log
	 1|	    9|	     |ONLINE |+ORAFRA/orcl/onlinelog/redo9b.log
	 1|	   10|	     |ONLINE |+ORASYS/orcl/onlinelog/redo10a.log
	 1|	   10|	     |ONLINE |+ORAFRA/orcl/onlinelog/redo10b.log

40 rows selected.

Node-1
SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint global;

System altered.

SQL> select inst_id,group#,thread#,sequence#,bytes,members,status from gv$log
where status='INACTIVE';  2

   INST_ID|    GROUP#|	 THREAD#| SEQUENCE#|	 BYTES|   MEMBERS|STATUS
----------|----------|----------|----------|----------|----------|----------------
	 2|	    1|	       1|      1407|1073741824| 	2|INACTIVE
	 2|	    2|	       1|      1406|1073741824| 	2|INACTIVE
	 2|	    4|	       2|      1164|1073741824| 	2|INACTIVE
	 2|	    5|	       1|      1408|3221225472| 	2|INACTIVE
	 2|	    6|	       1|      1409|3221225472| 	2|INACTIVE
	 1|	    1|	       1|      1407|1073741824| 	2|INACTIVE
	 1|	    2|	       1|      1406|1073741824| 	2|INACTIVE
	 1|	    4|	       2|      1164|1073741824| 	2|INACTIVE
	 1|	    5|	       1|      1408|3221225472| 	2|INACTIVE
	 1|	    6|	       1|      1409|3221225472| 	2|INACTIVE

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

Node-2
Note:- alter system switch logfile; and check above query once again for INACTIVE for group 4 and 3.

SQL> alter database drop logfile group 4;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> select inst_id,group#,status,type,member from gv$logfile;

   INST_ID|    GROUP#|STATUS |TYPE   |MEMBER
----------|----------|-------|-------|-----------------------------------------------
	 2|	    5|	     |ONLINE |+ORASYS/orcl/onlinelog/redo5a.log
	 2|	    5|	     |ONLINE |+ORAFRA/orcl/onlinelog/redo5b.log
	 2|	    6|	     |ONLINE |+ORASYS/orcl/onlinelog/redo6a.log
	 2|	    6|	     |ONLINE |+ORAFRA/orcl/onlinelog/redo6b.log
	 2|	    7|	     |ONLINE |+ORASYS/orcl/onlinelog/redo7a.log
	 2|	    7|	     |ONLINE |+ORAFRA/orcl/onlinelog/redo7b.log
	 2|	    8|	     |ONLINE |+ORASYS/orcl/onlinelog/redo8a.log
	 2|	    8|	     |ONLINE |+ORAFRA/orcl/onlinelog/redo8b.log
	 2|	    9|	     |ONLINE |+ORASYS/orcl/onlinelog/redo9a.log
	 2|	    9|	     |ONLINE |+ORAFRA/orcl/onlinelog/redo9b.log
	 2|	   10|	     |ONLINE |+ORASYS/orcl/onlinelog/redo10a.log
	 2|	   10|	     |ONLINE |+ORAFRA/orcl/onlinelog/redo10b.log
	 1|	    5|	     |ONLINE |+ORASYS/orcl/onlinelog/redo5a.log
	 1|	    5|	     |ONLINE |+ORAFRA/orcl/onlinelog/redo5b.log
	 1|	    6|	     |ONLINE |+ORASYS/orcl/onlinelog/redo6a.log
	 1|	    6|	     |ONLINE |+ORAFRA/orcl/onlinelog/redo6b.log
	 1|	    7|	     |ONLINE |+ORASYS/orcl/onlinelog/redo7a.log
	 1|	    7|	     |ONLINE |+ORAFRA/orcl/onlinelog/redo7b.log
	 1|	    8|	     |ONLINE |+ORASYS/orcl/onlinelog/redo8a.log
	 1|	    8|	     |ONLINE |+ORAFRA/orcl/onlinelog/redo8b.log
	 1|	    9|	     |ONLINE |+ORASYS/orcl/onlinelog/redo9a.log
	 1|	    9|	     |ONLINE |+ORAFRA/orcl/onlinelog/redo9b.log
	 1|	   10|	     |ONLINE |+ORASYS/orcl/onlinelog/redo10a.log
	 1|	   10|	     |ONLINE |+ORAFRA/orcl/onlinelog/redo10b.log

24 rows selected.

SQL> select inst_id,group#,thread#,sequence#,bytes,members,status from gv$log;

   INST_ID|    GROUP#|	 THREAD#| SEQUENCE#|	 BYTES|   MEMBERS|STATUS
----------|----------|----------|----------|----------|----------|----------------
	 2|	    5|	       1|      1411|3221225472| 	2|CURRENT
	 2|	    6|	       1|      1409|3221225472| 	2|INACTIVE
	 2|	    7|	       1|      1410|3221225472| 	2|INACTIVE
	 2|	    8|	       2|      1170|3221225472| 	2|CURRENT
	 2|	    9|	       2|      1167|3221225472| 	2|INACTIVE
	 2|	   10|	       2|      1168|3221225472| 	2|INACTIVE
	 1|	    5|	       1|      1411|3221225472| 	2|CURRENT
	 1|	    6|	       1|      1409|3221225472| 	2|INACTIVE
	 1|	    7|	       1|      1410|3221225472| 	2|INACTIVE
	 1|	    8|	       2|      1170|3221225472| 	2|CURRENT
	 1|	    9|	       2|      1167|3221225472| 	2|INACTIVE
	 1|	   10|	       2|      1168|3221225472| 	2|INACTIVE

12 rows selected.

🙂

Add new group to Oracle RAC
by default each instance having 2 group so for 2-node cluster there are four redo group available for rac database.

Node-1
SQL> alter database add logfile thread 1 group 7 ('+ORASYS/orcl/onlinelog/redo7a.log','+ORAFRA/orcl/onlinelog/redo7b.log') size 3072M;

Database altered.

Node-2
SQL> alter database add logfile thread 2 group 10 ('+ORASYS/orcl/onlinelog/redo10a.log','+ORAFRA/orcl/onlinelog/redo10b.log') size 3072M;

Database altered.

Oracle RAC Database – Resize the undo table space

Node-1
SQL> set time on
02:40:17 SQL> show parameter undo

NAME				    |TYPE	|VALUE
------------------------------------|-----------|------------------------------
undo_management 		    |string	|AUTO
undo_retention			    |integer	|900
undo_tablespace 		    |string	|UNDOTBS1
02:40:25 SQL>
02:40:28 SQL> select tablespace_name,status,(bytes/1024)/1024,AUTOEXTENSIBLE,FILE_NAME from dba_data_files where tablespace_name like '%UNDO%';

TABLESPACE_NAME 	      |STATUS	|(BYTES/1024)/1024|AUT|FILE_NAME
------------------------------|---------|-----------------|---|-----------------------------------------------
UNDOTBS1		      |AVAILABLE|	      4280|YES|+ORASYS/nvtxkk/datafile/undotbs1.258.833543283
UNDOTBS2		      |AVAILABLE|	     10240|YES|+ORASYS/nvtxkk/datafile/undotbs2.264.833543433

02:40:46 SQL>
02:40:48 SQL> ALTER DATABASE DATAFILE '+ORASYS/nvtxkk/datafile/undotbs1.258.833543283' RESIZE 10240M;

Database altered.

Node-2
02:37:45 SQL> ALTER DATABASE DATAFILE '+ORASYS/nvtxkk/datafile/undotbs2.264.833543433' RESIZE 10240M;

Database altered.

02:41:26 SQL> select tablespace_name,status,(bytes/1024)/1024,AUTOEXTENSIBLE,FILE_NAME from dba_data_files where tablespace_name like '%UNDO%';

TABLESPACE_NAME 	      |STATUS	|(BYTES/1024)/1024|AUT|FILE_NAME
------------------------------|---------|-----------------|---|----------------------------------------------
UNDOTBS1		      |AVAILABLE|	     10240|YES|+ORASYS/nvtxkk/datafile/undotbs1.258.833543283
UNDOTBS2		      |AVAILABLE|	     10240|YES|+ORASYS/nvtxkk/datafile/undotbs2.264.833543433

Add the Local VIP String in tnsnames.ora

Edit below entries in file $ORACLE_HOME/network/admin/tnsnames.ora on each node

orcl1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = node01-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
      (INSTANCE_NAME = orcl1)
    )
  )

orcl2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = node02-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
      (INSTANCE_NAME = orcl2)
    )
  )

Note:-
Orcl1 is the instance1 SID
Orcl2 is the instance2 SID
Orcl is the Database SID

Node01-vip is the vip hostname of instance1 on node1
Node02-vip is the vip hostname of instance2 on node2

Increase the Cache Size of SEQUENCE for bulky or heavy dml operation tables

Increase the INITRANS for bulky or high dml concurrney tables

Binding the application batch process module on specific node

 

Administration and Maintainable Tasks and Ref

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s