Oracle DBA

Estimate / calculate the memory will be consumed during opatch

2012-01-13T01:23:00.000-08:00

Formula:

Memory Consumed = (Number of Oracle Processes + 1) X (Size of Patched)

Example:

memory consumed = [ (select value from v$parameter where name='processes' ; ) + 1 ] X (du -sm )

memory consumed = [ (select value from v$parameter where name='processes' ; ) + 1 ] X (du -sm 9795214)

memory consumed = 600+1 X 0.89MB

memory consumed = 601 X 0.89MB

memory consumed = 534.89 MB

.

TUNING DB BUFFER CACHE

2011-03-17T12:17:00.000-07:00

Point to remember:
##################

. Resizing the BUFFER CACHE memory allocation
. Setting the Dynamic Buffer Cache Advisory Parameter
ON
OFF
READY
. The Lease Recenty Used(LRU) List.
. The Dirty List
. The DB writer Process(DBwn)
. Check the SGA Memory maximum allocation size
. Using the v$parameter view
. Checking the Buffer Cache size
. Checking the Redo log buffer Size.
. Checking the Java Pool Size
. Decreasing the Shared Pool Memory size
. Increasing the Buffer Cache memory size

Commands:
#########

Show parameter DB_buffer

alter system set db_cache_advice=ON/OFF/READY;
alter system set db_cache_size =80M;
alter system set shared_pool_size=60M;
alter system set db_cache_size=80M;

LRU & Dirty Buffer
###################

The buffers in the buffer cache are organized in two lists:

Least Recently Used(LRU)
Dirty List.

Dirty List:

The Dirty buffers are blocks in the buffer cache that have been changed.
The dirty buffers are moved to the dirty list and written to datafiles by the db writer processs(DBwn)
When a free buffer is found, the data block is read from the disk into the free buffer and the buffer is moved higher in the LRU list
The Least Recently Used(LRU) list is the list of blocks that have been used at the least amount of time recently at the SGA memory

If the buffer size is too High/ too Low. That will result in too much I/O or poorly utilized real memory.
To assist you proper configuration, Oracle provide you with the DB_CACHE_ADVICE parameter.
When the parameter is set to ON. Oracle will collect statistics about cache utilization and projects the physical I/O for 20 Cache sizes
Ranging from 10 to 200 percent of the current size.

Using Buffer Cache Advise:

alter system set db_cache_advise=ON/OFF/READY
ON -- Allocates memory and gather statistics.
OFF -- Disables advise statistics gathering.
Ready -- Allocates memrory, but statistics are not gathered.

-- Check at which size blocks are increasing and physical reads are decreasing
-- That Estimated cached size is good for DB_CACHE_BUFFER.

select SIZE_FOR_ESTIMATE "Estimated Cached Size(MB)",
BUFFERS_FOR_ESTIMATE "Buffers",
ESTD_PHYSICAL_READS "Physical Reads"
from v$db_cache_advice
order by 1;

Resizing Buffer Cache:

first check SGA_MAX_SIZE & SGA_TARGET

Now check the maximum size that SGA that can grow on.
Compare the SGA Parameter size with the calculated size from the Shared Pool, Buffer Cache and Redo log sizes along with the java pool.

show parameter sga_max_size

select sum(value) as "SGA Size"
from V$parameter
Where name in ('Shared_pool_size','db_cache_size','log_buffer','java_pool-size');

If the size is very close then decrease one size in order to increase the other.
Check the shared_pool and buffer_cache sizes individually.

To increase of decrease DB_cache

alter system set DB_cache_size=100M;
alter system set shared_pool_size=200m;

####################################################################################
More of Resizing and measuring the Buffer Cache Hit Ratio.
####################################################################################

Points to remember to resolve performance issues.

. Measuring the Buffer Cache Hit Ratio.
. Creating a Cache Table
. Calculating the Hit Ratio for Multiple Pools
. Displaying the Hit Ratio for the KEEP buffer Pool
. Caching the Oralce Objects in the Buffer Pools
. Diagnosing the FREELIST Contentions
. Adding a FREELIST to a table
. Using the DEFAULT pool
. Using the KEEP Pool
. Using the RECYCLE pOOL
. Using the v$sysstat view
. Using the V$buffer_pool View
. Using the V$BUFFER_POOL_STATISTICS dictionary view
. Using the DBA_SEGMENTS view
. Using the V$SESSION_WAIT view
. Dropping a table

Buffere Cache Hit Ratio:

Calculate the Buffer Cache Hit Ratio from the V$SYSSTAT
The view V$SYSSTAT contains the oracle system usages such as session logical reads,
physical reads direct, etc.

select 1-((p.value - l.value - d.value) / s.value) as " Buffer Cache Hit Ratio"
from v$sysstat s, v$sysstat l, v$sysstat d, v$sysstat p
where s.name='session logical reads'
and d.name='physical reads direct'
and l.name='physical reads direct(LOB)'
and p.name='physical reads';

select 1 - ((p.value - l.value - d.value) / s.value) as " Buffer Cache Hit Ratio"
from v$sysstat s, v$sysstat l, v$sysstat d, v$sysstat p
where s.name='session logical reads'
and d.name='physical reads direct'
and l.name='physical reads direct(LOB)'
and p.name='physical reads';

Note if the Buffer Cache Hit Ratio is more the 90% then there is no problem.
If the Buffer Cache Hit Ratio is between 70% to 90% then there could be a problem.
If the Buffer Cache Hit Ratio is less then 70% there is Definitly a problem and buffer size need to be increased.

Resize Buffer Cache:

alter system set db_cache_size=60M;
alter system set db_keep_cache_size=15M;

-- to add a table to keep cache.

alter table keep cache;

select name, buffers from v$buffer_pool;

Keep Buffer Pool: Keep buffer pool is used to keep buffers in the pool as long as possible for data blocks that are likely to be reused.

Tunning Shared_pool

2011-03-17T11:43:00.000-07:00

Tunning Shared_pool

Point to remember:
###################

. Resizing the shared pool memory allocation
. Keeping an object in the shared pool memory
. Displaying the library cache hit ratio
. Displaying the object from the shared pool memory
. Remobing an object from the shared pool memory
. Calculating the dictionay cache hit ratio
. The major components of the shared Pool memory
. The library cache
. the dictionary cache
. The user Global Area
. Using the shared_pool_reserved_size

Commands:
##########

Set serveroutput on
execute sys.dbms_shared_pool.sizes(150);
execute sys.dbms_shared_pool.keep('SYS'.STANDARD);
execute sys.dbms_shared_pool.unkeep('SYS'.STANDARD);

The major components of SHARED POOL are library cache, data dictionary and UGA

Caculate Library Cache Hit Ratio.

select sum(pins-reloads), sum(pins)*100 as "library cache hit ratio"
from v$librarycache;

select sum(pins-reloads)/sum(pins)*100 as "library cache hit ratio" -- the hit ratio must be above 99%
from v$librarycache;

Details of Library cache Hit ratio:

-- GETHITRATIO must not be below 99%

