catalog database 的配置

简介：

RMAN要进行备份的数据库叫做目标数据库，RMAN是一个客户端程序，需要建立到目标数据库的客户端连接，并在目标数据库上创建对应的服务器进程以及session，RMAN输入的各个命令，从客户端传递到服务器，由服务器进程负责执行，RMAN只能使用专用服务器连接，不能使用共享服务器连接
备份过程中，RMAN读取目标数据库的控制文件，从而获得目标数据库里包含哪些数据文件、归档日志文件、以及各个检查点SCN，指导RMAN完成备份和恢复的具体操作。

前置步骤：

开启归档模式

alter database archivelog
1

1.建立存放rman 元数据的tablespace

create tablespace rman_tbs datafile ‘/rman/newdababase/datafile/rman_tbs.dbf’ size 50m autoextend off;

2.建立user，用于管理rman

create user rman identified by rman default tablespace rman_tbs;
1

3.授权

grant connect,resource,recovery_catalog_owner to rman;
1

4.使用rman连接，确保恢复目录与数据库服务器计算机上的Oracle Database 11g RMAN客户端兼容。

rman target sys/oracle@prod3 catalog rman/rman@jfdb;
创建表空间
RMAN> create catalog tablespace rman_tbs;
1
2
3

5.注册目标数据库，将prod3实例注册到jfdb实例中

rman target sys/oracle@prod3 catalog rman/rman@jfdb;
注册数据库
register database;

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
1
2
3
4
5
6
7
8
9

6.查看数据库信息

report schema;

RMAN> report schema;

Report of database schema for database with db_unique_name JFDB
1
2
3

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    700      SYSTEM               YES     /rman/newdababase/datafile/system01.dbf
2    600      SYSAUX               NO      /rman/newdababase/datafile/sysaux01.dbf
3    340      UNDOTBS1             YES     /rman/newdababase/datafile/undotbs01.dbf
4    5        USERS                NO      /rman/newdababase/datafile/users01.dbf
5    20       TBS_03               NO      /rman/newdababase/datafile/tbs_03.dbf
6    20       TBS_03               NO      /rman/newdababase/datafile/tbs_03_02.dbf
7    20       YY                   NO      /rman/newdababase/datafile/yy.dbf
8    50       RMAN_TBS             NO      /rman/newdababase/datafile/rman_tbs.dbf
1
2
3
4
5
6
7
8
9
10
11
12

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       /rman/newdababase/datafile/temp01.dbf
1
2
3
4
5

6.升级 catalog

upgrade catalog;
upgrade catalog; --敲两遍！

RMAN> upgrade catalog;

recovery catalog owner is RMAN
enter UPGRADE CATALOG command again to confirm catalog upgrade

RMAN> upgrade catalog

recovery catalog upgraded to version 11.02.00.04
DBMS_RCVMAN package upgraded to version 11.02.00.04
DBMS_RCVCAT package upgraded to version 11.02.00.04
1
2
3
4
5
6
7
8
9
10

7.查看rman基本配置

show all;

RMAN> show all;

RMAN configuration parameters for database with db_unique_name JFDB are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default  备份保留策略
CONFIGURE BACKUP OPTIMIZATION OFF; # default  备份优化算法
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default  默认备份选择磁盘通道
CONFIGURE CONTROLFILE AUTOBACKUP ON; #控制文件自动备份
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/rman/%F';  #备份路径格式
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 '/u01/app/oracle/product/11.2.0/dbs/snapcf_jfdb.f'; # default
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

将控制文件自动备份参数和备份保留策略参数打开
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE CONTROLFILE AUTOBACKUP ON;

RMAN> CONFIGURE BACKUP OPTIMIZATION ON;

new RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

MAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

8.开始备份

backup database plus archivelog;

RMAN> backup database plus archivelog;


