麒麟v10 上部署 TiDB v5.1.2 生产环境优化实践
724
2023-05-01
恢复控制文件后如何避免resetlogs打开数据库？
恢复控制文件后如何避免resetlogs打开数据库？
恢复控制文件后，数据库使用resetlogs方式打开，在线日志的序列被重置。
SYS@practice >select group#,sequence#,archived,status,first_change#,next_change# from v$log; GROUP# SEQUENCE# ARC STATUS FIRST_CHANGE# NEXT_CHANGE# ---------- ---------- --- ---------------- ------------- ------------ 1 1 NO CURRENT 1014415 2.8147E+14 2 0 YES UNUSED 0 0 3 0 YES UNUSED 0 0
如果没有被resetlog截断的话当前日志的序列号应该是7，8，9
SYS@practice >select recid,sequence#,first_change#,next_change# from v$log_history; RECID SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# ---------- ---------- ------------- ------------ 1 1 925702 955284 2 2 955284 955847 3 3 955847 971151 4 4 971151 987800 5 5 987800 997957 6 6 997957 1010981 7 1 1010981 1013463 8 2 1013463 1013471 9 3 1013471 1014415
如果恢复完控制文件，不使用resetlogs是打不开数据库的。
我们可以采取手工创建控制文件的方法打开数据库，保持联机日志的sequence#连续。下面的实验来演示具体操作过程。
在实验之前先手工切换3次日志，使得在线日志的序列号产生变化。在恢复结束后作为参考。
SYS@practice >alter system archive log current; SYS@practice >alter system archive log current; SYS@practice >alter system archive log current;
切换后当前连接日志状态如下
SYS@practice >select group#,sequence#,archived,status,first_change#,next_change# from v$log; GROUP# SEQUENCE# ARC STATUS FIRST_CHANGE# NEXT_CHANGE# ---------- ---------- --- ---------------- ------------- ------------ 1 4 NO CURRENT 1030130 2.8147E+14 2 2 YES INACTIVE 1030109 1030117 3 3 YES ACTIVE 1030117 1030130
1、恢复控制文件
2、启动数据库到mount状态
RMAN> mount database; database mounted released channel: ORA_DISK_1
3、恢复数据库
4，备份控制文件到trace
SYS@practice >select open_mode from v$database; OPEN_MODE -------------------- MOUNTED SYS@practice >alter database backup controlfile to trace; SYS@practice >select value from v$diag_info where name='Default Trace File'; VALUE -------------------------------------------------------------------------------- /u01/diag/rdbms/practice/practice/trace/practice_ora_1185.trc
5、重启实例到nomount状态
RMAN> startup force nomount; *** instance started Total System Global Area 580395008 bytes Fixed Size 2255392 bytes Variable Size 402654688 bytes Database Buffers 171966464 bytes Redo Buffers 3518464 bytes
6、执行重建控制文件命令进入到mount状态
vi /home/***/create_controlfile.sql STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "PRACTICE" NORESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/u01/oradata/practice/redo01.log' SIZE 50M BLOCKSIZE 512, GROUP 2 '/u01/oradata/practice/redo02.log' SIZE 50M BLOCKSIZE 512, GROUP 3 '/u01/oradata/practice/redo03.log' SIZE 50M BLOCKSIZE 512 DATAFILE '/u01/oradata/practice/system01.dbf', '/u01/oradata/practice/sysaux01.dbf', '/u01/oradata/practice/undotbs01.dbf', '/u01/oradata/practice/users01.dbf', '/u01/oradata/practice/example01.dbf' CHARACTER SET AL32UTF8 ; VARIABLE RECNO NUMBER; EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON'); RECOVER DATABASE ALTER SYSTEM ARCHIVE LOG ALL; ALTER DATABASE OPEN; ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/oradata/practice/temp01.dbf' REUSE;
在sqlplus下执行创建控制文件脚本，数据库会启动到open状态并添加临时表空间文件。
此时数据库已经处于read write状态也就是open状态了
SYS@practice >select open_mode from v$database; OPEN_MODE -------------------- READ WRITE
查看当前日志的序列号，没有被重置。
SYS@practice >select group#,sequence#,archived,status,first_change#,next_change# from v$log; GROUP# SEQUENCE# ARC STATUS FIRST_CHANGE# NEXT_CHANGE# ---------- ---------- --- ---------------- ------------- ------------ 1 4 YES INACTIVE 1030130 1050296 2 5 NO CURRENT 1050296 2.8147E+14 3 3 YES INACTIVE 1030117 1030130
查看临时表数据文件也被创建出来了。
SYS@practice >select name from v$tempfile; NAME -------------------------------------------------------------------------------- /u01/oradata/practice/temp01.dbf
7，重新识别控制文件的备份信息和配置信息
此时的控制文件中没有之前备份过控制文件的信息
RMAN> list backup of controlfile; using target database control file instead of recovery catalog specification does not match any backup in the repository
从闪回恢复区重新注册备份信息
RMAN> catalog db_recovery_file_dest;
从执行过数据库全备份的地址注册备份信息
RMAN> catalog start with '/home/***/';
再次列出备份过的控制文件，可以看到已经全部注册成功
RMAN> list backup of controlfile; List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 1 Full 9.67M DISK 00:00:00 06-OCT-14 BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20141006T175610 Piece Name: /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860262970_b363zt2x_.bkp Control File Included: Ckp SCN: 1051644 Ckp time: 06-OCT-14 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 3 Full 9.33M DISK 00:00:00 06-OCT-14 BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20141006T132827 Piece Name: /u01/fast_recovery_area/PRACTICE/backupset/2014_10_06/o1_mf_ncnnf_TAG20141006T132827_b35n9w39_.bkp Control File Included: Ckp SCN: 1005439 Ckp time: 06-OCT-14 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 7 Full 9.33M DISK 00:00:00 06-OCT-14 BP Key: 7 Status: AVAILABLE Compressed: NO Tag: TAG20141006T143909 Piece Name: /home/***/full_PRACTICE_9_20141006_1.bak Control File Included: Ckp SCN: 1013438 Ckp time: 06-OCT-14
版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。
相关文章
