Managing Redo Log Files

 Managing REDO Log Files:
    
     Redo log files record all changes made to data and provide a recovery mechanism from a system or media failure.

• Redo log files are organized into groups.
• An Oracle database requires at least two groups.
• Each redo log within a group is called a member.

How Redo Logs Work
==================

• Redo logs are used in a cyclic fashion.
• When a redo log file is full, LGWR will move to the next log group.
– This is called a log switch
– Checkpoint operation also occurs
– Information is written to the control file


How to add Redo log group:
=====================

SQL> select *from v$log;

SQL> ALTER DATABASE ADD LOGFILE GROUP 3 '/u01/app/oracle/oradata/cts/log3a.rdo' SIZE 4M;


How to add a Redo log member into the redo log group:
=========================================

SQL>select *from v$logfile;

ALTER DATABASE ADD LOGFILE MEMBER
'/u01/app/oracle/oradata/cts/log1b.rdo' to group 1,   
'/u01/app/oracle/oradata/cts/log2b.rdo' to group 2,
'/u01/app/oracle/oradata/cts/log3b.rdo' to group 3;

How to drop a redo log group:
=======================

SQL> ALTER DATABASE DROP LOGFILE GROUP 3;


How to drop a redo log group member:
============================

SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u01/app/oracle/oradata/cts/log1b.rdo';


How to switche logfile and checkpoint force:

SQL> ALTER SYSTEM SWITCH LOGFILE;

SQL> ALTER SYSTEM CHECKPOINT;

       
Note :
=====

* We can't drop the CURRENT and ACTIVE redo log group as well as redo log member.
* We can't Resize the redolog group. If you want to increase the size of the redolog group then add new redolog group with new size and delete old redolog group.
* We can have different size for all redolog groups but have same size for all redolog groups.

Oracle Controlfile Multiplexing

CONTROLFILE MULTIPLXEING
      
 A control file is a small binary file that is part of an Oracle database. The control file is used to keep track of the database's status and physical structure.

We can do controlfile multiplexing in 2 ways

1. Using pfile
2. Using Spfile

1. Using pfile:
===============

1. Shut down the database.

   SQL> shut immediate

2. go to the pfile location and edit the pfile.

   cd /u01/app/oracle/admin/cts/pfile

   vi init.ora

   press "i" to eit
  

control_files='/u01/app/oracle/oradata/cts/controlfile01.ctl','/u01

/app/oracle/oradata/cts/controlfile02.ctl','/u01/app/oracle/oradata

/cts/controlfile03.ctl','/u01/app/oracle/oradata/cts/controlfile04.ctl'

Press ESC+SHIFT + : + wq  --> to save

3. copy the controlfile01.ctl and change the as controlfile04.ctl

  cd /u01/app/oracle/oradata/cts/

  cp controlfile01.ctl controlfile04.ctl

4. start the database using PFILE and check the controfiles

    SQL> startup nomount

pfile='/u01/app/oracle/admin/cts/pfile/init.ora'

    SQL> select *from v$controlfile;

--------------------------------------------------------------------------------------------

2. Using Spfile:
============

1. check the controlfiles and modify the spfile

    SQL> select *from v$controfile

    SQL> alter system set

control_files='/u01/app/oracle/oradata/cts/controlfile01.ctl','/u01

/app/oracle/oradata/cts/controlfile02.ctl','/u01/app/oracle/oradata

/cts/controlfile03.ctl','/u01/app/oracle/oradata/cts/controlfile04.

ctl','/u01/app/oracle/oradata/cts/controlfile05.ctl' scope=spfile;

2. Shut down the database.

   SQL> shut immediate

3. copy the controlfile01.ctl and change as controlfile05.ctl

  cd /u01/app/oracle/oradata/cts/

  cp controlfile01.ctl controlfile05.ctl

4. start the database and check the conrofiles

    SQL> startup

    SQL> select *from v$controlfile;

Tablespaces and Datafiles Management

 TABLESPACES and DATAFILES MANAGEMENT:
 
   Tablespaces are the bridge between certain physical and logical components of the Oracle database. Tablespaces are where you store Oracle database objects such as tables, indexes and rollback segments.  You can think of a tablespace like a shared disk drive in Windows. You can store files on your shared drive, move files around and remove files. The same is true with tablespaces. A tablespace is made up of one or more database datafiles.     

