redo log generation per hour basis

Redo generation ratio/per hour 

SELECT day,A "00",B "01",C "02",D "03",E "04",F "05",G "06",H "07",I "08",J "09",K "10",
L "11",M "12",N "13",O "14",P "15",Q "16",R "17",S "18",T "19",U "20",V "21",W "22",X "23",
A+B+C+D+E+F+G+H+I+J+K+L+M+N+O+P+Q+R+S+T+U+V+W+X DAY_SUM FROM (
select to_char(first_time,'YYYY.MM.DD') day,
to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'00',1,0)),'999') "A",
to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'01',1,0)),'999') "B",
to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'02',1,0)),'999') "C",
to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'03',1,0)),'999') "D",
to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'04',1,0)),'999') "E",
to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'05',1,0)),'999') "F",
to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'06',1,0)),'999') "G",
to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'07',1,0)),'999') "H",
to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'08',1,0)),'999') "I",
to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'09',1,0)),'999') "J",
to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'10',1,0)),'999') "K",
to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'11',1,0)),'999') "L",
to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'12',1,0)),'999') "M",
to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'13',1,0)),'999') "N",
to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'14',1,0)),'999') "O",
to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'15',1,0)),'999') "P",
to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'16',1,0)),'999') "Q",
to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'17',1,0)),'999') "R",
to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'18',1,0)),'999') "S",
to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'19',1,0)),'999') "T",
to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'20',1,0)),'999') "U",
to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'21',1,0)),'999') "V",
to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'22',1,0)),'999') "W",
to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'23',1,0)),'999') "X"
from v$log_history
group by to_char(first_time,'YYYY.MM.DD')
order by to_char(first_time,'YYYY.MM.DD')) ;

Redolog resizing at dataguard environment

 Steps 01: Stop/Shutdown standby database
===========================================

SQL> Alter database recover managed standby database cancel;
SQL> Shutdown immediate;

 Steps 02: delete redolog file and standby file using rm command in standby side
====================================================================================


Steps: 3. Drop online redologfiles in primary database
========================================================
SQL> alter system switch logfile;
SQL> alter system checkpoint;
SQL> alter database drop logfile group 1;

SQL> ALTER DATABASE ADD LOGFILE GROUP 1 ('/oradata1/redo1/redo01a.log',
'/oradata1/redo2/redo01b.log') SIZE 300M;

SQL> alter database drop logfile group 2;


SQL> ALTER DATABASE ADD LOGFILE GROUP 2 ('/oradata1/redo1/redo02a.log',
'/oradata1/redo2/redo02b.log') SIZE 300M;

SQL> alter database drop logfile group 3;


SQL> ALTER DATABASE ADD LOGFILE GROUP 3 ('/oradata1/redo1/redo03a.log',
'/oradata1/redo2/redo03b.log') SIZE 300M;

SQL> alter database drop logfile group 4;

SQL> ALTER DATABASE ADD LOGFILE GROUP 4 ('/oradata1/redo1/redo04a.log',
'/oradata1/redo2/redo04b.log') SIZE 300M;

==========================================================================================

Steps 04:  Drop standby logs in primary database and add standby logs in primary

SQL> ALTER DATABASE DROP STANDBY LOGFILE GROUP 5;

SQL> ALTER DATABASE DROP STANDBY LOGFILE GROUP 6;

SQL> ALTER DATABASE DROP STANDBY LOGFILE GROUP 7;

SQL> ALTER DATABASE DROP STANDBY LOGFILE GROUP 8;

SQL> ALTER DATABASE DROP STANDBY LOGFILE GROUP 9;


===========================================================================================

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/oradata1/redo1/standbyredo05a.log',
'/oradata1/redo2/standbyredo05b.log') SIZE 300M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/oradata1/redo1/standbyredo06a.log',
'/oradata1/redo2/standbyredo06b.log') SIZE 300M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/oradata1/redo1/standbyredo07a.log',
'/oradata1/redo2/standbyredo07b.log') SIZE 300M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 8 ('/oradata1/redo1/standbyredo08a.log',
'/oradata1/redo2/standbyredo08b.log') SIZE 300M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 9 ('/oradata1/redo1/standbyredo09a.log',
'/oradata1/redo2/standbyredo09b.log') SIZE 300M;

