Oracle NZ - Francisco Munoz Alvarez

I want to start this New Year creating a new session on my blog called “DBA Tip of the month” where I will try to post every month an important tip to help all fellows DBAs to improve their careers.

The first topic of this session will be:
 

“Being Proactive with Backup & Recovery”

Always when I arrive on a new client I ask the DBA on charge the following questions:

• Do you have your recovery strategy documented step by step?
• Are you 100% sure that your tape backups are usable?
• Do you know exactly how long a recovery on your production environment will take if necessary?

And almost 90% of the time the answers will be:

• No!
• I not sure, but I think so!
• No idea, probably…!

You will be on chock to know how many times I’m call to support a DBA to try to recover a Database because the most current tape backup is unusable!

Backup & Recovery are a very important (crucial) part of a DBA role, as a DBA I’ll never be stressed enough to repeat over and over what in my opinion is the most important rule for a DBA:

“The most important rule with respect to data is to never put yourself into an unrecoverable situation.”

You know, because bad stuff happen….

…When you less expect, and due to this, I’ll always recommend a DBA to perform a proactive approach to his/her Database Backup and Recovery strategy.

The main idea is:

1. Randomly choose a backup tape and recovery it on a test machine (It can be a virtual one).
2. Take this opportunity to document all the recover process.
3. Review the entire process ant try to improve it!
4. Repeat this exercise every month and try to involve other DBAs in the process!

This easy process will allow you to:

1. Test your Tape backups and see if they are being backup correctly.
2. Check and improve your recovery knowledge and strategy.
3. Document all your recovery process that could be used for any other DBA in the company in case you are not available in the recovery situation.
4. Detect any error on your backup & recovery strategy.
5. Know your recovery time. Next time your manager asks you” Do you know how long a recovery will take? You will know the exact answer.
6. Have an opportunity to review your process and try to make it more efficient.

Like you can see, this is an easy proactive exercise that will allow you and your company to be prepared in case of a disaster and recovery situations occurs, and you know when this always happens….
 

Cheers,

Francisco Munoz Alvarez

DB_ULTRA_SAFE is a new parameter introduced with Oracle 11gR1, and a fantastic new GEM for High Availability, that using Data Guard to configure on both the primary and standby will trigger the most comprehensive data corruption prevention and detection (and repair on 11gR2, see **) tool in the market.

** Starting in Oracle Database 11g Release 2 (11.2), the primary database automatically attempts to repair the corrupted block in real time by fetching a good version of the same block from a physical standby database.

Speaking simple, what this new functionality will do is use your Standby Database as a backup to correct automatically any data corruption on your primary database and vice-versa (again on 11.2).

The DB_ULTRA_SAFE initialization parameter also controls other data protection behavior in Oracle Database, such as requiring ASM to perform sequential mirror write I/Os.

You basically need to understand that when setting DB_ULTRA_SAFE it will control the behaviour of DB_BLOCK_CHECKING, DB_BLOCK_CHECKSUM, or DB_LOST_WRITE_PROTECT parameters for you, which mean:

Lets’ Check  all the parameters affected by DB_ULTRA_SAFE:

• DB_BLOCK_CHECKING (Introduced with Oracle 8.1.6) prevents memory and data corruptions, but it incurs some performance overhead on every block change.
• DB_BLOCK_CHECKSUM (Introduced with Oracle 8.1.6) detects redo and data block corruptions and detect corruptions on the primary database and protect the standby database. This parameter requires minimal CPU resources.
• DB_LOST_WRITE_PROTECT (also introduced with 11gR1) enable or disable a physical standby database to detect lost write corruptions on both the primary and physical standby database.

Important: if you explicitly set the DB_BLOCK_CHECKING, DB_BLOCK_CHECKSUM, or DB_LOST_WRITE_PROTECT parameters in the initialization parameter file, then the DB_ULTRA_SAFE parameter has no effect and no changes are made to the parameter values. Thus, if you specify the DB_ULTRA_SAFE parameter, do not explicitly set these underlying parameters.

To activate it, all you need to do is follow the following steps:

On the Primary Database:  

1. Set the DB_ULTRA_SAFE=DATA_AND_INDEX initialization parameter using:
2. SQL> alter system set db_ultra_safe=dta_and_index scope=spfile;
3. SQL> shutdown immediate (Shutdown your Database)
4. SQL> startup (This will start your primary Database using your new parameter set in the SPFILE previously)

On the Physical Standby Database: 

1. Set the DB_ULTRA_SAFE=DATA_AND_INDEX initialization parameter using:
2. SQL> alter system set db_ultra_safe=dta_and_index scope=spfile;
3. SQL> startup nomount
4. SQL> alter database mount standby database;
5. SQL> alter database recover managed standby database disconnect from session;

If you are using your Standby Database on Read Only mode you also need to run the follow commands on your Physical Standby DB: 

1. SQL> alter database recover managed standby database cancel;
2. SQL> alter database open read only;

If you decide to change later the Read Only Standby to Standby again, you just will need to run the following command: 

1. SQL> alter database recover managed standby database disconnect from session;

Hoping this information could help you in the future,

Francisco Munoz Alvarez

Example done on primary:

Example done at the Standby:

Technorati Tags: DB_ULTRA_SAFE,HIGH AVAILABILITY,BLOCK CORRUPTION,DATA CORRUPTION

Let start this New Year with a new mentality or a new resolution if you prefer call this way, let stop being reactive and start being proactive. Being proactive will reduce your database administration costs and increase your level of efficiency, and best of all, save your hours of sleep when you are on call.

Why check the problems only when they are critical, or when is too late and the database is down, or the users are screaming?

Being proactive is the best approach to keep your DB healthy and to show your company, or your clients that you really care about them.

Many DBA’s expend most of their time being firefighters only, fixing problems and working on user’s requests all the time. They don’t do any proactive work; this mentality only will cause an overload of work to them, thousands of dollars of overtime, several hours without access to the data to the users,  poor performance to the applications, and what is worse of all, several unhappy users thinking that you doesn’t have the knowledge needed to take care of their data.

Let’s mention a small example, you have the archive log area alert set to fire when it is 95% full, and this happens in the middle of the night, some DBA’s will take seriously the alert and solve the problem quickly, others will wait until the next day to take care of it because they are tired, or sleeping, or they are in a place without internet access at the moment the alert arrived. Will be a lot easier if they set a proactive alert to be fire when 75% or 85%, or even better ,take a look in the general health status of the DB before leave their work shift, to try to detect and solve any possible problem before be a real problem and be awake in the middle of the night or during the weekend (Remember how important is your personal and family time). I’ll always recommend to DBA’s to run 2 checklists daily, one in the start of their shift and other before they leave their shift.

I know several DBA’s that complain all the time that they got so many calls when they are on call, but they don’t do anything to solve the root problem, they only expend their time to solve the symptoms.

Here you can find an Oracle checklist script that will help to make your life a little easier (This is not my complete script, but will be a good start for you). This script is a compilation of several normal checklists and you can setup them with your own requirement and thresholds and always remember to have a baseline to compare. This script will not only help you to detect future or current problems, but also will help you to detect possible tuning requirement.

Here is an example of the script first phase outcome:

– ———————————————————————– –
– Oracle Instance Information
– ———————————————————————– –
Cpu_Count                                    4   |      Host_Name             OLIVER
Instance_Name                      prod   |      Database_Status    ACTIVE
Status                                     OPEN   |      Startup_Time        10-01-2009 19:50
Version                            11.1.0.7.0   |      Instance_Role        PRIMARY_INSTANCE
Database Space (Mb)       36604    |     SGA (Mb)                 511
Nb. Datafiles                                 43    |    Nb. Tempfiles          1

Archive destination LOCATION=E:\oracle\oradata\prod\archive
Database log mode ARCHIVELOG
Background Dump Dest d:\oracle\diag\rdbms\prod\prod\trace
Spfile D:\ORACLE\PRODUCT\11.1\PROD\DATABASE\SPFILEPROD.ORA
Redo size (Kb) 102400

