ORACLE ADMIN I PART III

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

1. DML lock
2. 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

1. statement – because of insufficient privilege,quoto,exceeded, logic problem
2. User process failure – PMON (Session abnormal termination)
3. Network failure – Network problems
4. User error – Sematics problems
5. Instance failure (SMON) – Abort
6. Media failure – Like HD failure

Phase of instance recovery

1. Roll forward – After commit we have to use this phase with the help of backup file
2. Roll backward – To recover un-commited transaction

Types of backup

1. User managed backup
2. RMAN (Auto backup)

Modes of backup

1. Closed (or) cold backup – In shutdown state
2. 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

ORACLE ADMIN I PART II

Table Space & DATA File

Tablespace – Logical

Data file – Physical files

Note : Tablespace can be created to use one of the following method to manage free space & used space

i)                Locally managed tablspace

ii)            Dictionary-managed tablespace

It is recommended to use locally managed tablspace, If your using Dictionary managed tablespace to migrate from it use below given procedure DBMS_SPACE_ADMIN.TABLSPACE_MIGRATE_TO_LOCAL

Viewing Tableapce, Datafiles, & Temp file information

Tablespace – DBA_TABLESPACES,V$TABLESPACE

Data file – DBA_DATA_FILES,V$DATAFILES

Temp file information – DBA_TEMP_FILES,V$TEMPFILE

Types of DATA Files

1.    Permanent

2.    Temporary

3.    Undo data files

To See the all the table space

SQL > select * from v$tablespace

To see the Data files

SQL > select * from v$datafiles

Creating new table space

SQL > create tablespace <new name> datafile ‘d:\oracle\..\file.dbf’ size 5m

To see the current user tablespace

SQL > select default_tablespace from dba_users where username=’USER1’

SQL > select tablespace_name from user_tables

To see the datafile which belog to table space

SQL > select name,byte from V$Datafile where TS#=7

Allocating tablespace to user

SQL > create user <user> identified by <pwd> default table space <tb name> quota 5m on <tb name>

                  (or)

SQL > alter user <user> default tablespace <table space name>

To modify table space size

SQL > alter database datafile ‘d:\file.dbf’ resize 20m

SQL > select name,byte from v$datafile where TS#=7

To set the table space to auto increment

SQL > desc dba_data_file

SQL > select * from dba_data_files where file_id=6

SQL > alter database datafile ‘d:\file.dbf’ autoextend 1m maxsize unlimited

SQL > select * from dba_data_files where file_id=6

Tablespace Properties

Note : sysaux and system are the mandatory tablspace

v      Segments – Exists within tablespace

Note: The segment space management within a locally managed tablespace can be specified as (i)Automatic or (ii) Manual

v      Extends – Segments are made up of collection of extends

Note: The extend within the a locally managed tablespace can be allocated in two types (i) Automatic (ii)Uniform

v      Datablock – Extends are made up of collection of datablocks

v      Disk block – datavlock are mapped to disk block

v      Default datablock size is 8kb

To bring the other than system table space online or offline

SQL > alter tablespace <tbs name> offline/online

Note : When table space is offline

Possible queries –      Alter table <name> add

                        Alter table <name> modify

                        Drop table <name>

Impossible query -      select, DML, create, truncate, alter table <name> drop col

To see the status of table space

SQL > select name,status,enabled from v$datafile where ts#=<tbs number>

To bring the other than system table space read only or read write mode

SQL > alter tablespace <tbs name> readonly / read write

Note : When table space is read only mode

Possible queries –      Alter table <name> add

                        Alter table <name> modify

                        Drop table <name>

                        Select

Impossible query -      DML, create, truncate,

To see the status of table space

SQL > select name,status,enabled from v$datafile where ts#=<tbs number>

Droping table space

Note: To drop table space if any content inside have to drop those also

SQL > drop tablespace <tbs name> including contents and datafiles

Handling Default table space

Note : To drop default table space, have to change as non default and remove it

SQL > select * from database_properties where property_name =’Default_permanent_tablespace’ - To See the current default tablespace

SQL > alter database default tablespace <new tbs name>

SQL > drop tablespace <old default tbs> including content and datafile

Handling with system tablespace

Note: We cannot bring system table space offline only read only is possible

That to by executing sequence of following command not like previous drop tablespace command.

When we make system table space read only all other table space also automatically become read only.

When system table space in read only

Possible – select query

Impossible – all other queries are not possible including

SQL > alter table add/modify column

Steps to bring system table space to read only

SQL > shutdown immediate

SQL > startup mount

SQL > alter database open read only

SQL > select name,status,enabled from v$datafile where ts#=0

To bring back the system tablespace to read write mode

SQL > shutdown immediate

SQL > startup

