营销型和展示型网站网站排名下降
我们在做备份时，究竟需要备份数据库的哪些文件呢？
其实只需要备份数据文件和控制文件就可以了，其他的参数文件，重做日志文件以及口令文件与数据文件相比都非常小，所以在一般情况下都会一起备份。
冷备份步骤：
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.dbf2.创建一张测试表
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.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
----------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/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