Locks
If two user trying to access same table
records means system will get hang
To avoid this only we need locks
To see the locks
SQL > select * from v$locks where
id1=51966
Note : to get the above id use below given
query
SQL > select object_id from user_objects
where object_name=’table’
Two types of locks
- DML lock
- DDL lock
Shared lock
SQL > lock table <name> in share
mode – Will allow only read any other DML tried session will be locked. Once
commit or rollback is issued only lock will get released
Exclusive lock
SQL > lock table <name> - only one
user can use this command in a table
Share update mode
Used for row level locking
SQL > lock table <name> in share
update mode
DDL lockc
Is nothing but foreing key, when there is
relationship you cannot deleted parent table
Storage
structure & relationship
Segments
– Which ever database objects occupies physical memory called as segments
Types of segqments
1. table
2. Table
property
3. Customer
4. Index
5. Index
organized segment
6. Index
partition
7. Undo
segment
8. Temporary
segment
9. LOB
segment
10.Nested
segment
11.Bootstrap
segment
To see how many blocks used
SQL > select * from dba_segments where
segment_name=’TBX’
To see in detail
SQL > select * from dba_extents where
segment_name=’TBX’
Note : when you delete row,column or
truncate table those extents are automatically de-allocated
To see default segment used
& free space percentage
SQL > select pct_used,pct_free from
dba_tables where table_name=’TBX’
To use the manually used space
SQL > alter table <name> pctused
80;
To see all the tablespace
SQL > desc dba_tablespace
Note : From 10g only auto segment features
are added
Creating table in a tablespace
SQL > create tablespace <tbs>
datafile ‘d:\..\..\file.dbf
SQL > create table <name> (col1)
tablespace <tbs>
SQL > select pct_used,pct_free from
dba_tablspace where table_name=’name’
SQL > select
tablespace_name,segment_space_management from dba_tablespaces;
Managing
tablespace & Indexes
Which will be useful for data migration.
To see which tablespace table is
stored
SQL >
select tablespace_name from dba_tables where table_name=’TB_SAMPLE’ and
owner=’sys’;
To see the datafile which used
SQL > select name from V$datafile where
TS#=0
To prealocate extend to index
SQL > alter index <name> allocate
extend ‘datafile d:\oracle\...oradata\orcl\system01.dbf’ size 100k;
TO deallocate allotted extend
SQL > alter table/index <name>
deallocate unused
To see how extends are allocated
SQL > select * from dba_extents where
segmenr_name=’table’;
To move table/ index to another
tablespace
SQL > alter table/index <name> move
tablespace <new tablespace>
Creating global temporary table
In which all record will be de allocated
once transaction is commited
SQL > create global temporary
<table> as select * from <table>
Table integrity
Deferable – Data stored &
integrity/constraint only checked at the time of DCL
SQL > alter session set constraint =
‘derrered’
To see deferrable
SQL
> select constraint_name,deferrable,deferred from dba_constraint
table_name=’table’
Export & import utilities
TO
Export Users & tables
Start
- > Run - > Cmd ;/>SET ORACLE_SID = ORCL
Cmd:/>
EXP system/oracle owner=scott file=d:/bkp.dmp [rows=n]
Note
: rows = n – to copy only structure with out data
(or)
Cmd:/> exp system/oracle – To customized
copy
Cmd:/> exp system/oracle owner=scott,HR
file=d:/bkp.dmp rows =n – To copy two users tables & other things
Cmd:/> exp system/oracle
tables=scott.table file=d:\fil-bkp.dmp
Import
SQL > select user <name>
identifiedby <pwd>
SQL > select object_name from dba_objects
Cmd:/> imp system/oracle fromuser=HR
touser=<user1> file=d:/>bkp.dmp [rows=n]
Cmd:/> imp sys/oracle fromuser=HR
touser=user1 file=d:/>bkp.dmp table=emp – To import only specific table
To export & import entire
database
Cmd:/>exp system/oracle
Cmd:/>imp system/oracle
Backup
& Recovery
Types of failure
- statement – because of insufficient privilege,quoto,exceeded,
logic problem
- User process failure – PMON (Session abnormal termination)
- Network failure – Network problems
- User error – Sematics problems
- Instance failure (SMON) – Abort
- Media failure – Like HD failure
Phase of instance recovery
- Roll forward – After commit we have to use this phase with
the help of backup file
- Roll backward – To recover un-commited transaction
Types of backup
- User managed backup
- RMAN (Auto backup)
Modes of backup
- Closed (or) cold backup – In shutdown state
- Open (or) Hot backup – In running state
To check whether backup is running
or not
SQL > select name from v$datafile where
TS#=4
To take cold/closed backup
SQL > shutdown immediate
Cmd:/> copy the user01.dbf file to new
backup location manually
Backup & recovery in open / hot
mode
- Open DB backup
- Archive log list
SQL >
select name from V$datafile where TS#=4
SQL >
select tablespace_name from dba_tables where table_name=’TX’ and owner=’SCOTT’
SQL >
select file#,num from v$datafile where ts#=4
SQL >
select * from v$backup where file#=4
SQL >
alter tablespace users begin backup
SQL >
select * from v$backup where file#=4
SQL > host
– Will take us to the cmd prompt
Cmd:/>oocopy
c:\user01 d:\>user01
SQL >
shutdown immediate
SQL >
startup
Note: If any
data is commited after backup date db’s will thorugh error here.
To over come
we have to update those new data from redo
Log files
SQL >
recover datafile 4
SQL >
alter database open
Recovering from Archive log file
Scenario : If your Taking backup in open
mode after that adding few records & commiting. Switching redo log file,
whil switching all redo log file content will be moved to archive log file.
Again adding some content & switching file. In this scenario there will not
be any data in redo log file only we have to get it from archive file.
1. Taking
backup in open mode
SQL >
select * from v$backup where file#=4 – To see already backup running
SQL >
alter tablespace users begin backup – To begin the backup
SQL > host
– Will take us to the cmd prompt
Cmd:/>oocopy
c:\user01 d:\>user01
2. Adding
few more records and commiting
SQL >
insert into tb_sample values (&sno)
SQL >
commit
3. Switching
redo log file
SQL >
alter system switch logfile
SQL >
alter system checkpoint
4. Now
shutdown the server & feel like data file ‘User01’ file is corrupt’
5. SQL
> startup – Will throw an error
6. Now
point data file to backup location or replace file to original location
SQL >
alter database rename d:\oracle\...\user01 to e:\backup\user01
7. SQL
> startup – Now DB will be mounted but not opened throw an error still some
data is missing
8. To
recover from archive file
SQL > recover
datafile <4> - then give auto when prompted
Recovering when Tablespace
corrupt
Scnario : when tablespace is corrupt
1. Table
created
2. Values
are inserted & commited
3. Online
backup taken
SQL >
select * from v$backup where file#=4 – To see already backup running
SQL >
alter tablespace users begin backup – To begin the backup
SQL > host
– Will take us to the cmd prompt
Cmd:/>oocopy
c:\user01 d:\>user01
4. After
backup some more records added and commited
5. Brin
the tablespace offline
SQL
> alter tablespace <user01> offline
6. Delete
the tablespace manually file by locating it
7. When
trying to start the erver, get the error
8. Copy
the backup file replace it to the original location
9. try
to startup throw error
10.SQL
> recover tablespace <users> instead of (recover datafile 4) – Give
auto when prompted
Recovering
when both original data file & backup datafile are missing
1. Shutdown
delete both original & backup data file
2. startup
– Throw an error,
3. Now
bring the missing datafile offline
SQL
> alter database datafile 4 offline
4. Now
try to staru
SQL
> startup – will work
Note. Only missed datafile/data’s cannot be
recovered. Admin my recrated and assign same tablespace to user
Note. In the above scenario that user can
log in even his data file is missing, but cannot create any tables & there
is no old tables
No comments:
Post a Comment