Starting backup at 04-SEP-22
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
skipping archived logs of thread 1 from sequence 65 to 70; already backed up
skipping archived logs of thread 1 from sequence 3 to 11; already backed up
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=16 RECID=29 STAMP=1114555842
channel ORA_DISK_1: starting piece 1 at 04-SEP-22
channel ORA_DISK_1: finished piece 1 at 04-SEP-22
piece handle=/flash_recover_area/JFDB/backupset/2022_09_04/o1_mf_annnn_TAG20220904T225043_kk9gt38q_.bkp tag=TAG20220904T225043 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 04-SEP-22

Starting backup at 04-SEP-22
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=/rman/newdababase/datafile/system01.dbf
input datafile file number=00002 name=/rman/newdababase/datafile/sysaux01.dbf
input datafile file number=00003 name=/rman/newdababase/datafile/undotbs01.dbf
input datafile file number=00008 name=/rman/newdababase/datafile/rman_tbs.dbf
input datafile file number=00005 name=/rman/newdababase/datafile/tbs_03.dbf
input datafile file number=00006 name=/rman/newdababase/datafile/tbs_03_02.dbf
input datafile file number=00007 name=/rman/newdababase/datafile/yy.dbf
input datafile file number=00004 name=/rman/newdababase/datafile/users01.dbf
channel ORA_DISK_1: starting piece 1 at 04-SEP-22
channel ORA_DISK_1: finished piece 1 at 04-SEP-22
piece handle=/flash_recover_area/JFDB/backupset/2022_09_04/o1_mf_nnndf_TAG20220904T225044_kk9gt4wl_.bkp tag=TAG20220904T225044 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 04-SEP-22

Starting backup at 04-SEP-22
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=17 RECID=30 STAMP=1114555870
channel ORA_DISK_1: starting piece 1 at 04-SEP-22
channel ORA_DISK_1: finished piece 1 at 04-SEP-22
piece handle=/flash_recover_area/JFDB/backupset/2022_09_04/o1_mf_annnn_TAG20220904T225110_kk9gtyjc_.bkp tag=TAG20220904T225110 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 04-SEP-22

Starting Control File and SPFILE Autobackup at 04-SEP-22
piece handle=/rman/c-1479428695-20220904-00 comment=NONE
Finished Control File and SPFILE Autobackup at 04-SEP-22
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

其它备份方式：

为数据库PROD1执行备份。在任何情况下都可以恢复。标记为“Q107”，并长期保留

rman target sys/oracle@prod3 catalog rman/rman@jfdb;
backup database format '/home/oracle/backup/backup_q107_%U' tag 'Q107' keep forever;
1
2

1.PROD3数据库中示例数据文件的备份过程需要很长时间。执行必要的配置以加快备份过程并减小备份文件的大小。每个备份块的大小不应大于30 MB。

backup as compressed backupset tablespace EXAMPLE format '/home/oracle/backup/prod3_30m_%U' section size 30m keep forever;
1

2.将PROD3数据库上的快速增量备份的开销降至最低。

sqlplus sys/oracle@prod3 as sysdba;
alter database enable block change tracking using file '/u01/app/oracle/oradata/PROD1/block_change_tracking.f' ;
1
2

3.level 0/level 1/level 2 backups.

backup incremental level 0 database;
1

9.开始恢复

恢复方法
模拟删除数据文件目录下的所有文件

1.关闭数据库

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
1
2
3
4

2.删除数据文件

[oracle@jfdb 2022_09_04]$ cd /rman/newdababase/datafile/
[oracle@jfdb datafile]$ ll
总用量 1798760
-rw-r----- 1 oracle oinstall  52436992 9月   4 23:49 rman_tbs.dbf
-rw-r----- 1 oracle oinstall 629153792 9月   4 23:52 sysaux01.dbf
-rw-r----- 1 oracle oinstall 734011392 9月   4 23:50 system01.dbf
-rw-r----- 1 oracle oinstall  20979712 9月   4 23:49 tbs_03_02.dbf
-rw-r----- 1 oracle oinstall  20979712 9月   4 23:49 tbs_03.dbf
-rw-r----- 1 oracle oinstall  20979712 9月   4 23:47 temp01.dbf
-rw-r----- 1 oracle oinstall 356524032 9月   4 23:50 undotbs01.dbf
-rw-r----- 1 oracle oinstall   5251072 9月   4 23:49 users01.dbf
-rw-r----- 1 oracle oinstall  20979712 9月   4 23:49 yy.dbf
[oracle@jfdb datafile]$ rm -rf /rman/newdababase/datafile/yy.dbf
1
2
3
4
5
6
7
8
9
10
11
12
13

