备份只读表空间后切换成读写表空间的恢复(实验系列)

SQL> conn /as sysdba
已连接。
SQL> drop table t1;

表已删除。

SQL> commit;

提交完成。

SQL> create table t1 tablespace users as select 'read write' status from dual;

表已创建。

SQL> alter tablespace users read only;

表空间已更改。

SQL> !
SP2-0042: 未知命令 "!" - 其余行忽略。
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM03.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM04.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\RMAN_TS.DBF

已选择8行。

SQL> host copy E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF E:\ORACLE\PRODUCT\10.2.0\ORADATA\USERS01.DBF
已复制         1 个文件。

SQL> alter system swtich logfile;
alter system swtich logfile
             *
第 1 行出现错误:
ORA-02065: 非法的 ALTER SYSTEM 选项


SQL> alter system switch logfile;

系统已更改。

SQL> /

系统已更改。

SQL> /

系统已更改。

SQL> alter system checkpoint;

系统已更改。

SQL> set linesize 150
SQL> col name for a50
SQL> select name, status,checkpoint_change#,last_change# from v$datafile;

NAME                                               STATUS  CHECKPOINT_CHANGE# LAST_CHANGE#
-------------------------------------------------- ------- ------------------ ------------
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF SYSTEM             1151943
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DB ONLINE             1151943
F

E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF ONLINE             1151943
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF  ONLINE             1151859      1151859
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DB ONLINE             1151943
F

E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM03.DBF SYSTEM             1151943
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM04.DBF SYSTEM             1151943

NAME                                               STATUS  CHECKPOINT_CHANGE# LAST_CHANGE#
-------------------------------------------------- ------- ------------------ ------------
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\RMAN_TS.DBF  ONLINE             1151943

已选择8行。

SQL> select name, recover, fuzzy, checkpoint_change# from v$datafile_header;

NAME                                               REC FUZ CHECKPOINT_CHANGE#
-------------------------------------------------- --- --- ------------------
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF NO  YES            1151943
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DB NO  YES            1151943
F

E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF NO  YES            1151943
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF  NO  NO             1151859
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DB NO  YES            1151943
F

E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM03.DBF NO  YES            1151943
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM04.DBF NO  YES            1151943

NAME                                               REC FUZ CHECKPOINT_CHANGE#
-------------------------------------------------- --- --- ------------------
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\RMAN_TS.DBF  NO  YES            1151943

已选择8行。

SQL> alter tablespace users read write;

表空间已更改。

SQL> select name, recover, fuzzy, checkpoint_change# from v$datafile_header;

NAME                                               REC FUZ CHECKPOINT_CHANGE#
-------------------------------------------------- --- --- ------------------
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF NO  YES            1151943
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DB NO  YES            1151943
F

E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF NO  YES            1151943
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF  NO  YES            1151981
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DB NO  YES            1151943
F

E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM03.DBF NO  YES            1151943
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM04.DBF NO  YES            1151943

NAME                                               REC FUZ CHECKPOINT_CHANGE#
-------------------------------------------------- --- --- ------------------
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\RMAN_TS.DBF  NO  YES            1151943

已选择8行。

SQL> shutdown abort;
ORACLE 例程已经关闭。
SQL> host del E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
另一个程序正在使用此文件，进程无法访问。

SQL> exit
从 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options 断开

C:\Documents and Settings\xixi.song>del E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF

C:\Documents and Settings\xixi.song>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 11月 25 15:23:35 2011

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

SQL> conn /as sysdba
已连接到空闲例程。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area  591396864 bytes
Fixed Size                  1250308 bytes
Variable Size             218106876 bytes
Database Buffers          364904448 bytes
Redo Buffers                7135232 bytes
数据库装载完毕。
ORA-01157: 无法标识/锁定数据文件 4 - 请参阅 DBWR 跟踪文件
ORA-01110: 数据文件 4: 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF'


SQL> host copy E:\ORACLE\PRODUCT\10.2.0\ORADATA\USERS01.DBF E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
已复制         1 个文件。

SQL> set linesize 150
SQL> col name for a50
SQL> select name, recover, fuzzy, checkpoint_change# from v$datafile_header;

NAME                                               REC FUZ CHECKPOINT_CHANGE#
-------------------------------------------------- --- --- ------------------
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF NO  YES            1151943
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DB NO  YES            1151943
F

E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF NO  YES            1151943
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF  YES NO             1151859
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DB NO  YES            1151943
F

E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM03.DBF NO  YES            1151943
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM04.DBF NO  YES            1151943

NAME                                               REC FUZ CHECKPOINT_CHANGE#
-------------------------------------------------- --- --- ------------------
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\RMAN_TS.DBF  NO  YES            1151943

已选择8行。

SQL> select hxfil FILENUMBER,fhsta STATUS,fhscn SCN,fhrba_Seq SEQUENCE from x$kcvfh;

FILENUMBER     STATUS SCN                SEQUENCE
---------- ---------- ---------------- ----------
         1       8196 1151943                  13
         2          4 1151943                  13
         3          4 1151943                  13
         4          0 1151859                  10
         5          4 1151943                  13
         6          4 1151943                  13
         7          4 1151943                  13
         8          4 1151943                  13

已选择8行。

SQL> SELECT GROUP#,BYTES,ARCHIVED,FIRST_CHANGE#,SEQUENCE#,STATUS FROM V$LOG;

    GROUP#      BYTES ARC FIRST_CHANGE#  SEQUENCE# STATUS
---------- ---------- --- ------------- ---------- ----------------
         1   52428800 NO        1151929         13 CURRENT
         3   52428800 YES       1151919         12 INACTIVE
         2   52428800 YES       1151917         11 INACTIVE

SQL> recover database;
完成介质恢复。
SQL> alter database open;

数据库已更改。

SQL> select tablespace_name , status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
UNDOTBS1                       ONLINE
SYSAUX                         ONLINE
TEMP                           ONLINE
USERS                          ONLINE
EXAMPLE                        ONLINE
RMAN_TS                        ONLINE

已选择7行。

SQL>