select namespace, gethitratio
from v$librarycache
where gethitratio>0;

View Cached Objects:

-- Shows details of each object type that was cached more then 100 times.

select type, count(*) as "Number of Objects"
from v$db_object_cache
group by type
having count(*)>100;

View Large objects:

-- Shows object with size larger then 150K.

set serveroutput on
execute SYS.DBMS_SHARED_POOL.SIZES(150);

Cache a pl/sql Procedure:

BEGIN
SYS.DBMS_SHARED_POOL.KEEP('SYS',STANDARD);
END;
/

unCache a pl/sql Procedure:

BEGIN
SYS.DBMS_SHARED_POOL.UNKEEP('SYS',STANDARD);
END;
/

Dictionary Cache Hit Ratio:

-- Hit ratio must be above 98%
-- Low hit ratio indiates object not loaded into cache yet.

select parameter, gets , getmisses, 100*(gets-getmisses)/(gets) HIT_RATIO, modifications
from v$rowcache
where gets>0;

Overall Dictionary Cache Hit Ratio:

--The hit ratio should be above 85%
--If the Hit Ratio is belwo 85% then need to increase the Shared pool size.

select ((sum(gets-getmisses))/sum(gets))*100 as "Dictionary Hit Ratio"
from v$rowcache;

Segment Tunning.

2011-03-13T13:13:00.003-07:00

Segment Tuning:

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

what is Fragmentations in a table.

when there is huge delete operations done on a table,

The blocks which occupied the data will be get free from data but it will not release the space.

as there is free space but when you do insert oracle will occupy the new blocks not the used one.

High water mark:

The blocks which is once used ( means after delete blocks are free from data but not from space)

difference between truncate & delete

Truncate Delete

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

DDl DML

No Where Clause Where Clause.

No Rollback Rollback

It releases space It will not release Space.

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

Note : When a table is 20% or more fragmented Then go for re- organizing the table.

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

Disadvantage if Table is Fragmented:

If tables is fragmented more then 20% queries with full table scan, scan's upto high water mark.

Advantages if you re- org (TABLE/INDEX):

--> FTS Queries time reduces ( out of 10GB table after re- org it will scan 6GB)

--> Definitely Space will be released.

How to find out table fragmentation:

--> we need to have upto date statistics gathered for table.

formulae

table_size= (blocks * block_size) / 1024 / 1024 ( table size including free space)

table_free_space = (num_rows * avg_row_length) / 1024 / 1024 ( table free space size)

fragmentations= table_size - table_free_space

Query

--Author : Syed Kaleemuddin.

--Script name : Tab_frag.sql

--Description : check fragmentations in table (fragmentation size displayed in MB)

select (((blocks * (select value from v$parameter where name= 'db_block_size')) /1024 /1024) - ((num_rows * avg_row_len) / 1024 / 1024) ) "Fragmentation MB",

(((blocks * (select value from v$parameter where name= 'db_block_size')) /1024/1024/1024) - ((num_rows * avg_row_len) /1024/1024/1024) ) "Fragmentation GB"

from dba_tables

--where block_size=(select value from v$parameter where name= 'db_block_size')

where owner=upper('&owner')

and table_name=upper('&Table_name');

How to Re - Organize Table:

1) MOVE Command.

pre Check's -- Check table size as we need same amount of space in another tablespace where we are moving and same amount of space in temp also.

suppose table size = 10GB in users tablespace

need 10GB space in tablespace where we are moving this table and

need 10GB space in temp tablespace also.

Syntax: alter table move; --> if not moving to any other tablespace.

alter table move tablespace ; --> if moving table to different tablespace.

alter table move parallel 8; --> if use parallel option it will be fast simultaneously 8

process will move.

alter table move tablespace parallel 8; --> move table to diff tablespace using parallel option.

--> After moving tables indexes become unusable state, Rebuild the indexes.

alter index rebuild;

alter index rebuild parallel 8; (once rebuild index make to no parallel)

alter index noparallel;

To check indexes in parallel mode.

select index_name, ownere, degree from dba_indexes where degree > '1'; --> if you see any indexes whose degree is more

Make that index NOPARALLEL.

--> Gather stats on table.

2) SHRINK Command.

This method is introduced in Oracle 10g

This method does not require Down time.

Condition:

i. Tablespace must be ASSM ( Automatics Segment Space Management)

parameter Automatic_segment_space_management=Auto

ii. Table Should not contain Function Based Indexes.

Steps:

i. Enable Row movement.

select table_name, row_movement

from dba_tables

where table_name=upper('&table_name');

--> if it is disabled enable it

alter table enable row movement;

ii. Shrink table.

alter table Shrink space cascade; --> using cascade option indexes will not go unusable state.

iii. Gather Stats.

iv. Disable Row Movement.

alter table disable row movement;

3) Export - Import.

Export Table. --> Dangerous if export dump is currupted.

Truncate table.

Import Table.

4) CTAS (Create Table as select)

Steps:

i. create table as select * from ;

iii. insert into select * from ;

iv. Drop table ;

5) online Re-Defination:

need to prepare Notes.

How to Re - Organize Indexes:

if DEL_LF_ROWS of (sys.index_stats) > 20% --> index is fragmented

and BLEVEL of (sys.dba_indexes) = 4 --> index is fragmented

select index_name, blevel

from dba_indexes

where table_name=upper('&table_name');

select index_name, blevel

from dba_indexes

where BLEVEL > 3;

Check index Size.

select segment_name, bytes/1024/1024

from dba_segments

where segment_name in (select index_name

from dba_indexes

where table_name=upper('&table_name'));

select segment_name, bytes/1024/1024

from dba_segments

where segment_name in (select index_name

from dba_indexes

where BLEVEL > 1);

once get the index fragmented to bubble out space rebuild the indexes.

select name, height, lf_blks, del_lf_rows, blocks, distinct_keys

from index_stats

where name=upper('&index_name');

--> if del_lf_rows is 20% of lf_blks --> Re - Organize ( Rebuild) the indexe.

alter index rebuild;

alter index rebuild online;

alter index rebuild online parallel 8;

if using parallel then after rebuilding indexes make indexes to noparallel.

alter index noparallel;

Gather stats.

exec dbms_stats.gather_stats('SCOTT', 'TEST', Cascade=>True, Degree=>4);

exec dbms_stats.gather_stats('owner', 'table_name', Cascade=>True, Degree=>4);

select name, height, lf_blks, del_lf_rows, blocks, distinct_keys

from index_stats

where name=upper('&index_name');

--> if no output

Analyze index validate Structure;

--> output will display as you done rebuild indexes.

--> del_lf_rows = 0.

Differences between Data Pump impdp and import utility

2011-01-10T02:14:00.000-08:00

command differences of traditional exp- imp and datapump ( expdp – impdp)

Original Import Parameter
Comparable Data Pump Import Parameter

BUFFER
A parameter comparable to BUFFER is not needed.

CHARSET
A parameter comparable to CHARSET is not needed.

