Admin Portion

Oracle-Administration

Concentrate on dynamic views and data dictionary

Types of views dba views, all views and user views

Use of Putty

Networking

1.     TNS files (Transparent Network Substrate (TNS)) – TNS Configuration- tnsnames.ora file

2.     Listener files

·         The Listener is comprised of two binaries:

(1) tnslsnr which is the Listener itself and

(2) The Listener Control Utility (lsnrctl) which is used to administer the Listener on the server or remotely.

Transparent Network Substrate (TNS) is the network protocol used by Oracle for connectivity to Oracle Databases.

3.     SQLNET.ORA – and some importance profile parameter like sqlnet.authentication_services etc

4.     Two methods by which a listener comes to know of a database instance. In Oracle terminology, this is referred to as “registering with the listener.” Static instance registration and dynamic instance registration with listener & Local_listener parameter

5.     Listener.log – location of it and use of it

6.     Utility tnsping

7.     Utility ping

Note: TNSPING runs over the TNS layer. Similar to TCP/IP's ping utility, tnsping sends a short message to the listener. The type of message is known to all Oracle listeners. It requests acknowledgement that the service name is valid and that the listener is configured to handle

requests for that service name.

8.     REMOTE_LOGIN_PASSWORDFILE parameter

9.     v$psfile_users data dictionary

Connecting to Database

1.       Using putty from windows machine

2.      Try and understand the meaning of secured connection

Creating Database manually and with the help of DBCA

Prerequisites to create database

Steps involved in creating database

What is DBCA-

Types of Parameters in a pfile/spfile

·         Normal Parameters. These could be seen through the show parameter parametername

·         Hidden Parameters or underscore parameters.

·         How to see hidden parameters (Through which views) - extra

·         While using hidden parameters what precaution we should take – extra

V$views and dba_, all_, user_ views

V$process, v$session etc

Managing Controlfiles

What Is a Control File?

 Guidelines for Control Files - Size of a control file

Calculate the size of a control file

 Creating Control Files

 Contents of Controlfile
 Multiplexing Controlfile
 Backup of Controlfile (Trace & Binary)

               Recreating controlfile using trace
               Use of backing control file
               Recovering a Control File Using a Current Copy
               Dropping Control Files
               Displaying Control File Information – related data dictionary views like v$controlfile

              and     v$controlfile_record_section

               Parameter - controlfile record keep time

Managing Controlfiles (Cloning) – adv

Renaming database

How to do cloning, steps required for cloning a database, control file and clone

Multiplexing of controlfiles

The minimum number of days that a reusable record is kept in the controlfile is controlled by the  control_file_record_keep_time parameter.

Control file growth and size restriction

Managing Redologs

 Contents of Redo logs

 Need of Redo logs

 Multiplexing of Redo logs- group and member, delete Redologs – group and members etc.

Information about redo log groups and members- data dictionary

Log writer – conditions on which log writer writes

Parameters in the pfile/spfile decides the redolog members and groups

Maximum and minimum of redo log members and groups

V$database – log_mode column for understanding whether the database is in archive log mode or not as well as Archive log list command also shows whether the database  is in archive log mode or not

V$log – for info about group (column status shows the status of a group)
v$logfile – for info about members within a group (column status shows status of a log member)

List all the possible status for a log member and log group

Log status, Logfile Status,  Log sequence No,

Putting database in archive/noarchive log mode

Changing archive location ( current location filled or reasons like this)

Configuring FRA (Flashback recovery area – prerequisite to do it)

Exploring the Storage Structure of Your Database

Tablespaces

Segment

Extent

Blocks

Row chaining and row migration

Temporary Tablespace Groups

Rollback Segments

Redo Log Groups

 Active Logs

Segment vs object

Storage Structure
Types of Segments?
 Extent Allocation & Deallocation
 Block Structure, Row Structure
 Block Space Utilization Parameters

High water mark – (to set the high water mark below – truncating tables, shrink etc)
  

Managing Tablespace

                Types of tablespace management (Locally & Dictionary) Adv & Disadv
                Difference between local and dictionary managed Tablespaces

               

               

Tablespace Management i.e. extent management within a tablespace

                                Locally(Default in 10g)  managed

                                Dictionary managed(not used in 11g)

                Extent Management  i.e. extent allocation

          

   Whether the extents allocated will be same in size (Uniform) or oracle decides the extent size:

                                Autoallocate (default)

                                Uniform

                Segment Space Management

                                Manual - freelist

                                Automatic (default) – bitmap

                             ASSM

 Note: segment space management for system, temp and undo tablespace  is manual in Oracle 10g. If try to create a temporary tablespace with the following syntax:

SQL>create temporary tablespace temp2 tempfile 'C:\temp\temp02.dbf' size 50M extent management local segment space management auto;

create temporary tablespace temp2 tempfile 'C:\temp\temp02.dbf' size 50M extent management local segment space management auto

  *

ERROR at line 1:

ORA-30573: AUTO segment space management not valid for this type of tablespace

Guidelines for Managing Tablespaces
Creating Tablespaces – (while creating tablespace- options such as logging/nologging/ FORCE LOGGING, Storage options such as size, next, maxsize, autoextent on/uniform size nk)
Specifying Nonstandard Block Sizes for Tablespaces
Controlling the Writing of Redo Records
Altering Tablespace Availability
Using Read-Only Tablespaces