– ———————————————————————– –
– Instance CheckList –
– ———————————————————————– –
Instance Status                                  OK    |      Listener Status      OK
– ———————————————————————– –
– Performance Memory CheckList –
– ———————————————————————– –
Total Sessions < 700                           OK - 19
Active sessions number <15               OK - 9
Data Buffer Hit Ratio > 80                   OK - 97
L.Buffer Reload Pin Ratio > 99         OK - 99
Row Cache Miss Ratio < 0.015         NO - 1.351
Dict.Buffer Hit Ratio > 80                     OK - 99
Log Buffer Waits = 0                             NO - 110
Log Buffer Retries < 0.0010               OK - 0
Switch number (Daily Avg) < 5          OK - 1
Jobs Broken = 0                                     OK 0
Shared_Pool Failure = 0                    OK - 0
– ———————————————————————– –
– Storage CheckList                                            –
– ———————————————————————– –
Dba_Tablespaces Status                   OK   |       V$Log Status             OK
V$Datafile Status                                  OK   |       V$Tempfile Status   OK
V$Recover_File                                    OK   |       V$Recovery_Log      OK
Tablespace in Backup Mode = 0     OK - 0
Tablespace < 95%                               OK- 0
Objects Invalid = 0                                NO - 147
Indexes unusable = 0                          OK - 0
Trigger Disabled = 0                            NO- 5
Constraint Disabled = 0                       NO - 2
Objects close max extents = 0           OK - 0
Objects can not extent = 0                  NO - 552
User Objects on Systems = 0              NO - 26
FK Without Index = 0                           NO - 138
– ———————————————————————– –
– Datagard CheckList                                          –
– ———————————————————————– –
Datagard Errors = 0                               OK- 0
Datagard Gap = 0                                OK - 0
Archives not Aplied < 5                       OK - 2
– ———————————————————————- –
– Installed options :
– ———————————————————————- –
- Objects option
- Connection multiplexing option
- Connection pooling option
- Database queuing option
- Incremental backup and recovery option
- Instead-of triggers option
- Parallel load option
- Proxy authentication/authorization option
- Plan Stability option
- Coalesce Index option
- Transparent Application Failover option
- Sample Scan option
- Java option
- OLAP Window Functions option

You also have several tools available in the market that can help you to monitor and setup your DB alerts, and help you with the proactive monitoring like: Grid Control, Enterprise Manager, Insider (FourthElephant), Spotlight (Quest) or if you prefer, your own scripts. The idea is to use them always on a proactive way, never reactive.

Let’s change our mentality, let stop being a firefighter and start to be a real hero!

Cheers,

Francisco Munoz Alvarez

About the DBA job: 

Most of the people that I talk to who have difficulties starting out in their DBA career really have an issue trying to absorb the mountainous volumes of information that a DBA needs to know. After all, System Administrators make a whole career out of learning the ins and outs of the OS. Application Developers make a whole career out of learning how to build and code excellent applications. Not only does a DBA have to know a great deal about these two different jobs, but then the DBA needs to spend even more time working on understanding the architecture of the database, and understanding how every piece of everything fits together! Does it sound too daunting of a task? There have been many who think so and after becoming frustrated, have left their DBA job for a completely different job. Then there are those individuals who thrive on disseminating and understanding all of this information, and using that information to make good, sound technical decisions. As I was fond of saying in my early days as a DBA, it all seems to me to be one great puzzle. The challenge is in getting all of the pieces to fit. Which type of person are you? 

Many DBAs are “on-call”. They get called at all hours of the day and night to resolve critical problems with their database. The database is the life-blood of the business’s IT infrastructure. Without data, there would be no need to have a computer system. It is the data that drives the business. Where would amazon.com be if their web site couldn’t search the database for products and if no one could place an order for their products? They wouldn’t be in business for very long. There are many companies that lose significant revenues when their database is down, even for the shortest period of time. For this reason, the DBA needs to be available to resolve issues as fast as possible, should they occur. Many shops have a team of DBAs who rotate being on-call. These DBAs support databases for 24×7 applications. Are you ready to be placed on-call if the job requires it? Some of the DBAs duties include applying patches to software or making database changes. Often times, these changes cannot be done while the company’s employees are at work, expecting that the database be up and running so that they can get their jobs done. This means that the DBA frequently has to come in real early in the morning, or real late at night, or even on the weekends to perform work that can only be done outside of normal business hours. Are you ready to work some strange hours at times, or are you looking for a 9 to 5 job? 

One key asset for a DBA to hold is what is commonly referred to as “soft skills”. The DBA needs to be able to work well in a team environment, commonly in diverse teams with System Administrators, Network Administrators, Application Developers, Project Managers and others. DBAs need to be able to explain difficult, technical concepts in plain English that others in the team environment can understand. DBAs need to be able to direct team members on database-related issues. How are your soft skills? While not an all-inclusive list, typical DBAs perform the following duties: 

