Segments with more IO read writes

set line 200
col segment_name for a60


WITH segment_stats 
            AS (SELECT  ss.owner || '.' || ss.object_name 
                                || DECODE (ss.subobject_name,NULL, '',
                                '(' || ss.subobject_name || ')') segment_name,
                                ss.object_type, 
                                SUM ( CASE WHEN statistic_name LIKE 'physical reads%' 
                                        THEN VALUE ELSE 0 END) reads, 
                                SUM ( CASE WHEN statistic_name LIKE 'physical writes%' 
                                        THEN VALUE ELSE 0 END) writes, 
                                ROUND (SUM (bytes) / 1048576) mb 
                    FROM     v$segment_statistics ss 
                    JOIN      dba_segments s 
                    ON     (s.owner = ss.owner AND s.segment_name = ss.object_name 
                             AND NVL (ss.subobject_name, 'x') =NVL (s.partition_name, 'x') )
                    WHERE    statistic_name LIKE 'physical reads%' 
                    OR          statistic_name LIKE 'physical writes' 
                    GROUP BY ss.owner, ss.object_name, ss.subobject_name, ss.object_type) 
SELECT segment_name, object_type, reads, writes, 
            ROUND (reads * 100 / SUM (reads) OVER (), 2) pct_reads, 
            ROUND (writes * 100 / SUM (writes) OVER (), 2) pct_writes, mb 
FROM segment_stats 
ORDER BY reads DESC 

Sample Result:

Ref: http://guyharrison.squarespace.com

Oracle SQL*Plus banner

How to set the SQL *Plus banner as in below image.?
















Append below lines to $ORACLE_HOME\sqlplus\admin\glogin.sql

SELECT 'This system is  for the  use of  authorized users  only. Individuals using this computer  system with  authority,  without  authority, or  in  excess  of their  authority, are  subject  to  having  all of  their activities on  this  system monitored  and  recorded  by  system  personnel.  In  the  course of monitoring individuals improperly using this system, or in the course of system maintenance, the activities of authorized  users also  may be monitored. Anyone using this system  expressly  consents  to such  monitoring  and  is  advised that if such monitoring reveals possible evidence of criminal activity, system personnel may provide the evidence of such monitoring  to  law enforcement  officials and will result in prosecution to the maximum extent allowable by applicable law.'  "********** WARNING ***********" FROM dual;

Note: 

This glogin.sql will be invoked immediately after connecting to database.

This sqlplus\admin\glogin.sql file should be available in Oracle database home and Oracle client home too.

Oracle express client may not have sqlplus\admin\ directory.

Delete archivelogs on standby [ tested ]

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

----------------------------------------------------------------

-r-x------ 1 oracle oinstall  654 Dec 19 06:30 delarch.sh
-r-------- 1 oracle oinstall  240 Dec 19 05:49 Maxseq.sql

[oracle@orastby ~]$ cat delarch.sh
----------------------------------------------------------------
#!/bin/bash

export ORACLE_HOME=/oracle/app/product/11.2.0/dbhome_1
export ORACLE_SID=FIN2
export NLS_DATE_FORMAT="DD-MON-RRRR HH24:MI:SS"
export TODAY=`date '+%Y%m%d'`
export TIME=`date '+%H%M%S'`
 rm -f /home/oracle/log/MAXSEQ.tmp
 $ORACLE_HOME/bin/sqlplus -S /nolog < /home/oracle/Maxseq.sql 
 seq1=`cat /home/oracle/log/MAXSEQ.tmp | awk 'NR==1{print $2}'`
#seq2=`cat /home/oracle/log/MAXSEQ.tmp | awk 'NR==2{print $2}'`
#seq3=`cat /home/oracle/log/MAXSEQ.tmp | awk 'NR==3{print $2}'`
 echo $seq1 
$ORACLE_HOME/bin/rman msglog /home/oracle/log/STANDBY_ARC_DEL.${TODAY}_${TIME}.log << EOF
 connect target /
 run{
 crosscheck archivelog all;
 delete noprompt archivelog until sequence $seq1 thread 1;
#delete noprompt archivelog until sequence $seq2 thread 2;
#delete noprompt archivelog until sequence $seq3 thread 3;
 }
 EOF
find /home/oracle/log/ -name "STANDBY_ARC_DEL.*" -mtime +7  -exec rm -rf {}  \;
----------------------------------------------------------------

[oracle@orastby ~]$ cat Maxseq.sql 
----------------------------------------------------------------
connect / as sysdba 
set heading off pages 0 echo off feedback off; 
spool /home/oracle/log/MAXSEQ.tmp; 
select thread#, max(sequence#)-10 from v$archived_log where applied ='YES' and REGISTRAR='RFS' group by thread# ; 
spool off; 
exit; 
----------------------------------------------------------------
Note: For RAC please uncomment the lines for thread 2,3,..

delete files older than x days linux

find . -name "*.trc" -mtime +35 -exec ls -lrt {} \; -- list
find . -name "*.trc" -mtime +35 -exec rm -rf  {} \; -- remove

---# above commands will find in current directory and
---# all sub directories for files older than 35 days, with .trc 