COMMIT
A parameter comparable to COMMIT is not supported.

COMPILE
A parameter comparable to COMPILE is not supported.

CONSTRAINTS
EXCLUDE=CONSTRAINT

DATAFILES
TRANSPORT_DATAFILES

DESTROY
REUSE_DATAFILES

FEEDBACK
STATUS

FILE
DUMPFILE

FILESIZE
Not necessary. It is included in the dump file set.

FROMUSER
SCHEMAS

FULL
FULL

GRANTS
EXCLUDE=GRANT

HELP
HELP

IGNORE
TABLE_EXISTS_ACTION

INDEXES
EXCLUDE=INDEX

INDEXFILE
SQLFILE with INCLUDE INDEX

LOG
LOGFILE

PARFILE
PARFILE

RECORDLENGTH
A parameter comparable to RECORDLENGTH is not needed.

RESUMABLE
A parameter comparable to RESUMABLE is not needed. It is automatically defaulted for privileged users.

RESUMABLE_NAME
A parameter comparable to RESUMABLE_NAME is not needed. It is automatically defaulted for privileged users.

RESUMABLE_TIMEOUT
A parameter comparable to RESUMABLE_TIMEOUT is not needed. It is automatically defaulted for privileged users.

ROWS=N
CONTENT=METADATA_ONLY

ROWS=Y
CONTENT=ALL

SHOW
SQLFILE

SKIP_UNUSABLE_INDEXES
SKIP_UNUSABLE_INDEXES

STATISTICS
A parameter comparable to STATISTICS is not needed. If the source table has statistics, they are imported.

STREAMS_CONFIGURATION
STREAMS_CONFIGURATION

STREAMS_INSTANTIATION
A parameter comparable to STREAMS_INSTANTIATION is not needed.

TABLES
TABLES

TABLESPACES
This parameter still exists, but some of its functionality is now performed using the TRANSPORT_TABLESPACES parameter.

TOID_NOVALIDATE
A command comparable to TOID_NOVALIDATE is not needed. OIDs are no longer used for type validation.

TOUSER
REMAP_SCHEMA

TRANSPORT_TABLESPACE
TRANSPORT_TABLESPACES (see command description)

TTS_OWNERS
A parameter comparable to TTS_OWNERS is not needed because the information is stored in the dump file set.

USERID
A parameter comparable to USERID is not needed. This information is supplied as the username/password when you invoke Import.

VOLSIZE
A parameter comparable to VOLSIZE is not needed because tapes are not supported.

Materialized View

2010-11-17T22:52:00.001-08:00

+++++++++++++++++++++++++++++++++++++
MVIEW refresh Status on MASTER Site :
+++++++++++++++++++++++++++++++++++++
set pages 1000
set linesize 180
col SNAPSHOT_SITE for a30
select r.name, r.snapshot_site, l.current_snapshots
from dba_registered_snapshots r, dba_snapshot_logs l
where r.snapshot_id = l.snapshot_id and l.master in (select master
from dba_snapshot_logs) ;
CLEAR COLUMNS

Show all materialized and resfresh times
========================================
set lines 100 pages 999
col last_refresh format a20
select owner
, mview_name
, to_char(last_refresh_date, 'dd/mm/yy hh24:mi') last_refresh
from dba_mviews
order by owner, last_refresh
/

Manually start a refresh
===========================

execute dbms_mview.refresh ('');

List all materialized view logs
=================================
select log_owner, log_table from dba_mview_logs;

MIGRATING AN ORACLE DATABASE FROM ONE PLATFORM TO ANOTHER PLATFORM (SOLARIS - LINUX)

2010-09-10T19:11:00.000-07:00

As per project requirement if database's needs to be moved from one platform to another
say if your current oracle database is running on SOLARIS platfrom and now need to move to a server on LINUX platform

Then one of the easist way you can follow this steps.

From xxx platform to any new platform ( solaris -- linux)

step 1) Take full db export backup of source (solaris)
exp full=y file=ab.dmp log=log.log
password: / as sysdba

step 2) Take a picture of all tablespaces and its size's
and datafile counts for each tablespace in source db

SQL> select tablespace_name, file_name, bytes/1024/1024 from dba_data_files;

step 3) Create an empty database in target server (linux) with same init.ora parameter file

step 4) run in target database

SQL> conn / as sysdba
SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql

SQL> conn system/passwd
SQL> @?/sqlplus/admin/pupbld.sql

step 5) in target db (LINUX)
create tablespaces same as in source (SOLARIS) with same size

eg: source (SOLARIS) target ( LINUX)
Tablespace_name size | Tablespace_name size
--------------- ---- | ---------------- ----
users 2048 | create users 2048
tools 16GB | create tools 16GB

like this create all tablespace except (SYSTEM, SYSAUX) as this tablespaces will be
create while creating db
now create undo , temp tablespaces.

step 6) scp the export dump from source( SOLARIS ) To target ( LINUX) server

step 7) import the dump into target (LINUX) new db

imp file=ab.dmp log=imp_mig_solaris-linux.log full=y buffer=500000 commit=y statistics=none

or you can also do one thing
import only the users which are not there in new database.
leaving (SYSTEM, SYSAUX, DBSNMP, etc) and importing only applications schema.

imp file=ab.dmp log=imp_mig-user-sol-lin.log commit=y, buffer=500000 statistics=none
fromuser=appdata touser=appdata

like this import all schemas if you dont want to do a full import.

once import completes your migration is over, just run this to compile all invalids.

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

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

if you are migrating and upgrading at the same time then also no issues
in source db(SOLARIS) run this first

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

it will display all the requirement to upgrade a db
while you create a new db in target (LINUX) use the same requirements

normally it will be

system tablespace size must be = XXXX MB
sysaux tablespac size must be = xxxx MB
temp = xxxx MB
undo_tbs = xxxx MB

in init.ora if recommends then you can modify it while creating a new db.

ONCE YOU ARE DONE WITH THIS ---> NOW TIME TO UPGRADE.
-------------------------------------------------------------------------------------

UPGRADE: In target server (linux)
bring down the db
startup in upgrade mode
-- SQL> shutdown immediate
-- SQL> starup upgrade
-- SQL> @?/rdbms/admin/catupgrd.sql
-- SQL> @?/rdbms/admin/utlrp.sql
-- SQL> shutdown immediate
-- SQL> startup
-- $ update the listener entry in listener.ora and tnsnames.ora
-- $ bring up the lister
-- $ lsnrctl start
-------------------------------------------------------------------------------------

You can follow the same procedure to migrate an oracle database

windows to linux & vice versa
windows to solaris & vice versa
solaris to linux & vice versa.
linux to AIX & vice versa
Aix to linux & vice versa
HP to linux & vice versa
windows to HP & vice versa
AIX to HP & vice versa
HP to AIS & vice versa

In windows only need to create an oradim service and it needs to be started.

Create instance - The following command can be used to create an instance with oradim:

C:\oracle\bin\oradim –new –sid MYSID –intpwd –maxusers 20 startmode auto –pfile initMYSID.ora

