I saw two case recently about losing one of the data files for a tablespace, first case was losing a data file totally, second case is one of the data files has no recent backup, so it's equal to missing a data file. In this case how to recover the data from that tablespace? We can offline drop the missing data file and then get the database open. I create tablespace with three data files for demo.

SQL> SELECT FILE_ID FROM DBA_DATA_FILES
  2     WHERE TABLESPACE_NAME='USERS01';

   FILE_ID
----------
         5
         6
         7

    And I create a table on this tablespace, filled in some data, make sure it has extents on all three data files.

SQL> SELECT EXTENT_ID, FILE_ID FROM DBA_EXTENTS
  2     WHERE SEGMENT_NAME='T_OBJECTS' AND OWNER=USER;

EXTENT_ID    FILE_ID
---------- ----------
         0          7
         1          5
         2          6
         3          7

    To avoid recover data file every time I online a data file, I put the tablespace in read only mode.

Read Full Text of【When lossing one of the data files for a tablespace】

Related Posts

Leave New Comment(Current: 0)

Link: http://www.dbatools.net/experience/lost_one_data_file.html

©作者：eygle 发布在 eygle.com

在某些情况下，数据库失去响应，sqlplus也无法连接，此时通常只能通过杀掉进程来解决。

但是我们仍然希望能够获得此时的数据库状态信息，以便用于事后诊断。
从Oracle10g开始，sqlplus提供了一个参数选项-prelim，可以在通常sqlplus无法连接的情况下进行连接。
通过以下步骤可以获取系统信息：

sqlplus -prelim / as sysdba
oradebug setmypid
oradebug unlimit;
oradebug dump systemstate 10

这种方法非常有用：

$ sqlplus -prelim / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Oct 25 09:42:20 2007

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

SQL>

但是需要注意的是，在Oracle 10.2.0.1中，使用如上方法会出现一个错误：

$ sqlplus -prelim / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Oct 25 09:38:14 2007

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

SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug dump systemstate 10
ORA-03113: end-of-file on communication channel
ORA-24323: value not allowed

Thu Oct 25 09:38:32 2007
System State dumped to trace file
Thu Oct 25 09:38:32 2007
Errors in file /opt/oracle/admin/test201/udump/test201_ora_1402.trc:
ORA-07445: exception encountered: core dump [kgldmp()+1360] [SIGSEGV] [Address not mapped to object] [0x000000030] [] []

记录一下!

-The End-

相关文章|Related Articles

• Oracle HowTo:判断一个死事务的恢复进度
  
• 整理数据的小技巧-使用Excel 、SQLPLUS和PL/SQL developer
  
• FAQ List
  

评论数量(1)|Add Comments

本文网址：http://www.eygle.com/archives/2007/10/sqlplus_prelim.html

Contributed by Alexander Todorov

Splitting big files into pieces is a common task. Another common task is to create a tar archive, and split it into smaller chunks that can be burned onto CD/DVD. The straightforward approach is to create the archive and then use ’split.’ To do this, you will need more free space on your disk. In fact, you’ll need space twice the size of the created archive. To avoid this limitation, split the archive as it is being created.

To create a tar archive that splits itself on the fly use the following set of commands:

First create the archive:

tar -czf /dev/stdout $(DIRECTORY_OR_FILE_TO_COMPRESS) | split -d -b $(CHUNK_SIZE_IN_BYTES) - $(FILE_NAME_PREFIX)

To extract the contents:

cat $(FILE_NAME_PREFIX)* >> /dev/stdout | tar -xzf /dev/stdin

The above shown set of commands works on the fly. You don’t need additional free space for temporary files.

A few notes about this exercise:

• ‘tar -L’ prompts you on every chunk created. Compression can not be used with -L option. The above command is not interactive and does not prompt for anything. Compression can be used.
• The number of separate files is 100. This is because we use numerical suffixes - ’split -d.’ If the specified chunk size is small you will get ’split: Output file suffixes exhausted’ error. Try with bigger chunk size or with alphabetic suffixes.
• ‘cat’ will concatenate the files properly if they are not renamed. This is due to the fact that the sort order is retained by the appended chunk suffixes.
• Replace ‘tar -z’ with ‘tar -j’ for bzip2 compression or try your favourite compression program. Almost all ‘tar’ and ’split’ options should be possible.
• The resulting chunk files are not valid tar archives. They can not be extracted separately. If you want such functionality use ’split-tar,’ which also needs more free space.

The information provided in this article is for your information only. The origin of this information may be internal or external to Red Hat. While Red Hat attempts to verify the validity of this information before it is posted, Red Hat makes no express or implied claims to its validity.

OK, I am not getting too much people feedback on what would they like to hear about on MySQL Users Conference, so I went ahead and submitted few presentation ideas.

I do not expect all of them would be accepted, furthermore it would be hard to prepare so many good presentations if they are so please let me know if anything of this is of special interest for you. When I would be able to show that to organizers to help with decision.

Also let me know if you have submitted similar talk as in this case there may be way to work together to produce better talk instead.

Hidden Innodb Tuning Options Innodb Storage Engine has a lot of tuning options hardcoded as constants. We investigate if current values are truly optimal for modern systems and if you can get any additional performance.

Investigating Innodb Scalability Limits You may have heard Innodb has limited scalability with multiple CPUs and some of these were fixed in recent MySQL 5.0 versions. In this presentations we will look into which problems are fixed.

