Marko Sutic's Oracle Blog

Troubleshooting memory usage

2012-01-10T15:48:00.004+01:00

Last few days we had significant load problems with one of our database servers. Server was choking under minor database load which caused applications to be non-responsive.

This is 8-core 32-bit OEL5 (2.6.18-92.el5PAE) server with 4GB RAM running two Oracle 10g (10.2.0.4) SE databases. SGA's of those databases where 1,2 GB and 700MB with pga_aggregate_target 64M and 128M.

As databases were small (under 50G) and not very active I didn't expected any performance problems.

Using vmstat, mpstat, top and sar tools I've noticed that we were having slight issues with swapping, despite that there was enough physical memory.

So I've started troubleshooting...

First I've found that one of top running processes was "kswapd0" process.
This is Kernel Swap Deamon responsible for scanning memory to locate free pages, and scheduling dirty pages to be written to disk. It is OK to occasionally notice kswapd running, but it shouldn't continuously appear in top processes. In some situations you can resolve kswapd issues using huge pages but this wasn't the case here.

I've decided to check several OS parameters related to swapping to find possible cause of our issues.

Parameters below were specified in "/etc/sysctl.conf":
...
vm.overcommit_memory=1
vm.swappiness=0
vm.min_free_kbytes=262144
vm.page-cluster=5
...

Considering that I didn't installed this server, this parameters were little odd to me. All of them were custom parameters not required from install guide.

The swappiness variable bothered me the most. It tells Linux kernel how fast it can move inactive pages to swap area. Default value is 60, but in this case value was changed to 0 which tells the Linux kernel to avoid swapping as much as possible.

As this was production server which was still choking we had to react quickly without prior testing. I haven't seen the purpose of vm custom parameters on this server so I've decided to comment them and apply only default parameters required from install guide. It's easy to add custom parameters later if there is a need.

After I applied parameters running "sysctl -p" load average suddenly started to fall. Several minutes later it fell from 13 to 1. From vmstat I've noticed significant drop in swapping and kswapd0 wasn't in top processes anymore.

With altering swappiness to 60 (default) I've told kernel to find inactive pages more aggressively and swap them out to disk.
Probably that helped OS to use memory more effectively.

I will closely monitor performance of this server in next few days but so far it is working very good.

Considerations for future:
- enable Huge Pages
- migrate os/db to 64bit

When I'm installing database servers I tend to use parameters specified in install guide or Oracle support site. Eventually I will add some custom parameters but only if I have proper reason for that. Even then it is wise to test changes before applying them in production.

So my conclusion for this case would be - do not add custom parameters without proper reason.
Google - "Compulsive Tuning Disorder" :)

IMPDP creates indexes with parallel degree 1 during import

2011-08-31T13:36:00.002+02:00

Last few days I’ve been playing with export and import of 750 GB database. During this whole trial and error process I’ve learned several things that were unfamiliar to me. In this blog post I want to share some of the stuff that I learnt.

I have Oracle 10.2.0.4 database on Solaris 64bit which I want to migrate to Oracle 11.1.0.7 on Linux 64bit. It is 750GB large database with mostly partitioned tables (compressed partitions).

Let's get to the subject of the post.

I won't talk much about export operation - just to mention that it is important to specify this bolded line in parameter file:
dumpfile=exportdb_%U.dmp
filesize=30G

This means that multiple 30GB sized dump files will be generated during export. It is important to specify %U expression or to manually define multiple dump files if you want to get the most from parallel processing during export and import.

In my case - after performing full export of the database I've created 20 dump files which is about 600GB of export data.

Now comes the tricky part - importing database to the new environment.

Data import was very fast as it finished in below 2,5 hours. During import server was fully utilized using all available resources in parallel processing. When index creation started utilization of the server dropped to about 10%.

Only one worker performed the entire work, while others were waiting - even with parallel option specified.

Then I found that this was the result of the very nasty bug:
“Bug 8604502 - IMPDP creates indexes with parallel degree 1 during import”

This issue is fixed in:

12.1 (Future Release)
11.2.0.2 (Server Patch Set)
11.2.0.1 Bundle Patch 7 for Exadata Database
11.1.0.7 Patch 24 on Windows Platforms

Great, I don’t have fix for this bug.
With only one worker creating indexes this import could last for days.

For a quick solution I’ve decided to perform import in two steps. Firstly I will import everything but indexes and in next step I will import just indexes.

Data import again lasted below 2,5 hours and it finished without any problems fully utilizing the server.

For index creation I’ve generated CREATE INDEX script using SQLFILE option in IMPDP.

*parameter file*
userid=xxx/yyy
sqlfile=createidx.sql
dumpfile=exportdb_%U.dmp
include=INDEX
directory=export_dir
schemas=(...)

As SQLFILE generates CREATE INDEX scripts with parallel degree set to 1 I’ve decided to manually edit script and replace parallel 1 to parallel 16.

Besides parallel 16 option, I’ve specified NOLOGGING option to restrict generation of large redo log. As I expected creation of large indexes I’ve allocated large enough temporary tablespace and specified higher pga_aggregate_target parameter.

Then executed script from sqlplus and monitored server utilization and waits.

Script finished in about 3 hours which was nice improvement.
Server was fully utilized - mostly using 16 processes for index creation.

Main waits were:
- WAITING direct path read
- WAITING direct path read temp
- WAITING direct path write temp

This waits were expected as temporary space was extensively used.

Later I’ve found nice article from great Oracle expert Randolf Geist - Concurrent Index Creation. As a solution for the same problem he wrote tool for concurrent index creation.
I plan to test this approach in my environment.

Wrong results with 'hash group by' aggregation on 10.2.0.2

2011-07-07T13:54:00.004+02:00

If you are running 10.2.0.2 in your production environment check document [ID 4604970.8] on Oracle support site.

This week colleague noticed that query, he wanted to use for some reports, returns different results than he expected. He made some additional checks using Microsof Excel and confirmed that something is wrong. He was getting incorrect results.

My first suspicion was that probably something is wrong with query or maybe logical corruption happened.

Example of the queries (I’ve changed names of the table/columns):

-- partitioned_table (date range partitioned table)

create table temp_tab_2011
as
select col, date_to,  sum (col1) col1, 
       sum (col2) f_col1, sum (col3) col3, 
    sum (col4) col4, 
    sum (col5) col5, count(*) cnt
from partitioned_table t
where t.date_to >= to_date ('01.02.2011 00:00:00', 'dd.mm.yyyy HH24:MI:SS')
    and t.date_to < to_date ('01.07.2011 00:00:00', 'dd.mm.yyyy HH24:MI:SS')
group by col, date_to
/

create table temp_tab_2010_2011
as
select col, date_to, sum (col1) col1, 
       sum (f_col1) f_col1, sum (col3) col3, 
    sum (col4) col4, 
    sum (col5) col5, count(*) cnt
from partitioned_table t
where t.date_to >= to_date ('01.02.2010 00:00:00', 'dd.mm.yyyy HH24:MI:SS')
    and t.date_to < to_date ('01.07.2011 00:00:00', 'dd.mm.yyyy HH24:MI:SS')
group by col, date_to
/

-- COMPARASION QUERY
select j.col, j.col1, j.col2, j.col3, j.cnt, d.col, d.col1, d.col2, d.col3, d.cnt, j.col1 - d.col1
from
temp_tab_2011 j, temp_tab_2010_2011 d
where 1=1
and j.col = d.col
and j.date_to = d.date_to
and j.date_to = to_date ('01.06.2011 00:00:00', 'dd.mm.yyyy HH24:MI:SS')
and d.date_to = to_date ('01.06.2011 00:00:00', 'dd.mm.yyyy HH24:MI:SS')
and (j.col1 - d.col1) <> 0
/

We expected 0 rows returned for comparison query but we received some rows - which was serious issue.

I’ve restored that database on another machine. Checked files for logical corruption, rebuilded indexes into another tablespace, rebuilded table into another tablespace and re-created temporary tablespace - but still we were receiving wrong results.

Luckily for us I’ve noticed this blog post during my searches for answer on Google:
http://www.oracloid.com/2006/05/hash-group-by-can-give-wrong-result-in-oracle-102/

Queries were using this plan:
(click "view source" to get better output)

----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT               |                               |       |       |       | 43069 (100)|          |       |       |
|   1 |  LOAD AS SELECT                      |                               |       |       |       |            |          |       |       |
|   2 |   PARTITION RANGE ITERATOR           |                               |   631K|    50M|       | 39729   (1)| 00:13:08 |    57 |    74 |
|   3 |    HASH GROUP BY                     |                               |   631K|    50M|   219M| 39729   (1)| 00:13:08 |       |       |
|   4 |     TABLE ACCESS BY LOCAL INDEX ROWID| PARTITIONED_TABLE             |  1557K|   124M|       | 20999   (1)| 00:06:56 |    57 |    74 |
|*  5 |      INDEX RANGE SCAN                | IX_DATE                       |  1557K|       |       |  3874   (1)| 00:01:17 |    57 |    74 |
----------------------------------------------------------------------------------------------------------------------------------------------

In our version 10.2.0.2 wrong results are possible from the result of a non-distinct aggregation with a group by when HASH GROUB BY is used. We were hitting that bug.

In session level we changed undocumented parameter "_gby_hash_aggregation_enabled" to FALSE and executed queries again. Result was as expected - 0 rows returned.

It is noted in Metalink note that this issue is fixed in 10.2.0.3 version so I’ve upgraded test instance to 10.2.0.4 version and that solved problems with incorrect results.

This is nasty Oracle bug and if you are running 10.2.0.1 or 10.2.0.2 check your results :)

Recover lost datafile without valid backup

2011-06-09T16:10:00.005+02:00

I’ve decided to perform this test case and publish blog post after reading this topic on OTN forums - http://bit.ly/mLwaA9

How to recover lost datafile when you don’t have valid backup of your database. Your database is running in archivelog mode and you have all necessary archive logs.

In situation when you don’t have valid backup and your datatabase is running in noarchivelog mode - your datafile is lost forever.

So take regular backups of your database and enable archivelog mode ;-)

Environment for this test:
OS: OEL 5
DB: Oracle EE 10.2.0.4

To check am I running my database in archivelog mode.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     70
Next log sequence to archive   72
Current log sequence           72

I will create new tablespace for my test and name it - TS_TEST.

SQL> create tablespace ts_test datafile '/u01/oradata/orcl/ts_test01.dbf' size 256M;

Tablespace created.

Now to create table in TS_TEST tablespace. I will fill this table with 1000000 rows using Tom Kyte's script.

SQL> create table bigtab tablespace ts_test
  2  as
  3  select rownum id, a.*
  from all_objects a
 where 1=0;  4    5
 
 
 
SQL> select tablespace_name, segment_name from 
2         dba_segments where segment_name = 'BIGTAB';

TABLESPACE_NAME                SEGMENT_NAME
--------------- ---------------------------------------
TS_TEST                        BIGTAB
 
--
-- I'VE EXCLUDED SCRIPT LINES CAUSE
-- IT'S NOT IMPORTANT FOR THIS TEST CASE
--
SQL> select count(*) from bigtab;

  COUNT(*)
----------
   1000000

I can see in alert log that there were 5 log switches during insert.

Starting control autobackup
Control autobackup written to DISK device
        handle '/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2011_06_09/o1_mf_s_753372242_6z1folfy_.bkp'
Completed: create tablespace ts_test datafile '/u01/oradata/orcl/ts_test01.dbf' size 256M
Thu Jun  9 14:07:42 2011
Thread 1 advanced to log sequence 73 (LGWR switch)
  Current log# 1 seq# 73 mem# 0: /u01/oradata/orcl/redo01.log
Thu Jun  9 14:07:45 2011
Thread 1 advanced to log sequence 74 (LGWR switch)
  Current log# 2 seq# 74 mem# 0: /u01/oradata/orcl/redo02.log
Thu Jun  9 14:10:55 2011
Thread 1 cannot allocate new log, sequence 75
Checkpoint not complete
  Current log# 2 seq# 74 mem# 0: /u01/oradata/orcl/redo02.log
Thu Jun  9 14:10:56 2011
Thread 1 advanced to log sequence 75 (LGWR switch)
  Current log# 3 seq# 75 mem# 0: /u01/oradata/orcl/redo03.log
Thu Jun  9 14:10:58 2011
Thread 1 advanced to log sequence 76 (LGWR switch)
  Current log# 1 seq# 76 mem# 0: /u01/oradata/orcl/redo01.log
Thread 1 cannot allocate new log, sequence 77
Checkpoint not complete
  Current log# 1 seq# 76 mem# 0: /u01/oradata/orcl/redo01.log
Thu Jun  9 14:11:00 2011
Thread 1 advanced to log sequence 77 (LGWR switch)
  Current log# 2 seq# 77 mem# 0: /u01/oradata/orcl/redo02.log

To simulate disaster I will delete “ts_test01.dbf” datafile using OS command and flush buffer cache several times.

SQL> !rm /u01/oradata/orcl/ts_test01.dbf

SQL> alter system flush buffer_cache;

System altered.

SQL> /

System altered.

SQL> /

System altered.

Now my query throws an error because datafile 8 is missing.

SQL> select count(*) from bigtab;
select count(*) from bigtab
*
ERROR at line 1:
ORA-01116: error in opening database file 8
ORA-01110: data file 8: '/u01/oradata/orcl/ts_test01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

How to recover this datafile when you don’t have valid backup to restore it?

Trick is to create new datafile based on the file “ts_test01.dbf” using command:
ALTER DATABASE CREATE DATAFILE 'xxxx.dbf' AS 'xxxx.dbf' and fill it with data from archive logs.

SQL> alter database create datafile '/u01/oradata/orcl/ts_test01.dbf' as '/u01/oradata/orcl/ts_test01.dbf';
alter database create datafile '/u01/oradata/orcl/ts_test01.dbf' as '/u01/oradata/orcl/ts_test01.dbf'
*
ERROR at line 1:
ORA-01182: cannot create database file 8 - file is in use or recovery
ORA-01110: data file 8: '/u01/oradata/orcl/ts_test01.dbf'

To avoid error - before creating new datafile, you must take the existing datafile (or the tablespace) offline.

SQL> alter database datafile 8 offline drop;

Database altered.


SQL> alter database create datafile '/u01/oradata/orcl/ts_test01.dbf' as '/u01/oradata/orcl/ts_test01.dbf';

Database altered.

Now I have new empty datafile which I will fill with records applying archive logs over it during recovery.

SQL> recover datafile 8;
ORA-00279: change 92069000 generated at 06/09/2011 14:03:59 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_09/o1_mf_1_72_%u_.arc
ORA-00280: change 92069000 for thread 1 is in sequence #72


Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 92069587 generated at 06/09/2011 14:07:42 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_09/o1_mf_1_73_%u_.arc
ORA-00280: change 92069587 for thread 1 is in sequence #73
ORA-00278: log file
'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_09/o1_mf_1_72_6z1fw
gvn_.arc' no longer needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 92069713 generated at 06/09/2011 14:07:45 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_09/o1_mf_1_74_%u_.arc
ORA-00280: change 92069713 for thread 1 is in sequence #74
ORA-00278: log file
'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_09/o1_mf_1_73_6z1fw
kyj_.arc' no longer needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 92070100 generated at 06/09/2011 14:10:56 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_09/o1_mf_1_75_%u_.arc
ORA-00280: change 92070100 for thread 1 is in sequence #75
ORA-00278: log file
'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_09/o1_mf_1_74_6z1g2
jh1_.arc' no longer needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 92071034 generated at 06/09/2011 14:10:58 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_09/o1_mf_1_76_%u_.arc
ORA-00280: change 92071034 for thread 1 is in sequence #76
ORA-00278: log file
'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_09/o1_mf_1_75_6z1g2
lbg_.arc' no longer needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 92071751 generated at 06/09/2011 14:11:00 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_09/o1_mf_1_77_%u_.arc
ORA-00280: change 92071751 for thread 1 is in sequence #77
ORA-00278: log file
'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_09/o1_mf_1_76_6z1g2
nmz_.arc' no longer needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}

Log applied.
Media recovery complete.

Recovery is successfully completed and now to alter datafile 8 to be online again.

SQL> alter database datafile 8 online;

Database altered.


-- CHECK
SQL> select count(*) from bigtab;

  COUNT(*)
----------
   1000000


-- CLEANUP

