Things that happen in support messages that I don’t like… Part II

++ 07.03.2011 ++ UPDATE ++ UPDATE ++ UPDATE ++

The mentioned error message had been improved with Oracle 10.2.0.5 (“Things change for the better” or “How I enhanced the Oracle DBMS software”). Thus, the content of this blog post is only valid for older versions of the Oracle database software.

++ UPDATE ++ UPDATE ++ UPDATE ++

There are dozens of support messages on the BC-DB-ORA component each year that have been opened because of only one error message:
ORA-01113: file string needs media recovery
Usually customers see this error when trying to startup a db instance that was shut down “the hard way” (that is shutdown abort, a power outtage or something similar).

The next thing that is usually done is typing in
RECOVER DATABASE...

That could be the worst thing to do! Why?

Let’s have a look how Oracle describes this error:

Cause: An attempt was made to online or open a database with a file that is in need of media recovery.
Action: First apply media recovery to the file.

Huh? What is wrong here? Haven’t I just written that a recovery is not the right thing to do?
Yes, I did.

The problem with this error message is: it tells us only the half of the truth.
It’s true – if there is a file that needs recovery then this error will appear.
But in most of the cases when this error occurs there is no need to recover anything.

All that has to be done is to update the fileheader.
And this is done by
ALTER DATABASE DATAFILE '' END BACKUP;

Unfortunately a datafile in ONLINE BACKUP mode looks very much the same to the Oracle startup procedure as a restored datafile does.
Both have lower system change numbers (SCN) in the datafile header than the current SCN.
To catch up redo-log information is necessary, but only for the restored file.

The file in ONLINE BACKUP mode just needs to get the datafile header ‘unfreezed’ so that it will be updated again. That’s an action of some minutes if not only seconds.
But due to the misleading message text, people try the recovery over and over again. If something goes wrong with it, restore is tried and so times flies…

In fact I once (and only once!) fell for this misleading error and supported a customer in trying the recovery.
The backup/recovery setup at the customer side was not well tested… in the end the customer had to work the whole weekend to get his database online again.
(To my excuse I was quite new in database support back then!)

All that just because the Oracle developers decided to write the error-message as they did.
Why couldn’t they change it to something like this:
ORA-01113: file string needs media recovery or is in online backup mode
?
They would save so much time of customers with that…

KR Lars
p.s.
Of course SAP has a note for this error:
Go ahead and check this one: #4162 – Missing “end backup”

Things that happen in support messages that I don’t like…

After some years working in support solving messages everybody will have a list of things that do occur over and over again and that never seem to get better.
Today I will start with the most anoying one (at least to me):

No remote connection possible!

Imagine you experience a heart attack. What you usually need is somebody trained to help you in that situation and do at least the primary steps to save your life.
At best this person is a paramedic so you can be rather sure that you’re in good hands.
Now imagine you have this person at your front door, when the heart attack occurs but when he wants to enter the room where you’re lying at the floor you just tell him:
“You cannot enter! Our companys security policy denies access by people not working for us… Please tell me how I can save myself!”

Althought this situation might be overbooked it is basically what I (and many of my collegues) face every day.
Customers facing a complete system down situtation are not able to provide TELNET access to their database servers. They are just not prepared to do so.
Of course TELNET is not state of the art concerning security on connections over the internet, no question about that.
But it’s possible to make up something like logging on to a remote connection server via Windows Terminal Services (for example) and from there via SSL to the target machine. So there are options to make this access secure.

And even if customers make the (correct!) decision that for this special situation the potential risk of beeing hacked while the TELNET connection is open is – by far – less costly than having a whole shift of workers leaving early due to the system down situation, even than it’s often just not possible to use the connection.
The companys firewalls block the TELNET port, the SAP service connection is not maintained or (this is the real classic): people ignore the description how to set up such a service connection and forget to allow port 23 (TELNET) on their SAPROUTER. So the infamous “route permission denied” error is just right on its way.

My recommendation: make plans how support should access your systems when they are down!
Set the connections up.
Have them tested – just open a support message and ask for somebody to logon!
This may save you precious HOURS the next time the database won’t come up.

