【12c】RMAN 与 DataGuard
由于主库和备库的db_name相同的，只是db_unique_name不同，可以使用备库进行数据库的备份，从而减轻主库备份的负担。本篇演示DataGuard环境下RMAN工具的使用。
1 查看主库RMAN配置
[oracle@sz ~]$ rman target / catalog rman/rman@catdbRecovery Manager: Release 12.1.0.2.0 - Production on Sun Apr 26 21:49:56 2020Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.connected to target database: ORCL (DBID=1565938253)connected to recovery catalog databaseRMAN> show all;RMAN configuration parameters for database with db_unique_name ORCL are:CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;CONFIGURE BACKUP OPTIMIZATION OFF; # defaultCONFIGURE DEFAULT DEVICE TYPE TO DISK; # defaultCONFIGURE CONTROLFILE AUTOBACKUP OFF; # defaultCONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # defaultCONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # defaultCONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # defaultCONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # defaultCONFIGURE MAXSETSIZE TO UNLIMITED; # defaultCONFIGURE ENCRYPTION FOR DATABASE OFF; # defaultCONFIGURE ENCRYPTION ALGORITHM 'AES128'; # defaultCONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # defaultCONFIGURE DB_UNIQUE_NAME 'orcl' CONNECT IDENTIFIER 'shzhen';CONFIGURE DB_UNIQUE_NAME 'orcldg' CONNECT IDENTIFIER 'shhai';CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # defaultCONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/12.1.0/dbhome_1/dbs/snapcf_orcl.f'; # defaultRMAN>2 查看备库RMAN配置
[oracle@sh ~]$ rman target / catalog rman/rman@catdbRecovery Manager: Release 12.1.0.2.0 - Production on Sun Apr 26 21:50:16 2020Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.connected to target database: ORCL (DBID=1565938253)connected to recovery catalog databaseRMAN> show all;RMAN configuration parameters for database with db_unique_name ORCLDG are:CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;CONFIGURE BACKUP OPTIMIZATION OFF; # defaultCONFIGURE DEFAULT DEVICE TYPE TO DISK; # defaultCONFIGURE CONTROLFILE AUTOBACKUP ON;CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # defaultCONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # defaultCONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # defaultCONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # defaultCONFIGURE MAXSETSIZE TO UNLIMITED; # defaultCONFIGURE ENCRYPTION FOR DATABASE OFF; # defaultCONFIGURE ENCRYPTION ALGORITHM 'AES128'; # defaultCONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # defaultCONFIGURE DB_UNIQUE_NAME 'orcl' CONNECT IDENTIFIER 'shzhen';CONFIGURE DB_UNIQUE_NAME 'orcldg' CONNECT IDENTIFIER 'shhai';CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # defaultCONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # defaultCONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/12.1.0/dbhome_1/dbs/snapcf_orcldg.f'; # defaultRMAN>3 备库进行数据库的备份
对主备用数据库进行RMAN配置后，接下来就可以将物理备库作为RMAN的目标数据库进行连接，将原来在主库上的备份操作完全移至备库。由于备库本质上是主库的物理副本，因此，不论在主库备份，还是在备库备份，在恢复和还原数据库时，两者时通用的、可以互换的，但需注意的是，在RMAN中查看和管理备份时，它们还是和dB_UNIQUE_NAME相关联的。
[oracle@sh ~]$ rman target sys/system@shhai catalog rman/rman@catdbRecovery Manager: Release 12.1.0.2.0 - Production on Sun Apr 26 22:49:07 2020Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.connected to target database: ORCL (DBID=1565938253)connected to recovery catalog databaseRMAN> backup database plus archivelog;Starting backup at 26-APR-20current log archived at primary databaseallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=21 device type=DISKchannel ORA_DISK_1: starting archived log backup setchannel ORA_DISK_1: specifying archived log(s) in backup setinput archived log thread=1 sequence=12 RECID=1 STAMP=1038160947input archived log thread=1 sequence=13 RECID=2 STAMP=1038160948input archived log thread=1 sequence=14 RECID=3 STAMP=1038161310input archived log thread=1 sequence=15 RECID=4 STAMP=1038161793input archived log thread=1 sequence=16 RECID=5 STAMP=1038164237input archived log thread=1 sequence=17 RECID=7 STAMP=1038341915input archived log thread=1 sequence=18 RECID=6 STAMP=1038341913input archived log thread=1 sequence=19 RECID=8 STAMP=1038344530input archived log thread=1 sequence=20 RECID=9 STAMP=1038344716input archived log thread=1 sequence=21 RECID=11 STAMP=1038345256input archived log thread=1 sequence=22 RECID=13 STAMP=1038351398input archived log thread=1 sequence=23 RECID=16 STAMP=1038351676input archived log thread=1 sequence=24 RECID=18 STAMP=1038351701input archived log thread=1 sequence=25 RECID=17 STAMP=1038351701input archived log thread=1 sequence=26 RECID=19 STAMP=1038768564input archived log thread=1 sequence=27 RECID=20 STAMP=1038768690input archived log thread=1 sequence=28 RECID=21 STAMP=1038768781input archived log thread=1 sequence=29 RECID=22 STAMP=1038768877input archived log thread=1 sequence=30 RECID=23 STAMP=1038772825input archived log thread=1 sequence=31 RECID=24 STAMP=1038782819input archived log thread=1 sequence=32 RECID=25 STAMP=1038782819input archived log thread=1 sequence=33 RECID=26 STAMP=1038782826input archived log thread=1 sequence=34 RECID=27 STAMP=1038782922input archived log thread=1 sequence=35 RECID=28 STAMP=1038782959channel ORA_DISK_1: starting piece 1 at 26-APR-20channel ORA_DISK_1: finished piece 1 at 26-APR-20piece handle=/u01/app/oracle/fast_recovery_area/ORCLDG/backupset/2020_04_26/o1_mf_annnn_TAG20200426T224922_hbc7vlqf_.bkp tag=TAG20200426T224922 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:07Finished backup at 26-APR-20Starting backup at 26-APR-20using channel ORA_DISK_1channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinput datafile file number=00005 name=/u01/app/oracle/oradata/orcldg/example01.dbfinput datafile file number=00001 name=/u01/app/oracle/oradata/orcldg/system01.dbfinput datafile file number=00003 name=/u01/app/oracle/oradata/orcldg/sysaux01.dbfinput datafile file number=00004 name=/u01/app/oracle/oradata/orcldg/undotbs01.dbfinput datafile file number=00006 name=/u01/app/oracle/oradata/orcldg/users01.dbfchannel ORA_DISK_1: starting piece 1 at 26-APR-20channel ORA_DISK_1: finished piece 1 at 26-APR-20piece handle=/u01/app/oracle/fast_recovery_area/ORCLDG/backupset/2020_04_26/o1_mf_nnndf_TAG20200426T224929_hbc7vtob_.bkp tag=TAG20200426T224929 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:01:26Finished backup at 26-APR-20Starting backup at 26-APR-20current log archived at primary databaseusing channel ORA_DISK_1channel ORA_DISK_1: starting archived log backup setchannel ORA_DISK_1: specifying archived log(s) in backup setinput archived log thread=1 sequence=36 RECID=29 STAMP=1038783056channel ORA_DISK_1: starting piece 1 at 26-APR-20channel ORA_DISK_1: finished piece 1 at 26-APR-20piece handle=/u01/app/oracle/fast_recovery_area/ORCLDG/backupset/2020_04_26/o1_mf_annnn_TAG20200426T225059_hbc7ympo_.bkp tag=TAG20200426T225059 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01Finished backup at 26-APR-20Starting Control File and SPFILE Autobackup at 26-APR-20piece handle=/u01/app/oracle/fast_recovery_area/ORCLDG/autobackup/2020_04_26/o1_mf_s_1038782959_hbc7ynyn_.bkp comment=NONEFinished Control File and SPFILE Autobackup at 26-APR-20RMAN>4 模拟主库故障，恢复主库
4.1 有备份，删除主库数据文件，进行恢复
1）删除数据文件
[oracle@sz ~]$ cd /u01/app/oracle/oradata/orcl/[oracle@sz orcl]$ lltotal 3362568-rw-r----- 1 oracle asmadmin 10076160 Apr 26 22:53 control01.ctl-rw-r----- 1 oracle asmadmin 1304174592 Apr 26 22:49 example01.dbf-rw-r----- 1 oracle asmadmin 52429312 Apr 26 22:50 redo01.log-rw-r----- 1 oracle asmadmin 52429312 Apr 26 22:53 redo02.log-rw-r----- 1 oracle asmadmin 52429312 Apr 26 22:49 redo03.log-rw-r----- 1 oracle asmadmin 52429312 Apr 21 23:00 redo04.log-rw-r----- 1 oracle asmadmin 52429312 Apr 21 23:01 redo05.log-rw-r----- 1 oracle asmadmin 52429312 Apr 21 23:01 redo06.log-rw-r----- 1 oracle asmadmin 52429312 Apr 21 23:01 redo07.log-rw-r----- 1 oracle asmadmin 744497152 Apr 26 22:49 sysaux01.dbf-rw-r----- 1 oracle asmadmin 838868992 Apr 26 22:49 system01.dbf-rw-r----- 1 oracle asmadmin 206577664 Apr 26 19:00 temp01.dbf-rw-r----- 1 oracle asmadmin 167780352 Apr 26 22:49 undotbs01.dbf-rw-r----- 1 oracle asmadmin 5251072 Apr 26 22:49 users01.dbf[oracle@sz orcl]$ rm users01.dbf2）数据库出错
[oracle@sz orcl]$ sqlplus scott/scott@shzhenSQL*Plus: Release 12.1.0.2.0 Production on Sun Apr 26 22:54:17 2020Copyright (c) 1982, 2014, Oracle. All rights reserved.Connected to:Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit ProductionWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing optionsSQL> select count(1) from scott.emp;select count(1) from scott.emp*ERROR at line 1:ORA-01116: error in opening database file 6ORA-01110: data file 6: '/u01/app/oracle/oradata/orcl/users01.dbf'ORA-27041: unable to open fileLinux-x86_64 Error: 2: No such file or directoryAdditional information: 33）查看备份信息
[oracle@sz ~]$ rman target / catalog rman/rman@catdbRecovery Manager: Release 12.1.0.2.0 - Production on Sun Apr 26 22:55:30 2020Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.connected to target database: ORCL (DBID=1565938253)connected to recovery catalog databaseRMAN> list backup summary;specification does not match any backup in the repositoryRMAN> list backup summary for db_unique_name orcldg;List of Backups for database with db_unique_name ORCLDG===============Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag------- -- -- - ----------- --------------- ------- ------- ---------- ---621 B A A DISK 26-APR-20 1 1 NO TAG20200426T224922654 B F A DISK 26-APR-20 1 1 NO TAG20200426T224929691 B A A DISK 26-APR-20 1 1 NO TAG20200426T225059692 B F A DISK 26-APR-20 1 1 NO TAG20200426T225100RMAN> list backupset 654 for db_unique_name orcldg;List of Backup Set for database with db_unique_name ORCLDG===================BS Key Type LV Size Device Type Elapsed Time Completion Time------- ---- -- ---------- ----------- ------------ ---------------654 Full 1.42G DISK 00:01:18 26-APR-20BP Key: 679 Status: AVAILABLE Compressed: NO Tag: TAG20200426T224929Piece Name: /u01/app/oracle/fast_recovery_area/ORCLDG/backupset/2020_04_26/o1_mf_nnndf_TAG20200426T224929_hbc7vtob_.bkpList of Datafiles in backup set 654File LV Type Ckp SCN Ckp Time Name---- -- ---- ---------- --------- ----1 Full 2222976 26-APR-20 /u01/app/oracle/oradata/orcl/system01.dbf3 Full 2222976 26-APR-20 /u01/app/oracle/oradata/orcl/sysaux01.dbf4 Full 2222976 26-APR-20 /u01/app/oracle/oradata/orcl/undotbs01.dbf5 Full 2222976 26-APR-20 /u01/app/oracle/oradata/orcl/example01.dbf6 Full 2222976 26-APR-20 /u01/app/oracle/oradata/orcl/users01.dbfRMAN> catalog backuppiece '/home/oracle/o1_mf_nnndf_TAG20200426T224929_hbc7vtob_.bkp';cataloged backup piecebackup piece handle=/home/oracle/o1_mf_nnndf_TAG20200426T224929_hbc7vtob_.bkp RECID=2 STAMP=1038785184List of Backups===============Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag------- -- -- - ----------- --------------- ------- ------- ---------- ---621 B A A DISK 26-APR-20 1 1 NO TAG20200426T224922654 B F A DISK 26-APR-20 1 1 NO TAG20200426T2249294）还原数据文件
RMAN> restore datafile 6 from tag='TAG20200426T224929';Starting restore at 26-APR-20allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=24 device type=DISKchannel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/orcl/users01.dbfchannel ORA_DISK_1: reading from backup piece /home/oracle/o1_mf_nnndf_TAG20200426T224929_hbc7vtob_.bkpchannel ORA_DISK_1: piece handle=/home/oracle/o1_mf_nnndf_TAG20200426T224929_hbc7vtob_.bkp tag=TAG20200426T224929channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:01Finished restore at 26-APR-20RMAN> recover datafile 6;Starting recover at 26-APR-20using channel ORA_DISK_1starting media recoverymedia recovery complete, elapsed time: 00:00:01Finished recover at 26-APR-20RMAN> alter database open;Statement processed5）查看还原后的文件
[oracle@sz orcl]$ ll users01.dbf-rw-r----- 1 oracle asmadmin 5251072 Apr 26 23:28 users01.dbf4.2 无备份，删除主库数据文件，进行恢复
1）主备库均无备份可用
[oracle@sz ~]$ rman target / catalog rman/rman@catdbRecovery Manager: Release 12.1.0.2.0 - Production on Sun Apr 26 23:33:28 2020Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.connected to target database: ORCL (DBID=1565938253)connected to recovery catalog databaseRMAN> list backup for db_unique_name orcl;specification does not match any backup in the repositoryRMAN> list backup for db_unique_name orcldg;specification does not match any backup in the repository2）删除数据文件
[oracle@sz orcl]$ rm users01.dbf[oracle@sz orcl]$ ll users01.dbfls: cannot access users01.dbf: No such file or directory3）启动数据库出错
SQL> startupORACLE instance started.Total System Global Area 1644167168 bytesFixed Size 2925024 bytesVariable Size 973082144 bytesDatabase Buffers 654311424 bytesRedo Buffers 13848576 bytesDatabase mounted.ORA-01157: cannot identify/lock data file 6 - see DBWR trace fileORA-01110: data file 6: '/u01/app/oracle/oradata/orcl/users01.dbf'4）利用物理备库的数据文件还原主库损坏的数据文件
在RMAN中将备库作为目标数据库，将主库作为辅助数据库连接，同时连接恢复目录。
[oracle@sh ~]$ rman target sys/system@shhai auxiliary sys/system@shzhen catalog rman/rman@catdbRecovery Manager: Release 12.1.0.2.0 - Production on Sun Apr 26 23:37:03 2020Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.connected to target database: ORCL (DBID=1565938253)connected to recovery catalog databaseconnected to auxiliary database: ORCL (DBID=1565938253, not open)RMAN> backup as copy datafile 6 auxiliary format '/home/oracle/users_from_std.dbf';Starting backup at 26-APR-20allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=37 device type=DISKchannel ORA_DISK_1: starting datafile copyinput datafile file number=00006 name=/u01/app/oracle/oradata/orcldg/users01.dbfoutput file name=/home/oracle/users_from_std.dbf tag=TAG20200426T233826channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01Finished backup at 26-APR-205）在主库注册获得的文件复制（可选）
[oracle@sz ~]$ rman target / catalog rman/rman@catdbRecovery Manager: Release 12.1.0.2.0 - Production on Sun Apr 26 23:41:14 2020Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.connected to target database: ORCL (DBID=1565938253, not open)connected to recovery catalog databaseRMAN> catalog datafilecopy '/home/oracle/users_from_std.dbf';cataloged datafile copydatafile copy file name=/home/oracle/users_from_std.dbf RECID=1 STAMP=10387861006）利用已注册的数据文件拷贝还原损坏的数据文件（可选）
RMAN> restore datafile 6;Starting restore at 26-APR-20allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=26 device type=DISKchannel ORA_DISK_1: restoring datafile 00006input datafile copy RECID=1 STAMP=1038786100 file name=/home/oracle/users_from_std.dbfdestination for restore of datafile 00006: /u01/app/oracle/oradata/orcl/users01.dbfchannel ORA_DISK_1: copied datafile copy of datafile 00006output file name=/u01/app/oracle/oradata/orcl/users01.dbf RECID=0 STAMP=0Finished restore at 26-APR-20或者直接利用下面的命令：
RMAN> run{2> set newname for datafile 6 to '/home/oracle/users_from_std.dbf';3> switch datafile 6;4> }executing command: SET NEWNAMEdatafile 6 switched to datafile copyinput datafile copy RECID=1 STAMP=1038786100 file name=/home/oracle/users_from_std.dbfstarting full resync of recovery catalogfull resync complete7）恢复损坏的数据文件，打开数据库
RMAN> recover datafile 6 ;Starting recover at 26-APR-20using channel ORA_DISK_1starting media recoverymedia recovery complete, elapsed time: 00:00:00Finished recover at 26-APR-20RMAN> alter database open;Statement processed4.3 利用网络进行恢复（12c新特性）
1）删除数据文件
[oracle@sz ~]$ rm users_from_std.dbf2）连接到主库
[oracle@sz ~]$ rman target / catalog rman/rman@catdbRecovery Manager: Release 12.1.0.2.0 - Production on Sun Apr 26 23:49:41 2020Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.connected to target database: ORCL (DBID=1565938253)connected to recovery catalog database3）设置加密算法
RMAN> set encryption algorithm 'AES128';executing command: SET encryption4）利用备库恢复主库
RMAN> restore datafile '/home/oracle/users_from_std.dbf'2> from service 'shhai' section size 120M;Starting restore at 26-APR-20allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=22 device type=DISKchannel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: using network backup set from service shhaichannel ORA_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00006 to /home/oracle/users_from_std.dbfchannel ORA_DISK_1: restoring section 1 of 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:01Finished restore at 26-APR-20RMAN> alter database open;RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of sql statement command at 04/26/2020 23:53:33ORA-01113: file 6 needs media recoveryORA-01110: data file 6: '/home/oracle/users_from_std.dbf'RMAN> recover datafile 6;Starting recover at 26-APR-20using channel ORA_DISK_1starting media recoverymedia recovery complete, elapsed time: 00:00:00Finished recover at 26-APR-20RMAN> alter database open;Statement processedRMAN> select count(1) from scott.emp;COUNT(1)----------14以上就是DataGuard环境下使用RMAN进行数据库备份和恢复的演示。
No comments:
Post a Comment