百度网站建设工具企业手机网站建设方案

当前位置： 首页 > news >正文

百度网站建设工具,企业手机网站建设方案,网站群建设工作培训会,郑州网站建设 股权投资背景#xff1a; 我们在一些恢复测试案例中#xff0c;会经常遇到一些奇怪的问题#xff0c;其中有的是源端数据文件不规范而导致恢复过程出错#xff0c;比较常见的错误有#xff1a; 数据文件名称重复#xff08;如#xff1a;/oradata1/user01.dbf 和 /oradata2/us…背景 我们在一些恢复测试案例中会经常遇到一些奇怪的问题其中有的是源端数据文件不规范而导致恢复过程出错比较常见的错误有 数据文件名称重复如/oradata1/user01.dbf 和 /oradata2/user01.dbf这种情况下我们在做恢复测试时候如果往同一块盘中恢复数据文件就会报错需要我们重新rename 重名的datafile。数据文件状态为recover非online通常发生在客户使用RAC环境增加数据文件时候错误的将datafile加到了本地磁盘而发现错误后又执行了offline datafile操作。 本文主要测试在数据库中含有recover 状态的数据文件如果做rman恢复测试 1.创建测试表空间 SQL CREATE TABLESPACE test DATAFILE /u01/app/oracle/oradata/TEST/test01.dbf size 10M autoextend on EXTENT MANAGEMENT LOCAL;Tablespace created.SQL alter tablespace test add datafile /u01/app/oracle/oradata/TEST/test02.dbf size 10M;Tablespace altered.2.在测试表空间中创建表 SQL create table scott.test1 tablespace test as select * from dba_objects;Table created.SQL select count() from scott.test1 ;COUNT() ———-789183.模拟offline datafile 3.1 执行offline datafile SQL set line222 SQL col name for a80 SQL select file#,name,status from v\(datafile;FILE# NAME STATUS ---------- -------------------------------------------------------------------------------- --------------1 /u01/app/oracle/oradata/TEST/system01.dbf SYSTEM2 /u01/app/oracle/oradata/TEST/sysaux01.dbf ONLINE3 /u01/app/oracle/oradata/TEST/undotbs01.dbf ONLINE4 /u01/app/oracle/oradata/TEST/users01.dbf ONLINE5 /u01/app/oracle/oradata/TEST/example01.dbf ONLINE6 /u01/app/oracle/oradata/TEST/test01.dbf ONLINE7 /u01/app/oracle/oradata/TEST/test02.dbf ONLINESQL alter database datafile 7 offline;Database altered.SQL select file#,name,status from v\)datafile;FILE# NAME STATUS ———- ——————————————————————————– ————–1 /u01/app/oracle/oradata/TEST/system01.dbf SYSTEM2 /u01/app/oracle/oradata/TEST/sysaux01.dbf ONLINE3 /u01/app/oracle/oradata/TEST/undotbs01.dbf ONLINE4 /u01/app/oracle/oradata/TEST/users01.dbf ONLINE5 /u01/app/oracle/oradata/TEST/example01.dbf ONLINE6 /u01/app/oracle/oradata/TEST/test01.dbf ONLINE7 /u01/app/oracle/oradata/TEST/test02.dbf RECOVER7 rows selected.此时我们可以看到datafile 7 的状态已经变为recover。 3.2 再次查询报错 SQL select count() from scott.test1 ; select count() from scott.test1* ERROR at line 1: ORA-00376: file 7 cannot be read at this time ORA-01110: data file 7: /u01/app/oracle/oradata/TEST/test02.dbf3.3 继续创建测试表 SQL create table scott.test2 tablespace test as select * from dbaobjects;Table created.SQL select count() from scott.test2 ;COUNT() ———-789193.4 手动删除归档日志 SQL alter system switch logfile ;System altered.SQL /System altered.SQL alter system checkpoint;System altered.– 手动删除归档日志 [oracleora11204 ~]$ rm -rf 1*3.5 继续插入数据切换日志 SQL insert into scott.test2 select * from dba_objects;78919 rows created.SQL select count() from scott.test2 ;COUNT() ———-157838SQL alter system switch logfile ;System altered.SQL /System altered.SQL alter system checkpoint;System altered.结论虽然datafile 7 的状态变成了recover 但是受影响的只是原来的表test1而新表test2不受影响可以继续写入。 其实也就是说如果offline的datafile中不含有对象那么对数据的使用没有影响。 3.6 尝试recover datafile 7 SQL recover datafile 7; ORA-00279: change 4592370 generated at 07/09/2022 12:38:37 needed for thread 1 ORA-00289: suggestion : /home/oracle/1_37_1104664055.dbf ORA-00280: change 4592370 for thread 1 is in sequence #37Specify log: {RETsuggested | filename | AUTO | CANCEL} AUTO ORA-00308: cannot open archived log /home/oracle/1_37_1104664055.dbf ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3ORA-00308: cannot open archived log /home/oracle/1_37_1104664055.dbf ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3结论这个时候如果我们想去recover datafile 7恢复正常的online状态已经不行了因为归档日志都已经被手动删除了。 所以正确的做法应该是 在3.1手动执行offline datafile之后立即做recover datafile这样不管到什么时候都可以online datafile即使归档日志已经被删除。 此时我们的数据库中就包含了一个recover 状态的数据文件那么我们接下来看如何备份恢复

