RMAN恢复实验(2)-Controlfile恢复及重建Controlfile

继RMAN恢复实验(1)- SPFILE恢复实验后,继续进行RMAN其他相关重要文件的恢复实验,本次实验恢复的目标为-Controlfile 控制文件:

场景(1)RAC集群架构 + RMAN AUTOBACKUP

模拟CONTROLFILE丢失:删除ASM中存储的controlfile01与controlfile02

CON1-1

启动数据库抛出control file错误,此时数据库实例状态为NOMOUNT状态:

CON1-2

我们进入RMAN客户端尝试利用Controlfile Autobackup方式(需要提前配置autoback on,如果没有配置会将controlfile与spfile加入到备份集中),恢复controlfile.同恢复SPFILE时一致,需要先指定DBID后进行restore操作

CON1-3

CON1-4

我们看到,ASM中的CONTROLFILE已恢复成功,接下来便是启动该数据库操作:

CON1-5

 

因为我们使用的是BACKUP CONTROLFILE,所以需要recover database操作,并且开库必须为open resetlogs模式(如果归档存在,可不丢数据,但是概念上仍未不完全恢复)

PS: CATALOG模式下,可直接restore controlfile 元数据信息不再依赖DB CONTROLFILE。

场景2:RAC集群架构,重建控制文件:

(1) NOCATALOGS:

重建控制文件前,需要将cluster_database改为false,否则无法创建control file
SQL> alter system set cluster_database=FALSE scope=spfile;
System altered.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1.3495E+11 bytes
Fixed Size 2270072 bytes
Variable Size 3.5970E+10 bytes
Database Buffers 9.8784E+10 bytes
Redo Buffers 197844992 bytes
创建控制文件 NORESETLOGS:
SQL> CREATE CONTROLFILE REUSE DATABASE "ORA" NORESETLOGS ARCHIVELOG
 2 MAXLOGFILES 192
 3 MAXLOGMEMBERS 3
 4 MAXDATAFILES 1024
 5 MAXINSTANCES 32
 6 MAXLOGHISTORY 292
 7 LOGFILE
 8 GROUP 1 (
 9 '+DG_REDO/ora/onlinelog/group_1.256.992871445',
 10 '+DG_REDO/ora/onlinelog/group_1.257.992871445'
 11 ) SIZE 300M BLOCKSIZE 512,
 12 GROUP 2 (
 13 '+DG_REDO/ora/onlinelog/group_2.258.992871447',
 14 '+DG_REDO/ora/onlinelog/group_2.259.992871447'
 15 ) SIZE 300M BLOCKSIZE 512,
 16 GROUP 3 (
 17 '+DG_REDO/ora/onlinelog/group_3.260.992871449',
 18 '+DG_REDO/ora/onlinelog/group_3.261.992871449'
 19 ) SIZE 300M BLOCKSIZE 512,
 20 GROUP 4 (
 21 '+DG_REDO/ora/onlinelog/group_4.262.992871451',
 22 '+DG_REDO/ora/onlinelog/group_4.263.992871451'
 23 ) SIZE 300M BLOCKSIZE 512,
 24 GROUP 5 (
 25 '+DG_REDO/ora/onlinelog/group_5.265.1002900989',
 26 '+DG_REDO/ora/onlinelog/group_5.264.1002900989'
 27 ) SIZE 100M BLOCKSIZE 512,
 28 GROUP 6 (
 29 '+DG_REDO/ora/onlinelog/group_6.267.1002901009',
 30 '+DG_REDO/ora/onlinelog/group_6.266.1002901009'
 31 ) SIZE 100M BLOCKSIZE 512,
 32 GROUP 7 (
 33 '+DG_REDO/ora/onlinelog/group_7.268.992871457',
 34 '+DG_REDO/ora/onlinelog/group_7.269.992871457'
 35 ) SIZE 300M BLOCKSIZE 512,
 36 GROUP 8 (
 37 '+DG_REDO/ora/onlinelog/group_8.270.992871459',
 38 '+DG_REDO/ora/onlinelog/group_8.271.992871459'
 39 ) SIZE 300M BLOCKSIZE 512,
 40 GROUP 9 (
 41 '+DG_REDO/ora/onlinelog/group_9.272.992871461',
 42 '+DG_REDO/ora/onlinelog/group_9.273.992871461'
 43 ) SIZE 300M BLOCKSIZE 512,
 44 GROUP 10 (
 45 '+DG_REDO/ora/onlinelog/group_10.274.992871463',
 46 '+DG_REDO/ora/onlinelog/group_10.275.992871463'
 47 ) SIZE 300M BLOCKSIZE 512,
 48 GROUP 11 (
 49 '+DG_REDO/ora/onlinelog/group_11.291.1002901021',
 50 '+DG_REDO/ora/onlinelog/group_11.290.1002901021'
 51 ) SIZE 100M BLOCKSIZE 512,
 52 GROUP 12 (
 53 '+DG_REDO/ora/onlinelog/group_12.277.1002901029',
 54 '+DG_REDO/ora/onlinelog/group_12.276.1002901029'
 55 ) SIZE 100M BLOCKSIZE 512,
 56 GROUP 13 (
 57 '+DG_REDO/ora/onlinelog/group_13.280.992871467',
 58 '+DG_REDO/ora/onlinelog/group_13.281.992871469'
 59 ) SIZE 300M BLOCKSIZE 512,
 60 GROUP 14 (
 61 '+DG_REDO/ora/onlinelog/group_14.282.992871469',
 62 '+DG_REDO/ora/onlinelog/group_14.283.992871471'
 63 ) SIZE 300M BLOCKSIZE 512,
 64 GROUP 15 (
 65 '+DG_REDO/ora/onlinelog/group_15.284.992871471',
 66 '+DG_REDO/ora/onlinelog/group_15.285.992871473'
 67 ) SIZE 300M BLOCKSIZE 512,
 68 GROUP 16 (
 69 '+DG_REDO/ora/onlinelog/group_16.286.992871473',
 70 '+DG_REDO/ora/onlinelog/group_16.287.992871475'
 71 ) SIZE 300M BLOCKSIZE 512,
 72 GROUP 17 (
 73 '+DG_REDO/ora/onlinelog/group_17.279.1002901091',
 74 '+DG_REDO/ora/onlinelog/group_17.278.1002901091'
 75 ) SIZE 100M BLOCKSIZE 512,
 76 GROUP 18 (
 77 '+DG_REDO/ora/onlinelog/group_18.288.1002980873',
 78 '+DG_REDO/ora/onlinelog/group_18.289.1002980873'
 79 ) SIZE 100M BLOCKSIZE 512
 80 -- STANDBY LOGFILE
 81 DATAFILE
 82 '+DG_DATA/ora/datafile/system.259.992870715',
 83 '+DG_DATA/ora/datafile/sysaux.260.992870723',
 84 '+DG_DATA/ora/datafile/undotbs1.261.992870729',
 85 '+DG_DATA/ora/datafile/undotbs2.263.992870739',
 86 '+DG_DATA/ora/datafile/undotbs3.264.992870745',
 87 '+DG_DATA/ora/datafile/users.265.992870751',
 88 '+DG_DATA/ora/datafile/checkpoint_info.268.1004708615',
 89 '+DG_DATA/ora/datafile/albert_dat.272.1015595177',
 90 '+DG_DATA/ora/datafile/travelskydba_dat.258.1005904905',
 91 '+DG_DATA/ora/datafile/travelskydba_idx.257.1005904921'
 92 CHARACTER SET AL32UTF8
 93 ;

