An example of Oracle recovery -- ora-03113, ora-24324, ora-01041 error
Wheat seedling DB 2021-06-26 08:06:30
Oracle A case of recovery --ORA-03113ORA-24324,ORA-01041 error

background :

 

    It's over tonight OCM After the course , There is one OCP And high availability students turn to Mr. mai . His library is Windows 10.2.0.1 The database of , And then because of the virus , The students made a cold preparation for the database , And then we did the recovery operation , The result is that it can't be started when it's started , Report errors ORA-03113、ORA-24324,ORA-01041 error . After simple guidance, wechat still can't be started , So I can only TEAMVIEWER Or sunflower to help solve the problem remotely .TV It was disconnected after a while , Only sunflowers .

 

First of all, the first problem , It is estimated that the database service is used before , So we need to use oradim Reconstruction services :

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

Microsoft Windows [ edition  5.2.3790]

(C)  copyright  1985-2003 Microsoft Corp.

C:\Documents and Settings\Administrator>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on  Thursday  8 month  1 22:21:19 2019

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected .

SQL> select open_mode from v$database;

select open_mode from v$database

*

The first  1  Line error :

ORA-01012: not logged on

SQL> conn / as sysdba

Connected to idle routine .

SQL> startup nomount

ORA-01081: cannot start already-running ORACLE - shut it down first

SQL> shutdown abort

ORA-03113:  End of communication channel file

SQL> startup nomount

ORA-24324:  Service handle not initialized

ORA-01041:  internal error , hostdef  Extension does not exist

SQL>

SQL> shutdown abort

ORA-24324:  Service handle not initialized

ORA-01041:  internal error , hostdef  Extension does not exist

SQL>

SQL> exit

Disconnected connection

C:\Documents and Settings\Administrator>

C:\Documents and Settings\Administrator>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on  Thursday  8 month  1 22:22:42 2019

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

ERROR:

ORA-12560: TNS:  Protocol adapter error

Please enter a user name :

ERROR:

ORA-12560: TNS:  Protocol adapter error

Please enter a user name :

ERROR:

ORA-12560: TNS:  Protocol adapter error

SP2-0157:  stay  3  Unable to connect to after attempts  ORACLE,  sign out  SQL*Plus

C:\Documents and Settings\Administrator>sc delete oracleserviceorcl

[SC] DeleteService  success

C:\Documents and Settings\Administrator>oradim

ORADIM:  [options]. Please refer to the manual .

Enter one of the following commands :

Create an instance by specifying the following options :

     -NEW -SID sid | -SRVC srvc | -ASMSID sid | -ASMSRVC srvc [-SYSPWD pass]

 [-STARTMODE auto|manual] [-SRVCSTART system|demand] [-PFILE file | -SPFILE]

 [-SHUTMODE normal|immediate|abort] [-TIMEOUT secs] [-RUNAS osusr/ospass]

Edit the instance by specifying the following options :

     -EDIT -SID sid | -ASMSID sid [-SYSPWD pass]

 [-STARTMODE auto|manual] [-SRVCSTART system|demand] [-PFILE file | -SPFILE]

 [-SHUTMODE normal|immediate|abort] [-SHUTTYPE srvc|inst] [-RUNAS osusr/ospass]

Delete instances by specifying the following options :

     -DELETE -SID sid | -ASMSID sid | -SRVC srvc | -ASMSRVC srvc

Start the service and instance by specifying the following options :

     -STARTUP -SID sid | -ASMSID sid [-SYSPWD pass]

 [-STARTTYPE srvc|inst|srvc,inst] [-PFILE filename | -SPFILE]

Shut down services and instances by specifying the following options :

     -SHUTDOWN -SID sid | -ASMSID sid [-SYSPWD pass]

 [-SHUTTYPE srvc|inst|srvc,inst] [-SHUTMODE normal|immediate|abort]

  Query help by specifying the following parameters : -? | -h | -help

C:\Documents and Settings\Administrator>oradim -new -sid ORCL -pfile E:\oracle\p

roduct\10.2.0\db_1\database\initorcl.ora

Instance created .

C:\Documents and Settings\Administrator>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on  Thursday  8 month  1 22:32:21 2019

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to idle routine .

SQL> startup nomount

ORACLE  Routine started .

Total System Global Area  612368384 bytes

Fixed Size                  1332348 bytes

Variable Size             174762884 bytes

Database Buffers          432013312 bytes

Redo Buffers                4259840 bytes

SQL> alter database mount;

The database has changed .

SQL> alter database open;

alter database open

*

The first  1  Line error :

ORA-03113:  End of communication channel file

SQL>

  Finally working startup The command , however open It's still a mistake , Then through the alarm log, we found pfile There are a lot of wrong paths in the library , also redo The log also reported an error : 

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

orcl.__db_cache_size=432013312

orcl.__java_pool_size=4194304

orcl.__large_pool_size=4194304

orcl.__shared_pool_size=163577856

orcl.__streams_pool_size=0

*.audit_file_dest='E:\oracle\product\10.2.0/admin/orcl/adump'

*.background_dump_dest='E:\oracle\product\10.2.0/admin/orcl/bdump'

*.compatible='10.2.0.1.0'

*.control_files='E:\oracle\product\10.2.0/oradata/orcl/\control01.ctl','E:\oracle\product\10.2.0/oradata/orcl/\control02.ctl','E:\oracle\product\10.2.0/oradata/orcl/\control03.ctl'

*.core_dump_dest='E:\oracle\product\10.2.0/admin/orcl/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='orcl'

*.db_recovery_file_dest='E:\oracle\product\10.2.0/flash_recovery_area'

*.db_recovery_file_dest_size=2147483648

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.job_queue_processes=10

*.nls_language='SIMPLIFIED CHINESE'

*.nls_territory='CHINA'

*.open_cursors=300

*.pga_aggregate_target=203423744

*.processes=1000

*.remote_login_passwordfile='EXCLUSIVE'

*.sessions=1105

*.sga_target=612368384

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='E:\oracle\product\10.2.0/admin/orcl/udump'

therefore , I changed it to the following pattern : 

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

orcl.__db_cache_size=432013312

orcl.__java_pool_size=4194304

orcl.__large_pool_size=4194304

orcl.__shared_pool_size=163577856

orcl.__streams_pool_size=0

*.audit_file_dest='E:\oracle\product\10.2.0\admin\orcl\adump'

*.background_dump_dest='E:\oracle\product\10.2.0\admin\orcl\bdump'

*.compatible='10.2.0.1.0'

*.control_files='E:\oracle\product\10.2.0\oradata\orcl\control01.ctl','E:\oracle\product\10.2.0\oradata\orcl\control02.ctl','E:\oracle\product\10.2.0\oradata\orcl\control03.ctl'

*.core_dump_dest='E:\oracle\product\10.2.0\admin\orcl\cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='orcl'

*.db_recovery_file_dest='E:\oracle\product\10.2.0\flash_recovery_area'

*.db_recovery_file_dest_size=2147483648

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.job_queue_processes=10

*.nls_language='SIMPLIFIED CHINESE'

*.nls_territory='CHINA'

*.open_cursors=300

*.pga_aggregate_target=203423744

*.processes=1000

*.remote_login_passwordfile='EXCLUSIVE'

*.sessions=1105

*.sga_target=612368384

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='E:\oracle\product\10.2.0\admin\orcl\udump'

Then restart the database , Boot to mount Stage , Keep looking at redo Log files :

1

2

3

4

5

6

7

8

9

10

11

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS

---------- ---------- ---------- ---------- ---------- --- ----------

FIRST_CHANGE# FIRST_TIME

