Monday, August 24, 2009

Characterset Woes

Ever create a database with a characterset only to find out later the application requires something different. OK, so now what, recreate the database? Change the character set?
Changing the characterset is definitely an option but there are some hoops to go through to make this happen. Depending on when it is discovered that a different characterset is needed recreating the database is a valid option.
So, since there are issues and things to work through with charactersets, lets go through some basic discussions to have to decide what characterset to use first. With international databases and several platforms offering national characterset datatypes, there are several combinations and charactersets to choose from. I was of the mindset to just use the current UTF8 version and then set the varchars big enoug to handle any language that comes its way. Now this might work for an application where there is discussion about the datatypes and control over the code with the developers, but for reporting and other applications sitting on top of the database this might not be the best approach. Make sure to double check and maybe even ask again with the vendor to know which database characterset and which national characterset is needed. Also, when looking at what characterset to use the Oracle Globalization documentation does provide some helpful hints as well as thinking about supersets in planning if you have to change.
With great planning or possibly needing to use an existing database, a characterset change might still be needed. There are several good notes out there and tricks on how to do this, but I thought I would add my quick checklist to here to help out where possible, since I just went through this pain. In my case I have existing databases that now the NCHARs and NVARCHARs will be used and the vendor has a specific national characterset that is needed.
I decided that I didn't want to recreate the database and do an export and import to switch over, but checked to make sure that NCHARs, NVARCHARs and NBLOBs (NCLOBs) etc. are not being used currently. So there are no values here from a user perspective but might be some in the system tables. If there were any N-values then export these tables and truncate them. It is not a problem to have them in the database but if there are values populating these columns are the problem. The characterset that was needed is a strict superset of the current characterset and again the Oracle documentation will provide a list of which charactersets can change to others.
Now it appears that it is just a quick alter database national character set NEW_CHARACTERSET, right? Probably not. Additional checks are needed. Also, assumptions here are being made, that a spfile is being used, RAC clusters are altered to single instance mode to change the characterset and the checks of the data types being supported in the new characterset has been completed.
XBD tables use N-data, and this can be truncated if under 7 rows are in the tables xdb.XDB$QNAME_ID and xdb.XDB$NMSPC_ID (open a case with Oracle with more than 7). These are the tables that caused me a lot of headache because I kept getting the ORA-12717: Cannot issue ALTER DATABASE NATIONAL CHARACTER SET when NCLOB, NCHAR
or NVARCHAR2 data exists, and wasn't sure where it was coming from.
After dealing with this data, run the csscan FULL=Y TONCHAR=UTF8 LOGcheck CAPTURE=Y ARRAY=1000000 PROCESS=2 as sysdba.
Shutdown the database and startup in restrict mode. Other parameters that need to be set job_queue_processes=0 and aq_tm_processes=0, then ALTER DATABASE NATIONAL CHARACTER SET NEW_CHARACTERSET, run $ORACLE_HOME/rdbms/admin/csalter.plb.
New character should now be set, and then the next steps are just to put things back the things that were changed to make this happen.
Set job_queue_processes and aq_tm_processes back to the original values, and then shutdown and startup. Don't forget about the data in the XDB tables which can be inserted from $ORACLE_HOME/rdbms/admin/catxdbtm.sql.
Are you now understanding why I started this off with choose your characterset wisely? There are several steps that are needed for the change as well as knowing that the database is able to change over and data is either not there or able to export to make it happen. These are just some of the highlights that I ran into going through these steps which will hopefully help someone out with their next characterset change.

Friday, August 21, 2009

Change Controls and Audits

Some of the day to day things we do as database administrators are not completely understood by people that might be reviewing the change or auditing the changes. So, for them a rebuild of an index or analyzing statistics might not be as straight forward. And are these even considered changes in the databases and why would they need change controls around them? Well, even adding space to a tablespace could cause trouble on the database. It would have to be a really bad, but it is possible to mistype where a datafile is supposed to go or fill up a file system with the wrong size information (thank goodness for resize). Needless to say the things we do against the database even though minor can have impact on a system and maybe reviewed by a change board because of the process controls for compliance.
Now in going back and considering that someone reviewing a minor change may not have the information or experience about what that change does, and analyzing statistics could mean something very different in their world, why not provide them with some basic information. Instead of submitting a change, rebuild indexes and leaving it at that, state: Rebuilding indexes online to reduce fragmentation of the index space usage for better performance of the indexed data. This does not change any of the data within the table or index, just reorders it again for quicker access and this can occur while users are accessing the system. Or same with statistics, updating table statistics which provide Oracle the information about the table, such as row counts, how many distinct values, indexes and more information about the type of data to develop a good query plan to access the data as efficiently as possible.
Just a little more details about why and what is changing, and honestly makes the change a little less scary. It also provides information about data changes, which from a SOX perspective is very important if a task a DBA does is changing data. Now, as DBAs, we don’t want to have the responsibility of changing any data, but people reviewing changing and verifying processes might just need the verification of the task that is performed is not doing that. They might know that system types permissions may allow for that, so more details that can be provided about a change is useful.
This also comes to patching and applying CPUs (Critical Patch Updates). In reading the release notes and understanding the areas that might be affected, and providing some basic information about that. For example, there is a security fix that might touch a type of driver connection, so testing in the implementation of the patch includes the testing of the connection to the database through this driver and verifies that all connects are still good. Or even stating, the application doesn’t connect through this driver, so there is no effect with this change. However, still as part of a test plan there is probably connection testing from the application. Test plans can reflect the details of the security fixes, or just a quick description of the issues being fixed with some more basic information can really help when approving a change or reviewing and validating a change is what it is.
So, words that are thrown out between DBAs, rebuild, statitics, CPUs, might have a different meaning to others outside of the world we live but are needing to review or approve changes we make. More details or providing some basic training on what some of these simple, minor tasks performed again the database will help bridge that gap. Both sides will benefit from understanding the change for approvals and validation of the processes being followed.

Monday, August 3, 2009

Never under estimate a backout plan

Every well planned and thought out change could be implemented without problems in several environments. But it only takes small issue, a missed step or something that wasn't completely tested to cause an issue. Following a process to implement a change is important, but knowing what steps change be recovered from or rolled back are extremely important.
Can a step be repeated without an issue, what happens if you have an error after a step and the all dreaded forgetting a step? Checks through out the process and knowing if an error there means redoing everything or just running something to fix it at that spot will help prevent larger issues. Being able to isolate a change and know where the errors could come from will help solidify the change process and make a more robust implementation.
If this happens, then I have options to backout the change, and here are my steps to do that. If the change doesn't work or completely fails, I have a backup to restore and either start again, or live to try another day.
I could have applied this patch in 20 environments the exact same way, but run into issues where the code was different or parameters were slightly off, and it causes an issue, so how do I remove the patch, and what needs to be run afterwards to clean it up.
Compliance and IT processes should include test plans so you know what you need to test to validate the change as well as what you need to do to back out the change. Good backup strategies are also key here and understanding how long after the change the backups are still valid. Knowing how to put the database back to before the change would help if you have already hit that point of no return on the backups.
Implementing changes in databases can be a difficult process or it can be planned for the unexpected issues. Having test plans that hit the critical areas are important, and because of sizing and other factors, even the best test plans are not going to test everything all of the time. Being prepared that even if it is the last database for the change, something could go wrong and needing to revert the change might be inevitable. Steps created before the change, and then even testing that before applying the change in the all of the environments will elimate some of the fear of rolling out changes. Keeping the databases stable, available and productive after a change means good planning and being prepared in this area.