SSH script to access multiple servers

#!/bin/ksh
#set -x
LOGFILE=/tmp/scan.log
echo " Scan test` date ` " > $LOGFILE
echo " " >>$LOGFILE
echo " " >>$LOGFILE
echo "==================================================================================================================" >>$LOGFILE
for i in ` cat /tmp/server.lst` ; do
server=`echo $i | awk -F\~ ' { print $1} ' `
echo Scan Status for $server  | tee -a $LOGFILE
/usr/sbin/ping $server
var_ping=$?
if [[ $var_ping -eq 0 ]] ; then
    ssh -o batchmode=yes $server true
    var_date=$?
    if [[ $var_date -eq 0 ]] ; then
      echo $server id | tee -a $LOGFILE
      ssh $server id | tee -a $LOGFILE
      echo "==================================================================================================================" >>$LOGFILE
    else
      echo $server ========== not Accessable =========== >>$LOGFILE2
    fi
else
  echo $server ========== not pingable =========== >>$LOGFILE2
fi
done

How to collect RDA in RAC env. This is applicable for only RDA 4.XXX

 How to collect RDA in RAC env. This is applicable for only RDA 4.XXX

  1.1 To setup the config file
./rda.sh -vX RDA::Remote setup_cluster

1.2 verify the nodes for which rda will be collected.

./rda.sh -vX RDA::Remote list

1.3  Collect RDA
./rda.sh -v -e REMOTE_TRACE=1

How to generate awr in a rac instance for every one hour ( snapshot)

How to generate awr in a rac instance for every one hour ( snapshot)

# Jeyaseelan

