CRS ( Oracle Clusterware ) Cheat Sheet

Oracle Clusterware enables servers in an Oracle database Real Application Cluster to coordinate simultaneous workload on the same database files. The crsctl command provides administrators many useful capabilities. For example, with crsctl, you can check Clusterware health disable/enable Oracle Clusterware startup on boot, find information on the voting disk and check the Clusterware version, and more.

1. Do you want to check the health of the Clusterware?
# crsctl check crs
CSS appears healthy
CRS appears healthy
EVM appears healthy

if you receive any output other than the "…appears healthy" output, then kill crsd.bin on the node that gave you the unhealthy output ( login as root)

2. Do you want to reboot a node for maintenance without Clusterware coming up on boot?
## Disable clusterware on machine2 bootup:
# crsctl disable crs
## Stop the database then stop clusterware processes:
# srvctl stop instance –d db –i db2
# crsctl stop crs
# reboot

## Enable clusterware on machine bootup:
# crsctl enable crs
# crsctl start crs
# srvctl start instance –d db –i db2

3. Do you wonder where your voting disk is?
# crsctl query css votedisk
0. 0 /dev/raw/raw2

4. Do you need to find out what clusterware version is running on a server?
# crsctl query crs softwareversion
CRS software version on node [db2] is [10.2.0.2.0]

STOP CRS

Connect as root and from ORA_CRS_HOME execute the following command on both servers

[root@vmractest1 bin]# ./crsctl stop crs
Stopping resources.
Successfully stopped CRS resources
Stopping CSSD.
Shutting down CSS daemon.
Shutdown request successfully issued.

Restart CRS on Both Nodes

[root@vmractest1 bin]# ./crsctl start crs
[root@vmractest2 bin]# ./crsctl start crs
Restarting CRS brought up all resources:


CRS Verion

$ crsctl query crs softwareversion
$ crsctl query crs activeversion

Monitor CRS Heath using OEM

$ cat /opt/oracle/admin/OEM/UserDefinedHostScripts/checkCRS.sh
#!/usr/bin/ksh
# OEM User Defined OS Metric. Script is called by OEM to monitor the health of CRSd on a particular node.
if [[ $(/ora01/oracle/product/crs_1020/bin/crsctl check crsd) = "CRS appears healthy" ]]
then print "em_result=0\nem_message=CRSd is healthy\n"
else print "em_result=1\nem_message=CRSd not responding\n"
fi
exit

Datapump Sample Scripts

# Prepare for datapump
sqlplus / as sysdba
grant create any directory to scott;
create or replace directory dumpdir as '/ora01/oracle/dpdump/';
grant read,write on directory dumpdir to scott;

# export schema
expdp scott/manager schemas=scott dumpfile=t1 logfile=t1.log directory=dumpdir job_name=dpjob1

# impot schema
impdp scott/manager schemas=scott dumpfile=t1 logfile=t1.log directory=dumpdir job_name=dpjob1

# tables
expdp scott/manager tables=t1 dumpfile=t1 logfile=t1.log directory=dumpdir job_name=dpjob1

--
# export schema metadata only
date
ORACLE_SID=demod1;ORAENV_ASK=NO;. oraenv;ORAENV_ASK=YES
expdp system dumpfile=demod_dmp_dir:ACT_OWNCStructure.dmp logfile=demod_dmp_dir:ACT_OWNCStructure.log \
schemas=act_ownc content=METADATA_ONLY
date


# export schema data only
date
ORACLE_SID=demod1;ORAENV_ASK=NO;. oraenv;ORAENV_ASK=YES
expdp system dumpfile=demod_dmp_dir:demod_ACT_OWNC.dmp logfile=demod_dmp_dir:demod_ACT_OWNC.log \
schemas=act_ownc content=DATA_ONLY
date

# export schema data estimate_only
date
ORACLE_SID=demod1;ORAENV_ASK=NO;. oraenv;ORAENV_ASK=YES
expdp system estimate_only=y logfile=demod_dmp_dir:demod_ACT_OWNC_est.log \
schemas=act_ownc content=DATA_ONLY
date

# export data + metadata
date
ORACLE_SID=demod1;ORAENV_ASK=NO;. oraenv;ORAENV_ASK=YES
expdp act_ownb dumpfile=demod_dmp_dir:ACT_OWNBSelected2.dmp logfile=demod_dmp_dir:ACT_OWNBSelectedExport2.log \
tables=\
ACT_OWN_STATS,\
ALIAS,\
ATTRIBUTE
date

# export tables data only
date
ORACLE_SID=demod1;ORAENV_ASK=NO;. oraenv;ORAENV_ASK=YES
expdp act_ownc dumpfile=demod_dmp_dir:demod_ACT_OWNCp1.dmp logfile=demod_dmp_dir:demod_ACT_OWNCp1.log \
content=DATA_ONLY tables=\
TRAFFIC_UNCOMPRESSED_BASE,\
TRAFFIC_UNCOMPRESSED_FORECAST,\
TRAFFIC_UNCOMPRESSED_SP1,\
TRAFFIC_UNCOMPRESSED_SP3,\
TRAFFIC_UNCOMPRESSED_SP2
date


# import data ; truncate existing table before import
date
ORACLE_SID=demod1;ORAENV_ASK=NO;. oraenv;ORAENV_ASK=YES
impdp system/eaxo042 dumpfile=demod_dmp_dir:ACT_OWNBSelected.dmp logfile=demod_dmp_dir:actowncimp.log remap_schema=act_ownb:act_ownb \
table_exists_action=truncate
date

# import data Only; truncate existing table before import
date
ORACLE_SID=demod1;ORAENV_ASK=NO;. oraenv;ORAENV_ASK=YES
impdp system dumpfile=demod_dmp_dir:ACT_OWNDSPtables.dmp logfile=demod_dmp_dir:actowncimpsp.log \
remap_schema=act_ownd:act_ownc content=data_only \
table_exists_action=truncate
date


# import data append
date
ORACLE_SID=demod1;ORAENV_ASK=NO;. oraenv;ORAENV_ASK=YES
impdp system dumpfile=demod_dmp_dir:ACT_OWNB_mar13.dmp logfile=demod_dmp_dir:actownbimpmar13.log remap_schema=act_ownb:act_ownb \
table_exists_action=append
date

Clone Database

Create a Duplicate database with NOCATALOG on same Node

Source : LLAP
Target : llad

Steps
1. Prerequisities
Source is at least mounted

2. Init.ora configuration in duplicate database to AVOID original file being OVERWRITTEN

remote_login_passwordfile=EXCLUSIVE
db_file_name_convert =('LLAP','llad')
log_file_name_convert=('LLAP','llad')
Modify Name and location of control file
database name
instance name

create password file
startup nomount

3. Setup sqlnet configuration
add clone database entry to tnsnames.ora and listener.ora

4. Backup of Source Database

5. Get SCN/Log sequence/Time for cloning

6. Duplicate database

7. Post Duplicate Steps

Example

-- setup duplicate database enviornment(llad), source is LLAP
# create directory structure
# create init.ora file - cp init.ora init.ora
# change all references to except log_archive_????
# add following lines to init.ora
remote_login_passwordfile=EXCLUSIVE
db_file_name_convert =('LLAP','llad')
log_file_name_convert=('LLAP','llad')
# add target to oratab
# create link for init file in $ORACLE_HOME/dbs for target
# create password file in $ORACLE_HOME/dbs
orapwd file=orapw password=
# add target database to tnsnames.ora
# add target database to listerner.ora
# reload the listener
# startup nomount <-- target database
select * from v$instance


# backup source database

bkup.sh
-------
date
echo LLAP |. oraenv
cd /ora01/oracle/admin/BACKUP/llad
rman nocatalog target / cmdfile=rman_backup_LLAP_special.cmd
status=$?
date
exit $status


bkup.cmd
--------
# host 'echo Starting rman_backup_LLAP_special.cmd on rs204, `date`.';
# backup to several channels to disk
run {
allocate channel LLAP_d1 type disk format '/psoft200/oracle/BACKUP/LLAP/%U';
allocate channel LLAP_d3 type disk format '/psoft200/oracle/BACKUP/LLAP/%U';
backup setsize = 62914560
(database)
(current controlfile);
}
# host 'echo Ending rman_backup_LLAP_special.cmd on rs204, `date`.';



# get SCN
Method 1) set numwidth 18; select max(CHECKPOINT_CHANGE#) from v$backup_datafile;
2) list backup from rman
3) list backupset for database from rman


dup.sh
------
date
echo LLAP |. oraenv
cd /ora01/oracle/admin/BACKUP/llad
rman target / auxiliary "sys/manager@llad" @dup.cmd
date
exit


dup.cmd
-------
run {
set until scn 607710483792; <-- from get SCN step
duplicate target database to llad;
}

# add temp file
# rename global_name
# remove parameter added to target database for cloning
# bounce target database

References:
Metalink Note : 245262.1
72912.1
73912.1
228257.1
259694.1

RMAN Backup Report to get SCN for Recovery

Two methods.

1) >rman target / log=rmanlistbackup.log
Rman>list backup;
Rman>exit

vi rmanlistbackup.log. The last entry in the file shows this:
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
22021 Full 27M DISK 00:00:01 15-MAY-07
BP Key: 22025 Status: AVAILABLE Tag:
Piece Name: /ora01/oracle/product/9.2.0.4.0/dbs/c-4049168349-20070515-02 <-- control file backup at the very end of the last disk backup.
Controlfile Included: Ckp SCN: 607710483792 Ckp time: 15-MAY-07

2) login to database
check following dictionary Views

from v$backup_set where start_time > sysdate -1

from v$backup_piece
from v$backup_datafile
from v$backup_redolog


This will provide last SCN number
set numwidth 18
select max(CHECKPOINT_CHANGE#) from v$backup_datafile;

MAX(CHECKPOINT_CHANGE#)
-----------------------
607710483792

Stop All Processes in 10g

Stop All Processes

Stop all listeners and other processes running in the Oracle home directory, where you need to install the patch set.

Note: If you shut down ASM instances, you must first shut down all database instances that use ASM, even if these databases run from different Oracle homes.

• If you are upgrading a single instance installation, shut down the following Oracle Database 10g processes before installing the patch set:

  
  

  Note: You must perform these steps in the order listed.

  
  

  1. Shut down all processes in the Oracle home that might be accessing a database, for example Oracle Enterprise Manager Database Control or iSQL*Plus.

     
     

     Note: Before you shutdown all processes that are monitored by Enterprise Manager Grid Control, set a blackout in Grid Control for the processes that you intend to shut down. This is necessary so that the availability records for these processes indicate that the shutdown was planned downtime rather than an unplanned system outage.

     
     

  2. Shut down all database instances running in the Oracle home directory, where you need to install the patch set.

  3. Shut down the ASM instance, if the ASM instance is running in the Oracle home directory, where you need to install the patch set.

  4. Shut down all listeners running in the Oracle home directory, where you need to install the patch set.

  5. Shut down the Oracle Cluster Synchronization Services (CSS) daemon as the root user:

     # oracle_home/bin/crsctl stop crs
     
     

• If you are upgrading an Oracle Clusterware installation, shut down the following Oracle Database 10g processes before installing the patch set:

  
  

  Note: You must perform these steps in the order listed.

  
  

  1. Shut down all processes in the Oracle home on each node that might be accessing a database, for example Oracle Enterprise Manager Database Control.

     
     

     Note: Before you shutdown all processes that are monitored by Enterprise Manager Grid Control, set a blackout in Grid Control for the processes that you intend to shut down. This is necessary so that the availability records for these processes indicate that the shutdown was planned downtime rather than an unplanned system outage.

     
     

  2. Shut down all RAC instances on all nodes. To shut down all RAC instances for a database, enter the following command where db_name is the name of the database:

     $ oracle_home/bin/srvctl stop database -d db_name -i inst_name
     
     

  3. Shut down all ASM instances on all nodes. To shut down an ASM instance, enter the following command where node is the name of the node where the ASM instance is running:

     $ oracle_home/bin/srvctl stop asm -n node
     
     

  4. Stop all node applications on all nodes. To stop node applications running on a node, enter the following command where node is the name of the node where the applications are running:

     $ oracle_home/bin/srvctl stop nodeapps -n node
     
     

  5. Shut down the Oracle Clusterware process by entering the following command on all nodes as the root user:

     # crs_home/bin/crsctl stop crs
     
     

     
     

     Note: In these examples, crs_home is the Oracle Clusterware home directory where the Oracle Clusterware 10g installation that you want to upgrade is installed.

     
     
     
     

     Note: If the Oracle Clusterware installation is not on a shared Oracle home you can upgrade the Oracle Clusterware installation one node at a time. To do this, perform the preceding steps only on the first node that you are upgrading, then follow the instructions on the installer screen.

     
     

• If you are upgrading a RAC installation, shut down the following Oracle Database 10g processes before installing the patch set:

  
  

  Note: You must perform these steps in the order listed.

  
  

  1. Shut down all processes in the Oracle home on each node that might be accessing a database, for example Oracle Enterprise Manager Database Control.

     
     

     Note: Before you shutdown all processes that are monitored by Enterprise Manager Grid Control, set a blackout in Grid Control for the processes that you intend to shut down. This is necessary so that the availability records for these processes indicate that the shutdown was planned downtime rather than an unplanned system outage.

     
     

  2. Shut down all RAC instances on the nodes, which run from the Oracle home on which you are going to apply the patch set. To shut down all RAC instances for a database, enter the following command where db_name is the name of the database:

     $ oracle_home/bin/srvctl stop database -d db_name
     
     

  3. Shut down all ASM instances on all nodes. To shut down an ASM instance, enter the following command where node is the name of the node where the ASM instance is running:

     $ oracle_home/bin/srvctl stop asm -n node
     
     

  4. Stop any listeners that are running from the Oracle home that you are patching on all nodes. To stop the listener running on a node, enter the following command, where node is the name of the node where the listener is running:

     $ oracle_home/bin/srvctl stop listener -n node [-l listenername]
     
     

All About SCN

select start_scnw, start_scnb from v$transaction;

alter system checkpoint;

select CONTROLFILE_CHANGE#  from v$database;

select dbms_flashback.get_system_change_number flashback_scn from v$database;

SELECT dbms_flashback.get_system_change_number FROM dual;

what is my session doing

-- sessions in non-RAC
set linesize 132
Prompt "Oracle Sessions"
column osuser format A08 Head "OS User"
column user format A08 Head "User"
column module format A15 Head "Module"
column action format A25 Head "Action"
column logon_time format A15 Head "Logon Time"

select sid,
s.serial#,
spid,
substr(osuser,1,12) osuser,
substr(s.username,1,15) "user",
status,
decode(p.background,'1','Y','N') background,
decode(s.lockwait,null,'N','Y') lockwait,
decode(p.latchwait,null,'N','Y') latchwait,
substr(to_char(logon_time,'dd-mon-yy hh24:mi'),1,18) logon_time,
substr(module,1,45) module,
action
from v$session s,
v$process p
where s.username not in (' ','SYS','PATROL')
and p.addr=s.paddr
order by status desc, logon_time
/

set lines 80

-- sessions in RAC
set linesize 132
Prompt "RAC Oracle Sessions"
column inst_id format 9 Head "Inst|Id."
column osuser format A08 Head "OS User"
column user format A08 Head "User"
column module format A15 Head "Module"
column action format A25 Head "Action"
column logon_time format A15 Head "Logon Time"
select inst_id, sid, serial#,
substr(osuser,1,12) osuser, substr(username,1,15) "user",
status,
substr(to_char(logon_time,'dd-mon-yy hh24:mi'),1,18) logon_time,
substr(module,1,45) module, action
from gv$session
where username not in (' ','SYS','PATROL')
order by status desc;

-- 'Session Reponse in terms of |Waiting|IO Operation|CPU Operation'
ttitle 'Session Reponse in terms of |Waiting|IO Operation|CPU Operation'

Column event format a30
Column sid format 9999
Column session_cpu heading "CPU|used"
Column physical_reads heading "physical|reads"
Column consistent_gets heading "logical|reads"
Column seconds_in_wait heading "seconds|waiting"

select a.sid,
a.value session_cpu,
c.physical_reads,
c.consistent_gets,
d.event,
d.seconds_in_wait
from v$sesstat a,
v$statname b,
v$sess_io c,
v$session_wait d
where a.sid like '&Sid'
and b.name = 'CPU used by this session'
and a.statistic# = b.statistic#
and a.sid=c.sid
and a.sid=d.sid;


-- WHAT IS SESSION doing, we have pid

SELECT /*+ ordered */ p.spid, s.sid, s.serial#, s.username,
TO_CHAR(s.logon_time, 'mm-dd-yyyy hh24:mi') logon_time,
s.last_call_et, st.value, s.sql_hash_value, s.sql_address, sq.sql_text
FROM v$statname sn, v$sesstat st, v$process p, v$session s, v$sql sq
WHERE s.paddr=p.addr
AND s.sql_hash_value = sq.hash_value and s.sql_Address = sq.address
AND s.sid = st.sid
AND st.STATISTIC# = sn.statistic#
AND sn.NAME = 'CPU used by this session'
AND p.spid = &osPID -- parameter to restrict for a specific PID
AND s.status = 'ACTIVE'
ORDER BY st.value desc;

-- v$session_wait, current waits on the system
SELECT event,
sum(decode(wait_time,0,1,0)) "Curr",
sum(decode(wait_time,0,0,1)) "Prev",
count(*)"Total"
FROM v$session_wait
GROUP BY event
ORDER BY count(*);

-- v$session_event, cummulative session waits
SELECT event, total_waits waits, total_timeouts timeouts,
time_waited total_time, average_wait avg
FROM v$session_event
WHERE sid = &sid
ORDER BY time_waited DESC;

-- V$SESSTAT, session-level summary of resource usage since session startup
SELECT ses.sid
, DECODE(ses.action,NULL,'online','batch') "User"
, MAX(DECODE(sta.statistic#,9,sta.value,0))
/greatest(3600*24*(sysdate-ses.logon_time),1) "Log IO/s"
, MAX(DECODE(sta.statistic#,40,sta.value,0))
/greatest(3600*24*(sysdate-ses.logon_time),1) "Phy IO/s"
, 60*24*(sysdate-ses.logon_time) "Minutes"
FROM V$SESSION ses
, V$SESSTAT sta
WHERE ses.status = 'ACTIVE'
AND sta.sid = ses.sid
AND sta.statistic# IN (9,40)
GROUP BY ses.sid, ses.action, ses.logon_time
ORDER BY
SUM( DECODE(sta.statistic#,40,100*sta.value,sta.value) )
/ greatest(3600*24*(sysdate-ses.logon_time),1) DESC;

-- V$SYSTEM_EVENT , Finding the Total Waits on the System

SELECT event, total_waits waits, total_timeouts timeouts,
time_waited total_time, average_wait avg
FROM V$SYSTEM_EVENT
ORDER BY 4 DESC;

listerner log file - switch to new log file

#!/bin/ksh

. ~/.profile
ORACLE_SID=demo;ORAENV_ASK=NO;. oraenv;ORAENV_ASK=YES

listener_log_extension=`date '+%m%d%y'`

echo "set current_listener LISTENER" >/tmp/listener
echo "set log_file listener.$listener_log_extension">>/tmp/listener
echo "quit">>/tmp/listener


lsnrctl < /tmp/listener

Archive log

init.ora parameters
set log_checkpoints_to_alert=TRUE
remove log_checkpoint_timeout, log_checkpoint_interval

general
set numformat 999999999999
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

turn on archive log mode
steps

A. configure
1) alter system set db_recovery_file_dest='+ORAFLASH001' scope=both sid='*';
2) alter system set db_recovery_file_dest_size = 20G scope=both sid='*';

B. archivelog mode
1) shutdown database
2) startup database in mount mode
3) alter database archivelog; # as sysdba
4) archive log list # as sysdba
sys > ARCHIVE LOG LIST
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 416
Next log sequence to archive 418
Current log sequence 418
5) open database


FIX archive log destination to ORAFLASH001 in 10g using ASM, if set incorrectly
steps
# of course db_recovery_file_dest should be set to +ORAFLASH001
1) alter system set log_archive_dest_10='LOCATION=use_db_recovery_file_dest' scope=both sid='*';
2) alter system set log_archive_dest_1='' scope=both sid='*'; # to null out this parameter
3) alter system switch logfile; # to test, check alert log


