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 

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

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