if [ $# -ne 2 ] ; then
 echo usage : ./awr.sh instance_number date
 echo         ./awr.sh 1 12-JUN-2011
exit
fi
# Directory where this script resides
set -xv
STATSDIR=.
STATSREP=.
INSTANCE_NUMBER=$1
CURR_DATE=$2

AWRREPORT=snap.sql
# -------------------------------------------------->
# ---------- Start of program

rm  $AWRREPORT
echo "Generating reports in $STATSREP ... Pls wait ..."
sqlplus -s /nolog <<!
conn / as sysdba
set serveroutput on size 1000000
set feed off term off trims on linesize 300
 dbms_output.put_line('database connection');
spool $AWRREPORT
declare
    min_snap number;
    next_snap number;
    min_snap_time varchar2(20);
    next_snap_time varchar2(20);
    fileName varchar2(255);
    cursor c1 is select instance_number,snap_id, to_char(startup_time,'ddmm_hh24mi') snap_time from dba_hist_snapshot where instance_number=$INSTANCE_NUMBER and to_char(BEGIN_INTERVAL_TIME,'dd-MON-yyyy') = '$CURR_DATE' order by snap_id;
    c1_rec c1%rowtype;
begin
    for c1_rec in c1 loop
        select min(snap_id) into next_snap from dba_hist_snapshot  where snap_id > c1_rec.snap_id and instance_number=$INSTANCE_NUMBER;
        select to_char(end_interval_time,'ddmonyyy_hh24mi') into next_snap_time from dba_hist_snapshot where instance_number=$INSTANCE_NUMBER and   snap_id = next_snap;
        dbms_output.put_line('define begin_snap='||c1_rec.snap_id);
        dbms_output.put_line('define end_snap='||next_snap);
       fileName := '$STATSREP'||'/'||'$ORACLE_SID'||'_awrrpt'||'_'||c1_rec.instance_number||'_'||c1_rec.snap_id||'_'||next_snap||'.lst';
      dbms_output.put_line('define report_name='||fileName);
        dbms_output.put_line('define report_type='||'text');
        dbms_output.put_line('define num_days='||'3');
        dbms_output.put_line('@$ORACLE_HOME/rdbms/admin/awrrpt');
    end loop;
exception
    when no_data_found then
        -- Only last snap remaining
        null;
    when others then
        dbms_output.put_line(sqlerrm);
end;
/
spool off
!
echo test
sqlplus -s /nolog  <<!
 conn / as sysdba
        @$AWRREPORT
!

How to Delete a Target in EM12C/EM13c

1. The proper way of deleting the target is via EM COnsole or EMCLI command
    EM CONSOLE => Login via manage target priv account=> search the target => Click Target Name
    => target Type => target Setup => remove target

    alternatively usinh emcli
     emcli delete_target -name="<target_Name>" -type="target_type" -delete_monitored_targets
       for example
 emcli delete_target -name="test.as.in.com:3832" -type="oracle_emd" -delete_monitored_targets
   
  Please note to delete an agent, all the related targets should be deleted first.



2. for some reason if if the host or agent is unreachable or down during target removal
   the above step will not work , you may want to use the following command
    BEGIN mgmt_admin.cleanup_agent('test.as.in.com:3832'); END;

3. Sometime while deleting the target, the connection from OEM and agent got disconnected
   we may get into the following error message

ORA-20238: Target LSNR_TEST:oracle_listener is currently in the process of being deleted
do the following steps.. if the first step failed move on the second step
exec sysman.mgmt_admin.delete_target('TARGET_NAME','TARGET_TYPE');
exec sysman.mgmt_admin.delete_target_internal('TARGET_NAME','TARGET_TYPE');
for example:
exec sysman.mgmt_admin.delete_target('ORCLE12CHOME_1','oracle_home');
exec sysman.mgmt_admin.delete_target_internal('ORCLE12CHOME_1','oracle_home');

How to disable and enable logshipping when your standby database is in maximum availabilty through DataGuard Broker Architecture.

   We may not be able to disable the logtransport when the database is in maximum availabilty mode. We may get the below error




DGMGRL> edit database 'DBNAME' set state=TRANSPORT-OFF;

Error: ORA-16627: operation disallowed since no standby databases would remain to support protection mode
 We may need to transfer the mode to maximum performance and disable the log transport.
Disable Logshipping
   1.1. Connect to dgmgrl broker
            dgmgrl
                 connect /
  1.2  Verify the protection mode
  DGMGRL> show configuration
 Configuration - DBNAME
   Protection Mode: MaxAvailability
   Databases:
     DBNAME    - Primary database
     DBNAME_SB - Physical standby database
 Fast-Start Failover: DISABLED
 Configuration Status:
 SUCCESS
 1.3 Disable the log apply in standby database
    EDIT DATABASE 'DBNAME_SB' SET STATE='APPLY-OFF';  
 1.4 Convert the database into maximum performance mode
   EDIT CONFIGURATION SET PROTECTION MODE AS MAXPERFORMANCE;
    
  1.5  Verify the configutaion
 DGMGRL> show configuration
 Configuration - DBNAME
   Protection Mode: MaxPerformance
   Databases:
     DBNAME    - Primary database
     DBNAME_SB - Physical standby database
 Fast-Start Failover: DISABLED
 Configuration Status:
 SUCCESS
 1.6  Disable to the log transport
      edit database 'DBNAME' set state='LOG-TRANSPORT-OFF';
 1.7 Verify the configuration in verbose mode
       show database verbose 'DBNAME'
      Database - DBNAME
   Role:            PRIMARY
   Intended State:  TRANSPORT-OFF
   Instance(s):
    .....
Enable Logshipping
 1.1 EDIT DATABASE 'DBNAME_SB' SET STATE='APPLY-ON';
 1.2 EDIT DATABASE 'DBNAME'  SET STATE='TRANSPORT-ON';
 1.3 EDIT CONFIGURATION SET PROTECTION MODE AS  'MAXAVAILABILITY'

   

Fast Mirror resync and Fast Rebalance - 11g new feature

1. Fast Mirror resync and Fast Rebalance - 11g new feature
     Fast Mirror resync and Fast Rebalance two new features available in 11g .

 Fast Mirror resync:

  ASM fast resync keeps track of pending changes to extents on an OFFLINE disk during an outage. The extents are resynced when the disk is brought back online or replaced.

By default, ASM drops a disk shortly after it is taken offline. You can set the DISK_REPAIR_TIME attribute to prevent this operation by specifying a time interval to repair the disk and bring it back online.

To use this feature, the disk group compatibility attributes must be set to 11.1 or higher
Fast Rebalance

In 11g, you can use the STARTUP RESTRICT command to control access to an ASM instance while you perform maintenance. This startup option is used to implement ASM Fast Rebalance feature. Rebalance operations that occur while a disk group is in RESTRICTED mode eliminate the lock and unlock extent map messaging between ASM instances in Oracle RAC environments.

When an ASM instance is started with 'STARTUP RESTRICT' option and is active in this mode, all of the disk groups are mounted in RESTRICTED mode which prevents databases from connecting to the ASM instance. In addition, the restricted clause of the ALTER SYSTEM statement is disabled for the ASM instance.
  Follow the below steps to do the fast rebalance
  1. startup restrict;
  2.  alter diskgroup dg1 mount restricted;
            ( we cannot mount the disk in mount mode and may get ORA-15251: only restricted mount is allowed in the ASM instance )

3.alter diskgroup dg1 rebalance;
  ( we cannot open database against the asm when it is in restrict mode,we may get the below error

ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '+DG1/11g/datafile/exy.256.627345731'

4.shutdown immediate
   ( we cannot enable restriced mode as we do in normal rdbms
     we may the below error
ORA-15000: command disallowed by current instance type
)

5.startup


2. How to improve rebalance operation when multiple disk maintenance activities are there and monitor the status of it.

  if you have multiple diskgroup managment commands like add disk /drop disks. It is recomended to run as a single command to have single rebalance operations rather than running the commands multiple times and invoke rebalances operations

  alter diskgroup diskgroup_name add disk '..../ drop disk '...' ;

Montior the sttaus of the asm operations using v$asm_operations view


SQL> desc v$asm_operation
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 GROUP_NUMBER                                       NUMBER
 OPERATION                                          CHAR(5)
 STATE                                              VARCHAR2(4)
 POWER                                              NUMBER
 ACTUAL                                             NUMBER
 SOFAR                                              NUMBER
 EST_WORK                                           NUMBER
 EST_RATE                                           NUMBER
 EST_MINUTES                                        NUMBER
 ERROR_CODE                                         VARCHAR2(44)

State of the operation:
WAIT - No operations running for the group
RUN - Operation running for the group
REAP - Operation is being run down
HALT - Operation halted by admin
ERRORS - Operation halted by errors

select state,ACTUAL ,sofar,EST_MINUTES ,operations from v$asm_operation

Speed-up expdp/impdp

 
1)Identify known issues/bugs and apply the required patches pro-actively on the souce & target servers
Checklist For Slow Performance Of DataPump Export (expdp) And Import (impdp) (Doc ID 453895.1)








2) Establish a dedicate connection between source and Target servers ( if feasible)
2.1. Avoid public network to transfer the data
        Open dedicated bandwidth between the source & Target server ( ip-ip copy: to speed up the copy over High Bandwidth card ) 

2.2.     Listener to be created on this network

2.3.     Create a DBLINK between source and target using this  network
2.4.     Do network based exp/imp
2.5      Don't copy LOB using network copy
ref: Performance Problems When Transferring LOBs Using IMPDP With NETWORK_LINK (Doc ID 1488229.1)








3)  Unload data only. Once data is unloaded, Later create indexes, constraints( enable novalidate if you are sure data is intact)
  ( Please let IMPDP create the partitioned table Ref: Slow DataPump Import (Impdp) Performance using Parallel Option with Pre-created Partitioned Tables (Doc ID 2098931.1))








4) In-case if you have RAC, Try to avail parallelism by running parallel exp/imp from different instances.







5) split into Multiple jobs

  5.1) Large tables can be typically exported or imported individually and smaller tables were grouped together into several batches with parallel options.







6) Table with LOB columns
   6.1) Isolate the BIG LOB tables ( don't club it together with the other small tables )
   6.1) Enable CACHE to improvise the import performance.
   6.2) Logically split the tables into multiple exp/imp jobs based on application logic using primary key or unique key.
   6.3 use DATA_OPTIONS=DISABLE_APPEND_HINT to avoid TM lock while doing multiple
import on the same lob table.
  6.4) Avoid Network COPY for LOB
  Ref: Performance Problems When Transferring LOBs Using IMPDP With NETWORK_LINK (Doc ID 1488229.1)







7) In-case if you want to use multiple imp on the same table
   7.1) use DATA_OPTIONS=DISABLE_APPEND_HINT to avoid TM lock while doing multiple import on the same lob table.





8) Undocumented parameters
    _disable_logging ( skip redo generation, it is very dangerous command , in-case if instance crash, data may get corrupted, end up doing reimport again)






9) estimate enough PGA for  creating indexes or enabling constraint at end of data unloading.