Welcome to here my friends who are interesting on Oracle Tech,we enhanced our ability of oracle tech through communication together. I share my experience referred to testing, actual case and ideas of studying. All articles of the space is only my point of view, <<Version3.0-Oracle Database 10g-Administration Workshop I>> <<Version3.0-Oracle Database 10g-Administration Workshop II>> <<Oracle Data Integrator Administration and Development>> <<深入浅...

重建控制文件之后,只读表空间的状态变化

上一篇 / 下一篇  2008-08-15 13:38:12

SQL> alter tablespace staging read only;

SQL> alter database backup controlfile to trace;

Database altered.

SQL> shutdown immediate;ITPUB个人空间J"KD}2oL4YA
Database closed.
q eHm8lMMaY3i0Database dismounted.ITPUB个人空间N3G4|`.y-U.U
ORACLE instance shut down.ITPUB个人空间#RY)EG\s|^
SQL> startup nomount;
CFI3SO0p0ORACLE instance started.

Total System Global Area  167772160 bytesITPUB个人空间 `D&{ MHuL?
Fixed Size                  1218316 bytesITPUB个人空间#V&W3rr1W}:K
Variable Size              79694068 bytesITPUB个人空间O2}?IfB
Database Buffers           83886080 bytesITPUB个人空间O*Z:Q,me tt1R
Redo Buffers                2973696 bytes


