Rman backup slow on Oracle 10g Standard version

Rman backup takes more time to complete on an Oracle 10g Standard version. There is a limitation on the number of channels that can be used on the Standard version of Oracle. we can use only One channel for RMAN backup.
This will increase the backup time as we cannot exploit the multiple channels to backup the database which is possible in Oracle Enterprise version.
So if the backup time doesnot matter and you dont want to use the advance features than this is a good solution to reduce the license cost( at least in the development environment)

Database Links Overview

Database Links: An Overview
—————————
1) What are database links?

Database links are connections between two databases on the
same or different machines.

2) What are database links used for?

To query data on a different database (distributed query)
To do DML on data on a different database (distributed transaction)
To either query or do DML on a non Oracle database (transparent
gateways and generic connectivity with 10g and earlier,database gateways from 11g)

3) The Anatomy of a Database Link

A database link has 4 main parts:

Owner
Link name
Username/password
Host (Service Name)

3.1) The database link OWNER

Like most objects in an Oracle database … database links have an
owner (the user who creates the link) or may be owned by PUBLIC.

If owned by PUBLIC … the database link may be accessed by all
users on the database.

Syntax: CREATE public DATABASE LINK …
CREATE DATABASE LINK …

3.2) The database link NAME

The database link name can be any valid Oracle name.

If global_names = TRUE in the init.ora then the database link name
must be the global name of the remote database
(SELECT GLOBAL_NAME FROM GLOBAL_NAME).

Syntax: CREATE DATABASE LINK kbcook …

3.3) The database link USERNAME

The database link username/password is an OPTIONAL clause. If it
is not specified then the current username/password for the local
database are used to connect to the remote database.

If a username is specified then all connections through that link are
connected as the user specified.

Syntax: CREATE DATABASE LINK kbcook
CONNECT TO scott IDENTIFIED BY tiger …

3.4) The database link HOST

The Host is the entry in the TNSNAMES.ORA for the database that is
being linked to.

Syntax: CREATE DATABASE LINK kbcook
CONNECT TO scott IDENTIFIED BY tiger
USING ‘rtcsol1_v805.us.oracle.com’

4) How do you locate the TNSNAMES.ORA?

If the $TNS_ADMIN environment variable is defined then look in that
directory for the TNSNAMES.ORA.

If $TNS_ADMIN is not set then look in the $ORACLE_HOME/network/admin
directory. Alternately the tnsnames may exist in */etc or
/var/opt/oracle as well.

5) How do I locate the HOST in the TNSNAMES.ORA?

The TNSNAMES.ORA will look like:

RTCSOL1_V805.US.ORACLE.COM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rtcsol1)(PORT = 1521))
(CONNECT_DATA = (SID = V805))
)

The host name is everything before the =

Note: You can specify the full description as the host

Syntax: CREATE DATABASE LINK kbcook
CONNECT TO scott IDENTIFIED BY tiger
using ‘(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)
(HOST = rtcsol1)(PORT = 1521))
(CONNECT_DATA = (SID = V805))’

6) How a database link works

Step 1: SELECT * FROM TABLE@DBLINK

Step 2: The database will resolve DBLINK to a host name … it will
use the TNSNAMES.ORA unless fully described

Step 3: Naming resolution (DNS, NIS etc) will resolve the HOST to a
TCP/IP address

Step 4: A connection will be made to a listener at the TCP/IP address

Step 5: The listener for the PORT will resolve the SID and finish
the connection to the database (uses the listener.ora to
determine if it is listening for connections to that sid)

7) How do you locate the LISTENER.ORA?

If the $TNS_ADMIN environment variable is defined then look in that
directory for the LISTENER.ORA.

If $TNS_ADMIN is not set then look in the $ORACLE_HOME/network/admin
directory. Alternately the listener.ora may exist in */etc or
/var/opt/oracle as well.

8) How do I locate the HOST in the LISTENER.ORA?

The LISTENER.ora will look like:

LISTENER=
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rtcsol1)(PORT = 1521))
)
)
(DESCRIPTION =
(PROTOCOL_STACK =
(PRESENTATION = GIOP)
(SESSION = RAW)
)
(ADDRESS = (PROTOCOL = TCP)(HOST = rtcsol1)(PORT = 14000))
)
)

SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=V805)
(ORACLE_HOME=/u04/app/oracle/product/8.0.5)
)
)
)

10) Additional Clauses

