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 sys@s 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 sys@intserver @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/password@test 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/hello@test @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/test@test 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/employees@test 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/system@vtc file=c:\tmp\employees.exp
log=c:\a.log fromuser=employees touser=employees buffer=10240 commit=y
exp system/system@test 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/system@vtc file=c:\tmp\employees.exp
log=c:\a.log fromuser=books touser=books buffer=10240 commit=y
create user booking
imp system/system@vtc 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/password@test 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/system@test@ 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/password@test 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/password@rman 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/rman@rman
connect target system/system@vtc
rman catalog rman/tmp@rman @createCatalog.sql
form the shell command line
rman target=”sysem/system@vtc” nocatalog
rman target=”sysem/system@vtc” catalog=”rman/rman@rman”
rman target / nocatalog
rman target / catalog=”rman/rman@rman”
rman target / nocatalog rman/rman@rman
can connect 3 database at onces
registering database to repository
dbmust be started
rman target=”system/system@test” catalog=”rman/rman@rman”
register database;
rman target=”system/system@test1″ catalog=”rman/rman@rman”
register database;
rman target=”system/system@oemrep” catalog=”rman/rman@rman”
register database;
unregistering database
connect rman/rman@rman;
select * from db;
connect sys/password@test as sysdba
select dbid,name from v$database;
execute dbms_rcvcat.unregisterdatabase (DB_KEY,DB_ID);
rman target=”system/system@vtc” catalog=”rman/rman@rman”
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/password@vtc as sysdba
select dbid,name,open_mod from v$database;
connect rman/rman@rman;
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/system@test” catlog=”rman/rman@rman”
controlfiles
backup current controlfile;
backup current controlfile tab ‘one’;
list backup of controlfile;
rman target=”system/system@master” catlog=”rman/rman@rman”
backup current controlfile;
rman target=”system/system@oemrep” catlog=”rman/rman@rman”
backup current controlfile;
rman target=”system/system@test” catlog=”rman/rman@rman”
–datafiles
–connect sys/password@test as sysdba;
select file_id,tablespace_name,filename from dba_data_files;
rman target=”system/system@test” catlog=”rman/rman@rman”
backup datafile 1,2,5,8,10,11 filesperset 2;
list backup of datafile 10;
connect sys/password@master as sysdba;
select file_id,tablespace_name,filename from dba_data_files;
rman target=”system/system@master” catlog=”rman/rman@rman”
backup datafile 1,2,5,8,10,11 filesperset 3;
connect sys/password@oem as sysdba;
select file_id,tablespace_name,filename from dba_data_files;
rman target=”system/system@oem” catlog=”rman/rman@rman”
backup datafile 10;
rman target=”system/system@test” catlog=”rman/rman@rman”
connect sys/password@test 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/password@master as sysdba;
select tablespace_name from dba_tablespaces;
rman target=”system/system@master” catlog=”rman/rman@rman”
backup filesperset=2 tablespace indx,users,data,streams;
connect sys/password@oemrep as sysdba;
rman target=”system/system@oemrep” catlog=”rman/rman@rman”
backup filesperset=2 tablespace system,oem_repository;
rman target=”system/system@test” catlog=”rman/rman@rman”
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/password@oemrep as sysdba;
–shutdown immediate
–startup mount;
–alter database archivelog;
–mkdir
–copy archive parameters using alter system
–rman target=”system/system@oemrep” catlog=”rman/rman@rman”
backup archivelog all;
–rman target=”system/system@master” catlog=”rman/rman@rman”
backup archivelog all;
backup binary parameter file
rman target=”system/system@test” catlog=”rman/rman@rman”
backup spfile;
rman target=”system/system@oemrep” catlog=”rman/rman@rman”
backup spfile;
rman target=”system/system@master” catlog=”rman/rman@rman”
backup spfile;
–rman target=”system/system@test” catlog=”rman/rman@rman”
–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/system@test” catlog=”rman/rman@rman”
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/system@test” catlog=”rman/rman@rman”
–connected to target
shutdown immediate;
startup mount;
–veryfy target database mode
–connect sys/password@test as sysdba;
select name, open_mode from v$database;
–rman target=”system/system@test” catlog=”rman/rman@rman”
restore database;
recover database;
alter database open;
–verify target database mode
–connect sys/password@vtc as sysdba;
select name,open_mode from V$database;
–partial restoration and recovery
–connect sys/password@vtc as sysdba;
alter tablespace data offline immediate;
–rman target=”system/system@test” catlog=”rman/rman@rman”
restore tablespace data;
recover tablespace data;
–connect sys/password@test as sysdba;
alter tablespace data online;
incomplete recovery
–connect to target
shutdown immediate;
startup mount;
–rman target=”system/system@test” catlog=”rman/rman@rman”
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/system@test” catlog=”rman/rman@rman”
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/system@test” catlog=”rman/rman@rman”
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/password@rman as sysdba;
drop user rman cascade;
drop tablespace rvcat including contents;
–show file in c:\tmp directory dissapered!