Alejandro Vargas' Blog

On a previous post "Production Upgrade from 8.1.7.4 to 9.2.0.8"  I  did describe the preparation steps for a critical production database upgrade, I was responsible for the rdbms upgrade, a little part of the whole project that included several OS and Application layers.

In few words, the bottom idea on this post is to summarize some lessons learned during the upgrade. They are:

When executing a critical process, that needs to be finished within a restricted time window:

1. Build a step by step detailed check list for every action related to the process.
   
2. Prepare ready made scripts for each step that can be executed through scripts.
3. Rehearse and improve the check lists on test until they run smoothly.
4. Make at least 2 general rehearsals on a full set environment.
5. On Production, follow your check lists carefully.
6. If something arise that can wait until after finishing the procedure, put it on the waiting list.
7. If something unexpected arise, before taking any action:
   
   
   a) Gather the relevant people and review the facts,
   b) Review the options,
   c) Only then decide on a course of action.
   
   
8. Once the process finish, gather the relevant people and perform an after action review to summarize the learned lessons.
   

This is the anecdotal part of the post:

I did start the Database upgrade at15:30 and finished at 17:00, less that 50% of the time we had on the general rehearsal; we were running on a much powerful machine, compile was done using parallel 35 and finished really fast.  Being 3 hours before the planned time was nice.

Then the problems started.

A colleague was upgrading statspack, and got some errors, so he decided to remove Perfstat tables. One of them, was a dictionary managed table that had several hundred of thousands of extents.

We were ready to turn the database back into archive log mode to proceed with application checks but the drop was still running. We went to have dinner while the drop was still running.

Archive log mode was a precondition to move to the next stage and we needed to shutdown the database to move it to archive log mode. But the drop was running...

My colleague killed his session expecting that this will give us the possibility to proceed with the other steps, but this was not the case.

Once a session that was running more than an hour dropping a dictionary managed object with more than 200.000 extents is killed, smon take over the task of moving used extents to free extents, the only correct action in this case is to wait, and let smon to finish its work.

Shutdown immediate will not work because oracle waits for smon to finish. The original drop  statement converted the segment to a temporary segment; when the drop is interrupted, the temporary segment will be cleaned up by smon.  Shutdown abort will work and the database will come up, but you will not be able to set archive log mode until smon finish its cleaning up actions.

Shutdown abort in this case was a bad idea, on startup it forced smon to scan all extents already set, a slow and painful process... trace of smon showed that it scanned uet$ row by row reviewing all the job that was already completed:



There were several other highlights during this 36 hours upgrade, that finished with great success and on time, that is other rule: always add at least 40% spare time for each task :-)

When there is no more than the actual backup, and the database serves a critical online production facility, and the facility virtually stops because of the problem, then the last thing you want to meet is  a corrupted archived log!

It started early in the morning, a very top industry had its ERP database down, a colleague was all the night working on the case, but the database was still down...

Facts were:

• Database was performing a hot backup,
• While being backed up crashed
• Startup normal does not work
• Recovery fails with oracle pointing to a corrupted archived log
  

Fortunately the problem was not that complicated:

• The crash happened while the database was in backup mode.
• Select status from v$backup revealed that several datafiles were still on backup mode.
• To perform recovery you must finish backup.
• Recover again failed with the corruption being reported on the same archived log as before, but we noticed that the sequence of this archived log was on the range of the online logs.
• We did recovery again and applied the matching online log instead of the corrupted archived log, and the following sequences until the current, recovery finished successfully.
• Before opening the database with resetlogs option we did backup the online logs, to replace the corrupted one.
• The database opened with resetlogs successfully.

Note:

To end backup mode in 10g you can use:

    alter database end backup.

In this case we were working with a 9i database so we used :

    alter tablespace <tbsname> end backup;

A script like this can be used to match datafiles to its tablespaces:

set echo off  head off
SELECT     'alter tablespace '||name||' end backup;'
from     V$TABLESPACE WHERE TS# IN (
    SELECT TS# FROM V$DATAFILE WHERE FILE# IN (
        SELECT FILE# FROM V$BACKUP WHERE STATUS='ACTIVE'))
/

alter tablespace USERS end backup;
alter tablespace PERFSTAT end backup;

One of the RAC environments I'm working with is a very critical production database, there are several development, test and training clones of it, and from time to time a new clone needs to be created.

The main backup strategy we are using is based on EMC snapshots, so we can easily create clones from snapshots.

Still sometimes you get not standard requirements, or find circumstances that require creative solutions.

The special request was:

• Replicate the 64 bit prod database to a 32 bit training environment
  
• Production is ASM based, training will be File System based
• There is no, and cannot be communication between Prod and Train

The workaround I did use has several steps but worked fine:

1. Open read-only a prod snapshot on a recovery server. In my case I did use a server where we automatically open read only the prod snapshots for backup verification.
   
2. Mount NFS the data File System of the training server on the recovery server
3. Generate a create controlfile script using :
   
            alter database backup controlfile to trace;
   
   
4. Find the trace on user_dump_destination, rename it to CRCTL.sql and move it to the NFS mounted File System
5. Backup the database using rman backup as copy. You will get files named like:
   
   
   data_D-PRODDB_I-2123417810_TS-UNDOTBS2_FNO-21_g5il8cge
   
   
6. Once the backup finish make a list of the file names order according the FNO-# ; from FNO-1 until the last file
7. Optionally you may rename the files to a more simple name, like :
   
   
   mv data_D-PRODDB_I-2123417810_TS-UNDOTBS2_FNO-21_g5il8cge UNDOTBS2_01.dbf
   
   
   