As there are plenty of SAP notes describing the various remote connections I won’t fail to list the most important ones:

#35010 – Service connections: Composite note (overview)

Specific connection types:
Telnet Connection: Note 37001
R/3 Support: Note 812732
SAP-DB Connection: Note 202344
WTS Connection: Note 605795

Official statement concerning SSH-connections:
#789026 – Remote Connection: SSH
For semi-automatic line-opening see:
#797124 – LOP – Line Opener Program

By the way: the connection types listed in the overview (35010) note are exclusive. Whatever remote access tool or technology you are using usually in your company – when it’s not on the list, support cannot use it. So better don’t waste time and ask for such things to be tried.

Best regards
Lars

We all hate the BR*TOOLS, don’t we?

We all hate the  BR*TOOLS, don’t we?

You won’t believe how often we in support get asked about if SAPDBA is available for use with Oracle 10g. Even more customers tell us, how they dislike the BRTOOLS and want the SAPDBA back.

The plain answer they all get is: SAPDBA is out of maintenance now for ages – use the current BRTOOLS.

But what is behind all this? Is SAP really ignorant against customer wishes? What are the main reasons for customers to reject the BRTOOLS.

I believe the biggest issue for customers is the changed user-interface. While SAPBA used a rather classic-approach with a central starting menu from which the DBA could jump between functions as he liked the BRTOOLS are designed by a task-oriented way.

What does that mean? The idea is, that the DBA does not use the BRTOOLS for “just looking” or “trying out things”. When the DBA starts the BRTOOLS he already knows exactly, what he wants to do, which task he wants to accomplish. To support this approach, most menus in the BRTOOLS do appear in a wizard-like-sequence, usually starting with rather general settings ending up with the options for choosing details on the desired function.

Let’s check this on an example: Let’s create a UNDO-tablespace.

   > brtools
   BR0651I BRTOOLS 7.00 (26)
    
   BR0280I BRTOOLS time stamp: 2007-06-18 12.05.08
   BR0656I Choice menu 1 - please make a selection
   ---------------------------------------------------------------
   BR*Tools main menu
    
    1 = Instance management
    2 - Space management
    3 - Segment management
    4 - Backup and database copy
    5 - Restore and recovery
    6 - Check and verification
    7 - Database statistics
    8 - Additional functions
    9 - Exit program
    
   Standard keys: c - cont, b - back, s - stop, r - refr, h - help
   ---------------------------------------------------------------
   BR0662I Enter your choice:
   2

As you may have noticed I can either select a function from the menu or choose ‘c’ for continue. Whenever there is a ‘=’ between the menu number and the description that’s the default choice of the current menu and you can just use ‘c’ to go ahead.

In this case the ‘Instance Management’ is the default choice – but I like to add a UNDO tablespace so I go for ‘2 – Space Management’.

   BR0280I BRTOOLS time stamp: 2007-06-18 12.05.59
   BR0663I Your choice: '2'
    
   BR0280I BRTOOLS time stamp: 2007-06-18 12.05.59
   BR0657I Input menu 82 - please check/enter input values
   -----------------------------------------------------------------------------
   BRSPACE options for create tablespace
    
    1 - BRSPACE profile (profile) ...... [initTDB.sap]
    2 - Database user/password (user) .. [/]
    3 ~ Tablespace name (tablespace) ... []
    4 - Confirmation mode (confirm) .... [yes]
    5 - Scrolling line count (scroll) .. [20]
    6 - Message language (language) .... [E]
    7 - BRSPACE command line (command) . [-p initTDB.sap -s 20 -l E -f tscreate]
    
   Standard keys: c - cont, b - back, s - stop, r - refr, h - help
   -----------------------------------------------------------------------------
   BR0662I Enter your choice:

In that menu I can enter additional information like the name of the tablespace I’m about to create, but I don’t have to. That’s visible by the ~ (tilde) sign at the menu point.

Due to the modular concept of the BRTOOLS the tool BRSPACE will now be called. You can see the command line parameters used for this at menu entry 7. If you would start BRSPACE with these options, it will work the exact same way.