Creating tablespace:
=====================
create tablespace APPSBI datafile '/u01/app/oracle/oradata/cts/appsbi01.dbf' size 200m;

Adding Datafile:
================
alter tablespace APPSBI add datafile '/u01/app/oracle/oradata/cts/appsbi02.dbf' size 200m;

Resizing Datafile:
==================
alter database datafile '/u01/app/oracle/oradata/cts/appsbi02.dbf' resize 300m;

To create UNDO tablepsace
=========================
create undo tablespace APP_UNDO datafile '/u01/app/oracle/oradata/cts/appundo01.dbf' size 200m;

To add UNDO datafile:
=====================
alter tablespace APP_UNDO add datafile '/u01/app/oracle/oradata/cts/appundo02.dbf' size 100m;

To Resize UNDO datafile:
========================
alter database datafile '/u01/app/oracle/oradata/cts/appundo02.dbf' resize 150m;

To Create TEMPORARY TABLESPACE:
===============================
create temporary tablespace app_temp tempfile '/u01/app/oracle/oradata/cts/apptemp01.dbf' size 100m;

select file_name,bytes/1024/1024 from dba_temp_files where tablespace_name='APP_TEMP';

To ADD Tempfile:
================
alter tablespace app_temp add tempfile '/u01/app/oracle/oradata/cts/apptemp02.dbf' size 200m;

To Resize Tempfile:
===================

alter database tempfile '/u01/app/oracle/oradata/cts/apptemp01.dbf' resize 150m;


To Check Free space of all the tablespaces:
================================

 select TABLESPACE_NAME,sum(bytes/1024/1024) from DBA_FREE_SPACE group by tablespace_name;

To check allocated Space of all the tablespaces:
====================================

 select TABLESPACE_NAME,sum(bytes/1024/1024) from DBA_DATA_FILES group by tablespace_name;

To Check used space of all the tablespaces:
================================
 select TABLESPACE_NAME,sum(bytes/1024/1024) from DBA_SEGMENTS group by tablespace_name;

To check all the datafiles with allocated size in particular tablespace:
==================================================
select file_name,bytes/1024/1024 from dba_data_files where tablespace_name='APPSBI';

Tablespace related operations:
=============================
offline/online
datafile add/resize
read/write mode
create/drop tablespace
logging/nologging

Related views:
=============
dba_tablespaces
dba_data_files
dba_temp_files
dba_temp_free_space
v$tempfile
v$datafile

Note:

1. We can create a table with particular tablespace spacification

create table test(eno number) tablespace appsbi;

2. We can allocate the datafile to  particular table

alter table test allocate extent (size 10k datafile '/u01/app/oracle/oradata/algates/appsbi02.dbf');

ORACLE DATABASE MANNUAL CREATION STEPS - For Beginners! Easy 10 Steps!
================================================================
Database Name: cts                         ||
OS: LINUX/AIX                             ||         RAFFEEK :)
Oracle S/W : 10g                             ||
================================================================
1. cd /u01/app/oracle/admin

ls -lrt
mkdir cts
cd cts

mkdir adump bdump cdump udump pfile

2.cd /u01/app/oracle/oradata

mkdir cts

3. pfile creation steps

 cd /u01/app/oracle/admin

ls -lrt

cd <EXISTING DATABASE>/pfile

ls -lrt

cp init.ora /u01/app/oracle/admin/cts/pfile

cd /u01/app/oracle/admin/cts/pfile

ls -lrt

vi init.ora

press "i" to edit

change database name,audit/background/core/user dump locations, controlfile locations and dispatchers name

esc+shift+:+wq  --> to save


4. password file creation steps

cd /u01/app/oracle/product/10.2.0/db_1/dbs

pwd

orapwd file='/u01/app/oracle/product/10.2.0/db_1/dbs/orapwcts.ora' password=sys entries=5

ls -lrt

5. edit the oratab entry

 vi /etc/oratab

Press "i" to edit

cts:/u01/app/oracle/product/10.2.0/db_1:N