8. Edit the create controlfile script, change the database name using the 'set database ' clause. Replace the datafile names on the CRCTL.sql script you generated on step 3 with the names of the datafiles you got from Rman (or you renamed on step 7) take care to order them according their File Number: FNO-1, FNO-2 etc.
9. If this is the first time you clone a database in this server you will need to create the init.ora, and set the bdump, udump, cdump and archived log destinations
10. Recreate the controlfile using the CRCTL.sql and open the database with resetlogs.
11. Shutdown the database and open it in migrate mode:
    
              startup migrate;
    
    
12. Execute utlirp.sql to change the word size from 64 to 32 bits
    
              @?/rdbms/admin/utlirp.sql
    
    
13. Shutdown immediate
14. Startup normal
    

The database I worked with was a 500GB database, I did use a 2 CPU Linux box to open it read only and backed it up using 8 channels. The throughput was of 1GB per minute.

This is the rman script:

#!/bin/ksh
echo `date` >start.backup

# Environment Variables
# ---------------------
export ORACLE_SID=proddb
export ORACLE_BASE=/u01/app01/oracle
export ORACLE_HOME=/u01/app01/oracle/product/10.2.0.3

# Path Variables
# --------------
export BASE_PATH=/u01/app01/oracle/scripts/general:/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/usr/X11R6/bin:/root/bin:/u01/app01/oracle/scripts

v_bdest=/NFSmnt/traindb

export PATH=${ORACLE_HOME}/bin:${BASE_PATH}

# Backup database
# ---------------
rman target / nocatalog <<EOF
run {
allocate channel backup_disk1 type disk format '$v_bdest/%U';
allocate channel backup_disk2 type disk format '$v_bdest/%U';
allocate channel backup_disk3 type disk format '$v_bdest/%U';
allocate channel backup_disk4 type disk format '$v_bdest/%U';
allocate channel backup_disk5 type disk format '$v_bdest/%U';
allocate channel backup_disk6 type disk format '$v_bdest/%U';
allocate channel backup_disk7 type disk format '$v_bdest/%U';
allocate channel backup_disk8 type disk format '$v_bdest/%U';
backup as COPY database include current controlfile;
release channel backup_disk1;
release channel backup_disk2;
release channel backup_disk3;
release channel backup_disk4;
release channel backup_disk5;
release channel backup_disk6;
release channel backup_disk7;
release channel backup_disk8;
}
exit
EOF

echo `date` >end.backup
# EOF backup_to_fs

Sometimes you need to configure raw devices for OCR, Votingdisk and ASM Spfile, but the directory /dev/raw does not exist.

As root create the /dev/raw directory:

    mkdir /dev/raw

Then create the required raw devices using the following sintax:

i.e.:

For setting up 12 raw devices use a loop:

Once configured create or update the script /etc/udev/scripts/dev-raw.sh to automatize the configuration of raw devices for RAC on each startup

# raw-dev.sh
MAKEDEV raw
mv /dev/raw/raw1 /dev/raw/votingdisk
mv /dev/raw/raw2 /dev/raw/ocr.dbf
mv /dev/raw/raw3 /dev/raw/spfile+ASM.ora

chmod 660 /dev/raw/votingdisk
chmod 660 /dev/raw/ocr.dbf
chmod 660 /dev/raw/spfile+ASM.ora

chown oracle:dba /dev/raw/votingdisk
chown oracle:dba /dev/raw/ocr.dbf
chown oracle:dba /dev/raw/spfile+ASM.ora

Is the location of database files irrelevant these days when the capabilities of high end storage seems to address all possible issues?

The answer is still yes according to a good friend I was working with the last two weeks. It is extremely relevant and the performance gaining of  following this rule is high, as high is the penalty for not following it.

Every production environment needs to be built according to this standard:

      The formula for database location on the frame is 20%-40%-20%-20%:

• 20% of the disk located on the external cylinder is the best performing area of the frame, to be used by online redo logs, undo segments, temp segments and most accessed database files
• 40% near the external cylinder is a good performing area, to be used by all other database files
• Next 20% is a slow performing area and should not be used for database files, except parameter files, archived logs, software install
• Last 20% correspond to the inner cylinder, is the slowest performing area and should not be used for database files at all.

Lun's to be used by the database needs to be mapped to assure that the database is located in the most performing areas of your frame.

This is a task that must be accomplished working together with Storage Vendors and Storage Administrators.

If this task was not done, a mapping of the actual lun's can be requested from the vendor or storage administrator.
 

Problem solving strategies sometimes find clear causes and answers and sometimes not, in this case analyzing the facts did show the way to find a workaround.

It happened on a multi TB RAC Database.

Symptoms observed were that queries on a partitioned table started to fail with ORA-1410 on some partitions.

The initial diagnostic was of a logical corruption affecting one specific datafile, in this case a shared raw device. Some of these partitions were dropped and recreated using create as select from a backup.

Once the create as select finished, checkup on the new partitions again returned ORA-1410, all traces pointing to the same raw device.

The common factor was always the same device. Building a new raw device and moving all partitions siting on the affected one solved the problem.

This is a quick example of Log Miner implementation.

Log Miner is a powerful tool and all DBA's should know and master it.

Attached a summary of the first steps for mining archived logs or online redo logs

This is the link to the complete reference on the Utilities Guide:

And this is an excellent article by Arup Nanda on OTN: