rman 对数据文件 的恢复测试

一下是关于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>