------------- --------------

         1          1      19103   52428800          1 NO  INACTIVE

    471044279 07-6 month  -19

         3          1      19102   52428800          1 NO  INACTIVE

    471009748 06-6 month  -19

         2          1      19104   52428800          1 NO  CURRENT

    471095223 07-6 month  -19

I tried to clear the damaged log file , But the premise of successful execution : Consistency off , Obviously, this order doesn't work , And try rman Not good either. :

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

SQL> startup force mount

ORACLE  Routine started .

Total System Global Area  612368384 bytes

Fixed Size                  1332348 bytes

Variable Size             174762884 bytes

Database Buffers          432013312 bytes

Redo Buffers                4259840 bytes

Database loading complete .

SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;

ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2

*

The first  1  Line error :

ORA-01624:  journal  2  It's an example of emergency recovery  orcl ( Threads  1)  Necessary

ORA-00312:  Online log  2  Threads  1:

'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG'

SQL> ALTER DATABASE CLEAR  LOGFILE GROUP 2;

ALTER DATABASE CLEAR  LOGFILE GROUP 2

*

The first  1  Line error :

ORA-01624:  journal  2  It's an example of emergency recovery  orcl ( Threads  1)  Necessary

ORA-00312:  Online log  2  Threads  1:

'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG'

SQL> alter system switch logfile;

alter system switch logfile

*

The first  1  Line error :

ORA-01109:  database not open

  

SQL> recover database until cancel;

ORA-00279:  change  471095223 ( stay  06/07/2019 20:22:28  Generate )  For threads  1  It's necessary

ORA-00289:  Suggest :

E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2019_08_01\O1_MF_1_

19104_%U_.ARC

ORA-00280:  change  471095223 ( For threads  1)  In the sequence  #19104  in

