跳至主要内容

关于tablespace和table

今天拿测试库做练习，想来该做点什么，那么就建个tablespace吧。
第一次输入

SQL>CREATE TABLESPACE username DATAFILE '/SERVER/ora9/oradata/ora9 ' SIZE
100M AUTOEXTEND ON NEXT 5M MAXSIZE 200M;
(后面回想的，前面怎么做的我在putty上也找不到了，SELECT了一个语句不知道不刷到哪里去了)然后发觉username应该是自己的用户名（傻了，-______-）继续写了下面这条
SQL>CREATE TABLESPACE atu DATAFILE '/SERVER/ora9/oradata/ora9/atu.dbf ' SIZE
100M AUTOEXTEND ON NEXT 5M MAXSIZE 200M;

然后第一条就成了今天郁闷的关键，因为在本来ora9是已经存在的文档目录所有.dbf文件都存放在这个下。

当我自己在linux环境下检查的时候发觉同时存在，就rm掉了，回到sqlplus下

SQL>shutdown immedaite;时候出现以下问题
ORA-01116: error in opening database file 15
ORA-01110: data file 15: '/SERVER/ora9/oradata/ora9'
ORA-27041: unable to open fileLinux Error: 2: No such file or
directory

无解，求助领导。

SQL>select file_id,file_name,tablespace_name from dba_data_files;
1 /SERVER/ora9/oradata/ora9/system01.dbf SYSTEM
2 /SERVER/ora9/oradata/ora9/hyf.dbf HYF
3 /SERVER/ora9/oradata/ora9/cwmlite01.dbf CWMLITE
4 /SERVER/ora9/oradata/ora9/drsys01.dbf DRSYS
5 /SERVER/ora9/oradata/ora9/indx01.dbf INDX
6 /SERVER/ora9/oradata/ora9/odm01.dbf ODM
7 /SERVER/ora9/oradata/ora9/tools01.dbf TOOLS
8 /SERVER/ora9/oradata/ora9/users01.dbf USERS
9 /SERVER/ora9/oradata/ora9/xdb01.dbf XDB
10 /SERVER/ora9/oradata/ora9/t2007q1.dbf T2007Q1
11 /SERVER/ora9/oradata/ora9/t2007q2.dbf T2007Q2
12 /SERVER/ora9/oradata/ora9/t2007q3.dbf T2007Q3
13 /SERVER/ora9/oradata/ora9/t2007q4.dbf T2007Q4
14 /SERVER/ora9/oradata/ora9/undo.dbf UNDO
15 /SERVER/ora9/oradata/ora9 USERDATA
16 /SERVER/ora9/oradata/ora9/atu.dbf ATU

由于我刚刚的失误，导致第15条语句无法加载，而且无法正常的shutdown想直接删除表空间:

SQL>drop tablespace USERDATA ;
ORA-01116: error in opening database file 15
ORA-01110: data file 15: '/SERVER/ora9/oradata/ora9'
ORA-27041: unable to open fileLinux Error: 2: No such file or directory

OFFLINE办法:

SQL>alter tablespace UESRDATA offline;
ORA-01116: error in opening database file 15
ORA-01110: data file 15: '/SERVER/ora9/oradata/ora9'
ORA-27041: unable to open fileLinux Error: 2: No such file or directory

试着更名,可是不能OFFLINE也不能改名.

  • 先SHUTDOWN ABORT;
  • STARTUP MOUNT:
  • Alter database backup controlfile to trace;得到重建控制文件脚本;
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORA9" RESETLOGS FORCE LOGGING
ARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 '/SERVER/ora9/oradata/ora9/redo01.log' SIZE 20M,
GROUP 2 '/SERVER/ora9/oradata/ora9/redo02.log' SIZE 20M,
GROUP 3 '/SERVER/ora9/oradata/ora9/redo03.log' SIZE 20M
-- STANDBY LOGFILE
DATAFILE'/SERVER/ora9/oradata/ora9/drsys01.dbf',
'/SERVER/ora9/oradata/ora9/indx01.dbf',
'/SERVER/ora9/oradata/ora9/odm01.dbf',
'/SERVER/ora9/oradata/ora9/tools01.dbf',
'/SERVER/ora9/oradata/ora9/users01.dbf',
'/SERVER/ora9/oradata/ora9/xdb01.dbf',
'/SERVER/ora9/oradata/ora9/t2007q1.dbf',
'/SERVER/ora9/oradata/ora9/t2007q2.dbf',
'/SERVER/ora9/oradata/ora9/t2007q3.dbf',
'/SERVER/ora9/oradata/ora9/t2007q4.dbf',
'/SERVER/ora9/oradata/ora9/undo.dbf',
'/SERVER/ora9/oradata/ora9',
'/SERVER/ora9/oradata/ora9/atu.dbf'
CHARACTER SET ZHS16GBK
并在脚本中删除错误的数据文件: '/SERVER/ora9/oradata/ora9'