others tests
alter system archive log current;
alter system archive log all;
alter system switch logfile;

increase archive log destination size. but 1st find out, How much archived
steps
1) select count(*),sum(blocks*block_size) from v$archived_log where backup_count=0 and deleted='NO';
2) increase db_recovery_file_dest_size
ALTER SYSTEM SET db_recovery_file_dest_size = 30G SCOPE=BOTH SID='*';
3) SHOW PARAMETER RECOVERY

Check space utilization
steps
1. col name format a20
select * from v$recovery_file_dest;
NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
--------------- ----------- ---------- ----------------- ---------------
+ORAFLASH001 2147483648 1271777792 596992 33

2. select * from v$flash_recovery_area_usage;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE .71 0 1
ONLINELOG 0 0 0
ARCHIVELOG 16.81 .03 24
BACKUPPIECE 41.63 0 8
IMAGECOPY 0 0 0
FLASHBACKLOG 0 0 0

6 rows selected.

list archived files not backed-up
select NAME, ARCHIVED , deleted, status
from v$archived_log
where status='A';

others queries
SELECT SEQUENCE#, FIRST_TIME, FIRST_CHANGE#, NEXT_TIME, NEXT_CHANGE#
FROM v$archived_log;

SELECT sequence#, thread#, first_change#, next_change#
FROM v$archived_log;