esc+shift+:+wq  --> to save


6. . oraenv  --> Set the oracle environment

? cts

  (or)

export ORACLE_SID=cts
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1

echo $ORACLE_SID   --> just to check the database name
echo $ORACLE_HOME  --> just to check the oracle home path

sqlplus "/as sysdba"

7.SQL> startup nomount pfile='/u01/app/oracle/admin/cts/pfile/init.ora';


8. SQL> create database cts
datafile '/u01/app/oracle/oradata/cts/system01.dbf' size 250m
sysaux datafile '/u01/app/oracle/oradata/cts/sysaux01.dbf' size 300m
default temporary tablespace temp tempfile '/u01/app/oracle/oradata/cts/tmp01.dbf' size 40m
undo tablespace undotbs1 datafile '/u01/app/oracle/oradata/cts/undo01.dbf' size 40m
logfile
 group 1 '/u01/app/oracle/oradata/cts/log1.dbf' size 40m,
 group 2 '/u01/app/oracle/oradata/cts/log2.dbf' size 40m
/

9. spfile creation

SQL> create spfile from pfile='/u01/app/oracle/admin/cts/pfile/init.ora';

SQL> select status from v$instance;


10.SQL> @?/rdbms/admin/catalog.sql

SQL> @?/rdbms/admin/catproc.sql

SQL>conn system/manager

SQL> @?/sqlplus/admin/pupbld.sql

exit

ps -ef|grep pmon

Oracle Patching

Applying Patches to Oracle Database:

   Oracle continues to recommend that, even though other mitigation measures may be available, Critical Patch Updates be applied as soon as possible in order for organizations to retain their security in depth posture. 

Pre-check:

1. Check oracle database full backup.
2. take oracle home backup and inventory backup.
cd $ORACLE_HOME
tar -cvf /u01/oracle/home10204_bkp.tar .

3.chek vaild and invaild object counts.
select owner,object_name,object_type from dba_objects where status='INVALID'

Apply to 10204 home:
================

Shut down DBs and listener on this home
Check for zombie processes: ps -ef | grep ora

if any process is there kill that process.

export PATH=$PATH:/u01/app/oracle/product/10.2.0/db_1/OPatch
which opatch (verify 10.2.0.4)
echo $ORACLE_HOME (verify 10.2.0.4)

Apply Jul2011CPU:
cd /orasw/dba/patches/12419249

opatch napply -skip_subset -skip_duplicate

Hit enter when it asks for e-mail address

Enter "Y" I am ok being uninformed

Then "Y" for All

Once successfully completed. start the database.

Apply to Database:
===============

SQL>Startup

Now run CATBUNDLE for each DB:
#cd $ORACLE_HOME/rdbms/admin
startup
@?/rdbms/admin/catbundle.sql cpu apply
@?/rdbms/admin/utlrp.sql

Post-check:

select *from  registry$history;
check the vaild and invaild object  counts.

start the listener.
Done!

opatch lsinventory --> os level we can check what are the patchs are applied.

How to rollback:
================
opatch rollback -id <patchid>

Basic - Oracle architecture for beginners

 

As an Oracle DBA, you must understand the concepts of Oracle architecture clearly. It is a basic step ormain point that you need before you go to manage your database. By this article, I will try to share my knowledge about it. Hope it can be useful for you.

What is An Oracle Database?

Basically, there are two main components of Oracle database –– instance and database itself. An instance consists of some memory structures and the background processes, whereas a database refers to the disk resources. Figure 1 will show you the relationship. Figure 1. Two main components of Oracle database

Instance

As we cover above, the memory structures and background processes constitute an instance. The memory structure itself consists of System Global Area (SGA), Program Global Area (PGA), and an optional area ––Software Area Code. In the other hand, the mandatory background processes are Database Writer(DBWn), Log Writer (LGWR), Checkpoint (CKPT), System Monitor (SMON), and Process Monitor (PMON).And another optional background processes are Archiver (ARCn), Recoverer (RECO), etc. Figure 2 will illustrate the relationship for those components on an instance.

Figure 2. The instance components

System Global Area

