oracle rman 冷备,数据库冷备份及RMAN简单使用

本文详细介绍了Oracle数据库从归档模式开启、全量备份、时间点恢复到数据文件丢失后的恢复操作。通过SQL*Plus和RMAN,展示了如何管理数据库的状态、执行备份以及在不同场景下进行数据恢复,确保数据库的安全和可用性。
摘要由CSDN通过智能技术生成

-------------------------------(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>

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值