DBA Arena

Silent install of Oracle Software, Database and Listener.

Go to the software location
Copy sample response file from /database/response/db_install.rsp
[oracle@dbaarena ~]$ cp -rfv /u01/11gR2Setup/database/response/ /u01/home

Modify the db_install.rsp file.
oracle.install.option=INSTALL_DB_SWONLY
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/oracle/OraInventory
ORACLE_HOME=/u01/home/oracle/product/11.2.0/dbhome_1
ORACLE_BASE=/u01/home
oracle.install.db.InstallEdition=EE
oracle.install.db.DBA_GROUP=dba
oracle.install.db.OPER_GROUP=dba
DECLINE_SECURITY_UPDATES=true
Read more...

Kill all processes of FNDLIBR.

ps -ef |grep FNDLIBR | grep -v grep | awk '{print $2}' | xargs kill -9


Similarly, we can kill other processes as well by replacing 'FNDLIBR' with other process names.

Kill all processes of java.

ps -ef |grep java | grep -v grep | awk '{print $2}' | xargs kill -9

Read more...

Finding the root cause of sudden increase in the archive log generation on the database.

Find tables that have maximum number of changes during the period there was huge archive generation.

SELECT to_char(begin_interval_time,'YYYY_MM_DD HH24:MI') snap_time,
dhsso.object_name,
SUM(db_block_changes_delta)
FROM dba_hist_seg_stat dhss,
dba_hist_seg_stat_obj dhsso,
dba_hist_snapshot dhs
WHERE dhs.snap_id = dhss.snap_id
AND dhs.instance_number = dhss.instance_number
AND dhss.obj# = dhsso.obj#
AND dhss.dataobj# = dhsso.dataobj#
AND begin_interval_time BETWEEN to_date('2015_05_23 12','YYYY_MM_DD HH24')
AND to_date('2015_05_25 12','YYYY_MM_DD HH24')
GROUP BY to_char(begin_interval_time,'YYYY_MM_DD HH24:MI'),
dhsso.object_name order by SUM(db_block_changes_delta) desc;

Read more...

Purpose

Use the CREATE RESTORE POINT statement to create a restore point, which is a name associated with an SCN of the database corresponding to the time of the creation of the restore point. A restore point can be used to flash a table or the database back to the time of creation of the restore point without the need to determine the SCN or timestamp.

There are two types of restore point:
• Guaranteed restore points: A guaranteed restore point enables you to flash the database back to the restore point regardless of the DB_FLASHBACK_RETENTION_TARGET initialization parameter setting.
Guaranteed restore points must be dropped explicitly by the user using the DROP RESTORE POINT statement. They do not age out.
• Normal restore points: A normal restore point enables you to flash the database back to a restore point within the time period determined by the DB_FLASHBACK_RETENTION_TARGET initialization parameter. You can explicitly drop a normal restore point using the DROP RESTORE POINT statement.

Read more...

This script will report on all backups – full, incremental and archivelog backups.

col STATUS format a9
col hrs format 999.99
select session_key, input_type, status,to_char(start_time,'mm/dd/yy hh24:mi') start_time,to_char(end_time,'mm/dd/yy hh24:mi') end_time,elapsed_seconds/3600 hrs from V$rman_backup_job_details order by session_key;

SESSION_KEY  INPUT_TYPE    STATUS              START_TIME           END_TIME              HRS
--------------------------------------------------------------------------------------------------------------------------------
378                   DB FULL          RUNNING            02/25/15 08:10      02/25/15 10:15     2.08416
378                   DB FULL          COMPLETED       02/25/15 08:10      02/25/15 10:31     2.35944
386                   BACKUPSET    RUNNING           02/25/15 10:31      02/25/15 11:03     0.53305
386                   BACKUPSET    COMPLETED       02/25/15 10:31      02/25/15 11:20     0.81388
========================================================================================

Monitor RMAN Progress

Read more...