SGA is the primary memory structures. When Oracle DBAs talk about memory, they usually mean theSGA. This area is broken into a few of part memory –– Buffer Cache, Shared Pool, Redo Log Buffer, LargePool, and Java Pool.

Buffer Cache

Buffer cache is used to stores the copies of data block that retrieved from datafiles. That is, when userretrieves data from database, the data will be stored in buffer cache. Its size can be manipulated viaDB_CACHE_SIZE parameter in init.ora initialization parameter file.

Shared Pool

Shared pool is broken into two small part memories –– Library Cache and Dictionary Cache. The librarycache is used to stores information about the commonly used SQL and PL/SQL statements; and ismanaged by a Least Recently Used (LRU) algorithm. It is also enables the sharing those statemens amongusers. In the other hand, dictionary cache is used to stores information about object definitions in thedatabase, such as columns, tables, indexes, users, privileges, etc.The shared pool size can be set via SHARED_POOL_SIZE parameter in init.ora initialization parameter file.

Redo Log Buffer

Each DML statement (insert, update, and delete) executed by users will generates the redo entry. What isa redo entry? It is an information about all data changes made by users. That redo entry is stored in redolog buffer before it is written into the redo log files. To manipulate the size of redo log buffer, you can usethe LOG_BUFFER parameter in init.ora initialization parameter file.

Large Pool

Large pool is an optional area of memory in the SGA. It is used to relieves the burden place on the sharedpool. It is also used for I/O processes. The large pool size can be set by LARGE_POOL_SIZE parameter ininit.ora initialization parameter file.

Java Pool

As its name, Java pool is used to services parsing of the Java commands. Its size can be set byJAVA_POOL_SIZE parameter in init.ora initialization parameter file.

Program Global Area

Although the result of SQL statemen parsing is stored in library cache, but the value of binding variablewill be stored in PGA. Why? Because it must be private or not be shared among users. The PGA is alsoused for sort area.

Software Area Code

Software area code is a location in memory where the Oracle application software resides.

Oracle Background Processes

Oracle background processes is the processes behind the scene that work together with the memories.

DBWn

Database writer (DBWn) process is used to write data from buffer cache into the datafiles. Historically, thedatabase writer is named DBWR. But since some of Oracle version allows us to have more than onedatabase writer, the name is changed to DBWn, where n value is a number 0 to 9.

LGWR 

Log writer (LGWR) process is similar to DBWn. It writes the redo entries from redo log buffer into the redolog files.

CKPT

Checkpoint (CKPT) is a process to give a signal to DBWn to writes data in the buffer cache into datafiles. Itwill also updates datafiles and control files header when log file switch occurs.

SMON

System Monitor (SMON) process is used to recover the system crach or instance failure by applying theentries in the redo log files to the datafiles.

PMON

Process Monitor (PMON) process is used to clean up work after failed processes by rolling back thetransactions and releasing other resources.

Database

The database refers to disk resources, and is broken into two main structures –– Logical structures andPhysical structures.

Logical Structures

Oracle database is divided into smaller logical units to manage, store, and retrieve data effeciently. Thelogical units are tablespace, segment, extent, and data block. Figure 3 will illustrate the relationshipsbetween those units.

 

Oracle DBA Work

Hi friends, This is abdul raffeek working as a Sr.Oracle DBA in an Indian Based MNC, I would like to share something about Oracle DBA Work nature, day to day activities and advance concepts.

DBA Work Nature:

As an Oracle DBA, you can expect to be involved in the following tasks:

• Installing Oracle software
  
• Creating Oracle databases
  
• Performing upgrades of the database and software to new release levels
  
• Starting up and shutting down the database
  
• Managing the database's storage structures
  
• Managing users and security
  
• Managing schema objects, such as tables, indexes, and views
  
• Making database backups and performing recovery when necessary
  
• Proactively monitoring the database's health and taking preventive or corrective action as required
  
• Monitoring and tuning performance
  
   Daily Activities:
  In a small to midsize database environment, you might be the sole person performing these tasks. In large, enterprise environments, the job is often divided among several DBAs, each with their own specialty, such as database security or database tuning.
  
  Advance Concepts:
   Datagurad, ASM and RAC