find /u01/ -name "*.log" -mtime +7 -exec ls -lrt {} \; --list
find /u01/ -name "*.log" -mtime +7 -exec rm -rf {} \; --remove

---# above commands will find in /u01/ directory and
---# all sub directories for files older than 7 days, with .log

Linux rsync, scp without password prompting

On source server:

[oracle@oradbpr] $ ls -rlt /home/oracle/.ssh/id_rsa.pub

if above file exists, copy id_rsa.pub to destination server, else generate with below ssh-keygen command.

[oracle@oradbpr] $ ssh-keygen -t rsa

Hit enter key, when prompted for passphrase.
private key and public key generated at /home/oracle/.ssh/id_rsa.pub
copy id_rsa.pub file to remote server.(destination server)

[oracle@oradbpr]$ scp /home/oracle/.ssh/id_rsa.pub oracle@oradbdr:/home/oracle
oracle@oradbdr's password:

On destination server:

[oracle@oradbdr] $ pwd
/home/oracle
[oracle@oradbdr] $ cat id_rsa.pub >> /home/oracle/.ssh/authorized_keys
[oracle@oradbdr] $ chmod 700 /home/oracle/.ssh/authorized_keys

On source server:

[oracle@oradbpr] $ rsync -avH /orabkp/ARC_PROD* oracle@oradbdr:/backup/archbkp

now rsync without password prompting.

how to execute same SQL script in all Oracle databases in Linux server [single shot]

Linux shell script: sample

[orcl@qact /home/oracle/adt]$ more all_db.sh 

#!/bin/bash

for I in GTD1 PTD1 FTD1 CTD1 MTD1 MSD1 # List of DB names
do

  echo "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"

  echo "executing scripts...."

  export ORACLE_SID=$I

  echo "DB Name: "$ORACLE_SID

  sqlplus -S -L  / as sysdba < /home/oracle/adt/adt.sql

  echo "Done."

done

[orcl@qact /home/oracle/adt]$ more adt.sql 

spool /home/oracle/adt/DB_result_$ORACLE_SID.log
set line 280
select name, open_mode, log_mode from v$database;
.
.
.
spool off;
exit;

[orcl@qact /home/oracle/adt]$ ./all_db.sh

[orcl@qact /home/oracle/adt]$ ll

total 8308

-rwxr--r-- 1 oracle dba    2567 Nov 15 12:38 adt.sql

-rwxr--r-- 1 oracle dba     314 Nov 15 12:02 all_db.sh

-rw-r--r-- 1 oracle dba   96709 Nov 15 12:39 DB_result_GTD1.log

-rw-r--r-- 1 oracle dba   90588 Nov 15 12:39 DB_result_PTD1.log

-rw-r--r-- 1 oracle dba   64756 Nov 15 12:39 DB_result_FTD1.log

-rw-r--r-- 1 oracle dba   92605 Nov 15 12:39 DB_result_CTD1.log

-rw-r--r-- 1 oracle dba   92408 Nov 15 12:39 DB_result_MTD1.log

-rw-r--r-- 1 oracle dba   92408 Nov 15 12:39 DB_result_MSD1.log

Note: " sqlplus -S -L " 

-S silent.
-L try login only once. (else 3 times)

ORA-00119 ORA-00132

during startup, faced below errors..?

ORA-00119: invalid specification for system parameter LOCAL_LISTENER

ORA-00132: syntax error or unresolved network name 'LISTENER_AZE'

Cause: non-default listener port number.

Solution: add an entry in tnsnames.ora mentioning non-default port. (blue text below)

 Note: Please avoid using port 0-1023 [ IANA range]

FLASHBACK ON / OFF [11g]

########## Flashback enable: [11g] ##########

[ reboot of database Not required for 11g]

SQL> select log_mode,flashback_on from v$database;

LOG_MODE     FLASHBACK_ON
------------ ------------------
ARCHIVELOG   NO

SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=3G;
SQL> alter system set DB_RECOVERY_FILE_DEST='D:\recovery_area';
SQL> alter system set DB_FLASHBACK_RETENTION_TARGET=120;
SQL> alter database flashback on;
SQL> select log_mode,flashback_on from v$database;

LOG_MODE     FLASHBACK_ON
------------ ------------------
ARCHIVELOG   YES





########## Flashback disable: [11g] ##########

SQL> alter database flashback off;

SQL> select log_mode,flashback_on from v$database;

LOG_MODE     FLASHBACK_ON
------------ ------------------
ARCHIVELOG   NO

FLASHBACK ON / OFF [10g]

############## Flashback enable: [10g] ########################


SQL> select open_mode, log_mode, flashback_on from v$database;

OPEN_MODE            LOG_MODE     FLASHBACK_ON
-------------------- ------------ -------------
READ WRITE           ARCHIVELOG   NO

SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=3G;
SQL> alter system set DB_RECOVERY_FILE_DEST='D:\app\Administrator\recovery_area';
SQL> alter system set DB_FLASHBACK_RETENTION_TARGET=120;
SQL> show parameter DB_RECOVERY_FILE_DEST
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database flashback on;
SQL> select * from v$bgprocess where name='RVWR';
SQL> alter database open;



