If current redo log deleted at OS level , How to recover database error.
- Check the status of redo log group
SQL> SELECT G.GROUP#, G.STATUS FROM V$LOG G ORDER BY 1;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 INACTIVE
3 INACTIVE
- Group 1 is in current state
SQL> SELECT G.GROUP# FROM V$LOG G WHERE STATUS='CURRENT';
GROUP#
----------
1
- Check the location of current redo log group in current state.
SQL> SELECT 'host rm -f ' || MEMBER AS CODE FROM V$LOGFILE WHERE GROUP#=(SELECT G.GROUP# FROM V$LOG G WHERE STATUS='CURRENT');
CODE
--------------------------------------------------------------------------------
host rm -f /u01/app/oracle/oradata/plm/redo01.log
- Remove file at OS level
SQL> host rm -f /u01/app/oracle/oradata/plm/redo01.log
- Check status of database
SQL> host ps -ef | grep pmon
grid 2919 1 0 May21 ? 00:00:03 asm_pmon_+ASM1
grid 4252 1 0 May21 ? 00:00:03 mdb_pmon_-MGMTDB
oracle 4499 1 0 May21 ? 00:00:03 ora_pmon_RAC1
oracle 9241 1 0 May21 ? 00:00:03 ora_pmon_plm
oracle 15302 11529 0 13:00 pts/0 00:00:00 /bin/bash -c ps -ef | grep pmon
oracle 15304 15302 0 13:00 pts/0 00:00:00 grep pmon
- Create table and create check point so we can see error in alert log.
SQL> CREATE TABLE TEST.TEST AS SELECT * FROM DBA_TABLES;
Table created.
SQL> alter system switch logfile;
System altered.
- Alert.log details:
Errors in file /u01/app/oracle/diag/rdbms/plm/plm/trace/plm_arc1_9509.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/plm/redo01.log'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Fri May 22 13:02:07 2020
Errors in file /u01/app/oracle/diag/rdbms/plm/plm/trace/plm_arc1_9509.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/plm/redo01.log'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Fri May 22 13:02:07 2020
Errors in file /u01/app/oracle/diag/rdbms/plm/plm/trace/plm_arc1_9509.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/plm/redo01.log'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Master background archival failure: 313
Actions to restore the lost redo log group
- Shutdown database
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
- Startup database in mount stage
SQL> STARTUP MOUNT
ORACLE instance started.
Total System Global Area 2717908992 bytes
Fixed Size 2928104 bytes
Variable Size 704643608 bytes
Database Buffers 1996488704 bytes
Redo Buffers 13848576 bytes
Database mounted.
- Clear logfile group 1
SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1;
oracle@suse1:~> rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Fri May 22 13:45:50 2020
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: PLM (DBID=1811128417, not open)
- Restore database
RMAN> RESTORE DATABASE;
Starting restore at 22-MAY-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=27 device type=DISK
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 /u01/app/oracle/oradata/plm/system01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/plm/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/plm/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/plm/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/PLM/backupset/2020_05_21/o1_mf_nnndf_FULL_DB_hddvcjly_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/PLM/backupset/2020_05_21/o1_mf_nnndf_FULL_DB_hddvcjly_.bkp tag=FULL_DB
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:05
Finished restore at 22-MAY-20
- Recover database
RMAN> RECOVER DATABASE;
Starting recover at 22-MAY-20
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/fast_recovery_area/PLM/archivelog/2020_05_21/o1_mf_1_8_hddvz6y8_.arc
archived log for thread 1 with sequence 10 is already on disk as file /u01/app/oracle/fast_recovery_area/PLM/archivelog/2020_05_21/o1_mf_1_10_hddx2olb_.arc
archived log for thread 1 with sequence 11 is already on disk as file /u01/app/oracle/fast_recovery_area/PLM/archivelog/2020_05_21/o1_mf_1_11_hddx8kn1_.arc
archived log for thread 1 with sequence 12 is already on disk as file /u01/app/oracle/fast_recovery_area/PLM/archivelog/2020_05_21/o1_mf_1_12_hddx8lyl_.arc
archived log for thread 1 with sequence 13 is already on disk as file /u01/app/oracle/fast_recovery_area/PLM/archivelog/2020_05_21/o1_mf_1_13_hddx8p22_.arc
archived log for thread 1 with sequence 14 is already on disk as file /u01/app/oracle/fast_recovery_area/PLM/archivelog/2020_05_21/o1_mf_1_14_hddy5dt7_.arc
archived log for thread 1 with sequence 15 is already on disk as file /u01/app/oracle/fast_recovery_area/PLM/archivelog/2020_05_21/o1_mf_1_15_hdfc7n4y_.arc
archived log for thread 1 with sequence 16 is already on disk as file /u01/app/oracle/fast_recovery_area/PLM/archivelog/2020_05_21/o1_mf_1_16_hdfcbbf5_.arc
archived log for thread 1 with sequence 17 is already on disk as file /u01/app/oracle/fast_recovery_area/PLM/archivelog/2020_05_21/o1_mf_1_17_hdfcctsj_.arc
archived log for thread 1 with sequence 18 is already on disk as file /u01/app/oracle/fast_recovery_area/PLM/archivelog/2020_05_22/o1_mf_1_18_hdfyjmp6_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/PLM/archivelog/2020_05_21/o1_mf_1_8_hddvz6y8_.arc thread=1 sequence=8
unable to find archived log
archived log thread=1 sequence=9
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/22/2020 13:48:07
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 9 and starting SCN of 1679803
- Recover database until cancel
SQL> RECOVER DATABASE until cancel;
ORA-00279: change 1679803 generated at 05/21/2020 17:41:18 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/PLM/archivelog/2020_05_22/o1_mf_1_9_%u_.arc
ORA-00280: change 1679803 for thread 1 is in sequence #9
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00308: cannot open archived log
'/u01/app/oracle/fast_recovery_area/PLM/archivelog/2020_05_22/o1_mf_1_9_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log
'/u01/app/oracle/fast_recovery_area/PLM/archivelog/2020_05_22/o1_mf_1_9_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
- Open database in resetlogs
SQL> alter database open resetlogs;
Database altered.
SQL>
- Check the status of redo log
SQL> @display_logs
GROUP# G_STATUS M_STATU MEMBER
---------- ---------------- ------- ------------------------------
1 CURRENT /u01/app/oracle/oradata/plm/re
do01.log
2 UNUSED /u01/app/oracle/oradata/plm/re
do02.log
3 UNUSED /u01/app/oracle/oradata/plm/re
do03.log
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> @display_logs
GROUP# G_STATUS M_STATU MEMBER
---------- ---------------- ------- ------------------------------
1 CURRENT /u01/app/oracle/oradata/plm/re
do01.log
2 INACTIVE /u01/app/oracle/oradata/plm/re
do02.log
3 INACTIVE /u01/app/oracle/oradata/plm/re
do03.log
SQL>
No comments:
Post a Comment