Anyhow, I continue with ‘c’.

   #####################################################################
    
   BR1001I BRSPACE 7.00 (26)
   BR1002I Start of BRSPACE processing: sdvmycvk.tsc 2007-06-18 12.47.48
   BR0484I BRSPACE log file: C:ORACLETDBsapreorgsdvmycvk.tsc
    
   BR0280I BRSPACE time stamp: 2007-06-18 12.47.52
   BR1009I Name of database instance: TDB
   BR1010I BRSPACE action ID: sdvmycvk
   BR1011I BRSPACE function ID: tsc
   BR1012I BRSPACE function: tscreate
    
   BR0280I BRSPACE time stamp: 2007-06-18 12.47.53
   BR0656I Choice menu 304 - please make a selection
   ---------------------------------------------------------------------
   Tablespace creation mainmenu
    
    1 = Create tablespace
    2 - Show tablespaces
    3 - Show data files
    4 - Show disk volumes
    5 * Exit program
    6 - Reset program status
    
   Standard keys: c - cont, b - back, s - stop, r - refr, h - help
   ---------------------------------------------------------------------
   BR0662I Enter your choice:

We see a line of hashes (‘#######…’) indicating hat an external tool – BRSPACE in this case – has been called and that we now see the output of this tool. Additionally we see the BRSPACE header, so we can be pretty sure, that we’re now using BRSPACE.

The default choice is now automatically set to ‘create tablespace’, so if we already know what we want, we can just move on with ‘c’. Otherwise we can check what tablespace are already there, how much space on the physical disks are left and so on with the other menu entries.

Let’s move on with ‘c’.

   BR0280I BRSPACE time stamp: 2007-06-18 12.51.05
   BR0657I Input menu 305 - please check/enter input values
   ---------------------------------------------------------------
   Main options for creation of tablespace in database TDB
    
    1 ? Tablespace name (tablespace) ......... []
    2 - Tablespace contents (contents) ....... [data]
    3 - Segment space management (space) ..... [auto]
    4 # Database owner of tablespace (owner) . []
    5 ~ Table data class / tabart (class) .... []
    6 - Data type in tablespace (data) ....... [both]
    7 # Joined index/table tablespace (join) . []
    8 ~ Uniform size in MB (uniform) ......... []
    
   Standard keys: c - cont, b - back, s - stop, r - refr, h - help
   ---------------------------------------------------------------
   BR0662I Enter your choice:

Now I’ve to enter the name for the new tablespace – the ‘c’ won’t work until all menu entries marked with ‘?’ have been filled. For the contents and segment space management part there are default choices that should fit the majority of all use cases. Anyhow for my test, I’ll change the contents-type to UNDO and go ahead with ‘c’

 

   BR0280I BRSPACE time stamp: 2007-06-18 12.53.19
   BR0657I Input menu 306 - please check/enter input values
   -------------------------------------------------------------------------------
   Space options for creation of tablespace PSAPUNDOX (1. file)
    
    1 - Tablespace file name (file) .......... [C:undox.data1]
    2 # Raw disk / link target (rawlink) ..... []
    3 - File size in MB (size) ............... [10]
    4 # File autoextend mode (autoextend) .... [no]
    5 # Maximum file size in MB (maxsize) .... []
    6 # File increment size in MB (incrsize) . []
    7 - SQL command (command) ................ [create undo tablespace PSAPUNDOX datafile 'C:undox.data1' size 10M autoextend off]
    
   Standard keys: c - cont, b - back, s - stop, r - refr, h - help
   -------------------------------------------------------------------------------
   BR0662I Enter your choice:

The last screen before the actual command is send to the database give the option to setup the sizes and locations of data files for the tablespace. Again default values are given (especially the filename) and can be used by just entering ‘c’.

One nice feature is that the SQL command is visible AND editable at menu entry 7. So if you want to edit it manually, you can do this easily (ever tried that with SAPDBA?).

The next menu is a hurdle for many BRTOOLS users:

   BR0280I BRSPACE time stamp: 2007-06-18 12.56.35
   BR1091I Next data file can be specified now
   BR0675I Do you want to perform this action?
   BR0676I Enter 'y[es]' to perform the action, 'n[o]/c[ont]' to skip it, 's[top]' to abort:

The question is: do you want another data file right now or not? If so, enter ‘y’es. If not, you can simply use ‘c’ again to go on. Sometimes this is over read and DBAs end up with far more data files than they wanted – because they always entered ‘y’, ‘y’, ‘y’… So better stick to ‘c’!

 

   BR0280I BRSPACE time stamp: 2007-06-18 12.58.39
   BR0370I Directory C:\ORACLE\TDB\sap\reorgsdvmycvk created
    
   BR0280I BRSPACE time stamp: 2007-06-18 12.58.41
   BR0319I Control file copy created: C:ORACLETDBsapreorgsdvmycvkCNTRLTDB.old 22036480
   BR0252W Function CreateFile() failed for 'C:\hiberfil.sys' at location BrFileStatGet-9
   BR0253W errno 2: No such file or directory
   BR0252W Function CreateFile() failed for 'C:\pagefile.sys' at location BrFileStatGet-9
   BR0253W errno 2: No such file or directory
    
   BR0280I BRSPACE time stamp: 2007-06-18 12.58.41
   BR1089I Creating tablespace PSAPUNDOX...
    
   BR0280I BRSPACE time stamp: 2007-06-18 12.58.43
   BR1016I SQL statement 'create undo tablespace PSAPUNDOX datafile 'C:\undox.data1' size 10M autoextend off' executed successfully
   BR1060I Tablespace PSAPUNDOX created successfully with file: C:\undox.data1 10M
    
   BR0280I BRSPACE time stamp: 2007-06-18 12.58.44
   BR0340I Switching to next online redo log file for database instance TDB ...
   BR0321I Switch to next online redo log file for database instance TDB successful
    
   BR0280I BRSPACE time stamp: 2007-06-18 12.58.47
   BR0319I Control file copy created: C:\ORACLE\TDB\sap\reorgsdvmycvk\CNTRLTDB.new 22036480
    
   BR0280I BRSPACE time stamp: 2007-06-18 12.58.47
   BR0256I Enter 'c[ont]' to continue, 's[top]' to cancel BRSPACE:

 

Ok, we see some warnings here – they are the result of the non-standard, non-sensible and non-recommended location of my data file. I just put it into c: – so at least I get a warning when I do such nonsense.

To finish the action, you guess it, press ‘c’.

   BR0280I BRSPACE time stamp: 2007-06-18 13.00.41
   BR0656I Choice menu 304 - please make a selection
   ---------------------------------------------------------------
   Tablespace creation main menu
    
    1 + Create tablespace
    2 - Show tablespaces
    3 - Show data files
    4 - Show disk volumes
    5 = Exit program
    6 - Reset program status
    
   Standard keys: c - cont, b - back, s - stop, r - refr, h - help
   ---------------------------------------------------------------
   BR0662I Enter your choice:

We’re back at BRSPACE menu and since we’re rather finished with our action, the exit is already the default choice, so ‘c’ is again the correct key.

   BR0292I Execution of BRSPACE finished with return code 1
    
   BR0668IWarnings or errors occurred - you can continue to ignore them or go back to repeat the last action
   BR0280I BRTOOLS time stamp: 2007-06-18 13.01.45
   BR0670I Enter 'c[ont]' to continue, 'b[ack]' to go back, 's[top]' to abort:

Now, what’s that? Errors? We just have successfully created a new UNDO tablespace and now there should have been errors? But wait a minute! It’s just to inform you that there have been WARNINGS or ERRORS. If you’re already aware of them – ignore them by using ‘c’. If you don’t know what errors could have been there: check the scroll back buffer of your terminal or – better – the log file of your current action. With BRGUI (the recommended way to use the BRTOOLS) it’s easy to display the log file – just click on log file and you even get a coloured output so finding warning and errors is really easy.

With the command line usage you’ve to check the log file yourself – just scroll up to where BRTOOLS has called BRSPACE. In that very step you’ll find the line

   BR0484I BRSPACE log file: C:\ORACLE\TDB\sap\reorgsdvmycvk.tsc

That’s the file you should have a look into.

But let us go ahead for now.

   ###############################################################################
    
   BR0292I Execution of BRSPACE finished with return code 1
    
   BR0668I Warnings or errors occurred - you can continue to ignore them or go back to repeat the last action
   BR0280I BRTOOLS time stamp: 2007-06-18 13.01.45
   BR0670I Enter 'c[ont]' to continue, 'b[ack]' to go back, 's[top]' to abort:
   c
   BR0280I BRTOOLS time stamp: 2007-06-18 13.09.34
   BR0257I Your reply: 'c'
   BR0259I Program execution will be continued...
    
   BR0280I BRTOOLS time stamp: 2007-06-18 13.09.34
   BR0656I Choice menu 5 - please make a selection
   -------------------------------------------------------------------------------
   Database space management
    
    1 = Extend tablespace
    2 + Create tablespace
    3 - Drop tablespace
    4 - Alter tablespace
    5 - Alter data file
    6 - Move data file
    7 - Additional space functions
    8 - Reset program status
    
   Standard keys: c - cont, b - back, s - stop, r - refr, h - help
   -------------------------------------------------------------------------------
   BR0662I Enter your choice:

Ok, again we see the ‘#’-line to indicate that the current program has changed. We also see again that there has been a minor problem or warning within the BRSPACE function we used.

Anyhow we’re now back in the BRTOOLS menu and can either continue with other functions or just leave the program. For that – the first time in the whole procedure – we don’t have to use ‘c’ but ‘b’ instead. Remember we’re still in the ‘space management’ menu so we have to go back to the main menu.

 

    
   BR0280I BRTOOLS time stamp: 2007-06-18 13.25.10
   BR0663I Your choice: 'b'
   BR0673I Going back to the previous menu...
    
   BR0280I BRTOOLS time stamp: 2007-06-18 13.25.10
   BR0656I Choice menu 1 - please make a selection
   ----------------------------------------------------------------
   BR*Tools main menu
    
    1 = Instance management
    2 + Space management
    3 - Segment management
    4 - Backup and database copy
    5 - Restore and recovery
    6 - Check and verification
    7 - Database statistics
    8 - Additional functions
    9 - Exit program
    
   Standard keys: c - cont, b - back, s - stop, r - refr, h - help
   ----------------------------------------------------------------
   BR0662I Enter your choice:
   9
   BR0280I BRTOOLS time stamp: 2007-06-18 13.25.23
   BR0663I Your choice: '9'
   BR0280I BRTOOLS time stamp: 2007-06-18 13.25.23
   BR0680I Do you really want to exit BRTOOLS? Enter y[es]/n[o]:
   y
   BR0280I BRTOOLS time stamp: 2007-06-18 13.25.25
   BR0257I Your reply: 'y'
    
   BR0280I BRTOOLS time stamp: 2007-06-18 13.25.25
   BR0653I BRTOOLS completed successfully with warnings

Obviously, ‘exit’ is not the default choice in the main menu – so choosing it with ‘9’ lets us out. Again we see the information concerning the warnings that appeared so that we would have to be deaf andblind to miss them.

What have we learned here? Opposed to SAPDBA the BRTOOLS are far more modular. For nearly every step we take a log is written to make it possible to analyse errors. There are far more security questions. An unwanted ‘drop tablespace’ should not happen that way. Most of the action paths you usually have to take as a DBA a predefined and linked via the ‘c’-continue access.

So, yes the BRTOOLS do work different than the SAPDBA.

But they are more secure and it’s obvious to work even with new features of the database since the modular concept allows a quick integration into the tools – it just follows the same pattern of menus.

From the trainings I’ve given my advice concerning the BRTOOLS is: try them. Use a test system and try your most used actions. Get used to them – these are tools that will be there for the next years.

And of course even the next generation is already in pole position…(see BRTOOLS STUDIO in SDN SAP on Oracle ).

KR Lars

Related SAP notes:

#647697 – BRSPACE – New tool for Oracle database administration

#012741 – Current versions of SAPDBA and BR*Tools