Maison >base de données >tutoriel mysql >rman 对数据文件 的恢复测试

rman 对数据文件 的恢复测试

WBOY
WBOYoriginal
2016-06-07 17:08:03878parcourir

一下是关于rman 的备份,恢复的测试: database 是mount 状态,其他用户连不了:SQLgt; select status from v$instance; STATUS

一下是关于rman 的备份,恢复的测试:

database 是mount 状态,其他用户连不了:
SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> conn scott/Oracle
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress

Warning: You are no longer connected to ORACLE.
SQL>

好,,下面切换到sys 用户来test:

-----
先看一下参数:
RMAN> startup mount;

Oracle instance started
database mounted

Total System Global Area     335544320 bytes

Fixed Size                     1219280 bytes
Variable Size                 83887408 bytes
Database Buffers             247463936 bytes
Redo Buffers                   2973696 bytes


对rman auobackup on 开启,和路径设置,实验:
[oracle@aoracle rman]$ ls -lrt
total 0
[oracle@aoracle rman]$ pwd
/u02/rman
[oracle@aoracle rman]$

RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u02/rman/ctl_%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/pp/oracle/product/10.2/db_1/dbs/snapcf_ezhou.f'; # default

好,下面进行实验:

对一个database 进行备份:

RMAN> backup database format '/u02/rman/ezhou_%U.bak';

Starting backup at 16-OCT-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=152 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u02/ezhou/system01.dbf
input datafile fno=00002 name=/u02/ezhou/undotbs01.dbf
input datafile fno=00003 name=/u02/ezhou/sysaux01.dbf
input datafile fno=00005 name=/u02/ezhou/example01.dbf
input datafile fno=00004 name=/u02/ezhou/users01.dbf
input datafile fno=00006 name=/u02/ezhou/example02.dbf
channel ORA_DISK_1: starting piece 1 at 16-OCT-11
channel ORA_DISK_1: finished piece 1 at 16-OCT-11
piece handle=/u02/rman/ezhou_09mp76h8_1_1.bak tag=TAG20111016T021256 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:20
Finished backup at 16-OCT-11

Starting Control File and SPFILE Autobackup at 16-OCT-11
piece handle=/u02/rman/ctl_c-4046377924-20111016-00 comment=NONE
Finished Control File and SPFILE Autobackup at 16-OCT-11

仔细研究一下log:
看到是一个channel,生成的文件放在:
/u02/rman/ezhou_09mp76h8_1_1.bak
/u02/rman/ctl_c-4046377924-20111016-00

上面一个是database ,下面一个是control file 和spfile ,这个是自动备份的。
因为上面的control file autobackup 是on.

好,看一下生成的文件:
[oracle@aoracle rman]$ ls -lrt
total 898088
-rw-r----- 1 oracle oinstall 911589376 Oct 16 02:15 ezhou_09mp76h8_1_1.bak
-rw-r----- 1 oracle oinstall   7143424 Oct 16 02:15 ctl_c-4046377924-20111016-00

下面对数据文件进行恢复:
先看一下数据文件放的位置:

RMAN> report schema;

Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    480      SYSTEM               ***     /u02/ezhou/system01.dbf
2    285      UNDOTBS1             ***     /u02/ezhou/undotbs01.dbf
3    250      SYSAUX               ***     /u02/ezhou/sysaux01.dbf
4    25       USERS                ***     /u02/ezhou/users01.dbf
5    100      EXAMPLE              ***     /u02/ezhou/example01.dbf
6    10       EXAMPLE              ***     /u02/ezhou/example02.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       /u02/ezhou/temp01.dbf

[oracle@aoracle ezhou]$ ls -lrt
total 1371388
drwxr-xr-x 3 oracle oinstall      4096 Sep 28 16:34 patch
-rw-r----- 1 oracle oinstall  20979712 Oct 15 09:58 temp01.dbf
-rw-r----- 1 oracle oinstall  52429312 Oct 15 15:24 redo01.log
-rw-r----- 1 oracle oinstall  52429312 Oct 15 15:25 redo02.log
-rw-r----- 1 oracle oinstall  26222592 Oct 16 00:49 users01.dbf
-rw-r----- 1 oracle oinstall 298852352 Oct 16 00:49 undotbs01.dbf
-rw-r----- 1 oracle oinstall 503324672 Oct 16 00:49 system01.dbf
-rw-r----- 1 oracle oinstall 262152192 Oct 16 00:49 sysaux01.dbf
-rw-r----- 1 oracle oinstall  52429312 Oct 16 00:49 redo03.log
-rw-r----- 1 oracle oinstall  10493952 Oct 16 00:49 example02.dbf
-rw-r----- 1 oracle oinstall 104865792 Oct 16 00:49 example01.dbf
-rw-r----- 1 oracle oinstall   7061504 Oct 16 02:25 control03.ctl
-rw-r----- 1 oracle oinstall   7061504 Oct 16 02:25 control02.ctl
-rw-r----- 1 oracle oinstall   7061504 Oct 16 02:25 control01.ctl
[oracle@aoracle ezhou]$ rm -rf *.dbf
[oracle@aoracle ezhou]$ ls -lrt
total 174508
drwxr-xr-x 3 oracle oinstall     4096 Sep 28 16:34 patch
-rw-r----- 1 oracle oinstall 52429312 Oct 15 15:24 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Oct 15 15:25 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Oct 16 00:49 redo03.log
-rw-r----- 1 oracle oinstall  7061504 Oct 16 02:25 control03.ctl
-rw-r----- 1 oracle oinstall  7061504 Oct 16 02:25 control02.ctl
-rw-r----- 1 oracle oinstall  7061504 Oct 16 02:25 control01.ctl

