重庆思庄Oracle、、PostgreSQL、Redhat认证学习论坛

标题: 物理备库的备份,能够用于物理主库的恢复吗 [打印本页]
作者: 郑全    时间: 2013-12-24 17:31
标题: 物理备库的备份,能够用于物理主库的恢复吗

如题,

平时为了减轻主库的压力,备份放到备库上执行,如果主库的某一个数据文件丢失,那么,能否使用备库的备份来恢复主库上文件的损坏呢?

 

 

作者: 郑全    时间: 2013-12-24 17:34
标题: 1.在备库上执行数据库的备份

 

 

备库主机名字:auxdb

主库主机名字:sztech1

 

在备库上执行数据库的备份

 

[oracle@auxdb admin]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Dec 24 17:08:54 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: SZTECH1 (DBID=3253410194)

RMAN> backup database format '/home/oracle/dbback_%D_%T_%U.bak';

Starting backup at 24-DEC-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/sztech1/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/sztech1/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/sztech1/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/sztech1/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/sztech1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 24-DEC-13
channel ORA_DISK_1: finished piece 1 at 24-DEC-13
piece handle=/home/oracle/dbback_24_20131224_07osb4aj_1_1.bak tag=TAG20131224T170939 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:16
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 24-DEC-13
channel ORA_DISK_1: finished piece 1 at 24-DEC-13
piece handle=/home/oracle/dbback_24_20131224_08osb4cv_1_1.bak tag=TAG20131224T170939 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 24-DEC-13

RMAN> exit

作者: 郑全    时间: 2013-12-24 17:38
标题: 2.模拟主库上文件被删除

在主库:dbserver 上模拟数据文件被删除

 

1.进入数据文件目录:

oracle@dbserver sztech1]$ pwd
/u01/app/oracle/oradata/sztech1

2.察看目前的文件

[oracle@dbserver sztech1]$ ls -ltr
total 1927780
-rw-r-----. 1 oracle oinstall  30416896 Dec 24 16:30 temp01.dbf
-rw-r-----. 1 oracle oinstall  52429312 Dec 24 17:07 redo01.log
-rw-r-----. 1 oracle oinstall  52429312 Dec 24 17:07 redo02.log
-rw-r-----. 1 oracle oinstall   5251072 Dec 24 17:07 users01.dbf
-rw-r-----. 1 oracle oinstall 328343552 Dec 24 17:07 example01.dbf
-rw-r-----. 1 oracle oinstall 566239232 Dec 24 17:14 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 775954432 Dec 24 17:16 system01.dbf
-rw-r-----. 1 oracle oinstall 110108672 Dec 24 17:16 undotbs01.dbf
-rw-r-----. 1 oracle oinstall   9748480 Dec 24 17:16 control01.ctl
-rw-r-----. 1 oracle oinstall  52429312 Dec 24 17:16 redo03.log

3.改名users01.dbf文件

 

[oracle@dbserver sztech1]$ mv users01.dbf users01.dbf20131224

4.验证修改后:

 

[oracle@dbserver sztech1]$ ls -ltr
total 1927780
-rw-r-----. 1 oracle oinstall  30416896 Dec 24 16:30 temp01.dbf
-rw-r-----. 1 oracle oinstall  52429312 Dec 24 17:07 redo01.log
-rw-r-----. 1 oracle oinstall  52429312 Dec 24 17:07 redo02.log
-rw-r-----. 1 oracle oinstall   5251072 Dec 24 17:07 users01.dbf20131224
-rw-r-----. 1 oracle oinstall 328343552 Dec 24 17:07 example01.dbf
-rw-r-----. 1 oracle oinstall 566239232 Dec 24 17:14 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 775954432 Dec 24 17:16 system01.dbf
-rw-r-----. 1 oracle oinstall 110108672 Dec 24 17:16 undotbs01.dbf
-rw-r-----. 1 oracle oinstall   9748480 Dec 24 17:16 control01.ctl
-rw-r-----. 1 oracle oinstall  52429312 Dec 24 17:16 redo03.log

 

发现users01文件已经被改名了.

 

 

作者: 郑全    时间: 2013-12-24 17:42
标题: 3.察看主库机器上备份情况

在主库上执行list backup 检查备份情况,确认主库没有备份:

 

[oracle@dbserver sztech1]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Dec 24 17:17:30 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: SZTECH1 (DBID=3253410194)

 

RMAN>

RMAN> list backup;

specification does not match any backup in the repository


RMAN>

 

确定主库确实没有备份.

