Orac is the creation of a dedicated group of volunteer programmers led by Andy J. Duncan. Some of the major contributors include Kevin Brannen and Thomas Lowery, but many others too numerous to mention have contributed as well. A list of most of the contributors is included in the README file that accompanies the program. You can find out more about Orac, including installation hints and tips and mailing list information, from the Orac home page listed below.

http://technet.oracle.com/linux/: Oracle’s Linux home page

http://www.tux.org/dclug/oracle/index.htm: “Presentation: Oracle Database Administration on Linux”

http://linuxworld.com/linuxworld/lw-1999-06/lw-06-oracle.html: Installing Oracle on Linux

http://jordan.fortwayne.com/oracle/: Installation hints and tips

http://www.kkitts.com/orac-dba/: Orac Home Page/Mailing List Information

http://www.symbolstone.org/technology/perl/DBI/index.html: Information on Programming with DBI/DBD

ftp://ftp.funet.fi/pub/languages/perl/CPAN/authors/id/A/AN/ANDYDUNC: Download the Orac software here.

http://www.lazydba.com/: Oracle DBA site, scripts and tips

BEA’s WEblogic, Iplanet Application Server, WEbSphere,Oracle Application Server, Macromedia JRun, Persistence’s PowerTier, Brokat’s Gemstone/J,HP’s Bluestone,IONA’s IPortal,Borland’s AppServer and the JBoss application server
Togethersoft’s Together/J and Rational’s Rational Rose

v

set wrap off

select table_name from user_tables;

show all

set linesize 80

set pagesize 24
set time on

Oralce Instance(exits in memory )

Buffers
Processes
Files
Network communication

___________________________________________________
Oralce Instance
___________________________________________________
ProcessLayer
___________________________________________________
Memory layer(Sga -shared Area)
_________________________________
Databse buffers            | Database writer
redo log buffer            | log write
sort buffer            | archiver
shared pool            | pmon smon
Parsed sql and pl/sql    |
latches and locks    |
metadata        |
___________________________________________________

Datafiles, redologs,archivelog, controlfiles,config files
____________________________________________________
Networking configuration and process

Simple Data Types

[n]varchar2(n) Variable length string up to 4000

[n]char(n) Fixed length string up to 2000 charaters

number(p,s) Precision=number of character, scale= number of decimal places p=1 to 38 s=-84 to 127

float(p) A floting poin or real number. p=1 to 126

Integer Creates number(38 , size 38 character number field

Smallint create number(38), size 38 characters number field

Date stored as Juian date in seconds containing year month, day, hours, minutes, seconds to 100th precision

timestamp(p) precision is scale in fractions of a second thus timestamp(2) stores 10th of a second

BLOB=large video ,picture text sound in binary format
CLOB= large text doucumet
NCLOB

complex data types

REf pointer refencinge another object

bfile pointer including full path and file name to a file stored externally to the database. Typically used for multimedia objects

Nested Table Dynamic or variable length arry used for creating user defined data types

varray(n) fixed length arry used for creating user defined data types

Xml storage and direct xml document retrieval

Spatial GIS and CAM sysstem geometric modeling data storage

Media Multimedia storage

Any Generic daa type storage

=================================================
types of tables
Relational tables
Object table
Temporary table(one session cannot see another’s data)
index organized table(table+indexcolum)
cluser(join multiple table into single)
exernal table(store externaly in file)
XML type table (store xml document)
Partitionaled table
(Partition or split a single tables into multiple tables places on different disk)

==================================================
set timing off
spool log\createuser.log;
create user sabin identified by shrestha
default tablespace data
temporary tablespace temp
quota unlimited on data
quota unlimited on INDX
quota unlimited on objects
quota unlimited on temp;
grant connect,resource to sabin;
grant unlimited tablespace to sabin;
grant query rewrite to sabin;

spool off;
exit;
===============================
Create global temporary table shows(act varchar2(64),
venue varchar2(64))
on commit preserve rows;
=====================================
Type of Indexes
Btree(Binary Tree)
Bitmap(for multidedia file)
function Based
Index Organized Table
Cluster
Bitmap Jion
Domain(for picture goematical)

====================
Acending or decending
uniqueness
composites
compression
reverse key indexes
sorting(no sor)
nall values

primary and unique are created internal index foregin key are not created

create index on foregin key
create index ind1 on show (act_id asc);
alter index ind1 rebuild

===============================
sequence
create sequence test_seq start with 1 increment by 1
nomaxvalue nocycle;
=========================================
Synonyms
another name for table
create or replace public synonym [schema.]synonym
for [schema.]object
================================
metadata
allviews
userviews

Purpose
Interrogation
maintenance
performance

select table_name from all_tables;
select table_name from dba_tables;
select table_name from user_tables;
validation
dba vies

V$ performance views

user_objects
usertables
user_indexes
user_clusters
user_lobs
user_sequences
user_sysnonyms
user_views

table index and cluster columns
user_tab_columns
user_ind_columns
user_clu_columns

constraints
user_constraints
user_cons_columns

user defined types
user_nested_tables
user_varrays

privileges
user_role_privs
user_sys_privs
user_tab_privs

pl/sql
user_procedures
user_triggers
user_triggers_cols
user_source

user_users
user_errors

Error messages in oracle websites
====================================================
Schemas and users
schemas schematic
Ceteral area to store table stuructre and  data

users can be looks at single schema
users and schema is same

System previleges(create table index sequence)
object privileges(alreday created privileges)
column privileges
views
the virtual private database

Groupings
Roles
public and security

Special Roles
dbi
selectcatlog

sharing resources and profiling
user_role_privs
user_sys_privs
user_tab_privs

connect [email protected] as sysdba
desc role_sys_privs;

types of users
Database Administrators
=====================================

Security Administrators
Network Administrators
System Administrators
Applications Developers
Testing
Prower Users
End Users

sqlplus [email protected] @a.sql

revoke connect from developer;
grant create session to developer
===============================
dbausers
system
sys

previlies
sysdba sysoper

system privileges
object privileges

====================
database and sysem
sysdba and syoper
database links
rollback segments
sessions
tablespaces
analiyze
grant

=======================
roles alter any drop any,grant any
session  create,alter
users create,alter,drop

==============================================================================================

JOins
Cross Join /cartesian product
Natural /innerjoin
outer join(left right full)
self join
eqi/anti/range join
mutable/complex join

=====================================

==============================
rollup
cube
grouping sets(filter on rollup and cube)

group by rollup(v.name);
group by cub(v.name,a.name);
group by grouping sets((v.name,a.name),(a.name),(v.name));

group by grouping sets((v.name,a.name),(a.name));

group by grouping sets((v.name,a.name));
==================================
subquery tune the select command

Single Query
categoryid=(select)

Multiple Row
categoryid IN(select)

Multiple Column
and(s.show_id,s.venue_id) in(select showid,venue_id from ticket)

regular
caegory_id in(1,2,34,5);

correlated
where exists(select category_id from show where category_id=c.category_id);

exists is fast then in()

data is low then in
data is hight then use exists

Conditional Comparisions
(subquery) [= != > < >= <=] (subquery)
(subquery) [not]like (subquery)
(subquery)  IN (subquery)
[NOT]  Exists (subquery)
(subquery) between(subquery(sigle row ret)) and (subquery(sigle row ret))

(subquery) [= != > < >= <=] [any some all] (subquery(multipule row))

Hierarchiacal Queries
familytree(tablename)
name             father      level
william ford1             1
john ford     williamford1   2
willam ford II   john ford      3
henry ford       william ford II 4
edsal ford       henry ford    5

select name ,father,level from familytree
start with name=”William Ford I’
connect by prior name=father;

sets opeator and composit Queries
Union all all rows from both queies including duplicates

insert
update
deleted
merge (new data is inserted,already existing data will be updated)

Transactions(block of dml command)
commit and rollback
set transation
commit rollback(end transation
savepoint(label to return block structure)

truncate table test

merg
merge 2 tables

merge into category c
using newcategory nc
on(c.category_id=nc.categoryid)
when matched then
updae set c.name=nc.name
when not matched then
insert valued (nc.categoryid,nc.name,nc.parentid);

=====================================================

Create database
shared pool 128 Mb
buffer cache 64
javapool=0
largerpool=8
pga=0

DbSizing
sort Area size 32 K

create achive mode

Tablespace
remoove tools
remove users
and add data
(locally manged
automatic

redo log
18 redo log files
all 10 Meg

datadistnory is saved in system tablespace

parameterfile in
ora92\database
initprod.ora
ifile=’e;\oracle\admin\prod\pfile\init.ora’

4 Memory Structure

System Global Area
show sga
desc v$sgastat
select * from v$sgastat

javapool=0
Databse Buffer Cache(cpu and disk io  buffer management)
Multiple Buffer Cache

Shared Pool
Library Cache
metadata or Dictionary Cache
v$librarycache
v$rowcache

redo log buffer

large pool

Session conections
Program global Area(PGA)
Shared Servers(MTS)

Java pool(java store procedure)

Processes
Server Processes(executed by client in server)
Dedicated Server
Shared Server
Dispatcher

client Connection Proc
Background Processes(executed by oracle)
System Monitor (SMON)
Maintain of Physcial space, temprory spaces and recovery)clean of work
Process Monitor(PMON)
Monitor process ,and fail clean up client connection is broken then clean it free it

Database Writer
communication between databasebuffer and disk, ditry block is data that is changed

Log Writer
write log entry into redo log file

ARcn
same as log write but it copies and write redo log file into archive log file such cyclic redo file can be re used again

oracle
=====================
|                    |
| Tablespace  |  | schema <– Owner |   Object
|             |  |                  |   /
|  Datafile   |  |      Segment     |
=========== ^   |                  |
| – – – – -Extent      |
|                  |
|      block       |
=================

Datafile Tablespaces and Object
Tablespaces
is a logical low level object which includes data files(logical wrapper of datafiles)
can contain multiple datafile orcle can contain multiple table spaces

Datafile is file that is stored in os as a file which contains database information,tablerow indexes datadictionary

Schema
is a schematic, digram,plan or container or defination of things
defination of owner or usernames
usernames contains tables logical object or logical overlays over physical structure of datafile
schema and tablespace are not directly related
schema can make user of multipule tablespaces
and tablespaces can contain multiple tablespaces

Segment
object defined within schema
physical information within that object actual data with in table
schema define defincation contain bucket for putting data
segment is physcial area occupied by the data

Schema contain table
segment is data within the table

extent
is physical chunk added onto datafile as the data file grows
added automatically as define sized defined in database
571QYuge1C0
phicial chuk is added to allow to add more rows related to schema and datafile

below extent lowest it is block
smalleset phiysical chuck within oracle database datafile segment index table

large block is usefull when reading whole table
samll block is usefull when reading single row

Controlfiles and recoverability
Logging
it contains a bunch of pointer to other things
logging is can be used to remcover before 1days 1 weeks using pointers
redo log file is used to cycle when they are completed they are copied to archive log
archive log is historical log

insert: row+rowid
update:changes|rowid
delete: rowid
ddl activity
Not everything must be logged(indexes)

Records transations in redo log files
Archiving
stores old redo log files
Recoverability
is control by controlfile
pointer to logs and datafiles (physical location,files)
syncronsize between to logs and datafiles
logs+archives
datafiles

Commit and rollback
commit store changes to database
rollback is to undoo
savepoint
transaction control
set transaction

before commit or rollback
database is changed
log is written
rollback is written

on commit
rollback is deleted
rollback writes changes to redo logs

on rollback
rollback applied to database
rollback recorded is logs
rollback is deleted
rollback writes changes to redo logs

(redo logs contain record of both changes and rollback undo)

Manul rollback
undo_management=manual

rollback is maunal
specify and build rollback segments

undo is automated rollback
rollback is information used to undo the the changes of databases which not yet commited

undo
undo_management=auto
automatic rollback management

when rollback manual
tuning rollback segments
oldtp= small and many
Minimize Transactions/RollbackSegment

Undo
in period of time for which alredy committed rollback information is kept or retain
used for flashback it is based for period of time

desc dba_tablespaces; tabespace_name,contents
Tomporary tablespaces sort spaces
workarea_size_policy
sort_area_size
sort_area_retained_size

auto can also used as dedicated not is shared
pga_aggregated_target

installation

lsnrctl
status

emca

db_create_file_dest ( destination of datafile)
db_create_oneline_log_dest_n (1 to 5) (duplex)

parameter file
OFA
oracle Flexible Architecture
is a directory structure

oradim -new -sid test -intpwd password -maxuser 2 -startmode auto -pfile c:=oracle\admin\prod\pfile\init.ora
create tns
syslplus /nolog
connect sys/[email protected] as sysdba

lsnrctl stop
lsnrctl start
tnsping

oradim -delete -sid test

orapwd

============
starting up and shutting down a database
select * from v$database
sqlplus /nolog
sqlplus sabin/[email protected] @a.sql

startup
nomount mount
open read write readonly recover
force restrict pfile=paramenter

shutdown
normal
transactional
immediate
abrot

select name,open_mode from v$database

shutdown normal;

shartdown nomount don’t not open ctrl file redologfile tablespace etc

startup restrected (any dba can connected)
select * from v$pwfile_users;
allow shutdown and start from os

restrict when user’s have session restricted privilased can login

select username from dba_users;

grant restricted session to sabin;

revoke restricted session from sabin;

starup pfile=

shutdown
shudwown after all users logged off no further connections

shutdown transactional when sql execution is finish
shutdown immediate shutdown after all users forcibly disconnted all transacton forcib rollback
shutdown abort terminates the instance

combining startup and alter database
startup nomount
alter database mount
alter database open

Configuration Parameter
textbase
parsed on database statup

binary file
can be altered online
alter system set

set pages 500;
select named,value from v$parameter order by 1
alter system set trace_enabled=true

classifying parameters
DAbase and instance identification
controlfiles
database cahce and i/0
shared memory
server
connection
The optimizer
logs and recovery
rollback and undo
sql * net
diagnostics, tracing and statics

remote_login_passwordfile=EXCLUSIVE

controlfiles
control_files=(“c;\oacle\oradata\prod\control01.ctl”,
“d;\oacle\oradata\prod\control01.ctl”,
“e;\oacle\oradata\prod\control01.ctl”)

db cahce and i/0

db_file_multiblock_read_count=32

db_block_checking=TRUE
shared_pool_size=128 M
shared_pool_reserved_size=4M
large_pool_size=8M rman backup
log_buffer=131072 recycle buffer of memory for redo log

processes=200
job_queue_processes=25

tuning
hash_join_enable=true
query_rewrite_enable=true
query_rewrite_enable=true
query_rewrite_integrity=trusted
star_transformation_enabled=true
parellel_automatic_tuning=true
compibility=9.2.0.1.0

undo_management=AUTO
undo_retention=900

sql * net
dispatcher=”(protocal=tcp) (dispatchers=1) (port=1521)”
shared server=2
circuits=5
shared_server_session=5
max_shared_server=5
max_dispatcher=3

sql_trace=true
max_dump_file_zie=1M
statistics_level=typical

alert_log (very import )

mts multi threaded server

===================
alter database to modify the physical database

starting and shuting database
datafiles
tempfles
logfiles and archives
controlfiles
defaults

alter database default temporary tablespace temp

create temporary tablespace temp2 tempfile
‘e:\oracle\oradata\abc\temp02.dbf’ size 10M
extent management local uniform size 1M;

alter database default temporary tablespace temp2

desc v$datafile;
select file#,name from v$datafile;

alter database datafile “e:\oracle\oradata\prod\tools01.dbf” offline
recover datafile
alter database  rename file “e:\oracle\oradata\prod\tools01.dbf’ to ‘e:\oralce\oradata\prod\rman01.dbf’
alter database datafile “e:\oracle\oradata\prod\rman011.dbf” online

desc dba_data_files

select autoextensible,maxbytes/1024/1024,increment_by,file_name from dba_data_files;

select bytes/1024/1024 ,name from v$datafiles

alter database datafile “e:\oracle\oradata\prod\tools01.dbf’ resize 20M

select name from v$tempfile
select table_name from dictionary where table_name like ‘%TEMP%’;

desc dba_temp_files;
select file_name from dba_temp_files;

select name from v$datafile
union
select name from v$tempfile

alter database tempfile
alter database datafile

control file (standby and backup)
backup control files
alter database backup  controlfile to ‘e:\oracle\oradata\prod\control01.bak’
alter database backup  controlfile to ‘e:\oracle\oradata\prod\control01.bak’ reuse

shutdown immediate
copy of control to different drive
change the paratemer file

to update binaryfile
edit in e:\oracle\ora92\database
abc.ora

startup nomount pfile=e:\oarcle\admin\prod\pfile\init.ora
shutdown immediate
create spfile=’e:\oracle\ora92\database\spfileabc.ora form pfile=’e:\oracle\admin\proc\pfile\init.ora

redo log files
select * from v$logfile
select * from v$log

alter database add logfile ‘e:\oracle\oradata\prod\redo04.log’ size 10M;
alter database add logfile ‘e:\oracle\oradata\prod\redo05.log’ size 10M;

select * from v$logfile

select * from v$log

alter system switch logfile
alter system switch logfile

explain current, active and inactive status

alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;

alter database add logfile ‘e:\oracle\oradata\proc\redo01.log’ size 10M reuse
alter database add logfile ‘e:\oracle\oradata\proc\redo02.log’ size 10M
select * from v$logfile
select * from v$log

duplex log file
alter database add logfile member ‘f:\oracle\oradata\proc\redo01.log’ to group 1
alter database add logfile member ‘f:\oracle\oradata\proc\redo01.log’ to group 2
alter database add logfile member ‘f:\oracle\oradata\proc\redo01.log’ to group 3
alter database add logfile member ‘f:\oracle\oradata\proc\redo01.log’ to group 4
alter database add logfile member ‘f:\oracle\oradata\proc\redo01.log’ to group 5

select * from v$logfile
select * from v$log

select table_name from dictionary where table_name like v$archive%’;

alter system
archive log list
alter system archive log stop;
archive log list
alter sytem archive log start
alter sytem switch log file

alter system flush shared_pool;

desc v$archive_log;
select name from v$archive_log;

Session
desc v$session
select username,sid,serial$,status from v$session;
alter system kill session ’12(sid),37(serial)’;
alter system kill session ’12(sid),37(serial)’ immediate;

alter system set trace_enabled=false;
alter system set db_cache_size=20M
select name,value,isdefault from v$parameter

automated undo and rollback
select name,value from v$parameter;

shutdown immediate
edit text parameter file
comment 3 parameter
rollback_segments(rb00,rb01,rb02,rb03,rb04,rb0,rb05,rb06,rb07,rb08,rb09); create 50
startup pfile=e:\oracle\admin\prod\pfile\init.ora;
select name,value from v$paramenter

create tablespace rbs datafile ‘e:\oracle\oradata\prod\rbs01.dbf’
size 50M autoextend on minimum extent 1M
default storage (initial 50M next 1M minextents 50 maxextents unlimited);

create rollback segment rbtemp tablespace system storage(initial 64k next 64k extents 300);
alter rollback segment rbtemp online

create public rollback segment rb00 tablespace rbs storage (initial 16k next 16K extens 15k);
alter rollback segment rb00 online
repeat for till rb09

select segment_name,status from dba_rollback_segs;

meta dataview
v$performace

mounted mode just attached control file to database
desc v$datafile
desc v$tabalespace;
des v$controlfile_record_sections;
des v$log
desc v$logfile
desc v$archive_log;
select table_name from dictionary

Ddl and logical database(container of data)

controlfile
redologs             /    |           \       Archive logs

|————————-
|           system
|temporary        rollback
|
|   data            index
|
|
————————-
Expanding Database Objects for Database Administration

on the top of the physical database we get Table space

tablespaces(logical)(group datafiles)
|
multiple datafiles in a tablespace(phiysicl)

database object are stored via tablespaces and datafiles
database object and tablespaces are logical structure overlayed in datfile physical structure

Database Objects
Table
Index
cluster
sequence
synonym
view
type
procedure

Tabalespace Management(2 type)
Dictionary(stored as metadata)
Local
desc dba_tablespaces;
select tablespace_name,block_size,status,contents from dba_tablespaces;

select tablespace_name,extent_management,segment_space_management from dba_tablespaces;

db_cache_size=25165824
db_2k_cache_size=4M
db_16k_cache_size=4M

statup pfile=e:\oracle\admin\prod\pfile\init.ora
create spfile=’e:\oracle\ora92\database\spfileabc.ora’ from pfile =’e:\oracle\admin\prod\pfile\init.ora’

shutdown immediate

desc v$tablespace

============
Expanding Database Object for Database Administration
Creating table
Select table space
organized using Index (IOT)
Storge
initi Size 1 MB
netsize 1 Mb
increase size by 0 %
minimun number 1
maximum number unlimeted
umber of transation
initital  1    maximum 255
buffer pool default

Option
Parellel

Caching option
palce frequent accessed data to the top of the bufer cache
monitoring option
Monitoring

Creating altering and dropping indexes(physical aspect of index)

create table actname cluster test(col) as select name from act

undrop table dropped table [as new table];

purge
{table dropped table |index dropped index}
{dba_ recyclebin}
{tablespace tablespace [user user] }};

select * from user_recyclebin;

Partitioning
chuck of physical data space split into multipulte chuk
range partition
list partition
hash partition
composite partition
range-hash
range-list

Partitioning Indexing
Local index

Globale index

partitioning tricks
add drop and truncate
split and merge
rename
move
exchange

Range Partition
Create table rangepartitiontable(
… columns and constraints…)
Partition by range (dte)(
partition dataglp1999 values less than
(To_date(‘2000-03-01′,’YYYY-MM-DD’)) tablespace dataglp1999
,partition dataglp2000 values less than
(To_date(‘2001-03-01′,’YYYY-MM-DD’)) tablespace dataglp2000
,partition dataglp2001 values less than
(to_date(‘2002-03-01′,’YYYY-MM-DD’))Tablespace DATAglp2001
,partition dataglp2002 values less than
(maxvalue) tablespace dataglp2001);

Partition Indexing
create index fkx_glp_1 on glp(coa#) tablespace indx;
create index lk_glp_1 on glp(dte,coa#) local(
partition indxglp1999 tablespace indxglp1999
partition indxglp2000 tablespace indxglp2000
partition indxglp2001 tablespace indxglp2001
partition indxglp2002 tablespace indxglp2002);

List Partition
create table Listpartitiontable(
…. columns and constraints…)
partition by list(type)(
partition purchases values (‘P’) tablespace datatsp
,partition sales values(‘S’) Tablespace datatss);

Hash Partition
create table hashpartition table(
…. columns and constraints..)
partition by hash(order_id) partitions 3
store in(dataol1,datao2,datao3);

range hash composite partition
create table rangehashcompositepartition table(
… columns and constraints..)
partition by range(dte) subpartition by hash(generalledger_id)
subpartitions 4 store in (sp1,sp2,sp3)
(partition dataglp1999 values less than
(to_date(‘2000-01-01′,’YYYY-MM-DD’)) tablespace dataglp1999
,partition dataglp2000 values lessthan
(to_date(‘2001-01-01′,’YYYY-MM-DD’)) tablespace dataglp2000
,partition dataglp2001 values lessthan
(to_date(‘2002-01-01′,’YYYY-MM-DD’)) tablespace dataglp2001
,partition dataglp2002 values lessthan
(maxvalue) tablespace dataglp2002);

range list composite partition
create table rangelist compositepartitiontable(
generalledger_id number not null
,coa# char(5) not null
,dr number(10,2) not null
,cr number(10,2) not null
,dte date not null)
partition by range(dte) subpartition by list(coa#)
(partition dataglp1999 values less than
(to_date (‘2000-01-01,’YYYY-MM-DD’)) tablespace dataglp1999
(subpartition assets1999 values(‘20001′,’20002’);
,subpartition liabilities1999 values(‘10001′,’10002’);
,subpartition expenses1999 values(‘50001′,’50002’);
,subpartition income1999 values(‘60001′,’60002’);
,partition dataglp2000 values less than
(to_date(‘2001-01-01′.’YYYY-MM-DD’)) tablespace dataglp2000
(subpartition assets2000 values(‘20001′,’20002′)….

dba_tables
dba_index
desc user_tables
user_indexes
desc user_clusters;
user_sequences;
user_tab_columns;
user_ind_colums;
user_constraints;

user_cons_colums

set wrap off linesize 132 pages 80;
column pos foramt 990;
column col format a10;
column ind format a25;
column tab format a25;
column typ format a20;
column tbs format a25;

select t.table_name “Tab”
,decode (t.index_type,’NORMAL’,’BTree’,’BITMAP,’Bitmap’,’FUNCTION-BASE NORMAL’,’Function-based Btree’,t.index_type) “typ”
,T.INDEX_NAME “IND”
,C COLUM_NAME “COL”
,c.column_position “pos”
,t.tablespace_name “tbs”
from user_indexes t, user_ind_columns c
where t.table_name =c.table_name
and t.index_name =c.index_name
and t.index_tpe not in (‘IOT – TOP’,’LOB’)
order by t.table_name, t.index_name, c.cloumn_position;

alter index abc rebuild online tablespace indx;

set wrap off linesize 132 pages 80;
column key foramt a10;
column pos format 990;
column col format a15;
column cons format a20;
column tab format a20;
column own format a10;

select decode(t.constraint_type,’P”,’Primary’,’R’,’Foreign’,’U’,’altenate’) as key
,t.table_name “Tab”
,t.constraint_name “cons”
,c.column_name “col”
,c.position “Pos”
from user_constraint t, user_cons_columns c
where t.constraint_type in (‘P’,’R’,’U’)
and t.table_name-c.table_name
and t.constraint_name =c.constraint_name
order by t.table_name, t.constraint_type,c.position;

database security

type os users
Database Administrators
security Administrators
network Administrators
system administrors
application developers
testing
power users
endusers

granting privileges

create user concerts identified by concerts
default tablespace data
temporary tablespace temp
quota unlimited on data
quota unlimited on indx
quota unlimited on objets
quota unlimited on temp;

system=metadata
tables and indexes are separated since both are read concurrently
temp sort space

Dba users
system
sys

Administrator Privileges
as sysdba or sysoper

select * from v$pwfile_users;

revoke sysoper from test
grant sysdba to system;

select * from v$pwfile_users;

sqlplus /NOLOG
connect sys/[email protected] as sysdba
startup

orapwd

privileges
system privileges

REsources and profiles

rosource limits

processor time
allocation                                   profile   userss

i/o amount                       ======
allocation

concurrent sessions                         profiles   usersss
idel time
connection time
shared server
per-parsed sql
space limitation

Database Auditing
Statement Auditing
privilege auditing
object auditing

Scheduling
every 1 second
declare
jobno number;
i integer default 1;
begin
for j in 1..25 loop
dbms_job.submit(jobno,’testproc;’,sysdate,’sysdate+1/86400);
end loop;
commit;
end;
/
select job,log_user “user”,last_sec “prev”, this_sec “curr”,next_sec “next”, failures ,
from user_jobs;

declare
cursor cjobs is select job from user_jobs;
begin
for rjob in cjobs loop
dbms_job.remove(rjob.job);
end loop;
end;

utilities
export help=y
import help=y

sqlldr help-y

sqlldr userid=user/password
discardmax=2 errors=10000000
direct=true
data=data/data.txt control=ctl/data.ctl
log=log/data.log bad=bad/data.bad
discard=dsc/data.dsc

data.ctl
load data
infile ‘data/data.txt’
into table atable
truncate
fields terminated by ” ”
trailing nullcols
( col1 char(32)
,col2(char(128)
,col3 integer external defaultif  duration=blanks
)

value1 value2 0

orapwd help=y

buildin packages
packages
dbms_job
dbms_output

===============================================================
Oracle Certification IZ0-032

Oracle Net Services Architecture
Listener
Connection String and Naming Methods
Oracle Shared Servers
Connection Manager
Oracle Net services Utilities

Types of Backups
Types of Recovery
physical Backups using datafiles
RMan (Recovery manager)

Standy and Replication
Transportable Trablespaces
sql * loader

technet.oralce.com

Transportable Tablespaces
Sql * loader (utilities)
Utilities

Introduction to Oracle Net Services
Server
listner
Server Processes
Client configuratio
Naming Methods
Web & Application Servers

Client/Web Application-Server
Oracle shared Server(MTS)
Large Network configurations
connection manager
oracle Names
ldap
Host and external Naming ? (novel)

Options for Managing Oracle database network
Local and Centralized Naming
Large Networks
Dedicated and Shared Servers
connection Sharing
connection Profiles
Standardizing Configuration

Multiple Listeners
Load Balancing
Multiple Protocols
Connection Cencentration
connection Pooling

Listener Timouts and queue size
SDU Buffer Size
Trobleshooting

Oracle Enterprice Manager
Oracle Agent
The Management Server

Local versus centralzed Naming
Local is taking to listener directly
is controlby tns
transparent Network substrate
Configuration
Simple
listener.ora
tnsnames.ora
sqlnet.ora (profile and information)

Centrilized Naming
Methods
HostNames
External Software (novel)
Oracle Names Server
LDAP
Connection Manager

Configuration
Can be hightly complex
many configuration files
multiple machines
large networks

Simple server configuration
where are the configuration files>
c:\oracle\ora92\network\admin

what are the configuration files?
listener.ora
sqlnet.ora
tnsnames.ora
snmp_ro.ora ( for oracle agent process)
snmp_rw.ora

Oracle Shared Server or (MTS)
sharing of connection
Configuration Parameters

c:\oracle\admin\master(database)
initMaster.ora
#sql * net
dispatchers=*(protocol=TCP) (Dispatchers=1) (port=1521)”
shared_servers=2
circuits=5
shared_server_session=5
max_shared_servers=5
max_dispatchers=3

Connection Profiles sqlnet.ora(standarized connection) this option is overwrited of clients
TCP.NoDELAY=YES
user_DEDICATED_SERVER=yes
sqlnet.expire_time=5
network outage Detection
oracle 10g

Introduction Naming Option

local naming
tns names.ora

Directory Naming (LDAP)
looking in ldap server like dns server

Oracle names
like dns server

hostnames

externaling Names like novel

oracle Connection Manager
connection concentration
funneling or Multiplexing
Multiple Protocol Support
specific configuration
cmadmin (processes)
cmgw

Backup and recovery
Logical backups
export (dump tablespace and schema definition and data)
import (to import)

Pysical Backups
cold backups (shutdown database)
offline
Hot Backups
online (tablespace offline copied individually)
individual tablespaces
RMan
automate locagial and physical backup

standby (physical(read only) or logical(rw))

Replication
oracle repliction
oracle Streams and Advance queuing
Materialzed Views

clustering

Mirroring
Raid Arrays
Operation System

Transportable Tablespaces

sql * loader
=================

Import and exprot
copies of
individual
Tables
schemas
full database (not good_
can be
consistent
tuned
transportable Trablespaces (make use)

Cold Backups
shutdown database
copy
datafiles
controlfiles
archivelogs
Not required
redo logs(used by duplixing)
optional
network files
prarameter file
binary password file

restart Database

Hot Tablespace backup(online)
database is in open
individual datafile is in backup mode(offline) any changed into redo log files
when when done the redo log is writethem back to tablespace and datafile
larger data file longer time
if large slow down
don’t switch all tablespace do one by one
switch one tablespace offline backit up and make it online one by one

redologs are covered by duplixinge
control files are backup using alter database command
copy archive logs

Data files copied individually
changes written to redologs during tablespace backup
when the table space is switch online
changes writen to datafile from redo logs after tablespace backup

RMan
Recovery Manager
Advantages
Complex Management
fewer Human errors
scalability
incremental backup
reliability
incremantal
faster

disadvantanges
planned as oralce standard
requires backup of itself
can use controlfile
repository loss
catastrophic loss
power User Tool(small amount of thinking)

redo covered by duplexing
Archive logs no longer needed are destoryed
only new archives
control file contain simple repository
repositary placed into rman specific database
requires independent offline backup (table space backup mode_
databse resitory stores references and scripting

easier and safer

Recovery

restore copying file in os datafile
recover restore datafile 1 week ago upto
an including all changed made in any of redolog and archive redo logs

taking the datafile and upding all changed that repleid after the was backup made

contol file has pointet to each of the datafile
pointer or changeno for redo and archive logs
if the redo logs has pointer value is which is later than restored data file
then the control file or database knows that data file is restored
is behind in changes with are record in redo logs same applied to archive logs

if poiner value in rodo log is later than datafile then control file is telling database
there is extra changes which should be applied to datafile those changes will be applied until
datafile is uptod date with redologs changes full update

Archive redo log  if changes are so far behind in restored datafile it is possible they could also
retrive from archive redo log

archive logs can be deleted pirior to previous datafile backup

Standby database
standby is to take over failover production database
copy the archive log to standby database
automated
copies log entries
at archiving

9i complex
2 form
physical standby
readonly mode

logical (rw)

physcial standby
Easy to Implement
easto to mange
inaccessible Managed Recovery mode
readonly
self contained
entire primary  database only
copy data physicaly and maintain physically

logical standby
difficult to implement
difficult to mange
readwire capable
not only for backup
not self contained
can add new objec to stadby
cannot change primary objects from standby
erors more likely
can operate on subset of Primary Database
can duplicate schemama not entirely database

Replication
highly complex
duplicating changed into slave
very detailed
difficul to implement
innappropriately used
Distribution of processing
not backup & recovery

type of replication
one direction only
master and slave one way

unidirectional
master to master

Replication by other Means
oracle streams
pipline between 2 database
database
>
apply queue
^
oracle stream
^
capture queue
^
database

Advance queuing
Master to Master capable
multiple daabase capable
distributed Enviromenents
Materialized views (snapshot)
one way
master to slave only
Manually using Coding
scary

Transportable Tablespaces

tablespace can be transport one database to another (same version)

copy datafile of tablespace and copying to new database
logal export and import utility to copy itt
unpluging from one database and pluging to another database

sql * loader
utitily to rapidly take falt file or external define file
and pass them using control files applyng and formating
syntax to data in flat and external file into database
no need to use dml

fata data load
pre-configuration and automation
very large amount of data

data warehouse
full command line syntax copability
oracle enterprise manger
special loading
direct appending
parrell processing
external table and flat files

Oracle Net Service Architecture
Open Systems Interconnection Model
Oracle’s OSI Model
Protocols
communication
dedicated and shared connections
shared server processes and dispatchers

the OSI Model
www.iso.org

oracle osi Model
client stack
Clinet Application Layer
oci(oracle call interfce) define server call ,syntax parsing validation ,squel curor, variable binding,execute sql statemnt, fetch it’s rows and close the cursor,or as single or group of messages like procedure
TTC(two task common) datatype converstion
net8 layer (session,transport) network interface,network naming/ network session,network route,network Authentication)
oralce Protocal Adapter
oralce Protocal Adapter(tcp,sll, spx namedpipes )
Netork specif Protocols(physical layer)

server stack
opi(oracle program Interface)(exec request,prarse, format,translate the restult to oci later)
TTC (two task common) data type conversion
net8 layer (session,transport) ntwork interface,network naming/ network session,network route,network Authentication)
oralce Protocal Adapter(tcp,sll, spx namedpipes )
Netork specif Protocols(physical layer)

Protocols
TCP/IP
Bequeath(same server and client)
Others
sPX,Named piped,

Dedicated and shared Connections
Server Processes
Dedicated

shared
overide from client

Dispatchers
the dispatch messages and request user shared server process

The listener

internet users
|
dispatcher
/            \
Sshared server proc        shared server processes
\             /

Database
/          \
dedicated         deicated
/              \
client         client

dedicated and shared server are server processes

listener sit between client and server processing

shared server process and dispatchers

========================================
tnsnames.ora
tnsping sabin

profile parameter file
slnet.ora
NAME.directory_PATH= (TNSNames,Onames)

Basic Databse configuration Parameters
dispatchers=”(Protocol=tcp) (dispatchers=1) (port=1521)”
shared_servers=2
circuits=5
shared_server_sesson=5
max_shared_server=5
max_dispatcher=3

Listener
over network
for request
Prasses request to
server process
dispatchers
Possible changes
queue size
logigng and tracing
multiple listners
loadbalancing
timeouts
SDU buffer

lsnrctl
help
status
stop
start
services

show
show trace level
show log_status

to disbale logging
in listener.ora

logging_listener = off
trace_level_listener = off
connect_timeout_listener = 30

logging_listener1 = off
trace_level_listener1 = off
connect_timeout_listener1 = 30

lnsrctl
start listener
start listener1

loadbalacing
failover(taf)
(Load_balance=yes)
failover=yes

SDU Buffer
session data unit buffer
buffer flushed to network when full
(big transton large buffer samall transtion low buffer)512byte 32kb
(SDU= 32768)

connecton profile configuration
oracle_home/network/admin/sqnet.ora
profile apply to all users
spceif to client computer

priorize Naming
logging and tracing
routing
external naming
advaced security features

Connecton String and naming methods

connect <username?/password @
(description=(protocol=tcp)
(host=<hostname>(port=1521))
connect_data=
(service_name=PROd.xyz.com)))

Naming Methods
localnaming
host naming
external naming
ldap
oralce naming(onames.ora)

oracle Shared Servers
multithreaded server(mts)
shares server processes
connections share proceses
allocates using dispatcher processes

Introuction Dispatchers
Data base configuraiton prameters
the most Important Ones
dispatchers
shared_servers

Dispatchers=”(Protocol=TCP)(DIspatchers=1)(port=1521)))
shared_servers=2
circuits=10 (buffer )
shared_sever_session=10
max_dispatchers=3
max_shared_servers=5

Dispatchers=”(Protocol=TCP)(DIspatchers=1)(port=1521)(queuesize=50)))
(session=500)
(connections=500)
multiplex=on)
(pool=on)
(tick=2)(in=10)(out=15)”

tnsname
(service_name=test)(server=shared)

rman requies dedicatied connection

Connectoin Manager
Router
connects sent to next address
firwall
filtering of connection request

connecion concentration
oralce shred servers

Access control

Multiple protocol support
multiple network Integration

2 process
cmgw
gateway process
listens from client connection request
cmadmin
administrative process

Connection concentration
funneling or multiplexing
saves server resources
multiple connection between process

Dispatchers=  (multiplex=on)

Multiple protocol support
communicate between incompatible
network and protocols

<tnsname>=
(description =
(source_route= yes)    sprohibits user of load balacing and failover
(Address_LIST =
(Address = (Protocol =spx) (service =cman))
(Address = (Protocol =tcp) (host=hostname )(port=no)

network/admin/cman.ora

cman=
(Address_list =
(Address=(protocol=tcp)(host=hostname)(prot=no))
(Address=(protocol=tcps)(host=hostname)(prot=no))
(Address=(protocol=spx)(host=hostname)(prot=no))
)

cman_admin

cman_admin=
(Address=protocol=tcp)(host=hostname)(prot=1830))

cman_rules

cman_rules =
(rule_list =
(rule = (src=hostname)(dst=hostname)(srv=sid)(act=accept|reject))

(rule = (src=hostname)(dst=hostname)(srv=sid)(act=accept|reject))

)

cman_profile
cman_profile=(parameter_list =(…))

netservices and utilities
tnsping
trcsess
trcroute
lnnrctl
namectl
cmctl
oracle enterprise manger
agentctl
dbsnmp

oracle net services Advanced Features
Transparent application failover
runtime failove option

desc v$session
username
failover_type
failover_method
failed_over

taf parameters
backup
failover tns name
delay
connection attempt delay

method
basic
preconnect
minimum failover time
type
non
session
reconnects
select
recovers selects

retries
failover connection attempts

configuration taf
taf can failover to another listener
transparent and seamless
connection /session uninterrupted
not the default
default is no select recovery
overhead
tnsnames.ora
failover_mode parameter

primary=
(description_list =
(failover=true) (load_balance=yes)
(description =
(address=
(protocol =tcp)(host =hotname)(port=1521))
(connect_data = (service_name=sid)(server=dedicated)
(failover_mode = (type=select)(method=preconnect)(backup=failover)
(retries=20)(delay=30))))
(description=
(Address =
(protocol=tcp)(host=hostname)(port=1522))
(connect_data =(service_name=sid) (server=dedicated))))

failover=
(description=
(Address =
(protocol=tcp)(host=hostname)(port=1522))
(connect_data =(service_name=sid) (server=dedicated))))

external procedures(c,c++,java)
heterogeneous services
other relational databases
transparent gateways
databaselinks

sybase
ingres
informx
sql server
db2

Oracle net services metadata
Performance view

shared servers
v$shared_server
v$queue
v$shared_server_monitor

dispatchers
v$dispatcher
v$queue
v$dispatcher_rate

virtual circuits
v$circuit

===========================
Backup and recovery

backup
restroation (file copy)
recovery

why backup
protect database
system failure
partial
catastrophic(entrie)

reconstruction
recovery
complete
partial
scn
point in time

thing to rember
recover as as backup
atleat

local versus physical
logical simpler
incomplete
can be slow
physical more complex
restore and recovery
much faster
complete

thing to consider
split to separate disk
datafiles
redo logs
controlfile
archive logs

redo logs
select * from v$log
select name,open_mode from v$database;
select * from v$logfile;

alter database add logfile member ‘c:\oracle\oradata\test\redo01b’ to group 1
alter database add logfile member ‘c:\oracle\oradata\test\redo02b’ to group 2
alter database add logfile member ‘c:\oracle\oradata\test\redo03b’ to group 3
alter database add logfile member ‘c:\oracle\oradata\test\redo04b’ to group 4
alter database add logfile member ‘c:\oracle\oradata\test\redo05b’ to group 5

shutdown immediate
startup mount pfile=’c:\oracle\admin\prod\initprod.ora’;
creat pfile=’ ‘ from file ‘ ‘;
shutdown immediate
startup

alter system switch logfile;

control file
shutdownd database
copy 2 control file
edit parameter file
add 2 ctrl file
startup mount pfile=’c:\oracle\admin\prod\initprod.ora’;
creat pfile=’ ‘ from file ‘ ‘;
shutdown immediate
startup

archive log
select name,value from v$parameter order=1;

log_archive_dest_1

multipex controlfiles
duplex redo logs
unrecoverable /nologging
ricky
backup prudent
Automate backup and recovery
procedures
scripting
tools
rman
scheduling
rotate tapes

primary backup
on database server
rapid restore
minor error
secondary backup
tape
cdrom
disk elsewhere
use stanby,Replication or clustering

imporat of backup and recovery
potential data loss
minor
castrophic

backup and recovery
planning
potential restore and recovery
strategy
backup
cater for fast recovery

points of failure
media failure
fare

user/appliatoin error
most lekely

oracle failure
session termination
database shutdown

alter system enable restricted session;
sid,serial#,username v$session
alter system kill session “11,25”;

how bckup and recovey works

check point
alter system checkpoint;
flush dirty block  to data buffer cache to datafile

when backup to online alter checkpoing

The controlfile
contis set of pointer to datfile archivelogs datafiles

datafile header will contain scns
and control file with contail scn for each data file

if scn datfile is behind the control file is not uptodate with
archivelog and redologs and updates using scn form archive logs and redo logs
redo contain the last scn and matche the scn and redo logs

older copies of redolog is archivelogs

SCN (System change number)
is changed happend in database has sequencle number
each datafile in its header contains scn to it self
control file contains scn reference to each datafile

if datafile scn is behind the control file
then process of recovey goes to redolog file and archive logs
and replythem with datafile headr and scn

Basic archive configuration parameters

log_archive_dest_1= location=”c:\oracle\oradata\vtc\archive’
log_archive_format=%t_%s.dbf (t= threadno s for sequence)
log_archive_start=true
remote_archive_enable=true
log_parallelism=1

archive_lag_target
log_archive_trace
log_parallelism (datamining)
fast_start_mttr_target

exmine database archive status
archive log list;

switching off
log_archive_start=false
and recreate the binary file

shutdown immediate
connect sys/password$test as sysdba
startup mount pfile=”
create spfile=
alter databse archivelog;
startup

shutdown immediate
startup mount
alter databale archivelog; ==in mounted mode
shutdown immediate
statup

alter database noarchivelog

alter system switch logfile;== mounted mode

v$arvhivedlog

multiple archvie processes
log_archive_max_processes=2

v$archive_processes;

alter system set log_archive_max_processes=3;

Multiple Archive Destination
log_archive_dest
log_archive_duplex_dest
log_archive_min_succeed_dest
log_archive_dest_{1..10}
log_archive_dest_state_{1..10}
standby_archive_dest

Standby Archiving
primary database
standby database
multiple standby  database
remote database

primary database parameters

log_archive_deskt_1=’location=c:\oracle\oradata\prim\archive Mandatory reopen=30′
log_archive_dest_state_1=enable
log_archive_dest2=
‘service=stby lgwr sync=parallel affirm’
log_archive_dest_state2=enable
log_archive_dest3=
‘service=failover arch affirm’
log_archive_dest_state_3=enable
log_archive_min_succeed_dest=3
remote_archive_enable=true

standby_archive_dest=
‘e:\oracle\oradata\sid\archive’
log_archive_dest_1=
‘location=e:\oracle\oradata\sid\archive’
remote_archive_enable=true

export import
buffer
(read process of reading and wriding)
10240

exp system/system files=c:\a.exp log=c:\a.log full=y buffer=1024 direct=y
imp system/system files=c:\a.exp log=c:\aa.log full=y buffer=1024

exp employees/[email protected] file=c:\test.exp log=c:\a.log buffer=10240 direct=y consistent=y

create tablespace data datafile ‘c:\oracle\oradata\prod\data01.dbf’ size 10M autoextend on;
create tablespace objects datafile ‘c:\oracle\oradata\prod\objects01.dbf’ size 10M autoextend on;

select tablespace_name from dba_tablespaces;
drop user employees cascade;

create user employee identified by employees
default tablespace data
temporary tablespace temp
quota unlimited on data
quota unlimited on indx
quota unlimited on objects
quota unlimited on temp;

grant connect,resource to employees;
grant unlimited tablespace to employees;
grant query rewrite to employees;

imp system/[email protected] file=c:\tmp\employees.exp
log=c:\a.log fromuser=employees touser=employees buffer=10240 commit=y

exp system/[email protected] owner=(books,abc,xy) file=c:\a.dmp
log=c:\a.log buffer=10240 direct=y consistent=y

create 2 user
book and abc

imp system/[email protected] file=c:\tmp\employees.exp
log=c:\a.log fromuser=books touser=books buffer=10240 commit=y

create user booking
imp system/[email protected] file=c:\tmp\employees.exp
log=c:\a.log fromuser=books touser=booking buffer=1024. commit=y

physical backup , restore and recovery
at operating system level
shutdown database
copy
datafiles
controlfiles
archive logs

not required
redo logs

optional
configuration files
password files

restart database

connect sys/[email protected] As sysdba
shutdown immediate
copy datafile , controlfiles, redo logs and archive logs
no need to copy temp
startup

can also copy parameters, password file and all network configuration

hot backup
data files are copied individualy
control file are copyed using alter database
no need to copy redologs

connect sys/[email protected]@ sysdba
alter tablespace data begin backup
$copy c:\oracle\oradata\test\data01.dbf c:\tmp
alter tablespace data end backup
alter database backup control file to ‘c:\oracle\data\test\control01.bak’ reuse
alter database backup controlfile to trace

copy archive logs
can also copy parameter password file and all network configuration

restore
dbv help=y
dbc file=c:\oracle\oradata\test\data01.bak blocksize=8192 logfile=c:\a.log

try to find the below error
total pages failing
total pages marked corrupt
total pages infux

#!/bin/sh
USAGE=”$0: incorrect argument, Use $0 <sid>”

if [ `whoami` !- ‘oracle’ ]; then
echo Aborted -u `whoami` is incorrect, must be user oracle
exit 1
elif [ -z “$1” ]; then
echo “$usage”
exit 1

else
echo executing .. perl beforRestore.pl $ORACLE_BASE/oradata/$1 $ORACLE_HOME
perl beforeRestore.pl $ORACLE_BASE/oradata/$1 $ORACLE_HOME
perl beforeRestorelOGS.pl $ORACLE_BASE/oradata/$1 $ORACLE_HOME
fi

beforeRestore.pl
$path = shift;
$ORACLE_HOME = shift;

system(“ls -a $path/*.dbf > ./list”);
open(IN,”./list”);

while(<IN>)
{
chomp($_);
@name = split(“\/”,$_);
$names = $name;
$filename = $name[$name-1]; chomp($filename);
print “$ORACLE_HOME/bin/dbv file=$_ blocksize=8192 logile=./log/$filename.log\n”;
system (“$ORACLE_HOME/bin/dbv file=$_ blocksize=8192 logile=./log/$filename.log”);
}
close(IN);

beforeRestoreLogs.pl

$path = shift;
$ORACLE_HOME = shift;

system(“ls -a $path/*.dbf > ./list”);
open(IN,”./list”);

while(<IN>)
{

chomp($_);
@name = split(“\/”,$_);
$names = $name;
$filename = $name[$name-1]; chomp($filename);

if (open(file,”./log/$filename.log”) == 1)
{
while(<file>
{

chomp($_);
if ($_ =~ /^Total Pages Failing.*: (\d*)$/i { if (int($1) != 0) { print “$filename, $_\n”; } }
if ($_ =~ /^Total Pages Marked Corrupt.*: (\d*)$/i { if(int($) != 0){ print “$filename, $_\n”; } }
if ($_ =~ /^Total Pages Influx.*: (\d*)$/i { if(int($) !=0 ){ print “$filename, $_\n”; } }
}
close(file);
}
}
close(IN);

Restore
in cold physical database (database snapshot)
conect sys/[email protected] as sysdba
shutdown immediate
restore and copy the datafiles,controlfile,archivelogs back in os
startup

in restore hot backup

alter database datafile ‘c:\oracle\oradata\test\data01’ offline;
alter database data offile
dba_tablespaces

to restore simply copy files back and recover usin(archive and datafiles)
to take effet

–alter database recover database;
–alter database recover tablespace data
alter database recover datafile ‘c:\oracle\oradata\test\data01.dbf’;
alter database datafile ‘c:\oracle\oradata\test\data01’ online;
alter tablespace data online

— partial recovery recovers parts of a database such as a single datafile or tablespace or database
when requies recovery will be detected by the controlfile besed on scn no
then the missing redo log entries are re=executed in what ever datafiles and updated from archivelog
both redo log file and archive log files will be used if so required…

Read only Tablespace cannot be changed
never changed
simply copy
static tables object

incomplete Recovery (recover till centrain time)
time based
cancel based
changebased
rman
log sequence number

recover database
[until
{time date
|change scn
|cancel }
];

Automation and scability

what is rman
utility
backup
recovery
commandline
oracle entriprise manager

Advantage
complexity management
fewer human errors
scabilitity (incremental backup)
reliability (fully tested)
incremental
faster

Repository and catalog
repository is used store the record of backup
catlog add one piece of guddies like scripts
repository resign in ,controlfile on target database

control file
on target database
backup only
repository
no scripting
limited automation
risck of loss
sperate
catalog and repository
schema
database

catalog and repository
repository goes into controlfile
it store all the backup of record
it also has catlog stored in seperate schema database or exta database server

catlog allow the complete record of all back to
ability to stop scriptring and execute automatically
within rman and recovery manager
manage multiple database from single recovery manager repository

The recovery catalog
store in seperate database
backup, records with a controlfile
catlog basically includes both the history of all
backup sessions references backup plus genera
meta data views and perforamtion inforamtion into that backup
plus the scripts which can be automated

The Recovery Catalog
all backups
references
dates
set membership
Strogae
control file
Rman repository
any databse
independent database
auto mation
scription
cannot backup itself

Rman Architecture
rman executed from os
need deticated connection
rman has seperate database

and can maintain standby and duplicaton

waht is a channel
device or connect to device to allow copying or bakupand recovry
connection to the device to do backup and recovery

allocate channel to the process

allocating a channel
run
{
allocate channel ch1 type disc
sql ‘alter system archive log current’;
backup database filesperset2;
format $ORACLE_DBF/backup/database/%U’;
tag=’database’;
release channel ch1;
allocate channel ch1 type disk;
backup current control file
tag=’post daabase controlfile’;
release channel ch1;
}

automated in 10g

Creating a repository

Create a database
The rman tablespace
the rman user
privileges
the rman catlog

new database
rman
remove exta option
no archivemode
(datafile) undotbs01.dbf filezie=20

create the rman tabalespace
conncect sys/[email protected] as sysdba;
create tablespace rcvcat datafile ‘c:\oracle\oradata\rman\rcvcat01.dbf’
size 10M autoextend on;

create user rman identified by rman
defualt tablespace rcvcat
temporary tablespace temp
quota unlimited on rcvcat

grant sysdba,sysoper,dba,recovery_catlog_owner,
connect,resource, select any table to rman;

select username,default_tablespace from dba_users;
select tablespace_name,file_name from dba_data_files;

from shell
rman
connect target
connect catalog rman/[email protected]
connect target system/[email protected]
rman catalog rman/[email protected] @createCatalog.sql

form the shell command line
rman target=”sysem/[email protected]” nocatalog
rman target=”sysem/[email protected]” catalog=”rman/[email protected]
rman target / nocatalog
rman target / catalog=”rman/[email protected]

rman target / nocatalog rman/[email protected]

can connect 3 database at onces

registering database to repository

dbmust be started
rman target=”system/[email protected]” catalog=”rman/[email protected]
register database;

rman target=”system/[email protected]″ catalog=”rman/[email protected]
register database;

rman target=”system/[email protected]” catalog=”rman/[email protected]
register database;

unregistering database
connect rman/[email protected];
select * from db;
connect sys/[email protected] as sysdba
select dbid,name from v$database;
execute dbms_rcvcat.unregisterdatabase (DB_KEY,DB_ID);

rman target=”system/[email protected]” catalog=”rman/[email protected]

viewing the repository
list command
list { [expired] backup [by file | summary ] | [expired] copy | archivelog all};

list backup
list backup summary
list expired copy;
list archivelog all;

report need backup
report unrecoverable database;

crosscheck { backup | copy };

connect sys/[email protected] as sysdba
select dbid,name,open_mod from v$database;

connect rman/[email protected];
select * from db;
select * from rc_database;

Backup Sets
set of file
within a backup
allows for restaring

backup {databse filesperset=2
archivelog all filesperset=5

up to 2 datafiles per backup set
up to 5 archive log files  per backupset

backup maxsetsize=10M archivelog ALL;

types of backups
Unit backups
controlfiles
datafiles
tablepaces
archivelogs
binary parameters file
Database Backups
Incremental

Unit backup(pieces backup)
rman target=”system/[email protected]” catlog=”rman/[email protected]

controlfiles
backup current controlfile;
backup current controlfile tab ‘one’;
list backup of controlfile;

rman target=”system/[email protected]” catlog=”rman/[email protected]
backup current controlfile;

rman target=”system/[email protected]” catlog=”rman/[email protected]
backup current controlfile;

rman target=”system/[email protected]” catlog=”rman/[email protected]
–datafiles
–connect sys/[email protected] as sysdba;
select file_id,tablespace_name,filename from dba_data_files;

rman target=”system/[email protected]” catlog=”rman/[email protected]
backup datafile 1,2,5,8,10,11 filesperset 2;
list backup of datafile 10;

connect sys/[email protected] as sysdba;
select file_id,tablespace_name,filename from dba_data_files;

rman target=”system/[email protected]” catlog=”rman/[email protected]
backup datafile 1,2,5,8,10,11 filesperset 3;

connect sys/[email protected] as sysdba;
select file_id,tablespace_name,filename from dba_data_files;

rman target=”system/[email protected]” catlog=”rman/[email protected]
backup datafile 10;

rman target=”system/[email protected]” catlog=”rman/[email protected]

connect sys/[email protected] as sysdba;
select tablespace_name from dba_tablespaces;

backup filesperset=2 tablespace system,indx,users,data,objects;
list backup of tablespaces data;
backup filesperset=2 tablespace system,indx,users,data,objects include current controlfile;
backup tablespace data include current controlfile;
list backup of tablespace data;
list backup of controlfile;

connect sys/[email protected] as sysdba;
select tablespace_name from dba_tablespaces;
rman target=”system/[email protected]” catlog=”rman/[email protected]
backup filesperset=2 tablespace indx,users,data,streams;

connect sys/[email protected] as sysdba;
rman target=”system/[email protected]” catlog=”rman/[email protected]
backup filesperset=2 tablespace system,oem_repository;

rman target=”system/[email protected]” catlog=”rman/[email protected]
backup archivelog all
–delete all copies on disk
–backup archivelog [all delete all input ];
–backup archvielog [ from time ‘sysdate-1 until time ‘sysdate’ ];
list backup of archivelog all;

–oemrep is not archived
–connect sys/[email protected] as sysdba;
–shutdown immediate
–startup mount;
–alter database archivelog;
–mkdir
–copy archive parameters using alter system

–rman target=”system/[email protected]” catlog=”rman/[email protected]
backup archivelog all;

–rman target=”system/[email protected]” catlog=”rman/[email protected]
backup archivelog all;

backup binary parameter file
rman target=”system/[email protected]” catlog=”rman/[email protected]
backup spfile;
rman target=”system/[email protected]” catlog=”rman/[email protected]
backup spfile;
rman target=”system/[email protected]” catlog=”rman/[email protected]
backup spfile;

–rman target=”system/[email protected]” catlog=”rman/[email protected]
–control file and spfile automatically included with backup database
backup database;
backup database format ‘c:\tmp\%U’ tag=’whole database’;
–show files in c:\tmp directory

validation prior to backup (like dbverify, no backups created just vrication
backup validate database

–alter system switch logfile;
–backup database plus archivelog;
–backup validate database archivelog all;
–list backup of database;
–list backup of database summary;

–restarting a backup
backup not backed up since ‘SYSDATE-1′ DATABASE PLUS archivelog;

Incremental Backups
writes only changed blocks
reads all of datafile
function in layers
levels
levels 0 base level
automatic if none exits
basis of all subsequent backups
level1 backup
all blocks changed after last level 1
level2 backup
all blocks changed after last level 2

level 1 over writes 1 level2 overwirtes level2
Monly: level 0
weeky level 1
daily: level 2

Differentail and cumulative

differential
since most current level
cumulative
since most current level -1

–rman target=”system/[email protected]” catlog=”rman/[email protected]
monthly
backup incremental level=0 database;

weekly
backup incremental level=1 tabalespace system,indx,users,data,objects tag=’weekly’;

–daily
backup incremental level=2 tablespace user,data tag=’daily’;

list backup of database;

–weekly
backup incremental level=2 cumulative database;

Restore and Recovery
Unit (partial) recovery
Incomplete Recovery
UNTIL TIME
UNTIL Sequence
validation

Option
Restore
to Difference locations
enven a different host
individual corrupted blocks
create
duplicate databases
standby dtabases

–rman target=”system/[email protected]” catlog=”rman/[email protected]

–connected to target
shutdown immediate;
startup mount;

–veryfy target database mode
–connect sys/[email protected] as sysdba;
select name, open_mode from v$database;

–rman target=”system/[email protected]” catlog=”rman/[email protected]
restore database;
recover database;
alter database open;

–verify target database mode
–connect sys/[email protected] as sysdba;
select name,open_mode from V$database;

–partial restoration and recovery
–connect sys/[email protected] as sysdba;
alter tablespace data offline immediate;
–rman target=”system/[email protected]” catlog=”rman/[email protected]
restore tablespace data;
recover tablespace data;

–connect sys/[email protected] as sysdba;
alter tablespace data online;

incomplete recovery

–connect to target
shutdown immediate;
startup mount;
–rman target=”system/[email protected]” catlog=”rman/[email protected]
restore database until time ‘sysdate’;
recover database until time ‘sysdate’;

–connected to target
alter database open resetlogs;

–validate backup without restore or recovery
restore database validate;

Scripts and Rman

must use catalog
nocatlog
using controlfile

scripts
create
replace
delete
execute

–rman target=”system/[email protected]” catlog=”rman/[email protected]
create script monthly
{
backup database plus archivelog;
delete obsolete;
}
delete script ‘monthly’;

create script monthly
{
backup incremental level=0 database plus archivelog;
delete obsolete;
}

create script weekly
{
backup incremental level=1 tablespace system,indx,users,data,objects;
delete obsolete;
}

create script daily
{
backup incremental level=2 tablespace user,data;
delete obsolete;
}

run { execute script daily; }

replace script daily
{
backup incremental level=2 tablespace users,data,objects;
delete obsolete;
}
run{ execute script daily; }

deleting rman backups
list backup of database archivelog all

BP KEY: 752

–rman target=”system/[email protected]” catlog=”rman/[email protected]
report obsolete;
delete obsolete;

crosscheck backup
delete expired;
delete archivelog all;

during backup
backup archivelog all delete input;
list copy;

allocate channel for maintenance device type disk;
list backup
delete backuppiece 752;
delete backuppiece 768,778,554,214;

list backup of database archivelog all;

–the repository should now be emply of all backups
connect sys/[email protected] as sysdba;
drop user rman cascade;
drop tablespace rvcat including contents;

–show file in c:\tmp directory dissapered!

Leave a Reply