在重建控制文件后,试着打开数据库:


SQL> alter database open resetlogs;
alter database open resetlogs
*ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1:
'/SERVER/ora9/oradata/ora9/system01.dbf'//需要恢复

SQL> recover database using backup controlfile;
ORA-00279: change 10703011 generated at 12/19/2007 07:22:31 needed for
thread
ORA-00289: suggestion : /SERVER/ora9/arc/1_108.dbf
ORA-00280: change 10703011 for thread 1 is in sequence #108
Specify
log: {=suggested filename AUTO
CANCEL}/SERVER/ora9/oradata/ora9/redo02.log
ORA-00310: archived log contains sequence 107; sequence 108 required
ORA-00334: archived log: '/SERVER/ora9/oradata/ora9/redo02.log'

SQL> recover database using backup controlfile;
ORA-00279: change 10703011 generated at 12/19/2007 07:22:31 needed for
thread
ORA-00289: suggestion : /SERVER/ora9/arc/1_108.dbf
ORA-00280: change 10703011 for thread 1 is in sequence #108

Specify log: {=suggested filename AUTO
CANCEL}/SERVER/ora9/oradata/ora9/redo03.log (使用的是当前日志)
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to controlfile by media recovery
ORA-01110: data file 15: '/SERVER/ora9/oradata/ora9'ORA-01112: media
recovery not started

SQL> recover database using backup controlfile;
ORA-00283: recovery session canceled due to errors
ORA-01111: name for data file 15 is unknown - rename to correct file
ORA-01110: data file 15: '/SERVER/ora9/product/9.2/dbs/UNNAMED00015'
ORA-01157: cannot identify/lock data file 15 - see DBWR trace file
ORA-01111: name for data file 15 is unknown - rename to correct file
ORA-01110: data file 15: '/SERVER/ora9/product/9.2/dbs/UNNAMED00015'
由于系统表空间中,在记录这个数据文件,而在控制文件中没有,所以系统报这个错误.

SQL> select name from v$datafile;
NAME--------------------------------------------------------------------------------/SERVER/ora9/oradata/ora9/system01.dbf/SERVER/ora9/oradata/ora9/hyf.dbf/SERVER/ora9/oradata/ora9/cwmlite01.dbf/SERVER/ora9/oradata/ora9/drsys01.dbf/SERVER/ora9/oradata/ora9/indx01.dbf/SERVER/ora9/oradata/ora9/odm01.dbf/SERVER/ora9/oradata/ora9/tools01.dbf/SERVER/ora9/oradata/ora9/users01.dbf/SERVER/ora9/oradata/ora9/xdb01.dbf/SERVER/ora9/oradata/ora9/t2007q1.dbf/SERVER/ora9/oradata/ora9/t2007q2.dbf
NAME--------------------------------------------------------------------------------/SERVER/ora9/oradata/ora9/t2007q3.dbf/SERVER/ora9/oradata/ora9/t2007q4.dbf/SERVER/ora9/oradata/ora9/undo.dbf/SERVER/ora9/product/9.2/dbs/UNNAMED00015/SERVER/ora9/oradata/ora9/atu.dbf
16 rows selected.

SQL> alter database
datafile'/SERVER/ora9/product/9.2/dbs/UNNAMED00015' offline;
Database
altered.

SQL> alter database open resetlogs;alter database open
resetlogs*ERROR at line 1:
ORA-01113: file 1 needs media recoveryORA-01110: data file 1:
'/SERVER/ora9/oradata/ora9/system01.dbf'

SQL> recover database using backup controlfile;
ORA-00279: change 10712117 generated at 12/19/2007 10:12:24 needed for
thread 1
ORA-00289: suggestion : /SERVER/ora9/arc/1_108.dbf
ORA-00280: change 10712117 for thread 1 is in sequence #108
Specify
log: {=suggested filename AUTO CANCEL}/SERVER/ora9/oradata/ora9/redo03.log
(当前日志)Log applied.Media recovery complete.

SQL> alter database open resetlogs;alter database open
resetlogs*ERROR at line 1:
ORA-01245: offline file 15 will be lost if RESETLOGS is done
ORA-01111: name for data file 15 is unknown - rename to correct file
ORA-01110: data file 15: '/SERVER/ora9/product/9.2/dbs/UNNAMED00015'