#\i IQ0mN^6O#MN0SQL> CREATE CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS  ARCHIVELOG
,IZ x2] bz7L? wO0    MAXLOGFILES 16ITPUB个人空间/n/IM5iJ+oq6j
    MAXLOGMEMBERS 3ITPUB个人空间%`"mG7@svrac,k
    MAXDATAFILES 100ITPUB个人空间J.n%O g u9X9{ rtZ;g
    MAXINSTANCES 8ITPUB个人空间!M rI b'f~6^
    MAXLOGHISTORY 292
puk%v"Z%\0LOGFILE
c$^y%H\(b8Tq7mH+v0  GROUP 1 (
V]Q7d(y.{&_T0    '/oradata/test/redo01.log',
]H_n%RV/G;n1BSB$?0    '/oradata/test/redo01b.log'ITPUB个人空间 F }QI$E4F CAw
  ) SIZE 5M,ITPUB个人空间:G5P I^*ra
  GROUP 2 (
;T8ZnQA!v YBf[0    '/oradata/test/redo02.log',
~_ ]|-P,a/}0    '/oradata/test/redo02b.log'
S2BP3D#vd`2~)O0  ) SIZE 5M,
o:tj9H.{`8g0  GROUP 3 (ITPUB个人空间B/E5y7t8Bo"_/c.?~
    '/oradata/test/redo03.log',
^#Fm'm-s1s-G|0    '/oradata/test/redo03b.log'
#hp"k5Zs|0  ) SIZE 5M,
dUR,b e8O H0  GROUP 4 '/oradata/test/redo04.log'  SIZE 10MITPUB个人空间$Se/Em)F
-- STANDBY LOGFILEITPUB个人空间0d b)l;qP+u0j
DATAFILEITPUB个人空间J&i/ft0x4i L
  '/oradata/test/system01.dbf',ITPUB个人空间xS5@/H ]0WW4kx
  '/oradata/test/undotbs01.dbf',ITPUB个人空间 pCx Fu%p
  '/oradata/test/sysaux01.dbf',
$hQ l {7f&\:YX0  '/oradata/test/users01.dbf',ITPUB个人空间!KP!Bq+q(XC n
  '/oradata/test/ecc_view.dbf'ITPUB个人空间,^/SE&M$a?9Z#Z
CHARACTER SET ZHS16GBK
$i {]Dmsrf0@G:G0;
doI{A,YmZ0Control file created.

SQL>  select status, last_time, file#, CHECKPOINT_TIME, ENABLED from v$datafile;ITPUB个人空间%d7[ ]#n6D/X-`p
STATUS  LAST_TIME      FILE# CHECKPOIN ENABLED
2iRQw] x3G:I0------- --------- ---------- --------- ----------ITPUB个人空间s"ElQQI+|)a\
SYSTEM  10-APR-08          1 10-APR-08 DISABLED
M)Pr(^#yL4[0ONLINE  10-APR-08          2 10-APR-08 DISABLEDITPUB个人空间$lXOCdO
ONLINE  10-APR-08          3 10-APR-08 DISABLEDITPUB个人空间 v1Bo8G:c
ONLINE  10-APR-08          4 10-APR-08 DISABLEDITPUB个人空间axsJ A(B\$y
ONLINE  10-APR-08          5 10-APR-08 DISABLED

SQL> alter database open;

Database altered.

SQL>  select status, last_time, file#, CHECKPOINT_TIME, ENABLED from v$datafile;
(B@/Z.CG2X_0STATUS  LAST_TIME      FILE# CHECKPOIN ENABLED
@z~1R"obhm0------- --------- ---------- --------- ----------ITPUB个人空间2{|$V Xp s
SYSTEM                     1 10-APR-08 READ WRITE
T7pU8o_#t0ONLINE                     2 10-APR-08 READ WRITEITPUB个人空间 J1x L_(G1b"u$v4st
ONLINE                     3 10-APR-08 READ WRITEITPUB个人空间+Yt]K!v(^P
ONLINE                     4 10-APR-08 READ WRITE
X lR"@^9~D`0ONLINE                     5 10-APR-08 READ WRITEITPUB个人空间,tPz4z:k%^
OFFLINE 10-APR-08          6           READ ONLY

SQL> select file_id, file_name from dba_data_files;ITPUB个人空间c K2lMJS*G
   FILE_ID FILE_NAMEITPUB个人空间2VB8Ey D g7E
---------- ---------------------------------------------------------------------         5 /oradata/test/ecc_view.dbfITPUB个人空间K['ga0z)\(w$Y2Y1s
         4 /oradata/test/users01.dbf
FTz$`7n7^bE0         3 /oradata/test/sysaux01.dbfITPUB个人空间 zXR/\5a2y/?6R@
         2 /oradata/test/undotbs01.dbf
7H}#a.TBp6b0         1 /oradata/test/system01.dbfITPUB个人空间.Tf4z9]Q
         6 /home/oracle/oracle/product/10.2.0/db_1/dbs/MISSING00006

6 rows selected.

--注意:在/home/oracle/oracle/product/10.2.0/db_1/dbs/下是没有MISSING00006这个文件存在的ITPUB个人空间^*E!D:O%^p4]
SQL> select file_name from dba_data_files;

FILE_NAME
$x!s~/DDs-X O0--------------------------------------------------------------------------------/oradata/test/ecc_view.dbfITPUB个人空间}&c9V} yCl"p
/oradata/test/users01.dbfITPUB个人空间:A'k1c f(f(B0a8}
/oradata/test/sysaux01.dbf
w~0J7jt0/oradata/test/undotbs01.dbf
v~.mYfHoj(a0/oradata/test/system01.dbf
.y&qct |2ar+I%t$ae0/home/oracle/oracle/product/10.2.0/db_1/dbs/MISSING00006

6 rows selected.

SQL> alter database rename file 'MISSING00006' to '/oradata/test/staging.dbf';

Database altered.

SQL> select file_id, file_name from dba_data_files;

   FILE_ID FILE_NAME
U T{J$w|)Q.P;Z+Ua,c0---------- ---------------------------------------------------------------------         5 /oradata/test/ecc_view.dbf
E7YN$[\([x0         4 /oradata/test/users01.dbfITPUB个人空间6G^+Gc/YDQ!c
         3 /oradata/test/sysaux01.dbfITPUB个人空间*w4F*x A,at
         2 /oradata/test/undotbs01.dbf
H$l TT"BQV1i#p#a0         1 /oradata/test/system01.dbf
2dV{DW8~H%{0         6 /oradata/test/staging.dbf

6 rows selected.ITPUB个人空间*Ohp u G;CgLC&BK
SQL>  select status, last_time, file#, CHECKPOINT_TIME, ENABLED from v$datafile;ITPUB个人空间0U7D5x8G}Ih+F
STATUS  LAST_TIME      FILE# CHECKPOIN ENABLED
Ef XCgP/`jW)k7W$N&f0------- --------- ---------- --------- ----------
Q'e_(MU(rD#pxY0SYSTEM                     1 10-APR-08 READ WRITEITPUB个人空间H7JpRJ%? hT(w
ONLINE                     2 10-APR-08 READ WRITE
:`8U9NN J0ONLINE                     3 10-APR-08 READ WRITEITPUB个人空间QcL]{9^+|
ONLINE                     4 10-APR-08 READ WRITEITPUB个人空间qGdRr!] P
ONLINE                     5 10-APR-08 READ WRITE
Rd D"]I)j&auI.H1w0OFFLINE 10-APR-08          6           READ ONLY

6 rows selected.

SQL> alter tablespace staging online;

Tablespace altered.

SQL> select status, last_time, file#, CHECKPOINT_TIME, ENABLED from v$datafile;

STATUS  LAST_TIME                FILE# CHECKPOINT_TIME     ENABLED
+m.h|#e)IV h?0------- ------------------- ---------- ------------------- ----------ITPUB个人空间(c$o9Rde6B N
SYSTEM                               1 2008-04-10 15:37:18 READ WRITE
iF.i8Ojz yP0ONLINE                               2 2008-04-10 15:37:18 READ WRITEITPUB个人空间#o%Z3vly2pc
ONLINE                               3 2008-04-10 15:37:18 READ WRITEITPUB个人空间#{e%y-h} v
ONLINE                               4 2008-04-10 15:37:18 READ WRITEITPUB个人空间*my*Z"YTbS-q|T+C r[
ONLINE                               5 2008-04-10 15:37:18 READ WRITEITPUB个人空间9~,ALt0R9g)v
ONLINE  2008-04-10 15:20:30          6                     READ ONLY

6 rows selected.

SQL> alter tablespace staging read write;

Tablespace altered.

SQL>  select status, last_time, file#, CHECKPOINT_TIME, ENABLED from v$datafile;ITPUB个人空间7mgnl@Z,?
STATUS  LAST_TIME                FILE# CHECKPOINT_TIME     ENABLED
qk&n~zqK*{0------- ------------------- ---------- ------------------- ----------
eZ)SDb2Q B&^6Zr0SYSTEM                               1 2008-04-10 16:06:20 READ WRITEITPUB个人空间1[5Cm&F2R|C~]1B@
ONLINE                               2 2008-04-10 16:06:20 READ WRITE
:B ?5fBO0jc u+H;W0ONLINE                               3 2008-04-10 16:06:20 READ WRITEITPUB个人空间"Y(M&Q7IN O|0H*u
ONLINE                               4 2008-04-10 16:06:20 READ WRITE
:l(c.O'J'w#G*}0ONLINE                               5 2008-04-10 16:06:20 READ WRITEITPUB个人空间)k%Z6SgV.}+M
ONLINE                               6 2008-04-10 16:10:51 READ WRITE

6 rows selected.
Wir-IQVO9N"`)\0


TAG:

heming96的个人空间 引用 删除 heming96   /   2008-08-22 10:53:29
oracle 就是有这个问题 rman恢复数据库的时候,如果有readonly 表空间也会出这种问题
 

评分:0

我来说两句

显示全部

:loveliness: :handshake :victory: :funk: :time: :kiss: :call: :hug: :lol :'( :Q :L ;P :$ :P :o :@ :D :( :)

Open Toolbar