The SHARED and AUTHENTICATED BY clauses are used to establish a multi-threaded database connection and both are required to do so.

11) How to check if a Database Link is private / public or shared/not shared:
If the value of the OWNER column of the view DBA_DB_LINKS is PUBLIC,
it is a public database link. All other values indicate it is a private
database link.

SQL> select OWNER from dba_db_links where DB_LINK = ‘MYLINK’;

To check for shared database links, query sys.link$
SQL> select NAME from sys.link$ where AUTHUSR is not null;

Disable Automatic Statistics Collection in 10G

Disable Automatic Statistics Collection in 10G.

The Automatic Statistics collection feature is enabled by default in 10G.

You can verify this by checking the following :

SQL> SELECT STATE FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = ‘GATHER_STATS_JOB’;

STATE
—————
SCHEDULED
Solution

1)To Disable the automatic statistics collection in 10G , you can execute the following procedure as sysdba:

EXEC DBMS_SCHEDULER.DISABLE(‘GATHER_STATS_JOB’);

2) To check whether the job is disabled, run the following QUERY:

SQL> SELECT STATE FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = ‘GATHER_STATS_JOB’;

STATE
—————
DISABLED

New Background Process Information in 10g

BACKGROUND PROCESS INFORMATION
=================================

MMAN

SGA Background Process
The Automatic Shared Memory Management feature uses a new background process named Memory Manager (MMAN). MMAN serves as the SGA Memory Broker and coordinates the sizing of the memory components. The SGA Memory Broker keeps track of the sizes of the components and pending resize operations

RVWR
======
Flashback database
—————————–
– This a new feature introduced in 10g.
– Flashbacking a database means going back to a previous database state.
– The Flashback Database feature provides a way to quickly revert an entire Oracle database to the state it was in at a past point in time.
– This is different from traditional point in time recovery.
– A new background process Recovery Writer ( RVWR) introduced which is responsible for writing flashback logs which stores pre-image(s) of data blocks
– This feature is not applicable for recovering the database in case of media
failure.
– The time required for flashbacking a database to a specific time in past is DIRECTLY PROPORTIONAL to the number of changes made and not on the size of the database.

Jnnn
=====
These are job queue processes which are spawned as needed by CJQ0 to complete scheduled jobs. This is not a new process.

CTWR
=====

This is a new process Change Tracking Writer (CTWR) which works with the new block changed tracking features in 10g for fast RMAN incremental backups.

MMNL
=====

The Memory Monitor Light (MMNL) process is a new process in 10g which works with the Automatic Workload Repository new features (AWR) to write out full statistics buffers to disk as needed.

MMON
======

The Manageability Monitor (MMON) process was introduced in 10g and is associated with the Automatic Workload Repository new features used for automatic problem detection and self-tuning. MMON writes out the required statistics for AWR on a scheduled basis.

M000
======
MMON background slave (m000) processes.

CJQn
=====
This is the Job Queue monitoring process which is initiated with the job_queue_processes parameter. This is not new.

RBAL
====
This is the ASM related process that performs rebalancing of disk resources controlled by ASM.

ARBx
====
These processes are managed by the RBAL process and are used to do the actual rebalancing of ASM
controlled disk resources. The number of ARBx processes invoked is directly influenced by the asm_power_limit parameter.

ASMB
=====
The ASMB process is used to provide information to and from the Cluster Synchronization Services used by ASM to manage the disk resources. It is also used to update statistics and provide a heartbeat mechanism.

Default parameter settings when gathering statistics on 9i and 10g

This document outlines how to determine the default parameter settings when gathering statistics on Table on 9i and 10g.

On 9i, Gather procedures have a number of hard coded default values
On 10g, All procedures that gather optimizer statistics no longer have hardcoded default values.

The defaults can be viewed using :

select dbms_stats.get_param(‘cascade’) from dual;
select dbms_stats.get_param(‘degree’) from dual;
select dbms_stats.get_param(‘estimate_percent’) from dual;
select dbms_stats.get_param(‘method_opt’) from dual;
select dbms_stats.get_param(‘no_invalidate’) from dual;
select dbms_stats.get_param(‘granularity’) from dual;

Parameters can be set using DBMS_STATS.SET_PARAM.

EXAMPLES

Get Parameter Example

SQL> select dbms_stats.get_param(‘method_opt’) from dual;

DBMS_STATS.GET_PARAM(‘METHOD_OPT’)
———————————-
FOR ALL COLUMNS SIZE AUTO