Startup - The following command can be used to start the instance with oradim:

C:\oracle\bin\oradim –startup –sid MYSID

Shutdown - The following command can be used to shutdown the instance with oradim:

C:\oracle\bin\oradim -shutdown -sid MYSID -shutttype SRVC,INST –shutmode A

for more information on ORADIM utility
refer this

http://javeedkaleem.blogspot.com/2009/12/starting-and-stopping-oracle-on-windows.html

How to apply oracle database patch (OPatch) without downtime.

2010-05-11T17:26:00.001-07:00

HOW TO APPLY ORACLE PATCH (OPATCH) WITHOUT DOWNTIME.
====================================================

This is commonly asked question in interviews.

To apply Opatch
coditions are db and listener both must be down as opatch will update your current ORACLE_HOME with patches.
in single instance its not possible.
but for RAC instance its possible.
as in RAC there will be two seperate oracle home and two seperate instances running once instance on each oracle_home

use this command:

opatch napply -skip_subset -skip_duplicate -local -oh $ORACLE_HOME

when using -local parameter and -oh $ORACLE_HOME this means this patch session will only apply patch to current sourced ORACLE_HOME.

steps before applying patch:
----------------------------

1) check the database status.
wch_db.sql
-----------
select name,
open_mode,
database_name,
created,
log_mode,
platform_name
from v$database;

2) Check the object's invalid.

user_inv.sql
============
SELECT owner,
COUNT(*)
FROM dba_objects
WHERE status = 'INVALID'
GROUP BY owner;

count_inv.sql
-------------
select count(*)
from dba_objects
WHERE status ='INVALID';

3) Take backup of invalid's

create table bk_inv_ as select * from dba_objects
where status='INVALID';

4) check opatch version using
opatch -v
if opatch version is not compatible check the readme file and
download the latest version and uncompress
in $ORACLE_HOME.

5) check oraInst.loc file pointing to your current $ORACLE_HOME or not.
cat /etc/oraInst.loc

inventory_loc=/u01/app/oracle/10.2.0/GlobalOraInv
inst_group=dba

if your server have more then one $ORACLE_HOME then comment the other $ORACLE_HOME and
uncomment the current $ORACLE_HOME

inventory must point to the current $ORACLE_HOME which is getting patched.

6) check free space on $ORACLE_HOME
df -h $ORACLE_HOME

7) chek the utilities like
which ld
which ar
which make
etc as per readme file.

8) unzip the patch
unzip -d /loc_2_unzip p.zip

9) Go the patch directory
cd /loc_2_unzip/patch_number

10) Bring down the listner.
cd $ORACLE_HOME/bin
lsnrctl stop

11) Bring down the database
Shutdown immediate.

12) export opatch
export PATH=$PATH:$HOME:$ORACLE_HOME/OPatch:/bin

13) Start the patch
opatch napply -skip_subset -skip_duplicate

for RAC database then database can be up
as it may be having more then one instance
so you can bring down one instance and listener and apply the patch and open it
and then do the same on another node.
like this db will be up and no user will face issue in outage also.

to apply opatch in RAC instance
opatch napply -skip_subset -skip_duplicate -local -oh $ORACLE_HOME

when using -local parameter and
-oh $ORACLE_HOME this means this patch session will only apply patch to current ORACLE_HOME only.

--------------------------------------------------------
. All-Node Patch

. Shutdown all Oracle instances on all nodes
. Apply the patch to all nodes
. Bring all nodes up

. Minimum downtime

. Shutdown the Oracle instance on node 1
. Apply the patch to the Oracle instance on node 1
. Shutdown the Oracle instance on node 2
. Apply the patch to the Oracle instance on node 2
. Shutdown the Oracle instance on node 3
. At this point, instances on nodes 1 and 2 can be brought up
. Apply the patch to the Oracle instance on node 3
. Startup the Oracle instance on node 3

. (no downtime)

. Shutdown the Oracle instance on node 1
. Apply the patch to the Oracle instance on node 1
. Start the Oracle instance on node 1
. Shutdown the Oracle instance on node 2
. Apply the patch to the Oracle instance on node 2
. Start the Oracle instance on node 2
. Shutdown the Oracle instance on node 3
. Apply the patch to the Oracle instance on node 3
. Start the Oracle instance on node 3
-------------------------------------------------------------

14) Once patch installation is completed need to do post patching steps.

a) starup the instance
startup
b) Loading modified sqlfiles into the database.
@$ORACLE_HOME/rdbms/admin/catbundle.sql cpu apply

to check the logs generated
catbundle_CPU__APPLY_.log
catbundle_CPU__GENERATE_.log

c) Recompiling Views in the Database
shutdown immediate
startup upgrade
@$ORACLE_HOME/cpu/view_recompile/view_recompile_jan2008cpu.sql
shutdown immediate
startup

If it is a RAC instance.
shutdown
startup nomount
alter database set cluster database=false scope=spfile;
shutdown
startup upgrade
@?/cpu/view_recompile/view_recompile_jan2008cpu.sql
shutdown
startup
alter database set cluster database=true scope=spfile;

restart the database.
cd $CRS_HOME/bin
srvctl start database -d
15) If any invalid objects were reported, run the utlrp.sql script as follows

user_inv.sql
============
SELECT owner,
COUNT(*)
FROM dba_objects
WHERE status = 'INVALID'
GROUP BY owner;

count_inv.sql
-------------
select count(*)
from dba_objects
WHERE status ='INVALID';

if any new invalids seen then again take backup of invalid objects and compile it.

create table bk_inv_ as select * from dba_objects
where status='INVALID';

@?/rdbms/admin/utlrp.sql --- to compile the invalid objects.

16) Confirm that patch has been applied successfully or not at db level also.

post_patch.sql
--------------
col action_time for a40
col action for a15
col namespace for a15
col version for a15
col comments for a40
set pages 1000
set lines 170
select * from registry$history ;

Oracle DataPump Quick Sstart

2010-05-06T16:44:00.000-07:00

INTRODUCTION

Oracle Data Pump is the replacement for the original Export and Import utilities.
Available starting in Oracle Database 10g, Oracle Data Pump enables very high-speed
movement of data and metadata from one database to another.

The new Data Pump Export and Import utilities have a similar look and feel to the
original utilities, but they are much more efficient and give you greater control and
management of your import and export jobs.

This paper gives users of the original Export and Import utilities a primer on how to move
up to the faster, more powerful, and more flexible Data Pump Export and Import utilities.
See the Oracle Database 10g Utilities Guide for more comprehensive information about
Oracle Data Pump.
NEW CONCEPTS IN ORACLE DATA PUMP

There are two new concepts in Oracle Data Pump that are different from original Export
and Import.

DIRECTORY OBJECTS
Data Pump differs from original Export and Import in that all jobs run primarily on the
server using server processes. These server processes access files for the Data Pump
jobs using directory objects that identify the location of the files. The directory objects
enforce a security model that can be used by DBAs to control access to these files.