Relocating & renaming DATA file

SQL > alter tablespace <name> offline

• Then manually cut & past the data file to new location

SQL > alter tablespace <name> rename datafile <old path\file.dbf>

<New path\file.dbf>

SQL > alter tablespace <name> online

OMF (Oracle Managed File)

SQL > show parameter db_create_file_dest

SQL > alter system set db_create_file_dest=<path> - To change default table space location

SQL > create tablespace <name> - Will stored in above declared path

Note : Without setting ‘db_create_file_dest’ parameter creating tablespace without location will throw an error

Relocate & rename system tablespace

SQL > shudown immediate

• Physically relocate the file

SQL > alter database rename file <old path\fil.dbf> to <new path\fil.dbf>

Note : In open state command is different i.e ‘alter system’ if it is shutdown state command is ‘alter database’

Note : When currently used tablespace is removed user not able to create any table. Without users commiting their transaction it is not possible to remove the table space

Temporary tablespace

SQL > select temporary_tablespace from dba_users where username=’SCOTT’

SQL > select temporary_tablespace from dba_users where username=’USER1’

Note : For all users temporary table space common

To see the default temp table space

SQL > select * from database_properties where property_name=’DEFAULT_TEMP_TABLESPACE’

Create new temporary tablespace

Create temporary tablespace <name> tempfile <path\file.dbf> size 10m

To assign new temp tablespace

SQL > alter user <user> temporary tablespace <tablespace name>

Droping tablespace

SQL > drop tablespace <name> including content

Note : It is not possible to delete default temporary tablespace

UNDO Tablespace

After deletion before commit if we want to undo the deletion data is fetched from undo table space

Note : There will be only one undo tablespace for a database.When undo tablespace is full system get hanged. Only option to fix the issue is rollback (or) kill user sessions

To avoid this situation keep commiting often

To check undo tablespace size

SQL > select name,bytes from V$datafile

SQL > desc dba_users

TO see the undo tablespace

SQL > show parameter undo_tablespace

SQL > show parameter undo

Reating undo tablspace

SQL > create undo tablespace <name> datafile <path\file.dbf> size 10m

To set the newly created undo tablespace as default

SQL > alter system set undo_tablspace=<nem table space>

Droping undo tablespace

SQL > drop tablespace <name> inclding content

Internal Preocess when we delete a record

1.    Compilation & user authentication checked in chared pool

2.    Data deleted frompermanent data file

3.    a. Data which is selected for deletion stored in database buffer

b. cache. That block become used block

   When deletion done it become dirty block

1.     The data transferred to redo log buffer

2.     Then with hello of LGWR it is written to redo log file

Use of UNDO Segment

Storage & relationship structure

Configuring Oracle SID for (TNS Protocol error)

Windows - > Run - > cmd

In cmd -> set oracle_SID=<orcl>

In cmd -> d:/>sqlplusw /nolog

Creating User & Profile

Inside profile there are two concept
1.     Resource management (Values will be in term of minutes)
2.     Password management (Values will be in term of days)

Password management

To see the assigned profile privilege

SQL > select resource_name,limit from dba_profile where profile=’DEFAULT’

To see the assigned profile

SQL > select profile from dba_user wherer user_name=’USER1’

To change the pofile parameters

SQL > alter profile <name> limit FILED_LOGIN_ATTEMPTS 2,PASSWORD_LOCK_TIME 1; - In

the above query only 2 login attempts are allowed & if wrong password is given password get locked for 1 day next day it will be released

To see the user status whether locker or not