Set Parameter Example

SQL> exec dbms_stats.set_param(‘METHOD_OPT’, ‘FOR ALL COLUMNS SIZE 1’)
PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_param(‘method_opt’) from dual;

DBMS_STATS.GET_PARAM(‘METHOD_OPT’)
———————————-
FOR ALL COLUMNS SIZE 1

The default values on 9i are hard coded and are as follows:

DBMS_STATS.GATHER_TABLE_STATS (

ownname VARCHAR2,
tabname VARCHAR2,
partname VARCHAR2 DEFAULT NULL, –> ALL partitions
estimate_percent NUMBER DEFAULT NULL, –> 100% sample
block_sample BOOLEAN DEFAULT FALSE,
method_opt VARCHAR2 DEFAULT ‘FOR ALL COLUMNS SIZE 1’,
degree NUMBER DEFAULT NULL, –> parallel degree 1
granularity VARCHAR2 DEFAULT ‘DEFAULT’, –> level (PARTITION + GLOBAL)
cascade BOOLEAN DEFAULT FALSE, –> does not cascade to indexes by default
no_invalidate BOOLEAN DEFAULT FALSE);

Recommendation Statistics generation for Siebel Database in 10g

Recommendation Statistics generation for Siebel Database in 10g: the optimizer mode is all_rows

1. For all tables that have more than 1 million rows use the following:

EXEC DBMS_STATS.GATHER_TABLE_STATS(
ownname => ‘SIEBEL’,
tabname => ‘THE_TABLE’,
method_opt => ‘FOR ALL INDEXED COLUMNS SIZE 1’,
estimate_percent => 20,
granularity => ‘ALL’,
cascade => TRUE,
degree => 8);

For large tables only take a 20 percent percent sample size, set 8 degrees of parallelism. The method_opt => ‘FOR ALL INDEXED COLUMNS SIZE 1’ tells the db not to generate histograms for the indexed columns of these large tables.

2. For all tables that have more than 15 rows and less than 1 million rows use the following:

EXEC DBMS_STATS.GATHER_TABLE_STATS(
ownname => ‘SIEBEL’,
tabname => ‘THE_TABLE’,
method_opt => ‘FOR ALL INDEXED COLUMNS SIZE 254’,
granularity => ‘ALL’,
cascade => TRUE,
degree => DBMS_STATS.DEFAULT_DEGREE);

For smaller tables let the db determine the estimate_percent and degrees of parallelism. The method_opt => ‘FOR ALL INDEXED COLUMNS SIZE 254’ tells the db to generate histograms for the indexed columns of these tables (in addition to other index stats generating mechanisms).

3. For all tables that have 15 rows or less delete the stats from these tables:

EXEC DBMS_STATS.DELETE_TABLE_STATS(
ownname => ‘SIEBEL’,
tabname => ‘THE_TABLE’,
force => TRUE);

The Oracle db optimizer is known to perform inefficiently if stats are generated on empty and very small tables. Therefore the solution is to delete any stats on these tables.

We generated the stats using the above mechanisms using the SQL below, this resulted in much better performance application wide.

largest segment (table or index) in each Tablespace

column tablespace format a18
column segment format a23
select a.ts tablespace,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.tsb),’9,999,999,999,999′) ” TS BYTES”,
to_char(sum(a.segb)/decode(count(*),1,1,count(*)-1)/decode(sum(a.tsb),0,1,
sum(a.tsb))*100,’990.99′)
” PCT”
from (
select tablespace_name ts,segment_name seg,
sum(bytes) segb,000000000000 tsb
from dba_segments d
where tablespace_name not in (‘SYSTEM’,’RBS’,’TEMP’,’TOOLS’,’TSMSYS’,’DBSNMP’,’OUTLN’,’UNDOTBS’)
group by tablespace_name,segment_name
having sum(bytes) = (
select max(sum(bytes))
from dba_segments c
where c.tablespace_name = d.tablespace_name
group by c.segment_name)
union all
select tablespace_name,’~’,0,sum(bytes)
from dba_segments
where tablespace_name not in (‘SYSTEM’,’RBS’,’TEMP’,’TOOLS’,’TSMSYS’,’DBSNMP’,’OUTLN’,’UNDOTBS’)
group by tablespace_name) a
group by a.ts;
set linesize 80 termout on heading on pagesize 24 timing on feedback 6
set termout on verify on echo on showmode both

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

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