Sunday, September 26, 2010

ROLLBACK_SEGMENTS and UNDO_MANAGEMENT

ROLLBACK SEGMENTS are used to store undo data for uncommited transactions.
In the event of instance failure, Oracle will use the data inside the ROLLBACK SEGMENTS to undo uncommited data inside the database. To enable ROLLBACK_SEGMENTS,

SQL> alter system set undo_management=manual scope=spfile;

SQL> create tablespace rollback_ts datafile 

      '$ORACLE_BASE/oradata//rollback_ts01.dbf' size 50M logging extent 
      management local segment space management manual;   

SQL> create rollback segment rollback_seg tablespace rollback_ts;

SQL> alter system set rollback_segments='rollback_seg' scope=spfile;

SQL> select segment_name, segment_type 
       from dba_segments
      where segment_type='ROLLBACK';

SQL> startup nomount force;

Upon instance restart, Oracle will use the segment rollback_seg specified in the ROLLBACK_SEGMENTS parameter to contain all undo transaction informaton. 

UNDO tablespaces carry out the same task as ROLLBACK SEGMENTS. They to keep the original data modified in uncommited transactions.
UNDO tablespaces are easier to administrate and supposedly more efficient.

To utilise UNDO tablespaces, first the database needs to be put into automatic undo management by running,

SQL> alter system set undo_management=AUTO scope=pfile

An undo tablespace needs to be created to handle the undo data
SQL> create undo tablespace 'UNDOTBS2' datafile  
      '/u01/app/oracle/oradata/orcl/undotbs1.dbf' size 100M retention guarantee

SQL> alter system set undo_tablespace = 'UNDOTBS1';


The retention guarantee option ensures that Oracle does not overwrite data in the undo tablespace which has not passed the expiration date specified in the dynamic UNDO_RETENTION system parameter - even in the event of the tablespace filling up completely.


Last changes being applied to system parameter file 

































Wednesday, September 8, 2010

Installing and preparing OEL 5

Most of my DBA related work is carried out on phsycial servers spread across the network. It is very rare that I install a server from top to bottom with both OS, OS configuration and Oracle.

This post serves as a reference for installing and setting up Linux on a physical server or VM for a training purposes. In this instance, I will be installing 'Oracle Enterprise Linux' - which is based on Red Hat.

The step by step installation assumes you have prepared the VM or server with at least 25GBs of disc space, you have placed the first CD into the CD/DVD ROM drive, booted off the drive and have chosen to install OEL using the GUI method. We begin from the first GUI prompt.

1.Click Next

2.Choose your installation language.

3. Choose your keyboard language layout.

4.Clear out the primary volume partition or VM drive by choosing 'YES'.
If you have any doubt about the importance of the PC/VM you are using, STOP and maybe assess whats on the drive or speak to the person who gave you the machien/VM. This is similar clearing a partition in a windows installation.


5.Choose to use the default layout provided by OEL.




6.Flatten the partition, for cautionary notice, please read what was written in step 4.




7.Leave the eth0 as is unless you know better.
Its preferable to leave the domain as is for consistency and training purposes. 

8.Choose your location

9.Select a ROOT password. Keep it simple for now. Dont forget it. 

10.Its safe to click next without selecting Customize Now.

11.Click Next.

12.The GUI installer will prompt you to have all installion discs at hand. Once you have everthing, proceed by clicking next.










13. Halfway there. The system will reboot when you click "Reboot" and continue with the 2nd half of the installation.

14. Upon reboot, you should be presented with the screen above. Click "Forward"

15. Accept the license agreement

16.Disable the firewall.

17. Choose "Yes"


18. Disable Security Enhanced Unix

19. Choose Yes




20. Disable KDump and click "Forward"


21. Choose date and time, click "Forward"

22. You have the option to add addiotnal users. The Oracle user will be created in the the separate blog post at the bottom of this post.

23. Choose Continue should you not create any users.

24. Test your soundcard if available, then click "Forward"

25. There are no additional CD's, click "Finish"


26. All done! Click OK when you are ready to reboot. 

Get your Oracle CD's and read the following post to set up your newly installed OEL environment.
http://dbaray.blogspot.com/2010/07/install-on-oracle-enterprise-edition-5.html


Tuesday, September 7, 2010

Get rid of the ANNOYING BEEP in your linux vmware

