只读表空间的备份与恢复

SQL> prompt readonly-readonly
备份状态readonly-破坏状态readonly
SQL> alter tablespace abctest read only;


Tablespace altered.


SQL> ho cp /oradata/ORCL/datafile/test_01.dbf /tmp/test_01.dbf


SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@11g ~]$ vi /oradata/ORCL/datafile/test_01.dbf
修改内容 
[oracle@11g ~]$ sqlplus / as sysdba


SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 13 07:21:45 2017


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




Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


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


Total System Global Area 1252663296 bytes
Fixed Size                  2252824 bytes
Variable Size             956305384 bytes
Database Buffers          285212672 bytes
Redo Buffers                8892416 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/oradata/ORCL/datafile/test_01.dbf'




SQL> ho cp /tmp/test_01.dbf /oradata/ORCL/datafile/test_01.dbf


SQL> alter database open;


Database altered.


SQL> select * from test.worker;


WNO       WNAME
--------- --------------------
001       adam


SQL> prompt readonly-readwrite
备份状态readonly-破坏状态readwrite
SQL> alter tablespace abctest read write;


Tablespace altered.


SQL> insert into test.worker values('002','eva');


1 row created.


SQL> 
SQL> commit;


Commit complete.


SQL> select * from test.worker;


WNO       WNAME
--------- --------------------
001       adam
002       eva


SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 
[oracle@11g ~]$ sqlplus / as sysdba


SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 13 07:52:11 2017


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




Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> insert into test.worker values('003','sakiel');


1 row created.


SQL> commit;


Commit complete.


SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@11g ~]$ vi /oradata/ORCL/datafile/test_01.dbf
修改内容 
[oracle@11g ~]$ sqlplus / as sysdba


SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 13 07:54:18 2017


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




Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> shutdown immediate
ORA-01122: database file 5 failed verification check
ORA-01110: data file 5: '/oradata/ORCL/datafile/test_01.dbf'
ORA-01208: data file is an old version - not accessing current version
SQL> ho cp /tmp/test_01.dbf /oradata/ORCL/datafile/test_01.dbf


SQL> select * from test.worker;


WNO       WNAME
--------- --------------------
001       adam
002       eva
003       sakiel


SQL> select * from v$recover_file;


no rows selected


SQL> shutdown immediate
ORA-01122: database file 5 failed verification check
ORA-01110: data file 5: '/oradata/ORCL/datafile/test_01.dbf'
ORA-01208: data file is an old version - not accessing current version
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.


Total System Global Area 1252663296 bytes
Fixed Size                  2252824 bytes
Variable Size             956305384 bytes
Database Buffers          285212672 bytes
Redo Buffers                8892416 bytes
Database mounted.
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/oradata/ORCL/datafile/test_01.dbf'




SQL> recover datafile 5;
Media recovery complete.
SQL> alter tablespace abctest online;
alter tablespace abctest online
*
ERROR at line 1:
ORA-01109: database not open




SQL> alter database open;


Database altered.


SQL> select * from v$recover_file;


no rows selected


SQL> select * from test.worker;


WNO       WNAME
--------- --------------------
001       adam
002       eva
003       sakiel


SQL> prompt readwrite-readonly
备份状态readwrite-破坏状态readonly
SQL> alter tablespace abctest begin backup;


Tablespace altered.


SQL> ho cp /oradata/ORCL/datafile/test_01.dbf /tmp/test_01.dbf


SQL> alter tablespace abctest end backup;


Tablespace altered.


SQL> delete from test.worker where wno=001;


1 row deleted.


SQL> commit;


Commit complete.


SQL> alter tablespace abctest read only;


Tablespace altered.


SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@11g ~]$ vi /oradata/ORCL/datafile/test_01.dbf
修改内容 
[oracle@11g ~]$ sqlplus / as sysdba


SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 13 08:13:46 2017


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




Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


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


Total System Global Area 1252663296 bytes
Fixed Size                  2252824 bytes
Variable Size             956305384 bytes
Database Buffers          285212672 bytes
Redo Buffers                8892416 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/oradata/ORCL/datafile/test_01.dbf'




SQL> ho cp /tmp/test_01.dbf /oradata/ORCL/datafile/test_01.dbf


SQL> recover datafile 5;
Media recovery complete.
SQL> alter database open;


Database altered.


SQL> select * from test.worker;


WNO       WNAME
--------- --------------------
002       eva
003       sakiel


无备份情况
SQL> select file#,name,enabled from v$datafile where file#=5;


     FILE#
----------
NAME
--------------------------------------------------------------------------------
ENABLED
----------
         5
/oradata/ORCL/datafile/test_01.dbf
READ ONLY




SQL> alter tablespace abctest read write;


Tablespace altered.


SQL> drop table test.worker;


Table dropped.


SQL> commit;


Commit complete.


SQL> alter tablespace abctest read only;


Tablespace altered.


SQL> alter tablespace abctest read write;


Tablespace altered.


SQL> CREATE TABLE test.laucher
  2  (Wno CHAR(9) PRIMARY KEY,
  3  Wname CHAR(20) NOT NULL);


insert into test.laucher values('003','sakiel');


Table created.


SQL> 
SQL> 
1 row created.


SQL> SQL> commit;


Commit complete.


SQL> alter system checkpoint;


System altered.


SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@11g ~]$ vi /oradata/ORCL/datafile/test_01.dbf
修改内容
[oracle@11g ~]$ sqlplus / as sysdba


SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 13 08:28:21 2017


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




Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> alter system checkpoint; 


System altered.


SQL> select * from v$recover_file; 


no rows selected


SQL> shutdown immediate
ORA-01122: database file 5 failed verification check
ORA-01110: data file 5: '/oradata/ORCL/datafile/test_01.dbf'
ORA-01208: data file is an old version - not accessing current version
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.


Total System Global Area 1252663296 bytes
Fixed Size                  2252824 bytes
Variable Size             956305384 bytes
Database Buffers          285212672 bytes
Redo Buffers                8892416 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/oradata/ORCL/datafile/test_01.dbf'




SQL> alter database create datafile 5;


Database altered.


SQL> recover datafile 5;
Media recovery complete.
SQL> alter database open;


Database altered.


SQL> select file#,name,status from v$datafile where file#=5;


     FILE#
----------
NAME
--------------------------------------------------------------------------------
STATUS
-------
         5
/oradata/ORCL/datafile/test_01.dbf
ONLINE




SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options