INTERACTIVE COMMAND-LINE MODE
Besides regular operating system command-line mode, there is now a very powerful
interactive command-line mode which allows the user to monitor and control Data Pump
Export and Import operations.
CHANGING FROM ORIGINAL EXPORT/IMPORT TO ORACLE DATA PUMP

CREATING DIRECTORY OBJECTS

In order to use Data Pump, the database administrator must create a directory object
and grant privileges to the user on that directory object. If a directory object is not
specified, a default directory object called data_pump_dir is provided. The default
data_pump_dir is available only to privileged users unless access is granted by the DBA.
In the following example, the following SQL statement creates a directory object named
dpump_dir1 that is mapped to a directory located at /usr/apps/datafiles.

You would login to SQL*Plus as system and enter the following SQL command to
create a directory.

1. SQL> CREATE DIRECTORY dpump_dir1 AS ‘/usr/apps/datafiles’;

After a directory is created, you need to grant READ and WRITE permission on the
directory to other users. For example, to allow the Oracle database to read and to write
to files on behalf of user scott in the directory named by dpump_dir1, you must execute
the following command:

2. SQL> GRANT READ,WRITE ON DIRECTORY dpump_dir1 TO scott;

Note that READ or WRITE permission to a directory object means only that the Oracle
database will read or write that file on your behalf. You are not given direct access to
those files outside of the Oracle database unless you have the appropriate operating
system privileges. Similarly, the Oracle database requires permission from the operating
system to read and write files in the directories.

Once the directory access is granted, the user scott can export his database objects with
command arguments:

3. >expdp username/password DIRECTORY=dpump_dir1 dumpfile=scott.dmp

COMPARISON OF COMMAND-LINE PARAMETERS FROM ORIGINAL EXPORT AND IMPORT
TO DATA PUMP

Data Pump commands have a similar look and feel to the Original Export and Import
commands, but are different. Below are a few examples that demonstrate some of
these differences.

1) Example import of tables from scott’s account to jim’s account

Original Import:
> imp username/password FILE=scott.dmp FROMUSER=scott TOUSER=jim
TABLES=(*)

Data Pump Import:
> impdp username/password DIRECTORY=dpump_dir1 DUMPFILE=scott.dmp
TABLES=scott.emp REMAP_SCHEMA=scott:jim

Note how the FROMUSER/TOUSER syntax is replaced by the REMAP_SCHEMA
option.

2) Example export of an entire database to a dump file with all GRANTS, INDEXES,
and data

> exp username/password FULL=y FILE=dba.dmp GRANTS=y INDEXES=y ROWS=y

> expdp username/password FULL=y INCLUDE=GRANT INCLUDE= INDEX
DIRECTORY=dpump_dir1 DUMPFILE=dba.dmp CONTENT=ALL

Data Pump offers much greater metadata filtering than Original Export and Import. The
INCLUDE parameter allows you to specify which object (and its dependent objects) you
want to keep in the export job. The EXCLUDE parameter allows you to specify which
object (and its dependent objects) you want to keep out of the export job. You cannot
mix the two parameters in one job. Both parameters work with Data Pump Import as
well, and you can use different INCLUDE and EXCLUDE options for different operations
on the same dump file.

3) Tuning Parameters

Unlike Original Export and Import, which used the BUFFER, COMMIT, COMPRESS,
CONSISTENT, DIRECT, and RECORDLENGTH parameters, Data Pump needs no
tuning to achieve maximum performance. Data Pump chooses the best method to
ensure that data and metadata are exported and imported in the most efficient manner.
Initialization parameters should be sufficient upon installation.

4) Moving data between versions

The Data Pump method for moving data between different database versions is different
from the method used by original Export and Import. With original Export, you had to run
an older version of Export to produce a dump file that was compatible with an older
database version. With Data Pump, you use the current Export version and simply use
the VERSION parameter to specify the target database version. You cannot specify
versions earlier than Oracle Database 10g (since Data Pump did not exist before 10g).

Example:
> expdp username/password TABLES=hr.employees VERSION=10.1
DIRECTORY=dpump_dir1 DUMPFILE=emp.dmp

Data Pump Import can always read dump file sets created by older versions of Data
Pump Export.

Note that Data Pump Import cannot read dump files produced by original Export.
MAXIMIZING THE POWER OF ORACLE DATA PUMP

Data Pump works great with default parameters, but once you are comfortable with Data
Pump, there are new capabilities that you will want to explore.

PARALLELISM

Data Pump Export and Import operations are processed in the database as a Data
Pump job, which is much more efficient that the client-side execution of original Export
and Import. Now Data Pump operations can take advantage of the server’s parallel
processes to read or write multiple data streams simultaneously (PARALLEL is only
available in the Enterprise Edition of Oracle Database 10g.)

The number of parallel processes can be changed on the fly using Data Pump’s
interactive command-line mode. You may have a certain number of processes running
during the day and decide to change that number if more system resources become
available at night (or vice versa). For best performance, you should do the following:

• Make sure your system is well balanced across CPU, memory, and I/O.

• Have at least one dump file for each degree of parallelism. If there aren’t enough
dump files, performance will not be optimal because multiple threads of
execution will be trying to access the same dump file.

• Put files that are members of a dump file set on separate disks so that they will
be written and read in parallel.

• For export operations, use the %U variable in the DUMPFILE parameter so
multiple dump files can be automatically generated.

Example:
> expdp username/password DIRECTORY=dpump_dir1 JOB_NAME=hr
DUMPFILE=par_exp%u.dmp PARALLEL=4

REMAP

• REMAP_TABLESPACE – This allows you to easily import a table into a different
tablespace from which it was originally exported. The databases have to be 10.1
or later.

Example:
> impdp username/password REMAP_TABLESPACE=tbs_1:tbs_6
DIRECTORY=dpumpdir1 DUMPFILE=employees.dmp

• REMAP_DATAFILES – This is a very useful feature when you move databases
between platforms that have different file naming conventions. This parameter
changes the source datafile name to the target datafile name in all SQL
statements where the source datafile is referenced. Because the
REMAP_DATAFILE value uses quotation marks, it’s best to specify the
parameter within a parameter file.

Example:
The parameter file, payroll.par, has the following content:
DIRECTORY=dpump_dir1
FULL=Y
DUMPFILE=db_full.dmp
REMAP_DATAFILE=”’C:\DB1\HRDATA\PAYROLL\tbs6.dbf’:’/db1/hrdata/payroll/t
bs6.dbf’”
You can then issue the following command:
> impdp username/password PARFILE=payroll.par

EVEN MORE ADVANCED FEATURES OF ORACLE DATA PUMP

Beyond the command-line and performance features of Oracle Data Pump are new
capabilities that DBAs will find invaluable. A couple of prominent features are described
here.

INTERACTIVE COMMAND-LINE MODE

You have much more control in monitoring and controlling Data Pump jobs with
interactive command-line mode. Because Data Pump jobs run entirely on the server,
you can start an export or import job, detach from it, and later reconnect to the job to
monitor its progress. Here are some of the things you can do while in this mode:

• See the status of the job. All of the information needed to monitor the job’s
execution is available.

• Add more dump files if there is insufficient disk space for an export file.

• Change the default size of the dump files.

• Stop the job (perhaps it is consuming too many resources) and later restart it
(when more resources become available).

• Restart the job. If a job was stopped for any reason (system failure, power
outage), you can attach to the job and then restart it.

• Increase or decrease the number of active worker processes for the job.
(Enterprise Edition only.)

• Attach to a job from a remote site (such as from home) to monitor status.

NETWORK MODE

Data Pump gives you the ability to pass data between two databases over a network (via
a database link), without creating a dump file on disk. This is very useful if you’re
moving data between databases, like data marts to data warehouses, and disk space is
not readily available. Note that if you are moving large volumes of data, Network mode
is probably going to be slower than file mode.

Network export creates the dump file set on the instance where the Data Pump job is
running and extracts the metadata and data from the remote instance.

Network export gives you the ability to export read-only databases. (Data Pump Export
cannot run locally on a read-only instance because the job requires write operations on
the instance.) This is useful when there is a need to export data from a standby
database.

GENERATING SQLFILES

In original Import, the INDEXFILE parameter generated a text file which contained the
SQL commands necessary to recreate tables and indexes that you could then edit to get
a workable DDL script.

With Data Pump, it’s a lot easier to get a workable DDL script. When you run Data
Pump Import and specify the SQLFILE parameter, a text file is generated that has the
necessary DDL (Data Definition Language) in it to recreate all object types, not just
tables and indexes. Although this output file is ready for execution, the DDL statements
are not actually executed, so the target system will not be changed.

SQLFILEs can be particularly useful when pre-creating tables and objects in a new
database. Note that the INCLUDE and EXCLUDE parameters can be used for tailoring
sqlfile output. For example, if you want to create a database that contains all the tables
and indexes of the source database, but that does not include the same constraints,
grants, and other metadata, you would issue a command as follows:

>impdp username/password DIRECTORY=dpumpdir1 DUMPFILE=expfull.dmp
SQLFILE=dpump_dir2:expfull.sql INCLUDE=TABLE,INDEX

The SQL file named expfull.sql is written to dpump_dir2 and would include SQL DDL
that could be executed in another database to create the tables and indexes as desired.

FREQUENTLY ASKED QUESTIONS

Why are directory objects needed?
They are needed to ensure data security and integrity. Otherwise, users would be able
to read data that they should not have access to and perform unwarranted operations on
the server.

What makes Data Pump faster than original Export and Import?

There are three main reasons that Data Pump is faster than original Export and Import.
First, the Direct Path data access method (which permits the server to bypass SQL and
go right to the data blocks on disk) has been rewritten to be much more efficient and
now supports Data Pump Import and Export. Second, because Data Pump does its
processing on the server rather than in the client, much less data has to be moved
between client and server. Finally, Data Pump was designed from the ground up to take
advantage of modern hardware and operating system architectures in ways that original
Export/ and Import cannot. These factors combine to produce significant performance
improvements for Data Pump over original Export and Import

How much faster is Data Pump than the original Export and Import utilities?

For a single stream, Data Pump Export is approximately 2 times faster than original
Export and Data Pump Import is approximately 15 to 40 times faster than original Import.
Speed can be dramatically improved using the PARALLEL parameter.

Why is Data Pump slower on small jobs?

Data Pump was designed for big jobs with lots of data. Each Data Pump job has a
master table that has all the information about the job and is needed for restartability.
The overhead of creating this master table makes small jobs take longer, but the speed
in processing large amounts of data gives Data Pump a significant advantage in medium
and larger jobs.

Are original Export and Import going away?

Original Export is being deprecated with the Oracle Database 11g release. Original
Import will always be supported so that dump files from earlier releases (release 5.0 and
later) will be able to be imported. Original and Data Pump dump file formats are not
compatible.

Are Data Pump dump files and original Export and Import dump files compatible?

No, the dump files are not compatible or interchangeable. If you have original Export
dump files, you must use original Import to load them.

How can I monitor my Data Pump jobs to see what is going on?

In interactive mode, you can get a lot of detail through the STATUS command. In
SQL, you can query the following views:

• DBA_DATAPUMP_JOBS - all active Data Pump jobs and the state of each
job
• USER_DATAPUMP_JOBS – summary of the user’s active Data Pump jobs
• DBA_DATAPUMP_SESSIONS – all active user sessions that are attached to
a Data Pump job
• V$SESSION_LONGOPS – shows all progress on each active Data Pump job

Can I use Oracle Enterprise Manager with Data Pump?

Yes, OEM supports a fully functional interface to Data Pump.

Can I use gzip with Data Pump?

You can do a Data Pump Export and then run the dump files through gzip to compress
them. You cannot compress the dump files prior to writing them. (The new
COMPRESS parameter can be used to compress metadata, and metadata compression
is enabled by default in Oracle Database 10g Release 2.)
CONCLUSION

Data Pump is fast and flexible. It replaces original Export and Import starting in Oracle
Database 10g. Moving to Data Pump is easy, and opens up a world of new options and
features.

Guide to configuring HP Omniback integration with Oracle RMAN

2010-04-01T12:38:00.000-07:00

PURPOSE

The purpose of this document is to act as a 'quick start' guide to configuring
HP Omniback integration with Oracle RMAN.

SCOPE & APPLICATION

This document is aimed at Support Personnel. It is not intended as a replacement
for detailed Omniback or RMAN documentation.
Omniback queries should be directed at the Omniback / Dataprotector Support Centre.

NOTE: This document describes a setup in a certain configuration and might
be bound to the used versions.
New versions of OmniBack / Dataprotector might have a different setup.
Contact your Omniback / Dataprotector Support Centre for more specific information.

RELATED DOCUMENTS

Note:50875.1 Getting started with Server-Managed Recovery (SMR) and RMAN
Note:73431.1 RMAN Compatibility Matrix

Oracle8i & 9i Server Backup & Recovery Guide
Omniback documentation
http://ovweb.external.hp.com/lpe/doc_serv

0. Introduction
===============
This Note can be used as a starting point when configuring Omniback with
RMAN. The examples in this Note used the following software:

i. HP Omniback 4.10 for HP-UX 11
ii. Oracle 8.1.7 (32 bit)

The Oracle commands are not specific to HP-UX. However, many of the Omniback
references may not be relevant to other operating systems (Omniback is also
available on NT and Solaris).

1. Create Recovery Catalog
==========================
Create a recovery catalog, as detailed in Note:50875.1.
The recovery catalog schema can be initially sized at 20MB.
Note that creating/using a recovery catalog is optional.

2. Setup SQLNet Connectivity to Recovery Catalog
================================================
A TCP loopback test to the catalog should be performed.

% sqlplus rman/rman@catalog
where the SQL*Net alias for the catalog database is 'catalog'.

3. Setup SQLNet Connectivity to Target Database
===============================================
A user with SYSDBA privileges is required to connect to the target database.

i. set up remote logins to the target database
a. add the following to the init.ora:
remote_login_passwordfile=exclusive
b. create a password file
orapwd file=$ORACLE_HOME/dbs/orapw$SID password=oracle
ii. for the above changes to take affect, stop/start the target instance
iii. create a user, and grant SYSDBA priveleges
This step is optional, as the 'internal' or '/' could be used.
SVRMGR> create user targdba identified by targdba
temporary tablespace temp
default tablespace users quota unlimited on users;
SVRMGR> grant connect, resource to targdba;
SVRMGR> grant sysdba to targdba;

Note, if (i) and (ii) have not succeeded, this step will fail with
ORA 1994 - GRANT failed: cannot add users to public password file

iv. Do a loopback test as the SYSDBA user

% sqlplus targdba/targdba@target
or
% sqlplus internal/oracle@target
where the SQLnet alias for the target database is 'target'.

NB. Certain operations in the Omniback GUI require access to tables/views that
are not available to all users.
For example, expanding the 'database' icon into a list if tablespaces
requires access to DBA_TABLESPACES.
The targdba user given above does not have SELECT privilege on DBA_TABLESPACES.
For this reason, it may be preferable to use the internal/oracle example.

4. Enable Archiving
===================
Both the RMAN catalog and the target database may need to have archiving enabled.
eg, init.ora changes:
log_archive_dest = /oracle/OFA_base/app/oracle/admin/V817/arch/arch_
log_archive_format = _%s.arc
log_archive_start = true

SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;

5. Install Omniback
===================
Follow the Omniback documentation to install Omniback. A common problem is not
being able to find the Omniback API Library after installation. This is because
initial installation only installs the Oracle installation packet (referred to
as OB2-ORA). This installation packet needs to be pushed to the Oracle server
(in this context, Oracle servers are Omniback clients).

To do this, open the Omniback GUI, and choose clients. Choose the client that
has the Oracle server, and right click. Choose 'add components', and follow
the wizard.

After installation, Omniback creates the following base directories:
/var/opt/omni
/opt/omni
/etc/opt/omni

Omniback patches can be found at
http://support.openview.hp.com/patches/patch_index.jsp

6. Integrate RMAN with the Omniback API Library
===============================================
The instance (in fact, all instances with this ORACLE_HOME) need to be shutdown
prior to the following, and restarted after. This assumes the default location
of /opt/omni/lib for the Omniback library.

For 32 bit Oracle8i:
% cd $ORACLE_HOME/lib
% mv libobk.sl libob.sl.orig
% ln -s /opt/omni/lib/libob2oracle8.sl libobk.sl

For 64 bit Oracle8i
% cd $ORACLE_HOME/lib64
% mv libobk.sl libob.sl.orig
% ln -s /opt/omni/lib/libob2oracle8_64bit.sl libobk.sl

For Oracle 9i (on HP-UX, this is available as 64 bit only)
% cd $ORACLE_HOME/lib
% mv libobk.sl libob.sl.orig
% ln -s /opt/omni/lib/libob2oracle8_64bit.sl libobk.sl

To verify the relink was succesful,
% cd $ORACLE_HOME/bin
% chatr oracle
oracle:
shared executable
shared library dynamic path search:
SHLIB_PATH disabled second
embedded path disabled first Not Defined
shared library list:
dynamic /oracle2/OFA_base/app/oracle/product/8.1.7/lib/libjox8.sl
dynamic /oracle2/OFA_base/app/oracle/product/8.1.7/lib/libobk.sl
<...more output here...>

% ls -la /oracle2/OFA_base/app/oracle/product/8.1.7/lib/libobk.sl
lrwxr-xr-x 1 hpsupp support 30 Apr 29 12:48
/oracle2/OFA_base/app/oracle/product/8.1.7/lib/libobk.sl@ -> /opt/omni/lib/libob2oracle8.sl

Note that the Omniback API is now linked into the oracle 8i executable.

Note that for Oracle9i the libobk file is no longer linked in with the oracle
executable. The libobk is linked in at runtime by looking for the libobk in
the $ORACLE_HOME/lib directory or by setting RMAN allocate or configure channel
parameter SBT_LIBRARY.

7. Create the 'oracle' user in Omniback
=======================================
Start /opt/omni/bin/xomni as root

i. from the Omniback 'Main' window, choose 'Users' from dropdown list
ii. right click on 'operator' or 'admin' in 'Users' list, and choose 'Add Users...'
NB. The user must be in the admin or operator group
iii. 'Type' field is UNIX
'Name' field is the oracle software owner eg, oracle
'UNIX Group' is the UNIX group of the oracle software owner eg, dba
'Description' is some string that identifies this user
'Client' should be the host on which the target instance resides

NB. If text cannot be entered into the input boxes, choose '' from the
dropdown list of values, and edit user afterwards.

8. Register the Target Database via Omniback
============================================
This step uses a Omniback script to
a. tell Omniback about the target and catalog databases
b. register the target database with the catalog (if not already registered)

The simplest way to do this is to run 'Create New Backup' from the Omniback GUI.
If the target database is not already registered with Omniback, then Omniback
prompts for relevant parameters, and runs /opt/omni/lbin/util_oracle8.exe
transparently. The GUI can also be used to check the configuration.

Alternatively, run /opt/omni/lbin/util_oracle8.exe manually, as shown below.

i. run /opt/omni/lbin/util_oracle8.exe. The usage is:
/opt/omni/lbin/util_oracle8.exe -CONFIG \
\
\

ii. this will cause SQLNet connections to be established to the
catalog and target databases. Consequently, TNS_ADMIN must be set if
required.

For example, using the info from steps (2) and (3)
% export TNS_ADMIN=/oracle/TNS_ADMIN
% /opt/omni/lbin/util_oracle8.exe -CONFIG RMAN81T \
/oracle2/OFA_base/app/oracle/product/8.1.7 \
targdba/targdba@81targ \
rman/rman@81cat

This step creates the following files in the directory
/etc/opt/omni/integ/config/Oracle8
i. %
- this contains encrypted logon info, ORACLE_HOME, ORACLE_VERSION
- replaces the following, found in previous releases of Omnniback
/etc/opt/omni/oracle8/
catconn - an encrypted catalog connect string
tgtconn - an encrypted target connect string
oracle_home - ORACLE_HOME for the target
ii. %_OB2_GLOBAL
- global configuration file for Omniback
- contains a list of instances configured for Omniback
a list of Omniback global variables

Note that if TNS_ADMIN needs to be set in the environment, then the file
/etc/opt/omni/oracle8//.profile
needs to be created to set TNS_ADMIN. The contents of this file need to
be of the form:
TNS_ADMIN=/oracle/TNS_ADMIN
export TNS_ADMIN

To check the config, run the following
%/opt/omni/lbin/util_oracle8.exe -CHKCONF

For a succesful configuration, the output is:
% /opt/omni/lbin/util_oracle8.exe -CHKCONF RMAN81T
*RETVAL*0