Steps 05:  create standby controlfile- and move it on standby db side
===========================================================================================

SQL> alter database create standby controlfile as '/oradata1/standby01.ctl';

Move Control file to standby remote destination
============================
Steps 06: Startup standby database--
============================

SQL> startup nomount

SQL> alter database mount standby database;

SQL> alter database recover managed standby database disconnect from session

Steps 07:  Open read-only mode and real-time redo apply
========================================================
SQL> startup
SQL> alter database recover managed standby database disconnect using current logfile;

RAC privet interconnect IP address change

Oracle RAC user isolate network connectivity for privet interconnect.
Bellow steps will describe how to change privet interconnect.

Make sure full cluster or service downtime


Step 1: Determine current private IP address

[grid@rac1 ~]$ oifcfg getif
eth0  192.168.20.0  global  public
eth1  10.0.0.0  global  cluster_interconnect

Step 2: Determine current configure Ethernet/NIC card name

[grid@rac1 ~]$ oifcfg iflist
eth0  192.168.20.0
eth1  10.0.0.0

Step 3: Add new interface Subnet ID:

[grid@rac1 ~]$ oifcfg setif -global eth1/172.20.0.0:cluster_interconnect

Step 4 : Verify the modification:

[grid@rac1 ~]$ oifcfg getif
eth0  192.168.20.0  global  public
eth1  10.0.0.0  global  cluster_interconnect
eth1  172.20.0.0  global  cluster_interconnect

Step 5: Stop cluster on all node:

[grid@rac1 ~]# su - root
[root@rac1 ~]# cd /u01/app/11.2.0/grid/bin/
[root@rac1 ~]#  ./crsctl stop cluster -all

Step 6: Modify netwrok address on network adapter of each box:

[root@rac1 ~]# ifconfig eth1 172.20.0.10 netmask 255.255.0.0 broadcast 172.20.255.255
[root@rac2 ~]# ifconfig eth1 172.20.0.11 netmask 255.255.0.0 broadcast 172.20.255.255

Step 7: Change /etc/hosts file on each node.

[root@rac1 ~]# cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1    localhost.localdomain    localhost

#10.10.10.10       rac1-priv
172.16.0.10        rac1-priv

#10.10.10.11   rac2-priv
172.20.0.11    rac2-priv

Step 8: Start cluster all node

[grid@rac1 ~]# su - root
[root@rac1 ~]# cd /u01/app/11.2.0/grid/bin/
[root@rac1 ~]#  ./crsctl start cluster -all

Step 9: Remove old private interconnect entry from cluster registry

[grid@rac1 ~]$ oifcfg delif -global eth1/10.0.0.0:cluster_interconnect

Step 10: Verify the changes:

[grid@rac1 ~]$ oifcfg getif
eth0  192.168.20.0  global  public
eth1  172.20.0.0  global  cluster_interconnect

[grid@rac1 ~]$ ping rac1-priv
PING test1-priv.localdomain (172.20.0.10) 56(84) bytes of data.
64 bytes from test1-priv.localdomain (172.20.0.10): icmp_seq=1 ttl=64 time=0.026 ms
64 bytes from test1-priv.localdomain (172.20.0.10): icmp_seq=2 ttl=64 time=0.018 ms

Diagnostics Collection Script for Grid Infrastructure

Diagnostics Collection Script for Grid Infrastructure

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



We suggest to  use diagcollection.pl script  for collecting t diagnostic information of  Oracle Grid Infrastructure ( Oracle clusterware )

Script locate at
$GRID_HOME/bin/

/u01/app/11.2.0/grid/bin/diagcollection.pl

./diagcollection.pl --collect --all

log on as  root user then run above mention script.


When invoked script
•crsData_hostname_date.tar.gz contains log files
•coreData_host_name_date.tar.gz contains core files
•ocrData_hostname_date.tar.gz files contains ocrdump and ocrcheck also show OCR backups.
•osData_hostname_date.tar.gz contains operating system logs

To remove file from local director ..

/u01/app/11.2.0/grid/bin/diagcollection.pl –clean