Database Administration

DATABASE ADMINISTRATION Seminar Persentation On Database Administration By Satish Kumar

Responsibility of Database Administration Must Responsibility of Installation Oracle Software .

Must Responsibility of Installation Operating System . Must Responsibility put the security of Database.

Must Responsibility for Hot Backup and Cold Backup. Managing Tablespace .

Managing Import and Export utility.

Must Responsibility to configure Network Netca. Managing users and Privileges .

Control and Monitor users and access database.

Database Architecture Background Process

Buffer Cache

Data File SGA

Redolog File

Control Files

Architecture

Tablespace

SGA-System Global Area PGA-Programmable Global Area

Schema Object

PGA

Database Architecture

Process

Memory

Storage

Database Architecture 1.Storage Area: (a)Physical Database(data files,Redolog files,Control files, and Parameter files.)

2.Memories :

(a) System Global Area(Db buffer,Log buffer,Shared pool area) (b) Program Global Area 3.Process :

(a) Client Process (b) Server Process(Oracle Server Process ,Background Process)

Database Architecture Physical Structure Of Database: 1.Data files 2.Redolog Files 3.Control files

Logical Structure of Database: 1.Tablespace 2.Schema Object (Table,view,Trigger,Procedure ) 3.Segment 4.Extents 5.Blocks