NB - any non-zero RETVAL is an error

9. Configure and Run an Omniback Backup
=======================================
This cannot be fully described in this Note. However, the main points encountered
during setup are documented here.

i. from the Omniback 'Main' window, click on 'Backup'
ii. from the 'Backup' Window menu, expand the 'Backup' tree to:
Backup
--> Backup Specification
--> Oracle8 Server (this is the case for Oracle9 also)
Right click on 'Oracle8 Server'. Choose 'Add Backup'
iii. Fill in the 'Client and 'Application Database' values from the list of values
given.
The list of 'Application Databases' is from /etc/oratab.

The 'User' and 'Group' are the UNIX user that will carry out the backup,
typically oracle/dba.

iv. Complete the remaining steps.

The RMAN script produced will be of the following form (assuming the script
is saved with as 'test_full'):

run {
allocate channel 'dev_0' type 'sbt_tape'
parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=RMAN81T,OB2BARLIST=test_full)';
backup incremental level filesperset 1
format 'test_full.dbf'
database
include current controlfile
;
}

This is saved as a Omniback script /etc/opt/omni/barlists/oracle8/test_full.
The script is referred to as the 'backup specification name'.
Note the ENV parameters in the 'allocate channel' command. These are Omniback
specific; the meanings are
OB2BARTYPE - this will be 'Oracle8' for RDBMS 8i and 9i
OB2APPNAME - this is the target database ORACLE_SID
OB2BARLIST - this is the name of the RMAN script

The 'rman' executable is called by the Omniback executable ob2rman.exe.

By default, Omniback will take an export of the RMAN Catalog (a user level export,
as 'rman' user), and backs that up to tape. The export is initially dumped in
/var/opt/omni/tmp.

Note: This section has omitted many steps, such as:
- setting the protection levels on the device
- setting concurrency on the device
- pre and post exec scripts (see below)
- reporting levels

The progress of a backup can be monitored with :
/opt/omni/bin/omnistat

Restrictions imposed on backup scripts are discussed below.

If the backup does not run, verify the integration as follows:

i. ensure the configuration has been checked (see Step (8))
ii. ensure the cell server name is correct in
/etc/opt/omni/cell/cell_server
Try replacing this with the IP address if hostname lookup is problematic
iii. verify Omniback internal data transfer. The following command will write
a very small amount of data to tape, simulating a backup

/opt/omni/bin/utilns/testbar -type:Oracle8 \
-appname: -bar: -perform:backup

The Monitor window in the Omniback GUI will report any errors, which should
be followed accordingly.

10. Restoring an Omniback Backup
================================
Omniback does not have functionallity for a RMAN restore. The restore must be
carried out manually.

For example, using the connect info already provided in the note.
% rman catalog rman/rman@81cat target targdba/targdba@81targ

RMAN> run {
2> allocate channel c1 type 'sbt_tape' ;
3> restore database;
4> }

During the restore, the Omniback GUI 'Monitor' Window will track progress. This
info can also be found in sbtio.log (user_dump_dest).

To diagnose restore problems, Omniback provides 'omnidb' to interrogate the
Omniback database, and 'testbar' to simulate restore of identified backup sets.

omnidb is used to gather details about the backup that needs to be restored. This
also verifies that the object exists in the Omniback catalog.

For example
% omnidb -session -detail
- execute this if no details known about the backup
% omnidb -session -datalist "Oracle8 "
- execute this to limit the output (if backup spec name known)
eg, omnidb -session -datalist "Oracle8 test_full"

Look through the output, and pick out the SessionID for the session that carried
out the backup. The SessionID wil be of the form "2002/05/15-7".

To obtain the names of Omniback Objects (backup sets), execute
% omnidb -session
eg, omnidb -session 2002/05/15-7

Note the "Object Name" values.

Armed with this info, call testbar to simulate a recovery session. This will
restore a small amount of data from the specified Object Name.

/opt/omni/bin/utilns/testbar -type:Oracle8 -appname: \
-perform:restore -object:"" \
-bar: -version:

where
is the ObjectName returned by omnidb (see above)
Do not inlcude the "hostname:" returned by omnidb
is the SessionID given above

eg,
testbar -type:Oracle8 -appname:RMAN81T -perform:restore \
-object:"test_full.dbf" \
-bar:test_full -version:2002/05/15-7

The Monitor window in the Omniback GUI will report any errors, which should be
followed accordingly.

11. Log Files & Debugging
=========================
a. There are many log files in /var/opt/omni/log, notably
oracle8.log - oracle related messages
debug.log - Omniback system messages and unexpected errors
media.log - Omniback tape related messages

b. sbtio.log in user_dump_dest

c. Omniback Debugging
Most Omniback commands can take the -debug parameter. The format of the
debug switch is
-debug 1-200 XYZ "host"
where 1-200 is the debug range - HP should be able to advise on this
XYZ is the debug trc file suffix
host is where debug is to be turned on (optional)

Debug will cause many trace files to be created in /tmp, with names of the form
OBprogram_Host_OBPid_XYZ

From an Oracle perspective, turn on debugging by executing a command like:

/opt/omni/lbin/util_cmd -putopt Oracle8 RMAN81T OB2OPTS \
'-debug 1-200 debug_RMAN81T.txt' \
-sublist Environment

Turn the debugging off with :
/opt/omni/lbin/util_cmd -putopt Oracle8 RMAN81T OB2OPTS \
'' \
-sublist Environment

In the above RMAN81T is the ORACLE_SID

This updates /etc/opt/omni/integ/config/Oracle8/% with an
entry in the Environment string, such as
Environment={
OB2OPTS='-debug 1-200 debug_RMAN81T.txt';
}

From now on, all Omniback commands for this instance will give debug output.
The debug filenames will of the form /tmp/*debug_RMAN81T.txt.

HP should be consulted before enabling Omniback debugging.

APPENDIX A - Miscellaneuos Omniback Commands
==========
% /opt/omni/lbin/util_cmd
util_cmd -getconf Oracle8
- get config for an instance

util_cmd -putopt Oracle8 OB2OPTS \
'-debug 1-200 INSTANCE.txt' -sublist Environment
- set global environment (debugging in this case)

% /opt/omni/lbin/omnigetmsg
omnigetmsg
- get an error description for a given error in a given set
omnigetmsg 12
- Oracle errors are in set 12

Note that the error descriptions tend to be high level
eg, [12:8324] This Oracle8 Server instance is not configured

% /opt/omni/bin/utilns/testbase -perform:checkuser
- verifies permissions of current user
should run as oracle/dba

% /opt/omni/lbin/util_oracle8.exe -CHKCONF
- check config of Oracle server

% /opt/omni/sbin/utilns/get_info
- gather Omniback system info, in preparation for submitting a Omniback
support call to HP

APPENDIX B - PRE-EXEC, POST-EXEC SCRIPTS
==========
When configuring a backup, pre-exec and post-exec scripts can be entered. These
are scripts/commands that are started on the Oracle server prior/post backup.

ob2rman.exe is called with arguments "-pre