rman 對資料檔案 的恢複測試

一下是關於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>