[20171109]資料庫與asm例項的通訊問題.txt

[20171109]資料庫與asm例項的通訊問題.txt

http://www.toadworld.com/platforms/oracle/b/weblog/archive/2017/11/07/solving-communication-problems-between-db-and-asm-instances

--//摘要其中的段落:
--//建立新表空間報如下錯誤:

A long time ago I received a call from a customer saying that there were some errors in the database instance. Well,
interestingly the databases were executing DMLs properly without any issue. I asked the customer if these errors
appeared only with one specific operation like an Insert, or like a CREATE <something>, etc.; and he said that he was
running a script received from the application team to create several tablespaces with its datafiles.  When he was
running the script he was receiving the following errors:

ORA-01119: error in creating database file '+DATA'
ORA-17502: ksfdcre:4 Failed to create file +DATA
ORA-27300: OS system dependent operation:open failed with status: 2
ORA-27301: OS failure message: No such file or directory
ORA-27302: failure occurred at: sskgmsmr_7

--//有人意外刪除 $ORACLE_HOME/dbs/ab_<ASM SID>.dat.注意是grid使用者下dbs目錄.
# locate ab_+ASM1.dat
/u01/app/11.2.0/grid/dbs/ab_+ASM1.dat


First, you can see that the set of errors says that there is a directory or file that don't exist in the OS on the other
hand, it points to the ASM disk group, which in this case is "+DATA". So this is confusing, because either the file that
the database is looking for is in ASM or it is in the OS.  I did a quick check of the database instance and it was OK.
There were no errors in the alert log, all the disks were healthy. On the database side, however, there seemed to be
some issues, specifically with the sentences "CREATE TABLESPACE" which the customer had in the script provided by the
application team.

So, the clues were:

    No issues with the ASM Instance
    DMLs were being executed successfully in the database instance.
    CREATE TABLESPACE statements fail in the database instance.
    ASM and OS are both involved in a "file" or "directory" that doesn't exist.

With these four clues to go by, you should be on the right track if your concepts are solid. The root cause you would be
thinking about would involve the file that the database instance uses to communicate with the ASM instance This file is
named "ab_<ASM SID>.dat" and it is located in the $ORACLE_HOME/dbs. You need to know about the existence of this file
and what its function is.This file rarely has issues, or rarely causes problems…but sometimes it happens,

Let's define this file:

What is the "ab_<ASM SID>.dat" file? This file is used by the database instance to message an ASM instance. When the
database instance needs to send a message to the ASM instance, the database instance reads this file in order to find
out the information required for getting connected to the ASM instance. This file is in $ORACLE_HOME/dbs. If this file
doesn't exist the database will not be able to connect to the ASM instance and you will receive an error. This file is
important because it is involved in the database instance work.

Some time ago I wrote an article with several tests of where this file is required to execute some sentences in the
database and in which sentences the file is not required. You can read the details here.

The conclusion of that earlier article indicates:

    Tablespace creation – required
    Datafile creation – required
    Table creation – not required
    DML operations – not required
    Drop tablespace – not required
    Delete datafile – not required
    Startup database instance – required
    Shutdown database instance – not required

Well, taking that into account, to solve this customer's issue, I listed all the files in $ORACLE_HOME/dbs and the root
cause was confirmed. The file "ab_<ASM SID>.dat" did not exist in the directory. I asked the customer if he had moved
the file somewhere else or if he'd deleted it and he said that the day before the junior DBA was "cleaning" logs and
traces that were using space and that could be deleted. I think that one of those files that "could be deleted" was
"ab_<ASM SID>.dat". As I said before, this situation happens rarely. Solving the problem is not a big deal; what we have
to do is reboot the ASM instance, but in order to do that we have to reboot the database instance as well.  After
rebooting the ASM instance the file was recreated and the database was able to use it. The script that the customer had
was executed successfully and all the CREATE TABLESPACE operations were success.

Conclusion

Sometimes there are issues whose root cause is very rare, and in order to determine it quickly we have to have all our
concepts solid; otherwise, we might spend several hours trying to figure out what's going on, reading notes and so on.

In this case, it was very important to identify the clues. We had four clues here which pointed us to the right root
cause.  Sometimes the customer is stressed and under pressure and wants us to fix the problem fast, but DBAs have to
stay calm, we have to extract the clues (syntomps), to think about the root cause,  to create an hypothesis and work to
prove it. To shorten diagnostic time make sure you're on solid ground conceptually, which you can do by practicing
various scenarios while you are getting prepared for a certification.