ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > Linux操作系统 > Linux操作系统 > oracle实验记录 （恢复-完全恢复）

oracle实验记录 （恢复-完全恢复）

原创 Linux操作系统 作者：fufuh2o 时间：2009-08-20 16:37:38 0 删除 编辑

恢复的基础操作
先说完全恢复:从备份时候的SCN 应用到CURRENT REDOLOG的最后一个SCN
recover database(不能OPEN恢复)
recover tablespace
recover datafile

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     84
Next log sequence to archive   87
Current log sequence           87

SQL> select tablespace_name ,file_name from dba_data_files;

TABLESPACE_NAME                FILE_NAME
------------------------------ ------------------------------
EXAMPLE                        E:\ORACLE\PRODUCT\10.1.0\ORADA
                               TA\ORCL\EXAMPLE01.DBF

USERS                          E:\ORACLE\PRODUCT\10.1.0\ORADA
                               TA\ORCL\USERS01.DBF

SYSAUX                         E:\ORACLE\PRODUCT\10.1.0\ORADA
                               TA\ORCL\SYSAUX01.DBF

UNDOTBS1                       E:\ORACLE\PRODUCT\10.1.0\ORADA
                               TA\ORCL\UNDOTBS01.DBF

TABLESPACE_NAME                FILE_NAME
------------------------------ ------------------------------

SYSTEM                         E:\ORACLE\PRODUCT\10.1.0\ORADA
                               TA\ORCL\SYSTEM01.DBF

TEST                           D:\TEST.DBF

6 rows selected.

SQL> alter tablespace test begin backup;

Tablespace altered.

SQL> host copy d:\test.dbf  e:\test.dbf
已复制         1 个文件。

SQL> alter tablespace test end backup;

Tablespace altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> host del d:\test.dbf


SQL> startup
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 10 - see DBWR trace file
ORA-01110: data file 10: 'D:\TEST.DBF'~~~~~~~~~~~~~~~

SQL> col error format a30
SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_ ERROR                             CHANGE#
---------- ------- ------- ------------------------------ ----------
TIME
--------------
        10 ONLINE  ONLINE  FILE NOT FOUND                          0

SQL> select file#,status,recover from v$datafile_header;

     FILE# STATUS  REC
---------- ------- ---
         1 ONLINE  NO
         2 ONLINE  NO
         3 ONLINE  NO
         4 ONLINE  NO
         5 ONLINE  NO
        10 ONLINE         File needs media recovery (YES | NO)

SQL> host copy e:\test.dbf  d:\test.dbf;
已复制         1 个文件。

SQL> select file#,status,recover from v$datafile_header;

     FILE# STATUS  REC
---------- ------- ---
         1 ONLINE  NO
         2 ONLINE  NO
         3 ONLINE  NO
         4 ONLINE  NO
         5 ONLINE  NO
        10 ONLINE  YES~~~~~~~~~~~~~~~~~~~~~~~~~要恢复

6 rows selected.

SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_ ERROR                             CHANGE#
---------- ------- ------- ------------------------------ ----------
TIME
--------------
        10 ONLINE  ONLINE                                    7615801~~~~从这开始应用redo
19-8月 -09


SQL> recover tablespace test;*************************
Media recovery complete.
SQL> select file#,status,recover from v$datafile_header;~~~~~~~~~~~~~~~

     FILE# STATUS  REC
---------- ------- ---
         1 ONLINE  NO
         2 ONLINE  NO
         3 ONLINE  NO
         4 ONLINE  NO
         5 ONLINE  NO
        10 ONLINE  NO

6 rows selected.

SQL> select * from v$recover_file;~~~~~~~~~~~~~~~~~~~~~
no rows selected
SQL> alter database open;~~~~~~~~~~~~~~~~~~~

Database altered.
SQL> select tablespace_name ,file_name,status from dba_data_files;

TABLESPACE_NAME                FILE_NAME                      STATUS
------------------------------ ------------------------------ ---------
EXAMPLE                        E:\ORACLE\PRODUCT\10.1.0\ORADA AVAILABLE
                               TA\ORCL\EXAMPLE01.DBF

USERS                          E:\ORACLE\PRODUCT\10.1.0\ORADA AVAILABLE
                               TA\ORCL\USERS01.DBF

SYSAUX                         E:\ORACLE\PRODUCT\10.1.0\ORADA AVAILABLE
                               TA\ORCL\SYSAUX01.DBF

UNDOTBS1                       E:\ORACLE\PRODUCT\10.1.0\ORADA AVAILABLE
                               TA\ORCL\UNDOTBS01.DBF

TABLESPACE_NAME                FILE_NAME                      STATUS
------------------------------ ------------------------------ ---------

SYSTEM                         E:\ORACLE\PRODUCT\10.1.0\ORADA AVAILABLE
                               TA\ORCL\SYSTEM01.DBF

TEST                           D:\TEST.DBF                    AVAILABLE

6 rows selected.

~~~~~~~~~~~~~~~~~~~~~~~~~~非常简单的恢复
重新指定新位置
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> host del d:\test.dbf

SQL> startup
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 10 - see DBWR trace file
ORA-01110: data file 10: 'D:\TEST.DBF'


SQL> alter database datafile 10 offline;~~~~~~~~~~~offline后就不检查了 就可以OPEN了

Database altered.

SQL> alter database open;