SQL> alter database
datafile'/SERVER/ora9/product/9.2/dbs/UNNAMED00015' offline;Database altered.
SQL> alter database open resetlogs;alter database open resetlogs*ERROR at
line 1:
ORA-01245: offline file 15 will be lost if RESETLOGS is done
ORA-01111: name for data file 15 is unknown - rename to correct file
ORA-01110: data file 15: '/SERVER/ora9/product/9.2/dbs/UNNAMED00015'

SQL> alter database datafile 15 offline drop;Database altered.

SQL> alter database open resetlogs;Database altered.
到此,数据库正常打开,恢复全部完成.

以上是我领导恢复的，借鉴下例子，
其实在做事情的时候首先想到的应该是备份，上次在市府那次也应该是这样，不论你的数据有多么的重要先做好备份是一个好习惯。
其实在更改你的id名想让你的id名字和你的tablespace一致的话其实可以这样。

SQL>SELECT default_tablespace FROM dba_users WHERE username =
'ATU';
结果应该是hyf
SQL>CREATE TABLESPACE atu DATAFILE '/SERVER/ora9/oradata/ora9/atu.dbf'
SIZE 100M;
SQL>ATLER USER atu default tablespace atu;
SQL>SELECT default_tablespace FROM dba_users WHERE username = 'ATU'

现在看就是atu了原来在hyf下我所建立的表转到atu下
SQL>ALTER table atu.a move atu
现在所看到的是a表转到atu这个tablespace了，然后批量转需要做一个脚本，脚本目前还暂时不会做。刚刚也没看清楚生成后在sql，command下完成。

P.S. （网络上找的消息）
数据文件就好像水桶。
水桶中没有被水淹过的地方（高水位以上）， 空间可以回收。

alter datafile datafile FILE resize xxxM ;

如果水桶都被淹过了，那就有点困难了。
一种方法是alter table move tablespace 命令。

比如表T的tablespace为system,现在要把它移动到tablespace users,
语法是： alter table T move tablespace users;
如果是索引，比如是idx1,要移动到tablespace ts_idx,语法是： alter index idx1 rebuild
tablespace ts_idx ;

另一种方法是使用exp/imp。

方法二：
alter database create datafile 15 as ’/ server/ora9.../ora9 ' reuse;recover datafile 15
。。。。似乎是因为我乱删东西造成的

今天买了双鞋子，几个人看了都不好，我觉得差不多，便宜么，能打就好了，打得时候看着是何不适合。


标签：

评论

发表评论

此博客中的热门博文

Python学习笔记20100117

映射list   Dictionary 是用{}. list 是一那个[]. turple是用() 当你定义过dictionary后，你可以使用d.keys(), d.values(),d.items()将定义后的时候分别显示出来 当然可以将list里的值加减乘除，也可以如一般的定义直接重新复制这个list. 链接与分割字符串的 li=";", join(li) 则显示的就是 分隔符为;的数 如果使用li=li.split(";")则将刚刚;的分隔符删除，而split里也可定义域，如li.split(";",1)   自省 <---这是啥 之前在使用的时候发觉有些书本上的模块不能调用，很多是py脚本定义过的函数。 下载该脚本s，然后上传至指定位置。 >>>import sys >>>sys.path >>>sys.path.append("绝对位置")然后就能调用这些脚本和参数了。 删除 >>>sys.path.pop() 定义一个参数是 def info(test, test1=10.test2=12): info是函数名，test是必备参数，因为没有定义值，test1和test2是可选参数，定义了初始值   以上是外部函数的调用，下面转到内部函数 内部函数有type,str,dir及其他   ---type 返回任意字符的类型，模块也可以。types模块 >>> type(1) <type 'int'> >>> li=[] >>> type(li) <type 'list'> >>> import odbchelper >>> type(odbchelper) <type 'module'> >>> type(sys) <type 'module'> >>> import types ...
发表评论
阅读全文

Setagaya park

  We visited Setagaya Park (世田谷区) yesterday. This park reminds us when we in Europe, which has a fancy fountain in the middle, and free entrance. People just laid down on the grass, enjoy the day time they can waste. people who have kids together,  play all different kinds of sports. you really can't image whether you are in Europe or  Japan. I would prefer to use a Bus instead of the metro, the metro is fast, but you only can see the dark, light, people sit or stand as Sardine, without any emotion. but on the bus, you can see the traffic, the shop, everything on the road. When we on our way to the park, we stop by a good chocolate shop named " craft chocolate works ",  this is a very good place to have ice cream, you probably can choose half & half, you can enjoy the feeling when you smell the chocolate smell and eat with it.  or you can enjoy beer or cake in "globe antiques (ザ グローブアン ティ ークス） .   also, we checked the Blue Bottle Coffee Sangenjaya ...
发表评论
阅读全文