Specify log : {

auto

ORA-00308:  Unable to open archive log

'E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2019_08_01\O1_MF_1

_19104_%U_.ARC'

ORA-27041:  Can't open file

OSD-04002:  Can't open file

O/S-Error: (OS 2)  The system cannot find the specified file .

ORA-00308:  Unable to open archive log

'E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2019_08_01\O1_MF_1

_19104_%U_.ARC'

ORA-27041:  Can't open file

OSD-04002:  Can't open file

O/S-Error: (OS 2)  The system cannot find the specified file .

ORA-01547:  Warning : RECOVER  Successful but  OPEN RESETLOGS  The following error will occur

ORA-01194:  file  1  More recovery is needed to maintain consistency

ORA-01110:  Data files  1: 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF'

SQL> alter database open noresetlogs;

alter database open noresetlogs

*

The first  1  Line error :

ORA-03113:  End of communication channel file

SQL> startup force

ORA-24324:  Service handle not initialized

ORA-01041:  internal error , hostdef  Extension does not exist

SQL> exit

from  Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options  To break off

C:\Documents and Settings\Administrator>

C:\Documents and Settings\Administrator>

C:\Documents and Settings\Administrator>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on  Thursday  8 month  1 22:58:16 2019

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to idle routine .

SQL> create spfile from pfile;

File created .

SQL> startup mount

ORACLE  Routine started .

Total System Global Area  612368384 bytes

Fixed Size                  1332348 bytes

Variable Size             174762884 bytes

Database Buffers          432013312 bytes

Redo Buffers                4259840 bytes

Database loading complete .

SQL> alter database open;

alter database open

*

The first  1  Line error :

ORA-03113:  End of communication channel file

SQL> startup foree mount

SP2-0714:  invalid         STARTUP  Combination of options

SQL> startup force mount

ORA-24324:  Service handle not initialized

ORA-01041:  internal error , hostdef  Extension does not exist

SQL> exit

from  Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options  To break off

C:\Documents and Settings\Administrator>

C:\Documents and Settings\Administrator>

C:\Documents and Settings\Administrator>

C:\Documents and Settings\Administrator>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on  Thursday  8 month  1 22:59:52 2019

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to idle routine .

SQL> startup force mount

ORACLE  Routine started .

Total System Global Area  612368384 bytes

Fixed Size                  1332348 bytes

Variable Size             174762884 bytes

Database Buffers          432013312 bytes

Redo Buffers                4259840 bytes

Database loading complete .

SQL> exit

from  Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options  To break off

C:\Documents and Settings\Administrator>

C:\Documents and Settings\Administrator>rman target /

Recovery Manager : Release 10.2.0.1.0 - Production on  Thursday  8 month  1 23:00:08 2019

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to target database : ORCL (DBID=1313825522,  Not opened )

RMAN> list backupset;

Replace the recovery directory with the target database control file

RMAN> list archivelog all;

Description does not match any archive logs in the recovery Directory

RMAN> exit

Recovery manager complete .

Finally try to use recover database until cancel; To solve :

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

C:\Documents and Settings\Administrator>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on  Thursday  8 month  1 23:00:58 2019

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connect to :

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> archive log list;

Database logging mode               Non Archive Mode

Auto archive               Ban

End of archive             USE_DB_RECOVERY_FILE_DEST

The earliest online log sequence      19102

Current log sequence            19104

SQL>

SQL> recover database until cancel;

ORA-00279:  change  471095223 ( stay  06/07/2019 20:22:28  Generate )  For threads  1  It's necessary

ORA-00289:  Suggest :

E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2019_08_01\O1_MF_1_

19104_%U_.ARC

ORA-00280:  change  471095223 ( For threads  1)  In the sequence  #19104  in

Specify log : {

E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG

Applied logs .

Complete media recovery .

SQL>

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS

---------- ---------- ---------- ---------- ---------- --- ----------------

FIRST_CHANGE# FIRST_TIME

------------- --------------

         1          1      19103   52428800          1 NO  INACTIVE

    471044279 07-6 month  -19

         3          1      19102   52428800          1 NO  INACTIVE

    471009748 06-6 month  -19

         2          1      19104   52428800          1 NO  CURRENT

    471095223 07-6 month  -19

SQL> alter database open;

alter database open

*

The first  1  Line error :

ORA-01589:  To open a database, you must use  RESETLOGS  or  NORESETLOGS  Options

SQL> alter database open noresetlogs;

The database has changed .

SQL> select open_mode from v$database;

OPEN_MODE

----------

READ WRITE

C:\Documents and Settings\Administrator>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on  Thursday  8 month  1 23:18:30 2019

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to idle routine .

SQL> startup

ORACLE  Routine started .

Total System Global Area  612368384 bytes

Fixed Size                  1332348 bytes

Variable Size             174762884 bytes

Database Buffers          432013312 bytes

Redo Buffers                4259840 bytes

Database loading complete .

The database has been opened .

SQL> select open_mode from v$database;

OPEN_MODE

----------

READ WRITE

Finally, restart the database again : 

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

C:\Documents and Settings\Administrator>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on  Thursday  8 month  1 23:18:30 2019

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to idle routine .

SQL> startup

ORACLE  Routine started .

Total System Global Area  612368384 bytes

Fixed Size                  1332348 bytes

Variable Size             174762884 bytes

Database Buffers          432013312 bytes

Redo Buffers                4259840 bytes

Database loading complete .

The database has been opened .

SQL> select open_mode from v$database;

OPEN_MODE

----------

READ WRITE

 

 

summary :

    1、 Open the alarm log , You can get a lot of useful information from the alarm log

    2、pfile and spfile You can't make mistakes in the contents of

    3、oradim Used to create windows Under the oracle service , At least make sure that sqlplus Can be used in startup or shutdown command

    4、redo If it's damaged, try it in SQL We're going to recover under the weather , Use command : recover database until cancel;

 

--- The last suggestion :

1、 Change to archive mode

2、 Do it in time RMAN Backup

Please bring the original link to reprint ,thank
Similar articles

2021-06-04

2021-06-04

2021-06-06

2021-06-27

2021-06-29