Database altered.

SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_ ERROR                             CHANGE#
---------- ------- ------- ------------------------------ ----------
TIME
--------------
        10 OFFLINE OFFLINE FILE NOT FOUND                          0

 


SQL> alter database rename file 'd:\test.dbf' to 'e:\test.dbf';~~~~若原来位置用不了了 放入

新位置 先改controlfile(正好备份在E:\TEST.DBF)

Database altered.

SQL> reocver tablespace test;


~~~~~~~~~~~~~~~~~~~~
SQL> col file_name format a30
SQL>  select tablespace_name ,file_name from dba_data_files;

TABLESPACE_NAME                FILE_NAME
------------------------------ ------------------------------
EXAMPLE                        E:\ORACLE\PRODUCT\10.1.0\ORADA
                               TA\ORCL\EXAMPLE01.DBF

USERS                          E:\ORACLE\PRODUCT\10.1.0\ORADA
                               TA\ORCL\USERS01.DBF

SYSAUX                         E:\ORACLE\PRODUCT\10.1.0\ORADA
                               TA\ORCL\SYSAUX01.DBF

UNDOTBS1                       E:\ORACLE\PRODUCT\10.1.0\ORADA
                               TA\ORCL\UNDOTBS01.DBF

TABLESPACE_NAME                FILE_NAME
------------------------------ ------------------------------

SYSTEM                         E:\ORACLE\PRODUCT\10.1.0\ORADA
                               TA\ORCL\SYSTEM01.DBF

TEST                           E:\TEST.DBF

6 rows selected.   恢复database(MOUNT下) 与恢复datafile与此类似


使用rman来恢复
RMAN> backup tablespace "TEST"
2> ;

Starting backup at 20-8月 -09
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00010 name=E:\TEST.DBF
channel ORA_DISK_1: starting piece 1 at 20-8月 -09
channel ORA_DISK_1: finished piece 1 at 20-8月 -09
piece handle=E:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\XHTEST\BACKUPSET\2009_
08_20\O1_MF_NNNDF_TAG20090820T163233_58T2K1OH_.BKP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 20-8月 -09

RMAN> list backup of tablespace 'TEST';
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
32      Full    304K       DISK        00:00:03     20-8月 -09
        BP Key: 34   Status: AVAILABLE  Compressed: NO  Tag: TAG20090820T163233
        Piece Name: E:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\XHTEST\BACKUPSE
T\2009_08_20\O1_MF_NNNDF_TAG20090820T163233_58T2K1OH_.BKP

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> host del e:\test.dbf;

SQL> startup
ORACLE instance started.
RMAN> run{restore tablespace 'TEST';~~~~~~~~~~~~~~~~~~~~
2> recover tablespace 'TEST';};~~~~~~~~~~~~

Starting restore at 20-8月 -09
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=142 devtype=DISK

channel ORA_DISK_1: restoring datafile 00010
input datafilecopy recid=4 stamp=695388316 filename=D:\TEST.DBF
destination for restore of datafile 00010: E:\TEST.DBF
channel ORA_DISK_1: copied datafilecopy of datafile 00010
output filename=E:\TEST.DBF recid=5 stamp=695407018
Finished restore at 20-8月 -09

Starting recover at 20-8月 -09
using channel ORA_DISK_1

starting media recovery
media recovery complete~~~~~~~~~~~~~~~~~~~~~~~~~


SQL>  select tablespace_name ,file_name from dba_data_files;

TABLESPACE_NAME                FILE_NAME
------------------------------ ------------------------------
EXAMPLE                        E:\ORACLE\PRODUCT\10.1.0\ORADA
                               TA\ORCL\EXAMPLE01.DBF

USERS                          E:\ORACLE\PRODUCT\10.1.0\ORADA
                               TA\ORCL\USERS01.DBF

SYSAUX                         E:\ORACLE\PRODUCT\10.1.0\ORADA
                               TA\ORCL\SYSAUX01.DBF

UNDOTBS1                       E:\ORACLE\PRODUCT\10.1.0\ORADA
                               TA\ORCL\UNDOTBS01.DBF

TABLESPACE_NAME                FILE_NAME
------------------------------ ------------------------------

SYSTEM                         E:\ORACLE\PRODUCT\10.1.0\ORADA
                               TA\ORCL\SYSTEM01.DBF

TEST                           E:\TEST.DBF

6 rows selected.


如果原先位置不能用的话


SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> host del e:\test.dbf;

SQL> startup
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 10 - see DBWR trace file
ORA-01110: data file 10: 'E:\TEST.DBF'

RMAN> run{set newname for datafile 'E:\TEST.DBF' to 'D:\TEST.DBF';~~~~该restore到的位置
2> restore tablespace 'TEST';~~~~~~~~restore
3> switch datafile all;~~~~~~~~~~改controlfile
4> recover tablespace 'TEST';~~~~~~~~~~~~~~~~~~~~~恢复
5> sql 'alter database datafile 10 online';};
Finished recover at 20-8月 -09
SQL> alter database open;

Database altered.

 

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/12020513/viewspace-612770/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： oracle实验记录Rman duplicate database 2
下一篇： oracle实验记录（恢复-丢失未备份数据文件）
请登录后发表评论 登录
全部评论
fufuh2o

注册时间：2009-06-26

  • 博文量
    182
  • 访问量
    471655

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593