作者: 郑全    时间: 2013-12-24 17:44
标题: 4.拷贝备库的备份到主库

 

主库:dbserver 192.168.133.120

备库:auxdb     192.168.133.121

 

把备库上的备份文件拷贝到主库:

 

[oracle@auxdb ~]$ scp *.bak 192.168.133.120:/home/oracle/.
The authenticity of host '192.168.133.120 (192.168.133.120)' can't be established.
RSA key fingerprint is ba:da:31:63:54:e0:0e:7c:bb:7f:06:76:4a:1c:45:b3.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.133.120' (RSA) to the list of known hosts.
oracle@192.168.133.120's password:
dbback_24_20131224_07osb4aj_1_1.bak                                                       100% 1111MB  17.1MB/s   01:05   
dbback_24_20131224_08osb4cv_1_1.bak                                                       100% 9600KB   9.4MB/s   00:00   

作者: 郑全    时间: 2013-12-24 17:48
标题: 5.尝试在主库上进行恢复

在备库已经把备份文件拷贝到主库后,使用以下命令,进行恢复:

 

[oracle@dbserver sztech1]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Dec 24 17:17:30 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: SZTECH1 (DBID=3253410194)

 

RMAN> run{
2> sql 'alter tablespace users offline immediate';
3> restore tablespace users;
4> recover tablespace users;
5> sql 'alter tablespace users online';
6> }

sql statement: alter tablespace users offline immediate

Starting restore at 24-DEC-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 12/24/2013 17:19:10
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 4 found to restore

 

:没有找到users的数据文件备份.看来拷贝没有认到.

作者: 郑全    时间: 2013-12-24 17:53
标题: 6.把备库的备份在主库注册

由于备份在备库操作的,那么备份的元信息就在备库的控制文件中,主库实际上认不到,

 

这时,需要在主库上对拷贝过来的备库的备份进行注册,备份文件拷贝过来,放在 /home/oracle/目录下:

[oracle@dbserver ~]$ pwd
/home/oracle
[oracle@dbserver ~]$ ls -ltr
total 1147592
-rw-r--r--. 1 oracle oinstall       1264 Dec 24 16:34 initsztech1.ora
-rw-r-----. 1 oracle oinstall 1165295616 Dec 24 17:14 dbback_24_20131224_07osb4aj_1_1.bak
-rw-r-----. 1 oracle oinstall    9830400 Dec 24 17:14 dbback_24_20131224_08osb4cv_1_1.bak
[oracle@dbserver ~]$

 

进行注册:

 

[oracle@dbserver sztech1]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Dec 24 17:17:30 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: SZTECH1 (DBID=3253410194)

 

RMAN> catalog start with '/home/oracle/';

searching for all files that match the pattern /home/oracle/

List of Files Unknown to the Database
=====================================
File Name: /home/oracle/.bashrc
File Name: /home/oracle/.bash_history
File Name: /home/oracle/.viminfo
File Name: /home/oracle/.kshrc
File Name: /home/oracle/.ssh/known_hosts
File Name: /home/oracle/.bash_profile
File Name: /home/oracle/initsztech1.ora
File Name: /home/oracle/.bash_logout
File Name: /home/oracle/dbback_24_20131224_07osb4aj_1_1.bak
File Name: /home/oracle/dbback_24_20131224_08osb4cv_1_1.bak

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /home/oracle/dbback_24_20131224_07osb4aj_1_1.bak
File Name: /home/oracle/dbback_24_20131224_08osb4cv_1_1.bak

List of Files Which Where Not Cataloged
=======================================
File Name: /home/oracle/.bashrc
  RMAN-07517: Reason: The file header is corrupted
File Name: /home/oracle/.bash_history
  RMAN-07517: Reason: The file header is corrupted
File Name: /home/oracle/.viminfo
  RMAN-07517: Reason: The file header is corrupted
File Name: /home/oracle/.kshrc
  RMAN-07517: Reason: The file header is corrupted
File Name: /home/oracle/.ssh/known_hosts
  RMAN-07517: Reason: The file header is corrupted
File Name: /home/oracle/.bash_profile
  RMAN-07517: Reason: The file header is corrupted
File Name: /home/oracle/initsztech1.ora
  RMAN-07517: Reason: The file header is corrupted
File Name: /home/oracle/.bash_logout
  RMAN-07517: Reason: The file header is corrupted

 

 

作者: 郑全    时间: 2013-12-24 17:55
标题: 7.注册后,验证主库备份信息是否包含备库的备份

在主库操作:

 

[oracle@dbserver sztech1]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Dec 24 17:17:30 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: SZTECH1 (DBID=3253410194)

 

