DBA

I get a request to check ldap.ora on all $ORACLE_HOME on 300 Unix servers. I do not want to logon to each server and check each oracle_home, I use the following method to check, it took a few hours to build this solution, and then I can run it again and again, and each time it only took a few minutes to complete! Step1: find out all the ORACLE_HOME registered in Grid Control. I use this query: with oraclehomevw as ( SELECT mgmt$target.host_name  , mgmt$target.target_name  , mgmt$target.target_type  , mgmt$target_properties.property_name  , mgmt$target_properties.property_value  FROM mgmt$target , mgmt$target_properties  WHERE ( mgmt$target.target_name = mgmt$target_properties.target_name )  AND ( mgmt$target.target_type = mgmt$target_properties.target_type )  and ( lower(mgmt$target_properties.property_name) like '%%home%')  and  mgmt$target_properties.property_value is not null and lower(mgmt$target_properties.property_name) not in ('domain_home','home_guid'

I use this script to delete archivelog for dataguard database running RAC. example: /path/this_script SID #!/bin/bash export PATH=/bin:/usr/bin:/usr/local/bin:/usr/sbin:.:/usr/ccs/bin:/usr/ucb:/usr/openwin/bin:/u01/oracle/dba #. $HOME/.profile ORACLE_SID=$1 export ORAENV_ASK=NO . /usr/local/bin/oraenv SCRIPT_HOME=/u01/dba/bin DATE=`date +%d%m%y_%H%M` LOG=${SCRIPT_HOME}/logs/archive_cleanup_${ORACLE_SID}.log rm -rf ${LOG} cd ${SCRIPT_HOME} touch ${LOG} echo $PATH >${LOG} $ORACLE_HOME/bin/sqlplus  -S / as sysdba <<EOF set pagesize 0 set feedback off spool ${SCRIPT_HOME}/$SID_tmp.lst select database_role from v\$database; spool off ; EOF DB_ROLE=`grep -i "PHYSICAL STANDBY" ${SCRIPT_HOME}/$SID_tmp.lst |wc -l` echo $DB_ROLE if [ "$DB_ROLE" -eq '1' ]; then $ORACLE_HOME/bin/sqlplus  -S / as sysdba <<EOF set pagesize 0 set feedback off spool ${SCRIPT_HOME}/$SID_tmp.lst select 'delete noprompt archivelog until sequence '||max(sequence#)||&#

This is for the Grid Control server. #!/bin/sh #for whatever reason, /bin/bash does not work, it does not even create the log file while server reboot # https://gruffdba.wordpress.com/2013/02/09/installing-oracle-enterprise-manager-12c-12-1-0-2-on-centos-6-3-on-vmware-workstation-8/#more-584 # Created on Oct 7th, 2016 by Jiulu Sun # This script is used on Oracle Grid Control OMS Server, it will start/stop oracle database, listener, OMS and agent when the box is reboot. # !!! I have to modify first line of $ORACLE_HOME/bin/dbstart and dbshut script to use ksh rather than sh, otherwise it run into error 'invalid identifier' using sh ORA_OWNER=orabcf #export ORAENV_ASK=NO #export ORACLE_SID=oem11g #. oraenv #export ORACLE_HOME=/u01/oracle/oem11g/product/11.2.0 #export OMS_HOME=/u01/oracle/grc/middleware/oms11g #export AGENT_HOME=/u01/oracle/grc/middleware/agent11g case "$1" in     start) echo "start">/u01/orabcf/dbstartup.log date>>

This is excellent series article to read, looks like it's easy to start processing big data. http://www.sqlservercentral.com/stairway/142480/

This is cool query that worth to refering to in order to find duplicate index that can be removed.   However, I do not think the author should use " ORDER BY INDEX_COLUMN_DATA_KEY_COLS.key_ordinal" because the column order do not need to be same but still can be a duplicated indexed column. I would rather to use "ORDER BY COLUMN_DATA_KEY_COLS.name", this way, does not matter which order the column is indexed in the column list.   Same thing about "ORDER BY INDEX_COLUMN_DATA_INC_COLS.key_ordinal", I would use "ORDER BY COLUMN_DATA_INC_COLS.name"   Below is the link and query:   http://www.sqlservercentral.com/articles/Indexing/110106/   ; WITH CTE_INDEX_DATA AS ( SELECT SCHEMA_DATA . name AS schema_name , TABLE_DATA . name AS table_name , INDEX_DATA . name AS index_name , STUFF (( SELECT ', ' + COLUMN_DATA_KEY_COLS . name + ' ' + CASE WHEN

I use the following SQL to reset the password: select  distinct  'alter profile '||profile||' limit PASSWORD_REUSE_MAX UNLIMITED;' from dba_profiles where profile in (select profile from dba_users where ACCOUNT_STATUS like '%EXPIRED%'); select  distinct 'alter profile '||profile||' limit PASSWORD_REUSE_TIME UNLIMITED;' from dba_profiles where profile in (select profile from dba_users where ACCOUNT_STATUS like '%EXPIRED%'); select  distinct 'alter profile '||profile||' limit PASSWORD_LIFE_TIME unlimited;' from dba_profiles where profile in (select profile from dba_users where ACCOUNT_STATUS like '%EXPIRED%'); select 'alter user '||sys.user$.name||' identified by values '||''''||sys.user$.password||''''||';' from sys.user$, dba_users b where sys.user$.name=b.username and  b.account_status like '%EXPIRED%';

Active DataGuard failover: https://docs.oracle.com/cd/E11882_01/server.112/e41134/role_management.htm#SBYDB4769 check the status before switchover or failover: SELECT STATUS, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID = 2; SELECT NAME, VALUE, DATUM_TIME FROM V$DATAGUARD_STATS; SELECT SWITCHOVER_STATUS FROM V$DATABASE;   Switchover steps: Old Primary: ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN; SHUTDOWN ABORT; STARTUP MOUNT;   New primary: SELECT SWITCHOVER_STATUS FROM V$DATABASE;   -- value should be TO PRIMARY or SESSIONS ACTIVE ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN; ALTER DATABASE OPEN; Bounce the database – this is important, without this step, the v$archive_dest_status will show “bad parameter” and no redo apply on standby.   New standby: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT; Select * from v$dataguard_stats; SELECT STATUS, GAP_STATUS

I use this command on linux: for db in `ps -ef|grep ckpt|awk '{print $2}'`; do  ps -fp $db; ls -l /proc/$db/cwd done sample output: UID        PID  PPID  C STIME TTY          TIME CMD oracle    2452     1  0 Feb19 ?        00:05:30 ora_ckpt_DB1 lrwxrwxrwx 1 root root 0 Feb 19 11:50 /proc/2452/cwd -> /u01/app/oracle/product/11.2.0.3EE/dbs UID        PID  PPID  C STIME TTY          TIME CMD oracle    9221     1  0 Feb26 ?        00:02:05 ora_ckpt_DB2 lrwxrwxrwx 1 root root 0 Mar  1 12:40 /proc/9221/cwd -> /u01/app/oracle/product/12.1.0.2/dbs UID        PID  PPID  C STIME TTY          TIME CMD ...

We are using OEM 12C, it reports spike of CPU utilization of a server, but vmstat result does not. According to MOS doc, OEM use kstat to calculate CPU usage. How To Calculate CPU Usage on Solaris (Doc ID 1910409.1) And this link has some info about kstat on Solaris: https://blogs.oracle.com/tpenta/entry/how_solaris_calculates_user_system So I deleveloped the following shell script to verify the result: while true; do date >> kstat.dropit kstat -n sys -s 'cpu_nsec*'|awk 'BEGIN {idletotal=0;usertotal=0;systotal=0;total=0} {if ($1~/cpu_nsec_idle/) {idletotal=idletotal+$2;} else if ($1~/cpu_nsec_user/) {usertotal=usertotal+$2;} else if ($1~/cpu_nsec_kernel/) {systotal=systotal+$2;}} END {total=idletotal+usertotal+systotal; userpercent=usertotal*100/total;syspercent=systotal*100/total;idlepercent=idletotal*100/total; print "total="total,"idletotal="idletotal,"usertotal="usertotal,"systotal="systotal,"us