############## Flashback disable: [10g]  ######################


SQL> select open_mode, log_mode, flashback_on from v$database;

OPEN_MODE            LOG_MODE     FLASHBACK_ON
-------------------- ------------ -------------
READ WRITE           ARCHIVELOG   YES

SQL> alter database flashback off;

RMAN backup script [tested] Windows, Linux

*Works on Oracle 10g, 11g

###############################################################

## For Windows with separate log for every day of the week.

###############################################################

-----------------------rman configuration-----------------------

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default PARALLELISM CAN BE > 1 ONLY FOR ENTERPRISE EDITION
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\ORACLE\PRODUCT\10.2.0\DB_2\DATABASE\SNCFBSCGH.ORA'; # default

-----------------------rman script------------------------------

file name: rman_backup.sql

connect target 'system/password'
run
{
allocate channel d1 type disk;
crosscheck archivelog all;
delete noprompt expired archivelog all;
crosscheck backup;
delete noprompt expired backup;
backup AS COMPRESSED BACKUPSET tag Full_database_open format 'E:\dbbackup_rman\db_%t_%s_p%p.dbf' database;
sql 'alter system archive log current';
sql 'alter system archive log current';
backup AS COMPRESSED BACKUPSET archivelog all format 'E:\dbbackup_rman\al_%t_%s_p%p.dbf' delete all input;
delete noprompt obsolete redundancy = 1;
backup AS COMPRESSED BACKUPSET current controlfile tag =cf1 format 'E:\dbbackup_rman\cf_%t_%s_p%p.dbf';
crosscheck archivelog all;
delete noprompt expired archivelog all;
crosscheck backup;
delete noprompt expired backup;
}

-----------------------rman.bat---------------------------------
e:
cd E:\ora10g\BIN
rman @E:\dbbackup_rman\rman_backup.sql > E:\dbbackup_rman\log\rman_backup_%date:~0,3%.LOG

----------------------------------------------------------------

How to create ".bat" file?

----------------------------------------------------------------




###############################################################

## For Linux with separate log for every day of the week.

###############################################################


#!/bin/bash
export ORACLE_SID=msats
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
cd /u01/app/oracle/product/10.2.0/db_1/bin
./rman  target 'system/password' log=/u03/rmanbackup/rmanlog.`date '+%a'` << EOF
run
{
allocate channel d1 type disk;
crosscheck archivelog all;
delete noprompt expired archivelog all;
crosscheck backup;
delete noprompt expired backup;
backup AS COMPRESSED BACKUPSET tag whole_database_open format '/u03/rmanbackup/db_%t_%s_p%p' database;
sql 'alter system archive log current';
sql 'alter system archive log current';
backup archivelog all format '/u03/rmanbackup/al_%t_%s_p%p' delete all input;
delete noprompt obsolete redundancy=1;
backup current controlfile tag =cf1 format '/u03/rmanbackup/cf_%t_%s_p%p';
crosscheck archivelog all;
delete noprompt expired archivelog all;
crosscheck backup;
delete noprompt expired backup;

}
#exit;
EOF


----------------------- END -----------------------------------

Oracle patch numbers

Quick Reference to Patchset Patch Numbers [ID 753736.1]

How to get today's date on log file name ? ( Windows & Linux )

For Windows:

C:\test> echo "SUCCESS" > SAMPLE_%date:~0,3%.LOG             
C:\test> dir                                                 
10/17/2012  01:18 PM                12 SAMPLE_Wed.LOG         

C:\test> rman @c:\rman_bkp.sql >                             c:\rman_bkp%date:~10,4%%date:~7,2%%date:~4,2%.log             

What is Oracle verify_function_11g

what is "verify_function_11g" ?

This is Oracle provided function to implement restriction in passwords, 

like lock time, grace time, failed login attemp, etc.,

verify_function - 10g

verify_function_11g - 11g

# create function using Oracle provided script utlpwdmg.sql

# should be connected as sysdba (function should be created in 'sys' schema)

SQL > conn / as sysdba

SQL > @ ?/rdbms/admin/utlpwdmg.sql 

Caution: This script alters the 'default' profile and default profile might be assigned to your application user in production database. So application users will be effected with password policy.

SQL> column OBJECT_NAME for a20
SQL> select object_name, object_type, owner from dba_objects where object_name like 'VERIFY%';

OBJECT_NAME           OBJECT_TYPE   OWNER
-------------------   ------------- -----------
VERIFY_FUNCTION_11G   FUNCTION      SYS

How to use verify_function_11g ?

CREATE PROFILE non_default LIMIT

   FAILED_LOGIN_ATTEMPTS 5
   PASSWORD_LIFE_TIME 60
   PASSWORD_REUSE_TIME 60
   PASSWORD_REUSE_MAX 5
   PASSWORD_VERIFY_FUNCTION verify_function_11g
   PASSWORD_LOCK_TIME 1/24
   PASSWORD_GRACE_TIME 10;

Alter user temp_user profile non_default;

SQL> SELECT * FROM dba_profiles WHERE resource_type='PASSWORD' ORDER BY profile;