Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

How to fix the problems in the execution process of ​ Oracle database

2025-01-16 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Servers >

Share

Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to fix the problems in the execution process of Oracle database". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Failure during operation

There are many oracle failures, bad blocks and abnormal file deletions during operation, especially when the primary DBA is just getting started. Let's simulate this scene first.

Undo tablespace is one of the core Oracle tablespaces, which will cause serious problems and failures after deletion.

SQL > select file_name from dba_data_files where tablespace_name='UNDOTBS1'

FILE_NAME

/ u01/oradata/WILSON/datafile/o1_mf_undotbs1_7xt3yzl5_.dbf

The current database is running in Open, and suddenly the Undo file is deleted at the post-OS level.

[oracle@bspdev datafile] $ls-l | grep undo

-rw-r- 1 oracle oinstall 346038272 Sep 6 07:21 o1_mf_undotbs1_7xt3yzl5_.dbf

[oracle@bspdev datafile] $mv o1_mf_undotbs1_7xt3yzl5_.dbf o1_mf_undotbs1_7xt3yzl5_.dbf.bak

[oracle@bspdev datafile] $ls-l | grep undo

-rw-r- 1 oracle oinstall 346038272 Sep 6 07:21 o1_mf_undotbs1_7xt3yzl5_.dbf.bak

At this point, the working process of the "checker" in the previous article can appear in alert log.

Fri Sep 06 07:25:47 2013

Checker run found 1 new persistent data failures

Fri Sep 06 07:26:34 2013

Starting background process SMCO

Fri Sep 06 07:26:34 2013

SMCO started with pid=19, OS id=4819

Fri Sep 06 07:26:46 2013

Errors in file / u01/diag/rdbms/wilson/wilson/trace/wilson_mmnl_4418.trc:

ORA-01116: error in opening database file 3

ORA-01110: datafile 3:'/ u01Universe Oradata Universe WILSONUniplex datafileplico1examples mfqundotbs1and7xt3yzl5room.dbf'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

Fri Sep 06 07:26:48 2013

Errors in file / u01/diag/rdbms/wilson/wilson/trace/wilson_m000_4835.trc:

ORA-01116: error in opening database file 3

ORA-01110: datafile 3:'/ u01Universe Oradata Universe WILSONUniplex datafileUniplico1ipmfqundotbs1xt3yzl5room.dbf'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

An error was reported in about two seconds and found that the file had been deleted and could not be opened.

At this point, we use the list failure command on rman to view the generated error message.

RMAN > list failure all

List of Database Failures

=

Failure ID Priority Status Time Detected Summary

--

242 HIGH OPEN 06-SEP-13 One or more non-system datafiles are missing

We use advisor failure to see an Oracle proposal.

RMAN > advise failure

List of Database Failures

=

Failure ID Priority Status Time Detected Summary

--

242 HIGH OPEN 06-SEP-13 One or more non-system datafiles are missing

Analyzing automatic repair options; this may take some time

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: SID=30 device type=DISK

Analyzing automatic repair options complete

Mandatory Manual Actions

=

1. If file/ u01/oradata/WILSON/datafile/o1_mf_undotbs1_7xt3yzl5_.dbf was unintentionally renamed or moved, restore it

2. Automatic repairs may be available if you shutdown the database and restart it in mount mode

3. Contact Oracle Support Services if the preceding recommendations cannot be used, or if they do not fix the failures selected for repair

Optional Manual Actions

=

No manual actions available

Automated Repair Options

=

No automatic repair options available

Notice that we don't see the script information in automated repair options. It shows that Oracle doesn't seem to have a good method at present. In Manual Actions, Oracle DRA requires that the database be restarted to the mount state in order for automatic scripts to appear. Manual Actions are the steps that Oracle feels need to be performed manually by the user to continue.

Restart the library and load it into the mount state.

-- forced closure

RMAN > shutdown abort

Oracle instance shut down

RMAN > startup mount

Connected to target database (not started)

Oracle instance started

Database mounted

Total System Global Area 849530880 bytes

Fixed Size 1339824 bytes

Variable Size 616566352 bytes

Database Buffers 226492416 bytes

Redo Buffers 5132288 bytes

At this point, use the DRA tool again to look at the problems and tips.

RMAN > advise failure

List of Database Failures

=

Failure ID Priority Status Time Detected Summary

--

242 HIGH OPEN 06-SEP-13 One or more non-system datafiles are missing

Analyzing automatic repair options; this may take some time

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: SID=18 device type=DISK

Analyzing automatic repair options complete

Mandatory Manual Actions

=

No manual actions available

Optional Manual Actions

=

1. If file/ u01/oradata/WILSON/datafile/o1_mf_undotbs1_7xt3yzl5_.dbf was unintentionally renamed or moved, restore it

Automated Repair Options

=

Option Repair Description

--

1 Restore and recover datafile 3

Strategy: The repair includes complete media recovery with no data loss

Repair script. / u01/diag/rdbms/wilson/wilson/hm/reco_1850469943.hm

Use the repair failure review command to view the execution statement.

RMAN > repair failure preview

Strategy: The repair includes complete media recovery with no data loss

Repair script. / u01/diag/rdbms/wilson/wilson/hm/reco_1850469943.hm

Contents of repair script.:

# restore and recover datafile

Restore datafile 3

Recover datafile 3

Note: at this point Oracle DRA discovers that we currently have backup and archive logs for Undo. So with restore followed by recover, recovery can be achieved quickly.

If you don't find a problem in preview, you can restore it by executing the repair failure command.

RMAN > repair failure

Strategy: The repair includes complete media recovery with no data loss

Repair script. / u01/diag/rdbms/wilson/wilson/hm/reco_1850469943.hm

Contents of repair script.:

# restore and recover datafile

Restore datafile 3

Recover datafile 3

Do you really want to execute the above repair (enter YES or NO)? Yes

Executing repair script

Starting restore at 06-SEP-13

Using channel ORA_DISK_1

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 00003 to / u01/oradata/WILSON/datafile/o1_mf_undotbs1_7xt3yzl5_.dbf

Channel ORA_DISK_1: reading from backup piece / u01/flash_recovery_area/WILSON/backupset/2013_09_06/o1_mf_nnndf_TAG20130906T061608_92l0od6w_.bkp

Channel ORA_DISK_1: piece handle=/u01/flash_recovery_area/WILSON/backupset/2013_09_06/o1_mf_nnndf_TAG20130906T061608_92l0od6w_.bkp tag=TAG20130906T061608

Channel ORA_DISK_1: restored backup piece 1

Channel ORA_DISK_1: restore complete, elapsed time: 00:00:25

Finished restore at 06-SEP-13

Starting recover at 06-SEP-13

Using channel ORA_DISK_1

Starting media recovery

Media recovery complete, elapsed time: 00:00:02

Finished recover at 06-SEP-13

Repair failure complete

-- you can choose to open the database

Do you want to open the database (enter YES or NO)? Yes

Database opened

We can monitor the recovery steps in alert log.

-- Restore process

Fri Sep 06 07:35:49 2013

Full restore complete of datafile 3 / u01/oradata/WILSON/datafile/o1_mf_undotbs1_92l5b0v4_.dbf. Elapsed time: 0:00:15

Checkpoint is 3838694

Last deallocation scn is 3817636

Undo Optimization current scn is 3815429

Fri Sep 06 07:35:54 2013

Alter database recover datafile list clear

Completed: alter database recover datafile list clear

-- recovery process

Alter database recover if needed

Datafile 3

Media Recovery Start

Serial Media Recovery started

Recovery of Online Redo Log: Thread 1 Group 2 Seq 176 Reading mem 0

Mem# 0: / u01/oradata/WILSON/onlinelog/o1_mf_2_870n48hc_.log

Mem# 1: / u01/flash_recovery_area/WILSON/onlinelog/o1_mf_2_870n4dtl_.log

Recovery of Online Redo Log: Thread 1 Group 3 Seq 177 Reading mem 0

Mem# 0: / u01/oradata/WILSON/onlinelog/o1_mf_3_870n4lsg_.log

Mem# 1: / u01/flash_recovery_area/WILSON/onlinelog/o1_mf_3_870n4o31_.log

Recovery of Online Redo Log: Thread 1 Group 1 Seq 178 Reading mem 0

Mem# 0: / u01/oradata/WILSON/onlinelog/o1_mf_1_870n42n1_.log

Mem# 1: / u01/flash_recovery_area/WILSON/onlinelog/o1_mf_1_870n44z3_.log

Media Recovery Complete (wilson)

Completed: alter database recover if needed

Datafile 3

Fri Sep 06 07:36:04 2013

Alter database open

At this point, the database error is eliminated.

RMAN > list failure

No failures found that match specification

Finally, we have one more command to use, which is change failure. The function of the Change Failure command is to change the status of the error as shown. The most common practice is: when an error occurs, if we do not solve it at the RMAN level, such as using a cold backup method to restore. Failure information does not change the state. At this point, you can use the change failure command to set the state to Closed, such as: change failure all closed.

This is the end of the content of "how to fix the problems in the execution process of Oracle database". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Servers

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report