RMAN> list backup;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3       Full    1.09G      DISK        00:00:00     24-DEC-13     
        BP Key: 3   Status: AVAILABLE  Compressed: NO  Tag: TAG20131224T170939
        Piece Name: /home/oracle/dbback_24_20131224_07osb4aj_1_1.bak
  List of Datafiles in backup set 3
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 777820     24-DEC-13 /u01/app/oracle/oradata/sztech1/system01.dbf
  2       Full 777820     24-DEC-13 /u01/app/oracle/oradata/sztech1/sysaux01.dbf
  3       Full 777820     24-DEC-13 /u01/app/oracle/oradata/sztech1/undotbs01.dbf
  4       Full 777820     24-DEC-13 /u01/app/oracle/oradata/sztech1/users01.dbf
  5       Full 777820     24-DEC-13 /u01/app/oracle/oradata/sztech1/example01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4       Full    9.36M      DISK        00:00:00     24-DEC-13     
        BP Key: 4   Status: AVAILABLE  Compressed: NO  Tag: TAG20131224T170939
        Piece Name: /home/oracle/dbback_24_20131224_08osb4cv_1_1.bak
  SPFILE Included: Modification time: 24-DEC-13
  SPFILE db_unique_name: AUXDB
  Standby Control File Included: Ckp SCN: 777820       Ckp time: 24-DEC-13

RMAN>

 

 

我们看到,备库的备份信息已经成功注册.

作者: 郑全    时间: 2013-12-24 17:57
标题: 8.主库上执行users恢复

 

[oracle@dbserver sztech1]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Dec 24 17:17:30 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: SZTECH1 (DBID=3253410194)

 

RMAN> run{
2> sql 'alter tablespace users offline immediate';
3> restore tablespace users;
4> recover tablespace users;
5> sql 'alter tablespace users online';
6> }

sql statement: alter tablespace users offline immediate

Starting restore at 24-DEC-13
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/sztech1/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/dbback_24_20131224_07osb4aj_1_1.bak
channel ORA_DISK_1: piece handle=/home/oracle/dbback_24_20131224_07osb4aj_1_1.bak tag=TAG20131224T170939
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 24-DEC-13

Starting recover at 24-DEC-13
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 24-DEC-13

sql statement: alter tablespace users online

RMAN> exit

 

恢复完成,中间不再抱错.

作者: 郑全    时间: 2013-12-24 18:05
标题: 9.验证恢复的正确性

恢复后,需要验证数据文件及内容是否可用,主库的变化是否可以继续同步到备库;

 

1.察看表空间情况

 

[oracle@dbserver sztech1]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 24 17:24:32 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

SQL> connect / as sysdba
Connected.
SQL>
SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
EXAMPLE                        ONLINE

 

2.在users表空间上的内容进行变化

 

[oracle@dbserver sztech1]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 24 17:24:32 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

 

 

 

SQL> conn hr/hr
Connected.
SQL> select table_name,tablespace_name from tabs;

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
COUNTRIES
JOB_HISTORY                    EXAMPLE
EMPLOYEES                      EXAMPLE
JOBS                           EXAMPLE
DEPARTMENTS                    EXAMPLE
LOCATIONS                      EXAMPLE
REGIONS                        EXAMPLE
DEPT                           USERS
EMP                            USERS

 

 

删除99行数据


SQL> delete from emp where rownum<100;

99 rows deleted.

SQL> commit;

Commit complete.

 

 

SQL> select count(*) from emp;

  COUNT(*)
----------
         8

SQL> conn / as sysdba
Connected.

 

进行日志切换:


SQL> alter system switch logfile;

System altered.

SQL> r
  1* alter system switch logfile

System altered.

SQL>

 

3.备库上验证数据是否同步

[oracle@auxdb ~]$ sqlplus /nolog
co
SQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 24 17:27:59 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

SQL> nnect / as sysdba
Connected.
SQL> conn hr/hr
Connected.
SQL> select count(*) from emp;

  COUNT(*)
----------
         8

SQL>

 

和数据一致。

 

说明数据同步没有问题。使用备库的备份，用于恢复主库，不影响主备关系。

 

其实，这里是物理备库，备库就是主库的一个克隆。所以，备库的备份可以直接在主库使用 。只是事先需要catalog一下，否则主库认不到这些备份而已。

 





欢迎光临 重庆思庄Oracle、、PostgreSQL、Redhat认证学习论坛 (http://bbs.cqsztech.com/) Powered by Discuz! X3.2