oracle rman备份恢复

联机备份：

1、查看rman的配置参数

[oracle@myoracle ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Jan 3 09:21:36 2023

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DEYDB (DBID=385778334)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name DEYDB are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
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 COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_deydb.f'; # default

2、设置归档模式、闪回恢复区

归档模式

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1586708480 bytes
Fixed Size                  2253624 bytes
Variable Size             989859016 bytes
Database Buffers          587202560 bytes
Redo Buffers                7393280 bytes
Database 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            /home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence     65
Next log sequence to archive   67
Current log sequence           67
SQL> 

闪回恢复区

SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=4G;

System altered.

SQL> alter system set db_recovery_file_dest='/home/oracle/arch_log';

System altered.

SQL> show parameter db_recovery_file_dest;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /home/oracle/arch_log
db_recovery_file_dest_size           big integer 4G
SQL> 

3、创建恢复目录、登录用户

表空间

SQL> create tablespace rman_ts datafile '/home/oracle/app/oracle/oradata/deydb/rman_ts1.dbf' size 2G autoextend on next 500M maxsize 6G;

Tablespace created.

登录用户

SQL> create user rman_reuser identified by 123456 default tablespace rman_ts temporary tablespace temp;

User created.

SQL> grant connect,resource,recovery_catalog_owner to rman_reuser;

Grant succeeded.

SQL> 

Rman模式下创建恢复目录，名字和表空间一样

[oracle@myoracle ~]$ rman target/ catalog rman_reuser/123456;

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Jan 3 10:57:05 2023

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DEYDB (DBID=385778334)
connected to recovery catalog database

RMAN> create catalog tablespace rman_ts;

recovery catalog created

注册数据库

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN> 

4、设置备份文件和控制文件保存目录

备份文件保存目录

RMAN> configure channel device type disk format '/home/oracle/backup/data_%d_%M_%U';

new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/home/oracle/backup/data_%d_%M_%U';
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

控制文件默认存放位置

RMAN> configure controlfile autobackup format for device type disk to '/home/oracle/backup/ctl_%d_%M_%F';

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/oracle/backup/ctl_%d_%M_%F';
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

RMAN> 

5、联机备份

全备

RMAN> backup as compressed backupset database plus archivelog delete all input;


Starting backup at 03-JAN-23
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=201 device type=DISK
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=67 RECID=1 STAMP=1125141177
channel ORA_DISK_1: starting piece 1 at 03-JAN-23
channel ORA_DISK_1: finished piece 1 at 03-JAN-23
piece handle=/home/oracle/backup/data_DEYDB_01_011h0ils_1_1 tag=TAG20230103T111300 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/home/oracle/arch_log/DEYDB/archivelog/2023_01_03/o1_mf_1_67_kv779lx8_.arc RECID=1 STAMP=1125141177
Finished backup at 03-JAN-23

Starting backup at 03-JAN-23
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/home/oracle/app/oracle/oradata/deydb/rman_ts1.dbf
input datafile file number=00005 name=/home/oracle/app/oracle/oradata/deydb/undotbs02.dbf
input datafile file number=00001 name=/home/oracle/app/oracle/oradata/deydb/system01.dbf
input datafile file number=00002 name=/home/oracle/app/oracle/oradata/deydb/sysaux01.dbf
input datafile file number=00006 name=/home/oracle/app/oracle/oradata/deydb/undotbs03.dbf
input datafile file number=00003 name=/home/oracle/app/oracle/oradata/deydb/undotbs01.dbf
input datafile file number=00004 name=/home/oracle/app/oracle/oradata/deydb/users01.dbf
channel ORA_DISK_1: starting piece 1 at 03-JAN-23
channel ORA_DISK_1: finished piece 1 at 03-JAN-23
piece handle=/home/oracle/backup/data_DEYDB_01_021h0im1_1_1 tag=TAG20230103T111304 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting compressed 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 03-JAN-23
channel ORA_DISK_1: finished piece 1 at 03-JAN-23
piece handle=/home/oracle/backup/data_DEYDB_01_031h0io2_1_1 tag=TAG20230103T111304 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 03-JAN-23

Starting backup at 03-JAN-23
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=68 RECID=2 STAMP=1125141253
channel ORA_DISK_1: starting piece 1 at 03-JAN-23
channel ORA_DISK_1: finished piece 1 at 03-JAN-23
piece handle=/home/oracle/backup/data_DEYDB_01_041h0io5_1_1 tag=TAG20230103T111413 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/home/oracle/arch_log/DEYDB/archivelog/2023_01_03/o1_mf_1_68_kv77d4w8_.arc RECID=2 STAMP=1125141253
Finished backup at 03-JAN-23

RMAN> 

脚本命令

RMAN> run {

allocate channel ch1 device type disk;

allocate channel ch2 device type disk;

backup database format '/home/oracle/backup/Data_%d_%M_%U'

plus archivelog format '/home/oracle/backup/log_%d_%M_%U';

release channel ch1;

release channel ch2;
}

输出结果
allocated channel: ch1
channel ch1: SID=201 device type=DISK

allocated channel: ch2
channel ch2: SID=13 device type=DISK


Starting backup at 03-JAN-23
current log archived
channel ch1: starting archived log backup set
channel ch1: specifying archived log(s) in backup set
input archived log thread=1 sequence=69 RECID=3 STAMP=1125148043
channel ch1: starting piece 1 at 03-JAN-23
channel ch2: starting archived log backup set
channel ch2: specifying archived log(s) in backup set
input archived log thread=1 sequence=70 RECID=4 STAMP=1125148122
input archived log thread=1 sequence=71 RECID=5 STAMP=1125148165
channel ch2: starting piece 1 at 03-JAN-23
channel ch1: finished piece 1 at 03-JAN-23
piece handle=/home/oracle/backup/log_DEYDB_01_091h0pg6_1_1 tag=TAG20230103T130926 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
channel ch2: finished piece 1 at 03-JAN-23
piece handle=/home/oracle/backup/log_DEYDB_01_0a1h0pg6_1_1 tag=TAG20230103T130926 comment=NONE
channel ch2: backup set complete, elapsed time: 00:00:01
Finished backup at 03-JAN-23

Starting backup at 03-JAN-23
channel ch1: starting full datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00007 name=/home/oracle/app/oracle/oradata/deydb/rman_ts1.dbf
input datafile file number=00006 name=/home/oracle/app/oracle/oradata/deydb/undotbs03.dbf
input datafile file number=00003 name=/home/oracle/app/oracle/oradata/deydb/undotbs01.dbf
channel ch1: starting piece 1 at 03-JAN-23
channel ch2: starting full datafile backup set
channel ch2: specifying datafile(s) in backup set
input datafile file number=00005 name=/home/oracle/app/oracle/oradata/deydb/undotbs02.dbf
input datafile file number=00001 name=/home/oracle/app/oracle/oradata/deydb/system01.dbf
input datafile file number=00002 name=/home/oracle/app/oracle/oradata/deydb/sysaux01.dbf
input datafile file number=00004 name=/home/oracle/app/oracle/oradata/deydb/users01.dbf
channel ch2: starting piece 1 at 03-JAN-23
channel ch1: finished piece 1 at 03-JAN-23
piece handle=/home/oracle/backup/Data_DEYDB_01_0b1h0pg8_1_1 tag=TAG20230103T130927 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:07
channel ch1: starting full datafile backup set
channel ch1: specifying datafile(s) in backup set
including current control file in backup set
channel ch1: starting piece 1 at 03-JAN-23
channel ch1: finished piece 1 at 03-JAN-23
piece handle=/home/oracle/backup/Data_DEYDB_01_0d1h0pgf_1_1 tag=TAG20230103T130927 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
channel ch1: starting full datafile backup set
channel ch1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ch1: starting piece 1 at 03-JAN-23
channel ch1: finished piece 1 at 03-JAN-23
piece handle=/home/oracle/backup/Data_DEYDB_01_0e1h0pgh_1_1 tag=TAG20230103T130927 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
channel ch2: finished piece 1 at 03-JAN-23
piece handle=/home/oracle/backup/Data_DEYDB_01_0c1h0pg8_1_1 tag=TAG20230103T130927 comment=NONE
channel ch2: backup set complete, elapsed time: 00:02:02
Finished backup at 03-JAN-23

Starting backup at 03-JAN-23
current log archived
channel ch1: starting archived log backup set
channel ch1: specifying archived log(s) in backup set
input archived log thread=1 sequence=72 RECID=6 STAMP=1125148290
channel ch1: starting piece 1 at 03-JAN-23
channel ch1: finished piece 1 at 03-JAN-23
piece handle=/home/oracle/backup/log_DEYDB_01_0f1h0pk4_1_1 tag=TAG20230103T131132 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 03-JAN-23

released channel: ch1

released channel: ch2

RMAN> 

6、数据文件被破坏

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@myoracle ~]$ cd app/oracle/oradata/deydb/
[oracle@myoracle deydb]$ pwd
/home/oracle/app/oracle/oradata/deydb
[oracle@myoracle deydb]$ mv users01.dbf users01.dbfBAK
[oracle@myoracle deydb]$ ll
total 5625628
...
-rw-r-----. 1 oracle oinstall 2147491840 Jan  3 13:53 rman_ts1.dbf
...
-rw-r-----. 1 oracle oinstall    5251072 Jan  3 13:44 users01.dbfBAK