• Monitor database instances on a daily basis to ensure availability. Resolve unavailability issues.
• Collect system statistics and performance data for trending and configuration analysis.
• Configure and tune dB instances for optimal performance under application specific guidelines.
• Analyze and administer dB security. Control and monitor user access to dB. Audit database usage when necessary.
• Monitor backup procedures. Provide recovery when needed. Develop and test backup and recovery procedures.
• Upgrade RDBMS software and apply patches when needed. Upgrade or migrate database instances as necessary.
• Support application developers with any and all dB related activities.
• Keep up with dB trends & technologies. Use new technologies when applicable. Install, test, and evaluate new Oracle related products.
• Perform storage and physical design. Balance design issues to achieve optimal performance.
• Create, configure and design new dB instances.
• Diagnose, troubleshoot and resolve any dB related problems. Work with Oracle Support if necessary to bring problems to a successful resolution.
• Ensure Oracle networking software (SQL*Net, Netx, Names, OiD) is configured and running properly.
• Work with System Administrators (Unix & NT) to ensure Oracle related matters are handled properly.
• Create any necessary scripts for effective and occasionally periodic dB maintenance activities.

Tips for your starting Career: 

Tip #1:Become educated. – Learn as much as you can you can about a database. This will most likely involve some time and effort on your part, outside of normal working hours. Take a database class at a local college or university. Many training companies offer classes on Database Administration. You may find that you have to pay for these yourself if your employer will not fund your education opportunities. Many DBA positions require at least a Bachelor’s degree in Computer Science or a related field, so you should have at least that credential. 

Tip #2: Practice being a DBA. – Many database vendors let you download trial, test, or evaluation copies of their database system. Download a copy and install the software on your own personal computer. Play with the database. Intentionally break the database and try to fix it. Try to perform as many of the DBA functions as you can think of. Test out and hone your skills on your own test platform so that you can be able to demonstrate some level of database administration ability. 

Tip #3: Get certified – Many database vendors now offer a certification for their database product. Many companies now look at certification as a measuring stick. One thing to keep in mind is that just being certified is not enough. Passing DBA certification tests do not automatically mean that you know how to administer a database. They just say to the potential employer that you now possess a certain set of skills. Being certified also tells a potential employer that you are serious about your pursuit of a DBA job. I’ve seen many people complain that they are certified with no experience, but still can’t get that first DBA job. Certification alone won’t land you the job, but it doesn’t hurt either. If nothing else, you’ve learned a great deal while trying to get certified. Just don’t rely on the certification to get you that job you are looking for. You will need more than that. But it will help in the end. 

Tip #4: Leverage your existing skill set – Many DBAs come from a System Administrator background. Others come from an Application Development background. If possible, see if you can use your existing skill set to get a job. The goal here is to make it a win-win situation for you and your employer. For instance, let’s assume that you are already a SysAdmin looking to break into the DBA field. Maybe you can find a job at a company that will be able to use your SysAdmin skills part of the time, while being able to get your feet wet in Database Administration the rest of the time. If you are already a DBA on one vendor’s platform but wish to move to another vendor’s platform, see if you can land a job which has both platforms. For instance, use your SQL Server DBA skills in a shop that also lets you backup the Oracle DBA. In this way, both the company and you get what you want. After you’ve had exposure to DBA work, you can try to get a position that will let you do it full time, maybe even with the same company.  

Tip #5: Take advantage of current opportunities – Sometimes, one gets into the DBA field just by being in the right place at the right time. If your current employer has an opportunity for you to work on any database project, jump at the chance! Any database experience is worth more than no database experience. Let your management know that you are actively seeking any database opportunities that come by. Hopefully, they will think of you when the next one comes along. After working on these database projects and seeing the desire in your eyes to become a DBA, they may decide to train you, and promote you. Many, many people get their first DBA job in exactly this manner, sliding into a Junior DBA position once they have worked on a few database-related projects. Often times, when a DBA leaves the company, that company will look at hiring an internal candidate if they feel that candidate is trainable. 

Tips to Become a Good DBA: 

You will find that there is an enormous amount of material that you must learn to become an effect Database Administrator. Your first year or two will be spent learning more than you may have ever learned in your career. If you find that the amount of information is leading to brain overload, just sit back, take a breather, and come back to it. To help you along the way, you can follow the roadmap below: 

