backup details script

---> Sort by Backup Type Values (ARCH,INC,REC) in GB


set lines 220
set pages 1000
col cf for 9,999
col df for 9,999
col elapsed_seconds heading "ELAPSED|SECONDS"
col i0 for 9,999
col i1 for 9,999
col l for 9,999
col END_TIME for a20
col START_TIME for a20
col DOW for a15
col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES"
col session_recid for 999999 heading "SESSION|RECID"
col session_stamp for 99999999999 heading "SESSION|STAMP"
col status for a10 trunc
col time_taken_display for a10 heading "TIME|TAKEN"
col output_instance for 9999 heading "OUT|INST"
select
  j.session_recid, j.session_stamp, x.device_type,
  to_char(j.start_time, 'yyyy-mm-dd hh24:mi:Ss') start_time,
  to_char(j.end_time, 'yyyy-mm-dd hh24:mi:Ss') end_time,
  (j.output_bytes/1024/1024/1024) output_gbytes, j.status, j.input_type,
  decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',
                                     3, 'Tuesday', 4, 'Wednesday',
                                     5, 'Thursday', 6, 'Friday',
                                     7, 'Saturday') dow,
  j.elapsed_seconds, j.time_taken_display
from V$RMAN_BACKUP_JOB_DETAILS j
  left outer join (select
                     d.session_recid, d.session_stamp, d.device_type,
                     sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,
                     sum(case when d.controlfile_included = 'NO'
                               and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,
                     sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,
                     sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,
                     sum(case when d.backup_type = 'L' then d.pieces else 0 end) L
                   from
                     V$BACKUP_SET_DETAILS d
                     join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
                   where s.input_file_scan_only = 'NO'
                   group by d.session_recid, d.session_stamp, d.device_type) x
    on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp
  left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id
                   from GV$RMAN_OUTPUT o
                   group by o.session_recid, o.session_stamp)
    ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp
where j.start_time > trunc(sysdate)-&NUMBER_OF_DAYS
and j.input_type like '%&BACKUP_TYPE%'
order by j.start_time
/

ASM Installation

  How To:Setup Oracle ASM 11gR2 on Oracle Enterprise Linux
  ---------------------------------------------------------

Software Used
---------------
Vmware
Oracle Enterprise Linux:
Oracle Database 11g Release2(11.2.0.4.0) For Linux x86-64
Oracle Grid Infracture 11g Release 2(11.2.0.4.0) for Linux x86-64

What Will We do?
---------------
Setup Oracle grid Infracture for standalone "ASM"                                -Under User grid
Setup Oracle Database                                                            -Under User oracle


Packages
----------
We will need to install Prerequisites packages for oracle database               -Under User root

We will need to install Prerequisites packages for ASM for Oracle Linux 6         -Under User root

get them from:
http://www.oracle.com/technetwork/server-storage/linux/downloads

You have to choose the right rpm for your kernel

--Check your Kernel
uname -rm

Asm Packages
------------
--oracleasm-support
--oracleasm
--oracleasmlib

rpm -ivh "package name"



--Create groups for database
groupadd dba
groupadd oinstall
groupadd oper
------
--Create groups for ASM
groupadd asmadmin
groupadd asmdba
groupadd asmoper


--Create Users
useradd -g oinstall -G dba,oper,asmdba -d /home/oracle oracle                   --Oracle User
useradd -g oinstall -G dba,asmadmin,asmdba,asmoper -d /home/grid grid           --Grid User

--Set passwords for users created above
passwd oracle            
passwd grid

--Make directory structures for database and grid infracture installation
mkdir -p /u01/app/oracle/product/11.2.0/db_home1
mkdir -p /u01/app/grid/product/11.2.0/grid

--Change ownership for /u01 mount point to oracle and grid for installation
chown -R oracle:oinstall /u01
chown -R grid:oinstall /u01/app/grid/product/11.2.0/grid

--Change permision to /u01 to read,write,execute
chmod -R 775 /u01



--Set profile in .bash_profile from oracle user
export ORACLE_SID=ORCL
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/11.2.0/db_home1
export PATH=$ORACLE_HOME/bin:$PATH

--Set profile in .bash_profile from grid user
export ORACLE_SID=+ASM
export ORACLE_BASE=/u01/app/grid
export ORACLE_HOME=/u01/app/grid/product/11.2.0/grid
export PATH=$ORACLE_HOME/bin:$PATH

--Create Linux Hard disk using VMware "Adding 3 Hard Disk minimum of 5 GB"

