oracle备份,恢复基础

备份分类:

1.完全脱机备份(关掉数据库拷贝文件)

shutdown immediate;

os copy;

2.部分脱机备份(offline表空间 但system,undo表空间不能offline)

alter tablespace users offline;

os copy;

3.部分联机备份

alter tablespace system begin backup;

os copy;

alter tablespace system end backup;

alter database begin backup;

alter database end backup;

select  * from v$backup;查询哪些表空间处于联机备份模式

4:rman备份

恢复基础:

1.完全恢复

2.不完全恢复

恢复的起点:select file#,checkpoint_change# from v$datafile_header; 来至数据头文件

恢复的终点:select file#,checkpoint_change# from v$datafile;来只控制文件

模拟日志文件丢的恢复:

1：非当前日志文件丢失，就新建一组日志文件就可以了.

       alter database drop logfile group 2;从控制文件中删除日志的记录信息

       alter database add logfile group 2 '/u01/app/oracle/oradata/orc1/redo02.log' size 50m;添加一组新的日志文件

2：当前日志文件丢失的恢复

      SQL> select * from v$log;

      GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1          2   52428800          1 YES INACTIVE                364409 18-MAY-13
         2          1          3   52428800          1 YES INACTIVE                364414 18-MAY-13
         3          1          4   52428800          1 NO  CURRENT                 364420 18-MAY-13

     由上可以看出第3组是当前日志文件然后操作系统删除第3组日志文件

     [root@localhost orc1]# rm redo03.log
     rm: remove regular file `redo03.log'? y   然后shutdown immediate数据库 ，启动的时候会报错：

      SQL> startup
ORACLE instance started.

Total System Global Area 1224736768 bytes
Fixed Size                  2020384 bytes
Variable Size             301992928 bytes
Database Buffers          905969664 bytes
Redo Buffers               14753792 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orc1/redo03.log'

SQL> select status from v$instance;
STATUS
------------
MOUNTED

SQL> recover database until cancel;  做不完全恢复;
Media recovery complete.

SQL> alter database open resetlogs; 打开数据库的时候需要resetlogs，会自动生存日志文件的。
Database altered.

附加：

alter database add logfile member '/app/oracle/oradata/TEST/redo03_3.log' to  group 3;增加镜像日志

alter database drop logfile member '/app/oracle/oradata/TEST/redo03_3.log';删除镜像日志

2：模拟控制文件丢失的恢复

首先备份下控制文件：

SQL> alter database backup controlfile to trace as '/home/oracle/con.bak';
Database altered.
然后删除3个控制文件

[root@localhost orc1]# ll cont*
-rw-r----- 1 oracle oinstall 7192576 May 18 23:12 control01.ctl
-rw-r----- 1 oracle oinstall 7192576 May 18 23:12 control02.ctl
-rw-r----- 1 oracle oinstall 7192576 May 18 23:12 control03.ctl
[root@localhost orc1]# rm cont* -rf

然后shutdown abort关闭oracle，启动的时候就会报错：

SQL> startup
ORACLE instance started.

Total System Global Area 1224736768 bytes
Fixed Size                  2020384 bytes
Variable Size             301992928 bytes
Database Buffers          905969664 bytes
Redo Buffers               14753792 bytes
ORA-00205: error in identifying control file, check alert log for more info

然后根据备份的控制文件重建控制文件的代码进行重建控制文件，有2个一种不需要resetlogs，一种需要resetlogs的。

 SQL> CREATE CONTROLFILE REUSE DATABASE "ORC1" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/orc1/redo01.log'  SIZE 50M,
  GROUP 2 '/u01/app/oracle/oradata/orc1/redo02.log'  SIZE 50M,
  GROUP 3 '/u01/app/oracle/oradata/orc1/redo03.log'  SIZE 50M
DATAFILE
  '/u01/app/oracle/oradata/orc1/system01.dbf',
  '/u01/app/oracle/oradata/orc1/undotbs01.dbf',
  '/u01/app/oracle/oradata/orc1/sysaux01.dbf',
  '/u01/app/oracle/oradata/orc1/users01.dbf',
  '/u01/app/oracle/oradata/orc1/test.dbf',
  '/u01/app/oracle/oradata/orc1/test11.dbf'
CHARACTER SET WE8ISO8859P1
;

Control file created.

SQL> alter database open resetlogs;因为上面重建的代码是选择的需要resetlogs.

Database altered.


如果控制控制文件不一致，启动数据库的时候会报错：

SQL> startup
ORACLE instance started.

Total System Global Area 1224736768 bytes
Fixed Size                  2020384 bytes
Variable Size             301992928 bytes
Database Buffers          905969664 bytes
Redo Buffers               14753792 bytes
ORA-00214: control file '/u01/app/oracle/oradata/orc1/control02.ctl' version 1440 inconsistent with file '/u01/app/oracle/oradata/orc1/control01.ctl'
version 1432

这种情况处理如下：

alter system set control_files='/u01/app/oracle/oradata/orc1/control03.ctl' scope=spfile;

然后关闭数据库，在启动就ok了。