Peasland Database Blog

Every once in a while, no matter which company I work for, I am asked to set up a new production database. I was working on this very task today when I started thinking about how much work it was to create a brand new database in that past, how much the DBCA handles for us today, and how much is still left to do.

Currently, we have a development and test database for our third party application. We will be rolling out the application to production by the end of the week so I was tasked with setting up a production version of this database. The production database server is a 3-node RAC cluster which has already been set up for me because we are currently running two other databases on the cluster. So this saves me the step of installing and configuring Grid Infrastructure and the RDBMS software. But as I started setting up the database, I got to thinking about how much work I still had left to do. And since we rarely set up brand new production databases, some of these tasks are not as easily remembered as others. Below are the steps I went through today to get the production database up and running.

1. Using the dev/test databases as my guide, I determined my memory and disk storage requirements.
2. I verified the production RAC cluster had enough memory to support the new database instances.
3. I worked with my Storage Admin to get the necessary disk storage mounted on the cluster.
4. I then fired up the DBCA to create the brand new database. I walked through the wizard and filled in appropriate values and then let DBCA do its magic.
5. I really do not like how the DBCA lets me create/allocate redo logs so after the database was created, I created my own redo logs groups (multiplexed of course) and dropped the redo log groups the DBCA created for me.
6. I can never figure out how to add a 3rd control file in the DBCA. So after the database is created, I shut it down, make a 3rd copy of the control file, update the SPFILE with the fact that there are now 3 control files and start the database.
7. The DBCA put my password file and spfile in locations that are not optimal for me. So I moved them. In $ORACLE_HOME/dbs I created softlinks pointing to the new locations. Then I used srvctl to update the spfile location in CRS.
8. I’ve never once used the DBCA to set up archivelog mode. So I always skip that part of the DBCA. In addition, I like the idea of not archiving my redo logs when the DBCA is creating the database to speed up that process. So at this point, I set up archive logging for the database.
9. The database will be used with a Standby and I like to ensure I have a log switch at least once per hour, so I set ARCHIVE_LAG_TARGET to 3600.

At this point, the bare bones database is set up and ready for use. Now its time to get the database read for our application.

10. I set up any required tablespaces for the application.
11. I set up any required users for the application.
12. Changed the database’s default tablespace to one of the ones I created above. Then dropped the USERS tablespace.
13. Since this is a RAC database, we need to set up the service for the application to connect.

14. Now that the database is ready for the application, we need to set up the Standby database. This was easily done by using the Add Standby Database wizard in Grid Control.
15. Our Standby database is on a 2-node RAC cluster. The Add Standby Database wizard creates a single-instance database so the Convert to Cluster Database wizard was run in Grid Control to make the Standby a RAC database.

Finally, the last step was to ensure any maintenance tasks were extended to the new database. For example, cron jobs to delete old log files needed to be modified for the new instance.

Wow! That’s a lot of work to set up an initial database in our production environment. As I said in the beginning, the DBCA does a lot of work for us now. And Grid Control automates a lot of the Standby creation work as well. But there is still a lot of steps involved.

I swear that as I get further and further along in my Oracle career, I have a harder and harder time keeping up with Oracle’s background processes. I’d like to chalk this up to getting older, but after digging into it, I’m pretty sure its just Oracle’s explosion of background processes in 11g that is causing me fits.

Earlier today, I was watching a production database performance tab in Grid Control. My 3-node RAC cluster was experiencing high CPU so I drilled down and noticed that the majority of the CPU cycles were from a background process named NSA2. What?!?!?! What is NSA2? I’m pretty sure this has nothing to do with national security.

In doing a Google search of this background process, I came across a nifty query to give you a quick description of each Oracle background process. You will need to run this as SYS since it queries X$ tables:

column EXTERNAL_NAME format a13
column INTERNAL_NAME format a13
set lin 120
SELECT x$ksbdd.ksbddidn AS external_name,
x$ksmfsv.ksmfsnam AS internal_name, x$ksbdd.ksbdddsc AS description
FROM x$ksbdd, x$ksbdp, x$ksmfsv
WHERE x$ksbdd.indx = x$ksbdp.indx AND x$ksbdp.addr = x$ksmfsv.ksmfsadr
ORDER BY 1;

In my 11.2.0.2 database, this query returned 296 rows! No wonder I can’t keep up. I remember when I could list all of the background processes off the top of my head (SMON, PMON, ARCH, DBWR, LGWR, CKPT, and RECO). Each new Oracle version increases the number of background processes, which is probably a good thing. But it makes it difficult for the DBA to diagnose problems sometimes.

So what is NSA2? Its a redo transport process responsible for shipping archived redo logs to my Standby database. Once I saw the description from the query above, it was obvious.

So take a read in your database to see all of the wonderful background processes you may meet on your journeys. One of my favorites in the list is TEST.

In the past, one would use the PCTVERSION storage parameter for their LOB segments to reserve a percentage of storage space for read consistency of LOB segments. In Oracle 11g, you can now use the RETENTION parameter. The RETENTION parameter will use the UNDO_RETENTION parameter for determining how long to keep LOB data for read-consistency purposes. But be advised that it does not use the Undo tablespace! The LOB segment’s tablespace is used for read-consistency purposes.

When you change the UNDO_RETENTION parameter, the LOB segment’s retention value is not modified. If you query the RETENTION column of the DBA_LOBS view, you will notice the old UNDO_RETENTION value still remains after you have modified the UNDO_RETENTION parameter. To change the LOB segment’s RETENTION value to match the new UNDO_RETENTION value, do the following:

ALTER TABLE my_table MODIFY LOB (lob_column) (PCTVERSION 20);
ALTER TABLE my_table MODIFY LOB (lob_column) (RETENTION);

By momentarily changing the LOB to use PCTVERSION and then back to RETENTION, the new value of UNDO_RETENTION will be used. You need to do this for all LOB segments that you intend to modify.