If you want to remove that annoying prompt beep received when performing a backspace
or an invalid shell command on your linux vm then append the following
inside your $HOME/.bashrc file

#happy happy joy joy
xset b off

This will stop the beeping from your server bootup as well as your invalid linux commands

Recreating SPFILE

We received a helpdesk email from a user attempting to time travel on a local development server.
The system date was set to a value in the past. They were attempting to bring the date to now.
The way which this is done is by procedure which launches dynamic SQL and modifies the SPFILE and memory through script similar to the following:

if paramater_date = 'NONE' then
--Remove the entry from SPFILE
execute immediate 'alter system reset fixed_date scope=spfile sid='||''''||'*'||'''';
--Reset value inside memory
execute immediate 'alter system set fixed_date='NONE' scope=memory';
else
--Set value to supplied parameter
execute immediate 'alter system set fixed_date='||''''||parameter_date||'''';
end if;

The user was receiving the following error:

Line Pos Text
202 1 ORA-32001: write to SPFILE requested but no SPFILE is in use
ORA-06512: at "ADMIN.SET_SYSDATE", line 6
ORA-06512: at line 2

I logged into the development database and queried the spfile parameter to see if an SPFILE had been set since the last database refresh:

SQL> show parameter spfile;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string

SQL>

No value had been specified.

So when procedure fires, it attempts to reset a value in a file not specified.
The solution to this error is to create an spfile from the last pfile successfully used to refresh,load or restore the database.

By default, pfiles and spfiles can be found within the $ORACLE_HOME/dbs directory.

server01:~ $ ll $ORACLE_HOME/dbs/initorcl*
-rw-r--r-- 1 oracle oinstall 1342 Sep 7 10:45 /app/oracle/product/11.2.0/dbs/initorcl.ora
server01:~ $

For this instance, I choose to restore an spfile from the pfile in the location
/app/oracle/product/11.2.0/dbs/initorcl.ora

We do this by running
SQL> CREATE SPFILE FROM PFILE='/app/oracle/product/11.2.0/dbs/initorcl.ora'
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;



Now, when the user makes a request to the proc, the proc can now complete the command it originally set out to execute!

exec ops$admin.set_sysdate('NONE');
select sysdate from dual;

ORA-32010: cannot find entry to delete in SPFILE
ORA-06512: at "ADMIN.SET_SYSDATE", line 6
ORA-06512: at line 2

Okay not quite. Whats happening here is that the command:
execute immediate 'alter system reset fixed_date scope=spfile sid='||''''||'*'||'''';
results in Oracle searching the spfile for the entry *.fixed_date
There is a chance the the spfile no longer has the parameter *.fixed_date.
This could have happened in 2 ways:
First, the user may have executed the proc with NONE as the parameter, which would have removed the the parameter in the first transaction successfully.
THEN the next or same person comes along and executes the same proc AGAIN at which time, the parameter no longer exists resulting in the error.

Second, the pfile may have never HAD the parameter in it, therefore, the solution would be to either alter the pfile and include
the paramater then create SPFILE from this, or to create the parameter at run time by firing the following cmd:
alter system set fixed_date='NONE' scope=both
This would create the parameter in the spfile and memory and so if the proc is fired, the parameter will successfully be removed

Tuesday, August 17, 2010

Losing your SPFILE

If you have lost your spfile it is possible to restore it using the DBID and a control file.
However, things become complicated if the DBID is not available or autobackups are not taken.


If you still have the pfile or have an init file for the database from a previous occasion then the following command can be used to restore spfile using that init file.


--#create new spfile from pfile

create spfile = 'spfileinstance' from pfile = 'init.ora'


The init file can be found in
/u01/app/oracle/admin/instance/pfile/init.ora

Sunday, August 1, 2010

Configuring Oracle NET Listener

During the preparation for a tutorial, I stumbled on the following error after attempting to connect to Oracle from the hosting XP machine using SQLTools:
TNS-12505: TNS:listener does not currently know of SID given in connect descriptor


Bear in mind that the VM had been restarted for the first time after its installation.

Detailed messages for listeners can be found in the log file
/u01/app/oracle/diag/tnslsnr/localhost/listener/trace/listener.log
TNS-12505: TNS:listener does not currently know of SID given in connect descriptor
01-AUG-2010 19:57:54 * (CONNECT_DATA=(SID=orcl)(CID=(PROGRAM=C:\Program?Files\SQLTools?1.5\SQLTools.exe)(HOST=CHUCKIE)(USER=Ray))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.98.1)(PORT=3725)) * establish * orcl

Below is the original listener  file before I applied changes
# listener.ora Network Configuration File: /u01/app/oracle/product/11.1.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
    )
  )
ADR_BASE_LISTENER = /u01/app/oracle
The listener file was left unconfigured after the installation. No changes were made to it.

The first item to be removed is the IPC protocol (highlighted) as we are not connecting to Oracle via IPC.

Secondly, because I we have no DNS server and limited to the host and VM we will
change the HOST (highlighted) to a hardcoded IP of the VM..
In addition to this, we remove the ADR_BASE line to stop the listener from writing message logs to xml.

Lastly, we add the SID_LIST_LISTENER list to statically register the instance and allow for remote configuration of the database using Oracle Enterprise Manager.

The listner configuration now looks like this:
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.98.129)(PORT = 1521))
      )
    )
  )

SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME = LISTENER)
      (SID_NAME = ORCL)
     )
   )

All that needs to be done is to stop and restart the listener


[oracle@localhost bin]$ lsnrctl stop
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 01-AUG-2010 20:39:48
Copyright (c) 1991, 2009, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.98.129)(PORT=1521))(CONNECT_DATA=(SID=ORCL)(SERVER=DEDICATED)))

The command completed successfully

[oracle@localhost bin]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 01-AUG-2010 20:39:53

Copyright (c) 1991, 2009, Oracle. All rights reserved.

Starting /u01/app/oracle/product/11.1.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production

System parameter file is /u01/app/oracle/product/11.1.0/db_1/network/admin/listener.ora

Log messages written to /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.98.129)(PORT=1521))(CONNECT_DATA=(SID=ORCL)(SERVER=DEDICATED)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.98.129)(PORT=1521))(CONNECT_DATA=(SID=ORCL)(SERVER=DEDICATED)))

STATUS of the LISTENER

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

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production

Start Date 01-AUG-2010 20:39:53

Uptime 0 days 0 hr. 0 min. 0 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/oracle/product/11.1.0/db_1/network/admin/listener.ora

Listener Log File /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.98.129)(PORT=1521))(CONNECT_DATA=(SID=ORCL)(SERVER=DEDICATED)))

Services Summary...

Service "LISTENER" has 1 instance(s).

Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

*status UNKNOWN means the listener is statically registered.


 Now to connect remotely

Error

ERROR:

ORA-01034: ORACLE not available

ORA-27101: shared memory realm does not exist

SVR4 Error: 2: No such file or directory


Not good. Something is wrong. First thing to check is if its possible to ping the listener.

[oracle@localhost admin]$ tnsping ORCL
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 01-AUG-2010 21:43:46
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:

/u01/app/oracle/product/11.1.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.98.129)(PORT = 1521)) (CONNECT_DATA = (SID = orcl) (SERVER = DEDICATED)))

OK (10 msec)

No problem there. Next to check the logs.

Sun Aug 01 21:41:10 2010

01-AUG-2010 21:41:10 * (CONNECT_DATA=(SID=orcl)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=Ray))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.98.1)(PORT=4842)) * establish * orcl * 0

Sun Aug 01 21:43:16 2010
01-AUG-2010 21:43:16 * ping * 0
Sun Aug 01 21:43:41 2010
01-AUG-2010 21:43:41 * ping * 0
01-AUG-2010 21:43:46 * ping * 0
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
SVR4 Error: 2: No such file or directory


After much searching on the web, I found out that the problem is the SID in the listener is UPPER CASE.

(SID_NAME = ORCL)
Change it to:

(SID_NAME = orcl)

We restart and connect again

Listener Log File /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml

Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.98.129)(PORT=1521)))

Services Summary...
Service "LISTENER" has 1 instance(s).

Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@localhost admin]$


SELECT * FROM global_name

GLOBAL_NAME
ORCL.LOCALDOMAIN

Connected Successfully!



Friday, July 30, 2010

Basic RMAN Script

configure controlfile autobackup on;

run {
     shutdown immediate;
     startup mount;
     configure retention policy to redundancy 2;
     backup full database tag "_FULL";
     configure retention policy to redundancy 1;
     delete noprompt obsolete;
     alter database open;
     }

list backup;

Thursday, July 29, 2010

Install on Oracle Enterprise Edition 5

As root execute the following commands:

#Add groups
/usr/sbin/groupadd oinstall
/usr/sbin/groupadd dba
/usr/sbin/groupadd oper

#Create the operating system user oracle:
/usr/sbin/useradd -g oinstall -G dba,oper -d /home/oracle oracle

#Enter the following command to set the password of the oracle user:
/usr/bin/passwd

#With an editor of your choice, edit /home/oracle/.bash_profile  to include the following entries:
PATH=$PATH:$HOME/bin
umask 022
PATH=/bin:/usr/bin:/usr/local/bin:/usr/X11R6/bin
LD_LIBRARY_PATH=/usr/lib:/usr/X11R6/lib
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=$ORACLE_BASE/product/11.1.0/db_1
ORACLE_SID=orcl
$ORACLE_HOME/jdk/jre/lib/i386/server:
$ORACLE_HOME/rdbms/lib:$ORACLE_HOME/lib:$LD_LIBRARY_PATH
PATH=$ORACLE_HOME/bin:$PATH
ORACLE_LABS=/home/oracle/labs

export PATH LD_LIBRARY_PATH
export ORACLE_BASE ORACLE_HOME ORACLE_SID
export PATH

#Create the directory for the software installation and assign ownership to oracle:oinstall.
mkdir -p /u01/app/oracle
chown -R oracle:oinstall /u01/app
chmod -R 775 /u01/app

#Open the /etc/sysctl.conf file in any text editor and add lines similar to the following:

#The maximum number and size of semaphore sets that can be allocate
kernel.sem = 250 32000 100 128

# Controls the maximum number of shared memory segments, in pages
kernel.shmall = 2097152

# Controls the maximum shared segment size, in bytes
kernel.shmmax = 2147483648

#The maximum number of shared memory segments.
kernel.shmmni = 4096

#The maximum number of file-handles that the Linux kernel will allocate
fs.file-max = 65536

#The local port range that is used by TCP and UDP traffic
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 262144
net.core.rmem_max = 262144
net.core.wmem_default = 262144
net.core.wmem_max = 262144

#Issue the following command to set the kernel parameters:
/sbin/sysctl -p

Finally, ensure that the following packages are installed off the OEL cd’s.

#INSERT DISC 1
mount -t auto /dev/cdroom /media/cdrom
cd /media/cdrom/Server
rpm -Uvh binutils-2.*
rpm -Uvh elfutils-libelf-0.*
rpm -Uvh glibc-2.*
rpm -Uvh glibc-common-2.*
rpm -Uvh libgcc-3.*
rpm -Uvh libstdc++-3.*
rpm -Uvh compat-libstdc++-33*
rpm -Uvh make-3.*
rpm -Uvh unixODBC-2.*
cd /
eject

#INSERT DISC 2mount -t auto /dev/cdroom /media/cdrom
cd /media/cdrom/Server
rpm -Uvh glibc-devel-2.*
rpm -Uvh gcc-3.*
rpm -Uvh gcc-c++-3.*
rpm -Uvh libstdc++-devel-3.*
cd /
eject

#INSERT DISC 3
mount -t auto /dev/cdrom /media/cdrom
cd /media/cdrom/Server
rpm -Uvh libaio-0.*
rpm -Uvh libaio-devel-0.*
rpm -Uvh sysstat-5.*
cd /
eject

#INSERT DISC 4
mount -t auto /dev/cdroom /media/cdrom
cd /media/cdrom/Server
rpm -Uvh elfutils-libelf-devel-0.*
rpm -Uvh unixODBC-devel-2.*
cd /
eject

oracle 11g is ready to be installed.

You can install VM client tools to help share the iso across the network.
Use the vmplayer to do the install as putty can be disconnected.
Mount the VM Iso from the program files vmplayer  directory on the host.
gunzip the VMwareTools-8.1.3-203739.tar.gz file into the staging area under /u01/stage
then untar the file using tar -xvf VMwareTools-8.1.3-203739.tar.,
navigate to the vmware-tools-distrib sub directory and execut
./vmware-install.pl
Confirm all installer prompts messages.
Once installed you can start tools by running /usr/bin/vmware-toolbox.

To use adv GUI features, run /usr/bin/vmware-user and open and close your gui.

As the root, unzip or mount the Oracle 11g intall into a staging area.
Log in as the oracle user.
Navigate to the stage point and either execute ./runInstaller from the oracle user desktop
or xwindows.

To execute Oracle Universal Installer in the host environment, install cygwin xwindows,
open up an xwindows session and input xhost +
From the server, as the oracle user input the following into a terminal

export DISPLAY=:0.0

Finally execute ./runInstaller

After a minute, the oracle universal installer (OUI) should bubble up to the client desktop.

Wednesday, July 28, 2010

File Types and Locations

Control Files

A control file is the root file which  maps out the other files which make up the the physical structure of the database.
Information stored includes:

  • Database name and DBID

  • Tablespace information

  • Path information about datafiles, online redo log files, and archived redo log files

  • Timestamp of creation

  • RMAN backups


This file is required to open the database and serves to keep track of structural changes such as adding,  renaming, and dropping datafiles.
The file contains metadata which is required when the database is not open.
This data includes important checkpoints (stored SCN Number) which holds the point from where database can recover from in event of failure.
Commited transactions occuring before the checkpoint(SCN ###) will have been safely written to datafile.

Every 3 seconds, the control file is updated with the latest checkpoint position in the online redo logs.

Diagram - Control File

Data Files
Oracle stores user and application data in datafiles. A datafile can constitute a single tablespace.
A tablespace can be made up of multiple datafiles.
A segment can span many datafiles but only one tablespace.
A database MUST have a SYSTEM and SYSAUX table space.
Oracle usually assigns the first datafiles to these tablespaces.
The SYSTEM holds  the datadictionary, while the SYSAUX holds statistic information

Mount

mount /dev/cdrom /media/cdrom

Linux Network Commands

#Get current IP
ifconfig

#Release
dhclient -r

#Get new ip
dhclient

#Restart network
/etc/init.d/networking restart

Monday, July 26, 2010

SGA Buffer Cache States

Buffer Cache blocks can be in the following states

  • Pinned: Prevent writing to the same block from multiple sessions

  • Clean: Unpinned, candidate for aging

  • Free or Unused: Empty because instance just started

  • Dirty: Buffer not pinned, content changed, DBWn will write to disc


A more details view of the buffer cache can be seen using the v$bh view.
SELECT b.block#,
b.class#,
b.status,
object_name,
object_type,
dirty Dirty
FROM v$bh b,
dba_objects o

WHERE b.objd = o.data_object_id
AND o.owner = 'RAY';

BLOCK# CLASS# STATUS OBJECT_NAME OBJECT_TYPE DIRTY
51425      4 xcur   STF         TABLE       N

51427      1 xcur   STF         TABLE       N

51426      1 xcur   STF         TABLE       N

49328      1 xcur   NOTES       TABLE       N

49325      4 xcur   NOTES       TABLE       N

49327      1 xcur   NOTES       TABLE       N

49326      1 xcur   NOTES       TABLE       N

XCUR state means the instance has exclusive access to the 
buffer block and can modify it.
CR state means the instance has older version of the block
and can perform a consistent read of the block
SCUR state means the instance has shared access to the block
and can read only
PI state means the instance has made changes to the block
but holds copies of the block prior to changes.

Network Services

Oracle features an active background process called a 'Listener' which can be configured through listener.ora found at the default location $ORACLE_HOME/network/admin/

A service request packet is sent from the client to the address specified in the calling clients TNSNAMES.ORA file. If a listener exists at the destination address, the listener searches through its own list of available services and if the requested service is valid, the LISTENER spawns a server process to handle the request from there on. The server process proceeds to authenticate the call, pass on parameters and spawn a oracle session for the calling client.

From here on, the server process acts as the clients agent on the server by performing:
-SQL Parsing
-Checking the DB Buffer cache for blocks required to perform actions
-Reading data blocks from data files
-Managing Sorting
-Returning results to the user process
-Reading auditing options and reporting

Processes

Oracle Instance Management - Processes

-(SMON) System Monitor : Crash recovery

-(PMON) Process Monitor : Clean up resources

-(DBWn)  Database Writer : Write buffer cache to database file

-(CKPT)  Write the latest checkpoint to data file header and control files

-(LGWR) Write the log buffers to redo log files on disc. In RAC, each RAC has its own
LGWR process

-(ARCH) Writes filled redo logs to archive log locations.