Check block corruption in oracle database

Errors in file /opt/oradiag/diag/rdbms/phsub/phsub2/trace/phsub2_pz99_23564.trc  (incident=496822):
ORA-01578: ORACLE data block corrupted (file # 1, block # 112636)
ORA-01110: data file 1: '/oracle/oradata1/phsub/system01.dbf'
Incident details in: /opt/oradiag/diag/rdbms/phsub/phsub2/incident/incdir_496822/phsub2_pz99_23564_i496822.trc




SELECT DISTINCT owner, segment_name
FROM   gv$database_block_corruption dbc
       JOIN dba_extents e ON dbc.file# = e.file_id AND dbc.block# BETWEEN e.block_id and e.block_id+e.blocks-1
ORDER BY 1,2;


SELECT segment_type, segment_name
  FROM dba_extents
WHERE file_id = 1
   AND 112636 BETWEEN block_id AND block_id + blocks -1;
 
 


Validate datafile 249 block 226263 ;

VALIDATE CHECK LOGICAL DATAFILE 249;

VALIDATE DATAFILE 249;

Password reset procedure oracle

Deleting a password from history table in oracle and resetting the password

CREATE OR REPLACE PROCEDURE password_reset( p_user_name IN VARCHAR2,
                                       p_password  IN VARCHAR2) is
  ln_user_id   NUMBER;
  lc_alter     VARCHAR2(100);
BEGIN
   ln_user_id := NULL;
   lc_alter   := NULL;
   SELECT user_id
     INTO ln_user_id
  FROM dba_users
 WHERE username = p_user_name;
   dbms_output.put_line('User id is :'||ln_user_id);
 
   DELETE FROM  user_history$
    WHERE user#=ln_user_id;
   dbms_output.put_line(SQL%ROWCOUNT||' rows deleted for the User id :'||ln_user_id);
   COMMIT;
 
   lc_alter := 'alter user '||p_user_name||' identified by '||p_password||' account unlock';
   EXECUTE IMMEDIATE lc_alter;
 
EXCEPTION
WHEN OTHERS
THEN
   dbms_output.put_line('Exception Message is :'||SQLERRM);
END;


grant execute on sys.password_reset to user;



Execute it using the following

DECLARE
  P_USER_NAME VARCHAR2(32767);
  P_PASSWORD VARCHAR2(32767);

BEGIN
  P_USER_NAME := 'xxx';
  P_PASSWORD := 'Passwrd#1';

  SYS.PASSWORD_RESET ( P_USER_NAME, P_PASSWORD );
  COMMIT;
END;

pre req's for 11g oracle database install

For linux

yum install xorg-x11-apps

yum install xorg-x11-apps.x86_64
xhost +


yum install wget
For linux 7

Oracle Linux 7
cd /etc/yum.repos.d
wget http://yum.oracle.com/public-yum-ol7.repo

yum install oracle-rdbms-server-11gR2-preinstall


after install try to install below rpm's

If fails saying gpgcheck failed

add below line and rerun the commands

yum install --nogpgcheck gcc -y





yum install binutils -y
yum install compat-libstdc++-33 -y
yum install compat-libstdc++-33.i686 -y
yum install gcc -y
yum install gcc-c++ -y
yum install glibc -y
yum install glibc.i686 -y
yum install glibc-devel -y
yum install glibc-devel.i686 -y
yum install ksh -y
yum install libgcc -y
yum install libgcc.i686 -y
yum install libstdc++ -y
yum install libstdc++.i686 -y
yum install libstdc++-devel -y
yum install libstdc++-devel.i686 -y
yum install libaio -y
yum install libaio.i686 -y
yum install libaio-devel -y
yum install libaio-devel.i686 -y
yum install libXext -y
yum install libXext.i686 -y
yum install libXtst -y
yum install libXtst.i686 -y
yum install libX11 -y
yum install libX11.i686 -y
yum install libXau -y
yum install libXau.i686 -y
yum install libxcb -y
yum install libxcb.i686 -y
yum install libXi -y
yum install libXi.i686 -y
yum install make -y
yum install sysstat -y
yum install unixODBC -y
yum install unixODBC-devel -y
yum install zlib-devel -y
yum install elfutils-libelf-devel -y

yum install ksh -y


Once everything is installed.. proceed with installation


while installing oracle database

During the link phase you will encounter an error invoking the "ins_emagent.mk" file. To fix this, edit the "$ORACLE_HOME/sysman/lib/ins_emagent.mk", doing a search and replace for the line shown below.

FROM:
$(MK_EMAGENT_NMECTL)
TO  :
$(MK_EMAGENT_NMECTL) -lnnz11


****************************************************************

For Solaris

pkg install pkg:/x11/diagnostic/x11-info-clients
pkg install oracle-rdbms-server-12-1-preinstall


ndd -set /dev/udp udp_smallest_anon_port 9000
ndd -set /dev/udp udp_largest_anon_port 65500
ndd -set /dev/tcp tcp_smallest_anon_port 9000
ndd -set /dev/tcp tcp_largest_anon_port 65500

Scheduler job oracle db

BEGIN
  SYS.DBMS_SCHEDULER.CREATE_JOB
    (
       job_name        => 'schema.JOB'
      ,start_date      => TO_TIMESTAMP_TZ('2017/06/07 15:00:00.000000 +07:00','yyyy/mm/dd hh24:mi:ss.ff tzr')
      ,repeat_interval => 'FREQ=MINUTELY;INTERVAL=10'
      ,end_date        => NULL
      ,job_class       => 'DEFAULT_JOB_CLASS'
      ,job_type        => 'STORED_PROCEDURE'
      ,job_action      => 'shema.proc_name'
      ,comments        => 'ITSM xxxx'
    );
    END;
    /

Using logrorate in linux for oracle listener and alert logs

Do the following as root user

cd /etc/logrotate.d

vi oracle_listener

/u01/app/11.2.0.4/grid/log/diag/tnslsnr/nasipexadb01/listener_scan3/trace/listener_scan3.log {
weekly
copytruncate
rotate 4
compress
}
/u01/app/11.2.0.4/grid/log/diag/tnslsnr/nasipexadb01/listener_scan2/trace/listener_scan2.log {
weekly
copytruncate
rotate 4
compress
}
/u01/app/11.2.0.4/grid/log/diag/tnslsnr/nasipexadb01/listener_scan1/trace/listener_scan1.log {
weekly
copytruncate
rotate 4
compress
}
/u01/app/oracle/diag/tnslsnr/nasipexadb01/listener/trace/listener.log {
weekly
copytruncate
rotate 4
compress
}

Refer logrorate manpage for more info on parameters.

The above does copy the log file to a diff name and zip it, then truncates the original files.

Rotation will be done weekly and 4 copies will be retained.

If you want to force rotation immediate run the below command

logrotate -f oracle_listener

Note: You may change permissions on the file to 777 just in case if you dont want to ask sys admin every time you want to edit the file

Hanganalyze and Systemstate collection during RAC hang

During hanging situation in database, you may do :

Collection commands for Hanganalyze and Systemstate: RAC

sqlplus '/ as sysdba'
oradebug setorapname reco
oradebug unlimit
oradebug -g all hanganalyze 3
oradebug -g all hanganalyze 3
oradebug -g all dump systemstate 258
oradebug -g all dump systemstate 258
SQL> oradebug tracefile_name

Then upload alert.log and in case hanging reoccur. 

[off topic] Thinkorswim scripts

MTD
plot mreturn =round(100* (close(period = AggregationPeriod.DAY)-close(period = AggregationPeriod.DAY)[21])/(close(period = AggregationPeriod.DAY)[21]),2);
;

WTD
plot wreturn =round(100* (close(period = AggregationPeriod.DAY)-close(period = AggregationPeriod.DAY)[5])/(close(period = AggregationPeriod.DAY)[5]),2);
;

1 year
plot yearreturn =100* (close(period = AggregationPeriod.DAY)-close(period = AggregationPeriod.DAY)[251])/(close(period = AggregationPeriod.DAY)[251]);
;

YTD

def startOfYear = GetYear() <> GetYear()[1];
rec startingClose = if startOfYear then close[1] else startingClose[1];
plot percentChange = 100 * (close / startingClose - 1);
percentChange.AssignValueColor(if percentChange > 1.0 then Color.BLACK else if percentChange < -1.0 then Color.BLACK else Color.CURRENT); AssignBackgroundColor(if percentChange > 1.0 then Color.GREEN else if percentChange < -1.0 then Color.RED else Color.CURRENT);


Study filter for scan


SV_1year_perf() is greater than or equal to 30 and SV_YTD_perf() is greater than or equal to 18