3.启动数据库

SQL> startup
ORACLE instance started.

Total System Global Area 1653518336 bytes
Fixed Size		    2253784 bytes
Variable Size		  973081640 bytes
Database Buffers	  671088640 bytes
Redo Buffers		    7094272 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/rman/newdababase/datafile/yy.dbf'
1
2
3
4
5
6
7
8
9
10
11

4.使用以下命令进行恢复
rman target sys/oracle@prod3 catalog rman/rman@JFDB;
list failure; # LIST FAILURE查看数据库状态

RMAN> list failure; 

using target database control file instead of recovery catalog
List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
2243       CRITICAL OPEN      05-SEP-22     System datafile 1: '/rman/newdababase/datafile/system01.dbf' is missing
262        HIGH     OPEN      05-SEP-22     One or more non-system datafiles are missing
 

1
2
3
4
5
6
7
8
9
10
11
12

advise failure; #ADVISE FAILURE查看建议

RMAN> advise failure;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
2243       CRITICAL OPEN      05-SEP-22     System datafile 1: '/rman/newdababase/datafile/system01.dbf' is missing
262        HIGH     OPEN      05-SEP-22     One or more non-system datafiles are missing

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
1. If file /rman/newdababase/datafile/system01.dbf was unintentionally renamed or moved, restore it
2. If file /rman/newdababase/datafile/sysaux01.dbf was unintentionally renamed or moved, restore it
3. If file /rman/newdababase/datafile/undotbs01.dbf was unintentionally renamed or moved, restore it
4. If file /rman/newdababase/datafile/users01.dbf was unintentionally renamed or moved, restore it
5. If file /rman/newdababase/datafile/tbs_03.dbf was unintentionally renamed or moved, restore it
6. If file /rman/newdababase/datafile/tbs_03_02.dbf was unintentionally renamed or moved, restore it
7. If file /rman/newdababase/datafile/yy.dbf was unintentionally renamed or moved, restore it
8. If file /rman/newdababase/datafile/rman_tbs.dbf was unintentionally renamed or moved, restore it

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Restore and recover database  
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /u01/app/oracle/diag/rdbms/jfdb/jfdb/hm/reco_1306055677.hm
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

repair failure; #根据建议自动恢复

RMAN> repair failure
2> ;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/jfdb/jfdb/hm/reco_1306055677.hm

contents of repair script:
   # restore and recover database
   restore database;
   recover database;

Do you really want to execute the above repair (enter YES or NO)? yes
executing repair script

Starting restore at 05-SEP-22
using channel ORA_DISK_1

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 /rman/newdababase/datafile/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /rman/newdababase/datafile/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /rman/newdababase/datafile/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /rman/newdababase/datafile/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /rman/newdababase/datafile/tbs_03.dbf
channel ORA_DISK_1: restoring datafile 00006 to /rman/newdababase/datafile/tbs_03_02.dbf
channel ORA_DISK_1: restoring datafile 00007 to /rman/newdababase/datafile/yy.dbf
channel ORA_DISK_1: restoring datafile 00008 to /rman/newdababase/datafile/rman_tbs.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/backup_q107_2116tp5p_1_1
channel ORA_DISK_1: piece handle=/home/oracle/backup/backup_q107_2116tp5p_1_1 tag=Q107
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 05-SEP-22

Starting recover at 05-SEP-22
using channel ORA_DISK_1

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

Finished recover at 05-SEP-22
repair failure complete

Do you want to open the database (enter YES or NO)? yes
database opened
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

恢复完成