SQL> drop tablespace ts_test including contents and datafiles;

Tablespace dropped.

Oracle Listener crashes with core dump in Linux 64bit

2011-06-08T11:24:00.000+02:00

We had serious problems with Oracle TNS Listener last few days. Problems were happening on very critical system - most active OLTP database.

Every few hours/minutes, in irregular intervals, TNS listener suddenly crashes which forces our Red Hat Cluster Manager to restart instance and even switch database to another node. Business systems that have relied on this database were experiencing great difficulties.

It is worth to mention that everything worked without any problems for about 2 years without any intervention on OS or database configuration.

Environment:
OS: Red Hat Enterprise Linux ES release 4 (64bit)
DB: Oracle EE 10.2.0.4.0

From Listener log:

…
…
…
07-LIP-2011 11:19:21 * (CONNECT_DATA=(SERVICE_NAME=odb )(CID=(PROGRAM=httpd)(HOST=rad2)(USER=apache))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.3.13.49)(PORT=58405)) * establish * odb * 12518
TNS-12518: TNS:listener could not hand off client connection
TNS-12547: TNS:lost contact
 TNS-12560: TNS:protocol adapter error
  TNS-00517: Lost contact
    Linux Error: 32: Broken pipe
07-LIP-2011 11:19:21 * service_update * odb * 0
07-LIP-2011 11:19:21 * service_update * odb * 0
07-LIP-2011 11:19:21 * (CONNECT_DATA=(SERVICE_NAME=otp)(CID=(PROGRAM=httpd)(HOST=rad2)(USER=apache))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.3.13.49)(PORT=58402)) * establish * odb * 0

TNSLSNR for Linux: Version 10.2.0.4.0 - Production on 07-LIP-2011 11:21:23

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

System parameter file is /u01/app/oracle/product/10.2/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/10.2/db_1/network/log/listener_odb.log
Trace information written to /u01/app/oracle/product/10.2/db_1/network/trace/listener_odb.trc
Trace level is currently 0

Started with pid=9607
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.2.10.40)(PORT=1521)))
Listener completed notification to CRS on start
…
…
...

….
….
….
07-LIP-2011 11:10:16 * (CONNECT_DATA=(SERVICE_NAME=UDB)(CID=(PROGRAM=)(HOST=POP1)(USER=root))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.3.12.71)(PORT=44557)) * establish * UDB * 12518
TNS-12518: TNS:listener could not hand off client connection
TNS-12571: TNS:packet writer failure
 TNS-12560: TNS:protocol adapter error
  TNS-00530: Protocol adapter error
    Linux Error: 104: Connection reset by peer

TNSLSNR for Linux: Version 10.2.0.4.0 - Production on 07-LIP-2011 11:11:01

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

System parameter file is /u01/app/oracle/product/10.2/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/10.2/db_1/network/log/listener_udb.log
Trace information written to /u01/app/oracle/product/10.2/db_1/network/trace/listener_udb.trc
Trace level is currently 0

Started with pid=21862
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.2.10.39)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Listener completed notification to CRS on start

TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
WARNING: Subscription for node down event still pending
…
…

From /var/log/messages:

tnslsnr[31928]: segfault at 0000000000000018 rip 0000003d34268593 rsp 0000007fbfff9ba0 error 4
tnslsnr[21862]: segfault at 0000000000000018 rip 0000003c00367f2d rsp 0000007fbfff9220 error 4

My first destinations to search for answers were Oracle support site, OTN forums and Oracle related technical blogs.

I’ve found interesting note on Oracle support site: [ID 549932.1]
Symptoms mentioned there:
- There may be heavy load on the CPU shooting up to 100%.
- The number of sessions in the database is well below the upper or maximum limit defined in the parameter file.
- The listener crashes suddenly during this heavy CPU load generating the core.
- (Optional) Listener.Ora has SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF.

Cause:
Extensive paging/swapping activity is a clear indication that the system is running out of the physical memory.

So I’ve decided to run vmstat for hour or two to check for swapping activity.

$vmstat 5

Columns si and so were 0 all the time so I thought that our problems weren’t associated with paging/swapping activity. We continued to search problems in other areas.

To cut a long story short - after few days searching for answers we found nothing so I’ve decided to apply patch for unpublished Bug 6139856 despite the fact that I haven't found problem with swapping.

Before applying patch I like to take backup of Oracle Home so I’ve decided to make compressed copy of that directory. Soon after compression began CPU load significantly raised and vmstat started to display swapping activity.

It was pretty clear now that paging/swapping activity is probably the cause of our problems so I applied the patch 6139856.
That fixed our problems and system was stable again :-)

Conclusion
Due to some recent network/firewall problems CPU load on our OLTP database server occasionally raises, causes extensive swapping which crashes TNS listener.

Datapump fails with ORA-27086 on NFS

2011-05-31T14:07:00.001+02:00

I've received following error while trying to perform full database export using datapump to NFS:

Export: Release 10.2.0.4.0 - Production on Tuesday, 31 May, 2011 11:27:50

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Release 10.2.0.4.0 - Production
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "/backup-nfs/testdb/export/exportfull.dmp"
ORA-27086: unable to lock file - already in use
Linux Error: 37: No locks available
Additional information: 10

In this case problem was lying in NFS locking deamons - on most Unix-like systems also known as rpc.lockd and the rpc.statd.

Checking if 'nfslock' service is running:

# service nfslock status
rpc.statd is stopped

Start 'nfslock' service:

# /etc/init.d/nfslock start
Starting NFS statd:                                        [  OK  ]

Status:

# service nfslock status
rpc.statd (pid  4943) is running...

Starting 'nfslock' service resolved my problem with performing export to NFS.

Replacing old database servers (project check list)

2011-03-22T20:58:00.002+01:00

We are currently in process of replacing our old database servers with new ones. During the evaluation process we’re checking various technical solutions to find the best option in terms of price/performance.

For me, real life experience is the most important when we are talking about database server performance so I’ve posted question on Oracle-L lists to get some answers about noted servers and performance.

DBA’s from Oracle-L lists helped me a lot. Thanks guys!

As replay to my question David Robillard posted answer that is worth publishing on this blog. It is nicely summarized project check list that can be used as a template when evaluating various technical solutions in or outside world of Oracle.

We will surely use this check list in our projects :-)

What to look for when buying (database) servers.

Hello Marko,

> we're considering to replace our SunFire v490 servers with newer generation
> of Oracle SUN or IBM servers.
>
> Our current configuration is:
> 2x SunFire v490 , 4GB RAM , 2 (1x dual-port HBA) x 2Gb FC
> 2x SunFire v490 , 8GB RAM , 2 (1x dual-port HBA) x 2Gb FC
>
> Do you have any experience with Oracle M-Series SPARC servers or x86 Sun
> servers running as database servers?

Yes indeed :)

> We were checking Intel 7500 X4470 and Sparc VII+ M4000 servers as replacement
> for our SunFire v490's.

Both CPU architectures can run your type of workload, that's for sure.
What you need to look out for is (but not limited to)

a) Budget.
b) Server's RAS & Virtualization features.
c) Server's technical specs.
d) Employee technical knowledge in hardware and software.
e) Data center system's homogenization.

So if we go into more detail for each bullet point, we have...

-- a) Budget.

This is a very important starting point in which you need to check: 1.
Oracle software licenses, 2. Oracle hardware cost of purchase, 3.
Oracle software and hardware maintenance costs and 4. electrical and
HVAC costs.

1. Oracle software licenses.

Make sure to check with your Oracle database friendly salesman. I
don't know if this is still the case, but the Oracle license cost for
an x86 CPU was not the same as for the SPARC achitecture. Count the
number of cores and run the numbers with your Oracle software
representative. Unless things have changed since the aquisition of Sun
by Oracle, you might be in for a little surprise.

2. Oracle hardware cost of purchase.

The X4470 and M4000 machines are probably not the same price up front.
And you might not be able to get the same discount on both
architectures. Make sure to talk with your IHV or Oracle hardware
technical representative to see what configuration of each system is
adequate for your workload and then get a price for both system types.
There could be a big difference.

3. Oracle software and hardware maintenance costs.

Depending on the type of hardware, your Oracle software licenses might
be different. So is the maintenance contract on the hardware itself.
Make sure to check those two over a period of 3 to 5 years and compare
the TCO at the end of the system's life cycle. Again, that can be
quite significant. Considering your pay around 20 % annually of the
total cost of purchase on the Oracle software licenses, if they're
more expensive up front, they're going to be a lot more expensive
after 5 years. I'm not sure about the 20 %, so check this number with
your Oracle sales rep.

4. electrical and HVAC costs.

Check the electrical specs for both systems. Keep in mind that you
need to feed these machines power to make the run and more power to
cool them. Since they probably won't have the same requirements, make
sure to check this with your data center specialist to see how much
money it will cost to run those systems? Again, run those numbers up
to 3 or 5 years and compare them.

-- b) Server's RAS and Virtualization features.

The M-series is packed with RAS features. Especially if you run
Solaris on them. Compare those features with what the X-series
platform offers you. The M-series also has built in hardware
virtualization with logical domains and such. In an Oracle database
scenario, are those important? For example, you could decide to assign
a single CPU/memory board for OLTP and perform a dynamic domain change
to add the other CPU/memory board for backup or batch jobs. Will you
be using RAC? Don't forget to ask your Oracle software and hardware
reps to know if you can run RAC in logical domains and/or Solaris
containers?

Make a side-by-side comparision and see what is an added-value to your
organisation and what is simly a nice to have. See if the price
difference is worth the features?

-- c) Server's technical specs.

The M4000 machine can use up to 256 GB while the X4470 is limited to
64 GB. Is that interesting to you? Of course most of us would you
benifit from a very big SGA, but does it makes sense to pay for the
M4000 and 256 GB if your workload is happy with a 10 GB SGA that fits
into both hardware platforms?

The Intel 7500 CPU is most probably faster then the SPARC64 VII+ for
single threaded applications. But the SPARC architecture is probably
better wth multi-threaded applications. Check your SQL code and see if
you would benefit from one or the other. Ask your Oracle hardware rep
for Oracle benchmarks on both architectures and try to compare apples
to apples.

The X4470 is a 3 U machine while the M4000 requires double that. Do
you have enough space in your data center racks to have the new
machines online while the old ones are still there? You will need it
in order to have a smooth transition of your old production machines
to the new ones.

-- d) Employee technical knowledge in hardware and software.

Your team of sysadmins and DBAs have obviously been working with
Solaris + SPARC for a few years now. Are they familiar with Linux +
x86? Or Solaris + x86? They are not the same. Even Solaris SPARC vs
Solaris x86 is not the same. If you have all your operating practices
documented on Solaris + SPARC, are you ready to update all those to
another OS? Will you need to send sysadmins to Oracle university to
learn about Solaris x86, the new M4000 or the X4470? Don't forget to
factor these into the overall price of the solution too. You talked
about IBM machines. Will they be running AIX or Linux? Consider that
the IBM POWER systems running AIX are *very* different then the Oracle
SPARC systems running Solaris. Both are very good products, but be
ready for a big learning curve if you switch from one to another.

IMHO you should stick to what you know and comfortable with. In this
case this is Solaris SPARC. But the price might be prohibitive. If you
need to switch to Solaris x86, consider training your staff to Linux
maybe?

-- e) Data center system's homogenization.

What do you have in your data center? Only Sun Oracle hardware or a
mix of many vendors? It's a lot easier to handle a single vendor then
three to four different ones. Plus the spare parts are easier to stock
and share between the machines of an identical vendor. You also have
an easier time to manage the service contracts from one or two vendors
then three of four. If your team is trained on Sun and Oracle
procedures and hardware, be wary of moving to IBM. It's a lot
different and the learning curve is steep. Keep in mind that it's also
more expensive and more difficult to keep and find trained sysadmins
on different OSes and platforms. Think of what your job offer would
look like if you need to hire another sysadmin. The more product you
have, the more difficult it will be to find qualified sysadmins. Not
to mention that they might ask for a higher salary :)

> Do you think that Intel 7500 X4470 is strong enough to handle workload of two
> SunFire v490 servers?
> Same question related to Sparc VII+ M4000 server - can this server handle
> workload of two SunFire v490's?

Again, ask your Oracle rep these questions. I'm pretty sure the answer
is yes, both machines can support your workload. But at what speed?
And at what cost?

Finally, make sure you have fun! That's a very nice project you have there :o)

Cheers,

David

"SQL*Net message to/from dblink" - How to improve query performance over dblink?

2011-02-22T15:35:00.003+01:00

Colleague reported to me that execution of his query is taking too long. After quick look it was obvious that this was ideal tunning candidate.
I will note my tuning process in this blog post.

Table and column names are changed.

DB: Oracle 10.2.0.2.0
OS: Solaris 9 64-bit

Query:

SELECT
       ROUND (
          SUM (poz_traj) / 60,2)
          mins,
       SUM (amount) iznos,
       COUNT (*) pozivi
  FROM 
       (SELECT co.con_nr
          FROM usr.ugov@remote_db co,
               usr.ugov_item@remote_db ci,
               usr.np_zah@remote_db np
         WHERE     1 = 1
               AND co.ugov_id = ci.ugov_id
               AND ci.ugov_item_id = np.ugov_item_id
               AND NP.kol_id IN
                      (355375, 360996, 424469, 466193, 466194, 480438, 492748)
               AND np.u_po IS NOT NULL
               AND status_cd = 'PRI'
               AND co.status <> 'CC') c,
             table1 x
 WHERE 1 = 1 
       AND x.sub_cd = c.con_nr
       AND record_date >=
              TO_DATE ('24.01.2011 08:40:01', 'dd.mm.yyyy hh24:mi:ss')
       AND record_date <
              TO_DATE ('24.01.2011 18:00:01', 'dd.mm.yyyy hh24:mi:ss')
       AND orgn = 0                                     
       AND item_cd = 1                      
       AND corr_status > -1
       AND parameter_sg = 'glas';

This query is using database link to access tables on remote database - "REMOTE_DB".

Explain plan showed:

NOTE!
Click "view source" to get explain plan better formatted.

