Remember database exception recovery once

Last Login:fri Jul 16:54:26 2018 from 9.1.173.100[[email protected] ~]# Su-oracle[ctcdb:oracle]:/home/oracle&gt   ; Sqlplus "/As SYSDBA" Sql*plus:release 11.2.0.4.0 Production on Fri Jul 16:55:38 2018Copyright (c) 1982,, Oracle. All rights reserved. Connected to an idle instance. Sql> Startuporacle instance started. Total System Global area 1.3683E+11 bytesfixed size 2270360 bytesvariable size 1.2616E+10 byte Sdatabase buffers 1.2402E+11 Bytesredo buffers 189456384 bytesdatabase mounted. Ora-00742:log read detects lost write in thread%d sequence%d block%dora-00312:online Log 1 thread 1: ' +datadg/orcl/re Do01.log ' sql> shutdown abortoracle instance shut down. Sql> Startup ORACLE instance started. Total System Global area 1.3683E+11 bytesfixed size 2270360 bytesvariable size 1.2616E+10 byte Sdatabase buffers 1.2402E+11 Bytesredo buffers 189456384 bytesdatabase mounted.orA-00742:log read detects lost write in thread%d sequence%d block%dora-00312:online Log 1 thread 1: ' +datadg/orcl/redo 01.log ' sql> RECOVER DATABASE USING BACKUP controlfile UNTIL cancelora-00279:change 361503907875 generated at 07/20/20 14:00:39 needed forthread 1ora-00289:suggestion: +archdgora-15173:entry ' ORCL ' does not exist in directory '/' ORA-00 280:change 361503907875 for thread 1 was in sequence #9922Specify log: {<RET>=suggested | filename | AUTO | Cancel}ora-00308:cannot Open archived log ' +ARCHDG ' Ora-17503:ksfdopn:2 Failed to open file +archdgora-15045:asm file na Me ' +ARCHDG ' isn't in reference Formora-01547:warning:recover succeeded but OPEN Resetlogs would get error belowORA-011  94:file 1 needs more recovery to be consistentora-01110:data file 1: ' +datadg/orcl/datafile/system.259.978924803 ' sql> ALTER DATABASE OPEN resetlogs; ALTER DATABASE OPEN resetlogs*error at line 1:ora-01194:file 1 needs + recovery to be consistentora-01110:data file 1 : ' +datadg/orcl/datafile/system.259.978924803 ' sql> shutdown abort;oracle instance shut down. Sql> Startup Mount;oracle instance started. Total System Global area 1.3683E+11 bytesfixed size 2270360 bytesvariable size 1.2616E+10 byte Sdatabase buffers 1.2402E+11 Bytesredo buffers 189456384 bytesdatabase mounted. Sql> exitdisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 -64bit Productionwith The partitioning, Automatic Storage Management, OLAP, Data miningand Real application Testing Opti Ons[ctcdb:oracle]:/home/oracle>lsnctl Status-bash:lsnctl:command not found[ctcdb:oracle]:/home/oracle>  Lsnrctl Statuslsnrctl for linux:version 11.2.0.4.0-production on 20-jul-2018 17:06:20copyright (c) 1991,-Oracle. All rights reserved.                     Connecting to (Address= (PROTOCOL=TCP) (host=) (port=1521)) STATUS of the LISTENER------------------------Alias Listenerversion Tnslsnr for linux:version 11.2.0.4.0-productionstart Date 20-jul                  -2018 16:53:40uptime 0 days 0 hr. Min. sectrace level offsecurity On:local OS authenticationsnmp Offlistener Parameter file/u01/app/11.2.0.4/grid/network/admin/lis  Tener.oralistener Log file/u01/app/grid/diag/tnslsnr/ctcdb/listener/alert/log.xmllistening Endpoints Summary ... (Description= (ADdress= (PROTOCOL=IPC) (key=extproc1521)) (Description= (address= (protocol=tcp) (HOST=CTCDB) (PORT=1521))) Services Summary ...  Service "+asm" has 1 instance (s). Instance "+asm", status ready, have 1 handler (s) for the This service ...  Service "ORCL" has 1 instance (s). Instance "ORCL", status ready, have 1 handler (s) for the This service ... The command completed Successfully[ctcdb:oracle]:/home/oracle>sqlplus "/as SYSDBA" Sql*plus:release 11.2.0.4.0  Production on Fri Jul 17:07:00 2018Copyright (c) 1982, Oracle. All rights reserved. Connected to:oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit productionwith the partitioning, Automatic  Storage Management, OLAP, Data miningand Real application testing optionssql> sql> sql> ALTER DATABASE Open;alter Database Open*error at line 1:ora-01589:must with resetlogs or noresetlogs option for database opensql> recover Databa     Se using Backup controlfile; Ora-00279:change 361503907875 generated at 07/20/2018 14:00:39 neeDed forthread 1ora-00289:suggestion: +archdgora-15173:entry ' ORCL ' does not exist in directory '/' Ora-00280:change 361 503907875 for thread 1 was in sequence #9922Specify log: {<RET>=suggested | filename | AUTO | Cancel}autoora-00308:cannot Open archived log ' +ARCHDG ' Ora-17503:ksfdopn:2 Failed to open File +archdgora-15045:asm fil E name ' +ARCHDG ' isn't in reference Formora-00308:cannot open archived log ' +ARCHDG ' Ora-17503:ksfdopn:2 Failed to open File +archdgora-15045:asm file name ' +ARCHDG ' isn't in reference formsql> ALTER DATABASE open Resetlogs;alter Databas E Open Resetlogs*error at line 1:ora-01113:file 1 needs media recoveryora-01110:data file 1: ' +datadg/orcl/datafile/syst em.259.978924803 ' sql> exitdisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit productio Nwith the partitioning, Automatic Storage Management, OLAP, Data miningand Real application testing options[ctcdb:oracle]: /home/oracle>ps-ef |        grep ckptgrid 11493 1 0 16:54?        00:00:00 asm_ckpt_+asmoracle 36151 1 0 17:05? 00:00:00 ora_ckpt_orcloracle 102216 11677 0 17:21 pts/2 00:00:00 grep ckpt[ctcdb:oracle]:/home/oracle>cat/etc/hosts127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4::1 Local Host Localhost.localdomain Localhost6 localhost6.localdomain610.1.71.73 ctcdb[ctcdb:oracle]:/home/oracle>  Sqlplus/as sysdbasql*plus:release 11.2.0.4.0 Production on Fri Jul 17:22:12 2018Copyright (c) 1982, DB, Oracle. All rights reserved. Connected to:oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit productionwith the partitioning, Automatic Storage Management, OLAP, Data miningand Real application Testing optionssql> Select Open_mode from V$database;open_mod E----------------------------------------mountedsql> ALTER DATABASE open;alter database Open*error at line 1:o Ra-01589:must use Resetlogs or noresetlogs option for database opensql> sql> sql> sql> sql> recover Databa Se;ora-00283:recovery session canceled due to errorsora-01610:recovery using the BACKUP controlfile option must be dones Ql> Show Parameter BackgrOundname TYPE----------------------------------------------------------VALUE------------- -----------------Background_core_dump stringpartialbackground_dump_dest String/u01/app/ora Cle/diag/rdbms/orcl/orcl/tracesql> Set line 300sql>/alter database open*error on line 1:ora-01589:must use RESETLO  GS or Noresetlogs option for database opensql> recover database using Backup Controlfile;ora-00279:change 361503907875  Generated at 07/20/2018 14:00:39 needed for thread 1ora-00289:suggestion: +archdgora-15173:entry ' ORCL ' does not exist In the directory '/' Ora-00280:change 361503907875 for thread 1 was in sequence #9922Specify log: {<RET>=suggested | filename | AUTO | Cancel}cancel Media recovery cancelled. Sql> Recover database;ora-00283:recovery Session canceled due to errorsora-01610:recovery using the BACKUP controlfil E option must is donesql> shutdown immediateora-01109:database not opendatabase dismounted. ORACLE instance shut down. Sql> exitdisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit productionwith the Partiti Oning, Automatic Storage Management, OLAP, Data miningand Real application Testing options[ctcdb:oracle]:/home/oracle >exitlogout[[email protected] ~]# Su-grid[ctcdb:grid]:/home/grid>which Crsctl/u01/app/11.2.0.4/grid/ Bin/crsctl[ctcdb:grid]:/home/grid>exitlogout[[email protected] ~]#/u01/app/11.2.0.4/grid/bin/crsctl stop Crscrs-4013:this command is not supported in a Single-node configuration. Crs-4000:command Stop failed, or completed with errors. [[email protected] ~]#/u01/app/11.2.0.4/grid/bin/crsctl stop crs-fcrs-4013:this COmmand is not supported in a Single-node configuration. Crs-4000:command Stop failed, or completed with errors. [[email protected] ~]#/u01/app/11.2.0.4/grid/bin/crsctl start crscrs-4013:this command is not supported in a SINGL E-node configuration. Crs-4000:command Start failed, or completed with errors. [[email protected] ~]#/u01/app/11.2.0.4/grid/bin/crsctl stop have crs-2791:starting shutdown of Oracle high Availab ility services-managed resources on ' ctcdb ' crs-2673:attempting to stop ' Ora. Listener.lsnr ' on ' ctcdb ' crs-2673:attempting to stop ' Ora. Archdg.dg ' on ' ctcdb ' crs-2673:attempting to stop ' Ora. Datadg.dg ' on ' ctcdb ' Crs-2677:stop of ' Ora. Archdg.dg ' on ' ctcdb ' Succeededcrs-2677:stop of ' Ora. Listener.lsnr ' on ' ctcdb ' succeededcrs-5017:the resource action ' Ora. DATADG.DG Stop "encountered the following error:ora-03113:end-of-file on communication channelprocess id:11558session I d:1099 Serial Number:3. For details refer to "(: CLSN00108:)" in "/u01/app/11.2.0.4/gRid/log/ctcdb/agent/ohasd/oraagent_grid//oraagent_grid.log ". Crs-2677:stop of ' Ora. Datadg.dg ' on ' ctcdb ' succeededcrs-2679:attempting-to-clean ' ora. Datadg.dg ' on ' ctcdb ' Crs-2681:clean of ' Ora. Datadg.dg ' on ' ctcdb ' succeededcrs-2673:attempting-stop ' ora.asm ' on ' ctcdb ' crs-2677:stop of ' ora.asm ' on ' ctcdb ' suc Ceededcrs-2673:attempting to stop ' ora.cssd ' in ' ctcdb ' crs-2677:stop of ' ora.cssd ' on ' ctcdb ' succeededcrs-2673:attempt ing to stop ' ora.evmd ' in ' ctcdb ' crs-2677:stop of ' ora.evmd ' on ' ctcdb ' Succeededcrs-2793:shutdown of Oracle high Availa Bility services-managed resources on ' ctcdb ' have completedcrs-4133:oracle high availability Services have been stopped. [[email protected] ~]#/u01/app/11.2.0.4/grid/bin/crsctl start crscrs-4013:this command is not supported in a SINGL E-node configuration. Crs-4000:command Start failed, or completed with errors. [[email protected] ~]#/u01/app/11.2.0.4/grid/bin/crsctl start hascrs-4123:oracle high availability Services have BEen started. [[email protected] ~]# su-oracle[ctcdb:oracle]:/home/oracle>sqlplus/as sysdbasql*plus:release 11.2.0.4.0  Production on Fri Jul 17:44:04 2018Copyright (c) 1982, Oracle. All rights reserved. Connected to an idle instance. Sql> Startup Nomount;oracle instance started. Total System Global area 1.3683E+11 bytesfixed size 2270360 bytesvariable size 1.2616E+10 byte Sdatabase buffers 1.2402E+11 Bytesredo buffers 189456384 bytessql> ALTER DATABASE Mount;database a Ltered. sql> ALTER DATABASE Open;alter database Open*error at line 1:ora-01589:must use Resetlogs or noresetlogs option for DA Tabase opensql> ALTER DATABASE recover;alter database Recover*error at line 1:ora-00283:recovery session canceled due To Errorsora-01610:recovery using the BACKUP controlfile option must is donesql> ALTER DATABASE open Resetlogs;alter D Atabase Open Resetlogs*error at line 1:ora-01113:file 1 needs media recoveryORA-01110:data file 1: ' +datadg/orcl/datafile/system.259.978924803 ' sql> sql> sql> recover datafile 1;ORA-00283:  Recovery session canceled due to errorsora-01610:recovery using the BACKUP controlfile option must be donesql> recover Database using Backup Controlfile;ora-00279:change 361503907875 generated at 07/20/2018 14:00:39 needed Forthread 1ora-0  0289:suggestion: +archdgora-15173:entry ' ORCL ' does not exist in directory '/' Ora-00280:change 361503907875 for thread 1 is in sequence #9922Specify log: {<RET>=suggested | filename | AUTO | Cancel}autoora-00308:cannot Open archived log ' +ARCHDG ' Ora-17503:ksfdopn:2 Failed to open File +archdgora-15045:asm fil E name ' +ARCHDG ' isn't in reference Formora-00308:cannot open archived log ' +ARCHDG ' Ora-17503:ksfdopn:2 Failed to open                       File +archdgora-15045:asm file name ' +ARCHDG ' is not in reference formsql> archive log list; Database log mode No Archive modeautomatic archival disabledarchive destination +archd Goldest Online log sequence 9920Current log sequence 9922sql> select file#,checkpoint_change# from V$data     File_header;       file# checkpoint_change#----------------------------1 361503907875 2 361503907875 3       361503907875 4 361503907875 5 361503907875 6 361503907875 7  361503907875 8 361503907875 9 361503907875 10 361503907875      361503907875 file# checkpoint_change#----------------------------12 361503907875       13 361503907875 14 361503907875 15 361503907875 16 361503907875 17 361503907875 18 361503907875 19 361503907875 20 361503907875 21 361 503907875 361503907875 file# checkpoint_change#----------------------------23 361503907        875 24 361503907875 25 361503907875 26 361503907875 27 361503907875 361503907875sql> RECOVER DATABASE USING BACKUP controlfile UNTIL cancel;ora-00279:change 361503907875 ge nerated at 07/20/2018 14:00:39 needed forthread 1ora-00289:suggestion: +archdgora-00280:change 361503907875 for thread 1 is in sequence #9922Specify log: {<RET>=suggested | filename | AUTO | Cancel}+datadg/orcl/redo01.loglog applied. Media recovery complete. sql> ALTER DATABASE open resetlogs;     Sql> select file#,checkpoint_change# from V$datafile_header;       file# checkpoint_change#----------------------------1 361503908937 2 361503908937 3       361503908937 4 361503908937 5 361503908937 6 361503908937 7 361503908937 8 361503908937 9 361503908937 10 361503908937 11 361503         908937 file# checkpoint_change#----------------------------12 361503908937 13 361503908937        14 361503908937 15 361503908937 16 361503908937 17 361503908937       18 361503908937 19 361503908937 20 361503908937 21 361503908937 22 361503908937 file# checkpoint_change#----------------------------23 361503908937 24 361503908937 25 361503908937 26 36150390                                         8937 27 361503908937) 28 361503908937

Remember database exception recovery once