rman恢復到指定時間點

--

如果非歸檔模式：

shutdown immediate;

startup mount;

alter database archivelog;

--

[oracle@localhost archivelog]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Aug 16 18:05:17 2012

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

SQL> conn system/zhangle as sysdba;
Connected.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 0
Next log sequence to archive 1
Current log sequence 1
SQL>

[oracle@localhost ~]$ rman target/

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Aug 16 17:15:50 2012

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

connected to target database: YANGKAI (DBID=406290488)

RMAN>list backup of database;

using target database control file instead of recovery catalog

RMAN>

RMAN> backup database;

Starting backup at 16-AUG-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=139 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/oracleinstall/oracle/oradata/yangkai/system01.dbf
input datafile fno=00003 name=/oracleinstall/oracle/oradata/yangkai/sysaux01.dbf
input datafile fno=00002 name=/oracleinstall/oracle/oradata/yangkai/undotbs01.dbf
input datafile fno=00004 name=/oracleinstall/oracle/oradata/yangkai/users01.dbf
channel ORA_DISK_1: starting piece 1 at 16-AUG-12
channel ORA_DISK_1: finished piece 1 at 16-AUG-12
piece handle=/oracleinstall/oracle/flash_recovery_area/YANGKAI/backupset/2012_08_16/o1_mf_nnndf_TAG20120816T172507_82sh3qdk_.bkp tag=TAG20120816T172507 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:04:35
Finished backup at 16-AUG-12

Starting Control File and SPFILE Autobackup at 16-AUG-12
piece handle=/oracleinstall/rmanyk/c-406290488-20120816-03 comment=NONE
Finished Control File and SPFILE Autobackup at 16-AUG-12

RMAN> quit