Tip #1: Relational database theory – For this paper, I’m going to assume that the type of database you will be administering is a “relational” database. Other database models do exist, but the relational model is the dominant one in the industry for the last twenty years. If your database system follows a different model, then learn that theory. Relational database theory is very important. It is the background upon which everything has been built. I’ve seen many people who make the jump to database administration and never bother to learn solid relational database theory. Inevitably, their lack of a solid basis in this theory shows up as a shortcoming many times during their career. If you understand relational database theory well, then you will be able to make smoother transitions to any vendor’s Relational Database Management System (RDBMS). It doesn’t matter if I am using Oracle’s database, or IBM’s DB2, or Microsoft’s SQL Server. All of them are relational database systems. They all do basically the same things. The difference lies in how they do the same things. A solid relational database theory is not essential for a Junior DBA position. But it is vital if you ever want to grow your career past the Junior DBA level., also many college-level textbooks cover relational database theory very well.  

Tip #2: Learn the query language inside and out – Databases all have a language that lets you get data from the database, put data into the database, and modify the data that is in the database. For relational databases, that language is Structured Query Language (SQL). This one language is your tool to interface with the database. It is vital that this tool not be a barrier to further learning. In your test database, practice various SQL statements until they become second nature to you.  

Tip #3: Begin learning basic database administration functions – Isn’t this why you are here in the first place? So why is it third on the list? We are trying to build a pyramid of knowledge and I feel strongly that one needs to know relational database theory and SQL real well as they will become tools that you will use as you learn how to perform basic database administration functions. These functions can include starting and stopping a database, backing up and recovering a database, and creating/dropping/altering database objects. For Oracle database administration, there is a lot of material on oracle.tahiti.com that can gives you a good taste of what to expect. At this time, you should also be reading and understanding the Oracle 9i/10gR2/11g Concepts Guide, the Oracle 9i/10gR2/11g Administrator’s Guide, and the Oracle 9i/10gR2/11g Backup and Recovery Guide, all from the Oracle documentation. 

Tip #4: Read, read, and read – Since you just started your career as a DBA, you are just beginning to build a skill set. It takes a long time to build, absorb, and comprehend all of the information you will be learning. Undoubtedly, your Senior DBA will have work to do, so he or she will not always be able to devote a ton of time to your studies. You will have to learn many things on your own. This is where reading comes in. There are many books on the market, which answer a lot of database related topics. Oracle Press is Oracle Corp’s official publishing company with a large number of Oracle-related books. There are other publishing companies as well, like Wrox Press, Rampage, Apress and O’Reilly Press. You also have the Oracle documentation to read. And there are numerous web sites and newsgroups available as well. Read as much as you can get your hands on. And it’s not a bad idea to read these items more than once to absorb things you may have missed the first time. 

Tip #5: Create test cases – I often see beginner questions that ask the most basic questions that can easily be answered if the person just took the time to figure it out themselves. Undoubtedly, you will have many questions as you begin your Oracle studies. Decide if these are questions that you can answer yourself. For instance, I once had someone ask me if it was possible to insert NULL values into a column with a UNIQUE constraint. At first, this may not seem to be an easy question to answer. But it is really easy to test! Just create a simple table. On one of your columns, enable a UNIQUE constraint. Try to insert NULL values into that column. Does it work? You should be able to answer this question quite easily. So why create these test cases? One reason is that by doing so, you will be enhancing your problem solving skills. The same skills required to create these test cases are some of the same skills used in problem solving. Problem solving skills will greatly help your DBA career. Another reason is that you will often need to create more complex test cases as your career progresses in order to guarantee database and application success. Even simple test cases are building blocks for more complex database and application analysis in the future.  

Tip #6: Find a mentor – A mentor can be used to guide, or steer your DBA career (or any career for that matter). They can give pointers, answer questions, and help save some time as you grow your DBA career. Hopefully, this paper will serve as a mentor towards part of growing your career. If you are working in an environment with a Senior DBA, then that person should be responsible for mentoring a good portion of your career. You may choose to look at other mentors as well.  

Tip #7: Participate in local user groups – Many cities across the nation have local user groups which meet periodically to talk about database-related topics. Join one of these local user groups if possible. This gives you a great way to interact and network with others in your field.

by Grow That DBA Career