好下面对数据库进行恢复:
看一下数据库的status :
SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> select status from v$instance;
select status from v$instance
*
ERROR at line 1:
ORA-01034: ORACLE not available


SQL> startup;
ORACLE instance started.

Total System Global Area  335544320 bytes
Fixed Size                  1219280 bytes
Variable Size              83887408 bytes
Database Buffers          247463936 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u02/ezhou/system01.dbf'

数据库没有数据文件:
现在的状态是mounted:
RMAN> run {
2> restore database;
3> recover database;
4> }  

Starting restore at 16-OCT-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u02/ezhou/system01.dbf
restoring datafile 00002 to /u02/ezhou/undotbs01.dbf
restoring datafile 00003 to /u02/ezhou/sysaux01.dbf
restoring datafile 00004 to /u02/ezhou/users01.dbf
restoring datafile 00005 to /u02/ezhou/example01.dbf
restoring datafile 00006 to /u02/ezhou/example02.dbf
channel ORA_DISK_1: reading from backup piece /u02/rman/ezhou_09mp76h8_1_1.bak
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/rman/ezhou_09mp76h8_1_1.bak tag=TAG20111016T021256
channel ORA_DISK_1: restore complete, elapsed time: 00:01:26
Finished restore at 16-OCT-11

Starting recover at 16-OCT-11
using channel ORA_DISK_1

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

Finished recover at 16-OCT-11

看一下数据文件:已经恢复:
[oracle@aoracle ezhou]$ ls -lrt
total 1353340
drwxr-xr-x 3 oracle oinstall      4096 Sep 28 16:34 patch
-rw-r----- 1 oracle oinstall  52429312 Oct 15 15:24 redo01.log
-rw-r----- 1 oracle oinstall  52429312 Oct 15 15:25 redo02.log
-rw-r----- 1 oracle oinstall  52429312 Oct 16 00:49 redo03.log
-rw-r----- 1 oracle oinstall  10493952 Oct 16 02:32 example02.dbf
-rw-r----- 1 oracle oinstall  26222592 Oct 16 02:32 users01.dbf
-rw-r----- 1 oracle oinstall 104865792 Oct 16 02:32 example01.dbf
-rw-r----- 1 oracle oinstall 262152192 Oct 16 02:33 sysaux01.dbf
-rw-r----- 1 oracle oinstall 298852352 Oct 16 02:33 undotbs01.dbf
-rw-r----- 1 oracle oinstall 503324672 Oct 16 02:33 system01.dbf
-rw-r----- 1 oracle oinstall   7061504 Oct 16 02:35 control03.ctl
-rw-r----- 1 oracle oinstall   7061504 Oct 16 02:35 control02.ctl
-rw-r----- 1 oracle oinstall   7061504 Oct 16 02:35 control01.ctl

打开数据库:
SQL> alter database open;

Database altered.

看一下备份的记录:
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8       Full    869.35M    DISK        00:02:18     16-OCT-11     
        BP Key: 8   Status: AVAILABLE  Compressed: NO  Tag: TAG20111016T021256
        Piece Name: /u02/rman/ezhou_09mp76h8_1_1.bak
  List of Datafiles in backup set 8
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 5483534    16-OCT-11 /u02/ezhou/system01.dbf
  2       Full 5483534    16-OCT-11 /u02/ezhou/undotbs01.dbf
  3       Full 5483534    16-OCT-11 /u02/ezhou/sysaux01.dbf
  4       Full 5483534    16-OCT-11 /u02/ezhou/users01.dbf
  5       Full 5483534    16-OCT-11 /u02/ezhou/example01.dbf
  6       Full 5483534    16-OCT-11 /u02/ezhou/example02.dbf
 
-----------

下面补充date改时间格式的方法:
alter system set nls_date_format ='yyyy-mm-dd hh24:mi:ss' scope=spfile;

SQL> select sysdate from dual;

SYSDATE
---------
16-OCT-11

SQL> show parameter format;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_format                   string      %t_%s_%r.dbf
nls_date_format                      string
nls_time_format                      string
nls_time_tz_format                   string
nls_timestamp_format                 string
nls_timestamp_tz_format              string
star_transformation_enabled          string      FALSE
SQL> alter system set nls_date_format='yyyy-mm-dd hh24:mi:ss' scope=spfile;

System altered.

SQL> select sysdate from dual;

SYSDATE
---------
16-OCT-11

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> select sysdate from dual;
select sysdate from dual
*
ERROR at line 1:
ORA-01034: ORACLE not available


SQL> startup nomount;
ORACLE instance started.

Total System Global Area  335544320 bytes
Fixed Size                  1219280 bytes
Variable Size              83887408 bytes
Database Buffers          247463936 bytes
Redo Buffers                2973696 bytes
SQL> select sysdate from dual;

SYSDATE
-------------------
2011-10-16 02:56:41

SQL>

linux

Déclaration:
Le contenu de cet article est volontairement contribué par les internautes et les droits d'auteur appartiennent à l'auteur original. Ce site n'assume aucune responsabilité légale correspondante. Si vous trouvez un contenu suspecté de plagiat ou de contrefaçon, veuillez contacter admin@php.cn
Article précédent:MongoDB副本集Article suivant:Golden Gate安装说明