1. 模拟备份恢复 4.1 rman备份 – 备份前先删掉expired archivelog [oracleora11204 backup]\( rman target /Recovery Manager: Release 11.2.0.4.0 - Production on Sat Jul 9 12:47:26 2022Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.connected to target database: LHR11G (DBID2007947551)RMAN crosscheck archivelog all;RMAN delete expired archivelog all; Do you really want to delete the above objects (enter YES or NO)? yes deleted archived log archived log file name/home/oracle/1_18_1104664055.dbf RECID30 STAMP1107010872 deleted archived log archived log file name/home/oracle/1_19_1104664055.dbf RECID31 STAMP1107084082 deleted archived log archived log file name/home/oracle/1_20_1104664055.dbf RECID32 STAMP1107084317 deleted archived log archived log file name/home/oracle/1_21_1104664055.dbf RECID33 STAMP1107248395 deleted archived log archived log file name/home/oracle/1_23_1104664055.dbf RECID35 STAMP1107248458 deleted archived log archived log file name/home/oracle/1_24_1104664055.dbf RECID36 STAMP1107248477 deleted archived log archived log file name/home/oracle/1_25_1104664055.dbf RECID37 STAMP1107248494 deleted archived log archived log file name/home/oracle/1_26_1104664055.dbf RECID38 STAMP1107248708 deleted archived log archived log file name/home/oracle/1_27_1104664055.dbf RECID39 STAMP1107249210 deleted archived log archived log file name/home/oracle/1_28_1104664055.dbf RECID40 STAMP1107249384 deleted archived log archived log file name/home/oracle/1_29_1104664055.dbf RECID41 STAMP1107594979 deleted archived log archived log file name/home/oracle/1_30_1104664055.dbf RECID42 STAMP1107965313 deleted archived log archived log file name/home/oracle/1_31_1104664055.dbf RECID43 STAMP1109430200 deleted archived log archived log file name/home/oracle/1_32_1104664055.dbf RECID44 STAMP1109400986 deleted archived log archived log file name/home/oracle/1_33_1104664055.dbf RECID45 STAMP1109514747 deleted archived log archived log file name/home/oracle/1_34_1104664055.dbf RECID46 STAMP1109515327 deleted archived log archived log file name/home/oracle/1_35_1104664055.dbf RECID47 STAMP1109593810 deleted archived log archived log file name/home/oracle/1_36_1104664055.dbf RECID48 STAMP1109593862 deleted archived log archived log file name/home/oracle/1_37_1104664055.dbf RECID49 STAMP1109594665 deleted archived log archived log file name/home/oracle/1_38_1104664055.dbf RECID50 STAMP1109594666 Deleted 20 EXPIRED objects-- 正式备份 RMAN run{ 2 allocate channel c1 device type disk; 3 backup database filesperset 3 format /home/oracle/backup/db_%d_%T_%U; 4 backup archivelog all format /home/oracle/backup/arc_%t_%s; 5 backup current controlfile format /home/oracle/backup/cntrl_%s_%p_%s; 6 release channel c1; 7 } released channel: ORA_DISK_1 allocated channel: c1 channel c1: SID33 device typeDISKStarting backup at 2022-07-09 12:48:34 channel c1: starting full datafile backup set channel c1: specifying datafile(s) in backup set input datafile file number00001 name/u01/app/oracle/oradata/TEST/system01.dbf channel c1: starting piece 1 at 2022-07-09 12:48:34 channel c1: finished piece 1 at 2022-07-09 12:48:41 piece handle/home/oracle/backup/db_LHR11G_20220709_4k1264p2_1_1 tagTAG20220709T124834 commentNONE channel c1: backup set complete, elapsed time: 00:00:07 channel c1: starting full datafile backup set channel c1: specifying datafile(s) in backup set input datafile file number00003 name/u01/app/oracle/oradata/TEST/undotbs01.dbf input datafile file number00005 name/u01/app/oracle/oradata/TEST/example01.dbf input datafile file number00007 name/u01/app/oracle/oradata/TEST/test02.dbf channel c1: starting piece 1 at 2022-07-09 12:48:41 channel c1: finished piece 1 at 2022-07-09 12:48:44 piece handle/home/oracle/backup/db_LHR11G_20220709_4l1264p9_1_1 tagTAG20220709T124834 commentNONE channel c1: backup set complete, elapsed time: 00:00:03 channel c1: starting full datafile backup set channel c1: specifying datafile(s) in backup set input datafile file number00002 name/u01/app/oracle/oradata/TEST/sysaux01.dbf input datafile file number00004 name/u01/app/oracle/oradata/TEST/users01.dbf input datafile file number00006 name/u01/app/oracle/oradata/TEST/test01.dbf channel c1: starting piece 1 at 2022-07-09 12:48:44 channel c1: finished piece 1 at 2022-07-09 12:48:47 piece handle/home/oracle/backup/db_LHR11G_20220709_4m1264pc_1_1 tagTAG20220709T124834 commentNONE channel c1: backup set complete, elapsed time: 00:00:03 channel c1: starting full datafile backup set channel c1: specifying datafile(s) in backup set including current control file in backup set including current SPFILE in backup set channel c1: starting piece 1 at 2022-07-09 12:48:48 channel c1: finished piece 1 at 2022-07-09 12:48:49 piece handle/home/oracle/backup/db_LHR11G_20220709_4n1264pf_1_1 tagTAG20220709T124834 commentNONE channel c1: backup set complete, elapsed time: 00:00:01 Finished backup at 2022-07-09 12:48:49Starting backup at 2022-07-09 12:48:50 current log archived channel c1: starting archived log backup set channel c1: specifying archived log(s) in backup set input archived log thread1 sequence39 RECID51 STAMP1109594808 input archived log thread1 sequence40 RECID52 STAMP1109594809 input archived log thread1 sequence41 RECID53 STAMP1109594930 channel c1: starting piece 1 at 2022-07-09 12:48:50 channel c1: finished piece 1 at 2022-07-09 12:48:51 piece handle/home/oracle/backup/arc_1109594930_152 tagTAG20220709T124850 commentNONE channel c1: backup set complete, elapsed time: 00:00:01 Finished backup at 2022-07-09 12:48:51Starting backup at 2022-07-09 12:48:51 channel c1: starting full datafile backup set channel c1: specifying datafile(s) in backup set including current control file in backup set channel c1: starting piece 1 at 2022-07-09 12:48:52 channel c1: finished piece 1 at 2022-07-09 12:48:53 piece handle/home/oracle/backup/cntrl_153_1_153 tagTAG20220709T124851 commentNONE channel c1: backup set complete, elapsed time: 00:00:01 Finished backup at 2022-07-09 12:48:53released channel: c14.2. 恢复DB 利用备份做异机恢复拷贝备份、pfile文件、密码文件等过程省略… 4.2.1 启动数据库至nomount状态 SQL startup nomount ORACLE instance started.Total System Global Area 1068937216 bytes Fixed Size 2260088 bytes Variable Size 331350920 bytes Database Buffers 729808896 bytes Redo Buffers 5517312 bytes4.2.2 恢复控制文件 [oracleora11204 backup]\) rman target /Recovery Manager: Release 11.2.0.4.0 - Production on Sat Jul 9 12:51:00 2022Copyright © 1982, 2011, Oracle and/or its affiliates. All rights reserved.connected to target database: LHR11G (not mounted)RMAN restore controlfile from /home/oracle/backup/cntrl_153_1_153;Starting restore at 2022-07-09 12:51:01 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID19 device typeDISKchannel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 output file name/u01/app/oracle/oradata/TEST/control01.ctl Finished restore at 2022-07-09 12:51:034.2.3 mount数据库 RMAN alter database mount;database mounted released channel: ORA_DISK_14.2.4 restore database RMAN restore database;Starting restore at 2022-07-09 12:51:23 Starting implicit crosscheck backup at 2022-07-09 12:51:23 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID19 device typeDISK Crosschecked 22 objects Finished implicit crosscheck backup at 2022-07-09 12:51:24Starting implicit crosscheck copy at 2022-07-09 12:51:24 using channel ORA_DISK_1 Finished implicit crosscheck copy at 2022-07-09 12:51:24searching for all files in the recovery area cataloging files… no files catalogedusing channel ORA_DISK_1channel 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 /u01/app/oracle/oradata/TEST/system01.dbf channel ORA_DISK_1: reading from backup piece /home/oracle/backup/db_LHR11G_20220709_4k1264p2_1_1 channel ORA_DISK_1: piece handle/home/oracle/backup/db_LHR11G_20220709_4k1264p2_1_1 tagTAG20220709T124834 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:03 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 00003 to /u01/app/oracle/oradata/TEST/undotbs01.dbf channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/TEST/example01.dbf channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/TEST/test02.dbf channel ORA_DISK_1: reading from backup piece /home/oracle/backup/db_LHR11G_20220709_4l1264p9_1_1 channel ORA_DISK_1: piece handle/home/oracle/backup/db_LHR11G_20220709_4l1264p9_1_1 tagTAG20220709T124834 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:03 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 00002 to /u01/app/oracle/oradata/TEST/sysaux01.dbf channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/TEST/users01.dbf channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/TEST/test01.dbf channel ORA_DISK_1: reading from backup piece /home/oracle/backup/db_LHR11G_20220709_4m1264pc_1_1 channel ORA_DISK_1: piece handle/home/oracle/backup/db_LHR11G_20220709_4m1264pc_1_1 tagTAG20220709T124834 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:03 Finished restore at 2022-07-09 12:51:334.2.5 在RMAN中执行recover database RMAN recover database;Starting recover at 2022-07-09 12:51:49 using channel ORA_DISK_1starting media recoveryarchived log for thread 1 with sequence 39 is already on disk as file /home/oracle/1_39_1104664055.dbf archived log for thread 1 with sequence 40 is already on disk as file /home/oracle/1_40_1104664055.dbf archived log for thread 1 with sequence 41 is already on disk as file /home/oracle/1_41_1104664055.dbf Oracle Error: ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01152: file 1 was not restored from a sufficiently old backup ORA-01110: data file 1: /u01/app/oracle/oradata/TEST/system01.dbfRMAN-00571: RMAN-00569: ERROR MESSAGE STACK FOLLOWS RMAN-00571: RMAN-03002: failure of recover command at 07/09/2022 12:51:49 RMAN-06053: unable to perform media recovery because of missing log RMAN-06025: no backup of archived log for thread 1 with sequence 38 and starting SCN of 4593111 found to restore RMAN-06025: no backup of archived log for thread 1 with sequence 37 and starting SCN of 4591558 found to restore结论我们直接用传统的方式在rman中执行recover database会报错需要37、38 两个已经被删除的archive log无法完成recover。 4.2.6 在SQLPLUS中执行recover database – 先恢复归档全备中的归档日志 RMAN restore archivelog from logseq 39 thread 1;Starting restore at 2022-07-09 13:02:02 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID1 device typeDISKchannel ORA_DISK_1: starting archived log restore to default destination channel ORA_DISK_1: restoring archived log archived log thread1 sequence39 channel ORA_DISK_1: restoring archived log archived log thread1 sequence40 channel ORA_DISK_1: restoring archived log archived log thread1 sequence41 channel ORA_DISK_1: reading from backup piece /home/oracle/backup/arc_1109594930_152 channel ORA_DISK_1: piece handle/home/oracle/backup/arc_1109594930_152 tagTAG20220709T124850 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 2022-07-09 13:02:03– 执行 recover database操作 SQL recover database using backup controlfile until cancel; ORA-00279: change 4592370 generated at 07/09/2022 12:38:37 needed for thread 1 ORA-00289: suggestion : /home/oracle/1_37_1104664055.dbf ORA-00280: change 4592370 for thread 1 is in sequence #37结论我们在sqlplus中执行recover database也会报错需要37、38 两个已经被删除的archive log无法完成recover。 4.3. 解决办法 4.3.1 查询数据文件状态 SQL set line222 SQL col name for a60 SQL SELECT a.FILE#,a.NAME,a.RECOVER,a.CHECKPOINT_CHANGE#,status FROM v\(datafile_header a;FILE# NAME RECOVE CHECKPOINT_CHANGE# STATUS ---------- --------------------------------------------------------- ------ ------------------ -------------1 /u01/app/oracle/oradata/TEST/system01.dbf 4593921 ONLINE2 /u01/app/oracle/oradata/TEST/sysaux01.dbf 4593926 ONLINE3 /u01/app/oracle/oradata/TEST/undotbs01.dbf 4593924 ONLINE4 /u01/app/oracle/oradata/TEST/users01.dbf 4593926 ONLINE5 /u01/app/oracle/oradata/TEST/example01.dbf 4593924 ONLINE6 /u01/app/oracle/oradata/TEST/test01.dbf 4593926 ONLINE7 /u01/app/oracle/oradata/TEST/test02.dbf 4592370 ONLINE7 rows selected.SQL select file#,name,status from v\)datafile;FILE# NAME STATUS ———- ——————————————————— ————–1 /u01/app/oracle/oradata/TEST/system01.dbf SYSTEM2 /u01/app/oracle/oradata/TEST/sysaux01.dbf ONLINE3 /u01/app/oracle/oradata/TEST/undotbs01.dbf ONLINE4 /u01/app/oracle/oradata/TEST/users01.dbf ONLINE5 /u01/app/oracle/oradata/TEST/example01.dbf ONLINE6 /u01/app/oracle/oradata/TEST/test01.dbf ONLINE7 /u01/app/oracle/oradata/TEST/test02.dbf RECOVER7 rows selected.我们可以看到在vdatafileheader视图中datafile7状态仍然为online,在vdatafile_header视图中datafile 7 状态仍然为online,在vdatafileh​eader视图中datafile7状态仍然为online,在vdatafile中状态为recover。 4.3.2 执行offline datafile 操作 SQL alter database datafile 7 offline drop;Database altered.SQL set line222 SQL col name for a60 SQL SELECT a.FILE#,a.NAME,a.RECOVER,a.CHECKPOINT_CHANGE#,status FROM v\(datafile_header a;FILE# NAME RECOVE CHECKPOINT_CHANGE# STATUS ---------- ---------------------------------------------------- ------ ------------------ --------------1 /u01/app/oracle/oradata/TEST/system01.dbf 4593921 ONLINE2 /u01/app/oracle/oradata/TEST/sysaux01.dbf 4593926 ONLINE3 /u01/app/oracle/oradata/TEST/undotbs01.dbf 4593924 ONLINE4 /u01/app/oracle/oradata/TEST/users01.dbf 4593926 ONLINE5 /u01/app/oracle/oradata/TEST/example01.dbf 4593924 ONLINE6 /u01/app/oracle/oradata/TEST/test01.dbf 4593926 ONLINE7 /u01/app/oracle/oradata/TEST/test02.dbf 4592370 OFFLINE SQL recover database using backup controlfile until cancel; ORA-00279: change 4593921 generated at 07/09/2022 12:48:34 needed for thread 1 ORA-00289: suggestion : /home/oracle/1_41_1104664055.dbf ORA-00280: change 4593921 for thread 1 is in sequence #41Specify log: {RETsuggested | filename | AUTO | CANCEL} AUTO ORA-00279: change 4593946 generated at 07/09/2022 12:48:50 needed for thread 1 ORA-00289: suggestion : /home/oracle/1_42_1104664055.dbf ORA-00280: change 4593946 for thread 1 is in sequence #42 ORA-00278: log file /home/oracle/1_41_1104664055.dbf no longer needed for this recoveryORA-00308: cannot open archived log /home/oracle/1_42_1104664055.dbf ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3结论recover不在需要37、38 两个已经被删除的archive log正常完成recover 操作。 4.3.3 查看redo logfile tempfile -- 确保redo logfile tempfile路径存在或者rename file SQL select member from v\)logfile;MEMBER ———————————————————————————————————— /u01/app/oracle/oradata/TEST/redo01.log /u01/app/oracle/oradata/TEST/redo02.log /u01/app/oracle/oradata/TEST/redo03.logSQL select name from v$tempfile;NAME ———————————————————— /u01/app/oracle/oradata/TEST/temp01.dbf4.3.4 open数据库 SQL alter database open resetlogs;Database altered.4.4 验证数据 SQL select count() from scott.test2 ;COUNT() ———-157838SQL select count() from scott.test1 ; select count() from scott.test1* ERROR at line 1: ORA-00376: file 7 cannot be read at this time ORA-01110: data file 7: /u01/app/oracle/oradata/TEST/test02.dbf4.5 尝试删除datafile 7 SQL alter tablespace test drop datafile /u01/app/oracle/oradata/TEST/test02.dbf; alter tablespace test drop datafile /u01/app/oracle/oradata/TEST/test02.dbf * ERROR at line 1: ORA-03264: cannot drop offline datafile of locally managed tablespace结论datafile 7 删除报错如果一定要处理的话可以通过bbed的方式去修复后删除。