【DB宝22】使用DG环境的物理备库进行备份还原的备份一致性问题 |
您所在的位置：网站首页 › adg备份 › 【DB宝22】使用DG环境的物理备库进行备份还原的备份一致性问题 |
【DB宝22】使用DG环境的物理备库进行备份还原的备份一致性问题
|
在部署完ADG（Active Data Guard）后，不但可以将只读的查询交给备库执行，还可以把日常的数据库备份工作放在备库上执行，从而减轻主库的压力，充分的发挥服务器资源。 本文演示的是利用Oracle DG环境的备库执行备份，然后异机还原恢复成一个新的主库的过程。 ※使用物理备库备份数据库的一致性说明之前发过一篇类似的文章，请参考： 【DB宝15】生产环境中，如何利用DG的备库来异机还原一个新库？ 连接地址为： https://mp.weixin.qq.com/s/ptB9D3sDzwNyHyHujTwKbQ 这几天还原了一个将近2T的库，碰到了一个坑。情况是这样的，我在物理备库进行的备份，但是当我把备份拿到新主机进行还原后，死活就是不能正常启动数据库，报错“数据库不一致，需要相关的归档日志进行恢复”，即使加上隐含参数“_allow_resetlogs_corruption”强制启库也不行。无奈之下，找了找相关资料，发现在物理备库进行的备份其实是存在备份一致性的问题的。 请看下图，来自于Benefits and Usage of RMAN with Standby Databases (文档 ID 602299.1)：  在备库进行备份，主要关注备份的一致性问题，分为2种情况： 1、备库可以连接到主库： 方法a、对数据文件备份完成后，需要在主库切换归档后，等最新的归档传递到备库后，再在备库备份归档，最后备份控制文件。需要注意的是，如果是11.2.0.4以上，可以不用单独在主库执行切换归档操作，因为在备库执行备份的过程中，会自动去连接主库执行一个切换归档的操作，但是若主库本身拒绝备库的连接，会报RMAN警告（RMAN-06820: WARNING: failed to archive current log at primary database），那么依然需要手工在主库进行切换。 方法b、灾备端可以在mount状态或read only状态进行备份，但是不能启用实时应用功能（mrp进程）。因为，即使数据库处于read only或mount状态，mrp进程也会持续更新数据块。 2、备库不能连接到主库：灾备端可以在mount状态或read only状态进行备份，但是不能启用实时应用功能（mrp进程）。因为，即使数据库处于read only或mount状态，mrp进程也会持续更新数据块。 如果备库还原完成后，还需要归档才能打开数据库，那么我们也可以在原主库上拷贝相关的归档日志到我们的新主库进行恢复（recover），即可打开数据库，只是有的情况下，环境不允许我们连接主库，主库一般都是生产库。 其它更多内容请参考：http://blog.itpub.net/26736162/viewspace-2723840/ 参考文档： ( Doc ID 1616074.1) RMAN-06820 ORA-17629 During Backup at Standby SiteHow to take consistent backups at standby site (文档 ID 1292126.1)( Doc ID 1419923.1) Howto make a consistent RMAN backup in an Standby database in Active DataGuard modeStep by Step method to create Primary/Standby Database from Standby Backup (文档 ID 1604251.1)How to quickly check that Database is consistent after incomplete recovery (Point in Time Recovery) before OPEN RESETLOGS (文档 ID 1354256.1)Benefits and Usage of RMAN with Standby Databases (文档 ID 602299.1)本环境中，我们选择第2种办法在灾备端进行备份数据库，并拿到新环境进行还原。 ※DG环境介绍项目 primary db physical standby db 新库 数据库类型（rac或单实例） 单实例 单实例 单实例 数据库版本 11.2.0.3.0 11.2.0.3.0 11.2.0.3.0 platform_name Linux x86 64-bit Linux x86 64-bit Linux x86 64-bit ORACLE_SID oradg11g oradgphy TEST db_name/GLOBAL_DBNAME oradg11g oradg11g oradg11g db_unique_name oradg11g oradgphy TEST ORACLE_HOME /u01/app/oracle/product/11.2.0/dbhome_1 /u01/app/oracle/product/11.2.0/dbhome_1 /u01/app/oracle/product/11.2.0/dbhome_1 一、备库执行备份1.1、备库取消恢复为了能在备库达到一致性备份，需要取消实时应用功能： 代码语言：javascript复制alter database recover managed standby database cancel;检查是否处于一致性状态： 代码语言：javascript复制alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS'; set line 1000 set numwidth 16 col name format a55 select fuzzy, status,checkpoint_change#, checkpoint_time,file#,name from v$datafile_header ;如果fuzzy为NO，且checkpoint_change#列的值都一致，那么就代表数据库处于一致性状态，可以进行rman备份。 代码语言：javascript复制SYS@oradgphy > alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS'; Session altered. SYS@oradgphy > set line 1000 SYS@oradgphy > set numwidth 16 SYS@oradgphy > col name format a55 SYS@oradgphy > select fuzzy, status,checkpoint_change#, checkpoint_time,file#,name from v$datafile_header ; FUZ STATUS CHECKPOINT_CHANGE# CHECKPOINT_TIME FILE# NAME --- ------- ------------------ ------------------- ---------------- ------------------------------------------------------- NO ONLINE 3500675 2020-09-25 14:44:17 1 /u01/app/oracle/oradata/oradgphy/system01.dbf NO ONLINE 3500675 2020-09-25 14:44:17 2 /u01/app/oracle/oradata/oradgphy/sysaux01.dbf NO ONLINE 3500675 2020-09-25 14:44:17 3 /u01/app/oracle/oradata/oradgphy/undotbs01.dbf NO ONLINE 3500675 2020-09-25 14:44:17 4 /u01/app/oracle/oradata/oradgphy/users01.dbf NO ONLINE 3500675 2020-09-25 14:44:17 5 /u01/app/oracle/oradata/oradgphy/example01.dbf NO ONLINE 3500675 2020-09-25 14:44:17 6 /u01/app/oracle/oradata/oradgphy/logmnrtbs1.dbf SYS@oradgphy > select open_mode , database_role, flashback_on from v$database; OPEN_MODE DATABASE_ROLE FLASHBACK_ON -------------------- ---------------- ------------------ READ ONLY PHYSICAL STANDBY NO 1.2、备库执行备份 代码语言：javascript复制export NLS_DATE_FORMAT='yyyy-mm-dd HH24:mi:ss' ORACLE_SID=oradgphy rman target / run{ backup as compressed backupset database format '/home/oracle/oracle_bk/FULL_%d_%U.full' section size 100G; backup as compressed backupset archivelog from time 'sysdate-2/24' format '/home/oracle/oracle_bk/ARC_%d_%U.arc' section size 100G; backup current controlfile format '/home/oracle/oracle_bk/standby_1.ctl'; backup spfile format '/home/oracle/oracle_bk/spfile_%d_%U.ora'; }执行过程： 代码语言：javascript复制[oracle@rhel6lhr oracle_bk]$ export NLS_DATE_FORMAT='yyyy-mm-dd HH24:mi:ss' [oracle@rhel6lhr oracle_bk]$ rman target / Recovery Manager: Release 11.2.0.3.0 - Production on Fri Sep 25 14:48:52 2020 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: ORADG11G (DBID=1403587593) RMAN> run{ 2> backup as compressed backupset database format '/home/oracle/oracle_bk/FULL_%d_%U.full' section size 100G; 3> backup as compressed backupset archivelog from time 'sysdate-2/24' format '/home/oracle/oracle_bk/ARC_%d_%U.arc' section size 100G; 4> backup current controlfile format '/home/oracle/oracle_bk/standby_1.ctl'; 5> backup spfile format '/home/oracle/oracle_bk/spfile_%d_%U.ora'; 6> } Starting backup at 2020-09-25 14:48:55 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 compressed full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/u01/app/oracle/oradata/oradgphy/system01.dbf input datafile file number=00002 name=/u01/app/oracle/oradata/oradgphy/sysaux01.dbf input datafile file number=00006 name=/u01/app/oracle/oradata/oradgphy/logmnrtbs1.dbf input datafile file number=00005 name=/u01/app/oracle/oradata/oradgphy/example01.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/oradgphy/users01.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/oradgphy/undotbs01.dbf channel ORA_DISK_1: starting piece 1 at 2020-09-25 14:48:55 channel ORA_DISK_1: finished piece 1 at 2020-09-25 14:49:30 piece handle=/home/oracle/oracle_bk/FULL_ORADG11G_36vbaa2n_1_1.full tag=TAG20200925T144855 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35 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 2020-09-25 14:49:31 channel ORA_DISK_1: finished piece 1 at 2020-09-25 14:49:32 piece handle=/home/oracle/oracle_bk/FULL_ORADG11G_37vbaa3q_1_1.full tag=TAG20200925T144855 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 2020-09-25 14:49:32 Starting backup at 2020-09-25 14:49:32 using channel ORA_DISK_1 specification does not match any archived log in the repository backup cancelled because there are no files to backup Finished backup at 2020-09-25 14:49:32 Starting backup at 2020-09-25 14:49:32 using channel ORA_DISK_1 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 channel ORA_DISK_1: starting piece 1 at 2020-09-25 14:49:33 channel ORA_DISK_1: finished piece 1 at 2020-09-25 14:49:34 piece handle=/home/oracle/oracle_bk/standby_1.ctl tag=TAG20200925T144932 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 2020-09-25 14:49:35 Starting backup at 2020-09-25 14:49:35 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current SPFILE in backup set channel ORA_DISK_1: starting piece 1 at 2020-09-25 14:49:35 channel ORA_DISK_1: finished piece 1 at 2020-09-25 14:49:36 piece handle=/home/oracle/oracle_bk/spfile_ORADG11G_39vbaa3v_1_1.ora tag=TAG20200925T144935 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 2020-09-25 14:49:36 1.3、备库启用日志应用进程 代码语言：javascript复制alter database recover managed standby database using current logfile disconnect from session; ------------物理dg日志应用情况(主备库都可以) alter session set NLS_DATE_FORMAT='yyyy-mm-dd HH24:mi:ss'; COL NAME FOR A100 SET LINESIZE 9999 PAGESIZE 9999 SELECT A.THREAD#, A.NAME, A.SEQUENCE#, A.APPLIED, A.FIRST_TIME FROM V$ARCHIVED_LOG A, (SELECT NB.THREAD#,NB.RESETLOGS_CHANGE#,NB.DEST_ID,(MAX(NB.SEQUENCE#) - 3) MAX_SEQUENCE# FROM V$ARCHIVED_LOG NB WHERE NB.APPLIED = 'YES' and NB.DEST_ID in (SELECT NB.DEST_ID FROM V$ARCHIVE_DEST_STATUS NB where STATUS 'INACTIVE') GROUP BY NB.THREAD#,NB.RESETLOGS_CHANGE#,NB.DEST_ID) B WHERE B.THREAD# = A.THREAD# AND B.RESETLOGS_CHANGE# = A.RESETLOGS_CHANGE# AND A.DEST_ID=B.DEST_ID AND A.SEQUENCE# >= MAX_SEQUENCE# AND A.STANDBY_DEST = (SELECT CASE WHEN NB.DATABASE_ROLE LIKE '%STANDBY%' THEN 'NO' ELSE 'YES' END FROM V$DATABASE NB) ORDER BY A.THREAD#, A.SEQUENCE#;执行过程： 代码语言：javascript复制SYS@oradgphy > alter session set NLS_DATE_FORMAT='yyyy-mm-dd HH24:mi:ss'; Session altered. SYS@oradgphy > COL NAME FOR A100 SYS@oradgphy > SET LINESIZE 9999 PAGESIZE 9999 SYS@oradgphy > SELECT A.THREAD#, A.NAME, A.SEQUENCE#, A.APPLIED, A.FIRST_TIME 2 FROM V$ARCHIVED_LOG A, 3 (SELECT NB.THREAD#,NB.RESETLOGS_CHANGE#,NB.DEST_ID,(MAX(NB.SEQUENCE#) - 3) MAX_SEQUENCE# 4 FROM V$ARCHIVED_LOG NB 5 WHERE NB.APPLIED = 'YES' 6 and NB.DEST_ID in (SELECT NB.DEST_ID FROM V$ARCHIVE_DEST_STATUS NB where STATUS 'INACTIVE') 7 GROUP BY NB.THREAD#,NB.RESETLOGS_CHANGE#,NB.DEST_ID) B 8 WHERE B.THREAD# = A.THREAD# 9 AND B.RESETLOGS_CHANGE# = A.RESETLOGS_CHANGE# 10 AND A.DEST_ID=B.DEST_ID 11 AND A.SEQUENCE# >= MAX_SEQUENCE# 12 AND A.STANDBY_DEST = (SELECT CASE WHEN NB.DATABASE_ROLE LIKE '%STANDBY%' THEN 'NO' ELSE 'YES' END FROM V$DATABASE NB) 13 ORDER BY A.THREAD#, A.SEQUENCE#; THREAD# NAME SEQUENCE# APPLIED FIRST_TIME ---------- ---------------------------------------------------------------------------------------------------- ---------- --------- ------------------- 1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2020_09_25/o1_mf_1_242_hpv5042k_.arc 242 YES 2020-09-25 14:53:53 1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2020_09_25/o1_mf_1_243_hpv5050f_.arc 243 YES 2020-09-25 14:53:56 1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2020_09_25/o1_mf_1_244_hpv505oh_.arc 244 IN-MEMORY 2020-09-25 14:53:57确保DG环境恢复正常运行。 二、将备份文件传递到新主库接下来就是把/home/oracle/oracle_bk/备份目录下的内容都拷贝到新主机上，方法很多，例如scp、磁盘卸载重新挂载等。 代码语言：javascript复制[oracle@rhel6lhr oracle_bk]$ scp /home/oracle/oracle_bk/* [email protected]:/home/oracle/oracle_bk/ [email protected]'s password: FULL_ORADG11G_36vbaa2n_1_1.full 100% 333MB 19.6MB/s 00:17 FULL_ORADG11G_37vbaa3q_1_1.full 100% 1120KB 1.1MB/s 00:00 spfile_ORADG11G_39vbaa3v_1_1.ora 100% 96KB 96.0KB/s 00:00 standby_1.ctl 100% 10MB 9.8MB/s 00:00 [oracle@rhel6lhr oracle_bk]$ 三、在新主机执行恢复操作3.1、恢复spfile 代码语言：javascript复制export ORACLE_SID=TEST rman target / startup nomount; restore spfile to pfile '?/dbs/initTEST.ora' from '/home/oracle/oracle_bk/spfile_ORADG11G_39vbaa3v_1_1.ora'; -- 修改pfile，去除dg相关参数 vi $ORACLE_HOME/dbs/initTEST.ora需要根据情况对pfile做相关的修改，最终的参数文件内容： 建议加上log_file_name_convert和db_recovery_file_dest参数，可以避免很多后续的日志转换操作。 根据pfile文件内容创建相关目录 根据pfile创建spfile，并启动到nomout 整个执行过程： 代码语言：javascript复制[oracle@lhrora11203 oracle_bk]$ export ORACLE_SID=TEST [oracle@lhrora11203 oracle_bk]$ rman target / Recovery Manager: Release 11.2.0.3.0 - Production on Fri Sep 25 15:07:43 2020 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database (not started) RMAN> startup nomount; Oracle instance started Total System Global Area 242208768 bytes Fixed Size 2227176 bytes Variable Size 184550424 bytes Database Buffers 50331648 bytes Redo Buffers 5099520 bytes RMAN> restore spfile to pfile '?/dbs/initTEST.ora' from '/home/oracle/oracle_bk/spfile_ORADG11G_39vbaa3v_1_1.ora'; Starting restore at 2020-09-25 15:15:08 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=156 device type=DISK channel ORA_DISK_1: restoring spfile from AUTOBACKUP /home/oracle/oracle_bk/spfile_ORADG11G_39vbaa3v_1_1.ora channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete Finished restore at 2020-09-25 15:15:10 RMAN> exit Recovery Manager complete. [oracle@lhrora11203 ~]$ [oracle@lhrora11203 ~]$ more /u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/initTEST.ora oradgphy.__db_cache_size=37748736 oradgphy.__java_pool_size=4194304 oradgphy.__large_pool_size=4194304 oradgphy.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment oradgphy.__pga_aggregate_target=12582912 oradgphy.__sga_target=197132288 oradgphy.__shared_io_pool_size=0 oradgphy.__shared_pool_size=142606336 oradgphy.__streams_pool_size=0 *.archive_lag_target=0 *.audit_file_dest='/u01/app/oracle/admin/oradgphy/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='/u01/app/oracle/oradata/oradgphy/crontal01.ctl','/u01/app/oracle/oradata/oradgphy/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_file_name_convert='oradg11g','oradgphy' *.db_name='oradg11g' *.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area' *.db_recovery_file_dest_size=4322230272 *.db_unique_name='oradgphy' *.dg_broker_start=TRUE *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=oradg11gXDB)' *.fal_client='tns_oradg11g' *.fal_server='tns_oradg11g' *.log_archive_config='dg_config=(oradgphy,oradg11g,oradglg)' *.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=oradgphy' *.log_archive_dest_2='SERVICE=tns_oradg11g LGWR ASYNC db_unique_name=oradg11g valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)' *.log_archive_dest_state_1='ENABLE' *.log_archive_dest_state_2='ENABLE' oradgphy.log_archive_format='%t_%s_%r.dbf' *.log_archive_max_processes=4 *.log_archive_min_succeed_dest=1 oradgphy.log_archive_trace=0 *.log_file_name_convert='oradg11g','oradgphy' *.memory_max_target=209715200 *.memory_target=209715200 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.standby_file_management='AUTO' *.undo_tablespace='UNDOTBS1' [oracle@lhrora11203 ~]$ [oracle@lhrora11203 ~]$ [oracle@lhrora11203 ~]$ cat > /home/oracle/a.txt *.audit_trail='db' > *.compatible='11.2.0.0.0' > *.control_files='/home/oracle/oradata/TEST/control01.ctl' > *.db_block_size=8192 > *.db_domain='' > *.db_name='oradg11g' > *.db_recovery_file_dest='/home/oracle/oradata/flash_recovery_area' > *.db_recovery_file_dest_size=4322230272 > *.db_unique_name='TEST' > *.dispatchers='(PROTOCOL=TCP) (SERVICE=oradg11gXDB)' > *.memory_max_target=229715200 > *.open_cursors=300 > *.processes=150 > *.remote_login_passwordfile='EXCLUSIVE' > *.standby_file_management='AUTO' > *.undo_tablespace='UNDOTBS1' > *.log_file_name_convert='/u01/app/oracle/oradata/oradg11g/','/home/oracle/oradata/' > *.db_recovery_file_dest='/home/oracle/oradata/' > EOF [oracle@lhrora11203 ~]$ sas SQL*Plus: Release 11.2.0.3.0 Production on Fri Sep 25 15:18:00 2020 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SYS@TEST> create spfile from pfile='/home/oracle/a.txt'; File created. SYS@TEST> startup force nomount ORACLE instance started. Total System Global Area 242208768 bytes Fixed Size 2227176 bytes Variable Size 184550424 bytes Database Buffers 50331648 bytes Redo Buffers 5099520 bytes 3.2、恢复控制文件 代码语言：javascript复制-- 因为要恢复为主库，所以需要加上primary关键字 restore primary controlfile from '/home/oracle/oracle_bk/standby_1.ctl';执行过程： 代码语言：javascript复制[oracle@lhrora11203 ~]$ rman target / Recovery Manager: Release 11.2.0.3.0 - Production on Fri Sep 25 15:24:08 2020 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: ORADG11G (not mounted) RMAN> restore primary controlfile from '/home/oracle/oracle_bk/standby_1.ctl'; Starting restore at 2020-09-25 15:24:30 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=156 device type=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/home/oracle/oradata/TEST/control01.ctl Finished restore at 2020-09-25 15:24:32 3.3、注册备份信息 代码语言：javascript复制-- 启动到mout阶段 alter database mount; -- 清除之前的备份信息 EXECUTE DBMS_BACKUP_RESTORE.RESETCFILESECTION(11); /** CLEAR V$ARCHIVED_LOG */ EXECUTE DBMS_BACKUP_RESTORE.RESETCFILESECTION(12); /** CLEAR V$BACKUP_SET */ EXECUTE DBMS_BACKUP_RESTORE.RESETCFILESECTION(13); /** CLEAR V$BACKUP_PIECE */ --重新注册,注意路径最后一定需要加上/ catalog start with '/home/oracle/oracle_bk/';执行过程： 代码语言：javascript复制SYS@TEST> alter database mount; Database altered. SYS@TEST> EXECUTE DBMS_BACKUP_RESTORE.RESETCFILESECTION(11); PL/SQL procedure successfully completed. SYS@TEST> SYS@TEST> EXECUTE DBMS_BACKUP_RESTORE.RESETCFILESECTION(12); PL/SQL procedure successfully completed. SYS@TEST> SYS@TEST> EXECUTE DBMS_BACKUP_RESTORE.RESETCFILESECTION(13); PL/SQL procedure successfully completed. SYS@TEST> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@lhrora11203 ~]$ rman target / Recovery Manager: Release 11.2.0.3.0 - Production on Fri Sep 25 15:31:44 2020 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: ORADG11G (DBID=1403587593, not open) RMAN> catalog start with '/home/oracle/oracle_bk/'; Starting implicit crosscheck backup at 2020-09-25 15:31:47 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=156 device type=DISK Finished implicit crosscheck backup at 2020-09-25 15:31:48 Starting implicit crosscheck copy at 2020-09-25 15:31:48 using channel ORA_DISK_1 Crosschecked 4 objects Finished implicit crosscheck copy at 2020-09-25 15:31:48 searching for all files in the recovery area cataloging files... no files cataloged searching for all files that match the pattern /home/oracle/oracle_bk/ List of Files Unknown to the Database ===================================== File Name: /home/oracle/oracle_bk/standby_1.ctl File Name: /home/oracle/oracle_bk/FULL_ORADG11G_36vbaa2n_1_1.full File Name: /home/oracle/oracle_bk/spfile_ORADG11G_39vbaa3v_1_1.ora File Name: /home/oracle/oracle_bk/FULL_ORADG11G_37vbaa3q_1_1.full Do you really want to catalog the above files (enter YES or NO)? yes cataloging files... cataloging done List of Cataloged Files ======================= File Name: /home/oracle/oracle_bk/standby_1.ctl File Name: /home/oracle/oracle_bk/FULL_ORADG11G_36vbaa2n_1_1.full File Name: /home/oracle/oracle_bk/spfile_ORADG11G_39vbaa3v_1_1.ora File Name: /home/oracle/oracle_bk/FULL_ORADG11G_37vbaa3q_1_1.full RMAN> RMAN> list backupset summary; using target database control file instead of recovery catalog List of Backups =============== Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag ------- -- -- - ----------- ------------------- ------- ------- ---------- --- 1 B F A DISK 2020-09-25 14:49:32 1 1 NO TAG20200925T144932 2 B F A DISK 2020-09-25 14:48:55 1 1 YES TAG20200925T144855 3 B F A DISK 2020-09-25 14:49:35 1 1 NO TAG20200925T144935 4 B F A DISK 2020-09-25 14:49:30 1 1 YES TAG20200925T144855 3.4、还原数据文件 代码语言：javascript复制run{ SET NEWNAME FOR DATABASE TO '/home/oracle/oradata/%b'; restore database; switch datafile all; }执行过程： 代码语言：javascript复制[oracle@lhrora11203 ~]$ rman target / Recovery Manager: Release 11.2.0.3.0 - Production on Fri Sep 25 15:31:44 2020 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: ORADG11G (DBID=1403587593, not open) RMAN> run{ 2> SET NEWNAME FOR DATABASE TO '/home/oracle/oradata/%b'; 3> restore database; 4> switch datafile all; 5> } executing command: SET NEWNAME Starting restore at 2020-09-25 15:32:54 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 /home/oracle/oradata/system01.dbf channel ORA_DISK_1: restoring datafile 00002 to /home/oracle/oradata/sysaux01.dbf channel ORA_DISK_1: restoring datafile 00003 to /home/oracle/oradata/undotbs01.dbf channel ORA_DISK_1: restoring datafile 00004 to /home/oracle/oradata/users01.dbf channel ORA_DISK_1: restoring datafile 00005 to /home/oracle/oradata/example01.dbf channel ORA_DISK_1: restoring datafile 00006 to /home/oracle/oradata/logmnrtbs1.dbf channel ORA_DISK_1: reading from backup piece /home/oracle/oracle_bk/FULL_ORADG11G_36vbaa2n_1_1.full channel ORA_DISK_1: piece handle=/home/oracle/oracle_bk/FULL_ORADG11G_36vbaa2n_1_1.full tag=TAG20200925T144855 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:01:26 Finished restore at 2020-09-25 15:34:20 datafile 1 switched to datafile copy input datafile copy RECID=36 STAMP=1052062460 file name=/home/oracle/oradata/system01.dbf datafile 2 switched to datafile copy input datafile copy RECID=37 STAMP=1052062460 file name=/home/oracle/oradata/sysaux01.dbf datafile 3 switched to datafile copy input datafile copy RECID=38 STAMP=1052062460 file name=/home/oracle/oradata/undotbs01.dbf datafile 4 switched to datafile copy input datafile copy RECID=39 STAMP=1052062460 file name=/home/oracle/oradata/users01.dbf datafile 5 switched to datafile copy input datafile copy RECID=40 STAMP=1052062460 file name=/home/oracle/oradata/example01.dbf datafile 6 switched to datafile copy input datafile copy RECID=41 STAMP=1052062460 file name=/home/oracle/oradata/logmnrtbs1.dbf RMAN> exit Recovery Manager complete. 3.5、激活备库为主库，并启动数据库 代码语言：javascript复制-- 1、查询数据库状态 select open_mode , database_role, flashback_on from v$database; -- 2、激活备库为主库，如果已经是主库了，那就跳过该步骤 alter database activate standby database; -- ALTER DATABASE CLEAR LOGFILE GROUP 4; -- alter database drop logfile group 4; -- 3、启动数据库 alter database open resetlogs;结果： 代码语言：javascript复制[oracle@lhrora11203 ~]$ sas SQL*Plus: Release 11.2.0.3.0 Production on Fri Sep 25 15:34:58 2020 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SYS@TEST> select open_mode , database_role, flashback_on from v$database; OPEN_MODE DATABASE_ROLE FLASHBACK_ON -------------------- ---------------- ------------------ MOUNTED PRIMARY NO SYS@TEST> set line 1000 SYS@TEST> set numwidth 16 SYS@TEST> col name format a55 SYS@TEST> select fuzzy, status,checkpoint_change#, checkpoint_time,file#,name from v$datafile_header ; FUZ STATUS CHECKPOINT_CHANGE# CHECKPOINT_TIME FILE# NAME --- ------- ------------------ ------------------- ---------------- ------------------------------------------------------- NO ONLINE 3500675 2020-09-25 14:44:17 1 /home/oracle/oradata/system01.dbf NO ONLINE 3500675 2020-09-25 14:44:17 2 /home/oracle/oradata/sysaux01.dbf NO ONLINE 3500675 2020-09-25 14:44:17 3 /home/oracle/oradata/undotbs01.dbf NO ONLINE 3500675 2020-09-25 14:44:17 4 /home/oracle/oradata/users01.dbf NO ONLINE 3500675 2020-09-25 14:44:17 5 /home/oracle/oradata/example01.dbf NO ONLINE 3500675 2020-09-25 14:44:17 6 /home/oracle/oradata/logmnrtbs1.dbf -- 此处查询出来fuzzy列都为NO，所以应该可以直接启动数据库，而不用执行recover操作。 SYS@TEST> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-00392: log 2 of thread 1 is being cleared, operation not allowed ORA-00312: online log 2 thread 1: '/home/oracle/oradata/redo02.log' SYS@TEST> ALTER DATABASE CLEAR LOGFILE GROUP 2; Database altered. SYS@TEST> alter database open resetlogs; Database altered. SYS@TEST> select open_mode , database_role, flashback_on from v$database; OPEN_MODE DATABASE_ROLE FLASHBACK_ON -------------------- ---------------- ------------------ READ WRITE PRIMARY NO SYS@TEST> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 1 Next log sequence to archive 1 Current log sequence 1 3.6、删除不用的日志组 代码语言：javascript复制col member format a100 select * from v$logfile; select 'alter database drop logfile group '|| GROUP# ||';' from v$standby_log; alter database drop logfile group 4; ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 4;执行过程： 代码语言：javascript复制SYS@TEST> col member format a100 SYS@TEST> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_ ---------------- ------- ------- ------------------------------------------------------- --- 3 ONLINE /home/oracle/oradata/redo03.log NO 2 ONLINE /home/oracle/oradata/redo02.log NO 1 ONLINE /home/oracle/oradata/redo01.log NO 4 STANDBY /u01/app/oracle/oradata/oradgphy/standby_redo04.log NO 5 STANDBY /u01/app/oracle/oradata/oradgphy/standby_redo05.log NO 6 STANDBY /u01/app/oracle/oradata/oradgphy/standby_redo06.log NO 7 STANDBY /home/oracle/oradata/standby_redo07.log NO 7 rows selected. SYS@TEST> select 'alter database drop logfile group '|| GROUP# ||';' from v$standby_log; 'ALTERDATABASEDROPLOGFILEGROUP'||GROUP#||';' --------------------------------------------------------------------------- alter database drop logfile group 4; alter database drop logfile group 5; alter database drop logfile group 6; alter database drop logfile group 7; SYS@TEST> alter database drop logfile group 4; Database altered. SYS@TEST> alter database drop logfile group 5; alter database drop logfile group 5 * ERROR at line 1: ORA-00313: open failed for members of log group 5 of thread 1 ORA-00312: online log 5 thread 1: '/u01/app/oracle/oradata/oradgphy/standby_redo05.log' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 SYS@TEST> alter database drop logfile group 6; Database altered. SYS@TEST> alter database drop logfile group 7; Database altered. SYS@TEST> alter database drop logfile group 5; alter database drop logfile group 5 * ERROR at line 1: ORA-00313: open failed for members of log group 5 of thread 1 ORA-00312: online log 5 thread 1: '/u01/app/oracle/oradata/oradgphy/standby_redo05.log' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 SYS@TEST> ALTER DATABASE CLEAR LOGFILE GROUP 5; ALTER DATABASE CLEAR LOGFILE GROUP 5 * ERROR at line 1: ORA-00350: log 5 of instance TEST (thread 1) needs to be archived ORA-00312: online log 5 thread 1: '/u01/app/oracle/oradata/oradgphy/standby_redo05.log' SYS@TEST> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 5; ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 5 * ERROR at line 1: ORA-00344: unable to re-create online log '/u01/app/oracle/oradata/oradgphy/standby_redo05.log' ORA-27040: file create error, unable to create file Linux-x86_64 Error: 2: No such file or directory Additional information: 1 SYS@TEST> ALTER DATABASE CLEAR LOGFILE GROUP 5; ALTER DATABASE CLEAR LOGFILE GROUP 5 * ERROR at line 1: ORA-00344: unable to re-create online log '/u01/app/oracle/oradata/oradgphy/standby_redo05.log' ORA-27040: file create error, unable to create file Linux-x86_64 Error: 2: No such file or directory Additional information: 1 SYS@TEST> alter database drop logfile group 5; Database altered. SYS@TEST> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_ ---------------- ------- ------- --------------------------------- --- 3 ONLINE /home/oracle/oradata/redo03.log NO 2 ONLINE /home/oracle/oradata/redo02.log NO 1 ONLINE /home/oracle/oradata/redo01.log NO本文结束，感兴趣的朋友可以自行测试不同情况下的还原。 |
【本文地址】
今日新闻 |
推荐新闻 |