oracle11g数据库 冷备份与冷恢复

我们在做备份时，究竟需要备份数据库的哪些文件呢？

其实只需要备份数据文件和控制文件就可以了，其他的参数文件，重做日志文件以及口令文件与数据文件相比都非常小，所以在一般情况下都会一起备份。

冷备份步骤：

1.v$contorlfile 找到所有的控制文件

2.dba_data_files 找到所有的数据文件

3.v$logfile找到所有的日志文件

4.v$tempfiles 和v$tablespace找到所有的临时文件以及与表空间的对应关系

5.正常关闭数据库

6.将所有的文件复制到硬盘或者磁带上

7.重新启动数据库

冷恢复：

1.正常关闭数据库

2.将备份的所有文件复制到原来的位置

3.重新启动数据库

实验：

1.首先找到要备份的文件的位置

SQL> select file_name,file_id,tablespace_name,bytes,blocks,status from dba_data_files;FILE_NAME                          FILE_ID TABLESPACE_NAME              BYTES     BLOCKS STATUS
-------------------------------------------------- ---------- ------------------------------ ---------- ---------- ---------
/u01/app/oracle/oradata/cai/users01.dbf             4 USERS                   62914560       7680 AVAILABLE
/u01/app/oracle/oradata/cai/undotbs01.dbf            3 UNDOTBS1                  110100480      13440 AVAILABLE
/u01/app/oracle/oradata/cai/sysaux01.dbf            2 SYSAUX                  587202560      71680 AVAILABLE
/u01/app/oracle/oradata/cai/system01.dbf            1 SYSTEM                  828375040     101120 AVAILABLE
/u01/app/oracle/oradata/cai/example01.dbf            5 EXAMPLE                  104857600      12800 AVAILABLESQL> select status,name from v$controlfile;
SQL> col name for a60;
SQL> /STATUS    NAME
------- ------------------------------------------------------------/u01/app/oracle/oradata/cai/control01.ctl/u01/app/oracle/flash_recovery_area/cai/control02.ctlSQL> select group#,status,member from v$logfile;
SQL> col member for a30
SQL> /GROUP# STATUS  MEMBER
---------- ------- ------------------------------3       /u01/app/oracle/oradata/cai/redo03.log2       /u01/app/oracle/oradata/cai/redo02.log1       /u01/app/oracle/oradata/cai/redo01.logSQL> show parameter pfile;NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
spfile                     string     /u01/app/oracle/product/11.2.0/db_home1/dbs/spfilecai.oraSQL> select name from v$tempfile;NAME
------------------------------------------------------------
/u01/app/oracle/oradata/cai/temp01.dbf

2.创建一张测试表

SQL> create user nice identified by oracle2  default tablespace example3  quota 1m on example;User created.SQL> grant create table,connect ,dba to nice;
\
Grant succeeded.SQL>conn nice/oracle                       
Connected.
SQL> show user
USER is "NICE"SQL> create table test(id number);Table created.SQL> insert into test values(1);
insert into test values(1)*
ERROR at line 1:
ORA-01647: tablespace 'EXAMPLE' is read-only, cannot allocate space in itSQL> alter user nice default tablespace users quota 1m on users;User altered.SQL> drop table test ;Table dropped.SQL> create table test(id number);Table created.SQL> insert into test values(1);
insert into test values(1)*
ERROR at line 1:
ORA-01647: tablespace 'USERS' is read-only, cannot allocate space in itSQL> select tablespace_name,status from dba_tablespaces;TABLESPACE_NAME            STATUS
------------------------------ ---------
SYSTEM                   ONLINE
SYSAUX                   ONLINE
UNDOTBS1               ONLINE
TEMP                   ONLINE
USERS                   READ ONLY
EXAMPLE                READ ONLY6 rows selected.SQL> alter tablespace users read write;Tablespace altered.SQL> insert into test values(1);1 row created.SQL> select * from test;ID
----------1SQL> conn sys/oracle as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

3.复制要备份的文件到备份目录

[oracle@11g ~]$ cd /u01/app/oracle/product/11.2.0/db_home1/dbs/
[oracle@11g dbs]$ ll -ls
total 28
4 -rw-rw---- 1 oracle oinstall 1544 Feb 29 13:29 hc_cai.dat
4 -rw-rw---- 1 oracle oinstall 1544 Jan  8 13:15 hc_DBUA0.dat
4 -rw-r--r-- 1 oracle oinstall  962 Feb 29 12:26 initcai.ora
4 -rw-r--r-- 1 oracle oinstall 2851 May 15  2009 init.ora
4 -rw-r----- 1 oracle oinstall   24 Jan  8 13:18 lkCAI
4 -rw-r----- 1 oracle oinstall 1536 Jan  8 13:21 orapwcai
4 -rw-r----- 1 oracle oinstall 2560 Feb 29 13:24 spfilecai.ora
[oracle@11g dbs]$ scp initcai.ora /home/oracle/backup
[oracle@11g dbs]$ scp spfilecai.ora /home/oracle/backup
[oracle@11g dbs]$ cd /u01/app/oracle/oradata/cai/
[oracle@11g cai]$ ll -ls
total 18222249552 -rw-r----- 1 oracle oinstall   9781248 Feb 29 13:29 control01.ctl
102408 -rw-r----- 1 oracle oinstall 104865792 Jan 14 17:35 example01.dbf51204 -rw-r----- 1 oracle oinstall  52429312 Feb 29 13:29 redo01.log51204 -rw-r----- 1 oracle oinstall  52429312 Feb 29 13:26 redo02.log51204 -rw-r----- 1 oracle oinstall  52429312 Feb 29 13:26 redo03.log
573448 -rw-r----- 1 oracle oinstall 587210752 Feb 29 13:29 sysaux01.dbf
808968 -rw-r----- 1 oracle oinstall 828383232 Feb 29 13:29 system01.dbf5260 -rw-r----- 1 oracle oinstall  38805504 Feb 29 12:02 temp01.dbf
107528 -rw-r----- 1 oracle oinstall 110108672 Feb 29 13:29 undotbs01.dbf61448 -rw-r----- 1 oracle oinstall  62922752 Feb 29 13:29 users01.dbf
[oracle@11g cai]$ scp * /home/oracle/backup
[oracle@11g cai]$ cd /home/oracle/backup
[oracle@11g backup]$ ll -ls
total 18242209552 -rw-r----- 1 oracle oinstall   9781248 Feb 29 13:30 control01.ctl
102408 -rw-r----- 1 oracle oinstall 104865792 Feb 29 13:30 example01.dbf4 -rw-r--r-- 1 oracle oinstall       962 Feb 29 13:30 initcai.ora51204 -rw-r----- 1 oracle oinstall  52429312 Feb 29 13:30 redo01.log51204 -rw-r----- 1 oracle oinstall  52429312 Feb 29 13:30 redo02.log51204 -rw-r----- 1 oracle oinstall  52429312 Feb 29 13:30 redo03.log4 -rw-r----- 1 oracle oinstall      2560 Feb 29 13:30 spfilecai.ora
573448 -rw-r----- 1 oracle oinstall 587210752 Feb 29 13:30 sysaux01.dbf
808968 -rw-r----- 1 oracle oinstall 828383232 Feb 29 13:30 system01.dbf7248 -rw-r----- 1 oracle oinstall  38805504 Feb 29 13:30 temp01.dbf
107528 -rw-r----- 1 oracle oinstall 110108672 Feb 29 13:30 undotbs01.dbf61448 -rw-r----- 1 oracle oinstall  62922752 Feb 29 13:30 users01.dbf

3.模拟数据丢失，truncate table test

SQL> startup
ORACLE instance started.Total System Global Area  885211136 bytes
Fixed Size		    2218432 bytes
Variable Size		  369100352 bytes
Database Buffers	  507510784 bytes
Redo Buffers		    6381568 bytes
Database mounted.
Database opened.
SQL> select * from test;ID
----------1SQL> truncate table test;Table truncated.SQL> select * from test;no rows selected

4.将备份的文件复制回原来的位置

我这里做了一个不一样的实验，首先我只将表所在的数据文件恢复到原来的位置，启动数据库后发现数据表中还是没有数据

[oracle@11g backup]$ ll -ls
total 18242129552 -rw-r----- 1 oracle oinstall   9781248 Feb 29 15:35 control01.ctl
102408 -rw-r----- 1 oracle oinstall 104865792 Feb 29 15:35 example01.dbf51204 -rw-r----- 1 oracle oinstall  52429312 Feb 29 15:35 redo01.log51204 -rw-r----- 1 oracle oinstall  52429312 Feb 29 15:35 redo02.log51204 -rw-r----- 1 oracle oinstall  52429312 Feb 29 15:35 redo03.log
573448 -rw-r----- 1 oracle oinstall 587210752 Feb 29 15:35 sysaux01.dbf
808968 -rw-r----- 1 oracle oinstall 828383232 Feb 29 15:35 system01.dbf7248 -rw-r----- 1 oracle oinstall  38805504 Feb 29 15:35 temp01.dbf
107528 -rw-r----- 1 oracle oinstall 110108672 Feb 29 15:35 undotbs01.dbf61448 -rw-r----- 1 oracle oinstall  62922752 Feb 29 15:35 users01.dbf
[oracle@11g backup]$ scp users01.dbf /u01/app/oracle/oradata/cai

SQL> startup;
ORACLE instance started.Total System Global Area  885211136 bytes
Fixed Size		    2218432 bytes
Variable Size		  369100352 bytes
Database Buffers	  507510784 bytes
Redo Buffers		    6381568 bytes
Database mounted.
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '/u01/app/oracle/oradata/cai/users01.dbf'SQL> recover datafile 4;
Media recovery complete.
SQL> alter database open;Database altered.SQL> select * from test;no rows selected

然后我又继续对控制文件进行恢复，报错如下

SQL> alter database mount;Database altered.SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/u01/app/oracle/oradata/cai/system01.dbf'
ORA-01207: file is more recent than control file - old control file

所以冷恢复必须对全库进行恢复，不能只是单独的恢复丢失数据的数据文件和控制文件

SQL> startup;
ORACLE instance started.Total System Global Area  885211136 bytes
Fixed Size            2218432 bytes
Variable Size          369100352 bytes
Database Buffers      507510784 bytes
Redo Buffers            6381568 bytes
Database mounted.
Database opened.
SQL> select * from test;ID
----------1