我们在做备份时，究竟需要备份数据库的哪些文件呢？
其实只需要备份数据文件和控制文件就可以了，其他的参数文件，重做日志文件以及口令文件与数据文件相比都非常小，所以在一般情况下都会一起备份。
冷备份步骤：
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 AVAILABLE
SQL> 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.ctl
SQL> select group#,status,member from v$logfile;
SQL> col member for a30
SQL> /
GROUP# STATUS MEMBER
---------- ------- ------------------------------
3 /u01/app/oracle/oradata/cai/re
do03.log
2 /u01/app/oracle/oradata/cai/re
do02.log
1 /u01/app/oracle/oradata/cai/re
do01.log
SQL> show parameter pfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/db_home1/dbs/spfilecai.ora
SQL> select name from v$tempfile;
NAME
------------------------------------------------------------
/u01/app/oracle/oradata/cai/temp01.dbf2.创建一张测试表
SQL> create user nice identified by oracle
2 default tablespace example
3 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 it
SQL> 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 it
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS READ ONLY
EXAMPLE READ ONLY
6 rows selected.
SQL> alter tablespace users read write;
Tablespace altered.
SQL> insert into test values(1);
1 row created.
SQL> select * from test;
ID
----------
1
SQL> 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 1822224
9552 -rw-r----- 1 oracle oinstall 9781248 Feb 29 13:29 control01.ctl
102408 -rw-r----- 1 oracle oinstall 104865792 Jan 14 17:35 example01.dbf
51204 -rw-r----- 1 oracle oinstall 52429312 Feb 29 13:29 redo01.log
51204 -rw-r----- 1 oracle oinstall 52429312 Feb 29 13:26 redo02.log
51204 -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.dbf
5260 -rw-r----- 1 oracle oinstall 38805504 Feb 29 12:02 temp01.dbf
107528 -rw-r----- 1 oracle oinstall 110108672 Feb 29 13:29 undotbs01.dbf
61448 -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 1824220
9552 -rw-r----- 1 oracle oinstall 9781248 Feb 29 13:30 control01.ctl
102408 -rw-r----- 1 oracle oinstall 104865792 Feb 29 13:30 example01.dbf
4 -rw-r--r-- 1 oracle oinstall 962 Feb 29 13:30 initcai.ora
51204 -rw-r----- 1 oracle oinstall 52429312 Feb 29 13:30 redo01.log
51204 -rw-r----- 1 oracle oinstall 52429312 Feb 29 13:30 redo02.log
51204 -rw-r----- 1 oracle oinstall 52429312 Feb 29 13:30 redo03.log
4 -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.dbf
7248 -rw-r----- 1 oracle oinstall 38805504 Feb 29 13:30 temp01.dbf
107528 -rw-r----- 1 oracle oinstall 110108672 Feb 29 13:30 undotbs01.dbf
61448 -rw-r----- 1 oracle oinstall 62922752 Feb 29 13:30 users01.dbf3.模拟数据丢失，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
----------
1
SQL> truncate table test;
Table truncated.
SQL> select * from test;
no rows selected
4.将备份的文件复制回原来的位置
我这里做了一个不一样的实验，首先我只将表所在的数据文件恢复到原来的位置，启动数据库后发现数据表中还是没有数据
[oracle@11g backup]$ ll -ls
total 1824212
9552 -rw-r----- 1 oracle oinstall 9781248 Feb 29 15:35 control01.ctl
102408 -rw-r----- 1 oracle oinstall 104865792 Feb 29 15:35 example01.dbf
51204 -rw-r----- 1 oracle oinstall 52429312 Feb 29 15:35 redo01.log
51204 -rw-r----- 1 oracle oinstall 52429312 Feb 29 15:35 redo02.log
51204 -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.dbf
7248 -rw-r----- 1 oracle oinstall 38805504 Feb 29 15:35 temp01.dbf
107528 -rw-r----- 1 oracle oinstall 110108672 Feb 29 15:35 undotbs01.dbf
61448 -rw-r----- 1 oracle oinstall 62922752 Feb 29 15:35 users01.dbf
[oracle@11g backup]$ scp users01.dbf /u01/app/oracle/oradata/caiSQL> 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
241
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