启动数据库

SQL> startup
ORACLE instance started.

Total System Global Area 1586708480 bytes
Fixed Size                  2253624 bytes
Variable Size             989859016 bytes
Database Buffers          587202560 bytes
Redo Buffers                7393280 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/home/oracle/app/oracle/oradata/deydb/users01.dbf'

由于数据文件已破坏，需将损坏的数据文件先offline再打开

SQL> alter database datafile 4 offline;

Database altered.

SQL> alter database open;

Database altered.

SQL> 

7、恢复数据文件

Rman模式下

RMAN> restore datafile 4;

Starting restore at 03-JAN-23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=196 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 00004 to /home/oracle/app/oracle/oradata/deydb/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/Data_DEYDB_01_0c1h0pg8_1_1
channel ORA_DISK_1: piece handle=/home/oracle/backup/Data_DEYDB_01_0c1h0pg8_1_1 tag=TAG20230103T130927
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 03-JAN-23

RMAN> recover datafile 4;

Starting recover at 03-JAN-23
using channel ORA_DISK_1

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

Finished recover at 03-JAN-23

RMAN> 

数据文件恢复成功，将数据文件online

SQL> alter database datafile 4 online;

Database altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@myoracle deydb]$ ll
total 5625628
...
-rw-r-----. 1 oracle oinstall 2147491840 Jan  3 13:53 rman_ts1.dbf
...
-rw-r-----. 1 oracle oinstall    5251072 Jan  3 13:57 users01.dbf
-rw-r-----. 1 oracle oinstall    5251072 Jan  3 13:44 users01.dbfBAK