Database Creation 1.Database Configuration Assistance (DBCA 2.Script Create instance: C:\Oradim -new -sid orclse -syspwd password –startmode auto -pfile F:\app\DatabaseAdmin\product\12.1.0\dbhome_1\dbs\orclseinit.ora; C:\oradim –delete –sid orclse C:\net continue oracleserviceorclse C:\set oracle_home=F:\app\DatabaseAdmin\product\12.1.0\dbhome_1 C:\set oracle_sid=orclse C:\sqlplus /nolog Sql>connect sys/password as sysdba Sql>Startup nomount pfile=‘F:\app\DatabaseAdmin\product\12.1.0\dbhome_1\dbs\orclseinit .ora’; Sql>create spfile from pfile=‘F:\app\DatabaseAdmin\product\12.1.0\dbhome_1\dbs\orclseinit .ora’;

Database Creation

Create Database create database orclse user sys identified by administrator user system identified by administrator maxinstances 1 maxloghistory 1 maxlogfiles 5 maxlogmembers 5 character set US7ASCII national character set AL16UTF16 datafile 'F:\app\DatabaseAdmin\oradata\orclse\DATAFILE\system0 1.dbf' size 500M extent management LOCAL SYSAUX datafile 'F:\app\DatabaseAdmin\oradata\orclse\DATAFILE\sysaux01 .dbf' size 500M DEFAULT TEMPORARY tablespace temp01 tempfile 'F:\app\DatabaseAdmin\oradata\orclse\DATAFILE\temp01_ 01.dbf' size 100M

Database Creation

UNDO tablespace undotbs01 datafile 'F:\app\DatabaseAdmin\oradata\orclse\DATAFILE\undotbs01.dbf' size 200M DEFAULT tablespace users datafile 'F:\app\DatabaseAdmin\oradata\orclse\DATAFILE\users01.dbf' size 100M LOGFILE group 1 ('F:\app\DatabaseAdmin\oradata\orclse\ONLINELOG\redo01.log') size 100M, group 2 ('F:\app\DatabaseAdmin\oradata\orclse\ONLINELOG\redo02.log') size 100M; Database Created Sql>@ F:\app\DatabaseAdmin\product\12.1.0\dbhome_1\RDBMS\ADMIN\catalog.s ql Sql>@ F:\app\DatabaseAdmin\product\12.1.0\dbhome_1\RDBMS\ADMIN\catproc.s ql Sql>@ F:\app\DatabaseAdmin\product\12.1.0\dbhome_1\sqlplus\admin\pupbld.sql

Tablespace Management

1.sql>Create tablespace tablespace_name datafile ‘d:\oracle\app\datafile\mydb.dbf ’ size 100mb;

2.sql>Create tablespace tablespace_name datafile ‘d:\oracle\app\datafile\mydb.dbf’ size 100mb extent management local uniform size 10m block size 8k online; 3.sql>Alter tablespace tablespace_name add datafile ‘d:\oracle\app\datafile\mydbf01.dbf ’ size 100mb;

4.sql>Alter tablespace tablespace_name rename to new tablespace_name; 5.sql>Alter tablespace tablespace_name drop datafile ‘d:\oracle\app\datafile\mydb01.dbf ‘; 6.sql>Drop tablespace tablespace_name including contents and datafile;

7.sql>Alter database datafile ‘d:\oracle\app\datafile\mydb.dbf ’ autoextend on next 10mb maxsize 20mb;

Tablespace Management Create

Allocation Extent

Temporary

Alter Database ,Resize Datafile

Tablespace

Autoextend on next

Add Data File

Online,Offline

Read,Write

Initial,Next,minextent,maxextent Sql>alter tablespace tablespace_name rename to new name

Storage Management 1.Initial : The size in byte of the first extent allocated block. (a) Default 5 blocks (b)Minimum 2 blocks (c) Maximum OS. 2.Next:The size of the next extend to be allocated for a segment. (a)Default 5 blocks (b)Minimum 1 blocks (c) Maximum OS. 3.Maxextents:The total number of extents including the first can ever be allocated for the segment.(a)Default depend on data block size(b)Minimum 1 Extent (c)Maximum OS 4.Minextents :The total number of extents to be allocated when the segment is created .(a)Default 1 extent (b)Minimum 1 Extent (c)Maximum OS 5.Pctincrease :Percent by which each incremental extent grows over the last incremental extent allocated for segment (a)Default 50%(b)Minimum 0%(c)Maximum OS 6.sql>Select file_name,file_id,bytes ,tablespace_name from dba_data_files;

Storage Management Dba_ segment

Allocation Extent

Dba_segments

Dba_extent Dba_tables

User_segments

Data Dictionary

Dba_index

Dba_free_space

Dba_data_files dba_tablespace

Rollback Segment Management

Rollback segments storage undo information and are used for undo the previous command,read consistancy ,and crash recovery. (a)Public (b)Private 1.sql>Select segment_type,segment_name from dba_segment where segment_type=‘deffered_rollback’; 2.sql>Create rolback segment rollbs tablespace rollbs_dbs storage(initail 10k next 10k optimal 20k minextents 2 maxextents 5); 3.sql>alter rollback segment rollbs storage (maxextents 10); 4.sql> alter rollback segment rollbs online/offline; Dba_segments User_segments Dba_rollback_seg

RBS data dictionary

V$rollstat V$rollname

Managing Users and Resource To access a database a user must run a database application and connect to the database instances using a valid user name define in the database .

User have following Parameter •Default Tablespace •Temporary Tablespace •Tablespace quota •Profile sql>Create user scott identified by tiger default tablespace tablespace_name quota 10mb on tablespace_name quota 10m on system profile profile_name; Note : Profile must be create before the create user . sql>alter user scott profile profile_name;

Managing user and resource Quota on 5mTablespace

Password

Default tablespace

User Name

quota 5m on system

Temporary Tablespace Profile

Managing Resource with Profile System resource limits are managed with the user profile.

A profile is a named set of resource limit that can be assigned to a user . The database administration has option to globally enable or disable profile.

Limits value are Default/integer/Unlimited

1. sql>alter system set resource limit =True; 2. sql>create profile profile_name limit session_per_user 1 connect_time 560 cpu_per_call 600 failed_login_attmpts 3 password_life_time 60 password_lock_time 1 Password_grace_time 10 idle_time 15;

Managing Resource with Profile Cpu_per_session

Password_verify_function

Password_life_time Password_reuse_max

cpu_per_call Session_per_user

Profile Limit

Idle_time

Password_lock_time Private_sga

connect_time

Password_grace_time

Composite_limit

Failed_login_attempts

Logical_read_per_session and call

Managing User Privileges and Role

A privileges is a permission to execute an action or to access another user’s object.

• The right to access the database.

• The right to select data from another user’s tables.

•The right to execute another user’s stored procedure .

•The right to create new users.

There are two types of privileges. (a)System Privileges (b) Object Privileges (a)System Privileges :A system privileges is the right or permission to execute a particular database action on a particular type of object.eg create tablespace

Object priviles : An object privileges is permission to perform an action on specific object.eg alter,select,insert,etc Role:Role are named group of of related privileges that are to individual users and other roles.

Sql>create role demo identified by demo; Sql>drop role demo;

Managing User Privileges and Role Create Table,Session

Admin

Connect,Resource Update any cache

Create any index Create any view

System Privileges

Create any synonyme Drop Table

Execute any proced.

Insert any Table Update Table

Alter any view

Alter any Procedure

Alter any Table,index,sequence

Managing User Privileges and Role Alter Table,sequence

Delete Table

Execute Procedure

Object Privileges

Insert Table ,View

Update Table,view Select Table,Table,view,sequence

Managing Undo Tablespace

An undo tablespace is organised as uniformed bitmaped tablesapce.It is composed of one or more files containing undo segment.

Rollback segment are still used but are internally created maintained and are called undo segments.

Sql>create undo tablespace undotbs01 datafile ‘d:\oracle\app|datafile|undotbs01.dbf ’ size 10mb;

An undo tablespace is permanent locally managed tablespace,read ,write and loging mode with default block size.

Sql>alter system set undo_tablespace=‘Undotbs01’; Sql>Drop tablespace undtbs01 including contents;

Managing Redo log files Every database must have least two redo log groups and file per group.

Drop log file member

Add group

Rename

Redo log file

Add Member

Drop Log file group Clear Unarchived Red log file

Managing Control File

Control files are created by Oracle the path of control file is specified in init.ora.Every oracle database should have at least two control files each stored on different disk.If control file is damaged due to disk failure the associated instance must be shutdown once the disk driver is repaired the damaged control file and instance can be restarted .

C:\copy f:\app\DatabaseAdmin\oradata\orcl\control01.ctl d:\controlfile\control01.ctl Archived Redo log file: It is database choice where to run database in archive log mode or Nonarchive log mode. sql>alter database Archivelog; sql>alter database open; Data Dictionary : V$Acrchive_log V$Archive_Dest V$Archive_Process

Managing Export and Import(logical Backup) Export and import is an oracle utility used to store oracle database data is export format (.dmp) for later retrival.

System/manager

dumpfile=name.dmp

logfile=name.log

‘ f:\impdir’

Export and Import

Directory Dir_name

tables=employees Tables Schemas Full

owner=scott remap_schema=fromuser:touser

Sql >Create directory dir as ‘f:\impdir’;

Managing Sql loader Load the data from flat file to oracle database in table D:\sqlldr username/password controlfile.ctl userid=username/password Insert Append Truncate Insert,append,truncate

control file

(col1,clo2,col3,col4)

Sqlldr utility

Into table table_name load data,infile filename

Managing Backup and Recovery

Cold Backup:Cold backup perform at the shutdown time.

Sql>@ getfile.sql set echo off set pause off set feed off set head off set linesize 200 spool c:\batchfile\coldzipbackup.bat select ' copy ' ||NAME|| ' d:\oracle\backup ' from v$datafile; select ' copy ' ||NAME|| ' d:\oracle\backup ' from v$tempfile; select ' copy ' ||NAME|| ' d:\oracle\backup ' from v$controlfile; select ' copy ' ||MEMBER|| ' d:\oracle\backup ' from v$logfile; spool off shutdown immediate; host cls host c:\batchfile\coldzipbackup.bat host zip cold.zip d:\oracle\backup\*.* host move c:\cold.zip f:\Zipbackup startup;

Managing Backup and Recovery

Hot Backup:Hot backup perform at the running time (online).

Sql>startup nomount; Sql>alter database archivelog; Sql>alter database open; Sql>select log_mode from v$database; Sql>archive log list; Sql>alter database begin backup; Sql>host C:\copy *.* d:\backup\ Exit Sql>select * from v$backup; Sql>alter database backup controlfile to ‘d:\backup\control’; Sql>alter database end backup; Sql>shutdown; Sql>startup mount; Sql>recover database until cancel using backup controlfile;

Managing Transportable Tablespace

Transportable Tablespace:We can Transport Tablespace one database to another database useing expdp and impdp.

Sql>startup nomount; Sql>alter database open; Sql>select * from dba_directories; Sql>create directory imp_dir as ‘d:\dump’; Sql>select * from dba_directories; Sql>host C:\expdp sys/databaseadmin directory=imp_dir dumpfile='f:\imp_dir\sales02.dmp‘ transport_tablespaces=sales02 Open other cmd C:\set oracle_sid=orclxe C:\sqlplus sys as sysdba Password: Sql>startup mount Sql>alter database open; Sql>create user username identifies by username default tablespace tablespace_name; Sql>create directory exp_dir as ‘d:\dump’; Sql>host C:\Copy dumpfile to d:\dump directory Exit

Managing Transportable Tablespace C:\ copy d:\oracle\datafile\sales02.dbf d:\oracle\datafile\orclxe\sales02.dbf C:\exit Sql>select * from dba_directories; Sql>host C:\ impdp sys/databaseadminxe dumpfile=sales02.dmp transport_datafiles=‘f:\app\databaseadmin\oradata\orclxe\dat afile\sales02.dbf ' directory=exp_dir Exit Sql>select file_name,tablespace_name,status from dba_data_files;

Managing Pluggable Database C:\sqlplus sys as sysdba Sql>startup; Sql>show con_name; Sql>show pdbs; Sql>select con_id,con_name from v$pdbs; Sql>alter session set container=pluggable_database_name; Sql>alter pluggable database pluggable_database_name open; Sql>connect user/password@pluggable_database_name; Sql>alter pluggable database pluggable_database_name close; Edit lsnrctl and save C:\lsnrctl reload

Create pluggable Database: Using DBCA: Using Script: