-------------------------------(1)查看归档模式
[oracle@h1 ~]$ sqlplus "/as SYSDBA"
SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 13 23:04:08 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 18
Current log sequence 20
SQL> --------------#归档模式未启动
-------------------------------(2)启动归档模式
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 501059584 bytes
Fixed Size 2214736 bytes
Variable Size 373294256 bytes
Database Buffers 121634816 bytes
Redo Buffers 3915776 bytes
Database mounted.
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 18
Next log sequence to archive 20
Current log sequence 20
SQL>
-------------------------------(3)建立目录
[oracle@h1 ~]$ cd /home/oracle
[oracle@h1 ~]$ mkdir dbbackup
------------------注:需要时备份文件夹需要授权 chmod 777 dbbackup
-------------------------------(4)Rman 登陆
Last login: Tue Nov 13 22:44:07 2012 from 192.168.5.1
[oracle@h1 ~]$ rman target /
-----------------------
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Nov 13 23:27:30 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1326142752)
RMAN> exit
Recovery Manager complete.
[oracle@h1 ~]$ rman target u01/abc
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Nov 13 23:27:52 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1326142752)
-------------------------------(5)rman 全备份
RMAN> backup database format='/home/oracle/dbbackup/db_full_%d_%s_%T.dbf';
------------_%d_%s_%T是
-----------%d 是 数据库name
-----------%s 是 数据库第几次备份
-----------%T 是 时间戳
Starting backup at 13-NOV-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00002 name=/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00003 name=/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00005 name=/app/oracle/oradata/orcl/example01.dbf
input datafile file number=00004 name=/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 13-NOV-12
channel ORA_DISK_1: finished piece 1 at 13-NOV-12
piece handle=/home/oracle/dbbackup/db_full_ORCL_3_20121113.dbf tag=TAG20121113T233510 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:06
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 13-NOV-12
channel ORA_DISK_1: finished piece 1 at 13-NOV-12
piece handle=/home/oracle/dbbackup/db_full_ORCL_4_20121113.dbf tag=TAG20121113T233510 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 13-NOV-12
RMAN> exit
Recovery Manager complete.
[oracle@h1 ~]$ cd /home/oracle/dbbackup/
[oracle@h1 dbbackup]$ ls
--------备份结果
db_full_ORCL_3_20121113.dbf db_full_ORCL_4_20121113.dbf
-------------------------------(4)Rman 恢复时间点数据
-----------*用户u01建表
SQL> conn u01/abc
Connected.
SQL> create table salgrade as select * from scott.salgrade;
Table created. -----------*Rman全备
RMAN> backup database format='/home/oracle/dbbackup/db_full_%d_%s_%T.dbf';
Starting backup at 13-NOV-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00002 name=/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00003 name=/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00005 name=/app/oracle/oradata/orcl/example01.dbf
input datafile file number=00004 name=/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 13-NOV-12
channel ORA_DISK_1: finished piece 1 at 13-NOV-12
piece handle=/home/oracle/dbbackup/db_full_ORCL_5_20121113.dbf tag=TAG20121113T234553 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 13-NOV-12
channel ORA_DISK_1: finished piece 1 at 13-NOV-12
piece handle=/home/oracle/dbbackup/db_full_ORCL_6_20121113.dbf tag=TAG20121113T234553 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 13-NOV-12
RMAN> exit
Recovery Manager complete.
[oracle@h1 dbbackup]$ cd /home/oracle/dbbackup/
[oracle@h1 dbbackup]$ ls
db_full_ORCL_3_20121113.dbf db_full_ORCL_5_20121113.dbf
db_full_ORCL_4_20121113.dbf db_full_ORCL_6_20121113.dbf
----------------蓝色体为最新全备 -----------*u01 drop表
SQL> drop table salgrade;
Table dropped.
SQL> -----------*数据库进入mount状态
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 501059584 bytes
Fixed Size 2214736 bytes
Variable Size 373294256 bytes
Database Buffers 121634816 bytes
Redo Buffers 3915776 bytes
Database mounted.
SQL>
-----------*rman restore
RMAN> restore database;
Starting restore at 13-NOV-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/dbbackup/db_full_ORCL_5_20121113.dbf
channel ORA_DISK_1: piece handle=/home/oracle/dbbackup/db_full_ORCL_5_20121113.dbf tag=TAG20121113T234553
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:39
Finished restore at 13-NOV-12
-----------*rman recover
RMAN> recover database;
Starting recover at 14-NOV-12
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 14-NOV-12
-----------*数据库open
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> alter database open;
Database altered.
SQL>
SQL> conn u01/abc
Connected.
SQL> select * from salgrade;
select * from salgrade
*
ERROR at line 1:
ORA-00942: table or view does not exist
---------------------------------------因为没有设置时间点数据库recover后日志redo到drop table阶段
-----------------解决办法
[oracle@h1 dbbackup]$ pwd
/home/oracle/dbbackup
[oracle@h1 dbbackup]$ ls
db_full_ORCL_11_20121114.dbf db_full_ORCL_12_20121114.dbf
RMAN> run{
2> set until time "to_date('2012-11-14 01:06:00','yyyy-mm-dd hh24:mi:ss')";
3> restore database;
4> recover database;
5> sql 'alter database open resetlogs';
6> }
executing command: SET until clause
Starting restore at 14-NOV-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/dbbackup/db_full_ORCL_11_20121114.dbf
channel ORA_DISK_1: piece handle=/home/oracle/dbbackup/db_full_ORCL_11_20121114.dbf tag=TAG20121114T010321
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:05
Finished restore at 14-NOV-12
Starting recover at 14-NOV-12
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 14-NOV-12
sql statement: alter database open resetlogs
----------------(5)Rman 恢复datafile丢失
-----------*模拟datafile丢失
[oracle@h1 orcl]$ sqlplus "/as SYSDBA"
SQL*Plus: Release 11.2.0.1.0 Production on Wed Nov 14 01:45:17 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@h1 orcl]$ pwd
/app/oracle/oradata/orcl
[oracle@h1 orcl]$ mv users01.dbf users01.dbf.bak
[oracle@h1 orcl]$ ls
control01.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf.bak
[oracle@h1 orcl]$ sqlplus "/as SYSDBA"
SQL*Plus: Release 11.2.0.1.0 Production on Wed Nov 14 01:46:56 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 501059584 bytes
Fixed Size 2214736 bytes
Variable Size 373294256 bytes
Database Buffers 121634816 bytes
Redo Buffers 3915776 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/app/oracle/oradata/orcl/users01.dbf'
----------------------数据文件users01.dbf丢失,数据库无法启动
SQL> SQL> SQL> SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 501059584 bytes
Fixed Size 2214736 bytes
Variable Size 373294256 bytes
Database Buffers 121634816 bytes
Redo Buffers 3915776 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
-----------*Rman恢复数据文件
[oracle@h1 orcl]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Nov 14 01:54:07 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1326142752, not open)
RMAN> restore database;
Starting restore at 14-NOV-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/dbbackup/db_full_ORCL_11_20121114.dbf
channel ORA_DISK_1: piece handle=/home/oracle/dbbackup/db_full_ORCL_11_20121114.dbf tag=TAG20121114T010321
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:04:06
Finished restore at 14-NOV-12
RMAN> recover database;
Starting recover at 14-NOV-12
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 1 is already on disk as file /app/oracle/flash_recovery_area/ORCL/archivelog/2012_11_14/o1_mf_1_1_8b50djx1_.arc
archived log file name=/app/oracle/flash_recovery_area/ORCL/archivelog/2012_11_14/o1_mf_1_1_8b50djx1_.arc thread=1 sequence=1
media recovery complete, elapsed time: 00:00:15
Finished recover at 14-NOV-12
RMAN> exit
Recovery Manager complete.
-----------*数据库启动
[oracle@h1 orcl]$ sqlplus "/as SYSDBA"
SQL*Plus: Release 11.2.0.1.0 Production on Wed Nov 14 02:01:41 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter database open;
Database altered.
SQL>