10g feature - Default Permanent Tablespace

10g feature - Rename Tablespace

10g feature -SYSAUX Tablespace

10 g feature -Multiple Temporary Tablespaces: Using Tablespace Groups

10g feature- BigFile Tablespace

Dropping Tablespaces
Diagnosing and Repairing Locally Managed Tablespace Problems
Migrating the SYSTEM Tablespace to a Locally Managed Tablespace
Transporting Tablespaces between Databases- Transportable tablespace
Viewing Tablespace Information – dba_tablespaces, dba_tablespace_groups, v$tablespace etc.

                 Creating Locally Managed Tablespace
                Alter Locally Managed tablespace – Change Availability (Online, Offline, RO, RW) Storage options, Coalesce, Add/ Drop/ Rename datafile

 Managing Undo & Temporary Tablespace

What Is Undo? What are the contents of UNDO?

Undo Tablespace – Create Undo Tablespace Alter - Add file, Rename/Relocate file)

 Switch Undo Tablespace-

You can create multiple Undo tablespaces in a database but only one of them can be active at any one time. A particular Undo tablespace can be activated by using the ALTER SYSTEM SET UNDO_TABLESPACE command

 Introduction to Automatic Undo Management
Setting the Undo Retention Period - UNDO_RETENTION – initialization parameter

Sizing the Undo Tablespace
Managing Undo Tablespaces
Migrating to Automatic Undo Management
Viewing Information About Undo, for e.g.  V$UNDOSTAT etc

Undo retention in 10g release 2

            RETENTION GUARANTEE clause for the undo tablespace

             ORA-1555 (snapshot too old) error.

Temporary Tablespaces

Why is temportary tablespace required? Can we do without it?

Creating a Locally Managed Temporary Tablespace
Creating a Bigfile Temporary Tablespace- 10g Feature

Create Temp Tablespace

Drop Temp Tablespace

Multiple Temporary Tablespaces: Using Tablespace Groups- Creating a Tablespace Group, Changing Members of a Tablespace Group, Assigning a Tablespace Group as the Default Temporary Tablespace

Managing Datafiles

Guidelines for Managing Datafiles
Creating Datafiles and Adding Datafiles to a Tablespace
Changing a Datafile's Size

Altering Datafile Availability
Renaming and Relocating, resize Datafiles

Change Availability of Datafiiles
Dropping Datafiles
Verifying Data Blocks in Datafiles
Mapping Files to Physical Devices
Viewing Datafile Information

Add Datafiles to tablespace,

Limitations When Adding Datafiles to a Tablespace- DB_FILES initilialization parameter and  the MAXDATAFILES  parameter of create database or create control file command. The error ORA-00059 relating to the DB_FILES parameter and solution to it
   

Database storage structure

Datafiles

Control Files

Online Redo Log Files

Archive Log Files
Other Storage Structures like undo tablespaces

Tables

Create, alter, drop, rename table, rename column of a table

DDL statements

 Data Integrity, Users Privileges & Roles

            How a user connects to database? (Normal user like Scott)

Concept of RDBMS

Constraints & Business Rule

User & Schema
Create users

                allocating default tablespace and quota on it.

                Assigning temporary tablespace

                changing default tablespace

                database default tablespace

Grant to user

Role

What are Roles? Adv of Roles? purpose of creating roles

 Default Roles
 Creating , Altering & Droping Roles
 Effect of Alter & Drop
Grant to Role

                list out few roles and privileges in them.

                Creating role

                assigning privileges

                Connect, Resource role

                Why when granted resource role to any user, it even give quota unlimited on all the available

                 tablespaces?

                Can we give unlimited quota on tablespace to a role

                Can we grant sysdba/sysoper privilege to a role

Privileges

                            System & Object Privileges

    Granting system privileges with admin option

   Granting privileges With Admin/ With Grant option
   Revoking System & Object Privileges – Effect of  revoke?
  Object privileges with grant option

Profiles

Need to have profiles, What are Profiles?
Creation of a profile

                Password Management features

                The Oracle server provides a password complexity verification function named                 VERIFY_FUNCTION. This function is created with the /rdbms/admin/utlpwdmg.sql            script. The password complexity verification function must be created in the SYS schema. It              can be used as a template for you customized password verification.

                Data dictionary dba_users have a column profile which shows profile currently assigned to users
                Create Profiles
                Alter Profiles – Effect of altering
                Drop Profile – Effect of Drop
                Resource Management Features – How to Enable

Indexes

What is an index

Types of index

Data dictionary views for Index

Create- drop Index

Bitmap index (high and low cardinality)

Function based index

Index organized table(IOT)

Unique and non unique index

B-tree index

Rebuild index / reorganize index

B-level  or branch level (blevel column is present in user_indexes)

Height( height column is present in index_stats)

Analyze index

Alter index

Extra about Index

Extra: Changing database name  using ( database utility NID ) Metalink 863800.1

ASM –

Automatic Storage Management :

Automatic Storage Management  - feature introduced in Oracle 11g

·         The new “SYSASM” role to manage the ASM instance, variable extent sizes to reduce shared pool usage &

·         The ability of an instance to read from a specific disk of a diskgroup are just some of the great new features

ASM storage :

Storage components

ASM Disk Group Administration