Hello Friend's,
In this post we will discuss about the database crash error for oracle restart (ASM)
Below is the error recived when tried to start the database.
[oracle@dm01gg01 ~]$ srvctl start database -d CDBSOURCE
PRCR-1079 : Failed to start resource ora.cdbsource.db
CRS-5017: The resource action "ora.cdbsource.db start" encountered the following error:
ORA-00742: Log read detects lost write in thread 1 sequence 9 block 11906
ORA-00312: online log 3 thread 1: '+FRA/CDBSOURCE/ONLINELOG/group_3.259.10457625 95'
ORA-00312: online log 3 thread 1: '+DATA/CDBSOURCE/ONLINELOG/group_3.264.1045762 575'
. For details refer to "(:CLSN00107:)" in "/oracle/app/grid/diag/crs/dm01gg01/cr s/trace/ohasd_oraagent_grid.trc".
CRS-2674: Start of 'ora.cdbsource.db' on 'dm01gg01' failed
now because of this error database will not start , so we have to try and mount the database.
srvctl start database -d CDBSOURCE -o mount or startup mount from sql prompt
and check the logfiles status as below
SQL> set lines 200
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
1 1 7 209715200 512 2 YES INACTIVE 1643718 16-JUL-20 1758312 27-JUL-20 0
3 1 9 209715200 512 2 NO CURRENT 1866863 27-JUL-20 1.8447E+19 0
2 1 8 209715200 512 2 YES INACTIVE 1758312 27-JUL-20 1866863 27-JUL-20 0
SQL> col MEMBER for a50
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- -------------------------------------------------- --- ----------
3 ONLINE +DATA/CDBSOURCE/ONLINELOG/group_3.264.1045762575 NO 0
3 ONLINE +FRA/CDBSOURCE/ONLINELOG/group_3.259.1045762595 YES 0
2 ONLINE +DATA/CDBSOURCE/ONLINELOG/group_2.263.1045762543 NO 0
2 ONLINE +FRA/CDBSOURCE/ONLINELOG/group_2.258.1045762581 YES 0
1 ONLINE +DATA/CDBSOURCE/ONLINELOG/group_1.262.1045762543 NO 0
1 ONLINE +FRA/CDBSOURCE/ONLINELOG/group_1.257.1045762561 YES 0
6 rows selected.
Now we have to recover the database until cancel try all the logfile one by one until you get the output as shown below
SQL> recover database until cancel;
ORA-00279: change 1866864 generated at 07/27/2020 20:13:39 needed for thread 1
ORA-00289: suggestion : +FRA
ORA-00280: change 1866864 for thread 1 is in sequence #9
Specify log: {=suggested | filename | AUTO | CANCEL}
+DATA/CDBSOURCE/ONLINELOG/group_3.264.1045762575
Log applied.
Media recovery complete.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 1872963 generated at 07/27/2020 20:49:08 needed for thread 1
ORA-00289: suggestion : +FRA
ORA-00280: change 1872963 for thread 1 is in sequence #9
Specify log: {=suggested | filename | AUTO | CANCEL}
+FRA/CDBSOURCE/ONLINELOG/group_3.259.1045762595
Log applied.
Media recovery complete.
Now try to open the database, it will give an error ,as this becomes the incomplete recovery we have to open database in resetlogs mode
SQL> alter database open resetlogs;
Database altered.
SQL> set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a30
select name DB_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,log_mode,version DB_VERSION,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,v$instance;
SQL> SQL> SQL>
DB_NAME HOST_NAME DATABASE_ROLE OPEN_MODE LOG_MODE DB_VERSION LOGINS DB UP TIME
--------- ------------------------------ ---------------- ---------- ------------ ----------------- ---------- -----------------------------
CDBSOURC dm01gg01.database.com PRIMARY READ WRITE ARCHIVELOG 12.2.0.1.0 ALLOWED 28-JUL-2020 08:18:19
SQL>
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBSOURCE READ WRITE NO
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
1 1 1 209715200 512 2 NO CURRENT 1872965 28-JUL-20 1.8447E+19 0
2 1 0 209715200 512 2 YES UNUSED 0 0 0
3 1 0 209715200 512 2 YES UNUSED 0 0 0
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- -------------------------------------------------- --- ----------
3 ONLINE +DATA/CDBSOURCE/ONLINELOG/group_3.264.1045762575 NO 0
3 ONLINE +FRA/CDBSOURCE/ONLINELOG/group_3.259.1045762595 YES 0
2 ONLINE +DATA/CDBSOURCE/ONLINELOG/group_2.263.1045762543 NO 0
2 ONLINE +FRA/CDBSOURCE/ONLINELOG/group_2.258.1045762581 YES 0
1 ONLINE +DATA/CDBSOURCE/ONLINELOG/group_1.262.1045762543 NO 0
1 ONLINE +FRA/CDBSOURCE/ONLINELOG/group_1.257.1045762561 YES 0
6 rows selected.
now stop and start the database again and swicth some logs and check, Error is resolved.
[oracle@dm01gg01 ~]$ srvctl stop database -d CDBSOURCE
[oracle@dm01gg01 ~]$
[oracle@dm01gg01 ~]$ srvctl start database -d CDBSOURCE
[oracle@dm01gg01 ~]$
[oracle@dm01gg01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Tue Jul 28 08:48:51 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a30
select name DB_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,log_mode,version DB_VERSION,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,v$instance;
SQL> SQL> SQL>
DB_NAME HOST_NAME DATABASE_ROLE OPEN_MODE LOG_MODE DB_VERSION LOGINS DB UP TIME
--------- ------------------------------ ---------------- ---------- ------------ ----------------- ---------- -----------------------------
CDBSOURC dm01gg01.database.com PRIMARY READ WRITE ARCHIVELOG 12.2.0.1.0 ALLOWED 28-JUL-2020 08:37:22
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBSOURCE READ WRITE NO
Hope This helps,
Regards,
Sultan Khan
ConversionConversion EmoticonEmoticon