----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop | Inst   |IN-OUT|
----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |               |       |       |   105 (100)|          |       |       |        |      |
|   1 |  SORT AGGREGATE                        |               |     1 |   275 |            |          |       |       |        |      |
|   2 |   NESTED LOOPS                         |               |     1 |   275 |   105   (0)| 00:00:03 |       |       |        |      |
|   3 |    NESTED LOOPS                        |               |    32 |  3264 |   103   (0)| 00:00:03 |       |       |        |      |
|   4 |     NESTED LOOPS                       |               |    16 |  1216 |    55   (0)| 00:00:02 |       |       |        |      |
|   5 |      PARTITION RANGE SINGLE            |               |    16 |   704 |    23   (0)| 00:00:01 |    37 |    37 |        |      |
|*  6 |       TABLE ACCESS BY LOCAL INDEX ROWID| TABLE1        |    16 |   704 |    23   (0)| 00:00:01 |    37 |    37 |        |      |
|*  7 |        INDEX RANGE SCAN                | IX_TABLE1_RD  |    21 |       |     3   (0)| 00:00:01 |    37 |    37 |        |      |
|   8 |      REMOTE                            | UGOV          |     1 |    32 |     2   (0)| 00:00:01 |       |       | REMO_~ | R->S |
|   9 |     REMOTE                             | UGOV_ITEM     |     2 |    52 |     3   (0)| 00:00:01 |       |       | REMO_~ | R->S |
|  10 |    REMOTE                              | NP_ZAH        |     1 |   173 |     1   (0)| 00:00:01 |       |       | REMO_~ | R->S |
----------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - filter(("item_cd"=1 AND "parameter_sg"='glas' AND "orgn"=0 AND "corr_status">(-1)))
   7 - access("RECORD_DATE">=TO_DATE('2011-01-24 08:40:01', 'yyyy-mm-dd hh24:mi:ss') AND "RECORD_DATE"<TO_DATE('2011-01-24
              18:00:01', 'yyyy-mm-dd hh24:mi:ss'))

To help myself a little I’ve checked how many rows there are in tables queried in inline view.

ugov - 777802 rows
ugov_item - 1581553 rows
np_zah - 155802 rows

SQL>  SELECT co.con_nr
  2            FROM usr.ugov@remote_db co,
  3                 usr.ugov_item@remote_db ci,
  4                 usr.np_zah@remote_db np
  5           WHERE     1 = 1
  6                 AND co.ugov_id = ci.ugov_id
  7                 AND ci.ugov_item_id = np.ugov_item_id
  8                 AND NP.kol_id IN
  9                        (355375, 360996, 424469, 466193, 466194, 480438, 492748)
 10                 AND np.u_po IS NOT NULL
 11                 AND status_cd = 'PRI'
 12                 AND co.status <> 'CC';

4599 rows selected.

Elapsed: 00:00:23.60

Execution of inline view was satisfying with 4599 rows received.

After this initial checks it was obvious where problem lies. Oracle probably transfers whole tables over network from remote database and then resolves the query.

To confirm my thoughts I’ve decided to trace execution of the current query and check wait events.

Results were:

SQL> SELECT
  2         ROUND (
  3            SUM (poz_traj) / 60,
  4            2)
  5            mins,
  6         SUM (amount) iznos,
  7         COUNT (*) pozivi
  8    FROM
  9         (SELECT co.con_nr
 10            FROM usr.ugov@remote_db co,
 11                 usr.ugov_item@remote_db ci,
 12                 usr.np_zah@remote_db np
 13           WHERE     1 = 1
 14                 AND co.ugov_id = ci.ugov_id
 15                 AND ci.ugov_item_id = np.ugov_item_id
 16                 AND NP.kol_id IN
 17                        (355375, 360996, 424469, 466193, 466194, 480438, 492748)
 18                 AND np.u_po IS NOT NULL
 19                 AND status_cd = 'PRI'
 20                 AND co.status <> 'CC') c,
 21               table1 x
 22   WHERE 1 = 1
 23         AND x.sub_cd = c.con_nr
 24         AND record_date >=
 25                TO_DATE ('24.01.2011 08:40:01', 'dd.mm.yyyy hh24:mi:ss')
 26         AND record_date <
 27                TO_DATE ('24.01.2011 18:00:01', 'dd.mm.yyyy hh24:mi:ss')
 28         AND orgn = 0
 29         AND item_cd = 1
 30         AND corr_status > -1
 31         AND parameter_sg = 'glas';

1 row selected.

Elapsed: 01:22:23.17

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to dblink                 3055416        0.00          1.55
  SQL*Net message from dblink               3055416        2.73       3835.51
  db file sequential read                     34051        0.51        226.57
  SQL*Net message from client                     2      323.35        323.35
  SQL*Net message to client                       1        0.00          0.00

Time of query execution was 1:22:23.

Top wait events were "SQL*Net message to dblink" and "SQL*Net message from dblink".

I’ve had network bottleneck because query produced lot of traffic between databases.

To resolve this problem I have to minimize data transfer from remote database.
Inline view should be executed on remote database to cut down number of rows that are transferred over dblink.

So I’ve created view on remote database and checked execution again.

REMOTE DATABASE

create view temp_view_test as
  SELECT co.con_nr
          FROM usr.ugov co,
               usr.ugov_item ci,
               usr.np_zah np
         WHERE     1 = 1
               AND co.ugov_id = ci.ugov_id
               AND ci.ugov_item_id = np.ugov_item_id
               AND NP.kol_id IN
                      (355375, 360996, 424469, 466193, 466194, 480438, 492748)
               AND np.u_po IS NOT NULL
               AND status_cd = 'PRI'
               AND co.status <> 'CC';

What will happen now:

SQL > SELECT
  2         ROUND (
  3            SUM (poz_traj) / 60,
  4            2)
  5            mins,
  6         SUM (amount) iznos,
  7         COUNT (*) pozivi
  8    FROM
  9         usr.temp_view_test@remote_db c,
 10               table1 x
 11   WHERE 1 = 1
 12         AND x.sub_cd = c.con_nr
 13         AND record_date >=
 14                TO_DATE ('25.01.2011 08:40:01', 'dd.mm.yyyy hh24:mi:ss')
 15         AND record_date <
 16                TO_DATE ('25.01.2011 18:00:01', 'dd.mm.yyyy hh24:mi:ss')
 17         AND orgn = 0
 18         AND item_cd = 1
 19         AND corr_status > -1
 20         AND parameter_sg = 'glas';

1 row selected.

Elapsed: 00:02:55.53

----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop | Inst   |IN-OUT|
----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                 |       |       |   625 (100)|          |       |       |        |      |
|   1 |  SORT AGGREGATE                      |                 |     1 |    56 |            |          |       |       |        |      |
|*  2 |   HASH JOIN                          |                 |     8 |   448 |   625   (1)| 00:00:13 |       |       |        |      |
|   3 |    PARTITION RANGE SINGLE            |                 |    16 |   704 |    23   (0)| 00:00:01 |    37 |    37 |        |      |
|*  4 |     TABLE ACCESS BY LOCAL INDEX ROWID| TABLE1          |    16 |   704 |    23   (0)| 00:00:01 |    37 |    37 |        |      |
|*  5 |      INDEX RANGE SCAN                | IX_TABLE1_RD    |    21 |       |     3   (0)| 00:00:01 |    37 |    37 |        |      |
|   6 |    REMOTE                            | TEMP_VIEW_TEST  | 51418 |   602K|   601   (1)| 00:00:12 |       |       | REMO_~ | R->S |
----------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("X"."sub_cd"="C"."con_nr")
   4 - filter(("item_cd"=1 AND "parameter_sg"='glas' AND "orgn"=0 AND "corr_status">(-1)))
   5 - access("RECORD_DATE">=TO_DATE('2011-01-24 08:40:01', 'yyyy-mm-dd hh24:mi:ss') AND "RECORD_DATE"<TO_DATE('2011-01-24
              18:00:01', 'yyyy-mm-dd hh24:mi:ss'))

This was significant performance improvement. From 1 hour 22 mins to 2 mins 55 secs !

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                     27932        0.27         41.32
  SQL*Net message to dblink                       2        0.00          0.00
  SQL*Net message from dblink                     2       42.79         44.46
  SQL*Net more data from dblink                  36        3.24         65.83
  SQL*Net message from client                     2        0.01          0.01
  SQL*Net message to client

Top wait events weren't related to network bottlenecks.

So the trick was to create view on remote database which reduced number of rows transferred over network.

Colleague was happy with new execution time :)

Invalid objects after Revoking Execute On DBMS_JOB, DBMS_LOB, UTL_TCP, UTL_HTTP, UTL_SMTP, UTL_FILE from PUBLIC

2010-11-19T16:57:00.002+01:00

You can use Oracle Grid Control for checking policy violations, to keep compliance with company security or configuration standards. Grid Control comes with pre-installed set of policies and recommendations of best practices for databases.

With having that in mind I checked Critical Policy Violations for my database and noticed this critical violations:

...
Execute Privileges on DBMS_JOB to PUBLIC 
Restricted Privilege to Execute UTL_TCP
Restricted Privilege to Execute UTL_HTTP 
Restricted Privilege to Execute UTL_SMTP 
Execute Privileges on DBMS_LOB to PUBLIC 
Execute Privileges on UTL_FILE To PUBLIC
...

It is recommended to remove excessive privileges from some users to prevent possible attacks for happening in the first place. So based on recommendations from Grid Control I’ve decided to revoke noted privileges from PUBLIC and check what will happen after.

As SYS user I executed:

REVOKE EXECUTE ON utl_tcp FROM public;
REVOKE EXECUTE ON utl_smtp FROM public;
REVOKE EXECUTE ON utl_http FROM public;
REVOKE EXECUTE ON utl_file FROM public;
REVOKE EXECUTE ON dbms_job FROM public;
REVOKE EXECUTE ON dbms_lob FROM public;

Immediately after revoking those grants I checked database status noticed invalid objects in sys schema, mdsys, ordsys, wmsys, etc.

My next move was rollback:

GRANT EXECUTE ON dbms_job TO public;
GRANT EXECUTE ON utl_file TO public;
GRANT EXECUTE ON utl_tcp TO public;
GRANT EXECUTE ON utl_http TO public;
GRANT EXECUTE ON utl_smtp TO public;
GRANT EXECUTE ON dbms_lob TO public;

-- Compile
SQL> shutdown immediate;
SQL> startup restrict;
SQL> @$ORACLE_HOME/rdbms/admin/@utlrp.sql
SQL> shutdown immediate;
SQL> startup;

After granting privileges back and recompiling invalid objects everything was valid again.
This test was very educational for me.

My next step was searching for Metalink notes that explain this situation and I’ve found very useful notes - [ID 247093.1] [ID 1165830.1] [ID 797706.1]

Important lesson learned in this experiment was - do not blindly follow EM/GC policy recommendations without consulting Oracle support or (at least) skimming through Metalink notes.

From Metalink note:
“Oracle highly recommends keeping DBMS_SQL, DBMS_JOB, DBMS_LOB granted to PUBLIC just to keep user’s database running. Otherwise lots of things break including some of the utilities like Import/Export, Datapump expdp/impdp, SQL*Loader, etc.”

PUBLIC is pseudo user/schema used by database for its internal functionality. Privileges are granted to PUBLIC with CATALOG and CATPROC components for 10g database instance.

If you still want to revoke privileges from PUBLIC make sure that you re-grant privileges to user that require them and recompile possible invalidated objects.

RMAN restore fails with RMAN-06023 but there are backups available

2010-11-18T12:56:00.002+01:00

I must admit - today I learned new thing about RMAN restore and this is good enough reason for a blog post.

Part of my daily tasks is to perform RMAN backup and recovery scenarios in my test environment (as I said beore), to be familiar with every possible situation that can happen.

So today, very simple task of restoring database to another machine took me over 2 hours...

Demo case:
Environment : Oracle 10.2.0.4 on Linux 64bit

I have backup files of the database:

$ ls -l /oracle/backup/*
ARCH_OTP_20101117_0tlt6lum_s8221_p1
controlf_OTP_0ult6mop_8222_20101117
FULL_OTP_0rlt6ip4
spfile_OTP_0vlt6mos_8223_20101117

Restore controlfile from backup and mount database:

$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Thu Nov 18 08:32:09 2010
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
connected to target database (not started)

RMAN> startup nomount;
Oracle instance started
Total System Global Area     838860800 bytes
Fixed Size                     2087672 bytes
Variable Size                750781704 bytes
Database Buffers              67108864 bytes
Redo Buffers                  18882560 bytes

RMAN> run
2> {
3> allocate channel c1 device type disk;
4> restore controlfile from '/oracle/backup/controlf_OTP_0ult6mop_8222_20101117';
5> }

Starting restore at 18.11.2010 08:40:51

channel c1: restoring control file
channel c1: restore complete, elapsed time: 00:00:01
output filename=/oracle/product/10.2.0/db_1/dbs/control01.ctl
output filename=/oracle/product/10.2.0/db_1/dbs/control02.ctl
Finished restore at 18.11.2010 08:40:52
released channel: c1

RMAN> alter database mount;
database mounted

I will delete all EXPIRED backups from RMAN repository:

RMAN> crosscheck backup;
RMAN> delete noprompt expired backup;

RMAN> crosscheck archivelog all;
RMAN> delete noprompt expired archivelog all;

For this case it is very important to mention that I’ve had enabled controlfile autobackup (CONFIGURE CONTROLFILE AUTOBACKUP ON) in older incarnations. With this parameter enabled RMAN automatically takes backup of controlfile and server parameter file whenever the database structure metadata in the control file changes or whenever a backup or copy operation is performed using RMAN.

List backup command shows that there are two old autobackup files in flash recovery area.

RMAN> list backup;


List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
8199    Full    7.89M      DISK        00:00:00     16.06.2010 14:50:28
        BP Key: 8199   Status: AVAILABLE  Compressed: NO  Tag: TAG20100616T145028
        Piece Name: /oracle/oradata/archive/flash_recovery_area/OTP/autobackup/2010_06_16/o1_mf_s_721839028_61kl4o2y_.bkp
  Control File Included: Ckp SCN: 43471045788   Ckp time: 16.06.2010 14:50:28
  SPFILE Included: Modification time: 16.06.2010 13:15:03

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
8200    Full    7.89M      DISK        00:00:00     16.06.2010 14:49:49
        BP Key: 8200   Status: AVAILABLE  Compressed: NO  Tag: TAG20100616T144949
        Piece Name: /oracle/oradata/archive/flash_recovery_area/OTP/autobackup/2010_06_16/o1_mf_s_721838989_61kl3hhq_.bkp
  Control File Included: Ckp SCN: 43471045571   Ckp time: 16.06.2010 14:49:49
  SPFILE Included: Modification time: 16.06.2010 13:15:03

To continue with my restore process I will catalog my backup files noted before.

RMAN> catalog start with '/oracle/backup/';

searching for all files that match the pattern /oracle/backup/

List of Files Unknown to the Database
=====================================
File Name: /oracle/backup/FULL_OTP_0rlt6ip4
File Name: /oracle/backup/ARCH_OTP_20101117_0tlt6lum_s8221_p1
File Name: /oracle/backup/controlf_OTP_0ult6mop_8222_20101117

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /oracle/backup/FULL_OTP_0rlt6ip4
File Name: /oracle/backup/ARCH_OTP_20101117_0tlt6lum_s8221_p1
File Name: /oracle/backup/controlf_OTP_0ult6mop_8222_20101117

Check backup of database.

RMAN> list backup of database;


List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
8196    Incr 0  11.18G     DISK        00:53:38     17.11.2010 01:53:58
        BP Key: 8201   Status: AVAILABLE  Compressed: YES  Tag: WEEKLY_FULL
        Piece Name: /oracle/backup/FULL_OTP_0rlt6ip4
  List of Datafiles in backup set 8196
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  1    0  Incr 48852276752 17.11.2010 01:00:20 /u02/oradata/system/otp/system01.dbf
  2    0  Incr 48852276752 17.11.2010 01:00:20 /u02/oradata/data02/otp/users02.dbf
  3    0  Incr 48852276752 17.11.2010 01:00:20 /u02/oradata/system/otp/sysaux01.dbf
  4    0  Incr 48852276752 17.11.2010 01:00:20 /u02/oradata/data02/otp/users01.dbf
  5    0  Incr 48852276752 17.11.2010 01:00:20 /u02/oradata/index/otp/tools01.dbf
  6    0  Incr 48852276752 17.11.2010 01:00:20 /u02/oradata/data02/otp/users03.dbf
  7    0  Incr 48852276752 17.11.2010 01:00:20 /u02/oradata/system/otp/undotbs01.dbf
  8    0  Incr 48852276752 17.11.2010 01:00:20 /u02/oradata/system/otp/cluser_tbs01.dbf

Restore database files from backup to another location.

RMAN> run
2> {
3> allocate channel c1 device type disk;
4> SET NEWNAME FOR DATAFILE 1 TO '/oracle/u02/oradata/system/otp/system01.dbf';
5> SET NEWNAME FOR DATAFILE 2 TO '/oracle/u02/oradata/data02/otp/users02.dbf';
6> SET NEWNAME FOR DATAFILE 3 TO '/oracle/u02/oradata/system/otp/sysaux01.dbf';
7> SET NEWNAME FOR DATAFILE 4 TO '/oracle/u02/oradata/data02/otp/users01.dbf';
8> SET NEWNAME FOR DATAFILE 5 TO '/oracle/u02/oradata/index/otp/tools01.dbf';
9> SET NEWNAME FOR DATAFILE 6 TO '/oracle/u02/oradata/data02/otp/users03.dbf';
10> SET NEWNAME FOR DATAFILE 7 TO '/oracle/u02/oradata/system/otp/undotbs01.dbf';
11> SET NEWNAME FOR DATAFILE 8 TO '/oracle/u02/oradata/system/otp/cluser_tbs01.dbf';
12>
13> RESTORE DATABASE;
14> SWITCH DATAFILE ALL;
15> RECOVER DATABASE;
16> }

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=321 devtype=DISK

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 18.11.2010 08:46:21

released channel: c1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/18/2010 08:46:22
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore

Hm...
For me this was unexpected error because I had database backup available.

RMAN> list backup of datafile 4;


List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
8196    Incr 0  11.18G     DISK        00:53:38     17.11.2010 01:53:58
        BP Key: 8201   Status: AVAILABLE  Compressed: YES  Tag: WEEKLY_FULL
        Piece Name: /oracle/backup/FULL_OTP_0rlt6ip4
  List of Datafiles in backup set 8196
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  4    0  Incr 48852276752 17.11.2010 01:00:20 /u02/oradata/data02/otp/users01.dbf

RMAN> list backup of datafile 3;


List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
8196    Incr 0  11.18G     DISK        00:53:38     17.11.2010 01:53:58
        BP Key: 8201   Status: AVAILABLE  Compressed: YES  Tag: WEEKLY_FULL
        Piece Name: /oracle/backup/FULL_OTP_0rlt6ip4
  List of Datafiles in backup set 8196
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  3    0  Incr 48852276752 17.11.2010 01:00:20 /u02/oradata/system/otp/sysaux01.dbf

…
…
…

After two hours of searching for answers and performing more tests I ran to the Metalink document 965122.1.

The problem were those autobackups in FRA that I showed before. That files belonged to different incarnation than the available backups current incarnation.

[From Metalink document]
If we start a RESTORE database with a BACKUP controlfile and Flash Recovery Area is defined, RMAN execute and implicit crosscheck and catalog of all the objects in the Flash Recovery Area.

RMAN will catalog any objects in the Flash Recovery Area that will not be registered in the controlfile and if any of this files belongs to an incarnation different from CURRENT incarnation in the controlfile then changes controlfile CURRENT incarnation to the one found in the file that is being cataloged.

This prevents database from restoring backups that belong to old CURRENT incarnation.
RMAN considers backup availble for being restored if the backup incarnation and CURRENT incarnation in controlfile are the same.

So I’ve decided to temporary disable FRA during recovery process commending db_recovery_file_dest and db_recovery_file_dest_size parameters in parameter file.

RMAN> shutdown abort;

Oracle instance shut down

$ vi $ORACLE_HOME/dbs/initotp.ora
...
#*.db_recovery_file_dest='/oracle/oradata/archive/flash_recovery_area'
#*.db_recovery_file_dest_size=2147483648
…
...

$ sqlplus "/as sysdba"

SQL> startup nomount pfile='$ORACLE_HOME/dbs/initotp.ora';
ORACLE instance started.

Total System Global Area  838860800 bytes
Fixed Size                  2087672 bytes
Variable Size             750781704 bytes
Database Buffers           67108864 bytes
Redo Buffers               18882560 bytes

Restore and recover database.

RMAN> run
2> {
3> allocate channel c1 device type disk;
4> restore controlfile from '/oracle/backup/controlf_OTP_0ult6mop_8222_20101117';
5> }
…
…
…
RMAN> alter database mount;
database mounted

RMAN> catalog start with '/oracle/backup';
…
…

RMAN> run
2> {
3> allocate channel c1 device type disk;
4> SET NEWNAME FOR DATAFILE 1 TO '/oracle/u02/oradata/system/otp/system01.dbf';
5> SET NEWNAME FOR DATAFILE 2 TO '/oracle/u02/oradata/data02/otp/users02.dbf';
6> SET NEWNAME FOR DATAFILE 3 TO '/oracle/u02/oradata/system/otp/sysaux01.dbf';
7> SET NEWNAME FOR DATAFILE 4 TO '/oracle/u02/oradata/data02/otp/users01.dbf';
8> SET NEWNAME FOR DATAFILE 5 TO '/oracle/u02/oradata/index/otp/tools01.dbf';
9> SET NEWNAME FOR DATAFILE 6 TO '/oracle/u02/oradata/data02/otp/users03.dbf';
10> SET NEWNAME FOR DATAFILE 7 TO '/oracle/u02/oradata/system/otp/undotbs01.dbf';
11> SET NEWNAME FOR DATAFILE 8 TO '/oracle/u02/oradata/system/otp/cluser_tbs01.dbf';
12>
13> RESTORE DATABASE;
14> SWITCH DATAFILE ALL;
15> RECOVER DATABASE;
16> }

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=321 devtype=DISK

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 18.11.2010 09:01:48

channel c1: starting datafile backupset restore
channel c1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oracle/u02/oradata/system/otp/system01.dbf
restoring datafile 00002 to /oracle/u02/oradata/data02/otp/users02.dbf
restoring datafile 00003 to /oracle/u02/oradata/system/otp/sysaux01.dbf
restoring datafile 00004 to /oracle/u02/oradata/data02/otp/users01.dbf
restoring datafile 00005 to /oracle/u02/oradata/index/otp/tools01.dbf
restoring datafile 00006 to /oracle/u02/oradata/data02/otp/users03.dbf
restoring datafile 00007 to /oracle/u02/oradata/system/otp/undotbs01.dbf
restoring datafile 00008 to /oracle/u02/oradata/system/otp/cluser_tbs01.dbf
channel c1: reading from backup piece /var/umoracle/otp/fullbkp_dir/FULL_OTP_0rlt6ip4
channel c1: restored backup piece 1
failover to piece handle=/mnt/l01dbdev-s01storage1/oracle/backup/FULL_OTP_0rlt6ip4 tag=WEEKLY_FULL
channel c1: restore complete, elapsed time: 01:28:51
Finished restore at 18.11.2010 10:30:40

datafile 1 switched to datafile copy
input datafile copy recid=9 stamp=735388241 filename=/oracle/u02/oradata/system/otp/system01.dbf
datafile 2 switched to datafile copy
input datafile copy recid=10 stamp=735388241 filename=/oracle/u02/oradata/data02/otp/users02.dbf
datafile 3 switched to datafile copy
input datafile copy recid=11 stamp=735388241 filename=/oracle/u02/oradata/system/otp/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=12 stamp=735388241 filename=/oracle/u02/oradata/data02/otp/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=13 stamp=735388241 filename=/oracle/u02/oradata/index/otp/tools01.dbf
datafile 6 switched to datafile copy
input datafile copy recid=14 stamp=735388242 filename=/oracle/u02/oradata/data02/otp/users03.dbf
datafile 7 switched to datafile copy
input datafile copy recid=15 stamp=735388242 filename=/oracle/u02/oradata/system/otp/undotbs01.dbf
datafile 8 switched to datafile copy
input datafile copy recid=16 stamp=735388242 filename=/oracle/u02/oradata/system/otp/cluser_tbs01.dbf

Starting recover at 18.11.2010 10:30:43

starting media recovery

channel c1: starting archive log restore to default destination
channel c1: restoring archive log
archive log thread=1 sequence=33610
channel c1: reading from backup piece /var/otp/fullbkp_dir/ARCH_OTP_20101117_0tlt6lum_s8221_p1
channel c1: restored backup piece 1
failover to piece handle=/oracle/backup/ARCH_OTP_20101117_0tlt6lum_s8221_p1 tag=ARCHIVELOGS
channel c1: restore complete, elapsed time: 00:11:44
archive log filename=/oracle/oradata/archive/arch_1_33610_586538926.arc thread=1 sequence=33610
unable to find archive log
archive log thread=1 sequence=33611
released channel: c1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/18/2010 10:50:16
RMAN-06054: media recovery requesting unknown log: thread 1 seq 33611 lowscn 48852472202
RMAN> exit


$ sqlplus "/as sysdba"

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 48852472202 generated at 11/17/2010 01:54:16 needed for
thread 1
ORA-00289: suggestion :
/oracle/oradata/archive/arch_1_33611_586538926.arc
ORA-00280: change 48852472202 for thread 1 is in sequence #33611

Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.

SQL> alter database open resetlogs;
Database altered.

I'm glad that this problem occurred now in test environment because those two hours spent on searching for answers could be critical in recovery of a production database.

You never know :)

REFERENCES
Metalink - [ID 965122.1]

Recover From Incrementally Updated Backup (test case)

2010-11-04T21:21:00.002+01:00

In this test I will play with incrementally updated backup. Using this feature we can recover datafile image copies applying incremental backups to them.

NOTE!
You should use 'flash recovery area' as primary location for your backups whenever you can. To complicate this test case a little I won’t use 'flash recovery area' for my backups.

Oracle 10.2.0.4.0
Linux OEL5

For taking backup I will use this RMAN script:

connect target /
run
{
sql 'alter system checkpoint';
allocate channel c1 device type disk format '/home/ora10g/backup/%U.dbf';
backup as compressed backupset
check logical incremental level 1
for recover of copy with tag DAILY_FULL database
filesperset 1;

recover copy of database with tag DAILY_FULL;
release channel c1;

allocate channel c1 device type disk;
backup (archivelog all format '/home/ora10g/backup/ARCH_%d_%T_%u_s%s_p%p' delete input TAG "archivelogs");
backup current controlfile format '/home/ora10g/backup/controlf_%d_%u_%s_%T';
release channel c1;
}

(I've borrowed first part of the script from great DBA Husnu Sensoy - Thanks!)

After first execution of this script RMAN will create initial image copy of the database, backup archived redo logs and current controlfile to directory ‘/home/ora10g/backup’.

Now I will create small table just for later checks and run script above again.

$ sqlplus admin

SQL> create table test123 as select * from dba_objects;
Table created.

SQL> alter system switch logfile;
System altered.

Next time RMAN will find image copies of data files and make just incremental backups. Additional step we will be updating image copies of datafiles applying the incremental backups to them.

RMAN script in usual circumstances works perfectly but this time I will interfere and kill RMAN process during recovery using ‘kill -9 [rman pid]’ command to simulate disaster.

To simulate disk crash I will drop original database using rm command.

So to summarize:
- I’ve lost my original database due to the 'disk crash'
- I have:
Image copies of datafiles partially recovered
Backups of archived redo logs
Multiplexed redo logs
Backup of controlfile

Can I fully recover my original database using this files? I can try... :)

I have already created empty directories where I will put database files, controlfiles, etc.

Restore controlfile and mount database:

RMAN> run
2> {
3> allocate channel c1 device type disk;
4> restore controlfile from '/home/ora10g/backup/controlf_RASAPP_khls5prr_3729_20101104';
5> }

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=157 devtype=DISK

Starting restore at 04-NOV-10

channel c1: restoring control file
channel c1: restore complete, elapsed time: 00:00:02
output filename=/u01/oradata/rasapp/control01.ctl
output filename=/u01/oradata/rasapp/control02.ctl
output filename=/u02/oradata/rasapp/control03.ctl
Finished restore at 04-NOV-10
released channel: c1

RMAN> alter database mount;

database mounted

Catalog backup pieces from backup directory ‘/home/ora10g/backup’:

RMAN> catalog start with '/home/ora10g/backup';
...
...

-- Check copy of database:

RMAN> list copy of database;
...
...

Now I will try to switch database to the latest image copy.

RMAN> switch database to copy;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of switch to copy command at 11/04/2010 15:30:15
RMAN-06571: datafile 9 does not have recoverable copy

As I’ve killed RMAN process during recovery operation my image copy of database is not valid.

How to overcome this error...

I will run ‘RECOVER COPY OF DATABASE WITH TAG ‘DAILY_FULL’’ to apply incremental backups to datafile copies with the same tag.

RMAN> recover copy of database with tag 'DAILY_FULL';

Starting recover at 04-NOV-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
no copy of datafile 9 found to recover
no copy of datafile 12 found to recover
no copy of datafile 13 found to recover
no copy of datafile 17 found to recover
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile copies to recover
recovering datafile copy fno=00018 name=/home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-UNDOTBS2_FNO-18_k4ls5pee.dbf
channel ORA_DISK_1: reading from backup piece /home/ora10g/backup/krls5qrn_1_1.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/ora10g/backup/krls5qrn_1_1.dbf tag=TAG20101104T145544
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile copies to recover
recovering datafile copy fno=00008 name=/home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-SAP_MERGE_FNO-8_k3ls5p99.dbf
channel ORA_DISK_1: reading from backup piece /home/ora10g/backup/kqls5qrm_1_1.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/ora10g/backup/kqls5qrm_1_1.dbf tag=TAG20101104T145544
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile copies to recover
recovering datafile copy fno=00006 name=/home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-BILL_DATA_FNO-6_k8ls5pnh.dbf
channel ORA_DISK_1: reading from backup piece /home/ora10g/backup/kvls5qrs_1_1.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/ora10g/backup/kvls5qrs_1_1.dbf tag=TAG20101104T145544
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
...
...
Finished recover at 04-NOV-10

Notice this part of the log:
no copy of datafile 9 found to recover
no copy of datafile 12 found to recover
no copy of datafile 13 found to recover
no copy of datafile 17 found to recover

From alertlog:
...
Thu Nov  4 15:26:51 2010
datafilecopy header validation failure for file /home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-LNP_FNO-9_k1ls5orq.dbf
datafilecopy header validation failure for file /home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-LNP_FNO-9_k1ls5orq.dbf
datafilecopy header validation failure for file /home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-PSUB_DATA_FNO-12_jvls5nuo.dbf
datafilecopy header validation failure for file /home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-PSUB_DATA_FNO-12_jvls5nuo.dbf
datafilecopy header validation failure for file /home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-DSLAM_STATS_FNO-13_juls5nco.dbf
datafilecopy header validation failure for file /home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-DSLAM_STATS_FNO-13_juls5nco.dbf
datafilecopy header validation failure for file /home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-TS_DATA_FNO-17_k0ls5od9.dbf
datafilecopy header validation failure for file /home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-TS_DATA_FNO-17_k0ls5od9.dbf
Thu Nov  4 15:27:06 2010
datafilecopy header validation failure for file /home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-LNP_FNO-9_k1ls5orq.dbf
datafilecopy header validation failure for file /home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-PSUB_DATA_FNO-12_jvls5nuo.dbf
datafilecopy header validation failure for file /home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-DSLAM_STATS_FNO-13_juls5nco.dbf
datafilecopy header validation failure for file /home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-TS_DATA_FNO-17_k0ls5od9.dbf
...
...

ORA-19563:
%s header validation failed for file string

Cause: When opening the file to be placed in a copy or backup set, to be inspected, or used as the target for an incremental restore, its header was not recognized as a valid file header for a file of the indicated type (data file, archived log, or control file) belonging to the current database.

Action: Ensure that the correct files are being specified for the copy or backup operation.

When I run ‘switch database to copy’ command it fails again with same error as before:

RMAN> switch database to copy;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of switch to copy command at 11/04/2010 18:29:03
RMAN-06571: datafile 9 does not have recoverable copy

To overcome this error I will uncatalog noted datafiles:

RMAN> change copy of datafile 9 uncatalog;

using target database control file instead of recovery catalog
uncataloged datafile copy
datafile copy filename=/home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-LNP_FNO-9_k1ls5orq.dbf recid=95 stamp=734192788
Uncataloged 1 objects


RMAN> change copy of datafile 12 uncatalog;

uncataloged datafile copy
datafile copy filename=/home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-PSUB_DATA_FNO-12_jvls5nuo.dbf recid=93 stamp=734192037
Uncataloged 1 objects


RMAN> change copy of datafile 13 uncatalog;

uncataloged datafile copy
datafile copy filename=/home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-DSLAM_STATS_FNO-13_juls5nco.dbf recid=92 stamp=734191568
Uncataloged 1 objects


RMAN> change copy of datafile 17 uncatalog;

uncataloged datafile copy
datafile copy filename=/home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-TS_DATA_FNO-17_k0ls5od9.dbf recid=94 stamp=734192505
Uncataloged 1 objects

Catalog them again:

RMAN> catalog start with '/home/ora10g/backup';

searching for all files that match the pattern /home/ora10g/backup

List of Files Unknown to the Database
=====================================
File Name: /home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-PSUB_DATA_FNO-12_jvls5nuo.dbf
File Name: /home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-LNP_FNO-9_k1ls5orq.dbf
File Name: /home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-DSLAM_STATS_FNO-13_juls5nco.dbf
File Name: /home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-TS_DATA_FNO-17_k0ls5od9.dbf

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-PSUB_DATA_FNO-12_jvls5nuo.dbf
File Name: /home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-LNP_FNO-9_k1ls5orq.dbf
File Name: /home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-DSLAM_STATS_FNO-13_juls5nco.dbf
File Name: /home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-TS_DATA_FNO-17_k0ls5od9.dbf

Let’s try switch database to copy:

RMAN> switch database to copy;

datafile 1 switched to datafile copy "/home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-SYSTEM_FNO-1_k6ls5plb.dbf"
datafile 2 switched to datafile copy "/home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-SAPBI_DATA_FNO-2_k7ls5pmo.dbf"
datafile 3 switched to datafile copy "/home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-SYSAUX_FNO-3_k5ls5piv.dbf"
datafile 4 switched to datafile copy "/home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-APP_DATA_FNO-4_k2ls5p4o.dbf"
datafile 5 switched to datafile copy "/home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-USERS_FNO-5_kbls5ppj.dbf"
datafile 6 switched to datafile copy "/home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-BILL_DATA_FNO-6_k8ls5pnh.dbf"
datafile 7 switched to datafile copy "/home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-OT_DATA_FNO-7_kcls5pq2.dbf"
datafile 8 switched to datafile copy "/home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-SAP_MERGE_FNO-8_k3ls5p99.dbf"
datafile 9 switched to datafile copy "/home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-LNP_FNO-9_k1ls5orq.dbf"
datafile 10 switched to datafile copy "/home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-PHONEBOOK_FNO-10_kals5pp4.dbf"
datafile 11 switched to datafile copy "/home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-IVR_DATA_FNO-11_kdls5pqh.dbf"
datafile 12 switched to datafile copy "/home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-PSUB_DATA_FNO-12_jvls5nuo.dbf"
datafile 13 switched to datafile copy "/home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-DSLAM_STATS_FNO-13_juls5nco.dbf"
datafile 14 switched to datafile copy "/home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-SIPDIR_DATA_FNO-14_k9ls5pob.dbf"
datafile 16 switched to datafile copy "/home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-INV_RN_INTERFACE_FNO-16_kels5pr0.dbf"
datafile 17 switched to datafile copy "/home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-TS_DATA_FNO-17_k0ls5od9.dbf"
datafile 18 switched to datafile copy "/home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-UNDOTBS2_FNO-18_k4ls5pee.dbf"

It worked...

Recover database now:

RMAN> recover database;

Starting recover at 04-NOV-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK

starting media recovery

channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=10
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=11
channel ORA_DISK_1: reading from backup piece /home/ora10g/backup/ARCH_RASAPP_20101104_kjls5q8e_s3731_p1
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/ora10g/backup/ARCH_RASAPP_20101104_kjls5q8e_s3731_p1 tag=ARCHIVELOGS
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
archive log filename=/u01/oradata/rasapp/archlogs/1_10_734173548.dbf thread=1 sequence=10
archive log filename=/u01/oradata/rasapp/archlogs/1_11_734173548.dbf thread=1 sequence=11
unable to find archive log
archive log thread=1 sequence=12
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/04/2010 18:31:26
RMAN-06054: media recovery requesting unknown log: thread 1 seq 12 lowscn 48367782959

I’m missing some redo entries - this is because I forgot to copy multiplexed redo log files to the correct location.

Copy multiplexed redo logs to specified locations:

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/oradata/rasapp/redo01a.rdo
/u02/oradata/rasapp/redo01b.rdo
/u01/oradata/rasapp/redo02a.rdo
/u02/oradata/rasapp/redo02b.rdo
/u01/oradata/rasapp/redo03a.rdo
/u02/oradata/rasapp/redo03b.rdo

6 rows selected.


$ cp redo01b.rdo /u02/oradata/rasapp/
...
...

Everything should be OK now during recovery:

$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Thu Nov 4 18:50:25 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: RASAPP (DBID=1650089695, not open)

RMAN> recover database;

Starting recover at 04-NOV-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK

starting media recovery

archive log thread 1 sequence 12 is already on disk as file /u01/oradata/rasapp/redo03b.rdo
archive log filename=/u02/oradata/rasapp/redo03b.rdo thread=1 sequence=12
media recovery complete, elapsed time: 00:00:06
Finished recover at 04-NOV-10

RMAN> alter database open resetlogs;

database opened

Let’s check data:

SQL> col file_name for a100
SQL> set lines 200
SQL> set pages 999
SQL> select file_name from dba_data_files;

FILE_NAME
----------------------------------------------------------------------------------------------------
/home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-SYSTEM_FNO-1_k6ls5plb.dbf
/home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-SAPBI_DATA_FNO-2_k7ls5pmo.dbf
/home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-SYSAUX_FNO-3_k5ls5piv.dbf
/home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-OT_DATA_FNO-7_kcls5pq2.dbf
/home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-APP_DATA_FNO-4_k2ls5p4o.dbf
/home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-USERS_FNO-5_kbls5ppj.dbf
/home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-BILL_DATA_FNO-6_k8ls5pnh.dbf
/home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-SAP_MERGE_FNO-8_k3ls5p99.dbf
/home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-LNP_FNO-9_k1ls5orq.dbf
/home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-PHONEBOOK_FNO-10_kals5pp4.dbf
/home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-IVR_DATA_FNO-11_kdls5pqh.dbf
/home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-PSUB_DATA_FNO-12_jvls5nuo.dbf
/home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-DSLAM_STATS_FNO-13_juls5nco.dbf
/home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-SIPDIR_DATA_FNO-14_k9ls5pob.dbf
/home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-INV_RN_INTERFACE_FNO-16_kels5pr0.dbf
/home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-TS_DATA_FNO-17_k0ls5od9.dbf
/home/ora10g/backup/data_D-RASAPP_I-1650089695_TS-UNDOTBS2_FNO-18_k4ls5pee.dbf

17 rows selected.

SQL> select count(*) from dba_objects where status<>'VALID';

  COUNT(*)
----------
         0

SQL> select count(*) from admin.test123;

  COUNT(*)
----------
     50118

In your own test environment, you can try out many different recovery scenarios without doing any harm. Performing test cases is very important for DBA’s because you must be well prepared when production database encounters disaster ;)

Finally, I’m Oracle 10g Certified Professional now

2010-10-19T15:14:00.000+02:00

I’ve been studying last couple of months for the exam “1Z0-043 Oracle Database 10g: Administration II” and today I finally passed that exam. So I’m officially OCP 10g now :)

As I was mostly learning in my spare time whole that process lasted longer then I’ve expected. Material was familiar to me before I began studying for the exam but still I’ve become more proficient with resource management, using scheduler, handling block corruption, securing listener, etc.

For learning material I used Official Oracle Documentation, several different books, blog articles and OTN forum posts. But the most important was testing environment where I installed 10g database and played with it.

I don’t know what will be my next certification but I’m sure that I will find something interesting eventually :)

Using Flashback Database to perform PITR

2010-08-24T11:21:00.004+02:00

Flashback database is great alternative for incomplete recovery because it is much less time consuming in some cases. You can find in documentation nice explanations how flashback database works so I won’t concentrate much on theory.

In this blog post I will perform simple demo case of point in time recovery using flashback database feature.

Database must be in ARCHIVELOG mode and FLASHBACK should be enabled.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Prod
PL/SQL Release 10.1.0.4.0 - Production
CORE    10.1.0.4.0      Production
TNS for Linux: Version 10.1.0.4.0 - Production
NLSRTL Version 10.1.0.4.0 - Production





SQL> select log_mode,flashback_on from v$database;

LOG_MODE     FLASHBACK_ON
------------ ------------------
ARCHIVELOG   YES

For testing purposes I will use SCOTT schema where I will create three tables in different time periods with my “sensitive data”.

Created first table at 21:35.

SQL> conn scott/tiger
Connected.

SQL> select to_char(sysdate,'dd.mm.yyyy hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'DD
--------
23.08.2010 21:35:19

SQL> create table t1 (a number);

Table created.

SQL> insert into t1 values (1);

1 row created.

SQL> commit;

Commit complete.

Second table at 21:45.

SQL> select to_char(sysdate,'dd.mm.yyyy hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'DD
--------
23.08.2010 21:45:05

SQL> create table t2 (b number);

Table created.

SQL> insert into t2 values (2);

1 row created.

SQL> commit;

Commit complete.

Third table at 22:05.

SQL> select to_char(sysdate,'dd.mm.yyyy hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'DD
--------
23.08.2010 22:05:48

SQL> create table t3 (c number);

Table created.

SQL> insert into t3 values (3);

1 row created.

SQL> commit;

Commit complete.

Lets assume that SCOTT is important production schema and DBA drops that schema by mistake. It is very important to recover lost schema as quickly as possible. DBA isn’t sure about correct time when he/she dropped schema.

Drop SCOTT schema:

$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 23 22:13:13 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> drop user scott cascade;

User dropped.

RMAN uses flashback logs to undo changes to point before target time and then applies redo logs that are needed to make database consistent.

I will shutdown database and start in mount mode. As you can see I will use “shutdown abort” opinion - simply to minimize downtime.

SQL> shutdown abort;
ORACLE instance shut down.


SQL> startup mount;
ORACLE instance started.

Total System Global Area  297795584 bytes
Fixed Size                  1219064 bytes
Variable Size              83887624 bytes
Database Buffers          209715200 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL>

Flashback database to 21:40 because DBA said that he/she dropped schema somewhere around that time.
Before opening database with resetlogs opinion I want to check for my sensitive data. So I will temporary open flashbacked database in read only mode and do some checks.

SQL> flashback database to timestamp
 2  to_timestamp('23.08.2010 21:40:00','dd.mm.yyyy hh24:mi:ss');

Flashback complete.


SQL> alter database open read only;

Database altered.

My SCOTT schema is here but some data is missing.

SQL> select * from scott.t1;

        A
----------
        1

SQL> select * from scott.t2;
select * from scott.t2
                   *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from scott.t3;
select * from scott.t3
                   *
ERROR at line 1:
ORA-00942: table or view does not exist

I will recover database to 21:50 and do some checks in read only mode:

SQL> shutdown abort;
ORACLE instance shut down.
SQL>
SQL>
SQL> startup mount;
ORACLE instance started.

Total System Global Area  297795584 bytes
Fixed Size                  1219064 bytes
Variable Size              83887624 bytes
Database Buffers          209715200 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL>


SQL> recover database until time '2010-08-23:21:50:00';

ORA-00279: change 609683 generated at 08/23/2010 21:40:13 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_
recovery_area/ORA10G/archivelog/2010_08_23/o1_mf_1_10_%u_.
arc
ORA-00280: change 609683 for thread 1 is in sequence #10


Specify log: {=suggested | filename | AUTO | CANCEL}

Log applied.
Media recovery complete.

This is better situation but still some data is missing.

SQL> alter database open read only;

Database altered.

SQL> select * from scott.t1;

        A
----------
        1

SQL> select * from scott.t2;

        B
----------
        2

SQL> select * from scott.t3;
select * from scott.t3
                   *
ERROR at line 1:
ORA-00942: table or view does not exist

This time I will recover to 22:07.

SQL> shutdown abort;
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  297795584 bytes
Fixed Size                  1219064 bytes
Variable Size              83887624 bytes
Database Buffers          209715200 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL>
SQL>
SQL> recover database until time '2010-08-23:22:07:00';
Media recovery complete.

Now everything is here.

SQL> alter database open read only;

Database altered.

SQL> select * from scott.t1;

        A
----------
        1

SQL> select * from scott.t2;

        B
----------
        2

SQL> select * from scott.t3;

        C
----------
        3

As I recovered all my sensitive data it is safe to open database with RESETLOGS opinion.

SQL> shutdown abort;
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  608174080 bytes
Fixed Size                  1268896 bytes
Variable Size             209716064 bytes
Database Buffers          390070272 bytes
Redo Buffers                7118848 bytes
Database mounted.

SQL> alter database open resetlogs;
Database altered.

Point in time recovery with flashback database lasted only several minutes with minimal loss of data.

REFERENCE
http://www.comp.dit.ie/btierney/oracle11gdoc/backup.111/b28273/rcmsynta023.htm

Recovering From A Lost Controlfile

2010-08-22T00:25:00.001+02:00

I decided to play a little with control file recovery. It is always wise to have controlfiles multiplexed on separate disks but for testing purposes my controlfiles will be placed in the same directory.

What will happen when I delete one of my three controlfiles?

From documentation:

Damage to any control file, whether it is multiplexed or not, halts database operation when the database attempts to read or write to the damaged control file (which happens frequently, for example at every checkpoint and log switch).

Little demonstration...

Environment:
Linux 32 bit
Oracle 10gR2

Delete one controlfile:

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora10g/control01.ctl
/u01/app/oracle/oradata/ora10g/control02.ctl
/u01/app/oracle/oradata/ora10g/control03.ctl


SQL> !rm /u01/app/oracle/oradata/ora10g/control03.ctl

First I will try shutdown immediate:
(Operation failed but database is still up and active.)

SQL> shutdown immediate;
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/ora10g/control03.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

-- from alert.log:

Sat Aug 21 23:52:00 2010
Errors in file /u01/app/oracle/admin/ora10g/udump/ora10g_ora_16200.trc:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/ora10g/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
Sat Aug 21 23:52:00 2010
Errors in file /u01/app/oracle/admin/ora10g/udump/ora10g_ora_16200.trc:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/ora10g/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
Sat Aug 21 23:52:01 2010
Errors in file /u01/app/oracle/admin/ora10g/udump/ora10g_ora_16200.trc:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/ora10g/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

What will happen if I copy good controlfile to location of missing one and then try to alter tablespace.

SQL> !cp /u01/app/oracle/oradata/ora10g/control01.ctl -
> /u01/app/oracle/oradata/ora10g/control03.ctl

SQL> alter system checkpoint;

System altered.



SQL> alter tablespace users read only;
alter tablespace users read only
     *
ERROR at line 1:
ORA-03113: end-of-file on communication channel



-- from alert.log:
Sat Aug 21 18:54:29 2010
********************* ATTENTION: ********************
The controlfile header block returned by the OS
has a sequence number that is too old.
The controlfile might be corrupted.
PLEASE DO NOT ATTEMPT TO START UP THE INSTANCE
without following the steps below.
RE-STARTING THE INSTANCE CAN CAUSE SERIOUS DAMAGE
TO THE DATABASE, if the controlfile is truly corrupted.
In order to re-start the instance safely,
please do the following:
(1) Save all copies of the controlfile for later
    analysis and contact your OS vendor and Oracle support.
(2) Mount the instance and issue:
    ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
(3) Unmount the instance.
(4) Use the script in the trace file to
    RE-CREATE THE CONTROLFILE and open the database.
*****************************************************
Instance terminated by DBW0, pid = 7444

Instance was immediately terminated.

What if I execute startup now:
(Operation failed with error below.)

SQL> startup
ORACLE instance started.

Total System Global Area  297795584 bytes
Fixed Size                  1219064 bytes
Variable Size              71304712 bytes
Database Buffers          222298112 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-01110: data file 4: '/u01/app/oracle/oradata/ora10g/users01.dbf'
ORA-00372: file 4 cannot be modified at this time
ORA-01110: data file 4: '/u01/app/oracle/oradata/ora10g/users01.dbf'

So what is the proper way to handle this controlfile recovery.

Delete one controlfile to simulate disk or controller failure.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora10g/control01.ctl
/u01/app/oracle/oradata/ora10g/control02.ctl
/u01/app/oracle/oradata/ora10g/control03.ctl

SQL>

SQL> !rm /u01/app/oracle/oradata/ora10g/control03.ctl

Shutdown abort database and copy good copy of controlfile to the location of the lost one. You can also modify parameter file (CONTROL_FILES parameter) if you have to change location of lost controlfile.

SQL> shutdown abort;
ORACLE instance shut down.

SQL> !cp /u01/app/oracle/oradata/ora10g/control01.ctl -
> /u01/app/oracle/oradata/ora10g/control03.ctl

SQL> startup
ORACLE instance started.

Total System Global Area  297795584 bytes
Fixed Size                  1219064 bytes
Variable Size              71304712 bytes
Database Buffers          222298112 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.

SQL>

Database is up and fully functional.

REFERENCE
http://download.oracle.com/docs/cd/B19306_01/backup.102/b14192/intro008.htm#sthref96

Automatic Workload Repository (AWR) space usage

2010-07-21T11:27:00.002+02:00

AWR is very powerful diagnostic tool which is used for problem detection and tuning. By default, snapshots of database performance statistics and metrics are taken every hour, retained for 7 days and stored in SYSAUX tablespace.

If possible, I prefer to change default settings to increase retention period and reduce snapshot interval. Before changing default values it is important to think about space consumption by the Automatic Workload Repository.

Nice tool for estimating amount of space required for the SYSAUX tablespace is - utlsyxsz.sql stored in “$ORACLE_HOME/rdbms/admin” directory.

In this example I will estimate size of AWR data with 15 min intervals and 60 days of retention.

This script estimates the space required for the SYSAUX tablespace.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Specify the Report File Name
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is utlsyxsz.txt. To use this name,
press to continue, otherwise enter an alternative.

Enter value for report_name: /tmp/estimate_awr.out

Using the report name /tmp/estimate_awr.out
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SYSAUX Size Estimation Report
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Estimated at
10:03:56 on Srp 21, 2010 ( Srijeda ) in Timezone +02:00

DB_NAME HOST_PLATFORM INST STARTUP_TIME PAR
----------- ---------------------------------------- ----- ----------------- ---
* TESTDB l01test1 - Linux IA (32-bit) 1 22:08:09 (10/13) NO

~~~~~~~~~~~~~~~~~~~~
Current SYSAUX usage
~~~~~~~~~~~~~~~~~~~~
| Total SYSAUX size: 653.9 MB
|
| Total size of SM/AWR 334.8 MB ( 51.2% of SYSAUX )
| Total size of SM/OPTSTAT 104.2 MB ( 15.9% of SYSAUX )
| Total size of EM 48.8 MB ( 7.5% of SYSAUX )
| Total size of XDB 48.2 MB ( 7.4% of SYSAUX )
| Total size of SM/ADVISOR 45.2 MB ( 6.9% of SYSAUX )
| Total size of SDO 33.1 MB ( 5.1% of SYSAUX )
| Total size of WM 7.1 MB ( 1.1% of SYSAUX )
| Total size of LOGMNR 5.9 MB ( 0.9% of SYSAUX )
| Total size of SM/OTHER 4.9 MB ( 0.8% of SYSAUX )
| Total size of TEXT 4.6 MB ( 0.7% of SYSAUX )
| Total size of EXPRESSION_FILTER 3.6 MB ( 0.6% of SYSAUX )
| Total size of EM_MONITORING_USER 1.6 MB ( 0.2% of SYSAUX )
| Total size of LOGSTDBY 0.9 MB ( 0.1% of SYSAUX )
| Total size of XSOQHIST 0.8 MB ( 0.1% of SYSAUX )
| Total size of AO 0.8 MB ( 0.1% of SYSAUX )
| Total size of JOB_SCHEDULER 0.8 MB ( 0.1% of SYSAUX )
| Total size of ORDIM 0.5 MB ( 0.1% of SYSAUX )
| Total size of STREAMS 0.5 MB ( 0.1% of SYSAUX )
| Total size of TSM 0.3 MB ( 0.0% of SYSAUX )
| Total size of ODM 0.3 MB ( 0.0% of SYSAUX )
| Total size of Others 7.2 MB ( 1.1% of SYSAUX )
|

~~~~~~~~~~~~~~~~~~~~
AWR Space Estimation
~~~~~~~~~~~~~~~~~~~~

| To estimate the size of the Automatic Workload Repository (AWR)
| in SYSAUX, we need the following values:
|
| - Interval Setting (minutes)
| - Retention Setting (days)
| - Number of Instances
| - Average Number of Active Sessions
| - Number of Datafiles

|
| For 'Interval Setting',
| Press [return] to use the current value: 60.0 minutes
| otherwise enter an alternative
|
Enter value for interval: 15

** Value for 'Interval Setting': 15

|
| For 'Retention Setting',
| Press [return] to use the current value: 30.0 days
| otherwise enter an alternative
|
Enter value for retention: 60

** Value for 'Retention Setting': 60

|
| For 'Number of Instances',
| Press [return] to use the current value: 1.00
| otherwise enter an alternative
|
Enter value for num_instances: 1

** Value for 'Number of Instances': 1

|
| For 'Average Number of Active Sessions',
| Press [return] to use the current value: 0.43
| otherwise enter an alternative
|
Enter value for active_sessions:

** Value for 'Average Number of Active Sessions': .43

| ***************************************************
| Estimated size of AWR: 2,816.7 MB
|
| The AWR estimate was computed using
| the following values:
|
| Interval - 15 minutes
| Retention - 60.00 days
| Num Instances - 1
| Active Sessions - 0.43
| Datafiles - 17
| ***************************************************

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Optimizer Stat History Space Estimation
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

| To estimate the size of the Optimizer Statistics History
| we need the following values:
|
| - Number of Tables in the Database
| - Number of Partitions in the Database
| - Statistics Retention Period (days)
| - DML Activity in the Database (level)

|
| For 'Number of Tables',
| Press [return] to use the current value: 463.0
| otherwise enter an alternative [a positive integer]
|
Enter value for number_of_tables:

** Value for 'Number of Tables': 463

|
| For 'Number of Partitions',
| Press [return] to use the current value: 0.00
| otherwise enter an alternative [a positive integer]
|
Enter value for number_of_partitions:

** Value for 'Number of Partitions': 0

|
| For 'Statistics Retention',
| Press [return] to use the current value: 31.0 days
| otherwise enter an alternative [a positive integer]
|
Enter value for stats_retention:

** Value for 'Statistics Retention': 31

|
| For 'DML Activity',
| Press [return] to use the current value: 2 [medium]
| otherwise enter an alternative [1=low, 2=medium, 3=high]
|
Enter value for dml_activity:

** Value for 'DML Activity': 2

| ***************************************************
| Estimated size of Stats history 33.4 MB
|
| The space for Optimizer Statistics history was
| estimated using the following values:
|
| Tables - 463
| Indexes - 496
| Columns - 5,734
| Partitions - 0
| Indexes on Partitions - 0
| Columns in Partitions - 0
| Stats Retention in Days - 31
| Level of DML Activity - Medium
| ***************************************************

~~~~~~~~~~~~~~~~~~~~~~
Estimated SYSAUX usage
~~~~~~~~~~~~~~~~~~~~~~

| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Estimated size of AWR: 2,816.7 MB
|
| The AWR estimate was computed using
| the following values:
|
| Interval - 15 minutes
| Retention - 60.00 days
| Num Instances - 1
| Active Sessions - 0.43
| Datafiles - 17
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Estimated size of Stats history 33.4 MB
|
| The space for Optimizer Statistics history was
| estimated using the following values:
|
| Tables - 463
| Indexes - 496
| Columns - 5,734
| Partitions - 0
| Indexes on Partitions - 0
| Columns in Partitions - 0
| Stats Retention in Days - 31
| Level of DML Activity - Medium
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| For all the other components, the estimate
| is equal to the current space usage of
| the component.
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
|
| ***************************************************
| Summary of SYSAUX Space Estimation
| ***************************************************

| Est size of EM 48.8 MB
| Est size of XDB 48.2 MB
| Est size of SM/ADVISOR 45.2 MB
| Est size of SDO 33.1 MB
| Est size of WM 7.1 MB
| Est size of LOGMNR 5.9 MB
| Est size of SM/OTHER 4.9 MB
| Est size of TEXT 4.6 MB
| Est size of EXPRESSION_FILTER 3.6 MB
| Est size of EM_MONITORING_USER 1.6 MB
| Est size of LOGSTDBY 0.9 MB
| Est size of XSOQHIST 0.8 MB
| Est size of AO 0.8 MB
| Est size of JOB_SCHEDULER 0.8 MB
| Est size of ORDIM 0.5 MB
| Est size of STREAMS 0.5 MB
| Est size of TSM 0.3 MB
| Est size of ODM 0.3 MB
| Est size of Others 7.2 MB

| Est size of SM/AWR 2,816.7 MB
| Est size of SM/OPTSTAT 33.4 MB
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Total Estimated SYSAUX size: 3,065.0 MB
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| ***************************************************

End of Report

Another nice script to mention - awrinfo.sql stored in “$ORACLE_HOME/rdmbs/admin” directory. This script displays general AWR information such as current space usage, data distribution, etc. I like to check space usage with awrinfo.sql script when SYSAUX starts to increase rapidly.

DBA_TS_QUOTAS (ORA-00959,ORA-01536)

2010-07-20T13:38:00.002+02:00

How much space user can consume in particular tablespace is showed in DBA_TS_QUOTAS dictionary view. Quota can be assigned to any user on any tablespace. More about quotas can be found in Oracle documentation...

I will concentrate on small demo case in this blog post.

Oracle 10.2.0.4
Linux x86

Create tablespace TEST_TBS and TESTUSR.

SQL> create tablespace test_tbs;
Tablespace created.

SQL> create user testusr identified by test;
User created.

SQL> grant create session to testusr;
Grant succeeded.

SQL> grant create table to testusr;
Grant succeeded.

By default, user has no quota on any tablespace.

-- system user
SQL> set lines 200
SQL> col tablespace_name for a30
SQL> col username for a20
SQL> col dropped for a15
SQL>
SQL> select * from dba_ts_quotas
 2  where username='TESTUSR';

no rows selected

-- testusr user
SQL> connect testusr/test
Connected.

SQL> create table testtab(a number) tablespace test_tbs;
create table testtab(a number) tablespace test_tbs
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'TEST_TBS'

Now I will grant RESOURCE role to TESTUSR and check DBA_TS_QUTAS view.

SQL> grant resource to testusr;
Grant succeeded.

SQL> select * from dba_ts_quotas
 2  where username='TESTUSR';

no rows selected

Still no quota entries for TESTUSR (in dba_ts_quotas), but user is able to create table in TEST_TBS tablespace.

SQL> create table testtab1(a number) tablespace test_tbs;

Table created.

When I granted RESOURCE role to TESTUSR, “UNLIMITED TABLESPACE” privilege was granted automatically.
More about “UNLIMITED TABLESPACE” privilege:
http://msutic.blogspot.com/2008/07/oracle-roles-unlimited-tablespace.html

When I revoke RESOURCE role, “CREATE TABLE” statement fails.

SQL> revoke resource from testusr;
Revoke succeeded.

SQL> create table testtab2(a number) tablespace test_tbs;
create table testtab2(a number) tablespace test_tbs
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'TEST_TBS'

Add some quota for user.

SQL> alter user testusr quota 50M on test_tbs;
User altered.

SQL> select tablespace_name,username,max_bytes,dropped
  2  from dba_ts_quotas
  3  where username='TESTUSR';

TABLESPACE_NAME                USERNAME              MAX_BYTES DROPPED
------------------------------ -------------------- ---------- ---------------
TEST_TBS                       TESTUSR                52428800 NO


SQL> create table testtab2(a number) tablespace test_tbs;
Table created.

Now I managed to create table in TEST_TBS tablespace.

I will drop TEST_TBS tablespace and check DBA_TS_QUOTAS.

SQL> drop tablespace test_tbs including contents and datafiles;

Tablespace dropped.

SQL> select tablespace_name,username,max_bytes,dropped
  2  from dba_ts_quotas
  3  where username='TESTUSR';

TABLESPACE_NAME                USERNAME              MAX_BYTES DROPPED
------------------------------ -------------------- ---------- ---------------
TEST_TBS                       TESTUSR                52428800 YES



SQL> alter user testusr quota 0 on test_tbs;
alter user testusr quota 0 on test_tbs
*
ERROR at line 1:
ORA-00959: tablespace 'TEST_TBS' does not exist

User quota entry is still there with DROPPED = YES (tablespace has been dropped). Quota cannot be removed from DBA_TS_QUOTES if tablespace does not exist.

I will create new tablespace with the same name as dropped one - “TEST_TBS”.

-- execute as SYSTEM
SQL> create tablespace test_tbs;
Tablespace created.

SQL> select tablespace_name,username,max_bytes,dropped
  2  from dba_ts_quotas
  3  where username='TESTUSR';

TABLESPACE_NAME                USERNAME              MAX_BYTES DROPPED
------------------------------ -------------------- ---------- ---------------
TEST_TBS                       TESTUSR                52428800 NO


-- execute as TESTUSR
SQL> create table testtab3(a number) tablespace test_tbs;
Table created.

Now column DROPPED = NO for TESTUSR and TESTUSR is able to create table in new tablespace. To prevent this you should remove quota entries before you drop tablespace.

Remove user quota entry.

SQL> alter user testusr quota 0 on test_tbs;

User altered.

SQL> select * from dba_ts_quotas
 2  where username='TESTUSR';

no rows selected

Cleanup!

SQL> drop tablespace test_tbs including contents and datafiles;

Tablespace dropped.

SQL> drop user testusr cascade;

User dropped.

Convert "10.2.0.4 SE+CPU" to "10.2.0.4 EE+CPU"

2010-07-14T14:05:00.001+02:00

In this test case I will convert "Oracle 10.2.0.4 SE + CPU" to "Oracle 10.2.0.4 EE + CPU".

I’ve installed "Oracle 10.2.0.4 SE+CPU" on my test machine and created database for test purpose. Database resides on Linux 32bit operating system.

My steps:

1. Install Oracle 10.2.0.1 EE software to different location.
During software installation I will keep my database running and won’t touch 10.2.0.4 SE installation.

2. Upgrade 10.2.0.1 EE software to 10.2.0.4 version and apply CPU.

3. Shutdown database and listener + take cold backup.

4. Copy parameter file (init.ora/spfile.ora) from SE/dbs to EE/dbs.

5. Copy tnsnames.ora, listener.ora and sqlnet.ora from SE/network/admin to EE/network/admin.

6. Change ORACLE_HOME environment variable to point database to new EE home.

7. If necessary change /etc/oratab entries to point new EE home.

8. Startup database and listener.

9. Run the catalog.sql and catproc.sql scripts.

10. Run utlrp.sql to recompile invalid objects.

11. Later you can remove SE installation using OUI (if everything went OK).

Small checks:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

5 rows selected.

SQL> select count(*) from dba_objects where status<>'VALID';

 COUNT(*)
----------
        0

1 row selected.


Check Alert.log -> OK.

Maybe some steps are unnecessary or incorrect so if you have any suggestion or correction please leave it in a comment and I will add correction.

Thanks!

My first Oracle Course

2010-07-10T08:38:00.001+02:00

After waiting for couple of months finally enough students signed up for the “Oracle Database 11g: Administration Workshop II Release 2” and we could start with the course.

Given that most of the topics were already familiar to me I didn’t expect to learn much. So I must confess that I was a bit sceptical about the course.

But the course exceeded all my expectations...

The biggest benefit of taking this in-class course was the opportunity to talk with our lecturer and classmates about various stuff in person. Most of my questions were out of the scope of the slides but we discussed about them anyway. Our lecturer was very accessible and clear in his explanations.

My classmates were great, lecturer was great and I’ve learned much so I think this is very well spent money :)

If you are considering about taking Oracle course I would recommend to choose in-class instead of online especially if the price is the same.

Now to catch up with blogs, forums, mails...

Regards ;)

Table missing in DBA_TABLES dictionary view

2010-05-19T14:38:00.003+02:00

Today I learned something new about DBA_TABLES and DBA_ALL_TABLES dictionary views.

During some PL/SQL programming I noticed that several tables exist in DBA_SEGMENTS and DBA_OBJECTS views but not in DBA_TABLES.

That confused me a little but I found answer quickly after posting question on OTN forums. User named "admin" helped me with that.

Example:

select * from v$version;
 
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 Production
TNS for Solaris: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production
 
5 rows selected.
 
 
select * from dba_segments where segment_name = 'TMP_WEB_DATA';
 
OWNER : TST
SEGMENT_NAME : TMP_WEB_DATA
PARTITION_NAME :
SEGMENT_TYPE : TABLE
TABLESPACE_NAME : TST_DATA
HEADER_FILE : 3
HEADER_BLOCK : 514179
BYTES : 65536
BLOCKS : 8
EXTENTS : 1
INITIAL_EXTENT : 65536
NEXT_EXTENT :
MIN_EXTENTS : 1
MAX_EXTENTS : 2147483645
PCT_INCREASE :
FREELISTS :
FREELIST_GROUPS :
RELATIVE_FNO : 3
BUFFER_POOL : DEFAULT
-----------------


select * from dba_objects where object_name = 'TMP_WEB_DATA';
 
OWNER : TST
OBJECT_NAME : TMP_WEB_DATA
SUBOBJECT_NAME :
OBJECT_ID : 42663
DATA_OBJECT_ID : 42663
OBJECT_TYPE : TABLE
CREATED : 23.05.2007 17:05:20
LAST_DDL_TIME : 19.12.2008 14:05:20
TIMESTAMP : 2007-05-23:17:05:20
STATUS : VALID
TEMPORARY : N
GENERATED : N
SECONDARY : N
-----------------
 

select * from dba_tables where table_name = 'TMP_WEB_DATA';
 
no rows selected
-----------------


select count(*) from TST.TMP_WEB_DATA;
 
COUNT(*)
----------
1

The answer lies in dictionary view DBA_ALL_TABLES.

select * from dba_all_tables where table_name = 'TMP_WEB_DATA';

OWNER                         : TST
TABLE_NAME                    : TMP_WEB_DATA
TABLESPACE_NAME               : TST_DATA
CLUSTER_NAME                  :
IOT_NAME                      :
PCT_FREE                      : 10
PCT_USED                      :
INI_TRANS                     : 1
MAX_TRANS                     : 255
INITIAL_EXTENT                : 65536
NEXT_EXTENT                   :
MIN_EXTENTS                   : 1
MAX_EXTENTS                   : 2147483645
PCT_INCREASE                  :
FREELISTS                     :
FREELIST_GROUPS               :
LOGGING                       : YES
BACKED_UP                     : N
NUM_ROWS                      :
BLOCKS                        :
EMPTY_BLOCKS                  :
AVG_SPACE                     :
CHAIN_CNT                     :
AVG_ROW_LEN                   :
AVG_SPACE_FREELIST_BLOCKS     :
NUM_FREELIST_BLOCKS           :
DEGREE                        :          1
INSTANCES                     :          1
CACHE                         :     N
TABLE_LOCK                    : ENABLED
SAMPLE_SIZE                   :
LAST_ANALYZED                 :
PARTITIONED                   : NO
IOT_TYPE                      :
OBJECT_ID_TYPE                : SYSTEM GENERATED
TABLE_TYPE_OWNER              : TST
TABLE_TYPE                    : PARTNER_WEB_DATA
TEMPORARY                     : N
SECONDARY                     : N
NESTED                        : NO
BUFFER_POOL                   : DEFAULT
ROW_MOVEMENT                  : DISABLED
GLOBAL_STATS                  : NO
USER_STATS                    : NO
DURATION                      :
SKIP_CORRUPT                  : DISABLED
MONITORING                    : NO
CLUSTER_OWNER                 :
DEPENDENCIES                  : DISABLED
COMPRESSION                   : DISABLED
-----------------

When I peeked in documentation for explanation:

- DBA_TABLES describes all relational tables in the database. Its columns are the same as those in ALL_TABLES. To gather statistics for this view, use the ANALYZE SQL statement.

- DBA_ALL_TABLES describes all object tables and relational tables in the database. Its columns are the same as those in ALL_ALL_TABLES.

DDL of TMP_WEB_DATA:

CREATE TABLE "TST"."TMP_WEB_DATA" OF "TST"."PARTNER_WEB_DATA"
OIDINDEX ( PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TST_DATA" )
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TST_DATA" ;

So TMP_WEB_DATA is object table and this is the reason why it was missing from DBA_TABLES view.

It's nice to know that :)

Missing or extra files in ControlFile

2010-05-11T15:03:00.001+02:00

At the end of partial restore of database I've noticed entries MISSING00008, MISSING00009, etc. Oracle created those entries because some datafiles were found in data dictionary but not in new controlfile.

I didn't know that Oracle will add "MISSINGnnnn" entry for read only and offline files also.

Small demo case...

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

Create read only tablespace with one table:

SQL> create tablespace DEMO_RO datafile '/oracle/oradata/test10/demo_ro.dbf' size 50M;

Tablespace created.

SQL> create table test_ro (id number, name varchar2(20)) tablespace DEMO_RO;

Table created.

SQL> insert into test_ro values (1,'Cartman');

1 row created.

SQL> commit;

Commit complete.

SQL> alter tablespace DEMO_RO read only;

Tablespace altered.

Create tablespace with one table and alter this tablespace with "offline normal" opinion:

SQL> create tablespace DEMO_OFF datafile '/oracle/oradata/test10/demo_off.dbf' size 50M;

Tablespace created.

SQL> create table test_off (id number, name varchar2(20)) tablespace DEMO_OFF;

Table created.

SQL> insert into test_off values (5,'Kenny');

1 row created.

SQL> commit;

Commit complete.

SQL> alter tablespace DEMO_OFF offline normal;

Tablespace altered.

Backup controlfile to trace and shutdown database:

SQL> alter database backup controlfile to  trace as  '/tmp/ctlfile.sql';

Database altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Check controlfile script:

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "TEST10" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/oracle/oradata/test10/redo01.log'  SIZE 50M,
  GROUP 2 '/oracle/oradata/test10/redo02.log'  SIZE 50M,
  GROUP 3 '/oracle/oradata/test10/redo03.log'  SIZE 50M
DATAFILE
  '/oracle/oradata/test10/system01.dbf',
  '/oracle/oradata/test10/undotbs01.dbf',
  '/oracle/oradata/test10/sysaux01.dbf',
  '/oracle/oradata/test10/users01.dbf'
CHARACTER SET UTF8
;

It is interesting to notice that newly created database files '/oracle/oradata/test10/demo_ro.dbf' and '/oracle/oradata/test10/demo_off.dbf' are missing.

I will delete existing controlfiles to force myself to create new ones.

$ rm control01.ctl control02.ctl control03.ctl

Create new controlfile based on script above and open database with resetlogs opinion:

$ sqlplus "/as sysdba"

SQL> @/tmp/ctlfile.sql
ORACLE instance started.

Total System Global Area  608174080 bytes
Fixed Size                  1268896 bytes
Variable Size             163578720 bytes
Database Buffers          436207616 bytes
Redo Buffers                7118848 bytes

Control file created.


SQL> alter database open resetlogs;

Database altered.

Excerpt from alert.log:

Dictionary check beginning
Tablespace 'TEMP' #3 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'DEMO_RO' #6 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'DEMO_OFF' #7 found in data dictionary,
but not in the controlfile. Adding to controlfile.
File #5 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00005' in the controlfile.
File #6 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00006' in the controlfile.
Dictionary check complete

During dictionary checking Oracle found that some files exist in data dictionary but not in controlfile.

To query database files:

SQL> col tablespace_name for a30
SQL> col file_name for a50
SQL> set lines 200
SQL> select tablespace_name, file_name from dba_data_files;

TABLESPACE_NAME                FILE_NAME
------------------------------ --------------------------------------------------
USERS                          /oracle/oradata/test10/users01.dbf
SYSAUX                         /oracle/oradata/test10/sysaux01.dbf
UNDOTBS1                       /oracle/oradata/test10/undotbs01.dbf
SYSTEM                         /oracle/oradata/test10/system01.dbf
DEMO_RO                        /oracle/product/10.2.0/dbs/MISSING00005
DEMO_OFF                       /oracle/product/10.2.0/dbs/MISSING00006

6 rows selected.

I will try to get data from test tables:

SQL> select * from test_ro;
select * from test_ro
              *
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01111: name for data file 5 is unknown - rename to correct file
ORA-01110: data file 5: '/oracle/product/10.2.0/dbs/MISSING00005'


SQL> select * from test_off;
select * from test_off
              *
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01111: name for data file 6 is unknown - rename to correct file
ORA-01110: data file 6: '/oracle/product/10.2.0/dbs/MISSING00006'

As expected - that operation failed. According to ORA-01111 error I have to rename datafile name to correct file and make it accessible.

Renaming:

SQL> alter database rename file 'MISSING00006'
  to '/oracle/oradata/test10/demo_off.dbf';
 
Database altered.


SQL> alter database rename file 'MISSING00005'
  to '/oracle/oradata/test10/demo_ro.dbf';
 
 
SQL> alter tablespace demo_ro online;

Tablespace altered.

SQL> alter tablespace demo_off online;

Tablespace altered.

Now to get data from test tables:

SQL> select * from test_ro;

        ID NAME
---------- --------------------
         1 Cartman

SQL> select * from test_off;

        ID NAME
---------- --------------------
         5 Kenny

I can make datafile accessible by renaming MISSINGnnnn entry only if datafile was offline normal or read only.

REFERENCES:
Managing Control Files

My first Oracle certificate

2010-02-18T14:34:00.003+01:00

I've passed the exam "1Z0-007 Introduction to Oracle9i SQL®" few hours ago with excellent score. About three months ago I've passed "1Z0-042 Oracle Database 10g: Administration I" also with excellent score. And this was enough for Oracle Database 10g Administrator Certified Associate "OCA" certificate.

It was great for me that I could take exams without attending a training course because training is quite expensive.

My next goal is to attend "Oracle Database 10g: Administration Workshop II Release 2" training course and taking "1Z0-043 Oracle Database 10g: Administration II" exam which will lead me to Oracle Database 10g Oracle Certified Professional "OCP" certificate.

While I was preparing for this exams I've noticed several areas of weakness which I could improve. Now after preparation period I can say that I'm slightly better DBA.

Uff... my RSS reader is full with so many great blog posts...

It's time to read some new interesting stuff from Oracle professionals :)

Quick query to check for the existence of a row in a table

2010-02-08T12:38:00.010+01:00

I've seen several times that when developer needs to check for the existence of a row in a table he counts how many times row occurs in a table.

Something like:

declare
l_cnt number;
begin
    select count(*) into l_cnt
    from test_table
    where row = something;
if l_cnt>0 then
    exists, do something
else
    not exists, do something
end if;
end;
/

This query is correct, but it is not very fast when test_table is big table with many rows.

Much better approach is to stop query when it hits first row. Counting how many times row occurs in a table is unnecessary.

Checking row existence is sometimes used as a part of triggers or procedures that execute very often. In that cases using quicker query check can be great opinion to improve performance.

There are many ways to rewrite your query to speed up things a little and I will show you just few of them. This is probably familiar to most developers and DBA's but I'll will add solutions to the post anyway.

OS: OEL5
DB: Oracle 10.2.0.4

Create simple table without indexes and populate that table with 2000000 rows.

drop table testtab;

create table testtab (id     number,
                             code number,
                             text  varchar2(50),
                             tdate date);

Table created.


insert /*+ append */ into testtab
select rownum, mod(rownum,1000), rpad('x',50,'x'), sysdate+rownum
from all_objects o1,
     all_objects o2
where
   rownum <= 2000000
/

2000000 rows created.

SQL> commit;

Commit complete.

In test cases I will search for an existence of row with code 999.

First solution:

SQL> set timing on
SQL> alter system flush buffer_cache;

System altered.

declare
l_cnt number;
begin
    select count(*) into l_cnt
    from testtab
    where code=999;
if l_cnt>0 then
    dbms_output.put_line('Exists!');
else
    dbms_output.put_line('NOT Exists!');
end if;
end;
/

Exists!

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.32

In this case I spent more then 4 secs to check existence of specified row.

Second solution:

SQL> set timing on
SQL> alter system flush buffer_cache;

System altered.

declare
l_cnt number;
begin
    select count(*) into l_cnt
    from dual
    where exists (select code
          from testtab
          where code=999);
if l_cnt>0 then
    dbms_output.put_line('Exists!');
else
    dbms_output.put_line('NOT Exists!');
end if;
end;
/

Exists!

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07

You can notice significant improvement in this case.
Checking existence lasted only 00.07 secs.
This is my preferred way to check existence - query stops after hitting first row.

Third solution:

SQL> set timing on
SQL> alter system flush buffer_cache;

System altered.

declare
l_cnt number;
begin
    select count(*) into l_cnt
    from testtab
    where code = 999
    and rownum = 1;
if l_cnt>0 then
    dbms_output.put_line('Exists!');
else
    dbms_output.put_line('NOT Exists!');
end if;
end;
/

Exists!

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.06

This is another option using rownum=1 inside query. Result is displayed after finding first matching row.

Fourth solution:

SQL> set timing on
SQL> alter system flush buffer_cache;

System altered.

begin
for x in (select count(*) cnt
          from dual
          where exists (select NULL from testtab
                where code=999))
loop
    if (x.cnt = 1)
    then
     dbms_output.put_line('Exists!');
    else
     dbms_output.put_line('NOT Exists!');
    end if;
end loop;
end;
/

Exists!

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07

I found this solution on AskTom site.

Fifth solution:

SQL> set timing on
SQL> alter system flush buffer_cache;

System altered.

begin
for x in (select /*+ first_rows(1) */ count(*) cnt
          from testtab
          where code=999
      and rownum < 2)
loop
    if (x.cnt = 1)
    then
     dbms_output.put_line('Exists!');
    else
     dbms_output.put_line('NOT Exists!');
    end if;
end loop;
end;
/

Exists!

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.06

Another solution from AskTom site. This time without querying dual table.

Drop test table:

drop table testtab;

Table dropped.

You can find many other solutions for checking row existence using Google.

REFERENCES:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3069487275935

ORA-14074 when adding partition at the beginning of a table

2010-01-19T10:16:00.021+01:00

One of maintenance tasks for DBA's in our company is archiving old data. To ease maintenance for us we've created few big interval-date range partitioned tables to be able perform partition based operations on that tables.

We archive old partitions using exchange partition + transportable tablespace feature.

This time I won't concentrate on archiving partitions but adding old partitions back to table.

So how to add "old" partition at the beginning or in the middle of a table?

My first try was using 'alter table add partition' command but then I received error:
ORA-14074: partition bound must collate higher than that of the last partition

Small demo case with workaround...

First to create partitioned table with global and local indexes:

create table testtab (id number,
                      code number,
                      datum date)
partition by range (datum) (
    partition part_2009_10 values less than (to_date('01.11.2009','dd.mm.yyyy')),
    partition part_2009_11 values less than (to_date('01.12.2009','dd.mm.yyyy')),
    partition part_2009_12 values less than (to_date('01.01.2010','dd.mm.yyyy')),
    partition part_2010_01 values less than (to_date('01.02.2010','dd.mm.yyyy'))
    );

 
-- global index    
create unique index id_idx on testtab(id);


-- local indexes
create index code_idx on testtab(code)
local ( partition part_2009_10,
        partition part_2009_11,
        partition part_2009_12,
        partition part_2010_01);
        
        
-- create primary key
alter table testtab add constraint pk_id
    primary key (id) using index;

Populate table:

insert into testtab
select level,abs(dbms_random.random), trunc(sysdate)-level
from dual
connect by level<101;

commit;

Small check:

SQL> begin
  2  dbms_stats.gather_table_stats(ownname=>user,tabname=>'TESTTAB');
  3  end;
  4  /

PL/SQL procedure successfully completed.

select partition_name,high_value,num_rows as "NR", blocks
from user_tab_partitions
where table_name='TESTTAB';

PARTITION_NAME  HIGH_VALUE                                                  NR    BLOCKS
--------------- ---------------------------------------------------------- ----- -------
PART_2009_11    TO_DATE(' 2009-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')     30       5
PART_2009_12    TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')     31       5
PART_2010_01    TO_DATE(' 2010-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')     17       5
PART_2009_10    TO_DATE(' 2009-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')     22       5

4 rows selected.

Now to "archive old partition":

-- create new table based on testtab for "archive" data
create table tab_2009_10
as select * from testtab where 1=2;

alter table tab_2009_10 modify (id not null);

create index code_2009_10_idx on tab_2009_10(code);


-- exchange "old" partition
alter table testtab exchange partition part_2009_10
with table tab_2009_10 including indexes update global indexes;


-- drop "old" partition prom partitioned table
alter table testtab drop partition part_2009_10 update global indexes;


-- select
col partition_name for a15
col high_value for a90

select partition_name,high_value
from user_tab_partitions
where table_name='TESTTAB'
order by 1;

PARTITION_NAME  HIGH_VALUE
--------------- -------------------------------------------------------------
PART_2009_11    TO_DATE(' 2009-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')
PART_2009_12    TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')
PART_2010_01    TO_DATE(' 2010-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')

3 rows selected.



-- select "archived" data from newly created table
select min(datum),max(datum),count(*)
from tab_2009_10;

MIN(DATUM)          MAX(DATUM)            COUNT(*)
------------------- ------------------- ----------
10.10.2009 00:00:00 31.10.2009 00:00:00         22

1 row selected.

It is now safe to export tab_2009_10 table and archive it somewhere else. But what if I need this archived data to be a part of partitioned table again... First I will try to add partition using "add partition" command:

-- add partition test
SQL> alter table testtab add partition part_2009_10
  2      values less than (to_date('01.11.2009','dd.mm.yyyy'));
alter table testtab add partition part_2009_10
                                  *
ERROR at line 1:
ORA-14074: partition bound must collate higher than that of the last partition

So little workaround is needed... We could use "split partition" command to add partition at the beginning of the table:

alter table testtab split partition part_2009_11
at (to_date('01.11.2009','dd.mm.yyyy'))
into (partition part_2009_10,partition part_2009_11);

Little check:

col partition_name for a15
col high_value for a90

select partition_name,high_value
from user_tab_partitions
where table_name='TESTTAB'
order by 1;

PARTITION_NAME  HIGH_VALUE
--------------- --------------------------------------------------------------
PART_2009_10    TO_DATE(' 2009-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')
PART_2009_11    TO_DATE(' 2009-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')
PART_2009_12    TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')
PART_2010_01    TO_DATE(' 2010-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')

So PART_2009_10 is again part of partitioned table. Now it is possible to insert archived rows in partitioned table using "insert ... select" command:

insert into testtab
    select * from tab_2009_10;

This is little inconvenient solution when we have to insert many rows, so there is another solution using exchange partition:

alter table testtab exchange partition part_2009_10
  2  with table tab_2009_10 including indexes update global indexes;
alter table testtab exchange partition part_2009_10
*
ERROR at line 1:
ORA-14130: UNIQUE constraints mismatch in ALTER TABLE EXCHANGE PARTITION

To avoid this error I've disabled and re-enabled constraint of partitioned table. NOTE! I don't know if this is appropriate solution so I would be tankful if someone could suggest me better solution.

SQL> alter table testtab disable novalidate constraint PK_ID;

Table altered.

-- because of disable novalidate pk_id lost not null constraint
SQL> alter table tab_2009_10 modify (id null);

Table altered.

SQL> alter table testtab exchange partition part_2009_10
  2  with table tab_2009_10 including indexes update global indexes;

Table altered.

SQL> alter table testtab enable validate constraint PK_ID;

Table altered.

For final check I will query "old" data in partitioned table:

SQL> select min(datum),max(datum),count(*)
  2   from testtab partition (part_2009_10);

MIN(DATUM)          MAX(DATUM)            COUNT(*)
------------------- ------------------- ----------
10.10.2009 00:00:00 31.10.2009 00:00:00         22

1 row selected.

References: Metalink [ID 727306.1]

Partial restore of a database

2010-01-12T12:51:00.024+01:00

Before database migration to new version it is always wise to test how will applications work on this new version.

But what if you have in production very big database and your test storage is limited - then partial restore comes as handy solution.

In most cases you will probably have few big interval-date range partitioned tables in production database. To fit in limited storage you can expel some tables or partitions. Using partial restored database you can still successfully test functionality of application.

Note:
After partial restore is finished it is probably important to recreate few tables or to fix some other errors - but this depends from application to application.

In this demo case I will concentrate only on partial restore.

OS: Red Hat Enterprise Linux Server release 5.4 (Tikanga) 32bit
DB: Oracle 10.2.0.4

First to list tablespaces of my database:

SQL> set lines 200
SQL> set pages 999
SQL> col tablespace_name for a30
SQL> col file_name for a55
SQL> select tablespace_name, file_name from dba_data_files;

TABLESPACE_NAME                FILE_NAME
------------------------------ -------------------------------------------------------
SYSTEM                         /oratest/oradata/datafiles/system01.dbf
UNDOTBS1                       /oratest/oradata/datafiles/undotbs101.dbf
SYSAUX                         /oratest/oradata/datafiles/sysaux01.dbf
APP_DATA                       /oratest/oradata/datafiles/app_data01.dbf
USERS                          /oratest/oradata/datafiles/users01.dbf
BILL_DATA                      /oratest/oradata/datafiles/bill_data01.dbf
OT_DATA_01                     /oratest/oradata/datafiles/ot_data_01.dbf
OT_DATA_02                     /oratest/oradata/datafiles/ot_data_02.dbf
OT_DATA_03                     /oratest/oradata/datafiles/ot_data_03.dbf
OT_DATA_04                     /oratest/oradata/datafiles/ot_data_04.dbf
OT_DATA_05                     /oratest/oradata/datafiles/ot_data_05.dbf
OT_DATA_06                     /oratest/oradata/datafiles/ot_data_06.dbf
OT_DATA_07                     /oratest/oradata/datafiles/ot_data_07.dbf

Before anything I will backup this database using script below:

RMAN> run
2> {
3> allocate channel c1 type disk;
4> backup as compressed backupset incremental level 0 database format '/home/oratest/rmanbkp/FULL_%d_%u_%s_%T';
5> BACKUP CURRENT CONTROLFILE FORMAT '/home/oratest/rmanbkp/controlf_%d_%u_%s_%T';
6> BACKUP SPFILE FORMAT '/home/oratest/rmanbkp/spfile_%d_%u_%s_%T';
7> release channel c1;
8> }

Now to create control file script:

SQL> alter database backup controlfile to trace as '/home/oratest/ctlfile_scr.sql';

Database altered.

What tablespaces are mandatory for partial restore:
- SYSTEM tablespace
- SYSAUX tablespace (if my database is 10g or higher)
- Other tablespaces that hold dictionary information
select distinct tablespace_name from dba_segments where owner in ('SYS', 'SYSTEM');
- Tablespace with rollback segments

Optional:
- All tablespaces with user data that I want to use for restore

In this case i will use this tablespaces for partial restore:

SYSTEM /oratest/oradata/datafiles/system01.dbf
SYSAUX /oratest/oradata/datafiles/sysaux01.dbf
UNDOTBS1 /oratest/oradata/datafiles/undotbs101.dbf
APP_DATA /oratest/oradata/datafiles/app_data01.dbf
USERS /oratest/oradata/datafiles/users01.dbf
BILL_DATA /oratest/oradata/datafiles/bill_data01.dbf
OT_DATA_01 /oratest/oradata/datafiles/ot_data_01.dbf

I've expelled OT_DATA "old" parts...

Edit control file script including only tablespaces that I want to restore:

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "TESTDB" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/oratest/oradata/TESTDB/redo/redo01a.rdo',
    '/oratest/oradata/TESTDB/redo/redo01b.rdo'
  ) SIZE 100M,
  GROUP 2 (
    '/oratest/oradata/TESTDB/redo/redo02a.rdo',
    '/oratest/oradata/TESTDB/redo/redo02b.rdo'
  ) SIZE 100M,
  GROUP 3 (
    '/oratest/oradata/TESTDB/redo/redo03a.rdo',
    '/oratest/oradata/TESTDB/redo/redo03b.rdo'
  ) SIZE 100M
-- STANDBY LOGFILE

DATAFILE
  '/oratest/oradata/datafiles/system01.dbf',
  '/oratest/oradata/datafiles/undotbs101.dbf',
  '/oratest/oradata/datafiles/sysaux01.dbf',
  '/oratest/oradata/datafiles/app_data01.dbf',
  '/oratest/oradata/datafiles/users01.dbf',
  '/oratest/oradata/datafiles/bill_data01.dbf',
  '/oratest/oradata/datafiles/ot_data_01.dbf'
CHARACTER SET AL32UTF8
;

It is time to test this partial restore.

First to restore control file and mount database:

RMAN> startup nomount;

RMAN> run
2> {
3> allocate channel c1 device type disk;
4> restore controlfile from '/home/oratest/rmanbkp/controlf_TESTDB_i5l38viu_1605_20100112';
5> release channel c1;
6> }

RMAN> alter database mount;
database mounted

To check File# of datafiles that I want to restore:

SQL> SET PAGES 200
SQL> COLUMN NAME FORMAT a60
SQL> SELECT FILE#, NAME FROM V$DATAFILE;

     FILE# NAME
---------- ------------------------------------------------------------
         1 /oratest/oradata/datafiles/system01.dbf
         2 /oratest/oradata/datafiles/undotbs101.dbf
         3 /oratest/oradata/datafiles/sysaux01.dbf
         4 /oratest/oradata/datafiles/app_data01.dbf
         5 /oratest/oradata/datafiles/users01.dbf
         6 /oratest/oradata/datafiles/bill_data01.dbf
         7 /oratest/oradata/datafiles/ot_data_01.dbf
         8 /oratest/oradata/datafiles/ot_data_02.dbf
         9 /oratest/oradata/datafiles/ot_data_03.dbf
        10 /oratest/oradata/datafiles/ot_data_04.dbf
        11 /oratest/oradata/datafiles/ot_data_05.dbf
        12 /oratest/oradata/datafiles/ot_data_06.dbf
        13 /oratest/oradata/datafiles/ot_data_07.dbf

Restore specified datafiles:

RMAN> run
2> {
3> restore datafile 1;
4> restore datafile 2;
5> restore datafile 3;
6> restore datafile 4;
7> restore datafile 5;
8> restore datafile 6;
9> restore datafile 7;
10> }

Create new control file using script above and mount database:

$ sqlplus "/as sysdba"

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> @/home/oratest/ctlfile_scr.sql
ORACLE instance started.

Total System Global Area 1342177280 bytes
Fixed Size                  1267380 bytes
Variable Size            1191184716 bytes
Database Buffers          134217728 bytes
Redo Buffers               15507456 bytes

Control file created.

I will cancel media recovery in this test case:

SQL> recover database until cancel using backup controlfile;
ORA-00279: change 36022694011 generated at 01/12/2010 09:48:08 needed for
thread 1
ORA-00289: suggestion : /oratest/oradata/archlogs/1_18_706354584.dbf
ORA-00280: change 36022694011 for thread 1 is in sequence #18


Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.

Open database using resetlogs command:

SQL> alter database open resetlogs;

Database altered.

Create temporary tablespace:

SQL> ALTER TABLESPACE TMP ADD TEMPFILE '/oratest/oradata/datafiles/tmp01.dbf'
  2  SIZE 50M REUSE AUTOEXTEND ON NEXT 25M  MAXSIZE 2048M;


Tablespace altered.

Excerpt from alert log:

...
Dictionary check beginning
Tablespace 'TMP' #7 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'OT_DATA_02' #9 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'OT_DATA_03' #10 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'OT_DATA_04' #11 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'OT_DATA_05' #12 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'OT_DATA_06' #13 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'OT_DATA_07' #14 found in data dictionary,
but not in the controlfile. Adding to controlfile.
File #8 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00008' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #9 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00009' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #10 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00010' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #11 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00011' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #12 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00012' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #13 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00013' in the controlfile.
This file can no longer be recovered so it must be dropped.
Dictionary check complete
...

Tablespace check:

SQL> set lines 200
SQL> set pages 999
SQL> col tablespace_name for a30
SQL> col file_name for a55
SQL> select tablespace_name, file_name from dba_data_files
  2  union all
  3  select tablespace_name, file_name from dba_temp_files;

TABLESPACE_NAME                FILE_NAME
------------------------------ -------------------------------------------------------
BILL_DATA                      /oratest/oradata/datafiles/bill_data01.dbf
USERS                          /oratest/oradata/datafiles/users01.dbf
APP_DATA                       /oratest/oradata/datafiles/app_data01.dbf
SYSAUX                         /oratest/oradata/datafiles/sysaux01.dbf
UNDOTBS1                       /oratest/oradata/datafiles/undotbs101.dbf
SYSTEM                         /oratest/oradata/datafiles/system01.dbf
OT_DATA_01                     /oratest/oradata/datafiles/ot_data_01.dbf
OT_DATA_02                     /oratest/product/10.2.0/dbs/MISSING00008
OT_DATA_03                     /oratest/product/10.2.0/dbs/MISSING00009
OT_DATA_04                     /oratest/product/10.2.0/dbs/MISSING00010
OT_DATA_05                     /oratest/product/10.2.0/dbs/MISSING00011
OT_DATA_06                     /oratest/product/10.2.0/dbs/MISSING00012
OT_DATA_07                     /oratest/product/10.2.0/dbs/MISSING00013
TMP                            /oratest/oradata/datafiles/tmp01.dbf

14 rows selected.

Drop tablespaces with missing datafiles:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1342177280 bytes
Fixed Size                  1267380 bytes
Variable Size            1191184716 bytes
Database Buffers          134217728 bytes
Redo Buffers               15507456 bytes
Database mounted.
SQL> alter database datafile '/oratest/product/10.2.0/dbs/MISSING00008' offline drop;

Database altered.

SQL> alter database datafile '/oratest/product/10.2.0/dbs/MISSING00009' offline drop;

Database altered.

SQL> alter database datafile '/oratest/product/10.2.0/dbs/MISSING00010' offline drop;

Database altered.

SQL> alter database datafile '/oratest/product/10.2.0/dbs/MISSING00011' offline drop;

Database altered.

SQL> alter database datafile '/oratest/product/10.2.0/dbs/MISSING00012' offline drop;

Database altered.

SQL> alter database datafile '/oratest/product/10.2.0/dbs/MISSING00013' offline drop;

Database altered.



SQL> alter database open;

Database altered.


SQL> drop tablespace OT_DATA_02 including contents;

Tablespace dropped.

SQL> drop tablespace OT_DATA_03 including contents;

Tablespace dropped.

SQL> drop tablespace OT_DATA_04 including contents;

Tablespace dropped.

SQL> drop tablespace OT_DATA_05 including contents;

Tablespace dropped.

SQL> drop tablespace OT_DATA_06 including contents;

Tablespace dropped.

SQL> drop tablespace OT_DATA_07 including contents;

Tablespace dropped.

Final check:

SQL> select tablespace_name, file_name from dba_data_files;

TABLESPACE_NAME                FILE_NAME
------------------------------ -------------------------------------------------------
OT_DATA_01                     /oratest/oradata/datafiles/ot_data_01.dbf
BILL_DATA                      /oratest/oradata/datafiles/bill_data01.dbf
USERS                          /oratest/oradata/datafiles/users01.dbf
APP_DATA                       /oratest/oradata/datafiles/app_data01.dbf
SYSAUX                         /oratest/oradata/datafiles/sysaux01.dbf
UNDOTBS1                       /oratest/oradata/datafiles/undotbs101.dbf
SYSTEM                         /oratest/oradata/datafiles/system01.dbf

12 rows selected.

Update:
I want to thank Damir Vadas for noticing unnecessary cataloging of backup pieces in this demo case.

References:
Metalink ID 102786.1

Validating backups and database files using RMAN

2009-12-29T11:18:00.002+01:00

The most critical tasks for every DBA are database backup and recovery procedures. Knowledge of all Oracle backup and recovery scenarios is practically the most essential skill. For almost every problem there is some solution, but if you can't restore/recover data you are in deep trouble.

To avoid possible problems it is important to regularly monitor backups and to perform validations using various tools. You can't say that you have valid backup until you successfully perform test restore on another machine. There are several methods to validate backups taken with RMAN. Restores on another machine is the best validation method and you should perform it regularly according how critical your database is. Big benefit from performing test restores is valuable practice you'll get.

Another method for validating backups is using RMAN commands.
As a part of my daily tasks I'am using this simple RMAN script for backup validation:

run
{
restore database validate;
restore archivelog from time 'sysdate-1' validate;
restore controlfile validate;
restore spfile validate;
}

In this script I'am doing this checks:
- check most recent RMAN database backup
- check most recent RMAN archivelog(s) backup
- check most recent controlfile backup
- check most recent RMAN spfile backup

To check older backups just set SET UNTIL clause.

Using RESTORE VALIDATION I can verify whether database could be successfully restored or not using existing backups.

It is good practice to automate this tasks and then grep logs for errors. RESTORE VALIDATE operation takes time and it consumes valuable resources so you have to think about when to schedule validation.

You can consider this RMAN validation as addition to your regular trial recoveries.

About using RMAN to check Logical & Physical Database Corruption

During a RMAN backup or RMAN 'backup validate' every block currently used or previously used is read into memory then written to another portion of memory. During this memory to memory write the block is checked for corruption. Therefore RMAN's BACKUP command with the VALIDATE and CHECK LOGICAL clauses allow a Database Adminstrator to quickly check for both physical and logical corruption. If the initialization parameter DB_BLOCK_CHECKSUM=TRUE, specifying CHECK LOGICAL detects all types of corruption that are possible to detect.

Physical corruption can be defined as a damage to the internal data structure and prevents Oracle from being able to read the data in the corrupted blocks.
Logical corruption is situation when Oracle is able to find the data, but data values are incorrect.

To validate all datafiles execute:

RMAN> run
2> {
3> allocate channel c1 type disk;
4> backup check logical validate database;
5> release channel c1;
6> }

After validate is complete query v$database_block_corruption to see if there are any rows listed there. Additionally you can check alert log for corruptions.

REFERENCES:
Metalink notes: [ID 338607.1], [ID 283053.1], [ID 428570.1]