Primary DB Scripts

#!/bin/bash
# collecting information from primary db, filename primary_db-info.sh

#Begin environment setup

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/grid
export ORACLE_SID=+ASM
export PATH=$PATH:/usr/sbin:$ORACLE_HOME/bin:$HOME/bin:.

CURRENT_DATE=`date +%Y%m%d`
logfile="/home/oracle/bin/dba_scripts/prim_db_info$CURRENT_DATE.log"
# Begin sql script, -S turns off the banner
cd /home/oracle/bin/dba_scripts/
sqlplus  -S /nolog @ '/home/oracle/bin/dba_scripts/primarydb_asm_info.sql'


export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export ORACLE_SID=TESTDB


# Begin sql script
sqlplus  -S /nolog @ '/home/oracle/bin/dba_scripts/primary_db_info.sql' 

# Validation and e-mail status
status=$?
if [ $status -gt 0 ]; then
mailx  -s "testdb_primary info gathering FAILED" dba@testdb.com << !
`df -h`
`cat $logfile`
!
else
 mailx  -s "testdb_primary info gathering" dba@testdb.com << !
`df -h`
 `cat $logfile`
!
fi

rm $logfile

---------------------------
connect / as sysdba

-------------------------------------------------------------------------
-- this script is to collect   data guard related info for primary db  --
-- It is referencing a note in metalink  
-- file name primary_db_info.sql                             --
-------------------------------------------------------------------------
column timecol new_value timestamp
column spool_extension new_value suffix

--this date format has to match whatever in the standby_db_info.sh so the spooled log can be emailed.
select to_char(sysdate,'YYYYmmdd') timecol,
'.log' spool_extension from sys.dual;
spool prim_db_info&&timestamp&&suffix APPEND

set echo off
set feedback off
set lines 200
set pagesize 35
set trim on
set trims on

alter session set nls_date_format = 'MON-DD-YYYY HH24:MI:SS';

COLUMN time heading "PRIMARY DATABASE INFO GENERATED ON" FORMAT a35
select to_char(sysdate) time from dual;

column pid Format 9999999999
column status  heading STATUS JUS L Format a13 tru
column thread# JUS R Format  99
column sequence# Format 9999999999
column block# Format 9999999999
column delay_mins Format 99999999999

-- Query v$managed_standby to see the status of processes involved in
-- the shipping redo on this system.  Does not include processes needed to
-- apply redo.

select process,status,client_process,sequence#
from v$managed_standby
where PROCESS='LNS' OR (PROCESS='ARCH' AND STATUS='CONNECTED');

-- The following query will determine the current sequence number
-- and the last sequence archived.  If you are remotely archiving
-- using the LGWR process then the archived sequence should be one
-- higher than the current sequence.  If remotely archiving using the
-- ARCH process then the archived sequence should be equal to the
-- current sequence.  The applied sequence information is updated at
-- log switch time.

select ads.dest_id,max(sequence#) "Current Sequence",
           max(log_sequence) "Last Archived"
   from v$archived_log al, v$archive_dest ad, v$archive_dest_status ads
   where ad.dest_id=al.dest_id
   and al.dest_id=ads.dest_id
   and al.resetlogs_change#=(select max(resetlogs_change#) from v$archived_log )
   group by ads.dest_id;

-- In the following the database_role should be primary. If protection_level is different
-- than protection_mode then for some reason the mode listed in
-- protection_mode experienced a need to downgrade.  Once the error
-- condition has been corrected the protection_level should match the
-- protection_mode after the next log switch.

column role format a7 tru
column name format a10 wrap

select name,platform_id,database_role role,log_mode,
       flashback_on flashback,protection_mode,protection_level 
from v$database;

-- ARCHIVER can be (STOPPED | STARTED | FAILED). FAILED means that the
-- archiver failed to archive a log last time, but will try again within 5
-- minutes. LOG_SWITCH_WAIT is the ARCHIVE LOG/CLEAR LOG/CHECKPOINT event log
-- switching is waiting for.  Note that if ALTER SYSTEM SWITCH LOGFILE is
-- hung, but there is room in the current online redo log, then value is
-- NULL

column host_name format a20 tru
column version format a9 tru

select instance_name,host_name,version,archiver,log_switch_wait
from v$instance;
 


-- Force logging is not mandatory but is recommended.
-- During normal operations it is acceptable for
-- SWITCHOVER_STATUS to be SESSIONS ACTIVE or TO STANDBY.

column force_logging format a13 tru
column remote_archive format a14 tru
column dataguard_broker format a16 tru

select force_logging,remote_archive,
       supplemental_log_data_pk,supplemental_log_data_ui,
       switchover_status,dataguard_broker
from v$database; 

-- This query produces a list of all archive destinations.  It shows if
-- they are enabled, what process is servicing that destination, if the
-- destination is local or remote, and if remote what the current mount ID
-- is.

column destination format a35 wrap
column process format a7
column archiver format a8
column ID format 99
column mid format 99

select dest_id "ID",destination,status,target,
       schedule,process,mountid  mid
from v$archive_dest where dest_id=1 or dest_id=2;

-- This select will give further detail on the destinations as to what
-- options have been set.  Register indicates whether or not the archived
-- redo log is registered in the remote destination control file.

set numwidth 8
column ID format 99

select dest_id "ID",archiver,transmit_mode,affirm,async_blocks async,
       net_timeout net_time,delay_mins delay,reopen_secs reopen,
       register,binding
from v$archive_dest where dest_id=1 or dest_id=2;

-- The following select will show any errors that occured the last time
-- an attempt to archive to the destination was attempted.  If ERROR is
-- blank and status is VALID then the archive completed correctly.

column error format a55 wrap

select dest_id,status,error from v$archive_dest
where dest_id=1 or dest_id=2;
-- The query below will determine if any error conditions have been
-- reached by querying the v$dataguard_status view (view only available in
-- 9.2.0 and above):

column message format a80

select message, timestamp
from v$dataguard_status
where severity in ('Error','Fatal')
order by timestamp;



-- The following select will attempt to gather as much information as
-- possible from the standby.  SRLs are not supported with Logical Standby
-- until Version 10.1.

set numwidth 8
column ID format 99
column "SRLs" format 99
column Active format 99

select dest_id id,database_mode db_mode,recovery_mode,
       protection_mode,standby_logfile_count "SRLs",
       standby_logfile_active ACTIVE,
       archived_seq#
from v$archive_dest_status where dest_id=1 or dest_id=2;



-- The following query is run on the primary to see if SRL's have been
-- created in preparation for switchover.

SELECT 'STANDBY LOGS (SRL)' AS "The Following Are:" from dual;
select group#,sequence#,bytes from v$standby_log;

-- The above SRL's should one more in number and match in size with the ORL's
-- returned below:

SELECT 'ONLINE REDO LOGS (ORL)' AS "The Following Are:" from dual;
select group#,thread#,sequence#,bytes,archived,status from v$log;


spool off

exit;

----------------------------
--file name primarydb_asm_info.sql

connect / as sysdba
column timecol new_value timestamp
column spool_extension new_value suffix

--this date format has to match whatever in the standby_db_info.sh so the spooled log can be emailed.
select to_char(sysdate,'YYYYmmdd') timecol,
'.log' spool_extension from sys.dual;
spool prim_db_info&&timestamp&&suffix

set echo OFF
set feedback off

SET LINESIZE 600
set pagesize 35
set trim on
set trims on

alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

COLUMN time heading "ASM DISK GROUP INFO GENERATED ON" FORMAT a35
select  sysdate time from dual;
select 'HOSTNAME:'AS HOSTNAME, MACHINE " " from v$session where program like '%SMON%';


SELECT GROUP_NUMBER, NAME, TOTAL_MB/1024 AS TOTAL_GB, FREE_MB/1024 AS FREE_GB FROM V$ASM_DISKGROUP ORDER BY GROUP_NUMBER;

SELECT GROUP_NUMBER, DISK_NUMBER,BYTES_READ/1024/1024/1024 AS GB_READ,BYTES_WRITTEN/1024/1024/1024 GB_WRITTEN, READ_ERRS,WRITE_ERRS,HOT_READS,HOT_WRITES, COLD_READS, COLD_WRITES FROM v$ASM_DISK ORDER BY GROUP_NUMBER,DISK_NUMBER;


spool off

exit;