SELECT MAX(sequence#)
FROM v$archived_log;

find archive logs to be restored

select completion_time,name,sequence#,first_change#,first_time,next_change#,next_time, archived
from v$archived_log;

restore archive log using seqeunce
run {
allocate channel demo_t1 type 'SBT_TAPE'
parms='ENV=(NB_ORA_CLASS=RMAN_rs204_Tier1)';
restore archivelog from sequence 35347 until sequence 35359;
}

run {
allocate channel demo_t1 type 'SBT_TAPE'
parms='ENV=(NB_ORA_CLASS=RMAN_rs204_Tier2)';
set archivelog destination to '/oraarc01/oracle/arch/demo/' ;
restore archivelog from logseq=70241 until logseq=70250 ;
}

run {
allocate channel demo_t3 type 'SBT_TAPE'
parms='ENV=(NB_ORA_CLASS=RMAN_rs204_Tier2)';
set archivelog destination to '/opt/splexmv/tmparclog/' ;
restore archivelog sequence between 70276 and 70376 ;
}
restore archive log using time
run {
allocate channel icmt_t1 type 'SBT_TAPE'
parms='ENV=(NB_ORA_CLASS=RMAN_rs204_Tier2,NB_ORA_CLIENT=rs204-bkup)';
restore archivelog
from time = "to_date('Apr 5 2005 19:20:00','Mon DD YYYY HH24:MI:SS')"
until time = "to_date('Apr 5 2005 20:00:00','Mon DD YYYY HH24:MI:SS')";
}

backup archive log
run {
allocate channel ch1 type 'SBT_TAPE'
parms='ENV=(NB_ORA_CLASS=RMAN_MTBackup_Tier3)';
set command id to 'rman backup';
backup
maxsetsize = 1G
diskratio = 0
skip inaccessible
tag = 'Archive Logs'
(archivelog all delete input format'SBT_TAPE/RMAN_%d.%t.arch.%c.%s.%p');
}

backup database including archivelog
backup setsize = 62914560
(database)
plus archivelog
skip inaccessible;


backup
full
filesperset = 1024
diskratio = 0
tag = 'Full'
(database format 'SBT_TAPE/RMAN_%d.%t.full.%c.%s.%p')
plus archivelog
skip inaccessible
delete input
format 'SBT_TAPE/RMAN_%d.%t.arch.%c.%s.%p';

Commands

Alter system archive log [start|stop|all|...]

alter system switch logfile;

alter system archive log all;
alter system archive log next;
alter system archive log sequence 104;
alter system archive log current;
alter system archive log stop;
alter system archive log current noswitch;

alter system checkpoint;

alter system flush buffer_cache;
alter system flush buffer_pool;

alter system quiesce restricted;
alter system suspend|resume;
alter system switch logfile;

More Info

export and import using pipe

# export and compress using pipe

export EXPDATE=`date "+%m%d_%H%M"`
export DUMPFILE=demo_user_${EXPDATE}.dmp
export EXPLOGFILE=demo_user_${EXPDATE}.log


/usr/sbin/mknod ${DUMPFILE} p
/usr/bin/gzip < ${DUMPFILE} > ${DUMPFILE}.gz &
sleep 30

exp scott/tiger file=${DUMPFILE} log=${EXPLOGFILE} \
owner=SYSADM consistent=y buffer=10000000 direct=Y statistics=none

tail -5 demo_user_${EXPDATE}.log | mailx -s "demo export" me@google.com

# import from compresed file using pipe

export DUMPFILE=myexp.dmp
export IMPLOGFILE=imp_user_${EXPDATE}.log

/usr/sbin/mknod ${DUMPFILE} p

imp scott/tiger file=${DUMPFILE} log=${IMPLOGFILE} \
fromuser=SYSADM touser=SYSADM indexes=y ignore=y buffer=10000000 commit=y &

/usr/bin/gzip -d < ${DUMPFILE}.gz > ${DUMPFILE}
sleep 300

tail -5 demo_imp_user_${EXPDATE}.log | mailx -s "demo export" me@google.com

export full, zip it and remove files other than 5 days - all in one

#!/bin/ksh
export ORACLE_SID=demo
export ORACLE_HOME=/ora01/oracle/product/db_1020
export PATH=$ORACLE_HOME/bin:$PATH
export EXPDATE=`date "+%m%d_%H%M"`

$ORACLE_HOME/bin/exp \'/ as sysdba\' file=demo_full_${EXPDATE}.dmp full=y statistics=none log=demo_full_${EXPDATE}.log

/bin/gzip demo_full_${EXPDATE}.dmp

find -name exp_demo\* -mtime +5 -exec rm {} \;

tail -5 demo_full_${EXPDATE}.log | mailx -s "demo export" me@google.com

SYSTEM related statistics in 10gr2

3 More SYSTEM related statistics in 10gr2 system, dictionary and fixed_objects

# Gather system statistics
execute dbms_stats.gather_system_stats('Start');

-- some time delay while the database is under a typical workload

execute dbms_stats.gather_system_stats('Stop');

-- OR

exec dbms_stats.gather_system_stats('interval', interval=>N);
where N is the number of minutes stats will be gathered

# collect OLTP and BATCH related system stats and import it when desired
exec dbms_stats.create_stat_table('SYS','CSX_STAT');

BEGIN
DBMS_STATS.GATHER_SYSTEM_STATS(
gathering_mode => 'interval',
interval => 720,
stattab => 'CSX_STAT',
statid => 'OLTP');
END;
/

BEGIN
DBMS_STATS.GATHER_SYSTEM_STATS(
gathering_mode => 'interval',
interval => 720,
stattab => 'CSX_STAT',
statid => 'OLAP');
END;
/

During the day, the following jobs import the OLTP statistics for the daytime run:
VARIABLE jobno number;
BEGIN
DBMS_JOB.SUBMIT(:jobno,
'DBMS_STATS.IMPORT_SYSTEM_STATS(''CSX_STAT'',''OLTP'');'
SYSDATE, 'SYSDATE + 1');
COMMIT;
END;
/

During the night, the following jobs import the OLAP statistics for the nighttime run:

BEGIN
DBMS_JOB.SUBMIT(:jobno,
'DBMS_STATS.IMPORT_SYSTEM_STATS(''CSX_STAT'',''OLAP'');'
SYSDATE + 0.5, 'SYSDATE + 1');
COMMIT;
END;
/


# Gather statistics on dictionay
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
-- or
begin
dbms_stats.gather_dictionary_stats(
comp_id => null,
estimate_percent => 15,
granularity => 'DEFAULT',
cascade => TRUE);

end;
/

# gather fixed object(synamic performance tables eg. x$) stats
exec dbms_stats.gather_fixed_objects_stats;
-- or
SQL> conn / as sysdba
SQL> exec DBMS_STATS.CREATE_STAT_TABLE('sys','csxfixedtablestat');
SQL> EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS ('csxfixedtablestat');



# Flush statistics from memory
DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO

# view
USER_TAB_MODIFICATIONS

# system statistics views
select pname, pval1
from sys.aux_stats$
where sname = 'SYSSTATS_MAIN';

gather statistics, dbms_stats

Manage statistics using dbms_stats 
 
create stat table
begin
dbms_stats.create_stat_table(
ownname => 'SYSADM',
stattab => 'mystattable',
tblspace=> 'tools');
end;

drop stat table
begin
dbms_stats.drop_stat_table(
ownname => 'SYSADM',
stattab => 'mystattable');
end;

gather database Stat
begin
dbms_stats.gather_database_stats(
method_opt=> 'FOR ALL INDEXED COLUMNS SIZE 1',
estimate_percent => 15,
cascade=>TRUE);
end;

delete database stat
EXEC DBMS_STATS.delete_database_stats;

gather schema Stat
begin
dbms_stats.gather_schema_stats(
ownname => 'SYSADM',
method_opt=> 'FOR ALL INDEXED COLUMNS SIZE 1',
estimate_percent => 15,
cascade=>TRUE);
end;

delete schema stat
EXEC DBMS_STATS.delete_schema_stats('SCOTT');

export table stat
dbms_stats.export_table_stats(
ownname => 'SYSADM',
tabname =>'my_table',
stattab =>'mystattable',
statid =>'bkup',
cascade =>true);
end;

import table stat
begin
dbms_stats.import_table_stats(
ownname => 'SYSADM',
tabname =>'my_table',
stattab =>'mystattable',
statid =>'bkup',
cascade =>true);
end;

delete table stat
begin
dbms_stats.delete_table_stats(
ownname => 'SYSADM',
tabname =>'my_table');
end;

gather table stat
begin
dbms_stats.gather_table_stats(
ownname => 'SYSADM',
tabname => 'my_table',
method_opt=>'FOR ALL INDEXED COLUMNS SIZE 1',
estimate_percent => 15,
cascade => TRUE );
end;

gather index stat
EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMPLOYEES_PK', estimate_percent => 15);

delete index stat
EXEC DBMS_STATS.delete_index_stats('SCOTT', 'EMPLOYEES_PK');

delete column stat
exec dbms_stats.delete_column_stats(USER, 'PERSON', 'PERSON_ID');

check stats
select table_name, num_rows from dba_tables
where table_name in ('PS_JOB','PS_SCRTY_TBL_DEPT','PS_NAMES','PS_PERSON') order by table_name ;

select table_name,index_name, num_rows from dba_indexes
where table_name in ('PS_JOB','PS_SCRTY_TBL_DEPT','PS_NAMES','PS_PERSON') order by table_name ;

select TABLE_NAME, COLUMN_NAME, NUM_BUCKETS from dba_tab_columns
where table_name in ('PS_JOB','PS_SCRTY_TBL_DEPT','PS_NAMES','PS_PERSON') order by table_name, column_name;

select count(*) from PS_SCRTY_TBL_DEPT ;
select count(*) from PS_JOB ;
select count(*) from PS_NAMES ;
select count(*) from PS_PERSON ;


Steps

1. Export current stats
2. Gather or Import or Set Stats
3. trace
4. tkprof

More info



Hope this helps. Regards Rupam

Substitue

# substitute word
perl -e 's/gopher/World Wide Web/gi' -p -i.bak *.html

# Renaming within the name:
ls -1 *old* | awk '{print "mv "$1" "$1}' | sed s/old/new/2 | sh
(although in some cases it will fail, as in file_old_and_old)

OR

for i in *.sh
> do
> y=`echo $i |sed -e 's/hr/hd/'`
> mv $i $y
> done

# Execute as shell script and redirect log file
echo “restore.sh >restore.log 2>&1”|at now

# convert to upper case
cat fileName.txt | tr '[a-z]' '[A-Z]' > newFileName.txt
perl -p -e 'tr/t/p/;' -p -i fileName.txt
perl -p -e 'tr/a-z/A-Z/;' -p -i fileName.txt

-p option tells it to read and process each line from standard input and print the results to standard output
-e option lets you specify a Perl expression (a program, actually) on the command line.

config of a RAC database

lion336@PSPX1$ srvctl config database -d PSPX -a

lion336 PSPX1 /ora01/oracle/product/db_1020
lion337 PSPX2 /ora01/oracle/product/db_1020
lion338 PSPX3 /ora01/oracle/product/db_1020
DB_NAME: PSPX
ORACLE_HOME: /ora01/oracle/product/db_1020
SPFILE: +ORADATA001/PSPX/spfilePSPX.ora
DOMAIN: null
DB_ROLE: null
START_OPTIONS: null
POLICY: AUTOMATIC
ENABLE FLAG: DB ENABLED
lion336@PSPX1:/ora01/oracle/admin/network $