Find the largest segment (table or index) for each owner

column owner format a18 trunc
column segment format a23 trunc
set line 5000
select a.ow owner,min(a.seg) || decode(count(*),1,'(none)’,2,’ ‘,’ +’)
segment,
to_char(sum(a.segb)/decode(count(*),1,1,count(*)-1),’9,999,999,999′)
” SEG BYTES”,
to_char(sum(a.owb),’9,999,999,999,999′) ” OWNER BYTES”,
to_char(sum(a.segb)/decode(count(*),1,1,count(*)-1)/decode(sum(a.owb),0,1,
sum(a.owb))*100,’990.99′)
” PCT”
from (
select owner ow,segment_name seg,
sum(bytes) segb,000000000000 owb
from dba_segments d
where owner not in (‘SYS’,’SYSTEM’,’SCOTT’,’TSMSYS’,’DBSNMP’,’OUTLN’)
group by owner,segment_name
having sum(bytes) = (
select max(sum(bytes))
from dba_segments c
where c.owner = d.owner
group by c.segment_name)
union all
select owner,’~’,0,sum(bytes)
from dba_segments
where owner not in (‘SYS’,’SYSTEM’,’SCOTT’,’TSMSYS’,’DBSNMP’,’OUTLN’)
group by owner) a
group by a.ow;
set linesize 80 termout on heading on pagesize 24 timing on feedback 6
set termout on verify on echo on showmode both

Advertisements

Undo information on database sessions

SET LINESIZE 200

COLUMN username FORMAT A15

SELECT s.username,
s.sid,
s.serial#,
t.used_ublk,
t.used_urec,
rs.segment_name,
r.rssize,
r.status
FROM v$transaction t,
v$session s,
v$rollstat r,
dba_rollback_segs rs
WHERE s.saddr = t.ses_addr
AND t.xidusn = r.usn
AND rs.segment_id = t.xidusn
ORDER BY t.used_ublk DESC;

Autosys Scheduler Cheatsheet

SUB-COMMANDS

insert_job: Saves a brand-new job to the database
update_job: PERMANENTLY changes the definition of a pre-
existing job
override_job: TEMPORARILY changes the definition of a pre-
existing job
delete_job: Deletes a single job from the database
delete_box: Deletes a box as well as all the contents

ATTRIBUTES

job_type: b, c, f (command is default)
machine: Name of machine (or IP) where job is to be
run
command: Command to be executed (.exe, .sh, .bat)
watch_file: File being monitored by file watcher
box_name: Used to nest a job inside a box
std_out_file: Redirects output from a command job to a text
file
std_err_file: Redirects error messages to a text file
condition: Used to structure job dependencies (success,
failure, terminated, done, notrunning, exit code,
and value)
min_run_alarm: Causes job to issue an alarm if it finishes too
quickly
max_run_alarm: Causes a job to issue an alarm if it runs too
long
alarm_if_fail: States whether a job will issue an alarm if it
fails
date_conditions: Toggle which must be set in order for date/time
attributes to be recognized by AutoSys
run_calendar: Specifies the calendar a job will run off of
[cannot be used with days_of_week]
days_of_week: Specifies exact days a job will run [cannot be used with run_calendar]
start_times: Exact time each day a job will run [cannot be
used with start_mins]
start_mins: Minutes after each hour a job will execute
[cannot be used with start_times]
exclude_calendar: Specifies a calendar with days specified upon
which a job will not execute
watch_interval: Steady state for file watchers
watch_file_min_size: Minimum size a file must be before a file watcher
can evaluate to success
box_success: Specifies custom success condition for a box
box_failure: Specifies custom failure condition for a box
max_exit_success: Specifies maximum exit code which will be
evaluated as a success
box_terminator: “If I fail, I kill the box I’m in”
job_terminator: “If the box I’m in fails or gets killed, I kill
myself”
term_run_time: “I kill myself after this many minutes”
chk_files: Resource check that verifies a minimum amount
of file space is available before running job
heartbeat_interval: Specifies frequency in minutes at which job’s
command is expected to issue a “heartbeat”
profile: Specifies a file which contains custom
environment variables to be used by a single job
std_in_file: Specifies a file to be used as input for a job
n_retrys: Specifies how many times a job should attempt to
re-run itself after an application-level failure
timezone: Specifies which timezone a job should use when
running
auto_delete: Specifies how many HOURS after successful
completion a job should delete itself from the
database
auto_hold: Used only with jobs in boxes. When the box goes
to a RUNNING state, the job will automatically
go ON_HOLD
permission: Extend edit or execute permissions to others
run_window: Specifies when a job can start
avg_runtime: *Only accessible through JIL* Specifies how long
a job takes to run, on average

SAP Transation Codes

Following are some of the SAP transaction codes that can be used to view the database details in SAP GUI.
DataBase SAP Transaction Codes

NMON Analyzer: AIX server performance stats

This is a useful application provided by IBM

NMON_Analyser is designed to complement NMON (Nigel’s Monitor) in analysing and reporting performance problems; it produces graphs for virtually all sections of output created using the “spreadsheet output” mode of NMON as well as doing some additional analyses for ESS, EMC and FAStT subsystems. It will also work with files produced by topasout and with other tools that produce data in “NMON” format. It is written in VBA for Excel and will work with the 2002 or 2003 editions.

NMON UserGuide v33

NMON Excel File

Recovering a Standby database from a missing archivelog

Recovering a Standby database from a missing archivelog
A Physical Standby database relies on continuous application of archivelogs from a Primary Database to be in synch with it. In Oracle Database versions prior to 10g in the event of an archivelog gone missing or corrupt you had to rebuild the standby database from scratch.

In 10g you can use an incremental backup and recover the standby using the same to compensate for the missing archivelogs as shown below

In the case below archivelogs with sequence numbers 137 and 138 which are required on the standby are deleted to simulate this problem.

Step 1: On the standby database check the current scn.

STDBY>>select current_scn from v$database;
CURRENT_SCN
———–
4793543

Step 2: On the primary database create the needed incremental backup from the above SCN

$>rman target /
Recovery Manager: Release 10.2.0.2.0 – Production

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: USYD (DBID=768471617)

RMAN> BACKUP DEVICE TYPE DISK INCREMENTAL FROM SCN 4793543 DATABASE
FORMAT ‘C:\temp\bkup_%U’;

Starting backup at 25/AUG/09
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\USYD\SYSTEM01.DBF
input datafile fno=00002 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\USYD\UNDOTBS01.DBF
input datafile fno=00003 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\USYD\SYSAUX01.DBF
input datafile fno=00004 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\USYD\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 25/AUG/09
channel ORA_DISK_1: finished piece 1 at 25/AUG/09
piece handle=C:\TEMP\BKUP_02I0NSNE_1_1 tag=TAG20061025T134102 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 25/AUG/09
channel ORA_DISK_1: finished piece 1 at 25/AUG/09
piece handle=C:\TEMP\BKUP_03I0NSOI_1_1 tag=TAG20061025T134102 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 25/AUG/09

Step 3: Cancel managed recovery at the standby database

STDBY>>recover managed standby database cancel;
Media recovery complete.

Move the backup files to a new folder called INCR_BACKUP so that they are the only files in that folder.

Step 4: Catalog the Incremental Backup Files at the Standby Database

$>rman target /

Recovery Manager: Release 10.2.0.2.0 – Production

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: USYD (DBID=768471617, not open)

RMAN> CATALOG START WITH ‘C:\temp\INCR_BACKUP’;
searching for all files that match the pattern C:\temp\INCR_BACKUP

List of Files Unknown to the Database
=====================================
File Name: C:\TEMP\INCR_BACKUP\BKUP_02I0NSNE_1_1
File Name: C:\TEMP\INCR_BACKUP\BKUP_03I0NSOI_1_1

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files…
cataloging done

List of Cataloged Files
=======================
File Name: C:\TEMP\INCR_BACKUP\BKUP_02I0NSNE_1_1
File Name: C:\TEMP\INCR_BACKUP\BKUP_03I0NSOI_1_1

Step 5: Apply the Incremental Backup to the Standby Database

RMAN> RECOVER DATABASE NOREDO;

Starting recover at 25/AUG/09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=151 devtype=DISK
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: C:\ORACLE\PRODUCT\10.2.0\ORADATA\STDB
Y\SYSTEM01.DBF
destination for restore of datafile 00002: C:\ORACLE\PRODUCT\10.2.0\ORADATA\STDB
Y\UNDOTBS01.DBF
destination for restore of datafile 00003: C:\ORACLE\PRODUCT\10.2.0\ORADATA\STDB
Y\SYSAUX01.DBF
destination for restore of datafile 00004: C:\ORACLE\PRODUCT\10.2.0\ORADATA\STDB
Y\USERS01.DBF
channel ORA_DISK_1: reading from backup piece C:\TEMP\INCR_BACKUP\BKUP_02I0NSNE_
1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\TEMP\INCR_BACKUP\BKUP_02I0NSNE_1_1 tag=TAG20061025T134102
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished recover at 25/AUG/09

Step 6: Put the standby database back to managed recovery mode.

STDBY>> recover managed standby database nodelay disconnect;
Media recovery complete.

From the alert.log you will notice that the standby database is still looking for the old log files

*************************************************
FAL[client]: Failed to request gap sequence
GAP – thread 1 sequence 137-137
DBID 768471617 branch 600609988
**************************************************

This is because the controlfile has not been updated.
Hence the standby controlfile has to be recreated

On the primary

PRIM>>alter database create standby controlfile as ‘C:\temp\standby01.ctl’;
System altered.

Copy the standby control file to the standby site and restart the standby database in managed recovery mode…

Managed Standby Recovery not using Real Time Apply
parallel recovery started with 2 processes
Media Recovery Waiting for thread 1 sequence 139

As you can see from the above output the standby is now looking for the next archivelog.

List objects in the SYSTEM tablespace that doesn’t belong to SYS or SYSTEM

select *
from sys.dba_segments
where owner not in (‘PUBLIC’, ‘SYS’, ‘SYSTEM’)
and tablespace_name = ‘SYSTEM’