Control file created.
 SQL> -- Configure RMAN configuration record 1
 SQL> VARIABLE RECNO NUMBER;
 SQL> EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG
      ('CONTROLFILE AUTOBACKUP','ON');
PL/SQL procedure successfully completed.

SQL> -- Configure RMAN configuration record 2
SQL> VARIABLE RECNO NUMBER;
SQL> EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE',
'DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET');
PL/SQL procedure successfully completed.

SQL> -- Configure RMAN configuration record 3
SQL> VARIABLE RECNO NUMBER;
SQL> EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG
('CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE','DISK TO ''%F''');
PL/SQL procedure successfully completed.

SQL> -- Configure RMAN configuration record 4
SQL> VARIABLE RECNO NUMBER;
SQL> EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG
('COMPRESSION ALGORITHM','''high'' AS OF RELEASE ''DEFAULT'' OPTIMIZE 
FOR LOAD TRUE');PL/SQL procedure successfully completed.

SQL> VARIABLE RECNO NUMBER;
SQL> EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG
('BACKUP OPTIMIZATION','OFF');
PL/SQL procedure successfully completed.

SQL> RECOVER DATABASE
Media recovery complete.

可以直接OPEN数据库
SQL> alter database open
Database altered.

 

RESETLOGS:

重建控制文件前,需要将cluster_database改为false,否则无法创建control file
SQL> alter system set cluster_database=FALSE scope=spfile;
System altered.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1.3495E+11 bytes
Fixed Size 2270072 bytes
Variable Size 3.5970E+10 bytes
Database Buffers 9.8784E+10 bytes
Redo Buffers 197844992 bytes
重建控制文件:RESETLOGS:
SQL> CREATE CONTROLFILE REUSE DATABASE "ORA" RESETLOGS ARCHIVELOG
 2 MAXLOGFILES 192
 3 MAXLOGMEMBERS 3
 4 MAXDATAFILES 1024
 5 MAXINSTANCES 32
 6 MAXLOGHISTORY 292
 7 LOGFILE
 8 GROUP 1 (
 9 '+DG_REDO/ora/onlinelog/group_1.256.992871445',
 10 '+DG_REDO/ora/onlinelog/group_1.257.992871445'
 11 ) SIZE 300M BLOCKSIZE 512,
 12 GROUP 2 (
 13 '+DG_REDO/ora/onlinelog/group_2.258.992871447',
 14 '+DG_REDO/ora/onlinelog/group_2.259.992871447'
 15 ) SIZE 300M BLOCKSIZE 512,
 16 GROUP 3 (
 17 '+DG_REDO/ora/onlinelog/group_3.260.992871449',
 18 '+DG_REDO/ora/onlinelog/group_3.261.992871449'
 19 ) SIZE 300M BLOCKSIZE 512,
 20 GROUP 4 (
 21 '+DG_REDO/ora/onlinelog/group_4.262.992871451',
 22 '+DG_REDO/ora/onlinelog/group_4.263.992871451'
 23 ) SIZE 300M BLOCKSIZE 512,
 24 GROUP 5 (
 25 '+DG_REDO/ora/onlinelog/group_5.265.1002900989',
 26 '+DG_REDO/ora/onlinelog/group_5.264.1002900989'
 27 ) SIZE 100M BLOCKSIZE 512,
 28 GROUP 6 (
 29 '+DG_REDO/ora/onlinelog/group_6.267.1002901009',
 30 '+DG_REDO/ora/onlinelog/group_6.266.1002901009'
 31 ) SIZE 100M BLOCKSIZE 512,
 32 GROUP 7 (
 33 '+DG_REDO/ora/onlinelog/group_7.268.992871457',
 34 '+DG_REDO/ora/onlinelog/group_7.269.992871457'
 35 ) SIZE 300M BLOCKSIZE 512,
 36 GROUP 8 (
 37 '+DG_REDO/ora/onlinelog/group_8.270.992871459',
 38 '+DG_REDO/ora/onlinelog/group_8.271.992871459'
 39 ) SIZE 300M BLOCKSIZE 512,
 40 GROUP 9 (
 41 '+DG_REDO/ora/onlinelog/group_9.272.992871461',
 42 '+DG_REDO/ora/onlinelog/group_9.273.992871461'
 43 ) SIZE 300M BLOCKSIZE 512,
 44 GROUP 10 (
 45 '+DG_REDO/ora/onlinelog/group_10.274.992871463',
 46 '+DG_REDO/ora/onlinelog/group_10.275.992871463'
 47 ) SIZE 300M BLOCKSIZE 512,
 48 GROUP 11 (
 49 '+DG_REDO/ora/onlinelog/group_11.291.1002901021',
 50 '+DG_REDO/ora/onlinelog/group_11.290.1002901021'
 51 ) SIZE 100M BLOCKSIZE 512,
 52 GROUP 12 (
 53 '+DG_REDO/ora/onlinelog/group_12.277.1002901029',
 54 '+DG_REDO/ora/onlinelog/group_12.276.1002901029'
 55 ) SIZE 100M BLOCKSIZE 512,
 56 GROUP 13 (
 57 '+DG_REDO/ora/onlinelog/group_13.280.992871467',
 58 '+DG_REDO/ora/onlinelog/group_13.281.992871469'
 59 ) SIZE 300M BLOCKSIZE 512,
 60 GROUP 14 (
 61 '+DG_REDO/ora/onlinelog/group_14.282.992871469',
 62 '+DG_REDO/ora/onlinelog/group_14.283.992871471'
 63 ) SIZE 300M BLOCKSIZE 512,
 64 GROUP 15 (
 65 '+DG_REDO/ora/onlinelog/group_15.284.992871471',
 66 '+DG_REDO/ora/onlinelog/group_15.285.992871473'
 67 ) SIZE 300M BLOCKSIZE 512,
 68 GROUP 16 (
 69 '+DG_REDO/ora/onlinelog/group_16.286.992871473',
 70 '+DG_REDO/ora/onlinelog/group_16.287.992871475'
 71 ) SIZE 300M BLOCKSIZE 512,
 72 GROUP 17 (
 73 '+DG_REDO/ora/onlinelog/group_17.279.1002901091',
 74 '+DG_REDO/ora/onlinelog/group_17.278.1002901091'
 75 ) SIZE 100M BLOCKSIZE 512,
 76 GROUP 18 (
 77 '+DG_REDO/ora/onlinelog/group_18.288.1002980873',
 78 '+DG_REDO/ora/onlinelog/group_18.289.1002980873'
 79 ) SIZE 100M BLOCKSIZE 512
 80 -- STANDBY LOGFILE
 81 DATAFILE
 82 '+DG_DATA/ora/datafile/system.259.992870715',
 83 '+DG_DATA/ora/datafile/sysaux.260.992870723',
 84 '+DG_DATA/ora/datafile/undotbs1.261.992870729',
 85 '+DG_DATA/ora/datafile/undotbs2.263.992870739',
 86 '+DG_DATA/ora/datafile/undotbs3.264.992870745',
 87 '+DG_DATA/ora/datafile/users.265.992870751',
 88 '+DG_DATA/ora/datafile/checkpoint_info.268.1004708615',
 89 '+DG_DATA/ora/datafile/albert_dat.272.1015595177',
 90 '+DG_DATA/ora/datafile/travelskydba_dat.258.1005904905',
 91 '+DG_DATA/ora/datafile/travelskydba_idx.257.1005904921'
 92 CHARACTER SET AL32UTF8
 93 ;
Control file created.
-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG
('CONTROLFILE AUTOBACKUP','ON');
-- Configure RMAN configuration record 2
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE',
'DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET');
-- Configure RMAN configuration record 3
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE 
AUTOBACKUP FORMAT FOR DEVICE TYPE','DISK TO ''%F''');
-- Configure RMAN configuration record 4
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('COMPRESSION 
ALGORITHM','''high'' AS OF RELEASE ''DEFAULT'' OPTIMIZE FOR LOAD TRUE');
-- Configure RMAN configuration record 5
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('BACKUP 
OPTIMIZATION','OFF');
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '+DG_ARC';
-- ALTER DATABASE REGISTER LOGFILE '+DG_ARC';
-- ALTER DATABASE REGISTER LOGFILE '+DG_ARC';
-- ALTER DATABASE REGISTER LOGFILE '+DG_ARC';
-- ALTER DATABASE REGISTER LOGFILE '+DG_ARC';
-- ALTER DATABASE REGISTER LOGFILE '+DG_ARC';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE;
ORA-00279: change 38138260 generated at 08/06/2019 14:15:29 needed for 
           thread 3
ORA-00289: suggestion : +DG_ARC
ORA-00280: change 38138260 for thread 3 is in sequence #2
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
添加日志组 THREAD2:
SQL> ALTER DATABASE ADD LOGFILE THREAD 2
 2 GROUP 7 (
 3 '+DG_REDO',
 4 '+DG_REDO'
 5 ) SIZE 300M BLOCKSIZE 512 REUSE,
 6 GROUP 8 (
 7 '+DG_REDO',
 8 '+DG_REDO'
 9 ) SIZE 300M BLOCKSIZE 512 REUSE,
 10 GROUP 9 (
 11 '+DG_REDO',
 12 '+DG_REDO'
 13 ) SIZE 300M BLOCKSIZE 512 REUSE,
 14 GROUP 10 (
 15 '+DG_REDO',
 16 '+DG_REDO'
 17 ) SIZE 300M BLOCKSIZE 512 REUSE,
 18 GROUP 11 (
 19 '+DG_REDO',
 20 '+DG_REDO'
 21 ) SIZE 100M BLOCKSIZE 512 REUSE,
 22 GROUP 12 (
 23 '+DG_REDO',
 24 '+DG_REDO'
 25 ) SIZE 100M BLOCKSIZE 512 REUSE;
Database altered.
添加日志组THREAD 3
SQL> ALTER DATABASE ADD LOGFILE THREAD 3
 2 GROUP 13 (
 3 '+DG_REDO',
 4 '+DG_REDO'
 5 ) SIZE 300M BLOCKSIZE 512 REUSE,
 6 GROUP 14 (
 7 '+DG_REDO',
 8 '+DG_REDO'
 9 ) SIZE 300M BLOCKSIZE 512 REUSE,
 10 GROUP 15 (
 11 '+DG_REDO',
 12 '+DG_REDO'
 13 ) SIZE 300M BLOCKSIZE 512 REUSE,
 14 GROUP 16 (
 15 '+DG_REDO',
 16 '+DG_REDO'
 17 ) SIZE 300M BLOCKSIZE 512 REUSE,
 18 GROUP 17 (
 19 '+DG_REDO',
 20 '+DG_REDO'
 21 ) SIZE 100M BLOCKSIZE 512 REUSE,
 22 GROUP 18 (
 23 '+DG_REDO',
 24 '+DG_REDO'
 25 ) SIZE 100M BLOCKSIZE 512 REUSE;
Database altered.
SQL> ALTER DATABASE OPEN RESETLOGS;  --RESETLOGS方式开库
Database altered.
PS:归档日志完好,可以保证不丢数据,但是从Oracle概念上讲仍为不完全恢复。