优秀网站建设模板,遵义招标网,erp系统有什么用,手机兼职平台app排行榜前十名我们在做备份时#xff0c;究竟需要备份数据库的哪些文件呢#xff1f;
其实只需要备份数据文件和控制文件就可以了#xff0c;其他的参数文件#xff0c;重做日志文件以及口令文件与数据文件相比都非常小#xff0c;所以在一般情况下都会一起备份。
冷备份步骤#xf…
我们在做备份时究竟需要备份数据库的哪些文件呢
其实只需要备份数据文件和控制文件就可以了其他的参数文件重做日志文件以及口令文件与数据文件相比都非常小所以在一般情况下都会一起备份。
冷备份步骤
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.SQLconn nice/oracle
Connected.
SQL show user
USER is NICESQL 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.复制要备份的文件到备份目录
[oracle11g ~]$ cd /u01/app/oracle/product/11.2.0/db_home1/dbs/
[oracle11g 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
[oracle11g dbs]$ scp initcai.ora /home/oracle/backup
[oracle11g dbs]$ scp spfilecai.ora /home/oracle/backup
[oracle11g dbs]$ cd /u01/app/oracle/oradata/cai/
[oracle11g 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
[oracle11g cai]$ scp * /home/oracle/backup
[oracle11g cai]$ cd /home/oracle/backup
[oracle11g 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 selected4.将备份的文件复制回原来的位置
我这里做了一个不一样的实验首先我只将表所在的数据文件恢复到原来的位置启动数据库后发现数据表中还是没有数据
[oracle11g 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
[oracle11g 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.dbfSQL 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