MySQL and Social Networks benchmarks We’re looking at typical operations patterns used in Social Networking web applications and analyzing how to implement them most efficiently with MySQL.

MySQL Performance Landscape This presentation compares MySQL Performance with different CPUs, Operation systems, RAID Configuration, File Systems to assist you with your choices next time you select platform for MySQL.

Performance comparison of MySQL 5.0, MySQL 5.1 and MySQL 6.0 Come to see how performance of MySQL 5.1 and forthcoming MySQL 6.0 are different from MySQL 5.0 - we’ll cover different workloads and see when we see gains and when regressions.

Practical MySQL Variables Tuning This session takes practical approach to MySQL variable tuning. Instead of giving you theoretical guesses on what would be right variable values we’ll perform benchmarks and see that.

Searching Billions of documents with MySQL and Sphinx Learn how to build large scale Full Text Search solutions with MySQL and Sphinx. This presentation will talk about architecture of BoardReader.com which indexes over billion of forum posts.

Transactional Storage Engines Performance We’re looking at performance at Open Source Transactional Storage engine MySQL - Innodb, Falcon, SolidDB, PBXT and analyzing how things have changed since last summer.

Using Slave Server Without Breaking User Experience MySQL Replication is asynchronous this means you should be using slaves only for reads and only reads which can read stale data. This presentation teaches you efficient methods of doing so.

Entry posted by peter | One comment

Add to: | | | |

dbanotes posted a photo:

莫非这也是人工智能的体现麽?

Thanks to Jake, I now have a bunch of my “Web 2.0 accounts” aggregated into one place at FriendFeed: del.icio.us, Flickr, Google Reader, Google Shared Stuff, LinkedIn, YouTube and Twitter.

You can subscribe to my feed here. FriendFeed is great. I like it. It is currently in private beta testing. However, if you’re interested, just leave a comment below and I’ll send you an invite.

Speaking of twitter, I’m starting to like it more. You really have to pick the right friends you follow, otherwise you’ll be overwhelmed by useless chatter. Too bad my employer has blocked twitter, but that’s not a problem. I use:

1. twittermail to post tweets by email from work
2. itweet to read and post tweets from my iPhone
3. twitbin to read and post tweets from my Firefox sidebar from home
4. twittergram to post voice tweets from my iPhone (or any phone)
5. jott to convert my voice into text tweets from my iPhone (or any phone)
6. twitterfeed to publish any RSS feed to twitter.

I use twitterfeed in many different ways, like for example posting photo tweets from my iPhone to Flickr->twitter. I also publish my blog feed to my twitter account. Moreover, OraNA, Oracle and OOW twitter accounts use twitterfeed as well.

There are many other twitter applications, but the ones above are my favorites.

---
Related Articles at Eddie Awad's Blog:

• The Oracle Community on Twitter
• Mini Blogs for 2007-04-07
• Mini Posts Roundup from Twitterland (2007-04-08)
• Oracle OpenWorld 2007 - Social Networking, Twittering, Flickring and Unconferencing
• Quick Posts - 2007-08-06

About three months ago I announced ClickAider to become available to general public. And I think it is about the time to write about the progress we have with this project for those who interested.

The project generates decent interest and we have about 3000 sites Registered over this time, which I consider decent number especially as we did not do much of advertisement and PR keeping it low profile and working out few bugs which we might have.

We use GeoIP DNS based load balancing between “gathering” servers in Europe and US which seems to work very well both providing level of HA if one of the servers goes down and allowing to increase accuracy by reducing round trip. Over time we are planning to get more locations with pair of servers in each so we do not need to use relatively slow DNS based fail over if one of the servers goes down.

We get some 600 tracking events per second on our lighttpd tracking servers which currently works well and there is still some capacity available but we’re still planning to get rid of little PHP code we have left at this layer to get it even more efficient. It would be good to handle some 5000 events/sec per server.

MySQL 5.1 with Partitioning works nicely for data storage with no MySQL bugs hitting us with this project so far. We use Innodb tables now because checking and repairing MyISAM is nightmare and PBXT which could be good for this work is just not ready.

MySQL Performance is in fact the most serious issues we have to work with, even now reporting only clicks statistics for some huge sites like Mininova may take quite a while to generate.

The typical solution for trackers is to have summary data built one way or around and we might need to do some of it for certain most common queries. At this point however we’re looking how much performance we can get from real time aggregation because we want absolutely unrestricted dynamic filters and dynamic timezones and this makes things hard to aggregate. This is surely fun challenge to deal with.

We also continue to work on adding more Advertisers we support and improving a ways we track the old ones. Recently we’ve added support for Vibrant Media Intellitxt (hovers only at this point) . We also now support ShoppingAds even though they are not yet out of private beta version.

Looking at the site we’ve added major improvement of saved reports - now you can create custom reports with all filters you would want to and save them for quick use at later time. For example you can track what are the most popular click directions for US audience compared to general audience or track performance for referrals from given domain name to see if partnership makes any sense for you.

Finally we’ve added Demo Account so you do not have to register any more to see system in action. We used our MySQL Performance Forums site for the demo, which might be a bit low traffic but still good to see how system works. This is the reason why we added Google Adsense Adds on that site.

If you have any other ideas what would you like to see implemented in ClickAider, let us know.

Entry posted by peter | 7 comments

Add to: | | | |