用SQL*PLUS不能启动到mount阶段，因为没PFILE或者SPFILE。使用RMAN可以，因为它会使用INIT.ORA文件启动。

背景：

用RMAN备份

backup database plus archivelog;

模拟丢失对象scott.ojects

记录下时间

alter system set nls_date_format='yyyy-mm-dd hh24:mi:ss' scope=spfile;
SQL> select sysdate from dual;

SYSDATE
-------------------
2011-01-25 11:32:28

恢复流程如下：

RMAN:

rman target /

restore spfile from <>; --恢复SPFILE

shutdown immediate --关闭DB

startup nomount; --使用恢复的SPFILE重新启动

restore controlfile from <>;--恢复控制文件

alter database mount;

run {
set until time=<>:

restore database;

recover database;

} --还原DB

SQLPLUS:

alter database open read only; --read only打开DB查看，是否是得到丢失对象。如没有，修改时间，重复上面的过程，直到找到对象。

alter database open resetlogs;

具体流程如下：

[oracle@jerry ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Jan 26 05:23:38 2011

connected to target database: DUMMY (not mounted)
RMAN> restore spfile from '/opt/oracle/app/oracle/flash_recovery_area/OCP/backupset/2011_01_25/o1_mf_ncsnf_TAG20110125T113843_6mwkhmv6_.bkp';

Starting restore at 26-JAN-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK

channel ORA_DISK_1: autobackup found: /opt/oracle/app/oracle/flash_recovery_area/OCP/backupset/2011_01_25/o1_mf_ncsnf_TAG20110125T113843_6mwkhmv6_.bkp
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 26-JAN-11

RMAN> shutdown immediate

Oracle instance shut down

RMAN> startup nomount;

connected to target database (not started)
Oracle instance started

Total System Global Area 167772160 bytes

Fixed Size 1218316 bytes
Variable Size 67111156 bytes
Database Buffers 96468992 bytes
Redo Buffers 2973696 bytes

RMAN> exit

Recovery Manager complete.
[oracle@jerry ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Jan 26 05:26:23 2011

connected to target database: ocp (not mounted)

RMAN> restore controlfile from '/opt/oracle/app/oracle/flash_recovery_area/OCP/backupset/2011_01_25/o1_mf_ncsnf_TAG20110125T113843_6mwkhmv6_.bkp';

Starting restore at 26-JAN-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/opt/oracle/app/oracle/oradata/ocp/control01.ctl
output filename=/opt/oracle/app/oracle/oradata/ocp/control02.ctl
output filename=/opt/oracle/app/oracle/oradata/ocp/control03.ctl
Finished restore at 26-JAN-11

[oracle@jerry ~]$ ls /opt/oracle/app/oracle/oradata/ocp
control01.ctl control02.ctl control03.ctl

RMAN> list backup;

released channel: ORA_DISK_1

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

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
4 50.59M DISK 00:00:03 25-JAN-11
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20110125T113838
Piece Name: /opt/oracle/app/oracle/data_bak/OCP_full_backup

List of Archived Logs in backup set 4
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 2 472785 25-JAN-11 480825 25-JAN-11
1 3 480825 25-JAN-11 481990 25-JAN-11
1 4 481990 25-JAN-11 482015 25-JAN-11
1 5 482015 25-JAN-11 482070 25-JAN-11

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5 Full 588.21M DISK 00:00:41 25-JAN-11
BP Key: 5 Status: AVAILABLE Compressed: NO Tag: TAG20110125T113843
Piece Name: /opt/oracle/app/oracle/flash_recovery_area/OCP/backupset/2011_01_25/o1_mf_nnndf_TAG20110125T113843_6mwkg3px_.bkp
List of Datafiles in backup set 5
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 482075 25-JAN-11 /opt/oracle/app/oracle/oradata/ocp/system01.dbf
2 Full 482075 25-JAN-11 /opt/oracle/app/oracle/oradata/ocp/undotbs01.dbf
3 Full 482075 25-JAN-11 /opt/oracle/app/oracle/oradata/ocp/sysaux01.dbf
4 Full 482075 25-JAN-11 /opt/oracle/app/oracle/oradata/ocp/users01.dbf
5 Full 482075 25-JAN-11 /opt/oracle/app/oracle/oradata/ocp/example01.dbf

RMAN> report schema;

Starting implicit crosscheck backup at 26-JAN-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
Crosschecked 2 objects
Finished implicit crosscheck backup at 26-JAN-11

Starting implicit crosscheck copy at 26-JAN-11
using channel ORA_DISK_1
Finished implicit crosscheck copy at 26-JAN-11

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /opt/oracle/app/oracle/flash_recovery_area/OCP/archivelog/2011_01_25/o1_mf_1_6_6mwkho52_.arc
File Name: /opt/oracle/app/oracle/flash_recovery_area/OCP/backupset/2011_01_25/o1_mf_ncsnf_TAG20110125T113843_6mwkhmv6_.bkp

RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 0 SYSTEM *** /opt/oracle/app/oracle/oradata/ocp/system01.dbf
2 0 UNDOTBS1 *** /opt/oracle/app/oracle/oradata/ocp/undotbs01.dbf
3 0 SYSAUX *** /opt/oracle/app/oracle/oradata/ocp/sysaux01.dbf
4 0 USERS *** /opt/oracle/app/oracle/oradata/ocp/users01.dbf
5 0 EXAMPLE *** /opt/oracle/app/oracle/oradata/ocp/example01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 0 TEMP 32767 /opt/oracle/app/oracle/oradata/ocp/temp01.dbf

RMAN> restore database;

Starting restore at 26-JAN-11
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /opt/oracle/app/oracle/oradata/ocp/system01.dbf
restoring datafile 00002 to /opt/oracle/app/oracle/oradata/ocp/undotbs01.dbf
restoring datafile 00003 to /opt/oracle/app/oracle/oradata/ocp/sysaux01.dbf
restoring datafile 00004 to /opt/oracle/app/oracle/oradata/ocp/users01.dbf
restoring datafile 00005 to /opt/oracle/app/oracle/oradata/ocp/example01.dbf
channel ORA_DISK_1: reading from backup piece /opt/oracle/app/oracle/flash_recovery_area/OCP/backupset/2011_01_25/o1_mf_nnndf_TAG20110125T113843_6mwkg3px_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/opt/oracle/app/oracle/flash_recovery_area/OCP/backupset/2011_01_25/o1_mf_nnndf_TAG20110125T113843_6mwkg3px_.bkp tag=TAG20110125T113843
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 26-JAN-11

[oracle@jerry ~]$ ls /opt/oracle/app/oracle/oradata/ocp
control01.ctl control03.ctl sysaux01.dbf undotbs01.dbf
control02.ctl example01.dbf system01.dbf users01.dbf

RMAN> run{
set until time=to_date('2011-01-25 11:32:28','yyyy-mm-dd hh24:mi:ss');
restore database;
recover database;
};2>
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "identifier": expecting one of: "double-quoted-string, single-quoted-string"
RMAN-01008: the bad identifier was: to_date
RMAN-01007: at line 2 column 16 file: standard input

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "(": expecting one of: "allocate, alter, backup, beginline, blockrecover, catalog, change, connect, copy, convert, create, crosscheck, configure, duplicate, debug, delete, drop, exit, endinline, flashback, host, {, library, list, mount, open, print, quit, recover, register, release, replace, report, renormalize, reset, restore, resync, rman, run, rpctest, set, setlimit, sql, switch, spool, startup, shutdown, send, show, test, transport, upgrade, unregister, validate"
RMAN-01007: at line 2 column 23 file: standard input

RMAN>
Starting restore at 26-JAN-11
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /opt/oracle/app/oracle/oradata/ocp/system01.dbf
restoring datafile 00002 to /opt/oracle/app/oracle/oradata/ocp/undotbs01.dbf
restoring datafile 00003 to /opt/oracle/app/oracle/oradata/ocp/sysaux01.dbf
restoring datafile 00004 to /opt/oracle/app/oracle/oradata/ocp/users01.dbf
restoring datafile 00005 to /opt/oracle/app/oracle/oradata/ocp/example01.dbf
channel ORA_DISK_1: reading from backup piece /opt/oracle/app/oracle/flash_recovery_area/OCP/backupset/2011_01_25/o1_mf_nnndf_TAG20110125T113843_6mwkg3px_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/opt/oracle/app/oracle/flash_recovery_area/OCP/backupset/2011_01_25/o1_mf_nnndf_TAG20110125T113843_6mwkg3px_.bkp tag=TAG20110125T113843
channel ORA_DISK_1: restore complete, elapsed time: 00:00:36
Finished restore at 26-JAN-11

RMAN>
Starting recover at 26-JAN-11
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 6 is already on disk as file /opt/oracle/app/oracle/flash_recovery_area/OCP/archivelog/2011_01_25/o1_mf_1_6_6mwkho52_.arc
archive log filename=/opt/oracle/app/oracle/flash_recovery_area/OCP/archivelog/2011_01_25/o1_mf_1_6_6mwkho52_.arc thread=1 sequence=6
unable to find archive log
archive log thread=1 sequence=7
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/26/2011 05:58:19
RMAN-06054: media recovery requesting unknown log: thread 1 seq 7 lowscn 482094

SQL> alter database open read only;

Database altered.

SQL> conn scott/abcdef
Connected.
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
T1 TABLE
OJECTS TABLE

6 rows selected.

[@more@]

ITPub博客

rman恢复测试2

最新文章