--To avoid rebooting the machine to mount the new Hard disks
echo "- - -" > /sys/class/scsi_host/host0/scan
echo "- - -" > /sys/class/scsi_host/host1/scan
echo "- - -" > /sys/class/scsi_host/host2/scan
echo "- - -" > /sys/class/scsi_host/host3/scan


--If still not dected reboot the server

--List the new Hard diske
fdisk -l

Disk /dev/sdb: 2147 MB, 2147483648 bytes
255 heads, 63 sectors/track, 261 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0xc9c7323b

Disk /dev/sdc: 2147 MB, 2147483648 bytes
255 heads, 63 sectors/track, 261 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0xdac64d98


--Partition the newly added disk to  primary partitions          
fdisk /dev/sdb
fdisk /dev/sdc
fdisk /dev/sdd


--List the partitioned disks
fdisk -l

--Change the ownership to user grid on the partitioned disks
chown grid:asmadmin /dev/sdb1
chown grid:asmadmin /dev/sdc1
chown grid:asmadmin /dev/sdd1

--Change the permissions on the partitioned disks
chmod 660 /dev/sdb1
chmod 660 /dev/sdc1
chmod 660 /dev/sdd1


--Configure ASM and Create ASM Disks

--Configure
ll /dev/sd*
/etc/init.d/oracleasm configure
/etc/init.d/oracleasm scandisks
/etc/init.d/oracleasm listdisks
/etc/init.d/oracleasm status

Usage: /etc/init.d/oracleasm {start|stop|restart|enable|disable|configure|createdisk|deletedisk|querydisk|listdisks|scandisks|status}


--Create ASM Disk from the partioned disks
/etc/init.d/oracleasm createdisk VOL1 /dev/sdb1
/etc/init.d/oracleasm createdisk VOL2 /dev/sdc1
/etc/init.d/oracleasm createdisk VOL3 /dev/sdd1


---Check once the disks are created
[grid@oracle1 grid]$ ls -lrt /dev/oracleasm/disks/*
brw-rw----. 1 grid asmadmin 8, 17 Jan 19 18:59 /dev/oracleasm/disks/VOL1
brw-rw----. 1 grid asmadmin 8, 33 Jan 19 18:59 /dev/oracleasm/disks/VOL2
brw-rw----. 1 grid asmadmin 8, 49 Jan 19 19:00 /dev/oracleasm/disks/VOL3





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

Display issue:
--------------

1> execute runinstaller and select no
2>new terminal type xhost+
3>new terminal execute runinstaller again


--> If still error make the below entry in hosts file with the servername

Server name:oracle1

[root@oracle1 ~]# cat /etc/hosts
127.0.0.1   oracle1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         oracle1 localhost localhost.localdomain localhost6 localhost6.localdomain6


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

Executing sql script to run in background

lvpu7:/opt/oraprd # cat rebuild_index.sh

#!/bin/ksh

sqlplus "/ as sysdba" << EOF

@/opt/oraprd/rebulid.sql

exit;

EOF

lvpu7:/opt/oraprd # cat rebulid.sql

spool index_rebuild.log

set echo on time on timing on

alter index ELCC.CK_CLIENT_MEMBER_ID rebuild nologging;

alter index ELCC.CLGDR_X1 rebuild nologging;

alter index ELCC.CLNT_CONFIDENCE_SCORE rebuild nologging;

spool off

nohup sh rebuild_index.sh &

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

#!/bin/ksh

sqlplus <<-eof o:p="">

/ as sysdba

set echo on timing on

exec dbms_stats.gather_schema_stats(ownname=>'CASHPOOL',options=>'gather auto',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,degree=>6);

EOF

nohup sh gather.sh &

SH

Hi,

Shift : Morning

Handover by : Harsha

Takeover by : Sayeed

1) Incidents Ticket Details:

No. of WIP Ticket(s) : 5

No. of SLEEP Ticket(s) : 0

No. of Solved Ticket(s) : 0

No. of Escalated Ticket(s) in Bin : 45

No. of Customer Ticket(s) : 1

No. of tickets with priority 1,2 : 0

Remarks:

TT 18524622 - SR 3-6350571721 - Priority 2 - Please monitor.

TT 18518010 - Weekly copy failed,Restore the database with Rman.Please refer TT.

2) Task Ticket Details:

No. of ticket(s) in progress :TT0018519980 -> Backup is in progress Reffer TT log for details

No. of escalated Ticket(s)in Bin :

Remarks:

3) Vendor cases:

4) Additional Information:

5) Escalation to L2 group:

Incident tickets :

Task tickets :

Problem tickets :

Remarks:

6) Message to MOD group:Nil