oracle10g冷备份与恢复_数据库恢复工具[通俗易懂]

oracle10g冷备份与恢复_数据库恢复工具[通俗易懂]我这里做了一个不一样的实验,首先我只将表所在的数据文件恢复到原来的位置,启动数据库后发现数据表中还是没有数据

oracle10g冷备份与恢复_数据库恢复工具[通俗易懂]"

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

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

冷备份步骤:

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.dbf

2.创建一张测试表

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.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
----------
	 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/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

 

 

今天的文章oracle10g冷备份与恢复_数据库恢复工具[通俗易懂]分享到此就结束了,感谢您的阅读。

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。
如需转载请保留出处:https://bianchenghao.cn/83123.html

(0)
编程小号编程小号

相关推荐

发表回复

您的电子邮箱地址不会被公开。 必填项已用 * 标注