[oracle@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Aug 16 17:32:04 2012

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

SQL> conn system/zhangle;
Connected.
SQL> select * from test816;
select * from test816
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> startup;
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORA-01031: insufficient privileges
SQL> conn system/zhangle as sysdba;
Connected.
SQL> select * from test816;

PPID
----------
1
5

SQL> set time on;
17:33:33 SQL> insert into test816 values(6);

1 row created.

17:34:32 SQL> delete from test816 where ppid=5;

1 row deleted.

17:34:53 SQL> commit;

Commit complete.

17:34:56 SQL> exit

4.在使用RMAN完成基於時間點的不完全恢復之前，最好對現場做一個備份，我們這裡只需要備份資料庫的控制檔案和日誌檔案即可。當恢復結束後不滿足我們要求時，可以恢復控制檔案和日誌檔案後重新進行恢復。

[oracle@localhost ~]$ rman target/

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Aug 16 17:35:58 2012

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

connected to target database: YANGKAI (DBID=406290488)

RMAN> shutdown immediate;

using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down

-----------------------另起一個視窗，執行：------------

[oracle@localhost 2012_08_16]$ cd ..
[oracle@localhost archivelog]$ ls
2012_08_16
[oracle@localhost archivelog]$ cp 2012_08_16/o1_mf_1_3_82sgxl1z_.arc ./
[oracle@localhost archivelog]$ ll
total 3652
drwxr-x--- 2 oracle oinstall 4096 Aug 16 17:21 2012_08_16
-rw-r----- 1 oracle oinstall 3729408 Aug 16 17:37 o1_mf_1_3_82sgxl1z_.arc
[oracle@localhost archivelog]$
[oracle@localhost oradata]$ cp yangkai/*ctl ./
[oracle@localhost oradata]$ ll
total 20728
-rw-r----- 1 oracle oinstall 7061504 Aug 16 17:39 control01.ctl
-rw-r----- 1 oracle oinstall 7061504 Aug 16 17:39 control02.ctl
-rw-r----- 1 oracle oinstall 7061504 Aug 16 17:39 control03.ctl
drwxr-x--- 2 oracle oinstall 4096 Aug 15 02:44 yangkai

--------------------------繼續rman視窗執行-------------

RMAN> startup;

connected to target database (not started)
Oracle instance started
database mounted
database opened

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes
Variable Size 113247824 bytes
Database Buffers 167772160 bytes
Redo Buffers 2973696 bytes

RMAN> @/home/oracle/testrman.rman

RMAN> run{
2> allocate channel c1 type disk;
3> allocate channel c2 type disk;
4> sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';
5> set until time='2012-08-16 17:30:00';
6> restore database;
7> recover database;
8> sql 'alter database open resetlog';}
allocated channel: c1
channel c1: sid=142 devtype=DISK

allocated channel: c2
channel c2: sid=141 devtype=DISK

sql statement: alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"

executing command: SET until clause

Starting restore at 16-AUG-12

channel c1: starting datafile backupset restore
channel c1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oracleinstall/oracle/oradata/yangkai/system01.dbf
restoring datafile 00002 to /oracleinstall/oracle/oradata/yangkai/undotbs01.dbf
restoring datafile 00003 to /oracleinstall/oracle/oradata/yangkai/sysaux01.dbf
restoring datafile 00004 to /oracleinstall/oracle/oradata/yangkai/users01.dbf
channel c1: reading from backup piece /oracleinstall/oracle/flash_recovery_area/YANGKAI/backupset/2012_08_16/o1_mf_nnndf_TAG20120816T172507_82sh3qdk_.bkp
released channel: c1
released channel: c2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 08/16/2012 17:41:01
ORA-19870: error reading backup piece /oracleinstall/oracle/flash_recovery_area/YANGKAI/backupset/2012_08_16/o1_mf_nnndf_TAG20120816T172507_82sh3qdk_.bkp
ORA-19573: cannot obtain exclusive enqueue for datafile 1

RMAN> **end-of-file**

RMAN> shutdown immediate;

database closed
database dismounted
Oracle instance shut down

RMAN>

-----------------------------解決方案--------------

執行rman，必須在mount狀態。

startup的過程是：1.啟動例項；2.載入資料庫；3開啟資料庫。--startup mount/nomount

RMAN> shutdown immediate;

database closed
database dismounted
Oracle instance shut down

RMAN> startup mount;

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes
Variable Size 117442128 bytes
Database Buffers 163577856 bytes
Redo Buffers 2973696 bytes

RMAN> @/home/oracle/testrman.rman

RMAN> run{
2> allocate channel c1 type disk;
3> allocate channel c2 type disk;
4> sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';
5> set until time='2012-08-16 17:30:00';
6> restore database;
7> recover database;
8> sql 'alter database open resetlog';}
allocated channel: c1
channel c1: sid=157 devtype=DISK

allocated channel: c2
channel c2: sid=154 devtype=DISK

sql statement: alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"

executing command: SET until clause

Starting restore at 16-AUG-12

channel c1: starting datafile backupset restore
channel c1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oracleinstall/oracle/oradata/yangkai/system01.dbf
restoring datafile 00002 to /oracleinstall/oracle/oradata/yangkai/undotbs01.dbf
restoring datafile 00003 to /oracleinstall/oracle/oradata/yangkai/sysaux01.dbf
restoring datafile 00004 to /oracleinstall/oracle/oradata/yangkai/users01.dbf
channel c1: reading from backup piece /oracleinstall/oracle/flash_recovery_area/YANGKAI/backupset/2012_08_16/o1_mf_nnndf_TAG20120816T172507_82sh3qdk_.bkp
channel c1: restored backup piece 1
piece handle=/oracleinstall/oracle/flash_recovery_area/YANGKAI/backupset/2012_08_16/o1_mf_nnndf_TAG20120816T172507_82sh3qdk_.bkp tag=TAG20120816T172507
channel c1: restore complete, elapsed time: 00:00:46
Finished restore at 16-AUG-12

Starting recover at 16-AUG-12

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

Finished recover at 16-AUG-12

sql statement: alter database open resetlog
released channel: c1
released channel: c2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 08/16/2012 17:50:38
RMAN-11003: failure during parse/execution of SQL statement: alter database open resetlog
ORA-02288: invalid OPEN mode

RMAN> **end-of-file**

RMAN>

--註釋：指令碼中有錯誤，8> sql 'alter database open resetlog';}，不能這樣執行。。。

RMAN> sql 'alter database open restlogs';

sql statement: alter database open restlogs
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 08/16/2012 17:53:47
RMAN-11003: failure during parse/execution of SQL statement: alter database open restlogs
ORA-02288: invalid OPEN mode

Recovery Manager complete.
[oracle@localhost ~]$

--註釋：在rman下可以直接執行alter語句

[oracle@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Aug 16 17:58:13 2012

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

SQL> conn system/zhangle as sysdba;
Connected.
SQL> select * from test816;

PPID
----------
1
5

SQL>

[oracle@localhost 2012_08_16]$ cat /home/oracle/testrman.rman
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';
set until time='2012-08-16 17:30:00';
restore database;
recover database;
sql 'alter database open resetlog';}