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: