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

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

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:

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!

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;