SQL > select account_status from dba_users where user_name=<’USER1`’>

User lock types

There are two types

1.     timed (lock) – will be unlocked automatically based on lock period

2.     locked – have to be manually un locked

SQL > alter user <name> account lock/unlock

To lock based on minutes

Calculation are

(1/24)/60 – is a minute i.e .0006

To release the lock automatically after 1 minute

SQL > alter profile <name> limit FILED_LOGIN_ATTEMPTS 2,PASSWORD_LOCK_TIME .0006

Password life time & grace time

SQL > alter profile default limit password_life_time 10, password_grace_time 5

Password reuse history restriction

SQL > alter profile <name> limit password_reuse_max 1,

Password_reuse_time .0006

Note : After one minute same old password accepted

Password verify function

There is inbuikt PL/SQl function to apply for password restriction like

Moer that 8 char must, no dictionary word, etc.

To apply the script restriction

SQL > @ d:\oracle\product\10.2.0\db_1\rdbms\admin\utlpwdmg.sql

To cancel password verify function

SQL > alter profile <name> limit password_verify_function NULL;

To create New profie             

SQL > create profile <name> limit

Failed_login_attempts 1

Password_limit 1

Password resuse_time 1

To assign profile to user

SQL > alter user <name> profile <profile name>

To see the assigned profile

SQL > selecy profile from dba_users where user_name=’USER1’;

To drop profile

SQL > drop profile <name> casecade – Even assigne profile will be deleted that user profile changed to DEFAULT

Note – It is not possible to delete default profile

Resource management

Before changing any resource parameter we have to set resource_limit = true,

SQL > alter system set resource_limit = True

SQL > show parameter resource_limit

SQL > alter profile set session_per_user 1 – only one session for a login

SQL > alter profile set connect_time 20,idle_time 1;

Unable to see the demo of following resource management

CPU_PER_SESSION,CPU_PER_CALL – Like setting download limit, call limit

Privileges & Roles

Two types of privileges

1.     System privileges – Sys is the owner, With Admin option. Enable user to perform particular action with database

2.     Object privileges – With grant option. Enable user to access and manipulate a specific object.

System Privilege

SQL > create session,Table,Index,Procedure,user,etc

SQL > Alter session,table,Index,Procedure,user,etc

SQL > Drop session,table,Index,Procedure,user,etc

SQL > Create/alter/drop any schema.table/index – any means it allows you to create table or index in other users schema

SQL > grant create any table to user with admin – With admin allows users to pass the permission means received user can give the same privilege to other users

Note : If system privilege is given with admin option if received user give  permission to other user,if grant parent delete the permission from parent means child permission will not be deleted automatically has to be done manually.

For object privilege chil permission also get deleted

To check who are all have the admin privilege

SQL > select grantee from dba_sys_privs where privilege=’CREATE ANY TABLE’

To Revoke given permission

SQL > revoke create any table from user1,user2

Roles

Role is grouping of privileges,predefined roles are connect,resource,DBA,etc

To see the existing roles

SQL > select * from dba_roles

Create roles

SQL > create role <name>

SQL > grant create user,alter user to <role name>

SQL > grant <Role> to <user>

As a end user to see applied role

SQL > select * from session_roles

As a end user to see applied privileges

SQL > select * from role_sys_privs

To see role assigned to which user

SQL > select * from dba_role_privs where granted_role=<role name>

To drop role

SQL > drop role <name>

As a SYS to check user log in

SQL > select * from dba_sys_privs where grantee=<User name>

Installing and uninstalling using Wizard

1.     Use OUI to remove oracle from server

2.     regedit - HKLM – SW – Oracle – search for oracle and sid remove

3.     regedit – HKLM – SYS – Service – oracle – deleteCreating oracle database manually

1.     Create the following folders manually

            SID folder

            Insdide SID folder three folder pfile,bdump,udump) (cdump is optional)

2.     Copy and paste the pfile inside the parameter file change all old sid to new sid

3.     Create password file using following command in command prompt

C:\> prapwd file=d:\oracle\product\10.2.0\db1\database\<passfile+sidname>.ora password=oracle

4.     Cerate service manually uing following command in command prompt

C:\> oradim –new –SID <SID name> -pfile d:\oracle\product\10.2.0\admin\india\pfile\init.ora

5.     C:\> set oracle-sid=<sid name>

6.     C:\> sqlplusw /nolog

In SQLPLUSW

SQL >  conn sys/oracle as sysdba

SQL > startup nomount pfile=’d:\oracle\product\10.2.0\admin\india\pfile\init.ora

Note:

Minimum system file required is system,sysaux,undo,temp

Run SQl command to create database

SQL > create database <name>

maxlogfiles 10

maxlogmembers 20

maxdatafiles 4

maxloghistroy 100

datafile ‘d:\oracle\product\...\oradata\india\system01.dbf size 300m’

sysaux datafile ‘d:\oracle\product\...\oradata\india\sysaux01.dbf size 300m’

undo tablespace undotbs1 default

       ‘d:\oracle\product\...\oradata\india\undotbs01.dbf size 200m’

Default temporary tablespace temp

      Tempfile ‘d:\oracle\product\...\oradata\india\temptbs01.dbf size 20m’

Logfile group1 (‘d:\oracle\....\oradata \india\redo01.log’) size 8m

  Group2 (‘d:\oracle\....\oradata \india\redo02.log’) size 8m

      Noarchivelog

/

Creating dictionary

Note: During manual database installation no dictionary exist has to be created manually

SQL > @ ‘D:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\ADMIN\catalog.sql’

Creating packages

Note: During manual database installation no packages exist has to be created manually

SQL > @ ‘D:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\ADMIN\cataproc.sql’

Droping Database manually

SQL > shutdown immediate

SQL > startup mount restrict

pfile=’=’d:\oracle\product\10.2.0\admin\india\pfile\init.ora

SQL > drop database

Note: drop database command only available from 10g